View: FA_AEL_SL_MRC_V

Product:	OFA - Assets
Description:
Implementation/DBA Data:	APPS.FA_AEL_SL_MRC_V

View Text

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

Columns