OKL Lease Property Tax

Description
Categories: BI Publisher, Financials
Application: Lease and Finance Management
Source: Lease Property Tax Report
Short Name: OKLBPPTAXRPT
DB package:
			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')
Parameter Name SQL text Validation
Asset Number To
 
LOV Oracle
Asset Number From
 
LOV Oracle
Contract Number To
 
LOV Oracle
Contract Number From
 
LOV Oracle