Extract data from Dynamics GP for Credit Card Reconciliation

Extract data from Dynamics GP for Credit Card Reconciliation

Bank reconciliation is often a painful process with GP particularly when there large amount credit card transaction fed into GP by 3rd party plugins like Azox CCE and Sales pad.  This script saves lot of time in compiling the required data to start the process of actual reconciliation with merchant and bank statement.

— expected output:
— extract gl transactions for a give account, add the payment #, order #, credit card authorization #, order #
— when salespad card control and azox credit card extensions is used in a replicated environment.

— purpose – credit card reconciliations

–Set the GL Account to which the credit card accounts post.
declare @account1 as nvarchar(5) = ‘9999’
declare @account2 as nvarchar(5) = ’99’
–Set the date variables
declare @month as nvarchar(2) = month(getdate())
declare @year as nvarchar(4) = year(getdate())
declare @day as nvarchar(2)= day(getdate())
–Set publisher and subscriber
declare @pub as varchar(3) = ‘PUB’
declare @sub as varchar(3) = ‘SUB’
–add cc authorization based on paymentno
SELECT azoxcc = CASE
WHEN receipttype = ‘Credit Card’
AND ortrxtyp = 9 THEN (SELECT TOP 1 referencenum
FROM
[eSource].[dbo].[ep_transactions]
WHERE invoicenum = paymentno)
ELSE azox
END,
*
FROM (
— step 5 add card name
SELECT receipttype = Isnull((SELECT recipttype = CASE rcptype
WHEN 1 THEN ‘Check’
WHEN 2 THEN ‘Cash’
WHEN 3 THEN ‘Credit Card’
END
FROM cm20300
WHERE cmlinkid = ormstrid
AND ( ( rcptnmbr = paymentno )
OR srcdocnum = paymentno )),
”),
cardtype = Isnull((SELECT cardname
FROM cm20300
WHERE cmlinkid = ormstrid
AND ( ( rcptnmbr = paymentno )
OR srcdocnum = paymentno )), ”),
*
FROM (
— Step 4 add payment number
SELECT paymentno = CASE
WHEN ortrxtyp = 9 THEN ordocnum
WHEN ortrxtyp = 2 THEN
(SELECT TOP 1 apfrdcnm
FROM rm20201
WHERE custnmbr = ormstrid
AND aptodcnm = (
SELECT
sopnumbe
FROM
sop30200
WHERE
orignumb = orctrnum
AND custnmbr = ormstrid
AND soptype = ‘3’))
WHEN ortrxtyp = 3 THEN
(SELECT TOP 1 apfrdcnm
FROM rm20201
WHERE custnmbr = ormstrid
AND
trxsorce = orgntsrc
AND
aptodcnm = orctrnum)
WHEN ortrxtyp = 0 THEN
(SELECT TOP 1 apfrdcnm
FROM rm20201
WHERE custnmbr = ormstrid
AND aptodcnm = (
SELECT
sopnumbe
FROM
sop30200
WHERE
orignumb = orctrnum
AND custnmbr = ormstrid
AND soptype = ‘3’))
ELSE ”
END,
*
FROM (
— Step 3 Add azox authorization numbers
— SUB = Subscriber server
SELECT Azox = CASE
WHEN ortrxtyp = 0 THEN (SELECT
referencenum
FROM
SUB.esource.dbo.ep_transactions
WHERE
transactionid = (SELECT authcode
FROM
SUB.pdt.dbo.sop10103
WHERE
sopnumbe = step3.orctrnum))
ELSE ”
END,
*
FROM (
— Step 2 ADD sales pad authorization #
SELECT SALESPAD = Isnull(
(SELECT TOP 1 auth_code
FROM spcc.creditcardtransaction
WHERE document_number =
STEP2.ordernumber2),
”),
*
FROM (
— ADD ORDER NUMBER = INVOICE NUMBER WHERE TRXTYPE = 3 CANADA INVOICE
SELECT *,
ORDERNUMBER2 =
CASE
WHEN ortrxtyp = 3
AND ordernumber =
” THEN
ordocnum
ELSE ordernumber
END
FROM (
— Add order numbers for all sales pad orders GL.trxtype = 3 or 2
SELECT GL.jrnentry,
GL.sourcdoc,
GL.refrence,
GL.dscriptn,
GL.trxdate,
GL.trxsorce,
dbo.gl00105.actnumbr_1,
dbo.gl00105.actnumbr_2,
dbo.gl00105.actnumbr_3,
dbo.gl00105.actnumbr_4,
GL.orgntsrc,
GL.orgnatyp,
GL.ortrxtyp,
GL.orctrnum,
GL.ormstrid,
GL.ormstrnm,
GL.ordocnum,
GL.ortrxsrc,
GL.crdtamnt,
GL.debitamt,
GL.curncyid,
GL.ratetpid,
GL.exgtblid,
GL.xchgrate,
GL.exchdate,
GL.orcrdamt,
GL.ordbtamt,
ORDERNUMBER = CASE
— 3 Most likely sales pad
WHEN
GL.ortrxtyp = ‘3’
THEN
Isnull(
(SELECT TOP 1 orignumb
FROM sop30200
WHERE
sopnumbe = GL.ordocnum), ”)
WHEN GL.ortrxtyp = ‘2’ THEN
GL.ordocnum
— 0 Most likely e Commerce
ELSE ”
END
FROM dbo.gl00105
INNER JOIN dbo.gl20000 AS
GL
ON
dbo.gl00105.actindx
= GL.actindx
WHERE ( dbo.gl00105.actnumbr_1
=
@account1 )
AND (
dbo.gl00105.actnumbr_2 =
@account2 )
AND ( Month(GL.trxdate) =
@month )
AND ( Year(GL.trxdate) =
@year ))
AS STEP1)
AS STEP2)
AS step3)
AS step4)
AS step5)
AS step6