[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_WF
Source
1 PACKAGE BODY AMS_LIST_WF AS
2 /* $Header: amsvwlib.pls 120.4 2006/08/01 05:20:08 bmuthukr ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_LIST_WF ';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvwlib.pls';
6
7 -- Start of Comments
8 --
9 -- NAME
10 -- StartProcess
11 --
12 -- PURPOSE
13 -- This Procedure will Start the flow
14 --
15 -- IN
16 -- OUT
17 --
18 -- Used By Activities
19 --
20 -- NOTES
21 --
22 -- HISTORY
23 -- End of Comments
24
25 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
26 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
27 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
28
29 PROCEDURE Get_User_Role
30 ( p_user_id IN NUMBER,
31 x_role_name OUT NOCOPY VARCHAR2,
32 x_role_display_name OUT NOCOPY VARCHAR2 ,
33 x_return_status OUT NOCOPY VARCHAR2)
34 IS
35 CURSOR c_resource IS
36 SELECT employee_id source_id
37 FROM ams_jtf_rs_emp_v
38 WHERE resource_id = p_user_id ;
39 l_person_id number;
40 BEGIN
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 OPEN c_resource ;
43 FETCH c_resource INTO l_person_id ;
44 IF c_resource%NOTFOUND THEN
45 x_return_status := FND_API.G_RET_STS_ERROR;
46 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_RESOURCE_ID');
47 FND_MSG_PUB.Add;
48 END IF;
49 CLOSE c_resource ;
50 -- Pass the Employee ID to get the Role
51 WF_DIRECTORY.getrolename
52 ( p_orig_system => 'PER',
53 p_orig_system_id => l_person_id ,
54 p_name => x_role_name,
55 p_display_name => x_role_display_name );
56 IF x_role_name is null then
57 x_return_status := FND_API.G_RET_STS_ERROR;
58 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
59 FND_MSG_PUB.Add;
60 END IF;
61 END Get_User_Role;
62
63 PROCEDURE Handle_Err
64 (p_msg_count IN NUMBER , -- Number of error Messages
65 p_msg_data IN VARCHAR2 ,
66 x_error_msg OUT NOCOPY VARCHAR2
67 )
68 IS
69 l_msg_count NUMBER ;
70 l_msg_data VARCHAR2(2000);
71 l_final_data VARCHAR2(4000);
72 l_msg_index NUMBER ;
73 l_cnt NUMBER := 0 ;
74 BEGIN
75 -- Retriveing Error Message from FND_MSG_PUB
76 -- Called by most of the procedures if it encounter error
77 WHILE l_cnt < p_msg_count
78 LOOP
79 FND_MSG_PUB.Get
80 (p_msg_index => l_cnt + 1,
81 p_encoded => FND_API.G_FALSE,
82 p_data => l_msg_data,
83 p_msg_index_out => l_msg_index ) ;
84 l_final_data := l_final_data ||l_msg_index||': '
85 ||l_msg_data||fnd_global.local_chr(10) ;
86 l_cnt := l_cnt + 1 ;
87 END LOOP ;
88 x_error_msg := l_final_data;
89 END Handle_Err;
90
91
92 PROCEDURE StartProcess
93 ( p_list_header_id IN NUMBER
94 --,workflowprocess IN VARCHAR2 DEFAULT NULL) IS
95 ,workflowprocess IN VARCHAR2 ) IS
96 itemtype VARCHAR2(30) := 'AMSLISTG';
97 itemkey VARCHAR2(30) ;
98 itemuserkey VARCHAR2(80) ;
99
100 l_clistheader_rec AMS_ListHeader_PVT.list_header_rec_type;
101 l_listheader_rec AMS_ListHeader_PVT.list_header_rec_type;
102 l_display_name varchar2(360);
103 l_requester_role varchar2(360);
104 l_return_status varchar2(1);
105
106 --bmuthukr changes for bug 3895455.
107 l_user_id NUMBER;
108 cursor c1(l_last_updated_by number) is
109 select jtf.resource_id
110 from jtf_rs_resource_extns jtf
111 where jtf.user_id = l_last_updated_by;
112 --
113
114 BEGIN
115 -- Start Process :
116 -- If workflowprocess is passed, it will be run.
117 -- If workflowprocess is NOT passed, the selector function
118 -- defined in the item type will determine which process to run.
119 IF (AMS_DEBUG_HIGH_ON) THEN
120
121 Ams_Utility_pvt.debug_message('Start');
122 END IF;
123 IF (AMS_DEBUG_HIGH_ON) THEN
124
125 Ams_Utility_pvt.debug_message('Item Type : '||itemtype);
126 END IF;
127 IF (AMS_DEBUG_HIGH_ON) THEN
128
129 Ams_Utility_pvt.debug_message('Item key : '||itemkey);
130 END IF;
131
132 --dbms_output.put_line('Start');
133 AMS_ListHeader_PVT.Init_listheader_rec(l_listheader_rec );
134 l_listheader_rec.list_header_id := p_list_header_id;
135
136 AMS_ListHeader_PVT.Complete_ListHeader_rec(
137 p_listheader_rec => l_listheader_rec,
138 x_complete_rec => l_clistheader_rec
139 ) ;
140 update ams_list_headers_all
141 set object_version_number = object_version_number + 1
142 where list_header_id = p_list_header_id;
143
144 --dbms_output.put_line('list->' || l_clistheader_rec.list_header_id);
145 --dbms_output.put_line('listname ->' || l_clistheader_rec.list_name);
146 --dbms_output.put_line('object->' || l_clistheader_rec.object_version_number);
147 itemkey := p_list_header_id ||'_'||
148 l_clistheader_rec.object_version_number;
149 WF_ENGINE.CreateProcess (itemtype => 'AMSLISTG', --itemtype,
150 itemkey => itemkey ,
151 process => 'LIST_GENERATION');
152
153
154
155 --dbms_output.put_line('listname ->' || l_clistheader_rec.list_name);
156 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
157 itemkey => itemkey,
158 aname => 'LIST_NAME',
159 avalue => l_clistheader_rec.list_name);
160
161 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
162 itemkey => itemkey,
163 aname => 'LIST_TYPE',
164 avalue => l_clistheader_rec.list_type);
165
166 --dbms_output.put_line('listname ->' || l_clistheader_rec.list_header_id);
167 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
168 itemkey => itemkey,
169 aname => 'LIST_HEADER_ID',
170 avalue => l_clistheader_rec.list_header_id);
171
172 --dbms_output.put_line('status ifd ->' || l_clistheader_rec.user_status_id);
173 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
174 itemkey => itemkey,
175 aname => 'LIST_STATUS_ID',
176 avalue => l_clistheader_rec.user_status_id);
177
178 --dbms_output.put_line('status cde ->' || l_clistheader_rec.status_code);
179 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
180 itemkey => itemkey,
181 aname => 'LIST_STATUS_CODE',
182 avalue => l_clistheader_rec.status_code);
183
184 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
185 itemkey => itemkey,
186 aname => 'MONITOR_URL',
187 avalue => wf_monitor.geturl(
188 wf_core.TRANSLATE('WF_WEB_AGENT'),
189 itemtype,
190 itemkey,
191 'NO'));
192
193 --dbms_output.put_line('status ifd ->' || l_clistheader_rec.user_entered_start_time);
194 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
195 itemkey => itemkey,
196 aname => 'WAITING_TIME',
197 avalue => l_clistheader_rec.user_entered_start_time);
198 --bmuthukr for bug 3895455. Getting last updated user's id and passing..instead of passing owner id.
199 open c1(l_clistHeader_rec.last_updated_by);
200 fetch c1 into l_user_id;
201 close c1;
202
203 Get_User_Role(p_user_id => l_user_id, --l_clistHeader_rec.owner_user_id ,
204 x_role_name => l_requester_role,
205 x_role_display_name => l_display_name,
206 x_return_status => l_return_status);
207 --
208
209 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
210 RAISE FND_API.G_EXC_ERROR;
211 END IF;
212
213
214 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
215 itemkey => itemkey,
216 aname => 'LIST_OWNER',
217 avalue => l_requester_role );
218
219
220
221 WF_ENGINE.SetItemOwner(itemtype => itemtype,
222 itemkey => itemkey,
223 owner => l_requester_role);
224
225
226
227
228 WF_ENGINE.StartProcess (itemtype => itemtype,
229 itemkey => itemkey);
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 wf_core.context (G_PKG_NAME, 'StartProcess',
234 p_list_header_id,itemuserkey,workflowprocess);
235 RAISE;
236 END StartProcess;
237
238 -- Start of Comments
239 --
240 -- NAME
241 -- Generate_list
242 --
243 -- PURPOSE
244 -- This Procedure will generate List
245 -- Success or Failure
246 --
247 -- IN
248 -- Itemtype - AMSLISTG
249 -- Itemkey -
250 -- Accid -
251 -- Funmode - Run/Cancel/Timeout
252 --
253 -- OUT
254 -- Result - 'COMPLETE:SUCCESS' If the list is successfully completed
255 -- - 'COMPLETE:FAILURE' If there is an error in list
256 --
257 -- Used By Activities
258 -- Item Type - AMSLISTG
259 -- Activity - Generate_list
260 --
261 -- NOTES
262 --
263 --
264 -- HISTORY
265 -- End of Comments
266
267 PROCEDURE Generate_list(itemtype IN VARCHAR2,
268 itemkey IN VARCHAR2,
269 actid IN NUMBER,
270 funcmode IN VARCHAR2,
271 result OUT NOCOPY VARCHAR2) IS
272 l_return_status varchar2(1);
273 l_list_header_id number;
274 l_msg_count number;
275 l_msg_data varchar2(2000);
276 l_error_msg VARCHAR2(4000);
277 BEGIN
278 -- RUN mode - Normal Process Execution
279 IF (funcmode = 'RUN') THEN
280 l_list_header_id := WF_ENGINE.getItemAttrNumber
281 (itemtype => itemtype,
282 itemkey => itemkey,
283 aname => 'LIST_HEADER_ID');
284
285 l_return_status := FND_API.G_RET_STS_SUCCESS;
286
287 AMS_ListGeneration_PKG.GENERATE_LIST
288 ( p_api_version => 1.0,
289 p_init_msg_list => FND_API.G_TRUE,
290 p_commit => FND_API.G_FALSE,
291 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
292 p_list_header_id => l_list_header_id,
293 x_return_status => l_return_status,
294 x_msg_count => l_msg_count,
295 x_msg_data => l_msg_data) ;
296
297 IF (l_return_status = FND_API.G_RET_STS_SUCCESS )THEN
298 result := 'COMPLETE:SUCCESS' ;
299 RETURN;
300 ELSE
301 result := 'COMPLETE:FAILURE' ;
302 RETURN;
303 END IF;
304 END IF;
305
306 -- CANCEL mode - Normal Process Execution
307 IF (funcmode = 'CANCEL')
308 THEN
309 result := 'COMPLETE:' ;
310 RETURN;
311 END IF;
312
313 -- TIMEOUT mode - Normal Process Execution
314 IF (funcmode = 'TIMEOUT')
315 THEN
316 result := 'COMPLETE:' ;
317 RETURN;
318 END IF;
319 EXCEPTION
320 WHEN OTHERS THEN
321 Handle_Err
322 ( p_msg_count => l_msg_count, -- Number of error Messages
323 p_msg_data => l_msg_data ,
324 x_error_msg => l_error_msg );
325 wf_core.context(G_PKG_NAME,'Genrate_List',itemtype,
326 itemkey,actid,funcmode|| l_error_msg);
327 RAISE;
328 raise ;
329 END Generate_List ;
330
331 PROCEDURE Check_SCH(itemtype IN VARCHAR2,
332 itemkey IN VARCHAR2,
333 actid IN NUMBER,
334 funcmode IN VARCHAR2,
335 result OUT NOCOPY VARCHAR2) IS
336 l_return_status varchar2(1);
337 l_gen_date date;
338 l_list_header_id number;
339 l_msg_count number;
340 l_msg_data varchar2(2000);
341 l_error_msg VARCHAR2(4000);
342 BEGIN
343 -- RUN mode - Normal Process Execution
344 IF (funcmode = 'RUN') THEN
345 l_list_header_id := WF_ENGINE.getItemAttrNumber
346 (itemtype => itemtype,
347 itemkey => itemkey,
348 aname => 'LIST_HEADER_ID');
349
350 l_gen_date := WF_ENGINE.getItemAttrDate
351 (itemtype => itemtype,
352 itemkey => itemkey,
353 aname => 'WAITING_TIME');
354 if l_gen_date > sysdate then
355 update ams_list_headers_all
356 set status_code = 'SCHEDULED',
357 user_status_id = 301,
358 status_date = sysdate,
359 last_update_date = sysdate
360 WHERE list_header_id = l_list_header_id;
361
362 result := 'COMPLETE:AMS_SCH_TRUE' ;
363 RETURN;
364 else
365 update ams_list_headers_all
366 set status_code = 'GENERATING',
367 user_status_id = 302,
368 status_date = sysdate,
369 last_update_date = sysdate
370 WHERE list_header_id = l_list_header_id;
371
372 result := 'COMPLETE:AMS_SCH_FALSE' ;
373 RETURN;
374 end if;
375
376 END IF;
377
378 -- CANCEL mode - Normal Process Execution
379 IF (funcmode = 'CANCEL')
380 THEN
381 result := 'COMPLETE:' ;
382 RETURN;
383 END IF;
384
385 -- TIMEOUT mode - Normal Process Execution
386 IF (funcmode = 'TIMEOUT')
387 THEN
388 result := 'COMPLETE:' ;
389 RETURN;
390 END IF;
391 EXCEPTION
392 WHEN OTHERS THEN
393 Handle_Err
394 ( p_msg_count => l_msg_count, -- Number of error Messages
395 p_msg_data => l_msg_data ,
396 x_error_msg => l_error_msg );
397 wf_core.context(G_PKG_NAME,'Genrate_List',itemtype,
398 itemkey,actid,funcmode|| l_error_msg);
399 RAISE;
400 raise ;
401 END Check_sch ;
402
403 PROCEDURE Check_TAR(itemtype IN VARCHAR2,
404 itemkey IN VARCHAR2,
405 actid IN NUMBER,
406 funcmode IN VARCHAR2,
407 result OUT NOCOPY VARCHAR2) IS
408 l_return_status varchar2(1);
409 l_gen_date date;
410 l_list_header_id number;
411 l_msg_count number;
412 l_msg_data varchar2(2000);
413 l_error_msg VARCHAR2(4000);
414 l_list_type varchar2(30);
415 BEGIN
416 -- RUN mode - Normal Process Execution
417 IF (funcmode = 'RUN') THEN
418 l_list_header_id := WF_ENGINE.getItemAttrNumber
419 (itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'LIST_HEADER_ID');
422 l_list_type := WF_ENGINE.getItemAttrText
423 (itemtype => itemtype,
424 itemkey => itemkey,
425 aname => 'LIST_TYPE');
426 update ams_list_headers_all
427 set status_code = 'GENERATING',
428 user_status_id = 302,
429 status_date = sysdate,
430 last_update_date = sysdate
431 WHERE list_header_id = l_list_header_id;
432
433 if l_list_type = 'TARGET' then
434 result := 'COMPLETE:Y' ;
435 else
436 result := 'COMPLETE:N' ;
437 end if;
438
439 END IF;
440
441 -- CANCEL mode - Normal Process Execution
442 IF (funcmode = 'CANCEL')
443 THEN
444 result := 'COMPLETE:' ;
445 RETURN;
446 END IF;
447
448 -- TIMEOUT mode - Normal Process Execution
449 IF (funcmode = 'TIMEOUT')
450 THEN
451 result := 'COMPLETE:' ;
452 RETURN;
453 END IF;
454 COMMIT;
455 EXCEPTION
456 WHEN OTHERS THEN
457 Handle_Err
458 ( p_msg_count => l_msg_count, -- Number of error Messages
459 p_msg_data => l_msg_data ,
460 x_error_msg => l_error_msg );
461 wf_core.context(G_PKG_NAME,'Genrate_List',itemtype,
462 itemkey,actid,funcmode|| l_error_msg);
463 RAISE;
464 raise ;
465 END Check_TAR ;
466
467 PROCEDURE GEN_TARGET(itemtype IN VARCHAR2,
468 itemkey IN VARCHAR2,
469 actid IN NUMBER,
470 funcmode IN VARCHAR2,
471 result OUT NOCOPY VARCHAR2) IS
472 l_return_status varchar2(1);
473 l_gen_date date;
474 l_list_header_id number;
475 l_msg_count number;
476 l_msg_data varchar2(2000);
477 l_error_msg VARCHAR2(4000);
478 cursor c2 is
479 select list_used_by , list_used_by_id
480 from ams_act_lists
481 where list_header_id = l_list_header_id
482 and list_act_type = 'TARGET' ;
483 l_list_used_by varchar2(30);
484 l_list_used_by_id number;
485
486 BEGIN
487 -- RUN mode - Normal Process Execution
488 IF (funcmode = 'RUN') THEN
489 l_list_header_id := WF_ENGINE.getItemAttrNumber
490 (itemtype => itemtype,
491 itemkey => itemkey,
492 aname => 'LIST_HEADER_ID');
493
494 open c2;
495 fetch c2 into l_list_used_by, l_list_used_by_id;
496 close c2;
497
498 AMS_Act_List_PVT.generate_target_group_list_old
499 ( p_api_version => 1.0,
500 p_init_msg_list => FND_API.G_TRUE,
501 p_commit => FND_API.G_FALSE,
502 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
503 p_list_used_by => l_list_used_by,
504 p_list_used_by_id => l_list_used_by_id,
505 x_return_status => l_return_status,
506 x_msg_count => l_msg_count,
507 x_msg_data => l_msg_data
508 ) ;
509 result := 'COMPLETE:' ;
510
511 --To fix bug 5187640
512 IF (l_return_status = FND_API.G_RET_STS_SUCCESS )THEN
513 result := 'COMPLETE:SUCCESS' ;
514 RETURN;
515 ELSE
516 result := 'COMPLETE:FAILURE' ;
517 RETURN;
518 END IF;
519
520 END IF;
521
522 -- CANCEL mode - Normal Process Execution
523 IF (funcmode = 'CANCEL')
524 THEN
525 result := 'COMPLETE:' ;
526 RETURN;
527 END IF;
528
529 -- TIMEOUT mode - Normal Process Execution
530 IF (funcmode = 'TIMEOUT')
531 THEN
532 result := 'COMPLETE:' ;
533 RETURN;
534 END IF;
535 EXCEPTION
536 WHEN OTHERS THEN
537 Handle_Err
538 ( p_msg_count => l_msg_count, -- Number of error Messages
539 p_msg_data => l_msg_data ,
540 x_error_msg => l_error_msg );
541 wf_core.context(G_PKG_NAME,'Genrate_List',itemtype,
542 itemkey,actid,funcmode|| l_error_msg);
543 RAISE;
544 raise ;
545 END GEN_TARGET ;
546 -- -----------------------------------------------------------------------
547 PROCEDURE StartListBizEventProcess
548 ( p_list_header_id IN NUMBER) IS
549
550 l_parameter_list WF_PARAMETER_LIST_T;
551 l_new_item_key VARCHAR2(30);
552 l_return_status VARCHAR2(30);
553 l_start_time DATE;
554 l_sys_start_time DATE;
555 l_timezone NUMBER;
556 l_msg_count NUMBER ;
557 l_msg_data VARCHAR2(2000);
558 l_is_manual VARCHAR2(1);
559
560
561 CURSOR c_list_det IS
562 select USER_ENTERED_START_TIME, timezone_id
563 from ams_list_headers_vl
564 where list_header_id = p_list_header_id;
565
566 BEGIN
567
568 AMS_LISTGENERATION_PKG.is_manual (
569 p_list_header_id => p_list_header_id,
570 x_return_status => l_return_status,
571 x_msg_count => l_msg_count,
572 x_msg_data => l_msg_data,
573 x_is_manual => l_is_manual
574 );
575
576 IF (l_is_manual = 'Y')
577 then
578 RAISE FND_API.G_EXC_ERROR;
579 end if;
580
581
582 AMS_Utility_PVT.Create_Log (
583 x_return_status => l_return_status,
584 p_arc_log_used_by => 'LIST',
585 p_log_used_by_id => p_list_header_id,
586 p_msg_data => ' Raise Business event-- in StartListBusinessEventProcess process.',
587 p_msg_type => 'DEBUG'
588 );
589
590 AMS_Utility_PVT.debug_message('Raise Business event-- in StartListBizEventProcess process');
591 -- Raise a business event
592 l_new_item_key := p_list_header_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
593 l_parameter_list := WF_PARAMETER_LIST_T();
594 AMS_Utility_PVT.debug_message('Raise Business event-- after WF_PARAMETER_LIST_T call');
595 AMS_Utility_PVT.Create_Log (
596 x_return_status => l_return_status,
597 p_arc_log_used_by => 'LIST',
598 p_log_used_by_id => p_list_header_id,
599 p_msg_data => ' Raise Business event-- after WF_PARAMETER_LIST_T call',
600 p_msg_type => 'DEBUG'
601 );
602 AMS_Utility_PVT.Create_Log (
603 x_return_status => l_return_status,
604 p_arc_log_used_by => 'LIST',
605 p_log_used_by_id => p_list_header_id,
606 p_msg_data => 'Raise Business event p_list_header_id= '||to_char(p_list_header_id),
607 p_msg_type => 'DEBUG'
608 );
609 wf_event.AddParameterToList(p_name => 'LIST_HEADER_ID',
610 p_value => p_list_header_id,
611 p_parameterlist => l_parameter_list);
612 AMS_Utility_PVT.Create_Log (
613 x_return_status => l_return_status,
614 p_arc_log_used_by => 'LIST',
615 p_log_used_by_id => p_list_header_id,
616 p_msg_data => 'Raise Business event-- after AddParameterToList call',
617 p_msg_type => 'DEBUG'
618 );
619 AMS_Utility_PVT.debug_message('Raise Business event-- after AddParameterToList call');
620 OPEN c_list_det;
621 FETCH c_list_det INTO l_start_time, l_timezone;
622 CLOSE c_list_det;
623 /*
624 AMS_UTILITY_PVT.Convert_Timezone(
625 p_init_msg_list => FND_API.G_TRUE,
626 x_return_status => l_return_status,
627 x_msg_count => l_msg_count,
628 x_msg_data => l_msg_data,
629
630 p_user_tz_id => l_timezone,
631 p_in_time => l_start_time,
632 p_convert_type => 'SYS',
633
634 x_out_time => l_sys_start_time
635 );
636 */
637 -- If any errors happen let start time be sysdate
638 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
639 l_sys_start_time := SYSDATE;
640 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
641 l_sys_start_time := SYSDATE;
642 END IF;
643 AMS_Utility_PVT.debug_message('Raise Business event-- Start');
644 /*
645 update ams_list_headers_all set WORKFLOW_ITEM_KEY = l_new_item_key
646 where list_header_id = p_list_header_id;
647 */
648 update ams_list_headers_all
649 set status_code = 'SCHEDULED',
650 user_status_id = 301,
651 status_date = sysdate,
652 last_update_date = sysdate,
653 WORKFLOW_ITEM_KEY = l_new_item_key
654 WHERE list_header_id = p_list_header_id;
655
656
657 AMS_Utility_PVT.Create_Log (
658 x_return_status => l_return_status,
659 p_arc_log_used_by => 'LIST',
660 p_log_used_by_id => p_list_header_id,
661 p_msg_data => 'Raise Business event-- Start',
662 p_msg_type => 'DEBUG'
663 );
664 WF_EVENT.Raise
665 ( p_event_name => 'oracle.apps.ams.list.ListGenerationEvent',
666 p_event_key => l_new_item_key,
667 p_parameters => l_parameter_list,
668 p_send_date => l_start_time);
669 -- p_send_date => l_sys_start_time);
670 AMS_Utility_PVT.Create_Log (
671 x_return_status => l_return_status,
672 p_arc_log_used_by => 'LIST',
673 p_log_used_by_id => p_list_header_id,
674 p_msg_data => 'Raise Business event-- End',
675 p_msg_type => 'DEBUG'
676 );
677 AMS_Utility_PVT.debug_message('Raise Business event-- End');
678 commit;
679 END StartListBizEventProcess;
680
681 PROCEDURE Wf_Init_var(itemtype IN VARCHAR2,
682 itemkey IN VARCHAR2,
683 actid IN NUMBER,
684 funcmode IN VARCHAR2,
685 result OUT NOCOPY VARCHAR2) IS
686 -- itemtype VARCHAR2(30) := 'AMSLISTG';
687 -- itemkey VARCHAR2(30) ;
688 itemuserkey VARCHAR2(80) ;
689
690 l_clistheader_rec AMS_ListHeader_PVT.list_header_rec_type;
691 l_listheader_rec AMS_ListHeader_PVT.list_header_rec_type;
692 l_display_name varchar2(360);
693 l_requester_role varchar2(360);
694 l_return_status varchar2(1);
695 l_list_header_id number;
696 --bmuthukr changes for bug 3895455.
697 l_user_id NUMBER;
698 cursor c1(l_last_updated_by number) is
699 select jtf.resource_id
700 from jtf_rs_resource_extns jtf
701 where jtf.user_id = l_last_updated_by;
702 --
703 begin
704
705 IF (funcmode = 'RUN')
706 THEN
707
708 IF (AMS_DEBUG_HIGH_ON) THEN
709
710 Ams_Utility_pvt.debug_message('Start');
711 END IF;
712
713 --dbms_output.put_line('Start');
714 l_list_header_id := WF_ENGINE.GetItemAttrText(
715 itemtype => itemtype,
716 itemkey => itemkey ,
717 aname => 'LIST_HEADER_ID');
718
719 AMS_Utility_PVT.Create_Log (
720 x_return_status => l_return_status,
721 p_arc_log_used_by => 'LIST',
722 p_log_used_by_id => l_list_header_id,
723 p_msg_data => 'WF_INIT_VAR: started',
724 p_msg_type => 'DEBUG'
725 );
726
727 AMS_ListHeader_PVT.Init_listheader_rec(l_listheader_rec );
728 l_listheader_rec.list_header_id := l_list_header_id;
729
730 AMS_ListHeader_PVT.Complete_ListHeader_rec(
731 p_listheader_rec => l_listheader_rec,
732 x_complete_rec => l_clistheader_rec
733 ) ;
734 update ams_list_headers_all
735 set object_version_number = object_version_number + 1
736 where list_header_id = l_list_header_id;
737 /*
738 WF_ENGINE.CreateProcess (itemtype => 'AMSLISTG', --itemtype,
739 itemkey => itemkey ,
740 process => 'LIST_GENERATION');
741 */
742 --dbms_output.put_line('listname ->' || l_clistheader_rec.list_name);
743 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
744 itemkey => itemkey,
745 aname => 'LIST_NAME',
746 avalue => l_clistheader_rec.list_name);
747
748 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
749 itemkey => itemkey,
750 aname => 'LIST_TYPE',
751 avalue => l_clistheader_rec.list_type);
752
753 --dbms_output.put_line('listname ->' || l_clistheader_rec.list_header_id);
754 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
755 itemkey => itemkey,
756 aname => 'LIST_HEADER_ID',
757 avalue => l_clistheader_rec.list_header_id);
758
759 --dbms_output.put_line('status ifd ->' || l_clistheader_rec.user_status_id);
760 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
761 itemkey => itemkey,
762 aname => 'LIST_STATUS_ID',
763 avalue => l_clistheader_rec.user_status_id);
764
765 --dbms_output.put_line('status cde ->' || l_clistheader_rec.status_code);
766 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
767 itemkey => itemkey,
768 aname => 'LIST_STATUS_CODE',
769 avalue => l_clistheader_rec.status_code);
770
771 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
772 itemkey => itemkey,
773 aname => 'MONITOR_URL',
774 avalue => wf_monitor.geturl(
775 wf_core.TRANSLATE('WF_WEB_AGENT'),
776 itemtype,
777 itemkey,
778 'NO'));
779 --dbms_output.put_line('status ifd ->' || l_clistheader_rec.user_entered_start_time);
780 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
781 itemkey => itemkey,
782 aname => 'WAITING_TIME',
783 avalue => l_clistheader_rec.user_entered_start_time);
784 --bmuthukr for bug 3895455. Getting last updated user's id and passing..instead of passing owner id.
785 open c1(l_clistHeader_rec.last_updated_by);
786 fetch c1 into l_user_id;
787 close c1;
788
789 Get_User_Role(p_user_id => l_user_id, --l_clistHeader_rec.owner_user_id ,
790 x_role_name => l_requester_role,
791 x_role_display_name => l_display_name,
792 x_return_status => l_return_status);
793 --
794 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
795 RAISE FND_API.G_EXC_ERROR;
796 END IF;
797
798
799 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
800 itemkey => itemkey,
801 aname => 'LIST_OWNER',
802 avalue => l_requester_role );
803
804
805
806 WF_ENGINE.SetItemOwner(itemtype => itemtype,
807 itemkey => itemkey,
808 owner => l_requester_role);
809
810
811 /*
812
813 WF_ENGINE.StartProcess (itemtype => itemtype,
814 itemkey => itemkey);
815 */
816
817 END IF;
818
819 -- CANCEL mode - Normal Process Execution
820 IF (funcmode = 'CANCEL')
821 THEN
822 RETURN;
823 END IF;
824
825 -- TIMEOUT mode - Normal Process Execution
826 IF (funcmode = 'TIMEOUT')
827 THEN
828 RETURN;
829 END IF;
830 EXCEPTION
831 WHEN OTHERS THEN
832 wf_core.context (G_PKG_NAME, 'Wf_Init_var',itemtype,itemkey,actid,funcmode);
833 RAISE;
834
835 end Wf_Init_var;
836
837 PROCEDURE Wf_abort_process
838 ( p_list_header_id IN NUMBER) IS
839
840
841 l_item_type varchar2(100) := 'AMSLISTG';
842 l_item_key varchar2(100);
843 l_status_code varchar2(100);
844
845 cursor check_wf
846 is select item_key
847 from wf_item_activity_statuses
848 where item_type = l_item_type
849 and item_key like p_list_header_id|| '%'
850 and activity_status in ('ERROR','ACTIVE');
851
852 Begin
853 select status_code into l_status_code from ams_list_headers_all
854 where list_header_id = p_list_header_id;
855 if l_status_code <> 'DRAFT' then
856 update ams_list_headers_all
857 set WORKFLOW_ITEM_KEY = null,
858 status_code = 'FAILED',
859 user_status_id = 311,
860 last_update_date = sysdate,
861 status_date = sysdate
862 where list_header_id = p_list_header_id;
863 commit;
864 end if;
865
866 open check_wf ;
867 fetch check_wf into l_item_key;
868 close check_wf ;
869 if l_item_key is not null then
870 begin
871 WF_ENGINE.abortProcess(l_item_type ,
872 l_item_key);
873 exception
874 when no_data_found then
875 null;
876 end;
877 end if;
878
879 END Wf_abort_process;
880
881 -- -----------------------------------------
882 PROCEDURE Check_Item_Key (itemtype IN VARCHAR2,
883 itemkey IN VARCHAR2,
884 actid IN NUMBER,
885 funcmode IN VARCHAR2,
886 result OUT NOCOPY VARCHAR2) is
887
888 l_list_header_id NUMBER;
889 l_list_item_key NUMBER;
890 -- l_list_item_key VARCHAR2(60);
891 l_dummy NUMBER;
892 l_return_status VARCHAR2(1);
893
894 CURSOR c_item_key_name IS
895 select WORKFLOW_ITEM_KEY
896 from ams_list_headers_all where list_header_id = l_list_header_id;
897
898 BEGIN
899 -- RUN mode - Normal Process Execution
900 IF (funcmode = 'RUN')
901 THEN
902 l_list_header_id := WF_ENGINE.GetItemAttrText(
903 itemtype => itemtype,
904 itemkey => itemkey ,
905 aname => 'LIST_HEADER_ID');
906 AMS_Utility_PVT.Create_Log (
907 x_return_status => l_return_status,
908 p_arc_log_used_by => 'LIST',
909 p_log_used_by_id => l_list_header_id,
910 p_msg_data => 'l_list_header_id = ' || l_list_header_id,
911 p_msg_type => 'DEBUG'
912 );
913 OPEN c_item_key_name;
914 FETCH c_item_key_name INTO l_list_item_key;
915 CLOSE c_item_key_name;
916 AMS_Utility_PVT.Create_Log (
917 x_return_status => l_return_status,
918 p_arc_log_used_by => 'LIST',
919 p_log_used_by_id => l_list_header_id,
920 p_msg_data => 'l_list_item_key = ' || l_list_item_key,
921 p_msg_type => 'DEBUG'
922 );
923 IF l_list_item_key <> itemkey THEN
924 result := 'COMPLETE:N' ;
925 AMS_Utility_PVT.Create_Log (
926 x_return_status => l_return_status,
927 p_arc_log_used_by => 'LIST',
928 p_log_used_by_id => l_list_header_id,
929 p_msg_data => 'Check_Item_Key : 1. For List Header ID = ' || itemkey || result,
930 p_msg_type => 'DEBUG'
931 );
932 RETURN;
933 End if;
934 IF l_list_item_key = itemkey THEN
935 result := 'COMPLETE:Y' ;
936 AMS_Utility_PVT.Create_Log (
937 x_return_status => l_return_status,
938 p_arc_log_used_by => 'LIST',
939 p_log_used_by_id => l_list_header_id,
940 p_msg_data => 'Check_Item_Key : 1. For List Header ID = ' || itemkey || result,
941 p_msg_type => 'DEBUG'
942 );
943 RETURN;
944 END IF;
945 END IF;
946 -- CANCEL mode - Normal Process Execution
947 IF (funcmode = 'CANCEL')
948 THEN
949 result := 'COMPLETE:' ;
950 RETURN;
951 END IF;
952
953 -- TIMEOUT mode - Normal Process Execution
954 IF (funcmode = 'TIMEOUT')
955 THEN
956 result := 'COMPLETE:' ;
957 RETURN;
958 END IF;
959 EXCEPTION
960 WHEN OTHERS THEN
961 wf_core.context(G_PKG_NAME,'AMS_LIST_WF',itemtype,itemkey,actid,funcmode);
962 raise ;
963 END Check_Item_Key;
964
965 END AMS_LIST_WF;