VIEW: APPS.AP_INVOICES_UNION_PREPAYS_V

Source

View Text - Preformatted

SELECT AI.ROWID ROW_ID , AI.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID , AI.AMOUNT_APPLICABLE_TO_DISCOUNT AMOUNT_APPLICABLE_TO_DISCOUNT , AI.AMOUNT_PAID AMOUNT_PAID , AI.APPROVAL_DESCRIPTION APPROVAL_DESCRIPTION , AI.APPROVED_AMOUNT APPROVED_AMOUNT , AI.ATTRIBUTE1 ATTRIBUTE1 , AI.ATTRIBUTE10 ATTRIBUTE10 , AI.ATTRIBUTE11 ATTRIBUTE11 , AI.ATTRIBUTE12 ATTRIBUTE12 , AI.ATTRIBUTE13 ATTRIBUTE13 , AI.ATTRIBUTE14 ATTRIBUTE14 , AI.ATTRIBUTE15 ATTRIBUTE15 , AI.ATTRIBUTE2 ATTRIBUTE2 , AI.ATTRIBUTE3 ATTRIBUTE3 , AI.ATTRIBUTE4 ATTRIBUTE4 , AI.ATTRIBUTE5 ATTRIBUTE5 , AI.ATTRIBUTE6 ATTRIBUTE6 , AI.ATTRIBUTE7 ATTRIBUTE7 , AI.ATTRIBUTE8 ATTRIBUTE8 , AI.ATTRIBUTE9 ATTRIBUTE9 , AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , AI.AUTHORIZED_BY AUTHORIZED_BY , AI.AWT_FLAG AWT_FLAG , AI.AWT_GROUP_ID AWT_GROUP_ID , AI.BASE_AMOUNT BASE_AMOUNT , AI.BATCH_ID BATCH_ID , AI.CANCELLED_AMOUNT CANCELLED_AMOUNT , AI.CANCELLED_BY CANCELLED_BY , AI.CANCELLED_DATE CANCELLED_DATE , AI.CREATED_BY CREATED_BY , AI.CREATION_DATE CREATION_DATE , AI.DESCRIPTION DESCRIPTION , AI.DISCOUNT_AMOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN , AI.DOC_CATEGORY_CODE DOC_CATEGORY_CODE , AI.DOC_SEQUENCE_ID DOC_SEQUENCE_ID , AI.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE , AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE , AI.EXCHANGE_DATE EXCHANGE_DATE , AI.EXCHANGE_RATE EXCHANGE_RATE , AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE , AI.EXCLUSIVE_PAYMENT_FLAG EXCLUSIVE_PAYMENT_FLAG , AI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , AI.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , AI.EXPENDITURE_TYPE EXPENDITURE_TYPE , AI.FREIGHT_AMOUNT FREIGHT_AMOUNT , AI.GOODS_RECEIVED_DATE GOODS_RECEIVED_DATE , SUM(APS.AMOUNT_REMAINING) PAY_CURR_INVOICE_AMOUNT , AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , AI.INVOICE_DATE INVOICE_DATE , AI.INVOICE_ID INVOICE_ID , AI.INVOICE_NUM INVOICE_NUM , AI.INVOICE_RECEIVED_DATE INVOICE_RECEIVED_DATE , AI.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE , AI.LAST_UPDATED_BY LAST_UPDATED_BY , AI.LAST_UPDATE_DATE LAST_UPDATE_DATE , AI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , AI.ORIGINAL_PREPAYMENT_AMOUNT ORIGINAL_PREPAYMENT_AMOUNT , AI.PAYMENT_AMOUNT_TOTAL PAYMENT_AMOUNT_TOTAL , AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE , AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE , AI.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD_LOOKUP_CODE , AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG , AI.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE , AI.PA_DEFAULT_DIST_CCID PA_DEFAULT_DIST_CCID , AI.PA_QUANTITY PA_QUANTITY , AI.PO_HEADER_ID PO_HEADER_ID , AI.PROJECT_ACCOUNTING_CONTEXT PROJECT_ACCOUNTING_CONTEXT , AI.PROJECT_ID PROJECT_ID , AI.RECURRING_PAYMENT_ID RECURRING_PAYMENT_ID , AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , AI.SOURCE SOURCE , AI.TASK_ID TASK_ID , AI.TAX_AMOUNT TAX_AMOUNT , AI.TEMP_CANCELLED_AMOUNT TEMP_CANCELLED_AMOUNT , AI.TERMS_DATE TERMS_DATE , AI.TERMS_ID TERMS_ID , AI.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE , AI.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT , AI.VAT_CODE VAT_CODE , AI.VENDOR_ID VENDOR_ID , AI.VENDOR_SITE_ID VENDOR_SITE_ID , AI.VOUCHER_NUM VOUCHER_NUM , ALC1.DISPLAYED_FIELD INVOICE_TYPE , ALC2.DISPLAYED_FIELD PAYMENT_STATUS , PV.VENDOR_NAME VENDOR_NAME , PV.SEGMENT1 VENDOR_NUMBER , PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE , 'INVOICE' PREPAYMENT_FLAG FROM AP_INVOICES AI, AP_PAYMENT_SCHEDULES APS, AP_LOOKUP_CODES ALC1, AP_LOOKUP_CODES ALC2, PO_VENDORS PV, PO_VENDOR_SITES PVS WHERE ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE' AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE AND ALC2.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS' AND ALC2.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG AND AI.INVOICE_ID = APS.INVOICE_ID AND AI.VENDOR_ID = PV.VENDOR_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT', 'CREDIT', 'DEBIT') AND AI.PAYMENT_STATUS_FLAG||'' IN ('N','P') AND 'A' = (SELECT MAX(DECODE(AID.MATCH_STATUS_FLAG, 'T','A', NVL(AID.MATCH_STATUS_FLAG,'Z'))) FROM AP_INVOICE_DISTRIBUTIONS AID WHERE AID.INVOICE_ID = AI.INVOICE_ID) AND NOT EXISTS (SELECT 'UNRELEASED HOLDS EXIST' FROM AP_HOLDS AH WHERE AH.INVOICE_ID = AI.INVOICE_ID AND AH.RELEASE_LOOKUP_CODE IS NULL) AND 0 < (SELECT SUM(APS.AMOUNT_REMAINING) FROM AP_PAYMENT_SCHEDULES APS WHERE APS.INVOICE_ID = AI.INVOICE_ID) AND NOT EXISTS (SELECT 'INVOICE SELECTED FOR PAYMENT' FROM AP_SELECTED_INVOICES ASI WHERE ASI.INVOICE_ID = AI.INVOICE_ID) GROUP BY AI.ROWID, AI.ACCTS_PAY_CODE_COMBINATION_ID, AI.AMOUNT_APPLICABLE_TO_DISCOUNT, AI.AMOUNT_PAID, AI.APPROVAL_DESCRIPTION, AI.APPROVED_AMOUNT, AI.ATTRIBUTE1, AI.ATTRIBUTE10, AI.ATTRIBUTE11, AI.ATTRIBUTE12, AI.ATTRIBUTE13, AI.ATTRIBUTE14, AI.ATTRIBUTE15, AI.ATTRIBUTE2, AI.ATTRIBUTE3, AI.ATTRIBUTE4, AI.ATTRIBUTE5, AI.ATTRIBUTE6, AI.ATTRIBUTE7, AI.ATTRIBUTE8, AI.ATTRIBUTE9, AI.ATTRIBUTE_CATEGORY, AI.AUTHORIZED_BY, AI.AWT_FLAG, AI.AWT_GROUP_ID, AI.BASE_AMOUNT, AI.BATCH_ID, AI.CANCELLED_AMOUNT, AI.CANCELLED_BY, AI.CANCELLED_DATE, AI.CREATED_BY, AI.CREATION_DATE, AI.DESCRIPTION, AI.DISCOUNT_AMOUNT_TAKEN, AI.DOC_CATEGORY_CODE, AI.DOC_SEQUENCE_ID, AI.DOC_SEQUENCE_VALUE, AI.EARLIEST_SETTLEMENT_DATE, AI.EXCHANGE_DATE, AI.EXCHANGE_RATE, AI.EXCHANGE_RATE_TYPE, AI.EXCLUSIVE_PAYMENT_FLAG, AI.EXPENDITURE_ITEM_DATE, AI.EXPENDITURE_ORGANIZATION_ID, AI.EXPENDITURE_TYPE, AI.FREIGHT_AMOUNT, AI.GOODS_RECEIVED_DATE, AI.INVOICE_CURRENCY_CODE, AI.INVOICE_DATE, AI.INVOICE_ID, AI.INVOICE_NUM, AI.INVOICE_RECEIVED_DATE, AI.INVOICE_TYPE_LOOKUP_CODE, AI.LAST_UPDATED_BY, AI.LAST_UPDATE_DATE, AI.LAST_UPDATE_LOGIN, AI.ORIGINAL_PREPAYMENT_AMOUNT, AI.PAYMENT_AMOUNT_TOTAL, AI.PAYMENT_CROSS_RATE, AI.PAYMENT_CURRENCY_CODE, AI.PAYMENT_METHOD_LOOKUP_CODE, AI.PAYMENT_STATUS_FLAG, AI.PAY_GROUP_LOOKUP_CODE, AI.PA_DEFAULT_DIST_CCID, AI.PA_QUANTITY, AI.PO_HEADER_ID, AI.PROJECT_ACCOUNTING_CONTEXT, AI.PROJECT_ID, AI.RECURRING_PAYMENT_ID, AI.SET_OF_BOOKS_ID, AI.SOURCE, AI.TASK_ID, AI.TAX_AMOUNT, AI.TEMP_CANCELLED_AMOUNT, AI.TERMS_DATE, AI.TERMS_ID, AI.USSGL_TRANSACTION_CODE, AI.USSGL_TRX_CODE_CONTEXT, AI.VAT_CODE, AI.VENDOR_ID, AI.VENDOR_SITE_ID, AI.VOUCHER_NUM, ALC1.DISPLAYED_FIELD, ALC2.DISPLAYED_FIELD, PV.VENDOR_NAME, PV.SEGMENT1, PVS.VENDOR_SITE_CODE UNION SELECT AI.ROWID ROW_ID, AI.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID, AI.AMOUNT_APPLICABLE_TO_DISCOUNT AMOUNT_APPLICABLE_TO_DISCOUNT, AI.AMOUNT_PAID AMOUNT_PAID, AI.APPROVAL_DESCRIPTION APPROVAL_DESCRIPTION, AI.APPROVED_AMOUNT APPROVED_AMOUNT, AI.ATTRIBUTE1 ATTRIBUTE1, AI.ATTRIBUTE10 ATTRIBUTE10, AI.ATTRIBUTE11 ATTRIBUTE11, AI.ATTRIBUTE12 ATTRIBUTE12, AI.ATTRIBUTE13 ATTRIBUTE13, AI.ATTRIBUTE14 ATTRIBUTE14, AI.ATTRIBUTE15 ATTRIBUTE15, AI.ATTRIBUTE2 ATTRIBUTE2, AI.ATTRIBUTE3 ATTRIBUTE3, AI.ATTRIBUTE4 ATTRIBUTE4, AI.ATTRIBUTE5 ATTRIBUTE5, AI.ATTRIBUTE6 ATTRIBUTE6, AI.ATTRIBUTE7 ATTRIBUTE7, AI.ATTRIBUTE8 ATTRIBUTE8, AI.ATTRIBUTE9 ATTRIBUTE9, AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, AI.AUTHORIZED_BY AUTHORIZED_BY, AI.AWT_FLAG AWT_FLAG, AI.AWT_GROUP_ID AWT_GROUP_ID, AI.BASE_AMOUNT BASE_AMOUNT, AI.BATCH_ID BATCH_ID, AI.CANCELLED_AMOUNT CANCELLED_AMOUNT, AI.CANCELLED_BY CANCELLED_BY, AI.CANCELLED_DATE CANCELLED_DATE, AI.CREATED_BY CREATED_BY, AI.CREATION_DATE CREATION_DATE, AI.DESCRIPTION DESCRIPTION, AI.DISCOUNT_AMOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN, AI.DOC_CATEGORY_CODE DOC_CATEGORY_CODE, AI.DOC_SEQUENCE_ID DOC_SEQUENCE_ID, AI.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE, AI.EXCHANGE_DATE EXCHANGE_DATE, AI.EXCHANGE_RATE EXCHANGE_RATE, AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE, AI.EXCLUSIVE_PAYMENT_FLAG EXCLUSIVE_PAYMENT_FLAG, AI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE, AI.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID, AI.EXPENDITURE_TYPE EXPENDITURE_TYPE, AI.FREIGHT_AMOUNT FREIGHT_AMOUNT, AI.GOODS_RECEIVED_DATE GOODS_RECEIVED_DATE, nvl(AI.PAY_CURR_INVOICE_AMOUNT,INVOICE_AMOUNT) PAY_CURR_INVOICE_AMOUNT, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, AI.INVOICE_DATE INVOICE_DATE, AI.INVOICE_ID INVOICE_ID, AI.INVOICE_NUM INVOICE_NUM, AI.INVOICE_RECEIVED_DATE INVOICE_RECEIVED_DATE, AI.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE, AI.LAST_UPDATED_BY LAST_UPDATED_BY, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE, AI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, AI.ORIGINAL_PREPAYMENT_AMOUNT ORIGINAL_PREPAYMENT_AMOUNT, AI.PAYMENT_AMOUNT_TOTAL PAYMENT_AMOUNT_TOTAL, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE, AI.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD_LOOKUP_CODE, AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG, AI.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE, AI.PA_DEFAULT_DIST_CCID PA_DEFAULT_DIST_CCID, AI.PA_QUANTITY PA_QUANTITY, AI.PO_HEADER_ID PO_HEADER_ID, AI.PROJECT_ACCOUNTING_CONTEXT PROJECT_ACCOUNTING_CONTEXT, AI.PROJECT_ID PROJECT_ID, AI.RECURRING_PAYMENT_ID RECURRING_PAYMENT_ID, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, AI.SOURCE SOURCE, AI.TASK_ID TASK_ID, AI.TAX_AMOUNT TAX_AMOUNT, AI.TEMP_CANCELLED_AMOUNT TEMP_CANCELLED_AMOUNT, AI.TERMS_DATE TERMS_DATE, AI.TERMS_ID TERMS_ID, AI.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, AI.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT, AI.VAT_CODE VAT_CODE, AI.VENDOR_ID VENDOR_ID, AI.VENDOR_SITE_ID VENDOR_SITE_ID, AI.VOUCHER_NUM VOUCHER_NUM, ALC1.DISPLAYED_FIELD INVOICE_TYPE, ALC2.DISPLAYED_FIELD PAYMENT_STATUS, PV.VENDOR_NAME VENDOR_NAME, PV.SEGMENT1 VENDOR_NUMBER, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, 'PREPAYMENT' PREPAYMENT_FLAG FROM AP_INVOICES AI, AP_LOOKUP_CODES ALC1, AP_LOOKUP_CODES ALC2, PO_VENDORS PV, PO_VENDOR_SITES PVS WHERE ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE' AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE AND ALC2.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS' AND ALC2.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG AND AI.VENDOR_ID = PV.VENDOR_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.PAYMENT_STATUS_FLAG||'' = 'Y' AND AI.EARLIEST_SETTLEMENT_DATE <= SYSDATE AND AI.INVOICE_AMOUNT > 0

View Text - HTML Formatted

SELECT AI.ROWID ROW_ID
, AI.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID
, AI.AMOUNT_APPLICABLE_TO_DISCOUNT AMOUNT_APPLICABLE_TO_DISCOUNT
, AI.AMOUNT_PAID AMOUNT_PAID
, AI.APPROVAL_DESCRIPTION APPROVAL_DESCRIPTION
, AI.APPROVED_AMOUNT APPROVED_AMOUNT
, AI.ATTRIBUTE1 ATTRIBUTE1
, AI.ATTRIBUTE10 ATTRIBUTE10
, AI.ATTRIBUTE11 ATTRIBUTE11
, AI.ATTRIBUTE12 ATTRIBUTE12
, AI.ATTRIBUTE13 ATTRIBUTE13
, AI.ATTRIBUTE14 ATTRIBUTE14
, AI.ATTRIBUTE15 ATTRIBUTE15
, AI.ATTRIBUTE2 ATTRIBUTE2
, AI.ATTRIBUTE3 ATTRIBUTE3
, AI.ATTRIBUTE4 ATTRIBUTE4
, AI.ATTRIBUTE5 ATTRIBUTE5
, AI.ATTRIBUTE6 ATTRIBUTE6
, AI.ATTRIBUTE7 ATTRIBUTE7
, AI.ATTRIBUTE8 ATTRIBUTE8
, AI.ATTRIBUTE9 ATTRIBUTE9
, AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AI.AUTHORIZED_BY AUTHORIZED_BY
, AI.AWT_FLAG AWT_FLAG
, AI.AWT_GROUP_ID AWT_GROUP_ID
, AI.BASE_AMOUNT BASE_AMOUNT
, AI.BATCH_ID BATCH_ID
, AI.CANCELLED_AMOUNT CANCELLED_AMOUNT
, AI.CANCELLED_BY CANCELLED_BY
, AI.CANCELLED_DATE CANCELLED_DATE
, AI.CREATED_BY CREATED_BY
, AI.CREATION_DATE CREATION_DATE
, AI.DESCRIPTION DESCRIPTION
, AI.DISCOUNT_AMOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN
, AI.DOC_CATEGORY_CODE DOC_CATEGORY_CODE
, AI.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE
, AI.EXCHANGE_DATE EXCHANGE_DATE
, AI.EXCHANGE_RATE EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, AI.EXCLUSIVE_PAYMENT_FLAG EXCLUSIVE_PAYMENT_FLAG
, AI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, AI.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, AI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, AI.FREIGHT_AMOUNT FREIGHT_AMOUNT
, AI.GOODS_RECEIVED_DATE GOODS_RECEIVED_DATE
, SUM(APS.AMOUNT_REMAINING) PAY_CURR_INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_NUM INVOICE_NUM
, AI.INVOICE_RECEIVED_DATE INVOICE_RECEIVED_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE
, AI.LAST_UPDATED_BY LAST_UPDATED_BY
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AI.ORIGINAL_PREPAYMENT_AMOUNT ORIGINAL_PREPAYMENT_AMOUNT
, AI.PAYMENT_AMOUNT_TOTAL PAYMENT_AMOUNT_TOTAL
, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, AI.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD_LOOKUP_CODE
, AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG
, AI.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE
, AI.PA_DEFAULT_DIST_CCID PA_DEFAULT_DIST_CCID
, AI.PA_QUANTITY PA_QUANTITY
, AI.PO_HEADER_ID PO_HEADER_ID
, AI.PROJECT_ACCOUNTING_CONTEXT PROJECT_ACCOUNTING_CONTEXT
, AI.PROJECT_ID PROJECT_ID
, AI.RECURRING_PAYMENT_ID RECURRING_PAYMENT_ID
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AI.SOURCE SOURCE
, AI.TASK_ID TASK_ID
, AI.TAX_AMOUNT TAX_AMOUNT
, AI.TEMP_CANCELLED_AMOUNT TEMP_CANCELLED_AMOUNT
, AI.TERMS_DATE TERMS_DATE
, AI.TERMS_ID TERMS_ID
, AI.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, AI.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT
, AI.VAT_CODE VAT_CODE
, AI.VENDOR_ID VENDOR_ID
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, AI.VOUCHER_NUM VOUCHER_NUM
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, PV.VENDOR_NAME VENDOR_NAME
, PV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, 'INVOICE' PREPAYMENT_FLAG
FROM AP_INVOICES AI
, AP_PAYMENT_SCHEDULES APS
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
WHERE ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS'
AND ALC2.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG
AND AI.INVOICE_ID = APS.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'CREDIT'
, 'DEBIT')
AND AI.PAYMENT_STATUS_FLAG||'' IN ('N'
, 'P')
AND 'A' = (SELECT MAX(DECODE(AID.MATCH_STATUS_FLAG
, 'T'
, 'A'
, NVL(AID.MATCH_STATUS_FLAG
, 'Z')))
FROM AP_INVOICE_DISTRIBUTIONS AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID)
AND NOT EXISTS (SELECT 'UNRELEASED HOLDS EXIST'
FROM AP_HOLDS AH
WHERE AH.INVOICE_ID = AI.INVOICE_ID
AND AH.RELEASE_LOOKUP_CODE IS NULL)
AND 0 < (SELECT SUM(APS.AMOUNT_REMAINING)
FROM AP_PAYMENT_SCHEDULES APS
WHERE APS.INVOICE_ID = AI.INVOICE_ID)
AND NOT EXISTS (SELECT 'INVOICE SELECTED FOR PAYMENT'
FROM AP_SELECTED_INVOICES ASI
WHERE ASI.INVOICE_ID = AI.INVOICE_ID) GROUP BY AI.ROWID
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AI.AMOUNT_APPLICABLE_TO_DISCOUNT
, AI.AMOUNT_PAID
, AI.APPROVAL_DESCRIPTION
, AI.APPROVED_AMOUNT
, AI.ATTRIBUTE1
, AI.ATTRIBUTE10
, AI.ATTRIBUTE11
, AI.ATTRIBUTE12
, AI.ATTRIBUTE13
, AI.ATTRIBUTE14
, AI.ATTRIBUTE15
, AI.ATTRIBUTE2
, AI.ATTRIBUTE3
, AI.ATTRIBUTE4
, AI.ATTRIBUTE5
, AI.ATTRIBUTE6
, AI.ATTRIBUTE7
, AI.ATTRIBUTE8
, AI.ATTRIBUTE9
, AI.ATTRIBUTE_CATEGORY
, AI.AUTHORIZED_BY
, AI.AWT_FLAG
, AI.AWT_GROUP_ID
, AI.BASE_AMOUNT
, AI.BATCH_ID
, AI.CANCELLED_AMOUNT
, AI.CANCELLED_BY
, AI.CANCELLED_DATE
, AI.CREATED_BY
, AI.CREATION_DATE
, AI.DESCRIPTION
, AI.DISCOUNT_AMOUNT_TAKEN
, AI.DOC_CATEGORY_CODE
, AI.DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE
, AI.EARLIEST_SETTLEMENT_DATE
, AI.EXCHANGE_DATE
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, AI.EXCLUSIVE_PAYMENT_FLAG
, AI.EXPENDITURE_ITEM_DATE
, AI.EXPENDITURE_ORGANIZATION_ID
, AI.EXPENDITURE_TYPE
, AI.FREIGHT_AMOUNT
, AI.GOODS_RECEIVED_DATE
, AI.INVOICE_CURRENCY_CODE
, AI.INVOICE_DATE
, AI.INVOICE_ID
, AI.INVOICE_NUM
, AI.INVOICE_RECEIVED_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE
, AI.LAST_UPDATED_BY
, AI.LAST_UPDATE_DATE
, AI.LAST_UPDATE_LOGIN
, AI.ORIGINAL_PREPAYMENT_AMOUNT
, AI.PAYMENT_AMOUNT_TOTAL
, AI.PAYMENT_CROSS_RATE
, AI.PAYMENT_CURRENCY_CODE
, AI.PAYMENT_METHOD_LOOKUP_CODE
, AI.PAYMENT_STATUS_FLAG
, AI.PAY_GROUP_LOOKUP_CODE
, AI.PA_DEFAULT_DIST_CCID
, AI.PA_QUANTITY
, AI.PO_HEADER_ID
, AI.PROJECT_ACCOUNTING_CONTEXT
, AI.PROJECT_ID
, AI.RECURRING_PAYMENT_ID
, AI.SET_OF_BOOKS_ID
, AI.SOURCE
, AI.TASK_ID
, AI.TAX_AMOUNT
, AI.TEMP_CANCELLED_AMOUNT
, AI.TERMS_DATE
, AI.TERMS_ID
, AI.USSGL_TRANSACTION_CODE
, AI.USSGL_TRX_CODE_CONTEXT
, AI.VAT_CODE
, AI.VENDOR_ID
, AI.VENDOR_SITE_ID
, AI.VOUCHER_NUM
, ALC1.DISPLAYED_FIELD
, ALC2.DISPLAYED_FIELD
, PV.VENDOR_NAME
, PV.SEGMENT1
, PVS.VENDOR_SITE_CODE UNION SELECT AI.ROWID ROW_ID
, AI.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID
, AI.AMOUNT_APPLICABLE_TO_DISCOUNT AMOUNT_APPLICABLE_TO_DISCOUNT
, AI.AMOUNT_PAID AMOUNT_PAID
, AI.APPROVAL_DESCRIPTION APPROVAL_DESCRIPTION
, AI.APPROVED_AMOUNT APPROVED_AMOUNT
, AI.ATTRIBUTE1 ATTRIBUTE1
, AI.ATTRIBUTE10 ATTRIBUTE10
, AI.ATTRIBUTE11 ATTRIBUTE11
, AI.ATTRIBUTE12 ATTRIBUTE12
, AI.ATTRIBUTE13 ATTRIBUTE13
, AI.ATTRIBUTE14 ATTRIBUTE14
, AI.ATTRIBUTE15 ATTRIBUTE15
, AI.ATTRIBUTE2 ATTRIBUTE2
, AI.ATTRIBUTE3 ATTRIBUTE3
, AI.ATTRIBUTE4 ATTRIBUTE4
, AI.ATTRIBUTE5 ATTRIBUTE5
, AI.ATTRIBUTE6 ATTRIBUTE6
, AI.ATTRIBUTE7 ATTRIBUTE7
, AI.ATTRIBUTE8 ATTRIBUTE8
, AI.ATTRIBUTE9 ATTRIBUTE9
, AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AI.AUTHORIZED_BY AUTHORIZED_BY
, AI.AWT_FLAG AWT_FLAG
, AI.AWT_GROUP_ID AWT_GROUP_ID
, AI.BASE_AMOUNT BASE_AMOUNT
, AI.BATCH_ID BATCH_ID
, AI.CANCELLED_AMOUNT CANCELLED_AMOUNT
, AI.CANCELLED_BY CANCELLED_BY
, AI.CANCELLED_DATE CANCELLED_DATE
, AI.CREATED_BY CREATED_BY
, AI.CREATION_DATE CREATION_DATE
, AI.DESCRIPTION DESCRIPTION
, AI.DISCOUNT_AMOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN
, AI.DOC_CATEGORY_CODE DOC_CATEGORY_CODE
, AI.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE
, AI.EXCHANGE_DATE EXCHANGE_DATE
, AI.EXCHANGE_RATE EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, AI.EXCLUSIVE_PAYMENT_FLAG EXCLUSIVE_PAYMENT_FLAG
, AI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, AI.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, AI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, AI.FREIGHT_AMOUNT FREIGHT_AMOUNT
, AI.GOODS_RECEIVED_DATE GOODS_RECEIVED_DATE
, NVL(AI.PAY_CURR_INVOICE_AMOUNT
, INVOICE_AMOUNT) PAY_CURR_INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_NUM INVOICE_NUM
, AI.INVOICE_RECEIVED_DATE INVOICE_RECEIVED_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE
, AI.LAST_UPDATED_BY LAST_UPDATED_BY
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AI.ORIGINAL_PREPAYMENT_AMOUNT ORIGINAL_PREPAYMENT_AMOUNT
, AI.PAYMENT_AMOUNT_TOTAL PAYMENT_AMOUNT_TOTAL
, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, AI.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD_LOOKUP_CODE
, AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG
, AI.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE
, AI.PA_DEFAULT_DIST_CCID PA_DEFAULT_DIST_CCID
, AI.PA_QUANTITY PA_QUANTITY
, AI.PO_HEADER_ID PO_HEADER_ID
, AI.PROJECT_ACCOUNTING_CONTEXT PROJECT_ACCOUNTING_CONTEXT
, AI.PROJECT_ID PROJECT_ID
, AI.RECURRING_PAYMENT_ID RECURRING_PAYMENT_ID
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AI.SOURCE SOURCE
, AI.TASK_ID TASK_ID
, AI.TAX_AMOUNT TAX_AMOUNT
, AI.TEMP_CANCELLED_AMOUNT TEMP_CANCELLED_AMOUNT
, AI.TERMS_DATE TERMS_DATE
, AI.TERMS_ID TERMS_ID
, AI.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, AI.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT
, AI.VAT_CODE VAT_CODE
, AI.VENDOR_ID VENDOR_ID
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, AI.VOUCHER_NUM VOUCHER_NUM
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, PV.VENDOR_NAME VENDOR_NAME
, PV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, 'PREPAYMENT' PREPAYMENT_FLAG
FROM AP_INVOICES AI
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
WHERE ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS'
AND ALC2.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND AI.PAYMENT_STATUS_FLAG||'' = 'Y'
AND AI.EARLIEST_SETTLEMENT_DATE <= SYSDATE
AND AI.INVOICE_AMOUNT > 0