/****** Object: View [dbo].[BI_AR_Apply_Detail] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*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
/****** Object: View [dbo].[BI_AR_Pmt_SR] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_Pmt_SR]
AS
SELECT DISTINCT Document_Number, MAX(SLPRSNID) AS PmtSR
FROM dbo.BI_AR_Apply_Detail
GROUP BY Document_Number
GO
/****** Object: View [dbo].[BI_AR_CashRcts_SOP_Unposted] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_CashRcts_SOP_Unposted]
AS
SELECT dbo.SOP10103.SOPTYPE, dbo.SOP10103.SOPNUMBE, dbo.SOP10103.SEQNUMBR, dbo.SOP10103.PYMTTYPE, dbo.SOP10103.DOCNUMBR, dbo.SOP10103.RMDTYPAL, dbo.SOP10103.CHEKBKID,
dbo.SOP10103.CHEKNMBR, dbo.SOP10103.CARDNAME, dbo.SOP10103.RCTNCCRD, dbo.SOP10103.AUTHCODE, dbo.SOP10103.AMNTPAID, dbo.SOP10103.OAMTPAID, dbo.SOP10103.AMNTREMA,
dbo.SOP10103.OAMNTREM, dbo.SOP10103.DOCDATE, dbo.SOP10103.EXPNDATE, dbo.SOP10103.CURNCYID, dbo.SOP10103.CURRNIDX, dbo.SOP10103.TRXSORCE, dbo.SOP10103.DEPSTATS,
dbo.SOP10103.DELETE1, dbo.SOP10103.GLPOSTDT, dbo.SOP10103.CASHINDEX, dbo.SOP10103.DEPINDEX, dbo.SOP10103.EFTFLAG, dbo.SOP10103.DEX_ROW_ID, dbo.SOP10100.CUSTNMBR,
dbo.SOP10100.BACHNUMB, dbo.SOP10100.SLPRSNID, dbo.SOP10100.VOIDSTTS
FROM dbo.SOP10103 INNER JOIN
dbo.SOP10100 ON dbo.SOP10103.SOPNUMBE = dbo.SOP10100.SOPNUMBE AND dbo.SOP10103.SOPTYPE = dbo.SOP10100.SOPTYPE
GO
/****** Object: View [dbo].[BI_AR_Unposted] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_Unposted]
AS
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RM_Doc_Type, BACHNUMB, docTypeNum, DOCNUMBR, ORTRXAMT, Curtrxam, amtAppl, RMDTYPAL, mrkdnamt, Factor, ORTRXAMT + mrkdnamt AS GrossAmt, SLPRSNID,
TAXAMNT, CURNCYID, voidstts, CHEKBKID AS TrxCheckbook
FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, BACHNUMB,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' 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,
ORTRXAMT AS Curtrxam, 0 AS amtAppl, RMDTYPAL, 0 AS mrkdnamt, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor,
'' AS SLPRSNID, 0 AS TAXAMNT, CURNCYID, 0 AS voidstts, CHEKBKID
FROM dbo.RM10201
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, BACHNUMB,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' END AS docTypeNum, DOCNUMBR, DOCAMNT,
DOCAMNT AS Curtrxam, 0 AS amtAppl, RMDTYPAL, 0 AS mrkdnamt, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor,
SLPRSNID, TAXAMNT, CURNCYID, 0 AS voidstts, '' AS Chekbkid
FROM dbo.RM10301
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, BACHNUMB,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE PYMTTYPE WHEN 5 THEN 'Payment - Check ' +
CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 4 THEN 'Payment - Cash' WHEN 6 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, AMNTPAID,
AMNTPAID AS Curtrxam, 0 AS amtAppl, RMDTYPAL, 0 AS Expr1, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor, SLPRSNID,
0 AS Taxamnt, CURNCYID, VOIDSTTS, CHEKBKID
FROM dbo.BI_AR_CashRcts_SOP_Unposted
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, CASE SOPTYPE WHEN 3 THEN 'Sale/Invoice' WHEN 4 THEN 'Return' END AS stype, BACHNUMB,
CASE SOPTYPE WHEN 3 THEN 'Sale/Invoice' WHEN 4 THEN 'Return' END AS Expr1, SOPNUMBE, DOCAMNT, DOCAMNT AS Curtrxam, 0 AS amtAppl, SOPTYPE, MRKDNAMT,
CASE SOPTYPE WHEN 3 THEN 1 WHEN 4 THEN - 1 END AS factor, SLPRSNID, TAXAMNT, CURNCYID, VOIDSTTS, '' AS Chekbkid
FROM dbo.SOP10100
WHERE (SOPTYPE > 2)) AS UnpostedAr
GO
/****** Object: View [dbo].[BI_AR_Posted] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_Posted]
AS
SELECT T.CUSTNMBR AS Customer_ID, T.DOCDATE AS Document_Date, T.GLPOSTDT AS GL_Posting_Date,
CASE T .RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' 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, T.RMDTYPAL, SOPSR.MRKDNAMT, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor,
T.ORTRXAMT + SOPSR.MRKDNAMT AS GrossAmt, SOPSR.SLPRSNID, SOPSR.TAXAMNT, SOPSR.CURNCYID, T.VOIDSTTS, T.checkbkid AS TrxCheckbook
FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' 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, VOIDSTTS, MSCSCHID AS checkbkid
FROM dbo.RM20101
WHERE (VOIDSTTS = 0)
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' 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, VOIDSTTS, MSCSCHID AS checkbkid
FROM dbo.RM30101
WHERE (VOIDSTTS = 0)) AS T LEFT OUTER JOIN
(SELECT SOPNUMBE, SOPTYPE, MRKDNAMT, SLPRSNID, TAXAMNT, CURNCYID
FROM dbo.SOP30200
WHERE (SOPTYPE IN (3, 4))) AS SOPSR ON T.DOCNUMBR = SOPSR.SOPNUMBE
GO
/****** Object: View [dbo].[BI_SOP_Comments] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Comments]
AS
SELECT dbo.SOP10106.SOPTYPE, dbo.SOP10106.SOPNUMBE, dbo.SOP10106.CMMTTEXT AS Comments, SOPCMTID.COMMNTID AS CommentID
FROM dbo.SOP10106 INNER JOIN
(SELECT SOPTYPE, SOPNUMBE, COMMNTID
FROM dbo.SOP30200) AS SOPCMTID ON dbo.SOP10106.SOPNUMBE = SOPCMTID.SOPNUMBE AND dbo.SOP10106.SOPTYPE = SOPCMTID.SOPTYPE
WHERE (dbo.SOP10106.SOPTYPE >= 3)
GO
/****** Object: View [dbo].[BI_AR_ALL] Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_ALL]
AS
SELECT AllAR.Status, AllAR.Customer_ID, AllAR.Document_Date, AllAR.GL_Posting_Date, AllAR.RM_Doc_Type, AllAR.Payment_Batch, AllAR.Document_Type_and_Number, AllAR.Document_Number,
AllAR.Original_Trx_Amount * AllAR.Factor AS Original_Trx_Amount, AllAR.Current_Trx_Amount, AllAR.Total_Applied_Amount, AllAR.RMDTYPAL, ISNULL(AllAR.MRKDNAMT, 0) * AllAR.Factor AS Markdown,
AllAR.Factor, ISNULL(AllAR.GrossAmt, 0) * AllAR.Factor AS GrossAmt, dbo.BI_SOP_Comments.CommentID, dbo.BI_SOP_Comments.Comments, AllAR.SLPRSNID AS TrxSr, dbo.BI_AR_Pmt_SR.PmtSR,
dbo.RM00101.SLPRSNID AS CusSrID, CASE WHEN (pmtsr IS NULL OR
pmtsr = '') THEN CASE WHEN allar.slprsnid IS NULL THEN rm00101.slprsnid ELSE allar.slprsnid END ELSE pmtsr END AS FinalSR, ISNULL(AllAR.TAXAMNT, 0) * AllAR.Factor AS Taxamnt,
dbo.RM00101.CUSTCLAS AS CustomerClass, dbo.RM00101.CUSTNAME AS CustomerName, ISNULL(AllAR.CURNCYID, 'TTD') AS Currency, (AllAR.Original_Trx_Amount - ISNULL(AllAR.TAXAMNT, 0))
* AllAR.Factor AS OriginalAmtNoVAT, CASE WHEN (voidstts = 0 OR
voidstts IS NULL) THEN 'Normal' ELSE 'Voided' END AS [Void Status], AllAR.TrxCheckbook, dbo.RM00101.CHEKBKID AS CusCheckbook
FROM (SELECT 'Posted' AS Status, Customer_ID, Document_Date, GL_Posting_Date, RM_Doc_Type, Payment_Batch, Document_Type_and_Number, Document_Number, Original_Trx_Amount, Current_Trx_Amount,
Total_Applied_Amount, RMDTYPAL, MRKDNAMT, Factor, GrossAmt, SLPRSNID, TAXAMNT, CURNCYID, VOIDSTTS, TrxCheckbook
FROM dbo.BI_AR_Posted
UNION
SELECT 'Unposted' AS Status, CUSTNMBR, DOCDATE, GLPOSTDT, RM_Doc_Type, BACHNUMB, docTypeNum, DOCNUMBR, ORTRXAMT, Curtrxam, amtAppl, RMDTYPAL, mrkdnamt, Factor, GrossAmt,
SLPRSNID, TAXAMNT, CURNCYID, voidstts, TrxCheckbook
FROM dbo.BI_AR_Unposted) AS AllAR LEFT OUTER JOIN
dbo.RM00101 ON AllAR.Customer_ID = dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.BI_AR_Pmt_SR ON AllAR.Document_Number = dbo.BI_AR_Pmt_SR.Document_Number LEFT OUTER JOIN
dbo.BI_SOP_Comments ON AllAR.Document_Number = dbo.BI_SOP_Comments.SOPNUMBE
GO
No comments:
Post a Comment