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 |
