[Home] [Help]
PACKAGE BODY: APPS.XNP_STANDARD
Source
1 PACKAGE BODY XNP_STANDARD AS
2 /* $Header: XNPSTACB.pls 120.1.12010000.2 2008/09/25 05:24:11 mpathani ship $ */
3
4 ------------------------------------------------------------------
5 -- Wrapper function for XDP_ENGINE package
6 -- Catches execptions incase of undefined
7 -- values.
8 -- When exception encountered logs it in
9 -- the wf_core.context information
10 ------------------------------------------------------------------
11 FUNCTION GET_MANDATORY_WI_PARAM_VALUE
12 (p_WORKITEM_INSTANCE_ID NUMBER
13 ,p_PARAMETER_NAME VARCHAR2
14 )
15 RETURN VARCHAR2
16 IS
17
18 l_VALUE VARCHAR2(4000) := NULL;
19
20 BEGIN
21
22 l_VALUE :=
23 XDP_ENGINE.GET_WORKITEM_PARAM_VALUE
24 (p_WORKITEM_INSTANCE_ID
25 ,p_PARAMETER_NAME
26 );
27
28 if (l_value IS NULL) then
29 raise NO_DATA_FOUND;
30 end if;
31
32 RETURN l_VALUE;
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 XNP_UTILS.NOTIFY_ERROR
37 (P_PKG_NAME => 'XNP_STANDARD'
38 ,P_PROC_NAME => 'GET_MANDATORY_WI_PARAM_VALUE'
39 ,P_MSG_NAME => 'GET_MANDATORY_WI_PARAM_ERR'
40 ,P_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
41 ,P_TOK1 => 'WIID'
42 ,P_VAL1 => to_char(p_WORKITEM_INSTANCE_ID)
43 ,P_TOK2 => 'PARAM_NAME'
44 ,P_VAL2 => p_PARAMETER_NAME
45 ,P_TOK3 => 'ERRTEXT'
46 ,P_VAL3 => SQLERRM
47 );
48
49 raise;
50
51 END GET_MANDATORY_WI_PARAM_VALUE;
52
53 ------------------------------------------------------------------
54 -- Wrapper function for XDP_ENGINE package
55 -- Catches execptions incase of undefined
56 -- values. A workflow notification is sent
57 -- in case of exceptions
58 ------------------------------------------------------------------
59 PROCEDURE SET_MANDATORY_WI_PARAM_VALUE
60 (p_WORKITEM_INSTANCE_ID NUMBER
61 ,p_PARAMETER_NAME VARCHAR2
62 ,p_PARAMETER_VALUE VARCHAR2
63 ,p_PARAMETER_REFERENCE_VALUE IN VARCHAR2 DEFAULT NULL
64 )
65 IS
66 BEGIN
67
68 XDP_ENGINE.SET_WORKITEM_PARAM_VALUE
69 (p_WORKITEM_INSTANCE_ID
70 ,p_PARAMETER_NAME
71 ,p_PARAMETER_VALUE
72 ,p_PARAMETER_REFERENCE_VALUE
73 );
74
75 EXCEPTION
76 WHEN OTHERS THEN
77
78 XNP_UTILS.NOTIFY_ERROR
79 (P_PKG_NAME => 'XNP_STANDARD'
80 ,P_PROC_NAME => 'SET_MANDATORY_WI_PARAM_VALUE'
81 ,P_MSG_NAME => 'SET_MANDATORY_WI_PARAM_ERR'
82 ,P_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
83 ,P_TOK1 => 'WIID'
84 ,P_VAL1 => to_char(p_WORKITEM_INSTANCE_ID)
85 ,P_TOK2 => 'PARAM_NAME'
86 ,P_VAL2 => p_PARAMETER_NAME
87 ,P_TOK3 => 'ERRTEXT'
88 ,P_VAL3 => SQLERRM
89 );
90
91 RAISE;
92
93 END SET_MANDATORY_WI_PARAM_VALUE;
94
95 ------------------------------------------------------------------
96 -- Wrapper function for XDP_ENGINE package
97 -- Catches execptions incase of undefined
98 -- values
99 ------------------------------------------------------------------
100 PROCEDURE SET_WORKITEM_PARAM_VALUE
101 (p_WORKITEM_INSTANCE_ID NUMBER
102 ,p_PARAMETER_NAME VARCHAR2
103 ,p_PARAMETER_VALUE VARCHAR2
104 ,p_PARAMETER_REFERENCE_VALUE IN VARCHAR2 DEFAULT NULL
105 )
106 IS
107 BEGIN
108
109 XDP_ENGINE.SET_WORKITEM_PARAM_VALUE
110 (p_WORKITEM_INSTANCE_ID
111 ,p_PARAMETER_NAME
112 ,p_PARAMETER_VALUE
113 ,p_PARAMETER_REFERENCE_VALUE
114 );
115
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 null;
119 WHEN OTHERS THEN
120 null;
121
122 END SET_WORKITEM_PARAM_VALUE;
123
124 ------------------------------------------------------------------
125 -- Wrapper function for XDP_ENGINE package
126 -- Catches execptions incase of undefined
127 -- values. Ignores NO_DATA_FOUND errors
128 ------------------------------------------------------------------
129 FUNCTION GET_WORKITEM_PARAM_VALUE
130 (p_WORKITEM_INSTANCE_ID NUMBER
131 ,p_PARAMETER_NAME VARCHAR2
132 )
133 RETURN VARCHAR2
134 IS
135 l_VALUE VARCHAR2(4000) := NULL;
136 BEGIN
137
138 l_VALUE :=
139 XDP_ENGINE.GET_WORKITEM_PARAM_VALUE
140 (p_WORKITEM_INSTANCE_ID
141 ,p_PARAMETER_NAME
142 );
143
144 RETURN l_VALUE;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 RETURN NULL;
149 WHEN OTHERS THEN
150 RETURN NULL;
151
152 END GET_WORKITEM_PARAM_VALUE;
153
154
155 --------------------------------------------------------------------
156 --
157 -- Called when: there is a Create Ported Number
158 -- request from NRC
159 -- Called by: XNP_STANDARD.SMS_CREATE_PORTED_NUMBER
160 -- Description: Extracts the order information from SFM
161 -- Workitem params
162 -- Mandatory: Gets the PORTING_ID, STARTING_NUMBER, ENDING_NUMBER,
163 -- PORTING_TIME,ROUTING_NUMBER
164 -- Optional: CNAM_ADDRESS, CNAM_SUBSYSTEM,
165 -- ISVM_ADDRESS, ISVM_SUBSYSTEM, LIDB_ADDRESS, LIDB_SUBSYSTEM,
166 -- CLASS_ADDRESS, CLASS_SUBSYSTEM, WSMSC_ADDRESS, WSMSC_SUBSYSTEM,
167 -- RN_ADDRESS, RN_SUBSYSTEM, SUBSCRIPTION_TYPE
168 -- Creates an entry in SMS table for each TN in the range
169 ------------------------------------------------------------------
170 PROCEDURE SMS_CREATE_PORTED_NUMBER
171 (
172 p_ORDER_ID IN NUMBER,
173 p_LINEITEM_ID IN NUMBER,
174 p_WORKITEM_INSTANCE_ID IN NUMBER,
175 p_FA_INSTANCE_ID IN NUMBER,
176 x_ERROR_CODE OUT NOCOPY NUMBER,
177 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
178 )
179 IS
180 l_sv_id NUMBER;
181 l_porting_id VARCHAR2(80) := null;
182 l_routing_number VARCHAR2(40);
183 l_starting_number VARCHAR2(20);
184 l_porting_time VARCHAR2(40);
185 l_ending_number VARCHAR2(20);
186 l_counter BINARY_INTEGER;
187 l_index BINARY_INTEGER := 0;
188 l_NRC_ID NUMBER ;
189 l_GEO_ID NUMBER ;
190 l_ROUTING_NUMBER_ID NUMBER;
191
192 l_SUBSCRIPTION_TYPE VARCHAR2(80) := NULL;
193 l_CNAM_ADDRESS VARCHAR2(80) := NULL;
194 l_CNAM_SUBSYSTEM VARCHAR2(80) := NULL;
195 l_ISVM_ADDRESS VARCHAR2(80) := NULL;
196 l_ISVM_SUBSYSTEM VARCHAR2(80) := NULL;
197 l_LIDB_ADDRESS VARCHAR2(80) := NULL;
198 l_LIDB_SUBSYSTEM VARCHAR2(80) := NULL;
199 l_CLASS_ADDRESS VARCHAR2(80) := NULL;
200 l_CLASS_SUBSYSTEM VARCHAR2(80) := NULL;
201 l_WSMSC_ADDRESS VARCHAR2(80) := NULL;
202 l_WSMSC_SUBSYSTEM VARCHAR2(80) := NULL;
203 l_RN_ADDRESS VARCHAR2(80) := NULL;
204 l_RN_SUBSYSTEM VARCHAR2(80) := NULL;
205 l_NUMBER_RANGE_ID NUMBER := NULL;
206
207 BEGIN
208 x_error_code := 0;
209
210 l_porting_id :=
211 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
212 (p_WORKITEM_INSTANCE_ID
213 ,'PORTING_ID'
214 );
215
216 l_porting_time :=
217 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
218 (p_WORKITEM_INSTANCE_ID
219 ,'PORTING_TIME'
220 );
221
222 l_starting_number :=
223 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
224 (p_WORKITEM_INSTANCE_ID
225 ,'STARTING_NUMBER'
226 );
227
228 l_ending_number :=
229 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
230 (p_WORKITEM_INSTANCE_ID
231 ,'ENDING_NUMBER'
232 );
233
234 -- verify if its a valid number range
235 xnp_core.get_number_range_id( p_starting_number => l_starting_number,
236 p_ending_number => l_ending_number,
237 x_number_range_id => l_number_range_id,
238 x_error_code => x_error_code,
239 x_error_message => x_error_message
240 );
241 IF (x_error_code <> 0) THEN
242 return;
243 END IF;
244
245 l_routing_number :=
246 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
247 (p_WORKITEM_INSTANCE_ID
248 ,'ROUTING_NUMBER'
249 );
250
251 -- Get the routing_number_id corresponding to the code
252
253 XNP_CORE.GET_ROUTING_NUMBER_ID
254 (l_ROUTING_NUMBER
255 ,l_ROUTING_NUMBER_ID
256 ,x_ERROR_CODE
257 ,x_ERROR_MESSAGE
258 );
259 IF x_ERROR_CODE <> 0 THEN
260 RETURN;
261 END IF;
262
263 l_SUBSCRIPTION_TYPE :=
264 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
265 (p_WORKITEM_INSTANCE_ID
266 ,'SUBSCRIPTION_TYPE'
267 );
268 IF (l_SUBSCRIPTION_TYPE IS null) THEN
269 l_SUBSCRIPTION_TYPE := 'NP';
270 END IF;
271
272 l_CNAM_ADDRESS :=
273 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
274 (p_WORKITEM_INSTANCE_ID
275 ,'CNAM_ADDRESS'
276 );
277
278 l_CNAM_SUBSYSTEM :=
279 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
280 (p_WORKITEM_INSTANCE_ID
281 ,'CNAM_SUBSYSTEM'
282 );
283
284 l_ISVM_ADDRESS :=
285 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
286 (p_WORKITEM_INSTANCE_ID
287 ,'ISVM_ADDRESS'
288 );
289
290 l_ISVM_SUBSYSTEM :=
291 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
292 (p_WORKITEM_INSTANCE_ID
293 ,'ISVM_SUBSYSTEM'
294 );
295
296 l_LIDB_ADDRESS :=
297 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
298 (p_WORKITEM_INSTANCE_ID
299 ,'LIDB_ADDRESS'
300 );
301
302 l_LIDB_SUBSYSTEM :=
303 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
304 (p_WORKITEM_INSTANCE_ID
305 ,'LIDB_SUBSYSTEM'
306 );
307
308 l_CLASS_ADDRESS :=
309 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
310 (p_WORKITEM_INSTANCE_ID
311 ,'CLASS_ADDRESS'
312 );
313
314 l_CLASS_SUBSYSTEM :=
315 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
316 (p_WORKITEM_INSTANCE_ID
317 ,'CLASS_SUBSYSTEM'
318 );
319
320 l_WSMSC_ADDRESS :=
321 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
322 (p_WORKITEM_INSTANCE_ID
323 ,'WSMSC_ADDRESS'
324 );
325
326 l_WSMSC_SUBSYSTEM :=
327 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
328 (p_WORKITEM_INSTANCE_ID
329 ,'WSMSC_SUBSYSTEM'
330 );
331
332 l_RN_ADDRESS :=
333 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
334 (p_WORKITEM_INSTANCE_ID
335 ,'RN_ADDRESS'
336 );
337
338 l_RN_SUBSYSTEM :=
339 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
340 (p_WORKITEM_INSTANCE_ID
341 ,'RN_SUBSYSTEM'
342 );
343
344 XNP_CORE.SMS_CREATE_PORTED_NUMBER
345 (p_PORTING_ID =>l_porting_id
346 ,p_STARTING_NUMBER =>to_number(l_STARTING_NUMBER)
347 ,p_ENDING_NUMBER =>to_number(l_ENDING_NUMBER)
348 ,p_SUBSCRIPTION_TYPE =>l_SUBSCRIPTION_TYPE
349 ,p_ROUTING_NUMBER_ID =>l_ROUTING_NUMBER_ID
350 ,p_PORTING_TIME =>XNP_UTILS.CANONICAL_TO_DATE(l_porting_time)
351 ,p_CNAM_ADDRESS =>l_CNAM_ADDRESS
352 ,p_CNAM_SUBSYSTEM =>l_CNAM_SUBSYSTEM
353 ,p_ISVM_ADDRESS =>l_ISVM_ADDRESS
354 ,p_ISVM_SUBSYSTEM =>l_ISVM_SUBSYSTEM
355 ,p_LIDB_ADDRESS =>l_LIDB_ADDRESS
356 ,p_LIDB_SUBSYSTEM =>l_LIDB_SUBSYSTEM
357 ,p_CLASS_ADDRESS =>l_CLASS_ADDRESS
358 ,p_CLASS_SUBSYSTEM =>l_CLASS_SUBSYSTEM
359 ,p_WSMSC_ADDRESS =>l_WSMSC_ADDRESS
360 ,p_WSMSC_SUBSYSTEM =>l_WSMSC_SUBSYSTEM
361 ,p_RN_ADDRESS =>l_RN_ADDRESS
362 ,p_RN_SUBSYSTEM =>l_RN_SUBSYSTEM
363 ,p_ORDER_ID =>P_ORDER_ID
364 ,p_LINEITEM_ID =>p_LINEITEM_ID
365 ,p_WORKITEM_INSTANCE_ID =>p_WORKITEM_INSTANCE_ID
366 ,p_FA_INSTANCE_ID =>p_FA_INSTANCE_ID
367 ,x_ERROR_CODE =>x_ERROR_CODE
368 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
369 );
370 EXCEPTION
371 WHEN OTHERS THEN
372 -- Grab the error message and error no.
373 x_error_code := SQLCODE;
374
375 fnd_message.set_name('XNP','STD_ERROR');
376 fnd_message.set_token('ERROR_LOCN'
377 ,'XNP_STANDARD.SMS_CREATE_PORTED_NUMBER');
378 fnd_message.set_token('ERROR_TEXT',SQLERRM);
379 x_error_message := fnd_message.get;
380 END SMS_CREATE_PORTED_NUMBER;
381
382
383 --------------------------------------------------------------------
384 --
385 -- Called when: there is a Delete Ported Number
386 -- request from NRC
387 -- Called by: XNP_STANDARD.SMS_DELETE_PORTED_NUMBER
388 -- Description: Extracts the order information from SFM
389 -- Workitem params table
390 -- Gets the STARTING_NUMBER, ENDING_NUMBER
391 -- and calls XNP_CORE.SMS_DELETE_PORTED_NUMBER
392 --
393 ------------------------------------------------------------------
394 PROCEDURE SMS_DELETE_PORTED_NUMBER
395 (p_WORKITEM_INSTANCE_ID IN NUMBER
396 ,x_ERROR_CODE OUT NOCOPY NUMBER
397 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
398 )
399 IS
400 l_starting_number VARCHAR2(20);
401 l_ending_number VARCHAR2(20);
402
403 BEGIN
404
405 x_error_code := 0;
406
407 l_starting_number :=
408 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
409 (p_WORKITEM_INSTANCE_ID
410 ,'STARTING_NUMBER'
411 );
412
413 l_ending_number :=
414 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
415 (p_WORKITEM_INSTANCE_ID
416 ,'ENDING_NUMBER'
417 );
418
419 XNP_CORE.SMS_DELETE_PORTED_NUMBER
420 (p_STARTING_NUMBER=>l_starting_number
421 ,p_ENDING_NUMBER=>l_ending_number
422 ,x_ERROR_CODE=>x_ERROR_CODE
423 ,x_ERROR_MESSAGE=>x_ERROR_MESSAGE
424 );
425
426 END SMS_DELETE_PORTED_NUMBER;
427
428 --------------------------------------------------------------------
429 --
430 -- Called when: there is a Pre-order Peer's Porting
431 -- Inquiry
432 -- Called by: XNP_STANDARD.SOA_PORTING_INQUIRY
433 -- Description:
434 -- Inserts a row in the the SOA table based on the
435 -- SP type (i.e. donor or recipient)
436 -- All necessary values are got from the workitems table
437 -- SV Status: PREORDER_PENDING
438 ------------------------------------------------------------------
439 /********* commented this procedure as this procedure is not found to be called any where in the code
440 PROCEDURE SOA_PORTING_INQUIRY
441 (
442 p_WORKITEM_INSTANCE_ID IN NUMBER
443 ,x_ERROR_CODE OUT NOCOPY NUMBER
444 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
445 )
446 IS
447 BEGIN
448 x_error_code := 0;
449 NULL;
450 END SOA_PORTING_INQUIRY;
451 ************/
452 --------------------------------------------------------------------
453 --
454 -- Called by: XNP_STANDARD.SOA_UPDATE_CUTOFF_DATE
455 -- Description: Extracts the order information from SFM
456 -- Workitem params table namely PORTING_ID, STARTING_NUMBER,
457 -- ENDING_NUMBER, OLD_SP_CUTOFF_DUE_DATE
458 -- Calls XNP_CORE.SOA_UPDATE_CUTOFF_DATE to update the
459 -- cutoff date of each TN in the range
460 ------------------------------------------------------------------
461 PROCEDURE SOA_UPDATE_CUTOFF_DATE
462 ( p_ORDER_ID IN NUMBER,
463 p_LINEITEM_ID IN NUMBER,
464 p_WORKITEM_INSTANCE_ID IN NUMBER,
465 p_FA_INSTANCE_ID IN NUMBER,
466 p_CUR_STATUS_TYPE_CODE VARCHAR2,
467 x_ERROR_CODE OUT NOCOPY NUMBER,
468 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
469 )
470 IS
471 l_porting_id VARCHAR2(40) := null;
472 l_sv_id NUMBER;
473 l_starting_number VARCHAR2(20);
474 l_ending_number VARCHAR2(20);
475 l_porting_time VARCHAR2(40);
476 l_counter BINARY_INTEGER;
477 l_index BINARY_INTEGER := 0;
478 l_SV_ID NUMBER;
479 l_LOCAL_SP_ID NUMBER := 0;
480 l_cutoff_date VARCHAR2(40);
481 l_SP_NAME VARCHAR2(40) := NULL;
482
483 BEGIN
484
485 x_error_code := 0;
486
487 l_porting_id :=
488 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
489 (p_WORKITEM_INSTANCE_ID
490 ,'PORTING_ID'
491 );
492
493 l_starting_number :=
494 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
495 (p_WORKITEM_INSTANCE_ID
496 ,'STARTING_NUMBER'
497 );
498
499 l_ending_number :=
500 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
501 (p_WORKITEM_INSTANCE_ID
502 ,'ENDING_NUMBER'
503 );
504
505 l_cutoff_date :=
506 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
507 (p_WORKITEM_INSTANCE_ID
508 ,'OLD_SP_CUTOFF_DUE_DATE'
509 );
510
511 l_SP_NAME :=
512 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
513 (p_WORKITEM_INSTANCE_ID
514 ,'SP_NAME'
515 );
516
517 IF( l_porting_id is not null) THEN
518
519 -- call updation procedure to update the status
520
521 XNP_CORE.SOA_UPDATE_CUTOFF_DATE
522 (p_porting_id => l_porting_id
523 ,p_old_sp_cutoff_due_date=> xnp_utils.canonical_to_date(l_cutoff_date)
524 ,p_local_sp_id => l_LOCAL_SP_ID
525 ,p_order_id => p_order_id
526 ,p_lineitem_id => p_lineitem_id
527 ,p_workitem_instance_id => p_workitem_instance_id
528 ,p_fa_instance_id => p_fa_instance_id
529 ,x_error_code => x_ERROR_CODE
530 ,x_error_message => x_ERROR_MESSAGE
531 );
532 ELSE
533 -- call updation procedure to update the status
534
535 XNP_CORE.SOA_UPDATE_CUTOFF_DATE
536 (p_starting_number => l_starting_number
537 ,p_ending_number => l_ending_number
538 ,p_CUR_STATUS_TYPE_CODE => p_CUR_STATUS_TYPE_CODE
539 ,p_local_sp_id => l_LOCAL_SP_ID
540 ,p_old_sp_cutoff_due_date=> xnp_utils.canonical_to_date(l_cutoff_date)
541 ,p_order_id => p_order_id
542 ,p_lineitem_id => p_lineitem_id
543 ,p_workitem_instance_id => p_workitem_instance_id
544 ,p_fa_instance_id => p_fa_instance_id
545 ,x_error_code => x_ERROR_CODE
546 ,x_error_message => x_ERROR_MESSAGE
547 );
548 END IF;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 -- Grab the error message and error no.
553 x_error_code := SQLCODE;
554
555 fnd_message.set_name('XNP','STD_ERROR');
556 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_CUTOFF_DATE');
557 fnd_message.set_token('ERROR_TEXT',SQLERRM);
558 x_error_message := fnd_message.get;
559
560 END SOA_UPDATE_CUTOFF_DATE;
561
562
563 --------------------------------------------------------------------
564 --
565 -- Called when: there is a order Peer's Porting
566 -- Order or OMS porting order
567 -- Called by: XNP_WF_STANDARD.SOA_CREATE_PORTING_ORDER
568 -- Description:
569 -- Updates corresponding rows in the the SOA table based on
570 -- SP type (i.e. donor or recipient)
571 -- All necessary values are got from the workitems table
572 -- Mandatory Parameters needed: STARTING_NUMBER, ENDING_NUMBER,
573 -- DONOR_SP_ID,RECIPIENT_SP_ID,NEW_SP_DUE_DATE,ROUTING_NUMBER
574 -- Optional Parameters : OLD_SP_CUTOFF_DUE_DATE,CUSTOMER_ID,
575 -- CUSTOMER_NAME,CUSTOMER_TYPE,ADDRESS_LINE1,ADDRESS_LINE2,CITY,PHONE,FAX,EMAIL,ZIP_CODE,COUNTRY,
576 -- RETAIN_TN_FLAG,CUSTOMER_CONTACT_REQ_FLAG,ORDER_PRIORITY,
577 -- RETAIN_DIR_INFO_FLAG,CONTACT_NAME, CNAM_ADDRESS,
578 -- CNAM_SUBSYSTEM, ISVM_ADDRESS, ISVM_SUBSYSTEM, LIDB_ADDRESS,
579 -- LIDB_SUBSYSTEM, CLASS_ADDRESS, CLASS_SUBSYSTEM, WSMSC_ADDRESS,
580 -- WSMSC_SUBSYSTEM, RN_ADDRESS, RN_SUBSYSTEM, PAGER, PAGER_PIN,
581 -- INTERNET_ADDRESS, PREORDER_AUTHORIZATION_CODE, ACTIVATION_DUE_DATE,
582 -- SUBSCRIPTION_TYPE,COMMENTS,NOTES
583 -- SV Status: Status with initial flag as true
584 ------------------------------------------------------------------
585 PROCEDURE SOA_CREATE_PORTING_ORDER
586 ( p_ORDER_ID IN NUMBER,
587 p_LINEITEM_ID IN NUMBER,
588 p_WORKITEM_INSTANCE_ID IN NUMBER,
589 p_FA_INSTANCE_ID IN NUMBER,
590 p_SP_ROLE VARCHAR2,
591 x_ERROR_CODE OUT NOCOPY NUMBER,
592 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
593 )
594 IS
595 l_PORTING_ID VARCHAR2(40) := null;
596 l_STARTING_NUMBER VARCHAR2(40);
597 l_ENDING_NUMBER VARCHAR2(40);
598 l_DONOR_SP_ID NUMBER := 0;
599 l_RECIPIENT_SP_ID NUMBER := 0;
600 l_ROUTING_NUMBER_ID NUMBER := 0;
601 l_ROUTING_NUMBER VARCHAR2(40) := null;
602 l_SP_DUE_DATE VARCHAR2(40);
603 l_OLD_SP_CUTOFF_DUE_DATE VARCHAR2(40);
604 l_CUSTOMER_ID VARCHAR2(30);
605 l_CUSTOMER_NAME VARCHAR2(80);
606 l_CUSTOMER_TYPE VARCHAR2(10);
607 l_ADDRESS_LINE1 VARCHAR2(400);
608 l_ADDRESS_LINE2 VARCHAR2(400);
609 l_CITY VARCHAR2(40);
610 l_PHONE VARCHAR2(40);
611 l_FAX VARCHAR2(40);
612 l_EMAIL VARCHAR2(40);
613 l_ZIP_CODE VARCHAR2(40);
614 l_COUNTRY VARCHAR2(40);
615 l_NEW_SP_DUE_DATE VARCHAR2(40);
616 l_OLD_SP_DUE_DATE VARCHAR2(40);
617 l_COMMENTS VARCHAR2(2000);
618 l_NOTES VARCHAR2(2000);
619 l_SUBSCRIPTION_TYPE VARCHAR2(80) := 'NP';
620 l_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2(3);
621 l_RETAIN_TN_FLAG VARCHAR2(3);
622 l_RETAIN_DIR_INFO_FLAG VARCHAR2(3);
623 l_RETAIN_TN VARCHAR2(5);
624 l_RETAIN_DIR_INFO VARCHAR2(5);
625 l_DONOR_SP_CODE VARCHAR2(80);
626 l_RECIPIENT_SP_CODE VARCHAR2(80);
627 l_CONTACT_REQUESTED VARCHAR2(5);
628 l_counter BINARY_INTEGER;
629 l_index BINARY_INTEGER := 0;
630 l_CONTACT_NAME VARCHAR2(40) := NULL;
631 l_CNAM_ADDRESS VARCHAR2(80) := NULL;
632 l_CNAM_SUBSYSTEM VARCHAR2(80) := NULL;
633 l_ISVM_ADDRESS VARCHAR2(80) := NULL;
634 l_ISVM_SUBSYSTEM VARCHAR2(80) := NULL;
635 l_LIDB_ADDRESS VARCHAR2(80) := NULL;
636 l_LIDB_SUBSYSTEM VARCHAR2(80) := NULL;
637 l_CLASS_ADDRESS VARCHAR2(80) := NULL;
638 l_CLASS_SUBSYSTEM VARCHAR2(80) := NULL;
639 l_WSMSC_ADDRESS VARCHAR2(80) := NULL;
640 l_WSMSC_SUBSYSTEM VARCHAR2(80) := NULL;
641 l_RN_ADDRESS VARCHAR2(80) := NULL;
642 l_RN_SUBSYSTEM VARCHAR2(80) := NULL;
643
644 l_PAGER VARCHAR2(20) := NULL;
645 l_PAGER_PIN VARCHAR2(80) := NULL;
646 l_INTERNET_ADDRESS VARCHAR2(40) := NULL;
647 l_PREORDER_AUTHORIZATION_CODE VARCHAR2(20) := NULL;
648 l_ACTIVATION_DUE_DATE VARCHAR2(40) := NULL;
649 l_ORDER_PRIORITY VARCHAR2(30) := NULL;
650 l_SUBSEQUENT_PORT_FLAG VARCHAR2(1) := 'N';
651 -- l_NUMBER_RANGE_ID NUMBER := NULL;
652 l_VALIDATION_FLAG VARCHAR2(1):= 'Y';
653
654 BEGIN
655 x_error_code := 0;
656
657 l_PORTING_ID :=
658 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
659 (p_WORKITEM_INSTANCE_ID
660 ,'PORTING_ID'
661 );
662
663 l_starting_number :=
664 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
665 (p_WORKITEM_INSTANCE_ID
666 ,'STARTING_NUMBER'
667 );
668
669 l_ending_number :=
670 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
671 (p_WORKITEM_INSTANCE_ID
672 ,'ENDING_NUMBER'
673 );
674
675 /**** Moved this code to validate the number range to individual
676 create_port_order APIs in XNP_CORE package on 04/11/2001 -- spusegao
677 in order to accomodate NRC_NOVALIDATION SP Role
678
679 -- verify if its a valid number range
680
681 xnp_core.get_number_range_id( p_starting_number => l_starting_number,
682 p_ending_number => l_ending_number,
683 x_number_range_id => l_number_range_id,
684 x_error_code => x_error_code,
685 x_error_message => x_error_message
686 );
687 IF (x_error_code <> 0) THEN
688 return;
689 END IF;
690 ******/
691
692 l_DONOR_SP_CODE :=
693 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
694 (p_WORKITEM_INSTANCE_ID
695 ,'DONOR_SP_ID'
696 );
697
698 -- Get the SP id for this SP code
699 XNP_CORE.GET_SP_ID
700 (l_DONOR_SP_CODE
701 ,l_DONOR_SP_ID
702 ,x_ERROR_CODE
703 ,x_ERROR_MESSAGE
704 );
705
706 IF x_ERROR_CODE <> 0 THEN
707 RETURN;
708 END IF;
709
710 l_RECIPIENT_SP_CODE :=
711 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
712 (p_WORKITEM_INSTANCE_ID
713 ,'RECIPIENT_SP_ID'
714 );
715
716 -- Get the SP id for this recipient code
717 XNP_CORE.GET_SP_ID
718 (l_RECIPIENT_SP_CODE
719 ,l_RECIPIENT_SP_ID
720 ,x_ERROR_CODE
721 ,x_ERROR_MESSAGE
722 );
723
724 IF x_ERROR_CODE <> 0 THEN
725 RETURN;
726 END IF;
727
728 -- The new sp due date is part of order incase of recipient
729 -- The new sp due date is part of inbound mesg incase of donor
730
731 l_new_sp_due_date :=
732 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
733 (p_WORKITEM_INSTANCE_ID
734 ,'NEW_SP_DUE_DATE'
735 );
736 l_SP_DUE_DATE := l_new_sp_due_date;
737
738
739 l_OLD_SP_CUTOFF_DUE_DATE :=
740 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
741 (p_WORKITEM_INSTANCE_ID
742 ,'OLD_SP_CUTOFF_DUE_DATE'
743 );
744
745 l_CUSTOMER_ID :=
746 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
747 (p_WORKITEM_INSTANCE_ID
748 ,'CUSTOMER_ID'
749 );
750
751 l_CUSTOMER_NAME :=
752 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
753 (p_WORKITEM_INSTANCE_ID
754 ,'CUSTOMER_NAME'
755 );
756
757 l_CUSTOMER_TYPE :=
758 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
759 (p_WORKITEM_INSTANCE_ID
760 ,'CUSTOMER_TYPE'
761 );
762
763 l_ADDRESS_LINE1 :=
764 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
765 (p_WORKITEM_INSTANCE_ID
766 ,'ADDRESS_LINE1'
767 );
768
769 l_ADDRESS_LINE2 :=
770 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
771 (p_WORKITEM_INSTANCE_ID
772 ,'ADDRESS_LINE2'
773 );
774
775 l_CITY :=
776 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
777 (p_WORKITEM_INSTANCE_ID
778 ,'CITY'
779 );
780
781 l_PHONE :=
782 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
783 (p_WORKITEM_INSTANCE_ID
784 ,'PHONE'
785 );
786
787 l_FAX :=
788 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
789 (p_WORKITEM_INSTANCE_ID
790 ,'FAX'
791 );
792
793 l_EMAIL :=
794 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
795 (p_WORKITEM_INSTANCE_ID
796 ,'EMAIL'
797 );
798
799 l_ZIP_CODE :=
800 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
801 (p_WORKITEM_INSTANCE_ID
802 ,'ZIP_CODE'
803 );
804
805 l_COUNTRY :=
806 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
807 (p_WORKITEM_INSTANCE_ID
808 ,'COUNTRY'
809 );
810
811 l_RETAIN_TN_FLAG :=
812 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
813 (p_WORKITEM_INSTANCE_ID
814 ,'RETAIN_TN_FLAG'
815 );
816
817 l_CUSTOMER_CONTACT_REQ_FLAG :=
818 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
819 (p_WORKITEM_INSTANCE_ID
820 ,'CUSTOMER_CONTACT_REQ_FLAG'
821 );
822
823 l_CONTACT_NAME :=
824 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
825 (p_WORKITEM_INSTANCE_ID
826 ,'CONTACT_NAME'
827 );
828
829 l_CNAM_ADDRESS :=
830 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
831 (p_WORKITEM_INSTANCE_ID
832 ,'CNAM_ADDRESS'
833 );
834
835 l_CNAM_SUBSYSTEM :=
836 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
837 (p_WORKITEM_INSTANCE_ID
838 ,'CNAM_SUBSYSTEM'
839 );
840
841 l_ISVM_ADDRESS :=
842 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
843 (p_WORKITEM_INSTANCE_ID
844 ,'ISVM_ADDRESS'
845 );
846
847 l_ISVM_SUBSYSTEM :=
848 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
849 (p_WORKITEM_INSTANCE_ID
850 ,'ISVM_SUBSYSTEM'
851 );
852
853 l_LIDB_ADDRESS :=
854 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
855 (p_WORKITEM_INSTANCE_ID
856 ,'LIDB_ADDRESS'
857 );
858
859 l_LIDB_SUBSYSTEM :=
860 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
861 (p_WORKITEM_INSTANCE_ID
862 ,'LIDB_SUBSYSTEM'
863 );
864
865 l_CLASS_ADDRESS :=
866 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
867 (p_WORKITEM_INSTANCE_ID
868 ,'CLASS_ADDRESS'
869 );
870
871 l_CLASS_SUBSYSTEM :=
872 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
873 (p_WORKITEM_INSTANCE_ID
874 ,'CLASS_SUBSYSTEM'
875 );
876
877 l_WSMSC_ADDRESS :=
878 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
879 (p_WORKITEM_INSTANCE_ID
880 ,'WSMSC_ADDRESS'
881 );
882
883 l_WSMSC_SUBSYSTEM :=
884 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
885 (p_WORKITEM_INSTANCE_ID
886 ,'WSMSC_SUBSYSTEM'
887 );
888
889 l_RN_ADDRESS :=
890 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
891 (p_WORKITEM_INSTANCE_ID
892 ,'RN_ADDRESS'
893 );
894
895 l_RN_SUBSYSTEM :=
896 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
897 (p_WORKITEM_INSTANCE_ID
898 ,'RN_SUBSYSTEM'
899 );
900
901 l_PAGER :=
902 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
903 (p_WORKITEM_INSTANCE_ID
904 ,'PAGER'
905 );
906
907 l_PAGER_PIN :=
908 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
909 (p_WORKITEM_INSTANCE_ID
910 ,'PAGER_PIN'
911 );
912
913 l_INTERNET_ADDRESS :=
914 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
915 (p_WORKITEM_INSTANCE_ID
916 ,'INTERNET_ADDRESS'
917 );
918
919 l_RETAIN_DIR_INFO_FLAG :=
920 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
921 (p_WORKITEM_INSTANCE_ID
922 ,'RETAIN_DIR_INFO_FLAG'
923 );
924
925 l_PREORDER_AUTHORIZATION_CODE :=
926 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
927 (p_WORKITEM_INSTANCE_ID
928 ,'PREORDER_AUTHORIZATION_CODE'
929 );
930
931 l_ACTIVATION_DUE_DATE :=
932 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
933 (p_WORKITEM_INSTANCE_ID
934 ,'ACTIVATION_DUE_DATE'
935 );
936
937 l_ORDER_PRIORITY :=
938 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
939 (p_WORKITEM_INSTANCE_ID
940 ,'ORDER_PRIORITY'
941 );
942
943 l_COMMENTS :=
944 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
945 (p_WORKITEM_INSTANCE_ID
946 ,'COMMENTS'
947 );
948
949 l_NOTES :=
950 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
951 (p_WORKITEM_INSTANCE_ID
952 ,'NOTES'
953 );
954
955 l_SUBSCRIPTION_TYPE :=
956 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
957 (p_WORKITEM_INSTANCE_ID
958 ,'SUBSCRIPTION_TYPE'
959 );
960 IF (l_SUBSCRIPTION_TYPE IS null) THEN
961 l_SUBSCRIPTION_TYPE := 'NP';
962 END IF;
963
964 ------------------------------------------------------------------
965 -- Create an SV in the SOA table for each TN
966 -- The due dates should be entered based on self's identity
967 -- i.e. for recipient, enter NEW_SP_DUE_DATE and
968 -- donor enter OLD...
969 ------------------------------------------------------------------
970
971 -- Get the routing number id
972
973 l_ROUTING_NUMBER :=
974 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
975 (p_WORKITEM_INSTANCE_ID
976 ,'ROUTING_NUMBER'
977 );
978
979
980 -- Bug 2239283. Set the Subsequent Porting Flag regardless of the
981 -- SP Role
982
983 l_SUBSEQUENT_PORT_FLAG :=
984 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
985 (p_WORKITEM_INSTANCE_ID
986 ,'SUBSEQUENT_PORT'
987 );
988
989 IF (l_SUBSEQUENT_PORT_FLAG = NULL) THEN
990 l_SUBSEQUENT_PORT_FLAG := 'N';
991 END IF;
992
993 /****
994 Moved this validation call to individual create_porting_order
995 APIs in XNPCORE package to accomodate NRC_NOVALIDATION
996 on 04/11/2001 --spusegao
997
998 -- Get the routing_number_id corresponding to the code
999
1000 IF (l_ROUTING_NUMBER IS NOT NULL) THEN
1001
1002 XNP_CORE.GET_ROUTING_NUMBER_ID
1003 (l_routing_number
1004 ,l_ROUTING_NUMBER_ID
1005 ,x_ERROR_CODE
1006 ,x_ERROR_MESSAGE
1007 );
1008
1009 IF x_ERROR_CODE <> 0 THEN
1010 RETURN;
1011 END IF;
1012
1013 END IF;
1014 ****/
1015
1016 IF (p_SP_ROLE = 'RECIPIENT')
1017 OR (p_SP_ROLE = 'NRC')
1018 OR (p_SP_ROLE = 'NRC_WITHOUT_VALIDATION') THEN
1019
1020 --{ case of recipient or nrc
1021 --
1022 -- Bug 2239283. Set the Subsequent Porting Flag regardless of the
1023 -- SP Role. Moved the code above. Commented this code
1024 -- l_SUBSEQUENT_PORT_FLAG :=
1025 -- XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1026 -- (p_WORKITEM_INSTANCE_ID
1027 -- ,'SUBSEQUENT_PORT'
1028 -- );
1029
1030 -- IF (l_SUBSEQUENT_PORT_FLAG = NULL) THEN
1031 -- l_SUBSEQUENT_PORT_FLAG := 'N';
1032 -- END IF;
1033
1034 IF (p_SP_ROLE = 'RECIPIENT') THEN
1035 --{ Create porting order for the recipient SP
1036
1037 XNP_CORE.SOA_CREATE_REC_PORT_ORDER
1038 (p_PORTING_ID =>l_PORTING_ID
1039 ,p_STARTING_NUMBER =>to_number(l_STARTING_NUMBER)
1040 ,p_ENDING_NUMBER =>to_number(l_ENDING_NUMBER)
1041 ,p_SUBSCRIPTION_TYPE =>l_SUBSCRIPTION_TYPE
1042 ,p_DONOR_SP_ID =>l_DONOR_SP_ID
1043 ,p_RECIPIENT_SP_ID =>l_RECIPIENT_SP_ID
1044 ,p_ROUTING_NUMBER =>l_ROUTING_NUMBER
1045 ,p_NEW_SP_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_SP_DUE_DATE)
1046 ,p_OLD_SP_CUTOFF_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_OLD_SP_CUTOFF_DUE_DATE)
1047 ,p_CUSTOMER_ID =>l_CUSTOMER_ID
1048 ,p_CUSTOMER_NAME =>l_CUSTOMER_NAME
1049 ,p_CUSTOMER_TYPE =>l_CUSTOMER_TYPE
1050 ,p_ADDRESS_LINE1 =>l_ADDRESS_LINE1
1051 ,p_ADDRESS_LINE2 =>l_ADDRESS_LINE2
1052 ,p_CITY =>l_CITY
1053 ,p_PHONE =>l_PHONE
1054 ,p_FAX =>l_FAX
1055 ,p_EMAIL =>l_EMAIL
1056 ,p_PAGER =>l_PAGER
1057 ,p_PAGER_PIN =>l_PAGER_PIN
1058 ,p_INTERNET_ADDRESS =>l_INTERNET_ADDRESS
1059 ,p_ZIP_CODE =>l_ZIP_CODE
1060 ,p_COUNTRY =>l_COUNTRY
1061 ,p_CUSTOMER_CONTACT_REQ_FLAG =>l_CUSTOMER_CONTACT_REQ_FLAG
1062 ,p_CONTACT_NAME =>l_CONTACT_NAME
1063 ,p_RETAIN_TN_FLAG =>l_RETAIN_TN_FLAG
1064 ,p_RETAIN_DIR_INFO_FLAG =>l_RETAIN_DIR_INFO_FLAG
1065 ,p_CNAM_ADDRESS =>l_CNAM_ADDRESS
1066 ,p_CNAM_SUBSYSTEM =>l_CNAM_SUBSYSTEM
1067 ,p_ISVM_ADDRESS =>l_ISVM_ADDRESS
1068 ,p_ISVM_SUBSYSTEM =>l_ISVM_SUBSYSTEM
1069 ,p_LIDB_ADDRESS =>l_LIDB_ADDRESS
1070 ,p_LIDB_SUBSYSTEM =>l_LIDB_SUBSYSTEM
1071 ,p_CLASS_ADDRESS =>l_CLASS_ADDRESS
1072 ,p_CLASS_SUBSYSTEM =>l_CLASS_SUBSYSTEM
1073 ,p_WSMSC_ADDRESS =>l_WSMSC_ADDRESS
1074 ,p_WSMSC_SUBSYSTEM =>l_WSMSC_SUBSYSTEM
1075 ,p_RN_ADDRESS =>l_RN_ADDRESS
1076 ,p_RN_SUBSYSTEM =>l_RN_SUBSYSTEM
1077 ,p_PREORDER_AUTHORIZATION_CODE=>l_PREORDER_AUTHORIZATION_CODE
1078 ,p_ACTIVATION_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_ACTIVATION_DUE_DATE)
1079 ,p_ORDER_PRIORITY =>l_ORDER_PRIORITY
1080 ,p_SUBSEQUENT_PORT_FLAG => l_SUBSEQUENT_PORT_FLAG
1081 ,p_COMMENTS => l_COMMENTS
1082 ,p_NOTES => l_NOTES
1083 ,p_ORDER_ID => p_ORDER_ID
1084 ,p_LINEITEM_ID => p_LINEITEM_ID
1085 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
1086 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
1087 ,x_ERROR_CODE =>x_ERROR_CODE
1088 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
1089 );
1090 --} end create porting order for recipient
1091 ELSE -- NRC OR NRC_WITHOUT_VALIDATION and not a RECIPIENT
1092 --{ create porting order for NRC
1093
1094 IF p_SP_ROLE = 'NRC_WITHOUT_VALIDATION' THEN
1095 l_validation_flag := 'N';
1096 END IF;
1097
1098
1099 XNP_CORE.SOA_CREATE_NRC_PORT_ORDER
1100 (p_PORTING_ID =>l_PORTING_ID
1101 ,p_STARTING_NUMBER =>to_number(l_STARTING_NUMBER)
1102 ,p_ENDING_NUMBER =>to_number(l_ENDING_NUMBER)
1103 ,p_SUBSCRIPTION_TYPE =>l_SUBSCRIPTION_TYPE
1104 ,p_DONOR_SP_ID =>l_DONOR_SP_ID
1105 ,p_RECIPIENT_SP_ID =>l_RECIPIENT_SP_ID
1106 ,p_ROUTING_NUMBER =>l_ROUTING_NUMBER
1107 ,p_NEW_SP_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_SP_DUE_DATE)
1108 ,p_OLD_SP_CUTOFF_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_OLD_SP_CUTOFF_DUE_DATE)
1109 ,p_CUSTOMER_ID =>l_CUSTOMER_ID
1110 ,p_CUSTOMER_NAME =>l_CUSTOMER_NAME
1111 ,p_CUSTOMER_TYPE =>l_CUSTOMER_TYPE
1112 ,p_ADDRESS_LINE1 =>l_ADDRESS_LINE1
1113 ,p_ADDRESS_LINE2 =>l_ADDRESS_LINE2
1114 ,p_CITY =>l_CITY
1115 ,p_PHONE =>l_PHONE
1116 ,p_FAX =>l_FAX
1117 ,p_EMAIL =>l_EMAIL
1118 ,p_PAGER =>l_PAGER
1119 ,p_PAGER_PIN =>l_PAGER_PIN
1120 ,p_INTERNET_ADDRESS =>l_INTERNET_ADDRESS
1121 ,p_ZIP_CODE =>l_ZIP_CODE
1122 ,p_COUNTRY =>l_COUNTRY
1123 ,p_CUSTOMER_CONTACT_REQ_FLAG =>l_CUSTOMER_CONTACT_REQ_FLAG
1124 ,p_CONTACT_NAME =>l_CONTACT_NAME
1125 ,p_RETAIN_TN_FLAG =>l_RETAIN_TN_FLAG
1126 ,p_RETAIN_DIR_INFO_FLAG =>l_RETAIN_DIR_INFO_FLAG
1127 ,p_CNAM_ADDRESS =>l_CNAM_ADDRESS
1128 ,p_CNAM_SUBSYSTEM =>l_CNAM_SUBSYSTEM
1129 ,p_ISVM_ADDRESS =>l_ISVM_ADDRESS
1130 ,p_ISVM_SUBSYSTEM =>l_ISVM_SUBSYSTEM
1131 ,p_LIDB_ADDRESS =>l_LIDB_ADDRESS
1132 ,p_LIDB_SUBSYSTEM =>l_LIDB_SUBSYSTEM
1133 ,p_CLASS_ADDRESS =>l_CLASS_ADDRESS
1134 ,p_CLASS_SUBSYSTEM =>l_CLASS_SUBSYSTEM
1135 ,p_WSMSC_ADDRESS =>l_WSMSC_ADDRESS
1136 ,p_WSMSC_SUBSYSTEM =>l_WSMSC_SUBSYSTEM
1137 ,p_RN_ADDRESS =>l_RN_ADDRESS
1138 ,p_RN_SUBSYSTEM =>l_RN_SUBSYSTEM
1139 ,p_PREORDER_AUTHORIZATION_CODE=>l_PREORDER_AUTHORIZATION_CODE
1140 ,p_ACTIVATION_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_ACTIVATION_DUE_DATE)
1141 ,p_ORDER_PRIORITY =>l_ORDER_PRIORITY
1142 ,p_SUBSEQUENT_PORT_FLAG => l_SUBSEQUENT_PORT_FLAG
1143 ,p_COMMENTS => l_COMMENTS
1144 ,p_NOTES => l_NOTES
1145 ,p_ORDER_ID => p_ORDER_ID
1146 ,p_LINEITEM_ID => p_LINEITEM_ID
1147 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
1148 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
1149 ,p_VALIDATION_FLAG => l_VALIDATION_FLAG
1150 ,x_ERROR_CODE =>x_ERROR_CODE
1151 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
1152 );
1153 --} end create porting order for NRC
1154 END IF;
1155 --}
1156 ELSE --{ if DONOR
1157 -- create porting order from DONOR
1158 XNP_CORE.SOA_CREATE_DON_PORT_ORDER
1159 (p_PORTING_ID =>l_PORTING_ID
1160 ,p_STARTING_NUMBER =>to_number(l_STARTING_NUMBER)
1161 ,p_ENDING_NUMBER =>to_number(l_ENDING_NUMBER)
1162 ,p_SUBSCRIPTION_TYPE =>l_SUBSCRIPTION_TYPE
1163 ,p_DONOR_SP_ID =>l_DONOR_SP_ID
1164 ,p_RECIPIENT_SP_ID =>l_RECIPIENT_SP_ID
1165 ,p_ROUTING_NUMBER =>l_ROUTING_NUMBER
1166 ,p_NEW_SP_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_SP_DUE_DATE)
1167 ,p_OLD_SP_CUTOFF_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_OLD_SP_CUTOFF_DUE_DATE)
1168 ,p_CUSTOMER_ID =>l_CUSTOMER_ID
1169 ,p_CUSTOMER_NAME =>l_CUSTOMER_NAME
1170 ,p_CUSTOMER_TYPE =>l_CUSTOMER_TYPE
1171 ,p_ADDRESS_LINE1 =>l_ADDRESS_LINE1
1172 ,p_ADDRESS_LINE2 =>l_ADDRESS_LINE2
1173 ,p_CITY =>l_CITY
1174 ,p_PHONE =>l_PHONE
1175 ,p_FAX =>l_FAX
1176 ,p_EMAIL =>l_EMAIL
1177 ,p_PAGER =>l_PAGER
1178 ,p_PAGER_PIN =>l_PAGER_PIN
1179 ,p_INTERNET_ADDRESS =>l_INTERNET_ADDRESS
1180 ,p_ZIP_CODE =>l_ZIP_CODE
1181 ,p_COUNTRY =>l_COUNTRY
1182 ,p_CUSTOMER_CONTACT_REQ_FLAG=>l_CUSTOMER_CONTACT_REQ_FLAG
1183 ,p_CONTACT_NAME =>l_CONTACT_NAME
1184 ,p_RETAIN_TN_FLAG =>l_RETAIN_TN_FLAG
1185 ,p_RETAIN_DIR_INFO_FLAG =>l_RETAIN_DIR_INFO_FLAG
1186 ,p_CNAM_ADDRESS =>l_CNAM_ADDRESS
1187 ,p_CNAM_SUBSYSTEM =>l_CNAM_SUBSYSTEM
1188 ,p_ISVM_ADDRESS =>l_ISVM_ADDRESS
1189 ,p_ISVM_SUBSYSTEM =>l_ISVM_SUBSYSTEM
1190 ,p_LIDB_ADDRESS =>l_LIDB_ADDRESS
1191 ,p_LIDB_SUBSYSTEM =>l_LIDB_SUBSYSTEM
1192 ,p_CLASS_ADDRESS =>l_CLASS_ADDRESS
1193 ,p_CLASS_SUBSYSTEM =>l_CLASS_SUBSYSTEM
1194 ,p_WSMSC_ADDRESS =>l_WSMSC_ADDRESS
1195 ,p_WSMSC_SUBSYSTEM =>l_WSMSC_SUBSYSTEM
1196 ,p_RN_ADDRESS =>l_RN_ADDRESS
1197 ,p_RN_SUBSYSTEM =>l_RN_SUBSYSTEM
1198 ,p_PREORDER_AUTHORIZATION_CODE=>l_PREORDER_AUTHORIZATION_CODE
1199 ,p_ACTIVATION_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_ACTIVATION_DUE_DATE)
1200 ,p_ORDER_PRIORITY =>l_ORDER_PRIORITY
1201 ,p_SUBSEQUENT_PORT_FLAG => l_SUBSEQUENT_PORT_FLAG
1202 ,p_COMMENTS => l_COMMENTS
1203 ,p_NOTES => l_NOTES
1204 ,p_ORDER_ID => p_ORDER_ID
1205 ,p_LINEITEM_ID => p_LINEITEM_ID
1206 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
1207 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
1208 ,x_ERROR_CODE =>x_ERROR_CODE
1209 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
1210 );
1211 --} create porting order from DONOR
1212 END IF;
1213
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 -- Grab the error message and error no.
1217 x_error_code := SQLCODE;
1218
1219 fnd_message.set_name('XNP','STD_ERROR');
1220 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_CREATE_PORTING_ORDER');
1221 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1222 x_error_message := fnd_message.get;
1223
1224 END SOA_CREATE_PORTING_ORDER;
1225
1226 --------------------------------------------------------------------
1227 -- Called by:Donor's XNP_WF_STANDARD.SOA_CHECK_NOTIFY_DIR_SVS
1228 -- Description: Gets the WI param PORTING_ID and calls
1229 -- XNP_CORE.SOA_CHECK_NOTIFY_DIR_SVS
1230 -- Returns: 'Y' if true
1231 ------------------------------------------------------------------
1232 PROCEDURE SOA_CHECK_NOTIFY_DIR_SVS
1233 (
1234 p_WORKITEM_INSTANCE_ID IN NUMBER
1235 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
1236 ,x_ERROR_CODE OUT NOCOPY NUMBER
1237 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1238 )
1239 IS
1240 l_porting_id VARCHAR2(40) := null;
1241 l_RETAIN_DIR_INFO VARCHAR2(1);
1242 l_sp_name VARCHAR2(80) := null;
1243 l_local_sp_id NUMBER := 0;
1244
1245 BEGIN
1246 x_error_code := 0;
1247
1248 -- Get the porting id and check the corr.
1249 l_porting_id :=
1250 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1251 (p_WORKITEM_INSTANCE_ID
1252 ,'PORTING_ID'
1253 );
1254
1255
1256 l_SP_NAME :=
1257 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1258 (p_WORKITEM_INSTANCE_ID
1259 ,'SP_NAME'
1260 );
1261
1262 XNP_CORE.SOA_CHECK_NOTIFY_DIR_SVS
1263 (p_porting_id =>l_PORTING_ID
1264 ,p_local_sp_id =>l_local_sp_id
1265 ,x_check_status =>x_CHECK_STATUS
1266 ,x_error_code =>x_ERROR_CODE
1267 ,x_error_message=>x_ERROR_MESSAGE
1268 );
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 -- Grab the error message and error no.
1272 x_error_code := SQLCODE;
1273
1274 fnd_message.set_name('XNP','STD_ERROR');
1275 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_CHECK_NOTIFY_DIR_SVS');
1276 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1277 x_error_message := fnd_message.get;
1278
1279 END SOA_CHECK_NOTIFY_DIR_SVS;
1280
1281
1282 --------------------------------------------------------------------
1283 -- Called at: donor sp when need to check if its the
1284 -- initial donor
1285 -- Called by:Donor XNP_WF_STANDARD.DETERMINE_SP_ROLE
1286 -- Description:
1287 -- Extracts the (LOCAL)SP_NAME and compares it to donor,
1288 -- recipient. If either of them don't match checks
1289 -- if its INITIAL DONOR.
1290 -- The foll. WI params are referenced STARTING_NUMBER,
1291 -- ENDING_NUMBER, DONOR_SP_ID, SP_NAME, RECIPIENT_SP_ID
1292 -- Returns: DONOR, ORIG_DONOR, RECIPIENT
1293 ------------------------------------------------------------------
1294 PROCEDURE DETERMINE_SP_ROLE
1295 (p_WORKITEM_INSTANCE_ID IN NUMBER
1296 ,x_SP_ROLE OUT NOCOPY VARCHAR2
1297 ,x_ERROR_CODE OUT NOCOPY NUMBER
1298 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1299 )
1300 IS
1301 l_starting_number VARCHAR2(20);
1302 l_ending_number VARCHAR2(20);
1303 l_donor_sp_id VARCHAR2(20);
1304 l_recipient_sp_id VARCHAR2(20);
1305 l_SERVING_SP_ID NUMBER;
1306 l_CHECK_STATUS VARCHAR2(1) := 'Y';
1307 l_SP_NAME VARCHAR2(40) := NULL;
1308
1309 BEGIN
1310
1311 x_error_code := 0;
1312
1313
1314 l_starting_number :=
1315 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1316 (p_WORKITEM_INSTANCE_ID
1317 ,'STARTING_NUMBER'
1318 );
1319
1320 l_ending_number :=
1321 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1322 (p_WORKITEM_INSTANCE_ID
1323 ,'ENDING_NUMBER'
1324 );
1325
1326 l_donor_sp_id :=
1327 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1328 (p_WORKITEM_INSTANCE_ID
1329 ,'DONOR_SP_ID'
1330 );
1331
1332 l_recipient_sp_id :=
1333 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1334 (p_WORKITEM_INSTANCE_ID
1335 ,'RECIPIENT_SP_ID'
1336 );
1337
1338 l_SP_NAME :=
1339 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1340 (p_WORKITEM_INSTANCE_ID
1341 ,'SP_NAME'
1342 );
1343
1344 IF l_donor_sp_id = l_SP_NAME THEN
1345 x_SP_ROLE := 'DONOR';
1346 RETURN;
1347 ELSIF l_recipient_sp_id = l_SP_NAME THEN
1348 x_SP_ROLE := 'RECIPIENT';
1349 RETURN;
1350 ELSE
1351 XNP_CORE.SOA_CHECK_IF_INITIAL_DONOR
1352 (p_DONOR_SP_ID =>l_DONOR_SP_ID
1353 ,p_STARTING_NUMBER =>l_STARTING_NUMBER
1354 ,p_ENDING_NUMBER =>l_ENDING_NUMBER
1355 ,x_CHECK_STATUS =>l_CHECK_STATUS
1356 ,x_ERROR_CODE =>x_ERROR_CODE
1357 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
1358 );
1359 END IF;
1360
1361 IF l_CHECK_STATUS = 'Y' THEN
1362 x_SP_ROLE := 'ORIG_DONOR';
1363 ELSE
1364 x_SP_ROLE := 'OTHER';
1365 END IF;
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 -- Grab the error message and error no.
1370 x_error_code := SQLCODE;
1371
1372 fnd_message.set_name('XNP','STD_ERROR');
1373 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.DETERMINE_SP_ROLE');
1374 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1375 x_error_message := fnd_message.get;
1376
1377 END DETERMINE_SP_ROLE;
1378
1379
1380 ------------------------------------------------------------------
1381 -- Procedure used to update the status of a SV in
1382 -- XNP_SV_SOA to the given status
1383 -- Using the WORKITEM_INSTANCE_ID the starting and ending TN
1384 -- is found to derive the SV
1385 -- The foll WI params are checked STARTING_NUMBER,
1386 -- ENDING_NUMBER, PORTING_ID
1387 ------------------------------------------------------------------
1388 PROCEDURE SOA_UPDATE_SV_STATUS
1389 ( p_ORDER_ID IN NUMBER,
1390 p_LINEITEM_ID IN NUMBER,
1391 p_WORKITEM_INSTANCE_ID IN NUMBER,
1392 p_FA_INSTANCE_ID IN NUMBER,
1393 p_CUR_STATUS_TYPE_CODE VARCHAR2,
1394 p_NEW_STATUS_TYPE_CODE VARCHAR2,
1395 p_STATUS_CHANGE_CAUSE_CODE VARCHAR2,
1396 x_ERROR_CODE OUT NOCOPY NUMBER,
1397 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1398 )
1399
1400 IS
1401 l_starting_number VARCHAR2(20);
1402 l_ending_number VARCHAR2(20);
1403 l_LOCAL_SP_ID NUMBER := 0;
1404 l_SP_NAME VARCHAR2(40) := NULL;
1405 l_PHASE VARCHAR2(40) := NULL;
1406 l_NEW_PHASE_INDICATOR VARCHAR2(40) := NULL;
1407
1408 BEGIN
1409
1410 x_error_code := 0;
1411
1412 l_starting_number :=
1413 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1414 (p_WORKITEM_INSTANCE_ID
1415 ,'STARTING_NUMBER'
1416 );
1417
1418 l_ENDING_NUMBER :=
1419 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1420 (p_WORKITEM_INSTANCE_ID
1421 ,'ENDING_NUMBER'
1422 );
1423
1424 l_SP_NAME :=
1425 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1426 (p_WORKITEM_INSTANCE_ID
1427 ,'SP_NAME'
1428 );
1429
1430 -- Get the phase of new status being set
1431 XNP_CORE.GET_PHASE_FOR_STATUS
1432 (p_CUR_STATUS_TYPE_CODE =>p_NEW_STATUS_TYPE_CODE
1433 ,x_PHASE_INDICATOR =>l_NEW_PHASE_INDICATOR
1434 ,x_ERROR_CODE =>x_ERROR_CODE
1435 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
1436 );
1437 IF x_ERROR_CODE <> 0 THEN
1438 RETURN;
1439 END IF;
1440
1441 ------------------------------------------------------------------
1442 -- If the new status is 'ACTIVE' (for example)
1443 -- Ensure that no other SV exists in Active state
1444 ------------------------------------------------------------------
1445 IF l_NEW_PHASE_INDICATOR = 'ACTIVE' THEN
1446
1447 XNP_CORE.SOA_RESET_SV_STATUS
1448 (p_STARTING_NUMBER => l_starting_number
1449 ,p_ENDING_NUMBER => l_ending_number
1450 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
1451 ,p_CUR_PHASE_INDICATOR => l_NEW_PHASE_INDICATOR
1452 ,p_RESET_PHASE_INDICATOR => 'OLD'
1453 ,p_OMIT_STATUS => p_CUR_STATUS_TYPE_CODE
1454 ,P_STATUS_CHANGE_CAUSE_CODE => 'Reset to OLD'
1455 ,p_ORDER_ID => p_ORDER_ID
1456 ,p_LINEITEM_ID => p_LINEITEM_ID
1457 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
1458 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
1459 ,X_ERROR_CODE => x_ERROR_CODE
1460 ,X_ERROR_MESSAGE => x_ERROR_MESSAGE
1461 );
1462 END IF;
1463
1464 -- call updation procedure to update the status
1465
1466 XNP_CORE.SOA_UPDATE_SV_STATUS
1467 (p_STARTING_NUMBER => l_starting_number
1468 ,p_ENDING_NUMBER => l_ending_number
1469 ,p_CUR_STATUS_TYPE_CODE => p_cur_status_type_code
1470 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
1471 ,P_NEW_STATUS_TYPE_CODE => p_new_status_type_code
1472 ,P_STATUS_CHANGE_CAUSE_CODE => p_status_change_cause_code
1473 ,p_ORDER_ID => p_ORDER_ID
1474 ,p_LINEITEM_ID => p_LINEITEM_ID
1475 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
1476 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
1477 ,X_ERROR_CODE => x_ERROR_CODE
1478 ,X_ERROR_MESSAGE => x_ERROR_MESSAGE
1479 );
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 -- Grab the error message and error no.
1484 x_error_code := SQLCODE;
1485
1486 fnd_message.set_name('XNP','STD_ERROR');
1487 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_SV_STATUS');
1488 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1489 x_error_message := fnd_message.get;
1490
1491 END SOA_UPDATE_SV_STATUS;
1492
1493 --------------------------------------------------------------------
1494 -- Descrition:
1495 -- Updates SVs in SOA table for the Porting Id
1496 -- with the invoice infomation
1497 -- Mandatory Parameters: PORTING_ID,STARTING_NUMBER ENDING_NUMBER, SP_NAME
1498 -- Optional Parameters: INVOICE_DUE_DATE, CHARGING_INFO, BILLING_ID,
1499 -- USER_LOCTN_VALUE, USER_LOCTN_TYPE
1500 --
1501 ------------------------------------------------------------------
1502 PROCEDURE SOA_UPDATE_CHARGING_INFO
1503 ( p_ORDER_ID IN NUMBER,
1504 p_LINEITEM_ID IN NUMBER,
1505 p_WORKITEM_INSTANCE_ID IN NUMBER,
1506 p_FA_INSTANCE_ID IN NUMBER,
1507 p_CUR_STATUS_TYPE_CODE VARCHAR2,
1508 x_ERROR_CODE OUT NOCOPY NUMBER,
1509 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1510 )
1511 IS
1512 l_PORTING_ID VARCHAR2(80) := null;
1513 l_STARTING_NUMBER VARCHAR2(20);
1514 l_ENDING_NUMBER VARCHAR2(20);
1515 l_INVOICE_DUE_DATE VARCHAR2(20);
1516 l_CHARGING_INFO VARCHAR2(200);
1517 l_LOCAL_SP_ID NUMBER := 0;
1518 l_SP_NAME VARCHAR2(40) := NULL;
1519 l_BILLING_ID NUMBER := 0;
1520 l_USER_LOCTN_VALUE VARCHAR2(80) := NULL;
1521 l_USER_LOCTN_TYPE VARCHAR2(40) := NULL;
1522 l_PRICE_CODE VARCHAR2(40);
1523 l_PRICE_PER_CALL VARCHAR2(40);
1524 l_PRICE_PER_MINUTE VARCHAR2(40);
1525
1526 BEGIN
1527
1528 x_error_code := 0;
1529
1530 l_porting_id :=
1531 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1532 (p_WORKITEM_INSTANCE_ID
1533 ,'PORTING_ID'
1534 );
1535
1536 l_invoice_due_date :=
1537 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1538 (p_WORKITEM_INSTANCE_ID
1539 ,'INVOICE_DUE_DATE'
1540 );
1541
1542 l_charging_info :=
1543 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1544 (p_WORKITEM_INSTANCE_ID
1545 ,'CHARGING_INFO'
1546 );
1547
1548 l_BILLING_ID :=
1549 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1550 (p_WORKITEM_INSTANCE_ID
1551 ,'BILLING_ID'
1552 );
1553
1554 l_USER_LOCTN_VALUE :=
1555 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1556 (p_WORKITEM_INSTANCE_ID
1557 ,'USER_LOCTN_VALUE'
1558 );
1559
1560 l_USER_LOCTN_TYPE :=
1561 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1562 (p_WORKITEM_INSTANCE_ID
1563 ,'USER_LOCTN_TYPE'
1564 );
1565
1566 l_PRICE_CODE :=
1567 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1568 (p_WORKITEM_INSTANCE_ID
1569 ,'PRICE_CODE'
1570 );
1571
1572 l_PRICE_PER_CALL :=
1573 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1574 (p_WORKITEM_INSTANCE_ID
1575 ,'PRICE_PER_CALL'
1576 );
1577
1578 l_PRICE_PER_MINUTE :=
1579 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1580 (p_WORKITEM_INSTANCE_ID
1581 ,'PRICE_PER_MINUTE'
1582 );
1583
1584 l_SP_NAME :=
1585 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1586 (p_WORKITEM_INSTANCE_ID
1587 ,'SP_NAME'
1588 );
1589
1590 -- call updation procedure to update the charging info
1591
1592 XNP_CORE.SOA_UPDATE_CHARGING_INFO
1593 (p_porting_id =>l_porting_id
1594 ,p_LOCAL_SP_ID =>l_LOCAL_SP_ID
1595 ,p_INVOICE_DUE_DATE =>XNP_UTILS.CANONICAL_TO_DATE(l_invoice_due_date)
1596 ,p_CHARGING_INFO =>l_charging_info
1597 ,p_BILLING_ID =>l_BILLING_ID
1598 ,p_USER_LOCTN_VALUE =>l_USER_LOCTN_VALUE
1599 ,p_USER_LOCTN_TYPE =>l_USER_LOCTN_TYPE
1600 ,p_PRICE_CODE =>l_PRICE_CODE
1601 ,p_PRICE_PER_CALL =>l_PRICE_PER_CALL
1602 ,p_PRICE_PER_MINUTE =>l_PRICE_PER_MINUTE
1603 ,p_ORDER_ID =>p_ORDER_ID
1604 ,p_LINEITEM_ID =>p_LINEITEM_ID
1605 ,p_WORKITEM_INSTANCE_ID =>p_WORKITEM_INSTANCE_ID
1606 ,p_FA_INSTANCE_ID =>p_FA_INSTANCE_ID
1607 ,x_ERROR_CODE =>x_ERROR_CODE
1608 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
1609 );
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 -- Grab the error message and error no.
1614 x_error_code := SQLCODE;
1615
1616 fnd_message.set_name('XNP','STD_ERROR');
1617 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_CHARGING_INFO');
1618 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1619 x_error_message := fnd_message.get;
1620
1621 END SOA_UPDATE_CHARGING_INFO;
1622
1623 --------------------------------------------------------------------
1624 --
1625 -- Called when: Inquiry or Order response is awaited
1626 -- Description:
1627 -- Gets the TN range and calls
1628 -- and calls XNP_CORE.SOA_CHECK_ORDER_STATUS
1629 -- References the following WI parameter
1630 -- ORDER_STATUS
1631 ------------------------------------------------------------------
1632 PROCEDURE SOA_CHECK_ORDER_STATUS
1633 (p_WORKITEM_INSTANCE_ID IN NUMBER
1634 ,x_ORDER_STATUS OUT NOCOPY VARCHAR2
1635 ,x_error_code OUT NOCOPY NUMBER
1636 ,x_error_message OUT NOCOPY VARCHAR2
1637 )
1638 IS
1639
1640 BEGIN
1641 x_error_code := 0;
1642
1643 -- Get the starting and ending TN for this workitem
1644 x_ORDER_STATUS :=
1645 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1646 (p_WORKITEM_INSTANCE_ID
1647 ,'ORDER_RESULT'
1648 );
1649
1650 EXCEPTION
1651 WHEN OTHERS THEN
1652 -- Grab the error message and error no.
1653 x_error_code := SQLCODE;
1654
1655 fnd_message.set_name('XNP','STD_ERROR');
1656 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_CHECK_ORDER_STATUS');
1657 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1658 x_error_message := fnd_message.get;
1659
1660 END SOA_CHECK_ORDER_STATUS;
1661
1662 --------------------------------------------------------------------
1663 -- Checks if the records in the given status
1664 -- and returns 'Y' if true
1665 --
1666 ------------------------------------------------------------------
1667 PROCEDURE CHECK_SOA_STATUS_EXISTS
1668 (p_WORKITEM_INSTANCE_ID NUMBER
1669 ,p_STATUS_TYPE_CODE VARCHAR2
1670 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
1671 ,x_error_code OUT NOCOPY NUMBER
1672 ,x_error_message OUT NOCOPY VARCHAR2
1673 )
1674 IS
1675 l_starting_number VARCHAR2(20);
1676 l_ending_number VARCHAR2(20);
1677 l_donor_sp_id VARCHAR2(20);
1678 l_SERVING_SP_ID NUMBER;
1679 l_LOCAL_SP_ID NUMBER := 0;
1680 l_SP_NAME VARCHAR2(40) := NULL;
1681
1682 BEGIN
1683
1684 x_error_code := 0;
1685
1686
1687 l_starting_number :=
1688 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1689 (p_WORKITEM_INSTANCE_ID
1690 ,'STARTING_NUMBER'
1691 );
1692
1693 l_ending_number :=
1694 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1695 (p_WORKITEM_INSTANCE_ID
1696 ,'ENDING_NUMBER'
1697 );
1698
1699 l_SP_NAME :=
1700 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1701 (p_WORKITEM_INSTANCE_ID
1702 ,'SP_NAME'
1703 );
1704
1705 XNP_CORE.CHECK_SOA_STATUS_EXISTS
1706 (l_STARTING_NUMBER
1707 ,l_ENDING_NUMBER
1708 ,p_STATUS_TYPE_CODE
1709 ,l_LOCAL_SP_ID
1710 ,x_CHECK_STATUS
1711 ,x_ERROR_CODE
1712 ,x_ERROR_MESSAGE
1713 );
1714
1715 EXCEPTION
1716 WHEN OTHERS THEN
1717 -- Grab the error message and error no.
1718 x_error_code := SQLCODE;
1719
1720 fnd_message.set_name('XNP','STD_ERROR');
1721 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.CHECK_SOA_STATUS_EXISTS');
1722 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1723 x_error_message := fnd_message.get;
1724
1725 END CHECK_SOA_STATUS_EXISTS;
1726
1727 --------------------------------------------------------------------
1728 -- Sets the ORDER_RESULT work item
1729 -- parameter value to give one
1730 ------------------------------------------------------------------
1731 PROCEDURE SET_ORDER_RESULT
1732 (p_WORKITEM_INSTANCE_ID NUMBER
1733 ,p_ORDER_RESULT VARCHAR2
1734 ,p_ORDER_REJECT_CODE VARCHAR2
1735 ,p_ORDER_REJECT_EXPLN VARCHAR2
1736 ,x_error_code OUT NOCOPY NUMBER
1737 ,x_error_message OUT NOCOPY VARCHAR2
1738 )
1739
1740 IS
1741
1742 BEGIN
1743
1744 x_error_code := 0;
1745
1746 IF p_ORDER_RESULT = 'SUCCESS' THEN
1747
1748 -- Set the order result to 'Y'
1749 XNP_STANDARD.SET_WORKITEM_PARAM_VALUE
1750 (p_WORKITEM_INSTANCE_ID
1751 ,'ORDER_RESULT'
1752 ,'Y'
1753 ,NULL
1754 );
1755
1756 XNP_STANDARD.SET_WORKITEM_PARAM_VALUE
1757 (p_WORKITEM_INSTANCE_ID
1758 ,'ORDER_REJECT_CODE'
1759 ,'SUCCESS'
1760 ,NULL
1761 );
1762
1763 XNP_STANDARD.SET_WORKITEM_PARAM_VALUE
1764 (p_WORKITEM_INSTANCE_ID
1765 ,'ORDER_REJECT_EXPLN'
1766 ,'SUCCESS'
1767 ,NULL
1768 );
1769
1770 ELSE
1771
1772 -- Set the order result to 'N'
1773 XNP_STANDARD.SET_WORKITEM_PARAM_VALUE
1774 (p_WORKITEM_INSTANCE_ID
1775 ,'ORDER_RESULT'
1776 ,'N'
1777 ,NULL
1778 );
1779
1780 XNP_STANDARD.SET_WORKITEM_PARAM_VALUE
1781 (p_WORKITEM_INSTANCE_ID
1782 ,'ORDER_REJECT_CODE'
1783 ,'FAILURE'
1784 ,NULL
1785 );
1786
1787 XNP_STANDARD.SET_WORKITEM_PARAM_VALUE
1788 (p_WORKITEM_INSTANCE_ID
1789 ,'ORDER_REJECT_EXPLN'
1790 ,'FAILURE'
1791 ,NULL
1792 );
1793
1794 END IF;
1795
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 -- Grab the error message and error no.
1799 x_error_code := SQLCODE;
1800
1801 fnd_message.set_name('XNP','STD_ERROR');
1802 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SET_ORDER_RESULT');
1803 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1804 x_error_message := fnd_message.get;
1805
1806 END SET_ORDER_RESULT;
1807
1808 ------------------------------------------------------------------
1809 -- Called to publish a single business event
1810 -- The recipients of this event should have
1811 -- already subscribed for it incase of
1812 -- internal events
1813 --
1814 -- Note:
1815 -- EVENT TYPE: The message/event type to send
1816 -- PARAM LIST: gives names of the
1817 -- workitem parameters which contain the values.
1818 -- E.g. of format could be
1819 -- S=$STARTING_NUMBER,E=$ENDING_NUMBER
1820 --
1821 -- CALLBACK_REF_ID: Gives the callback handle.
1822 --
1823 ------------------------------------------------------------------
1824 PROCEDURE PUBLISH_EVENT
1825 (p_ORDER_ID NUMBER
1826 ,p_WORKITEM_INSTANCE_ID NUMBER
1827 ,p_FA_INSTANCE_ID NUMBER
1828 ,p_EVENT_TYPE VARCHAR2
1829 ,p_PARAM_LIST VARCHAR2
1830 ,p_CALLBACK_REF_ID VARCHAR2
1831 ,x_error_code OUT NOCOPY NUMBER
1832 ,x_error_message OUT NOCOPY VARCHAR2
1833 )
1834 IS
1835 l_start_posn NUMBER := 0;
1836 l_end_posn NUMBER := 0;
1837 l_name VARCHAR2(256) := NULL;
1838 l_value VARCHAR2(256) := NULL;
1839 l_invocation_param_list VARCHAR2(1024) := NULL;
1840 l_temp VARCHAR2(1024);
1841 l_wf_value VARCHAR2(1024);
1842 l_message_id NUMBER := 0;
1843 l_recipient_list VARCHAR2(2000) := NULL;
1844 l_consumer_list VARCHAR2(4000) := NULL;
1845 l_version NUMBER := 1;
1846 -- l_sender_name VARCHAR2(40) := NULL;
1847 l_sender_name VARCHAR2(300) := NULL; -- increased the size from 40 to 300 for 6880763
1848
1849 --
1850 -- The following line is added by Anping Wang, See comments below for
1851 -- Details. 12/02/2000
1852 --
1853 l_opp_reference VARCHAR2(1024) := NULL;
1854 --
1855 l_callback_reference VARCHAR2(1024) := NULL;
1856
1857 l_CURSOR NUMBER := 0;
1858 l_PROC_CALL VARCHAR2(2000) := NULL;
1859 l_NUM_ROWS NUMBER := 0;
1860 --
1861 -- pre and suffixes with predefined strings
1862 -- By Anping Wang, bug refer. 1650015
1863 -- 02/19/2001
1864 l_pkg_name VARCHAR2(2000);
1865
1866 BEGIN
1867
1868 x_error_code := 0;
1869
1870 l_callback_reference := p_callback_ref_id;
1871
1872 IF p_PARAM_LIST IS NOT NULL THEN
1873
1874 ------------------------------------------------------------------
1875 -- Parse and retrieve the attribute to
1876 -- retrieve from Parameters
1877 -- Rule: The paramters are stored as
1878 -- name value pairs in the foll. format
1879 -- NAME1=$VALUE1,NAME2=$VALUE2,...
1880 -- Each VALUE referes to the corresponding
1881 -- workitem parameter name
1882 ------------------------------------------------------------------
1883 LOOP
1884 l_start_posn := l_end_posn+1;
1885 l_end_posn :=
1886 INSTR(p_PARAM_LIST, '=$',l_start_posn,1);
1887
1888 l_name :=
1889 SUBSTR
1890 (p_PARAM_LIST
1891 , l_start_posn
1892 , (l_end_posn - l_start_posn));
1893
1894 l_start_posn := l_end_posn+1;
1895
1896 l_start_posn := l_start_posn+1; -- next to the '$'
1897
1898 l_end_posn :=
1899 INSTR(p_PARAM_LIST, ',', l_start_posn, 1);
1900 IF l_end_posn <= l_start_posn
1901 THEN
1902 l_end_posn := LENGTH(p_PARAM_LIST)+1;
1903 END IF;
1904
1905 l_value :=
1906 SUBSTR
1907 (p_PARAM_LIST
1908 ,l_start_posn
1909 ,(l_end_posn - l_start_posn)
1910 );
1911
1912 -- Get the value from the WI Parameters
1913 l_wf_value :=
1914 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
1915 (p_WORKITEM_INSTANCE_ID
1916 ,l_value
1917 );
1918
1919 -- Append the NV pair to the message param list
1920 IF l_invocation_param_list IS NULL
1921 THEN
1922 l_temp := '''' || l_wf_value || '''';
1923 ELSE
1924 l_temp := ','|| '''' || l_wf_value || '''';
1925 END IF;
1926
1927 -- Concatenate the paramter list
1928 l_invocation_param_list :=
1929 CONCAT(l_invocation_param_list,l_temp);
1930
1931 EXIT WHEN l_end_posn >= LENGTH(p_PARAM_LIST);
1932 END LOOP;
1933 END IF; -- param list is not null
1934
1935 -- Get the SENDER NAME
1936 l_sender_name :=
1937 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1938 (p_WORKITEM_INSTANCE_ID
1939 ,'SP_NAME'
1940 );
1941
1942
1943 -- The following block was commented out by Anping Wang. Replaced with a new block
1944 -- followed. The order of argument list was not consistent with the prototype used
1945 -- to generate the publish procedure by iMessage studio.
1946 -- 12/06/2000
1947 -- Construct the dynamic SQL
1948
1949 -- pre and suffixes with predefined strings
1950 -- By Anping Wang, bug refer. 1650015
1951 -- 02/19/2001
1952 l_pkg_name := XNP_MESSAGE.g_pkg_prefix || p_event_type || XNP_MESSAGE.g_pkg_suffix ;
1953
1954 IF l_invocation_param_list IS NOT NULL
1955 THEN
1956 l_PROC_CALL :=
1957 'BEGIN
1958 '||l_pkg_name||'.PUBLISH(' ||
1959 l_invocation_param_list || '
1960 ,:l_message_id
1961 ,:x_error_code
1962 ,:x_error_message
1963 ,:l_consumer_list
1964 ,:l_sender_name
1965 ,:l_recipient_list
1966 ,:l_version
1967 ,:l_callback_reference
1968 ,:l_opp_reference_id
1969 ,:p_order_id
1970 ,:p_workitem_instance_id
1971 ,:p_fa_instance_id
1972 );
1973 END;';
1974 ELSE
1975
1976 -- Construct the dynamic SQL
1977 l_PROC_CALL :=
1978 'BEGIN
1979 '||l_pkg_name||'.PUBLISH(' || '
1980 :l_message_id
1981 ,:x_error_code
1982 ,:x_error_message
1983 ,:l_consumer_list
1984 ,:l_sender_name
1985 ,:l_recipient_list
1986 ,:l_version
1987 ,:l_callback_reference
1988 ,:l_opp_reference_id
1989 ,:p_order_id
1990 ,:p_workitem_instance_id
1991 ,:p_fa_instance_id
1992 );
1993 END;';
1994 END IF; -- invocation param list is null
1995
1996 BEGIN
1997
1998 EXECUTE IMMEDIATE l_proc_call USING
1999 OUT l_message_id
2000 ,OUT x_error_code
2001 ,OUT x_error_message
2002 ,IN l_consumer_list
2003 ,IN l_sender_name
2004 ,IN l_recipient_list
2005 ,IN l_version
2006 ,IN l_callback_reference
2007 ,IN l_opp_reference
2008 ,IN p_order_id
2009 ,IN p_workitem_instance_id
2010 ,IN p_fa_instance_id;
2011
2012 EXCEPTION
2013 WHEN OTHERS THEN
2014 -- Grab the error message and error no.
2015 x_error_code := SQLCODE;
2016
2017 fnd_message.set_name('XNP','STD_ERROR');
2018 fnd_message.set_token('ERROR_LOCN'
2019 ,'XNP_STANDARD.PUBLISH_EVENT');
2020 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2021 x_error_message := fnd_message.get;
2022 END;
2023
2024 END PUBLISH_EVENT;
2025
2026
2027 ------------------------------------------------------------------
2028 -- Sends a message to a single recipient. The message is
2029 -- sent to external recipients or internal ones based on
2030 -- the type of the message and queue name
2031 -- Parameters:
2032 -- ORDER ID: order id of this SFM order
2033 -- EVENT TYPE: The message/event type to send
2034 -- PARAM LIST: gives names of the
2035 -- workitem parameters which contain the values.
2036 -- E.g. of format could be
2037 -- S=$STARTING_NUMBER,E=$ENDING_NUMBER
2038 -- WORKITEM INSTANCE ID:gives the handle to fetch
2039 -- the values.
2040 -- CONSUMER: gives the procedure to get the fe name
2041 -- or adapter name) of the receiver
2042 -- CALLBACK_REF_ID: Gives the callback handle.
2043 -- RECEIVER: gives the procedure to get the recipient name
2044 -- VERSION: Version number of the message
2045 -- Mandatory Workitem parameters: Whatever parameter mentioned
2046 -- in the 'PARAM LIST' and additional workitem parameter which
2047 -- are configured as part of the message type definition.
2048 --
2049 ------------------------------------------------------------------
2050 PROCEDURE SEND_MESSAGE
2051 (p_ORDER_ID NUMBER
2052 ,p_WORKITEM_INSTANCE_ID NUMBER
2053 ,p_FA_INSTANCE_ID NUMBER
2054 ,p_EVENT_TYPE VARCHAR2
2055 ,p_PARAM_LIST VARCHAR2
2056 ,p_CALLBACK_REF_ID VARCHAR2
2057 ,p_CONSUMER VARCHAR2
2058 ,p_RECEIVER VARCHAR2
2059 ,p_VERSION NUMBER
2060 ,x_error_code OUT NOCOPY NUMBER
2061 ,x_error_message OUT NOCOPY VARCHAR2
2062 )
2063 IS
2064 l_start_posn NUMBER := 0;
2065 l_end_posn NUMBER := 0;
2066 l_name VARCHAR2(256) := NULL;
2067 l_value VARCHAR2(256) := NULL;
2068 l_invocation_param_list VARCHAR2(1024) := NULL;
2069 l_temp VARCHAR2(1024);
2070 l_wf_value VARCHAR2(1024);
2071 l_message_id NUMBER := 0;
2072
2073 l_FE_NAME VARCHAR2(40) := NULL; -- adapter name
2074
2075 l_CURSOR NUMBER := 0;
2076 l_PROC_CALL VARCHAR2(2000) := NULL;
2077 l_NUM_ROWS NUMBER := 0;
2078
2079 l_CONSUMER_NAME VARCHAR2(40) := NULL;
2080 l_CONSUMER_CURSOR NUMBER := 0;
2081 l_CONSUMER_PROC_CALL VARCHAR2(2000) := NULL;
2082 l_CONSUMER_NUM_ROWS NUMBER := 0;
2083
2084 l_RECIPIENT_NAME VARCHAR2(40) := NULL; -- recipient name
2085 l_RECIPIENT_CURSOR NUMBER := 0;
2086 l_RECIPIENT_PROC_CALL VARCHAR2(2000) := NULL;
2087 l_RECIPIENT_NUM_ROWS NUMBER := 0;
2088
2089 -- l_sender_name VARCHAR2(40) := NULL;
2090 l_sender_name VARCHAR2(300) := NULL; -- increased the size from 40 to 300 for 6880763
2091 l_OPP_REFERENCE_ID VARCHAR2(240);
2092 l_pkg_name VARCHAR2(2000);
2093
2094 BEGIN
2095 x_error_code := 0;
2096
2097 ------------------------------------------------------------------
2098 -- Get the consumer name (ie. adpater name) and
2099 -- recipient name before proceeding to call SEND
2100 ------------------------------------------------------------------
2101 -- Get the consumer name
2102
2103 -- Construct the dynamic SQL
2104 l_CONSUMER_PROC_CALL :=
2105 'BEGIN
2106 '||p_CONSUMER||'(' || '
2107 :p_ORDER_ID
2108 ,:p_WORKITEM_INSTANCE_ID
2109 ,:p_FA_INSTANCE_ID
2110 ,:l_CONSUMER_NAME
2111 ,:x_error_code
2112 ,:x_error_message);
2113 END;';
2114
2115 BEGIN
2116
2117 EXECUTE IMMEDIATE l_consumer_proc_call USING
2118 IN p_order_id
2119 ,IN p_workitem_instance_id
2120 ,IN p_fa_instance_id
2121 ,OUT l_consumer_name
2122 ,OUT x_error_code
2123 ,OUT x_error_message;
2124
2125 IF x_error_code <> 0
2126 THEN
2127 fnd_message.set_name('XNP','STD_GET_FAILED');
2128 fnd_message.set_token
2129 ('FAILED_PROC','XNP_STANDARD.SEND_MESSAGE');
2130 fnd_message.set_token('ATTRNAME',p_CONSUMER);
2131 fnd_message.set_token('KEY','WORKITEM_INSTANCE_ID');
2132 fnd_message.set_token('VALUE',p_WORKITEM_INSTANCE_ID);
2133 x_error_message := fnd_message.get||':'||x_error_message;
2134 RETURN;
2135 END IF;
2136
2137 -- BUG # 1500177
2138 -- l_consumer_proc now returns the FE_NAME (and not the ADAPTER_NAME)
2139 -- Get the ADAPTER_NAME for the FE_NAME
2140
2141 l_fe_name := l_consumer_name;
2142
2143 l_consumer_name := XDP_ADAPTER_CORE_DB.IS_MESSAGE_ADAPTER_AVAILABLE(p_fe_name => l_fe_name);
2144
2145 IF l_consumer_name IS NULL
2146 THEN
2147 x_error_code := xnp_errors.g_no_adapter_for_fe;
2148 fnd_message.set_name('XNP','NO_ADAPTER_FOR_FE');
2149 fnd_message.set_token('NAME',l_fe_name );
2150 x_error_message := fnd_message.get;
2151 END IF;
2152
2153 IF x_error_code <> 0
2154 THEN
2155 fnd_message.set_name('XNP','STD_GET_FAILED');
2156 fnd_message.set_token
2157 ('FAILED_PROC','XNP_STANDARD.SEND_MESSAGE');
2158 fnd_message.set_token('ATTRNAME',p_CONSUMER);
2159 fnd_message.set_token('KEY','WORKITEM_INSTANCE_ID');
2160 fnd_message.set_token('VALUE',p_WORKITEM_INSTANCE_ID);
2161 x_error_message := fnd_message.get||':'||x_error_message;
2162 RETURN;
2163 END IF;
2164
2165
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 -- Grab the error message and error no.
2169 x_error_code := SQLCODE;
2170 fnd_message.set_name('XNP','XNP_DYNA_EXEC_FAILED');
2171 fnd_message.set_token
2172 ('FAILED_PROC','XNP_STANDARD.SEND_MESSAGE');
2173 fnd_message.set_token('DYNA_PROC',p_CONSUMER);
2174 fnd_message.set_token('KEY','WORKITEM_INSTANCE_ID');
2175 fnd_message.set_token('VALUE',p_WORKITEM_INSTANCE_ID);
2176 x_error_message :=
2177 fnd_message.get;
2178 x_error_message := x_error_message ||':'||SQLERRM;
2179 END;
2180
2181 -- If error while getting consumer name then return
2182 IF x_error_code <> 0
2183 THEN
2184 -- Close the cursor
2185 RETURN;
2186 END IF;
2187
2188 -- Get the recipient name
2189
2190 -- Construct the dynamic SQL
2191 l_RECIPIENT_PROC_CALL :=
2192 'BEGIN
2193 '||p_receiver||'(' || '
2194 :p_order_id
2195 ,:p_workitem_instance_id
2196 ,:p_fa_instance_id
2197 ,:l_recipient_name
2198 ,:x_error_code
2199 ,:x_error_message);
2200 END;';
2201
2202
2203 BEGIN
2204
2205 EXECUTE IMMEDIATE l_recipient_proc_call USING
2206 IN p_order_id
2207 ,IN p_workitem_instance_id
2208 ,IN p_fa_instance_id
2209 ,OUT l_recipient_name
2210 ,OUT x_error_code
2211 ,OUT x_error_message;
2212
2213 IF x_error_code <> 0
2214 THEN
2215 fnd_message.set_name('XNP','STD_GET_FAILED');
2216 fnd_message.set_token('ATTRNAME',p_RECEIVER);
2217 fnd_message.set_token
2218 ('FAILED_PROC','XNP_STANDARD.SEND_MESSAGE');
2219 fnd_message.set_token('KEY','WORKITEM_INSTANCE_ID');
2220 fnd_message.set_token('VALUE',p_WORKITEM_INSTANCE_ID);
2221 x_error_message := fnd_message.get;
2222 x_error_message := fnd_message.get||':'||x_error_message;
2223 RETURN;
2224 END IF;
2225
2226
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 -- Grab the error message and error no.
2230 x_error_code := SQLCODE;
2231
2232 fnd_message.set_name('XNP','XNP_DYNA_EXEC_FAILED');
2233 fnd_message.set_token('DYNA_PROC',p_RECEIVER);
2234 fnd_message.set_token
2235 ('FAILED_PROC','XNP_STANDARD.SEND_MESSAGE');
2236 fnd_message.set_token('KEY','WORKITEM_INSTANCE_ID');
2237 fnd_message.set_token('VALUE',p_WORKITEM_INSTANCE_ID);
2238 x_error_message := fnd_message.get||':'||x_error_message;
2239 END;
2240
2241 -- If error while getting recipient name then return
2242 IF x_error_code <> 0
2243 THEN
2244 RETURN;
2245 END IF;
2246
2247 ------------------------------------------------------------------
2248 -- Parse and retrieve the attribute to
2249 -- retrieve from Parameters
2250 -- Rule: The paramters are stored as
2251 -- name value pairs in the foll. format
2252 -- NAME1=$VALUE1,NAME2=$VALUE2,...
2253 -- Each VALUE referes to the corresponding
2254 -- workitem parameter name
2255 ------------------------------------------------------------------
2256 l_start_posn := 0;
2257 l_end_posn := 0;
2258 l_invocation_param_list := NULL;
2259
2260 IF p_PARAM_LIST IS NOT NULL
2261 THEN
2262 LOOP
2263 l_start_posn := l_end_posn+1;
2264 l_end_posn :=
2265 INSTR(p_PARAM_LIST, '=$',l_start_posn,1);
2266
2267 l_name :=
2268 SUBSTR
2269 (p_PARAM_LIST
2270 , l_start_posn
2271 , (l_end_posn - l_start_posn));
2272
2273 l_start_posn := l_end_posn+1;
2274
2275 l_start_posn := l_start_posn+1; -- next to the '$'
2276
2277 l_end_posn :=
2278 INSTR(p_PARAM_LIST, ',', l_start_posn, 1);
2279 IF l_end_posn <= l_start_posn
2280 THEN
2281 l_end_posn := LENGTH(p_PARAM_LIST)+1;
2282 END IF;
2283
2284 l_value :=
2285 SUBSTR
2286 (p_PARAM_LIST
2287 ,l_start_posn
2288 ,(l_end_posn - l_start_posn)
2289 );
2290
2291
2292 -- Get the value from the WI Parameters
2293 l_wf_value :=
2294 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2295 (p_WORKITEM_INSTANCE_ID
2296 ,l_value
2297 );
2298
2299 -- Append the NV pair to the message param list
2300 IF l_invocation_param_list IS NULL
2301 THEN
2302 l_temp := '''' || l_wf_value || '''';
2303 ELSE
2304 l_temp := ','|| '''' || l_wf_value || '''';
2305 END IF;
2306
2307
2308 -- Concatenate the paramter list
2309 l_invocation_param_list :=
2310 CONCAT(l_invocation_param_list,l_temp);
2311
2312 EXIT WHEN l_end_posn >= LENGTH(p_PARAM_LIST);
2313 END LOOP;
2314 END IF; -- p_PARAM_LIST not null
2315
2316 -- Get the SENDER NAME
2317 l_sender_name :=
2318 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2319 (p_WORKITEM_INSTANCE_ID
2320 ,'SP_NAME'
2321 );
2322
2323 -- Get the opposite party's reference id
2324 l_OPP_REFERENCE_ID :=
2325 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2326 (p_WORKITEM_INSTANCE_ID
2327 ,'OPP_REFERENCE_ID'
2328 );
2329
2330 -- Construct the dynamic SQL
2331
2332 -- pre and suffixes with predefined strings
2333 -- By Anping Wang, bug refer. 1650015
2334 -- 02/19/2001
2335 l_pkg_name := XNP_MESSAGE.g_pkg_prefix || p_event_type || XNP_MESSAGE.g_pkg_suffix ;
2336
2337 IF l_invocation_param_list IS NOT NULL
2338 THEN
2339 l_PROC_CALL :=
2340 'BEGIN
2341 '||l_pkg_name||'.SEND(' ||
2342 l_invocation_param_list || '
2343 ,:l_message_id
2344 ,:x_error_code
2345 ,:x_error_message
2346 ,:l_consumer_name
2347 ,:l_sender_name
2348 ,:l_recipient_name
2349 ,:p_version
2350 ,:p_callback_ref_id
2351 ,:l_opp_reference_id
2352 ,:p_order_id
2353 ,:p_workitem_instance_id
2354 ,:p_fa_instance_id);
2355 END;';
2356 ELSE
2357
2358 l_PROC_CALL :=
2359 'BEGIN
2360 '||l_pkg_name||'.SEND(' || '
2361 :l_message_id
2362 ,:x_error_code
2363 ,:x_error_message
2364 ,:l_consumer_name
2365 ,:l_sender_name
2366 ,:l_recipient_name
2367 ,:p_version
2368 ,:p_callback_ref_id
2369 ,:l_opp_reference_id
2370 ,:p_order_id
2371 ,:p_workitem_instance_id
2372 ,:p_fa_instance_id);
2373 END;';
2374 END IF;
2375
2376 -- BUG # 1500177
2377 -- This FE_NAME is used by the publish() called within the send()
2378 -- while doing push() to the queue to populate correct FE_NAME
2379
2380 XNP_STANDARD.FE_NAME := l_fe_name;
2381
2382
2383 BEGIN
2384 EXECUTE IMMEDIATE l_proc_call USING
2385 OUT l_message_id
2386 ,OUT x_error_code
2387 ,OUT x_error_message
2388 ,IN l_consumer_name
2389 ,IN l_sender_name
2390 ,IN l_recipient_name
2391 ,IN p_version
2392 ,IN p_callback_ref_id
2393 ,IN l_opp_reference_id
2394 ,IN p_order_id
2395 ,IN p_workitem_instance_id
2396 ,IN p_fa_instance_id;
2397
2398 EXCEPTION
2399 WHEN OTHERS THEN
2400
2401 XNP_STANDARD.FE_NAME := null;
2402
2403 -- Grab the error message and error no.
2404 x_error_code := SQLCODE;
2405
2406 fnd_message.set_name('XNP','STD_ERROR');
2407 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SEND_MESSAGE');
2408 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2409 x_error_message := fnd_message.get;
2410 END;
2411
2412 XNP_STANDARD.FE_NAME := null;
2413
2414 END SEND_MESSAGE;
2415
2416
2417 --------------------------------------------------------------------
2418 --
2419 -- Called when: During provisioning phase of the order
2420 -- Called by:
2421 -- Description:
2422 -- Calls XNP_CORE.SMS_DELETE_FE_MAP
2423 -- References WI params STARTING_NUMBER, ENDING_NUMBER
2424 --
2425 ------------------------------------------------------------------
2426 PROCEDURE SMS_DELETE_FE_MAP
2427 (p_WORKITEM_INSTANCE_ID IN NUMBER
2428 ,p_FE_ID IN NUMBER
2429 ,p_FEATURE_TYPE VARCHAR2
2430 ,x_ERROR_CODE OUT NOCOPY NUMBER
2431 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2432 )
2433 IS
2434 l_STARTING_NUMBER VARCHAR2(40);
2435 l_ENDING_NUMBER VARCHAR2(40);
2436
2437 BEGIN
2438 x_error_code := 0;
2439 -- Get the starting and ending TN for this workitem
2440
2441 l_starting_number :=
2442 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2443 (p_WORKITEM_INSTANCE_ID
2444 ,'STARTING_NUMBER'
2445 );
2446
2447 l_ending_number :=
2448 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2449 (p_WORKITEM_INSTANCE_ID
2450 ,'ENDING_NUMBER'
2451 );
2452
2453 -- Call XNP_CORE.SMS_DELETE_FE_MAP
2454 XNP_CORE.SMS_DELETE_FE_MAP
2455 (l_STARTING_NUMBER
2456 ,l_ENDING_NUMBER
2457 ,p_FE_ID
2458 ,p_FEATURE_TYPE
2459 ,x_ERROR_CODE
2460 ,x_ERROR_MESSAGE
2461 );
2462
2463 EXCEPTION
2464 WHEN OTHERS THEN
2465 -- Grab the error message and error no.
2466 x_error_code := SQLCODE;
2467
2468 fnd_message.set_name('XNP','STD_ERROR');
2469 fnd_message.set_token('ERROR_LOCN'
2470 ,'XNP_STANDARD.SMS_DELETE_FE_MAP');
2471 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2472 x_error_message := fnd_message.get;
2473
2474 END SMS_DELETE_FE_MAP;
2475
2476 ------------------------------------------------------------------
2477 -- Gets the TN range for this order and
2478 -- checks if there exists a TN in the
2479 -- given phase with the local SP performing
2480 -- the given role
2481 -- Mandatory Workitem parameters:
2482 -- STARTING_NUMBER,
2483 -- ENDING_NUMBER, SP_NAME, PORTING_ID
2484 --
2485 ------------------------------------------------------------------
2486
2487 PROCEDURE CHECK_PHASE_FOR_ROLE
2488 (p_WORKITEM_INSTANCE_ID IN NUMBER
2489 ,p_SP_ROLE IN VARCHAR2
2490 ,p_PHASE_INDICATOR IN VARCHAR2
2491 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
2492 ,x_ERROR_CODE OUT NOCOPY NUMBER
2493 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2494 )
2495 IS
2496 l_starting_number VARCHAR2(20);
2497 l_ending_number VARCHAR2(20);
2498 l_SP_NAME VARCHAR2(20);
2499 l_SP_ID NUMBER;
2500 l_donor_sp_id VARCHAR2(20);
2501 l_recipient_sp_id VARCHAR2(20);
2502 l_SERVING_SP_ID NUMBER;
2503
2504 BEGIN
2505
2506 x_error_code := 0;
2507 x_CHECK_STATUS := 'N';
2508
2509 l_starting_number :=
2510 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2511 (p_WORKITEM_INSTANCE_ID
2512 ,'STARTING_NUMBER'
2513 );
2514
2515 l_ending_number :=
2516 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2517 (p_WORKITEM_INSTANCE_ID
2518 ,'ENDING_NUMBER'
2519 );
2520
2521 l_SP_NAME :=
2522 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2523 (p_WORKITEM_INSTANCE_ID
2524 ,'SP_NAME'
2525 );
2526
2527 -- Get the SP id for this SP code
2528 XNP_CORE.GET_SP_ID
2529 (l_SP_NAME
2530 ,l_SP_ID
2531 ,x_ERROR_CODE
2532 ,x_ERROR_MESSAGE
2533 );
2534
2535 IF x_ERROR_CODE <> 0
2536 THEN
2537 RETURN;
2538 END IF;
2539
2540 IF p_SP_ROLE = 'DONOR'
2541 THEN
2542 -- check for TN in given phase with SP as donor
2543 XNP_CORE.CHECK_DONOR_PHASE
2544 (p_STARTING_NUMBER =>l_STARTING_NUMBER
2545 ,p_ENDING_NUMBER =>l_ENDING_NUMBER
2546 ,p_SP_ID =>l_SP_ID
2547 ,p_PHASE_INDICATOR =>p_PHASE_INDICATOR
2548 ,x_CHECK_EXISTS =>x_CHECK_STATUS
2549 ,x_ERROR_CODE =>x_ERROR_CODE
2550 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
2551 );
2552 ELSE
2553 -- check for TN in given phase with SP as recipient
2554 XNP_CORE.CHECK_RECIPIENT_PHASE
2555 (p_STARTING_NUMBER =>l_STARTING_NUMBER
2556 ,p_ENDING_NUMBER =>l_ENDING_NUMBER
2557 ,p_SP_ID =>l_SP_ID
2558 ,p_PHASE_INDICATOR =>p_PHASE_INDICATOR
2559 ,x_CHECK_EXISTS =>x_CHECK_STATUS
2560 ,x_ERROR_CODE =>x_ERROR_CODE
2561 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
2562 );
2563 END IF;
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567 -- Grab the error message and error no.
2568 x_error_code := SQLCODE;
2569
2570 fnd_message.set_name('XNP','STD_ERROR');
2571 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.CHECK_PHASE_FOR_ROLE');
2572 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2573 x_error_message := fnd_message.get;
2574 END CHECK_PHASE_FOR_ROLE;
2575
2576 ------------------------------------------------------------------
2577 -- Purpose: Registers a callback for the given event
2578 -- from the remote or local system.
2579 -- Calls XNP_EVENT.SUBSCRIBE
2580 ------------------------------------------------------------------
2581 PROCEDURE SUBSCRIBE_FOR_EVENT
2582 (p_MESSAGE_TYPE IN VARCHAR2
2583 ,p_WORKITEM_INSTANCE_ID IN NUMBER
2584 ,p_CALLBACK_REF_ID VARCHAR2
2585 ,p_PROCESS_REFERENCE IN VARCHAR2
2586 ,p_ORDER_ID IN NUMBER
2587 ,p_FA_INSTANCE_ID IN NUMBER
2588 ,x_ERROR_CODE OUT NOCOPY NUMBER
2589 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2590 )
2591 IS
2592 l_callback_reference VARCHAR2(1024) := NULL;
2593 l_name VARCHAR2(256) := NULL;
2594 l_value VARCHAR2(256) := NULL;
2595 l_start_posn NUMBER := 0;
2596 l_end_posn NUMBER := 0;
2597 l_wf_value VARCHAR2(1024);
2598 l_temp VARCHAR2(1024);
2599 BEGIN
2600
2601 x_error_code := 0;
2602 x_error_message := NULL ;
2603 l_callback_reference := p_CALLBACK_REF_ID;
2604
2605 ------ Register callback for the event -------
2606
2607 XNP_EVENT.SUBSCRIBE
2608 (P_MSG_CODE=>p_MESSAGE_TYPE
2609 ,P_REFERENCE_ID=>l_callback_reference
2610 ,P_PROCESS_REFERENCE=>p_process_reference
2611 ,P_PROCEDURE_NAME=>'XNP_EVENT.RESUME_WORKFLOW'
2612 ,P_CALLBACK_TYPE=>'PL/SQL'
2613 ,P_CLOSE_REQD_FLAG=>'Y'
2614 ,P_ORDER_ID=>p_ORDER_ID
2615 ,P_WI_INSTANCE_ID=>p_WORKITEM_INSTANCE_ID
2616 ,P_FA_INSTANCE_ID=>p_FA_INSTANCE_ID
2617 );
2618
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 x_error_code := SQLCODE;
2622
2623 fnd_message.set_name('XNP','STD_ERROR');
2624 fnd_message.set_token('ERROR_LOCN'
2625 ,'XNP_STANDARD.SUBSCRIBE_FOR_EVENT');
2626 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2627 x_error_message := fnd_message.get;
2628 END SUBSCRIBE_FOR_EVENT;
2629
2630 --------------------------------------------------------------------
2631 -- Checks if this is a subsequent porting
2632 -- request and returns Y/N accordingly
2633 -- Expects the workitem paramter 'SUBSEQUENT_PORT'
2634 ------------------------------------------------------------------
2635 PROCEDURE SOA_IS_SUBSEQUENT_PORT
2636 (p_WORKITEM_INSTANCE_ID IN NUMBER
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 BEGIN
2643
2644 -- Get the work item parameter 'SUBSEQUENT_PORT'
2645 x_CHECK_STATUS :=
2646 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2647 (p_WORKITEM_INSTANCE_ID
2648 ,'SUBSEQUENT_PORT'
2649 );
2650
2651 if (x_CHECK_STATUS = NULL)
2652 then
2653 x_CHECK_STATUS := 'N';
2654 end if;
2655
2656 EXCEPTION
2657 WHEN OTHERS THEN
2658 x_error_code := SQLCODE;
2659
2660 fnd_message.set_name('XNP','STD_ERROR');
2661 fnd_message.set_token('ERROR_LOCN'
2662 ,'XNP_STANDARD.SOA_IS_SUBSEQUENT_PORT');
2663 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2664 x_error_message := fnd_message.get;
2665 END SOA_IS_SUBSEQUENT_PORT;
2666
2667 ------------------------------------------------------------------
2668 -- Updates the SMS_FE_MAP status for the
2669 -- SVs corresponding to the given TNs
2670 -- to the new PROVISION_STATUS
2671 ------------------------------------------------------------------
2672 PROCEDURE SMS_UPDATE_FE_MAP_STATUS
2673 (p_ORDER_ID IN NUMBER,
2674 p_LINEITEM_ID IN NUMBER,
2675 p_WORKITEM_INSTANCE_ID IN NUMBER,
2676 p_FA_INSTANCE_ID IN NUMBER,
2677 p_FEATURE_TYPE VARCHAR2,
2678 p_FE_ID NUMBER,
2679 p_PROV_STATUS VARCHAR2,
2680 x_ERROR_CODE OUT NOCOPY NUMBER,
2681 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2682 )
2683 IS
2684 l_STARTING_NUMBER VARCHAR2(20);
2685 l_ENDING_NUMBER VARCHAR2(20);
2686
2687 BEGIN
2688
2689 x_error_code := 0;
2690
2691 l_starting_number :=
2692 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2693 (p_WORKITEM_INSTANCE_ID
2694 ,'STARTING_NUMBER'
2695 );
2696
2697 l_ending_number :=
2698 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2699 (p_WORKITEM_INSTANCE_ID
2700 ,'ENDING_NUMBER'
2701 );
2702
2703 -- call updation procedure to get the fe list
2704
2705 XNP_CORE.SMS_UPDATE_FE_MAP_STATUS
2706 (p_STARTING_NUMBER => l_STARTING_NUMBER
2707 ,p_ENDING_NUMBER => l_ENDING_NUMBER
2708 ,p_FE_ID => p_FE_ID
2709 ,p_PROV_STATUS => p_PROV_STATUS
2710 ,p_FEATURE_TYPE => p_FEATURE_TYPE
2711 ,p_ORDER_ID => p_ORDER_ID
2712 ,p_LINEITEM_ID => p_LINEITEM_ID
2713 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
2714 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
2715 ,x_ERROR_CODE => x_ERROR_CODE
2716 ,x_ERROR_MESSAGE => x_ERROR_MESSAGE
2717 );
2718
2719
2720 EXCEPTION
2721 WHEN OTHERS THEN
2722 -- Grab the error message and error no.
2723 x_error_code := SQLCODE;
2724
2725 fnd_message.set_name('XNP','STD_ERROR');
2726 fnd_message.set_token('ERROR_LOCN'
2727 ,'XNP_STANDARD.SMS_UPDATE_FE_MAP_STATUS');
2728 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2729 x_error_message := fnd_message.get;
2730
2731 END SMS_UPDATE_FE_MAP_STATUS;
2732
2733
2734 --------------------------------------------------------------------
2735 --
2736 -- Called when: there is a Modify Ported Number
2737 -- request from NRC
2738 -- Description: Extracts the order information from SFM
2739 -- Workitem params
2740 -- Mandatory: Gets the PORTING_ID, STARTING_NUMBER, ENDING_NUMBER,
2741 -- PORTING_TIME,ROUTING_NUMBER
2742 -- Optional: CNAM_ADDRESS, CNAM_SUBSYSTEM,
2743 -- ISVM_ADDRESS, ISVM_SUBSYSTEM, LIDB_ADDRESS, LIDB_SUBSYSTEM,
2744 -- CLASS_ADDRESS, CLASS_SUBSYSTEM, WSMSC_ADDRESS, WSMSC_SUBSYSTEM,
2745 -- RN_ADDRESS, RN_SUBSYSTEM, SUBSCRIPTION_TYPE
2746 -- Modifies entry in SMS table for each TN in the range
2747 ------------------------------------------------------------------
2748 PROCEDURE SMS_MODIFY_PORTED_NUMBER
2749 ( p_ORDER_ID IN NUMBER,
2750 p_LINEITEM_ID IN NUMBER,
2751 p_WORKITEM_INSTANCE_ID IN NUMBER,
2752 p_FA_INSTANCE_ID IN NUMBER,
2753 x_ERROR_CODE OUT NOCOPY NUMBER,
2754 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2755 )
2756 IS
2757 l_sv_id NUMBER;
2758 l_porting_id VARCHAR2(80) := null;
2759 l_routing_number VARCHAR2(40);
2760 l_starting_number VARCHAR2(20);
2761 l_porting_time VARCHAR2(40);
2762 l_ending_number VARCHAR2(20);
2763 l_counter BINARY_INTEGER;
2764 l_index BINARY_INTEGER := 0;
2765 l_NRC_ID NUMBER;
2766 l_GEO_ID NUMBER;
2767 l_ROUTING_NUMBER_ID NUMBER;
2768
2769 l_SUBSCRIPTION_TYPE VARCHAR2(80) := 'NP';
2770 l_CNAM_ADDRESS VARCHAR2(80) := NULL;
2771 l_CNAM_SUBSYSTEM VARCHAR2(80) := NULL;
2772 l_ISVM_ADDRESS VARCHAR2(80) := NULL;
2773 l_ISVM_SUBSYSTEM VARCHAR2(80) := NULL;
2774 l_LIDB_ADDRESS VARCHAR2(80) := NULL;
2775 l_LIDB_SUBSYSTEM VARCHAR2(80) := NULL;
2776 l_CLASS_ADDRESS VARCHAR2(80) := NULL;
2777 l_CLASS_SUBSYSTEM VARCHAR2(80) := NULL;
2778 l_WSMSC_ADDRESS VARCHAR2(80) := NULL;
2779 l_WSMSC_SUBSYSTEM VARCHAR2(80) := NULL;
2780 l_RN_ADDRESS VARCHAR2(80) := NULL;
2781 l_RN_SUBSYSTEM VARCHAR2(80) := NULL;
2782
2783 BEGIN
2784
2785 x_error_code := 0;
2786
2787 l_porting_id :=
2788 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2789 (p_WORKITEM_INSTANCE_ID
2790 ,'PORTING_ID'
2791 );
2792
2793 l_porting_time :=
2794 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2795 (p_WORKITEM_INSTANCE_ID
2796 ,'PORTING_TIME'
2797 );
2798
2799 l_starting_number :=
2800 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2801 (p_WORKITEM_INSTANCE_ID
2802 ,'STARTING_NUMBER'
2803 );
2804
2805 l_ending_number :=
2806 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2807 (p_WORKITEM_INSTANCE_ID
2808 ,'ENDING_NUMBER'
2809 );
2810
2811 l_routing_number :=
2812 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2813 (p_WORKITEM_INSTANCE_ID
2814 ,'ROUTING_NUMBER'
2815 );
2816
2817 -- Get the routing_number_id corresponding to the code
2818
2819 XNP_CORE.GET_ROUTING_NUMBER_ID
2820 (l_ROUTING_NUMBER
2821 ,l_ROUTING_NUMBER_ID
2822 ,x_ERROR_CODE
2823 ,x_ERROR_MESSAGE
2824 );
2825 IF x_ERROR_CODE <> 0
2826 THEN
2827 RETURN;
2828 END IF;
2829
2830 l_SUBSCRIPTION_TYPE :=
2831 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2832 (p_WORKITEM_INSTANCE_ID
2833 ,'SUBSCRIPTION_TYPE'
2834 );
2835 if (l_SUBSCRIPTION_TYPE IS null) then
2836 l_SUBSCRIPTION_TYPE := 'NP';
2837 end if;
2838
2839 l_CNAM_ADDRESS :=
2840 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2841 (p_WORKITEM_INSTANCE_ID
2842 ,'CNAM_ADDRESS'
2843 );
2844
2845 l_CNAM_SUBSYSTEM :=
2846 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2847 (p_WORKITEM_INSTANCE_ID
2848 ,'CNAM_SUBSYSTEM'
2849 );
2850
2851 l_ISVM_ADDRESS :=
2852 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2853 (p_WORKITEM_INSTANCE_ID
2854 ,'ISVM_ADDRESS'
2855 );
2856
2857 l_ISVM_SUBSYSTEM :=
2858 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2859 (p_WORKITEM_INSTANCE_ID
2860 ,'ISVM_SUBSYSTEM'
2861 );
2862
2863 l_LIDB_ADDRESS :=
2864 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2865 (p_WORKITEM_INSTANCE_ID
2866 ,'LIDB_ADDRESS'
2867 );
2868
2869 l_LIDB_SUBSYSTEM :=
2870 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2871 (p_WORKITEM_INSTANCE_ID
2872 ,'LIDB_SUBSYSTEM'
2873 );
2874
2875 l_CLASS_ADDRESS :=
2876 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2877 (p_WORKITEM_INSTANCE_ID
2878 ,'CLASS_ADDRESS'
2879 );
2880
2881 l_CLASS_SUBSYSTEM :=
2882 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2883 (p_WORKITEM_INSTANCE_ID
2884 ,'CLASS_SUBSYSTEM'
2885 );
2886
2887 l_WSMSC_ADDRESS :=
2888 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2889 (p_WORKITEM_INSTANCE_ID
2890 ,'WSMSC_ADDRESS'
2891 );
2892
2893 l_WSMSC_SUBSYSTEM :=
2894 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2895 (p_WORKITEM_INSTANCE_ID
2896 ,'WSMSC_SUBSYSTEM'
2897 );
2898
2899 l_RN_ADDRESS :=
2900 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2901 (p_WORKITEM_INSTANCE_ID
2902 ,'RN_ADDRESS'
2903 );
2904
2905 l_RN_SUBSYSTEM :=
2906 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
2907 (p_WORKITEM_INSTANCE_ID
2908 ,'RN_SUBSYSTEM'
2909 );
2910
2911 XNP_CORE.SMS_MODIFY_PORTED_NUMBER
2912 (p_PORTING_ID =>l_porting_id
2913 ,p_STARTING_NUMBER =>to_number(l_STARTING_NUMBER)
2914 ,p_ENDING_NUMBER =>to_number(l_ENDING_NUMBER)
2915 ,p_ROUTING_NUMBER_ID =>l_ROUTING_NUMBER_ID
2916 ,p_PORTING_TIME =>XNP_UTILS.CANONICAL_TO_DATE(l_porting_time)
2917 ,p_CNAM_ADDRESS =>l_CNAM_ADDRESS
2918 ,p_CNAM_SUBSYSTEM =>l_CNAM_SUBSYSTEM
2919 ,p_ISVM_ADDRESS =>l_ISVM_ADDRESS
2920 ,p_ISVM_SUBSYSTEM =>l_ISVM_SUBSYSTEM
2921 ,p_LIDB_ADDRESS =>l_LIDB_ADDRESS
2922 ,p_LIDB_SUBSYSTEM =>l_LIDB_SUBSYSTEM
2923 ,p_CLASS_ADDRESS =>l_CLASS_ADDRESS
2924 ,p_CLASS_SUBSYSTEM =>l_CLASS_SUBSYSTEM
2925 ,p_WSMSC_ADDRESS =>l_WSMSC_ADDRESS
2926 ,p_WSMSC_SUBSYSTEM =>l_WSMSC_SUBSYSTEM
2927 ,p_RN_ADDRESS =>l_RN_ADDRESS
2928 ,p_RN_SUBSYSTEM =>l_RN_SUBSYSTEM
2929 ,p_ORDER_ID =>p_ORDER_ID
2930 ,p_LINEITEM_ID =>p_LINEITEM_ID
2931 ,p_WORKITEM_INSTANCE_ID =>p_WORKITEM_INSTANCE_ID
2932 ,p_FA_INSTANCE_ID =>p_FA_INSTANCE_ID
2933 ,x_ERROR_CODE =>x_ERROR_CODE
2934 ,x_ERROR_MESSAGE =>x_ERROR_MESSAGE
2935 );
2936
2937 EXCEPTION
2938 WHEN OTHERS THEN
2939 -- Grab the error message and error no.
2940 x_error_code := SQLCODE;
2941
2942 fnd_message.set_name('XNP','STD_ERROR');
2943 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SMS_MODIFY_PORTED_NUMBER');
2944 fnd_message.set_token('ERROR_TEXT',SQLERRM);
2945 x_error_message := fnd_message.get;
2946 END SMS_MODIFY_PORTED_NUMBER;
2947
2948 ------------------------------------------------------------------
2949 -- Calls xnp_core.check_donor_status_exists
2950 -- to check if there exists a porting record
2951 -- for the number range and created by the donor
2952 -- with the given status type code
2953 -- Mandatory WI Parameters: STARTING_NUMBER,ENDING_NUMBER,DONOR_SP_ID
2954 ------------------------------------------------------------------
2955 PROCEDURE SOA_CHECK_DON_STATUS_EXISTS
2956 (p_WORKITEM_INSTANCE_ID IN NUMBER
2957 ,P_STATUS_TO_CHECK_WITH IN VARCHAR2
2958 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
2959 ,x_ERROR_CODE OUT NOCOPY NUMBER
2960 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2961 )
2962 IS
2963 l_starting_number varchar2(80) := null;
2964 l_ending_number varchar2(80) := null;
2965 l_donor_sp_code varchar2(80) := null;
2966 l_donor_sp_id number := 0;
2967
2968 BEGIN
2969
2970 l_STARTING_NUMBER :=
2971 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2972 (p_WORKITEM_INSTANCE_ID
2973 ,'STARTING_NUMBER'
2974 );
2975
2976 l_ENDING_NUMBER :=
2977 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2978 (p_WORKITEM_INSTANCE_ID
2979 ,'ENDING_NUMBER'
2980 );
2981
2982 l_DONOR_SP_CODE :=
2983 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
2984 (p_WORKITEM_INSTANCE_ID
2985 ,'DONOR_SP_ID'
2986 );
2987
2988 -- Get the SP id for this SP code
2989 XNP_CORE.GET_SP_ID
2990 (l_DONOR_SP_CODE
2991 ,l_DONOR_SP_ID
2992 ,x_ERROR_CODE
2993 ,x_ERROR_MESSAGE
2994 );
2995
2996 -- check the status as donor for this number range
2997 XNP_CORE.CHECK_DONOR_STATUS_EXISTS
2998 (p_STARTING_NUMBER =>l_starting_number
2999 ,p_ENDING_NUMBER =>l_ending_number
3000 ,p_STATUS_TYPE_CODE => p_status_to_check_with
3001 ,p_DONOR_SP_ID => l_donor_sp_id
3002 ,x_CHECK_STATUS =>x_check_status
3003 ,x_error_code =>x_error_code
3004 ,x_error_message =>x_error_message
3005 );
3006
3007 IF x_ERROR_CODE <> 0
3008 THEN
3009 RETURN;
3010 END IF;
3011
3012 EXCEPTION
3013 WHEN OTHERS THEN
3014 -- Grab the error message and error no.
3015 x_error_code := SQLCODE;
3016
3017 fnd_message.set_name('XNP','STD_ERROR');
3018 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_CHECK_DON_STATUS_EXISTS');
3019 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3020 x_error_message := fnd_message.get;
3021 END SOA_CHECK_DON_STATUS_EXISTS;
3022
3023 ------------------------------------------------------------------
3024 -- Calls xnp_core.check_recipient_status_exists
3025 -- to check if there exists a porting record
3026 -- for the number range and created by the donor
3027 -- with the given status type code
3028 -- Mandatory WI Parameters: STARTING_NUMBER,ENDING_NUMBER,RECIPIENT_SP_ID
3029 ------------------------------------------------------------------
3030 PROCEDURE SOA_CHECK_REC_STATUS_EXISTS
3031 (p_WORKITEM_INSTANCE_ID IN NUMBER
3032 ,P_STATUS_TO_CHECK_WITH IN VARCHAR2
3033 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
3034 ,x_ERROR_CODE OUT NOCOPY NUMBER
3035 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3036 )
3037 IS
3038 l_starting_number varchar2(80) := null;
3039 l_ending_number varchar2(80) := null;
3040 l_recipient_sp_code varchar2(80) := null;
3041 l_recipient_sp_id number := 0;
3042
3043 BEGIN
3044
3045 l_STARTING_NUMBER :=
3046 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3047 (p_WORKITEM_INSTANCE_ID
3048 ,'STARTING_NUMBER'
3049 );
3050
3051 l_ENDING_NUMBER :=
3052 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3053 (p_WORKITEM_INSTANCE_ID
3054 ,'ENDING_NUMBER'
3055 );
3056
3057 l_RECIPIENT_SP_CODE :=
3058 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3059 (p_WORKITEM_INSTANCE_ID
3060 ,'RECIPIENT_SP_ID'
3061 );
3062
3063 -- Get the SP id for this SP code
3064 XNP_CORE.GET_SP_ID
3065 (l_RECIPIENT_SP_CODE
3066 ,l_RECIPIENT_SP_ID
3067 ,x_ERROR_CODE
3068 ,x_ERROR_MESSAGE
3069 );
3070
3071 -- check the status as donor for this number range
3072 XNP_CORE.CHECK_RECIPIENT_STATUS_EXISTS
3073 (p_STARTING_NUMBER =>l_starting_number
3074 ,p_ENDING_NUMBER =>l_ending_number
3075 ,p_STATUS_TYPE_CODE => p_status_to_check_with
3076 ,p_RECIPIENT_SP_ID => l_recipient_sp_id
3077 ,x_CHECK_STATUS =>x_check_status
3078 ,x_error_code =>x_error_code
3079 ,x_error_message =>x_error_message
3080 );
3081
3082 IF x_ERROR_CODE <> 0 THEN
3083 RETURN;
3084 END IF;
3085
3086 EXCEPTION
3087 WHEN OTHERS THEN
3088 -- Grab the error message and error no.
3089 x_error_code := SQLCODE;
3090
3091 fnd_message.set_name('XNP','STD_ERROR');
3092 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_CHECK_REC_STATUS_EXISTS');
3093 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3094 x_error_message := fnd_message.get;
3095 END SOA_CHECK_REC_STATUS_EXISTS;
3096
3097 ------------------------------------------------------------------
3098 -- Called when: need to update the SV status according
3099 -- to the activity parameter SV_STATUS
3100 -- Gets the Item Attributes WORKITEM_INSTANCE
3101 -- Calls XNP_CORE.SOA_UPDATE_SV_STATUS
3102 -- Description: Procedure to update the status of
3103 -- the Porting Order Records to the new status
3104 -- for the given PORTING_ID
3105 -- (a.k.a OBJECT_REFERENCE) and
3106 -- belonging to the (local) SP ID.
3107 ------------------------------------------------------------------
3108 PROCEDURE SOA_UPDATE_SV_STATUS
3109 ( p_ORDER_ID IN NUMBER,
3110 p_LINEITEM_ID IN NUMBER,
3111 p_WORKITEM_INSTANCE_ID IN NUMBER,
3112 p_FA_INSTANCE_ID IN NUMBER,
3113 p_NEW_STATUS_TYPE_CODE VARCHAR2,
3114 p_STATUS_CHANGE_CAUSE_CODE VARCHAR2,
3115 x_ERROR_CODE OUT NOCOPY NUMBER,
3116 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3117 )
3118 IS
3119 l_porting_id VARCHAR2(80) := NULL;
3120 l_starting_number VARCHAR2(80) := NULL;
3121 l_ending_number VARCHAR2(80) := NULL;
3122 l_cur_status_type_code VARCHAR2(80) := NULL;
3123 l_new_phase_indicator VARCHAR2(80) := NULL;
3124 l_LOCAL_SP_ID NUMBER := 0;
3125 l_SP_NAME VARCHAR2(40) := NULL;
3126
3127
3128 CURSOR c_CUR_STATUS IS
3129 SELECT status_type_code
3130 FROM xnp_sv_soa
3131 WHERE object_reference = l_porting_id;
3132
3133 BEGIN
3134 x_error_code := 0;
3135
3136 l_porting_id :=
3137 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3138 (p_WORKITEM_INSTANCE_ID
3139 ,'PORTING_ID'
3140 );
3141
3142 l_starting_number :=
3143 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3144 (p_WORKITEM_INSTANCE_ID
3145 ,'STARTING_NUMBER'
3146 );
3147
3148 l_ENDING_NUMBER :=
3149 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3150 (p_WORKITEM_INSTANCE_ID
3151 ,'ENDING_NUMBER'
3152 );
3153
3154 l_SP_NAME :=
3155 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3156 (p_WORKITEM_INSTANCE_ID
3157 ,'SP_NAME'
3158 );
3159
3160 -- Get the phase of new status being set
3161 XNP_CORE.GET_PHASE_FOR_STATUS
3162 (p_NEW_STATUS_TYPE_CODE
3163 ,l_NEW_PHASE_INDICATOR
3164 ,x_ERROR_CODE
3165 ,x_ERROR_MESSAGE
3166 );
3167 IF x_ERROR_CODE <> 0
3168 THEN
3169 RETURN;
3170 END IF;
3171
3172 ------------------------------------------------------------------
3173 -- If the new status is 'ACTIVE' (for example)
3174 -- Ensure that no other SV exists in Active state
3175 ------------------------------------------------------------------
3176 IF l_NEW_PHASE_INDICATOR = 'ACTIVE'
3177 THEN
3178
3179 OPEN c_CUR_STATUS;
3180 FETCH c_CUR_STATUS INTO l_cur_status_type_code;
3181
3182 IF c_CUR_STATUS%FOUND THEN
3183
3184 XNP_CORE.SOA_RESET_SV_STATUS
3185 (p_STARTING_NUMBER => l_starting_number
3186 ,p_ENDING_NUMBER => l_ending_number
3187 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
3188 ,p_CUR_PHASE_INDICATOR => l_NEW_PHASE_INDICATOR
3189 ,p_RESET_PHASE_INDICATOR => 'OLD'
3190 ,p_OMIT_STATUS => l_CUR_STATUS_TYPE_CODE
3191 ,P_STATUS_CHANGE_CAUSE_CODE => 'Reset to OLD'
3192 ,p_ORDER_ID => p_ORDER_ID
3193 ,p_LINEITEM_ID => p_LINEITEM_ID
3194 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
3195 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
3196 ,X_ERROR_CODE => x_ERROR_CODE
3197 ,X_ERROR_MESSAGE => x_ERROR_MESSAGE
3198 );
3199
3200 END IF;
3201 END IF;
3202
3203 -- call updation procedure to update the status
3204
3205 XNP_CORE.SOA_UPDATE_SV_STATUS
3206 (p_PORTING_ID => l_porting_id
3207 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
3208 ,P_NEW_STATUS_TYPE_CODE => p_new_status_type_code
3209 ,P_STATUS_CHANGE_CAUSE_CODE => p_status_change_cause_code
3210 ,p_ORDER_ID => p_ORDER_ID
3211 ,p_LINEITEM_ID => p_LINEITEM_ID
3212 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
3213 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
3214 ,X_ERROR_CODE => x_ERROR_CODE
3215 ,X_ERROR_MESSAGE => x_ERROR_MESSAGE
3216 );
3217
3218 EXCEPTION
3219 WHEN OTHERS THEN
3220 -- Grab the error message and error no.
3221 x_error_code := SQLCODE;
3222
3223 fnd_message.set_name('XNP','STD_ERROR');
3224 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_SV_STATUS');
3225 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3226 x_error_message := fnd_message.get;
3227
3228 END SOA_UPDATE_SV_STATUS;
3229
3230 ------------------------------------------------------------------
3231 -- Updates the Notes info
3232 -- Mandatory WI Params: PORTING_ID
3233 -- Optional WI Params: COMMENTS,NOTES,PREORDER_AUTHORIZATION_CODE
3234 ------------------------------------------------------------------
3235 PROCEDURE SOA_UPDATE_NOTES_INFO
3236 ( p_ORDER_ID IN NUMBER,
3237 p_LINEITEM_ID IN NUMBER,
3238 p_WORKITEM_INSTANCE_ID IN NUMBER,
3239 p_FA_INSTANCE_ID IN NUMBER,
3240 x_ERROR_CODE OUT NOCOPY NUMBER,
3241 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3242 )
3243 IS
3244 l_PORTING_ID VARCHAR2(40) := null;
3245 l_COMMENTS VARCHAR2(2000);
3246 l_NOTES VARCHAR2(2000);
3247 l_PREORDER_AUTHORIZATION_CODE VARCHAR2(40) := NULL;
3248 l_SP_NAME VARCHAR2(80) := NULL;
3249 l_LOCAL_SP_ID NUMBER := 0;
3250
3251 BEGIN
3252
3253 x_error_code := 0;
3254
3255 l_PORTING_ID :=
3256 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3257 (p_WORKITEM_INSTANCE_ID
3258 ,'PORTING_ID'
3259 );
3260
3261 l_COMMENTS :=
3262 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3263 (p_WORKITEM_INSTANCE_ID
3264 ,'COMMENTS'
3265 );
3266
3267 l_NOTES :=
3268 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3269 (p_WORKITEM_INSTANCE_ID
3270 ,'NOTES'
3271 );
3272
3273 l_PREORDER_AUTHORIZATION_CODE :=
3274 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3275 (p_WORKITEM_INSTANCE_ID
3276 ,'PREORDER_AUTHORIZATION_CODE'
3277 );
3278
3279
3280 l_SP_NAME :=
3281 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3282 (p_WORKITEM_INSTANCE_ID
3283 ,'SP_NAME'
3284 );
3285
3286 XNP_CORE.SOA_UPDATE_NOTES_INFO
3287 (p_PORTING_ID => l_PORTING_ID
3288 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
3289 ,p_PREORDER_AUTHORIZATION_CODE=> l_PREORDER_AUTHORIZATION_CODE
3290 ,p_COMMENTS => l_COMMENTS
3291 ,p_NOTES => l_NOTES
3292 ,p_ORDER_ID => p_ORDER_ID
3293 ,p_LINEITEM_ID => p_LINEITEM_ID
3294 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
3295 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
3296 ,x_ERROR_CODE => x_ERROR_CODE
3297 ,x_ERROR_MESSAGE => x_ERROR_MESSAGE
3298 );
3299
3300 IF (x_error_code <> 0) THEN
3301 return;
3302 END IF;
3303
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 -- Grab the error message and error no.
3307 x_error_code := SQLCODE;
3308
3309 fnd_message.set_name('XNP','STD_ERROR');
3310 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_NOTES_INFO');
3311 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3312 x_error_message := fnd_message.get;
3313
3314 END SOA_UPDATE_NOTES_INFO;
3315
3316
3317 ------------------------------------------------------------------
3318 -- Updates the Network information for the given
3319 -- Porting id in XNP_SV_SOA
3320 -- Mandatory WI parameter: ROUTING_NUMBER
3321 -- Optional WI Params: ROUTING_NUMBER,
3322 -- CNAM_ADDRESS, CNAM_SUBSYSTEM, ISVM_ADDRESS
3323 -- ISVM_SUBSYSTEM, LIDB_ADDRESS, LIDB_SUBSYSTEM
3324 -- CLASS_ADDRESS,CLASS_SUBSYSTEM,WSMSC_ADDRESS,
3325 -- WSMSC_SUBSYSTEM, RN_ADDRESS, RN_SUBSYSTEM
3326 ------------------------------------------------------------------
3327 PROCEDURE SOA_UPDATE_NETWORK_INFO
3328 ( p_ORDER_ID IN NUMBER,
3329 p_LINEITEM_ID IN NUMBER,
3330 p_WORKITEM_INSTANCE_ID IN NUMBER,
3331 p_FA_INSTANCE_ID IN NUMBER,
3332 x_ERROR_CODE OUT NOCOPY NUMBER,
3333 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3334 )
3335 IS
3336 l_LOCAL_SP_ID NUMBER := 0;
3337 l_ROUTING_NUMBER_ID NUMBER := 0;
3338 l_ROUTING_NUMBER VARCHAR2(40);
3339 l_PORTING_ID VARCHAR2(40) := null;
3340 l_CNAM_ADDRESS VARCHAR2(80) := NULL;
3341 l_CNAM_SUBSYSTEM VARCHAR2(80) := NULL;
3342 l_ISVM_ADDRESS VARCHAR2(80) := NULL;
3343 l_ISVM_SUBSYSTEM VARCHAR2(80) := NULL;
3344 l_LIDB_ADDRESS VARCHAR2(80) := NULL;
3345 l_LIDB_SUBSYSTEM VARCHAR2(80) := NULL;
3346 l_CLASS_ADDRESS VARCHAR2(80) := NULL;
3347 l_CLASS_SUBSYSTEM VARCHAR2(80) := NULL;
3348 l_WSMSC_ADDRESS VARCHAR2(80) := NULL;
3349 l_WSMSC_SUBSYSTEM VARCHAR2(80) := NULL;
3350 l_RN_ADDRESS VARCHAR2(80) := NULL;
3351 l_RN_SUBSYSTEM VARCHAR2(80) := NULL;
3352 l_SP_NAME VARCHAR2(80) := NULL;
3353
3354 BEGIN
3355
3356 x_error_code := 0;
3357
3358 l_PORTING_ID :=
3359 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3360 (p_WORKITEM_INSTANCE_ID
3361 ,'PORTING_ID'
3362 );
3363
3364 l_CNAM_ADDRESS :=
3365 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3366 (p_WORKITEM_INSTANCE_ID
3367 ,'CNAM_ADDRESS'
3368 );
3369
3370 l_CNAM_SUBSYSTEM :=
3371 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3372 (p_WORKITEM_INSTANCE_ID
3373 ,'CNAM_SUBSYSTEM'
3374 );
3375
3376 l_ISVM_ADDRESS :=
3377 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3378 (p_WORKITEM_INSTANCE_ID
3379 ,'ISVM_ADDRESS'
3380 );
3381
3382 l_ISVM_SUBSYSTEM :=
3383 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3384 (p_WORKITEM_INSTANCE_ID
3385 ,'ISVM_SUBSYSTEM'
3386 );
3387
3388 l_LIDB_ADDRESS :=
3389 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3390 (p_WORKITEM_INSTANCE_ID
3391 ,'LIDB_ADDRESS'
3392 );
3393
3394 l_LIDB_SUBSYSTEM :=
3395 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3396 (p_WORKITEM_INSTANCE_ID
3397 ,'LIDB_SUBSYSTEM'
3398 );
3399
3400 l_CLASS_ADDRESS :=
3401 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3402 (p_WORKITEM_INSTANCE_ID
3403 ,'CLASS_ADDRESS'
3404 );
3405
3406 l_CLASS_SUBSYSTEM :=
3407 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3408 (p_WORKITEM_INSTANCE_ID
3409 ,'CLASS_SUBSYSTEM'
3410 );
3411
3412 l_WSMSC_ADDRESS :=
3413 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3414 (p_WORKITEM_INSTANCE_ID
3415 ,'WSMSC_ADDRESS'
3416 );
3417
3418 l_WSMSC_SUBSYSTEM :=
3419 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3420 (p_WORKITEM_INSTANCE_ID
3421 ,'WSMSC_SUBSYSTEM'
3422 );
3423
3424 l_RN_ADDRESS :=
3425 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3426 (p_WORKITEM_INSTANCE_ID
3427 ,'RN_ADDRESS'
3428 );
3429
3430 l_RN_SUBSYSTEM :=
3431 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3432 (p_WORKITEM_INSTANCE_ID
3433 ,'RN_SUBSYSTEM'
3434 );
3435
3436 -- Get the routing number id
3437 l_ROUTING_NUMBER :=
3438 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3439 (p_WORKITEM_INSTANCE_ID
3440 ,'ROUTING_NUMBER'
3441 );
3442
3443 -- Get the routing_number_id corresponding to the code
3444 XNP_CORE.GET_ROUTING_NUMBER_ID
3445 (l_routing_number
3446 ,l_ROUTING_NUMBER_ID
3447 ,x_ERROR_CODE
3448 ,x_ERROR_MESSAGE
3449 );
3450
3451 IF x_ERROR_CODE <> 0
3452 THEN
3453 RETURN;
3454 END IF;
3455
3456
3457 l_SP_NAME :=
3458 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3459 (p_WORKITEM_INSTANCE_ID
3460 ,'SP_NAME'
3461 );
3462
3463 XNP_CORE.SOA_UPDATE_NETWORK_INFO
3464 (p_porting_id => l_porting_id
3465 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
3466 ,p_ROUTING_NUMBER_ID => l_ROUTING_NUMBER_ID
3467 ,p_CNAM_ADDRESS => l_CNAM_ADDRESS
3468 ,p_CNAM_SUBSYSTEM => l_CNAM_SUBSYSTEM
3469 ,p_ISVM_ADDRESS => l_ISVM_ADDRESS
3470 ,p_ISVM_SUBSYSTEM => l_ISVM_SUBSYSTEM
3471 ,p_LIDB_ADDRESS => l_LIDB_ADDRESS
3472 ,p_LIDB_SUBSYSTEM => l_LIDB_SUBSYSTEM
3473 ,p_CLASS_ADDRESS => l_CLASS_ADDRESS
3474 ,p_CLASS_SUBSYSTEM => l_CLASS_SUBSYSTEM
3475 ,p_WSMSC_ADDRESS => l_WSMSC_ADDRESS
3476 ,p_WSMSC_SUBSYSTEM => l_WSMSC_SUBSYSTEM
3477 ,p_RN_ADDRESS => l_RN_ADDRESS
3478 ,p_RN_SUBSYSTEM => l_RN_SUBSYSTEM
3479 ,p_ORDER_ID => p_ORDER_ID
3480 ,p_LINEITEM_ID => p_LINEITEM_ID
3481 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
3482 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
3483 ,x_ERROR_CODE => x_ERROR_CODE
3484 ,x_ERROR_MESSAGE => x_ERROR_MESSAGE
3485 );
3486
3487 IF (x_error_code <> 0) THEN
3488 return;
3489 END IF;
3490
3491 EXCEPTION
3492 WHEN OTHERS THEN
3493 -- Grab the error message and error no.
3494 x_error_code := SQLCODE;
3495 fnd_message.set_name('XNP','STD_ERROR');
3496 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_NETWORK_INFO');
3497 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3498 x_error_message := fnd_message.get;
3499
3500 END SOA_UPDATE_NETWORK_INFO;
3501
3502 ------------------------------------------------------------------
3503 -- Updates the Customer information for the Porting id
3504 -- Mandatory WI Params: PORTING_ID
3505 -- Optional WI Params: PAGER, PAGER_PIN,INTERNET_ADDRESS, CUSTOMER_ID,
3506 -- CUSTOMER_NAME,ADDRESS_LINE1,CITY,PHONE,FAX,EMAIL,ZIP_CODE,
3507 -- CUSTOMER_CONTACT_REQ_FLAG,CONTACT_NAME
3508 ------------------------------------------------------------------
3509 PROCEDURE SOA_UPDATE_CUSTOMER_INFO
3510 ( p_ORDER_ID IN NUMBER,
3511 p_LINEITEM_ID IN NUMBER,
3512 p_WORKITEM_INSTANCE_ID IN NUMBER,
3513 p_FA_INSTANCE_ID IN NUMBER,
3514 x_ERROR_CODE OUT NOCOPY NUMBER,
3515 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3516 )
3517 IS
3518 l_PORTING_ID VARCHAR2(40) := null;
3519 l_CUSTOMER_ID VARCHAR2(30);
3520 l_CUSTOMER_NAME VARCHAR2(80);
3521 l_CUSTOMER_TYPE VARCHAR2(10);
3522 l_ADDRESS_LINE1 VARCHAR2(400);
3523 l_ADDRESS_LINE2 VARCHAR2(400);
3524 l_CITY VARCHAR2(40);
3525 l_PHONE VARCHAR2(40);
3526 l_FAX VARCHAR2(40);
3527 l_EMAIL VARCHAR2(40);
3528 l_ZIP_CODE VARCHAR2(40);
3529 l_COUNTRY VARCHAR2(40);
3530 l_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2(3);
3531 l_CONTACT_REQUESTED VARCHAR2(5);
3532 l_CONTACT_NAME VARCHAR2(40) := NULL;
3533 l_PAGER VARCHAR2(20) := NULL;
3534 l_PAGER_PIN VARCHAR2(80) := NULL;
3535 l_INTERNET_ADDRESS VARCHAR2(40) := NULL;
3536 l_SP_NAME VARCHAR2(80) := NULL;
3537 l_LOCAL_SP_ID NUMBER := 0;
3538
3539 BEGIN
3540
3541 x_error_code := 0;
3542
3543 l_PORTING_ID :=
3544 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3545 (p_WORKITEM_INSTANCE_ID
3546 ,'PORTING_ID'
3547 );
3548
3549 l_CUSTOMER_ID :=
3550 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3551 (p_WORKITEM_INSTANCE_ID
3552 ,'CUSTOMER_ID'
3553 );
3554
3555 l_CUSTOMER_NAME :=
3556 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3557 (p_WORKITEM_INSTANCE_ID
3558 ,'CUSTOMER_NAME'
3559 );
3560
3561 l_CUSTOMER_TYPE :=
3562 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3563 (p_WORKITEM_INSTANCE_ID
3564 ,'CUSTOMER_TYPE'
3565 );
3566
3567 l_ADDRESS_LINE1 :=
3568 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3569 (p_WORKITEM_INSTANCE_ID
3570 ,'ADDRESS_LINE1'
3571 );
3572
3573 l_ADDRESS_LINE2 :=
3574 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3575 (p_WORKITEM_INSTANCE_ID
3576 ,'ADDRESS_LINE2'
3577 );
3578
3579 l_CITY :=
3580 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3581 (p_WORKITEM_INSTANCE_ID
3582 ,'CITY'
3583 );
3584
3585 l_PHONE :=
3586 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3587 (p_WORKITEM_INSTANCE_ID
3588 ,'PHONE'
3589 );
3590
3591 l_FAX :=
3592 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3593 (p_WORKITEM_INSTANCE_ID
3594 ,'FAX'
3595 );
3596
3597 l_EMAIL :=
3598 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3599 (p_WORKITEM_INSTANCE_ID
3600 ,'EMAIL'
3601 );
3602
3603 l_ZIP_CODE :=
3604 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3605 (p_WORKITEM_INSTANCE_ID
3606 ,'ZIP_CODE'
3607 );
3608
3609 l_COUNTRY :=
3610 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3611 (p_WORKITEM_INSTANCE_ID
3612 ,'COUNTRY'
3613 );
3614
3615 l_CUSTOMER_CONTACT_REQ_FLAG :=
3616 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3617 (p_WORKITEM_INSTANCE_ID
3618 ,'CUSTOMER_CONTACT_REQ_FLAG'
3619 );
3620
3621 l_CONTACT_NAME :=
3622 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3623 (p_WORKITEM_INSTANCE_ID
3624 ,'CONTACT_NAME'
3625 );
3626
3627
3628 l_PAGER :=
3629 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3630 (p_WORKITEM_INSTANCE_ID
3631 ,'PAGER'
3632 );
3633
3634 l_PAGER_PIN :=
3635 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3636 (p_WORKITEM_INSTANCE_ID
3637 ,'PAGER_PIN'
3638 );
3639
3640 l_INTERNET_ADDRESS :=
3641 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3642 (p_WORKITEM_INSTANCE_ID
3643 ,'INTERNET_ADDRESS'
3644 );
3645
3646
3647 l_SP_NAME :=
3648 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
3649 (p_WORKITEM_INSTANCE_ID
3650 ,'SP_NAME'
3651 );
3652
3653 XNP_CORE.SOA_UPDATE_CUSTOMER_INFO
3654 (p_PORTING_ID => l_PORTING_ID
3655 ,p_LOCAL_SP_ID => l_LOCAL_SP_ID
3656 ,p_CUSTOMER_ID => l_CUSTOMER_ID
3657 ,p_CUSTOMER_NAME => l_CUSTOMER_NAME
3658 ,p_CUSTOMER_TYPE => l_CUSTOMER_TYPE
3659 ,p_ADDRESS_LINE1 => l_ADDRESS_LINE1
3660 ,p_ADDRESS_LINE2 => l_ADDRESS_LINE2
3661 ,p_CITY => l_CITY
3662 ,p_PHONE => l_PHONE
3663 ,p_FAX => l_FAX
3664 ,p_EMAIL => l_EMAIL
3665 ,p_PAGER => l_PAGER
3666 ,p_PAGER_PIN => l_PAGER_PIN
3667 ,p_INTERNET_ADDRESS => l_INTERNET_ADDRESS
3668 ,p_ZIP_CODE => l_ZIP_CODE
3669 ,p_COUNTRY => l_COUNTRY
3670 ,p_CUSTOMER_CONTACT_REQ_FLAG => l_CUSTOMER_CONTACT_REQ_FLAG
3671 ,p_CONTACT_NAME => l_CONTACT_NAME
3672 ,p_ORDER_ID => p_ORDER_ID
3673 ,p_LINEITEM_ID => p_LINEITEM_ID
3674 ,p_WORKITEM_INSTANCE_ID => p_WORKITEM_INSTANCE_ID
3675 ,p_FA_INSTANCE_ID => p_FA_INSTANCE_ID
3676 ,x_ERROR_CODE => x_ERROR_CODE
3677 ,x_ERROR_MESSAGE => x_ERROR_MESSAGE
3678 );
3679
3680 IF (x_error_code <> 0) THEN
3681 return;
3682 END IF;
3683
3684 EXCEPTION
3685 WHEN OTHERS THEN
3686 -- Grab the error message and error no.
3687 x_error_code := SQLCODE;
3688 fnd_message.set_name('XNP','STD_ERROR');
3689 fnd_message.set_token('ERROR_LOCN','XNP_STANDARD.SOA_UPDATE_CUSTOMER_INFO');
3690 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3691 x_error_message := fnd_message.get;
3692
3693 END SOA_UPDATE_CUSTOMER_INFO;
3694
3695 ----------------------------------------------------------------------
3696 -- Runtime Validation check for NP workitem.
3697 -- Calls XNP_CORE.RUNTIME_VALIDATION
3698 -- Optional WI Params: STARTING_NUMBER,ENDING_NUMBER,ROUTING_NUMBER,
3699 -- DONOR_SP_CODE,RECIPIENT_SP_CODE
3700 ---------------------------------------------------------------------
3701 PROCEDURE RUNTIME_VALIDATION
3702 ( p_ORDER_ID IN NUMBER
3703 ,p_LINE_ITEM_ID IN NUMBER
3704 ,p_WORKITEM_INSTANCE_ID IN NUMBER
3705 ,x_ERROR_CODE OUT NOCOPY NUMBER
3706 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
3707 )
3708 IS
3709 l_starting_number varchar2(80) := null;
3710 l_ending_number varchar2(80) := null;
3711 l_donor_sp_code varchar2(80) := null;
3712 l_recipient_sp_code varchar2(80):= null;
3713 l_routing_number varchar2(40):= null;
3714
3715 BEGIN
3716
3717 x_ERROR_CODE:=0;
3718
3719 l_starting_number :=
3720 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3721 (p_WORKITEM_INSTANCE_ID
3722 ,'STARTING_NUMBER'
3723 );
3724
3725 l_ending_number :=
3726 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3727 (p_WORKITEM_INSTANCE_ID
3728 ,'ENDING_NUMBER'
3729 );
3730
3731 l_donor_sp_code :=
3732 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3733 (p_WORKITEM_INSTANCE_ID
3734 ,'DONOR_SP_ID'
3735 );
3736
3737 l_recipient_sp_code :=
3738 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3739 (p_WORKITEM_INSTANCE_ID
3740 ,'RECIPIENT_SP_ID'
3741 );
3742
3743 l_routing_number :=
3744 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
3745 (p_WORKITEM_INSTANCE_ID
3746 ,'ROUTING_NUMBER'
3747 );
3748
3749 XNP_CORE.RUNTIME_VALIDATION
3750 (p_ORDER_ID =>p_ORDER_ID
3751 ,p_LINE_ITEM_ID =>p_LINE_ITEM_ID
3752 ,p_WORKITEM_INSTANCE_ID=>p_WORKITEM_INSTANCE_ID
3753 ,p_STARTING_NUMBER=>l_starting_number
3754 ,p_ENDING_NUMBER=>l_ending_number
3755 ,p_ROUTING_NUMBER=>l_routing_number
3756 ,p_DONOR_SP_CODE=>l_donor_sp_code
3757 ,p_RECIPIENT_SP_CODE=>l_recipient_sp_code
3758 ,x_ERROR_CODE=>x_ERROR_CODE
3759 ,x_ERROR_MESSAGE=>x_ERROR_MESSAGE
3760 );
3761
3762 EXCEPTION
3763 WHEN OTHERS THEN
3764 -- Grab the error message and error no.
3765 x_error_code := SQLCODE;
3766
3767 fnd_message.set_name('XNP','STD_ERROR');
3768 fnd_message.set_token('ERROR_LOCN' ,'XNP_STANDARD.RUNTIME_VALIDATION');
3769 fnd_message.set_token('ERROR_TEXT',SQLERRM);
3770 x_error_message := fnd_message.get;
3771
3772
3773 END RUNTIME_VALIDATION;
3774
3775
3776 PROCEDURE DEREGISTER_ALL
3777 ( p_order_id IN NUMBER
3778 ,x_error_code OUT NOCOPY NUMBER
3779 ,x_error_message OUT NOCOPY VARCHAR2
3780 )
3781
3782 IS
3783
3784 BEGIN
3785
3786 x_error_code := 0;
3787 xnp_timer_standard.deregister(p_order_id => DEREGISTER_ALL.p_order_id,
3788 x_error_code => DEREGISTER_ALL.x_error_code,
3789 x_error_message => DEREGISTER_ALL.x_error_message);
3790
3791 if (x_error_code <> 0 ) then
3792 return;
3793 end if;
3794
3795 x_error_code := 0;
3796 xnp_event.deregister(p_order_id => DEREGISTER_ALL.p_order_id,
3797 x_error_code => DEREGISTER_ALL.x_error_code,
3798 x_error_message => DEREGISTER_ALL.x_error_message);
3799
3800 if (x_error_code <> 0 ) then
3801 return;
3802 end if;
3803
3804 END DEREGISTER_ALL;
3805
3806 END XNP_STANDARD;