View: OKL_K_ASSETS_UV

Product:	OKL - Lease and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_K_ASSETS_UV

View Text

SELECT OTAB.DNZ_KHR_ID CONTRACT_ID
, OTAT.DESCRIPTION ASSET_DESCRIPTION
, OTAB.CURRENT_UNITS QUANTITY
, KLE_TOP.OEC OEC
, TL.NAME ASSET_CATEGORY
, FNL.MEANING INSURANCE_CLASS
, OICC.IAC_CODE INSURANCE_CLASS_CODE
, OKX_COUNTRY.NAME COUNTRY
, NVL(NULL
, 0) PREMIUM
, KLE_TOP.ID KLE_ID
, OTAB.DEPRECIATION_ID ASSET_CATEGORY_ID
, OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB
, OKL_TXL_ASSETS_TL OTAT
, OKX_ASST_CATGRS_V TL
, FND_LOOKUPS FNL
, OKL_INS_CLASS_CATS OICC
, OKL_K_LINES KLE
, OKL_K_LINES KLE_TOP
, OKC_K_LINES_B CLE
, OKX_COUNTRIES_V OKX_COUNTRY
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND TL.CATEGORY_ID = OTAB.DEPRECIATION_ID
AND OTAB.ID = OTAT.ID
AND OICC.IAY_ID = OTAB.DEPRECIATION_ID
AND FNL.LOOKUP_CODE = OICC.IAC_CODE
AND SYSDATE BETWEEN OICC.DATE_FROM
AND NVL(OICC.DATE_TO
, SYSDATE +1)
AND OKL_UTIL.GET_PREACTIVE_LINE_INST(KLE_TOP.ID) = OKX_COUNTRY.ID1
AND FNL.LOOKUP_TYPE = 'OKL_INSURANCE_ASSET_CLASS'
AND OTAT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT '1'
FROM OKC_K_ITEMS CIM
WHERE CIM.CLE_ID = OTAB.KLE_ID
AND CIM.OBJECT1_ID1 IS NOT NULL)
AND CLE.STS_CODE NOT IN ( 'TERMINATED'
, 'EXPIRED'
, 'ABANDONED' ) UNION SELECT OTAB.DNZ_KHR_ID CONTRACT_ID
, OTAT.DESCRIPTION ASSET_DESCRIPTION
, OTAB.CURRENT_UNITS QUANTITY
, KLE_TOP.OEC OEC
, TL.NAME ASSET_CATEGORY
, FNL.MEANING INSURANCE_CLASS
, OICC.IAC_CODE INSURANCE_CLASS_CODE
, OKX_COUNTRY.NAME COUNTRY
, NVL(NULL
, 0) PREMIUM
, KLE_TOP.ID KLE_ID
, OTAB.DEPRECIATION_ID ASSET_CATEGORY_ID
, OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB
, OKL_TXL_ASSETS_TL OTAT
, OKX_ASST_CATGRS_V TL
, FND_LOOKUPS FNL
, OKL_INS_CLASS_CATS OICC
, OKL_K_LINES KLE
, OKL_K_LINES KLE_TOP
, OKC_K_LINES_B CLE
, OKX_COUNTRIES_V OKX_COUNTRY
, OKL_K_HEADERS KHR
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND TL.CATEGORY_ID = KLE_TOP.ITEM_INSURANCE_CATEGORY
AND OTAB.ID = OTAT.ID
AND OICC.IAY_ID = KLE_TOP.ITEM_INSURANCE_CATEGORY
AND FNL.LOOKUP_CODE = OICC.IAC_CODE
AND SYSDATE BETWEEN OICC.DATE_FROM
AND NVL(OICC.DATE_TO
, SYSDATE +1)
AND OKL_UTIL.GET_PREACTIVE_LINE_INST(KLE_TOP.ID) = OKX_COUNTRY.ID1
AND FNL.LOOKUP_TYPE = 'OKL_INSURANCE_ASSET_CLASS'
AND OTAT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT '1'
FROM OKC_K_ITEMS CIM
WHERE CIM.CLE_ID = OTAB.KLE_ID
AND CIM.OBJECT1_ID1 IS NOT NULL)
AND KHR.ID = CLE.DNZ_CHR_ID
AND KHR.DEAL_TYPE = 'LOAN'
AND CLE.STS_CODE NOT IN ( 'TERMINATED'
, 'EXPIRED'
, 'ABANDONED'
, 'BOOKED' ) UNION SELECT CLE.DNZ_CHR_ID CONTRACT_ID
, OTL.ITEM_DESCRIPTION ASSET_DESCRIPTION
, MODEL.NUMBER_OF_ITEMS QUANTITY
, KLE.OEC OEC
, TL.NAME ASSET_CATEGORY
, FNL.MEANING INSURANCE_CLASS
, OICC.IAC_CODE INSURANCE_CLASS_CODE
, OKX_COUNTRY.NAME COUNTRY
, NVL(NULL
, 0) PREMIUM
, KLE.ID KLE_ID
, FAD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
, FAD.ASSET_NUMBER ASSET_NUMBER
FROM OKL_K_LINES KLE
, OKX_COUNTRIES_V OKX_COUNTRY
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LS
, OKC_K_ITEMS CIM
, OKC_K_LINES_TL OTL
, OKC_K_LINES_B ITEM_CLE
, OKC_LINE_STYLES_B ITEM_LS
, OKC_K_ITEMS MODEL
, OKX_ASST_CATGRS_V TL
, FND_LOOKUPS FNL
, OKL_INS_CLASS_CATS OICC
, FA_ADDITIONS_B FAD
, OKC_K_LINES_B FINAC_CLE
, OKC_LINE_STYLES_B FINAC_LS
WHERE FINAC_LS.LTY_CODE = 'FREE_FORM1'
AND FINAC_CLE.LSE_ID = FINAC_LS.ID
AND FINAC_CLE.ID = KLE.ID
AND OICC.IAY_ID = FAD.ASSET_CATEGORY_ID
AND FNL.LOOKUP_CODE = OICC.IAC_CODE
AND SYSDATE BETWEEN OICC.DATE_FROM
AND NVL(OICC.DATE_TO
, SYSDATE +1)
AND FNL.LOOKUP_TYPE = 'OKL_INSURANCE_ASSET_CLASS'
AND FAD.ASSET_ID = CIM.OBJECT1_ID1
AND CIM.OBJECT1_ID2 = '#'
AND TL.CATEGORY_ID = FAD.ASSET_CATEGORY_ID
AND MODEL.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND MODEL.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND MODEL.CLE_ID = ITEM_CLE.ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAC_CLE.ID
AND OKX_COUNTRY.ID1 = OKL_UTIL.GET_ACTIVE_LINE_INST_COUNTRY(FINAC_CLE.ID)
AND OTL.ID = CLE.ID
AND OTL.LANGUAGE = USERENV('LANG')
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND CLE.CLE_ID = FINAC_CLE.ID
AND FINAC_CLE.STS_CODE NOT IN ( 'TERMINATED'
, 'EXPIRED'
, 'ABANDONED' ) UNION SELECT CLE.DNZ_CHR_ID CONTRACT_ID
, OTL.ITEM_DESCRIPTION ASSET_DESCRIPTION
, MODEL.NUMBER_OF_ITEMS QUANTITY
, KLE.OEC OEC
, TL.NAME ASSET_CATEGORY
, FNL.MEANING INSURANCE_CLASS
, OICC.IAC_CODE INSURANCE_CLASS_CODE
, OKX_COUNTRY.NAME COUNTRY
, NVL(NULL
, 0) PREMIUM
, KLE.ID KLE_ID
, KLE.ITEM_INSURANCE_CATEGORY ASSET_CATEGORY_ID
, FINAN_CLET.NAME ASSET_NUMBER
FROM OKL_K_LINES KLE
, OKX_ASST_CATGRS_V TL
, FND_LOOKUPS FNL
, OKL_INS_CLASS_CATS OICC
, OKC_K_LINES_B CLE
, OKC_K_LINES_TL OTL
, OKC_LINE_STYLES_B LS
, OKC_K_LINES_B ITEM_CLE
, OKC_LINE_STYLES_B ITEM_LS
, OKC_K_ITEMS MODEL
, OKX_COUNTRIES_V OKX_COUNTRY
, OKL_K_HEADERS KHR
, OKC_K_LINES_B FINAN_CLE
, OKC_K_LINES_TL FINAN_CLET
WHERE OKL_UTIL.GET_ACTIVE_LINE_INST_COUNTRY(FINAN_CLE.ID) = OKX_COUNTRY.ID1
AND MODEL.CLE_ID = ITEM_CLE.ID
AND MODEL.DNZ_CHR_ID = ITEM_CLE.DNZ_CHR_ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAN_CLE.ID
AND ITEM_CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND CLE.CLE_ID = FINAN_CLE.ID
AND CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND TL.CATEGORY_ID = KLE.ITEM_INSURANCE_CATEGORY
AND OICC.IAY_ID = KLE.ITEM_INSURANCE_CATEGORY
AND FNL.LOOKUP_CODE = OICC.IAC_CODE
AND FNL.LOOKUP_TYPE = 'OKL_INSURANCE_ASSET_CLASS'
AND SYSDATE BETWEEN OICC.DATE_FROM
AND NVL(OICC.DATE_TO
, SYSDATE +1)
AND OTL.ID = CLE.ID
AND OTL.LANGUAGE = USERENV('LANG')
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND FINAN_CLE.STS_CODE NOT IN ( 'TERMINATED'
, 'EXPIRED'
, 'ABANDONED' )
AND FINAN_CLET.LANGUAGE = USERENV('LANG')
AND FINAN_CLET.ID = FINAN_CLE.ID
AND KLE.ID = FINAN_CLE.ID
AND FINAN_CLE.DNZ_CHR_ID = KHR.ID
AND FINAN_CLE.CHR_ID = KHR.ID
AND FINAN_CLE.CLE_ID IS NULL
AND KHR.DEAL_TYPE = 'LOAN'

Columns