https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/
/*Minor changes have been made to include additional information*/
CREATE VIEW [dbo].[BI_AR_Apply_Detail]
AS
SELECT T.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_Name, CM.SHRTNAME AS Short_Name, T.DOCDATE AS Document_Date, T.GLPOSTDT AS 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 AS Payment_Batch, T.docTypeNum AS Document_Type_and_Number, T.DOCNUMBR AS Document_Number,
T.ORTRXAMT AS Original_Trx_Amount, T.CURTRXAM AS Current_Trx_Amount, T.amountApplied AS Total_Applied_Amount, A.APPTOAMT AS Amount_Applied, A.APTODCTY AS Applied_to_Doc_Type, A.debitType AS Applied_to_Doc_Type_Name,
A.APTODCNM AS Applied_to_Doc_Number, A.APTODCDT AS Applied_to_Document_Date, A.ApplyToGLPostDate AS Applied_to_GL_Posting_Date, A.DISTKNAM AS Discount, A.WROFAMNT AS Writeoff, A.DATE1 AS Apply_Date, A.GLPOSTDT AS Apply_GL_Posting_Date,
D.ORTRXAMT AS Applied_To_Doc_Total, D.DINVPDOF AS Applied_To_Date_Paid_Off, D.CURTRXAM AS Applied_To_Doc_Unapplied_Amount, D.CSPORNBR AS Customer_PO_Number, D.SLPRSNID
FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN
'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM AS amountApplied
FROM dbo.RM20101
WHERE (RMDTYPAL > 6) AND (VOIDSTTS = 0)
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN
'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM AS amountApplied
FROM dbo.RM30101
WHERE (RMDTYPAL > 6) AND (VOIDSTTS = 0)) AS T INNER JOIN
dbo.RM00101 AS CM ON T.CUSTNMBR = CM.CUSTNMBR INNER JOIN
(SELECT tO1.CUSTNMBR, tO2.APTODCTY, tO2.APTODCNM, tO2.APFRDCTY, tO2.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, tO2.APPTOAMT,
tO2.ApplyToGLPostDate, tO2.APTODCDT, tO2.DISTKNAM, tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT
FROM dbo.RM20201 AS tO2 INNER JOIN
dbo.RM20101 AS tO1 ON tO2.APTODCTY = tO1.RMDTYPAL AND tO2.APTODCNM = tO1.DOCNUMBR
UNION
SELECT tH1.CUSTNMBR, tH2.APTODCTY, tH2.APTODCNM, tH2.APFRDCTY, tH2.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, tH2.APPTOAMT,
tH2.ApplyToGLPostDate, tH2.APTODCDT, tH2.DISTKNAM, tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT
FROM dbo.RM30201 AS tH2 INNER JOIN
dbo.RM30101 AS tH1 ON tH2.APTODCTY = tH1.RMDTYPAL AND tH2.APTODCNM = tH1.DOCNUMBR) AS A ON A.APFRDCTY = T.RMDTYPAL AND A.APFRDCNM = T.DOCNUMBR INNER JOIN
(SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM, CSPORNBR, SLPRSNID
FROM dbo.RM20101 AS RM20101_1
UNION
SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, 0 AS CURTRXAM, CSPORNBR, SLPRSNID
FROM dbo.RM30101 AS RM30101_1) AS D ON A.APTODCTY = D.RMDTYPAL AND A.APTODCNM = D.DOCNUMBR
GO
--------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_AR_Pmt_SR]
AS
SELECT DISTINCT Document_Number, SLPRSNID as PmtSR
FROM dbo.BI_AR_Apply_Detail
GO
No comments:
Post a Comment