[Home] [Help]
PACKAGE BODY: APPS.PQH_WORKFLOW
Source
1 Package body pqh_workflow as
2 /* $Header: pqwrkflw.pkb 120.4.12010000.5 2008/09/22 13:13:37 brsinha ship $ */
3 g_package varchar2(100) := 'pqh_workflow.';
4 procedure get_primary_asg_details(p_person_id in number,
5 p_effective_date in date,
6 p_assignment_id out nocopy number,
7 p_position_id out nocopy number) is
8 begin
9 select assignment_id,position_id
10 into p_assignment_id, p_position_id
11 from per_all_assignments_f
12 where person_id = p_person_id
13 and primary_flag ='Y'
14 AND assignment_type = 'E' -- bug 7330323
15 and p_effective_date between effective_start_date and effective_end_date;
16 exception
17 when others THEN
18 hr_utility.set_location('Error inside get_primary_asg_details: ',10);
19 hr_utility.set_location(sqlerrm,15);
20 p_assignment_id := null;
21 p_position_id := null;
22 end;
23 procedure get_user_name_details(p_user_name in varchar2,
24 p_user_id out nocopy number,
25 p_employee_id out nocopy varchar2) is
26 l_user_id fnd_user.user_id%type;
27 l_employee_id fnd_user.employee_id%type;
28 begin
29 select user_id,employee_id
30 into l_user_id,l_employee_id
31 from fnd_user
32 where user_name = p_user_name;
33 p_user_id := l_user_id;
34 p_employee_id := l_employee_id;
35 exception
36 when others then
37 p_user_id := l_user_id;
38 p_employee_id := l_employee_id;
39 end;
40
41 procedure get_user_id_details(p_user_id in number,
42 p_user_name out nocopy varchar2,
43 p_employee_id out nocopy varchar2) is
44 l_user_name fnd_user.user_name%type;
45 l_employee_id fnd_user.employee_id%type;
46 begin
47 select user_name,employee_id
48 into l_user_name,l_employee_id
49 from fnd_user
50 where user_id = p_user_id;
51 p_user_name := l_user_name;
52 p_employee_id := l_employee_id;
53 exception
54 when others then
55 p_user_name := l_user_name;
56 p_employee_id := l_employee_id;
57 end;
58 function tran_setup(p_tran_cat_id in number) return varchar2 is
59 l_setup_type_cd varchar2(30);
60 l_freeze_status_cd varchar2(30);
61 begin
62 select freeze_status_cd,setup_type_cd
63 into l_freeze_status_cd,l_setup_type_cd
64 from pqh_transaction_categories
65 where transaction_category_id = p_tran_cat_id
66 and nvl(enable_flag,'Y') = 'Y';
67 if l_freeze_status_cd <>'FREEZE_CATEGORY' then
68 l_setup_type_cd := 'INCOMPLETE' ;
69 end if;
70 return l_setup_type_cd;
71 end tran_setup;
72
73 function pos_in_ph(p_position_id in number,
74 p_pos_str_ver_id in number) return varchar2 is
75 l_return varchar2(30);
76 begin
77 hr_utility.set_location('Entering pos_in_ph',10);
78
79 select 'TRUE' into l_return
80 from per_pos_structure_elements
81 where subordinate_position_id = p_position_id
82 and pos_structure_version_id = p_pos_str_ver_id;
83
84 hr_utility.set_location('Exiting pos_in_ph',100);
85
86 return l_return;
87 exception
88 when no_data_found then
89 begin
90 hr_utility.set_location('Exiting pos_in_ph',101);
91
92 select 'TRUE' into l_return
93 from per_pos_structure_elements
94 where parent_position_id = p_position_id
95 and pos_structure_version_id = p_pos_str_ver_id;
96
97 hr_utility.set_location('Exiting pos_in_ph',102);
98
99 return l_return;
100 exception
101 when no_data_found then
102 hr_utility.set_location('Exiting pos_in_ph',103);
103
104 l_return := 'FALSE';
105 return l_return;
106 when too_many_rows then
107 hr_utility.set_location('Exiting pos_in_ph',104);
108 return 'TRUE';
109 end;
110 when too_many_rows then
111 hr_utility.set_location('Exiting pos_in_ph',105);
112 return 'TRUE';
113 end;
114
115 function get_transaction_category_name(p_transaction_category_id in number) return varchar2 is
116 cursor c1 is select name
117 from pqh_transaction_categories
118 where transaction_category_id = p_transaction_category_id;
119 l_transaction_category_name pqh_transaction_categories.name%type;
120 begin
121 open c1;
122 fetch c1 into l_transaction_category_name;
123 close c1;
124 return l_transaction_category_name;
125
126 end get_transaction_category_name;
127
128 function get_role_name(p_role_id in number) return varchar2 is
129 cursor c1 is select role_name
130 from pqh_roles
131 where role_id = p_role_id;
132 l_role_name pqh_roles.role_name%type;
133 begin
134 open c1;
135 fetch c1 into l_role_name;
136 close c1;
137 return l_role_name;
138 end get_role_name;
139
140 /**
141 function get_user_default_role(p_user_id in number,
142 p_session_date in date) return number is
143 l_role_id number;
144 cursor c_get_user_role (p_user_id number) is
145 select decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0) role_id
146 from per_people_extra_info pei , per_all_people_f ppf, fnd_user usr
147 WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
148 and p_session_date between ppf.effective_start_date and ppf.effective_end_date
149 and usr.employee_id = ppf.person_id
150 and nvl(pei.pei_information5,'Y')='Y'
151 and nvl(pei.pei_information4,'N')='Y'
152 and usr.user_id = p_user_id;
153 begin
154 open c_get_user_role(p_user_id => p_user_id);
155 fetch c_get_user_role into l_role_id;
156 close c_get_user_role;
157 return l_role_id;
158 end get_user_default_role;
159 **/
160
161 procedure get_default_role(p_session_date in date,
162 p_transaction_category_id in number,
163 p_user_id in number,
164 p_person_id out nocopy number,
165 p_role_id out nocopy number) is
166 l_member_cd pqh_transaction_categories.member_cd%type;
167 l_position_id number;
168 l_person_name varchar2(240);
169 l_position_name hr_all_positions_f.name%type;
170 l_assignment_id number;
171 l_workflow_enable_flag pqh_transaction_categories.workflow_enable_flag%type;
172 l_proc varchar2(256) := g_package||'get_default_role';
173 l_user_name fnd_user.user_name%type := fnd_profile.value('USERNAME');
174
175 cursor c_get_employee(p_user_id number) is
176 select employee_id
177 from fnd_user
178 where user_id = p_user_id;
179
180 cursor c_get_txn_cat (p_transaction_category_id number) IS
181 select member_cd, workflow_enable_flag
182 from pqh_transaction_categories tct
183 where transaction_category_id = p_transaction_category_id;
184
185 cursor c_get_assignment (p_user_id number) IS
186 select asg.assignment_id,asg.position_id
187 from per_all_assignments_f asg
188 , fnd_user fu
189 where asg.person_id = fu.employee_id
190 and fu.user_id = p_user_id
191 and asg.primary_flag = 'Y'
192 AND asg.assignment_type = 'E' -- Bug 7422915
193 and p_session_date between asg.effective_start_date and asg.effective_end_date;
194
195 cursor c_get_pos_role(p_assignment_id number) is
196 select decode(information_type, 'PQH_POS_ROLE_ID' , to_number(poei_information3), -1) ROLE_ID
197 from per_position_extra_info pei, hr_all_positions_f pos, per_all_assignments_f asg
198 where pei.position_id=pos.position_id and pei.information_type='PQH_POS_ROLE_ID'
199 and pos.position_id = asg.position_id
200 and p_session_date between pos.effective_start_date and pos.effective_end_date
201 and p_session_date between asg.effective_start_date and asg.effective_end_date
202 and asg.assignment_id = p_assignment_id;
203 begin
204 hr_utility.set_location('Entering'||l_proc,10);
205 hr_utility.set_location('user_id is'||p_user_id||l_proc,11);
206 open c_get_txn_cat(p_transaction_category_id => p_transaction_category_id);
207 fetch c_get_txn_cat into l_member_cd, l_workflow_enable_flag;
208 close c_get_txn_cat;
209 if nvl(l_workflow_enable_flag, 'N') = 'Y' then
210 hr_utility.set_location('txn_cat wf_enabled'||l_proc,20);
211 open c_get_employee(p_user_id => p_user_id);
212 fetch c_get_employee into p_person_id;
213 close c_get_employee;
214 if p_person_id is null then
215 hr_utility.set_location('person does not exist'||l_proc,21);
216 p_role_id := -2;
217 else
218 l_person_name := hr_general.decode_person_name(p_person_id =>p_person_id);
219 hr_utility.set_location('person_id is'||p_person_id||l_proc,25);
220 hr_utility.set_location('person is'||l_person_name||l_proc,26);
221 if l_member_cd = 'R' then
222 hr_utility.set_location('routing style RL'||l_proc,30);
223 p_role_id := get_user_default_role(p_user_id => p_user_id);
224 /**
225 p_role_id := get_user_default_role(p_user_id => p_user_id,
226 p_session_date => p_session_date);
227 **/
228 elsif l_member_cd in ('P','S') then
229 hr_utility.set_location('routing style '||l_member_cd||l_proc,40);
230 open c_get_assignment(p_user_id => p_user_id);
231 fetch c_get_assignment into l_assignment_id,l_position_id;
232 if c_get_assignment%notfound then
233 close c_get_assignment;
234 hr_utility.set_location('primary assignment not found '||l_proc,45);
235 hr_utility.set_message(8302,'PQH_NO_PRIMARY_ASSIGNMENT');
236 hr_utility.set_message_token('PERSON',l_person_name);
237 else
238 close c_get_assignment;
239 if l_position_id is not null then
240 hr_utility.set_location('assignment for pos:'||l_position_id||l_proc,50);
241 open c_get_pos_role(p_assignment_id => l_assignment_id);
242 fetch c_get_pos_role into p_role_id;
243 if c_get_pos_role%notfound then
244 hr_utility.set_location('role not attached to POS'||l_proc,52);
245 l_position_name := hr_general.decode_position_latest_name(p_position_id =>l_position_id);
246 hr_utility.set_location('POS name'||l_position_name||l_proc,53);
247 hr_utility.set_message(8302,'PQH_NO_ACTIVE_ROLE_FOR_POS');
248 hr_utility.set_message_token('POSITION',l_position_name);
249 hr_utility.set_message_token('PERSON',l_person_name);
250 p_role_id := -3 ;
251 else
252 hr_utility.set_location('role for pos:'||p_role_id||l_proc,55);
253 end if;
254 close c_get_pos_role;
255 else
256 hr_utility.set_location('user role '||l_proc,60);
257 p_role_id := get_user_default_role(p_user_id => p_user_id);
258 /**
259 p_role_id := get_user_default_role(p_user_id => p_user_id,
260 p_session_date => p_session_date);
261 **/
262 end if;
263 end if;
264 end if;
265 end if;
266 else
267 p_role_id := -1;
268 end if;
269 hr_utility.set_location('default role is'||p_role_id||l_proc,70);
270 hr_utility.set_location('Exiting'||l_proc,80);
271 exception when others then
272 p_person_id := null;
273 p_role_id := null;
274 raise;
275 end get_default_role;
276
277 function get_role_template(p_role_id in number,
278 p_transaction_category_id in number) return number is
279 cursor c1 is select template_id
280 from pqh_role_templates
281 where role_id = p_role_id
282 and transaction_category_id = p_transaction_category_id
283 and enable_flag = 'Y';
284 l_template_id pqh_role_templates.template_id%type;
285 l_role_name pqh_roles.role_name%type;
286 l_transaction_category_name pqh_transaction_categories.name%type;
287 begin
288 open c1;
289 fetch c1 into l_template_id;
290 if c1%notfound then
291 l_template_id := -1 ;
292 l_role_name := get_role_name(p_role_id);
293 l_transaction_category_name := get_transaction_category_name(p_transaction_category_id);
294 hr_utility.set_message(8302,'PQH_NO_DOMAIN_TEMPLATE');
295 hr_utility.set_message_token('ROLE',l_role_name);
296 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_transaction_category_name);
297 end if;
298 close c1;
299 return l_template_id;
300 end get_role_template;
301
302 function check_user_pos_details(p_value_date in date,
303 p_person_id in number) return number is
304 l_error_cd number := 0;
305 l_position_id number;
306 l_assignment_id number;
307 l_proc varchar2(256) := g_package||'check_user_pos_details';
308 l_effective_date date;
309 l_user_name fnd_user.user_name%type := fnd_profile.value('USERNAME');
310 l_position_name varchar2(240);
311 l_person_name varchar2(240);
312 begin
313 hr_utility.set_location('Entering '||l_proc,10);
314 l_person_name := hr_general.decode_person_name(p_person_id =>p_person_id);
315 get_primary_asg_details(p_person_id => p_person_id,
316 p_effective_date => p_value_date,
317 p_assignment_id => l_assignment_id,
318 p_position_id => l_position_id);
319 if l_assignment_id is null then
320 l_error_cd := 2;
321 hr_utility.set_location('primary assignment not found '||l_proc,50);
322 hr_utility.set_message(8302,'PQH_NO_PRIMARY_ASSIGNMENT');
323 hr_utility.set_message_token('PERSON',l_person_name);
324 else
325 hr_utility.set_location('details found '||l_proc,52);
326 if l_position_id is null then
327 l_error_cd := 3;
328 hr_utility.set_location('primary assignment not for position '||l_proc,54);
329 hr_utility.set_message(8302,'PQH_PRIMARY_ASG_NOT_POS');
330 hr_utility.set_message_token('PERSON',l_person_name);
331 else
332 hr_utility.set_location('position found '||l_position_id||l_proc,60);
333 l_effective_date := hr_general.get_position_date_end(p_position_id => l_position_id);
334 if (l_effective_date is null or l_effective_date > trunc(sysdate)) then
335 hr_utility.set_location('valid position '||l_proc,61);
336 else
337 l_error_cd := 4;
338 l_position_name := hr_general.decode_position_latest_name(l_position_id);
339 hr_utility.set_location('Position Eliminated '||l_proc,62);
340 hr_utility.set_message(8302,'PQH_POS_ELIMINATED');
341 hr_utility.set_message_token('POSITION',l_position_name);
342 end if;
343 end if;
344 end if;
345 return l_error_cd;
346 end check_user_pos_details;
347
348 function check_user_asg_details(p_value_date in date,
349 p_person_id in number) return number is
350 l_error_cd number := 0 ;
351 l_assignment_id number;
352 l_position_id number;
353 l_user_name fnd_user.user_name%type := fnd_profile.value('USERNAME');
354 l_person_name varchar2(240);
355 l_proc varchar2(256) := g_package||'check_user_asg_details';
356 begin
357 hr_utility.set_location('Entering '||l_proc,10);
358 hr_utility.set_location('Value date is '||to_char(p_value_date,'dd/MM/RRRR')||l_proc,22);
359 l_person_name := hr_general.decode_person_name(p_person_id =>p_person_id);
360 get_primary_asg_details(p_person_id => p_person_id,
361 p_effective_date => p_value_date,
362 p_assignment_id => l_assignment_id,
363 p_position_id => l_position_id);
364 if l_assignment_id is null then
365 l_error_cd := 2;
366 hr_utility.set_location('primary assignment not found '||l_proc,50);
367 hr_utility.set_message(8302,'PQH_NO_PRIMARY_ASSIGNMENT');
368 hr_utility.set_message_token('PERSON',l_person_name);
369 else
370 hr_utility.set_location('details found '||l_proc,52);
371 end if;
372 hr_utility.set_location('Exiting '||l_proc,200);
373 return l_error_cd;
374 end check_user_asg_details ;
375
376 function check_user_role_details(p_role_id in number,
377 p_user_id in number,
378 p_session_date in date) return number is
379 cursor c1 is select role_name from pqh_roles
380 where role_id = p_role_id
381 and nvl(enable_flag,'X') = 'Y';
382 cursor c3 is select 'X'
383 from per_people_extra_info pei , per_all_people_f ppf, fnd_user usr
384 WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
385 and p_session_date between ppf.effective_start_date and ppf.effective_end_date
386 and usr.employee_id = ppf.person_id
387 and decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0)= p_role_id
388 and nvl(pei.pei_information5,'Y')='Y'
389 and usr.user_id = p_user_id ;
390 l_error_cd number := 0;
391 l_role_name pqh_roles.role_name%type;
392 l_dummy varchar2(30);
393 l_proc varchar2(256) := g_package||'check_user_role_details';
394 begin
395 hr_utility.set_location('Entering '||l_proc,10);
396 open c1;
397 fetch c1 into l_role_name;
398 if c1%found then
399 hr_utility.set_location('role exists '||l_proc,20);
400 close c1;
401 -- check role is assigned to user
402 open c3;
403 fetch c3 into l_dummy;
404 if c3%notfound then
405 hr_utility.set_location('role-user combination does not exist'||l_proc,50);
406 l_error_cd := 20;
407 hr_utility.set_message(8302,'PQH_ROLE_NOT_ATCHD_TO_USER');
408 hr_utility.set_message_token('ROLE',l_role_name);
409 end if;
410 close c3;
411 else
412 hr_utility.set_location('role does not exist '||l_proc,60);
413 close c1;
414 -- role is a disabled role
415 l_error_cd := 1;
416 hr_utility.set_message(8302,'PQH_INVALID_ROLE');
417 end if;
418 hr_utility.set_location('error_cd is '||l_error_cd||l_proc,70);
419 hr_utility.set_location('exiting'||l_proc,100);
420 return l_error_cd;
421 end check_user_role_details;
422
423 function routing_role(p_member_id in number,
424 p_routing_history_id in number,
425 p_user_id in number) return number is
426 l_role_id number;
427 l_role_name varchar2(300);
428 l_dummy varchar2(10);
429 cursor c_rlm(p_routing_history_id number) is
430 select role_id
431 from pqh_routing_history rht, pqh_routing_list_members rlm
432 where rht.forwarded_to_member_id = rlm.routing_list_member_id
433 and rht.routing_history_id = p_routing_history_id;
434 cursor c_role_users(p_user_id number, p_role_id number) is
435 select 'X'
436 from per_people_extra_info pei , per_all_people_f ppf, fnd_user usr
437 WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
438 and sysdate between ppf.effective_start_date and ppf.effective_end_date
439 and usr.employee_id = ppf.person_id
440 and decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0)= p_role_id
441 and nvl(pei.pei_information5,'Y')='Y'
442 and usr.user_id = p_user_id ;
443
444 begin
445 open c_rlm(p_routing_history_id);
446 fetch c_rlm into l_role_id;
447 if c_rlm%notfound then
448 close c_rlm;
449 hr_utility.set_message(8302,'PQH_MEMBER_NOTIN_RL');
450 l_role_id := -1;
451 else
452 close c_rlm;
453 open c_role_users(p_user_id, l_role_id);
454 fetch c_role_users INTO l_dummy;
455 if c_role_users%notfound then
456 close c_role_users;
457 l_role_name := get_role_name(l_role_id);
458 hr_utility.set_message(8302,'PQH_ROLE_NOT_ATCHD_TO_USER');
459 hr_utility.set_message_token('ROLE',l_role_name);
460 else
461 close c_role_users;
462 end if;
463 end if;
464 return(l_role_id);
465 end routing_role;
466
467 function get_routinghistory_role(p_routing_history_id in number,
468 p_user_id in number,
469 p_user_name in varchar2) return number IS
470 l_role_id number := -1;
471 l_role_name varchar2(300);
472 l_position_id number;
473 l_person_id number;
474 l_position_name varchar2(240);
475 l_rht_member_id number;
476 l_rht_position_id number;
477 l_rht_assignment_id number;
478 cursor c_person_details is
479 select employee_id from fnd_user
480 where user_id = p_user_id;
481 cursor c_position_role(p_position_id number) is
482 SELECT decode(information_type, 'PQH_POS_ROLE_ID' , to_number(poei_information3), -1) ROLE_ID
483 from per_position_extra_info pei
484 WHERE pei.position_id=p_position_id
485 and pei.information_type='PQH_POS_ROLE_ID';
486
487 cursor c_asg_details(p_assignment_id number,l_person_id number) is
488 select position_id
489 from per_all_assignments_f
490 where assignment_id = p_assignment_id
491 and person_id = l_person_id
492 and primary_flag = 'Y'
493 and sysdate between effective_start_date and effective_end_date;
494
495 cursor c_person_role(p_person_id number) is
496 select decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0) role_id
497 from per_people_extra_info pei , per_all_people_f ppf
498 WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
499 and sysdate between ppf.effective_start_date and ppf.effective_end_date
500 and ppf.person_id = p_person_id
501 and nvl(pei.pei_information5,'Y')='Y'
502 and nvl(pei.pei_information4,'N')='Y';
503 l_user_name fnd_user.user_name%type := fnd_profile.value('USERNAME');
504 l_person_name varchar2(240);
505 l_proc varchar2(71) := g_package||'get_routinghist_role' ;
506 BEGIN
507 hr_utility.set_location('entering'||l_proc,10);
508 if nvl(p_routing_history_id,-1) >0 then
509 open c_person_details;
510 fetch c_person_details into l_person_id;
511 close c_person_details;
512 l_person_name := hr_general.decode_person_name(p_person_id =>l_person_id);
513 hr_utility.set_location('person_id is'||l_person_id||l_proc,20);
514 hr_utility.set_location('person_name is'||l_person_name||l_proc,21);
515 select forwarded_to_member_id, forwarded_to_position_id,forwarded_to_assignment_id
516 into l_rht_member_id,l_rht_position_id,l_rht_assignment_id
517 from pqh_routing_history
518 where routing_history_id = p_routing_history_id;
519 hr_utility.set_location('rout_hist details pulled'||l_proc,30);
520 if l_rht_member_id is not null then
521 hr_utility.set_location('member role to be pulled'||l_proc,40);
522 l_role_id := routing_role(p_routing_history_id => p_routing_history_id,
523 p_member_id => l_rht_member_id,
524 p_user_id => p_user_id);
525 hr_utility.set_location('member role is'||l_role_id||l_proc,50);
526 end if;
527 if (l_rht_assignment_id is not null) then
528 hr_utility.set_location('assignment details '||l_proc,60);
529 open c_asg_details(l_rht_assignment_id,l_person_id);
530 fetch c_asg_details into l_position_id;
531 if c_asg_details%notfound then
532 close c_asg_details;
533 hr_utility.set_message(8302,'PQH_NO_PRIMARY_ASSIGNMENT');
534 hr_utility.set_message_token('PERSON',l_person_name);
535 else
536 close c_asg_details;
537 hr_utility.set_location('assignment details found'||l_proc,70);
538 if l_position_id is not null then
539 hr_utility.set_location('position assignment '||l_proc,80);
540 open c_position_role(l_position_id);
541 fetch c_position_role into l_role_id;
542 if c_position_role%notfound then
543 hr_utility.set_location('position role notfound '||l_proc,90);
544 close c_position_role;
545 l_position_name := hr_general.decode_position_latest_name(l_position_id);
546 hr_utility.set_message(8302,'PQH_NO_ACTIVE_ROLE_FOR_POS');
547 hr_utility.set_message_token('POSITION',l_position_name);
548 hr_utility.set_message_token('PERSON',l_person_name);
549 else
550 hr_utility.set_location('position role is '||l_role_id||l_proc,100);
551 close c_position_role;
552 end if;
553 else
554 hr_utility.set_location('other assignment '||l_proc,120);
555 open c_person_role(l_person_id);
556 fetch c_person_role into l_role_id;
557 if c_person_role%notfound then
558 hr_utility.set_location('person role notfound '||l_proc,130);
559 close c_person_role;
560 hr_utility.set_message(8302,'PQH_NO_DEFAULT_ROLE');
561 hr_utility.set_message_token('USERNAME',p_user_name);
562 else
563 hr_utility.set_location('person role is '||l_role_id||l_proc,140);
564 close c_person_role;
565 end if;
566 end if;
567 end if;
568 elsif l_rht_position_id is not null then
569 hr_utility.set_location('position role '||l_proc,150);
570 open c_position_role(l_rht_position_id);
571 fetch c_position_role into l_role_id;
572 if c_position_role%notfound then
573 hr_utility.set_location('position role notfound'||l_proc,160);
574 close c_position_role;
575 l_position_name := hr_general.decode_position_latest_name(l_rht_position_id);
576 hr_utility.set_message(8302,'PQH_NO_ACTIVE_ROLE_FOR_POS');
577 hr_utility.set_message_token('POSITION',l_position_name);
578 hr_utility.set_message_token('PERSON',l_person_name);
579 else
580 hr_utility.set_location('position role is'||l_role_id||l_proc,170);
581 close c_position_role;
582 end if;
583 end if;
584 end if;
585 hr_utility.set_location('exiting'||l_proc,200);
586 return l_role_id;
587 end get_routinghistory_role;
588
589 function get_txn_cat( p_short_name in varchar2,
590 p_business_group_id in number default null) return number IS
591 l_local_txncat_id number;
592 l_global_txncat_id number;
593 l_txncat_id number;
594 l_proc varchar2(71) := g_package||'get_txn_cat' ;
595 BEGIN
596 hr_utility.set_location('entering'||l_proc,10);
597 hr_utility.set_location('short_name'||p_short_name,12);
598 hr_utility.set_location('business_group_id'||p_business_group_id,13);
599 -- only local transaction category can be disabled
600 select transaction_category_id
601 into l_local_txncat_id
602 from pqh_transaction_categories
603 where business_group_id = nvl(p_business_group_id,-1)
604 and short_name = p_short_name
605 and nvl(enable_flag,'Y') = 'Y';
606 hr_utility.set_location('local tcat is'||l_local_txncat_id||l_proc,15);
607 hr_utility.set_location('exiting'||l_proc,10);
608 return l_local_txncat_id ;
609 exception
610 when no_data_found then
611 begin
612 hr_utility.set_location('bg tcat does not exist'||l_proc,20);
613 select transaction_category_id
614 into l_global_txncat_id
615 from pqh_transaction_categories
616 where business_group_id is null
617 and short_name = p_short_name;
618 hr_utility.set_location('global tcat is'||l_global_txncat_id||l_proc,30);
619 return l_global_txncat_id ;
620 exception
621 when no_data_found then
622 hr_utility.set_location('tcat does not exist'||l_proc,30);
623 hr_utility.set_message(8302,'PQH_INVALID_TXN_CAT_ID');
624 hr_utility.set_message_token('TRANSACTION',p_short_name);
625 hr_utility.raise_error;
626 end;
627 END;
628
629 procedure list_rout_crit is
630 i number;
631 begin
632 i := g_routing_criterion.first;
633 if i is not null then
634 loop
635 -- hr_utility.set_location('rec# is'||i||', att_id is'||g_routing_criterion(i).attribute_id,20);
636 -- hr_utility.set_location('used for is'||g_routing_criterion(i).used_for,40);
637 -- hr_utility.set_location('Attribute_type is'||g_routing_criterion(i).attribute_type,30);
638 -- hr_utility.set_location('Range_name is'||g_routing_criterion(i).rule_name,30);
639 -- hr_utility.set_location('value_num is'||g_routing_criterion(i).value_num,50);
640 hr_utility.set_location('from_num is'||g_routing_criterion(i).from_num||'-'||g_routing_criterion(i).to_num,60);
641 --hr_utility.set_location('value_num is'||g_routing_criterion(i).value_num,50);
642 --hr_utility.set_location('from_num is'||g_routing_criterion(i).from_num||'-'||g_routing_criterion(i).to_num,60);
643 exit when i= g_routing_criterion.last;
644 i := g_routing_criterion.next(i);
645 end loop;
646 end if;
647 end list_rout_crit;
648 procedure insert_rout_crit(p_attribute_id in number,
649 p_used_for in varchar default null,
650 p_rule_name in varchar default null,
651 p_attribute_type in varchar default null,
652 p_from_char in varchar default null,
653 p_to_char in varchar default null,
654 p_from_num in number default null,
655 p_to_num in number default null,
656 p_from_date in date default null,
657 p_to_date in date default null,
658 p_value_char in varchar default null,
659 p_value_num in number default null,
660 p_value_date in date default null) is
661 l_proc varchar2(81) := g_package||'insert_rout_crit';
662 i number;
663 begin
664 hr_utility.set_location('entering'||l_proc,10);
665 if p_used_for is not null then
666 i := nvl(g_routing_criterion.last,0) + 1;
667 hr_utility.set_location('Adding attribute_id'||p_attribute_id||l_proc,20);
668 hr_utility.set_location('Adding for i'||i||l_proc,30);
669 if p_attribute_type ='V' and p_value_char is not null then
670 g_routing_criterion(i).attribute_id := p_attribute_id;
671 g_routing_criterion(i).attribute_type := p_attribute_type;
672 g_routing_criterion(i).used_for := p_used_for;
673 g_routing_criterion(i).rule_name := p_rule_name;
674 g_routing_criterion(i).from_char := p_from_char;
675 g_routing_criterion(i).to_char := p_to_char;
676 g_routing_criterion(i).value_char := p_value_char;
677 end if;
678 if p_attribute_type ='N' and p_value_num is not null then
679 g_routing_criterion(i).attribute_id := p_attribute_id;
680 g_routing_criterion(i).attribute_type := p_attribute_type;
681 g_routing_criterion(i).used_for := p_used_for;
682 g_routing_criterion(i).rule_name := p_rule_name;
683 g_routing_criterion(i).from_num := p_from_num;
684 g_routing_criterion(i).to_num := p_to_num;
685 g_routing_criterion(i).value_num := p_value_num;
686 end if;
687 if p_attribute_type ='D' and p_value_date is not null then
688 g_routing_criterion(i).attribute_id := p_attribute_id;
689 g_routing_criterion(i).attribute_type := p_attribute_type;
690 g_routing_criterion(i).used_for := p_used_for;
691 g_routing_criterion(i).rule_name := p_rule_name;
692 g_routing_criterion(i).from_date := p_from_date;
693 g_routing_criterion(i).to_date := p_to_date;
694 g_routing_criterion(i).value_date := p_value_date;
695 end if;
696 end if;
697 hr_utility.set_location('exit'||l_proc,100);
698 list_rout_crit;
699 end insert_rout_crit;
700 procedure delete_rout_crit(p_used_for in varchar,
701 p_rule_name in varchar) is
702 l_proc varchar2(81) := g_package||'delete_rout_crit1';
703 i number;
704 begin
705 hr_utility.set_location('entering'||l_proc,10);
706 hr_utility.set_location('deleting rout_crit for'||p_used_for||l_proc,10);
707 i := g_routing_criterion.first;
708 if i is not null then
709 loop
710 if g_routing_criterion(i).used_for = p_used_for and
711 g_routing_criterion(I).rule_name <> p_rule_name then
712 g_routing_criterion.delete(i);
713 hr_utility.set_location('row deleted'||l_proc,10);
714 end if;
715 exit when i >= nvl(g_routing_criterion.LAST,0);
716 i := g_routing_criterion.NEXT(i);
717 end loop;
718 end if;
719 list_rout_crit;
720 hr_utility.set_location('exiting'||l_proc,100);
721 exception
722 when others then
723 hr_utility.set_location('error in delete_routing criterion'||l_proc,140);
724 null;
725 end delete_rout_crit;
726 procedure delete_rout_crit(p_used_for in varchar) is
727 l_proc varchar2(81) := g_package||'delete_rout_crit';
728 i number;
729 begin
730 hr_utility.set_location('entering'||l_proc,10);
731 hr_utility.set_location('deleting rout_crit for'||p_used_for||l_proc,10);
732 i := g_routing_criterion.first;
733 if i is not null then
734 loop
735 if g_routing_criterion(i).used_for = p_used_for then
736 g_routing_criterion.delete(i);
737 hr_utility.set_location('row deleted'||l_proc,10);
738 end if;
739 exit when i >= nvl(g_routing_criterion.LAST,0);
740 i := g_routing_criterion.NEXT(i);
741 end loop;
742 end if;
743 list_rout_crit;
744 hr_utility.set_location('exiting'||l_proc,100);
745 exception
746 when others then
747 hr_utility.set_location('error in delete_routing criterion'||l_proc,140);
748 null;
749 end delete_rout_crit;
750 procedure get_role_user(p_member_id in number,
751 p_role_id out nocopy number,
752 p_user_id out nocopy number ) is
753 cursor c1 is select role_id,user_id
754 from pqh_routing_list_members
755 where routing_list_member_id = p_member_id ;
756 begin
757 open c1;
758 fetch c1 into p_role_id,p_user_id;
759 if c1%notfound then
760 close c1;
761 hr_utility.set_message(8302,'PQH_MEMBER_NOTIN_RL');
762 hr_utility.raise_error;
763 end if;
764 close c1;
765 exception when others then
766 p_role_id := null;
767 p_user_id := null;
768 raise;
769 end;
770
771 function override_approver(p_routing_category_id in number,
772 p_member_cd in varchar2,
773 p_role_id in number default null,
774 p_user_id in number default null,
775 p_position_id in number default null,
776 p_assignment_id in number default null
777 ) return boolean is
778 cursor c1 is select override_role_id,override_user_id,override_position_id,override_assignment_id
779 from pqh_routing_categories
780 where routing_category_id = p_routing_category_id
781 and nvl(enable_flag,'X') = 'Y'
782 and nvl(delete_flag,'N') = 'N';
783 l_proc varchar2(256) := g_package||'override_approver';
784 l_result boolean ;
785 l_role_user_id number;
786 begin
787 hr_utility.set_location('Entering for rc'||p_routing_category_id||l_proc,10);
788 for i in c1 loop
789 if p_member_cd ='R' then
790 if p_role_id = i.override_role_id then
791 hr_utility.set_location('RL and role=override_role '||l_proc,20);
792 if i.override_user_id is null then
793 hr_utility.set_location('override approver was role alone '||l_proc,22);
794 l_result := TRUE;
795 else
796 if i.override_user_id = p_user_id then
797 hr_utility.set_location('user=override_user '||l_proc,24);
798 l_result := TRUE;
799 else
800 hr_utility.set_location('user<>override_user '||l_proc,26);
801 end if;
802 end if;
803 else
804 hr_utility.set_location('RL and role does not match '||l_proc,27);
805 end if;
806 elsif p_member_cd ='P' and p_position_id = i.override_position_id then
807 hr_utility.set_location('PS and position=override_position '||l_proc,30);
808 l_result := TRUE;
809 elsif p_member_cd ='S' and p_assignment_id = i.override_assignment_id then
810 hr_utility.set_location('Sup and assignment=override_assignment '||l_proc,40);
811 l_result := TRUE;
812 end if;
813 if l_result is null then
814 hr_utility.set_location('no match of override '||l_proc,50);
815 l_result := FALSE;
816 end if;
817 end loop;
818 hr_utility.set_location('Exiting '||l_proc,100);
819 return l_result;
820 end override_approver;
821
822 procedure old_approver_valid(p_transaction_category_id in number,
823 p_transaction_id in number,
824 p_transaction_status in varchar2,
825 p_old_approver_valid out nocopy varchar2 ) is
826 l_max_routing_history_id number(15);
827 l_routing_category_id number(15);
828 l_rh_routing_category_id number(15);
829 l_position_id number(15);
830 l_assignment_id number(15);
831 l_user_id number(15);
832 l_role_id number(15);
833 l_role_name varchar2(200);
834 l_user_name varchar2(200);
835 l_member_id number(15);
836 l_member_cd varchar2(30);
837 l_member_flag boolean ;
838 l_routing_list_id number(15);
839 l_pos_str_id number(15);
840 l_from_clause varchar2(2000);
841 l_applicable_flag boolean;
842 l_status_flag number;
843 l_error_flag boolean := FALSE;
844 l_can_approve boolean;
845 l_override_approver boolean;
846 l_value_date date := sysdate;
847 l_setup varchar2(30);
848 l_range_name varchar2(100);
849 l_proc varchar2(256) := g_package||'old_approver_valid' ;
850 cursor c1 is select forwarded_by_position_id,forwarded_by_assignment_id,forwarded_by_user_id,forwarded_by_role_id,
851 forwarded_by_member_id,routing_category_id
852 from pqh_routing_history
853 where routing_history_id = l_max_routing_history_id;
854 cursor c2 is select rlm.role_id
855 from pqh_routing_categories rc,pqh_routing_list_members rlm
856 where rc.routing_category_id = l_rh_routing_category_id
857 and rc.routing_list_id = rlm.routing_list_id
858 and rlm.routing_list_member_id = l_member_id
859 and nvl(rc.enable_flag,'X') = 'Y'
860 and nvl(rlm.enable_flag,'X') = 'Y'
861 and nvl(rc.delete_flag,'X') <> 'Y';
862 begin
863 p_old_approver_valid := 'N' ;
864 if p_transaction_id is null then
865 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
866 hr_utility.raise_error;
867 elsif p_transaction_category_id is null then
868 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
869 hr_utility.raise_error;
870 elsif p_transaction_status <> 'APPROVED' then
871 l_error_flag := TRUE;
872 end if;
873 if l_error_flag = FALSE then
874 begin
875 select max(routing_history_id)
876 into l_max_routing_history_id
877 from pqh_routing_history
878 where transaction_category_id = p_transaction_category_id
879 and transaction_id = p_transaction_id
880 and approval_cd ='APPROVED' ;
881 hr_utility.set_location('max RH id is '||l_max_routing_history_id||l_proc,10);
882 if nvl(l_max_routing_history_id,0) >0 then
883 open c1;
884 fetch c1 into l_position_id,l_assignment_id,l_user_id,l_role_id,l_member_id,l_rh_routing_category_id;
885 if c1 %notfound then
886 hr_utility.set_location('error in getting routing history'||l_proc,20);
887 close c1;
888 l_status_flag := 1;
889 hr_utility.set_message(8302,'PQH_RH_FETCH_FAILED');
890 hr_utility.raise_error;
891 else
892 close c1;
893 hr_utility.set_location('routing history details pulled'||l_proc,25);
894 l_status_flag := 0;
895 end if;
896 else
897 hr_utility.set_location('routing history does not exist'||l_proc,27);
898 l_status_flag := 11;
899 end if;
900 end;
901 if l_status_flag = 0 then
902 prepare_from_clause(p_tran_cat_id => p_transaction_category_id,
903 p_trans_id => p_transaction_id,
904 p_from_clause => l_from_clause);
905 if l_from_clause is null then
906 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
907 hr_utility.raise_error;
908 hr_utility.set_location('From_clause not there '||l_proc,30);
909 else
910 l_setup := tran_setup(p_transaction_category_id);
911 hr_utility.set_location('tran_cat setup is '||l_setup||l_proc,66);
912 if nvl(l_setup,'XYZ') = 'STANDARD' then
913 list_range_check(p_tran_cat_id => p_transaction_category_id,
914 p_member_cd => l_member_cd,
915 p_used_for => 'O',
916 p_routing_list_id => l_routing_list_id,
917 p_pos_str_id => l_pos_str_id,
918 p_routing_category_id => l_routing_category_id,
919 p_status_flag => l_status_flag);
920 elsif nvl(l_setup,'XYZ') = 'ADVANCED' then
921 hr_utility.set_location('From_clause selected '||l_proc,40);
922 list_range_check(p_tran_cat_id => p_transaction_category_id,
923 p_trans_id => p_transaction_id,
924 p_from_clause => l_from_clause,
925 p_used_for => 'O',
926 p_member_cd => l_member_cd,
927 p_routing_list_id => l_routing_list_id,
928 p_pos_str_id => l_pos_str_id,
929 p_routing_category_id => l_routing_category_id,
930 p_range_name => l_range_name,
931 p_status_flag => l_status_flag);
932 else
933 l_status_flag := 10;
934 end if;
935 if l_status_flag = 0 then
936 hr_utility.set_location('CATg selected'||to_char(l_routing_category_id)||l_range_name||l_proc,50);
937 if l_routing_category_id <> l_rh_routing_category_id then
938 hr_utility.set_location('selected routing catg was used for approval'||l_proc,52);
939 else
940 hr_utility.set_location('catg change has happened'||l_proc,54);
941 hr_utility.set_location('catg used for approval'||l_rh_routing_category_id||l_proc,56);
942 end if;
943 else
944 hr_utility.set_location('errors in selecting routing_category'||l_proc,60);
945 end if;
946 end if;
947 end if;
948 if l_status_flag = 0 then
949 if l_member_cd = 'R' then
950 hr_utility.set_location('Routing category is RL '||l_proc,70);
951 -- check wether this role-user combination exist in the selected routing list
952 rlm_user_seq(p_routing_list_id => l_routing_list_id,
953 p_role_id => l_role_id,
954 p_role_name => l_role_name,
955 p_user_id => l_user_id,
956 p_user_name => l_user_name,
957 p_member_id => l_member_id,
958 p_member_flag => l_member_flag);
959 if l_member_flag = TRUE then
960 hr_utility.set_location('got member for the new RL'||l_member_id||l_proc,100);
961 rl_member_applicable(p_tran_cat_id => p_transaction_category_id,
962 p_from_clause => l_from_clause,
963 p_member_id => l_member_id ,
964 p_routing_category_id => l_routing_category_id,
965 p_applicable_flag => l_applicable_flag,
966 p_status_flag => l_status_flag,
967 p_can_approve => l_can_approve );
968 else
969 hr_utility.set_location('current user not RL member '||l_proc,100);
970 hr_utility.set_location('checking override approver '||l_proc,102);
971 l_override_approver := override_approver(p_member_cd => l_member_cd,
972 p_routing_category_id => l_routing_category_id,
973 p_assignment_id => l_assignment_id,
974 p_role_id => l_role_id,
975 p_user_id => l_user_id,
976 p_position_id => l_position_id);
977 if l_override_approver then
978 l_can_approve := TRUE;
979 hr_utility.set_location('member is defined as override approver '||l_proc,129);
980 else
981 l_can_approve := FALSE;
982 l_status_flag := 11;
983 hr_utility.set_location('member is not defined as override approver '||l_proc,129);
984 end if;
985 end if;
986 elsif l_member_cd ='P' then
987 hr_utility.set_location('Routing category is PS '||l_proc,110);
988 ps_element_applicable(p_tran_cat_id => p_transaction_category_id,
989 p_from_clause => l_from_clause,
990 p_position_id => l_position_id,
991 p_routing_category_id => l_routing_category_id,
992 p_value_date => l_value_date,
993 p_applicable_flag => l_applicable_flag,
994 p_status_flag => l_status_flag,
995 p_can_approve => l_can_approve );
996 elsif l_member_cd ='S' then
997 hr_utility.set_location('Routing category is SH '||l_proc,120);
998 assignment_applicable(p_tran_cat_id => p_transaction_category_id,
999 p_from_clause => l_from_clause,
1000 p_assignment_id => l_assignment_id,
1001 p_routing_category_id => l_routing_category_id,
1002 p_value_date => l_value_date,
1003 p_applicable_flag => l_applicable_flag,
1004 p_status_flag => l_status_flag,
1005 p_can_approve => l_can_approve );
1006 else
1007 hr_utility.set_location('invalid member_cd '||l_proc,130);
1008 end if;
1009 if l_status_flag = 0 then
1010 if l_can_approve = TRUE then
1011 p_old_approver_valid := 'Y' ;
1012 hr_utility.set_location('old approver remains a valid approver'||l_proc,132);
1013 else
1014 p_old_approver_valid := 'N' ;
1015 hr_utility.set_location('old approver no longer approver'||l_proc,134);
1016 end if;
1017 else
1018 hr_utility.set_location('applicable check failed '||l_proc,140);
1019 end if;
1020 else
1021 hr_utility.set_location('routing category fetch failed '||l_proc,160);
1022 if l_status_flag = 11 then
1023 hr_utility.set_location('there is no routing history '||l_proc,160);
1024 p_old_approver_valid := 'Y' ;
1025 end if;
1026 end if;
1027 else
1028 hr_utility.set_location('transaction is not already approved '||l_proc,170);
1029 end if;
1030 hr_utility.set_location('exiting '||l_proc,200);
1031 exception when others then
1032 p_old_approver_valid := null;
1033 raise;
1034 end old_approver_valid;
1035 procedure next_applicable(p_member_cd in pqh_transaction_categories.member_cd%type,
1036 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1037 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1038 p_trans_id in pqh_routing_history.transaction_id%type,
1039 p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
1040 p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
1041 p_routing_list_id in pqh_routing_categories.routing_list_id%type,
1042 p_cur_position_id in pqh_position_transactions.position_id%type,
1043 p_pos_str_ver_id in per_pos_structure_elements.pos_structure_version_id%type,
1044 p_next_position_id out nocopy pqh_position_transactions.position_id%type,
1045 p_next_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1046 p_next_role_id out nocopy number,
1047 p_next_user_id out nocopy number,
1048 p_next_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1049 p_status_flag out nocopy number)
1050 is
1051 l_applicable_flag boolean := FALSE;
1052 l_proc varchar2(256) := g_package||'next_applicable' ;
1053 begin
1054 hr_utility.set_location('entering '||l_proc,10);
1055 if p_member_cd is null then
1056 hr_utility.set_message(8302,'PQH_MEMBER_CD_REQD');
1057 hr_utility.raise_error;
1058 elsif p_member_cd ='R' and p_routing_list_id is null then
1059 hr_utility.set_message(8302,'PQH_ROUTING_LIST_REQD');
1060 hr_utility.raise_error;
1061 elsif p_member_cd ='R' and p_cur_member_id is null then
1062 hr_utility.set_message(8302,'PQH_CURRENT_MEMBER_REQD');
1063 hr_utility.raise_error;
1064 elsif p_member_cd ='P' and p_cur_position_id is null then
1065 hr_utility.set_message(8302,'PQH_CUR_POS_REQD');
1066 hr_utility.raise_error;
1067 elsif p_member_cd ='S' and p_cur_assignment_id is null then
1068 hr_utility.set_message(8302,'PQH_CUR_ASG_REQD');
1069 hr_utility.raise_error;
1070 elsif p_member_cd ='P' and p_pos_str_ver_id is null then
1071 hr_utility.set_message(8302,'PQH_POS_STR_VER_REQD');
1072 hr_utility.raise_error;
1073 elsif p_routing_category_id is null then
1074 hr_utility.set_message(8302,'PQH_ROUTING_CATEGORY_REQD');
1075 hr_utility.raise_error;
1076 elsif p_tran_cat_id is null then
1077 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
1078 hr_utility.raise_error;
1079 elsif p_trans_id is null then
1080 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
1081 hr_utility.raise_error;
1082 end if;
1083 hr_utility.set_location('transaction_id is '||p_trans_id||l_proc,51);
1084 hr_utility.set_location('trans_cat_id is '||p_tran_cat_id||l_proc,52);
1085 if p_member_cd = 'R' then
1086 hr_utility.set_location('calling next_member '||l_proc,20);
1087 next_applicable_member(p_routing_category_id => p_routing_category_id,
1088 p_tran_cat_id => p_tran_cat_id,
1089 p_trans_id => p_trans_id,
1090 p_cur_member_id => p_cur_member_id,
1091 p_routing_list_id => p_routing_list_id,
1092 p_used_for => 'N',
1093 p_member_id => p_next_member_id,
1094 p_role_id => p_next_role_id,
1095 p_user_id => p_next_user_id,
1096 p_status_flag => p_status_flag,
1097 p_applicable_flag => l_applicable_flag);
1098 elsif p_member_cd ='S' then
1099 hr_utility.set_location('calling next_assignment '||l_proc,30);
1100 next_applicable_assignment(p_routing_category_id => p_routing_category_id,
1101 p_tran_cat_id => p_tran_cat_id,
1102 p_trans_id => p_trans_id,
1103 p_cur_assignment_id => p_cur_assignment_id,
1104 p_assignment_id => p_next_assignment_id,
1105 p_used_for => 'N',
1106 p_status_flag => p_status_flag,
1107 p_applicable_flag => l_applicable_flag);
1108 elsif p_member_cd ='P' then
1109 hr_utility.set_location('calling next_position '||l_proc,40);
1110 next_applicable_position(p_routing_category_id => p_routing_category_id,
1111 p_tran_cat_id => p_tran_cat_id,
1112 p_trans_id => p_trans_id,
1113 p_cur_position_id => p_cur_position_id,
1114 p_used_for => 'N',
1115 p_pos_str_ver_id => p_pos_str_ver_id,
1116 p_position_id => p_next_position_id,
1117 p_status_flag => p_status_flag,
1118 p_applicable_flag => l_applicable_flag);
1119 else
1120 hr_utility.set_message(8302,'PQH_INVALID_MEMBER_CD');
1121 hr_utility.raise_error;
1122 end if;
1123 list_rout_crit;
1124 hr_utility.set_location('exiting '||l_proc,10000);
1125 exception when others then
1126 p_next_position_id := null;
1127 p_next_member_id := null;
1128 p_next_role_id := null;
1129 p_next_user_id := null;
1130 p_next_assignment_id := null;
1131 p_status_flag := null;
1132 raise;
1133 end next_applicable;
1134 procedure next_applicable_assignment(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1135 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1136 p_trans_id in pqh_routing_history.transaction_id%type,
1137 p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
1138 p_used_for in varchar2,
1139 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1140 p_status_flag out nocopy number,
1141 p_applicable_flag out nocopy boolean)
1142 is
1143 l_value_date date := trunc(sysdate);
1144 l_from_clause varchar2(2000) ;
1145 l_can_approve boolean := FALSE;
1146 l_cur_assignment_id per_all_assignments_f.assignment_id%type;
1147 l_proc varchar2(256) := g_package||'next_applicable_assignment' ;
1148 begin
1149 p_applicable_flag := FALSE;
1150 p_status_flag := 0;
1151 l_cur_assignment_id := p_cur_assignment_id;
1152 hr_utility.set_location('entering '||l_proc,10);
1153 if p_routing_category_id is null then
1154 hr_utility.set_message(8302,'PQH_ROUTING_CATEGORY_REQD');
1155 hr_utility.raise_error;
1156 elsif p_trans_id is null then
1157 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
1158 hr_utility.raise_error;
1159 elsif p_tran_cat_id is null then
1160 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
1161 hr_utility.raise_error;
1162 elsif p_cur_assignment_id is null then
1163 hr_utility.set_message(8302,'PQH_CUR_ASG_REQD');
1164 hr_utility.raise_error;
1165 end if;
1166 hr_utility.set_location('reqd data there '||l_proc,20);
1167 l_cur_assignment_id := p_cur_assignment_id;
1168 p_status_flag := 0;
1169 prepare_from_clause(p_tran_cat_id => p_tran_cat_id,
1170 p_trans_id => p_trans_id,
1171 p_from_clause => l_from_clause);
1172 if l_from_clause is null then
1173 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
1174 hr_utility.raise_error;
1175 else
1176 hr_utility.set_location('from clause selected '||l_proc,30);
1177 while p_status_flag = 0 and p_applicable_flag = FALSE loop
1178 su_next_user(p_cur_assignment_id => l_cur_assignment_id,
1179 p_value_date => l_value_date,
1180 p_assignment_id => p_assignment_id,
1181 p_status_flag => p_status_flag) ;
1182 if p_status_flag = 0 then
1183 hr_utility.set_location('next assignment found check appli'||to_char(p_assignment_id)||l_proc,40);
1184 assignment_applicable(p_tran_cat_id => p_tran_cat_id,
1185 p_from_clause => l_from_clause,
1186 p_assignment_id => p_assignment_id,
1187 p_routing_category_id => p_routing_category_id,
1188 p_used_for => p_used_for,
1189 p_value_date => l_value_date,
1190 p_applicable_flag => p_applicable_flag,
1191 p_status_flag => p_status_flag,
1192 p_can_approve => l_can_approve );
1193 if p_applicable_flag = FALSE then
1194 hr_utility.set_location('next assignment unapplicable '||to_char(p_assignment_id)||l_proc,50);
1195 l_cur_assignment_id := p_assignment_id;
1196 else
1197 hr_utility.set_location('assignment applicable '||to_char(p_assignment_id)||l_proc,60);
1198 end if;
1199 else
1200 hr_utility.set_location('error getting next_assignment '||to_char(p_status_flag)||l_proc,70);
1201 end if;
1202 end loop;
1203 end if;
1204 hr_utility.set_location('exiting '||l_proc,10000);
1205 exception when others then
1206 p_assignment_id := null;
1207 p_status_flag := null;
1208 p_applicable_flag := null;
1209
1210 raise;
1211 end next_applicable_assignment;
1212
1213 procedure next_applicable_member(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1214 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1215 p_trans_id in pqh_routing_history.transaction_id%type,
1216 p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
1217 p_routing_list_id in pqh_routing_categories.routing_list_id%type,
1218 p_used_for in varchar2,
1219 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1220 p_role_id out nocopy number,
1221 p_user_id out nocopy number,
1222 p_status_flag out nocopy number,
1223 p_applicable_flag out nocopy boolean)
1224 is
1225 l_value_date date := trunc(sysdate);
1226 l_from_clause varchar2(2000) ;
1227 l_can_approve boolean := FALSE;
1228 l_error_flag boolean := FALSE;
1229 l_member_role_id pqh_roles.role_id%type;
1230 l_member_user_id fnd_user.user_id%type;
1231 l_cur_member_id number;
1232 l_proc varchar2(256) := g_package||'next_applicable_member' ;
1233 begin
1234 p_applicable_flag := FALSE;
1235 p_status_flag := 0;
1236 hr_utility.set_location('entering '||l_proc,10);
1237 if p_routing_category_id is null then
1238 hr_utility.set_message(8302,'PQH_ROUTING_CATEGORY_REQD');
1239 hr_utility.raise_error;
1240 elsif p_trans_id is null then
1241 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
1242 hr_utility.raise_error;
1243 elsif p_tran_cat_id is null then
1244 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
1245 hr_utility.raise_error;
1246 elsif p_cur_member_id is null then
1247 hr_utility.set_message(8302,'PQH_CURRENT_MEMBER_REQD');
1248 hr_utility.raise_error;
1249 elsif p_routing_list_id is null then
1250 hr_utility.set_message(8302,'PQH_ROUTING_LIST_REQD');
1251 hr_utility.raise_error;
1252 else
1253 l_error_flag := FALSE;
1254 end if;
1255 if l_error_flag = FALSE then
1256 hr_utility.set_location('reqd data there '||l_proc,20);
1257 p_status_flag := 0;
1258 prepare_from_clause(p_tran_cat_id => p_tran_cat_id,
1259 p_trans_id => p_trans_id,
1260 p_from_clause => l_from_clause);
1261 if l_from_clause is null then
1262 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
1263 hr_utility.raise_error;
1264 else
1265 hr_utility.set_location('from clause selected '||l_proc,30);
1266 l_cur_member_id := p_cur_member_id;
1267 while p_status_flag = 0 and p_applicable_flag = FALSE loop
1268 rl_next_user(p_routing_list_id => p_routing_list_id,
1269 p_cur_member_id => l_cur_member_id,
1270 p_member_id => p_member_id,
1271 p_role_id => p_role_id,
1272 p_user_id => p_user_id,
1273 p_status_flag => p_status_flag);
1274 if p_status_flag = 0 then
1275 hr_utility.set_location('next member found check appli'||to_char(p_member_id)||l_proc,40);
1276 rl_member_applicable(p_tran_cat_id => p_tran_cat_id,
1277 p_from_clause => l_from_clause,
1278 p_member_id => p_member_id ,
1279 p_routing_category_id => p_routing_category_id,
1280 p_used_for => p_used_for,
1281 p_applicable_flag => p_applicable_flag,
1282 p_status_flag => p_status_flag,
1283 p_can_approve => l_can_approve );
1284 if p_applicable_flag = FALSE then
1285 hr_utility.set_location('next member unapplicable '||to_char(p_member_id)||l_proc,50);
1286 l_cur_member_id := p_member_id ;
1287 else
1288 hr_utility.set_location('member applicable '||to_char(p_member_id )||l_proc,60);
1289 end if;
1290 elsif p_status_flag = 1 then
1291 hr_utility.set_location('last_member, status returned:'||to_char(p_status_flag)||l_proc,70);
1292 else
1293 hr_utility.set_location('error getting next_member '||to_char(p_status_flag)||l_proc,70);
1294 end if;
1295 end loop;
1296 end if;
1297 else
1298 hr_utility.set_location('mandatory data missing'||l_proc,80);
1299 hr_utility.set_message(8302,'PQH_MANDATORY_DATA_MISS');
1300 end if;
1301 hr_utility.set_location('exiting '||l_proc,10000);
1302 exception when others then
1303 p_member_id := null;
1304 p_role_id := null;
1305 p_user_id := null;
1306 p_status_flag := null;
1307 p_applicable_flag := null;
1308 raise;
1309 end next_applicable_member;
1310
1311 procedure next_applicable_position(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1312 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1313 p_trans_id in pqh_routing_history.transaction_id%type,
1314 p_cur_position_id in pqh_position_transactions.position_id%type,
1315 p_pos_str_ver_id in per_pos_structure_elements.pos_structure_version_id%type,
1316 p_used_for in varchar2,
1317 p_position_id out nocopy pqh_position_transactions.position_id%type,
1318 p_status_flag out nocopy number,
1319 p_applicable_flag out nocopy boolean)
1320 is
1321 l_value_date date := trunc(sysdate);
1322 l_from_clause varchar2(2000) ;
1323 l_can_approve boolean := FALSE;
1324 l_error_flag boolean := FALSE;
1325 l_pos_str_id pqh_routing_categories.position_structure_id%type;
1326 l_cur_position_id pqh_position_transactions.position_id%type;
1327 l_proc varchar2(256) := g_package||'next_applicable_position' ;
1328 begin
1329 p_applicable_flag := FALSE;
1330 p_status_flag := 0;
1331 l_cur_position_id := p_cur_position_id;
1332 hr_utility.set_location('entering '||l_proc,10);
1333 if p_routing_category_id is null then
1334 hr_utility.set_message(8302,'PQH_ROUTING_CATEGORY_REQD');
1335 hr_utility.raise_error;
1336 elsif p_trans_id is null then
1337 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
1338 hr_utility.raise_error;
1339 elsif p_tran_cat_id is null then
1340 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
1341 hr_utility.raise_error;
1342 elsif p_cur_position_id is null then
1343 hr_utility.set_message(8302,'PQH_CUR_POS_REQD');
1344 hr_utility.raise_error;
1345 elsif p_pos_str_ver_id is null then
1346 hr_utility.set_message(8302,'PQH_POS_STR_VER_REQD');
1347 hr_utility.raise_error;
1348 else
1349 l_error_flag := FALSE;
1350 end if;
1351 if l_error_flag = FALSE then
1352 hr_utility.set_location('reqd data there '||l_proc,20);
1353 p_status_flag := 0;
1354 prepare_from_clause(p_tran_cat_id => p_tran_cat_id,
1355 p_trans_id => p_trans_id,
1356 p_from_clause => l_from_clause);
1357 if l_from_clause is null then
1358 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
1359 hr_utility.raise_error;
1360 else
1361 hr_utility.set_location('from clause selected '||l_proc,30);
1362 /* Changed while loop condition to check for next applicable
1363 position if eliminated position encountered-Bug#2295241 */
1364 while (p_status_flag = 0 or p_status_flag = 8) and p_applicable_flag = FALSE loop
1365 ph_next_user(p_cur_position_id => l_cur_position_id,
1366 p_pos_str_ver_id => p_pos_str_ver_id,
1367 p_position_id => p_position_id,
1368 p_status_flag => p_status_flag) ;
1369 if p_status_flag = 0 then
1370 hr_utility.set_location('next position found check appli'||to_char(p_position_id)||l_proc,40);
1371 ps_element_applicable(p_tran_cat_id => p_tran_cat_id,
1372 p_from_clause => l_from_clause,
1373 p_position_id => p_position_id,
1374 p_routing_category_id => p_routing_category_id,
1375 p_value_date => l_value_date,
1376 p_used_for => p_used_for,
1377 p_applicable_flag => p_applicable_flag,
1378 p_status_flag => p_status_flag,
1379 p_can_approve => l_can_approve );
1380 if p_applicable_flag = FALSE then
1381 hr_utility.set_location('next position unapplicable '||to_char(p_position_id)||l_proc,50);
1382 l_cur_position_id := p_position_id;
1383 else
1384 hr_utility.set_location('position applicable '||to_char(p_position_id)||l_proc,60);
1385 end if;
1386 elsif p_status_flag = 8 then
1387 /* Added for Bug#2295241 */
1388 hr_utility.set_location('next position is eliminated:'||to_char(p_position_id)||l_proc,65);
1389 l_cur_position_id := p_position_id;
1390 /* End Bug#2295241 */
1391 else
1392 hr_utility.set_location('error getting next_pos '||to_char(p_status_flag)||l_proc,70);
1393 end if;
1394 end loop;
1395 end if;
1396 else
1397 hr_utility.set_location('mandatory data missing'||l_proc,80);
1398 hr_utility.set_message(8302,'PQH_MANDATORY_DATA_MISS');
1399 end if;
1400 hr_utility.set_location('exiting '||l_proc,10000);
1401 exception when others then
1402 p_position_id := null;
1403 p_status_flag := null;
1404 p_applicable_flag := null;
1405 raise;
1406 end next_applicable_position;
1407
1408 procedure position_applicable(p_position_id in pqh_position_transactions.position_id%type,
1409 p_pos_str_ver_id in per_pos_structure_versions.pos_structure_version_id%type,
1410 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1411 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1412 p_trans_id in pqh_position_transactions.position_transaction_id%type,
1413 p_status_flag out nocopy number,
1414 p_can_approve out nocopy boolean,
1415 p_applicable_flag out nocopy boolean)
1416 as
1417 l_proc varchar2(256) := g_package||'position_applicable';
1418 l_from_clause varchar2(2000) ;
1419 l_value_date date := trunc(sysdate);
1420 begin
1421 hr_utility.set_location('entering '||l_proc,10);
1422 prepare_from_clause(p_tran_cat_id => p_tran_cat_id,
1423 p_trans_id => p_trans_id,
1424 p_from_clause => l_from_clause);
1425 ps_element_applicable(p_tran_cat_id => p_tran_cat_id,
1426 p_from_clause => l_from_clause,
1427 p_position_id => p_position_id,
1428 p_routing_category_id => p_routing_category_id,
1429 p_value_date => l_value_date,
1430 p_applicable_flag => p_applicable_flag,
1431 p_status_flag => p_status_flag,
1432 p_can_approve => p_can_approve );
1433 hr_utility.set_location('Exiting '||l_proc,10000);
1434 exception when others then
1435 p_status_flag := null;
1436 p_can_approve := null;
1437 p_applicable_flag := null;
1438 raise;
1439 end;
1440
1441 procedure position_occupied(p_position_id in pqh_position_transactions.position_id%type,
1442 p_value_date in date,
1443 p_applicable_flag out nocopy boolean)
1444 as
1445 cursor c1 is select person_id
1446 from per_all_assignments_f
1447 where position_id = p_position_id
1448 and primary_flag = 'Y'
1449 and p_value_date between effective_start_date and effective_end_date;
1450 l_person_id fnd_user.employee_id%type;
1451 l_proc varchar2(256) := g_package||'Position_occupied';
1452 l_error_flag boolean := FALSE ;
1453 begin
1454 hr_utility.set_location('Entering '||l_proc,10);
1455 p_applicable_flag := FALSE;
1456 if p_position_id is null then
1457 hr_utility.set_location('Position id reqd '||l_proc,20);
1458 hr_utility.set_message(8302,'PQH_POSITION_REQD');
1459 hr_utility.raise_error;
1460 elsif p_value_date is null then
1461 hr_utility.set_location('date reqd'||l_proc,30);
1462 hr_utility.set_message(8302,'PQH_POS_EFF_DATE_REQD');
1463 hr_utility.raise_error;
1464 else
1465 l_error_flag := FALSE;
1466 end if;
1467 -- the idea of this procedure is to find out atleast one person occupying the selected position
1468 -- to have a user.
1469 if l_error_flag = FALSE then
1470 open c1;
1471 fetch c1 into l_person_id;
1472 loop
1473 if c1%notfound then
1474 hr_utility.set_location('EOL '||l_proc,40);
1475 p_applicable_flag := FALSE;
1476 exit;
1477 else
1478 hr_utility.set_location('checking person has user for person '||l_person_id||l_proc,45);
1479 person_has_user(p_person_id => l_person_id,
1480 p_value_date => p_value_date,
1481 p_applicable_flag => p_applicable_flag );
1482 if p_applicable_flag = TRUE then
1483 hr_utility.set_location('Person has user'||l_proc,50);
1484 exit;
1485 else
1486 hr_utility.set_location('Person, no user'||l_proc,60);
1487 fetch c1 into l_person_id;
1488 end if;
1489 end if;
1490 end loop;
1491 else
1492 p_applicable_flag := FALSE;
1493 hr_utility.set_location('Mandatory data missing '||l_proc,70);
1494 hr_utility.set_message(8302,'PQH_MANDATORY_DATA_MISS');
1495 end if;
1496 hr_utility.set_location('Exiting '||l_proc,10000);
1497 exception when others then
1498 p_applicable_flag := null;
1499 raise;
1500 end position_occupied;
1501
1502 procedure person_has_user(p_person_id in fnd_user.employee_id%type,
1503 p_value_date in date,
1504 p_applicable_flag out nocopy boolean)
1505 as
1506 l_user_id fnd_user.user_id%type;
1507 cursor c1 is select user_id
1508 from fnd_user
1509 where employee_id = p_person_id
1510 and p_value_date between nvl(start_date,p_value_date)
1511 and nvl(end_date,p_value_date);
1512 l_proc varchar2(256) := g_package||'person_has_user';
1513 begin
1514 hr_utility.set_location('Entering '||l_proc,10);
1515 p_applicable_flag := FALSE;
1516 if p_person_id is null then
1517 hr_utility.set_location('Person id reqd '||l_proc,20);
1518 hr_utility.set_message(8302,'PQH_PERSON_ID_REQD');
1519 hr_utility.raise_error;
1520 elsif p_value_date is null then
1521 hr_utility.set_location('Date reqd'||l_proc,30);
1522 hr_utility.set_message(8302,'PQH_PER_EFF_DATE_REQD');
1523 hr_utility.raise_error;
1524 end if;
1525 open c1;
1526 fetch c1 into l_user_id;
1527 if c1%notfound then
1528 p_applicable_flag := FALSE;
1529 hr_utility.set_location('No user for person '||to_char(p_person_id)||l_proc,40);
1530 else
1531 p_applicable_flag := TRUE;
1532 hr_utility.set_location('user exists for person '||to_char(p_person_id)||l_proc,50);
1533 end if;
1534 close c1;
1535 hr_utility.set_location('Exiting '||l_proc,10000);
1536 exception when others then
1537 p_applicable_flag := null;
1538 raise;
1539 end person_has_user;
1540
1541 procedure applicable_next_user(p_trans_id in pqh_routing_history.transaction_id%type,
1542 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1543 p_cur_user_id in out nocopy fnd_user.user_id%type,
1544 p_cur_user_name in out nocopy fnd_user.user_name%type,
1545 p_user_active_role_id in out nocopy pqh_roles.role_id%type,
1546 p_user_active_role_name in out nocopy pqh_roles.role_name%type,
1547 p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
1548 p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
1549 p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
1550 p_routing_history_id out nocopy pqh_routing_history.routing_history_id%type,
1551 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1552 p_person_id out nocopy fnd_user.employee_id%type,
1553 p_old_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1554 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
1555 p_old_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
1556 p_member_role_id out nocopy pqh_roles.role_id%type,
1557 p_member_user_id out nocopy fnd_user.user_id%type,
1558 p_cur_person_id out nocopy fnd_user.employee_id%type,
1559 p_cur_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1560 p_position_id out nocopy pqh_position_transactions.position_id%type,
1561 p_old_position_id out nocopy pqh_position_transactions.position_id%type,
1562 p_cur_position_id out nocopy pqh_position_transactions.position_id%type,
1563 p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1564 p_old_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1565 p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
1566 p_old_pos_str_ver_id out nocopy pqh_routing_categories.position_structure_id%type,
1567 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1568 p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1569 p_old_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1570 p_status_flag out nocopy number,
1571 p_history_flag out nocopy boolean,
1572 p_range_name out nocopy pqh_attribute_ranges.range_name%type,
1573 p_can_approve out nocopy boolean)
1574 as
1575 l_from_clause pqh_table_route.from_clause%type;
1576 l_applicable_flag boolean;
1577 l_value_date date := trunc(sysdate);
1578 l_cur_user_id fnd_user.user_id%type := p_cur_user_id;
1579 l_cur_user_name fnd_user.user_name%type := p_cur_user_name;
1580 l_user_active_role_id pqh_roles.role_id%type := p_user_active_role_id;
1581 l_user_active_role_name pqh_roles.role_name%type := p_user_active_role_name;
1582
1583 -- variable used to hold the value of approve_flag for thenext user
1584 l_can_approve boolean;
1585
1586 l_error_flag boolean := FALSE;
1587 l_old_user_id number;
1588 l_old_role_id number;
1589 l_setup varchar2(30);
1590 l_proc varchar2(256) := g_package||'applicable_next_user';
1591 begin
1592 hr_utility.set_location('Entering '||l_proc,10);
1593 if p_trans_id is null then
1594 hr_utility.set_location('Transaction id reqd '||l_proc,20);
1595 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
1596 hr_utility.raise_error;
1597 elsif p_tran_cat_id is null then
1598 hr_utility.set_location('Transaction category reqd '||l_proc,30);
1599 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
1600 hr_utility.raise_error;
1601 elsif p_cur_user_id is null and p_cur_user_name is null then
1602 hr_utility.set_location('user id or user name reqd'||l_proc,40);
1603 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
1604 hr_utility.raise_error;
1605 elsif p_user_active_role_id is null and p_user_active_role_name is null then
1606 hr_utility.set_location('user role id or role name reqd'||l_proc,50);
1607 hr_utility.set_message(8302,'PQH_ROLEID_OR_NAME_REQD');
1608 hr_utility.raise_error;
1609 end if;
1610 hr_utility.set_location('transaction_id is '||p_trans_id||l_proc,51);
1611 hr_utility.set_location('trans_cat_id is '||p_tran_cat_id||l_proc,52);
1612 hr_utility.set_location('user_role_id is '||p_user_active_role_id||l_proc,53);
1613 hr_utility.set_location('user_role_name is '||p_user_active_role_name||l_proc,54);
1614 hr_utility.set_location('user_id is '||p_cur_user_id||l_proc,55);
1615 hr_utility.set_location('user_name is '||p_cur_user_name||l_proc,56);
1616 prepare_from_clause(p_tran_cat_id => p_tran_cat_id,
1617 p_trans_id => p_trans_id,
1618 p_from_clause => l_from_clause);
1619 if l_from_clause is null then
1620 hr_utility.set_location('From_clause not there '||l_proc,57);
1621 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
1622 hr_utility.raise_error;
1623 else
1624 hr_utility.set_location('From_clause selected '||l_proc,60);
1625 routing_current(p_tran_cat_id => p_tran_cat_id,
1626 p_trans_id => p_trans_id,
1627 p_history_flag => p_history_flag,
1628 p_old_member_cd => p_old_member_cd,
1629 p_position_id => p_old_position_id,
1630 p_member_id => p_old_member_id,
1631 p_role_id => l_old_role_id,
1632 p_user_id => l_old_user_id,
1633 p_assignment_id => p_old_assignment_id,
1634 p_pos_str_ver_id => p_old_pos_str_ver_id,
1635 p_routing_list_id => p_old_routing_list_id,
1636 p_routing_history_id => p_routing_history_id,
1637 p_status_flag => p_status_flag);
1638 if p_status_flag = 0 then
1639 hr_utility.set_location('no error in routing history'||l_proc,65);
1640 l_setup := tran_setup(p_tran_cat_id);
1641 hr_utility.set_location('tran_cat setup is '||l_setup||l_proc,66);
1642 if nvl(l_setup,'XYZ') = 'STANDARD' then
1643 list_range_check(p_tran_cat_id => p_tran_cat_id,
1644 p_member_cd => p_member_cd,
1645 p_used_for => 'L',
1646 p_routing_list_id => p_routing_list_id,
1647 p_pos_str_id => p_pos_str_id,
1648 p_routing_category_id => p_routing_category_id,
1649 p_status_flag => p_status_flag);
1650 elsif nvl(l_setup,'XYZ') = 'ADVANCED' then
1651 list_range_check(p_tran_cat_id => p_tran_cat_id,
1652 p_trans_id => p_trans_id,
1653 p_from_clause => l_from_clause,
1654 p_member_cd => p_member_cd,
1655 p_used_for => 'L',
1656 p_routing_list_id => p_routing_list_id,
1657 p_pos_str_id => p_pos_str_id,
1658 p_routing_category_id => p_routing_category_id,
1659 p_range_name => p_range_name,
1660 p_status_flag => p_status_flag);
1661 else
1662 p_status_flag := 10;
1663 end if;
1664 if p_status_flag = 0 then
1665 hr_utility.set_location('CATg selected'||to_char(p_routing_category_id)||p_range_name||l_proc,70);
1666 else
1667 hr_utility.set_location('errors in selecting routing_category'||l_proc,70);
1668 end if;
1669 else
1670 hr_utility.set_location('errors in selecing history '||l_proc,65);
1671 end if;
1672 end if;
1673 if p_status_flag = 0 then
1674 if p_member_cd = 'R' then
1675 hr_utility.set_location('Routing category is RL '||l_proc,80);
1676 rl_member_check(p_routing_list_id => p_routing_list_id,
1677 p_old_routing_list_id => p_old_routing_list_id,
1678 p_history_flag => p_history_flag,
1679 p_tran_cat_id => p_tran_cat_id,
1680 p_from_clause => l_from_clause,
1681 p_routing_category_id => p_routing_category_id,
1682 p_cur_member_id => p_cur_member_id,
1683 p_old_member_id => p_old_member_id,
1684 p_old_role_id => l_old_role_id ,
1685 p_old_user_id => l_old_user_id ,
1686 p_user_active_role_id => p_user_active_role_id,
1687 p_user_active_role_name => p_user_active_role_name,
1688 p_cur_user_id => p_cur_user_id,
1689 p_cur_user_name => p_cur_user_name,
1690 p_member_id => p_member_id,
1691 p_member_role_id => p_member_role_id,
1692 p_member_user_id => p_member_user_id,
1693 p_status_flag => p_status_flag,
1694 p_applicable_flag => l_applicable_flag,
1695 p_old_can_approve => p_can_approve,
1696 p_can_approve => l_can_approve) ;
1697 if p_status_flag <> 0 then
1698 hr_utility.set_location('error '||p_status_flag||l_proc,90);
1699 else
1700 hr_utility.set_location('procedure went fine'||l_proc,100);
1701 end if;
1702 elsif p_member_cd = 'P' then
1703 hr_utility.set_location('Routing category is PS'||l_proc,110);
1704 ps_element_check(p_history_flag => p_history_flag,
1705 p_value_date => l_value_date,
1706 p_tran_cat_id => p_tran_cat_id,
1707 p_from_clause => l_from_clause,
1708 p_routing_category_id => p_routing_category_id,
1709 p_old_position_id => p_old_position_id,
1710 p_pos_str_id => p_pos_str_id,
1711 p_pos_str_ver_id => p_pos_str_ver_id,
1712 p_cur_user_id => p_cur_user_id,
1713 p_cur_user_name => p_cur_user_name,
1714 p_cur_position_id => p_cur_position_id,
1715 p_cur_person_id => p_cur_person_id,
1716 p_cur_assignment_id => p_cur_assignment_id,
1717 p_old_pos_str_id => p_old_pos_str_id,
1718 p_position_id => p_position_id,
1719 p_status_flag => p_status_flag,
1720 p_old_can_approve => p_can_approve,
1721 p_can_approve => l_can_approve,
1722 p_applicable_flag => l_applicable_flag);
1723 if p_status_flag <> 0 then
1724 hr_utility.set_location('error in execution of '||l_proc,120);
1725 else
1726 hr_utility.set_location('fine'||l_proc,130);
1727 end if;
1728 elsif p_member_cd = 'S' then
1729 hr_utility.set_location('Routing category is S '||l_proc,140);
1730 assignment_check(p_history_flag => p_history_flag,
1731 p_tran_cat_id => p_tran_cat_id,
1732 p_from_clause => l_from_clause,
1733 p_routing_category_id => p_routing_category_id,
1734 p_old_assignment_id => p_old_assignment_id,
1735 p_value_date => l_value_date,
1736 p_cur_user_id => p_cur_user_id,
1737 p_cur_user_name => p_cur_user_name,
1738 p_cur_person_id => p_cur_person_id,
1739 p_assignment_id => p_assignment_id,
1740 p_person_id => p_person_id,
1741 p_status_flag => p_status_flag,
1742 p_cur_assignment_id => p_cur_assignment_id,
1743 p_old_can_approve => p_can_approve,
1744 p_can_approve => l_can_approve,
1745 p_applicable_flag => l_applicable_flag );
1746 if p_status_flag <> 0 then
1747 hr_utility.set_location('error in procedure '||l_proc,150);
1748 else
1749 hr_utility.set_location('The execution went fine'||l_proc,160);
1750 end if;
1751 else
1752 hr_utility.set_location('Invalid member_cd '||l_proc,170);
1753 end if;
1754 else
1755 hr_utility.set_location('error reported is '||to_char(p_status_flag)||l_proc,180);
1756 end if;
1757 list_rout_crit;
1758 hr_utility.set_location('Rout catg'||to_char(p_routing_category_id)||l_proc,200);
1759 hr_utility.set_location('Exiting '||l_proc,10000);
1760 exception when others then
1761 p_cur_user_id := l_cur_user_id;
1762 p_cur_user_name := l_cur_user_name;
1763 p_user_active_role_id := l_user_active_role_id;
1764 p_user_active_role_name := l_user_active_role_name;
1765 p_routing_category_id := null;
1766 p_member_cd := null;
1767 p_old_member_cd := null;
1768 p_routing_history_id := null;
1769 p_member_id := null;
1770 p_person_id := null;
1771 p_old_member_id := null;
1772 p_routing_list_id := null;
1773 p_old_routing_list_id := null;
1774 p_member_role_id := null;
1775 p_member_user_id := null;
1776 p_cur_person_id := null;
1777 p_cur_member_id := null;
1778 p_position_id := null;
1779 p_old_position_id := null;
1780 p_cur_position_id := null;
1781 p_pos_str_id := null;
1782 p_old_pos_str_id := null;
1783 p_pos_str_ver_id := null;
1784 p_old_pos_str_ver_id := null;
1785 p_assignment_id := null;
1786 p_cur_assignment_id := null;
1787 p_old_assignment_id := null;
1788 p_status_flag := null;
1789 p_history_flag := null;
1790 p_range_name := null;
1791 p_can_approve := null;
1792 raise;
1793 end applicable_next_user;
1794
1795 procedure person_on_assignment(p_assignment_id in per_all_assignments_f.assignment_id%type,
1796 p_value_date in date,
1797 p_person_id out nocopy fnd_user.employee_id%type )
1798 as
1799 cursor c1 is select person_id
1800 from per_all_assignments_f
1801 where assignment_id = p_assignment_id
1802 and p_value_date between effective_start_date and effective_end_date;
1803 l_proc varchar2(256) := g_package||'person_on_assignment';
1804 begin
1805 hr_utility.set_location('Entering '||l_proc,10);
1806 if p_assignment_id is not null then
1807 open c1;
1808 fetch c1 into p_person_id;
1809 if c1%notfound then
1810 hr_utility.set_location('error in fetching info.'||l_proc,20);
1811 else
1812 hr_utility.set_location('person for assignment '||to_char(p_assignment_id)||' is '||to_char(p_person_id)||l_proc,30);
1813 end if;
1814 close c1;
1815 else
1816 hr_utility.set_location('assignment id reqd for person '||l_proc,40);
1817 end if;
1818 hr_utility.set_location('Exiting '||l_proc,10000);
1819 exception when others then
1820 p_person_id := null;
1821 raise;
1822 end person_on_assignment;
1823
1824 procedure rl_member_check(p_routing_list_id in pqh_routing_lists.routing_list_id%type,
1825 p_old_routing_list_id in pqh_routing_lists.routing_list_id%type,
1826 p_history_flag in boolean,
1827 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1828 p_from_clause in pqh_table_route.from_clause%type,
1829 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1830 p_old_member_id in pqh_routing_list_members.routing_list_member_id%type,
1831 p_old_user_id in number,
1832 p_old_role_id in number,
1833 p_user_active_role_id in out nocopy pqh_roles.role_id%type,
1834 p_user_active_role_name in out nocopy pqh_roles.role_name%type,
1835 p_cur_user_id in out nocopy fnd_user.user_id%type,
1836 p_cur_user_name in out nocopy fnd_user.user_name%type,
1837 p_cur_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1838 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1839 p_member_role_id out nocopy pqh_routing_list_members.role_id%type,
1840 p_member_user_id out nocopy pqh_routing_list_members.user_id%type,
1841 p_status_flag out nocopy number,
1842 p_applicable_flag out nocopy boolean,
1843 p_old_can_approve out nocopy boolean,
1844 p_can_approve out nocopy boolean )
1845 as
1846 l_user_active_role_id pqh_roles.role_id%type := p_user_active_role_id;
1847 l_user_active_role_name pqh_roles.role_name%type := p_user_active_role_name;
1848 l_cur_user_id fnd_user.user_id%type := p_cur_user_id;
1849 l_cur_user_name fnd_user.user_name%type := p_cur_user_name;
1850
1851 l_member_id pqh_routing_list_members.routing_list_member_id%type;
1852 l_member_flag boolean ;
1853 l_applicable_flag boolean;
1854 l_error_flag boolean := FALSE ;
1855 l_override_approver boolean ;
1856 l_proc varchar2(256) := g_package||'rl_member_check';
1857 begin
1858 hr_utility.set_location('Entering '||l_proc,10);
1859 if p_tran_cat_id is null then
1860 hr_utility.set_location('Transaction category reqd '||l_proc,20);
1861 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
1862 hr_utility.raise_error;
1863 elsif p_from_clause is null then
1864 hr_utility.set_location('from clause reqd '||l_proc,30);
1865 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
1866 hr_utility.raise_error;
1867 elsif p_routing_list_id is null then
1868 hr_utility.set_location('Routing list reqd '||l_proc,40);
1869 hr_utility.set_message(8302,'PQH_ROUTING_LIST_REQD');
1870 hr_utility.raise_error;
1871 elsif p_cur_user_id is null and p_cur_user_name is null then
1872 hr_utility.set_location('USER id or name reqd '||l_proc,50);
1873 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
1874 hr_utility.raise_error;
1875 elsif p_user_active_role_id is null and p_user_active_role_name is null then
1876 hr_utility.set_location('role id or name reqd '||l_proc,50);
1877 hr_utility.set_message(8302,'PQH_ROLEID_OR_NAME_REQD');
1878 hr_utility.raise_error;
1879 end if;
1880 hr_utility.set_location('All the required data is there '||l_proc,60);
1881 p_status_flag := 0;
1882 p_applicable_flag := FALSE;
1883 rlm_user_seq(p_routing_list_id => p_routing_list_id,
1884 p_old_user_id => p_old_user_id ,
1885 p_old_role_id => p_old_role_id ,
1886 p_old_member_id => p_old_member_id ,
1887 p_role_id => p_user_active_role_id,
1888 p_role_name => p_user_active_role_name,
1889 p_user_id => p_cur_user_id,
1890 p_user_name => p_cur_user_name,
1891 p_member_id => p_cur_member_id,
1892 p_member_flag => l_member_flag);
1893 if l_member_flag = TRUE then
1894 hr_utility.set_location('Cur memberid '||to_char(p_cur_member_id)||l_proc,70);
1895 -- calculate wether the current member can approve the transaction or not.
1896 rl_member_applicable(p_tran_cat_id => p_tran_cat_id,
1897 p_from_clause => p_from_clause,
1898 p_member_id => p_cur_member_id,
1899 p_routing_category_id => p_routing_category_id,
1900 p_used_for => 'C',
1901 p_applicable_flag => l_applicable_flag,
1902 p_status_flag => p_status_flag,
1903 p_can_approve => p_old_can_approve);
1904 if p_status_flag <> 0 then
1905 hr_utility.set_location('current member authority check failed'||l_proc,80);
1906 hr_utility.set_message(8302,'PQH_CURMEMBER_APPROVE_CHK_FAIL');
1907 hr_utility.raise_error;
1908 else
1909 if p_old_can_approve = TRUE then
1910 hr_utility.set_location('current member can approve '||l_proc,90);
1911 else
1912 hr_utility.set_location('current member cannot approve '||l_proc,92);
1913 end if;
1914 end if;
1915 else
1916 hr_utility.set_location('current user not in RL '||l_proc,100);
1917 l_override_approver := override_approver(p_member_cd => 'R',
1918 p_routing_category_id => p_routing_category_id,
1919 p_assignment_id => '',
1920 p_role_id => p_user_active_role_id,
1921 p_user_id => p_cur_user_id,
1922 p_position_id => '');
1923 if l_override_approver then
1924 p_old_can_approve := TRUE;
1925 hr_utility.set_location('member is defined as override approver '||l_proc,129);
1926 else
1927 p_old_can_approve := FALSE;
1928 hr_utility.set_location('member is not defined as override approver '||l_proc,129);
1929 end if;
1930 end if;
1931 l_member_id := p_cur_member_id;
1932 while p_status_flag = 0 and nvl(p_applicable_flag,FALSE) = FALSE loop
1933 rl_next_user(p_routing_list_id => p_routing_list_id,
1934 p_cur_member_id => l_member_id,
1935 p_member_id => p_member_id,
1936 p_role_id => p_member_role_id,
1937 p_user_id => p_member_user_id,
1938 p_status_flag => p_status_flag);
1939 if p_status_flag = 0 then
1940 hr_utility.set_location('user selected, checking applicability'||l_proc,110);
1941 rl_member_applicable(p_tran_cat_id => p_tran_cat_id,
1942 p_from_clause => p_from_clause,
1943 p_member_id => p_member_id,
1944 p_routing_category_id => p_routing_category_id,
1945 p_used_for => 'N',
1946 p_applicable_flag => p_applicable_flag,
1947 p_status_flag => p_status_flag,
1948 p_can_approve => p_can_approve);
1949 if p_status_flag = 0 then
1950 hr_utility.set_location('User checked for applicability no error'||l_proc,120);
1951 if p_applicable_flag = FALSE then
1952 hr_utility.set_location('user not applicable, another iteration '||l_proc,130);
1953 l_member_id := p_member_id;
1954 else
1955 hr_utility.set_location('user is applicable '||l_proc,140);
1956 end if;
1957 elsif p_status_flag = 1 then
1958 hr_utility.set_location('got EOL for member'||to_char(p_member_id)||l_proc,150);
1959 else
1960 hr_utility.set_location('Error, status is '||to_char(p_status_flag)||' memberid '||to_char(p_member_id)||l_proc,160);
1961 end if;
1962 elsif p_status_flag = 1 then
1963 hr_utility.set_location('Got EOL '||l_proc,170);
1964 else
1965 hr_utility.set_location('error '||to_char(p_status_flag)||to_char(l_member_id)||l_proc,180);
1966 end if;
1967 end loop;
1968 if p_status_flag = 0 then
1969 hr_utility.set_location('no error so far and out nocopy of loop '||l_proc,190);
1970 if p_applicable_flag = TRUE then
1971 hr_utility.set_location('Applicable member found to be '||to_char(p_member_id)||l_proc,200);
1972 else
1973 hr_utility.set_location('This message should not be shown '||l_proc,210);
1974 end if;
1975 else
1976 hr_utility.set_location('Out of loop, status_flag '||to_char(p_status_flag)||l_proc,220);
1977 end if;
1978 hr_utility.set_location('Exiting '||l_proc,10000);
1979 exception when others then
1980 p_user_active_role_id := l_user_active_role_id;
1981 p_user_active_role_name := l_user_active_role_name;
1982 p_cur_user_id := l_cur_user_id;
1983 p_cur_user_name := l_cur_user_name;
1984 p_cur_member_id := null;
1985 p_member_id := null;
1986 p_member_role_id := null;
1987 p_member_user_id := null;
1988 p_status_flag := null;
1989 p_applicable_flag := null;
1990 p_old_can_approve := null;
1991 p_can_approve := null;
1992 raise;
1993 end rl_member_check ;
1994
1995 procedure ps_element_check(p_history_flag in boolean,
1996 p_value_date in date,
1997 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1998 p_from_clause in pqh_table_route.from_clause%type,
1999 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
2000 p_old_position_id in pqh_position_transactions.position_id%type,
2001 p_pos_str_id in per_pos_structure_versions.position_structure_id%type,
2002 p_cur_user_id in out nocopy fnd_user.user_id%type,
2003 p_cur_user_name in out nocopy fnd_user.user_name%type,
2004 p_pos_str_ver_id out nocopy per_pos_structure_elements.pos_structure_version_id%type,
2005 p_cur_position_id out nocopy per_all_assignments_f.position_id%type,
2006 p_cur_person_id out nocopy fnd_user.employee_id%type,
2007 p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
2008 p_old_pos_str_id out nocopy per_pos_structure_versions.position_structure_id%type,
2009 p_position_id out nocopy pqh_position_transactions.position_id%type,
2010 p_status_flag out nocopy number,
2011 p_can_approve out nocopy boolean,
2012 p_old_can_approve out nocopy boolean,
2013 p_applicable_flag out nocopy boolean )
2014 as
2015 l_cur_user_id fnd_user.user_id%type := p_cur_user_id;
2016 l_cur_user_name fnd_user.user_name%type := p_cur_user_name;
2017 l_cur_position_id pqh_position_transactions.position_id%type;
2018 l_override_approver boolean ;
2019 l_error_flag boolean := FALSE;
2020 l_member_flag varchar2(30) ;
2021 l_pos_str_change boolean;
2022 l_applicable_flag boolean;
2023 l_proc varchar2(256) := g_package||'ps_element_check';
2024 begin
2025 hr_utility.set_location('Entering '||l_proc,10);
2026 p_status_flag := 0;
2027 p_applicable_flag := FALSE;
2028 if p_tran_cat_id is null then
2029 hr_utility.set_location('Transaction category reqd '||l_proc,20);
2030 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
2031 hr_utility.raise_error;
2032 elsif p_from_clause is null then
2033 hr_utility.set_location('From clause reqd '||l_proc,30);
2034 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
2035 hr_utility.raise_error;
2036 elsif p_cur_user_id is null and p_cur_user_name is null then
2037 hr_utility.set_location('either cur user id or name reqd '||l_proc,40);
2038 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
2039 hr_utility.raise_error;
2040 elsif p_pos_str_id is null then
2041 hr_utility.set_location('Position structure must be provided '||l_proc,41);
2042 hr_utility.set_message(8302,'PQH_POS_STR_REQD');
2043 hr_utility.raise_error;
2044 end if;
2045 hr_utility.set_location('Get the latest version '||l_proc,42);
2046 p_pos_str_ver_id := pos_str_version(p_pos_str_id => p_pos_str_id);
2047 hr_utility.set_location('getting current user details'||l_proc,52);
2048 user_position_and_assignment(p_user_id => p_cur_user_id,
2049 p_user_name => p_cur_user_name,
2050 p_value_date => p_value_date,
2051 p_assignment_id => p_cur_assignment_id,
2052 p_person_id => p_cur_person_id,
2053 p_position_id => p_cur_position_id);
2054 hr_utility.set_location('Current user position id is '||to_char(p_cur_position_id)||l_proc,70);
2055 l_member_flag := pos_in_ph(p_position_id => p_cur_position_id,
2056 p_pos_str_ver_id => p_pos_str_ver_id );
2057 hr_utility.set_location('Current user position id is '||l_proc,701);
2058
2059 if l_member_flag = 'TRUE' then
2060 hr_utility.set_location('l_member_flag = TRUE '||l_proc,702);
2061 ps_element_applicable(p_tran_cat_id => p_tran_cat_id,
2062 p_from_clause => p_from_clause,
2063 p_position_id => p_cur_position_id,
2064 p_routing_category_id => p_routing_category_id,
2065 p_value_date => p_value_date,
2066 p_used_for => 'C',
2067 p_applicable_flag => l_applicable_flag,
2068 p_status_flag => p_status_flag,
2069 p_can_approve => p_old_can_approve );
2070 hr_utility.set_location('l_member_flag = TRUE '||l_proc,703);
2071 if p_status_flag = 0 then
2072 hr_utility.set_location('l_member_flag = TRUE '||l_proc,704);
2073 if p_old_can_approve then
2074 hr_utility.set_location('cur user can approve '||l_proc,71);
2075 else
2076 hr_utility.set_location('cur user can not approve '||l_proc,72);
2077 end if;
2078 else
2079 hr_utility.set_location('cur position approve checked'||l_proc,73);
2080 hr_utility.set_message(8302,'PQH_CURPOS_APPROVE_FAILED');
2081 hr_utility.raise_error;
2082 end if;
2083 hr_utility.set_location('l_member_flag = TRUE '||l_proc,705);
2084 else
2085 hr_utility.set_location('current position not in PH'||l_proc,74);
2086 hr_utility.set_location('checking override approver'||l_proc,75);
2087 -- check for override position approver
2088 l_override_approver := override_approver(p_member_cd => 'P',
2089 p_routing_category_id => p_routing_category_id,
2090 p_assignment_id => '',
2091 p_role_id => '',
2092 p_user_id => '',
2093 p_position_id => p_cur_position_id);
2094 if l_override_approver then
2095 p_old_can_approve := TRUE;
2096 hr_utility.set_location('override position'||l_proc,76);
2097 end if;
2098 end if;
2099 hr_utility.set_location('After l_member_flag cond '||l_proc,710);
2100 l_cur_position_id := p_cur_position_id;
2101 /* Changed the while condition-to fetch next available position
2102 in case of eliminated position Bug#2295241 */
2103 while (p_status_flag = 0 or p_status_flag = 8)and nvl(p_applicable_flag,FALSE) = FALSE loop
2104 hr_utility.set_location('Inside the next position loop'||l_proc,80);
2105 ph_next_user(p_cur_position_id => l_cur_position_id,
2106 p_pos_str_ver_id => p_pos_str_ver_id,
2107 p_position_id => p_position_id,
2108 p_status_flag => p_status_flag) ;
2109 if p_status_flag = 0 then
2110 hr_utility.set_location('user selected '||to_char(p_position_id)||l_proc,90);
2111 ps_element_applicable(p_tran_cat_id => p_tran_cat_id,
2112 p_from_clause => p_from_clause,
2113 p_position_id => p_position_id,
2114 p_routing_category_id => p_routing_category_id,
2115 p_value_date => p_value_date,
2116 p_used_for => 'N',
2117 p_applicable_flag => p_applicable_flag,
2118 p_status_flag => p_status_flag,
2119 p_can_approve => p_can_approve );
2120 if p_status_flag = 0 then
2121 hr_utility.set_location('User checked no error'||l_proc,100);
2122 if p_applicable_flag = FALSE then
2123 hr_utility.set_location('user unapplicable, iteration reqd'||l_proc,110);
2124 l_cur_position_id := p_position_id;
2125 else
2126 hr_utility.set_location('user applicable '||l_proc,120);
2127 end if;
2128 elsif p_status_flag = 1 then
2129 hr_utility.set_location('got EOL'||to_char(l_cur_position_id)||l_proc,130);
2130 /* Added for Bug#2295241 */
2131 elsif p_status_flag = 8 then
2132 hr_utility.set_location('Eliminated Position:'||to_char(l_cur_position_id)||l_proc,135);
2133 l_cur_position_id := p_position_id;
2134 /* End Bug#2295241*/
2135 else
2136 hr_utility.set_location('Error,stat'||to_char(p_status_flag)||' Pos'||to_char(l_cur_position_id)||l_proc,140);
2137 end if;
2138 elsif p_status_flag = 1 then
2139 hr_utility.set_location('Got EOL '||l_proc,150);
2140 elsif p_status_flag = 8 then
2141 hr_utility.set_location('Got Eliminated Position '||l_proc,155);
2142 /* Added for Bug#2295241 */
2143 l_cur_position_id := p_position_id;
2144 /* End Bug#2295241 */
2145 else
2146 hr_utility.set_location('error next position '||to_char(l_cur_position_id)||l_proc,160);
2147 end if;
2148 end loop;
2149 if p_status_flag = 0 then
2150 hr_utility.set_location('no error so far '||l_proc,170);
2151 if p_applicable_flag = TRUE then
2152 hr_utility.set_location('Applicable position '||to_char(p_position_id)||l_proc,180);
2153 else
2154 hr_utility.set_location('message not be shown '||l_proc,190);
2155 end if;
2156 else
2157 hr_utility.set_location('Out,status_flag '||to_char(p_status_flag)||l_proc,200);
2158 end if;
2159 hr_utility.set_location('Exiting '||l_proc,10000);
2160 exception when others then
2161 p_cur_user_id := l_cur_user_id;
2162 p_cur_user_name := l_cur_user_name;
2163 p_pos_str_ver_id := null;
2164 p_cur_position_id := null;
2165 p_cur_person_id := null;
2166 p_cur_assignment_id := null;
2167 p_old_pos_str_id := null;
2168 p_position_id := null;
2169 p_status_flag := null;
2170 p_can_approve := null;
2171 p_old_can_approve := null;
2172 p_applicable_flag := null;
2173 raise;
2174 end ps_element_check ;
2175
2176 procedure assignment_check(p_history_flag in boolean,
2177 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2178 p_from_clause in pqh_table_route.from_clause%type,
2179 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
2180 p_old_assignment_id in per_all_assignments_f.assignment_id%type,
2181 p_value_date in date,
2182 p_cur_user_id in out nocopy fnd_user.user_id%type,
2183 p_cur_user_name in out nocopy fnd_user.user_name%type,
2184 p_cur_person_id out nocopy fnd_user.employee_id%type,
2185 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
2186 p_person_id out nocopy per_all_assignments_f.person_id%type,
2187 p_status_flag out nocopy number,
2188 p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
2189 p_old_can_approve out nocopy boolean,
2190 p_can_approve out nocopy boolean,
2191 p_applicable_flag out nocopy boolean )
2192 as
2193 l_old_assignment_id per_all_assignments_f.assignment_id%type;
2194 l_applicable_flag boolean ;
2195 l_override_approver boolean;
2196 l_error_flag boolean := FALSE ;
2197 l_proc varchar2(256) := g_package||'assignment_check';
2198 l_cur_user_id fnd_user.user_id%type := p_cur_user_id;
2199 l_cur_user_name fnd_user.user_name%type := p_cur_user_name;
2200 begin
2201 hr_utility.set_location('Entering '||l_proc,10);
2202 if p_tran_cat_id is null then
2203 hr_utility.set_location('Trans_cat reqd '||l_proc,20);
2204 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
2205 hr_utility.raise_error;
2206 elsif p_from_clause is null then
2207 hr_utility.set_location('From clause reqd '||l_proc,30);
2208 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
2209 hr_utility.raise_error;
2210 elsif p_cur_user_id is null and p_cur_user_name is null then
2211 hr_utility.set_location('Cur userid or name reqd '||l_proc,40);
2212 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
2213 hr_utility.raise_error;
2214 end if;
2215 p_status_flag := 0;
2216 user_assignment(p_user_id => p_cur_user_id,
2217 p_user_name => p_cur_user_name,
2218 p_person_id => p_cur_person_id,
2219 p_value_date => p_value_date,
2220 p_assignment_id => p_cur_assignment_id);
2221 assignment_applicable(p_tran_cat_id => p_tran_cat_id,
2222 p_from_clause => p_from_clause,
2223 p_assignment_id => p_cur_assignment_id,
2224 p_routing_category_id => p_routing_category_id,
2225 p_value_date => p_value_date,
2226 p_used_for => 'C',
2227 p_applicable_flag => l_applicable_flag,
2228 p_status_flag => p_status_flag,
2229 p_can_approve => p_old_can_approve) ;
2230 if p_status_flag = 0 then
2231 if p_old_can_approve then
2232 hr_utility.set_location('cur user can approve '||l_proc,41);
2233 else
2234 hr_utility.set_location('cur user can not approve '||l_proc,42);
2235 end if;
2236 else
2237 hr_utility.set_location('assignment not applicable, checking override'||l_proc,50);
2238 l_override_approver := override_approver(p_member_cd => 'S',
2239 p_routing_category_id => p_routing_category_id,
2240 p_assignment_id => p_assignment_id,
2241 p_role_id => '',
2242 p_user_id => '',
2243 p_position_id => '');
2244 if l_override_approver then
2245 p_old_can_approve := TRUE;
2246 hr_utility.set_location('assignment is defined as override approver '||l_proc,129);
2247 else
2248 p_old_can_approve := FALSE;
2249 hr_utility.set_location('assignment is not defined as override approver '||l_proc,129);
2250 end if;
2251 end if;
2252 l_old_assignment_id := p_cur_assignment_id;
2253 while p_status_flag = 0 and nvl(p_applicable_flag,FALSE) = FALSE loop
2254 hr_utility.set_location('Finding the supervisor of assignment'||to_char(l_old_assignment_id)||l_proc,70);
2255 su_next_user(p_cur_assignment_id => l_old_assignment_id,
2256 p_value_date => p_value_date,
2257 p_assignment_id => p_assignment_id,
2258 p_status_flag => p_status_flag) ;
2259 if p_status_flag = 0 then
2260 hr_utility.set_location('next assignment selected, checking '||l_proc,80);
2261 assignment_applicable(p_tran_cat_id => p_tran_cat_id,
2262 p_from_clause => p_from_clause,
2263 p_assignment_id => p_assignment_id,
2264 p_routing_category_id => p_routing_category_id,
2265 p_value_date => p_value_date,
2266 p_used_for => 'N',
2267 p_applicable_flag => p_applicable_flag,
2268 p_status_flag => p_status_flag,
2269 p_can_approve => p_can_approve) ;
2270 if p_status_flag = 0 then
2271 hr_utility.set_location('assignment checked no error'||l_proc,90);
2272 if p_applicable_flag = FALSE then
2273 hr_utility.set_location('unapplicable, iteration'||l_proc,100);
2274 l_old_assignment_id := p_assignment_id;
2275 else
2276 hr_utility.set_location('assignment applicable'||l_proc,110);
2277 end if;
2278 elsif p_status_flag = 1 then
2279 hr_utility.set_location('got EOL, assignment '||to_char(l_old_assignment_id)||l_proc,120);
2280 else
2281 hr_utility.set_location('Error , status '||to_char(p_status_flag)||' assignment '||to_char(l_old_assignment_id)||l_proc,130);
2282 end if;
2283 elsif p_status_flag = 1 then
2284 hr_utility.set_location('Got EOL'||l_proc,140);
2285 else
2286 hr_utility.set_location('error , assignment '||to_char(l_old_assignment_id)||l_proc,150);
2287 end if;
2288 end loop;
2289 if p_status_flag = 0 then
2290 hr_utility.set_location('no error , out nocopy of loop '||l_proc,160);
2291 if p_applicable_flag = TRUE then
2292 hr_utility.set_location('Applicable assignment '||to_char(p_assignment_id)||l_proc,170);
2293 else
2294 hr_utility.set_location('message should not be shown '||l_proc,180);
2295 end if;
2296 else
2297 hr_utility.set_location('Out of loop and status_flag '||to_char(p_status_flag)||l_proc,190);
2298 end if;
2299 hr_utility.set_location('Exiting '||l_proc,10000);
2300 exception when others then
2301 p_cur_user_id := l_cur_user_id;
2302 p_cur_user_name := l_cur_user_name;
2303 p_cur_person_id := null;
2304 p_assignment_id := null;
2305 p_person_id := null;
2306 p_status_flag := null;
2307 p_cur_assignment_id := null;
2308 p_old_can_approve := null;
2309 p_can_approve := null;
2310 p_applicable_flag := null;
2311 raise;
2312 end assignment_check ;
2313
2314 function get_attribute_name(p_attribute_id in number,
2315 p_transaction_id in number,
2316 p_tran_cat_id in number)
2317 return varchar2 is
2318 l_attribute_name varchar2(200);
2319 l_column_name varchar2(200);
2320 l_unit_name varchar2(200);
2321 l_avail_desc varchar2(200);
2322 l_budget_id number;
2323 cursor c0 is select wks.budget_id
2324 from pqh_worksheets wks, pqh_worksheet_details wdt
2325 where wdt.worksheet_detail_id = p_transaction_id
2326 and wdt.worksheet_id = wks.worksheet_id
2327 and wks.wf_transaction_category_id = p_tran_cat_id;
2328 cursor c1 is select attribute_name,column_name
2329 from pqh_attributes_vl
2330 where attribute_id = p_attribute_id
2331 and nvl(enable_flag,'X') ='Y';
2332 cursor c2 is select sty.shared_type_name unit_name,lkp.description avail_desc
2333 from pqh_budgets bgt, per_shared_types sty, hr_lookups lkp
2334 where bgt.budget_id = l_budget_id
2335 and bgt.budget_unit1_aggregate = lkp.lookup_code
2336 and lkp.lookup_type ='PQH_BGT_UOM_AGGREGATE'
2337 and bgt.budget_unit1_id = sty.shared_type_id ;
2338 cursor c3 is select sty.shared_type_name unit_name, lkp.description avail_desc
2339 from pqh_budgets bgt, per_shared_types sty, hr_lookups lkp
2340 where bgt.budget_id = l_budget_id
2341 and bgt.budget_unit2_aggregate = lkp.lookup_code
2342 and lkp.lookup_type ='PQH_BGT_UOM_AGGREGATE'
2343 and bgt.budget_unit2_id = sty.shared_type_id ;
2344 cursor c4 is select sty.shared_type_name unit_name, lkp.description avail_desc
2345 from pqh_budgets bgt, per_shared_types sty, hr_lookups lkp
2346 where bgt.budget_id = l_budget_id
2347 and bgt.budget_unit3_aggregate = lkp.lookup_code
2348 and lkp.lookup_type ='PQH_BGT_UOM_AGGREGATE'
2349 and bgt.budget_unit3_id = sty.shared_type_id ;
2350 l_proc varchar2(256) := g_package||'get_attribute_name';
2351 begin
2352 hr_utility.set_location('Entering '||l_proc,10);
2353 hr_utility.set_location('attribute_id is '||p_attribute_id||l_proc,20);
2354 open c0;
2355 fetch c0 into l_budget_id;
2356 if c0%found then
2357 hr_utility.set_location('budget is '||l_budget_id||l_proc,20);
2358 for i in c1 loop
2359 l_attribute_name := i.attribute_name;
2360 l_column_name := i.column_name;
2361 end loop;
2362 hr_utility.set_location('attribute_name is '||l_attribute_name||l_proc,30);
2363 hr_utility.set_location('column_name is '||l_column_name||l_proc,30);
2364 if l_column_name in ('WDT.BUDGET_UNIT1_VALUE','WDT.BUDGET_UNIT1_AVAILABLE','WDT.BUDGET_UNIT1_PERCENT') then
2365 hr_utility.set_location('unit column is '||l_column_name||l_proc,30);
2366 open c2;
2367 fetch c2 into l_unit_name,l_avail_desc ;
2368 if c2%notfound then
2369 hr_utility.set_location('unit1 is not defined '||l_proc,30);
2370 else
2371 hr_utility.set_location('unit1_name fetched '||l_unit_name||l_proc,30);
2372 if l_column_name = 'WDT.BUDGET_UNIT1_VALUE' then
2373 l_attribute_name := l_unit_name ;
2374 elsif l_column_name = 'WDT.BUDGET_UNIT1_PERCENT' then
2375 l_attribute_name := l_unit_name ||' : % ';
2376 elsif l_column_name = 'WDT.BUDGET_UNIT1_AVAILABLE' then
2377 l_attribute_name := l_unit_name ||' : '||l_avail_desc;
2378 end if;
2379 end if;
2380 close c2;
2381 elsif l_column_name in ('WDT.BUDGET_UNIT2_VALUE','WDT.BUDGET_UNIT2_AVAILABLE','WDT.BUDGET_UNIT2_PERCENT') then
2382 hr_utility.set_location('unit column is '||l_column_name||l_proc,30);
2383 open c3;
2384 fetch c3 into l_unit_name,l_avail_desc ;
2385 if c3%notfound then
2386 hr_utility.set_location('unit2 is not defined '||l_proc,30);
2387 else
2388 hr_utility.set_location('unit2_name fetched '||l_unit_name||l_proc,30);
2389 if l_column_name = 'WDT.BUDGET_UNIT2_VALUE' then
2390 l_attribute_name := l_unit_name ;
2391 elsif l_column_name = 'WDT.BUDGET_UNIT2_PERCENT' then
2392 l_attribute_name := l_unit_name ||' : % ';
2393 elsif l_column_name = 'WDT.BUDGET_UNIT2_AVAILABLE' then
2394 l_attribute_name := l_unit_name ||' : '||l_avail_desc;
2395 end if;
2396 end if;
2397 close c3;
2398 elsif l_column_name in ('WDT.BUDGET_UNIT3_VALUE','WDT.BUDGET_UNIT3_AVAILABLE','WDT.BUDGET_UNIT3_PERCENT') then
2399 hr_utility.set_location('unit column is '||l_column_name||l_proc,30);
2400 open c4;
2401 fetch c4 into l_unit_name,l_avail_desc ;
2402 if c4%notfound then
2403 hr_utility.set_location('unit3 is not defined'||l_proc,30);
2404 else
2405 hr_utility.set_location('unit3_name fetched '||l_unit_name||l_proc,30);
2406 if l_column_name = 'WDT.BUDGET_UNIT3_VALUE' then
2407 l_attribute_name := l_unit_name ;
2408 elsif l_column_name = 'WDT.BUDGET_UNIT3_PERCENT' then
2409 l_attribute_name := l_unit_name ||' : % ';
2410 elsif l_column_name = 'WDT.BUDGET_UNIT3_AVAILABLE' then
2411 l_attribute_name := l_unit_name ||' : '||l_avail_desc;
2412 end if;
2413 end if;
2414 close c4;
2415 end if;
2416 else
2417 hr_utility.set_location('tran_cat is '||p_tran_cat_id||l_proc,30);
2418 for i in c1 loop
2419 l_attribute_name := i.attribute_name;
2420 l_column_name := i.column_name;
2421 end loop;
2422 end if;
2423 hr_utility.set_location('attribute_name is '||l_attribute_name||l_proc,30);
2424 return l_attribute_name;
2425 hr_utility.set_location('Exiting '||l_proc,10000);
2426 end;
2427
2428 procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2429 p_used_for in varchar2 default null,
2430 p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
2431 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
2432 p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
2433 p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
2434 p_status_flag out nocopy number ) is
2435 cursor c0 is select member_cd
2436 from pqh_transaction_categories
2437 where transaction_category_id = p_tran_cat_id;
2438 cursor c1 is
2439 select routing_list_id ,routing_category_id
2440 from pqh_routing_categories
2441 where transaction_category_id = p_tran_cat_id
2442 and nvl(enable_flag,'N') ='Y'
2443 and nvl(default_flag,'N') = 'Y'
2444 and nvl(delete_flag,'N') = 'N'
2445 and routing_list_id is not null;
2446
2447 cursor c2 is
2448 select position_structure_id ,routing_category_id
2449 from pqh_routing_categories
2450 where transaction_category_id = p_tran_cat_id
2451 and nvl(enable_flag,'N') ='Y'
2452 and nvl(default_flag,'N') = 'Y'
2453 and nvl(delete_flag,'N') = 'N'
2454 and position_structure_id is not null;
2455
2456 cursor c3 is
2457 select routing_category_id
2458 from pqh_routing_categories
2459 where transaction_category_id = p_tran_cat_id
2460 and nvl(enable_flag,'N') ='Y'
2461 and nvl(default_flag,'N') = 'Y'
2462 and nvl(delete_flag,'N') = 'N'
2463 and routing_list_id is null
2464 and position_structure_id is null;
2465
2466 l_proc varchar2(256) := g_package||'list_range_check_std';
2467 begin
2468 -- this procedure will be getting called if only standard setup is complete.
2469 /*
2470 The change in logic , to pick up the routing category was changed with discussion with stella
2471 as per the change in wizard functionality.
2472 earlier we were assuming that only one default routing category will be there, but there can be
2473 multiple default routing categories for different member_cd.
2474 for a member_cd however there will be only one. so based on that assumption, three cursors
2475 were made to pull up the routing category based on member_cd
2476 */
2477 hr_utility.set_location('Entering '||l_proc,10);
2478 p_status_flag := 0;
2479 if p_tran_cat_id is null then
2480 hr_utility.set_location('Transaction category reqd '||l_proc,20);
2481 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
2482 hr_utility.raise_error;
2483 end if;
2484 delete_rout_crit(p_used_for => nvl(p_used_for,'L'));
2485 hr_utility.set_location('data deleted for rout select '||l_proc,40);
2486 open c0;
2487 fetch c0 into p_member_cd;
2488 close c0;
2489 if p_member_cd = 'R' then
2490 hr_utility.set_location('Routing List '||l_proc,50);
2491 open c1;
2492 fetch c1 into p_routing_list_id,p_routing_category_id;
2493 close c1;
2494 elsif p_member_cd ='P' then
2495 hr_utility.set_location('Position hierarchy '||l_proc,60);
2496 open c2;
2497 fetch c2 into p_pos_str_id,p_routing_category_id;
2498 close c2;
2499 elsif p_member_cd ='S' then
2500 hr_utility.set_location('Supervisory hierarchy '||l_proc,70);
2501 open c3;
2502 fetch c3 into p_routing_category_id;
2503 close c3;
2504 else
2505 hr_utility.set_location('invalid member cd '||l_proc,70);
2506 p_status_flag := 1;
2507 end if;
2508 hr_utility.set_location('Exiting '||l_proc,200);
2509 exception
2510 when others then
2511 p_member_cd := null;
2512 p_routing_list_id := null;
2513 p_pos_str_id := null;
2514 p_routing_category_id := null;
2515 p_status_flag := null;
2516 hr_utility.set_location('some error'||substr(sqlerrm,1,50),100);
2517 hr_utility.raise_error;
2518 end list_range_check;
2519
2520 procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2521 p_trans_id in pqh_routing_history.transaction_id%type,
2522 p_from_clause in pqh_table_route.from_clause%type,
2523 p_used_for in varchar2 default null,
2524 p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
2525 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
2526 p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
2527 p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
2528 p_range_name out nocopy pqh_attribute_ranges.range_name%type,
2529 p_status_flag out nocopy number )
2530 as
2531 cursor c1 is
2532 select distinct ar.routing_category_id, ar.range_name
2533 from pqh_attribute_ranges ar
2534 where ar.routing_list_member_id is null
2535 and ar.position_id is null
2536 and ar.assignment_id is null
2537 and nvl(ar.enable_flag,'X') = 'Y'
2538 and nvl(ar.delete_flag,'N') = 'N'
2539 and routing_category_id in (select routing_category_id
2540 from pqh_routing_categories rc, pqh_transaction_categories tc
2541 where rc.transaction_category_id = p_tran_cat_id
2542 and tc.transaction_category_id = rc.transaction_category_id
2543 and ((tc.member_cd = 'R' and rc.routing_list_id is not null) or
2544 (tc.member_cd = 'P' and rc.Position_structure_id is not null) or
2545 (tc.member_cd = 'S' and rc.routing_list_id is null and rc.position_structure_id is null))
2546 and nvl(rc.enable_flag,'X') = 'Y'
2547 and nvl(rc.delete_flag,'N') = 'N'
2548 and nvl(rc.default_flag,'X') <> 'Y' );
2549 cursor c2 is
2550 select att.attribute_id,att.attribute_name,att.column_name,att.column_type
2551 from pqh_attributes att, pqh_txn_category_attributes tca
2552 where att.attribute_id = tca.attribute_id
2553 and tca.transaction_category_id = p_tran_cat_id
2554 and tca.list_identifying_flag = 'Y'
2555 and nvl(att.enable_flag,'X') = 'Y';
2556 cursor c3(p_attribute_id varchar2) is
2557 select ar.from_char,ar.to_char,ar.from_date,ar.to_date,ar.from_number,
2558 ar.to_number,rc.routing_category_id,ar.range_name
2559 from pqh_attribute_ranges ar,pqh_routing_categories rc
2560 where ar.attribute_id = p_attribute_id
2561 and ar.routing_category_id = rc.routing_category_id
2562 and rc.transaction_category_id = p_tran_cat_id
2563 and ar.routing_list_member_id is null
2564 and ar.position_id is null
2565 and ar.assignment_id is null
2566 and nvl(ar.enable_flag,'X') = 'Y'
2567 and nvl(rc.delete_flag,'N') = 'N'
2568 and nvl(ar.delete_flag,'N') = 'N'
2569 and nvl(rc.enable_flag,'X') = 'Y'
2570 and nvl(rc.default_flag,'X') <> 'Y';
2571 cursor c4 is
2572 select tc.member_cd,rc.routing_list_id,rc.position_structure_id
2573 from pqh_routing_categories rc, pqh_transaction_categories tc
2574 where rc.routing_category_id = p_routing_category_id
2575 and tc.transaction_category_id = rc.transaction_category_id
2576 and nvl(rc.delete_flag,'N') = 'N'
2577 and nvl(rc.enable_flag,'X') = 'Y';
2578 l_attribute_value_char varchar2(2000);
2579 l_attribute_value_date date;
2580 l_attribute_value_num number;
2581 l_attributes_name varchar2(2000);
2582 l_null_attributes_name varchar2(2000);
2583 l_attribute_name varchar2(2000);
2584 l_in_range boolean ;
2585 l_rule_cnt number ;
2586 type list_rec is record (
2587 routing_category_id pqh_routing_categories.routing_category_id%type,
2588 range_name pqh_attribute_ranges.range_name%type,
2589 selected_flag boolean) ;
2590 type list_tab is table of list_rec
2591 index by binary_integer;
2592 l_hierarchy list_tab;
2593 l_error_flag boolean := FALSE;
2594 l_range_found_flag boolean;
2595 l_approver_flag boolean;
2596 l_rout_cat number;
2597 l_standard_setup number;
2598 l_proc varchar2(256) := g_package||'list_range_check_adv';
2599 begin
2600 /* called when advanced setup is complete */
2601 hr_utility.set_location('Entering '||l_proc,10);
2602 p_status_flag := 0;
2603 if p_tran_cat_id is null then
2604 hr_utility.set_location('Transaction category reqd '||l_proc,20);
2605 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
2606 hr_utility.raise_error;
2607 elsif p_from_clause is null then
2608 hr_utility.set_location('from clause reqd '||l_proc,30);
2609 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
2610 hr_utility.raise_error;
2611 end if;
2612 delete_rout_crit(p_used_for => p_used_for);
2613 l_rule_cnt := 0;
2614 hr_utility.set_location('Finding unique rules for tran_cat : '||to_char(p_tran_cat_id)||l_proc,40);
2615 for i in c1 loop
2616 l_hierarchy(l_rule_cnt).routing_category_id := i.routing_category_id;
2617 l_hierarchy(l_rule_cnt).range_name := i.range_name;
2618 l_hierarchy(l_rule_cnt).selected_flag := TRUE ;
2619 l_rule_cnt := l_rule_cnt + 1;
2620 hr_utility.set_location('catg added'||to_char(i.routing_category_id)||i.range_name||l_proc,45);
2621 end loop;
2622 if l_rule_cnt >0 then
2623 hr_utility.set_location('fetch the values '||l_proc,50);
2624 for i in c2 loop
2625 hr_utility.set_location('inside loop for each list_identifying attribute '||l_proc,51);
2626 hr_utility.set_location('col_name 1 '||substr(i.column_name,1,50)||l_proc,52);
2627 hr_utility.set_location('col_name 2 '||substr(i.column_name,51,50)||l_proc,53);
2628 l_attribute_name := get_attribute_name(p_attribute_id => i.attribute_id,
2629 p_transaction_id => p_trans_id,
2630 p_tran_cat_id => p_tran_cat_id);
2631 hr_utility.set_location('att_name 1'||substr(l_attributes_name,1,50)||l_proc,54);
2632 hr_utility.set_location('att_name 2'||substr(l_attributes_name,51,50)||l_proc,55);
2633 if i.column_type ='V' or i.column_type ='C' then
2634 hr_utility.set_location('column type is '||i.column_type||l_proc,58);
2635 begin
2636 execute immediate 'select '||i.column_name||' '||p_from_clause
2637 into l_attribute_value_char ;
2638 hr_utility.set_location('value for attribute is '||l_attribute_value_char||l_proc,60 );
2639 exception
2640 when no_data_found then
2641 hr_utility.set_location('no data in trans table'||l_proc,70);
2642 when others then
2643 hr_utility.set_location('error in select table'||l_proc,70);
2644 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
2645 hr_utility.raise_error;
2646 end;
2647 if l_attributes_name is null then
2648 l_attributes_name := l_attribute_name ||' => '||l_attribute_value_char ;
2649 else
2650 if l_attribute_name is not null then
2651 l_attributes_name := l_attributes_name||','||l_attribute_name ||' => '||l_attribute_value_char;
2652 end if;
2653 end if;
2654 if l_attribute_value_char is null then
2655 if l_null_attributes_name is null then
2656 l_null_attributes_name := l_attribute_name;
2657 else
2658 if l_attribute_name is not null then
2659 l_null_attributes_name := l_null_attributes_name||','||l_attribute_name;
2660 end if;
2661 end if;
2662 end if;
2663 elsif i.column_type ='D' then
2664 hr_utility.set_location('column type is '||i.column_type||l_proc,54);
2665 begin
2666 execute immediate 'select '||i.column_name||' '||p_from_clause
2667 into l_attribute_value_date ;
2668 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_date,'DDMMRRRR')||l_proc,80 );
2669 exception
2670 when no_data_found then
2671 hr_utility.set_location('no data in trans table'||l_proc,90);
2672 when others then
2673 hr_utility.set_location('error in select table'||l_proc,70);
2674 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
2675 hr_utility.raise_error;
2676 end;
2677 if l_attributes_name is null then
2678 l_attributes_name := l_attribute_name ||' => '||to_char(l_attribute_value_date,'DDMMRRRR') ;
2679 else
2680 if l_attribute_name is not null then
2681 l_attributes_name := l_attributes_name||','||l_attribute_name ||' => '||to_char(l_attribute_value_date,'DDMMRRRR');
2682 end if;
2683 end if;
2684 if l_attribute_value_date is null then
2685 if l_null_attributes_name is null then
2686 l_null_attributes_name := l_attribute_name;
2687 else
2688 if l_attribute_name is not null then
2689 l_null_attributes_name := l_null_attributes_name||','||l_attribute_name;
2690 end if;
2691 end if;
2692 end if;
2693 elsif i.column_type ='N' then
2694 hr_utility.set_location('column type is '||i.column_type||l_proc,54);
2695 begin
2696 execute immediate 'select '||i.column_name||' '||p_from_clause
2697 into l_attribute_value_num ;
2698 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_num)||l_proc,100 );
2699 exception
2700 when no_data_found then
2701 hr_utility.set_location('no data in trans table'||l_proc,110);
2702 when others then
2703 hr_utility.set_location('error in select table'||l_proc,70);
2704 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
2705 hr_utility.raise_error;
2706 end;
2707 if l_attributes_name is null then
2708 l_attributes_name := l_attribute_name ||' => '||l_attribute_value_num ;
2709 else
2710 if l_attribute_name is not null then
2711 l_attributes_name := l_attributes_name||','||l_attribute_name ||' => '||l_attribute_value_num;
2712 end if;
2713 end if;
2714 if l_attribute_value_num is null then
2715 if l_null_attributes_name is null then
2716 l_null_attributes_name := l_attribute_name;
2717 else
2718 if l_attribute_name is not null then
2719 l_null_attributes_name := l_null_attributes_name||','||l_attribute_name;
2720 end if;
2721 end if;
2722 end if;
2723 else
2724 hr_utility.set_location('column type is '||i.column_type||l_proc,54);
2725 end if;
2726 hr_utility.set_location('going for checking range allowed for attribute '||l_proc,111);
2727 for j in c3(i.attribute_id) loop
2728 insert_rout_crit(p_attribute_id => i.attribute_id,
2729 p_used_for => p_used_for,
2730 p_rule_name => j.range_name,
2731 p_attribute_type => i.column_type,
2732 p_from_num => j.from_number,
2733 p_to_num => j.to_number,
2734 p_value_num => l_attribute_value_num,
2735 p_from_char => j.from_char,
2736 p_to_char => j.to_char,
2737 p_value_char => l_attribute_value_char,
2738 p_from_date => j.from_date,
2739 p_to_date => j.to_date,
2740 p_value_date => l_attribute_value_date);
2741 if i.column_type = 'V' then
2742 hr_utility.set_location('varchar,range '||j.from_char||' to '||j.to_char||l_proc,120 );
2743 check_value_range(p_value_char => l_attribute_value_char,
2744 p_from_char => j.from_char,
2745 p_to_char => j.to_char,
2746 p_in_range => l_in_range,
2747 p_can_approve => l_approver_flag);
2748 elsif i.column_type = 'N' then
2749 hr_utility.set_location('number,range '||to_char(j.from_number)||' to '||to_char(j.to_number)||l_proc,130 );
2750 check_value_range(p_value_num => l_attribute_value_num,
2751 p_from_num => j.from_number,
2752 p_to_num => j.to_number,
2753 p_in_range => l_in_range,
2754 p_can_approve => l_approver_flag);
2755 elsif i.column_type = 'D' then
2756 hr_utility.set_location('number,range '||to_char(j.from_date,'ddmmRRRR')||' to '||to_char(j.to_date,'ddmmRRRR')||l_proc,140 );
2757 check_value_range(p_value_date => l_attribute_value_date,
2758 p_from_date => j.from_date,
2759 p_to_date => j.to_date,
2760 p_in_range => l_in_range,
2761 p_can_approve => l_approver_flag);
2762 end if;
2763 if l_in_range = FALSE then
2764 hr_utility.set_location('Value not in range deselect range '||l_proc,150);
2765 for k in 0..(l_rule_cnt-1) loop
2766 if j.range_name = l_hierarchy(k).range_name and j.routing_category_id = l_hierarchy(k).routing_category_id then
2767 l_hierarchy(k).selected_flag := FALSE ;
2768 hr_utility.set_location('catg deleted'||to_char(j.routing_category_id)||j.range_name||l_proc,155);
2769 end if;
2770 end loop;
2771 end if;
2772 end loop;
2773 end loop;
2774 hr_utility.set_location('Picking selected range '||l_proc,160);
2775 l_range_found_flag := FALSE;
2776 for i in 0..(l_rule_cnt - 1) loop
2777 if l_hierarchy(i).selected_flag = TRUE then
2778 if l_range_found_flag = TRUE then
2779 -- hard coding the value of routing catg to 0
2780 p_routing_category_id := 0;
2781 p_status_flag := 11;
2782 hr_utility.set_location('more than one routing category applicable '||l_proc,162);
2783 hr_utility.set_message(8302,'PQH_MORE_ROUTCAT_APPLICABLE');
2784 hr_utility.set_message_token('ATTRIBUTES', l_null_attributes_name);
2785 else
2786 p_routing_category_id := l_hierarchy(i).routing_category_id;
2787 p_range_name := l_hierarchy(i).range_name;
2788 g_list_range := p_range_name;
2789 l_range_found_flag := TRUE ;
2790 hr_utility.set_location('catg sele'||to_char(p_routing_category_id)||p_range_name||l_proc,165);
2791 delete_rout_crit(p_used_for => p_used_for,
2792 p_rule_name => p_range_name);
2793 end if;
2794 end if;
2795 end loop;
2796 if p_status_flag = 0 and l_range_found_flag = TRUE then
2797 open c4;
2798 fetch c4 into p_member_cd,p_routing_list_id,p_pos_str_id;
2799 if c4%notfound then
2800 hr_utility.set_location('Rout_cat not exist '||to_char(p_routing_category_id)||l_proc,170);
2801 hr_utility.set_message(8302,'PQH_ROUTCAT_NOT_EXISTS');
2802 hr_utility.raise_error;
2803 else
2804 hr_utility.set_location('details member_cd is '||p_member_cd||' ,RL is'||to_char(p_routing_list_id)||' , PS is '||to_char(p_pos_str_id)||l_proc,180);
2805 end if;
2806 close c4;
2807 elsif p_status_flag = 11 then
2808 hr_utility.set_location('more than one range applicable'||l_proc,190);
2809 else
2810 list_rout_crit;
2811 hr_utility.set_location('no range applicable'||p_status_flag||l_proc,192);
2812 -- check standard setup can be taken or not if yes then call it.
2813 select count(*) into l_standard_setup
2814 from pqh_routing_categories
2815 where transaction_category_id = p_tran_cat_id
2816 and nvl(default_flag,'X') ='Y'
2817 and nvl(delete_flag,'N') = 'N'
2818 and nvl(enable_flag,'X') = 'Y';
2819 if nvl(l_standard_setup,0) > 0 then
2820 hr_utility.set_location('calling standard setup'||l_proc,200);
2821 list_range_check(p_tran_cat_id => p_tran_cat_id,
2822 p_member_cd => p_member_cd,
2823 p_used_for => 'L',
2824 p_routing_list_id => p_routing_list_id,
2825 p_pos_str_id => p_pos_str_id,
2826 p_routing_category_id => p_routing_category_id,
2827 p_status_flag => p_status_flag);
2828 hr_utility.set_location('out of standard setup'||l_proc,200);
2829 else
2830 -- hard coding the value of routing catg to 0
2831 p_routing_category_id := 0;
2832 p_status_flag := 11;
2833 hr_utility.set_message(8302,'PQH_NO_RANGE_SELECTED');
2834 hr_utility.set_message_token('ATTRIBUTES', l_attributes_name);
2835 end if;
2836 end if;
2837 else
2838 -- no ranges are defined.
2839 hr_utility.set_location('no range defined'||l_proc,210);
2840 select count(*) into l_rout_cat
2841 from pqh_routing_categories
2842 where transaction_category_id = p_tran_cat_id
2843 and nvl(delete_flag,'N') = 'N'
2844 and nvl(enable_flag,'X') = 'Y';
2845 if l_rout_cat =1 then
2846 hr_utility.set_location('only one routing category with no ranges'||l_proc,220);
2847 p_range_name := '';
2848 g_list_range := '';
2849 begin
2850 select tc.member_cd,rc.routing_list_id,rc.position_structure_id,rc.routing_category_id
2851 into p_member_cd,p_routing_list_id,p_pos_str_id,p_routing_category_id
2852 from pqh_routing_categories rc, pqh_transaction_categories tc
2853 where tc.transaction_category_id = p_tran_cat_id
2854 and tc.transaction_category_id = rc.transaction_category_id
2855 and nvl(rc.enable_flag,'X') = 'Y'
2856 and nvl(rc.delete_flag,'N') = 'N' ;
2857
2858 hr_utility.set_location('details member_cd is '||p_member_cd||' ,RL is'||to_char(p_routing_list_id)||' , PS is '||to_char(p_pos_str_id)||l_proc,240);
2859 p_status_flag := 0;
2860 exception
2861 when others then
2862 hr_utility.set_location('Rout_cat does not exist '||l_proc,230);
2863 hr_utility.set_message(8302,'PQH_ROUTCAT_NOT_EXISTS');
2864 hr_utility.raise_error;
2865 end;
2866 end if;
2867 end if;
2868 hr_utility.set_location('Exiting '||l_proc,1000);
2869 exception when others then
2870 p_member_cd := null;
2871 p_routing_list_id := null;
2872 p_pos_str_id := null;
2873 p_routing_category_id := null;
2874 p_range_name := null;
2875 p_status_flag := null;
2876 raise;
2877 end list_range_check;
2878
2879 procedure assignment_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2880 p_from_clause in pqh_table_route.from_clause%type,
2881 p_assignment_id in per_all_assignments_f.assignment_id%type,
2882 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
2883 p_value_date in date,
2884 p_used_for in varchar2 default null,
2885 p_applicable_flag out nocopy boolean,
2886 p_status_flag out nocopy number,
2887 p_can_approve out nocopy boolean)
2888 as
2889 cursor c1 is
2890 select distinct range_name, NVL(approver_flag,'N') approve_flag
2891 from pqh_attribute_ranges
2892 where assignment_id = p_assignment_id
2893 and routing_category_id = p_routing_category_id
2894 and nvl(enable_flag,'X') = 'Y'
2895 and nvl(delete_flag,'N') = 'N';
2896 cursor c2 is
2897 select att.attribute_id,att.attribute_name,att.column_name,att.column_type
2898 from pqh_attributes att, pqh_txn_category_attributes tca
2899 where att.attribute_id = tca.attribute_id and
2900 tca.transaction_category_id = p_tran_cat_id
2901 and tca.member_identifying_flag = 'Y'
2902 and nvl(att.enable_flag,'X') = 'Y';
2903 cursor c3(p_attribute_id number) is
2904 select range_name,from_char,to_char,from_date,to_date,from_number,to_number,approver_flag
2905 from pqh_attribute_ranges
2906 where attribute_id = p_attribute_id
2907 and assignment_id = p_assignment_id
2908 and routing_category_id = p_routing_category_id
2909 and nvl(delete_flag,'N') = 'N'
2910 and nvl(enable_flag,'X') = 'Y';
2911 l_attribute_value_char varchar2(2000);
2912 l_attribute_value_num number ;
2913 l_attribute_value_date date;
2914 type rule_rec is record (
2915 range_name varchar2(240),
2916 approve_flag boolean,
2917 selected_flag boolean ) ;
2918 type rule_tab is table of rule_rec
2919 index by binary_integer;
2920 l_assignment_rules rule_tab;
2921 l_rule_cnt number ;
2922 l_in_range boolean ;
2923 l_error_flag boolean := FALSE;
2924 l_approver_flag boolean;
2925 l_override_approver boolean;
2926 l_person_id fnd_user.employee_id%type;
2927 l_proc varchar2(256) := g_package||'assignment_applicable';
2928 begin
2929 hr_utility.set_location('Entering '||l_proc,10);
2930 if p_tran_cat_id is null then
2931 hr_utility.set_location('Trans_cat reqd '||l_proc,20);
2932 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
2933 hr_utility.raise_error;
2934 elsif p_from_clause is null then
2935 hr_utility.set_location('From clause reqd '||l_proc,30);
2936 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
2937 hr_utility.raise_error;
2938 end if;
2939 -- deletes the old records of the used for type from the plsql table
2940 delete_rout_crit(p_used_for => p_used_for);
2941 p_applicable_flag := FALSE;
2942 p_status_flag := 0;
2943 l_rule_cnt := 0;
2944 for i in c1 loop
2945 l_assignment_rules(l_rule_cnt).range_name := i.range_name ;
2946 l_assignment_rules(l_rule_cnt).selected_flag := TRUE ;
2947 --Added below if condition to check approval_flag
2948 --instead of defaulting to TRUE. Bug #2236178.
2949 if i.approve_flag = 'Y' then
2950 l_assignment_rules(l_rule_cnt).approve_flag := TRUE ;
2951 else
2952 l_assignment_rules(l_rule_cnt).approve_flag := FALSE ;
2953 end if;
2954 l_rule_cnt := l_rule_cnt + 1 ;
2955 end loop;
2956 hr_utility.set_location(' '||to_char(l_rule_cnt)||' rules for member '||l_proc,40);
2957 if l_rule_cnt <> 0 then
2958 for i in c2 loop
2959 hr_utility.set_location('Attribute is '||to_char(i.attribute_id)||l_proc,50 );
2960 if i.column_type ='V' or i.column_type ='C' then
2961 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
2962 begin
2963 execute immediate 'select '||i.column_name||' '||p_from_clause
2964 into l_attribute_value_char ;
2965 hr_utility.set_location('value for attribute is '||l_attribute_value_char||l_proc,60 );
2966 exception
2967 when no_data_found then
2968 hr_utility.set_location('no data in trans table'||l_proc,91);
2969 when others then
2970 hr_utility.set_location('error in select table'||l_proc,92);
2971 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
2972 hr_utility.raise_error;
2973 end;
2974 elsif i.column_type ='D' then
2975 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
2976 begin
2977 execute immediate 'select '||i.column_name||' '||p_from_clause
2978 into l_attribute_value_date ;
2979 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_date,'DDMMRRRR')||l_proc,70 );
2980 exception
2981 when no_data_found then
2982 hr_utility.set_location('no data in trans table'||l_proc,91);
2983 when others then
2984 hr_utility.set_location('error in select table'||l_proc,92);
2985 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
2986 hr_utility.raise_error;
2987 end;
2988 elsif i.column_type ='N' then
2989 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
2990 begin
2991 execute immediate 'select '||i.column_name||' '||p_from_clause
2992 into l_attribute_value_num ;
2993 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_num)||l_proc,80 );
2994 exception
2995 when no_data_found then
2996 hr_utility.set_location('no data in trans table'||l_proc,91);
2997 when others then
2998 hr_utility.set_location('error in select table'||l_proc,92);
2999 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3000 hr_utility.raise_error;
3001 end;
3002 end if;
3003 for j in c3(i.attribute_id) loop
3004 hr_utility.set_location('Picking ranges for attribute '||to_char(i.attribute_id)||l_proc,90 );
3005 insert_rout_crit(p_attribute_id => i.attribute_id,
3006 p_used_for => p_used_for,
3007 p_attribute_type => i.column_type,
3008 p_from_num => j.from_number,
3009 p_to_num => j.to_number,
3010 p_value_num => l_attribute_value_num,
3011 p_from_char => j.from_char,
3012 p_to_char => j.to_char,
3013 p_value_char => l_attribute_value_char,
3014 p_from_date => j.from_date,
3015 p_to_date => j.to_date,
3016 p_value_date => l_attribute_value_date);
3017 if i.column_type = 'V' or i.column_type = 'C' then
3018 hr_utility.set_location('varchar ,range is '||j.from_char||' to '||j.to_char||l_proc,100 );
3019 check_value_range(p_value_char => l_attribute_value_char,
3020 p_from_char => j.from_char,
3021 p_to_char => j.to_char,
3022 p_in_range => l_in_range,
3023 p_can_approve => l_approver_flag);
3024 elsif i.column_type = 'N' then
3025 hr_utility.set_location('number, range is '||to_char(j.from_number)||' to '||to_char(j.to_number)||l_proc,110 );
3026 check_value_range(p_value_num => l_attribute_value_num,
3027 p_from_num => j.from_number,
3028 p_to_num => j.to_number,
3029 p_in_range => l_in_range,
3030 p_can_approve => l_approver_flag);
3031 elsif i.column_type = 'D' then
3032 hr_utility.set_location('date,range '||to_char(j.from_date,'ddmmRRRR')||' to '||to_char(j.to_date,'ddmmRRRR')||l_proc,120 );
3033 check_value_range(p_value_date => l_attribute_value_date,
3034 p_from_date => j.from_date,
3035 p_to_date => j.to_date,
3036 p_in_range => l_in_range,
3037 p_can_approve => l_approver_flag);
3038 end if;
3039 for k in 0..(l_rule_cnt-1) loop
3040 if l_assignment_rules(k).range_name = j.range_name then
3041 if l_in_range = TRUE then
3042 if upper(j.approver_flag) = 'N' or l_approver_flag = FALSE then
3043 l_assignment_rules(k).approve_flag := FALSE ;
3044 end if;
3045 else
3046 l_assignment_rules(k).selected_flag := FALSE ;
3047 hr_utility.set_location('deleting the range'||l_proc,122);
3048 end if;
3049 end if;
3050 end loop;
3051 end loop;
3052 end loop;
3053 for i in 0..(l_rule_cnt-1) loop
3054 if l_assignment_rules(i).selected_flag = TRUE then
3055 if p_used_for = 'C' then
3056 g_current_member_range := l_assignment_rules(i).range_name;
3057 elsif p_used_for = 'N' then
3058 g_next_member_range := l_assignment_rules(i).range_name;
3059 end if;
3060 delete_rout_crit(p_used_for => p_used_for,
3061 p_rule_name => l_assignment_rules(i).range_name);
3062 p_can_approve := l_assignment_rules(i).approve_flag ;
3063 p_applicable_flag := TRUE;
3064 end if;
3065 end loop;
3066 else
3067 p_can_approve := FALSE;
3068 p_applicable_flag := TRUE;
3069 end if;
3070 if p_can_approve = FALSE then
3071 l_override_approver := override_approver(p_member_cd => 'S',
3072 p_routing_category_id => p_routing_category_id,
3073 p_assignment_id => p_assignment_id,
3074 p_role_id => '',
3075 p_user_id => '',
3076 p_position_id => '');
3077 if l_override_approver then
3078 p_can_approve := TRUE;
3079 end if;
3080 end if;
3081 if p_applicable_flag = TRUE then
3082 person_on_assignment(p_assignment_id => p_assignment_id,
3083 p_value_date => p_value_date,
3084 p_person_id => l_person_id);
3085 hr_utility.set_location('Person on assignment is '||to_char(l_person_id)||l_proc,140);
3086 if l_person_id is not null then
3087 person_has_user(p_person_id => l_person_id,
3088 p_value_date => p_value_date,
3089 p_applicable_flag => p_applicable_flag);
3090 if p_applicable_flag = TRUE then
3091 hr_utility.set_location('Person has user defined'||l_proc,150);
3092 else
3093 hr_utility.set_location('Person has no user '||l_proc,160);
3094 end if;
3095 else
3096 hr_utility.set_location('assignment has no person attached',180);
3097 p_applicable_flag := FALSE;
3098 end if;
3099 end if;
3100 hr_utility.set_location('Exiting '||l_proc,10000);
3101 exception when others then
3102 p_applicable_flag := null;
3103 p_status_flag := null;
3104 p_can_approve := null;
3105 raise;
3106 end assignment_applicable;
3107 -- Added for Tar 4085705.996
3108 Procedure is_std_rule (p_routing_category_id in number,
3109 p_position_id in number,
3110 p_range_name in varchar2,
3111 p_approver_flag out nocopy varchar2,
3112 p_is_std_rule_flag out nocopy varchar2) is
3113 --
3114 Cursor csr_std_rule is
3115 Select attribute_id,nvl(approver_flag,'N') approver_flag
3116 from pqh_attribute_ranges
3117 where position_id = p_position_id
3118 and routing_category_id = p_routing_category_id
3119 and nvl(delete_flag,'N') = 'N'
3120 and nvl(enable_flag,'X') ='Y'
3121 and range_name = p_range_name;
3122 --
3123 l_attr pqh_attribute_ranges.attribute_id%type;
3124 l_appr pqh_attribute_ranges.approver_flag%type;
3125 l_cnt number(15);
3126 l_proc varchar2(72) := 'is_std_rule';
3127 --
3128 Begin
3129 --
3130 hr_utility.set_location('Entering '||l_proc,5);
3131 l_cnt := 0;
3132 p_is_std_rule_flag := 'N';
3133 p_approver_flag := 'N';
3134 For attr_rec in csr_std_rule loop
3135 l_cnt := l_cnt + 1;
3136 l_attr := attr_rec.attribute_id;
3137 l_appr := attr_rec.approver_flag;
3138 End loop;
3139
3140 If l_cnt = 1 and l_attr is null then
3141 p_is_std_rule_flag := 'Y';
3142 p_approver_flag := l_appr;
3143 else
3144 p_is_std_rule_flag := 'N';
3145 p_approver_flag := 'Y';
3146 End if;
3147 hr_utility.set_location('Exiting '||l_proc,10000);
3148 --
3149 End;
3150 -- End of Added for Tar 4085705.996
3151
3152
3153
3154 procedure ps_element_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3155 p_from_clause in pqh_table_route.from_clause%type,
3156 p_position_id in pqh_position_transactions.position_id%type,
3157 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
3158 p_value_date in date,
3159 p_used_for in varchar2 default null,
3160 p_applicable_flag out nocopy boolean,
3161 p_status_flag out nocopy number,
3162 p_can_approve out nocopy boolean) as
3163 cursor c1 is select distinct range_name
3164 from pqh_attribute_ranges
3165 where position_id = p_position_id
3166 and routing_category_id = p_routing_category_id
3167 and nvl(delete_flag,'N') = 'N'
3168 and nvl(enable_flag,'X') ='Y';
3169 cursor c2 is
3170 select att.attribute_id,att.attribute_name,att.column_name,att.column_type
3171 from pqh_attributes att,pqh_txn_category_attributes tca
3172 where att.attribute_id = tca.attribute_id and
3173 tca.transaction_category_id = p_tran_cat_id
3174 and tca.member_identifying_flag = 'Y'
3175 and nvl(att.enable_flag,'X') = 'Y';
3176 cursor c3(p_attribute_id number) is
3177 select range_name,from_char,to_char,from_date,to_date,from_number,to_number,approver_flag
3178 from pqh_attribute_ranges
3179 where attribute_id = p_attribute_id
3180 and position_id = p_position_id
3181 and routing_category_id = p_routing_category_id
3182 and nvl(delete_flag,'N') = 'N'
3183 and nvl(enable_flag,'X') ='Y';
3184 l_attribute_value_char varchar2(2000);
3185 l_attribute_value_num number ;
3186 l_attribute_value_date date;
3187 type rule_rec is record (
3188 range_name varchar2(240),
3189 approve_flag boolean,
3190 selected_flag boolean ) ;
3191 type rule_tab is table of rule_rec
3192 index by binary_integer;
3193 l_position_rules rule_tab;
3194 l_rule_cnt number := 0 ;
3195 l_in_range boolean ;
3196 l_error_flag boolean := FALSE;
3197 l_approver_flag boolean;
3198 l_override_approver boolean;
3199 --
3200 -- Added for Tar 4085705.996
3201 l_appr pqh_attribute_ranges.approver_flag%type;
3202 l_std_rule varchar2(10);
3203 --
3204 -- End of Added for Tar 4085705.996
3205 l_proc varchar2(256) := g_package||'ps_element_applicable';
3206 begin
3207 hr_utility.set_location('Entering '||l_proc,10);
3208 p_applicable_flag := FALSE;
3209 p_status_flag := 0;
3210 if p_tran_cat_id is null then
3211 hr_utility.set_location('trans_cat reqd '||l_proc,20);
3212 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
3213 hr_utility.raise_error;
3214 elsif p_from_clause is null then
3215 hr_utility.set_location('from clause reqd '||l_proc,30);
3216 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
3217 hr_utility.raise_error;
3218 end if;
3219
3220 for i in c1 loop
3221 l_position_rules(l_rule_cnt).range_name := i.range_name ;
3222
3223 -- Added for Tar 4085705.996
3224
3225 is_std_rule(p_routing_category_id, p_position_id, i.range_name, l_appr, l_std_rule);
3226 If l_std_rule = 'Y' then
3227 If l_appr = 'Y' then
3228 l_position_rules(l_rule_cnt).approve_flag := TRUE ;
3229 Else
3230 l_position_rules(l_rule_cnt).approve_flag := FALSE ;
3231 End if;
3232 Else
3233 l_position_rules(l_rule_cnt).approve_flag := TRUE ;
3234 End if;
3235 -- l_position_rules(l_rule_cnt).approve_flag := TRUE ;
3236 -- End of Added for Tar 4085705.996
3237
3238 l_position_rules(l_rule_cnt).selected_flag := TRUE ;
3239 l_rule_cnt := l_rule_cnt + 1 ;
3240 end loop;
3241 hr_utility.set_location(' '||to_char(l_rule_cnt)||' rules for member '||l_proc,40);
3242 -- deletes the old records of the used for type from the plsql table
3243 delete_rout_crit(p_used_for => p_used_for);
3244 if l_rule_cnt <> 0 then
3245 hr_utility.set_location('ranges defined '||l_proc,50);
3246 for i in c2 loop
3247 hr_utility.set_location('Attribute is '||to_char(i.attribute_id)||l_proc,60 );
3248 if i.column_type ='V' or i.column_type ='C' then
3249 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
3250 begin
3251 execute immediate 'select '||i.column_name||' '||p_from_clause
3252 into l_attribute_value_char ;
3253 hr_utility.set_location('value for attribute is '||l_attribute_value_char||l_proc,70 );
3254 exception
3255 when no_data_found then
3256 hr_utility.set_location('no data in trans table'||l_proc,91);
3257 when others then
3258 hr_utility.set_location('error in select table'||l_proc,92);
3259 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3260 hr_utility.raise_error;
3261 end;
3262 elsif i.column_type ='D' then
3263 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
3264 begin
3265 execute immediate 'select '||i.column_name||' '||p_from_clause
3266 into l_attribute_value_date ;
3267 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_date,'DDMMRRRR')||l_proc,80 );
3268 exception
3269 when no_data_found then
3270 hr_utility.set_location('no data in trans table'||l_proc,91);
3271 when others then
3272 hr_utility.set_location('error in select table'||l_proc,92);
3273 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3274 hr_utility.raise_error;
3275 end;
3276 elsif i.column_type ='N' then
3277 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
3278 begin
3279 execute immediate 'select '||i.column_name||' '||p_from_clause
3280 into l_attribute_value_num ;
3281 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_num)||l_proc,90 );
3282 exception
3283 when no_data_found then
3284 hr_utility.set_location('no data in trans table'||l_proc,91);
3285 when others then
3286 hr_utility.set_location('error in select table'||l_proc,92);
3287 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3288 hr_utility.raise_error;
3289 end;
3290 end if;
3291 for j in c3(i.attribute_id) loop
3292 insert_rout_crit(p_attribute_id => i.attribute_id,
3293 p_used_for => p_used_for,
3294 p_attribute_type => i.column_type,
3295 p_from_num => j.from_number,
3296 p_to_num => j.to_number,
3297 p_value_num => l_attribute_value_num,
3298 p_from_char => j.from_char,
3299 p_to_char => j.to_char,
3300 p_value_char => l_attribute_value_char,
3301 p_from_date => j.from_date,
3302 p_to_date => j.to_date,
3303 p_value_date => l_attribute_value_date);
3304 if i.column_type = 'V' or i.column_type = 'C' then
3305 hr_utility.set_location('varchar,range '||j.from_char||' to '||j.to_char||l_proc,100 );
3306 check_value_range(p_value_char => l_attribute_value_char,
3307 p_from_char => j.from_char,
3308 p_to_char => j.to_char,
3309 p_in_range => l_in_range,
3310 p_can_approve => l_approver_flag);
3311 elsif i.column_type = 'N' then
3312 hr_utility.set_location('number,range '||to_char(j.from_number)||' to '||to_char(j.to_number)||l_proc,110 );
3313 hr_utility.set_location('number,value '||to_char(l_attribute_value_num)||l_proc,111 );
3314 check_value_range(p_value_num => l_attribute_value_num,
3315 p_from_num => j.from_number,
3316 p_to_num => j.to_number,
3317 p_in_range => l_in_range,
3318 p_can_approve => l_approver_flag);
3319 elsif i.column_type = 'D' then
3320 hr_utility.set_location('date,range '||to_char(j.from_date,'ddmmRRRR')||' to '||to_char(j.to_date,'ddmmRRRR')||l_proc,120 );
3321 check_value_range(p_value_date => l_attribute_value_date,
3322 p_from_date => j.from_date,
3323 p_to_date => j.to_date,
3324 p_in_range => l_in_range,
3325 p_can_approve => l_approver_flag);
3326 end if;
3327 for k in 0..(l_rule_cnt-1) loop
3328 if l_position_rules(k).range_name = j.range_name then
3329 hr_utility.set_location('range match '||l_proc,122);
3330 if l_in_range = TRUE then
3331 hr_utility.set_location('in range, '||l_proc,123);
3332 if upper(j.approver_flag) = 'N' or l_approver_flag = FALSE then
3333 l_position_rules(k).approve_flag := FALSE ;
3334 end if;
3335 else
3336 hr_utility.set_location('not in range, deselecting'||l_proc,125);
3337 l_position_rules(k).selected_flag := FALSE ;
3338 hr_utility.set_location('deleting the range'||l_proc,122);
3339 end if;
3340 end if;
3341 end loop;
3342 end loop;
3343 end loop ;
3344 for i in 0..(l_rule_cnt-1) loop
3345 if l_position_rules(i).selected_flag = TRUE then
3346 if p_used_for = 'C' then
3347 g_current_member_range := l_position_rules(i).range_name;
3348 elsif p_used_for = 'N' then
3349 g_next_member_range := l_position_rules(i).range_name;
3350 end if;
3351 delete_rout_crit(p_used_for => p_used_for,
3352 p_rule_name => l_position_rules(i).range_name);
3353 p_can_approve := l_position_rules(i).approve_flag ;
3354 p_applicable_flag := TRUE;
3355 end if;
3356 end loop;
3357 else
3358 hr_utility.set_location('No ranges, position applicable '||l_proc,127);
3359 p_can_approve := FALSE;
3360 p_applicable_flag := TRUE;
3361 end if;
3362 if p_can_approve = FALSE then
3363 hr_utility.set_location('position is not defined as approver '||l_proc,129);
3364 l_override_approver := override_approver(p_member_cd => 'P',
3365 p_routing_category_id => p_routing_category_id,
3366 p_assignment_id => '',
3367 p_role_id => '',
3368 p_user_id => '',
3369 p_position_id => p_position_id);
3370 if l_override_approver then
3371 p_can_approve := TRUE;
3372 hr_utility.set_location('position is defined as override approver '||l_proc,129);
3373 end if;
3374 end if;
3375 if p_applicable_flag = TRUE then
3376 position_occupied(p_position_id => p_position_id,
3377 p_value_date => p_value_date,
3378 p_applicable_flag => p_applicable_flag);
3379 if p_applicable_flag = TRUE then
3380 hr_utility.set_location('Position occupied by user '||l_proc,130);
3381 else
3382 hr_utility.set_location('Position unoccupied '||l_proc,140);
3383 end if;
3384 end if;
3385 hr_utility.set_location('Exiting '||l_proc,10000);
3386 exception when others then
3387 p_applicable_flag := null;
3388 p_status_flag := null;
3389 p_can_approve := null;
3390 raise;
3391 end ps_element_applicable;
3392
3393 procedure rl_member_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3394 p_from_clause in pqh_table_route.from_clause%type,
3395 p_member_id in pqh_routing_list_members.routing_list_member_id%type,
3396 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
3397 p_used_for in varchar2 default null,
3398 p_applicable_flag out nocopy boolean,
3399 p_status_flag out nocopy number,
3400 p_can_approve out nocopy boolean) as
3401 cursor c1 is select distinct range_name, NVL(approver_flag,'N') approve_flag
3402 from pqh_attribute_ranges
3403 where routing_list_member_id = p_member_id
3404 and routing_category_id = p_routing_category_id
3405 and nvl(delete_flag,'N') = 'N'
3406 and nvl(enable_flag,'X') ='Y';
3407 cursor c2 is
3408 select att.attribute_id,att.attribute_name,att.column_name,att.column_type
3409 from pqh_attributes att, pqh_txn_category_attributes tca
3410 where att.attribute_id = tca.attribute_id and
3411 tca.transaction_category_id = p_tran_cat_id
3412 and tca.member_identifying_flag = 'Y'
3413 and nvl(att.enable_flag,'X') = 'Y';
3414 cursor c3(p_attribute_id number) is
3415 select range_name,from_char,to_char,from_date,to_date,from_number,to_number,approver_flag
3416 from pqh_attribute_ranges
3417 where attribute_id = p_attribute_id
3418 and routing_list_member_id = p_member_id
3419 and routing_category_id = p_routing_category_id
3420 and nvl(delete_flag,'N') = 'N'
3421 and nvl(enable_flag,'X') ='Y';
3422 cursor c4 is select approver_flag
3423 from pqh_routing_list_members
3424 where routing_list_member_id = p_member_id
3425 and nvl(enable_flag,'X') = 'Y';
3426 l_attribute_value_char varchar2(2000);
3427 l_attribute_value_num number ;
3428 l_attribute_value_date date;
3429 type rule_rec is record (
3430 range_name varchar2(240),
3431 approve_flag boolean,
3432 selected_flag boolean ) ;
3433 type rule_tab is table of rule_rec
3434 index by binary_integer;
3435 l_member_rules rule_tab;
3436 l_rule_cnt number := 0 ;
3437 l_in_range boolean ;
3438 l_member_approve_flag pqh_routing_list_members.approver_flag%type;
3439 l_error_flag boolean := FALSE;
3440 l_approver_flag boolean;
3441 l_override_approver boolean;
3442 l_role_id number;
3443 l_user_id number;
3444 l_proc varchar2(256) := g_package||'rl_member_applicable';
3445 begin
3446 hr_utility.set_location('Entering '||l_proc,10);
3447 p_applicable_flag := FALSE ;
3448 p_status_flag := 0;
3449 if p_tran_cat_id is null then
3450 hr_utility.set_location('Trans_cat reqd '||l_proc,20);
3451 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
3452 hr_utility.raise_error;
3453 elsif p_from_clause is null then
3454 hr_utility.set_location('From clause reqd '||l_proc,30);
3455 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
3456 hr_utility.raise_error;
3457 end if;
3458 for i in c1 loop
3459 l_member_rules(l_rule_cnt).range_name := i.range_name ;
3460 l_member_rules(l_rule_cnt).selected_flag := TRUE ;
3461 --Added below if condition to check approval_flag
3462 --instead of defaulting to TRUE. Bug #2236178.
3463 if i.approve_flag = 'Y' then
3464 l_member_rules(l_rule_cnt).approve_flag := TRUE ;
3465 else
3466 l_member_rules(l_rule_cnt).approve_flag := FALSE ;
3467 end if;
3468 l_rule_cnt := l_rule_cnt + 1 ;
3469 end loop;
3470
3471 -- deletes the old records of the used for type from the plsql table
3472 delete_rout_crit(p_used_for => p_used_for);
3473 hr_utility.set_location(' '||to_char(l_rule_cnt)||' rules for member '||l_proc,40);
3474 if l_rule_cnt <> 0 then
3475 hr_utility.set_location('ranges defined for member'||l_proc,50);
3476 for i in c2 loop
3477 hr_utility.set_location('Attribute is '||to_char(i.attribute_id)||l_proc,60 );
3478 if i.column_type ='V' or i.column_type ='C' then
3479 hr_utility.set_location('column type is '||i.column_type||l_proc,61);
3480 begin
3481 execute immediate 'select '||i.column_name||' '||p_from_clause
3482 into l_attribute_value_char ;
3483 hr_utility.set_location('value for attribute is '||l_attribute_value_char||l_proc,70 );
3484 exception
3485 when no_data_found then
3486 hr_utility.set_location('no data in trans table'||l_proc,71);
3487 when others then
3488 hr_utility.set_location('error in select table'||l_proc,72);
3489 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3490 hr_utility.raise_error;
3491 end;
3492 elsif i.column_type ='D' then
3493 hr_utility.set_location('column type is '||i.column_type||l_proc,75);
3494 begin
3495 execute immediate 'select '||i.column_name||' '||p_from_clause
3496 into l_attribute_value_date ;
3497 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_date,'DDMMRRRR')||l_proc,80 );
3498 exception
3499 when no_data_found then
3500 hr_utility.set_location('no data in trans table'||l_proc,81);
3501 when others then
3502 hr_utility.set_location('error in select table'||l_proc,82);
3503 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3504 hr_utility.raise_error;
3505 end;
3506 elsif i.column_type ='N' then
3507 hr_utility.set_location('column type is '||i.column_type||l_proc,85);
3508 begin
3509 execute immediate 'select '||i.column_name||' '||p_from_clause
3510 into l_attribute_value_num ;
3511 hr_utility.set_location('value for attribute is '||to_char(l_attribute_value_num)||l_proc,90 );
3512 exception
3513 when no_data_found then
3514 hr_utility.set_location('no data in trans table'||l_proc,91);
3515 when others then
3516 hr_utility.set_location('error in select table'||l_proc,92);
3517 hr_utility.set_message(8302,'PQH_SELECT_FAILED');
3518 hr_utility.raise_error;
3519 end;
3520 end if;
3521 for j in c3(i.attribute_id) loop
3522 insert_rout_crit(p_attribute_id => i.attribute_id,
3523 p_used_for => p_used_for,
3524 p_attribute_type => i.column_type,
3525 p_from_num => j.from_number,
3526 p_to_num => j.to_number,
3527 p_value_num => l_attribute_value_num,
3528 p_from_char => j.from_char,
3529 p_to_char => j.to_char,
3530 p_value_char => l_attribute_value_char,
3531 p_from_date => j.from_date,
3532 p_to_date => j.to_date,
3533 p_value_date => l_attribute_value_date);
3534 hr_utility.set_location('ranges for attribute '||to_char(i.attribute_id)||l_proc,100 );
3535 if i.column_type = 'V' or i.column_type = 'C' then
3536 hr_utility.set_location('varchar,range '||j.from_char||' to '||j.to_char||l_proc,110 );
3537 check_value_range(p_value_char => l_attribute_value_char ,
3538 p_from_char => j.from_char,
3539 p_to_char => j.to_char,
3540 p_in_range => l_in_range,
3541 p_can_approve => l_approver_flag) ;
3542 elsif i.column_type = 'N' then
3543 hr_utility.set_location('number,range'||to_char(j.from_number)||' to '||to_char(j.to_number)||l_proc,120 );
3544 check_value_range(p_value_num => l_attribute_value_num,
3545 p_from_num => j.from_number,
3546 p_to_num => j.to_number,
3547 p_in_range => l_in_range,
3548 p_can_approve => l_approver_flag) ;
3549 elsif i.column_type = 'D' then
3550 hr_utility.set_location('date,range'||to_char(j.from_date,'ddmmRRRR')||' to '||to_char(j.to_date,'ddmmRRRR')||l_proc,130 );
3551 check_value_range(p_value_date => l_attribute_value_date,
3552 p_from_date => j.from_date,
3553 p_to_date => j.to_date,
3554 p_in_range => l_in_range,
3555 p_can_approve => l_approver_flag) ;
3556 end if;
3557 for k in 0..(l_rule_cnt-1) loop
3558 if l_member_rules(k).range_name = j.range_name then
3559 if l_in_range= TRUE then
3560 if nvl(upper(j.approver_flag),'N') = 'N' or l_approver_flag = FALSE then
3561 l_member_rules(k).approve_flag := FALSE ;
3562 end if;
3563 else
3564 l_member_rules(k).selected_flag := FALSE ;
3565 hr_utility.set_location('not in range, deselecting'||l_member_rules(k).range_name||l_proc,132);
3566 hr_utility.set_location('deleting the range'||l_proc,122);
3567 end if;
3568 exit;
3569 end if;
3570 end loop;
3571 end loop;
3572 end loop;
3573 for k in 0..(l_rule_cnt-1) loop
3574 if l_member_rules(k).selected_flag = TRUE then
3575 if l_member_rules(k).approve_flag = TRUE then
3576 p_can_approve := TRUE;
3577 hr_utility.set_location('can approve '||l_proc,133);
3578 else
3579 p_can_approve := FALSE;
3580 hr_utility.set_location('cant approve '||l_proc,134);
3581 end if;
3582 if p_used_for = 'C' then
3583 g_current_member_range := l_member_rules(k).range_name;
3584 elsif p_used_for = 'N' then
3585 g_next_member_range := l_member_rules(k).range_name;
3586 end if;
3587 delete_rout_crit(p_used_for => p_used_for,
3588 p_rule_name => l_member_rules(k).range_name);
3589 p_applicable_flag := TRUE;
3590 hr_utility.set_location('range match found '||l_member_rules(k).range_name||l_proc,135);
3591 exit;
3592 else
3593 hr_utility.set_location('cant approve '||l_proc,136);
3594 p_can_approve := FALSE;
3595 p_applicable_flag := FALSE;
3596 end if;
3597 end loop;
3598 else
3599 hr_utility.set_location('no rules '||l_proc,137) ;
3600 p_applicable_flag := TRUE;
3601 open c4;
3602 fetch c4 into l_member_approve_flag;
3603 if c4%notfound then
3604 hr_utility.set_location('error RL_member '||to_char(p_member_id)||l_proc,140) ;
3605 else
3606 hr_utility.set_location('going to check approve '||l_proc,142);
3607 if upper(l_member_approve_flag) = 'Y' then
3608 p_can_approve := TRUE ;
3609 hr_utility.set_location('can approve '||l_proc,143);
3610 else
3611 p_can_approve := FALSE ;
3612 hr_utility.set_location('cant approve '||l_proc,144);
3613 end if;
3614 end if;
3615 close c4;
3616 end if;
3617 if p_can_approve = FALSE then
3618 hr_utility.set_location('member is not defined as approver '||l_proc,129);
3619 get_role_user(p_member_id => p_member_id,
3620 p_user_id => l_user_id,
3621 p_role_id => l_role_id );
3622 l_override_approver := override_approver(p_member_cd => 'R',
3623 p_routing_category_id => p_routing_category_id,
3624 p_assignment_id => '',
3625 p_role_id => l_role_id,
3626 p_user_id => l_user_id,
3627 p_position_id => '');
3628 if l_override_approver then
3629 p_can_approve := TRUE;
3630 hr_utility.set_location('member is defined as override approver '||l_proc,129);
3631 end if;
3632 end if;
3633 hr_utility.set_location('Exiting '||l_proc,10000);
3634 exception when others then
3635 p_applicable_flag := null;
3636 p_status_flag := null;
3637 p_can_approve := null;
3638 raise;
3639 end rl_member_applicable;
3640
3641 procedure su_next_user(p_cur_assignment_id in number,
3642 p_value_date in date,
3643 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
3644 p_status_flag out nocopy number) as
3645 l_person_id per_all_assignments_f.supervisor_id%type;
3646 l_position_id per_all_assignments_f.position_id%type;
3647 cursor c2 is select supervisor_id
3648 from per_all_assignments_f
3649 where assignment_id = p_cur_assignment_id
3650 and primary_flag ='Y'
3651 and p_value_date between effective_start_date and effective_end_date;
3652 l_proc varchar2(256) := g_package||'su_next_user';
3653 begin
3654 hr_utility.set_location('Entering '||l_proc,10);
3655 open c2;
3656 fetch c2 into l_person_id;
3657 if c2%notfound then
3658 hr_utility.set_location('status eol '||l_proc,30);
3659 p_status_flag := 1 ;
3660 else
3661 hr_utility.set_location('found supervisor '||l_proc,40);
3662 if l_person_id is null then
3663 hr_utility.set_location('no supervisor defined'||l_proc,42);
3664 p_status_flag := 1 ;
3665 else
3666 hr_utility.set_location('supervisor exists'||l_proc,45);
3667 get_primary_asg_details(p_person_id => l_person_id,
3668 p_effective_date => p_value_date,
3669 p_assignment_id => p_assignment_id,
3670 p_position_id => l_position_id );
3671 if p_assignment_id is null then
3672 hr_utility.set_location('supervisor donot have assignment '||l_proc,50);
3673 hr_utility.set_message(8302,'PQH_SUPER_NO_ASG');
3674 hr_utility.raise_error;
3675 else
3676 hr_utility.set_location('supervisor assignment is '||to_char(p_assignment_id)||l_proc,60);
3677 p_status_flag := 0;
3678 end if;
3679 end if;
3680 end if;
3681 close c2;
3682 hr_utility.set_location('Exiting '||l_proc,10000);
3683 exception when others then
3684 p_assignment_id := null;
3685 p_status_flag := null;
3686 raise;
3687 end su_next_user;
3688
3689 procedure user_assignment(p_value_date in date,
3690 p_user_id in out nocopy fnd_user.user_id%type,
3691 p_user_name in out nocopy fnd_user.user_name%type,
3692 p_person_id out nocopy fnd_user.employee_id%type,
3693 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type) as
3694 l_error_flag boolean := FALSE;
3695 l_person_name varchar2(240);
3696 l_user_id fnd_user.user_id%type := p_user_id;
3697 l_user_name fnd_user.user_name%type := p_user_name;
3698 l_position_id number;
3699 l_proc varchar2(256) := g_package||'user_assignment';
3700 begin
3701 hr_utility.set_location('Entering '||l_proc,10);
3702 if p_user_id is null and p_user_name is null then
3703 hr_utility.set_location('Userid or user name reqd '||l_proc,20);
3704 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
3705 hr_utility.raise_error;
3706 elsif p_user_id is not null then
3707 get_user_id_details(p_user_id => p_user_id,
3708 p_user_name => p_user_name,
3709 p_employee_id => p_person_id);
3710 elsif p_user_name is not null then
3711 get_user_name_details(p_user_id => p_user_id,
3712 p_user_name => p_user_name,
3713 p_employee_id => p_person_id);
3714 end if;
3715 hr_utility.set_location('Value date is '||to_char(p_value_date,'dd/MM/RRRR')||l_proc,22);
3716 if p_person_id is null then
3717 if p_user_name is null or p_user_id is null then
3718 hr_utility.set_location('details not found for userid'||to_char(p_user_id)||',name '||p_user_name||l_proc,30 );
3719 hr_utility.set_message(8302,'PQH_INVALID_USER_ID');
3720 hr_utility.raise_error;
3721 else
3722 hr_utility.set_location('no employee defined for user '||l_proc,90);
3723 hr_utility.set_message(8302,'PQH_EMP_NOTFOR_USER');
3724 hr_utility.raise_error;
3725 end if;
3726 else
3727 hr_utility.set_location('employee # is '||to_char(p_person_id)||l_proc,40 );
3728 l_person_name := hr_general.decode_person_name(p_person_id =>p_person_id);
3729 get_primary_asg_details(p_person_id => p_person_id,
3730 p_effective_date => p_value_date,
3731 p_assignment_id => p_assignment_id,
3732 p_position_id => l_position_id );
3733 if p_assignment_id is null then
3734 hr_utility.set_location('primary assignment not found '||l_proc,50);
3735 hr_utility.set_message(8302,'PQH_NO_PRIMARY_ASSIGNMENT');
3736 hr_utility.set_message_token('PERSON',l_person_name);
3737 hr_utility.raise_error;
3738 else
3739 hr_utility.set_location('details found '||l_proc,52);
3740 end if;
3741 end if;
3742 hr_utility.set_location('Exiting '||l_proc,200);
3743 exception when others then
3744 p_user_id := l_user_id;
3745 p_user_name := l_user_name;
3746 p_person_id := null;
3747 p_assignment_id := null;
3748 raise;
3749 end user_assignment ;
3750
3751 procedure user_position_and_assignment(p_value_date in date,
3752 p_user_id in out nocopy fnd_user.user_id%type,
3753 p_user_name in out nocopy fnd_user.user_name%type,
3754 p_person_id out nocopy fnd_user.employee_id%type,
3755 p_position_id out nocopy pqh_position_transactions.position_id%type,
3756 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type) as
3757 l_error_flag boolean := FALSE;
3758 l_proc varchar2(256) := g_package||'user_position_and_assignment';
3759 l_effective_date date;
3760 l_person_name varchar2(240);
3761 l_user_id fnd_user.user_id%type := p_user_id;
3762 l_user_name fnd_user.user_name%type := p_user_name;
3763 l_position varchar2(240);
3764 begin
3765 hr_utility.set_location('Entering '||l_proc,10);
3766 if p_user_id is null and p_user_name is null then
3767 hr_utility.set_location('Userid or user name reqd '||l_proc,20);
3768 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
3769 hr_utility.raise_error;
3770 elsif p_user_id is not null then
3771 get_user_id_details(p_user_id => p_user_id,
3772 p_user_name => p_user_name,
3773 p_employee_id => p_person_id);
3774 elsif p_user_name is not null then
3775 get_user_name_details(p_user_id => p_user_id,
3776 p_user_name => p_user_name,
3777 p_employee_id => p_person_id);
3778 end if;
3779 hr_utility.set_location('Value date is '||to_char(p_value_date,'dd/MM/RRRR')||l_proc,22);
3780 if p_person_id is null then
3781 if p_user_name is null or p_user_id is null then
3782 hr_utility.set_location('details not found for userid'||to_char(p_user_id)||',name '||p_user_name||l_proc,30 );
3783 hr_utility.set_message(8302,'PQH_INVALID_USER_ID');
3784 hr_utility.raise_error;
3785 else
3786 hr_utility.set_location('no employee defined for user '||l_proc,90);
3787 hr_utility.set_message(8302,'PQH_EMP_NOTFOR_USER');
3788 hr_utility.raise_error;
3789 end if;
3790 else
3791 hr_utility.set_location('employee # is '||to_char(p_person_id)||l_proc,40 );
3792 l_person_name := hr_general.decode_person_name(p_person_id =>p_person_id);
3793 get_primary_asg_details(p_person_id => p_person_id,
3794 p_effective_date => p_value_date,
3795 p_assignment_id => p_assignment_id,
3796 p_position_id => p_position_id );
3797 if p_assignment_id is null then
3798 hr_utility.set_location('primary assignment not found '||l_proc,50);
3799 hr_utility.set_message(8302,'PQH_NO_PRIMARY_ASSIGNMENT');
3800 hr_utility.set_message_token('PERSON',l_person_name);
3801 hr_utility.raise_error;
3802 else
3803 hr_utility.set_location('details found '||l_proc,52);
3804 if p_position_id is null then
3805 hr_utility.set_location('primary assignment not for position '||l_proc,54);
3806 hr_utility.set_message(8302,'PQH_PRIMARY_ASG_NOT_POS');
3807 hr_utility.set_message_token('PERSON',l_person_name);
3808 hr_utility.raise_error;
3809 else
3810 hr_utility.set_location('position found '||p_position_id||l_proc,60);
3811 l_effective_date := hr_general.get_position_date_end(p_position_id => p_position_id);
3812 if (l_effective_date is null or l_effective_date > trunc(sysdate)) then
3813 hr_utility.set_location('valid position '||l_proc,61);
3814 else
3815 hr_utility.set_location('Position Eliminated '||l_proc,62);
3816 hr_utility.set_message(8302,'PQH_POSITION_ELIMINATED');
3817 l_position := hr_general.decode_position_latest_name(p_position_id);
3818 hr_utility.set_message_token('POSITION',l_position);
3819 hr_utility.raise_error;
3820 end if;
3821 end if;
3822 end if;
3823 end if;
3824 hr_utility.set_location('Exiting '||l_proc,200);
3825 exception
3826 when others then
3827 p_user_id := l_user_id;
3828 p_user_name := l_user_name;
3829 p_person_id := null;
3830 p_position_id := null;
3831 p_assignment_id := null;
3832 raise;
3833 end user_position_and_assignment ;
3834
3835 procedure prepare_from_clause(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3836 p_trans_id in pqh_routing_history.transaction_id%type,
3837 p_from_clause out nocopy pqh_table_route.from_clause%type ) as
3838 cursor c1 is select rou.from_clause,rou.where_clause
3839 from pqh_transaction_categories cat ,pqh_table_route rou
3840 where cat.transaction_category_id = p_tran_cat_id
3841 and cat.consolidated_table_route_id = rou.table_route_id ;
3842 l_from_clause pqh_table_route.from_clause%type ;
3843 l_where_clause_in pqh_table_route.where_clause%type ;
3844 l_where_clause_out pqh_table_route.where_clause%type ;
3845 l_error_flag boolean := FALSE ;
3846 l_proc varchar2(256) := g_package||'prepare_from_clause';
3847 begin
3848 hr_utility.set_location('Entering '||l_proc,10);
3849 if p_tran_cat_id is null then
3850 hr_utility.set_location('Transaction category id reqd '||l_proc,20);
3851 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
3852 hr_utility.raise_error;
3853 elsif p_trans_id is null then
3854 hr_utility.set_location('Transaction id reqd '||l_proc,30);
3855 hr_utility.set_message(8302,'PQH_FROM_CLAUSE_NULL');
3856 hr_utility.raise_error;
3857 end if;
3858 -- hr_utilIty.set_location('tran_cat passed is '||to_char(p_tran_cat_id)||l_proc,40);
3859 -- hr_utility.set_location('transid passed is '||to_char(p_trans_id)||l_proc,40);
3860 open c1;
3861 fetch c1 into l_from_clause,l_where_clause_in;
3862 if c1%notfound then
3863 hr_utility.set_location('error for '||to_char(p_tran_cat_id)||l_proc,40);
3864 else
3865 hr_utility.set_location('From clause '||substr(l_from_clause,1,45)||l_proc,50);
3866 hr_utility.set_location('where clause '||substr(l_where_clause_in,1,45)||l_proc,55);
3867 /*
3868 l_where_clause_in does not contain where and has column id which is
3869 to be replaced by the transaction id . This where clause is to be
3870 then linked with the l_from_clause to form the p_from_clause
3871 */
3872 -- p_from_clause := replace(l_from_clause,'<PK>',p_trans_id);
3873 pqh_refresh_data.replace_where_params(
3874 p_where_clause_in => l_where_clause_in,
3875 p_txn_tab_flag => 'Y',
3876 p_txn_id => p_trans_id,
3877 p_where_clause_out => l_where_clause_out);
3878 p_from_clause := 'from '||l_from_clause||' where '||l_where_clause_out ;
3879 end if;
3880 close c1;
3881 hr_utility.set_location('From : 1'||substr(p_from_clause,1,45)||l_proc,61);
3882 hr_utility.set_location('From : 2'||substr(p_from_clause,46,45)||l_proc,62);
3883 hr_utility.set_location('From : 3'||substr(p_from_clause,91,45)||l_proc,63);
3884 hr_utility.set_location('From : 4'||substr(p_from_clause,136,45)||l_proc,64);
3885 hr_utility.set_location('From : 5'||substr(p_from_clause,181,45)||l_proc,65);
3886 hr_utility.set_location('From : 6'||substr(p_from_clause,226,45)||l_proc,66);
3887 hr_utility.set_location('From : 7'||substr(p_from_clause,271,45)||l_proc,67);
3888 hr_utility.set_location('From : 8'||substr(p_from_clause,316,45)||l_proc,68);
3889 hr_utility.set_location('From : 9'||substr(p_from_clause,361,45)||l_proc,69);
3890 hr_utility.set_location('From : 10'||substr(p_from_clause,406,45)||l_proc,70);
3891 hr_utility.set_location('From : 11'||substr(p_from_clause,451,45)||l_proc,71);
3892 hr_utility.set_location('Exiting '||l_proc,10000);
3893 exception when others then
3894 p_from_clause := null;
3895 raise;
3896 end prepare_from_clause;
3897
3898 procedure check_value_range(p_from_num in pqh_attribute_ranges.from_number%type,
3899 p_to_num in pqh_attribute_ranges.to_number%type,
3900 p_value_num in pqh_attribute_ranges.to_number%type,
3901 p_in_range out nocopy boolean ,
3902 p_can_approve out nocopy boolean ) as
3903 l_proc varchar2(256) := g_package||'check_value_range_num';
3904 range_known exception;
3905 --pragma exception_init (range_known, 200000);
3906 begin
3907 hr_utility.set_location('Entering '||l_proc,10);
3908 --Modified p_value_num is null check to handle return can_approve TRUE
3909 --if either from_num or to_num range value is NULL
3910 if p_value_num is null then
3911 if p_from_num is not null and p_to_num is not null then
3912 if (p_from_num = 0 OR p_to_num = 0) then
3913 p_can_approve := TRUE;
3914 p_in_range := TRUE;
3915 else
3916 p_can_approve := FALSE;
3917 p_in_range := FALSE;
3918 end if;
3919 else
3920 p_can_approve := TRUE;
3921 p_in_range := TRUE;
3922 end if;
3923 raise range_known;
3924 end if;
3925
3926 if p_to_num IS NULL
3927 AND p_from_num IS NULL then
3928 p_in_range := TRUE;
3929 p_can_approve := TRUE;
3930 raise range_known;
3931 end if;
3932 if p_to_num is not null and p_from_num is not null then
3933 if p_value_num between p_from_num and p_to_num then
3934 p_in_range := TRUE;
3935 p_can_approve := TRUE;
3936 raise range_known;
3937 else
3938 p_in_range := FALSE;
3939 p_can_approve := FALSE;
3940 raise range_known;
3941 end if;
3942 else
3943 if p_to_num is not null then
3944 if p_to_num >= p_value_num then
3945 p_in_range := TRUE;
3946 p_can_approve := TRUE;
3947 raise range_known;
3948 else
3949 p_in_range := FALSE;
3950 p_can_approve := FALSE;
3951 raise range_known;
3952 end if;
3953 else
3954 if p_from_num <= p_value_num then
3955 p_in_range := TRUE;
3956 p_can_approve := TRUE;
3957 raise range_known;
3958 else
3959 p_in_range := FALSE;
3960 p_can_approve := FALSE;
3961 raise range_known;
3962 end if;
3963 end if;
3964 end if;
3965 exception
3966 when range_known then
3967 if p_in_range = TRUE then
3968 hr_utility.set_location('inside the range '||l_proc,100);
3969 else
3970 hr_utility.set_location('not inside the range '||l_proc,110);
3971 end if;
3972 hr_utility.set_location('Exiting '||l_proc,10000);
3973 when others then
3974 p_in_range := null;
3975 p_can_approve := null;
3976 raise;
3977 end check_value_range ;
3978
3979 procedure check_value_range (p_from_date in pqh_attribute_ranges.from_date%type,
3980 p_to_date in pqh_attribute_ranges.to_date%type,
3981 p_value_date in pqh_attribute_ranges.to_date%type,
3982 p_in_range out nocopy boolean ,
3983 p_can_approve out nocopy boolean ) as
3984 l_proc varchar2(256) := g_package||'check_value_range_date';
3985 range_known exception;
3986 begin
3987 hr_utility.set_location('Entering '||l_proc,10);
3988 if p_value_date is null then
3989 p_in_range := TRUE;
3990 if p_from_date is not null and p_to_date is not null then
3991 p_can_approve := FALSE;
3992 else
3993 p_can_approve := TRUE;
3994 end if;
3995 raise range_known;
3996 end if;
3997 hr_utility.set_location('value date not null '||l_proc,20);
3998 if p_to_date IS NULL
3999 AND p_from_date IS NULL then
4000 p_in_range := TRUE;
4001 p_can_approve := TRUE;
4002 raise range_known;
4003 end if;
4004 hr_utility.set_location('to_date and from_date null '||l_proc,30);
4005 if p_to_date is not null and p_from_date is not null then
4006 if p_value_date between p_from_date and p_to_date then
4007 p_in_range := TRUE;
4008 p_can_approve := TRUE;
4009 raise range_known;
4010 else
4011 p_in_range := FALSE;
4012 p_can_approve := FALSE;
4013 raise range_known;
4014 end if;
4015 hr_utility.set_location('to_date and from_date not null '||l_proc,40);
4016 else
4017 if p_to_date is not null then
4018 if p_to_date >= p_value_date then
4019 p_in_range := TRUE;
4020 p_can_approve := TRUE;
4021 raise range_known;
4022 else
4023 p_in_range := FALSE;
4024 p_can_approve := FALSE;
4025 raise range_known;
4026 end if;
4027 hr_utility.set_location('to_date is not null '||l_proc,50);
4028 else
4029 if p_from_date <= p_value_date then
4030 p_in_range := TRUE;
4031 p_can_approve := TRUE;
4032 raise range_known;
4033 else
4034 p_in_range := FALSE;
4035 p_can_approve := FALSE;
4036 raise range_known;
4037 end if;
4038 hr_utility.set_location('from_date is not null '||l_proc,60);
4039 end if;
4040 end if;
4041 exception
4042 when range_known then
4043 if p_in_range = TRUE then
4044 hr_utility.set_location('inside the range '||l_proc,100);
4045 else
4046 hr_utility.set_location('not inside the range '||l_proc,110);
4047 end if;
4048 hr_utility.set_location('Exiting '||l_proc,10000);
4049 when others then
4050 p_in_range := null;
4051 p_can_approve := null;
4052 raise;
4053 end check_value_range ;
4054
4055 procedure check_value_range (p_from_char in pqh_attribute_ranges.from_char%type,
4056 p_to_char in pqh_attribute_ranges.to_char%type,
4057 p_value_char in pqh_attribute_ranges.to_char%type,
4058 p_in_range out nocopy boolean,
4059 p_can_approve out nocopy boolean ) as
4060 --Added l_from_char and l_to_char parms by deenath.
4061 l_from_char pqh_attribute_ranges.from_char%type;
4062 l_to_char pqh_attribute_ranges.to_char%type;
4063 l_proc varchar2(256) := g_package||'check_value_range_char';
4064 range_known exception;
4065 begin
4066 hr_utility.set_location('Entering '||l_proc,10);
4067 --Added below checks for 'All Entities' so that these values are always in Range.
4068 l_from_char := p_from_char;
4069 l_to_char := p_to_char;
4070 if p_from_char = 'All Entities' then
4071 l_from_char := NULL;
4072 end if;
4073 if p_to_char = 'All Entities' then
4074 l_to_char := NULL;
4075 end if;
4076 --End of code addition. Replaced p_from_char and p_to_char with l_from_char and l_to_char.
4077 if p_value_char is null then
4078 if l_from_char is not null and l_to_char is not null then
4079 p_in_range := FALSE;
4080 p_can_approve := FALSE;
4081 else
4082 p_in_range := TRUE;
4083 p_can_approve := TRUE;
4084 end if;
4085 raise range_known;
4086 end if;
4087 if l_to_char IS NULL
4088 AND l_from_char IS NULL then
4089 p_in_range := TRUE;
4090 p_can_approve := TRUE;
4091 raise range_known;
4092 end if;
4093 if l_to_char is not null and l_from_char is not null then
4094 if p_value_char between l_from_char and l_to_char then
4095 p_in_range := TRUE;
4096 p_can_approve := TRUE;
4097 raise range_known;
4098 else
4099 p_in_range := FALSE;
4100 p_can_approve := FALSE;
4101 raise range_known;
4102 end if;
4103 else
4104 if l_to_char is not null then
4105 if l_to_char >= p_value_char then
4106 p_in_range := TRUE;
4107 p_can_approve := TRUE;
4108 raise range_known;
4109 else
4110 p_in_range := FALSE;
4111 p_can_approve := FALSE;
4112 raise range_known;
4113 end if;
4114 else
4115 if l_from_char <= p_value_char then
4116 p_in_range := TRUE;
4117 p_can_approve := TRUE;
4118 raise range_known;
4119 else
4120 p_in_range := FALSE;
4121 p_can_approve := FALSE;
4122 raise range_known;
4123 end if;
4124 end if;
4125 end if;
4126 exception
4127 when range_known then
4128 if p_in_range = TRUE then
4129 hr_utility.set_location('inside the range '||l_proc,100);
4130 else
4131 hr_utility.set_location('not inside the range '||l_proc,110);
4132 end if;
4133 hr_utility.set_location('Exiting '||l_proc,10000);
4134 when others then
4135 p_in_range := null;
4136 p_can_approve := null;
4137 raise;
4138 end check_value_range ;
4139
4140 function find_pos_structure(p_pos_str_ver_id in per_pos_structure_versions.pos_structure_version_id%type) return number is
4141 cursor c1 is select position_structure_id
4142 from per_pos_structure_versions
4143 where pos_structure_version_id = p_pos_str_ver_id ;
4144 l_proc varchar2(256) := g_package||'find_pos_structure';
4145 l_pos_str_id per_pos_structure_versions.position_structure_id%type;
4146 begin
4147 hr_utility.set_location('Entering '||l_proc,10);
4148 if p_pos_str_ver_id is null then
4149 hr_utility.set_location('Pos_str_version reqd '||l_proc,20);
4150 hr_utility.set_message(8302,'PQH_POS_STR_VER_REQD');
4151 hr_utility.raise_error;
4152 end if;
4153 open c1;
4154 fetch c1 into l_pos_str_id;
4155 if c1%notfound then
4156 hr_utility.set_location('Pos_str not there'||l_proc,30);
4157 else
4158 hr_utility.set_location('pos_str is '||to_char(l_pos_str_id)||l_proc,40);
4159 end if;
4160 close c1;
4161 hr_utility.set_location('Exiting '||l_proc,10000);
4162 return l_pos_str_id;
4163 end find_pos_structure;
4164
4165 function pos_str_version(p_pos_str_id in per_pos_structure_versions.position_structure_id%type) return number is
4166 cursor c1 is select max(pos_structure_version_id)
4167 from per_pos_structure_versions
4168 where position_structure_id = p_pos_str_id ;
4169 l_proc varchar2(256) := g_package||'pos_str_version';
4170 l_pos_str_ver_id number;
4171 begin
4172 hr_utility.set_location('Entering '||l_proc,10);
4173 if p_pos_str_id is null then
4174 hr_utility.set_location('Pos_str reqd '||l_proc,20);
4175 hr_utility.set_message(8302,'PQH_POS_STRUCT_ID_IS_NULL');
4176 hr_utility.raise_error;
4177 end if;
4178 open c1;
4179 fetch c1 into l_pos_str_ver_id;
4180 if c1%notfound then
4181 hr_utility.set_location('error getting ver for str '||to_char(p_pos_str_id)||l_proc,30);
4182 else
4183 hr_utility.set_location('ver for str is'||to_char(l_pos_str_ver_id)||l_proc,40);
4184 end if;
4185 close c1;
4186 hr_utility.set_location('Exiting '||l_proc,10000);
4187 return l_pos_str_ver_id;
4188 end pos_str_version;
4189
4190 procedure rlm_user_seq( p_routing_list_id in pqh_routing_lists.routing_list_id%type,
4191 p_old_user_id in number default null,
4192 p_old_role_id in number default null,
4193 p_old_member_id in number default null,
4194 p_role_id in out nocopy pqh_roles.role_id%type,
4195 p_role_name in out nocopy pqh_roles.role_name%type,
4196 p_user_id in out nocopy fnd_user.user_id%type,
4197 p_user_name in out nocopy fnd_user.user_name%type,
4198 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
4199 p_member_flag out nocopy boolean) as
4200
4201 l_role_id pqh_roles.role_id%type := p_role_id;
4202 l_role_name pqh_roles.role_name%type := p_role_name;
4203 l_user_id fnd_user.user_id%type := p_user_id;
4204 l_user_name fnd_user.user_name%type := p_user_name;
4205
4206 cursor c1 is select role_id,role_name
4207 from pqh_roles
4208 where role_name = nvl(p_role_name,role_name)
4209 and role_id = nvl(p_role_id,role_id)
4210 and nvl(enable_flag,'X') ='Y';
4211 cursor c2 is select routing_list_member_id
4212 from pqh_routing_list_members
4213 where routing_list_id = p_routing_list_id
4214 and role_id = p_role_id
4215 and user_id = p_user_id
4216 and nvl(enable_flag,'X') ='Y';
4217 cursor c3 is select routing_list_member_id
4218 from pqh_routing_list_members
4219 where routing_list_id = p_routing_list_id
4220 and role_id = p_role_id and user_id is null
4221 and nvl(enable_flag,'X') ='Y';
4222
4223 -- finding new member based on old member and new routing list
4224 cursor c4 is select routing_list_member_id
4225 from pqh_routing_list_members
4226 where routing_list_id = p_routing_list_id
4227 and role_id = p_old_role_id
4228 and user_id = p_old_user_id
4229 and nvl(enable_flag,'X') ='Y';
4230 cursor c5 is select routing_list_member_id
4231 from pqh_routing_list_members
4232 where routing_list_id = p_routing_list_id
4233 and role_id = p_old_role_id
4234 and (user_id is null or user_id = p_user_id)
4235 and nvl(enable_flag,'X') ='Y';
4236 l_proc varchar2(256) := g_package||'rlm_user_seq';
4237 l_member_check varchar2(1) := 'Y';
4238 l_employee_id number;
4239 begin
4240 hr_utility.set_location('Entering '||l_proc,10);
4241 p_member_flag := TRUE;
4242 if p_role_id is null and p_role_name is null then
4243 hr_utility.set_location('Roleid or name reqd '||l_proc,12);
4244 hr_utility.set_message(8302,'PQH_ROLEID_OR_NAME_REQD');
4245 hr_utility.raise_error;
4246 elsif p_user_id is null and p_user_name is null then
4247 hr_utility.set_location('Userid or name reqd '||l_proc,20);
4248 hr_utility.set_message(8302,'PQH_USERID_OR_NAME_REQD');
4249 hr_utility.raise_error;
4250 elsif p_routing_list_id is null then
4251 hr_utility.set_location('RL reqd for finding member '||l_proc,30);
4252 hr_utility.set_message(8302,'PQH_ROUTING_LIST_REQD');
4253 hr_utility.raise_error;
4254 end if;
4255 if p_role_id is null or p_role_name is null then
4256 open c1;
4257 fetch c1 into p_role_id,p_role_name;
4258 if c1%notfound then
4259 hr_utility.set_location('role id or role_name is wrong'||l_proc,40);
4260 else
4261 hr_utility.set_location('role id is '||to_char(p_role_id)||l_proc,45);
4262 hr_utility.set_location('role name is '||p_role_name||l_proc,50);
4263 end if;
4264 close c1;
4265 end if;
4266 if p_user_id is not null then
4267 get_user_id_details(p_user_id => p_user_id,
4268 p_user_name => p_user_name,
4269 p_employee_id => l_employee_id);
4270 elsif p_user_name is not null then
4271 get_user_name_details(p_user_id => p_user_id,
4272 p_user_name => p_user_name,
4273 p_employee_id => l_employee_id);
4274 end if;
4275 hr_utility.set_location('user id is '||to_char(p_user_id)||l_proc,52);
4276 hr_utility.set_location('user name is '||p_user_name||l_proc,53);
4277 if p_old_role_id is not null then
4278 if p_role_id = p_old_role_id then
4279 hr_utility.set_location('person of the same role opening trans'||l_proc,54);
4280 if p_old_user_id is null then
4281 hr_utility.set_location('transaction was routed to role alone'||l_proc,55);
4282 l_member_check := 'N' ;
4283 else
4284 hr_utility.set_location('transaction was routed to role + user'||l_proc,56);
4285 if p_old_user_id = p_user_id then
4286 hr_utility.set_location('same user opening trans'||l_proc,57);
4287 l_member_check := 'N' ;
4288 else
4289 hr_utility.set_location('different user opening trans'||l_proc,58);
4290 end if;
4291 end if;
4292 else
4293 hr_utility.set_location('person of the different role opening trans'||l_proc,59);
4294 end if;
4295 else
4296 hr_utility.set_location('no routing history'||l_proc,59);
4297 end if;
4298 if l_member_check = 'Y' then
4299 hr_utility.set_location('checking current user and/or Role in RL '||l_proc,60);
4300 open c2;
4301 fetch c2 into p_member_id;
4302 if c2%notfound then
4303 hr_utility.set_location('role user combination does not exist in RL members '||l_proc,61);
4304 hr_utility.set_location('checking just role as the member in RL '||l_proc,62);
4305 open c3;
4306 fetch c3 into p_member_id;
4307 if c3%notfound then
4308 hr_utility.set_location('role alone also does not exist in RL member '||l_proc,64);
4309 p_member_flag := FALSE;
4310 else
4311 hr_utility.set_location('member exists'||l_proc,70);
4312 p_member_flag := TRUE;
4313 end if;
4314 close c3;
4315 else
4316 hr_utility.set_location('member exists'||l_proc,72);
4317 p_member_flag := TRUE;
4318 end if;
4319 close c2;
4320 else
4321 if p_old_member_id is not null then
4322 -- old member is to be used for finding out next member
4323 if p_old_user_id is null then
4324 open c5;
4325 fetch c5 into p_member_id;
4326 if c5%notfound then
4327 p_member_flag := FALSE;
4328 else
4329 p_member_flag := TRUE;
4330 end if;
4331 close c5;
4332 else
4333 open c4;
4334 fetch c4 into p_member_id;
4335 if c4%notfound then
4336 p_member_flag := FALSE;
4337 else
4338 p_member_flag := TRUE;
4339 end if;
4340 close c4;
4341 end if;
4342 hr_utility.set_location('new member is'||p_member_id||l_proc,73);
4343 hr_utility.set_location('old member is'||p_old_member_id||l_proc,73);
4344 else
4345 p_member_flag := FALSE;
4346 hr_utility.set_location('old member is null'||l_proc,74);
4347 end if;
4348 hr_utility.set_location('Routing history user had trans '||l_proc,75);
4349 end if;
4350 hr_utility.set_location('Exiting '||l_proc,10000);
4351 exception when others then
4352 p_role_id := l_role_id;
4353 p_role_name := l_role_name;
4354 p_user_id := l_user_id;
4355 p_user_name := l_user_name;
4356 p_member_id := null;
4357 p_member_flag := null;
4358 raise;
4359 end rlm_user_seq;
4360
4361 procedure routing_current (p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
4362 p_trans_id in pqh_routing_history.transaction_id%type,
4363 p_history_flag out nocopy boolean,
4364 p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
4365 p_position_id out nocopy pqh_routing_history.forwarded_to_position_id%type,
4366 p_member_id out nocopy pqh_routing_history.forwarded_to_member_id%type,
4367 p_role_id out nocopy number,
4368 p_user_id out nocopy number,
4369 p_assignment_id out nocopy pqh_routing_history.forwarded_to_assignment_id%type,
4370 p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
4371 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
4372 p_routing_history_id out nocopy pqh_routing_history.routing_history_id%type,
4373 p_status_flag out nocopy number) as
4374 cursor c1 is select max(routing_history_id)
4375 from pqh_routing_history
4376 where transaction_category_id = p_tran_cat_id
4377 and transaction_id = p_trans_id
4378 and user_action_cd <> 'APPLY';
4379 cursor c2 is select rh.forwarded_to_position_id,rh.forwarded_to_role_id,rh.forwarded_to_user_id,rh.forwarded_to_member_id,
4380 rh.forwarded_to_assignment_id,rh.pos_structure_version_id,rc.routing_list_id
4381 from pqh_routing_history rh,pqh_routing_categories rc
4382 where routing_history_id = p_routing_history_id
4383 and rh.routing_category_id = rc.routing_category_id;
4384 l_error_flag boolean := FALSE;
4385 l_proc varchar2(256) := g_package||'routing_current';
4386 begin
4387 hr_utility.set_location('Entering '||l_proc,10);
4388 p_history_flag := TRUE;
4389 p_status_flag := 0;
4390 if p_tran_cat_id is null then
4391 hr_utility.set_location('Trans_cat reqd '||l_proc,20);
4392 hr_utility.set_message(8302,'PQH_TRAN_CAT_REQD');
4393 hr_utility.raise_error;
4394 elsif p_trans_id is null then
4395 hr_utility.set_location('Transaction id reqd '||l_proc,30);
4396 hr_utility.set_message(8302,'PQH_TRANSACTION_ID_REQD');
4397 hr_utility.raise_error;
4398 end if;
4399 open c1;
4400 fetch c1 into p_routing_history_id ;
4401 close c1;
4402 if p_routing_history_id is not null then
4403 open c2;
4404 fetch c2 into p_position_id,p_role_id,p_user_id,p_member_id,p_assignment_id,p_pos_str_ver_id,p_routing_list_id ;
4405 if c2%notfound then
4406 hr_utility.set_location('wrong routing history id'||l_proc,40);
4407 p_history_flag := FALSE;
4408 else
4409 if p_position_id is not null then
4410 p_old_member_cd := 'P' ;
4411 hr_utility.set_location('Pos '||to_char(p_position_id)||l_proc,50 );
4412 elsif p_routing_list_id is not null then
4413 p_old_member_cd := 'R' ;
4414 hr_utility.set_location('Member '||to_char(p_member_id)||l_proc,50 );
4415 elsif p_assignment_id is not null then
4416 p_old_member_cd := 'S' ;
4417 hr_utility.set_location('Assig '||to_char(p_assignment_id)||l_proc,50 );
4418 else
4419 hr_utility.set_location('Invalid data in rout_hist'||l_proc,80);
4420 p_history_flag := FALSE;
4421 hr_utility.set_message(8302,'PQH_INVALID_ROUT_HIST');
4422 hr_utility.raise_error;
4423 end if;
4424 close c2;
4425 end if;
4426 else
4427 hr_utility.set_location('no history '||l_proc,85);
4428 p_history_flag := FALSE;
4429 end if;
4430 hr_utility.set_location('Exiting '||l_proc,10000);
4431 exception when others then
4432 p_history_flag := null;
4433 p_old_member_cd := null;
4434 p_position_id := null;
4435 p_member_id := null;
4436 p_role_id := null;
4437 p_user_id := null;
4438 p_assignment_id := null;
4439 p_pos_str_ver_id := null;
4440 p_routing_list_id := null;
4441 p_routing_history_id := null;
4442 p_status_flag := null;
4443 raise;
4444 end routing_current;
4445
4446 procedure rl_next_user(p_routing_list_id in pqh_routing_list_members.routing_list_id%type,
4447 p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
4448 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
4449 p_role_id out nocopy pqh_routing_list_members.role_id%type,
4450 p_user_id out nocopy pqh_routing_list_members.user_id%type,
4451 p_status_flag out nocopy number)
4452 as
4453 cursor c1 is
4454 select routing_list_member_id,role_id,user_id
4455 from pqh_routing_list_members
4456 where routing_list_id = p_routing_list_id
4457 and seq_no = (select min(seq_no)
4458 from pqh_routing_list_members
4459 where routing_list_id = p_routing_list_id
4460 and seq_no > (select seq_no
4461 from pqh_routing_list_members
4462 where routing_list_id = p_routing_list_id
4463 and routing_list_member_id = p_cur_member_id)
4464 and nvl(enable_flag,'X') = 'Y');
4465 cursor c2 is select routing_list_member_id,role_id,user_id
4466 from pqh_routing_list_members
4467 where routing_list_id = p_routing_list_id
4468 and seq_no = (select min(seq_no)
4469 from pqh_routing_list_members
4470 where routing_list_id = p_routing_list_id
4471 and nvl(enable_flag,'X') = 'Y');
4472 l_error_flag boolean := FALSE;
4473 l_proc varchar2(256) := g_package||'rl_next_user';
4474 begin
4475 hr_utility.set_location('Entering '||l_proc,10);
4476 if p_routing_list_id is null then
4477 hr_utility.set_message(8302,'PQH_ROUTING_LIST_REQD ');
4478 hr_utility.raise_error;
4479 end if;
4480 p_status_flag := 0;
4481 if p_cur_member_id is not null then
4482 hr_utility.set_location('next member using '||to_char(p_cur_member_id)||l_proc,80);
4483 open c1;
4484 fetch c1 into p_member_id,p_role_id,p_user_id ;
4485 if c1%notfound then
4486 hr_utility.set_location('error getting next RL_member '||l_proc,89);
4487 hr_utility.set_location('for RL '||to_char(p_routing_list_id)||to_char(p_cur_member_id)||l_proc,90);
4488 p_status_flag := 1;
4489 else
4490 hr_utility.set_location('RL_members , next member is '||to_char(p_member_id)||l_proc,100);
4491 end if;
4492 close c1;
4493 else
4494 hr_utility.set_location('first member of the routing list'||l_proc,110);
4495 open c2;
4496 fetch c2 into p_member_id,p_role_id,p_user_id;
4497 if c2%notfound then
4498 hr_utility.set_location('error getting first RL_member for RL '||to_char(p_routing_list_id)||l_proc,120);
4499 p_status_flag := 1;
4500 else
4501 hr_utility.set_location('RL_members , next member is '||to_char(p_member_id)||l_proc,130);
4502 end if;
4503 close c2;
4504 end if;
4505 hr_utility.set_location('Exiting '||l_proc,10000);
4506 exception
4507 when others then
4508 p_member_id := null;
4509 p_role_id := null;
4510 p_user_id := null;
4511 p_status_flag := null;
4512 raise;
4513 end rl_next_user;
4514 procedure ph_next_user(p_cur_position_id in pqh_position_transactions.position_id%type,
4515 p_pos_str_ver_id in pqh_routing_history.pos_structure_version_id%type,
4516 p_position_id out nocopy pqh_position_transactions.position_id%type,
4517 p_status_flag out nocopy number ) as
4518 cursor c1(l_position_id number) is
4519 select parent_position_id
4520 from per_pos_structure_elements
4521 where subordinate_position_id = l_position_id
4522 and pos_structure_version_id = p_pos_str_ver_id ;
4523 l_proc varchar2(256) := g_package||'ph_next_user';
4524 l_effective_date date;
4525 begin
4526 hr_utility.set_location('Entering '||l_proc,10);
4527 if p_cur_position_id is null then
4528 hr_utility.set_message(8302,'PQH_CUR_OLD_POS_NULL');
4529 hr_utility.raise_error;
4530 elsif p_pos_str_ver_id is null then
4531 hr_utility.set_message(8302,'PQH_POS_STR_OR_VER_NULL');
4532 hr_utility.raise_error;
4533 end if;
4534 hr_utility.set_location('value of cur pos is'||to_char(p_cur_position_id),152);
4535 open c1(p_cur_position_id);
4536 fetch c1 into p_position_id ;
4537 if c1%notfound then
4538 hr_utility.set_location('eol set'||l_proc,200);
4539 p_status_flag := 1 ;
4540 else
4541 hr_utility.set_location('position found'||l_proc,210);
4542 l_effective_date := hr_general.get_position_date_end(p_position_id => p_position_id);
4543 if l_effective_date is not null then
4544 if l_effective_date < trunc(sysdate) then
4545 hr_utility.set_location('position eliminated'||l_proc,220);
4546 p_status_flag := 8 ;
4547 else
4548 hr_utility.set_location('valid position '||l_proc,230);
4549 p_status_flag := 0 ;
4550 end if;
4551 else
4552 hr_utility.set_location('valid position '||l_proc,240);
4553 p_status_flag := 0 ;
4554 end if;
4555 end if;
4556 close c1;
4557 hr_utility.set_location('Exiting '||l_proc,10000);
4558 exception
4559 when others then
4560 p_position_id := null;
4561 p_status_flag := null;
4562 raise;
4563 end ph_next_user;
4564
4565 function get_txn_cat_id(p_transaction_id in number,
4566 p_short_name in varchar2) return number is
4567 l_txn_cat_id number;
4568 begin
4569 if p_short_name ='POSITION_TRANSACTION' then
4570 select wf_transaction_category_id
4571 into l_txn_cat_id
4572 from pqh_position_transactions
4573 where position_transaction_id = p_transaction_id;
4574 elsif p_short_name ='BUDGET_WORKSHEET' then
4575 select wks.wf_transaction_category_id
4576 into l_txn_cat_id
4577 from pqh_worksheet_details wkd, pqh_worksheets wks
4578 where worksheet_detail_id = p_transaction_id
4579 and wks.worksheet_id = wkd.worksheet_id;
4580 elsif p_short_name ='PQH_BPR' then
4581 select wf_transaction_category_id
4582 into l_txn_cat_id
4583 from pqh_budget_pools
4584 where pool_id = p_transaction_id;
4585 end if;
4586 return l_txn_cat_id;
4587 exception
4588 when others then
4589 return l_txn_cat_id;
4590 end;
4591
4592 -- when transaction id is passed business group id is not even checked
4593 -- business group id is used for pulling transaction category id only.
4594 procedure valid_user_opening(p_business_group_id in number default null,
4595 p_short_name in varchar2 ,
4596 p_transaction_id in number default null,
4597 p_routing_history_id in number default null,
4598 p_wf_transaction_category_id out nocopy number,
4599 p_glb_transaction_category_id out nocopy number,
4600 p_role_id out nocopy number,
4601 p_role_template_id out nocopy number,
4602 p_status_flag out nocopy varchar2) is
4603 l_proc varchar2(256) := g_package||'valid_user_opening';
4604 l_freeze_status_cd varchar2(30);
4605 l_member_cd varchar2(30);
4606 l_txn_catg_name varchar2(240);
4607 l_user_id fnd_user.user_id%type := fnd_profile.value('USER_ID');
4608 l_user_name fnd_user.user_name%type := fnd_profile.value('USERNAME');
4609 l_scope_name varchar2(240);
4610 l_default_role_id number;
4611 l_routing_role_id number;
4612 l_routing_history_id number;
4613 l_person_id number;
4614 l_error_cd number;
4615 l_date_start date;
4616 l_actual_term_date date;
4617 l_session_date date;
4618
4619 cursor c_tcat_details(p_transaction_category_id number) is
4620 select member_cd,freeze_status_cd,name
4621 from pqh_transaction_categories
4622 where transaction_category_id = p_transaction_category_id
4623 and nvl(enable_flag,'Y') = 'Y';
4624 cursor c_get_person_id(p_user_id number) is
4625 select employee_id
4626 from fnd_user
4627 where user_id = p_user_id;
4628 cursor c_emp_term(p_date date) is
4629 select ppos.date_start, ppos.actual_termination_date
4630 from per_periods_of_service ppos
4631 where ppos.person_id = l_person_id
4632 and p_date between ppos.date_start and nvl(ppos.actual_termination_date, hr_general.end_of_time) ;
4633
4634 cursor c_emp_date_start is
4635 select min(ppos.date_start)
4636 from per_periods_of_service ppos
4637 where ppos.person_id = l_person_id;
4638
4639 cursor c_user_action is
4640 select user_action_cd
4641 from pqh_routing_history
4642 where routing_history_id = p_routing_history_id;
4643 --
4644 l_user_action_cd varchar2(30);
4645 --
4646 begin
4647 hr_utility.set_location('Entering '||l_proc,10);
4648 hr_utility.set_location('user is '||l_user_name||l_proc,15);
4649 p_status_flag := null;
4650
4651 --
4652 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
4653 -- record if one does not already exist.
4654 --
4655 begin
4656 SELECT effective_date
4657 INTO l_session_date
4658 FROM fnd_sessions
4659 WHERE session_id = userenv('sessionid');
4660 exception
4661 when others then
4662 insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'),trunc(sysdate));
4663 l_session_date := trunc(sysdate);
4664 end;
4665 --
4666 open c_get_person_id(p_user_id => l_user_id);
4667 fetch c_get_person_id into l_person_id;
4668 close c_get_person_id;
4669
4670 if l_person_id is null then
4671 p_status_flag := 1.1;
4672 hr_utility.set_message(8302, 'PQH_NOT_EMP');
4673 else
4674 open c_emp_term(l_session_date);
4675 fetch c_emp_term into l_date_start, l_actual_term_date;
4676 --
4677 if c_emp_term%notfound then
4678 p_status_flag := 1.1;
4679 hr_utility.set_message(8302,'PQH_EMP_TERMED');
4680 --
4681 open c_emp_date_start;
4682 fetch c_emp_date_start into l_date_start;
4683 close c_emp_date_start;
4684 --
4685 if l_date_start > l_session_date then
4686 p_status_flag := 1.2;
4687 hr_utility.set_message(8302, 'PQH_EMP_NOT_STARTED');
4688 end if;
4689 --
4690 end if;
4691 close c_emp_term;
4692 end if;
4693 p_glb_transaction_category_id := get_txn_cat(p_short_name);
4694 hr_utility.set_location('global txn_cat is '||p_glb_transaction_category_id||l_proc,20);
4695 if p_transaction_id is null then
4696 hr_utility.set_location('txn_id is '||p_transaction_id||l_proc,22);
4697 if p_business_group_id is not null then
4698 p_wf_transaction_category_id := get_txn_cat(p_short_name,p_business_group_id);
4699 else
4700 p_wf_transaction_category_id := p_glb_transaction_category_id;
4701 end if;
4702 else
4703 hr_utility.set_location('txn_id is there'||l_proc,23);
4704 p_wf_transaction_category_id := get_txn_cat_id(p_transaction_id => p_transaction_id,
4705 p_short_name => p_short_name);
4706 hr_utility.set_location('txn txncat is '||p_wf_transaction_category_id||l_proc,25);
4707 end if;
4708 hr_utility.set_location('local txn_cat is '||p_wf_transaction_category_id||l_proc,30);
4709 if p_wf_transaction_category_id is not null then
4710 open c_tcat_details(p_wf_transaction_category_id);
4711 fetch c_tcat_details into l_member_cd,l_freeze_status_cd,l_txn_catg_name;
4712 if c_tcat_details%notfound then
4713 hr_utility.set_location('txn_cat details does not exist '||l_proc,50);
4714 close c_tcat_details;
4715 p_status_flag := 1;
4716 hr_utility.set_message(8302,'PQH_INVALID_TXN_CAT_ID');
4717 hr_utility.set_message_token('TRANSACTION',l_txn_catg_name);
4718 else
4719 close c_tcat_details;
4720 hr_utility.set_location('txn_cat details pulled '||l_proc,60);
4721 if nvl(l_freeze_status_cd,'NOT_FROZEN') <> 'FREEZE_CATEGORY' then
4722 hr_utility.set_location('txn_cat not frozen'||l_proc,70);
4723 if p_wf_transaction_category_id <> p_glb_transaction_category_id then
4724 l_scope_name := hr_general.decode_organization(p_business_group_id);
4725 else
4726 l_scope_name := hr_general.decode_lookup('PQH_TCT_SCOPE','GLOBAL');
4727 end if;
4728 p_status_flag := 2;
4729 hr_utility.set_message(8302,'PQH_TXN_CAT_NOT_FROZEN');
4730 hr_utility.set_message_token('TRANSACTION',l_txn_catg_name);
4731 hr_utility.set_message_token('SCOPE',l_scope_name);
4732 end if;
4733 end if;
4734 else
4735 p_status_flag := 1;
4736 hr_utility.set_message(8302,'PQH_INVALID_TXN_CAT_ID');
4737 hr_utility.set_message_token('TRANSACTION',l_txn_catg_name);
4738 end if;
4739 hr_utility.set_location('status is '||p_status_flag||l_proc,601);
4740 if p_status_flag is null then
4741 hr_utility.set_location('going for default role'||l_proc,80);
4742 get_default_role(p_session_date => l_session_date,
4743 p_transaction_category_id => p_wf_transaction_category_id,
4744 p_person_id => l_person_id,
4745 p_user_id => l_user_id,
4746 p_role_id => l_default_role_id);
4747 hr_utility.set_location('default role is '||l_default_role_id||l_proc,80);
4748 if l_default_role_id is null then
4749 p_status_flag := 3;
4750 hr_utility.set_message(8302,'PQH_USER_HAS_NO_ROLE');
4751 elsif l_default_role_id = -1 then
4752 p_status_flag := 4;
4753 hr_utility.set_message(8302,'PQH_TXN_CAT_NOT_WF_ENABLED');
4754 hr_utility.set_message_token('TRANSACTION',l_txn_catg_name);
4755 elsif l_default_role_id = -2 then
4756 p_status_flag := 5;
4757 hr_utility.set_message(8302,'PQH_EMP_NOTFOR_USER');
4758 elsif l_default_role_id = -3 then
4759 p_status_flag := 6;
4760 end if;
4761 end if;
4762 hr_utility.set_location('status is '||p_status_flag||l_proc,602);
4763 if p_status_flag is null then
4764 hr_utility.set_location('going for routing role'||l_proc,90);
4765 if p_transaction_id is not null then
4766 hr_utility.set_location('opening from inbox '||l_proc,110);
4767 if p_routing_history_id is null then
4768 hr_utility.set_location('transaction was saved in inbox after start'||l_proc,115);
4769 else
4770 select max(routing_history_id)
4771 into l_routing_history_id
4772 from pqh_routing_history
4773 where routing_history_id > nvl(p_routing_history_id,0)
4774 and transaction_id = p_transaction_id
4775 and transaction_category_id = p_wf_transaction_category_id;
4776 if l_routing_history_id > 0 then
4777 p_status_flag := 11;
4778 hr_utility.set_message(8302,'PQH_TRANS_ROUTING_EXISTS');
4779 else
4780 hr_utility.set_location('checking routing role '||l_proc,120);
4781
4782 /* if transaction status (aka user_action_cd) is TIMEOUT then
4783 do not fetch routing role, so that user's default role is used,
4784 instead of the routing role. NS:08/08/2006: Bug 5436925 */
4785 open c_user_action;
4786 fetch c_user_action into l_user_action_cd;
4787 close c_user_action;
4788
4789 if (nvl(l_user_action_cd,'ERROR') <> 'TIMEOUT') then
4790 l_routing_role_id := get_routinghistory_role(p_routing_history_id => p_routing_history_id,
4791 p_user_id => l_user_id,
4792 p_user_name => l_user_name);
4793 end if;
4794 hr_utility.set_location('routing role is'||l_routing_role_id||l_proc,130);
4795 end if;
4796 end if;
4797 end if;
4798 if nvl(l_routing_role_id,-1) > 0 then
4799 hr_utility.set_location('routing role is to be used '||l_proc,150);
4800 p_role_id := l_routing_role_id ;
4801 else
4802 p_role_id := l_default_role_id ;
4803 hr_utility.set_location('default role is to be used '||l_proc,140);
4804 end if;
4805 end if;
4806 hr_utility.set_location('status is '||p_status_flag||l_proc,603);
4807 if p_status_flag is null and p_short_name ='POSITION_TRANSACTION' then
4808 hr_utility.set_location('fetching role template for PTX'||l_proc,160);
4809 p_role_template_id := get_role_template(p_role_id => p_role_id,
4810 p_transaction_category_id => p_glb_transaction_category_id);
4811 hr_utility.set_location('role template is'||p_role_template_id||l_proc,170);
4812 if p_role_template_id = -1 then
4813 p_status_flag := 21;
4814 end if;
4815 end if;
4816 hr_utility.set_location('status is '||p_status_flag||l_proc,604);
4817 if p_status_flag is null then
4818 hr_utility.set_location('validation for type of routing'||l_proc,180);
4819 if l_member_cd ='R' then
4820 hr_utility.set_location('user-role-validation '||l_proc,190);
4821 l_error_cd := check_user_role_details(p_role_id => p_role_id,
4822 p_user_id => l_user_id,
4823 p_session_date => l_session_date);
4824 if l_error_cd <> 0 then
4825 p_status_flag := 31;
4826 end if;
4827 elsif l_member_cd ='P' then
4828 hr_utility.set_location('user-position-validation '||l_proc,200);
4829 l_error_cd := check_user_pos_details(p_person_id => l_person_id,
4830 p_value_date => trunc(sysdate));
4831 if l_error_cd <> 0 then
4832 p_status_flag := 41;
4833 end if;
4834 elsif l_member_cd ='S' then
4835 hr_utility.set_location('user-assignment-validation '||l_proc,210);
4836 l_error_cd := check_user_asg_details(p_person_id => l_person_id,
4837 p_value_date => trunc(sysdate));
4838 if l_error_cd <> 0 then
4839 p_status_flag := 51;
4840 end if;
4841 end if;
4842 end if;
4843 hr_utility.set_location('status flag is '||p_status_flag||l_proc,220);
4844 hr_utility.set_location('Exiting '||l_proc,10000);
4845 exception
4846 when others then
4847 p_wf_transaction_category_id := null;
4848 p_glb_transaction_category_id := null;
4849 p_role_id := null;
4850 p_role_template_id := null;
4851 p_status_flag := null;
4852 raise;
4853 end valid_user_opening;
4854
4855 function get_user_default_role(p_user_id in number)
4856 return Number is
4857 --
4858 l_proc varchar2(256) := g_package||'get_user_default_role';
4859 l_role_id pqh_roles.role_id%type;
4860 --
4861 Cursor csr_def_rl is
4862 Select
4863 decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0) role_id
4864 from per_people_extra_info pei , fnd_user usr
4865 WHERE usr.user_id = p_user_id
4866 and usr.employee_id = pei.person_id
4867 and information_type = 'PQH_ROLE_USERS'
4868 /** Check if default role **/
4869 and nvl(pei.pei_information4,'N') = 'Y'
4870 /** Check if enabled **/
4871 and nvl(pei.pei_information5,'Y')='Y';
4872 --
4873 begin
4874 --
4875 hr_utility.set_location('Entering '||l_proc,5);
4876 --
4877 Open csr_def_rl;
4878 Fetch csr_def_rl into l_role_id;
4879 If csr_def_rl%notfound then
4880 Close csr_def_rl;
4881 Return NULL;
4882 End if;
4883
4884 Close csr_def_rl;
4885 Return l_role_id;
4886 --
4887 hr_utility.set_location('Exiting '||l_proc,10);
4888 --
4889 end get_user_default_role;
4890 --
4891 --
4892 end pqh_workflow;