VIEW: APPS.OKL_LA_RELEASE_ASSETS_UV

Source

View Text - Preformatted

SELECT fa.asset_number asset_number, cim_fa.object1_id1 asset_id, fa.current_units current_units, clet_tl.item_description asset_description, mtlt.inventory_item_id inventory_item_id, mtlt.description item_description, mtlt.organization_id organization_id, okl_am_util_pvt.get_fa_nbv(cleb_ml.dnz_chr_id, cim_fa.Object1_Id1) COST, trunc(decode(sign(cleb_tl.end_date - cleb_tl.date_terminated),-1,cleb_tl.end_date, cleb_tl.date_terminated)) DATE_TERMINATED, oar.legal_entity_id legal_entity_id FROM FA_ADDITIONS_TL FAT,FA_ADDITIONS_B FA,FA_BOOKS FB,FA_BOOK_CONTROLS FBC,FA_DEPRN_SUMMARY FDS,OKC_K_ITEMS CIM_FA,OKC_LINE_STYLES_B LSE_FA,OKC_K_LINES_B CLEB_FA,MTL_SYSTEM_ITEMS_TL MTLT,OKC_K_ITEMS CIM_ML,OKC_LINE_STYLES_B LSE_ML,OKC_K_LINES_B CLEB_ML,OKL_ASSET_RETURNS_B OAR,OKC_SUBCLASS_TOP_LINE STL,OKC_LINE_STYLES_B LSE_TL,OKC_K_LINES_TL CLET_TL,OKC_K_LINES_B CLEB_TL WHERE cleb_tl.id = clet_tl.id AND clet_tl.LANGUAGE = userenv('lang') AND lse_tl.id = cleb_tl.lse_id AND lse_tl.lty_code = 'FREE_FORM1' AND lse_tl.lse_parent_id IS NULL AND lse_tl.lse_type = 'TLS' AND lse_tl.id = stl.lse_id AND stl.scs_code IN('LEASE', 'LOAN') AND oar.kle_id = cleb_tl.id AND oar.ars_code = 'RE_LEASE' AND cleb_ml.cle_id = cleb_tl.id AND cleb_ml.lse_id = lse_ml.id AND lse_ml.lty_code = 'ITEM' AND cleb_ml.id = cim_ml.cle_id AND cleb_ml.dnz_chr_id = cim_ml.dnz_chr_id AND cim_ml.object1_id1 = to_char(mtlt.inventory_item_id) AND cim_ml.object1_id2 = to_char(mtlt.organization_id) AND mtlt.LANGUAGE = userenv('lang') AND cleb_fa.cle_id = cleb_tl.id AND cleb_fa.lse_id = lse_fa.id AND lse_fa.lty_code = 'FIXED_ASSET' AND cleb_fa.id = cim_fa.cle_id AND cleb_fa.dnz_chr_id = cim_fa.dnz_chr_id AND cim_fa.object1_id1 = fa.asset_id AND cim_fa.object1_id2 = '#' AND fa.asset_id = fat.asset_id AND fat.LANGUAGE = userenv('lang') AND fb.asset_id = fa.asset_id AND fb.book_type_code = fbc.book_type_code AND fbc.book_class = 'CORPORATE' AND fb.transaction_header_id_out IS NULL AND fds.book_type_code = fb.book_type_code AND fds.asset_id = fb.asset_id AND fds.period_counter = (SELECT MAX(fds2.period_counter) FROM fa_deprn_summary fds2 WHERE fds2.book_type_code = fds.book_type_code AND fds2.asset_id = fds.asset_id) AND NOT EXISTS (SELECT '1' FROM okc_k_headers_b chrb_2, okc_statuses_b chr_sts, okc_k_lines_b cleb_2, okc_statuses_b cle_sts, okc_k_items cim_2 WHERE chrb_2.id = cleb_2.dnz_chr_id AND chrb_2.scs_code IN('LEASE', 'LOAN') AND chrb_2.sts_code = chr_sts.code AND chr_sts.ste_code NOT IN('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED') AND cleb_2.id = cim_2.cle_id AND cleb_2.dnz_chr_id = cim_2.dnz_chr_id AND cleb_2.sts_code = cle_sts.code AND cle_sts.ste_code NOT IN('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED') AND cim_2.object1_id1 = to_char(fa.asset_id) AND cim_2.object1_id2 = '#' AND cim_2.jtot_object1_code = 'OKX_ASSET')

View Text - HTML Formatted

SELECT FA.ASSET_NUMBER ASSET_NUMBER
, CIM_FA.OBJECT1_ID1 ASSET_ID
, FA.CURRENT_UNITS CURRENT_UNITS
, CLET_TL.ITEM_DESCRIPTION ASSET_DESCRIPTION
, MTLT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MTLT.DESCRIPTION ITEM_DESCRIPTION
, MTLT.ORGANIZATION_ID ORGANIZATION_ID
, OKL_AM_UTIL_PVT.GET_FA_NBV(CLEB_ML.DNZ_CHR_ID
, CIM_FA.OBJECT1_ID1) COST
, TRUNC(DECODE(SIGN(CLEB_TL.END_DATE - CLEB_TL.DATE_TERMINATED)
, -1
, CLEB_TL.END_DATE
, CLEB_TL.DATE_TERMINATED)) DATE_TERMINATED
, OAR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
FROM FA_ADDITIONS_TL FAT
, FA_ADDITIONS_B FA
, FA_BOOKS FB
, FA_BOOK_CONTROLS FBC
, FA_DEPRN_SUMMARY FDS
, OKC_K_ITEMS CIM_FA
, OKC_LINE_STYLES_B LSE_FA
, OKC_K_LINES_B CLEB_FA
, MTL_SYSTEM_ITEMS_TL MTLT
, OKC_K_ITEMS CIM_ML
, OKC_LINE_STYLES_B LSE_ML
, OKC_K_LINES_B CLEB_ML
, OKL_ASSET_RETURNS_B OAR
, OKC_SUBCLASS_TOP_LINE STL
, OKC_LINE_STYLES_B LSE_TL
, OKC_K_LINES_TL CLET_TL
, OKC_K_LINES_B CLEB_TL
WHERE CLEB_TL.ID = CLET_TL.ID
AND CLET_TL.LANGUAGE = USERENV('LANG')
AND LSE_TL.ID = CLEB_TL.LSE_ID
AND LSE_TL.LTY_CODE = 'FREE_FORM1'
AND LSE_TL.LSE_PARENT_ID IS NULL
AND LSE_TL.LSE_TYPE = 'TLS'
AND LSE_TL.ID = STL.LSE_ID
AND STL.SCS_CODE IN('LEASE'
, 'LOAN')
AND OAR.KLE_ID = CLEB_TL.ID
AND OAR.ARS_CODE = 'RE_LEASE'
AND CLEB_ML.CLE_ID = CLEB_TL.ID
AND CLEB_ML.LSE_ID = LSE_ML.ID
AND LSE_ML.LTY_CODE = 'ITEM'
AND CLEB_ML.ID = CIM_ML.CLE_ID
AND CLEB_ML.DNZ_CHR_ID = CIM_ML.DNZ_CHR_ID
AND CIM_ML.OBJECT1_ID1 = TO_CHAR(MTLT.INVENTORY_ITEM_ID)
AND CIM_ML.OBJECT1_ID2 = TO_CHAR(MTLT.ORGANIZATION_ID)
AND MTLT.LANGUAGE = USERENV('LANG')
AND CLEB_FA.CLE_ID = CLEB_TL.ID
AND CLEB_FA.LSE_ID = LSE_FA.ID
AND LSE_FA.LTY_CODE = 'FIXED_ASSET'
AND CLEB_FA.ID = CIM_FA.CLE_ID
AND CLEB_FA.DNZ_CHR_ID = CIM_FA.DNZ_CHR_ID
AND CIM_FA.OBJECT1_ID1 = FA.ASSET_ID
AND CIM_FA.OBJECT1_ID2 = '#'
AND FA.ASSET_ID = FAT.ASSET_ID
AND FAT.LANGUAGE = USERENV('LANG')
AND FB.ASSET_ID = FA.ASSET_ID
AND FB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND FBC.BOOK_CLASS = 'CORPORATE'
AND FB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.ASSET_ID = FB.ASSET_ID
AND FDS.PERIOD_COUNTER = (SELECT MAX(FDS2.PERIOD_COUNTER)
FROM FA_DEPRN_SUMMARY FDS2
WHERE FDS2.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FDS2.ASSET_ID = FDS.ASSET_ID)
AND NOT EXISTS (SELECT '1'
FROM OKC_K_HEADERS_B CHRB_2
, OKC_STATUSES_B CHR_STS
, OKC_K_LINES_B CLEB_2
, OKC_STATUSES_B CLE_STS
, OKC_K_ITEMS CIM_2
WHERE CHRB_2.ID = CLEB_2.DNZ_CHR_ID
AND CHRB_2.SCS_CODE IN('LEASE'
, 'LOAN')
AND CHRB_2.STS_CODE = CHR_STS.CODE
AND CHR_STS.STE_CODE NOT IN('HOLD'
, 'EXPIRED'
, 'TERMINATED'
, 'CANCELLED')
AND CLEB_2.ID = CIM_2.CLE_ID
AND CLEB_2.DNZ_CHR_ID = CIM_2.DNZ_CHR_ID
AND CLEB_2.STS_CODE = CLE_STS.CODE
AND CLE_STS.STE_CODE NOT IN('HOLD'
, 'EXPIRED'
, 'TERMINATED'
, 'CANCELLED')
AND CIM_2.OBJECT1_ID1 = TO_CHAR(FA.ASSET_ID)
AND CIM_2.OBJECT1_ID2 = '#'
AND CIM_2.JTOT_OBJECT1_CODE = 'OKX_ASSET')