Organization Query in Oracle Apps R12

Organization Query in Oracle Apps R12

select * from HR_LOCATIONS where trunc(sysdate) =trunc(creation_date) and location_id = 30982

select * from HR_ORGANIZATION_UNITS wheretrunc(sysdate) = trunc(creation_date)

select * from HR_ALL_ORGANIZATION_UNITS wheretrunc(sysdate) = trunc(creation_date)

select ORGANIZATION_ID,

 NAME,

 BUSINESS_GROUP_ID,

 LOCATION_ID,

 DATE_FROM,

 INTERNAL_EXTERNAL_FLAG,

 INTERNAL_ADDRESS_LINE,

 TYPE from HR_ALL_ORGANIZATION_UNITS wheretrunc(sysdate) = trunc(creation_date)

select * from HR_ORGANIZATION_INFORMATION wheretrunc(sysdate) = trunc(creation_date)

select ORG_INFORMATION_ID,

 ORGANIZATION_ID,

 ORG_INFORMATION_CONTEXT,

 ORG_INFORMATION1  fromHR_ORGANIZATION_INFORMATION where trunc(sysdate) =trunc(creation_date)

All organizations are maintained inHR_ALL_ORGANIZATION_UNITS table and organizationclassifications like Legal entity or operating

unit information are stored inHR_ORGANIZATION_INFORMATION table. There are 3views provided based on these 2 tables to easily find out

the Legal entity, Operating unit and Inventoryorganization viz:

select ORGANIZATION_ID,               

BUSINESS_GROUP_ID ,

NAME    ,                      

DATE_FROM   ,                  

DATE_TO                       

 from apps.HR_LEGAL_ENTITIES

select ORGANIZATION_ID,       

BUSINESS_GROUP_ID,

NAME,                  

DATE_FROM,             

DATE_TO,                       

SET_OF_BOOKS_ID  from HR_OPERATING_UNITS

select ORGANIZATION_ID,               

BUSINESS_GROUP_ID,             

USER_DEFINITION_ENABLE_DATE,

DISABLE_DATE,                  

ORGANIZATION_CODE,             

ORGANIZATION_NAME,             

SET_OF_BOOKS_ID,               

CHART_OF_ACCOUNTS_ID,          

INVENTORY_ENABLED_FLAG,        

OPERATING_UNIT,                

LEGAL_ENTITY  from ORG_ORGANIZATION_DEFINITIONS

Query to find few business groups set up in theinstance :

select

   business_group_id,name

from per_business_groups

where lower(name) like '%vision corporation%'

order by name

Query to find SOBs set up in the instance :

select

   set_of_books_id,

   name sob_name,

   chart_of_accounts_id,

   chart_of_accounts_name,

   period_set_name calendar_period,

   accounted_period_type,

   user_period_type,

   currency_code

from gl_sets_of_books_v

where set_of_books_id=1

A very important query to find out  inventory organizations for an operating unit :

select

   organization_id,

   organization_code,

   organization_name,

   (select location_id fromhr_all_organization_units ou

     where od.organization_id=ou.organization_id)location_id,

   user_definition_enable_date,

   disable_date,

   chart_of_accounts_id,

   inventory_enabled_flag,

   operating_unit,

   legal_entity,

   set_of_books_id,

   business_group_id

from org_organization_definitions od

where operating_unit=204 and ORGANIZATION_ID =9073

order by organization_code

select * from org_organization_definitions whereORGANIZATION_NAME like 'M12%'--trunc(sysdate) = trunc(creation_date)

select *from mtl_parameters where ORGANIZATION_ID= 9073

select * from MTL_SECONDARY_INVENTORIES_FK_V wheretrunc(sysdate) = trunc(creation_date)

select * from MTL_SECONDARY_INVENTORIES wheretrunc(sysdate) = trunc(creation_date)

select * from MTL_UOM_CLASSES where trunc(sysdate)= trunc(creation_date)

select * from MTL_ITEM_LOCATIONS  wheretrunc(sysdate) = trunc(creation_date)