Multi Org Structure Table and Query

Multi Org Structure Table and Query

Name :   Business Group

Table :   HRFV_BUSINESS_GROUPS

Profile:   HR:Business Group ID

Column:   BUSINESS_GROUP_ID

Name :    Set Of Books

Table :    GL_SET_OF_BOOKS

Profile:    GL: Set of books name

Column:   Set_of_book_id

Name :  Legal Entity

Table :  HR_LEGAL_ENTITIES

Profile:

Column:

Name :  Operating Unit

Table :  HR_OPERATING_UNITS

Profile:  MO:Operating Unit

Column:  ORG_ID

Name:  Inventroy Organization

Table:   ORG_ORGANIZATION_DEFINITIONS

Profile:

Column:  ORGANIZATION_ID

Name:  Sub Inventory

Table:  MTL_SECONDARY_INVENTORIES

Profile:

Column:

Name:  Stock Locations

Table:  MTL_ITEM_LOCATIONS

Profile:

Column:

Name:  MTL_SYSTEM_ITEMS

Table:

Profile:

Column:  Requisition, RFQ, Quotations, PO, PO Receipts

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Business Group ID Tables

    * PER_ALL_PEOPLE_F

    * PER_JOBS

    * PER_GRADES

    * PER_POSITIONS

   * per_all_assignments_f

ORG ID Tables

    * PO_REQUESITION_HEADERS_ALL

    * PO_HEADERS_ALL

    * PO_DISTRIBUTION_ALL

ORGANIZATION ID  - Tables

    * MTL_SYSTEM_ITES_B

    * WIP_ENTITITES

    * BOM_RESOURCES

    * BOM_DEPARTMENT

Note : PO_Vendors not having _ALL Table

But PO_vendor_sites_all having _ALL table

     HRFV_BUSINESS_GROUPS AND HR_LEGAL_ENTITIES

-------------------------------------------------------------------------------------------------------

/* Formatted on 2012/02/22 10:42 (Formatter Plus v4.8.8) */

SELECT pg.business_group_name, pg.working_hours_frequency,

       pg.business_group_short_name, pg.default_currency_code,

       pg.enabled_flag, pg.competence_structure, pg.business_group_id,

       pg.location_id, le.organization_id, le.NAME

  FROM hrfv_business_groups pg, hr_legal_entities le

 WHERE pg.business_group_id = le.business_group_id

   AND pg.business_group_id = 202

HRFV_BUSINESS_GROUPS AND HR_LEGAL_ENTITIES AND HR_OPERATING_UNITES

-------------------------------------------------------------------------------------------------------------------------

/* Formatted on 2012/02/22 10:42 (Formatter Plus v4.8.8) */

SELECT pg.business_group_name, pg.working_hours_frequency,

       pg.business_group_short_name, pg.default_currency_code,

       pg.enabled_flag, pg.competence_structure, pg.business_group_id,

       pg.location_id, le.organization_id, le.NAME,ORG.SET_OF_BOOKS_ID

  FROM hrfv_business_groups pg, hr_legal_entities le,hr_operating_units ORG

 WHERE pg.business_group_id = le.business_group_id

 AND PG.BUSINESS_GROUP_ID = ORG.BUSINESS_GROUP_ID

 AND LE.ORGANIZATION_ID = ORG.ORGANIZATION_ID

   AND pg.business_group_id = 202

  

    HRFV_BUSINESS_GROUPS AND HR_LEGAL_ENTITIES AND HR_OPERATING_UNITES AND ORG_ORGANIZATION_DEFINITIONS

---------------------------------------------------------------------------------------------------------------------------

/* Formatted on 2012/02/22 11:05 (Formatter Plus v4.8.8) */

SELECT pg.business_group_name, pg.working_hours_frequency,

       pg.business_group_short_name, pg.default_currency_code,

       pg.enabled_flag, pg.competence_structure, pg.business_group_id,

       pg.location_id, le.organization_id, le.NAME, org.set_of_books_id

  FROM hrfv_business_groups pg,

       hr_legal_entities le,

       hr_operating_units org,

       org_organization_definitions orga_id

 WHERE pg.business_group_id = le.business_group_id

   AND pg.business_group_id = org.business_group_id

   AND le.organization_id = org.organization_id

   AND pg.business_group_id = orga_id.business_group_id

   AND le.business_group_id = orga_id.business_group_id

   AND org.set_of_books_id = orga_id.set_of_books_id

   AND org.business_group_id = orga_id.business_group_id

   AND org.organization_id = orga_id.organization_id

   AND pg.business_group_id = 202

/*       HRFV_BUSINESS_GROUPS   AND HR_LEGAL_ENTITIES AND HR_OPERATING_UNITES AND ORG_ORGANIZATION_DEFINITIONS AND MTL_SECONDARY_INVENTORIES          */

  SELECT pg.business_group_name, pg.working_hours_frequency,

       pg.business_group_short_name, pg.default_currency_code,

       pg.enabled_flag, pg.competence_structure, pg.business_group_id,

       pg.location_id, le.organization_id, le.NAME, org.set_of_books_id,

       MTSI.SECONDARY_INVENTORY_NAME,MTSI.DESCRIPTION

   FROM hrfv_business_groups pg,

       hr_legal_entities le,

       hr_operating_units org,

       org_organization_definitions orga_id,

       MTL_SECONDARY_INVENTORIES mtsi

 WHERE pg.business_group_id = le.business_group_id

   AND pg.business_group_id = org.business_group_id

   AND le.organization_id = org.organization_id

   AND pg.business_group_id = orga_id.business_group_id

   AND le.business_group_id = orga_id.business_group_id

   AND org.set_of_books_id = orga_id.set_of_books_id

   AND org.business_group_id = orga_id.business_group_id

   AND org.organization_id = orga_id.organization_id

   AND org.organization_id = mtsi.organization_id

   and orga_id.organization_id = mtsi.organization_id

   AND pg.business_group_id = 202

/*   --------------    TEST   ------------------   */

 /* Formatted on 2012/02/22 11:07 (Formatter Plus v4.8.8) */

SELECT pg.business_group_name, pg.business_group_id, orga_id.operating_unit,

       le.organization_id, le.NAME, LE.NAME AS LE_HR_LEGAL,ORG.set_of_books_id,

      MTSI.SECONDARY_INVENTORY_NAME,MTSI.DESCRIPTION

  FROM hrfv_business_groups pg,

       hr_legal_entities le,

       hr_operating_units org,

       org_organization_definitions orga_id,

       MTL_SECONDARY_INVENTORIES mtsi

 WHERE pg.business_group_id = le.business_group_id

   AND pg.business_group_id = org.business_group_id

   AND le.organization_id = org.organization_id

   AND pg.business_group_id = orga_id.business_group_id

   AND le.business_group_id = orga_id.business_group_id

   AND org.set_of_books_id = orga_id.set_of_books_id

   AND org.business_group_id = orga_id.business_group_id

   AND org.organization_id = orga_id.organization_id

   AND org.organization_id = mtsi.organization_id

   and orga_id.organization_id = mtsi.organization_id

   AND pg.business_group_id = 202

Multi-org sql query

          Query to find Oracle Apps "BUSINESS_GROUP"

---------------------------------------------------------------------------------------------

select * from per_all_assignments_f

select distinct BUSINESS_GROUP_ID from per_all_assignments_f

       Query to find Oracle Apps Instance Version

---------------------------------------------------------------------------------

select * from fnd_product_groups;

SELECT release_name FROM fnd_product_groups;

         Query to find Oracle Apps Instance Name

------------------------------------------------------------------------------

select * from v$INSTANCE

SELECT instance_name FROM v$instance;

select INSTANCE_NAME,HOST_NAME,DATABASE_STATUS,VERSION from v$instance

      Query to find Oracle Apps Instance is set up for Multi - Org

-------------------------------------------------------------------------------------------

select * from fnd_product_groups

SELECT multi_org_flag,release_name FROM fnd_product_groups;

  Query to find list of Products and their version installed on your Oracle Apps Instance

--------------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM product_component_version;

        Query to find size of database

----------------------------------------------------------

SELECT SUM(bytes)/1024/1024 mb FROM dba_extents;

        Query to find workflow version

--------------------------------------------------------------------

select * from WF_RESOURCES

SELECT wf_core.translate('WF_VERSION') FROM dual;

SELECT text FROM WF_RESOURCES WHERE name= 'WF_VERSION';

SELECT text FROM WF_RESOURCES WHERE name= 'WF_WEB_AGENT';

            Query to find Application

----------------------------------------------------------------

select * from fnd_product_installations

select * from fnd_product_installations where APPLICATION_ID =202

     Query to find Application Patch Level

 ------------------------------------------------------------------

SELECT patch_level

 FROM fnd_product_installations

WHERE patch_level LIKE '%R12.DDR.B.1%';

                           Multi-org Security Query

-----------------------------------------------------------------------------

select     psp.SECURITY_PROFILE_NAME,

                 psp.SECURITY_PROFILE_ID,

                 hou.NAME,

                 hou.ORGANIZATION_ID

  from    PER_SECURITY_PROFILES psp,

                PER_SECURITY_ORGANIZATIONS pso,

                 HR_OPERATING_UNITS hou

 Where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID

   and     pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

                    SECURITY_GROUP  TABLE

------------------------------------------------------------------

   select * from FND_SECURITY_GROUPS

Multi-org sql query

--Query to find Oracle Apps "BUSINESS_GROUP"
-------------------------------------------------
select * from per_all_assignments_f
select distinct BUSINESS_GROUP_ID from per_all_assignments_f
--  Query to find Oracle Apps Instance Version
------------------------------------------------
select * from fnd_product_groups;
SELECT release_name FROM fnd_product_groups;
--Query to find Oracle Apps Instance Name
-------------------------------------------------
select * from v$INSTANCE
SELECT instance_name FROM v$instance;
select INSTANCE_NAME,HOST_NAME,DATABASE_STATUS,VERSION from v$instance
--Query to find Oracle Apps Instance is set up for MultiOrg
---------------------------------------------------------------------
select * from fnd_product_groups
SELECT multi_org_flag,release_name FROM fnd_product_groups;

--Query to find list of Products and their version installed on your Oracle Apps Instance
---------------------------------------------------------------------------------------------
SELECT * FROM product_component_version;
--Query to find size of database
--------------------------------------
SELECT SUM(bytes)/1024/1024 mb FROM dba_extents;
--Query to find workflow version
-----------------------------------------
select * from WF_RESOURCES
SELECT wf_core.translate('WF_VERSION') FROM dual;
SELECT text FROM WF_RESOURCES WHERE name= 'WF_VERSION';
SELECT text FROM WF_RESOURCES WHERE name= 'WF_WEB_AGENT';
--Query to find Application
------------------------------
select * from fnd_product_installations
select * from fnd_product_installations where APPLICATION_ID =202
--Query to find Application Patch Level
---------------------------------------
SELECT patch_level
 FROM fnd_product_installations
WHERE patch_level LIKE '%R12.DDR.B.1%';
--Multi-org Security Query
-------------------------------------
select psp.SECURITY_PROFILE_NAME,
       psp.SECURITY_PROFILE_ID,
       hou.NAME,
       hou.ORGANIZATION_ID
  from PER_SECURITY_PROFILES psp,
       PER_SECURITY_ORGANIZATIONS pso,
       HR_OPERATING_UNITS hou
 where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
   and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;
  
   select * from FND_SECURITY_GROUPS

select * from org_organization_definitions
select * from fnd_application where application_short_name = 'PO'
select * from FND_APPLICATION_TL
select * from FND_APP_SERVERS
select * from fnd_product_installations fnpi,fnd_application fna,FND_APPLICATION_TL fatl
where fnpi.APPLICATION_ID = fna.APPLICATION_ID
and fna.application_id = fatl.application_id

--------------------------------------------------------------------------------------------------------------

/* Formatted on 2012/02/21 15:15 (Formatter Plus v4.8.8) */
DECLARE
   l_user_id           fnd_user.user_id%TYPE;
   l_resp_id           fnd_responsibility.responsibility_id%TYPE;
   l_appl_id           fnd_application.application_id%TYPE;
   l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
   l_ou_value          fnd_profile_option_values.profile_option_value%TYPE;
   l_sp_value          fnd_profile_option_values.profile_option_value%TYPE;
BEGIN
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = UPPER ('IN047');
   SELECT responsibility_id
     INTO l_resp_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = ('Receivables, Vision Project Mfg (EUR)');  -- APPLICATION_DEVELOPER
   SELECT application_id, application_short_name
     INTO l_appl_id, l_appl_short_name
     FROM fnd_application_vl
    WHERE application_short_name = UPPER ('AR');  -- AR
   l_ou_value :=
        fnd_profile.value_specific ('ORG_ID', l_user_id, l_resp_id, l_appl_id);
   l_sp_value :=
      fnd_profile.value_specific ('XLA_MO_SECURITY_PROFILE_LEVEL',
                                  l_user_id,
                                  l_resp_id,
                                  l_appl_id
                                 );
   DBMS_OUTPUT.put_line ('MO: Operating Unit: ' || l_ou_value);
   DBMS_OUTPUT.put_line ('MO: Security Profile: ' || l_sp_value);
   IF l_sp_value IS NULL AND l_ou_value IS NULL
   THEN
      DBMS_OUTPUT.put_line
             ('No operating unit or security profile information
    found');
   ELSE
      mo_global.set_org_access (l_ou_value, l_sp_value, l_appl_short_name);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;
/
/*
select * from fnd_user where USER_NAME like 'IN047%'
select * from fnd_responsibility_vl
select * from fnd_application_vl where APPLICATION_NAME = 'Receivables'  ---like 'APPLICATION%'  */

multi- org securiy query

select psp.SECURITY_PROFILE_NAME,
       psp.SECURITY_PROFILE_ID,
       hou.NAME,
       hou.ORGANIZATION_ID
  from PER_SECURITY_PROFILES psp,
       PER_SECURITY_ORGANIZATIONS pso,
       HR_OPERATING_UNITS hou
 where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
   and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

Multi-Org

Multi-Org is an important feature of Oracle Applications for several reasons:

• Allows multiple sets of books and multiple legal entities to be configured and to operate in the same instance
• Provides support for data security between business units within a single applications installation
• Permits users to sell and ship products from different legal entities (in different sets of books) with automatic intercompany accounting
• Supports internal requisitions and purchasing/receiving products from different inventory organizations (within the same set of books)
• Enables an enterprise to be housed in one database instance of Oracle, spanning multiple countries, currencies, and legal entities without a reduction in response times (architecture-related)
• Multiple Organizations Reporting enhances the reporting capabilities of Oracle Applications products by allowing you to report at the:Set of Books level, Legal entity level or Operating unit level

Multiple Organization in Oracle Applications depends primarily on defining your organizational structure in the multi-level hierarchy used by Oracle Applications. The levels are:

• Business groups
• Accounting sets of books
• Legal entities
• Operating units]
• Inventory organizations

Business Groups: Oracle Applications secures human resources information, including organization definition, by business group. At least one Business Group will be required for every country since the employee legislation is specific for each country and employee profile is set up at the Business Group level. Security access to the sensitive Human Resources data is secured at the highest level at the Business Group. In short Business Group partitions Human Resources information in a multi-organization structure. Organization Structures and Organization Hierarchy is defined within the Business Group.
Multiple sets of books can share the same business group if they share the same business group attributes, including HR flex-field structures (Grade, positions flex-fields etc).
Accounting sets of books: A General Ledger concept for having separate financial reporting entities for which chart of accounts, calendar, or functional currency differs. In addition for scalability and ensuring independent numbering system for all the accounting transactions as well as the ability to open and close each of the legal entities period independently a separate set of books will be configured for each legal entity.

Legal entities: An organization that represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other relevant legal company information to this entity.
Operating units: An organization that partitions and uses data for Payables, Purchasing, Order Management, Cash Management, Fixed Assets and Receivables. Operating Units allow for configuration of the Oracle Applications across Multiple Business Groups using a single installation of the software.
Inventory organizations: An organization that tracks inventory transactions and balances, and/or that manufactures or distributes products or components. Segregate Item data objects for Inventory, Purchasing, Order Entry, and the Manufacturing Applications (Organization_Id).
Examples could be manufacturing plants, warehouses, distribution centers, and sales offices
The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and Purchasing receiving functions. To run any of these applications, you must choose an organization that has been classified as an inventory organization.
HR OrganizationInternal Departments to which Employees are Assigned.
Asset OrganizationsAn asset organization is an organization that allows you to perform asset–related activities for a specific Oracle Assets corporate depreciation book. Oracle Assets uses only organizations designated as asset organizations