Notes

WITH base AS (

SELECT
COMPANY AS company,
CUSTOMER_NUMBER AS customer_number,
CUST_ACC_NAME AS customer_name,
TRY_CONVERT(DECIMAL(18,2), AMOUNT_DUE_ORIGINAL) AS original_amount_due,
TRY_CONVERT(DECIMAL(18,2), OUTSTANDING_AMOUNT) AS outstanding_amount,
TRY_CONVERT(DECIMAL(18,2), TOTAL_AB_30) AS total_over_terms,
TRY_CONVERT(DECIMAL(18,2), B_CURR_AMT) AS current_amount,
TRY_CONVERT(DECIMAL(18,2), B_1_AMT) AS amt_1_30,
TRY_CONVERT(DECIMAL(18,2), B_2_AMT) AS amt_31_60,
TRY_CONVERT(DECIMAL(18,2), B_3_AMT) AS amt_61_90,
TRY_CONVERT(DECIMAL(18,2), B_4_AMT) AS amt_91_120,
TRY_CONVERT(DECIMAL(18,2), B_5_AMT) AS amt_121_180,
TRY_CONVERT(DECIMAL(18,2), B_6_AMT) AS amt_181_360,
TRY_CONVERT(DECIMAL(18,2), B_7_AMT) AS amt_361_plus,

AR_SPECIALIST AS ar_specialist,
TRX_NUMBER AS invoice_number,
GL_DATE AS accounting_gl_date,
COALESCE(TRY_CONVERT(date, TRX_DATE), CONVERT(date, GETDATE())) AS transaction_date,

DUE_DATE AS due_date,
DAYS_DUE AS days_due,
TRANSACTION_CLASS AS transaction_class,

TRY_CONVERT(DECIMAL(18,2), AMOUNT_APPLIED) AS amount_applied,
STATUS AS status,
PLACE_HOLDER1 AS last_payment_amount,
PLACE_HOLDER2 AS last_payment_date,
PLACE_HOLDER3 AS account_level_notes,
PLACE_HOLDER4 AS account_level_date,
PLACE_HOLDER5 AS delivery_method,
PLACE_HOLDER6 AS payment_term,
PLACE_HOLDER7 AS place_holder7,
PLACE_HOLDER11 AS strategic,
TRY_CONVERT(DECIMAL(18,2), PLACE_HOLDER12) AS p2p_amount,
PLACE_HOLDER13 AS p2p_date,
PLACE_HOLDER14 AS place_holder14,
PLACE_HOLDER15 AS place_holder15,
ORIG_INVOICE_NUMBER AS orig_invoice_number,
INDUSTRY_GROUP AS industry_group,
REGION AS region,
AREA AS area,
BRANCH AS branch,
DISTRICT AS district,
PROJ_NUM AS project_number,

CASE
WHEN CHARINDEX('-', PROJ_NUM) > 0
THEN TRY_CONVERT(BIGINT, LEFT(PROJ_NUM, CHARINDEX('-', PROJ_NUM) - 1))
ELSE TRY_CONVERT(BIGINT, PROJ_NUM)
END AS project_copy_1,

CASE
WHEN CHARINDEX('-', PROJ_NUM) > 0
THEN SUBSTRING(PROJ_NUM, CHARINDEX('-', PROJ_NUM) + 1, LEN(PROJ_NUM))
ELSE NULL
END AS project_copy_2,

CONTRACT_NUMBER AS contract_number,
CONTRACT_NAME AS contract_name,
TOPTASKNO AS top_task_number,

CASE
WHEN CHARINDEX('-', TOPTASKNO) > 0
THEN TRY_CONVERT(BIGINT, LEFT(TOPTASKNO, CHARINDEX('-', TOPTASKNO) - 1))
ELSE TRY_CONVERT(BIGINT, TOPTASKNO)
END AS top_task_copy_1,

CASE
WHEN CHARINDEX('-', TOPTASKNO) > 0
THEN SUBSTRING(TOPTASKNO, CHARINDEX('-', TOPTASKNO) + 1, LEN(TOPTASKNO))
ELSE NULL
END AS top_task_copy_2,

BILLER_NAME AS biller_name,
CLASS_CODE AS class_code,
WORK_ORDER AS work_order_ppm,
TRANSACTION_NOTES AS transaction_notes,

DUE_DATE AS custom_due_date,

CONVERT(DECIMAL(18,2), NULL) AS unapplied_cash

FROM OCF.ED_COLLECTIONS_AGING_DETAILS_REPORT
WHERE TRANSACTION_CLASS <> 'Receipt'

UNION ALL

SELECT
CAST('' AS NVARCHAR(100)) AS company,
CUSTOMER_ACCOUNT AS customer_number,
CUSTOMER_NAME AS customer_name,
CAST(0 AS DECIMAL(18,2)) AS original_amount_due,
CAST(0 AS DECIMAL(18,2)) AS outstanding_amount,
CAST(0 AS DECIMAL(18,2)) AS total_over_terms,
CAST(0 AS DECIMAL(18,2)) AS current_amount,
CAST(0 AS DECIMAL(18,2)) AS amt_1_30,
CAST(0 AS DECIMAL(18,2)) AS amt_31_60,
CAST(0 AS DECIMAL(18,2)) AS amt_61_90,
CAST(0 AS DECIMAL(18,2)) AS amt_91_120,
CAST(0 AS DECIMAL(18,2)) AS amt_121_180,
CAST(0 AS DECIMAL(18,2)) AS amt_181_360,
CAST(0 AS DECIMAL(18,2)) AS amt_361_plus,
CAST('' AS NVARCHAR(100)) AS ar_specialist,
CAST('' AS NVARCHAR(100)) AS invoice_number,
CAST('' AS NVARCHAR(100)) AS accounting_gl_date,
NULL AS transaction_date,
CAST('' AS NVARCHAR(100)) AS due_date,
CAST('' AS NVARCHAR(100)) AS days_due,
CAST('Receipt' AS NVARCHAR(100)) AS transaction_class,
CAST(0 AS DECIMAL(18,2)) AS amount_applied,
CAST('' AS NVARCHAR(100)) AS status,
CAST('' AS NVARCHAR(100)) AS last_payment_amount,
CAST('' AS NVARCHAR(100)) AS last_payment_date,
CAST('' AS NVARCHAR(100)) AS account_level_notes,
CAST('' AS NVARCHAR(100)) AS account_level_date,
CAST('' AS NVARCHAR(100)) AS delivery_method,
CAST('' AS NVARCHAR(100)) AS payment_term,
CAST('' AS NVARCHAR(100)) AS place_holder7,
CAST('' AS NVARCHAR(100)) AS strategic,
CAST(0 AS DECIMAL(18,2)) AS p2p_amount,
CAST('' AS NVARCHAR(100)) AS p2p_date,
CAST('' AS NVARCHAR(100)) AS place_holder14,
CAST('' AS NVARCHAR(100)) AS place_holder15,
CAST('' AS NVARCHAR(100)) AS orig_invoice_number,
CAST('' AS NVARCHAR(100)) AS industry_group,

CASE
WHEN REGION_NAME IS NULL OR RTRIM(REGION_NAME) = '' THEN NULL
WHEN CHARINDEX(',', REGION_NAME) > 1 THEN CAST('unapplied' AS NVARCHAR(100))
WHEN CHARINDEX('-', REGION_NAME) > 0
AND CHARINDEX('-', REGION_NAME, CHARINDEX('-', REGION_NAME) + 1) > 0
THEN SUBSTRING(
REGION_NAME,
CHARINDEX('-', REGION_NAME) + 1,
CHARINDEX('-', REGION_NAME, CHARINDEX('-', REGION_NAME) + 1)
- CHARINDEX('-', REGION_NAME) - 1
)
ELSE CAST('unapplied' AS NVARCHAR(100))
END AS region,

CASE
WHEN AREA_NAME IS NULL OR RTRIM(AREA_NAME) = '' THEN NULL
WHEN CHARINDEX(',', AREA_NAME) > 1 THEN CAST('unapplied' AS NVARCHAR(100))
WHEN CHARINDEX('-', AREA_NAME) > 0
AND CHARINDEX('-', AREA_NAME, CHARINDEX('-', AREA_NAME) + 1) > 0
THEN SUBSTRING(
AREA_NAME,
CHARINDEX('-', AREA_NAME) + 1,
CHARINDEX('-', AREA_NAME, CHARINDEX('-', AREA_NAME) + 1)
- CHARINDEX('-', AREA_NAME) - 1
)
ELSE CAST('unapplied' AS NVARCHAR(100))
END AS area,

CAST('unapplied' AS NVARCHAR(100)) AS branch,

CASE
WHEN DISTRICT_NAME IS NULL OR RTRIM(DISTRICT_NAME) = '' THEN NULL
WHEN CHARINDEX(',', DISTRICT_NAME) > 1 THEN CAST('unapplied' AS NVARCHAR(100))
WHEN CHARINDEX('-', DISTRICT_NAME) > 0
AND CHARINDEX('-', DISTRICT_NAME, CHARINDEX('-', DISTRICT_NAME) + 1) > 0
THEN SUBSTRING(
DISTRICT_NAME,
CHARINDEX('-', DISTRICT_NAME) + 1,
CHARINDEX('-', DISTRICT_NAME, CHARINDEX('-', DISTRICT_NAME) + 1)
- CHARINDEX('-', DISTRICT_NAME) - 1
)
ELSE CAST('unapplied' AS NVARCHAR(100))
END AS district,

CAST('' AS NVARCHAR(100)) AS project_number,
NULL AS project_copy_1,
CAST(NULL AS NVARCHAR(100)) AS project_copy_2,
CAST('' AS NVARCHAR(100)) AS contract_number,
CAST('' AS NVARCHAR(100)) AS contract_name,
CAST('' AS NVARCHAR(100)) AS top_task_number,
NULL AS top_task_copy_1,
CAST(NULL AS NVARCHAR(100)) AS top_task_copy_2,
CAST('' AS NVARCHAR(100)) AS biller_name,
CAST('' AS NVARCHAR(100)) AS class_code,
CAST('' AS NVARCHAR(100)) AS work_order_ppm,
CAST('' AS NVARCHAR(100)) AS transaction_notes,
CAST('' AS NVARCHAR(100)) AS custom_due_date,
CAST(UNNAPPLIED_AMOUNT AS DECIMAL(18,2)) AS unapplied_cash

FROM OCF.ED_UNAPPLIED_CASH_REPORT
),

customer_dim AS (
SELECT
CUSTOMER_NUMBER AS customer_number,
CUST_ACC_NAME AS customer_name
FROM OCF.ED_COLLECTIONS_AGING_DETAILS_REPORT

UNION

SELECT
CUSTOMER_ACCOUNT AS customer_number,
CUSTOMER_NAME AS customer_name
FROM OCF.ED_UNAPPLIED_CASH_REPORT
WHERE CUSTOMER_ACCOUNT NOT IN (
SELECT DISTINCT CUSTOMER_NUMBER
FROM OCF.ED_COLLECTIONS_AGING_DETAILS_REPORT
)
),

project_set AS (
SELECT DISTINCT
TRY_CAST(PROJECT_CODE AS BIGINT) AS project_copy_1,
PROJECTSET_COSTCENTER_CODE AS project_set
FROM OCF.ED_PROJ_HRCH_REPORT WITH (NOLOCK)
)

SELECT
b.*,
CASE
WHEN b.transaction_class = 'Receipt'
AND ISNULL(b.invoice_number, '') = ''
THEN 'Unapplied'
ELSE 'Invoice'
END AS row_type,

cd.customer_name AS customer_name_unified,
ps.project_set AS project_set

FROM base b
LEFT JOIN customer_dim cd
ON cd.customer_number = b.customer_number
LEFT JOIN project_set ps
ON ps.project_copy_1 = b.project_copy_1;