VIEW: APPS.CE_222_RECONCILED_V

Source

View Text - Preformatted

SELECT CRH.ROWID, /* 1 */ 'N', /* 2 */ SL.STATEMENT_LINE_ID, /* 3 */ aba.BANK_ACCOUNT_ID, /* 4 */ CRH.CASH_RECEIPT_HISTORY_ID, /* 5 */ CR.TYPE, /* 6 */ L1.MEANING, /* 7 */ CR.RECEIPT_NUMBER, /* 8 */ CR.CURRENCY_CODE, /* 9 */ DECODE(CR.CURRENCY_CODE, /* 10 */ SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0), /* 11 */ DECODE(CR.CURRENCY_CODE, /* 12 */ SOB.CURRENCY_CODE, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0), ABA.CURRENCY_CODE, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0), NVL(CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0), CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0))), DECODE(CR.CURRENCY_CODE, /* 13 */ SOB.CURRENCY_CODE, DECODE(DECODE(aps2.class, 'DM', 'REVERSED', CRH.STATUS), 'CLEARED',CRH.AMOUNT, 'REMITTED',CRH.AMOUNT, 'REVERSED',CRH.AMOUNT), ABA.CURRENCY_CODE, DECODE(DECODE(aps2.class, 'DM', 'REVERSED', CRH.STATUS), 'CLEARED',CRH.AMOUNT, 'REMITTED',CRH.AMOUNT, 'REVERSED',CRH.AMOUNT), DECODE(DECODE(aps2.class, 'DM', 'REVERSED', CRH.STATUS), 'CLEARED',NVL(CRH.ACCTD_AMOUNT,CRH.AMOUNT), 'REMITTED',NVL(CRH.ACCTD_AMOUNT,CRH.AMOUNT), 'REVERSED',NVL(CRH.ACCTD_AMOUNT,CRH.AMOUNT))), CRH.GL_DATE, /* 14 */ DECODE(aps2.class, /* 15 */ 'DM', DECODE(CRE.REFERENCE_TYPE, 'DM REVERSAL', (select L5.MEANING from AR_LOOKUPS L5 WHERE L5.lookup_type = 'RECEIPT_CREATION_STATUS' AND L5.LOOKUP_CODE = 'REVERSED'), L3.MEANING), L3.MEANING), DECODE(aps2.class, /* 16 */ 'DM', DECODE(CRE.REFERENCE_TYPE, 'DM REVERSAL','REVERSED', CRH.STATUS), CRH.STATUS), CR.RECEIPT_DATE, /* 17 */ CRH.TRX_DATE, /* 18 */ APS.DUE_DATE, /* 19 */ CRH.EXCHANGE_DATE, /* 20 */ GCT.USER_CONVERSION_TYPE, /* 21 */ CRH.EXCHANGE_RATE, /* 22 */ DECODE(CR.CURRENCY_CODE, /* 23 */ SOB.CURRENCY_CODE,CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT, CRH.FACTOR_DISCOUNT_AMOUNT)), TO_NUMBER(NULL), /* 24 */ B.NAME, /* 25 */ CRH.BATCH_ID, /* 26 */ HZ.PARTY_NAME, /* 27 */ HZ.PARTY_NAME, /* 28 */ NULL, /* 29 */ CR.PAY_FROM_CUSTOMER, /* 30 */ TO_NUMBER(NULL), /* 31 */ L2.MEANING, /* 32 */ CR.REFERENCE_TYPE, /* 33 */ CR.REFERENCE_ID, /* 34 */ CRE.CREATION_DATE, /* 35 */ CRE.CREATED_BY, /* 36 */ CRE.LAST_UPDATE_DATE, /* 37 */ CRE.LAST_UPDATED_BY, /* 38 */ SL.STATEMENT_HEADER_ID, /* 39 */ CR.CASH_RECEIPT_ID, /* 40 */ ABA.BANK_BRANCH_ID, /* 41 */ CR.RECEIPT_DATE, /* 42 */ DECODE(CRH.GL_POSTED_DATE,NULL,'N','Y'), /* 43 */ CRH.GL_POSTED_DATE, /* 44 */ L4.MEANING, /* 45 */ SL.LINE_NUMBER, /* 46 */ SL.TRX_DATE, /* 47 */ SL.AMOUNT, /* 48 */ SL.STATUS, /* 49 */ TC.DESCRIPTION, /* 50 */ SH.STATEMENT_NUMBER, /* 51 */ SH.STATEMENT_DATE, /* 52 */ ABA.BANK_ACCOUNT_NAME, /* 53 */ ABA.BANK_ACCOUNT_NUM, /* 54 */ ABA.CURRENCY_CODE, /* 55 */ SH.DOC_SEQUENCE_VALUE, /* 56 */ SL.TRX_TYPE, /* 57 */ B.BANK_DEPOSIT_NUMBER, /* 58 */ SH.CONTROL_END_BALANCE, /* 59 */ 222, /* 60 */ 1, /* 61 */ DECODE(CR.CURRENCY_CODE, /* 62 */ SOB.CURRENCY_CODE, DECODE(CRH.STATUS, 'CLEARED',CRH.AMOUNT+ NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0)), ABA.CURRENCY_CODE, DECODE(CRH.STATUS, 'CLEARED',CRH.AMOUNT+ NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0)), DECODE(CRH.STATUS, 'CLEARED',NVL(CRH.ACCTD_AMOUNT,CRH.AMOUNT)+ NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0))), CR.TYPE, /* 63 */ CRE.REQUEST_ID, /* 64 */ SL.INVOICE_TEXT, /* 65 */ SL.BANK_ACCOUNT_TEXT, /* 66 */ SL.CUSTOMER_TEXT, /* 67 */ CR.ACTUAL_VALUE_DATE, /* 68 */ BAU.BANK_ACCT_USE_ID, /* 69 */ CRH.ORG_ID, /* 70 */ TO_NUMBER(NULL) FROM CE_TRANSACTION_CODES TC, HZ_PARTIES HZ, hz_cust_accounts CU, GL_DAILY_CONVERSION_TYPES GCT, AR_PAYMENT_SCHEDULES_ALL APS, CE_SYSTEM_PARAMETERS SYS, gl_ledgers sob , CE_BANK_ACCOUNTS ABA, CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_STATEMENT_HEADERS SH, AR_LOOKUPS L3, CE_LOOKUPS L4, AR_LOOKUPS L2, CE_LOOKUPS L1, AR_BATCHES_ALL B, AR_CASH_RECEIPTS_ALL CR, AR_CASH_RECEIPT_HISTORY_ALL CRH, CE_STATEMENT_LINES SL, CE_STATEMENT_RECONCILS_ALL CRE, ar_payment_schedules_all aps2 WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER AND HZ.PARTY_ID(+) = CU.party_id AND GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID AND APS.ORG_ID (+) = CR.ORG_ID AND L4.LOOKUP_TYPE = 'BANK_TRX_TYPE' AND L4.LOOKUP_CODE = SL.TRX_TYPE AND L3.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS' AND L3.LOOKUP_CODE(+) = CRH.STATUS AND L2.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE' AND L2.LOOKUP_CODE(+) = CR.REFERENCE_TYPE AND L1.LOOKUP_TYPE = 'TRX_TYPE' AND L1.LOOKUP_CODE(+) = CR.TYPE AND B.BATCH_ID(+) = CRH.BATCH_ID AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID and sob.object_type_code = 'L' AND nvl(sob.complete_flag, 'Y') = 'Y' AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CR.ORG_ID = CRH.ORG_ID AND CRH.CASH_RECEIPT_HISTORY_ID = CRE.REFERENCE_ID AND CRH.ORG_ID = CRE.ORG_ID AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID AND BAU.AR_USE_ENABLE_FLAG = 'Y' AND BAU.ORG_ID = OU.ORGANIZATION_ID AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID /*AND BAU.ORG_ID = SYS.ORG_ID*/ AND BAU.ORG_ID = CRE.ORG_ID AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID AND CRE.REFERENCE_TYPE IN ('RECEIPT', 'DM REVERSAL') AND CRE.STATUS_FLAG = 'M' AND CRE.CURRENT_RECORD_FLAG = 'Y' and aps2.reversed_cash_receipt_id (+) = crh.cash_receipt_id AND APS2.ORG_ID (+) = CRH.ORG_ID

View Text - HTML Formatted

SELECT CRH.ROWID
, /* 1 */ 'N'
, /* 2 */ SL.STATEMENT_LINE_ID
, /* 3 */ ABA.BANK_ACCOUNT_ID
, /* 4 */ CRH.CASH_RECEIPT_HISTORY_ID
, /* 5 */ CR.TYPE
, /* 6 */ L1.MEANING
, /* 7 */ CR.RECEIPT_NUMBER
, /* 8 */ CR.CURRENCY_CODE
, /* 9 */ DECODE(CR.CURRENCY_CODE
, /* 10 */ SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, /* 11 */ DECODE(CR.CURRENCY_CODE
, /* 12 */ SOB.CURRENCY_CODE
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, ABA.CURRENCY_CODE
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, NVL(CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)))
, DECODE(CR.CURRENCY_CODE
, /* 13 */ SOB.CURRENCY_CODE
, DECODE(DECODE(APS2.CLASS
, 'DM'
, 'REVERSED'
, CRH.STATUS)
, 'CLEARED'
, CRH.AMOUNT
, 'REMITTED'
, CRH.AMOUNT
, 'REVERSED'
, CRH.AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(APS2.CLASS
, 'DM'
, 'REVERSED'
, CRH.STATUS)
, 'CLEARED'
, CRH.AMOUNT
, 'REMITTED'
, CRH.AMOUNT
, 'REVERSED'
, CRH.AMOUNT)
, DECODE(DECODE(APS2.CLASS
, 'DM'
, 'REVERSED'
, CRH.STATUS)
, 'CLEARED'
, NVL(CRH.ACCTD_AMOUNT
, CRH.AMOUNT)
, 'REMITTED'
, NVL(CRH.ACCTD_AMOUNT
, CRH.AMOUNT)
, 'REVERSED'
, NVL(CRH.ACCTD_AMOUNT
, CRH.AMOUNT)))
, CRH.GL_DATE
, /* 14 */ DECODE(APS2.CLASS
, /* 15 */ 'DM'
, DECODE(CRE.REFERENCE_TYPE
, 'DM REVERSAL'
, (SELECT L5.MEANING
FROM AR_LOOKUPS L5
WHERE L5.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND L5.LOOKUP_CODE = 'REVERSED')
, L3.MEANING)
, L3.MEANING)
, DECODE(APS2.CLASS
, /* 16 */ 'DM'
, DECODE(CRE.REFERENCE_TYPE
, 'DM REVERSAL'
, 'REVERSED'
, CRH.STATUS)
, CRH.STATUS)
, CR.RECEIPT_DATE
, /* 17 */ CRH.TRX_DATE
, /* 18 */ APS.DUE_DATE
, /* 19 */ CRH.EXCHANGE_DATE
, /* 20 */ GCT.USER_CONVERSION_TYPE
, /* 21 */ CRH.EXCHANGE_RATE
, /* 22 */ DECODE(CR.CURRENCY_CODE
, /* 23 */ SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, CRH.FACTOR_DISCOUNT_AMOUNT))
, TO_NUMBER(NULL)
, /* 24 */ B.NAME
, /* 25 */ CRH.BATCH_ID
, /* 26 */ HZ.PARTY_NAME
, /* 27 */ HZ.PARTY_NAME
, /* 28 */ NULL
, /* 29 */ CR.PAY_FROM_CUSTOMER
, /* 30 */ TO_NUMBER(NULL)
, /* 31 */ L2.MEANING
, /* 32 */ CR.REFERENCE_TYPE
, /* 33 */ CR.REFERENCE_ID
, /* 34 */ CRE.CREATION_DATE
, /* 35 */ CRE.CREATED_BY
, /* 36 */ CRE.LAST_UPDATE_DATE
, /* 37 */ CRE.LAST_UPDATED_BY
, /* 38 */ SL.STATEMENT_HEADER_ID
, /* 39 */ CR.CASH_RECEIPT_ID
, /* 40 */ ABA.BANK_BRANCH_ID
, /* 41 */ CR.RECEIPT_DATE
, /* 42 */ DECODE(CRH.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, /* 43 */ CRH.GL_POSTED_DATE
, /* 44 */ L4.MEANING
, /* 45 */ SL.LINE_NUMBER
, /* 46 */ SL.TRX_DATE
, /* 47 */ SL.AMOUNT
, /* 48 */ SL.STATUS
, /* 49 */ TC.DESCRIPTION
, /* 50 */ SH.STATEMENT_NUMBER
, /* 51 */ SH.STATEMENT_DATE
, /* 52 */ ABA.BANK_ACCOUNT_NAME
, /* 53 */ ABA.BANK_ACCOUNT_NUM
, /* 54 */ ABA.CURRENCY_CODE
, /* 55 */ SH.DOC_SEQUENCE_VALUE
, /* 56 */ SL.TRX_TYPE
, /* 57 */ B.BANK_DEPOSIT_NUMBER
, /* 58 */ SH.CONTROL_END_BALANCE
, /* 59 */ 222
, /* 60 */ 1
, /* 61 */ DECODE(CR.CURRENCY_CODE
, /* 62 */ SOB.CURRENCY_CODE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT+ NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0))
, ABA.CURRENCY_CODE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT+ NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0))
, DECODE(CRH.STATUS
, 'CLEARED'
, NVL(CRH.ACCTD_AMOUNT
, CRH.AMOUNT)+ NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)))
, CR.TYPE
, /* 63 */ CRE.REQUEST_ID
, /* 64 */ SL.INVOICE_TEXT
, /* 65 */ SL.BANK_ACCOUNT_TEXT
, /* 66 */ SL.CUSTOMER_TEXT
, /* 67 */ CR.ACTUAL_VALUE_DATE
, /* 68 */ BAU.BANK_ACCT_USE_ID
, /* 69 */ CRH.ORG_ID
, /* 70 */ TO_NUMBER(NULL)
FROM CE_TRANSACTION_CODES TC
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS CU
, GL_DAILY_CONVERSION_TYPES GCT
, AR_PAYMENT_SCHEDULES_ALL APS
, CE_SYSTEM_PARAMETERS SYS
, GL_LEDGERS SOB
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_STATEMENT_HEADERS SH
, AR_LOOKUPS L3
, CE_LOOKUPS L4
, AR_LOOKUPS L2
, CE_LOOKUPS L1
, AR_BATCHES_ALL B
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, CE_STATEMENT_LINES SL
, CE_STATEMENT_RECONCILS_ALL CRE
, AR_PAYMENT_SCHEDULES_ALL APS2
WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID
AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND HZ.PARTY_ID(+) = CU.PARTY_ID
AND GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE
AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND APS.ORG_ID (+) = CR.ORG_ID
AND L4.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L4.LOOKUP_CODE = SL.TRX_TYPE
AND L3.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS'
AND L3.LOOKUP_CODE(+) = CRH.STATUS
AND L2.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'
AND L2.LOOKUP_CODE(+) = CR.REFERENCE_TYPE
AND L1.LOOKUP_TYPE = 'TRX_TYPE'
AND L1.LOOKUP_CODE(+) = CR.TYPE
AND B.BATCH_ID(+) = CRH.BATCH_ID
AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID
AND SOB.OBJECT_TYPE_CODE = 'L'
AND NVL(SOB.COMPLETE_FLAG
, 'Y') = 'Y'
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CR.ORG_ID = CRH.ORG_ID
AND CRH.CASH_RECEIPT_HISTORY_ID = CRE.REFERENCE_ID
AND CRH.ORG_ID = CRE.ORG_ID
AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.AR_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT'
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID /*AND BAU.ORG_ID = SYS.ORG_ID*/
AND BAU.ORG_ID = CRE.ORG_ID
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID
AND CRE.REFERENCE_TYPE IN ('RECEIPT'
, 'DM REVERSAL')
AND CRE.STATUS_FLAG = 'M'
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND APS2.REVERSED_CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID
AND APS2.ORG_ID (+) = CRH.ORG_ID