[Home] [Help]
63: AND okh.date_terminated is NULL
64: AND okh.id not in(
65: SELECT ole.subject_chr_id
66: FROM okc_operation_instances_v oie,
67: okc_operation_lines_v ole,
68: okc_class_operations_v oco
69: WHERE oie.id = ole.oie_id
70: AND oie.id = :p_ctr_rec_oie_id
71: AND oie.cop_id = oco.id AND opn_code = ''MASS_CHANGE'')
103: AND okh.datetime_cancelled IS NULL -- Added as part of LLC
104: AND okh.date_terminated is NULL
105: AND okh.id not in ( SELECT ole.subject_chr_id
106: FROM okc_operation_instances_v oie,
107: okc_operation_lines_v ole,
108: okc_class_operations_v oco
109: WHERE oie.id = ole.oie_id
110: AND oie.id = :p_ctr_rec_oie_id
111: AND oie.cop_id = oco.id AND opn_code = ''MASS_CHANGE'')
155: mod.qa_check_yn,
156: org.name ,
157: okh.billed_at_source';
158:
159: l_from :=' FROM okc_k_headers_v okh,okc_operation_lines_v ole ,oks_mschg_operations_dtls mod, hr_operating_units org';
160: l_where:=' WHERE ole.select_yn = ''Y''
161: AND ole.process_flag IN (''A'',''E'')
162: AND okh.id = ole.subject_chr_id
163: AND ole.oie_id = :p_ctr_rec_oie_id
251: l_from||l_where||
252: ' AND okh.renewal_type_code is NULL
253: AND not exists(
254: select ol.object_chr_id
255: from okc_operation_lines ol
256: ,okc_operation_instances oi
257: ,okc_class_operations co
258: WHERE ol.object_chr_id = okh.id
259: AND ol.process_flag = ''P''
269: l_stmt := l_select||' , :l_old_value old_value' ||
270: l_from||l_where||
271: ' AND not exists(
272: select ol.object_chr_id
273: from okc_operation_lines ol
274: ,okc_operation_instances oi
275: ,okc_class_operations co
276: WHERE ol.object_chr_id = okh.id
277: AND ol.process_flag = ''P''
293: AND ''ERN'' = :p_ctr_rec_old_value
294: AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y'')
295: AND not exists(
296: select ol.object_chr_id
297: from okc_operation_lines ol
298: ,okc_operation_instances oi
299: ,okc_class_operations co
300: WHERE ol.object_chr_id = okh.id
301: AND ol.process_flag = ''P''
316: AND okh.renewal_type_code = :p_ctr_rec_old_value
317: AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <>''Y''
318: AND not exists(
319: select ol.object_chr_id
320: from okc_operation_lines ol
321: ,okc_operation_instances oi
322: ,okc_class_operations co
323: WHERE ol.object_chr_id = okh.id
324: AND ol.process_flag = ''P''
1387: l_where||
1388: ' AND okh.renewal_type_code is NULL
1389: AND not exists(
1390: select ol.object_chr_id
1391: from okc_operation_lines ol
1392: ,okc_operation_instances oi
1393: ,okc_class_operations co
1394: WHERE ol.object_chr_id = okh.id
1395: AND ol.process_flag = ''P''
1406: l_from||
1407: l_where||
1408: ' AND not exists(
1409: select ol.object_chr_id
1410: from okc_operation_lines ol
1411: ,okc_operation_instances oi
1412: ,okc_class_operations co
1413: WHERE ol.object_chr_id = okh.id
1414: AND ol.process_flag = ''P''
1432: AND ''ERN'' = :p_ctr_rec_old_value
1433: AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y''
1434: AND not exists(
1435: select ol.object_chr_id
1436: from okc_operation_lines ol
1437: ,okc_operation_instances oi
1438: ,okc_class_operations co
1439: WHERE ol.object_chr_id = okh.id
1440: AND ol.process_flag = ''P''
1456: AND okh.renewal_type_code = :p_ctr_rec_old_value
1457: AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <> ''Y''
1458: AND not exists(
1459: select ol.object_chr_id
1460: from okc_operation_lines ol
1461: ,okc_operation_instances oi
1462: ,okc_class_operations co
1463: WHERE ol.object_chr_id = okh.id
1464: AND ol.process_flag = ''P''
2494: l_where||
2495: ' AND okh.renewal_type_code is NULL
2496: AND not exists(
2497: select ol.object_chr_id
2498: from okc_operation_lines ol
2499: ,okc_operation_instances oi
2500: ,okc_class_operations co
2501: WHERE ol.object_chr_id = okh.id
2502: AND ol.process_flag = ''P''
2516: l_where|| 'AND okh.id = okp1.chr_id
2517: AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
2518: AND not exists(
2519: select ol.object_chr_id
2520: from okc_operation_lines ol
2521: ,okc_operation_instances oi
2522: ,okc_class_operations co
2523: WHERE ol.object_chr_id = okh.id
2524: AND ol.process_flag = ''P''
2541: AND ''ERN'' = :p_ctr_rec_old_value
2542: AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y''
2543: AND not exists(
2544: select ol.object_chr_id
2545: from okc_operation_lines ol
2546: ,okc_operation_instances oi
2547: ,okc_class_operations co
2548: WHERE ol.object_chr_id = okh.id
2549: AND ol.process_flag = ''P''
2567: AND okh.renewal_type_code = :p_ctr_rec_old_value
2568: AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <> ''Y''
2569: AND not exists(
2570: select ol.object_chr_id
2571: from okc_operation_lines ol
2572: ,okc_operation_instances oi
2573: ,okc_class_operations co
2574: WHERE ol.object_chr_id = okh.id
2575: AND ol.process_flag = ''P''
3826: l_where||
3827: ' AND okh.renewal_type_code is NULL
3828: AND not exists(
3829: select ol.object_chr_id
3830: from okc_operation_lines ol
3831: ,okc_operation_instances oi
3832: ,okc_class_operations co
3833: WHERE ol.object_chr_id = okh.id
3834: AND ol.process_flag = ''P''
3845: l_from||
3846: l_where||
3847: ' AND not exists(
3848: select ol.object_chr_id
3849: from okc_operation_lines ol
3850: ,okc_operation_instances oi
3851: ,okc_class_operations co
3852: WHERE ol.object_chr_id = okh.id
3853: AND ol.process_flag = ''P''
3870: AND ''ERN'' = :p_ctr_rec_old_value
3871: AND oksh.ELECTRONIC_RENEWAL_FLAG = ''Y''
3872: AND not exists(
3873: select ol.object_chr_id
3874: from okc_operation_lines ol
3875: ,okc_operation_instances oi
3876: ,okc_class_operations co
3877: WHERE ol.object_chr_id = okh.id
3878: AND ol.process_flag = ''P''
3894: AND okh.renewal_type_code = :p_ctr_rec_old_value
3895: AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <> ''Y''
3896: AND not exists(
3897: select ol.object_chr_id
3898: from okc_operation_lines ol
3899: ,okc_operation_instances oi
3900: ,okc_class_operations co
3901: WHERE ol.object_chr_id = okh.id
3902: AND ol.process_flag = ''P''
4958: l_where||
4959: ' AND okh.renewal_type_code is NULL
4960: AND not exists(
4961: select ol.object_chr_id
4962: from okc_operation_lines ol
4963: ,okc_operation_instances oi
4964: ,okc_class_operations co
4965: WHERE ol.object_chr_id = okh.id
4966: AND ol.process_flag = ''P''
4980: l_from||
4981: l_where||
4982: ' AND not exists(
4983: select ol.object_chr_id
4984: from okc_operation_lines ol
4985: ,okc_operation_instances oi
4986: ,okc_class_operations co
4987: WHERE ol.object_chr_id = okh.id
4988: AND ol.process_flag = ''P''
5008: AND ''ERN'' = :p_ctr_rec_old_value
5009: AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y''
5010: AND not exists(
5011: select ol.object_chr_id
5012: from okc_operation_lines ol
5013: ,okc_operation_instances oi
5014: ,okc_class_operations co
5015: WHERE ol.object_chr_id = okh.id
5016: AND ol.process_flag = ''P''
5036: AND okh.renewal_type_code = :p_ctr_rec_old_value
5037: AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <>''Y''
5038: AND not exists(
5039: select ol.object_chr_id
5040: from okc_operation_lines ol
5041: ,okc_operation_instances oi
5042: ,okc_class_operations co
5043: WHERE ol.object_chr_id = okh.id
5044: AND ol.process_flag = ''P''
7036: mrd.old_value,
7037: okh.billed_at_source
7038:
7039: FROM okc_k_headers_v okh,
7040: okc_operation_lines_v ole,
7041: okc_masschange_req_dtls_v mrd
7042: WHERE ole.select_yn = 'Y'
7043: AND ole.process_flag is NULL
7044: AND okh.id = ole.subject_chr_id
13120: l_omrv_tbl_in OKC_OPER_INST_PUB.mrdv_tbl_type; --OPERATION LINES
13121:
13122: CURSOR Get_opn_lines IS
13123: SELECT ole.id ole_id,omr.id omr_id
13124: from OKC_OPERATION_LINES_V ole, OKC_MASSCHANGE_REQ_DTLS_V omr
13125: where ole.id = omr.ole_id
13126: AND omr.oie_id IS NULL
13127: AND ole.oie_id = p_oie_id;
13128: ------------------------------------------------------------------
13943: PROCEDURE UPDATE_LINE_STATUS(p_oie_id IN Number) IS
13944: PRAGMA AUTONOMOUS_TRANSACTION;
13945: BEGIN
13946:
13947: UPDATE okc_operation_lines
13948: SET process_flag = NULL
13949: WHERE oie_id = p_oie_id
13950: AND select_yn = 'Y'
13951: AND process_flag in ('A','E');
13973: ,mrd.last_update_login
13974: ,mrd.security_group_id
13975: ,okh.sts_code status_code
13976: FROM okc_masschange_req_dtls mrd,
13977: okc_operation_lines opn,
13978: okc_k_headers_b okh
13979: WHERE mrd.ole_id = opn.id(+)
13980: AND opn.subject_chr_id = okh.id(+) ;
13981: