VIEW: APPS.CE_200_REVERSAL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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