VIEW: APPS.OKL_IPY_ASSETS_UV

Source

View Text - Preformatted

SELECT OTAB.DNZ_KHR_ID CONTRACT_ID, INA.IPY_ID POLICY_ID, OTAB.ASSET_NUMBER ASSET_NUMBER, 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, INA.ASSET_PREMIUM PREMIUM, KLE_TOP.ID KLE_ID FROM OKX_COUNTRIES_V OKX_COUNTRY,OKL_TXL_ASSETS_B OTAB,OKL_TXL_ASSETS_TL OTAT,OKL_INS_ASSETS_ALL INA,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 WHERE KLE_TOP.ID = INA.KLE_ID AND 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 ( 'ABANDONED' ) UNION SELECT OTAB.DNZ_KHR_ID CONTRACT_ID, INA.IPY_ID POLICY_ID, OTAB.ASSET_NUMBER ASSET_NUMBER, 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, INA.ASSET_PREMIUM PREMIUM, KLE_TOP.ID KLE_ID FROM OKL_INS_ASSETS INA, 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_TOP.ID = INA.KLE_ID AND 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 ( 'ABANDONED','BOOKED' ) UNION select CLE.DNZ_CHR_ID CONTRACT_ID, INA.IPY_ID POLICY_ID, FAD.ASSET_NUMBER ASSET_NUMBER, 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, INA.ASSET_PREMIUM PREMIUM, KLE.ID KLE_ID from OKL_INS_ASSETS INA, 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_CLE.ID = INA.KLE_ID AND 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 ('ABANDONED' ) union SELECT CLE.DNZ_CHR_ID CONTRACT_ID, INA.IPY_ID POLICY_ID, FINAN_CLET.NAME ASSET_NUMBER, 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, INA.ASSET_PREMIUM PREMIUM, KLE.ID KLE_ID FROM OKL_INS_ASSETS INA, 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 FINAN_CLE.ID = INA.KLE_ID AND 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 ( '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'

View Text - HTML Formatted

SELECT OTAB.DNZ_KHR_ID CONTRACT_ID
, INA.IPY_ID POLICY_ID
, OTAB.ASSET_NUMBER ASSET_NUMBER
, 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
, INA.ASSET_PREMIUM PREMIUM
, KLE_TOP.ID KLE_ID
FROM OKX_COUNTRIES_V OKX_COUNTRY
, OKL_TXL_ASSETS_B OTAB
, OKL_TXL_ASSETS_TL OTAT
, OKL_INS_ASSETS_ALL INA
, 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
WHERE KLE_TOP.ID = INA.KLE_ID
AND 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 ( 'ABANDONED' ) UNION SELECT OTAB.DNZ_KHR_ID CONTRACT_ID
, INA.IPY_ID POLICY_ID
, OTAB.ASSET_NUMBER ASSET_NUMBER
, 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
, INA.ASSET_PREMIUM PREMIUM
, KLE_TOP.ID KLE_ID
FROM OKL_INS_ASSETS INA
, 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_TOP.ID = INA.KLE_ID
AND 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 ( 'ABANDONED'
, 'BOOKED' ) UNION SELECT CLE.DNZ_CHR_ID CONTRACT_ID
, INA.IPY_ID POLICY_ID
, FAD.ASSET_NUMBER ASSET_NUMBER
, 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
, INA.ASSET_PREMIUM PREMIUM
, KLE.ID KLE_ID
FROM OKL_INS_ASSETS INA
, 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_CLE.ID = INA.KLE_ID
AND 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 ('ABANDONED' ) UNION SELECT CLE.DNZ_CHR_ID CONTRACT_ID
, INA.IPY_ID POLICY_ID
, FINAN_CLET.NAME ASSET_NUMBER
, 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
, INA.ASSET_PREMIUM PREMIUM
, KLE.ID KLE_ID
FROM OKL_INS_ASSETS INA
, 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 FINAN_CLE.ID = INA.KLE_ID
AND 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 ( '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'