Read this:

1. http://www.oracleappshub.com/account-payable/r12-ebs-banking-model-in-demanding-and-changing-world/
2. http://www.oracleappshub.com/account-payable/welcome-to-r12-account-payable/

Three key CE tables now as:

• CE_BANK_ACCOUNTS for bank accounts
• CE_BANK_ACCT_USES_ALL for account uses by Operating Units & Legal Entities
• CE_GL_ACCOUNTS_CCID for bank account use accounting data

TCA and Bank

The TCA party model is being used to model banks and bank branches as parties with the associated attributes of Relationships, Address, Contact and Locations. The TCA tables used by Cash Management for modeling Banks and Bank Branches are listed below:

1. HZ_PARTIES
2. HZ_RELATIONSHIPS
3. HZ_RELATIONSHIP_TYPES
4. HZ_ORG_CONTACTS
5. HZ_ORG_CONTACT_ROLES
6. HZ_CONTACT_POINTS
7. HZ_PARTY_SITES
8. HZ_LOCATIONS
9. HZ_ORGANIZATION_PROFILES

The HZ_ORGANIZATION_PROFILES table stores additional attributes of banks and bank branches along with the history of changes made to Banks and Bank Branches. The contact person at the bank, bank branch and bank account is defined as a party in HZ_PARTIES, while the contact details will be stored in HZ_CONTACT_POINTS (stores contact methods), HZ_ORG_CONTACTS (stores the contact’s title) and HZ_ORG_CONTACT_ROLES (stores the contact’s purpose or role). The address details of Banks and Bank Branches will be in HZ_LOCATIONS (stores addresses) and HZ_PARTY_SITES (stores party sites).

The new table CE_BANK_ACCOUNT stores bank account attributes while the CE_BANK_ACCT_USES_ALL table stores the bank account use attributes specific to Operating Unit (AR, AP) and Legal Entity (Treasury).

The accounting data pertaining to the bank account use will be stored in the CE_GL_ACCOUNTS_CCID table.

All of the bank, branch and bank account related attributes in AP_BANK_BRANCHES and AP_BANK_ACCOUNTS_ALL tables will be upgraded to HZ_PARTIES and the new tables in Cash Management.

Within TCA model, here is various attributes how they fits inside the model.

Hey did you notice there is one thing that keep changing since last 2 releases …the bank. We have seen there was once from pre 10.x to 11i when supplier bank separated from suppliers data and now its again in R12 when it become part of TCA.This time , because of changing business need and high demand of global partners working model. Not only your company is operating globally,your partner too operating global,then why not use them. In typical business cost model, if corporate office is using Citibank for payroll for USA operation then why not Citibank Singapore branch is used for payroll for Singapore if they are operating there. Sound bit low…why ..

As we aware the key message of R12 while release was .

• Think Globally - using business intelligence and analysis tools
• Work Globally - using the global capabilities of the applications
• Manage Globally - using the latest system architecture and middleware

so what , think globally and work globally is factor driving for the changes .This release have witness the great changes ever into the bank model. Now the bank accounts is attached to your legal entity level rather than Operating Unit in which current and existing versions Offers. This makes bank with strong capability to pay across operating units. More over its important to understand banks accounts can be shared by applications and can be designed for use by Payables, Receivables and Payroll.

What is new in R12 for Bank

These changes make easier and more reliable by

• Single access point
• Single Legal Entity ownership
• Usage rights granted to one or more Organizations
  • Reconciliation option defined at Bank Account level
  • More flexibility and control

Take a looks of some of the releases

Release 10.6 character/10.7 NCA to 11i
How 10.x supplier banks are mapped to 11i

Release 11i

These are tables which hold the bank details irrespective of supplier or internal banks.

Comparing the 11i Vs R12

If we compare the bank with 11i vs R12, we can notice the bank was utilized into three different places , finance ,payroll and treasury, which requires altogether a different setup. It was one of the big issues with integration aspect, as significant problem was recognized once the Expense management and payroll uses same bank for the respective person.

There was a common question/confusion between the Integration Existence between Bank Data in Accounts Payable and Bank Data In Payroll ?

As discussed above , you know most of release of 11i family of oracle Application does not have integration between HR and AP for bank account data.

We have notice in 11i there was functionality in which Payables in which we will create an employee type supplier from HR data and it will contain name and address info but not bank information. The reason for this is that HR/Payroll does not store the bank information in a standard way that makes the integration possible.

So now r12 this was well taken care and integration is built. There are plans under way for all bank account data models in the various products to be consolidated in the new TCA architecture. The Cash Management team is working on this project. Payables and HR/Payroll are working so that the eventual idea will be that you can set up bank accounts in one place and then indicate the usage (pay, expense reimbursement, etc).

For understanding it is comparison between 11i and release 12 , where TCA community take cares of every things.

Release 12 , what is new than

Bank Accounts will be stored in a new table called CE_BANK_ACCOUNTS and will be located at a Bank Branch.

The new table which hold the bank information are as:

1. CE_BANK_ACCOUNT:stores bank account attributes
2. CE_BANK_ACCT_USES_ALL : This stores the bank account use attributes specific to Operating Unit (AR, AP) and Legal Entity (Treasury).
3. CE_GL_ACCOUNTS_CCID :The accounting data pertaining to the bank account use will be stored in the table.

This new data model allows the bank and bank branch entities to be defined separately allowing users to establish a hierarchical relationship between them.

Missing link between Supplier And Supplier Banks?

You should know

• The link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id.
• The link between PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.
• When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS
• When a bank Is Created, the banking information will be stored in

IBY_EXT_BANK_ACCOUNTS IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id

• When the Bank is assigned to Vendors then it will be updated in HZ_CODE_ASSIGNMENTS.

HZ_CODE_ASSIGNMENTS.owner_table_id = IBY_EXT_BANK_ACCOUNTS.branch_id.

Internal Bank Accounts & Supplier and Customer Bank Accounts in R12

Internal Bank Accounts
In Release 12, each internal bank account is assigned to a Legal Entity. Any or all operating units associated with that legal entity are permitted to use that bank account.
Supplier and Customer Bank Accounts
In Release 12 provides a centralized repository for suppliers’ and customers’ bank account and credit card information. All data is stored in one, secure place, providing shared service centers and collection departments consistent information that they need.

Any comment guys..

In Oracle supplier can be entered from three different place.These are the Setup required to perform for a suppliers.

• Supplier Accounts
• Taxes
• Payments
• Term Date Basis
• Due Date Basis
• Invoice Currency
• Holds

In Oracle these are the important features through the screen:

• Enter Suppliers
• Enter employees as Suppliers
• Merge duplicate Suppliers

Supplier Data Entry

Other Details

• Classification Information (Significant for type and employee name)
• Parent subsidiary relationship
• Supplier bank information
• Appropriate GL accounts information
• Record tax information
• Hold conditions to supplier site (rather than on individual invoices)

and these are the optional field

• Tax payer ID
• VAT registration number
• Inactive date (to prevent invoice/PO entry after a certain date)

Important Table that holds supplier

• PO_VENDORS : this hold a suppliers information

Some important columns:

• • SEGMENT1 – SYSTEM GENERATED OR MANUALLY ENTERED NUMBER FOR SUPPLIER (PO_UNIQUE_IDENTIFIER_CONTROL)
  • VENDOR_ID – SYSTEM GENERATED UNIQUE NUMBER
  • CREDIT STATUS –GOOD/POOR
  • ORGANIZATION_TYPE_LOOKUP_CODE
  • VENDOR_TYPE_LOOKUP_CODE – VENDOR/EMPLOYEE (EMPLOYEE_ID)
  • ONE_TIME_FLAG
• PO_VENDOR_SITES_ALL : This hold corresponding site information Contains Details of the site like Full Address/Work of site
  and its link with vendor_id.Important columns includes:
  • PURCHASING_SITE_FLAG, VENDOR_SITE_CODE, RFQ_ONLY_SITE_FLAG, PAY_SITE_FLAG,HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG
• PO_VENDOR_CONTACTS : This hold supplier contact information for respective sites.

How they connected

These are typically connected as:

Take a note Segment1 is holding suppliers Number.

Quick query for new babies

select * from po_vendors
where segment1 = ‘1001‘;

select *
from po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = ‘1001′
and pov.vendor_id = pvs.vendor_id;

select *
from po_vendor_contacts pvc,
po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = ‘1001′
and pov.vendor_id = pvs.vendor_id
and pvs.VENDOR_SITE_ID = pvc.vendor_site_id;

Supplier Interface

Yes, that was one of the enhancement in oracle 11.5.10 release. Prior to that version, there was no interface or Public exist for supplliers, thus it was a tricky to handle supplier. As we have already seen AP availability of AP some time back.

If you are having 11.5.10+ version,and if you have requirement to create supplier from your legacy data than use Supplier Interface, life will be easier. Is n’t.

How we start

This consist in 2 steps process:

Step 1. Populate the new interface tables with data. This can be done via SQL*Loader or typically used to load data in a table.

Step 2. Run the Supplier Open Interface Request Set (FNDRSSUB1703)

Alternatively, run the individual import programs to load one table at a time

Some important notes for suppliers Interface

• When you load the data it should be ‘NEW’ initially. After the Supplier Sites Open Interface is run the status gets changed to PROCESSED’/'REJECTED’.

The entire flow can be shown as:

A Note with R12 for Suppliers

As we know in R12 Supplier is part of TCA , thus the link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id. The link between
PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.

When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS.

Do you know, there are many default value which can be controlled at set up level. Here is the explanation based out of close microscopic view of default values of Account Payable.

Do we have any General Setup Hierarchy for Default values???

Yes , we have a General Setup Hierarchy for Default values, which can be typically described as:

AP takes care of all payments in Oracle Applications. This module has active interaction with Purchasing and General Ledger modules.

The different functionality of the Payables module are:

• Set up Supplier.
• Enter, review and Approve invoices.
• Pay invoices and reconcile payments to bank a/c.
• Enter and apply pre-payments.
• Create journal entries for posting to GL.

An top level overview of the module can be given as :

1.Enter Suppliers

This step consist of set up as well as maintenance of existing one.During the setup, we need to do setup for

• Supplier Accounts
• Taxes
• Payments
• Term Date Basis
• Due Date Basis
• Invoice Currency
• Holds

Importantly the other features in this area consist is :

• Enter employees as Suppliers : this is for managing and controlling Expense report
• Merge duplicate Suppliers

2.Invoices

This consist Setup for;

• Distribution Set
• Tax
• Setup tolerance, matching options

3. Approve invoice

This required set up for rule, so that approval and validation process can validate based on some parameter.

4.Payments

This requires set up for payment related dependency which include ;

• Format
• Bank
• Bank account
• Build program
• Payment Methods
  • Manual
  • Quick Check
  • Automatic Check

Take a note in manual and Quick check, you have to simply enter payment details and correlate to invoices. Automatic check is applicable for batch payments.

5. Create Journal entries

Typical flow for creating Journal entries is summarized as:

And here is final note of accounting Entries of AP :

• During Invoicing
  • Expenses A/C Dr to vendor liability
• During Payment
  • Vendor liability A/C Dr to Cash/Bank A/C

…And, This is Four Rule of Payables

• You must have a vendor to do anything
• Every Invoice must be fully Charged out before paid or posting to GL.
• All Payments must have an invoice
• Every Invoice must run through approval process.

Hope this is a brief overview of the Payable business flow, will discuss & share some more in next couple of post.Till then keep watching this space.

This is in continuation to API’s availability,Here are the API’s availability in AP.

Credit Card Transaction Processing

• Manual Credit Card Entry: A form is available for entry through the Oracle Payables application.
• Credit Card Transaction Interface Table: This process loads transaction data from the credit card issuer (American Express, Diner’s Club, MasterCard and Visa) into Oracles AP module. Oracle Payable then uses this data to confirm transactions with employees. Once the transactions have been approved the credit card transaction lines are imported into Payables Open Interface using the Credit Card Invoice Interface Summary program. The credit card transactions are then available for final import to Oracle Payables where the transactions become invoices.

Creating Invoices
In Oracle Payables four methods are available for entering an invoice into the system.

• Manual Invoice Entry: Two methods for manual invoice entry exist. Using Oracle Payables system Invoice Workbench which includes the Invoice Batches window and Distribution window users can enter complex invoices, and invoices requiring online validation. In addition this functionality is useful in processing invoices that require special attention such as immediate payment.
• The second method of manual invoice entry, Invoice Gateway, is used when entering high volumes of invoices. Generally these invoices do not require online validation or defaulting of accounting information. After entering invoices using the gateway, the invoice import must be run, at that time validation and defaulting is performed. This method of entry can be used in lieu of invoice interfaces. Although, using the gateway can accomplish similar results to that found with an interface process, manual entry is no substitute.
• Automatic Invoice Creation: Oracle Payables provides functionality to produce periodically recurring invoices. This functionality may be used to substitute manual invoice entry or the payables open interface. In the case of recurring invoices that would normally come from an external system choosing to setup a recurring invoice formula may be an alternative.
• Using Payables Open Interface: Choose this method for processing large volumes of invoices that originate in external system. Additionally, this process is useful in handling employee expense reports generated through self-service applications, expense reports entered by the Payables Department, invoices for employee credit card expenses, Oracle Projects billing and time and expenses, and EDI invoices transferred from the Oracle EDI gateway.

Purchase Order Matching

Oracle Payables can be implemented without Oracle Purchasing, to allow this several purchasing tables are installed initially which must be populated using custom interfaces. Only two-way matching is available when data is loaded using the interface method. Two-way approval verifies that purchase order and invoice information match within your tolerances as follows: Quantity billed on the purchase order shipment is less than or equal to Quantity ordered on the purchase order shipment. Invoice price on the purchase order shipment is less than or equal to Purchase order price on the purchase order shipment.
Receipts can only be matched in an install that also includes Oracle Purchasing.
The only method for interfacing data into the purchasing tables described in this section is through a customized interface from an external source. Note: Interfacing this information is not supported by Oracle.

• Loading PO_HEADERS: Each record in this table represents a purchase order, which is an order for goods or services from a single supplier. Each purchase order may have multiple lines (PO_LINES). In addition, each blanket purchase order may have multiple blanket releases (PO_RELEASES), which release an amount from the blanket.
• Loading PO_LINES: Each record in this table represents a purchase order line, which identifies the items and unit price for the goods ordered on a purchase order. Each purchase order line may have multiple shipments (PO_LINE_LOCATIONS).
• Loading PO_LINE_LOCATIONS: Each record in this table represents a purchase order line, which identifies the items and unit price for the goods ordered on a purchase order. Each purchase order line may have multiple shipments (PO_LINE_LOCATIONS).
• Loading PO_DISTRIBUTIONS/PO_DISTRIBUTIONS_AP_V: Each record in this table/view represents a purchase order distribution, which identifies the account charged for the items on a purchase order shipment.
• Loading PO_RELEASES: Each record in this table represents a blanket release for a purchase order. A blanket release may create multiple shipments.
• Loading AP_INVOICES/AP_INVOICE_DISTRIBUTIONS: Each purchase order shipment can be matched to multiple invoices(AP_INVOICES), and a single invoice may be matched to multiple purchase order shipments. When you match an invoice to a purchase order shipment, Payables creates an invoice distribution AP_INVOICE_DISTRIBUTIONS) from each purchase order distribution on the shipment. When you match an invoice to a single purchase order distribution, Payables creates a single invoice distribution from the purchase order distribution.

Vendor Interface

Existing and new vendors must be loaded into the Oracle Payables application to support payables activities. Two methods may be used to enter vendors into Oracle Applications, manual entry and direct loading using a customized loader program.

• Manual Vendor Loading: Use the Enter Vendor window. Using this functionality vendor header information as well as vendor site information can be entered.
• Vendor Interface: When interfacing vendors into Oracle Applications two tables must be loaded the PO_VENDORS and PO_VENDOR_SITES_ALL. The vendor table contains all header information and the site table contains information about each the vendor locations such as “ship to” and “bill to”. Using this method makes sense when the volume of new vendors is large. Note: Interfacing this information is not supported by Oracle.
• Those who are in 11.5.10 they will find yet another method ie by interface.
  You have to Load data into the staging tables first ie.AP_SUPPLIERS_INT - Supplier Information
  AP_SUPPLIER_SITES_INT - Supplier Sites Information ,AP_SUP_SITE_CONTACT_INT - supplier Contact details This uses Vendor ID, Vendor Site Code to relate the contacts to specific vendor. Once data get loaded three interface program should be kicked out which is as
  • Supplier Open Interface Import.
  • Supplier Sites Open Interface Import
  • Supplier Site Contacts Open Interface Import

This is new enhancement we have seen in 2nd generation i-expense version, where there is provision of setting up of Itemization rule. In layman language, it can be explained as ..when an employee stays in a hotel on a business trip, he receives one receipt for his stay. That one receipt includes hotel stay, food, room service, laundry and other personal expenses (of course.). The Expense Itemization feature allows an employee to enter one receipt for the whole amount and lets them itemize the same.

This feature allows you to require itemization and determine which expense types for the itemization are independent of each other. If itemization is required, you can also identify into which expense types a receipt can or should be itemized. This expedites the expenses entry process and ensures more accurate accounting. This also helps in easy reconciliation for your finance department, and also no argument for clarifying personal stuff.

Take a note, oracle 11.5.10.2 does not have any seeded report that will have drill down information for itemized claim. So don’t get surprised , if Finance will ask you for detailed Itemized report.

In the Procure to pay cycle, we all know it start from requisition to payment, thus it is equally important to understand the relation between PO and Invoice, which of course is subject to Matching based on tolerances level set.

How many ways for matching
They are mainly 3 ways:

• 2-ways
• 3-ways
• 4-ways

How to explain

2-way matching

This verifies verifies that Purchase order and invoice information match within your tolerances as follows:
Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered

3-way matching

This verifies that the receipt and invoice information match with the quantity tolerances defined:
Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered
Quantity billed <= Quantity received

4-way matching

This verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered
Quantity billed <= Quantity received
Quantity billed <= Quantity accepted

How to relate between PO and invoice tables:

Here are the entity relationship diagram, explaining 2 way and 3 way matching condition.

In summary

Lets have something informative for some important reports to reconcile Accounts Payable sub ledger to the General Ledger.

1. Accounts Payable Trial Balance

(Normally this report is run at the end of this period and the prior period. This represents your opening and closing AP Trial Balance)

The closing value per AP control account in this trial Balance should agree to the corresponding Account in the General Ledger Trial Balance.

If it does not, you will need to investigate why.

Run the following reports to assist you in the process:

o Posted Invoices report
o Posted Payments report

The Opening Account Payable Trail Balance (AP TB)

+ The Posted Invoice
- The Posted payments
= The Closing AP TB

The above formula is a key formula in attempting to identify the source of potential problems.

Note that in above:

The Aging Invoice Report Total should equal
The AP Trial Balance Total +
The Total of Swept (Unposted Transactions)

You may wish to perform this reconciliation as a further month end reconciliation check.

2.Invoice Aging Report

This is yet another important report which shows you a complete list of all unpaid invoices on the system irrespective of whether they are due for payment or not, whether they are on hold or not. This report groups invoices by how many days they are over due. The grouping of invoices by age past due is dependent on the Aging bucket selected for the report.

Running the Invoice Aging report is like running any other report. The key points to note are that
1. The report name to choose is “Invoice Aging Report”
2. It is recommended that you use the Standard Aging bucket.

Accounts Payable Trial Balance

The Accounts Payable Trial Balance shows you a complete list of all unpaid invoices on the system that has been transferred to the General Ledger, irrespective of whether they are due for payment or not. This report will assist with the month end reconciliation process.

Apart of the above, these are the additional reports which helps in completing account payable month end close procedure.

a) Invoice on Hold Report

b) This report normally run to identify the invoices with Holds that need releasing.

c) Payables Accounting Entries Report

d) Invoice validation report

 Normally to approve multiple invoices run the “Invoice Validation” request.once this report request runs the payables approval process and produces a report detailing the types and number of holds placed on invoices as a result of this run.

e) Unaccounted Transactions Report

f) Final Payment Register

g) Reconciliation Reports

Lets summarize some important steps that highlight the activity required to perform the month end close in account payable.

• The very first step is to submit and review the Invoice Validation and Invoice on Hold Report.

From the output, determine if there are invoices on hold; Resolve any holds on invoices. Note that depending on the type of hold on an invoice, it may be impossible to transfer the Invoice to the General Ledger.

• Make sure that if you still have any invoices on hold, they are invoices you intend to roll over to the following month. Once you are satisfied with your review, submit the Payables Transfer to General Ledger Program to transfer invoice and payment Accounting information to the General Ledger. This process will auto generate the Payables Accounting Process request. This creates all the accounting entries for Oracle Payables.
• Once your concurrent request get completes and you are satisfied that your journal has been Transferred, you are ready to reconcile your month end numbers. Sweep any unposted invoices to the following month.
• After your reconciliation, close the current period and open the following period.
  For This you should

Navigation > Accounting > Control Payables Periods

• An Accounts Payable Reconciliation is required to record reconciling items when reconciling Accounts Payable to the General Ledger.

The other activity, which involves accounts Payable User/Manager, is responsible for:

• Preparing all journal entries pertaining to Accounts Payable transactions, accrued Accounts Payable, and adjustments for Accounts Payable aging reconciliations
• Posting Accounts Payable transactions to the General Ledger
• Reconciling Accounts Payable accounts to the General Ledger
• Verifying current Accounts Payable aging
• Locating and voiding missing checks
• Correcting account distribution errors.