Bank-accounts-supplier-site ,link between IBY and HZ

 Banks and their Branches are now each stored as Parties (HZ_PARTIES) in their own right. They are linked together through Relationships(HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

- The Bank Accounts themselves are now stored in the new Oracle Payments Application

- Below are the Key tables where the Bank Account information is stored

IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

- Below are the Key tables where the Bank Data of R12 TCA is stored

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
HZ_CONTACT_POINTS
HZ_ORG_CONTACT
HZ_ORG_CONTACT_ROLES

• The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

• The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name customer_name
, cust_acct.account_number
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_account_owners acc_owner
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id
AND cust.party_id = cust_acct.party_id;

AP Invoice flow Technical Details with Invoice Functional

AP Invoice Technical Details with Functional Inputs

When Invoice Booked and Saved
===========================
One row created in ap_invoices_all and its distribution lines created in ap_invoice_distributions_all

When Invoice Validated :
======================
ap_invoice_distributions_all.MATCH_STATUS_FLAG='A'
ap_invoice_distributions_all.ACCOUNTING_EVENT_ID=NOT NULL(Here 1370092)
one row created in ap_accounting_events_all with accounting_event_id=ap_invoice_distributions_all.ACCOUNTING_EVENT_ID ap_accounting_events_all.EVENT_STATUS_CODE='CREATED' ap_accounting_events_all.SOURCE_TABLE='AP_INVOICES'
ap_accounting_events_all.SOURCE_ID=ap_invoice_distributions_all.INVOICE_ID=
AP_INVOICE_ALL.INVOICE_ID

When Invoice Accounted :
=====================
ap_invoice_distributions_all.ACCRUAL_POSTED_FLAG='Y'
ap_invoice_distributions_all.POSTED_FLAG='Y' ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'
ONE ROW CREATED IN AP_AE_HEADERS_ALL where AP_AE_HEADERS_ALL. accounting_event_id=ap_accounting_events_all.accounting_event_id Rows created in ap_ae_lines_all
where AP_AE_HEADERS_ALL.ae_header_id=ap_ae_lines_all.ae_header_id as
below The number of rows generally created in ap_ae_lines_all counted as 1)
one row for invoice with ap_ae_lines_all.AE_LINE_TYPE_CODE='LIABILITY'
ap_ae_lines_all.SOURCE_TABLE='AP_INVOICES' ,ap_ae_lines_all.source_id=ap_invoices_all.invoice_id2)
Other rows are created for the invoice distribution lines (one line per invoice distribution line).ap_ae_lines_all.AE_LINE_TYPE_CODE='CHARGE',
SOURCE_TABLE='AP_INVOICE_DISTRIBUTIONS',
ap_ae_lines_all.SOURCE_ID=AP_INVOICE_DISTRIBUTIONS.INVOICE_ID

When Invoice Approved : 
========================
ap_invoices_all.WFAPPROVAL_STATUS='MANUALLY APPROVED', initially it was 'REQUIRED'

When Payment Created
=====================
when payment created the one record created in ap_checks_all table.

When Payment Accounted
=============================
When payment document accounted then one row is created in ap_accounting_events_all table.
AP_invoice_payments_all.ACCOUNTING_EVENT_ID=
ap_accounting_events_all.ACCOUNTING_EVENT_ID
ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'. andap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id.

After Doing the Payment (paid) of invoice with Created Payment Document
=============================================================
ap_invoices_all.payment_status_flag='Y' BEFORE 'N'It creates the linKing between ap_invoices_all and ap_checks_all by AP_INVOICE_PAYMENTS_ALL.one row created in AP_INVOICE_PAYMENTS_ALL with reference of invoice id.
AP_INVOICE_PAYMENTS_ALL.ACCRUAL_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.CASH_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.POSTED_FLAG='Y'and when get void the AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG='Y' unless it is 'N'
When payment got accounted the one row created in ap_accounting_events_all with ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'

After Clearing Check from cash management
=====================================
open payment document and create accounting for it, showing partial now.
after successfull accounting of the document:
one line is created in AP_PAYMENT_HISTORY_all with new accounting _event_id.
AP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_idone new line created in ap_accounting_events_all with EVENT_TYPE_CODE='PAYMENT CLEARING'and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'
ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id andAP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_id
one row created in AP_AE_HEADERS_ALL with new accounting_event_id and two rows in this case created in ap_ae_lines_all with AE_LINE_TYPE_CODE='CASH CLEARING ' AND 'CASH',SOURCE_TABLE='AP_CHECKS'.


 AP invoice to XLA link
 ======================
xla_distribution_links l  table
liked in AP_INVOIVES_ALL table and AP_INVOICE_DISTRIBUTIONS_ALL
  AND l.applied_to_source_id_num_1 = i.invoice_id
  AND l.source_distribution_id_num_1 = ad.invoice_distribution_id

 xla.xla_transaction_entities xte  table =   xte.entity_code = 'AP_INVOICES'

  XLA to GL 
 ===========

apps.gl_import_references r,
apps.xla_ae_lines al,

LINK
=====
 and al.gl_sl_link_id = r.gl_sl_link_id

When Posted in GL (GL_POSTING)
===============================
after running the request "Payables Transfer to General Ledger".The ap_ae_lines_all.GL_SL_LINK_ID populates.AP_AE_HEADERS_ALL.GL_TRANSFER_FLAG='Y'AP_AE_HEADERS_ALL.
GL_TRANSFER_RUN_ID IS NOT NULL AP_AE_HEADERS_ALL.TRIAL_BALANCE_FLAG='Y'

Query 
=========

/* Formatted on 12/26/2013 9:33:44 AM (QP5 v5.114.809.3010) */
SELECT   A.ORG_ID "ORG ID",
         E.VENDOR_NAME "VENDOR NAME",
         UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         F.VENDOR_SITE_CODE "VENDOR SITE",
         F.ADDRESS_LINE1 "ADDRESS",
         F.CITY "CITY",
         F.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE",
         D.SEGMENT1 "PO NUMBER",
         D.TYPE_LOOKUP_CODE "PO TYPE",
         C.QUANTITY_ORDERED "QTY ORDERED",
         C.QUANTITY_CANCELLED "QTY CANCALLED",
         G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         G.UNIT_PRICE "UNIT PRICE",
         (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))
         * NVL (G.UNIT_PRICE, 0)
            "PO Line Amount",
         (SELECT   DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')
            FROM   PO.PO_HEADERS_ALL PH
           WHERE   PH.PO_HEADER_ID = D.PO_HEADER_ID)
            "PO STATUS",
         A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         A.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE",
         A.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)
            "Invoice Approved?",
         A.AMOUNT_PAID,
         H.AMOUNT,
         I.CHECK_NUMBER "CHEQUE NUMBER",
         TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"
  FROM   AP.AP_INVOICES_ALL A,
         AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
         PO.PO_DISTRIBUTIONS_ALL C,
         PO.PO_HEADERS_ALL D,
         PO.PO_VENDORS E,
         PO.PO_VENDOR_SITES_ALL F,
         PO.PO_LINES_ALL G,
         AP.AP_INVOICE_PAYMENTS_ALL H,
         AP.AP_CHECKS_ALL I
 WHERE       A.INVOICE_ID = B.INVOICE_ID
         AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)
         AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)
         AND E.VENDOR_ID(+) = D.VENDOR_ID
         AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID
         AND D.PO_HEADER_ID = G.PO_HEADER_ID
         AND C.PO_LINE_ID = G.PO_LINE_ID
         AND A.INVOICE_ID = H.INVOICE_ID
         AND H.CHECK_ID = I.CHECK_ID
         AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
         AND C.PO_HEADER_ID IS NOT NULL
         AND A.PAYMENT_STATUS_FLAG = 'Y'
         AND D.TYPE_LOOKUP_CODE != 'BLANKET';


account paybles(ap)module important tables in oracle apps 

===============================================

--ACCOUNT PAYBLES(AP) IMPORTANT TABLES

--INVOICES

Select * from   AP_INVOICES_ALL --IN 11I

Select * from   AP_INVOICE_DISTRIBUTIONS_ALL  --IN 11I

Select * from   AP_INVOICES_ALL

Select * from   AP_INVOICE_LINES_ALL

Select * from   AP_INVOICE_DISTRIBUTIONS_ALL

--ACCOUNTING

Select * from   AP_AE_HEADERS_ALL  11I 

Select * from   AP_AE_LINES_ALL  11I 

--JOURNALS

Select * from   GL_JE_HEADERS

Select * from   GL_JE_LINES

--PAYMENTS

Select * from   AP_INVOICE_PAYMENTS_ALL

Select * from   AP_CHECKS_ALL

--DISTRIBUTION SETS

Select * from   AP_DISTRIBUTION_SETS_ALL

Select * from   AP_DISTRIBUTION_SET_LINES_ALL

--PAYMENT TERMS

Select * from   AP_TERMS_TL

Select * from   AP_TERMS_LINES

--SCHEDULE PAYMENTS

Select * from   AP_PAYMENT_SCHEDULES_ALL

--HOLD

Select * from   AP_HOLDS_ALL

--SUPPLIERS

Select * from   PO_VENDORS

Select * from   PO_VENDOR_SITES_ALL

Select * from   PO_VENDOR_CONTACTS

Select * from AP_SUPPLIERS

Select * from AP_SUPPLIERS_SITS_ALL

--INVOICE TYPES

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='INVOICE_TYPE')

--INVOICE DIST TYPES

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='INVOICE_DISTRIBUTION_TYPE')

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='SOURCE')