FND Design Data [Home] [Help]

View: AP_INVOICES_READY_TO_PAY_V

Product: AP - Payables
Description:
Implementation/DBA Data: ViewAPPS.AP_INVOICES_READY_TO_PAY_V
View Text

SELECT I.INVOICE_NUM INVOICE_NUM
, I.INVOICE_ID INVOICE_ID
, I.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE
, NVL(I.EXCLUSIVE_PAYMENT_FLAG
, 'N') EXCLUSIVE_PAYMENT_FLAG
, I.PAYMENT_CURRENCY_CODE CURRENCY_CODE
, I.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, P.PAYMENT_NUM PAYMENT_NUM
, NVL(P.AMOUNT_REMAINING
, 0) AMOUNT_REMAINING
, V.ALWAYS_TAKE_DISC_FLAG ALWAYS_TAKE_DISC_FLAG
, P.PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE
, P.DISCOUNT_AMOUNT_AVAILABLE DISCOUNT_AMOUNT_AVAILABLE
, P.DISCOUNT_DATE DISCOUNT_DATE
, P.SECOND_DISCOUNT_DATE SECOND_DISCOUNT_DATE
, P.SECOND_DISC_AMT_AVAILABLE SECOND_DISC_AMT_AVAILABLE
, P.THIRD_DISCOUNT_DATE THIRD_DISCOUNT_DATE
, P.THIRD_DISC_AMT_AVAILABLE THIRD_DISC_AMT_AVAILABLE
, P.GROSS_AMOUNT GROSS_AMOUNT
, I.DESCRIPTION DESCRIPTION
, I.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBI_ID
, I.VENDOR_ID VENDOR_ID
, I.VENDOR_SITE_ID VENDOR_SITE_ID
, P.DUE_DATE DUE_DATE
, P.EXTERNAL_BANK_ACCOUNT_ID EXTERNAL_BANK_ACCOUNT_ID
, V.FUTURE_DATED_PAYMENT_CCID
, I.ORG_ID
, I.PARTY_ID
, I.PARTY_SITE_ID
, I.PAYMENT_FUNCTION /* BUG 4965233 */
, I.PAY_PROC_TRXN_TYPE_CODE
, ALC.DISPLAYED_FIELD PAY_ALONE /* BUG 5296127 */ /* BUG 7535348*/
, P.REMIT_TO_SUPPLIER_NAME
, P.REMIT_TO_SUPPLIER_ID
, P.REMIT_TO_SUPPLIER_SITE
, P.REMIT_TO_SUPPLIER_SITE_ID
, I.RELATIONSHIP_ID /* BUG 7535348*/
FROM AP_PAYMENT_SCHEDULES P
, AP_INVOICES_ALL I
, AP_SUPPLIER_SITES_ALL V
, AP_LOOKUP_CODES ALC
WHERE V.VENDOR_ID=I.VENDOR_ID
AND V.VENDOR_SITE_ID=I.VENDOR_SITE_ID
AND I.INVOICE_ID=P.INVOICE_ID
AND P.ORG_ID = I.ORG_ID
AND I.CANCELLED_DATE IS NULL
AND NVL(I.PAYMENT_STATUS_FLAG
, 'N') <>'Y'
AND NVL(P.HOLD_FLAG
, 'N')='N'
AND NVL(P.PAYMENT_STATUS_FLAG
, 'N') <> 'Y'
AND P.CHECKRUN_ID IS NULL
AND I.INVOICE_TYPE_LOOKUP_CODE <> 'PAYMENT REQUEST'
AND NOT EXISTS(SELECT NULL
FROM AP_HOLDS_ALL AH
WHERE AH.INVOICE_ID=P.INVOICE_ID
AND AH.RELEASE_LOOKUP_CODE IS NULL)
AND EXISTS (SELECT NULL
FROM AP_INVOICE_DISTRIBUTIONS_ALL D
WHERE D.INVOICE_ID=P.INVOICE_ID)
AND NOT EXISTS (SELECT NULL
FROM AP_INVOICES_ALL AI2 /* BUG 4900225 */
WHERE AI2.INVOICE_ID = P.INVOICE_ID
AND (AP_INVOICES_PKG.GET_APPROVAL_STATUS( AI2.INVOICE_ID
, AI2.INVOICE_AMOUNT
, AI2.PAYMENT_STATUS_FLAG
, AI2.INVOICE_TYPE_LOOKUP_CODE) IN ('NEVER APPROVED'
, 'NEEDS REAPPROVAL'
, 'UNAPPROVED')))
AND (I.WFAPPROVAL_STATUS IN ('WFAPPROVED'
, 'NOT REQUIRED'
, 'MANUALLY APPROVED'))
AND NVL(I.EXCLUSIVE_PAYMENT_FLAG
, 'N') = ALC.LOOKUP_CODE /* 5296127
, 5387512 */
AND ALC.LOOKUP_TYPE = 'YES_NO_REQUIRED' UNION SELECT I.INVOICE_NUM INVOICE_NUM
, I.INVOICE_ID INVOICE_ID
, I.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE
, NVL(I.EXCLUSIVE_PAYMENT_FLAG
, 'N') EXCLUSIVE_PAYMENT_FLAG
, I.PAYMENT_CURRENCY_CODE CURRENCY_CODE
, I.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, P.PAYMENT_NUM PAYMENT_NUM
, NVL(P.AMOUNT_REMAINING
, 0) AMOUNT_REMAINING
, NULL ALWAYS_TAKE_DISC_FLAG
, P.PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE
, P.DISCOUNT_AMOUNT_AVAILABLE DISCOUNT_AMOUNT_AVAILABLE
, P.DISCOUNT_DATE DISCOUNT_DATE
, P.SECOND_DISCOUNT_DATE SECOND_DISCOUNT_DATE
, P.SECOND_DISC_AMT_AVAILABLE SECOND_DISC_AMT_AVAILABLE
, P.THIRD_DISCOUNT_DATE THIRD_DISCOUNT_DATE
, P.THIRD_DISC_AMT_AVAILABLE THIRD_DISC_AMT_AVAILABLE
, P.GROSS_AMOUNT GROSS_AMOUNT
, I.DESCRIPTION DESCRIPTION
, I.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBI_ID
, I.VENDOR_ID VENDOR_ID
, I.VENDOR_SITE_ID VENDOR_SITE_ID
, P.DUE_DATE DUE_DATE
, P.EXTERNAL_BANK_ACCOUNT_ID EXTERNAL_BANK_ACCOUNT_ID
, NULL FUTURE_DATED_PAYMENT_CCID
, I.ORG_ID
, I.PARTY_ID
, I.PARTY_SITE_ID
, I.PAYMENT_FUNCTION /* BUG 4965233
, ADDED THE FOLLOWING COLUMNS */
, I.PAY_PROC_TRXN_TYPE_CODE
, ALC.DISPLAYED_FIELD PAY_ALONE /* BUG 5296127 */ /* BUG 7535348*/
, I.REMIT_TO_SUPPLIER_NAME
, I.REMIT_TO_SUPPLIER_ID
, I.REMIT_TO_SUPPLIER_SITE
, I.REMIT_TO_SUPPLIER_SITE_ID
, I.RELATIONSHIP_ID /* BUG 7535348*/
FROM AP_PAYMENT_SCHEDULES P
, AP_INVOICES_ALL I
, AP_LOOKUP_CODES ALC
WHERE I.INVOICE_ID=P.INVOICE_ID
AND I.ORG_ID = P.ORG_ID
AND I.CANCELLED_DATE IS NULL
AND NVL(I.PAYMENT_STATUS_FLAG
, 'N') <>'Y'
AND NVL(P.HOLD_FLAG
, 'N')='N'
AND NVL(P.PAYMENT_STATUS_FLAG
, 'N') <> 'Y'
AND P.CHECKRUN_ID IS NULL
AND I.INVOICE_TYPE_LOOKUP_CODE = 'PAYMENT REQUEST'
AND NOT EXISTS(SELECT NULL
FROM AP_HOLDS_ALL AH
WHERE AH.INVOICE_ID=P.INVOICE_ID
AND AH.RELEASE_LOOKUP_CODE IS NULL)
AND EXISTS (SELECT NULL
FROM AP_INVOICE_DISTRIBUTIONS_ALL D
WHERE D.INVOICE_ID=P.INVOICE_ID)
AND NOT EXISTS (SELECT NULL
FROM AP_INVOICES_ALL AI2 /* BUG 4900225 */
WHERE AI2.INVOICE_ID = P.INVOICE_ID
AND (AP_INVOICES_PKG.GET_APPROVAL_STATUS( AI2.INVOICE_ID
, AI2.INVOICE_AMOUNT
, AI2.PAYMENT_STATUS_FLAG
, AI2.INVOICE_TYPE_LOOKUP_CODE) IN ('NEVER APPROVED'
, 'NEEDS REAPPROVAL'
, 'UNAPPROVED')))
AND (I.WFAPPROVAL_STATUS IN ('WFAPPROVED'
, 'NOT REQUIRED'
, 'MANUALLY APPROVED'))
AND NVL(I.EXCLUSIVE_PAYMENT_FLAG
, 'N') = ALC.LOOKUP_CODE /* BUG 5296127
, 5387512*/
AND ALC.LOOKUP_TYPE = 'YES_NO_REQUIRED'

Columns

Name
INVOICE_NUM
INVOICE_ID
INVOICE_TYPE
EXCLUSIVE_PAYMENT_FLAG
CURRENCY_CODE
SET_OF_BOOKS_ID
PAYMENT_NUM
AMOUNT_REMAINING
ALWAYS_TAKE_DISC_FLAG
PAYMENT_METHOD_CODE
DISCOUNT_AMOUNT_AVAILABLE
DISCOUNT_DATE
SECOND_DISCOUNT_DATE
SECOND_DISC_AMT_AVAILABLE
THIRD_DISCOUNT_DATE
THIRD_DISC_AMT_AVAILABLE
GROSS_AMOUNT
DESCRIPTION
ACCTS_PAY_CODE_COMBI_ID
VENDOR_ID
VENDOR_SITE_ID
DUE_DATE
EXTERNAL_BANK_ACCOUNT_ID
FUTURE_DATED_PAYMENT_CCID
ORG_ID
PARTY_ID
PARTY_SITE_ID
PAYMENT_FUNCTION
PAY_PROC_TRXN_TYPE_CODE
PAY_ALONE
REMIT_TO_SUPPLIER_NAME
REMIT_TO_SUPPLIER_ID
REMIT_TO_SUPPLIER_SITE
REMIT_TO_SUPPLIER_SITE_ID
RELATIONSHIP_ID