[Home] [Help]
PACKAGE BODY: APPS.XDP_ENGINE
Source
1 PACKAGE BODY XDP_ENGINE AS
2 /* $Header: XDPENGNB.pls 120.1 2005/06/15 22:56:01 appldev $ */
3
4
5 Procedure Get_WI_Parameter_info (p_wi_instance_id IN NUMBER,
6 p_parameter_name IN VARCHAR2,
7 p_evaluation_procedure OUT NOCOPY VARCHAR2,
8 p_evaluation_mode OUT NOCOPY VARCHAR2,
9 p_default_value OUT NOCOPY VARCHAR2,
10 p_parameter_value OUT NOCOPY VARCHAR2,
11 p_parameter_ref_value OUT NOCOPY VARCHAR2,
12 p_audit_flag OUT NOCOPY VARCHAR2,
13 p_workitem_id OUT NOCOPY NUMBER);
14
15 Procedure Get_FA_Parameter_info (p_fa_instance_id IN NUMBER,
16 p_parameter_name IN VARCHAR2,
17 p_evaluation_procedure OUT NOCOPY VARCHAR2,
18 p_fa_id OUT NOCOPY NUMBER,
19 p_default_value OUT NOCOPY VARCHAR2,
20 p_parameter_value OUT NOCOPY VARCHAR2,
21 p_parameter_ref_value OUT NOCOPY VARCHAR2,
22 p_audit_flag OUT NOCOPY VARCHAR2);
23
24 Function DoesWIParamExist (p_wi_instance_id IN NUMBER,
25 p_parameter_name IN VARCHAR2) return VARCHAR2;
26
27
28 Function DoesFAParamExist (p_fa_instance_id IN NUMBER,
29 p_parameter_name IN VARCHAR2) return VARCHAR2;
30
31 Function DoesOrderParamExist (p_order_id IN NUMBER,
32 p_parameter_name IN VARCHAR2) return VARCHAR2;
33
34 Function DoesLineParamExist (p_line_item_id IN NUMBER,
35 p_parameter_name IN VARCHAR2) return VARCHAR2;
36
37 Procedure LoadWorklistDetails(p_wi_instance_id IN NUMBER,
38 p_parameter_name IN VARCHAR2,
39 p_workitem_id IN NUMBER,
40 p_is_value_evaluated IN VARCHAR2,
41 p_parameter_value IN VARCHAR2,
42 p_parameter_ref_value IN VARCHAR2);
43
44 Procedure UpdateWorklistDetails(
45 p_wi_instance_id IN NUMBER,
46 p_parameter_name IN VARCHAR2,
47 p_is_value_evaluated IN VARCHAR2,
48 p_parameter_value IN VARCHAR2,
49 p_parameter_ref_value IN VARCHAR2);
50
51 Procedure LoadFADetails(p_fa_instance_id IN NUMBER,
52 p_parameter_name IN VARCHAR2,
53 p_fa_id IN NUMBER,
54 p_is_value_evaluated IN VARCHAR2,
55 p_parameter_value IN VARCHAR2,
56 p_parameter_ref_value IN VARCHAR2);
57
58 Procedure UpdateFaDetails(p_fa_instance_id IN NUMBER,
59 p_parameter_name IN VARCHAR2,
60 p_evaluated_flag IN VARCHAR2,
61 p_parameter_value IN VARCHAR2,
62 p_parameter_ref_value IN VARCHAR2);
63
64 Procedure LoadOrderParameters(p_order_id IN NUMBER,
65 p_parameter_name IN VARCHAR2,
66 p_parameter_value IN VARCHAR2);
67
68 Procedure LoadLineDetails(p_line_item_id IN NUMBER,
69 p_parameter_name IN VARCHAR2,
70 p_parameter_value IN VARCHAR2,
71 p_parameter_reference_value IN VARCHAR2);
72
73 Procedure CallWIEvalProc (p_wi_instance_id IN NUMBER,
74 p_procedure_name IN VARCHAR2,
75 p_order_id IN NUMBER,
76 p_line_item_id IN NUMBER,
77 p_param_val IN VARCHAR2,
78 p_param_ref_val IN VARCHAR2,
79 p_param_eval_val OUT NOCOPY VARCHAR2,
80 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
81 p_return_code OUT NOCOPY NUMBER,
82 p_error_description OUT NOCOPY VARCHAR2);
83
84 Procedure ComputeWIParamValue(p_raise IN VARCHAR2,
85 p_mode IN VARCHAR2,
86 p_wi_instance_id IN NUMBER,
87 p_procedure_name IN VARCHAR2,
88 p_param_val IN VARCHAR2,
89 p_param_ref_val IN VARCHAR2,
90 p_default_value IN VARCHAR2,
91 p_param_eval_val OUT NOCOPY VARCHAR2,
92 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
93 p_return_code OUT NOCOPY NUMBER,
94 p_error_description OUT NOCOPY VARCHAR2);
95
96 Procedure EvaluateWIParamValue(
97 p_order_id IN NUMBER,
98 p_line_item_id IN NUMBER,
99 p_workitem_id IN NUMBER,
100 p_wi_instance_id IN NUMBER,
101 p_parameter_name IN VARCHAR2,
102 p_procedure_name IN VARCHAR2,
103 p_mode IN VARCHAR2,
104 p_param_val IN VARCHAR2,
105 p_param_ref_val IN VARCHAR2,
106 p_param_eval_val OUT NOCOPY VARCHAR2,
107 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
108 p_return_code OUT NOCOPY NUMBER,
109 p_error_description OUT NOCOPY VARCHAR2);
110
111 Procedure SetWIParamValue(
112 p_wi_instance_id IN NUMBER,
113 p_workitem_id IN NUMBER,
114 p_parameter_name IN VARCHAR2,
115 p_parameter_value IN VARCHAR2,
116 p_parameter_ref_value IN VARCHAR2,
117 p_is_value_evaluated IN VARCHAR2,
118 x_return_code OUT NOCOPY NUMBER,
119 x_error_description IN VARCHAR2);
120
121 Procedure CallFAEvalProc (p_fa_instance_id IN NUMBER,
122 p_wi_instance_id IN NUMBER,
123 p_procedure_name IN VARCHAR2,
124 p_order_id IN NUMBER,
125 p_line_item_id IN NUMBER,
126 p_param_val IN VARCHAR2,
127 p_param_ref_val IN VARCHAR2,
128 p_param_eval_val OUT NOCOPY VARCHAR2,
129 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
130 p_return_code OUT NOCOPY NUMBER,
131 p_error_description OUT NOCOPY VARCHAR2);
132
133 Procedure ComputeFAParamValue(p_raise IN VARCHAR2,
134 p_mode IN VARCHAR2,
135 p_fa_instance_id IN NUMBER,
136 p_procedure_name IN VARCHAR2,
137 p_order_id IN NUMBER,
138 p_wi_instance_id IN NUMBER,
139 p_line_item_id IN NUMBER,
140 p_param_val IN VARCHAR2,
141 p_param_ref_val IN VARCHAR2,
142 p_default_value IN VARCHAR2,
143 p_log_flag OUT NOCOPY BOOLEAN,
144 p_param_eval_val OUT NOCOPY VARCHAR2,
145 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
146 p_return_code OUT NOCOPY NUMBER,
147 p_error_description OUT NOCOPY VARCHAR2);
148
149 Procedure SetFAParamValue ( p_fa_instance_id IN NUMBER,
150 p_wi_instance_id IN NUMBER,
151 p_fa_id IN NUMBER,
152 p_parameter_name IN VARCHAR2,
153 p_default_value IN VARCHAR2,
154 p_parameter_value IN VARCHAR2,
155 p_parameter_ref_value IN VARCHAR2,
156 p_eval_flag IN BOOLEAN,
157 p_eval_mode IN VARCHAR2,
158 p_procedure_name IN VARCHAR2,
159 p_order_id IN NUMBER,
160 p_line_item_id IN NUMBER,
161 p_return_code OUT NOCOPY NUMBER,
162 p_error_description OUT NOCOPY VARCHAR2);
163
164 Procedure GetFeConfigInfoText(p_fe IN VARCHAR2,
165 p_fe_id OUT NOCOPY NUMBER,
166 p_fetype_id OUT NOCOPY NUMBER,
167 p_fetype OUT NOCOPY VARCHAR2,
168 p_fe_sw_generic OUT NOCOPY varchar2,
169 p_adapter_type OUT NOCOPY varchar2,
170 p_gen_lookup_id OUT NOCOPY NUMBER,
171 p_connect_proc OUT NOCOPY VARCHAR2,
172 p_disconnect_proc OUT NOCOPY VARCHAR2);
173
174 Procedure GetFeConfigInfoNum (p_fe_id IN NUMBER,
175 p_fe OUT NOCOPY VARCHAR2,
176 p_fetype_id OUT NOCOPY NUMBER,
177 p_fetype OUT NOCOPY VARCHAR2,
178 p_fe_sw_generic OUT NOCOPY varchar2,
179 p_adapter_type OUT NOCOPY varchar2,
180 p_gen_lookup_id OUT NOCOPY NUMBER,
181 p_connect_proc OUT NOCOPY VARCHAR2,
182 p_disconnect_proc OUT NOCOPY VARCHAR2);
183
184 Function GetAttrVal( p_attribute_name IN VARCHAR2,
185 p_fe_id IN NUMBER,
186 p_fe_sw_gen_lookup IN NUMBER) return varchar2 ;
187
188 Function DecodeAttrValue ( p_attribute_value in varchar2) return varchar2;
189
190
191 -- PL/SQL Specification
192
193 /****************************
194 Get a workitem parameter value. The macro
195 $WI.<parameter_name> in FP actually uses this
196 function for runtime value substitution.
197 *****************************/
198 FUNCTION GET_WORKITEM_PARAM_VALUE(
199 p_wi_instance_id IN NUMBER,
200 p_parameter_name IN VARCHAR2)
201 RETURN VARCHAR2
202 IS
203 lv_param_value VARCHAR2(4000);
204 lv_curr_val varchar2(4000);
205 lv_curr_ref_val varchar2(4000);
206 lv_default varchar2(4000);
207 lv_proc varchar2(80);
208 lv_log_flag varchar2(1);
209 lv_order_id number;
210 lv_line_id number;
211 lv_mode varchar2(30);
212 lv_tmp varchar2(4000);
213 lv_ret number;
214 lv_str varchar2(2000);
215 lv_order_id number;
216 lv_line_item_id number;
217 lv_workitem_id number;
218 lv_workitem_name varchar2(50);
219 x_progress varchar2(2000);
220 e_compute_WI_param_failed exception;
221 BEGIN
222
223 Get_WI_Parameter_info (p_wi_instance_id => GET_WORKITEM_PARAM_VALUE.p_wi_instance_id,
224 p_parameter_name => GET_WORKITEM_PARAM_VALUE.p_parameter_name,
225 p_evaluation_procedure => lv_proc,
226 p_evaluation_mode => lv_mode,
227 p_default_value => lv_default,
228 p_parameter_value => lv_curr_val,
229 p_parameter_ref_value => lv_curr_ref_val,
230 p_audit_flag => lv_log_flag,
231 p_workitem_id => lv_workitem_id);
232
233 ComputeWIParamValue ( p_raise => 'Y',
234 p_mode => lv_mode,
235 p_wi_instance_id => GET_WORKITEM_PARAM_VALUE.p_wi_instance_id,
236 p_procedure_name => lv_proc,
237 p_param_val => lv_curr_val,
238 p_param_ref_val => lv_curr_ref_val,
239 p_default_value => lv_default,
240 p_param_eval_val => lv_param_value,
241 p_param_eval_ref_val => lv_tmp,
242 p_return_code => lv_ret,
243 p_error_description => lv_str);
244 IF ( lv_ret <> 0 ) THEN
245 x_progress := 'ComputeWIParamValue failed error code : ' || lv_ret || ' error description : ' || lv_str;
246 RAISE e_compute_WI_param_failed;
247 END IF;
248
249 return lv_param_value;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PARAM_VALUE', 'WI', p_wi_instance_id, x_progress );
254 RAISE;
255
256 END GET_WORKITEM_PARAM_VALUE;
257
258 /*
259 This procedure is used by SEND function to
260 get the additional information regarding
261 an Workitem parameter. It will also return a flag
262 to indicate if the parameter contains decrypted value
263 for an encypted parameter. If it does, the command
264 string which SFM is about to send will not be logged
265 in our command audit trail tables.
266 */
267 PROCEDURE GET_WORKITEM_PARAM_VALUE(
268 p_wi_instance_id IN NUMBER,
269 p_parameter_name IN VARCHAR2,
270 p_param_val OUT NOCOPY VARCHAR2,
271 p_param_ref_val OUT NOCOPY VARCHAR2,
272 p_log_value_flag OUT NOCOPY BOOLEAN,
273 p_return_code OUT NOCOPY number,
274 p_error_description OUT NOCOPY VARCHAR2)
275 IS
276 lv_param_value VARCHAR2(4000);
277 lv_curr_val varchar2(4000);
278 lv_curr_ref_val varchar2(4000);
279 lv_default varchar2(4000);
280 lv_proc varchar2(80);
281 lv_order_id number;
282 lv_line_item_id number;
283 lv_workitem_id number;
284 lv_mode varchar2(30);
285 lv_tmp varchar2(4000);
286 lv_log_flag varchar2(1);
287 BEGIN
288 p_return_code := 0;
289
290 Get_WI_Parameter_info (p_wi_instance_id => GET_WORKITEM_PARAM_VALUE.p_wi_instance_id,
291 p_parameter_name => GET_WORKITEM_PARAM_VALUE.p_parameter_name,
292 p_evaluation_procedure => lv_proc,
293 p_evaluation_mode => lv_mode,
294 p_default_value => lv_default,
295 p_parameter_value => lv_curr_val,
296 p_parameter_ref_value => lv_curr_ref_val,
297 p_audit_flag => lv_log_flag,
298 p_workitem_id => lv_workitem_id);
299
300 if lv_log_flag = 'Y' then
301 p_log_value_flag := TRUE;
302 else
303 p_log_value_flag := FALSE;
304 end if;
305
306 ComputeWIParamValue
307 (p_raise => 'N',
308 p_mode => lv_mode,
309 p_wi_instance_id => GET_WORKITEM_PARAM_VALUE.p_wi_instance_id,
310 p_procedure_name => lv_proc,
311 p_param_val => lv_curr_val,
312 p_param_ref_val => lv_curr_ref_val,
313 p_default_value => lv_default,
314 p_param_eval_val => GET_WORKITEM_PARAM_VALUE.p_param_val,
315 p_param_eval_ref_val => GET_WORKITEM_PARAM_VALUE.p_param_ref_val,
316 p_return_code => GET_WORKITEM_PARAM_VALUE.p_return_code,
317 p_error_description => GET_WORKITEM_PARAM_VALUE.p_error_description);
318
319 IF ( p_return_code <> 0 ) THEN
320 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PARAM_VALUE', 'WI', p_wi_instance_id );
321 END IF;
322
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PARAM_VALUE', 'WI', p_wi_instance_id);
327 RAISE;
328
329 END GET_WORKITEM_PARAM_VALUE;
330
331
335 $WI_REF.<parameter_name> in FP actually uses this
332
333 /****************************
334 Get a workitem parameter reference value. The macro
336 function for runtime value substitution.
337 *****************************/
338
339 FUNCTION GET_WORKITEM_PARAM_REF_VALUE(
340 p_wi_instance_id IN NUMBER,
341 p_parameter_name IN VARCHAR2)
342 RETURN VARCHAR2
343 IS
344 lv_param_value VARCHAR2(4000);
345 lv_curr_val varchar2(4000);
346 lv_curr_ref_val varchar2(4000);
347 lv_default varchar2(4000);
348 lv_proc varchar2(80);
349 lv_log_flag varchar2(1);
350 lv_order_id number;
351 lv_line_item_id number;
352 lv_workitem_id number;
353 lv_mode varchar2(30);
354 lv_tmp varchar2(4000);
355 lv_ret number;
356 lv_str varchar2(2000);
357
358 x_progress varchar2(2000);
359 e_compute_WI_param_failed exception;
360
361 BEGIN
362
363 Get_WI_Parameter_info
364 (p_wi_instance_id => GET_WORKITEM_PARAM_REF_VALUE.p_wi_instance_id,
365 p_parameter_name => GET_WORKITEM_PARAM_REF_VALUE.p_parameter_name,
366 p_evaluation_procedure => lv_proc,
367 p_evaluation_mode => lv_mode,
368 p_default_value => lv_default,
369 p_parameter_value => lv_curr_val,
370 p_parameter_ref_value => lv_curr_ref_val,
371 p_audit_flag => lv_log_flag,
372 p_workitem_id => lv_workitem_id);
373
374 ComputeWIParamValue
375 (p_raise => 'Y',
376 p_mode => lv_mode,
377 p_wi_instance_id => GET_WORKITEM_PARAM_REF_VALUE.p_wi_instance_id,
378 p_procedure_name => lv_proc,
379 p_param_val => lv_curr_val,
380 p_param_ref_val => lv_curr_ref_val,
381 p_default_value => lv_default,
382 p_param_eval_val => lv_tmp,
383 p_param_eval_ref_val => lv_param_value,
384 p_return_code => lv_ret,
385 p_error_description => lv_str);
386
387 IF ( lv_ret <> 0 ) THEN
388 x_progress := 'ComputeWIParamValue failed error code : ' || lv_ret || ' error description : ' || lv_str;
389 RAISE e_compute_WI_param_failed;
390 END IF;
391
392 return lv_param_value;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PARAM_REF_VALUE', 'PARAMETER', p_parameter_name, x_progress );
397 RAISE;
398
399 END GET_WORKITEM_PARAM_REF_VALUE;
400
401 /****************************
402 Get a list of the parameter values for
403 a given workitem instance
404 *****************************/
405 FUNCTION GET_WORKITEM_PARAM_List( -- done need to check where lv_param_list is being used
406 p_wi_instance_id IN NUMBER)
407
408 RETURN XDP_ENGINE.PARAMETER_LIST
409 IS
410 lv_param_list XDP_ENGINE.PARAMETER_LIST;
411 CURSOR lc_param IS
412 select
413 wpr.parameter_name,
414 is_value_evaluated,
415 decode(parameter_value,NULL,wpr.default_value,wdl.parameter_value) param_value,
416 parameter_ref_value
417 from
418 xdp_wi_parameters wpr,
419 xdp_worklist_details wdl
420 where
421 wpr.parameter_name = wdl.parameter_name and
422 wpr.workitem_id = wdl.workitem_id and
423 wdl.workitem_instance_id = p_wi_instance_id;
424
425 lv_index NUMBER := 0;
426 BEGIN
427
428 FOR lv_param_rec in lc_param LOOP
429 lv_index := lv_index + 1;
430 lv_param_list(lv_index).parameter_name := lv_param_rec.parameter_name;
431 lv_param_list(lv_index).IS_VALUE_EVALUATED_FLAG :=
432 lv_param_rec.IS_VALUE_EVALUATED ;
433 lv_param_list(lv_index).PARAMETER_VALUE := lv_param_rec.PARAM_VALUE ;
434 lv_param_list(lv_index).PARAMETER_REFERENCE_VALUE :=
435 lv_param_rec.PARAMETER_REF_VALUE ;
436 END LOOP;
437
438 return lv_param_list;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PARAM_List', 'WI', p_wi_instance_id );
443 RAISE;
444
445 END GET_WORKITEM_PARAM_List;
446
447
448 /****************************
449 Set the workitem parameter value for
450 a given workitem instance. The parameter
451 evaluation procedure will NOT be executed.
452
453 --p_evaluation_required is obsolete from 11.5.6
454
455 -- Date: 13-Jan-2005 Author: DPUTHIYE. Bug#: 4083708
456 -- p_evaluation_required is again supported from 11.5.9
457 -- to fix the bug 4083708. This API is called by XDP_OA_UTIL.Add_WI_To_Line
458 -- and is required to set a param value with evaluation_required = TRUE
459 -- APIs will need to explicitly set evaluation_required = TRUE to force evaluation
460 *****************************/
461
462 PROCEDURE Set_Workitem_Param_value(
463 p_wi_instance_id IN NUMBER,
464 p_parameter_name IN VARCHAR2,
465 p_parameter_value IN VARCHAR2,
466 p_parameter_reference_value IN VARCHAR2 DEFAULT NULL,
467 p_evaluation_required IN BOOLEAN DEFAULT FALSE)
468 IS
469 l_return_code NUMBER;
470 l_error_description VARCHAR2(2000);
471 l_param_evaluated CHAR(1) := 'Y'; --introduced to fix bug 4083708
472 x_progress varchar2(2000);
473 Set_WI_param_failed exception;
474
475 BEGIN
476
480
477 if ( p_evaluation_required = TRUE ) then -- Fixing bug 4083708
478 l_param_evaluated := 'N';
479 end if;
481 SetWIParamValue(
482 p_wi_instance_id =>p_wi_instance_id,
483 p_workitem_id => NULL,
484 p_parameter_name =>p_parameter_name,
485 p_is_value_evaluated => l_param_evaluated,
486 p_parameter_value =>p_parameter_value,
487 p_parameter_ref_value => p_parameter_reference_value,
488 x_return_code => l_return_code,
489 x_error_description => l_error_description
490 );
491
492 IF ( l_return_code <> 0 ) THEN
493 x_progress := 'SetWIParamValue failed error code : ' || l_return_code || ' error_description : ' || l_error_description;
494 RAISE Set_WI_param_failed;
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PARAM_VALUE','WI', p_wi_instance_id, x_progress );
500 RAISE;
501
502 END SET_WORKITEM_PARAM_VALUE;
503
504 /***************************
505 Get the Fulfillment Date for a Workitem Instance
506 ***************************/
507 FUNCTION GET_WORKITEM_PROV_DATE(
508 p_wi_instance_id IN NUMBER)
509 RETURN DATE
510 IS
511 lv_prov_date DATE;
512 BEGIN
513 select
514 provisioning_date
515 into
516 lv_prov_date
517 from
518 XDP_FULFILL_WORKLIST
519 where
520 workitem_instance_id = p_wi_instance_id;
521
522 return lv_prov_date;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 XDPCORE.context('XDP_ENGINE', 'GET_WORKITEM_PROV_DATE','WI', p_wi_instance_id );
527 RAISE;
528
529 END GET_WORKITEM_PROV_DATE;
530
531
532 /****************************
533 Set the Fulfillment Date for a Workitem Instance
534 If the Workitem is being processed or already processed an error code is returned
535 ****************************/
536
537
538 PROCEDURE SET_WORKITEM_PROV_DATE(
539 p_wi_instance_id in NUMBER,
540 p_prov_date IN DATE,
541 p_return_code OUT NOCOPY NUMBER,
542 p_error_description OUT NOCOPY VARCHAR2)
543 IS
544 cursor c_update_prov_date is
545 select status_code status
546 from XDP_FULFILL_WORKLIST
547 where workitem_instance_id = p_wi_instance_id
548 for update of provisioning_date nowait;
549
550 e_InvalidWIStatusException exception;
551 lv_wi_name VARCHAR2(200);
552 lv_message_params VARCHAR2(1000);
553 BEGIN
554 p_return_code := 0;
555
556 SavePoint UpdateProvDate;
557
558 FOR v_update_prov_date in c_update_prov_date LOOP
559 if v_update_prov_date.status NOT in ('IN PROGRESS','SUCCESS','SUCCESS_WITH_OVERRIDE','CANCELED') then
560
561 update XDP_FULFILL_WORKLIST
562 set last_updated_by = FND_GLOBAL.USER_ID,
563 last_update_date = sysdate,
564 last_update_login = FND_GLOBAL.LOGIN_ID,
565 provisioning_date = p_prov_date
566 where current of c_update_prov_date;
567
568 else
569 rollback to UpdateProvDate;
570 raise e_InvalidWIStatusException;
571 end if;
572 END LOOP;
573
574 EXCEPTION
575 when e_InvalidWIStatusException then
576
577 lv_wi_name := XDPCORE_WI.get_display_name( p_wi_instance_id );
578
579 lv_message_params := 'WORK_ITEM='||lv_wi_name||'#XDP#';
580
581 XDPCORE.error_context( 'WI', p_wi_instance_id, 'XDP_WI_UPDATE_NOT_ALLOWED', lv_message_params);
582 XDPCORE.context('XDP_ENGINE', 'SET_WORKITEM_PROV_DATE','WI', p_wi_instance_id );
583 XDPCORE.RAISE;
584
585 when others then
586 rollback to UpdateProvDate;
587 XDPCORE.context('XDP_ENGINE', 'SET_WORKITEM_PROV_DATE','WI', p_wi_instance_id );
588 p_return_code := SQLCODE;
589 p_error_description := SQLERRM;
590 END SET_WORKITEM_PROV_DATE;
591
592
593 /****************************
594 Get an FA parameter value. The macro
595 $FA.<parameter_name> in FP actually uses this
596 function for runtime value substitution.
597 *****************************/
598
599 FUNCTION GET_FA_PARAM_VALUE(
600 p_fa_instance_id IN NUMBER,
601 p_parameter_name IN VARCHAR2)
602 RETURN VARCHAR2
603 IS
604 lv_param_value varchar2(4000);
605 lv_curr_val varchar2(4000);
606 lv_curr_ref_val varchar2(4000);
607 lv_default varchar2(4000);
608 lv_proc varchar2(80);
609 lv_order_id number;
610 lv_line_item_id number;
611 lv_wi_id number;
612 lv_fa_id number;
613 lv_mode varchar2(30);
614 lv_tmp varchar2(4000);
615 lv_ret number;
616 lv_str varchar2(2000);
617 lv_log_flag boolean;
618
619 x_progress varchar2(2000);
620 e_compute_FA_param_failed exception;
621
622 BEGIN
623
624 Get_FA_Parameter_info (p_fa_instance_id => GET_FA_PARAM_VALUE.p_fa_instance_id,
625 p_parameter_name => GET_FA_PARAM_VALUE.p_parameter_name,
626 p_evaluation_procedure => lv_proc,
627 p_fa_id => lv_fa_id,
628 p_default_value => lv_default,
629 p_parameter_value => lv_curr_val,
630 p_parameter_ref_value => lv_curr_ref_val,
634 p_mode => lv_mode,
631 p_audit_flag => lv_mode);
632
633 ComputeFAParamValue ( p_raise => 'Y',
635 p_fa_instance_id => p_fa_instance_id,
636 p_procedure_name => lv_proc,
637 p_order_id => NULL,
638 p_wi_instance_id => NULL,
639 p_line_item_id=> NULL,
640 p_param_val => lv_curr_val,
641 p_param_ref_val => lv_curr_ref_val,
642 p_default_value => lv_default,
643 p_log_flag => lv_log_flag,
644 p_param_eval_val => lv_param_value,
645 p_param_eval_ref_val => lv_tmp,
646 p_return_code => lv_ret,
647 p_error_description => lv_str);
648
649 IF ( lv_ret <> 0 ) THEN
650 x_progress := 'ComputeFAParamValue failed error code : ' || lv_ret || ' error_description : ' || lv_str;
651 RAISE e_compute_FA_param_failed;
652 END IF;
653
654 return lv_param_value;
655
656 EXCEPTION
657 WHEN OTHERS THEN
658 XDPCORE.context('XDP_ENGINE', 'GET_FA_PARAM_VALUE', 'FA', p_fa_instance_id, x_progress );
659 RAISE;
660
661 END GET_FA_PARAM_VALUE;
662
663 /****************************
664 Get an FA parameter reference value. The macro
665 $FA_REF.<parameter_name> in FP actually uses this
666 function for runtime value substitution.
667 *****************************/
668
669 FUNCTION GET_FA_PARAM_REF_VALUE(
670 p_fa_instance_id IN NUMBER,
671 p_parameter_name IN VARCHAR2)
672 RETURN VARCHAR2
673 IS
674 lv_param_value varchar2(4000);
675 lv_curr_val varchar2(4000);
676 lv_curr_ref_val varchar2(4000);
677 lv_default varchar2(4000);
678 lv_proc varchar2(80);
679 lv_order_id number;
680 lv_line_item_id number;
681 lv_wi_id number;
682 lv_fa_id number;
683 lv_mode varchar2(30);
684 lv_tmp varchar2(4000);
685 lv_ret number;
686 lv_str varchar2(2000);
687 lv_log_flag boolean;
688
689 x_progress varchar2(2000);
690 e_compute_FA_param_failed exception;
691
692 BEGIN
693
694 Get_FA_Parameter_info (p_fa_instance_id => GET_FA_PARAM_REF_VALUE.p_fa_instance_id,
695 p_parameter_name => GET_FA_PARAM_REF_VALUE.p_parameter_name,
696 p_evaluation_procedure => lv_proc,
697 p_fa_id => lv_fa_id,
698 p_default_value => lv_default,
699 p_parameter_value => lv_curr_val,
700 p_parameter_ref_value => lv_curr_ref_val,
701 p_audit_flag => lv_mode);
702
703 ComputeFAParamValue ( p_raise => 'Y',
704 p_mode => lv_mode,
705 p_fa_instance_id => p_fa_instance_id,
706 p_procedure_name => lv_proc,
707 p_order_id => NULL,
708 p_wi_instance_id => NULL,
709 p_line_item_id=> NULL,
710 p_param_val => lv_curr_val,
711 p_param_ref_val => lv_curr_ref_val,
712 p_default_value => lv_default,
713 p_log_flag => lv_log_flag,
714 p_param_eval_val => lv_tmp,
715 p_param_eval_ref_val => lv_param_value,
716 p_return_code => lv_ret,
717 p_error_description => lv_str);
718
719 IF ( lv_ret <> 0 ) THEN
720 x_progress := 'ComputeFAParamValue failed error code : ' || lv_ret || ' error_description : ' || lv_str;
721 RAISE e_compute_FA_param_failed;
722 END IF;
723
724 RETURN lv_param_value;
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 XDPCORE.context('XDP_ENGINE', 'GET_FA_PARAM_REF_VALUE', 'FA', p_fa_instance_id, x_progress );
729 RAISE;
730
731 END GET_FA_PARAM_REF_VALUE;
732
733 /****************************
734 This procedure is used by SEND function to
735 get the additional information regarding
736 an FA parameter. It will also return a flag
737 to indicate if the parameter contains decrypted value
738 for an encypted parameter. If it does, the command
739 string which SFM is about to send will not be logged
740 in our command audit trail tables.
741 *****************************/
742 PROCEDURE GET_FA_PARAM(
743 p_fa_instance_id IN NUMBER,
744 p_parameter_name IN VARCHAR2,
745 p_param_val OUT NOCOPY VARCHAR2,
746 p_param_ref_val OUT NOCOPY VARCHAR2,
747 p_log_value_flag OUT NOCOPY BOOLEAN,
748 p_return_code OUT NOCOPY number,
749 p_error_description OUT NOCOPY VARCHAR2)
750 IS
751 lv_curr_val varchar2(4000);
752 lv_curr_ref_val varchar2(4000);
753 lv_default varchar2(4000);
754 lv_proc varchar2(80);
755 lv_order_id number;
756 lv_line_item_id number;
757 lv_wi_id number;
758 lv_fa_id number;
759 lv_mode varchar2(30);
760 lv_tmp varchar2(4000);
761 lv_ret number;
762 lv_str varchar2(2000);
763 BEGIN
764 p_return_code := 0;
765 Get_FA_Parameter_info (p_fa_instance_id => GET_FA_PARAM.p_fa_instance_id,
766 p_parameter_name => GET_FA_PARAM.p_parameter_name,
767 p_evaluation_procedure => lv_proc,
768 p_fa_id => lv_fa_id,
769 p_default_value => lv_default,
770 p_parameter_value => lv_curr_val,
771 p_parameter_ref_value => lv_curr_ref_val,
772 p_audit_flag => lv_mode);
773
774 ComputeFAParamValue ( p_raise => 'N',
775 p_mode => lv_mode,
776 p_fa_instance_id => p_fa_instance_id,
777 p_procedure_name => lv_proc,
778 p_order_id => NULL,
779 p_wi_instance_id => NULL,
780 p_line_item_id=> NULL,
784 p_log_flag => p_log_value_flag,
781 p_param_val => lv_curr_val,
782 p_param_ref_val => lv_curr_ref_val,
783 p_default_value => lv_default,
785 p_param_eval_val => p_param_val,
786 p_param_eval_ref_val => p_param_ref_val,
787 p_return_code => p_return_code,
788 p_error_description => p_error_description);
789
790 IF ( p_return_code <> 0 ) THEN
791 XDPCORE.context('XDP_ENGINE', 'GET_FA_PARAM', 'FA', p_fa_instance_id );
792 END IF;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 XDPCORE.context('XDP_ENGINE', 'GET_FA_PARAM', 'FA', p_fa_instance_id );
797 RAISE;
798
799 END GET_FA_PARAM;
800
801 /****************************
802 Get a list of the parameter values for
803 a given FA instance
804 *****************************/
805 FUNCTION GET_FA_PARAM_List( --Done
806 p_fa_instance_id IN NUMBER)
807 RETURN XDP_ENGINE.PARAMETER_LIST
808 IS
809 lv_param_list XDP_ENGINE.PARAMETER_LIST;
810 CURSOR lc_param IS
811 select
812 fpr.parameter_name,
813 is_value_evaluated,
814 decode(parameter_value,NULL,fpr.default_value,fdl.parameter_value) param_value,
815 parameter_ref_value
816 from
817 xdp_fa_parameters fpr,
818 xdp_fa_details fdl
819 where
820 fpr.fulfillment_action_id = fdl.fulfillment_action_id and
821 fpr.parameter_name = fdl.parameter_name and
822 fdl.fa_instance_id = p_fa_instance_id;
823
824 lv_index NUMBER := 0;
825 BEGIN
826
827 FOR lv_param_rec in lc_param LOOP
828 lv_index := lv_index + 1;
829 lv_param_list(lv_index).parameter_name := lv_param_rec.parameter_name;
830 lv_param_list(lv_index).IS_VALUE_EVALUATED_FLAG :=
831 lv_param_rec.IS_VALUE_EVALUATED ;
832 lv_param_list(lv_index).PARAMETER_VALUE := lv_param_rec.PARAM_VALUE ;
833 lv_param_list(lv_index).PARAMETER_REFERENCE_VALUE :=
834 lv_param_rec.PARAMETER_REF_VALUE ;
835 END LOOP;
836
837 return lv_param_list;
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 XDPCORE.context('XDP_ENGINE', 'GET_FA_PARAM_List', 'FA', p_fa_instance_id );
842 RAISE;
843
844 END GET_FA_PARAM_List;
845
846 /****************************
847 Set the FA parameter value for
848 a given FA instance. The parameter
849 evaluation procedure will be executed if
850 applicable.
851 *****************************/
852 PROCEDURE Set_FA_Param_value(
853 p_fa_instance_id IN NUMBER,
854 p_parameter_name IN VARCHAR2,
855 p_parameter_value IN VARCHAR2,
856 p_parameter_reference_value IN VARCHAR2 DEFAULT NULL,
857 p_evaluation_required IN BOOLEAN DEFAULT FALSE)
858 IS
859
860 lv_proc varchar2(80);
861 lv_exists varchar2(1) := 'N';
862 lv_eval_mode varchar2(30);
863 lv_param_id number;
864 lv_wi_id number;
865 lv_fa_id number;
866 lv_order_id number;
867 lv_line_item_id number;
868 lv_wi_instance_id number;
869 lv_eval_val varchar2(4000);
870 lv_eval_ref_val varchar2(4000);
871 lv_default_val varchar2(4000);
872 lv_ret number;
873 lv_str varchar2(2000);
874 lv_eval_flag varchar2(1);
875
876 x_progress varchar2(2000);
877 set_FA_param_failed exception;
878
879
880 CURSOR c_GetFADetails is
881 select
882 fwt.order_id,
883 fwt.line_item_id,
884 frt.workitem_instance_id,
885 fpr.parameter_name,
886 NVL(log_in_audit_trail_flag,'Y') log_flag,
887 evaluation_procedure,
888 fpr.default_value,
889 frt.fulfillment_action_id
890 from
891 xdp_fa_runtime_list frt,
892 xdp_fa_parameters fpr,
893 XDP_FULFILL_WORKLIST fwt
894 where
895 frt.fa_instance_id = p_fa_instance_id and
896 frt.workitem_instance_id = fwt.workitem_instance_id and
897 frt.fulfillment_action_id = fpr.fulfillment_action_id and
898 fpr.parameter_name = p_parameter_name;
899
900 BEGIN
901 for v_GetFADetails in c_GetFADetails loop
902 lv_order_id := v_GetFADetails.order_id;
903 lv_line_item_id := v_GetFADetails.line_item_id;
904 lv_wi_instance_id := v_GetFADetails.workitem_instance_id;
905 lv_eval_mode := v_GetFADetails.log_flag;
906 lv_proc := v_GetFADetails.evaluation_procedure;
907 lv_default_val := v_GetFADetails.default_value;
908 lv_fa_id := v_GetFADetails.fulfillment_action_id;
909
910 lv_exists := 'Y';
911
912 end loop;
913
914
915 if lv_exists = 'N' then
916 raise no_data_found;
917 end if;
918
919 SetFAParamValue
920 (p_fa_instance_id => Set_FA_Param_value.p_fa_instance_id,
921 p_wi_instance_id => lv_wi_instance_id,
922 p_fa_id => lv_fa_id,
923 p_parameter_name => Set_FA_Param_value.p_parameter_name,
924 p_default_value => lv_default_val,
925 p_parameter_value => Set_FA_Param_value.p_parameter_value,
926 p_parameter_ref_value => Set_FA_Param_value.p_parameter_reference_value,
927 p_eval_flag => Set_FA_Param_value.p_evaluation_required,
928 p_eval_mode => lv_eval_mode,
929 p_procedure_name => lv_proc,
930 p_order_id => lv_order_id,
934
931 p_line_item_id => lv_line_item_id,
932 p_return_code => lv_ret,
933 p_error_description => lv_str);
935 IF ( lv_ret <> 0 ) THEN
936 x_progress := 'SetFAParamValue failed error code : ' || lv_ret || ' error_description : ' || lv_str;
937 RAISE set_FA_param_failed;
938 END IF;
939
940 EXCEPTION
941
942 WHEN OTHERS THEN
943 XDPCORE.context('XDP_ENGINE', 'SET_FA_PARAM_VALUE', 'FA', p_fa_instance_id, x_progress );
944 RAISE;
945
946 END SET_FA_PARAM_VALUE;
947
948
949 /****************************
950 Get the value of an order parameter
951 *****************************/
952 FUNCTION GET_ORDER_PARAM_VALUE(
953 p_order_id IN NUMBER,
954 p_parameter_name IN VARCHAR2)
955 RETURN VARCHAR2
956 IS
957 lv_param_value varchar2(4000);
958
959 CURSOR c_GetOrderParam is
960 select order_parameter_value
961 from xdp_order_parameters
962 where
963 order_id = p_order_id and
964 order_parameter_name = p_parameter_name;
965
966 lv_exists varchar2(1) := 'N';
967 BEGIN
968 for v_GetOrderParam in c_GetOrderParam loop
969 lv_param_value := v_GetOrderParam.order_parameter_value;
970 lv_exists := 'Y';
971 end loop;
972
973 if lv_exists = 'N' then
974 raise no_data_found;
975 end if;
976
977 return lv_param_value;
978
979
980 END GET_ORDER_PARAM_VALUE;
981
982 /****************************
983 Get a list of the order parameter values
984 for a given order
985 *****************************/
986 FUNCTION GET_ORDER_PARAM_List(
987 p_order_id IN NUMBER )
988 RETURN XDP_ENGINE.PARAMETER_LIST
989 IS
990 lv_param_list XDP_ENGINE.PARAMETER_LIST;
991 CURSOR lc_param IS
992 select
993 order_parameter_name parameter_name,
994 order_parameter_value parameter_value
995 from
996 xdp_order_parameters
997 where
998 order_id = p_order_id;
999
1000 lv_index NUMBER := 0;
1001 BEGIN
1002
1003 FOR lv_param_rec in lc_param LOOP
1004 lv_index := lv_index + 1;
1005 lv_param_list(lv_index).parameter_name := lv_param_rec.parameter_name;
1006 lv_param_list(lv_index).PARAMETER_VALUE := lv_param_rec.PARAMETER_VALUE ;
1007 END LOOP;
1008
1009
1010 return lv_param_list;
1011
1012
1013 END GET_ORDER_PARAM_LIST;
1014
1015 /****************************
1016 Set an order parameter value
1017 *****************************/
1018 PROCEDURE Set_ORDER_Param_value(
1019 p_order_id IN NUMBER,
1020 p_parameter_name IN VARCHAR2,
1021 p_parameter_value IN VARCHAR2)
1022 IS
1023 lv_exists varchar2(1);
1024 BEGIN
1025
1026 lv_exists := DoesOrderParamExist
1027 (p_order_id => Set_Order_Param_Value.p_order_id,
1028 p_parameter_name => Set_Order_Param_value.p_parameter_name);
1029
1030 IF lv_exists = 'Y' then
1031 update xdp_order_parameters
1032 set
1033 last_updated_by = FND_GLOBAL.USER_ID,
1034 last_update_date = sysdate,
1035 last_update_login = FND_GLOBAL.LOGIN_ID,
1036 order_parameter_value = p_parameter_value
1037 where
1038 order_id = p_order_id and
1039 order_parameter_name = p_parameter_name;
1040 ELSE
1041 LoadOrderParameters(p_order_id => set_order_param_value.p_order_id,
1042 p_parameter_name => set_order_param_value.p_parameter_name,
1043 p_parameter_value => set_order_param_value.p_parameter_value);
1044
1045 END IF;
1046
1047 END SET_ORDER_PARAM_VALUE;
1048
1049 /****************************
1050 Get the value of a line parameter
1051 *****************************/
1052 FUNCTION GET_line_PARAM_VALUE(
1053 p_line_item_id IN NUMBER,
1054 p_parameter_name IN VARCHAR2)
1055 RETURN VARCHAR2
1056 IS
1057 lv_param_value varchar2(4000);
1058
1059 CURSOR c_GetLineParam is
1060 select parameter_value
1061 from XDP_ORDER_LINEITEM_DETS
1062 where
1063 line_item_id = p_line_item_id and
1064 line_parameter_name = p_parameter_name;
1065
1066 lv_exists varchar2(1) := 'N';
1067 BEGIN
1068 for v_GetLineParam in c_GetLineParam loop
1069 lv_param_value := v_GetLineParam.parameter_value;
1070 lv_exists := 'Y';
1071 end loop;
1072
1073 if lv_exists = 'N' then
1074 raise no_data_found;
1075 end if;
1076
1077 return lv_param_value;
1078
1079 END GET_LINE_PARAM_VALUE;
1080
1081 /****************************
1082 Get the reference value of a line parameter
1083 *****************************/
1084 FUNCTION GET_line_PARAM_REF_VALUE(
1085 p_line_item_id IN NUMBER,
1086 p_parameter_name IN VARCHAR2)
1087 RETURN VARCHAR2
1088 IS
1089 lv_param_value varchar2(4000);
1090
1091 CURSOR c_GetLineParam is
1092 select parameter_reference_value
1093 from XDP_ORDER_LINEITEM_DETS
1094 where
1095 line_item_id = p_line_item_id and
1099
1096 line_parameter_name = p_parameter_name;
1097
1098 lv_exists varchar2(1) := 'N';
1100 BEGIN
1101 for v_GetLineParam in c_GetLineParam loop
1102 lv_param_value := v_GetLineParam.parameter_reference_value;
1103 lv_exists := 'Y';
1104 end loop;
1105
1106 if lv_exists = 'N' then
1107 raise no_data_found;
1108 end if;
1109
1110 return lv_param_value;
1111
1112 END GET_LINE_PARAM_REF_VALUE;
1113
1114 /********************************************
1115 Add a runtime parameter to a given line
1116 *******************************************/
1117 PROCEDURE ADD_LINE_PARAM(
1118 p_line_item_id IN NUMBER,
1119 p_parameter_name IN VARCHAR2,
1120 p_parameter_value IN VARCHAR2,
1121 p_parameter_reference_value IN VARCHAR2 DEFAULT NULL)
1122 IS
1123 lv_exists varchar2(1);
1124
1125 BEGIN
1126
1127 lv_exists := DoesLineParamExist
1128 (p_line_item_id => Add_line_param.p_line_item_id,
1129 p_parameter_name => Add_line_param.p_parameter_name);
1130
1131 if lv_exists = 'Y' then
1132 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_PARAM_NAME_EXISTS');
1133 FND_MESSAGE.SET_TOKEN('PARAM_NAME', p_parameter_name);
1134 APP_EXCEPTION.RAISE_EXCEPTION;
1135 else
1136 LoadLineDetails
1137 (p_line_item_id => Add_line_param.p_line_item_id,
1138 p_parameter_name => Add_line_param.p_parameter_name,
1139 p_parameter_value => Add_line_param.p_parameter_value,
1140 p_parameter_reference_value => Add_line_param.p_parameter_reference_value);
1141
1142 end if;
1143
1144 END Add_Line_Param;
1145
1146 /********************************************
1147 Update an existing parameter value for a given line
1148 *******************************************/
1149 PROCEDURE Set_LINE_PARAM_Value(
1150 p_line_item_id IN NUMBER,
1151 p_parameter_name IN VARCHAR2,
1152 p_parameter_value IN VARCHAR2,
1153 p_parameter_reference_value IN VARCHAR2 DEFAULT NULL)
1154 IS
1155 lv_exists varchar2(1);
1156
1157 BEGIN
1158
1159 lv_exists := DoesLineParamExist
1160 (p_line_item_id => set_line_param_value.p_line_item_id,
1161 p_parameter_name => set_line_param_value.p_parameter_name);
1162
1163 if lv_exists = 'N' then
1164 raise no_data_found;
1165 end if;
1166
1167 update XDP_ORDER_LINEITEM_DETS
1168 set
1169 last_updated_by = FND_GLOBAL.USER_ID,
1170 last_update_date = sysdate,
1171 last_update_login = FND_GLOBAL.LOGIN_ID,
1172 parameter_value = p_parameter_value,
1173 parameter_reference_value = p_parameter_reference_value
1174 where
1175 line_item_id = p_line_item_id and
1176 line_parameter_name = p_parameter_name;
1177
1178 END Set_Line_Param_Value;
1179
1180
1181 /****************************
1182 Retrieve the configuration data
1183 for a given fulfillment element
1184 *****************************/
1185 PROCEDURE Get_FE_ConfigInfo(
1186 p_fe IN VARCHAR2,
1187 p_fe_id OUT NOCOPY NUMBER, /* Internal ID for the FE*/
1188 p_fetype_id OUT NOCOPY NUMBER, /* Internal id for the FE type */
1189 p_fetype OUT NOCOPY VARCHAR2, /* name of the FETYPE*/
1190 p_fe_sw_generic OUT NOCOPY varchar2,/* The current software generic of the FE*/
1191 p_adapter_type OUT NOCOPY varchar2 /* the current adapter type of the FE */)
1192 IS
1193 lv_dummynum number;
1194 lv_dummyvar varchar2(80);
1195 BEGIN
1196
1197 GetFeConfigInfoText(p_fe => Get_FE_ConfigInfo.p_fe,
1198 p_fe_id => Get_FE_ConfigInfo.p_fe_id,
1199 p_fetype_id => Get_FE_ConfigInfo.p_fetype_id,
1200 p_fetype => Get_FE_ConfigInfo.p_fetype,
1201 p_fe_sw_generic => Get_FE_ConfigInfo.p_fe_sw_generic,
1202 p_adapter_type => Get_FE_ConfigInfo.p_adapter_type,
1203 p_gen_lookup_id => lv_dummynum,
1204 p_connect_proc => lv_dummyvar,
1205 p_disconnect_proc => lv_dummyvar);
1206
1207 END GET_FE_ConfigInfo;
1208
1209 /****************************
1210 Retrieve the configuration data
1211 for a given fulfillment element
1212 *****************************/
1213 PROCEDURE Get_FE_ConfigInfo(
1214 p_fe_id IN NUMBER,
1215 p_fe_name OUT NOCOPY VARCHAR2, /* name of the FE*/
1216 p_fetype_id OUT NOCOPY NUMBER, /* Internal id for the FE type */
1217 p_fetype OUT NOCOPY VARCHAR2, /* name of the FETYPE*/
1218 p_fe_sw_generic OUT NOCOPY varchar2,/* The current software generic of the FE*/
1219 p_adapter_type OUT NOCOPY varchar2 /* the current adapter type of the FE */)
1220 IS
1221
1222 lv_dummynum number;
1223 lv_dummyvar varchar2(80);
1224 BEGIN
1225 GetFeConfigInfoNum (p_fe_id => Get_FE_ConfigInfo.p_fe_id,
1226 p_fe => Get_FE_ConfigInfo.p_fe_name,
1227 p_fetype_id => Get_FE_ConfigInfo.p_fetype_id,
1228 p_fetype => Get_FE_ConfigInfo.p_fetype,
1229 p_fe_sw_generic => Get_FE_ConfigInfo.p_fe_sw_generic,
1230 p_adapter_type => Get_FE_ConfigInfo.p_adapter_type,
1231 p_gen_lookup_id => lv_dummynum,
1232 p_connect_proc => lv_dummyvar,
1233 p_disconnect_proc => lv_dummyvar);
1234
1235 null;
1236 END GET_FE_ConfigInfo;
1237
1238 /********************************
1242 FUNCTION Get_FE_AttributeVal(
1239 ** Retrieve the FE Attribute value
1240 ** for a given fulfillment element
1241 **********************************/
1243 p_fe_name IN VARCHAR2,
1244 p_attribute_name IN VARCHAR2)
1245 return varchar2
1246 IS
1247 lv_attrVal varchar2(4000);
1248 lv_fe_id NUMBER;
1249 lv_fetype_id NUMBER;
1250 lv_fetype varchar2(40);
1251 lv_fe_sw_generic varchar2(40);
1252 lv_adapter_type varchar2(40);
1253 lv_gen_lookup_id number;
1254
1255 lv_dummyvar varchar2(80);
1256
1257 BEGIN
1258 GetFeConfigInfoText(p_fe => Get_FE_AttributeVal.p_fe_name,
1259 p_fe_id => lv_fe_id,
1260 p_fetype_id => lv_fetype_id,
1261 p_fetype => lv_fetype,
1262 p_fe_sw_generic => lv_fe_sw_generic,
1263 p_adapter_type => lv_adapter_type,
1264 p_gen_lookup_id => lv_gen_lookup_id,
1265 p_connect_proc => lv_dummyvar,
1266 p_disconnect_proc => lv_dummyvar);
1267
1268 lv_attrVal := GetAttrVal(
1269 p_attribute_name => Get_FE_AttributeVal.p_attribute_name,
1270 p_fe_id => lv_fe_id,
1271 p_fe_sw_gen_lookup => lv_gen_lookup_id);
1272
1273 return lv_attrVal;
1274
1275 END Get_FE_AttributeVal;
1276
1277 /********************************
1278 ** Retrieve the FE Attribute value
1279 ** for a given fulfillment element
1280 **********************************/
1281 FUNCTION Get_FE_AttributeVal(
1282 p_fe_id IN NUMBER,
1283 p_attribute_name IN VARCHAR2)
1284 return varchar2
1285 IS
1286 lv_attrVal varchar2(4000);
1287 lv_fe_name varchar2(40);
1288 lv_fetype_id NUMBER;
1289 lv_fetype varchar2(40);
1290 lv_fe_sw_generic varchar2(40);
1291 lv_adapter_type varchar2(40);
1292
1293 lv_gen_lookup_id number;
1294
1295 lv_dummyvar varchar2(80);
1296 BEGIN
1297 GetFeConfigInfoNum (p_fe_id => Get_FE_AttributeVal.p_fe_id,
1298 p_fe => lv_fe_name,
1299 p_fetype_id => lv_fetype_id,
1300 p_fetype => lv_fetype,
1301 p_fe_sw_generic => lv_fe_sw_generic,
1302 p_adapter_type => lv_adapter_type,
1303 p_gen_lookup_id => lv_gen_lookup_id,
1304 p_connect_proc => lv_dummyvar,
1305 p_disconnect_proc => lv_dummyvar);
1306
1307 lv_attrVal := GetAttrVal(
1308 p_attribute_name => Get_FE_AttributeVal.p_attribute_name,
1309 p_fe_id => Get_FE_AttributeVal.p_fe_id,
1310 p_fe_sw_gen_lookup => lv_gen_lookup_id);
1311
1312 return lv_attrVal;
1313
1314 END Get_FE_AttributeVal;
1315
1316 /*
1317 Retrieve all the FE Attribute value
1318 for a given fulfillment element
1319 */
1320 FUNCTION Get_FE_AttributeVal_List(
1321 p_fe_name in varchar2)
1322 RETURN XDP_TYPES.ORDER_PARAMETER_LIST
1323 IS
1324 lv_attrVal varchar2(4000);
1325 lv_fe_id NUMBER;
1326 lv_fetype_id NUMBER;
1327 lv_fetype varchar2(40);
1328 lv_fe_sw_generic varchar2(40);
1329 lv_adapter_type varchar2(40);
1330 lv_conceal_data VARCHAR2(10);
1331 lv_gen_lookup_id number;
1332 lv_attr_list XDP_TYPES.ORDER_PARAMETER_LIST;
1333 lv_index number := 0;
1334
1335 lv_dummyvar varchar2(80);
1336
1337 CURSOR lc_FE_Attr(l_fe_id number,l_gen_lookup_id number) IS
1338 select
1339 fan.fe_attribute_name fe_attribute_name,
1340 decode(fe_attribute_value,NULL,default_value,fe_attribute_value) fe_attribute_value,
1341 fan.conceal_data conceal_data
1342 from
1343 xdp_fe_attribute_def fan,
1344 (select fe_attribute_id, fe_attribute_value
1345 from
1346 XDP_FE_ATTRIBUTE_VAL fae,
1347 XDP_FE_GENERIC_CONFIG fge
1348 where
1349 fae.fe_generic_config_id = fge.fe_generic_config_id and
1350 fge.fe_id = l_fe_id and
1351 fge.fe_sw_gen_lookup_id = l_gen_lookup_id ) fae2
1352 where
1353 fan.fe_sw_gen_lookup_id = l_gen_lookup_id and
1354 fan.fe_attribute_id = fae2.fe_attribute_id(+);
1355 BEGIN
1356
1357 GetFeConfigInfoText(p_fe => Get_FE_AttributeVal_List.p_fe_name,
1358 p_fe_id => lv_fe_id,
1359 p_fetype_id => lv_fetype_id,
1360 p_fetype => lv_fetype,
1361 p_fe_sw_generic => lv_fe_sw_generic,
1362 p_adapter_type => lv_adapter_type,
1363 p_gen_lookup_id => lv_gen_lookup_id,
1364 p_connect_proc => lv_dummyvar,
1365 p_disconnect_proc => lv_dummyvar);
1366
1367
1368 FOR lv_attr_rec IN lc_FE_Attr(lv_fe_id,lv_gen_lookup_id) LOOP
1369 lv_index := lv_index + 1;
1370 lv_attrVal := lv_attr_rec.fe_attribute_value;
1371
1372 lv_attr_list(lv_index).parameter_name := lv_attr_rec.fe_attribute_name;
1373 lv_attr_list(lv_index).parameter_value := lv_attrVal;
1374 IF lv_attr_rec.conceal_data = 'Y' THEN
1375 lv_attrVal:= DecodeAttrValue(lv_attrVal);
1376 lv_attr_list(lv_index).parameter_value := lv_attrVal;
1377 END IF;
1378
1379 END LOOP;
1380
1381 return lv_attr_list;
1382
1383 END Get_FE_AttributeVal_List;
1384
1385 /*
1386 Retrieve all the FE Attribute value
1387 for a given fulfillment element
1388 */
1389 FUNCTION Get_FE_AttributeVal_List(
1390 p_fe_id in number)
1391 RETURN XDP_TYPES.ORDER_PARAMETER_LIST
1392 IS
1393 lv_attrVal varchar2(4000);
1394 lv_fe_name varchar2(80);
1395 lv_fetype_id NUMBER;
1399 lv_conceal_data VARCHAR2(10);
1396 lv_fetype varchar2(40);
1397 lv_fe_sw_generic varchar2(40);
1398 lv_adapter_type varchar2(40);
1400 lv_gen_lookup_id number;
1401 lv_attr_list XDP_TYPES.ORDER_PARAMETER_LIST;
1402 lv_index number := 0;
1403
1404 lv_dummyvar varchar2(80);
1405
1406 CURSOR lc_FE_Attr(l_fe_id number,l_gen_lookup_id number) IS
1407 select
1408 fan.fe_attribute_name fe_attribute_name,
1409 decode(fe_attribute_value,NULL,default_value,fe_attribute_value) fe_attribute_value,
1410 fan.conceal_data conceal_data
1411 from
1412 xdp_fe_attribute_def fan,
1413 (select fe_attribute_id, fe_attribute_value
1414 from
1415 XDP_FE_ATTRIBUTE_VAL fae,
1416 XDP_FE_GENERIC_CONFIG fge
1417 where
1418 fae.fe_generic_config_id = fge.fe_generic_config_id and
1419 fge.fe_id = l_fe_id and
1420 fge.fe_sw_gen_lookup_id = l_gen_lookup_id ) fae2
1421 where
1422 fan.fe_sw_gen_lookup_id = l_gen_lookup_id and
1423 fan.fe_attribute_id = fae2.fe_attribute_id(+);
1424 BEGIN
1425
1426 GetFeConfigInfoNum( p_fe_id => Get_FE_AttributeVal_List.p_fe_id,
1427 p_fe => lv_fe_name,
1428 p_fetype_id => lv_fetype_id,
1429 p_fetype => lv_fetype,
1430 p_fe_sw_generic => lv_fe_sw_generic,
1431 p_adapter_type => lv_adapter_type,
1432 p_gen_lookup_id => lv_gen_lookup_id,
1433 p_connect_proc => lv_dummyvar,
1434 p_disconnect_proc => lv_dummyvar);
1435
1436
1437 FOR lv_attr_rec IN lc_FE_Attr(p_fe_id, lv_gen_lookup_id) LOOP
1438 lv_index := lv_index + 1;
1439 lv_attrVal := lv_attr_rec.fe_attribute_value;
1440 lv_attr_list(lv_index).parameter_name := lv_attr_rec.fe_attribute_name;
1441 lv_attr_list(lv_index).parameter_value := lv_attrVal;
1442 IF lv_attr_rec.conceal_data = 'Y' THEN
1443 lv_attrVal:= DecodeAttrValue(lv_attrVal);
1444 lv_attr_list(lv_index).parameter_value := lv_attrVal;
1445 END IF;
1446 END LOOP;
1447
1448 return lv_attr_list;
1449
1450 END Get_FE_AttributeVal_List;
1451
1452
1453 /*
1454 Retrieve the FE connect/disconnect procedure name
1455 for a given fulfillment element
1456 */
1457 PROCEDURE Get_FE_ConnectionProc(
1458 p_fe_name IN VARCHAR2,
1459 p_connect_proc_name OUT NOCOPY VARCHAR2,
1460 p_disconnect_proc_name OUT NOCOPY VARCHAR2)
1461 IS
1462 lv_fe_id NUMBER;
1463 lv_fetype_id NUMBER;
1464 lv_fetype varchar2(40);
1465 lv_fe_sw_generic varchar2(40);
1466 lv_adapter_type varchar2(40);
1467
1468 lv_dummynum NUMBER;
1469
1470 BEGIN
1471 GetFeConfigInfoText
1472 (p_fe => Get_FE_ConnectionProc.p_fe_name,
1473 p_fe_id => lv_fe_id,
1474 p_fetype_id => lv_fetype_id,
1475 p_fetype => lv_fetype,
1476 p_fe_sw_generic => lv_fe_sw_generic,
1477 p_adapter_type => lv_adapter_type,
1478 p_gen_lookup_id => lv_dummynum,
1479 p_connect_proc => Get_FE_ConnectionProc.p_connect_proc_name,
1480 p_disconnect_proc => Get_FE_ConnectionProc.p_disconnect_proc_name);
1481
1482 END Get_FE_ConnectionProc;
1483
1484 /*
1485 Retrieve the FE connect/disconnect procedure name
1486 for a given fulfillment element
1487 */
1488 PROCEDURE Get_FE_ConnectionProc(
1489 p_fe_id IN NUMBER,
1490 p_connect_proc_name OUT NOCOPY VARCHAR2,
1491 p_disconnect_proc_name OUT NOCOPY VARCHAR2)
1492 IS
1493 lv_fe_name varchar2(80);
1494 lv_fetype_id NUMBER;
1495 lv_fetype varchar2(40);
1496 lv_fe_sw_generic varchar2(40);
1497 lv_adapter_type varchar2(40);
1498
1499 lv_dummynum NUMBER;
1500 BEGIN
1501 GetFeConfigInfoNum
1502 (p_fe_id => Get_FE_ConnectionProc.p_fe_id,
1503 p_fe => lv_fe_name,
1504 p_fetype_id => lv_fetype_id,
1505 p_fetype => lv_fetype,
1506 p_fe_sw_generic => lv_fe_sw_generic,
1507 p_adapter_type => lv_adapter_type,
1508 p_gen_lookup_id => lv_dummynum,
1509 p_connect_proc => Get_FE_ConnectionProc.p_connect_proc_name,
1510 p_disconnect_proc => Get_FE_ConnectionProc.p_disconnect_proc_name);
1511
1512 END Get_FE_ConnectionProc;
1513
1514
1515 Function Is_Fe_Valid(p_fe_id in number) return BOOLEAN
1516 is
1517 lv_valid boolean := FALSE;
1518 lv_dummynum NUMBER;
1519 lv_dummyvar VARCHAR2(80);
1520
1521 begin
1522
1523 begin
1524 GetFeConfigInfoNum
1525 (p_fe_id => Is_Fe_Valid.p_fe_id,
1526 p_fe => lv_dummyvar,
1527 p_fetype_id => lv_dummynum,
1528 p_fetype => lv_dummyvar,
1529 p_fe_sw_generic => lv_dummyvar,
1530 p_adapter_type => lv_dummyvar,
1531 p_gen_lookup_id => lv_dummynum,
1532 p_connect_proc => lv_dummyvar,
1533 p_disconnect_proc => lv_dummyvar);
1534
1535 lv_valid := TRUE;
1536
1537 exception
1538 when no_data_found then
1539 lv_valid := FALSE;
1540 end;
1541
1542 return (lv_valid);
1543
1544 end Is_Fe_Valid;
1545
1546
1547 Function Is_Fe_Valid(p_fe_name in varchar2) return BOOLEAN
1548 is
1549 lv_valid boolean := FALSE;
1550 lv_dummynum NUMBER;
1551 lv_dummyvar VARCHAR2(80);
1552
1553 begin
1554
1555 begin
1556 GetFeConfigInfoText
1557 (p_fe => Is_Fe_Valid.p_fe_name,
1558 p_fe_id => lv_dummynum,
1562 p_adapter_type => lv_dummyvar,
1559 p_fetype_id => lv_dummynum,
1560 p_fetype => lv_dummyvar,
1561 p_fe_sw_generic => lv_dummyvar,
1563 p_gen_lookup_id => lv_dummynum,
1564 p_connect_proc => lv_dummyvar,
1565 p_disconnect_proc => lv_dummyvar);
1566
1567 lv_valid := TRUE;
1568
1569 exception
1570 when no_data_found then
1571 lv_valid := FALSE;
1572 end;
1573
1574 return (lv_valid);
1575 end Is_Fe_Valid;
1576
1577
1578 /*
1579 Retrieve the list of the workitems SFM has executed for the given order.
1580 */
1581 Function Get_Workitem_List(
1582 p_sdp_order_id NUMBER)
1583 return XDP_TYPES.WORKITEM_LIST
1584 IS
1585 lv_list XDP_TYPES.WORKITEM_LIST;
1586 lv_index number;
1587 lv_err varchar2(4000);
1588 lv_ret number;
1589 lv_str varchar2(2000);
1590 lv_err_list XDP_TYPES.MESSAGE_LIST;
1591 CURSOR lc_wi IS
1592 select
1593 fwt.workitem_instance_id,
1594 fwt.workitem_id,
1595 wim.workitem_name,
1596 fwt.wi_sequence,
1597 fwt.status_code,
1598 fwt.priority,
1599 fwt.line_item_id,
1600 fwt.line_number,
1601 fwt.provisioning_date,
1602 NVL(fwt.error_ref_id,0) error_ref_id
1603 from
1604 XDP_FULFILL_WORKLIST fwt,
1605 xdp_workitems wim
1606 where
1607 fwt.order_id = p_sdp_order_id and
1608 fwt.workitem_id = wim.workitem_id;
1609
1610 lv_index2 binary_integer;
1611 lv_error_type varchar2(30);
1612 lv_date DATE;
1613
1614 BEGIN
1615
1616 lv_index := 0;
1617
1618 for lv_wi_rec in lc_wi loop
1619 lv_index := lv_index + 1;
1620 lv_list(lv_index).workitem_name := lv_wi_rec.workitem_name;
1621 lv_list(lv_index).workitem_id := lv_wi_rec.workitem_id;
1622 lv_list(lv_index).provisioning_sequence := lv_wi_rec.wi_sequence;
1623 lv_list(lv_index).provisioning_date := lv_wi_rec.provisioning_date;
1624 lv_list(lv_index).priority := lv_wi_rec.priority;
1625 lv_list(lv_index).workitem_status := lv_wi_rec.status_code;
1626 lv_list(lv_index).workitem_instance_id := lv_wi_rec.workitem_instance_id;
1627 lv_list(lv_index).line_item_id := lv_wi_rec.line_item_id;
1628 lv_list(lv_index).line_number := lv_wi_rec.line_number;
1629
1630 -- if lv_wi_rec.error_ref_id > 0 then
1631 if lv_wi_rec.status_code = 'ERROR' then
1632
1633 -- XDP_ERRORS_PKG.Get_Message_List (
1634 -- p_message_ref_id => lv_wi_rec.error_ref_id ,
1635 -- p_message_list => lv_err_list,
1636 -- p_sql_code => lv_ret,
1637 -- p_sql_desc => lv_str);
1638
1639 -- if lv_err_list.count > 0 then
1640 -- lv_index2 := lv_err_list.first;
1641 -- lv_list(lv_index).error_description :=
1642 -- lv_err_list(lv_index2).message_text;
1643 -- end if;
1644
1645 XDP_ERRORS_PKG.Get_Last_Message (
1646 p_object_type => 'WORKITEM',
1647 p_object_key => lv_wi_rec.workitem_instance_id,
1648 p_message => lv_list(lv_index).error_description,
1649 p_error_type => lv_error_type,
1650 p_message_timestamp => lv_date);
1651 end if;
1652
1653 end loop;
1654
1655 return lv_list;
1656
1657 END Get_Workitem_List;
1658
1659 /*
1660 Retrieve the list of the fulfillment actions SFM has executed
1661 for the given workitem.
1662 */
1663 Function Get_FA_List(
1664 p_wi_instance_id NUMBER)
1665 return XDP_TYPES.FULFILLMENT_ACTION_LIST
1666 IS
1667 lv_list XDP_TYPES.FULFILLMENT_ACTION_LIST;
1668 lv_index number;
1669 lv_err varchar2(4000);
1670 lv_ret number;
1671 lv_str varchar2(2000);
1672 lv_err_list XDP_TYPES.MESSAGE_LIST;
1673 lv_index2 binary_integer;
1674
1675 lv_error_type varchar2(30);
1676 lv_date DATE;
1677
1678
1679 CURSOR lc_fa IS
1680 select
1681 frt.fa_instance_id,
1682 fan.fulfillment_action,
1683 frt.status_code,
1684 frt.fulfillment_action_id,
1685 frt.priority,
1686 frt.provisioning_sequence,
1687 NVL(frt.error_ref_id, 0) error_ref_id
1688 from
1689 xdp_fa_runtime_list frt,
1690 XDP_FULFILL_ACTIONS fan
1691 where
1692 frt.workitem_instance_id = p_wi_instance_id and
1693 frt.fulfillment_action_id = fan.fulfillment_action_id;
1694 BEGIN
1695
1696 lv_index := 0;
1697 FOR lv_fa_rec in lc_fa loop
1698 lv_index := lv_index + 1;
1699 lv_list(lv_index).fa_instance_id := lv_fa_rec.fa_instance_id ;
1700 lv_list(lv_index).fulfillment_action_id := lv_fa_rec.fulfillment_action_id;
1701 lv_list(lv_index).provisioning_sequence := lv_fa_rec.provisioning_sequence;
1702 lv_list(lv_index).priority := lv_fa_rec.priority;
1703 lv_list(lv_index).fa_status := lv_fa_rec.status_code;
1704 lv_list(lv_index).fulfillment_action := lv_fa_rec.fulfillment_action;
1705
1706 -- Changed - sacsharm - 11.5.6 ErrorHandling
1707
1708 -- if lv_fa_rec.error_ref_id > 0 then
1709 if lv_fa_rec.status_code = 'ERROR' then
1710
1711 -- XDP_ERRORS_PKG.Get_Message_List (
1712 -- p_message_ref_id => lv_fa_rec.error_ref_id ,
1713 -- p_message_list => lv_err_list,
1714 -- p_sql_code => lv_ret,
1715 -- p_sql_desc => lv_str);
1716 --if lv_err_list.count > 0 then
1717 -- lv_index2 := lv_err_list.first;
1718 -- lv_list(lv_index).error_description :=
1719 -- lv_err_list(lv_index2).message_text;
1720 --end if;
1721
1725 p_object_key => lv_fa_rec.fa_instance_id,
1722 -- Anyway earlier code was interest in last message
1723 XDP_ERRORS_PKG.Get_Last_Message (
1724 p_object_type => 'FA',
1726 p_message => lv_list(lv_index).error_description,
1727 p_error_type => lv_error_type,
1728 p_message_timestamp => lv_date);
1729 end if;
1730
1731 END LOOP;
1732
1733 return lv_list;
1734
1735 END Get_FA_List;
1736
1737 /*
1738 Retrieve the list of commands which have been sent by the
1739 given FA and the responds SFM received from the FE
1740 */
1741 Function Get_FA_AUDIT_TRAILS(
1742 p_fa_instance_id NUMBER)
1743 return XDP_TYPES.FA_COMMAND_AUDIT_TRAIL
1744 IS
1745 lv_list XDP_TYPES.FA_COMMAND_AUDIT_TRAIL;
1746 lv_index number;
1747 CURSOR lc_fe IS
1748 select
1749 fcl.fa_instance_id,
1750 fcl.fe_command_seq ,
1751 fan.fulfillment_action,
1752 frt.fulfillment_action_id ,
1753 fcl.command_sent ,
1754 fcl.command_sent_date ,
1755 fcl.response ,
1756 fcl.response_date ,
1757 fcl.USER_RESPONSE ,
1758 fcl.msg_id ,
1759 fcl.provisioning_procedure,
1760 fcl.fulfillment_element_name
1761 from
1762 xdp_fa_runtime_list frt,
1763 XDP_FULFILL_ACTIONS fan,
1764 xdp_fe_cmd_aud_trails fcl
1765 where
1766 frt.fa_instance_id = fcl.fa_instance_id and
1767 frt.fulfillment_action_id = fan.fulfillment_action_id and
1768 fcl.fa_instance_id = p_fa_instance_id;
1769 BEGIN
1770
1771 lv_index := 0;
1772 FOR lv_fa_rec in lc_fe loop
1773 lv_index := lv_index + 1;
1774 lv_list(lv_index).fa_instance_id := lv_fa_rec.fa_instance_id ;
1775 lv_list(lv_index).fulfillment_action_id := lv_fa_rec.fulfillment_action_id;
1776 lv_list(lv_index).fulfillment_action := lv_fa_rec.fulfillment_action;
1777 lv_list(lv_index).command_sequence := lv_fa_rec.fe_command_seq;
1778 lv_list(lv_index).command_sent := lv_fa_rec.command_sent;
1779 lv_list(lv_index).command_sent_date:= lv_fa_rec.command_sent_date;
1780 lv_list(lv_index).fe_response := lv_fa_rec.response;
1781 lv_list(lv_index).response_date := lv_fa_rec.response_date;
1782 lv_list(lv_index).user_response := lv_fa_rec.user_response;
1783 lv_list(lv_index).message_id := lv_fa_rec.msg_id;
1784 lv_list(lv_index).fulfillment_procedure_name := lv_fa_rec.provisioning_procedure;
1785 lv_list(lv_index).fulfillment_element_name := lv_fa_rec.fulfillment_element_name;
1786 END LOOP;
1787
1788 return lv_list;
1789
1790 END Get_FA_AUDIT_TRAILS;
1791
1792 -------------------------------------------------------------------
1793 -- Description : Procedure to reset a Synchronisation Request
1794 -------------------------------------------------------------------
1795 --
1796 PROCEDURE Reset_Sync_Registration (
1797 pp_sync_label IN VARCHAR2
1798 ,po_error_code OUT NOCOPY NUMBER
1799 ,po_error_msg OUT NOCOPY VARCHAR2
1800 )
1801 IS
1802 -- lv_error_code NUMBER;
1803 -- lv_error_msg VARCHAR2(1000);
1804
1805 BEGIN
1806
1807 -- Reset the Synchronisation Request
1808 --
1809 Xnp_WF_Sync.Reset_Sync_Register (pp_sync_label => pp_sync_label
1810 ,po_error_code => po_error_code
1811 ,po_error_msg => po_error_msg);
1812 -- po_error_code := lv_error_code;
1813 -- po_error_msg := lv_error_msg;
1814
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 po_error_code := -191266;
1818 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1819 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
1820 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1821 po_error_msg := FND_MESSAGE.GET;
1822
1823 END Reset_Sync_Registration;
1824
1825 -------------------------------------------------------------------
1826 -- Description : Procedure to recalculate timers
1827 -------------------------------------------------------------------
1828 PROCEDURE recalculate
1829 (
1830 p_reference_id IN VARCHAR2
1831 ,p_timer_message_code IN VARCHAR2
1832 ,x_error_code OUT NOCOPY NUMBER
1833 ,x_error_message OUT NOCOPY VARCHAR2
1834 )
1835 IS
1836
1837 BEGIN
1838 --- Call the recalculate api from timer core
1839 xnp_timer_core.recalculate(p_reference_id => p_reference_id
1840 ,p_timer_message_code => p_timer_message_code
1841 ,x_error_code => x_error_code
1842 ,x_error_message => x_error_message
1843 );
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 x_error_code := -191266;
1847 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1848 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
1849 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1850 x_error_message := FND_MESSAGE.GET;
1851
1852
1853 END recalculate;
1854
1855 -------------------------------------------------------------------
1856 -- Description : Procedure to recalculate all timers
1857 -------------------------------------------------------------------
1858 PROCEDURE recalculate_all
1859 (
1860 p_reference_id IN VARCHAR2
1861 ,x_error_code OUT NOCOPY NUMBER
1865
1862 ,x_error_message OUT NOCOPY VARCHAR2
1863 )
1864 IS
1866 BEGIN
1867 --- Call the recalculate_all api from timer core
1868 xnp_timer_core.recalculate_all
1869 (p_reference_id => p_reference_id
1870 ,x_error_code => x_error_code
1871 ,x_error_message => x_error_message
1872 );
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 x_error_code := -191266;
1876 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1877 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
1878 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1879 x_error_message := FND_MESSAGE.GET;
1880
1881 END recalculate_all;
1882 -------------------------------------------------------------------
1883 -- Description : Procedure to get timer status using reference_id and
1884 -- timer message code
1885 -------------------------------------------------------------------
1886 PROCEDURE get_timer_status
1887 (
1888 p_reference_id IN VARCHAR2
1889 ,p_timer_message_code IN VARCHAR2
1890 ,x_timer_id OUT NOCOPY NUMBER
1891 ,x_status OUT NOCOPY VARCHAR2
1892 ,x_error_code OUT NOCOPY NUMBER
1893 ,x_error_message OUT NOCOPY VARCHAR2
1894 )
1895 IS
1896
1897 BEGIN
1898 --- Call the get_timer_status api from timer core
1899 --- Returns the status and timer_id
1900 xnp_timer_core.get_timer_status
1901 (p_reference_id => p_reference_id
1902 ,p_timer_message_code => p_timer_message_code
1903 ,x_timer_id => x_timer_id
1904 ,x_status => x_status
1905 ,x_error_code => x_error_code
1906 ,x_error_message => x_error_message
1907 );
1908 EXCEPTION
1909 WHEN OTHERS THEN
1910 x_error_code := -191266;
1911 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1912 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
1913 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1914 x_error_message := FND_MESSAGE.GET;
1915
1916 END get_timer_status;
1917 -------------------------------------------------------------------
1918 -- Description : Procedure to update timer status using reference_id
1919 -- and timer_message_code
1920 -------------------------------------------------------------------
1921 PROCEDURE update_timer_status
1922 (
1923 p_reference_id IN VARCHAR2
1924 ,p_timer_message_code IN VARCHAR2
1925 ,p_status IN VARCHAR2
1926 ,x_error_code OUT NOCOPY NUMBER
1927 ,x_error_message OUT NOCOPY VARCHAR2
1928 )
1929 IS
1930
1931 BEGIN
1932 --- Call the get_timer_status api from timer core
1933 --- Returns the status and timer_id
1934 xnp_timer_core.update_timer_status
1935 (p_reference_id => p_reference_id
1936 ,p_timer_message_code => p_timer_message_code
1937 ,p_status => p_status
1938 ,x_error_code => x_error_code
1939 ,x_error_message => x_error_message
1940 );
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 x_error_code := -191266;
1944 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1945 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
1946 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1947 x_error_message := FND_MESSAGE.GET;
1948
1949 END update_timer_status;
1950
1951 -------------------------------------------------------------------
1952 -- Description : Procedure to remove timer using reference_id and
1953 -- timer_message_code
1954 -------------------------------------------------------------------
1955 PROCEDURE remove_timer
1956 (
1957 p_reference_id IN VARCHAR2
1958 ,p_timer_message_code IN VARCHAR2
1959 ,x_error_code OUT NOCOPY NUMBER
1960 ,x_error_message OUT NOCOPY VARCHAR2
1961 )
1962 IS
1963
1964 BEGIN
1965 --- Call the remove_timer api from timer core
1966 xnp_timer_core.remove_timer
1967 (p_reference_id => p_reference_id
1968 ,p_timer_message_code => p_timer_message_code
1969 ,x_error_code => x_error_code
1970 ,x_error_message => x_error_message
1971 );
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 x_error_code := -191266;
1975 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1976 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
1977 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1978 x_error_message := FND_MESSAGE.GET;
1979
1980 END remove_timer;
1981 -------------------------------------------------------------------
1982 -- Description : Procedure to restart a timer using a reference_id
1983 -- and timer_message_code
1984 -------------------------------------------------------------------
1988 ,p_timer_message_code IN VARCHAR2
1985 PROCEDURE restart
1986 (
1987 p_reference_id IN VARCHAR2
1989 ,x_error_code OUT NOCOPY NUMBER
1990 ,x_error_message OUT NOCOPY VARCHAR2
1991 )
1992
1993 IS
1994
1995 BEGIN
1996 --- Call the restart api from timer core
1997 xnp_timer_core.restart
1998 (p_reference_id => p_reference_id
1999 ,p_timer_message_code => p_timer_message_code
2000 ,x_error_code => x_error_code
2001 ,x_error_message => x_error_message
2002 );
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005 x_error_code := -191266;
2006 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2007 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
2008 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2009 x_error_message := FND_MESSAGE.GET;
2010
2011 END restart;
2012 -------------------------------------------------------------------
2013 -- Description : Procedure to deregister timers for an order_id
2014 -------------------------------------------------------------------
2015 PROCEDURE deregister
2016 (
2017 p_order_id IN NUMBER
2018 ,x_error_code OUT NOCOPY NUMBER
2019 ,x_error_message OUT NOCOPY VARCHAR2
2020 )
2021
2022 IS
2023
2024 BEGIN
2025 --- Call the deregister api from timer core
2026 xnp_timer_core.deregister
2027 (p_order_id => p_order_id
2028 ,x_error_code => x_error_code
2029 ,x_error_message => x_error_message
2030 );
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 x_error_code := -191266;
2034 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2035 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
2036 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2037 x_error_message := FND_MESSAGE.GET;
2038
2039 END deregister;
2040 -------------------------------------------------------------------
2041 -- Description : Procedure to restart all timers using reference_id
2042 -------------------------------------------------------------------
2043 PROCEDURE restart_all
2044 (
2045 p_reference_id IN VARCHAR2
2046 ,x_error_code OUT NOCOPY NUMBER
2047 ,x_error_message OUT NOCOPY VARCHAR2
2048
2049 )
2050 IS
2051
2052 BEGIN
2053 --- Call the restart_all api from timer core
2054 xnp_timer_core.restart_all
2055 (p_reference_id => p_reference_id
2056 ,x_error_code => x_error_code
2057 ,x_error_message => x_error_message
2058 );
2059 EXCEPTION
2060 WHEN OTHERS THEN
2061 x_error_code := -191266;
2062 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2063 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
2064 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2065 x_error_message := FND_MESSAGE.GET;
2066
2067 END restart_all;
2068 -------------------------------------------------------------------
2069 -- Description : Procedure to start timers related to a message
2070 -------------------------------------------------------------------
2071 PROCEDURE start_related_timers
2072 (
2073 p_message_code IN VARCHAR2
2074 ,p_reference_id IN VARCHAR2
2075 ,x_error_code OUT NOCOPY NUMBER
2076 ,x_error_message OUT NOCOPY VARCHAR2
2077 ,p_opp_reference_id IN VARCHAR2 DEFAULT NULL
2078 ,p_sender_name IN VARCHAR2 DEFAULT NULL
2079 ,p_recipient_name IN VARCHAR2 DEFAULT NULL
2080 ,p_order_id IN NUMBER DEFAULT NULL
2081 ,p_wi_instance_id IN NUMBER DEFAULT NULL
2082 ,p_fa_instance_id IN NUMBER DEFAULT NULL
2083 )
2084
2085 IS
2086
2087 BEGIN
2088 --- Call the start_related_timers api from timer core
2089 xnp_timer_core.start_related_timers
2090 (p_message_code => p_message_code
2091 ,p_reference_id => p_reference_id
2092 ,x_error_code => x_error_code
2093 ,x_error_message => x_error_message
2094 ,p_opp_reference_id => p_opp_reference_id
2095 ,p_sender_name => p_sender_name
2096 ,p_recipient_name => p_recipient_name
2097 ,p_order_id => p_order_id
2098 ,p_wi_instance_id => p_wi_instance_id
2099 ,p_fa_instance_id => p_fa_instance_id
2100 );
2101 EXCEPTION
2102 WHEN OTHERS THEN
2103 x_error_code := -191266;
2104 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2105 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
2106 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2107 x_error_message := FND_MESSAGE.GET;
2108
2109 END start_related_timers;
2110
2111 -- API to set the order reference information such as order
2112 -- reference name, order reference value, service provider
2113 -- order number, and service provider user ID
2114 PROCEDURE Set_Order_Reference
2118 p_order_ref_value IN VARCHAR2,
2115 (
2116 p_order_id IN NUMBER,
2117 p_order_ref_name IN VARCHAR2,
2119 p_sp_order_number IN VARCHAR2 DEFAULT NULL,
2120 p_sp_user_id IN NUMBER DEFAULT NULL,
2121 x_return_code OUT NOCOPY NUMBER,
2122 x_error_description OUT NOCOPY VARCHAR2
2123 )
2124 IS
2125 lv_ref_name VARCHAR2(80);
2126 lv_ref_val VARCHAR2(300);
2127 lv_sp_order VARCHAR2(80);
2128 --lv_sp_uid NUMBER;
2129 BEGIN
2130 x_return_code := 0;
2131 select ORDER_REF_NAME,
2132 ORDER_REF_VALUE,
2133 SP_ORDER_NUMBER
2134 --SP_USERID
2135 into lv_ref_name,
2136 lv_ref_val,
2137 lv_sp_order
2138 --,lv_sp_uid
2139 from xdp_order_headers
2140 where order_id = p_order_id;
2141
2142 update xdp_order_headers
2143 set
2144 order_ref_name = NVL(p_order_ref_name,lv_ref_name),
2145 order_ref_value = NVL(p_order_ref_value,lv_ref_val),
2146 sp_order_number = NVL(p_sp_order_number,lv_sp_order),
2147 --sp_userid = NVL(p_sp_user_id,lv_sp_uid),
2148 last_updated_by = FND_GLOBAL.USER_ID,
2149 last_update_date = sysdate,
2150 last_update_login = FND_GLOBAL.LOGIN_ID
2151 where order_id = p_order_id;
2152
2153
2154 EXCEPTION
2155 WHEN NO_DATA_FOUND THEN
2156 x_return_code := SQLCODE;
2157 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
2158 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_order_id);
2159 x_error_description := FND_MESSAGE.GET;
2160 WHEN OTHERS THEN
2161 x_return_code := -191266;
2162 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2163 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB');
2164 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2165 x_error_description := FND_MESSAGE.GET;
2166
2167 END Set_Order_Reference;
2168
2169 -- a private utility used by XDP_SYNC_LINE_ITEM_PV
2170 PROCEDURE Get_Parameter_Value_From_WI(
2171 p_line_item_id IN NUMBER,
2172 p_parameter_name IN VARCHAR2,
2173 x_parameter_value OUT NOCOPY VARCHAR2)
2174 IS
2175 cursor c_GetLatestWiParam is
2176 SELECT parameter_value
2177 FROM
2178 xdp_worklist_details wd,
2179 xdp_fulfill_worklist fw,
2180 xdp_wi_parameters wp
2181 WHERE FW.line_item_id=p_line_item_id
2182 AND FW.workitem_instance_id = wd.workitem_instance_id
2183 AND wd.workitem_id = wp.workitem_id
2184 AND wd.parameter_name = wp.parameter_name --to do
2185 AND wp.parameter_name = p_parameter_name
2186 AND parameter_value is not null
2187 ORDER BY wd.creation_date desc;
2188
2189 BEGIN
2190 x_parameter_value := NULL;
2191 FOR v_GetLatestWiParam in c_GetLatestWiParam LOOP
2192 x_parameter_value := v_GetLatestWiParam.parameter_value;
2193 EXIT; -- ONLY TAKE THE LATEST VALUE
2194 END LOOP;
2195 END Get_Parameter_Value_From_WI;
2196
2197 PROCEDURE XDP_SYNC_LINE_ITEM_PV(
2198 p_line_item_id IN XDP_ORDER_LINE_ITEMS.LINE_ITEM_ID%TYPE,
2199 x_return_code OUT NOCOPY NUMBER,
2200 x_error_description OUT NOCOPY VARCHAR2
2201 ) IS
2202 CURSOR c_line_item IS
2203 SELECT line_parameter_name, PARAMETER_VALUE
2204 FROM XDP_ORDER_LINEITEM_DETS
2205 WHERE LINE_ITEM_ID = p_line_item_id
2206 AND PARAMETER_VALUE IS NULL
2207 FOR UPDATE OF PARAMETER_VALUE NOWAIT;
2208 l_parameter_value VARCHAR2(4000);
2209 BEGIN
2210 FOR l_line_item_dets IN c_line_item LOOP
2211 Get_Parameter_Value_From_WI(p_line_item_id,l_line_item_dets.line_parameter_name,l_parameter_value);
2212 IF (l_parameter_value IS NOT NULL) THEN
2213 UPDATE XDP_ORDER_LINEITEM_DETS SET PARAMETER_VALUE = l_parameter_value
2214 WHERE CURRENT OF c_line_item;
2215 END IF;
2216 END LOOP;
2217
2218 EXCEPTION
2219 WHEN OTHERS THEN
2220 x_return_code := -191266;
2221 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2222 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGNB.XDP_SYNC_LINE_ITEM_PV');
2223 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2224 x_error_description := FND_MESSAGE.GET;
2225 END XDP_SYNC_LINE_ITEM_PV;
2226
2227 Procedure Get_WI_Parameter_Info (p_wi_instance_id IN NUMBER,
2228 p_parameter_name IN VARCHAR2,
2229 p_evaluation_procedure OUT NOCOPY VARCHAR2,
2230 p_evaluation_mode OUT NOCOPY VARCHAR2,
2231 p_default_value OUT NOCOPY VARCHAR2,
2232 p_parameter_value OUT NOCOPY VARCHAR2,
2233 p_parameter_ref_value OUT NOCOPY VARCHAR2,
2234 p_audit_flag OUT NOCOPY VARCHAR2,
2235 p_workitem_id OUT NOCOPY NUMBER)
2236 IS
2237
2238 CURSOR c_GetWiParamInfo IS
2239 select
2240 is_value_evaluated,
2241 modified_flag,
2242 parameter_value,
2243 parameter_ref_value,
2244 workitem_id
2245 from xdp_worklist_details
2246 where workitem_instance_id = p_wi_instance_id
2247 and parameter_name = p_parameter_name;
2248
2249 CURSOR c_GetEvalInfo is
2250 select
2251 wpr.workitem_id,
2252 wpr.evaluation_procedure,
2253 wpr.evaluation_mode,
2254 wpr.default_value,
2255 NVL(wpr.log_in_audit_trail_flag,'Y') audit_trail_flag
2256 from
2260 wpr.workitem_id = wdl.workitem_id and
2257 xdp_wi_parameters wpr,
2258 xdp_worklist_details wdl
2259 where
2261 wpr.parameter_name = p_parameter_name and
2262 wdl.workitem_instance_id = p_wi_instance_id;
2263
2264 lv_found_details BOOLEAN := FALSE;
2265 lv_found_config BOOLEAN := FALSE;
2266 lv_Evaluated varchar2(10);
2267 begin
2268
2269 p_default_value := null;
2270 p_evaluation_procedure := null;
2271 p_evaluation_mode := null;
2272 p_audit_flag := null;
2273
2274 for v_GetWiParamInfo in c_GetWiParamInfo loop
2275 p_parameter_value := v_GetWiParamInfo.parameter_value;
2276 p_parameter_ref_value := v_GetWiParamInfo.parameter_ref_value;
2277 lv_Evaluated := v_GetWiParamInfo.is_value_evaluated;
2278 p_workitem_id := v_GetWiParamInfo.workitem_id;
2279
2280 lv_found_details := TRUE;
2281 end loop;
2282
2283 if lv_Evaluated = 'Y' then
2284 return;
2285 end if;
2286
2287 -- Parameter is not Yet Evaluated-- Must be Deferred Evaluation
2288 -- Get the Deferred Evaluation procedure
2289 for v_GetEvalInfo in c_GetEvalInfo loop
2290 p_evaluation_procedure := v_GetEvalInfo.evaluation_procedure;
2291 p_audit_flag := v_GetEvalInfo.audit_trail_flag;
2292 p_default_value := v_GetEvalInfo.default_value;
2293 p_evaluation_mode := v_GetEvalInfo.evaluation_mode;
2294 p_workitem_id := v_GetEvalInfo.workitem_id;
2295
2296 lv_found_config := TRUE;
2297 end loop;
2298
2299 if (not lv_found_details) and (not lv_found_config) then
2300 raise no_data_found;
2301 end if;
2302
2303 end Get_WI_Parameter_Info;
2304
2305 Procedure Get_FA_Parameter_info (p_fa_instance_id IN NUMBER,
2306 p_parameter_name IN VARCHAR2,
2307 p_evaluation_procedure OUT NOCOPY VARCHAR2,
2308 p_fa_id OUT NOCOPY NUMBER,
2309 p_default_value OUT NOCOPY VARCHAR2,
2310 p_parameter_value OUT NOCOPY VARCHAR2,
2311 p_parameter_ref_value OUT NOCOPY VARCHAR2,
2312 p_audit_flag OUT NOCOPY VARCHAR2)
2313 is
2314
2315 CURSOR c_GetFAParamInfo IS
2316 select
2317 evaluation_procedure,
2318 NVL(log_in_audit_trail_flag,'Y') audit_trail_flag,
2319 default_value,
2320 parameter_value,
2321 parameter_ref_value,
2322 frt.fulfillment_action_id
2323 from
2324 xdp_fa_parameters fpr,
2325 xdp_fa_details frt
2326 where
2327 fpr.parameter_name = frt.parameter_name and
2328 fpr.fulfillment_action_id = frt.fulfillment_action_id and
2329 fpr.parameter_name = p_parameter_name and
2330 frt.fa_instance_id = p_fa_instance_id;
2331
2332 lv_found BOOLEAN := FALSE;
2333 begin
2334
2335 for v_GetFAParamInfo in c_GetFAParamInfo loop
2336 p_parameter_value := v_GetFAParamInfo.parameter_value;
2337 p_parameter_ref_value := v_GetFAParamInfo.parameter_ref_value;
2338 p_default_value := v_GetFAParamInfo.default_value;
2339 p_evaluation_procedure := v_GetFAParamInfo.evaluation_procedure;
2340 p_fa_id := v_GetFAParamInfo.fulfillment_action_id;
2341 p_audit_flag := v_GetFAParamInfo.audit_trail_flag;
2342
2343 lv_found := TRUE;
2344 end loop;
2345
2346 if not lv_found then
2347 raise no_data_found;
2348 end if;
2349
2350 End Get_FA_Parameter_info;
2351
2352 Function DoesWIParamExist (p_wi_instance_id IN NUMBER,
2353 p_parameter_name IN VARCHAR2) return VARCHAR2
2354 is
2355
2356 CURSOR c_CheckWIParam is
2357 select 'Y' yahoo
2358 from dual
2359 where exists(
2360 select 'x' from
2361 xdp_worklist_details
2362 where workitem_instance_id = p_wi_instance_id and
2363 parameter_name = p_parameter_name);
2364
2365 lv_exists varchar2(1) := 'N';
2366 begin
2367
2368 For v_CheckWIParam in c_CheckWIParam loop
2369 lv_exists := v_CheckWIParam.yahoo;
2370 exit;
2371 end loop;
2372
2373
2374 return (lv_exists);
2375
2376 End DoesWIParamExist;
2377
2378
2379 Function DoesFAParamExist (p_fa_instance_id IN NUMBER,
2380 p_parameter_name IN VARCHAR2) return VARCHAR2
2381 is
2382 lv_exists varchar2(1) := 'N';
2383
2384 CURSOR c_CheckFAParam is
2385 select 'Y' yahoo
2386 from dual
2387 where exists(
2388 select 'x' from
2389 xdp_fa_details
2390 where fa_instance_id = p_fa_instance_id and
2391 parameter_name = p_parameter_name);
2392 begin
2393
2394 For v_CheckFAParam in c_CheckFAParam loop
2395 lv_exists := v_CheckFAParam.yahoo;
2396 exit;
2397 end loop;
2398
2399
2400 return (lv_exists);
2401
2402 end DoesFAParamExist;
2403
2404
2405 Function DoesOrderParamExist (p_order_id IN NUMBER,
2406 p_parameter_name IN VARCHAR2) return VARCHAR2
2407 is
2408
2409 CURSOR c_CheckOrderParam is
2410 select 'Y' yahoo
2411 from dual
2412 where exists(
2413 select 'x'
2414 from xdp_order_parameters
2415 where order_id = p_order_id and
2416 order_parameter_name = p_parameter_name);
2417
2418 lv_exists varchar2(1) := 'N';
2419 begin
2420
2421 For v_CheckOrderParam in c_CheckOrderParam loop
2425
2422 lv_exists := v_CheckOrderParam.yahoo;
2423 exit;
2424 end loop;
2426
2427 return (lv_exists);
2428 end DoesOrderParamExist;
2429
2430
2431 Function DoesLineParamExist (p_line_item_id IN NUMBER,
2432 p_parameter_name IN VARCHAR2) return VARCHAR2
2433 is
2434 CURSOR c_CheckLineParam is
2435 select 'Y' yahoo
2436 from dual
2437 where exists(
2438 select 'x'
2439 from XDP_ORDER_LINEITEM_DETS
2440 where line_item_id = p_line_item_id and
2441 line_parameter_name = p_parameter_name);
2442
2443 lv_exists varchar2(1) := 'N';
2444 begin
2445
2446 For v_CheckLineParam in c_CheckLineParam loop
2447 lv_exists := v_CheckLineParam.yahoo;
2448 exit;
2449 end loop;
2450
2451 return (lv_exists);
2452
2453 end DoesLineParamExist;
2454
2455
2456 Procedure LoadOrderParameters( p_order_id IN NUMBER,
2457 p_parameter_name IN VARCHAR2,
2458 p_parameter_value IN VARCHAR2)
2459 is
2460
2461 begin
2462
2463 insert into xdp_order_parameters
2464 (order_id,
2465 order_parameter_name,
2466 order_parameter_value,
2467 created_by,
2468 creation_date,
2469 last_updated_by,
2470 last_update_date,
2471 last_update_login
2472 )
2473 values
2474 ( p_order_id,
2475 p_parameter_name,
2476 p_parameter_value,
2477 FND_GLOBAL.USER_ID,
2478 sysdate,
2479 FND_GLOBAL.USER_ID,
2480 sysdate,
2481 FND_GLOBAL.LOGIN_ID
2482 );
2483
2484 end LoadOrderParameters;
2485
2486 Procedure LoadLineDetails(p_line_item_id IN NUMBER,
2487 p_parameter_name IN VARCHAR2,
2488 p_parameter_value IN VARCHAR2,
2489 p_parameter_reference_value IN VARCHAR2)
2490 is
2491 begin
2492
2493 insert into XDP_ORDER_LINEITEM_DETS
2494 ( line_item_id,
2495 line_parameter_name,
2496 parameter_value,
2497 parameter_reference_value,
2498 created_by,
2499 creation_date,
2500 last_updated_by,
2501 last_update_date,
2502 last_update_login
2503 )
2504 values
2505 ( p_line_item_id,
2506 p_parameter_name,
2507 p_parameter_value,
2508 p_parameter_reference_value,
2509 FND_GLOBAL.USER_ID,
2510 sysdate,
2511 FND_GLOBAL.USER_ID,
2512 sysdate,
2513 FND_GLOBAL.LOGIN_ID
2514 );
2515
2516 end LoadLineDetails;
2517
2518 Procedure LoadWorklistDetails(
2519 p_wi_instance_id IN NUMBER,
2520 p_parameter_name IN VARCHAR2,
2521 p_workitem_id IN NUMBER,
2522 p_is_value_evaluated IN VARCHAR2,
2523 p_parameter_value IN VARCHAR2,
2524 p_parameter_ref_value IN VARCHAR2)
2525 IS
2526 l_workitem_id NUMBER;
2527 BEGIN
2528 l_workitem_id := p_workitem_id;
2529 IF l_workitem_id IS NULL THEN
2530 SELECT
2531 workitem_id INTO l_workitem_id
2532 FROM
2533 xdp_fulfill_worklist
2534 WHERE
2535 workitem_instance_id = p_wi_instance_id;
2536 END IF;
2537 INSERT INTO xdp_worklist_details
2538 (
2539 workitem_instance_id,
2540 parameter_name,
2541 workitem_id,
2542 is_value_evaluated,
2543 parameter_value,
2544 parameter_ref_value,
2545 modified_flag,
2546 created_by,
2547 creation_date,
2548 last_updated_by,
2549 last_update_date,
2550 last_update_login
2551 )VALUES
2552 (
2553 p_wi_instance_id,
2554 p_parameter_name,
2555 l_workitem_id,
2556 p_is_value_evaluated,
2557 p_parameter_value,
2558 p_parameter_ref_value,
2559 'N',
2560 FND_GLOBAL.USER_ID,
2561 sysdate,
2562 FND_GLOBAL.USER_ID,
2563 sysdate,
2564 FND_GLOBAL.LOGIN_ID
2565 );
2566 END LoadWorklistDetails;
2567
2568 Procedure UpdateWorklistDetails(
2569 p_wi_instance_id IN NUMBER,
2570 p_parameter_name IN VARCHAR2,
2571 p_is_value_evaluated IN VARCHAR2,
2572 p_parameter_value IN VARCHAR2,
2573 p_parameter_ref_value IN VARCHAR2)
2574 IS
2575 BEGIN
2576 UPDATE xdp_worklist_details
2577 SET
2578 parameter_value = p_parameter_value ,
2579 parameter_ref_value = p_parameter_ref_value,
2580 is_value_evaluated = p_is_value_evaluated,
2581 modified_flag = 'Y',
2582 last_updated_by = FND_GLOBAL.USER_ID,
2583 last_update_date = sysdate,
2584 last_update_login = FND_GLOBAL.LOGIN_ID
2585 WHERE
2586 workitem_instance_id = p_wi_instance_id AND
2587 parameter_name = p_parameter_name;
2588 END UpdateWorklistDetails;
2589
2590 Procedure LoadFADetails(p_fa_instance_id IN NUMBER,
2591 p_parameter_name IN VARCHAR2,
2592 p_fa_id IN NUMBER,
2593 p_is_value_evaluated IN VARCHAR2,
2594 p_parameter_value IN VARCHAR2,
2595 p_parameter_ref_value IN VARCHAR2)
2596 is
2597 begin
2598
2602 fulfillment_action_id,
2599 insert into xdp_fa_details
2600 ( fa_instance_id,
2601 parameter_name,
2603 is_value_evaluated,
2604 parameter_value,
2605 parameter_ref_value,
2606 created_by,
2607 creation_date,
2608 last_updated_by,
2609 last_update_date,
2610 last_update_login
2611 )
2612 values
2613 ( p_fa_instance_id,
2614 p_parameter_name,
2615 p_fa_id,
2616 p_is_value_evaluated,
2617 p_parameter_value,
2618 p_parameter_ref_value,
2619 FND_GLOBAL.USER_ID,
2620 sysdate,
2621 FND_GLOBAL.USER_ID,
2622 sysdate,
2623 FND_GLOBAL.LOGIN_ID
2624 );
2625
2626 end LoadFADetails;
2627
2628 Procedure UpdateFaDetails(p_fa_instance_id IN NUMBER,
2629 p_parameter_name IN VARCHAR2,
2630 p_evaluated_flag IN VARCHAR2,
2631 p_parameter_value IN VARCHAR2,
2632 p_parameter_ref_value IN VARCHAR2)
2633 is
2634
2635 begin
2636
2637 update xdp_fa_details
2638 set
2639 parameter_value = UpdateFaDetails.p_parameter_value,
2640 parameter_ref_value = UpdateFaDetails.p_parameter_ref_value,
2641 is_value_evaluated = UpdateFaDetails.p_evaluated_flag,
2642 last_updated_by = FND_GLOBAL.USER_ID,
2643 last_update_date = sysdate,
2644 last_update_login = FND_GLOBAL.LOGIN_ID
2645 where
2646 fa_instance_id = UpdateFaDetails.p_fa_instance_id and
2647 parameter_name = UpdateFaDetails.p_parameter_name;
2648
2649 end UpdateFaDetails;
2650
2651 Procedure CallWIEvalProc (p_wi_instance_id IN NUMBER,
2652 p_procedure_name IN VARCHAR2,
2653 p_order_id IN NUMBER,
2654 p_line_item_id IN NUMBER,
2655 p_param_val IN VARCHAR2,
2656 p_param_ref_val IN VARCHAR2,
2657 p_param_eval_val OUT NOCOPY VARCHAR2,
2658 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
2659 p_return_code OUT NOCOPY NUMBER,
2660 p_error_description OUT NOCOPY VARCHAR2)
2661 is
2662 lv_order_id number;
2663 lv_line_item_id number;
2664 e_wi_eval_failed exception;
2665 begin
2666 if (p_line_item_id IS NULL) OR (lv_order_id IS NULL) then
2667 select order_id,line_item_id
2668 into lv_order_id, lv_line_item_id
2669 from XDP_FULFILL_WORKLIST
2670 where workitem_instance_id = p_wi_instance_id;
2671 else
2672 lv_order_id := p_order_id;
2673 lv_line_item_id := p_line_item_id;
2674 end if;
2675
2676 XDP_UTILITIES.CallWIParamEvalProc(
2677 p_procedure_name => CallWIEvalProc.p_procedure_name,
2678 p_order_id => lv_order_id,
2679 p_line_item_id => lv_line_item_id,
2680 p_wi_instance_id => CallWIEvalProc.p_wi_instance_id,
2681 p_param_val => CallWIEvalProc.p_param_val,
2682 p_param_ref_val => CallWIEvalProc.p_param_ref_val,
2683 p_param_eval_val => CallWIEvalProc.p_param_eval_val,
2684 p_param_eval_ref_val => CallWIEvalProc.p_param_eval_ref_val,
2685 p_return_code => CallWIEvalProc.p_return_code,
2686 p_error_description => CallWIEvalProc.p_error_description);
2687 if( p_return_code <> 0 ) THEN
2688 raise e_wi_eval_failed;
2689 end if;
2690 EXCEPTION
2691 when others then
2692 xdpcore.context( 'XDP_ENGINE', 'CallWIEvalProc', 'WI', p_wi_instance_id );
2693 END CallWIEvalProc;
2694
2695 Procedure ComputeWIParamValue(
2696 p_raise IN VARCHAR2,
2697 p_mode IN VARCHAR2,
2698 p_wi_instance_id IN NUMBER,
2699 p_procedure_name IN VARCHAR2,
2700 p_param_val IN VARCHAR2,
2701 p_param_ref_val IN VARCHAR2,
2702 p_default_value IN VARCHAR2,
2703 p_param_eval_val OUT NOCOPY VARCHAR2,
2704 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
2705 p_return_code OUT NOCOPY NUMBER,
2706 p_error_description OUT NOCOPY VARCHAR2)
2707 IS
2708 BEGIN
2709 p_param_eval_val := NVL(p_param_val,p_default_value);
2710 p_param_eval_ref_val := p_param_ref_val;
2711
2712 -- Date: 13-Jan-2005 Author: DPUTHIYE Bug: 4083708.
2713 -- Change Desc: In the following IF clause the constant identifier has been replaced with the value of
2714 -- 'Deferred' eval mode as defined in the UI. The value of the constant seemed to be wrong.
2715 --IF p_mode = pv_evalModeDeferred THEN
2716 IF p_mode = 'ON_WORKITEM_START' THEN
2717 IF p_procedure_name IS NOT NULL then
2718 EvaluateWIParamValue(
2719 p_order_id => NULL,
2720 p_line_item_id => NULL,
2721 p_workitem_id => NULL,
2722 p_wi_instance_id => p_wi_instance_id,
2723 p_parameter_name => NULL,
2724 p_procedure_name => p_procedure_name,
2725 p_mode => p_mode,
2726 p_param_val => p_param_val,
2727 p_param_ref_val => p_param_ref_val,
2728 p_param_eval_val => p_param_eval_val,
2729 p_param_eval_ref_val => p_param_eval_ref_val,
2730 p_return_code => p_return_code,
2731 p_error_description => p_error_description);
2732 IF p_return_code <> 0 THEN
2736 FND_MESSAGE.SET_TOKEN('ERROR_STRING2', p_error_description);
2733 IF p_raise = 'Y' THEN
2734 FND_MESSAGE.SET_NAME('XDP', 'XDP_EXEC_EVAL_PROC_ERROR');
2735 FND_MESSAGE.SET_TOKEN('ERROR_STRING1', p_procedure_name);
2737 APP_EXCEPTION.RAISE_EXCEPTION;
2738 END IF;
2739 END IF;
2740 END IF;
2741 END IF;
2742 END ComputeWIParamValue;
2743
2744 --
2745 -- This is the only procedure that should be called when one need to
2746 -- evaluate workitem parameter value
2747 --
2748
2749 Procedure EvaluateWIParamValue(
2750 p_order_id IN NUMBER,
2751 p_line_item_id IN NUMBER,
2752 p_workitem_id IN NUMBER,
2753 p_wi_instance_id IN NUMBER,
2754 p_parameter_name IN VARCHAR2,
2755 p_procedure_name IN VARCHAR2,
2756 p_mode IN VARCHAR2,
2757 p_param_val IN VARCHAR2,
2758 p_param_ref_val IN VARCHAR2,
2759 p_param_eval_val OUT NOCOPY VARCHAR2,
2760 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
2761 p_return_code OUT NOCOPY NUMBER,
2762 p_error_description OUT NOCOPY VARCHAR2)
2763 IS
2764
2765 l_wi_disp_name varchar2(100);
2766 l_message_params VARCHAR2(1000);
2767 l_evalFailed exception;
2768 lv_item_type varchar2(200);
2769 lv_item_key varchar2(200);
2770 lv_error_code NUMBER;
2771 lv_error_desc VARCHAR2(2000);
2772
2773 -- skilaru 06/03/2002 fix for ER# 2347984
2774 -- get the parent line work flow info to set the error
2775 -- context in case of wi parameter evaluation failure..
2776
2777 CURSOR c_getWFData IS
2778 SELECT oli.wf_item_type, oli.wf_item_key
2779 FROM xdp_order_line_items oli, xdp_fulfill_worklist fwl
2780 WHERE oli.line_item_id = fwl.line_item_id
2781 AND fwl.workitem_instance_id = p_wi_instance_id;
2782
2783 BEGIN
2784
2785 IF p_procedure_name IS NULL THEN
2786 p_return_code := -1;
2787 p_error_description := 'No evaluation procedure is specified when call EvaluateWIParamValue';
2788 END IF;
2789
2790 CallWIEvalProc (
2791 p_wi_instance_id => p_wi_instance_id,
2792 p_procedure_name => p_procedure_name,
2793 p_order_id => p_order_id,
2794 p_line_item_id=> p_line_item_id,
2795 p_param_val => p_param_val,
2796 p_param_ref_val => p_param_ref_val,
2797 p_param_eval_val => p_param_eval_val,
2798 p_param_eval_ref_val => p_param_eval_ref_val,
2799 p_return_code => p_return_code,
2800 p_error_description => p_error_description);
2801
2802 IF p_return_code <> 0 THEN
2803
2804 l_wi_disp_name := XDPCORE_WI.get_display_name( p_wi_instance_id );
2805 -- build the token string for xdp_errors_log..
2806 l_message_params := 'WI='||l_wi_disp_name||'#XDP#PARAM='|| p_parameter_name||'#XDP#';
2807
2808 -- set the business error...
2809 XDPCORE.error_context( 'WI', p_wi_instance_id, 'XDP_WI_PARAM_EVAL_FAILED', l_message_params );
2810 xdpcore.context( 'XDP_ENGINE', 'CallWIParamEvalProc', 'WI', p_wi_instance_id, p_error_description );
2811
2812 -- skilaru 06/03/2002 fix for ER# 2347984
2813 -- Set the Parameter name and Workitem instance ID in the workflow context
2814 -- so that when user responds to the notification with a new parameter value
2815 -- we should be able to extract the context and set the new value ..
2816
2817 FOR lv_rec IN c_getWFData LOOP
2818 lv_item_type := lv_rec.wf_item_type;
2819 lv_item_key := lv_rec.wf_item_key;
2820
2821 xdpcore.CheckNAddItemAttrNumber( lv_item_type, lv_item_key,
2822 'WORKITEM_INSTANCE_ID', p_wi_instance_id,
2823 lv_error_code, lv_error_desc);
2824
2825 xdpcore.CheckNAddItemAttrText( lv_item_type, lv_item_key,
2826 'WI_PARAMETER_NAME', p_parameter_name,
2827 lv_error_code, lv_error_desc);
2828 EXIT;
2829 END LOOP;
2830 Raise l_evalFailed;
2831 END IF;
2832
2833 IF p_mode = pv_evalModeDeferred THEN
2834 -- no parameter value will be written to the database table in this mode
2835 RETURN;
2836 ELSE
2837 SetWIParamValue(
2838 p_wi_instance_id =>p_wi_instance_id,
2839 p_workitem_id =>p_workitem_id,
2840 p_parameter_name =>p_parameter_name,
2841 p_is_value_evaluated =>'Y',
2842 p_parameter_value =>p_param_eval_val,
2843 p_parameter_ref_value => p_param_eval_ref_val,
2844 x_return_code => p_return_code,
2845 x_error_description => p_error_description
2846 );
2847 END IF;
2848
2849 exception
2850 when others then
2851 xdpcore.context( 'XDP_ENGINE', 'EvaluateWIParamValue', 'PARAMETER', p_parameter_name );
2852 END EvaluateWIParamValue;
2853
2854 Procedure SetWIParamValue(
2855 p_wi_instance_id IN NUMBER,
2856 p_workitem_id IN NUMBER,
2857 p_parameter_name IN VARCHAR2,
2858 p_parameter_value IN VARCHAR2,
2859 p_parameter_ref_value IN VARCHAR2,
2863 IS
2860 p_is_value_evaluated IN VARCHAR2,
2861 x_return_code OUT NOCOPY NUMBER,
2862 x_error_description IN VARCHAR2)
2864 lv_exists VARCHAR2(1) := 'N';
2865 BEGIN
2866 lv_exists := DoesWIParamExist(
2867 p_wi_instance_id => p_wi_instance_id,
2868 p_parameter_name => p_parameter_name
2869 );
2870
2871 IF lv_exists = 'Y' THEN
2872 UpdateWorklistDetails(
2873 p_wi_instance_id => p_wi_instance_id,
2874 p_parameter_name => p_parameter_name,
2875 p_is_value_evaluated => p_is_value_evaluated,
2876 p_parameter_value => p_parameter_value,
2877 p_parameter_ref_value => p_parameter_ref_value
2878 );
2879 ELSE
2880 LoadWorklistDetails(
2881 p_wi_instance_id => p_wi_instance_id,
2882 p_parameter_name => p_parameter_name,
2883 p_workitem_id => p_workitem_id,
2884 p_is_value_evaluated => p_is_value_evaluated,
2885 p_parameter_value => p_parameter_value,
2886 p_parameter_ref_value => p_parameter_ref_value);
2887 END IF;
2888 END SetWIParamValue;
2889
2890 Procedure CallFAEvalProc (p_fa_instance_id IN NUMBER,
2891 p_wi_instance_id IN NUMBER,
2892 p_procedure_name IN VARCHAR2,
2893 p_order_id IN NUMBER,
2894 p_line_item_id IN NUMBER,
2895 p_param_val IN VARCHAR2,
2896 p_param_ref_val IN VARCHAR2,
2897 p_param_eval_val OUT NOCOPY VARCHAR2,
2898 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
2899 p_return_code OUT NOCOPY NUMBER,
2900 p_error_description OUT NOCOPY VARCHAR2)
2901 is
2902
2903 lv_order_id number;
2904 lv_line_item_id number := -9999;
2905 lv_wi_id number;
2906
2907 CURSOR c_GetOrderData is
2908 select order_id, frt.workitem_instance_id,fwt.line_item_id
2909 from
2910 XDP_FULFILL_WORKLIST fwt,
2911 xdp_fa_runtime_list frt
2912 where
2913 fwt.workitem_instance_id = frt.workitem_instance_id and
2914 frt.fa_instance_id = p_fa_instance_id;
2915
2916 begin
2917
2918 if p_line_item_id is null then
2919 for v_GetOrderData in c_GetOrderData loop
2920 lv_order_id := v_GetOrderData.order_id;
2921 lv_wi_id := v_GetOrderData.workitem_instance_id;
2922 lv_line_item_id := v_GetOrderData.line_item_id;
2923 end loop;
2924
2925 if lv_line_item_id = -9999 then
2926 raise no_data_found;
2927 end if;
2928 else
2929 lv_order_id := p_order_id;
2930 lv_line_item_id := p_line_item_id;
2931 lv_wi_id := p_wi_instance_id;
2932 end if;
2933
2934 XDP_UTILITIES.CallFAParamEvalProc(
2935 p_procedure_name => CallFAEvalProc.p_procedure_name,
2936 p_order_id => lv_order_id,
2937 p_line_item_id => lv_line_item_id,
2938 p_wi_instance_id => lv_wi_id,
2939 p_fa_instance_id => CallFAEvalProc.p_fa_instance_id,
2940 p_param_val => CallFAEvalProc.p_param_val,
2941 p_param_ref_val => CallFAEvalProc.p_param_ref_val,
2942 p_param_eval_val => CallFAEvalProc.p_param_eval_val,
2943 p_param_eval_ref_val => CallFAEvalProc.p_param_eval_ref_val,
2944 p_return_code => CallFAEvalProc.p_return_code,
2945 p_error_description => CallFAEvalProc.p_error_description);
2946
2947 if( p_return_code <> 0 ) then
2948 xdpcore.context( 'XDP_ENGINE' , 'CallFAEvalProc', 'WI', p_wi_instance_id );
2949 end if;
2950 end CallFAEvalProc;
2951
2952 Procedure ComputeFAParamValue (p_raise IN VARCHAR2,
2953 p_mode IN VARCHAR2,
2954 p_fa_instance_id IN NUMBER,
2955 p_procedure_name IN VARCHAR2,
2956 p_order_id IN NUMBER,
2957 p_wi_instance_id IN NUMBER,
2958 p_line_item_id IN NUMBER,
2959 p_param_val IN VARCHAR2,
2960 p_param_ref_val IN VARCHAR2,
2961 p_default_value IN VARCHAR2,
2962 p_log_flag OUT NOCOPY BOOLEAN,
2963 p_param_eval_val OUT NOCOPY VARCHAR2,
2964 p_param_eval_ref_val OUT NOCOPY VARCHAR2,
2965 p_return_code OUT NOCOPY NUMBER,
2966 p_error_description OUT NOCOPY VARCHAR2)
2967 is
2968
2969
2970 begin
2971
2972 IF p_mode = 'N' THEN
2973 p_log_flag := FALSE;
2974
2975 if p_procedure_name is not null then
2976
2977 CallFAEvalProc(
2978 p_procedure_name => ComputeFAParamValue.p_procedure_name,
2979 p_order_id => ComputeFAParamValue.p_order_id,
2980 p_line_item_id => ComputeFAParamValue.p_line_item_id,
2981 p_wi_instance_id => ComputeFAParamValue.p_wi_instance_id,
2982 p_fa_instance_id => ComputeFAParamValue.p_fa_instance_id,
2983 p_param_val => ComputeFAParamValue.p_param_val,
2984 p_param_ref_val => ComputeFAParamValue.p_param_ref_val,
2985 p_param_eval_val => ComputeFAParamValue.p_param_eval_val,
2986 p_param_eval_ref_val => ComputeFAParamValue.p_param_eval_ref_val,
2987 p_return_code => ComputeFAParamValue.p_return_code,
2988 p_error_description => ComputeFAParamValue.p_error_description);
2989
2990 if p_raise = 'Y' then
2991 IF p_return_code <> 0 Then
2992 FND_MESSAGE.SET_NAME('XDP', 'XDP_EXEC_EVAL_PROC_ERROR');
2993 FND_MESSAGE.SET_TOKEN('ERROR_STRING1', p_procedure_name);
2994 FND_MESSAGE.SET_TOKEN('ERROR_STRING2', p_error_description);
2995 APP_EXCEPTION.RAISE_EXCEPTION;
2999 p_param_eval_val := NVL(p_param_val,p_default_value);
2996 End IF;
2997 end if;
2998 else
3000 p_param_eval_ref_val := p_param_ref_val;
3001 end if;
3002
3003 ELSIF p_param_val IS NOT NULL Then
3004 p_log_flag := TRUE;
3005 p_param_eval_val := p_param_val;
3006 p_param_eval_ref_val := p_param_ref_val;
3007 ELSE
3008 p_log_flag := TRUE;
3009 p_param_eval_val := p_default_value;
3010 p_param_eval_ref_val := p_param_ref_val;
3011 END IF;
3012
3013 end ComputeFAParamValue;
3014
3015
3016 Procedure SetFAParamValue ( p_fa_instance_id IN NUMBER,
3017 p_wi_instance_id IN NUMBER,
3018 p_fa_id IN NUMBER,
3019 p_parameter_name IN VARCHAR2,
3020 p_default_value IN VARCHAR2,
3021 p_parameter_value IN VARCHAR2,
3022 p_parameter_ref_value IN VARCHAR2,
3023 p_eval_flag IN BOOLEAN,
3024 p_eval_mode IN VARCHAR2,
3025 p_procedure_name IN VARCHAR2,
3026 p_order_id IN NUMBER,
3027 p_line_item_id IN NUMBER,
3028 p_return_code OUT NOCOPY NUMBER,
3029 p_error_description OUT NOCOPY VARCHAR2)
3030 is
3031 lv_exists varchar2(1) := 'N';
3032 lv_eval_val varchar2(4000);
3033 lv_eval_ref_val varchar2(4000);
3034 lv_eval_flag varchar2(1) := 'N';
3035 l_message_params varchar2(2000);
3036 l_fa_disp_name varchar2(100);
3037 begin
3038 lv_exists := DoesFAParamExist -- Needs to be changed by Maya
3039 (p_fa_instance_id => SetFAParamValue.p_fa_instance_id,
3040 p_parameter_name => SetFAParamValue.p_parameter_name);
3041
3042 if p_eval_flag = TRUE and
3043 p_eval_mode = 'Y' and
3044 p_procedure_name IS NOT NULL THEN
3045
3046 lv_eval_flag := 'Y';
3047
3048 CallFAEvalProc(
3049 p_procedure_name => SetFAParamValue.p_procedure_name,
3050 p_order_id => SetFAParamValue.p_order_id,
3051 p_line_item_id => SetFAParamValue.p_line_item_id,
3052 p_wi_instance_id => SetFAParamValue.p_wi_instance_id,
3053 p_fa_instance_id => SetFAParamValue.p_fa_instance_id,
3054 p_param_val => SetFAParamValue.p_parameter_value,
3055 p_param_ref_val => SetFAParamValue.p_parameter_ref_value,
3056 p_param_eval_val => lv_eval_val,
3057 p_param_eval_ref_val => lv_eval_ref_val,
3058 p_return_code => SetFAParamValue.p_return_code,
3059 p_error_description => SetFAParamValue.p_error_description);
3060
3061 if p_return_code <> 0 then
3062
3063 xdpcore.context( 'XDP_ENGINE', 'SetFAParamValue', 'FA', p_fa_instance_id, p_error_description );
3064 -- get FA display name..
3065 l_fa_disp_name := XDPCORE_FA.get_display_name( p_fa_instance_id );
3066
3067 -- build the token string for xdp_errors_log..
3068 l_message_params := 'FA='||l_fa_disp_name||'#XDP#PARAM='||p_parameter_name ||'#XDP#';
3069
3070 -- set the business error...
3071 XDPCORE.error_context( 'FA', p_fa_instance_id, 'XDP_FA_PARAM_EVAL_FAILED', l_message_params );
3072
3073 /*
3074 FND_MESSAGE.SET_NAME('XDP', 'XDP_EXEC_EVAL_PROC_ERROR');
3075 FND_MESSAGE.SET_TOKEN('ERROR_STRING1', p_procedure_name);
3076 FND_MESSAGE.SET_TOKEN('ERROR_STRING2', p_error_description);
3077 APP_EXCEPTION.RAISE_EXCEPTION;
3078 */
3079 end if;
3080 lv_eval_flag := 'Y';
3081 else
3082 lv_eval_flag := 'N';
3083 if p_eval_mode = 'Y' then
3084 lv_eval_val := NVL(p_parameter_value,p_default_value) ;
3085 else
3086 lv_eval_val := p_parameter_value ;
3087 end if;
3088
3089 lv_eval_ref_val := p_parameter_ref_value;
3090 end if;
3091
3092 IF lv_exists = 'Y' then
3093 UpdateFaDetails
3094 (p_fa_instance_id => SetFAParamValue.p_fa_instance_id,
3095 p_parameter_name => SetFAParamValue.p_parameter_name,
3096 p_evaluated_flag => lv_eval_flag,
3097 p_parameter_value => lv_eval_val,
3098 p_parameter_ref_value => lv_eval_ref_val);
3099 ELSE
3100 LoadFADetails( p_fa_instance_id => SetFAParamValue.p_fa_instance_id,
3101 p_parameter_name => SetFAParamValue.p_parameter_name, -- needs to be changed
3102 p_fa_id => SetFAParamValue.p_fa_id,
3103 p_is_value_evaluated => lv_eval_flag,
3104 p_parameter_value => lv_eval_val,
3105 p_parameter_ref_value => lv_eval_ref_val);
3106 END IF;
3107
3108 EXCEPTION
3109 when others then
3110 xdpcore.context( 'XDP_ENGINE', 'SetFAParamValue', 'FA', p_fa_instance_id );
3111 raise;
3112 end SetFAParamValue;
3113
3114
3115 Procedure GetFeConfigInfoText ( p_fe IN VARCHAR2,
3116 p_fe_id OUT NOCOPY NUMBER,
3117 p_fetype_id OUT NOCOPY NUMBER,
3118 p_fetype OUT NOCOPY VARCHAR2,
3119 p_fe_sw_generic OUT NOCOPY VARCHAR2,
3120 p_adapter_type OUT NOCOPY VARCHAR2,
3121 p_gen_lookup_id OUT NOCOPY NUMBER,
3122 p_connect_proc OUT NOCOPY VARCHAR2,
3123 p_disconnect_proc OUT NOCOPY VARCHAR2)
3124 is
3125
3126 lv_date DATE := sysdate;
3127
3128 CURSOR c_GetFeConfig is
3129 select
3130 fet.fE_ID,
3131 fet.fetype_id,
3132 fee.fulfillment_element_type,
3133 fsp.sw_generic,
3134 fsp.adapter_type,
3135 fsp.fe_sw_gen_lookup_id,
3139 XDP_FES fet,
3136 decode(fge.sw_start_proc,NULL,fsp.sw_start_proc,fge.sw_start_proc) connect_proc,
3137 decode(fge.sw_exit_proc,NULL,fsp.sw_exit_proc,fge.sw_exit_proc) disconnect_proc
3138 from
3140 XDP_FE_GENERIC_CONFIG fge,
3141 XDP_FE_TYPES fee,
3142 XDP_FE_SW_GEN_LOOKUP fsp
3143 where
3144 fet.fulfillment_element_name = p_fe and
3145 fet.FE_ID = fge.FE_ID and
3146 fet.fetype_id = fee.fetype_id and
3147 fge.FE_SW_GEN_LOOKUP_ID = fsp.FE_SW_GEN_LOOKUP_ID and
3148 fge.START_DATE = (
3149 select MAX( FGE2.START_DATE )
3150 from XDP_FE_GENERIC_CONFIG fge2
3151 where fge2.FE_ID= fet.FE_ID and
3152 lv_date >= fge2.START_DATE and
3153 lv_date <= NVL(fge2.END_DATE,lv_date));
3154
3155
3156 lv_exists varchar2(1) := 'N';
3157 begin
3158
3159 for v_GetFeConfig in c_GetFeConfig loop
3160 p_fe_id := v_GetFeConfig.fe_id;
3161 p_fetype_id := v_GetFeConfig.fetype_id;
3162 p_fetype := v_GetFeConfig.fulfillment_element_type;
3163 p_fe_sw_generic := v_GetFeConfig.sw_generic;
3164 p_adapter_type := v_GetFeConfig.adapter_type;
3165 p_gen_lookup_id := v_GetFeConfig.fe_sw_gen_lookup_id;
3166 p_connect_proc := v_GetFeConfig.connect_proc;
3167 p_disconnect_proc := v_GetFeConfig.disconnect_proc;
3168
3169 lv_exists := 'Y';
3170 exit;
3171 end loop;
3172
3173
3174 if lv_exists = 'N' then
3175 raise no_data_found;
3176 end if;
3177
3178 end GetFeConfigInfoText;
3179
3180 Procedure GetFeConfigInfoNum ( p_fe_id IN NUMBER,
3181 p_fe OUT NOCOPY VARCHAR2,
3182 p_fetype_id OUT NOCOPY NUMBER,
3183 p_fetype OUT NOCOPY VARCHAR2,
3184 p_fe_sw_generic OUT NOCOPY VARCHAR2,
3185 p_adapter_type OUT NOCOPY VARCHAR2,
3186 p_gen_lookup_id OUT NOCOPY NUMBER,
3187 p_connect_proc OUT NOCOPY VARCHAR2,
3188 p_disconnect_proc OUT NOCOPY VARCHAR2)
3189 is
3190
3191 lv_date DATE := sysdate;
3192
3193 CURSOR c_GetFeConfig is
3194 select
3195 fet.fulfillment_element_name,
3196 fet.fetype_id,
3197 fee.fulfillment_element_type,
3198 fsp.sw_generic,
3199 fsp.adapter_type,
3200 fsp.fe_sw_gen_lookup_id,
3201 decode(fge.sw_start_proc,NULL,fsp.sw_start_proc,fge.sw_start_proc) connect_proc,
3202 decode(fge.sw_exit_proc,NULL,fsp.sw_exit_proc,fge.sw_exit_proc) disconnect_proc
3203 from
3204 XDP_FES fet,
3205 XDP_FE_GENERIC_CONFIG fge,
3206 XDP_FE_TYPES fee,
3207 XDP_FE_SW_GEN_LOOKUP fsp
3208 where
3209 fet.fe_id = p_fe_id and
3210 fet.FE_ID = fge.FE_ID and
3211 fet.fetype_id = fee.fetype_id and
3212 fge.FE_SW_GEN_LOOKUP_ID = fsp.FE_SW_GEN_LOOKUP_ID and
3213 fge.START_DATE = (
3214 select MAX( FGE2.START_DATE )
3215 from XDP_FE_GENERIC_CONFIG fge2
3216 where fge2.FE_ID= fet.FE_ID and
3217 lv_date >= fge2.START_DATE and
3218 lv_date <= NVL(fge2.END_DATE,lv_date));
3219
3220 lv_exists varchar2(1) := 'N';
3221 begin
3222
3223 for v_GetFeConfig in c_GetFeConfig loop
3224 p_fe := v_GetFeConfig.fulfillment_element_name;
3225 p_fetype_id := v_GetFeConfig.fetype_id;
3226 p_fetype := v_GetFeConfig.fulfillment_element_type;
3227 p_fe_sw_generic := v_GetFeConfig.sw_generic;
3228 p_adapter_type := v_GetFeConfig.adapter_type;
3229 p_gen_lookup_id := v_GetFeConfig.fe_sw_gen_lookup_id;
3230 p_connect_proc := v_GetFeConfig.connect_proc;
3231 p_disconnect_proc := v_GetFeConfig.disconnect_proc;
3232
3233 lv_exists := 'Y';
3234 exit;
3235 end loop;
3236
3237
3238 if lv_exists = 'N' then
3239 raise no_data_found;
3240 end if;
3241
3242 end GetFeConfigInfoNum;
3243
3244
3245 Function GetAttrVal(p_attribute_name IN VARCHAR2,
3246 p_fe_id IN NUMBER,
3247 p_fe_sw_gen_lookup IN NUMBER) return varchar2
3248 is
3249
3250 lv_attrVal varchar2(4000);
3251 lv_conceal_data varchar2(10);
3252 lv_publicKey varchar2(80) := 'MGDGDDNDFGDIUHJKDFIUHTER';
3253 lv_privateKey VARCHAR2(80);
3254
3255 lv_exists varchar2(1) := 'N';
3256
3257 CURSOR c_GetAttrValue is
3258 select
3259 decode(fe_attribute_value,NULL,default_value,fe_attribute_value) attr_val,
3260 fan.conceal_data
3261 from
3262 xdp_fe_attribute_def fan,
3263 (select fe_attribute_id, fe_attribute_value
3264 from
3265 XDP_FE_ATTRIBUTE_VAL fae,
3266 XDP_FE_GENERIC_CONFIG fge
3267 where
3268 fae.fe_generic_config_id = fge.fe_generic_config_id and
3269 fge.fe_id = GetAttrVal.p_fe_id and
3270 fge.fe_sw_gen_lookup_id = GetAttrVal.p_fe_sw_gen_lookup ) fae2
3271 where
3272 fan.fe_attribute_name = GetAttrVal.p_attribute_name and
3273 fan.fe_sw_gen_lookup_id = GetAttrVal.p_fe_sw_gen_lookup and
3274 fan.fe_attribute_id = fae2.fe_attribute_id(+);
3275
3276 begin
3277
3278 for v_GetAttrValue in c_GetAttrValue loop
3279 lv_attrVal := v_GetAttrValue.attr_val;
3280 lv_conceal_data := v_GetAttrValue.conceal_data;
3281
3282 lv_exists := 'Y';
3283 end loop;
3284
3285 if lv_exists = 'N' then
3289 IF lv_conceal_data = 'Y' THEN
3286 raise no_data_found;
3287 end if;
3288
3290 lv_attrVal := DecodeAttrValue(p_attribute_value => lv_attrVal);
3291 END IF;
3292
3293 return (lv_attrVal);
3294
3295 end GetAttrVal;
3296
3297
3298 Function DecodeAttrValue ( p_attribute_value in varchar2) return varchar2
3299
3300 is
3301
3302 lv_attrVal varchar2(4000);
3303 lv_conceal_data varchar2(10);
3304 lv_publicKey varchar2(80) := 'MGDGDDNDFGDIUHJKDFIUHTER';
3305 lv_privateKey VARCHAR2(80);
3306
3307 begin
3308
3309 lv_privateKey := xdp_crypt_tools.GetKey(lv_publicKey);
3310 IF lv_privateKey IS NULL THEN
3311 FND_MESSAGE.SET_NAME('XDP', 'XDP_DECRYPT_CONCEAL_DATA');
3312 APP_EXCEPTION.RAISE_EXCEPTION;
3313 END IF;
3314 lv_attrVal := xdp_crypt_tools.Encrypt(p_attribute_value, lv_privateKey);
3315
3316 return (lv_attrVal);
3317
3318 end DecodeAttrValue;
3319
3320
3321 Procedure PreFetch_FeAttrList( p_fe_name in varchar2,
3322 p_attr_count OUT NOCOPY number)
3323 is
3324
3325 begin
3326 pv_FeAttributeList.delete;
3327 pv_FeAttributeList := Get_FE_AttributeVal_List(p_fe_name => PreFetch_FeAttrList.p_fe_name);
3328 p_attr_count := pv_FeAttributeList.count;
3329
3330 end PreFetch_FeAttrList;
3331
3332
3333 Procedure Fetch_FeAttrFromList( p_index in number,
3334 p_attr_name OUT NOCOPY varchar2,
3335 p_attr_value OUT NOCOPY varchar2)
3336 is
3337
3338 begin
3339 p_attr_name := pv_FeAttributeList(p_index).parameter_name;
3340 p_attr_value := pv_FeAttributeList(p_index).parameter_value;
3341
3342 end Fetch_FeAttrFromList;
3343
3344
3345 PROCEDURE EvaluateWIParamsOnStart(p_wi_instance_id in number)
3346 IS
3347 CURSOR c_GetAllWIParams IS
3348 SELECT
3349 wpr.parameter_name,
3350 xfw.order_id,
3351 xfw.line_item_id,
3352 xfw.workitem_id,
3353 wpr.evaluation_procedure,
3354 wpr.evaluation_mode
3355 FROM
3356 xdp_fulfill_worklist xfw,
3357 xdp_wi_parameters wpr
3358 WHERE
3359 xfw.workitem_instance_id = p_wi_instance_id
3360 AND xfw.workitem_id = wpr.workitem_id
3361 AND wpr.evaluation_procedure is not null
3362 AND wpr.evaluation_mode = pv_evalModeWIStart
3363 ORDER BY wpr.evaluation_seq;
3364 l_param_val VARCHAR2(4000);
3365 l_param_ref_val VARCHAR2(4000);
3366 l_param_eval_val VARCHAR2(4000);
3367 l_is_value_evaluated VARCHAR2(1);
3368 l_param_eval_ref_val VARCHAR2(4000);
3369 l_return_code NUMBER;
3370 l_error_description VARCHAR2(2000);
3371 e_parameter_eval_failed EXCEPTION;
3372 BEGIN
3373 FOR v_GetAllWIParams in c_GetAllWIParams LOOP
3374 BEGIN
3375 SELECT parameter_value, parameter_ref_value, is_value_evaluated
3376 INTO l_param_val,l_param_ref_val, l_is_value_evaluated
3377 FROM xdp_worklist_details
3378 WHERE workitem_instance_id = p_wi_instance_id
3379 AND parameter_name = v_GetAllWIParams.parameter_name;
3380 EXCEPTION
3381 WHEN NO_DATA_FOUND THEN
3382 l_param_val := NULL;
3383 l_param_ref_val := NULL;
3384 END;
3385
3386 IF ( l_is_value_evaluated = 'N' ) THEN
3387 BEGIN
3388 EvaluateWIParamValue(
3389 p_order_id => v_GetAllWIParams.order_id,
3390 p_line_item_id => v_GetAllWIParams.line_item_id,
3391 p_workitem_id => v_GetAllWIParams.workitem_id,
3392 p_wi_instance_id => p_wi_instance_id,
3393 p_parameter_name => v_GetAllWIParams.parameter_name,
3394 p_procedure_name => v_GetAllWIParams.evaluation_procedure,
3395 p_mode => v_GetAllWIParams.evaluation_mode,
3396 p_param_val => l_param_val,
3397 p_param_ref_val => l_param_ref_val,
3398 p_param_eval_val => l_param_eval_val,
3399 p_param_eval_ref_val => l_param_eval_ref_val,
3400 p_return_code => l_return_code,
3401 p_error_description => l_error_description);
3402 IF l_return_code <> 0 THEN
3403 RAISE e_parameter_eval_failed;
3404 END IF;
3405 EXCEPTION
3406 --skilaru 01/16/02
3407 --This should be handled by changing the signature and returning the
3408 --errcode. If we change this signature then we need to touch all
3409 --the packages its been refered from.. by not doing that we only
3410 --cant differetiate a system exception from a business exception.
3411 WHEN others THEN
3412 RAISE;
3413 END;
3414 END IF;
3415 -- skilaru 06/04/2002
3416 -- Reset the flag so that when we get NO_DATA_FOUND in the upper block
3417 -- we wont evaluate that if earlier iteration has initialized thie
3418 -- value to 'N' ...
3419 l_is_value_evaluated := NULL;
3420 END LOOP;
3421 EXCEPTION
3422 WHEN others THEN
3423 xdpcore.context( 'XDP_ENGINE', 'EvaluateWIParamsOnStart', 'WI', p_wi_instance_id );
3424 raise;
3425 END EvaluateWIParamsOnStart;
3426
3430
3427 -- Package initialization
3428 begin
3429 pv_FeAttributeList.delete;
3431 END XDP_ENGINE;