VIEW: APPS.JL_CO_FA_AEL_GL_V

Source

View Text - Preformatted

SELECT DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign(adj.adjustment_amount), -1, abs (adj.adjustment_amount),null) ACCOUNTED_CR, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), 1, abs (adj.adjustment_amount),null) ACCOUNTED_DR, TH.transaction_date_entered ACCOUNTING_DATE, 'ADJ' AEL_TABLE, L1.meaning || ' : ' || to_number(TH.transaction_header_id) AE_LINE_REFERENCE, ADJ.adjustment_type ACCT_LINE_TYPE, ADJ.adjustment_type ACCT_LINE_TYPE_NAME, 140 APPLICATION_ID , ADJ.code_combination_id CODE_COMBINATION_ID, TH.transaction_name COMMENTS, SOB.currency_code CURRENCY_CODE, to_date(null) CURRENCY_CONVERSION_DATE, to_number(null) CURRENCY_CONVERSION_RATE, null CURRENCY_CONVERSION_TYPE, null CURRENCY_USER_CONVERSION_TYPE, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), -1, abs (adj.adjustment_amount),null) ENTERED_CR, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), 1, abs (adj.adjustment_amount),null) ENTERED_DR, decode(ADJ.je_header_id, null,'N', 'Y') GL_TRANSFER_STATUS, L3.meaning GL_TRANSFER_STATUS_NAME, BC.set_of_books_id SET_OF_BOOKS_ID , TH.transaction_header_id SOURCE_ID, 'TH' SOURCE_TABLE, 'TRANSACTION' TRX_CLASS , 'Transaction' TRX_CLASS_NAME , TH.transaction_date_entered TRX_DATE, TH.transaction_header_id TRX_HDR_ID, 'TH' TRX_HDR_TABLE, to_char(TH.transaction_header_id) TRX_NUMBER_DISPLAYED , TH.transaction_header_id TRX_NUMBER_N, TH.transaction_type_code TRX_TYPE_C , L1.meaning TRX_TYPE_NAME , ADJ.last_update_date LAST_UPDATE_DATE, ADJ.last_updated_by LAST_UPDATED_BY, ADJ.last_update_login LAST_UPDATE_LOGIN, TH.asset_id ASSET_ID, AD.asset_number ASSET_NUMBER, AD.description ASSET_DESCRIPTION, TH.book_type_code ASSET_BOOK_TYPE_CODE, SYS.category_flex_structure ASSET_CATEGORY_FLEX_STRUCTURE, AH.category_id ASSET_CATEGORY_ID, SYS.asset_key_flex_structure ASSET_KEY_FLEX_STRUCTURE, AD.asset_key_ccid ASSET_KEY_CCID, ADJ.je_header_id JE_HEADER_ID, ADJ.je_line_num JE_LINE_NUM FROM fa_system_controls SYS, fa_lookups L3, fa_lookups L1, gl_sets_of_books SOB, fa_book_controls BC, fa_asset_history AH, fa_additions AD, fa_adjustments ADJ, fa_transaction_headers TH WHERE L3.lookup_code = decode(ADJ.je_header_id, null,'NO', 'YES') AND L3.lookup_type = 'YESNO' AND L1.lookup_type = 'FAXOLTRX' AND L1.lookup_code = TH.transaction_type_code AND SOB.set_of_books_id = BC.set_of_books_id AND BC.book_type_code = ADJ.book_type_code AND ah.asset_id = ad.asset_id AND th.transaction_header_id >= ah.transaction_header_id_in AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) AND ADJ.SOURCE_TYPE_CODE != 'DEPRECIATION' AND TH.asset_id = AD.asset_id AND TH.transaction_header_id = ADJ.transaction_header_id AND TH.asset_id = ADJ.asset_id AND BC.book_class = decode(BC.book_class,'TAX',decode(BC.global_attribute1,'Y','ADJUSTED',BC.book_class), BC.book_class) UNION ALL SELECT DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), -1, abs (adj.adjustment_amount),null) ACCOUNTED_CR, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), 1, abs (adj.adjustment_amount),null) ACCOUNTED_DR, TH.transaction_date_entered ACCOUNTING_DATE, 'ADJ' AEL_TABLE, L1.meaning|| ' : ' || to_number(TH.transaction_header_id) AE_LINE_REFERENCE, ADJ.adjustment_type ACCT_LINE_TYPE, ADJ.adjustment_type ACCT_LINE_TYPE_NAME, 140 APPLICATION_ID , ADJ.code_combination_id CODE_COMBINATION_ID, TH.transaction_name COMMENTS, SOB.currency_code CURRENCY_CODE, to_date(null) CURRENCY_CONVERSION_DATE, to_number(null) CURRENCY_CONVERSION_RATE, null CURRENCY_CONVERSION_TYPE, null CURRENCY_USER_CONVERSION_TYPE, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), -1, abs (adj.adjustment_amount),null) ENTERED_CR, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount), 1, abs (adj.adjustment_amount),null) ENTERED_DR, decode(ADJ.sequence_line, null,'N', 'Y') GL_TRANSFER_STATUS, L3.meaning GL_TRANSFER_STATUS_NAME, BC.set_of_books_id SET_OF_BOOKS_ID , TH.transaction_header_id SOURCE_ID, 'TH' SOURCE_TABLE, 'TRANSACTION' TRX_CLASS , 'Transaction' TRX_CLASS_NAME , TH.transaction_date_entered TRX_DATE, TH.transaction_header_id TRX_HDR_ID, 'TH' TRX_HDR_TABLE, to_char(TH.transaction_header_id) TRX_NUMBER_DISPLAYED , TH.transaction_header_id TRX_NUMBER_N, TH.transaction_type_code TRX_TYPE_C , L1.meaning TRX_TYPE_NAME , ADJ.last_update_date LAST_UPDATE_DATE, ADJ.last_updated_by LAST_UPDATED_BY, ADJ.last_update_login LAST_UPDATE_LOGIN, TH.asset_id ASSET_ID, AD.asset_number ASSET_NUMBER, AD.description ASSET_DESCRIPTION, ADJ.book_type_code ASSET_BOOK_TYPE_CODE, SYS.category_flex_structure ASSET_CATEGORY_FLEX_STRUCTURE, AH.category_id ASSET_CATEGORY_ID, SYS.asset_key_flex_structure ASSET_KEY_FLEX_STRUCTURE, AD.asset_key_ccid ASSET_KEY_CCID, GIR.je_header_id JE_HEADER_ID, GIR.je_line_num JE_LINE_NUM FROM fa_system_controls SYS, fa_lookups L3, fa_lookups L1, gl_sets_of_books SOB, fa_book_controls BC, fa_asset_history AH, fa_additions AD, jl_co_fa_adjustments ADJ, fa_transaction_headers TH, gl_import_references GIR WHERE L3.lookup_code = decode(ADJ.sequence_line, null,'NO', 'YES') AND L3.lookup_type = 'YESNO' AND L1.lookup_type = 'FAXOLTRX' AND L1.lookup_code = TH.transaction_type_code AND SOB.set_of_books_id = BC.set_of_books_id AND BC.book_type_code = ADJ.book_type_code AND ah.asset_id = ad.asset_id AND th.transaction_header_id >= ah.transaction_header_id_in AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) AND ADJ.SOURCE_TYPE_CODE != 'DEPRECIATION' AND TH.asset_id = AD.asset_id AND TH.transaction_header_id = ADJ.transaction_header_id AND TH.asset_id = ADJ.asset_id AND ADJ.je_category_name = GIR.reference_1 AND to_char(ADJ.sequence_line)= GIR.reference_2

View Text - HTML Formatted

SELECT DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN(ADJ.ADJUSTMENT_AMOUNT)
, -1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ACCOUNTED_CR
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, 1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ACCOUNTED_DR
, TH.TRANSACTION_DATE_ENTERED ACCOUNTING_DATE
, 'ADJ' AEL_TABLE
, L1.MEANING || ' : ' || TO_NUMBER(TH.TRANSACTION_HEADER_ID) AE_LINE_REFERENCE
, ADJ.ADJUSTMENT_TYPE ACCT_LINE_TYPE
, ADJ.ADJUSTMENT_TYPE ACCT_LINE_TYPE_NAME
, 140 APPLICATION_ID
, ADJ.CODE_COMBINATION_ID CODE_COMBINATION_ID
, TH.TRANSACTION_NAME COMMENTS
, SOB.CURRENCY_CODE CURRENCY_CODE
, TO_DATE(NULL) CURRENCY_CONVERSION_DATE
, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE
, NULL CURRENCY_CONVERSION_TYPE
, NULL CURRENCY_USER_CONVERSION_TYPE
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, -1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ENTERED_CR
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, 1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ENTERED_DR
, DECODE(ADJ.JE_HEADER_ID
, NULL
, 'N'
, 'Y') GL_TRANSFER_STATUS
, L3.MEANING GL_TRANSFER_STATUS_NAME
, BC.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, TH.TRANSACTION_HEADER_ID SOURCE_ID
, 'TH' SOURCE_TABLE
, 'TRANSACTION' TRX_CLASS
, 'TRANSACTION' TRX_CLASS_NAME
, TH.TRANSACTION_DATE_ENTERED TRX_DATE
, TH.TRANSACTION_HEADER_ID TRX_HDR_ID
, 'TH' TRX_HDR_TABLE
, TO_CHAR(TH.TRANSACTION_HEADER_ID) TRX_NUMBER_DISPLAYED
, TH.TRANSACTION_HEADER_ID TRX_NUMBER_N
, TH.TRANSACTION_TYPE_CODE TRX_TYPE_C
, L1.MEANING TRX_TYPE_NAME
, ADJ.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ADJ.LAST_UPDATED_BY LAST_UPDATED_BY
, ADJ.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, TH.ASSET_ID ASSET_ID
, AD.ASSET_NUMBER ASSET_NUMBER
, AD.DESCRIPTION ASSET_DESCRIPTION
, TH.BOOK_TYPE_CODE ASSET_BOOK_TYPE_CODE
, SYS.CATEGORY_FLEX_STRUCTURE ASSET_CATEGORY_FLEX_STRUCTURE
, AH.CATEGORY_ID ASSET_CATEGORY_ID
, SYS.ASSET_KEY_FLEX_STRUCTURE ASSET_KEY_FLEX_STRUCTURE
, AD.ASSET_KEY_CCID ASSET_KEY_CCID
, ADJ.JE_HEADER_ID JE_HEADER_ID
, ADJ.JE_LINE_NUM JE_LINE_NUM
FROM FA_SYSTEM_CONTROLS SYS
, FA_LOOKUPS L3
, FA_LOOKUPS L1
, GL_SETS_OF_BOOKS SOB
, FA_BOOK_CONTROLS BC
, FA_ASSET_HISTORY AH
, FA_ADDITIONS AD
, FA_ADJUSTMENTS ADJ
, FA_TRANSACTION_HEADERS TH
WHERE L3.LOOKUP_CODE = DECODE(ADJ.JE_HEADER_ID
, NULL
, 'NO'
, 'YES')
AND L3.LOOKUP_TYPE = 'YESNO'
AND L1.LOOKUP_TYPE = 'FAXOLTRX'
AND L1.LOOKUP_CODE = TH.TRANSACTION_TYPE_CODE
AND SOB.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID
AND BC.BOOK_TYPE_CODE = ADJ.BOOK_TYPE_CODE
AND AH.ASSET_ID = AD.ASSET_ID
AND TH.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN
AND TH.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT
, TH.TRANSACTION_HEADER_ID + 1)
AND ADJ.SOURCE_TYPE_CODE != 'DEPRECIATION'
AND TH.ASSET_ID = AD.ASSET_ID
AND TH.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID
AND TH.ASSET_ID = ADJ.ASSET_ID
AND BC.BOOK_CLASS = DECODE(BC.BOOK_CLASS
, 'TAX'
, DECODE(BC.GLOBAL_ATTRIBUTE1
, 'Y'
, 'ADJUSTED'
, BC.BOOK_CLASS)
, BC.BOOK_CLASS) UNION ALL SELECT DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, -1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ACCOUNTED_CR
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, 1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ACCOUNTED_DR
, TH.TRANSACTION_DATE_ENTERED ACCOUNTING_DATE
, 'ADJ' AEL_TABLE
, L1.MEANING|| ' : ' || TO_NUMBER(TH.TRANSACTION_HEADER_ID) AE_LINE_REFERENCE
, ADJ.ADJUSTMENT_TYPE ACCT_LINE_TYPE
, ADJ.ADJUSTMENT_TYPE ACCT_LINE_TYPE_NAME
, 140 APPLICATION_ID
, ADJ.CODE_COMBINATION_ID CODE_COMBINATION_ID
, TH.TRANSACTION_NAME COMMENTS
, SOB.CURRENCY_CODE CURRENCY_CODE
, TO_DATE(NULL) CURRENCY_CONVERSION_DATE
, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE
, NULL CURRENCY_CONVERSION_TYPE
, NULL CURRENCY_USER_CONVERSION_TYPE
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, -1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ENTERED_CR
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT)
, 1
, ABS (ADJ.ADJUSTMENT_AMOUNT)
, NULL) ENTERED_DR
, DECODE(ADJ.SEQUENCE_LINE
, NULL
, 'N'
, 'Y') GL_TRANSFER_STATUS
, L3.MEANING GL_TRANSFER_STATUS_NAME
, BC.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, TH.TRANSACTION_HEADER_ID SOURCE_ID
, 'TH' SOURCE_TABLE
, 'TRANSACTION' TRX_CLASS
, 'TRANSACTION' TRX_CLASS_NAME
, TH.TRANSACTION_DATE_ENTERED TRX_DATE
, TH.TRANSACTION_HEADER_ID TRX_HDR_ID
, 'TH' TRX_HDR_TABLE
, TO_CHAR(TH.TRANSACTION_HEADER_ID) TRX_NUMBER_DISPLAYED
, TH.TRANSACTION_HEADER_ID TRX_NUMBER_N
, TH.TRANSACTION_TYPE_CODE TRX_TYPE_C
, L1.MEANING TRX_TYPE_NAME
, ADJ.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ADJ.LAST_UPDATED_BY LAST_UPDATED_BY
, ADJ.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, TH.ASSET_ID ASSET_ID
, AD.ASSET_NUMBER ASSET_NUMBER
, AD.DESCRIPTION ASSET_DESCRIPTION
, ADJ.BOOK_TYPE_CODE ASSET_BOOK_TYPE_CODE
, SYS.CATEGORY_FLEX_STRUCTURE ASSET_CATEGORY_FLEX_STRUCTURE
, AH.CATEGORY_ID ASSET_CATEGORY_ID
, SYS.ASSET_KEY_FLEX_STRUCTURE ASSET_KEY_FLEX_STRUCTURE
, AD.ASSET_KEY_CCID ASSET_KEY_CCID
, GIR.JE_HEADER_ID JE_HEADER_ID
, GIR.JE_LINE_NUM JE_LINE_NUM
FROM FA_SYSTEM_CONTROLS SYS
, FA_LOOKUPS L3
, FA_LOOKUPS L1
, GL_SETS_OF_BOOKS SOB
, FA_BOOK_CONTROLS BC
, FA_ASSET_HISTORY AH
, FA_ADDITIONS AD
, JL_CO_FA_ADJUSTMENTS ADJ
, FA_TRANSACTION_HEADERS TH
, GL_IMPORT_REFERENCES GIR
WHERE L3.LOOKUP_CODE = DECODE(ADJ.SEQUENCE_LINE
, NULL
, 'NO'
, 'YES')
AND L3.LOOKUP_TYPE = 'YESNO'
AND L1.LOOKUP_TYPE = 'FAXOLTRX'
AND L1.LOOKUP_CODE = TH.TRANSACTION_TYPE_CODE
AND SOB.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID
AND BC.BOOK_TYPE_CODE = ADJ.BOOK_TYPE_CODE
AND AH.ASSET_ID = AD.ASSET_ID
AND TH.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN
AND TH.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT
, TH.TRANSACTION_HEADER_ID + 1)
AND ADJ.SOURCE_TYPE_CODE != 'DEPRECIATION'
AND TH.ASSET_ID = AD.ASSET_ID
AND TH.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID
AND TH.ASSET_ID = ADJ.ASSET_ID
AND ADJ.JE_CATEGORY_NAME = GIR.REFERENCE_1
AND TO_CHAR(ADJ.SEQUENCE_LINE)= GIR.REFERENCE_2