MSS_FS_OPERATION_MATERIALS_V

Details

• Schema: FUSION

• Object owner: MSS

• Object type: VIEW

Columns

Name
PLAN_ID WO_OPERATION_ID RESOURCE_SEQ_NUMBER INVENTORY_ITEM_ID MATERIAL_TYPE QUANTITY REMAINING_QUANTITY

Query

SQL_Statement

select fsOp.PLAN_ID, fsOp.WO_OPERATION_ID, fsOp.RESOURCE_SEQ_NUMBER, fsWdOpMat.INVENTORY_ITEM_ID, 'Consumed Item' MATERIAL_TYPE, Decode(fsWdOpMat.BASIS_TYPE, 2, 1, case when wo.PRODUCTION_LINE_ID is not null and wo.ACTUAL_END_DATE is not null then Nvl( wo.PRIMARY_PRODUCT_QUANTITY, Nvl(wo.NETTABLE_PLANNED_QUANTITY, wo.PLANNED_START_QUANTITY) ) else Decode(wo.PRIMARY_PRODUCT_QUANTITY, null, Nvl(wo.NETTABLE_REMAINING_QUANTITY, wo.REMAINING_QUANTITY), wo.PRIMARY_PRODUCT_REMAINING_QTY) end ) * fsWdOpMat.QUANTITY_PER_PRODUCT QUANTITY, Decode(fsWdOpMat.BASIS_TYPE, 2, 1, case when wo.PRODUCTION_LINE_ID is not null and wo.ACTUAL_END_DATE is not null then Nvl( wo.PRIMARY_PRODUCT_QUANTITY, Nvl(wo.NETTABLE_PLANNED_QUANTITY, wo.PLANNED_START_QUANTITY) ) else Decode(wo.PRIMARY_PRODUCT_QUANTITY, null, Nvl(wo.NETTABLE_REMAINING_QUANTITY, wo.REMAINING_QUANTITY), wo.PRIMARY_PRODUCT_REMAINING_QTY) end ) * fsWdOpMat.QUANTITY_PER_PRODUCT REMAINING_QUANTITY from MSS_FS_OPERATIONS_V fsOp join MSS_WORK_ORDERS wo on fsOp.PLAN_ID = wo.PLAN_ID and fsOp.WORK_ORDER_ID = wo.WORK_ORDER_ID join MSS_FS_WD_OP_MATERIALS fsWdOpMat on fsOp.PLAN_ID = fsWdOpMat.PLAN_ID and fsOp.FS_WD_HEADER_ID = fsWdOpMat.FS_WD_HEADER_ID and fsOp.OPERATION_SEQ_NUMBER = fsWdOpMat.OPERATION_SEQ_NUMBER join MSS_ITEMS item on fsWdOpMat.PLAN_ID = item.PLAN_ID and fsWdOpMat.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID Union all select wo.PLAN_ID, fsOp.WO_OPERATION_ID, fsOp.RESOURCE_SEQ_NUMBER, wo.INVENTORY_ITEM_ID, 'Primary Item' MATERIAL_TYPE, case when wo.PRODUCTION_LINE_ID is not null and wo.ACTUAL_END_DATE is not null then Nvl( wo.PRIMARY_PRODUCT_QUANTITY, Nvl(wo.NETTABLE_PLANNED_QUANTITY, wo.PLANNED_START_QUANTITY) ) else Decode(wo.PRIMARY_PRODUCT_QUANTITY, null, Nvl(wo.NETTABLE_REMAINING_QUANTITY, wo.REMAINING_QUANTITY), wo.PRIMARY_PRODUCT_REMAINING_QTY) end as QUANTITY, case when wo.PRODUCTION_LINE_ID is not null and wo.ACTUAL_END_DATE is not null then Nvl( wo.PRIMARY_PRODUCT_QUANTITY, Nvl(wo.NETTABLE_PLANNED_QUANTITY, wo.PLANNED_START_QUANTITY) ) else Decode(wo.PRIMARY_PRODUCT_QUANTITY, null, Nvl(wo.NETTABLE_REMAINING_QUANTITY, wo.REMAINING_QUANTITY), wo.PRIMARY_PRODUCT_REMAINING_QTY) end as REMAINING_QUANTITY from MSS_WORK_ORDERS wo join MSS_FS_OPERATIONS_V fsOp on wo.PLAN_ID = fsOp.PLAN_ID and wo.WORK_ORDER_ID = fsOp.WORK_ORDER_ID and fsOp.FS_OPERATION_TYPE='Takt Operation'