R12 BANK ACCOUNTS-SUPPLIER AND CUSTOMER

- 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;

Register a custom table in Oracle 11i/R12

Register a custom table in Oracle 11i/R12
You register your custom application tables using a PL/SQL routine in the AD_DD package.
Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their

columns) that will be used with flexfields or Oracle Alert.
You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration.
To alter a registration you should first delete the registration, then re-register the table or column.
The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that

the tables and columns registered actually exist and have the same format as that defined using the AD_DD API.
You need NOT register views.
You should include calls to the table registration routines in a PL/SQL script.
You should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.
Procedures in the AD_DD Package:

 procedure register_table (p_appl_short_name in varchar2,
                       p_tab_name    in varchar2,
                       p_tab_type    in varchar2,
                       p_next_extent in number default 512,
                       p_pct_free    in number default 10,
                       p_pct_used    in number default 70);

p_appl_short_ name: The application short name of the application that owns the table (usually your custom application).
p_tab_name: The name of the table (in uppercase letters).
p_tab_type: Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table.
p_next_extent: The next extent size, in kilobytes. Do not include the 'K'.
p_pct_free: The percentage of space in each of the table's blocks reserved for future updates to the table (1-99).
p_pct_used: Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.

procedure register_column (p_appl_short_name in varchar2,
                       p_tab_name   in varchar2,
                       p_col_name   in varchar2,
                       p_col_seq    in number,
                       p_col_type   in varchar2,
                       p_col_width  in number,
                       p_nullable   in varchar2,
                       p_translate  in varchar2,
                       p_precision  in number default null,
                       p_scale      in number default null);

p_col_name: The name of the column (in uppercase letters).
p_col_seq: The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type: The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
p_col_width: The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable: Use 'N' if the column is mandatory or 'Y' if the column allows null values.
p_translate: Use 'Y' if the column values will be translated for an Oracle E-Business Suite product release (used only by Oracle E-Business Suite products) or 'N' if

the values are not translated (most application columns).
p_precision: The total number of digits in a number.
p_scale: The number of digits to the right of the decimal point in a number.

procedure delete_table  (p_appl_short_name in varchar2,
                       p_tab_name    in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
                       p_tab_name    in varchar2,
                       p_col_name    in varchar2);

Example:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);

EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');

registering primary keys

Begin
ad_dd.register_primary_key('XXCUS','LINES_FK','XX_LINES','number','S','Y','Y');
end;

BEGIN
ad_dd.register_primary_key_column('XXCUS','LINES_FK','XX_LINES','ORDER_NO',1);
END;