[Home] [Help]
30: CURSOR evergreen_contracts_csr ( p_contract_number VARCHAR2 ) IS
31: SELECT oklh.id khr_id,
32: okch.contract_number,
33: nvl(stm.kle_id, -99) kle_id
34: FROM okl_k_headers oklh,
35: okc_k_headers_b okch,
36: okc_statuses_b khs,
37: okl_streams stm
38: WHERE oklh.id = okch.id
62: CURSOR distinct_evergreen_contracts( p_contract_number VARCHAR2 ) IS
63: SELECT distinct oklh.id khr_id,
64: okch.contract_number,
65: iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bkrpcy_sts
66: FROM okl_k_headers oklh,
67: okc_k_headers_b okch,
68: okc_statuses_b khs,
69: okl_streams stm,
70: hz_cust_accounts hca
98: SELECT khr.contract_number contract_number,
99: stm.kle_id, --added by pgomes
100: stm.id stm_id,
101: sty.stream_type_purpose sty_name
102: FROM okl_k_headers_full_v khr,
103: okl_streams stm,
104: okl_strm_type_v sty
105: WHERE khr.id = p_khr_id
106: AND stm.khr_id = khr.id
369: CURSOR c_sec_strm_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER, p_stream_element_date DATE) IS
370: select distinct khr.id khr_id
371: from okl_pool_contents_v pol
372: , OKL_POOLS pool
373: ,okl_k_headers_full_v khr
374: where pol.khr_id = p_khr_id
375: and nvl(pol.kle_id, -99) = nvl(p_kle_id, -99)
376: and pol.sty_id = p_sty_id
377: and trunc(p_stream_element_date) between trunc(pol.streams_from_date) and trunc(pol.streams_to_date)
450: ------------------------------------------------------------
451: -- Declare local variables used in the program
452: ------------------------------------------------------------
453:
454: l_khr_id okl_k_headers.id%TYPE;
455: l_kle_id okl_streams.kle_id%TYPE;
456: l_sty_id okl_strm_type_v.id%TYPE;
457: l_evrgrn_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
458: l_evrgrn_prim_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
505: l_investor_agrmt_id NUMBER;
506: l_bill_freq OKC_RULES_B.object1_id1%TYPE;
507: l_primary_sty_id NUMBER;
508: l_primary_for_dep_sty_id NUMBER;
509: l_prev_khr_id okl_k_headers.id%TYPE; --dkagrawa added for bug# 4728636
510:
511: -----------------------------------------------------
512: -- Error Processing Variables
513: -----------------------------------------------------
1466: l_stmv_rec.date_current := sysdate;
1467: l_stmv_rec.comments := l_evrgrn_strm_purpose;
1468: IF (l_investor_agrmt_id IS NOT NULL) THEN
1469: l_stmv_rec.source_id := l_investor_agrmt_id;
1470: l_stmv_rec.source_table := 'OKL_K_HEADERS';
1471: END IF;
1472:
1473: FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating ' || l_evrgrn_strm_purpose || ' Stream');
1474:
1512:
1513: l_selv_rec.sel_id := l_sel_id;
1514: IF (l_investor_agrmt_id IS NOT NULL) THEN
1515: l_selv_rec.source_id := l_investor_agrmt_id;
1516: l_selv_rec.source_table := 'OKL_K_HEADERS';
1517: END IF;
1518:
1519: Okl_Sel_Pvt.insert_row(
1520: p_api_version,
1642: l_api_name CONSTANT VARCHAR2(30) := 'CHECK_FEE_EVERGREEN_BILL';
1643: x_msg_count NUMBER;
1644: x_msg_data VARCHAR2(2000);
1645:
1646: CURSOR c_assets_csr(p_chr_id OKL_K_HEADERS.KHR_ID%TYPE) IS
1647: SELECT kle.name,
1648: kle.id,
1649: kle.fee_type,
1650: ls.lty_code,
1657: AND kle.dnz_chr_id = p_chr_id
1658: AND sts.code = kle.sts_code
1659: AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED', 'TERMINATED');
1660:
1661: CURSOR c_fee_lines_csr(p_chr_id OKL_K_HEADERS.KHR_ID%TYPE) IS
1662: SELECT kle.name,
1663: kle.id,
1664: kle.fee_type
1665: FROM OKL_K_LINES_FULL_V kle,
1763: IS
1764: SELECT pdt.id pid,
1765: NVL(pdt.reporting_pdt_id, -1) report_pdt_id
1766: FROM okc_k_headers_v chr,
1767: okl_k_headers khr,
1768: okl_products_v pdt
1769: WHERE khr.id = chr.id
1770: AND chr.id = p_chrId
1771: AND khr.pdt_id = pdt.id(+);
1830: from okc_rules_b
1831: where rule_information_category = 'LAEVEL'
1832: and dnz_chr_id = p_chr_id
1833: and not exists (select '1'
1834: from okl_k_headers
1835: where deal_type in ('LOAN', 'LOAN-REVOLVING')
1836: and id = p_chr_id);
1837: exception
1838: when no_data_found then