[Home] [Help]
195: ------------------------------------------------------------------
196: CURSOR operation_lines(p_target_id IN NUMBER) IS
197: SELECT opl.id, oie_id, object_chr_id, object_cle_id, parent_ole_id, select_yn
198: FROM okc_operation_lines OPL,
199: okc_k_lines_b cle
200: WHERE OPL.subject_chr_id = p_target_id
201: AND EXISTS (SELECT 'X'
202: FROM okc_operation_instances OPI
203: WHERE OPI.id = OPL.oie_id
213: and NVL(line_renewal_type_code,'FUL') not in ('KEP','DNR') -- bug 5078797
214: and date_terminated is null
215: and cle_id is not null
216: AND exists
217: (SELECT 1 from okc_k_lines_b cle1
218: WHERE cle1.id = cle.cle_id
219: AND NVL(cle1.line_renewal_type_code,'FUL') not in ('DNR', 'KEP'))
220: /*
221: MINUS
247: SELECT h.id
248: FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
249: okc_k_party_roles_b pr,
250: okc_statuses_b st
251: , okc_k_lines_b s
252: WHERE h.id = l_source_chr_id
253: AND h.scs_code IN ('WARRANTY', x_scs_code)
254: AND h.chr_type = x_chr_type
255: AND h.template_yn = x_template_yn
295:
296: CURSOR get_terminated_opl(cp_opl_id IN NUMBER) IS
297: SELECT 1
298: FROM okc_operation_lines OPL,
299: okc_k_lines_b KLN
300: WHERE OPL.id = cp_opl_id
301: AND OPL.object_cle_id = KLN.id
302: AND KLN.date_terminated IS NOT NULL;
303:
544:
545: CURSOR valid_subline_sources(p_chr_id IN NUMBER , p_target_chr_id IN NUMBER) IS
546: SELECT s.id,
547: s.cle_id
548: FROM okc_k_lines_b s
549: WHERE s.dnz_chr_id = p_chr_id
550: AND s.end_date BETWEEN x_start_date AND x_end_date
551: --AND s.date_renewed is NULL
552: AND s.date_terminated IS NULL
553: AND s.date_cancelled IS NULL
554: AND s.lse_id IN (35, 7, 8, 9, 10, 11, 25)
555: AND NVL(s.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- Added by MKS
556: AND exists
557: (SELECT 1 from okc_k_lines_b cle
558: WHERE cle.id = s.cle_id
559: AND NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP')) -- Added by MKS
560: -- Added following code for bug#5981381 i.e. removed looping of function call of is_laready_in_ol
561: -- and moved the entire code from that function to here
1398:
1399: FUNCTION GET_LRT_RULE(p_line_id IN NUMBER) RETURN VARCHAR2 IS
1400: CURSOR GET_RULE IS
1401: SELECT NVL(LINE_RENEWAL_TYPE_CODE,'FUL') LINE_RENEWAL_TYPE_CODE
1402: FROM OKC_K_LINES_B
1403: WHERE ID = p_line_id;
1404: x_return VARCHAR2(200) := 'NOLRTRULE';
1405: BEGIN
1406: FOR cur_get_rule IN get_rule LOOP
1422: WHERE cle_id = p_line_id;
1423:
1424: CURSOR get_lrt_bto IS
1425: SELECT NVL(LINE_RENEWAL_TYPE_CODE,'FUL') LINE_RENEWAL_TYPE_CODE, BILL_TO_SITE_USE_ID, START_DATE, END_DATE
1426: FROM OKC_K_LINES_B
1427: WHERE ID = p_line_id;
1428:
1429:
1430: BEGIN
1632: l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1633:
1634: CURSOR target_line_id IS
1635: SELECT id
1636: FROM okc_k_lines_b
1637: WHERE dnz_chr_id = p_target_contract_id
1638: AND cle_id IS NULL
1639: AND lse_id IN (1, 12, 14, 19);
1640:
1702: l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PROGRAM_NAME || '.' || l_api_name;
1703:
1704:
1705: CURSOR get_oper_sub_lines(p_id IN NUMBER) IS
1706: SELECT object_cle_id FROM okc_operation_lines ol, okc_k_lines_b cle
1707: WHERE ol.parent_ole_id = p_id
1708: and ol.process_flag IN ('A','E')
1709: and ol.select_yn = 'Y'
1710: and cle.id = ol.object_cle_id
1837: chr.inv_organization_id,
1838: chr.authoring_org_id,
1839: chr.currency_code
1840: FROM okc_operation_lines b,
1841: okc_k_lines_b cle,
1842: okc_k_headers_all_b chr,
1843: okc_statuses_b st
1844: WHERE b.oie_id = p_oie_id
1845: and cle.id = b.object_cle_id
1861: AND select_yn = 'Y';
1862: */
1863: CURSOR get_oper_sub_lines(p_id IN NUMBER) IS
1864: SELECT ol.*, lse_id, start_date, end_date FROM okc_operation_lines ol,
1865: okc_k_lines_b cle
1866: WHERE parent_ole_id = p_id
1867: and process_flag IN ('A','E')
1868: and select_yn = 'Y'
1869: and cle.id = ol.object_cle_id
1888: -- anyway
1889:
1890: CURSOR get_subline_details(p_subline_id in NUMBER) IS
1891: select lse_id,start_date,end_date
1892: from okc_k_lines_b
1893: where id =p_subline_id;
1894:
1895: -- The following cursor was added by MSENGUPT 11/07
1896: -- Need to check that target is valid as by the time the CP picks this up the target contract may not be in valid status
2640: END IF;
2641:
2642: l_date_renewed := sysdate;
2643: FORALL i IN l_src_sub_line_id_tbl.FIRST..l_src_sub_line_id_tbl.LAST
2644: UPDATE okc_k_lines_b SET
2645: date_renewed = l_date_renewed,
2646: object_version_number = object_version_number + 1,
2647: last_updated_by = l_user_id,
2648: last_update_date = l_update_date,
2658: IF(FND_LOG.level_statement >= FND_LOG.g_current_runtime_level)THEN
2659: FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_top_lines','updating date_renewed for source toplines');
2660: END IF;
2661:
2662: UPDATE okc_k_lines_b tl SET
2663: tl.date_renewed = l_date_renewed,
2664: tl.object_version_number = tl.object_version_number + 1,
2665: tl.last_updated_by = l_user_id,
2666: tl.last_update_date = l_update_date,
2676: AND a.id = b.parent_ole_id
2677: AND a.parent_ole_id IS NULL
2678: AND b.select_yn = 'Y')
2679: AND NOT EXISTS
2680: (SELECT 1 FROM okc_k_lines_b sl
2681: WHERE sl.cle_id = tl.id
2682: AND sl.date_terminated IS NULL
2683: AND sl.date_cancelled IS NULL
2684: AND sl.date_renewed IS NULL);
2705: AND a.object_cle_id IS NULL
2706: AND a.subject_cle_id IS NULL
2707: AND a.select_yn = 'Y')
2708: AND NOT EXISTS
2709: (SELECT 1 FROM okc_k_lines_b tl
2710: WHERE tl.dnz_chr_id = h.id
2711: AND tl.cle_id IS NULL
2712: AND tl.lse_id IN (1,12,14,19)
2713: AND tl.date_terminated IS NULL
2917: l_error_text VARCHAR2(512);
2918:
2919: CURSOR c_top_lines(cp_chr_id IN NUMBER) IS
2920: SELECT c.cle_id, SUM(nvl(c.price_negotiated, 0)), SUM(nvl(s.tax_amount, 0))
2921: FROM okc_k_lines_b c, oks_k_lines_b s
2922: WHERE c.dnz_chr_id = cp_chr_id
2923: --get only sublines for 1,12,19 (14:no renewal, 46:no sublines)
2924: AND c.lse_id IN (7, 8, 9, 10, 11, 35, 13, 25)
2925: AND s.cle_id = c.id
2966:
2967: EXIT WHEN (l_id_tbl.COUNT = 0);
2968:
2969: FORALL i IN l_id_tbl.first..l_id_tbl.last
2970: UPDATE okc_k_lines_b
2971: SET price_negotiated = l_price_tbl(i),
2972: object_version_number = object_version_number + 1,
2973: last_updated_by = l_user_id,
2974: last_update_date = l_update_date,
3002:
3003: --update the header
3004: UPDATE okc_k_headers_all_b h
3005: SET h.estimated_amount =
3006: (SELECT SUM(price_negotiated) FROM okc_k_lines_b tl
3007: WHERE tl.dnz_chr_id = p_header_id AND tl.cle_id IS NULL
3008: AND tl.lse_id IN (1, 12, 19, 46)),
3009: h.object_version_number = h.object_version_number + 1,
3010: h.last_updated_by = l_user_id,
3017: WHERE h.id = p_header_id;
3018:
3019: UPDATE oks_k_headers_b h
3020: SET h.tax_amount =
3021: (SELECT SUM(stl.tax_amount) FROM okc_k_lines_b ctl, oks_k_lines_b stl
3022: WHERE ctl.dnz_chr_id = p_header_id AND ctl.cle_id IS NULL
3023: AND ctl.lse_id IN (1, 12, 19, 46) AND stl.cle_id = ctl.id),
3024: h.object_version_number = h.object_version_number + 1,
3025: h.last_updated_by = l_user_id,