SQL Query To Fetch Supplier Bank Details In Oracle Apps R12

 This query will help you to get Supplier bank details like Supplier bank name, bank address, bank account number, branch type etc in Oracle R12 Application

SELECT   VENDOR_NAME,IEBA.BANK_ACCOUNT_NUM,
         IEBA.BANK_ACCOUNT_NAME,
         cbv.BANK_NAME,
         cbv.ADDRESS_LINE1 BANK_ADDRESS_1,
         CBV.COUNTRY BANK_COUNTRY,
         CBV.CITY BANK_CITY,
         CBBV.BANK_BRANCH_NAME,
         CBBV.ADDRESS_LINE1 BRANCH_ADDRESS_1,
         CBBV.CITY BRANCH_CITY,
         CBBV.COUNTRY BRANCH_COUNTRY,
         CBBV.BRANCH_NUMBER,
         CBBV.EFT_SWIFT_CODE Swift_Code ,
         FOREIGN_PAYMENT_USE_FLAG
 FROM    apps.AP_SUPPLIERS APS,
         apps.IBY_EXTERNAL_PAYEES_ALL IEPA,
         apps.IBY_PMT_INSTR_USES_ALL IPIUA,
         APPS.IBY_EXT_BANK_ACCOUNTS IEBA,
         apps.ce_banks_v cbv, 
         apps.ce_bank_BRANCHES_V CBBV
 WHERE   1=1
     AND APS.VENDOR_ID IN (select VENDOR_ID from apps.AP_SUPPLIER_SITES_ALL ass)
     AND IEPA.PAYEE_PARTY_ID=APS.PARTY_ID
     AND PARTY_SITE_ID IS NULL
     AND SUPPLIER_SITE_ID IS NULL
     AND IPIUA.EXT_PMT_PARTY_ID(+)=IEPA.EXT_PAYEE_ID
     AND IEBA.EXT_BANK_ACCOUNT_ID(+)=IPIUA.INSTRUMENT_ID
     AND IEBA.BANK_ID=cbv.BANK_PARTY_ID(+)
     AND IEBA.BRANCH_ID=CBBV.BRANCH_PARTY_ID(+);

Columns in XLA_AE_HEADERS and Transfer Status

What columns in XLA_AE_HEADERS indicate the Transfer Status of accounting data in subledger?

The XLA_AE_HEADERS table shows the transfer status of the accounting data, after successful transfer to the GL and Journal Import:

XLA_AE_HEADERS.gl_transfer_status_code = Y
XLA_AE_HEADERS.gl_transfer_date is not null
XLA_AE_HEADERS.group_id is not null

For 11i data upgraded to R12, the transfer status can be determined using the following

XLA_AE_HEADERS.gl_transfer_status_code = Y
XLA_AE_HEADERS.gl_transfer_date is null
XLA_AE_HEADERS.group_id is null
XLA_AE_HEADERS.upg_batch_id is not null  –> indicates 11i data

How to join GL tables with XLA (SubLedger Accounting) tables

GL_JE_BATCHES (je_batch_id)                            => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                           => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)               => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_HEADERS (application_id, event_id)              => XLA_EVENTS (application_id, event_id)   
XLA_AE_LINES (application_id, ae_header_id)            => XLA_AE_HEADERS (application_id, ae_header_id) 
XLA_EVENTS (application_id, entity_id)                 => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)








The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.

The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

AP Table Relation Oracle Apps

AP Table Relation Oracle Apps

ORACLE PAYABLE TABLE RELATION
Source Table	Dependent Table	Condition
AP_INVOICE_LINES_ALL AIL	ZX_LINES_SUMMARY ZLS	AIL.invoice_id=ZLS.trx_id and ZLS.application_id = 200 and ZLS.entity_code = 'AP_INVOICES' and ZLS.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.summary_tax_line_id=ZLS.summary_tax_line_id
AP_INVOICE_LINES_ALL AIL	ZX_LINES ZL	AIL.invoice_id=ZL.trx_id and ZL.application_id = 200 and ZL.entity_code = 'AP_INVOICES' and ZL.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.line_number=ZL.trx_line_number
AP_INVOICE_DISTRIBUTIONS_ALL AID	ZX_REC_NREC_DIST ZD	AID.invoice_id=ZD.trx_id and ZD.application_id = 200 and ZD.entity_code = 'AP_INVOICES' and ZD.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AID.summary_tax_line_id = ZD.summary_tax_line_id and AID.detail_tax_dist_id = ZD.rec_nrec_tax_dist_id
AP_SELF_ASSESSED_TAX_DIST_ALL ASTD	ZX_REC_NREC_DIST ZD	ASTD.invoice_id=ZD.trx_id and ZD.application_id = 200 and ZD.entity_code = 'AP_INVOICES' and ZD.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and ASTD.summary_tax_line_id = ZD.summary_tax_line_id and ASTD.detail_tax_dist_id=ZD.rec_nrec_tax_dist_id and ZD.self_assessed_flag='Y'
AP_INVOICE_LINES_ALL AIL	ZX_LINES_DET_FACTORS ZLDF	AIL.invoice_id=ZLDF.trx_id and ZLDF.application_id = 200 and ZLDF.entity_code = 'AP_INVOICES' and ZLDF.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.line_number=ZLDF.trx_line_number
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	XLA_EVENTS XE	Actual Event: -------------- xe.event_id = aid.accounting_event_id and xe.application_id = 200 Budgetary Event: ----------------- xe.event_id = aid.bc_event_id and xe.application_id = 200
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	RCV_TRANSACTIONS RT	aid.rcv_transaction_id = rt.transaction_id
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	GL_CODE_COMBINATIONS GCC	aid.dist_code_combination_id=gcc.code_combination_id
AP_PAYMENT_HISTORY_ALL APH	XLA_EVENTS XE	aph.accounting_event_id = xe.event_id    and xe.application_id = 200
AP_INVOICE_PAYMENTS_ALL AIP	XLA_EVENTS XE	aip.accounting_event_id = xe.event_id and xe.application_id = 200
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	PO_DISTRIBUTIONS_ALL	PD aid.po_distribution_id = pd.po_distribution_id
AP_INVOICES_ALL AI	PO_HEADERS_ALL PH	ai.po_header_id = ph.po_header_id
AP_SUPPLIER_SITES_ALL ASS	HZ_PARTY_SITES HPS	ass.party_site_id = hps.party_site_id
AP_SUPPLIERS AS	HZ_PARTIES HP	as.party_id = hp.party_id
AP_PRERPAY_APP_DISTS or AP_PAYMENT_HIST_DISTS APA	XLA_EVENTS XE	apa.accounting_event_id = xe.event_id xe.application_id = 200
AP_PREPAY_HISTORY_ALL APH	XLA_EVENTS XE	aph.accounting_event_id = xe.event_id and xe.application_id = 200
AP_INVOICES_ALL AI	XLA.XLA_TRANSACTION_ENTITIES XTE	ai.invoice_id = nvl(xte.source_id_int_1, -99) and xte.entity_code = 'AP_INVOICES' and xte.application_id = 200
AP_CHECKS_ALL AC	XLA.XLA_TRANSACTION_ENTITIES XTE	ac.check_id = nvl(xte.source_id_int_1, -99) and xte.entity_code = 'AP_PAYMENTS' and xte.application_id =200