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