GO
/****** Object: View [dbo].[BI_ARApplyDtl] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ARApplyDtl]
AS
SELECT T.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_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.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_Document_Date,
A.GLPOSTDT AS Apply_GL_Posting_Date, CAST(YEAR(A.DATE1) AS varchar) + '-' + RIGHT('00' + CAST(MONTH(A.DATE1) AS varchar), 2) AS AppYrMth,
DATEDIFF(dd, A.APTODCDT, A.DATE1) AS DaystoPay, T.SLPRSNID, T.SLSTERCD
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, ORTRXAMT - CURTRXAM AS amountApplied, SLPRSNID, SLSTERCD
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, ORTRXAMT - CURTRXAM AS amountApplied, SLPRSNID, SLSTERCD
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, tO1.SLPRSNID, tO1.SLSTERCD
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, tH1.SLPRSNID, tH1.SLSTERCD
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
GO
/****** Object: View [dbo].[BI_Cust_First_Sale] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Cust_First_Sale]
AS
SELECT CUSTNMBR, MIN(DOCDATE) AS FirstSalesDate
FROM dbo.SOP30200
GROUP BY CUSTNMBR
GO
/****** Object: View [dbo].[BI_Sales_History] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Sales_History]
AS
SELECT TOP (100) PERCENT dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date],
dbo.SOP30200.GLPOSTDT AS [GL Post Date], CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT)
* - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs,
CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30200.CUSTNMBR AS [Customer Number],
CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype
= 4 THEN 'Return' WHEN sop30200.soptype = 5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type],
dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name],
dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold],
dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD, dbo.SOP30200.SLPRSNID AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2,
dbo.IV00101.USCATVLS_3, dbo.SOP30300.ITEMNMBR AS [Item Number], dbo.SOP30300.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName,
LEFT(dbo.SOP30300.ITEMNMBR, 2) AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, dbo.BI_Cust_First_Sale.FirstSalesDate,
LEFT(dbo.SOP30300.ITEMNMBR, 2) AS AB, dbo.SOP30300.UNITPRCE,
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.ZIP ELSE rm00301.zip END AS [Sales Person Department],
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.SLPRSNFN ELSE rm00301.SLPRSNFN END AS [Sales Person First Name],
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.SPRSNSLN ELSE rm00301.SPRSNSLN END AS [Sales Person Last Name],
dbo.SOP30200.LOCNCODE, dbo.SOP30300.SLPRSNID AS SOPLineSR,
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.slprsnid ELSE rm00301.slprsnid END AS FinalSR, dbo.SOP30300.LNITMSEQ,
dbo.SOP30300.DEX_ROW_ID, AlertCRM.dbo.InvoiceDetail.EmployeeCode
FROM dbo.RM00101 INNER JOIN
dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR INNER JOIN
AlertCRM.dbo.InvoiceDetail ON dbo.SOP30300.SOPNUMBE = AlertCRM.dbo.InvoiceDetail.InvoiceNumber AND
dbo.SOP30300.LNITMSEQ = AlertCRM.dbo.InvoiceDetail.InvoiceDetailRecord LEFT OUTER JOIN
dbo.RM00301 AS RM00301_1 ON AlertCRM.dbo.InvoiceDetail.EmployeeCode = RM00301_1.SLPRSNID LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
dbo.BI_Cust_First_Sale ON dbo.RM00101.CUSTNMBR = dbo.BI_Cust_First_Sale.CUSTNMBR
WHERE (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4)) AND (dbo.SOP30300.CMPNTSEQ = 0)
GO
/****** Object: View [dbo].[BI_Sales_History_ReturnItems] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Sales_History_ReturnItems]
AS
SELECT dbo.BI_ARApplyDtl.Applied_to_Doc_Number AS AppliedtoInv, dbo.BI_ARApplyDtl.RM_Doc_Type, dbo.BI_Sales_History.[SOP Number] AS RtnNo,
dbo.BI_Sales_History.[Item Number], dbo.BI_Sales_History.Sales AS RtnAmount, dbo.BI_Sales_History.Qty AS RtnQty
FROM dbo.BI_ARApplyDtl LEFT OUTER JOIN
dbo.BI_Sales_History ON dbo.BI_ARApplyDtl.Document_Number = dbo.BI_Sales_History.[SOP Number]
GO
/****** Object: View [dbo].[BI_AR_LastDateApplied] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_LastDateApplied]
AS
SELECT Applied_to_Doc_Number, MAX(Document_Date) AS LastDocDate, MAX(Apply_Document_Date) AS LastDateApplied
FROM dbo.BI_ARApplyDtl
GROUP BY Applied_to_Doc_Number
GO
/****** Object: View [dbo].[BI_CusMaster] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_CusMaster]
AS
SELECT Cus.CUSTNMBR, Cus.CUSTNAME, Cus.CPRCSTNM, Cus.CUSTCLAS, CASE WHEN Cus.Div IS NULL
THEN '(NONE)' WHEN Cus.Div = '' THEN '(NONE)' ELSE Cus.Div END AS Div, CASE WHEN rm00101.SLPRSNID IS NULL
THEN '(NONE)' WHEN rm00101.SLPRSNID = '' THEN '(NONE)' ELSE rtrim(rm00101.SLPRSNID) END AS SLPRSNID, CASE WHEN rm00101.SALSTERR IS NULL
THEN '(NONE)' WHEN rm00101.SALSTERR = '' THEN '(NONE)' ELSE rtrim(rm00101.SALSTERR) END AS SALSTERR, Cus.RlNatCus,
dbo.RM00101.CUSTNAME AS RlNatCusNm, RTRIM(Cus.CUSTNMBR) + ' | ' + RTRIM(Cus.CUSTNAME) AS CusLbl, dbo.RM00201.CLASDSCR,
LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClsDiv, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCs,
LEFT(dbo.RM00201.CLASDSCR, 3) + '-' + Cus.Div AS CusDiv
FROM (SELECT RM00101_1.CUSTNMBR, RM00101_1.CUSTNAME, RM00101_1.CPRCSTNM, RM00101_1.CUSTCLAS, CASE LEFT(CUSTNMBR, 1)
WHEN '2' THEN LEFT(Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN CLASDSCR ELSE 'PARENT CUSTOMER' END AS Div,
RM00101_1.SLPRSNID, RM00101_1.SALSTERR, CASE WHEN CPRCSTNM = '' THEN custnmbr ELSE cprcstnm END AS RlNatCus
FROM dbo.RM00101 AS RM00101_1 LEFT OUTER JOIN
dbo.RM00201 AS RM00201_1 ON RM00101_1.CUSTCLAS = RM00201_1.CLASSID) AS Cus LEFT OUTER JOIN
dbo.RM00201 ON Cus.CUSTCLAS = dbo.RM00201.CLASSID LEFT OUTER JOIN
dbo.RM00101 ON Cus.CUSTNMBR = dbo.RM00101.CUSTNMBR
GO
/****** Object: View [dbo].[BI_ARActivity] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ARActivity]
AS
SELECT DATEDIFF(d, AllOS.DOCDATE, GETDATE()) AS DaysOld, AllOS.CUSTNMBR, AllOS.CPRCSTNM, AllOS.DOCNUMBR, AllOS.DOCDATE, AllOS.DUEDATE,
AllOS.ORTRXAMT, AllOS.CURTRXAM, CASE WHEN rmdtypal >= 7 THEN curtrxam * - 1 ELSE curtrxam END AS CurrOsAmt, AllOS.RMDTYPAL,
CASE WHEN rmdtypal >= 7 THEN ortrxamt ELSE 0 END AS Credits, CASE WHEN rmdtypal >= 7 THEN 0 ELSE ortrxamt END AS Debits,
CAST(YEAR(AllOS.DOCDATE) AS varchar) + '-' + RIGHT('00' + CAST(MONTH(AllOS.DOCDATE) AS varchar), 2) AS YrMth,
dbo.BI_AR_LastDateApplied.LastDateApplied, AllOS.SLPRSNID, AllOS.SLSTERCD, dbo.BI_AR_LastDateApplied.LastDocDate,
dbo.BI_CusMaster.CUSTNAME
FROM (SELECT CUSTNMBR, CPRCSTNM, DOCNUMBR, DOCDATE, DUEDATE, ORTRXAMT, CURTRXAM, RMDTYPAL, SLPRSNID, SLSTERCD
FROM dbo.RM20101
UNION
SELECT CUSTNMBR, CPRCSTNM, DOCNUMBR, DOCDATE, DUEDATE, ORTRXAMT, CURTRXAM, RMDTYPAL, SLPRSNID, SLSTERCD
FROM dbo.RM30101) AS AllOS LEFT OUTER JOIN
dbo.BI_CusMaster ON AllOS.CUSTNMBR = dbo.BI_CusMaster.CUSTNMBR LEFT OUTER JOIN
dbo.BI_AR_LastDateApplied ON AllOS.DOCNUMBR = dbo.BI_AR_LastDateApplied.Applied_to_Doc_Number
GO
/****** Object: View [dbo].[BI_FullyPaidSalesDetails] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_FullyPaidSalesDetails]
AS
SELECT dbo.BI_ARActivity.DaysOld, dbo.BI_ARActivity.CUSTNMBR, dbo.BI_ARActivity.CPRCSTNM, dbo.BI_ARActivity.DOCNUMBR, dbo.BI_ARActivity.DOCDATE,
dbo.BI_ARActivity.DUEDATE, dbo.BI_ARActivity.ORTRXAMT, dbo.BI_ARActivity.CURTRXAM, dbo.BI_ARActivity.CurrOsAmt, dbo.BI_ARActivity.RMDTYPAL,
dbo.BI_ARActivity.Credits, dbo.BI_ARActivity.Debits, dbo.BI_ARActivity.YrMth, dbo.BI_ARActivity.LastDateApplied, RTRIM(dbo.BI_ARActivity.SLPRSNID)
AS SLPRSNID, dbo.BI_ARActivity.SLSTERCD, dbo.BI_ARActivity.LastDocDate, dbo.BI_ARActivity.CUSTNAME, dbo.BI_Sales_History.[Item Number],
dbo.BI_Sales_History.[Item Description], dbo.BI_Sales_History.UNITPRCE, dbo.BI_Sales_History.[Salesperson ID],
dbo.BI_Sales_History.[Sales Person First Name], dbo.BI_Sales_History.[Sales Person Last Name], dbo.BI_Sales_History.[Sales Person Department],
dbo.BI_Sales_History.[Units Sold], dbo.BI_Sales_History.Qty, dbo.BI_Sales_History.Sales, dbo.BI_Sales_History.FinalSR,
ISNULL(dbo.BI_Sales_History_ReturnItems.RtnAmount, 0) AS RtnAmount, ISNULL(dbo.BI_Sales_History_ReturnItems.RtnQty, 0) AS RtnQty,
ISNULL(dbo.BI_Sales_History_ReturnItems.RtnAmount, 0) + dbo.BI_Sales_History.Sales AS FSales, ISNULL(dbo.BI_Sales_History_ReturnItems.RtnQty, 0)
+ dbo.BI_Sales_History.Qty AS FQty
FROM dbo.BI_ARActivity INNER JOIN
dbo.BI_Sales_History ON dbo.BI_ARActivity.DOCNUMBR = dbo.BI_Sales_History.[SOP Number] LEFT OUTER JOIN
dbo.BI_Sales_History_ReturnItems ON dbo.BI_Sales_History.[SOP Number] = dbo.BI_Sales_History_ReturnItems.AppliedtoInv AND
dbo.BI_Sales_History.[Item Number] = dbo.BI_Sales_History_ReturnItems.[Item Number]
GO
/****** Object: View [dbo].[BI_SRMaster] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SRMaster]
AS
SELECT RTRIM(dbo.RM00301.SLPRSNID) AS SLPRSNID, CASE WHEN rm00301.SALSTERR = '' THEN '(NONE)' ELSE rtrim(rm00301.SALSTERR) END AS SALSTERR,
ISNULL(dbo.RM00303.SLTERDSC, '(NONE)') AS SLTERDSC, dbo.RM00301.EMPLOYID, dbo.RM00301.SLPRSNFN, dbo.RM00301.SPRSNSLN,
RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrNm, RTRIM(dbo.RM00301.SLPRSNID)
+ ' | ' + RTRIM(CASE WHEN rm00301.SALSTERR = '' THEN '(NONE)' ELSE rm00301.SALSTERR END) + ' | ' + RTRIM(dbo.RM00301.SLPRSNFN)
+ ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrTerrLbl, dbo.RM00301.STATE, dbo.RM00301.INACTIVE AS SRInactive, ISNULL(dbo.RM00303.INACTIVE, 0)
AS TerrInactive, dbo.RM00301.SPRSNSMN
FROM dbo.RM00301 LEFT OUTER JOIN
dbo.RM00303 ON dbo.RM00301.SALSTERR = dbo.RM00303.SALSTERR
GO
No comments:
Post a Comment