Oracle R12 MultiOrg Basics

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 Now some screenshots