VIEW: APPS.EAM_WORK_ORDER_DIRECT_ITEMS_V

Source

View Text - Preformatted

SELECT wed.wip_entity_id AS wip_entity_id, wed.operation_seq_num, wed.organization_id, to_number(null) AS Item_id, wed.direct_item_sequence_id, to_char(null) item_name, wed.description, wed.uom AS UOM_CODE, wed.unit_price, wed.auto_request_material, wed.need_by_date, wed.purchasing_category_id, wed.required_quantity quantity_required, wed.quantity, pd.quantity, sum(pd.quantity_delivered), sum(pd.quantity_ordered), sum(pd.quantity_cancelled), wed.suggested_vendor_id, nvl(wed.suggested_vendor_name, pov.vendor_name), ml.meaning, 1, nvl(wed.suggested_vendor_site, povs.VENDOR_SITE_CODE), wed.suggested_vendor_site_id, nvl(wed.suggested_vendor_contact, DECODE(povc.first_name, NULL, povc.last_name, decode( povc.last_name, NULL, povc.first_name, povc.last_name || ', ' || povc.first_name) ) ), wed.suggested_vendor_contact_id, nvl(wed.suggested_vendor_phone, povc.PHONE), wed.suggested_vendor_item_num, 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 , to_number(null) AS effectivity_control, to_number(null) AS eam_item_type, nvl(wed.order_type_lookup_code, pd.order_type_lookup_code), nvl(wed.amount, pd.amount), pd.amount_delivered, wed.rql_amount_ordered, pd.amount 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, wed.suggested_vendor_id, wed.suggested_vendor_name, wed.suggested_vendor_site, wed.suggested_vendor_site_id, wed.suggested_vendor_contact, wed.suggested_vendor_contact_id, wed.suggested_vendor_phone, wed.suggested_vendor_item_num, 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, 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 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 upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED') AND rql.item_id is null AND ( wed.direct_item_sequence_id = rql.wip_resource_seq_num OR rql.wip_resource_seq_num is null ) AND wed.description = rql.item_description(+) 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, wed.suggested_vendor_site_id, wed.suggested_vendor_contact, wed.suggested_vendor_contact_id, wed.suggested_vendor_phone, wed.suggested_vendor_item_num, 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 pd1.wip_entity_id, pd1.wip_operation_seq_num, pd1.destination_organization_id, pol.item_description, pd1.wip_resource_seq_num, pol.quantity, pd1.quantity_delivered, pd1.quantity_ordered, pd1.quantity_cancelled, pol.item_id, pol.cancel_flag, pol.order_type_lookup_code, pd1.amount_ordered AS amount, pd1.amount_delivered AS amount_delivered FROM po_lines_all pol, po_distributions_all pd1 WHERE pol.po_line_id = pd1.po_line_id ) pd, po_vendors pov, PO_VENDOR_SITES_ALL povs, PO_VENDOR_CONTACTS povc, 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 upper(nvl(pd.cancel_flag,'N')) <> 'Y' AND pd.item_id is null AND ( wed.direct_item_sequence_id = pd.wip_resource_seq_num OR pd.wip_resource_seq_num is null ) AND wed.description = pd.item_description(+) AND wed.suggested_vendor_id = pov.vendor_id (+) AND wed.suggested_vendor_site_id = povs.VENDOR_SITE_ID (+) AND wed.suggested_vendor_contact_id = povc.VENDOR_CONTACT_ID (+) 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, pd.quantity, wed.suggested_vendor_id, wed.suggested_vendor_name, pov.vendor_name, wed.suggested_vendor_site, povs.VENDOR_SITE_CODE, wed.suggested_vendor_site_id, wed.suggested_vendor_contact, povc.first_name, povc.last_name, wed.suggested_vendor_contact_id, wed.suggested_vendor_phone, povc.PHONE, wed.suggested_vendor_item_num, 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, pd.quantity, sum(pd.quantity_delivered), sum(pd.quantity_ordered), sum(pd.quantity_cancelled), wro.vendor_id, nvl(wro.suggested_vendor_name, pov.vendor_name), ml.meaning, 2, to_char(null), to_number(null), to_char(null), to_number(null), to_char(null), to_char(null), wro.department_id, 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 , 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, wro.amount) AS amount, pd.amount_delivered, wro.amount, pd.amount 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, wro.vendor_id, wro.suggested_vendor_name, wro.department_id, 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, wro.effectivity_control, wro.eam_item_type, rql.order_type_lookup_code, rql.amount 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, 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, msi.effectivity_control, msi.eam_item_type 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 upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED') AND rql.wip_resource_seq_num is null AND wro.inventory_item_id = rql.item_id (+) 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.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.effectivity_control, wro.eam_item_type ) wro, ( SELECT pd1.wip_entity_id, pd1.wip_operation_seq_num, pd1.destination_organization_id, pol.item_description, pd1.wip_resource_seq_num, pol.quantity, pd1.quantity_delivered, pd1.quantity_ordered, pd1.quantity_cancelled, pol.item_id, pol.cancel_flag, pol.order_type_lookup_code, pd1.amount_ordered AS amount, pd1.amount_delivered FROM po_lines_all pol, po_distributions_all pd1 WHERE pol.po_line_id = pd1.po_line_id ) pd, po_vendors pov, mfg_lookups ml WHERE wro.wip_entity_id = pd.wip_entity_id(+) AND wro.organization_id = pd.destination_organization_id(+) AND wro.vendor_id = pov.vendor_id (+) AND wro.operation_seq_num = pd.wip_operation_seq_num(+) AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y' 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, wro.amount, wro.auto_request_material, wro.date_required, wro.category_id, wro.required_quantity, wro.quantity, pd.quantity, wro.vendor_id, wro.suggested_vendor_name, pov.vendor_name, wro.department_id, 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, wro.effectivity_control, wro.eam_item_type, pd.amount_delivered

View Text - HTML Formatted

SELECT WED.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WED.OPERATION_SEQ_NUM
, WED.ORGANIZATION_ID
, TO_NUMBER(NULL) AS ITEM_ID
, WED.DIRECT_ITEM_SEQUENCE_ID
, TO_CHAR(NULL) ITEM_NAME
, WED.DESCRIPTION
, WED.UOM AS UOM_CODE
, WED.UNIT_PRICE
, WED.AUTO_REQUEST_MATERIAL
, WED.NEED_BY_DATE
, WED.PURCHASING_CATEGORY_ID
, WED.REQUIRED_QUANTITY QUANTITY_REQUIRED
, WED.QUANTITY
, PD.QUANTITY
, SUM(PD.QUANTITY_DELIVERED)
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, WED.SUGGESTED_VENDOR_ID
, NVL(WED.SUGGESTED_VENDOR_NAME
, POV.VENDOR_NAME)
, ML.MEANING
, 1
, NVL(WED.SUGGESTED_VENDOR_SITE
, POVS.VENDOR_SITE_CODE)
, WED.SUGGESTED_VENDOR_SITE_ID
, NVL(WED.SUGGESTED_VENDOR_CONTACT
, DECODE(POVC.FIRST_NAME
, NULL
, POVC.LAST_NAME
, DECODE( POVC.LAST_NAME
, NULL
, POVC.FIRST_NAME
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME) ) )
, WED.SUGGESTED_VENDOR_CONTACT_ID
, NVL(WED.SUGGESTED_VENDOR_PHONE
, POVC.PHONE)
, WED.SUGGESTED_VENDOR_ITEM_NUM
, 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
, TO_NUMBER(NULL) AS EFFECTIVITY_CONTROL
, TO_NUMBER(NULL) AS EAM_ITEM_TYPE
, NVL(WED.ORDER_TYPE_LOOKUP_CODE
, PD.ORDER_TYPE_LOOKUP_CODE)
, NVL(WED.AMOUNT
, PD.AMOUNT)
, PD.AMOUNT_DELIVERED
, WED.RQL_AMOUNT_ORDERED
, PD.AMOUNT
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
, WED.SUGGESTED_VENDOR_ID
, WED.SUGGESTED_VENDOR_NAME
, WED.SUGGESTED_VENDOR_SITE
, WED.SUGGESTED_VENDOR_SITE_ID
, WED.SUGGESTED_VENDOR_CONTACT
, WED.SUGGESTED_VENDOR_CONTACT_ID
, WED.SUGGESTED_VENDOR_PHONE
, WED.SUGGESTED_VENDOR_ITEM_NUM
, 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
, 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
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 UPPER(NVL(RQH.AUTHORIZATION_STATUS
, 'APPROVED') ) NOT IN ('CANCELLED'
, 'REJECTED'
, 'SYSTEM_SAVED')
AND RQL.ITEM_ID IS NULL
AND ( WED.DIRECT_ITEM_SEQUENCE_ID = RQL.WIP_RESOURCE_SEQ_NUM OR RQL.WIP_RESOURCE_SEQ_NUM IS NULL )
AND WED.DESCRIPTION = RQL.ITEM_DESCRIPTION(+) 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
, WED.SUGGESTED_VENDOR_SITE_ID
, WED.SUGGESTED_VENDOR_CONTACT
, WED.SUGGESTED_VENDOR_CONTACT_ID
, WED.SUGGESTED_VENDOR_PHONE
, WED.SUGGESTED_VENDOR_ITEM_NUM
, 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 PD1.WIP_ENTITY_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.DESTINATION_ORGANIZATION_ID
, POL.ITEM_DESCRIPTION
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.QUANTITY
, PD1.QUANTITY_DELIVERED
, PD1.QUANTITY_ORDERED
, PD1.QUANTITY_CANCELLED
, POL.ITEM_ID
, POL.CANCEL_FLAG
, POL.ORDER_TYPE_LOOKUP_CODE
, PD1.AMOUNT_ORDERED AS AMOUNT
, PD1.AMOUNT_DELIVERED AS AMOUNT_DELIVERED
FROM PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL PD1
WHERE POL.PO_LINE_ID = PD1.PO_LINE_ID ) PD
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, 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 UPPER(NVL(PD.CANCEL_FLAG
, 'N')) <> 'Y'
AND PD.ITEM_ID IS NULL
AND ( WED.DIRECT_ITEM_SEQUENCE_ID = PD.WIP_RESOURCE_SEQ_NUM OR PD.WIP_RESOURCE_SEQ_NUM IS NULL )
AND WED.DESCRIPTION = PD.ITEM_DESCRIPTION(+)
AND WED.SUGGESTED_VENDOR_ID = POV.VENDOR_ID (+)
AND WED.SUGGESTED_VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND WED.SUGGESTED_VENDOR_CONTACT_ID = POVC.VENDOR_CONTACT_ID (+)
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
, PD.QUANTITY
, WED.SUGGESTED_VENDOR_ID
, WED.SUGGESTED_VENDOR_NAME
, POV.VENDOR_NAME
, WED.SUGGESTED_VENDOR_SITE
, POVS.VENDOR_SITE_CODE
, WED.SUGGESTED_VENDOR_SITE_ID
, WED.SUGGESTED_VENDOR_CONTACT
, POVC.FIRST_NAME
, POVC.LAST_NAME
, WED.SUGGESTED_VENDOR_CONTACT_ID
, WED.SUGGESTED_VENDOR_PHONE
, POVC.PHONE
, WED.SUGGESTED_VENDOR_ITEM_NUM
, 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
, PD.QUANTITY
, SUM(PD.QUANTITY_DELIVERED)
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, WRO.VENDOR_ID
, NVL(WRO.SUGGESTED_VENDOR_NAME
, POV.VENDOR_NAME)
, ML.MEANING
, 2
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, WRO.DEPARTMENT_ID
, 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
, 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
, WRO.AMOUNT) AS AMOUNT
, PD.AMOUNT_DELIVERED
, WRO.AMOUNT
, PD.AMOUNT
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
, WRO.VENDOR_ID
, WRO.SUGGESTED_VENDOR_NAME
, WRO.DEPARTMENT_ID
, 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
, WRO.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.AMOUNT
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
, 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
, MSI.EFFECTIVITY_CONTROL
, MSI.EAM_ITEM_TYPE
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 UPPER(NVL(RQH.AUTHORIZATION_STATUS
, 'APPROVED') ) NOT IN ('CANCELLED'
, 'REJECTED'
, 'SYSTEM_SAVED')
AND RQL.WIP_RESOURCE_SEQ_NUM IS NULL
AND WRO.INVENTORY_ITEM_ID = RQL.ITEM_ID (+) 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.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.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE ) WRO
, ( SELECT PD1.WIP_ENTITY_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.DESTINATION_ORGANIZATION_ID
, POL.ITEM_DESCRIPTION
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.QUANTITY
, PD1.QUANTITY_DELIVERED
, PD1.QUANTITY_ORDERED
, PD1.QUANTITY_CANCELLED
, POL.ITEM_ID
, POL.CANCEL_FLAG
, POL.ORDER_TYPE_LOOKUP_CODE
, PD1.AMOUNT_ORDERED AS AMOUNT
, PD1.AMOUNT_DELIVERED
FROM PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL PD1
WHERE POL.PO_LINE_ID = PD1.PO_LINE_ID ) PD
, PO_VENDORS POV
, MFG_LOOKUPS ML
WHERE WRO.WIP_ENTITY_ID = PD.WIP_ENTITY_ID(+)
AND WRO.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID(+)
AND WRO.VENDOR_ID = POV.VENDOR_ID (+)
AND WRO.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM(+)
AND UPPER(NVL(PD.CANCEL_FLAG
, 'N')) <> 'Y'
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
, WRO.AMOUNT
, WRO.AUTO_REQUEST_MATERIAL
, WRO.DATE_REQUIRED
, WRO.CATEGORY_ID
, WRO.REQUIRED_QUANTITY
, WRO.QUANTITY
, PD.QUANTITY
, WRO.VENDOR_ID
, WRO.SUGGESTED_VENDOR_NAME
, POV.VENDOR_NAME
, WRO.DEPARTMENT_ID
, 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
, WRO.EFFECTIVITY_CONTROL
, WRO.EAM_ITEM_TYPE
, PD.AMOUNT_DELIVERED