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





Tuesday 21 February 2012

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%'  */

Monday 20 February 2012

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
multiorg

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