[Home] [Help]
PACKAGE BODY: APPS.XNP_CVU_PKG
Source
1 Package Body XNP_CVU_PKG AS
2 /* $Header: XNPCVUB.pls 120.2 2006/02/13 07:44:03 dputhiye ship $ */
3
4
5
6 g_category_no_error BOOLEAN; -- Used in StartCategory, EndCategory, EndOfTables
7
8 FUNCTION GetNumErrMsg(v_msg_name IN VARCHAR2,
9 v_token_1 IN VARCHAR2 DEFAULT NULL,
10 v_value_1 IN VARCHAR2 DEFAULT NULL,
11 v_token_2 IN VARCHAR2 DEFAULT NULL,
12 v_value_2 IN VARCHAR2 DEFAULT NULL,
13 v_token_3 IN VARCHAR2 DEFAULT NULL,
14 v_value_3 IN VARCHAR2 DEFAULT NULL,
15 v_token_4 IN VARCHAR2 DEFAULT NULL,
16 v_value_4 IN VARCHAR2 DEFAULT NULL)
17 RETURN VARCHAR2 IS
18 l_msg_name VARCHAR2(1996) := NULL;
19 BEGIN
20 l_msg_name := GetMsgTxt (v_msg_name,
21 v_token_1,
22 v_value_1,
23 v_token_2,
24 v_value_2,
25 v_token_3,
26 v_value_3,
27 v_token_4,
28 v_value_4);
29
30 l_msg_name := fnd_message.get_number('XNP',v_msg_name) || ':'|| l_msg_name;
31 return l_msg_name;
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 fnd_message.set_name('XNP','XNP_CVU_PROGRAM_ERROR');
36 fnd_message.set_token('ERROR_CODE',to_char(SQLCODE));
37 fnd_message.set_token('ERROR_MESSAGE',SQLERRM);
38 htp.p(htf.bodyOpen);
39 htp.p(gc_error_display||' : '||fnd_message.get);
40 htp.p(htf.br);
41 htp.p(htf.bodyClose);
42 END GetNumErrMsg;
43
44
45 -- GetMsgTxt: Using msg_name and msg_tokens, form the message
46 -- v_msg_name: Message Name
47 -- v_token_n: Token to be substituted
48
49 FUNCTION GetMsgTxt (v_msg_name IN VARCHAR2,
50 v_token_1 IN VARCHAR2 DEFAULT NULL,
51 v_value_1 IN VARCHAR2 DEFAULT NULL,
52 v_token_2 IN VARCHAR2 DEFAULT NULL,
53 v_value_2 IN VARCHAR2 DEFAULT NULL,
54 v_token_3 IN VARCHAR2 DEFAULT NULL,
55 v_value_3 IN VARCHAR2 DEFAULT NULL,
56 v_token_4 IN VARCHAR2 DEFAULT NULL,
57 v_value_4 IN VARCHAR2 DEFAULT NULL)
58 RETURN VARCHAR2
59 IS
60 l_msg_name VARCHAR2(1996) := NULL;
61 BEGIN
62 fnd_message.set_name('XNP',substr(v_msg_name, 1, 30));
63
64 if (v_token_1 IS NOT NULL) then
65 fnd_message.set_token(v_token_1, v_value_1);
66 end if;
67 if (v_token_2 IS NOT NULL) then
68 fnd_message.set_token(v_token_2, v_value_2);
69 end if;
70 if (v_token_3 IS NOT NULL) then
71 fnd_message.set_token(v_token_3, v_value_3);
72 end if;
73 if (v_token_4 IS NOT NULL) then
74 fnd_message.set_token(v_token_4, v_value_4);
75 end if;
76
77 l_msg_name := fnd_message.get;
78 return l_msg_name;
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 fnd_message.set_name('XNP','XNP_CVU_PROGRAM_ERROR');
83 fnd_message.set_token('ERROR_CODE',to_char(SQLCODE));
84 fnd_message.set_token('ERROR_MESSAGE',SQLERRM);
85 htp.p(htf.bodyOpen);
86 htp.p(gc_error_display||' : '||fnd_message.get);
87 htp.p(htf.br);
88 htp.p(htf.bodyClose);
89 END GetMsgTxt;
90
91 -- Print: Print the message based on the paramters passed
92 -- p_text: Text to be printed
93 -- p_text_indicator: 'OK','ERROR','WARNING'
94 -- p_text_type: gc_RECORDS, gc_HEADER, gc_MESSAGE, gc_FOOTER, gc_CONTEXT, gc_SUB_CONTEXT
95
96 PROCEDURE Print(
97 p_text IN VARCHAR2,
98 p_text_indicator IN VARCHAR2 DEFAULT gc_OK,
99 p_text_type IN VARCHAR2 DEFAULT gc_RECORDS)
100 IS
101 BEGIN
102
103 IF p_text_type = gc_HEADER THEN
104 htp.p(htf.nl);
105 htp.p(htf.nl);
106 htp.fontOpen(ccolor=>'blue', csize=>5);
107 htp.p(htf.italic(p_text)) ;
108 htp.fontClose;
109 htp.p(htf.nl);
110 htp.p(htf.nl);
111 ELSIF p_text_type = gc_CONTEXT OR p_text_type = gc_MESSAGE THEN
112 htp.fontOpen(ccolor=>'gray', csize=>3);
113 htp.p(htf.para);
114 htp.p(htf.strong(p_text)) ;
115 htp.fontClose;
116 htp.p(htf.line);
117 ELSIF p_text_type = gc_SUB_CONTEXT THEN
118 htp.fontOpen(ccolor=>'gray', csize=>3);
119 htp.p(htf.strong(p_text)) ;
120 htp.fontClose;
121 ELSIF p_text_type = gc_RECORDS THEN
122 htp.p(htf.strong(p_text)) ;
123 END IF;
124
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 -- log error;
129 fnd_message.set_name('XNP','XNP_CVU_PROGRAM_ERROR');
130 fnd_message.set_token('ERROR_CODE',to_char(SQLCODE));
131 fnd_message.set_token('ERROR_MESSAGE',SQLERRM);
132 htp.p(htf.bodyOpen);
133 htp.p(gc_error_display||' : '||fnd_message.get);
134 htp.p(htf.br);
135 htp.p(htf.bodyClose);
136 END Print;
137
138 FUNCTION GetGeoCode(id IN NUMBER)
139 RETURN VARCHAR2
140 IS
141 l_code VARCHAR2(40) := NULL;
142 BEGIN
143 SELECT code
144 INTO l_code
145 FROM xnp_geo_areas_b
146 WHERE geo_area_id = id;
147 RETURN l_code;
148 END;
149
150
151 FUNCTION GetFeName(id IN NUMBER)
152 RETURN VARCHAR2
153 IS
154 l_FeName VARCHAR2(40) := NULL;
155 l_fe_type VARCHAR2(40) := NULL;
156 l_fe_type_id NUMBER := NULL;
157 l_fe_sw_generic VARCHAR2(40) := NULL;
158 l_adapter_type VARCHAR2(40) := NULL;
159
160 BEGIN
161 xdp_engine.get_fe_configinfo
162 (id
163 ,l_FeName
164 ,l_fe_type_id
165 ,l_fe_type
166 ,l_fe_sw_generic
167 ,l_adapter_type
168 );
169 RETURN l_FeName;
170 END;
171
172 -- HandleError
173 --
174
175 PROCEDURE HandleError(p_custom_message IN VARCHAR2,
176 p_sqlcode IN NUMBER,
177 p_sqlerrm IN VARCHAR2)
178 IS
179 BEGIN
180
181 Print(p_custom_message || ' ' ||p_sqlerrm, gc_OK, gc_message);
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 htp.p('HandleError'|| sqlerrm);
186 END HandleError;
187
188
189
190 FUNCTION ColorCode(
191 p_text VARCHAR2,
192 p_indicator VARCHAR2,
193 p_type VARCHAR2) RETURN VARCHAR2
194 IS
195 v_text VARCHAR2(200);
196 BEGIN
197
198 v_text := p_text;
199
200 IF p_type = gc_HEADING THEN
201 v_text := p_text;
202 ELSIF p_type = gc_DETAILS THEN
203 IF p_indicator = gc_ERROR THEN
204 v_text := htf.fontOpen(ccolor=>'red')||p_text||htf.fontClose;
205 ELSIF p_indicator = gc_WARNING THEN
206 v_text := htf.fontOpen(ccolor=>'amber')||p_text||htf.fontClose;
207 ELSE
208 v_text := p_text;
209 END IF;
210 ELSE
211 v_text := p_text;
212
213 END IF;
214
215 return v_text;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 return v_text;
220 END ColorCode;
221
222
223
224
225 PROCEDURE PrintReportDetails(
226 p_type VARCHAR2,
227 p_indicator VARCHAR2,
228 p_one VARCHAR2,
229 p_two VARCHAR2 Default NULL,
230 p_three VARCHAR2 Default NULL,
231 p_four VARCHAR2 Default NULL,
232 p_five VARCHAR2 Default NULL)
233 IS
234 v_text VARCHAR2(1000);
235 v_one_len NUMBER := 50;
236 v_two_len NUMBER := 50;
237 v_three_len NUMBER := 50;
238 v_four_len NUMBER := 50;
239 v_five_len NUMBER := 50;
240
241 v_delim VARCHAR2(40);
242
243 BEGIN
244
245
246 IF p_type = gc_HEADING THEN
247
248 v_delim := gc_TH;
249
250 IF p_five IS NOT NULL THEN
251 v_text :=
252 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
253 ||v_delim ||
254 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type)
255 ||v_delim ||
256 ColorCode(lpad(p_three, v_three_len),p_indicator, p_type)
257 ||v_delim ||
258 ColorCode(lpad(p_four, v_four_len),p_indicator, p_type)
259 ||v_delim ||
260 ColorCode(lpad(p_five, v_five_len),p_indicator, p_type);
261 ELSIF p_four IS NOT NULL THEN
262 v_text :=
263 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
264 ||v_delim ||
265 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type)
266 ||v_delim ||
267 ColorCode(lpad(p_three, v_three_len),p_indicator, p_type)
268 ||v_delim ||
269 ColorCode(lpad(p_four, v_four_len),p_indicator, p_type);
270 ELSIF p_three IS NOT NULL THEN
271 v_text :=
272 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
273 ||v_delim ||
274 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type)
275 ||v_delim ||
276 ColorCode(lpad(p_three, v_three_len),p_indicator, p_type);
277 ELSIF p_two IS NOT NULL THEN
278 v_text :=
279 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
280 ||v_delim ||
281 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type);
282 ELSE
283 v_text :=
284 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type);
285 END IF;
286
287 v_text := gc_TRTH_open ||v_text || gc_TRTH_close;
288 Print(gc_TABLE_open, gc_OK, gc_RECORDS);
289 Print(v_text, p_indicator, gc_RECORDS);
290
291 ELSIF p_type = gc_DETAILS THEN
292 v_delim := gc_TD;
293
294 IF p_five IS NOT NULL THEN
295 v_text :=
296 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
297 ||v_delim ||
298 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type)
299 ||v_delim ||
300 ColorCode(lpad(p_three, v_three_len),p_indicator, p_type)
301 ||v_delim ||
302 ColorCode(lpad(p_four, v_four_len),p_indicator, p_type)
303 ||v_delim ||
304 ColorCode(lpad(p_five, v_five_len),p_indicator, p_type);
305 ELSIF p_four IS NOT NULL THEN
306 v_text :=
307 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
308 ||v_delim ||
309 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type)
310 ||v_delim ||
311 ColorCode(lpad(p_three, v_three_len),p_indicator, p_type)
312 ||v_delim ||
313 ColorCode(lpad(p_four, v_four_len),p_indicator, p_type);
314 ELSIF p_three IS NOT NULL THEN
315 v_text :=
316 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
317 ||v_delim ||
318 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type)
319 ||v_delim ||
320 ColorCode(lpad(p_three, v_three_len),p_indicator, p_type);
321 ELSIF p_two IS NOT NULL THEN
322 v_text :=
323 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type)
324 ||v_delim ||
325 ColorCode(lpad(p_two, v_two_len),p_indicator, p_type);
326 ELSE
327 v_text :=
328 ColorCode(lpad(p_one, v_one_len), p_indicator, p_type);
329 END IF;
330
331 v_text := gc_TRTD_open || v_text || gc_TRTD_close;
332 Print(v_text, p_indicator, gc_RECORDS);
333
334 ELSIF p_type = gc_SUB_CONTEXT THEN
335 Print(v_text, gc_OK, gc_SUB_CONTEXT);
336 ELSE
337 Print(v_text, gc_OK, gc_CONTEXT);
338 END IF;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 htp.p ('PrintReportDetails' || sqlerrm);
343 END PrintReportDetails;
344
345 -- Table is opened in the heading printing.
346 -- heading is printed only for the first time (in most cases)
347 -- After that first_time flag is set to FALSE
348 -- so it is sufficient to pass the NOT(p_first_time) flag to this.
349
350 PROCEDURE EndOfTable(p_table_open BOOLEAN)
351 IS
352 BEGIN
353
354 IF p_table_open THEN
355 IF g_display_type = gc_HTML THEN
356 -- Close Table Opened in Header
357 Print(gc_TABLE_close, gc_OK, gc_RECORDS);
358 END IF;
359 g_category_no_error := FALSE; -- Used in EndCategory function
360 END IF;
361
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 htp.p ('EndOfTable' || sqlerrm);
366 END EndOfTable;
367
368 -- Message Names:
369 -- INITIAL_STATUS_OF_SV
370 -- One and Only one Subscription Version Status Type among the
371 -- Active status types should be "Initial Status of
372 -- subscription version
373
374 PROCEDURE INITIAL_STATUS_OF_SV IS
375 v_found BOOLEAN := FALSE;
376 v_text VARCHAR2(1000);
377 v_default_porting_status varchar2(150) := null;
378 v_cnt NUMBER := 0;
379 v_invalid_status number := 0;
380
381 BEGIN
382 FND_PROFILE.GET(NAME => 'DEFAULT_PORTING_STATUS',
383 VAL => v_default_porting_status);
384
385 SELECT 1
386 INTO v_invalid_status
387 FROM dual
388 WHERE EXISTS
389 (SELECT 'X'
390 FROM xnp_sv_status_types_b
391 WHERE status_type_code = v_default_porting_status
392 AND active_flag = 'Y');
393
394 EXCEPTION when no_data_found then
395 v_text := GetNumErrMsg('XNP_CVU_INITIAL_STATUS_OF_SV');
396 Print(v_text,gc_ERROR, gc_MESSAGE);
397 WHEN OTHERS THEN
398 HandleError('INITIAL_STATUS_OF_SV', SQLCODE, SQLERRM);
399 END INITIAL_STATUS_OF_SV;
400
401 -- Message Names:
402 -- XNP_CVU_LOCAL_SP_NAME
403 -- The local sp's name must be not null and must be a
404 -- valid service provider name
405 --
406
407 PROCEDURE LOCAL_SP_NAME IS
408 v_found BOOLEAN := FALSE;
409 v_text VARCHAR2(1000);
410 v_local_sp_name varchar2(150) := null;
411 v_invalid_sp_name number := 0;
412
413 BEGIN
414 FND_PROFILE.GET(NAME => 'SP_NAME',
415 VAL => v_local_sp_name);
416
417 SELECT 1
418 INTO v_invalid_sp_name
419 FROM dual
420 WHERE EXISTS
421 (SELECT 'X'
422 FROM xnp_service_providers
423 WHERE code = v_local_sp_name
424 AND active_flag = 'Y');
425
426 EXCEPTION when no_data_found then
427 v_text := GetNumErrMsg('XNP_CVU_LOCAL_SP_NAME');
428 Print(v_text,gc_ERROR, gc_MESSAGE);
429 WHEN OTHERS THEN
430 HandleError('LOCAL_SP_NAME', SQLCODE, SQLERRM);
431 END LOCAL_SP_NAME;
432
433
434 -- Message Names:
435 -- XNP_CVU_PHASE_IND_NO_STAT_TYPE
436 -- Each Phase Indicator should have at least one
437 -- Active Status Type Code associated with it
438
439 PROCEDURE PHASE_IND_NO_STAT_TYPE IS
440
441 v_text VARCHAR2(1000);
442
443 CURSOR c_pi IS
444 SELECT pi , count(*) cnt,
445 decode (pi,
446 'INQUIRY', decode (count(*), 0, gc_WARNING,gc_OK),
447 decode(count(*), 0, gc_ERROR,gc_OK)) result_type
448 FROM
449 (SELECT flv.lookup_code pi, sst.status_type_code stc
450 FROM xnp_sv_status_types_b sst, fnd_lookup_values flv
451 WHERE flv.lookup_code = sst.phase_indicator (+)
452 AND flv.lookup_type = 'XNP_PHASE_INDICATOR'
453 AND flv.enabled_flag = 'Y'
454 AND sst.active_flag = 'Y')
455 GROUP BY pi;
456
457 v_first_time BOOLEAN := TRUE;
458 BEGIN
459 FOR pi_rec in c_pi
460 LOOP
461 IF pi_rec.result_type = gc_ERROR THEN
462 IF v_first_time THEN
463 v_text := GetNumErrMsg('XNP_CVU_PHASE_IND_NO_STAT_TYPE');
464 Print(v_text,gc_ERROR,gc_CONTEXT);
465
466 PrintReportDetails(gc_HEADING,gc_ERROR, 'PHASE_INDICATOR','COUNT', 'RESULT');
467
468 v_first_time := FALSE;
469 END IF;
470 PrintReportDetails(gc_DETAILS, pi_rec.result_type,
471 pi_rec.pi, pi_rec.cnt, pi_rec.result_type);
472 Print(v_text, pi_rec.result_type, gc_RECORDS);
473 END IF;
474 END LOOP;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 HandleError('PHASE_IND_NO_STAT_TYPE', SQLCODE, SQLERRM);
479 END PHASE_IND_NO_STAT_TYPE;
480
481 -- Mesage Name:
482 -- XNP_CVU_SINGLE_GEO_TREE
483 -- There can be only one Geographic tree and except for "WORLD"
484 -- no other node can have parent node to be null
485
486 PROCEDURE SINGLE_GEO_TREE IS
487
488 -- CHECK: Need to look at performance issue in big trees
489
490 -- Bug fix 1647105.
491
492 CURSOR c_bad_nodes IS
493 SELECT gho.child_geo_area_id child,
494 gho.parent_geo_area_id parent
495 FROM xnp_geo_hierarchy gho
496 WHERE NOT EXISTS
497 (SELECT 'X'
498 FROM xnp_geo_hierarchy ghi
499 WHERE ghi.child_geo_area_id = gho.child_geo_area_id
500 AND ghi.parent_geo_area_id = gho.parent_geo_area_id
501 -- START WITH parent_geo_area_id = 0
502 START WITH parent_geo_area_id = (select geo_area_id from xnp_geo_areas_b
503 where geo_area_type_code = 'REGION' and
504 code = 'WORLD')
505 CONNECT BY PRIOR child_geo_area_id = parent_geo_area_id);
506
507 v_text VARCHAR2(1000);
508 v_first_time BOOLEAN := TRUE;
509
510 BEGIN
511 FOR bad_nodes_rec IN c_bad_nodes
512 LOOP
513 IF v_first_time THEN
514 v_text := GetNumErrMsg('XNP_CVU_SINGLE_GEO_TREE');
515 Print(v_text,gc_ERROR, gc_CONTEXT);
516
517 PrintReportDetails(gc_HEADING, gc_ERROR, 'PARENT','CHILD');
518
519 v_first_time := FALSE;
520 END IF;
521
522 PrintReportDetails(gc_DETAILS,gc_ERROR,
523 GetGeoCode(bad_nodes_rec.parent),
524 GetGeoCode(bad_nodes_rec.child));
525 END LOOP;
526
527 EndOfTable(NOT v_first_time);
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 HandleError('SINGLE_GEO_TREE', SQLCODE, SQLERRM);
532 END SINGLE_GEO_TREE;
533
534
535
536 -- Message Name:
537 -- XNP_CVU_FA_SHOULD_HAVE_FP
538 -- FA should have FP configured for it
539 -- XNP_CVU_FA_SHOULD_HAVE_FE
540 -- If FA has a valid configuration,
541 -- it should have a valid FEs defind for it
542
543 PROCEDURE FULFILL_ACTIONS
544 IS
545 v_text VARCHAR2(1000);
546 v_first_time BOOLEAN := TRUE;
547
548 CURSOR c_no_fp IS
549 SELECT fulfillment_action_id id, fulfillment_action name
550 FROM xdp_fulfill_actions fa
551 WHERE NOT EXISTS
552 (SELECT 1
553 FROM xdp_fa_fulfillment_proc fap
554 WHERE fap.fulfillment_action_id = fa.fulfillment_action_id);
555
556 CURSOR c_no_fe IS
557 SELECT fulfillment_action_id id, fulfillment_action name
558 FROM xdp_fulfill_actions fa
559 WHERE EXISTS
560 (SELECT 1
561 FROM xdp_fa_fulfillment_proc fap
562 WHERE fap.fulfillment_action_id = fa.fulfillment_action_id)
563
564 AND NOT EXISTS
565 (SELECT 1
566 FROM xdp_fa_fulfillment_proc fap ,
567 xdp_fes fes,
568 xdp_fe_sw_gen_lookup fgl
569 WHERE fap.fulfillment_action_id
570 = fa.fulfillment_action_id
571 AND fap.fe_sw_gen_lookup_id
572 = fgl.fe_sw_gen_lookup_id
573 AND fgl.fetype_id = fes.fetype_id
574 AND SYSDATE BETWEEN fes.valid_date
575 AND nvl(fes.invalid_date, gc_max_date));
576
577 BEGIN
578 FOR rec IN c_no_fp
579 LOOP
580 IF v_first_time THEN
581 v_text := GetNumErrMsg('XNP_CVU_FA_SHOULD_HAVE_FP');
582 Print(v_text,gc_ERROR, gc_CONTEXT);
583
584 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
585
586 v_first_time := FALSE;
587 END IF;
588
589 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
590 END LOOP;
591
592 EndOfTable(NOT v_first_time);
593
594
595 v_text := NULL;
596 v_first_time := TRUE;
597
598 FOR rec IN c_no_fe
599 LOOP
600 IF v_first_time THEN
601 v_text := GetNumErrMsg('XNP_CVU_FA_SHOULD_HAVE_FE');
602 Print(v_text,gc_ERROR, gc_CONTEXT);
603
604 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
605
606 v_first_time := FALSE;
607 END IF;
608
609 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
610
611 END LOOP;
612
613 EndOfTable(NOT v_first_time);
614
615
616 EXCEPTION
617 WHEN OTHERS THEN
618 HandleError('FULFILL_ACTIONS', SQLCODE, SQLERRM);
619 END FULFILL_ACTIONS;
620
621
622 -- Message Names:
623 -- XNP_CVU_INVALID_FE
624 -- If valid_date is null or invalid_date < sysdate
625 -- XNP_CVU_FE_DATE_WINDOW
626 -- Generic Configuration date window must lie within the corresponding
627 -- Fulfillment Elements date window.
628
629 PROCEDURE FULFILL_ELEMENT_VALIDITY
630 IS
631 v_text VARCHAR2(1000);
632 v_first_time BOOLEAN := TRUE;
633
634 CURSOR c_invalid_fe IS
635 SELECT fe_id id, fulfillment_element_name name,
636 to_char(invalid_date, gc_DATE_FORMAT) valid_date,
637 DECODE(invalid_date, null, gc_NULL,
638 to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
639 FROM xdp_fes
640 WHERE invalid_date < SYSDATE;
641
642 CURSOR c_date_window IS
643 SELECT fe.fe_id id, fulfillment_element_name name,
644 to_char(valid_date, gc_DATE_FORMAT) valid_date,
645 DECODE(invalid_date, null, gc_NULL,
646 to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
647 FROM xdp_fes fe, xdp_fe_generic_config fgc
648 WHERE fe.fe_id = fgc.fe_id
649 AND NOT
650 (fgc.start_date BETWEEN
651 fe.valid_date and nvl(fe.invalid_date, gc_max_date)
652 AND
653 nvl(fgc.end_date, gc_max_date) BETWEEN
654 fe.valid_date and nvl(fe.invalid_date, gc_max_date));
655
656 BEGIN
657 FOR rec IN c_invalid_fe
658 LOOP
659 IF v_first_time THEN
660 v_text := GetNumErrMsg('XNP_CVU_INVALID_FE');
661 Print(v_text,gc_ERROR, gc_CONTEXT);
662
663 PrintReportDetails(gc_HEADING, gc_ERROR, gc_NAME,
664 gc_VALID_DATE, gc_INVALID_DATE);
665 v_first_time := FALSE;
666 END IF;
667
668 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.name,
669 rec.valid_date, rec.invalid_date);
670
671 END LOOP;
672
673 EndOfTable(NOT v_first_time);
674
675 v_first_time := TRUE;
676
677 FOR rec IN c_date_window
678 LOOP
679 IF v_first_time THEN
680 v_text := GetNumErrMsg('XNP_CVU_FE_DATE_WINDOW');
681 Print(v_text,gc_ERROR, gc_CONTEXT);
682
683 PrintReportDetails(gc_HEADING, gc_ERROR, gc_NAME,
684 gc_VALID_DATE, gc_INVALID_DATE);
685 v_first_time := FALSE;
686 END IF;
687
688 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.name,
689 rec.valid_date, rec.invalid_date);
690
691 END LOOP;
692 EndOfTable(NOT v_first_time);
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 HandleError('FULFILL_ELEMENT_VALIDITY', SQLCODE, SQLERRM);
697 END FULFILL_ELEMENT_VALIDITY;
698
699
700 -- Message Names
701 -- XNP_CVU_FE_NO_GENERIC_CONFIG
702 -- Every Fulfillment Element should have a Valid Generic Configuration
703 -- XNP_CVU_FE_NO_ADAPTER
704 -- FE should have adapters defined for it.
705 -- XNP_CVU_FETYPE_NO_SW_GEN
706 -- Every Fulfillment Element Type should have a Valid SW Generic Configuration
707
708 PROCEDURE FULFILL_ELEMENT_REFERENCES
709 IS
710 v_text VARCHAR2(1000);
711 v_first_time BOOLEAN := TRUE;
712
713 CURSOR c_no_gen_config IS
714 SELECT fe.fe_id id , fe.fulfillment_element_name name
715 FROM xdp_fes fe
716 WHERE NOT EXISTS
717 (SELECT 1
718 FROM xdp_fe_generic_config fgc
719 WHERE fe.fe_id = fgc.fe_id
720 AND SYSDATE BETWEEN fgc.start_date
721 AND nvl(fgc.end_date, gc_max_date)
722 );
723
724 CURSOR c_no_adapter IS
725 SELECT fe.fe_id id, fe.fulfillment_element_name name
726 FROM xdp_fes fe
727 WHERE NOT EXISTS
728 (SELECT 1
729 FROM xdp_adapter_reg ar
730 WHERE fe.fe_id = ar.fe_id);
731
732 CURSOR c_no_sw_gen_config IS
733 SELECT fet.fetype_id id, fet.fulfillment_element_type name
734 FROM xdp_fe_types fet
735 WHERE NOT EXISTS
736 (SELECT 1
737 FROM xdp_fe_sw_gen_lookup sgl
738 WHERE fet.fetype_id = sgl.fetype_id
739 );
740
741 BEGIN
742 FOR rec IN c_no_gen_config
743 LOOP
744 IF v_first_time THEN
745 v_text := GetNumErrMsg('XNP_CVU_FE_NO_GENERIC_CONFIG');
746 Print(v_text,gc_ERROR, gc_CONTEXT);
747
748 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
749
750 v_first_time := FALSE;
751 END IF;
752
753 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
754
755 END LOOP;
756
757 EndOfTable(NOT v_first_time);
758
759 v_first_time := TRUE;
760 FOR rec IN c_no_adapter
761 LOOP
762 IF v_first_time THEN
763 v_text := GetNumErrMsg('XNP_CVU_FE_NO_ADAPTER');
764 Print(v_text,gc_ERROR, gc_CONTEXT);
765
766 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
767
768 v_first_time := FALSE;
769 END IF;
770
771 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
772
773 END LOOP;
774
775 EndOfTable(NOT v_first_time);
776
777 v_first_time := TRUE;
778 FOR rec IN c_no_sw_gen_config
779 LOOP
780 IF v_first_time THEN
781 v_text := GetNumErrMsg('XNP_CVU_FETYPE_NO_SW_GEN');
782 Print(v_text,gc_ERROR, gc_CONTEXT);
783
784 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
785
786 v_first_time := FALSE;
787 END IF;
788
789 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
790
791 END LOOP;
792
793 EndOfTable(NOT v_first_time);
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 HandleError('FULFILL_ELEMENT_REFERENCES', SQLCODE, SQLERRM);
798 END FULFILL_ELEMENT_REFERENCES;
799
800
801
802 -- Message Names:
803 -- XNP_CVU_NEW_ADAPTER_NO_PORC
804 -- If ADAPTER_TYPE is "FILE", "JSCRIPT" or "INTERACTIVE"
805 -- then there should be a procedure defined in SW_START_PROC and SW_END_PROC.
806
807 PROCEDURE NEW_ADAPTER_TYPES
808 IS
809 v_text VARCHAR2(1000);
810 v_first_time BOOLEAN := TRUE;
811
812 CURSOR c_no_proc IS
813 SELECT fgc.fe_id,
814 nvl(fgc.sw_start_proc, gc_NULL) sw_start_proc,
815 nvl(fgc.sw_exit_proc, gc_NULL) sw_exit_proc
816 FROM xdp_fe_generic_config fgc
817 WHERE EXISTS
818 (SELECT 1
819 FROM xdp_fes fes
820 WHERE fes.fe_id = fgc.fe_id
821 AND fetype_id IN (SELECT fetype_id
822 FROM xdp_fe_types
823 WHERE fulfillment_element_type
824 IN (gc_FILE
825 , gc_JSCRIPT
826 , gc_INTERACTIVE)))
827
828 AND (fgc.sw_start_proc IS NULL
829 OR
830 fgc.sw_exit_proc IS NULL);
831
832 BEGIN
833 FOR rec IN c_no_proc
834 LOOP
835 IF v_first_time THEN
836 v_text := GetNumErrMsg('XNP_CVU_NEW_ADAPTER_NO_PORC');
837 Print(v_text,gc_ERROR, gc_CONTEXT);
838
839 PrintReportDetails(gc_HEADING, gc_ERROR, 'SW_START_PROC', 'SW_END_PROC');
840
841 v_first_time := FALSE;
842 END IF;
843
844 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.sw_start_proc, rec.sw_exit_proc);
845
846 END LOOP;
847
848 EndOfTable(NOT v_first_time);
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 HandleError('NEW_ADAPTER_TYPES', SQLCODE, SQLERRM);
853 END NEW_ADAPTER_TYPES;
854
855 -- Message Names:
856 -- SP_NO_ADAPTER:
857 -- Service Provider should have valid adapter associated with it.
858
859 PROCEDURE SERVICE_PROVIDERS IS
860
861 v_text VARCHAR2(1000);
862 v_first_time BOOLEAN := TRUE;
863
864 CURSOR c_sp IS
865 SELECT sp_id, code, sp_type, name
866 FROM xnp_service_providers sp
867 WHERE NOT EXISTS
868 (SELECT 1
869 FROM xnp_sp_adapters spa
870 WHERE sp.sp_id = spa.sp_id);
871 BEGIN
872 FOR rec IN c_sp
873 LOOP
874 IF v_first_time THEN
875 v_text := GetNumErrMsg('XNP_CVU_SP_NO_ADAPTER');
876 Print(v_text,gc_ERROR, gc_CONTEXT);
877
878 PrintReportDetails(gc_HEADING, gc_ERROR, 'CODE', 'SP_TYPE', 'NAME');
879
880 v_first_time := FALSE;
881 END IF;
882
883 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.code, rec.sp_type, rec.name);
884
885 END LOOP;
886
887 EndOfTable(NOT v_first_time);
888
889 EXCEPTION
890 WHEN OTHERS THEN
891 HandleError('SERVICE_PROVIDERS', SQLCODE, SQLERRM);
892 END SERVICE_PROVIDERS;
893
894
895 -- Message Names:
896 -- XNP_CVU_NUM_RANGE_NO_GEO_ID
897 -- If the Geo Area Indicator is GEO then Number range
898 -- should have associated Geographic Area
899 -- XNP_CVU_NUM_RANGE_GEO
900 -- If the Geo Area Indicator is NOT GEO then Number range
901 -- should NOT have Geographic Area
902
903 PROCEDURE NUM_RANGE_GEO_AREA
904 IS
905 v_text VARCHAR2(1000);
906 v_first_time BOOLEAN := TRUE;
907
908 CURSOR c_geo_num_range IS
909 SELECT number_range_id, starting_number, ending_number
910 FROM xnp_number_ranges
911 WHERE geo_indicator = gc_GEO
912 AND geo_area_id IS NULL;
913
914 CURSOR c_nogeo_num_range IS
915 SELECT number_range_id, starting_number,ending_number
916 FROM xnp_number_ranges
917 WHERE geo_indicator <> gc_GEO
918 AND geo_area_id IS NOT NULL;
919
920 BEGIN
921 v_first_time := TRUE;
922 FOR rec IN c_geo_num_range
923 LOOP
924 IF v_first_time THEN
925 v_text := GetNumErrMsg('XNP_CVU_NUM_RANGE_NO_GEO_ID');
926 Print(v_text,gc_WARNING, gc_CONTEXT);
927
928 PrintReportDetails(gc_HEADING, gc_WARNING,
929 'NUMBER_RANGE_ID', 'STARTING_NUM', 'ENDING_NUM');
930
931 v_first_time := FALSE;
932 END IF;
933
934 PrintReportDetails(gc_DETAILS,gc_WARNING,
935 rec.number_range_id, rec.starting_number, rec.ending_number);
936
937 END LOOP;
938
939 EndOfTable(NOT v_first_time);
940
941 v_first_time := TRUE;
942 FOR rec IN c_nogeo_num_range
943 LOOP
944 IF v_first_time THEN
945 v_text := GetNumErrMsg('XNP_CVU_NUM_RANGE_GEO');
946 Print(v_text,gc_WARNING, gc_CONTEXT);
947
948 PrintReportDetails(gc_HEADING, gc_WARNING,
949 'NUMBER_RANGE_ID', 'STARTING_NUM', 'ENDING_NUM');
950
951 v_first_time := FALSE;
952 END IF;
953
954 PrintReportDetails(gc_DETAILS,gc_WARNING,
955 rec.number_range_id, rec.starting_number, rec.ending_number);
956
957 END LOOP;
958
959 EndOfTable(NOT v_first_time);
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 HandleError('NUM_RANGE_GEO_AREA', SQLCODE, SQLERRM);
964 END NUM_RANGE_GEO_AREA;
965
966 -- Message Names:
967 -- XNP_CVU_INVALID_NUM_RANGES
968 -- Number Range needs to be "Active" and currently Effective
969
970 PROCEDURE INVALID_NUMBER_RANGES
971 IS
972 v_text VARCHAR2(1000);
973 v_first_time BOOLEAN := TRUE;
974
975 CURSOR c_num_range IS
976 SELECT number_range_id, starting_number, ending_number,
977 to_char(effective_date, gc_DATE_FORMAT) effective_date,
978 nvl(active_flag, gc_NULL) active_flag
979 FROM xnp_number_ranges
980 WHERE effective_date > SYSDATE
981 OR nvl(active_flag,'N') <> 'Y';
982
983
984 BEGIN
985 FOR rec IN c_num_range
986 LOOP
987 IF v_first_time THEN
988 v_text := GetNumErrMsg('XNP_CVU_INVALID_NUM_RANGES');
989 Print(v_text,gc_WARNING, gc_CONTEXT);
990
991 PrintReportDetails(gc_HEADING, gc_WARNING,
992 'NUMBER_RANGE_ID', 'STARTING_NUM', 'ENDING_NUM',
993 'EFFECTIVE_DATE', 'ACTIVE_FLAG');
994
995 v_first_time := FALSE;
996 END IF;
997
998 PrintReportDetails(gc_DETAILS,gc_WARNING,
999 rec.number_range_id, rec.starting_number, rec.ending_number,
1000 rec.effective_date, rec.active_flag);
1001
1002 END LOOP;
1003
1004 EndOfTable(NOT v_first_time);
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 HandleError('INVALID_NUMBER_RANGES', SQLCODE, SQLERRM);
1009 END INVALID_NUMBER_RANGES;
1010
1011 -- Message Names:
1012 -- XNP_CVU_NOT_POOLED_NUM_RANGES
1013 -- If number range is not a pooled one, then Assigned Service provider and the
1014 -- Owning Service provider should be same
1015 -- XNP_CVU_POOLED_NUM_RANGES
1016 -- If it is a Pooled number range and Assigned Service provider should not be NULL
1017
1018 PROCEDURE POOLED_NUMBER_RANGES
1019 IS
1020 v_text VARCHAR2(1000);
1021 v_first_time BOOLEAN := TRUE;
1022
1023 CURSOR c_not_pooled_num_ranges IS
1024 SELECT number_range_id, starting_number, ending_number,
1025 assigned_sp_id, owning_sp_id, pooled_flag
1026 FROM xnp_number_ranges
1027 WHERE pooled_flag = 'N'
1028 AND assigned_sp_id <> owning_sp_id;
1029
1030 CURSOR c_pooled_num_ranges IS
1031 SELECT number_range_id, starting_number, ending_number
1032 FROM xnp_number_ranges
1033 WHERE pooled_flag = 'Y'
1034 AND assigned_sp_id IS NULL;
1035
1036 BEGIN
1037 v_first_time := TRUE;
1038 FOR rec IN c_not_pooled_num_ranges
1039 LOOP
1040 IF v_first_time THEN
1041 v_text := GetNumErrMsg('XNP_CVU_NOT_POOLED_NUM_RANGES');
1042 Print(v_text,gc_WARNING, gc_CONTEXT);
1043 PrintReportDetails(gc_HEADING, gc_WARNING,
1044 'NUMBER_RANGE_ID', 'STARTING_NUM', 'ENDING_NUM',
1045 'ASSIGNED_SP_ID', 'OWNING_SP_ID');
1046
1047 v_first_time := FALSE;
1048 END IF;
1049
1050 PrintReportDetails(gc_DETAILS,gc_WARNING,
1051 rec.number_range_id, rec.starting_number, rec.ending_number,
1052 rec.assigned_sp_id, rec.owning_sp_id);
1053
1054 END LOOP;
1055
1056 EndOfTable(NOT v_first_time);
1057
1058 v_first_time := TRUE;
1059 FOR rec IN c_pooled_num_ranges
1060 LOOP
1061 IF v_first_time THEN
1062 v_text := GetNumErrMsg('XNP_CVU_POOLED_NUM_RANGES');
1063 Print(v_text,gc_WARNING, gc_CONTEXT);
1064
1065 PrintReportDetails(gc_HEADING, gc_WARNING,
1066 'NUMBER_RANGE_ID', 'STARTING_NUM', 'ENDING_NUM');
1067
1068 v_first_time := FALSE;
1069 END IF;
1070
1071 PrintReportDetails(gc_DETAILS,gc_WARNING,
1072 rec.number_range_id, rec.starting_number, rec.ending_number);
1073
1074 END LOOP;
1075
1076 EndOfTable(NOT v_first_time);
1077
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 HandleError('POOLED_NUMBER_RANGES', SQLCODE, SQLERRM);
1081 END POOLED_NUMBER_RANGES;
1082
1083 -- Message Names:
1084 -- NON_PORT_NUM_RANGES
1085 -- If the Number Range is "Non Ported" or "Non Portable" then indicate "WARNING".
1086
1087 PROCEDURE NON_PORTED_PORTABLE_NUM_RANGES
1088 IS
1089 v_text VARCHAR2(1000);
1090 v_first_time BOOLEAN := TRUE;
1091
1092 CURSOR c_number_ranges IS
1093 SELECT number_range_id,
1094 starting_number, ending_number, ported_indicator
1095 FROM xnp_number_ranges
1096 WHERE ported_indicator NOT IN ('NON_PORTED', 'NON_PORTABLE');
1097
1098 BEGIN
1099 v_first_time := TRUE;
1100 FOR rec IN c_number_ranges
1101 LOOP
1102 IF v_first_time THEN
1103 v_text := GetNumErrMsg('XNP_CVU_NON_PORT_NUM_RANGES');
1104 Print(v_text,gc_WARNING, gc_CONTEXT);
1105
1106 PrintReportDetails(gc_HEADING, gc_WARNING,
1107 'NUMBER_RANGE_ID', 'STARTING_NUM', 'ENDING_NUM',
1108 'PORTED_INDICATOR');
1109
1110 v_first_time := FALSE;
1111 END IF;
1112
1113 PrintReportDetails(gc_DETAILS,gc_WARNING,
1114 rec.number_range_id, rec.starting_number, rec.ending_number,
1115 rec.ported_indicator);
1116
1117 END LOOP;
1118
1119 EndOfTable(NOT v_first_time);
1120
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 HandleError('NON_PORTED_PORTABLE_NUM_RANGES', SQLCODE, SQLERRM);
1124 END NON_PORTED_PORTABLE_NUM_RANGES;
1125
1126
1127 -- Message Names:
1128 -- XNP_CVU_SERVED_FE_NP_FEATURE
1129 -- Served number ranges must have fulfillment element
1130 -- associated with it.
1131
1132 PROCEDURE SERVED_NUMBER_RANGES
1133 IS
1134 v_text VARCHAR2(1000);
1135 v_first_time BOOLEAN := TRUE;
1136
1137
1138 CURSOR c_served_num_ranges_err IS
1139 SELECT snr.number_range_id, nr.starting_number, nr.ending_number
1140 FROM xnp_served_num_ranges snr, xnp_number_ranges nr
1141 WHERE NOT EXISTS
1142 (SELECT 'X'
1143 FROM xdp_fes fes
1144 WHERE fes.fe_id = snr.fe_id
1145 AND SYSDATE
1146 BETWEEN fes.valid_date and NVL(fes.invalid_date,SYSDATE)
1147 )
1148 AND nr.number_range_id = snr.number_range_id;
1149 BEGIN
1150 v_first_time := TRUE;
1151 FOR rec IN c_served_num_ranges_err
1152 LOOP
1153 IF v_first_time THEN
1154 v_text := GetNumErrMsg('XNP_CVU_SERVED_FE_NP_FEATURE');
1155 Print(v_text,gc_ERROR, gc_CONTEXT);
1156
1157 PrintReportDetails(gc_HEADING, gc_ERROR, 'NUMBER_RANGE_ID','STARTING_NUMBER',
1158 'ENDING_NUMBER');
1159
1160 v_first_time := FALSE;
1161 END IF;
1162
1163 PrintReportDetails(gc_DETAILS,gc_ERROR,
1164 rec.number_range_id, rec.starting_number, rec.ending_number);
1165
1166 END LOOP;
1167
1168 EndOfTable(NOT v_first_time);
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 HandleError('SERVED_NUMBER_RANGES', SQLCODE, SQLERRM);
1173 END SERVED_NUMBER_RANGES;
1174
1175
1176
1177 -- Message Name:
1178 -- XNP_CVU_ENABLE_NRC
1179
1180 PROCEDURE ENABLE_NRC
1181 IS
1182 v_text VARCHAR2(1000);
1183 v_first_time BOOLEAN := TRUE;
1184 v_nrc_id NUMBER := NULL;
1185 v_error_code NUMBER := NULL;
1186 v_error_message VARCHAR2(1000) := NULL;
1187
1188 CURSOR c_number_ranges IS
1189 SELECT number_range_id,
1190 starting_number, ending_number
1191 FROM xnp_number_ranges;
1192
1193
1194 BEGIN
1195 v_first_time := TRUE;
1196 FOR rec IN c_number_ranges
1197 LOOP
1198
1199 xnp_core.get_nrc_id(rec.starting_number, rec.ending_number,
1200 v_nrc_id, v_error_code, v_error_message);
1201
1202 /* Bug Fix
1203 -- v_nrc_id = NULL => Error
1204 -- v_nrc_id = 0 => NRC not found
1205
1206 IF v_nrc_id = 0 THEN
1207 */
1208 -- v_error_code = 0 => NRC Flag NOT set or NRC Found
1209 -- v_error_code <> 0 => NRC not found
1210
1211 IF v_error_code <> 0 THEN
1212 IF v_first_time THEN
1213 v_text := GetNumErrMsg('XNP_CVU_ENABLE_NRC');
1214 Print(v_text,gc_ERROR, gc_MESSAGE);
1215
1216 PrintReportDetails(gc_HEADING, gc_ERROR,
1217 'NUMBER_RANGE_ID','STARTING_NUMBER',
1218 'ENDING_NUMBER');
1219
1220 v_first_time := FALSE;
1221 END IF;
1222
1223 PrintReportDetails(gc_DETAILS,gc_ERROR,
1224 rec.number_range_id, rec.starting_number, rec.ending_number);
1225
1226 END IF;
1227
1228 END LOOP;
1229 EndOfTable(NOT v_first_time);
1230
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 HandleError('ENABLE_NRC', SQLCODE, SQLERRM);
1234 END ENABLE_NRC;
1235
1236 -- Message Names:
1237 -- XNP_CVU_INVALID_SERVICES
1238 -- If valid_date is null or invalid_date < sysdate then
1239 -- issue warninG.
1240 -- Removed after 11.5.6
1241 -- XNP_CVU_SERVICE_ACTIONS
1242 -- Service should have valid actions defined for it.
1243 -- Removed after 11.5.6
1244 -- XNP_CVU_DSERVICE_WI_MAP
1245 -- For dynamic Service Action WI Mapping procedure must be defined
1246
1247 PROCEDURE SERVICE_VALIDATIONS
1248 IS
1249 v_text VARCHAR2(1000);
1250 v_first_time BOOLEAN := TRUE;
1251 --
1252 -- remove the following validations as part of integration with inventory
1253 -- 07/19/2001
1254 --
1255 /*
1256 CURSOR c_invalid_service_warn IS
1257 SELECT service_id id , service_name name,
1258 to_char(valid_date, gc_DATE_FORMAT) valid_date,
1259 DECODE(invalid_date, NULL, gc_NULL,
1260 to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
1261 FROM xdp_services
1262 WHERE invalid_date < SYSDATE;
1263
1264 CURSOR c_service_actions IS
1265 SELECT service_id id, service_name name
1266 FROM xdp_services s
1267 WHERE NOT EXISTS
1268 (SELECT 1
1269 FROM xdp_service_val_acts sva
1270 WHERE sva.service_id = s.service_id
1271 AND SYSDATE
1272 BETWEEN sva.valid_date
1273 AND nvl(sva.invalid_date,gc_max_date)
1274 );
1275
1276 -- wi_mappping_proc validity need not be checked as there is
1277 -- foreign key constraint defined.
1278
1279 CURSOR c_dservice_wi_map_proc IS
1280 SELECT sva.service_id id, s.service_name name
1281 FROM xdp_service_val_acts sva, xdp_services s
1282 WHERE sva.service_id = s.service_id
1283 AND sva.wi_mapping_type = gc_DYNAMIC
1284 AND sva.wi_mapping_proc is NULL;
1285 */
1286 --
1287 -- After integration with inventory, the new way of validate wi_mapping_proc.
1288 -- 07/19/2001
1289
1290 CURSOR c_dservice_wi_map_proc IS
1291 SELECT mtl.inventory_item_id id
1292 , mtl.organization_id organization_id
1293 , mtl.concatenated_segments name
1294 FROM xdp_service_val_acts sva
1295 , mtl_system_items_vl mtl
1296 WHERE sva.inventory_item_id = mtl.inventory_item_id
1297 AND sva.organization_id = mtl.organization_id
1298 AND sva.wi_mapping_type = gc_DYNAMIC
1299 AND sva.wi_mapping_proc is NULL;
1300
1301 BEGIN
1302 --
1303 -- remove the following validations as part of integration with inventory
1304 -- 07/19/2001
1305 --
1306 /*
1307 v_first_time := TRUE;
1308 FOR rec IN c_invalid_service_warn
1309 LOOP
1310 IF v_first_time THEN
1311 v_text := GetNumErrMsg('XNP_CVU_INVALID_SERVICES');
1312 Print(v_text,gc_WARNING, gc_CONTEXT);
1313
1314 PrintReportDetails(gc_HEADING, gc_WARNING, 'NAME','VALID_DATE',
1315 'INVALID_DATE');
1316
1317 v_first_time := FALSE;
1318 END IF;
1319
1320 PrintReportDetails(gc_DETAILS,gc_WARNING,
1321 rec.name, rec.valid_date, rec.invalid_date);
1322
1323 END LOOP;
1324
1325 EndOfTable(NOT v_first_time);
1326
1327 v_first_time := TRUE;
1328 FOR rec IN c_service_actions
1329 LOOP
1330 IF v_first_time THEN
1331 v_text := GetNumErrMsg('XNP_CVU_SERVICE_ACTIONS');
1332 Print(v_text,gc_ERROR, gc_CONTEXT);
1333
1334 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
1335
1336 v_first_time := FALSE;
1337 END IF;
1338
1339 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
1340
1341 END LOOP;
1342
1343 EndOfTable(NOT v_first_time);
1344
1345 */
1346 v_first_time := TRUE;
1347 FOR rec IN c_dservice_WI_MAP_PROC
1348 LOOP
1349 IF v_first_time THEN
1350 v_text := GetNumErrMsg('XNP_CVU_DSERVICE_WI_MAP_PROC');
1351 Print(v_text,gc_ERROR, gc_CONTEXT);
1352
1353 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
1354
1355 v_first_time := FALSE;
1356 END IF;
1357
1358 PrintReportDetails(gc_DETAILS,gc_ERROR, rec.id, rec.name);
1359
1360 END LOOP;
1361
1362 EndOfTable(NOT v_first_time);
1363
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 HandleError('SERVICE_VALIDATIONS', SQLCODE, SQLERRM);
1367 END SERVICE_VALIDATIONS;
1368
1369 -- Message Name:
1370 -- XNP_CVU_INVALID_PACKAGES
1371 -- If valid_date is null or invalid_date < sysdate
1372 -- Removed after 11.5.6
1373 -- XNP_CVU_EMPTY_PACKAGEs
1374 -- Service package should have valid service defined for it
1375 -- Removed after 11.5.6
1376
1377 PROCEDURE PACKAGE_VALIDATIONS
1378 IS
1379 v_text VARCHAR2(1000);
1380 v_first_time BOOLEAN := TRUE;
1381
1382 --
1383 -- remove the following validations as part of integration with inventory
1384 -- 07/19/2001
1385 -- Packages now reside in BOM We have no good way to identify what BOMs are ours.
1386 -- We can add checks against our packages in BOM later on.
1387 --
1388 /*
1389 CURSOR c_invalid_package_warn IS
1390 SELECT package_id id, package_name name,
1391 to_char(valid_date, gc_DATE_FORMAT) valid_date,
1392 DECODE(invalid_date, NULL, gc_NULL,
1393 to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
1394 FROM xdp_service_packages
1395 WHERE invalid_date < SYSDATE;
1396
1397 CURSOR c_empty_packages IS
1398 SELECT package_id id, package_name name
1399 FROM xdp_service_packages sp
1400 WHERE NOT EXISTS
1401 (SELECT 1
1402 FROM xdp_service_pkg_det spd, xdp_services sv
1403 WHERE spd.package_id = spd.package_id
1404 AND spd.service_id = sv.service_id
1405 AND SYSDATE BETWEEN sv.valid_date
1406 AND nvl(sv.invalid_date,gc_max_date)
1407 );
1408 */
1409
1410 BEGIN
1411 v_first_time := TRUE;
1412 --
1413 -- remove the following validations as part of integration with inventory
1414 -- 07/19/2001
1415 -- Packages now reside in BOM We have no good way to identify what BOMs are ours.
1416 -- We can add checks against our packages in BOM later on.
1417 --
1418 /*
1419 FOR rec IN c_invalid_package_warn
1420 LOOP
1421 IF v_first_time THEN
1422 v_text := GetNumErrMsg('XNP_CVU_INVALID_PACKAGES');
1423 Print(v_text,gc_ERROR, gc_CONTEXT);
1424
1425 PrintReportDetails(gc_HEADING, gc_WARNING, gc_ID, gc_NAME,
1426 gc_VALID_DATE, gc_INVALID_DATE);
1427
1428 v_first_time := FALSE;
1429 END IF;
1430
1431 PrintReportDetails(gc_DETAILS,gc_WARNING, rec.id, rec.name,
1432 rec.valid_date, rec.invalid_date);
1433
1434 END LOOP;
1435
1436 EndOfTable(NOT v_first_time);
1437
1438 v_first_time := TRUE;
1439 FOR rec IN c_empty_packages
1440 LOOP
1441 IF v_first_time THEN
1442 v_text := GetNumErrMsg('XNP_CVU_EMPTY_PACKAGES');
1443 Print(v_text,gc_ERROR, gc_CONTEXT);
1444
1445 PrintReportDetails(gc_HEADING, gc_WARNING, gc_ID, gc_NAME);
1446
1447 v_first_time := FALSE;
1448 END IF;
1449
1450 PrintReportDetails(gc_DETAILS, gc_WARNING, rec.id, rec.name);
1451
1452 END LOOP;
1453
1454 EndOfTable(NOT v_first_time);
1455 */
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 HandleError('PACKAGE_VALIDATIONS', SQLCODE, SQLERRM);
1460 END PACKAGE_VALIDATIONS;
1461
1462
1463 -- Message Names
1464 -- XNP_CVU_VALID_WORKITEMS:
1465 -- If valid_date is null or invalid_date < sysdate
1466 -- XNP_CVU_STATIC_WI_NO_FE
1467 -- If work item is static then there should be FA configured for it.
1468
1469 PROCEDURE WORK_ITEMS
1470 IS
1471 v_text VARCHAR2(1000);
1472 v_first_time BOOLEAN := TRUE;
1473
1474 CURSOR c_valid_wi IS
1475 SELECT workitem_id id, workitem_name name,
1476 to_char(valid_date, gc_DATE_FORMAT) valid_date,
1477 DECODE(invalid_date, NULL, gc_NULL,
1478 to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
1479 FROM xdp_workitems
1480 WHERE invalid_date < SYSDATE;
1481
1482 CURSOR c_static_wi_no_fe IS
1483 SELECT workitem_id id, workitem_name name
1484 FROM xdp_workitems wi
1485 WHERE wi.wi_type_code = gc_STATIC
1486 AND NOT EXISTS
1487 (SELECT 1
1488 FROM xdp_wi_fa_mapping wfa
1489 WHERE wfa.workitem_id = wi.workitem_id);
1490
1491 BEGIN
1492 v_first_time := TRUE;
1493 FOR rec IN c_valid_wi
1494 LOOP
1495 IF v_first_time THEN
1496 v_text := GetNumErrMsg('XNP_CVU_VALID_WORKITEMS');
1497 Print(v_text,gc_ERROR, gc_CONTEXT);
1498
1499
1500 PrintReportDetails(gc_HEADING, gc_WARNING, gc_ID, gc_NAME,
1501 gc_VALID_DATE, gc_INVALID_DATE);
1502
1503 v_first_time := FALSE;
1504 END IF;
1505
1506 PrintReportDetails(gc_DETAILS, gc_WARNING, rec.id, rec.name,
1507 rec.valid_date, rec.invalid_date);
1508
1509 END LOOP;
1510
1511 EndOfTable(NOT v_first_time);
1512
1513 v_first_time := TRUE;
1514 FOR rec IN c_static_wi_no_fe
1515 LOOP
1516 IF v_first_time THEN
1517 v_text := GetNumErrMsg('XNP_CVU_STATIC_WI_NO_FE');
1518 Print(v_text,gc_ERROR, gc_CONTEXT);
1519
1520
1521 PrintReportDetails(gc_HEADING, gc_ERROR, gc_ID, gc_NAME);
1522
1523 v_first_time := FALSE;
1524 END IF;
1525
1526 PrintReportDetails(gc_DETAILS, gc_ERROR, rec.id, rec.name);
1527
1528 END LOOP;
1529
1530 EndOfTable(NOT v_first_time);
1531
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 HandleError('WORK_ITEMS', SQLCODE, SQLERRM);
1535 END WORK_ITEMS;
1536
1537 -- Message Name:
1538 -- XNP_CVU_ACTIVITY_BASED_TIMERS
1539 -- If there is a Timer associated with the event
1540 -- then it should subscribe to the same response
1541
1542 PROCEDURE ACTIVITY_BASED_TIMERS
1543 IS
1544 v_text VARCHAR2(1000);
1545 v_first_time BOOLEAN := TRUE;
1546
1547 CURSOR c_timers IS
1548 SELECT tp.timer_publisher_id, tp.source_message_code, tp.timer_message_code
1549 FROM xnp_timer_publishers tp
1550 WHERE NOT EXISTS
1551 (SELECT 1
1552 FROM xnp_msg_acks ma
1553 WHERE ma.source_msg_code = tp.source_message_code
1554 AND ma.ack_msg_code = tp.timer_message_code );
1555
1556 BEGIN
1557 v_first_time := TRUE;
1558 FOR rec IN c_timers
1559 LOOP
1560 IF v_first_time THEN
1561 v_text := GetNumErrMsg('XNP_CVU_ACTIVITY_BASED_TIMERS');
1562 Print(v_text,gc_ERROR, gc_CONTEXT);
1563
1564 PrintReportDetails(gc_HEADING, gc_ERROR, 'TIMER_PUBLISHER_ID',
1565 'SOURCE_MESSAGE_CODE', 'TIMER_MESSAGE_CODE');
1566
1567 v_first_time := FALSE;
1568 END IF;
1569
1570 PrintReportDetails(gc_DETAILS, gc_ERROR, rec.timer_publisher_id,
1571 rec.source_message_code, rec.timer_message_code);
1572
1573 END LOOP;
1574
1575 EndOfTable(NOT v_first_time);
1576
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 HandleError('ACTIVITY_BASED_TIMERS', SQLCODE, SQLERRM);
1580 END ACTIVITY_BASED_TIMERS;
1581
1582 -- Message Name:
1583 -- XNP_CVU_UNCOMPILED_ISTUDIO_MSG
1584
1585 PROCEDURE MESSAGE_COMPILATION
1586 IS
1587 v_text VARCHAR2(1000);
1588 v_first_time BOOLEAN := TRUE;
1589
1590 CURSOR c_msg_code IS
1591 SELECT msg_code
1592 FROM xnp_msg_types_b
1593 WHERE status = 'UNCOMPILED';
1594
1595 v_error_code NUMBER;
1596 v_error_msg VARCHAR2(150);
1597 v_pkg_spec VARCHAR2(150);
1598 v_pkg_body VARCHAR2(150);
1599 v_synonym VARCHAR2(150);
1600
1601 BEGIN
1602 v_first_time := TRUE;
1603 FOR rec IN c_msg_code
1604 LOOP
1605 IF v_first_time THEN
1606 v_text := GetNumErrMsg('XNP_CVU_UNCOMPILED_ISTUDIO_MSG');
1607 Print(v_text,gc_ERROR, gc_CONTEXT);
1608 PrintReportDetails(gc_HEADING, gc_ERROR, 'MSG_CODE');
1609
1610 v_first_time := FALSE;
1611 END IF;
1612
1613 PrintReportDetails(gc_DETAILS, gc_ERROR, rec.msg_code);
1614
1615 END LOOP;
1616
1617 EndOfTable(NOT v_first_time);
1618
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 HandleError('MESSAGE_COMPILATION', SQLCODE, SQLERRM);
1622 END MESSAGE_COMPILATION;
1623
1624
1625 -- Message Name:
1626 -- XNP_CVU_INALID_FP
1627 -- All Fulfillment Procedures must be VALID objects.
1628
1629 PROCEDURE INVALID_FP
1630 IS
1631 v_text VARCHAR2(1000);
1632 v_first_time BOOLEAN := TRUE;
1633
1634 CURSOR c_invalid_fp IS
1635 SELECT distinct proc_name name
1636 FROM xdp_proc_body pb, user_objects uo
1637 WHERE uo.object_name = substr(pb.proc_name, 1, (INSTR(pb.proc_name,'.')-1))
1638 AND uo.object_type in ('PACKAGE','PACKAGE BODY')
1639 AND uo.status = 'INVALID';
1640 BEGIN
1641 v_first_time := TRUE;
1642 FOR rec IN c_invalid_fp
1643 LOOP
1644 IF v_first_time THEN
1645 v_text := GetNumErrMsg('XNP_CVU_INVALID_FP');
1646 Print(v_text, gc_ERROR, gc_CONTEXT);
1647
1648 PrintReportDetails(gc_HEADING, gc_ERROR, gc_NAME);
1649
1650 v_first_time := FALSE;
1651 END IF;
1652
1653 PrintReportDetails(gc_DETAILS, gc_ERROR, rec.name);
1654
1655 END LOOP;
1656
1657 EndOfTable(NOT v_first_time);
1658
1659 EXCEPTION
1660 WHEN OTHERS THEN
1661 HandleError('INVALID_FP', SQLCODE, SQLERRM);
1662 END INVALID_FP;
1663
1664
1665 PROCEDURE CrossValidate(p_work_instance_id VARCHAR2,
1666 p_message_code VARCHAR2,
1667 p_LHS VARCHAR2,
1668 p_RHS VARCHAR2,
1669 p_error IN OUT NOCOPY BOOLEAN)
1670 IS
1671 v_lhs_msg VARCHAR2(100) default NULL;
1672 v_rhs_msg VARCHAR2(100) default NULL;
1673 v_text VARCHAR2(1000) default NULL;
1674
1675 v_trace VARCHAR2(150) default NULL;
1676 BEGIN
1677
1678 -- p_ERROR will be FALSE for the first time.
1679 -- SO it can be used to Print the Header.
1680 -- For subsequent errors Header can be avoided
1681 -- XNP_CVU_WF_LHS_NOT_VALID_ELEM -> ERROR
1682
1683 v_trace := 'XNP_CVU_WF_LHS_NOT_VALID_ELEM';
1684
1685 BEGIN
1686 SELECT 'XNP_CVU_WF_LHS_NOT_VALID_ELEM'
1687 INTO v_lhs_msg
1688 FROM dual
1689 WHERE NOT EXISTS
1690 (SELECT 1
1691 FROM xnp_msg_elements me
1692 WHERE me.name = p_LHS
1693 AND me.parameter_flag = 'Y'
1694 AND me.msg_code = p_message_code);
1695
1696 EXCEPTION
1697 WHEN NO_DATA_FOUND THEN
1698 v_lhs_msg := NULL;
1699 END;
1700
1701 -- XNP_CVU_WF_RHS_NOTVALID_PARAM -> ERROR
1702
1703 v_trace := 'XNP_CVU_WF_RHS_NOTVALID_PARAM';
1704
1705 BEGIN
1706 SELECT 'XNP_CVU_WF_RHS_NOTVALID_PARAM'
1707 INTO v_rhs_msg
1708 FROM dual
1709 WHERE NOT EXISTS
1710 (SELECT 1
1711 FROM fnd_lookups flk
1712 WHERE flk.lookup_code = p_RHS
1713 AND flk.lookup_type = 'CSI_EXTEND_ATTRIB_POOL');
1714 --
1715 -- After 11.5.6, XDP_PARAMETER_POOL will be dropped and parameters are seededin FND_LOOKUPS
1716 -- Hence the change.
1717 -- 07/19/2001
1718 /*
1719 SELECT 'XNP_CVU_WF_RHS_NOTVALID_PARAM'
1720 INTO v_rhs_msg
1721 FROM dual
1722 WHERE NOT EXISTS
1723 (SELECT 1
1724 FROM xdp_parameter_pool pol
1725 WHERE pol.parameter_name = p_RHS);
1726 */
1727 EXCEPTION
1728 WHEN NO_DATA_FOUND THEN
1729 v_rhs_msg := NULL;
1730 END;
1731
1732
1733 IF v_lhs_msg IS NOT NULL OR v_rhs_msg IS NOT NULL THEN
1734
1735 IF v_lhs_msg IS NOT NULL THEN
1736 v_text := GetNumErrMsg('XNP_CVU_WF_LHS_NOT_VALID_ELEM'
1737 , 'MSG_ELEM'
1738 , p_lhs);
1739 Print(v_text,gc_ERROR,gc_MESSAGE);
1740 p_error := TRUE;
1741 END IF;
1742
1743 IF v_rhs_msg IS NOT NULL THEN
1744 v_text := GetNumErrMsg('XNP_CVU_WF_RHS_NOTVALID_PARAM'
1745 , 'MSG_CODE'
1746 , p_rhs);
1747 Print(v_text, gc_ERROR,gc_MESSAGE);
1748 p_error := TRUE;
1749 END IF;
1750
1751 END IF;
1752
1753 EXCEPTION
1754 WHEN OTHERS THEN
1755 HandleError('CrossValidate:'||v_trace, SQLCODE, SQLERRM);
1756 RAISE;
1757 END CrossValidate;
1758
1759 -- v_param_list: LHS1=$RHS1,LHS2=$RHS2,LHS3=RHS4
1760
1761 PROCEDURE PerformCrossValidations (p_work_instance_id VARCHAR2,
1762 p_message_code VARCHAR2,
1763 p_param_list VARCHAR2,
1764 p_error IN OUT NOCOPY BOOLEAN)
1765 IS
1766
1767 v_no_equal NUMBER := 0;
1768 v_no_elements NUMBER:= 0;
1769
1770 v_current_pos NUMBER := 0;
1771 v_equal_pos NUMBER := 0;
1772 v_comma_pos NUMBER := 0;
1773
1774 v_LHS VARCHAR2(30) := NULL;
1775 v_RHS VARCHAR2(30) := NULL;
1776
1777 v_all_processed BOOLEAN := FALSE;
1778
1779 v_text VARCHAR2(1000);
1780 v_no_such_evt VARCHAR2(150);
1781 BEGIN
1782
1783 BEGIN
1784 SELECT 'XNP_CVU_WF_NOT_VALID_EVT_TYPE'
1785 INTO v_no_such_evt
1786 FROM dual
1787 WHERE NOT EXISTS
1788 (SELECT 1
1789 FROM xnp_msg_types_b me
1790 WHERE me.msg_code = p_message_code);
1791
1792 IF v_no_such_evt IS NOT NULL THEN
1793 v_text := GetNumErrMsg('XNP_CVU_WF_NOT_VALID_EVT_TYPE',
1794 'MSG_CODE', p_message_code);
1795 Print(v_text ,gc_ERROR ,gc_MESSAGE);
1796 p_error := TRUE;
1797 END IF;
1798
1799
1800 EXCEPTION
1801 WHEN NO_DATA_FOUND THEN
1802 v_no_such_evt := NULL;
1803 END;
1804
1805 IF p_param_list IS NULL THEN
1806 /* This condition is not an error */
1807 null;
1808 ELSE
1809 v_no_equal := 0;
1810 v_current_pos := 1;
1811 v_all_processed := FALSE;
1812
1813 WHILE NOT v_all_processed
1814 LOOP
1815
1816 v_equal_pos := INSTR(p_param_list, '=',v_current_pos, 1);
1817
1818 IF v_equal_pos = 0 THEN -- Error Data
1819 v_LHS := SUBSTR(p_param_list, v_current_pos, LENGTH(p_param_list));
1820 v_RHS := NULL;
1821
1822 v_all_processed := TRUE;
1823 ELSE
1824 v_no_equal := v_no_equal + 1;
1825
1826 v_comma_pos := INSTR(p_param_list, ',',v_current_pos, 1);
1827
1828 IF v_comma_pos = 0 THEN
1829 v_comma_pos := LENGTH(p_param_list) + 1; -- would be position
1830 v_all_processed:= TRUE;
1831 END IF;
1832
1833 v_LHS := SUBSTR(p_param_list,v_current_pos,
1834 v_equal_pos - v_current_pos); -- Till '='
1835 v_RHS := SUBSTR(p_param_list,v_equal_pos + 2 ,
1836 v_comma_pos - v_equal_pos - 2); -- skip '=$' and ','
1837
1838 v_current_pos := v_comma_pos + 1;
1839 END IF;
1840
1841 CrossValidate(p_work_instance_id,
1842 p_message_code,
1843 v_LHS,
1844 v_RHS,
1845 p_error);
1846 END LOOP;
1847
1848 -- XNP_CVU_WF_PAIRS_GT_ELEM -> ERROR
1849 -- XNP_CVU_WF_PAIRS_LT_ELEM -> WARNING
1850
1851 SELECT count(*)
1852 INTO v_no_elements
1853 FROM xnp_msg_elements me
1854 WHERE me.parameter_flag = 'Y'
1855 AND me.msg_code = p_message_code;
1856
1857 IF v_no_equal > v_no_elements THEN
1858 v_text := GetNumErrMsg('XNP_CVU_WF_PAIRS_GT_ELEM', v_no_equal, v_no_elements);
1859 Print(v_text,gc_ERROR,gc_MESSAGE);
1860 p_error := TRUE;
1861 ELSIF v_no_equal < v_no_elements THEN
1862 v_text := GetNumErrMsg('XNP_CVU_WF_PAIRS_LT_ELEM', v_no_equal, v_no_elements);
1863 Print(v_text,gc_WARNING,gc_MESSAGE);
1864 p_error := TRUE;
1865 END IF;
1866 END IF;
1867
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 XNP_CVU_PKG.HandleError('PerformCrossValidations', SQLCODE, SQLERRM);
1871
1872 RAISE;
1873 END PerformCrossValidations;
1874
1875
1876 -- Calls: PerformCrossValidations -> CrossValidate
1877 -- Message Name:
1878 -- XNP_CVU_WF_LHS_NOT_VALID_ELEM
1879 -- Check that <<LHS>> is a valid parameter type element in the iMessage Studio
1880 -- XNP_CVU_WF_RHS_NOTVALID_PARAM
1881 -- Check that <<RHS>> is valid parameter name form SFM parameter pool
1882 -- XNP_CVU_WF_PAIRS_GT_ELEM (#EQUALS, #ELEMENTS)
1883 -- XNP_CVU_WF_PAIRS_LT_ELEM (#EQUALS, #ELEMENTS)
1884 -- <<No of parameter pairs>> defined for <<message code>> in the work flow
1885 -- is equal to the number of parameter type elements for the corresponding
1886 -- message defined in the iMessage Studio. If it is more indicate "ERROR",
1887 -- if it is less indicate "WARNING".
1888 -- XNP_CVU_WF_NULL_PARAM
1889 -- Parameter string is NULL.
1890
1891 -- Assumption
1892 -- Due to Order by clause, the query is expected to return the records in the
1893 -- following way. Cae IV and similar are NOT handled by the program.
1894 -- Instance_id Attribute
1895 -- CASE I
1896 -- 1 EVENT_TYPE
1897 -- CASE II
1898 -- 2 PARAM_LIST
1899 -- CASE III
1900 -- 3 EVENT_TYPE
1901 -- 3 PARAM_LIST
1902 -- CASE IV
1903 -- 4 EVENT_TYPE
1904 -- 4 EVENT_TYPE
1905 -- 4 PARAM_LIST
1906
1907 PROCEDURE WF_PP_MSG_CROSS_VALIDATION IS
1908
1909 CURSOR c_wi_processes
1910 IS
1911 SELECT pat.process_name process_name,
1912 pat.process_item_type process_item_type,
1913 max(pat.process_version) process_version
1914 FROM wf_process_activities pat, xdp_workitems wi
1915 WHERE
1916 -- xdp_workitem - process
1917 pat.process_name = wi.user_wf_process_name
1918 AND pat.process_item_type = wi.user_wf_item_type
1919 -- Only work flow work items
1920 AND wi.wi_type_code = gc_WORKFLOW
1921 GROUP BY pat.process_item_type, pat.process_name;
1922
1923 CURSOR c_wf (p_process_name varchar2,
1924 p_process_item_type varchar2,
1925 p_process_version number) IS
1926 SELECT pat.instance_id, pat.process_name,
1927 pat.process_item_type, pat.process_version,
1928 atv.name attribute_name,
1929 atv.text_value attribute_value, pat.activity_name
1930 FROM wf_process_activities pat, wf_activities ac, wf_activity_attr_values atv
1931 WHERE
1932 -- highest version for the process
1933 pat.process_name = p_process_name
1934 AND pat.process_item_type = p_process_item_type
1935 AND pat.process_version = p_process_version
1936 -- process to activites
1937 AND ac.name = pat.activity_name
1938 AND ac.item_type = pat.activity_item_type
1939
1940 -- should consider only the max version AND ac.version = pat.process_version
1941 -- alternatively less cleaner but easier approach is to
1942 -- pick up the record with end_date is null
1943 -- AND ac.version = pat.process_version
1944 AND ac.end_date IS NULL
1945 --<
1946 AND pat.instance_id = atv.process_activity_id
1947 -- Only for PUBLISH_EVENT and SEND_MESSAGE functions
1948 AND ac.function IN ('XNP_WF_STANDARD.PUBLISH_EVENT',
1949 'XNP_WF_STANDARD.SEND_MESSAGE')
1950 -- Parameter attributes
1951 AND atv.name IN (gc_PARAM_LIST, gc_EVENT_TYPE)
1952 ORDER BY pat.instance_id
1953 , pat.process_name
1954 , pat.process_item_type
1955 , pat.process_version
1956 , DECODE(atv.name, gc_EVENT_TYPE, 1, 2)
1957 , atv.text_value;
1958
1959 v_work_instance_id wf_process_activities.instance_id%TYPE := NULL;
1960 v_work_process_name wf_process_activities.process_name%TYPE := NULL;
1961 v_work_activity_name wf_process_activities.activity_name%TYPE := NULL;
1962 v_work_process_item_type wf_process_activities.process_item_type%TYPE := NULL;
1963 v_work_process_version wf_process_activities.process_version%TYPE := NULL;
1964
1965 v_work_attribute_name wf_activity_attr_values.name%TYPE := NULL;
1966 v_work_attribute_value wf_activity_attr_values.text_value%TYPE := NULL;
1967
1968
1969 v_message_code VARCHAR2(150) := NULL;
1970 v_param_list VARCHAR2(150) := NULL;
1971 v_first_time BOOLEAN;
1972 v_error BOOLEAN;
1973 BEGIN
1974
1975 v_first_time := TRUE;
1976
1977
1978 FOR processes_rec IN c_wi_processes
1979 LOOP
1980 FOR rec IN c_wf(processes_rec.process_name,
1981 processes_rec.process_item_type,
1982 processes_rec.process_version)
1983 LOOP
1984
1985 -- Displays the key for the business rule
1986 -- displayed from the earlier iteration
1987
1988 IF v_error THEN
1989 PrintReportDetails(gc_HEADING, gc_ERROR,
1990 'PROCESS_ITEM_TYPE',
1991 'PROCESS_NAME',
1992 'ACTIVITY_NAME',
1993 'ATTRIBUTE_NAME',
1994 'ATTRIBUTE_VALUE');
1995 PrintReportDetails(gc_DETAILS, gc_ERROR,
1996 v_work_process_item_type,
1997 v_work_process_name,
1998 v_work_activity_name,
1999 v_work_attribute_name,
2000 v_work_attribute_value);
2001 EndOFTable(True);
2002 v_error := FALSE;
2003
2004 END IF;
2005
2006 -- New Process
2007 IF rec.instance_id <> v_work_instance_id OR
2008 v_work_instance_id IS NULL
2009 THEN
2010 -- If only MESSAGE_CODE is defined and NO PARAM_LIST defined
2011 -- then Validate before starting the NEW Process
2012 -- CASE I
2013 IF v_message_code IS NOT NULL THEN
2014
2015 PerformCrossValidations(v_work_instance_id,
2016 v_message_code,
2017 replace(v_param_list,' ',''),
2018 v_error);
2019
2020 v_message_code := NULL;
2021 v_param_list := NULL;
2022 END IF;
2023
2024 --- Store Work Process Key
2025 v_work_instance_id := rec.instance_id;
2026 v_work_process_name := rec.process_name;
2027 v_work_process_item_type := rec.process_item_type;
2028 v_work_process_version := rec.process_version;
2029 v_work_activity_name := rec.activity_name;
2030
2031 v_work_attribute_name := rec.attribute_name;
2032 v_work_attribute_value := rec.attribute_value;
2033
2034 --- Get MESSAGE CODE
2035 IF rec.attribute_name = gc_EVENT_TYPE THEN
2036 v_message_code := rec.attribute_value;
2037 END IF;
2038
2039
2040 -- Get PARAM LIST in the next iteration
2041
2042 IF rec.attribute_name = gc_PARAM_LIST THEN
2043
2044 -- At this step it means that
2045 -- PARAM_LIST is defined but MESSAGE_CODE is not defined
2046
2047 -- CASE III
2048
2049 v_param_list := rec.attribute_value;
2050
2051 PerformCrossValidations(v_work_instance_id,
2052 v_message_code,
2053 replace(v_param_list,' ',''),
2054 v_error);
2055
2056 v_message_code := NULL;
2057 v_param_list := NULL;
2058 END IF;
2059 -- Second iteration for the same process
2060 ELSE
2061 IF rec.attribute_name = gc_PARAM_LIST THEN
2062
2063 -- At this step it means that
2064 -- PARAM_LIST and MESSAGE_CODE are both defined
2065
2066 v_param_list := rec.attribute_value;
2067
2068 PerformCrossValidations(v_work_instance_id,
2069 v_message_code,
2070 replace(v_param_list,' ',''),
2071 v_error);
2072
2073 v_message_code := NULL;
2074 v_param_list := NULL;
2075
2076 ELSE
2077 -- CASE IV
2078 -- Program should never enter here
2079 Print('WF_PP_MSG_CROSS_VALIDATION'|| '**CASE IV**');
2080 END IF;
2081 END IF;
2082 END LOOP;
2083
2084 END LOOP;
2085
2086 IF v_error THEN
2087 PrintReportDetails(gc_HEADING, gc_ERROR,
2088 'PROCESS_ITEM_TYPE',
2089 'PROCESS_NAME',
2090 'ACTIVITY_NAME',
2091 'ATTRIBUTE_NAME',
2092 'ATTRIBUTE_VALUE');
2093 PrintReportDetails(gc_DETAILS, gc_ERROR,
2094 v_work_process_item_type,
2095 v_work_process_name,
2096 v_work_activity_name,
2097 v_work_attribute_name,
2098 v_work_attribute_value);
2099 EndOFTable(True);
2100 v_error := FALSE;
2101
2102 END IF;
2103
2104 EXCEPTION
2105 WHEN OTHERS THEN
2106 -- dbms_output.put_line(' Exception block ');
2107 XNP_CVU_PKG.HandleError('WF_PP_MSG_CROSS_VALIDATION', SQLCODE, SQLERRM);
2108 END WF_PP_MSG_CROSS_VALIDATION;
2109
2110 -- Message Name:
2111 -- XNP_CVU_WF_NO_RQD_ITEM_ATT
2112 -- For user defined work flows, ORDER_ID, WORKITEM_INSTANCE_ID,
2113 -- LINE_ITEM_IDitem attributes must be defined
2114
2115 PROCEDURE WF_NO_RQD_ITEM_ATT IS
2116
2117 CURSOR c_no_special_ia IS
2118
2119 SELECT DISTINCT wi.user_wf_item_type item_type , workitem_name
2120 FROM xdp_workitems wi
2121 WHERE wi.wi_type_code = 'WORKFLOW'
2122 AND wi.user_wf_item_type IS NOT NULL
2123 AND ( NOT EXISTS
2124 (SELECT 1
2125 FROM wf_item_attributes ia
2126 WHERE ia.name = 'ORDER_ID'
2127 AND ia.item_type = wi.user_wf_item_type
2128 )
2129 OR NOT EXISTS
2130 (SELECT 1
2131 FROM wf_item_attributes ia
2132 WHERE ia.name = 'WORKITEM_INSTANCE_ID'
2133 AND ia.item_type = wi.user_wf_item_type
2134 )
2135 OR NOT EXISTS
2136 (SELECT 1
2137 FROM wf_item_attributes ia
2138 WHERE ia.name = 'LINE_ITEM_ID'
2139 AND ia.item_type = wi.user_wf_item_type
2140 )
2141 );
2142
2143 v_first_time BOOLEAN := TRUE;
2144 v_text VARCHAR2(1000) := NULL;
2145 BEGIN
2146
2147 v_first_time := TRUE;
2148 FOR rec IN c_no_special_ia
2149 LOOP
2150
2151 IF v_first_time = TRUE THEN
2152 v_text := GetNumErrMsg('XNP_CVU_WF_NO_RQD_ITEM_ATT');
2153 Print(v_text, gc_OK, gc_CONTEXT);
2154
2155 PrintReportDetails(gc_HEADING, gc_ERROR, 'ITEM_TYPE', 'WORKITEM_NAME');
2156 v_first_time := FALSE;
2157 END IF;
2158
2159 PrintReportDetails(gc_DETAILS, gc_ERROR, rec.item_type, rec.workitem_name);
2160
2161 END LOOP;
2162
2163 EndOfTable(NOT v_first_time);
2164
2165 EXCEPTION
2166 WHEN OTHERS THEN
2167 XNP_CVU_PKG.HandleError('WF_NO_RQD_ITEM_ATT', SQLCODE, SQLERRM);
2168 END WF_NO_RQD_ITEM_ATT;
2169
2170
2171 FUNCTION ActivityExists(p_act_name VARCHAR2,
2172 p_process_name varchar2,
2173 p_process_item_type varchar2,
2174 p_process_version number)
2175 RETURN NUMBER
2176 IS
2177 v_count NUMBER := 0;
2178 BEGIN
2179 SELECT COUNT(*)
2180 INTO v_count
2181 FROM wf_process_activities pat
2182 WHERE pat.process_name = p_process_name
2183 AND pat.process_item_type = p_process_item_type
2184 AND pat.process_version = p_process_version
2185 AND pat.activity_name = p_act_name;
2186
2187 return v_count;
2188
2189 EXCEPTION
2190 WHEN OTHERS THEN
2191 XNP_CVU_PKG.HandleError('ActivityExists', SQLCODE, SQLERRM);
2192 RAISE;
2193 END ActivityExists;
2194
2195
2196 -- XNP_CVU_EXECUTE_FA_FA_NAME
2197 -- XNP_CVU_EXECUTE_FA_FE_NAME
2198
2199 PROCEDURE CheckAttrValues(p_instance_id NUMBER,
2200 p_text1 IN OUT NOCOPY VARCHAR2,
2201 p_text2 IN OUT NOCOPY VARCHAR2)
2202 IS
2203 v_text_value VARCHAR2(1000);
2204 v_count NUMBER := 0;
2205 BEGIN
2206
2207 -- XNP_CVU_EXECUTE_FA_FA_NAME
2208 BEGIN
2209 SELECT text_value
2210 INTO v_text_value
2211 FROM wf_activity_attr_values
2212 WHERE process_activity_id = p_instance_id
2213 AND NAME = gc_FA_NAME;
2214
2215 IF v_text_value IS NULL
2216 THEN
2217 p_text1 := 'XNP_CVU_EXECUTE_FA_FA_NAME';
2218 ELSE
2219 SELECT count(*)
2220 INTO v_count
2221 FROM xdp_fulfill_actions
2222 WHERE fulfillment_action = v_text_value;
2223
2224 IF v_count = 0 THEN
2225 p_text1 := 'XNP_CVU_EXECUTE_FA_FA_NAME';
2226 END IF;
2227 END IF;
2228
2229
2230 EXCEPTION
2231 WHEN NO_DATA_FOUND THEN
2232 p_text1 := 'XNP_CVU_EXECUTE_FA_FA_NAME';
2233 WHEN OTHERS THEN
2234 raise;
2235 END;
2236
2237 -- XNP_CVU_EXECUTE_FA_FE_NAME
2238 BEGIN
2239 SELECT text_value
2240 INTO v_text_value
2241 FROM wf_activity_attr_values
2242 WHERE process_activity_id = p_instance_id
2243 AND NAME = gc_FE_NAME;
2244
2245 IF v_text_value IS NOT NULL
2246 THEN
2247 SELECT count(*)
2248 INTO v_count
2249 FROM xdp_fes
2250 WHERE fulfillment_element_name = v_text_value;
2251
2252 IF v_count = 0 THEN
2253 p_text2 := 'XNP_CVU_EXECUTE_FA_FE_NAME';
2254 END IF;
2255 END IF;
2256
2257 EXCEPTION
2258 WHEN NO_DATA_FOUND THEN
2259 null;
2260 WHEN OTHERS THEN
2261 raise;
2262 END;
2263
2264 EXCEPTION
2265 WHEN OTHERS THEN
2266 XNP_CVU_PKG.HandleError('CheckAttrValues', SQLCODE, SQLERRM);
2267 RAISE;
2268 END CheckAttrValues;
2269
2270
2271 -- Message Name:
2272 -- XNP_CVU_WF_NO_COMP_WI_USTATUS
2273 -- User defined Work Flow should have the activity "COMPLETE_WI_UPDATE_STATUS"
2274 -- in all the end paths of the process.
2275 -- XNP_CVU_WF_PROVISION_FE
2276 -- For user defined workflows, If activity "PROVISON_FE" is defined
2277 -- then "CREATE_SMS_PORTING_RECORD" and "WAIT_FOR_RESPONSE" must be defined.
2278 -- XNP_CVU_WF_DEPROVISION_FE
2279 -- For user defined workflows, If activity "PROVISON_FE" is defined
2280 -- then "CREATE_SMS_PORTING_RECORD" and "WAIT_FOR_RESPONSE" must be defined.
2281 -- XNP_CVU_WF_FIRE_REMOVE
2282 -- For user defined workflows, If activity "Fire Timer" is defined
2283 -- then "Remove Timer" must be defined
2284 -- XNP_CVU_EXEUTE_FA
2285 -- IF EXECUTE_FA activity is defined then following must be true:
2286 -- XNP_CVU_EXECUTE_FA_FA_NAME
2287 -- FA_NAME must be defined and should be NOT NULL and Valid FA.
2288 -- XNP_CVU_EXECUTE_FA_FE_NAME
2289 -- FE_NAME must be valid if defined.
2290
2291 PROCEDURE WF_SPECIAL_ACTIVITIES
2292 IS
2293 CURSOR c_wi_processes
2294 IS
2295 SELECT pat.process_item_type, pat.process_name, max(pat.process_version) process_version
2296 FROM wf_process_activities pat, xdp_workitems wi
2297 WHERE
2298 -- xdp_workitem - process
2299 pat.process_name = wi.user_wf_process_name
2300 AND pat.process_item_type = wi.user_wf_item_type
2301 -- Only work flow work items
2302 AND wi.wi_type_code = gc_WORKFLOW
2303 GROUP BY pat.process_item_type, pat.process_name;
2304
2305
2306 -- Used for CVU_EXECUTE_FA validation
2307 -- Limitation: Wouldn't work if there is same activity from different item_types in the same
2308 -- process. Unlikely but possible scenario.
2309
2310 CURSOR c_wf_activities ( p_process_name varchar2,
2311 p_process_item_type varchar2,
2312 p_process_version number)
2313 IS
2314 SELECT pat.instance_id
2315 FROM wf_process_activities pat
2316 WHERE pat.process_name = p_process_name
2317 AND pat.process_item_type = p_process_item_type
2318 AND pat.process_version = p_process_version
2319 AND pat.activity_name = gc_EXECUTE_FA;
2320
2321 v_first_time BOOLEAN := TRUE;
2322
2323 v_text VARCHAR2(1000) := NULL;
2324 v_err_text1 VARCHAR2(150) := NULL;
2325 v_err_text2 VARCHAR2(150) := NULL;
2326
2327 v_comp_wi_upd_stat_cnt NUMBER := 0;
2328 v_end_cnt NUMBER := 0;
2329 v_provision_fe_cnt NUMBER := 0;
2330 v_deprovision_fe_cnt NUMBER := 0;
2331 v_fire_cnt NUMBER := 0;
2332 v_remove_cnt NUMBER := 0;
2333
2334 BEGIN
2335
2336
2337 -- XNP_CVU_WF_NO_COMP_WI_USTATUS
2338 v_first_time := TRUE;
2339 FOR rec IN c_wi_processes
2340 LOOP
2341 -- count the no. of COMP WI update stat and END activities
2342 -- The counts should match for each process
2343 v_comp_wi_upd_stat_cnt :=
2344 ActivityExists(gc_COMPLETE_WI_UPDATE_STATUS,
2345 rec.process_name,
2346 rec.process_item_type,
2347 rec.process_version);
2348 v_end_cnt :=
2349 ActivityExists(gc_XDP_END,
2350 rec.process_name,
2351 rec.process_item_type,
2352 rec.process_version) +
2353 ActivityExists(gc_END,
2354 rec.process_name,
2355 rec.process_item_type,
2356 rec.process_version)
2357 ;
2358 -- Sometimes for performance, XDP_END is used instead of END as END does additional checks
2359 -- so check is made on count of END or XDP_END.
2360 IF ((v_comp_wi_upd_stat_cnt - v_end_cnt) <> 0)
2361 THEN
2362 IF v_first_time THEN
2363 v_text := GetNumErrMsg('XNP_CVU_WF_NO_COMP_WI_USTATUS');
2364 Print(v_text,gc_ERROR, gc_CONTEXT);
2365
2366 PrintReportDetails(gc_HEADING, gc_ERROR,
2367 'PROCESS_ITEM_TYPE', 'PROCESS_NAME', 'PROCESS_VERSION');
2368 v_first_time := FALSE;
2369 END IF;
2370
2371 PrintReportDetails(gc_DETAILS, gc_ERROR,
2372 rec.process_item_type, rec.process_name, rec.process_version);
2373 END IF;
2374
2375 v_comp_wi_upd_stat_cnt := 0;
2376 v_end_cnt := 0;
2377
2378 END LOOP;
2379
2380 EndOfTable(NOT v_first_time);
2381 -- Message Names:
2382 -- XNP_CVU_EXEUTE_FA - Header Message
2383 -- XNP_CVU_EXECUTE_FA_FA_NAME
2384 -- XNP_CVU_EXECUTE_FA_FE_NAME
2385
2386 v_first_time := TRUE;
2387 FOR rec IN c_wi_processes
2388 LOOP
2389
2390
2391 FOR ac_rec IN c_wf_activities( rec.process_name,
2392 rec.process_item_type,
2393 rec.process_version)
2394 LOOP
2395
2396 CheckAttrValues(ac_rec.instance_id, v_err_text1, v_err_text2);
2397
2398 IF v_err_text1 IS NOT NULL OR v_err_text2 IS NOT NULL
2399 THEN
2400
2401 IF v_first_time THEN
2402 v_text := GetNumErrMsg('XNP_CVU_EXECUTE_FA');
2403 Print(v_text,gc_ERROR, gc_MESSAGE);
2404 PrintReportDetails(gc_HEADING, gc_ERROR,
2405 'PROCESS_ITEM_TYPE', 'PROCESS_NAME', 'PROCESS_VERSION',
2406 'INSTANCE_ID',
2407 'ERROR');
2408 v_first_time := FALSE;
2409 END IF;
2410
2411 IF v_err_text1 IS NOT NULL THEN
2412 PrintReportDetails(gc_DETAILS, gc_ERROR,
2413 rec.process_item_type, rec.process_name, rec.process_version,
2414 ac_rec.instance_id,
2415 v_err_text1);
2416 END IF;
2417
2418 IF v_err_text2 IS NOT NULL THEN
2419 PrintReportDetails(gc_DETAILS, gc_ERROR,
2420 rec.process_item_type, rec.process_name, rec.process_version,
2421 ac_rec.instance_id,
2422 v_err_text2);
2423 END IF;
2424
2425 END IF;
2426 END LOOP;
2427 END LOOP;
2428 EndOfTable(NOT v_first_time);
2429
2430 -- XNP_CVU_WF_PROVISION_FE
2431 v_first_time := TRUE;
2432 FOR rec IN c_wi_processes
2433 LOOP
2434 v_provision_fe_cnt := ActivityExists(gc_PROVISION_FE,
2435 rec.process_name,
2436 rec.process_item_type,
2437 rec.process_version) ;
2438 IF v_provision_fe_cnt > 0
2439 AND
2440 (ActivityExists(gc_CREATE_SMS_PORTING_RECORD,
2441 rec.process_name,
2442 rec.process_item_type,
2443 rec.process_version) = 0
2444 OR
2445 ActivityExists(gc_WAITFORFLOW,
2446 rec.process_name,
2447 rec.process_item_type,
2448 rec.process_version) <> v_provision_fe_cnt)
2449 THEN
2450 IF v_first_time THEN
2451 v_text := GetNumErrMsg('XNP_CVU_WF_PROVISION_FE');
2452 Print(v_text,gc_ERROR, gc_CONTEXT);
2453 PrintReportDetails(gc_HEADING, gc_ERROR,
2454 'PROCESS_ITEM_TYPE', 'PROCESS_NAME', 'PROCESS_VERSION');
2455
2456 v_first_time := FALSE;
2457 END IF;
2458
2459 PrintReportDetails(gc_DETAILS, gc_ERROR,
2460 rec.process_item_type, rec.process_name, rec.process_version);
2461 END IF;
2462 v_provision_fe_cnt := 0;
2463 END LOOP;
2464 EndOfTable(NOT v_first_time);
2465
2466 -- XNP_CVU_WF_DEPROVISION_FE
2467 v_first_time := TRUE;
2468 FOR rec IN c_wi_processes
2469 LOOP
2470 v_deprovision_fe_cnt := ActivityExists(gc_DEPROVISION_FE,
2471 rec.process_name,
2472 rec.process_item_type,
2473 rec.process_version) ;
2474 IF v_deprovision_fe_cnt > 0
2475 AND
2476 ActivityExists(gc_WAITFORFLOW,
2477 rec.process_name,
2478 rec.process_item_type,
2479 rec.process_version) <> v_deprovision_fe_cnt
2480 THEN
2481 IF v_first_time THEN
2482 v_text := GetNumErrMsg('XNP_CVU_WF_DEPROVISION_FE');
2483 Print(v_text, gc_CONTEXT, gc_CONTEXT);
2484 PrintReportDetails(gc_HEADING, gc_ERROR,
2485 'PROCESS_ITEM_TYPE', 'PROCESS_NAME', 'PROCESS_VERSION');
2486
2487 v_first_time := FALSE;
2488 END IF;
2489
2490 PrintReportDetails(gc_DETAILS, gc_ERROR,
2491 rec.process_item_type, rec.process_name, rec.process_version);
2492 END IF;
2493 v_deprovision_fe_cnt := 0;
2494 END LOOP;
2495 EndOfTable(NOT v_first_time);
2496
2497 -- XNP_CVU_WF_FIRE_REMOVE
2498 v_first_time := TRUE;
2499 FOR rec IN c_wi_processes
2500 LOOP
2501 v_fire_cnt := ActivityExists(gc_FIRE,
2502 rec.process_name,
2503 rec.process_item_type,
2504 rec.process_version);
2505
2506 v_remove_cnt := ActivityExists(gc_REMOVE,
2507 rec.process_name,
2508 rec.process_item_type,
2509 rec.process_version);
2510
2511 IF (v_fire_cnt > v_remove_cnt)
2512 THEN
2513 IF v_first_time THEN
2514 v_text := GetNumErrMsg('XNP_CVU_WF_FIRE_REMOVE');
2515 Print(v_text,gc_WARNING,gc_CONTEXT);
2516 PrintReportDetails(gc_HEADING, gc_ERROR,
2517 'PROCESS_ITEM_TYPE', 'PROCESS_NAME', 'PROCESS_VERSION');
2518
2519 v_first_time := FALSE;
2520 END IF;
2521 PrintReportDetails(gc_DETAILS, gc_ERROR,
2522 rec.process_item_type, rec.process_name, rec.process_version);
2523 END IF;
2524 v_deprovision_fe_cnt := 0;
2525 END LOOP;
2526 EndOfTable(NOT v_first_time);
2527
2528 EXCEPTION
2529 WHEN OTHERS THEN
2530 XNP_CVU_PKG.HandleError('WF_SPECIAL_ACTIVITIES', SQLCODE, SQLERRM);
2531 END WF_SPECIAL_ACTIVITIES;
2532
2533
2534 -- MESSABE NAMES:
2535 -- XNP_CVU_SUBSCRIBE_TO_BUS_EVTS
2536 -- Workflow activity outcome value must be same
2537 -- as the event that is being submitted to
2538
2539 PROCEDURE SUBSCRIBE_TO_BUSINESS_EVENTS
2540 IS
2541 CURSOR c_wi_processes
2542 IS
2543 SELECT pat.process_name process_name,
2544 pat.process_item_type process_item_type,
2545 max(pat.process_version) process_version
2546 FROM wf_process_activities pat, xdp_workitems wi
2547 WHERE
2548 -- xdp_workitem - process
2549 pat.process_name = wi.user_wf_process_name
2550 AND pat.process_item_type = wi.user_wf_item_type
2551 -- Only work flow work items
2552 AND wi.wi_type_code = gc_WORKFLOW
2553 GROUP BY pat.process_item_type, pat.process_name;
2554
2555
2556 CURSOR c_activities(p_process_name varchar2,
2557 p_process_item_type varchar2,
2558 p_process_version number)
2559 IS
2560
2561 SELECT pa.instance_id, pa.process_item_type,
2562 pa.process_name, pa.process_version, ats.result_code
2563 FROM wf_process_activities pa,
2564 wf_activity_transitions ats
2565 WHERE
2566 -- process_activities
2567 pa.process_name = p_process_name
2568 AND pa.process_item_type = p_process_item_type
2569 AND pa.process_version = p_process_version
2570 -- process_activities -> activity_transitions
2571 AND ats.from_process_activity = pa.instance_id
2572 -- filters
2573 AND pa.activity_name = gc_SUBSCRIBE_TO_BUSS_EVTS
2574 AND ats.result_code <> '*'
2575 AND NOT EXISTS
2576 (
2577 SELECT 1
2578 FROM wf_activity_attr_values ack_aav
2579 WHERE ack_aav.process_activity_id = pa.instance_id
2580 AND ack_aav.name = gc_EVENT_TYPE
2581 AND ack_aav.text_value = ats.result_code)
2582 ORDER BY pa.process_item_type, pa.process_name, pa.process_version;
2583
2584 v_first_time BOOLEAN := TRUE;
2585 v_text VARCHAR2(1000) := NULL;
2586
2587 BEGIN
2588
2589 v_first_time := TRUE;
2590
2591 FOR processes_rec IN c_wi_processes
2592 LOOP
2593 FOR rec IN c_activities(processes_rec.process_name,
2594 processes_rec.process_item_type,
2595 processes_rec.process_version)
2596 LOOP
2597 IF v_first_time THEN
2598 v_text := GetNumErrMsg('XNP_CVU_SUBSCRIBE_TO_BUS_EVTS');
2599 Print(v_text,gc_ERROR, gc_CONTEXT);
2600
2601 PrintReportDetails(gc_HEADING, gc_ERROR,
2602 'INSTANCE_ID', 'PROCESS_ITEM_TYPE',
2603 'PROCESS_NAME', 'PROCESS_VERSION', 'RESULT_CODE');
2604 v_first_time := FALSE;
2605 END IF;
2606
2607 PrintReportDetails(gc_DETAILS, gc_ERROR,
2608 rec.instance_id, rec.process_item_type,
2609 rec.process_name, rec.process_version, rec.result_code);
2610
2611 END LOOP;
2612 END LOOP;
2613
2614 EndOfTable(NOT v_first_time);
2615
2616 EXCEPTION
2617 WHEN OTHERS THEN
2618 XNP_CVU_PKG.HandleError('SUBSCRIBE_TO_BUSINESS_EVENTS', SQLCODE, SQLERRM);
2619 END SUBSCRIBE_TO_BUSINESS_EVENTS;
2620
2621
2622 -- MESSABE NAMES:
2623 -- XNP_CVU_SUBSCRIBE_TO_ACKS
2624 -- All the activity outcomes must the subset of the
2625 -- susbcribed events or messages or timers
2626
2627 PROCEDURE SUBSCRIBE_TO_ACKS
2628 IS
2629 CURSOR c_wi_processes
2630 IS
2631 SELECT pat.process_name process_name,
2632 pat.process_item_type process_item_type,
2633 max(pat.process_version) process_version
2634 FROM wf_process_activities pat, xdp_workitems wi
2635 WHERE
2636 -- xdp_workitem - process
2637 pat.process_name = wi.user_wf_process_name
2638 AND pat.process_item_type = wi.user_wf_item_type
2639 -- Only work flow work items
2640 AND wi.wi_type_code = gc_WORKFLOW
2641 GROUP BY pat.process_item_type, pat.process_name;
2642
2643
2644 CURSOR c_activities(p_process_name varchar2,
2645 p_process_item_type varchar2,
2646 p_process_version number)
2647 IS
2648 SELECT pa.instance_id, pa.process_item_type,
2649 pa.process_name, pa.process_version, ats.result_code
2650 FROM wf_process_activities pa,
2651 wf_activity_transitions ats
2652 WHERE
2653 -- process_activities
2654 pa.process_name = p_process_name
2655 AND pa.process_item_type = p_process_item_type
2656 AND pa.process_version = p_process_version
2657 -- process_activities -> activity_transitions
2658 AND ats.from_process_activity = pa.instance_id
2659 -- filters
2660 AND pa.activity_name = gc_SUBSCRIBE_TO_ACKS
2661 AND ats.result_code <> '*'
2662 AND NOT EXISTS
2663 (
2664 SELECT 1
2665 FROM wf_activity_attr_values ack_aav, xnp_msg_acks ack_xma
2666 WHERE
2667 -- Join with the outer SQL
2668 ack_aav.process_activity_id = pa.instance_id
2669 AND ack_xma.ack_msg_code = ats.result_code
2670 -- filter for attribute name
2671 AND ack_aav.name = gc_EVENT_TYPE
2672 -- activity_attr_values -> xnp_msg_acks
2673 AND ack_aav.text_value = ack_xma.source_msg_code)
2674
2675 ORDER BY pa.process_item_type, pa.process_name, pa.process_version;
2676
2677 v_first_time BOOLEAN := TRUE;
2678 v_text VARCHAR2(1000) := NULL;
2679
2680 BEGIN
2681
2682 v_first_time := TRUE;
2683
2684 FOR processes_rec IN c_wi_processes
2685 LOOP
2686 FOR rec IN c_activities(processes_rec.process_name,
2687 processes_rec.process_item_type,
2688 processes_rec.process_version)
2689 LOOP
2690 IF v_first_time THEN
2691 v_text := GetNumErrMsg('XNP_CVU_SUBSCRIBE_TO_ACKS');
2692 Print(v_text,gc_ERROR, gc_CONTEXT);
2693
2694 PrintReportDetails(gc_HEADING, gc_ERROR,
2695 'INSTANCE_ID','PROCESS_ITEM_TYPE',
2696 'PROCESS_NAME', 'PROCESS_VERSION', 'RESULT_CODE');
2697 v_first_time := FALSE;
2698 END IF;
2699
2700 PrintReportDetails(gc_DETAILS, gc_ERROR,
2701 rec.instance_id, rec.process_item_type,
2702 rec.process_name, rec.process_version, rec.result_code);
2703 END LOOP;
2704 END LOOP;
2705
2706 EndOfTable(NOT v_first_time);
2707
2708
2709 EXCEPTION
2710 WHEN OTHERS THEN
2711 XNP_CVU_PKG.HandleError('SUBSCRIBE_TO_ACKS', SQLCODE, SQLERRM);
2712 END SUBSCRIBE_TO_ACKS;
2713
2714
2715 FUNCTION ReloadRequired(p_fnd_lookup_type VARCHAR2,
2716 p_wf_lookup_type VARCHAR2)
2717 RETURN BOOLEAN
2718 IS
2719 v_cnt NUMBER;
2720 BEGIN
2721
2722 SELECT count(*)
2723 INTO v_cnt
2724 FROM fnd_lookups fl
2725 WHERE lookup_type = p_fnd_lookup_type
2726 AND NOT EXISTS
2727 (SELECT 1
2728 FROM wf_lookups wl
2729 WHERE substr(wl.lookup_code,1,29) = substr(fl.lookup_code,1, 29)
2730 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(fl.meaning, 1,75),'#')
2731 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(fl.description, 1, 239),'#')
2732 AND wl.lookup_type = p_wf_lookup_type);
2733
2734 IF v_cnt > 0 THEN
2735 return TRUE;
2736 ELSE
2737 return FALSE;
2738 END IF;
2739
2740 EXCEPTION
2741 WHEN OTHERS THEN
2742 HandleError('ReloadRequired', SQLCODE, SQLERRM);
2743 RAISE;
2744 END ReloadRequired;
2745
2746
2747 -- Message Name
2748 -- XNP_CVU_WF_LOOKUP_CODES
2749 -- Lookup codes in the workflow DO NOT match the SFM configuration.
2750 -- Re-run the lookup code loader API.
2751
2752 PROCEDURE WF_LOOKUP_CODES
2753 IS
2754 v_reload_flag BOOLEAN;
2755 v_failure VARCHAR2(1000) := NULL;
2756 v_cnt NUMBER;
2757 v_text VARCHAR2(1000) := NULL;
2758 v_delim VARCHAR2(100) := ' ';
2759 BEGIN
2760
2761 IF ReloadRequired('XNP_CHANGE_CAUSE_CODE', 'STATUS_CHANGE_CAUSE')
2762 THEN
2763 v_reload_flag := TRUE;
2764 v_failure := 'STATUS_CHANGE_CAUSE';
2765 END IF;
2766
2767 IF ReloadRequired('GET_CONSUMER_FE', 'GET_CONSUMER_FE')
2768 THEN
2769 v_reload_flag := TRUE;
2770 v_failure := v_failure ||v_delim|| 'GET_CONSUMER_FE';
2771 END IF;
2772
2773 IF ReloadRequired('XNP_FEATURE_TYPE', 'FEATURE_TYPE')
2774 THEN
2775 v_reload_flag := TRUE;
2776 v_failure := v_failure ||v_delim|| 'FEATURE_TYPE';
2777 END IF;
2778
2779 IF ReloadRequired('XNP_PHASE_INDICATOR', 'PORTING_PHASE')
2780 THEN
2781 v_reload_flag := TRUE;
2782 v_failure := v_failure ||v_delim|| 'PORTING_PHASE';
2783 END IF;
2784
2785 IF ReloadRequired('GET_RECEIVER_NAME', 'GET_RECEIVER_NAME')
2786 THEN
2787 v_reload_flag := TRUE;
2788 v_failure := v_failure ||v_delim|| 'GET_RECEIVER_NAME';
2789 END IF;
2790
2791 -- FE_NAME
2792
2793 SELECT count(*)
2794 INTO v_cnt
2795 FROM xdp_fes_vl fe
2796 WHERE NOT EXISTS
2797 (SELECT 1
2798 FROM wf_lookups wl
2799 WHERE substr(wl.lookup_code,1,29) = substr(fe.fulfillment_element_name,1, 29)
2800 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(fe.display_name, 1,75),'#')
2801 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(fe.description, 1, 239),'#')
2802 AND wl.lookup_type = 'FE_NAME');
2803
2804 IF v_cnt <> 0 THEN
2805 v_reload_flag := TRUE;
2806 v_failure := v_failure ||v_delim|| 'FE_NAME';
2807 END IF;
2808
2809 -- FA_NAME
2810
2811 SELECT count(*)
2812 INTO v_cnt
2813 FROM xdp_fulfill_actions_vl fa
2814 WHERE NOT EXISTS
2815 (SELECT 1
2816 FROM wf_lookups wl
2817 WHERE substr(wl.lookup_code,1,29) = substr(fa.fulfillment_action,1, 29)
2818 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(fa.display_name, 1,75),'#')
2819 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(fa.description, 1, 239),'#')
2820 AND wl.lookup_type = 'FA_NAME');
2821
2822 IF v_cnt <> 0 THEN
2823 v_reload_flag := TRUE;
2824 v_failure := v_failure ||v_delim|| 'FA_NAME';
2825 END IF;
2826
2827 -- MESSAGE_TYPE
2828
2829 SELECT count(*)
2830 INTO v_cnt
2831 FROM xnp_msg_types_vl mt
2832 WHERE NOT EXISTS
2833 (SELECT 1
2834 FROM wf_lookups wl
2835 WHERE substr(wl.lookup_code,1,29) = substr(mt.msg_code,1, 29)
2836 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(mt.display_name, 1,75),'#')
2837 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(mt.description, 1, 239),'#')
2838 AND wl.lookup_type = 'MESSAGE_TYPE');
2839
2840 IF v_cnt <> 0 THEN
2841 v_reload_flag := TRUE;
2842 v_failure := v_failure ||v_delim|| 'MESSAGE_TYPE';
2843 END IF;
2844
2845 -- TIMER_NAMES
2846
2847 SELECT count(*)
2848 INTO v_cnt
2849 FROM xnp_msg_types_vl mt
2850 WHERE msg_type = 'TIMER'
2851 AND NOT EXISTS
2852 (SELECT 1
2853 FROM wf_lookups wl
2854 WHERE substr(wl.lookup_code,1,29) = substr(mt.msg_code,1, 29)
2855 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(mt.display_name, 1,75),'#')
2856 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(mt.description, 1, 239),'#')
2857 AND wl.lookup_type = 'TIMER_NAMES');
2858
2859 IF v_cnt <> 0 THEN
2860 v_reload_flag := TRUE;
2861 v_failure := v_failure ||v_delim|| 'TIMER_NAMES';
2862 END IF;
2863
2864
2865 -- CUSTOMIZED_NOTN_MESSAGES
2866
2867 SELECT count(*)
2868 INTO v_cnt
2869 FROM fnd_new_messages nm
2870 WHERE message_name like 'X%_NOTFN_%'
2871 AND NOT EXISTS
2872 (SELECT 1
2873 FROM wf_lookups wl
2874 WHERE substr(wl.lookup_code,1,29) = substr(nm.message_name,1, 29)
2875 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(nm.description, 1,75),'#')
2876 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(nm.description, 1, 239),'#')
2877 AND wl.lookup_type = 'CUSTOMIZED_NOTN_MESSAGES');
2878
2879 IF v_cnt <> 0 THEN
2880 v_reload_flag := TRUE;
2881 v_failure := v_failure ||v_delim|| 'CUSTOMIZED_NOTN_MESSAGES';
2882 END IF;
2883
2884 -- STATUS
2885
2886 SELECT count(*)
2887 INTO v_cnt
2888 FROM xnp_sv_status_types_vl sst
2889 WHERE NOT EXISTS
2890 (SELECT 1
2891 FROM wf_lookups wl
2892 WHERE substr(wl.lookup_code,1,29) = substr(sst.status_type_code,1, 29)
2893 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(sst.display_name, 1,75),'#')
2894 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(sst.description, 1, 239),'#')
2895 AND wl.lookup_type = 'STATUS');
2896
2897 IF v_cnt <> 0 THEN
2898 v_reload_flag := TRUE;
2899 v_failure := v_failure ||v_delim|| 'STATUS';
2900 END IF;
2901
2902 -- WORKITEM
2903
2904 SELECT count(*)
2905 INTO v_cnt
2906 FROM xdp_workitems_vl wi
2907 WHERE NOT EXISTS
2908 (SELECT 1
2909 FROM wf_lookups wl
2910 WHERE substr(wl.lookup_code,1,29) = substr(wi.workitem_name,1, 29)
2911 AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(wi.display_name, 1,75),'#')
2912 AND nvl(substr(wl.description,1,239),'#') = nvl(substr(wi.description, 1, 239),'#')
2913 AND wl.lookup_type = 'WORKITEM');
2914
2915 IF v_cnt <> 0 THEN
2916 v_reload_flag := TRUE;
2917 v_failure := v_failure ||v_delim|| 'WORKITEM';
2918 END IF;
2919
2920 -- Display Error
2921
2922 IF v_reload_flag THEN
2923 v_text := GetNumErrMsg('XNP_CVU_WF_LOOKUP_CODES');
2924 Print(v_text,gc_WARNING, gc_MESSAGE);
2925 Print(v_failure,gc_WARNING, gc_MESSAGE);
2926 END IF;
2927
2928 EXCEPTION
2929 WHEN OTHERS THEN
2930 HandleError('WF_LOOKUP_CODES', SQLCODE, SQLERRM);
2931 END WF_LOOKUP_CODES;
2932
2933 PROCEDURE InitializeDisplay(p_display_type VARCHAR2)
2934 IS
2935 BEGIN
2936 IF p_display_type = gc_HTML THEN
2937 htp.p(htf.htmlOpen);
2938 htp.p(htf.title(GetMsgTxt('XNP_CVU_REPORT')));
2939 htp.p(htf.header(nsize=>2,
2940 cheader=>GetMsgTxt('XNP_CVU_REPORT'), calign=>'center'));
2941 htp.p(htf.bodyOpen);
2942 htp.p(htf.nl);
2943 htp.p(htf.nl);
2944 END IF;
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 HandleError('IniTializeDisplay', SQLCODE, SQLERRM);
2948 END InitializeDisplay;
2949
2950
2951
2952
2953 PROCEDURE CloseDisplay(p_display_type VARCHAR2)
2954 IS
2955 BEGIN
2956 IF p_display_type = gc_HTML THEN
2957 htp.p(htf.nl);
2958 htp.p(htf.nl);
2959 htp.p(htf.header(nsize=>2, cheader=>GetMsgTxt('XNP_END_OF_REPORT'),
2960 calign=>'center'));
2961 htp.p(htf.line);
2962 htp.p(htf.bodyClose);
2963 htp.p(htf.htmlClose);
2964 END IF;
2965 EXCEPTION
2966 WHEN OTHERS THEN
2967 HandleError('CloseDisplay', SQLCODE, SQLERRM);
2968 END CloseDisplay;
2969
2970 PROCEDURE StartCategory(p_error_code VARCHAR2)
2971 IS
2972 BEGIN
2973 Print(GetMsgTxt(p_error_code), gc_OK, gc_HEADER);
2974 g_category_no_error := TRUE;
2975 EXCEPTION
2976 WHEN OTHERS THEN
2977 HandleError('StartCategory', SQLCODE, SQLERRM);
2978 END StartCategory;
2979
2980 PROCEDURE EndCategory
2981 IS
2982 BEGIN
2983 IF g_category_no_error THEN
2984 Print(GetMsgTxt('XNP_NO_ERROR_OR_WARNING'), gc_OK, gc_SUB_CONTEXT);
2985 END IF;
2986 EXCEPTION
2987 WHEN OTHERS THEN
2988 HandleError('EndCategory', SQLCODE, SQLERRM);
2989 END EndCategory;
2990
2991 PROCEDURE STARTUP
2992 IS
2993 BEGIN
2994
2995
2996 InitializeDisplay(g_display_type);
2997
2998
2999 StartCategory('XNP_CVU_STATUS_TYPE_VALDNS');
3000 INITIAL_STATUS_OF_SV;
3001 LOCAL_SP_NAME;
3002 PHASE_IND_NO_STAT_TYPE;
3003 EndCategory;
3004
3005 StartCategory('XNP_CVU_GEO_VALDNS');
3006 SINGLE_GEO_TREE;
3007 EndCategory;
3008
3009 StartCategory('XNP_CVU_FAS_VALDNS');
3010 FULFILL_ACTIONS;
3011 EndCategory;
3012
3013 StartCategory('XNP_CVU_FE_VALDNS');
3014 FULFILL_ELEMENT_VALIDITY;
3015 FULFILL_ELEMENT_REFERENCES;
3016 NEW_ADAPTER_TYPES;
3017 EndCategory;
3018
3019 StartCategory('XNP_CVU_SP_VALDNS');
3020 SERVICE_PROVIDERS;
3021 EndCategory;
3022
3023 StartCategory('XNP_CVU_NUMBER_VALDNS');
3024 NUM_RANGE_GEO_AREA;
3025 INVALID_NUMBER_RANGES;
3026 POOLED_NUMBER_RANGES;
3027 NON_PORTED_PORTABLE_NUM_RANGES;
3028 SERVED_NUMBER_RANGES;
3029 ENABLE_NRC;
3030 EndCategory;
3031
3032
3033 StartCategory('XNP_CVU_SERVICE_VALDNS');
3034 SERVICE_VALIDATIONS;
3035 EndCategory;
3036
3037 StartCategory('XNP_CVU_PACKAGE_VALDNS');
3038 PACKAGE_VALIDATIONS;
3039 EndCategory;
3040
3041 StartCategory('XNP_CVU_MSG_VALDNS');
3042 ACTIVITY_BASED_TIMERS;
3043 MESSAGE_COMPILATION;
3044 EndCategory;
3045
3046 StartCategory('XNP_CVU_WI_VALDNS');
3047 WORK_ITEMS;
3048 EndCategory;
3049
3050 StartCategory('XNP_CVU_FP_VALDNS');
3051 INVALID_FP;
3052 EndCategory;
3053
3054
3055 StartCategory('XNP_CVU_WF_VALDNS');
3056 -- For Cross validations only verbose report make sense
3057 WF_PP_MSG_CROSS_VALIDATION;
3058 WF_NO_RQD_ITEM_ATT;
3059 WF_SPECIAL_ACTIVITIES;
3060 SUBSCRIBE_TO_BUSINESS_EVENTS;
3061 SUBSCRIBE_TO_ACKS;
3062 WF_LOOKUP_CODES;
3063 EndCategory;
3064
3065
3066 CloseDisplay(g_display_type);
3067
3068
3069 EXCEPTION
3070 WHEN OTHERS THEN
3071 HandleError('STARTUP', SQLCODE, SQLERRM);
3072 END STARTUP;
3073 END XNP_CVU_PKG;