eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.XDP_ENG_UTIL

Source


1 PACKAGE BODY XDP_ENG_UTIL AS
2 /* $Header: XDPENGUB.pls 120.1 2005/06/15 22:57:10 appldev  $ */
3 
4 
5 Procedure Load_FA_Runtime_List( p_wi_instance_id IN NUMBER,
6 				p_fa_id		 IN NUMBER,
7 				p_status	 IN VARCHAR2,
8 				p_fe_id		 IN NUMBER,
9 				p_prov_seq	 IN NUMBER,
10 				p_priority	 IN NUMBER,
11 				p_fa_instance_id OUT NOCOPY NUMBER);
12 
13 -- PL/SQL Specification
14 
15  /*
16    This function will add a fulfillment action instnace
17    to a workitem instance.  The FA instance ID will return.
18   */
19  FUNCTION Add_FA_toWI
20            (p_wi_instance_id    IN NUMBER,
21 	    p_fa_name           IN VARCHAR2,
22 	    p_fe_name           IN VARCHAR2 DEFAULT NULL,
23 	    p_priority          IN number default 100,
24 	    p_provisioning_seq  IN NUMBER default 0)
25    return NUMBER IS
26 
27   lv_fa_instance_id NUMBER;
28   lv_fa_id          NUMBER := -1;
29   lv_fe_id          NUMBER := -1;
30 
31   CURSOR lc_fa_param(l_fa_id number) IS
32 	 SELECT parameter_name,
33                 NVL(evaluation_seq,0)
34 	   FROM xdp_fa_parameters
35           WHERE fulfillment_action_id = l_fa_id
36 	  ORDER by evaluation_seq;
37 
38   CURSOR c_GetFAID IS
39          SELECT fulfillment_action_id
40            FROM XDP_FULFILL_ACTIONS
41            WHERE fulfillment_action = p_fa_name;
42 
43   CURSOR c_GetFEID is
44          SELECT fe_id
45            FROM XDP_FES
46           WHERE fulfillment_element_name = p_fe_name;
47 
48 BEGIN
49 
50   FOR v_GetFAID in c_GetFAID
51       LOOP
52 	lv_fa_id := v_GetFAID.fulfillment_action_id;
53 	EXIT;
54       END LOOP;
55 
56   IF lv_fa_id = -1 THEN
57 	RAISE no_data_found;
58   END IF;
59 
60   IF p_fe_name IS NOT NULL THEN
61     FOR v_GetFEID in c_GetFEID
62         LOOP
63            lv_fe_id := v_GetFEID.fe_id;
64    	   EXIT;
65         END LOOP;
66 
67     IF lv_fe_id = -1 THEN
68 	RAISE no_data_found;
69     END IF;
70   ELSE
71     lv_fe_id := NULL;
72   END IF;
73 
74 	Load_FA_Runtime_List(   p_wi_instance_id => Add_FA_toWI.p_wi_instance_id,
75 				p_fa_id => lv_fa_id,
76 				p_status => 'STANDBY',
77 				p_fe_id => lv_fe_id,
78 				p_prov_seq => p_provisioning_seq,
79 				p_priority => p_priority,
80 				p_fa_instance_id => lv_fa_instance_id);
81 
82   For lv_param_rec in lc_fa_param(lv_fa_id) loop
83     BEGIN
84 	XDP_ENGINE.Set_FA_Param_value(
85 		p_fa_instance_id => lv_fa_instance_id,
86 		p_parameter_name => lv_param_rec.parameter_name,
87 		p_parameter_value => NULL,
88 		p_evaluation_required => TRUE);
89       EXCEPTION
90         WHEN OTHERS THEN
91           raise;
92     END;
93   END loop;
94 
95   return lv_fa_instance_id;
96  EXCEPTION
97    when others then
98      xdpcore.context( 'XDP_ENG_UTIL', 'Add_FA_toWI', 'WI',p_wi_instance_id );
99      raise;
100 
101 END Add_FA_toWI;
102 
103  /*
104    This function will add a fulfillment action instnace
105    to a workitem instance.  The FA instance ID will return.
106    This is an overload function which takes the fulfillment
107    action ID as one of its arguments.
108   */
109 
110  FUNCTION Add_FA_toWI(
111 	p_wi_instance_id 	IN   NUMBER,
112 	p_fulfillment_action_id IN   NUMBER,
113 	p_fe_name               IN VARCHAR2 DEFAULT NULL,
114 	p_priority              IN NUMBER default 100,
115 	p_provisioning_seq      IN NUMBER default 0)
116    RETURN NUMBER
117 IS
118   lv_fa_instance_id NUMBER;
119   lv_fe_id          NUMBER := -1;
120   lv_dummy          NUMBER;
121 
122   CURSOR lc_fa_param IS
123 	SELECT parameter_name,
124                NVL(evaluation_seq,0)
125 	  FROM xdp_fa_parameters
126          WHERE fulfillment_action_id = p_fulfillment_action_id
127  	 ORDER BY evaluation_seq;
128 
129   CURSOR c_GetFEID IS
130          SELECT fe_id
131            FROM XDP_FES
132            WHERE fulfillment_element_name = p_fe_name;
133 BEGIN
134 
135   /* Check if the fa ID exists in our configuration table*/
136   select 1 into lv_dummy
137   from XDP_FULFILL_ACTIONS
138   where fulfillment_action_id = p_fulfillment_action_id;
139 
140   if p_fe_name is not null then
141     For v_GetFEID in c_GetFEID loop
142 	lv_fe_id := v_GetFEID.fe_id;
143 	exit;
144     end loop;
145 
146     if lv_fe_id = -1 then
147 	raise no_data_found;
148     end if;
149 
150   else
151     lv_fe_id := NULL;
152   END IF;
153 
154 	Load_FA_Runtime_List(   p_wi_instance_id => Add_FA_toWI.p_wi_instance_id,
155 				p_fa_id => p_fulfillment_action_id,
156 				p_status => 'STANDBY',
157 				p_fe_id => lv_fe_id,
158 				p_prov_seq => p_provisioning_seq,
159 				p_priority => p_priority,
160 				p_fa_instance_id => lv_fa_instance_id);
161 
162   For lv_param_rec in lc_fa_param loop
163 	XDP_ENGINE.Set_FA_Param_value(
164 		p_fa_instance_id => lv_fa_instance_id,
165 		p_parameter_name => lv_param_rec.parameter_name,
166 		p_parameter_value => NULL,
167 		p_evaluation_required => TRUE);
168   END loop;
169 
170   return lv_fa_instance_id;
171 
172 exception
173   when others then
174    xdpcore.context( 'XDP_ENG_UTIL', 'Add_FA_toWI', 'WI', p_wi_instance_id );
175    raise;
176 END Add_FA_toWI;
177 
178 
179  /*
180    This function will add a fulfillment action instnace
181    to a workitem instance.  The FA instance ID will return.
182    This is an overloaded with FE_ID as input
183   */
184  FUNCTION Add_FA_toWI(
185 	p_wi_instance_id IN NUMBER,
186 	p_fa_name   IN VARCHAR2,
187 	p_fe_id  IN NUMBER DEFAULT NULL,
188 	p_priority  IN number default 100,
189 	p_provisioning_seq  IN NUMBER default 0)
190    return NUMBER
191 IS
192   lv_fa_instance_id NUMBER;
193   lv_fa_id  NUMBER := -1;
194   lv_fe_id  NUMBER := -1;
195 
196   CURSOR lc_fa_param(l_fa_id number) IS
197 	select parameter_name,
198              NVL(evaluation_seq,0)
199 	from xdp_fa_parameters
200       where
201          fulfillment_action_id = l_fa_id
202 	order by evaluation_seq;
203 
204   CURSOR c_GetFAID is
205    select fulfillment_action_id
206   from XDP_FULFILL_ACTIONS
207   where fulfillment_action = p_fa_name;
208 
209 BEGIN
210 
211   For v_GetFAID in c_GetFAID loop
212 	lv_fa_id := v_GetFAID.fulfillment_action_id;
213 	exit;
214   end loop;
215 
216   if lv_fa_id = -1 then
217 	raise no_data_found;
218   end if;
219 
220 	Load_FA_Runtime_List(   p_wi_instance_id => Add_FA_toWI.p_wi_instance_id,
221 				p_fa_id => lv_fa_id,
222 				p_status => 'STANDBY',
223 				p_fe_id => Add_FA_toWI.p_fe_id,
224 				p_prov_seq => p_provisioning_seq,
225 				p_priority => p_priority,
226 				p_fa_instance_id => lv_fa_instance_id);
227 
228   For lv_param_rec in lc_fa_param(lv_fa_id) loop
229 	XDP_ENGINE.Set_FA_Param_value(
230 		p_fa_instance_id => lv_fa_instance_id,
231 		p_parameter_name => lv_param_rec.parameter_name,
232 		p_parameter_value => NULL,
233 		p_evaluation_required => TRUE);
234   END loop;
235 
236   return lv_fa_instance_id;
237 
238 END Add_FA_toWI;
239 
240 
241  /*
242    This function will resubmit a fulfillment action instnace
243    for a workitem instance.  The FA instance ID will return.
244   */
245 
246  FUNCTION Resubmit_FA(
247 	p_resubmission_job_id 	IN   NUMBER,
248 	p_resub_fa_instance_id  IN   NUMBER)
249    return NUMBER
250 IS
251   lv_fa_instance_id NUMBER;
252 
253 BEGIN
254   select XDP_FA_RUNTIME_LIST_S.nextval
255   into lv_fa_instance_id
256   from  dual;
257 
258   insert into xdp_fa_runtime_list
259   (fa_instance_id,
260    workitem_instance_id,
261    fulfillment_action_id,
262    status_code,
263    resubmission_job_id,
264    fe_id,
265    provisioning_sequence,
266    priority,
267    start_provisioning_date,
268    created_by,
269    creation_date,
270    last_updated_by,
271    last_update_date,
272    last_update_login
273    )
274   select
275    lv_fa_instance_id,
276    workitem_instance_id,
277    fulfillment_action_id ,
278    'STANDBY',
279    p_resubmission_job_id ,
280    fe_id,
281    provisioning_sequence,
282    priority,
283    sysdate,
284    FND_GLOBAL.USER_ID,
285    sysdate,
286    FND_GLOBAL.USER_ID,
287    sysdate,
288    FND_GLOBAL.LOGIN_ID
289   from
290     xdp_fa_runtime_list
291   where
292     fa_instance_id = p_resub_fa_instance_id;
293 
294   insert into xdp_fa_details(
295     fa_instance_id,
296     parameter_name,
297     fulfillment_action_id,
298     is_value_evaluated,
299     parameter_value,
300     parameter_ref_value,
301     created_by,
302     creation_date,
303     last_updated_by,
304     last_update_date,
305     last_update_login
306   )
307   select
308     lv_fa_instance_id,
309     parameter_name,
310     fulfillment_action_id,
311     is_value_evaluated,
312     parameter_value,
313     parameter_ref_value,
314     FND_GLOBAL.USER_ID,
315     sysdate,
316     FND_GLOBAL.USER_ID,
317     sysdate,
318     FND_GLOBAL.LOGIN_ID
319   from
320     xdp_fa_details
321   where
322     fa_instance_id = p_resub_fa_instance_id;
323 
324   return lv_fa_instance_id;
325 
326 END Resubmit_FA;
327 
328 
329 
330  /*
331    This procedure will execute the SFM FA execution
332    process.  The workflow item type and item key for
333    the workitem are needed to establish parent child
334    relationship between the WI workflow and the FA
335    process.  At the end of the FA process, a call
336    to CONTINUEFLOW will be made to notify its parent
337    to continue process.
338   */
339  PROCEDURE Execute_FA(
340 		p_order_id       IN NUMBER,
341 		p_wi_instance_id IN NUMBER,
342 		p_fa_instance_id IN NUMBER,
343 		p_wi_item_type   IN varchar2,
344 		p_wi_item_key    IN varchar2,
345 		p_return_code    OUT NOCOPY NUMBER,
346 		p_error_description  OUT NOCOPY VARCHAR2,
347 		p_fa_caller  IN VARCHAR2 DEFAULT 'EXTERNAL')
348 IS
349 
350   lv_fa_itemtype varchar2(8);
351   lv_fa_itemkey  varchar2(240);
352   lv_priority  number;
353   lv_master varchar2(240);
354 
355   CURSOR c_GetPriority is
356    select priority
357    from xdp_fa_runtime_list
358    where
359      fa_instance_id = p_fa_instance_id;
360 
361 BEGIN
362 	p_return_code := 0;
363 
364    for v_GetPriority in c_GetPriority loop
365 	lv_priority := v_GetPriority.priority;
366 	exit;
367    end loop;
368 
369    /*
370      Create FA workflow process
371    */
372 
373 
374       XDPCORE_FA.CreateFAProcess(
375 			orderID => p_order_id,
376 			WIInstanceID => p_wi_instance_id,
377 			FAInstanceID => p_fa_instance_id,
378 			ParentItemType =>p_wi_item_type,
379 			ParentItemKey => p_wi_item_key,
380 			FACaller => p_fa_caller,
381 			FAMaster => lv_master,
382 			FAItemType => lv_fa_itemtype,
383 			FAItemKey => lv_fa_itemkey,
384 			ErrCode => p_return_code,
385 			ErrStr => p_error_description);
386 
387 
388    If p_return_code = 0 Then
389       XDP_AQ_UTILITIES.Add_FA_ToQ(
390 		p_order_id => p_order_id,
391 		p_wi_instance_id => p_wi_instance_id,
392 		p_fa_instance_id => p_fa_instance_id,
393 	        p_wf_item_type => lv_fa_itemtype ,
394                 p_wf_item_key => lv_fa_itemkey,
395 		p_priority => lv_priority,
396 		p_return_code => p_return_code,
397 		p_error_description => p_error_description);
398    END If;
399 
400 
401 EXCEPTION
402 WHEN OTHERS THEN
403   p_return_code := -191266;
404   FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
405   FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGUB');
406   FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
407   p_error_description := FND_MESSAGE.GET;
408 END Execute_FA;
409 
410  /*
411    This procedure will execute the SFM FA execution
412    process for resubmission. The workflow item type and item key for
413    the workitem are needed to establish parent child
414    relationship between the WI workflow and the FA
415    process.  At the end of the FA process, a call
416    to CONTINUEFLOW will be made to notify its parent
417    to continue process.
418   */
419  PROCEDURE Execute_Resubmit_FA(
420 		p_order_id       IN NUMBER,
421 		p_line_item_id   IN NUMBER,
422 		p_wi_instance_id IN NUMBER,
423 		p_fa_instance_id IN NUMBER,
424 		p_oru_item_type   IN varchar2,
425 		p_oru_item_key    IN varchar2,
426 		p_fa_master		IN VARCHAR2,
427 		p_resubmission_job_id IN NUMBER,
428 		p_return_code    OUT NOCOPY NUMBER,
429 		p_error_description  OUT NOCOPY VARCHAR2,
430 		p_fa_caller  IN VARCHAR2 DEFAULT 'EXTERNAL')
431 IS
432 
433   lv_fa_itemtype varchar2(8);
434   lv_fa_itemkey  varchar2(240);
435   lv_priority  number;
436   lv_master varchar2(240);
437 
438   CURSOR c_GetPriority is
439    select priority
440    from xdp_fa_runtime_list
441    where
442      fa_instance_id = p_fa_instance_id;
443 
444 BEGIN
445 	p_return_code := 0;
446 
447    for v_GetPriority in c_GetPriority loop
448 	lv_priority := v_GetPriority.priority;
449 	exit;
450    end loop;
451 
452    /*
453      Create FA workflow process
454    */
455 
456 
457       XDPCORE_FA.CreateFAProcess(
458 			orderID => p_order_id,
459 			lineItemID => p_line_item_id,
460 			WIInstanceID => p_wi_instance_id,
461 			FAInstanceID => p_fa_instance_id,
462 			ParentItemType =>p_oru_item_type,
463 			ParentItemKey => p_oru_item_key,
464 			FACaller => p_fa_caller,
465 			FAMaster => p_fa_master,
466 			resubmissionJobID => p_resubmission_job_id,
467 			FAItemType => lv_fa_itemtype,
468 			FAItemKey => lv_fa_itemkey,
469 			ErrCode => p_return_code,
470 			ErrStr => p_error_description);
471 
472 
473    If p_return_code = 0 Then
474       XDP_AQ_UTILITIES.Add_FA_ToQ(
475 		p_order_id => p_order_id,
476 		p_wi_instance_id => p_wi_instance_id,
477 		p_fa_instance_id => p_fa_instance_id,
478 	        p_wf_item_type => lv_fa_itemtype ,
479                 p_wf_item_key => lv_fa_itemkey,
480 		p_priority => lv_priority,
481 		p_return_code => p_return_code,
482 		p_error_description => p_error_description);
483    END If;
484 
485 
486 EXCEPTION
487 WHEN OTHERS THEN
488   p_return_code := -191266;
489   FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
490   FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPENGUB');
491   FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
492   p_error_description := FND_MESSAGE.GET;
493 END Execute_Resubmit_FA;
494 
495 
496 Procedure Load_FA_Runtime_List( p_wi_instance_id IN NUMBER,
497 				p_fa_id		 IN NUMBER,
498 				p_status	 IN VARCHAR2,
499 				p_fe_id		 IN NUMBER,
500 				p_prov_seq	 IN NUMBER,
501 				p_priority	 IN NUMBER,
502 				p_fa_instance_id OUT NOCOPY NUMBER)
503 is
504 
505 begin
506 
507   insert into xdp_fa_runtime_list
508   (fa_instance_id,
509    workitem_instance_id,
510    fulfillment_action_id,
511    status_code,
512    fe_id,
513    provisioning_sequence,
514    priority,
515    start_provisioning_date,
516    created_by,
517    creation_date,
518    last_updated_by,
519    last_update_date,
520    last_update_login
521    )
522   values
523   ( XDP_FA_RUNTIME_LIST_S.nextval,
524    p_wi_instance_id,
525    p_fa_id,
526    p_status,
527    p_fe_id,
528    p_prov_seq,
529    p_priority,
530    sysdate,
531    FND_GLOBAL.USER_ID,
532    sysdate,
533    FND_GLOBAL.USER_ID,
534    sysdate,
535    FND_GLOBAL.LOGIN_ID
536   ) returning fa_instance_id into p_fa_instance_id;
537 
538 exception
539   When others then
540    raise;
541 end Load_FA_Runtime_List;
542 
543 
544 END XDP_ENG_UTIL;