VIEW: APPS.PO_LINE_LOCATIONS_INQ_V

Source

View Text - Preformatted

SELECT PLL.ROWID, PLL.DAYS_EARLY_RECEIPT_ALLOWED, PLL.PO_LINE_ID, PLL.CREATION_DATE, PLL.QUANTITY, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_REJECTED ), PLL.SHIP_TO_LOCATION_ID, PLL.NEED_BY_DATE, PLL.LAST_ACCEPT_DATE, PLL.UNENCUMBERED_QUANTITY, PLL.FREIGHT_TERMS_LOOKUP_CODE, PLL.ESTIMATED_TAX_AMOUNT, PLL.PRICE_OVERRIDE, PLL.QTY_RCV_EXCEPTION_CODE, NVL (PLL.CLOSED_CODE, 'OPEN'), PLL.RECEIVE_CLOSE_TOLERANCE, PLL.PROGRAM_ID, PLL.GOVERNMENT_CONTEXT, PLL.USSGL_TRANSACTION_CODE, PLL.CLOSED_DATE, PLL.CLOSED_REASON, PLL.UNIT_OF_MEASURE_CLASS, PLL.SHIPMENT_NUM, PLL.DAYS_LATE_RECEIPT_ALLOWED, PLL.ENFORCE_SHIP_TO_LOCATION_CODE, PLL.INSPECTION_REQUIRED_FLAG, PLL.QTY_RCV_TOLERANCE, PLL.RECEIPT_REQUIRED_FLAG, DECODE ( PLL.RECEIPT_REQUIRED_FLAG, 'N', DECODE (PLL.INSPECTION_REQUIRED_FLAG, 'N', '2-Way', '2-Way'), 'Y', DECODE ( PLL.INSPECTION_REQUIRED_FLAG, 'N', '3-Way', 'Y', '4-Way', '3-Way' ) ), PLL.SHIP_TO_ORGANIZATION_ID, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG, PLL.RECEIPT_DAYS_EXCEPTION_CODE, PLL.INVOICE_CLOSE_TOLERANCE, PLL.REQUEST_ID, PLL.PROGRAM_APPLICATION_ID, PLL.PROGRAM_UPDATE_DATE, PLL.RECEIVING_ROUTING_ID, PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.CLOSED_BY, PLL.LEAD_TIME, PLL.LEAD_TIME_UNIT, PLL.PRICE_DISCOUNT, PLL.TERMS_ID, PLL.APPROVED_FLAG, PLL.CLOSED_FLAG, DECODE (PLL.CANCEL_FLAG, 'I', NULL, PLL.CANCEL_FLAG), PLL.CANCELLED_BY, PLL.CANCEL_DATE, PLL.CANCEL_REASON, NVL (PLL.FIRM_STATUS_LOOKUP_CODE, 'N'), PLL.FIRM_DATE, PLL.ENCUMBER_NOW, PLL.SOURCE_SHIPMENT_ID, PLL.SHIPMENT_TYPE, PLL.LINE_LOCATION_ID, PLL.LAST_UPDATE_DATE, PLL.LAST_UPDATED_BY, PLL.PO_HEADER_ID, PLL.LAST_UPDATE_LOGIN, PLL.CREATED_BY, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_RECEIVED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_ACCEPTED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_BILLED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_CANCELLED ), PLL.PO_RELEASE_ID, PLL.PROMISED_DATE, PLL.ENCUMBERED_FLAG, PLL.ENCUMBERED_DATE, PLL.FOB_LOOKUP_CODE, PLL.TAXABLE_FLAG, NULL, PLL.ATTRIBUTE_CATEGORY, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, RRH.ROUTING_NAME, HRL.LOCATION_CODE, HOUT.NAME, PLL2.SHIPMENT_NUM, POH.SEGMENT1, POH.TYPE_LOOKUP_CODE, TO_NUMBER(NULL), TO_CHAR(NULL), POH.AGENT_ID, POH.VENDOR_ID, POV.VENDOR_NAME, POH.VENDOR_SITE_ID, POVS.VENDOR_SITE_CODE, DECODE ( POVC.LAST_NAME, NULL, NULL, POVC.LAST_NAME || ', ' || POVC.FIRST_NAME ), POH.BILL_TO_LOCATION_ID, HRL2.LOCATION_CODE, POH.CURRENCY_CODE, NVL(POH.AUTHORIZATION_STATUS, 'INCOMPLETE'), POH.REVISION_NUM, PDTL.TYPE_NAME, POH.CREATION_DATE, POH.APPROVED_DATE, POH.REVISED_DATE, APT.NAME, POH.SHIP_VIA_LOOKUP_CODE, POH.RATE_TYPE, POH.RATE_DATE, POH.RATE, PLL.START_DATE, PLL.END_DATE, POH.BLANKET_TOTAL_AMOUNT, POH.NOTE_TO_RECEIVER, POH.CONFIRMING_ORDER_FLAG, POH.ACCEPTANCE_DUE_DATE, POL.LINE_NUM, POL.LINE_TYPE_ID, PLTT.LINE_TYPE, POL.ITEM_ID, POL.ITEM_REVISION, POL.ITEM_DESCRIPTION, POL.CATEGORY_ID, POL.FROM_HEADER_ID, POL.FROM_LINE_ID, POH2.QUOTE_VENDOR_QUOTE_NUMBER, POL.CONTRACT_NUM, POL.UNIT_MEAS_LOOKUP_CODE, POL.ALLOW_PRICE_OVERRIDE_FLAG, POL.NOT_TO_EXCEED_PRICE, POL.UN_NUMBER_ID, POUNT.UN_NUMBER, POL.HAZARD_CLASS_ID, POHCT.HAZARD_CLASS, POL.NOTE_TO_VENDOR, POL.UNORDERED_FLAG, POL.VENDOR_PRODUCT_NUM, POL.MIN_RELEASE_AMOUNT, NVL (PLTB.OUTSIDE_OPERATION_FLAG, 'N'), NVL(PLL.MATCH_OPTION,'PO'), PLL.TAX_CODE_ID, FT.TERRITORY_SHORT_NAME, PLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER, POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, PLL.SECONDARY_UNIT_OF_MEASURE, PLL.SECONDARY_QUANTITY, PLL.PREFERRED_GRADE, PLL.SECONDARY_QUANTITY_RECEIVED, PLL.SECONDARY_QUANTITY_ACCEPTED, PLL.SECONDARY_QUANTITY_REJECTED, PLL.SECONDARY_QUANTITY_CANCELLED, PLL.VMI_FLAG, POH.GLOBAL_AGREEMENT_FLAG, POH.ENABLE_ALL_SITES, POH.ORG_ID, POH.ORG_ID, POH.CONSIGNED_CONSUMPTION_FLAG, PLL.CONSIGNED_FLAG, POL.CONTRACT_ID, PLL.AMOUNT_RECEIVED, PLL.AMOUNT, PLL.AMOUNT_BILLED, PLL.AMOUNT_CANCELLED, PLL.AMOUNT_ACCEPTED, PLL.AMOUNT_REJECTED, PLL.DROP_SHIP_FLAG, PLL.SALES_ORDER_UPDATE_DATE, PLL.TRANSACTION_FLOW_HEADER_ID, POL.FROM_LINE_LOCATION_ID, POL.JOB_ID, PLTB.ORDER_TYPE_LOOKUP_CODE, PLTB.PURCHASE_BASIS, DECODE ( PLL.SHIPMENT_TYPE, 'BLANKET', (DECODE (PLL.MANUAL_PRICE_CHANGE_FLAG, 'Y', 'N', 'Y')) ), PLL.SECONDARY_QUANTITY_SHIPPED, PDT.SECURITY_LEVEL_CODE, POH.STYLE_ID FROM HR_LOCATIONS_ALL_TL HRL, HR_ALL_ORGANIZATION_UNITS_TL HOUT, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_VENDOR_CONTACTS POVC, HR_LOCATIONS_ALL_TL HRL2, PO_DOCUMENT_TYPES_ALL_B PDT, PO_DOCUMENT_TYPES_ALL_TL PDTL, PO_UN_NUMBERS_TL POUNT, PO_HAZARD_CLASSES_TL POHCT, AP_TERMS APT, PO_LINE_TYPES_B PLTB, PO_LINE_TYPES_TL PLTT, PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH2, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, FND_TERRITORIES_TL FT, PO_DOC_STYLE_HEADERS PDSH WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND NVL (POH.GLOBAL_AGREEMENT_FLAG, 'N') = 'N' AND PLL.PO_RELEASE_ID IS NULL AND PLL2.LINE_LOCATION_ID(+) = PLL.SOURCE_SHIPMENT_ID AND RRH.ROUTING_HEADER_ID(+) = PLL.RECEIVING_ROUTING_ID AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV ('LANG') AND POV.VENDOR_ID(+) = POH.VENDOR_ID AND POVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID AND POVC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_ID AND HRL2.LANGUAGE(+) = USERENV ('LANG') AND PDT.DOCUMENT_TYPE_CODE IN ('PO', 'PA') AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE AND PDTL.LANGUAGE = USERENV ('LANG') AND PDT.ORG_ID(+) = PLL.ORG_ID AND PDTL.ORG_ID(+) = PLL.ORG_ID AND APT.TERM_ID(+) = POH.TERMS_ID AND PLTB.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID AND PLTB.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+) AND PLTT.LANGUAGE(+) = USERENV ('LANG') AND POH2.PO_HEADER_ID(+) = POL.FROM_HEADER_ID AND POUNT.UN_NUMBER_ID(+) = POL.UN_NUMBER_ID AND POUNT.LANGUAGE(+) = USERENV ('LANG') AND POHCT.HAZARD_CLASS_ID(+) = POL.HAZARD_CLASS_ID AND POHCT.LANGUAGE(+) = USERENV ('LANG') AND PLL.COUNTRY_OF_ORIGIN_CODE = FT.TERRITORY_CODE(+) AND DECODE (FT.TERRITORY_CODE, NULL, '1', FT.LANGUAGE) = DECODE (FT.TERRITORY_CODE, NULL, '1', USERENV ('LANG')) AND HOUT.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID AND HOUT.LANGUAGE(+) = USERENV ('LANG') AND POH.STYLE_ID=PDSH.STYLE_ID AND NVL(PDSH.PROGRESS_PAYMENT_FLAG,'N') = 'N' AND ( POH.VENDOR_CONTACT_ID IS NULL OR POH.VENDOR_SITE_ID=POVC.VENDOR_SITE_ID ) UNION ALL SELECT PLL.ROWID, PLL.DAYS_EARLY_RECEIPT_ALLOWED, PLL.PO_LINE_ID, PLL.CREATION_DATE, PLL.QUANTITY, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_REJECTED ), PLL.SHIP_TO_LOCATION_ID, PLL.NEED_BY_DATE, PLL.LAST_ACCEPT_DATE, PLL.UNENCUMBERED_QUANTITY, PLL.FREIGHT_TERMS_LOOKUP_CODE, PLL.ESTIMATED_TAX_AMOUNT, PLL.PRICE_OVERRIDE, PLL.QTY_RCV_EXCEPTION_CODE, NVL (PLL.CLOSED_CODE, 'OPEN'), PLL.RECEIVE_CLOSE_TOLERANCE, PLL.PROGRAM_ID, PLL.GOVERNMENT_CONTEXT, PLL.USSGL_TRANSACTION_CODE, PLL.CLOSED_DATE, PLL.CLOSED_REASON, PLL.UNIT_OF_MEASURE_CLASS, PLL.SHIPMENT_NUM, PLL.DAYS_LATE_RECEIPT_ALLOWED, PLL.ENFORCE_SHIP_TO_LOCATION_CODE, PLL.INSPECTION_REQUIRED_FLAG, PLL.QTY_RCV_TOLERANCE, PLL.RECEIPT_REQUIRED_FLAG, DECODE ( PLL.RECEIPT_REQUIRED_FLAG, 'N', DECODE (PLL.INSPECTION_REQUIRED_FLAG, 'N', '2-Way', '2-Way'), 'Y', DECODE ( PLL.INSPECTION_REQUIRED_FLAG, 'N', '3-Way', 'Y', '4-Way', '3-Way' ) ), PLL.SHIP_TO_ORGANIZATION_ID, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG, PLL.RECEIPT_DAYS_EXCEPTION_CODE, PLL.INVOICE_CLOSE_TOLERANCE, PLL.REQUEST_ID, PLL.PROGRAM_APPLICATION_ID, PLL.PROGRAM_UPDATE_DATE, PLL.RECEIVING_ROUTING_ID, PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.CLOSED_BY, PLL.LEAD_TIME, PLL.LEAD_TIME_UNIT, PLL.PRICE_DISCOUNT, PLL.TERMS_ID, PLL.APPROVED_FLAG, PLL.CLOSED_FLAG, DECODE (PLL.CANCEL_FLAG, 'I', NULL, PLL.CANCEL_FLAG), PLL.CANCELLED_BY, PLL.CANCEL_DATE, PLL.CANCEL_REASON, NVL (PLL.FIRM_STATUS_LOOKUP_CODE, 'N'), PLL.FIRM_DATE, PLL.ENCUMBER_NOW, PLL.SOURCE_SHIPMENT_ID, PLL.SHIPMENT_TYPE, PLL.LINE_LOCATION_ID, PLL.LAST_UPDATE_DATE, PLL.LAST_UPDATED_BY, PLL.PO_HEADER_ID, PLL.LAST_UPDATE_LOGIN, PLL.CREATED_BY, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_RECEIVED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_ACCEPTED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_BILLED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_CANCELLED ), PLL.PO_RELEASE_ID, PLL.PROMISED_DATE, PLL.ENCUMBERED_FLAG, PLL.ENCUMBERED_DATE, PLL.FOB_LOOKUP_CODE, PLL.TAXABLE_FLAG, NULL, PLL.ATTRIBUTE_CATEGORY, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, RRH.ROUTING_NAME, HRL.LOCATION_CODE, HOUT.NAME, PLL2.SHIPMENT_NUM, POH.SEGMENT1, POH.TYPE_LOOKUP_CODE, POR.RELEASE_NUM, POR.RELEASE_TYPE, POR.AGENT_ID, POH.VENDOR_ID, POV.VENDOR_NAME, POH.VENDOR_SITE_ID, POVS.VENDOR_SITE_CODE, DECODE ( POVC.LAST_NAME, NULL, NULL, POVC.LAST_NAME || ', ' || POVC.FIRST_NAME ), POH.BILL_TO_LOCATION_ID, HRL2.LOCATION_CODE, POH.CURRENCY_CODE, NVL(POR.AUTHORIZATION_STATUS, 'INCOMPLETE'), POR.REVISION_NUM, PDTL.TYPE_NAME, POR.CREATION_DATE, POR.APPROVED_DATE, POR.REVISED_DATE, APT.NAME, POH.SHIP_VIA_LOOKUP_CODE, POH.RATE_TYPE, POH.RATE_DATE, POH.RATE, PLL.START_DATE, PLL.END_DATE, POH.BLANKET_TOTAL_AMOUNT, POH.NOTE_TO_RECEIVER, POH.CONFIRMING_ORDER_FLAG, POH.ACCEPTANCE_DUE_DATE, POL.LINE_NUM, POL.LINE_TYPE_ID, PLTT.LINE_TYPE, POL.ITEM_ID, POL.ITEM_REVISION, POL.ITEM_DESCRIPTION, POL.CATEGORY_ID, POL.FROM_HEADER_ID, POL.FROM_LINE_ID, POH2.QUOTE_VENDOR_QUOTE_NUMBER, POL.CONTRACT_NUM, POL.UNIT_MEAS_LOOKUP_CODE, POL.ALLOW_PRICE_OVERRIDE_FLAG, POL.NOT_TO_EXCEED_PRICE, POL.UN_NUMBER_ID, POUNT.UN_NUMBER, POL.HAZARD_CLASS_ID, POHCT.HAZARD_CLASS, POL.NOTE_TO_VENDOR, POL.UNORDERED_FLAG, POL.VENDOR_PRODUCT_NUM, POL.MIN_RELEASE_AMOUNT, NVL (PLTB.OUTSIDE_OPERATION_FLAG, 'N'), NVL (PLL.MATCH_OPTION, 'PO'), PLL.TAX_CODE_ID, FT.TERRITORY_SHORT_NAME, PLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER, POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, PLL.SECONDARY_UNIT_OF_MEASURE, PLL.SECONDARY_QUANTITY, PLL.PREFERRED_GRADE, PLL.SECONDARY_QUANTITY_RECEIVED, PLL.SECONDARY_QUANTITY_ACCEPTED, PLL.SECONDARY_QUANTITY_REJECTED, PLL.SECONDARY_QUANTITY_CANCELLED, PLL.VMI_FLAG, POH.GLOBAL_AGREEMENT_FLAG, POH.ENABLE_ALL_SITES, POH.ORG_ID, POH.ORG_ID, POR.CONSIGNED_CONSUMPTION_FLAG, PLL.CONSIGNED_FLAG, POL.CONTRACT_ID, PLL.AMOUNT_RECEIVED, PLL.AMOUNT, PLL.AMOUNT_BILLED, PLL.AMOUNT_CANCELLED, PLL.AMOUNT_ACCEPTED, PLL.AMOUNT_REJECTED, PLL.DROP_SHIP_FLAG, PLL.SALES_ORDER_UPDATE_DATE, PLL.TRANSACTION_FLOW_HEADER_ID, POL.FROM_LINE_LOCATION_ID, POL.JOB_ID, PLTB.ORDER_TYPE_LOOKUP_CODE, PLTB.PURCHASE_BASIS, DECODE ( PLL.SHIPMENT_TYPE, 'BLANKET', (DECODE (PLL.MANUAL_PRICE_CHANGE_FLAG, 'Y', 'N', 'Y')) ), PLL.SECONDARY_QUANTITY_SHIPPED, PDT.SECURITY_LEVEL_CODE, POH.STYLE_ID FROM HR_LOCATIONS_ALL_TL HRL, HR_ALL_ORGANIZATION_UNITS_TL HOUT, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_VENDOR_CONTACTS POVC, HR_LOCATIONS_ALL_TL HRL2, PO_DOCUMENT_TYPES_ALL_B PDT, PO_DOCUMENT_TYPES_ALL_TL PDTL, PO_UN_NUMBERS_TL POUNT, PO_HAZARD_CLASSES_TL POHCT, AP_TERMS APT, PO_LINE_TYPES_B PLTB, PO_LINE_TYPES_TL PLTT, PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH2, PO_RELEASES_ALL POR, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, FND_TERRITORIES_TL FT, PO_DOC_STYLE_HEADERS PDSH WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND NVL (POH.GLOBAL_AGREEMENT_FLAG, 'N') = 'N' AND PLL.PO_RELEASE_ID IS NOT NULL AND POR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID AND PLL2.LINE_LOCATION_ID(+) = PLL.SOURCE_SHIPMENT_ID AND RRH.ROUTING_HEADER_ID(+) = PLL.RECEIVING_ROUTING_ID AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV ('LANG') AND POV.VENDOR_ID(+) = POH.VENDOR_ID AND POVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID AND POVC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_ID AND HRL2.LANGUAGE(+) = USERENV ('LANG') AND PDT.DOCUMENT_TYPE_CODE = 'RELEASE' AND PDT.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE AND PDTL.LANGUAGE = USERENV ('LANG') AND PDT.ORG_ID(+) = PLL.ORG_ID AND PDTL.ORG_ID(+) = PLL.ORG_ID AND APT.TERM_ID(+) = POH.TERMS_ID AND PLTB.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID AND PLTB.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+) AND PLTT.LANGUAGE(+) = USERENV ('LANG') AND POH2.PO_HEADER_ID(+) = POL.FROM_HEADER_ID AND POUNT.UN_NUMBER_ID(+) = POL.UN_NUMBER_ID AND POUNT.LANGUAGE(+) = USERENV ('LANG') AND POHCT.HAZARD_CLASS_ID(+) = POL.HAZARD_CLASS_ID AND POHCT.LANGUAGE(+) = USERENV ('LANG') AND PLL.COUNTRY_OF_ORIGIN_CODE = FT.TERRITORY_CODE(+) AND DECODE (FT.TERRITORY_CODE, NULL, '1', FT.LANGUAGE) = DECODE (FT.TERRITORY_CODE, NULL, '1', USERENV ('LANG')) AND HOUT.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID AND HOUT.LANGUAGE(+) = USERENV ('LANG') AND POH.STYLE_ID=PDSH.STYLE_ID AND NVL(PDSH.PROGRESS_PAYMENT_FLAG,'N') = 'N' AND ( POH.VENDOR_CONTACT_ID IS NULL OR POH.VENDOR_SITE_ID=POVC.VENDOR_SITE_ID ) UNION ALL SELECT PLL.ROWID, PLL.DAYS_EARLY_RECEIPT_ALLOWED, PLL.PO_LINE_ID, PLL.CREATION_DATE, PLL.QUANTITY, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_REJECTED ), PLL.SHIP_TO_LOCATION_ID, PLL.NEED_BY_DATE, PLL.LAST_ACCEPT_DATE, PLL.UNENCUMBERED_QUANTITY, PLL.FREIGHT_TERMS_LOOKUP_CODE, PLL.ESTIMATED_TAX_AMOUNT, PLL.PRICE_OVERRIDE, PLL.QTY_RCV_EXCEPTION_CODE, NVL (PLL.CLOSED_CODE, 'OPEN'), PLL.RECEIVE_CLOSE_TOLERANCE, PLL.PROGRAM_ID, PLL.GOVERNMENT_CONTEXT, PLL.USSGL_TRANSACTION_CODE, PLL.CLOSED_DATE, PLL.CLOSED_REASON, PLL.UNIT_OF_MEASURE_CLASS, PLL.SHIPMENT_NUM, PLL.DAYS_LATE_RECEIPT_ALLOWED, PLL.ENFORCE_SHIP_TO_LOCATION_CODE, PLL.INSPECTION_REQUIRED_FLAG, PLL.QTY_RCV_TOLERANCE, PLL.RECEIPT_REQUIRED_FLAG, DECODE ( PLL.RECEIPT_REQUIRED_FLAG, 'N', DECODE (PLL.INSPECTION_REQUIRED_FLAG, 'N', '2-Way', '2-Way'), 'Y', DECODE ( PLL.INSPECTION_REQUIRED_FLAG, 'N', '3-Way', 'Y', '4-Way', '3-Way' ) ), PLL.SHIP_TO_ORGANIZATION_ID, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG, PLL.RECEIPT_DAYS_EXCEPTION_CODE, PLL.INVOICE_CLOSE_TOLERANCE, PLL.REQUEST_ID, PLL.PROGRAM_APPLICATION_ID, PLL.PROGRAM_UPDATE_DATE, PLL.RECEIVING_ROUTING_ID, PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.CLOSED_BY, PLL.LEAD_TIME, PLL.LEAD_TIME_UNIT, PLL.PRICE_DISCOUNT, PLL.TERMS_ID, PLL.APPROVED_FLAG, PLL.CLOSED_FLAG, DECODE (PLL.CANCEL_FLAG, 'I', NULL, PLL.CANCEL_FLAG), PLL.CANCELLED_BY, PLL.CANCEL_DATE, PLL.CANCEL_REASON, NVL (PLL.FIRM_STATUS_LOOKUP_CODE, 'N'), PLL.FIRM_DATE, PLL.ENCUMBER_NOW, PLL.SOURCE_SHIPMENT_ID, PLL.SHIPMENT_TYPE, PLL.LINE_LOCATION_ID, PLL.LAST_UPDATE_DATE, PLL.LAST_UPDATED_BY, PLL.PO_HEADER_ID, PLL.LAST_UPDATE_LOGIN, PLL.CREATED_BY, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_RECEIVED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_ACCEPTED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_BILLED ), DECODE ( POL.ORDER_TYPE_LOOKUP_CODE, 'RATE', TO_NUMBER (NULL), 'FIXED PRICE', TO_NUMBER (NULL), PLL.QUANTITY_CANCELLED ), PLL.PO_RELEASE_ID, PLL.PROMISED_DATE, PLL.ENCUMBERED_FLAG, PLL.ENCUMBERED_DATE, PLL.FOB_LOOKUP_CODE, PLL.TAXABLE_FLAG, NULL, PLL.ATTRIBUTE_CATEGORY, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, RRH.ROUTING_NAME, HRL.LOCATION_CODE, HOUT.NAME, TO_NUMBER (NULL), POH.SEGMENT1, POH.TYPE_LOOKUP_CODE, TO_NUMBER (NULL), NULL, POH.AGENT_ID, POH.VENDOR_ID, POV.VENDOR_NAME, POH.VENDOR_SITE_ID, POVS.VENDOR_SITE_CODE, DECODE ( POVC.LAST_NAME, NULL, NULL, POVC.LAST_NAME || ', ' || POVC.FIRST_NAME ), POH.BILL_TO_LOCATION_ID, HRL2.LOCATION_CODE, POH.CURRENCY_CODE, NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE'), POH.REVISION_NUM, PDTL.TYPE_NAME, POH.CREATION_DATE, POH.APPROVED_DATE, POH.REVISED_DATE, APT.NAME, POH.SHIP_VIA_LOOKUP_CODE, POH.RATE_TYPE, POH.RATE_DATE, POH.RATE, PLL.START_DATE, PLL.END_DATE, POH.BLANKET_TOTAL_AMOUNT, POH.NOTE_TO_RECEIVER, POH.CONFIRMING_ORDER_FLAG, POH.ACCEPTANCE_DUE_DATE, POL.LINE_NUM, POL.LINE_TYPE_ID, PLTT.LINE_TYPE, POL.ITEM_ID, POL.ITEM_REVISION, POL.ITEM_DESCRIPTION, POL.CATEGORY_ID, POL.FROM_HEADER_ID, POL.FROM_LINE_ID, NULL, POL.CONTRACT_NUM, POL.UNIT_MEAS_LOOKUP_CODE, POL.ALLOW_PRICE_OVERRIDE_FLAG, POL.NOT_TO_EXCEED_PRICE, POL.UN_NUMBER_ID, POUNT.UN_NUMBER, POL.HAZARD_CLASS_ID, POHCT.HAZARD_CLASS, POL.NOTE_TO_VENDOR, POL.UNORDERED_FLAG, POL.VENDOR_PRODUCT_NUM, POL.MIN_RELEASE_AMOUNT, NVL (PLTB.OUTSIDE_OPERATION_FLAG, 'N'), NVL (PLL.MATCH_OPTION, 'PO'), PLL.TAX_CODE_ID, FT.TERRITORY_SHORT_NAME, PLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER, POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, PLL.SECONDARY_UNIT_OF_MEASURE, PLL.SECONDARY_QUANTITY, PLL.PREFERRED_GRADE, PLL.SECONDARY_QUANTITY_RECEIVED, PLL.SECONDARY_QUANTITY_ACCEPTED, PLL.SECONDARY_QUANTITY_REJECTED, PLL.SECONDARY_QUANTITY_CANCELLED, PLL.VMI_FLAG, POH.GLOBAL_AGREEMENT_FLAG, POH.ENABLE_ALL_SITES, POH.ORG_ID, POH.ORG_ID, POH.CONSIGNED_CONSUMPTION_FLAG, PLL.CONSIGNED_FLAG, POL.CONTRACT_ID, PLL.AMOUNT_RECEIVED, PLL.AMOUNT, PLL.AMOUNT_BILLED, PLL.AMOUNT_CANCELLED, PLL.AMOUNT_ACCEPTED, PLL.AMOUNT_REJECTED, PLL.DROP_SHIP_FLAG, PLL.SALES_ORDER_UPDATE_DATE, PLL.TRANSACTION_FLOW_HEADER_ID, POL.FROM_LINE_LOCATION_ID, POL.JOB_ID, PLTB.ORDER_TYPE_LOOKUP_CODE, PLTB.PURCHASE_BASIS, NULL/** AUTO_PRICED_FLAG **/, PLL.SECONDARY_QUANTITY_SHIPPED, PDT.SECURITY_LEVEL_CODE, POH.STYLE_ID FROM HR_LOCATIONS_ALL_TL HRL, HR_ORG_UNITS_NO_JOIN HROU, HR_ALL_ORGANIZATION_UNITS_TL HOUT, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_VENDOR_CONTACTS POVC, HR_LOCATIONS_ALL_TL HRL2, PO_DOCUMENT_TYPES_ALL_B PDT, PO_DOCUMENT_TYPES_ALL_TL PDTL, PO_UN_NUMBERS_TL POUNT, PO_HAZARD_CLASSES_TL POHCT, AP_TERMS APT, PO_LINE_TYPES_B PLTB, PO_LINE_TYPES_TL PLTT, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, FND_TERRITORIES_TL FT, PO_DOC_STYLE_HEADERS PDSH WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND NVL (POH.GLOBAL_AGREEMENT_FLAG, 'N') = 'Y' AND ( (POH.ORG_ID IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)) OR (EXISTS (SELECT 'ENABLED IN CURRENT OU' FROM PO_GA_ORG_ASSIGNMENTS POGA WHERE POH.PO_HEADER_ID = POGA.PO_HEADER_ID AND POGA.ENABLED_FLAG ='Y' AND ( (POGA.ORGANIZATION_ID IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)) OR (POGA.PURCHASING_ORG_ID IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)) ) ) AND POH.AUTHORIZATION_STATUS = 'APPROVED' AND NVL(POH.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' AND NVL(POH.CANCEL_FLAG,'N') = 'N' AND NVL(POH.FROZEN_FLAG,'N') = 'N' AND (TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.START_DATE), TRUNC(SYSDATE)-1) AND NVL(TRUNC(POH.END_DATE), TRUNC(SYSDATE)+1) OR (TRUNC(POH.START_DATE) IS NOT NULL AND SYSDATE <= POH.START_DATE )) AND TRUNC(SYSDATE) <= NVL(TRUNC(POL.EXPIRATION_DATE), TRUNC(SYSDATE)) AND NVL(POL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' AND NVL(POL.CANCEL_FLAG,'N') = 'N' AND NVL(PLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' AND NVL(PLL.CANCEL_FLAG,'N') = 'N') ) AND RRH.ROUTING_HEADER_ID(+) = PLL.RECEIVING_ROUTING_ID AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV ('LANG') AND HROU.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID AND POV.VENDOR_ID(+) = POH.VENDOR_ID AND POVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID AND POVC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_ID AND HRL2.LANGUAGE(+) = USERENV ('LANG') AND PDT.DOCUMENT_TYPE_CODE = 'PA' AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE AND PDTL.LANGUAGE = USERENV ('LANG') AND PDT.ORG_ID = POH.ORG_ID AND PDTL.ORG_ID = PDT.ORG_ID AND APT.TERM_ID(+) = POH.TERMS_ID AND PLTB.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID AND PLTB.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+) AND PLTT.LANGUAGE(+) = USERENV ('LANG') AND POUNT.UN_NUMBER_ID(+) = POL.UN_NUMBER_ID AND POUNT.LANGUAGE(+) = USERENV ('LANG') AND POHCT.HAZARD_CLASS_ID(+) = POL.HAZARD_CLASS_ID AND POHCT.LANGUAGE(+) = USERENV ('LANG') AND PLL.COUNTRY_OF_ORIGIN_CODE = FT.TERRITORY_CODE(+) AND DECODE (FT.TERRITORY_CODE, NULL, '1', FT.LANGUAGE) = DECODE (FT.TERRITORY_CODE, NULL, '1', USERENV ('LANG')) AND HROU.ORGANIZATION_ID = HOUT.ORGANIZATION_ID(+) AND DECODE (HOUT.ORGANIZATION_ID, NULL, '1', HOUT.LANGUAGE) = DECODE (HOUT.ORGANIZATION_ID, NULL, '1', USERENV ('LANG')) AND POH.STYLE_ID=PDSH.STYLE_ID AND NVL(PDSH.PROGRESS_PAYMENT_FLAG,'N') = 'N' AND ( POH.VENDOR_CONTACT_ID IS NULL OR POH.VENDOR_SITE_ID=POVC.VENDOR_SITE_ID )

View Text - HTML Formatted

SELECT PLL.ROWID
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, PLL.QUANTITY
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_REJECTED )
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, PLL.UNENCUMBERED_QUANTITY
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.ESTIMATED_TAX_AMOUNT
, PLL.PRICE_OVERRIDE
, PLL.QTY_RCV_EXCEPTION_CODE
, NVL (PLL.CLOSED_CODE
, 'OPEN')
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.PROGRAM_ID
, PLL.GOVERNMENT_CONTEXT
, PLL.USSGL_TRANSACTION_CODE
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.UNIT_OF_MEASURE_CLASS
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.QTY_RCV_TOLERANCE
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE ( PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, '2-WAY')
, 'Y'
, DECODE ( PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, '3-WAY' ) )
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_UPDATE_DATE
, PLL.RECEIVING_ROUTING_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.CLOSED_BY
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, PLL.TERMS_ID
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE (PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, NVL (PLL.FIRM_STATUS_LOOKUP_CODE
, 'N')
, PLL.FIRM_DATE
, PLL.ENCUMBER_NOW
, PLL.SOURCE_SHIPMENT_ID
, PLL.SHIPMENT_TYPE
, PLL.LINE_LOCATION_ID
, PLL.LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.PO_HEADER_ID
, PLL.LAST_UPDATE_LOGIN
, PLL.CREATED_BY
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_RECEIVED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_ACCEPTED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_BILLED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_CANCELLED )
, PLL.PO_RELEASE_ID
, PLL.PROMISED_DATE
, PLL.ENCUMBERED_FLAG
, PLL.ENCUMBERED_DATE
, PLL.FOB_LOOKUP_CODE
, PLL.TAXABLE_FLAG
, NULL
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, RRH.ROUTING_NAME
, HRL.LOCATION_CODE
, HOUT.NAME
, PLL2.SHIPMENT_NUM
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, POH.AGENT_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE ( POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME )
, POH.BILL_TO_LOCATION_ID
, HRL2.LOCATION_CODE
, POH.CURRENCY_CODE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.REVISION_NUM
, PDTL.TYPE_NAME
, POH.CREATION_DATE
, POH.APPROVED_DATE
, POH.REVISED_DATE
, APT.NAME
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, PLL.START_DATE
, PLL.END_DATE
, POH.BLANKET_TOTAL_AMOUNT
, POH.NOTE_TO_RECEIVER
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.LINE_TYPE_ID
, PLTT.LINE_TYPE
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.FROM_HEADER_ID
, POL.FROM_LINE_ID
, POH2.QUOTE_VENDOR_QUOTE_NUMBER
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.UN_NUMBER_ID
, POUNT.UN_NUMBER
, POL.HAZARD_CLASS_ID
, POHCT.HAZARD_CLASS
, POL.NOTE_TO_VENDOR
, POL.UNORDERED_FLAG
, POL.VENDOR_PRODUCT_NUM
, POL.MIN_RELEASE_AMOUNT
, NVL (PLTB.OUTSIDE_OPERATION_FLAG
, 'N')
, NVL(PLL.MATCH_OPTION
, 'PO')
, PLL.TAX_CODE_ID
, FT.TERRITORY_SHORT_NAME
, PLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.VMI_FLAG
, POH.GLOBAL_AGREEMENT_FLAG
, POH.ENABLE_ALL_SITES
, POH.ORG_ID
, POH.ORG_ID
, POH.CONSIGNED_CONSUMPTION_FLAG
, PLL.CONSIGNED_FLAG
, POL.CONTRACT_ID
, PLL.AMOUNT_RECEIVED
, PLL.AMOUNT
, PLL.AMOUNT_BILLED
, PLL.AMOUNT_CANCELLED
, PLL.AMOUNT_ACCEPTED
, PLL.AMOUNT_REJECTED
, PLL.DROP_SHIP_FLAG
, PLL.SALES_ORDER_UPDATE_DATE
, PLL.TRANSACTION_FLOW_HEADER_ID
, POL.FROM_LINE_LOCATION_ID
, POL.JOB_ID
, PLTB.ORDER_TYPE_LOOKUP_CODE
, PLTB.PURCHASE_BASIS
, DECODE ( PLL.SHIPMENT_TYPE
, 'BLANKET'
, (DECODE (PLL.MANUAL_PRICE_CHANGE_FLAG
, 'Y'
, 'N'
, 'Y')) )
, PLL.SECONDARY_QUANTITY_SHIPPED
, PDT.SECURITY_LEVEL_CODE
, POH.STYLE_ID
FROM HR_LOCATIONS_ALL_TL HRL
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, HR_LOCATIONS_ALL_TL HRL2
, PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_UN_NUMBERS_TL POUNT
, PO_HAZARD_CLASSES_TL POHCT
, AP_TERMS APT
, PO_LINE_TYPES_B PLTB
, PO_LINE_TYPES_TL PLTT
, PO_LINE_LOCATIONS_ALL PLL2
, PO_HEADERS_ALL POH2
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, FND_TERRITORIES_TL FT
, PO_DOC_STYLE_HEADERS PDSH
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND NVL (POH.GLOBAL_AGREEMENT_FLAG
, 'N') = 'N'
AND PLL.PO_RELEASE_ID IS NULL
AND PLL2.LINE_LOCATION_ID(+) = PLL.SOURCE_SHIPMENT_ID
AND RRH.ROUTING_HEADER_ID(+) = PLL.RECEIVING_ROUTING_ID
AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV ('LANG')
AND POV.VENDOR_ID(+) = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID
AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LANGUAGE(+) = USERENV ('LANG')
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE
AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE
AND PDTL.LANGUAGE = USERENV ('LANG')
AND PDT.ORG_ID(+) = PLL.ORG_ID
AND PDTL.ORG_ID(+) = PLL.ORG_ID
AND APT.TERM_ID(+) = POH.TERMS_ID
AND PLTB.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID
AND PLTB.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+)
AND PLTT.LANGUAGE(+) = USERENV ('LANG')
AND POH2.PO_HEADER_ID(+) = POL.FROM_HEADER_ID
AND POUNT.UN_NUMBER_ID(+) = POL.UN_NUMBER_ID
AND POUNT.LANGUAGE(+) = USERENV ('LANG')
AND POHCT.HAZARD_CLASS_ID(+) = POL.HAZARD_CLASS_ID
AND POHCT.LANGUAGE(+) = USERENV ('LANG')
AND PLL.COUNTRY_OF_ORIGIN_CODE = FT.TERRITORY_CODE(+)
AND DECODE (FT.TERRITORY_CODE
, NULL
, '1'
, FT.LANGUAGE) = DECODE (FT.TERRITORY_CODE
, NULL
, '1'
, USERENV ('LANG'))
AND HOUT.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HOUT.LANGUAGE(+) = USERENV ('LANG')
AND POH.STYLE_ID=PDSH.STYLE_ID
AND NVL(PDSH.PROGRESS_PAYMENT_FLAG
, 'N') = 'N'
AND ( POH.VENDOR_CONTACT_ID IS NULL OR POH.VENDOR_SITE_ID=POVC.VENDOR_SITE_ID ) UNION ALL SELECT PLL.ROWID
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, PLL.QUANTITY
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_REJECTED )
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, PLL.UNENCUMBERED_QUANTITY
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.ESTIMATED_TAX_AMOUNT
, PLL.PRICE_OVERRIDE
, PLL.QTY_RCV_EXCEPTION_CODE
, NVL (PLL.CLOSED_CODE
, 'OPEN')
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.PROGRAM_ID
, PLL.GOVERNMENT_CONTEXT
, PLL.USSGL_TRANSACTION_CODE
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.UNIT_OF_MEASURE_CLASS
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.QTY_RCV_TOLERANCE
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE ( PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, '2-WAY')
, 'Y'
, DECODE ( PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, '3-WAY' ) )
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_UPDATE_DATE
, PLL.RECEIVING_ROUTING_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.CLOSED_BY
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, PLL.TERMS_ID
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE (PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, NVL (PLL.FIRM_STATUS_LOOKUP_CODE
, 'N')
, PLL.FIRM_DATE
, PLL.ENCUMBER_NOW
, PLL.SOURCE_SHIPMENT_ID
, PLL.SHIPMENT_TYPE
, PLL.LINE_LOCATION_ID
, PLL.LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.PO_HEADER_ID
, PLL.LAST_UPDATE_LOGIN
, PLL.CREATED_BY
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_RECEIVED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_ACCEPTED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_BILLED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_CANCELLED )
, PLL.PO_RELEASE_ID
, PLL.PROMISED_DATE
, PLL.ENCUMBERED_FLAG
, PLL.ENCUMBERED_DATE
, PLL.FOB_LOOKUP_CODE
, PLL.TAXABLE_FLAG
, NULL
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, RRH.ROUTING_NAME
, HRL.LOCATION_CODE
, HOUT.NAME
, PLL2.SHIPMENT_NUM
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, POR.AGENT_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE ( POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME )
, POH.BILL_TO_LOCATION_ID
, HRL2.LOCATION_CODE
, POH.CURRENCY_CODE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POR.REVISION_NUM
, PDTL.TYPE_NAME
, POR.CREATION_DATE
, POR.APPROVED_DATE
, POR.REVISED_DATE
, APT.NAME
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, PLL.START_DATE
, PLL.END_DATE
, POH.BLANKET_TOTAL_AMOUNT
, POH.NOTE_TO_RECEIVER
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.LINE_TYPE_ID
, PLTT.LINE_TYPE
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.FROM_HEADER_ID
, POL.FROM_LINE_ID
, POH2.QUOTE_VENDOR_QUOTE_NUMBER
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.UN_NUMBER_ID
, POUNT.UN_NUMBER
, POL.HAZARD_CLASS_ID
, POHCT.HAZARD_CLASS
, POL.NOTE_TO_VENDOR
, POL.UNORDERED_FLAG
, POL.VENDOR_PRODUCT_NUM
, POL.MIN_RELEASE_AMOUNT
, NVL (PLTB.OUTSIDE_OPERATION_FLAG
, 'N')
, NVL (PLL.MATCH_OPTION
, 'PO')
, PLL.TAX_CODE_ID
, FT.TERRITORY_SHORT_NAME
, PLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.VMI_FLAG
, POH.GLOBAL_AGREEMENT_FLAG
, POH.ENABLE_ALL_SITES
, POH.ORG_ID
, POH.ORG_ID
, POR.CONSIGNED_CONSUMPTION_FLAG
, PLL.CONSIGNED_FLAG
, POL.CONTRACT_ID
, PLL.AMOUNT_RECEIVED
, PLL.AMOUNT
, PLL.AMOUNT_BILLED
, PLL.AMOUNT_CANCELLED
, PLL.AMOUNT_ACCEPTED
, PLL.AMOUNT_REJECTED
, PLL.DROP_SHIP_FLAG
, PLL.SALES_ORDER_UPDATE_DATE
, PLL.TRANSACTION_FLOW_HEADER_ID
, POL.FROM_LINE_LOCATION_ID
, POL.JOB_ID
, PLTB.ORDER_TYPE_LOOKUP_CODE
, PLTB.PURCHASE_BASIS
, DECODE ( PLL.SHIPMENT_TYPE
, 'BLANKET'
, (DECODE (PLL.MANUAL_PRICE_CHANGE_FLAG
, 'Y'
, 'N'
, 'Y')) )
, PLL.SECONDARY_QUANTITY_SHIPPED
, PDT.SECURITY_LEVEL_CODE
, POH.STYLE_ID
FROM HR_LOCATIONS_ALL_TL HRL
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, HR_LOCATIONS_ALL_TL HRL2
, PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_UN_NUMBERS_TL POUNT
, PO_HAZARD_CLASSES_TL POHCT
, AP_TERMS APT
, PO_LINE_TYPES_B PLTB
, PO_LINE_TYPES_TL PLTT
, PO_LINE_LOCATIONS_ALL PLL2
, PO_HEADERS_ALL POH2
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, FND_TERRITORIES_TL FT
, PO_DOC_STYLE_HEADERS PDSH
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND NVL (POH.GLOBAL_AGREEMENT_FLAG
, 'N') = 'N'
AND PLL.PO_RELEASE_ID IS NOT NULL
AND POR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID
AND PLL2.LINE_LOCATION_ID(+) = PLL.SOURCE_SHIPMENT_ID
AND RRH.ROUTING_HEADER_ID(+) = PLL.RECEIVING_ROUTING_ID
AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV ('LANG')
AND POV.VENDOR_ID(+) = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID
AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LANGUAGE(+) = USERENV ('LANG')
AND PDT.DOCUMENT_TYPE_CODE = 'RELEASE'
AND PDT.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE
AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE
AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE
AND PDTL.LANGUAGE = USERENV ('LANG')
AND PDT.ORG_ID(+) = PLL.ORG_ID
AND PDTL.ORG_ID(+) = PLL.ORG_ID
AND APT.TERM_ID(+) = POH.TERMS_ID
AND PLTB.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID
AND PLTB.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+)
AND PLTT.LANGUAGE(+) = USERENV ('LANG')
AND POH2.PO_HEADER_ID(+) = POL.FROM_HEADER_ID
AND POUNT.UN_NUMBER_ID(+) = POL.UN_NUMBER_ID
AND POUNT.LANGUAGE(+) = USERENV ('LANG')
AND POHCT.HAZARD_CLASS_ID(+) = POL.HAZARD_CLASS_ID
AND POHCT.LANGUAGE(+) = USERENV ('LANG')
AND PLL.COUNTRY_OF_ORIGIN_CODE = FT.TERRITORY_CODE(+)
AND DECODE (FT.TERRITORY_CODE
, NULL
, '1'
, FT.LANGUAGE) = DECODE (FT.TERRITORY_CODE
, NULL
, '1'
, USERENV ('LANG'))
AND HOUT.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HOUT.LANGUAGE(+) = USERENV ('LANG')
AND POH.STYLE_ID=PDSH.STYLE_ID
AND NVL(PDSH.PROGRESS_PAYMENT_FLAG
, 'N') = 'N'
AND ( POH.VENDOR_CONTACT_ID IS NULL OR POH.VENDOR_SITE_ID=POVC.VENDOR_SITE_ID ) UNION ALL SELECT PLL.ROWID
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, PLL.QUANTITY
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_REJECTED )
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, PLL.UNENCUMBERED_QUANTITY
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.ESTIMATED_TAX_AMOUNT
, PLL.PRICE_OVERRIDE
, PLL.QTY_RCV_EXCEPTION_CODE
, NVL (PLL.CLOSED_CODE
, 'OPEN')
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.PROGRAM_ID
, PLL.GOVERNMENT_CONTEXT
, PLL.USSGL_TRANSACTION_CODE
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.UNIT_OF_MEASURE_CLASS
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.QTY_RCV_TOLERANCE
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE ( PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, '2-WAY')
, 'Y'
, DECODE ( PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, '3-WAY' ) )
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_UPDATE_DATE
, PLL.RECEIVING_ROUTING_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.CLOSED_BY
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, PLL.TERMS_ID
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE (PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, NVL (PLL.FIRM_STATUS_LOOKUP_CODE
, 'N')
, PLL.FIRM_DATE
, PLL.ENCUMBER_NOW
, PLL.SOURCE_SHIPMENT_ID
, PLL.SHIPMENT_TYPE
, PLL.LINE_LOCATION_ID
, PLL.LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.PO_HEADER_ID
, PLL.LAST_UPDATE_LOGIN
, PLL.CREATED_BY
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_RECEIVED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_ACCEPTED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_BILLED )
, DECODE ( POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, TO_NUMBER (NULL)
, 'FIXED PRICE'
, TO_NUMBER (NULL)
, PLL.QUANTITY_CANCELLED )
, PLL.PO_RELEASE_ID
, PLL.PROMISED_DATE
, PLL.ENCUMBERED_FLAG
, PLL.ENCUMBERED_DATE
, PLL.FOB_LOOKUP_CODE
, PLL.TAXABLE_FLAG
, NULL
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, RRH.ROUTING_NAME
, HRL.LOCATION_CODE
, HOUT.NAME
, TO_NUMBER (NULL)
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, TO_NUMBER (NULL)
, NULL
, POH.AGENT_ID
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE ( POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME )
, POH.BILL_TO_LOCATION_ID
, HRL2.LOCATION_CODE
, POH.CURRENCY_CODE
, NVL (POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.REVISION_NUM
, PDTL.TYPE_NAME
, POH.CREATION_DATE
, POH.APPROVED_DATE
, POH.REVISED_DATE
, APT.NAME
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, PLL.START_DATE
, PLL.END_DATE
, POH.BLANKET_TOTAL_AMOUNT
, POH.NOTE_TO_RECEIVER
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.LINE_TYPE_ID
, PLTT.LINE_TYPE
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.FROM_HEADER_ID
, POL.FROM_LINE_ID
, NULL
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.UN_NUMBER_ID
, POUNT.UN_NUMBER
, POL.HAZARD_CLASS_ID
, POHCT.HAZARD_CLASS
, POL.NOTE_TO_VENDOR
, POL.UNORDERED_FLAG
, POL.VENDOR_PRODUCT_NUM
, POL.MIN_RELEASE_AMOUNT
, NVL (PLTB.OUTSIDE_OPERATION_FLAG
, 'N')
, NVL (PLL.MATCH_OPTION
, 'PO')
, PLL.TAX_CODE_ID
, FT.TERRITORY_SHORT_NAME
, PLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.VMI_FLAG
, POH.GLOBAL_AGREEMENT_FLAG
, POH.ENABLE_ALL_SITES
, POH.ORG_ID
, POH.ORG_ID
, POH.CONSIGNED_CONSUMPTION_FLAG
, PLL.CONSIGNED_FLAG
, POL.CONTRACT_ID
, PLL.AMOUNT_RECEIVED
, PLL.AMOUNT
, PLL.AMOUNT_BILLED
, PLL.AMOUNT_CANCELLED
, PLL.AMOUNT_ACCEPTED
, PLL.AMOUNT_REJECTED
, PLL.DROP_SHIP_FLAG
, PLL.SALES_ORDER_UPDATE_DATE
, PLL.TRANSACTION_FLOW_HEADER_ID
, POL.FROM_LINE_LOCATION_ID
, POL.JOB_ID
, PLTB.ORDER_TYPE_LOOKUP_CODE
, PLTB.PURCHASE_BASIS
, NULL/** AUTO_PRICED_FLAG **/
, PLL.SECONDARY_QUANTITY_SHIPPED
, PDT.SECURITY_LEVEL_CODE
, POH.STYLE_ID
FROM HR_LOCATIONS_ALL_TL HRL
, HR_ORG_UNITS_NO_JOIN HROU
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, HR_LOCATIONS_ALL_TL HRL2
, PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_UN_NUMBERS_TL POUNT
, PO_HAZARD_CLASSES_TL POHCT
, AP_TERMS APT
, PO_LINE_TYPES_B PLTB
, PO_LINE_TYPES_TL PLTT
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, FND_TERRITORIES_TL FT
, PO_DOC_STYLE_HEADERS PDSH
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND NVL (POH.GLOBAL_AGREEMENT_FLAG
, 'N') = 'Y'
AND ( (POH.ORG_ID IN (SELECT ORG_ID
FROM PO_SYSTEM_PARAMETERS)) OR (EXISTS (SELECT 'ENABLED IN CURRENT OU'
FROM PO_GA_ORG_ASSIGNMENTS POGA
WHERE POH.PO_HEADER_ID = POGA.PO_HEADER_ID
AND POGA.ENABLED_FLAG ='Y'
AND ( (POGA.ORGANIZATION_ID IN (SELECT ORG_ID
FROM PO_SYSTEM_PARAMETERS)) OR (POGA.PURCHASING_ORG_ID IN (SELECT ORG_ID
FROM PO_SYSTEM_PARAMETERS)) ) )
AND POH.AUTHORIZATION_STATUS = 'APPROVED'
AND NVL(POH.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POH.FROZEN_FLAG
, 'N') = 'N'
AND (TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.START_DATE)
, TRUNC(SYSDATE)-1)
AND NVL(TRUNC(POH.END_DATE)
, TRUNC(SYSDATE)+1) OR (TRUNC(POH.START_DATE) IS NOT NULL
AND SYSDATE <= POH.START_DATE ))
AND TRUNC(SYSDATE) <= NVL(TRUNC(POL.EXPIRATION_DATE)
, TRUNC(SYSDATE))
AND NVL(POL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N') )
AND RRH.ROUTING_HEADER_ID(+) = PLL.RECEIVING_ROUTING_ID
AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV ('LANG')
AND HROU.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND POV.VENDOR_ID(+) = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID
AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LANGUAGE(+) = USERENV ('LANG')
AND PDT.DOCUMENT_TYPE_CODE = 'PA'
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDTL.DOCUMENT_TYPE_CODE = PDT.DOCUMENT_TYPE_CODE
AND PDTL.DOCUMENT_SUBTYPE = PDT.DOCUMENT_SUBTYPE
AND PDTL.LANGUAGE = USERENV ('LANG')
AND PDT.ORG_ID = POH.ORG_ID
AND PDTL.ORG_ID = PDT.ORG_ID
AND APT.TERM_ID(+) = POH.TERMS_ID
AND PLTB.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID
AND PLTB.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+)
AND PLTT.LANGUAGE(+) = USERENV ('LANG')
AND POUNT.UN_NUMBER_ID(+) = POL.UN_NUMBER_ID
AND POUNT.LANGUAGE(+) = USERENV ('LANG')
AND POHCT.HAZARD_CLASS_ID(+) = POL.HAZARD_CLASS_ID
AND POHCT.LANGUAGE(+) = USERENV ('LANG')
AND PLL.COUNTRY_OF_ORIGIN_CODE = FT.TERRITORY_CODE(+)
AND DECODE (FT.TERRITORY_CODE
, NULL
, '1'
, FT.LANGUAGE) = DECODE (FT.TERRITORY_CODE
, NULL
, '1'
, USERENV ('LANG'))
AND HROU.ORGANIZATION_ID = HOUT.ORGANIZATION_ID(+)
AND DECODE (HOUT.ORGANIZATION_ID
, NULL
, '1'
, HOUT.LANGUAGE) = DECODE (HOUT.ORGANIZATION_ID
, NULL
, '1'
, USERENV ('LANG'))
AND POH.STYLE_ID=PDSH.STYLE_ID
AND NVL(PDSH.PROGRESS_PAYMENT_FLAG
, 'N') = 'N'
AND ( POH.VENDOR_CONTACT_ID IS NULL OR POH.VENDOR_SITE_ID=POVC.VENDOR_SITE_ID )