[Home] [Help]
PACKAGE BODY: APPS.XNP_UTILS
Source
1 PACKAGE BODY XNP_UTILS AS
2 /* $Header: XNPUTILB.pls 120.1 2005/06/17 03:44:45 appldev $ */
3
4 g_new_line CONSTANT VARCHAR2(10) := convert(fnd_global.local_chr(10),
5 substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
6 'WE8ISO8859P1') ;
7 ----------------------------------------------------------------------
8 ----*** Procedure: MSG_TO_ORDER()
9 ----*** Purpose: Retrieves order parameters from a flat XML message.
10 ----------------------------------------------------------------------
11
12 PROCEDURE MSG_TO_ORDER
13 (P_MSG_TEXT IN VARCHAR2
14 ,P_WI_NAME IN VARCHAR2
15 ,X_LINE_PARAM_LIST OUT NOCOPY XDP_TYPES.LINE_PARAM_LIST
16 ,X_ORDER_LINE_LIST OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST
17 ,X_ERROR_CODE OUT NOCOPY NUMBER
18 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
19 )
20 IS
21 l_value VARCHAR2(16000) ;
22 l_index NUMBER ;
23 l_starting_number VARCHAR2(80) := null;
24 l_ending_number VARCHAR2(80) := null;
25 e_MISSING_MANDATORY_DATA EXCEPTION ;
26
27 CURSOR get_wi_params IS
28 SELECT wip.parameter_name name,
29 wip.required_flag mandatory_flag
30 FROM xdp_workitems wis,
31 xdp_wi_parameters wip
32 WHERE wis.workitem_name = p_wi_name
33 AND wis.workitem_id = wip.workitem_id;
34
35
36 BEGIN
37
38 x_order_line_list(1).line_number := 1 ;
39 x_order_line_list(1).line_item_name := p_wi_name ;
40 x_order_line_list(1).action := NULL ;
41 x_order_line_list(1).IS_WORKITEM_FLAG := 'Y';
42 x_order_line_list(1).provisioning_required_flag := 'Y';
43
44 XNP_XML_UTILS.DECODE(p_msg_text, 'STARTING_NUMBER', l_starting_number) ;
45 x_order_line_list(1).starting_number := l_starting_number ;
46
47 XNP_XML_UTILS.DECODE(p_msg_text, 'ENDING_NUMBER', l_ending_number) ;
48 x_order_line_list(1).ending_number := l_ending_number ;
49
50 l_index := 1 ;
51
52 -- First swap the reference id and opposite refernce id
53
54 XNP_XML_UTILS.DECODE(p_msg_text, 'REFERENCE_ID', l_value) ;
55 x_line_param_list(l_index).line_number := 1;
56 x_line_param_list(l_index).parameter_name := 'OPP_REFERENCE_ID';
57 x_line_param_list(l_index).parameter_value := l_value ;
58 l_index := l_index + 1;
59
60 XNP_XML_UTILS.DECODE(p_msg_text, 'OPP_REFERENCE_ID', l_value) ;
61 x_line_param_list(l_index).line_number := 1;
62 x_line_param_list(l_index).parameter_name := 'REFERENCE_ID';
63 x_line_param_list(l_index).parameter_value := l_value ;
64 l_index := l_index + 1;
65
66
67 -- Set the SP_NAME same as the RECIPIENT_NAME
68
69 XNP_XML_UTILS.DECODE(p_msg_text, 'RECIPIENT_NAME', l_value) ;
70 x_line_param_list(l_index).line_number := 1;
71 x_line_param_list(l_index).parameter_name := 'SP_NAME';
72 x_line_param_list(l_index).parameter_value := l_value ;
73
74 l_index := l_index + 1;
75
76 FOR wi_param IN get_wi_params LOOP
77
78 IF ( (wi_param.name <> 'REFERENCE_ID')
79 AND (wi_param.name <> 'OPP_REFERENCE_ID')
80 AND (wi_param.name <> 'SP_NAME') )
81 THEN
82 XNP_XML_UTILS.DECODE(p_msg_text, wi_param.name, l_value) ;
83
84 IF (wi_param.mandatory_flag = 'Y') THEN
85 IF (l_value IS NULL) THEN
86 x_error_message := 'Missing Mandatory Parameter - ' ||
87 wi_param.name ;
88 RAISE e_MISSING_MANDATORY_DATA ;
89 END IF ;
90 END IF ;
91
92 x_line_param_list(l_index).line_number := 1;
93 x_line_param_list(l_index).parameter_name := wi_param.name ;
94 x_line_param_list(l_index).parameter_value := l_value ;
95
96 l_index := l_index + 1 ;
97 END IF; -- check for reference and opp reference id
98
99 END LOOP ;
100
101 EXCEPTION
102 WHEN e_MISSING_MANDATORY_DATA THEN
103 x_error_code := XNP_ERRORS.G_MISSING_MANDATORY_DATA ;
104 WHEN OTHERS THEN
105 x_error_code := SQLCODE ;
106 x_error_message := SQLERRM ;
107
108 END MSG_TO_ORDER ;
109
110 ----------------------------------------------------------------------
111 ----*** Function: GET_GEO_INFO ()
112 ----*** Purpose: gets the geographic name for the given porting ID
113 ----------------------------------------------------------------------
114
115 FUNCTION GET_GEO_INFO ( P_PORTING_ID IN VARCHAR2 )
116 RETURN VARCHAR2
117 IS
118 l_subscription_tn VARCHAR2(10) ;
119 l_geo_area_name XNP_GEO_AREAS_B.CODE%TYPE ;
120
121 CURSOR get_geo_area (subscription_tn IN VARCHAR2) IS
122 SELECT geo.code FROM xnp_geo_areas_b geo
123 WHERE geo.geo_area_id IN (SELECT nre.geo_area_id
124 FROM xnp_number_ranges nre
125 WHERE subscription_tn BETWEEN
126 starting_number AND ending_number );
127 CURSOR get_tn is
128 SELECT subscription_tn
129 FROM XNP_SV_SOA
130 WHERE object_reference = p_porting_id ;
131
132 BEGIN
133
134 OPEN get_tn;
135 FETCH get_tn INTO l_subscription_tn;
136 CLOSE get_tn;
137
138 OPEN get_geo_area(l_subscription_tn) ;
139 FETCH get_geo_area INTO l_geo_area_name ;
140 CLOSE get_geo_area;
141
142 RETURN (l_geo_area_name) ;
143
144 END ;
145
146 ----------------------------------------------------------------------
147 ----*** Function: SEND_ACK_MSG ()
148 ----*** Purpose: Wrapper for creating an ACK message
149 ----------------------------------------------------------------------
150
151 PROCEDURE SEND_ACK_MSG (P_MSG_TO_ACK IN NUMBER
152 ,P_CODE IN NUMBER
153 ,P_DESCRIPTION IN VARCHAR2
154 ,X_ERROR_CODE OUT NOCOPY NUMBER
155 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
156 )
157 IS
158
159 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
160 l_tmp_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;
161
162 l_msg_text VARCHAR2(32767) ;
163
164 BEGIN
165
166 l_msg_text := NULL ;
167 XNP_MESSAGE.GET_HEADER(p_msg_to_ack, l_tmp_header) ;
168
169 XNP_ACK_U.create_msg (XNP$CODE=>p_code,
170 XNP$DESCRIPTION=>p_description,
171 x_msg_header=>l_msg_header,
172 x_msg_text=>l_msg_text,
173 x_error_code=>x_error_code,
174 x_error_message=>x_error_message,
175 p_reference_id=>TO_CHAR(p_msg_to_ack),
176 p_opp_reference_id=>l_tmp_header.reference_id) ;
177
178 IF (x_error_code = 0) THEN
179
180 l_msg_header.direction_indr := 'I' ;
181
182 XNP_MESSAGE.PUSH(p_msg_header=>l_msg_header,
183 p_body_text=>l_msg_text,
184 p_queue_name=>'XNP_IN_MSG_Q',
185 p_correlation_id=>TO_CHAR(p_msg_to_ack)) ;
186
187 END IF ;
188
189 END SEND_ACK_MSG ;
190
191
192 --------------------------------------------------------------------
193 -- Procedure to get the fe_name of the donor for this
194 -- transaction.
195 ------------------------------------------------------------------
196 PROCEDURE GET_DONOR_FE
197 (p_ORDER_ID IN NUMBER
198 ,p_WI_INSTANCE_ID IN NUMBER
199 ,p_FA_INSTANCE_ID IN NUMBER
200 ,x_FE_NAME OUT NOCOPY VARCHAR2
201 ,x_RETURN_CODE OUT NOCOPY NUMBER
202 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
203 )
204 IS
205 l_PORTING_ID VARCHAR2(40);
206 l_DONOR_SP_ID NUMBER := 0;
207 l_DONOR_SP_CODE VARCHAR2(40) := NULL;
208 l_error_desc varchar2(4000) := NULL;
209 BEGIN
210
211 x_RETURN_CODE := 0;
212 x_ERROR_DESCRIPTION := NULL;
213
214 ------------------------------------------------------------------ Get the donor sp id from the workitem params
215 -- directly. If not found there then get it from
216 -- the SOA table
217 ------------------------------------------------------------------
218 l_DONOR_SP_CODE :=
219 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
220 (p_WI_INSTANCE_ID
221 ,'DONOR_SP_ID'
222 );
223
224 IF l_DONOR_SP_CODE = NULL
225 THEN
226
227 -- Get the Porting ID from the WI params table
228 l_PORTING_ID :=
229 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
230 (p_WI_INSTANCE_ID
231 ,'PORTING_ID'
232 );
233
234 -- Get the donor sp id from the SOA table
235 SELECT DONOR_SP_ID INTO l_DONOR_SP_ID
236 FROM XNP_SV_SOA
237 WHERE OBJECT_REFERENCE = l_PORTING_ID;
238
239 ELSE
240
241 XNP_CORE.GET_SP_ID
242 (p_SP_NAME=>l_DONOR_SP_CODE
243 ,x_SP_ID=>l_DONOR_SP_ID
244 ,x_ERROR_CODE=>x_RETURN_CODE
245 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
246 );
247 IF x_RETURN_CODE <> 0
248 THEN
249 RETURN;
250 END IF;
251
252 END IF;
253
254 /* BUG 1500177
255 -- get the Adapter name
256 */
257 -- get the fe name
258 XNP_UTILS.GET_FE_NAME_FOR_SP
259 (p_SP_ID=>l_DONOR_SP_ID
260 ,x_FE_NAME=>x_FE_NAME
261 ,x_ERROR_CODE=>x_RETURN_CODE
262 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
263 );
264 IF x_RETURN_CODE <> 0
265 THEN
266 RETURN;
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 -- Grab the error message and error no.
272 fnd_message.set_name('XNP','GET_DONOR_FE_ERR_REASON');
273 fnd_message.set_token('PORTING_ID',l_porting_id);
274 l_error_desc := fnd_message.get;
275
276
277 x_RETURN_CODE := SQLCODE;
278 x_ERROR_DESCRIPTION := SQLERRM||l_error_desc;
279
280 END GET_DONOR_FE;
281
282 --------------------------------------------------------------------
283 -- Procedure to get the fe_name of the initial donor for this
284 -- transaction.
285 ------------------------------------------------------------------
286 PROCEDURE GET_ORIG_DONOR_FE
287 (p_ORDER_ID IN NUMBER
288 ,p_WI_INSTANCE_ID IN NUMBER
289 ,p_FA_INSTANCE_ID IN NUMBER
290 ,x_FE_NAME OUT NOCOPY VARCHAR2
291 ,x_RETURN_CODE OUT NOCOPY NUMBER
292 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
293 )
294 IS
295 l_STARTING_NUMBER VARCHAR2(40);
296 l_ENDING_NUMBER VARCHAR2(40);
297 l_ORIGINAL_DONOR_SP_ID NUMBER := 0;
298 l_DONOR_SP_CODE VARCHAR2(40) := NULL;
299 l_error_description varchar2(2000) := NULL;
300 BEGIN
301
302 x_RETURN_CODE := 0;
303 x_ERROR_DESCRIPTION := NULL;
304
305 l_STARTING_NUMBER :=
306 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
307 (p_WI_INSTANCE_ID
308 ,'STARTING_NUMBER'
309 );
310
311 l_ENDING_NUMBER :=
312 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
313 (p_WI_INSTANCE_ID
314 ,'ENDING_NUMBER'
315 );
316
317 XNP_CORE.GET_ASSIGNED_SP_ID
318 (p_STARTING_NUMBER=>l_STARTING_NUMBER
319 ,p_ENDING_NUMBER=>l_ENDING_NUMBER
320 ,x_ASSIGNED_SP_ID=>l_ORIGINAL_DONOR_SP_ID
321 ,x_ERROR_CODE=>x_RETURN_CODE
322 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
323 );
324
325 IF x_RETURN_CODE <> 0
326 THEN
327 RETURN;
328 END IF;
329
330
331 /* BUG 1500177
332 -- get the fe name
333 */
334 -- get the fe name
335 XNP_UTILS.GET_FE_NAME_FOR_SP
336 (p_SP_ID=>l_ORIGINAL_DONOR_SP_ID
337 ,x_FE_NAME=>x_FE_NAME
338 ,x_ERROR_CODE=>x_RETURN_CODE
339 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
340 );
341 IF x_RETURN_CODE <> 0
342 THEN
343 RETURN;
344 END IF;
345
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 -- Grab the error message and error no.
350 fnd_message.set_name('XNP','GET_ORIG_DONOR_FE_ERR_REASON');
351 l_error_description := fnd_message.get;
352
353 x_RETURN_CODE := SQLCODE;
354 x_ERROR_DESCRIPTION := SQLERRM||l_error_description;
355
356 END GET_ORIG_DONOR_FE;
357
358 --------------------------------------------------------------------
359 -- Procedure to get the fe_name of the NRC for this
360 -- transaction.
361 ------------------------------------------------------------------
362 PROCEDURE GET_NRC_FE
363 (p_ORDER_ID IN NUMBER
364 ,p_WI_INSTANCE_ID IN NUMBER
365 ,p_FA_INSTANCE_ID IN NUMBER
366 ,x_FE_NAME OUT NOCOPY VARCHAR2
367 ,x_RETURN_CODE OUT NOCOPY NUMBER
368 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
369 )
370 IS
371 l_STARTING_NUMBER VARCHAR2(40);
372 l_ENDING_NUMBER VARCHAR2(40);
373 l_NRC_SP_ID NUMBER;
374 l_error_description varchar2(2000) := NULL;
375 BEGIN
376
377 x_RETURN_CODE := 0;
378 x_ERROR_DESCRIPTION := NULL;
379
380 -- Get the NRC id for this range
381 l_STARTING_NUMBER :=
382 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
383 (p_WI_INSTANCE_ID
384 ,'STARTING_NUMBER'
385 );
386
387 l_ENDING_NUMBER :=
388 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
389 (p_WI_INSTANCE_ID
390 ,'ENDING_NUMBER'
391 );
392
393 XNP_CORE.GET_NRC_ID
394 (p_STARTING_NUMBER=>l_STARTING_NUMBER
395 ,p_ENDING_NUMBER=>l_ENDING_NUMBER
396 ,x_NRC_ID=>l_NRC_SP_ID
397 ,x_ERROR_CODE=>x_RETURN_CODE
398 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
399 );
400 IF x_RETURN_CODE <> 0
401 THEN
402 RETURN;
403 END IF;
404
405 /* BUG 1500177
406 -- get the fe name
407 */
408 -- get the fe name
409 XNP_UTILS.GET_FE_NAME_FOR_SP
410 (p_SP_ID=>l_NRC_SP_ID
411 ,x_FE_NAME=>x_FE_NAME
412 ,x_ERROR_CODE=>x_RETURN_CODE
413 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
414 );
415 IF x_RETURN_CODE <> 0
416 THEN
417 RETURN;
418 END IF;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 -- Grab the error message and error no.
423 fnd_message.set_name('XNP','GET_NRC_FE_ERR_REASON');
424 l_error_description := fnd_message.get;
425
426 x_RETURN_CODE := SQLCODE;
427 x_ERROR_DESCRIPTION := SQLERRM||l_error_description;
428
429 END GET_NRC_FE;
430
431 --------------------------------------------------------------------
432 -- Procedure to get the fe_name of the Recipient for this
433 -- transaction.
434 ------------------------------------------------------------------
435 PROCEDURE GET_RECIPIENT_FE
436 (p_ORDER_ID IN NUMBER
437 ,p_WI_INSTANCE_ID IN NUMBER
438 ,p_FA_INSTANCE_ID IN NUMBER
439 ,x_FE_NAME OUT NOCOPY VARCHAR2
440 ,x_RETURN_CODE OUT NOCOPY NUMBER
441 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
442 )
443 IS
444 l_PORTING_ID VARCHAR2(40);
445 l_RECIPIENT_SP_ID NUMBER;
446 l_RECIPIENT_SP_CODE VARCHAR2(40);
447 l_error_description varchar2(2000) := NULL;
448 BEGIN
449
450 x_RETURN_CODE := 0;
451 x_ERROR_DESCRIPTION := NULL;
452
453 ------------------------------------------------------------------ Get the recipient sp id from the workitem params
454 -- directly. If not found there then get it from
455 -- the SOA table
456 ------------------------------------------------------------------
457 l_RECIPIENT_SP_CODE :=
458 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
459 (p_WI_INSTANCE_ID
460 ,'RECIPIENT_SP_ID'
461 );
462
463 IF l_RECIPIENT_SP_CODE = NULL
464 THEN
465 -- Get the Porting ID from the WI params table
466 l_PORTING_ID :=
467 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
468 (p_WI_INSTANCE_ID
469 ,'PORTING_ID'
470 );
471
472 -- Get the donor sp id from the SOA table
473 SELECT RECIPIENT_SP_ID INTO l_RECIPIENT_SP_ID
474 FROM XNP_SV_SOA
475 WHERE OBJECT_REFERENCE = l_PORTING_ID;
476 ELSE
477
478 XNP_CORE.GET_SP_ID
479 (p_SP_NAME=>l_RECIPIENT_SP_CODE
480 ,x_SP_ID=>l_RECIPIENT_SP_ID
481 ,x_ERROR_CODE=>x_RETURN_CODE
482 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
483 );
484 IF x_RETURN_CODE <> 0
485 THEN
486 RETURN;
487 END IF;
488
489 END IF;
490
491 /* BUG 1500177
492 -- get the fe name
493 */
494 -- get the fe name
495 XNP_UTILS.GET_FE_NAME_FOR_SP
496 (p_SP_ID=>l_RECIPIENT_SP_ID
497 ,x_FE_NAME=>x_FE_NAME
498 ,x_ERROR_CODE=>x_RETURN_CODE
499 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
500 );
501 IF x_RETURN_CODE <> 0
502 THEN
503 RETURN;
504 END IF;
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 -- Grab the error message and error no.
509 fnd_message.set_name('XNP','GET_RECIPIENT_FE_ERR_REASON');
510 fnd_message.set_token('PORTING_ID',l_porting_id);
511 l_error_description := fnd_message.get;
512
513 x_RETURN_CODE := SQLCODE;
514 x_ERROR_DESCRIPTION := SQLERRM||l_error_description;
515
516 END GET_RECIPIENT_FE;
517
518 PROCEDURE GET_SENDER_FE
519 (p_ORDER_ID IN NUMBER
520 ,p_WI_INSTANCE_ID IN NUMBER
521 ,p_FA_INSTANCE_ID IN NUMBER
522 ,x_FE_NAME OUT NOCOPY VARCHAR2
523 ,x_RETURN_CODE OUT NOCOPY NUMBER
524 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
525 )
526 IS
527 l_SENDER_SP_ID NUMBER;
528 l_SENDER_SP_CODE VARCHAR2(40);
529 l_error_description varchar2(2000) := NULL;
530 BEGIN
531
532 x_RETURN_CODE := 0;
533 x_ERROR_DESCRIPTION := NULL;
534
535 ------------------------------------------------------------------ Get the recipient sp id from the workitem params
536 -- directly. If not found there then get it from
537 -- the SOA table
538 ------------------------------------------------------------------
539 l_SENDER_SP_CODE :=
540 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
541 (p_WI_INSTANCE_ID
542 ,'SENDER_NAME'
543 );
544
545 XNP_CORE.GET_SP_ID
546 (p_SP_NAME=>l_SENDER_SP_CODE
547 ,x_SP_ID=>l_SENDER_SP_ID
548 ,x_ERROR_CODE=>x_RETURN_CODE
549 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
550 );
551 IF x_RETURN_CODE <> 0
552 THEN
553 RETURN;
554 END IF;
555
556 /* BUG 1500177
557 -- get the fe name
558 */
559 -- get the fe name
560 XNP_UTILS.GET_FE_NAME_FOR_SP
561 (p_SP_ID=>l_SENDER_SP_ID
562 ,x_FE_NAME=>x_FE_NAME
563 ,x_ERROR_CODE=>x_RETURN_CODE
564 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
565 );
566 IF x_RETURN_CODE <> 0
567 THEN
568 RETURN;
569 END IF;
570
571 EXCEPTION
572 when others then
573 x_return_code := SQLCODE;
574 fnd_message.set_name('XNP','GET_NAME_ERR_REASON');
575 fnd_message.set_token('PARAM_NAME','Get_Sender_Fe:SENDER_NAME');
576
577 x_error_description := SQLERRM||' : '||fnd_message.get;
578
579 END GET_SENDER_FE;
580
581
582 ----------------------------------------------------------------------
583 ----*** Function: LOG_MSG ()
584 ----*** Purpose:
585 ----------------------------------------------------------------------
586
587 PROCEDURE LOG_MSG (P_SEVERITY_LEVEL IN NUMBER
588 ,P_CONTEXT IN VARCHAR2
589 ,P_DESCRIPTION IN VARCHAR2
590 )
591 IS
592 l_debug_level VARCHAR2(40) ;
593 l_debug_id NUMBER := null;
594 BEGIN
595 FND_PROFILE.GET( NAME => 'XNP_DEBUG_LEVEL',
596 VAL => l_debug_level ) ;
597 G_DEBUG_LEVEL := TO_NUMBER(l_debug_level) ;
598 IF (P_SEVERITY_LEVEL <= G_DEBUG_LEVEL) THEN
599 INSERT INTO XNP_DEBUG (DEBUG_ID
600 ,DEBUG_LEVEL
601 , CONTEXT
602 , DESCRIPTION
603 , created_by
604 , creation_date
605 ,last_updated_by
606 ,last_update_date)
607 VALUES( xnp_debug_s.nextval, P_SEVERITY_LEVEL, P_CONTEXT, P_DESCRIPTION
608 ,fnd_global.user_id,sysdate,fnd_global.user_id,sysdate) ;
609 END IF ;
610 END LOG_MSG ;
611
612 ------------------------------------------------------------------
613 -- This function converts dates to the canonical format
614 ------------------------------------------------------------------
615 FUNCTION CANONICAL_TO_DATE
616 (p_DATE_AS_CHAR VARCHAR2
617 )
618 RETURN DATE
619 IS
620
621 l_CANONICAL_MASK VARCHAR2(15) := 'YYYY/MM/DD';
622 l_CANONICAL_DT_MASK VARCHAR2(26) := 'YYYY/MM/DD HH24:MI:SS';
623
624 e_invalid_date_format exception;
625 l_return_date DATE := null;
626
627 BEGIN
628
629 l_return_date := fnd_date.canonical_to_date(p_date_as_char);
630
631 if ( l_return_date IS null) then
632 raise e_invalid_date_format;
633 else
634 return l_return_date;
635 end if;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 --wf_core.context( 'XNP_UTILS'
640 -- , 'CANONICAL_TO_DATE'
641 -- , 'String format of given date is invalid hence returning NULL: Allowed format is :'||l_CANONICAL_DT_MASK
642 -- , null
643 -- , null
644 -- , null
645 -- , null
646 -- );
647 RETURN NULL;
648
649 END CANONICAL_TO_DATE;
650
651 ------------------------------------------------------------------
652 -- This function converts dates to chars in canonical format
653 ------------------------------------------------------------------
654 FUNCTION DATE_TO_CANONICAL
655 (p_DATE DATE
656 ,p_MASK_TYPE VARCHAR2 DEFAULT 'DATETIME'
657 )
658 RETURN VARCHAR2
659
660 IS
661
662
663 l_CANONICAL_MASK VARCHAR2(15) := 'YYYY/MM/DD';
664 l_CANONICAL_DT_MASK VARCHAR2(26) := 'YYYY/MM/DD HH24:MI:SS';
665 l_return_date VARCHAR2(26) := null;
666 e_wrong_date_format exception;
667
668 BEGIN
669
670 l_return_date := fnd_date.date_to_canonical(p_date);
671 if (l_return_date IS null) then
672 raise e_wrong_date_format;
673 else
674 return l_return_date;
675 end if;
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 --wf_core.context( 'XNP_UTILS'
680 -- , 'DATE_TO_CANONICAL'
681 -- , 'Date format of given date is invalid hence returning NULL: Preferred format is :'||l_CANONICAL_DT_MASK
682 -- , null
683 -- , null
684 -- , null
685 -- , null
686 -- );
687 RETURN NULL;
688 END DATE_TO_CANONICAL;
689
690 --------------------------------------------------------------------
691 -- Procedure to get the fe_name of the donor for this
692 -- transaction.
693 ------------------------------------------------------------------
694 PROCEDURE GET_DONOR_NAME
695 (p_ORDER_ID IN NUMBER
696 ,p_WI_INSTANCE_ID IN NUMBER
697 ,p_FA_INSTANCE_ID IN NUMBER
698 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
699 ,x_RETURN_CODE OUT NOCOPY NUMBER
700 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
701 )
702 IS
703 BEGIN
704
705 x_RECIPIENT_NAME :=
706 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
707 (p_WI_INSTANCE_ID
708 ,'DONOR_SP_ID'
709 );
710
711 exception
712 when others then
713 x_return_code := SQLCODE;
714 fnd_message.set_name('XNP','GET_NAME_ERR_REASON');
715 fnd_message.set_token('PARAM_NAME','DONOR_SP_ID');
716
717 x_error_description := SQLERRM||' : '||fnd_message.get;
718
719 END GET_DONOR_NAME;
720
721 --------------------------------------------------------------------
722 -- Procedure to get the of the sender of the
723 -- earlier message
724 ------------------------------------------------------------------
725 PROCEDURE GET_SENDER_NAME
726 (p_ORDER_ID IN NUMBER
727 ,p_WI_INSTANCE_ID IN NUMBER
728 ,p_FA_INSTANCE_ID IN NUMBER
729 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
730 ,x_RETURN_CODE OUT NOCOPY NUMBER
731 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
732 )
733 IS
734 BEGIN
735
736 x_RECIPIENT_NAME :=
737 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
738 (p_WI_INSTANCE_ID
739 ,'SENDER_NAME'
740 );
741
742 exception
743 when others then
744 x_return_code := SQLCODE;
745 fnd_message.set_name('XNP','GET_NAME_ERR_REASON');
746 fnd_message.set_token('PARAM_NAME','SENDER_NAME');
747
748 x_error_description := SQLERRM||' : '||fnd_message.get;
749
750
751 END GET_SENDER_NAME;
752
753 --------------------------------------------------------------------
754 -- Procedure to get the fe_name of the initial donor for this
755 -- transaction.
756 ------------------------------------------------------------------
757 PROCEDURE GET_ORIG_DONOR_NAME
758 (p_ORDER_ID IN NUMBER
759 ,p_WI_INSTANCE_ID IN NUMBER
760 ,p_FA_INSTANCE_ID IN NUMBER
761 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
762 ,x_RETURN_CODE OUT NOCOPY NUMBER
763 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
764 )
765 IS
766 l_STARTING_NUMBER VARCHAR2(40);
767 l_ENDING_NUMBER VARCHAR2(40);
768 l_ORIGINAL_DONOR_SP_ID NUMBER := 0;
769 BEGIN
770 x_RETURN_CODE := 0;
771
772 l_STARTING_NUMBER :=
773 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
774 (p_WI_INSTANCE_ID
775 ,'STARTING_NUMBER'
776 );
777
778 l_ENDING_NUMBER :=
779 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
780 (p_WI_INSTANCE_ID
781 ,'ENDING_NUMBER'
782 );
783
784 XNP_CORE.GET_ASSIGNED_SP_ID
785 (p_STARTING_NUMBER=>l_STARTING_NUMBER
786 ,p_ENDING_NUMBER=>l_ENDING_NUMBER
787 ,x_ASSIGNED_SP_ID=>l_ORIGINAL_DONOR_SP_ID
788 ,x_ERROR_CODE=>x_RETURN_CODE
789 ,x_ERROR_MESSAGE=>x_ERROR_DESCRIPTION
790 );
791 IF x_RETURN_CODE <> 0
792 THEN
793 RETURN;
794 END IF;
795
796 XNP_CORE.GET_SP_NAME
797 (l_ORIGINAL_DONOR_SP_ID
798 ,x_RECIPIENT_NAME
799 ,x_RETURN_CODE
800 ,x_ERROR_DESCRIPTION
801 );
802 exception
803 when others then
804 x_return_code := SQLCODE;
805 fnd_message.set_name('XNP','GET_NAME_ERR_REASON');
806 fnd_message.set_token('PARAM_NAME','STARTING_NUMBER/ENDING_NUMBER');
807
808 x_error_description := SQLERRM||' : '||fnd_message.get;
809
810 END GET_ORIG_DONOR_NAME;
811
812 --------------------------------------------------------------------
813 -- Procedure to get the fe_name of the NRC for this
814 -- transaction.
815 ------------------------------------------------------------------
816 PROCEDURE GET_NRC_NAME
817 (p_ORDER_ID IN NUMBER
818 ,p_WI_INSTANCE_ID IN NUMBER
819 ,p_FA_INSTANCE_ID IN NUMBER
820 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
821 ,x_RETURN_CODE OUT NOCOPY NUMBER
822 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
823 )
824 IS
825 l_starting_number VARCHAR2(40);
826 l_ending_number VARCHAR2(40);
827 l_GEO_ID NUMBER := 0;
828 l_NRC_ID NUMBER := 0;
829 BEGIN
830 l_starting_number :=
831 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
832 (p_WI_INSTANCE_ID
833 ,'STARTING_NUMBER'
834 );
835
836 l_ending_number :=
837 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
838 (p_WI_INSTANCE_ID
839 ,'ENDING_NUMBER'
840 );
841
842 -- Get the NRC id
843
844 XNP_CORE.GET_NRC_ID
845 (l_STARTING_NUMBER
846 ,l_ENDING_NUMBER
847 ,l_NRC_ID
848 ,x_RETURN_CODE
849 ,x_ERROR_DESCRIPTION
850 );
851 if x_return_code <> 0 then
852 return;
853 end if;
854
855 XNP_CORE.GET_SP_NAME
856 (l_NRC_ID
857 ,x_RECIPIENT_NAME
858 ,x_RETURN_CODE
859 ,x_ERROR_DESCRIPTION
860 );
861 if x_return_code <> 0 then
862 return;
863 end if;
864
865 exception
866 when others then
867 x_return_code := SQLCODE;
868 fnd_message.set_name('XNP','GET_NAME_ERR_REASON');
869 fnd_message.set_token('PARAM_NAME','STARTING_NUMBER/ENDING_NUMBER');
870
871 x_error_description := SQLERRM||' : '||fnd_message.get;
872
873 END GET_NRC_NAME;
874
875
876 --------------------------------------------------------------------
877 -- Procedure to get the fe_name of the NRC for this
878 -- transaction.
879 ------------------------------------------------------------------
880 PROCEDURE GET_RECIPIENT_NAME
881 (p_ORDER_ID IN NUMBER
882 ,p_WI_INSTANCE_ID IN NUMBER
883 ,p_FA_INSTANCE_ID IN NUMBER
884 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
885 ,x_RETURN_CODE OUT NOCOPY NUMBER
886 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
887 )
888 IS
889 BEGIN
890 x_RECIPIENT_NAME :=
891 XNP_STANDARD.GET_MANDATORY_WI_PARAM_VALUE
892 (p_WI_INSTANCE_ID
893 ,'RECIPIENT_SP_ID'
894 );
895 exception
896 when others then
897 x_return_code := SQLCODE;
898 fnd_message.set_name('XNP','GET_NAME_ERR_REASON');
899 fnd_message.set_token('PARAM_NAME','RECIPIENT_SP_ID');
900 x_error_description := SQLERRM||' : '||fnd_message.get;
901
902
903 END GET_RECIPIENT_NAME;
904
905 ------------------------------------------------------------------
906 -- Get the FE Name for the given FE ID
907 ------------------------------------------------------------------
908 PROCEDURE GET_FE_NAME
909 (p_FE_ID NUMBER
910 ,x_FE_NAME OUT NOCOPY VARCHAR2
911 ,x_ERROR_CODE OUT NOCOPY NUMBER
912 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
913 )
914 IS
915 CURSOR get_fe_name_for_fe_id IS
916 SELECT fulfillment_element_name
917 FROM xdp_fes FET
918 WHERE FET.fe_id = p_fe_id;
919 BEGIN
920
921 -- get the fe name
922 OPEN get_fe_name_for_fe_id ;
923 FETCH get_fe_name_for_fe_id INTO x_FE_NAME ;
924 close get_fe_name_for_fe_id ;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 x_error_code := SQLCODE;
929 fnd_message.set_name('XNP','GET_DONOR_FE_ERR_REASON');
930 fnd_message.set_token('FE_ID',to_char(p_fe_id));
931
932 x_error_message := SQLERRM||':'|| fnd_message.get;
933
934 IF get_fe_name_for_fe_id%ISOPEN THEN
935 close get_fe_name_for_fe_id ;
936 END IF ;
937
938 END GET_FE_NAME;
939
940
941 PROCEDURE GET_FE_NAME
942 (p_SP_ID NUMBER
943 ,x_FE_NAME OUT NOCOPY VARCHAR2
944 ,x_ERROR_CODE OUT NOCOPY NUMBER
945 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
946 )
947 IS
948
949
950
951 /****** declared at package level as it was used
952 in differenc procedures.. skilaru 03/26/2001
953
954 CURSOR get_fe_name_for_sp IS
955 SELECT fulfillment_element_name
956 FROM xdp_fes FET,
957 xnp_sp_adapters SPA,
958 xdp_fe_generic_config SWG
959 WHERE FET.fe_id = SPA.fe_id
960 AND SPA.fe_id = SWG.fe_id
961 AND SPA.sp_id = p_sp_id
962 AND (sysdate BETWEEN SWG.start_date
963 AND NVL(SWG.end_date, sysdate))
964 ORDER BY preferred_flag desc, sequence asc ;
965
966 ****/
967
968 BEGIN
969 -- get the fe name
970
971 OPEN g_get_fe_name_for_sp_csr(p_sp_id);
972 FETCH g_get_fe_name_for_sp_csr INTO x_FE_NAME ;
973
974
975 IF g_get_fe_name_for_sp_csr%NOTFOUND THEN
976 x_error_code := xnp_errors.g_no_fe_for_sp;
977 fnd_message.set_name('XNP','NO_FE_FOR_SP');
978 fnd_message.set_token('NAME', TO_CHAR(p_sp_id));
979 x_error_message := fnd_message.get;
980 CLOSE g_get_fe_name_for_sp_csr ;
981 RETURN ;
982
983 END IF;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 x_error_code := SQLCODE;
988 fnd_message.set_name('XNP','GET_FE_NAME_FOR_SP_ERR_REASON');
989 fnd_message.set_token('SP_ID',p_sp_id);
990
991 x_error_message := SQLERRM||':'||fnd_message.get;
992
993 IF g_get_fe_name_for_sp_csr%ISOPEN THEN
994 close g_get_fe_name_for_sp_csr ;
995 END IF ;
996
997 END GET_FE_NAME;
998
999 PROCEDURE GET_FE_NAME_FOR_SP
1000 (p_SP_ID NUMBER
1001 ,x_FE_NAME OUT NOCOPY VARCHAR2
1002 ,x_ERROR_CODE OUT NOCOPY NUMBER
1003 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1004 )
1005 IS
1006 /**** declared at package level as it is used in
1007 several procedures. -- skilaru 03/25/2001
1008
1009 CURSOR get_fe_name_for_sp IS
1010 SELECT fulfillment_element_name
1011 FROM xdp_fes FET,
1012 xnp_sp_adapters SPA,
1013 xdp_fe_generic_config SWG
1014 WHERE FET.fe_id = SPA.fe_id
1015 AND SPA.fe_id = SWG.fe_id
1016 AND SPA.sp_id = p_sp_id
1017 AND (sysdate BETWEEN SWG.start_date
1018 AND NVL(SWG.end_date, sysdate))
1019 ORDER BY preferred_flag desc, sequence asc ;
1020 *****/
1021
1022 BEGIN
1023 -- get the fe name
1024
1025 OPEN g_get_fe_name_for_sp_csr( p_sp_id) ;
1026 FETCH g_get_fe_name_for_sp_csr INTO x_FE_NAME ;
1027
1028 IF g_get_fe_name_for_sp_csr%NOTFOUND THEN
1029 x_error_code := xnp_errors.g_no_fe_for_sp;
1030 fnd_message.set_name('XNP','NO_FE_FOR_SP');
1031 fnd_message.set_token('NAME', TO_CHAR(p_sp_id));
1032 x_error_message := fnd_message.get;
1033 CLOSE g_get_fe_name_for_sp_csr ;
1034 RETURN ;
1035
1036 END IF;
1037
1038 close g_get_fe_name_for_sp_csr ;
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 x_error_code := SQLCODE;
1043 fnd_message.set_name('XNP','GET_FE_NAME_FOR_SP_ERR_REASON');
1044 fnd_message.set_token('SP_ID',p_sp_id);
1045
1046 x_error_message := SQLERRM||':'||fnd_message.get;
1047
1048 IF g_get_fe_name_for_sp_csr%ISOPEN THEN
1049 close g_get_fe_name_for_sp_csr ;
1050 END IF ;
1051
1052 END GET_FE_NAME_FOR_SP;
1053 ----------------------------------------------------------------------
1054 ----*** Procedure: EXEC_DYNAMIC_CREATE_MSG()
1055 ----*** Purpose: Execute dynamic SQL for message create.
1056 ----------------------------------------------------------------------
1057
1058 PROCEDURE EXEC_DYNAMIC_CREATE_MSG
1059 (P_DYNAMIC_MSG_TEXT IN VARCHAR2
1060 ,X_MSG_TEXT OUT NOCOPY VARCHAR2
1061 ,X_ERROR_CODE OUT NOCOPY NUMBER
1062 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1063 )
1064 IS
1065 ----------------------------------------------------------------------
1066 ----*** local variables
1067 ----------------------------------------------------------------------
1068
1069 l_cursor NUMBER ;
1070 l_sql VARCHAR2(16000) ;
1071 l_num_rows NUMBER ;
1072 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
1073 BEGIN
1074
1075 ----------------------------------------------------------------------
1076 -- open cursor for dynamic SQL
1077 ----------------------------------------------------------------------
1078
1079 l_cursor := DBMS_SQL.OPEN_CURSOR;
1080 x_error_code := 0 ;
1081 x_error_message := NULL ;
1082
1083 ----------------------------------------------------------------------
1084 -- parse the SQL statement
1085 ----------------------------------------------------------------------
1086
1087 DBMS_SQL.PARSE ( l_cursor, P_DYNAMIC_MSG_TEXT, DBMS_SQL.V7 ) ;
1088
1089 ------------------------------------------------------------------
1090 -- bind all input and output variables
1091 ------------------------------------------------------------------
1092 DBMS_SQL.BIND_VARIABLE ( l_cursor, ':msg_text', x_msg_text ) ;
1093 DBMS_SQL.BIND_VARIABLE ( l_cursor, ':error_code', x_error_code ) ;
1094 DBMS_SQL.BIND_VARIABLE ( l_cursor, ':error_message', x_error_message, 1024 ) ;
1095
1096 ----------------------------------------------------------------------
1097 -- execute the procedure call
1098 ----------------------------------------------------------------------
1099
1100 l_num_rows := DBMS_SQL.EXECUTE ( l_cursor ) ;
1101
1102 ----------------------------------------------------------------------
1103 -- retrieve the return values
1104 ----------------------------------------------------------------------
1105
1106 DBMS_SQL.VARIABLE_VALUE ( l_cursor, ':msg_text', x_msg_text ) ;
1107 DBMS_SQL.VARIABLE_VALUE ( l_cursor, ':error_code', x_error_code ) ;
1108 DBMS_SQL.VARIABLE_VALUE ( l_cursor, ':error_message', x_error_message ) ;
1109
1110 ----------------------------------------------------------------------
1111 -- not processing the error message, an exception will be raised
1112 ----------------------------------------------------------------------
1113
1114 DBMS_SQL.CLOSE_CURSOR ( l_cursor ) ;
1115
1116 END EXEC_DYNAMIC_CREATE_MSG ;
1117
1118 ----------------------------------------------------------------------
1119 ----*** Procedure: EXEC_DYNAMIC_SEND_PUBLISH()
1120 ----*** Purpose: Execute dynamic SQL for message send and publish.
1121 ----------------------------------------------------------------------
1122
1123 PROCEDURE EXEC_DYNAMIC_SEND_PUBLISH
1124 (P_DYNAMIC_MSG_TEXT IN VARCHAR2
1125 ,X_MSG_ID OUT NOCOPY NUMBER
1126 ,X_ERROR_CODE OUT NOCOPY NUMBER
1127 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1128 )
1129 IS
1130 ----------------------------------------------------------------------
1131 ----* local variables
1132 ----------------------------------------------------------------------
1133
1134 l_cursor NUMBER ;
1135 l_sql VARCHAR2(16000) ;
1136 l_num_rows NUMBER ;
1137 BEGIN
1138
1139 ----------------------------------------------------------------------
1140 -- open cursor for dynamic SQL
1141 ----------------------------------------------------------------------
1142
1143 l_cursor := DBMS_SQL.OPEN_CURSOR;
1144 x_error_code := 0 ;
1145 x_error_message := NULL ;
1146
1147 ----------------------------------------------------------------------
1148 -- parse the SQL statement
1149 ----------------------------------------------------------------------
1150
1151 DBMS_SQL.PARSE ( l_cursor, P_DYNAMIC_MSG_TEXT, DBMS_SQL.V7 ) ;
1152
1153 ------------------------------------------------------------------
1154 -- bind all input and output variables
1155 ------------------------------------------------------------------
1156 DBMS_SQL.BIND_VARIABLE ( l_cursor, ':msg_id', x_msg_id ) ;
1157 DBMS_SQL.BIND_VARIABLE ( l_cursor, ':error_code', x_error_code ) ;
1158 DBMS_SQL.BIND_VARIABLE ( l_cursor, ':error_message', x_error_message, 1024 ) ;
1159
1160 ----------------------------------------------------------------------
1161 -- execute the procedure call
1162 ----------------------------------------------------------------------
1163
1164 l_num_rows := DBMS_SQL.EXECUTE ( l_cursor ) ;
1165
1166 ----------------------------------------------------------------------
1167 -- retrieve the return values
1168 ----------------------------------------------------------------------
1169
1170 DBMS_SQL.VARIABLE_VALUE ( l_cursor, ':msg_id', x_msg_id ) ;
1171 DBMS_SQL.VARIABLE_VALUE ( l_cursor, ':error_code', x_error_code ) ;
1172 DBMS_SQL.VARIABLE_VALUE ( l_cursor, ':error_message', x_error_message ) ;
1173
1174 ----------------------------------------------------------------------
1175 -- not processing the error message, an exception will be raised
1176 ----------------------------------------------------------------------
1177
1178 DBMS_SQL.CLOSE_CURSOR ( l_cursor ) ;
1179
1180 END EXEC_DYNAMIC_SEND_PUBLISH ;
1181
1182 ------------------------------------------------------------------
1183 -- Procedure to notify errors in the workflow activities
1184 ------------------------------------------------------------------
1185 PROCEDURE NOTIFY_ERROR
1186 (P_PKG_NAME VARCHAR2
1187 ,P_PROC_NAME VARCHAR2
1188 ,P_MSG_NAME VARCHAR2
1189 ,P_WORKITEM_INSTANCE_ID NUMBER
1190 ,P_TOK1 VARCHAR2 DEFAULT NULL
1191 ,P_VAL1 VARCHAR2 DEFAULT NULL
1192 ,P_TOK2 VARCHAR2 DEFAULT NULL
1193 ,P_VAL2 VARCHAR2 DEFAULT NULL
1194 ,P_TOK3 VARCHAR2 DEFAULT NULL
1195 ,P_VAL3 VARCHAR2 DEFAULT NULL
1196 )
1197 IS
1198 l_progress VARCHAR2(4000) := NULL;
1199 l_STARTING_NUMBER VARCHAR2(40) := NULL;
1200 l_ENDING_NUMBER VARCHAR2(40) := NULL;
1201 BEGIN
1202 fnd_message.set_name('XNP',p_msg_name);
1203
1204 l_STARTING_NUMBER :=
1205 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1206 (P_WORKITEM_INSTANCE_ID
1207 ,'STARTING_NUMBER'
1208 );
1209
1210 l_ENDING_NUMBER :=
1211 XNP_STANDARD.GET_WORKITEM_PARAM_VALUE
1212 (P_WORKITEM_INSTANCE_ID
1213 ,'ENDING_NUMBER'
1214 );
1215 fnd_message.set_token('NUMRANGE'
1216 , l_STARTING_NUMBER||' to ' || l_ENDING_NUMBER);
1217
1218 if (p_tok1 IS NOT NULL) then
1219 fnd_message.set_token(p_tok1, p_val1);
1220 end if;
1221
1222 if (p_tok2 IS NOT NULL) then
1223 fnd_message.set_token(p_tok2, p_val2);
1224 end if;
1225
1226 if (p_tok3 IS NOT NULL) then
1227 fnd_message.set_token(p_tok3, p_val3);
1228 end if;
1229
1230
1231 l_progress := '$' || fnd_message.get;
1232
1233 wf_core.context( p_pkg_name
1234 , p_proc_name
1235 , l_progress
1236 , null
1237 , null
1238 , null
1239 , null
1240 );
1241
1242 END NOTIFY_ERROR;
1243
1244 ----------------------------------------------------------------------
1245 ----*** Procedure: GET_WF_INSTANCE()
1246 ----*** Purpose: Returns the workflow type, key and activity.
1247 ----------------------------------------------------------------------
1248
1249
1250 PROCEDURE GET_WF_INSTANCE (
1251 P_PROCESS_REFERENCE IN VARCHAR2
1252 ,X_WF_TYPE OUT NOCOPY VARCHAR2
1253 ,X_WF_KEY OUT NOCOPY VARCHAR2
1254 ,X_WF_ACTIVITY OUT NOCOPY VARCHAR2
1255 )
1256 IS
1257 l_str VARCHAR2(256) ;
1258 l_start_pos NUMBER := 0;
1259 l_end_pos NUMBER := 0;
1260
1261 BEGIN
1262
1263 x_wf_type := NULL ;
1264 x_wf_key := NULL ;
1265 x_wf_activity := NULL ;
1266 l_str := p_process_reference;
1267
1268 l_start_pos := l_end_pos+1;
1269 l_end_pos := INSTR(l_str, ':',l_start_pos,1);
1270 x_wf_type := SUBSTR(l_str, l_start_pos, (l_end_pos - l_start_pos));
1271
1272
1273 l_start_pos := l_end_pos+1;
1274 l_end_pos := INSTR(l_str, ':', l_start_pos, 1);
1275 x_wf_key := SUBSTR(l_str,l_start_pos, (l_end_pos - l_start_pos));
1276
1277 l_start_pos := l_end_pos+1;
1278 l_end_pos := LENGTH(p_process_reference)+1;
1279
1280 x_wf_activity := SUBSTR(l_str,l_start_pos, (l_end_pos - l_start_pos));
1281
1282 END GET_WF_INSTANCE ;
1283
1284 ------------------------------------------------------------------
1285 -- Gets the process referenc against the reference_id
1286 -- from xnp_callback_events. Then looks up the WF to
1287 -- get the item attribute WORKITEM_INSTANCE_ID
1288 ------------------------------------------------------------------
1289
1290 PROCEDURE GET_WORKITEM_INSTANCE_ID
1291 (p_reference_id VARCHAR2
1292 ,x_workitem_instance_id OUT NOCOPY NUMBER
1293 ,x_error_code OUT NOCOPY NUMBER
1294 ,x_error_message OUT NOCOPY VARCHAR2
1295 )
1296 IS
1297 l_process_reference VARCHAR2(512) := NULL;
1298 l_wf_type VARCHAR2(40) := NULL;
1299 l_wf_key VARCHAR2(240) := NULL;
1300 l_wf_activity VARCHAR2(240) := NULL;
1301
1302 CURSOR c_get_process_reference IS
1303 SELECT process_reference
1304 FROM xnp_callback_events
1305 WHERE reference_id = p_reference_id;
1306
1307 BEGIN
1308
1309 x_error_code := 0;
1310
1311 -- Get the first of the available rows returned by the cursor
1312 OPEN c_get_process_reference;
1313 FETCH c_get_process_reference INTO l_process_reference;
1314
1315 IF c_get_process_reference%NOTFOUND THEN
1316 raise NO_DATA_FOUND;
1317 END IF;
1318
1319 CLOSE c_get_process_reference;
1320
1321 XNP_UTILS.GET_WF_INSTANCE
1322 (P_PROCESS_REFERENCE => l_process_reference
1323 ,X_WF_TYPE => l_wf_type
1324 ,X_WF_KEY => l_wf_key
1325 ,X_WF_ACTIVITY => l_wf_activity
1326 );
1327
1328 x_workitem_instance_id := wf_engine.GetItemAttrNumber
1329 (itemtype=>l_wf_type
1330 ,itemkey=>l_wf_key
1331 ,aname=>'WORKITEM_INSTANCE_ID'
1332 );
1333
1334 EXCEPTION
1335 WHEN NO_DATA_FOUND THEN
1336 x_error_code := SQLCODE;
1337 fnd_message.set_name('XNP','STD_GET_FAILED');
1338 fnd_message.set_token('FAILED_PROC','XNP_UTILS.GET_WORKITEM_INSTANCE_ID');
1339 fnd_message.set_token('ATTRNAME','PROCESS_REFERENCE');
1340 fnd_message.set_token('KEY','REFERENCE_ID');
1341 fnd_message.set_token('VALUE',p_reference_id);
1342 x_error_message := fnd_message.get||':'||SQLERRM;
1343
1344 IF c_get_process_reference%ISOPEN THEN
1345 close c_get_process_reference;
1346 END IF;
1347
1348 WHEN OTHERS THEN
1349 x_error_code := SQLCODE;
1350 fnd_message.set_name('XNP','STD_ERROR');
1351 fnd_message.set_token('ERROR_LOCN','XNP_UTILS.GET_WORKITEM_INSTANCE_ID');
1352 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1353 x_error_message := fnd_message.get;
1354
1355 IF c_get_process_reference%ISOPEN THEN
1356 close c_get_process_reference;
1357 END IF;
1358
1359 END GET_WORKITEM_INSTANCE_ID;
1360
1361 ------------------------------------------------------------------
1362 -- Procedure Get the PORTING_ID from the body text
1363 -- of the message and overwrites the REFERENCE_ID
1364 -- with this value
1365 -- For the subsequent transactions, the PORITNG_ID's
1366 -- value will be the REFERENCE_ID for this transaction
1367 --
1368 ------------------------------------------------------------------
1369 PROCEDURE RESET_REFERENCE_ID (
1370 p_msg_header IN OUT NOCOPY XNP_MESSAGE.MSG_HEADER_REC_TYPE,
1371 p_msg_text IN VARCHAR2,
1372 x_error_code OUT NOCOPY NUMBER,
1373 x_error_message OUT NOCOPY VARCHAR2 )
1374 IS
1375 l_PORTING_ID VARCHAR2(80) := NULL;
1376 BEGIN
1377 XNP_XML_UTILS.DECODE(p_msg_text, 'PORTING_ID', l_PORTING_ID);
1378
1379 if (l_PORTING_ID IS NOT NULL) then
1380 p_msg_header.reference_id := l_PORTING_ID;
1381 end if;
1382 END RESET_REFERENCE_ID;
1383
1384 ------------------------------------------------------------------
1385 -- Gets the value in CALLBACK_REF_ID_NAME activity attribute
1386 -- if the value is not CUSTOM
1387 -- then it refers to a WI param name so the value is
1388 -- got from there
1389 -- if the value IS CUSTOM
1390 -- then the actual value is got from the CUSTOM_CALLBACK_REFERENCE_ID
1391 -- activity attrbute and directly returned.
1392 ------------------------------------------------------------------
1393 PROCEDURE CHECK_TO_GET_REF_ID
1394 (p_itemtype in varchar2
1395 ,p_itemkey in varchar2
1396 ,p_actid in number
1397 ,p_workitem_instance_id in number
1398 ,x_reference_id OUT NOCOPY varchar2
1399 )
1400 IS
1401
1402 l_activity_name VARCHAR2(256);
1403 l_atype VARCHAR2(256);
1404 l_asubtype VARCHAR2(256);
1405 l_aformat VARCHAR2(256);
1406 l_number_value NUMBER;
1407 l_date_value DATE;
1408 l_wi_param_name VARCHAR2(80) := NULL;
1409 l_callback_ref_id_name VARCHAR2(80) := 'CALLBACK_REFERENCE_ID_NAME';
1410 l_custom_callback_ref_id VARCHAR2(80) := 'CUSTOM_CALLBACK_REFERENCE_ID';
1411
1412 BEGIN
1413
1414 l_wi_param_name := wf_engine.GetActivityAttrText
1415 (itemtype => p_itemtype
1416 ,itemkey => p_itemkey
1417 ,actid => p_actid
1418 ,aname => l_callback_ref_id_name
1419 );
1420
1421 if (l_wi_param_name = 'CUSTOM') then
1422 -- get the value from the activity attribute CUSTOM_CALLBACK_REFERENCE_ID
1423 x_reference_id := wf_engine.GetActivityAttrText
1424 (itemtype => p_itemtype
1425 ,itemkey => p_itemkey
1426 ,actid => p_actid
1427 ,aname => l_custom_callback_ref_id
1428 );
1429 else
1430 -- get the reference id value from the workitem parameter value
1431 x_reference_id := xnp_standard.get_mandatory_wi_param_value
1432 (p_workitem_instance_id
1433 ,l_wi_param_name
1434 );
1435
1436 end if;
1437
1438 END CHECK_TO_GET_REF_ID;
1439
1440 ------------------------------------------------------------------
1441 -- Copies the workitem parameter value from the
1442 -- source workitem to the destination workitem
1443 ------------------------------------------------------------------
1444 PROCEDURE COPY_WI_PARAM_VALUE
1445 (p_src_wi_id number
1446 ,p_dest_wi_id number
1447 ,p_param_name varchar2
1448 )
1449 IS
1450 l_param_value varchar2(200) := null;
1451 BEGIN
1452 l_param_value := xnp_standard.get_workitem_param_value
1453 (p_workitem_instance_id => p_src_wi_id
1454 ,p_parameter_name => p_param_name
1455 );
1456
1457 -- Set the value in the destination workitem
1458 xnp_standard.set_workitem_param_value
1459 (p_workitem_instance_id=>p_dest_wi_id
1460 ,p_parameter_name=>p_param_name
1461 ,p_parameter_value=>l_param_value
1462 );
1463
1464 END COPY_WI_PARAM_VALUE;
1465
1466 ------------------------------------------------------------------
1467 -- Copies the all workitem parameter values from the
1468 -- source workitem to the destination workitem
1469 ------------------------------------------------------------------
1470 PROCEDURE COPY_ALL_WI_PARAMS
1471 (p_src_wi_id number
1472 ,p_dest_wi_id number
1473 )
1474 IS
1475 CURSOR c_wi_params IS
1476
1477 SELECT wip.parameter_name name
1478 FROM xdp_wi_parameters wip
1479 WHERE wip.workitem_id =
1480 (SELECT fwl.workitem_id
1481 FROM xdp_fulfill_worklist fwl
1482 WHERE fwl.workitem_instance_id = p_src_wi_id
1483 );
1484 BEGIN
1485
1486 -- Copy all the workitem parameters one by one
1487 FOR wi_param IN c_wi_params LOOP
1488 XNP_UTILS.COPY_WI_PARAM_VALUE
1489 (p_src_wi_id => p_src_wi_id
1490 ,p_dest_wi_id => p_dest_wi_id
1491 ,p_param_name => wi_param.name
1492 );
1493 END LOOP;
1494
1495 END COPY_ALL_WI_PARAMS;
1496
1497 ------------------------------------------------------------------
1498 -- The MLS message is got. All the tokens are replace by
1499 -- the values as in the workitem parameters
1500 --
1501 -- Note:
1502 -- 1. Ensure that each workitem paramter (token) has
1503 -- atleast 1 space after it
1504 -- 2. The subject will be the character after the first new line
1505 ------------------------------------------------------------------
1506 PROCEDURE GET_INTERPRETED_NOTIFICATION
1507 (p_workitem_instance_id number
1508 ,p_mls_message_name varchar2
1509 ,x_subject OUT NOCOPY varchar2
1510 ,x_body OUT NOCOPY varchar2
1511 ,x_error_code OUT NOCOPY number
1512 ,x_error_message OUT NOCOPY varchar2
1513 )
1514 IS
1515 token varchar2(2000) := null;
1516 l_length number := 0;
1517 l_string varchar2(4000) := null;
1518 l_new_string varchar2(4000) := null;
1519 l_body varchar2(4000) := null;
1520
1521 l_value varchar2(200):= null;
1522 l_posn number := 0;
1523 l_appln_name varchar2(4) := null;
1524 BEGIN
1525 x_error_code := 0;
1526 x_error_message := null;
1527
1528 -- Get the string to be parsed and decoded
1529 l_appln_name := substr(p_mls_message_name,1,3);
1530 l_string := fnd_message.get_string(l_appln_name,p_mls_message_name);
1531
1532 fnd_message.set_name(l_appln_name,p_mls_message_name);
1533
1534 -- ensure that there is a space before each '&'
1535 -- and space after the message string
1536 l_string := replace(l_string,'&',' &');
1537 l_string := l_string || ' ';
1538
1539 -- get the lenght of the final string
1540 l_length := LENGTH(l_string);
1541
1542
1543 -- Loop through and interpret each token
1544 LOOP
1545
1546 -- Check if there are any more uninterpreted tags
1547 if (l_string = replace(l_string,'&','%'))
1548 then
1549 exit;
1550 end if;
1551
1552 token := substr
1553 (substr(l_string, instr(l_string,'&')+1)
1554 ,1
1555 ,instr(substr(l_string,instr(l_string,'&')+1),' ')
1556 );
1557
1558 l_posn := instr(token, xnp_utils.g_new_line);
1559 if(l_posn > 0) then
1560 token := substr(token,1,l_posn-1);
1561 end if;
1562 l_posn := 0;
1563
1564 token := replace(token,' ','');
1565
1566 l_value := xnp_standard.get_workitem_param_value
1567 (p_workitem_instance_id
1568 ,token
1569 );
1570
1571 fnd_message.set_token(token,nvl(l_value,'<null>'));
1572
1573 l_string := substr(l_string,instr(l_string,token),l_length);
1574
1575 EXIT WHEN ((length(l_string) = 0) OR (l_string is null ));
1576 END LOOP;
1577
1578 l_body := fnd_message.get;
1579 x_body := l_body;
1580 x_subject := substr(l_body,1,instr(l_body,xnp_utils.g_new_line));
1581 return;
1582
1583 END GET_INTERPRETED_NOTIFICATION;
1584
1585
1586 PROCEDURE get_adapter_name(
1587 p_sp_id NUMBER
1588 ,x_adapter_name OUT NOCOPY VARCHAR2
1589 ,x_error_code OUT NOCOPY NUMBER
1590 ,x_error_message OUT NOCOPY VARCHAR2
1591 )
1592
1593 IS
1594 CURSOR get_fe_name_for_sp1 IS
1595 SELECT fulfillment_element_name, FET.fe_id
1596 FROM xdp_fes FET,
1597 xnp_sp_adapters SPA,
1598 xdp_fe_generic_config SWG
1599 WHERE FET.fe_id = SPA.fe_id
1600 AND SPA.fe_id = SWG.fe_id
1601 AND SPA.sp_id = p_sp_id
1602 AND (sysdate BETWEEN SWG.start_date
1603 AND NVL(SWG.end_date, sysdate))
1604 ORDER BY preferred_flag desc, sequence asc ;
1605 l_fe_id NUMBER;
1606 l_fe_name VARCHAR2(1024) ;
1607
1608 BEGIN
1609 -- get the fe name
1610
1611 OPEN get_fe_name_for_sp1;
1612
1613 FETCH get_fe_name_for_sp1 INTO l_fe_name , l_fe_id;
1614
1615 IF get_fe_name_for_sp1%NOTFOUND THEN
1616
1617 x_error_code := xnp_errors.g_no_fe_for_sp;
1618 fnd_message.set_name('XNP','NO_FE_FOR_SP');
1619 fnd_message.set_token('NAME', TO_CHAR(p_sp_id));
1620 x_error_message := fnd_message.get;
1621 CLOSE get_fe_name_for_sp1 ;
1622 RETURN ;
1623
1624 END IF;
1625
1626 CLOSE get_fe_name_for_sp1 ;
1627
1628 -- get adapter given the FE ID
1629 x_adapter_name := XDP_ADAPTER_CORE_DB.Is_Message_Adapter_Available(l_fe_id);
1630
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633
1634 x_error_code := SQLCODE;
1635 fnd_message.set_name('XNP','GET_FE_NAME_FOR_SP_ERR_REASON');
1636 fnd_message.set_token('SP_ID',p_sp_id);
1637 x_error_message := SQLERRM||':'||fnd_message.get;
1638
1639 IF get_fe_name_for_sp1%ISOPEN THEN
1640 close get_fe_name_for_sp1 ;
1641 END IF ;
1642
1643 END GET_ADAPTER_NAME;
1644
1645
1646 FUNCTION get_adapter_using_fe(
1647 p_fe_name IN VARCHAR2 )
1648 RETURN VARCHAR2
1649 IS
1650 --
1651 -- NOTE: This method should always be exactly the same as
1652 -- XDP_ADAPTER_CORE_DB.Is_Message_Adapter_Available(p_fe_name).
1653 -- We were not able to call this method directly because of the
1654 -- PRAGMA restrictions
1655 --
1656 l_adapter_name varchar2(80) := NULL;
1657
1658 cursor c_getadapter IS
1659 SELECT xad.adapter_name
1660 FROM xdp_adapter_reg xad, xdp_adapter_types_b t,xdp_fes XFE
1661 WHERE XAD.fe_id = XFE.fe_id
1662 AND XFE.fulfillment_element_name = p_fe_name
1663 AND xad.adapter_type = t.adapter_type
1664 AND application_mode='QUEUE'
1665 AND xad.adapter_status not in ('NOT_AVAILABLE')
1666 ORDER BY
1667 DECODE(adapter_status, 'IDLE', 1,
1668 'SUSPENDED', 2,
1669 'DISCONNECTED', 3,
1670 'SHUTDOWN', 4, 5)
1671 ASC ;
1672
1673 BEGIN
1674
1675 if c_getadapter%ISOPEN then
1676 close c_getadapter;
1677 end if;
1678
1679 open c_getadapter;
1680
1681 fetch c_getadapter into l_adapter_name;
1682
1683 if c_getadapter%NOTFOUND then
1684 l_adapter_name := NULL;
1685 end if;
1686
1687 return l_adapter_name;
1688 exception
1689 when others then
1690 if c_getadapter%ISOPEN then
1691 close c_getadapter;
1692 end if;
1693 raise;
1694
1695
1696 END get_adapter_using_fe ;
1697
1698 --
1699 -- Checks if the number range exists
1700 --
1701 PROCEDURE CHECK_IF_NUM_RANGE_EXISTS
1702 (p_STARTING_NUMBER IN VARCHAR2
1703 ,p_ENDING_NUMBER IN VARCHAR2
1704 ,p_NUMBER_RANGE_ID IN NUMBER
1705 )
1706 IS
1707
1708 l_number_range_id NUMBER := null;
1709 --- 4 cases to be considered to check if new number
1710 ---- is overlapping with existing number ranges
1711 --- |-----------------|
1712 --- 1. <----------->
1713 --- 2. <----------->
1714 --- 3. <----------->
1715 --- 4. <--------------------------->
1716
1717 CURSOR c_num_range_exists IS
1718 SELECT number_range_id
1719 FROM xnp_number_ranges
1720 WHERE
1721 -- Case 1
1722 ( (to_number(starting_number) >= to_number(p_starting_number))
1723 AND (to_number(starting_number) <= to_number(p_ending_number))
1724 AND (to_number(ending_number) >= to_number(p_ending_number)))
1725 -- Case 2
1726 OR ( (to_number(starting_number) <= to_number(p_starting_number))
1727 AND (to_number(ending_number) >= to_number(p_ending_number)))
1728 -- Case 3
1729 OR ( (to_number(starting_number) <= to_number(p_starting_number))
1730 AND (to_number(ending_number) >= to_number(p_starting_number))
1731 AND (to_number(ending_number) <= to_number(p_ending_number)))
1732 -- Case 4
1733 OR ( (to_number(starting_number) >= to_number(p_starting_number))
1734 AND (to_number(ending_number) <= to_number(p_ending_number)));
1735
1736 BEGIN
1737
1738 OPEN c_num_range_exists;
1739 FETCH c_num_range_exists INTO l_number_range_id;
1740
1741 IF ( (l_number_range_id is not null)
1742 AND (l_number_range_id <> nvl(p_number_range_id,-1)) )
1743 THEN
1744 fnd_message.set_name('XNP','XNP_NUM_RANGE_EXISTS');
1745 fnd_message.set_token('STARTING_NUMBER',p_STARTING_NUMBER);
1746 fnd_message.set_token('ENDING_NUMBER',p_ENDING_NUMBER);
1747 app_exception.raise_exception;
1748 END IF;
1749
1750 END CHECK_IF_NUM_RANGE_EXISTS;
1751
1752 END XNP_UTILS;