MNT_WO_DISPATCH_LISTS

Details

Schema: FUSION
Object owner: MNT
Object type: VIEW

Columns

Name
WORK_ORDER_ID WORK_DEFINITION_ID WO_OBJECT_VERSION_NUMBER WORK_ORDER_NUMBER WORK_ORDER_DESCRIPTION WORK_ORDER_PRIORITY RELEASED_DATE ORGANIZATION_ID UOM UOM_CODE PLANNED_START_QUANTITY ITEM_NUMBER INVENTORY_ITEM_ID ITEM_DESCRIPTION SERIAL_CONTROLLED_ITEM_FLAG LOT_CONTROLLED_ITEM_FLAG PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_PROJECT_NUMBER PJC_TASK_ID PJC_TASK_NUMBER PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_WORK_TYPE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID WORK_CENTER_NAME WORK_CENTER_ID WORK_AREA_ID WO_OPERATION_ID WO_OPERATION_NAME OPERATION_SEQ_NUMBER EXECUTION_SEQUENCE RESEQUENCE_FLAG COMPLETED_QUANTITY READY_QUANTITY IN_PROCESS_QUANTITY OPERATION_TYPE OSP_ITEM_ID OSP_ITEM_NUMBER DISPATCH_STATE DISPATCH_LOOKUP_CODE QUANTITY PREVIOUS_CP_OP_SEQ_NUM NEXT_CP_OP_SEQ_NUM TOLL_GATE_OP_SEQ_NUM OPERATION_START_DATE OPERATION_COMPLETION_DATE PO_HEADER_ID PO_LINE_ID PO_HEADER_NUMBER PO_LINE_NUMBER ASSET_ID ASSET_NUMBER SERIAL_NUMBER ASSET_DESCRIPTION SUPPLIER_ID SUPPLIER_NAME SUPPLIER_SITE SUPPLIER_SITE_ID MATERIAL_REPORTING_INDICATOR RESOURCE_REPORTING_INDICATOR WORK_ORDER_TYPE WORK_ORDER_SUB_TYPE ITEM_REVISION COUNT_POINT_OPERATION_FLAG

Name

WORK_ORDER_ID

WORK_DEFINITION_ID

WO_OBJECT_VERSION_NUMBER

WORK_ORDER_NUMBER

WORK_ORDER_DESCRIPTION

WORK_ORDER_PRIORITY

RELEASED_DATE

ORGANIZATION_ID

UOM

UOM_CODE

PLANNED_START_QUANTITY

ITEM_NUMBER

INVENTORY_ITEM_ID

ITEM_DESCRIPTION

SERIAL_CONTROLLED_ITEM_FLAG

LOT_CONTROLLED_ITEM_FLAG

PJC_CONTEXT_CATEGORY

PJC_PROJECT_ID

PJC_PROJECT_NUMBER

PJC_TASK_ID

PJC_TASK_NUMBER

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_WORK_TYPE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

WORK_CENTER_NAME

WORK_CENTER_ID

WORK_AREA_ID

WO_OPERATION_ID

WO_OPERATION_NAME

OPERATION_SEQ_NUMBER

EXECUTION_SEQUENCE

RESEQUENCE_FLAG

COMPLETED_QUANTITY

READY_QUANTITY

IN_PROCESS_QUANTITY

OPERATION_TYPE

OSP_ITEM_ID

OSP_ITEM_NUMBER

DISPATCH_STATE

DISPATCH_LOOKUP_CODE

QUANTITY

PREVIOUS_CP_OP_SEQ_NUM

NEXT_CP_OP_SEQ_NUM

TOLL_GATE_OP_SEQ_NUM

OPERATION_START_DATE

OPERATION_COMPLETION_DATE

PO_HEADER_ID

PO_LINE_ID

PO_HEADER_NUMBER

PO_LINE_NUMBER

ASSET_ID

ASSET_NUMBER

SERIAL_NUMBER

ASSET_DESCRIPTION

SUPPLIER_ID

SUPPLIER_NAME

SUPPLIER_SITE

SUPPLIER_SITE_ID

MATERIAL_REPORTING_INDICATOR

RESOURCE_REPORTING_INDICATOR

WORK_ORDER_TYPE

WORK_ORDER_SUB_TYPE

ITEM_REVISION

COUNT_POINT_OPERATION_FLAG

Query

SQL_Statement
SELECT wo.work_order_id WORK_ORDER_ID, wo.work_definition_id WORK_DEFINITION_ID, wo.object_version_number WO_OBJECT_VERSION_NUMBER, wo.work_order_number WORK_ORDER_NUMBER, wo.work_order_description WORK_ORDER_DESCRIPTION, 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, CASE WHEN esi.serial_number_control_code IN (2,5,6) THEN 'Y' ELSE 'N' END SERIAL_CONTROLLED_ITEM_FLAG, CASE WHEN esi.lot_control_code = 2 THEN 'Y' ELSE 'N' END LOT_CONTROLLED_ITEM_FLAG, wo.pjc_context_category pjc_context_category, wo.pjc_project_id pjc_project_id, wo.pjc_project_number pjc_project_number, wo.pjc_task_id pjc_task_id, wo.pjc_task_number pjc_task_number, wo.pjc_expenditure_type_id pjc_expenditure_type_id, wo.pjc_expenditure_item_date pjc_expenditure_item_date, wo.pjc_organization_id pjc_organization_id, wo.pjc_billable_flag pjc_billable_flag, wo.pjc_capitalizable_flag pjc_capitalizable_flag, wo.pjc_work_type_id pjc_work_type_id, wo.pjc_contract_id pjc_contract_id, wo.pjc_contract_line_id pjc_contract_line_id, wo.pjc_funding_allocation_id pjc_funding_allocation_id, 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.execution_sequence EXECUTION_SEQUENCE, woop.resequence_flag RESEQUENCE_FLAG, woop.completed_quantity COMPLETED_QUANTITY, woop.ready_quantity READY_QUANTITY, woop.in_process_quantity IN_PROCESS_QUANTITY, woop.operation_type OPERATION_TYPE, woop.osp_item_id OSP_ITEM_ID, ospi.item_number OSP_ITEM_NUMBER, 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 IN ('OPTIONAL', 'IN_PROCESS') 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.tollgate_op_seq_num TOLL_GATE_OP_SEQ_NUM, woop.planned_start_date OPERATION_START_DATE, woop.planned_completion_date OPERATION_COMPLETION_DATE, 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, cab.asset_id ASSET_ID, cab.asset_number ASSET_NUMBER, cab.SERIAL_NUMBER, cab.description ASSET_DESCRIPTION, DECODE(woop.operation_type, 'SUPPLIER', 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 DISTINCT osp.supplier_id 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 NULL END, NULL ) AS SUPPLIER_ID, DECODE(woop.operation_type, 'SUPPLIER', 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, NULL ) AS SUPPLIER_NAME, DECODE(woop.operation_type, '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, NULL ) AS SUPPLIER_SITE, DECODE(woop.operation_type, '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 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, NULL ) AS SUPPLIER_SITE_ID, 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_REPORTING_INDICATOR, NVL( (SELECT SUM(DECODE(SIGN(NVL(ACTUAL_RESOURCE_USAGE,0)-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)RESOURCE_REPORTING_INDICATOR, wo.work_order_type WORK_ORDER_TYPE, wo.work_order_sub_type WORK_ORDER_SUB_TYPE, wo.item_revision ITEM_REVISION, woop.count_point_operation_flag COUNT_POINT_OPERATION_FLAG FROM wie_wo_operations_vl woop, WIE_WORK_ORDERS_VL wo, wis_work_centers_vl wc, egp_system_items_vl esi, egp_system_items_vl ospi, fnd_lookups fl, inv_units_of_measure_vl uoms, po_headers_all poh, po_lines_all pol, wie_wo_assets wwa, cse_assets_vl cab WHERE wo.work_order_id = woop.work_order_id AND wo.organization_id = woop.organization_id AND woop.osp_item_id = ospi.inventory_item_id(+) AND woop.organization_id = ospi.organization_id(+) AND woop.work_center_id = wc.work_center_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 fl.lookup_type = 'ORA_WIE_DISPATCH_STATE' 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 wwa.work_order_id = wo.work_order_id AND cab.asset_id = wwa.asset_id AND esi.inventory_item_id = cab.item_id AND esi.organization_id = cab.item_organization_id AND NVL(woop.auto_transact_flag,'N') = 'N' AND ((fl.lookup_code = 'READY' AND (woop.ready_quantity > 0 OR (woop.resequence_flag='Y' AND NVL(woop.ready_quantity,0) = 0 AND NVL(woop.completed_quantity,0) = 0 AND EXISTS (SELECT 1 FROM wie_wo_operations_b WHERE work_order_id = woop.work_order_id AND ready_quantity > 0 AND NVL(completed_quantity,0) = 0 AND tollgate_op_seq_num=woop.tollgate_op_seq_num)))) OR(woop.completed_quantity > 0 AND fl.lookup_code = 'COMPLETE') OR(NVL(WOOP.COUNT_POINT_OPERATION_FLAG, 'N') = 'N' AND fl.lookup_code = 'OPTIONAL') OR (woop.operation_type = 'SUPPLIER' AND (woop.in_process_quantity > 0 OR (NVL(woop.in_process_quantity, 0) = 0 AND NVL(woop.ready_quantity, 0) = 0 AND NVL(woop.completed_quantity, 0) = 0)) AND fl.lookup_code = 'IN_PROCESS') )

SQL_Statement

SELECT wo.work_order_id WORK_ORDER_ID,

wo.work_definition_id WORK_DEFINITION_ID,

wo.object_version_number WO_OBJECT_VERSION_NUMBER,

wo.work_order_number WORK_ORDER_NUMBER,

wo.work_order_description WORK_ORDER_DESCRIPTION,

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,

CASE

WHEN esi.serial_number_control_code IN (2,5,6)

THEN 'Y'

ELSE 'N'

END SERIAL_CONTROLLED_ITEM_FLAG,

CASE

WHEN esi.lot_control_code = 2

THEN 'Y'

ELSE 'N'

END LOT_CONTROLLED_ITEM_FLAG,

wo.pjc_context_category pjc_context_category,

wo.pjc_project_id pjc_project_id,

wo.pjc_project_number pjc_project_number,

wo.pjc_task_id pjc_task_id,

wo.pjc_task_number pjc_task_number,

wo.pjc_expenditure_type_id pjc_expenditure_type_id,

wo.pjc_expenditure_item_date pjc_expenditure_item_date,

wo.pjc_organization_id pjc_organization_id,

wo.pjc_billable_flag pjc_billable_flag,

wo.pjc_capitalizable_flag pjc_capitalizable_flag,

wo.pjc_work_type_id pjc_work_type_id,

wo.pjc_contract_id pjc_contract_id,

wo.pjc_contract_line_id pjc_contract_line_id,

wo.pjc_funding_allocation_id pjc_funding_allocation_id,

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.execution_sequence EXECUTION_SEQUENCE,

woop.resequence_flag RESEQUENCE_FLAG,

woop.completed_quantity COMPLETED_QUANTITY,

woop.ready_quantity READY_QUANTITY,

woop.in_process_quantity IN_PROCESS_QUANTITY,

woop.operation_type OPERATION_TYPE,

woop.osp_item_id OSP_ITEM_ID,

ospi.item_number OSP_ITEM_NUMBER,

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 IN ('OPTIONAL', 'IN_PROCESS')

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.tollgate_op_seq_num TOLL_GATE_OP_SEQ_NUM,

woop.planned_start_date OPERATION_START_DATE,

woop.planned_completion_date OPERATION_COMPLETION_DATE,

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,

cab.asset_id ASSET_ID,

cab.asset_number ASSET_NUMBER,

cab.SERIAL_NUMBER,

cab.description ASSET_DESCRIPTION,

DECODE(woop.operation_type, 'SUPPLIER',

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 DISTINCT osp.supplier_id

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

NULL

END,

NULL

) AS SUPPLIER_ID,

DECODE(woop.operation_type, 'SUPPLIER',

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,

NULL

) AS SUPPLIER_NAME,

DECODE(woop.operation_type, '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,

NULL

) AS SUPPLIER_SITE,

DECODE(woop.operation_type, '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 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,

NULL

) AS SUPPLIER_SITE_ID,

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_REPORTING_INDICATOR,

NVL(

(SELECT SUM(DECODE(SIGN(NVL(ACTUAL_RESOURCE_USAGE,0)-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)RESOURCE_REPORTING_INDICATOR,

wo.work_order_type WORK_ORDER_TYPE,

wo.work_order_sub_type WORK_ORDER_SUB_TYPE,

wo.item_revision ITEM_REVISION,

woop.count_point_operation_flag COUNT_POINT_OPERATION_FLAG

FROM wie_wo_operations_vl woop,

WIE_WORK_ORDERS_VL wo,

wis_work_centers_vl wc,

egp_system_items_vl esi,

egp_system_items_vl ospi,

fnd_lookups fl,

inv_units_of_measure_vl uoms,

po_headers_all poh,

po_lines_all pol,

wie_wo_assets wwa,

cse_assets_vl cab

WHERE wo.work_order_id = woop.work_order_id

AND wo.organization_id = woop.organization_id

AND woop.osp_item_id = ospi.inventory_item_id(+)

AND woop.organization_id = ospi.organization_id(+)

AND woop.work_center_id = wc.work_center_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 fl.lookup_type = 'ORA_WIE_DISPATCH_STATE'

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 wwa.work_order_id = wo.work_order_id

AND cab.asset_id = wwa.asset_id

AND esi.inventory_item_id = cab.item_id

AND esi.organization_id = cab.item_organization_id

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

AND ((fl.lookup_code = 'READY'

AND (woop.ready_quantity > 0

OR (woop.resequence_flag='Y' AND NVL(woop.ready_quantity,0) = 0 AND NVL(woop.completed_quantity,0) = 0

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

AND ready_quantity > 0

AND NVL(completed_quantity,0) = 0

AND tollgate_op_seq_num=woop.tollgate_op_seq_num))))

OR(woop.completed_quantity > 0

AND fl.lookup_code = 'COMPLETE')

OR(NVL(WOOP.COUNT_POINT_OPERATION_FLAG, 'N') = 'N'

AND fl.lookup_code = 'OPTIONAL')

OR (woop.operation_type = 'SUPPLIER'

AND (woop.in_process_quantity > 0

OR (NVL(woop.in_process_quantity, 0) = 0

AND NVL(woop.ready_quantity, 0) = 0

AND NVL(woop.completed_quantity, 0) = 0))

AND fl.lookup_code = 'IN_PROCESS')

)