View: AP_INVOICES_UNION_PREPAYS_V

Product:	AP - Payables
Description:	(Release 10SC Only)
Implementation/DBA Data:	APPS.AP_INVOICES_UNION_PREPAYS_V

View Text

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

Columns