[Home] [Help]
PACKAGE BODY: APPS.PQH_WF
Source
1 PACKAGE BODY PQH_WF
2 /* $Header: pqhwfpc.pkb 120.2.12010000.3 2009/04/24 10:31:48 brsinha ship $ */
3 AS
4 g_package VARCHAR2(31) := 'PQH_WF.';
5 --
6 -- Gets itemkey and returns transaction id and transaction category id
7 --
8 PROCEDURE decode_itemkey(
9 p_itemkey IN VARCHAR2
10 , p_transaction_category_id OUT NOCOPY NUMBER
11 , p_transaction_id OUT NOCOPY NUMBER
12 )
13 IS
14 l_hyphen_pos NUMBER;
15 l_proc VARCHAR2(61) := g_package || 'decode_itemkey';
16 BEGIN
17 hr_utility.set_location(l_proc || 'Entering',10);
18 l_hyphen_pos := INSTR(p_itemkey, '-');
19 p_transaction_category_id := TO_NUMBER(SUBSTR(p_itemkey, 1, l_hyphen_pos - 1));
20 p_transaction_id := TO_NUMBER(SUBSTR(p_itemkey, l_hyphen_pos + 1));
21 hr_utility.set_location(l_proc || 'txn_cat'||p_transaction_category_id,20);
22 hr_utility.set_location(l_proc || 'txn_id'||p_transaction_id,30);
23 hr_utility.set_location(l_proc || 'Exiting',100);
24 exception when others then
25 p_transaction_category_id := null;
26 p_transaction_id := null;
27 raise;
28 END;
29
30 --
31 -- Create process log
32 --
33 procedure create_process_log(p_log_text varchar2
34 )
35 IS
36 l_process_log_id NUMBER;
37 l_proc VARCHAR2(61) := g_package || 'create_process_log';
38 BEGIN
39 hr_utility.set_location(l_proc || ' Entering',10);
40 /*
41 INSERT INTO ghr_process_log (
42 PROCESS_LOG_ID
43 , PROGRAM_NAME
44 , LOG_TEXT
45 , LOG_DATE
46 , LAST_UPDATE_DATE
47 , LAST_UPDATED_BY
48 , CREATION_DATE
49 , CREATED_BY
50 , LAST_UPDATE_LOGIN
51 )
52 VALUES (
53 ghr_process_log_s.nextval
54 , 'PQH_WORKFLOW'
55 , p_log_text
56 , sysdate
57 , sysdate
58 , 1
59 , sysdate
60 , 1
61 , 1
62 )
63 ;
64 */
65 hr_utility.set_location(l_proc || ' '|| p_log_text,90);
66 hr_utility.set_location(l_proc || ' Exiting',100);
67 END;
68 --
69 -- Check wether the transaction was approved by override approver or not
70 --
71 Function check_approver(p_itemkey in varchar2) return boolean is
72 l_transaction_id number;
73 l_tran_cat_id number;
74
75 cursor c1 is
76 select routing_history_id
77 from pqh_routing_history
78 where transaction_id = l_transaction_id
79 and transaction_category_id = l_tran_cat_id
80 and user_action_cd ='OVERRIDE'
81 and nvl(approval_cd,'X') <> 'APPROVED'
82 order by routing_history_id desc;
83 cursor c2(p_routing_history_id number) is
84 select approval_cd
85 from pqh_routing_history
86 where transaction_id = l_transaction_id
87 and routing_history_id > p_routing_history_id
88 and transaction_category_id = l_tran_cat_id
89 order by routing_history_id desc;
90
91 l_matchfound boolean := FALSE;
92 l_approval_cd varchar2(30);
93 l_proc VARCHAR2(61) := g_package || 'check_approver ';
94 begin
95 hr_utility.set_location('entering '||l_proc,10);
96 decode_itemkey( p_itemkey => p_itemkey
97 , p_transaction_category_id => l_tran_cat_id
98 , p_transaction_id => l_transaction_id);
99 for i in c1 loop
100 hr_utility.set_location('in loop for rh_id'||i.routing_history_id||l_proc,20);
101 open c2(i.routing_history_id);
102 fetch c2 into l_approval_cd;
103 if c2%notfound then
104 hr_utility.set_location('not routed after that'||l_proc,30);
105 l_matchfound := FALSE;
106 close c2;
107 else
108 hr_utility.set_location('routed after sending to ov '||l_proc,40);
109 if l_approval_cd ='APPROVED' then
110 hr_utility.set_location('Approved by OA'||l_proc,50);
111 l_matchfound := TRUE;
112 return l_matchfound;
113 end if;
114 close c2;
115 end if;
116 hr_utility.set_location('out loop for rh_id'||i.routing_history_id||l_proc,60);
117 end loop;
118 hr_utility.set_location('exiting'||l_proc,100);
119 return l_matchfound;
120 end check_approver;
121 --
122 -- Check if workflow process is running
123 --
124 FUNCTION wf_process_not_running (
125 p_itemkey IN VARCHAR2
126 , p_itemtype IN VARCHAR2)
127 RETURN BOOLEAN
128 IS
129 l_running BOOLEAN;
130 l_temp_holder VARCHAR2(1);
131 l_proc VARCHAR2(61) := g_package || 'wf_process_not_running ';
132 CURSOR c_wf IS
133 SELECT 'x'
134 FROM wf_items wfi
135 WHERE wfi.item_type = p_itemtype
136 AND wfi.item_key = p_itemkey;
137 BEGIN
138 hr_utility.set_location(l_proc || 'Entering',10);
139 OPEN c_wf;
140 FETCH c_wf INTO l_temp_holder;
141 l_running := c_wf%FOUND;
142 CLOSE c_wf;
143 if l_running THEN
144 hr_utility.set_location(l_proc || 'WF Running - TRUE', 100);
145 ELSE
146 hr_utility.set_location(l_proc || 'WF Running - FALSE', 100);
147 END IF;
148 hr_utility.set_location(l_proc || 'Exiting',100);
149 RETURN (NOT l_running);
150 END;
151 PROCEDURE fyi_notification( document_id in varchar2,
152 display_type in varchar2,
153 document in out nocopy varchar2,
154 document_type in out nocopy varchar2) is
155 l_proc VARCHAR2(61) := g_package || 'fyi_notification';
156 fyi_notification_not_defined EXCEPTION;
157 pragma exception_init (fyi_notification_not_defined, -6550);
158 l_tran_cat_id number;
159 l_transaction_id number;
160 l_tran_cat_name varchar2(30);
161 l_post_txn_function varchar2(61);
162 l_hyphen_pos number;
163 l_fyi_notification varchar2(200);
164
165 l_document varchar2(2000) := document;
166 l_document_type varchar2(2000) := document_type;
167
168 cursor c1 is select post_txn_function,short_name
169 from pqh_transaction_categories
170 where transaction_category_id = l_tran_cat_id ;
171 begin
172 hr_utility.set_location(l_proc || 'Entering',10);
173 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
174 l_hyphen_pos := INSTR(document_id, ':');
175 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
176 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
177 open c1;
178 fetch c1 into l_post_txn_function,l_tran_cat_name;
179 close c1;
180 document_type := 'text/plain';
181 l_fyi_notification := 'begin :l_status := ' || l_post_txn_function ||
182 '.fyi_notification(p_transaction_id =>'||
183 to_char(l_transaction_id) || '); end; ';
184
185 DECLARE
186 l_sqlerrm VARCHAR2(2000);
187 l_sqlcode NUMBER;
188 BEGIN
189 EXECUTE IMMEDIATE l_fyi_notification USING OUT document;
190 EXCEPTION
191 WHEN fyi_notification_not_defined THEN
192 document := 'fyi notification not defined';
193 WHEN OTHERS THEN
194 document := l_document;
195 document_type := l_document_type;
196 l_sqlcode := sqlcode;
197 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
198 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
199 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
200 END;
201 -- document := document || l_fyi_notification ;
202 hr_utility.set_location(l_proc || 'Exiting',100);
203 end;
204
205 PROCEDURE reject_notification( document_id in varchar2,
206 display_type in varchar2,
207 document in out nocopy varchar2,
208 document_type in out nocopy varchar2) is
209 l_proc VARCHAR2(61) := g_package || 'reject_notification';
210 reject_notice_not_defined EXCEPTION;
211 pragma exception_init (reject_notice_not_defined, -6550);
212 l_tran_cat_id number;
213 l_transaction_id number;
214 l_tran_cat_name varchar2(30);
215 l_post_txn_function varchar2(61);
216 l_hyphen_pos number;
217 l_reject_notification varchar2(200);
218 l_document varchar2(2000) := document;
219 l_document_type varchar2(2000) := document_type;
220 cursor c1 is select post_txn_function,short_name
221 from pqh_transaction_categories
222 where transaction_category_id = l_tran_cat_id ;
223 begin
224 hr_utility.set_location(l_proc || 'Entering',10);
225 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
226 document_type := 'text/plain';
227 l_hyphen_pos := INSTR(document_id, ':');
228 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
229 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
230 open c1;
231 fetch c1 into l_post_txn_function,l_tran_cat_name;
232 close c1;
233 l_reject_notification := 'begin :l_status := ' || l_post_txn_function ||
234 '.reject_notification(p_transaction_id =>'||
235 to_char(l_transaction_id) || '); end; ';
236
237 DECLARE
238 l_sqlerrm VARCHAR2(2000);
239 l_sqlcode NUMBER;
240 BEGIN
241 EXECUTE IMMEDIATE l_reject_notification USING OUT document;
242 EXCEPTION
243 WHEN reject_notice_not_defined THEN
244 document := 'reject notification not defined';
245 WHEN OTHERS THEN
246 document := l_document;
247 document_type := l_document_type;
248 l_sqlcode := sqlcode;
249 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
250 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
251 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
252 END;
253 hr_utility.set_location(l_proc || 'Exiting',100);
254 end;
255
256 PROCEDURE back_notification( document_id in varchar2,
257 display_type in varchar2,
258 document in out nocopy varchar2,
259 document_type in out nocopy varchar2) is
260 l_proc VARCHAR2(61) := g_package || 'back_notification';
261 back_notice_not_defined EXCEPTION;
262 pragma exception_init (back_notice_not_defined, -6550);
263 l_tran_cat_id number;
264 l_transaction_id number;
265 l_tran_cat_name varchar2(30);
266 l_post_txn_function varchar2(61);
267 l_hyphen_pos number;
268 l_back_notification varchar2(200);
269 l_document varchar2(2000) := document;
270 l_document_type varchar2(2000) := document_type;
271 cursor c1 is select post_txn_function,short_name
272 from pqh_transaction_categories
273 where transaction_category_id = l_tran_cat_id ;
274 begin
275 hr_utility.set_location(l_proc || 'Entering',10);
276 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
277 document_type := 'text/plain';
278 l_hyphen_pos := INSTR(document_id, ':');
279 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
280 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
281 open c1;
282 fetch c1 into l_post_txn_function,l_tran_cat_name;
283 close c1;
284 l_back_notification := 'begin :l_status := ' || l_post_txn_function ||
285 '.back_notification(p_transaction_id =>'||
286 to_char(l_transaction_id) || '); end; ';
287
288 DECLARE
289 l_sqlerrm VARCHAR2(2000);
290 l_sqlcode NUMBER;
291 BEGIN
292 EXECUTE IMMEDIATE l_back_notification USING OUT document;
293 EXCEPTION
294 WHEN back_notice_not_defined THEN
295 document := 'back notification not defined';
296 WHEN OTHERS THEN
297 document := l_document;
298 document_type := l_document_type;
299 l_sqlcode := sqlcode;
300 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
301 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
302 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
303 END;
304 hr_utility.set_location(l_proc || 'Exiting',100);
305 end;
306
307 PROCEDURE override_notification( document_id in varchar2,
308 display_type in varchar2,
309 document in out nocopy varchar2,
310 document_type in out nocopy varchar2) is
311 l_proc VARCHAR2(61) := g_package || 'override_notification';
312 override_notice_not_defined EXCEPTION;
313 pragma exception_init (override_notice_not_defined, -6550);
314 l_tran_cat_id number;
315 l_transaction_id number;
316 l_tran_cat_name varchar2(30);
317 l_post_txn_function varchar2(61);
318 l_hyphen_pos number;
319 l_override_notification varchar2(2000);
320 l_document varchar2(2000) := document;
321 l_document_type varchar2(2000) := document_type;
322 cursor c1 is select post_txn_function,short_name
323 from pqh_transaction_categories
324 where transaction_category_id = l_tran_cat_id ;
325 begin
326 hr_utility.set_location(l_proc || 'Entering',10);
327 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
328 document_type := 'text/plain';
329 l_hyphen_pos := INSTR(document_id, ':');
330 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
331 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
332 open c1;
333 fetch c1 into l_post_txn_function,l_tran_cat_name;
334 close c1;
335 l_override_notification := 'begin :l_status := ' || l_post_txn_function ||
336 '.override_notification(p_transaction_id =>'||
337 to_char(l_transaction_id) || '); end; ';
338
339 DECLARE
340 l_sqlerrm VARCHAR2(2000);
341 l_sqlcode NUMBER;
342 BEGIN
343 EXECUTE IMMEDIATE l_override_notification USING OUT document;
344 EXCEPTION
345 WHEN override_notice_not_defined THEN
346 document := 'override notification not defined';
347 WHEN OTHERS THEN
348 document := l_document;
349 document_type := l_document_type;
350 l_sqlcode := sqlcode;
351 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
352 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
353 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
354 END;
355 hr_utility.set_location(l_proc || 'Exiting',100);
356 end;
357
358 PROCEDURE apply_notification( document_id in varchar2,
359 display_type in varchar2,
360 document in out nocopy varchar2,
361 document_type in out nocopy varchar2) is
362 l_proc VARCHAR2(61) := g_package || 'apply_notification';
363 apply_notice_not_defined EXCEPTION;
364 pragma exception_init (apply_notice_not_defined, -6550);
365 l_tran_cat_id number;
366 l_transaction_id number;
367 l_tran_cat_name varchar2(30);
368 l_post_txn_function varchar2(61);
369 l_hyphen_pos number;
370 l_document varchar2(2000) := document;
371 l_document_type varchar2(2000) := document_type;
372 l_apply_notification varchar2(200);
373 cursor c1 is select post_txn_function,short_name
374 from pqh_transaction_categories
375 where transaction_category_id = l_tran_cat_id ;
376 begin
377 hr_utility.set_location(l_proc || 'Entering',10);
378 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
379 document_type := 'text/plain';
380 l_hyphen_pos := INSTR(document_id, ':');
381 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
382 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
383 open c1;
384 fetch c1 into l_post_txn_function,l_tran_cat_name;
385 close c1;
386 l_apply_notification := 'begin :l_status := ' || l_post_txn_function ||
387 '.apply_notification(p_transaction_id =>'||
388 to_char(l_transaction_id) || '); end; ';
389
390 DECLARE
391 l_sqlerrm VARCHAR2(2000);
392 l_sqlcode NUMBER;
393 BEGIN
394 EXECUTE IMMEDIATE l_apply_notification USING OUT document;
395 EXCEPTION
396 WHEN apply_notice_not_defined THEN
397 document := 'apply notification not defined';
398 WHEN OTHERS THEN
399 document := l_document;
400 document_type := l_document_type;
401 l_sqlcode := sqlcode;
402 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
403 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
404 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
405 END;
406 hr_utility.set_location(l_proc || 'Exiting',100);
407 end;
408
409 PROCEDURE warning_notification( document_id in varchar2,
410 display_type in varchar2,
411 document in out nocopy varchar2,
412 document_type in out nocopy varchar2) is
413 l_proc VARCHAR2(61) := g_package || 'warning_notification';
414 warning_notice_not_defined EXCEPTION;
415 pragma exception_init (warning_notice_not_defined, -6550);
416 l_tran_cat_id number;
417 l_transaction_id number;
418 l_tran_cat_name varchar2(30);
419 l_post_txn_function varchar2(61);
420 l_document varchar2(2000) := document;
421 l_document_type varchar2(2000) := document_type;
422 l_hyphen_pos number;
423 l_warning_notification varchar2(200);
424 cursor c1 is select post_txn_function,short_name
425 from pqh_transaction_categories
426 where transaction_category_id = l_tran_cat_id ;
427 begin
428 hr_utility.set_location(l_proc || 'Entering',10);
429 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
430 document_type := 'text/plain';
431 l_hyphen_pos := INSTR(document_id, ':');
432 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
433 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
434 open c1;
435 fetch c1 into l_post_txn_function,l_tran_cat_name;
436 close c1;
437 l_warning_notification := 'begin :l_status := ' || l_post_txn_function ||
438 '.warning_notification(p_transaction_id =>'||
439 to_char(l_transaction_id) || '); end; ';
440
441 DECLARE
442 l_sqlerrm VARCHAR2(2000);
443 l_sqlcode NUMBER;
444 BEGIN
445 EXECUTE IMMEDIATE l_warning_notification USING OUT document;
446 EXCEPTION
447 WHEN warning_notice_not_defined THEN
448 document := 'warning notification not defined';
449 WHEN OTHERS THEN
450 document := l_document;
451 document_type := l_document_type;
452 l_sqlcode := sqlcode;
453 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
454 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
455 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
456 END;
457 hr_utility.set_location(l_proc || 'Exiting',100);
458 end;
459
460 PROCEDURE respond_notification( document_id in varchar2,
461 display_type in varchar2,
462 document in out nocopy varchar2,
463 document_type in out nocopy varchar2) is
464 l_proc VARCHAR2(61) := g_package || 'respond_notification';
465 respond_notice_not_defined EXCEPTION;
466 pragma exception_init (respond_notice_not_defined, -6550);
467 l_tran_cat_id number;
468 l_transaction_id number;
469 l_tran_cat_name varchar2(30);
470 l_post_txn_function varchar2(61);
471 l_hyphen_pos number;
472 l_respond_notification varchar2(200);
473 l_document varchar2(2000) := document;
474 l_document_type varchar2(2000) := document_type;
475 cursor c1 is select post_txn_function,short_name
476 from pqh_transaction_categories
477 where transaction_category_id = l_tran_cat_id ;
478 begin
479 hr_utility.set_location(l_proc || 'Entering',10);
480 hr_utility.set_location(l_proc || 'document id ' || document_id,20);
481 document_type := 'text/plain';
482 l_hyphen_pos := INSTR(document_id, ':');
483 l_transaction_id := TO_NUMBER(SUBSTR(document_id, 1, l_hyphen_pos - 1));
484 l_tran_cat_id := TO_NUMBER(SUBSTR(document_id, l_hyphen_pos + 1));
485 open c1;
486 fetch c1 into l_post_txn_function,l_tran_cat_name;
487 close c1;
488 l_respond_notification := 'begin :l_status := ' || l_post_txn_function ||
489 '.respond_notification(p_transaction_id =>'||
490 to_char(l_transaction_id) || '); end; ';
491
492 DECLARE
493 l_sqlerrm VARCHAR2(2000);
494 l_sqlcode NUMBER;
495 BEGIN
496 EXECUTE IMMEDIATE l_respond_notification USING OUT document;
497 EXCEPTION
498 WHEN respond_notice_not_defined THEN
499 document := 'respond notification not defined';
500 WHEN OTHERS THEN
501 l_document := document;
502 l_document_type := document_type;
503 l_sqlcode := sqlcode;
504 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
505 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
506 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
507 END;
508 -- document := document || l_respond_notification ;
509 -- document := 'Testing' || document_id;
510 hr_utility.set_location(l_proc || 'Exiting',100);
511 end;
512
513 --
514 -- Create routing history row
515 --
516 PROCEDURE create_routing_history(
517 p_transaction_category_id IN NUMBER
518 , p_transaction_id IN NUMBER
519 , p_routing_category_id IN NUMBER
520 , p_pos_structure_version_id IN NUMBER
521 , p_user_action_cd IN VARCHAR2
522 , p_approval_cd IN VARCHAR2
523 , p_notification_date IN DATE
524 , p_comments IN VARCHAR2
525 , p_forwarded_to_user_id IN NUMBER
526 , p_forwarded_to_role_id IN NUMBER
527 , p_forwarded_to_position_id IN NUMBER
528 , p_forwarded_to_assignment_id IN NUMBER
529 , p_forwarded_to_member_id IN NUMBER
530 , p_forwarded_by_user_id IN NUMBER
531 , p_forwarded_by_role_id IN NUMBER
532 , p_forwarded_by_position_id IN NUMBER
533 , p_forwarded_by_assignment_id IN NUMBER
534 , p_forwarded_by_member_id IN NUMBER
535 , p_routing_history_id OUT NOCOPY NUMBER
536 )
537 IS
538 l_proc VARCHAR2(61) := g_package || 'create_routing_history';
539 l_rha_tab pqh_routing_history_api.t_rha_tab;
540 l_routing_history_id pqh_routing_history.routing_history_id%type;
541 l_object_version_number pqh_routing_history.object_version_number%type;
542 l_effective_date date := trunc(sysdate);
543 j binary_integer := 0;
544 BEGIN
545 hr_utility.set_location(l_proc || 'Entering',10);
546 hr_utility.set_location(l_proc || 'p_approval_cd is '||p_approval_cd,12);
547 FOR i in NVL(pqh_workflow.g_routing_criterion.FIRST,0)..NVL(pqh_workflow.g_routing_criterion.LAST,-1) loop
548 if pqh_workflow.g_routing_criterion.exists(i) then
549 l_rha_tab(j).attribute_id := pqh_workflow.g_routing_criterion(i).Attribute_id;
550 l_rha_tab(j).from_char := pqh_workflow.g_routing_criterion(i).from_char;
551 l_rha_tab(j).from_date := pqh_workflow.g_routing_criterion(i).from_date;
552 l_rha_tab(j).from_number := pqh_workflow.g_routing_criterion(i).from_num;
553 l_rha_tab(j).to_char := pqh_workflow.g_routing_criterion(i).to_char;
554 l_rha_tab(j).to_date := pqh_workflow.g_routing_criterion(i).to_date;
555 l_rha_tab(j).to_number := pqh_workflow.g_routing_criterion(i).to_num;
556 l_rha_tab(j).range_type_cd := pqh_workflow.g_routing_criterion(i).used_for;
557 l_rha_tab(j).value_date := pqh_workflow.g_routing_criterion(i).value_date;
558 l_rha_tab(j).value_number := pqh_workflow.g_routing_criterion(i).value_num;
559 l_rha_tab(j).value_char := pqh_workflow.g_routing_criterion(i).value_char;
560 j := j + 1;
561 end if;
562 END LOOP;
563 hr_utility.set_location(l_proc || 'out of xfer ',14);
564
565 pqh_routing_history_api.create_routing_history_bp (
566 p_validate => false
567 ,p_routing_history_id => l_routing_history_id
568 ,p_approval_cd => p_approval_cd
569 ,p_comments => p_comments
570 ,p_forwarded_by_assignment_id => p_forwarded_by_assignment_id
571 ,p_forwarded_by_member_id => p_forwarded_by_member_id
572 ,p_forwarded_by_position_id => p_forwarded_by_position_id
573 ,p_forwarded_by_user_id => p_forwarded_by_user_id
574 ,p_forwarded_by_role_id => p_forwarded_by_role_id
575 ,p_forwarded_to_assignment_id => p_forwarded_to_assignment_id
576 ,p_forwarded_to_member_id => p_forwarded_to_member_id
577 ,p_forwarded_to_position_id => p_forwarded_to_position_id
578 ,p_forwarded_to_role_id => p_forwarded_to_role_id
579 ,p_forwarded_to_user_id => p_forwarded_to_user_id
580 ,p_notification_date => p_notification_date
581 ,p_pos_structure_version_id => p_pos_structure_version_id
582 ,p_routing_category_id => p_routing_category_id
583 ,p_transaction_category_id => p_transaction_category_id
584 ,p_transaction_id => p_transaction_id
585 ,p_user_action_cd => p_user_action_cd
586 ,p_object_version_number => l_object_version_number
587 ,p_from_range_name => pqh_workflow.g_current_member_range
588 ,p_to_range_name => pqh_workflow.g_next_member_range
589 ,p_list_range_name => pqh_workflow.g_list_range
590 ,p_effective_date => l_effective_date
591 ,p_rha_tab => l_rha_tab
592 );
593 p_routing_history_id := l_routing_history_id;
594 hr_utility.set_location(l_proc || 'Exiting',100);
595 exception
596 when others then
597 p_routing_history_id := null;
598 hr_utility.set_location(sqlerrm,110 );
599 raise;
600 END;
601 --
602 -- Update routing history row
603 --
604 PROCEDURE update_routing_history(
605 p_routing_history_id IN NUMBER
606 , p_user_action_cd IN VARCHAR2
607 )
608 IS
609 l_proc VARCHAR2(61) := g_package || 'update_routing_history';
610 BEGIN
611 hr_utility.set_location(l_proc || 'Entering',10);
612 UPDATE pqh_routing_history
613 set
614 user_action_cd = p_user_action_cd
615 WHERE routing_history_id = p_routing_history_id;
616 hr_utility.set_location(l_proc || 'Exiting',100);
617 END;
618
619 --
620 -- Get Last routing history row
621 --
622
623 PROCEDURE get_last_rh_row (
624 p_transaction_category_id IN NUMBER
625 , p_transaction_id IN NUMBER
626 , p_routing_history_id OUT NOCOPY NUMBER
627 , p_user_name OUT NOCOPY VARCHAR2
628 )
629 IS
630 l_proc VARCHAR2(61) := g_package || 'get_last_rh_row';
631 -- l_user VARCHAR2(100);
632 CURSOR c_rht IS
633 SELECT '1-USR-TO' order_by
634 , rht.routing_history_id routing_history_id
635 , user_name user_name
636 FROM pqh_routing_history rht
637 , fnd_user usr
638 WHERE usr.user_id = rht.forwarded_by_user_id
639 AND transaction_category_id = p_transaction_category_id
640 AND transaction_id = p_transaction_id
641 AND user_action_cd <> 'TIMEOUT'
642 UNION
643 SELECT '2-POS'
644 , rht.routing_history_id
645 , wfr.name
646 FROM pqh_routing_history rht
647 , wf_roles wfr
648 WHERE wfr.orig_system = 'POS'
649 AND wfr.orig_system_id = rht.forwarded_by_position_id
650 AND rht.forwarded_to_user_id IS NULL
651 AND transaction_category_id = p_transaction_category_id
652 AND transaction_id = p_transaction_id
653 AND user_action_cd <> 'TIMEOUT'
654 UNION
655 SELECT '3-RLS'
656 , rht.routing_history_id
657 , wfr.name
658 FROM pqh_routing_history rht
659 , wf_roles wfr
660 , pqh_routing_list_members rlm
661 WHERE wfr.orig_system = 'PQH_ROLE'
662 AND rlm.routing_list_member_id = rht.forwarded_by_member_id
663 AND wfr.orig_system_id = rlm.role_id
664 AND rht.forwarded_to_user_id IS NULL
665 AND transaction_category_id = p_transaction_category_id
666 AND transaction_id = p_transaction_id
667 AND user_action_cd <> 'TIMEOUT'
668 UNION
669 SELECT '4-USR-BY'
670 , rht.routing_history_id
671 , user_name
672 FROM pqh_routing_history rht
673 , fnd_user usr
674 WHERE usr.user_id = rht.forwarded_by_user_id
675 AND rht.forwarded_to_user_id IS NULL
676 AND transaction_category_id = p_transaction_category_id
677 AND transaction_id = p_transaction_id
678 AND user_action_cd <> 'TIMEOUT'
679 ORDER BY 2 DESC, 1 ASC;
680 r_rht c_rht%ROWTYPE;
681 BEGIN
682 hr_utility.set_location(l_proc || 'Entering',10);
683 OPEN c_rht;
684 FETCH c_rht INTO r_rht;
685 CLOSE c_rht;
686 p_user_name := r_rht.user_name;
687 p_routing_history_id := r_rht.routing_history_id;
688 hr_utility.set_location(l_proc || ' order by : ' || r_rht.order_by, 90);
689 hr_utility.set_location(l_proc || ' Last User : ' || r_rht.user_name, 90);
690 hr_utility.set_location(l_proc || 'Exiting',100);
691 exception when others then
692 p_routing_history_id := null;
693 p_user_name := null;
694 raise;
695 END;
696
697 FUNCTION get_last_rh_id (
698 p_itemkey IN VARCHAR2
699 )
700 RETURN NUMBER
701 IS
702 l_user_name VARCHAR2(100);
703 l_routing_history_id NUMBER;
704 l_transaction_category_id NUMBER;
705 l_transaction_id NUMBER;
706 BEGIN
707 decode_itemkey(p_itemkey => p_itemkey
708 , p_transaction_category_id => l_transaction_category_id
709 , p_transaction_id => l_transaction_id);
710 get_last_rh_row (
711 p_transaction_category_id => l_transaction_category_id
712 , p_transaction_id => l_transaction_id
713 , p_routing_history_id => l_routing_history_id
714 , p_user_name => l_user_name
715 );
716 RETURN l_routing_history_id;
717 END;
718
719 FUNCTION get_last_user (
720 p_transaction_category_id IN NUMBER
721 , p_transaction_id IN NUMBER
722 )
723 RETURN VARCHAR2
724 IS
725 l_user_name VARCHAR2(100);
726 l_routing_history_id NUMBER;
727 BEGIN
728 get_last_rh_row (
729 p_transaction_category_id => p_transaction_category_id
730 , p_transaction_id => p_transaction_id
731 , p_routing_history_id => l_routing_history_id
732 , p_user_name => l_user_name
733 );
734 RETURN l_user_name;
735 END;
736 FUNCTION get_last_user (
737 p_itemkey IN VARCHAR2
738 )
739 RETURN VARCHAR2
740 IS
741 l_transaction_category_id NUMBER;
742 l_transaction_id NUMBER;
743 l_user VARCHAR2(100);
744 l_proc VARCHAR2(61) := g_package || 'get_last_user';
745 BEGIN
746 hr_utility.set_location(l_proc || 'Entering',10);
747 decode_itemkey(
748 p_itemkey => p_itemkey
749 , p_transaction_category_id => l_transaction_category_id
750 , p_transaction_id => l_transaction_id
751 );
752 l_user := get_last_user(
753 p_transaction_category_id => l_transaction_category_id
754 , p_transaction_id => l_transaction_id
755 );
756 hr_utility.set_location(l_proc || ' Exiting',100);
757 RETURN l_user;
758 END;
759 --
760 -- Get Approver's user name
761 --
762
763 FUNCTION get_approver (
764 p_transaction_category_id IN NUMBER
765 , p_transaction_id IN NUMBER
766 )
767 RETURN VARCHAR2
768 IS
769 l_proc VARCHAR2(61) := g_package || 'get_approver';
770 l_user VARCHAR2(100);
771 CURSOR c_rht IS
772 SELECT user_name
773 FROM pqh_routing_history rht
774 , fnd_user usr
775 WHERE usr.user_id = rht.forwarded_by_user_id
776 AND transaction_category_id = p_transaction_category_id
777 AND transaction_id = p_transaction_id
778 AND approval_cd = 'APPROVED'
779 ORDER BY routing_history_id DESC;
780 BEGIN
781 hr_utility.set_location(l_proc || ' Entering',10);
782 OPEN c_rht;
783 FETCH c_rht INTO l_user;
784 CLOSE c_rht;
785 hr_utility.set_location(l_proc || ' Approver : ' || l_user, 90);
786 hr_utility.set_location(l_proc || ' Exiting',100);
787 RETURN l_user;
788 END;
789
790 FUNCTION get_approver (
791 p_itemkey IN VARCHAR2
792 )
793 RETURN VARCHAR2
794 IS
795 l_transaction_category_id NUMBER;
796 l_transaction_id NUMBER;
797 l_user VARCHAR2(100);
798 l_proc VARCHAR2(61) := g_package || 'get_approver';
799 BEGIN
800 hr_utility.set_location(l_proc || ' Entering',10);
801 decode_itemkey(
802 p_itemkey => p_itemkey
803 , p_transaction_category_id => l_transaction_category_id
804 , p_transaction_id => l_transaction_id
805 );
806 l_user := get_approver(
807 p_transaction_category_id => l_transaction_category_id
808 , p_transaction_id => l_transaction_id
809 );
810 hr_utility.set_location(l_proc || ' Exiting',100);
811 RETURN l_user;
812 END;
813 --
814 -- Get Requestor's Routing History
815 --
816 PROCEDURE get_requestor_history (
817 p_transaction_category_id IN NUMBER
818 , p_transaction_id IN NUMBER
819 , p_user_name OUT NOCOPY VARCHAR2
820 , p_forwarded_by_assignment_id OUT NOCOPY NUMBER
821 , p_forwarded_by_member_id OUT NOCOPY NUMBER
822 , p_forwarded_by_position_id OUT NOCOPY NUMBER
823 , p_forwarded_by_user_id OUT NOCOPY NUMBER
824 , p_forwarded_by_role_id OUT NOCOPY NUMBER
825 )
826 IS
827 l_proc VARCHAR2(61) := g_package || 'get_requestor_history';
828 CURSOR c_rht IS
829 SELECT user_name
830 , forwarded_by_assignment_id
831 , forwarded_by_member_id
832 , forwarded_by_position_id
833 , forwarded_by_user_id
834 , forwarded_by_role_id
835 FROM pqh_routing_history rht
836 , fnd_user usr
837 WHERE usr.user_id = rht.forwarded_by_user_id
838 AND routing_history_id = (
839 SELECT MIN(routing_history_id)
840 FROM pqh_routing_history
841 WHERE transaction_category_id = p_transaction_category_id
842 AND transaction_id = p_transaction_id
843 );
844 BEGIN
845 hr_utility.set_location(l_proc || ' Entering',10);
846 OPEN c_rht;
847 FETCH c_rht INTO
848 p_user_name
849 , p_forwarded_by_assignment_id
850 , p_forwarded_by_member_id
851 , p_forwarded_by_position_id
852 , p_forwarded_by_user_id
853 , p_forwarded_by_role_id;
854 CLOSE c_rht;
855 hr_utility.set_location(l_proc || ' Exiting',100);
856 exception when others then
857 p_user_name := null;
858 p_forwarded_by_assignment_id := null;
859 p_forwarded_by_member_id := null;
860 p_forwarded_by_position_id := null;
861 p_forwarded_by_user_id := null;
862 p_forwarded_by_role_id := null;
863 raise;
864 END;
865
866 -- Get Requestor's user name
867 --
868 FUNCTION get_requestor (
869 p_transaction_category_id IN NUMBER
870 , p_transaction_id IN NUMBER
871 )
872 RETURN VARCHAR2
873 IS
874 l_proc VARCHAR2(61) := g_package || 'get_requestor';
875 l_user VARCHAR2(100);
876 l_forwarded_by_assignment_id NUMBER;
877 l_forwarded_by_member_id NUMBER;
878 l_forwarded_by_position_id NUMBER;
879 l_forwarded_by_user_id NUMBER;
880 l_forwarded_by_role_id NUMBER;
881
882 BEGIN
883 hr_utility.set_location(l_proc || ' Entering',10);
884 get_requestor_history(
885 p_transaction_category_id => p_transaction_category_id
886 , p_transaction_id => p_transaction_id
887 , p_user_name => l_user
888 , p_forwarded_by_assignment_id => l_forwarded_by_assignment_id
889 , p_forwarded_by_member_id => l_forwarded_by_member_id
890 , p_forwarded_by_position_id => l_forwarded_by_position_id
891 , p_forwarded_by_user_id => l_forwarded_by_user_id
892 , p_forwarded_by_role_id => l_forwarded_by_role_id
893 );
894
895 hr_utility.set_location(l_proc || ' Exiting',100);
896 hr_utility.set_location(l_proc || ' Requestor : ' || l_user, 90);
897 RETURN l_user;
898 END;
899
900 FUNCTION get_requestor (
901 p_itemkey IN VARCHAR2
902 )
903 RETURN VARCHAR2
904 IS
905 l_transaction_category_id NUMBER;
906 l_transaction_id NUMBER;
907 l_user VARCHAR2(100);
908 l_proc VARCHAR2(61) := g_package || 'get_requestor';
909 BEGIN
910 hr_utility.set_location(l_proc || ' Entering',10);
911 decode_itemkey(
912 p_itemkey => p_itemkey
913 , p_transaction_category_id => l_transaction_category_id
914 , p_transaction_id => l_transaction_id
915 );
916 l_user := get_requestor(
917 p_transaction_category_id => l_transaction_category_id
918 , p_transaction_id => l_transaction_id
919 );
920 hr_utility.set_location(l_proc || ' Exiting',100);
921 RETURN l_user;
922 END;
923 --
924 -- Get workflow information based on transaction category
925 --
926 PROCEDURE get_workflow_info( p_transaction_category_id IN NUMBER
927 , p_transaction_category_name OUT NOCOPY VARCHAR2
928 , p_workflow_name OUT NOCOPY VARCHAR2
929 , p_process_name OUT NOCOPY VARCHAR2
930 , p_timeout_days OUT NOCOPY NUMBER
931 , p_form_name OUT NOCOPY VARCHAR2
932 , p_post_txn_function OUT NOCOPY VARCHAR2
933 , p_post_style_cd OUT NOCOPY VARCHAR2
934 , p_future_action_cd OUT NOCOPY VARCHAR2
935 , p_short_name OUT NOCOPY VARCHAR2
936 )
937 IS
938 l_proc VARCHAR2(61) := g_package || 'get_workflow_info';
939 CURSOR c_txn_cat (p_transaction_category_id NUMBER) IS
940 SELECT name
941 , custom_workflow_name
942 , custom_wf_process_name
943 , timeout_days
944 , form_name
945 , post_txn_function
946 , post_style_cd
947 , future_action_cd
948 , short_name
949 FROM pqh_transaction_categories tct
950 WHERE transaction_category_id = p_transaction_category_id;
951 r_txn_cat c_txn_cat%ROWTYPE;
952 BEGIN
953 hr_utility.set_location(l_proc || ' Entering',10);
954 IF p_transaction_category_id IS NULL then
955 hr_utility.set_message(8302,'PQH_NULL_TRANSACTION_ID_OR_CAT');
956 hr_utility.raise_error;
957 END IF;
958 OPEN c_txn_cat(p_transaction_category_id => p_transaction_category_id);
959 FETCH c_txn_cat INTO r_txn_cat;
960 CLOSE c_txn_cat;
961 p_transaction_category_name := r_txn_cat.name;
962 p_workflow_name := NVL(r_txn_cat.custom_workflow_name, 'PQHGEN');
963 p_process_name := NVL(r_txn_cat.custom_wf_process_name, 'PQH_ROUTING');
964 p_timeout_days := NVL(r_txn_cat.timeout_days, 0);
965 p_form_name := r_txn_cat.form_name;
966 p_post_txn_function := r_txn_cat.post_txn_function;
967 p_post_style_cd := r_txn_cat.post_style_cd;
968 p_future_action_cd := r_txn_cat.future_action_cd;
969 p_short_name := r_txn_cat.short_name;
970 hr_utility.set_location(l_proc || 'Exiting',100);
971 exception when others then
972 p_transaction_category_name := null;
973 p_workflow_name := null;
974 p_process_name := null;
975 p_timeout_days := null;
976 p_form_name := null;
977 p_post_txn_function := null;
978 p_post_style_cd := null;
979 p_future_action_cd := null;
980 p_short_name := null;
981 raise;
982 END;
983 FUNCTION get_workflow_name(p_transaction_category_id in number)
984 return varchar2
985 is
986 l_transaction_category_name VARCHAR2(100);
987 l_process_name VARCHAR2(30);
988 l_timeout_days NUMBER;
989 l_form_name VARCHAR2(30);
990 l_post_txn_function VARCHAR2(61);
991 l_future_action_cd VARCHAR2(30);
992 l_post_style_cd VARCHAR2(30);
993 l_workflow_name VARCHAR2(30);
994 l_short_name VARCHAR2(30);
995 begin
996 get_workflow_info(p_transaction_category_id => p_transaction_category_id
997 , p_transaction_category_name => l_transaction_category_name
998 , p_workflow_name => l_workflow_name
999 , p_process_name => l_process_name
1000 , p_timeout_days => l_timeout_days
1001 , p_form_name => l_form_name
1002 , p_post_txn_function => l_post_txn_function
1003 , p_future_action_cd => l_future_action_cd
1004 , p_post_style_cd => l_post_style_cd
1005 , p_short_name => l_short_name
1006 );
1007 return l_workflow_name;
1008 end;
1009 --
1010 -- Set FYI User
1011 --
1012 PROCEDURE SET_FYI_USER (
1013 p_itemtype in varchar2
1014 , p_itemkey in varchar2
1015 , p_fyi_user in varchar2
1016 )
1017 IS
1018 l_proc VARCHAR2(61) := g_package || 'set_fyi_user';
1019 BEGIN
1020 hr_utility.set_location(l_proc || ' Entering',10);
1021 hr_utility.set_location(l_proc || ' p_fyi_user '|| p_fyi_user,20);
1022 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1023 itemkey => p_ItemKey,
1024 aname => 'FYI_USER',
1025 avalue => p_fyi_user );
1026 hr_utility.set_location(l_proc || ' Exiting',100);
1027 END;
1028 PROCEDURE SET_FYI_USER (
1029 p_transaction_category_id in number
1030 , p_transaction_id in number
1031 , p_fyi_user in varchar2
1032 )
1033 IS
1034 l_itemkey VARCHAR2(30);
1035 l_workflow_name VARCHAR2(30);
1036 l_transaction_category_name VARCHAR2(100);
1037 l_short_name VARCHAR2(30);
1038 l_process_name VARCHAR2(30);
1039 l_timeout_days NUMBER;
1040 l_form_name VARCHAR2(30);
1041 l_post_txn_function VARCHAR2(61);
1042 l_future_action_cd VARCHAR2(30);
1043 l_post_style_cd VARCHAR2(30);
1044 l_proc VARCHAR2(61) := g_package || 'set_fyi_user';
1045 BEGIN
1046 hr_utility.set_location(l_proc || ' Entering',10);
1047 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
1048
1049 get_workflow_info(p_transaction_category_id => p_transaction_category_id
1050 , p_transaction_category_name => l_transaction_category_name
1051 , p_workflow_name => l_workflow_name
1052 , p_process_name => l_process_name
1053 , p_timeout_days => l_timeout_days
1054 , p_form_name => l_form_name
1055 , p_post_txn_function => l_post_txn_function
1056 , p_future_action_cd => l_future_action_cd
1057 , p_post_style_cd => l_post_style_cd
1058 , p_short_name => l_short_name
1059 );
1060 set_fyi_user (
1061 p_itemtype => l_workflow_name
1062 , p_itemkey => l_itemkey
1063 , p_fyi_user => p_fyi_user
1064 );
1065 hr_utility.set_location(l_proc || ' Exiting',100);
1066 END;
1067 --
1068 -- Set next user and status
1069 --
1070 PROCEDURE SET_NEXT_USER (
1071 p_itemtype in varchar2
1072 , p_itemkey in varchar2
1073 , p_route_to_user in varchar2
1074 , p_status in varchar2 DEFAULT NULL
1075 )
1076 IS
1077 l_proc VARCHAR2(61) := g_package || 'set_next_user';
1078 l_status VARCHAR2(30) := p_status;
1079 BEGIN
1080 hr_utility.set_location(l_proc || ' Entering',10);
1081 hr_utility.set_location(l_proc || ' p_route_to_user '|| p_route_to_user,20);
1082 hr_utility.set_location(l_proc || 'p_status '|| p_status,30);
1083 IF NOT wf_process_not_running (p_itemkey => p_itemkey
1084 , p_itemtype => p_itemtype) THEN
1085 IF p_route_to_user IS NOT NULL THEN
1086 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1087 itemkey => p_ItemKey,
1088 aname => 'ROUTE_TO_USER',
1089 avalue => p_route_to_user );
1090 IF l_status IS NULL AND p_route_to_user IS NOT NULL THEN
1091 l_status := 'FOUND';
1092 END IF;
1093 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1094 itemkey => p_ItemKey,
1095 aname => 'NEXT_USER_STATUS',
1096 avalue => l_status );
1097 ELSE
1098 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1099 itemkey => p_ItemKey,
1100 aname => 'NEXT_USER_STATUS',
1101 avalue => 'NOT_FOUND' );
1102 END IF;
1103 END IF;
1104 hr_utility.set_location(l_proc || ' Exiting',100);
1105 END;
1106 PROCEDURE SET_NEXT_USER (
1107 p_transaction_category_id in number
1108 , p_transaction_id in number
1109 , p_route_to_user in varchar2
1110 , p_status in varchar2
1111 )
1112 IS
1113 l_itemkey VARCHAR2(30);
1114 l_workflow_name VARCHAR2(30);
1115 l_transaction_category_name VARCHAR2(100);
1116 l_process_name VARCHAR2(30);
1117 l_short_name VARCHAR2(30);
1118 l_timeout_days NUMBER;
1119 l_form_name VARCHAR2(30);
1120 l_post_txn_function VARCHAR2(61);
1121 l_future_action_cd VARCHAR2(30);
1122 l_post_style_cd VARCHAR2(30);
1123 l_proc VARCHAR2(61) := g_package || 'set_next_user';
1124 BEGIN
1125 hr_utility.set_location(l_proc || ' Entering',10);
1126 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
1127
1128 get_workflow_info(p_transaction_category_id => p_transaction_category_id
1129 , p_transaction_category_name => l_transaction_category_name
1130 , p_workflow_name => l_workflow_name
1131 , p_process_name => l_process_name
1132 , p_timeout_days => l_timeout_days
1133 , p_form_name => l_form_name
1134 , p_post_txn_function => l_post_txn_function
1135 , p_future_action_cd => l_future_action_cd
1136 , p_post_style_cd => l_post_style_cd
1137 , p_short_name => l_short_name
1138 );
1139 set_next_user (
1140 p_itemtype => l_workflow_name
1141 , p_itemkey => l_itemkey
1142 , p_route_to_user => p_route_to_user
1143 , p_status => p_status
1144 );
1145 hr_utility.set_location(l_proc || ' Exiting',100);
1146 END;
1147 --
1148 -- Dummy Post Transaction Function
1149 --
1150 FUNCTION post_any_txn (p_transaction_id IN NUMBER
1151 )
1152 RETURN VARCHAR2 IS
1153 BEGIN
1154 IF TRUNC(p_transaction_id/2) = p_transaction_id / 2 THEN
1155 RETURN 'SUCCESS';
1156 ELSE
1157 RETURN 'FAILURE';
1158 END IF;
1159 END;
1160 --
1161 -- Start a workflow process
1162 --
1163 PROCEDURE StartProcess(
1164 p_itemkey in varchar2
1165 , p_itemtype in varchar2
1166 , p_process_name in varchar2
1167 , p_route_to_user in varchar2
1168 , p_user_status in varchar2
1169 , p_timeout_days in number
1170 , p_form_name in VARCHAR2
1171 , p_transaction_id in NUMBER
1172 , p_transaction_category_id in NUMBER
1173 , p_post_txn_function IN VARCHAR2
1174 , p_future_action_cd IN VARCHAR2
1175 , p_post_style_cd IN VARCHAR2
1176 , p_user_action_cd IN VARCHAR2
1177 , p_effective_date IN DATE
1178 , p_transaction_name IN varchar2
1179 , p_transaction_category_name IN varchar2
1180 , p_routing_history_id IN number
1181 , p_comments IN VARCHAR2
1182 , p_launch_url in varchar2
1183 , p_parameter1_name in varchar2 default null
1184 , p_parameter1_value in varchar2 default null
1185 , p_parameter2_name in varchar2 default null
1186 , p_parameter2_value in varchar2 default null
1187 , p_parameter3_name in varchar2 default null
1188 , p_parameter3_value in varchar2 default null
1189 , p_parameter4_name in varchar2 default null
1190 , p_parameter4_value in varchar2 default null
1191 , p_parameter5_name in varchar2 default null
1192 , p_parameter5_value in varchar2 default null
1193 , p_parameter6_name in varchar2 default null
1194 , p_parameter6_value in varchar2 default null
1195 , p_parameter7_name in varchar2 default null
1196 , p_parameter7_value in varchar2 default null
1197 , p_parameter8_name in varchar2 default null
1198 , p_parameter8_value in varchar2 default null
1199 , p_parameter9_name in varchar2 default null
1200 , p_parameter9_value in varchar2 default null
1201 , p_parameter10_name in varchar2 default null
1202 , p_parameter10_value in varchar2 default null
1203 )
1204 is
1205 l_proc VARCHAR2(61) := g_package || 'StartProcess';
1206 l_form_name VARCHAR2(100) := p_form_name;
1207 l_timeout_days number ;
1208 Begin
1209 hr_utility.set_location(l_proc || ' Entering',10);
1210 hr_utility.set_location(l_proc || ' Params - p_itemkey '|| p_itemkey,15);
1211 hr_utility.set_location(l_proc || ' Params - p_itemtype '|| p_itemtype,15);
1212 hr_utility.set_location(l_proc || ' Params - p_process name '|| p_process_name,15);
1213 hr_utility.set_location(l_proc || ' Params - p_route_to_user '|| p_route_to_user,15);
1214 hr_utility.set_location(l_proc || ' Params - p_user_status '|| p_user_status,15);
1215 hr_utility.set_location(l_proc || ' Params - p_timeout_days '|| to_char(p_timeout_days),15);
1216 hr_utility.set_location(l_proc || ' Params - p_form_name '|| p_form_name,15);
1217 hr_utility.set_location(l_proc || ' Params - p_transaction_id '|| to_char(p_transaction_id) || ' '||l_proc,15);
1218 hr_utility.set_location(l_proc || ' Params - p_transaction_category_id '|| to_char(p_transaction_category_id),15);
1219 hr_utility.set_location(l_proc || ' Params - p_post_txn_function '|| p_post_txn_function,15);
1220 hr_utility.set_location(l_proc || ' Params - p_future_action_cd '|| p_future_action_cd,15);
1221 hr_utility.set_location(l_proc || ' Params - p_post_style_cd '|| p_post_style_cd,15);
1222 -- as timeout is in days as per our transaction category form, while
1223 -- workflow keeps the timeout in minutes making changes .
1224 l_timeout_days := nvl(p_timeout_days,0) * 24 * 60;
1225 wf_engine.createProcess( ItemType => p_itemtype,
1226 ItemKey => p_ItemKey,
1227 process => p_process_name );
1228 hr_utility.set_location(l_proc || ' Set timeout',15);
1229 wf_engine.SetItemAttrNumber(itemtype => p_itemtype
1230 , itemkey => p_itemkey
1231 , aname => 'TIMEOUT_DAYS'
1232 , avalue => l_timeout_days);
1233 -- wf_engine.SetItemAttrText( itemtype => p_itemtype
1234 -- , itemkey => p_itemkey
1235 -- , aname => 'TRANSACTION_STATUS'
1236 -- , avalue => 'PENDING');
1237 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
1238 , itemkey => p_itemkey
1239 , aname => 'TRANSACTION_ID'
1240 , avalue => p_transaction_id);
1241 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
1242 , itemkey => p_itemkey
1243 , aname => 'TRANSACTION_CATEGORY_ID'
1244 , avalue => p_transaction_category_id);
1245 wf_engine.SetItemAttrText( itemtype => p_itemtype
1246 , itemkey => p_itemkey
1247 , aname => 'TRANSACTION_NAME'
1248 , avalue => p_transaction_name);
1249 hr_utility.set_location('launch url is being set',50);
1250 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1251 itemkey => p_itemkey,
1252 aname => 'LAUNCH_URL',
1253 avalue => p_launch_url);
1254 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1255 itemkey => p_itemkey,
1256 aname => 'TRAN_CAT_NAME',
1257 avalue => p_transaction_category_name);
1258
1259 l_form_name := l_form_name || ':TRANSACTION_ID=' || to_char(p_transaction_id) || ' ROUTING_HISTORY_ID='||to_char(nvl(p_routing_history_id, 0));
1260
1261 hr_utility.set_location(l_proc || ' Set form name ' || l_form_name,15);
1262
1263 wf_engine.SetItemAttrText(itemtype => p_itemtype
1264 , itemkey => p_itemkey
1265 , aname => 'RHT_FORM_NAME'
1266 , avalue => 'PQHWSRHT:TRANSACTION_CATEGORY_ID='||to_char(p_transaction_category_id)
1267 || ' TRANSACTION_ID=' || to_char(p_transaction_id));
1268 wf_engine.SetItemAttrText(itemtype => p_itemtype
1269 , itemkey => p_itemkey
1270 , aname => 'PROCESS_LOG_FORM_NAME'
1271 , avalue => 'PQHWSPLG:TRANSACTION_CATEGORY_ID='||to_char(p_transaction_category_id)
1272 || ' TRANSACTION_ID=' || to_char(p_transaction_id));
1273 wf_engine.SetItemAttrText(itemtype => p_itemtype
1274 , itemkey => p_itemkey
1275 , aname => 'FORM_NAME'
1276 , avalue => l_form_name);
1277 wf_engine.SetItemAttrText(itemtype => p_itemtype
1278 , itemkey => p_itemkey
1279 , aname => 'POST_TXN_FUNCTION'
1280 , avalue => p_post_txn_function);
1281 wf_engine.SetItemAttrText(itemtype => p_itemtype
1282 , itemkey => p_itemkey
1283 , aname => 'POST_STYLE_CD'
1284 , avalue => p_post_style_cd);
1285 wf_engine.SetItemAttrText(itemtype => p_itemtype
1286 , itemkey => p_itemkey
1287 , aname => 'FUTURE_ACTION_CD'
1288 , avalue => p_future_action_cd);
1289 wf_engine.SetItemAttrText(itemtype => p_itemtype
1290 , itemkey => p_itemkey
1291 , aname => 'TRANSACTION_STATUS'
1292 , avalue => p_user_action_cd);
1293 wf_engine.SetItemAttrDate(itemtype => p_itemtype
1294 , itemkey => p_itemkey
1295 , aname => 'EFFECTIVE_DATE'
1296 , avalue => p_effective_date);
1297 wf_engine.SetItemAttrText(itemtype => p_itemtype
1298 , itemkey => p_ItemKey
1299 , aname => 'ROUTED_BY_USER'
1300 , avalue => nvl(fnd_profile.value('USERNAME'),p_route_to_user));
1301 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1302 itemkey => p_itemkey,
1303 aname => 'COMMENTS',
1304 avalue => p_comments);
1305 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1306 itemkey => p_itemkey,
1307 aname => 'PARAMETER1_NAME',
1308 avalue => p_parameter1_name);
1309 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1310 itemkey => p_itemkey,
1311 aname => 'PARAMETER1_VALUE',
1312 avalue => p_parameter1_value);
1313 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1314 itemkey => p_itemkey,
1315 aname => 'PARAMETER2_NAME',
1316 avalue => p_parameter2_name);
1317 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1318 itemkey => p_itemkey,
1319 aname => 'PARAMETER2_VALUE',
1320 avalue => p_parameter2_value);
1321 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1322 itemkey => p_itemkey,
1323 aname => 'PARAMETER3_NAME',
1324 avalue => p_parameter3_name);
1325 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1326 itemkey => p_itemkey,
1327 aname => 'PARAMETER3_VALUE',
1328 avalue => p_parameter3_value);
1329 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1330 itemkey => p_itemkey,
1331 aname => 'PARAMETER4_NAME',
1332 avalue => p_parameter4_name);
1333 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1334 itemkey => p_itemkey,
1335 aname => 'PARAMETER4_VALUE',
1336 avalue => p_parameter4_value);
1337 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1338 itemkey => p_itemkey,
1339 aname => 'PARAMETER5_NAME',
1340 avalue => p_parameter5_name);
1341 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1342 itemkey => p_itemkey,
1343 aname => 'PARAMETER5_VALUE',
1344 avalue => p_parameter5_value);
1345 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1346 itemkey => p_itemkey,
1347 aname => 'PARAMETER6_NAME',
1348 avalue => p_parameter6_name);
1349 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1350 itemkey => p_itemkey,
1351 aname => 'PARAMETER6_VALUE',
1352 avalue => p_parameter6_value);
1353 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1354 itemkey => p_itemkey,
1355 aname => 'PARAMETER7_NAME',
1356 avalue => p_parameter7_name);
1357 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1358 itemkey => p_itemkey,
1359 aname => 'PARAMETER7_VALUE',
1360 avalue => p_parameter7_value);
1361 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1362 itemkey => p_itemkey,
1363 aname => 'PARAMETER8_NAME',
1364 avalue => p_parameter8_name);
1365 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1366 itemkey => p_itemkey,
1367 aname => 'PARAMETER8_VALUE',
1368 avalue => p_parameter8_value);
1369 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1370 itemkey => p_itemkey,
1371 aname => 'PARAMETER9_NAME',
1372 avalue => p_parameter9_name);
1373 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1374 itemkey => p_itemkey,
1375 aname => 'PARAMETER9_VALUE',
1376 avalue => p_parameter9_value);
1377 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1378 itemkey => p_itemkey,
1379 aname => 'PARAMETER10_NAME',
1380 avalue => p_parameter10_name);
1381 wf_engine.SetItemAttrText( itemtype => p_itemtype,
1382 itemkey => p_itemkey,
1383 aname => 'PARAMETER10_VALUE',
1384 avalue => p_parameter10_value);
1385 hr_utility.set_location(l_proc || ' p_route_to_user' || p_route_to_user,15);
1386 hr_utility.set_location(l_proc || ' p_user_status ' || p_user_status,15);
1387 IF p_route_to_user IS NOT NULL THEN
1388 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1389 itemkey => p_ItemKey,
1390 aname => 'ROUTE_TO_USER',
1391 avalue => p_route_to_user );
1392 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1393 itemkey => p_ItemKey,
1394 aname => 'NEXT_USER_STATUS',
1395 avalue => p_user_status );
1396 ELSE
1397 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1398 itemkey => p_ItemKey,
1399 aname => 'NEXT_USER_STATUS',
1400 avalue => 'NOT_FOUND' );
1401 END IF;
1402 hr_utility.set_location(l_proc || ' Start Process',15);
1403 wf_engine.StartProcess ( ItemType => p_itemtype,
1404 ItemKey => p_ItemKey );
1405 hr_utility.set_location(l_proc || ' Exiting ',100);
1406 End;
1407 PROCEDURE get_apply_error(p_itemkey IN VARCHAR2,
1408 p_workflow_name IN VARCHAR2,
1409 p_apply_error_mesg OUT NOCOPY VARCHAR2,
1410 p_apply_error_num OUT NOCOPY VARCHAR2)
1411 IS
1412 l_proc varchar2(61) := g_package ||'get_error' ;
1413 BEGIN
1414 hr_utility.set_location(l_proc || ' Entering',10);
1415 p_apply_error_mesg := wf_engine.GetItemAttrText(
1416 itemtype => p_workflow_name,
1417 itemkey => p_itemkey,
1418 aname => 'APPLY_ERROR_MESG');
1419 hr_utility.set_location(l_proc || 'apply_msg'||substr(p_apply_error_mesg,1,26),20);
1420 hr_utility.set_location(l_proc || 'apply_msg'||substr(p_apply_error_mesg,27,26),21);
1421 p_apply_error_num := wf_engine.GetItemAttrText(
1422 itemtype => p_workflow_name,
1423 itemkey => p_itemkey,
1424 aname => 'APPLY_ERROR_NUM');
1425 hr_utility.set_location(l_proc || 'apply_code'||p_apply_error_num,30);
1426 hr_utility.set_location(l_proc || ' Exiting',100);
1427 END;
1428
1429 PROCEDURE set_apply_error(p_itemkey IN VARCHAR2,
1430 p_workflow_name IN VARCHAR2,
1431 p_apply_error_mesg IN VARCHAR2,
1432 p_apply_error_num IN VARCHAR2) IS
1433 l_proc varchar2(61) := g_package ||'set_error' ;
1434 BEGIN
1435 hr_utility.set_location(l_proc || ' Entering',10);
1436 hr_utility.set_location(l_proc || ' itemkey'||p_itemkey,20);
1437 hr_utility.set_location(l_proc || ' workflow_name'||p_workflow_name,30);
1438 hr_utility.set_location(l_proc || ' error_mesg'||substr(p_apply_error_mesg,1,26),31);
1439 hr_utility.set_location(l_proc || ' error_mesg'||substr(p_apply_error_mesg,27,26),32);
1440 hr_utility.set_location(l_proc || ' error_num'||p_apply_error_num,33);
1441 wf_engine.SetItemAttrText(
1442 itemtype => p_workflow_name,
1443 itemkey => p_itemkey,
1444 aname => 'APPLY_ERROR_MESG',
1445 avalue => p_apply_error_mesg);
1446 hr_utility.set_location(l_proc || ' error_mesg'||substr(p_apply_error_mesg,1,26),35);
1447 hr_utility.set_location(l_proc || ' error_mesg'||substr(p_apply_error_mesg,27,26),35);
1448 wf_engine.SetItemAttrText(
1449 itemtype => p_workflow_name,
1450 itemkey => p_itemkey,
1451 aname => 'APPLY_ERROR_NUM',
1452 avalue => p_apply_error_num);
1453 hr_utility.set_location(l_proc || ' error_num'||p_apply_error_num,38);
1454 hr_utility.set_location(l_proc || ' Exiting',100);
1455 END;
1456
1457 function get_notification_detail(p_itemkey in varchar2,
1458 p_mode in varchar2) return varchar2 is
1459 cursor c1 is
1460 SELECT A.NAME activity_name,
1461 wf_directory.getroledisplayname(IAS.ASSIGNED_USER) role_name ,IAS.ASSIGNED_USER owner
1462 from WF_ACTIVITIES_VL A, WF_PROCESS_ACTIVITIES PA,
1463 WF_ITEM_TYPES_VL IT, WF_ITEMS I, WF_ITEM_ACTIVITY_STATUSES IAS
1464 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1465 and IAS.ITEM_KEY = I.ITEM_KEY
1466 and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
1467 and I.ITEM_TYPE = IT.NAME
1468 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1469 and PA.ACTIVITY_NAME = A.NAME
1470 and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
1471 and I.item_type ='PQHGEN'
1472 and PA.activity_name like 'NTF_%'
1473 and IAS.activity_status ='NOTIFIED'
1474 and I.item_key = p_itemkey;
1475 l_activity varchar2(100);
1476 l_user_name varchar2(240);
1477 l_role_name varchar2(240);
1478 l_user_name_display varchar2(240);
1479 l_role_prefix varchar2(80);
1480 begin
1481 open c1;
1482 fetch c1 into l_activity,l_role_name,l_user_name;
1483 close c1;
1484 if p_mode = 'ACT' then
1485 return l_activity;
1486 else
1487 if l_role_name is not null then
1488 if instr(l_user_name,'POS:') >0 then
1489 -- Position is the owner
1490 l_role_prefix := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
1491 p_lookup_code => 'POSITION');
1492 elsif instr(l_user_name,'PQH_ROLE:') >0 then
1493 -- PQH role is the owner
1494 l_role_prefix := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
1495 p_lookup_code => 'ROLE');
1496 else
1497 -- user is the owner
1498 l_role_prefix := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
1499 p_lookup_code => 'USER');
1500 end if;
1501 l_user_name_display := l_role_prefix||':'||l_role_name;
1502 end if;
1503 return l_user_name_display;
1504 end if;
1505 end get_notification_detail;
1506
1507 function get_respond_activity(p_itemkey in varchar2) return varchar2 is
1508 l_activity varchar2(100);
1509 begin
1510 l_activity := get_notification_detail(p_itemkey => p_itemkey,
1511 p_mode => 'ACT');
1512 return l_activity;
1513 end get_respond_activity;
1514
1515 PROCEDURE process_user_action(
1516 p_transaction_category_id IN NUMBER
1517 , p_transaction_id IN NUMBER
1518 , p_workflow_seq_no IN NUMBER DEFAULT null
1519 , p_routing_category_id IN NUMBER DEFAULT null
1520 , p_member_cd IN VARCHAR2 DEFAULT NULL
1521 , p_user_action_cd IN VARCHAR2 DEFAULT 'FORWARD'
1522 , p_route_to_user IN VARCHAR2
1523 , p_user_status IN VARCHAR2 DEFAULT 'FOUND'
1524 , p_approval_cd IN VARCHAR2 DEFAULT NULL
1525 , p_pos_structure_version_id IN NUMBER DEFAULT null
1526 , p_comments IN VARCHAR2 DEFAULT NULL
1527 , p_forwarded_to_user_id IN NUMBER DEFAULT null
1528 , p_forwarded_to_role_id IN NUMBER DEFAULT null
1529 , p_forwarded_to_position_id IN NUMBER DEFAULT null
1530 , p_forwarded_to_assignment_id IN NUMBER DEFAULT null
1531 , p_forwarded_to_member_id IN NUMBER DEFAULT null
1532 , p_forwarded_by_user_id IN NUMBER DEFAULT null
1533 , p_forwarded_by_role_id IN NUMBER DEFAULT null
1534 , p_forwarded_by_position_id IN NUMBER DEFAULT null
1535 , p_forwarded_by_assignment_id IN NUMBER DEFAULT null
1536 , p_forwarded_by_member_id in NUMBER DEFAULT null
1537 , p_effective_date IN DATE DEFAULT NULL
1538 , p_parameter1_name IN VARCHAR2 DEFAULT NULL
1539 , p_parameter1_value IN VARCHAR2 DEFAULT NULL
1540 , p_parameter2_name IN VARCHAR2 DEFAULT NULL
1541 , p_parameter2_value IN VARCHAR2 DEFAULT NULL
1542 , p_parameter3_name IN VARCHAR2 DEFAULT NULL
1543 , p_parameter3_value IN VARCHAR2 DEFAULT NULL
1544 , p_parameter4_name IN VARCHAR2 DEFAULT NULL
1545 , p_parameter4_value IN VARCHAR2 DEFAULT NULL
1546 , p_parameter5_name IN VARCHAR2 DEFAULT NULL
1547 , p_parameter5_value IN VARCHAR2 DEFAULT NULL
1548 , p_parameter6_name IN VARCHAR2 DEFAULT NULL
1549 , p_parameter6_value IN VARCHAR2 DEFAULT NULL
1550 , p_parameter7_name IN VARCHAR2 DEFAULT NULL
1551 , p_parameter7_value IN VARCHAR2 DEFAULT NULL
1552 , p_parameter8_name IN VARCHAR2 DEFAULT NULL
1553 , p_parameter8_value IN VARCHAR2 DEFAULT NULL
1554 , p_parameter9_name IN VARCHAR2 DEFAULT NULL
1555 , p_parameter9_value IN VARCHAR2 DEFAULT NULL
1556 , p_parameter10_name IN VARCHAR2 DEFAULT NULL
1557 , p_parameter10_value IN VARCHAR2 DEFAULT NULL
1558 , p_transaction_name IN VARCHAR2 DEFAULT NULL
1559 , p_apply_error_mesg OUT NOCOPY VARCHAR2
1560 , p_apply_error_num OUT NOCOPY VARCHAR2
1561 )
1562 IS
1563 l_itemkey VARCHAR2(30);
1564 l_del_itemkey VARCHAR2(30);
1565 l_workflow_name VARCHAR2(30);
1566 l_transaction_category_name VARCHAR2(100);
1567 l_process_name VARCHAR2(30);
1568 l_proc VARCHAR2(61) := g_package || 'process_user_action';
1569 l_timeout_days NUMBER;
1570 l_form_name VARCHAR2(100);
1571 l_pos NUMBER;
1572 l_post_txn_function VARCHAR2(61);
1573 l_future_action_cd VARCHAR2(30);
1574 l_post_style_cd VARCHAR2(30);
1575 l_short_name VARCHAR2(30);
1576 l_route_to_user FND_USER.USER_NAME%TYPE;
1577 l_wf_not_running BOOLEAN;
1578 l_apply_error_mesg VARCHAR2(200) := 'No_Error';
1579 l_apply_error_num VARCHAR2(30) := '0' ;
1580 l_routing_history_id NUMBER;
1581 l_user_action_cd VARCHAR2(50) := p_user_action_cd;
1582 l_forwarded_to_assignment_id NUMBER;
1583 l_forwarded_to_member_id NUMBER;
1584 l_forwarded_to_position_id NUMBER;
1585 l_forwarded_to_user_id NUMBER;
1586 l_forwarded_to_role_id NUMBER;
1587 l_url varchar2(2000);
1588 l_requestor VARCHAR2(100);
1589 l_rejector VARCHAR2(100);
1590 l_activity VARCHAR2(100);
1591 l_effective_date DATE;
1592
1593 BEGIN
1594 --
1595 -- Print parameters for debugging
1596 --
1597 hr_utility.set_location(l_proc || ' Entering',10);
1598 hr_utility.set_location(l_proc || ' Parameter - p_transaction_category_id = ' || to_char(p_transaction_category_id), 15);
1599 hr_utility.set_location(l_proc || ' Parameter - p_transaction_id = ' || to_char(p_transaction_id), 20);
1600 hr_utility.set_location(l_proc || ' Parameter - p_routing_category_id = '|| to_char(p_routing_category_id), 25);
1601 hr_utility.set_location(l_proc || ' Parameter - p_user_action_cd = ' || p_user_action_cd, 30);
1602 hr_utility.set_location(l_proc || ' Parameter - p_route_to_user = ' || p_route_to_user, 35);
1603 hr_utility.set_location(l_proc || ' Parameter - to_role = ' || p_forwarded_to_role_id, 35);
1604 hr_utility.set_location(l_proc || ' Parameter - to_user = ' || p_forwarded_to_user_id, 35);
1605 hr_utility.set_location(l_proc || ' Parameter - to_position = ' || p_forwarded_to_position_id, 35);
1606 hr_utility.set_location(l_proc || ' Parameter - to_assignment = ' || p_forwarded_to_assignment_id, 35);
1607 hr_utility.set_location(l_proc || ' Parameter - p_user_status = ' || p_user_status, 40);
1608 hr_utility.set_location(l_proc || ' Parameter - p_pos_structure_version_id= ' || to_char(p_pos_structure_version_id), 45);
1609 hr_utility.set_location(l_proc || ' Parameter - p_comments = ' || substr(p_comments,1,20), 50);
1610 hr_utility.set_location(l_proc || ' Parameter - p_member_cd = ' || p_member_cd, 55);
1611 hr_utility.set_location(l_proc || ' Parameter - p_transaction_name = ' || substr(p_transaction_name,1,20), 57);
1612 --
1613 -- Return if Save and Continue selected
1614 --
1615 IF l_user_action_cd = 'SAVE' THEN
1616 RETURN;
1617 END IF;
1618
1619 l_forwarded_to_assignment_id := p_forwarded_to_assignment_id;
1620 l_forwarded_to_member_id := p_forwarded_to_member_id;
1621 l_forwarded_to_position_id := p_forwarded_to_position_id;
1622 l_forwarded_to_user_id := p_forwarded_to_user_id;
1623 l_forwarded_to_role_id := p_forwarded_to_role_id;
1624
1625 --
1626 -- Check transaction category and transaction id before continueing
1627 --
1628 IF p_transaction_category_id IS NULL
1629 OR p_transaction_id IS NULL THEN
1630 hr_utility.set_message(8302,'PQH_NULL_TRANSACTION_ID_OR_CAT');
1631 hr_utility.raise_error;
1632 END IF;
1633 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
1634 --
1635 -- Currently only for FYI Notifications.
1636 --
1637 IF l_user_action_cd in ('FYI_NOT','PQH_BPR')THEN
1638 if p_workflow_seq_no = 0 THEN
1639 hr_utility.set_message(8302,'PQH_FYI_NOT_THEN_SEQ_NO_ZERO');
1640 hr_utility.raise_error;
1641 END IF;
1642 else
1643 if p_workflow_seq_no <> 0 THEN
1644 hr_utility.set_message(8302,'PQH_FYI_NOT_THEN_SEQ_NO_ZERO');
1645 hr_utility.raise_error;
1646 END IF;
1647 END IF;
1648
1649 if nvl(p_workflow_seq_no, 0) <> 0 then
1650 l_itemkey := l_itemkey || '-' || to_char(p_workflow_seq_no);
1651 end if;
1652 -- ----------------------------------------------------------------
1653
1654 create_process_log('In Process User Action ' || l_itemkey || '-'
1655 || p_route_to_user || '-' || l_user_action_cd);
1656
1657 l_route_to_user := p_route_to_user;
1658
1659 get_workflow_info(p_transaction_category_id => p_transaction_category_id
1660 , p_transaction_category_name => l_transaction_category_name
1661 , p_workflow_name => l_workflow_name
1662 , p_process_name => l_process_name
1663 , p_timeout_days => l_timeout_days
1664 , p_form_name => l_form_name
1665 , p_post_txn_function => l_post_txn_function
1666 , p_future_action_cd => l_future_action_cd
1667 , p_post_style_cd => l_post_style_cd
1668 , p_short_name => l_short_name
1669 );
1670
1671 l_wf_not_running := wf_process_not_running (p_itemkey => l_itemkey
1672 , p_itemtype => l_workflow_name);
1673 if l_short_name ='PQH_BPR' then
1674 hr_utility.set_Location('build the url',25);
1675 l_url := pqh_bdgt_realloc_utility.url_builder(p_transaction_id => p_transaction_id);
1676 hr_utility.set_Location('url1 is'||substr(l_url,1,50),26);
1677 hr_utility.set_Location('url2 is'||substr(l_url,51,50),27);
1678 hr_utility.set_Location('url3 is'||substr(l_url,101,50),28);
1679 hr_utility.set_Location('url4 is'||substr(l_url,151,50),29);
1680 end if;
1681 IF l_user_action_cd = 'APPLY' THEN
1682 IF p_effective_date IS NULL THEN
1683 hr_utility.set_message(8302,'PQH_NULL_EFFECTIVE_DATE');
1684 hr_utility.raise_error;
1685 END IF;
1686 l_route_to_user := nvl(fnd_profile.value('USERNAME'),p_route_to_user);
1687 hr_utility.set_location('route_to user'||l_route_to_user, 281);
1688 ELSIF l_user_action_cd IN ('FORWARD') THEN
1689 IF p_member_cd = 'R' AND p_forwarded_to_member_id IS NULL THEN
1690 hr_utility.set_location(l_proc || ' PQH_NULL_MEMBER_ID', 30);
1691 hr_utility.set_message(8302,'PQH_NULL_MEMBER_ID');
1692 hr_utility.raise_error;
1693 ELSIF p_member_cd = 'S' AND p_forwarded_to_assignment_id IS NULL THEN
1694 hr_utility.set_location(l_proc || ' PQH_NULL_ASSIGNMENT_ID', 30);
1695 hr_utility.set_message(8302, 'PQH_NULL_ASSIGNMENT_ID');
1696 hr_utility.raise_error;
1697 ELSIF p_member_cd = 'P' AND (p_forwarded_to_position_id IS NULL OR
1698 p_pos_structure_version_id IS NULL) THEN
1699 hr_utility.set_location(l_proc || ' PQH_NULL_POS_OR_STRUCTURE_ID', 35);
1700 hr_utility.set_message(8302, 'PQH_NULL_POS_OR_STRUCTURE_ID');
1701 hr_utility.set_location(l_proc || ' PQH_NULL_POS_OR_STRUCTURE_ID', 38);
1702 hr_utility.raise_error;
1703 END IF;
1704 IF l_route_to_user IS NULL
1705 OR p_user_status IS NULL THEN
1706 hr_utility.set_message(8302,'PQH_NULL_DESTINATION');
1707 hr_utility.raise_error;
1708 END IF;
1709 ELSIF l_user_action_cd IN ( 'BACK', 'OVERRIDE') THEN
1710 IF p_member_cd = 'R' AND p_forwarded_to_role_id IS NULL THEN
1711 hr_utility.set_location(l_proc || ' PQH_NULL_ROLE_ID', 30);
1712 hr_utility.set_message(8302,'PQH_NULL_ROLE_ID');
1713 hr_utility.raise_error;
1714 ELSIF p_member_cd = 'S' AND p_forwarded_to_assignment_id IS NULL THEN
1715 hr_utility.set_location(l_proc || ' PQH_NULL_ASSIGNMENT_ID', 30);
1716 hr_utility.set_message(8302, 'PQH_NULL_ASSIGNMENT_ID');
1717 hr_utility.raise_error;
1718 ELSIF p_member_cd = 'P' AND (p_forwarded_to_position_id IS NULL OR
1719 p_pos_structure_version_id IS NULL) THEN
1720 hr_utility.set_location(l_proc || ' PQH_NULL_POS_OR_STRUCTURE_ID', 35);
1721 hr_utility.set_message(8302, 'PQH_NULL_POS_OR_STRUCTURE_ID');
1722 hr_utility.set_location(l_proc || ' PQH_NULL_POS_OR_STRUCTURE_ID', 38);
1723 hr_utility.raise_error;
1724 END IF;
1725 IF l_route_to_user IS NULL
1726 OR p_user_status IS NULL THEN
1727 hr_utility.set_message(8302,'PQH_NULL_DESTINATION');
1728 hr_utility.raise_error;
1729 END IF;
1730 ELSIF l_user_action_cd in ('INBOX','DBERROR') THEN
1731 l_route_to_user := nvl(p_route_to_user, fnd_global.user_name );
1732 hr_utility.set_location(l_proc || ' inbox or dberror'||l_route_to_user, 40);
1733 ELSIF l_user_action_cd = 'REJECT' THEN
1734 get_requestor_history(
1735 p_transaction_category_id => p_transaction_category_id
1736 , p_transaction_id => p_transaction_id
1737 , p_user_name => l_requestor
1738 , p_forwarded_by_assignment_id => l_forwarded_to_assignment_id
1739 , p_forwarded_by_member_id => l_forwarded_to_member_id
1740 , p_forwarded_by_position_id => l_forwarded_to_position_id
1741 , p_forwarded_by_user_id => l_forwarded_to_user_id
1742 , p_forwarded_by_role_id => l_forwarded_to_role_id
1743 );
1744 hr_utility.set_location(l_proc || 'requestor is '||l_requestor,401);
1745 l_rejector := nvl(fnd_profile.value('USERNAME'),p_route_to_user);
1746 hr_utility.set_location(l_proc || 'rejected by is '||l_rejector,402);
1747 IF l_requestor is null or l_rejector is null then
1748 hr_utility.set_location('requestor or rejector null ', 403);
1749 l_forwarded_to_assignment_id := p_forwarded_to_assignment_id;
1750 l_forwarded_to_member_id := p_forwarded_to_member_id;
1751 l_forwarded_to_position_id := p_forwarded_to_position_id;
1752 l_forwarded_to_user_id := p_forwarded_to_user_id;
1753 l_forwarded_to_role_id := p_forwarded_to_role_id;
1754 elsif l_requestor <> l_rejector then
1755 -- transaction is to be routed to initiator, but if initiator position is eliminated
1756 -- user should be asked for immediate rejection.
1757 hr_utility.set_location('requestor different than rejector ', 404);
1758 if l_forwarded_to_position_id is not null then
1759 hr_utility.set_location('is sent to pos', 405);
1760 l_effective_date := hr_general.get_position_date_end(p_position_id => l_forwarded_to_position_id);
1761 hr_utility.set_location('pos effective date is'||to_char(l_effective_date,'ddmmRRRR'), 406);
1762 if (l_effective_date is null or l_effective_date > trunc(sysdate)) then
1763 hr_utility.set_location(l_proc || 'initiator position valid', 41);
1764 l_route_to_user := l_requestor;
1765 l_user_action_cd := 'FRWRD_RJCT';
1766 else
1767 hr_utility.set_location(l_proc || 'initiator position eliminated', 42);
1768 hr_utility.set_location(l_proc || 'rejecting it right away ', 44);
1769 l_forwarded_to_assignment_id := p_forwarded_to_assignment_id;
1770 l_forwarded_to_member_id := p_forwarded_to_member_id;
1771 l_forwarded_to_position_id := p_forwarded_to_position_id;
1772 l_forwarded_to_user_id := p_forwarded_to_user_id;
1773 l_forwarded_to_role_id := p_forwarded_to_role_id;
1774 end if;
1775 else
1776 hr_utility.set_location('not being sent to pos', 404);
1777 l_route_to_user := l_requestor;
1778 l_user_action_cd := 'FRWRD_RJCT';
1779 end if;
1780 ELSE
1781 hr_utility.set_location('requestor same as rejector',405);
1782 l_forwarded_to_assignment_id := p_forwarded_to_assignment_id;
1783 l_forwarded_to_member_id := p_forwarded_to_member_id;
1784 l_forwarded_to_position_id := p_forwarded_to_position_id;
1785 l_forwarded_to_user_id := p_forwarded_to_user_id;
1786 l_forwarded_to_role_id := p_forwarded_to_role_id;
1787 END IF;
1788 hr_utility.set_location('action_cd is'||l_user_action_cd, 405);
1789 ELSIF l_user_action_cd = 'DELEGATE' THEN
1790 -- check whether the notification already exists or not, if it exists in that case
1791 -- workflow is not to be started for the delegated worksheet but transaction is to be routed
1792 -- to the user recorded in the transaction
1793 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
1794 if not l_wf_not_running then
1795 set_next_user (
1796 p_itemtype => l_workflow_name
1797 , p_itemkey => l_itemkey
1798 , p_route_to_user => l_route_to_user
1799 , p_status => p_user_status
1800 );
1801 wf_engine.SetItemAttrText(
1802 itemtype => l_workflow_name,
1803 itemkey => l_itemkey,
1804 aname => 'TRANSACTION_NAME',
1805 avalue => p_transaction_name);
1806 wf_engine.SetItemAttrText(
1807 itemtype => l_workflow_name,
1808 itemkey => l_itemkey,
1809 aname => 'TRAN_CAT_NAME',
1810 avalue => l_transaction_category_name);
1811 l_activity := get_respond_activity(l_itemkey);
1812 wf_engine.CompleteActivity(
1813 l_workflow_name
1814 , l_itemkey
1815 , l_activity
1816 , l_user_action_cd);
1817 hr_utility.set_location(l_proc || 'Completed Activity '||l_activity,70);
1818 end if;
1819 END IF;
1820 hr_utility.set_location(l_proc || ' l_user_action_cd '|| l_user_action_cd,75);
1821 IF l_user_action_cd NOT IN ('INBOX','DBERROR','FYI_NOT', 'DELEGATE','PQH_BPR') THEN
1822 create_routing_history(
1823 p_transaction_category_id => p_transaction_category_id
1824 , p_transaction_id => p_transaction_id
1825 , p_routing_category_id => p_routing_category_id
1826 , p_pos_structure_version_id => p_pos_structure_version_id
1827 , p_user_action_cd => l_user_action_cd
1828 , p_approval_cd => p_approval_cd
1829 , p_notification_date => sysdate
1830 , p_comments => p_comments
1831 , p_forwarded_to_user_id => l_forwarded_to_user_id
1832 , p_forwarded_to_role_id => l_forwarded_to_role_id
1833 , p_forwarded_to_position_id => l_forwarded_to_position_id
1834 , p_forwarded_to_assignment_id => l_forwarded_to_assignment_id
1835 , p_forwarded_to_member_id => l_forwarded_to_member_id
1836 , p_forwarded_by_user_id => p_forwarded_by_user_id
1837 , p_forwarded_by_role_id => p_forwarded_by_role_id
1838 , p_forwarded_by_position_id => p_forwarded_by_position_id
1839 , p_forwarded_by_assignment_id => p_forwarded_by_assignment_id
1840 , p_forwarded_by_member_id => p_forwarded_by_member_id
1841 , p_routing_history_id => l_routing_history_id
1842 );
1843 IF not l_wf_not_running THEN -- Create a new workflow process
1844 l_form_name := wf_engine.GetItemAttrText(itemtype => l_workflow_name
1845 , itemkey => l_itemkey
1846 , aname => 'FORM_NAME'
1847 );
1848 l_pos := instr(l_form_name, ' ROUTIN');
1849 IF l_pos > 0 THEN
1850 l_form_name := substr(l_form_name, 1, l_pos - 1);
1851 end if;
1852 l_form_name := l_form_name || ' ROUTING_HISTORY_ID=' || to_char(nvl(l_routing_history_id, 0));
1853
1854 hr_utility.set_location(l_proc || ' Set form name ' || l_form_name,15);
1855 wf_engine.SetItemAttrText(itemtype => l_workflow_name
1856 , itemkey => l_itemkey
1857 , aname => 'FORM_NAME'
1858 , avalue => l_form_name);
1859 END IF;
1860 END IF;
1861 IF l_wf_not_running THEN -- Create a new workflow process
1862 hr_utility.set_location(l_proc || ' Before Start Process called', 35);
1863 hr_utility.set_location(l_proc || ' in - p_route_to_user = ' || p_route_to_user, 36);
1864 hr_utility.set_location(l_proc || ' out - l_route_to_user = ' || l_route_to_user, 37);
1865 StartProcess(
1866 p_itemkey => l_itemkey
1867 , p_itemtype => l_workflow_name
1868 , p_process_name => l_process_name
1869 , p_route_to_user => l_route_to_user
1870 , p_user_status => p_user_status
1871 , p_timeout_days => l_timeout_days
1872 , p_form_name => l_form_name
1873 , p_transaction_id => p_transaction_id
1874 , p_transaction_category_id => p_transaction_category_id
1875 , p_post_txn_function => l_post_txn_function
1876 , p_future_action_cd => l_future_action_cd
1877 , p_post_style_cd => l_post_style_cd
1878 , p_user_action_cd => l_user_action_cd
1879 , p_effective_date => p_effective_date
1880 , p_transaction_name => p_transaction_name
1881 , p_transaction_category_name => l_transaction_category_name
1882 , p_routing_history_id => l_routing_history_id
1883 , p_comments => p_comments
1884 , p_launch_url => l_url
1885 , p_parameter1_name => p_parameter1_name
1886 , p_parameter1_value => p_parameter1_value
1887 , p_parameter2_name => p_parameter2_name
1888 , p_parameter2_value => p_parameter2_value
1889 , p_parameter3_name => p_parameter3_name
1890 , p_parameter3_value => p_parameter3_value
1891 , p_parameter4_name => p_parameter4_name
1892 , p_parameter4_value => p_parameter4_value
1893 , p_parameter5_name => p_parameter5_name
1894 , p_parameter5_value => p_parameter5_value
1895 , p_parameter6_name => p_parameter6_name
1896 , p_parameter6_value => p_parameter6_value
1897 , p_parameter7_name => p_parameter7_name
1898 , p_parameter7_value => p_parameter7_value
1899 , p_parameter8_name => p_parameter8_name
1900 , p_parameter8_value => p_parameter8_value
1901 , p_parameter9_name => p_parameter9_name
1902 , p_parameter9_value => p_parameter9_value
1903 , p_parameter10_name => p_parameter10_name
1904 , p_parameter10_value => p_parameter10_value
1905 );
1906 END IF;
1907 hr_utility.set_location(l_proc || ' l_route_to_user '|| l_route_to_user,50);
1908 hr_utility.set_location(l_proc || ' l_user_action_cd '|| l_user_action_cd,50);
1909
1910 wf_engine.SetItemAttrText( itemtype => l_workflow_name,
1911 itemkey => l_itemkey,
1912 aname => 'COMMENTS',
1913 avalue => p_comments);
1914 IF NOT l_wf_not_running THEN -- Move forward with the existing workflow process
1915 hr_utility.set_location(l_proc || ' Completing Activity '||l_activity,60);
1916 wf_engine.SetItemAttrText(
1917 itemtype => l_workflow_name,
1918 itemkey => l_itemkey,
1919 aname => 'TRAN_CAT_NAME',
1920 avalue => l_transaction_category_name);
1921 hr_utility.set_location(l_proc || ' l_route_to_user '|| l_route_to_user,50);
1922 wf_engine.SetItemAttrDate(
1923 itemtype => l_workflow_name,
1924 itemkey => l_itemkey,
1925 aname => 'EFFECTIVE_DATE',
1926 avalue => p_effective_date);
1927 wf_engine.SetItemAttrText(
1928 itemtype => l_workflow_name,
1929 itemkey => l_itemkey,
1930 aname => 'TRANSACTION_STATUS',
1931 avalue => l_user_action_cd);
1932 wf_engine.SetItemAttrText(
1933 itemtype => l_workflow_name,
1934 itemkey => l_itemkey,
1935 aname => 'TRANSACTION_NAME',
1936 avalue => p_transaction_name);
1937 --added by kgowripe for fixing 2897321
1938 hr_utility.set_location('resetting launch url ',51);
1939 wf_engine.SetItemAttrText( itemtype => l_workflow_name,
1940 itemkey => l_itemkey,
1941 aname => 'LAUNCH_URL',
1942 avalue => l_url);
1943 --changes end here kgowripe
1944 set_apply_error(p_itemkey => l_itemkey,
1945 p_workflow_name => l_workflow_name,
1946 p_apply_error_mesg => l_apply_error_mesg,
1947 p_apply_error_num => l_apply_error_num );
1948 hr_utility.set_location(l_proc || ' After setting attributes ',65);
1949 IF l_user_action_cd IN ('FORWARD', 'BACK', 'OVERRIDE', 'INBOX','DBERROR','DELEGATE', 'FRWRD_RJCT') THEN
1950 set_next_user (
1951 p_itemtype => l_workflow_name
1952 , p_itemkey => l_itemkey
1953 , p_route_to_user => l_route_to_user
1954 , p_status => p_user_status
1955 );
1956 END IF;
1957 hr_utility.set_location(l_proc || ' After setting user ',67);
1958 l_activity := get_respond_activity(l_itemkey);
1959 hr_utility.set_location(l_proc || ' activity is '||l_activity,68);
1960 hr_utility.set_location(l_proc || ' action_cd is '||l_user_action_cd,69);
1961 begin
1962 wf_engine.CompleteActivity(
1963 l_workflow_name
1964 , l_itemkey
1965 , l_activity
1966 , l_user_action_cd);
1967 exception
1968 when others then
1969 hr_utility.set_location(l_proc || 'Completed Activity fail'||l_activity,690);
1970 hr_utility.set_location(l_proc || ':'||substr(sqlerrm,1,30),691);
1971 hr_utility.set_location(l_proc || ':'||substr(sqlerrm,31,30),692);
1972 end;
1973 hr_utility.set_location(l_proc || 'Completed Activity '||l_activity,70);
1974 END IF;
1975 get_apply_error(p_itemkey => l_itemkey,
1976 p_workflow_name => l_workflow_name,
1977 p_apply_error_mesg => p_apply_error_mesg,
1978 p_apply_error_num => p_apply_error_num );
1979 hr_utility.set_location(l_proc || 'apply_code'||p_apply_error_num,80);
1980 hr_utility.set_location(l_proc || 'apply_mesg'||substr(p_apply_error_mesg,1,20),90);
1981
1982 hr_utility.set_location(l_proc || ' Exiting ',100);
1983 END;
1984
1985 PROCEDURE REROUTE_FUTURE_ACTION (
1986 p_transaction_category_id in NUMBER
1987 , p_transaction_id in NUMBER
1988 , p_route_to_user in VARCHAR2
1989 , p_user_status in VARCHAR2
1990 )
1991 IS
1992 l_itemkey VARCHAR2(30);
1993 l_workflow_name VARCHAR2(30);
1994 l_transaction_category_name VARCHAR2(100);
1995 l_process_name VARCHAR2(30);
1996 l_proc VARCHAR2(61) := g_package || 'reroute_future_action';
1997 l_timeout_days NUMBER;
1998 l_form_name VARCHAR2(30);
1999 l_short_name VARCHAR2(30);
2000 l_post_txn_function VARCHAR2(61);
2001 l_future_action_cd VARCHAR2(30);
2002 l_post_style_cd VARCHAR2(30);
2003 BEGIN
2004 hr_utility.set_location(l_proc || ' Entering',10);
2005 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
2006
2007 get_workflow_info(p_transaction_category_id => p_transaction_category_id
2008 , p_transaction_category_name => l_transaction_category_name
2009 , p_workflow_name => l_workflow_name
2010 , p_process_name => l_process_name
2011 , p_timeout_days => l_timeout_days
2012 , p_form_name => l_form_name
2013 , p_post_txn_function => l_post_txn_function
2014 , p_future_action_cd => l_future_action_cd
2015 , p_post_style_cd => l_post_style_cd
2016 , p_short_name => l_short_name
2017 );
2018 set_next_user (
2019 p_itemtype => l_workflow_name
2020 , p_itemkey => l_itemkey
2021 , p_route_to_user => p_route_to_user
2022 , p_status => p_user_status
2023 );
2024 wf_engine.CompleteActivity(
2025 l_workflow_name
2026 , l_itemkey
2027 , 'BLOCK'
2028 , 'REROUTE');
2029 hr_utility.set_location(l_proc || ' Exiting',100);
2030 END;
2031 --
2032 -- get last user's response from routing history
2033 --
2034 FUNCTION get_user_response (p_transaction_id IN NUMBER
2035 , p_transaction_category_id IN NUMBER)
2036 RETURN VARCHAR2
2037 IS
2038 CURSOR c_get_status IS
2039 SELECT user_action_cd
2040 FROM pqh_routing_history
2041 WHERE transaction_id = p_transaction_id
2042 AND transaction_category_id = p_transaction_category_id
2043 ORDER BY routing_history_id desc;
2044 r_get_status c_get_status%ROWTYPE;
2045 l_proc VARCHAR2(61) := g_package || 'get_user_response';
2046 BEGIN
2047 hr_utility.set_location(l_proc || ' Entering',10);
2048 OPEN c_get_status;
2049 FETCH c_get_status INTO r_get_status;
2050 CLOSE c_get_status;
2051 hr_utility.set_location(l_proc || ' Exiting',100);
2052 RETURN r_get_status.user_action_cd;
2053 END;
2054 --
2055 -- Mark notification as sent
2056 --
2057 PROCEDURE mark_fyi_sent(p_fyi_notified_id IN NUMBER
2058 , p_status IN VARCHAR2)
2059 IS
2060 l_proc VARCHAR2(61) := g_package || 'mark_fyi_sent';
2061 BEGIN
2062 hr_utility.set_location(l_proc || ' Entering',10);
2063 UPDATE pqh_fyi_notify
2064 SET status = p_status
2065 , notification_date = sysdate
2066 WHERE fyi_notified_id = p_fyi_notified_id;
2067 hr_utility.set_location(l_proc || ' Exiting',100);
2068 END;
2069 PROCEDURE CHECK_FYI (
2070 itemtype in varchar2,
2071 itemkey in varchar2,
2072 actid in number,
2073 funcmode in varchar2,
2074 result out nocopy varchar2 )
2075 is
2076 l_proc VARCHAR2(61) := g_package || 'check_fyi';
2077 CURSOR c_get_fyi (p_transaction_id NUMBER
2078 , p_transaction_category_id NUMBER
2079 ) IS
2080 SELECT fyi_notified_id
2081 , notified_name
2082 , notification_event_cd
2083 , notified_type_cd
2084 FROM pqh_fyi_notify
2085 WHERE transaction_id = p_transaction_id
2086 AND transaction_category_id = p_transaction_category_id
2087 AND STATUS IS NULL;
2088 -- r_get_fyi c_get_fyi%ROWTYPE;
2089 l_user_action_cd pqh_routing_history.user_action_cd%TYPE;
2090 l_transaction_id pqh_routing_history.transaction_id%TYPE;
2091 l_transaction_category_id pqh_routing_history.transaction_category_id%TYPE;
2092 l_user fnd_user.user_name%TYPE;
2093 l_transaction_status VARCHAR2(30);
2094 BEGIN
2095 hr_utility.set_location(l_proc || ' Entering',10);
2096 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2097 IF (FUNCMODE = 'RUN') THEN
2098 l_user_action_cd := wf_engine.GetItemAttrText(itemtype => itemtype,
2099 itemkey => ItemKey,
2100 aname => 'TRANSACTION_STATUS');
2101 hr_utility.set_location(l_proc || ' user action code ' || l_user_action_cd, 25);
2102 --ns:26-Jun-2006: Bug 5357676: Set routed_by_user for correct From to appear in fyi notification.
2103 wf_engine.SetItemAttrText(itemtype => itemtype
2104 , itemkey => itemKey
2105 , aname => 'ROUTED_BY_USER'
2106 , avalue => FND_GLOBAL.user_name );
2107 --ns: end fix for 5357676
2108 IF l_user_action_cd in ('FYI_NOT','PQH_BPR') THEN
2109 l_user := wf_engine.GetItemAttrText(itemtype => itemtype,
2110 itemkey => ItemKey,
2111 aname => 'ROUTE_TO_USER');
2112 hr_utility.set_location(l_proc || ' user ' || l_user, 28);
2113 SET_FYI_USER (
2114 p_itemtype => itemtype
2115 , p_itemkey => itemkey
2116 , p_fyi_user => l_user
2117 );
2118 result := 'COMPLETE:'||l_user_action_cd;
2119 RETURN;
2120 END IF;
2121 decode_itemkey(p_itemkey => itemkey
2122 , p_transaction_category_id => l_transaction_category_id
2123 , p_transaction_id => l_transaction_id);
2124 -- l_user_action_cd := get_user_response(p_transaction_category_id => l_transaction_category_id
2125 -- , p_transaction_id => l_transaction_id);
2126 hr_utility.set_location(l_proc || 'Item Key ' || itemkey, 29);
2127 FOR r_get_fyi IN c_get_fyi(p_transaction_category_id => l_transaction_category_id
2128 , p_transaction_id => l_transaction_id)
2129 LOOP
2130 IF r_get_fyi.notified_type_cd = 'APPROVER' THEN
2131 l_user := get_approver(p_transaction_category_id => l_transaction_category_id
2132 , p_transaction_id => l_transaction_id);
2133 ELSIF r_get_fyi.notified_type_cd = 'REQUESTOR' THEN
2134 l_user := get_requestor(p_transaction_category_id => l_transaction_category_id
2135 , p_transaction_id => l_transaction_id);
2136 ELSIF r_get_fyi.notified_type_cd = 'LAST_USER' THEN
2137 l_user := get_last_user(p_transaction_category_id => l_transaction_category_id
2138 , p_transaction_id => l_transaction_id);
2139 ELSE
2140 l_user := r_get_fyi.notified_name;
2141 END IF;
2142 hr_utility.set_location(l_proc || 'for -'||r_get_fyi.fyi_notified_id||' l_user : ' || l_user, 90);
2143 IF (r_get_fyi.notification_event_cd = 'APPROVAL' AND l_user_action_cd IN ('APPLY', 'OVERRIDE','FORWARD','BACK'))
2144 OR (r_get_fyi.notification_event_cd = 'REJECTION' AND l_user_action_cd = 'REJECT')
2145 OR (r_get_fyi.notification_event_cd = 'DBFAILURE' AND l_user_action_cd = 'DBFAILURE')
2146 OR (r_get_fyi.notification_event_cd = 'DBSUCCESS' AND l_user_action_cd = 'DBSUCCESS')
2147 OR (r_get_fyi.notification_event_cd = 'COMPLETION' AND l_user_action_cd IN ('DBSUCCESS', 'REJECT', 'ERROR'))
2148 OR (r_get_fyi.notification_event_cd = 'IMMEDIATE')
2149 THEN
2150 IF l_user IS NOT NULL THEN
2151 SET_FYI_USER (
2152 p_itemtype => itemtype
2153 , p_itemkey => itemkey
2154 , p_fyi_user => l_user
2155 );
2156 mark_fyi_sent(p_fyi_notified_id => r_get_fyi.fyi_notified_id
2157 , p_status => 'SENT');
2158 result := 'COMPLETE:SUCCESS';
2159 hr_utility.set_location(l_proc || ' Exiting',10);
2160 RETURN;
2161 END IF;
2162 ELSIF (r_get_fyi.notification_event_cd = 'OVERRIDE' AND l_user_action_cd IN ('APPLY', 'OVERRIDE','FORWARD','BACK'))
2163 THEN
2164 -- if approver was override approver then this should be invoked
2165 if check_approver(p_itemkey => itemkey) and l_user IS NOT NULL THEN
2166 SET_FYI_USER (
2167 p_itemtype => itemtype
2168 , p_itemkey => itemkey
2169 , p_fyi_user => l_user
2170 );
2171 mark_fyi_sent(p_fyi_notified_id => r_get_fyi.fyi_notified_id
2172 , p_status => 'SENT');
2173 result := 'COMPLETE:SUCCESS';
2174 hr_utility.set_location(l_proc || ' Exiting',10);
2175 RETURN;
2176 END IF;
2177 END IF;
2178 END LOOP;
2179 result := 'COMPLETE:FAILURE';
2180 hr_utility.set_location(l_proc || ' Exiting',10);
2181 RETURN;
2182 END IF;
2183 hr_utility.set_location(l_proc || ' Exiting',10);
2184 END;
2185
2186 -- This procedure was added so that workflow definition can be used by Budget reallocation
2187
2188 PROCEDURE WHICH_TXN_CAT (
2189 itemtype in varchar2,
2190 itemkey in varchar2,
2191 actid in number,
2192 funcmode in varchar2,
2193 result out nocopy varchar2 )
2194 is
2195 l_proc VARCHAR2(61) := g_package || 'which_txn_cat';
2196 l_transaction_category_id number;
2197 l_transaction_id number;
2198 l_short_name pqh_transaction_categories.short_name%type;
2199 BEGIN
2200 hr_utility.set_location(l_proc || ' Entering',10);
2201 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2202 IF (FUNCMODE = 'RUN') THEN
2203 create_process_log('which_txn_cat ' || itemkey);
2204 decode_itemkey(p_itemkey => itemkey
2205 ,p_transaction_category_id => l_transaction_category_id
2206 ,p_transaction_id => l_transaction_id);
2207 select short_name into l_short_name
2208 from pqh_transaction_categories
2209 where transaction_category_id = l_transaction_category_id;
2210 result := 'COMPLETE:' || l_short_name;
2211 hr_utility.set_location(l_proc || ' short_name '||l_short_name,30);
2212 hr_utility.set_location(l_proc || ' Exiting ',100);
2213 return;
2214 ELSE
2215 hr_utility.set_location(l_proc || ' Exiting',100);
2216 END IF;
2217 exception when others then
2218 result := null;
2219 raise;
2220 END;
2221
2222 PROCEDURE FIND_NOTICE_TYPE (
2223 itemtype in varchar2,
2224 itemkey in varchar2,
2225 actid in number,
2226 funcmode in varchar2,
2227 result out nocopy varchar2 )
2228 is
2229 l_status varchar2(30);
2230 l_user varchar2(30);
2231 l_user_action_cd varchar2(30);
2232 l_proc VARCHAR2(61) := g_package || 'find_notice_type';
2233 BEGIN
2234 hr_utility.set_location(l_proc || ' Entering',10);
2235 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2236 IF (FUNCMODE = 'RUN') THEN
2237 create_process_log('Find Notice_type ' || itemkey);
2238 l_user_action_cd := wf_engine.GetItemAttrText(itemtype => itemtype,
2239 itemkey => ItemKey,
2240 aname => 'TRANSACTION_STATUS');
2241 if l_user_action_cd = 'OVERRIDE' then
2242 l_status := 'OVERRIDE';
2243 elsif l_user_action_cd ='BACK' then
2244 l_status := 'SEND_BACK';
2245 elsif l_user_action_cd ='FRWRD_RJCT' then
2246 l_status := 'REJECT';
2247 elsif l_user_action_cd ='WARNING' then
2248 l_status := 'WARNING';
2249 elsif l_user_action_cd ='DBERROR' then
2250 l_status := 'ERROR';
2251 else
2252 hr_utility.set_location(l_proc || 'user_action_cd'||l_user_action_cd,30);
2253 l_status := 'APPROVE';
2254 end if;
2255 result := 'COMPLETE:' || l_status;
2256 hr_utility.set_location(l_proc || ' l_status '||l_status,30);
2257 hr_utility.set_location(l_proc || ' Exiting ',100);
2258 return;
2259 ELSE
2260 hr_utility.set_location(l_proc || ' Exiting',100);
2261 END IF;
2262 exception when others then
2263 result := null;
2264 raise;
2265 END;
2266 --
2267 -- Procedure to be called from workflow
2268 --
2269
2270 PROCEDURE FIND_NEXT_USER (
2271 itemtype in varchar2,
2272 itemkey in varchar2,
2273 actid in number,
2274 funcmode in varchar2,
2275 result out nocopy varchar2 )
2276 is
2277 l_status varchar2(30);
2278 l_user varchar2(30);
2279 l_proc VARCHAR2(61) := g_package || 'find_next_user';
2280 BEGIN
2281 hr_utility.set_location(l_proc || ' Entering',10);
2282 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2283 IF (FUNCMODE = 'RUN') THEN
2284 create_process_log('Find Next User ' || itemkey);
2285 l_status := wf_engine.GetItemAttrText(itemtype => itemtype,
2286 itemkey => ItemKey,
2287 aname => 'NEXT_USER_STATUS');
2288 l_user := get_last_user(p_itemkey => itemkey);
2289 IF l_user IS NOT NULL THEN
2290 wf_engine.SetItemAttrText(itemtype => itemtype
2291 , itemkey => ItemKey
2292 , aname => 'ROUTED_BY_USER'
2293 , avalue => l_user);
2294 END IF;
2295 l_status := NVL(l_status, 'ERROR');
2296 result := 'COMPLETE:' || l_status;
2297 hr_utility.set_location(l_proc || ' l_user ' ||l_user,20);
2298 hr_utility.set_location(l_proc || ' l_status '||l_status,30);
2299 hr_utility.set_location(l_proc || ' Exiting ',100);
2300 return;
2301 ELSE
2302 hr_utility.set_location(l_proc || ' Exiting',100);
2303 END IF;
2304 exception when others then
2305 result := null;
2306 raise;
2307 END;
2308 PROCEDURE notify_requestor (
2309 itemtype in varchar2,
2310 itemkey in varchar2,
2311 actid in number,
2312 funcmode in varchar2,
2313 result out nocopy varchar2 )
2314 is
2315 l_user VARCHAR2(30);
2316 l_transaction_id NUMBER;
2317 l_transaction_category_id NUMBER;
2318 l_proc VARCHAR2(61) := g_package || 'notify_requestor';
2319 BEGIN
2320 hr_utility.set_location(l_proc || ' Entering',10);
2321 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2322 IF (FUNCMODE = 'RUN') THEN
2323 l_user := get_requestor(itemkey);
2324
2325 set_next_user (
2326 p_itemtype => itemtype
2327 , p_itemkey => itemkey
2328 , p_route_to_user => l_user
2329 , p_status => 'FOUND'
2330 );
2331 result := 'COMPLETE:FOUND';
2332 hr_utility.set_location(l_proc || ' Exiting',100);
2333 return;
2334 ELSE
2335 hr_utility.set_location(l_proc || ' Exiting',100);
2336 END IF;
2337 exception when others then
2338 result := null;
2339 raise;
2340 END;
2341
2342 PROCEDURE APPROVE_TXN (
2343 itemtype in varchar2
2344 , itemkey in varchar2
2345 , actid in number
2346 , funcmode in varchar2
2347 , result out nocopy varchar2
2348 )
2349 IS
2350 l_effective_date DATE;
2351 l_proc VARCHAR2(61) := g_package || 'approve_txn';
2352 l_future_action_cd pqh_transaction_categories.future_action_cd%TYPE;
2353 l_post_style_cd pqh_transaction_categories.post_style_cd%TYPE;
2354 BEGIN
2355 hr_utility.set_location(l_proc || ' Entering',10);
2356 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2357 IF (FUNCMODE = 'RUN') THEN
2358 l_future_action_cd := wf_engine.GetItemAttrText(
2359 itemtype => itemtype,
2360 itemkey => ItemKey,
2361 aname => 'FUTURE_ACTION_CD');
2362 l_post_style_cd := wf_engine.GetItemAttrText(
2363 itemtype => itemtype,
2364 itemkey => ItemKey,
2365 aname => 'POST_STYLE_CD');
2366 l_effective_date := wf_engine.GetItemAttrDate(
2367 itemtype => itemtype,
2368 itemkey => ItemKey,
2369 aname => 'EFFECTIVE_DATE');
2370 create_process_log('APPROVE_TXN : l_effective_date = ' || TO_CHAR(l_effective_date) || ' - ');
2371 IF l_effective_date > TRUNC(SYSDATE) THEN -- Future Actions
2372 IF NVL(l_future_action_cd, 'D') = 'D' THEN
2373 result := 'COMPLETE:FUTURE';
2374 ELSE
2375 result := 'COMPLETE:CURRENT';
2376 END IF;
2377 ELSIF l_effective_date < TRUNC(SYSDATE) THEN
2378 result := 'COMPLETE:RETROACTIVE';
2379 ELSE
2380 result := 'COMPLETE:CURRENT';
2381 END IF;
2382 END IF;
2383 hr_utility.set_location(l_proc || ' Exiting',100);
2384 exception when others then
2385 result := null;
2386 raise;
2387 END;
2388 PROCEDURE POST_TXN (
2389 itemtype in varchar2
2390 , itemkey in varchar2
2391 , actid in number
2392 , funcmode in varchar2
2393 , result out nocopy varchar2
2394 )
2395 IS
2396 l_proc VARCHAR2(61) := g_package || 'post_txn';
2397 l_dbupdate VARCHAR2(30) := 'FAILURE';
2398 l_post_txn_function VARCHAR2(2000);
2399 l_transaction_category_id NUMBER;
2400 l_transaction_id NUMBER;
2401 post_txn_not_defined EXCEPTION;
2402 pragma exception_init (post_txn_not_defined, -6550);
2403 l_apply_error_mesg VARCHAR2(200) := 'No Error';
2404 l_apply_error_num VARCHAR2(30) := '0' ;
2405 BEGIN
2406 hr_utility.set_location(l_proc || ' Entering',10);
2407 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2408 IF (FUNCMODE = 'RUN') THEN
2409 set_apply_error(p_itemkey => itemkey,
2410 p_workflow_name => itemtype,
2411 p_apply_error_mesg => l_apply_error_mesg,
2412 p_apply_error_num => l_apply_error_num );
2413 hr_utility.set_location(l_proc || ' After error setting ', 33);
2414 create_process_log('POST_TXN : itemkey = ' || itemkey);
2415 l_post_txn_function := wf_engine.GetItemAttrText(
2416 itemtype => itemtype,
2417 itemkey => ItemKey,
2418 aname => 'POST_TXN_FUNCTION');
2419 decode_itemkey(p_itemkey => itemkey
2420 , p_transaction_category_id => l_transaction_category_id
2421 , p_transaction_id => l_transaction_id
2422 );
2423 -- l_status is a variable which will be replaced by dbupdate and there is no need to define
2424 -- this variable
2425
2426 l_post_txn_function := 'begin :l_status := ' || l_post_txn_function ||
2427 '.apply_transaction(p_transaction_id =>'||
2428 to_char(l_transaction_id) ||
2429 ',p_validate_only =>''NO'' ); end;';
2430 hr_utility.set_location(l_proc ||substr(l_post_txn_function,1,40) , 22);
2431 hr_utility.set_location(l_proc ||substr(l_post_txn_function,41,40) , 22);
2432 hr_utility.set_location(l_proc ||substr(l_post_txn_function,81) , 22);
2433 savepoint before_apply_txn ;
2434 DECLARE
2435 l_sqlerrm VARCHAR2(2000);
2436 l_sqlcode NUMBER;
2437 BEGIN
2438 EXECUTE IMMEDIATE l_post_txn_function USING OUT l_dbupdate;
2439 EXCEPTION
2440 when post_txn_not_defined then
2441 hr_utility.set_location(l_proc || 'post_func not defined ' , 28);
2442 raise;
2443 WHEN OTHERS THEN
2444 l_sqlcode := sqlcode;
2445 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
2446 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
2447 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 30);
2448 rollback to before_apply_txn ;
2449 set_apply_error(p_itemkey => itemkey,
2450 p_workflow_name => itemtype,
2451 p_apply_error_mesg => l_sqlerrm,
2452 p_apply_error_num => l_sqlcode );
2453 hr_utility.set_location(l_proc || ' After error setting ', 33);
2454 END;
2455 IF l_dbupdate = 'SUCCESS' THEN
2456 wf_engine.SetItemAttrText(
2457 itemtype => itemtype,
2458 itemkey => itemkey,
2459 aname => 'TRANSACTION_STATUS',
2460 avalue => 'DBSUCCESS');
2461 result := 'COMPLETE:SUCCESS';
2462 ELSIF l_dbupdate = 'FAILURE' THEN
2463 wf_engine.SetItemAttrText(
2464 itemtype => itemtype,
2465 itemkey => itemkey,
2466 aname => 'TRANSACTION_STATUS',
2467 avalue => 'DBERROR');
2468 result := 'COMPLETE:ERROR';
2469 ELSE
2470 wf_engine.SetItemAttrText(
2471 itemtype => itemtype,
2472 itemkey => itemkey,
2473 aname => 'TRANSACTION_STATUS',
2474 avalue => 'DBWARNING');
2475 result := 'COMPLETE:WARNING';
2476 END IF;
2477 END IF;
2478 hr_utility.set_location(l_proc || ' Exiting',100);
2479 exception when others then
2480 result := null;
2481 raise;
2482 END;
2483
2484 PROCEDURE CHK_EFFECTIVE_DATE (
2485 itemtype in varchar2
2486 , itemkey in varchar2
2487 , actid in number
2488 , funcmode in varchar2
2489 , result out nocopy varchar2
2490 )
2491 IS
2492 l_proc VARCHAR2(61) := g_package || 'chk_effective_date';
2493 BEGIN
2494 hr_utility.set_location(l_proc || ' Entering',10);
2495 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2496 IF (FUNCMODE = 'RUN') THEN
2497 create_process_log('CHK_EFFECTIVE_DATE : itemkey = ' || itemkey);
2498 result := 'COMPLETE:FUTURE';
2499 END IF;
2500 hr_utility.set_location(l_proc || ' Exiting',100);
2501 exception when others then
2502 result := null;
2503 raise;
2504 END;
2505 PROCEDURE PROCESS_NOTIFICATION (
2506 itemtype in varchar2,
2507 itemkey in varchar2,
2508 actid in number,
2509 funcmode in varchar2,
2510 result out nocopy varchar2 )
2511 is
2512 l_proc VARCHAR2(61) := g_package || 'process_notification';
2513 l_form_name VARCHAR2(100);
2514 l_routing_history_id NUMBER;
2515 BEGIN
2516 hr_utility.set_location(l_proc || ' Entering',10);
2517 hr_utility.set_location(l_proc || 'Parameter - Funcmode = ' || funcmode,20);
2518 create_process_log('In ' || l_proc || ' ' || funcmode);
2519 l_form_name := wf_engine.GetItemAttrText(itemtype => itemtype,
2520 itemkey => ItemKey,
2521 aname => 'FORM_NAME');
2522 create_process_log('In ' || l_proc || ' Form Name ' || l_form_name);
2523 IF funcmode IN ('FORWARD', 'TRANSFER') THEN
2524 result := 'ERROR:USE_RESPOND_BUTTON';
2525 ELSIF funcmode = 'TIMEOUT' THEN
2526 l_routing_history_id := get_last_rh_id(p_itemkey => itemkey);
2527 IF l_routing_history_id IS NOT NULL THEN
2528 update_routing_history(p_routing_history_id => l_routing_history_id
2529 , p_user_action_cd => 'TIMEOUT');
2530 END IF;
2531 wf_engine.SetItemAttrText(itemtype => itemtype
2532 , itemkey => itemkey
2533 , aname => 'TRANSACTION_STATUS'
2534 , avalue => 'TIMEOUT');
2535 /* Code added for bug 7193557 */
2536 wf_engine.SetItemAttrText(itemtype => itemtype
2537 , itemkey => itemKey
2538 , aname => 'ROUTED_BY_USER'
2539 , avalue => FND_GLOBAL.user_name );
2540 /* Code added for bug 7193557 */
2541 result := 'COMPLETE:TIMEOUT';
2542 END IF;
2543 hr_utility.set_location(l_proc || ' Exiting',100);
2544 return;
2545 exception when others then
2546 result := null;
2547 raise;
2548 END;
2549 --
2550 -- Process response
2551 --
2552 PROCEDURE PROCESS_RESPONSE (
2553 itemtype in varchar2,
2554 itemkey in varchar2,
2555 actid in number,
2556 funcmode in varchar2,
2557 result out nocopy varchar2 )
2558 is
2559 l_proc VARCHAR2(61) := g_package || 'process_response';
2560 l_transaction_category_id NUMBER;
2561 l_transaction_id NUMBER;
2562 l_response VARCHAR2(30);
2563 l_user fnd_user.user_name%TYPE;
2564 l_user_action_cd VARCHAR2(30);
2565 l_requestor VARCHAR2(30);
2566 l_current_user VARCHAR2(30);
2567 BEGIN
2568 hr_utility.set_location(l_proc || ' Entering',10);
2569 hr_utility.set_location(l_proc || ' FuncMode' || funcmode, 20);
2570 IF (FUNCMODE = 'RUN') THEN
2571 l_user_action_cd := wf_engine.GetItemAttrText(itemtype => itemtype,
2572 itemkey => ItemKey,
2573 aname => 'TRANSACTION_STATUS');
2574 hr_utility.set_location(l_proc || ' User Action '|| l_user_action_cd, 20);
2575 IF l_user_action_cd in ('FYI_NOT','PQH_BPR') THEN
2576 result := 'COMPLETE:'||l_user_action_cd;
2577 hr_utility.set_location(l_proc || ' Exiting',100);
2578 RETURN;
2579 END IF;
2580 decode_itemkey(p_transaction_category_id => l_transaction_category_id
2581 , p_transaction_id => l_transaction_id
2582 , p_itemkey => itemkey);
2583 IF l_user_action_cd = 'DBFAILURE' THEN
2584 l_user_action_cd := 'FORWARD';
2585 END IF;
2586 if l_user_action_cd = 'TIMEOUT' then
2587 l_user := get_requestor(p_itemkey => itemkey);
2588 if l_user is not null then
2589 set_next_user (
2590 p_itemtype => itemtype
2591 , p_itemkey => itemkey
2592 , p_route_to_user => l_user
2593 , p_status => 'FOUND'
2594 );
2595 end if;
2596 elsif l_user_action_cd NOT IN ('REJECT','FRC_RJCT', 'APPLY','TIMEOUT') THEN
2597 l_user := wf_engine.GetItemAttrText(itemtype => itemtype,
2598 itemkey => ItemKey,
2599 aname => 'ROUTE_TO_USER');
2600 set_next_user (
2601 p_itemtype => itemtype
2602 , p_itemkey => itemkey
2603 , p_route_to_user => l_user
2604 , p_status => 'FOUND'
2605 );
2606 END IF;
2607 result := 'COMPLETE:' || l_user_action_cd;
2608 hr_utility.set_location(l_proc || ' Exiting',100);
2609 END IF;
2610 exception when others then
2611 result := null;
2612 raise;
2613 END;
2614 PROCEDURE CHK_FYI_RESULTS (
2615 itemtype in varchar2
2616 , itemkey in varchar2
2617 , actid in number
2618 , funcmode in varchar2
2619 , result out nocopy varchar2
2620 )
2621 IS
2622 l_proc VARCHAR2(61) := g_package || 'chk_fyi_results';
2623 l_user_action_cd VARCHAR2(30);
2624 BEGIN
2625 hr_utility.set_location(l_proc || ' Entering',10);
2626 hr_utility.set_location(l_proc || ' FuncMode ' || funcmode, 20);
2627 IF (FUNCMODE = 'RUN') THEN
2628 l_user_action_cd := wf_engine.GetItemAttrText(itemtype => itemtype,
2629 itemkey => ItemKey,
2630 aname => 'TRANSACTION_STATUS');
2631 result := 'COMPLETE:'||l_user_action_cd;
2632 END IF;
2633 hr_utility.set_location(l_proc || result, 50);
2634 hr_utility.set_location(l_proc || ' Exiting',100);
2635 exception when others then
2636 result := null;
2637 raise;
2638 END;
2639
2640 -- This function was added to cater to the requirement of worksheet that a delegated worksheet
2641 -- which is already approved can be started again
2642 -- in other cases this function won't be there and is trapped in the exception handler.
2643 PROCEDURE chk_root_node (itemtype in varchar2,
2644 itemkey in varchar2,
2645 actid in number,
2646 funcmode in varchar2,
2647 result out nocopy varchar2) is
2648 l_transaction_category_id number;
2649 l_transaction_id number;
2650 l_post_txn_function varchar2(4000);
2651 chk_root_not_defined EXCEPTION;
2652 pragma exception_init (chk_root_not_defined, -6550);
2653 l_proc VARCHAR2(61) := g_package || 'chk_root_node';
2654 chk_root_not_defined1 EXCEPTION;
2655 pragma exception_init (chk_root_not_defined1, -900);
2656 BEGIN
2657 hr_utility.set_location(l_proc || ' Entering',10);
2658 if (funcmode='RUN') then
2659 l_post_txn_function := wf_engine.GetItemAttrText(
2660 itemtype => itemtype,
2661 itemkey => ItemKey,
2662 aname => 'POST_TXN_FUNCTION');
2663 hr_utility.set_location(l_proc ||substr(l_post_txn_function,1,30),15);
2664 decode_itemkey(p_itemkey => itemkey
2665 , p_transaction_category_id => l_transaction_category_id
2666 , p_transaction_id => l_transaction_id
2667 );
2668 l_post_txn_function := 'begin :l_status := ' || l_post_txn_function ||
2669 '.chk_root_node(p_transaction_id =>'||
2670 to_char(l_transaction_id) || '); end;';
2671
2672 hr_utility.set_location(l_proc ||substr(l_post_txn_function,1,30),15);
2673 DECLARE
2674 l_sqlerrm VARCHAR2(2000);
2675 l_sqlcode NUMBER;
2676 l_dbupdate varchar2(2000);
2677 BEGIN
2678 EXECUTE IMMEDIATE l_post_txn_function USING OUT l_dbupdate;
2679 result := 'COMPLETE:'||l_dbupdate;
2680 EXCEPTION
2681 WHEN chk_root_not_defined1 THEN
2682 result := 'COMPLETE:ROOT';
2683 WHEN chk_root_not_defined THEN
2684 result := 'COMPLETE:ROOT';
2685 WHEN OTHERS THEN
2686 l_sqlcode := sqlcode;
2687 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
2688 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
2689 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 31);
2690 END;
2691 end if;
2692 exception when others then
2693 result := null;
2694 raise;
2695 END;
2696
2697 -- instead of this function, we use pqh_workflow.valid_user_opening procedure
2698 -- which in turn builds the default role
2699
2700 FUNCTION get_default_role (
2701 p_transaction_category_id NUMBER
2702 , p_user_id in NUMBER default FND_PROFILE.VALUE('USER_ID')
2703 )
2704 RETURN NUMBER
2705 IS
2706
2707 l_role_id number(15);
2708 l_member_cd pqh_transaction_categories.member_cd%type;
2709 l_position_id number;
2710 l_assignment_id number;
2711 l_workflow_enable_flag pqh_transaction_categories.workflow_enable_flag%type;
2712
2713 cursor c_get_txn_cat (p_transaction_category_id NUMBER) IS
2714 SELECT member_cd, workflow_enable_flag
2715 FROM pqh_transaction_categories tct
2716 WHERE transaction_category_id = p_transaction_category_id;
2717
2718 cursor c_get_user_role (p_user_id NUMBER) is
2719 SELECT role_id
2720 FROM pqh_role_users_v
2721 WHERE user_id = p_user_id
2722 AND nvl(default_role,'X') = 'Y';
2723
2724 cursor c_get_assignment (p_user_id NUMBER) IS
2725 SELECT asg.assignment_id,asg.position_id
2726 FROM per_all_assignments asg
2727 , fnd_user fu
2728 WHERE asg.person_id = fu.employee_id
2729 AND fu.user_id = p_user_id
2730 AND asg.primary_flag = 'Y'
2731 AND asg.assignment_type = 'E'; -- added for bug 7708168
2732
2733 cursor c_get_pos_role(p_assignment_id number) is
2734 SELECT rls.role_id
2735 FROM per_all_assignments asg
2736 , pqh_position_roles_v rls
2737 WHERE asg.position_id = rls.position_id;
2738
2739 BEGIN
2740 hr_utility.set_location(' User ID '||p_user_id,9999);
2741 OPEN c_get_txn_cat(p_transaction_category_id => p_transaction_category_id);
2742 FETCH c_get_txn_cat INTO l_member_cd, l_workflow_enable_flag;
2743 CLOSE c_get_txn_cat;
2744 hr_utility.set_location('l_member_cd '||l_member_cd,9999);
2745 IF NVL(l_workflow_enable_flag, 'N') = 'Y' THEN
2746 IF l_member_cd = 'R' THEN
2747 -- use the view pqh_role_users_v for selecting the default role of the user
2748 OPEN c_get_user_role(p_user_id => p_user_id);
2749 FETCH c_get_user_role INTO l_role_id;
2750 CLOSE c_get_user_role;
2751 hr_utility.set_location('l_role_id '||l_role_id,9997);
2752 ELSIF l_member_cd in ('P','S') then
2753 OPEN c_get_assignment(p_user_id => p_user_id);
2754 FETCH c_get_assignment INTO l_assignment_id,l_position_id;
2755 CLOSE c_get_assignment;
2756 hr_utility.set_location('l_assignment_id '||l_assignment_id,9998);
2757 hr_utility.set_location('l_position_id '||l_position_id,9998);
2758 if l_position_id is not null then
2759 OPEN c_get_pos_role(p_assignment_id => l_assignment_id);
2760 FETCH c_get_pos_role INTO l_role_id;
2761 CLOSE c_get_pos_role;
2762 hr_utility.set_location('l_role_id '||l_role_id,9998);
2763 else
2764 OPEN c_get_user_role(p_user_id => p_user_id);
2765 FETCH c_get_user_role INTO l_role_id;
2766 CLOSE c_get_user_role;
2767 hr_utility.set_location('l_role_id '||l_role_id,9999);
2768 end if;
2769 END IF;
2770 IF l_role_id IS NULL THEN
2771 hr_utility.set_message(8302,'PQH_USER_HAS_NO_ROLE');
2772 hr_utility.raise_error;
2773 END IF;
2774 ELSE
2775 l_role_id := -1;
2776 END IF;
2777 hr_utility.set_location('Just before return, role_id '||l_role_id,10000);
2778 RETURN l_role_id;
2779 END;
2780 procedure complete_delegate_workflow(
2781 p_itemkey in varchar2,
2782 p_workflow_name in varchar2 ) is
2783 begin
2784 wf_engine.CompleteActivity(
2785 p_workflow_name
2786 , p_itemkey
2787 , 'DELEGATE_BLOCK'
2788 , 'COMPLETE');
2789 end;
2790 procedure get_apply_error(p_transaction_id in number,
2791 p_transaction_category_id in number,
2792 p_apply_error_mesg out nocopy varchar2,
2793 p_apply_error_num out nocopy varchar2) is
2794 l_proc varchar2(61) := g_package ||'get_apply_error' ;
2795 l_workflow_name varchar2(30);
2796 l_itemkey varchar2(30);
2797 begin
2798 hr_utility.set_location(l_proc || ' Entering',10);
2799 l_workflow_name := get_workflow_name(p_transaction_category_id => p_transaction_category_id);
2800 hr_utility.set_location(l_proc || 'workflow name is'||l_workflow_name,20);
2801 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
2802 hr_utility.set_location(l_proc || 'itemkey'||l_itemkey,25);
2803 get_apply_error(p_itemkey => l_itemkey,
2804 p_workflow_name => l_workflow_name,
2805 p_apply_error_mesg => p_apply_error_mesg,
2806 p_apply_error_num => p_apply_error_num );
2807 hr_utility.set_location(l_proc || 'apply_code'||p_apply_error_num,30);
2808 hr_utility.set_location(l_proc || 'apply_mesg'||substr(p_apply_error_mesg,1,20),40);
2809 hr_utility.set_location(l_proc || ' Exiting',100);
2810 end;
2811
2812 PROCEDURE set_apply_error(p_transaction_id IN NUMBER,
2813 p_transaction_category_id IN NUMBER,
2814 p_apply_error_mesg IN VARCHAR2,
2815 p_apply_error_num IN VARCHAR2)
2816 IS
2817 l_proc varchar2(61) := g_package ||'set_apply_error' ;
2818 l_workflow_name varchar2(30);
2819 l_itemkey varchar2(30);
2820 BEGIN
2821 hr_utility.set_location(l_proc || 'Entering',10);
2822 hr_utility.set_location(l_proc || 'txn_cat is' ||p_transaction_category_id,15);
2823 hr_utility.set_location(l_proc || 'txn_id is' ||p_transaction_id,16);
2824
2825 IF p_transaction_category_id IS NULL
2826 OR p_transaction_id IS NULL THEN
2827 hr_utility.set_message(8302,'PQH_NULL_TRANSACTION_ID_OR_CAT');
2828 hr_utility.raise_error;
2829 END IF;
2830
2831 l_workflow_name := get_workflow_name(p_transaction_category_id => p_transaction_category_id);
2832 hr_utility.set_location(l_proc || 'workflow name is' ||l_workflow_name,20);
2833 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(p_transaction_id) ;
2834
2835 hr_utility.set_location(l_proc || 'txn_cat is' ||p_transaction_category_id,15);
2836 hr_utility.set_location(l_proc || 'itemkey' ||l_itemkey,25);
2837
2838 set_apply_error(p_itemkey => l_itemkey,
2839 p_workflow_name => l_workflow_name,
2840 p_apply_error_mesg => p_apply_error_mesg,
2841 p_apply_error_num => p_apply_error_num );
2842 hr_utility.set_location(l_proc || ' Exiting',100);
2843 END;
2844
2845 PROCEDURE set_status ( p_workflow_name IN VARCHAR2,
2846 p_item_id IN VARCHAR2,
2847 p_status IN VARCHAR2,
2848 p_result OUT NOCOPY VARCHAR2)
2849 IS
2850 l_proc VARCHAR2(61) := g_package || 'set_status';
2851 set_status_not_defined EXCEPTION;
2852 pragma exception_init (set_status_not_defined, -6550);
2853 l_tran_cat_id number;
2854 l_transaction_id number;
2855 l_tran_cat_name varchar2(30);
2856 l_post_txn_function varchar2(61);
2857 l_hyphen_pos number;
2858 l_set_status varchar2(200);
2859 cursor c1 is select post_txn_function,short_name
2860 from pqh_transaction_categories
2861 where transaction_category_id = l_tran_cat_id ;
2862 BEGIN
2863 hr_utility.set_location(l_proc || ' Entering',10);
2864 hr_utility.set_location(l_proc || ' workflow ' || p_workflow_name, 10);
2865 hr_utility.set_location(l_proc || ' item id ' || p_item_id,10);
2866 l_hyphen_pos := INSTR(p_item_id, '-');
2867 l_tran_cat_id := TO_NUMBER(SUBSTR(p_item_id, 1, l_hyphen_pos - 1));
2868 l_transaction_id := TO_NUMBER(SUBSTR(p_item_id, l_hyphen_pos + 1));
2869 hr_utility.set_location(l_proc || 'transaction id ' || l_transaction_id,20);
2870 hr_utility.set_location(l_proc || 'tran_cat_id ' || l_tran_cat_id,20);
2871 open c1;
2872 fetch c1 into l_post_txn_function,l_tran_cat_name;
2873 close c1;
2874 hr_utility.set_location(l_proc || 'post funtion string ' || l_post_txn_function,20);
2875 l_set_status := 'begin :l_status := ' || l_post_txn_function ||
2876 '.set_status( p_transaction_category_id => ' || to_char(l_tran_cat_id) ||
2877 ',p_transaction_id =>'|| to_char(l_transaction_id) ||
2878 ',p_status =>'''||p_status ||
2879 '''); end; ';
2880
2881 hr_utility.set_location(l_proc || 'dyn string ' || substr(l_set_status, 1, 40),20);
2882 hr_utility.set_location(l_proc || 'dyn string ' || substr(l_set_status, 41, 40),20);
2883 hr_utility.set_location(l_proc || 'dyn string ' || substr(l_set_status, 81, 40),20);
2884 hr_utility.set_location(l_proc || 'dyn string ' || substr(l_set_status, 121),20);
2885 DECLARE
2886 l_sqlerrm VARCHAR2(2000);
2887 l_sqlcode NUMBER;
2888 BEGIN
2889 EXECUTE IMMEDIATE l_set_status USING OUT p_result;
2890 EXCEPTION
2891 WHEN set_status_not_defined THEN
2892 p_result := 'UNDEF';
2893 raise;
2894 WHEN OTHERS THEN
2895 l_sqlcode := sqlcode;
2896 l_sqlerrm := substr(sqlerrm(l_sqlcode), 1, 100);
2897 p_result := l_sqlcode;
2898 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,1,30), 30);
2899 hr_utility.set_location(l_proc || ' ' || substr(l_sqlerrm,31,30), 32);
2900 raise;
2901 END;
2902 -- document := document || l_fyi_notification ;
2903 hr_utility.set_location(l_proc || 'Exiting',100);
2904 exception when others then
2905 p_result := null;
2906 raise;
2907 end;
2908 function get_current_owner(p_itemkey in varchar2) return varchar2 is
2909 l_user_name varchar2(100);
2910 begin
2911 l_user_name := get_notification_detail(p_itemkey => p_itemkey,
2912 p_mode => 'USER');
2913 return l_user_name;
2914 end;
2915 function get_current_owner(p_transaction_id in number,
2916 p_transaction_category_id in number,
2917 p_status in varchar2) return varchar2 is
2918 l_itemkey varchar2(100);
2919 l_current_owner varchar2(100);
2920 begin
2921 if nvl(p_status,'PENDING') not in ('APPLIED','TERMINATE','SUBMITTED','REJECT') then
2922 l_itemkey := p_transaction_category_id||'-'||p_transaction_id;
2923 l_current_owner := get_current_owner(p_itemkey => l_itemkey);
2924 else
2925 l_current_owner := '';
2926 end if;
2927 return l_current_owner;
2928 end;
2929 function get_current_owner(p_transaction_id in number,
2930 p_transaction_category_id in number) return varchar2 is
2931 l_itemkey varchar2(100);
2932 l_current_owner varchar2(100);
2933 begin
2934 l_itemkey := p_transaction_category_id||'-'||p_transaction_id;
2935 l_current_owner := get_current_owner(p_itemkey => l_itemkey);
2936 return l_current_owner;
2937 end;
2938 function get_person_name(p_user_id in number default null,
2939 p_assignment_id in number default null) return varchar2 is
2940 cursor c1 is select full_name
2941 from per_all_people_f per, fnd_user usr
2942 where per.person_id = usr.employee_id
2943 and usr.user_id = p_user_id;
2944 cursor c2 is select full_name
2945 from per_all_assignments_f asg, per_all_people_f per
2946 where asg.person_id = per.person_id
2947 and sysdate between asg.effective_start_date and asg.effective_end_date
2948 and sysdate between per.effective_start_date and per.effective_end_date
2949 and asg.assignment_id = p_assignment_id ;
2950 l_full_name varchar2(240);
2951 begin
2952 if p_user_id is not null then
2953 /* pick the first record of the person and show the name */
2954 open c1;
2955 fetch c1 into l_full_name;
2956 close c1;
2957 elsif p_assignment_id is not null then
2958 open c2;
2959 fetch c2 into l_full_name;
2960 close c2;
2961 end if;
2962 return l_full_name;
2963 end;
2964 END;