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
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,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 order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.customer_party_id) when '&reporting_entity_col_name Summary' then sum(nvl(z.Debit,0)) over () + sum(nvl(z.bbf,0)) over () else null end cumulative_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) when 'Customer Site 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) when 'Operating Unit Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_id,z.organization_id order by z.seq rows between unbounded preceding and current row) when 'Customer Summary' then sum(nvl(z.Credit,0)) over (partition by z.customer_party_id order by z.seq rows between unbounded preceding and current row) when '&reporting_entity_col_name Summary' then sum(nvl(z.Credit,0)) over () else null end cumulative_credit from (select rownum seq, y.* from (select x.* from (-- dummy period summary record select distinct 'Period Summary' record_type, q_customer.customer_name, q_customer.customer_number, q_customer.customer_tax_ref_number customer_tax_registration, q_customer.organization_name operating_unit, q_customer.party_site_name customer_site_name, q_customer.party_site_number customer_site_number, q_customer.customer_address, q_main.gp_period_name period_name, null Document_Type, null Document_Number, to_date(null) Document_Date, to_date(null) Maturity_Date, null Document_Status, to_date(null) GL_Date, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 1 sort_order, q_main.gp_start_date period_start_date, q_main.gp_period_num period_num, q_customer.customer_party_id, q_customer.customer_party_site_id, q_customer.organization_id, to_number(null) bbf from q_customer, q_main where q_main.customer_party_id = q_customer.customer_party_id and q_main.customer_party_site_id = q_customer.customer_party_site_id union all -- dummy supplier site summary record select distinct 'Customer Site Summary' record_type, q_customer.customer_name, q_customer.customer_number, q_customer.customer_tax_ref_number customer_tax_registration, q_customer.organization_name operating_unit, q_customer.party_site_name customer_site_name, q_customer.party_site_number customer_site_number, q_customer.customer_address, null period_name, null Document_Type, null Document_Number, to_date(null) Document_Date, to_date(null) Maturity_Date, null Document_Status, to_date(null) GL_Date, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 2 sort_order, to_date(null) period_start_date, to_number(null) period_num, q_customer.customer_party_id, q_customer.customer_party_site_id, q_customer.organization_id, q_customer.brought_forward_amount bbf from q_customer union all -- dummy operating unit summary record select distinct 'Operating Unit Summary' Record_Type, q_customer.customer_name, q_customer.customer_number, q_customer.customer_tax_ref_number customer_tax_registration, q_customer.organization_name operating_unit, null customer_site_name, null customer_site_number, null customer_address, null period_name, null Document_Type, null Document_Number, to_date(null) Document_Date, to_date(null) Maturity_Date, null Document_Status, to_date(null) GL_Date, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 3 sort_order, to_date(null) period_start_date, to_number(null) period_num, q_customer.customer_party_id, to_number(null) customer_party_site_id, q_customer.organization_id, to_number(null) bbf from q_customer where :p_reporting_level != '3000' -- don't show OU Summary when run by OU union all -- dummy supplier summary record select distinct 'Customer Summary' record_type, q_customer.customer_name, q_customer.customer_number, q_customer.customer_tax_ref_number customer_tax_registration, null operating_unit, null customer_site_name, null customer_site_number, null customer_address, null period_name, null Document_Type, null Document_Number, to_date(null) Document_Date, to_date(null) Maturity_Date, null Document_Status, to_date(null) GL_Date, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 4 sort_order, to_date(null) period_start_date, to_number(null) period_num, q_customer.customer_party_id, to_number(null) customer_party_site_id, to_number(null) organization_id, to_number(null) bbf from q_customer union all -- dummy supplier summary record select distinct '&reporting_entity_col_name Summary' record_type, null customer_name, null customer_number, null customer_tax_registration, null operating_unit, null customer_site_name, null customer_site_number, null customer_address, null period_name, null Document_Type, null Document_Number, to_date(null) Document_Date, to_date(null) Maturity_Date, null Document_Status, to_date(null) GL_Date, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 5 sort_order, to_date(null) period_start_date, to_number(null) period_num, to_number(null) customer_party_id, to_number(null) customer_party_site_id, to_number(null) organization_id, to_number(null) bbf from dual union all -- transactions select 'Transaction' record_type, q_customer.customer_name, q_customer.customer_number, q_customer.customer_tax_ref_number customer_tax_registration, q_customer.organization_name operating_unit, q_customer.party_site_name customer_site_name, q_customer.party_site_number customer_site_number, q_customer.customer_address, q_main.gp_period_name period_name, q_main.transaction_type Document_Type, nvl(q_main.adjustment_number ,q_main.transaction_number) Document_Number, q_main.transaction_date Document_Date, q_main.transaction_due_date Maturity_Date, case when q_main.due_remaining = 0 then case q_main.trx_type when 'T' then 'Closed' when 'R' then 'Fully Applied' end else case q_main.trx_type when 'T' then 'Open' when 'R' then case when q_main.due_original - q_main.due_remaining < 0 then 'Partially Applied' else 'Not Applied' end else null end end ||case q_main.trx_status when 'CLEARED' then '/Cleared' when 'REVERSED' then '/Reversed' else null end ||case q_main.trx_type when 'A' then 'Approved' else null end Document_Status, q_main.gl_date GL_Date, case q_main.trx_type when 'R' then to_number(null) else q_main.accounted_amount end Debit, case q_main.trx_type when 'R' then case q_main.trx_status when 'REVERSED' then -1 * q_main.accounted_amount else q_main.accounted_amount end else to_number(null) end Credit, case q_main.trx_status when 'REVERSED' then -1 * q_main.entered_amount else q_main.entered_amount end Original_Amount, q_main.transaction_currency Currency_Code, -1 sort_order, q_main.gp_start_date period_start_date, q_main.gp_period_num period_num, q_customer.customer_party_id, q_customer.customer_party_site_id, q_customer.organization_id, to_number(null) bbf from q_customer, q_main where q_main.customer_party_id = q_customer.customer_party_id and q_main.customer_party_site_id = q_customer.customer_party_site_id ) x order by x.customer_name, x.customer_number, x.customer_party_id, x.operating_unit, x.customer_site_number, x.customer_party_site_id, nvl(x.period_start_date,to_date('31/12/4712','DD/MM/YYYY')), nvl(x.period_num,99), nvl(x.period_name,'####'), x.sort_order, x.gl_date, x.document_date) y) z order by z.seq) q where ( (:p_summary_only = 'N' and q.record_type = 'Transaction') or (:p_summary_level = 'Period' and q.record_type not in ('Transaction')) or (:p_summary_level = 'Customer Site' and q.record_type not in ('Period Summary','Transaction')) or (:p_summary_level = 'Operating Unit' and q.record_type not in ('Customer Site Summary','Period Summary','Transaction')) or (:p_summary_level = 'Customer' and q.record_type = 'Customer Summary') or (:p_reporting_level = '3000' and q.record_type = 'Operating_Unit Summary') or (:p_reporting_level = '1000' and q.record_type = 'Ledger Summary') ) order by q.seq |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Reporting Level |
|
LOV Oracle | |
Reporting Context |
|
LOV Oracle | |
GL Date From |
|
Date | |
GL Date To |
|
Date | |
Document Date From |
|
Date | |
Document Date To |
|
Date | |
Customer Name From |
|
LOV Oracle | |
Customer Name To |
|
LOV Oracle | |
Currency |
|
LOV Oracle | |
Customer Category |
|
LOV Oracle | |
Customer Class |
|
LOV Oracle | |
Include Incomplete Transactions |
|
LOV Oracle | |
Accounted Transactions |
|
LOV Oracle | |
Summarization Level |
|
LOV | |
Show Summary Only |
|
LOV Oracle |