How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)

In this post, we will find the tables involved in storing the Payment Data related to the   Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance. 

AP_TERMS

SELECT *

FROM   AP_TERMS

WHERE  term_id IN

       ( SELECT DISTINCT terms_id

           FROM   AP_INVOICES_ALL

             WHERE  invoice_id = '166014'

       );        

AP_TERMS_LINES 

SELECT *

FROM   AP_TERMS_LINES

WHERE  term_id IN

       ( SELECT DISTINCT terms_id

           FROM   AP_INVOICES_ALL

             WHERE  invoice_id = '166014'

       ); 

AP_PAYMENT_SCHEDULES_ALL 

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 = '166014'; 

AP_INVOICE_PAYMENTS_ALL 

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 = '166014'

ORDER BY check_id ASC; 

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         = '166014';

AP_CHECKS_ALL 

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 = '166014'

      ); 

AP_PAYMENT_HISTORY_ALL 

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 = '166014'

      )

ORDER BY payment_history_id ASC; 

AP_PAYMENT_HIST_DISTS 

SELECT aphd.*

FROM   AP_INVOICE_DISTRIBUTIONS_ALL aid,

       AP_PAYMENT_HIST_DISTS aphd,

       AP_PAYMENT_HISTORY_ALL aph

WHERE  aid.invoice_id              = '166014'

AND    aid.invoice_distribution_id = aphd.invoice_distribution_id

AND    aph.payment_history_id      = aphd.payment_history_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 = '166014'

  );

  

AP_DOCUMENTS_PAYABLE 

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  -- Application id for Payables

AND   calling_app_doc_unique_ref2 = '166014'; 

IBY_DOCS_PAYABLE_ALL 

SELECT *

FROM   IBY_DOCS_PAYABLE_ALL

WHERE  calling_app_id            = 200

AND    calling_app_doc_unique_ref2 = '166014';

IBY_PAYMENTS_ALL 

SELECT *

FROM   IBY_PAYMENTS_ALL

WHERE  payment_id IN

       (SELECT payment_id

        FROM   IBY_DOCS_PAYABLE_ALL

        WHERE   calling_app_id              = 200

        AND     calling_app_doc_unique_ref2 = '166014'

        ); 

IBY_PAY_INSTRUCTIONS_ALL 

SELECT *

FROM   IBY_PAY_INSTRUCTIONS_ALL

WHERE  payment_instruction_id IN

       (SELECT payment_instruction_id

        FROM IBY_PAYMENTS_ALL

        WHERE payment_id IN

              (SELECT payment_id

               FROM   IBY_DOCS_PAYABLE_ALL

               WHERE   calling_app_id              = 200

               AND     calling_app_doc_unique_ref2 = '166014'

              );

        );

Asset Addition API

High Level Steps are described below in brief for Asset Addition Through API:

1) Declare Record and Table Type Variables as below:
l_trans_rec Fa_Api_Types.trans_rec_type;
l_dist_trans_rec Fa_Api_Types.trans_rec_type;
l_asset_hdr_rec Fa_Api_Types.asset_hdr_rec_type;
l_asset_desc_rec Fa_Api_Types.asset_desc_rec_type;
l_asset_cat_rec Fa_Api_Types.asset_cat_rec_type;
l_asset_type_rec Fa_Api_Types.asset_type_rec_type;
l_asset_hierarchy_rec Fa_Api_Types.asset_hierarchy_rec_type;
l_asset_fin_rec Fa_Api_Types.asset_fin_rec_type;
l_asset_deprn_rec Fa_Api_Types.asset_deprn_rec_type;
l_asset_dist_rec Fa_Api_Types.asset_dist_rec_type;
l_asset_dist_tbl Fa_Api_Types.asset_dist_tbl_type;
l_inv_rec Fa_Api_Types.inv_rec_type;
l_inv_tbl Fa_Api_Types.inv_tbl_type;
l_inv_rate_tbl Fa_Api_Types.inv_rate_tbl_type;

2) Declare Cursor for Asset Data Staging Table

3) Open Cursor and Loop through Staging Table Records to assign values to Composite Variables

BEGIN

Fa_Srvr_Msg.Init_Server_Message;
Fa_Debug_Pkg.Initialize;
FOR rec_c_assets IN c_assets

LOOP
l_asset_desc_rec.asset_key_ccid := v_asset_key_ccid;
l_asset_desc_rec.asset_number := rec_c_assets.asset_number;
l_asset_desc_rec.manufacturer_name := rec_c_assets.manufacturer_name;
l_asset_desc_rec.serial_number := rec_c_assets.serial_number;
l_asset_desc_rec.model_number := rec_c_assets.model_number;
l_asset_desc_rec.tag_number := rec_c_assets.tag_number;
l_asset_cat_rec.category_id := v_asset_category_id;

--type info
l_asset_type_rec.asset_type := 'CAPITALIZED';

-- invoice info
l_inv_rec.fixed_assets_cost := rec_c_assets.cost;
l_inv_rec.description := 'Asset Conversion';
l_inv_rec.invoice_number:=rec_c_assets.invoice_number;
l_inv_rec.feeder_system_name := '11.5.8';
l_inv_tbl (1) := l_inv_rec;
l_asset_fin_rec.COST := rec_c_assets.cost;
l_asset_fin_rec.date_placed_in_service :=fnd_conc_date.string_to_date(rec_c_assets.date_placed_in_service);
l_asset_fin_rec.depreciate_flag := 'YES';
l_asset_fin_rec.deprn_method_code := rec_c_assets.depreciation_method;
l_asset_fin_rec.life_in_months := rec_c_assets.life_in_months;

-- deprn info
l_asset_deprn_rec.ytd_deprn := rec_c_assets.ytd_depreciation;
l_asset_deprn_rec.deprn_reserve := rec_c_assets.depreciation_reserve;

-- book / trans info
l_asset_hdr_rec.book_type_code := rec_c_assets.book_type_code;
l_trans_rec.transaction_date_entered := fnd_conc_date.string_to_date(rec_c_assets.date_placed_in_service);
l_trans_rec.who_info.last_updated_by := Fnd_Global.USER_ID;
l_trans_rec.who_info.last_update_date:= sysdate;
l_trans_rec.who_info.created_by:=Fnd_Global.USER_ID;
l_trans_rec.who_info.creation_date:= sysdate;
l_trans_rec.who_info.last_update_login:=Fnd_Global.USER_ID;

-- distribution info
l_asset_dist_rec.units_assigned := rec_c_assets.units;
l_asset_dist_rec.expense_ccid := v_expense_ccid;
l_asset_dist_rec.location_ccid := v_location_ccid;
l_asset_dist_rec.assigned_to := NULL;
l_asset_dist_rec.transaction_units := rec_c_assets.units;
l_asset_dist_tbl(1) := l_asset_dist_rec;

4) Finally Call API

Fa_Addition_Pub.do_addition
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_calling_fn => NULL,
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec => l_asset_desc_rec,
px_asset_type_rec => l_asset_type_rec,
px_asset_cat_rec => l_asset_cat_rec,
px_asset_hierarchy_rec => l_asset_hierarchy_rec,
px_asset_fin_rec => l_asset_fin_rec,
px_asset_deprn_rec => l_asset_deprn_rec,
px_asset_dist_tbl => l_asset_dist_tbl,
px_inv_tbl => l_inv_tbl
);

END LOOP;
END

To get the Internal BankAccount / Bank Branch Details - R12

Purpose : R12 To get the Internal BankAccount / Bank Branch Details

-------------------------------------------------------------------------------------------

select ieba.ext_bank_account_id,
ieba.bank_account_num,
ieba.bank_account_name,
ieba.bank_id,
ieba.branch_id,
ieb.bank_name,
iebb.bank_branch_name,
iebb.branch_number,
iebb.eft_swift_code,
iebb.address_line1,
iebb.address_line2,
iebb.address_line3,
iebb.address_line4,
iebb.city,
iebb.state,
iebb.country,
iebb.zip,
ieba.creation_date,
(select user_name from fnd_user where user_id = ieba.created_by) created_by
from
apps.iby_ext_bank_branches_v iebb,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_accounts ieba
where iebb.bank_party_id = ieba.bank_id
and iebb.branch_party_id = ieba.branch_id
and ieb.bank_party_id = iebb.bank_party_id
and ieb.bank_party_id = ieba.bank_id

-- ----------------------------------------------------------------------------------------------------

Importing Supplier Bank Accounts using Supplier Open Interface

Importing Supplier Bank Accounts using Supplier Open Interface

In R12, You can create Supplier Bank Accounts and associate them to a Payee during Supplier Open Interface Program.

Follow below steps:
1. Load Supplier & Supplier Sites Data to the respective Interface Tables AP_SUPPLIERS_INT and AP_SUPPLIER_SITES_INT.
2. Load the respective Suppliers bank Accounts Data to the ‘IBY_TEMP_EXT_BANK_ACCTS’ Table
3. Run the Suppliers Open Interface Import

In R12, While validating/creating Supplier and Supplier Sites Data and inserting to AP and HZ Tables, a Payee is created in to the IBY Table. If the Payee is created successfully then a check is made for any related rows in ‘IBY_TEMP_EXT_BANK_ACCTS’ Table and an IBY API is called to create a Bank Account and associate it to the respective Payee.

Following conditions are to be met for successful import of Bank Account:
- Suppliers
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref1 = AP_SUPPLIERS_INT.vendor_interface_id
-Supplier Site
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2  = AP_SUPPLIER_SITES_INT.vendor_site_interface_id

Note:  Bank and Bank Branch should have already been defined in the System for the successful Bank Account Import.

Oracle R12 – Some good Question on Supplier – Bank Setups

Where is Supplier Bank Information Stored in Release 12?

The supplier bank account information is in the table: IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES

The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. This table also contains data retrieved from Dun & Bradstreet using either the Dun & Bradstreet online or batch download methods. Historical data for the organization can also be stored in this table. Each time organization information is changed, the effective end date column for the original record is updated and a new record that contains the updated information is created.

The column name is BANK_OR_BRANCH_NUMBER VARCHAR2 (30) Stores bank number for banks and branch number for bank branches, primarily ABA number for US bank branches.

Where are Supplier (External) Bank Accounts Created?

In R12, Internal bank accounts are now created in Cash Management (Setup -> Banks).  Where are Supplier (or External) bank accounts created?

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms.  In the Payables Manager responsibility:

1. Navigate to Suppliers -> Entry.
2. Query or create your supplier.
3. Click on Banking Details and then choose Create.

After you have created the bank account, you can assign the bank account to the supplier site.

How do you import Supplier Bank Accounts during Supplier and Supplier Site Open Interface?

After the Supplier or Supplier Site is validated and a rows entered in the various AP and HZ tables, a Payee is created in IBY (the new Payments application) for the Supplier or Supplier Site.

If the Payee is successfully created, we then check to see if there are any corresponding rows in
IBY_TEMP_EXT_BANK_ACCTS. If there are, we call an IBY API to create the Bank Account and associate it with the Payee.

So to import supplier bank accounts during Supplier and Supplier Site Open Interface, you can populate the IBY_TEMP_EXT_BANK_ACCTS table.

A row in IBY_TEMP_EXT_BANK_ACCTS is said to be associated with the Supplier or Supplier Site if the column IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref1 is equal to either AP_SUPPLIERS_INT.vendor_interface_id for Suppliers or  IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2 is equal to AP_SUPPLIER_SITES_INT.vendor_site_interface_id for Supplier Sites.

The bank and bank branch referenced in IBY_TEMP_EXT_BANK_ACCTS must already exist in the system.  There is no functionality in the Bank Account Import to create the bank and/or bank branch. This functionality associates the new supplier to an existing bank and/or bank branch.

How to enable the Supplier bank Account default to the Payment Schedules tab

A review of the current code, underlying in ibydiscb.pls ie IBY_DISBURSEMENT_COMP_PUB, depicts that the Supplier bank Account defaults to the Payment Schedules tab in the remit to bank Account only if any of the below mentioned two conditions are satisfied;
1. The Supplier bank Account has no currency associated to it
Or
2. The currency code associated to the Supplier Bank Account is the same as the Payment Currency
at the Invoice level

How to Query the Bank Account at the Supplier Site Level in SQL

R12 a Supplier Site is stored, in TCA, as a Party_Site. The Party Site has the Party ID of the Party that represents the Supplier record.
SELECT BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM
FROM IBY_EXT_BANK_ACCOUNTS
WHERE EXT_BANK_ACCOUNT_ID IN
(SELECT EXT_BANK_ACCOUNT_ID
FROM IBY_ACCOUNT_OWNERS
WHERE ACCOUNT_OWNER_PARTY_ID IN
(SELECT party_id
FROM hz_party_sites
WHERE party_site_name = ''))