Share
Report
3,015Is it possible for someone to help me add the DEBIT and CREDIT fields from RM10101 to the following query?
SELECT T.CUSTNMBR Customer_ID ,
CM.CUSTNAME Customer_Name ,
T.DOCDATE Document_Date ,
T.GLPOSTDT GL_Posting_Date ,
CASE T.RMDTYPAL
WHEN 7 THEN 'Credit Memo'
WHEN 8 THEN 'Return'
WHEN 9 THEN 'Payment'
END AS RM_Doc_Type ,
T.BACHNUMB Payment_Batch ,
T.CHEKNMBR Check_Number ,
T.DOCNUMBR Document_Number ,
ISNULL(PMD.DSTINDX,0) AS Cash_Account_index,
ISNULL(GLM.ACTDESCR,0) AS Cash_Account_Description,
ISNULL(GLM.ACTNUMBR_1,0) AS Cash_Account_Seg1,
ISNULL(GLM.ACTNUMBR_2,0) AS Cash_Account_Seg2,
ISNULL(GLM.ACTNUMBR_3,0) AS Cash_Account_Seg3,
ISNULL(GLM.ACTNUMBR_4,0) AS Cash_Account_Seg4,
ISNULL(GLM.ACTNUMBR_5,0) AS Cash_Account_Seg5,
T.ORTRXAMT Original_Trx_Amount ,
T.CURTRXAM Current_Trx_Amount ,
T.amountApplied Total_Applied_Amount ,
A.APPTOAMT Amount_Applied ,
A.APTODCTY Applied_to_Doc_Type ,
A.debitType Applied_to_Doc_Type_Name ,
A.APTODCNM Applied_to_Doc_Number ,
A.APTODCDT Applied_to_Document_Date ,
A.ApplyToGLPostDate Applied_to_GL_Posting_Date ,
A.DISTKNAM Discount ,
A.WROFAMNT Writeoff ,
A.DATE1 Apply_Document_Date ,
A.GLPOSTDT Apply_GL_Posting_Date ,
D.ORTRXAMT Applied_To_Doc_Total ,
D.DINVPDOF Applied_To_Date_Paid_Off ,
D.CURTRXAM Applied_To_Doc_Unapplied_Amount ,
D.CSPORNBR Customer_PO_Number
FROM ( SELECT CUSTNMBR ,
DOCDATE ,
GLPOSTDT ,
RMDTYPAL ,
CHEKNMBR ,
DOCNUMBR ,
ORTRXAMT ,
CURTRXAM ,
BACHNUMB ,
ORTRXAMT - CURTRXAM amountApplied
FROM RM20101
WHERE ( RMDTYPAL > 6 )
AND ( VOIDSTTS = 0 )
UNION
SELECT CUSTNMBR ,
DOCDATE ,
GLPOSTDT ,
RMDTYPAL ,
CHEKNMBR ,
DOCNUMBR ,
ORTRXAMT ,
CURTRXAM ,
BACHNUMB ,
ORTRXAMT - CURTRXAM amountApplied
FROM RM30101
WHERE ( RMDTYPAL > 6 )
AND ( VOIDSTTS = 0 )
) T
LEFT OUTER JOIN
( SELECT DOCNUMBR, DISTTYPE, RMDTYPAL, DSTINDX FROM dbo.RM10101
WHERE RMDTYPAL = 9 AND DISTTYPE = 1
UNION
SELECT DOCNUMBR, DISTTYPE, RMDTYPAL, DSTINDX FROM dbo.RM30301
WHERE RMDTYPAL = 9 AND DISTTYPE = 1
) AS PMD
ON PMD.DOCNUMBR = T.DOCNUMBR
LEFT OUTER JOIN dbo.GL00100 AS GLM ON GLM.ACTINDX = PMD.DSTINDX
INNER JOIN RM00101 CM ON T.CUSTNMBR = CM.CUSTNMBR
INNER JOIN GL00100 AS ACT ON ACT.ACTINDX = CM.RMARACC
INNER JOIN ( SELECT tO1.CUSTNMBR ,
APTODCTY ,
APTODCNM ,
APFRDCTY ,
APFRDCNM ,
CASE APTODCTY
WHEN 1 THEN 'Sale / Invoice'
WHEN 2 THEN 'Scheduled Payment'
WHEN 3 THEN 'Debit Memo'
WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair'
WHEN 6 THEN 'Warranty'
END AS debitType ,
APPTOAMT ,
ApplyToGLPostDate ,
APTODCDT ,
tO2.DISTKNAM ,
tO2.WROFAMNT ,
tO2.DATE1 ,
tO2.GLPOSTDT
FROM RM20201 tO2
INNER JOIN RM20101 tO1 ON tO2.APTODCTY = tO1.RMDTYPAL
AND tO2.APTODCNM = tO1.DOCNUMBR
UNION
SELECT tH1.CUSTNMBR ,
APTODCTY ,
APTODCNM ,
APFRDCTY ,
APFRDCNM ,
CASE APTODCTY
WHEN 1 THEN 'Sale / Invoice'
WHEN 2 THEN 'Scheduled Payment'
WHEN 3 THEN 'Debit Memo'
WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair'
WHEN 6 THEN 'Warranty'
END AS debitType ,
APPTOAMT ,
ApplyToGLPostDate ,
APTODCDT ,
tH2.DISTKNAM ,
tH2.WROFAMNT ,
tH2.DATE1 ,
tH2.GLPOSTDT
FROM RM30201 tH2
INNER JOIN RM30101 tH1 ON tH2.APTODCTY = tH1.RMDTYPAL
AND tH2.APTODCNM = tH1.DOCNUMBR
) A ON A.APFRDCTY = T.RMDTYPAL
AND A.APFRDCNM = T.DOCNUMBR
INNER JOIN ( SELECT RMDTYPAL ,
DOCNUMBR ,
ORTRXAMT ,
DINVPDOF ,
CURTRXAM ,
CSPORNBR
FROM RM20101
UNION
SELECT RMDTYPAL ,
DOCNUMBR ,
ORTRXAMT ,
DINVPDOF ,
CURTRXAM = 0 ,
CSPORNBR
FROM RM30101
) D ON A.APTODCTY = D.RMDTYPAL
AND A.APTODCNM = D.DOCNUMBR
*This post is locked for comments
All responses (
Answers (
