[Home] [Help]
550: CURSOR security_deposit IS
551: SELECT ste.amount amount
552: FROM okc_k_lines_b kle,
553: okc_line_styles_b lse,
554: okc_k_items ite,
555: okl_strm_type_b sty1,
556: okl_streams stm,
557: okl_strm_type_b sty2,
558: okl_strm_elements ste
752: SELECT 'Y' FROM okc_k_headers_b CHR
753: WHERE id = p_contract_id
754: AND EXISTS
755: (
756: SELECT 'x' FROM okc_k_items cim
757: WHERE cim.object1_id1 = TO_CHAR(CHR.id)
758: AND EXISTS
759: (
760: SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
874: SELECT 'Y' FROM okc_k_headers_b CHR
875: WHERE id = p_contract_id
876: AND EXISTS
877: (
878: SELECT 'x' FROM okc_k_items cim
879: WHERE cim.object1_id1 = TO_CHAR(CHR.id)
880: AND EXISTS
881: (
882: SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
1636: okc_k_lines_b top_cle,
1637: okc_line_styles_b top_stl,
1638: okc_k_lines_b sub_cle,
1639: okc_line_styles_b sub_stl,
1640: okc_k_items cim
1641: WHERE top_cle.lse_id = top_stl.id
1642: AND top_stl.lty_code in ('SOLD_SERVICE','FEE')
1643: AND top_cle.id = sub_cle.cle_id
1644: AND sub_cle.lse_id = sub_stl.id
1734: CURSOR l_asset_id_csr(cp_kle_id IN NUMBER) IS
1735: SELECT cim1.object1_id1
1736: FROM okc_k_lines_b cle,
1737: okc_k_lines_b cle1, /* to get all line types associated with free form 1 in that FIXED_ASSET */
1738: okc_k_items cim, /* to get the one having covered asset */
1739: okc_k_lines_b cle2, /* After getting the one having the covered asset take the id of the service line */
1740: okc_line_styles_b lse, /* to get free_form 1 */
1741: okc_line_styles_b lse1, /* to get FIXED_ASSET lty_code */
1742: okc_line_styles_b lse2, /* to get LINK_SERV_ASSET lty_code */
1739: okc_k_lines_b cle2, /* After getting the one having the covered asset take the id of the service line */
1740: okc_line_styles_b lse, /* to get free_form 1 */
1741: okc_line_styles_b lse1, /* to get FIXED_ASSET lty_code */
1742: okc_line_styles_b lse2, /* to get LINK_SERV_ASSET lty_code */
1743: okc_k_items cim1 /* to take the actual asset id */
1744: WHERE lse.id = cle.lse_id
1745: AND lse1.id = cle1.lse_id
1746: AND lse1.lty_code = 'FIXED_ASSET'
1747: AND cle.id = cle1.cle_id
1765:
1766: Note: The input for the above query would be the id of the service line.
1767: */
1768:
1769: l_asset_id okc_k_items.object1_id1%TYPE;
1770:
1771: BEGIN
1772:
1773: OPEN l_asset_id_csr(p_kle_id);