[Home] [Help]
268: upd_table_count NUMBER;
269:
270: -- For validating the template
271: l_error_msgs_tbl error_msgs_tbl_type;
272: l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
273:
274: -- Modified by RGOOTY
275: -- Bug 4094361: Start
276: CURSOR pdt_for_active_sgt_csr( p_gts_id OKL_ST_GEN_TMPT_SETS.ID%TYPE )
282: WHERE PDT.AES_ID = ATS.ID
283: AND ATS.GTS_ID = SGT.ID
284: AND SGT.ID = p_gts_id;
285:
286: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
287: IS
288: SELECT GTT.GTS_ID
289: FROM OKL_ST_GEN_TEMPLATES GTT
290: WHERE GTT.ID = p_gtt_id;
285:
286: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
287: IS
288: SELECT GTT.GTS_ID
289: FROM OKL_ST_GEN_TEMPLATES GTT
290: WHERE GTT.ID = p_gtt_id;
291:
292: CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
293: IS
288: SELECT GTT.GTS_ID
289: FROM OKL_ST_GEN_TEMPLATES GTT
290: WHERE GTT.ID = p_gtt_id;
291:
292: CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
293: IS
294: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
295: FROM OKL_ST_GEN_TEMPLATES GTT
296: WHERE GTT.ID = p_gtt_id;
291:
292: CURSOR fetch_gtt_dtls_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
293: IS
294: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
295: FROM OKL_ST_GEN_TEMPLATES GTT
296: WHERE GTT.ID = p_gtt_id;
297:
298: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
299: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
294: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
295: FROM OKL_ST_GEN_TEMPLATES GTT
296: WHERE GTT.ID = p_gtt_id;
297:
298: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
299: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
300: IS
301: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
302: FROM OKL_ST_GEN_TEMPLATES GTT
295: FROM OKL_ST_GEN_TEMPLATES GTT
296: WHERE GTT.ID = p_gtt_id;
297:
298: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
299: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
300: IS
301: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
302: FROM OKL_ST_GEN_TEMPLATES GTT
303: WHERE GTT.gts_ID = p_gts_id
298: CURSOR fetch_gtt_ver_dtls_csr( p_gts_id OKL_ST_GEN_TEMPLATES.ID%TYPE,
299: p_version OKL_ST_GEN_TEMPLATES.version%TYPE)
300: IS
301: SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
302: FROM OKL_ST_GEN_TEMPLATES GTT
303: WHERE GTT.gts_ID = p_gts_id
304: AND version = p_version;
305:
306: CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
302: FROM OKL_ST_GEN_TEMPLATES GTT
303: WHERE GTT.gts_ID = p_gts_id
304: AND version = p_version;
305:
306: CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
307: select max(chr.start_date) MAX_START_DATE
308: from okc_k_headers_b chr,
309: okl_k_headers khr,
310: okl_ae_tmpt_Sets aes,
307: select max(chr.start_date) MAX_START_DATE
308: from okc_k_headers_b chr,
309: okl_k_headers khr,
310: okl_ae_tmpt_Sets aes,
311: okl_st_gen_templates gtt,
312: okl_products pdt
313: where pdt.id = khr.pdt_id
314: and pdt.aes_id = aes.id
315: and aes.gts_id = gtt.gts_id
321: --srsreeni Bug5996170 end
322: ;
323:
324: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
325: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
326: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
327: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
328: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
329: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
322: ;
323:
324: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
325: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
326: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
327: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
328: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
329: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
330:
323:
324: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
325: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
326: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
327: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
328: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
329: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
330:
331: -- Bug 4094361: End
324: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
325: l_version OKL_ST_GEN_TEMPLATES.version%TYPE;
326: l_tmpt_status OKL_ST_GEN_TEMPLATES.tmpt_status%TYPE;
327: l_start_date OKL_ST_GEN_TEMPLATES.start_date%TYPE;
328: l_end_date OKL_ST_GEN_TEMPLATES.end_date%TYPE;
329: l_okl_new_version_date_rec okl_new_version_date_csr%ROWTYPE;
330:
331: -- Bug 4094361: End
332: BEGIN
747: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
748: ,x_return_status OUT NOCOPY VARCHAR2
749: ,x_msg_count OUT NOCOPY NUMBER
750: ,x_msg_data OUT NOCOPY VARCHAR2
751: ,p_gtt_id IN okl_st_gen_templates.id%type
752: ,p_mode IN VARCHAR2 DEFAULT G_DEFAULT_MODE
753: ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
754: ) IS
755:
752: ,p_mode IN VARCHAR2 DEFAULT G_DEFAULT_MODE
753: ,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
754: ) IS
755:
756: l_gtt_id OKL_ST_GEN_TEMPLATES.id%type := p_gtt_id;
757: l_mode VARCHAR2(10) := p_mode;
758: l_api_name CONSTANT VARCHAR2(40) := 'create_version_duplicate';
759: l_api_version CONSTANT NUMBER := 1.0;
760: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
796: ,isg_arrears_pay_dates_option
797: FROM OKL_ST_GEN_TMPT_SETS
798: WHERE ID = p_gts_id;
799:
800: CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
801: SELECT ID
802: ,GTS_ID
803: ,VERSION
804: ,START_DATE
803: ,VERSION
804: ,START_DATE
805: ,END_DATE
806: ,TMPT_STATUS
807: FROM OKL_ST_GEN_TEMPLATES
808: where ID = p_gtt_id;
809:
810: CURSOR okl_st_gen_prc_params_csr(p_gtt_id IN OKL_ST_GEN_PRC_PARAMS.gtt_id%type) IS
811: SELECT ID
830: FROM OKL_ST_GEN_TMPT_LNS
831: where GTT_ID = p_gtt_id
832: AND PRIMARY_YN = p_primary_yn;
833:
834: CURSOR okl_new_version_date_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
835: select max(chr.start_date) MAX_START_DATE
836: from okc_k_headers_b chr,
837: okl_k_headers khr,
838: okl_ae_tmpt_Sets aes,
835: select max(chr.start_date) MAX_START_DATE
836: from okc_k_headers_b chr,
837: okl_k_headers khr,
838: okl_ae_tmpt_Sets aes,
839: okl_st_gen_templates gtt,
840: okl_products pdt
841: where pdt.id = khr.pdt_id
842: and pdt.aes_id = aes.id
843: and aes.gts_id = gtt.gts_id
871: RAISE OKL_API.G_EXCEPTION_ERROR;
872: END IF;
873:
874: l_dummy := Okl_Api.G_FALSE;
875: FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
876: LOOP
877: l_gttv_rec_in.id := gttv_rec_temp.id;
878: l_gttv_rec_in.gts_id := gttv_rec_temp.gts_id;
879: l_gttv_rec_in.version := gttv_rec_temp.version;
1143: ,x_msg_count OUT NOCOPY NUMBER
1144: ,x_msg_data OUT NOCOPY VARCHAR2
1145: ,p_gtpv_tbl IN gtpv_tbl_type
1146: ) IS
1147: l_gtt_id okl_st_gen_templates.id%type ;
1148: l_api_name CONSTANT VARCHAR2(40) := 'delete_tmpt_prc_params';
1149: l_api_version CONSTANT NUMBER := 1.0;
1150: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1151:
1236: ,x_msg_count OUT NOCOPY NUMBER
1237: ,x_msg_data OUT NOCOPY VARCHAR2
1238: ,p_gtlv_tbl IN gtlv_tbl_type
1239: )IS
1240: l_gtt_id okl_st_gen_templates.id%type;
1241: l_api_name CONSTANT VARCHAR2(40) := 'delete_pri_tmpt_lns';
1242: l_api_version CONSTANT NUMBER := 1.0;
1243: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1244:
1375: ,x_msg_count OUT NOCOPY NUMBER
1376: ,x_msg_data OUT NOCOPY VARCHAR2
1377: ,p_gtlv_tbl IN gtlv_tbl_type
1378: )IS
1379: l_gtt_id okl_st_gen_templates.id%type;
1380: l_api_name CONSTANT VARCHAR2(40) := 'delete_dep_tmpt_lns';
1381: l_api_version CONSTANT NUMBER := 1.0;
1382: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1383:
1577: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1578: ,x_return_status OUT NOCOPY VARCHAR2
1579: ,x_msg_count OUT NOCOPY NUMBER
1580: ,x_msg_data OUT NOCOPY VARCHAR2
1581: ,p_gtt_id IN okl_st_gen_templates.id%type
1582: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
1583: ,p_during_upd_flag IN VARCHAR
1584: ,p_book_classification IN VARCHAR
1585: )
1592: days_in_yr_code
1593: FROM OKL_ST_GEN_TMPT_SETS
1594: WHERE ID = (
1595: SELECT gts_id
1596: FROM okl_st_gen_templates
1597: WHERE id = p_gtt_id
1598: );
1599:
1600: CURSOR get_billable_flag IS
1980: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1981: ,x_return_status OUT NOCOPY VARCHAR2
1982: ,x_msg_count OUT NOCOPY NUMBER
1983: ,x_msg_data OUT NOCOPY VARCHAR2
1984: ,p_gtt_id IN okl_st_gen_templates.id%type
1985: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
1986: ,p_during_upd_flag IN VARCHAR
1987: ,p_book_classification IN VARCHAR
1988:
1986: ,p_during_upd_flag IN VARCHAR
1987: ,p_book_classification IN VARCHAR
1988:
1989: )IS
1990: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
1991: l_api_name CONSTANT VARCHAR2(40) := 'validate_financial_template';
1992: l_api_version CONSTANT NUMBER := 1.0;
1993: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1994: l_error_msgs_tbl error_msgs_tbl_type;
2008:
2009:
2010: -- 1. If user selects one of the following Insurance purposes then all the
2011: -- insurance stream purposes must be defined.
2012: CURSOR okl_ins_purposes_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2013: SELECT count(*) ins_strms_count
2014: FROM OKL_ST_GEN_TMPT_LNS gtl
2015: , okl_strm_type_b stb
2016: WHERE gtl.primary_sty_id = stb.id
2034: -- The List contains Purposes for all Book Classifications.
2035: -- The sql statement checkes whether the purposes mentioned in the set are repeated
2036: -- or not. So, If they are not defined then that doesnot violoate the logic of the
2037: -- SQL statement.
2038: CURSOR fin_only_one_as_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2039: SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
2040: FROM OKL_ST_GEN_TMPT_LNS gtl
2041: ,okl_strm_type_b stb
2042: WHERE gtl.primary_sty_id = stb.id
2127: group by stb.stream_type_purpose
2128: having count(stb.stream_type_purpose) > 1;
2129:
2130: -- 3. All of the following primary purposes must be defined
2131: CURSOR fin_mandatory_pri_all_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2132: SELECT LOOKUP_CODE PURPOSE
2133: FROM FND_LOOKUPS
2134: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2135: AND LOOKUP_CODE IN
2191: ,'REBOOK_BILLING_ADJUSTMENT' -- VR Upgrade.
2192: )
2193: and GTL.GTT_ID = p_gtt_id;
2194:
2195: CURSOR fin_mandatory_pri_ln_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2196: SELECT LOOKUP_CODE PURPOSE
2197: FROM FND_LOOKUPS
2198: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2199: AND LOOKUP_CODE IN
2211: 'VARIABLE_INTEREST'
2212: )
2213: and GTL.GTT_ID = p_gtt_id;
2214:
2215: CURSOR fin_mandatory_pri_ls_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2216: SELECT LOOKUP_CODE PURPOSE
2217: FROM FND_LOOKUPS
2218: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2219: AND LOOKUP_CODE IN
2232: )
2233: and GTL.GTT_ID = p_gtt_id;
2234:
2235: --Bug 5139013 dpsingh start
2236: CURSOR fin_mandatory_pri_ic_float_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2237: SELECT LOOKUP_CODE PURPOSE
2238: FROM FND_LOOKUPS
2239: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2240: AND LOOKUP_CODE ='VARIABLE_INTEREST_SCHEDULE'
2249: --Bug 5139013 dpsingh end
2250:
2251: -- Modified by RGOOTY
2252: -- Bug 4111081: Start
2253: CURSOR fin_mandatory_pri_op_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2254: SELECT LOOKUP_CODE PURPOSE
2255: FROM FND_LOOKUPS
2256: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2257: AND LOOKUP_CODE IN
2269: 'INVESTOR_RENTAL_ACCRUAL'
2270: )
2271: and GTL.GTT_ID = p_gtt_id;
2272:
2273: CURSOR fin_mandatory_pri_df_n_st_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2274: SELECT LOOKUP_CODE PURPOSE
2275: FROM FND_LOOKUPS
2276: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2277: AND LOOKUP_CODE IN
2294: -- Modified by RGOOTY
2295: -- Bug 4129154: Start
2296:
2297: -- 4. Mandatory Dependent Streams
2298: CURSOR man_dep_all_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2299: SELECT LOOKUP_CODE PURPOSE
2300: FROM FND_LOOKUPS
2301: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2302: AND LOOKUP_CODE IN
2328: AND gtlpri.gtt_id = p_gtt_id
2329: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2330: );
2331:
2332: CURSOR man_dep_ln_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2333: SELECT LOOKUP_CODE PURPOSE
2334: FROM FND_LOOKUPS
2335: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2336: AND LOOKUP_CODE IN
2374: AND gtlpri.gtt_id = p_gtt_id
2375: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2376: );
2377:
2378: CURSOR man_dep_ln_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2379: SELECT LOOKUP_CODE PURPOSE
2380: FROM FND_LOOKUPS
2381: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2382: AND LOOKUP_CODE IN
2422: AND gtlpri.gtt_id = p_gtt_id
2423: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2424: );
2425:
2426: CURSOR man_dep_dfstop_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2427: SELECT LOOKUP_CODE PURPOSE
2428: FROM FND_LOOKUPS
2429: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2430: AND LOOKUP_CODE IN
2456: AND gtlpri.gtt_id = p_gtt_id
2457: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2458: );
2459:
2460: CURSOR man_dep_ln_icc_vrs_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2461: SELECT LOOKUP_CODE PURPOSE
2462: FROM FND_LOOKUPS
2463: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2464: AND LOOKUP_CODE IN
2491: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2492: );
2493:
2494: -- rgooty BUG#4290143 start
2495: CURSOR man_dep_ln_var_int_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2496: SELECT LOOKUP_CODE PURPOSE
2497: FROM FND_LOOKUPS
2498: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2499: AND LOOKUP_CODE = 'VARIABLE_INTEREST_INCOME'
2520: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2521: );
2522: -- rgooty BUG#4290143 end
2523:
2524: CURSOR man_dep_df_st_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2525: SELECT LOOKUP_CODE PURPOSE
2526: FROM FND_LOOKUPS
2527: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2528: AND LOOKUP_CODE IN
2554: AND gtlpri.gtt_id = p_gtt_id
2555: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2556: );
2557:
2558: CURSOR man_dep_ls_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2559: SELECT LOOKUP_CODE PURPOSE
2560: FROM FND_LOOKUPS
2561: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2562: AND LOOKUP_CODE IN
2592: AND gtlpri.gtt_id = p_gtt_id
2593: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2594: );
2595:
2596: CURSOR man_dep_ls_rv_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2597: SELECT LOOKUP_CODE PURPOSE
2598: FROM FND_LOOKUPS
2599: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2600: AND LOOKUP_CODE IN
2638: AND gtlpri.gtt_id = p_gtt_id
2639: AND gtlpri.primary_sty_id = gtl.primary_sty_id
2640: );
2641:
2642: CURSOR man_dep_op_rent_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2643: SELECT LOOKUP_CODE PURPOSE
2644: FROM FND_LOOKUPS
2645: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
2646: AND LOOKUP_CODE IN
2674: );
2675: -- Bug 4129154: End
2676:
2677: -- Rule 6. Certain Book classifications can have certain Purposes only.
2678: CURSOR purposes_for_df_and_st_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2679: (
2680: -- Retrieve the List of Primary Stream Purposes in the Template
2681: SELECT distinct stb.stream_type_purpose purpose
2682: FROM OKL_ST_GEN_TMPT_LNS gtl
2835: --srsreeni 6117982 ends
2836: , 'UPFRONT_TAX_BILLED' -- bug6619311
2837: );
2838:
2839: CURSOR purposes_for_op_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
2840: (
2841: -- Retrieve the List of Primary Stream Purposes in the Template
2842: SELECT distinct stb.stream_type_purpose purpose
2843: FROM OKL_ST_GEN_TMPT_LNS gtl
2996: --srsreeni 6117982 ends
2997: , 'UPFRONT_TAX_BILLED' -- bug 6619311
2998: );
2999:
3000: CURSOR purposes_for_ln_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3001: (
3002: -- Retrieve the List of Primary Stream Purposes in the Template
3003: SELECT distinct stb.stream_type_purpose purpose
3004: FROM OKL_ST_GEN_TMPT_LNS gtl
3150: , 'UPFRONT_TAX_BILLED' -- bug 6619311
3151: );
3152: -- Modified by RGOOTY
3153: -- Bug 4129154: Start
3154: CURSOR fetch_sgt_info( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type)
3155: IS
3156: SELECT gts.revenue_recog_meth_code revenue_recog_meth_code,
3157: gts.interest_calc_meth_code interest_calc_meth_code
3158: FROM OKL_ST_GEN_TMPT_SETS gts,
3155: IS
3156: SELECT gts.revenue_recog_meth_code revenue_recog_meth_code,
3157: gts.interest_calc_meth_code interest_calc_meth_code
3158: FROM OKL_ST_GEN_TMPT_SETS gts,
3159: OKL_ST_GEN_TEMPLATES gtt
3160: WHERE gtt.gts_id = gts.id
3161: AND gtt.id = p_gtt_id;
3162: l_rrm OKL_ST_GEN_TMPT_SETS.revenue_recog_meth_code%TYPE;
3163: l_icm OKL_ST_GEN_TMPT_SETS.interest_calc_meth_code%TYPE;
3677: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3678: ,x_return_status OUT NOCOPY VARCHAR2
3679: ,x_msg_count OUT NOCOPY NUMBER
3680: ,x_msg_data OUT NOCOPY VARCHAR2
3681: ,p_gtt_id IN okl_st_gen_templates.id%type
3682: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
3683: ,p_during_upd_flag IN VARCHAR
3684: )IS
3685: l_api_name CONSTANT VARCHAR2(40) := 'validate_investor_template';
3693: l_msg_data VARCHAR2(2000);
3694: l_dummy VARCHAR2(1) := okl_api.G_FALSE;
3695:
3696: -- 1. Only one stream type of the following primary purposes should be defined.
3697: CURSOR inv_only_one_as_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3698: SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
3699: FROM OKL_ST_GEN_TMPT_LNS gtl
3700: ,okl_strm_type_b stb
3701: WHERE gtl.primary_sty_id = stb.id
3722: group by stb.stream_type_purpose
3723: having count(stb.stream_type_purpose) > 1;
3724:
3725: -- 3. All of the following primary purposes must be defined
3726: CURSOR inv_mandatory_primary_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3727: SELECT LOOKUP_CODE PURPOSE
3728: FROM FND_LOOKUPS
3729: WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
3730: AND LOOKUP_CODE IN
3882: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3883: ,x_return_status OUT NOCOPY VARCHAR2
3884: ,x_msg_count OUT NOCOPY NUMBER
3885: ,x_msg_data OUT NOCOPY VARCHAR2
3886: ,p_gtt_id IN okl_st_gen_templates.id%type
3887: )IS
3888:
3889: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
3890: l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
3885: ,x_msg_data OUT NOCOPY VARCHAR2
3886: ,p_gtt_id IN okl_st_gen_templates.id%type
3887: )IS
3888:
3889: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
3890: l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
3891: l_api_version CONSTANT NUMBER := 1.0;
3892: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3893:
3897: l_dummy VARCHAR2(1) := okl_api.G_FALSE;
3898: l_gttv_rec_in gttv_rec_type;
3899: l_gttv_rec_out gttv_rec_type;
3900:
3901: CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
3902: SELECT ID
3903: ,GTS_ID
3904: ,VERSION
3905: ,START_DATE
3904: ,VERSION
3905: ,START_DATE
3906: ,END_DATE
3907: ,TMPT_STATUS
3908: FROM OKL_ST_GEN_TEMPLATES
3909: where ID = p_gtt_id;
3910: BEGIN
3911: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3912: l_return_status := OKL_API.START_ACTIVITY( l_api_name
3922: RAISE OKL_API.G_EXCEPTION_ERROR;
3923: END IF;
3924:
3925: l_dummy := Okl_Api.G_FALSE;
3926: FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
3927: LOOP
3928: l_gttv_rec_in.id := gttv_rec_temp.id;
3929: l_gttv_rec_in.gts_id := gttv_rec_temp.gts_id;
3930: l_gttv_rec_in.version := gttv_rec_temp.version;
4015: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4016: ,x_return_status OUT NOCOPY VARCHAR2
4017: ,x_msg_count OUT NOCOPY NUMBER
4018: ,x_msg_data OUT NOCOPY VARCHAR2
4019: ,p_gtt_id IN okl_st_gen_templates.id%type
4020: ,p_pri_sty_id IN okl_st_gen_tmpt_lns.primary_Sty_id%type
4021: ,x_missing_deps OUT NOCOPY VARCHAR2
4022: ,x_show_warn_flag OUT NOCOPY VARCHAR2
4023: ,p_deal_type IN VARCHAR2
4021: ,x_missing_deps OUT NOCOPY VARCHAR2
4022: ,x_show_warn_flag OUT NOCOPY VARCHAR2
4023: ,p_deal_type IN VARCHAR2
4024: )IS
4025: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4026: l_api_name CONSTANT VARCHAR2(40) := 'validate_mandatory_dep';
4027: l_api_version CONSTANT NUMBER := 1.0;
4028: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4029:
4029:
4030: l_purpose_list VARCHAR2(10000);
4031: -- Cursor to fetch the missed dependent streams for a Primary Stream type
4032: -- with purpose as 'Fee Payment'
4033: CURSOR fee_payment_man_dep_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type
4034: ,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.primary_sty_id%TYPE
4035: ,p_exclude_fr IN VARCHAR2 ) IS
4036: SELECT LOOKUP_CODE PURPOSE
4037: FROM FND_LOOKUPS
4131: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4132: ,x_return_status OUT NOCOPY VARCHAR2
4133: ,x_msg_count OUT NOCOPY NUMBER
4134: ,x_msg_data OUT NOCOPY VARCHAR2
4135: ,p_gtt_id IN okl_st_gen_templates.id%type
4136: ,x_wrn_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4137: ,p_during_upd_flag IN VARCHAR
4138: ,x_pri_purpose_list OUT NOCOPY VARCHAR
4139: )IS
4137: ,p_during_upd_flag IN VARCHAR
4138: ,x_pri_purpose_list OUT NOCOPY VARCHAR
4139: )IS
4140:
4141: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4142: l_api_name CONSTANT VARCHAR2(40) := 'validate_for_warnings';
4143: l_api_version CONSTANT NUMBER := 1.0;
4144: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4145: l_missing_deps VARCHAR2(10000);
4149: l_message VARCHAR2(2700);
4150: l_pri_purpose_list VARCHAR2(10000);
4151:
4152: -- Cursor to fetch the Primary Stream Types with purpose as 'FEE PAYMENT'
4153: CURSOR fee_payment_pri_strms_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
4154: SELECT GTL.PRIMARY_STY_ID PRI_STY_ID
4155: ,GTS.DEAL_TYPE DEAL_TYPE
4156: ,STB.ID STRM_TYPE_ID
4157: ,STB.CODE STRM_NAME
4156: ,STB.ID STRM_TYPE_ID
4157: ,STB.CODE STRM_NAME
4158: FROM OKL_ST_GEN_TMPT_LNS GTL
4159: ,OKL_ST_GEN_TMPT_SETS GTS
4160: ,OKL_ST_GEN_TEMPLATES GTT
4161: ,OKL_STRM_TYPE_B STB
4162: WHERE GTL.GTT_ID = p_gtt_id
4163: AND GTL.GTT_ID = GTT.ID
4164: AND GTS.ID = GTT.GTS_ID
4254: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4255: ,x_return_status OUT NOCOPY VARCHAR2
4256: ,x_msg_count OUT NOCOPY NUMBER
4257: ,x_msg_data OUT NOCOPY VARCHAR2
4258: ,p_gtt_id IN okl_st_gen_templates.id%type
4259: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4260: ,x_return_tmpt_status OUT NOCOPY VARCHAR2
4261: ,p_during_upd_flag IN VARCHAR2
4262: )IS
4259: ,x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type
4260: ,x_return_tmpt_status OUT NOCOPY VARCHAR2
4261: ,p_during_upd_flag IN VARCHAR2
4262: )IS
4263: l_gtt_id okl_st_gen_templates.id%type := p_gtt_id;
4264: l_api_name CONSTANT VARCHAR2(40) := 'validate_template';
4265: l_api_version CONSTANT NUMBER := 1.0;
4266: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4267: l_error_msgs_tbl error_msgs_tbl_type;
4276:
4277: l_gttv_rec_in gttv_rec_type;
4278: l_gttv_rec_out gttv_rec_type;
4279:
4280: CURSOR okl_st_gen_templates_csr(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%type) IS
4281: SELECT GTT.ID ID
4282: ,GTT.GTS_ID GTS_ID
4283: ,GTS.NAME NAME
4284: ,GTS.PRODUCT_TYPE PRODUCT_TYPE
4286: ,GTT.START_DATE START_DATE
4287: ,GTT.END_DATE END_DATE
4288: ,GTT.TMPT_STATUS TMPT_STATUS
4289: ,GTT.VERSION VERSION
4290: FROM OKL_ST_GEN_TEMPLATES GTT
4291: ,OKL_ST_GEN_TMPT_SETS GTS
4292: WHERE GTT.GTS_ID = GTS.ID
4293: AND GTT.ID = p_gtt_id;
4294: l_tmpt_set_rec okl_st_gen_templates_csr%ROWTYPE;
4290: FROM OKL_ST_GEN_TEMPLATES GTT
4291: ,OKL_ST_GEN_TMPT_SETS GTS
4292: WHERE GTT.GTS_ID = GTS.ID
4293: AND GTT.ID = p_gtt_id;
4294: l_tmpt_set_rec okl_st_gen_templates_csr%ROWTYPE;
4295: BEGIN
4296: -- Perform the Initializations
4297: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4298: l_return_status := OKL_API.START_ACTIVITY( l_api_name
4307: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4308: RAISE OKL_API.G_EXCEPTION_ERROR;
4309: END IF;
4310: l_dummy := Okl_Api.G_FALSE;
4311: FOR gttv_rec_temp IN okl_st_gen_templates_csr(l_gtt_id)
4312: LOOP
4313: l_tmpt_set_rec.id := gttv_rec_temp.id;
4314: l_tmpt_set_rec.gts_id := gttv_rec_temp.gts_id;
4315: l_tmpt_set_rec.start_date := gttv_rec_temp.start_date;
4487: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
4488: ,x_return_status OUT NOCOPY VARCHAR2
4489: ,x_msg_count OUT NOCOPY NUMBER
4490: ,x_msg_data OUT NOCOPY VARCHAR2
4491: ,p_gtt_id IN OKL_ST_GEN_TEMPLATES.ID%type
4492: ,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE
4493: ,p_gtlv_tbl IN gtlv_tbl_type
4494: ,x_missing_deps OUT NOCOPY VARCHAR2
4495: ,x_show_warn_flag OUT NOCOPY VARCHAR2
4514: upd_table_count NUMBER;
4515:
4516: --Added by bkatraga on 05-Apr-2005
4517: l_error_msgs_tbl error_msgs_tbl_type;
4518: l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4519: --end changes
4520:
4521: -- Modified by RGOOTY
4522: -- Bug 4054596: Issue No. 5: Start
4519: --end changes
4520:
4521: -- Modified by RGOOTY
4522: -- Bug 4054596: Issue No. 5: Start
4523: CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE
4524: ,p_pri_id IN OKL_ST_GEN_TMPT_LNS.primary_Sty_id%TYPE) IS
4525: SELECT STY.STREAM_TYPE_PURPOSE PURPOSE_CODE,
4526: ( SELECT MEANING FROM
4527: FND_LOOKUPS
4543: l_pri_sty_id OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE;
4544: l_purp_list VARCHAR2(250);
4545: -- Bug 4054596: Issue No. 5: End
4546:
4547: l_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4548: l_deal_type OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE;
4549:
4550: CURSOR tmpt_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE ) IS
4551: SELECT GTS.DEAL_TYPE DEAL_TYPE
4546:
4547: l_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4548: l_deal_type OKL_ST_GEN_TMPT_SETS.DEAL_TYPE%TYPE;
4549:
4550: CURSOR tmpt_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE ) IS
4551: SELECT GTS.DEAL_TYPE DEAL_TYPE
4552: ,GTT.TMPT_STATUS TMPT_STATUS
4553: ,GTS.ID GTS_ID -- Bug 4094361, Modified by RGOOTY
4554: FROM OKL_ST_GEN_TMPT_SETS GTS
4551: SELECT GTS.DEAL_TYPE DEAL_TYPE
4552: ,GTT.TMPT_STATUS TMPT_STATUS
4553: ,GTS.ID GTS_ID -- Bug 4094361, Modified by RGOOTY
4554: FROM OKL_ST_GEN_TMPT_SETS GTS
4555: ,OKL_ST_GEN_TEMPLATES GTT
4556: WHERE GTS.ID = GTT.GTS_ID
4557: AND GTT.ID = p_gtt_id;
4558:
4559: CURSOR tmpt_pri_strm_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE
4555: ,OKL_ST_GEN_TEMPLATES GTT
4556: WHERE GTS.ID = GTT.GTS_ID
4557: AND GTT.ID = p_gtt_id;
4558:
4559: CURSOR tmpt_pri_strm_det_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE
4560: ,p_pri_sty_id OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE)
4561: IS
4562: SELECT STY.STREAM_TYPE_PURPOSE PRI_STRM_PURPOSE
4563: FROM OKL_ST_GEN_TMPT_LNS GTL
4821: ,x_gttv_rec OUT NOCOPY gttv_rec_type
4822: ,x_pri_purpose_list OUT NOCOPY VARCHAR2)
4823: IS
4824: -- Cursor to fetch the Stream Generation Template Set ID
4825: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
4826: IS
4827: SELECT GTT.GTS_ID
4828: FROM OKL_ST_GEN_TEMPLATES GTT
4829: WHERE GTT.ID = p_gtt_id;
4824: -- Cursor to fetch the Stream Generation Template Set ID
4825: CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
4826: IS
4827: SELECT GTT.GTS_ID
4828: FROM OKL_ST_GEN_TEMPLATES GTT
4829: WHERE GTT.ID = p_gtt_id;
4830: -- Cursor to get the Dependent Stream Purpose Meaning which has been included more than once
4831: -- for a particular primary Stream
4832: CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE )
4828: FROM OKL_ST_GEN_TEMPLATES GTT
4829: WHERE GTT.ID = p_gtt_id;
4830: -- Cursor to get the Dependent Stream Purpose Meaning which has been included more than once
4831: -- for a particular primary Stream
4832: CURSOR okl_dep_purpose_dup_csr( p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE )
4833: IS
4834: SELECT STYP.CODE PRI_STRM_TYPE_NAME,
4835: -- gboomina Bug 4874272 - Added - Start
4836: STYP.STREAM_TYPE_PURPOSE PRI_STRM_TYPE_PURPOSE,
4875: -- Local Variables Declaration
4876: l_missing_deps VARCHAR2(10000);
4877: l_show_warn_flag VARCHAR2(30);
4878: l_error_msgs_tbl error_msgs_tbl_type;
4879: l_return_tmpt_status OKL_ST_GEN_TEMPLATES.TMPT_STATUS%TYPE;
4880: l_sgt_set_id OKL_ST_GEN_TMPT_SETS.ID%TYPE;
4881: l_pri_purpose_list VARCHAR2(10000);
4882: l_found VARCHAR2(30);
4883: l_msg VARCHAR2(10000);