AP Open Items Revaluation

➜ Back to Library
Description
Categories: BI Publisher, Enginatics
Repository: Github
Imported from BI Publisher
Description: Open Items Revaluation Report
Application: Payables
Source: Open Items Revaluation Report (XML)
Short Name: APOPITRN
DB package: AP_OPEN_ITEMS_REVAL_PKG
Run AP Open Items Revaluation and other Oracle EBS reports with Blitz Report™ on our demo environment
select
 trx.ledger
,trx.operating_unit
,trx.balancing_segment
,trx.account_segment
,trx.account              "Accounting Flexfield"
,trx.account_description  "Accouting Flexfield Desc."
&lp_report_format_cols
from
 (select 
   (select sob.name from gl_ledgers sob where sob.ledger_id = :g_ledger_id) ledger,
   (select haou.name from hr_all_organization_units haou where haou.organization_id = :p_org_id) operating_unit, 
   opit.balancing_segment     balancing_segment,
   opit.account_segment       account_segment,
   opit.code_combination_id,
   opit.account               account,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('account_description', 'SQLGL', 'GL#', :p_coa_id, null, opit.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') account_description,
   opit.party_id              party_id,
   opit.party_site_id         party_site_id,
   opit.vendor_id             vendor_id,
   opit.party_name            vendor_name,
   opit.vendor_number         vendor_number,
   opit.vendor_site_id        vendor_site_id,
   opit.vendor_site_code      vendor_site_code,
   opit.txn_id                txn_id,
   opit.txn_number            txn_number,
   opit.txn_type_lookup_code  txn_type_lookup_code,
   opit.txn_date              txn_date,
   opit.txn_currency_code     txn_currency_code,
   opit.payment_currency_code pmt_currency_code,
   round(opit.txn_base_exchange_rate, 5)         exchange_rate,
   opit.payment_cross_rate    payment_cross_rate,
   decode(opit.revaluation_rate,
          null, 'No Rate',
                round(opit.revaluation_rate, 5)
         )               revaluation_rate,
   opit.payment_status_flag   payment_status_flag,
   opit.entered_amount        entered_amount,
   opit.accounted_amount      accounted_amount,
   opit.open_entered_amount   open_entered_amount,
   opit.open_accounted_amount open_accounted_amount,
   nvl(to_char(round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision)), '*') revalued_amount_dsp,
   round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision) revalued_amount,
   case when opit.revaluation_rate is null or opit.open_accounted_amount > round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision)
   then opit.open_accounted_amount
   else round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision)
   end                   open_revalued_amount,
   ap_open_items_reval_pkg.get_due_date(opit.txn_id, opit.txn_type_lookup_code) due_date
  from
   (select  
     b.balancing_segment,
     b.account_segment,
     b.code_combination_id,
     b.account,
     b.party_id,
     b.party_site_id,
     b.party_name,
     b.vendor_id,
     b.vendor_number,
     b.vendor_site_id,
     b.vendor_site_code,
     b.txn_id,
     b.txn_number,
     b.txn_type_lookup_code,
     b.txn_date,
     b.txn_currency_code,
     b.payment_currency_code,
     b.txn_base_exchange_rate,
     b.payment_cross_rate,
     ap_open_items_reval_pkg.get_revaluation_rate(b.txn_currency_code,
     b.payment_cross_rate_type) revaluation_rate,
     b.payment_status_flag,
     b.entered_amount,
     b.accounted_amount,
     nvl(round((pay_cur_inv_entered_amt - payment_entered_amount)/b.payment_cross_rate, 2), b.entered_amount) open_entered_amount,
     nvl(round(round((pay_cur_inv_entered_amt - payment_entered_amount)/b.payment_cross_rate, 2) * b.txn_base_exchange_rate, :g_base_precision), b.accounted_amount) open_accounted_amount
    from
     (select /*+ leading (aoi aip) parallel(aoi)*/ distinct
       aoi.code_combination_id,
       aoi.party_id,
       aoi.party_site_id,
       aoi.vendor_id,
       aoi.vendor_number,
       aoi.vendor_site_id,
       aoi.txn_id invoice_id,
       aoi.txn_currency_code,
       aoi.payment_currency_code,
       aoi.txn_base_exchange_rate,
       aoi.payment_cross_rate,
       aoi.txn_type_lookup_code, --bug13613111
       round(aoi.entered_amount * aoi.payment_cross_rate, 2) pay_cur_inv_entered_amt,
       sum((nvl(aip.amount, 0) + nvl(aip.discount_taken, 0))) payment_entered_amount
      from 
       ap_open_items_reval_gt aoi,
       ap_invoice_payments_all aip,
       ap_checks_all ac
      where aip.invoice_id = aoi.txn_id
        and aip.set_of_books_id = :g_ledger_id
        and aip.org_id = :p_org_id
        and ac.check_id = aip.check_id
        and aip.accounting_date <= :g_revaluation_date
        and aoi.txn_type_lookup_code <> 'Payment'
        &lp_cleared_items_clause1
      group by 
       aoi.code_combination_id,
       aoi.party_id,
       aoi.party_site_id,
       aoi.vendor_id,
       aoi.vendor_number,
       aoi.vendor_site_id,
       aoi.txn_id ,
       aoi.txn_currency_code,
       aoi.payment_currency_code,
       aoi.txn_base_exchange_rate,
       aoi.payment_cross_rate,
       aoi.entered_amount,
       aoi.txn_type_lookup_code
     ) a, 
     ap_open_items_reval_gt b
    where b.txn_id = a.invoice_id(+)
      and b.txn_type_lookup_code = a.txn_type_lookup_code(+)
      and b.code_combination_id = a.code_combination_id(+)
      and nvl((a.pay_cur_inv_entered_amt - a.payment_entered_amount), b.entered_amount) <> 0
      and decode(nvl(sign(abs(a.pay_cur_inv_entered_amt - a.payment_entered_amount)-1), 1),-1, decode(nvl(b.payment_status_flag, 'N'),'Y', 0, 1), 1) <> 0 
      and :p_transfer_to_gl_only = 'N'
    union
    select /*+ parallal b */distinct
     b.balancing_segment,
     b.account_segment,
     b.code_combination_id,
     b.account,
     b.party_id,
     b.party_site_id,
     b.party_name,
     b.vendor_id,
     b.vendor_number,
     b.vendor_site_id,
     b.vendor_site_code,
     b.txn_id,
     b.txn_number,
     b.txn_type_lookup_code,
     b.txn_date,
     b.txn_currency_code,
     b.payment_currency_code,
     b.txn_base_exchange_rate,
     b.payment_cross_rate,
     ap_open_items_reval_pkg.get_revaluation_rate(b.txn_currency_code,
     b.payment_cross_rate_type) revaluation_rate,
     b.payment_status_flag,
     b.entered_amount,
     b.accounted_amount,
     b.entered_amount - sum(nvl(a.entered_amount, 0)) open_entered_amount,
     b.accounted_amount - sum(nvl(a.accounted_amount, 0)) open_accounted_amount
    from
     (select /*+ parallel(aoi) leading(aoi)*/ distinct
       200 application_id,
       null ref_ae_header_id,
       null temp_line_num,
       xah.ae_header_id,
       xal.ae_line_num,
       aoi.code_combination_id,
       aoi.account,
       aoi.txn_id invoice_id,
       aoi.txn_base_exchange_rate,
       aoi.txn_type_lookup_code,
       txn_amount invoice_amount,
       txn_base_amount invoice_base_amount,
       nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0) entered_amount,
       nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) accounted_amount
      from ap_open_items_reval_gt aoi,
       ap_checks_all ac,
       xla_transaction_entities xte,
       xla_ae_headers xah,
       xla_ae_lines xal
      where 1=1
        and ac.check_id in 
            (select bk.check_id
             from ap_invoice_payments_all bk
             where bk.invoice_id = aoi.txn_id
            )
        and nvl(xte.source_id_int_1, -99) = ac.check_id
        and nvl(xte.security_id_int_1, -99) = :p_org_id
        and xte.ledger_id = :g_ledger_id
        and xte.entity_code = 'AP_PAYMENTS'
        and xte.application_id = 200
        and xah.entity_id = xte.entity_id
        and xah.ledger_id = :g_ledger_id
        and xah.gl_transfer_status_code = 'Y'
        and xah.event_type_code <> 'MANUAL'
        and xah.application_id = 200
        and xah.accounting_date <= :g_revaluation_date
        and xah.upg_batch_id is not null
        and xal.ae_header_id = xah.ae_header_id
        and xal.code_combination_id = aoi.code_combination_id
        and xal.accounting_class_code= 'LIABILITY'
        and xal.application_id = 200
        and (     (    xal.source_table = 'AP_INVOICE_PAYMENTS'
                   and exists 
                      (select 1
                       from ap_invoice_payments_all aip
                       where aip.invoice_id = aoi.txn_id
                         and aip.invoice_payment_id = xal.source_id)
                  )
             or   (    xal.source_table = 'AP_INVOICES'
                   and xal.source_id = aoi.txn_id
                  )
             or   (    xal.source_table = 'AP_INVOICE_DISTRIBUTIONS'
                   and exists 
                       (select 1
                        from ap_invoice_distributions_all aid
                        where aid.invoice_id = aoi.txn_id
                          and aid.invoice_distribution_id = xal.source_id)
                  )
            )
        and aoi.txn_type_lookup_code <> 'Payment'
        and :p_transfer_to_gl_only = 'Y'
        &lp_cleared_items_clause1
      union
      select /*+ leading (aoi aip xte xah xal xdl) parallel(aoi)*/ distinct
       xdl.application_id,
       xdl.ref_ae_header_id,
       xdl.temp_line_num,
       xdl.ae_header_id,
       null ae_line_num,
       aoi.code_combination_id,
       aoi.account,
       aip.invoice_id,
       aoi.txn_base_exchange_rate,
       aoi.txn_type_lookup_code,
       txn_amount invoice_amount,
       txn_base_amount invoice_base_amount,
       nvl(xdl.unrounded_entered_dr, 0) - nvl(xdl.unrounded_entered_cr, 0) entered_amount,
       nvl(xdl.unrounded_accounted_dr, 0) - nvl(xdl.unrounded_accounted_cr, 0) accounted_amount
      from 
       ap_open_items_reval_gt aoi,
       ap_invoice_payments_all aip,
       ap_checks_all ac,
       xla_transaction_entities xte,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_distribution_links xdl
      where aip.invoice_id = aoi.txn_id
        and nvl(xte.source_id_int_1, -99) = aip.check_id
        and nvl(xte.security_id_int_1, -99) = :p_org_id
        and aip.set_of_books_id = :g_ledger_id
        and aip.org_id = :p_org_id
        and xte.ledger_id = :g_ledger_id
        and ac.check_id = aip.check_id
        and xah.entity_id = xte.entity_id
        and xah.ledger_id = :g_ledger_id
        and xah.gl_transfer_status_code = 'Y'
        and xah.event_type_code <> 'MANUAL'
        and xah.application_id = 200
        and xte.application_id = 200
        and xte.entity_code = 'AP_PAYMENTS'
        and xah.accounting_date <= :g_revaluation_date
        and xah.upg_batch_id is null
        and xal.ae_header_id = xah.ae_header_id
        and xdl.ae_header_id = xah.ae_header_id
        and xdl.ae_line_num = xal.ae_line_num
        and xal.code_combination_id = aoi.code_combination_id
        and xdl.applied_to_source_id_num_1 = aip.invoice_id
        and xal.accounting_class_code = 'LIABILITY'
        and xdl.applied_to_entity_code = 'AP_INVOICES'
        and xdl.application_id = 200
        and aoi.txn_type_lookup_code <> 'Payment'
        &lp_cleared_items_clause1
     ) a,
     ap_open_items_reval_gt b
    where b.txn_id = a.invoice_id(+)
      and b.txn_type_lookup_code = a.txn_type_lookup_code(+)
      and b.code_combination_id = a.code_combination_id(+)
      and :p_transfer_to_gl_only = 'Y'
    group by b.balancing_segment,
     b.account_segment,
     b.code_combination_id,
     b.account,
     b.party_id,
     b.party_site_id,
     b.party_name,
     b.vendor_id,
     b.vendor_number,
     b.vendor_site_id,
     b.vendor_site_code,
     b.txn_id,
     b.txn_number,
     b.txn_type_lookup_code,
     b.txn_date,
     b.txn_currency_code,
     b.payment_currency_code,
     b.txn_base_exchange_rate,
     b.payment_cross_rate,
     b.payment_cross_rate_type,
     b.payment_status_flag,
     b.entered_amount,
     b.accounted_amount
    having 
       b.entered_amount <> sum(nvl(a.entered_amount, 0))
    or b.accounted_amount <> sum(nvl(a.accounted_amount, 0)) 
   ) opit
  order by balancing_segment,
   account_segment,
   account,
   party_name,
   vendor_number,
   vendor_site_code,
   txn_number,
   txn_type_lookup_code,
   txn_date
 ) trx
&lp_group_by_cols
Parameter Name SQL text Validation
Operating Unit 
 
 LOV Oracle
Report Format 
,trx.vendor_name             "Trading Partner" 
,trx.vendor_number           "Supplier Number"
,trx.vendor_site_code        "Supplier Site"
,trx.txn_number              "Transaction Number"
,trx.txn_type_lookup_code    "Type"
,trx.txn_date                "Date"
,trx.due_date                "Due Date"
,trx.txn_currency_code       "Currency"
,trx.open_entered_amount     "Open Original Amount"
,trx.exchange_rate           "Exchange Rate"
,trx.open_accounted_amount   "Open Functional Amount (A)"
,trx.revaluation_rate        "Revaluation Rate"
,trx.revalued_amount         "Revalued Amount (B)"
,(nvl(trx.revalued_amount,0) - nvl(trx.open_accounted_amount,0)) "Diff (B-A)"
,trx.open_revalued_amount    "Open Revalued Amount (C)"
,(nvl(open_revalued_amount,0) - nvl(trx.open_accounted_amount,0)) "Diff (C-A)"
,nvl2(trx.revaluation_rate,null,'*') "No Revaluation Rate"
 LOV Oracle
Revaluation Period 
 
 LOV Oracle
Include Up to Due Date 
 
 Date
Rate Type 
 
 LOV Oracle
Daily Rate Type 
 
 LOV Oracle
Daily Rate Date 
 
 Date
Balancing Segment Low 
 
 LOV
Balancing Segment High 
 
 LOV
Transferred to GL only 
 
 LOV Oracle
Cleared only 
 
 LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: