WIE_WO_SUPPLIER_OPERATIONS_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 WO_OPERATION_ID OPERATION_SEQ_NUMBER READY_QUANTITY COMPLETED_QUANTITY SCRAPPED_QUANTITY REJECTED_QUANTITY PO_REQUESTED_QUANTITY PO_APPROVED_QUANTITY SHIPPED_QUANTITY RECEIVED_QUANTITY OVER_RECEIPT_QUANTITY IN_PROCESS_QUANTITY OPERATION_TYPE OSP_ITEM_ID OSP_ITEM_NUMBER SHIPPING_DOCUMENTS_FLAG 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 SUPPLIER SUPPLIER_SITE VENDOR_SITE_ID WORK_CENTER_ID WORK_AREA_ID SOURCE_FULFILL_HEADER_ID SOURCE_FULFILL_LINE_ID OPERATION_NAME WORK_METHOD_CODE WORK_METHOD_NAME 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 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, (SELECT uom.unit_of_measure FROM inv_units_of_measure_vl uom WHERE uom.uom_code = wo.uom_code ) UOM, wo.uom_code UOM_CODE, wo.planned_start_quantity PLANNED_START_QUANTITY, (SELECT pitem.item_number FROM egp_system_items_vl pitem WHERE pitem.inventory_item_id = wo.inventory_item_id AND pitem.organization_id = wo.organization_id ) ITEM_NUMBER, wo.inventory_item_id INVENTORY_ITEM_ID, (SELECT itemvl.description FROM egp_system_items_vl itemvl WHERE itemvl.inventory_item_id = wo.inventory_item_id AND itemvl.organization_id = wo.organization_id ) ITEM_DESCRIPTION, woop.wo_operation_id WO_OPERATION_ID, woop.operation_seq_number OPERATION_SEQ_NUMBER, NVL(woop.ready_quantity,0) READY_QUANTITY, NVL(woop.completed_quantity,0) COMPLETED_QUANTITY, NVL(woop.scrapped_quantity,0) SCRAPPED_QUANTITY, NVL(woop.rejected_quantity,0) REJECTED_QUANTITY, NVL(woop.po_requested_quantity,0) PO_REQUESTED_QUANTITY, NVL(woop.po_approved_quantity,0) PO_APPROVED_QUANTITY, NVL(woop.shipped_quantity,0) SHIPPED_QUANTITY, NVL(woop.received_quantity,0) RECEIVED_QUANTITY, NVL(woop.over_receipt_quantity,0) OVER_RECEIPT_QUANTITY, NVL(woop.in_process_quantity,0) IN_PROCESS_QUANTITY, woop.operation_type OPERATION_TYPE, woop.osp_item_id OSP_ITEM_ID, (SELECT oitem.item_number FROM egp_system_items_vl oitem WHERE oitem.inventory_item_id = woop.osp_item_id AND oitem.organization_id = woop.organization_id ) OSP_ITEM_NUMBER, woop.shipping_documents_flag SHIPPING_DOCUMENTS_FLAG, 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 NVL(woop.completed_quantity,0) WHEN fl.lookup_code = 'REJECT' THEN NVL(woop.rejected_quantity,0) WHEN fl.lookup_code = 'SCRAP' THEN NVL(woop.scrapped_quantity,0) WHEN fl.lookup_code = 'IN_PROCESS' THEN NVL(woop.in_process_quantity,0) 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, NVL( (SELECT SUM(DECODE(SIGN(NVL(issued_quantity,0)-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 supply_type ='1' ) ,-1) MATERIAL_ICON_IND, CASE DECODE( (SELECT COUNT(DISTINCT supplier_id) FROM wie_wo_osp_action_details WHERE document_type = 'PO' AND status ='APPROVED' AND wo_operation_id = woop.wo_operation_id ) ,0,'DEFAULT',1,'SINGLE','MULTIPLE') WHEN 'DEFAULT' THEN NULL WHEN 'SINGLE' THEN (SELECT parties.party_name FROM wie_wo_osp_action_details osp, poz_suppliers supplier, hz_parties parties WHERE osp.wo_operation_id = woop.wo_operation_id AND osp.document_type = 'PO' AND osp.status ='APPROVED' AND supplier.vendor_id = osp.supplier_id AND parties.party_id = supplier.party_id AND ROWNUM = 1 ) ELSE 'MULTIPLE' END SUPPLIER, CASE DECODE( (SELECT COUNT(DISTINCT supplier_site_id) FROM wie_wo_osp_action_details WHERE document_type = 'PO' AND status ='APPROVED' AND wo_operation_id = woop.wo_operation_id ) ,0,'DEFAULT',1,'SINGLE','MULTIPLE') WHEN 'DEFAULT' THEN NULL WHEN 'SINGLE' THEN (SELECT site.vendor_site_code FROM wie_wo_osp_action_details osp, poz_supplier_sites_all_m site WHERE osp.wo_operation_id = woop.wo_operation_id AND osp.document_type = 'PO' AND osp.status ='APPROVED' AND site.vendor_site_id = osp.supplier_site_id AND ROWNUM = 1 ) WHEN 'MULTIPLE' THEN 'MULTIPLE' END SUPPLIER_SITE, CASE DECODE( (SELECT COUNT(DISTINCT supplier_site_id) FROM wie_wo_osp_action_details WHERE document_type = 'PO' AND status ='APPROVED' AND wo_operation_id = woop.wo_operation_id ) ,0,'DEFAULT',1,'SINGLE','MULTIPLE') WHEN 'DEFAULT' THEN NULL WHEN 'SINGLE' THEN (SELECT DISTINCT osp.supplier_site_id FROM wie_wo_osp_action_details osp WHERE osp.wo_operation_id = woop.wo_operation_id AND osp.document_type = 'PO' AND osp.status ='APPROVED' AND ROWNUM = 1 ) WHEN 'MULTIPLE' THEN NULL END VENDOR_SITE_ID, wc.work_center_id WORK_CENTER_ID, wc.work_area_id WORK_AREA_ID, doo.header_id source_fulfill_header_id, doo.fulfill_line_id source_fulfill_line_id, woop.operation_name, wwm.work_method_code, wwm.work_method_name, CASE WHEN fl.lookup_code ='OPTIONAL' THEN 'NONE' WHEN (wwm.work_method_code='PROCESS_MANUFACTURING') OR ((wo.work_order_type = 'STANDARD' OR wo.work_order_type = 'NON_STANDARD') AND NVL(woop.count_point_operation_flag,'N') = 'Y') THEN (SELECT CASE WHEN ((MAX(NVL(output_quantity,0)-NVL(completed_quantity,0)) = 0) or (SIGN(MAX(NVL(output_quantity,0)-NVL(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 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.execution_sequence EXECUTION_SEQUENCE, woop.tollgate_op_seq_num TOLL_GATE_OP_SEQ_NUM, wo.under_completed_flag UNDER_COMPLETED_FLAG FROM wie_work_orders_b wo, wie_wo_operations_vl woop, wis_work_centers_b wc, fnd_lookups fl, dos_supply_tracking_lines dos, doo_fulfill_lines_all doo, wis_work_methods_vl wwm WHERE wo.order_less_flag ='N' 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 woop.work_order_id = wo.work_order_id AND wo.work_method_id = wwm.work_method_id AND woop.organization_id = wo.organization_id AND woop.operation_type ='SUPPLIER' AND wc.work_center_id = woop.work_center_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 ( (woop.ready_quantity > 0 AND fl.lookup_code = 'READY') 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(fl.lookup_code = 'IN_PROCESS' AND ( (NVL(woop.in_process_quantity,0) = 0 AND NVL(woop.po_requested_quantity,0) > NVL(woop.received_quantity,0)) OR (NVL(woop.in_process_quantity,0) > 0) OR (NVL(woop.over_receipt_quantity,0) > 0)) )) |