VIEW: APPS.PO_LINE_LOCATIONS_AP_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.ESTIMATED_TAX_AMOUNT ESTIMATED_TAX_AMOUNT , 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.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.TAXABLE_FLAG TAXABLE_FLAG , NULL TAX_NAME , PLL.TERMS_ID TERMS_ID , PLL.UNENCUMBERED_QUANTITY UNENCUMBERED_QUANTITY , NVL(PLL.UNIT_MEAS_LOOKUP_CODE,PL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE , PLL.UNIT_OF_MEASURE_CLASS UNIT_OF_MEASURE_CLASS , PLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE , NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBER , NVL(PL.LINE_NUM_DISPLAY,TO_CHAR(PL.LINE_NUM)) LINE_NUM, PL.ITEM_ID ITEM_ID , PL.ITEM_DESCRIPTION ITEM_DESCRIPTION , PL.TYPE_1099 TYPE_1099 , 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 , PH.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 , PLL.TAX_CODE_ID, PL.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 , PLL.AMOUNT , PLL.AMOUNT_RECEIVED , PLL.AMOUNT_BILLED , PLL.AMOUNT_CANCELLED , PLC2.DISPLAYED_FIELD MATCHING_BASIS , PLC2.LOOKUP_CODE MATCHING_BASIS_LOOKUP_CODE , PLC3.DISPLAYED_FIELD SHIPMENT_TYPE_DSP , PLL.PAYMENT_TYPE , PLC4.DISPLAYED_FIELD PAYMENT_TYPE_DSP , PLL.QUANTITY_FINANCED , PLL.AMOUNT_FINANCED , PLL.QUANTITY_RECOUPED , PLL.AMOUNT_RECOUPED , PLL.RETAINAGE_WITHHELD_AMOUNT , PLL.RETAINAGE_RELEASED_AMOUNT , PLL.QUANTITY_SHIPPED , PLL.AMOUNT_SHIPPED , PLL.DESCRIPTION , PL.VENDOR_PRODUCT_NUM , PL.TASK_ID , PLL.VALUE_BASIS , PL.ORDER_TYPE_LOOKUP_CODE , PLL.WORK_APPROVER_ID FROM PO_RELEASES_ALL PR, 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 PLL, PO_LOOKUP_CODES PLC1, PO_LOOKUP_CODES PLC2, PO_LOOKUP_CODES PLC3, PO_LOOKUP_CODES PLC4 WHERE PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND EXISTS (SELECT 1 FROM PO_DISTRIBUTIONS_ALL POD WHERE POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID) AND PLL.PO_LINE_ID = PL.PO_LINE_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 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 PLC2.LOOKUP_TYPE (+) = 'MATCHING BASIS' AND PLC2.LOOKUP_CODE (+) = PLL.MATCHING_BASIS AND PLC3.LOOKUP_TYPE(+) = 'SHIPMENT TYPE' AND PLC3.LOOKUP_CODE(+) = PLL.SHIPMENT_TYPE AND PLC4.LOOKUP_TYPE(+) = 'PAYMENT TYPE' AND PLC4.LOOKUP_CODE(+) = PLL.PAYMENT_TYPE AND ((PLL.PO_RELEASE_ID IS NOT NULL AND PR.PCARD_ID IS NULL) OR (PLL.PO_RELEASE_ID IS NULL AND PH.PCARD_ID IS NULL )) AND nvl(CLM_INFO_FLAG,'N') = 'N' AND ( nvl(clm_option_indicator,'B') <> 'O' OR Nvl(CLM_EXERCISED_FLAG,'N') = 'Y' ) AND PLL.SHIPMENT_TYPE != 'PLANNED'

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.ESTIMATED_TAX_AMOUNT ESTIMATED_TAX_AMOUNT
, 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.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.TAXABLE_FLAG TAXABLE_FLAG
, NULL TAX_NAME
, PLL.TERMS_ID TERMS_ID
, PLL.UNENCUMBERED_QUANTITY UNENCUMBERED_QUANTITY
, NVL(PLL.UNIT_MEAS_LOOKUP_CODE
, PL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE
, PLL.UNIT_OF_MEASURE_CLASS UNIT_OF_MEASURE_CLASS
, PLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBER
, NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) LINE_NUM
, PL.ITEM_ID ITEM_ID
, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PL.TYPE_1099 TYPE_1099
, 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
, PH.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
, PLL.TAX_CODE_ID
, PL.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
, PLL.AMOUNT
, PLL.AMOUNT_RECEIVED
, PLL.AMOUNT_BILLED
, PLL.AMOUNT_CANCELLED
, PLC2.DISPLAYED_FIELD MATCHING_BASIS
, PLC2.LOOKUP_CODE MATCHING_BASIS_LOOKUP_CODE
, PLC3.DISPLAYED_FIELD SHIPMENT_TYPE_DSP
, PLL.PAYMENT_TYPE
, PLC4.DISPLAYED_FIELD PAYMENT_TYPE_DSP
, PLL.QUANTITY_FINANCED
, PLL.AMOUNT_FINANCED
, PLL.QUANTITY_RECOUPED
, PLL.AMOUNT_RECOUPED
, PLL.RETAINAGE_WITHHELD_AMOUNT
, PLL.RETAINAGE_RELEASED_AMOUNT
, PLL.QUANTITY_SHIPPED
, PLL.AMOUNT_SHIPPED
, PLL.DESCRIPTION
, PL.VENDOR_PRODUCT_NUM
, PL.TASK_ID
, PLL.VALUE_BASIS
, PL.ORDER_TYPE_LOOKUP_CODE
, PLL.WORK_APPROVER_ID
FROM PO_RELEASES_ALL PR
, 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 PLL
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LOOKUP_CODES PLC4
WHERE PLL.PO_HEADER_ID = PH.PO_HEADER_ID
AND EXISTS (SELECT 1
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID)
AND PLL.PO_LINE_ID = PL.PO_LINE_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 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 PLC2.LOOKUP_TYPE (+) = 'MATCHING BASIS'
AND PLC2.LOOKUP_CODE (+) = PLL.MATCHING_BASIS
AND PLC3.LOOKUP_TYPE(+) = 'SHIPMENT TYPE'
AND PLC3.LOOKUP_CODE(+) = PLL.SHIPMENT_TYPE
AND PLC4.LOOKUP_TYPE(+) = 'PAYMENT TYPE'
AND PLC4.LOOKUP_CODE(+) = PLL.PAYMENT_TYPE
AND ((PLL.PO_RELEASE_ID IS NOT NULL
AND PR.PCARD_ID IS NULL) OR (PLL.PO_RELEASE_ID IS NULL
AND PH.PCARD_ID IS NULL ))
AND NVL(CLM_INFO_FLAG
, 'N') = 'N'
AND ( NVL(CLM_OPTION_INDICATOR
, 'B') <> 'O' OR NVL(CLM_EXERCISED_FLAG
, 'N') = 'Y' )
AND PLL.SHIPMENT_TYPE != 'PLANNED'