AP_INVOICES_READY_TO_PAY

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

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

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

Query

SQL_Statement
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 , I.PAY_PROC_TRXN_TYPE_CODE , ALC.DISPLAYED_FIELD PAY_ALONE FROM AP_PAYMENT_SCHEDULES_ALL P, AP_INVOICES_ALL I, AP_POZ_SITE_AND_ASSIGNMENT_V 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.ORG_ID = V.BU_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 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 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 , I.PAY_PROC_TRXN_TYPE_CODE , ALC.DISPLAYED_FIELD PAY_ALONE FROM AP_PAYMENT_SCHEDULES_ALL 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 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 AND ALC.LOOKUP_TYPE = 'YES_NO_REQUIRED'

SQL_Statement

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

, I.PAY_PROC_TRXN_TYPE_CODE

, ALC.DISPLAYED_FIELD PAY_ALONE

FROM AP_PAYMENT_SCHEDULES_ALL P,

AP_INVOICES_ALL I,

AP_POZ_SITE_AND_ASSIGNMENT_V 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.ORG_ID = V.BU_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

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

AND ALC.LOOKUP_TYPE = 'YES_NO_REQUIRED'

UNION