AR Customer Statement

Description
Categories: Enginatics
Repository: Github
Application: Receivables
Source: Customer Statement
Short Name: ARSTMTRPT
DB package: AR_TP_STMT_PKG
Run AR Customer Statement and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
Blitz Report™

Blitz Report™ provides multiple benefits: