[Home] [Help]
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
53: IS
54: l_dummy VARCHAR2(1);
55: x_value VARCHAR2(1) := Okl_Api.G_FALSE;
69: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
70: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
71: AND EXISTS (SELECT '1'
72: FROM okl_streams stmb,
73: okl_strm_type_b styb
74: WHERE stmb.sty_id = styb.id
75: AND stmb.khr_id = pocb.khr_id
76: AND stmb.id = pocb.stm_id
77: AND NVL(styb.stream_type_subclass,'x')
91: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) > TRUNC(p_effective_date)
92: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
93: AND EXISTS (SELECT '1'
94: FROM okl_streams stmb,
95: okl_strm_type_b styb
96: WHERE stmb.sty_id = styb.id
97: AND stmb.khr_id = pocb.khr_id
98: AND stmb.id = pocb.stm_id
99: AND NVL(styb.stream_type_subclass,'x')
114: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) < TRUNC(p_effective_date)
115: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
116: AND EXISTS (SELECT '1'
117: FROM okl_streams stmb,
118: okl_strm_type_b styb
119: WHERE stmb.sty_id = styb.id
120: AND stmb.khr_id = pocb.khr_id
121: AND stmb.id = pocb.stm_id
122: AND NVL(styb.stream_type_subclass,'x')
135: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) = TRUNC(p_effective_date)
136: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
137: AND EXISTS (SELECT '1'
138: FROM okl_streams stmb,
139: okl_strm_type_b styb
140: WHERE stmb.sty_id = styb.id
141: AND stmb.khr_id = pocb.khr_id
142: AND stmb.id = pocb.stm_id
143: AND NVL(styb.stream_type_subclass,'x')
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
220: -- cklee 08-08-2003 11.5.10
221: ,x_value OUT NOCOPY VARCHAR2
222: ,x_inv_agmt_chr_id_tbl OUT NOCOPY inv_agmt_chr_id_tbl_type
223: )
336: p_kle_id IN okc_k_lines_b.ID%TYPE
337: ,p_effective_date IN DATE
338: -- cklee 08-08-2003 11.5.10
339: ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
340: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
341: -- cklee 08-08-2003 11.5.10
342: ) RETURN VARCHAR
343: IS
344: l_dummy VARCHAR2(1);
358: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) >= TRUNC(p_effective_date)
359: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
360: AND EXISTS (SELECT '1'
361: FROM okl_streams stmb,
362: okl_strm_type_b styb
363: WHERE stmb.sty_id = styb.id
364: AND stmb.khr_id = pocb.khr_id
365: AND stmb.kle_id = pocb.kle_id
366: AND stmb.id = pocb.stm_id
381: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) > TRUNC(p_effective_date)
382: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
383: AND EXISTS (SELECT '1'
384: FROM okl_streams stmb,
385: okl_strm_type_b styb
386: WHERE stmb.sty_id = styb.id
387: AND stmb.khr_id = pocb.khr_id
388: AND stmb.kle_id = pocb.kle_id
389: AND stmb.id = pocb.stm_id
404: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) < TRUNC(p_effective_date)
405: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
406: AND EXISTS (SELECT '1'
407: FROM okl_streams stmb,
408: okl_strm_type_b styb
409: WHERE stmb.sty_id = styb.id
410: AND stmb.khr_id = pocb.khr_id
411: AND stmb.kle_id = pocb.kle_id
412: AND stmb.id = pocb.stm_id
427: AND TRUNC(NVL(pocb.streams_to_date,G_FINAL_DATE)) = TRUNC(p_effective_date)
428: AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
429: AND EXISTS (SELECT '1'
430: FROM okl_streams stmb,
431: okl_strm_type_b styb
432: WHERE stmb.sty_id = styb.id
433: AND stmb.khr_id = pocb.khr_id
434: AND stmb.kle_id = pocb.kle_id
435: AND stmb.id = pocb.stm_id
508: ,p_kle_id IN okc_k_lines_b.ID%TYPE
509: ,p_effective_date IN DATE
510: -- cklee 08-08-2003 11.5.10
511: ,p_effective_date_operator IN VARCHAR2 DEFAULT G_GREATER_THAN_EQUAL_TO
512: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
513: -- cklee 08-08-2003 11.5.10
514: ,x_value OUT NOCOPY VARCHAR2
515: ,x_inv_agmt_chr_id_tbl OUT NOCOPY inv_agmt_chr_id_tbl_type
516: )
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
648: ,x_inv_agmt_chr_id OUT NOCOPY NUMBER
649: )
650: 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
662: AND pocb.khr_id = p_khr_id
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
677: AND pocb.khr_id = p_khr_id
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
692: AND pocb.khr_id = p_khr_id
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
707: AND pocb.khr_id = p_khr_id
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
1838: ,x_msg_data OUT NOCOPY VARCHAR2)
1839: IS
1841: CURSOR l_okl_poc_csr
1842: IS
1843: SELECT pocb.id
1844: FROM okl_pool_contents pocb
1845: ,okl_strm_type_b styb
1846: WHERE pocb.khr_id = p_khr_id
1847: AND pocb.pol_id = p_pol_id
1848: AND pocb.sty_id = styb.id
1849: AND styb.stream_type_subclass = p_stream_type_subclass;
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
2055: ,x_msg_count OUT NOCOPY NUMBER
2056: ,x_msg_data OUT NOCOPY VARCHAR2
2061: CURSOR l_okl_pol_csr
2062: IS
2063: SELECT DISTINCT pocb.pol_id
2064: FROM okl_pool_contents pocb
2065: ,okl_strm_type_b styb
2066: WHERE pocb.khr_id = p_khr_id
2067: AND pocb.pol_id = p_pol_id
2068: AND pocb.sty_id = styb.id
2069: AND styb.stream_type_subclass = p_stream_type_subclass;
2092: ,pocb.sty_id sty_id
2093: ,pocb.sty_code
2094: ,pocb.streams_from_date
2095: FROM okl_pool_contents pocb
2096: ,okl_strm_type_b styb
2097: ,okl_pools polb
2098: WHERE pocb.khr_id = p_khr_id
2099: AND pocb.pol_id = p_pol_id
2100: AND pocb.pol_id = polb.id
2265: ,pocb.streams_from_date
2266: ,pocb.streams_to_date
2267: ,styb.stream_type_subclass
2268: FROM okl_pool_contents pocb
2269: ,okl_strm_type_b styb
2270: WHERE pocb.pol_id = p_pol_id
2271: AND pocb.khr_id = p_khr_id
2272: AND pocb.kle_id = NVL(p_kle_id,pocb.kle_id)
2273: AND pocb.sty_id = styb.id
2298: -- Bug# 7590979 - Start
2299: -- Cursor gets the buy back date of a contract from a pool
2300: CURSOR c_chk_buy_back_date (cp_pol_id OKL_POOL_CONTENTS.ID%TYPE
2301: , cp_khr_id OKC_K_HEADERS_B.ID%TYPE
2302: , cp_stream_type_subclass OKL_STRM_TYPE_B.STREAM_TYPE_SUBCLASS%TYPE) IS
2303: SELECT POX.TRANSACTION_DATE
2304: FROM OKL_POOL_CONTENTS POC
2305: , OKL_POOL_TRANSACTIONS POX
2306: , OKL_STRM_TYPE_B STY
2302: , cp_stream_type_subclass OKL_STRM_TYPE_B.STREAM_TYPE_SUBCLASS%TYPE) IS
2303: SELECT POX.TRANSACTION_DATE
2304: FROM OKL_POOL_CONTENTS POC
2305: , OKL_POOL_TRANSACTIONS POX
2306: , OKL_STRM_TYPE_B STY
2307: WHERE POC.KHR_ID = cp_khr_id
2308: AND POC.POL_ID = cp_pol_id
2309: AND STY.STREAM_TYPE_SUBCLASS = NVL(cp_stream_type_subclass,STY.STREAM_TYPE_SUBCLASS)
2310: AND POC.STY_ID = STY.ID
2596: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2597: ,p_transaction_reason IN VARCHAR2
2598: ,p_khr_id IN okc_k_headers_b.ID%TYPE
2599: ,p_kle_id IN OKC_K_LINES_B.ID%TYPE DEFAULT NULL
2600: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE DEFAULT NULL
2601: ,p_transaction_date IN DATE
2602: ,p_effective_date IN DATE
2603: ,x_return_status OUT NOCOPY VARCHAR2
2604: ,x_msg_count OUT NOCOPY NUMBER
2610: CURSOR l_okl_pol_csr
2611: IS
2612: SELECT DISTINCT pol_id
2613: FROM okl_pool_contents pocb
2614: ,okl_strm_type_b styb
2615: WHERE pocb.khr_id = p_khr_id
2616: AND pocb.kle_id = NVL(p_kle_id,pocb.kle_id)
2617: AND pocb.sty_id = styb.id
2618: AND styb.stream_type_subclass = NVL(p_stream_type_subclass,styb.stream_type_subclass)
3333: p_api_version IN NUMBER
3334: ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
3335: ,p_khr_id IN okc_k_headers_b.ID%TYPE
3336: ,p_pol_id IN okl_pools.ID%TYPE
3337: ,p_stream_type_subclass IN okl_strm_type_b.stream_type_subclass%TYPE
3338: ,p_effective_date IN DATE
3339: ,x_return_status OUT NOCOPY VARCHAR2
3340: ,x_msg_count OUT NOCOPY NUMBER
3341: ,x_msg_data OUT NOCOPY VARCHAR2)
3345: CURSOR l_okl_pol_csr
3346: IS
3347: SELECT DISTINCT pocb.pol_id
3348: FROM okl_pool_contents pocb
3349: ,okl_strm_type_b styb
3350: WHERE pocb.khr_id = p_khr_id
3351: AND pocb.pol_id = p_pol_id
3352: AND pocb.sty_id = styb.id
3353: AND styb.stream_type_subclass = p_stream_type_subclass;
3371: -- cursor to get stream type purposes for a stream_type_subclass
3372: CURSOR get_sty_id_csr (p_sty_sub_classs VARCHAR2)
3373: IS
3374: SELECT id, stream_type_purpose
3375: FROM OKL_STRM_TYPE_B
3376: WHERE stream_type_subclass = p_sty_sub_classs;
3377:
3378: lp_poxv_rec poxv_rec_type;
3379: lx_poxv_rec poxv_rec_type;