VIEW: APPS.CE_101_RECONCILED_V

Source

View Text - Preformatted

SELECT JEL.ROWID, /* 1 */ 'N', /* 2 */ SL.STATEMENT_LINE_ID, /* 3 */ ABA.BANK_ACCOUNT_ID, /* 4 */ JEL.JE_LINE_NUM, /* 5 */ DECODE(DECODE(NVL(JEL.ENTERED_DR, 0) , 0, NVL(JEL.ACCOUNTED_DR,0), /* 6 */ JEL.ENTERED_DR), 0,'PAYMENT','CASH'), L2.MEANING, /* 7 */ JEL.DESCRIPTION, /* 8 */ JEH.CURRENCY_CODE, /* 9 */ DECODE(JEH.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', /* 10 */ ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), NVL(JEL.ENTERED_DR,JEL.ENTERED_CR), /* 11 */ DECODE(JEH.CURRENCY_CODE, /* 12 */ SOB.CURRENCY_CODE, DECODE(NVL(JEL.ENTERED_DR,NVL(JEL.ACCOUNTED_DR,0)), 0,JEL.ENTERED_CR,JEL.ENTERED_DR), ABA.CURRENCY_CODE, DECODE(NVL(JEL.ENTERED_DR,NVL(JEL.ACCOUNTED_DR,0)), 0,JEL.ENTERED_CR,JEL.ENTERED_DR), DECODE(NVL(JEL.ACCOUNTED_DR,0),0,JEL.ACCOUNTED_CR,JEL.ACCOUNTED_DR)), DECODE(JEH.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 13 */ DECODE(NVL(JEL.ENTERED_DR,0),0,JEL.ENTERED_CR,JEL.ENTERED_DR), ABA.CURRENCY_CODE, DECODE(NVL(JEL.ENTERED_DR,0),0,JEL.ENTERED_CR,JEL.ENTERED_DR), DECODE(NVL(JEL.ACCOUNTED_DR,0),0,JEL.ACCOUNTED_CR,JEL.ACCOUNTED_DR)), JEL.EFFECTIVE_DATE, /* 14 */ L1.MEANING, /* 15 */ JEL.STATUS, /* 16 */ JEL.EFFECTIVE_DATE, /* 17 */ JEL.EFFECTIVE_DATE, /* 18 */ TO_DATE(NULL), /* 19 */ JEH.CURRENCY_CONVERSION_DATE, /* 20 */ GDC.USER_CONVERSION_TYPE, /* 21 */ JEH.CURRENCY_CONVERSION_RATE, /* 22 */ TO_NUMBER(NULL), /* 23 */ TO_NUMBER(NULL), /* 24 */ JEB.NAME, /* 25 */ JEB.JE_BATCH_ID, /* 26 */ NULL, /* 27 */ NULL, /* 28 */ NULL, /* 29 */ TO_NUMBER(NULL), /* 30 */ TO_NUMBER(NULL), /* 31 */ NULL, /* 32 */ NULL, /* 33 */ TO_NUMBER(NULL), /* 34 */ CRE.CREATION_DATE, /* 35 */ CRE.CREATED_BY, /* 36 */ CRE.LAST_UPDATE_DATE, /* 37 */ CRE.LAST_UPDATED_BY, /* 38 */ SL.STATEMENT_HEADER_ID, /* 39 */ JEH.JE_HEADER_ID, /* 40 */ ABA.BANK_BRANCH_ID, /* 41 */ TO_DATE(NULL), /* 42 */ NULL, /* 43 */ TO_DATE(NULL), /* 44 */ L3.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 */ NULL, /* 58 */ SH.CONTROL_END_BALANCE, /* 59 */ 101, /* 60 */ 1, /* 61 */ DECODE(JEH.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 62 */ DECODE(NVL(JEL.ENTERED_DR,0),0,JEL.ENTERED_CR,JEL.ENTERED_DR), ABA.CURRENCY_CODE, DECODE(NVL(JEL.ENTERED_DR,0),0,JEL.ENTERED_CR,JEL.ENTERED_DR), DECODE(NVL(JEL.ENTERED_DR,0),0,JEL.ACCOUNTED_CR,JEL.ACCOUNTED_DR)), 'JE_LINE', /* 63 */ CRE.REQUEST_ID /* 64 */ ,to_number(NULL) ,CRE.ORG_ID ,CRE.legal_entity_id FROM CE_STATEMENT_HEADERS SH, CE_TRANSACTION_CODES TC, CE_LOOKUPS L3, CE_LOOKUPS L2, GL_LOOKUPS L1, GL_DAILY_CONVERSION_TYPES GDC, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, CE_BANK_ACCTS_GT_V ABA, GL_JE_BATCHES JEB, GL_JE_HEADERS JEH, GL_JE_LINES JEL, CE_STATEMENT_RECONCILS_ALL CRE, CE_STATEMENT_LINES SL WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID AND L1.LOOKUP_TYPE = 'MJE_BATCH_STATUS' AND L1.LOOKUP_CODE = JEL.STATUS AND L2.LOOKUP_TYPE = 'TRX_TYPE' AND L2.LOOKUP_CODE = DECODE(DECODE(NVL(JEL.ENTERED_DR, 0), 0, NVL(JEL.ACCOUNTED_DR,0), JEL.ENTERED_DR), 0, 'JE_CREDIT','JE_DEBIT') AND L3.LOOKUP_TYPE = 'BANK_TRX_TYPE' AND L3.LOOKUP_CODE = SL.TRX_TYPE AND GDC.CONVERSION_TYPE(+) = JEH.CURRENCY_CONVERSION_TYPE AND SOB.SET_OF_BOOKS_ID (+) = SYS.SET_OF_BOOKS_ID AND JEB.JE_BATCH_ID = JEH.JE_BATCH_ID AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID AND JEL.JE_HEADER_ID = CRE.JE_HEADER_ID AND JEL.JE_LINE_NUM = CRE.REFERENCE_ID AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID /*AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.ORG_ID (+)*/ AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID (+) AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID AND CRE.CURRENT_RECORD_FLAG = 'Y' AND CRE.REFERENCE_TYPE = 'JE_LINE' AND CRE.STATUS_FLAG = 'M'

View Text - HTML Formatted

SELECT JEL.ROWID
, /* 1 */ 'N'
, /* 2 */ SL.STATEMENT_LINE_ID
, /* 3 */ ABA.BANK_ACCOUNT_ID
, /* 4 */ JEL.JE_LINE_NUM
, /* 5 */ DECODE(DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, NVL(JEL.ACCOUNTED_DR
, 0)
, /* 6 */ JEL.ENTERED_DR)
, 0
, 'PAYMENT'
, 'CASH')
, L2.MEANING
, /* 7 */ JEL.DESCRIPTION
, /* 8 */ JEH.CURRENCY_CODE
, /* 9 */ DECODE(JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 10 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(JEL.ENTERED_DR
, JEL.ENTERED_CR)
, /* 11 */ DECODE(JEH.CURRENCY_CODE
, /* 12 */ SOB.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, NVL(JEL.ACCOUNTED_DR
, 0))
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ACCOUNTED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR))
, DECODE(JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 13 */ DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ACCOUNTED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR))
, JEL.EFFECTIVE_DATE
, /* 14 */ L1.MEANING
, /* 15 */ JEL.STATUS
, /* 16 */ JEL.EFFECTIVE_DATE
, /* 17 */ JEL.EFFECTIVE_DATE
, /* 18 */ TO_DATE(NULL)
, /* 19 */ JEH.CURRENCY_CONVERSION_DATE
, /* 20 */ GDC.USER_CONVERSION_TYPE
, /* 21 */ JEH.CURRENCY_CONVERSION_RATE
, /* 22 */ TO_NUMBER(NULL)
, /* 23 */ TO_NUMBER(NULL)
, /* 24 */ JEB.NAME
, /* 25 */ JEB.JE_BATCH_ID
, /* 26 */ NULL
, /* 27 */ NULL
, /* 28 */ NULL
, /* 29 */ TO_NUMBER(NULL)
, /* 30 */ TO_NUMBER(NULL)
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ TO_NUMBER(NULL)
, /* 34 */ CRE.CREATION_DATE
, /* 35 */ CRE.CREATED_BY
, /* 36 */ CRE.LAST_UPDATE_DATE
, /* 37 */ CRE.LAST_UPDATED_BY
, /* 38 */ SL.STATEMENT_HEADER_ID
, /* 39 */ JEH.JE_HEADER_ID
, /* 40 */ ABA.BANK_BRANCH_ID
, /* 41 */ TO_DATE(NULL)
, /* 42 */ NULL
, /* 43 */ TO_DATE(NULL)
, /* 44 */ L3.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 */ NULL
, /* 58 */ SH.CONTROL_END_BALANCE
, /* 59 */ 101
, /* 60 */ 1
, /* 61 */ DECODE(JEH.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 62 */ DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, ABA.CURRENCY_CODE
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ENTERED_CR
, JEL.ENTERED_DR)
, DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, JEL.ACCOUNTED_CR
, JEL.ACCOUNTED_DR))
, 'JE_LINE'
, /* 63 */ CRE.REQUEST_ID /* 64 */
, TO_NUMBER(NULL)
, CRE.ORG_ID
, CRE.LEGAL_ENTITY_ID
FROM CE_STATEMENT_HEADERS SH
, CE_TRANSACTION_CODES TC
, CE_LOOKUPS L3
, CE_LOOKUPS L2
, GL_LOOKUPS L1
, GL_DAILY_CONVERSION_TYPES GDC
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCTS_GT_V ABA
, GL_JE_BATCHES JEB
, GL_JE_HEADERS JEH
, GL_JE_LINES JEL
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_STATEMENT_LINES SL
WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID
AND L1.LOOKUP_TYPE = 'MJE_BATCH_STATUS'
AND L1.LOOKUP_CODE = JEL.STATUS
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = DECODE(DECODE(NVL(JEL.ENTERED_DR
, 0)
, 0
, NVL(JEL.ACCOUNTED_DR
, 0)
, JEL.ENTERED_DR)
, 0
, 'JE_CREDIT'
, 'JE_DEBIT')
AND L3.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L3.LOOKUP_CODE = SL.TRX_TYPE
AND GDC.CONVERSION_TYPE(+) = JEH.CURRENCY_CONVERSION_TYPE
AND SOB.SET_OF_BOOKS_ID (+) = SYS.SET_OF_BOOKS_ID
AND JEB.JE_BATCH_ID = JEH.JE_BATCH_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEL.JE_HEADER_ID = CRE.JE_HEADER_ID
AND JEL.JE_LINE_NUM = CRE.REFERENCE_ID
AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID /*AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.ORG_ID (+)*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID (+)
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND CRE.REFERENCE_TYPE = 'JE_LINE'
AND CRE.STATUS_FLAG = 'M'