WIE_WO_DISPATCH_LISTS

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
WORK_ORDER_ID WO_OBJECT_VERSION_NUMBER WORK_ORDER_NUMBER WORK_ORDER_PRIORITY RELEASED_DATE ORGANIZATION_ID UOM UOM_CODE PLANNED_START_QUANTITY ITEM_NUMBER INVENTORY_ITEM_ID ITEM_DESCRIPTION WORK_CENTER_NAME WORK_CENTER_ID WORK_AREA_ID WO_OPERATION_ID WO_OPERATION_NAME OPERATION_SEQ_NUMBER COMPLETED_QUANTITY SCRAPPED_QUANTITY REJECTED_QUANTITY READY_QUANTITY DISPATCH_STATE DISPATCH_LOOKUP_CODE QUANTITY PREVIOUS_CP_OP_SEQ_NUM NEXT_CP_OP_SEQ_NUM OPERATION_START_DATE OPERATION_COMPLETION_DATE SERIAL_TRACKING_FLAG MATERIAL_ICON_IND RESOURCE_ICON_IND PO_HEADER_ID PO_LINE_ID PO_HEADER_NUMBER PO_LINE_NUMBER OPERATION_TYPE OPERATION_START_TIME STANDARD_OPERATION_ID ITEM_REVISION SOURCE_FULFILL_HEADER_ID SOURCE_FULFILL_LINE_ID WORK_METHOD WORK_METHOD_CODE OUTPUT_ICON_IND OP_COMPL_WITH_UNDER_ISSUE OP_COMPL_WITH_OPEN_EXCEPTIONS CUMULATIVE_SCRAP_QUANTITY PROJECT_ID PROJECT_NUMBER TASK_ID TASK_NUMBER WORK_ORDER_TYPE PREASSIGN_LOT_FLAG RESEQUENCE_FLAG EXECUTION_SEQUENCE TOLL_GATE_OP_SEQ_NUM UNDER_COMPLETED_FLAG

Name

WORK_ORDER_ID

WO_OBJECT_VERSION_NUMBER

WORK_ORDER_NUMBER

WORK_ORDER_PRIORITY

RELEASED_DATE

ORGANIZATION_ID

UOM

UOM_CODE

PLANNED_START_QUANTITY

ITEM_NUMBER

INVENTORY_ITEM_ID

ITEM_DESCRIPTION

WORK_CENTER_NAME

WORK_CENTER_ID

WORK_AREA_ID

WO_OPERATION_ID

WO_OPERATION_NAME

OPERATION_SEQ_NUMBER

COMPLETED_QUANTITY

SCRAPPED_QUANTITY

REJECTED_QUANTITY

READY_QUANTITY

DISPATCH_STATE

DISPATCH_LOOKUP_CODE

QUANTITY

PREVIOUS_CP_OP_SEQ_NUM

NEXT_CP_OP_SEQ_NUM

OPERATION_START_DATE

OPERATION_COMPLETION_DATE

SERIAL_TRACKING_FLAG

MATERIAL_ICON_IND

RESOURCE_ICON_IND

PO_HEADER_ID

PO_LINE_ID

PO_HEADER_NUMBER

PO_LINE_NUMBER

OPERATION_TYPE

OPERATION_START_TIME

STANDARD_OPERATION_ID

ITEM_REVISION

SOURCE_FULFILL_HEADER_ID

SOURCE_FULFILL_LINE_ID

WORK_METHOD

WORK_METHOD_CODE

OUTPUT_ICON_IND

OP_COMPL_WITH_UNDER_ISSUE

OP_COMPL_WITH_OPEN_EXCEPTIONS

CUMULATIVE_SCRAP_QUANTITY

PROJECT_ID

PROJECT_NUMBER

TASK_ID

TASK_NUMBER

WORK_ORDER_TYPE

PREASSIGN_LOT_FLAG

RESEQUENCE_FLAG

EXECUTION_SEQUENCE

TOLL_GATE_OP_SEQ_NUM

UNDER_COMPLETED_FLAG

Query

SQL_Statement
SELECT wo.work_order_id WORK_ORDER_ID, wo.object_version_number WO_OBJECT_VERSION_NUMBER, wo.work_order_number WORK_ORDER_NUMBER, wo.work_order_priority WORK_ORDER_PRIORITY, wo.released_date RELEASED_DATE, wo.organization_id ORGANIZATION_ID, uoms.unit_of_measure UOM, wo.uom_code UOM_CODE, wo.planned_start_quantity PLANNED_START_QUANTITY, esi.item_number ITEM_NUMBER, esi.inventory_item_id INVENTORY_ITEM_ID, esi.description ITEM_DESCRIPTION, wc.work_center_name WORK_CENTER_NAME, wc.work_center_id WORK_CENTER_ID, wc.work_area_id WORK_AREA_ID, woop.wo_operation_id WO_OPERATION_ID, woop.operation_name WO_OPERATION_NAME, woop.operation_seq_number OPERATION_SEQ_NUMBER, woop.completed_quantity COMPLETED_QUANTITY, woop.scrapped_quantity SCRAPPED_QUANTITY, woop.rejected_quantity REJECTED_QUANTITY, woop.ready_quantity READY_QUANTITY, fl.meaning DISPATCH_STATE, fl.lookup_code DISPATCH_LOOKUP_CODE, CASE WHEN fl.lookup_code = 'READY' THEN NVL(woop.ready_quantity,0) WHEN fl.lookup_code = 'COMPLETE' THEN woop.completed_quantity WHEN fl.lookup_code = 'REJECT' THEN woop.rejected_quantity WHEN fl.lookup_code = 'SCRAP' THEN woop.scrapped_quantity WHEN fl.lookup_code = 'IN_PROCESS' THEN woop.in_process_quantity WHEN fl.lookup_code = 'OPTIONAL' THEN NULL END QUANTITY, woop.previous_cp_op_seq_num PREVIOUS_CP_OP_SEQ_NUM, woop.next_cp_op_seq_num NEXT_CP_OP_SEQ_NUM, woop.planned_start_date OPERATION_START_DATE, woop.planned_completion_date OPERATION_COMPLETION_DATE, NVL(woop.serial_tracking_flag,'N') SERIAL_TRACKING_FLAG, CASE COUNT_POINT_OPERATION_FLAG WHEN 'Y' THEN NVL( (SELECT SUM(DECODE(SIGN(NVL(opmat.issued_quantity,0)-opmat.quantity),-1,1,0)) FROM wie_wo_operation_materials opmat WHERE opmat.wo_operation_id = woop.wo_operation_id AND opmat.work_order_id = woop.work_order_id AND opmat.supply_type = '1'),-1) ELSE -1 END material_icon_ind, CASE COUNT_POINT_OPERATION_FLAG WHEN 'Y' THEN NVL( (SELECT SUM(DECODE(SIGN(NVL(opresource.actual_resource_usage,0)-opresource.required_usage),-1,1,0)) FROM wie_wo_operation_resources opresource WHERE opresource.wo_operation_id = woop.wo_operation_id AND opresource.charge_type = 'MANUAL'),-1) ELSE -1 END resource_icon_ind, wo.cm_po_header_id PO_HEADER_ID, wo.cm_po_line_id PO_LINE_ID, poh.segment1 PO_HEADER_NUMBER, pol.line_num PO_LINE_NUMBER, woop.operation_type OPERATION_TYPE, oss.operation_start_time OPERATION_START_TIME, woop.standard_operation_id, wo.item_revision, doo.header_id source_fulfill_header_id, doo.fulfill_line_id source_fulfill_line_id, wwm.work_method_name WORK_METHOD, wwm.work_method_code WORK_METHOD_CODE, CASE WHEN (wwm.work_method_code='PROCESS_MANUFACTURING') OR ((wo.work_order_type IN ('STANDARD', 'NON_STANDARD')) AND ((NVL(woop.count_point_operation_flag,'N') = 'Y') OR (NVL(woop.count_point_operation_flag,'N') = 'N' AND NVL(woop.auto_transact_flag,'N') = 'N'))) THEN (SELECT CASE WHEN ((MAX(NVL(wwoo.output_quantity,0)-NVL(wwoo.completed_quantity,0)) = 0) or (SIGN(MAX(NVL(wwoo.output_quantity,0)-NVL(wwoo.completed_quantity,0)))=-1)) THEN 'CHECKMARK' WHEN MAX(1) IS NULL THEN 'CIRCLE' ELSE 'CHECKLIST' END FROM wie_wo_operation_outputs wwoo WHERE wwoo.wo_operation_id = woop.wo_operation_id AND wwoo.completion_type = 'MANUAL') ELSE 'NONE' END output_icon_ind, NVL(woop.op_compl_with_under_issue,'ALLOW') op_compl_with_under_issue, NVL(woop.op_compl_with_open_exceptions,'ALLOW') op_compl_with_open_exceptions, NVL(woop.cumulative_scrap_quantity,0) cumulative_scrap_quantity, wo.pjc_project_id PROJECT_ID, wo.pjc_project_number PROJECT_NUMBER, wo.pjc_task_id TASK_ID, wo.pjc_task_number TASK_NUMBER, wo.work_order_type WORK_ORDER_TYPE, wo.preassign_lot_flag PREASSIGN_LOT_FLAG, woop.resequence_flag RESEQUENCE_FLAG, woop.execution_sequence EXECUTION_SEQUENCE, woop.tollgate_op_seq_num TOLL_GATE_OP_SEQ_NUM, wo.under_completed_flag UNDER_COMPLETED_FLAG FROM wie_wo_operations_vl woop, wie_work_orders_b wo, wis_work_centers_vl wc, egp_system_items_vl esi, fnd_lookups fl, inv_units_of_measure_vl uoms, po_headers_all poh, po_lines_all pol, dos_supply_tracking_lines dos, doo_fulfill_lines_all doo, wis_work_methods_vl wwm, (SELECT wwoss.operation_start_time, wwoss.wo_operation_id FROM wie_wo_operation_start_stop wwoss WHERE wwoss.operation_stop_time IS NULL AND (wwoss.wo_product_serial_id IS NULL OR wwoss.wo_product_serial_id = (SELECT inner_wwoss.wo_product_serial_id FROM wie_wo_operation_start_stop inner_wwoss WHERE inner_wwoss.operation_stop_time IS NULL AND inner_wwoss.wo_operation_id = wwoss.wo_operation_id AND rownum = 1))) oss WHERE wo.work_order_id = woop.work_order_id AND wo.organization_id = woop.organization_id AND woop.work_center_id = wc.work_center_id AND wo.inventory_item_id = esi.inventory_item_id AND wo.organization_id = esi.organization_id AND uoms.uom_code = wo.uom_code AND wo.cm_po_header_id = pol.po_header_id (+) AND wo.cm_po_line_id = pol.po_line_id (+) AND wo.cm_po_header_id = poh.po_header_id (+) AND wo.source_line_ref_id = dos.tracking_line_id (+) AND dos.supply_order_reference_line_id = doo.fulfill_line_id (+) AND fl.lookup_type = 'ORA_WIE_DISPATCH_STATE' AND wo.work_method_id = wwm.work_method_id AND wwm.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING') AND wo.work_order_status_id IN (SELECT wost.wo_status_id FROM wie_wo_statuses_b wost WHERE wost.wo_system_status_code IN ('RELEASED','COMPLETED')) AND wo.order_less_flag = 'N' AND NVL(woop.auto_transact_flag,'N') = 'N' AND((fl.lookup_code = 'READY' AND (woop.ready_quantity > 0 OR ((NVL(woop.ready_quantity,0) = 0 AND NVL(woop.completed_quantity,0) = 0 AND NVL(woop.rejected_quantity,0) = 0 AND NVL(woop.scrapped_quantity,0) = 0) AND woop.resequence_flag = 'Y' AND EXISTS (SELECT 1 FROM wie_wo_operations_b opexists WHERE opexists.work_order_id = woop.work_order_id AND opexists.tollgate_op_seq_num = woop.tollgate_op_seq_num AND opexists.ready_quantity > 0 AND NVL(opexists.completed_quantity,0) = 0 AND NVL(opexists.rejected_quantity,0) = 0 AND NVL(opexists.scrapped_quantity,0) = 0)))) OR(woop.completed_quantity > 0 AND fl.lookup_code = 'COMPLETE') OR(woop.rejected_quantity > 0 AND fl.lookup_code = 'REJECT') OR(woop.scrapped_quantity > 0 AND fl.lookup_code = 'SCRAP') OR (woop.IN_PROCESS_QUANTITY > 0 AND fl.lookup_code = 'IN_PROCESS') OR(NVL(woop.count_point_operation_flag,'N') = 'N' AND fl.lookup_code = 'OPTIONAL')) AND woop.wo_operation_id = oss.wo_operation_id (+)

SQL_Statement

SELECT wo.work_order_id WORK_ORDER_ID,

wo.object_version_number WO_OBJECT_VERSION_NUMBER,

wo.work_order_number WORK_ORDER_NUMBER,

wo.work_order_priority WORK_ORDER_PRIORITY,

wo.released_date RELEASED_DATE,

wo.organization_id ORGANIZATION_ID,

uoms.unit_of_measure UOM,

wo.uom_code UOM_CODE,

wo.planned_start_quantity PLANNED_START_QUANTITY,

esi.item_number ITEM_NUMBER,

esi.inventory_item_id INVENTORY_ITEM_ID,

esi.description ITEM_DESCRIPTION,

wc.work_center_name WORK_CENTER_NAME,

wc.work_center_id WORK_CENTER_ID,

wc.work_area_id WORK_AREA_ID,

woop.wo_operation_id WO_OPERATION_ID,

woop.operation_name WO_OPERATION_NAME,

woop.operation_seq_number OPERATION_SEQ_NUMBER,

woop.completed_quantity COMPLETED_QUANTITY,

woop.scrapped_quantity SCRAPPED_QUANTITY,

woop.rejected_quantity REJECTED_QUANTITY,

woop.ready_quantity READY_QUANTITY,

fl.meaning DISPATCH_STATE,

fl.lookup_code DISPATCH_LOOKUP_CODE,

CASE

WHEN fl.lookup_code = 'READY'

THEN NVL(woop.ready_quantity,0)

WHEN fl.lookup_code = 'COMPLETE'

THEN woop.completed_quantity

WHEN fl.lookup_code = 'REJECT'

THEN woop.rejected_quantity

WHEN fl.lookup_code = 'SCRAP'

THEN woop.scrapped_quantity

WHEN fl.lookup_code = 'IN_PROCESS'

THEN woop.in_process_quantity

WHEN fl.lookup_code = 'OPTIONAL'

THEN NULL

END QUANTITY,

woop.previous_cp_op_seq_num PREVIOUS_CP_OP_SEQ_NUM,

woop.next_cp_op_seq_num NEXT_CP_OP_SEQ_NUM,

woop.planned_start_date OPERATION_START_DATE,

woop.planned_completion_date OPERATION_COMPLETION_DATE,

NVL(woop.serial_tracking_flag,'N') SERIAL_TRACKING_FLAG,

CASE COUNT_POINT_OPERATION_FLAG

WHEN 'Y'

THEN NVL(

(SELECT SUM(DECODE(SIGN(NVL(opmat.issued_quantity,0)-opmat.quantity),-1,1,0))

FROM wie_wo_operation_materials opmat

WHERE opmat.wo_operation_id = woop.wo_operation_id

AND opmat.work_order_id = woop.work_order_id

AND opmat.supply_type = '1'),-1)

ELSE -1

END material_icon_ind,

CASE COUNT_POINT_OPERATION_FLAG

WHEN 'Y'

THEN NVL(

(SELECT SUM(DECODE(SIGN(NVL(opresource.actual_resource_usage,0)-opresource.required_usage),-1,1,0))

FROM wie_wo_operation_resources opresource

WHERE opresource.wo_operation_id = woop.wo_operation_id

AND opresource.charge_type = 'MANUAL'),-1)

ELSE -1

END resource_icon_ind,

wo.cm_po_header_id PO_HEADER_ID,

wo.cm_po_line_id PO_LINE_ID,

poh.segment1 PO_HEADER_NUMBER,

pol.line_num PO_LINE_NUMBER,

woop.operation_type OPERATION_TYPE,

oss.operation_start_time OPERATION_START_TIME,

woop.standard_operation_id,

wo.item_revision,

doo.header_id source_fulfill_header_id,

doo.fulfill_line_id source_fulfill_line_id,

wwm.work_method_name WORK_METHOD,

wwm.work_method_code WORK_METHOD_CODE,

CASE

WHEN (wwm.work_method_code='PROCESS_MANUFACTURING') OR ((wo.work_order_type IN ('STANDARD', 'NON_STANDARD')) AND ((NVL(woop.count_point_operation_flag,'N') = 'Y') OR (NVL(woop.count_point_operation_flag,'N') = 'N' AND NVL(woop.auto_transact_flag,'N') = 'N')))

THEN

(SELECT

CASE

WHEN ((MAX(NVL(wwoo.output_quantity,0)-NVL(wwoo.completed_quantity,0)) = 0) or (SIGN(MAX(NVL(wwoo.output_quantity,0)-NVL(wwoo.completed_quantity,0)))=-1))

THEN 'CHECKMARK'

WHEN MAX(1) IS NULL

THEN 'CIRCLE'

ELSE 'CHECKLIST'

END

FROM wie_wo_operation_outputs wwoo

WHERE wwoo.wo_operation_id = woop.wo_operation_id

AND wwoo.completion_type = 'MANUAL')

ELSE 'NONE'

END output_icon_ind,

NVL(woop.op_compl_with_under_issue,'ALLOW') op_compl_with_under_issue,

NVL(woop.op_compl_with_open_exceptions,'ALLOW') op_compl_with_open_exceptions,

NVL(woop.cumulative_scrap_quantity,0) cumulative_scrap_quantity,

wo.pjc_project_id PROJECT_ID,

wo.pjc_project_number PROJECT_NUMBER,

wo.pjc_task_id TASK_ID,

wo.pjc_task_number TASK_NUMBER,

wo.work_order_type WORK_ORDER_TYPE,

wo.preassign_lot_flag PREASSIGN_LOT_FLAG,

woop.resequence_flag RESEQUENCE_FLAG,

woop.execution_sequence EXECUTION_SEQUENCE,

woop.tollgate_op_seq_num TOLL_GATE_OP_SEQ_NUM,

wo.under_completed_flag UNDER_COMPLETED_FLAG

FROM wie_wo_operations_vl woop,

wie_work_orders_b wo,

wis_work_centers_vl wc,

egp_system_items_vl esi,

fnd_lookups fl,

inv_units_of_measure_vl uoms,

po_headers_all poh,

po_lines_all pol,

dos_supply_tracking_lines dos,

doo_fulfill_lines_all doo,

wis_work_methods_vl wwm,

(SELECT wwoss.operation_start_time, wwoss.wo_operation_id

FROM wie_wo_operation_start_stop wwoss

WHERE wwoss.operation_stop_time IS NULL

AND (wwoss.wo_product_serial_id IS NULL

OR wwoss.wo_product_serial_id =

(SELECT inner_wwoss.wo_product_serial_id

FROM wie_wo_operation_start_stop inner_wwoss

WHERE inner_wwoss.operation_stop_time IS NULL

AND inner_wwoss.wo_operation_id = wwoss.wo_operation_id

AND rownum = 1))) oss

WHERE wo.work_order_id = woop.work_order_id

AND wo.organization_id = woop.organization_id

AND woop.work_center_id = wc.work_center_id

AND wo.inventory_item_id = esi.inventory_item_id

AND wo.organization_id = esi.organization_id

AND uoms.uom_code = wo.uom_code

AND wo.cm_po_header_id = pol.po_header_id (+)

AND wo.cm_po_line_id = pol.po_line_id (+)

AND wo.cm_po_header_id = poh.po_header_id (+)

AND wo.source_line_ref_id = dos.tracking_line_id (+)

AND dos.supply_order_reference_line_id = doo.fulfill_line_id (+)

AND fl.lookup_type = 'ORA_WIE_DISPATCH_STATE'

AND wo.work_method_id = wwm.work_method_id

AND wwm.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING')

AND wo.work_order_status_id IN (SELECT wost.wo_status_id FROM wie_wo_statuses_b wost

WHERE wost.wo_system_status_code IN ('RELEASED','COMPLETED'))

AND wo.order_less_flag = 'N'

AND NVL(woop.auto_transact_flag,'N') = 'N'

AND((fl.lookup_code = 'READY'

AND (woop.ready_quantity > 0

OR ((NVL(woop.ready_quantity,0) = 0 AND NVL(woop.completed_quantity,0) = 0 AND NVL(woop.rejected_quantity,0) = 0 AND NVL(woop.scrapped_quantity,0) = 0)

AND woop.resequence_flag = 'Y'

AND EXISTS (SELECT 1 FROM wie_wo_operations_b opexists WHERE opexists.work_order_id = woop.work_order_id

AND opexists.tollgate_op_seq_num = woop.tollgate_op_seq_num

AND opexists.ready_quantity > 0 AND NVL(opexists.completed_quantity,0) = 0 AND NVL(opexists.rejected_quantity,0) = 0 AND NVL(opexists.scrapped_quantity,0) = 0))))

OR(woop.completed_quantity > 0 AND fl.lookup_code = 'COMPLETE')

OR(woop.rejected_quantity > 0 AND fl.lookup_code = 'REJECT')

OR(woop.scrapped_quantity > 0 AND fl.lookup_code = 'SCRAP')

OR (woop.IN_PROCESS_QUANTITY > 0 AND fl.lookup_code = 'IN_PROCESS')

OR(NVL(woop.count_point_operation_flag,'N') = 'N' AND fl.lookup_code = 'OPTIONAL'))

AND woop.wo_operation_id = oss.wo_operation_id (+)