VIEW: APPS.OKL_CS_ASSET_LINES_V

Source

View Text - Preformatted

SELECT CLE.ID ID1, '#' ID2, FA.ASSET_NUMBER NAME, FAT.DESCRIPTION DESCRIPTION, TOP_LINE.ITEM_DESCRIPTION ITEM_DESCRIPTION, TL.COMMENTS COMMENTS, CLE.CHR_ID CHR_ID, CLE.DNZ_CHR_ID DNZ_CHR_ID, LS.LTY_CODE LTY_CODE, LS.LSE_TYPE LSE_TYPE, LS.LSE_PARENT_ID LSE_PARENT_ID, CLE.CLE_ID PARENT_LINE_ID, CLE.LINE_NUMBER LINE_NUMBER, CLE.DATE_TERMINATED DATE_TERMINATED, CLE.START_DATE START_DATE_ACTIVE, CLE.END_DATE END_DATE_ACTIVE, DECODE(SIGN(TRUNC(SYSDATE)-NVL(CLE.start_date,TRUNC(SYSDATE))),-1,'I', DECODE(SIGN(TRUNC(SYSDATE) - NVL(CLE.end_date,TRUNC(SYSDATE))), 1,'I','A')) STATUS, to_number(CIM.OBJECT1_ID1) ASSET_ID, FA.CURRENT_UNITS QUANTITY, CIM.UOM_CODE UNIT_OF_MEASURE_CODE, FA.ASSET_NUMBER ASSET_NUMBER, FAB.BOOK_TYPE_CODE CORPORATE_BOOK, FAB.LIFE_IN_MONTHS LIFE_IN_MONTHS, TOP.OEC ORIGINAL_COST, TOP.OEC COST, FAB.ADJUSTED_COST ADJUSTED_COST, FA.TAG_NUMBER TAG_NUMBER, FA.CURRENT_UNITS CURRENT_UNITS, FA.SERIAL_NUMBER SERIAL_NUMBER, FAB.REVAL_CEILING REVAL_CEILING, FA.NEW_USED NEW_USED, FAB.date_placed_in_service IN_SERVICE_DATE, FA.MANUFACTURER_NAME MANUFACTURER_NAME, FA.MODEL_NUMBER MODEL_NUMBER, FA.ASSET_TYPE ASSET_TYPE, FAB.SALVAGE_VALUE SALVAGE_VALUE, FAB.PERCENT_SALVAGE_VALUE PERCENT_SALVAGE_VALUE, FA.asset_category_id DEPRECIATION_CATEGORY, FAB.deprn_start_date DEPRN_START_DATE, FAB.DEPRN_METHOD_CODE DEPRN_METHOD_CODE, FAB.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR, FAB.BASIC_RATE BASIC_RATE, FAB.ADJUSTED_RATE ADJUSTED_RATE, FAB.RECOVERABLE_COST RECOVERABLE_COST, FBC.ORG_ID ORG_ID, FBC.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, FA.property_type_code PROPERTY_TYPE_CODE, FA.property_1245_1250_code PROPERTY_1245_1250_CODE, FA.in_use_flag IN_USE_FLAG, FA.owned_leased OWNED_LEASED, FA.inventorial INVENTORIAL, CLE.STS_CODE LINE_STATUS, STS.MEANING ASSET_STATUS, KLE.YEAR_BUILT YEAR_BUILT FROM OKC_STATUSES_V STS,OKC_K_LINES_B CLE,OKC_K_LINES_TL TL,OKC_K_LINES_TL TOP_LINE,OKL_K_LINES TOP,OKC_LINE_STYLES_B LS,OKC_K_ITEMS CIM,OKL_K_LINES KLE,FA_BOOKS FAB,FA_ADDITIONS_B FA,FA_ADDITIONS_TL FAT,FA_BOOK_CONTROLS FBC,OKC_K_HEADERS_B CHR WHERE CLE.DNZ_CHR_ID = CHR.ID AND TL.ID = CLE.ID AND TL.LANGUAGE(+) = USERENV('LANG') AND LS.ID = CLE.LSE_ID AND TOP_LINE.ID = CLE.CLE_ID AND TOP_LINE.LANGUAGE(+) = USERENV('LANG') AND TOP_LINE.ID = TOP.ID AND CIM.CLE_ID = CLE.ID AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET' AND to_char(FA.ASSET_ID) = CIM.OBJECT1_ID1 AND CIM.OBJECT1_ID2 = '#' AND FAB.ASSET_ID = FA.ASSET_ID AND FA.ASSET_ID = FAT.ASSET_ID AND FAT.LANGUAGE = USERENV('LANG') AND FAB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE AND FBC.BOOK_CLASS = 'CORPORATE' AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL AND KLE.ID = CLE.ID AND CLE.STS_CODE = STS.CODE AND CLE.STS_CODE <> 'ABANDONED' UNION SELECT CLE.ID ID1, '#' ID2, DECODE(TL.NAME,NULL,TXLA.ASSET_NUMBER,TL.NAME) NAME, TOP_LINE_TL.ITEM_DESCRIPTION DESCRIPTION, TOP_LINE_TL.ITEM_DESCRIPTION ITEM_DESCRIPTION, TL.COMMENTS COMMENTS, CLE.CHR_ID CHR_ID, CLE.DNZ_CHR_ID DNZ_CHR_ID, LS.LTY_CODE LTY_CODE, LS.LSE_TYPE LSE_TYPE, LS.LSE_PARENT_ID LSE_PARENT_ID, CLE.CLE_ID PARENT_LINE_ID, CLE.LINE_NUMBER LINE_NUMBER, CLE.DATE_TERMINATED DATE_TERMINATED, CLE.START_DATE START_DATE_ACTIVE, CLE.END_DATE END_DATE_ACTIVE, DECODE(SIGN(TRUNC(SYSDATE)-NVL(CLE.start_date, TRUNC(SYSDATE))), -1, 'I', DECODE(SIGN(TRUNC(SYSDATE) - NVL(CLE.end_date, TRUNC(SYSDATE))), 1, 'I', 'A')) STATUS, NVL(NULL, 0) ASSET_ID, TXLA.CURRENT_UNITS QUANTITY, NULL UNIT_OF_MEASURE_CODE, TXLA.ASSET_NUMBER ASSET_NUMBER, TXLA.CORPORATE_BOOK CORPORATE_BOOK, TXLA.LIFE_IN_MONTHS LIFE_IN_MONTHS, NVL(TOP_LINE.OEC, 0) ORIGINAL_COST, NVL(TOP_LINE.OEC, 0) COST, NVL(TOP_LINE.OEC,0) ADJUSTED_COST, TXLA.TAG_NUMBER TAG_NUMBER, TXLA.CURRENT_UNITS CURRENT_UNITS, NULL SERIAL_NUMBER, TO_NUMBER(NULL) REVAL_CEILING, NULL NEW_USED, TXLA.IN_SERVICE_DATE IN_SERVICE_DATE, TXLA.MANUFACTURER_NAME MANUFACTURER_NAME, TXLA.MODEL_NUMBER MODEL_NUMBER, NULL ASSET_TYPE, TXLA.SALVAGE_VALUE SALVAGE_VALUE, TXLA.PERCENT_SALVAGE_VALUE PERCENT_SALVAGE_VALUE, TXLA.DEPRECIATION_ID DEPRECIATION_CATEGORY, TO_DATE(NULL, 'dd-mon-yyyy') DEPRN_START_DATE, TXLA.DEPRN_METHOD DEPRN_METHOD_CODE, TO_NUMBER(NULL) RATE_ADJUSTMENT_FACTOR, TXLA.DEPRN_RATE BASIC_RATE, TO_NUMBER(NULL) ADJUSTED_RATE, TO_NUMBER(NULL) RECOVERABLE_COST, TXLA.ORG_ID ORG_ID, TO_NUMBER(NULL) SET_OF_BOOKS_ID, NULL PROPERTY_TYPE_CODE, NULL PROPERTY_1245_1250_CODE, NULL IN_USE_FLAG, NULL OWNED_LEASED, NULL INVENTORIAL, CLE.STS_CODE LINE_STATUS, STS.MEANING ASSET_STATUS, KLE.YEAR_BUILT YEAR_BUILT FROM OKL_TXL_ASSETS_B TXLA, OKC_K_LINES_B CLE, OKC_K_LINES_TL TL, OKC_K_LINES_TL TOP_LINE_TL, OKL_K_LINES TOP_LINE, OKC_LINE_STYLES_B LS , OKL_K_HEADERS KHR, OKL_K_LINES TOP_KLE, OKL_K_LINES KLE , OKC_STATUSES_V STS, OKC_K_HEADERS_B CHR WHERE CLE.DNZ_CHR_ID = CHR.ID AND TL.ID = CLE.ID AND TL.LANGUAGE(+) = USERENV('LANG') AND LS.ID = CLE.LSE_ID AND TOP_LINE_TL.ID = TOP_LINE.ID AND TOP_LINE_TL.LANGUAGE(+) = USERENV('LANG') AND TOP_LINE.ID = CLE.CLE_ID AND LS.LTY_CODE = 'FIXED_ASSET' AND TXLA.KLE_ID(+) = CLE.ID AND TXLA.TAL_TYPE IN ( 'CFA','CRB') AND NOT EXISTS ( SELECT '1' FROM okc_k_items fa_cim WHERE fa_cim.cle_id = cle.id AND fa_cim.object1_id1 IS NOT NULL) AND KHR.ID = CLE.DNZ_CHR_ID AND TOP_KLE.ID = CLE.CLE_ID AND KLE.ID = CLE.ID AND CLE.STS_CODE = STS.CODE AND CLE.STS_CODE <> 'ABANDONED'

View Text - HTML Formatted

SELECT CLE.ID ID1
, '#' ID2
, FA.ASSET_NUMBER NAME
, FAT.DESCRIPTION DESCRIPTION
, TOP_LINE.ITEM_DESCRIPTION ITEM_DESCRIPTION
, TL.COMMENTS COMMENTS
, CLE.CHR_ID CHR_ID
, CLE.DNZ_CHR_ID DNZ_CHR_ID
, LS.LTY_CODE LTY_CODE
, LS.LSE_TYPE LSE_TYPE
, LS.LSE_PARENT_ID LSE_PARENT_ID
, CLE.CLE_ID PARENT_LINE_ID
, CLE.LINE_NUMBER LINE_NUMBER
, CLE.DATE_TERMINATED DATE_TERMINATED
, CLE.START_DATE START_DATE_ACTIVE
, CLE.END_DATE END_DATE_ACTIVE
, DECODE(SIGN(TRUNC(SYSDATE)-NVL(CLE.START_DATE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE) - NVL(CLE.END_DATE
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')) STATUS
, TO_NUMBER(CIM.OBJECT1_ID1) ASSET_ID
, FA.CURRENT_UNITS QUANTITY
, CIM.UOM_CODE UNIT_OF_MEASURE_CODE
, FA.ASSET_NUMBER ASSET_NUMBER
, FAB.BOOK_TYPE_CODE CORPORATE_BOOK
, FAB.LIFE_IN_MONTHS LIFE_IN_MONTHS
, TOP.OEC ORIGINAL_COST
, TOP.OEC COST
, FAB.ADJUSTED_COST ADJUSTED_COST
, FA.TAG_NUMBER TAG_NUMBER
, FA.CURRENT_UNITS CURRENT_UNITS
, FA.SERIAL_NUMBER SERIAL_NUMBER
, FAB.REVAL_CEILING REVAL_CEILING
, FA.NEW_USED NEW_USED
, FAB.DATE_PLACED_IN_SERVICE IN_SERVICE_DATE
, FA.MANUFACTURER_NAME MANUFACTURER_NAME
, FA.MODEL_NUMBER MODEL_NUMBER
, FA.ASSET_TYPE ASSET_TYPE
, FAB.SALVAGE_VALUE SALVAGE_VALUE
, FAB.PERCENT_SALVAGE_VALUE PERCENT_SALVAGE_VALUE
, FA.ASSET_CATEGORY_ID DEPRECIATION_CATEGORY
, FAB.DEPRN_START_DATE DEPRN_START_DATE
, FAB.DEPRN_METHOD_CODE DEPRN_METHOD_CODE
, FAB.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, FAB.BASIC_RATE BASIC_RATE
, FAB.ADJUSTED_RATE ADJUSTED_RATE
, FAB.RECOVERABLE_COST RECOVERABLE_COST
, FBC.ORG_ID ORG_ID
, FBC.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, FA.PROPERTY_TYPE_CODE PROPERTY_TYPE_CODE
, FA.PROPERTY_1245_1250_CODE PROPERTY_1245_1250_CODE
, FA.IN_USE_FLAG IN_USE_FLAG
, FA.OWNED_LEASED OWNED_LEASED
, FA.INVENTORIAL INVENTORIAL
, CLE.STS_CODE LINE_STATUS
, STS.MEANING ASSET_STATUS
, KLE.YEAR_BUILT YEAR_BUILT
FROM OKC_STATUSES_V STS
, OKC_K_LINES_B CLE
, OKC_K_LINES_TL TL
, OKC_K_LINES_TL TOP_LINE
, OKL_K_LINES TOP
, OKC_LINE_STYLES_B LS
, OKC_K_ITEMS CIM
, OKL_K_LINES KLE
, FA_BOOKS FAB
, FA_ADDITIONS_B FA
, FA_ADDITIONS_TL FAT
, FA_BOOK_CONTROLS FBC
, OKC_K_HEADERS_B CHR
WHERE CLE.DNZ_CHR_ID = CHR.ID
AND TL.ID = CLE.ID
AND TL.LANGUAGE(+) = USERENV('LANG')
AND LS.ID = CLE.LSE_ID
AND TOP_LINE.ID = CLE.CLE_ID
AND TOP_LINE.LANGUAGE(+) = USERENV('LANG')
AND TOP_LINE.ID = TOP.ID
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND TO_CHAR(FA.ASSET_ID) = CIM.OBJECT1_ID1
AND CIM.OBJECT1_ID2 = '#'
AND FAB.ASSET_ID = FA.ASSET_ID
AND FA.ASSET_ID = FAT.ASSET_ID
AND FAT.LANGUAGE = USERENV('LANG')
AND FAB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND FBC.BOOK_CLASS = 'CORPORATE'
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND KLE.ID = CLE.ID
AND CLE.STS_CODE = STS.CODE
AND CLE.STS_CODE <> 'ABANDONED' UNION SELECT CLE.ID ID1
, '#' ID2
, DECODE(TL.NAME
, NULL
, TXLA.ASSET_NUMBER
, TL.NAME) NAME
, TOP_LINE_TL.ITEM_DESCRIPTION DESCRIPTION
, TOP_LINE_TL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, TL.COMMENTS COMMENTS
, CLE.CHR_ID CHR_ID
, CLE.DNZ_CHR_ID DNZ_CHR_ID
, LS.LTY_CODE LTY_CODE
, LS.LSE_TYPE LSE_TYPE
, LS.LSE_PARENT_ID LSE_PARENT_ID
, CLE.CLE_ID PARENT_LINE_ID
, CLE.LINE_NUMBER LINE_NUMBER
, CLE.DATE_TERMINATED DATE_TERMINATED
, CLE.START_DATE START_DATE_ACTIVE
, CLE.END_DATE END_DATE_ACTIVE
, DECODE(SIGN(TRUNC(SYSDATE)-NVL(CLE.START_DATE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE) - NVL(CLE.END_DATE
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')) STATUS
, NVL(NULL
, 0) ASSET_ID
, TXLA.CURRENT_UNITS QUANTITY
, NULL UNIT_OF_MEASURE_CODE
, TXLA.ASSET_NUMBER ASSET_NUMBER
, TXLA.CORPORATE_BOOK CORPORATE_BOOK
, TXLA.LIFE_IN_MONTHS LIFE_IN_MONTHS
, NVL(TOP_LINE.OEC
, 0) ORIGINAL_COST
, NVL(TOP_LINE.OEC
, 0) COST
, NVL(TOP_LINE.OEC
, 0) ADJUSTED_COST
, TXLA.TAG_NUMBER TAG_NUMBER
, TXLA.CURRENT_UNITS CURRENT_UNITS
, NULL SERIAL_NUMBER
, TO_NUMBER(NULL) REVAL_CEILING
, NULL NEW_USED
, TXLA.IN_SERVICE_DATE IN_SERVICE_DATE
, TXLA.MANUFACTURER_NAME MANUFACTURER_NAME
, TXLA.MODEL_NUMBER MODEL_NUMBER
, NULL ASSET_TYPE
, TXLA.SALVAGE_VALUE SALVAGE_VALUE
, TXLA.PERCENT_SALVAGE_VALUE PERCENT_SALVAGE_VALUE
, TXLA.DEPRECIATION_ID DEPRECIATION_CATEGORY
, TO_DATE(NULL
, 'DD-MON-YYYY') DEPRN_START_DATE
, TXLA.DEPRN_METHOD DEPRN_METHOD_CODE
, TO_NUMBER(NULL) RATE_ADJUSTMENT_FACTOR
, TXLA.DEPRN_RATE BASIC_RATE
, TO_NUMBER(NULL) ADJUSTED_RATE
, TO_NUMBER(NULL) RECOVERABLE_COST
, TXLA.ORG_ID ORG_ID
, TO_NUMBER(NULL) SET_OF_BOOKS_ID
, NULL PROPERTY_TYPE_CODE
, NULL PROPERTY_1245_1250_CODE
, NULL IN_USE_FLAG
, NULL OWNED_LEASED
, NULL INVENTORIAL
, CLE.STS_CODE LINE_STATUS
, STS.MEANING ASSET_STATUS
, KLE.YEAR_BUILT YEAR_BUILT
FROM OKL_TXL_ASSETS_B TXLA
, OKC_K_LINES_B CLE
, OKC_K_LINES_TL TL
, OKC_K_LINES_TL TOP_LINE_TL
, OKL_K_LINES TOP_LINE
, OKC_LINE_STYLES_B LS
, OKL_K_HEADERS KHR
, OKL_K_LINES TOP_KLE
, OKL_K_LINES KLE
, OKC_STATUSES_V STS
, OKC_K_HEADERS_B CHR
WHERE CLE.DNZ_CHR_ID = CHR.ID
AND TL.ID = CLE.ID
AND TL.LANGUAGE(+) = USERENV('LANG')
AND LS.ID = CLE.LSE_ID
AND TOP_LINE_TL.ID = TOP_LINE.ID
AND TOP_LINE_TL.LANGUAGE(+) = USERENV('LANG')
AND TOP_LINE.ID = CLE.CLE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND TXLA.KLE_ID(+) = CLE.ID
AND TXLA.TAL_TYPE IN ( 'CFA'
, 'CRB')
AND NOT EXISTS ( SELECT '1'
FROM OKC_K_ITEMS FA_CIM
WHERE FA_CIM.CLE_ID = CLE.ID
AND FA_CIM.OBJECT1_ID1 IS NOT NULL)
AND KHR.ID = CLE.DNZ_CHR_ID
AND TOP_KLE.ID = CLE.CLE_ID
AND KLE.ID = CLE.ID
AND CLE.STS_CODE = STS.CODE
AND CLE.STS_CODE <> 'ABANDONED'