AP Advances in Selected Currency

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payables
Source: Advances in Selected Currency
Short Name: APADVSELCUR
DB package: AP_BAL_PKG
Run AP Advances in Selected Currency and other Oracle EBS reports with Blitz Report™ on our demo environment
  SELECT rownum unq_ident    /*Bug 16613775*/
        ,ai.invoice_id                  prepay_id
        ,decode (
                 :format
                ,'EXCEL'
                ,'="' || TO_CHAR (ai.invoice_num) || '"'
                ,ai.invoice_num
                )                       invoice_number
        ,TO_CHAR(ai.gl_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK'))  gl_date
        ,ai.invoice_date                invoice_date
        ,ai.doc_sequence_value          doc_sequence_value
        ,ai.invoice_type_lookup_code    invoice_type
        ,pov.vendor_name                supplier_name
        ,pov.segment1                   supplier_code
        ,ai.payment_status_flag         paid_unpaid
        ,TO_CHAR(ai.earliest_settlement_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK'))  date_settled
        ,ai.invoice_currency_code       curr_code
        ,aid.accounting_date            accounting_date
        ,hou.organization_id            operating_unit_id
        ,hou.name                       organization_name
        ,NVL(xdl.unrounded_entered_dr,0) - NVL(xdl.unrounded_entered_cr,0)                      adv_amt_fr_curr
        ,NVL(xdl.unrounded_accounted_dr,0) - NVL(xdl.unrounded_accounted_cr,0)                  adv_amt_fn_curr
        ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_from_date)                             pre_amt_from_fr
        ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_from_date) * NVL(ai.exchange_rate,1)   pre_amt_from_fn
        ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_to_date)                               pre_amt_to_fr
        ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_to_date) * NVL(ai.exchange_rate,1)     pre_amt_to_fn
        /*Bug11655402 : Added decode below */
        ,DECODE(ai.payment_status_flag, 'Y', NVL(aid.prepay_amount_remaining,aid.amount), NVL(aid.prepay_amount_remaining,0))     bal_amt_fr_curr
        ,DECODE(ai.payment_status_flag, 'Y', NVL(aid.prepay_amount_remaining,aid.amount), NVL(aid.prepay_amount_remaining,0)) * NVL(ai.exchange_rate,1)      bal_amt_fn_curr
      /*,TO_CHAR(ac.check_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK'))  payment_date*/ /*Bug11791895*/
      ,AP_BAL_PKG.get_no_of_holds(ai.invoice_id)                           holdbacks
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_ff_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')                  acct_ff
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE')                      balancing_segment
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE')                 cost_center_segment
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('natural_acct_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')             natural_acct_segment
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_select_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION')                 balancing_desc
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'FULL_DESCRIPTION')            cost_center_desc
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('natural_acct_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')             natural_acct_desc
      ,ai.attribute3                    issuing_dept_code
      /*,NVL(avprpv.prepay_amount_applied,0)                           pre_amt_appl_fr_curr 
	  ,NVL(avprpv.prepay_amount_applied,0) * NVL(ai.exchange_rate,1) pre_amt_appl_fn_curr
      ,decode (
               :format
              ,'EXCEL'
              ,'="' || TO_CHAR (decode(aip.invoice_payment_type, 'PREPAY', ai.invoice_num, ac.check_number)) || '"'
              ,decode(aip.invoice_payment_type, 'PREPAY', ai.invoice_num, ac.check_number)
              )                                                                  int_num
      ,ac.check_id                                                               check_id*/ /*Bug11791895*/
      ,AP_BAL_PKG.get_settlement_date(AI.invoice_id,AIL.line_number)             settlement_date
      ,AP_BAL_PKG.description(gcc.segment4,'SEGMENT4')    desc_segment4
      ,AP_BAL_PKG.description(ai.attribute3,'ATTRIBUTE3') desc_attribute3
      ,gcc.segment1    segment1
      ,gcc.segment2    segment2
      ,gcc.segment3    segment3
      ,gcc.segment4    segment4
      ,gcc.segment5    segment5
      ,gcc.segment6    segment6
      ,gcc.segment7    segment7
      ,gcc.segment8    segment8
      ,gcc.segment9    segment9
      ,gcc.segment10   segment10
      ,gcc.segment11   segment11
      ,gcc.segment12   segment12
      ,gcc.segment13   segment13
      ,gcc.segment14   segment14
      ,gcc.segment15   segment15
      ,gcc.segment16   segment16
      ,gcc.segment17   segment17
      ,gcc.segment18   segment18
      ,gcc.segment19   segment19
      ,gcc.segment20   segment20
      ,gcc.segment21   segment21
      ,gcc.segment22   segment22
      ,gcc.segment23   segment23
      ,gcc.segment24   segment24
      ,gcc.segment25   segment25
      ,gcc.segment26   segment26
      ,gcc.segment27   segment27
      ,gcc.segment28   segment28
      ,gcc.segment29   segment29
      ,gcc.segment30   segment30
      ,xah.gl_transfer_status_code  gl_transfer_status_code
      ,gjh.status                   posting_status
FROM   ap_invoices                  ai
      ,ap_invoice_lines_all         ail
      ,ap_invoice_distributions_all aid
      ,xla_ae_headers               xah
      ,xla_ae_lines                 xal
      ,gl_code_combinations         gcc
      ,po_vendors                   pov
      ,xla_distribution_links       xdl
      ,hr_operating_units           hou
    /*,ap_invoice_payments_all     aip*/  /*Commenetd as code cleanup for Bug 16613775*/
    /*,ap_checks_all               ac */  /*Commenetd as code cleanup for Bug 16613775*/      
      ,gl_import_references         gir
      ,gl_je_headers                gjh
      /*Bug13389653 - Removed table avprpv */
      /*,(SELECT avprpv.org_id                org_id
              ,avprpv.prepay_id             prepay_id
              ,avprpv.prepay_line_number    prepay_line_number
              ,avprpv.prepay_amount_applied prepay_amount_applied
          FROM ap_view_prepays_fr_prepay_v  avprpv
         WHERE NVL(avprpv.accounting_date, :gd_from_date) <= :gd_to_date)  avprpv */
WHERE  ai.invoice_id                    = ail.invoice_id
  AND  ail.invoice_id                   = aid.invoice_id
  AND  ail.line_number                  = aid.invoice_line_number
  AND  xal.code_combination_id          = gcc.code_combination_id
  AND  ai.vendor_id                     = pov.vendor_id
  AND  ai.invoice_type_lookup_code      = 'PREPAYMENT'
  AND  ai.set_of_books_id               = xah.ledger_id
  --Bug8260360
  AND  aid.accounting_event_id          = xah.event_id
  AND  xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
  AND  xal.ae_header_id                 = xah.ae_header_id
  AND  xdl.ae_header_id                 = xal.ae_header_id
  AND  xdl.ae_line_num                  = xal.ae_line_num
  AND  hou.organization_id              = ai.org_id
  AND  xah.application_id               = 200
  AND  xal.application_id               = 200
  AND  xdl.application_id               = 200
   /* AND  xdl.rounding_class_code          <> 'LIABILITY'*/ /*Commented for bug 16733413*/
  AND xal.accounting_class_code           <> 'LIABILITY'   /*Added for bug 16733413*/
  AND  xdl.source_distribution_type     <> 'AP_PMT_DIST'
  --Used to confirm the Invoice is Accounted (Final)
  AND  xah.accounting_entry_status_code <> 'D'
  --Used to confirm the Invoice is Accounted
  AND  aid.accrual_posted_flag          = 'Y'
  --Used to Obtain the records with the Balance Type 'Actual'
  AND  xah.balance_type_code            = 'A'
  AND  NVL (aid.accounting_date, ai.gl_date) <= :gd_to_date
  AND  ai.cancelled_date IS NULL
  /*AND  ai.invoice_id                    = aip.invoice_id(+) */ /*Commented as code cleanup for Bug 16613775 Start*/
  /*AND  aip.check_id                     = ac.check_id(+)  
    AND  ac.void_date      IS NULL                               Bug 16613775 End */  
  /* Bug13389653 */
  /* AND  ail.invoice_id                   = avprpv.prepay_id(+)    
  AND  ail.line_number                  = avprpv.prepay_line_number(+) 
  AND  ail.org_id                       = avprpv.org_id(+) */
  /* End of Bug13389653 */
  AND  xal.gl_sl_link_id                = gir.gl_sl_link_id(+) 
  AND  xal.gl_sl_link_table             = gir.gl_sl_link_table(+)
  AND  gir.je_header_id                 = gjh.je_header_id(+)
  AND  xal.ledger_id                    = NVL(gjh.ledger_id,xal.ledger_id)
  AND  &flex_where_gl_account
  &gc_currency
  &gc_status
  &gc_org_where
  &gc_supplier
  &gc_where_clause
  &gc_additional_where
  ORDER BY gl_date
Parameter Name SQL text Validation
Operating Unit 
 
 LOV Oracle
As Of Date 
 
 Date
Currency Code 
 
 LOV Oracle
Account From 
 
 Char
Account To 
 
 Char
Supplier From 
 
 LOV Oracle
Supplier To 
 
 LOV Oracle
Paid Only 
 
 LOV Oracle
Posted Only 
 
 LOV Oracle
Download
 
Related Blitz Reports
AP Prepayment Balance Report - Functional Currency (CZech) AP Prepayment Balance Report - Entered Currency (CZech) AP Prepayment Balance GL Account Analysis GL Account Analysis (Distributions) CAC Invoice Price Variance JA India - Open Transactions Report for Prior-GST (XML Publisher) GL Account Analysis (Distributions) 11g AP Open Balances Revaluation (1)
Blitz Report™

Blitz Report™ provides multiple benefits: