eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: CE_200_TRANSACTIONS_V

Product:	CE - Cash Management
Description:	Available AP payments for reconciliation or clearing
Implementation/DBA Data:	APPS.CE_200_TRANSACTIONS_V

View Text

SELECT C.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ BANKPARTY.PARTY_NAME
, /* 6 */ BRANCHPARTY.PARTY_NAME
, /* 7 */ C.CHECK_ID
, /* 8 */ 'PAYMENT'
, /* 9 */ L2.MEANING
, /* 10 */ TO_CHAR(C.CHECK_NUMBER)
, /* 11 */ C.CHECK_NUMBER
, /* 12 */ C.CURRENCY_CODE
, /* 13 */ C.AMOUNT
, /* 14 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, ABA.CURRENCY_CODE
, C.AMOUNT
, C.AMOUNT * C.EXCHANGE_RATE)
, /* 15 */ DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 16 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, DECODE( DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)))
, DECODE(APH.TRANSACTION_TYPE
, 'PAYMENT CLEARING'
, APH.ACCOUNTING_DATE)
, /* 17 */ L1.DISPLAYED_FIELD
, /* 18 */ C.STATUS_LOOKUP_CODE
, /* 19 */ C.CHECK_DATE
, /* 20 */ C.CLEARED_DATE
, /* 21 */ C.FUTURE_PAY_DUE_DATE
, /* 22 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 23 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_DATE
, C.EXCHANGE_DATE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 24 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE_TYPE
, C.EXCHANGE_RATE_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 25 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, GDCC.USER_CONVERSION_TYPE
, GDC.USER_CONVERSION_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 26 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE
, C.EXCHANGE_RATE)
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 27 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_BASE_AMOUNT))
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 28 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_BASE_AMOUNT))
, NVL(TO_CHAR(C.PAYMENT_INSTRUCTION_ID)
, C.CHECKRUN_NAME)
, /* 29 */ NVL(C.PAYMENT_INSTRUCTION_ID
, C.CHECKRUN_ID)
, /* 30 */ C.VENDOR_NAME
, /* 31 */ NULL
, /* 32 */ C.PAYMENT_METHOD_CODE
, /* LK.DISPLAYED_FIELD
, */ /* 33 */ C.VENDOR_NAME
, /* 34 */ TO_NUMBER(NULL)
, /* 35 */ C.VENDOR_ID
, /* 36*/ NULL
, /* 37 */ NULL
, /* 38 */ TO_NUMBER(NULL)
, /* 39 */ DECODE(CRE.STATUS_FLAG
, 'M'
, /* 40 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.CLEARED_AMOUNT
, ABA.CURRENCY_CODE
, C.CLEARED_AMOUNT
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)) - NVL(CLEARED_CHARGES_BASE_AMOUNT
, 0) -NVL(CLEARED_ERROR_BASE_AMOUNT
, 0))
, C.CREATION_DATE
, /* 41 */ C.CREATED_BY
, /* 42 */ C.LAST_UPDATE_DATE
, /* 43 */ C.LAST_UPDATED_BY
, /* 44 */ DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, B.BATCH_IDENTIFIER
, TO_CHAR(C.PAYMENT_INSTRUCTION_ID))
, /* 45 */ TO_NUMBER(DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, 200
, 673))
, /* 46 */ 200
, /* 47 */ 0
, /* 48 */ ABA.CURRENCY_CODE
, /* 49 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 50 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AP_ASSET_CCID
, GAC.ASSET_CODE_COMBINATION_ID)
, /* 51 */ NULL
, /* 52 */ NULL
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ TO_NUMBER(NULL)
, /* 55 */ DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT')
, /* 56 */ NULL
, /* 57 */ DECODE( DECODE(C.STATUS_LOOKUP_CODE
, /* 58 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_BASE_AMOUNT
, C.BASE_AMOUNT)
, TO_NUMBER(NULL)
, /* 59 */ TO_NUMBER(NULL)
, /* 60 */ NULL
, /* 61 */ TO_DATE(NULL)
, /* 62 */ C.ACTUAL_VALUE_DATE
, NULL
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, C.ORG_ID
FROM CE_BANK_ACCOUNTS ABA
, /*CE_BANK_ACCT_USES_OU_V BAU
, */ CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, /*CE_BANK_BRANCHES_V ABB
, */ HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, AP_CHECKS_ALL C
, GL_DAILY_CONVERSION_TYPES GDC
, GL_DAILY_CONVERSION_TYPES GDCC
, CE_LOOKUPS L2
, AP_LOOKUP_CODES L1
, /* AP_LOOKUP_CODES LK
, */ GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, AP_INV_SELECTION_CRITERIA_ALL B
, /*IBY_FD_PAYMENTS_V PAY
, */ AP_PAYMENT_HISTORY_ALL APH
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE /*ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID*/ ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.AP_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' /*AND BAU.ORG_ID = SYS.ORG_ID*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID
AND BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID AND BAU.ORG_ID = C.ORG_ID
AND C.PAYMENT_TYPE_FLAG != 'R'
AND GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE
AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_CODE
AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = 'PAYMENT'
AND L1.LOOKUP_TYPE = 'CHECK STATE'
AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE
AND C.STATUS_LOOKUP_CODE IN ('NEGOTIABLE'
, 'ISSUED'
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'VOIDED')
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'STOP INITIATED' )
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED'))
, DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED BUT UNACCOUNTED')))
AND DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED')
, 'CLEARED'
, NVL(C.CLEARED_DATE
, C.CHECK_DATE)
, C.CHECK_DATE) >=SYS.CASHBOOK_BEGIN_DATE
AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID /*AND C.PAYMENT_ID = PAY.PAYMENT_ID (+)*/
AND APH.ORG_ID (+) = C.ORG_ID
AND APH.CHECK_ID(+) = C.CHECK_ID
AND APH.TRANSACTION_TYPE (+) LIKE 'PAYMENT '|| DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'UNCLEARING'
, '%CLEARING')
AND NOT EXISTS (SELECT NULL
FROM AP_PAYMENT_HISTORY APH2
WHERE APH2.CHECK_ID = C.CHECK_ID
AND APH2.TRANSACTION_TYPE IN ('PAYMENT CLEARING'
, 'PAYMENT UNCLEARING')
AND APH2.PAYMENT_HISTORY_ID > APH.PAYMENT_HISTORY_ID)
AND CRE.ORG_ID (+) = C.ORG_ID
AND CRE.REFERENCE_ID(+) = C.CHECK_ID
AND CRE.REFERENCE_TYPE(+) = 'PAYMENT'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID UNION ALL SELECT C.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ BANKPARTY.PARTY_NAME
, /* 6 */ BRANCHPARTY.PARTY_NAME
, /* 7 */ C.CHECK_ID
, /* 8 */ 'REFUND'
, /* 9 */ L2.MEANING
, /* 10 */ TO_CHAR(C.CHECK_NUMBER)
, /* 11 */ C.CHECK_NUMBER
, /* 12 */ C.CURRENCY_CODE
, /* 13 */ C.AMOUNT
, /* 14 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, ABA.CURRENCY_CODE
, C.AMOUNT
, C.AMOUNT * C.EXCHANGE_RATE)
, /* 15 */ DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 16 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, DECODE( DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)))
, DECODE(APH.TRANSACTION_TYPE
, 'PAYMENT CLEARING'
, APH.ACCOUNTING_DATE)
, /* 17 */ L1.DISPLAYED_FIELD
, /* 18 */ C.STATUS_LOOKUP_CODE
, /* 19 */ C.CHECK_DATE
, /* 20 */ C.CLEARED_DATE
, /* 21 */ C.FUTURE_PAY_DUE_DATE
, /* 22 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 23 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_DATE
, C.EXCHANGE_DATE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 24 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE_TYPE
, C.EXCHANGE_RATE_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 25 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, GDCC.USER_CONVERSION_TYPE
, GDC.USER_CONVERSION_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 26 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE
, C.EXCHANGE_RATE)
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 27 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_BASE_AMOUNT))
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 28 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_BASE_AMOUNT))
, NVL(TO_CHAR(C.PAYMENT_INSTRUCTION_ID)
, C.CHECKRUN_NAME)
, /* 29 */ NVL(C.PAYMENT_INSTRUCTION_ID
, C.CHECKRUN_ID)
, /* 30 */ C.VENDOR_NAME
, /* 31 */ NULL
, /* 32 */ C.PAYMENT_METHOD_CODE
, /* LK.DISPLAYED_FIELD
, */ /* 33 */ C.VENDOR_NAME
, /* 34 */ TO_NUMBER(NULL)
, /* 35 */ C.VENDOR_ID
, /* 36*/ NULL
, /* 37 */ NULL
, /* 38 */ TO_NUMBER(NULL)
, /* 39 */ DECODE(CRE.STATUS_FLAG
, 'M'
, /* 40 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.CLEARED_AMOUNT
, ABA.CURRENCY_CODE
, C.CLEARED_AMOUNT
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)) - NVL(CLEARED_CHARGES_BASE_AMOUNT
, 0) -NVL(CLEARED_ERROR_BASE_AMOUNT
, 0))
, C.CREATION_DATE
, /* 41 */ C.CREATED_BY
, /* 42 */ C.LAST_UPDATE_DATE
, /* 43 */ C.LAST_UPDATED_BY
, /* 44 */ DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, B.BATCH_IDENTIFIER
, TO_CHAR(C.PAYMENT_INSTRUCTION_ID))
, /* 45 */ TO_NUMBER(DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, 200
, 673))
, /* 46 */ 200
, /* 47 */ 0
, /* 48 */ ABA.CURRENCY_CODE
, /* 49 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 50 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AP_ASSET_CCID
, GAC.ASSET_CODE_COMBINATION_ID)
, /* 51 */ NULL
, /* 52 */ NULL
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ TO_NUMBER(NULL)
, /* 55 */ DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT')
, /* 56 */ NULL
, /* 57 */ DECODE( DECODE(C.STATUS_LOOKUP_CODE
, /* 58 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_BASE_AMOUNT
, C.BASE_AMOUNT)
, TO_NUMBER(NULL)
, /* 59 */ TO_NUMBER(NULL)
, /* 60 */ NULL
, /* 61 */ TO_DATE(NULL)
, /* 62 */ C.ACTUAL_VALUE_DATE
, NULL
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, C.ORG_ID
FROM CE_BANK_ACCOUNTS ABA
, /*CE_BANK_ACCT_USES_OU_V BAU
, */ CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, /*CE_BANK_BRANCHES_V ABB
, */ HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, AP_CHECKS_ALL C
, GL_DAILY_CONVERSION_TYPES GDC
, GL_DAILY_CONVERSION_TYPES GDCC
, CE_LOOKUPS L2
, AP_LOOKUP_CODES L1
, /* AP_LOOKUP_CODES LK
, */ GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, AP_INV_SELECTION_CRITERIA_ALL B
, /*IBY_FD_PAYMENTS_V PAY
, */ AP_PAYMENT_HISTORY_ALL APH
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE /*ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID*/ ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.AP_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' AND BAU.ORG_ID = C.ORG_ID /*AND BAU.ORG_ID = SYS.ORG_ID*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID
AND BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID
AND C.PAYMENT_TYPE_FLAG = 'R'
AND GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE
AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_CODE
AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = 'REFUND'
AND L1.LOOKUP_TYPE = 'CHECK STATE'
AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE
AND C.STATUS_LOOKUP_CODE IN ('NEGOTIABLE'
, 'ISSUED'
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'VOIDED')
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'STOP INITIATED' )
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED'))
, DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED BUT UNACCOUNTED')))
AND DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED')
, 'CLEARED'
, NVL(C.CLEARED_DATE
, C.CHECK_DATE)
, C.CHECK_DATE) >=SYS.CASHBOOK_BEGIN_DATE
AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID /*AND C.PAYMENT_ID = PAY.PAYMENT_ID (+)*/
AND APH.ORG_ID (+) = C.ORG_ID
AND APH.CHECK_ID(+) = C.CHECK_ID
AND APH.TRANSACTION_TYPE (+) LIKE 'PAYMENT '|| DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'UNCLEARING'
, '%CLEARING')
AND NOT EXISTS (SELECT NULL
FROM AP_PAYMENT_HISTORY APH2
WHERE APH2.CHECK_ID = C.CHECK_ID
AND APH2.TRANSACTION_TYPE IN ('PAYMENT CLEARING'
, 'PAYMENT UNCLEARING')
AND APH2.PAYMENT_HISTORY_ID > APH.PAYMENT_HISTORY_ID)
AND CRE.ORG_ID (+) = C.ORG_ID
AND CRE.REFERENCE_ID(+) = C.CHECK_ID
AND CRE.REFERENCE_TYPE(+) = 'PAYMENT'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID

Columns

Name
ROW_ID
MULTI_SELECT
BANK_ACCOUNT_ID
BANK_ACCOUNT_NAME
BANK_ACCOUNT_NUM
BANK_NAME
BANK_BRANCH_NAME
TRX_ID
TRX_TYPE
TYPE_MEANING
TRX_NUMBER
CHECK_NUMBER
CURRENCY_CODE
AMOUNT
BANK_ACCOUNT_AMOUNT
AMOUNT_CLEARED
GL_DATE
STATUS_DSP
STATUS
TRX_DATE
CLEARED_DATE
MATURITY_DATE
EXCHANGE_RATE_DATE
EXCHANGE_RATE_TYPE
USER_EXCHANGE_RATE_TYPE
EXCHANGE_RATE
BANK_CHARGES
BANK_ERRORS
BATCH_NAME
BATCH_ID
AGENT_NAME
CUSTOMER_NAME
PAYMENT_METHOD
VENDOR_NAME
CUSTOMER_ID
SUPPLIER_ID
REFERENCE_TYPE_DSP
REFERENCE_TYPE
REFERENCE_ID
ACTUAL_AMOUNT_CLEARED
CREATION_DATE
CREATED_BY
LAST_UPDATE_DATE
LAST_UPDATED_BY
REMITTANCE_NUMBER
CASH_RECEIPT_ID
APPLICATION_ID
COUNT_CLEARED
BANK_CURRENCY_CODE
TRX_CURRENCY_TYPE
CODE_COMBINATION_ID
PERIOD_NAME
JOURNAL_ENTRY_NAME
DOCUMENT_NUMBER
JOURNAL_ENTRY_LINE_NUMBER
CLEARING_TRX_TYPE
JOURNAL_CATEGORY
BASE_AMOUNT
RECEIPT_CLASS_ID
RECEIPT_METHOD_ID
RECEIPT_CLASS_NAME
DEPOSIT_DATE
VALUE_DATE
REVERSED_RECEIPT_FLAG
CE_BANK_ACCT_USE_ID
LEGAL_ENTITY_ID
ORG_ID