bberkovich of 1800 Radiator
5/18/2016 - 10:03 PM
Past Due balances for MSO PIDs - used to identify PIDs with a past due before sending out rebate reports
Past Due balances for MSO PIDs - used to identify PIDs with a past due before sending out rebate reports
--This will give the balance of a PID as of the 1st of the current month.
SELECT BGM.CustomerID
,BGM.GroupID
,bg.Name
,wc.com
,wc.rte_wh
INTO #Pids
FROM accounting_11.dbo.buyinggroupmember BGM
LEFT JOIN accounting_11.dbo.BuyingGroup BG --Used to identify which MSO
ON BGM.GroupID = BG.GroupID
JOIN crm_10.dbo.weekly_cust WC
ON wc.pid = bgm.CustomerID
ORDER BY GroupID
DROP TABLE #TempAging
DROP TABLE #AgingSummary
/*****************************beginning OF AGING**********************************************************************************/
CREATE TABLE #tempaging
(
[type] [VARCHAR](20) NOT NULL
,[typeid] [SMALLINT] NOT NULL
,[id] [BIGINT] NOT NULL
,[payterm] [VARCHAR](20) NULL
,[customerid] [BIGINT] NOT NULL
,[rpid] [BIGINT] NULL
,[date] [SMALLDATETIME] NOT NULL
,[amount] [DECIMAL](8, 2) NOT NULL
,[balance] [DECIMAL](8, 2) NOT NULL
,[ref] [VARCHAR](30) NULL
,[companyid] [SMALLINT] NOT NULL
)
------------------------------------------------------------------------------------------
-----SALESORDERS --delete from #tempaging
INSERT INTO #tempaging
SELECT t.[type]
,t.[typeid]
,t.[id]
,t.payterm
,t.customerid
,t.rpid
,t.[date]
,t.amount
,t.balance
,t.ref
,t.companyid
FROM (
-- sos and unconverted credit sos
SELECT CASE
WHEN s.aramt >= 0 THEN 'SO'
ELSE 'Credit'
END AS [type]
,5 [typeid]
,s.so [id]
,s.payterms [payterm]
,s.pid customerid
,s.rpid
,s.saledate [date]
,s.aramt [amount]
,s.aramt - Isnull(Sum(pa.amountallocated), 0) [balance]
,s.po ref
,sc.collectorcompanyid companyid
FROM salesandquotes_10.dbo.som s (nolock)
INNER JOIN accounting_11.dbo.salescollector sc (nolock)
ON sc.so = s.so
AND sc.sotypeid = 1
INNER JOIN #Pids c (nolock)
ON c.customerid = s.pid
--inner join wizmo2005_10.dbo.payterms pt (nolock) on pt.paytermid=c.paytermid
LEFT JOIN accounting_11.dbo.paymentallocation pa (nolock)
ON pa.entitytypeid = 5
AND pa.entityid = s.so
AND pa.isvalid = 1
AND dateallocated < CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01'
WHERE saledate BETWEEN CAST((YEAR(DATEADD(Year,-2,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' AND
CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01'
AND NOT EXISTS (SELECT paymentid
FROM accounting_11.dbo.payment (nolock)
WHERE paymenttypeid = 4
AND authnumber = CONVERT(VARCHAR, s.so)
AND isvalid = 1)
GROUP BY s.so
,s.payterms
,s.pid
,s.rpid
,s.saledate
,s.aramt
,s.po
,sc.collectorcompanyid
HAVING ( s.aramt - Isnull(Sum(pa.amountallocated), 0) ) <> 0) t;
-------CREDITS
INSERT INTO #tempaging
SELECT t.[type]
,t.[typeid]
,t.[id]
,t.payterm
,t.customerid
,t.rpid
,t.[date]
,t.amount
,t.balance
,t.ref
,t.companyid
FROM (
-- credits
SELECT 'Credit' [type]
,5 typeid
,s.so [id]
,s.payterms [payterm]
,p.customerid
,p.rpid
,p.datecreated [date]
,-p.amount [amount]
,-( p.amount - Isnull(Sum(pa.amountallocated), 0) ) [balance]
,s.po ref
,sc.collectorcompanyid companyid
FROM accounting_11.dbo.payment p (nolock)
INNER JOIN salesandquotes_10.dbo.som s (nolock)
ON s.so = p.authnumber
INNER JOIN accounting_11.dbo.salescollector sc (nolock)
ON sc.so = s.so
AND sc.sotypeid = 1
INNER JOIN #Pids c (nolock)
ON c.customerid = s.pid
--inner join wizmo2005_10.dbo.payterms pt (nolock) on pt.paytermid=c.paytermid
LEFT JOIN accounting_11.dbo.paymentallocation pa (nolock)
ON pa.paymentid = p.paymentid
AND pa.isvalid = 1
AND pa.dateallocated < CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01'
WHERE p.paymenttypeid = 4
AND p.isvalid = 1
AND p.datecreated BETWEEN CAST((YEAR(DATEADD(Year,-2,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' AND
CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01'
GROUP BY s.so
,s.payterms
,p.customerid
,p.rpid
,p.datecreated
,p.amount
,s.po
,sc.collectorcompanyid
HAVING ( p.amount - Isnull(Sum(pa.amountallocated), 0) ) <> 0) t;
----------------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [ix_tempaging_customerid]
ON [#tempaging]([customerid])
WITH FILLFACTOR=75 ON [primary]
INSERT INTO #tempaging
SELECT t.[type]
,t.[typeid]
,t.[id]
,t.payterm
,t.customerid
,t.rpid
,t.[date]
,t.amount
,t.balance
,t.ref
,t.companyid
FROM (
-- fees
SELECT ft.description [type]
,7 typeid
,f.feeid [id]
,NULL [payterm]
,f.customerid
,f.rpid
,f.datecreated [date]
,f.amount
,f.amount - Isnull(Sum(pa.amountallocated), 0) [balance]
,CONVERT(VARCHAR, f.feeid) ref
,f.companyid
FROM accounting_11.dbo.fee f (nolock)
INNER JOIN accounting_11.dbo.feetype ft (nolock)
ON f.feetypeid = ft.feetypeid
INNER JOIN #Pids c (nolock)
ON c.customerid = f.customerid
--and c.customertypeid<>57
--inner join wizmo2005_10.dbo.payterms pt (nolock) on pt.paytermid=c.paytermid
LEFT JOIN accounting_11.dbo.paymentallocation pa
ON pa.entitytypeid = 7
AND pa.entityid = f.feeid
AND pa.isvalid = 1
AND pa.dateallocated < GETDATE()
WHERE Isnull(f.rpid, 0) = 0
AND f.datecreated BETWEEN CAST((YEAR(DATEADD(Year,-2,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' AND
CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01'
GROUP BY ft.description
,f.feeid
,f.customerid
,f.rpid
,f.datecreated
,f.amount
,f.companyid
HAVING ( f.amount - Isnull(Sum(pa.amountallocated), 0) ) <> 0
UNION ALL
-- overpayments
SELECT ptype.description [type]
,2 typeid
,p.paymentid [id]
,NULL payterm
,p.customerid
,p.rpid
,p.datecreated [date]
,-p.amount amount
,-( p.amount - Isnull(Sum(pa.amountallocated), 0) ) balance
,Isnull(p.authnumber, '') ref
,p.companyid
FROM accounting_11.dbo.payment p (nolock)
INNER JOIN accounting_11.dbo.paymenttype ptype (nolock)
ON p.paymenttypeid = ptype.paymenttypeid
INNER JOIN #Pids c (nolock)
ON c.customerid = p.customerid
--and c.customertypeid <> 57
--inner join wizmo2005_10.dbo.payterms pt (nolock) on pt.paytermid=c.paytermid
LEFT JOIN accounting_11.dbo.paymentallocation pa (nolock)
ON pa.paymentid = p.paymentid
AND pa.isvalid = 1
AND pa.dateallocated < GETDATE()
WHERE p.paymenttypeid IN ( 1, 3, 9, 12 )
AND p.isvalid = 1
AND Isnull(p.whse, 0) <> 34
AND Isnull(p.rpid, 0) = 0
AND p.datecreated BETWEEN CAST((YEAR(DATEADD(Year,-2,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' AND
CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01'
AND ( EXISTS (SELECT customerid
FROM #tempaging
WHERE customerid = p.customerid) )
GROUP BY ptype.description
,p.paymentid
,p.customerid
,p.rpid
,p.datecreated
,p.amount
,p.authnumber
,p.companyid
HAVING ( p.amount - Isnull(Sum(pa.amountallocated), 0) ) <> 0) t;
DROP TABLE #AgingSummary
SELECT customerid
,Sum(ta.amount) AS TotalAmount
,Sum(CASE
WHEN balance = amount AND Datediff(day, [date], CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' ) <= 30 THEN balance
ELSE 0
END) Balance_30
,Sum(CASE
WHEN balance = amount AND Datediff(day, [date], CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' ) BETWEEN 31 AND 60 THEN balance
ELSE 0
END) Balance_31_60
,Sum(CASE
WHEN balance = amount AND Datediff(day, [date], CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' ) BETWEEN 61 AND 90 THEN balance
ELSE 0
END) Balance_61_90
,Sum(CASE
WHEN balance = amount AND Datediff(day, [date], CAST((YEAR(DATEADD(Year,0,GETDATE()))) as VARCHAR(4)) + '-' + CAST(MONTH(DATEADD(Day,0,GETDATE())) AS VARCHAR(2)) + + '-01' ) > 90 THEN balance
ELSE 0
END) Balance_91Plus
--totalSO = SUM(CASE WHEN ta.TYPE = 'so' THEN Amount ELSE 0 END),
--totalSOBalance = SUM(CASE WHEN ta.TYPE = 'so' THEN Balance ELSE 0 END),
--totalFinanceCharge = SUM(CASE WHEN ta.TYPE = 'so' THEN Amount ELSE 0 END),
--totalFinanceCHargeBalance = SUM(CASE WHEN ta.TYPE = 'so' THEN Balance ELSE 0 END),
INTO #agingsummary
FROM #tempaging ta
GROUP BY customerid