OM Related queries ============== Oracle Apps BOM Tables and Queries
BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table
BOM_BILL_OF_MATERIALS
BOM_PARAMETERS
BOM_DEPENDENT_DESC_ELEMENTS
BOM_REFERENCE_DESIGNATORS
BOM_SUBSTITUTE_COMPONENTS
FND_DOCUMENTS
FND_DOCUMENTS_TL
FND_DOCUMENTS_SHORT_TEXT
FND_ATTACHED_DOCUMENTS
Select
*
from
MFG_LOOKUPS
WHERE
--LOOKUP_CODE = 'BOM_ITEM_TYPE'
--LOOKUP_CODE = 'WIP_SUPPLY_TYPE'
--LOOKUP_CODE = 'BOM_ASSEMBLY_TYPE'
-----------------------------------------------------------------
1) Query for BOM Details
SELECT
d.inventory_item_id,
d.concatenated_segments "Assembly_item",
e.organization_code "Organization",
nvl(
a.ALTERNATE_BOM_DESIGNATOR, '10'
) "ALTERNATIVE BOM",
b.ITEM_NUM "ITEM",
c.concatenated_segments "COMPONENT",
b.component_quantity "UNIT",
c.primary_uom_code "QUANTITY",
d.primary_uom_code "UNIT"
FROM
bom_bill_of_materials a,
bom_inventory_components b,
mtl_system_items_kfv c,
mtl_system_items_kfv d,
org_organization_definitions e
WHERE
1 = 1
AND a.organization_id in (: P_organization_id)
AND (
a.BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID
OR a.COMMON_BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID
)
AND b.implementation_date IS NOT NULL
AND b.DISABLE_DATE IS NULL
AND a.organization_id = c.organization_id
AND b.COMPONENT_ITEM_ID = c.inventory_ITEM_ID
AND a.assembly_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
AND a.organization_id = e.organization_id --AND c.organization_id = :P_organization_id
--AND a.ASSEMBLY_ITEM_ID = :P_assembly_item_id
-- AND ALTERNATE_BOM_DESIGNATOR IS NULL
ORDER BY
e.organization_code,
d.inventory_item_id,
ALTERNATE_BOM_DESIGNATOR,
b.ITEM_NUM;
----------------------------------------------------------------
2) Query for Resources
SELECT
c.concatenated_segments "Assemly_Item",
d.organization_code "Organization",
a.alternate_routing_designator "Alternate_routing",
b.operation_seq_num "OPERATION NUMBER",
e.department_code "WORK CENTER",
b.operation_description "DESCRIPTION(Operation)",
c.primary_uom_code "OPERATION UNIT",
G.uom "UNIT",
f.operation_code,
g.resource_code,
g.USAGE_RATE_OR_AMOUNT h.DESCRIPTION
FROM
bom_operational_routings a,
bom_operation_sequences b,
mtl_system_items_kfv c,
org_organization_definitions d,
bom_departments e,
bom_standard_operations f,
bom_operation_resources_v g,
bom_resources h
WHERE
a.organization_id in (: P_organization_id)
AND a.organization_id = d.organization_id
AND a.organization_id = c.organization_id
AND a.assembly_item_id = c.inventory_item_id
AND a.routing_sequence_id = b.routing_sequence_id
AND b.department_id = e.department_id
AND b.disable_date is null --AND a.assembly_item_id = :P_assembly_item_id
AND f.standard_operation_id(+) = b.standard_operation_id
AND g.operation_sequence_id = b.operation_sequence_id
AND h.resource_id = g.resource_id
order by
d.organization_code,
c.concatenated_segments,
a.alternate_routing_designator,
b.operation_seq_num --------------------------------------------------------------------------------------------
3) Departments and Resources and Departments associated with Resources
select
b.organization_code "Plant",
a.DEPARTMENT_CODE "Department",
a.DESCRIPTION "Department Description",
a.DEPARTMENT_CLASS_CODE "Department Class",
c.DESCRIPTION "Department Class Description",
d.location_code "Department_location"
from
BOM_DEPARTMENTS a,
org_organization_definitions b,
BOM_DEPARTMENT_CLASSES c,
hr_locations d
where
a.organization_id in (: P_organization_id)
AND a.organization_id = b.organization_id
AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND a.organization_id = c.organization_id
AND a.location_id = d.location_id(+)
select
b.organization_code "Plant",
a.RESOURCE_CODE "Resource Name",
a.DESCRIPTION "Resource Description",
a.UNIT_OF_MEASURE
from
BOM_RESOURCES a,
org_organization_definitions b --,mfg_lookups c
where
a.organization_id in (: P_organization_id)
AND a.organization_id = b.organization_id
AND a.Disable_date is Null --AND c.lookup_type = 'BOM_BASIS_TYPE'
--AND c.LOOKUP_CODE = a.DEFAULT_BASIS_TYPE
exec mo_global.set_policy_context('S',: P_org_id);
--BOM_RESOURCE_TYPE
--BOM_BASIS_TYPE
select
*
from
mfg_lookups
where
lookup_type like 'BOM_BASIS_TYPE'
select
*
from
BOM_DEPARTMENT_RESOURCES
select
*
from
BOM_RESOURCE_SHIFTS
select
b.organization_code "Plant",
a.DEPARTMENT_CODE "Department",
a.DESCRIPTION "Department Description",
a.DEPARTMENT_CLASS_CODE "Department Class",
c.DESCRIPTION "Department Class Description",
d.location_code "Department_location",
f.RESOURCE_CODE "Resource Name",
f.DESCRIPTION "Resource Description",
f.UNIT_OF_MEASURE
from
BOM_DEPARTMENTS a,
org_organization_definitions b,
BOM_DEPARTMENT_CLASSES c,
hr_locations d,
BOM_DEPARTMENT_RESOURCES e,
BOM_RESOURCES f -- ,BOM_RESOURCE_SHIFTS g
where
a.organization_id in (: P_oranization_id)
AND a.organization_id = b.organization_id
AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND a.organization_id = c.organization_id
AND a.location_id = d.location_id(+)
AND a.DEPARTMENT_ID = e.DEPARTMENT_ID
AND e.RESOURCE_ID = f.RESOURCE_ID --AND a.DEPARTMENT_ID = g.DEPARTMENT_ID
--AND f.RESOURCE_ID = g.RESOURCE_ID
--------------------------------------------------------------------------------------------------------
Routing Open Interface Concurrent: Bill
and Routing Interface -- Create Routing
insert into bom_op_routings_interface (
assembly_item_id, process_revision,
process_flag, transaction_type,
routing_type, organization_id
)
values
(
218967,
Null,
1,
--1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'CREATE',
1,
----1) manufacturing, 2) engineering
204
);
-- Create Routing Sequences
insert into bom_op_sequences_interface (
assembly_item_id, operation_seq_num,
department_id, department_code,
process_flag, transaction_type,
organization_id, effectivity_date
)
values
(
218967, 10, 42877, 'PD1', 1, 'CREATE',
204, sysdate
);
-- Create Operation Resources
insert into bom_op_resources_interface (
resource_seq_num,
resource_id,
resource_code,
usage_rate_or_amount,
--basis_type ,
assigned_units,
--schedule_flag,autocharge_type,
assembly_item_id,
operation_seq_num,
--operation_sequence_id,
process_flag,
transaction_type,
effectivity_date,
organization_id,
schedule_flag,
schedule_seq_num
)
values
(
10,
90529,
'PR1',
1,
1,
218967,
10,
--2346216 ,
1,
'CREATE',
sysdate,
204,
1,
10
);
select
*
from
mtl_system_items_kfv
where
inventory_item_id = 133
and organization_id = 204
select
*
from
bom_departments
where
department_id = 42877
and organization_id = 204
select
*
from
BOM_DEPARTMENT_RESOURCES
where
department_id = 42877
select
*
from
bom_resources
where
resource_id = 90529
select
*
from
bom_op_resources_interface
