AR Customer Statement
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Receivables
Source: Customer Statement
Short Name: ARSTMTRPT
DB package: AR_TP_STMT_PKG
Source: Customer Statement
Short Name: ARSTMTRPT
DB package: AR_TP_STMT_PKG
with q_customer as (SELECT hca.cust_account_id customer_party_id, hcsu.site_use_id customer_party_site_id, AR_TP_STMT_PKG.balance_brought_forward(hca.cust_account_id,hcsu.site_use_id,hcas.org_id) brought_forward_amount, hca.account_number customer_number, hpar.party_name customer_name, hps.party_site_number party_site_number, hps.party_site_name party_site_name, hpar.address1||' '|| hpar.address2||' '|| hpar.address3||' '|| hpar.address4||' '|| hpar.city ||' '|| hpar.postal_code customer_address, hpar.jgzz_fiscal_code customer_tax_payer_id, NVL(zptp.rep_registration_number,hpar.tax_reference) customer_tax_ref_number, hou.name organization_name, hou.organization_id organization_id FROM hz_cust_accounts hca, hz_parties hpar, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_party_sites hps, hr_operating_units hou, zx_party_tax_profile zptp WHERE hca.party_id = hpar.party_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.party_site_id = hps.party_site_id AND hcsu.site_use_code = 'BILL_TO' AND hpar.party_id = hps.party_id AND hcas.org_id = hou.organization_id AND hpar.party_id = zptp.party_id(+) AND zptp.party_type_code(+) = 'THIRD_PARTY' AND hcas.org_id = hou.organization_id &gc_reporting_entity &gc_customer_name &gc_cust_category &gc_cust_class ), q_main as (SELECT 'T' trx_type, rctt.name transaction_type, NULL adjustment_number, rct.trx_number transaction_number, rctld.gl_date GL_Date, rct.trx_date transaction_date, apsa.due_date transaction_due_date, rct.invoice_currency_code transaction_currency, rctld.amount entered_amount, rctld.acctd_amount accounted_amount, gp.period_name gp_period_name, gp.period_num gp_period_num, gp.start_date gp_start_date, apsa.amount_due_original due_original, apsa.amount_due_remaining due_remaining, rct.status_trx trx_status, rct.bill_to_customer_id customer_party_id, rct.bill_to_site_use_id customer_party_site_id FROM ra_customer_trx rct, ar_payment_schedules_all apsa, ra_cust_trx_types_all rctt, ra_cust_trx_line_gl_dist_all rctld, gl_periods gp, gl_ledgers gled WHERE rct.customer_trx_id = apsa.customer_trx_id(+) AND rct.customer_trx_id = rctld.customer_trx_id AND rct.cust_trx_type_id = rctt.cust_trx_type_id AND rct.org_id = rctt.org_id AND gled.period_set_name = gp.period_set_name AND gled.accounted_period_type = gp.period_type AND gled.ledger_id = rct.set_of_books_id AND rctld.gl_date BETWEEN gp.start_date AND gp.end_date AND rctld.latest_rec_flag = 'Y' AND rctld.account_class = 'REC' AND rctt.post_to_gl = 'Y' -- Only Postable to GL are picked AND rctt.type IN ('CB','INV','DM','CM','BR','DEP') -- Guarantees are not picked AND rctld.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE AND gp.adjustment_period_flag = 'N' AND 1=1 &gc_org_id &gc_currency &gc_accounted &gc_incomplete_trx UNION ALL SELECT 'R' trx_type, arm.name transaction_type, NULL adjustment_number, acr.receipt_number transaction_number, acrh.gl_date GL_Date, acr.receipt_date transaction_date, apsa.due_date transaction_due_date, acr.currency_code transaction_currency, acr.amount entered_amount, acr.amount * NVL(acr.exchange_rate,1) accounted_amount, gp.period_name gp_period_name, gp.period_num gp_period_num, gp.start_date gp_start_date, apsa.amount_due_original due_original, apsa.amount_due_remaining due_remaining, acrh.status trx_status, acr.pay_from_customer customer_party_id, acr.customer_site_use_id customer_party_site_id FROM ar_cash_receipts acr, ar_receipt_methods arm, ar_cash_receipt_history_all acrh, ar_payment_schedules_all apsa, gl_periods gp, gl_ledgers gled WHERE acr.cash_receipt_id = apsa.cash_receipt_id(+) AND acr.org_id = apsa.org_id(+) AND acr.receipt_method_id = arm.receipt_method_id AND acr.cash_receipt_id = acrh.cash_receipt_id AND acr.org_id = acrh.org_id AND gled.period_set_name = gp.period_set_name AND gled.accounted_period_type = gp.period_type AND acr.set_of_books_id = gled.ledger_id AND acrh.gl_date BETWEEN gp.start_date AND gp.end_date AND acrh.first_posted_record_flag = 'Y' AND acrh.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE AND gp.adjustment_period_flag = 'N' AND 2=2 &gc_rcpt_org_id &gc_rcpt_currency &gc_rcpt_accounted UNION ALL SELECT 'R' trx_type, arm.name transaction_type, NULL adjustment_number, acr.receipt_number transaction_number, acrh.gl_date GL_Date, acr.receipt_date transaction_date, apsa.due_date transaction_due_date, acr.currency_code transaction_currency, acr.amount entered_amount, acr.amount * NVL(acr.exchange_rate,1) accounted_amount, gp.period_name gp_period_name, gp.period_num gp_period_num, gp.start_date gp_start_date, apsa.amount_due_original due_original, apsa.amount_due_remaining due_remaining, acrh.status trx_status, acr.pay_from_customer customer_party_id, acr.customer_site_use_id customer_party_site_id FROM ar_cash_receipts acr, ar_receipt_methods arm, ar_cash_receipt_history_all acrh, ar_payment_schedules_all apsa, gl_periods gp, gl_ledgers gled WHERE acr.cash_receipt_id = apsa.cash_receipt_id(+) AND acr.org_id = apsa.org_id(+) AND acr.receipt_method_id = arm.receipt_method_id AND acr.cash_receipt_id = acrh.cash_receipt_id AND acr.org_id = acrh.org_id AND gled.period_set_name = gp.period_set_name AND gled.accounted_period_type = gp.period_type AND acr.set_of_books_id = gled.ledger_id AND acrh.gl_date BETWEEN gp.start_date AND gp.end_date AND acr.reversal_date IS NOT NULL AND acrh.current_record_flag = 'Y' AND acrh.status = 'REVERSED' -- To Consider the first status for Reversed Receipts AND gp.adjustment_period_flag = 'N' AND 3=3 &gc_rcpt_org_id &gc_rcpt_currency &gc_rcpt_accounted UNION ALL SELECT 'A' trx_type, al.meaning transaction_type, aa.adjustment_number adjustment_number, rct.trx_number transaction_number, aa.gl_date GL_Date, aa.apply_date transaction_date, aa.apply_date transaction_due_date, rct.invoice_currency_code transaction_currency, aa.amount entered_amount, aa.acctd_amount accounted_amount, gp.period_name gp_period_name, gp.period_num gp_period_num, gp.start_date gp_start_date, apsa.amount_due_original due_original, apsa.amount_due_remaining due_remaining, aa.status trx_status, rct.bill_to_customer_id customer_party_id, rct.bill_to_site_use_id customer_party_site_id FROM ar_adjustments aa, ar_lookups al, ra_customer_trx_all rct, ra_cust_trx_types_all rctt, ar_payment_schedules_all apsa, gl_periods gp, gl_ledgers gled WHERE rct.customer_trx_id = apsa.customer_trx_id(+) AND rct.org_id = apsa.org_id(+) AND rct.cust_trx_type_id = rctt.cust_trx_type_id AND rct.org_id = rctt.org_id AND rct.customer_trx_id = aa.customer_trx_id AND rct.org_id = aa.org_id AND gled.period_set_name = gp.period_set_name AND gled.accounted_period_type = gp.period_type AND gled.ledger_id = aa.set_of_books_id AND al.lookup_type = 'ADJUSTMENT_TYPE' AND aa.status = 'A' -- For approved Adjustments AND aa.type = al.lookup_code AND aa.gl_date BETWEEN gp.start_date AND gp.end_date AND aa.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE AND gp.adjustment_period_flag = 'N' AND rctt.post_to_gl = 'Y' -- Only Postable to GL are picked AND rctt.type IN ('CB','INV','DM','CM','BR','DEP') -- Guarantees are not picked AND 4=4 &gc_org_id &gc_currency &gc_adj_accounted &gc_incomplete_trx UNION ALL SELECT 'R' trx_type, art.name transaction_type, NULL adjustment_number, acr.receipt_number transaction_number, ara.gl_date gl_date, ara.apply_date transaction_date, ara.apply_date transaction_due_date, acr.currency_code transaction_currency, -1*(ara.amount_applied) entered_amount, -1*(ara.acctd_amount_applied_from) accounted_amount, gp.period_name gp_period_name, gp.period_num gp_period_num, gp.start_date gp_start_date, 0 due_original, 0 due_remaining, art.status trx_status, acr.pay_from_customer customer_party_id, acr.customer_site_use_id customer_party_site_id FROM ar_cash_receipts acr, ar_receivable_applications_all ara, ar_receivables_trx_all art, gl_periods gp, gl_ledgers gled WHERE acr.cash_receipt_id = ara.cash_receipt_id AND acr.org_id = ara.org_id AND ara.receivables_trx_id = art.receivables_trx_id AND ara.org_id = art.org_id AND gled.period_set_name = gp.period_set_name AND gled.accounted_period_type = gp.period_type AND acr.set_of_books_id = gled.ledger_id AND ara.gl_date BETWEEN gp.start_date AND gp.end_date AND ara.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE AND art.type = 'WRITEOFF' --AND art.status = 'A' --Only Active Receipt WriteOffs AND gp.adjustment_period_flag = 'N' AND 5=5 &gc_rcpt_org_id &gc_rcpt_currency &gc_app_accounted ) -- -- Main Query Start Here -- select :p_reporting_entity_name &reporting_entity_col_name, q.customer_name, q.customer_number, q.customer_tax_registration, q.customer_site_name, q.customer_site_number, q.customer_address, &lp_operating_unit_column q.period_name, replace(q.Record_Type,'_',' ') record_type, q.balance_bought_forward_debit, q.balance_bought_forward_credit, q.net_debit, q.net_credit, q.cumulative_debit, q.cumulative_credit, q.balance_bought_forward_credit - q.balance_bought_forward_debit balance_bought_forward_amount, q.net_credit - q.net_debit net_amount, q.cumulative_credit - q.cumulative_debit cumualtive_amount &document_columns from (select z.*, case z.record_type when 'Period Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) - sum(nvl(z.Debit,0)) over (partition by z.customer_party_site_id,z.period_name) when 'Customer Site Summary' then sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) when 'Operating Unit Summary' then sum(nvl(z.bbf,0)) over (partition by z.customer_party_id,z.organization_id) when 'Customer Summary' then sum(nvl(z.bbf,0)) over (partition by z.customer_party_id) when '&reporting_entity_col_name Summary' then sum(nvl(z.bbf,0)) over () else null end balance_bought_forward_debit, case z.record_type when 'Period Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_site_id order by z.seq rows between unbounded preceding and current row) - sum(nvl(z.Credit,0)) over (partition by z.customer_party_site_id,z.period_name) when 'Customer Site Summary' then 0 when 'Operating Unit Summary' then 0 when 'Customer Summary' then 0 when '&reporting_entity_col_name Summary' then 0 else null end balance_bought_forward_credit, case z.record_type when 'Period Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_site_id,z.period_name) when 'Customer Site Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_site_id) when 'Operating Unit Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_id,z.organization_id) when 'Customer Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_id) when '&reporting_entity_col_name Summary' then sum(nvl(z.Debit,0)) over () else null end net_debit, case z.record_type when 'Period Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_site_id,z.period_name) when 'Customer Site Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_site_id) when 'Operating Unit Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_id,z.organization_id) when 'Customer Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_id) when '&reporting_entity_col_name Summary' then sum(nvl(z.Credit,0)) over () else null end net_credit, case z.record_type when 'Period Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) when 'Customer Site Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) when 'Operating Unit Summary' then sum(nvl(z.Debit,0)) over (partition by z.customer_party_id,z.organization_id order by z |