View: AP_INV_SELECTION_CRITERIA_V

View Text

SELECT AISC.ROWID ROW_ID
, AISC.ATTRIBUTE1 ATTRIBUTE1
, AISC.ATTRIBUTE10 ATTRIBUTE10
, AISC.ATTRIBUTE11 ATTRIBUTE11
, AISC.ATTRIBUTE12 ATTRIBUTE12
, AISC.ATTRIBUTE13 ATTRIBUTE13
, AISC.ATTRIBUTE14 ATTRIBUTE14
, AISC.ATTRIBUTE15 ATTRIBUTE15
, AISC.ATTRIBUTE2 ATTRIBUTE2
, AISC.ATTRIBUTE3 ATTRIBUTE3
, AISC.ATTRIBUTE4 ATTRIBUTE4
, AISC.ATTRIBUTE5 ATTRIBUTE5
, AISC.ATTRIBUTE6 ATTRIBUTE6
, AISC.ATTRIBUTE7 ATTRIBUTE7
, AISC.ATTRIBUTE8 ATTRIBUTE8
, AISC.ATTRIBUTE9 ATTRIBUTE9
, AISC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AISC.AUDIT_REQUIRED_FLAG AUDIT_REQUIRED_FLAG
, AISC.BANK_ACCOUNT_ID BANK_ACCOUNT_ID
, AISC.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME_IN_TABLE
, ABA. BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, AISC.BATCH_IDENTIFIER BATCH_IDENTIFIER
, AISC.CHECKRUN_ID CHECKRUN_ID
, AISC.CHECKRUN_NAME CHECKRUN_NAME
, AISC.CHECK_DATE CHECK_DATE
, AISC.CHECK_STOCK_ID CHECK_STOCK_ID
, AISC.CREATED_BY CREATED_BY
, AISC.CREATION_DATE CREATION_DATE
, AISC.CURRENCY_CODE CURRENCY_CODE
, AISC.DOCUMENT_ORDER_LOOKUP_CODE DOCUMENT_ORDER_LOOKUP_CODE
, AISC.END_PRINT_DOCUMENT END_PRINT_DOCUMENT
, AISC.EXCHANGE_DATE EXCHANGE_DATE
, AISC.EXCHANGE_RATE EXCHANGE_RATE
, AISC.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, AISC.FIRST_AVAILABLE_DOCUMENT FIRST_AVAILABLE_DOCUMENT
, AISC.FIRST_VOUCHER_NUMBER FIRST_VOUCHER_NUMBER
, AISC.HI_PAYMENT_PRIORITY HI_PAYMENT_PRIORITY
, AISC.INTERVAL INTERVAL
, AISC.LAST_UPDATED_BY LAST_UPDATED_BY
, AISC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AISC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AISC.LOW_PAYMENT_PRIORITY LOW_PAYMENT_PRIORITY
, AISC.MAX_OUTLAY MAX_OUTLAY
, AISC.MAX_PAYMENT_AMOUNT MAX_PAYMENT_AMOUNT
, AISC.MIN_CHECK_AMOUNT MIN_CHECK_AMOUNT
, AISC.ORG_ID ORG_ID
, AISC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD_LOOKUP_CODE
, AISC.PAY_ONLY_WHEN_DUE_FLAG PAY_ONLY_WHEN_DUE_FLAG
, AISC.PAY_THRU_DATE PAY_THRU_DATE
, AISC.PERIOD_NAME PERIOD_NAME
, AISC.START_PRINT_DOCUMENT START_PRINT_DOCUMENT
, AISC.STATUS STATUS
, AISC.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, AISC.USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT
, AISC.VENDOR_PAY_GROUP VENDOR_PAY_GROUP
, AISC.VOLUME_SERIAL_NUMBER VOLUME_SERIAL_NUMBER
, AISC.ZERO_AMOUNTS_ALLOWED ZERO_AMOUNTS_ALLOWED
, AISC.ZERO_INVOICES_ALLOWED ZERO_INVOICES_ALLOWED
, AISC.TEMPLATE_FLAG TEMPLATE_FLAG
, ACS.NAME DOCUMENT_NAME
, ABA.CURRENCY_CODE ACCOUNT_CURRENCY
, ACS.LAST_AVAILABLE_DOCUMENT_NUM LAST_DOCUMENT
, ALC1.DISPLAYED_FIELD DOCUMENT_ORDER
, ALC2.DISPLAYED_FIELD PAYMENT_METHOD
, APP1.PROGRAM_NAME BUILD_PAYMENTS_PROGRAM_NAME
, APP1.FRIENDLY_NAME BUILD_PAYMENTS_FRIENDLY_NAME
, APP2.PROGRAM_NAME FORMAT_PAYMENTS_PROGRAM_NAME
, APP2.FRIENDLY_NAME FORMAT_PAYMENTS_FRIENDLY_NAME
, APP3.PROGRAM_NAME REMITTANCE_PROGRAM_NAME
, APP3.FRIENDLY_NAME REMITTANCE_FRIENDLY_NAME
, DECODE(AISC.STATUS
, 'UNSTARTED'
, 'AUTOSELECT'
, 'BUILDING'
, 'BUILD'
, 'BUILT'
, 'FORMAT'
, 'CANCELED'
, 'NONE'
, 'CANCELING'
, 'NONE'
, 'CONFIRMED'
, 'REMITTANCE ADVICE'
, 'CONFIRMING'
, 'CONFIRM'
, 'FORMATTED'
, 'CONFIRM'
, 'FORMATTING'
, 'FORMAT'
, 'MODIFYING'
, 'BUILD'
, 'QUICKCHECK'
, 'NONE'
, 'REBUILDING'
, 'BUILD'
, 'RESTARTING'
, 'BUILD'
, 'SELECTED'
, 'BUILD'
, 'SELECTING'
, 'AUTOSELECT'
, NULL) NEXT_STEP_LOOKUP_CODE
, AP_INV_SELECTION_CRITERIA_PKG.GET_EXECUTION_STATE_CODE(ACCP.REQUEST_ID) NEXT_STEP_EXEC_STATE_CODE
, ACCP.REQUEST_ID REQUEST_ID
, GDCT.USER_CONVERSION_TYPE USER_RATE_TYPE
, DECODE(AISC.STATUS
, 'BUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'BUILT'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'FORMATTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'FORMATTED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'CONFIRMED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'CANCELING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'CANCELED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'QUICKCHECK'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'REBUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, 'RESTARTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_INV_COUNT(AISC.CHECKRUN_NAME)
, NULL) INVOICE_COUNT
, DECODE(AISC.STATUS
, 'BUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'BUILT'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'FORMATTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'FORMATTED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'CONFIRMED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'CANCELING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'CANCELED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'QUICKCHECK'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'REBUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, 'RESTARTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_PAYMT_TOTAL(AISC.CHECKRUN_NAME)
, NULL) PAYMENT_TOTAL
, DECODE(AISC.STATUS
, 'BUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'BUILT'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'FORMATTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'FORMATTED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'CONFIRMED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'CANCELING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'CANCELED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'QUICKCHECK'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'REBUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'RESTARTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_NEG_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, NULL) NEGOT_PAYMENT_COUNT
, DECODE(AISC.STATUS
, 'BUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'BUILT'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'FORMATTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'FORMATTED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_PRE_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'CONFIRMED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'CANCELING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'CANCELED'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'QUICKCHECK'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'REBUILDING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, 'RESTARTING'
, AP_INV_SELECTION_CRITERIA_PKG.GET_POST_CONF_ALL_PAYMT_COUNT(AISC.CHECKRUN_NAME)
, NULL) OVERALL_PAYMENT_COUNT
, ABA.MULTI_CURRENCY_FLAG BANK_MULTI_CURRENCY_FLAG
, ACF.MULTI_CURRENCY_FLAG FORMAT_MULTI_CURRENCY_FLAG
, AISC.TRANSFER_PRIORITY
, AISC.FUTURE_DATED_PAYMENT_FLAG
, AISC.BATCH_RUN_NAME
, AISC.BATCH_SET_ID
, AISC.BATCH_SET_LINE_ID
, APS.BATCH_SET_NAME
, ACS.FUTURE_DATED_PAYMENTS_FLAG
, AISC.ANTICIPATED_VALUE_DATE
, AISC.INVOICE_BATCH_ID
, AB.BATCH_NAME
, AISC.VENDOR_ID
, PV.VENDOR_NAME
, AISC.PROGRAM_ID
, AISC.PROGRAM_APPLICATION_ID
, AISC.PROGRAM_UPDATE_DATE
, AISC.REQUEST_ID PARENT_REQUEST_ID
FROM AP_INVOICE_SELECTION_CRITERIA AISC
, AP_CHECK_STOCKS ACS
, AP_BANK_ACCOUNTS ABA
, AP_PBATCH_SETS APS
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, AP_CHECKRUN_CONC_PROCESSES ACCP
, AP_PAYMENT_PROGRAMS APP1
, AP_PAYMENT_PROGRAMS APP2
, AP_PAYMENT_PROGRAMS APP3
, AP_CHECK_FORMATS ACF
, GL_DAILY_CONVERSION_TYPES GDCT
, AP_BATCHES AB
, PO_VENDORS PV
WHERE ACS.CHECK_STOCK_ID (+) = AISC.CHECK_STOCK_ID
AND ACS.CHECK_FORMAT_ID = ACF.CHECK_FORMAT_ID
AND ABA.BANK_ACCOUNT_ID (+) = AISC.BANK_ACCOUNT_ID
AND APS.BATCH_SET_ID (+) = AISC.BATCH_SET_ID
AND ALC1.LOOKUP_TYPE (+) = 'DOCUMENT ORDER'
AND ALC1.LOOKUP_CODE (+) = AISC.DOCUMENT_ORDER_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'PAYMENT METHOD'
AND ALC2.LOOKUP_CODE (+) = AISC.PAYMENT_METHOD_LOOKUP_CODE
AND AISC.CHECKRUN_NAME = ACCP.CHECKRUN_NAME(+)
AND APP1.PROGRAM_ID = ACF.BUILD_PAYMENTS_PROGRAM_ID
AND APP2.PROGRAM_ID = ACF.FORMAT_PAYMENTS_PROGRAM_ID
AND APP3.PROGRAM_ID (+) = ACF.REMITTANCE_ADVICE_PROGRAM_ID
AND DECODE(AISC.STATUS
, 'BUILDING'
, 'BUILD'
, 'BUILT'
, 'FORMAT'
, 'CANCELED'
, 'NONE'
, 'CANCELING'
, 'CANCEL'
, 'CONFIRMED'
, 'SEPARATE REMITTANCE ADVICE'
, 'CONFIRMING'
, 'CONFIRM'
, 'FORMATTED'
, 'CONFIRM'
, 'FORMATTING'
, 'FORMAT'
, 'MODIFYING'
, 'BUILD'
, 'QUICKCHECK'
, 'N/A'
, 'REBUILDING'
, 'BUILD'
, 'RESTARTING'
, 'BUILD'
, 'SELECTED'
, 'BUILD'
, 'SELECTING'
, 'AUTOSELECT'
, NULL) = ACCP.PROGRAM(+)
AND AISC.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND AISC.STATUS != 'QUICKCHECK'
AND AISC.INVOICE_BATCH_ID = AB.BATCH_ID(+)
AND AISC.VENDOR_ID = PV.VENDOR_ID(+)

Columns