View: CE_222_RECONCILED_V

Product:	CE - Cash Management
Description:	Reconciled AR receipts for unreconciling
Implementation/DBA Data:	APPS.CE_222_RECONCILED_V

View Text

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

Columns