--04 Open Receivables With Service Provider.sql -- SE rewrite of BO Open Receivables with service provider -- Views: udv_transaction_detail_34, udv_profile_information_view_34 -- Note: DimTEFullServiceList.SrvcProvider — unable to find; filter by Consultant as closest. DECLARE @CorpCode varchar(15) = NULL; DECLARE @SrvcProvider varchar(100) = NULL; -- was DimTEFullServiceList.SrvcProvider; use Consultant name/approx SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, NULL AS ParentCompany, -- Not used 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, 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, 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, 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 JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON td.[File #] = pf.[File #] 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) AND ( @SrvcProvider IS NULL OR @SrvcProvider = '' OR @SrvcProvider = 'XXXX' OR pf.[Consultant] LIKE '%' + @SrvcProvider + '%' -- SrvcProvider initials — unable to find ) ORDER BY td.[Corp Code], td.[File #], td.[Due Date], td.[Invoice Number];