[Home] [Help]
PACKAGE BODY: APPS.PAY_US_WORKFLOW_API_PKG
Source
1 PACKAGE BODY PAY_US_WORKFLOW_API_PKG as
2 /* $Header: payuswfapipkg.pkb 120.2 2011/07/15 10:19:30 rgottipa ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_workflow_api_pkg
21
22 Description :
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ------ ------- --------------------------
28 08-JUN-2003 jgoswami 115.0 Created
29 19-JUN-2003 jgoswami 115.1 3006871 Added procedure ExecuteConcProgram,
30 CheckProcessInputs
31
32 12-APR-2004 JGoswami 115.4 3316422 Added procedure IsResponseRequired
33 12-APR-2004 JGoswami 115.4 3316527 Modified get_assignment_info.
34 24-DEC-2009 mikarthi 115.5 9211154 Modified call to
35 FND_GLOBAL.Apps_Initialize by passing
36 security_group_id instead of security
37 profile id
38 15-Jul-2011 rgottipa 115.6 12704242 Added procedure GetRetroEnhancedInfo
39 ******************************************************************************/
40
41 -- IN
42 -- itemtype - type of the current item
43 -- itemkey - key of the current item
44 -- actid - process activity instance id
45 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
46 -- OUT
47 -- result
48 -- - COMPLETE[:<result ]
49 -- activity has completed with the indicated result
50 -- - WAITING
51 -- activity is waiting for additional transitions
52 -- - DEFERED
53 -- execution should be defered to background
54 -- - NOTIFIED[:<notification_id :<assigned_user ]
55 -- activity has notified an external entity that this
56 -- step must be performed. A call to wf_engine.CompleteActivty
57 -- will signal when this step is complete. Optional
58 -- return of notification ID and assigned user.
59 -- - ERROR[:<error_code ]
60 -- function encountered an error.
61
62 X_bg_id NUMBER;
63 X_org_id NUMBER;
64 X_req_id VARCHAR2(50);
65 X_payroll_id NUMBER;
66
67
68 /* ************************************************************************
69
70 This procedure gets the Assignment Info and create the Document.
71 ************************************************************************ */
72
73 PROCEDURE get_assignment_info (document_id IN VARCHAR2,
74 display_type IN VARCHAR2,
75 document IN OUT nocopy VARCHAR2,
76 document_type IN OUT nocopy VARCHAR2)
77
78 IS
79
80 ln_request_id number(15);
81 ln_business_group_id number(15);
82 ln_payroll_id number(15);
83 ld_payroll_date_paid varchar2(20);
84 ln_total number(9);
85 ln_complete number(9);
86 ln_error number(9);
87 ln_unprocessed number(9);
88 lv_business_group_name varchar2(240);
89 X_Segment1 VARCHAR2(240);
90 X_Segment2 VARCHAR2(240);
91 X_Segment3 VARCHAR2(240);
92 X_Segment4 VARCHAR2(240);
93 X_Segment5 VARCHAR2(240);
94 X_result VARCHAR2(2000);
95 l_cur_req_id Varchar2(240);
96 l_payroll_flag varchar2(1);
97 l_bg_flag varchar2(1);
98 l_space varchar2(25);
99
100
101 CURSOR asg_info_cur(p_req_id Number)
102 IS
103 select ppf.payroll_name PAYROLL_NAME,
104 to_char(count(paa.assignment_action_id)) ASG_COUNT,
105 paa.action_status ASG_STATUS
106 from pay_assignment_actions paa,
107 pay_payroll_actions ppa,
108 pay_all_payrolls_f ppf
109 where paa.payroll_action_id = ppa.payroll_action_id
110 and ppa.request_id = to_number(p_req_id)
111 and ppa.business_group_id = ln_business_group_id
112 and ppa.payroll_id = ppf.payroll_id
113 and ppf.payroll_id = ln_payroll_id
114 and ppa.effective_date between
115 ppf.effective_start_date and
116 ppf.effective_end_date
117 and ppa.effective_date = trunc(to_date(ld_payroll_date_paid,'YYYY/MM/DD HH24:MI:SS'))
118 and ppa.action_type = 'R'
119 and paa.source_action_id is null
120 and paa.run_type_id is null
121 group by ppf.payroll_name,paa.action_status;
122
123
124 BEGIN
125 l_payroll_flag := 'N';
126 l_bg_flag := 'N';
127 l_space := ' ';
128 ln_total := 0;
129 ln_complete := 0;
130 ln_error := 0;
131 ln_unprocessed := 0;
132
133 hr_utility.trace('B4 ASG Info');
134 hr_utility.trace('Document Id '||document_id);
135
136 ln_request_id := substr(document_id,1,instr(document_id,':') -1 );
137 ln_business_group_id := substr(document_id,instr(document_id,':',1,1)+1 ,
138 instr(document_id,':',1,2) -instr(document_id,':',1,1)-1 );
139 ln_payroll_id := substr(document_id,instr(document_id,':',1,2)+1 ,
140 instr(document_id,':',1,3) -instr(document_id,':',1,2)-1 );
141 ld_payroll_date_paid := substr(document_id,instr(document_id,':',1,3)+1 );
142
143 hr_utility.trace('ln_request_id = '||ln_request_id);
144 hr_utility.trace('ln_business_group_id = '||ln_business_group_id);
145 hr_utility.trace('ln_payroll_id = '||ln_payroll_id);
146 hr_utility.trace('ld_payroll_date_paid = '||ld_payroll_date_paid);
147
148 begin
149 select pbg.name into lv_business_group_name
150 from per_business_groups pbg
151 where pbg.business_group_id = ln_business_group_id;
152
153 hr_utility.trace('ln_business_group_id = '||ln_business_group_id);
154 exception when others then
155 null;
156 end;
157
158 FOR asg_info_rec in asg_info_cur(ln_request_id) LOOP
159
160
161 if lv_business_group_name is not null
162 and l_bg_flag <> 'Y'then
163 X_segment1 := 'Business Group :'||lv_business_group_name||l_space||wf_core.newline;
164 l_bg_flag := 'Y';
165 end if;
166
167 if asg_info_rec.PAYROLL_NAME is not null
168 and l_payroll_flag <> 'Y'then
169 X_segment2 := 'Payroll :'||asg_info_rec.PAYROLL_NAME||l_space||wf_core.newline;
170 l_payroll_flag := 'Y';
171 end if;
172
173 if asg_info_rec.ASG_STATUS = 'C' then
174 ln_complete := asg_info_rec.ASG_COUNT;
175 elsif asg_info_rec.ASG_STATUS = 'E' then
176 ln_error := asg_info_rec.ASG_COUNT;
177 elsif asg_info_rec.ASG_STATUS = 'U' then
178 ln_unprocessed := asg_info_rec.ASG_COUNT;
179 end if;
180
181 END LOOP ;
182
183 X_segment3 := 'Total Assignment Successfully Processed :'||to_char(ln_complete)||l_space||wf_core.newline;
184 X_segment4 := 'Total Assignment Errored :'||to_char(ln_error)||l_space||wf_core.newline;
185 X_segment5 := 'Total Assignment Un-Processed :'||to_char(ln_unprocessed)||l_space||wf_core.newline;
186
187 document := '<p>'||X_segment3||'<br>'||X_segment4||'<br>'||X_segment5||'<br></p>';
188
189 document := document || l_space||wf_core.newline;
190
191 hr_utility.trace('Document '||document);
192
193
194 document_type := 'text/html';
195 Exception When others then
196 hr_utility.trace('In Exception ');
197
198 End Get_Assignment_Info;
199
200 --
201
202 /*
203 procedure set_attr_value(itemtype in varchar2,
204 itemkey in varchar2,
205 actid in number,
206 funcmode in varchar2,
207 resultout out nocopy varchar2) is
208 ---> <local declarations>
209 lv_aname varchar2(30);
210
211 begin
212 -- Do nothing in cancel or timeout mode
213 if (funcmode <> wf_engine.eng_run) then
214 resultout := wf_engine.eng_null;
215 else
216
217 lv_aname := PAY_WORKFLOW_API_PKG.set_value(itemtype,itemkey,actid);
218 hr_utility.trace('lv_aname = '|| lv_aname);
219 -- put this activity in wait/notified state
220 if lv_aname is not null then
221 resultout := 'COMPLETE'||':'||wf_engine.eng_null;
222 else
223 resultout := wf_engine.eng_null;
224 end if;
225
226 end if;
227 return;
228 exception
229 when others then
230 WF_CORE.CONTEXT ('PAY_WORKFLOW_API_PKG', 'set_attr_value', itemtype,
231 itemkey, to_char(actid), funcmode);
232 raise;
233 end set_attr_value;
234
235 procedure get_attr_value(itemtype in varchar2,
236 itemkey in varchar2,
237 actid in number,
238 funcmode in varchar2,
239 resultout out nocopy varchar2) is
240 ---> <local declarations>
241 lv_aname varchar2(30);
242
243 begin
244 -- Do nothing in cancel or timeout mode
245 if (funcmode <> wf_engine.eng_run) then
246 resultout := wf_engine.eng_null;
247 else
248
249 lv_aname := PAY_WORKFLOW_API_PKG.get_value(itemtype,itemkey,actid)
250 -- put this activity in wait/notified state
251 if lv_aname is not null then
252 resultout := 'COMPLETE'||':'||wf_engine.eng_null;
253 else
254 resultout := wf_engine.eng_null;
255 end if;
256
257 end if;
258 return;
259 exception
260 when others then
261 WF_CORE.CONTEXT ('PAY_WORKFLOW_API_PKG', 'get_attr_value', itemtype,
262 itemkey, to_char(actid), funcmode);
263 raise;
264 end get_attr_value;
265 */
266
267 PROCEDURE get_message_details (document_id IN VARCHAR2,
268 display_type IN VARCHAR2,
269 document IN OUT nocopy VARCHAR2,
270 document_type IN OUT nocopy VARCHAR2)
271
272 IS
273
274 X_Segment1 VARCHAR2(240);
275 X_Segment2 VARCHAR2(240);
276 X_Segment3 VARCHAR2(240);
277 X_Segment4 VARCHAR2(240);
278 X_Segment5 VARCHAR2(240);
279 X_result VARCHAR2(2000);
280 l_cur_req_id Varchar2(240);
281 l_payroll_flag varchar2(1);
282 l_bg_flag varchar2(1);
283 l_space varchar2(25);
284
285
286 args Varchar2(240);
287 firstcolon number;
288 nextcolon number;
289 lv_itemtype Varchar2(240);
290 lv_itemkey Varchar2(240);
291 lv_attr_name Varchar2(30);
292
293 BEGIN
294 l_payroll_flag := 'N';
295 l_bg_flag := 'N';
296 l_space := ' ';
297 hr_utility.trace('B4 MSG Details ');
298 hr_utility.trace('Document Id '||document_id);
299
300 args := document_id;
301 -- args has format itemtype:itemkey:attr_name
302 firstcolon := instr(args,':');
303 nextcolon := instr(args,':',firstcolon+1);
304 lv_itemtype := substr(args,1, firstcolon-1);
305 hr_utility.trace('Item Type '||lv_itemtype);
306 lv_itemkey := substr(args,firstcolon+1,nextcolon -(firstcolon-1));
307 hr_utility.trace('Item Key '||lv_itemkey);
308 lv_attr_name := substr(args,nextcolon+1);
309 hr_utility.trace('Attr Name '||lv_attr_name);
310
311 -- l_cur_req_id := get_value(lv_itemtype,lv_itemkey,lv_attr_name);
312
313 X_segment1 := '<p> The Function GET_VALUE retrives the value of the request id for the given attribute name Request Id :'||l_cur_req_id||wf_core.newline ||'<br> </p>';
314 document := document || X_segment1||wf_core.newline;
315 document_type := 'text/html';
316 hr_utility.trace('Document '||document);
317 Exception When others then
318 hr_utility.trace('In Exception of Get message_details');
319
320 End get_message_details;
321
322 /*
323 -- Get Value
324 FUNCTION get_value(
325 wf_item_type in varchar2,
326 wf_item_key in varchar2,
327 attr_name in varchar2
328 ) RETURN VARCHAR2 IS
329 ignore_notfound boolean := FALSE;
330 attr_value varchar2(80);
331 Begin
332
333 -- From the Runtime Attributes Get the Value of the Attribute for the Workflow
334 -- Item Type, Item Key,Attribute Name.
335
336 attr_value := WF_Engine.GetItemAttrText(wf_item_type , wf_item_key , attr_name , ignore_notfound);
337
338 return attr_value;
339
340 Exception When others then
341 hr_utility.trace('In Exception of get_value');
342
343 End get_value;
344 */
345
346 /*
347 -- Set Value
348 FUNCTION set_value(
349 wf_item_type in varchar2 default 'NO_WF_ITEM',
350 wf_item_key in varchar2,
351 wf_actid in number
352 ) RETURN VARCHAR2 IS
353 aname Wf_Engine.NameTabTyp;
354 avalue Wf_Engine.TextTabTyp;
355
356 lv_attr_name varchar2(30);
357 lv_attr_value varchar2(30);
358 Begin
359
360 -- Add an Item Attribute at Runtime and Set the Value of the Attribute
361 -- for the Workflow Item Type, Item Key,Attribute Name.
362
363 lv_attr_name := wf_engine.GetActivityAttrText(wf_item_type,wf_item_key,wf_actid, 'ATTR_NAME');
364 lv_attr_value := wf_engine.GetActivityAttrText(wf_item_type,wf_item_key,wf_actid, 'ATTR_VALUE');
365 -- aname(1) := lv_attr_name;
366 -- avalue(1) := lv_attr_value;
367
368 hr_utility.trace('aname = '|| lv_attr_name);
369 hr_utility.trace('avalue = '|| lv_attr_value);
370 if aname is not null then
371 --WF_Engine.AddItemAttr(wf_item_type, wf_item_key, aname, avalue);
372 WF_Engine.AddItemAttr(wf_item_type, wf_item_key, lv_attr_name, lv_attr_value);
373 --return aname;
374 return lv_attr_name;
375 else
376 return null;
377 end if;
378 Exception When others then
379 hr_utility.trace('In Exception of set_value');
380
381 End set_value;
382 */
383
384 procedure GetRetroInformation(itemtype in varchar2,
385 itemkey in varchar2,
386 actid in number,
387 funcmode in varchar2,
388 resultout out nocopy varchar2) is
389 aname varchar2(80);
390 avalue number(30);
391 result varchar2(30);
392 lv_result varchar2(30);
393 lv_retro_asg_set varchar2(30);
394 ln_retro_asg_set_id number(30);
395 ln_get_retro_asgset_id number(30);
396 ignore_notfound boolean;
397 l_notification_id NUMBER;
398 l_notification_id2 NUMBER;
399 nid NUMBER;
400
401
402 begin
403
404 lv_result := 'SKIP';
405 ignore_notfound := FALSE;
406
407 hr_utility.trace(' In GetRetroInformation ');
408 if ( funcmode = 'RUN' ) then
409 hr_utility.trace('Function Mode = '||funcmode);
410 -- get attr value
411 --<your RUN executable statements>
412 hr_utility.trace('itemtype = '||itemtype);
413 hr_utility.trace('itemkey = '||itemkey);
414 hr_utility.trace('actid = '||to_char(actid));
415
416 aname := 'RETRO_ASSIGNMENT_SET_NAME';
417 lv_retro_asg_set := Wf_Engine.GetItemAttrText(
418 itemtype ,
419 itemkey ,
420 aname,
421 ignore_notfound);
422
423 aname := 'P_BUSINESS_GROUP_ID';
424
425 X_bg_id := Wf_Engine.GetItemAttrNumber(
426 itemtype,
427 itemkey,
428 aname,
429 ignore_notfound);
430
431
432 hr_utility.trace(' BG Id = '|| to_char(X_bg_id));
433 hr_utility.trace(' Retro Asg Set = '|| lv_retro_asg_set);
434
435 begin
436 select assignment_set_id
437 into ln_retro_asg_set_id
438 from hr_assignment_sets
439 where business_group_id = X_bg_id
440 and assignment_set_name like lv_retro_asg_set||'%';
441
442 hr_utility.trace(' Retro Asg Set ID = '|| to_char(ln_retro_asg_set_id));
443 exception when others then
444 result := 'SKIP';
445 hr_utility.trace('Skiping Retro Pay By Element as Assignment Set Not Found');
446 end;
447
448 if ln_retro_asg_set_id is not null then
449
450 Wf_Engine.SetItemAttrNumber
451 (itemtype,
452 itemkey,
453 'RETRO_ASSIGNMENT_SET_ID',
454 ln_retro_asg_set_id);
455 result := 'RUN';
456 hr_utility.trace('result = '||result);
457 else
458 result := 'SKIP';
459
460 hr_utility.trace('result = '||result);
461 end if;
462
463 resultout := 'COMPLETE:'||result;
464 hr_utility.trace(' Resultout = '|| resultout);
465 return;
466 elsif ( funcmode = 'CANCEL' ) then
467 -- <your CANCEL executable statements>
468 null;
469 result := 'SKIP';
470 resultout := 'COMPLETE:'||result;
471 hr_utility.trace('In Skip Resultout = '|| resultout);
472 return;
473
474 end if;
475
476 exception
477 when others then
478 WF_CORE.CONTEXT ('PAY_US_WORKFLOW_API_PKG', 'GetRetroInformation', itemtype, itemkey, to_char(actid), funcmode);
479 raise;
480 end GetRetroInformation;
481
482 --bug 12704242
483 procedure GetRetroEnhancedInfo(itemtype in varchar2,
484 itemkey in varchar2,
485 actid in number,
486 funcmode in varchar2,
487 resultout out nocopy varchar2) is
488 aname varchar2(80);
489 l_is_retro char(1) := 'N';
490 result varchar2(30);
491 lv_result varchar2(30);
492 ignore_notfound boolean;
493 l_notification_id NUMBER;
494 l_notification_id2 NUMBER;
495 nid NUMBER;
496
497
498 begin
499
500 lv_result := 'SKIP';
501 ignore_notfound := FALSE;
502 hr_utility.trace(' In GetRetroEnhancedInfo ');
503 if ( funcmode = 'RUN' ) then
504 hr_utility.trace('Function Mode = '||funcmode);
505 -- get attr value
506 --<your RUN executable statements>
507 hr_utility.trace('itemtype = '||itemtype);
508 hr_utility.trace('itemkey = '||itemkey);
509 hr_utility.trace('actid = '||to_char(actid));
510
511
512 aname := 'PAYROLL_ID';
513
514 X_payroll_id := to_number(Wf_Engine.GetItemAttrText(
515 itemtype,
516 itemkey,
517 aname,
518 ignore_notfound));
519
520
521 hr_utility.trace(' Business Id = '|| to_char(X_bg_id));
522
523 begin
524 select distinct('Y') into l_is_retro from
525 pay_assignment_actions paa,
526 pay_payroll_actions ppa,
527 pay_retro_assignments pra
528 where ppa.payroll_id = X_payroll_id
529 --ppa.business_group_id = X_bg_id
530 and ppa.payroll_action_id = paa.payroll_action_id
531 and paa.assignment_id = pra.assignment_id
532 and pra.retro_assignment_action_id is null;
533
534 exception when others then
535 result := 'SKIP';
536 hr_utility.trace('Skiping Retro Pay Enhanced: '||sqlerrm);
537 end;
538
539 if l_is_retro = 'Y' then
540 result := 'RUN';
541 else result := 'SKIP';
542 end if;
543 resultout := 'COMPLETE:'||result;
544 hr_utility.trace(' Resultout = '|| resultout);
545 return;
546 elsif ( funcmode = 'CANCEL' ) then
547 -- <your CANCEL executable statements>
548 null;
549 result := 'SKIP';
550 resultout := 'COMPLETE:'||result;
551 hr_utility.trace('In Skip Resultout = '|| resultout);
552 return;
553
554 end if;
555
556 exception
557 when others then
558 WF_CORE.CONTEXT ('PAY_US_WORKFLOW_API_PKG', 'GetRetroInformation', itemtype, itemkey, to_char(actid), funcmode);
559 raise;
560 end GetRetroEnhancedInfo;
561
562 procedure post_notification_set_attr(itemtype in varchar2,
563 itemkey in varchar2,
564 actid in number,
565 funcmode in varchar2,
566 resultout out nocopy varchar2) is
567 aname varchar2(80);
568 avalue number(30);
569 result varchar2(30);
570 lv_result varchar2(30);
571 lv_retro_asg_set varchar2(30);
572 ln_retro_asg_set_id number(30);
573 ln_get_retro_asgset_id number(30);
574 ignore_notfound boolean;
575 l_notification_id NUMBER;
576 l_notification_id2 NUMBER;
577 nid NUMBER;
578
579 begin
580
581 lv_result := 'SKIP';
582 ignore_notfound := FALSE;
583
584 hr_utility.trace('1. Function Mode = '||funcmode);
585 if ( funcmode = 'RUN' ) then
586 ln_get_retro_asgset_id := Wf_Engine.GetItemAttrNumber(
587 itemtype,
588 itemkey,
589 'RETRO_ASSIGNMENT_SET_ID');
590
591 hr_utility.trace('Function Mode = '||funcmode);
592 hr_utility.trace('1. ln_get_retro_asgset_id = '||to_char(ln_get_retro_asgset_id));
593
594 if ln_get_retro_asgset_id is not null then
595 resultout := 'COMPLETE:RUN';
596 hr_utility.trace(' Resultout = '|| resultout);
597 else
598 resultout := 'COMPLETE:SKIP';
599 hr_utility.trace(' Resultout = '|| resultout);
600 end if;
601
602 return;
603 end if;
604
605
606 if ( funcmode = 'RESPOND' ) then
607 hr_utility.trace('Function Mode = '||funcmode);
608 -- get attr value
609 --<your RUN executable statements>
610 hr_utility.trace('itemtype = '||itemtype);
611 hr_utility.trace('itemkey = '||itemkey);
612 hr_utility.trace('actid = '||to_char(actid));
613 l_notification_id2:= wf_engine.context_nid;
614 hr_utility.trace('2. l_notification_id = '||to_char(l_notification_id2));
615
616 if (l_notification_id is not null ) then
617 lv_result := WF_NOTIFICATION.GetAttrText( l_notification_id, 'RESULT');
618 hr_utility.trace('1. lv_result = '||lv_result);
619 elsif ( l_notification_id2 is not null) then
620 lv_result := WF_NOTIFICATION.GetAttrText( l_notification_id2, 'RESULT');
621 hr_utility.trace('2.lv_result = '||lv_result);
622 end if;
623
624 if (lv_result <> 'SKIP') then
625 aname := 'RETRO_ASSIGNMENT_SET_NAME';
626 lv_retro_asg_set := Wf_Engine.GetItemAttrText(
627 itemtype ,
628 itemkey ,
629 aname,
630 ignore_notfound);
631
632 aname := 'P_BUSINESS_GROUP_ID';
633
634 X_bg_id := Wf_Engine.GetItemAttrNumber(
635 itemtype,
636 itemkey,
637 aname,
638 ignore_notfound);
639
640
641 hr_utility.trace(' BG Id = '|| to_char(X_bg_id));
642 hr_utility.trace(' Retro Asg Set = '|| lv_retro_asg_set);
643
644 begin
645 select assignment_set_id
646 into ln_retro_asg_set_id
647 from hr_assignment_sets
648 where business_group_id = X_bg_id
649 and assignment_set_name like lv_retro_asg_set||'%';
650
651 hr_utility.trace(' Retro Asg Set ID = '|| to_char(ln_retro_asg_set_id));
652 exception when others then
653 result := 'SKIP';
654 hr_utility.trace('Skiping Retro Pay By Element as Assignment Set Not Found');
655 end;
656
657 if ln_retro_asg_set_id is not null then
658
659 Wf_Engine.SetItemAttrNumber
660 (itemtype,
661 itemkey,
662 'RETRO_ASSIGNMENT_SET_ID',
663 ln_retro_asg_set_id);
664 result := 'RUN';
665 hr_utility.trace('result = '||result);
666 else
667 result := 'SKIP';
668
669 hr_utility.trace('result = '||result);
670 end if;
671
672 resultout := 'COMPLETE:'||result;
673 hr_utility.trace(' Resultout = '|| resultout);
674 return;
675 else
676 result := 'SKIP';
677 resultout := 'COMPLETE:'||result;
678 hr_utility.trace('In Skip Resultout = '|| resultout);
679 return;
680
681 end if;
682 -- result := 'SKIP';
683 -- resultout := 'COMPLETE:'||result;
684 -- hr_utility.trace('In Skip Resultout = '|| resultout);
685 -- return;
686
687 end if;
688
689 if ( funcmode = 'CANCEL' ) then
690 -- <your CANCEL executable statements>
691 null;
692 result := 'SKIP';
693 resultout := 'COMPLETE:'||result;
694 return;
695 end if;
696
697
698 exception
699 when others then
700 WF_CORE.CONTEXT ('PAY_US_WORKFLOW_API_PKG', 'post_notification_set_attr', itemtype, itemkey, to_char(actid), funcmode);
701 raise;
702 end post_notification_set_attr;
703
704 PROCEDURE ExecuteConcProgram
705 ( p_itemtype in varchar2
706 , p_itemkey in varchar2
707 , p_actid in number
708 , p_funcmode in varchar2
709 , p_result in out nocopy varchar2
710 )
711 IS
712 l_user_id NUMBER;
713 l_resp_id NUMBER;
714 l_resp_appl_id NUMBER;
715 l_org_id NUMBER;
716 l_resultout varchar2(80);
717 l_security_group_id NUMBER;
718 l_per_security_id NUMBER;
719
720 work_item_org_id NUMBER;
721 session_org_id varchar2(100);
722 BEGIN
723
724 /*
725 cursor c1 is select
726 furg.user_id,
727 furg.responsibility_id,
728 furg.responsibility_application_id
729 from fnd_user_resp_groups furg,
730 fnd_user fu,
731 fnd_responsibility fr
732 where fu.user_id = furg.user_id
733 and furg.responsibility_id = fr.responsibility_id
734 and fu.user_name = 'JATIN'
735 and responsibility_key like 'JJ CA HRMS MANAGER';
736 */
737
738
739
740 --FOR crec in c1 loop
741
742 IF (p_funcmode = 'RUN') THEN
743
744 -- Code that determines Start Process
745 -- p_result := 'COMPLETE';
746 -- get Item Attributes for user_id, responsibility_id and application_id
747 -- this assumes that they were set as item attribute, probably through
748 -- definition.
749
750 hr_utility.trace('In set context of ExecuteConcProgram');
751 l_user_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'USER_ID');
752 l_resp_appl_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'APPLICATION_ID');
753 l_resp_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'RESPONSIBILITY_ID');
754 l_org_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'ORG_ID');
755
756 l_security_group_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'SECURITY_GROUP_ID');
757 l_per_security_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'PER_SECURITY_PROFILE_ID');
758 /*
759 l_user_id:= crec.user_id;
760 l_resp_appl_id:= crec.responsibility_application_id;
761 l_resp_id:= crec.responsibility_id;
762
763 */
764 hr_utility.trace('l_user_id = '|| l_user_id);
765 hr_utility.trace('l_resp_appl_id: = '|| l_resp_appl_id);
766 hr_utility.trace('l_resp_id = '|| l_resp_id);
767 hr_utility.trace('l_org_id = '|| l_org_id);
768 hr_utility.trace('l_security_group_id = '|| l_security_group_id);
769 hr_utility.trace('l_per_security_id = '|| l_per_security_id);
770
771 -- Set the database session context which also sets the org
772 --Bug 9211154 - Pass l_security_group_id instead of l_per_security_id
773 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_security_group_id);
774 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_per_security_id);
775
776 hr_utility.trace('In funcmode RUN of ExecuteConcProgram');
777 -- HR_SIGNON.Initialize_HR_Security;
778 hr_utility.trace('A4 HR_SIGNON.Initialize_HR_Security of ExecuteConcProgram');
779 fnd_wf_standard.ExecuteConcProgram(p_itemtype ,
780 p_itemkey ,
781 p_actid ,
782 p_funcmode ,
783 l_resultout );
784
785 p_result := l_resultout;
786 return;
787
788 ELSIF (p_funcmode = 'TEST_CTX') THEN
789 hr_utility.trace('In Test context of ExecuteConcProgram');
790 -- Code that compares current session context
791 -- with the work item context required to execute
792 -- the workflow safely
793
794 fnd_profile.get(name=>'ORG_ID',val=>session_org_id);
795
796 work_item_org_id := wf_engine.GetItemAttrNumber(p_itemtype, p_itemkey, 'ORG_ID');
797
798 if session_org_id = work_item_org_id then
799
800 p_result := 'COMPLETE:TRUE';
801
802 else
803 -- If the background engine is executing the
804 -- Selector/Callback function, the workflow engine
805 -- Will immediately run the Selector/Callback
806 -- Function in SET_CTX mode
807
808 p_result := 'COMPLETE:FALSE';
809
810 end if;
811
812 return;
813
814 ELSIF(p_funcmode = 'SET_CTX') THEN
815
816 -- Code that sets the current session context
817 -- based on the work item context stored in item attributes
818
819 -- get Item Attributes for user_id, responsibility_id and application_id
820 -- this assumes that they were set as item attribute, probably through
821 -- definition.
822
823 hr_utility.trace('In set context of ExecuteConcProgram');
824 l_user_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'USER_ID');
825 l_resp_appl_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'APPLICATION_ID');
826 l_resp_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'RESPONSIBILITY_ID');
827 l_org_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'ORG_ID');
828
829 /*
830 l_user_id:= crec.user_id;
831 l_resp_appl_id:= crec.responsibility_application_id;
832 l_resp_id:= crec.responsibility_id;
833
834 */
835 -- Set the database session context which also sets the org
836 --Bug 9211154 - Pass l_security_group_id
837 l_security_group_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'SECURITY_GROUP_ID');
838 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_security_group_id);
839 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
840
841
842 p_result := 'COMPLETE';
843
844 return;
845
846 ELSE
847 p_result := 'COMPLETE';
848 return;
849
850
851 END IF;
852 --end loop;
853
854
855 EXCEPTION
856 WHEN OTHERS THEN NULL;
857 WF_CORE.Context('PAY_US_WORKFLOW_API_PKG', 'ExecuteConcProgram',
858 p_itemtype, p_itemkey, p_actid, p_funcmode);
859 RAISE;
860
861
862 END ExecuteConcProgram;
863
864 PROCEDURE CheckProcessInputs
865 ( p_itemtype in varchar2
866 , p_itemkey in varchar2
867 , p_actid in number
868 , p_funcmode in varchar2
869 , p_result in out nocopy varchar2
870 )
871 IS
872 l_user_id NUMBER;
873 l_resp_id NUMBER;
874 l_resp_appl_id NUMBER;
875 l_org_id NUMBER;
876 l_resultout varchar2(80);
877 l_security_group_id NUMBER;
878 l_per_security_id NUMBER;
879
880 BEGIN
881
882 hr_utility.trace('In CheckProcessInputs');
883
884 IF (p_funcmode = 'RUN') THEN
885
886 -- Code that determines Start Process
887 -- p_result := 'COMPLETE:RUN';
888 -- get Item Attributes for user_id, responsibility_id and application_id
889 -- this assumes that they were set as item attribute, probably through
890 -- definition.
891 hr_utility.trace('In set context of ExecuteConcProgram');
892 l_user_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'USER_ID');
893 l_resp_appl_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'APPLICATION_ID');
894 l_resp_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'RESPONSIBILITY_ID');
895 l_org_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'ORG_ID');
896
897 l_security_group_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'SECURITY_GROUP_ID');
898 l_per_security_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'PER_SECURITY_PROFILE_ID');
899
900 hr_utility.trace('l_user_id = '|| l_user_id);
901 hr_utility.trace('l_resp_appl_id: = '|| l_resp_appl_id);
902 hr_utility.trace('l_resp_id = '|| l_resp_id);
903 hr_utility.trace('l_org_id = '|| l_org_id);
904 hr_utility.trace('l_security_group_id = '|| l_security_group_id);
905 hr_utility.trace('l_per_security_id = '|| l_per_security_id);
906
907 -- Set the database session context which also sets the org
908 --Bug 9211154 - Pass l_security_group_id instead of l_per_security_id
909 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_security_group_id);
910 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_per_security_id);
911
912 WF_STANDARD.COMPARE(p_itemtype ,
913 p_itemkey ,
914 p_actid ,
915 p_funcmode ,
916 l_resultout );
917
918 if (l_resultout = 'COMPLETE:EQ') then
919 p_result := 'COMPLETE:RUN';
920 elsif ((l_resultout = 'COMPLETE:LT') or
921 (l_resultout = 'COMPLETE:GT') or
922 (l_resultout = 'COMPLETE:NULL')) then
923 p_result := 'COMPLETE:SKIP';
924 end if;
925
926 return;
927
928 ELSE
929 p_result := 'COMPLETE:SKIP';
930 return;
931
932 END IF;
933
934
935 EXCEPTION
936 WHEN OTHERS THEN NULL;
937 WF_CORE.Context('PAY_US_WORKFLOW_API_PKG', 'CheckProcessInputs',
938 p_itemtype, p_itemkey, p_actid, p_funcmode);
939 RAISE;
940
941
942 END CheckProcessInputs;
943
944
945 PROCEDURE IsResponseRequired
946 ( p_itemtype in varchar2
947 , p_itemkey in varchar2
948 , p_actid in number
949 , p_funcmode in varchar2
950 , p_result in out nocopy varchar2
951 )
952 IS
953 l_user_id NUMBER;
954 l_resp_id NUMBER;
955 l_resp_appl_id NUMBER;
956 l_org_id NUMBER;
957 l_resultout varchar2(80);
958 l_security_group_id NUMBER;
959 l_per_security_id NUMBER;
960
961 BEGIN
962
963 hr_utility.trace('In IsResponseRequired');
964
965 IF (p_funcmode = 'RUN') THEN
966
967 -- Code that determines Start Process
968 -- p_result := 'COMPLETE:RUN';
969 -- get Item Attributes for user_id, responsibility_id and application_id
970 -- this assumes that they were set as item attribute, probably through
971 -- definition.
972 hr_utility.trace('In set context of ExecuteConcProgram');
973 l_user_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'USER_ID');
974 l_resp_appl_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'APPLICATION_ID');
975 l_resp_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'RESPONSIBILITY_ID');
976 l_org_id:= wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'ORG_ID');
977
978 l_security_group_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'SECURITY_GROUP_ID');
979 l_per_security_id := wf_engine.GetItemAttrNumber(p_itemtype,p_itemkey,'PER_SECURITY_PROFILE_ID');
980
981 hr_utility.trace('l_user_id = '|| l_user_id);
982 hr_utility.trace('l_resp_appl_id: = '|| l_resp_appl_id);
983 hr_utility.trace('l_resp_id = '|| l_resp_id);
984 hr_utility.trace('l_org_id = '|| l_org_id);
985 hr_utility.trace('l_security_group_id = '|| l_security_group_id);
986 hr_utility.trace('l_per_security_id = '|| l_per_security_id);
987
988 -- Set the database session context which also sets the org
989 --Bug 9211154 - Pass l_security_group_id instead of l_per_security_id
990 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_security_group_id);
991 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id,l_per_security_id);
992
993 WF_STANDARD.COMPARE(p_itemtype ,
994 p_itemkey ,
995 p_actid ,
996 p_funcmode ,
997 l_resultout );
998
999 if (l_resultout = 'COMPLETE:EQ') then
1000 p_result := 'COMPLETE:Y';
1001 elsif ((l_resultout = 'COMPLETE:LT') or
1002 (l_resultout = 'COMPLETE:GT') or
1003 (l_resultout = 'COMPLETE:NULL')) then
1004 p_result := 'COMPLETE:N';
1005 end if;
1006
1007 return;
1008
1009 ELSE
1010 p_result := 'COMPLETE:Y';
1011 return;
1012
1013 END IF;
1014
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN NULL;
1018 WF_CORE.Context('PAY_US_WORKFLOW_API_PKG', 'IsResponseRequired',
1019 p_itemtype, p_itemkey, p_actid, p_funcmode);
1020 RAISE;
1021
1022
1023 END IsResponseRequired;
1024
1025 --begin
1026 -- hr_utility.trace_on(null,'PYWF');
1027
1028 END PAY_US_WORKFLOW_API_PKG;