SELECT khr.contract_number CONTRACT_NUMBER,
asset.name ASSET_NUMBER,
ptv.JURSDCTN_NAME ,
trunc(stream_element_date) LIEN_DATE ,
sty.STREAM_TYPE_PURPOSE ,
sty.name STREAM_TYPE,
trunc(ste.stream_element_date) STREAM_ELEMENT_DATE,
ste.amount AMOUNT_IMP ,
decode(date_billed,NULL,0,ste.amount) AMOUNT_BILLED,
decode(date_billed,NULL,0,ste.amount) AMOUNT,
decode(date_billed,NULL,0,ste.amount) - ste.amount DIFF_AMT1,
0 SUM_AMT1
FROM okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
okl_k_headers khl,
okl_k_lines_full_v asset,
okc_line_styles_b ls,
okl_property_tax_v ptv,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE ste.amount < > 0
AND stm.id = ste.stm_id
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND sty.STREAM_TYPE_PURPOSE = 'ACTUAL_PROPERTY_TAX'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.id = :CHR_ID
AND asset.chr_id = khr.id
AND asset.id = stm.kle_id
AND asset.id = :KLE_ID
AND ls.id = asset.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND ptv.id = ste.source_id
AND ste.source_table = 'OKL_PROPERTY_TAX_V'
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
UNION
SELECT khr.contract_number CONTRACT_NUMBER,
asset.name EST_PROP_ASST_NUM,
NULL EST_PROP_JURSDCTN_NAME,
NULL EST_PROP_LIEN_DATE,
sty.STREAM_TYPE_PURPOSE EST_PROP_STRM_TYPE_PUR,
sty.name EST_PROP_STRM_TYPE,
trunc(ste.stream_element_date) EST_PROP_STRM_ELE_DATE,
0 EST_PROP_AMOUNT_IMP,
0 EST_PROP_AMOUNT_BILLED,
ste.amount EST_PROP_AMOUNT,
0 DIFF_AMT1,
ste.amount SUM_AMT1
FROM okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty,
okc_k_headers_all_b khr,
okl_k_lines_full_v asset,
okc_line_styles_b ls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE ste.amount < > 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NOT NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND sty.STREAM_TYPE_PURPOSE = 'ESTIMATED_PROPERTY_TAX'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.id = :CHR_ID
AND asset.chr_id = khr.id
AND asset.id = stm.kle_id
AND asset.id = :KLE_ID
AND ls.id = asset.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
UNION
SELECT khr.contract_number,
asset.name,
NULL EST_PROP_JURSDCTN_NAME,
NULL EST_PROP_LIEN_DATE,
sty.STREAM_TYPE_PURPOSE ,
sty.name STREAM_TYPE,
tai.date_invoiced,
0 EST_PROP_AMOUNT_IMP,
0 EST_PROP_AMOUNT_BILLED,
til.amount amount,
0 DIFF_AMT1,
til.amount SUM_AMT1
FROM okc_k_headers_all_b khr,
okl_trx_ar_invoices_b tai,
okl_txl_ar_inv_lns_b til,
okl_strm_type_v sty,
okl_k_lines_full_v asset,
okc_line_styles_b ls
WHERE khr.id = :CHR_ID
AND asset.chr_id = khr.id
AND asset.id = til.kle_id
AND ls.id = asset.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND tai.khr_id = khr.id
AND tai.id = til.tai_id
AND tai.qte_id IS NOT NULL
AND til.sty_id = sty.id
AND til.kle_id = :KLE_ID
AND sty.STREAM_TYPE_PURPOSE = 'AMPRTX'
UNION
SELECT khr.contract_number ADJ_PROP_CON_NUM,
asset.name ADJ_PROP_ASST_NUM,
NULL ADJ_PROP_JURSDCTN_NAME,
NULL ADJ_PROP_LIEN_DATE,
sty.STREAM_TYPE_PURPOSE ADJ_PROP_STRM_TYPE_PUR,
sty.name ADJ_PROP_STRM_TYPE,
trunc(ste.stream_element_date) ADJ_PROP_STRM_ELE_DATE,
0 ADJ_PROP_AMOUNT_IMP,
0 ADJ_PROP_AMOUNT_BILLED,
ste.amount ADJ_PROP_TAX_AMOUNT,
0 DIFF_AMT1,
0 SUM_AMT1
FROM okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
okl_k_headers khl,
okl_k_lines_full_v asset,
okc_line_styles_b ls,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE ste.amount < > 0
AND ste.date_billed IS NOT NULL
AND stm.id = ste.stm_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND sty.STREAM_TYPE_PURPOSE = 'ADJUSTED_PROPERTY_TAX'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.id = :CHR_ID
AND asset.chr_id = khr.id
AND asset.id = stm.kle_id
AND asset.id = :KLE_ID
AND ls.id = asset.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED') |
