[Home] [Help]
269: l_max_version NUMBER;
270:
271: -- For validating the template
272: l_error_msgs_tbl error_msgs_tbl_type;
273: l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
274:
275: -- Modified by RGOOTY
276: -- Bug 4094361: Start
277: CURSOR pdt_for_active_sgt_csr( p_gts_id OKL_ST_GEN_TMPT_SETS.ID%TYPE )
283: WHERE PDT.AES_ID = ATS.ID
284: AND ATS.GTS_ID = SGT.ID
285: AND SGT.ID = p_gts_id;
286:
287: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
288: IS
289: SELECT GTT.GTS_ID
290: FROM OKL_ST_GEN_TEMPLATES GTT
291: WHERE GTT.ID = p_gtt_id;
286:
287: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
288: IS
289: SELECT GTT.GTS_ID
290: FROM OKL_ST_GEN_TEMPLATES GTT
291: WHERE GTT.ID = p_gtt_id;
292:
293: CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
294: IS
289: SELECT GTT.GTS_ID
290: FROM OKL_ST_GEN_TEMPLATES GTT
291: WHERE GTT.ID = p_gtt_id;
292:
293: CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
294: IS
295: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
296: FROM OKL_ST_GEN_TEMPLATES GTT
297: WHERE GTT.ID = p_gtt_id;
292:
293: CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
294: IS
295: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
296: FROM OKL_ST_GEN_TEMPLATES GTT
297: WHERE GTT.ID = p_gtt_id;
298:
299: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
300: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
295: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
296: FROM OKL_ST_GEN_TEMPLATES GTT
297: WHERE GTT.ID = p_gtt_id;
298:
299: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
300: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
301: IS
302: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
303: FROM OKL_ST_GEN_TEMPLATES GTT
296: FROM OKL_ST_GEN_TEMPLATES GTT
297: WHERE GTT.ID = p_gtt_id;
298:
299: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
300: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
301: IS
302: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
303: FROM OKL_ST_GEN_TEMPLATES GTT
304: WHERE GTT.gts_ID = p_gts_id
299: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
300: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
301: IS
302: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
303: FROM OKL_ST_GEN_TEMPLATES GTT
304: WHERE GTT.gts_ID = p_gts_id
305: AND version = p_version;
306:
307: CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
303: FROM OKL_ST_GEN_TEMPLATES GTT
304: WHERE GTT.gts_ID = p_gts_id
305: AND version = p_version;
306:
307: CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
308: select max(chr.start_date) MAX_START_DATE
309: from okc_k_headers_b chr,
310: okl_k_headers khr,
311: okl_ae_tmpt_Sets aes,
308: select max(chr.start_date) MAX_START_DATE
309: from okc_k_headers_b chr,
310: okl_k_headers khr,
311: okl_ae_tmpt_Sets aes,
312: okl_st_gen_templates gtt,
313: okl_products pdt
314: where pdt.id = khr.pdt_id
315: and pdt.aes_id = aes.id
316: and aes.gts_id = gtt.gts_id
322: --srsreeni Bug5996170 end
323: ;
324:
325: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
326: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
327: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
328: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
329: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
330: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
323: ;
324:
325: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
326: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
327: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
328: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
329: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
330: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
331:
324:
325: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
326: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
327: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
328: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
329: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
330: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
331:
332: -- Bug 4094361: End
325: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
326: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
327: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
328: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
329: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
330: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
331:
332: -- Bug 4094361: End
333: BEGIN
461: IF l_tmpt_status = G_STATUS_ACTIVE THEN
462: -- Bug 9435866
463: SELECT max(FND_NUMBER.CANONICAL_TO_NUMBER(version)) -- Bug 10369520
464: INTO l_max_version
465: FROM OKL_ST_GEN_TEMPLATES
466: WHERE gts_id = (SELECT gts_id
467: FROM OKL_ST_GEN_TEMPLATES
468: WHERE id = l_gttv_rec_in.id);
469:
463: SELECT max(FND_NUMBER.CANONICAL_TO_NUMBER(version)) -- Bug 10369520
464: INTO l_max_version
465: FROM OKL_ST_GEN_TEMPLATES
466: WHERE gts_id = (SELECT gts_id
467: FROM OKL_ST_GEN_TEMPLATES
468: WHERE id = l_gttv_rec_in.id);
469:
470: -- The following check should be done for the latest version
471: IF FND_NUMBER.CANONICAL_TO_NUMBER(l_gttv_rec_in.version) = l_max_version THEN -- Bug 10369520
761: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
762: ,x_return_status OUT NOCOPY VARCHAR2
763: ,x_msg_count OUT NOCOPY NUMBER
764: ,x_msg_data OUT NOCOPY VARCHAR2
765: ,p_gtt_id IN okl_st_gen_templates.id%type
766: ,p_mode IN VARCHAR2 DEFAULT G_DEFAULT_MODE
767: ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
768: ) IS
769:
766: ,p_mode IN VARCHAR2 DEFAULT G_DEFAULT_MODE
767: ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
768: ) IS
769:
770: l_gtt_id OKL_ST_GEN_TEMPLATES.id%type := p_gtt_id;
771: l_mode VARCHAR2(10) := p_mode;
772: l_api_name CONSTANT VARCHAR2(40) := 'create_version_duplicate';
773: l_api_version CONSTANT NUMBER := 1.0;
774: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
810: ,isg_arrears_pay_dates_option
811: FROM OKL_ST_GEN_TMPT_SETS
812: WHERE ID = p_gts_id;
813:
814: CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
815: SELECT ID
816: ,GTS_ID
817: ,VERSION
818: ,START_DATE
817: ,VERSION
818: ,START_DATE
819: ,END_DATE
820: ,TMPT_STATUS
821: FROM OKL_ST_GEN_TEMPLATES
822: where ID = p_gtt_id;
823:
824: CURSOR okl_st_gen_prc_params_csr(p_gtt_id IN OKL_ST_GEN_PRC_PARAMS.gtt_id%type) IS
825: SELECT ID
844: FROM OKL_ST_GEN_TMPT_LNS
845: where GTT_ID = p_gtt_id
846: AND PRIMARY_YN = p_primary_yn;
847:
848: CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
849: select max(chr.start_date) MAX_START_DATE
850: from okc_k_headers_b chr,
851: okl_k_headers khr,
852: okl_ae_tmpt_Sets aes,
849: select max(chr.start_date) MAX_START_DATE
850: from okc_k_headers_b chr,
851: okl_k_headers khr,
852: okl_ae_tmpt_Sets aes,
853: okl_st_gen_templates gtt,
854: okl_products pdt
855: where pdt.id = khr.pdt_id
856: and pdt.aes_id = aes.id
857: and aes.gts_id = gtt.gts_id
885: RAISE OKL_API.G_EXCEPTION_ERROR;
886: END IF;
887:
888: l_dummy := Okl_Api.G_FALSE;
889: FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
890: LOOP
891: l_gttv_rec_in.id := gttv_rec_temp.id;
892: l_gttv_rec_in.gts_id := gttv_rec_temp.gts_id;
893: l_gttv_rec_in.version := gttv_rec_temp.version;
1157: ,x_msg_count OUT NOCOPY NUMBER
1158: ,x_msg_data OUT NOCOPY VARCHAR2
1159: ,p_gtpv_tbl IN gtpv_tbl_type
1160: ) IS
1161: l_gtt_id okl_st_gen_templates.id%type ;
1162: l_api_name CONSTANT VARCHAR2(40) := 'delete_tmpt_prc_params';
1163: l_api_version CONSTANT NUMBER := 1.0;
1164: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1165:
1250: ,x_msg_count OUT NOCOPY NUMBER
1251: ,x_msg_data OUT NOCOPY VARCHAR2
1252: ,p_gtlv_tbl IN gtlv_tbl_type
1253: )IS
1254: l_gtt_id okl_st_gen_templates.id%type;
1255: l_api_name CONSTANT VARCHAR2(40) := 'delete_pri_tmpt_lns';
1256: l_api_version CONSTANT NUMBER := 1.0;
1257: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1258:
1389: ,x_msg_count OUT NOCOPY NUMBER
1390: ,x_msg_data OUT NOCOPY VARCHAR2
1391: ,p_gtlv_tbl IN gtlv_tbl_type
1392: )IS
1393: l_gtt_id okl_st_gen_templates.id%type;
1394: l_api_name CONSTANT VARCHAR2(40) := 'delete_dep_tmpt_lns';
1395: l_api_version CONSTANT NUMBER := 1.0;
1396: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1397:
1591: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1592: ,x_return_status OUT NOCOPY VARCHAR2
1593: ,x_msg_count OUT NOCOPY NUMBER
1594: ,x_msg_data OUT NOCOPY VARCHAR2
1595: ,p_gtt_id IN okl_st_gen_templates.id%type
1596: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
1597: ,p_during_upd_flag IN VARCHAR
1598: ,p_book_classification IN VARCHAR
1599: )
1606: days_in_yr_code
1607: FROM OKL_ST_GEN_TMPT_SETS
1608: WHERE ID = (
1609: SELECT gts_id
1610: FROM okl_st_gen_templates
1611: WHERE id = p_gtt_id
1612: );
1613:
1614: CURSOR get_billable_flag IS
1995: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1996: ,x_return_status OUT NOCOPY VARCHAR2
1997: ,x_msg_count OUT NOCOPY NUMBER
1998: ,x_msg_data OUT NOCOPY VARCHAR2
1999: ,p_gtt_id IN okl_st_gen_templates.id%type
2000: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
2001: ,p_during_upd_flag IN VARCHAR
2002: ,p_book_classification IN VARCHAR
2003:
2001: ,p_during_upd_flag IN VARCHAR
2002: ,p_book_classification IN VARCHAR
2003:
2004: )IS
2005: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
2006: l_api_name CONSTANT VARCHAR2(40) := 'validate_financial_template';
2007: l_api_version CONSTANT NUMBER := 1.0;
2008: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2009: l_error_msgs_tbl error_msgs_tbl_type;
2023:
2024:
2025: -- 1. If user selects one of the following Insurance purposes then all the
2026: -- insurance stream purposes must be defined.
2027: CURSOR okl_ins_purposes_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2028: SELECT count(*) ins_strms_count
2029: FROM OKL_ST_GEN_TMPT_LNS gtl
2030: , okl_strm_type_b stb
2031: WHERE gtl.primary_sty_id = stb.id
2049: -- The List contains Purposes for all Book Classifications.
2050: -- The sql statement checkes whether the purposes mentioned in the set are repeated
2051: -- or not. So, If they are not defined then that doesnot violoate the logic of the
2052: -- SQL statement.
2053: CURSOR fin_only_one_as_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2054: SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
2055: FROM OKL_ST_GEN_TMPT_LNS gtl
2056: ,okl_strm_type_b stb
2057: WHERE gtl.primary_sty_id = stb.id
2142: group by stb.stream_type_purpose
2143: having count(stb.stream_type_purpose) > 1;
2144:
2145: -- 3. All of the following primary purposes must be defined
2146: CURSOR fin_mandatory_pri_all_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2147: SELECT LOOKUP_CODE PURPOSE
2148: FROM FND_LOOKUPS
2149: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2150: AND LOOKUP_CODE IN
2206: ,'REBOOK_BILLING_ADJUSTMENT' -- VR Upgrade.
2207: )
2208: and GTL.GTT_ID = p_gtt_id;
2209:
2210: CURSOR fin_mandatory_pri_ln_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2211: SELECT LOOKUP_CODE PURPOSE
2212: FROM FND_LOOKUPS
2213: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2214: AND LOOKUP_CODE IN
2226: 'VARIABLE_INTEREST'
2227: )
2228: and GTL.GTT_ID = p_gtt_id;
2229:
2230: CURSOR fin_mandatory_pri_ls_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2231: SELECT LOOKUP_CODE PURPOSE
2232: FROM FND_LOOKUPS
2233: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2234: AND LOOKUP_CODE IN
2247: )
2248: and GTL.GTT_ID = p_gtt_id;
2249:
2250: --Bug 5139013 dpsingh start
2251: CURSOR fin_mandatory_pri_ic_float_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2252: SELECT LOOKUP_CODE PURPOSE
2253: FROM FND_LOOKUPS
2254: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2255: AND LOOKUP_CODE ='VARIABLE_INTEREST_SCHEDULE'
2264: --Bug 5139013 dpsingh end
2265:
2266: -- Modified by RGOOTY
2267: -- Bug 4111081: Start
2268: CURSOR fin_mandatory_pri_op_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2269: SELECT LOOKUP_CODE PURPOSE
2270: FROM FND_LOOKUPS
2271: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2272: AND LOOKUP_CODE IN
2284: 'INVESTOR_RENTAL_ACCRUAL'
2285: )
2286: and GTL.GTT_ID = p_gtt_id;
2287:
2288: CURSOR fin_mandatory_pri_df_n_st_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2289: SELECT LOOKUP_CODE PURPOSE
2290: FROM FND_LOOKUPS
2291: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2292: AND LOOKUP_CODE IN
2309: -- Modified by RGOOTY
2310: -- Bug 4129154: Start
2311:
2312: -- 4. Mandatory Dependent Streams
2313: CURSOR man_dep_all_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2314: SELECT LOOKUP_CODE PURPOSE
2315: FROM FND_LOOKUPS
2316: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2317: AND LOOKUP_CODE IN
2343: AND gtlpri.gtt_id = p_gtt_id
2344: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2345: );
2346:
2347: CURSOR man_dep_ln_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2348: SELECT LOOKUP_CODE PURPOSE
2349: FROM FND_LOOKUPS
2350: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2351: AND LOOKUP_CODE IN
2389: AND gtlpri.gtt_id = p_gtt_id
2390: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2391: );
2392:
2393: CURSOR man_dep_ln_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2394: SELECT LOOKUP_CODE PURPOSE
2395: FROM FND_LOOKUPS
2396: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2397: AND LOOKUP_CODE IN
2437: AND gtlpri.gtt_id = p_gtt_id
2438: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2439: );
2440:
2441: CURSOR man_dep_dfstop_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2442: SELECT LOOKUP_CODE PURPOSE
2443: FROM FND_LOOKUPS
2444: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2445: AND LOOKUP_CODE IN
2471: AND gtlpri.gtt_id = p_gtt_id
2472: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2473: );
2474:
2475: CURSOR man_dep_ln_icc_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2476: SELECT LOOKUP_CODE PURPOSE
2477: FROM FND_LOOKUPS
2478: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2479: AND LOOKUP_CODE IN
2506: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2507: );
2508:
2509: -- rgooty BUG#4290143 start
2510: CURSOR man_dep_ln_var_int_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2511: SELECT LOOKUP_CODE PURPOSE
2512: FROM FND_LOOKUPS
2513: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2514: AND LOOKUP_CODE = 'VARIABLE_INTEREST_INCOME'
2535: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2536: );
2537: -- rgooty BUG#4290143 end
2538:
2539: CURSOR man_dep_df_st_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2540: SELECT LOOKUP_CODE PURPOSE
2541: FROM FND_LOOKUPS
2542: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2543: AND LOOKUP_CODE IN
2569: AND gtlpri.gtt_id = p_gtt_id
2570: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2571: );
2572:
2573: CURSOR man_dep_ls_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2574: SELECT LOOKUP_CODE PURPOSE
2575: FROM FND_LOOKUPS
2576: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2577: AND LOOKUP_CODE IN
2607: AND gtlpri.gtt_id = p_gtt_id
2608: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2609: );
2610:
2611: CURSOR man_dep_ls_rv_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2612: SELECT LOOKUP_CODE PURPOSE
2613: FROM FND_LOOKUPS
2614: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2615: AND LOOKUP_CODE IN
2653: AND gtlpri.gtt_id = p_gtt_id
2654: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2655: );
2656:
2657: CURSOR man_dep_op_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2658: SELECT LOOKUP_CODE PURPOSE
2659: FROM FND_LOOKUPS
2660: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2661: AND LOOKUP_CODE IN
2689: );
2690: -- Bug 4129154: End
2691:
2692: -- Rule 6. Certain Book classifications can have certain Purposes only.
2693: CURSOR purposes_for_df_and_st_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2694: (
2695: -- Retrieve the List of Primary Stream Purposes in the Template
2696: SELECT distinct stb.stream_type_purpose purpose
2697: FROM OKL_ST_GEN_TMPT_LNS gtl
2850: --srsreeni 6117982 ends
2851: , 'UPFRONT_TAX_BILLED' -- bug6619311
2852: );
2853:
2854: CURSOR purposes_for_op_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2855: (
2856: -- Retrieve the List of Primary Stream Purposes in the Template
2857: SELECT distinct stb.stream_type_purpose purpose
2858: FROM OKL_ST_GEN_TMPT_LNS gtl
3011: --srsreeni 6117982 ends
3012: , 'UPFRONT_TAX_BILLED' -- bug 6619311
3013: );
3014:
3015: CURSOR purposes_for_ln_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3016: (
3017: -- Retrieve the List of Primary Stream Purposes in the Template
3018: SELECT distinct stb.stream_type_purpose purpose
3019: FROM OKL_ST_GEN_TMPT_LNS gtl
3165: , 'UPFRONT_TAX_BILLED' -- bug 6619311
3166: );
3167: -- Modified by RGOOTY
3168: -- Bug 4129154: Start
3169: CURSOR fetch_sgt_info( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type)
3170: IS
3171: SELECT gts.revenue_recog_meth_code revenue_recog_meth_code,
3172: gts.interest_calc_meth_code interest_calc_meth_code
3173: FROM OKL_ST_GEN_TMPT_SETS gts,
3170: IS
3171: SELECT gts.revenue_recog_meth_code revenue_recog_meth_code,
3172: gts.interest_calc_meth_code interest_calc_meth_code
3173: FROM OKL_ST_GEN_TMPT_SETS gts,
3174: OKL_ST_GEN_TEMPLATES gtt
3175: WHERE gtt.gts_id = gts.id
3176: AND gtt.id = p_gtt_id;
3177: l_rrm OKL_ST_GEN_TMPT_SETS.revenue_recog_meth_code%TYPE;
3178: l_icm OKL_ST_GEN_TMPT_SETS.interest_calc_meth_code%TYPE;
3692: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3693: ,x_return_status OUT NOCOPY VARCHAR2
3694: ,x_msg_count OUT NOCOPY NUMBER
3695: ,x_msg_data OUT NOCOPY VARCHAR2
3696: ,p_gtt_id IN okl_st_gen_templates.id%type
3697: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
3698: ,p_during_upd_flag IN VARCHAR
3699: )IS
3700: l_api_name CONSTANT VARCHAR2(40) := 'validate_investor_template';
3708: l_msg_data VARCHAR2(2000);
3709: l_dummy VARCHAR2(1) := okl_api.G_FALSE;
3710:
3711: -- 1. Only one stream type of the following primary purposes should be defined.
3712: CURSOR inv_only_one_as_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3713: SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
3714: FROM OKL_ST_GEN_TMPT_LNS gtl
3715: ,okl_strm_type_b stb
3716: WHERE gtl.primary_sty_id = stb.id
3737: group by stb.stream_type_purpose
3738: having count(stb.stream_type_purpose) > 1;
3739:
3740: -- 3. All of the following primary purposes must be defined
3741: CURSOR inv_mandatory_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3742: SELECT LOOKUP_CODE PURPOSE
3743: FROM FND_LOOKUPS
3744: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
3745: AND LOOKUP_CODE IN
3897: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3898: ,x_return_status OUT NOCOPY VARCHAR2
3899: ,x_msg_count OUT NOCOPY NUMBER
3900: ,x_msg_data OUT NOCOPY VARCHAR2
3901: ,p_gtt_id IN okl_st_gen_templates.id%type
3902: )IS
3903:
3904: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
3905: l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
3900: ,x_msg_data OUT NOCOPY VARCHAR2
3901: ,p_gtt_id IN okl_st_gen_templates.id%type
3902: )IS
3903:
3904: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
3905: l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
3906: l_api_version CONSTANT NUMBER := 1.0;
3907: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3908:
3912: l_dummy VARCHAR2(1) := okl_api.G_FALSE;
3913: l_gttv_rec_in gttv_rec_type;
3914: l_gttv_rec_out gttv_rec_type;
3915:
3916: CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3917: SELECT ID
3918: ,GTS_ID
3919: ,VERSION
3920: ,START_DATE
3919: ,VERSION
3920: ,START_DATE
3921: ,END_DATE
3922: ,TMPT_STATUS
3923: FROM OKL_ST_GEN_TEMPLATES
3924: where ID = p_gtt_id;
3925: BEGIN
3926: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3927: l_return_status := OKL_API.START_ACTIVITY( l_api_name
3937: RAISE OKL_API.G_EXCEPTION_ERROR;
3938: END IF;
3939:
3940: l_dummy := Okl_Api.G_FALSE;
3941: FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
3942: LOOP
3943: l_gttv_rec_in.id := gttv_rec_temp.id;
3944: l_gttv_rec_in.gts_id := gttv_rec_temp.gts_id;
3945: l_gttv_rec_in.version := gttv_rec_temp.version;
4030: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4031: ,x_return_status OUT NOCOPY VARCHAR2
4032: ,x_msg_count OUT NOCOPY NUMBER
4033: ,x_msg_data OUT NOCOPY VARCHAR2
4034: ,p_gtt_id IN okl_st_gen_templates.id%type
4035: ,p_pri_sty_id IN okl_st_gen_tmpt_lns.primary_Sty_id%type
4036: ,x_missing_deps OUT NOCOPY VARCHAR2
4037: ,x_show_warn_flag OUT NOCOPY VARCHAR2
4038: ,p_deal_type IN VARCHAR2
4036: ,x_missing_deps OUT NOCOPY VARCHAR2
4037: ,x_show_warn_flag OUT NOCOPY VARCHAR2
4038: ,p_deal_type IN VARCHAR2
4039: )IS
4040: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4041: l_api_name CONSTANT VARCHAR2(40) := 'validate_mandatory_dep';
4042: l_api_version CONSTANT NUMBER := 1.0;
4043: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4044:
4044:
4045: l_purpose_list VARCHAR2(10000);
4046: -- Cursor to fetch the missed dependent streams for a Primary Stream type
4047: -- with purpose as 'Fee Payment'
4048: CURSOR fee_payment_man_dep_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type
4049: ,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.primary_sty_id%TYPE
4050: ,p_exclude_fr IN VARCHAR2 ) IS
4051: SELECT LOOKUP_CODE PURPOSE
4052: FROM FND_LOOKUPS
4146: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4147: ,x_return_status OUT NOCOPY VARCHAR2
4148: ,x_msg_count OUT NOCOPY NUMBER
4149: ,x_msg_data OUT NOCOPY VARCHAR2
4150: ,p_gtt_id IN okl_st_gen_templates.id%type
4151: ,x_wrn_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4152: ,p_during_upd_flag IN VARCHAR
4153: ,x_pri_purpose_list OUT NOCOPY VARCHAR
4154: )IS
4152: ,p_during_upd_flag IN VARCHAR
4153: ,x_pri_purpose_list OUT NOCOPY VARCHAR
4154: )IS
4155:
4156: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4157: l_api_name CONSTANT VARCHAR2(40) := 'validate_for_warnings';
4158: l_api_version CONSTANT NUMBER := 1.0;
4159: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4160: l_missing_deps VARCHAR2(32000);
4164: l_message VARCHAR2(2700);
4165: l_pri_purpose_list VARCHAR2(32000);
4166:
4167: -- Cursor to fetch the Primary Stream Types with purpose as 'FEE PAYMENT'
4168: CURSOR fee_payment_pri_strms_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
4169: SELECT GTL.PRIMARY_STY_ID PRI_STY_ID
4170: ,GTS.DEAL_TYPE DEAL_TYPE
4171: ,STB.ID STRM_TYPE_ID
4172: ,STB.CODE STRM_NAME
4171: ,STB.ID STRM_TYPE_ID
4172: ,STB.CODE STRM_NAME
4173: FROM OKL_ST_GEN_TMPT_LNS GTL
4174: ,OKL_ST_GEN_TMPT_SETS GTS
4175: ,OKL_ST_GEN_TEMPLATES GTT
4176: ,OKL_STRM_TYPE_B STB
4177: WHERE GTL.GTT_ID = p_gtt_id
4178: AND GTL.GTT_ID = GTT.ID
4179: AND GTS.ID = GTT.GTS_ID
4269: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4270: ,x_return_status OUT NOCOPY VARCHAR2
4271: ,x_msg_count OUT NOCOPY NUMBER
4272: ,x_msg_data OUT NOCOPY VARCHAR2
4273: ,p_gtt_id IN okl_st_gen_templates.id%type
4274: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4275: ,x_return_tmpt_status OUT NOCOPY VARCHAR2
4276: ,p_during_upd_flag IN VARCHAR2
4277: )IS
4274: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4275: ,x_return_tmpt_status OUT NOCOPY VARCHAR2
4276: ,p_during_upd_flag IN VARCHAR2
4277: )IS
4278: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4279: l_api_name CONSTANT VARCHAR2(40) := 'validate_template';
4280: l_api_version CONSTANT NUMBER := 1.0;
4281: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4282: l_error_msgs_tbl error_msgs_tbl_type;
4291:
4292: l_gttv_rec_in gttv_rec_type;
4293: l_gttv_rec_out gttv_rec_type;
4294:
4295: CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
4296: SELECT GTT.ID ID
4297: ,GTT.GTS_ID GTS_ID
4298: ,GTS.NAME NAME
4299: ,GTS.PRODUCT_TYPE PRODUCT_TYPE
4301: ,GTT.START_DATE START_DATE
4302: ,GTT.END_DATE END_DATE
4303: ,GTT.TMPT_STATUS TMPT_STATUS
4304: ,GTT.VERSION VERSION
4305: FROM OKL_ST_GEN_TEMPLATES GTT
4306: ,OKL_ST_GEN_TMPT_SETS GTS
4307: WHERE GTT.GTS_ID = GTS.ID
4308: AND GTT.ID = p_gtt_id;
4309: l_tmpt_set_rec okl_st_gen_templates_csr%ROWTYPE;
4305: FROM OKL_ST_GEN_TEMPLATES GTT
4306: ,OKL_ST_GEN_TMPT_SETS GTS
4307: WHERE GTT.GTS_ID = GTS.ID
4308: AND GTT.ID = p_gtt_id;
4309: l_tmpt_set_rec okl_st_gen_templates_csr%ROWTYPE;
4310: BEGIN
4311: -- Perform the Initializations
4312: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4313: l_return_status := OKL_API.START_ACTIVITY( l_api_name
4322: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4323: RAISE OKL_API.G_EXCEPTION_ERROR;
4324: END IF;
4325: l_dummy := Okl_Api.G_FALSE;
4326: FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
4327: LOOP
4328: l_tmpt_set_rec.id := gttv_rec_temp.id;
4329: l_tmpt_set_rec.gts_id := gttv_rec_temp.gts_id;
4330: l_tmpt_set_rec.start_date := gttv_rec_temp.start_date;
4502: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4503: ,x_return_status OUT NOCOPY VARCHAR2
4504: ,x_msg_count OUT NOCOPY NUMBER
4505: ,x_msg_data OUT NOCOPY VARCHAR2
4506: ,p_gtt_id IN OKL_ST_GEN_TEMPLATES.ID%type
4507: ,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE
4508: ,p_gtlv_tbl IN gtlv_tbl_type
4509: ,x_missing_deps OUT NOCOPY VARCHAR2
4510: ,x_show_warn_flag OUT NOCOPY VARCHAR2
4529: upd_table_count NUMBER;
4530:
4531: --Added by bkatraga on 05-Apr-2005
4532: l_error_msgs_tbl error_msgs_tbl_type;
4533: l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4534: --end changes
4535:
4536: -- Modified by RGOOTY
4537: -- Bug 4054596: Issue No. 5: Start
4534: --end changes
4535:
4536: -- Modified by RGOOTY
4537: -- Bug 4054596: Issue No. 5: Start
4538: CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE
4539: ,p_pri_id IN OKL_ST_GEN_TMPT_LNS.primary_Sty_id%TYPE) IS
4540: SELECT STY.STREAM_TYPE_PURPOSE PURPOSE_CODE,
4541: ( SELECT MEANING FROM
4542: FND_LOOKUPS
4558: l_pri_sty_id OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE;
4559: l_purp_list VARCHAR2(250);
4560: -- Bug 4054596: Issue No. 5: End
4561:
4562: l_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4563: l_deal_type OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE;
4564:
4565: CURSOR tmpt_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE ) IS
4566: SELECT GTS.DEAL_TYPE DEAL_TYPE
4561:
4562: l_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4563: l_deal_type OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE;
4564:
4565: CURSOR tmpt_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE ) IS
4566: SELECT GTS.DEAL_TYPE DEAL_TYPE
4567: ,GTT.TMPT_STATUS TMPT_STATUS
4568: ,GTS.ID GTS_ID -- Bug 4094361, Modified by RGOOTY
4569: FROM OKL_ST_GEN_TMPT_SETS GTS
4566: SELECT GTS.DEAL_TYPE DEAL_TYPE
4567: ,GTT.TMPT_STATUS TMPT_STATUS
4568: ,GTS.ID GTS_ID -- Bug 4094361, Modified by RGOOTY
4569: FROM OKL_ST_GEN_TMPT_SETS GTS
4570: ,OKL_ST_GEN_TEMPLATES GTT
4571: WHERE GTS.ID = GTT.GTS_ID
4572: AND GTT.ID = p_gtt_id;
4573:
4574: CURSOR tmpt_pri_strm_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE
4570: ,OKL_ST_GEN_TEMPLATES GTT
4571: WHERE GTS.ID = GTT.GTS_ID
4572: AND GTT.ID = p_gtt_id;
4573:
4574: CURSOR tmpt_pri_strm_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE
4575: ,p_pri_sty_id OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE)
4576: IS
4577: SELECT STY.STREAM_TYPE_PURPOSE PRI_STRM_PURPOSE
4578: FROM OKL_ST_GEN_TMPT_LNS GTL
4836: ,x_gttv_rec OUT NOCOPY gttv_rec_type
4837: ,x_pri_purpose_list OUT NOCOPY VARCHAR2)
4838: IS
4839: -- Cursor to fetch the Stream Generation Template Set ID
4840: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
4841: IS
4842: SELECT GTT.GTS_ID
4843: FROM OKL_ST_GEN_TEMPLATES GTT
4844: WHERE GTT.ID = p_gtt_id;
4839: -- Cursor to fetch the Stream Generation Template Set ID
4840: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
4841: IS
4842: SELECT GTT.GTS_ID
4843: FROM OKL_ST_GEN_TEMPLATES GTT
4844: WHERE GTT.ID = p_gtt_id;
4845: -- Cursor to get the Dependent Stream Purpose Meaning which has been included more than once
4846: -- for a particular primary Stream
4847: CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE )
4843: FROM OKL_ST_GEN_TEMPLATES GTT
4844: WHERE GTT.ID = p_gtt_id;
4845: -- Cursor to get the Dependent Stream Purpose Meaning which has been included more than once
4846: -- for a particular primary Stream
4847: CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE )
4848: IS
4849: SELECT STYP.CODE PRI_STRM_TYPE_NAME,
4850: -- gboomina Bug 4874272 - Added - Start
4851: STYP.STREAM_TYPE_PURPOSE PRI_STRM_TYPE_PURPOSE,
4890: -- Local Variables Declaration
4891: l_missing_deps VARCHAR2(32000);
4892: l_show_warn_flag VARCHAR2(30);
4893: l_error_msgs_tbl error_msgs_tbl_type;
4894: l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4895: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
4896: l_pri_purpose_list VARCHAR2(32000);
4897: l_found VARCHAR2(30);
4898: l_msg VARCHAR2(10000);