SELECT
dbo.CM20200.CMTrxNum,
dbo.CM20200.CMTrxType,
dbo.CM20200.TRXDATE,
dbo.CM20200.TRXAMNT,
dbo.CM20200.CMLinkID,
dbo.CM20200.paidtorcvdfrom,
dbo.PM30300.VENDORID,
dbo.PM30300.APTODCNM,
dbo.PM30200.TRXDSCRN,
dbo.PM30200.DOCDATE,
dbo.PM30200.DOCAMNT,
dbo.PM30200.DOCNUMBR,
dbo.PM30200.BACHNUMB,
dbo.PM30200.PORDNMBR,D.DistRef
FROM
dbo.CM20200 // Transaction Table
INNER JOIN dbo.PM30300 ON
(dbo.CM20200.CMTrxNum = dbo.PM30300.APFRDCNM) AND (dbo.CM20200.CMLinkID = dbo.PM30300.VENDORID)
INNER JOIN dbo.PM30200 ON
(dbo.PM30300.APTODCNM = dbo.PM30200.DOCNUMBR) AND
(dbo.PM30300.VENDORID = dbo.PM30200.VENDORID) AND
(dbo.PM30200.DOCTYPE = 1) // 6 - Checks, 1 - Invoices - But we need the invoice data
LEFT OUTER JOIN
(SELECT DISTINCT VENDORID, VCHRNMBR, DistRef FROM PM30600 WHERE CNTRLTYP = 0 AND DISTTYPE = 6) D
ON dbo.PM30300.VENDORID = D.VENDORID
AND dbo.PM30300.APTVCHNM = D.VCHRNMBR
WHERE
(dbo.PM30200.TRXDSCRN LIKE @SystemID + '%') AND
(dbo.CM20200.TRXDATE = @TRXDATE);
SELECT
T.CUSTNMBR,
CM.CUSTNAME,
T.DOCDATE,
T.GLPOSTDT,
T.CHEKNMBR,
sum(A.APPTOAMT) APPTOAMT,
A.APTODCNM,
T.BACHNUMB,
T.RMDTYPAL,
T.DOCNUMBR,
DistWorkFile.DistRef
FROM
-- T
(SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
DOCNUMBR, DEX_ROW_TS, BACHNUMB, CHEKNMBR
FROM RM20101 -- RM Apply Open File
WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) and (AGNGBUKT <= 1) -- Only completed items
UNION
SELECT
CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
DOCNUMBR, DEX_ROW_TS, BACHNUMB, CHEKNMBR
FROM RM30101 -- RM History File
WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) -- Only completed items
) T
INNER JOIN RM00101 CM -- Customer Master
ON T.CUSTNMBR = CM.CUSTNMBR
INNER JOIN
-- A
(SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM,
APFRDCTY, APFRDCNM, APPTOAMT
FROM RM20201 tO2 -- RM Apply Open File
INNER JOIN RM20101 tO1 -- RM Open File Debit Copy
ON tO2.APTODCTY = tO1.RMDTYPAL
AND tO2.APTODCNM = tO1.DOCNUMBR
WHERE (APTODCNM LIKE @SystemID + '%')
UNION
SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM,
APFRDCTY, APFRDCNM, APPTOAMT
FROM RM30201 tH2 -- RM Apply History File
INNER JOIN RM30101 tH1 -- RM History File
ON tH2.APTODCTY = tH1.RMDTYPAL
AND tH2.APTODCNM = tH1.DOCNUMBR
WHERE (APTODCNM LIKE @SystemID + '%')
) A
ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR
INNER JOIN
-- D
(SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF
FROM RM20101 -- RM Open File Debit Copy
UNION
SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF
FROM RM30101 -- RM History File
) D
ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR
LEFT OUTER JOIN
-- Distribution Work File
(SELECT DISTINCT DOCNUMBR, RMDTYPAL, DistRef
FROM dbo.RM10101
WHERE DISTTYPE = 9
) DistWorkFile ON
D.DOCNUMBR = DistWorkFile.DOCNUMBR and
D.RMDTYPAL = DistWorkFile.RMDTYPAL
WHERE
T.DEX_ROW_TS >= @DEX_ROW_TS and T.DEX_ROW_TS <= DATEADD(D,1,@DEX_ROW_TS)
GROUP BY
T.CUSTNMBR,
CM.CUSTNAME,
T.DOCDATE,
T.GLPOSTDT,
T.CHEKNMBR,
A.APTODCNM,
T.BACHNUMB,
T.RMDTYPAL,
T.DOCNUMBR,
DistWorkFile.DistRef
ORDER BY
A.APTODCNM;