BOM Start to end query

Query for Fetching BOM/Routing/Resources



select
       (select mtl1.segment1||'-'||mtl1.segment2 from mtl_system_items_b mtl1
       where mtl1.inventory_item_id=bill.assembly_item_id and mtl1.ORGANIZATION_ID=bill.ORGANIZATION_ID) Assembly_item,
       bill.ALTERNATE_BOM_DESIGNATOR,
       bill.DESCRIPTION,
       bill.uom,
       org.ORGANIZATION_code,
       org.ORGANIZATION_name,
       bill.ITEM_TYPE,
       bill.ORGANIZATION_ID,
       comp.ITEM_NUM,
       comp.operation_seq_num,
       (select mtl1.segment1||'-'||mtl1.segment2 from mtl_system_items_b mtl1
       where mtl1.inventory_item_id=comp.COMPONENT_ITEM_ID and mtl1.ORGANIZATION_ID=bill.ORGANIZATION_ID) component_item,
       comp.COMPONENT_QUANTITY,
       oper.department_code,
       res.RESOURCE_SEQ_NUM ,
       res.RESOURCE_CODE ,
       decode (res.AUTOCHARGE_TYPE,4,'PO Move',2,'Manual',3,'PO Receipt',1,'WIP Move' ) Charge_type,
              (select mtl.segment1||'-'||mtl.segment2 from mtl_system_items_b mtl,BOM_RESOURCES_V b_res
         where  b_res.PURCHASE_ITEM_ID=mtl.INVENTORY_ITEM_ID
         and b_res.ORGANIZATION_ID=mtl.ORGANIZATION_ID
         and b_res.resource_id=res.RESOURCE_ID) item_code,
       (select DESCRIPTION from BOM_RESOURCES_V where resource_id=res.RESOURCE_ID) resoruce_item_desc,
       (select mtl.LIST_PRICE_PER_UNIT from mtl_system_items_b mtl,BOM_RESOURCES_V b_res
         where  b_res.PURCHASE_ITEM_ID=mtl.INVENTORY_ITEM_ID
         and b_res.ORGANIZATION_ID=mtl.ORGANIZATION_ID
         and b_res.resource_id=res.RESOURCE_ID) unit_price
from BOM_BILL_OF_MATERIALS_V bill,
BOM_INVENTORY_COMPONENTS_V comp,
BOM_OPERATIONAL_ROUTINGS_V rout,
BOM_OPERATION_SEQUENCES_V oper,
BOM_OPERATION_RESOURCES_V res,
org_organization_definitions org
where bill.BILL_SEQUENCE_ID=comp.BILL_SEQUENCE_ID
and bill.ASSEMBLY_ITEM_ID=rout.ASSEMBLY_ITEM_ID
AND  (bill.ALTERNATE_BOM_DESIGNATOR= rout.ALTERNATE_ROUTING_DESIGNATOR  or bill.ALTERNATE_BOM_DESIGNATOR is null)
and rout.ROUTING_SEQUENCE_ID=oper.ROUTING_SEQUENCE_ID
and oper.OPERATION_SEQUENCE_ID = res.OPERATION_SEQUENCE_ID(+)
and bill.ORGANIZATION_ID=org.ORGANIZATION_ID
and rout.ORGANIZATION_ID=bill.ORGANIZATION_ID
and org.OPERATING_UNIT=:P_ORG_ID
and comp.DISABLE_DATE is  null
and bill.ORGANIZATION_ID =145
--and bill.assembly_item_id=35548
order by org.ORGANIZATION_code,bill.assembly_item_id,bill.ALTERNATE_BOM_DESIGNATOR,comp.ITEM_NUM