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