Wednesday, February 6, 2013

ORG_ID and Multi Org Concept In Oracle

In this Oracle Apps training article, we will learn about org_id. I hope that you have read and understood the significance of profile options that we discussed in the earlier chapter.

Before I tell you what is org_id, lets do some questions & answers:-

Why do we need org_id
In any global company, there will be different company locations that are autonomous in their back office operations. For example, lets take the example of a gaming company named GameGold Inc that has operations in both UK and France.

Please note the following carefully:-
1. This company(GameGold Inc) has offices in both London and Paris
2. UK has different taxation rules that France, and hence different tax codes are defined for these countries.
3.GameGold Inc has implemented Oracle Apps in single instance(one common Oracle Apps database for both UK & France).
4. When "UK order entry" or "UK Payables" user logs into Oracle Apps, they do not wish to see tax codes for their French sister company. This is important because French tax codes are not applicable to UK business.
5. Given the single database instance of GameGold Inc, there is just one table that holds list of taxes. Lets assume that the name of the Oracle table is ap_tax_codes_all
6. Lets assume there are two records in this table.
Record 1 tax code -"FRVAT"
Record 2 tax code - "UKVAT"
7. Lets further assume that there are two responsibilities
Responsibility 1 - "French order entry".
Responsibility 2 - "UK order entry"
8. Now, users in France are assigned responsibility 1 - "French order entry"
9. Users in UK will be using responsibility named "UK order entry"
10. In the Order Entry screen, there is a field named Tax Code(or VAT Code).
11. To the French user, from the vat field in screen, in list of values UKVAT must not be visible.
12. Also, the "French order entry" user should only be able to select "FRVAT" in the tax field.
13. Similarly, UK order entry user, that uses responsibility "UK Order Entry", only "UKVAT" should be pickable.

How can all this be achieved, without any hard coding in the screen.
Well....the answer is org_id

ORG_ID/Multi-Org/Operating Unit are the terminologies that get used interchangeably.

In brief steps, first the setup required to support this....
The screenshots are at the bottom of the article

1. You will be defining two organizations in apps named "French operations" and "UK Operations". This can be done by using organization definition screen.
2. In Oracle Apps, an organization can be classified as HRMS Org, or Inventory Warehouse Org, or Business Group, Operating Unit Org or much more types. Remember, Organization type is just a mean of tagging a flag to an organization definition.
3. The two organizations we define here will be of type operating unit. I will be using words org_Id & operating unit interchangeably.
4. Lets say, uk org has an internal organization_I'd =101
And french org has orgid =102.

Qns: How will you establish a relation betwee uk responsibility and uk organization.
By setting profile option MO : Operating unit to a value of UK Org, against uk order entry responsibility

Qns: How will the system know that UKVAT belongs to uk org?
In VAT code entry screen(where Tax Codes will be entered), following insert will be done
Insert into ap_vat_codes_all values(:screenblock.vatfield, fnd_profile.value('org_id').
Alternately, use USERENV('CLIENT_INFO')

Next question, when displaying VAT Codes in LOV, will oracle do: select * from ap_vat_codes_all where org_id=fnd_profile.value('ORG_ID')?
almost yes.

Oracle will do the following
1. At the tme of inserting data into multi-org table, it will do insert into (vatcode,org_id) ....
2. Creates a view in apps as below
Create or replace view ap_vat_codes as Select * from ap_vat_codes_all where org_id = fnd_profile.value('ORG_ID')
3. In the lov, select * from ap_vat_codes ,

If the above doesn't make sense, then keep reading.

May be quick revesion is necessary:_
1. In multi org environment(like uk + france in one db), each Multi-Org Table will have a column named org_id. Tables like invoices are org sensitive, because UK has no purpose to see and modify french invoices. Hence a invoice table is a candidate for ORG_ID column.
By doing so, UK Responsibities will filter just UK Invoices. This is possible because in Apps, Invoice screens will use ap_invoices in their query and not AP_INVOICES_ALL.
2. Vendor Sites/Locations are partitined too, because UK will place its ordersfrom whereas france will raise orders from These are called vendor sites in Oracle Terminology.
Any table that is mutli-org (has column named org_id), then such table name will end with _all
4. For each _all table , Oracle provides a correspondong view without _all. For examples create or replace view xx_invoices as select * from xx_invoices_all where org_id=fnd _profile.value('org_id').
5. At the time of inserting records in such table, org_id column will always be populated.
6. If you ever wish to report across all operating units, then select from _all table.
7. _all object in APPS will be a synonym to the corresponding _all table in actual schema. For example po_headers_all in apps schema is a synonym for po_headers_all in PO schema.
8. When you connect to SQL*Plus do the below
connect apps/apps@dbapps ;
--assuming 101 is French Org Id
execute dbms_application_info.set_client_info ( 101 );
select tax_code from ap_tax_codes ;
---Returns FRVAT

--assuming 102 is UKOrg Id
execute dbms_application_info.set_client_info ( 102 );
select tax_code from ap_tax_codes ;
---Returns UKVAT

Order to Cash - Technical Flow in R12

This article lists the Order to Cash Flow in R12, with technical flow of data as taught in our FocusThread trainings. 

 1. Order Entry
This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.
  • Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all flow_status_code = ENTERED, booked_flag = N), Primary key=HEADER_ID
    • No record exist in any other table for this order till now.
  • Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. oe_order_lines_all (flow_status_code = ENTERED, booked_flag = N, open_flag = Y) Primary key= LINE_ID

2.Order Booking
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.
  • oe_order_headers_alL (flow_status_code as BOOKED, booked_flag updated to Y)
  • oe_order_lines_all (flow_status_code as AWAITING_SHIPPING, booked_flag updated Y)
  • wsh_delivery_details (DELIVERY_DETAIL_ID is assigned here, released_status ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
  • wsh_delivery_assignments (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in wsh_delivery_details, DELIVERY_ID remains blank till this stage)
*In shipping transaction form order status remains "Ready to Release".
At the same time, Demand interface program runs in background And insert into inventory tables mtl_demand, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

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_demand and mtl_reservations table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:
  • If step 3 is not done then MTL_RESERVATIONS gets updated now.
  • wsh_new_deliveries (one record gets inserted with SOURCE_HEADER_ID= order header ID, status_code=OP =>open)
  • wsh_delivery_assignments (DELIVERY_ID gets assigned which comes from wsh_new_deliveries)
  • wsh_delivery_details (released_status ‘S’ ‘submitted for release’)
  • (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
  • Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id, this table holds the record temporally)
  • MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
  • MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )
*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5.Pick Confirm/ Move Order Transaction
Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.
  • MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
  • oe_order_lines_all (flow_status_code ‘PICKED’ )
  • mtl_transaction_accounts
  • wsh_delivery_details (released_status becomes ‘Y’ => ‘Released’ )
  • wsh_delivery_assignments
  • MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
  • MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6.Ship Confirm
Here ship confirm interface program runs in background. Data removed from wsh_new_deliveries.
The items on the delivery gets shipped to customer at this stage.
  • oe_order_lines_all (flow_status_code ‘shipped’)
  • wsh_delivery_details (released_status ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
  • WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
  • 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
  • MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
  • MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7.Enter Invoice
After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. 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. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.
ra_interface_lines_all (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
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 order number and line_id of the orders)

8.Complete Line
In this stage order line level 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”)

In this post, we have tried to describe the process flow of customer conversion in graphical manner and Oracle Standard API’s to load the data into R12 Target System. The methodology described here is one of the proven strategies which we used in our conversion project. This will give you an approach for your customer requirements.

Functional Setups need to be considered/verified
Below are the important entities need to be setup before running the customer conversion loading program in the target system
  1. External Banks
  2. Orig System
  3. Customer profile classes
  4. Collectors
  5. Statement cycles
  6. Grouping rules
  7. Payment terms   
  8. Dunning Letter Sets
  9. Various lookups used in party/customer account related entities
10.  Loading Party Data:
11.  There are lot of party related entities like party, addresses, address uses, code assignments, org contacts etc. Here we have tried describe the methodology to load different party related entites via oracle standard API’s.

12.  Oracle Standard API’s for each step:
Oracle Standard API
Load organization party
Load person type party
Load organization classifications
Load locations
Load party sites
Load party site uses
Load org contacts
Load org contact roles
Load contact points

Tables/views involved Brief Overview:
Table/View name
Stores the payment method information of  the customer
Contains the payment term details
Stores dunning letter master details
Statement cycle details
Master table for customer account profile classes
Master table for collector information
Master table for hierarches details related to Lockbox program
Stores the account code combinations
Stores the party information
Master table for party site
Master table for addresses
Master table for party sites
Master table for customer account details
Master table for customer account sites
Master table for customer account site uses
Master table for customer account/site profiles
Master table for customer account/site profile amounts
Master table for customer account/site contacts
Master table for customer account/site contact responsibilities
Master table for storing party relationship details
Master table for party organization classifications
Master table for storing the organization type party profiles
Master table for storing the person type party profiles
Master table for storing the organization contact details
Master table for storing the organization contact roles responsibilities
Master table for storing the party/party site contact point details

Loading Customer Account Data:
There are lot of customer account related entities like customer accounts, customer account sites, customer account site uses, customer profiles, customer profile amounts etc. Here we have tried describe the methodology to load different customer account related entites via oracle standard API’s.

Oracle Standard API’s for each step:
Oracle Standard API
Load customer accounts
Load customer account bill to sites
Load customer account ship-to sites
Load customer account bill-to site uses
Load customer account ship-to site uses
Load customer profiles
Load customer account profile amounts
Load customer account roles
Load customer account role responsibilities
Load customer account payment methods
Load customer account relations

Query to Fetch the Payment attribute details of the customer account level or customer account site level. At data level, customer account site level payment attribute record will have its column  acct_site_use_id populated.

SELECT iep.ext_payer_id,
       ou.NAME org_name,
  FROM iby_external_payers_all iep,
       hr_operating_units ou
 WHERE iep.org_id          = ou.organization_id(+)
   AND iep.cust_account_id = vl_cust_account_id
   AND iep.acct_site_use_id= vl_acct_site_use_id

Query to find the receipt methods associated with Customer Account/Site

SELECT rcrm.cust_receipt_method_id,
  FROM ar_cust_receipt_methods_v rcrm
 WHERE 1 = 1
   AND rcrm.customer_id = :vl_cust_account_id
   AND rcrm.site_use_id = :vl_cust_site_use_id

Query to fetch Party, Person profile and Organization profile details

      SELECT hp.*
       FROM  hz_parties               hp,
             hz_person_profiles       hpp,
             hz_organization_profiles hop
      WHERE  hp.party_id                = hpp.party_id(+)
      AND    hp.party_id                = hop.party_id(+)
      AND    hop.content_source_type(+) = 'USER_ENTERED'
      AND    hpp.content_source_type(+) = 'USER_ENTERED'
      AND    hop.effective_end_date     IS NULL
      AND    hpp.effective_end_date     IS NULL
      AND    hp.status                  != 'M'
      AND    hp.party_name               = 'SHAREORACLEAPPS';

Query to fetch customer account details

      SELECT cust.*,
       pl.NAME price_list_name,
       org.NAME warehouse_name,
       hcpc.NAME AS cust_profile_class_name
  FROM hz_cust_accounts cust,
       so_price_lists pl,
       hr_all_organization_units org,
       hz_cust_profile_classes hcpc,
       hz_customer_profiles hcp
 WHERE cust.price_list_id  = pl.price_list_id(+)
   AND hcp.cust_account_id = cust.cust_account_id
   AND hcp.site_use_id IS NULL
   AND hcp.profile_class_id = hcpc.profile_class_id
   AND cust.warehouse_id    = org.organization_id(+)
   AND cust.cust_account_id = :vl_cust_account_id

Query to fetch Party site, Customer Account Site,  address details

SELECT hps.*,
       ht.global_timezone_name timezone_name,
       hcas.org_id acct_org_id,
       ou.NAME acct_org_name,
  FROM hz_locations hl,
       hz_party_sites hps,
       hz_cust_acct_sites_all hcas,
       hr_operating_units ou,
       hz_timezones ht,
       hz_cust_accounts hca
 WHERE hl.location_id      = hps.location_id
   AND hps.party_site_id   = hcas.party_site_id
   AND ou.organization_id  = hcas.org_id
   AND hca.cust_account_id = hcas.cust_account_id
   AND ht.timezone_id(+)   = hl.timezone_id
   AND hps.status          != 'M'
   AND hcas.cust_account_id = :vl_cust_account_id
   AND hcas.cust_acct_site_id = :vl_cust_acct_site_id


Query to fetch customer Profile Details (HZ_CUSTOMER_PROFILES)

The profile information available in the HZ_CUSTOMER_PROFILES can be created in three levels namely Party, Customer Account  and Customer Account Site. The values available in the three columns of the table HZ_CUSTOMER_PROFILES PARTY_ID , CUST_ACCOUNT_ID,SITE_USE_ID dictates the level of profile information.

Party Level Profile:
Party_id                = vl_party_id
Cust_account_id = -1
Site_use_id           = NULL

Customer Account Level Profile:
Party_id                = vl_party_id
Cust_account_id = vl_cust_account_id
Site_use_id           = NULL

Customer Account Site Level Profile:
Party_id                = vl_party_id
Cust_account_id = vl_cust_account_id
Site_use_id           = vl_cust_site_use_id

A Query with joins to the other master table is given below,

SELECT cp.cust_account_profile_id,
       col.NAME collector_name,
       cpc.NAME profile_class_name,
       term.NAME standard_terms,
       cyc.NAME statement_cycle_name,
       hier.hierarchy_name autocash_hierarchy_name,
       grp.NAME grouping_rule_name,
       hier_adr.hierarchy_name autocash_hierarchy_name_adr,
  FROM hz_customer_profiles cp,
       ar_collectors col,
       hz_cust_profile_classes cpc,
       ar_dunning_letter_sets dun_set,
       ar_statement_cycles cyc,
       ar_autocash_hierarchies hier,
       ra_grouping_rules grp,
       ra_terms term,
       ar_autocash_hierarchies hier_adr
 WHERE cp.collector_id          = col.collector_id
   AND cp.profile_class_id      = cpc.profile_class_id(+)
   AND cp.dunning_letter_set_id = dun_set.dunning_letter_set_id(+)
   AND cp.statement_cycle_id    = cyc.statement_cycle_id(+)
   AND cp.autocash_hierarchy_id = hier.autocash_hierarchy_id(+)
   AND cp.grouping_rule_id      = grp.grouping_rule_id(+)
   AND cp.standard_terms        = term.term_id(+)
   AND cp.autocash_hierarchy_id_for_adr = hier_adr.autocash_hierarchy_id(+)
   AND cp.party_id              = vl_party_id
   AND cp.cust_account_id       = vl_cust_account_id
   AND cp.site_use_id           = vl_site_use_id

Query to Fetch the customer account related bank details

SELECT ipiu.instrument_payment_use_id,
       DECODE (ipiu.order_of_preference, 1, 'YES', 'NO') primary_flag,
  FROM iby_pmt_instr_uses_all ipiu,
       iby_ext_bank_accounts ieb,
       iby_external_payers_all iep,
       ce_bank_branches_v cb
 WHERE ieb.ext_bank_account_id = ipiu.instrument_id
   AND ipiu.ext_pmt_party_id   = iep.ext_payer_id
   AND cb.bank_party_id        = ieb.bank_id
   AND iep.cust_account_id     = :vl_cust_account_id
   AND iep.acct_site_use_id    = :vl_cust_site_use_id;

1 comment:

pavan kumar said...

hi Raju,

I am not able to see the screen shots your are sharing...should i need any settings on my machine to view them.


Post a Comment

Best Blogger TipsGet Flower Effect