WIE_WO_SUPPLIER_OPERATIONS

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

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' 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)) ))

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'

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)) ))