OKL Loans Amortization Schedule

select hb.contract_number CONTRACT_NUMBER,
       hzp.party_name CUSTOMER_NAME,
       hzc.account_number CUST_ACC_NUM,
       to_char(hb.START_DATE,'DD-Mon-YYYY') START_DATE,
       to_char(hb.END_DATE,'DD-Mon-YYYY') END_DATE,
       lb.term_duration term,
       (select max(proj_interest_rate)
       from okl_amort_sched_v
       where trx_req_id=ktrx.id) proj_interest_rate,
         (select chr.contract_number
	   from okc_k_headers_all_b chr, okc_governances_v gvr
	   where gvr.chr_id_referred = chr.id
	   and gvr.chr_id = hb.id
	   and gvr.dnz_chr_id = hb.id) master_lease,
       (select  ppv.revenue_recognition_method
	    from OKL_PRODUCT_PARAMETERS_all_V ppv,
	 		 okl_k_headers hblppv
		where hblppv.pdt_id = ppv.id
		and hblppv.id = hb.id) rev_rec_meth,
       hb.currency_code currency,
       (SELECT ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,BL.ADDRESS1,BL.ADDRESS2,BL.ADDRESS3, BL.ADDRESS4,BL.CITY,BL.COUNTY,BL.STATE,
	   		   BL.PROVINCE,BL.POSTAL_CODE,NULL,BL.COUNTRY,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'N','N',300,1,1) DESCRIPTION
		FROM HZ_CUST_SITE_USES_all BCS,
     		 HZ_PARTY_SITES BPS,
	 		 HZ_LOCATIONS BL,
	 		 HZ_CUST_ACCT_SITES_ALL BCA,
	 		 okc_k_headers_all_b Bhb
		WHERE BPS.LOCATION_ID = BL.LOCATION_ID
		AND BL.CONTENT_SOURCE_TYPE = 'USER_ENTERED' 
		AND BPS.PARTY_SITE_ID = BCA.PARTY_SITE_ID 
		AND BCA.CUST_ACCT_SITE_ID = BCS.CUST_ACCT_SITE_ID
		AND BCS.ORG_ID = Bhb.authoring_org_id
		and Bhb.id = hb.id 
		AND Bhb.bill_to_site_use_id = BCS.SITE_USE_ID
		AND BCS.SITE_USE_CODE = 'BILL_TO'
		AND BCS.STATUS = 'A') bill_to_add,
	   (SELECT ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,LL.ADDRESS1,LL.ADDRESS2,LL.ADDRESS3, LL.ADDRESS4,LL.CITY,LL.COUNTY,LL.STATE,
	   		   LL.PROVINCE,LL.POSTAL_CODE,NULL,LL.COUNTRY,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'N','N',300,1,1) DESCRIPTION 
	   FROM HZ_CUST_SITE_USES_all lCS,
       		HZ_PARTY_SITES lPS,
	 		HZ_LOCATIONS lL,
	 		HZ_CUST_ACCT_SITES_ALL lCA,
	 		okc_k_party_roles_b lcpl,
	 		okc_k_headers_all_b lhb
		WHERE lPS.LOCATION_ID = lL.LOCATION_ID 
		AND lL.CONTENT_SOURCE_TYPE = 'USER_ENTERED' 
		AND lPS.PARTY_SITE_ID = lCA.PARTY_SITE_ID 
		AND lCA.CUST_ACCT_SITE_ID = lCS.CUST_ACCT_SITE_ID
		AND lCS.ORG_ID = lhb.authoring_org_id
		AND lPS.party_id = lcpl.object1_id1
		AND lcpl.rle_code = 'LESSEE'
		AND lcpl.chr_id = lhb.id
		and lhb.id = hb.id 
		AND lCS.SITE_USE_CODE = 'LEGAL'
                AND lCS.STATUS = 'A'
                AND lL.COUNTRY = USERENV('LANG')
                ) legal_add,
	   (select meaning 
	   from fnd_lookups 
	   where lookup_type = 'YES_NO' 
	   and lookup_code = (select   distinct NVL(rl1.rule_information10,'N') 
	                     from okc_rules_b  rl1,
	                          okc_rules_b  rl2,
	                          okl_strm_type_b strm
			             where rl1.rule_information_category = 'LASLL' 
			             and   rl1.dnz_chr_id = rl2.dnz_chr_id
			             and rl1.object2_id1 = rl2.id
			             and rl1.jtot_object2_code = 'OKL_STRMHDR'
			             and   rl2.rule_information_category = 'LASLH'
			             and   rl2.object1_id1 = strm.id
			             and strm.stream_type_purpose in('RENT','PRINCIPAL_PAYMENT')
			             and rl1.dnz_chr_id = ktrx.dnz_khr_id)) ADV_ARR,
       (select meaning 
	   from fnd_lookups 
	   where lookup_type = 'OKL_FREQUENCY' 
	   and lookup_code = (select   rl11.object1_id1
	                     from okc_rules_b  rl11,
	                          okc_rules_b  rl22,
	                          okl_strm_type_b strm
			             where rl11.rule_information_category = 'LASLL' 
			             and   rl11.dnz_chr_id = rl22.dnz_chr_id
			             and rl11.object2_id1 = rl22.id
			             and rl11.jtot_object2_code = 'OKL_STRMHDR'
			             and   rl22.rule_information_category = 'LASLH'
			             and   rl22.object1_id1 = strm.id
			             and strm.stream_type_purpose in('RENT','PRINCIPAL_PAYMENT')
			             and rl11.dnz_chr_id = ktrx.dnz_khr_id
			             group by rl11.object1_id1
			             having count(*) = 1)) payment_freq,						 
	   to_char(sysdate,'DD-Mon-YYYY') DATE_OF_REPORT
        from     okc_k_headers_all_b hb,
	 okl_k_headers lb,
	 okl_products op,
	 OKL_AE_TMPT_SETS_all aes,
	 OKL_ST_GEN_TMPT_SETS_all gts,
	 fnd_lookups fl,
	 HZ_PARTIES hzp,
       hz_cust_accounts hzc,
	 okc_k_party_roles_b cpl,
         okl_trx_requests ktrx
where hb.id = ktrx.dnz_khr_id 
and hb.id=lb.id
and op.id=lb.pdt_id
and aes.id=op.aes_id
and aes.gts_id=gts.id
and lookup_type='OKL_BOOK_CLASS'
and fl.lookup_code=gts.deal_type
and cpl.object1_id1 = hzp.party_id
and cpl.rle_code = 'LESSEE'
and cpl.chr_id = hb.id
and cpl.dnz_chr_id = hb.id
and hb.cust_acct_id  = hzc.cust_account_id
and ktrx.id = :P_REQUEST_ID