VIEW: APPS.AP_INVOICES_READY_TO_PAY_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'