Thursday, August 1, 2013

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 where trunc(sysdate) = trunc(creation_date)

select * from HR_ALL_ORGANIZATION_UNITS where trunc(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 where trunc(sysdate) = trunc(creation_date)

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

select ORG_INFORMATION_ID,
 ORGANIZATION_ID,
 ORG_INFORMATION_CONTEXT,
 ORG_INFORMATION1  from HR_ORGANIZATION_INFORMATION where trunc(sysdate) = trunc(creation_date)


All organizations are maintained in HR_ALL_ORGANIZATION_UNITS table and organization classifications like Legal entity or operating
unit information are stored in HR_ORGANIZATION_INFORMATION table. There are 3 views provided based on these 2 tables to easily find out
the Legal entity, Operating unit and Inventory organization 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 the instance :

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 from hr_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 where ORGANIZATION_NAME like 'M12%'--trunc(sysdate) = trunc(creation_date)

select *from mtl_parameters where ORGANIZATION_ID = 9073

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

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

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

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


No comments:

Post a Comment