WIE_WO_DISPATCH_LISTS_V
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 |
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 (+) |