[Home] [Help]
PACKAGE BODY: APPS.XNP_CORE
Source
1 PACKAGE BODY XNP_CORE AS
2 /* $Header: XNPCOREB.pls 120.2 2006/02/13 07:42:29 dputhiye ship $ */
3
4
5 PROCEDURE GET_ASSIGNED_SP_ID
6 (p_STARTING_NUMBER IN VARCHAR2
7 ,p_ENDING_NUMBER IN VARCHAR2
8 ,x_ASSIGNED_SP_ID OUT NOCOPY NUMBER
9 ,x_ERROR_CODE OUT NOCOPY NUMBER
10 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
11 )
12 IS
13 CURSOR c_ASSIGNED_SP_ID IS
14 SELECT assigned_sp_id
15 FROM xnp_number_ranges
16 WHERE starting_number <= p_starting_number
17 AND ending_number >= p_ending_number
18 AND active_flag = 'Y';
19
20 BEGIN
21 x_error_code := 0;
22 x_assigned_sp_id := 0;
23
24 -- Get the ASSIGNED_SP_ID corresponding to
25 -- this number range
26
27 OPEN c_assigned_sp_id;
28 FETCH c_assigned_sp_id INTO x_assigned_sp_id;
29
30 IF c_assigned_sp_id%NOTFOUND THEN
31 raise NO_DATA_FOUND;
32 END IF;
33
34 CLOSE c_assigned_sp_id;
35
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN
38 x_error_code := SQLCODE;
39
40 fnd_message.set_name('XNP','STD_GET_FAILED');
41 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_ASSIGNED_SP_ID');
42 fnd_message.set_token('ATTRNAME','ASSIGNED_SP_ID');
43 fnd_message.set_token('KEY','STARTING_NUMBER:ENDING_NUMBER');
44 fnd_message.set_token('VALUE',p_STARTING_NUMBER||':'||p_ENDING_NUMBER);
45 x_error_message := fnd_message.get;
46 x_error_message := x_error_message||':'||SQLERRM;
47
48 fnd_message.set_name('XNP','GET_ASSIGNED_SP_ID_ERR');
49 fnd_message.set_token('ERROR_TEXT',x_error_message);
50 x_error_message := fnd_message.get;
51
52 IF c_ASSIGNED_SP_ID%ISOPEN THEN
53 CLOSE c_assigned_sp_id;
54 END IF;
55
56 WHEN OTHERS THEN
57 x_error_code := SQLCODE;
58
59 fnd_message.set_name('XNP','STD_ERROR');
60 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_ASSIGNED_SP_ID');
61 fnd_message.set_token('ERROR_TEXT',SQLERRM);
62 x_error_message := fnd_message.get;
63
64 IF c_assigned_sp_id%ISOPEN THEN
65 CLOSE c_assigned_sp_id;
66 END IF;
67
68 END GET_ASSIGNED_SP_ID;
69
70 PROCEDURE GET_SP_ID
71 (p_SP_NAME IN VARCHAR2
72 ,x_SP_ID OUT NOCOPY NUMBER
73 ,x_ERROR_CODE OUT NOCOPY NUMBER
74 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
75 )
76 IS
77
78 CURSOR c_sp_id IS
79 SELECT sp_id
80 FROM xnp_service_providers
81 WHERE code = p_sp_name
82 AND active_flag = 'Y';
83
84
85 BEGIN
86 x_error_code := 0;
87 x_sp_id := 0;
88
89 -- Get the SP_ID corresponding to
90 -- this name
91 OPEN c_sp_id;
92 FETCH c_sp_id INTO x_sp_id;
93
94 IF c_sp_id%NOTFOUND THEN
95 raise NO_DATA_FOUND;
96 END IF;
97
98 CLOSE c_sp_id;
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 x_error_code := SQLCODE;
103
104 fnd_message.set_name('XNP','STD_GET_FAILED');
105 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_SP_ID');
106 fnd_message.set_token('ATTRNAME','SP_ID');
107 fnd_message.set_token('KEY','CODE');
108 fnd_message.set_token('VALUE',p_SP_NAME);
109 x_error_message := fnd_message.get;
110 x_error_message := x_error_message||':'||SQLERRM;
111
112 fnd_message.set_name('XNP','GET_SP_ID_ERR');
113 fnd_message.set_token('ERROR_TEXT',x_error_message);
114 x_error_message := fnd_message.get;
115
116 IF c_sp_id%ISOPEN THEN
117 CLOSE c_sp_id;
118 END IF;
119
120 WHEN OTHERS THEN
121 x_error_code := SQLCODE;
122
123 fnd_message.set_name('XNP','STD_ERROR');
124 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_SP_ID');
125 fnd_message.set_token('ERROR_TEXT',SQLERRM);
126 x_error_message := fnd_message.get;
127
128 IF c_SP_ID%ISOPEN THEN
129 CLOSE c_sp_id;
130 END IF;
131
132 END GET_SP_ID;
133
134 PROCEDURE GET_SP_NAME
135 (p_SP_ID IN NUMBER
136 ,x_SP_NAME OUT NOCOPY VARCHAR2
137 ,x_ERROR_CODE OUT NOCOPY NUMBER
138 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
139 )
140 IS
141
142 CURSOR c_sp_name IS
143 SELECT code
144 FROM xnp_service_providers
145 WHERE sp_id = p_sp_id;
146
147 BEGIN
148
149 x_error_code := 0;
150 x_sp_name := NULL;
151
152 -- get the name for this id
153 OPEN c_sp_name;
154 FETCH c_sp_name INTO x_sp_name;
155
156 IF c_sp_name%NOTFOUND THEN
157 raise NO_DATA_FOUND;
158 END IF;
159
160 CLOSE c_sp_name;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 x_error_code := SQLCODE;
165
166 fnd_message.set_name('XNP','STD_GET_FAILED');
167 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_SP_NAME_ID');
168 fnd_message.set_token('ATTRNAME','CODE');
169 fnd_message.set_token('KEY','SP_ID');
170 fnd_message.set_token('VALUE',to_char(p_SP_ID));
171 x_error_message := fnd_message.get;
172 x_error_message := x_error_message||':'||SQLERRM;
173
174
175 fnd_message.set_name('XNP','GET_SP_NAME_ERR');
176 fnd_message.set_token('ERROR_TEXT',x_error_message);
177 x_error_message := fnd_message.get;
178
179 IF c_sp_name%ISOPEN THEN
180 CLOSE c_sp_name;
181 END IF;
182
183 WHEN OTHERS THEN
184 x_error_code := SQLCODE;
185
186 fnd_message.set_name('XNP','STD_ERROR');
187 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_SP_NAME');
188 fnd_message.set_token('ERROR_TEXT',SQLERRM);
189 x_error_message := fnd_message.get;
190
191 IF c_sp_name%ISOPEN THEN
192 CLOSE c_sp_name;
193 END IF;
194
195 END GET_SP_NAME;
196
197 PROCEDURE GET_ROUTING_NUMBER_ID
198 (p_ROUTING_NUMBER IN VARCHAR2
199 ,x_ROUTING_NUMBER_ID OUT NOCOPY NUMBER
200 ,x_ERROR_CODE OUT NOCOPY NUMBER
201 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
202 )
203 IS
204 CURSOR c_routing_number_id IS
205 SELECT routing_number_id
206 FROM xnp_routing_numbers
207 WHERE routing_number = p_routing_number
208 AND active_flag = 'Y';
209
210 BEGIN
211 x_error_code := 0;
212 x_routing_number_id := 0;
213
214 -- Get the ROUTING_NUMBER_ID corresponding to
215 -- this
216 OPEN c_routing_number_id;
217 FETCH c_routing_number_id INTO x_routing_number_id;
218
219 IF c_routing_number_id%NOTFOUND THEN
220 raise NO_DATA_FOUND;
221 END IF;
222
223 CLOSE c_routing_number_id;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 x_error_code := SQLCODE;
227
228 fnd_message.set_name('XNP','STD_GET_FAILED');
229 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_ROUTING_NUMBER_ID');
230 fnd_message.set_token('ATTRNAME','ROUTING_NUMBER_ID');
231 fnd_message.set_token('KEY','ROUTING_NUMBER');
232 fnd_message.set_token('VALUE',p_ROUTING_NUMBER);
233 x_error_message := fnd_message.get;
234 x_error_message := x_error_message||':'||SQLERRM;
235
236
237 fnd_message.set_name('XNP','GET_ROUTING_NUMBER_ID_ERR');
238 fnd_message.set_token('ERROR_TEXT',x_error_message);
239 x_error_message := fnd_message.get;
240
241 IF c_routing_number_id%ISOPEN THEN
242 CLOSE c_routing_number_id;
243 END IF;
244
245 WHEN OTHERS THEN
246 x_error_code := SQLCODE;
247
248 fnd_message.set_name('XNP','STD_ERROR');
249 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_ROUTING_NUMBER_ID');
250 fnd_message.set_token('ERROR_TEXT',SQLERRM);
251 x_error_message := fnd_message.get;
252
253 IF c_routing_number_id%ISOPEN THEN
254 CLOSE c_routing_number_id;
255 END IF;
256
257 END GET_ROUTING_NUMBER_ID;
258
259 PROCEDURE GET_NRC_ID
260 (p_STARTING_NUMBER IN VARCHAR2
261 ,p_ENDING_NUMBER IN VARCHAR2
262 ,x_NRC_ID OUT NOCOPY NUMBER
263 ,x_ERROR_CODE OUT NOCOPY NUMBER
264 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
265 )
266 IS
267
268 l_enable_nrc_flag VARCHAR2(1) := 'Y';
269 l_geo_id NUMBER := null;
270 l_starting_geo_id NUMBER := null;
271
272 CURSOR c_starting_geo_id IS
273 SELECT geo_area_id
274 FROM xnp_number_ranges
275 WHERE starting_number <= p_starting_number
276 AND ending_number >= p_ending_number
277 AND sysdate >= effective_date;
278
279 CURSOR c_geo_id (l_starting_geo_id IN NUMBER) IS
280 SELECT child_geo_area_id
281 FROM xnp_geo_hierarchy
282 START WITH child_geo_area_id = l_starting_geo_id
283 CONNECT BY PRIOR parent_geo_area_id = child_geo_area_id;
284
285 CURSOR c_nrc_id(l_geo_id IN NUMBER) IS
286 SELECT sp.sp_id
287 FROM xnp_service_providers sp
288 ,xnp_service_areas sa
289 WHERE sp.sp_id = sa.sp_id
290 AND sp.sp_type = 'NRC'
291 AND sa.geo_area_id = l_geo_id;
292 BEGIN
293
294 x_error_code := 0;
295 x_nrc_id := null;
296
297 l_enable_nrc_flag := g_enable_nrc_flag;
298
299 /**
300 fnd_profile.get
301 (name => 'ENABLE_NRC'
302 ,val => l_enable_nrc_flag
303 ) ;
304 **/
305
306 IF( (l_enable_nrc_flag IS NULL) OR (l_enable_nrc_flag <> 'Y') ) THEN
307 x_nrc_id := null;
308 RETURN;
309 END IF;
310
311 OPEN c_starting_geo_id;
312 FETCH c_starting_geo_id INTO l_starting_geo_id;
313
314 IF c_starting_geo_id%NOTFOUND THEN
315 IF c_starting_geo_id%ISOPEN THEN
316 CLOSE c_starting_geo_id;
317 END IF;
318 raise NO_DATA_FOUND;
319 END IF;
320
321 IF c_starting_geo_id%ISOPEN THEN
322 CLOSE c_starting_geo_id;
323 END IF;
324
325
326 -- Check for NRC in the geo hierarchy tree
327 -- starting with the children
328 OPEN c_geo_id(l_starting_geo_id);
329 LOOP
330 x_nrc_id := null;
331 l_geo_id := null;
332 FETCH c_geo_id INTO l_geo_id;
333 EXIT WHEN c_geo_id%NOTFOUND;
334
335 BEGIN
336
337 OPEN c_nrc_id(l_geo_id);
338 FETCH c_nrc_id INTO x_nrc_id;
339
340 IF c_nrc_id%NOTFOUND THEN
341 IF c_nrc_id%ISOPEN THEN
342 CLOSE c_nrc_id;
343 END IF;
344 raise NO_DATA_FOUND;
345 END IF;
346
347 IF c_nrc_id%ISOPEN THEN
348 CLOSE c_nrc_id;
349 END IF;
350
351 IF (x_nrc_id is not null) THEN
352 IF c_geo_id%ISOPEN THEN
353 CLOSE c_geo_id;
354 END IF;
355 RETURN;
356 END IF;
357
358 EXCEPTION WHEN NO_DATA_FOUND THEN
359 null; -- Ignore and move to the next parent geo area id
360 END;
361
362 END LOOP;
363
364 IF c_geo_id%ISOPEN THEN
365 CLOSE c_geo_id;
366 END IF;
367
368 -- If no NRC found after tracing through all the geo areas
369 -- then at this point raise a nodatafound exception
370 RAISE NO_DATA_FOUND;
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 x_error_code := SQLCODE;
375
376 fnd_message.set_name('XNP','STD_GET_FAILED');
377 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_NRC_ID');
378 fnd_message.set_token('ATTRNAME','NRC_ID');
379 fnd_message.set_token('KEY','STARTING_NUMBER:ENDING_NUMBER');
380 fnd_message.set_token('VALUE',p_STARTING_NUMBER||':'||p_ENDING_NUMBER);
381 x_error_message := fnd_message.get;
382 x_error_message := x_error_message||':'||SQLERRM;
383
384 fnd_message.set_name('XNP','GET_NRC_ID_ERR');
385 fnd_message.set_token('ERROR_TEXT',x_error_message);
386 x_error_message := fnd_message.get;
387
388 IF c_geo_id%ISOPEN THEN
389 CLOSE c_geo_id;
390 END IF;
391
392 IF c_starting_geo_id%ISOPEN THEN
393 CLOSE c_starting_geo_id;
394 END IF;
395
396 IF c_nrc_id%ISOPEN THEN
397 CLOSE c_nrc_id;
398 END IF;
399
400 WHEN OTHERS THEN
401 x_error_code := SQLCODE;
402
403 fnd_message.set_name('XNP','STD_ERROR');
404 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_NRC_ID');
405 fnd_message.set_token('ERROR_TEXT',SQLERRM);
406 x_error_message := fnd_message.get;
407
408 IF c_geo_id%ISOPEN THEN
409 CLOSE c_geo_id;
410 END IF;
411
412 IF c_starting_geo_id%ISOPEN THEN
413 CLOSE c_starting_geo_id;
414 END IF;
415
416 IF c_nrc_id%ISOPEN THEN
417 CLOSE c_nrc_id;
418 END IF;
419
420 END GET_NRC_ID;
421
422
423 PROCEDURE GET_SOA_SV_ID
424 (p_PHASE_INDICATOR VARCHAR2
425 ,p_SUBSCRIPTION_TN VARCHAR2
426 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
427 ,x_SV_ID OUT NOCOPY NUMBER
428 ,x_ERROR_CODE OUT NOCOPY NUMBER
429 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
430 )
431 IS
432
433 CURSOR c_sv_id IS
434 SELECT sv_soa_id
435 FROM xnp_sv_soa SOA , xnp_sv_status_types_b STA
436 WHERE SOA.subscription_tn = p_subscription_tn
437 AND STA.phase_indicator = p_phase_indicator
438 AND STA.status_type_code = SOA.status_type_code;
439
440 BEGIN
441 x_error_code := 0;
442 x_error_message := NULL;
443 x_sv_id := 0;
444
445 OPEN c_sv_id;
446 FETCH c_sv_id INTO x_sv_id;
447
448 IF c_sv_id%NOTFOUND THEN
449 raise NO_DATA_FOUND;
450 END IF;
451
452 CLOSE c_sv_id;
453
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 x_error_code := SQLCODE;
457 fnd_message.set_name('XNP','STD_GET_FAILED');
458 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_SOA_SV_ID');
459 fnd_message.set_token('ATTRNAME','SV');
460 fnd_message.set_token('KEY','TN:SPID:PHASE');
461 fnd_message.set_token
462 ('VALUE'
463 ,p_subscription_tn
464 ||':'||to_char(p_local_sp_id)
465 ||':'||p_phase_indicator
466 );
467 x_error_message := fnd_message.get;
468 x_error_message := x_error_message||':'||SQLERRM;
469
470
471 fnd_message.set_name('XNP','GET_SOA_SV_ID_ERR');
472 fnd_message.set_token('ERROR_TEXT',x_error_message);
473 x_error_message := fnd_message.get;
474
475 IF c_SV_ID%ISOPEN THEN
476 CLOSE c_sv_id;
477 END IF;
478
479 WHEN OTHERS THEN
480 x_error_code := SQLCODE;
481 fnd_message.set_name('XNP','STD_ERROR');
482 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_SOA_SV_ID');
483 fnd_message.set_token('ERROR_TEXT',SQLERRM);
484 x_error_message := fnd_message.get;
485
486 IF c_sv_id%ISOPEN THEN
487 CLOSE c_sv_id;
488 END IF;
489
490 END GET_SOA_SV_ID;
491
492 PROCEDURE GET_PHASE_FOR_STATUS
493 (p_CUR_STATUS_TYPE_CODE VARCHAR2
494 ,x_PHASE_INDICATOR OUT NOCOPY VARCHAR2
495 ,x_ERROR_CODE OUT NOCOPY NUMBER
496 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
497 )
498 IS
499 CURSOR c_phase_indicator IS
500 SELECT phase_indicator
501 FROM xnp_sv_status_types_b
502 WHERE status_type_code = p_cur_status_type_code;
503
504
505 BEGIN
506
507 x_error_code := 0;
508 x_error_message := NULL;
509 x_phase_indicator := NULL;
510
511 OPEN c_phase_indicator;
512 FETCH c_phase_indicator INTO x_phase_indicator;
513
514 IF c_phase_indicator%NOTFOUND THEN
515 raise NO_DATA_FOUND;
516 END IF;
517
518 CLOSE c_phase_indicator;
519
520 EXCEPTION
521 WHEN NO_DATA_FOUND THEN
522 x_error_code := SQLCODE;
523
524 fnd_message.set_name('XNP','STD_GET_FAILED');
525 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_PHASE_FOR_STATUS');
526 fnd_message.set_token('ATTRNAME','PHASE_INDICATOR');
527 fnd_message.set_token('KEY','STATUS_TYPE_CODE');
528 fnd_message.set_token('VALUE',p_CUR_STATUS_TYPE_CODE);
529 x_error_message := fnd_message.get;
530 x_error_message := x_error_message||':'||SQLERRM;
531
532
533 fnd_message.set_name('XNP','GET_PHASE_FOR_STATUS_ERR');
534 fnd_message.set_token('ERROR_TEXT',x_error_message);
535 x_error_message := fnd_message.get;
536
537 IF c_phase_indicator%ISOPEN THEN
538 CLOSE c_phase_indicator;
539 END IF;
540
541 WHEN OTHERS THEN
542 x_error_code := SQLCODE;
543
544 fnd_message.set_name('XNP','STD_ERROR');
545 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_PHASE_FOR_STATUS');
546 fnd_message.set_token('ERROR_TEXT',SQLERRM);
547 x_error_message := fnd_message.get;
548
549 IF c_phase_indicator%ISOPEN THEN
550 CLOSE c_phase_indicator;
551 END IF;
552
553 END GET_PHASE_FOR_STATUS;
554
555 PROCEDURE GET_NUMBER_RANGE_ID
556 (p_STARTING_NUMBER VARCHAR2
557 ,p_ENDING_NUMBER VARCHAR2
558 ,x_NUMBER_RANGE_ID OUT NOCOPY NUMBER
559 ,x_ERROR_CODE OUT NOCOPY NUMBER
560 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
561 )
562 IS
563
564 CURSOR c_number_range_id IS
565 SELECT number_range_id
566 FROM xnp_number_ranges
567 WHERE starting_number <= p_starting_number
568 AND ending_number >= p_ending_number
569 AND sysdate >= effective_date
570 AND active_flag='Y';
571
572 BEGIN
573
574 OPEN c_number_range_id;
575 FETCH c_number_range_id INTO x_number_range_id;
576
577 IF c_number_range_id%NOTFOUND THEN
578 raise NO_DATA_FOUND;
579 END IF;
580
581 CLOSE c_number_range_id;
582
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 x_error_code := SQLCODE;
586 fnd_message.set_name('XNP','STD_GET_FAILED');
587 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_NUMBER_RANGE_ID');
588 fnd_message.set_token('ATTRNAME','NUMBER_RANGE_ID');
589 fnd_message.set_token('KEY','STARTING_NUMBER:ENDING_NUMBER');
590 fnd_message.set_token
591 ('VALUE'
592 ,p_starting_number||':'||p_ending_number
593 );
594 x_error_message := fnd_message.get;
595 x_error_message := x_error_message||':'||SQLERRM;
596
597 fnd_message.set_name('XNP','GET_NUMBER_RANGE_ID_ERR');
598 fnd_message.set_token('ERROR_TEXT',x_error_message);
599 x_error_message := fnd_message.get;
600
601 IF c_number_range_id%ISOPEN THEN
602 CLOSE c_number_range_id;
603 END IF;
604 WHEN OTHERS THEN
605 x_error_code := SQLCODE;
606 fnd_message.set_name('XNP','STD_ERROR');
607 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_NUMBER_RANGE_ID');
608 fnd_message.set_token('ERROR_TEXT',SQLERRM);
609 x_error_message := fnd_message.get;
610 IF c_number_range_id%ISOPEN THEN
611 CLOSE c_number_range_id;
612 END IF;
613
614 END GET_NUMBER_RANGE_ID;
615
616
617 PROCEDURE GET_SMS_SV_ID
618 (p_SUBSCRIPTION_TN VARCHAR2
619 ,x_SV_ID OUT NOCOPY NUMBER
620 ,x_ERROR_CODE OUT NOCOPY NUMBER
621 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
622 )
623 IS
624 CURSOR c_sms_id IS
625 SELECT sv_sms_id
626 FROM xnp_sv_sms
627 WHERE subscription_tn = p_subscription_tn ;
628
629 BEGIN
630
631 x_sv_id := 0;
632 x_error_code := 0;
633 x_error_message := NULL;
634
635 OPEN c_sms_id;
636 FETCH c_sms_id INTO x_sv_id;
637
638 IF c_sms_id%NOTFOUND THEN
639 raise NO_DATA_FOUND;
640 END IF;
641
642 CLOSE c_sms_id;
643
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 x_error_code := SQLCODE;
647 fnd_message.set_name('XNP','STD_GET_FAILED');
648 fnd_message.set_token('FAILED_PROC','XNP_CORE.GET_SMS_SV_ID');
649 fnd_message.set_token('ATTRNAME','SMS SV');
650 fnd_message.set_token('KEY','SUBSCRIPTION_TN');
651 fnd_message.set_token('VALUE',p_SUBSCRIPTION_TN);
652 x_error_message := fnd_message.get;
653 x_error_message := x_error_message||':'||SQLERRM;
654
655
656 fnd_message.set_name('XNP','GET_SMS_SV_ID_ERR');
657 fnd_message.set_token('ERROR_TEXT',x_error_message);
658 x_error_message := fnd_message.get;
659
660 IF c_sms_id%ISOPEN THEN
661 CLOSE c_sms_id;
662 END IF;
663
664 WHEN OTHERS THEN
665 x_error_code := SQLCODE;
666
667 fnd_message.set_name('XNP','STD_ERROR');
668 fnd_message.set_token('ERROR_LOCN','XNP_CORE.GET_SMS_SV_ID');
669 fnd_message.set_token('ERROR_TEXT',SQLERRM);
670 x_error_message := fnd_message.get;
671
672 IF c_sms_id%ISOPEN THEN
673 CLOSE c_sms_id;
674 END IF;
675
676 END GET_SMS_SV_ID;
677
678
679 PROCEDURE SMS_CREATE_PORTED_NUMBER
680 (p_PORTING_ID IN VARCHAR2
681 ,p_STARTING_NUMBER IN NUMBER
682 ,p_ENDING_NUMBER IN NUMBER
683 ,p_SUBSCRIPTION_TYPE IN VARCHAR2
684 ,p_ROUTING_NUMBER_ID IN NUMBER
685 ,p_PORTING_TIME IN DATE
686 ,p_CNAM_ADDRESS VARCHAR2
687 ,p_CNAM_SUBSYSTEM VARCHAR2
688 ,p_ISVM_ADDRESS VARCHAR2
689 ,p_ISVM_SUBSYSTEM VARCHAR2
690 ,p_LIDB_ADDRESS VARCHAR2
691 ,p_LIDB_SUBSYSTEM VARCHAR2
692 ,p_CLASS_ADDRESS VARCHAR2
693 ,p_CLASS_SUBSYSTEM VARCHAR2
694 ,p_WSMSC_ADDRESS VARCHAR2
695 ,p_WSMSC_SUBSYSTEM VARCHAR2
696 ,p_RN_ADDRESS VARCHAR2
697 ,p_RN_SUBSYSTEM VARCHAR2
698 ,p_ORDER_ID IN NUMBER
699 ,p_LINEITEM_ID IN NUMBER
700 ,p_WORKITEM_INSTANCE_ID IN NUMBER
701 ,p_FA_INSTANCE_ID IN NUMBER
702 ,x_ERROR_CODE OUT NOCOPY NUMBER
703 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
704 )
705 IS
706 l_counter BINARY_INTEGER := 0;
707 l_diff NUMBER := (p_ending_number - p_starting_number);
708 l_init NUMBER := p_starting_number;
709 l_geo_id NUMBER := 0;
710 l_nrc_id NUMBER := 0;
711 l_sv_sms_id NUMBER := null;
712
713 CURSOR c_check_sms_sv_exists (l_ph_no IN VARCHAR2) IS
714 SELECT sv_sms_id
715 FROM xnp_sv_sms
716 WHERE subscription_tn = l_ph_no
717 AND subscription_type = p_subscription_type;
718
719 BEGIN
720 x_error_code := 0;
721
722 -- Get the NRC for the given GEO id
723
724 xnp_core.get_nrc_id
725 (p_starting_number
726 ,p_ending_number
727 ,l_nrc_id
728 ,x_error_code
729 ,x_error_message
730 );
731
732 IF x_error_code <> 0 THEN
733 RETURN;
734 END IF;
735
736 -- Insert a row into XNP_SV_SMS for each TN in the range
737 FOR l_counter IN 0..l_diff
738 LOOP
739
740 l_sv_sms_id := NULL;
741
742 OPEN c_check_sms_sv_exists(to_char(l_init+l_counter));
743 FETCH c_check_sms_sv_exists INTO l_sv_sms_id;
744
745 IF c_check_sms_sv_exists%NOTFOUND THEN
746
747 /**
748 SELECT xnp_sv_sms_s.nextval
749 INTO l_sv_sms_id
750 FROM dual;
751 **/
752
753 INSERT into xnp_sv_sms
754 (sv_sms_id ,
755 object_reference ,
756 routing_number_id ,
757 subscription_tn ,
758 subscription_type ,
759 mediator_sp_id ,
760 provision_sent_date ,
761 cnam_address ,
762 cnam_subsystem ,
763 isvm_address ,
764 isvm_subsystem ,
765 lidb_address ,
766 lidb_subsystem ,
767 class_address ,
768 class_subsystem ,
769 wsmsc_address ,
770 wsmsc_subsystem ,
771 rn_address ,
772 rn_subsystem ,
773 created_by ,
774 creation_date ,
775 last_updated_by ,
776 last_update_date
777 )
778 VALUES
779 (xnp_sv_sms_s.nextval ,
780 p_porting_id ,
781 p_routing_number_id ,
782 to_char((l_init+l_counter)) ,
783 p_subscription_type ,
784 l_nrc_id ,
785 p_porting_time ,
786 p_cnam_address ,
787 p_cnam_subsystem ,
788 p_isvm_address ,
789 p_isvm_subsystem ,
790 p_lidb_address ,
791 p_lidb_subsystem ,
792 p_class_address ,
793 p_class_subsystem ,
794 p_wsmsc_address ,
795 p_wsmsc_subsystem ,
796 p_rn_address ,
797 p_rn_subsystem ,
798 fnd_global.
799 user_id ,
800 sysdate ,
801 fnd_global.user_id ,
802 sysdate
803 ) RETURNING sv_sms_id INTO l_sv_sms_id ;
804
805 ELSE
806 UPDATE xnp_sv_sms
807 SET object_reference = p_porting_id
808 ,provision_sent_date = p_porting_time
809 ,routing_number_id = p_routing_number_id
810 ,cnam_address = p_cnam_address
811 ,cnam_subsystem = p_cnam_subsystem
812 ,isvm_address = p_isvm_address
813 ,isvm_subsystem = p_isvm_subsystem
814 ,lidb_address = p_lidb_address
815 ,lidb_subsystem = p_lidb_subsystem
816 ,class_address = p_class_address
817 ,class_subsystem = p_class_subsystem
818 ,wsmsc_address = p_wsmsc_address
819 ,wsmsc_subsystem = p_wsmsc_subsystem
820 ,rn_address = p_rn_address
821 ,rn_subsystem = p_rn_subsystem
822 ,last_updated_by = fnd_global.user_id
823 ,last_update_date = sysdate
824 WHERE sv_sms_id = l_sv_sms_id ;
825
826 END IF;
827
828 -- Call To CREATE_ORDER_MAPPING to create order mapping for the order in xnp_sv_order_mappnings table
829
830 CREATE_ORDER_MAPPING
831 (p_ORDER_ID ,
832 p_LINEITEM_ID ,
833 p_WORKITEM_INSTANCE_ID,
834 p_FA_INSTANCE_ID ,
835 NULL ,
836 l_sv_sms_id ,
837 x_ERROR_CODE ,
838 x_ERROR_MESSAGE
839 );
840
841 CLOSE c_check_sms_sv_exists;
842
843 END LOOP;
844
845 EXCEPTION
846 WHEN OTHERS THEN
847 x_error_code := SQLCODE;
848 fnd_message.set_name('XNP','STD_ERROR');
849 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SMS_CREATE_PORTED_NUMBER');
850 fnd_message.set_token('ERROR_TEXT',SQLERRM);
851 x_error_message := fnd_message.get;
852
853 IF c_check_sms_sv_exists%ISOPEN THEN
854 CLOSE c_check_sms_sv_exists;
855 END IF;
856
857 END SMS_CREATE_PORTED_NUMBER;
858
859
860 PROCEDURE SOA_UPDATE_CUTOFF_DATE
861 (p_STARTING_NUMBER VARCHAR2
862 ,p_ENDING_NUMBER VARCHAR2
863 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
864 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
865 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
866 ,p_ORDER_ID IN NUMBER
867 ,p_LINEITEM_ID IN NUMBER
868 ,p_WORKITEM_INSTANCE_ID IN NUMBER
869 ,p_FA_INSTANCE_ID IN NUMBER
870 ,x_ERROR_CODE OUT NOCOPY NUMBER
871 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
872 )
873 IS
874 l_counter BINARY_INTEGER := 0;
875 l_sv_id NUMBER := 0;
876 l_phase_indicator VARCHAR2(200) := null;
877 l_starting_number VARCHAR2(80) := null;
878 l_ending_number VARCHAR2(80) := null;
879
880 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
881 l_sv_soa_id SV_SOA_ID_TAB;
882 i BINARY_INTEGER;
883
884 BEGIN
885 x_error_code := 0;
886
887 l_starting_number := to_char(to_number(p_starting_number));
888 l_ending_number := to_char(to_number(p_ending_number));
889
890 -- Get the phase corresponding to this 'p_cur_status_type_code'
891
892 xnp_core.get_phase_for_status
893 (p_cur_status_type_code
894 ,l_phase_indicator
895 ,x_error_code
896 ,x_error_message
897 );
898
899 IF x_error_code <> 0 THEN
900 RETURN;
901 END IF;
902
903 -- For each TN Get the SVid which is in this phase
904 -- and update the cutoff date to the
905 -- given value
906
907 x_error_code := 0;
908
909 SELECT sv_soa_id BULK COLLECT
910 INTO l_sv_soa_id
911 FROM xnp_sv_soa soa,
912 xnp_sv_status_types_b sta
913 WHERE soa.subscription_tn
914 BETWEEN l_starting_number AND l_ending_number
915 AND sta.phase_indicator = l_phase_indicator
916 AND sta.status_type_code = soa.status_type_code;
917
918 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
919
920 UPDATE xnp_sv_soa soa
921 SET soa.old_sp_cutoff_due_date = p_old_sp_cutoff_due_date,
922 soa.modified_date = sysdate,
923 soa.last_updated_by = fnd_global.user_id,
924 soa.last_update_date = sysdate
925 WHERE soa.sv_soa_id = l_sv_soa_id(i);
926
927 -- Call CREATE_ORDER_MAPPING Procedure to create record in XNP_SV_ORDER_MAPPINGS table
928
929 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
930
931 INSERT INTO XNP_SV_ORDER_MAPPINGS
932 (sv_order_mapping_id ,
933 sv_soa_id ,
934 order_id ,
935 workitem_instance_id ,
936 created_by ,
937 creation_date ,
938 last_updated_by ,
939 last_update_date
940 )
941 VALUES
942 (XNP_SV_ORDER_MAPPINGS_S.nextval,
943 l_sv_soa_id(i) ,
944 p_order_id ,
945 p_workitem_instance_id ,
946 fnd_global.user_id ,
947 sysdate ,
948 fnd_global.user_id ,
949 sysdate
950 );
951
952 EXCEPTION
953
954 WHEN OTHERS THEN
955 x_error_code := SQLCODE;
956 fnd_message.set_name('XNP','STD_ERROR');
957 fnd_message.set_token('ERROR_LOCN'
958 ,'XNP_CORE.SOA_UPDATE_CUTOFF_DATE');
959 fnd_message.set_token('ERROR_TEXT',SQLERRM);
960 x_error_message := fnd_message.get;
961
962 END SOA_UPDATE_CUTOFF_DATE;
963
964 PROCEDURE SOA_UPDATE_SV_STATUS
965 (p_STARTING_NUMBER VARCHAR2
966 ,p_ENDING_NUMBER VARCHAR2
967 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
968 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
969 ,p_NEW_STATUS_TYPE_CODE VARCHAR2
970 ,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
971 ,p_ORDER_ID IN NUMBER
972 ,p_LINEITEM_ID IN NUMBER
973 ,p_WORKITEM_INSTANCE_ID IN NUMBER
974 ,p_FA_INSTANCE_ID IN NUMBER
975 ,x_ERROR_CODE OUT NOCOPY NUMBER
976 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
977 )
978 IS
979 l_counter BINARY_INTEGER := 0;
980 l_sv_id NUMBER :=0;
981 l_phase_indicator VARCHAR2(200) := null;
982 l_starting_number VARCHAR2(80) := null;
983 l_ending_number VARCHAR2(80) := null;
984
985 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
986 TYPE sv_soa_status_tab IS TABLE OF VARCHAR2(40);
987 l_sv_event_code SV_SOA_STATUS_TAB ;
988 l_sv_soa_id SV_SOA_ID_TAB;
989 i BINARY_INTEGER;
990
991 BEGIN
992
993 x_error_code := 0;
994
995 l_starting_number := to_char(to_number(p_starting_number));
996 l_ending_number := to_char(to_number(p_ending_number));
997
998 -- Get the phase corresponding to this 'p_cur_status_type_code'
999
1000 xnp_core.get_phase_for_status
1001 (p_cur_status_type_code
1002 ,l_phase_indicator
1003 ,x_error_code
1004 ,x_error_message
1005 );
1006 IF x_error_code <> 0 THEN
1007 RETURN;
1008 END IF;
1009
1010
1011 -- For each TN Get the SVid which is in this phase
1012 -- and update the status cutoff date to the
1013 -- given values
1014 --
1015
1016 SELECT soa.sv_soa_id,
1017 soa.status_type_code BULK COLLECT
1018 INTO l_sv_soa_id,
1019 l_sv_event_code
1020 FROM xnp_sv_soa soa,
1021 xnp_sv_status_types_b sta
1022 WHERE SOA.subscription_tn BETWEEN l_starting_number AND l_ending_number
1023 AND STA.phase_indicator = l_phase_indicator
1024 AND STA.status_type_code = SOA.status_type_code;
1025
1026 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
1027
1028 UPDATE xnp_sv_soa soa
1029 SET soa.status_type_code = p_new_status_type_code ,
1030 soa.status_change_cause_code = p_status_change_cause_code ,
1031 soa.prev_status_type_code = soa.status_type_code,
1032 soa.modified_date = sysdate,
1033 soa.last_updated_by = fnd_global.user_id,
1034 soa.last_update_date = sysdate
1035 WHERE soa.sv_soa_id = l_sv_soa_id(i)
1036 AND soa.status_type_code <> p_new_status_type_code;
1037
1038 -- Create a history record for the status event change XNP_SV_EVENT_HISTORY table
1039
1040 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
1041
1042 INSERT INTO XNP_SV_EVENT_HISTORY
1043 (sv_event_history_id ,
1044 sv_soa_id ,
1045 event_code ,
1046 event_type ,
1047 event_timestamp ,
1048 event_cause_code ,
1049 created_by ,
1050 creation_date ,
1051 last_updated_by ,
1052 last_update_date
1053 )
1054 VALUES
1055 (XNP_SV_EVENT_HISTORY_S.nextval,
1056 l_sv_soa_id(i) ,
1057 l_sv_event_code(i) ,
1058 'STATUS_CHANGE' ,
1059 sysdate ,
1060 p_status_change_cause_code,
1061 fnd_global.user_id ,
1062 sysdate ,
1063 fnd_global.user_id ,
1064 sysdate
1065 );
1066
1067 -- Call CREATE_ORDER_MAPPING Procedure to create record in XNP_SV_ORDER_MAPPINGS table
1068
1069 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
1070
1071
1072 INSERT INTO XNP_SV_ORDER_MAPPINGS
1073 (sv_order_mapping_id ,
1074 sv_soa_id ,
1075 order_id ,
1076 workitem_instance_id ,
1077 created_by ,
1078 creation_date ,
1079 last_updated_by ,
1080 last_update_date
1081 )
1082 VALUES
1083 (XNP_SV_ORDER_MAPPINGS_S.nextval,
1084 l_sv_soa_id(i) ,
1085 p_order_id ,
1086 p_workitem_instance_id ,
1087 fnd_global.user_id ,
1088 sysdate ,
1089 fnd_global.user_id ,
1090 sysdate
1091 );
1092 EXCEPTION
1093 WHEN dup_val_on_index THEN
1094 null;
1095 WHEN OTHERS THEN
1096 x_error_code := SQLCODE;
1097 fnd_message.set_name('XNP','STD_ERROR');
1098 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_SV_STATUS');
1099 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1100 x_error_message := fnd_message.get;
1101
1102 END SOA_UPDATE_SV_STATUS;
1103
1104 PROCEDURE SOA_CREATE_REC_PORT_ORDER
1105 (p_PORTING_ID VARCHAR2
1106 ,p_STARTING_NUMBER NUMBER
1107 ,p_ENDING_NUMBER NUMBER
1108 ,p_SUBSCRIPTION_TYPE VARCHAR2
1109 ,p_DONOR_SP_ID NUMBER
1110 ,p_RECIPIENT_SP_ID NUMBER
1111 ,p_ROUTING_NUMBER VARCHAR2
1112 ,p_NEW_SP_DUE_DATE DATE
1113 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
1114 ,p_CUSTOMER_ID VARCHAR2
1115 ,p_CUSTOMER_NAME VARCHAR2
1116 ,p_CUSTOMER_TYPE VARCHAR2
1117 ,p_ADDRESS_LINE1 VARCHAR2
1118 ,p_ADDRESS_LINE2 VARCHAR2
1119 ,p_CITY VARCHAR2
1120 ,p_PHONE VARCHAR2
1121 ,p_FAX VARCHAR2
1122 ,p_EMAIL VARCHAR2
1123 ,p_PAGER VARCHAR2
1124 ,p_PAGER_PIN VARCHAR2
1125 ,p_INTERNET_ADDRESS VARCHAR2
1126 ,p_ZIP_CODE VARCHAR2
1127 ,p_COUNTRY VARCHAR2
1128 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
1129 ,p_CONTACT_NAME VARCHAR2
1130 ,p_RETAIN_TN_FLAG VARCHAR2
1131 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
1132 ,p_CNAM_ADDRESS VARCHAR2
1133 ,p_CNAM_SUBSYSTEM VARCHAR2
1134 ,p_ISVM_ADDRESS VARCHAR2
1135 ,p_ISVM_SUBSYSTEM VARCHAR2
1136 ,p_LIDB_ADDRESS VARCHAR2
1137 ,p_LIDB_SUBSYSTEM VARCHAR2
1138 ,p_CLASS_ADDRESS VARCHAR2
1139 ,p_CLASS_SUBSYSTEM VARCHAR2
1140 ,p_WSMSC_ADDRESS VARCHAR2
1141 ,p_WSMSC_SUBSYSTEM VARCHAR2
1142 ,p_RN_ADDRESS VARCHAR2
1143 ,p_RN_SUBSYSTEM VARCHAR2
1144 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
1145 ,p_ACTIVATION_DUE_DATE DATE
1146 ,p_ORDER_PRIORITY VARCHAR2
1147 ,p_SUBSEQUENT_PORT_FLAG VARCHAR2
1148 ,p_COMMENTS VARCHAR2
1149 ,p_NOTES VARCHAR2
1150 ,p_ORDER_ID IN NUMBER
1151 ,p_LINEITEM_ID IN NUMBER
1152 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1153 ,p_FA_INSTANCE_ID IN NUMBER
1154 ,x_ERROR_CODE OUT NOCOPY NUMBER
1155 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1156 )
1157 IS
1158 l_counter BINARY_INTEGER := 0;
1159 l_SP_ID NUMBER;
1160 l_diff NUMBER := (p_ending_number - p_starting_number);
1161 l_init NUMBER := p_starting_number;
1162 l_new_status_type_code xnp_sv_status_types_b.STATUS_TYPE_CODE%TYPE;
1163 l_nrc_id NUMBER := 0;
1164 l_assigned_sp_id NUMBER := 0;
1165 l_mediator_sp_id NUMBER := 0;
1166 l_pto_flag VARCHAR2(1);
1167 l_geo_id NUMBER := 0;
1168
1169 l_subscription_tn VARCHAR2(20) := NULL;
1170 l_sv_soa_id NUMBER := null;
1171 l_porting_id VARCHAR2(80) := NULL;
1172 l_number_range_id NUMBER := null;
1173 l_routing_number_id NUMBER;
1174
1175 BEGIN
1176 x_error_code := 0;
1177
1178 -- verify if its a valid number range
1179 -- Removed this validation call from XNP_STANDARD.CREATE_PORTING_ORDER and added here spusegao 04/11/2001
1180
1181 get_number_range_id( p_starting_number => p_starting_number,
1182 p_ending_number => p_ending_number,
1183 x_number_range_id => l_number_range_id,
1184 x_error_code => x_error_code,
1185 x_error_message => x_error_message
1186 );
1187 IF (x_error_code <> 0) THEN
1188 return;
1189 END IF;
1190
1191 -- Get the routing_number_id corresponding to the code
1192 -- Moved this validation call from XNP_STANDARD.CREATE_PORTING_ORDER and added here -- spusegao 04/11/2001
1193
1194 IF (p_ROUTING_NUMBER IS NOT NULL) THEN
1195
1196 GET_ROUTING_NUMBER_ID
1197 (p_routing_number
1198 ,l_ROUTING_NUMBER_ID
1199 ,x_ERROR_CODE
1200 ,x_ERROR_MESSAGE
1201 );
1202
1203 IF x_ERROR_CODE <> 0 THEN
1204 RETURN;
1205 END IF;
1206
1207 END IF;
1208
1209 --
1210 -- First get the ASSIGNED_SP_ID for that TN Range
1211 -- Then check if the owning sp id is the same as
1212 -- the recipient
1213 --
1214 l_pto_flag := 'N'; -- Default is 'not PTO'
1215
1216 xnp_core.get_assigned_sp_id
1217 (to_char(p_starting_number)
1218 ,to_char(p_ending_number)
1219 ,l_assigned_sp_id
1220 ,x_error_code
1221 ,x_error_message
1222 );
1223
1224 IF x_error_code <> 0 THEN
1225 RETURN;
1226 END IF;
1227
1228 IF l_assigned_sp_id = p_recipient_sp_id THEN
1229 l_pto_flag := 'Y';
1230 END IF;
1231
1232 -- Get the NRC id
1233
1234 xnp_core.get_nrc_id
1235 (to_char(p_starting_number)
1236 ,to_char(p_ending_number)
1237 ,l_mediator_sp_id
1238 ,x_error_code
1239 ,x_error_message );
1240
1241 IF x_error_code <> 0 THEN
1242 RETURN;
1243 END IF;
1244
1245
1246 IF (p_subsequent_port_flag = 'N') AND (l_pto_flag = 'N') THEN
1247 IF (p_donor_sp_id <> l_assigned_sp_id) THEN
1248 x_error_code := XNP_ERRORS.G_DONOR_NOT_ASSIGNED_TN;
1249 fnd_message.set_name('XNP','NUMRANGE_NOT_BELONGING_TO_DON');
1250 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_CREATE_REC_PORT_ORDER');
1251 fnd_message.set_token('SN',to_char(p_STARTING_NUMBER));
1252 fnd_message.set_token('EN',to_char(p_ENDING_NUMBER));
1253 fnd_message.set_token('DON',to_char(p_DONOR_SP_ID));
1254 x_error_message := fnd_message.get;
1255 RETURN;
1256 END IF;
1257 END IF;
1258
1259 -- Get the initial porting status from the profiles
1260
1261 l_new_status_type_code := g_default_porting_status;
1262
1263 -- fnd_profile.get
1264 -- (name => 'DEFAULT_PORTING_STATUS'
1265 -- ,val => l_new_status_type_code) ;
1266
1267 IF (l_new_status_type_code IS null) THEN
1268 x_error_code := xnp_errors.g_invalid_sv_status;
1269 fnd_message.set_name('XNP','XNP_CVU_INITIAL_STATUS_OF_SV');
1270 x_error_message := fnd_message.get;
1271 END IF;
1272
1273 FOR l_counter IN 0..l_diff
1274
1275 LOOP
1276
1277 l_subscription_tn := to_char(l_init+l_counter);
1278
1279 -- SELECT xnp_sv_soa_s.nextval
1280 -- INTO l_sv_soa_id
1281 -- FROM dual;
1282
1283 INSERT INTO xnp_sv_soa
1284 (sv_soa_id
1285 ,object_reference
1286 ,subscription_tn
1287 ,subscription_type
1288 ,donor_sp_id
1289 ,recipient_sp_id
1290 ,routing_number_id
1291 ,status_type_code
1292 ,pto_flag
1293 ,created_by_sp_id
1294 ,changed_by_sp_id
1295 ,mediator_sp_id
1296 ,old_sp_cutoff_due_date
1297 ,customer_id
1298 ,customer_name
1299 ,customer_type
1300 ,address_line1
1301 ,address_line2
1302 ,city
1303 ,phone
1304 ,fax
1305 ,email
1306 ,zip_code
1307 ,country
1308 ,new_sp_due_date
1309 ,old_sp_due_date
1310 ,customer_contact_req_flag
1311 ,contact_name
1312 ,retain_tn_flag
1313 ,retain_dir_info_flag
1314 ,pager
1315 ,pager_pin
1316 ,internet_address
1317 ,cnam_address
1318 ,cnam_subsystem
1319 ,isvm_address
1320 ,isvm_subsystem
1321 ,lidb_address
1322 ,lidb_subsystem
1323 ,class_address
1324 ,class_subsystem
1325 ,wsmsc_address
1326 ,wsmsc_subsystem
1327 ,rn_address
1328 ,rn_subsystem
1329 ,preorder_authorization_code
1330 ,activation_due_date
1331 ,order_priority
1332 ,comments
1333 ,notes
1334 ,created_date
1335 ,modified_date
1336 ,created_by
1337 ,creation_date
1338 ,last_updated_by
1339 ,last_update_date
1340 )
1341 VALUES
1342 (xnp_sv_soa_s.nextval
1343 ,p_porting_id -- obj ref
1344 ,l_subscription_tn -- subs TN
1345 ,'NP' -- subs type
1346 ,p_donor_sp_id
1347 ,p_recipient_sp_id
1348 ,l_routing_number_id
1349 ,l_new_status_type_code
1350 ,l_pto_flag
1351 ,p_recipient_sp_id
1352 ,p_recipient_sp_id
1353 ,l_mediator_sp_id
1354 ,p_old_sp_cutoff_due_date
1355 ,p_customer_id
1356 ,p_customer_name
1357 ,p_customer_type
1358 ,p_address_line1
1359 ,p_address_line2
1360 ,p_city
1361 ,p_phone
1362 ,p_fax
1363 ,p_email
1364 ,p_zip_code
1365 ,p_country
1366 ,p_new_sp_due_date
1367 ,p_new_sp_due_date
1368 ,p_customer_contact_req_flag
1369 ,p_contact_name
1370 ,p_retain_tn_flag
1371 ,p_retain_dir_info_flag
1372 ,p_pager
1373 ,p_pager_pin
1374 ,p_internet_address
1375 ,p_cnam_address
1376 ,p_cnam_subsystem
1377 ,p_isvm_address
1378 ,p_isvm_subsystem
1379 ,p_lidb_address
1380 ,p_lidb_subsystem
1381 ,p_class_address
1382 ,p_class_subsystem
1383 ,p_wsmsc_address
1384 ,p_wsmsc_subsystem
1385 ,p_rn_address
1386 ,p_rn_subsystem
1387 ,p_preorder_authorization_code
1388 ,p_activation_due_date
1389 ,p_order_priority
1390 ,p_comments
1391 ,p_notes
1392 ,sysdate
1393 ,sysdate
1394 ,fnd_global.user_id
1395 ,sysdate
1396 ,fnd_global.user_id
1397 ,sysdate
1398 ) RETURNING sv_soa_id INTO l_sv_soa_id;
1399
1400 -- Call CREATE_ORDER_MAPPING Procedure to create record in XNP_SV_ORDER_MAPPINGS table
1401
1402 CREATE_ORDER_MAPPING
1403 (p_ORDER_ID ,
1404 p_LINEITEM_ID ,
1405 p_WORKITEM_INSTANCE_ID,
1406 p_FA_INSTANCE_ID ,
1407 l_sv_soa_id ,
1408 null ,
1409 x_ERROR_CODE ,
1410 x_ERROR_MESSAGE
1411 );
1412
1413 END LOOP; -- case of new sp
1414
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 x_error_code := SQLCODE;
1418 fnd_message.set_name('XNP','STD_ERROR');
1419 fnd_message.set_token('ERROR_LOCN'
1420 ,'XNP_CORE.SOA_CREATE_REC_PORT_ORDER');
1421 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1422 x_error_message := fnd_message.get;
1423
1424 END SOA_CREATE_REC_PORT_ORDER;
1425
1426 PROCEDURE SOA_CREATE_NRC_PORT_ORDER
1427 (p_PORTING_ID VARCHAR2
1428 ,p_STARTING_NUMBER NUMBER
1429 ,p_ENDING_NUMBER NUMBER
1430 ,p_SUBSCRIPTION_TYPE VARCHAR2
1431 ,p_DONOR_SP_ID NUMBER
1432 ,p_RECIPIENT_SP_ID NUMBER
1433 ,p_ROUTING_NUMBER VARCHAR2
1434 ,p_NEW_SP_DUE_DATE DATE
1435 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
1436 ,p_CUSTOMER_ID VARCHAR2
1437 ,p_CUSTOMER_NAME VARCHAR2
1438 ,p_CUSTOMER_TYPE VARCHAR2
1439 ,p_ADDRESS_LINE1 VARCHAR2
1440 ,p_ADDRESS_LINE2 VARCHAR2
1441 ,p_CITY VARCHAR2
1442 ,p_PHONE VARCHAR2
1443 ,p_FAX VARCHAR2
1444 ,p_EMAIL VARCHAR2
1445 ,p_PAGER VARCHAR2
1446 ,p_PAGER_PIN VARCHAR2
1447 ,p_INTERNET_ADDRESS VARCHAR2
1448 ,p_ZIP_CODE VARCHAR2
1449 ,p_COUNTRY VARCHAR2
1450 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
1451 ,p_CONTACT_NAME VARCHAR2
1452 ,p_RETAIN_TN_FLAG VARCHAR2
1453 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
1454 ,p_CNAM_ADDRESS VARCHAR2
1455 ,p_CNAM_SUBSYSTEM VARCHAR2
1456 ,p_ISVM_ADDRESS VARCHAR2
1457 ,p_ISVM_SUBSYSTEM VARCHAR2
1458 ,p_LIDB_ADDRESS VARCHAR2
1459 ,p_LIDB_SUBSYSTEM VARCHAR2
1460 ,p_CLASS_ADDRESS VARCHAR2
1461 ,p_CLASS_SUBSYSTEM VARCHAR2
1462 ,p_WSMSC_ADDRESS VARCHAR2
1463 ,p_WSMSC_SUBSYSTEM VARCHAR2
1464 ,p_RN_ADDRESS VARCHAR2
1465 ,p_RN_SUBSYSTEM VARCHAR2
1466 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
1467 ,p_ACTIVATION_DUE_DATE DATE
1468 ,p_ORDER_PRIORITY VARCHAR2
1469 ,p_SUBSEQUENT_PORT_FLAG VARCHAR2
1470 ,p_COMMENTS VARCHAR2
1471 ,p_NOTES VARCHAR2
1472 ,p_ORDER_ID IN NUMBER
1473 ,p_LINEITEM_ID IN NUMBER
1474 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1475 ,p_FA_INSTANCE_ID IN NUMBER
1476 ,p_VALIDATION_FLAG IN VARCHAR2
1477 ,x_ERROR_CODE OUT NOCOPY NUMBER
1478 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1479 )
1480 IS
1481 l_counter BINARY_INTEGER := 0;
1482 l_sp_id NUMBER;
1483 l_diff NUMBER := (p_ending_number - p_starting_number);
1484 l_init NUMBER := p_starting_number;
1485 l_new_status_type_code xnp_sv_status_types_b.STATUS_TYPE_CODE%TYPE;
1486 l_nrc_id NUMBER := 0;
1487 l_assigned_sp_id NUMBER := 0;
1488 l_mediator_sp_id NUMBER := 0;
1489 l_pto_flag VARCHAR2(1);
1490 l_geo_id NUMBER := 0;
1491
1492 l_subscription_tn VARCHAR2(20) := null;
1493 l_sv_soa_id NUMBER := null;
1494 l_porting_id VARCHAR2(80) := null;
1495 l_number_range_id NUMBER := null;
1496 l_routing_number_id NUMBER;
1497
1498 CURSOR c_routing_id_exists (l_routing_number IN NUMBER,
1499 l_sp_id IN NUMBER) IS
1500 SELECT routing_number_id
1501 FROM xnp_routing_numbers
1502 WHERE routing_number = l_routing_number
1503 AND sp_id = l_sp_id
1504 AND interconnect_type = 'LRN' ;
1505
1506 BEGIN
1507
1508 x_error_code := 0;
1509
1510 IF p_VALIDATION_FLAG = 'Y' THEN
1511
1512
1513 -- verify if its a valid number range
1514 -- Moved this validation call from XNP_STANDARD.CREATE_PORTING_ORDER and added here spusegao 04/11/2001
1515
1516 get_number_range_id( p_starting_number => p_starting_number,
1517 p_ending_number => p_ending_number,
1518 x_number_range_id => l_number_range_id,
1519 x_error_code => x_error_code,
1520 x_error_message => x_error_message
1521 );
1522
1523 IF (x_error_code <> 0) THEN
1524 return;
1525 END IF;
1526
1527 -- Get the routing_number_id corresponding to the code
1528 -- Moved this validation call from XNP_STANDARD.CREATE_PORTING_ORDER and added here -- spusegao 04/11/2001
1529
1530 IF (p_ROUTING_NUMBER IS NOT NULL) THEN
1531
1532 GET_ROUTING_NUMBER_ID
1533 (p_ROUTING_NUMBER
1534 ,l_ROUTING_NUMBER_ID
1535 ,x_ERROR_CODE
1536 ,x_ERROR_MESSAGE
1537 );
1538
1539 IF x_ERROR_CODE <> 0 THEN
1540 RETURN;
1541 END IF;
1542
1543 END IF;
1544
1545 --
1546 -- First get the ASSIGNED_SP_ID for that TN Range
1547 -- Then check if the owning sp id is the same as
1548 -- the recipient
1549 --
1550 l_pto_flag := 'N'; -- Default is 'not PTO'
1551
1552 xnp_core.get_assigned_sp_id
1553 (to_char(p_starting_number)
1554 ,to_char(p_ending_number)
1555 ,l_assigned_sp_id
1556 ,x_error_code
1557 ,x_error_message
1558 );
1559
1560 IF x_error_code <> 0 THEN
1561 RETURN;
1562 END IF;
1563
1564 IF l_assigned_sp_id = p_recipient_sp_id THEN
1565 l_pto_flag := 'Y';
1566 END IF;
1567
1568 -- Get the NRC id
1569
1570 xnp_core.get_nrc_id
1571 (to_char(p_starting_number)
1572 ,to_char(p_ending_number)
1573 ,l_mediator_sp_id
1574 ,x_error_code
1575 ,x_error_message
1576 );
1577
1578 IF x_error_code <> 0 THEN
1579 RETURN;
1580 END IF;
1581
1582
1583
1584 IF (p_subsequent_port_flag = 'N') AND (l_pto_flag = 'N') THEN
1585
1586 IF (p_donor_sp_id <> l_assigned_sp_id) THEN
1587 x_error_code := XNP_ERRORS.G_DONOR_NOT_ASSIGNED_TN;
1588 fnd_message.set_name('XNP','NUMRANGE_NOT_BELONGING_TO_DON');
1589 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_CREATE_NRC_PORT_ORDER');
1590 fnd_message.set_token('SN',to_char(p_STARTING_NUMBER));
1591 fnd_message.set_token('EN',to_char(p_ENDING_NUMBER));
1592 fnd_message.set_token('DON',to_char(p_DONOR_SP_ID));
1593
1594 x_error_message := fnd_message.get;
1595 RETURN;
1596 END IF;
1597 END IF;
1598
1599 ELSE
1600
1601 l_pto_flag := 'N'; -- Default is 'not PTO'
1602
1603 IF (p_ROUTING_NUMBER IS NOT NULL) THEN
1604
1605 OPEN c_routing_id_exists (p_routing_number , p_recipient_sp_id ) ;
1606 FETCH c_routing_id_exists INTO l_routing_number_id ;
1607
1608 IF c_routing_id_exists%NOTFOUND THEN
1609
1610 -- Create a routing record in the XNP_ROUTING_NUMBERS table
1611
1612 INSERT INTO XNP_ROUTING_NUMBERS
1613 (
1614 ROUTING_NUMBER_ID ,
1615 OBJECT_REFERENCE ,
1616 INTERCONNECT_TYPE ,
1617 SP_ID ,
1618 ROUTING_NUMBER ,
1619 ACTIVE_FLAG ,
1620 STATUS ,
1621 CREATED_BY ,
1622 CREATION_DATE ,
1623 LAST_UPDATED_BY ,
1624 LAST_UPDATE_DATE ,
1625 LAST_UPDATE_LOGIN
1626 )
1627 VALUES
1628 (
1629 XNP_ROUTING_NUMBERS_S.nextval ,
1630 p_RECIPIENT_SP_ID||'-'||p_ROUTING_NUMBER||'-'||'LRN',
1631 'LRN' ,
1632 p_RECIPIENT_SP_ID ,
1633 p_ROUTING_NUMBER ,
1634 'Y' ,
1635 null ,
1636 fnd_global.user_id ,
1637 sysdate ,
1638 fnd_global.user_id ,
1639 sysdate ,
1640 fnd_global.user_id
1641 ) RETURNING routing_number_id INTO l_routing_number_id ;
1642 END IF;
1643
1644 CLOSE c_routing_id_exists ;
1645
1646 END IF;
1647
1648 END IF;
1649
1650 -- Get the initial porting status from the profiles
1651
1652 l_new_status_type_code := g_default_porting_status;
1653
1654 --- fnd_profile.get
1655 --- (name => 'DEFAULT_PORTING_STATUS'
1656 --- ,val => l_new_status_type_code) ;
1657
1658 IF (l_new_status_type_code IS null) THEN
1659 x_error_code := xnp_errors.g_invalid_sv_status;
1660 fnd_message.set_name('XNP','XNP_CVU_INITIAL_STATUS_OF_SV');
1661 x_error_message := fnd_message.get;
1662 END IF;
1663
1664
1665 FOR l_counter IN 0..l_diff
1666 LOOP
1667
1668 l_subscription_tn := to_char(l_init+l_counter);
1669
1670 --- SELECT xnp_sv_soa_s.nextval
1671 --- INTO l_sv_soa_id
1672 --- FROM dual;
1673
1674 INSERT INTO XNP_SV_SOA
1675 (sv_soa_id
1676 ,object_reference
1677 ,subscription_tn
1678 ,subscription_type
1679 ,donor_sp_id
1680 ,recipient_sp_id
1681 ,routing_number_id
1682 ,status_type_code
1683 ,pto_flag
1684 ,created_by_sp_id
1685 ,changed_by_sp_id
1686 ,mediator_sp_id
1687 ,old_sp_cutoff_due_date
1688 ,customer_id
1689 ,customer_name
1690 ,customer_type
1691 ,address_line1
1692 ,address_line2
1693 ,city
1694 ,phone
1695 ,fax
1696 ,email
1697 ,zip_code
1698 ,country
1699 ,new_sp_due_date
1700 ,old_sp_due_date
1701 ,customer_contact_req_flag
1702 ,contact_name
1703 ,retain_tn_flag
1704 ,retain_dir_info_flag
1705 ,pager
1706 ,pager_pin
1707 ,internet_address
1708 ,cnam_address
1709 ,cnam_subsystem
1710 ,isvm_address
1711 ,isvm_subsystem
1712 ,lidb_address
1713 ,lidb_subsystem
1714 ,class_address
1715 ,class_subsystem
1716 ,wsmsc_address
1717 ,wsmsc_subsystem
1718 ,rn_address
1719 ,rn_subsystem
1720 ,preorder_authorization_code
1721 ,activation_due_date
1722 ,order_priority
1723 ,comments
1724 ,notes
1725 ,created_date
1726 ,modified_date
1727 ,created_by
1728 ,creation_date
1729 ,last_updated_by
1730 ,last_update_date
1731 )
1732 VALUES
1733 (xnp_sv_soa_s.nextval
1734 ,p_porting_id -- obj ref
1735 ,l_subscription_tn -- subs TN
1736 ,'NP' -- subs type
1737 ,p_donor_sp_id
1738 ,p_recipient_sp_id
1739 ,l_routing_number_id
1740 ,l_new_status_type_code
1741 ,l_pto_flag
1742 ,l_mediator_sp_id
1743 ,p_recipient_sp_id
1744 ,l_mediator_sp_id
1745 ,p_old_sp_cutoff_due_date
1746 ,p_customer_id
1747 ,p_customer_name
1748 ,p_customer_type
1749 ,p_address_line1
1750 ,p_address_line2
1751 ,p_city
1752 ,p_phone
1753 ,p_fax
1754 ,p_email
1755 ,p_zip_code
1756 ,p_country
1757 ,p_new_sp_due_date
1758 ,p_new_sp_due_date
1759 ,p_customer_contact_req_flag
1760 ,p_contact_name
1761 ,p_retain_tn_flag
1762 ,p_retain_dir_info_flag
1763 ,p_pager
1764 ,p_pager_pin
1765 ,p_internet_address
1766 ,p_cnam_address
1767 ,p_cnam_subsystem
1768 ,p_isvm_address
1769 ,p_isvm_subsystem
1770 ,p_lidb_address
1771 ,p_lidb_subsystem
1772 ,p_class_address
1773 ,p_class_subsystem
1774 ,p_wsmsc_address
1775 ,p_wsmsc_subsystem
1776 ,p_rn_address
1777 ,p_rn_subsystem
1778 ,p_preorder_authorization_code
1779 ,p_activation_due_date
1780 ,p_order_priority
1781 ,p_comments
1782 ,p_notes
1783 ,sysdate
1784 ,sysdate
1785 ,fnd_global.user_id
1786 ,sysdate
1787 ,fnd_global.user_id
1788 ,sysdate
1789 ) RETURNING sv_soa_id INTO l_sv_soa_id;
1790
1791
1792 -- Call CREATE_ORDER_MAPPING Procedure to create record in XNP_SV_ORDER_MAPPINGS table
1793
1794 CREATE_ORDER_MAPPING
1795 (p_ORDER_ID ,
1796 p_LINEITEM_ID ,
1797 p_WORKITEM_INSTANCE_ID,
1798 p_FA_INSTANCE_ID ,
1799 l_sv_soa_id ,
1800 null ,
1801 x_ERROR_CODE ,
1802 x_ERROR_MESSAGE
1803 );
1804
1805 END LOOP; -- case of new sp
1806
1807 EXCEPTION
1808 WHEN OTHERS THEN
1809 x_error_code := SQLCODE;
1810 fnd_message.set_name('XNP','STD_ERROR');
1811 fnd_message.set_token('ERROR_LOCN'
1812 ,'XNP_CORE.SOA_CREATE_NRC_PORT_ORDER');
1813 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1814 x_error_message := fnd_message.get;
1815
1816 END SOA_CREATE_NRC_PORT_ORDER;
1817
1818 PROCEDURE SOA_CREATE_DON_PORT_ORDER
1819 (p_PORTING_ID VARCHAR2
1820 ,p_STARTING_NUMBER NUMBER
1821 ,p_ENDING_NUMBER NUMBER
1822 ,p_SUBSCRIPTION_TYPE VARCHAR2
1823 ,p_DONOR_SP_ID NUMBER
1824 ,p_RECIPIENT_SP_ID NUMBER
1825 ,p_ROUTING_NUMBER VARCHAR2
1826 ,p_NEW_SP_DUE_DATE DATE
1827 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
1828 ,p_CUSTOMER_ID VARCHAR2
1829 ,p_CUSTOMER_NAME VARCHAR2
1830 ,p_CUSTOMER_TYPE VARCHAR2
1831 ,p_ADDRESS_LINE1 VARCHAR2
1832 ,p_ADDRESS_LINE2 VARCHAR2
1833 ,p_CITY VARCHAR2
1834 ,p_PHONE VARCHAR2
1835 ,p_FAX VARCHAR2
1836 ,p_EMAIL VARCHAR2
1837 ,p_PAGER VARCHAR2
1838 ,p_PAGER_PIN VARCHAR2
1839 ,p_INTERNET_ADDRESS VARCHAR2
1840 ,p_ZIP_CODE VARCHAR2
1841 ,p_COUNTRY VARCHAR2
1842 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
1843 ,p_CONTACT_NAME VARCHAR2
1844 ,p_RETAIN_TN_FLAG VARCHAR2
1845 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
1846 ,p_CNAM_ADDRESS VARCHAR2
1847 ,p_CNAM_SUBSYSTEM VARCHAR2
1848 ,p_ISVM_ADDRESS VARCHAR2
1849 ,p_ISVM_SUBSYSTEM VARCHAR2
1850 ,p_LIDB_ADDRESS VARCHAR2
1851 ,p_LIDB_SUBSYSTEM VARCHAR2
1852 ,p_CLASS_ADDRESS VARCHAR2
1853 ,p_CLASS_SUBSYSTEM VARCHAR2
1854 ,p_WSMSC_ADDRESS VARCHAR2
1855 ,p_WSMSC_SUBSYSTEM VARCHAR2
1856 ,p_RN_ADDRESS VARCHAR2
1857 ,p_RN_SUBSYSTEM VARCHAR2
1858 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
1859 ,p_ACTIVATION_DUE_DATE DATE
1860 ,p_ORDER_PRIORITY VARCHAR2
1861 ,p_SUBSEQUENT_PORT_FLAG VARCHAR2
1862 ,p_COMMENTS VARCHAR2
1863 ,p_NOTES VARCHAR2
1864 ,p_ORDER_ID IN NUMBER
1865 ,p_LINEITEM_ID IN NUMBER
1866 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1867 ,p_FA_INSTANCE_ID IN NUMBER
1868 ,x_ERROR_CODE OUT NOCOPY NUMBER
1869 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1870 )
1871 IS
1872 l_counter BINARY_INTEGER;
1873 l_SP_ID NUMBER;
1874 l_diff NUMBER := (p_ENDING_NUMBER - p_STARTING_NUMBER);
1875 l_init NUMBER := p_STARTING_NUMBER;
1876 l_NEW_STATUS_TYPE_CODE xnp_sv_status_types_b.STATUS_TYPE_CODE%TYPE;
1877 l_NRC_ID NUMBER := 0;
1878 l_ASSIGNED_SP_ID NUMBER := 0;
1879 l_MEDIATOR_SP_ID NUMBER := 0;
1880 l_PTO_FLAG VARCHAR2(1);
1881 l_GEO_ID NUMBER := 0;
1882
1883 l_PORTING_ID VARCHAR2(80) := NULL;
1884 l_SUBSCRIPTION_TN VARCHAR2(20) := NULL;
1885 l_sv_soa_id NUMBER := null;
1886 l_number_range_id NUMBER := null;
1887 l_routing_number_id NUMBER;
1888
1889 BEGIN
1890
1891 x_ERROR_CODE := 0;
1892
1893
1894 -- verify if its a valid number range
1895 -- Moved this validation call from XNP_STANDARD.CREATE_PORTING_ORDER and added here -- spusegao 04/11/2001
1896
1897 get_number_range_id( p_starting_number => p_starting_number,
1898 p_ending_number => p_ending_number,
1899 x_number_range_id => l_number_range_id,
1900 x_error_code => x_error_code,
1901 x_error_message => x_error_message
1902 );
1903 IF (x_error_code <> 0) THEN
1904 return;
1905 END IF;
1906
1907 -- Get the routing_number_id corresponding to the code
1908 -- Moved this validation call from XNP_STANDARD.CREATE_PORTING_ORDER and added here -- spusegao 04/11/2001
1909
1910 IF (p_ROUTING_NUMBER IS NOT NULL) THEN
1911
1912 GET_ROUTING_NUMBER_ID
1913 (p_routing_number
1914 ,l_ROUTING_NUMBER_ID
1915 ,x_ERROR_CODE
1916 ,x_ERROR_MESSAGE
1917 );
1918
1919 IF x_ERROR_CODE <> 0 THEN
1920 RETURN;
1921 END IF;
1922
1923 END IF;
1924
1925
1926 -- Get the initial porting status from the profiles
1927
1928 l_new_status_type_code := g_default_porting_status;
1929
1930 /***
1931 FND_PROFILE.GET
1932 (NAME => 'DEFAULT_PORTING_STATUS'
1933 ,VAL => l_new_status_type_code) ;
1934 ***/
1935
1936 IF (l_new_status_type_code IS null) THEN
1937 x_error_code := xnp_errors.g_invalid_sv_status;
1938 fnd_message.set_name('XNP','XNP_CVU_INITIAL_STATUS_OF_SV');
1939 x_error_message := fnd_message.get;
1940 END IF;
1941
1942
1943 --
1944 -- First get the ASSIGNED_SP_ID for that TN Range
1945 -- Then check if the owning sp id is the same as
1946 -- the recipient
1947 --
1948 l_PTO_FLAG := 'N'; -- Default is 'not PTO'
1949
1950 XNP_CORE.GET_ASSIGNED_SP_ID
1951 (to_char(p_starting_number)
1952 ,to_char(p_ending_number)
1953 ,l_ASSIGNED_SP_ID
1954 ,x_ERROR_CODE
1955 ,x_ERROR_MESSAGE
1956 );
1957 IF x_ERROR_CODE <> 0
1958 THEN
1959 RETURN;
1960 END IF;
1961
1962 IF l_ASSIGNED_SP_ID = p_RECIPIENT_SP_ID
1963 THEN
1964 l_PTO_FLAG := 'Y';
1965 END IF;
1966
1967 -- Get the NRC id
1968
1969 XNP_CORE.GET_NRC_ID
1970 (to_char(p_STARTING_NUMBER)
1971 ,to_char(p_ENDING_NUMBER)
1972 ,l_MEDIATOR_SP_ID
1973 ,x_ERROR_CODE
1974 ,x_ERROR_MESSAGE
1975 );
1976 IF x_ERROR_CODE <> 0
1977 THEN
1978 RETURN;
1979 END IF;
1980
1981
1982 IF (p_SUBSEQUENT_PORT_FLAG = 'N') AND (l_PTO_FLAG = 'N') THEN
1983 IF (p_DONOR_SP_ID <> l_ASSIGNED_SP_ID) THEN
1984 x_ERROR_CODE := XNP_ERRORS.G_DONOR_NOT_ASSIGNED_TN;
1985
1986 fnd_message.set_name('XNP','NUMRANGE_NOT_BELONGING_TO_DON');
1987 fnd_message.set_token('ERROR_LOCN'
1988 ,'XNP_CORE.SOA_CREATE_DON_PORT_ORDER');
1989 fnd_message.set_token('SN',to_char(p_STARTING_NUMBER));
1990 fnd_message.set_token('EN',to_char(p_ENDING_NUMBER));
1991 fnd_message.set_token('DON',to_char(p_DONOR_SP_ID));
1992
1993 x_error_message := fnd_message.get;
1994 RETURN;
1995 END IF;
1996 END IF;
1997
1998 FOR l_counter IN
1999 0..l_diff
2000 LOOP
2001
2002 l_SUBSCRIPTION_TN := to_char(l_init+l_counter);
2003
2004 /***
2005 SELECT xnp_sv_soa_s.nextval
2006 INTO l_sv_soa_id
2007 FROM dual;
2008 ***/
2009
2010 INSERT INTO XNP_SV_SOA
2011 (SV_SOA_ID
2012 ,OBJECT_REFERENCE
2013 ,SUBSCRIPTION_TN
2014 ,SUBSCRIPTION_TYPE
2015 ,DONOR_SP_ID
2016 ,RECIPIENT_SP_ID
2017 ,ROUTING_NUMBER_ID
2018 ,STATUS_TYPE_CODE
2019 ,PTO_FLAG
2020 ,CREATED_BY_SP_ID
2021 ,CHANGED_BY_SP_ID
2022 ,MEDIATOR_SP_ID
2023 ,NEW_SP_DUE_DATE
2024 ,OLD_SP_DUE_DATE
2025 ,OLD_SP_CUTOFF_DUE_DATE
2026 ,CUSTOMER_ID
2027 ,CUSTOMER_NAME
2028 ,CUSTOMER_TYPE
2029 ,ADDRESS_LINE1
2030 ,ADDRESS_LINE2
2031 ,CITY
2032 ,PHONE
2033 ,FAX
2034 ,EMAIL
2035 ,ZIP_CODE
2036 ,COUNTRY
2037 ,CUSTOMER_CONTACT_REQ_FLAG
2038 ,CONTACT_NAME
2039 ,RETAIN_TN_FLAG
2040 ,RETAIN_DIR_INFO_FLAG
2041 ,PAGER
2042 ,PAGER_PIN
2043 ,INTERNET_ADDRESS
2044 ,CNAM_ADDRESS
2045 ,CNAM_SUBSYSTEM
2046 ,ISVM_ADDRESS
2047 ,ISVM_SUBSYSTEM
2048 ,LIDB_ADDRESS
2049 ,LIDB_SUBSYSTEM
2050 ,CLASS_ADDRESS
2051 ,CLASS_SUBSYSTEM
2052 ,WSMSC_ADDRESS
2053 ,WSMSC_SUBSYSTEM
2054 ,RN_ADDRESS
2055 ,RN_SUBSYSTEM
2056 ,PREORDER_AUTHORIZATION_CODE
2057 ,ACTIVATION_DUE_DATE
2058 ,ORDER_PRIORITY
2059 ,COMMENTS
2060 ,NOTES
2061 ,CREATED_DATE
2062 ,MODIFIED_DATE
2063 ,CREATED_BY
2064 ,CREATION_DATE
2065 ,LAST_UPDATED_BY
2066 ,LAST_UPDATE_DATE
2067 )
2068 VALUES
2069 (xnp_sv_soa_s.nextval
2070 ,p_PORTING_ID
2071 ,l_SUBSCRIPTION_TN -- telephone number
2072 ,'NP' -- subs type
2073 ,p_DONOR_SP_ID
2074 ,p_RECIPIENT_SP_ID
2075 ,l_ROUTING_NUMBER_ID
2076 ,l_NEW_STATUS_TYPE_CODE
2077 ,l_PTO_FLAG
2078 ,p_DONOR_SP_ID
2079 ,p_DONOR_SP_ID
2080 ,l_MEDIATOR_SP_ID
2081 ,p_NEW_SP_DUE_DATE
2082 ,p_NEW_SP_DUE_DATE
2083 ,p_OLD_SP_CUTOFF_DUE_DATE
2084 ,p_CUSTOMER_ID
2085 ,p_CUSTOMER_NAME
2086 ,p_CUSTOMER_TYPE
2087 ,p_ADDRESS_LINE1
2088 ,p_ADDRESS_LINE2
2089 ,p_CITY
2090 ,p_PHONE
2091 ,p_FAX
2092 ,p_EMAIL
2093 ,p_ZIP_CODE
2094 ,p_COUNTRY
2095 ,p_CUSTOMER_CONTACT_REQ_FLAG
2096 ,p_CONTACT_NAME
2097 ,p_RETAIN_TN_FLAG
2098 ,p_RETAIN_DIR_INFO_FLAG
2099 ,p_PAGER
2100 ,p_PAGER_PIN
2101 ,p_INTERNET_ADDRESS
2102 ,p_CNAM_ADDRESS
2103 ,p_CNAM_SUBSYSTEM
2104 ,p_ISVM_ADDRESS
2105 ,p_ISVM_SUBSYSTEM
2106 ,p_LIDB_ADDRESS
2107 ,p_LIDB_SUBSYSTEM
2108 ,p_CLASS_ADDRESS
2109 ,p_CLASS_SUBSYSTEM
2110 ,p_WSMSC_ADDRESS
2111 ,p_WSMSC_SUBSYSTEM
2112 ,p_RN_ADDRESS
2113 ,p_RN_SUBSYSTEM
2114 ,p_PREORDER_AUTHORIZATION_CODE
2115 ,p_ACTIVATION_DUE_DATE
2116 ,p_ORDER_PRIORITY
2117 ,p_COMMENTS
2118 ,p_NOTES
2119 ,SYSDATE
2120 ,SYSDATE
2121 ,FND_GLOBAL.USER_ID
2122 ,SYSDATE
2123 ,FND_GLOBAL.USER_ID
2124 ,SYSDATE
2125 ) RETURNING sv_soa_id INTO l_sv_soa_id;
2126
2127
2128 -- Call CREATE_ORDER_MAPPING Procedure to create record in XNP_SV_ORDER_MAPPINGS table
2129
2130 CREATE_ORDER_MAPPING
2131 (p_ORDER_ID ,
2132 p_LINEITEM_ID ,
2133 p_WORKITEM_INSTANCE_ID,
2134 p_FA_INSTANCE_ID ,
2135 l_sv_soa_id ,
2136 null ,
2137 x_ERROR_CODE ,
2138 x_ERROR_MESSAGE
2139 );
2140
2141 END LOOP; -- case of new sp
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 x_ERROR_CODE := SQLCODE;
2146 fnd_message.set_name('XNP','STD_ERROR');
2147 fnd_message.set_token('ERROR_LOCN'
2148 ,'XNP_CORE.SOA_CREATE_DON_PORT_ORDER');
2149 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2150 x_error_message := fnd_message.get;
2151
2152 END SOA_CREATE_DON_PORT_ORDER;
2153
2154 PROCEDURE SOA_CHECK_NOTIFY_DIR_SVS
2155 (p_PORTING_ID VARCHAR2
2156 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
2157 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
2158 ,x_ERROR_CODE OUT NOCOPY NUMBER
2159 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2160 )
2161 IS
2162
2163 l_RETAIN_DIR_INFO VARCHAR2(1);
2164
2165 CURSOR c_CHECK_STATUS IS
2166 SELECT retain_dir_info_flag
2167 FROM xnp_sv_soa
2168 WHERE object_reference=p_porting_id;
2169
2170 BEGIN
2171 x_ERROR_CODE := 0;
2172
2173 OPEN c_CHECK_STATUS;
2174 FETCH c_CHECK_STATUS INTO x_CHECK_STATUS;
2175
2176 IF c_CHECK_STATUS%NOTFOUND THEN
2177 raise NO_DATA_FOUND;
2178 END IF;
2179
2180 CLOSE c_CHECK_STATUS;
2181
2182 EXCEPTION
2183 WHEN NO_DATA_FOUND THEN
2184 x_ERROR_CODE := SQLCODE;
2185 fnd_message.set_name('XNP','STD_GET_FAILED');
2186 fnd_message.set_token('FAILED_PROC','XNP_CORE.SOA_CHECK_NOTIFY_DIR_SVS');
2187 fnd_message.set_token('ATTRNAME','RETAIN_DIR_INFO_FLAG');
2188 fnd_message.set_token('KEY','PORTING_ID');
2189 fnd_message.set_token('VALUE',p_PORTING_ID);
2190 x_error_message :=
2191 fnd_message.get;
2192 x_ERROR_MESSAGE := x_error_message||':'||SQLERRM;
2193
2194 fnd_message.set_name('XNP','NOTIFY_DIR_SVS_ERR');
2195 fnd_message.set_token('ERROR_TEXT',x_error_message);
2196 x_error_message := fnd_message.get;
2197
2198 IF c_CHECK_STATUS%ISOPEN THEN
2199 CLOSE c_CHECK_STATUS;
2200 END IF;
2201
2202 WHEN OTHERS THEN
2203 x_ERROR_CODE := SQLCODE;
2204 fnd_message.set_name('XNP','STD_ERROR');
2205 fnd_message.set_token('ERROR_LOCN'
2206 ,'XNP_CORE.SOA_CHECK_NOTIFY_DIR_SVS');
2207 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2208 x_error_message := fnd_message.get;
2209
2210 IF c_CHECK_STATUS%ISOPEN THEN
2211 CLOSE c_CHECK_STATUS;
2212 END IF;
2213
2214 END SOA_CHECK_NOTIFY_DIR_SVS ;
2215
2216
2217 PROCEDURE SOA_CHECK_IF_INITIAL_DONOR
2218 (p_DONOR_SP_ID NUMBER
2219 ,p_STARTING_NUMBER VARCHAR2
2220 ,p_ENDING_NUMBER VARCHAR2
2221 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
2222 ,x_ERROR_CODE OUT NOCOPY NUMBER
2223 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2224 )
2225 IS
2226 l_SERVING_SP_ID NUMBER;
2227 BEGIN
2228 x_ERROR_CODE := 0;
2229 x_ERROR_MESSAGE := NULL;
2230 x_CHECK_STATUS := 'N';
2231
2232
2233 XNP_CORE.GET_ASSIGNED_SP_ID
2234 (p_STARTING_NUMBER
2235 ,p_ENDING_NUMBER
2236 ,l_SERVING_SP_ID
2237 ,x_ERROR_CODE
2238 ,x_ERROR_MESSAGE
2239 );
2240
2241 IF x_ERROR_CODE <> 0 THEN
2242 RETURN;
2243 END IF;
2244
2245 IF l_SERVING_SP_ID = p_DONOR_SP_ID THEN
2246 x_CHECK_STATUS := 'Y';
2247 END IF;
2248
2249 EXCEPTION
2250 WHEN OTHERS THEN
2251 x_ERROR_CODE := SQLCODE;
2252 fnd_message.set_name('XNP','STD_ERROR');
2253 fnd_message.set_token('ERROR_LOCN'
2254 ,'XNP_CORE.SOA_CHECK_IF_INITIAL_DONOR');
2255 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2256 x_error_message := fnd_message.get;
2257
2258 END SOA_CHECK_IF_INITIAL_DONOR;
2259
2260 PROCEDURE SOA_UPDATE_CHARGING_INFO
2261 (p_STARTING_NUMBER VARCHAR2
2262 ,p_ENDING_NUMBER VARCHAR2
2263 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
2264 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
2265 ,p_INVOICE_DUE_DATE DATE
2266 ,p_CHARGING_INFO VARCHAR2
2267 ,p_BILLING_ID NUMBER
2268 ,p_USER_LOCTN_VALUE VARCHAR2
2269 ,p_USER_LOCTN_TYPE VARCHAR2
2270 ,p_ORDER_ID IN NUMBER
2271 ,p_LINEITEM_ID IN NUMBER
2272 ,p_WORKITEM_INSTANCE_ID IN NUMBER
2273 ,p_FA_INSTANCE_ID IN NUMBER
2274 ,x_ERROR_CODE OUT NOCOPY NUMBER
2275 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2276 )
2277 IS
2278 l_counter BINARY_INTEGER := 0;
2279 l_SV_ID XNP_SV_SOA.SV_SOA_ID%TYPE;
2280 l_PHASE_INDICATOR XNP_SV_STATUS_TYPES_B.PHASE_INDICATOR%TYPE;
2281 l_STARTING_NUMBER VARCHAR2(80) := null;
2282 l_ENDING_NUMBER VARCHAR2(80) := null;
2283
2284 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
2285 l_sv_soa_id SV_SOA_ID_TAB;
2286 i BINARY_INTEGER;
2287
2288 BEGIN
2289
2290 x_ERROR_CODE := 0;
2291
2292
2293 l_starting_number := to_char(to_number(p_starting_number));
2294 l_ending_number := to_char(to_number(p_ending_number));
2295
2296 -- Get the phase corresponding to this 'p_CUR_STATUS_TYPE_CODE'
2297
2298 XNP_CORE.GET_PHASE_FOR_STATUS
2299 (p_CUR_STATUS_TYPE_CODE
2300 ,l_PHASE_INDICATOR
2301 ,x_ERROR_CODE
2302 ,x_ERROR_MESSAGE
2303 );
2304
2305 IF x_ERROR_CODE <> 0 THEN
2306 RETURN;
2307 END IF;
2308
2309 --
2310 -- For each TN Get the SVid which is in this phase
2311 -- and update the invoice info to the
2312 -- given value
2313 --
2314
2315 SELECT sv_soa_id BULK COLLECT
2316 INTO l_sv_soa_id
2317 FROM xnp_sv_soa soa,
2318 xnp_sv_status_types_b sta
2319 WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
2320 AND sta.phase_indicator = l_phase_indicator
2321 AND sta.status_type_code = soa.status_type_code;
2322
2323 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2324
2325 UPDATE xnp_sv_soa soa
2326 SET soa.invoice_due_date = p_invoice_due_date,
2327 soa.charging_info = p_charging_info,
2328 soa.user_loctn_type = p_user_loctn_type,
2329 soa.user_loctn_value = p_user_loctn_value,
2330 soa.modified_date = sysdate,
2331 soa.last_updated_by = fnd_global.user_id,
2332 soa.last_update_date = sysdate
2333 WHERE soa.sv_soa_id = l_sv_soa_id(i);
2334
2335 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
2336
2337 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2338
2339
2340 INSERT INTO XNP_SV_ORDER_MAPPINGS
2341 (sv_order_mapping_id ,
2342 sv_soa_id ,
2343 order_id ,
2344 workitem_instance_id ,
2345 created_by ,
2346 creation_date ,
2347 last_updated_by ,
2348 last_update_date
2349 )
2350 VALUES
2351 (XNP_SV_ORDER_MAPPINGS_S.nextval,
2352 l_sv_soa_id(i) ,
2353 p_order_id ,
2354 p_workitem_instance_id ,
2355 fnd_global.user_id ,
2356 sysdate ,
2357 fnd_global.user_id ,
2358 sysdate
2359 );
2360
2361 EXCEPTION
2362 WHEN dup_val_on_index THEN
2363 null;
2364 WHEN OTHERS THEN
2365 x_ERROR_CODE := SQLCODE;
2366 fnd_message.set_name('XNP','STD_ERROR');
2367 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_CHARGING_INFO');
2368 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2369 x_error_message := fnd_message.get;
2370
2371 fnd_message.set_name('XNP','UPD_CHARGING_INFO_ERR');
2372 fnd_message.set_token('ERROR_TEXT',x_error_message);
2373 x_error_message := fnd_message.get;
2374
2375 END SOA_UPDATE_CHARGING_INFO;
2376
2377 PROCEDURE SMS_INSERT_FE_MAP
2378 (p_STARTING_NUMBER NUMBER
2379 ,p_ENDING_NUMBER NUMBER
2380 ,p_FE_ID NUMBER
2381 ,p_FEATURE_TYPE VARCHAR2
2382 ,p_ORDER_ID IN NUMBER
2383 ,p_LINEITEM_ID IN NUMBER
2384 ,p_WORKITEM_INSTANCE_ID IN NUMBER
2385 ,p_FA_INSTANCE_ID IN NUMBER
2386 ,x_ERROR_CODE OUT NOCOPY NUMBER
2387 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2388 )
2389 IS
2390 l_SV_ID NUMBER := 0;
2391 l_counter NUMBER := 0;
2392 l_diff NUMBER := (p_ENDING_NUMBER - p_STARTING_NUMBER);
2393 l_init NUMBER := p_STARTING_NUMBER;
2394 l_sms_fe_map_id NUMBER := null;
2395
2396 CURSOR c_CHECK_IF_MAP_EXISTS (l_cur_sv_id IN NUMBER) IS
2397 SELECT sms_fe_map_id
2398 FROM xnp_sv_sms_fe_maps
2399 WHERE sv_sms_id = l_cur_sv_id
2400 AND fe_id = p_fe_id
2401 AND feature_type = p_feature_type;
2402
2403 BEGIN
2404 x_ERROR_CODE := 0;
2405
2406 -- For each TN Get the SVid in the SMS table
2407
2408 FOR l_counter IN
2409 0..l_diff
2410 LOOP
2411
2412 XNP_CORE.GET_SMS_SV_ID
2413 (to_char(l_init+l_counter)
2414 ,l_SV_ID
2415 ,x_ERROR_CODE
2416 ,x_ERROR_MESSAGE
2417 );
2418
2419 IF x_ERROR_CODE <> 0 THEN
2420 RETURN;
2421 END IF;
2422
2423 IF l_sv_id IS NOT NULL THEN
2424
2425 -- Call CREATE_ORDER_MAPPING Procedure to create record in XNP_SV_ORDER_MAPPINGS table
2426
2427 CREATE_ORDER_MAPPING
2428 (p_ORDER_ID ,
2429 p_LINEITEM_ID ,
2430 p_WORKITEM_INSTANCE_ID,
2431 p_FA_INSTANCE_ID ,
2432 null ,
2433 l_sv_id ,
2434 x_ERROR_CODE ,
2435 x_ERROR_MESSAGE
2436 );
2437
2438 END IF;
2439
2440 l_sms_fe_map_id := NULL;
2441
2442 OPEN c_CHECK_IF_MAP_EXISTS(l_sv_id);
2443 FETCH c_CHECK_IF_MAP_EXISTS INTO l_sms_fe_map_id;
2444
2445 IF c_CHECK_IF_MAP_EXISTS%NOTFOUND THEN
2446 /***
2447 SELECT xnp_sv_sms_fe_maps_s.nextval
2448 INTO l_sms_fe_map_id
2449 FROM dual;
2450 ***/
2451
2452 INSERT INTO xnp_sv_sms_fe_maps
2453 (SMS_FE_MAP_ID
2454 ,SV_SMS_ID
2455 ,FE_ID
2456 ,FEATURE_TYPE
2457 ,PROVISION_STATUS
2458 ,CREATED_BY
2459 ,CREATION_DATE
2460 ,LAST_UPDATED_BY
2461 ,LAST_UPDATE_DATE
2462 )
2463 VALUES (
2464 xnp_sv_sms_fe_maps_s.nextval
2465 ,l_SV_ID
2466 ,p_FE_ID
2467 ,p_FEATURE_TYPE
2468 ,'NOT_PROVISIONED'
2469 ,fnd_global.user_id
2470 ,sysdate
2471 ,fnd_global.user_id
2472 ,sysdate
2473 );
2474
2475 ELSE
2476
2477 UPDATE xnp_sv_sms_fe_maps
2478 SET provision_status = 'NOT_PROVISIONED' ,
2479 last_updated_by = fnd_global.user_id ,
2480 last_update_date = sysdate
2481 WHERE sms_fe_map_id = l_sms_fe_map_id;
2482
2483 END IF;
2484
2485 CLOSE c_CHECK_IF_MAP_EXISTS;
2486
2487 END LOOP;
2488
2489 EXCEPTION
2490 WHEN OTHERS THEN
2491 x_ERROR_CODE := SQLCODE;
2492 fnd_message.set_name('XNP','STD_ERROR');
2493 fnd_message.set_token('ERROR_LOCN'
2494 ,'XNP_CORE.SMS_INSERT_FE_MAP');
2495 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2496 x_error_message := fnd_message.get;
2497
2498 IF c_CHECK_IF_MAP_EXISTS%ISOPEN THEN
2499 CLOSE c_CHECK_IF_MAP_EXISTS;
2500 END IF;
2501
2502 END SMS_INSERT_FE_MAP;
2503
2504 PROCEDURE SOA_UPDATE_SV_STATUS
2505 (p_PORTING_ID VARCHAR2
2506 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
2507 ,p_NEW_STATUS_TYPE_CODE VARCHAR2
2508 ,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
2509 ,p_ORDER_ID IN NUMBER
2510 ,p_LINEITEM_ID IN NUMBER
2511 ,p_WORKITEM_INSTANCE_ID IN NUMBER
2512 ,p_FA_INSTANCE_ID IN NUMBER
2513 ,x_ERROR_CODE OUT NOCOPY NUMBER
2514 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2515 )
2516 IS
2517
2518 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
2519 TYPE sv_soa_status_tab IS TABLE OF VARCHAR2(40);
2520 l_sv_event_code SV_SOA_STATUS_TAB ;
2521 l_sv_soa_id SV_SOA_ID_TAB;
2522 i BINARY_INTEGER;
2523
2524 BEGIN
2525 x_ERROR_CODE := 0;
2526
2527 -- Get the current status of the SOA and
2528 -- call SOA_UPDATE_SV_STATUS with that value
2529 --
2530 SELECT soa.sv_soa_id,
2531 soa.status_type_code BULK COLLECT
2532 INTO l_sv_soa_id,
2533 l_sv_event_code
2534 FROM xnp_sv_soa soa
2535 WHERE soa.object_reference = p_porting_id ;
2536
2537 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2538
2539 UPDATE xnp_sv_soa soa
2540 SET soa.status_type_code = p_new_status_type_code ,
2541 soa.status_change_cause_code = p_status_change_cause_code ,
2542 soa.prev_status_type_code = soa.status_type_code,
2543 soa.modified_date = sysdate,
2544 soa.last_updated_by = fnd_global.user_id,
2545 soa.last_update_date = sysdate
2546 WHERE soa.sv_soa_id = l_sv_soa_id(i)
2547 AND soa.status_type_code <> p_new_status_type_code;
2548
2549 -- Create a history record for the status change event in XNP_SV_EVENT_HISTORY table
2550
2551 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2552
2553 INSERT INTO XNP_SV_EVENT_HISTORY
2554 (sv_event_history_id ,
2555 sv_soa_id ,
2556 event_code ,
2557 event_type ,
2558 event_timestamp ,
2559 event_cause_code ,
2560 created_by ,
2561 creation_date ,
2562 last_updated_by ,
2563 last_update_date
2564 )
2565 VALUES
2566 (XNP_SV_EVENT_HISTORY_S.nextval,
2567 l_sv_soa_id(i) ,
2568 l_sv_event_code(i) ,
2569 'STATUS_CHANGE' ,
2570 sysdate ,
2571 p_status_change_cause_code,
2572 fnd_global.user_id ,
2573 sysdate ,
2574 fnd_global.user_id ,
2575 sysdate
2576 );
2577
2578 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
2579
2580 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2581
2582
2583 INSERT INTO XNP_SV_ORDER_MAPPINGS
2584 (sv_order_mapping_id ,
2585 sv_soa_id ,
2586 order_id ,
2587 workitem_instance_id ,
2588 created_by ,
2589 creation_date ,
2590 last_updated_by ,
2591 last_update_date
2592 )
2593 VALUES
2594 (XNP_SV_ORDER_MAPPINGS_S.nextval,
2595 l_sv_soa_id(i) ,
2596 p_order_id ,
2597 p_workitem_instance_id ,
2598 fnd_global.user_id ,
2599 sysdate ,
2600 fnd_global.user_id ,
2601 sysdate
2602 );
2603
2604 EXCEPTION
2605 WHEN dup_val_on_index THEN
2606 null;
2607 WHEN NO_DATA_FOUND THEN
2608 x_ERROR_CODE := SQLCODE;
2609 fnd_message.set_name('XNP','STD_GET_FAILED');
2610 fnd_message.set_token ('FAILED_PROC','XNP_CORE.SOA_UPDATE_SV_STATUS');
2611 fnd_message.set_token('KEY','OBJECT_REFERENCE');
2612 fnd_message.set_token('VALUE',p_PORTING_ID);
2613 fnd_message.set_token('ATTRNAME','STATUS_TYPE_CODE');
2614 x_error_message := fnd_message.get;
2615 x_ERROR_MESSAGE := x_error_message||':'||SQLERRM;
2616
2617 fnd_message.set_name('XNP','UPD_SV_STATUS_ERR');
2618 fnd_message.set_token('ERROR_TEXT',x_error_message);
2619 x_error_message := fnd_message.get;
2620
2621 RETURN;
2622
2623 WHEN OTHERS THEN
2624 x_ERROR_CODE := SQLCODE;
2625 fnd_message.set_name('XNP','STD_ERROR');
2626 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_SV_STATUS');
2627 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2628 x_error_message := fnd_message.get;
2629
2630 END SOA_UPDATE_SV_STATUS;
2631
2632 PROCEDURE CHECK_SOA_STATUS_EXISTS
2633 (p_STARTING_NUMBER VARCHAR2
2634 ,p_ENDING_NUMBER VARCHAR2
2635 ,p_STATUS_TYPE_CODE VARCHAR2
2636 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
2637 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
2638 ,x_ERROR_CODE OUT NOCOPY NUMBER
2639 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2640 )
2641 IS
2642 l_SOA_SV_ID NUMBER := 0;
2643
2644 CURSOR c_SOA_SV_ID IS
2645 SELECT sv_soa_id
2646 FROM xnp_sv_soa SOA
2647 WHERE SOA.status_type_code = p_status_type_code
2648 AND TO_NUMBER(SOA.subscription_tn) BETWEEN TO_NUMBER(p_starting_number) AND TO_NUMBER(p_ending_number) ;
2649
2650 BEGIN
2651 x_ERROR_CODE := 0;
2652 x_CHECK_STATUS := 'Y';
2653
2654 -- see if there exists atleast one
2655
2656 OPEN c_SOA_SV_ID;
2657 FETCH c_SOA_SV_ID INTO l_SOA_SV_ID;
2658
2659 -- If exits then return 'Y'
2660
2661 IF c_SOA_SV_ID%FOUND THEN
2662 x_CHECK_STATUS := 'Y';
2663 ELSE
2664 x_CHECK_STATUS := 'N';
2665 END IF;
2666
2667 CLOSE c_SOA_SV_ID;
2668
2669 EXCEPTION
2670 WHEN OTHERS THEN
2671 x_ERROR_CODE := SQLCODE;
2672 fnd_message.set_name('XNP','STD_ERROR');
2673 fnd_message.set_token('ERROR_LOCN'
2674 ,'XNP_CORE.CHECK_SOA_STATUS_EXISTS');
2675 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2676 x_error_message := fnd_message.get;
2677 IF c_SOA_SV_ID%ISOPEN THEN
2678 CLOSE c_SOA_SV_ID;
2679 END IF;
2680
2681
2682 END CHECK_SOA_STATUS_EXISTS;
2683
2684
2685 PROCEDURE SMS_DELETE_FE_MAP
2686 (p_STARTING_NUMBER VARCHAR2
2687 ,p_ENDING_NUMBER VARCHAR2
2688 ,p_FE_ID NUMBER
2689 ,p_FEATURE_TYPE VARCHAR2
2690 ,x_ERROR_CODE OUT NOCOPY NUMBER
2691 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2692 )
2693 IS
2694 l_STARTING_NUMBER VARCHAR2(80) := null;
2695 l_ENDING_NUMBER VARCHAR2(80) := null;
2696
2697 BEGIN
2698
2699 x_ERROR_CODE := 0;
2700
2701
2702 l_starting_number := to_char(to_number(p_starting_number));
2703 l_ending_number := to_char(to_number(p_ending_number));
2704
2705 DELETE
2706 FROM xnp_sv_sms_fe_maps
2707 WHERE fe_id = p_fe_id
2708 AND feature_type = p_feature_type
2709 AND sv_sms_id IN
2710 (SELECT sv_sms_id
2711 FROM xnp_sv_sms
2712 WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number) ;
2713
2714 EXCEPTION
2715 WHEN OTHERS THEN
2716 x_ERROR_CODE := SQLCODE;
2717 fnd_message.set_name('XNP','STD_ERROR');
2718 fnd_message.set_token('ERROR_LOCN'
2719 ,'XNP_CORE.SMS_DELETE_FE_MAP');
2720 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2721 x_error_message := fnd_message.get;
2722
2723
2724 END SMS_DELETE_FE_MAP;
2725
2726 PROCEDURE CHECK_DONOR_PHASE
2727 (p_STARTING_NUMBER IN VARCHAR2
2728 ,p_ENDING_NUMBER IN VARCHAR2
2729 ,p_SP_ID IN NUMBER
2730 ,p_PHASE_INDICATOR IN VARCHAR2
2731 ,x_CHECK_EXISTS OUT NOCOPY VARCHAR2
2732 ,x_ERROR_CODE OUT NOCOPY NUMBER
2733 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2734 )
2735 IS
2736 l_STARTING_NUMBER VARCHAR2(80) := null;
2737 l_ENDING_NUMBER VARCHAR2(80) := null;
2738 l_SOA_SV_ID NUMBER := 0;
2739
2740 CURSOR c_SOA_SV_ID IS
2741 SELECT sv_soa_id
2742 FROM xnp_sv_soa SOA , xnp_sv_status_types_b STA
2743 WHERE SOA.subscription_tn = l_starting_number
2744 AND SOA.donor_sp_id = p_sp_id
2745 AND SOA.status_type_code = STA.status_type_code
2746 AND STA.phase_indicator = p_phase_indicator ;
2747
2748 BEGIN
2749
2750 x_ERROR_CODE := 0;
2751 x_CHECK_EXISTS := 'Y';
2752
2753 l_starting_number := to_char(to_number(p_starting_number));
2754 l_ending_number := to_char(to_number(p_ending_number));
2755
2756 OPEN c_SOA_SV_ID;
2757 FETCH c_SOA_SV_ID INTO l_SOA_SV_ID;
2758
2759 -- If exits then return 'Y'
2760 IF c_SOA_SV_ID%FOUND THEN
2761 x_CHECK_EXISTS := 'Y';
2762 ELSE
2763 x_CHECK_EXISTS := 'N';
2764 END IF;
2765
2766 CLOSE c_SOA_SV_ID;
2767
2768
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 x_ERROR_CODE := SQLCODE;
2772 fnd_message.set_name('XNP','STD_ERROR');
2773 fnd_message.set_token('ERROR_LOCN'
2774 ,'XNP_CORE.CHECK_DONOR_PHASE');
2775 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2776 x_error_message := fnd_message.get;
2777
2778 IF c_SOA_SV_ID%ISOPEN THEN
2779 CLOSE c_SOA_SV_ID;
2780 END IF;
2781
2782 END CHECK_DONOR_PHASE;
2783
2784 PROCEDURE CHECK_RECIPIENT_PHASE
2785 (p_STARTING_NUMBER IN VARCHAR2
2786 ,p_ENDING_NUMBER IN VARCHAR2
2787 ,p_SP_ID IN NUMBER
2788 ,p_PHASE_INDICATOR IN VARCHAR2
2789 ,x_CHECK_EXISTS OUT NOCOPY VARCHAR2
2790 ,x_ERROR_CODE OUT NOCOPY NUMBER
2791 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2792 )
2793 IS
2794 l_SOA_SV_ID NUMBER := 0;
2795 l_STARTING_NUMBER VARCHAR2(80) := null;
2796 l_ENDING_NUMBER VARCHAR2(80) := null;
2797
2798 CURSOR c_SOA_SV_ID IS
2799 SELECT sv_soa_id
2800 FROM xnp_sv_soa SOA ,
2801 xnp_sv_status_types_b STA
2802 WHERE SOA.subscription_tn = p_starting_number
2803 AND SOA.recipient_sp_id = p_sp_id
2804 AND SOA.status_type_code = STA.status_type_code
2805 AND STA.phase_indicator = p_phase_indicator ;
2806
2807 BEGIN
2808 x_ERROR_CODE := 0;
2809 x_CHECK_EXISTS := 'Y';
2810
2811 l_starting_number := to_char(to_number(p_starting_number));
2812 l_ending_number := to_char(to_number(p_ending_number));
2813
2814 OPEN c_SOA_SV_ID;
2815 FETCH c_SOA_SV_ID INTO l_SOA_SV_ID;
2816
2817 -- If exits then return 'Y'
2818 IF c_SOA_SV_ID%FOUND THEN
2819 x_CHECK_EXISTS := 'Y';
2820 ELSE
2821 x_CHECK_EXISTS := 'N';
2822 END IF;
2823
2824 CLOSE c_SOA_SV_ID;
2825
2826
2827 EXCEPTION
2828 WHEN OTHERS THEN
2829 x_ERROR_CODE := SQLCODE;
2830 fnd_message.set_name('XNP','STD_ERROR');
2831 fnd_message.set_token('ERROR_LOCN'
2832 ,'XNP_CORE.CHECK_RECIPIENT_PHASE');
2833 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2834 x_error_message := fnd_message.get;
2835
2836 IF c_SOA_SV_ID%ISOPEN THEN
2837 CLOSE c_SOA_SV_ID;
2838 END IF;
2839
2840 END CHECK_RECIPIENT_PHASE;
2841
2842 PROCEDURE SOA_RESET_SV_STATUS
2843 (p_STARTING_NUMBER VARCHAR2
2844 ,p_ENDING_NUMBER VARCHAR2
2845 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
2846 ,p_CUR_PHASE_INDICATOR VARCHAR2
2847 ,p_RESET_PHASE_INDICATOR VARCHAR2
2848 ,p_OMIT_STATUS VARCHAR2
2849 ,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
2850 ,p_ORDER_ID IN NUMBER
2851 ,p_LINEITEM_ID IN NUMBER
2852 ,p_WORKITEM_INSTANCE_ID IN NUMBER
2853 ,p_FA_INSTANCE_ID IN NUMBER
2854 ,x_ERROR_CODE OUT NOCOPY NUMBER
2855 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2856 )
2857 IS
2858 l_counter BINARY_INTEGER := 0;
2859 l_SV_ID NUMBER :=0;
2860 l_RESET_STATUS_TYPE_CODE VARCHAR2(40) := NULL;
2861 l_STARTING_NUMBER VARCHAR2(80) := null;
2862 l_ENDING_NUMBER VARCHAR2(80) := null;
2863
2864 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
2865 TYPE sv_soa_status_tab IS TABLE OF VARCHAR2(40);
2866 l_sv_event_code SV_SOA_STATUS_TAB ;
2867 l_sv_soa_id SV_SOA_ID_TAB;
2868 l_sv_reset_status SV_SOA_STATUS_TAB ;
2869 i BINARY_INTEGER;
2870
2871 BEGIN
2872
2873 x_ERROR_CODE := 0;
2874
2875
2876 l_starting_number := p_starting_number;
2877 l_ending_number := p_ending_number;
2878
2879
2880 -- For each TN Get the SVid which is in this phase
2881 -- and reset the status status to the reset status
2882 --
2883
2884 -- Reset Status for each SV in the given phase
2885
2886 SELECT sv_soa_id BULK COLLECT
2887 INTO l_sv_soa_id
2888 FROM xnp_sv_soa soa
2889 WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
2890 AND soa.status_type_code IN
2891 (SELECT sta.status_type_code
2892 FROM xnp_sv_status_types_b sta
2893 WHERE sta.phase_indicator = p_cur_phase_indicator
2894 AND sta.status_type_code <> p_omit_status);
2895
2896 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2897
2898 UPDATE xnp_sv_soa soa
2899 SET soa.status_type_code = (SELECT min(sta.status_type_code)
2900 FROM xnp_sv_status_types_b sta
2901 WHERE sta.phase_indicator = p_reset_phase_indicator) ,
2902 soa.status_change_cause_code = p_status_change_cause_code ,
2903 soa.prev_status_type_code = soa.status_type_code,
2904 soa.modified_date = sysdate,
2905 soa.last_updated_by = fnd_global.user_id,
2906 soa.last_update_date = sysdate
2907 WHERE soa.sv_soa_id = l_sv_soa_id(i)
2908 RETURNING soa.status_type_code BULK COLLECT INTO l_sv_event_code;
2909
2910 -- Create a history record for the status change event in XNP_SV_EVENT_HISTORY table
2911
2912 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2913
2914 INSERT INTO XNP_SV_EVENT_HISTORY
2915 (sv_event_history_id ,
2916 sv_soa_id ,
2917 event_code ,
2918 event_type ,
2919 event_timestamp ,
2920 event_cause_code ,
2921 created_by ,
2922 creation_date ,
2923 last_updated_by ,
2924 last_update_date
2925 )
2926 VALUES
2927 (XNP_SV_EVENT_HISTORY_S.nextval,
2928 l_sv_soa_id(i) ,
2929 l_sv_event_code(i) ,
2930 'STATUS_CHANGE' ,
2931 sysdate ,
2932 p_status_change_cause_code,
2933 fnd_global.user_id ,
2934 sysdate ,
2935 fnd_global.user_id ,
2936 sysdate
2937 );
2938
2939 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
2940
2941 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
2942
2943
2944 INSERT INTO XNP_SV_ORDER_MAPPINGS
2945 (sv_order_mapping_id ,
2946 sv_soa_id ,
2947 order_id ,
2948 workitem_instance_id ,
2949 created_by ,
2950 creation_date ,
2951 last_updated_by ,
2952 last_update_date
2953 )
2954 VALUES
2955 (XNP_SV_ORDER_MAPPINGS_S.nextval,
2956 l_sv_soa_id(i) ,
2957 p_order_id ,
2958 p_workitem_instance_id ,
2959 fnd_global.user_id ,
2960 sysdate ,
2961 fnd_global.user_id ,
2962 sysdate
2963 );
2964
2965 EXCEPTION
2966 WHEN DUP_VAL_ON_INDEX THEN
2967 null;
2968 WHEN OTHERS THEN
2969 x_ERROR_CODE := SQLCODE;
2970 fnd_message.set_name('XNP','STD_ERROR');
2971 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_RESET_SV_STATUS');
2972 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2973 x_error_message := fnd_message.get;
2974
2975 fnd_message.set_name('XNP','RESET_SV_STATUS');
2976 fnd_message.set_token('ERROR_TEXT',x_error_message);
2977 x_error_message := fnd_message.get;
2978
2979
2980 END SOA_RESET_SV_STATUS;
2981
2982 PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
2983 (p_STARTING_NUMBER VARCHAR2
2984 ,p_ENDING_NUMBER VARCHAR2
2985 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
2986 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
2987 ,p_OLD_SP_DUE_DATE DATE
2988 ,p_ORDER_ID IN NUMBER
2989 ,p_LINEITEM_ID IN NUMBER
2990 ,p_WORKITEM_INSTANCE_ID IN NUMBER
2991 ,p_FA_INSTANCE_ID IN NUMBER
2992 ,x_ERROR_CODE OUT NOCOPY NUMBER
2993 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2994 )
2995
2996 IS
2997 l_counter BINARY_INTEGER := 0;
2998 l_SV_ID XNP_SV_SOA.SV_SOA_ID%TYPE;
2999 l_PHASE_INDICATOR XNP_SV_STATUS_TYPES_B.PHASE_INDICATOR%TYPE;
3000 l_STARTING_NUMBER VARCHAR2(80) := null;
3001 l_ENDING_NUMBER VARCHAR2(80) := null;
3002
3003 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
3004 l_sv_soa_id SV_SOA_ID_TAB;
3005 i BINARY_INTEGER;
3006
3007 BEGIN
3008
3009 x_ERROR_CODE := 0;
3010
3011 l_starting_number := p_starting_number;
3012 l_ending_number := p_ending_number;
3013
3014 -- Get the phase corresponding to this 'p_CUR_STATUS_TYPE_CODE'
3015
3016 XNP_CORE.GET_PHASE_FOR_STATUS
3017 (p_CUR_STATUS_TYPE_CODE
3018 ,l_PHASE_INDICATOR
3019 ,x_ERROR_CODE
3020 ,x_ERROR_MESSAGE
3021 );
3022
3023 IF x_ERROR_CODE <> 0 THEN
3024 RETURN;
3025 END IF;
3026
3027 --
3028 -- For each TN Get the SVid which is in this phase
3029 -- and update the cutoff date to the
3030 -- given value
3031 --
3032
3033 x_ERROR_CODE := 0;
3034
3035 SELECT sv_soa_id BULK COLLECT
3036 INTO l_sv_soa_id
3037 FROM xnp_sv_soa soa,
3038 xnp_sv_status_types_b sta
3039 WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
3040 AND sta.phase_indicator = l_phase_indicator
3041 AND sta.status_type_code = soa.status_type_code;
3042
3043 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3044
3045 UPDATE xnp_sv_soa soa
3046 SET soa.old_sp_due_date = p_old_sp_due_date,
3047 soa.modified_date = sysdate,
3048 soa.last_updated_by = fnd_global.user_id,
3049 soa.last_update_date = sysdate
3050 WHERE soa.sv_soa_id = l_sv_soa_id(i);
3051
3052 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3053
3054 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3055
3056
3057 INSERT INTO XNP_SV_ORDER_MAPPINGS
3058 (sv_order_mapping_id ,
3059 sv_soa_id ,
3060 order_id ,
3061 workitem_instance_id ,
3062 created_by ,
3063 creation_date ,
3064 last_updated_by ,
3065 last_update_date
3066 )
3067 VALUES
3068 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3069 l_sv_soa_id(i) ,
3070 p_order_id ,
3071 p_workitem_instance_id ,
3072 fnd_global.user_id ,
3073 sysdate ,
3074 fnd_global.user_id ,
3075 sysdate
3076 );
3077
3078 EXCEPTION
3079 WHEN dup_val_on_index THEN
3080 null;
3081
3082 WHEN OTHERS THEN
3083 x_ERROR_CODE := SQLCODE;
3084 fnd_message.set_name('XNP','STD_ERROR');
3085 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE');
3086 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3087 x_error_message := fnd_message.get;
3088
3089 fnd_message.set_name('XNP','UPD_FOR_SV_ERR');
3090 fnd_message.set_token('ERROR_TEXT',x_error_message);
3091 x_error_message := fnd_message.get;
3092
3093
3094 END SOA_UPDATE_OLD_SP_DUE_DATE;
3095
3096 PROCEDURE SMS_DELETE_PORTED_NUMBER
3097 (p_STARTING_NUMBER IN VARCHAR2
3098 ,p_ENDING_NUMBER IN VARCHAR2
3099 ,x_ERROR_CODE OUT NOCOPY NUMBER
3100 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3101 )
3102 IS
3103 l_sms_map_id NUMBER := 0;
3104 l_STARTING_NUMBER VARCHAR2(80) := null;
3105 l_ENDING_NUMBER VARCHAR2(80) := null;
3106
3107 CURSOR c_sv_map_id is
3108 SELECT sms_fe_map_id
3109 FROM xnp_sv_sms_fe_maps
3110 WHERE sv_sms_id in
3111 (SELECT sv_sms_id
3112 FROM xnp_sv_sms
3113 WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number
3114 );
3115
3116 BEGIN
3117 x_ERROR_CODE := 0;
3118
3119 l_starting_number := p_starting_number;
3120 l_ending_number := p_ending_number;
3121
3122 OPEN c_sv_map_id;
3123 FETCH c_sv_map_id INTO l_sms_map_id;
3124
3125 -- If no sms fe map exists for this TN range
3126 -- the delete the sv order mappings and sms records
3127 IF (c_sv_map_id%NOTFOUND) THEN
3128
3129 -- Delete from order mappings
3130
3131 DELETE
3132 FROM xnp_sv_order_mappings
3133 WHERE sv_sms_id IN
3134 (SELECT sv_sms_id
3135 FROM xnp_sv_sms
3136 WHERE subscription_tn BETWEEN l_starting_number
3137 AND l_ending_number);
3138
3139 DELETE
3140 FROM xnp_sv_sms
3141 WHERE subscription_tn BETWEEN l_starting_number
3142 AND l_ending_number ;
3143
3144 END IF;
3145
3146 CLOSE c_sv_map_id;
3147
3148 EXCEPTION
3149 WHEN OTHERS THEN
3150 x_ERROR_CODE := SQLCODE;
3151 x_ERROR_MESSAGE := SQLERRM;
3152 fnd_message.set_name('XNP','STD_ERROR');
3153 fnd_message.set_token('ERROR_LOCN'
3154 ,'XNP_CORE.SMS_DELETE_PORTED_NUMBER');
3155 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3156 x_error_message := fnd_message.get;
3157
3158 fnd_message.set_name('XNP','DEL_PORTED_NUM_ERR');
3159 fnd_message.set_token('ERROR_TEXT',x_error_message);
3160 x_error_message := fnd_message.get;
3161
3162
3163 fnd_message.set_name('XNP','DEL_PORTED_NUM_ERR');
3164 fnd_message.set_token('ERROR_TEXT',x_error_message);
3165 x_error_message := fnd_message.get;
3166
3167 IF c_sv_map_id%ISOPEN THEN
3168 CLOSE c_sv_map_id;
3169 END IF;
3170 END SMS_DELETE_PORTED_NUMBER;
3171
3172
3173 PROCEDURE SMS_UPDATE_FE_MAP_STATUS
3174 (p_STARTING_NUMBER VARCHAR2
3175 ,p_ENDING_NUMBER VARCHAR2
3176 ,p_FE_ID NUMBER
3177 ,p_FEATURE_TYPE VARCHAR2
3178 ,p_PROV_STATUS VARCHAR2
3179 ,p_ORDER_ID IN NUMBER
3180 ,p_LINEITEM_ID IN NUMBER
3181 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3182 ,p_FA_INSTANCE_ID IN NUMBER
3183 ,x_ERROR_CODE OUT NOCOPY NUMBER
3184 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3185 )
3186 IS
3187
3188 l_STARTING_NUMBER VARCHAR2(80) := null;
3189 l_ENDING_NUMBER VARCHAR2(80) := null;
3190
3191 TYPE sv_sms_id_tab IS TABLE OF NUMBER;
3192 l_sv_sms_id SV_SMS_ID_TAB;
3193 i BINARY_INTEGER;
3194
3195 BEGIN
3196
3197 l_starting_number := p_starting_number;
3198 l_ending_number := p_ending_number;
3199
3200 SELECT sv_sms_id BULK COLLECT
3201 INTO l_sv_sms_id
3202 FROM xnp_sv_sms sms
3203 WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number ;
3204
3205 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
3206
3207 UPDATE xnp_sv_sms_fe_maps
3208 SET provision_status = p_prov_status ,
3209 last_updated_by = fnd_global.user_id ,
3210 last_update_date = sysdate
3211 WHERE sv_sms_id = l_sv_sms_id(i)
3212 AND fe_id = p_fe_id
3213 AND feature_type = p_feature_type;
3214
3215 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3216
3217 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
3218
3219
3220 INSERT INTO XNP_SV_ORDER_MAPPINGS
3221 (sv_order_mapping_id ,
3222 sv_sms_id ,
3223 order_id ,
3224 workitem_instance_id ,
3225 created_by ,
3226 creation_date ,
3227 last_updated_by ,
3228 last_update_date
3229 )
3230 VALUES
3231 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3232 l_sv_sms_id(i) ,
3233 p_order_id ,
3234 p_workitem_instance_id ,
3235 fnd_global.user_id ,
3236 sysdate ,
3237 fnd_global.user_id ,
3238 sysdate
3239 );
3240 EXCEPTION
3241 WHEN dup_val_on_index THEN
3242 null;
3243 WHEN OTHERS THEN
3244 x_ERROR_CODE := SQLCODE;
3245 fnd_message.set_name('XNP','STD_ERROR');
3246 fnd_message.set_token('ERROR_LOCN'
3247 ,'XNP_CORE.SMS_UPDATE_FE_MAP_STATUS');
3248 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3249 x_error_message := fnd_message.get;
3250
3251 fnd_message.set_name('XNP','UPD_FE_MAP_STATUS_ERR');
3252 fnd_message.set_token('ERROR_TEXT',x_error_message);
3253 x_error_message := fnd_message.get;
3254
3255
3256 END SMS_UPDATE_FE_MAP_STATUS;
3257
3258
3259 PROCEDURE SOA_UPDATE_REC_PORT_ORDER
3260 (p_PORTING_ID VARCHAR2
3261 ,p_STARTING_NUMBER NUMBER
3262 ,p_ENDING_NUMBER NUMBER
3263 ,p_DONOR_SP_ID NUMBER
3264 ,p_RECIPIENT_SP_ID NUMBER DEFAULT NULL
3265 ,p_ROUTING_NUMBER_ID NUMBER
3266 ,p_NEW_SP_DUE_DATE DATE
3267 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
3268 ,p_CUSTOMER_ID VARCHAR2
3269 ,p_CUSTOMER_NAME VARCHAR2
3270 ,p_CUSTOMER_TYPE VARCHAR2
3271 ,p_ADDRESS_LINE1 VARCHAR2
3272 ,p_ADDRESS_LINE2 VARCHAR2
3273 ,p_CITY VARCHAR2
3274 ,p_PHONE VARCHAR2
3275 ,p_FAX VARCHAR2
3276 ,p_EMAIL VARCHAR2
3277 ,p_PAGER VARCHAR2
3278 ,p_PAGER_PIN VARCHAR2
3279 ,p_INTERNET_ADDRESS VARCHAR2
3280 ,p_ZIP_CODE VARCHAR2
3281 ,p_COUNTRY VARCHAR2
3282 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
3283 ,p_CONTACT_NAME VARCHAR2
3284 ,p_RETAIN_TN_FLAG VARCHAR2
3285 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
3286 ,p_CNAM_ADDRESS VARCHAR2
3287 ,p_CNAM_SUBSYSTEM VARCHAR2
3288 ,p_ISVM_ADDRESS VARCHAR2
3289 ,p_ISVM_SUBSYSTEM VARCHAR2
3290 ,p_LIDB_ADDRESS VARCHAR2
3291 ,p_LIDB_SUBSYSTEM VARCHAR2
3292 ,p_CLASS_ADDRESS VARCHAR2
3293 ,p_CLASS_SUBSYSTEM VARCHAR2
3294 ,p_WSMSC_ADDRESS VARCHAR2
3295 ,p_WSMSC_SUBSYSTEM VARCHAR2
3296 ,p_RN_ADDRESS VARCHAR2
3297 ,p_RN_SUBSYSTEM VARCHAR2
3298 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
3299 ,p_ACTIVATION_DUE_DATE DATE
3300 ,p_ORDER_ID IN NUMBER
3301 ,p_LINEITEM_ID IN NUMBER
3302 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3303 ,p_FA_INSTANCE_ID IN NUMBER
3304 ,x_ERROR_CODE OUT NOCOPY NUMBER
3305 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3306 )
3307 IS
3308
3309 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
3310 l_sv_soa_id SV_SOA_ID_TAB;
3311 i BINARY_INTEGER;
3312
3313 BEGIN
3314
3315 SELECT sv_soa_id BULK COLLECT
3316 INTO l_sv_soa_id
3317 FROM xnp_sv_soa soa
3318 WHERE soa.object_reference = p_porting_id ;
3319
3320 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3321
3322 UPDATE xnp_sv_soa soa
3323 SET SOA.CHANGED_BY_SP_ID = p_RECIPIENT_SP_ID ,
3324 SOA.OLD_SP_CUTOFF_DUE_DATE = p_OLD_SP_CUTOFF_DUE_DATE ,
3325 SOA.CUSTOMER_ID = p_CUSTOMER_ID ,
3326 SOA.CUSTOMER_NAME = p_CUSTOMER_NAME ,
3327 SOA.CUSTOMER_TYPE = p_CUSTOMER_TYPE ,
3328 SOA.ADDRESS_LINE1 = p_ADDRESS_LINE1 ,
3329 SOA.ADDRESS_LINE2 = p_ADDRESS_LINE2 ,
3330 SOA.CITY = p_CITY ,
3331 SOA.PHONE = p_PHONE ,
3332 SOA.FAX = p_FAX ,
3333 SOA.EMAIL = p_EMAIL ,
3334 SOA.ZIP_CODE = p_ZIP_CODE ,
3335 SOA.COUNTRY = p_COUNTRY ,
3336 SOA.NEW_SP_DUE_DATE = p_NEW_SP_DUE_DATE ,
3337 SOA.CUSTOMER_CONTACT_REQ_FLAG = p_CUSTOMER_CONTACT_REQ_FLAG ,
3338 SOA.CONTACT_NAME = p_CONTACT_NAME ,
3339 SOA.RETAIN_TN_FLAG = p_RETAIN_TN_FLAG ,
3340 SOA.RETAIN_DIR_INFO_FLAG = p_RETAIN_DIR_INFO_FLAG ,
3341 SOA.PAGER = p_PAGER ,
3342 SOA.PAGER_PIN = p_PAGER_PIN ,
3343 SOA.INTERNET_ADDRESS = p_INTERNET_ADDRESS ,
3344 SOA.CNAM_ADDRESS = p_CNAM_ADDRESS ,
3345 SOA.CNAM_SUBSYSTEM = p_CNAM_SUBSYSTEM ,
3346 SOA.ISVM_ADDRESS = p_ISVM_ADDRESS ,
3347 SOA.ISVM_SUBSYSTEM = p_ISVM_SUBSYSTEM ,
3348 SOA.LIDB_ADDRESS = p_LIDB_ADDRESS ,
3349 SOA.LIDB_SUBSYSTEM = p_LIDB_SUBSYSTEM ,
3350 SOA.CLASS_ADDRESS = p_CLASS_ADDRESS ,
3351 SOA.CLASS_SUBSYSTEM = p_CLASS_SUBSYSTEM ,
3352 SOA.WSMSC_ADDRESS = p_WSMSC_ADDRESS ,
3353 SOA.WSMSC_SUBSYSTEM = p_WSMSC_SUBSYSTEM ,
3354 SOA.RN_ADDRESS = p_RN_ADDRESS ,
3355 SOA.RN_SUBSYSTEM = p_RN_SUBSYSTEM ,
3356 SOA.PREORDER_AUTHORIZATION_CODE = p_PREORDER_AUTHORIZATION_CODE ,
3357 SOA.ACTIVATION_DUE_DATE = p_ACTIVATION_DUE_DATE ,
3358 SOA.LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
3359 SOA.LAST_UPDATE_DATE = SYSDATE ,
3360 SOA.MODIFIED_DATE = SYSDATE
3361 WHERE soa.sv_soa_id = l_sv_soa_id(i);
3362
3363 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3364
3365 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3366
3367 INSERT INTO XNP_SV_ORDER_MAPPINGS
3368 (sv_order_mapping_id ,
3369 sv_soa_id ,
3370 order_id ,
3371 workitem_instance_id ,
3372 created_by ,
3373 creation_date ,
3374 last_updated_by ,
3375 last_update_date
3376 )
3377 VALUES
3378 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3379 l_sv_soa_id(i) ,
3380 p_order_id ,
3381 p_workitem_instance_id ,
3382 fnd_global.user_id ,
3383 sysdate ,
3384 fnd_global.user_id ,
3385 sysdate
3386 );
3387
3388 EXCEPTION
3389 WHEN dup_val_on_index THEN
3390 null;
3391 WHEN OTHERS THEN
3392 x_ERROR_CODE := SQLCODE;
3393 fnd_message.set_name('XNP','STD_ERROR');
3394 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_REC_PORT_ORDER');
3395 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3396 x_error_message := fnd_message.get;
3397
3398 fnd_message.set_name('XNP','UPD_PORT_ORDER_ERR');
3399 fnd_message.set_token('ERROR_TEXT',x_error_message);
3400 x_error_message := fnd_message.get;
3401
3402 END SOA_UPDATE_REC_PORT_ORDER;
3403
3404 PROCEDURE SOA_UPDATE_DON_PORT_ORDER
3405 (p_PORTING_ID VARCHAR2
3406 ,p_STARTING_NUMBER NUMBER
3407 ,p_ENDING_NUMBER NUMBER
3408 ,p_DONOR_SP_ID NUMBER DEFAULT NULL
3409 ,p_RECIPIENT_SP_ID NUMBER
3410 ,p_OLD_SP_DUE_DATE DATE
3411 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
3412 ,p_CUSTOMER_ID VARCHAR2
3413 ,p_CUSTOMER_NAME VARCHAR2
3414 ,p_CUSTOMER_TYPE VARCHAR2
3415 ,p_ADDRESS_LINE1 VARCHAR2
3416 ,p_ADDRESS_LINE2 VARCHAR2
3417 ,p_CITY VARCHAR2
3418 ,p_PHONE VARCHAR2
3419 ,p_FAX VARCHAR2
3420 ,p_EMAIL VARCHAR2
3421 ,p_PAGER VARCHAR2
3422 ,p_PAGER_PIN VARCHAR2
3423 ,p_INTERNET_ADDRESS VARCHAR2
3424 ,p_ZIP_CODE VARCHAR2
3425 ,p_COUNTRY VARCHAR2
3426 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
3427 ,p_CONTACT_NAME VARCHAR2
3428 ,p_RETAIN_TN_FLAG VARCHAR2
3429 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
3430 ,p_CNAM_ADDRESS VARCHAR2
3431 ,p_CNAM_SUBSYSTEM VARCHAR2
3432 ,p_ISVM_ADDRESS VARCHAR2
3433 ,p_ISVM_SUBSYSTEM VARCHAR2
3434 ,p_LIDB_ADDRESS VARCHAR2
3435 ,p_LIDB_SUBSYSTEM VARCHAR2
3436 ,p_CLASS_ADDRESS VARCHAR2
3437 ,p_CLASS_SUBSYSTEM VARCHAR2
3438 ,p_WSMSC_ADDRESS VARCHAR2
3439 ,p_WSMSC_SUBSYSTEM VARCHAR2
3440 ,p_RN_ADDRESS VARCHAR2
3441 ,p_RN_SUBSYSTEM VARCHAR2
3442 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
3443 ,p_ACTIVATION_DUE_DATE DATE
3444 ,p_ORDER_ID IN NUMBER
3445 ,p_LINEITEM_ID IN NUMBER
3446 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3447 ,p_FA_INSTANCE_ID IN NUMBER
3448 ,x_ERROR_CODE OUT NOCOPY NUMBER
3449 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3450 )
3451 IS
3452
3453 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
3454 l_sv_soa_id SV_SOA_ID_TAB;
3455 i BINARY_INTEGER;
3456
3457 BEGIN
3458
3459 SELECT sv_soa_id BULK COLLECT
3460 INTO l_sv_soa_id
3461 FROM xnp_sv_soa soa
3462 WHERE object_reference = p_porting_id;
3463
3464 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3465
3466 UPDATE xnp_sv_soa soa
3467 SET SOA.CHANGED_BY_SP_ID = p_DONOR_SP_ID ,
3468 SOA.OLD_SP_CUTOFF_DUE_DATE = p_OLD_SP_CUTOFF_DUE_DATE ,
3469 SOA.CUSTOMER_ID = p_CUSTOMER_ID ,
3470 SOA.CUSTOMER_NAME = p_CUSTOMER_NAME ,
3471 SOA.CUSTOMER_TYPE = p_CUSTOMER_TYPE ,
3472 SOA.ADDRESS_LINE1 = p_ADDRESS_LINE1 ,
3473 SOA.ADDRESS_LINE2 = p_ADDRESS_LINE2 ,
3474 SOA.CITY = p_CITY ,
3475 SOA.PHONE = p_PHONE ,
3476 SOA.FAX = p_FAX ,
3477 SOA.EMAIL = p_EMAIL ,
3478 SOA.ZIP_CODE = p_ZIP_CODE ,
3479 SOA.COUNTRY = p_COUNTRY ,
3480 SOA.OLD_SP_DUE_DATE = p_OLD_SP_DUE_DATE ,
3481 SOA.CUSTOMER_CONTACT_REQ_FLAG = p_CUSTOMER_CONTACT_REQ_FLAG ,
3482 SOA.CONTACT_NAME = p_CONTACT_NAME ,
3483 SOA.RETAIN_TN_FLAG = p_RETAIN_TN_FLAG ,
3484 SOA.RETAIN_DIR_INFO_FLAG = p_RETAIN_DIR_INFO_FLAG ,
3485 SOA.PAGER = p_PAGER ,
3486 SOA.PAGER_PIN = p_PAGER_PIN ,
3487 SOA.INTERNET_ADDRESS = p_INTERNET_ADDRESS ,
3488 SOA.CNAM_ADDRESS = p_CNAM_ADDRESS ,
3489 SOA.CNAM_SUBSYSTEM = p_CNAM_SUBSYSTEM ,
3490 SOA.ISVM_ADDRESS = p_ISVM_ADDRESS ,
3491 SOA.ISVM_SUBSYSTEM = p_ISVM_SUBSYSTEM ,
3492 SOA.LIDB_ADDRESS = p_LIDB_ADDRESS ,
3493 SOA.LIDB_SUBSYSTEM = p_LIDB_SUBSYSTEM ,
3494 SOA.CLASS_ADDRESS = p_CLASS_ADDRESS ,
3495 SOA.CLASS_SUBSYSTEM = p_CLASS_SUBSYSTEM ,
3496 SOA.WSMSC_ADDRESS = p_WSMSC_ADDRESS ,
3497 SOA.WSMSC_SUBSYSTEM = p_WSMSC_SUBSYSTEM ,
3498 SOA.RN_ADDRESS = p_RN_ADDRESS ,
3499 SOA.RN_SUBSYSTEM = p_RN_SUBSYSTEM ,
3500 SOA.PREORDER_AUTHORIZATION_CODE = p_PREORDER_AUTHORIZATION_CODE ,
3501 SOA.ACTIVATION_DUE_DATE = p_ACTIVATION_DUE_DATE ,
3502 SOA.LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
3503 SOA.LAST_UPDATE_DATE = SYSDATE,
3504 SOA.MODIFIED_DATE = SYSDATE
3505 WHERE SOA.SV_SOA_ID = L_SV_SOA_ID(i);
3506
3507 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3508
3509 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3510
3511
3512 INSERT INTO XNP_SV_ORDER_MAPPINGS
3513 (sv_order_mapping_id ,
3514 sv_soa_id ,
3515 order_id ,
3516 workitem_instance_id ,
3517 created_by ,
3518 creation_date ,
3519 last_updated_by ,
3520 last_update_date
3521 )
3522 VALUES
3523 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3524 l_sv_soa_id(i) ,
3525 p_order_id ,
3526 p_workitem_instance_id ,
3527 fnd_global.user_id ,
3528 sysdate ,
3529 fnd_global.user_id ,
3530 sysdate
3531 );
3532
3533 EXCEPTION
3534 WHEN dup_val_on_index THEN
3535 null;
3536 WHEN OTHERS THEN
3537 x_ERROR_CODE := SQLCODE;
3538 fnd_message.set_name('XNP','STD_ERROR');
3539 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_DON_PORT_ORDER');
3540 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3541 x_error_message := fnd_message.get;
3542
3543 fnd_message.set_name('XNP','UPD_PORT_ORDER_ERR');
3544 fnd_message.set_token('ERROR_TEXT',x_error_message);
3545 x_error_message := fnd_message.get;
3546
3547 END SOA_UPDATE_DON_PORT_ORDER;
3548
3549 PROCEDURE SMS_MODIFY_PORTED_NUMBER
3550 (p_PORTING_ID IN VARCHAR2
3551 ,p_STARTING_NUMBER IN NUMBER
3552 ,p_ENDING_NUMBER IN NUMBER
3553 ,p_ROUTING_NUMBER_ID IN NUMBER
3554 ,p_PORTING_TIME IN DATE
3555 ,p_CNAM_ADDRESS VARCHAR2
3556 ,p_CNAM_SUBSYSTEM VARCHAR2
3557 ,p_ISVM_ADDRESS VARCHAR2
3558 ,p_ISVM_SUBSYSTEM VARCHAR2
3559 ,p_LIDB_ADDRESS VARCHAR2
3560 ,p_LIDB_SUBSYSTEM VARCHAR2
3561 ,p_CLASS_ADDRESS VARCHAR2
3562 ,p_CLASS_SUBSYSTEM VARCHAR2
3563 ,p_WSMSC_ADDRESS VARCHAR2
3564 ,p_WSMSC_SUBSYSTEM VARCHAR2
3565 ,p_RN_ADDRESS VARCHAR2
3566 ,p_RN_SUBSYSTEM VARCHAR2
3567 ,p_ORDER_ID IN NUMBER
3568 ,p_LINEITEM_ID IN NUMBER
3569 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3570 ,p_FA_INSTANCE_ID IN NUMBER
3571 ,x_ERROR_CODE OUT NOCOPY NUMBER
3572 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3573 )
3574 IS
3575
3576 TYPE sv_sms_id_tab IS TABLE OF NUMBER;
3577 l_sv_sms_id SV_SMS_ID_TAB;
3578 i BINARY_INTEGER;
3579
3580
3581 BEGIN
3582
3583
3584 SELECT sv_sms_id BULK COLLECT
3585 INTO l_sv_sms_id
3586 FROM xnp_sv_sms sms
3587 WHERE object_reference = p_porting_id ;
3588
3589 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
3590
3591 UPDATE XNP_SV_SMS SMS
3592 SET SMS.PROVISION_SENT_DATE = P_PORTING_TIME ,
3593 SMS.ROUTING_NUMBER_ID = p_ROUTING_NUMBER_ID ,
3594 SMS.CNAM_ADDRESS = p_CNAM_ADDRESS ,
3595 SMS.CNAM_SUBSYSTEM = p_CNAM_SUBSYSTEM ,
3596 SMS.ISVM_ADDRESS = p_ISVM_ADDRESS ,
3597 SMS.ISVM_SUBSYSTEM = p_ISVM_SUBSYSTEM ,
3598 SMS.LIDB_ADDRESS = p_LIDB_ADDRESS ,
3599 SMS.LIDB_SUBSYSTEM = p_LIDB_SUBSYSTEM ,
3600 SMS.CLASS_ADDRESS = p_CLASS_ADDRESS ,
3601 SMS.CLASS_SUBSYSTEM = p_CLASS_SUBSYSTEM ,
3602 SMS.WSMSC_ADDRESS = p_WSMSC_ADDRESS ,
3603 SMS.WSMSC_SUBSYSTEM = p_WSMSC_SUBSYSTEM ,
3604 SMS.RN_ADDRESS = p_RN_ADDRESS ,
3605 SMS.RN_SUBSYSTEM = p_RN_SUBSYSTEM ,
3606 SMS.LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
3607 SMS.LAST_UPDATE_DATE = SYSDATE
3608 WHERE SMS.SV_SMS_ID = L_SV_SMS_ID(i);
3609
3610 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3611
3612 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
3613
3614 INSERT INTO XNP_SV_ORDER_MAPPINGS
3615 (sv_order_mapping_id ,
3616 sv_sms_id ,
3617 order_id ,
3618 workitem_instance_id ,
3619 created_by ,
3620 creation_date ,
3621 last_updated_by ,
3622 last_update_date
3623 )
3624 VALUES
3625 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3626 l_sv_sms_id(i) ,
3627 p_order_id ,
3628 p_workitem_instance_id ,
3629 fnd_global.user_id ,
3630 sysdate ,
3631 fnd_global.user_id ,
3632 sysdate
3633 );
3634
3635 EXCEPTION
3636 WHEN dup_val_on_index THEN
3637 null;
3638 WHEN OTHERS THEN
3639 x_ERROR_CODE := SQLCODE;
3640 fnd_message.set_name('XNP','STD_ERROR');
3641 fnd_message.set_token('ERROR_LOCN' ,'XNP_CORE.SMS_MODIFY_PORTED_NUMBER');
3642 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3643 x_error_message := fnd_message.get;
3644
3645 fnd_message.set_name('XNP','UPD_PORT_ORDER_ERR');
3646 fnd_message.set_token('ERROR_TEXT',x_error_message);
3647 x_error_message := fnd_message.get;
3648
3649 END SMS_MODIFY_PORTED_NUMBER;
3650
3651 PROCEDURE CHECK_IF_PORTABLE_RANGE
3652 (p_STARTING_NUMBER VARCHAR2
3653 ,p_ENDING_NUMBER VARCHAR2
3654 ,x_CHECK_STATUS OUT NOCOPY NUMBER
3655 ,x_ERROR_CODE OUT NOCOPY NUMBER
3656 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3657 )
3658 IS
3659
3660 l_PORTED_INDICATOR VARCHAR2(40) := NULL;
3661
3662 CURSOR c_PORTED_INDICATOR IS
3663 SELECT ported_indicator
3664 FROM xnp_number_ranges
3665 WHERE starting_number <= p_starting_number
3666 AND ending_number >= p_ending_number;
3667
3668 BEGIN
3669
3670 x_CHECK_STATUS := 'N';
3671 OPEN c_PORTED_INDICATOR;
3672 FETCH c_PORTED_INDICATOR INTO l_PORTED_INDICATOR;
3673
3674 IF c_PORTED_INDICATOR%NOTFOUND THEN
3675 raise NO_DATA_FOUND;
3676 END IF;
3677
3678 CLOSE c_PORTED_INDICATOR;
3679
3680 x_CHECK_STATUS := 'N';
3681
3682 if ( (l_PORTED_INDICATOR = 'PORTED_IN_USE')
3683 OR (l_PORTED_INDICATOR = 'PORTED_UNUSED') )
3684 then
3685 x_CHECK_STATUS := 'Y';
3686 end if;
3687
3688 EXCEPTION
3689 WHEN NO_DATA_FOUND THEN
3690 x_ERROR_CODE := SQLCODE;
3691 fnd_message.set_name('XNP','STD_GET_FAILED');
3692 fnd_message.set_token
3693 ('FAILED_PROC','XNP_CORE.CHECK_IF_PORTED_NUMBER_RANGE');
3694 fnd_message.set_token('ATTRNAME','PORTED_INDICATOR');
3695 fnd_message.set_token('KEY','STARTING_NUMBER:ENDING_NUMBER');
3696 fnd_message.set_token
3697 ('VALUE'
3698 ,p_STARTING_NUMBER||':'||p_ENDING_NUMBER
3699 );
3700 x_error_message :=
3701 fnd_message.get;
3702 x_ERROR_MESSAGE := x_error_message||':'||SQLERRM;
3703
3704 fnd_message.set_name('XNP','CHECK_IF_PORTABLE_ERR');
3705 fnd_message.set_token('ERROR_TEXT',x_error_message);
3706 x_error_message := fnd_message.get;
3707
3708 IF c_PORTED_INDICATOR%ISOPEN THEN
3709 CLOSE c_PORTED_INDICATOR;
3710 END IF;
3711
3712 WHEN OTHERS THEN
3713 x_ERROR_CODE := SQLCODE;
3714 fnd_message.set_name('XNP','STD_ERROR');
3715 fnd_message.set_token
3716 ('ERROR_LOCN','XNP_CORE.CHECK_IF_PORTED_NUMBER_RANGE');
3717 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3718 x_error_message := fnd_message.get;
3719 IF c_PORTED_INDICATOR%ISOPEN THEN
3720 CLOSE c_PORTED_INDICATOR;
3721 END IF;
3722
3723 END CHECK_IF_PORTABLE_RANGE;
3724
3725
3726 PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
3727 (p_PORTING_ID VARCHAR2
3728 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
3729 ,p_OLD_SP_DUE_DATE DATE
3730 ,p_ORDER_ID IN NUMBER
3731 ,p_LINEITEM_ID IN NUMBER
3732 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3733 ,p_FA_INSTANCE_ID IN NUMBER
3734 ,x_ERROR_CODE OUT NOCOPY NUMBER
3735 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3736 )
3737 IS
3738
3739 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
3740 l_sv_soa_id SV_SOA_ID_TAB;
3741 i BINARY_INTEGER;
3742
3743 e_SOA_UPDATE_OLD_SP_DUE_DATE exception;
3744
3745 BEGIN
3746
3747 if(p_old_sp_due_date = null) then
3748 raise e_SOA_UPDATE_OLD_SP_DUE_DATE;
3749 end if;
3750
3751 SELECT sv_soa_id BULK COLLECT
3752 INTO l_sv_soa_id
3753 FROM xnp_sv_soa soa
3754 WHERE soa.object_reference = p_porting_id;
3755
3756 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3757
3758 UPDATE xnp_sv_soa soa
3759 SET soa.old_sp_due_date = p_old_sp_due_date ,
3760 soa.modified_date = sysdate,
3761 soa.last_updated_by = fnd_global.user_id,
3762 soa.last_update_date = sysdate
3763 WHERE soa.sv_soa_id = l_sv_soa_id(i);
3764
3765 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3766
3767 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3768
3769
3770 INSERT INTO XNP_SV_ORDER_MAPPINGS
3771 (sv_order_mapping_id ,
3772 sv_soa_id ,
3773 order_id ,
3774 workitem_instance_id ,
3775 created_by ,
3776 creation_date ,
3777 last_updated_by ,
3778 last_update_date
3779 )
3780 VALUES
3781 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3782 l_sv_soa_id(i) ,
3783 p_order_id ,
3784 p_workitem_instance_id ,
3785 fnd_global.user_id ,
3786 sysdate ,
3787 fnd_global.user_id ,
3788 sysdate
3789 );
3790
3791 EXCEPTION
3792 WHEN dup_val_on_index THEN
3793 null;
3794
3795 WHEN e_SOA_UPDATE_OLD_SP_DUE_DATE THEN
3796 x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
3797 fnd_message.set_name('XNP','INVALID_DATE_FORMAT_ERR');
3798 fnd_message.set_token('PORTING_ID',p_porting_id);
3799
3800 WHEN OTHERS THEN
3801 x_ERROR_CODE := SQLCODE;
3802 fnd_message.set_name('XNP','STD_ERROR');
3803 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE');
3804 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3805 x_error_message := fnd_message.get;
3806
3807 END SOA_UPDATE_OLD_SP_DUE_DATE;
3808
3809 PROCEDURE SOA_UPDATE_NEW_SP_DUE_DATE
3810 (p_PORTING_ID VARCHAR2
3811 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
3812 ,p_NEW_SP_DUE_DATE DATE
3813 ,p_ORDER_ID IN NUMBER
3814 ,p_LINEITEM_ID IN NUMBER
3815 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3816 ,p_FA_INSTANCE_ID IN NUMBER
3817 ,x_ERROR_CODE OUT NOCOPY NUMBER
3818 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3819 )
3820 IS
3821
3822 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
3823 l_sv_soa_id SV_SOA_ID_TAB;
3824 i BINARY_INTEGER;
3825
3826 e_SOA_UPDATE_NEW_SP_DUE_DATE exception;
3827
3828 BEGIN
3829
3830 if(p_new_sp_due_date = null) then
3831 raise e_SOA_UPDATE_NEW_SP_DUE_DATE;
3832 end if;
3833
3834 SELECT sv_soa_id BULK COLLECT
3835 INTO l_sv_soa_id
3836 FROM xnp_sv_soa soa
3837 WHERE soa.object_reference = p_porting_id;
3838
3839 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3840
3841 UPDATE xnp_sv_soa soa
3842 SET soa.new_sp_due_date = p_new_sp_due_date,
3843 soa.modified_date = sysdate,
3844 soa.last_updated_by = fnd_global.user_id,
3845 soa.last_update_date = sysdate
3846 WHERE soa.sv_soa_id = l_sv_soa_id(i);
3847
3848 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
3849
3850 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
3851
3852
3853 INSERT INTO XNP_SV_ORDER_MAPPINGS
3854 (sv_order_mapping_id ,
3855 sv_soa_id ,
3856 order_id ,
3857 workitem_instance_id ,
3858 created_by ,
3859 creation_date ,
3860 last_updated_by ,
3861 last_update_date
3862 )
3863 VALUES
3864 (XNP_SV_ORDER_MAPPINGS_S.nextval,
3865 l_sv_soa_id(i) ,
3866 p_order_id ,
3867 p_workitem_instance_id ,
3868 fnd_global.user_id ,
3869 sysdate ,
3870 fnd_global.user_id ,
3871 sysdate
3872 );
3873
3874 EXCEPTION
3875 WHEN dup_val_on_index THEN
3876 null;
3877 WHEN e_SOA_UPDATE_NEW_SP_DUE_DATE THEN
3878 x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
3879 fnd_message.set_name('XNP','INVALID_DATE_FORMAT_ERR');
3880 fnd_message.set_token('PORTING_ID',p_porting_id);
3881 WHEN OTHERS THEN
3882 x_ERROR_CODE := SQLCODE;
3883 fnd_message.set_name('XNP','STD_ERROR');
3884 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_NEW_SP_DUE_DATE');
3885 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3886 x_error_message := fnd_message.get;
3887
3888 END SOA_UPDATE_NEW_SP_DUE_DATE;
3889
3890
3891 PROCEDURE CHECK_DONOR_STATUS_EXISTS
3892 (p_STARTING_NUMBER VARCHAR2
3893 ,p_ENDING_NUMBER VARCHAR2
3894 ,p_STATUS_TYPE_CODE VARCHAR2
3895 ,p_DONOR_SP_ID NUMBER
3896 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
3897 ,x_ERROR_CODE OUT NOCOPY NUMBER
3898 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3899 )
3900 IS
3901 l_SOA_SV_ID NUMBER := 0;
3902
3903 CURSOR c_SOA_SV_ID IS
3904 SELECT sv_soa_id
3905 FROM xnp_sv_soa SOA
3906 WHERE status_type_code = p_status_type_code
3907 AND SOA.donor_sp_id = p_donor_sp_id
3908 AND ( (to_number(subscription_tn) >= to_number(p_starting_number))
3909 AND (to_number(subscription_tn) <= to_number(p_ending_number)) ) ;
3910
3911 BEGIN
3912 x_ERROR_CODE := 0;
3913 x_CHECK_STATUS := 'Y';
3914
3915 -- see if there exists atleast one
3916
3917 OPEN c_SOA_SV_ID;
3918 FETCH c_SOA_SV_ID INTO l_SOA_SV_ID;
3919
3920 -- If exits then return 'Y'
3921 IF c_SOA_SV_ID%FOUND THEN
3922 x_CHECK_STATUS := 'Y';
3923 ELSE
3924 x_CHECK_STATUS := 'N';
3925 END IF;
3926
3927 CLOSE c_SOA_SV_ID;
3928
3929 EXCEPTION
3930 WHEN OTHERS THEN
3931 x_ERROR_CODE := SQLCODE;
3932 fnd_message.set_name('XNP','STD_ERROR');
3933 fnd_message.set_token('ERROR_LOCN'
3934 ,'XNP_CORE.CHECK_DONOR_STATUS_EXISTS');
3935 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3936 x_error_message := fnd_message.get;
3937 IF c_SOA_SV_ID%ISOPEN THEN
3938 CLOSE c_SOA_SV_ID;
3939 END IF;
3940
3941
3942 END CHECK_DONOR_STATUS_EXISTS;
3943
3944
3945
3946 PROCEDURE CHECK_RECIPIENT_STATUS_EXISTS
3947 (p_STARTING_NUMBER VARCHAR2
3948 ,p_ENDING_NUMBER VARCHAR2
3949 ,p_STATUS_TYPE_CODE VARCHAR2
3950 ,p_RECIPIENT_SP_ID NUMBER
3951 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
3952 ,x_ERROR_CODE OUT NOCOPY NUMBER
3953 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3954 )
3955 IS
3956 l_SOA_SV_ID NUMBER := 0;
3957
3958 CURSOR c_SOA_SV_ID IS
3959 SELECT sv_soa_id
3960 FROM xnp_sv_soa XSO
3961 WHERE status_type_code = p_status_type_code
3962 AND XSO.recipient_sp_id = p_recipient_sp_id
3963 AND to_number(subscription_tn) BETWEEN to_number(p_starting_number) AND to_number(p_ending_number);
3964
3965 BEGIN
3966 x_ERROR_CODE := 0;
3967 x_CHECK_STATUS := 'Y';
3968
3969 -- see if there exists atleast one
3970
3971 OPEN c_SOA_SV_ID;
3972 FETCH c_SOA_SV_ID INTO l_SOA_SV_ID;
3973
3974 -- If exits then return 'Y'
3975 IF c_SOA_SV_ID%FOUND THEN
3976 x_CHECK_STATUS := 'Y';
3977 ELSE
3978 x_CHECK_STATUS := 'N';
3979 END IF;
3980
3981 CLOSE c_SOA_SV_ID;
3982
3983 EXCEPTION
3984 WHEN OTHERS THEN
3985 x_ERROR_CODE := SQLCODE;
3986 fnd_message.set_name('XNP','STD_ERROR');
3987 fnd_message.set_token('ERROR_LOCN'
3988 ,'XNP_CORE.CHECK_RECIPIENT_STATUS_EXISTS');
3989 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3990 x_error_message := fnd_message.get;
3991 IF c_SOA_SV_ID%ISOPEN THEN
3992 CLOSE c_SOA_SV_ID;
3993 END IF;
3994
3995
3996 END CHECK_RECIPIENT_STATUS_EXISTS;
3997
3998 PROCEDURE SOA_UPDATE_CUTOFF_DATE
3999 (p_PORTING_ID VARCHAR2
4000 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4001 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
4002 ,p_ORDER_ID IN NUMBER
4003 ,p_LINEITEM_ID IN NUMBER
4004 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4005 ,p_FA_INSTANCE_ID IN NUMBER
4006 ,x_ERROR_CODE OUT NOCOPY NUMBER
4007 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4008 )
4009 IS
4010
4011 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
4012 l_sv_soa_id SV_SOA_ID_TAB;
4013 i BINARY_INTEGER;
4014
4015
4016 BEGIN
4017 x_ERROR_CODE := 0;
4018
4019 SELECT sv_soa_id BULK COLLECT
4020 INTO l_sv_soa_id
4021 FROM xnp_sv_soa soa
4022 WHERE soa.object_reference = p_porting_id;
4023
4024 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4025
4026 UPDATE xnp_sv_soa soa
4027 SET soa.old_sp_cutoff_due_date = XNP_UTILS.CANONICAL_TO_DATE(p_old_sp_cutoff_due_date) ,
4028 soa.modified_date = sysdate,
4029 soa.last_updated_by = fnd_global.user_id,
4030 soa.last_update_date = sysdate
4031 WHERE soa.sv_soa_id = l_sv_soa_id(i);
4032
4033 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4034
4035 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4036
4037
4038 INSERT INTO XNP_SV_ORDER_MAPPINGS
4039 (sv_order_mapping_id ,
4040 sv_soa_id ,
4041 order_id ,
4042 workitem_instance_id ,
4043 created_by ,
4044 creation_date ,
4045 last_updated_by ,
4046 last_update_date
4047 )
4048 VALUES
4049 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4050 l_sv_soa_id(i) ,
4051 p_order_id ,
4052 p_workitem_instance_id ,
4053 fnd_global.user_id ,
4054 sysdate ,
4055 fnd_global.user_id ,
4056 sysdate
4057 );
4058
4059 EXCEPTION
4060 WHEN dup_val_on_index THEN
4061 null;
4062 WHEN OTHERS THEN
4063 x_ERROR_CODE := SQLCODE;
4064 fnd_message.set_name('XNP','STD_ERROR');
4065 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_CUTOFF_DATE');
4066 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4067 x_error_message := fnd_message.get;
4068
4069 END SOA_UPDATE_CUTOFF_DATE;
4070
4071 PROCEDURE SOA_UPDATE_CHARGING_INFO
4072 (p_PORTING_ID VARCHAR2
4073 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4074 ,p_INVOICE_DUE_DATE DATE
4075 ,p_CHARGING_INFO VARCHAR2
4076 ,p_BILLING_ID NUMBER
4077 ,p_USER_LOCTN_VALUE VARCHAR2
4078 ,p_USER_LOCTN_TYPE VARCHAR2
4079 ,p_PRICE_CODE VARCHAR2
4080 ,p_PRICE_PER_CALL VARCHAR2
4081 ,p_PRICE_PER_MINUTE VARCHAR2
4082 ,p_ORDER_ID IN NUMBER
4083 ,p_LINEITEM_ID IN NUMBER
4084 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4085 ,p_FA_INSTANCE_ID IN NUMBER
4086 ,x_ERROR_CODE OUT NOCOPY NUMBER
4087 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4088 )
4089 IS
4090
4091 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
4092 l_sv_soa_id SV_SOA_ID_TAB;
4093 i BINARY_INTEGER;
4094
4095 l_counter BINARY_INTEGER := 0;
4096
4097 BEGIN
4098
4099 SELECT sv_soa_id BULK COLLECT
4100 INTO l_sv_soa_id
4101 FROM xnp_sv_soa soa
4102 WHERE soa.object_reference=p_porting_id;
4103
4104 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4105
4106 UPDATE xnp_sv_soa soa
4107 SET soa.invoice_due_date = p_invoice_due_date,
4108 soa.charging_info = p_charging_info ,
4109 soa.user_loctn_type = p_user_loctn_type,
4110 soa.user_loctn_value = p_user_loctn_value,
4111 soa.price_code = p_price_code,
4112 soa.price_per_call = p_price_per_call,
4113 soa.price_per_minute = p_price_per_minute,
4114 soa.modified_date = sysdate,
4115 soa.last_updated_by = fnd_global.user_id,
4116 soa.last_update_date = sysdate
4117 WHERE soa.sv_soa_id = l_sv_soa_id(i);
4118
4119 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4120
4121 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4122
4123
4124 INSERT INTO XNP_SV_ORDER_MAPPINGS
4125 (sv_order_mapping_id ,
4126 sv_soa_id ,
4127 order_id ,
4128 workitem_instance_id ,
4129 created_by ,
4130 creation_date ,
4131 last_updated_by ,
4132 last_update_date
4133 )
4134 VALUES
4135 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4136 l_sv_soa_id(i) ,
4137 p_order_id ,
4138 p_workitem_instance_id ,
4139 fnd_global.user_id ,
4140 sysdate ,
4141 fnd_global.user_id ,
4142 sysdate
4143 );
4144
4145 EXCEPTION
4146 WHEN dup_val_on_index THEN
4147 null;
4148 WHEN OTHERS THEN
4149 x_ERROR_CODE := SQLCODE;
4150 fnd_message.set_name('XNP','STD_ERROR');
4151 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_CHARGING_INFO');
4152 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4153 x_error_message := fnd_message.get;
4154
4155 fnd_message.set_name('XNP','UPD_CHARGING_INFO_ERR');
4156 fnd_message.set_token('ERROR_TEXT',x_error_message);
4157 x_error_message := fnd_message.get;
4158
4159 END SOA_UPDATE_CHARGING_INFO;
4160
4161 PROCEDURE CHECK_SOA_STATUS_EXISTS
4162 (p_PORTING_ID VARCHAR2
4163 ,p_STATUS_TYPE_CODE VARCHAR2
4164 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4165 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
4166 ,x_ERROR_CODE OUT NOCOPY NUMBER
4167 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4168 )
4169 IS
4170
4171 l_SOA_SV_ID NUMBER := 0;
4172
4173 CURSOR c_SOA_SV_ID IS
4174 SELECT sv_soa_id
4175 FROM xnp_sv_soa SOA
4176 WHERE SOA.object_reference = p_porting_id
4177 AND SOA.status_type_code = p_status_type_code;
4178
4179 BEGIN
4180 x_ERROR_CODE := 0;
4181 x_CHECK_STATUS := 'Y';
4182
4183 -- see if there exists atleast one --
4184
4185 OPEN c_SOA_SV_ID;
4186 FETCH c_SOA_SV_ID INTO l_SOA_SV_ID;
4187
4188 -- If exits then return 'Y'
4189 IF c_SOA_SV_ID%FOUND THEN
4190 x_CHECK_STATUS := 'Y';
4191 ELSE
4192 x_CHECK_STATUS := 'N';
4193 END IF;
4194
4195 CLOSE c_SOA_SV_ID;
4196
4197 EXCEPTION
4198 WHEN OTHERS THEN
4199 x_ERROR_CODE := SQLCODE;
4200 fnd_message.set_name('XNP','STD_ERROR');
4201 fnd_message.set_token('ERROR_LOCN'
4202 ,'XNP_CORE.CHECK_SOA_STATUS_EXISTS');
4203 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4204 x_error_message := fnd_message.get;
4205 IF c_SOA_SV_ID%ISOPEN THEN
4206 CLOSE c_SOA_SV_ID;
4207 END IF;
4208 END CHECK_SOA_STATUS_EXISTS;
4209
4210 PROCEDURE SOA_UPDATE_OLD_SP_AUTH_FLAG
4211 (p_PORTING_ID VARCHAR2
4212 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4213 ,p_OLD_SP_AUTHORIZATION_FLAG VARCHAR2
4214 ,p_ORDER_ID IN NUMBER
4215 ,p_LINEITEM_ID IN NUMBER
4216 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4217 ,p_FA_INSTANCE_ID IN NUMBER
4218 ,x_ERROR_CODE OUT NOCOPY NUMBER
4219 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4220 )
4221 IS
4222
4223 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
4224 l_sv_soa_id SV_SOA_ID_TAB;
4225 i BINARY_INTEGER;
4226
4227 BEGIN
4228
4229 x_ERROR_CODE := 0;
4230
4231 SELECT sv_soa_id BULK COLLECT
4232 INTO l_sv_soa_id
4233 FROM xnp_sv_soa soa
4234 WHERE soa.object_reference = p_porting_id;
4235
4236 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4237
4238 UPDATE xnp_sv_soa soa
4239 SET soa.old_sp_authorization_flag = p_old_sp_authorization_flag,
4240 soa.modified_date = sysdate,
4241 soa.last_updated_by = fnd_global.user_id,
4242 soa.last_update_date = sysdate
4243 WHERE soa.sv_soa_id = l_sv_soa_id(i);
4244
4245 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4246
4247 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4248
4249
4250 INSERT INTO XNP_SV_ORDER_MAPPINGS
4251 (sv_order_mapping_id ,
4252 sv_soa_id ,
4253 order_id ,
4254 workitem_instance_id ,
4255 created_by ,
4256 creation_date ,
4257 last_updated_by ,
4258 last_update_date
4259 )
4260 VALUES
4261 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4262 l_sv_soa_id(i) ,
4263 p_order_id ,
4264 p_workitem_instance_id ,
4265 fnd_global.user_id ,
4266 sysdate ,
4267 fnd_global.user_id ,
4268 sysdate
4269 );
4270
4271 EXCEPTION
4272 WHEN dup_val_on_index THEN
4273 null;
4274 WHEN OTHERS THEN
4275 x_ERROR_CODE := SQLCODE;
4276 fnd_message.set_name('XNP','STD_ERROR');
4277 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_OLD_SP_AUTH_FLAG');
4278 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4279 x_error_message := fnd_message.get;
4280
4281 fnd_message.set_name('XNP','UPD_FOR_SV_ERR');
4282 fnd_message.set_token('ERROR_TEXT',x_error_message);
4283 x_error_message := fnd_message.get;
4284
4285 END SOA_UPDATE_OLD_SP_AUTH_FLAG;
4286
4287 PROCEDURE SOA_UPDATE_NEW_SP_AUTH_FLAG
4288 (p_PORTING_ID VARCHAR2
4289 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4290 ,p_NEW_SP_AUTHORIZATION_FLAG VARCHAR2
4291 ,p_ORDER_ID IN NUMBER
4292 ,p_LINEITEM_ID IN NUMBER
4293 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4294 ,p_FA_INSTANCE_ID IN NUMBER
4295 ,x_ERROR_CODE OUT NOCOPY NUMBER
4296 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4297 )
4298
4299 IS
4300
4301 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
4302 l_sv_soa_id SV_SOA_ID_TAB;
4303 i BINARY_INTEGER;
4304
4305
4306 BEGIN
4307
4308 x_ERROR_CODE := 0;
4309
4310 SELECT sv_soa_id BULK COLLECT
4311 INTO l_sv_soa_id
4312 FROM xnp_sv_soa soa
4313 WHERE soa.object_reference = p_porting_id;
4314
4315 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4316
4317 UPDATE xnp_sv_soa soa
4318 SET soa.new_sp_authorization_flag = p_new_sp_authorization_flag ,
4319 soa.modified_date = sysdate,
4320 soa.last_updated_by = fnd_global.user_id,
4321 soa.last_update_date = sysdate
4322 WHERE soa.sv_soa_id = l_sv_soa_id(i);
4323
4324 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4325
4326 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4327
4328
4329 INSERT INTO XNP_SV_ORDER_MAPPINGS
4330 (sv_order_mapping_id ,
4331 sv_soa_id ,
4332 order_id ,
4333 workitem_instance_id ,
4334 created_by ,
4335 creation_date ,
4336 last_updated_by ,
4337 last_update_date
4338 )
4339 VALUES
4340 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4341 l_sv_soa_id(i) ,
4342 p_order_id ,
4343 p_workitem_instance_id ,
4344 fnd_global.user_id ,
4345 sysdate ,
4346 fnd_global.user_id ,
4347 sysdate
4348 );
4349
4350 EXCEPTION
4351 WHEN dup_val_on_index THEN
4352 null;
4353 WHEN OTHERS THEN
4354 x_ERROR_CODE := SQLCODE;
4355 fnd_message.set_name('XNP','STD_ERROR');
4356 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_NEW_SP_AUTH_FLAG');
4357 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4358 x_error_message := fnd_message.get;
4359
4360 fnd_message.set_name('XNP','UPD_FOR_SV_ERR');
4361 fnd_message.set_token('ERROR_TEXT',x_error_message);
4362 x_error_message := fnd_message.get;
4363
4364 END SOA_UPDATE_NEW_SP_AUTH_FLAG;
4365
4366 PROCEDURE SMS_MARK_FES_TO_PROVISION
4367 (p_STARTING_NUMBER VARCHAR2
4368 ,p_ENDING_NUMBER VARCHAR2
4369 ,p_FEATURE_TYPE VARCHAR2
4370 ,p_ORDER_ID NUMBER
4371 ,p_LINEITEM_ID NUMBER
4372 ,p_WORKITEM_INSTANCE_ID NUMBER
4373 ,p_FA_INSTANCE_ID NUMBER
4374 ,x_ERROR_CODE OUT NOCOPY NUMBER
4375 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4376 )
4377 IS
4378 l_NUMBER_RANGE_ID NUMBER := 0;
4379
4380 CURSOR c_ALL_FEs IS
4381 SELECT SNR.fe_id
4382 FROM XNP_SERVED_NUM_RANGES SNR, XDP_FES FES
4383 WHERE SNR.feature_type = p_feature_type
4384 AND SNR.number_range_id = l_number_range_id
4385 AND SNR.fe_id = FES.fe_id
4386 AND (sysdate BETWEEN FES.valid_date AND NVL(FES.invalid_date, sysdate));
4387
4388 BEGIN
4389 x_error_code := 0;
4390
4391 -- Determine the number range id
4392
4393 XNP_CORE.GET_NUMBER_RANGE_ID
4394 (p_STARTING_NUMBER
4395 ,p_ENDING_NUMBER
4396 ,l_NUMBER_RANGE_ID
4397 ,x_ERROR_CODE
4398 ,x_ERROR_MESSAGE
4399 );
4400
4401 IF x_error_code <> 0 THEN
4402 return;
4403 END IF;
4404
4405 -- get the fe list to provision
4406 FOR l_tmp_fe IN c_ALL_FEs LOOP
4407
4408 -- Insert the FE MAP for the FE to be provisioned
4409 XNP_CORE.SMS_INSERT_FE_MAP
4410 (p_ORDER_ID,
4411 p_LINEITEM_ID,
4412 p_WORKITEM_INSTANCE_ID,
4413 p_FA_INSTANCE_ID,
4414 to_number(p_STARTING_NUMBER),
4415 to_number(p_ENDING_NUMBER),
4416 l_TMP_FE.FE_ID,
4417 p_FEATURE_TYPE,
4418 x_ERROR_CODE,
4419 x_ERROR_MESSAGE
4420 );
4421
4422 IF (x_error_code <> 0) THEN
4423 return;
4424 END IF;
4425
4426 END LOOP;
4427
4428 EXCEPTION
4429 WHEN dup_val_on_index THEN
4430 null;
4431 WHEN OTHERS THEN
4432 x_ERROR_CODE := SQLCODE;
4433 fnd_message.set_name('XNP','STD_ERROR');
4434 fnd_message.set_token('ERROR_LOCN'
4435 ,'XNP_CORE.SMS_MARK_FES_TO_PROVISION');
4436 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4437 x_error_message := fnd_message.get;
4438
4439 fnd_message.set_name('XNP','UPD_FOR_SV_ERR');
4440 fnd_message.set_token('ERROR_TEXT',x_error_message);
4441 x_error_message := fnd_message.get;
4442
4443 END SMS_MARK_FES_TO_PROVISION;
4444
4445 PROCEDURE SMS_MARK_FES_TO_DEPROVISION
4446 (p_STARTING_NUMBER VARCHAR2
4447 ,p_ENDING_NUMBER VARCHAR2
4448 ,p_FEATURE_TYPE VARCHAR2
4449 ,p_DEPROVISION_STATUS VARCHAR2
4450 ,p_ORDER_ID IN NUMBER
4451 ,p_LINEITEM_ID IN NUMBER
4452 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4453 ,p_FA_INSTANCE_ID IN NUMBER
4454 ,x_ERROR_CODE OUT NOCOPY NUMBER
4455 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4456 )
4457 IS
4458
4459 l_STARTING_NUMBER VARCHAR2(80) := null;
4460 l_ENDING_NUMBER VARCHAR2(80) := null;
4461
4462 TYPE sv_sms_id_tab IS TABLE OF NUMBER;
4463 l_sv_sms_id SV_SMS_ID_TAB;
4464 i BINARY_INTEGER;
4465
4466 BEGIN
4467
4468 x_error_code := 0;
4469
4470 l_starting_number := p_starting_number;
4471 l_ending_number := p_ending_number;
4472
4473 SELECT sv_sms_id BULK COLLECT
4474 INTO l_sv_sms_id
4475 FROM xnp_sv_sms
4476 WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number;
4477
4478 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
4479
4480 UPDATE xnp_sv_sms_fe_maps
4481 SET provision_status = p_deprovision_status ,
4482 last_updated_by = fnd_global.user_id ,
4483 last_update_date = sysdate
4484 WHERE feature_type = p_feature_type
4485 AND sv_sms_id = l_sv_sms_id(i);
4486
4487 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4488
4489 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
4490
4491
4492 INSERT INTO XNP_SV_ORDER_MAPPINGS
4493 (sv_order_mapping_id ,
4494 sv_sms_id ,
4495 order_id ,
4496 workitem_instance_id ,
4497 created_by ,
4498 creation_date ,
4499 last_updated_by ,
4500 last_update_date
4501 )
4502 VALUES
4503 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4504 l_sv_sms_id(i) ,
4505 p_order_id ,
4506 p_workitem_instance_id ,
4507 fnd_global.user_id ,
4508 sysdate ,
4509 fnd_global.user_id ,
4510 sysdate
4511 );
4512
4513 EXCEPTION
4514 WHEN dup_val_on_index THEN
4515 null;
4516 WHEN OTHERS THEN
4517 x_ERROR_CODE := SQLCODE;
4518 fnd_message.set_name('XNP','STD_ERROR');
4519 fnd_message.set_token('ERROR_LOCN'
4520 ,'XNP_CORE.SMS_MARK_FES_TO_DEPROVISION');
4521 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4522 x_error_message := fnd_message.get;
4523
4524 fnd_message.set_name('XNP','UPD_FOR_SV_ERR');
4525 fnd_message.set_token('ERROR_TEXT',x_error_message);
4526 x_error_message := fnd_message.get;
4527
4528 END SMS_MARK_FES_TO_DEPROVISION;
4529
4530 PROCEDURE SOA_SET_LOCKED_FLAG
4531 (P_PORTING_ID VARCHAR2
4532 ,P_LOCAL_SP_ID NUMBER DEFAULT NULL
4533 ,P_LOCKED_FLAG VARCHAR2
4534 ,p_ORDER_ID IN NUMBER
4535 ,p_LINEITEM_ID IN NUMBER
4536 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4537 ,p_FA_INSTANCE_ID IN NUMBER
4538 ,X_ERROR_CODE OUT NOCOPY NUMBER
4539 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4540 )
4541 IS
4542
4543 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
4544 l_sv_soa_id SV_SOA_ID_TAB;
4545 i BINARY_INTEGER;
4546
4547 BEGIN
4548 x_error_code := 0;
4549
4550 SELECT sv_soa_id BULK COLLECT
4551 INTO l_sv_soa_id
4552 FROM xnp_sv_soa soa
4553 WHERE soa.object_reference = p_porting_id;
4554
4555 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4556
4557 UPDATE xnp_sv_soa soa
4558 SET soa.locked_flag = p_locked_flag,
4559 soa.modified_date = sysdate,
4560 soa.last_updated_by = fnd_global.user_id,
4561 soa.last_update_date = sysdate
4562 WHERE soa.sv_soa_id = l_sv_soa_id(i);
4563
4564 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4565
4566 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4567
4568
4569 INSERT INTO XNP_SV_ORDER_MAPPINGS
4570 (sv_order_mapping_id ,
4571 sv_soa_id ,
4572 order_id ,
4573 workitem_instance_id ,
4574 created_by ,
4575 creation_date ,
4576 last_updated_by ,
4577 last_update_date
4578 )
4579 VALUES
4580 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4581 l_sv_soa_id(i) ,
4582 p_order_id ,
4583 p_workitem_instance_id ,
4584 fnd_global.user_id ,
4585 sysdate ,
4586 fnd_global.user_id ,
4587 sysdate
4588 );
4589 EXCEPTION
4590 WHEN dup_val_on_index THEN
4591 null;
4592 WHEN NO_DATA_FOUND THEN
4593 x_error_code := SQLCODE;
4594 fnd_message.set_name('XNP','STD_ERROR');
4595 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_set_locked_flag');
4596 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4597 x_error_message := fnd_message.get;
4598
4599 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
4600 fnd_message.set_token('ERROR_TEXT',x_error_message);
4601 fnd_message.set_token('PORTING_ID',p_porting_id);
4602 x_error_message := fnd_message.get;
4603
4604 WHEN OTHERS THEN
4605 x_error_code := SQLCODE;
4606
4607 fnd_message.set_name('XNP','STD_ERROR');
4608 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_set_locked_flag');
4609 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4610 x_error_message := fnd_message.get;
4611
4612
4613 END SOA_SET_LOCKED_FLAG;
4614
4615 PROCEDURE SOA_GET_LOCKED_FLAG
4616 (P_PORTING_ID VARCHAR2
4617 ,P_LOCAL_SP_ID NUMBER DEFAUlT NULL
4618 ,X_LOCKED_FLAG OUT NOCOPY VARCHAR2
4619 ,X_ERROR_CODE OUT NOCOPY NUMBER
4620 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4621 )
4622 IS
4623
4624 CURSOR c_locked_flag IS
4625 SELECT locked_flag
4626 FROM xnp_sv_soa soa
4627 WHERE soa.object_reference = p_porting_id;
4628
4629 BEGIN
4630 x_error_code := 0;
4631 x_locked_flag := 'Y'; -- intialized value
4632
4633 -- Get the locked_flag corresponding to
4634 -- this porting_id
4635
4636 OPEN c_locked_flag;
4637 FETCH c_locked_flag INTO x_locked_flag;
4638
4639 IF c_locked_flag%NOTFOUND THEN
4640 raise NO_DATA_FOUND;
4641 END IF;
4642
4643 CLOSE c_locked_flag;
4644
4645 EXCEPTION
4646 WHEN NO_DATA_FOUND THEN
4647 x_error_code := SQLCODE;
4648 fnd_message.set_name('XNP','STD_ERROR');
4649 fnd_message.set_token('ERROR_LOCN'
4650 ,'xnp_core.soa_get_locked_flag');
4651 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4652 x_error_message := fnd_message.get;
4653
4654 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
4655 fnd_message.set_token('ERROR_TEXT',x_error_message);
4656 fnd_message.set_token('PORTING_ID',p_porting_id);
4657 x_error_message := fnd_message.get;
4658
4659 IF c_locked_flag%ISOPEN THEN
4660 CLOSE c_locked_flag;
4661 END IF;
4662
4663 WHEN OTHERS THEN
4664 x_error_code := SQLCODE;
4665
4666 fnd_message.set_name('XNP','STD_ERROR');
4667 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_get_locked_flag');
4668 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4669 x_error_message := fnd_message.get;
4670
4671 IF c_locked_flag%ISOPEN THEN
4672 CLOSE c_locked_flag;
4673 END IF;
4674
4675
4676 END SOA_GET_LOCKED_FLAG;
4677
4678 PROCEDURE SOA_GET_SV_STATUS
4679 (p_PORTING_ID VARCHAR2
4680 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4681 ,x_SV_STATUS OUT NOCOPY VARCHAR2
4682 ,x_ERROR_CODE OUT NOCOPY NUMBER
4683 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4684 )
4685 IS
4686
4687 CURSOR c_SV_STATUS IS
4688 SELECT status_type_code
4689 FROM xnp_sv_soa soa
4690 WHERE soa.object_reference=p_porting_id;
4691
4692 BEGIN
4693 x_error_code := 0;
4694 x_sv_status := NULL;
4695
4696 OPEN c_sv_status;
4697 FETCH c_sv_status INTO x_sv_status;
4698
4699 IF c_sv_status%NOTFOUND THEN
4700 raise NO_DATA_FOUND;
4701 END IF;
4702
4703 CLOSE c_sv_status;
4704
4705 EXCEPTION
4706
4707 WHEN NO_DATA_FOUND THEN
4708 x_error_code := SQLCODE;
4709 x_sv_status := NULL;
4710 fnd_message.set_name('XNP','STD_ERROR');
4711 fnd_message.set_token('ERROR_LOCN'
4712 ,'xnp_core.soa_get_sv_status');
4713 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4714 x_error_message := fnd_message.get;
4715
4716 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
4717 fnd_message.set_token('ERROR_TEXT',x_error_message);
4718 fnd_message.set_token('PORTING_ID',p_porting_id);
4719 x_error_message := fnd_message.get;
4720
4721 IF c_sv_status%ISOPEN THEN
4722 CLOSE c_sv_status;
4723 END IF;
4724
4725 WHEN OTHERS THEN
4726 x_error_code := SQLCODE;
4727 x_sv_status := NULL;
4728
4729 fnd_message.set_name('XNP','STD_ERROR');
4730 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_get_sv_status');
4731 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4732 x_error_message := fnd_message.get;
4733
4734 IF c_sv_status%ISOPEN THEN
4735 CLOSE c_sv_status;
4736 END IF;
4737
4738 END SOA_GET_SV_STATUS ;
4739
4740 PROCEDURE SOA_CHECK_SV_STATUS
4741 (p_PORTING_ID VARCHAR2
4742 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
4743 ,p_STATUS_TYPE_CODE VARCHAR2
4744 ,x_STATUS_MATCHED_FLAG OUT NOCOPY VARCHAR2
4745 ,x_ERROR_CODE OUT NOCOPY NUMBER
4746 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4747 )
4748 IS
4749
4750 l_sv_status varchar2(40) := NULL;
4751
4752 BEGIN
4753
4754 x_status_matched_flag := 'F'; -- default flag
4755
4756 xnp_core.soa_get_sv_status
4757 (p_porting_id => p_porting_id
4758 ,p_local_sp_id => p_local_sp_id
4759 ,x_sv_status => l_sv_status
4760 ,x_error_code => x_error_code
4761 ,x_error_message => x_error_message
4762 );
4763
4764 if ((x_error_code <> 0) OR (l_sv_status IS NULL)) then
4765 return;
4766 end if;
4767
4768 if (l_sv_status = p_status_type_code) then
4769 x_status_matched_flag := 'T';
4770 end if;
4771
4772 END SOA_CHECK_SV_STATUS;
4773
4774 PROCEDURE SOA_SET_BLOCKED_FLAG
4775 (P_PORTING_ID VARCHAR2
4776 ,P_LOCAL_SP_ID NUMBER DEFAULT NULL
4777 ,P_BLOCKED_FLAG VARCHAR2
4778 ,p_ORDER_ID IN NUMBER
4779 ,p_LINEITEM_ID IN NUMBER
4780 ,p_WORKITEM_INSTANCE_ID IN NUMBER
4781 ,p_FA_INSTANCE_ID IN NUMBER
4782 ,X_ERROR_CODE OUT NOCOPY NUMBER
4783 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
4784 )
4785 IS
4786
4787 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
4788 l_sv_soa_id SV_SOA_ID_TAB;
4789 i BINARY_INTEGER;
4790
4791 BEGIN
4792 x_error_code := 0;
4793
4794 SELECT sv_soa_id BULK COLLECT
4795 INTO l_sv_soa_id
4796 FROM xnp_sv_soa soa
4797 WHERE object_reference = p_porting_id;
4798
4799 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4800
4801 UPDATE xnp_sv_soa soa
4802 SET soa.blocked_flag = p_blocked_flag,
4803 soa.modified_date = sysdate,
4804 soa.last_updated_by = fnd_global.user_id,
4805 soa.last_update_date = sysdate
4806 WHERE soa.sv_soa_id = l_sv_soa_id(i);
4807
4808 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
4809
4810 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
4811
4812
4813 INSERT INTO XNP_SV_ORDER_MAPPINGS
4814 (sv_order_mapping_id ,
4815 sv_soa_id ,
4816 order_id ,
4817 workitem_instance_id ,
4818 created_by ,
4819 creation_date ,
4820 last_updated_by ,
4821 last_update_date
4822 )
4823 VALUES
4824 (XNP_SV_ORDER_MAPPINGS_S.nextval,
4825 l_sv_soa_id(i) ,
4826 p_order_id ,
4827 p_workitem_instance_id ,
4828 fnd_global.user_id ,
4829 sysdate ,
4830 fnd_global.user_id ,
4831 sysdate
4832 );
4833
4834 EXCEPTION
4835 WHEN dup_val_on_index THEN
4836 null;
4837 WHEN NO_DATA_FOUND THEN
4838 x_error_code := SQLCODE;
4839 fnd_message.set_name('XNP','STD_ERROR');
4840 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_set_blocked_flag');
4841 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4842 x_error_message := fnd_message.get;
4843
4844 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
4845 fnd_message.set_token('ERROR_TEXT',x_error_message);
4846 fnd_message.set_token('PORTING_ID',p_porting_id);
4847 x_error_message := fnd_message.get;
4848
4849 WHEN OTHERS THEN
4850 x_error_code := SQLCODE;
4851
4852 fnd_message.set_name('XNP','STD_ERROR');
4853 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_set_blocked_flag');
4854 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4855 x_error_message := fnd_message.get;
4856
4857 END SOA_SET_BLOCKED_FLAG;
4858
4859 PROCEDURE SOA_GET_BLOCKED_FLAG
4860 (p_porting_id VARCHAR2
4861 ,p_local_sp_id NUMBER DEFAULT NULL
4862 ,x_blocked_flag OUT NOCOPY VARCHAR2
4863 ,x_error_code OUT NOCOPY NUMBER
4864 ,x_error_message OUT NOCOPY VARCHAR2
4865 )
4866 IS
4867
4868 CURSOR c_blocked_flag IS
4869 SELECT blocked_flag
4870 FROM xnp_sv_soa
4871 WHERE object_reference = p_porting_id;
4872
4873 BEGIN
4874 x_error_code := 0;
4875 x_blocked_flag := 'Y'; -- intialized value
4876
4877 -- Get the blocked_flag corresponding to
4878 -- this porting_id
4879 OPEN c_blocked_flag;
4880 FETCH c_blocked_flag INTO x_blocked_flag;
4881
4882 IF c_blocked_flag%NOTFOUND THEN
4883 raise NO_DATA_FOUND;
4884 END IF;
4885
4886 CLOSE c_blocked_flag;
4887
4888 EXCEPTION
4889 WHEN NO_DATA_FOUND THEN
4890 x_error_code := SQLCODE;
4891 fnd_message.set_name('XNP','STD_ERROR');
4892 fnd_message.set_token('ERROR_LOCN'
4893 ,'xnp_core.soa_get_blocked_flag');
4894 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4895 x_error_message := fnd_message.get;
4896
4897 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
4898 fnd_message.set_token('ERROR_TEXT',x_error_message);
4899 fnd_message.set_token('PORTING_ID',p_porting_id);
4900 x_error_message := fnd_message.get;
4901
4902 IF c_blocked_flag%ISOPEN THEN
4903 CLOSE c_blocked_flag;
4904 END IF;
4905
4906 WHEN OTHERS THEN
4907 x_error_code := SQLCODE;
4908
4909 fnd_message.set_name('XNP','STD_ERROR');
4910 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_get_blocked_flag');
4911 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4912 x_error_message := fnd_message.get;
4913
4914 IF c_blocked_flag%ISOPEN THEN
4915 CLOSE c_blocked_flag;
4916 END IF;
4917
4918
4919 END SOA_GET_BLOCKED_FLAG;
4920
4921 PROCEDURE SOA_GET_NEW_SP_AUTH_FLAG
4922 (p_porting_id VARCHAR2
4923 ,p_local_sp_id NUMBER DEFAULT NULL
4924 ,x_new_sp_auth_flag OUT NOCOPY VARCHAR2
4925 ,x_error_code OUT NOCOPY NUMBER
4926 ,x_error_message OUT NOCOPY VARCHAR2
4927 )
4928
4929 IS
4930 CURSOR c_new_sp_auth_flag IS
4931 SELECT new_sp_authorization_flag
4932 FROM xnp_sv_soa
4933 WHERE object_reference = p_porting_id;
4934
4935 BEGIN
4936 x_error_code := 0;
4937 x_new_sp_auth_flag := 'Y'; -- intialized value
4938
4939 -- Get the new_sp_auth_flag corresponding to
4940 -- this porting_id
4941 OPEN c_new_sp_auth_flag;
4942 FETCH c_new_sp_auth_flag INTO x_new_sp_auth_flag;
4943
4944 IF c_new_sp_auth_flag%NOTFOUND THEN
4945 raise NO_DATA_FOUND;
4946 END IF;
4947
4948 CLOSE c_new_sp_auth_flag;
4949
4950 EXCEPTION
4951 WHEN NO_DATA_FOUND THEN
4952 x_error_code := SQLCODE;
4953 fnd_message.set_name('XNP','STD_ERROR');
4954 fnd_message.set_token('ERROR_LOCN'
4955 ,'xnp_core.soa_get_new_sp_auth_flag');
4956 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4957 x_error_message := fnd_message.get;
4958
4959 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
4960 fnd_message.set_token('ERROR_TEXT',x_error_message);
4961 fnd_message.set_token('PORTING_ID',p_porting_id);
4962 x_error_message := fnd_message.get;
4963
4964 IF c_new_sp_auth_flag%ISOPEN THEN
4965 CLOSE c_new_sp_auth_flag;
4966 END IF;
4967
4968 WHEN OTHERS THEN
4969 x_error_code := SQLCODE;
4970
4971 fnd_message.set_name('XNP','STD_ERROR');
4972 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_get_new_sp_auth_flag');
4973 fnd_message.set_token('ERROR_TEXT',SQLERRM);
4974 x_error_message := fnd_message.get;
4975
4976 IF c_new_sp_auth_flag%ISOPEN THEN
4977 CLOSE c_new_sp_auth_flag;
4978 END IF;
4979
4980 END SOA_GET_NEW_SP_AUTH_FLAG;
4981
4982 PROCEDURE SOA_GET_OLD_SP_AUTH_FLAG
4983 (p_porting_id VARCHAR2
4984 ,p_local_sp_id NUMBER DEFAULT NULL
4985 ,x_old_sp_auth_flag OUT NOCOPY VARCHAR2
4986 ,x_error_code OUT NOCOPY NUMBER
4987 ,x_error_message OUT NOCOPY VARCHAR2
4988 )
4989
4990 IS
4991 CURSOR c_old_sp_auth_flag IS
4992 SELECT old_sp_authorization_flag
4993 FROM xnp_sv_soa
4994 WHERE object_reference = p_porting_id;
4995
4996 BEGIN
4997 x_error_code := 0;
4998 x_old_sp_auth_flag := 'Y'; -- intialized value
4999
5000 -- Get the old_sp_auth_flag corresponding to
5001 -- this porting_id
5002 OPEN c_old_sp_auth_flag;
5003 FETCH c_old_sp_auth_flag INTO x_old_sp_auth_flag;
5004
5005 IF c_old_sp_auth_flag%NOTFOUND THEN
5006 raise NO_DATA_FOUND;
5007 END IF;
5008
5009 CLOSE c_old_sp_auth_flag;
5010
5011 EXCEPTION
5012 WHEN NO_DATA_FOUND THEN
5013 x_error_code := SQLCODE;
5014 fnd_message.set_name('XNP','STD_ERROR');
5015 fnd_message.set_token('ERROR_LOCN'
5016 ,'xnp_core.soa_get_old_sp_auth_flag');
5017 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5018 x_error_message := fnd_message.get;
5019
5020 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
5021 fnd_message.set_token('ERROR_TEXT',x_error_message);
5022 fnd_message.set_token('PORTING_ID',p_porting_id);
5023 x_error_message := fnd_message.get;
5024
5025 IF c_old_sp_auth_flag%ISOPEN THEN
5026 CLOSE c_old_sp_auth_flag;
5027 END IF;
5028
5029 WHEN OTHERS THEN
5030 x_error_code := SQLCODE;
5031
5032 fnd_message.set_name('XNP','STD_ERROR');
5033 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_get_old_sp_auth_flag');
5034 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5035 x_error_message := fnd_message.get;
5036
5037 IF c_old_sp_auth_flag%ISOPEN THEN
5038 CLOSE c_old_sp_auth_flag;
5039 END IF;
5040
5041 END SOA_GET_OLD_SP_AUTH_FLAG;
5042
5043 PROCEDURE SOA_UPDATE_ACTIVATION_DUE_DATE
5044 (p_PORTING_ID VARCHAR2
5045 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
5046 ,p_ACTIVATION_DUE_DATE DATE
5047 ,p_ORDER_ID IN NUMBER
5048 ,p_LINEITEM_ID IN NUMBER
5049 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5050 ,p_FA_INSTANCE_ID IN NUMBER
5051 ,x_ERROR_CODE OUT NOCOPY NUMBER
5052 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5053 )
5054 IS
5055
5056 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
5057 l_sv_soa_id SV_SOA_ID_TAB;
5058 i BINARY_INTEGER;
5059
5060 e_SOA_UPD_ACTIVATION_DUE_DATE exception;
5061
5062 BEGIN
5063
5064 if(p_ACTIVATION_DUE_DATE = null) then
5065 raise e_SOA_UPD_ACTIVATION_DUE_DATE;
5066 end if;
5067
5068 SELECT sv_soa_id BULK COLLECT
5069 INTO l_sv_soa_id
5070 FROM xnp_sv_soa soa
5071 WHERE soa.object_reference = p_porting_id;
5072
5073 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5074
5075 UPDATE xnp_sv_soa soa
5076 SET soa.activation_due_date = p_activation_due_date ,
5077 soa.modified_date = sysdate,
5078 soa.last_updated_by = fnd_global.user_id,
5079 soa.last_update_date = sysdate
5080 WHERE soa.sv_soa_id = l_sv_soa_id(i);
5081
5082 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
5083
5084 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5085
5086
5087 INSERT INTO XNP_SV_ORDER_MAPPINGS
5088 (sv_order_mapping_id ,
5089 sv_soa_id ,
5090 order_id ,
5091 workitem_instance_id ,
5092 created_by ,
5093 creation_date ,
5094 last_updated_by ,
5095 last_update_date
5096 )
5097 VALUES
5098 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5099 l_sv_soa_id(i) ,
5100 p_order_id ,
5101 p_workitem_instance_id ,
5102 fnd_global.user_id ,
5103 sysdate ,
5104 fnd_global.user_id ,
5105 sysdate
5106 );
5107
5108 EXCEPTION
5109 WHEN dup_val_on_index THEN
5110 null;
5111
5112 WHEN e_SOA_UPD_ACTIVATION_DUE_DATE THEN
5113 x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
5114 fnd_message.set_name('XNP','INVALID_DATE_FORMAT_ERR');
5115 fnd_message.set_token('PORTING_ID',p_porting_id);
5116
5117 WHEN OTHERS THEN
5118 x_ERROR_CODE := SQLCODE;
5119 fnd_message.set_name('XNP','STD_ERROR');
5120 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_ACTIVATION_DUE_DATE');
5121 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5122 x_error_message := fnd_message.get;
5123
5124 END SOA_UPDATE_ACTIVATION_DUE_DATE;
5125
5126
5127 PROCEDURE CHECK_IF_SP_ASSIGNED
5128 (p_STARTING_NUMBER IN VARCHAR2
5129 ,p_ENDING_NUMBER IN VARCHAR2
5130 ,p_SP_ID IN NUMBER
5131 ,x_CHECK_IF_ASSIGNED OUT NOCOPY VARCHAR2
5132 ,x_ERROR_CODE OUT NOCOPY NUMBER
5133 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5134 )
5135 IS
5136 l_STARTING_NUMBER VARCHAR2(80) := null;
5137 l_ENDING_NUMBER VARCHAR2(80) := null;
5138 l_first_owner_sp NUMBER := 0;
5139 l_second_owner_sp NUMBER := 0;
5140 l_assigned_sp_id NUMBER := 0;
5141 l_rownum NUMBER := 0;
5142
5143 CURSOR c_RN_OWNER IS
5144 SELECT DISTINCT sp_id, rownum
5145 FROM xnp_routing_numbers rn ,
5146 xnp_sv_sms sms
5147 WHERE rn.routing_number_id = sms.routing_number_id
5148 AND sms.subscription_tn BETWEEN l_starting_number AND l_ending_number;
5149
5150 BEGIN
5151 x_error_code := 0;
5152 x_check_if_assigned := 'N';
5153
5154 l_starting_number := p_starting_number;
5155 l_ending_number := p_ending_number;
5156
5157 -- The cursor must have just one row returned
5158 -- else it implies that more than one SPs have
5159 -- provisioned this number range
5160 -- So the second fetch should fail
5161 --
5162 OPEN c_rn_owner;
5163 FETCH c_rn_owner INTO l_first_owner_sp, l_rownum;
5164 IF c_rn_owner%NOTFOUND THEN
5165
5166 -- If no entries in SV_SMS table
5167 -- The check if the local sp is the assigned sp
5168
5169 XNP_CORE.GET_ASSIGNED_SP_ID
5170 (l_starting_number
5171 ,l_ending_number
5172 ,l_assigned_sp_id
5173 ,x_error_code
5174 ,x_error_message
5175 );
5176
5177 IF x_error_code <> 0 THEN
5178 close c_rn_owner;
5179 return;
5180 END IF;
5181
5182 IF (p_sp_id = l_assigned_sp_id) THEN
5183 x_check_if_assigned := 'Y';
5184 ELSE
5185 x_check_if_assigned := 'N';
5186 END IF;
5187
5188 close c_rn_owner;
5189 return;
5190
5191 END IF;
5192
5193 -- Check if there is a more than 1 SP which has provisioned
5194 -- the number range
5195 IF (l_rownum < 2) THEN
5196 -- then Only 1 SP has provisioned the entire range
5197 -- Check if this only owner (first) is the local SP
5198
5199 IF (l_first_owner_sp = p_sp_id) THEN
5200 x_check_if_assigned := 'Y';
5201 ELSE
5202 x_check_if_assigned := 'N';
5203 END IF;
5204 ELSE
5205 -- More than one SPs have provisioned this TN range
5206 -- So the local SP can't port out the entire range
5207 x_check_if_assigned := 'N';
5208 END IF;
5209
5210 IF c_rn_owner%ISOPEN THEN
5211 CLOSE c_rn_owner;
5212 END IF;
5213 return;
5214
5215 EXCEPTION
5216 WHEN NO_DATA_FOUND THEN
5217 x_ERROR_CODE := SQLCODE;
5218
5219 fnd_message.set_name('XNP','STD_GET_FAILED');
5220 fnd_message.set_token('FAILED_PROC','XNP_CORE.CHECK_IF_SP_ASSIGNED');
5221 fnd_message.set_token('ATTRNAME','SP_ID');
5222 fnd_message.set_token('KEY','SP_ID');
5223 fnd_message.set_token('VALUE',to_char(p_SP_ID));
5224 x_error_message := fnd_message.get;
5225 x_ERROR_MESSAGE := x_error_message||':'||SQLERRM;
5226
5227 fnd_message.set_name('XNP','CHECK_IF_SP_ASSIGNED_ERR');
5228 fnd_message.set_token('ERROR_TEXT',x_error_message);
5229 x_error_message := fnd_message.get;
5230
5231 IF c_rn_owner%ISOPEN THEN
5232 CLOSE c_rn_owner;
5233 END IF;
5234
5235 WHEN OTHERS THEN
5236 x_ERROR_CODE := SQLCODE;
5237
5238 fnd_message.set_name('XNP','STD_ERROR');
5239 fnd_message.set_token('ERROR_LOCN','XNP_CORE.CHECK_IF_SP_ASSIGNED');
5240 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5241 x_error_message := fnd_message.get;
5242
5243 IF c_rn_owner%ISOPEN THEN
5244 CLOSE c_rn_owner;
5245 END IF;
5246
5247 END CHECK_IF_SP_ASSIGNED;
5248
5249 PROCEDURE SOA_UPDATE_NOTES_INFO
5250 (p_PORTING_ID VARCHAR2
5251 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
5252 ,p_COMMENTS VARCHAR2
5253 ,p_NOTES VARCHAR2
5254 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
5255 ,p_ORDER_ID IN NUMBER
5256 ,p_LINEITEM_ID IN NUMBER
5257 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5258 ,p_FA_INSTANCE_ID IN NUMBER
5259 ,x_ERROR_CODE OUT NOCOPY NUMBER
5260 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5261 )
5262 IS
5263
5264 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
5265 l_sv_soa_id SV_SOA_ID_TAB;
5266 i BINARY_INTEGER;
5267
5268
5269 BEGIN
5270
5271 SELECT sv_soa_id BULK COLLECT
5272 INTO l_sv_soa_id
5273 FROM xnp_sv_soa soa
5274 WHERE soa.object_reference = p_porting_id;
5275
5276 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5277
5278 UPDATE xnp_sv_soa soa
5279 SET soa.comments = p_comments ,
5280 soa.notes = p_notes ,
5281 soa.preorder_authorization_code = p_preorder_authorization_code,
5282 soa.modified_date = sysdate,
5283 last_updated_by = fnd_global.user_id,
5284 last_update_date = sysdate
5285 WHERE soa.sv_soa_id = l_sv_soa_id(i);
5286
5287 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
5288
5289 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5290
5291
5292 INSERT INTO XNP_SV_ORDER_MAPPINGS
5293 (sv_order_mapping_id ,
5294 sv_soa_id ,
5295 order_id ,
5296 workitem_instance_id ,
5297 created_by ,
5298 creation_date ,
5299 last_updated_by ,
5300 last_update_date
5301 )
5302 VALUES
5303 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5304 l_sv_soa_id(i) ,
5305 p_order_id ,
5306 p_workitem_instance_id ,
5307 fnd_global.user_id ,
5308 sysdate ,
5309 fnd_global.user_id ,
5310 sysdate
5311 );
5312 EXCEPTION
5313 WHEN dup_val_on_index THEN
5314 null;
5315 WHEN NO_DATA_FOUND THEN
5316 x_error_code := SQLCODE;
5317 fnd_message.set_name('XNP','STD_ERROR');
5318 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_notes_info');
5319 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5320 x_error_message := fnd_message.get;
5321
5322 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
5323 fnd_message.set_token('ERROR_TEXT',x_error_message);
5324 fnd_message.set_token('PORTING_ID',p_porting_id);
5325 x_error_message := fnd_message.get;
5326
5327 WHEN OTHERS THEN
5328 x_error_code := SQLCODE;
5329
5330 fnd_message.set_name('XNP','STD_ERROR');
5331 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_notes_info');
5332 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5333 x_error_message := fnd_message.get ;
5334
5335 END SOA_UPDATE_NOTES_INFO;
5336
5337 PROCEDURE SOA_UPDATE_NETWORK_INFO
5338 (p_PORTING_ID VARCHAR2
5339 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
5340 ,p_ROUTING_NUMBER_ID NUMBER
5341 ,p_CNAM_ADDRESS VARCHAR2
5342 ,p_CNAM_SUBSYSTEM VARCHAR2
5343 ,p_ISVM_ADDRESS VARCHAR2
5344 ,p_ISVM_SUBSYSTEM VARCHAR2
5345 ,p_LIDB_ADDRESS VARCHAR2
5346 ,p_LIDB_SUBSYSTEM VARCHAR2
5347 ,p_CLASS_ADDRESS VARCHAR2
5348 ,p_CLASS_SUBSYSTEM VARCHAR2
5349 ,p_WSMSC_ADDRESS VARCHAR2
5350 ,p_WSMSC_SUBSYSTEM VARCHAR2
5351 ,p_RN_ADDRESS VARCHAR2
5352 ,p_RN_SUBSYSTEM VARCHAR2
5353 ,p_ORDER_ID IN NUMBER
5354 ,p_LINEITEM_ID IN NUMBER
5355 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5356 ,p_FA_INSTANCE_ID IN NUMBER
5357 ,x_ERROR_CODE OUT NOCOPY NUMBER
5358 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5359 )
5360 IS
5361
5362 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
5363 l_sv_soa_id SV_SOA_ID_TAB;
5364 i BINARY_INTEGER;
5365
5366 BEGIN
5367
5368 SELECT sv_soa_id BULK COLLECT
5369 INTO l_sv_soa_id
5370 FROM xnp_sv_soa soa
5371 WHERE soa.object_reference = p_porting_id;
5372
5373 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5374
5375 UPDATE xnp_sv_soa soa
5376 SET soa.routing_number_id = p_routing_number_id ,
5377 soa.cnam_address = p_cnam_address ,
5378 soa.cnam_subsystem = p_cnam_subsystem ,
5379 soa.isvm_address = p_isvm_address ,
5380 soa.isvm_subsystem = p_isvm_subsystem ,
5381 soa.lidb_address = p_lidb_address ,
5382 soa.lidb_subsystem = p_lidb_subsystem ,
5383 soa.class_address = p_class_address ,
5384 soa.class_subsystem = p_class_subsystem ,
5385 soa.wsmsc_address = p_wsmsc_address ,
5386 soa.wsmsc_subsystem = p_wsmsc_subsystem ,
5387 soa.rn_address = p_rn_address ,
5388 soa.rn_subsystem = p_rn_subsystem ,
5389 soa.modified_date = sysdate ,
5390 soa.last_updated_by = fnd_global.user_id ,
5391 soa.last_update_date = sysdate
5392 WHERE soa.sv_soa_id = l_sv_soa_id(i);
5393
5394 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
5395
5396 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5397
5398
5399 INSERT INTO XNP_SV_ORDER_MAPPINGS
5400 (sv_order_mapping_id ,
5401 sv_soa_id ,
5402 order_id ,
5403 workitem_instance_id ,
5404 created_by ,
5405 creation_date ,
5406 last_updated_by ,
5407 last_update_date
5408 )
5409 VALUES
5410 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5411 l_sv_soa_id(i) ,
5412 p_order_id ,
5413 p_workitem_instance_id ,
5414 fnd_global.user_id ,
5415 sysdate ,
5416 fnd_global.user_id ,
5417 sysdate
5418 );
5419
5420 EXCEPTION
5421 WHEN dup_val_on_index THEN
5422 null;
5423 WHEN NO_DATA_FOUND THEN
5424 x_error_code := SQLCODE;
5425 fnd_message.set_name('XNP','STD_ERROR');
5426 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_network_info');
5427 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5428 x_error_message := fnd_message.get;
5429
5430 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
5431 fnd_message.set_token('ERROR_TEXT',x_error_message);
5432 fnd_message.set_token('PORTING_ID',p_porting_id);
5433 x_error_message := fnd_message.get;
5434
5435 WHEN OTHERS THEN
5436 x_error_code := SQLCODE;
5437
5438 fnd_message.set_name('XNP','STD_ERROR');
5439 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_network_info');
5440 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5441 x_error_message := fnd_message.get;
5442
5443 END SOA_UPDATE_NETWORK_INFO;
5444
5445 PROCEDURE SOA_UPDATE_CUSTOMER_INFO
5446 (p_PORTING_ID VARCHAR2
5447 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
5448 ,p_CUSTOMER_ID VARCHAR2
5449 ,p_CUSTOMER_NAME VARCHAR2
5450 ,p_CUSTOMER_TYPE VARCHAR2
5451 ,p_ADDRESS_LINE1 VARCHAR2
5452 ,p_ADDRESS_LINE2 VARCHAR2
5453 ,p_CITY VARCHAR2
5454 ,p_PHONE VARCHAR2
5455 ,p_FAX VARCHAR2
5456 ,p_EMAIL VARCHAR2
5457 ,p_PAGER VARCHAR2
5458 ,p_PAGER_PIN VARCHAR2
5459 ,p_INTERNET_ADDRESS VARCHAR2
5460 ,p_ZIP_CODE VARCHAR2
5461 ,p_COUNTRY VARCHAR2
5462 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
5463 ,p_CONTACT_NAME VARCHAR2
5464 ,p_ORDER_ID IN NUMBER
5465 ,p_LINEITEM_ID IN NUMBER
5466 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5467 ,p_FA_INSTANCE_ID IN NUMBER
5468 ,x_ERROR_CODE OUT NOCOPY NUMBER
5469 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5470 )
5471 IS
5472
5473 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
5474 l_sv_soa_id SV_SOA_ID_TAB;
5475 i BINARY_INTEGER;
5476
5477
5478 BEGIN
5479
5480 SELECT sv_soa_id BULK COLLECT
5481 INTO l_sv_soa_id
5482 FROM xnp_sv_soa soa
5483 WHERE soa.object_reference = p_porting_id;
5484
5485 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5486
5487 UPDATE xnp_sv_soa soa
5488 SET soa.customer_id = p_customer_id ,
5489 soa.customer_name = p_customer_name ,
5490 soa.customer_type = p_customer_type ,
5491 soa.address_line1 = p_address_line1 ,
5492 soa.address_line2 = p_address_line2 ,
5493 soa.city = p_city ,
5494 soa.phone = p_phone ,
5495 soa.fax = p_fax ,
5496 soa.email = p_email ,
5497 soa.zip_code = p_zip_code ,
5498 soa.country = p_country ,
5499 soa.customer_contact_req_flag = p_customer_contact_req_flag ,
5500 soa.contact_name = p_contact_name ,
5501 soa.pager = p_pager ,
5502 soa.pager_pin = p_pager_pin ,
5503 soa.internet_address = p_internet_address ,
5504 soa.modified_date = sysdate,
5505 soa.last_updated_by = fnd_global.user_id,
5506 soa.last_update_date = sysdate
5507 WHERE soa.sv_soa_id = l_sv_soa_id(i);
5508
5509 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
5510
5511 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5512
5513
5514 INSERT INTO XNP_SV_ORDER_MAPPINGS
5515 (sv_order_mapping_id ,
5516 sv_soa_id ,
5517 order_id ,
5518 workitem_instance_id ,
5519 created_by ,
5520 creation_date ,
5521 last_updated_by ,
5522 last_update_date
5523 )
5524 VALUES
5525 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5526 l_sv_soa_id(i) ,
5527 p_order_id ,
5528 p_workitem_instance_id ,
5529 fnd_global.user_id ,
5530 sysdate ,
5531 fnd_global.user_id ,
5532 sysdate
5533 );
5534
5535 EXCEPTION
5536 WHEN dup_val_on_index THEN
5537 null;
5538 WHEN NO_DATA_FOUND THEN
5539 x_error_code := SQLCODE;
5540 fnd_message.set_name('XNP','STD_ERROR');
5541 fnd_message.set_token('ERROR_LOCN'
5542 ,'xnp_core.soa_update_customer_info');
5543 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5544 x_error_message := fnd_message.get;
5545
5546 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
5547 fnd_message.set_token('ERROR_TEXT',x_error_message);
5548 fnd_message.set_token('PORTING_ID',p_porting_id);
5549 x_error_message := fnd_message.get;
5550
5551 WHEN OTHERS THEN
5552 x_error_code := SQLCODE;
5553
5554 fnd_message.set_name('XNP','STD_ERROR');
5555 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_customer_info');
5556 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5557 x_error_message := fnd_message.get;
5558
5559 END SOA_UPDATE_CUSTOMER_INFO;
5560
5561
5562 PROCEDURE SOA_UPDATE_PORTING_ID
5563 (p_STARTING_NUMBER VARCHAR2
5564 ,p_ENDING_NUMBER VARCHAR2
5565 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
5566 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
5567 ,p_PORTING_ID VARCHAR2
5568 ,p_ORDER_ID NUMBER
5569 ,p_LINEITEM_ID IN NUMBER
5570 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5571 ,p_FA_INSTANCE_ID IN NUMBER
5572 ,x_ERROR_CODE OUT NOCOPY NUMBER
5573 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5574 )
5575 IS
5576
5577 l_SV_ID number :=0;
5578 l_PHASE_INDICATOR varchar2(200) := null;
5579 l_STARTING_NUMBER VARCHAR2(80) := null;
5580 l_ENDING_NUMBER VARCHAR2(80) := null;
5581 e_UPDATE_PORTING_ID exception;
5582
5583 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
5584 l_sv_soa_id SV_SOA_ID_TAB;
5585 i BINARY_INTEGER;
5586
5587 BEGIN
5588 x_ERROR_CODE := 0;
5589
5590 l_starting_number := p_starting_number;
5591 l_ending_number := p_ending_number;
5592
5593 -- Get the phase corresponding to this 'p_CUR_STATUS_TYPE_CODE'
5594
5595 XNP_CORE.GET_PHASE_FOR_STATUS
5596 (p_CUR_STATUS_TYPE_CODE
5597 ,l_PHASE_INDICATOR
5598 ,x_ERROR_CODE
5599 ,x_ERROR_MESSAGE
5600 );
5601 IF x_ERROR_CODE <> 0
5602 THEN
5603 RETURN;
5604 END IF;
5605
5606 --
5607 -- For each TN Get the SVid which is in this phase
5608 -- and update the porting id
5609 --
5610 SELECT sv_soa_id BULK COLLECT
5611 INTO l_sv_soa_id
5612 FROM xnp_sv_soa soa,
5613 xnp_sv_status_types_b sta
5614 WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
5615 AND sta.phase_indicator = l_phase_indicator
5616 AND sta.status_type_code = soa.status_type_code;
5617
5618 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5619
5620 UPDATE xnp_sv_soa soa
5621 SET soa.object_reference = p_porting_id,
5622 soa.modified_date = sysdate,
5623 soa.last_updated_by = fnd_global.user_id,
5624 soa.last_update_date = sysdate
5625 WHERE soa.sv_soa_id = l_sv_soa_id(i);
5626
5627 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
5628
5629 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
5630
5631
5632 INSERT INTO XNP_SV_ORDER_MAPPINGS
5633 (sv_order_mapping_id ,
5634 sv_soa_id ,
5635 order_id ,
5636 workitem_instance_id ,
5637 created_by ,
5638 creation_date ,
5639 last_updated_by ,
5640 last_update_date
5641 )
5642 VALUES
5643 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5644 l_sv_soa_id(i) ,
5645 p_order_id ,
5646 p_workitem_instance_id ,
5647 fnd_global.user_id ,
5648 sysdate ,
5649 fnd_global.user_id ,
5650 sysdate
5651 );
5652
5653 -- Update the order header with the porting id
5654
5655 xdp_engine.set_order_reference
5656 (p_order_id =>p_order_id
5657 ,p_order_ref_name =>'PORTING_ID'
5658 ,p_order_ref_value =>p_porting_id
5659 ,x_return_code =>x_error_code
5660 ,x_error_description =>x_error_message
5661 );
5662
5663 IF (x_error_code <> 0) THEN
5664 RAISE e_UPDATE_PORTING_ID;
5665 END IF;
5666
5667
5668 EXCEPTION
5669 WHEN dup_val_on_index THEN
5670 null;
5671 WHEN NO_DATA_FOUND THEN
5672 x_ERROR_CODE := SQLCODE;
5673 fnd_message.set_name('XNP','STD_GET_FAILED');
5674 fnd_message.set_token('FAILED_PROC','XNP_CORE.SOA_UPDATE_PORTING_ID');
5675 fnd_message.set_token('ATTRNAME','PORTING_ID:ORDER_ID');
5676 fnd_message.set_token('KEY','ORDER_ID:PORTING_ID');
5677 fnd_message.set_token('VALUE',to_char(p_order_id)||':'||p_porting_id);
5678 x_error_message := fnd_message.get;
5679 x_ERROR_MESSAGE := x_error_message||':'||SQLERRM;
5680
5681 WHEN OTHERS THEN
5682 x_ERROR_CODE := SQLCODE;
5683 fnd_message.set_name('XNP','STD_ERROR');
5684 fnd_message.set_token('ERROR_LOCN'
5685 ,'XNP_CORE.SOA_UPDATE_PORTING_ID');
5686 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5687 x_error_message := fnd_message.get;
5688
5689 END SOA_UPDATE_PORTING_ID;
5690
5691 PROCEDURE SMS_UPDATE_PROV_DONE_DATE
5692 (p_STARTING_NUMBER VARCHAR2
5693 ,p_ENDING_NUMBER VARCHAR2
5694 ,p_ORDER_ID IN NUMBER
5695 ,p_LINEITEM_ID IN NUMBER
5696 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5697 ,p_FA_INSTANCE_ID IN NUMBER
5698 ,x_ERROR_CODE OUT NOCOPY NUMBER
5699 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5700 )
5701 IS
5702
5703 TYPE sv_sms_id_tab IS TABLE OF NUMBER;
5704 l_sv_sms_id SV_SMS_ID_TAB;
5705 i BINARY_INTEGER;
5706
5707 BEGIN
5708
5709 SELECT sv_sms_id BULK COLLECT
5710 INTO l_sv_sms_id
5711 FROM xnp_sv_sms sms
5712 WHERE sms.subscription_tn BETWEEN p_starting_number AND p_ending_number ;
5713
5714 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
5715
5716 UPDATE xnp_sv_sms sms
5717 SET sms.provision_done_date = sysdate,
5718 sms.last_updated_by = fnd_global.user_id,
5719 sms.last_update_date = sysdate
5720 WHERE sms.sv_sms_id = l_sv_sms_id(i);
5721
5722 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
5723
5724 FORALL i IN l_sv_sms_id.first..l_sv_sms_id.last
5725
5726
5727 INSERT INTO XNP_SV_ORDER_MAPPINGS
5728 (sv_order_mapping_id ,
5729 sv_sms_id ,
5730 order_id ,
5731 workitem_instance_id ,
5732 created_by ,
5733 creation_date ,
5734 last_updated_by ,
5735 last_update_date
5736 )
5737 VALUES
5738 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5739 l_sv_sms_id(i) ,
5740 p_order_id ,
5741 p_workitem_instance_id ,
5742 fnd_global.user_id ,
5743 sysdate ,
5744 fnd_global.user_id ,
5745 sysdate
5746 );
5747
5748 EXCEPTION
5749 WHEN dup_val_on_index THEN
5750 null;
5751 WHEN OTHERS THEN
5752
5753 x_error_code := SQLCODE;
5754 x_error_message := SQLERRM;
5755
5756 END SMS_UPDATE_PROV_DONE_DATE;
5757
5758 -- Check whether Routing Number belongs to Recipient SP
5759
5760 PROCEDURE CHECK_RN_FOR_RECIPIENT
5761 (p_RECIPIENT_SP_ID IN NUMBER
5762 ,p_ROUTING_NUMBER_ID IN NUMBER
5763 ,x_ERROR_CODE OUT NOCOPY NUMBER
5764 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5765 )
5766 IS
5767 l_dummy VARCHAR2(1):=Null;
5768
5769 CURSOR c_rn_for_recipient IS
5770 SELECT '1'
5771 FROM XNP_ROUTING_NUMBERS
5772 WHERE routing_number_id = p_ROUTING_NUMBER_ID
5773 AND sp_id = p_RECIPIENT_SP_ID
5774 AND active_flag='Y';
5775
5776
5777 BEGIN
5778 x_ERROR_CODE:=0;
5779
5780
5781 OPEN c_rn_for_recipient;
5782 FETCH c_rn_for_recipient INTO l_dummy;
5783
5784 IF c_rn_for_recipient%NOTFOUND THEN
5785 raise NO_DATA_FOUND;
5786 END IF;
5787
5788 CLOSE c_rn_for_recipient;
5789
5790 EXCEPTION
5791 WHEN NO_DATA_FOUND THEN
5792 x_error_code := SQLCODE;
5793
5794 fnd_message.set_name('XNP','STD_GET_FAILED');
5795 fnd_message.set_token('FAILED_PROC','XNP_CORE.CHECK_RN_FOR_RECIPIENT');
5796 fnd_message.set_token('ATTRNAME','ROUTING_NUMBER_ID');
5797 fnd_message.set_token('KEY','ROUTING_NUMBER_ID');
5798 fnd_message.set_token('VALUE' ,to_char(p_ROUTING_NUMBER_ID));
5799 x_error_message := fnd_message.get;
5800 x_error_message := x_error_message||':'||SQLERRM;
5801
5802 fnd_message.set_name('XNP','CHECK_RN_FOR_RECIPIENT');
5803 fnd_message.set_token('ERROR_TEXT',x_error_message);
5804 x_error_message := fnd_message.get;
5805
5806
5807 IF c_rn_for_recipient%ISOPEN THEN
5808 CLOSE c_rn_for_recipient;
5809 END IF;
5810
5811 WHEN OTHERS THEN
5812 x_error_code := SQLCODE;
5813 fnd_message.set_name('XNP','STD_ERROR');
5814 fnd_message.set_token('ERROR_LOCN','XNP_CORE.CHECK_RN_FOR_RECIPIENT');
5815 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5816 x_error_message := fnd_message.get;
5817
5818 IF c_rn_for_recipient%ISOPEN THEN
5819 CLOSE c_rn_for_recipient;
5820 END IF;
5821
5822 END CHECK_RN_FOR_RECIPIENT;
5823
5824 -- Runtime Validation Check for Np Work Item
5825
5826 PROCEDURE RUNTIME_VALIDATION
5827 ( p_ORDER_ID IN NUMBER
5828 ,p_LINE_ITEM_ID IN NUMBER
5829 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5830 ,p_STARTING_NUMBER IN NUMBER
5831 ,p_ENDING_NUMBER IN NUMBER
5832 ,p_ROUTING_NUMBER IN VARCHAR2
5833 ,p_DONOR_SP_CODE IN VARCHAR2
5834 ,p_RECIPIENT_SP_CODE IN VARCHAR2
5835 ,x_ERROR_CODE OUT NOCOPY NUMBER
5836 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5837 )
5838 IS
5839 l_ERROR_MESSAGE VARCHAR2(2000) := NULL;
5840 l_NUMBER_RANGE_ID NUMBER :=0;
5841 l_ROUTING_NUMBER_ID NUMBER :=0;
5842 l_RECIPIENT_SP_ID NUMBER :=0;
5843 l_DONOR_SP_ID NUMBER := 0;
5844
5845 BEGIN
5846 -- Get the routing_number_id corresponding to the code
5847
5848 GET_ROUTING_NUMBER_ID
5849 (p_ROUTING_NUMBER =>p_ROUTING_NUMBER
5850 ,x_ROUTING_NUMBER_ID=>l_ROUTING_NUMBER_ID
5851 ,x_ERROR_CODE =>x_ERROR_CODE
5852 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
5853 );
5854
5855 IF (x_error_code <> 0) THEN
5856 l_error_message:= FND_GLOBAL.NEWLINE|| l_error_message||x_error_message;
5857
5858 END IF;
5859
5860 -- Get the SP id for this recipient code
5861 GET_SP_ID
5862 (p_SP_NAME => p_RECIPIENT_SP_CODE
5863 ,x_SP_ID => l_RECIPIENT_SP_ID
5864 ,x_ERROR_CODE => x_ERROR_CODE
5865 ,x_ERROR_MESSAGE => x_ERROR_MESSAGE
5866 );
5867
5868 IF x_ERROR_CODE <> 0 THEN
5869 l_error_message:=FND_GLOBAL.NEWLINE||l_error_message||x_error_message;
5870 END IF;
5871
5872 --Check for RN belongs to Recipient
5873 IF l_ROUTING_NUMBER_ID<>0 AND l_RECIPIENT_SP_ID <>0 THEN
5874 CHECK_RN_FOR_RECIPIENT( p_RECIPIENT_SP_ID =>l_RECIPIENT_SP_ID
5875 ,p_ROUTING_NUMBER_ID =>l_ROUTING_NUMBER_ID
5876 ,x_ERROR_CODE =>x_ERROR_CODE
5877 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
5878 );
5879
5880
5881 IF x_ERROR_CODE <> 0 THEN
5882 l_error_message:= FND_GLOBAL.NEWLINE||l_error_message||x_error_message;
5883 END IF;
5884 END IF;
5885
5886
5887 -- Verify if its a valid number range
5888
5889 get_number_range_id(p_STARTING_NUMBER => p_STARTING_NUMBER,
5890 p_ENDING_NUMBER => p_ENDING_NUMBER,
5891 x_NUMBER_RANGE_ID => l_NUMBER_RANGE_ID,
5892 x_error_code => x_error_code,
5893 x_error_message => x_error_message
5894 );
5895 IF (x_error_code <> 0) THEN
5896 l_error_message:=FND_GLOBAL.NEWLINE|| l_error_message||x_error_message;
5897
5898 END IF;
5899
5900
5901 -- Get the SP id for this SP code
5902 GET_SP_ID
5903 (p_SP_NAME =>p_DONOR_SP_CODE
5904 ,x_SP_ID =>l_DONOR_SP_ID
5905 ,x_ERROR_CODE=>x_ERROR_CODE
5906 ,x_ERROR_MESSAGE=>x_ERROR_MESSAGE
5907 );
5908
5909 IF x_ERROR_CODE <> 0 THEN
5910
5911 l_error_message:= FND_GLOBAL.NEWLINE|| l_error_message||x_error_message;
5912 END IF;
5913
5914
5915 -- Return x_error code and x_error_message
5916 IF l_error_message is NOT NULL THEN
5917 FND_MESSAGE.SET_NAME('XNP','XNP_RVU_VALIDATION_FAILED');
5918 FND_MESSAGE.SET_TOKEN('ORDER_ID',xdp_order.G_external_order_reference);
5919 FND_MESSAGE.SET_TOKEN('WORKITEM_NAME',XDP_OA_UTIL.g_Workitem_Name);
5920 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_message);
5921 x_error_message:= SUBSTR(FND_MESSAGE.GET,1,4000); -- rnyberg 01/26/2001. Added substr since max 4000 chars can be handled in parameter. To fix bug 1580568.
5922 X_ERROR_CODE:= -( FND_MESSAGE.GET_NUMBER('XNP',
5923 'XNP_RVU_VALIDATION_FAILED'));
5924 END IF;
5925
5926 EXCEPTION
5927 WHEN OTHERS THEN
5928 x_error_code := SQLCODE;
5929
5930 fnd_message.set_name('XNP','STD_ERROR');
5931 fnd_message.set_token('ERROR_LOCN','XNP_CORE.RUNTIME_VALIDATION');
5932 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5933 x_error_message := fnd_message.get;
5934
5935
5936 End RUNTIME_VALIDATION;
5937
5938 -- Procedure to create record in XNP_SV_ORDER_MAPPING
5939
5940 PROCEDURE CREATE_ORDER_MAPPING
5941 (p_ORDER_ID IN NUMBER,
5942 p_LINEITEM_ID IN NUMBER,
5943 p_WORKITEM_INSTANCE_ID IN NUMBER,
5944 p_FA_INSTANCE_ID IN NUMBER,
5945 p_SV_SOA_ID IN NUMBER,
5946 p_SV_SMS_ID IN NUMBER,
5947 x_ERROR_CODE OUT NOCOPY NUMBER,
5948 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
5949 ) IS
5950
5951 l_error_message VARCHAR2(2000) := NULL;
5952 l_sv_order_mapping_id NUMBER;
5953
5954 BEGIN
5955
5956 INSERT INTO XNP_SV_ORDER_MAPPINGS
5957 (sv_order_mapping_id ,
5958 sv_soa_id ,
5959 sv_sms_id ,
5960 order_id ,
5961 workitem_instance_id ,
5962 created_by ,
5963 creation_date ,
5964 last_updated_by ,
5965 last_update_date
5966 )
5967 VALUES
5968 (XNP_SV_ORDER_MAPPINGS_S.nextval,
5969 p_sv_soa_id ,
5970 p_sv_sms_id ,
5971 p_order_id ,
5972 p_workitem_instance_id ,
5973 fnd_global.user_id ,
5974 sysdate ,
5975 fnd_global.user_id ,
5976 sysdate
5977 );
5978
5979 EXCEPTION
5980 WHEN dup_val_on_index THEN
5981 null;
5982 WHEN others THEN
5983 x_error_code := sqlcode;
5984 fnd_message.set_name('XNP','STD ERROR');
5985 fnd_message.set_token('ERROR_LOCN','XNP_CORE.CREATE_ORDER_MAPPING');
5986 fnd_message.set_token('ERROR_TEXT',SQLERRM);
5987 x_error_message := fnd_message.get;
5988
5989 END CREATE_ORDER_MAPPING;
5990
5991 PROCEDURE SOA_UPDATE_DISCONN_DUE_DATE
5992 (p_PORTING_ID VARCHAR2
5993 ,p_DISCONNECT_DUE_DATE DATE
5994 ,p_ORDER_ID IN NUMBER
5995 ,p_LINEITEM_ID IN NUMBER
5996 ,p_WORKITEM_INSTANCE_ID IN NUMBER
5997 ,p_FA_INSTANCE_ID IN NUMBER
5998 ,x_ERROR_CODE OUT NOCOPY NUMBER
5999 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
6000 )
6001 IS
6002
6003 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
6004 l_sv_soa_id SV_SOA_ID_TAB;
6005 i BINARY_INTEGER;
6006
6007 e_SOA_UPDATE_DISCON_DUE_DATE exception;
6008
6009 BEGIN
6010
6011 if(p_disconnect_due_date = null) then
6012 raise e_SOA_UPDATE_DISCON_DUE_DATE;
6013 end if;
6014
6015 SELECT sv_soa_id BULK COLLECT
6016 INTO l_sv_soa_id
6017 FROM xnp_sv_soa soa
6018 WHERE soa.object_reference = p_porting_id;
6019
6020 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6021
6022 UPDATE xnp_sv_soa soa
6023 SET soa.disconnect_due_date = p_disconnect_due_date,
6024 soa.modified_date = sysdate,
6025 soa.last_updated_by = fnd_global.user_id,
6026 soa.last_update_date = sysdate
6027 WHERE soa.sv_soa_id = l_sv_soa_id(i);
6028
6029 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
6030
6031 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6032
6033
6034 INSERT INTO XNP_SV_ORDER_MAPPINGS
6035 (sv_order_mapping_id ,
6036 sv_soa_id ,
6037 order_id ,
6038 workitem_instance_id ,
6039 created_by ,
6040 creation_date ,
6041 last_updated_by ,
6042 last_update_date
6043 )
6044 VALUES
6045 (XNP_SV_ORDER_MAPPINGS_S.nextval,
6046 l_sv_soa_id(i) ,
6047 p_order_id ,
6048 p_workitem_instance_id ,
6049 fnd_global.user_id ,
6050 sysdate ,
6051 fnd_global.user_id ,
6052 sysdate
6053 );
6054
6055 EXCEPTION
6056 WHEN dup_val_on_index THEN
6057 null;
6058 WHEN e_SOA_UPDATE_DISCON_DUE_DATE THEN
6059 x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
6060 fnd_message.set_name('XNP','INVALID_DATE_FORMAT_ERR');
6061 fnd_message.set_token('PORTING_ID',p_porting_id);
6062 WHEN OTHERS THEN
6063 x_ERROR_CODE := SQLCODE;
6064 fnd_message.set_name('XNP','STD_ERROR');
6065 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_DISCONNECT_DUE_DATE');
6066 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6067 x_error_message := fnd_message.get;
6068
6069 END SOA_UPDATE_DISCONN_DUE_DATE;
6070
6071
6072 PROCEDURE SOA_UPDATE_EFFECT_REL_DUE_DATE
6073 (p_PORTING_ID VARCHAR2
6074 ,p_EFFECTIVE_RELEASE_DUE_DATE DATE
6075 ,p_ORDER_ID IN NUMBER
6076 ,p_LINEITEM_ID IN NUMBER
6077 ,p_WORKITEM_INSTANCE_ID IN NUMBER
6078 ,p_FA_INSTANCE_ID IN NUMBER
6079 ,x_ERROR_CODE OUT NOCOPY NUMBER
6080 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
6081 )
6082 IS
6083
6084 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
6085 l_sv_soa_id SV_SOA_ID_TAB;
6086 i BINARY_INTEGER;
6087
6088 e_SOA_UPDATE_EFF_REL_DUE_DATE exception;
6089
6090 BEGIN
6091
6092 if(p_EFFECTIVE_RELEASE_DUE_DATE = null) then
6093 raise e_SOA_UPDATE_EFF_REL_DUE_DATE;
6094 end if;
6095
6096 SELECT sv_soa_id BULK COLLECT
6097 INTO l_sv_soa_id
6098 FROM xnp_sv_soa soa
6099 WHERE soa.object_reference = p_porting_id;
6100
6101 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6102
6103 UPDATE xnp_sv_soa soa
6104 SET soa.effective_release_due_date = p_effective_release_due_date,
6105 soa.modified_date = sysdate,
6106 soa.last_updated_by = fnd_global.user_id,
6107 soa.last_update_date = sysdate
6108 WHERE soa.sv_soa_id = l_sv_soa_id(i);
6109
6110 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
6111
6112 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6113
6114
6115 INSERT INTO XNP_SV_ORDER_MAPPINGS
6116 (sv_order_mapping_id ,
6117 sv_soa_id ,
6118 order_id ,
6119 workitem_instance_id ,
6120 created_by ,
6121 creation_date ,
6122 last_updated_by ,
6123 last_update_date
6124 )
6125 VALUES
6126 (XNP_SV_ORDER_MAPPINGS_S.nextval,
6127 l_sv_soa_id(i) ,
6128 p_order_id ,
6129 p_workitem_instance_id ,
6130 fnd_global.user_id ,
6131 sysdate ,
6132 fnd_global.user_id ,
6133 sysdate
6134 );
6135
6136 EXCEPTION
6137 WHEN dup_val_on_index THEN
6138 null;
6139 WHEN e_SOA_UPDATE_EFF_REL_DUE_DATE THEN
6140 x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
6141 fnd_message.set_name('XNP','INVALID_DATE_FORMAT_ERR');
6142 fnd_message.set_token('PORTING_ID',p_porting_id);
6143 WHEN OTHERS THEN
6144 x_ERROR_CODE := SQLCODE;
6145 fnd_message.set_name('XNP','STD_ERROR');
6146 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_EFFECT_REL_DUE_DATE');
6147 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6148 x_error_message := fnd_message.get;
6149
6150 END SOA_UPDATE_EFFECT_REL_DUE_DATE;
6151
6152 PROCEDURE SOA_UPDATE_NUM_RETURN_DUE_DATE
6153 (p_PORTING_ID VARCHAR2
6154 ,p_NUMBER_RETURNED_DUE_DATE DATE
6155 ,p_ORDER_ID IN NUMBER
6156 ,p_LINEITEM_ID IN NUMBER
6157 ,p_WORKITEM_INSTANCE_ID IN NUMBER
6158 ,p_FA_INSTANCE_ID IN NUMBER
6159 ,x_ERROR_CODE OUT NOCOPY NUMBER
6160 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
6161 )
6162 IS
6163
6164 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
6165 l_sv_soa_id SV_SOA_ID_TAB;
6166 i BINARY_INTEGER;
6167
6168 e_SOA_UPDATE_NUM_RET_DUE_DATE exception;
6169
6170 BEGIN
6171
6172 if(p_NUMBER_RETURNED_DUE_DATE = null) then
6173 raise e_SOA_UPDATE_NUM_RET_DUE_DATE;
6174 end if;
6175
6176 SELECT sv_soa_id BULK COLLECT
6177 INTO l_sv_soa_id
6178 FROM xnp_sv_soa soa
6179 WHERE soa.object_reference = p_porting_id;
6180
6181 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6182
6183 UPDATE xnp_sv_soa soa
6184 SET soa.NUMBER_RETURNED_DUE_DATE = p_NUMBER_RETURNED_DUE_DATE,
6185 soa.modified_date = sysdate,
6186 soa.last_updated_by = fnd_global.user_id,
6187 soa.last_update_date = sysdate
6188 WHERE soa.sv_soa_id = l_sv_soa_id(i);
6189
6190 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
6191
6192 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6193
6194
6195 INSERT INTO XNP_SV_ORDER_MAPPINGS
6196 (sv_order_mapping_id ,
6197 sv_soa_id ,
6198 order_id ,
6199 workitem_instance_id ,
6200 created_by ,
6201 creation_date ,
6202 last_updated_by ,
6203 last_update_date
6204 )
6205 VALUES
6206 (XNP_SV_ORDER_MAPPINGS_S.nextval,
6207 l_sv_soa_id(i) ,
6208 p_order_id ,
6209 p_workitem_instance_id ,
6210 fnd_global.user_id ,
6211 sysdate ,
6212 fnd_global.user_id ,
6213 sysdate
6214 );
6215
6216 EXCEPTION
6217 WHEN dup_val_on_index THEN
6218 null;
6219 WHEN e_SOA_UPDATE_NUM_RET_DUE_DATE THEN
6220 x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
6221 fnd_message.set_name('XNP','INVALID_DATE_FORMAT_ERR');
6222 fnd_message.set_token('PORTING_ID',p_porting_id);
6223 WHEN OTHERS THEN
6224 x_ERROR_CODE := SQLCODE;
6225 fnd_message.set_name('XNP','STD_ERROR');
6226 fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_NUM_RETURN_DUE_DATE');
6227 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6228 x_error_message := fnd_message.get;
6229
6230 END SOA_UPDATE_NUM_RETURN_DUE_DATE;
6231
6232 PROCEDURE SOA_SET_CONCURRENCE_FLAG
6233 (P_PORTING_ID VARCHAR2
6234 ,P_LOCAL_SP_ID NUMBER DEFAULT NULL
6235 ,P_CONCURRENCE_FLAG VARCHAR2
6236 ,p_ORDER_ID IN NUMBER
6237 ,p_LINEITEM_ID IN NUMBER
6238 ,p_WORKITEM_INSTANCE_ID IN NUMBER
6239 ,p_FA_INSTANCE_ID IN NUMBER
6240 ,X_ERROR_CODE OUT NOCOPY NUMBER
6241 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
6242 )
6243 IS
6244
6245 TYPE sv_soa_id_tab IS TABLE OF NUMBER;
6246 l_sv_soa_id SV_SOA_ID_TAB;
6247 i BINARY_INTEGER;
6248
6249 BEGIN
6250 x_error_code := 0;
6251
6252 SELECT sv_soa_id BULK COLLECT
6253 INTO l_sv_soa_id
6254 FROM xnp_sv_soa soa
6255 WHERE soa.object_reference = p_porting_id;
6256
6257 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6258
6259 UPDATE xnp_sv_soa soa
6260 SET soa.concurrence_flag = p_concurrence_flag,
6261 soa.modified_date = sysdate,
6262 soa.last_updated_by = fnd_global.user_id,
6263 soa.last_update_date = sysdate
6264 WHERE soa.sv_soa_id = l_sv_soa_id(i);
6265
6266 -- Create a mapping record in XNP_SV_ORDER_MAPPINGS table
6267
6268 FORALL i IN l_sv_soa_id.first..l_sv_soa_id.last
6269
6270
6271 INSERT INTO XNP_SV_ORDER_MAPPINGS
6272 (sv_order_mapping_id ,
6273 sv_soa_id ,
6274 order_id ,
6275 workitem_instance_id ,
6276 created_by ,
6277 creation_date ,
6278 last_updated_by ,
6279 last_update_date
6280 )
6281 VALUES
6282 (XNP_SV_ORDER_MAPPINGS_S.nextval,
6283 l_sv_soa_id(i) ,
6284 p_order_id ,
6285 p_workitem_instance_id ,
6286 fnd_global.user_id ,
6287 sysdate ,
6288 fnd_global.user_id ,
6289 sysdate
6290 );
6291 EXCEPTION
6292 WHEN dup_val_on_index THEN
6293 null;
6294 WHEN NO_DATA_FOUND THEN
6295 x_error_code := SQLCODE;
6296 fnd_message.set_name('XNP','STD_ERROR');
6297 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_set_concurrence_flag');
6298 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6299 x_error_message := fnd_message.get;
6300
6301 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
6302 fnd_message.set_token('ERROR_TEXT',x_error_message);
6303 fnd_message.set_token('PORTING_ID',p_porting_id);
6304 x_error_message := fnd_message.get;
6305
6306 WHEN OTHERS THEN
6307 x_error_code := SQLCODE;
6308
6309 fnd_message.set_name('XNP','STD_ERROR');
6310 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_set_concurrence_flag');
6311 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6312 x_error_message := fnd_message.get;
6313
6314 END SOA_SET_CONCURRENCE_FLAG;
6315
6316 PROCEDURE SOA_GET_CONCURRENCE_FLAG
6317 (P_PORTING_ID VARCHAR2
6318 ,P_LOCAL_SP_ID NUMBER DEFAUlT NULL
6319 ,X_CONCURRENCE_FLAG OUT NOCOPY VARCHAR2
6320 ,X_ERROR_CODE OUT NOCOPY NUMBER
6321 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
6322 )
6323 IS
6324
6325 CURSOR c_concurrence_flag IS
6326 SELECT concurrence_flag
6327 FROM xnp_sv_soa soa
6328 WHERE soa.object_reference = p_porting_id;
6329
6330 BEGIN
6331 x_error_code := 0;
6332 x_concurrence_flag := 'Y'; -- intialized value
6333
6334 -- Get the concurrence_flag corresponding to
6335 -- this porting_id
6336
6337 OPEN c_concurrence_flag;
6338 FETCH c_concurrence_flag INTO x_concurrence_flag;
6339
6340 IF c_concurrence_flag%NOTFOUND THEN
6341 raise NO_DATA_FOUND;
6342 END IF;
6343
6344 CLOSE c_concurrence_flag;
6345
6346 EXCEPTION
6347 WHEN NO_DATA_FOUND THEN
6348 x_error_code := SQLCODE;
6349 fnd_message.set_name('XNP','STD_ERROR');
6350 fnd_message.set_token('ERROR_LOCN'
6351 ,'xnp_core.soa_get_concurrence_flag');
6352 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6353 x_error_message := fnd_message.get;
6354
6355 fnd_message.set_name('XNP','UPD_FOR_PORTING_ID_ERR');
6356 fnd_message.set_token('ERROR_TEXT',x_error_message);
6357 fnd_message.set_token('PORTING_ID',p_porting_id);
6358 x_error_message := fnd_message.get;
6359
6360 IF c_concurrence_flag%ISOPEN THEN
6361 CLOSE c_concurrence_flag;
6362 END IF;
6363
6364 WHEN OTHERS THEN
6365 x_error_code := SQLCODE;
6366
6367 fnd_message.set_name('XNP','STD_ERROR');
6368 fnd_message.set_token('ERROR_LOCN','xnp_core.soa_get_concurrence_flag');
6369 fnd_message.set_token('ERROR_TEXT',SQLERRM);
6370 x_error_message := fnd_message.get;
6371
6372 IF c_concurrence_flag%ISOPEN THEN
6373 CLOSE c_concurrence_flag;
6374 END IF;
6375
6376 END SOA_GET_CONCURRENCE_FLAG;
6377
6378 BEGIN
6379 BEGIN
6380
6381 FND_PROFILE.GET(name => 'ENABLE_NRC',
6382 val => g_enable_nrc_flag);
6383
6384 FND_PROFILE.GET(name => 'DEFAULT_PORTING_STATUS',
6385 val => g_default_porting_status);
6386
6387 END;
6388
6389 END XNP_CORE;