View: OKL_LA_RELEASE_ASSETS_UV

Product:	OKL - Leasing and Finance Management
Description:	User interface view for re-lease assets lov page. Displays assets eligible for re-lease
Implementation/DBA Data:	APPS.OKL_LA_RELEASE_ASSETS_UV

View Text

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
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')

Columns