VIEW: APPS.OKL_CS_LINE_VERSION_UV

Source

View Text - Preformatted

SELECT distinct hdr.id, hdr.contract_number, top_line.name asset_number, top_line.ID parent_line_id, top_line.item_description description, cim.number_of_items current_units, top_line.sts_code LINE_STATUS, STS.MEANING ASSET_STATUS, top_line.major_version major_version, nvl(kle.residual_value,0), nvl(kle.residual_percentage,0), nvl(kle.residual_grnty_amount,0), nvl(kle.oec,0), nvl(kle.capital_amount,0), hdr.currency_code FROM OKC_K_LINES_HV TOP_LINE,OKC_LINE_STYLES_B LSE,OKC_K_LINES_HV MODEL_CLE,OKC_LINE_STYLES_B MODEL_LSE,OKC_K_ITEMS_H CIM,OKL_K_LINES_H KLE,OKC_K_HEADERS_B HDR,OKC_STATUSES_V STS WHERE top_line.dnz_chr_id= hdr.id and lse.id = top_line.lse_id and lse.lty_code = 'FREE_FORM1' and model_cle.cle_id = top_line.id and model_lse.id = model_cle.lse_id and model_lse.lty_code = 'ITEM' and cim.cle_id = model_cle.id and kle.id = top_line.id and kle.major_version = top_line.major_version and model_cle.major_version = top_line.major_version and cim.major_version = top_line.major_version and top_line.sts_code = sts.code UNION SELECT distinct hdr.id contract_id , hdr.contract_number, txla.asset_number, txla.kle_id parent_line_id, TOP_LINE_TL.ITEM_DESCRIPTION ITEM_DESCRIPTION, txla.CURRENT_UNITS, CLE.STS_CODE LINE_STATUS, STS.MEANING ASSET_STATUS, kle.major_version MAJOR_VERSION, nvl(kle.residual_value, 0), nvl(kle.residual_percentage, 0), nvl(kle.residual_grnty_amount, 0), nvl(kle.oec, 0), nvl(kle.capital_amount, 0), hdr.currency_code FROM okc_k_headers_bh hdr, OKL_TXL_ASSETS_B TXLA, OKC_K_LINES_BH CLE, OKC_K_LINES_TLH TL, OKC_K_LINES_TLH TOP_LINE_TL, OKL_K_LINES TOP_LINE, OKC_LINE_STYLES_B LS, OKL_K_HEADERS KHR, OKL_K_LINES_H TOP_KLE, OKL_K_LINES_H KLE, OKC_STATUSES_V STS WHERE cle.dnz_chr_id = hdr.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= 'CFA' 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.ID AND TOP_KLE.ID = CLE.CLE_ID AND KLE.ID = top_line.ID AND LS.LTY_CODE ='FIXED_ASSET' and KHR.DEAL_TYPE = 'LOAN' AND CLE.STS_CODE = STS.CODE order by major_version, asset_number

View Text - HTML Formatted

SELECT DISTINCT HDR.ID
, HDR.CONTRACT_NUMBER
, TOP_LINE.NAME ASSET_NUMBER
, TOP_LINE.ID PARENT_LINE_ID
, TOP_LINE.ITEM_DESCRIPTION DESCRIPTION
, CIM.NUMBER_OF_ITEMS CURRENT_UNITS
, TOP_LINE.STS_CODE LINE_STATUS
, STS.MEANING ASSET_STATUS
, TOP_LINE.MAJOR_VERSION MAJOR_VERSION
, NVL(KLE.RESIDUAL_VALUE
, 0)
, NVL(KLE.RESIDUAL_PERCENTAGE
, 0)
, NVL(KLE.RESIDUAL_GRNTY_AMOUNT
, 0)
, NVL(KLE.OEC
, 0)
, NVL(KLE.CAPITAL_AMOUNT
, 0)
, HDR.CURRENCY_CODE
FROM OKC_K_LINES_HV TOP_LINE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_HV MODEL_CLE
, OKC_LINE_STYLES_B MODEL_LSE
, OKC_K_ITEMS_H CIM
, OKL_K_LINES_H KLE
, OKC_K_HEADERS_B HDR
, OKC_STATUSES_V STS
WHERE TOP_LINE.DNZ_CHR_ID= HDR.ID
AND LSE.ID = TOP_LINE.LSE_ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND MODEL_CLE.CLE_ID = TOP_LINE.ID
AND MODEL_LSE.ID = MODEL_CLE.LSE_ID
AND MODEL_LSE.LTY_CODE = 'ITEM'
AND CIM.CLE_ID = MODEL_CLE.ID
AND KLE.ID = TOP_LINE.ID
AND KLE.MAJOR_VERSION = TOP_LINE.MAJOR_VERSION
AND MODEL_CLE.MAJOR_VERSION = TOP_LINE.MAJOR_VERSION
AND CIM.MAJOR_VERSION = TOP_LINE.MAJOR_VERSION
AND TOP_LINE.STS_CODE = STS.CODE UNION SELECT DISTINCT HDR.ID CONTRACT_ID
, HDR.CONTRACT_NUMBER
, TXLA.ASSET_NUMBER
, TXLA.KLE_ID PARENT_LINE_ID
, TOP_LINE_TL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, TXLA.CURRENT_UNITS
, CLE.STS_CODE LINE_STATUS
, STS.MEANING ASSET_STATUS
, KLE.MAJOR_VERSION MAJOR_VERSION
, NVL(KLE.RESIDUAL_VALUE
, 0)
, NVL(KLE.RESIDUAL_PERCENTAGE
, 0)
, NVL(KLE.RESIDUAL_GRNTY_AMOUNT
, 0)
, NVL(KLE.OEC
, 0)
, NVL(KLE.CAPITAL_AMOUNT
, 0)
, HDR.CURRENCY_CODE
FROM OKC_K_HEADERS_BH HDR
, OKL_TXL_ASSETS_B TXLA
, OKC_K_LINES_BH CLE
, OKC_K_LINES_TLH TL
, OKC_K_LINES_TLH TOP_LINE_TL
, OKL_K_LINES TOP_LINE
, OKC_LINE_STYLES_B LS
, OKL_K_HEADERS KHR
, OKL_K_LINES_H TOP_KLE
, OKL_K_LINES_H KLE
, OKC_STATUSES_V STS
WHERE CLE.DNZ_CHR_ID = HDR.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= 'CFA'
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.ID
AND TOP_KLE.ID = CLE.CLE_ID
AND KLE.ID = TOP_LINE.ID
AND LS.LTY_CODE ='FIXED_ASSET'
AND KHR.DEAL_TYPE = 'LOAN'
AND CLE.STS_CODE = STS.CODE ORDER BY MAJOR_VERSION
, ASSET_NUMBER