DECLARE @BeginDate AS DATETIME, @EndDate AS DATETIME
SET @BeginDate = '02/01/2018'
SET @EndDate = '02/06/2018'
SELECT
RTRIM(PM30300.VENDORID) AS VendorID
, RTRIM(PM00200.VENDNAME) AS VendorName
, PM30300.DOCDATE AS PaymentDate
, RTRIM(PM30300.VCHRNMBR) AS PaymentVoucher
, PaymentDocType = CASE PM30300.DOCTYPE
WHEN 5 THEN 'Credit Memo'
WHEN 6 THEN 'Payment'
ELSE 'Other'
END
, RTRIM(PM30300.APFRDCNM) AS CheckNumber
, CheckTotal.CheckAmount AS CheckAmount
, RTRIM(PM30300.APTODCNM) AS ApplyToDocument
, PM30300.APFRMAPLYAMT AS ApplyAmount
, DistDetail.RecordSource
, DistDetail.Company1InvDocDate
, DistDetail.Company1InvVoucher
, DistDetail.Company1InvDistType
, DistDetail.DistSequence
, DistDetail.Account
, DistDetail.AccountDescription
, DistDetail.DebitAmount
, DistDetail.CreditAmount
, CASE WHEN DistDetail.RecordSource = 'Company2' THEN DistDetail.DebitAmount - DistDetail.CreditAmount ELSE 0 END AS Company2Amount
, CASE WHEN DistDetail.RecordSource = 'Company1' THEN DistDetail.DebitAmount - DistDetail.CreditAmount ELSE 0 END AS Company1Amount
FROM Company1..PM30300
INNER JOIN Company1..PM00200 ON PM30300.VENDORID = PM00200.VENDORID
INNER JOIN
(
SELECT PM30300.VCHRNMBR, SUM(PM30300.APFRMAPLYAMT) AS CheckAmount
FROM Company1..PM30300
GROUP BY PM30300.VCHRNMBR
) AS CheckTotal ON PM30300.VCHRNMBR = CheckTotal.VCHRNMBR
LEFT OUTER JOIN
(
SELECT
'Company1' AS RecordSource
, RTRIM(PM30200.VCHRNMBR) AS Company1InvVoucher
, PM30200.VENDORID AS VendorID
, PM00200.VENDNAME AS VendorName
, PM30200.DOCAMNT AS Company1InvAmount
, PM30200.DOCDATE AS Company1InvDocDate
, PM30600.DSTSQNUM AS DistSequence
, RTRIM(GL00100.ACTNUMBR_1) + '-' + RTRIM(GL00100.ACTNUMBR_2) + '-' + RTRIM(GL00100.ACTNUMBR_3) + '-' + RTRIM(GL00100.ACTNUMBR_4) + '-' + RTRIM(GL00100.ACTNUMBR_5) AS Account
, RTRIM(GL00100.ACTDESCR) AS AccountDescription
, Company1InvDistType = CASE PM30600.DISTTYPE
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Payable'
WHEN 3 THEN 'Discount Available'
WHEN 4 THEN 'Discount Taken'
WHEN 5 THEN 'Finance Charge'
WHEN 6 THEN 'Purchase'
WHEN 7 THEN 'Trade Disc.'
WHEN 8 THEN 'Misc. Charge'
WHEN 9 THEN 'Freight'
WHEN 10 THEN 'Taxes'
WHEN 11 THEN 'Writeoffs'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'GST Disc'
WHEN 14 THEN 'PPS Amount'
WHEN 16 THEN 'Round'
WHEN 17 THEN 'Realized Gain'
WHEN 18 THEN 'Relaized Loss'
WHEN 19 THEN 'Due To'
WHEN 20 THEN 'Due From'
END
, PM30600.DEBITAMT AS DebitAmount
, PM30600.CRDTAMNT AS CreditAmount
, RTRIM(PM30600.DistRef) AS DistReference
FROM Company1..PM30200
INNER JOIN Company1..PM30600 ON PM30200.VCHRNMBR = PM30600.VCHRNMBR AND PM30200.DOCTYPE = PM30600.DOCTYPE
INNER JOIN Company1..GL00100 ON PM30600.DSTINDX = GL00100.ACTINDX
INNER JOIN Company1..PM00200 ON PM30200.VENDORID = PM00200.VENDORID
WHERE
(PM30200.DOCTYPE = 1)
AND (PM30600.DSTINDX <> 26257) --Due From AEP
AND (PM30600.DISTTYPE <> 2) --PAYABLES
UNION ALL
SELECT
'Company2' AS RecordSource
, RTRIM(GL20000.ORCTRNUM) AS Company1InvVoucher
, GL20000.ORMSTRID AS VendorID
, GL20000.ORMSTRNM AS VendorName
, 0 AS Company1InvAmount
, GL20000.TRXDATE AS Company1InvDocDate
, GL20000.SEQNUMBR AS DistSequence
, RTRIM(GL00100.ACTNUMBR_1) + '-' + RTRIM(GL00100.ACTNUMBR_2) + '-' + RTRIM(GL00100.ACTNUMBR_3) + '-' + RTRIM(GL00100.ACTNUMBR_4) + '-' + RTRIM(GL00100.ACTNUMBR_5) AS Account
, RTRIM(GL00100.ACTDESCR) AS AccountDescription
, '' AS Company1InvDistType
, GL20000.DEBITAMT AS DebitAmount
, GL20000.CRDTAMNT AS CreditAmount
, RTRIM(GL20000.DSCRIPTN) AS DistReference
FROM Company2..GL20000
INNER JOIN Company2..GL00100 ON GL20000.ACTINDX = GL00100.ACTINDX
WHERE
(GL20000.ACTINDX <> 120) --INTERCO A/P MEMCO/Company2 - Due to Company1
AND (GL20000.TRXSORCE LIKE 'ICTRX%')
) AS DistDetail ON RTRIM(PM30300.APTVCHNM) = DistDetail.Company1InvVoucher
WHERE
(PM30300.DOCDATE BETWEEN @BeginDate AND @EndDate)
ORDER BY
PM30300.DOCDATE
, DistDetail.Company1InvVoucher
, PM30300.APFRDCNM
, RecordSource
, DistSequence