AP Open Items Revaluation

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