MSC_AP_WIP_JOB_COMP_DEMANDS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ORGANIZATION_ID INVENTORY_ITEM_ID ITEM_NAME ORGANIZATION_CODE USING_REQUIREMENT_QUANTITY CONVERSION_RATE USING_ASSEMBLY_DEMAND_DATE ORDER_NUMBER OPERATION_SEQ_NUM WIP_ENTITY_NAME QUANTITY_ISSUED WIP_ENTITY_ID WIP_STATUS_CODE WIP_SUPPLY_TYPE ORIGINATION_TYPE USING_ASSEMBLY_ITEM_ID DEMAND_TYPE PROJECT_ID TASK_ID PLANNING_GROUP END_ITEM_UNIT_NUMBER DEMAND_CLASS MPS_DATE_REQUIRED QUANTITY_PER_ASSEMBLY ASSET_ITEM_ID ASSET_SERIAL_NUMBER COMPONENT_SCALING_TYPE COMPONENT_YIELD_FACTOR MATERIAL_SEQ_NUMBER OPERATION_EFFECTIVITY_DATE OPROWID OPMROWID WORK_METHOD_CODE EXECUTION_SEQUENCE

Name

ORGANIZATION_ID

INVENTORY_ITEM_ID

ITEM_NAME

ORGANIZATION_CODE

USING_REQUIREMENT_QUANTITY

CONVERSION_RATE

USING_ASSEMBLY_DEMAND_DATE

ORDER_NUMBER

OPERATION_SEQ_NUM

WIP_ENTITY_NAME

QUANTITY_ISSUED

WIP_ENTITY_ID

WIP_STATUS_CODE

WIP_SUPPLY_TYPE

ORIGINATION_TYPE

USING_ASSEMBLY_ITEM_ID

DEMAND_TYPE

PROJECT_ID

TASK_ID

PLANNING_GROUP

END_ITEM_UNIT_NUMBER

DEMAND_CLASS

MPS_DATE_REQUIRED

QUANTITY_PER_ASSEMBLY

ASSET_ITEM_ID

ASSET_SERIAL_NUMBER

COMPONENT_SCALING_TYPE

COMPONENT_YIELD_FACTOR

MATERIAL_SEQ_NUMBER

OPERATION_EFFECTIVITY_DATE

OPROWID

OPMROWID

WORK_METHOD_CODE

EXECUTION_SEQUENCE

Query

SQL_Statement
SELECT wob.organization_id, wom.inventory_item_id, esib.item_number item_name , (SELECT O.ORGANIZATION_CODE FROM INV_ORG_PARAMETERS O WHERE O.ORGANIZATION_ID=esib.ORGANIZATION_ID) as organization_code , CASE WHEN wmb.work_method_code IN( 'DISCRETE_MANUFACTURING', 'MAINTENANCE') THEN wom.quantity - ( NVL(wom.issued_quantity, 0) + (wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0))) ELSE /* cumulative_scrap_quantity= Batch quantity scrapped till last operation quantity_per_product= Component required to produce one unit of work order. MFG calculates this as (total_comp_req/WO_batch_qty) scrapped_quantity= Batch quantity scrapped in current operation, where comp is attached completed_quantity= Batch quantity completed in current operation, where comp is attached Current comp demand = (total_comp_req - scrapped_qty till last operation - issued_qty) wom.quantity - (wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0)) - GREATEST(NVL(wom.issued_quantity, 0), (wom.quantity_per_product*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0))), 0) END using_requirement_quantity, CASE WHEN wom.uom_code = esib.uom_code THEN 1 ELSE inv_convert.inv_um_convert(wom.inventory_item_id, 10, NULL, wom.uom_code, esib.UOM_CODE, NULL, NULL) END conversion_rate, NVL(wom.required_date, NVL(WWOOB.actual_start_date, WWOOB.planned_start_date)) using_assembly_demand_date, wob.work_order_number order_number, WWOOB.operation_seq_number operation_seq_num, wob.work_order_number wip_entity_name, NVL(wom.issued_quantity, 0) quantity_issued, wob.work_order_id wip_entity_id, DECODE(wwsb.wo_system_status_code,'UNRELEASED',1, 'RELEASED',3, 'COMPLETED',4, 'CLOSED', 12, 'ON_HOLD',6, 'PENDING_APPROVAL',18, 'CANCELED', 7) wip_status_code, DECODE (wom.SUPPLY_TYPE, 'ASSEMBLY_PULL', 2, 'BASED_ON_WD', 7, 'BULK', 4, 'OPERATION_PULL', 3, 'PUSH', 1, 'SUPPLIER', 5, '1', 1, '2', 2, '3', 3, '4', 4, '5', 5, '6', 6, '7', 7, 7) wip_supply_type, DECODE(wob.work_order_type, 'STANDARD', 1003, 'NON_STANDARD', 1002, 'CORRECTIVE', 1050, 'FIELDSERVICE', 1050, 'PREVENTIVE', 1050, 1002) origination_type, wob.inventory_item_id using_assembly_item_id, 1 demand_type, NULL project_id, NULL task_id, NULL planning_group, NULL end_item_unit_number, NULL demand_class, NULL mps_date_required, wom.quantity_per_product quantity_per_assembly, NULL asset_item_id, NULL asset_serial_number, wom.basis_type component_scaling_type, wom.yield_factor component_yield_factor, wom.material_seq_number, NVL(WWOOB.actual_start_date, WWOOB.planned_start_date) operation_effectivity_date, WWOOB.rowid oprowid, wom.rowid opmrowid, DECODE(wmb.WORK_METHOD_CODE,'PROCESS_MANUFACTURING', 2, 'MAINTENANCE', 3, 1) WORK_METHOD_CODE, wwoob.execution_sequence FROM WIE_WORK_ORDERS_B wob, WIE_WO_STATUSES_B wwsb, WIS_WORK_METHODS_B WMB, WIE_WO_OPERATION_MATERIALS wom, WIE_WO_OPERATIONS_B WWOOB, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esib WHERE wob.work_method_id = WMB.work_method_id AND wmb.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING', 'MAINTENANCE') AND wwsb.WO_STATUS_ID = wob.WORK_ORDER_STATUS_ID AND wwsb.WO_SYSTEM_STATUS_CODE IN ('UNRELEASED','RELEASED','ON_HOLD','COMPLETED','PENDING_APPROVAL') AND wob.work_order_id = WWOOB.work_order_id AND WWOOB.wo_operation_id = wom.wo_operation_id AND WWOOB.work_order_id = wom.work_order_id AND wom.inventory_item_id = esib.inventory_item_id AND wom.organization_id = esib.organization_id AND wom.include_in_planning_flag = 'Y' AND wom.material_type IN ('COMPONENT','INDIRECT') AND wom.inventory_item_id != wob.inventory_item_id AND wom.quantity >= 0

SQL_Statement

SELECT wob.organization_id,

wom.inventory_item_id,

esib.item_number item_name ,

(SELECT O.ORGANIZATION_CODE FROM INV_ORG_PARAMETERS O WHERE O.ORGANIZATION_ID=esib.ORGANIZATION_ID) as organization_code ,

CASE

WHEN wmb.work_method_code IN( 'DISCRETE_MANUFACTURING', 'MAINTENANCE')

THEN wom.quantity - ( NVL(wom.issued_quantity, 0) + (wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0)))

ELSE

/*

cumulative_scrap_quantity= Batch quantity scrapped till last operation

quantity_per_product= Component required to produce one unit of work order. MFG

calculates this as (total_comp_req/WO_batch_qty)

scrapped_quantity= Batch quantity scrapped in current operation, where comp is attached

completed_quantity= Batch quantity completed in current operation, where comp is attached

Current comp demand = (total_comp_req - scrapped_qty till last operation - issued_qty)

wom.quantity - (wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0)) - GREATEST(NVL(wom.issued_quantity, 0), (wom.quantity_per_product*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0))), 0)

END using_requirement_quantity,

CASE

WHEN wom.uom_code = esib.uom_code

THEN 1

ELSE inv_convert.inv_um_convert(wom.inventory_item_id,

10,

NULL,

wom.uom_code,

esib.UOM_CODE,

NULL,

NULL)

END conversion_rate,

NVL(wom.required_date, NVL(WWOOB.actual_start_date, WWOOB.planned_start_date)) using_assembly_demand_date,

wob.work_order_number order_number,

WWOOB.operation_seq_number operation_seq_num,

wob.work_order_number wip_entity_name,

NVL(wom.issued_quantity, 0) quantity_issued,

wob.work_order_id wip_entity_id,

DECODE(wwsb.wo_system_status_code,'UNRELEASED',1,

'RELEASED',3,

'COMPLETED',4,

'CLOSED', 12,

'ON_HOLD',6,

'PENDING_APPROVAL',18,

'CANCELED', 7) wip_status_code,

DECODE (wom.SUPPLY_TYPE, 'ASSEMBLY_PULL', 2,

'BASED_ON_WD', 7,

'BULK', 4,

'OPERATION_PULL', 3,

'PUSH', 1,

'SUPPLIER', 5,

'1', 1, '2', 2, '3', 3, '4', 4, '5', 5, '6', 6, '7', 7, 7) wip_supply_type,

DECODE(wob.work_order_type, 'STANDARD', 1003,

'NON_STANDARD', 1002,

'CORRECTIVE', 1050,

'FIELDSERVICE', 1050,

'PREVENTIVE', 1050,

1002) origination_type,

wob.inventory_item_id using_assembly_item_id,

1 demand_type,

NULL project_id,

NULL task_id,

NULL planning_group,

NULL end_item_unit_number,

NULL demand_class,

NULL mps_date_required,

wom.quantity_per_product quantity_per_assembly,

NULL asset_item_id,

NULL asset_serial_number,

wom.basis_type component_scaling_type,

wom.yield_factor component_yield_factor,

wom.material_seq_number,

NVL(WWOOB.actual_start_date, WWOOB.planned_start_date) operation_effectivity_date,

WWOOB.rowid oprowid,

wom.rowid opmrowid,

DECODE(wmb.WORK_METHOD_CODE,'PROCESS_MANUFACTURING', 2, 'MAINTENANCE', 3, 1) WORK_METHOD_CODE,

wwoob.execution_sequence

FROM

WIE_WORK_ORDERS_B wob,

WIE_WO_STATUSES_B wwsb,

WIS_WORK_METHODS_B WMB,

WIE_WO_OPERATION_MATERIALS wom,

WIE_WO_OPERATIONS_B WWOOB,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esib

WHERE wob.work_method_id = WMB.work_method_id

AND wmb.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING', 'MAINTENANCE')

AND wwsb.WO_STATUS_ID = wob.WORK_ORDER_STATUS_ID

AND wwsb.WO_SYSTEM_STATUS_CODE IN ('UNRELEASED','RELEASED','ON_HOLD','COMPLETED','PENDING_APPROVAL')

AND wob.work_order_id = WWOOB.work_order_id

AND WWOOB.wo_operation_id = wom.wo_operation_id

AND WWOOB.work_order_id = wom.work_order_id

AND wom.inventory_item_id = esib.inventory_item_id

AND wom.organization_id = esib.organization_id

AND wom.include_in_planning_flag = 'Y'

AND wom.material_type IN ('COMPONENT','INDIRECT')

AND wom.inventory_item_id != wob.inventory_item_id

AND wom.quantity >= 0