[Home] [Help]
249: SELECT sel.stream_element_date bill_date
250: ,sel.amount principal
251: ,0 interest
252: ,decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
253: FROM okl_strm_elements sel,
254: okl_streams stm,
255: okl_strm_type_b sty,
256: okc_k_lines_b cle
257: WHERE stm.khr_id = cp_khr_id
268: SELECT sel.stream_element_date bill_date
269: ,0 principal
270: ,sel.amount interest
271: ,decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
272: FROM okl_strm_elements sel,
273: okl_streams stm,
274: okl_strm_type_b sty,
275: okc_k_lines_b cle
276: WHERE stm.khr_id = cp_khr_id
297: 0 principal,
298: 0 interest,
299: sel.amount princ_pay_down,
300: decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
301: FROM okl_strm_elements sel,
302: okl_streams stm,
303: okl_strm_type_b sty,
304: okc_k_lines_b cle
305: WHERE stm.khr_id = cp_khr_id
516: (
517: SELECT sel.stream_element_date bill_date
518: ,sel.amount principal
519: ,0 interest
520: FROM okl_strm_elements sel,
521: okl_streams stm,
522: okl_strm_type_b sty,
523: okc_k_lines_b cle
524: WHERE stm.khr_id = cp_khr_id
534: UNION ALL
535: SELECT sel.stream_element_date bill_date
536: ,0 principal
537: ,sel.amount interest
538: FROM okl_strm_elements sel,
539: okl_streams stm,
540: okl_strm_type_b sty,
541: okc_k_lines_b cle
542: WHERE stm.khr_id = cp_khr_id
557: --This cursor selects the past principal paydown streams for a contract.
558: CURSOR c_amort_sch_ppd_csr(cp_khr_id IN NUMBER) IS
559: SELECT sel.stream_element_date bill_date
560: ,sum(sel.amount) ppd_amount
561: FROM okl_strm_elements sel,
562: okl_streams stm,
563: okl_strm_type_b sty,
564: okc_k_lines_b cle
565: WHERE stm.khr_id = cp_khr_id
799: SELECT sel_dii.stream_element_date receipt_date,
800: 0 principal,
801: sel_dii.amount interest,
802: sel_dii.request_id
803: FROM okl_strm_elements sel_dii,
804: okl_streams stm_dii,
805: okl_strm_type_b sty_dii
806: WHERE stm_dii.khr_id = p_chr_id
807: AND sty_dii.id = stm_dii.sty_id
813: SELECT sel_dip.stream_element_date receipt_date,
814: sel_dip.amount principal,
815: 0 interest,
816: sel_dip.request_id
817: FROM okl_strm_elements sel_dip,
818: okl_streams stm_dip,
819: okl_strm_type_b sty_dip
820: WHERE stm_dip.khr_id = p_chr_id
821: AND sty_dip.id = stm_dip.sty_id
1021: (
1022: SELECT sel_dii.stream_element_date receipt_date,
1023: 0 principal,
1024: sel_dii.amount interest
1025: FROM okl_strm_elements sel_dii,
1026: okl_streams stm_dii,
1027: okl_strm_type_b sty_dii
1028: WHERE stm_dii.khr_id = p_chr_id
1029: AND sty_dii.id = stm_dii.sty_id
1034: UNION ALL
1035: SELECT sel_dip.stream_element_date receipt_date,
1036: sel_dip.amount principal,
1037: 0 interest
1038: FROM okl_strm_elements sel_dip,
1039: okl_streams stm_dip,
1040: okl_strm_type_b sty_dip
1041: WHERE stm_dip.khr_id = p_chr_id
1042: AND sty_dip.id = stm_dip.sty_id
1364: --Bug# 14683157: Fetch Last Billed Date only for Asset Principal Payment
1365: CURSOR last_bill_date_csr(p_chr_id IN NUMBER) IS
1366: SELECT MAX(sel.stream_element_date)
1367: FROM okl_streams stm,
1368: okl_strm_elements sel,
1369: okl_strm_type_b sty,
1370: okc_k_lines_b cle
1371: WHERE stm.khr_id = p_chr_id
1372: AND sty.id = stm.sty_id
1378: AND cle.id = stm.kle_id
1379: AND cle.lse_id = 33
1380: AND cle.sts_code <> 'ABANDONED';
1381:
1382: l_last_bill_date OKL_STRM_ELEMENTS.stream_element_date%TYPE;
1383:
1384: CURSOR chr_csr(p_chr_id IN NUMBER) IS
1385: SELECT chr.start_date
1386: FROM okc_k_headers_b chr
1399: SELECT sel.stream_element_date bill_date,
1400: 0 principal,
1401: sel.amount interest,
1402: G_BILLED payment_type
1403: FROM okl_strm_elements sel,
1404: okl_streams stm,
1405: okl_strm_type_b sty,
1406: okc_k_lines_b cle
1407: WHERE stm.khr_id = p_chr_id
1418: SELECT sel.stream_element_date bill_date,
1419: sel.amount principal,
1420: 0 interest,
1421: decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1422: FROM okl_strm_elements sel,
1423: okl_streams stm,
1424: okl_strm_type_b sty,
1425: okc_k_lines_b cle
1426: WHERE stm.khr_id = p_chr_id
1436: SELECT sel.stream_element_date bill_date,
1437: 0 principal,
1438: sel.amount interest,
1439: G_PROJECTED payment_type
1440: FROM okl_strm_elements sel,
1441: okl_streams stm,
1442: okl_strm_type_b sty,
1443: okc_k_lines_b cle
1444: WHERE stm.khr_id = p_chr_id
1466: 0 principal,
1467: 0 interest,
1468: sel.amount princ_pay_down,
1469: decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1470: FROM okl_strm_elements sel,
1471: okl_streams stm,
1472: okl_strm_type_b sty,
1473: okc_k_lines_b cle
1474: WHERE stm.khr_id = p_chr_id
1644: --Bug# 14683157: Fetch Last Billed Date only for Asset Principal Payment
1645: CURSOR last_bill_date_csr(p_chr_id IN NUMBER) IS
1646: SELECT MAX(sel.stream_element_date)
1647: FROM okl_streams stm,
1648: okl_strm_elements sel,
1649: okl_strm_type_b sty,
1650: okc_k_lines_b cle
1651: WHERE stm.khr_id = p_chr_id
1652: AND sty.id = stm.sty_id
1658: AND cle.id = stm.kle_id
1659: AND cle.lse_id = 33
1660: AND cle.sts_code <> 'ABANDONED';
1661:
1662: l_last_bill_date OKL_STRM_ELEMENTS.stream_element_date%TYPE;
1663:
1664: CURSOR chr_csr(p_chr_id IN NUMBER) IS
1665: SELECT chr.start_date
1666: FROM okc_k_headers_b chr
1676: (
1677: SELECT sel.stream_element_date bill_date,
1678: 0 principal,
1679: sel.amount interest
1680: FROM okl_strm_elements sel,
1681: okl_streams stm,
1682: okl_strm_type_b sty,
1683: okc_k_lines_b cle
1684: WHERE stm.khr_id = p_chr_id
1694: UNION ALL
1695: SELECT sel.stream_element_date bill_date,
1696: 0 principal,
1697: sel.amount interest
1698: FROM okl_strm_elements sel,
1699: okl_streams stm,
1700: okl_strm_type_b sty,
1701: okc_k_lines_b cle
1702: WHERE stm.khr_id = p_chr_id
1713: UNION ALL
1714: SELECT sel.stream_element_date bill_date,
1715: sel.amount principal,
1716: 0 interest
1717: FROM okl_strm_elements sel,
1718: okl_streams stm,
1719: okl_strm_type_b sty,
1720: okc_k_lines_b cle
1721: WHERE stm.khr_id = p_chr_id
1977: SELECT sel.stream_element_date bill_date,
1978: 0 principal,
1979: sel.amount interest,
1980: decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1981: FROM okl_strm_elements sel,
1982: okl_streams stm,
1983: okl_strm_type_b sty,
1984: okc_k_lines_b cle
1985: WHERE stm.khr_id = p_chr_id
1995: SELECT sel.stream_element_date bill_date,
1996: sel.amount principal,
1997: 0 interest,
1998: decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
1999: FROM okl_strm_elements sel,
2000: okl_streams stm,
2001: okl_strm_type_b sty,
2002: okc_k_lines_b cle
2003: WHERE stm.khr_id = p_chr_id
2016: SUM(sel.amount) principal,
2017: 0 interest,
2018: 0 princ_pay_down,
2019: G_BILLED payment_type
2020: FROM okl_strm_elements sel,
2021: okl_streams stm,
2022: okl_strm_type_b sty,
2023: okc_k_lines_b cle
2024: WHERE stm.khr_id = p_chr_id
2037: 0 principal,
2038: SUM(sel.amount) interest,
2039: 0 princ_pay_down,
2040: G_BILLED payment_type
2041: FROM okl_strm_elements sel,
2042: okl_streams stm,
2043: okl_strm_type_b sty,
2044: okc_k_lines_b cle
2045: WHERE stm.khr_id = p_chr_id
2065: 0 principal,
2066: 0 interest,
2067: sel.amount princ_pay_down,
2068: decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
2069: FROM okl_strm_elements sel,
2070: okl_streams stm,
2071: okl_strm_type_b sty,
2072: okc_k_lines_b cle
2073: WHERE stm.khr_id = p_chr_id
2272: SELECT sel.stream_element_date bill_date,
2273: 0 principal,
2274: sel.amount interest,
2275: DECODE(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
2276: FROM okl_strm_elements sel,
2277: okl_streams stm,
2278: okl_strm_type_b sty,
2279: okc_k_lines_b cle
2280: WHERE stm.khr_id = p_chr_id
2290: SELECT sel.stream_element_date bill_date,
2291: sel.amount principal,
2292: 0 interest,
2293: DECODE(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
2294: FROM okl_strm_elements sel,
2295: okl_streams stm,
2296: okl_strm_type_b sty,
2297: okc_k_lines_b cle
2298: WHERE stm.khr_id = p_chr_id