[Home] [Help]
44: -- Version : 1.0
45: -- End of comments
46: ----------------------------------------------------------------------------------
47: FUNCTION is_khr_securitized(
48: p_khr_id IN okc_k_headers_b.ID%TYPE
49: ,p_effective_date IN DATE
50: ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
51: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
52: ) RETURN VARCHAR
56: l_row_found BOOLEAN := FALSE;
57: v_sql VARCHAR2(4000);
58:
59: -- case 1
60: CURSOR c_khr_def(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
61: SELECT '1'
62: FROM okl_pool_contents pocb,
63: okl_pools polb,
64: okc_k_headers_b chrb
60: CURSOR c_khr_def(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
61: SELECT '1'
62: FROM okl_pool_contents pocb,
63: okl_pools polb,
64: okc_k_headers_b chrb
65: WHERE pocb.pol_id = polb.id
66: AND polb.khr_id = chrb.id -- inv agreement
67: AND chrb.sts_code = G_STS_CODE_ACTIVE
68: AND pocb.khr_id = p_khr_id
78: = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
79: );
80:
81: -- case 2
82: CURSOR c_khr_gr(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
83: SELECT '1'
84: FROM okl_pool_contents pocb,
85: okl_pools polb,
86: okc_k_headers_b chrb
82: CURSOR c_khr_gr(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
83: SELECT '1'
84: FROM okl_pool_contents pocb,
85: okl_pools polb,
86: okc_k_headers_b chrb
87: WHERE pocb.pol_id = polb.id
88: AND polb.khr_id = chrb.id -- inv agreement
89: AND chrb.sts_code = G_STS_CODE_ACTIVE
90: AND pocb.khr_id = p_khr_id
101: );
102:
103:
104: -- case 3
105: CURSOR c_khr_ls(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
106: SELECT '1'
107: FROM okl_pool_contents pocb,
108: okl_pools polb,
109: okc_k_headers_b chrb
105: CURSOR c_khr_ls(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
106: SELECT '1'
107: FROM okl_pool_contents pocb,
108: okl_pools polb,
109: okc_k_headers_b chrb
110: WHERE pocb.pol_id = polb.id
111: AND polb.khr_id = chrb.id -- inv agreement
112: AND chrb.sts_code = G_STS_CODE_ACTIVE
113: AND pocb.khr_id = p_khr_id
122: AND NVL(styb.stream_type_subclass,'x')
123: = NVL(p_stream_type_subclass, NVL(styb.stream_type_subclass,'x'))
124: );
125: -- case 4
126: CURSOR c_khr_eq(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
127: SELECT '1'
128: FROM okl_pool_contents pocb,
129: okl_pools polb,
130: okc_k_headers_b chrb
126: CURSOR c_khr_eq(p_khr_id okc_k_headers_b.ID%TYPE, p_effective_date DATE) IS
127: SELECT '1'
128: FROM okl_pool_contents pocb,
129: okl_pools polb,
130: okc_k_headers_b chrb
131: WHERE pocb.pol_id = polb.id
132: AND polb.khr_id = chrb.id -- inv agreement
133: AND chrb.sts_code = G_STS_CODE_ACTIVE
134: AND pocb.khr_id = p_khr_id
211: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
212: ,x_return_status OUT NOCOPY VARCHAR2
213: ,x_msg_count OUT NOCOPY NUMBER
214: ,x_msg_data OUT NOCOPY VARCHAR2
215: ,p_khr_id IN okc_k_headers_b.ID%TYPE
216: ,p_effective_date IN DATE
217: -- cklee 08-08-2003 11.5.10
218: ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
219: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
226: l_api_version CONSTANT NUMBER := 1.0;
227: i NUMBER;
228: l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
229:
230: CURSOR c_inv_khr(p_khr_id okc_k_headers_b.id%TYPE) IS
231: SELECT ph.khr_id -- inv agreemnet id
232: FROM okl_pools ph
233: WHERE EXISTS (SELECT '1'
234: FROM okl_pool_contents pl
349: CURSOR c_kle_def(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
350: SELECT '1'
351: FROM okl_pool_contents pocb,
352: okl_pools polb,
353: okc_k_headers_b chrb
354: WHERE pocb.pol_id = polb.id
355: AND polb.khr_id = chrb.id -- inv agreement
356: AND chrb.sts_code = G_STS_CODE_ACTIVE
357: AND pocb.kle_id = p_kle_id
372: CURSOR c_kle_gr(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
373: SELECT '1'
374: FROM okl_pool_contents pocb,
375: okl_pools polb,
376: okc_k_headers_b chrb
377: WHERE pocb.pol_id = polb.id
378: AND polb.khr_id = chrb.id -- inv agreement
379: AND chrb.sts_code = G_STS_CODE_ACTIVE
380: AND pocb.kle_id = p_kle_id
395: CURSOR c_kle_ls(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
396: SELECT '1'
397: FROM okl_pool_contents pocb,
398: okl_pools polb,
399: okc_k_headers_b chrb
400: WHERE pocb.pol_id = polb.id
401: AND polb.khr_id = chrb.id -- inv agreement
402: AND chrb.sts_code = G_STS_CODE_ACTIVE
403: AND pocb.kle_id = p_kle_id
418: CURSOR c_kle_eq(p_kle_id okc_k_lines_b.ID%TYPE, p_effective_date DATE) IS
419: SELECT '1'
420: FROM okl_pool_contents pocb,
421: okl_pools polb,
422: okc_k_headers_b chrb
423: WHERE pocb.pol_id = polb.id
424: AND polb.khr_id = chrb.id -- inv agreement
425: AND chrb.sts_code = G_STS_CODE_ACTIVE
426: AND pocb.kle_id = p_kle_id
639: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
640: ,x_return_status OUT NOCOPY VARCHAR2
641: ,x_msg_count OUT NOCOPY NUMBER
642: ,x_msg_data OUT NOCOPY VARCHAR2
643: ,p_khr_id IN okc_k_headers_b.ID%TYPE
644: ,p_effective_date IN DATE
645: ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
646: ,p_sty_id IN okl_strm_type_b.id%TYPE
647: ,x_value OUT NOCOPY VARCHAR2
653: CURSOR l_okl_sty_grt_csr IS
654: SELECT DISTINCT polb.khr_id
655: FROM okl_pool_contents pocb,
656: okl_pools polb,
657: okc_k_headers_b chrb,
658: okl_strm_type_b styb
659: WHERE pocb.pol_id = polb.id
660: AND polb.khr_id = chrb.id -- inv agreement
661: AND chrb.sts_code = G_STS_CODE_ACTIVE
668: CURSOR l_okl_sty_les_csr IS
669: SELECT DISTINCT polb.khr_id
670: FROM okl_pool_contents pocb,
671: okl_pools polb,
672: okc_k_headers_b chrb,
673: okl_strm_type_b styb
674: WHERE pocb.pol_id = polb.id
675: AND polb.khr_id = chrb.id -- inv agreement
676: AND chrb.sts_code = G_STS_CODE_ACTIVE
683: CURSOR l_okl_sty_eql_csr IS
684: SELECT DISTINCT polb.khr_id
685: FROM okl_pool_contents pocb,
686: okl_pools polb,
687: okc_k_headers_b chrb,
688: okl_strm_type_b styb
689: WHERE pocb.pol_id = polb.id
690: AND polb.khr_id = chrb.id -- inv agreement
691: AND chrb.sts_code = G_STS_CODE_ACTIVE
698: CURSOR l_okl_sty_geq_csr IS
699: SELECT DISTINCT polb.khr_id
700: FROM okl_pool_contents pocb,
701: okl_pools polb,
702: okc_k_headers_b chrb,
703: okl_strm_type_b styb
704: WHERE pocb.pol_id = polb.id
705: AND polb.khr_id = chrb.id -- inv agreement
706: AND chrb.sts_code = G_STS_CODE_ACTIVE
818: SELECT '1'
819: FROM okl_streams strm
820: ,okl_pool_contents pl
821: ,okl_pools ph
822: ,okc_k_headers_b khr
823: WHERE -- mvasudev, stm_id changes
824: /*
825: strm.KHR_ID = pl.KHR_ID
826: AND strm.KLE_ID = pl.KLE_ID
984: FROM okl_streams strm
985: ,okl_strm_elements ele
986: ,okl_pool_contents pl
987: ,okl_pools ph
988: ,okc_k_headers_b khr
989: WHERE strm.id = ele.stm_id
990: -- mvasudev, stm_id changes
991: /*
992: AND strm.KHR_ID = pl.KHR_ID
1143: ,x_return_status OUT NOCOPY VARCHAR2
1144: ,x_msg_count OUT NOCOPY NUMBER
1145: ,x_msg_data OUT NOCOPY VARCHAR2
1146: ,p_khr_id IN NUMBER
1147: ,p_scs_code IN okc_k_headers_b.scs_code%TYPE DEFAULT NULL
1148: ,p_trx_date IN DATE
1149: ,x_fact_synd_code OUT NOCOPY fnd_lookups.lookup_code%TYPE
1150: ,x_inv_acct_code OUT NOCOPY okc_rules_b.RULE_INFORMATION1%TYPE
1151: )
1202: -- cursor to get all pools to which contract is attached
1203: CURSOR csr_get_all_assoc_ia(p_chr_id IN NUMBER) IS
1204: SELECT ia.ID ia_id, ia.START_DATE ia_start_date,op.ID pool_id,opc.STY_CODE strm_type,
1205: opc.streams_from_date strm_from_date, opc.streams_to_date strm_to_date
1206: FROM okc_k_headers_b ia,okl_pools op, okl_pool_contents opc
1207: WHERE ia.ID = op.khr_id
1208: AND op.id = opc.pol_id
1209: AND opc.khr_id = p_chr_id
1210: AND opc.STATUS_CODE ='ACTIVE'
1214: --end:| 05-29-08 cklee -- fixed bug: 6932520(R12)/OKL.H: bug#6869289 |
1215:
1216: CURSOR scs_code (p_chr_id IN NUMBER) IS
1217: SELECT scs_code
1218: FROM okc_k_headers_b
1219: WHERE id = p_chr_id;
1220: l_scs_code okc_k_headers_b.scs_code%TYPE;
1221:
1222: BEGIN
1216: CURSOR scs_code (p_chr_id IN NUMBER) IS
1217: SELECT scs_code
1218: FROM okc_k_headers_b
1219: WHERE id = p_chr_id;
1220: l_scs_code okc_k_headers_b.scs_code%TYPE;
1221:
1222: BEGIN
1223: -- Set API savepoint
1224: SAVEPOINT check_khr_ia_associated_pvt;
1398: ,p_init_msg_list IN VARCHAR2
1399: ,x_return_status OUT NOCOPY VARCHAR2
1400: ,x_msg_count OUT NOCOPY NUMBER
1401: ,x_msg_data OUT NOCOPY VARCHAR2
1402: ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE
1403: ,p_effective_date IN DATE
1404: )
1405: IS
1406: BEGIN
1426: ,p_init_msg_list IN VARCHAR2
1427: ,x_return_status OUT NOCOPY VARCHAR2
1428: ,x_msg_count OUT NOCOPY NUMBER
1429: ,x_msg_data OUT NOCOPY VARCHAR2
1430: ,p_khr_id IN okc_k_headers_b.ID%TYPE
1431: ,p_effective_date IN DATE
1432: ,p_rgd_code IN VARCHAR2
1433: ,p_rdf_code IN VARCHAR2
1434: ,x_process_code OUT NOCOPY VARCHAR2
1444: l_action NUMBER;
1445:
1446: CURSOR c_action(p_rgd_code okc_rule_groups_b.rgd_code%TYPE,
1447: p_rdf_code okc_rules_b.RULE_INFORMATION_CATEGORY%TYPE,
1448: p_khr_id okc_k_headers_b.id%TYPE) IS
1449: SELECT MIN(DECODE(rg.RULE_INFORMATION1, G_PROCESS_AUTO_BACK_BACK, G_PRIORITY_2,
1450: G_PROCESS_NOT_ALLOWED, G_PRIORITY_1,
1451: G_PRIORITY_2))
1452: FROM okc_rule_groups_b rgd,
1467:
1468:
1469: CURSOR c_inv_khr(p_rgd_code okc_rule_groups_b.rgd_code%TYPE,
1470: p_rdf_code okc_rules_b.RULE_INFORMATION_CATEGORY%TYPE,
1471: p_khr_id okc_k_headers_b.id%TYPE) IS
1472: SELECT rgd.dnz_chr_id,
1473: rg.RULE_INFORMATION1
1474: FROM okc_rule_groups_b rgd,
1475: okc_rules_b rg
1829: /*
1830: PROCEDURE buyback_streams(
1831: p_api_version IN NUMBER
1832: ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1833: ,p_khr_id IN okc_k_headers_b.ID%TYPE
1834: ,p_pol_id IN okl_pools.ID%TYPE
1835: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
1836: ,x_return_status OUT NOCOPY VARCHAR2
1837: ,x_msg_count OUT NOCOPY NUMBER
2046: */
2047: PROCEDURE calculate_buyback_amount(
2048: p_api_version IN NUMBER
2049: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2050: ,p_khr_id IN okc_k_headers_b.ID%TYPE
2051: ,p_pol_id IN okl_pools.ID%TYPE
2052: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
2053: ,x_buyback_amount OUT NOCOPY NUMBER
2054: ,x_return_status OUT NOCOPY VARCHAR2
2290: CURSOR l_okl_khr_csr
2291: IS
2292: SELECT end_date,
2293: sts_code --Bug 6594724
2294: FROM okc_k_headers_b
2295: WHERE id = p_khr_id;
2296:
2297:
2298: l_api_name CONSTANT VARCHAR2(30) := 'adjust_pool_contents';
2539: PROCEDURE modify_pool_contents(
2540: p_api_version IN NUMBER
2541: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2542: ,p_transaction_reason IN VARCHAR2
2543: ,p_khr_id IN okc_k_headers_b.ID%TYPE
2544: ,p_kle_id IN OKC_K_LINES_B.ID%TYPE DEFAULT NULL
2545: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
2546: ,p_transaction_date IN DATE
2547: ,p_effective_date IN DATE
2568: -- Cursor to get the Lease Contract End Date
2569: CURSOR l_okl_khr_csr
2570: IS
2571: SELECT end_date
2572: FROM okc_k_headers_b
2573: WHERE id = p_khr_id;
2574:
2575: -- begin ankushar 29-11-2006 Legal Entity Changes
2576: -- Cursor to fecth LE associated to the pool
2866: PROCEDURE modify_pool_contents(
2867: p_api_version IN NUMBER
2868: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2869: ,p_transaction_reason IN VARCHAR2
2870: ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE
2871: ,p_kle_id IN OKC_K_LINES_B.ID%TYPE
2872: ,p_split_kle_ids IN cle_tbl_type
2873: ,p_transaction_date IN DATE
2874: ,p_effective_date IN DATE
3276:
3277: PROCEDURE buyback_pool_contents(
3278: p_api_version IN NUMBER
3279: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3280: ,p_khr_id IN okc_k_headers_b.ID%TYPE
3281: ,p_pol_id IN okl_pools.ID%TYPE
3282: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
3283: ,p_effective_date IN DATE
3284: ,x_return_status OUT NOCOPY VARCHAR2