Untitled
select * from phoenix_sandbox.ar_aging_category where snapshot_date = '2024-12-31' and unique_key in ('84581870|10332') -----------AR Aging Query----------------------- /* UPDATE phoenix_sandbox.ar_aging_category SET invoice_number = split_part(unique_key , '|' , 2) WHERE snapshot_date is null ; -- splits the invoice # */ */ /* UPDATE phoenix_sandbox.ar_aging_category SET snapshot_date = '2024-12-31' -- EOM Date WHERE snapshot_date is null ; -- update snapshot date */ -- below query generates input for ar-raw for Tatiana for update /* select A.customer_ofa_account_number as customer_ofa_account_number, A.customer_ofa_account_number || '|' || A.invoice_number as Unique_Key, A.customer_name as customer_name, A.invoice_date as invoice_date, A.invoice_number as invoice_number, A.payment_due_date as payment_due_date, datediff(day,A.payment_due_date,A.snapshot_date) as Age, case when datediff(day,A.payment_due_date,A.snapshot_date) > 30 then 'Yes' else 'no' end as "Age_>_30", A.amount_due_original_inv_cur as amount_due_original_inv_cur, A.amount_due_original_inv_cur as Original_invoice_curr_amount, A.amount_due_remaining_inv_cur as Remaining_invoice_curr_amount, A.amount_due_original_inv_cur - A.amount_due_remaining_inv_cur as Paid_invoice_curr_amount, (A.amount_due_original_inv_cur - A.amount_due_remaining_inv_cur) / A.amount_due_original_inv_cur as Remaining_invoice_curr_pct, A.amount_due_original_inv_cur - A.amount_due_original_inv_cur as Remaining_invoice_curr_check, A.bucket_current as bucket_current, A.bucket_past_due as bucket_past_due, A.bucket_total as bucket_total_usd, C.partner_common_name, case when A.product_code = '1000' then 'Books' when A.product_code = '1056' then 'KU' when A.product_code = '1100' then 'PRIME' when A.product_code = '1251' then 'AMU' when A.product_code = '1415' then 'PV' when A.product_code = '1420' then 'PV' when A.product_code = '6407' then 'AUD' when A.product_code = '1056' then 'AUD' when A.product_code = '6455' then 'PV' when A.product_code = '0000' then case when A.attribute_3 = '1000' then 'Books' when A.attribute_3 = '1056' then 'KU' when A.attribute_3 = '1100' then 'PRIME' when A.attribute_3 = '1251' then 'AMU' when A.attribute_3 = '1415' then 'PV' when A.attribute_3 = '1420' then 'PV' when A.attribute_3 = '6407' then 'AUD' when A.attribute_3 = '1056' then 'AUD' when A.attribute_3 = '6455' then 'PV' else 'Other' end else 'Other' end as Service_Type, B.Service_Period, B.Issue_Category, B.Root_Cause_Category, B.Recon_Phase, B.Next_Step_Owner, B.Recon_completion_ETA, B.Furos_Proj_Ref, B.Recon_PM, B.Short_Description from PHOENIX_SANDBOX.AR_AGING_MONTHLY_EMERGING_MAIN A LEFT JOIN ( SELECT * FROM PHOENIX_SANDBOX.ar_aging_category WHERE SNAPSHOT_DATE IN (SELECT MAX(SNAPSHOT_DATE) FROM PHOENIX_SANDBOX.ar_aging_category ) ) B ON A.customer_ofa_account_number = B.customer_ofa_account_number AND A.invoice_number = B.invoice_number LEFT JOIN (SELECT DISTINCT * FROM PHOENIX_SANDBOX.PARTNER_REFERENCE_TABLE) C ON A.CUSTOMER_NAME = C.OFA_NAME WHERE A.Snapshot_date = '2024-12-31' */
Leave a Comment