FND Design Data [Home] [Help]

View: CE_200_REVERSAL_V

Product: CE - Cash Management
Description: AP transactions for unclearing
Implementation/DBA Data: ViewAPPS.CE_200_REVERSAL_V
View Text

SELECT C.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, C.CHECK_ID
, DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT')
, L2.MEANING
, TO_CHAR(C.CHECK_NUMBER)
, C.CURRENCY_CODE
, C.AMOUNT
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, ABA.CURRENCY_CODE
, C.AMOUNT
, NVL(C.BASE_AMOUNT
, C.AMOUNT))
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 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)))
, APH.ACCOUNTING_DATE
, L1.DISPLAYED_FIELD
, C.STATUS_LOOKUP_CODE
, C.CHECK_DATE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_DATE)
, C.FUTURE_PAY_DUE_DATE
, NVL(TO_CHAR(C.PAYMENT_INSTRUCTION_ID)
, C.CHECKRUN_NAME)
, C.VENDOR_NAME
, TO_NUMBER(NULL)
, C.VENDOR_ID
, C.CREATION_DATE
, C.CREATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATED_BY
, C.PAYMENT_METHOD_LOOKUP_CODE
, /* LK.DISPLAYED_FIELD
, */ ABA.BANK_ACCOUNT_NUM
, ABA.BANK_ACCOUNT_NAME
, ABA.CURRENCY_CODE
, ABB.BANK_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, C.PAYMENT_METHOD_LOOKUP_CODE
, TO_CHAR(NVL(C.PAYMENT_INSTRUCTION_ID
, C.CHECKRUN_ID))
, TO_NUMBER(NULL)
, 200
, DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, B.BATCH_IDENTIFIER
, TO_CHAR(C.PAYMENT_INSTRUCTION_ID))
, V.SEGMENT1
, 'N'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_BASE_AMOUNT
, C.BASE_AMOUNT)
, C.ACTUAL_VALUE_DATE
, BAU.BANK_ACCT_USE_ID
, C.ORG_ID FROM CE_BANK_BRANCHES_V ABB
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_OU_V BAU
, AP_LOOKUP_CODES L1
, CE_LOOKUPS L2
, /*AP_LOOKUP_CODES LK
, */ AP_INV_SELECTION_CRITERIA_ALL B
, PO_VENDORS V
, AP_CHECKS_ALL C
, AP_PAYMENT_HISTORY_ALL APH
, AP_SYSTEM_PARAMETERS_ALL APS
WHERE L2.LOOKUP_TYPE = 'TRX_TYPE' AND L2.LOOKUP_CODE = DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT') AND L1.LOOKUP_TYPE = 'CHECK STATE' AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_LOOKUP_CODE AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/ AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.AP_USE_ENABLE_FLAG = 'Y' /*AND BAU.ORG_ID = SYS.ORG_ID*/
AND BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID AND BAU.ORG_ID = C.ORG_ID AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID AND B.ORG_ID(+) = C.ORG_ID AND V.VENDOR_ID(+) = C.VENDOR_ID AND C.VOID_DATE IS NULL AND C.STATUS_LOOKUP_CODE IN ('CLEARED'
, 'CLEARED BUT UNACCOUNTED') AND C.ORG_ID = APH.ORG_ID(+) AND C.CHECK_ID = APH.CHECK_ID(+) AND APH.TRANSACTION_TYPE(+) = 'PAYMENT CLEARING' AND C.ORG_ID = APS.ORG_ID AND APS.RECON_ACCOUNTING_FLAG = 'Y' AND NOT EXISTS ( SELECT NULL FROM AP_PAYMENT_HISTORY_ALL APH2 WHERE APH.CHECK_ID = APH2.CHECK_ID AND APS.ORG_ID = APH2.ORG_ID AND APH.TRANSACTION_TYPE = APH2.TRANSACTION_TYPE AND APH2.PAYMENT_HISTORY_ID > APH.PAYMENT_HISTORY_ID) UNION ALL SELECT C.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, C.CHECK_ID
, DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT')
, L2.MEANING
, TO_CHAR(C.CHECK_NUMBER)
, C.CURRENCY_CODE
, C.AMOUNT
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, ABA.CURRENCY_CODE
, C.AMOUNT
, NVL(C.BASE_AMOUNT
, C.AMOUNT))
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 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)))
, C.CHECK_DATE
, L1.DISPLAYED_FIELD
, C.STATUS_LOOKUP_CODE
, C.CHECK_DATE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_DATE)
, C.FUTURE_PAY_DUE_DATE
, C.CHECKRUN_NAME
, C.VENDOR_NAME
, TO_NUMBER(NULL)
, C.VENDOR_ID
, C.CREATION_DATE
, C.CREATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATED_BY
, C.PAYMENT_METHOD_LOOKUP_CODE
, /*LK.DISPLAYED_FIELD
, */ ABA.BANK_ACCOUNT_NUM
, ABA.BANK_ACCOUNT_NAME
, ABA.CURRENCY_CODE
, ABB.BANK_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, C.PAYMENT_METHOD_LOOKUP_CODE
, TO_CHAR(C.CHECKRUN_ID)
, TO_NUMBER(NULL)
, 200
, B.BATCH_IDENTIFIER
, V.SEGMENT1
, 'N'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_BASE_AMOUNT
, C.BASE_AMOUNT)
, C.ACTUAL_VALUE_DATE
, BAU.BANK_ACCT_USE_ID
, C.ORG_ID FROM CE_BANK_BRANCHES_V ABB
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_OU_V BAU
, AP_LOOKUP_CODES L1
, CE_LOOKUPS L2
, /*AP_LOOKUP_CODES LK
, */ AP_INV_SELECTION_CRITERIA_ALL B
, PO_VENDORS V
, AP_CHECKS_ALL C
, AP_SYSTEM_PARAMETERS_ALL APS
WHERE L2.LOOKUP_TYPE = 'TRX_TYPE' AND L2.LOOKUP_CODE = DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT') AND L1.LOOKUP_TYPE = 'CHECK STATE' AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_LOOKUP_CODE AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/ AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.AP_USE_ENABLE_FLAG = 'Y' /*AND BAU.ORG_ID = SYS.ORG_ID*/
AND BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID AND BAU.ORG_ID = C.ORG_ID AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID AND B.ORG_ID(+) = C.ORG_ID AND V.VENDOR_ID(+) = C.VENDOR_ID AND C.VOID_DATE IS NULL AND C.STATUS_LOOKUP_CODE IN ('CLEARED'
, 'CLEARED BUT UNACCOUNTED') AND NVL(APS.RECON_ACCOUNTING_FLAG
, 'N') = 'N' AND BAU.ORG_ID = APS.ORG_ID

Columns

Name
ROW_ID
MULTI_SELECT
BANK_ACCOUNT_ID
TRX_ID
TRX_TYPE
TYPE_MEANING
TRX_NUMBER
CURRENCY_CODE
AMOUNT
BANK_ACCOUNT_AMOUNT
AMOUNT_CLEARED
GL_DATE
STATUS_DSP
STATUS
TRX_DATE
CLEARED_DATE
MATURITY_DATE
BATCH_NAME
AGENT_NAME
CUSTOMER_ID
SUPPLIER_ID
CREATION_DATE
CREATED_BY
LAST_UPDATE_DATE
LAST_UPDATED_BY
PAYMENT_METHOD
BANK_ACCOUNT_NUM
BANK_ACCOUNT_NAME
BANK_CURRENCY_CODE
BANK_NAME
REVERSAL_CATEGORY
REVERSAL_CATEGORY_ID
REVERSAL_REASON
REVERSAL_REASON_ID
REVERSAL_COMMENTS
PAYMENT_METHOD_ID
BATCH_ID
CASH_RECEIPT_ID
APPLICATION_ID
REMITTANCE_NUMBER
AGENT_NUMBER
TRX_RECONCILED_FLAG
STATEMENT_LINE_ID
STATEMENT_NUMBER
STATEMENT_LINE_AMOUNT
TRX_CURRENCY_TYPE
BASE_AMOUNT
VALUE_DATE
CE_BANK_ACCT_USE_ID
ORG_ID