VIEW: APPS.POR_RECEIVE_ORDERS_V

Source

View Text - Preformatted

SELECT 'NOT' LINE_CHKBOX, SYSDATE RECEIPT_DATE, POH.AGENT_ID AGENT_ID, POD.DELIVER_TO_PERSON_ID REQUESTOR_ID, POH.CREATION_DATE REQUISITION_CREATION_DATE, 'VENDOR' SOURCE_TYPE_CODE, 'VENDOR' RECEIPT_SOURCE_CODE, 'PO' ORDER_TYPE_CODE, PLC.DISPLAYED_FIELD ORDER_TYPE, POLL.PO_HEADER_ID PO_HEADER_ID, POH.SEGMENT1 PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POL.LINE_NUM PO_LINE_NUMBER, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, POLL.PO_RELEASE_ID PO_RELEASE_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, TO_NUMBER(NULL) REQ_HEADER_ID, NULL REQ_NUMBER, TO_NUMBER(NULL) REQ_LINE_ID, TO_NUMBER(NULL) REQ_LINE, TO_NUMBER(NULL) REQ_DISTRIBUTION_ID, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID, POH.SEGMENT1 RCV_SHIPMENT_NUMBER, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID, POL.LINE_NUM RCV_LINE_NUMBER, POH.PO_HEADER_ID FROM_ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, POH.VENDOR_SITE_ID VENDOR_SITE_ID, PVS.VENDOR_SITE_CODE SUPPLIER_SITE, NVL(POLT.OUTSIDE_OPERATION_FLAG,'NOT') OUTSIDE_OPERATION_FLAG, POL.ITEM_ID ITEM_ID, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS PRIMARY_UOM_CLASS, POD.QUANTITY_DELIVERED, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)),0), POD.QUANTITY_CANCELLED, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE, NVL(MSI.LOCATION_CONTROL_CODE, 1) ITEM_LOCATOR_CONTROL, DECODE(MSI.RESTRICT_LOCATORS_CODE, 1,'Y','NOT') RESTRICT_LOCATORS_CODE, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE, 1,'Y','NOT') RESTRICT_SUBINVENTORIES_CODE, NVL(MSI.SHELF_LIFE_CODE, 1) SHELF_LIFE_CODE, NVL(MSI.SHELF_LIFE_DAYS, 0) SHELF_LIFE_DAYS, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, DECODE(MSI.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','NOT') ITEM_REV_CONTROL_FLAG_TO, NULL ITEM_REV_CONTROL_FLAG_FROM, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER, POL.ITEM_REVISION ITEM_REVISION, SUBSTR( POL.ITEM_DESCRIPTION, 1, 240) ITEM_DESCRIPTION, POL.CATEGORY_ID ITEM_CATEGORY_ID, MCA.CONCATENATED_SEGMENTS CATEGORY, POHC.HAZARD_CLASS HAZARD_CLASS, POUN.UN_NUMBER UN_NUMBER, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, HL.LOCATION_CODE SHIP_TO_LOCATION, HL2.LOCATION_CODE DELIVER_TO_LOCATION, NULL PACKING_SLIP, POLL.RECEIVING_ROUTING_ID ROUTING_ID, RCVRH.ROUTING_NAME ROUTING_NAME, POLL.NEED_BY_DATE NEED_BY_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, POD.QUANTITY_ORDERED ORDERED_QTY, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE, POL.UNIT_PRICE UNIT_PRICE, POH.CURRENCY_CODE, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE, POH.RATE_DATE CURRENCY_CONVERSION_DATE, POH.RATE CURRENCY_CONVERSION_RATE, POH.NOTE_TO_RECEIVER, POD.DESTINATION_TYPE_CODE, POD. DELIVER_TO_PERSON_ID, POD. DELIVER_TO_LOCATION_ID, POD.DESTINATION_SUBINVENTORY, POD.PO_DISTRIBUTION_ID, POLL.ATTRIBUTE_CATEGORY, POLL.ATTRIBUTE1, POLL.ATTRIBUTE2, POLL.ATTRIBUTE3, POLL.ATTRIBUTE4, POLL.ATTRIBUTE5, POLL.ATTRIBUTE6, POLL.ATTRIBUTE7, POLL.ATTRIBUTE8, POLL.ATTRIBUTE9, POLL.ATTRIBUTE10, POLL.ATTRIBUTE11, POLL.ATTRIBUTE12, POLL.ATTRIBUTE13, POLL.ATTRIBUTE14, POLL.ATTRIBUTE15, POLL.CLOSED_CODE, NULL AUTO_RECEIVE_FLAG, HRE.FULL_NAME REQUESTER, POH.REVISION_NUM, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) FROM HR_EMPLOYEES_CURRENT_V HRE, PO_RELEASES POR, PO_LOOKUP_CODES PLC, PO_VENDORS POV, PO_VENDOR_SITES PVS, PO_HAZARD_CLASSES POHC, PO_UN_NUMBERS POUN, RCV_ROUTING_HEADERS RCVRH, HR_LOCATIONS HL, HR_LOCATIONS HL2, MTL_SYSTEM_ITEMS MSI, MTL_UNITS_OF_MEASURE MUM, PO_LINE_TYPES POLT, MTL_CATEGORIES_KFV MCA, PO_LINES POL, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL WHERE HRE.EMPLOYEE_ID = POD.DELIVER_TO_PERSON_ID AND POD.DELIVER_TO_PERSON_ID IS NOT NULL AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) >0 AND NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POLL.RECEIVING_ROUTING_ID = 3 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+) AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+) AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+) AND POD.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID(+) AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND PLC.LOOKUP_TYPE = 'PO TYPE' AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+) AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+) AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE AND NVL(MSI.ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND POD.REQ_DISTRIBUTION_ID IS NULL AND MCA.CATEGORY_ID = POL.CATEGORY_ID UNION ALL SELECT 'NOT' LINE_CHKBOX, SYSDATE RECEIPT_DATE, POH.AGENT_ID AGENT_ID, POH.AGENT_ID REQUESTOR_ID, POH.CREATION_DATE REQUISITION_CREATION_DATE, 'VENDOR' SOURCE_TYPE_CODE, 'VENDOR' RECEIPT_SOURCE_CODE, 'PO' ORDER_TYPE_CODE, PLC.DISPLAYED_FIELD ORDER_TYPE, POLL.PO_HEADER_ID PO_HEADER_ID, POH.SEGMENT1 PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POL.LINE_NUM PO_LINE_NUMBER, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, POLL.PO_RELEASE_ID PO_RELEASE_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, TO_NUMBER(NULL) REQ_HEADER_ID, NULL REQ_NUMBER, TO_NUMBER(NULL) REQ_LINE_ID, TO_NUMBER(NULL) REQ_LINE, TO_NUMBER(NULL) REQ_DISTRIBUTION_ID, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID, POH.SEGMENT1 RCV_SHIPMENT_NUMBER, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID, POL.LINE_NUM RCV_LINE_NUMBER, POH.PO_HEADER_ID FROM_ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, POH.VENDOR_SITE_ID VENDOR_SITE_ID, PVS.VENDOR_SITE_CODE SUPPLIER_SITE, NVL(POLT.OUTSIDE_OPERATION_FLAG,'NOT') OUTSIDE_OPERATION_FLAG, POL.ITEM_ID ITEM_ID, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS PRIMARY_UOM_CLASS, POD.QUANTITY_DELIVERED, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)),0), POD.QUANTITY_CANCELLED, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE, NVL(MSI.LOCATION_CONTROL_CODE, 1) ITEM_LOCATOR_CONTROL, DECODE(MSI.RESTRICT_LOCATORS_CODE, 1,'Y','NOT') RESTRICT_LOCATORS_CODE, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE, 1,'Y','NOT') RESTRICT_SUBINVENTORIES_CODE, NVL(MSI.SHELF_LIFE_CODE, 1) SHELF_LIFE_CODE, NVL(MSI.SHELF_LIFE_DAYS, 0) SHELF_LIFE_DAYS, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, DECODE(MSI.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','NOT') ITEM_REV_CONTROL_FLAG_TO, NULL ITEM_REV_CONTROL_FLAG_FROM, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER, POL.ITEM_REVISION ITEM_REVISION, SUBSTR( POL.ITEM_DESCRIPTION, 1, 240) ITEM_DESCRIPTION, POL.CATEGORY_ID ITEM_CATEGORY_ID, MCA.CONCATENATED_SEGMENTS CATEGORY, POHC.HAZARD_CLASS HAZARD_CLASS, POUN.UN_NUMBER UN_NUMBER, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, HL.LOCATION_CODE SHIP_TO_LOCATION, HL2.LOCATION_CODE DELIVER_TO_LOCATION, NULL PACKING_SLIP, POLL.RECEIVING_ROUTING_ID ROUTING_ID, RCVRH.ROUTING_NAME ROUTING_NAME, POLL.NEED_BY_DATE NEED_BY_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, POD.QUANTITY_ORDERED ORDERED_QTY, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE, POL.UNIT_PRICE UNIT_PRICE, POH.CURRENCY_CODE, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE, POH.RATE_DATE CURRENCY_CONVERSION_DATE, POH.RATE CURRENCY_CONVERSION_RATE, POH.NOTE_TO_RECEIVER, POD.DESTINATION_TYPE_CODE, POD. DELIVER_TO_PERSON_ID, POD. DELIVER_TO_LOCATION_ID, POD.DESTINATION_SUBINVENTORY, POD.PO_DISTRIBUTION_ID, POLL.ATTRIBUTE_CATEGORY, POLL.ATTRIBUTE1, POLL.ATTRIBUTE2, POLL.ATTRIBUTE3, POLL.ATTRIBUTE4, POLL.ATTRIBUTE5, POLL.ATTRIBUTE6, POLL.ATTRIBUTE7, POLL.ATTRIBUTE8, POLL.ATTRIBUTE9, POLL.ATTRIBUTE10, POLL.ATTRIBUTE11, POLL.ATTRIBUTE12, POLL.ATTRIBUTE13, POLL.ATTRIBUTE14, POLL.ATTRIBUTE15, POLL.CLOSED_CODE, NULL AUTO_RECEIVE_FLAG, HRE.FULL_NAME REQUESTER, POH.REVISION_NUM, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) FROM HR_EMPLOYEES_CURRENT_V HRE, PO_RELEASES POR, PO_LOOKUP_CODES PLC, PO_VENDORS POV, PO_VENDOR_SITES PVS, PO_HAZARD_CLASSES POHC, PO_UN_NUMBERS POUN, RCV_ROUTING_HEADERS RCVRH, HR_LOCATIONS HL, HR_LOCATIONS HL2, MTL_SYSTEM_ITEMS MSI, MTL_UNITS_OF_MEASURE MUM, PO_LINE_TYPES POLT, MTL_CATEGORIES_KFV MCA, PO_LINES POL, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL WHERE HRE.EMPLOYEE_ID = POH.AGENT_ID AND POD.DELIVER_TO_PERSON_ID IS NULL AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) >0 AND NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POLL.RECEIVING_ROUTING_ID = 3 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+) AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+) AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+) AND POD.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID(+) AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND PLC.LOOKUP_TYPE = 'PO TYPE' AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+) AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+) AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE AND NVL(MSI.ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND POD.REQ_DISTRIBUTION_ID IS NULL AND MCA.CATEGORY_ID = POL.CATEGORY_ID UNION ALL SELECT 'NOT' LINE_CHKBOX, SYSDATE RECEIPT_DATE, POH.AGENT_ID AGENT_ID, POD.DELIVER_TO_PERSON_ID REQUESTOR_ID, POH.CREATION_DATE REQUISITION_CREATION_DATE, 'VENDOR' SOURCE_TYPE_CODE, 'VENDOR' RECEIPT_SOURCE_CODE, 'PO' ORDER_TYPE_CODE, PLC.DISPLAYED_FIELD ORDER_TYPE, POLL.PO_HEADER_ID PO_HEADER_ID, POH.SEGMENT1 PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POL.LINE_NUM PO_LINE_NUMBER, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, POLL.PO_RELEASE_ID PO_RELEASE_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, PORH.REQUISITION_HEADER_ID REQ_HEADER_ID, PORH.SEGMENT1 REQ_NUMBER, PORL2.REQUISITION_LINE_ID REQ_LINE_ID, PORL2.LINE_NUM REQ_LINE, PORD.DISTRIBUTION_ID REQ_DISTRIBUTION_ID, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID, POH.SEGMENT1 RCV_SHIPMENT_NUMBER, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID, POL.LINE_NUM RCV_LINE_NUMBER, POH.PO_HEADER_ID FROM_ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, POH.VENDOR_SITE_ID VENDOR_SITE_ID, PVS.VENDOR_SITE_CODE SUPPLIER_SITE, NVL(POLT.OUTSIDE_OPERATION_FLAG,'NOT') OUTSIDE_OPERATION_FLAG, POL.ITEM_ID ITEM_ID, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS PRIMARY_UOM_CLASS, POD.QUANTITY_DELIVERED, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)),0), POD.QUANTITY_CANCELLED, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE, NVL(MSI.LOCATION_CONTROL_CODE, 1) ITEM_LOCATOR_CONTROL, DECODE(MSI.RESTRICT_LOCATORS_CODE, 1,'Y','NOT') RESTRICT_LOCATORS_CODE, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE, 1,'Y','NOT') RESTRICT_SUBINVENTORIES_CODE, NVL(MSI.SHELF_LIFE_CODE, 1) SHELF_LIFE_CODE, NVL(MSI.SHELF_LIFE_DAYS, 0) SHELF_LIFE_DAYS, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, DECODE(MSI.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','NOT') ITEM_REV_CONTROL_FLAG_TO, NULL ITEM_REV_CONTROL_FLAG_FROM, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER, POL.ITEM_REVISION ITEM_REVISION, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION, POL.CATEGORY_ID ITEM_CATEGORY_ID, MCA.CONCATENATED_SEGMENTS CATEGORY, POHC.HAZARD_CLASS HAZARD_CLASS, POUN.UN_NUMBER UN_NUMBER, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, HL.LOCATION_CODE SHIP_TO_LOCATION, HL2.LOCATION_CODE DELIVER_TO_LOCATION, NULL PACKING_SLIP, POLL.RECEIVING_ROUTING_ID ROUTING_ID, RCVRH.ROUTING_NAME ROUTING_NAME, POLL.NEED_BY_DATE NEED_BY_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, POD.QUANTITY_ORDERED ORDERED_QTY, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE, POL.UNIT_PRICE UNIT_PRICE, POH.CURRENCY_CODE, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE, POH.RATE_DATE CURRENCY_CONVERSION_DATE, POH.RATE CURRENCY_CONVERSION_RATE, POH.NOTE_TO_RECEIVER, POD.DESTINATION_TYPE_CODE, POD.DELIVER_TO_PERSON_ID, POD.DELIVER_TO_LOCATION_ID, POD.DESTINATION_SUBINVENTORY, POD.PO_DISTRIBUTION_ID, POLL.ATTRIBUTE_CATEGORY, POLL.ATTRIBUTE1, POLL.ATTRIBUTE2, POLL.ATTRIBUTE3, POLL.ATTRIBUTE4, POLL.ATTRIBUTE5, POLL.ATTRIBUTE6, POLL.ATTRIBUTE7, POLL.ATTRIBUTE8, POLL.ATTRIBUTE9, POLL.ATTRIBUTE10, POLL.ATTRIBUTE11, POLL.ATTRIBUTE12, POLL.ATTRIBUTE13, POLL.ATTRIBUTE14, POLL.ATTRIBUTE15, POLL.CLOSED_CODE, PORL2.AUTO_RECEIVE_FLAG, HRE.FULL_NAME REQUESTER, POH.REVISION_NUM, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) FROM HR_EMPLOYEES_CURRENT_V HRE, PO_RELEASES POR, PO_LOOKUP_CODES PLC, PO_VENDORS POV, PO_VENDOR_SITES PVS, PO_HAZARD_CLASSES POHC, PO_UN_NUMBERS POUN, RCV_ROUTING_HEADERS RCVRH, HR_LOCATIONS HL, HR_LOCATIONS HL2, MTL_SYSTEM_ITEMS MSI, MTL_UNITS_OF_MEASURE MUM, PO_LINE_TYPES POLT, PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL2, MTL_CATEGORIES_KFV MCA, PO_REQ_DISTRIBUTIONS PORD, PO_LINES POL, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL WHERE HRE.EMPLOYEE_ID = POD.DELIVER_TO_PERSON_ID AND POD.DELIVER_TO_PERSON_ID IS NOT NULL AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) >0 AND NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POLL.RECEIVING_ROUTING_ID = 3 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+) AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+) AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+) AND PORL2.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID(+) AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND PLC.LOOKUP_TYPE = 'PO TYPE' AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+) AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+) AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE AND NVL(MSI.ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND PORD.REQUISITION_LINE_ID = PORL2.REQUISITION_LINE_ID AND EXISTS ( SELECT 'X' FROM PO_REQUISITION_LINES PORL WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID ) AND PORL2.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID AND MCA.CATEGORY_ID = POL.CATEGORY_ID UNION ALL SELECT 'NOT' LINE_CHKBOX, SYSDATE RECEIPT_DATE, POH.AGENT_ID AGENT_ID, POH.AGENT_ID REQUESTOR_ID, POH.CREATION_DATE REQUISITION_CREATION_DATE, 'VENDOR' SOURCE_TYPE_CODE, 'VENDOR' RECEIPT_SOURCE_CODE, 'PO' ORDER_TYPE_CODE, PLC.DISPLAYED_FIELD ORDER_TYPE, POLL.PO_HEADER_ID PO_HEADER_ID, POH.SEGMENT1 PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POL.LINE_NUM PO_LINE_NUMBER, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, POLL.PO_RELEASE_ID PO_RELEASE_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, PORH.REQUISITION_HEADER_ID REQ_HEADER_ID, PORH.SEGMENT1 REQ_NUMBER, PORL2.REQUISITION_LINE_ID REQ_LINE_ID, PORL2.LINE_NUM REQ_LINE, PORD.DISTRIBUTION_ID REQ_DISTRIBUTION_ID, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID, POH.SEGMENT1 RCV_SHIPMENT_NUMBER, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID, POL.LINE_NUM RCV_LINE_NUMBER, POH.PO_HEADER_ID FROM_ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, POH.VENDOR_SITE_ID VENDOR_SITE_ID, PVS.VENDOR_SITE_CODE SUPPLIER_SITE, NVL(POLT.OUTSIDE_OPERATION_FLAG,'NOT') OUTSIDE_OPERATION_FLAG, POL.ITEM_ID ITEM_ID, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS PRIMARY_UOM_CLASS, POD.QUANTITY_DELIVERED, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)),0), POD.QUANTITY_CANCELLED, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE, NVL(MSI.LOCATION_CONTROL_CODE, 1) ITEM_LOCATOR_CONTROL, DECODE(MSI.RESTRICT_LOCATORS_CODE, 1,'Y','NOT') RESTRICT_LOCATORS_CODE, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE, 1,'Y','NOT') RESTRICT_SUBINVENTORIES_CODE, NVL(MSI.SHELF_LIFE_CODE, 1) SHELF_LIFE_CODE, NVL(MSI.SHELF_LIFE_DAYS, 0) SHELF_LIFE_DAYS, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, DECODE(MSI.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','NOT') ITEM_REV_CONTROL_FLAG_TO, NULL ITEM_REV_CONTROL_FLAG_FROM, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER, POL.ITEM_REVISION ITEM_REVISION, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION, POL.CATEGORY_ID ITEM_CATEGORY_ID, MCA.CONCATENATED_SEGMENTS CATEGORY, POHC.HAZARD_CLASS HAZARD_CLASS, POUN.UN_NUMBER UN_NUMBER, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, HL.LOCATION_CODE SHIP_TO_LOCATION, HL2.LOCATION_CODE DELIVER_TO_LOCATION, NULL PACKING_SLIP, POLL.RECEIVING_ROUTING_ID ROUTING_ID, RCVRH.ROUTING_NAME ROUTING_NAME, POLL.NEED_BY_DATE NEED_BY_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, POD.QUANTITY_ORDERED ORDERED_QTY, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE, POL.UNIT_PRICE UNIT_PRICE, POH.CURRENCY_CODE, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE, POH.RATE_DATE CURRENCY_CONVERSION_DATE, POH.RATE CURRENCY_CONVERSION_RATE, POH.NOTE_TO_RECEIVER, POD.DESTINATION_TYPE_CODE, POD.DELIVER_TO_PERSON_ID, POD.DELIVER_TO_LOCATION_ID, POD.DESTINATION_SUBINVENTORY, POD.PO_DISTRIBUTION_ID, POLL.ATTRIBUTE_CATEGORY, POLL.ATTRIBUTE1, POLL.ATTRIBUTE2, POLL.ATTRIBUTE3, POLL.ATTRIBUTE4, POLL.ATTRIBUTE5, POLL.ATTRIBUTE6, POLL.ATTRIBUTE7, POLL.ATTRIBUTE8, POLL.ATTRIBUTE9, POLL.ATTRIBUTE10, POLL.ATTRIBUTE11, POLL.ATTRIBUTE12, POLL.ATTRIBUTE13, POLL.ATTRIBUTE14, POLL.ATTRIBUTE15, POLL.CLOSED_CODE, PORL2.AUTO_RECEIVE_FLAG, HRE.FULL_NAME REQUESTER, POH.REVISION_NUM, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) FROM HR_EMPLOYEES_CURRENT_V HRE, PO_RELEASES POR, PO_LOOKUP_CODES PLC, PO_VENDORS POV, PO_VENDOR_SITES PVS, PO_HAZARD_CLASSES POHC, PO_UN_NUMBERS POUN, RCV_ROUTING_HEADERS RCVRH, HR_LOCATIONS HL, HR_LOCATIONS HL2, MTL_SYSTEM_ITEMS MSI, MTL_UNITS_OF_MEASURE MUM, PO_LINE_TYPES POLT, PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL2, MTL_CATEGORIES_KFV MCA, PO_REQ_DISTRIBUTIONS PORD, PO_LINES POL, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL WHERE HRE.EMPLOYEE_ID = POH.AGENT_ID AND POD.DELIVER_TO_PERSON_ID IS NULL AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) >0 AND NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POLL.RECEIVING_ROUTING_ID = 3 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+) AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+) AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+) AND PORL2.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID(+) AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND PLC.LOOKUP_TYPE = 'PO TYPE' AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+) AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+) AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE AND NVL(MSI.ORGANIZATION_ID, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND PORD.REQUISITION_LINE_ID = PORL2.REQUISITION_LINE_ID AND EXISTS ( SELECT 'X' FROM PO_REQUISITION_LINES PORL WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID ) AND PORL2.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID AND MCA.CATEGORY_ID = POL.CATEGORY_ID

View Text - HTML Formatted

SELECT 'NOT' LINE_CHKBOX
, SYSDATE RECEIPT_DATE
, POH.AGENT_ID AGENT_ID
, POD.DELIVER_TO_PERSON_ID REQUESTOR_ID
, POH.CREATION_DATE REQUISITION_CREATION_DATE
, 'VENDOR' SOURCE_TYPE_CODE
, 'VENDOR' RECEIPT_SOURCE_CODE
, 'PO' ORDER_TYPE_CODE
, PLC.DISPLAYED_FIELD ORDER_TYPE
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POL.LINE_NUM PO_LINE_NUMBER
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, POLL.PO_RELEASE_ID PO_RELEASE_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, TO_NUMBER(NULL) REQ_HEADER_ID
, NULL REQ_NUMBER
, TO_NUMBER(NULL) REQ_LINE_ID
, TO_NUMBER(NULL) REQ_LINE
, TO_NUMBER(NULL) REQ_DISTRIBUTION_ID
, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID
, POH.SEGMENT1 RCV_SHIPMENT_NUMBER
, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID
, POL.LINE_NUM RCV_LINE_NUMBER
, POH.PO_HEADER_ID FROM_ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE
, NVL(POLT.OUTSIDE_OPERATION_FLAG
, 'NOT') OUTSIDE_OPERATION_FLAG
, POL.ITEM_ID ITEM_ID
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS PRIMARY_UOM_CLASS
, POD.QUANTITY_DELIVERED
, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))
, 0)
, POD.QUANTITY_CANCELLED
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'NOT') ITEM_REV_CONTROL_FLAG_TO
, NULL ITEM_REV_CONTROL_FLAG_FROM
, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER
, POL.ITEM_REVISION ITEM_REVISION
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, POL.CATEGORY_ID ITEM_CATEGORY_ID
, MCA.CONCATENATED_SEGMENTS CATEGORY
, POHC.HAZARD_CLASS HAZARD_CLASS
, POUN.UN_NUMBER UN_NUMBER
, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE SHIP_TO_LOCATION
, HL2.LOCATION_CODE DELIVER_TO_LOCATION
, NULL PACKING_SLIP
, POLL.RECEIVING_ROUTING_ID ROUTING_ID
, RCVRH.ROUTING_NAME ROUTING_NAME
, POLL.NEED_BY_DATE NEED_BY_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.QUANTITY_ORDERED ORDERED_QTY
, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, POL.UNIT_PRICE UNIT_PRICE
, POH.CURRENCY_CODE
, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE
, POH.RATE_DATE CURRENCY_CONVERSION_DATE
, POH.RATE CURRENCY_CONVERSION_RATE
, POH.NOTE_TO_RECEIVER
, POD.DESTINATION_TYPE_CODE
, POD. DELIVER_TO_PERSON_ID
, POD. DELIVER_TO_LOCATION_ID
, POD.DESTINATION_SUBINVENTORY
, POD.PO_DISTRIBUTION_ID
, POLL.ATTRIBUTE_CATEGORY
, POLL.ATTRIBUTE1
, POLL.ATTRIBUTE2
, POLL.ATTRIBUTE3
, POLL.ATTRIBUTE4
, POLL.ATTRIBUTE5
, POLL.ATTRIBUTE6
, POLL.ATTRIBUTE7
, POLL.ATTRIBUTE8
, POLL.ATTRIBUTE9
, POLL.ATTRIBUTE10
, POLL.ATTRIBUTE11
, POLL.ATTRIBUTE12
, POLL.ATTRIBUTE13
, POLL.ATTRIBUTE14
, POLL.ATTRIBUTE15
, POLL.CLOSED_CODE
, NULL AUTO_RECEIVE_FLAG
, HRE.FULL_NAME REQUESTER
, POH.REVISION_NUM
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM)
FROM HR_EMPLOYEES_CURRENT_V HRE
, PO_RELEASES POR
, PO_LOOKUP_CODES PLC
, PO_VENDORS POV
, PO_VENDOR_SITES PVS
, PO_HAZARD_CLASSES POHC
, PO_UN_NUMBERS POUN
, RCV_ROUTING_HEADERS RCVRH
, HR_LOCATIONS HL
, HR_LOCATIONS HL2
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE MUM
, PO_LINE_TYPES POLT
, MTL_CATEGORIES_KFV MCA
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE HRE.EMPLOYEE_ID = POD.DELIVER_TO_PERSON_ID
AND POD.DELIVER_TO_PERSON_ID IS NOT NULL
AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) >0
AND NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POLL.RECEIVING_ROUTING_ID = 3
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+)
AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND POD.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID(+)
AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PLC.LOOKUP_TYPE = 'PO TYPE'
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+)
AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+)
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND NVL(MSI.ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID
AND POD.REQ_DISTRIBUTION_ID IS NULL
AND MCA.CATEGORY_ID = POL.CATEGORY_ID UNION ALL SELECT 'NOT' LINE_CHKBOX
, SYSDATE RECEIPT_DATE
, POH.AGENT_ID AGENT_ID
, POH.AGENT_ID REQUESTOR_ID
, POH.CREATION_DATE REQUISITION_CREATION_DATE
, 'VENDOR' SOURCE_TYPE_CODE
, 'VENDOR' RECEIPT_SOURCE_CODE
, 'PO' ORDER_TYPE_CODE
, PLC.DISPLAYED_FIELD ORDER_TYPE
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POL.LINE_NUM PO_LINE_NUMBER
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, POLL.PO_RELEASE_ID PO_RELEASE_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, TO_NUMBER(NULL) REQ_HEADER_ID
, NULL REQ_NUMBER
, TO_NUMBER(NULL) REQ_LINE_ID
, TO_NUMBER(NULL) REQ_LINE
, TO_NUMBER(NULL) REQ_DISTRIBUTION_ID
, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID
, POH.SEGMENT1 RCV_SHIPMENT_NUMBER
, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID
, POL.LINE_NUM RCV_LINE_NUMBER
, POH.PO_HEADER_ID FROM_ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE
, NVL(POLT.OUTSIDE_OPERATION_FLAG
, 'NOT') OUTSIDE_OPERATION_FLAG
, POL.ITEM_ID ITEM_ID
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS PRIMARY_UOM_CLASS
, POD.QUANTITY_DELIVERED
, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))
, 0)
, POD.QUANTITY_CANCELLED
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'NOT') ITEM_REV_CONTROL_FLAG_TO
, NULL ITEM_REV_CONTROL_FLAG_FROM
, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER
, POL.ITEM_REVISION ITEM_REVISION
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, POL.CATEGORY_ID ITEM_CATEGORY_ID
, MCA.CONCATENATED_SEGMENTS CATEGORY
, POHC.HAZARD_CLASS HAZARD_CLASS
, POUN.UN_NUMBER UN_NUMBER
, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE SHIP_TO_LOCATION
, HL2.LOCATION_CODE DELIVER_TO_LOCATION
, NULL PACKING_SLIP
, POLL.RECEIVING_ROUTING_ID ROUTING_ID
, RCVRH.ROUTING_NAME ROUTING_NAME
, POLL.NEED_BY_DATE NEED_BY_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.QUANTITY_ORDERED ORDERED_QTY
, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, POL.UNIT_PRICE UNIT_PRICE
, POH.CURRENCY_CODE
, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE
, POH.RATE_DATE CURRENCY_CONVERSION_DATE
, POH.RATE CURRENCY_CONVERSION_RATE
, POH.NOTE_TO_RECEIVER
, POD.DESTINATION_TYPE_CODE
, POD. DELIVER_TO_PERSON_ID
, POD. DELIVER_TO_LOCATION_ID
, POD.DESTINATION_SUBINVENTORY
, POD.PO_DISTRIBUTION_ID
, POLL.ATTRIBUTE_CATEGORY
, POLL.ATTRIBUTE1
, POLL.ATTRIBUTE2
, POLL.ATTRIBUTE3
, POLL.ATTRIBUTE4
, POLL.ATTRIBUTE5
, POLL.ATTRIBUTE6
, POLL.ATTRIBUTE7
, POLL.ATTRIBUTE8
, POLL.ATTRIBUTE9
, POLL.ATTRIBUTE10
, POLL.ATTRIBUTE11
, POLL.ATTRIBUTE12
, POLL.ATTRIBUTE13
, POLL.ATTRIBUTE14
, POLL.ATTRIBUTE15
, POLL.CLOSED_CODE
, NULL AUTO_RECEIVE_FLAG
, HRE.FULL_NAME REQUESTER
, POH.REVISION_NUM
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM)
FROM HR_EMPLOYEES_CURRENT_V HRE
, PO_RELEASES POR
, PO_LOOKUP_CODES PLC
, PO_VENDORS POV
, PO_VENDOR_SITES PVS
, PO_HAZARD_CLASSES POHC
, PO_UN_NUMBERS POUN
, RCV_ROUTING_HEADERS RCVRH
, HR_LOCATIONS HL
, HR_LOCATIONS HL2
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE MUM
, PO_LINE_TYPES POLT
, MTL_CATEGORIES_KFV MCA
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE HRE.EMPLOYEE_ID = POH.AGENT_ID
AND POD.DELIVER_TO_PERSON_ID IS NULL
AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) >0
AND NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POLL.RECEIVING_ROUTING_ID = 3
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+)
AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND POD.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID(+)
AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PLC.LOOKUP_TYPE = 'PO TYPE'
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+)
AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+)
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND NVL(MSI.ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID
AND POD.REQ_DISTRIBUTION_ID IS NULL
AND MCA.CATEGORY_ID = POL.CATEGORY_ID UNION ALL SELECT 'NOT' LINE_CHKBOX
, SYSDATE RECEIPT_DATE
, POH.AGENT_ID AGENT_ID
, POD.DELIVER_TO_PERSON_ID REQUESTOR_ID
, POH.CREATION_DATE REQUISITION_CREATION_DATE
, 'VENDOR' SOURCE_TYPE_CODE
, 'VENDOR' RECEIPT_SOURCE_CODE
, 'PO' ORDER_TYPE_CODE
, PLC.DISPLAYED_FIELD ORDER_TYPE
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POL.LINE_NUM PO_LINE_NUMBER
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, POLL.PO_RELEASE_ID PO_RELEASE_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, PORH.REQUISITION_HEADER_ID REQ_HEADER_ID
, PORH.SEGMENT1 REQ_NUMBER
, PORL2.REQUISITION_LINE_ID REQ_LINE_ID
, PORL2.LINE_NUM REQ_LINE
, PORD.DISTRIBUTION_ID REQ_DISTRIBUTION_ID
, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID
, POH.SEGMENT1 RCV_SHIPMENT_NUMBER
, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID
, POL.LINE_NUM RCV_LINE_NUMBER
, POH.PO_HEADER_ID FROM_ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE
, NVL(POLT.OUTSIDE_OPERATION_FLAG
, 'NOT') OUTSIDE_OPERATION_FLAG
, POL.ITEM_ID ITEM_ID
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS PRIMARY_UOM_CLASS
, POD.QUANTITY_DELIVERED
, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))
, 0)
, POD.QUANTITY_CANCELLED
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'NOT') ITEM_REV_CONTROL_FLAG_TO
, NULL ITEM_REV_CONTROL_FLAG_FROM
, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER
, POL.ITEM_REVISION ITEM_REVISION
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, POL.CATEGORY_ID ITEM_CATEGORY_ID
, MCA.CONCATENATED_SEGMENTS CATEGORY
, POHC.HAZARD_CLASS HAZARD_CLASS
, POUN.UN_NUMBER UN_NUMBER
, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE SHIP_TO_LOCATION
, HL2.LOCATION_CODE DELIVER_TO_LOCATION
, NULL PACKING_SLIP
, POLL.RECEIVING_ROUTING_ID ROUTING_ID
, RCVRH.ROUTING_NAME ROUTING_NAME
, POLL.NEED_BY_DATE NEED_BY_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.QUANTITY_ORDERED ORDERED_QTY
, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, POL.UNIT_PRICE UNIT_PRICE
, POH.CURRENCY_CODE
, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE
, POH.RATE_DATE CURRENCY_CONVERSION_DATE
, POH.RATE CURRENCY_CONVERSION_RATE
, POH.NOTE_TO_RECEIVER
, POD.DESTINATION_TYPE_CODE
, POD.DELIVER_TO_PERSON_ID
, POD.DELIVER_TO_LOCATION_ID
, POD.DESTINATION_SUBINVENTORY
, POD.PO_DISTRIBUTION_ID
, POLL.ATTRIBUTE_CATEGORY
, POLL.ATTRIBUTE1
, POLL.ATTRIBUTE2
, POLL.ATTRIBUTE3
, POLL.ATTRIBUTE4
, POLL.ATTRIBUTE5
, POLL.ATTRIBUTE6
, POLL.ATTRIBUTE7
, POLL.ATTRIBUTE8
, POLL.ATTRIBUTE9
, POLL.ATTRIBUTE10
, POLL.ATTRIBUTE11
, POLL.ATTRIBUTE12
, POLL.ATTRIBUTE13
, POLL.ATTRIBUTE14
, POLL.ATTRIBUTE15
, POLL.CLOSED_CODE
, PORL2.AUTO_RECEIVE_FLAG
, HRE.FULL_NAME REQUESTER
, POH.REVISION_NUM
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM)
FROM HR_EMPLOYEES_CURRENT_V HRE
, PO_RELEASES POR
, PO_LOOKUP_CODES PLC
, PO_VENDORS POV
, PO_VENDOR_SITES PVS
, PO_HAZARD_CLASSES POHC
, PO_UN_NUMBERS POUN
, RCV_ROUTING_HEADERS RCVRH
, HR_LOCATIONS HL
, HR_LOCATIONS HL2
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE MUM
, PO_LINE_TYPES POLT
, PO_REQUISITION_HEADERS PORH
, PO_REQUISITION_LINES PORL2
, MTL_CATEGORIES_KFV MCA
, PO_REQ_DISTRIBUTIONS PORD
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE HRE.EMPLOYEE_ID = POD.DELIVER_TO_PERSON_ID
AND POD.DELIVER_TO_PERSON_ID IS NOT NULL
AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) >0
AND NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POLL.RECEIVING_ROUTING_ID = 3
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+)
AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND PORL2.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID(+)
AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PLC.LOOKUP_TYPE = 'PO TYPE'
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+)
AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+)
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND NVL(MSI.ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID
AND PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID
AND PORD.REQUISITION_LINE_ID = PORL2.REQUISITION_LINE_ID
AND EXISTS ( SELECT 'X'
FROM PO_REQUISITION_LINES PORL
WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID )
AND PORL2.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID
AND MCA.CATEGORY_ID = POL.CATEGORY_ID UNION ALL SELECT 'NOT' LINE_CHKBOX
, SYSDATE RECEIPT_DATE
, POH.AGENT_ID AGENT_ID
, POH.AGENT_ID REQUESTOR_ID
, POH.CREATION_DATE REQUISITION_CREATION_DATE
, 'VENDOR' SOURCE_TYPE_CODE
, 'VENDOR' RECEIPT_SOURCE_CODE
, 'PO' ORDER_TYPE_CODE
, PLC.DISPLAYED_FIELD ORDER_TYPE
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POL.LINE_NUM PO_LINE_NUMBER
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, POLL.PO_RELEASE_ID PO_RELEASE_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, PORH.REQUISITION_HEADER_ID REQ_HEADER_ID
, PORH.SEGMENT1 REQ_NUMBER
, PORL2.REQUISITION_LINE_ID REQ_LINE_ID
, PORL2.LINE_NUM REQ_LINE
, PORD.DISTRIBUTION_ID REQ_DISTRIBUTION_ID
, POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID
, POH.SEGMENT1 RCV_SHIPMENT_NUMBER
, POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID
, POL.LINE_NUM RCV_LINE_NUMBER
, POH.PO_HEADER_ID FROM_ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE
, NVL(POLT.OUTSIDE_OPERATION_FLAG
, 'NOT') OUTSIDE_OPERATION_FLAG
, POL.ITEM_ID ITEM_ID
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS PRIMARY_UOM_CLASS
, POD.QUANTITY_DELIVERED
, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))
, 0)
, POD.QUANTITY_CANCELLED
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'NOT') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'NOT') ITEM_REV_CONTROL_FLAG_TO
, NULL ITEM_REV_CONTROL_FLAG_FROM
, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) ITEM_NUMBER
, POL.ITEM_REVISION ITEM_REVISION
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, POL.CATEGORY_ID ITEM_CATEGORY_ID
, MCA.CONCATENATED_SEGMENTS CATEGORY
, POHC.HAZARD_CLASS HAZARD_CLASS
, POUN.UN_NUMBER UN_NUMBER
, POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE SHIP_TO_LOCATION
, HL2.LOCATION_CODE DELIVER_TO_LOCATION
, NULL PACKING_SLIP
, POLL.RECEIVING_ROUTING_ID ROUTING_ID
, RCVRH.ROUTING_NAME ROUTING_NAME
, POLL.NEED_BY_DATE NEED_BY_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.QUANTITY_ORDERED ORDERED_QTY
, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, POL.UNIT_PRICE UNIT_PRICE
, POH.CURRENCY_CODE
, POH.RATE_TYPE CURRENCY_CONVERSION_TYPE
, POH.RATE_DATE CURRENCY_CONVERSION_DATE
, POH.RATE CURRENCY_CONVERSION_RATE
, POH.NOTE_TO_RECEIVER
, POD.DESTINATION_TYPE_CODE
, POD.DELIVER_TO_PERSON_ID
, POD.DELIVER_TO_LOCATION_ID
, POD.DESTINATION_SUBINVENTORY
, POD.PO_DISTRIBUTION_ID
, POLL.ATTRIBUTE_CATEGORY
, POLL.ATTRIBUTE1
, POLL.ATTRIBUTE2
, POLL.ATTRIBUTE3
, POLL.ATTRIBUTE4
, POLL.ATTRIBUTE5
, POLL.ATTRIBUTE6
, POLL.ATTRIBUTE7
, POLL.ATTRIBUTE8
, POLL.ATTRIBUTE9
, POLL.ATTRIBUTE10
, POLL.ATTRIBUTE11
, POLL.ATTRIBUTE12
, POLL.ATTRIBUTE13
, POLL.ATTRIBUTE14
, POLL.ATTRIBUTE15
, POLL.CLOSED_CODE
, PORL2.AUTO_RECEIVE_FLAG
, HRE.FULL_NAME REQUESTER
, POH.REVISION_NUM
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM)
FROM HR_EMPLOYEES_CURRENT_V HRE
, PO_RELEASES POR
, PO_LOOKUP_CODES PLC
, PO_VENDORS POV
, PO_VENDOR_SITES PVS
, PO_HAZARD_CLASSES POHC
, PO_UN_NUMBERS POUN
, RCV_ROUTING_HEADERS RCVRH
, HR_LOCATIONS HL
, HR_LOCATIONS HL2
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE MUM
, PO_LINE_TYPES POLT
, PO_REQUISITION_HEADERS PORH
, PO_REQUISITION_LINES PORL2
, MTL_CATEGORIES_KFV MCA
, PO_REQ_DISTRIBUTIONS PORD
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE HRE.EMPLOYEE_ID = POH.AGENT_ID
AND POD.DELIVER_TO_PERSON_ID IS NULL
AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) >0
AND NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POLL.RECEIVING_ROUTING_ID = 3
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID(+)
AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND PORL2.DELIVER_TO_LOCATION_ID = HL2.LOCATION_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID(+)
AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PLC.LOOKUP_TYPE = 'PO TYPE'
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID(+)
AND POLL.RECEIVING_ROUTING_ID = RCVRH.ROUTING_HEADER_ID(+)
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND NVL(MSI.ORGANIZATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID
AND PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID
AND PORD.REQUISITION_LINE_ID = PORL2.REQUISITION_LINE_ID
AND EXISTS ( SELECT 'X'
FROM PO_REQUISITION_LINES PORL
WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID )
AND PORL2.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID
AND MCA.CATEGORY_ID = POL.CATEGORY_ID