Wednesday 22 February 2012

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





No comments:

Post a Comment