AR Open Items Revaluation

Description
Categories: BI Publisher, Enginatics
Repository: Github
Imported from BI Publisher
Description: Open Items Revaluation Report
Application: Receivables
Source: Open Items Revaluation Report (XML)
Short Name: ARXINREV_XML
DB package: AR_ARXINREV_XMLP_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

select
  trx.c_ledger         "Ledger"
, trx.c_balancing      "Balancing Segment"
, trx.c_account        "Account Segment"
, trx.c_flexdata       "Accounting Flexfield"
, trx.c_desc_all       "Accounting Flexfield Desc."
&lp_report_format_cols
from
( select
     ps.c_ccid
  ,  ps.c_balancing
  ,  ps.c_account
  ,  ps.c_flexdata
  ,  ps.c_cust_name
  ,  ps.c_cust_number
  ,  ps.c_cust_city
  ,  ps.c_trx_id
  ,  ps.c_pay_id
  ,  ps.c_trx_number
  ,  ps.c_inv_type
  ,  ps.c_inv_date
  ,  ps.c_due_date
  ,  ps.c_curr
  ,  ps.c_pay_amount
  ,  ps.c_exchange_rate
  ,  ps.c_type
  ,  ps.c_revaluate_yes_no
  ,  ps.c_inv_gl_posted_date
  ,  ps.c_previous_cust_trx_id
  ,  ps.c_trx_length
  ,  ps.c_ledger
  ,  ps.c_operating_unit
  ,  ps.c_desc_all
  --
  ,  ps.c_eop_rate
  ,  ps.c_receiptsformula
  ,  ps.c_adjustformula
  ,  ps.c_cmformula
  ,  ps.c_cm1formula
  --
  , (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) c_open_orig
  , case when ps.p_minimum_accountable_unit is null
    then round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate),ps.p_precision)
    else round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit
    end calc_open_func
  --
  , case when ps.p_minimum_accountable_unit is null
    then round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate),ps.p_precision)
    else round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit
    end calc_eop_amount
  --
--  , case
--    when ps.c_eop_rate is null
--    then to_number(null)
--    when ps.c_eop_rate < ps.c_exchange_rate
--    then (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate
--    else (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate
--    end calc_open_rev
  , case when ps.c_eop_rate is null
    then
      to_number(null)
    else
      case when ps.p_minimum_accountable_unit is null
      then round((((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) - ((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate)),ps.p_precision)
      else round((((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) - ((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0)  + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate)) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit
      end
    end calc_open_rev
  from
   (select
       dist.code_combination_id c_ccid
    ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') c_balancing
    ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') c_account
    ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('p_flexdata', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') c_flexdata
    ,  substrb(party.party_name,1,50) c_cust_name
    ,  cust.account_number c_cust_number
    ,  loc.city c_cust_city
    ,  trx.customer_trx_id c_trx_id
    ,  pay.payment_schedule_id c_pay_id
    ,  ltrim(rtrim(trx.trx_number)) c_trx_number
    ,  typ.name c_inv_type
    ,  trx.trx_date c_inv_date
    ,  pay.due_date c_due_date
    ,  pay.invoice_currency_code c_curr
    ,  pay.amount_due_original c_pay_amount
    ,  nvl(trx.exchange_rate,1.00) c_exchange_rate
    ,  typ.type c_type
    ,  typ.global_attribute1 c_revaluate_yes_no
    ,  dist.gl_posted_date c_inv_gl_posted_date
    ,  nvl(previous_customer_trx_id,0) c_previous_cust_trx_id
    ,  length(ltrim(rtrim(nvl(to_char(doc_sequence_value),trx.trx_number)))) c_trx_length
    ,  sob.name c_ledger
    ,  fc.precision p_precision
    ,  fc.minimum_accountable_unit p_minimum_accountable_unit
    ,  haouv.name c_operating_unit
    ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') c_desc_all
    --
    ,  case
       when pay.invoice_currency_code = sob.currency_code
       then 1
       when typ.type = 'DEP'
       then nvl (trx.exchange_rate,1)
       when :p_rate_type_lookup = 'PERIOD'
       then (select  /*+ push_pred */ decode(tr.eop_rate, 0,0, 1/tr.eop_rate)
             from gl_translation_rates tr
             where tr.set_of_books_id  = sob.set_of_books_id
             and tr.to_currency_code   = pay.invoice_currency_code
             and upper(tr.period_name) = upper(:p_revaluation_period)
             and tr.actual_flag        = 'A'
            )
       else case
            when gl_currency_api.get_rate_sql(pay.invoice_currency_code,sob.currency_code,:p_rate_date,:p_daily_rate_type) < 0
            then null
            else gl_currency_api.get_rate_sql(pay.invoice_currency_code,sob.currency_code,:p_rate_date,:p_daily_rate_type)
            end
       end c_eop_rate
    --
    ,  case when :p_cleared = 'N'
       then
         (select /*+ push_pred */
          sum(nvl(app2.amount_applied,0) + nvl(app2.earned_discount_taken,0) + nvl(app2.unearned_discount_taken,0)) receipt_amt
          from
          ar_receivable_applications app2
          where
              app2.applied_payment_schedule_id = pay.payment_schedule_id
          and app2.status = 'APP'
          and app2.gl_date <= gps.end_date
          and app2.application_type='CASH'
          and not exists
            (select 'reversed'
             from   ar_cash_receipt_history crh
             where  app2.cash_receipt_id = crh.cash_receipt_id
             and    crh.status = 'REVERSED'
             and    crh.gl_date <= gps.end_date
            )
         )
       else
         (select /*+ push_pred */
          sum(nvl(app2.amount_applied,0) + nvl(app2.earned_discount_taken,0) + nvl(app2.unearned_discount_taken,0)) receipt_amt
          from
          ar_receivable_applications app2
          where
              app2.applied_payment_schedule_id = pay.payment_schedule_id
          and app2.status = 'APP'
          and app2.gl_date <= gps.end_date
          and app2.application_type='CASH'
          and exists
            (select 'cleared cleared receipt'
             from   ar_cash_receipt_history crh
             where  app2.cash_receipt_id = crh.cash_receipt_id
             and    crh.status = 'CLEARED'
             and    crh.gl_date <= gps.end_date
             and    nvl(crh.reversal_gl_date,gps.end_date+1) > gps.end_date
            )
         )
       end c_receiptsformula
    --
    ,  (select /*+ push_pred */
        sum(adj2.amount) adjustment_amt
        from
        ar_adjustments adj2
        where
            adj2.payment_schedule_id = pay.payment_schedule_id
        and adj2.gl_date <= gps.end_date
        and adj2.status = 'A'
       ) c_adjustformula
    --
    ,  (select /*+ push_pred */
        sum(nvl(app2.amount_applied,0)) cm_amt
        from
        ar_receivable_applications app2
        where
            app2.applied_payment_schedule_id = pay.payment_schedule_id
        and app2.gl_date <= gps.end_date
        and app2.status ='APP'
        and app2.application_type = 'CM'
       ) c_cmformula
    --
    ,  (select /*+ push_pred */
        sum(nvl(app2.amount_applied,0)) cm_amt
        from
        ar_receivable_applications app2
        where
            app2.payment_schedule_id = pay.payment_schedule_id
        and app2.gl_date <= gps.end_date
        and (app2.status ='APP' or (app2.status='ACTIVITY' and app2.applied_payment_schedule_id = -8))
        and app2.application_type = 'CM'
       ) c_cm1formula
    from
      ra_cust_trx_types      typ
    , hz_cust_accounts       cust
    , hz_parties             party
    , hz_locations           loc
    , hz_cust_acct_sites     acct_site
    , hz_party_sites         party_site
    , hz_cust_site_uses      site
    , gl_code_combinations   cc
    , gl_sets_of_books       sob
    , fnd_currencies         fc
    , hr_all_organization_units_vl haouv
    , ra_customer_trx        trx
    , ar_xla_ctlgd_lines_v   dist
    , ar_payment_schedules   pay
    , (select /*+ push_pred */
       gps.set_of_books_id,gps.end_date
       from
       gl_period_statuses gps
       where
           gps.application_id = 222
       and gps.period_name = :p_revaluation_period
      ) gps
    where
        1=1
    and pay.gl_date                  <= gps.end_date
    and cust.cust_account_id       = pay.customer_id
    and cust.party_id              = party.party_id
    and trx.customer_trx_id        = pay.customer_trx_id
    and pay.customer_site_use_id   = site.site_use_id(+)
    and site.cust_acct_site_id     = acct_site.cust_acct_site_id(+)
    and acct_site.party_site_id    = party_site.party_site_id(+)
    and party_site.location_id     = loc.location_id(+)
    and dist.customer_trx_id       = trx.customer_trx_id
    and dist.account_class         = 'REC'
    and dist.latest_rec_flag       = 'Y'
    and dist.account_set_flag      = 'N'
    and trx.complete_flag          = 'Y'
    and trx.set_of_books_id        = sob.set_of_books_id
    and sob.currency_code          = fc.currency_code
    and trx.org_id                 = haouv.organization_id
    and typ.cust_trx_type_id       = pay.cust_trx_type_id
    and typ.org_id = pay.org_id
    and dist.code_combination_id   = cc.code_combination_id
    and dist.set_of_books_id       = sob.set_of_books_id
    and ar_arxinrev_xmlp_pkg.c_daily_rate_lookup_error_p
                                   = 'N'
    and gps.set_of_books_id        = sob.set_of_books_id
    and case when :p_rate_type_lookup = 'DAILY' and (:p_daily_rate_type is null or :p_rate_date is null)
        then 'Y' else 'N'
        end                        = 'N'
   ) ps
  order by
     c_ledger
  ,  c_balancing
  ,  c_account
  ,  c_flexdata
  ,  c_ccid
  ,  c_cust_name
  ,  c_cust_number
  ,  c_cust_number
  ,  c_trx_length
  ,  c_trx_number
  ,  c_inv_gl_posted_date
  ,  c_revaluate_yes_no
  ,  c_inv_type
  ,  c_inv_date
  ,  c_due_date
  ,  c_trx_id
  ,  c_exchange_rate
  ,  c_type
  ,  c_pay_id
  ,  c_curr
  ,  c_pay_amount
) trx
where
    trx.c_open_orig != 0
and (trx.c_type != 'CM' or (trx.c_type = 'CM' and trx.c_previous_cust_trx_id = 0))
&lp_group_by_cols
Parameter Name SQL text Validation
Ledger
sob.name=:ledger
LOV
Operating Unit
haouv.name=:operating_unit
LOV
Report Format
, trx.c_operating_unit "Operating Unit"
, trx.c_cust_name      "Customer Name"
, trx.c_cust_number    "Customer Number"
, trx.c_cust_city      "Customer City"
, trx.c_trx_number     "Trx. Number"
, trx.c_inv_type       "Trx. Type"
, trx.c_inv_date       "Trx. Date"
, trx.c_due_date       "Due Date"
, trx.c_curr           "Currency"
, trx.c_open_orig      "Open Original Amount"
, trx.c_exchange_rate  "Exchange Rate"
, trx.calc_open_func   "Open Functional Amount (A)"
, trx.c_eop_rate       "Revaluation Rate" 
, trx.calc_eop_amount  "Revaluation Open Amount (B)"
, nvl(trx.calc_eop_amount ,0) - trx.calc_open_func "Diff (B-A)"
, trx.calc_open_rev    "Open Revalued Adjustment"
, nvl2(trx.c_eop_rate,null,'Yes') "Missing Revaluation Rates"
LOV Oracle
Revaluation Period
 
LOV
Include Up To Due Date
pay.due_date <=:p_up_to_due_date
Date
Rate Type
 
LOV Oracle
Daily Rate Type
 
LOV Oracle
Daily Rate Date
 
Date
Balancing Segment Low
fnd_flex_xml_publisher_apis.process_kff_combination_1('bs', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id,'GL_BALANCING','N','VALUE') >= :p_bal_segment_low
LOV
Balancing Segment High
fnd_flex_xml_publisher_apis.process_kff_combination_1('bs', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id,'GL_BALANCING','N','VALUE') <= :p_bal_segment_high
LOV
Transferred To GL only
 
LOV Oracle
Cleared only
 
LOV Oracle
Download
   
Blitz Report™

Blitz Report™ provides multiple benefits: