Product: | ICX - Oracle iProcurement |
---|---|
Description: | Obsolete |
Implementation/DBA Data: | Not implemented in this database |
SELECT POL.MARKET_PRICE
, DECODE(POL.CANCEL_FLAG
, 'I'
, NULL
, POL.CANCEL_FLAG)
, POL.CANCEL_DATE
, POL.FIRM_STATUS_LOOKUP_CODE
, POL.CONTRACT_NUM
, ATC.NAME
, POL.PO_HEADER_ID
, POL.LINE_NUM
, POL.CREATION_DATE
, POL.ITEM_REVISION
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.COMMITTED_AMOUNT
, POL.NOT_TO_EXCEED_PRICE
, POL.QUANTITY
, POL.HAZARD_CLASS_ID
, POL.FROM_HEADER_ID
, POL.PROGRAM_ID
, POL.PROGRAM_UPDATE_DATE
, POL.REQUEST_ID
, POL.USSGL_TRANSACTION_CODE
, POL.CLOSED_DATE
, POL.CLOSED_REASON
, POL.TRANSACTION_REASON_CODE
, POL.CATEGORY_ID
, POL.PRICE_TYPE_LOOKUP_CODE
, POL.PRICE_BREAK_LOOKUP_CODE
, POL.QTY_RCV_TOLERANCE
, POL.TYPE_1099
, POL.CAPITAL_EXPENSE_FLAG
, POL.NEGOTIATED_BY_PREPARER_FLAG
, POL.MIN_RELEASE_AMOUNT
, POL.CLOSED_CODE
, POL.GOVERNMENT_CONTEXT
, POL.PROGRAM_APPLICATION_ID
, POL.MIN_ORDER_QUANTITY
, POL.MAX_ORDER_QUANTITY
, POL.OVER_TOLERANCE_ERROR_FLAG
, POL.UNORDERED_FLAG
, POL.CLOSED_FLAG
, POL.USER_HOLD_FLAG
, POL.CANCEL_REASON
, POL.FIRM_DATE
, POL.VENDOR_PRODUCT_NUM
, POL.TAXABLE_FLAG
, POL.PO_LINE_ID
, POL.LAST_UPDATE_DATE
, POL.LAST_UPDATED_BY
, POL.LINE_TYPE_ID
, POL.LAST_UPDATE_LOGIN
, POL.CREATED_BY
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.QUANTITY_COMMITTED
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.LIST_PRICE_PER_UNIT
, POL.UNIT_PRICE
, POL.UN_NUMBER_ID
, POL.NOTE_TO_VENDOR
, POL.FROM_LINE_ID
, POL.REFERENCE_NUM
, POL.ATTRIBUTE_CATEGORY
, POL.ATTRIBUTE1
, POL.ATTRIBUTE2
, POL.ATTRIBUTE3
, POL.ATTRIBUTE4
, POL.ATTRIBUTE5
, POL.ATTRIBUTE6
, POL.ATTRIBUTE7
, POL.ATTRIBUTE8
, POL.ATTRIBUTE9
, POL.ATTRIBUTE10
, POL.ATTRIBUTE11
, POL.ATTRIBUTE12
, POL.ATTRIBUTE13
, POL.ATTRIBUTE14
, POL.ATTRIBUTE15
, PLT.LINE_TYPE
, PLT.ORDER_TYPE_LOOKUP_CODE /* KM REMOVE ABOVE
, NOT NEEDED */
, NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') /* KM REMOVE ABOVE
, NOT NEEDED */
, MUOM.UOM_CLASS /* KM REMOVE; NOT NEEDED */
, POUN.UN_NUMBER
, PHC.HAZARD_CLASS
, POLC1.DISPLAYED_FIELD
, POLC2.DISPLAYED_FIELD
, POLC3.DISPLAYED_FIELD
, POLC4.DISPLAYED_FIELD
, POH2.SEGMENT1
, POH2.QUOTE_VENDOR_QUOTE_NUMBER
, POL2.LINE_NUM
, PO_INQ_SV.GET_PERSON_NAME(POL.CANCELLED_BY)
, PO_INQ_SV.GET_PERSON_NAME(POL.CLOSED_BY)
, POH.SEGMENT1
, 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.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID)
, POH.BILL_TO_LOCATION_ID
, HRL.LOCATION_CODE
, POH.SHIP_TO_LOCATION_ID
, HRL2.LOCATION_CODE
, POH.CURRENCY_CODE
, POH.TYPE_LOOKUP_CODE
, POH.REVISION_NUM
, PDT.TYPE_NAME
, POH.AUTHORIZATION_STATUS
, POLC5.DISPLAYED_FIELD
, POH.CREATION_DATE
, POH.APPROVED_DATE
, POH.REVISED_DATE
, APT.NAME
, POH.SHIP_VIA_LOOKUP_CODE
, POLC6.DISPLAYED_FIELD
, POLC7.DISPLAYED_FIELD
, GLDC.USER_CONVERSION_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.START_DATE
, POH.END_DATE
, POH.BLANKET_TOTAL_AMOUNT
, POH.NOTE_TO_RECEIVER
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
FROM PO_LINES POL
, PO_LINES POL2
, PO_HEADERS POH
, PO_HEADERS POH2
, PO_LINE_TYPES PLT
, MTL_UNITS_OF_MEASURE MUOM
, /* KM REMOVE MUOM */ PO_UN_NUMBERS POUN
, PO_HAZARD_CLASSES PHC
, PO_LOOKUP_CODES POLC1
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_VENDOR_CONTACTS POVC
, HR_LOCATIONS HRL
, HR_LOCATIONS HRL2
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES POLC5
, AP_TERMS APT
, AP_TAX_CODES ATC
, PO_LOOKUP_CODES POLC6
, PO_LOOKUP_CODES POLC7
, GL_DAILY_CONVERSION_TYPES GLDC
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND MUOM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE /* KM REMOVE JOIN TO MUOM; SHOULD NOT BE NECESSARY */
AND POUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID
AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID
AND POLC1.LOOKUP_TYPE (+) = 'PRICE TYPE'
AND POLC1.LOOKUP_CODE (+) = POL.PRICE_TYPE_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE (+) = 'TRANSACTION REASON'
AND POLC2.LOOKUP_CODE (+) = POL.TRANSACTION_REASON_CODE
AND POLC3.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC3.LOOKUP_CODE (+) = POL.CLOSED_CODE /* KM ADD NVL ON CLOSED CODE
AND REMOVE OUTER-JOIN */
AND POLC4.LOOKUP_TYPE (+) = 'PRICE BREAK TYPE'
AND POLC4.LOOKUP_CODE (+) = POL.PRICE_BREAK_LOOKUP_CODE
AND POH2.PO_HEADER_ID (+) = POL.FROM_HEADER_ID
AND POL2.PO_LINE_ID (+) = POL.FROM_LINE_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 HRL.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND POLC5.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC5.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND APT.TERM_ID (+) = POH.TERMS_ID
AND POL.TAX_CODE_ID = ATC.TAX_ID (+)
AND POLC6.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE
AND POLC6.LOOKUP_TYPE (+) = 'FOB'
AND POLC7.LOOKUP_CODE (+) = POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC7.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE