VIEW: APPS.MSC_SC_BILL_BOM_TREE_V

Source

View Text - Preformatted

SELECT DISTINCT ITEM1.ITEM_NAME || '/' || PT.organization_code , round(BIC.USAGE_QUANTITY,6) || 'x ' || ITEM2.ITEM_NAME || '/' || PT.organization_code , SETS.ASSIGNMENT_SET_ID , BBM.ORGANIZATION_ID , BBM.SR_INSTANCE_ID , PT.ORGANIZATION_CODE , BBM.ASSEMBLY_ITEM_ID , ITEM1.ITEM_NAME , OPR.OPERATION_SEQ_NUM , BIC.INVENTORY_ITEM_ID , ITEM2.ITEM_NAME , CAL.CALENDAR_DATE , BIC.USAGE_QUANTITY , BBM.ALTERNATE_BOM_DESIGNATOR , BBM.PLAN_ID , ITEM2.DESCRIPTION , pt.modeled_supplier_id, pt.modeled_supplier_site_id, msc_get_name.get_bom_item_type(item2.inventory_item_id) FROM MSC_ITEMS ITEM1, MSC_system_ITEMS ITEM2, MSC_CALENDAR_DATES CAL, MSC_BOM_COMPONENTS BIC, MSC_OPERATION_COMPONENTS MOC, MSC_ROUTING_OPERATIONS OPR, MSC_BOMS BBM, MSC_TRADING_PARTNERS PT, MSC_ASSIGNMENT_SETS SETS WHERE BIC.ORGANIZATION_ID = PT.SR_TP_ID AND BIC.SR_INSTANCE_ID = PT.SR_INSTANCE_ID AND PT.PARTNER_TYPE = 3 AND ITEM2.INVENTORY_ITEM_ID = BIC.INVENTORY_ITEM_ID AND ITEM2.SR_INSTANCE_ID = BIC.SR_INSTANCE_ID AND ITEM2.ORGANIZATION_ID = BIC.ORGANIZATION_ID AND ITEM2.PLAN_ID = BIC.PLAN_ID AND BIC.COMPONENT_SEQUENCE_ID = MOC.COMPONENT_SEQUENCE_ID (+) AND BIC.PLAN_ID = MOC.PLAN_ID (+) AND BIC.SR_INSTANCE_ID = MOC.SR_INSTANCE_ID (+) AND BIC.BILL_SEQUENCE_ID = MOC.BILL_SEQUENCE_ID (+) AND BIC.ORGANIZATION_ID = MOC.ORGANIZATION_ID (+) AND MOC.PLAN_ID = OPR.PLAN_ID (+) AND MOC.OPERATION_SEQUENCE_ID = OPR.OPERATION_SEQUENCE_ID (+) AND MOC.ROUTING_SEQUENCE_ID = OPR.ROUTING_SEQUENCE_ID (+) AND MOC.SR_INSTANCE_ID = OPR.SR_INSTANCE_ID (+) AND BBM.PLAN_ID = BIC.PLAN_ID AND BBM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID AND BBM.SR_INSTANCE_ID = BIC.SR_INSTANCE_ID AND ITEM1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID AND PT.CALENDAR_CODE = CAL.CALENDAR_CODE AND PT.SR_INSTANCE_ID = CAL.SR_INSTANCE_ID AND PT.CALENDAR_EXCEPTION_SET_ID = CAL.EXCEPTION_SET_ID AND CAL.CALENDAR_DATE BETWEEN BIC.EFFECTIVITY_DATE AND NVL(BIC.DISABLE_DATE, TO_DATE(2634525, 'J'))

View Text - HTML Formatted

SELECT DISTINCT ITEM1.ITEM_NAME || '/' || PT.ORGANIZATION_CODE
, ROUND(BIC.USAGE_QUANTITY
, 6) || 'X ' || ITEM2.ITEM_NAME || '/' || PT.ORGANIZATION_CODE
, SETS.ASSIGNMENT_SET_ID
, BBM.ORGANIZATION_ID
, BBM.SR_INSTANCE_ID
, PT.ORGANIZATION_CODE
, BBM.ASSEMBLY_ITEM_ID
, ITEM1.ITEM_NAME
, OPR.OPERATION_SEQ_NUM
, BIC.INVENTORY_ITEM_ID
, ITEM2.ITEM_NAME
, CAL.CALENDAR_DATE
, BIC.USAGE_QUANTITY
, BBM.ALTERNATE_BOM_DESIGNATOR
, BBM.PLAN_ID
, ITEM2.DESCRIPTION
, PT.MODELED_SUPPLIER_ID
, PT.MODELED_SUPPLIER_SITE_ID
, MSC_GET_NAME.GET_BOM_ITEM_TYPE(ITEM2.INVENTORY_ITEM_ID)
FROM MSC_ITEMS ITEM1
, MSC_SYSTEM_ITEMS ITEM2
, MSC_CALENDAR_DATES CAL
, MSC_BOM_COMPONENTS BIC
, MSC_OPERATION_COMPONENTS MOC
, MSC_ROUTING_OPERATIONS OPR
, MSC_BOMS BBM
, MSC_TRADING_PARTNERS PT
, MSC_ASSIGNMENT_SETS SETS
WHERE BIC.ORGANIZATION_ID = PT.SR_TP_ID
AND BIC.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND PT.PARTNER_TYPE = 3
AND ITEM2.INVENTORY_ITEM_ID = BIC.INVENTORY_ITEM_ID
AND ITEM2.SR_INSTANCE_ID = BIC.SR_INSTANCE_ID
AND ITEM2.ORGANIZATION_ID = BIC.ORGANIZATION_ID
AND ITEM2.PLAN_ID = BIC.PLAN_ID
AND BIC.COMPONENT_SEQUENCE_ID = MOC.COMPONENT_SEQUENCE_ID (+)
AND BIC.PLAN_ID = MOC.PLAN_ID (+)
AND BIC.SR_INSTANCE_ID = MOC.SR_INSTANCE_ID (+)
AND BIC.BILL_SEQUENCE_ID = MOC.BILL_SEQUENCE_ID (+)
AND BIC.ORGANIZATION_ID = MOC.ORGANIZATION_ID (+)
AND MOC.PLAN_ID = OPR.PLAN_ID (+)
AND MOC.OPERATION_SEQUENCE_ID = OPR.OPERATION_SEQUENCE_ID (+)
AND MOC.ROUTING_SEQUENCE_ID = OPR.ROUTING_SEQUENCE_ID (+)
AND MOC.SR_INSTANCE_ID = OPR.SR_INSTANCE_ID (+)
AND BBM.PLAN_ID = BIC.PLAN_ID
AND BBM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BBM.SR_INSTANCE_ID = BIC.SR_INSTANCE_ID
AND ITEM1.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND PT.CALENDAR_CODE = CAL.CALENDAR_CODE
AND PT.SR_INSTANCE_ID = CAL.SR_INSTANCE_ID
AND PT.CALENDAR_EXCEPTION_SET_ID = CAL.EXCEPTION_SET_ID
AND CAL.CALENDAR_DATE BETWEEN BIC.EFFECTIVITY_DATE
AND NVL(BIC.DISABLE_DATE
, TO_DATE(2634525
, 'J'))