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