http://aplistqueries.blogspot.com/2016/02/aplist-querires.html
APLIST Querires
AP_SYSTEM_PARAMETERS_ALL
SELECT * FROM AP_SYSTEM_PARAMETERS_ALL WHERE NVL(org_id, -99) = '185'
FINANCIALS_SYSTEM_PARAMS_ALL
SELECT * FROM FINANCIALS_SYSTEM_PARAMS_ALL WHERE NVL(org_id, -99) = '185'
HR_OPERATING_UNITS
SELECT * FROM HR_OPERATING_UNITS WHERE organization_id = '185'
GL_LEDGERS
select distinct gl.* from ap_invoices_all ai, gl_ledgers gl, gl_ledger_relationships glr1, gl_ledger_relationships glr2, gl_ledger_relationships glr3 where glr3.source_ledger_id = ai.set_of_books_id and ai.invoice_id = '80105' and glr3.source_ledger_id = glr2.primary_ledger_id and glr2.target_ledger_id = glr1.primary_ledger_id and gl.ledger_id = glr1.source_ledger_id
GL_LEDGER_CONFIG_DETAILS
select distinct glcd2.* from ap_invoices_all ai, GL_LEDGER_CONFIG_DETAILS glcd1, GL_LEDGER_CONFIG_DETAILS glcd2 where ai.legal_entity_id = glcd1.object_id and glcd1.configuration_id = glcd2.configuration_id and glcd1.object_type_code = 'LEGAL_ENTITY' and ai.invoice_id = '80105' order by glcd2.object_id
GL_LEDGER_RELATIONSHIPS
select distinct glr1.* from ap_invoices_all ai, gl_ledgers gl, gl_ledger_relationships glr1, gl_ledger_relationships glr2, gl_ledger_relationships glr3 where glr3.source_ledger_id = ai.set_of_books_id and ai.invoice_id = '80105' and glr3.source_ledger_id = glr2.primary_ledger_id and glr2.target_ledger_id = glr1.primary_ledger_id and gl.ledger_id = glr1.source_ledger_id
GL_LEGAL_ENTITIES_BSVS
SELECT * FROM GL_LEGAL_ENTITIES_BSVS WHERE legal_entity_id IN ( select distinct glcd2.object_id from ap_invoices_all ai, GL_LEDGER_CONFIG_DETAILS glcd1, GL_LEDGER_CONFIG_DETAILS glcd2 where ai.legal_entity_id = glcd1.object_id and glcd1.configuration_id = glcd2.configuration_id and glcd1.object_type_code = 'LEGAL_ENTITY' and ai.invoice_id = '80105' and glcd1.object_type_code = glcd2.object_type_code)
XLE_ENTITY_PROFILES
SELECT * FROM XLE_ENTITY_PROFILES WHERE legal_entity_id IN ( select distinct glcd2.object_id from ap_invoices_all ai, GL_LEDGER_CONFIG_DETAILS glcd1, GL_LEDGER_CONFIG_DETAILS glcd2 where ai.legal_entity_id = glcd1.object_id and glcd1.configuration_id = glcd2.configuration_id and glcd1.object_type_code = 'LEGAL_ENTITY' and ai.invoice_id = '80105' and glcd1.object_type_code = glcd2.object_type_code )
FND_PRODUCT_INSTALLATIONS
SELECT * FROM FND_PRODUCT_GROUPS
FND_PRODUCT_GROUPS
SELECT * FROM FND_PRODUCT_GROUPS
-------------------------------------------------------------------------------------------------------------------------
Summary
AP_INVOICES_ALL Key Columns
SELECT ai.invoice_id, substr(ai.invoice_num,1,25) invoice_num, substr(aps.vendor_name,1,25) vendor_name, ai.invoice_date, ai.invoice_amount, ai.base_amount, substr(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code, substr(ai.invoice_currency_code,1,3) invoice_currency_code, substr(ai.payment_currency_code,1,3) payment_currency_code, ai.legal_entity_id, ai.org_id FROM AP_INVOICES_ALL ai, AP_SUPPLIERS aps, AP_SUPPLIER_SITES_ALL avs WHERE ai.invoice_id = '80105' AND ai.vendor_id = aps.vendor_id(+) AND ai.vendor_site_id = avs.vendor_site_id(+) ORDER BY ai.invoice_id asc
AP_INVOICE_LINES_ALL Key Columns
SELECT line_number, line_type_lookup_code, line_source,accounting_date, period_name, amount, summary_tax_line_id, deferred_acctg_flag, org_id FROM AP_INVOICE_LINES_ALL WHERE invoice_id = '80105' order by line_number asc
AP_INVOICE_DISTRIBUTIONS_ALL Key Columns
SELECT invoice_id, invoice_line_number, substr(distribution_line_number,1,8) distribution_line_number, substr(line_type_lookup_code,1,9) line_type_lookup_code, accounting_date, period_name, amount, base_amount, posted_flag, match_status_flag, encumbered_flag, historical_flag, substr(dist_code_combination_id,1,15) dist_code_combination_id, substr(accounting_event_id,1,15) accounting_event_id, substr(bc_event_id,1,15) bc_event_id, substr(invoice_distribution_id,1,15) invoice_distribution_id, substr(parent_reversal_id,1,15) parent_reversal_id, substr(po_distribution_id,1,15) po_distribution_id, summary_tax_line_id, detail_tax_dist_id, org_id FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id = '80105' ORDER BY invoice_line_number, distribution_line_number asc
AP_PAYMENT_SCHEDULES_ALL Key Columns
SELECT amount_remaining, batch_id, due_date, gross_amount, hold_flag, invoice_id, payment_num, substr(payment_status_flag,1,1) payment_status_flag, org_id FROM AP_PAYMENT_SCHEDULES_ALL WHERE invoice_id = '80105'
AP_HOLDS_ALL Key Columns
SELECT held_by, hold_date, hold_lookup_code, substr(hold_reason,1,25) hold_reason, invoice_id, release_lookup_code, substr(release_reason,1,25) release_reason, status_flag, org_id FROM AP_HOLDS_ALL WHERE invoice_id = '80105'
AP_DOCUMENTS_PAYABLE Key Columns
SELECT pay_proc_trxn_type_code, calling_app_doc_unique_ref1 check_id, calling_app_doc_unique_ref2 invoice_id, calling_app_doc_unique_ref4 invoice_payment_id, calling_app_doc_ref_number invoice_number, payment_function, payment_date, document_date, document_type, payment_currency_code, payment_amount, payment_method_code FROM AP_DOCUMENTS_PAYABLE WHERE calling_app_id = 200 AND calling_app_doc_unique_ref2 = '80105'
AP_INVOICE_PAYMENTS_ALL Key Columns
SELECT check_id, substr(invoice_payment_id,1,15) invoice_payment_id, amount, payment_base_amount, invoice_base_amount, accounting_date, period_name, posted_flag, accounting_event_id, invoice_id, org_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ORDER BY check_id asc
AP_CHECKS_ALL Key Columns
SELECT check_id, check_number, vendor_site_code, amount, base_amount, checkrun_id, checkrun_name, check_date, substr(status_lookup_code,1,15) status_lookup_code, void_date, org_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' )
AP_PAYMENT_HISTORY_ALL Key Columns
SELECT payment_history_id, check_id, accounting_date, substr(transaction_type,1,20) transaction_type, posted_flag, substr(accounting_event_id,1,10) accounting_event_id, rev_pmt_hist_id, org_id FROM AP_PAYMENT_HISTORY_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ORDER BY payment_history_id asc
AP_SELECTED_INVOICES_ALL Key Columns
SELECT checkrun_name, payment_num, payment_amount, ok_to_pay_flag, dont_pay_reason_code, substr(vendor_name,1,25) vendor_name, substr(vendor_site_code,1,25) vendor_site_code, pay_selected_check_id, print_selected_check_id, org_id FROM AP_SELECTED_INVOICES_ALL WHERE invoice_id = '80105'
AP_SELECTED_INVOICE_CHECKS_ALL Key Columns
SELECT asic.checkrun_name, asic.check_number, asic.check_amount, asic.ok_to_pay_flag, asic.dont_pay_reason_code, asic.status_lookup_code, substr(asic.vendor_name,1,25) vendor_name, substr(asic.vendor_site_code,1,25) vendor_site_code, asic.check_id, asic.selected_check_id, asic.org_id FROM AP_SELECTED_INVOICE_CHECKS_ALL asic, AP_SELECTED_INVOICES_ALL asi WHERE asi.invoice_id = '80105' AND ( asic.selected_check_id = asi.pay_selected_check_id OR asic.selected_check_id = asi.print_selected_check_id )
-------------------------------------------------------------------------------------------------------------------------
Invoice
AP_BATCHES_ALL
SELECT * FROM AP_BATCHES_ALL WHERE batch_id IN ( SELECT batch_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_INVOICES_ALL
SELECT distinct ai.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , ai.accts_pay_code_combination_id) "Accts Pay Account" FROM AP_INVOICES_ALL ai WHERE ai.invoice_id IN ( SELECT aip.invoice_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_INVOICE_PAYMENTS_ALL aip2 WHERE aip.check_id = aip2.check_id AND aip2.invoice_id = '80105' UNION SELECT '80105' FROM dual ) ORDER BY ai.invoice_id asc
AP_INVOICE_LINES
SELECT * FROM AP_INVOICE_LINES_ALL WHERE invoice_id = '80105'
AP_INVOICE_DISTRIBUTIONS_ALL
SELECT aid.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aid.accts_pay_code_combination_id) "Accts Pay Account", fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aid.dist_code_combination_id) "Dist Account" FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ORDER BY aid.invoice_id, aid.invoice_line_number, aid.distribution_line_number asc
ZX_LINES
SELECT * FROM ZX_LINES WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
ZX_LINES_SUMMARY
SELECT * FROM zx_lines_summary WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
ZX_REC_NREC_DIST
SELECT * FROM zx_rec_nrec_dist WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
ZX_LINES_DET_FACTORS
SELECT * FROM zx_lines_det_factors WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AP_PAYMENT_SCHEDULES_ALL
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE invoice_id = '80105'
AP_RECURRING_PAYMENTS_ALL
select distinct arp.* from ap_recurring_payments_all arp, ap_invoices_all ai where ai.recurring_payment_id = arp.recurring_payment_id and ai.invoice_id = '80105'
AP_HOLDS_ALL
SELECT * FROM AP_HOLDS_ALL WHERE invoice_id = '80105'
AP_HOLD_CODES
SELECT * FROM AP_HOLD_CODES WHERE hold_lookup_code IN ( SELECT hold_lookup_code FROM AP_HOLDS_ALL WHERE invoice_id = '80105' )
AP_TERMS
SELECT * FROM AP_TERMS WHERE term_id IN ( SELECT distinct terms_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_TERMS_LINES
SELECT * FROM AP_TERMS_LINES WHERE term_id IN ( SELECT distinct terms_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_INVOICE_PREPAYS_ALL
SELECT * FROM AP_INVOICE_PREPAYS_ALL WHERE invoice_id = '80105'
AP_CHRG_ALLOCATIONS_ALL
SELECT distinct aca.* FROM AP_CHRG_ALLOCATIONS_ALL aca, AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE ( aca.charge_dist_id = aid.invoice_distribution_id OR aca.item_dist_id = aid.invoice_distribution_id ) AND aid.invoice_id = '80105' ORDER BY aca.charge_dist_id
AP_INV_APRVL_HIST_ALL
SELECT * FROM AP_INV_APRVL_HIST_ALL WHERE invoice_id = '80105' ORDER BY 1
AP_ALLOCATION_RULES
SELECT * FROM AP_ALLOCATION_RULES WHERE invoice_id = '80105'
AP_ALLOCATION_RULE_LINES
SELECT * FROM AP_ALLOCATION_RULE_LINES WHERE invoice_id = '80105'
AP_SELF_ASSESSED_TAX_DIST_ALL
SELECT * FROM AP_SELF_ASSESSED_TAX_DIST_ALL WHERE invoice_id = '80105'
AP_INVOICE_RELATIONSHIPS
SELECT * FROM AP_INVOICE_RELATIONSHIPS WHERE original_invoice_id = '80105' OR related_invoice_id = '80105'
-------------------------------------------------------------------------------------------------------------------------
Payment
AP_DOCUMENTS_PAYABLE
SELECT * FROM AP_DOCUMENTS_PAYABLE WHERE calling_app_id = 200 AND calling_app_doc_unique_ref2 = '80105'
AP_INVOICE_PAYMENTS_ALL
SELECT distinct aip.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aip.accts_pay_code_combination_id) "Accts Pay Account" FROM AP_INVOICE_PAYMENTS_ALL aip, AP_INVOICE_PAYMENTS_ALL aip2 WHERE aip2.check_id = aip.check_id AND aip2.invoice_id = '80105' ORDER BY aip.check_id asc, aip.invoice_payment_id asc
AP_CHECKS_ALL
SELECT * FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' )
AP_PAYMENT_DISTRIBUTIONS_ALL
SELECT tab.* FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_DISTRIBUTIONS_ALL tab WHERE aip.invoice_payment_id = tab.invoice_payment_id AND aip.invoice_id = '80105'
AP_PAYMENT_HISTORY_ALL
SELECT * FROM AP_PAYMENT_HISTORY_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ORDER BY accounting_event_id asc
AP_PAYMENT_HIST_DISTS
SELECT distinct aphd.* FROM ap_payment_hist_dists aphd, AP_PAYMENT_HISTORY_ALL aph WHERE aph.payment_history_id = aphd.payment_history_id and aph.check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ORDER BY aphd.payment_history_id asc
AP_INV_SELECTION_CRITERIA_ALL
SELECT * FROM AP_INV_SELECTION_CRITERIA_ALL WHERE checkrun_name IN ( SELECT checkrun_name FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) )
AP_SELECTED_INVOICES_ALL
SELECT * FROM AP_SELECTED_INVOICES_ALL WHERE invoice_id = '80105'
AP_SELECTED_INVOICE_CHECKS_ALL
SELECT asic.* FROM AP_SELECTED_INVOICE_CHECKS_ALL asic, AP_SELECTED_INVOICES_ALL asi WHERE asi.invoice_id = '80105' AND ( asic.selected_check_id = asi.pay_selected_check_id OR asic.selected_check_id = print_selected_check_id )
IBY_DOCS_PAYABLE_ALL
SELECT idp.* FROM IBY_DOCS_PAYABLE_ALL idp WHERE idp.calling_app_id = 200 AND(calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2) IN (SELECT TO_CHAR(aps.checkrun_id) checkrun_id , TO_CHAR(aps.invoice_id) FROM ap_payment_schedules_all aps WHERE aps.invoice_id='80105' UNION SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id , TO_CHAR(aip.invoice_id) FROM ap_invoice_payments_all aip , ap_checks_all ac WHERE aip.invoice_id='80105' AND aip.check_id =ac.check_id)
IBY_PAYMENTS_ALL
SELECT * FROM IBY_PAYMENTS_ALL WHERE payment_id IN (SELECT idp.payment_id FROM IBY_DOCS_PAYABLE_ALL idp WHERE idp.calling_app_id =200 AND(calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2) IN (SELECT TO_CHAR(aps.checkrun_id) checkrun_id , TO_CHAR(aps.invoice_id) FROM ap_payment_schedules_all aps WHERE aps.invoice_id='80105' UNION SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id , TO_CHAR(aip.invoice_id) FROM ap_invoice_payments_all aip , ap_checks_all ac WHERE aip.invoice_id='80105' AND aip.check_id =ac.check_id))
IBY_PAY_INSTRUCTIONS_ALL
SELECT * FROM IBY_PAY_INSTRUCTIONS_ALL WHERE payment_instruction_id IN (SELECT ipa.payment_instruction_id FROM IBY_DOCS_PAYABLE_ALL idp , IBY_PAYMENTS_ALL ipa WHERE idp.calling_app_id =200 AND ipa.payment_id =idp.payment_id AND(calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2) IN (SELECT TO_CHAR(aps.checkrun_id) checkrun_id , TO_CHAR(aps.invoice_id) FROM ap_payment_schedules_all aps WHERE aps.invoice_id='80105' UNION SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id , TO_CHAR(aip.invoice_id) FROM ap_invoice_payments_all aip , ap_checks_all ac WHERE aip.invoice_id='80105' AND aip.check_id =ac.check_id))
AP_RECON_DISTRIBUTIONS_ALL
SELECT * FROM AP_RECON_DISTRIBUTIONS_ALL WHERE check_id IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' )
-------------------------------------------------------------------------------------------------------------------------
Withholding Tax
AP_AWT_TAX_RATES
select distinct awt.* from ap_tax_codes_all atc, ap_awt_tax_rates_all awt where awt.tax_name = atc.name and atc.tax_id in (select distinct aid.tax_code_id tax_code_id from ap_invoice_distributions_all aid, ap_tax_codes_all atc where aid.tax_code_id = atc.tax_id and aid.tax_code_id is not null and atc.tax_type = 'AWT' and aid.invoice_id = '80105') UNION select distinct awt.* from ap_awt_tax_rates_all awt where awt.tax_rate_id in (select distinct aid.awt_tax_rate_id from ap_invoice_distributions_all aid where aid.awt_tax_rate_id is not null and aid.invoice_id = '80105')
AP_AWT_GROUPS
select distinct awt.* from ap_awt_groups awt where awt.group_id in (select distinct aid.awt_group_id from ap_invoice_distributions_all aid where aid.awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct aid.pay_awt_group_id from ap_invoice_distributions_all aid where aid.pay_awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct ai.awt_group_id from ap_invoices_all ai where ai.awt_group_id is not null and ai.invoice_id = '80105' UNION select distinct ai.pay_awt_group_id from ap_invoices_all ai where ai.pay_awt_group_id is not null and ai.invoice_id = '80105')
AP_AWT_GROUP_TAXES_ALL
select distinct awt.* from AP_AWT_GROUP_TAXES_ALL awt where awt.group_id in (select distinct aid.awt_group_id from ap_invoice_distributions_all aid where aid.awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct aid.pay_awt_group_id from ap_invoice_distributions_all aid where aid.pay_awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct ai.awt_group_id from ap_invoices_all ai where ai.awt_group_id is not null and ai.invoice_id = '80105' UNION select distinct ai.pay_awt_group_id from ap_invoices_all ai where ai.pay_awt_group_id is not null and ai.invoice_id = '80105' )
AP_AWT_TEMP_DISTRIBUTIONS_ALL
select * FROM ap_awt_temp_distributions_all where invoice_id = '80105'
--------------------------------------------------------------------------------------------------------------------------
Accounting
XLA_EVENTS (Invoice)
SELECT DISTINCT xe.* FROM ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xe.application_id =200 AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.entity_id =xe.entity_id ORDER BY xe.entity_id , xe.event_number
SELECT DISTINCT xeh.* FROM xla_ae_headers xeh , ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xte.entity_id =xeh.entity_id AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id ORDER BY xeh.event_id , xeh.ae_header_id ASC
XLA_AE_LINES (Invoice)
SELECT DISTINCT xel.* , fnd_flex_ext.get_segs('SQLGL', 'GL#', :1, xel.code_combination_id) "Account" FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xel.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND ai.invoice_id ='80105' AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.entity_id =xeh.entity_id AND xte.ledger_id =ai.set_of_books_id ORDER BY xel.ae_header_id , xel.ae_line_num ASC
XLA_DISTRIBUTION_LINKS (Invoice)
SELECT DISTINCT xdl.* FROM xla_distribution_links xdl , xla_ae_headers xeh , ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xdl.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xdl.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.entity_id =xeh.entity_id ORDER BY xdl.event_id , xdl.ae_header_id , xdl.ae_line_num ASC
XLA_TRANSACTION_ENTITIES (Invoice)
SELECT DISTINCT xte.* FROM ap_invoices_all ai , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id
XLA_ACCOUNTING_ERRORS (Invoice)
SELECT DISTINCT xae.* FROM ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte , xla_accounting_errors xae WHERE xte.application_id =200 AND xe.application_id =200 AND xae.application_id =200 AND ai.invoice_id ='80105' AND xe.event_id =xae.event_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.ledger_id =ai.set_of_books_id AND xte.entity_id =xe.entity_id
XLA_EVENTS (Payment)
SELECT DISTINCT xe.* FROM ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.ledger_id =aip.set_of_books_id AND xte.entity_id =xe.entity_id ORDER BY xe.entity_id , xe.event_number
XLA_AE_HEADERS (Payment)
SELECT DISTINCT xeh.* FROM xla_ae_headers xeh , ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND aip.set_of_books_id =xte.ledger_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id ORDER BY xeh.event_id , xeh.ae_header_id ASC
XLA_AE_LINES (Payment)
SELECT DISTINCT xel.* , fnd_flex_ext.get_segs('SQLGL', 'GL#', :1, xel.code_combination_id) "Account" FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xel.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND aip.set_of_books_id =xte.ledger_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id ORDER BY xel.ae_header_id , xel.ae_line_num ASC
XLA_DISTRIBUTION_LINKS (Payment)
SELECT DISTINCT xdl.* FROM xla_distribution_links xdl , xla_ae_headers xeh , ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xdl.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND xte.ledger_id =aip.set_of_books_id AND xdl.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id ORDER BY xdl.event_id , xdl.ae_header_id , xdl.ae_line_num ASC
XLA_TRANSACTION_ENTITIES (Payment)
SELECT DISTINCT xte.* FROM ap_invoice_payments_all aip , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND aip.invoice_id ='80105' AND xte.entity_code ='AP_PAYMENTS' AND xte.ledger_id =aip.set_of_books_id AND NVL(xte.source_id_int_1,-99)=aip.check_id
XLA_ACCOUNTING_ERRORS (Payment)
SELECT DISTINCT xae.* FROM ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte , xla_accounting_errors xae WHERE xte.application_id =200 AND xe.application_id =200 AND xae.application_id =200 AND aip.invoice_id ='80105' AND xe.event_id =xae.event_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.ledger_id =aip.set_of_books_id AND xte.entity_id =xe.entity_id
--------------------------------------------------------------------------------------------------------------------------
Trial Balance
AP_LIABILITY_BALANCE
select * from ap_liability_balance where invoice_id = '80105' order by ae_header_id
XLA_TRIAL_BALANCES
SELECT * FROM xla_trial_balances xtb WHERE NVL(xtb.applied_to_entity_id, xtb.source_entity_id) IN (SELECT xte.entity_id FROM xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xte.ledger_id ='2024' AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)='80105') ORDER BY definition_code , ledger_id
--------------------------------------------------------------------------------------------------------------------------
General Ledger
GL_JE_BATCHES (Invoice)
SELECT distinct gjb.* FROM GL_IMPORT_REFERENCES gir, GL_JE_BATCHES gjb, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gjb.je_batch_id = gir.je_batch_id
GL_JE_HEADERS (Invoice)
SELECT distinct gjh.* FROM GL_IMPORT_REFERENCES gir, GL_JE_HEADERS gjh, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gjh.je_header_id = gir.je_header_id
GL_JE_LINES (Invoice)
SELECT distinct gll.* FROM GL_IMPORT_REFERENCES gir, GL_JE_LINES gll, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gll.je_header_id = gir.je_header_id AND gll.je_line_num = gir.je_line_num
GL_IMPORT_REFERENCES (Invoice)
SELECT distinct gir.* FROM GL_IMPORT_REFERENCES gir, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id
GL_JE_BATCHES (Payment)
SELECT distinct gjb.* FROM GL_IMPORT_REFERENCES gir, GL_JE_BATCHES gjb, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gjb.je_batch_id = gir.je_batch_id
GL_JE_HEADERS (Payment)
SELECT distinct gjh.* FROM GL_IMPORT_REFERENCES gir, GL_JE_HEADERS gjh, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 AND aea.event_id = aeh.event_id AND ael.application_id =200 AND aeh.ae_header_id = ael.ae_header_id AND gjh.je_header_id = gir.je_header_id
GL_JE_LINES (Payment)
SELECT distinct gll.* FROM GL_IMPORT_REFERENCES gir, GL_JE_LINES gll, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and ael.application_id = 200 and aeh.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gll.je_header_id = gir.je_header_id AND gll.je_line_num = gir.je_line_num
GL_IMPORT_REFERENCES (Payment)
SELECT distinct gir.* FROM GL_IMPORT_REFERENCES gir, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id
GL_PERIOD_STATUSES
SELECT gps.* FROM GL_PERIOD_STATUSES gps ,(SELECT period_name , set_of_books_id FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id='80105' UNION ALL SELECT period_name , set_of_books_id FROM ap_invoice_lines_all WHERE invoice_id='80105' UNION ALL SELECT period_name , set_of_books_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id='80105' ) atg_date WHERE atg_date.period_name=gps.period_name AND gps.application_id IN(101, 200) AND gps.ledger_id =atg_date.set_of_books_id UNION SELECT gps.* FROM GL_PERIOD_STATUSES gps , AP_PAYMENT_HISTORY_ALL aph , ap_invoice_payments_all aip WHERE aph.accounting_date BETWEEN gps.start_date AND gps.end_date AND aph.check_id =aip.check_id AND aip.invoice_id ='80105' AND gps.application_id IN(101, 200) AND gps.ledger_id =aip.set_of_books_id
GL_CODE_COMBINATIONS
SELECT DISTINCT gcc.* FROM gl_code_combinations gcc WHERE gcc.code_combination_id IN ( SELECT DISTINCT xel.code_combination_id FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoices_all ai , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xel.application_id =200 AND ai.invoice_id ='80105' AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.ledger_id =ai.set_of_books_id AND xte.entity_id =xeh.entity_id UNION ALL SELECT DISTINCT xel.code_combination_id FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoice_payments_all aip , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xel.application_id =200 AND xeh.application_id =200 AND aip.invoice_id ='80105' AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND xte.ledger_id =aip.set_of_books_id AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id UNION ALL SELECT DISTINCT po.code_combination_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid , PO_DISTRIBUTIONS_ALL po WHERE aid.invoice_id ='80105' AND aid.po_distribution_id IS NOT NULL AND po.po_distribution_id =aid.po_distribution_id )
--------------------------------------------------------------------------------------------------------------------------
Supplier
AP_SUPPLIERS
SELECT * FROM AP_SUPPLIERS WHERE vendor_id IN ( SELECT vendor_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_SUPPLIER_SITES_ALL
SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE vendor_site_id IN ( SELECT vendor_site_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
HZ_PARTIES
SELECT DISTINCT HZP.* FROM AP_INVOICES_ALL AI, HZ_PARTIES HZP WHERE AI.PARTY_ID = HZP.PARTY_ID AND AI.INVOICE_ID = '80105'
HZ_PARTY_SITES
SELECT DISTINCT HZPS.* FROM AP_INVOICES_ALL AI, HZ_PARTY_SITES HZPS WHERE AI.PARTY_ID = HZPS.PARTY_ID AND AI.PARTY_SITE_ID = HZPS.PARTY_SITE_ID AND AI.INVOICE_ID = '80105'
--------------------------------------------------------------------------------------------------------------------------
Purchasing
PO_HEADERS_ALL
SELECT distinct poh.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po, PO_HEADERS_ALL poh WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id AND poh.po_header_id = po.po_header_id
PO_DISTRIBUTIONS_ALL
SELECT distinct po.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id
PO_LINES_ALL
SELECT distinct pl.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po, PO_LINES_ALL pl WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id AND pl.po_header_id = po.po_header_id AND pl.po_line_id = po.po_line_id
PO_LINE_LOCATIONS_ALL
SELECT distinct pll.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po, PO_LINE_LOCATIONS_ALL pll WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id AND pll.po_header_id = po.po_header_id AND pll.po_line_id = po.po_line_id AND pll.line_location_id = po.line_location_id
--------------------------------------------------------------------------------------------------------------------------
Encumbrance
GL_BC_PACKETS
SELECT * FROM GL_BC_PACKETS WHERE je_source_name = 'Payables' AND reference2 = TO_CHAR('80105')
--------------------------------------------------------------------------------------------------------------------------
Prepayment
AP_PREPAY_HISTORY_ALL
SELECT aph.* FROM AP_PREPAY_HISTORY_ALL aph WHERE aph.invoice_id = '80105' OR aph.prepay_invoice_id = '80105'
AP_PREPAY_APP_DISTS
SELECT apad.* FROM AP_PREPAY_APP_DISTS apad, AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' AND apad.invoice_distribution_id = aid.invoice_distribution_id
Prepayment Invoices Applied to Invoice
SELECT pv.vendor_name "Vendor Name", ai.invoice_num "Invoice Num", ai.invoice_id "Invoice Id", ai.invoice_amount "Invoice amount", ail.line_number "Invoice Line Num", ai2.invoice_id "Prepay Invoice Id", ai2.invoice_num "Prepay Invoice Num", ail.prepay_line_number "Prepay Invoice Line Num", (-1)*(ail.amount - NVL(ail.included_tax_amount,0)) "Prepay Amount Applied", NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) + NVL(ail.total_nrec_tax_amount, 0)), 0) "Tax amount Applied" FROM AP_INVOICES_ALL ai, AP_INVOICES_ALL ai2, AP_INVOICE_LINES_ALL ail, AP_SUPPLIERS pv WHERE ai.invoice_id = ail.invoice_id AND ai2.invoice_id = ail.prepay_invoice_id AND ail.amount < 0 AND NVL(ail.discarded_flag,'N') <> 'Y' AND ail.line_type_lookup_code = 'PREPAY' AND ai.vendor_id = pv.vendor_id AND ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT') AND ai.invoice_id = '80105'
--------------------------------------------------------------------------------------------------------------------------
Payment Document and Bank
AP_BANK_BRANCHES
SELECT abb.* FROM AP_BANK_ACCOUNTS_ALL aba, AP_CHECK_STOCKS_ALL acs, AP_BANK_BRANCHES abb WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.bank_account_id = aba.bank_account_id AND abb.bank_branch_id = aba.bank_branch_id ORDER BY abb.bank_name
AP_BANK_ACCOUNTS_ALL
SELECT aba.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aba.asset_code_combination_id) "Asset Account", fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aba.cash_clearing_ccid) "Cash Clearing Account" FROM AP_BANK_ACCOUNTS_ALL aba, AP_CHECK_STOCKS_ALL acs, AP_BANK_BRANCHES abb WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.bank_account_id = aba.bank_account_id AND abb.bank_branch_id = aba.bank_branch_id ORDER BY aba.bank_account_name
AP_CHECK_STOCKS_ALL
SELECT acs.* FROM AP_BANK_ACCOUNTS_ALL aba, AP_CHECK_STOCKS_ALL acs, AP_BANK_BRANCHES abb WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.bank_account_id = aba.bank_account_id AND abb.bank_branch_id = aba.bank_branch_id ORDER BY acs.name
AP_CHECK_FORMATS
SELECT acf.* FROM AP_PAYMENT_PROGRAMS app, AP_CHECK_STOCKS_ALL acs, AP_CHECK_FORMATS acf, AP_PAYMENT_PROGRAMS app2, AP_PAYMENT_PROGRAMS app3 WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.check_format_id = acf.check_format_id AND app.program_id = acf.format_payments_program_id AND acf.build_payments_program_id = app2.program_id AND acf.remittance_advice_program_id = app3.program_id(+) ORDER BY acf.name
AP_PAYMENT_PROGRAMS
SELECT app.* FROM AP_PAYMENT_PROGRAMS app, AP_CHECK_STOCKS_ALL acs, AP_CHECK_FORMATS acf, AP_PAYMENT_PROGRAMS app2, AP_PAYMENT_PROGRAMS app3 WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.check_format_id = acf.check_format_id AND app.program_id = acf.format_payments_program_id AND acf.build_payments_program_id = app2.program_id AND acf.remittance_advice_program_id = app3.program_id(+) ORDER BY app.program_name
--------------------------------------------------------------------------------------------------------------------------
Cash Management
CE_STATEMENT_RECONCILS_ALL
SELECT distinct csr.* FROM CE_STATEMENT_LINES cel, CE_STATEMENT_HEADERS ceh, CE_STATEMENT_RECONCILS_ALL csr, AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac WHERE cel.statement_header_id = ceh.statement_header_id AND ac.check_id = aip.check_id AND aip.invoice_id = '80105' AND aip.check_id = ac.check_id AND ac.bank_account_id = ceh.bank_account_id AND to_char(ac.check_number) = cel.bank_trx_number AND csr.statement_line_id = cel.statement_line_id
CE_STATEMENT_HEADERS_ALL
SELECT distinct ceh.* FROM CE_STATEMENT_LINES cel, CE_STATEMENT_HEADERS ceh, CE_STATEMENT_RECONCILS_ALL csr, AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac WHERE cel.statement_header_id = ceh.statement_header_id AND ac.check_id = aip.check_id AND aip.invoice_id = '80105' AND aip.check_id = ac.check_id AND ac.bank_account_id = ceh.bank_account_id AND to_char(ac.check_number) = cel.bank_trx_number AND csr.statement_line_id = cel.statement_line_id
CE_STATEMENT_LINES_ALL
SELECT distinct cel.* FROM CE_STATEMENT_LINES cel, CE_STATEMENT_HEADERS ceh, CE_STATEMENT_RECONCILS_ALL csr, AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac WHERE cel.statement_header_id = ceh.statement_header_id AND ac.check_id = aip.check_id AND aip.invoice_id = '80105' AND aip.check_id = ac.check_id AND ac.bank_account_id = ceh.bank_account_id AND to_char(ac.check_number) = cel.bank_trx_number AND csr.statement_line_id = cel.statement_line_id
--------------------------------------------------------------------------------------------------------------------------
Expense Report Details
AP_EXPENSE_REPORT_HEADERS_ALL
select erh.* from ap_invoices_all ai, ap_expense_report_headers_all erh where ai.invoice_id = '80105' and ai.invoice_type_lookup_code = 'EXPENSE REPORT' and ai.invoice_id = erh.vouchno
AP_EXPENSE_REPORT_LINES_ALL
select erl.* from ap_invoices_all ai, ap_expense_report_headers_all erh, ap_expense_report_lines_all erl where ai.invoice_id = '80105' and ai.invoice_type_lookup_code = 'EXPENSE REPORT' and ai.invoice_id = erh.vouchno and erh.report_header_id = erl.report_header_id
AP_EXP_REPORT_DISTS_ALL
select erd.* from ap_invoices_all ai, ap_expense_report_headers_all erh, ap_exp_report_dists_all erd where ai.invoice_id = '80105' and ai.invoice_type_lookup_code = 'EXPENSE REPORT' and ai.invoice_id = erh.vouchno and erh.report_header_id = erd.report_header_id
--------------------------------------------------------------------------------------------------------------------------
Treasury Confirmation
FV_TREASURY_CONFIRMATIONS_ALL
SELECT distinct tc.* FROM AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac, FV_TREASURY_CONFIRMATIONS_ALL tc WHERE ac.checkrun_name = tc.checkrun_name AND ac.check_id = aip.check_id AND aip.invoice_id = '80105'
--------------------------------------------------------------------------------------------------------------------------
Currency
FND_CURRENCIES
SELECT * FROM FND_CURRENCIES WHERE currency_code IN ( SELECT a.invoice_currency_code FROM AP_INVOICES_ALL a WHERE a.invoice_id = '80105' UNION SELECT b.payment_currency_code FROM AP_INVOICES_ALL b WHERE b.invoice_id = '80105' UNION SELECT c.base_currency_code FROM AP_SYSTEM_PARAMETERS_ALL c WHERE nvl(c.org_id, -99) = '185' )
APLIST Querires
AP_SYSTEM_PARAMETERS_ALL
SELECT * FROM AP_SYSTEM_PARAMETERS_ALL WHERE NVL(org_id, -99) = '185'
FINANCIALS_SYSTEM_PARAMS_ALL
SELECT * FROM FINANCIALS_SYSTEM_PARAMS_ALL WHERE NVL(org_id, -99) = '185'
HR_OPERATING_UNITS
SELECT * FROM HR_OPERATING_UNITS WHERE organization_id = '185'
GL_LEDGERS
select distinct gl.* from ap_invoices_all ai, gl_ledgers gl, gl_ledger_relationships glr1, gl_ledger_relationships glr2, gl_ledger_relationships glr3 where glr3.source_ledger_id = ai.set_of_books_id and ai.invoice_id = '80105' and glr3.source_ledger_id = glr2.primary_ledger_id and glr2.target_ledger_id = glr1.primary_ledger_id and gl.ledger_id = glr1.source_ledger_id
GL_LEDGER_CONFIG_DETAILS
select distinct glcd2.* from ap_invoices_all ai, GL_LEDGER_CONFIG_DETAILS glcd1, GL_LEDGER_CONFIG_DETAILS glcd2 where ai.legal_entity_id = glcd1.object_id and glcd1.configuration_id = glcd2.configuration_id and glcd1.object_type_code = 'LEGAL_ENTITY' and ai.invoice_id = '80105' order by glcd2.object_id
GL_LEDGER_RELATIONSHIPS
select distinct glr1.* from ap_invoices_all ai, gl_ledgers gl, gl_ledger_relationships glr1, gl_ledger_relationships glr2, gl_ledger_relationships glr3 where glr3.source_ledger_id = ai.set_of_books_id and ai.invoice_id = '80105' and glr3.source_ledger_id = glr2.primary_ledger_id and glr2.target_ledger_id = glr1.primary_ledger_id and gl.ledger_id = glr1.source_ledger_id
GL_LEGAL_ENTITIES_BSVS
SELECT * FROM GL_LEGAL_ENTITIES_BSVS WHERE legal_entity_id IN ( select distinct glcd2.object_id from ap_invoices_all ai, GL_LEDGER_CONFIG_DETAILS glcd1, GL_LEDGER_CONFIG_DETAILS glcd2 where ai.legal_entity_id = glcd1.object_id and glcd1.configuration_id = glcd2.configuration_id and glcd1.object_type_code = 'LEGAL_ENTITY' and ai.invoice_id = '80105' and glcd1.object_type_code = glcd2.object_type_code)
XLE_ENTITY_PROFILES
SELECT * FROM XLE_ENTITY_PROFILES WHERE legal_entity_id IN ( select distinct glcd2.object_id from ap_invoices_all ai, GL_LEDGER_CONFIG_DETAILS glcd1, GL_LEDGER_CONFIG_DETAILS glcd2 where ai.legal_entity_id = glcd1.object_id and glcd1.configuration_id = glcd2.configuration_id and glcd1.object_type_code = 'LEGAL_ENTITY' and ai.invoice_id = '80105' and glcd1.object_type_code = glcd2.object_type_code )
FND_PRODUCT_INSTALLATIONS
SELECT * FROM FND_PRODUCT_GROUPS
FND_PRODUCT_GROUPS
SELECT * FROM FND_PRODUCT_GROUPS
-------------------------------------------------------------------------------------------------------------------------
Summary
AP_INVOICES_ALL Key Columns
SELECT ai.invoice_id, substr(ai.invoice_num,1,25) invoice_num, substr(aps.vendor_name,1,25) vendor_name, ai.invoice_date, ai.invoice_amount, ai.base_amount, substr(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code, substr(ai.invoice_currency_code,1,3) invoice_currency_code, substr(ai.payment_currency_code,1,3) payment_currency_code, ai.legal_entity_id, ai.org_id FROM AP_INVOICES_ALL ai, AP_SUPPLIERS aps, AP_SUPPLIER_SITES_ALL avs WHERE ai.invoice_id = '80105' AND ai.vendor_id = aps.vendor_id(+) AND ai.vendor_site_id = avs.vendor_site_id(+) ORDER BY ai.invoice_id asc
AP_INVOICE_LINES_ALL Key Columns
SELECT line_number, line_type_lookup_code, line_source,accounting_date, period_name, amount, summary_tax_line_id, deferred_acctg_flag, org_id FROM AP_INVOICE_LINES_ALL WHERE invoice_id = '80105' order by line_number asc
AP_INVOICE_DISTRIBUTIONS_ALL Key Columns
SELECT invoice_id, invoice_line_number, substr(distribution_line_number,1,8) distribution_line_number, substr(line_type_lookup_code,1,9) line_type_lookup_code, accounting_date, period_name, amount, base_amount, posted_flag, match_status_flag, encumbered_flag, historical_flag, substr(dist_code_combination_id,1,15) dist_code_combination_id, substr(accounting_event_id,1,15) accounting_event_id, substr(bc_event_id,1,15) bc_event_id, substr(invoice_distribution_id,1,15) invoice_distribution_id, substr(parent_reversal_id,1,15) parent_reversal_id, substr(po_distribution_id,1,15) po_distribution_id, summary_tax_line_id, detail_tax_dist_id, org_id FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id = '80105' ORDER BY invoice_line_number, distribution_line_number asc
AP_PAYMENT_SCHEDULES_ALL Key Columns
SELECT amount_remaining, batch_id, due_date, gross_amount, hold_flag, invoice_id, payment_num, substr(payment_status_flag,1,1) payment_status_flag, org_id FROM AP_PAYMENT_SCHEDULES_ALL WHERE invoice_id = '80105'
AP_HOLDS_ALL Key Columns
SELECT held_by, hold_date, hold_lookup_code, substr(hold_reason,1,25) hold_reason, invoice_id, release_lookup_code, substr(release_reason,1,25) release_reason, status_flag, org_id FROM AP_HOLDS_ALL WHERE invoice_id = '80105'
AP_DOCUMENTS_PAYABLE Key Columns
SELECT pay_proc_trxn_type_code, calling_app_doc_unique_ref1 check_id, calling_app_doc_unique_ref2 invoice_id, calling_app_doc_unique_ref4 invoice_payment_id, calling_app_doc_ref_number invoice_number, payment_function, payment_date, document_date, document_type, payment_currency_code, payment_amount, payment_method_code FROM AP_DOCUMENTS_PAYABLE WHERE calling_app_id = 200 AND calling_app_doc_unique_ref2 = '80105'
AP_INVOICE_PAYMENTS_ALL Key Columns
SELECT check_id, substr(invoice_payment_id,1,15) invoice_payment_id, amount, payment_base_amount, invoice_base_amount, accounting_date, period_name, posted_flag, accounting_event_id, invoice_id, org_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ORDER BY check_id asc
AP_CHECKS_ALL Key Columns
SELECT check_id, check_number, vendor_site_code, amount, base_amount, checkrun_id, checkrun_name, check_date, substr(status_lookup_code,1,15) status_lookup_code, void_date, org_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' )
AP_PAYMENT_HISTORY_ALL Key Columns
SELECT payment_history_id, check_id, accounting_date, substr(transaction_type,1,20) transaction_type, posted_flag, substr(accounting_event_id,1,10) accounting_event_id, rev_pmt_hist_id, org_id FROM AP_PAYMENT_HISTORY_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ORDER BY payment_history_id asc
AP_SELECTED_INVOICES_ALL Key Columns
SELECT checkrun_name, payment_num, payment_amount, ok_to_pay_flag, dont_pay_reason_code, substr(vendor_name,1,25) vendor_name, substr(vendor_site_code,1,25) vendor_site_code, pay_selected_check_id, print_selected_check_id, org_id FROM AP_SELECTED_INVOICES_ALL WHERE invoice_id = '80105'
AP_SELECTED_INVOICE_CHECKS_ALL Key Columns
SELECT asic.checkrun_name, asic.check_number, asic.check_amount, asic.ok_to_pay_flag, asic.dont_pay_reason_code, asic.status_lookup_code, substr(asic.vendor_name,1,25) vendor_name, substr(asic.vendor_site_code,1,25) vendor_site_code, asic.check_id, asic.selected_check_id, asic.org_id FROM AP_SELECTED_INVOICE_CHECKS_ALL asic, AP_SELECTED_INVOICES_ALL asi WHERE asi.invoice_id = '80105' AND ( asic.selected_check_id = asi.pay_selected_check_id OR asic.selected_check_id = asi.print_selected_check_id )
-------------------------------------------------------------------------------------------------------------------------
Invoice
AP_BATCHES_ALL
SELECT * FROM AP_BATCHES_ALL WHERE batch_id IN ( SELECT batch_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_INVOICES_ALL
SELECT distinct ai.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , ai.accts_pay_code_combination_id) "Accts Pay Account" FROM AP_INVOICES_ALL ai WHERE ai.invoice_id IN ( SELECT aip.invoice_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_INVOICE_PAYMENTS_ALL aip2 WHERE aip.check_id = aip2.check_id AND aip2.invoice_id = '80105' UNION SELECT '80105' FROM dual ) ORDER BY ai.invoice_id asc
AP_INVOICE_LINES
SELECT * FROM AP_INVOICE_LINES_ALL WHERE invoice_id = '80105'
AP_INVOICE_DISTRIBUTIONS_ALL
SELECT aid.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aid.accts_pay_code_combination_id) "Accts Pay Account", fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aid.dist_code_combination_id) "Dist Account" FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ORDER BY aid.invoice_id, aid.invoice_line_number, aid.distribution_line_number asc
ZX_LINES
SELECT * FROM ZX_LINES WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
ZX_LINES_SUMMARY
SELECT * FROM zx_lines_summary WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
ZX_REC_NREC_DIST
SELECT * FROM zx_rec_nrec_dist WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
ZX_LINES_DET_FACTORS
SELECT * FROM zx_lines_det_factors WHERE application_id = 200 AND entity_code = 'AP_INVOICES' AND trx_id = '80105' AND event_class_code in ('STANDARD INVOICES' , 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AP_PAYMENT_SCHEDULES_ALL
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE invoice_id = '80105'
AP_RECURRING_PAYMENTS_ALL
select distinct arp.* from ap_recurring_payments_all arp, ap_invoices_all ai where ai.recurring_payment_id = arp.recurring_payment_id and ai.invoice_id = '80105'
AP_HOLDS_ALL
SELECT * FROM AP_HOLDS_ALL WHERE invoice_id = '80105'
AP_HOLD_CODES
SELECT * FROM AP_HOLD_CODES WHERE hold_lookup_code IN ( SELECT hold_lookup_code FROM AP_HOLDS_ALL WHERE invoice_id = '80105' )
AP_TERMS
SELECT * FROM AP_TERMS WHERE term_id IN ( SELECT distinct terms_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_TERMS_LINES
SELECT * FROM AP_TERMS_LINES WHERE term_id IN ( SELECT distinct terms_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_INVOICE_PREPAYS_ALL
SELECT * FROM AP_INVOICE_PREPAYS_ALL WHERE invoice_id = '80105'
AP_CHRG_ALLOCATIONS_ALL
SELECT distinct aca.* FROM AP_CHRG_ALLOCATIONS_ALL aca, AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE ( aca.charge_dist_id = aid.invoice_distribution_id OR aca.item_dist_id = aid.invoice_distribution_id ) AND aid.invoice_id = '80105' ORDER BY aca.charge_dist_id
AP_INV_APRVL_HIST_ALL
SELECT * FROM AP_INV_APRVL_HIST_ALL WHERE invoice_id = '80105' ORDER BY 1
AP_ALLOCATION_RULES
SELECT * FROM AP_ALLOCATION_RULES WHERE invoice_id = '80105'
AP_ALLOCATION_RULE_LINES
SELECT * FROM AP_ALLOCATION_RULE_LINES WHERE invoice_id = '80105'
AP_SELF_ASSESSED_TAX_DIST_ALL
SELECT * FROM AP_SELF_ASSESSED_TAX_DIST_ALL WHERE invoice_id = '80105'
AP_INVOICE_RELATIONSHIPS
SELECT * FROM AP_INVOICE_RELATIONSHIPS WHERE original_invoice_id = '80105' OR related_invoice_id = '80105'
-------------------------------------------------------------------------------------------------------------------------
Payment
AP_DOCUMENTS_PAYABLE
SELECT * FROM AP_DOCUMENTS_PAYABLE WHERE calling_app_id = 200 AND calling_app_doc_unique_ref2 = '80105'
AP_INVOICE_PAYMENTS_ALL
SELECT distinct aip.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aip.accts_pay_code_combination_id) "Accts Pay Account" FROM AP_INVOICE_PAYMENTS_ALL aip, AP_INVOICE_PAYMENTS_ALL aip2 WHERE aip2.check_id = aip.check_id AND aip2.invoice_id = '80105' ORDER BY aip.check_id asc, aip.invoice_payment_id asc
AP_CHECKS_ALL
SELECT * FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' )
AP_PAYMENT_DISTRIBUTIONS_ALL
SELECT tab.* FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_DISTRIBUTIONS_ALL tab WHERE aip.invoice_payment_id = tab.invoice_payment_id AND aip.invoice_id = '80105'
AP_PAYMENT_HISTORY_ALL
SELECT * FROM AP_PAYMENT_HISTORY_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ORDER BY accounting_event_id asc
AP_PAYMENT_HIST_DISTS
SELECT distinct aphd.* FROM ap_payment_hist_dists aphd, AP_PAYMENT_HISTORY_ALL aph WHERE aph.payment_history_id = aphd.payment_history_id and aph.check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ORDER BY aphd.payment_history_id asc
AP_INV_SELECTION_CRITERIA_ALL
SELECT * FROM AP_INV_SELECTION_CRITERIA_ALL WHERE checkrun_name IN ( SELECT checkrun_name FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) )
AP_SELECTED_INVOICES_ALL
SELECT * FROM AP_SELECTED_INVOICES_ALL WHERE invoice_id = '80105'
AP_SELECTED_INVOICE_CHECKS_ALL
SELECT asic.* FROM AP_SELECTED_INVOICE_CHECKS_ALL asic, AP_SELECTED_INVOICES_ALL asi WHERE asi.invoice_id = '80105' AND ( asic.selected_check_id = asi.pay_selected_check_id OR asic.selected_check_id = print_selected_check_id )
IBY_DOCS_PAYABLE_ALL
SELECT idp.* FROM IBY_DOCS_PAYABLE_ALL idp WHERE idp.calling_app_id = 200 AND(calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2) IN (SELECT TO_CHAR(aps.checkrun_id) checkrun_id , TO_CHAR(aps.invoice_id) FROM ap_payment_schedules_all aps WHERE aps.invoice_id='80105' UNION SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id , TO_CHAR(aip.invoice_id) FROM ap_invoice_payments_all aip , ap_checks_all ac WHERE aip.invoice_id='80105' AND aip.check_id =ac.check_id)
IBY_PAYMENTS_ALL
SELECT * FROM IBY_PAYMENTS_ALL WHERE payment_id IN (SELECT idp.payment_id FROM IBY_DOCS_PAYABLE_ALL idp WHERE idp.calling_app_id =200 AND(calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2) IN (SELECT TO_CHAR(aps.checkrun_id) checkrun_id , TO_CHAR(aps.invoice_id) FROM ap_payment_schedules_all aps WHERE aps.invoice_id='80105' UNION SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id , TO_CHAR(aip.invoice_id) FROM ap_invoice_payments_all aip , ap_checks_all ac WHERE aip.invoice_id='80105' AND aip.check_id =ac.check_id))
IBY_PAY_INSTRUCTIONS_ALL
SELECT * FROM IBY_PAY_INSTRUCTIONS_ALL WHERE payment_instruction_id IN (SELECT ipa.payment_instruction_id FROM IBY_DOCS_PAYABLE_ALL idp , IBY_PAYMENTS_ALL ipa WHERE idp.calling_app_id =200 AND ipa.payment_id =idp.payment_id AND(calling_app_doc_unique_ref1 , calling_app_doc_unique_ref2) IN (SELECT TO_CHAR(aps.checkrun_id) checkrun_id , TO_CHAR(aps.invoice_id) FROM ap_payment_schedules_all aps WHERE aps.invoice_id='80105' UNION SELECT TO_CHAR(NVL(ac.checkrun_id, ac.check_id)) checkrun_id , TO_CHAR(aip.invoice_id) FROM ap_invoice_payments_all aip , ap_checks_all ac WHERE aip.invoice_id='80105' AND aip.check_id =ac.check_id))
AP_RECON_DISTRIBUTIONS_ALL
SELECT * FROM AP_RECON_DISTRIBUTIONS_ALL WHERE check_id IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' )
-------------------------------------------------------------------------------------------------------------------------
Withholding Tax
AP_AWT_TAX_RATES
select distinct awt.* from ap_tax_codes_all atc, ap_awt_tax_rates_all awt where awt.tax_name = atc.name and atc.tax_id in (select distinct aid.tax_code_id tax_code_id from ap_invoice_distributions_all aid, ap_tax_codes_all atc where aid.tax_code_id = atc.tax_id and aid.tax_code_id is not null and atc.tax_type = 'AWT' and aid.invoice_id = '80105') UNION select distinct awt.* from ap_awt_tax_rates_all awt where awt.tax_rate_id in (select distinct aid.awt_tax_rate_id from ap_invoice_distributions_all aid where aid.awt_tax_rate_id is not null and aid.invoice_id = '80105')
AP_AWT_GROUPS
select distinct awt.* from ap_awt_groups awt where awt.group_id in (select distinct aid.awt_group_id from ap_invoice_distributions_all aid where aid.awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct aid.pay_awt_group_id from ap_invoice_distributions_all aid where aid.pay_awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct ai.awt_group_id from ap_invoices_all ai where ai.awt_group_id is not null and ai.invoice_id = '80105' UNION select distinct ai.pay_awt_group_id from ap_invoices_all ai where ai.pay_awt_group_id is not null and ai.invoice_id = '80105')
AP_AWT_GROUP_TAXES_ALL
select distinct awt.* from AP_AWT_GROUP_TAXES_ALL awt where awt.group_id in (select distinct aid.awt_group_id from ap_invoice_distributions_all aid where aid.awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct aid.pay_awt_group_id from ap_invoice_distributions_all aid where aid.pay_awt_group_id is not null and aid.invoice_id = '80105' UNION select distinct ai.awt_group_id from ap_invoices_all ai where ai.awt_group_id is not null and ai.invoice_id = '80105' UNION select distinct ai.pay_awt_group_id from ap_invoices_all ai where ai.pay_awt_group_id is not null and ai.invoice_id = '80105' )
AP_AWT_TEMP_DISTRIBUTIONS_ALL
select * FROM ap_awt_temp_distributions_all where invoice_id = '80105'
--------------------------------------------------------------------------------------------------------------------------
Accounting
XLA_EVENTS (Invoice)
SELECT DISTINCT xe.* FROM ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xe.application_id =200 AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.entity_id =xe.entity_id ORDER BY xe.entity_id , xe.event_number
SELECT DISTINCT xeh.* FROM xla_ae_headers xeh , ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xte.entity_id =xeh.entity_id AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id ORDER BY xeh.event_id , xeh.ae_header_id ASC
XLA_AE_LINES (Invoice)
SELECT DISTINCT xel.* , fnd_flex_ext.get_segs('SQLGL', 'GL#', :1, xel.code_combination_id) "Account" FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xel.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND ai.invoice_id ='80105' AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.entity_id =xeh.entity_id AND xte.ledger_id =ai.set_of_books_id ORDER BY xel.ae_header_id , xel.ae_line_num ASC
XLA_DISTRIBUTION_LINKS (Invoice)
SELECT DISTINCT xdl.* FROM xla_distribution_links xdl , xla_ae_headers xeh , ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xdl.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xdl.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.entity_id =xeh.entity_id ORDER BY xdl.event_id , xdl.ae_header_id , xdl.ae_line_num ASC
XLA_TRANSACTION_ENTITIES (Invoice)
SELECT DISTINCT xte.* FROM ap_invoices_all ai , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND ai.invoice_id ='80105' AND xte.ledger_id =ai.set_of_books_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id
XLA_ACCOUNTING_ERRORS (Invoice)
SELECT DISTINCT xae.* FROM ap_invoices_all ai , xla_events xe , xla_transaction_entities_upg xte , xla_accounting_errors xae WHERE xte.application_id =200 AND xe.application_id =200 AND xae.application_id =200 AND ai.invoice_id ='80105' AND xe.event_id =xae.event_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.ledger_id =ai.set_of_books_id AND xte.entity_id =xe.entity_id
XLA_EVENTS (Payment)
SELECT DISTINCT xe.* FROM ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.ledger_id =aip.set_of_books_id AND xte.entity_id =xe.entity_id ORDER BY xe.entity_id , xe.event_number
XLA_AE_HEADERS (Payment)
SELECT DISTINCT xeh.* FROM xla_ae_headers xeh , ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND aip.set_of_books_id =xte.ledger_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id ORDER BY xeh.event_id , xeh.ae_header_id ASC
XLA_AE_LINES (Payment)
SELECT DISTINCT xel.* , fnd_flex_ext.get_segs('SQLGL', 'GL#', :1, xel.code_combination_id) "Account" FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xel.application_id =200 AND xeh.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND aip.set_of_books_id =xte.ledger_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id ORDER BY xel.ae_header_id , xel.ae_line_num ASC
XLA_DISTRIBUTION_LINKS (Payment)
SELECT DISTINCT xdl.* FROM xla_distribution_links xdl , xla_ae_headers xeh , ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xdl.application_id =200 AND xe.application_id =200 AND aip.invoice_id ='80105' AND xte.ledger_id =aip.set_of_books_id AND xdl.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id AND xe.entity_id =xte.entity_id AND xe.event_id =xeh.event_id ORDER BY xdl.event_id , xdl.ae_header_id , xdl.ae_line_num ASC
XLA_TRANSACTION_ENTITIES (Payment)
SELECT DISTINCT xte.* FROM ap_invoice_payments_all aip , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND aip.invoice_id ='80105' AND xte.entity_code ='AP_PAYMENTS' AND xte.ledger_id =aip.set_of_books_id AND NVL(xte.source_id_int_1,-99)=aip.check_id
XLA_ACCOUNTING_ERRORS (Payment)
SELECT DISTINCT xae.* FROM ap_invoice_payments_all aip , xla_events xe , xla_transaction_entities_upg xte , xla_accounting_errors xae WHERE xte.application_id =200 AND xe.application_id =200 AND xae.application_id =200 AND aip.invoice_id ='80105' AND xe.event_id =xae.event_id AND xte.entity_code ='AP_PAYMENTS' AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.ledger_id =aip.set_of_books_id AND xte.entity_id =xe.entity_id
--------------------------------------------------------------------------------------------------------------------------
Trial Balance
AP_LIABILITY_BALANCE
select * from ap_liability_balance where invoice_id = '80105' order by ae_header_id
XLA_TRIAL_BALANCES
SELECT * FROM xla_trial_balances xtb WHERE NVL(xtb.applied_to_entity_id, xtb.source_entity_id) IN (SELECT xte.entity_id FROM xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xte.ledger_id ='2024' AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)='80105') ORDER BY definition_code , ledger_id
--------------------------------------------------------------------------------------------------------------------------
General Ledger
GL_JE_BATCHES (Invoice)
SELECT distinct gjb.* FROM GL_IMPORT_REFERENCES gir, GL_JE_BATCHES gjb, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gjb.je_batch_id = gir.je_batch_id
GL_JE_HEADERS (Invoice)
SELECT distinct gjh.* FROM GL_IMPORT_REFERENCES gir, GL_JE_HEADERS gjh, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gjh.je_header_id = gir.je_header_id
GL_JE_LINES (Invoice)
SELECT distinct gll.* FROM GL_IMPORT_REFERENCES gir, GL_JE_LINES gll, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gll.je_header_id = gir.je_header_id AND gll.je_line_num = gir.je_line_num
GL_IMPORT_REFERENCES (Invoice)
SELECT distinct gir.* FROM GL_IMPORT_REFERENCES gir, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aid.accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id
GL_JE_BATCHES (Payment)
SELECT distinct gjb.* FROM GL_IMPORT_REFERENCES gir, GL_JE_BATCHES gjb, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gjb.je_batch_id = gir.je_batch_id
GL_JE_HEADERS (Payment)
SELECT distinct gjh.* FROM GL_IMPORT_REFERENCES gir, GL_JE_HEADERS gjh, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 AND aea.event_id = aeh.event_id AND ael.application_id =200 AND aeh.ae_header_id = ael.ae_header_id AND gjh.je_header_id = gir.je_header_id
GL_JE_LINES (Payment)
SELECT distinct gll.* FROM GL_IMPORT_REFERENCES gir, GL_JE_LINES gll, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and ael.application_id = 200 and aeh.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id AND gll.je_header_id = gir.je_header_id AND gll.je_line_num = gir.je_line_num
GL_IMPORT_REFERENCES (Payment)
SELECT distinct gir.* FROM GL_IMPORT_REFERENCES gir, XLA_AE_LINES ael, XLA_AE_HEADERS aeh, XLA_EVENTS aea WHERE aea.event_id IN ( SELECT aip.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip WHERE aip.invoice_id = '80105' UNION SELECT distinct aph.accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL aip, AP_PAYMENT_HISTORY_ALL aph WHERE aph.check_id = aip.check_id AND aip.invoice_id = '80105' ) AND ael.gl_sl_link_id = gir.gl_sl_link_id AND ael.gl_sl_link_table =gir.gl_sl_link_table AND aea.application_id = 200 and aeh.application_id = 200 and ael.application_id = 200 AND aea.event_id = aeh.event_id AND aeh.ae_header_id = ael.ae_header_id
GL_PERIOD_STATUSES
SELECT gps.* FROM GL_PERIOD_STATUSES gps ,(SELECT period_name , set_of_books_id FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id='80105' UNION ALL SELECT period_name , set_of_books_id FROM ap_invoice_lines_all WHERE invoice_id='80105' UNION ALL SELECT period_name , set_of_books_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id='80105' ) atg_date WHERE atg_date.period_name=gps.period_name AND gps.application_id IN(101, 200) AND gps.ledger_id =atg_date.set_of_books_id UNION SELECT gps.* FROM GL_PERIOD_STATUSES gps , AP_PAYMENT_HISTORY_ALL aph , ap_invoice_payments_all aip WHERE aph.accounting_date BETWEEN gps.start_date AND gps.end_date AND aph.check_id =aip.check_id AND aip.invoice_id ='80105' AND gps.application_id IN(101, 200) AND gps.ledger_id =aip.set_of_books_id
GL_CODE_COMBINATIONS
SELECT DISTINCT gcc.* FROM gl_code_combinations gcc WHERE gcc.code_combination_id IN ( SELECT DISTINCT xel.code_combination_id FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoices_all ai , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xeh.application_id =200 AND xel.application_id =200 AND ai.invoice_id ='80105' AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND NVL(xte.source_id_int_1,-99)=ai.invoice_id AND xte.ledger_id =ai.set_of_books_id AND xte.entity_id =xeh.entity_id UNION ALL SELECT DISTINCT xel.code_combination_id FROM xla_ae_lines xel , xla_ae_headers xeh , ap_invoice_payments_all aip , xla_transaction_entities_upg xte WHERE xte.application_id =200 AND xel.application_id =200 AND xeh.application_id =200 AND aip.invoice_id ='80105' AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND xte.ledger_id =aip.set_of_books_id AND NVL(xte.source_id_int_1,-99)=aip.check_id AND xte.entity_id =xeh.entity_id UNION ALL SELECT DISTINCT po.code_combination_id FROM AP_INVOICE_DISTRIBUTIONS_ALL aid , PO_DISTRIBUTIONS_ALL po WHERE aid.invoice_id ='80105' AND aid.po_distribution_id IS NOT NULL AND po.po_distribution_id =aid.po_distribution_id )
--------------------------------------------------------------------------------------------------------------------------
Supplier
AP_SUPPLIERS
SELECT * FROM AP_SUPPLIERS WHERE vendor_id IN ( SELECT vendor_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
AP_SUPPLIER_SITES_ALL
SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE vendor_site_id IN ( SELECT vendor_site_id FROM AP_INVOICES_ALL WHERE invoice_id = '80105' )
HZ_PARTIES
SELECT DISTINCT HZP.* FROM AP_INVOICES_ALL AI, HZ_PARTIES HZP WHERE AI.PARTY_ID = HZP.PARTY_ID AND AI.INVOICE_ID = '80105'
HZ_PARTY_SITES
SELECT DISTINCT HZPS.* FROM AP_INVOICES_ALL AI, HZ_PARTY_SITES HZPS WHERE AI.PARTY_ID = HZPS.PARTY_ID AND AI.PARTY_SITE_ID = HZPS.PARTY_SITE_ID AND AI.INVOICE_ID = '80105'
--------------------------------------------------------------------------------------------------------------------------
PO_HEADERS_ALL
SELECT distinct poh.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po, PO_HEADERS_ALL poh WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id AND poh.po_header_id = po.po_header_id
PO_DISTRIBUTIONS_ALL
SELECT distinct po.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id
PO_LINES_ALL
SELECT distinct pl.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po, PO_LINES_ALL pl WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id AND pl.po_header_id = po.po_header_id AND pl.po_line_id = po.po_line_id
PO_LINE_LOCATIONS_ALL
SELECT distinct pll.* FROM AP_INVOICE_DISTRIBUTIONS_ALL aid, PO_DISTRIBUTIONS_ALL po, PO_LINE_LOCATIONS_ALL pll WHERE aid.invoice_id = '80105' AND aid.po_distribution_id is not null AND po.po_distribution_id = aid.po_distribution_id AND pll.po_header_id = po.po_header_id AND pll.po_line_id = po.po_line_id AND pll.line_location_id = po.line_location_id
--------------------------------------------------------------------------------------------------------------------------
Encumbrance
GL_BC_PACKETS
SELECT * FROM GL_BC_PACKETS WHERE je_source_name = 'Payables' AND reference2 = TO_CHAR('80105')
--------------------------------------------------------------------------------------------------------------------------
AP_PREPAY_HISTORY_ALL
SELECT aph.* FROM AP_PREPAY_HISTORY_ALL aph WHERE aph.invoice_id = '80105' OR aph.prepay_invoice_id = '80105'
AP_PREPAY_APP_DISTS
SELECT apad.* FROM AP_PREPAY_APP_DISTS apad, AP_INVOICE_DISTRIBUTIONS_ALL aid WHERE aid.invoice_id = '80105' AND apad.invoice_distribution_id = aid.invoice_distribution_id
Prepayment Invoices Applied to Invoice
SELECT pv.vendor_name "Vendor Name", ai.invoice_num "Invoice Num", ai.invoice_id "Invoice Id", ai.invoice_amount "Invoice amount", ail.line_number "Invoice Line Num", ai2.invoice_id "Prepay Invoice Id", ai2.invoice_num "Prepay Invoice Num", ail.prepay_line_number "Prepay Invoice Line Num", (-1)*(ail.amount - NVL(ail.included_tax_amount,0)) "Prepay Amount Applied", NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) + NVL(ail.total_nrec_tax_amount, 0)), 0) "Tax amount Applied" FROM AP_INVOICES_ALL ai, AP_INVOICES_ALL ai2, AP_INVOICE_LINES_ALL ail, AP_SUPPLIERS pv WHERE ai.invoice_id = ail.invoice_id AND ai2.invoice_id = ail.prepay_invoice_id AND ail.amount < 0 AND NVL(ail.discarded_flag,'N') <> 'Y' AND ail.line_type_lookup_code = 'PREPAY' AND ai.vendor_id = pv.vendor_id AND ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT') AND ai.invoice_id = '80105'
--------------------------------------------------------------------------------------------------------------------------
AP_BANK_BRANCHES
SELECT abb.* FROM AP_BANK_ACCOUNTS_ALL aba, AP_CHECK_STOCKS_ALL acs, AP_BANK_BRANCHES abb WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.bank_account_id = aba.bank_account_id AND abb.bank_branch_id = aba.bank_branch_id ORDER BY abb.bank_name
AP_BANK_ACCOUNTS_ALL
SELECT aba.*, fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aba.asset_code_combination_id) "Asset Account", fnd_flex_ext.get_segs('SQLGL','GL#', '50308' , aba.cash_clearing_ccid) "Cash Clearing Account" FROM AP_BANK_ACCOUNTS_ALL aba, AP_CHECK_STOCKS_ALL acs, AP_BANK_BRANCHES abb WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.bank_account_id = aba.bank_account_id AND abb.bank_branch_id = aba.bank_branch_id ORDER BY aba.bank_account_name
AP_CHECK_STOCKS_ALL
SELECT acs.* FROM AP_BANK_ACCOUNTS_ALL aba, AP_CHECK_STOCKS_ALL acs, AP_BANK_BRANCHES abb WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.bank_account_id = aba.bank_account_id AND abb.bank_branch_id = aba.bank_branch_id ORDER BY acs.name
AP_CHECK_FORMATS
SELECT acf.* FROM AP_PAYMENT_PROGRAMS app, AP_CHECK_STOCKS_ALL acs, AP_CHECK_FORMATS acf, AP_PAYMENT_PROGRAMS app2, AP_PAYMENT_PROGRAMS app3 WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.check_format_id = acf.check_format_id AND app.program_id = acf.format_payments_program_id AND acf.build_payments_program_id = app2.program_id AND acf.remittance_advice_program_id = app3.program_id(+) ORDER BY acf.name
AP_PAYMENT_PROGRAMS
SELECT app.* FROM AP_PAYMENT_PROGRAMS app, AP_CHECK_STOCKS_ALL acs, AP_CHECK_FORMATS acf, AP_PAYMENT_PROGRAMS app2, AP_PAYMENT_PROGRAMS app3 WHERE acs.check_stock_id IN ( SELECT check_stock_id FROM AP_CHECKS_ALL WHERE check_id IN ( SELECT distinct check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = '80105' ) ) AND acs.check_format_id = acf.check_format_id AND app.program_id = acf.format_payments_program_id AND acf.build_payments_program_id = app2.program_id AND acf.remittance_advice_program_id = app3.program_id(+) ORDER BY app.program_name
--------------------------------------------------------------------------------------------------------------------------
Cash Management
CE_STATEMENT_RECONCILS_ALL
SELECT distinct csr.* FROM CE_STATEMENT_LINES cel, CE_STATEMENT_HEADERS ceh, CE_STATEMENT_RECONCILS_ALL csr, AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac WHERE cel.statement_header_id = ceh.statement_header_id AND ac.check_id = aip.check_id AND aip.invoice_id = '80105' AND aip.check_id = ac.check_id AND ac.bank_account_id = ceh.bank_account_id AND to_char(ac.check_number) = cel.bank_trx_number AND csr.statement_line_id = cel.statement_line_id
CE_STATEMENT_HEADERS_ALL
SELECT distinct ceh.* FROM CE_STATEMENT_LINES cel, CE_STATEMENT_HEADERS ceh, CE_STATEMENT_RECONCILS_ALL csr, AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac WHERE cel.statement_header_id = ceh.statement_header_id AND ac.check_id = aip.check_id AND aip.invoice_id = '80105' AND aip.check_id = ac.check_id AND ac.bank_account_id = ceh.bank_account_id AND to_char(ac.check_number) = cel.bank_trx_number AND csr.statement_line_id = cel.statement_line_id
CE_STATEMENT_LINES_ALL
SELECT distinct cel.* FROM CE_STATEMENT_LINES cel, CE_STATEMENT_HEADERS ceh, CE_STATEMENT_RECONCILS_ALL csr, AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac WHERE cel.statement_header_id = ceh.statement_header_id AND ac.check_id = aip.check_id AND aip.invoice_id = '80105' AND aip.check_id = ac.check_id AND ac.bank_account_id = ceh.bank_account_id AND to_char(ac.check_number) = cel.bank_trx_number AND csr.statement_line_id = cel.statement_line_id
--------------------------------------------------------------------------------------------------------------------------
AP_EXPENSE_REPORT_HEADERS_ALL
select erh.* from ap_invoices_all ai, ap_expense_report_headers_all erh where ai.invoice_id = '80105' and ai.invoice_type_lookup_code = 'EXPENSE REPORT' and ai.invoice_id = erh.vouchno
AP_EXPENSE_REPORT_LINES_ALL
select erl.* from ap_invoices_all ai, ap_expense_report_headers_all erh, ap_expense_report_lines_all erl where ai.invoice_id = '80105' and ai.invoice_type_lookup_code = 'EXPENSE REPORT' and ai.invoice_id = erh.vouchno and erh.report_header_id = erl.report_header_id
AP_EXP_REPORT_DISTS_ALL
select erd.* from ap_invoices_all ai, ap_expense_report_headers_all erh, ap_exp_report_dists_all erd where ai.invoice_id = '80105' and ai.invoice_type_lookup_code = 'EXPENSE REPORT' and ai.invoice_id = erh.vouchno and erh.report_header_id = erd.report_header_id
--------------------------------------------------------------------------------------------------------------------------
Treasury Confirmation
FV_TREASURY_CONFIRMATIONS_ALL
SELECT distinct tc.* FROM AP_INVOICE_PAYMENTS_ALL aip, AP_CHECKS_ALL ac, FV_TREASURY_CONFIRMATIONS_ALL tc WHERE ac.checkrun_name = tc.checkrun_name AND ac.check_id = aip.check_id AND aip.invoice_id = '80105'
--------------------------------------------------------------------------------------------------------------------------
Currency
FND_CURRENCIES
SELECT * FROM FND_CURRENCIES WHERE currency_code IN ( SELECT a.invoice_currency_code FROM AP_INVOICES_ALL a WHERE a.invoice_id = '80105' UNION SELECT b.payment_currency_code FROM AP_INVOICES_ALL b WHERE b.invoice_id = '80105' UNION SELECT c.base_currency_code FROM AP_SYSTEM_PARAMETERS_ALL c WHERE nvl(c.org_id, -99) = '185' )
