[Home] [Help]
110:
111: CURSOR c_total_amount_paid (p_sty_id NUMBER)
112: IS
113: SELECT COUNT(*)
114: FROM okl_strm_elements STRE, OKL_STREAMS STR
115: WHERE STR.ID = STRE.STM_ID
116: AND STR.STY_ID = p_sty_id
117: AND STRE.DATE_BILLED IS NOT NULL
118: AND STR.KHR_ID = p_contract_id
190: -- cursor fetch replaced with the streams util call, change
191: -- done for user defined streams impacts, bug 3924300
192: -- begin changes for bug 3924300
193:
194: OKL_STREAMS_UTIL.get_primary_stream_type(p_contract_id,
195: 'INSURANCE_RECEIVABLE',
196: l_return_status,
197: l_stm_type_id);
198: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
419: CURSOR l_line_rents_csr (chrId NUMBER ) IS
420: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
421: NVL(SUM(sele.amount),0) amount
422: FROM okl_strm_elements sele,
423: okl_streams str,
424: --okl_strm_type_tl sty,
425: okl_strm_type_v sty,
426: okl_K_lines_full_v kle,
427: okc_statuses_b sts
448:
449: CURSOR l_chr_rents_csr (chrId NUMBER ) IS
450: SELECT NVL(SUM(sele.amount),0) amount
451: FROM okl_strm_elements sele,
452: okl_streams str,
453: --okl_strm_type_tl sty
454: okl_strm_type_v sty
455: WHERE sele.stm_id = str.id
456: AND str.sty_id = sty.id
468: CURSOR l_line_rents_nr_csr (chrId NUMBER, p_line_type VARCHAR2 ) IS
469: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
470: NVL(SUM(sele.amount),0) amount
471: FROM okl_strm_elements sele,
472: okl_streams str,
473: --okl_strm_type_tl sty,
474: okl_strm_type_v sty,
475: okl_K_lines_full_v kle,
476: okc_statuses_b sts
620: -- "Not being used" and hence not modified with its purpose
621: --
622: SELECT NVL(SUM(sele.amount),0) INTO l_income
623: FROM okl_strm_elements sele,
624: okl_streams str,
625: okl_strm_type_v sty,
626: okl_K_lines_full_v kle,
627: okc_statuses_b sts
628: WHERE sele.stm_id = str.id
3795: -- Bug 5055714. Commenting below derivation. Using utility provided instead.
3796: -- CURSOR principal_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_end_date DATE, p_prin_bal_id NUMBER) IS
3797: -- SELECT SUM(ste.amount)
3798: -- FROM OKL_STRM_TYPE_B sty,
3799: -- OKL_STREAMS stm,
3800: -- OKL_STRM_ELEMENTS ste
3801: -- WHERE stm.khr_id = p_ctr_id
3802: -- AND stm.active_yn = 'Y'
3803: -- AND stm.say_code = 'CURR'
3810: -- for given period is not found
3811: -- CURSOR prior_prin_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_prin_bal_id NUMBER) IS
3812: -- SELECT SUM(ste.amount)
3813: -- FROM OKL_STRM_TYPE_B sty,
3814: -- OKL_STREAMS stm,
3815: -- OKL_STRM_ELEMENTS ste
3816: -- WHERE stm.khr_id = p_ctr_id
3817: -- AND stm.active_yn = 'Y'
3818: -- AND stm.say_code = 'CURR'
3820: -- AND stm.sty_id = sty.id
3821: -- AND ste.stm_id = stm.id
3822: -- AND ste.stream_element_date = (SELECT MAX(stream_element_date)
3823: -- FROM OKL_STRM_TYPE_B sty,
3824: -- OKL_STREAMS stm,
3825: -- OKL_STRM_ELEMENTS ste
3826: -- WHERE stm.khr_id = p_ctr_id
3827: -- AND stm.active_yn = 'Y'
3828: -- AND stm.say_code = 'CURR'
3884:
3885: -- SGIYER
3886: -- UDS Impact
3887: -- Bug 5055714.
3888: -- OKL_STREAMS_UTIL.get_dependent_stream_type(
3889: -- p_khr_id => p_khr_id,
3890: -- p_primary_sty_purpose => 'RENT',
3891: -- p_dependent_sty_purpose => 'PRINCIPAL_BALANCE',
3892: -- x_return_status => l_return_status,
4004: -- SGIYER
4005: -- modifying cursor for user defind streams project
4006: CURSOR get_unb_rec_csr(p_ctr_id NUMBER, p_date DATE, p_sty_id NUMBER) IS
4007: SELECT SUM(ste.amount)
4008: FROM OKL_STREAMS stm,
4009: OKL_STRM_ELEMENTS ste,
4010: OKL_STRM_TYPE_B sty
4011: WHERE stm.khr_id = p_ctr_id
4012: AND stm.sty_id = sty.id
4048: END IF;
4049:
4050: -- SGIYER
4051: -- UDS Impact
4052: OKL_STREAMS_UTIL.get_primary_stream_type(
4053: p_khr_id => p_khr_id,
4054: p_primary_sty_purpose => 'RENT',
4055: x_return_status => l_return_status,
4056: x_primary_sty_id => l_sty_id);
4129: -- on product management's instructions.
4130: -- SGIYER. User defined streams project changes.09/22/04
4131: CURSOR get_unearn_rev_csr (p_ctr_id NUMBER, p_date DATE, p_rent_sty_id NUMBER, p_lease_inc_sty_id NUMBER) IS
4132: SELECT SUM(ste.amount)
4133: FROM OKL_STREAMS stm,
4134: OKL_STRM_ELEMENTS ste,
4135: OKL_STRM_TYPE_B sty
4136: WHERE stm.khr_id = p_ctr_id
4137: AND stm.sty_id = sty.id
4140: AND stm.say_code = 'CURR'
4141: AND ste.stm_id = stm.id
4142: AND ste.stream_element_date >=
4143: (SELECT TRUNC(MIN(ste.stream_element_date),'MM')
4144: FROM OKL_STREAMS stm,
4145: OKL_STRM_ELEMENTS ste,
4146: OKL_STRM_TYPE_B sty
4147: WHERE stm.khr_id = p_ctr_id
4148: AND stm.sty_id = sty.id
4152: AND ste.stream_element_date >= p_date
4153: AND ste.date_billed IS NULL)
4154: AND ste.stream_element_date <=
4155: (SELECT LAST_DAY(MAX(ste.stream_element_date))
4156: FROM OKL_STREAMS stm,
4157: OKL_STRM_ELEMENTS ste,
4158: OKL_STRM_TYPE_B sty
4159: WHERE stm.khr_id = p_ctr_id
4160: AND stm.sty_id = sty.id
4167: --Bug# 15992711 , Start
4168: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
4169: CURSOR get_unearn_rev_nr_csr (p_ctr_id NUMBER, p_date DATE, p_rent_sty_id NUMBER, p_lease_inc_sty_id NUMBER, p_line_type VARCHAR2) IS
4170: SELECT SUM(ste.amount)
4171: FROM OKL_STREAMS stm,
4172: OKL_STRM_ELEMENTS ste,
4173: OKL_STRM_TYPE_B sty,
4174: okc_k_lines_b cle,
4175: okl_k_lines kle
4180: AND stm.say_code = 'CURR'
4181: AND ste.stm_id = stm.id
4182: AND ste.stream_element_date >=
4183: (SELECT TRUNC(MIN(ste.stream_element_date),'MM')
4184: FROM OKL_STREAMS stm,
4185: OKL_STRM_ELEMENTS ste,
4186: OKL_STRM_TYPE_B sty,
4187: okc_k_lines_b cle,
4188: okl_k_lines kle
4199: AND NVL(kle.re_lease_yn,'N') = DECODE(p_line_type,'NEW','N','RELEASE','Y')
4200: )
4201: AND ste.stream_element_date <=
4202: (SELECT LAST_DAY(MAX(ste.stream_element_date))
4203: FROM OKL_STREAMS stm,
4204: OKL_STRM_ELEMENTS ste,
4205: OKL_STRM_TYPE_B sty,
4206: okc_k_lines_b cle,
4207: okl_k_lines kle
4268: END IF;
4269:
4270: -- SGIYER
4271: -- UDS Impact
4272: OKL_STREAMS_UTIL.get_primary_stream_type(
4273: p_khr_id => p_khr_id,
4274: p_primary_sty_purpose => 'RENT',
4275: x_return_status => l_return_status,
4276: x_primary_sty_id => l_rent_sty_id);
4283: END IF;
4284:
4285: -- SGIYER
4286: -- UDS Impact
4287: OKL_STREAMS_UTIL.get_dependent_stream_type(
4288: p_khr_id => p_khr_id,
4289: p_primary_sty_purpose => 'RENT',
4290: p_dependent_sty_purpose => 'LEASE_INCOME',
4291: x_return_status => l_return_status,
4453: CURSOR unaccrued_subsidy_csr(p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE, p_date DATE, p_subsidy_inc_id NUMBER) IS
4454: SELECT SUM(ste.amount)
4455: FROM OKL_STRM_ELEMENTS ste,
4456: OKL_STRM_TYPE_B sty,
4457: OKL_STREAMS stm
4458: WHERE stm.khr_id = p_ctr_id
4459: AND stm.sty_id = sty.id
4460: AND sty.id = p_subsidy_inc_id
4461: AND stm.active_yn = 'Y'
4497: END IF;
4498:
4499: -- Bug 4053623.
4500: -- Modifying error handling.
4501: OKL_STREAMS_UTIL.get_dependent_stream_type(
4502: p_khr_id => p_khr_id,
4503: p_primary_sty_purpose => 'SUBSIDY',
4504: p_dependent_sty_purpose => 'SUBSIDY_INCOME',
4505: x_return_status => l_return_status,
4695: END IF;
4696:
4697: --sechawla 21-sep-09 8830506
4698: IF l_rep_type = 'PRIMARY' THEN
4699: OKL_STREAMS_UTIL.get_dependent_stream_type(
4700: p_khr_id => p_khr_id,
4701: p_primary_sty_purpose => 'RENT',
4702: p_dependent_sty_purpose => 'ACTUAL_INCOME_ACCRUAL',
4703: x_return_status => l_return_status,
4702: p_dependent_sty_purpose => 'ACTUAL_INCOME_ACCRUAL',
4703: x_return_status => l_return_status,
4704: x_dependent_sty_id => l_sty_id);
4705: ELSIF l_rep_type = 'SECONDARY' THEN
4706: OKL_STREAMS_UTIL.get_dependent_stream_type_rep(
4707: p_khr_id => p_khr_id,
4708: p_primary_sty_purpose => 'RENT',
4709: p_dependent_sty_purpose => 'ACTUAL_INCOME_ACCRUAL',
4710: x_return_status => l_return_status,
5378: cp_contract_line_id NUMBER,
5379: cp_stream_type_id NUMBER,
5380: cp_quote_eff_date DATE ) IS
5381: SELECT SUM (NVL (ste.amount, 0)) amount_due
5382: FROM okl_streams stm,
5383: okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
5384: okl_strm_elements ste
5385: WHERE stm.khr_id = cp_contract_id
5386: AND stm.kle_id = cp_contract_line_id
5492: cp_contract_id NUMBER,
5493: cp_contract_line_id NUMBER,
5494: cp_stream_type_id NUMBER) IS
5495: SELECT SUM (NVL (ste.amount, 0)) amount_due
5496: FROM okl_streams stm,
5497: okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
5498: okl_strm_elements ste
5499: WHERE stm.khr_id = cp_contract_id
5500: AND stm.kle_id = cp_contract_line_id
5516: cp_contract_line_id NUMBER,
5517: cp_stream_type_id NUMBER,
5518: cp_trx_date DATE) IS
5519: SELECT SUM (NVL (ste.amount, 0)) amount_due
5520: FROM okl_streams stm,
5521: okl_strm_type_b sty,
5522: okl_strm_elements ste
5523: WHERE stm.khr_id = cp_contract_id
5524: AND stm.kle_id = cp_contract_line_id
5545: BEGIN
5546: --PAGARG 31-Dec-2004 Bug# 4097591 Start
5547: --UDS impact. Obtain stream type id and pass it to cursor
5548:
5549: OKL_STREAMS_UTIL.get_primary_stream_type(p_contract_id,
5550: 'RENT',
5551: l_return_status,
5552: l_stream_type_id);
5553:
5779: --Instead of using stream name, join the sty id passed to cursor
5780: CURSOR line_csr (c_contract_line_id NUMBER, p_sty_id NUMBER) IS
5781: SELECT NVL(SUM(sel.amount),0)
5782: FROM okl_strm_elements sel,
5783: okl_streams stm,
5784: okl_strm_type_v sty
5785: WHERE sty.id = p_sty_id
5786: AND stm.sty_id = sty.id
5787: AND stm.say_code = 'CURR'
5799: --Instead of using stream name, join the sty id passed to cursor
5800: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
5801: SELECT NVL(SUM(sel.amount),0)
5802: FROM okl_strm_elements sel,
5803: okl_streams stm,
5804: okl_strm_type_v sty
5805: WHERE sty.id = p_sty_id
5806: AND stm.sty_id = sty.id
5807: AND stm.say_code = 'CURR' -- reporting streams are current
5854: p_contract_id => p_contract_id,
5855: x_rep_product_id => lx_rep_product_id);
5856:
5857:
5858: OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => p_contract_id,
5859: p_product_id => lx_rep_product_id,
5860: p_primary_sty_purpose => 'RENT',
5861: p_dependent_sty_purpose => 'LEASE_INCOME',
5862: x_return_status => l_return_status,
5865: -- sechawla 05-dec-07 6671849 : END
5866:
5867: --PAGARG 31-Dec-2004 Bug# 4097591 Start
5868: --UDS impact. Obtain stream type id and pass it to cursor
5869: OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => p_contract_id,
5870: p_primary_sty_purpose => 'RENT',
5871: p_dependent_sty_purpose => 'LEASE_INCOME',
5872: x_return_status => l_return_status,
5873: x_dependent_sty_id => l_stream_type_id);
6030: CURSOR C (p_contract_id NUMBER)
6031: IS
6032: SELECT NVL(SUM(sele.amount),0)
6033: FROM okl_strm_elements sele,
6034: okl_streams str,
6035: okl_strm_type_v sty
6036: WHERE sele.stm_id = str.id
6037: AND str.sty_id = sty.id
6038: AND UPPER(sty.stream_type_purpose) = 'SECURITY_DEPOSIT'
6092:
6093: --Commented this code by rvaduri for bug 3487920
6094: /*
6095: SELECT NVL(SUM(cs.amount),0)
6096: FROM okl_streams_v asv,okl_strm_type_v bs,
6097: okl_strm_elements_v cs,
6098: okl_streams str,
6099: okl_strm_type_v sty
6100: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
6094: /*
6095: SELECT NVL(SUM(cs.amount),0)
6096: FROM okl_streams_v asv,okl_strm_type_v bs,
6097: okl_strm_elements_v cs,
6098: okl_streams str,
6099: okl_strm_type_v sty
6100: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
6101: AND str.sty_id = sty.id
6102: AND UPPER(sty.name) = 'RESIDUAL VALUE'
6168: CURSOR C (p_contract_id NUMBER)
6169: IS
6170: SELECT NVL(SUM(sele.amount),0)
6171: FROM okl_strm_elements sele,
6172: okl_streams str,
6173: okl_strm_type_v sty
6174: WHERE sele.stm_id = str.id
6175: AND str.sty_id = sty.id
6176: AND UPPER(sty.stream_type_purpose) = 'RENT'
6185: --Get all the future unbilled receivables for the line.
6186: CURSOR line (p_contract_id NUMBER, p_contract_line_id NUMBER) IS --sechawla 04-dec-08 : added p_contract_id
6187: SELECT NVL(SUM(sele.amount),0)
6188: FROM okl_strm_elements sele,
6189: okl_streams str,
6190: okl_strm_type_v sty
6191: WHERE sele.stm_id = str.id
6192: AND str.sty_id = sty.id
6193: --AND UPPER(sty.name) = 'RENT' --sechawla 04-dec-08 : remoevd
6249: CURSOR C (p_contract_id NUMBER)
6250: IS
6251: SELECT NVL(SUM(sele.amount),0)
6252: FROM okl_strm_elements sele,
6253: --okl_streams str, MGAAP 7263041
6254: okl_streams_rep_v str,
6255: okl_strm_type_v sty
6256: WHERE sele.stm_id = str.id
6257: AND str.sty_id = sty.id
6250: IS
6251: SELECT NVL(SUM(sele.amount),0)
6252: FROM okl_strm_elements sele,
6253: --okl_streams str, MGAAP 7263041
6254: okl_streams_rep_v str,
6255: okl_strm_type_v sty
6256: WHERE sele.stm_id = str.id
6257: AND str.sty_id = sty.id
6258: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME'
6266: --Commented by rvaduri for bug 3536862
6267: /*
6268: SELECT NVL(SUM(sele.amount),0)
6269: FROM okl_strm_elements sele,
6270: okl_streams str,
6271: okl_strm_type_v sty
6272: WHERE sele.stm_id = str.id
6273: AND str.sty_id = sty.id
6274: AND UPPER(sty.name) = 'UNEARNED INCOME'
6282:
6283: CURSOR line (p_contract_line_id NUMBER) IS
6284: SELECT NVL(SUM(sele.amount),0)
6285: FROM okl_strm_elements sele,
6286: --okl_streams str, MGAAP 7263041
6287: okl_streams_rep_v str,
6288: okl_strm_type_v sty
6289: WHERE sele.stm_id = str.id
6290: AND str.sty_id = sty.id
6283: CURSOR line (p_contract_line_id NUMBER) IS
6284: SELECT NVL(SUM(sele.amount),0)
6285: FROM okl_strm_elements sele,
6286: --okl_streams str, MGAAP 7263041
6287: okl_streams_rep_v str,
6288: okl_strm_type_v sty
6289: WHERE sele.stm_id = str.id
6290: AND str.sty_id = sty.id
6291: AND UPPER(sty.name) = 'PRE-TAX INCOME'
6299: /*
6300: *
6301: SELECT NVL(SUM(sel.amount),0)
6302: FROM okl_strm_elements sel,
6303: okl_streams stm,
6304: okl_strm_type_v sty
6305: WHERE sty.name = 'PRE-TAX INCOME'
6306: AND stm.sty_id = sty.id
6307: AND stm.say_code = 'CURR'
6410: l_streams_repo_policy VARCHAR2(80); -- MGAAP 7263041
6411:
6412: BEGIN
6413:
6414: l_streams_repo_policy := OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY;
6415:
6416: -- calculate asset line level depreciation
6417: IF(p_contract_line_id IS NOT NULL) THEN
6418: FOR p_asset_details_csr IN asset_details_csr(p_contract_line_id)
6528: /*
6529: CURSOR c (p_contract_id NUMBER)
6530: IS
6531: SELECT NVL(cs.amount,0)
6532: FROM okl_streams_v asv,okl_strm_type_v bs,
6533: okl_strm_elements_v cs,
6534: okl_streams str,
6535: okl_strm_type_v sty,
6536: okc_k_headers_v okh
6530: IS
6531: SELECT NVL(cs.amount,0)
6532: FROM okl_streams_v asv,okl_strm_type_v bs,
6533: okl_strm_elements_v cs,
6534: okl_streams str,
6535: okl_strm_type_v sty,
6536: okc_k_headers_v okh
6537: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
6538: AND str.sty_id = sty.id
6555: --It should be sum because we have to get the Principal balance on
6556: -- all the assets for the contract
6557: SELECT NVL(SUM(sel.amount),0)
6558: FROM okl_strm_elements sel,
6559: okl_streams stm,
6560: okl_strm_type_v sty
6561: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6562: AND stm.sty_id = sty.id
6563: AND stm.say_code = 'CURR'
6566: AND stm.khr_id = p_contract_id
6567: AND sel.stm_id = stm.id
6568: AND sel.stream_element_date =
6569: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6570: FROM okl_strm_elements sel,okl_streams stm,
6571: okl_strm_type_v sty
6572: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6573: AND stm.sty_id = sty.id
6574: AND stm.say_code = 'CURR'
6747: --It should be sum because we have to get the Principal balance on
6748: -- all the assets for the contract
6749: SELECT NVL(SUM(sel.amount),0)
6750: FROM okl_strm_elements sel,
6751: okl_streams stm,
6752: okl_strm_type_v sty
6753: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6754: AND stm.sty_id = sty.id
6755: AND stm.say_code = 'CURR'
6758: AND stm.khr_id = p_contract_id
6759: AND sel.stm_id = stm.id
6760: AND sel.stream_element_date =
6761: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6762: FROM okl_strm_elements sel,okl_streams stm,
6763: okl_strm_type_v sty
6764: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6765: AND stm.sty_id = sty.id
6766: AND stm.say_code = 'CURR'
6775:
6776: CURSOR line (p_contract_line_id NUMBER, p_date DATE) IS
6777: SELECT NVL(sel.amount,0)
6778: FROM okl_strm_elements sel,
6779: okl_streams stm,
6780: okl_strm_type_v sty
6781: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6782: AND stm.sty_id = sty.id
6783: AND stm.say_code = 'CURR'
6786: AND stm.kle_id = p_contract_line_id
6787: AND sel.stm_id = stm.id
6788: AND sel.stream_element_date =
6789: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6790: FROM okl_strm_elements sel,okl_streams stm,
6791: okl_strm_type_v sty
6792: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6793: AND stm.sty_id = sty.id
6794: AND stm.say_code = 'CURR'
7039:
7040: l_streams_repo_policy VARCHAR2(80); -- MGAAP 7263041
7041: BEGIN
7042:
7043: l_streams_repo_policy := OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY;
7044: -- calculate asset line level depreciation
7045: IF(p_contract_line_id IS NOT NULL) THEN
7046: FOR p_asset_details_csr IN asset_details_csr(p_contract_line_id)
7047: LOOP
7180: SELECT sel.amount rent
7181: ,sel.stream_element_date due_date
7182: ,stm.kle_id
7183: FROM okl_strm_elements sel,
7184: okl_streams stm,
7185: okl_strm_type_v sty
7186: WHERE sty.stream_type_purpose = 'RENT'
7187: AND stm.sty_id = sty.id
7188: AND stm.say_code = 'CURR'
7200: IS
7201: SELECT sel.amount rent
7202: ,sel.stream_element_date due_date
7203: FROM okl_strm_elements sel,
7204: okl_streams stm,
7205: okl_strm_type_v sty
7206: WHERE sty.stream_type_purpose = 'RENT'
7207: AND stm.sty_id = sty.id
7208: AND stm.say_code = 'CURR'
8083: ,p_sty_id IN NUMBER)
8084: IS
8085: SELECT NVL(SUM(selb.amount),0) total_amount
8086: FROM okl_strm_elements selb
8087: ,okl_streams stmb
8088: ,okl_pool_contents pocb
8089: ,okl_strm_type_v styv --ankushar --Bug 6594724
8090: ,okc_k_headers_b chrb --ankushar --Bug 6594724
8091: WHERE stmb.khr_id = p_dnz_chr_id
8135: ,p_sty_id IN NUMBER)
8136: IS
8137: SELECT NVL(SUM(selb.amount),0) total_amount
8138: FROM okl_strm_elements selb
8139: ,okl_streams stmb
8140: ,okl_pool_contents pocb
8141: WHERE stmb.khr_id = p_dnz_chr_id
8142: AND stmb.kle_id = p_kle_id
8143: AND stmb.sty_id = p_sty_id
8233: CURSOR l_okl_pv_amounts_csr(p_sty_purpose IN VARCHAR2, p_poc_sty_purpose IN VARCHAR2)
8234: IS
8235: SELECT NVL(SUM(selb.amount),0) total_amount
8236: FROM okl_strm_elements selb
8237: ,okl_streams stmb
8238: ,okl_strm_type_v styv
8239: ,okl_pool_contents pocb
8240: ,okl_strm_type_v styv1
8241: WHERE stmb.source_id = p_chr_id
8255: CURSOR l_okl_pv_amounts_pending_csr(p_sty_purpose IN VARCHAR2, p_poc_sty_purpose IN VARCHAR2)
8256: IS
8257: SELECT NVL(SUM(selb.amount),0) total_amount
8258: FROM okl_strm_elements selb
8259: ,okl_streams stmb
8260: ,okl_strm_type_v styv
8261: ,okl_pool_contents pocb
8262: ,okl_strm_type_v styv1
8263: WHERE stmb.source_id = p_chr_id
8502: -- mvasudev, 09/29/2004, Bug#3909240
8503: CURSOR c_rent_accrual(p_contract_id okc_k_headers_b.id%TYPE) IS
8504: SELECT NVL(SUM(SELB.AMOUNT),0)
8505: FROM
8506: OKL_STREAMS STMB,
8507: OKL_STRM_ELEMENTS SELB,
8508: OKL_STRM_TYPE_V STYV
8509: WHERE STMB.ID = SELB.STM_ID
8510: AND STMB.STY_ID = STYV.ID
8525:
8526: CURSOR c_rent_accrual_pending(p_contract_id okc_k_headers_b.id%TYPE) IS
8527: SELECT NVL(SUM(SELB.AMOUNT),0)
8528: FROM
8529: OKL_STREAMS STMB,
8530: OKL_STRM_ELEMENTS SELB,
8531: OKL_STRM_TYPE_V STYV
8532: WHERE STMB.ID = SELB.STM_ID
8533: AND STMB.STY_ID = STYV.ID
8729: SELECT
8730: NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
8731: FROM
8732: okl_strm_type_v styv,
8733: okl_streams stmb,
8734: okl_strm_elements selb,
8735: okl_pool_contents pocb
8736: WHERE styv.stream_type_subclass = p_stm_sub_class
8737: AND styv.id = stmb.sty_id
8750: SELECT
8751: NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
8752: FROM
8753: okl_strm_type_v styv,
8754: okl_streams stmb,
8755: okl_strm_elements selb,
8756: okl_pool_contents pocb
8757: WHERE styv.stream_type_subclass = p_stm_sub_class
8758: AND styv.id = stmb.sty_id
9516: ---------------------------------------------------
9517: CURSOR l_line_amortexp_csr (chrId NUMBER ) IS
9518: SELECT NVL(SUM(sele.amount),0) amount
9519: FROM okl_strm_elements sele,
9520: okl_streams str,
9521: --okl_strm_type_tl sty,
9522: okl_strm_type_v sty,
9523: okl_K_lines_full_v kle,
9524: okc_statuses_b sts
9543: -----------------------------------------------------
9544: CURSOR l_chr_amortexp_csr (chrId NUMBER) IS
9545: SELECT NVL(SUM(sele.amount),0) amount
9546: FROM okl_strm_elements sele,
9547: okl_streams str,
9548: --okl_strm_type_tl sty
9549: okl_strm_type_v sty
9550: WHERE sele.stm_id = str.id
9551: AND str.sty_id = sty.id
9635: cp_contract_line_id NUMBER,
9636: cp_date DATE,
9637: cp_sty_id NUMBER) IS
9638: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
9639: FROM okl_streams stm,
9640: okl_strm_type_b sty,
9641: okl_strm_elements ste
9642: ,okl_pool_contents pocb
9643: WHERE stm.khr_id = cp_contract_id
9662: cp_contract_line_id NUMBER,
9663: cp_sty_id NUMBER,
9664: cp_date DATE) IS --gboomina bug 4775555
9665: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
9666: FROM okl_streams stm,
9667: okl_strm_type_b sty,
9668: okl_strm_elements ste
9669: -- ,okl_pool_contents pocb
9670: WHERE stm.khr_id = cp_contract_id
9687: cp_contract_line_id NUMBER,
9688: cp_date DATE,
9689: cp_sty_id NUMBER) IS
9690: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
9691: FROM okl_streams stm,
9692: okl_strm_type_b sty,
9693: okl_strm_elements ste
9694: ,okl_pool_contents pocb
9695: WHERE stm.khr_id = cp_contract_id
9713: cp_contract_line_id NUMBER,
9714: cp_sty_id NUMBER,
9715: cp_date DATE) IS
9716: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
9717: FROM okl_streams stm,
9718: okl_strm_type_b sty,
9719: okl_strm_elements ste
9720: --,okl_pool_contents pocb
9721: WHERE stm.khr_id = cp_contract_id
9791: END IF;
9792:
9793: --PAGARG 19-Nov-2004 Bug# 4012614
9794: --UDS impact. Obtain stream type id and pass it to cursor
9795: OKL_STREAMS_UTIL.get_primary_stream_type(p_contract_id,
9796: 'RENT',
9797: l_return_status,
9798: l_sty_id);
9799:
9815: CLOSE l_unbill_stream_csr;
9816: END IF;
9817:
9818: --06-Dec-2004 PAGARG Bug# 3948473 Pass investor agreement id to obtain stream id
9819: OKL_STREAMS_UTIL.get_primary_stream_type(l_inv_agr_id,
9820: 'INVESTOR_RENT_DISB_BASIS',
9821: l_return_status,
9822: l_sty_id);
9823:
9908: cp_contract_id NUMBER,
9909: cp_contract_line_id NUMBER,
9910: cp_sty_id NUMBER) IS
9911: SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
9912: FROM okl_streams stm,
9913: okl_strm_type_b sty,
9914: okl_strm_elements ste,
9915: okl_pool_contents pocb
9916: WHERE stm.khr_id = cp_contract_id
9933: cp_contract_id NUMBER,
9934: cp_contract_line_id NUMBER,
9935: cp_sty_id NUMBER) IS
9936: SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
9937: FROM okl_streams stm,
9938: okl_strm_type_b sty,
9939: okl_strm_elements ste,
9940: okl_pool_contents pocb
9941: WHERE stm.khr_id = cp_contract_id
9964: -- Calculate result
9965: -- ****************
9966: --PAGARG 19-Nov-2004 Bug# 4012614
9967: --UDS impact. Obtain stream type id and pass it to cursor
9968: OKL_STREAMS_UTIL.get_primary_stream_type(p_contract_id,
9969: 'RESIDUAL_VALUE',
9970: l_return_status,
9971: l_sty_id);
9972:
10059: cp_contract_line_id NUMBER,
10060: cp_date DATE,
10061: cp_sty_id NUMBER) IS
10062: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
10063: FROM okl_streams stm,
10064: okl_strm_type_b sty,
10065: okl_strm_elements ste
10066: ,okl_pool_contents pocb
10067: WHERE stm.khr_id = cp_contract_id
10084: cp_contract_line_id NUMBER,
10085: cp_sty_id NUMBER,
10086: cp_date DATE) IS --gboomina bug 4775555
10087: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
10088: FROM okl_streams stm,
10089: okl_strm_type_b sty,
10090: okl_strm_elements ste
10091: ,okl_pool_contents pocb
10092: WHERE stm.khr_id = cp_contract_id
10107: cp_contract_line_id NUMBER,
10108: cp_date DATE,
10109: cp_sty_id NUMBER) IS
10110: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
10111: FROM okl_streams stm,
10112: okl_strm_type_b sty,
10113: okl_strm_elements ste,
10114: okl_pool_contents pocb
10115: WHERE stm.khr_id = cp_contract_id
10132: cp_contract_line_id NUMBER,
10133: cp_sty_id NUMBER,
10134: cp_date DATE) IS
10135: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
10136: FROM okl_streams stm,
10137: okl_strm_type_b sty,
10138: okl_strm_elements ste,
10139: okl_pool_contents pocb
10140: WHERE stm.khr_id = cp_contract_id
10215: END IF;
10216:
10217: --PAGARG 19-Nov-2004 Bug# 4012614
10218: --UDS impact. Obtain stream type id and pass it to cursor
10219: OKL_STREAMS_UTIL.get_dependent_stream_type(p_contract_id,
10220: 'RENT',
10221: 'PRINCIPAL_PAYMENT',
10222: l_return_status,
10223: l_sty_id);
10241: END IF;
10242:
10243: --PAGARG 19-Nov-2004 Bug# 4012614
10244: --UDS impact. Obtain stream type id and pass it to cursor
10245: OKL_STREAMS_UTIL.get_dependent_stream_type(p_contract_id,
10246: 'RENT',
10247: 'INTEREST_PAYMENT',
10248: l_return_status,
10249: l_sty_id);
10267: END IF;
10268:
10269: --PAGARG 19-Nov-2004 Bug# 4012614
10270: --UDS impact. Obtain stream type id and pass it to cursor
10271: OKL_STREAMS_UTIL.get_dependent_stream_type(p_contract_id,
10272: 'RENT',
10273: 'UNSCHEDULED_PRINCIPAL_PAYMENT',
10274: l_return_status,
10275: l_sty_id);
10296: l_unbill_loan_amount := l_unbill_principal_amount+l_unbill_interest_amount+l_unbill_ppd_amount;
10297:
10298: IF l_inv_agr_id IS NOT NULL THEN
10299: --06-Dec-2004 PAGARG Bug# 3948473 Pass investor agreement id to obtain stream id
10300: OKL_STREAMS_UTIL.get_primary_stream_type(l_inv_agr_id,
10301: 'INVESTOR_PRINCIPAL_DISB_BASIS',
10302: l_return_status,
10303: l_sty_id);
10304:
10319: FETCH l_undisb_rent_stream_csr INTO l_undisb_principal_amount;
10320: CLOSE l_undisb_rent_stream_csr;
10321: END IF;
10322:
10323: OKL_STREAMS_UTIL.get_primary_stream_type(l_inv_agr_id,
10324: 'INVESTOR_INTEREST_DISB_BASIS',
10325: l_return_status,
10326: l_sty_id);
10327:
10342: FETCH l_undisb_rent_stream_csr INTO l_undisb_interest_amount;
10343: CLOSE l_undisb_rent_stream_csr;
10344: END IF;
10345:
10346: OKL_STREAMS_UTIL.get_primary_stream_type(l_inv_agr_id,
10347: 'INVESTOR_PPD_DISB_BASIS',
10348: l_return_status,
10349: l_sty_id);
10350:
10561: cp_chr_id NUMBER,
10562: cp_fee_type VARCHAR2,
10563: cp_date DATE ) IS -- rmunjulu EDAT
10564: SELECT SUM(ste.amount) amount
10565: FROM okl_streams stm,
10566: okl_strm_type_b sty,
10567: okc_k_lines_b kle,
10568: okc_statuses_b kls,
10569: okc_line_styles_b lse,
10593: cp_chr_id NUMBER,
10594: cp_fee_type VARCHAR2,
10595: cp_date DATE ) IS -- rmunjulu EDAT
10596: SELECT SUM(ste.amount) amount
10597: FROM okl_streams stm
10598: ,okl_strm_type_b sty
10599: ,okc_k_lines_b kle
10600: ,okc_statuses_b kls
10601: ,okc_line_styles_b lse
10627: cp_fee_type VARCHAR2,
10628: cp_asset_id NUMBER,
10629: cp_date DATE ) IS -- rmunjulu EDAT
10630: SELECT SUM(ste.amount) amount
10631: FROM okl_streams stm
10632: ,okl_strm_type_b sty
10633: ,okc_k_lines_b kle
10634: ,okc_statuses_b kls
10635: ,okc_line_styles_b lse
10919: p_date_terminated okc_k_lines_b.date_terminated%TYPE,
10920: p_sty_id okl_strm_type_b.id%TYPE) IS
10921: SELECT NVL(SUM(selb.amount),0)
10922: FROM
10923: okl_streams stmb,
10924: okl_strm_elements selb
10925: WHERE stmb.id = selb.stm_id
10926: AND stmb.say_code = 'CURR'
10927: AND stmb.active_yn = 'Y'
10950:
10951: IF (r_subsidy_line.date_terminated IS NOT NULL) THEN
10952:
10953: /* Bug 6353756 Get dependent stream type from r_subsidy_line.sty_id */
10954: okl_streams_util.get_dependent_stream_type(
10955: p_khr_id => r_subsidy_line.chr_id,
10956: p_primary_sty_id => r_subsidy_line.sty_id,
10957: p_dependent_sty_purpose => 'SUBSIDY_INCOME',
10958: x_return_status => l_return_status,
11039: CURSOR l_line_income_csr (p_chr_id NUMBER) IS
11040: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
11041: NVL(SUM(sele.amount),0) amount
11042: FROM okl_strm_elements sele,
11043: --okl_streams str, MGAAP 7263041
11044: okl_streams_rep_v str,
11045: okl_strm_type_v sty,
11046: okc_k_headers_b CHR,
11047: okc_statuses_b sts
11040: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
11041: NVL(SUM(sele.amount),0) amount
11042: FROM okl_strm_elements sele,
11043: --okl_streams str, MGAAP 7263041
11044: okl_streams_rep_v str,
11045: okl_strm_type_v sty,
11046: okc_k_headers_b CHR,
11047: okc_statuses_b sts
11048: WHERE sele.stm_id = str.id
11070: CURSOR l_line_income_nr_csr (p_chr_id NUMBER, p_line_type VARCHAR2) IS
11071: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
11072: NVL(SUM(sele.amount),0) amount
11073: FROM okl_strm_elements sele,
11074: --okl_streams str, MGAAP 7263041
11075: okl_streams_rep_v str,
11076: okl_strm_type_v sty,
11077: okc_k_headers_b CHR,
11078: okc_statuses_b sts,
11071: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
11072: NVL(SUM(sele.amount),0) amount
11073: FROM okl_strm_elements sele,
11074: --okl_streams str, MGAAP 7263041
11075: okl_streams_rep_v str,
11076: okl_strm_type_v sty,
11077: okc_k_headers_b CHR,
11078: okc_statuses_b sts,
11079: okc_k_lines_b cle,
11890: IF (( p_contract_id IS NULL ) OR ( p_contract_line_id IS NULL )) THEN
11891: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
11892: END IF;
11893:
11894: l_streams_repo_policy := OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY;
11895: l_book_type_code := NULL;
11896: IF (l_streams_repo_policy = 'PRIMARY') THEN
11897: l_book_class := 'CORPORATE';
11898: ELSE
12180: IF (( p_contract_id IS NULL ) OR ( p_contract_line_id IS NULL )) THEN
12181: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
12182: END IF;
12183:
12184: l_streams_repo_policy := OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY;
12185: l_book_type_code := NULL;
12186: IF (l_streams_repo_policy = 'PRIMARY') THEN
12187: l_book_class := 'CORPORATE';
12188: ELSE
12798: cp_contract_line_id NUMBER,
12799: cp_stream_type_id NUMBER,
12800: cp_trx_date DATE) IS
12801: SELECT SUM (NVL (ste.amount, 0)) amount_due
12802: --FROM okl_streams stm,
12803: FROM okl_streams_rep_v stm,
12804: okl_strm_type_b sty,
12805: okl_strm_elements ste
12806: WHERE stm.khr_id = cp_contract_id
12799: cp_stream_type_id NUMBER,
12800: cp_trx_date DATE) IS
12801: SELECT SUM (NVL (ste.amount, 0)) amount_due
12802: --FROM okl_streams stm,
12803: FROM okl_streams_rep_v stm,
12804: okl_strm_type_b sty,
12805: okl_strm_elements ste
12806: WHERE stm.khr_id = cp_contract_id
12807: AND stm.kle_id = cp_contract_line_id
12822: cp_contract_line_id NUMBER,
12823: cp_stream_type_id NUMBER,
12824: cp_trx_date DATE) IS
12825: SELECT SUM (NVL (ste.amount, 0)) amount_due
12826: FROM okl_streams stm,
12827: okl_strm_type_b sty, -- Added this table to get the billable_yn flag
12828: okl_strm_elements ste
12829: WHERE stm.khr_id = cp_contract_id
12830: AND stm.kle_id = cp_contract_line_id
12858: cp_contract_line_id NUMBER,
12859: cp_stream_type_id NUMBER,
12860: cp_trx_date DATE) IS
12861: SELECT nvl(ste.amount, 0) amount
12862: FROM okl_streams stm,
12863: okl_strm_type_b sty,
12864: okl_strm_elements ste
12865: WHERE stm.khr_id = cp_contract_id
12866: AND stm.kle_id = cp_contract_line_id
12956: BEGIN
12957: --PAGARG 31-Dec-2004 Bug# 4097591 Start
12958: --UDS impact. Obtain stream type id and pass it to cursor
12959:
12960: OKL_STREAMS_UTIL.get_primary_stream_type(p_contract_id,
12961: 'RENT',
12962: l_return_status,
12963: l_stream_type_id);
12964:
13227: cp_trx_date DATE,
13228: p_sty_id NUMBER) IS
13229: SELECT NVL(SUM(sel.amount),0)
13230: FROM okl_strm_elements sel,
13231: --okl_streams stm, MGAAP 7263041
13232: okl_streams_rep_v stm,
13233: okl_strm_type_v sty
13234: WHERE sty.id = p_sty_id
13235: AND stm.sty_id = sty.id
13228: p_sty_id NUMBER) IS
13229: SELECT NVL(SUM(sel.amount),0)
13230: FROM okl_strm_elements sel,
13231: --okl_streams stm, MGAAP 7263041
13232: okl_streams_rep_v stm,
13233: okl_strm_type_v sty
13234: WHERE sty.id = p_sty_id
13235: AND stm.sty_id = sty.id
13236: AND stm.say_code = 'CURR'
13249: --Instead of using stream name, join the sty id passed to cursor
13250: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
13251: SELECT NVL(SUM(sel.amount),0)
13252: FROM okl_strm_elements sel,
13253: --okl_streams stm, MGAAP 7263041
13254: okl_streams_rep_v stm,
13255: okl_strm_type_v sty
13256: WHERE sty.id = p_sty_id
13257: AND stm.sty_id = sty.id
13250: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
13251: SELECT NVL(SUM(sel.amount),0)
13252: FROM okl_strm_elements sel,
13253: --okl_streams stm, MGAAP 7263041
13254: okl_streams_rep_v stm,
13255: okl_strm_type_v sty
13256: WHERE sty.id = p_sty_id
13257: AND stm.sty_id = sty.id
13258: AND stm.say_code = 'CURR' -- reporting streams are current
13287: cp_trx_date DATE,
13288: p_sty_id NUMBER) IS
13289: SELECT NVL(sel.amount,0) amount
13290: FROM okl_strm_elements sel,
13291: --okl_streams stm, MGAAP 7263041
13292: okl_streams_rep_v stm,
13293: okl_strm_type_v sty
13294: WHERE sty.id = p_sty_id
13295: AND stm.sty_id = sty.id
13288: p_sty_id NUMBER) IS
13289: SELECT NVL(sel.amount,0) amount
13290: FROM okl_strm_elements sel,
13291: --okl_streams stm, MGAAP 7263041
13292: okl_streams_rep_v stm,
13293: okl_strm_type_v sty
13294: WHERE sty.id = p_sty_id
13295: AND stm.sty_id = sty.id
13296: AND stm.say_code = 'CURR'
13373: x_msg_data => l_msg_data,
13374: p_contract_id => p_contract_id,
13375: x_rep_product_id => lx_rep_product_id);
13376:
13377: OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => p_contract_id,
13378: p_product_id => lx_rep_product_id,
13379: p_primary_sty_purpose => 'RENT',
13380: p_dependent_sty_purpose => 'LEASE_INCOME',
13381: x_return_status => l_return_status,
13390: ELSE
13391: -- sechawla 05-dec-07 6671849 -- START
13392: --PAGARG 31-Dec-2004 Bug# 4097591 Start
13393: --UDS impact. Obtain stream type id and pass it to cursor
13394: OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => p_contract_id,
13395: p_primary_sty_purpose => 'RENT',
13396: p_dependent_sty_purpose => 'LEASE_INCOME',
13397: x_return_status => l_return_status,
13398: x_dependent_sty_id => l_stream_type_id);
13472: -- gboomina Bug 5215019 - End
13473:
13474: --check if streams required for reporting product
13475: IF l_rep_prod_streams_yn = 'Y' THEN
13476: OKL_STREAMS_SEC_PVT.SET_REPO_STREAMS; --MGAAP 7263041
13477: --PAGARG 31-Dec-2004 Bug# 4097591, Pass stream type id to cursor
13478: --OPEN line_reporting_csr(p_contract_line_id, l_end_date, l_stream_type_id); -- now passing l_end_date 26-Aug-2004 3849355
13479: --OPEN line_csr(p_contract_line_id, l_end_date, l_stream_type_id); -- now passing l_end_date 26-Aug-2004 3849355
13480: OPEN line_csr(p_contract_line_id, l_accrual_adjst_date, l_stream_type_id); -- asahoo now passing l_accrual_adjst_date bug 13259692
13479: --OPEN line_csr(p_contract_line_id, l_end_date, l_stream_type_id); -- now passing l_end_date 26-Aug-2004 3849355
13480: OPEN line_csr(p_contract_line_id, l_accrual_adjst_date, l_stream_type_id); -- asahoo now passing l_accrual_adjst_date bug 13259692
13481: FETCH line_csr INTO l_unearned_income;
13482: CLOSE line_csr;
13483: OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
13484: ELSE
13485: OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
13486: -- Start : Bug 6030917 : prasjain
13487: --added for prorating incase of partial unit termination
13481: FETCH line_csr INTO l_unearned_income;
13482: CLOSE line_csr;
13483: OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
13484: ELSE
13485: OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
13486: -- Start : Bug 6030917 : prasjain
13487: --added for prorating incase of partial unit termination
13488:
13489: IF nvl(l_proration_factor,1) = 1 THEN
13725: SELECT
13726: sel.amount
13727: FROM
13728: okl_strm_elements sel,
13729: okl_streams stm
13730: WHERE
13731: stm.sty_id = p_sty_id
13732: AND stm.khr_id = p_contract_id
13733: AND stm.kle_id = p_contract_line_id
13766: AND lalevl.rgd_code = 'LALEVL'
13767: AND lalevl.id = laslh.rgp_id
13768: AND laslh.rule_information_category = 'LASLH';
13769:
13770: OKL_STREAMS_UTIL.get_dependent_stream_type(
13771: p_khr_id => p_contract_id,
13772: p_primary_sty_id => l_fee_payment_id,
13773: p_dependent_sty_purpose => 'PRINCIPAL_BALANCE',
13774: x_return_status => l_return_status,
14080:
14081: CURSOR cr_unbilled_streams(c_contract_id IN NUMBER) IS
14082: SELECT NVL(sum(sel.amount),0)
14083: FROM okl_strm_elements sel,
14084: okl_streams stm,
14085: okl_strm_type_b sty
14086: WHERE stm.say_code = 'CURR'
14087: AND stm.active_yn = 'Y'
14088: AND stm.purpose_code is NULL
14568: -- get all unbilled receivables for all ACTIVE assets
14569: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
14570: SELECT NVL(SUM(sele.amount),0)
14571: FROM okl_strm_elements sele
14572: ,okl_streams str
14573: ,okl_strm_type_v sty
14574: ,okc_k_lines_v line
14575: ,okc_statuses_b sts
14576: ,okc_line_styles_b style
14624:
14625: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
14626: SELECT NVL(SUM(sele.amount),0)
14627: FROM okl_strm_elements sele
14628: --,okl_streams str MGAAP 7263041
14629: ,okl_streams_rep_v str
14630: ,okl_strm_type_v sty
14631: ,okc_k_lines_b line
14632: ,okc_line_styles_b style
14625: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
14626: SELECT NVL(SUM(sele.amount),0)
14627: FROM okl_strm_elements sele
14628: --,okl_streams str MGAAP 7263041
14629: ,okl_streams_rep_v str
14630: ,okl_strm_type_v sty
14631: ,okc_k_lines_b line
14632: ,okc_line_styles_b style
14633: ,okc_statuses_b sts
14674: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE
14675: ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
14676: SELECT NVL(SUM(sele.amount),0)
14677: FROM okl_strm_elements sele
14678: ,okl_streams str
14679: ,okl_strm_type_v sty
14680: ,okc_k_lines_v line
14681: -- ,okc_statuses_b sts
14682: ,okc_line_styles_b style
14731: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
14732: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
14733: SELECT NVL(SUM(sele.amount),0)
14734: FROM okl_strm_elements sele
14735: --,okl_streams str MGAAP 7263041
14736: ,okl_streams_rep_v str
14737: ,okl_strm_type_v sty
14738: ,okc_k_lines_b line
14739: ,okc_line_styles_b style
14732: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
14733: SELECT NVL(SUM(sele.amount),0)
14734: FROM okl_strm_elements sele
14735: --,okl_streams str MGAAP 7263041
14736: ,okl_streams_rep_v str
14737: ,okl_strm_type_v sty
14738: ,okc_k_lines_b line
14739: ,okc_line_styles_b style
14740: -- ,okc_statuses_b sts
14795:
14796: CURSOR cr_next_payment_date(c_contract_id IN NUMBER) IS
14797: SELECT MIN(sel.stream_element_date)
14798: FROM okl_strm_elements sel,
14799: okl_streams stm,
14800: okl_strm_type_v sty
14801: WHERE stm.sty_id = sty.id
14802: AND stm.say_code = 'CURR'
14803: AND stm.active_yn = 'Y'
14811: CURSOR cr_next_payment_amt(c_contract_id IN NUMBER,
14812: c_next_due_date IN DATE) IS
14813: SELECT NVL(sum(sel.amount),0)
14814: FROM okl_strm_elements sel,
14815: okl_streams stm,
14816: okl_strm_type_v sty
14817: WHERE stm.sty_id = sty.id
14818: AND stm.say_code = 'CURR'
14819: AND stm.active_yn = 'Y'
15295: RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
15296: END IF;
15297:
15298: -- 7626121
15299: l_streams_repo_policy := OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY;
15300: l_book_type_code := NULL;
15301: IF (l_streams_repo_policy = 'PRIMARY') THEN
15302: l_book_class := 'CORPORATE';
15303: ELSE