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. Ans: 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? Ans: 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')? Answer: 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 dell.co.uk whereas france will raise orders from dell.co.fr. These are called vendor sites in Oracle Terminology. 3. 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’)
• MTL_TXN_REQUEST_HEADERS
• MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
• (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_MATERIAL_TRANSACTIONS (LINE_ID goes as TRX_SOURCE_LINE_ID)
• mtl_transaction_accounts
• wsh_delivery_details (released_status becomes ‘Y’ => ‘Released’ )
• wsh_delivery_assignments
• MTL_ONHAND_QUANTITIES
• 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”)

oe_order_HEADERS_all

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:

Step	Oracle Standard API
Load organization party	HZ_PARTY_V2PUB.CREATE_ORGANIZATION
Load person type party	HZ_PARTY_V2PUB.CREATE_PERSON
Load organization classifications	HZ_CLASSIFICATION_V2PUB.CREATE_CODE_ASSIGNMENT
Load locations	HZ_LOCATION_V2PUB.CREATE_LOCATION
Load party sites	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE
Load party site uses	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE
Load org contacts	HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT
Load org contact roles	HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT_ROLE
Load contact points	HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

Tables/views involved Brief Overview:

Table/View name	Purpose
AR_CUST_RECEIPT_METHODS_V	Stores the payment method information of  the customer
RA_TERMS	Contains the payment term details
AR_DUNNING_LETTER_SETS	Stores dunning letter master details
AR_STATEMENT_CYCLES	Statement cycle details
HZ_CUST_PROFILE_CLASSES	Master table for customer account profile classes
AR_COLLECTORS	Master table for collector information
AR_AUTOCASH_HIERARCHIES	Master table for hierarches details related to Lockbox program
GL_CODE_COMBINATIONS_KFV	Stores the account code combinations
HZ_PARTIES	Stores the party information
HZ_PARTY_SITES	Master table for party site
HZ_LOCATIONS	Master table for addresses
HZ_PARTY_SITES	Master table for party sites
HZ_CUST_ACCOUNTS	Master table for customer account details
HZ_CUST_ACCT_SITES_ALL	Master table for customer account sites
HZ_CUST_SITE_USES_ALL	Master table for customer account site uses
HZ_CUSTOMER_PROFILES	Master table for customer account/site profiles
HZ_CUST_PROFILE_AMTS	Master table for customer account/site profile amounts
HZ_CUST_ACCOUNT_ROLES	Master table for customer account/site contacts
HZ_ROLE_RESPONSIBILITIES	Master table for customer account/site contact responsibilities
HZ_RELATIONSHIPS	Master table for storing party relationship details
HZ_CODE_ASSIGNMENTS	Master table for party organization classifications
HZ_ORGANIZATION_PROFILES	Master table for storing the organization type party profiles
HZ_PERSON_PROFILES	Master table for storing the person type party profiles
HZ_ORG_CONTACTS	Master table for storing the organization contact details
HZ_ORG_CONTACT_ROLES	Master table for storing the organization contact roles responsibilities
HZ_CONTACT_POINTS	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:

Step	Oracle Standard API
Load customer accounts	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT
Load customer account bill to sites	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE
Load customer account ship-to sites	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE
Load customer account bill-to site uses	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE
Load customer account ship-to site uses	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE
Load customer profiles	HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE
Load customer account profile amounts	HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUST_PROFILE_AMT
Load customer account roles	HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Load customer account role responsibilities	HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_ROLE_RESPONSIBILITY
Load customer account payment methods	HZ_PAYMENT_METHOD_PUB.CREATE_PAYMENT_METHOD
Load customer account relations	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE

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,

       iep.payment_function,

       iep.party_id,

       iep.org_type,

       iep.org_id,

       ou.NAME org_name,

       iep.cust_account_id,

       iep.acct_site_use_id,

       iep.bank_charge_bearer_code,

       iep.dirdeb_instruction_code,

       iep.debit_advice_delivery_method,

       iep.debit_advice_email,

       iep.debit_advice_fax

  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,

       rcrm.customer_id,

       rcrm.receipt_method_id,

       rcrm.receipt_method_name,

       rcrm.primary_flag,

       rcrm.site_use_id,

       rcrm.start_date,

       rcrm.end_date

  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.*

            ,hpp.*

            ,hop.*

       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.*,

       cust.price_list_id,

       pl.NAME price_list_name,

       cust.warehouse_id,

       org.NAME warehouse_name,

       hcp.profile_class_id,

       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.*,

       hl.timezone_id,

       ht.global_timezone_name timezone_name,

       hcas.org_id acct_org_id,

       ou.NAME acct_org_name,

       hl.*,

       hcas.*

  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,

       cp.cust_account_id,

       cp.collector_id,

       col.NAME collector_name,

       cp.profile_class_id,

       cpc.NAME profile_class_name,

       cp.site_use_id,

       term.NAME standard_terms,

       cp.statement_cycle_id,

       cyc.NAME statement_cycle_name,

       cp.autocash_hierarchy_id,

       hier.hierarchy_name autocash_hierarchy_name,

       cp.grouping_rule_id,

       grp.NAME grouping_rule_name,

       cp.autocash_hierarchy_id_for_adr,

       hier_adr.hierarchy_name autocash_hierarchy_name_adr,

       cp.*

  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,

       ipiu.payment_flow,

       ipiu.ext_pmt_party_id,

       ipiu.instrument_type,

       ipiu.instrument_id,

       cb.bank_name,

       cb.bank_branch_name,

       ieb.currency_code,

       ieb.bank_account_name,

       ieb.bank_account_num,

       ipiu.payment_function,

       DECODE (ipiu.order_of_preference, 1, 'YES', 'NO') primary_flag,

       ieb.start_date,

       ieb.end_date,

       ipiu.debit_auth_flag,

       ipiu.debit_auth_method,

       ipiu.debit_auth_reference,

       ipiu.debit_auth_begin,

       ipiu.debit_auth_end,

       iep.org_id

  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;