[Home] [Help]
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 ,
NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBER , /*9481666 - CLM*/
NVL(PL.LINE_NUM_DISPLAY, TO_CHAR(PL.LINE_NUM)) LINE_NUM , /*9481666 - CLM*/
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 AI, /*Bug#10134010 Modifed from AP_INVOICES_ALL to secured synonym */
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 Commented for bug#10096939*/
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 /*Bug7536336*/
AND NVL(AIL.DISCARDED_FLAG,'N') <> 'Y' /* Bug 9727651 */
AND AI.INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD','MIXED')/*Bug9570368*/
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
,
NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBER
, /*9481666 - CLM*/
NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) LINE_NUM
, /*9481666 - CLM*/
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 AI
, /*BUG#10134010 MODIFED
FROM AP_INVOICES_ALL TO SECURED SYNONYM */
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 COMMENTED FOR BUG#10096939*/
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 /*BUG7536336*/
AND NVL(AIL.DISCARDED_FLAG
, 'N') <> 'Y' /* BUG 9727651 */
AND AI.INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD'
, 'MIXED')/*BUG9570368*/
|
|
|
|