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'
*/