Thursday, April 14, 2011

Understanding data flow for “Standard Order”

Normally standard sales order can be split into nine sub steps , which jointly carried out by some module like INV,OM, Pricing, Shipping and AR. Lets take each sub steps with data flow conditions. These are based out of the flow which is available in 11.5.10.2.
1. Order Entry
This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.
  • oe_order_headers_all (Here the flow_status_code as entered)
  • oe_order_lines_all (flow_status_code as entered) ( order number is generated)
    dataflow
2.Order Booking
This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.
  • oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
  • oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated)
  • wsh_new_deliveries (status_code OP open)
  • wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand
3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.
4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.
Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:
  • oe_order_lines_all (flow_status_code ‘PICKED’ )
  • wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
  • mtl_txn_request_headers
  • mtl_txn_request_lines
    (move order tables.Here request is generated to move item from saleble to staging sub inventory)
  • Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id
5.Pick Confirm
Items are transferred from saleble to staging Subinventory.
  • mtl_material_transactions
  • mtl_transaction_accounts
  • wsh_delivery_details (released_status ‘Y’‘Released’ )
  • wsh_delivery_assignments
6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries
  • oe_order_lines_all (flow_status_code ‘shipped’)
  • wsh_delivery_details (released_status ‘C’ ‘Shipped’)
  • mtl_transaction_interface
  • mtl_material_transactions(linked through Transaction source header id)
  • mtl_transaction_accounts
  • Data deleted from mtl_demand,mtl_reservations
  • Item deducted from mtl_onhand_quantities
7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details.
  • Invoicing workflow activity transfers shipped item information to Oracle Receivables.
  • ra_interface_lines_all (interface table into which the data is transferred from order management)T
  • Then Autoinvoice program imports data from this
  • Table which get affected into this stage are recievables base table.
    • ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
    • ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)
8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.
  • oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.
These are the table get affected in this step.
  • oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)
These are the typically data flow of a order to cash model for a standard order.

Tuesday, April 5, 2011

Welcome to R12 Account Payable

As we learnt during Release 12, the E-Business Suite has couple of new products like Subledger Accounting, E-Business Tax thus significant changes have been observed in Account Payable data module as some of functionality is shared by some other products. Thus it is important to understand what is new. I would like to briefly outline the details of some of new changes and underlying impact on the objects. More details can be found in R12 release documents published by Oracle a month ago.
Let’s have a dissection view of R12 payable, with some of its core objects
Supplier
We have seen in 11i
  • Suppliers defined in AP.
  • Supplier contacts replicated for each supplier site.
Where as in R12
  • Supplier becomes as TCA Party.
  • Suppliers Sites as TCA Party Site for each distinct address.
  • Contacts for each supplier/address , it means Single supplier address and contact can be leveraged by multiple sites, for each OU
    • A single change to an address can be seen instantly by all OUs
    • No longer need to manually 'push' updates across OUs.This can be best understood by the figure below.
SuppliersContact
Then the question is what will happen if any one can come from existing financial products. The Impact from upgrade can summarize as:
1. When we upgrade supplier tables replaced with backward compatible views.
2. One party site for each distinct supplier site address
Country and address line1 are required, this is because creation of suppliers in Party in TCA data model would requires Country and address information, but it also understood if there is no country or address line 1 specified for a supplier site in cases when upgrades takes place, Payables derives the country based on the most frequently used operating unit of the Supplier's historical transactions.
3. Employee as suppliers: address NOT migrated to party site in TCA remains in Oracle HR for data security reasons.
As we know in 11i employees are part of internal supplier's record in order for Oracle Payables to create payments for their expense reports. Employees defined in Oracle Human Resources and associated with an Oracle Payables supplier record have existing party information. During the upgrade, Oracle Payables updates the existing party information to have a party usage of supplier but it does not migrate the employee address to the party site in TCA, they remain in Oracle Human Resources for data security reasons.
4. Utilize TCA Party relationships for franchise or subsidiary and its parent company.
Invoice
Till 11i version, we have seen invoices:
  • Had only distributions line.
  • Allocation of freight and special charges are captured at the distribution level only
  • Tax and payment and Project accounting Payment was captured through global Descriptive Flexfields.
But in R12,
1. Invoice Lines as a new additional line accommodated in Invoice data model.
12RInvoice
Because of introduction of invoice line there is significant improvement of data flow with n other oracle modules like
  • Fixed Asset - Asset Tracking
  • Business Tax - Tax line
  • Payment - Payment
  • SubLedger Accounting - Accounting

2. Allocate freight and special charges are captured to the lines on the invoice
3. Invoice distributions created at the maximum level of detail similar to 11i.
4. Core functionality

The impact with Upgrade can be summarized as:
1. One invoice line for every distribution in 11i
2. Sub Ledger Accounting requires that Payables transform the invoice distributions to be stored at the maximum level of detail
3. Global Descriptive Flexfields migrated to named columns.
          That's means functional testing is more required while upgrade takes place.
Banks and Bank Details
Now a days corporate treasury role has been greatly enhanced thus picking up a global bank as partner for all banking need is demand of time in global working model. The recent couple of years have seen drastic increase in acquisition and merger of company thus global working as well as global instance get popularity in ERP areana, and this is one of reason of the reason bank data model has been significant changes from 11 to 11i and 11i to R12.
Internal Bank AccountsIn 11i we have seen internal Banks defined in AP and that is shared by AP/AR/CE, Payroll and Treasury and they are bank accounts often replicated in multiple OUs
Where as in R12,
  • Bank and Branch become part of TCA Parties.
  • Internal Bank Account in Cash Management which is owned by a Legal Entity. Here the Operating units have granted usage rights.
Suppliers Bank Accounts
In 11i
  • Banks/Branches defined in AP
  • Bank accounts often replicated in multiple OUs Before
R12
  • Suppliers, Banks and Branches are defined as Parties in TCA
  • Supplier (party's) payment information and all payment instruments (Bank Accounts, Credit Cards) moved into Oracle Payments.
The typical data model for bank can be summarized as:
R12BankDataModel
Impact of Upgrade
1. With Upgrade banks and branches migrated to TCA parties
2. Banks merged if the following attributes are all the same:

  • a. Bank Number
    b. Institution type
    c. Country
    d. Bank admin email
    e. Bank name alt
    f. Tax payer ID
    g. Tax reference number
    h. Description, Effective dates
3. Bank accounts, bank account uses are migrated into cash management.
4. Transactions are stamped with the bank account uses identifiers as part of the upgrade

 Integration with Oracle E-Business Tax
In 11i
  • Oracle standard functionality was based out of User which determines tax by assigning Tax Codes at line level of invoice and Tax rules was controlled at underline code.
  • There was global descriptive flex fields were captured for country-specific tax attributes.
  • More importanta most of the setup performed at OU level.
In R12
  • A new module eBusinessTax determines tax based on facts about each transaction, this is reason why Oracle has introduced additional line information at invoice level.
  • The module "ebusiness Tax" set and configure Tax rules which can be viewed
  • Tax attributes collected in fields on key entities
  • Configure tax rules once per regime and share with your legal entities
Impact of Upgrade
1. Payables Tax setup, Tax Code defaulting rules defined per OU are migrated to eBusiness Tax.
2. OUs migrated to tax content owner in R12
3. Tax information in tax codes are transformed to Regime-Rate flow.
4. E-Business Tax takes information from the AP invoice lines and creates summary and detail tax lines in the E-Business Tax repository.
Multi Org Access Control
MOAC is new enhancement to the Multiple Organizations feature of Oracle Applications.
This feature enables user to access data from one or many Operating Units while within a set given responsibility. Due to this change, all processing and some Reporting in Oracle Payables is available across Operating Units from a single Applications responsibility. Hence you can isolate your transaction data by Operating unit for security and local level compliance while still enabling shared Service centre processing.Data security is maintained using the Multiple Organizations Security Profile, defined in Oracle HRMS, which specifies a list of operating units and determines the data access privileges for a user.
Impact of UpgradeR12 Upgrade does not automatically create security profiles, thus is important if any one want to use Multiple Organizations Access Control, the first things is to define security profiles, then link them to respective responsibilities or users.

R12 Supplier Bank – Techno Functional Guide

Three banks you can manage in EBS
  • House Bank or internal bank
  • External bank for supplier and Customer
    • Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. 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.
  • Intermediary bank for SEPA payment : An intermediary bank is a financial institution that as a relationship with the destination bank (in this case the supplier bank account you are setting up) which is not a direct correspondent of the source bank (the disbursement bank in AP/Payments), which facilities the funds transfer to the destination bank.
You can enter intermediary bank accounts on Suppliers->Entry->Banking Details->Bank Account Details
This is important when paying a foreign supplier from a domestic disbursement account, there may be an intermediary bank used, and it would be set up on the supplier bank account. Although the intermediary bank UI is owned by Payments, the implementation is as embeddable UI components in pages owned by i-supplier Portal (suppliers) and AR/Collections (customers).
dgreybarrow Some information
  1. 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.
  2. Creating a supplier in AP now creates a record in HZ_PARTIES. In the create Supplier screen, you will notice that that Registry_id is the party_number in HZ_Parties.
  3. The table hz_party_usg_assignments table stores the party_usage_code SUPPLIER, and also contains the given party_id for that supplier. Running this query will return if customer was a SUPPLIER or CUSTOMER
  4. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds
  5. IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.
  6. The master record that replaces PO_VENDORS is now AP_SUPPLIERS. PO_VENDORS is a view that joins AP_SUPPLIERS and HZ_PARTIES.
  7. The table that hold mappings between AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID is PO_SUPPLIER_MAPPINGS. Query by party_id.
  8. 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.
dgreybarrowER Diagram(Bank Model)

suplier bank 
Oracle Table Involved
  • IBY_EXTERNAL_PAYEES_ALL : This stores supplier information and customer information
  • IBY_EXT_BANK_ACCOUNTS : This storage for bank accounts
  • IBY_EXT_PARTY_PMT_MTHDS : This storage for payment method usage rules.
  • IBY_CREDITCARD : stores the credit card information for a customer
  • IBY_EXT_BANK_ACCOUNTS :This Stores external bank accounts . These records have bank_account_type = Supplier
  • IBY_ACCOUNT_OWNERS :stores the joint account owners of a bank account
  • IBY_PMT_INSTR_USES_ALL : This stores data from AP_BANK_ACCOUNT_USES_ALL for payment instruments assignments .This information is stored in the following iPayment (IBY) tables: