Query : Get the Inventory org Address

select * from hr_locations_v hlv,
hr_all_organization_units hou
WHERE hlv.inventory_organization_id = hou.organization_id
AND hlv.location_id = hou.location_id
AND hou.organization_id = :p_mfg_org;

Read More

Query : Lists all the organization units in the system as well as their addresses

select org.organization_id,
org.name,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
loc.town_or_city,
loc.country,
loc.postal_code
from hr_all_organization_units org,
hr_locations_all loc
where org.location_id = loc.location_id

Read More

Setting Organization Context in Oracle R12

For those of you who are trying to set the organization context within your SQL sessions or custom programs, there’s a new API you must call in R12.

In 11i the way of setting organization context in 11i was to execute the following statement:

dbms_application_info.set_client_info(ORG_ID);

However, in R12, the new way of setting the organization context is as follows:

mo_global.init(’AR’);
mo_global.set_policy_context(’S',ORG_ID);

The 'S' parameter indicates a single organization context. Replace ORG_ID with the ID of the operating unit you wish to use. You can also set a context for multiple operating units by setting the security profile. This is done by executing the following statement:

mo_global.set_org_access(NULL,SECURITY_PROFILE_ID,’ASO’);

Replace SECURITY_PROFILE_ID with the ID of the appropriate security profile. ‘ASO’ represents the application short name associated with the responsibility you’ll be using.

Read More

Query to find subinventories for an inv organization

select
secondary_inventory_name subinventory,
description,
subinventory_type,
organization_id,
asset_inventory,
quantity_tracked,
inventory_atp_code,
availability_type,
reservable_type,
locator_type,
picking_order,
dropping_order,
location_id,
status_id
from mtl_secondary_inventories
where organization_id=
order by subinventory

Read More

Query to find organization parameters of an inv org

select
ood.organization_code ORG_CODE,
a.master_organization_id MASTER_ORG_ID,
o1.organization_code MASTER_ORG_CD,
o1.organization_name MASTER_ORG_NM,
a.cost_organization_id COST_ORG_ID,
o2.organization_code COST_ORG_CD,
o2.organization_name COST_ORG_NM,
a.source_organization_id SOURCE_ORG_ID,
o3.organization_code SOURCE_ORG_CD,
o3.organization_name SOURCE_ORG_NM,
mfg1.meaning PRIMARY_COST_METHOD,
mfg2.meaning NEGATIVE_BALANCE,
mfg11.meaning GL_UPDATE_CODE,
a.calendar_code CALENDAR_CODE,
a.default_demand_class DEFAULT_DEMAND_CLASS,
mfg12.meaning ENCUMBRANCE_REVERSAL_FLAG,
mfg3.meaning LOCATOR_CONTROL,
mfg4.meaning INTERORG_TRANSFER_CODE,
DECODE(a.maintain_fifo_qty_stack_type, NULL,'',mfg6.meaning)
MAINTAIN_FIFO_COST,
mfg7.meaning SERIAL_NUMBER_TYPE,
mfg8.meaning LOT_NUMBER_UNIQUENESS,
mfg9.meaning LOT_NUMBER_GENERATION,
DECODE(a.LOT_NUMBER_ZERO_PADDING, NULL, '' ,
mfg10.meaning)
LOT_NUMBER_ZERO_PADDING,
b.rule_name ATP_RULE_NAME,
c.picking_rule_name PICKING_RULE_NAME,
a.default_locator_order_value,
a.default_subinv_order_value,
a.interorg_trnsfr_charge_percent intorg_charge_percent,
a.auto_serial_alpha_prefix,
a.start_auto_serial_number,
a.auto_lot_alpha_prefix,
a.lot_number_length,
mfg13.meaning SERIAL_GENERATION,
mfg14.meaning SOURCE_TYPE,
a.source_subinventory SOURCE_SUBINV
from
mtl_parameters a,
mtl_atp_rules b,
mtl_picking_rules c,
org_organization_definitions ood,
org_organization_definitions o1,
org_organization_definitions o2,
org_organization_definitions o3,
mfg_lookups mfg1,
mfg_lookups mfg2,
mfg_lookups mfg3,
mfg_lookups mfg4,
mfg_lookups mfg6,
mfg_lookups mfg7,
mfg_lookups mfg8,
mfg_lookups mfg9,
mfg_lookups mfg10,
mfg_lookups mfg11,
mfg_lookups mfg12,
mfg_lookups mfg13,
mfg_lookups mfg14
where a.organization_id = 207
and a.master_organization_id = o1.organization_id (+)
and a.cost_organization_id = o2.organization_id (+)
and a.source_organization_id = o3.organization_id (+)
and a.organization_id = ood.organization_id (+)
and a.default_atp_rule_id = b.rule_id(+)
and a.default_picking_rule_id = c.picking_rule_id(+)
and mfg1.lookup_type (+) = 'MTL_PRIMARY_COST'
and a.primary_cost_method = mfg1.lookup_code(+)
and mfg2.lookup_type (+) = 'SYS_YES_NO'
and a.negative_inv_receipt_code = mfg2.lookup_code(+)
and mfg3.lookup_type (+) = 'MTL_LOCATION_CONTROL'
and a.stock_locator_control_code = mfg3.lookup_code(+)
and mfg4.lookup_type (+) = 'MTL_INTER_INV_TRANSFER'
and a.matl_interorg_transfer_code = mfg4.lookup_code(+)
and mfg6.lookup_type (+) = 'SYS_YES_NO'
and a.maintain_fifo_qty_stack_type = mfg6.lookup_code(+)
and mfg7.lookup_type (+) = 'MTL_SERIAL_NUMBER_TYPE'
and a.serial_number_type = mfg7.lookup_code(+)
and mfg8.lookup_type (+) = 'MTL_LOT_UNIQUENESS'
and a.lot_number_uniqueness = mfg8.lookup_code(+)
and mfg9.lookup_type (+) = 'MTL_LOT_GENERATION'
and a.lot_number_generation = mfg9.lookup_code(+)
and mfg10.lookup_type (+) = 'SYS_YES_NO'
and a.lot_number_zero_padding = mfg10.lookup_code(+)
and mfg11.lookup_type (+) = 'SYS_YES_NO'
and a.general_ledger_update_code = mfg11.lookup_code(+)
and mfg12.lookup_type (+) = 'SYS_YES_NO'
and a.encumbrance_reversal_flag = mfg12.lookup_code(+)
and mfg13.lookup_type (+) = 'MTL_SERIAL_GENERATION'
and a.serial_number_generation = mfg13.lookup_code(+)
and mfg14.lookup_type (+) = 'MTL_SOURCE_TYPES'
and a.source_type = mfg14.lookup_code (+)

Read More

Query :Operating Unit name based on the Operating Unit ID

select Business_group_id,
Organization_id,
name,date_from,date_to,
legal_entity_id,
set_of_books_id
from hr_operating_units
where organization_id = 'your Operating Unit ID';

Read More