VIEW: APPS.EAM_WO_DIRECT_ITEMS_LITE_V

Source

View Text - Preformatted

SELECT wed.wip_entity_id AS wip_entity_id , wed.operation_seq_num operation_seq_num , wed.organization_id AS organization_id , to_number(NULL) AS item_id , wed.direct_item_sequence_id AS direct_item_sequence_id, TO_CHAR(NULL) AS item_name , wed.description AS item_description , wed.uom AS uom_code , wed.unit_price AS unit_price , wed.auto_request_material AS auto_request_material , wed.need_by_date AS need_by_date , wed.purchasing_category_id AS category_id , wed.required_quantity AS quantity_required , wed.quantity AS rql_quantity_ordered , wed.quantity_cancelled AS rql_quantity_cancelled , SUM(pd.quantity) AS quantity_ordered , SUM(pd.quantity_delivered) AS quantity_received , SUM(pd.quantity_ordered) AS po_quantity_ordered , SUM(pd.quantity_cancelled) AS po_quantity_cancelled , wed.suggested_vendor_name AS supplier_name , ml.meaning AS direct_item_type , 1 AS direct_item_type_id , wed.department_id AS department_id , to_number(NULL) AS effectivity_control , to_number(NULL) AS eam_item_type , NVL(wed.order_type_lookup_code, pd.order_type_lookup_code) AS order_type_lookup_code , NVL(wed.amount,pd.amount) AS amount , pd.amount_delivered AS amount_delivered , wed.rql_amount_ordered AS rql_amount_ordered , pd.amount AS po_amount_ordered , wed.attribute_category attribute_category , wed.attribute1 attribute1 , wed.attribute2 attribute2 , wed.attribute3 attribute3 , wed.attribute4 attribute4 , wed.attribute5 attribute5 , wed.attribute6 attribute6 , wed.attribute7 attribute7 , wed.attribute8 attribute8 , wed.attribute9 attribute9 , wed.attribute10 attribute10 , wed.attribute11 attribute11 , wed.attribute12 attribute12 , wed.attribute13 attribute13 , wed.attribute14 attribute14 , wed.attribute15 attribute15 FROM (SELECT wed.wip_entity_id , wed.operation_seq_num , wed.organization_id , wed.direct_item_sequence_id, wed.description , wed.uom , wed.unit_price , wed.auto_request_material , wed.need_by_date , wed.purchasing_category_id , wed.required_quantity , SUM( rql.quantity) quantity , SUM( CASE WHEN (upper(rqh.authorization_status)IN('REJECTED','SYSTEM_SAVED','CANCELLED')) THEN rql.quantity WHEN (upper(rqh .authorization_status) = 'APPROVED' AND rql.cancel_flag = 'Y') THEN rql.quantity ELSE 0 END) quantity_cancelled , wed.suggested_vendor_id , wed.suggested_vendor_name , wed.suggested_vendor_site_id , wed.suggested_vendor_contact_id , wed.department_id , NVL(rql.order_type_lookup_code, wed.order_type_lookup_code) AS order_type_lookup_code, NVL(rql.amount,wed.amount) AS amount , rql.amount AS rql_amount_ordered , wed.attribute_category , wed.attribute1 , wed.attribute2 , wed.attribute3 , wed.attribute4 , wed.attribute5 , wed.attribute6 , wed.attribute7 , wed.attribute8 , wed.attribute9 , wed.attribute10 , wed.attribute11 , wed.attribute12 , wed.attribute13 , wed.attribute14 , wed.attribute15 FROM wip_eam_direct_items wed, po_requisition_lines_all rql , po_requisition_headers_all rqh WHERE wed.wip_entity_id = rql.wip_entity_id(+) AND wed.organization_id = rql.destination_organization_id(+) AND wed.operation_seq_num = rql.wip_operation_seq_num(+) AND rql.requisition_header_id = rqh.requisition_header_id(+) AND rql.item_id IS NULL AND wed.direct_item_sequence_id = rql.wip_resource_seq_num(+) AND nvl(rql.modified_by_agent_flag,'N') <> 'Y' GROUP BY wed.wip_entity_id , wed.operation_seq_num , wed.organization_id , wed.direct_item_sequence_id , wed.description , wed.uom , rql.order_type_lookup_code , wed.order_type_lookup_code , wed.unit_price , rql.amount , wed.amount , wed.auto_request_material , wed.need_by_date , wed.purchasing_category_id , wed.required_quantity , wed.suggested_vendor_id , wed.suggested_vendor_name , wed.suggested_vendor_site_id , wed.suggested_vendor_contact_id, wed.department_id , wed.attribute_category , wed.attribute1 , wed.attribute2 , wed.attribute3 , wed.attribute4 , wed.attribute5 , wed.attribute6 , wed.attribute7 , wed.attribute8 , wed.attribute9 , wed.attribute10 , wed.attribute11 , wed.attribute12 , wed.attribute13 , wed.attribute14 , wed.attribute15 ) wed , (SELECT pol.quantity , SUM(pd1.quantity_delivered) quantity_delivered, SUM(pd1.quantity_ordered) quantity_ordered , SUM(pd1.quantity_cancelled) quantity_cancelled, pol.order_type_lookup_code , SUM(NVL(pd1.amount_ordered,0)) amount , SUM(NVL(pd1.amount_delivered,0)) amount_delivered , pd1.wip_entity_id , pd1.destination_organization_id , pd1.wip_operation_seq_num , pol.cancel_flag , pol.item_id , pd1.wip_resource_seq_num , pol.item_description , pol.po_line_id FROM po_lines_all pol, po_distributions_all pd1 WHERE pd1.po_line_id = pol.po_line_id AND( pd1.PO_RELEASE_ID IS NOT NULL OR pd1.DISTRIBUTION_TYPE = 'STANDARD') GROUP BY pd1.wip_entity_id , pd1.destination_organization_id, pd1.wip_operation_seq_num , pd1.wip_resource_seq_num , pol.cancel_flag , pol.item_id , pol.item_description , pol.order_type_lookup_code , pol.po_line_id , pol.quantity ) pd, mfg_lookups ml WHERE wed.wip_entity_id = pd.wip_entity_id(+) AND wed.organization_id = pd.destination_organization_id(+) AND wed.operation_seq_num = pd.wip_operation_seq_num(+) AND pd.item_id IS NULL AND wed.direct_item_sequence_id = pd.wip_resource_seq_num (+) AND ml.lookup_type = 'EAM_DIRECT_ITEM_TYPE' AND ml.lookup_code = 1 GROUP BY wed.wip_entity_id , wed.operation_seq_num , wed.organization_id , ml.meaning , wed.direct_item_sequence_id, wed.description , wed.uom , wed.order_type_lookup_code , pd.order_type_lookup_code , wed.unit_price , wed.amount , pd.amount , wed.rql_amount_ordered , pd.amount_delivered , wed.auto_request_material , wed.need_by_date , wed.purchasing_category_id , wed.required_quantity , wed.quantity , wed.quantity_cancelled , wed.suggested_vendor_name , wed.department_id , wed.attribute_category , wed.attribute1 , wed.attribute2 , wed.attribute3 , wed.attribute4 , wed.attribute5 , wed.attribute6 , wed.attribute7 , wed.attribute8 , wed.attribute9 , wed.attribute10 , wed.attribute11 , wed.attribute12 , wed.attribute13 , wed.attribute14 , wed.attribute15 UNION ALL SELECT wro.wip_entity_id , wro.operation_seq_num , wro.organization_id , wro.inventory_item_id , to_number(NULL) AS direct_item_sequence_id , wro.item_name , wro.description , wro.uom_code , wro.unit_price , wro.auto_request_material , wro.date_required , wro.category_id , wro.required_quantity quantity_required , wro.quantity , wro.quantity_cancelled , SUM(pd.quantity) , SUM(pd.quantity_delivered) , SUM(pd.quantity_ordered) , SUM(pd.quantity_cancelled) , wro.suggested_vendor_name , ml.meaning , 2 , wro.department_id , wro.effectivity_control , wro.eam_item_type , NVL(pd.order_type_lookup_code, wro.order_type_lookup_code) AS order_type_lookup_code, NVL(pd.amount_ordered, wro.amount) AS amount , pd.amount_delivered , wro.amount , pd.amount_ordered , wro.attribute_category , wro.attribute1 , wro.attribute2 , wro.attribute3 , wro.attribute4 , wro.attribute5 , wro.attribute6 , wro.attribute7 , wro.attribute8 , wro.attribute9 , wro.attribute10 , wro.attribute11 , wro.attribute12 , wro.attribute13 , wro.attribute14 , wro.attribute15 FROM (SELECT wro.wip_entity_id , wro.operation_seq_num , wro.organization_id , wro.inventory_item_id , wro.item_name , wro.description , wro.uom_code , wro.unit_price , wro.auto_request_material , wro.date_required , wro.category_id , wro.required_quantity , SUM(rql.quantity) quantity, SUM ( CASE WHEN (upper(rqh.authorization_status) IN('REJECTED','SYSTEM_SAVED','CANCELLED')) THEN rql.quantity WHEN (upper(rqh.authorization_status) = 'APPROVED' AND rql.cancel_flag = 'Y') THEN rql.quantity ELSE 0 END) quantity_cancelled , wro.vendor_id , wro.suggested_vendor_name , wro.department_id , wro.attribute_category , wro.effectivity_control , wro.eam_item_type , rql.order_type_lookup_code, rql.amount , wro.attribute1 , wro.attribute2 , wro.attribute3 , wro.attribute4 , wro.attribute5 , wro.attribute6 , wro.attribute7 , wro.attribute8 , wro.attribute9 , wro.attribute10 , wro.attribute11 , wro.attribute12 , wro.attribute13 , wro.attribute14 , wro.attribute15 FROM (SELECT wro.wip_entity_id , wro.operation_seq_num , wro.organization_id , wro.inventory_item_id , wro.required_quantity , wro.date_required , wro.unit_price , wro.auto_request_material , msi.concatenated_segments AS item_name, wro.vendor_id , wro.suggested_vendor_name , msi.description description , msi.primary_uom_code AS uom_code , mic.category_id AS category_id , wro.department_id , wro.attribute_category , msi.effectivity_control , msi.eam_item_type , wro.attribute1 , wro.attribute2 , wro.attribute3 , wro.attribute4 , wro.attribute5 , wro.attribute6 , wro.attribute7 , wro.attribute8 , wro.attribute9 , wro.attribute10 , wro.attribute11 , wro.attribute12 , wro.attribute13 , wro.attribute14 , wro.attribute15 FROM wip_requirement_operations wro, mtl_system_items_kfv msi , mtl_item_categories mic , mtl_default_category_sets mdcs WHERE msi.inventory_item_id = wro.inventory_item_id AND msi.organization_id = wro.organization_id AND NVL(msi.stock_enabled_flag, 'N') = 'N' AND wro.inventory_item_id = mic.inventory_item_id AND wro.organization_id = mic.organization_id AND mic.category_set_id = mdcs.category_set_id AND mdcs.functional_area_id = 2 ) wro , po_requisition_lines_all rql, po_requisition_headers_all rqh WHERE wro.wip_entity_id = rql.wip_entity_id(+) AND wro.organization_id = rql.destination_organization_id(+) AND wro.operation_seq_num = rql.wip_operation_seq_num(+) AND rql.requisition_header_id = rqh.requisition_header_id(+) AND rql.wip_resource_seq_num IS NULL AND wro.inventory_item_id = rql.item_id(+) AND nvl(rql.modified_by_agent_flag,'N') <> 'Y' GROUP BY wro.wip_entity_id , wro.operation_seq_num , wro.organization_id , wro.inventory_item_id , wro.item_name , wro.description , wro.uom_code , rql.order_type_lookup_code, wro.unit_price , rql.amount , wro.category_id , wro.auto_request_material , wro.date_required , wro.required_quantity , wro.vendor_id , wro.suggested_vendor_name , wro.department_id , wro.attribute_category , wro.effectivity_control , wro.eam_item_type , wro.attribute1 , wro.attribute2 , wro.attribute3 , wro.attribute4 , wro.attribute5 , wro.attribute6 , wro.attribute7 , wro.attribute8 , wro.attribute9 , wro.attribute10 , wro.attribute11 , wro.attribute12 , wro.attribute13 , wro.attribute14 , wro.attribute15 ) wro , (SELECT pol.quantity , SUM(pd1.quantity_delivered) quantity_delivered, SUM(pd1.quantity_ordered) quantity_ordered , SUM(pd1.quantity_cancelled) quantity_cancelled, pol.order_type_lookup_code , SUM(NVL(pd1.amount_ordered,0)) amount_ordered , SUM(NVL(pd1.amount_delivered,0)) amount_delivered , pd1.wip_entity_id , pd1.destination_organization_id , pd1.wip_operation_seq_num , pol.cancel_flag , pol.item_id , pd1.wip_resource_seq_num , pol.item_description , pol.po_line_id FROM po_lines_all pol, po_distributions_all pd1 WHERE pd1.po_line_id = pol.po_line_id AND( pd1.PO_RELEASE_ID IS NOT NULL OR pd1.DISTRIBUTION_TYPE = 'STANDARD') GROUP BY pd1.wip_entity_id , pd1.destination_organization_id, pd1.wip_operation_seq_num , pd1.wip_resource_seq_num , pol.cancel_flag , pol.item_id , pol.item_description , pol.order_type_lookup_code , pol.po_line_id , pol.quantity ) pd, mfg_lookups ml WHERE wro.wip_entity_id = pd.wip_entity_id(+) AND wro.organization_id = pd.destination_organization_id(+) AND wro.operation_seq_num = pd.wip_operation_seq_num(+) AND pd.wip_resource_seq_num IS NULL AND wro.inventory_item_id = pd.item_id(+) AND ml.lookup_type = 'EAM_DIRECT_ITEM_TYPE' AND ml.lookup_code = 2 GROUP BY wro.wip_entity_id, wro.operation_seq_num , wro.organization_id , ml.meaning , wro.inventory_item_id , wro.item_name , wro.description , wro.uom_code , pd.order_type_lookup_code , wro.order_type_lookup_code , wro.unit_price , pd.amount_ordered , wro.amount , wro.auto_request_material , wro.date_required , wro.category_id , wro.required_quantity , wro.quantity , wro.quantity_cancelled , wro.suggested_vendor_name , wro.department_id , wro.effectivity_control , wro.eam_item_type , pd.amount_delivered , wro.attribute_category , wro.attribute1 , wro.attribute2 , wro.attribute3 , wro.attribute4 , wro.attribute5 , wro.attribute6 , wro.attribute7 , wro.attribute8 , wro.attribute9 , wro.attribute10 , wro.attribute11 , wro.attribute12 , wro.attribute13 , wro.attribute14 , wro.attribute15

View Text - HTML Formatted

SELECT WED.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WED.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
, WED.ORGANIZATION_ID AS ORGANIZATION_ID
, TO_NUMBER(NULL) AS ITEM_ID
, WED.DIRECT_ITEM_SEQUENCE_ID AS DIRECT_ITEM_SEQUENCE_ID
, TO_CHAR(NULL) AS ITEM_NAME
, WED.DESCRIPTION AS ITEM_DESCRIPTION
, WED.UOM AS UOM_CODE
, WED.UNIT_PRICE AS UNIT_PRICE
, WED.AUTO_REQUEST_MATERIAL AS AUTO_REQUEST_MATERIAL
, WED.NEED_BY_DATE AS NEED_BY_DATE
, WED.PURCHASING_CATEGORY_ID AS CATEGORY_ID
, WED.REQUIRED_QUANTITY AS QUANTITY_REQUIRED
, WED.QUANTITY AS RQL_QUANTITY_ORDERED
, WED.QUANTITY_CANCELLED AS RQL_QUANTITY_CANCELLED
, SUM(PD.QUANTITY) AS QUANTITY_ORDERED
, SUM(PD.QUANTITY_DELIVERED) AS QUANTITY_RECEIVED
, SUM(PD.QUANTITY_ORDERED) AS PO_QUANTITY_ORDERED
, SUM(PD.QUANTITY_CANCELLED) AS PO_QUANTITY_CANCELLED
, WED.SUGGESTED_VENDOR_NAME AS SUPPLIER_NAME
, ML.MEANING AS DIRECT_ITEM_TYPE
, 1 AS DIRECT_ITEM_TYPE_ID
, WED.DEPARTMENT_ID AS DEPARTMENT_ID
, TO_NUMBER(NULL) AS EFFECTIVITY_CONTROL
, TO_NUMBER(NULL) AS EAM_ITEM_TYPE
, NVL(WED.ORDER_TYPE_LOOKUP_CODE
, PD.ORDER_TYPE_LOOKUP_CODE) AS ORDER_TYPE_LOOKUP_CODE
, NVL(WED.AMOUNT
, PD.AMOUNT) AS AMOUNT
, PD.AMOUNT_DELIVERED AS AMOUNT_DELIVERED
, WED.RQL_AMOUNT_ORDERED AS RQL_AMOUNT_ORDERED
, PD.AMOUNT AS PO_AMOUNT_ORDERED
, WED.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, WED.ATTRIBUTE1 ATTRIBUTE1
, WED.ATTRIBUTE2 ATTRIBUTE2
, WED.ATTRIBUTE3 ATTRIBUTE3
, WED.ATTRIBUTE4 ATTRIBUTE4
, WED.ATTRIBUTE5 ATTRIBUTE5
, WED.ATTRIBUTE6 ATTRIBUTE6
, WED.ATTRIBUTE7 ATTRIBUTE7
, WED.ATTRIBUTE8 ATTRIBUTE8
, WED.ATTRIBUTE9 ATTRIBUTE9
, WED.ATTRIBUTE10 ATTRIBUTE10
, WED.ATTRIBUTE11 ATTRIBUTE11
, WED.ATTRIBUTE12 ATTRIBUTE12
, WED.ATTRIBUTE13 ATTRIBUTE13
, WED.ATTRIBUTE14 ATTRIBUTE14
, WED.ATTRIBUTE15 ATTRIBUTE15
FROM (SELECT WED.WIP_ENTITY_ID
, WED.OPERATION_SEQ_NUM
, WED.ORGANIZATION_ID
, WED.DIRECT_ITEM_SEQUENCE_ID
, WED.DESCRIPTION
, WED.UOM
, WED.UNIT_PRICE
, WED.AUTO_REQUEST_MATERIAL
, WED.NEED_BY_DATE
, WED.PURCHASING_CATEGORY_ID
, WED.REQUIRED_QUANTITY
, SUM( RQL.QUANTITY) QUANTITY
, SUM( CASE WHEN (UPPER(RQH.AUTHORIZATION_STATUS)IN('REJECTED'
, 'SYSTEM_SAVED'
, 'CANCELLED')) THEN RQL.QUANTITY WHEN (UPPER(RQH .AUTHORIZATION_STATUS) = 'APPROVED'
AND RQL.CANCEL_FLAG = 'Y') THEN RQL.QUANTITY ELSE 0 END) QUANTITY_CANCELLED
, WED.SUGGESTED_VENDOR_ID
, WED.SUGGESTED_VENDOR_NAME
, WED.SUGGESTED_VENDOR_SITE_ID
, WED.SUGGESTED_VENDOR_CONTACT_ID
, WED.DEPARTMENT_ID
, NVL(RQL.ORDER_TYPE_LOOKUP_CODE
, WED.ORDER_TYPE_LOOKUP_CODE) AS ORDER_TYPE_LOOKUP_CODE
, NVL(RQL.AMOUNT
, WED.AMOUNT) AS AMOUNT
, RQL.AMOUNT AS RQL_AMOUNT_ORDERED
, WED.ATTRIBUTE_CATEGORY
, WED.ATTRIBUTE1
, WED.ATTRIBUTE2
, WED.ATTRIBUTE3
, WED.ATTRIBUTE4
, WED.ATTRIBUTE5
, WED.ATTRIBUTE6
, WED.ATTRIBUTE7
, WED.ATTRIBUTE8
, WED.ATTRIBUTE9
, WED.ATTRIBUTE10
, WED.ATTRIBUTE11
, WED.ATTRIBUTE12
, WED.ATTRIBUTE13
, WED.ATTRIBUTE14
, WED.ATTRIBUTE15
FROM WIP_EAM_DIRECT_ITEMS WED
, PO_REQUISITION_LINES_ALL RQL
, PO_REQUISITION_HEADERS_ALL RQH
WHERE WED.WIP_ENTITY_ID = RQL.WIP_ENTITY_ID(+)
AND WED.ORGANIZATION_ID = RQL.DESTINATION_ORGANIZATION_ID(+)
AND WED.OPERATION_SEQ_NUM = RQL.WIP_OPERATION_SEQ_NUM(+)
AND RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID(+)
AND RQL.ITEM_ID IS NULL
AND WED.DIRECT_ITEM_SEQUENCE_ID = RQL.WIP_RESOURCE_SEQ_NUM(+)
AND NVL(RQL.MODIFIED_BY_AGENT_FLAG
, 'N') <> 'Y' GROUP BY WED.WIP_ENTITY_ID
, WED.OPERATION_SEQ_NUM
, WED.ORGANIZATION_ID
, WED.DIRECT_ITEM_SEQUENCE_ID
, WED.DESCRIPTION
, WED.UOM
, RQL.ORDER_TYPE_LOOKUP_CODE
, WED.ORDER_TYPE_LOOKUP_CODE
, WED.UNIT_PRICE
, RQL.AMOUNT
, WED.AMOUNT
, WED.AUTO_REQUEST_MATERIAL
, WED.NEED_BY_DATE
, WED.PURCHASING_CATEGORY_ID
, WED.REQUIRED_QUANTITY
, WED.SUGGESTED_VENDOR_ID
, WED.SUGGESTED_VENDOR_NAME
, WED.SUGGESTED_VENDOR_SITE_ID
, WED.SUGGESTED_VENDOR_CONTACT_ID
, WED.DEPARTMENT_ID
, WED.ATTRIBUTE_CATEGORY
, WED.ATTRIBUTE1
, WED.ATTRIBUTE2
, WED.ATTRIBUTE3
, WED.ATTRIBUTE4
, WED.ATTRIBUTE5
, WED.ATTRIBUTE6
, WED.ATTRIBUTE7
, WED.ATTRIBUTE8
, WED.ATTRIBUTE9
, WED.ATTRIBUTE10
, WED.ATTRIBUTE11
, WED.ATTRIBUTE12
, WED.ATTRIBUTE13
, WED.ATTRIBUTE14
, WED.ATTRIBUTE15 ) WED
, (SELECT POL.QUANTITY
, SUM(PD1.QUANTITY_DELIVERED) QUANTITY_DELIVERED
, SUM(PD1.QUANTITY_ORDERED) QUANTITY_ORDERED
, SUM(PD1.QUANTITY_CANCELLED) QUANTITY_CANCELLED
, POL.ORDER_TYPE_LOOKUP_CODE
, SUM(NVL(PD1.AMOUNT_ORDERED
, 0)) AMOUNT
, SUM(NVL(PD1.AMOUNT_DELIVERED
, 0)) AMOUNT_DELIVERED
, PD1.WIP_ENTITY_ID
, PD1.DESTINATION_ORGANIZATION_ID
, PD1.WIP_OPERATION_SEQ_NUM
, POL.CANCEL_FLAG
, POL.ITEM_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.ITEM_DESCRIPTION
, POL.PO_LINE_ID
FROM PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL PD1
WHERE PD1.PO_LINE_ID = POL.PO_LINE_ID AND( PD1.PO_RELEASE_ID IS NOT NULL OR PD1.DISTRIBUTION_TYPE = 'STANDARD') GROUP BY PD1.WIP_ENTITY_ID
, PD1.DESTINATION_ORGANIZATION_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.CANCEL_FLAG
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.ORDER_TYPE_LOOKUP_CODE
, POL.PO_LINE_ID
, POL.QUANTITY ) PD
, MFG_LOOKUPS ML
WHERE WED.WIP_ENTITY_ID = PD.WIP_ENTITY_ID(+)
AND WED.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID(+)
AND WED.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM(+)
AND PD.ITEM_ID IS NULL
AND WED.DIRECT_ITEM_SEQUENCE_ID = PD.WIP_RESOURCE_SEQ_NUM (+)
AND ML.LOOKUP_TYPE = 'EAM_DIRECT_ITEM_TYPE'
AND ML.LOOKUP_CODE = 1 GROUP BY WED.WIP_ENTITY_ID
, WED.OPERATION_SEQ_NUM
, WED.ORGANIZATION_ID
, ML.MEANING
, WED.DIRECT_ITEM_SEQUENCE_ID
, WED.DESCRIPTION
, WED.UOM
, WED.ORDER_TYPE_LOOKUP_CODE
, PD.ORDER_TYPE_LOOKUP_CODE
, WED.UNIT_PRICE
, WED.AMOUNT
, PD.AMOUNT
, WED.RQL_AMOUNT_ORDERED
, PD.AMOUNT_DELIVERED
, WED.AUTO_REQUEST_MATERIAL
, WED.NEED_BY_DATE
, WED.PURCHASING_CATEGORY_ID
, WED.REQUIRED_QUANTITY
, WED.QUANTITY
, WED.QUANTITY_CANCELLED
, WED.SUGGESTED_VENDOR_NAME
, WED.DEPARTMENT_ID
, WED.ATTRIBUTE_CATEGORY
, WED.ATTRIBUTE1
, WED.ATTRIBUTE2
, WED.ATTRIBUTE3
, WED.ATTRIBUTE4
, WED.ATTRIBUTE5
, WED.ATTRIBUTE6
, WED.ATTRIBUTE7
, WED.ATTRIBUTE8
, WED.ATTRIBUTE9
, WED.ATTRIBUTE10
, WED.ATTRIBUTE11
, WED.ATTRIBUTE12
, WED.ATTRIBUTE13
, WED.ATTRIBUTE14
, WED.ATTRIBUTE15 UNION ALL SELECT WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, WRO.INVENTORY_ITEM_ID
, TO_NUMBER(NULL) AS DIRECT_ITEM_SEQUENCE_ID
, WRO.ITEM_NAME
, WRO.DESCRIPTION
, WRO.UOM_CODE
, WRO.UNIT_PRICE
, WRO.AUTO_REQUEST_MATERIAL
, WRO.DATE_REQUIRED
, WRO.CATEGORY_ID
, WRO.REQUIRED_QUANTITY QUANTITY_REQUIRED
, WRO.QUANTITY
, WRO.QUANTITY_CANCELLED
, SUM(PD.QUANTITY)
, SUM(PD.QUANTITY_DELIVERED)
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, WRO.SUGGESTED_VENDOR_NAME
, ML.MEANING
, 2
, WRO.DEPARTMENT_ID
, WRO.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE
, NVL(PD.ORDER_TYPE_LOOKUP_CODE
, WRO.ORDER_TYPE_LOOKUP_CODE) AS ORDER_TYPE_LOOKUP_CODE
, NVL(PD.AMOUNT_ORDERED
, WRO.AMOUNT) AS AMOUNT
, PD.AMOUNT_DELIVERED
, WRO.AMOUNT
, PD.AMOUNT_ORDERED
, WRO.ATTRIBUTE_CATEGORY
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15
FROM (SELECT WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, WRO.INVENTORY_ITEM_ID
, WRO.ITEM_NAME
, WRO.DESCRIPTION
, WRO.UOM_CODE
, WRO.UNIT_PRICE
, WRO.AUTO_REQUEST_MATERIAL
, WRO.DATE_REQUIRED
, WRO.CATEGORY_ID
, WRO.REQUIRED_QUANTITY
, SUM(RQL.QUANTITY) QUANTITY
, SUM ( CASE WHEN (UPPER(RQH.AUTHORIZATION_STATUS) IN('REJECTED'
, 'SYSTEM_SAVED'
, 'CANCELLED')) THEN RQL.QUANTITY WHEN (UPPER(RQH.AUTHORIZATION_STATUS) = 'APPROVED'
AND RQL.CANCEL_FLAG = 'Y') THEN RQL.QUANTITY ELSE 0 END) QUANTITY_CANCELLED
, WRO.VENDOR_ID
, WRO.SUGGESTED_VENDOR_NAME
, WRO.DEPARTMENT_ID
, WRO.ATTRIBUTE_CATEGORY
, WRO.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.AMOUNT
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15
FROM (SELECT WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, WRO.INVENTORY_ITEM_ID
, WRO.REQUIRED_QUANTITY
, WRO.DATE_REQUIRED
, WRO.UNIT_PRICE
, WRO.AUTO_REQUEST_MATERIAL
, MSI.CONCATENATED_SEGMENTS AS ITEM_NAME
, WRO.VENDOR_ID
, WRO.SUGGESTED_VENDOR_NAME
, MSI.DESCRIPTION DESCRIPTION
, MSI.PRIMARY_UOM_CODE AS UOM_CODE
, MIC.CATEGORY_ID AS CATEGORY_ID
, WRO.DEPARTMENT_ID
, WRO.ATTRIBUTE_CATEGORY
, MSI.EFFECTIVITY_CONTROL
, MSI.EAM_ITEM_TYPE
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15
FROM WIP_REQUIREMENT_OPERATIONS WRO
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_CATEGORY_SETS MDCS
WHERE MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND NVL(MSI.STOCK_ENABLED_FLAG
, 'N') = 'N'
AND WRO.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
AND MDCS.FUNCTIONAL_AREA_ID = 2 ) WRO
, PO_REQUISITION_LINES_ALL RQL
, PO_REQUISITION_HEADERS_ALL RQH
WHERE WRO.WIP_ENTITY_ID = RQL.WIP_ENTITY_ID(+)
AND WRO.ORGANIZATION_ID = RQL.DESTINATION_ORGANIZATION_ID(+)
AND WRO.OPERATION_SEQ_NUM = RQL.WIP_OPERATION_SEQ_NUM(+)
AND RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID(+)
AND RQL.WIP_RESOURCE_SEQ_NUM IS NULL
AND WRO.INVENTORY_ITEM_ID = RQL.ITEM_ID(+)
AND NVL(RQL.MODIFIED_BY_AGENT_FLAG
, 'N') <> 'Y' GROUP BY WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, WRO.INVENTORY_ITEM_ID
, WRO.ITEM_NAME
, WRO.DESCRIPTION
, WRO.UOM_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE
, WRO.UNIT_PRICE
, RQL.AMOUNT
, WRO.CATEGORY_ID
, WRO.AUTO_REQUEST_MATERIAL
, WRO.DATE_REQUIRED
, WRO.REQUIRED_QUANTITY
, WRO.VENDOR_ID
, WRO.SUGGESTED_VENDOR_NAME
, WRO.DEPARTMENT_ID
, WRO.ATTRIBUTE_CATEGORY
, WRO.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15 ) WRO
, (SELECT POL.QUANTITY
, SUM(PD1.QUANTITY_DELIVERED) QUANTITY_DELIVERED
, SUM(PD1.QUANTITY_ORDERED) QUANTITY_ORDERED
, SUM(PD1.QUANTITY_CANCELLED) QUANTITY_CANCELLED
, POL.ORDER_TYPE_LOOKUP_CODE
, SUM(NVL(PD1.AMOUNT_ORDERED
, 0)) AMOUNT_ORDERED
, SUM(NVL(PD1.AMOUNT_DELIVERED
, 0)) AMOUNT_DELIVERED
, PD1.WIP_ENTITY_ID
, PD1.DESTINATION_ORGANIZATION_ID
, PD1.WIP_OPERATION_SEQ_NUM
, POL.CANCEL_FLAG
, POL.ITEM_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.ITEM_DESCRIPTION
, POL.PO_LINE_ID
FROM PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL PD1
WHERE PD1.PO_LINE_ID = POL.PO_LINE_ID AND( PD1.PO_RELEASE_ID IS NOT NULL OR PD1.DISTRIBUTION_TYPE = 'STANDARD') GROUP BY PD1.WIP_ENTITY_ID
, PD1.DESTINATION_ORGANIZATION_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.CANCEL_FLAG
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.ORDER_TYPE_LOOKUP_CODE
, POL.PO_LINE_ID
, POL.QUANTITY ) PD
, MFG_LOOKUPS ML
WHERE WRO.WIP_ENTITY_ID = PD.WIP_ENTITY_ID(+)
AND WRO.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID(+)
AND WRO.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM(+)
AND PD.WIP_RESOURCE_SEQ_NUM IS NULL
AND WRO.INVENTORY_ITEM_ID = PD.ITEM_ID(+)
AND ML.LOOKUP_TYPE = 'EAM_DIRECT_ITEM_TYPE'
AND ML.LOOKUP_CODE = 2 GROUP BY WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, ML.MEANING
, WRO.INVENTORY_ITEM_ID
, WRO.ITEM_NAME
, WRO.DESCRIPTION
, WRO.UOM_CODE
, PD.ORDER_TYPE_LOOKUP_CODE
, WRO.ORDER_TYPE_LOOKUP_CODE
, WRO.UNIT_PRICE
, PD.AMOUNT_ORDERED
, WRO.AMOUNT
, WRO.AUTO_REQUEST_MATERIAL
, WRO.DATE_REQUIRED
, WRO.CATEGORY_ID
, WRO.REQUIRED_QUANTITY
, WRO.QUANTITY
, WRO.QUANTITY_CANCELLED
, WRO.SUGGESTED_VENDOR_NAME
, WRO.DEPARTMENT_ID
, WRO.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE
, PD.AMOUNT_DELIVERED
, WRO.ATTRIBUTE_CATEGORY
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15