--01 Open Receivables Detail.sql -- SE rewrite of BO Open Receivables Aging detail (FactOpenReceivable) -- View: udv_transaction_detail_34 -- Open rows approximated where Invoiced/Transaction amount exceeds Received, or Current or Aging is set. DECLARE @CorpCode varchar(15) = NULL; -- DECLARE @ParentRollup bit = 0; -- ParentCompany — NOT USED IN SE SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, NULL AS ParentCompany, -- NOT USED IN SE td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, td.[Invoice Number] AS InvoiceNo, td.[Invoice Date] AS InvoiceDate, td.[Due Date] AS DueDate, td.[Finance Transfer Type Desc] AS DocType, -- approximate ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) AS OriginalAmount, CASE WHEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) IS NULL THEN NULL ELSE ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) END AS OpenAmount, CASE WHEN td.[Due Date] IS NULL THEN NULL ELSE DATEDIFF(DAY, td.[Due Date], GETDATE()) END AS DaysPastDue, -- derived td.[Aging Category] AS AgingBucket, CASE WHEN td.[Aging Category] IN ('Current', '0') THEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) ELSE 0 END AS CurrentAmt, -- approximate pivot CASE WHEN td.[Aging Category] LIKE '%1%30%' OR td.[Aging Category] = '1-30' THEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) ELSE 0 END AS Days1To30Amt, CASE WHEN td.[Aging Category] LIKE '%31%60%' OR td.[Aging Category] = '31-60' THEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) ELSE 0 END AS Days31To60Amt, CASE WHEN td.[Aging Category] LIKE '%61%90%' OR td.[Aging Category] = '61-90' THEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) ELSE 0 END AS Days61To90Amt, CASE WHEN td.[Aging Category] LIKE '%90%' OR td.[Aging Category] LIKE '%Over%' THEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) ELSE 0 END AS DaysOver90Amt, CAST(GETDATE() AS date) AS AsOfDate FROM se_reports.dbo.[udv_transaction_detail_34] td WHERE td.[Invoice Number] IS NOT NULL AND ( td.[Current or Aging] IS NOT NULL OR ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) > ISNULL(td.[Received Amount], 0) ) AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) ORDER BY td.[Corp Code], td.[File #], td.[Due Date], td.[Invoice Number];