VIEW: APPS.EAM_WORK_ORDER_REQ_PO_V

Source

View Text - Preformatted

SELECT pd.wip_entity_id, pd.wip_operation_seq_num, pd.destination_organization_id, rqh.segment1 req_number, poh.segment1 po_number, nvl (pd.item_description,rql.item_description), nvl(uom.uom_code,rqluom.uom_code), nvl(pd.unit_price,rql.unit_price), nvl(poh.currency_code,rql.currency_code), rql.quantity req_line_qty, ( 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) req_line_qty_cancelled , pd.quantity po_line_qty, sum(pd.quantity_delivered ), sum(pd.quantity_ordered), sum(pd.quantity_cancelled), pd.item_id, pd.wip_resource_seq_num, decode(poh.vendor_id, null, rql.vendor_id, poh.vendor_id ), nvl(pov.vendor_name, rql.suggested_vendor_name), nvl(poh.vendor_site_id, rql.vendor_site_id), nvl(povs.VENDOR_SITE_CODE, rql.suggested_vendor_location), nvl(poh.vendor_contact_id, rql.vendor_contact_id), nvl(DECODE(povc.first_name, NULL, povc.last_name, decode( povc.last_name, NULL, povc.first_name, povc.last_name || ', ' || povc.first_name) ), rql.suggested_vendor_contact ), nvl(povc.phone, rql.suggested_vendor_phone), nvl(pd.vendor_product_num, rql.suggested_vendor_product_code), rql.requisition_line_id, pd.po_line_id, rqh.requisition_header_id, poh.po_header_id, pd.po_release_id, nvl(pd.category_id,rql.category_id), nvl(polloc.need_by_date, rql.need_by_date) , nvl(pd.order_type_lookup_code, rql.order_type_lookup_code), DECODE(pd.order_type_lookup_code,NULL, sum(rql.amount),sum(pd.amount_ordered)), sum(pd.amount_delivered), sum(rql.amount), sum(pd.amount_ordered) FROM po_line_types plt, po_headers_all poh, po_line_locations_all polloc, po_requisition_headers_all rqh, po_requisition_lines_all rql, mtl_units_of_measure uom, mtl_units_of_measure rqluom, ( SELECT pd1.wip_entity_id, pd1.wip_operation_seq_num, pd1.destination_organization_id, pol.item_description, pol.unit_price, pol.quantity, pd1.quantity_delivered, pd1.quantity_ordered, pd1.quantity_cancelled, pol.closed_code, pol.po_line_id, pol.po_header_id, pd1.req_distribution_id, pd1.line_location_id, pol.line_type_id, pd1.destination_type_code, pol.unit_meas_lookup_code, pol.cancel_flag, pol.item_id, pol.category_id, pol.vendor_product_num, pd1.po_release_id, pd1.wip_resource_seq_num, pol.order_type_lookup_code, pd1.amount_ordered, 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, po_vendor_sites_all povs, po_vendor_contacts povc WHERE poh.po_header_id = pd.po_header_id AND rql.requisition_header_id = rqh.requisition_header_id(+) AND polloc.po_line_id = pd.po_line_id AND polloc.line_location_id = pd.line_location_id AND polloc.line_location_id = rql.line_location_id (+) AND pd.unit_meas_lookup_code = uom.unit_of_measure (+) AND rql.unit_meas_lookup_code = rqluom.unit_of_measure (+) AND pd.line_type_id = plt.line_type_id AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N' AND pd.destination_type_code = 'SHOP FLOOR' AND upper(nvl(pd.cancel_flag,'N')) <> 'Y' AND poh.vendor_id = pov. vendor_id (+) AND poh.vendor_site_id = povs.vendor_site_id (+) AND poh.vendor_site_id = povc.vendor_site_id (+) AND poh.vendor_contact_id = povc.vendor_contact_id (+) AND pd.wip_entity_id is not null AND nvl(rql.modified_by_agent_flag,'N') <> 'Y' GROUP BY pd.wip_entity_id, pd.wip_operation_seq_num, pd.destination_organization_id, rqh.segment1 , rqh.authorization_status, poh.segment1 , pd.item_description, rql.item_description, uom.uom_code, rqluom.uom_code, pd.category_id, rql.category_id, pd.order_type_lookup_code, rql.order_type_lookup_code, rql.unit_price, pd.unit_price, polloc.need_by_date, rql.need_by_date, rql.currency_code, poh.currency_code, rql.quantity , rql.cancel_flag, pd.quantity, pd.item_id, pd.wip_resource_seq_num, rql.vendor_id, poh.vendor_id, pov.vendor_name, rql.suggested_vendor_name, poh.vendor_site_id, rql.vendor_site_id, povs.vendor_site_code, rql.suggested_vendor_location, poh.vendor_contact_id, rql.vendor_contact_id, povc.first_name, povc.last_name, rql.suggested_vendor_contact, povc.phone, rql.suggested_vendor_phone, pd.vendor_product_num, rql.suggested_vendor_product_code, rql.requisition_line_id, pd.po_line_id, rqh.requisition_header_id, poh .po_header_id, pd.po_release_id UNION SELECT rql.wip_entity_id, rql.wip_operation_seq_num, rql.destination_organization_id, rqh.segment1 req_number, poh.segment1 po_number, nvl(pd.item_description,rql.item_description), nvl(uom.uom_code,rqluom.uom_code), nvl(pd.unit_price,rql.unit_price), nvl( poh.currency_code,rql.currency_code), rql.quantity req_line_qty, ( 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) req_line_qty_cancelled, pd.quantity po_line_qty, sum(pd.quantity_delivered), sum(pd.quantity_ordered), sum(pd.quantity_cancelled), rql.item_id, rql.wip_resource_seq_num, decode(poh.vendor_id, null, rql.vendor_id, poh.vendor_id), nvl(pov.vendor_name, rql.suggested_vendor_name), nvl(poh.vendor_site_id, rql.vendor_site_id), nvl(povs.VENDOR_SITE_CODE, rql.suggested_vendor_location), nvl(poh.vendor_contact_id, rql.vendor_contact_id), nvl(DECODE(povc.first_name, NULL, povc.last_name, decode( povc.last_name, NULL, povc.first_name, povc.last_name || ', ' || povc.first_name) ), rql.suggested_vendor_contact ), nvl(povc.phone, rql.suggested_vendor_phone), nvl(pd.vendor_product_num, rql.suggested_vendor_product_code), rql.requisition_line_id, pd.po_line_id, rqh.requisition_header_id, poh.po_header_id, pd.po_release_id, nvl(pd.category_id,rql.category_id), nvl(polloc.need_by_date, rql.need_by_date), nvl(pd.order_type_lookup_code, rql.order_type_lookup_code), DECODE(pd.order_type_lookup_code, NULL, sum(rql.amount),sum(pd.amount_ordered)), sum(pd.amount_delivered), sum(rql.amount), sum(pd.amount_ordered) FROM po_line_types plt, po_headers_all poh, po_line_locations_all polloc, po_requisition_headers_all rqh, po_requisition_lines_all rql, mtl_units_of_measure uom, mtl_units_of_measure rqluom, ( SELECT pd1.wip_entity_id, pd1.wip_operation_seq_num, pd1.destination_organization_id, pol.item_description, pol.unit_price, pol.quantity, pd1.quantity_delivered, pd1.quantity_ordered, pd1.quantity_cancelled, pol.closed_code, pol.po_line_id, pol.po_header_id, pd1.req_distribution_id, pd1.line_location_id, pol.line_type_id, pd1.destination_type_code, pol.unit_meas_lookup_code, pol.cancel_flag, pol.item_id, pol .category_id, pol.vendor_product_num, pd1.po_release_id, pd1.wip_resource_seq_num, pol.order_type_lookup_code, pd1.amount_ordered, 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, po_vendor_sites_all povs, po_vendor_contacts povc WHERE pd.po_header_id = poh.po_header_id(+) AND rql.requisition_header_id = rqh.requisition_header_id AND rql.line_location_id = polloc.line_location_id (+) AND polloc.po_line_id = pd.po_line_id (+) AND polloc.line_location_id = pd.line_location_id (+) AND pd.unit_meas_lookup_code = uom.unit_of_measure (+) AND rql.unit_meas_lookup_code = rqluom.unit_of_measure(+) AND rql.line_type_id = plt.line_type_id AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N' AND rql.destination_type_code = 'SHOP FLOOR' AND upper(nvl(rqh.cancel_flag, 'N')) <> 'Y' AND poh.vendor_id = pov.vendor_id (+) AND poh.vendor_site_id = povs.vendor_site_id (+) AND poh.vendor_contact_id = povc.vendor_contact_id (+) AND rql.wip_entity_id is not null AND nvl(rql.modified_by_agent_flag,'N') <> 'Y' GROUP BY rql.wip_entity_id, rql.wip_operation_seq_num, rql.destination_organization_id, rqh.segment1 , rqh.authorization_status, poh.segment1, pd.item_description, rql.item_description, rqluom.uom_code, uom.uom_code, pd.category_id, rql.category_id, pd.order_type_lookup_code, rql.order_type_lookup_code, rql.unit_price, pd.unit_price, polloc.need_by_date, rql.need_by_date, rql.currency_code, poh.currency_code, rql.quantity , rql.cancel_flag, pd.quantity , rql.item_id, rql.wip_resource_seq_num, rql.vendor_id, poh.vendor_id, pov.vendor_name, rql.suggested_vendor_name, poh.vendor_site_id, rql.vendor_site_id, povs.vendor_site_code, rql.suggested_vendor_location, poh.vendor_contact_id, rql.vendor_contact_id, povc.first_name, povc.last_name, rql.suggested_vendor_contact, povc.phone, rql.suggested_vendor_phone, pd.vendor_product_num, rql.suggested_vendor_product_code, rql.requisition_line_id, pd.po_line_id, rqh.requisition_header_id, poh.po_header_id, pd.po_release_id

View Text - HTML Formatted

SELECT PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1 REQ_NUMBER
, POH.SEGMENT1 PO_NUMBER
, NVL (PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION)
, NVL(UOM.UOM_CODE
, RQLUOM.UOM_CODE)
, NVL(PD.UNIT_PRICE
, RQL.UNIT_PRICE)
, NVL(POH.CURRENCY_CODE
, RQL.CURRENCY_CODE)
, RQL.QUANTITY REQ_LINE_QTY
, ( 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) REQ_LINE_QTY_CANCELLED
, PD.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED )
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, PD.ITEM_ID
, PD.WIP_RESOURCE_SEQ_NUM
, DECODE(POH.VENDOR_ID
, NULL
, RQL.VENDOR_ID
, POH.VENDOR_ID )
, NVL(POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME)
, NVL(POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID)
, NVL(POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION)
, NVL(POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID)
, NVL(DECODE(POVC.FIRST_NAME
, NULL
, POVC.LAST_NAME
, DECODE( POVC.LAST_NAME
, NULL
, POVC.FIRST_NAME
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME) )
, RQL.SUGGESTED_VENDOR_CONTACT )
, NVL(POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE)
, NVL(PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE)
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID
, NVL(PD.CATEGORY_ID
, RQL.CATEGORY_ID)
, NVL(POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE)
, NVL(PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE)
, DECODE(PD.ORDER_TYPE_LOOKUP_CODE
, NULL
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED))
, SUM(PD.AMOUNT_DELIVERED)
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED)
FROM PO_LINE_TYPES PLT
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL POLLOC
, PO_REQUISITION_HEADERS_ALL RQH
, PO_REQUISITION_LINES_ALL RQL
, MTL_UNITS_OF_MEASURE UOM
, MTL_UNITS_OF_MEASURE RQLUOM
, ( SELECT PD1.WIP_ENTITY_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.DESTINATION_ORGANIZATION_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_PRICE
, POL.QUANTITY
, PD1.QUANTITY_DELIVERED
, PD1.QUANTITY_ORDERED
, PD1.QUANTITY_CANCELLED
, POL.CLOSED_CODE
, POL.PO_LINE_ID
, POL.PO_HEADER_ID
, PD1.REQ_DISTRIBUTION_ID
, PD1.LINE_LOCATION_ID
, POL.LINE_TYPE_ID
, PD1.DESTINATION_TYPE_CODE
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.CANCEL_FLAG
, POL.ITEM_ID
, POL.CATEGORY_ID
, POL.VENDOR_PRODUCT_NUM
, PD1.PO_RELEASE_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.ORDER_TYPE_LOOKUP_CODE
, PD1.AMOUNT_ORDERED
, 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
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
WHERE POH.PO_HEADER_ID = PD.PO_HEADER_ID
AND RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID(+)
AND POLLOC.PO_LINE_ID = PD.PO_LINE_ID
AND POLLOC.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND POLLOC.LINE_LOCATION_ID = RQL.LINE_LOCATION_ID (+)
AND PD.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE (+)
AND RQL.UNIT_MEAS_LOOKUP_CODE = RQLUOM.UNIT_OF_MEASURE (+)
AND PD.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')) = 'N'
AND PD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND UPPER(NVL(PD.CANCEL_FLAG
, 'N')) <> 'Y'
AND POH.VENDOR_ID = POV. VENDOR_ID (+)
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND POH.VENDOR_SITE_ID = POVC.VENDOR_SITE_ID (+)
AND POH.VENDOR_CONTACT_ID = POVC.VENDOR_CONTACT_ID (+)
AND PD.WIP_ENTITY_ID IS NOT NULL
AND NVL(RQL.MODIFIED_BY_AGENT_FLAG
, 'N') <> 'Y' GROUP BY PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1
, RQH.AUTHORIZATION_STATUS
, POH.SEGMENT1
, PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION
, UOM.UOM_CODE
, RQLUOM.UOM_CODE
, PD.CATEGORY_ID
, RQL.CATEGORY_ID
, PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.UNIT_PRICE
, PD.UNIT_PRICE
, POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE
, RQL.CURRENCY_CODE
, POH.CURRENCY_CODE
, RQL.QUANTITY
, RQL.CANCEL_FLAG
, PD.QUANTITY
, PD.ITEM_ID
, PD.WIP_RESOURCE_SEQ_NUM
, RQL.VENDOR_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME
, POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION
, POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID
, POVC.FIRST_NAME
, POVC.LAST_NAME
, RQL.SUGGESTED_VENDOR_CONTACT
, POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE
, PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH .PO_HEADER_ID
, PD.PO_RELEASE_ID UNION SELECT RQL.WIP_ENTITY_ID
, RQL.WIP_OPERATION_SEQ_NUM
, RQL.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1 REQ_NUMBER
, POH.SEGMENT1 PO_NUMBER
, NVL(PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION)
, NVL(UOM.UOM_CODE
, RQLUOM.UOM_CODE)
, NVL(PD.UNIT_PRICE
, RQL.UNIT_PRICE)
, NVL( POH.CURRENCY_CODE
, RQL.CURRENCY_CODE)
, RQL.QUANTITY REQ_LINE_QTY
, ( 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) REQ_LINE_QTY_CANCELLED
, PD.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED)
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, RQL.ITEM_ID
, RQL.WIP_RESOURCE_SEQ_NUM
, DECODE(POH.VENDOR_ID
, NULL
, RQL.VENDOR_ID
, POH.VENDOR_ID)
, NVL(POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME)
, NVL(POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID)
, NVL(POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION)
, NVL(POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID)
, NVL(DECODE(POVC.FIRST_NAME
, NULL
, POVC.LAST_NAME
, DECODE( POVC.LAST_NAME
, NULL
, POVC.FIRST_NAME
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME) )
, RQL.SUGGESTED_VENDOR_CONTACT )
, NVL(POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE)
, NVL(PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE)
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID
, NVL(PD.CATEGORY_ID
, RQL.CATEGORY_ID)
, NVL(POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE)
, NVL(PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE)
, DECODE(PD.ORDER_TYPE_LOOKUP_CODE
, NULL
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED))
, SUM(PD.AMOUNT_DELIVERED)
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED)
FROM PO_LINE_TYPES PLT
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL POLLOC
, PO_REQUISITION_HEADERS_ALL RQH
, PO_REQUISITION_LINES_ALL RQL
, MTL_UNITS_OF_MEASURE UOM
, MTL_UNITS_OF_MEASURE RQLUOM
, ( SELECT PD1.WIP_ENTITY_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.DESTINATION_ORGANIZATION_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_PRICE
, POL.QUANTITY
, PD1.QUANTITY_DELIVERED
, PD1.QUANTITY_ORDERED
, PD1.QUANTITY_CANCELLED
, POL.CLOSED_CODE
, POL.PO_LINE_ID
, POL.PO_HEADER_ID
, PD1.REQ_DISTRIBUTION_ID
, PD1.LINE_LOCATION_ID
, POL.LINE_TYPE_ID
, PD1.DESTINATION_TYPE_CODE
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.CANCEL_FLAG
, POL.ITEM_ID
, POL .CATEGORY_ID
, POL.VENDOR_PRODUCT_NUM
, PD1.PO_RELEASE_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.ORDER_TYPE_LOOKUP_CODE
, PD1.AMOUNT_ORDERED
, 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
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
WHERE PD.PO_HEADER_ID = POH.PO_HEADER_ID(+)
AND RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
AND RQL.LINE_LOCATION_ID = POLLOC.LINE_LOCATION_ID (+)
AND POLLOC.PO_LINE_ID = PD.PO_LINE_ID (+)
AND POLLOC.LINE_LOCATION_ID = PD.LINE_LOCATION_ID (+)
AND PD.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE (+)
AND RQL.UNIT_MEAS_LOOKUP_CODE = RQLUOM.UNIT_OF_MEASURE(+)
AND RQL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')) = 'N'
AND RQL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND UPPER(NVL(RQH.CANCEL_FLAG
, 'N')) <> 'Y'
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND POH.VENDOR_CONTACT_ID = POVC.VENDOR_CONTACT_ID (+)
AND RQL.WIP_ENTITY_ID IS NOT NULL
AND NVL(RQL.MODIFIED_BY_AGENT_FLAG
, 'N') <> 'Y' GROUP BY RQL.WIP_ENTITY_ID
, RQL.WIP_OPERATION_SEQ_NUM
, RQL.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1
, RQH.AUTHORIZATION_STATUS
, POH.SEGMENT1
, PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION
, RQLUOM.UOM_CODE
, UOM.UOM_CODE
, PD.CATEGORY_ID
, RQL.CATEGORY_ID
, PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.UNIT_PRICE
, PD.UNIT_PRICE
, POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE
, RQL.CURRENCY_CODE
, POH.CURRENCY_CODE
, RQL.QUANTITY
, RQL.CANCEL_FLAG
, PD.QUANTITY
, RQL.ITEM_ID
, RQL.WIP_RESOURCE_SEQ_NUM
, RQL.VENDOR_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME
, POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION
, POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID
, POVC.FIRST_NAME
, POVC.LAST_NAME
, RQL.SUGGESTED_VENDOR_CONTACT
, POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE
, PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID