VIEW: APPS.AP_PO_CORRECTIONS_V

Source

View Text - Preformatted

SELECT PLL.ROWID ROW_ID , PLL.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT_FLAG , PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ALLOW_SUBSTITUTE_RECEIPTS_FLAG , PLL.APPROVED_DATE APPROVED_DATE , PLL.APPROVED_FLAG APPROVED_FLAG , PLL.ATTRIBUTE1 ATTRIBUTE1 , PLL.ATTRIBUTE10 ATTRIBUTE10 , PLL.ATTRIBUTE11 ATTRIBUTE11 , PLL.ATTRIBUTE12 ATTRIBUTE12 , PLL.ATTRIBUTE13 ATTRIBUTE13 , PLL.ATTRIBUTE14 ATTRIBUTE14 , PLL.ATTRIBUTE15 ATTRIBUTE15 , PLL.ATTRIBUTE2 ATTRIBUTE2 , PLL.ATTRIBUTE3 ATTRIBUTE3 , PLL.ATTRIBUTE4 ATTRIBUTE4 , PLL.ATTRIBUTE5 ATTRIBUTE5 , PLL.ATTRIBUTE6 ATTRIBUTE6 , PLL.ATTRIBUTE7 ATTRIBUTE7 , PLL.ATTRIBUTE8 ATTRIBUTE8 , PLL.ATTRIBUTE9 ATTRIBUTE9 , PLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , PLL.CANCELLED_BY CANCELLED_BY , PLL.CANCEL_DATE CANCEL_DATE , PLL.CANCEL_FLAG CANCEL_FLAG , PLL.CANCEL_REASON CANCEL_REASON , PLL.CLOSED_BY CLOSED_BY , PLL.CLOSED_CODE CLOSED_CODE , PLL.CLOSED_DATE CLOSED_DATE , PLL.CLOSED_FLAG CLOSED_FLAG , PLL.CLOSED_REASON CLOSED_REASON , PLL.CREATED_BY CREATED_BY , PLL.CREATION_DATE CREATION_DATE , PLL.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_RECEIPT_ALLOWED , PLL.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED , PLL.ENCUMBERED_DATE ENCUMBERED_DATE , PLL.ENCUMBERED_FLAG ENCUMBERED_FLAG , PLL.ENCUMBER_NOW ENCUMBER_NOW , PLL.END_DATE END_DATE , PLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE , PLL.FIRM_DATE FIRM_DATE , PLL.FIRM_STATUS_LOOKUP_CODE FIRM_STATUS_LOOKUP_CODE , PLL.FOB_LOOKUP_CODE FOB_LOOKUP_CODE , PLL.FREIGHT_TERMS_LOOKUP_CODE FREIGHT_TERMS_LOOKUP_CODE , PLL.FROM_HEADER_ID FROM_HEADER_ID , PLL.FROM_LINE_ID FROM_LINE_ID , PLL.FROM_LINE_LOCATION_ID FROM_LINE_LOCATION_ID , PLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT , PLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG , PLL.INVOICE_CLOSE_TOLERANCE INVOICE_CLOSE_TOLERANCE , PLL.LAST_ACCEPT_DATE LAST_ACCEPT_DATE , PLL.LAST_UPDATED_BY LAST_UPDATED_BY , PLL.LAST_UPDATE_DATE LAST_UPDATE_DATE , PLL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , PLL.LEAD_TIME LEAD_TIME , PLL.LEAD_TIME_UNIT LEAD_TIME_UNIT , PLL.LINE_LOCATION_ID LINE_LOCATION_ID , PLL.NEED_BY_DATE NEED_BY_DATE , PLL.ORG_ID ORG_ID , PLL.PO_HEADER_ID PO_HEADER_ID , PLL.PO_LINE_ID PO_LINE_ID , PLL.PO_RELEASE_ID PO_RELEASE_ID , PLL.PRICE_DISCOUNT PRICE_DISCOUNT , PLL.PRICE_OVERRIDE PRICE_OVERRIDE , PLL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , PLL.PROGRAM_ID PROGRAM_ID , PLL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , PLL.PROMISED_DATE PROMISED_DATE , PLL.QTY_RCV_EXCEPTION_CODE QTY_RCV_EXCEPTION_CODE , PLL.QTY_RCV_TOLERANCE QTY_RCV_TOLERANCE , PLL.QUANTITY QUANTITY , PLL.QUANTITY_ACCEPTED QUANTITY_ACCEPTED , PLL.QUANTITY_BILLED QUANTITY_BILLED , PLL.QUANTITY_CANCELLED QUANTITY_CANCELLED , PLL.QUANTITY_RECEIVED QUANTITY_RECEIVED , PLL.QUANTITY_REJECTED QUANTITY_REJECTED , PLL.AMOUNT AMOUNT, PLL.AMOUNT_BILLED AMOUNT_BILLED , PLL.AMOUNT_CANCELLED AMOUNT_CANCELLED , PLL.AMOUNT_RECEIVED AMOUNT_RECEIVED , PLL.RECEIPT_DAYS_EXCEPTION_CODE RECEIPT_DAYS_EXCEPTION_CODE , PLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG , PLL.RECEIVE_CLOSE_TOLERANCE RECEIVE_CLOSE_TOLERANCE , PLL.RECEIVING_ROUTING_ID RECEIVING_ROUTING_ID , PLL.REQUEST_ID REQUEST_ID , PLL.SHIPMENT_NUM SHIPMENT_NUM , PLL.SHIPMENT_TYPE SHIPMENT_TYPE , PLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID , PLL.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID , PLL.SHIP_VIA_LOOKUP_CODE SHIP_VIA_LOOKUP_CODE , PLL.SOURCE_SHIPMENT_ID SOURCE_SHIPMENT_ID , PLL.START_DATE START_DATE , PLL.TERMS_ID TERMS_ID , PLL.UNENCUMBERED_QUANTITY UNENCUMBERED_QUANTITY , PL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE , PLL.UNIT_OF_MEASURE_CLASS UNIT_OF_MEASURE_CLASS , PH.SEGMENT1 PO_NUMBER , PL.LINE_NUM LINE_NUM , PL.ITEM_ID ITEM_ID , PL.ITEM_DESCRIPTION ITEM_DESCRIPTION , PL.TYPE_1099 TYPE_1099 , PLT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE , PR.RELEASE_NUM RELEASE_NUM , PLC.DISPLAYED_FIELD FREIGHT_TERMS , AT.NAME PAYMENT_TERMS , HE.FULL_NAME BUYER , HL.LOCATION_CODE SHIP_TO_LOCATION , PO_LINE_LOCATIONS_AP_PKG.GET_LAST_RECEIPT(PLL.LINE_LOCATION_ID) LAST_RECEIPT_DATE , PO_LINE_LOCATIONS_AP_PKG.GET_REQUESTORS(PLL.LINE_LOCATION_ID) REQUESTORS , PO_LINE_LOCATIONS_AP_PKG.GET_NUM_DISTRIBUTIONS(PLL.LINE_LOCATION_ID ) NUM_DISTRIBUTIONS , PH.APPROVED_FLAG PO_APPROVED_FLAG , PH.CURRENCY_CODE CURRENCY_CODE , PH.VENDOR_ID , PLL.GLOBAL_ATTRIBUTE_CATEGORY , PLL.GLOBAL_ATTRIBUTE1 , PLL.GLOBAL_ATTRIBUTE2 , PLL.GLOBAL_ATTRIBUTE3 , PLL.GLOBAL_ATTRIBUTE4 , PLL.GLOBAL_ATTRIBUTE5 , PLL.GLOBAL_ATTRIBUTE6 , PLL.GLOBAL_ATTRIBUTE7 , PLL.GLOBAL_ATTRIBUTE8 , PLL.GLOBAL_ATTRIBUTE9 , PLL.GLOBAL_ATTRIBUTE10 , PLL.GLOBAL_ATTRIBUTE11 , PLL.GLOBAL_ATTRIBUTE12 , PLL.GLOBAL_ATTRIBUTE13 , PLL.GLOBAL_ATTRIBUTE14 , PLL.GLOBAL_ATTRIBUTE15 , PLL.GLOBAL_ATTRIBUTE16 , PLL.GLOBAL_ATTRIBUTE17 , PLL.GLOBAL_ATTRIBUTE18 , PLL.GLOBAL_ATTRIBUTE19 , PLL.GLOBAL_ATTRIBUTE20 , PLL.MATCH_OPTION , PL.CATEGORY_ID ITEM_CATEGORY_ID, decode(pr.release_num,null,ph.pay_on_code,pr.pay_on_code) pay_on_code , PH.APPROVED_FLAG HEADER_APPROVED_FLAG , PLC1.DISPLAYED_FIELD CLOSED_CODE_DISP , AI.INVOICE_NUM INVOICE_NUMBER , AI.INVOICE_ID INVOICE_ID , AI.INVOICE_AMOUNT INVOICE_AMOUNT, AI.INVOICE_DATE INVOICE_DATE, AIL.LINE_NUMBER LINE_NUMBER , AIL.QUANTITY_INVOICED LINE_QUANTITY_INVOICED, AIL.UNIT_PRICE LINE_UNIT_PRICE, AIL.AMOUNT LINE_AMOUNT, PLT.MATCHING_BASIS MATCHING_BASIS_LOOKUP_CODE, ALC.DISPLAYED_FIELD MATCHING_BASIS, PLL.AMOUNT_SHIPPED AMOUNT_SHIPPED, PLL.QUANTITY_SHIPPED QUANTITY_SHIPPED, PLL.PAYMENT_TYPE PAYMENT_TYPE, PLC2.DISPLAYED_FIELD SHIPMENT_TYPE_DSP, PLC3.DISPLAYED_FIELD PAYMENT_TYPE_DSP, PLL.DESCRIPTION DESCRIPTION, PL.VENDOR_PRODUCT_NUM, PL.TASK_ID FROM AP_INVOICES_ALL AI, AP_INVOICE_LINES_ALL AIL, PO_RELEASES_ALL PR, PO_LINE_TYPES PLT, PO_LOOKUP_CODES PLC, AP_TERMS AT, PER_PEOPLE_F HE, HR_LOCATIONS_ALL_TL HL, PO_HEADERS_ALL PH, PO_LINES_ALL PL, PO_LINE_LOCATIONS_ALL PLL, PO_LOOKUP_CODES PLC1, AP_LOOKUP_CODES ALC, PO_LOOKUP_CODES PLC2, PO_LOOKUP_CODES PLC3 WHERE AI.INVOICE_ID = AIL.INVOICE_ID AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND AIL.MATCH_TYPE IN ('ITEM_TO_PO','ITEM_TO_SERVICE_PO') AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND PLL.PO_LINE_ID = PL.PO_LINE_ID AND PLT.LINE_TYPE_ID = PL.LINE_TYPE_ID AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID (+) AND PLC.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND PLC.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE AND PH.TERMS_ID = AT.TERM_ID (+) AND NVL(PR.AGENT_ID,PH.AGENT_ID ) = HE.PERSON_ID AND HE.EMPLOYEE_NUMBER IS NOT NULL AND trunc(sysdate) BETWEEN HE.effective_start_date AND HE.effective_end_date AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+) AND HL.LANGUAGE (+) = USERENV('LANG') AND PLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE' AND PLC1.LOOKUP_CODE (+) = PLL.CLOSED_CODE AND NVL( PLL.CONSIGNED_FLAG, 'N' ) = 'N' AND ALC.LOOKUP_TYPE = 'MATCH_BASIS' AND ALC.LOOKUP_CODE = PLT.MATCHING_BASIS AND PLC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE' AND PLC2.LOOKUP_CODE(+) = PLL.SHIPMENT_TYPE AND PLC3.LOOKUP_TYPE(+) = 'PAYMENT TYPE' AND PLC3.LOOKUP_CODE(+) = PLL.PAYMENT_TYPE AND AIL.MATCH_TYPE NOT IN ('QTY_CORRECTION','PRICE_CORRECTION','AMOUNT_CORRECTION') AND AI.CANCELLED_DATE IS NULL

View Text - HTML Formatted

SELECT PLL.ROWID ROW_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT_FLAG
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.APPROVED_DATE APPROVED_DATE
, PLL.APPROVED_FLAG APPROVED_FLAG
, PLL.ATTRIBUTE1 ATTRIBUTE1
, PLL.ATTRIBUTE10 ATTRIBUTE10
, PLL.ATTRIBUTE11 ATTRIBUTE11
, PLL.ATTRIBUTE12 ATTRIBUTE12
, PLL.ATTRIBUTE13 ATTRIBUTE13
, PLL.ATTRIBUTE14 ATTRIBUTE14
, PLL.ATTRIBUTE15 ATTRIBUTE15
, PLL.ATTRIBUTE2 ATTRIBUTE2
, PLL.ATTRIBUTE3 ATTRIBUTE3
, PLL.ATTRIBUTE4 ATTRIBUTE4
, PLL.ATTRIBUTE5 ATTRIBUTE5
, PLL.ATTRIBUTE6 ATTRIBUTE6
, PLL.ATTRIBUTE7 ATTRIBUTE7
, PLL.ATTRIBUTE8 ATTRIBUTE8
, PLL.ATTRIBUTE9 ATTRIBUTE9
, PLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PLL.CANCELLED_BY CANCELLED_BY
, PLL.CANCEL_DATE CANCEL_DATE
, PLL.CANCEL_FLAG CANCEL_FLAG
, PLL.CANCEL_REASON CANCEL_REASON
, PLL.CLOSED_BY CLOSED_BY
, PLL.CLOSED_CODE CLOSED_CODE
, PLL.CLOSED_DATE CLOSED_DATE
, PLL.CLOSED_FLAG CLOSED_FLAG
, PLL.CLOSED_REASON CLOSED_REASON
, PLL.CREATED_BY CREATED_BY
, PLL.CREATION_DATE CREATION_DATE
, PLL.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENCUMBERED_DATE ENCUMBERED_DATE
, PLL.ENCUMBERED_FLAG ENCUMBERED_FLAG
, PLL.ENCUMBER_NOW ENCUMBER_NOW
, PLL.END_DATE END_DATE
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.FIRM_DATE FIRM_DATE
, PLL.FIRM_STATUS_LOOKUP_CODE FIRM_STATUS_LOOKUP_CODE
, PLL.FOB_LOOKUP_CODE FOB_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE FREIGHT_TERMS_LOOKUP_CODE
, PLL.FROM_HEADER_ID FROM_HEADER_ID
, PLL.FROM_LINE_ID FROM_LINE_ID
, PLL.FROM_LINE_LOCATION_ID FROM_LINE_LOCATION_ID
, PLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, PLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, PLL.INVOICE_CLOSE_TOLERANCE INVOICE_CLOSE_TOLERANCE
, PLL.LAST_ACCEPT_DATE LAST_ACCEPT_DATE
, PLL.LAST_UPDATED_BY LAST_UPDATED_BY
, PLL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PLL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PLL.LEAD_TIME LEAD_TIME
, PLL.LEAD_TIME_UNIT LEAD_TIME_UNIT
, PLL.LINE_LOCATION_ID LINE_LOCATION_ID
, PLL.NEED_BY_DATE NEED_BY_DATE
, PLL.ORG_ID ORG_ID
, PLL.PO_HEADER_ID PO_HEADER_ID
, PLL.PO_LINE_ID PO_LINE_ID
, PLL.PO_RELEASE_ID PO_RELEASE_ID
, PLL.PRICE_DISCOUNT PRICE_DISCOUNT
, PLL.PRICE_OVERRIDE PRICE_OVERRIDE
, PLL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PLL.PROGRAM_ID PROGRAM_ID
, PLL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PLL.PROMISED_DATE PROMISED_DATE
, PLL.QTY_RCV_EXCEPTION_CODE QTY_RCV_EXCEPTION_CODE
, PLL.QTY_RCV_TOLERANCE QTY_RCV_TOLERANCE
, PLL.QUANTITY QUANTITY
, PLL.QUANTITY_ACCEPTED QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED QUANTITY_CANCELLED
, PLL.QUANTITY_RECEIVED QUANTITY_RECEIVED
, PLL.QUANTITY_REJECTED QUANTITY_REJECTED
, PLL.AMOUNT AMOUNT
, PLL.AMOUNT_BILLED AMOUNT_BILLED
, PLL.AMOUNT_CANCELLED AMOUNT_CANCELLED
, PLL.AMOUNT_RECEIVED AMOUNT_RECEIVED
, PLL.RECEIPT_DAYS_EXCEPTION_CODE RECEIPT_DAYS_EXCEPTION_CODE
, PLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, PLL.RECEIVE_CLOSE_TOLERANCE RECEIVE_CLOSE_TOLERANCE
, PLL.RECEIVING_ROUTING_ID RECEIVING_ROUTING_ID
, PLL.REQUEST_ID REQUEST_ID
, PLL.SHIPMENT_NUM SHIPMENT_NUM
, PLL.SHIPMENT_TYPE SHIPMENT_TYPE
, PLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID
, PLL.SHIP_VIA_LOOKUP_CODE SHIP_VIA_LOOKUP_CODE
, PLL.SOURCE_SHIPMENT_ID SOURCE_SHIPMENT_ID
, PLL.START_DATE START_DATE
, PLL.TERMS_ID TERMS_ID
, PLL.UNENCUMBERED_QUANTITY UNENCUMBERED_QUANTITY
, PL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE
, PLL.UNIT_OF_MEASURE_CLASS UNIT_OF_MEASURE_CLASS
, PH.SEGMENT1 PO_NUMBER
, PL.LINE_NUM LINE_NUM
, PL.ITEM_ID ITEM_ID
, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PL.TYPE_1099 TYPE_1099
, PLT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE
, PR.RELEASE_NUM RELEASE_NUM
, PLC.DISPLAYED_FIELD FREIGHT_TERMS
, AT.NAME PAYMENT_TERMS
, HE.FULL_NAME BUYER
, HL.LOCATION_CODE SHIP_TO_LOCATION
, PO_LINE_LOCATIONS_AP_PKG.GET_LAST_RECEIPT(PLL.LINE_LOCATION_ID) LAST_RECEIPT_DATE
, PO_LINE_LOCATIONS_AP_PKG.GET_REQUESTORS(PLL.LINE_LOCATION_ID) REQUESTORS
, PO_LINE_LOCATIONS_AP_PKG.GET_NUM_DISTRIBUTIONS(PLL.LINE_LOCATION_ID ) NUM_DISTRIBUTIONS
, PH.APPROVED_FLAG PO_APPROVED_FLAG
, PH.CURRENCY_CODE CURRENCY_CODE
, PH.VENDOR_ID
, PLL.GLOBAL_ATTRIBUTE_CATEGORY
, PLL.GLOBAL_ATTRIBUTE1
, PLL.GLOBAL_ATTRIBUTE2
, PLL.GLOBAL_ATTRIBUTE3
, PLL.GLOBAL_ATTRIBUTE4
, PLL.GLOBAL_ATTRIBUTE5
, PLL.GLOBAL_ATTRIBUTE6
, PLL.GLOBAL_ATTRIBUTE7
, PLL.GLOBAL_ATTRIBUTE8
, PLL.GLOBAL_ATTRIBUTE9
, PLL.GLOBAL_ATTRIBUTE10
, PLL.GLOBAL_ATTRIBUTE11
, PLL.GLOBAL_ATTRIBUTE12
, PLL.GLOBAL_ATTRIBUTE13
, PLL.GLOBAL_ATTRIBUTE14
, PLL.GLOBAL_ATTRIBUTE15
, PLL.GLOBAL_ATTRIBUTE16
, PLL.GLOBAL_ATTRIBUTE17
, PLL.GLOBAL_ATTRIBUTE18
, PLL.GLOBAL_ATTRIBUTE19
, PLL.GLOBAL_ATTRIBUTE20
, PLL.MATCH_OPTION
, PL.CATEGORY_ID ITEM_CATEGORY_ID
, DECODE(PR.RELEASE_NUM
, NULL
, PH.PAY_ON_CODE
, PR.PAY_ON_CODE) PAY_ON_CODE
, PH.APPROVED_FLAG HEADER_APPROVED_FLAG
, PLC1.DISPLAYED_FIELD CLOSED_CODE_DISP
, AI.INVOICE_NUM INVOICE_NUMBER
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_AMOUNT INVOICE_AMOUNT
, AI.INVOICE_DATE INVOICE_DATE
, AIL.LINE_NUMBER LINE_NUMBER
, AIL.QUANTITY_INVOICED LINE_QUANTITY_INVOICED
, AIL.UNIT_PRICE LINE_UNIT_PRICE
, AIL.AMOUNT LINE_AMOUNT
, PLT.MATCHING_BASIS MATCHING_BASIS_LOOKUP_CODE
, ALC.DISPLAYED_FIELD MATCHING_BASIS
, PLL.AMOUNT_SHIPPED AMOUNT_SHIPPED
, PLL.QUANTITY_SHIPPED QUANTITY_SHIPPED
, PLL.PAYMENT_TYPE PAYMENT_TYPE
, PLC2.DISPLAYED_FIELD SHIPMENT_TYPE_DSP
, PLC3.DISPLAYED_FIELD PAYMENT_TYPE_DSP
, PLL.DESCRIPTION DESCRIPTION
, PL.VENDOR_PRODUCT_NUM
, PL.TASK_ID
FROM AP_INVOICES_ALL AI
, AP_INVOICE_LINES_ALL AIL
, PO_RELEASES_ALL PR
, PO_LINE_TYPES PLT
, PO_LOOKUP_CODES PLC
, AP_TERMS AT
, PER_PEOPLE_F HE
, HR_LOCATIONS_ALL_TL HL
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PLL
, PO_LOOKUP_CODES PLC1
, AP_LOOKUP_CODES ALC
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
WHERE AI.INVOICE_ID = AIL.INVOICE_ID
AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND AIL.MATCH_TYPE IN ('ITEM_TO_PO'
, 'ITEM_TO_SERVICE_PO')
AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PLL.PO_LINE_ID = PL.PO_LINE_ID
AND PLT.LINE_TYPE_ID = PL.LINE_TYPE_ID
AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID (+)
AND PLC.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND PLC.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE
AND PH.TERMS_ID = AT.TERM_ID (+)
AND NVL(PR.AGENT_ID
, PH.AGENT_ID ) = HE.PERSON_ID
AND HE.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE
AND HE.EFFECTIVE_END_DATE
AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
AND HL.LANGUAGE (+) = USERENV('LANG')
AND PLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
AND PLC1.LOOKUP_CODE (+) = PLL.CLOSED_CODE
AND NVL( PLL.CONSIGNED_FLAG
, 'N' ) = 'N'
AND ALC.LOOKUP_TYPE = 'MATCH_BASIS'
AND ALC.LOOKUP_CODE = PLT.MATCHING_BASIS
AND PLC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE'
AND PLC2.LOOKUP_CODE(+) = PLL.SHIPMENT_TYPE
AND PLC3.LOOKUP_TYPE(+) = 'PAYMENT TYPE'
AND PLC3.LOOKUP_CODE(+) = PLL.PAYMENT_TYPE
AND AIL.MATCH_TYPE NOT IN ('QTY_CORRECTION'
, 'PRICE_CORRECTION'
, 'AMOUNT_CORRECTION')
AND AI.CANCELLED_DATE IS NULL