ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EE9F3869E053B46B63588994Y 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' &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' and acrh.gl_date between :p_from_gl_date and :p_to_gl_date -- 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_brought_forward_debit, q.balance_brought_forward_credit, q.net_debit, q.net_credit, q.cumulative_debit, q.cumulative_credit, q.balance_brought_forward_credit - q.balance_brought_forward_debit balance_brought_forward_amount, q.net_credit - q.net_debit net_amount, q.cumulative_credit - q.cumulative_debit cumulative_amount, q.opening_balance, q.closing_balance &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_brought_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_brought_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, case z.record_type when 'Transaction' then sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) + nvl(sum(nvl(z.debit,0)-nvl(z.credit,0)) over (partition by z.customer_party_site_id order by z.seq rows between unbounded preceding and 1 preceding),0) when 'Period Summary' then 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 order by z.seq rows between unbounded preceding and current row) - sum(nvl(z.debit,0)) over (partition by z.customer_party_site_id,z.period_name) - 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 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 () end opening_balance, case z.record_type when 'Transaction' then sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) + sum(nvl(z.debit,0)-nvl(z.credit,0)) over (partition by z.customer_party_site_id order by z.seq rows between unbounded preceding and current row) when 'Period Summary' then sum(nvl(z.bbf,0)) over (partition by z.customer_party_site_id) + sum(nvl(z.debit,0)-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.bbf,0)) over (partition by z.customer_party_site_id) + sum(nvl(z.debit,0)-nvl(z.credit,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) + sum(nvl(z.debit,0)-nvl(z.credit,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) + sum(nvl(z.debit,0)-nvl(z.credit,0)) over (partition by z.customer_party_id) when '&reporting_entity_col_name Summary' then sum(nvl(z.bbf,0)) over () + sum(nvl(z.debit,0)-nvl(z.credit,0)) over () end closing_balance 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 customer 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 customer 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 reporting entity 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 Fixed duplicate join, and typos Added opening and closing balance columns AR ARSTMTRPT #,##0.00;[Red]-#,##0.00 AR AR كشف حساب العميل Application: المقبوضات Source: كشف حساب العميل Short Name: ARSTMTRPT DB package: AR_TP_STMT_PKG D AR Kundenauszug Application: Debitoren Source: Kundenauszug Short Name: ARSTMTRPT DB package: AR_TP_STMT_PKG F AR Relevé de client Application: Receivables Source: Relevé de client Short Name: ARSTMTRPT DB package: AR_TP_STMT_PKG US AR Customer Statement Application: Receivables Source: Customer Statement Short Name: ARSTMTRPT DB package: AR_TP_STMT_PKG ZHS AR 客户对帐单 Application: 应收帐款 Source: 客户对帐单 Short Name: ARSTMTRPT DB package: AR_TP_STMT_PKG Enginatics &document_columns &gc_accounted &gc_adj_accounted &gc_app_accounted &gc_currency &gc_cust_category &gc_cust_class &gc_customer_name &gc_incomplete_trx &gc_org_id &gc_rcpt_accounted &gc_rcpt_currency &gc_rcpt_org_id &gc_reporting_entity &lp_operating_unit_column &reporting_entity_col_name 1=1 2=2 3=3 4=4 5=5 :gc_accounted :gc_adj_accounted :gc_app_accounted :gc_currency :gc_cust_category :gc_cust_class :gc_customer_name :gc_incomplete_trx :gc_org_id :gc_rcpt_accounted :gc_rcpt_currency :gc_rcpt_org_id :gc_reporting_entity :p_accounted :p_currency :p_cust_category :p_cust_class :p_from_cust_name :p_from_doc_date :p_from_gl_date :p_incomplete_trx :p_reporting_entity_id :p_reporting_entity_name :p_reporting_level :p_summary_level :p_summary_only :p_to_cust_name :p_to_doc_date :p_to_gl_date 1 -10 :p_reporting_entity_name Char :$flex$.reporting_context.value AR اسم سياق التقرير D Kontextbezeichnung F Nom du contexte de rapport US Reporting Context Name ZHS 申报环境 - 名称 2 10 :p_reporting_level LOV Oracle FND_MO_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' and lookup_code in (1000,3000) order by value,description 1000 Y AR مستوى التقرير D Auswertungsebene F Niveau de rapport US Reporting Level ZHS 申报级别 3 &lp_operating_unit_column q.operating_unit, 1000 F Niveau de rapport US Reporting Level 4 &lp_operating_unit_column select null from dual 3000 F Niveau de rapport US Reporting Level 5 &reporting_entity_col_name select case :p_reporting_level when '1000' then 'Ledger' when '3000' then 'Operating_Unit' end from dual F Niveau de rapport US Reporting Level 6 20 :p_reporting_entity_id LOV Oracle FND_MO_REPORTING_ENTITY Y select entity_id id, entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.fnd_mo_reporting_level order by value,description select fmrev.entity_id from fnd_mo_reporting_entities_v fmrev where fmrev.reporting_level=:$flex$.fnd_mo_reporting_level and rownum=1 Y AR سياق التقرير D Auswertungskontext F Contexte du rapport US Reporting Context ZHS 申报环境 7 30 :p_from_gl_date Date trunc(sysdate,'MON') Y AR تاريخ الأستاذ العام من D Von Buchungsdatum F GL Date du US GL Date From ZHS GL 日期自 8 40 :p_to_gl_date Date last_day(:$flex$.Gl_Date_From) Y AR تاريخ الأستاذ العام إلى D Bis Buchungsdatum F GL Date à US GL Date To ZHS GL 日期至 9 50 :p_from_doc_date Date AR تاريخ المستند من D Von Belegdatum F Date du document US Document Date From ZHS 单据日期自 10 1=1 rct.trx_date >= trunc(:p_from_doc_date) F Date du document US Document Date From 11 2=2 acrh.trx_date >= trunc(:p_from_doc_date) F Date du document US Document Date From 12 3=3 acrh.trx_date >= trunc(:p_from_doc_date) F Date du document US Document Date From 13 4=4 aa.apply_date >= trunc(:p_from_doc_date) F Date du document US Document Date From 14 5=5 ara.apply_date >= trunc(:p_from_doc_date) F Date du document US Document Date From 15 60 :p_to_doc_date Date last_day(:$flex$.Document_Date_From) AR تاريخ المستند إلى D Bis Belegdatum F Date du document au US Document Date To ZHS 单据日期至 16 1=1 rct.trx_date < trunc(:p_to_doc_date)+1 F Date du document au US Document Date To 17 2=2 acrh.trx_date < trunc(:p_to_doc_date)+1 F Date du document au US Document Date To 18 3=3 acrh.trx_date < trunc(:p_to_doc_date)+1 F Date du document au US Document Date To 19 4=4 aa.apply_date < trunc(:p_to_doc_date)+1 F Date du document au US Document Date To 20 5=5 ara.apply_date < trunc(:p_to_doc_date)+1 F Date du document au US Document Date To 21 70 :p_from_cust_name LOV Oracle AR_CUSTOMER_NAME_WIDE Y Y select party.party_name id, party.party_name value, null description from hz_cust_accounts cu,hz_parties party where cu.party_id=party.party_id and exists (select cas.cust_account_id from hz_cust_acct_sites cas where cas.cust_account_id=cu.cust_account_id) order by value,description AR اسم العميل من D Von Kundenname F Nom du client De US Customer Name From ZHS 客户名称自 22 80 :p_to_cust_name LOV Oracle AR_CUSTOMER_NAME_WIDE Y Y select party.party_name id, party.party_name value, null description from hz_cust_accounts cu,hz_parties party where cu.party_id=party.party_id and exists (select cas.cust_account_id from hz_cust_acct_sites cas where cas.cust_account_id=cu.cust_account_id) order by value,description :$flex$.Customer_Name_From AR اسم العميل إلى D Bis Kundenname F Nom du client à US Customer Name To ZHS 客户名称至 23 90 :p_currency LOV Oracle AR_SRS_CURRENCY Y select currency_code id, currency_code value, name description from fnd_currencies_vl where currency_flag='Y' and enabled_flag in ('Y','N') order by currency_code AR العملة D Währung F Monnaie US Currency ZHS 币种 24 100 :p_cust_category LOV Oracle AR_BR_CUST_CATEGORY Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='CUSTOMER_CATEGORY' order by meaning AR فئة العميل D Kundenkategorie F Catégorie de client US Customer Category ZHS 客户类别 25 110 :p_cust_class LOV Oracle AR_BR_CUST_CLASS Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='CUSTOMER CLASS' order by meaning AR طبقة العميل D Kundenklasse F Classe de clients US Customer Class ZHS 客户分类 26 120 :p_incomplete_trx LOV Oracle AR_SRS_YES_NO Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by meaning N Y AR تضمين الحركات غير التامة D Mit unvollständigen Transaktionen F Inclure les transactions incomplètes US Include Incomplete Transactions ZHS 包括未完成的事务处理 27 130 :p_accounted LOV Oracle ZX_TRL_ACCOUNTED_STATUS Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='ZX_TRL_ACCOUNTED_STATUS' order by meaning BOTH Y AR الحركات المحتسبة D Verbuchte Transaktionen F Transactions comptabilisées US Accounted Transactions ZHS 已入帐的事务处理 28 140 :p_summary_level LOV custom Y select x.value, x.description from (select 'Period' value, 'Include Summary by Period' description from dual union all select 'Customer Site', 'Include Summary by Customer Site' from dual union all select 'Operating Unit', 'Include Summary by Customer/Operating Unit' from dual union all select 'Customer', 'Include Summary by Customer' from dual ) x order by x.value Customer Y F Niveau de résumé US Summarization Level 29 150 :p_summary_only LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y F Afficher le résumé uniquement US Show Summary Only 30 &document_columns , q.GL_Date , q.Debit Document_Debit , q.Credit Document_Credit , q.Document_Type , q.Document_Number , q.Document_Date , q.Maturity_Date , q.Document_Status , q.Original_Amount , q.Currency_Code N F Afficher le résumé uniquement US Show Summary Only 31 &document_columns select null from dual Y F Afficher le résumé uniquement US Show Summary Only