VIEW: APPS.FA_AEL_SL_MRC_V

Source

View Text - Preformatted

SELECT /*+ leading(trx) */ DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount- decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0)), -1, abs (adj.adjustment_amount - decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0)),null) ACCOUNTED_CR, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount- decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0)), 1, abs (adj.adjustment_amount - decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0) ),null) ACCOUNTED_DR, TRX.transaction_date_entered ACCOUNTING_DATE, 'ADJ' AEL_TABLE, L1.meaning || ' : ' || to_number(TRX.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, TRX.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 - decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0)), -1, abs (adj.adjustment_amount - decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0) ),null) ENTERED_CR, DECODE (decode (adj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) * sign (adj.adjustment_amount - decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0)), 1, abs (adj.adjustment_amount - decode(adj.adjustment_type,'RESERVE',nvl(adj_bonus.adjustment_amount,0),0) ),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 , TRX.transaction_header_id SOURCE_ID, 'TH' SOURCE_TABLE, 'TRANSACTION' TRX_CLASS , 'Transaction' TRX_CLASS_NAME , TRX.transaction_date_entered TRX_DATE, TRX.transaction_header_id TRX_HDR_ID, 'TH' TRX_HDR_TABLE, to_char(TRX.transaction_header_id) TRX_NUMBER_DISPLAYED , TRX.transaction_header_id TRX_NUMBER_N, TRX.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, /* The following columns are specific to Assets Transactions */ TRX.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, 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_mrc_v BC, fa_asset_history AH, fa_additions AD, fa_adjustments_mrc_v ADJ, fa_adjustments_mrc_v adj_bonus, (SELECT /*+ leading(trxgt) cardinality(trxgt, 10) index(th FA_TRANSACTION_HEADERS_U1) */ th.transaction_header_id, th.transaction_date_entered, th.transaction_name, th.transaction_type_code, th.book_type_code, th.asset_id FROM fa_inquiry_trx_gt trxgt, fa_transaction_headers th WHERE trxgt.transaction_header_id = th.transaction_header_id union select /*+ leading(trxgt) cardinality(trxgt, 10) index(th FA_TRANSACTION_HEADERS_N1) */ th.transaction_header_id, th.transaction_date_entered, th.transaction_name, th.transaction_type_code, th.book_type_code, th.asset_id from fa_inquiry_trx_gt trxgt, fa_transaction_headers th where TH.ASSET_ID = trxgt.asset_id AND TH.BOOK_TYPE_CODE = trxgt.book_type_code) trx 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 = TRX.transaction_type_code AND SOB.set_of_books_id = BC.set_of_books_id AND BC.book_type_code = TRX.book_type_code AND ah.asset_id = ad.asset_id AND trx.transaction_header_id >= ah.transaction_header_id_in AND trx.transaction_header_id < nvl(ah.transaction_header_id_out, trx.transaction_header_id + 1) AND ADJ.SOURCE_TYPE_CODE != 'DEPRECIATION' AND adj.adjustment_type != decode(adj.source_type_code,'RETIREMENT','EXPENSE','NULL') AND adj.adjustment_type <> decode(adj.source_type_code,'RETIREMENT','REVAL EXPENSE','NULL') and adj_bonus.transaction_header_id(+) = adj.transaction_header_id and adj_bonus.distribution_id(+) = adj.distribution_id and adj_bonus.asset_id(+) = adj.asset_id and adj_bonus.adjustment_type(+) = 'BONUS RESERVE' and adj_bonus.adjustment_amount(+) <> 0 and adj_bonus.book_type_code(+) = adj.book_type_code AND TRX.asset_id = AD.asset_id AND TRX.transaction_header_id = ADJ.transaction_header_id AND TRX.asset_id = ADJ.asset_id AND ADJ.adjustment_amount + nvl(adj_bonus.adjustment_amount,0) <> 0

View Text - HTML Formatted

SELECT /*+ LEADING(TRX) */ DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT- DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0))
, -1
, ABS (ADJ.ADJUSTMENT_AMOUNT - DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0))
, NULL) ACCOUNTED_CR
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT- DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0))
, 1
, ABS (ADJ.ADJUSTMENT_AMOUNT - DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0) )
, NULL) ACCOUNTED_DR
, TRX.TRANSACTION_DATE_ENTERED ACCOUNTING_DATE
, 'ADJ' AEL_TABLE
, L1.MEANING || ' : ' || TO_NUMBER(TRX.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
, TRX.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 - DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0))
, -1
, ABS (ADJ.ADJUSTMENT_AMOUNT - DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0) )
, NULL) ENTERED_CR
, DECODE (DECODE (ADJ.DEBIT_CREDIT_FLAG
, 'DR'
, 1
, 'CR'
, -1
, 0) * SIGN (ADJ.ADJUSTMENT_AMOUNT - DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0))
, 1
, ABS (ADJ.ADJUSTMENT_AMOUNT - DECODE(ADJ.ADJUSTMENT_TYPE
, 'RESERVE'
, NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0)
, 0) )
, 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
, TRX.TRANSACTION_HEADER_ID SOURCE_ID
, 'TH' SOURCE_TABLE
, 'TRANSACTION' TRX_CLASS
, 'TRANSACTION' TRX_CLASS_NAME
, TRX.TRANSACTION_DATE_ENTERED TRX_DATE
, TRX.TRANSACTION_HEADER_ID TRX_HDR_ID
, 'TH' TRX_HDR_TABLE
, TO_CHAR(TRX.TRANSACTION_HEADER_ID) TRX_NUMBER_DISPLAYED
, TRX.TRANSACTION_HEADER_ID TRX_NUMBER_N
, TRX.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
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO ASSETS TRANSACTIONS */ TRX.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
, 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_MRC_V BC
, FA_ASSET_HISTORY AH
, FA_ADDITIONS AD
, FA_ADJUSTMENTS_MRC_V ADJ
, FA_ADJUSTMENTS_MRC_V ADJ_BONUS
, (SELECT /*+ LEADING(TRXGT) CARDINALITY(TRXGT
, 10) INDEX(TH FA_TRANSACTION_HEADERS_U1) */ TH.TRANSACTION_HEADER_ID
, TH.TRANSACTION_DATE_ENTERED
, TH.TRANSACTION_NAME
, TH.TRANSACTION_TYPE_CODE
, TH.BOOK_TYPE_CODE
, TH.ASSET_ID
FROM FA_INQUIRY_TRX_GT TRXGT
, FA_TRANSACTION_HEADERS TH
WHERE TRXGT.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID UNION SELECT /*+ LEADING(TRXGT) CARDINALITY(TRXGT
, 10) INDEX(TH FA_TRANSACTION_HEADERS_N1) */ TH.TRANSACTION_HEADER_ID
, TH.TRANSACTION_DATE_ENTERED
, TH.TRANSACTION_NAME
, TH.TRANSACTION_TYPE_CODE
, TH.BOOK_TYPE_CODE
, TH.ASSET_ID
FROM FA_INQUIRY_TRX_GT TRXGT
, FA_TRANSACTION_HEADERS TH
WHERE TH.ASSET_ID = TRXGT.ASSET_ID
AND TH.BOOK_TYPE_CODE = TRXGT.BOOK_TYPE_CODE) TRX
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 = TRX.TRANSACTION_TYPE_CODE
AND SOB.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID
AND BC.BOOK_TYPE_CODE = TRX.BOOK_TYPE_CODE
AND AH.ASSET_ID = AD.ASSET_ID
AND TRX.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN
AND TRX.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT
, TRX.TRANSACTION_HEADER_ID + 1)
AND ADJ.SOURCE_TYPE_CODE != 'DEPRECIATION'
AND ADJ.ADJUSTMENT_TYPE != DECODE(ADJ.SOURCE_TYPE_CODE
, 'RETIREMENT'
, 'EXPENSE'
, 'NULL')
AND ADJ.ADJUSTMENT_TYPE <> DECODE(ADJ.SOURCE_TYPE_CODE
, 'RETIREMENT'
, 'REVAL EXPENSE'
, 'NULL')
AND ADJ_BONUS.TRANSACTION_HEADER_ID(+) = ADJ.TRANSACTION_HEADER_ID
AND ADJ_BONUS.DISTRIBUTION_ID(+) = ADJ.DISTRIBUTION_ID
AND ADJ_BONUS.ASSET_ID(+) = ADJ.ASSET_ID
AND ADJ_BONUS.ADJUSTMENT_TYPE(+) = 'BONUS RESERVE'
AND ADJ_BONUS.ADJUSTMENT_AMOUNT(+) <> 0
AND ADJ_BONUS.BOOK_TYPE_CODE(+) = ADJ.BOOK_TYPE_CODE
AND TRX.ASSET_ID = AD.ASSET_ID
AND TRX.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID
AND TRX.ASSET_ID = ADJ.ASSET_ID
AND ADJ.ADJUSTMENT_AMOUNT + NVL(ADJ_BONUS.ADJUSTMENT_AMOUNT
, 0) <> 0