[Home] [Help]
PACKAGE BODY: APPS.GHR_PDI_API
Source
1 PACKAGE BODY ghr_pdi_api AS
2 /* $Header: ghpdiapi.pkb 120.1 2006/01/17 06:20:19 sumarimu noship $ */
3
4
5 --Global Variables
6 g_package varchar2(200) := 'ghr_pdi_api';
7 PROCEDURE CREATE_PDI(
8 p_validate IN BOOLEAN default false,
9 p_position_description_id OUT NOCOPY number,
10 p_date_from IN date,
11 p_routing_grp_id IN number default null,
12 p_date_to IN date default null,
13 p_opm_cert_num IN ghr_position_descriptions.opm_cert_num%TYPE default null,
14 p_flsa IN ghr_position_descriptions.flsa%TYPE default null,
15 p_financial_statement IN ghr_position_descriptions.financial_statement%TYPE default null,
16 p_subject_to_ia_action IN ghr_position_descriptions.subject_to_ia_action%TYPE default null,
17 p_position_status IN ghr_position_descriptions.position_status%TYPE default null,
18 p_position_is IN ghr_position_descriptions.position_is%TYPE default null,
19 p_position_sensitivity IN ghr_position_descriptions.position_sensitivity%TYPE default null,
20 p_competitive_level IN ghr_position_descriptions.competitive_level%TYPE default null,
21 p_pd_remarks IN ghr_position_descriptions.pd_remarks%TYPE default null,
22 p_position_class_std IN ghr_position_descriptions.position_class_std%TYPE default null,
23 p_category IN ghr_position_descriptions.category%TYPE default null,
24 p_career_ladder IN ghr_position_descriptions.career_ladder%TYPE default null,
25 p_supervisor_name in varchar2 default hr_api.g_varchar2,
26 p_supervisor_title in varchar2 default hr_api.g_varchar2,
27 p_supervisor_date in date default hr_api.g_date,
28 p_manager_name in varchar2 default hr_api.g_varchar2,
29 p_manager_title in varchar2 default hr_api.g_varchar2,
30 p_manager_date in date default hr_api.g_date,
31 p_classifier_name in varchar2 default hr_api.g_varchar2,
32 p_classifier_title in varchar2 default hr_api.g_varchar2,
33 p_classifier_date in date default hr_api.g_date,
34 p_attribute_category in varchar2 default null,
35 p_attribute1 in varchar2 default null,
36 p_attribute2 in varchar2 default null,
37 p_attribute3 in varchar2 default null,
38 p_attribute4 in varchar2 default null,
39 p_attribute5 in varchar2 default null,
40 p_attribute6 in varchar2 default null,
41 p_attribute7 in varchar2 default null,
42 p_attribute8 in varchar2 default null,
43 p_attribute9 in varchar2 default null,
44 p_attribute10 in varchar2 default null,
45 p_attribute11 in varchar2 default null,
46 p_attribute12 in varchar2 default null,
47 p_attribute13 in varchar2 default null,
48 p_attribute14 in varchar2 default null,
49 p_attribute15 in varchar2 default null,
50 p_attribute16 in varchar2 default null,
51 p_attribute17 in varchar2 default null,
52 p_attribute18 in varchar2 default null,
53 p_attribute19 in varchar2 default null,
54 p_attribute20 in varchar2 default null,
55 p_business_group_id in number default null,
56 p_1_approved_flag in varchar2 default null,
57 p_1_user_name_acted_on in varchar2 default null,
58 p_1_action_taken in varchar2 default null,
59 p_2_user_name_routed_to in varchar2 default null,
60 p_2_groupbox_id in number default null,
61 p_2_routing_list_id in number default null,
62 p_2_routing_seq_number in number default null,
63 p_1_pd_routing_history_id out nocopy number,
64 p_1_pdh_object_version_number out nocopy number,
65 p_2_pdh_object_version_number out nocopy number,
66 p_2_pd_routing_history_id out nocopy number,
67 p_pdi_object_version_number out nocopy number)
68 IS
69 l_proc varchar2(72) := g_package||'create_pdi';
70 l_position_description_id ghr_position_descriptions.position_description_id%TYPE;
71 l_pdi_object_version_number number := 1;
72 /* Added by Dinkar. Karumuri to support workflow and routing of Position description*/
73 l_initiator_flag ghr_pd_routing_history.initiator_flag%TYPE;
74 l_reviewer_flag ghr_pd_routing_history.reviewer_flag%TYPE;
75 l_requester_flag ghr_pd_routing_history.requester_flag%TYPE;
76 l_authorizer_flag ghr_pd_routing_history.authorizer_flag%TYPE;
77 l_approver_flag ghr_pd_routing_history.approver_flag%TYPE;
78 l_approved_flag ghr_pd_routing_history.approved_flag%TYPE;
79 l_personnelist_flag ghr_pd_routing_history.personnelist_flag%TYPE;
80 l_user_name_employee_id per_people_f.person_id%TYPE;
81 l_user_name_emp_first_name per_people_f.first_name%TYPE;
82 l_user_name_emp_last_name per_people_f.last_name%TYPE;
83 l_user_name_emp_middle_names per_people_f.middle_names%TYPE;
84 l_2_routing_seq_number ghr_pd_routing_history.routing_seq_number%TYPE;
85 l_forward_to_name ghr_groupboxes.name%TYPE;
86 l_2_groupbox_id ghr_pd_routing_history.groupbox_id%TYPE;
87 l_2_user_name ghr_pd_routing_history.user_name%TYPE;
88 l_action_taken ghr_pd_routing_history.action_taken%TYPE;
89 l_item_key ghr_pd_routing_history.item_key%TYPE;
90 l_pd_routing_history_id ghr_pd_routing_history.pd_routing_history_id%TYPE;
91 -- Need to Make sure that we need this Cursor.
92 -- Open Issue is what do we use instead of effective_date.
93 /* Cursor C_user_emp_names is
94 select usr.employee_id,
95 per.first_name,
96 per.last_name,
97 per.middle_names
98 from per_people_f per,
99 fnd_user usr
100 where upper(usr.user_name) = upper(p_1_user_name_acted_on)
101 and per.person_id = usr.employee_id
102 and p_date_from
103 between per.effective_start_date
104 and per.effective_end_date;
105 */
106 -- Bug 4863608 Performance repository Changes for R12
107 CURSOR C_user_emp_names is
108 SELECT usr.employee_id,
109 per.first_name,
110 per.last_name,
111 per.middle_names
112 FROM per_people_f per,
113 fnd_user usr
114 WHERE usr.user_name = UPPER(p_1_user_name_acted_on)
115 AND per.person_id = usr.employee_id
116 AND p_date_from
117 BETWEEN per.effective_start_date
118 AND per.effective_end_date;
119
120 Cursor C_seq_number is
121 select rlm.seq_number,
122 rlm.groupbox_id,
123 rlm.user_name
124 from ghr_routing_list_members rlm
125 where rlm.routing_list_id = p_2_routing_list_id
126 order by rlm.seq_number asc;
127 Cursor c_history_exists is
128 select 1
129 from ghr_pd_routing_history pdh
130 where pdh.position_description_id = l_position_description_id;
131 Cursor c_groupbox_name is
132 select gbx.name
133 from ghr_groupboxes gbx
134 where gbx.groupbox_id = l_2_groupbox_id;
135 Cursor c_item_key_seq is
136 select ghr_pd_wf_item_key_s.nextval
137 from dual;
138
139 BEGIN
140 hr_utility.set_location('Now Entering:'||l_proc, 5);
141 -- Issue a savepoint if operating in validation only mode.
142 --IF p_validate THEN
143 SAVEPOINT create_pdi;
144 --END IF;
145 -- Call Before Process User Hook
146 --
147 begin
148 ghr_pdi_bk1.create_pdi_b (
149 p_date_from => p_date_from,
150 p_routing_grp_id => p_routing_grp_id,
151 p_date_to => p_date_to,
152 p_opm_cert_num => p_opm_cert_num,
153 p_flsa => p_flsa,
154 p_financial_statement => p_financial_statement,
155 p_subject_to_ia_action => p_subject_to_ia_action,
156 p_position_status => p_position_status,
157 p_position_is => p_position_is,
158 p_position_sensitivity => p_position_sensitivity,
159 p_competitive_level => p_competitive_level,
160 p_pd_remarks => p_pd_remarks,
161 p_position_class_std => p_position_class_std,
162 p_category => p_category,
163 p_career_ladder => p_career_ladder,
164 p_supervisor_name => p_supervisor_name,
165 p_supervisor_title => p_supervisor_title,
166 p_supervisor_date => p_supervisor_date,
167 p_manager_name => p_manager_name,
168 p_manager_title => p_manager_title,
169 p_manager_date => p_manager_date,
170 p_classifier_name => p_classifier_name,
171 p_classifier_title => p_classifier_title,
172 p_classifier_date => p_classifier_date,
173 p_attribute_category => p_attribute_category,
174 p_attribute1 => p_attribute1,
175 p_attribute2 => p_attribute2,
176 p_attribute3 => p_attribute3,
177 p_attribute4 => p_attribute4,
178 p_attribute5 => p_attribute5,
179 p_attribute6 => p_attribute6,
180 p_attribute7 => p_attribute7,
181 p_attribute8 => p_attribute8,
182 p_attribute9 => p_attribute9,
183 p_attribute10 => p_attribute10,
184 p_attribute11 => p_attribute11,
185 p_attribute12 => p_attribute12,
186 p_attribute13 => p_attribute13,
187 p_attribute14 => p_attribute14,
188 p_attribute15 => p_attribute15,
189 p_attribute16 => p_attribute16,
190 p_attribute17 => p_attribute17,
191 p_attribute18 => p_attribute18,
192 p_attribute19 => p_attribute19,
193 p_attribute20 => p_attribute20,
194 p_business_group_id => p_business_group_id,
195 p_1_approved_flag => p_1_approved_flag,
196 p_1_user_name_acted_on => p_1_user_name_acted_on,
197 p_1_action_taken => p_1_action_taken,
198 p_2_user_name_routed_to => p_2_user_name_routed_to,
199 p_2_groupbox_id => p_2_groupbox_id,
200 p_2_routing_list_id => p_2_routing_list_id,
201 p_2_routing_seq_number => p_2_routing_seq_number
202 );
203 exception
204 when hr_api.cannot_find_prog_unit then
205 hr_api.cannot_find_prog_unit_error
206 (p_module_name => 'create_pdi',
207 p_hook_type => 'BP'
208 );
209 end;
210 --
211 -- End of Before Process User Hook call
212 --
213 --
214 hr_utility.set_location(l_proc,6);
215 -- Validation in Addition to Row Handlers: There is no additional validation
216 -- Process Logic: The process logic is to call the row handlers to insert the information into
217 -- GHR_POSITION_DESCRIPTIONS and GHR_PD_CLASSIFICATIONS.
218 -- 1)First insert a row into GHR_POSITION_DESCRIPTIONS table.
219 ghr_pdi_ins.ins
220 (
221 p_position_description_id => l_position_description_id,
222 p_routing_group_id => p_routing_grp_id,
223 p_date_from => p_date_from,
224 p_date_to => p_date_to,
225 p_opm_cert_num => p_opm_cert_num,
226 p_flsa => p_flsa,
227 p_financial_statement => p_financial_statement,
228 p_subject_to_ia_action => p_subject_to_ia_action,
229 p_position_status => p_position_status,
230 p_position_is => p_position_is,
231 p_position_sensitivity => p_position_sensitivity,
232 p_competitive_level => p_competitive_level,
233 p_pd_remarks => p_pd_remarks,
234 p_position_class_std => p_position_class_std,
235 p_category => p_category,
236 p_career_ladder => p_career_ladder,
237 p_supervisor_name => p_supervisor_name,
238 p_supervisor_title => p_supervisor_title,
239 p_supervisor_date => p_supervisor_date,
240 p_manager_name => p_manager_name,
241 p_manager_title => p_manager_title,
242 p_manager_date => p_manager_date,
243 p_classifier_name => p_classifier_name,
244 p_classifier_title => p_classifier_title,
245 p_classifier_date => p_classifier_date,
246 p_attribute_category => p_attribute_category,
247 p_attribute1 => p_attribute1,
248 p_attribute2 => p_attribute2,
249 p_attribute3 => p_attribute3,
250 p_attribute4 => p_attribute4,
251 p_attribute5 => p_attribute5,
252 p_attribute6 => p_attribute6,
253 p_attribute7 => p_attribute7,
254 p_attribute8 => p_attribute8 ,
255 p_attribute9 => p_attribute9,
256 p_attribute10 => p_attribute10 ,
257 p_attribute11 => p_attribute11,
258 p_attribute12 => p_attribute12 ,
259 p_attribute13 => p_attribute13 ,
260 p_attribute14 => p_attribute14 ,
261 p_attribute15 => p_attribute15,
262 p_attribute16 => p_attribute16 ,
263 p_attribute17 => p_attribute17,
264 p_attribute18 => p_attribute18 ,
265 p_attribute19 => p_attribute19,
266 p_attribute20 => p_attribute20 ,
267 p_business_group_id => p_business_group_id ,
268 p_object_version_number => l_pdi_object_version_number
269 );
270 p_pdi_object_version_number := l_pdi_object_version_number;
271 p_position_description_id := l_position_description_id;
272 hr_utility.set_location('after pdi_ins.ins' ,6);
273 --insert_messages(2,l_position_description_id,l_proc);
274 --
275 --------------------------------------------------------------------------------------
276 -- Inserted by Dinkar. Karumuri for routing and Workflow on 05-AUG-1997
277 --
278 -- 3)Derive all parameters required to insert routing_history records.
279 -- Roles , Action_taken (and sequence Number if necessary)
280 l_action_taken := p_1_action_taken;
281 if p_1_user_name_acted_on is not null then
282 ghr_pdi_pkg.get_roles
283 (l_position_description_id,
284 p_routing_grp_id,
285 p_1_user_name_acted_on,
286 l_initiator_flag,
287 l_requester_flag,
288 l_authorizer_flag,
289 l_personnelist_flag,
290 l_approver_flag,
291 l_reviewer_flag
292 );
293 for user_emp_names in C_user_emp_names loop
294 l_user_name_employee_id := user_emp_names.employee_id;
295 l_user_name_emp_first_name := user_emp_names.first_name;
296 l_user_name_emp_last_name := user_emp_names.last_name;
297 l_user_name_emp_middle_names := user_emp_names.middle_names;
298 exit;
299 end loop;
300 else
301 hr_utility.set_message(8301,'GHR_38111_USER_NAME_REQD');
302 hr_utility.raise_error;
303
304 end if; -- End if for User Acted On condn.
305 l_action_taken := p_1_action_taken;
306 if l_action_taken is null then
307 l_action_taken := 'NO_ACTION';
308 end if;
309 hr_utility.set_location('before invalid condition',10);
310
311 if l_action_taken not in ('NOT_ROUTED','INITIATED','AUTHORIZED',
312 'NO_ACTION','REVIEWED','CANCELED','CLASSIFIED',
313 'REQUESTED')
314 then
315
316 hr_utility.set_message(8301,'GHR_38110_INVALID_ACTION_TAKEN');
317 hr_utility.raise_error;
318 end if;
319 -- to check if there is any routing information, if required.
320 if l_action_taken not in ('CANCELED','CLASSIFIED','NOT_ROUTED') then
321 if p_2_user_name_routed_to is null and
322 p_2_groupbox_id is null and
323 p_2_routing_list_id is null then
324 hr_utility.set_message(8301,'GHR_38280_NO_ROUTING_INFO');
325 hr_utility.raise_error;
326 end if;
327 end if;
328 hr_utility.set_location('before getting item key'||l_action_taken,10);
329 if nvl(l_action_taken,hr_api.g_varchar2) in
330 ('NOT_ROUTED','INITIATED','CLASSIFIED','AUTHORIZED','REQUESTED')
331 then
332
333 OPEN c_item_key_seq;
334 FETCH c_item_key_seq INTO l_item_key;
335 IF c_item_key_seq%NOTFOUND THEN
336
337 hr_utility.set_message(8301,'GHR_38625_NO_WF_ITEM_KEY_SEQ');
338 hr_utility.raise_error;
339 END IF;
340 CLOSE c_item_key_seq;
341 end if;
342
343 --insert_messages(5,l_position_description_id,'after item_key_seq');
344 -- write the first record into the routing history (actions done by the user)
345 if nvl(l_action_taken,hr_api.g_varchar2) not in
346 ('CANCELED') then
347 hr_utility.set_location('after not in cancled',10);
348 ghr_pdh_ins.ins
349 (
350 p_pd_routing_history_id => p_1_pd_routing_history_id,
351 p_position_description_id => l_position_description_id,
352 p_initiator_flag => nvl(l_initiator_flag,'N'),
353 p_requester_flag => nvl(l_requester_flag,'N'),
354 p_approver_flag => nvl(l_approver_flag,'N'),
355 p_reviewer_flag => nvl(l_reviewer_flag,'N') ,
356 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
357 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
358 p_approved_flag => nvl(p_1_approved_flag,'N'),
359 p_user_name => p_1_user_name_acted_on,
360 p_user_name_employee_id => l_user_name_employee_id,
361 p_user_name_emp_first_name => l_user_name_emp_first_name,
362 p_user_name_emp_last_name => l_user_name_emp_last_name ,
363 p_user_name_emp_middle_names =>l_user_name_emp_middle_names,
364 p_date_notification_sent => sysdate,
365 p_action_taken => l_action_taken,
366 p_object_version_number => p_1_pdh_object_version_number,
367 p_item_key => l_item_key,
368 p_validate => false
369 );
370 -- Insert 2nd record into routing_history for routing details
371 -- (exception when routing_status = 'NOT_ROUTED' )
372 if nvl(l_action_taken,hr_api.g_varchar2) not in ('CLASSIFIED','NOT_ROUTED') then
373 hr_utility.set_location('in the 2nd row',10);
374 l_2_routing_seq_number := p_2_routing_seq_number;
375 l_2_groupbox_id := p_2_groupbox_id;
376 l_2_user_name := p_2_user_name_routed_to;
377 -- derive the next sequence number for the speicific routing list if seq. number is not passed in
378 if p_2_routing_list_id is not null and p_2_routing_seq_number is null then
379 for rout_seq_numb in C_seq_number loop
380 l_2_routing_seq_number := rout_seq_numb.seq_number;
381 l_2_groupbox_id := rout_seq_numb.groupbox_id;
382 l_2_user_name := rout_seq_numb.user_name;
383 exit;
384 end loop;
385 if l_2_routing_seq_number is null then
386 hr_utility.set_message(8301,'GHR_38114_NO_MORE_SEQ_NUMBER' );
387 hr_utility.raise_error;
388 end if;
389 end if;
390
391 -- vravikan - Getting the next sequence number for workflow routing
392
393
394 ghr_pdh_ins.ins
395 (p_pd_routing_history_id => p_2_pd_routing_history_id,
396 p_position_description_id => l_position_description_id,
397 p_initiator_flag => 'N',
398 p_requester_flag => 'N',
399 p_approver_flag => 'N',
400 p_reviewer_flag => 'N',
401 p_authorizer_flag => 'N',
402 p_approved_flag => 'N',
403 p_personnelist_flag => 'N',
404 p_user_name => l_2_user_name,
405 p_groupbox_id => l_2_groupbox_id,
406 p_routing_list_id => p_2_routing_list_id,
407 p_routing_seq_number => l_2_routing_seq_number,
408 p_date_notification_sent => sysdate,
409
410 p_object_version_number => p_2_pdh_object_version_number,
411 p_item_key => l_item_key,
412
413 p_validate => false
414 );
415 end if;
416 else
417 hr_utility.set_message(8301,'GHR_38112_INVALID_API');
418 hr_utility.raise_error;
419 end if;
420 -----------------------------------------------------------------------------------
421 --- When in validation only mode raise the Validate_Enabled_Exception
422
423 --
424 -- Call After Process User Hook
425 --
426 begin
427 ghr_pdi_bk1.create_pdi_a (
428 p_position_description_id => l_position_description_id,
429 p_date_from => p_date_from,
430 p_routing_grp_id => p_routing_grp_id,
431 p_date_to => p_date_to,
432 p_opm_cert_num => p_opm_cert_num,
433 p_flsa => p_flsa,
434 p_financial_statement => p_financial_statement,
435 p_subject_to_ia_action => p_subject_to_ia_action,
436 p_position_status => p_position_status,
437 p_position_is => p_position_is,
438 p_position_sensitivity => p_position_sensitivity,
439 p_competitive_level => p_competitive_level,
440 p_pd_remarks => p_pd_remarks,
441 p_position_class_std => p_position_class_std,
442 p_category => p_category,
443 p_career_ladder => p_career_ladder,
444 p_supervisor_name => p_supervisor_name,
445 p_supervisor_title => p_supervisor_title,
446 p_supervisor_date => p_supervisor_date,
447 p_manager_name => p_manager_name,
448 p_manager_title => p_manager_title,
449 p_manager_date => p_manager_date,
450 p_classifier_name => p_classifier_name,
451 p_classifier_title => p_classifier_title,
452 p_classifier_date => p_classifier_date,
453 p_attribute_category => p_attribute_category,
454 p_attribute1 => p_attribute1,
455 p_attribute2 => p_attribute2,
456 p_attribute3 => p_attribute3,
457 p_attribute4 => p_attribute4,
458 p_attribute5 => p_attribute5,
459 p_attribute6 => p_attribute6,
460 p_attribute7 => p_attribute7,
461 p_attribute8 => p_attribute8,
462 p_attribute9 => p_attribute9,
463 p_attribute10 => p_attribute10,
464 p_attribute11 => p_attribute11,
465 p_attribute12 => p_attribute12,
466 p_attribute13 => p_attribute13,
467 p_attribute14 => p_attribute14,
468 p_attribute15 => p_attribute15,
469 p_attribute16 => p_attribute16,
470 p_attribute17 => p_attribute17,
471 p_attribute18 => p_attribute18,
472 p_attribute19 => p_attribute19,
473 p_attribute20 => p_attribute20,
474 p_business_group_id => p_business_group_id ,
475 p_1_approved_flag => p_1_approved_flag,
476 p_1_user_name_acted_on => p_1_user_name_acted_on,
477 p_1_action_taken => p_1_action_taken,
478 p_2_user_name_routed_to => p_2_user_name_routed_to,
479 p_2_groupbox_id => p_2_groupbox_id,
480 p_2_routing_list_id => p_2_routing_list_id,
481 p_2_routing_seq_number => p_2_routing_seq_number,
482 p_1_pd_routing_history_id => p_1_pd_routing_history_id,
483 p_1_pdh_object_version_number => p_1_pdh_object_version_number,
484 p_2_pdh_object_version_number => p_2_pdh_object_version_number,
485 p_2_pd_routing_history_id => p_2_pd_routing_history_id,
486 p_pdi_object_version_number => l_pdi_object_version_number
487 );
488 exception
489 when hr_api.cannot_find_prog_unit then
490 hr_api.cannot_find_prog_unit_error
491 (p_module_name => 'create_pdi',
492 p_hook_type => 'AP'
493 );
494 end;
495 --
496 -- End of After Process User Hook call
497 --
498 IF p_validate THEN
499 RAISE hr_api.validate_enabled;
500 END IF;
501 --
502 -- Set All output Arguments
503 --
504 p_position_description_id := l_position_description_id;
505 p_pdi_object_version_number := l_pdi_object_version_number;
506 hr_utility.set_location ('Leaving:'|| l_proc,11);
507 EXCEPTION
508
509 WHEN hr_api.validate_enabled THEN
510 -- As the validation exception has been raised
511 -- We must rollback to the Savepoint set.
512 ROLLBACK TO create_pdi;
513 --
514 -- Only Set Output warning arguments.
515 -- (Any key or derived arguments must be set to NULL
516 -- When validation only mode is being used.)
517 --
518 p_position_description_id := NULL;
519 p_1_pd_routing_history_id := NULL;
520 p_1_pdh_object_version_number := NULL;
521 p_2_pdh_object_version_number := NULL;
522 p_2_pd_routing_history_id := NULL;
523 p_pdi_object_version_number := NULL;
524 when others then
525 rollback to create_pdi;
526 --
527 -- Reset IN OUT parameters and set OUT parameters
528 --
529 p_position_description_id := NULL;
530 p_1_pd_routing_history_id := NULL;
531 p_1_pdh_object_version_number := NULL;
532 p_2_pdh_object_version_number := NULL;
533 p_2_pd_routing_history_id := NULL;
534 p_pdi_object_version_number := NULL;
535
536 raise;
537 --
538 hr_utility.set_location('Leaving:' || l_proc,12);
539 END create_pdi;
540 ----------------------------|------------< Update_pdi >------------|--------------------------------
541
542 PROCEDURE update_pdi
543 (
544 p_validate IN BOOLEAN default false,
545 p_position_description_id IN number,
546 p_routing_grp_id IN number default hr_api.g_number,
547 p_date_from IN date,
548 p_date_to IN date default hr_api.g_date,
549 p_opm_cert_num IN ghr_position_descriptions.opm_cert_num%TYPE default hr_api.g_varchar2,
550 p_flsa IN ghr_position_descriptions.flsa%TYPE default hr_api.g_varchar2,
551 p_financial_statement IN ghr_position_descriptions.financial_statement%TYPE default hr_api.g_varchar2,
552 p_subject_to_ia_action IN ghr_position_descriptions.subject_to_ia_action%TYPE default hr_api.g_varchar2,
553 p_position_status IN ghr_position_descriptions.position_status%TYPE default hr_api.g_number,
554 p_position_is IN ghr_position_descriptions.position_is%TYPE default hr_api.g_varchar2,
555 p_position_sensitivity IN ghr_position_descriptions.position_sensitivity%TYPE default hr_api.g_varchar2,
556 p_competitive_level IN ghr_position_descriptions.competitive_level%TYPE default hr_api.g_varchar2,
557 p_pd_remarks IN ghr_position_descriptions.pd_remarks%TYPE default hr_api.g_varchar2,
558 p_position_class_std IN ghr_position_descriptions.position_class_std%TYPE default hr_api.g_varchar2,
559 p_category IN ghr_position_descriptions.category%TYPE default hr_api.g_varchar2,
560 p_career_ladder IN ghr_position_descriptions.career_ladder%TYPE default hr_api.g_varchar2,
561 p_supervisor_name in varchar2 default hr_api.g_varchar2,
562 p_supervisor_title in varchar2 default hr_api.g_varchar2,
563 p_supervisor_date in date default hr_api.g_date,
564 p_manager_name in varchar2 default hr_api.g_varchar2,
565 p_manager_title in varchar2 default hr_api.g_varchar2,
566 p_manager_date in date default hr_api.g_date,
567 p_classifier_name in varchar2 default hr_api.g_varchar2,
568 p_classifier_title in varchar2 default hr_api.g_varchar2,
569 p_classifier_date in date default hr_api.g_date,
570 p_attribute_category in varchar2 default hr_api.g_varchar2,
571 p_attribute1 in varchar2 default hr_api.g_varchar2,
572 p_attribute2 in varchar2 default hr_api.g_varchar2,
573 p_attribute3 in varchar2 default hr_api.g_varchar2,
574 p_attribute4 in varchar2 default hr_api.g_varchar2,
575 p_attribute5 in varchar2 default hr_api.g_varchar2,
576 p_attribute6 in varchar2 default hr_api.g_varchar2,
577 p_attribute7 in varchar2 default hr_api.g_varchar2,
578 p_attribute8 in varchar2 default hr_api.g_varchar2,
579 p_attribute9 in varchar2 default hr_api.g_varchar2,
580 p_attribute10 in varchar2 default hr_api.g_varchar2,
581 p_attribute11 in varchar2 default hr_api.g_varchar2,
582 p_attribute12 in varchar2 default hr_api.g_varchar2,
583 p_attribute13 in varchar2 default hr_api.g_varchar2,
584 p_attribute14 in varchar2 default hr_api.g_varchar2,
585 p_attribute15 in varchar2 default hr_api.g_varchar2,
586 p_attribute16 in varchar2 default hr_api.g_varchar2,
587 p_attribute17 in varchar2 default hr_api.g_varchar2,
588 p_attribute18 in varchar2 default hr_api.g_varchar2,
589 p_attribute19 in varchar2 default hr_api.g_varchar2,
590 p_attribute20 in varchar2 default hr_api.g_varchar2,
591 p_business_group_id in number default hr_api.g_number,
592 p_u_approved_flag in varchar2 default hr_api.g_varchar2,
593 p_u_user_name_acted_on in varchar2 default hr_api.g_varchar2,
594 p_u_action_taken in varchar2 default null,
595 p_i_user_name_routed_to in varchar2 default null,
596 p_i_groupbox_id in number default null,
597 p_i_routing_list_id in number default null,
598 p_i_routing_seq_number in number default null,
599
600 p_u_pdh_object_version_number in out nocopy number,
601 p_i_pd_routing_history_id out nocopy number,
602 p_i_pdh_object_version_number out nocopy number,
603 p_o_pd_routing_history_id out nocopy number,
604
605
606 p_o_pdh_object_version_number out nocopy number,
607
608 p_pdi_object_version_number in out nocopy number)
609 IS
610 l_pdi_object_version_number number;
611 l_proc varchar2(72) := g_package||'Update_pdi';
612 l_routing_grp_id ghr_position_descriptions.routing_group_id%TYPE;
613 l_u_pd_routing_history_id ghr_pd_routing_history.pd_routing_history_id%TYPE;
614 l_i_pd_routing_history_id ghr_pd_routing_history.pd_routing_history_id%TYPE;
615 l_u_pdh_object_version_number ghr_pd_routing_history.object_version_number%TYPE;
616 l_initial_u_pdh_ovn ghr_pd_routing_history.object_version_number%TYPE;
617 l_i_pdh_object_version_number ghr_pd_routing_history.object_version_number%TYPE;
618 l_initial_pdi_ovn ghr_pd_routing_history.object_version_number%TYPE;
619 l_initiator_flag ghr_pd_routing_history.initiator_flag%TYPE;
620 l_reviewer_flag ghr_pd_routing_history.reviewer_flag%TYPE;
621 l_authorizer_flag ghr_pd_routing_history.authorizer_flag%TYPE;
622 l_requester_flag ghr_pd_routing_history.requester_flag%TYPE;
623 l_approver_flag ghr_pd_routing_history.approver_flag%TYPE;
624 l_personnelist_flag ghr_pd_routing_history.personnelist_flag%TYPE;
625 l_user_name_employee_id per_people_f.person_id%TYPE;
626 l_user_name_emp_first_name per_people_f.first_name%TYPE;
627 l_user_name_emp_last_name per_people_f.last_name%TYPE;
628 l_user_name_emp_middle_names per_people_f.middle_names%TYPE;
629 l_seq_numb ghr_pd_routing_history.routing_seq_number%TYPE;
630 l_cur_seq_numb ghr_pd_routing_history.routing_seq_number%TYPE;
631 l_next_seq_numb ghr_pd_routing_history.routing_seq_number%TYPE;
632 l_next_groupbox_name ghr_groupboxes.name%TYPE;
633 l_next_groupbox_id ghr_pd_routing_history.groupbox_id%TYPE;
634 l_next_user_name ghr_pd_routing_history.user_name%TYPE := p_i_user_name_routed_to;
635 l_action_taken ghr_pd_routing_history.action_taken%TYPE;
636 l_old_action_taken ghr_pd_routing_history.action_taken%TYPE;
637 l_forward_to_name ghr_pd_routing_history.user_name%type;
638 l_cnt_history number;
639 l_dummy ghr_pd_routing_history.action_taken%TYPE;
640 l_pd_initiated boolean;
641 l_exists boolean;
642 l_item_key ghr_pd_routing_history.item_key%TYPE;
643 l_reclass_direct_flag varchar2(1) := 'N';
644 l_reclass_action_taken ghr_pd_routing_history.action_taken%TYPE;
645 l_last_item_key ghr_pd_routing_history.item_key%TYPE;
646
647 CURSOR c_cnt_history is
648 SELECT count(*) cnt
649 FROM ghr_pd_routing_history pdh
650 WHERE pdh.position_description_id = p_position_description_id;
651
652 CURSOR c_routing_history_id is
653 SELECT pdh.pd_routing_history_id,
654 pdh.object_version_number,
655 pdh.action_taken,
656 pdh.item_key
657 FROM ghr_pd_routing_history pdh
658 WHERE pdh.position_description_id = p_position_description_id
659 ORDER BY pdh.pd_routing_history_id desc;
660
661 CURSOR c_routing_grp_id is
662 SELECT pdi.routing_group_id
663 FROM ghr_position_descriptions pdi
664 WHERE pdi.position_description_id = p_position_description_id;
665
666 /* CURSOR c_names is
667 SELECT usr.employee_id,
668 per.first_name,
669 per.last_name,
670 per.middle_names
671 FROM fnd_user usr,
672 per_people_f per
673 WHERE upper(p_u_user_name_acted_on) = upper(usr.user_name)
674 AND per.person_id = usr.employee_id
675 AND p_date_from
676 BETWEEN per.effective_start_date
677 AND per.effective_end_date; */
678 -- Bug 4863608 Perf. Repository Changes
679
680 CURSOR c_names IS
681 SELECT usr.employee_id,
682 per.first_name,
683 per.last_name,
684 per.middle_names
685 FROM fnd_user usr,
686 per_people_f per
687 WHERE usr.user_name = UPPER(p_u_user_name_acted_on)
688 AND per.person_id = usr.employee_id
689 AND p_date_from
690 BETWEEN per.effective_start_date
691 AND per.effective_end_date;
692
693 cursor cur_rout_list_used is
694 select pdh.routing_seq_number
695 from ghr_pd_routing_history pdh
696 where pdh.position_description_id = p_position_description_id
697 and pdh.routing_list_id = p_i_routing_list_id
698 order by pdh.pd_routing_history_id desc;
699 cursor cur_next_rout_seq is
700 select rlm.seq_number,
701 rlm.groupbox_id,
702 rlm.user_name
703 from ghr_routing_list_members rlm
704 where rlm.routing_list_id = p_i_routing_list_id
705 and rlm.seq_number > l_cur_seq_numb
706 order by rlm.seq_number asc;
707 cursor c_history_exists is
708 select action_taken
709 from ghr_pd_routing_history pdh
710 where pdh.position_description_id = p_position_description_id
711 order by pd_routing_history_id;
712
713 cursor c_pd_initiated is
714
715 select action_taken
716 from ghr_pd_routing_history pdh
717 where pdh.position_description_id = p_position_description_id
718 and action_taken = 'INITIATED';
719
720 cursor c_groupbox_name is
721 select gbx.name
722 from ghr_groupboxes gbx
723 where gbx.groupbox_id = l_next_groupbox_id;
724 Cursor c_item_key_seq is
725 select ghr_pd_wf_item_key_s.nextval
726 from dual;
727 BEGIN
728 hr_utility.set_location('Entering'||l_proc,5);
729 --
730 -- Issue a savepoint if operating in validation mode.
731 --
732 --IF p_validate THEN
733 SAVEPOINT update_pdi;
734 --END IF;
735 -- Call Before Process User Hook
736 --
737 --
738 -- Remember IN OUT parameter IN values
739 l_initial_u_pdh_ovn := p_u_pdh_object_version_number;
740 l_initial_pdi_ovn := p_pdi_object_version_number;
741
742 begin
743 ghr_pdi_bk2.update_pdi_b (
744 p_position_description_id => p_position_description_id,
745 p_routing_grp_id => p_routing_grp_id,
746 p_date_from => p_date_from,
747 p_date_to => p_date_to,
748 p_opm_cert_num => p_opm_cert_num,
749 p_flsa => p_flsa,
750 p_financial_statement => p_financial_statement,
751 p_subject_to_ia_action => p_subject_to_ia_action,
752 p_position_status => p_position_status,
753 p_position_is => p_position_is,
754 p_position_sensitivity => p_position_sensitivity,
755 p_competitive_level => p_competitive_level,
756 p_pd_remarks => p_pd_remarks,
757 p_position_class_std => p_position_class_std,
758 p_category => p_category,
759 p_career_ladder => p_career_ladder,
760 p_supervisor_name => p_supervisor_name,
761 p_supervisor_title => p_supervisor_title,
762 p_supervisor_date => p_supervisor_date,
763 p_manager_name => p_manager_name,
764 p_manager_title => p_manager_title,
765 p_manager_date => p_manager_date,
766 p_classifier_name => p_classifier_name,
767 p_classifier_title => p_classifier_title,
768 p_classifier_date => p_classifier_date,
769 p_attribute_category => p_attribute_category,
770 p_attribute1 => p_attribute1,
771 p_attribute2 => p_attribute2,
772 p_attribute3 => p_attribute3,
773 p_attribute4 => p_attribute4,
774 p_attribute5 => p_attribute5,
775 p_attribute6 => p_attribute6,
776 p_attribute7 => p_attribute7,
777 p_attribute8 => p_attribute8,
778 p_attribute9 => p_attribute9,
779 p_attribute10 => p_attribute10,
780 p_attribute11 => p_attribute11,
781 p_attribute12 => p_attribute12,
782 p_attribute13 => p_attribute13,
783 p_attribute14 => p_attribute14,
784 p_attribute15 => p_attribute15,
785 p_attribute16 => p_attribute16,
786 p_attribute17 => p_attribute17,
787 p_attribute18 => p_attribute18,
788 p_attribute19 => p_attribute19,
789 p_attribute20 => p_attribute20,
790 p_business_group_id => p_business_group_id,
791 p_u_approved_flag => p_u_approved_flag,
792 p_u_user_name_acted_on => p_u_user_name_acted_on,
793 p_u_action_taken => p_u_action_taken,
794 p_i_user_name_routed_to => p_i_user_name_routed_to,
795 p_i_groupbox_id => p_i_groupbox_id,
796 p_i_routing_list_id => p_i_routing_list_id,
797 p_i_routing_seq_number => p_i_routing_seq_number,
798 p_u_pdh_object_version_number => p_u_pdh_object_version_number,
799 p_i_pd_routing_history_id => p_i_pd_routing_history_id,
800 p_i_pdh_object_version_number => p_i_pdh_object_version_number,
801 p_pdi_object_version_number => p_pdi_object_version_number
802 );
803 exception
804 when hr_api.cannot_find_prog_unit then
805 hr_api.cannot_find_prog_unit_error
806 (p_module_name => 'update_pdi',
807 p_hook_type => 'BP'
808 );
809 end;
810 --
811 -- End of Before Process User Hook call
812 --
813 hr_utility.set_location(l_proc,6);
814 --
815 -- Validation in addition to row handlers.
816 --
817 -- To Update the ghr_position_descriptions table. we need the primary key.
818 IF p_position_description_id is null then
819 hr_utility.set_message(8301, 'GHR_PD_ID_PRIMARY_KEY_INVALID');
820 hr_utility.raise_error;
821 end if;
822 -- Process Logic
823 -- Update the row in ghr_position_description by calling the update row handler.
824 -- Routing Group _Id can be changed only in a case where the request has been initiated
825 -- but not yet routed , for instance when the user uses the task flow button to
826 -- naviage to another form and when he comes back he can change the routing_grouip_id
827
828 if p_routing_grp_id is not null and p_routing_grp_id <> hr_api.g_number then
829 for rout_group_id in c_routing_grp_id loop
830 l_routing_grp_id := rout_group_id.routing_group_id;
831 end loop;
832 if nvl(l_routing_grp_id,hr_api.g_number) <> p_routing_grp_id then
833 for cnt_of_history in c_cnt_history loop
834 l_cnt_history := cnt_of_history.cnt;
835 exit;
836 end loop;
837 if nvl(l_cnt_history,0) > 1 then
838 hr_utility.set_message(8301,'GHR_38638_PD_ROUT_GRP_NON_UPD');
839 hr_utility.raise_error;
840 end if;
841 end if;
842 end if;
843 l_pdi_object_version_number := p_pdi_object_version_number;
844 ghr_pdi_upd.upd
845 (
846 p_position_description_id => p_position_description_id,
847 p_routing_group_id => p_routing_grp_id,
848 p_date_from => p_date_from,
849 p_date_to => p_date_to,
850 p_opm_cert_num => p_opm_cert_num,
851 p_flsa => p_flsa,
852 p_financial_statement => p_financial_statement,
853 p_subject_to_ia_action => p_subject_to_ia_action,
854 p_position_status => p_position_status,
855 p_position_is => p_position_is,
856 p_position_sensitivity => p_position_sensitivity,
857 p_competitive_level => p_competitive_level,
858 p_pd_remarks => p_pd_remarks,
859 p_position_class_std => p_position_class_std,
860 p_category => p_category,
861 p_career_ladder => p_career_ladder,
862 p_supervisor_name => p_supervisor_name,
863 p_supervisor_title => p_supervisor_title,
864 p_supervisor_date => p_supervisor_date,
865 p_manager_name => p_manager_name,
866 p_manager_title => p_manager_title,
867 p_manager_date => p_manager_date,
868 p_classifier_name => p_classifier_name,
869 p_classifier_title => p_classifier_title,
870 p_classifier_date => p_classifier_date,
871 p_attribute_category => p_attribute_category,
872 p_attribute1 => p_attribute1,
873 p_attribute2 => p_attribute2,
874 p_attribute3 => p_attribute3,
875 p_attribute4 => p_attribute4,
876 p_attribute5 => p_attribute5,
877 p_attribute6 => p_attribute6,
878 p_attribute7 => p_attribute7,
879 p_attribute8 => p_attribute8 ,
880 p_attribute9 => p_attribute9,
881 p_attribute10 => p_attribute10 ,
882 p_attribute11 => p_attribute11,
883 p_attribute12 => p_attribute12 ,
884 p_attribute13 => p_attribute13 ,
885 p_attribute14 => p_attribute14 ,
886 p_attribute15 => p_attribute15,
887 p_attribute16 => p_attribute16 ,
888 p_attribute17 => p_attribute17,
889 p_attribute18 => p_attribute18 ,
890 p_attribute19 => p_attribute19,
891 p_attribute20 => p_attribute20 ,
892 p_business_group_id => p_business_group_id ,
893 p_object_version_number => l_pdi_object_version_number
894 );
895 --When in validation only mode raise the Validate_Enabled exception.
896 ------------------------------------------------------------------
897 -- Added by Dinkar. Karumuri for Routing and Workflow.
898 -----------------------------------------------------
899 --
900 --2)Derive all parameters required to insert routing_history records.
901 l_action_taken := p_u_action_taken;
902 if l_action_taken is null then
903 l_action_taken := 'NO_ACTION';
904 end if;
905 -- To check if PD has already been initiated
906
907 hr_utility.set_location('before open init',10);
908 OPEN c_pd_initiated;
909 FETCH c_pd_initiated INTO l_dummy;
910 IF c_pd_initiated%FOUND THEN
911 l_pd_initiated := TRUE;
912 ELSE
913 l_pd_initiated := FALSE;
914 END IF;
915 CLOSE c_pd_initiated;
916
917
918 OPEN c_history_exists;
919 FETCH c_history_exists INTO l_old_action_taken;
920
921 l_exists := true;
922
923 IF c_history_exists%NOTFOUND THEN
924 l_exists := false;
925 END IF;
926
927 CLOSE c_history_exists;
928
929
930
931 --Check for Invalid action taken
932
933 if l_action_taken not in('NOT_ROUTED','INITIATED','AUTHORIZED',
934 'NO_ACTION','REVIEWED','CANCELED','REOPENED',
935 'REQUESTED', 'RECLASSIFIED','CLASSIFIED')
936 then
937 hr_utility.set_message(8301,'GHR_38110_INVALID_ACTION_TAKEN');
938 hr_utility.raise_error;
939 end if;
940
941 hr_utility.set_location('after invalid action',10);
942 if l_action_taken not in ('CANCELED','CLASSIFIED','RECLASSIFIED','NOT_ROUTED') then
943 if p_i_user_name_routed_to is null and
944 p_i_groupbox_id is null and
945 p_i_routing_list_id is null then
946 hr_utility.set_message(8301,'GHR_38280_NO_ROUTING_INFO');
947 hr_utility.raise_error;
948 end if;
949 end if;
950
951 -- For Direct Reclassification and getting the last_item_key in case NOT_ROUTED to INITIATED
952
953 for cur_routing_history_id in C_routing_history_id loop
954 l_u_pd_routing_history_id := cur_routing_history_id.pd_routing_history_id;
955 l_u_pdh_object_version_number := cur_routing_history_id.object_version_number;
956 l_reclass_action_taken := cur_routing_history_id.action_taken;
957 l_last_item_key := cur_routing_history_id.item_key;
958 exit;
959 end loop;
960
961 if nvl(l_reclass_action_taken,hr_api.g_varchar2) in ('RECLASSIFIED','CLASSIFIED') then
962 if nvl(l_action_taken,hr_api.g_varchar2) in ('RECLASSIFIED','NOT_ROUTED') then
963 l_reclass_direct_flag := 'Y';
964 end if;
965 end if;
966
967 hr_utility.set_location('action taken is'||l_action_taken,10);
968
969 if l_action_taken in ('REOPENED') or l_reclass_direct_flag = 'Y' then
970 OPEN c_item_key_seq;
971 FETCH c_item_key_seq INTO l_item_key;
972 IF c_item_key_seq%NOTFOUND THEN
973
974 hr_utility.set_message(8301,'GHR_38625_NO_WF_ITEM_KEY_SEQ');
975 hr_utility.raise_error;
976 END IF;
977 CLOSE c_item_key_seq;
978 end if;
979
980
981 for cur_routing_history_id in C_routing_history_id loop
982 l_u_pd_routing_history_id := cur_routing_history_id.pd_routing_history_id;
983 l_u_pdh_object_version_number := cur_routing_history_id.object_version_number;
984 exit;
985 end loop;
986 if p_u_user_name_acted_on is not null then
987 ghr_pdi_pkg.get_roles
988 (p_position_description_id,
989 p_routing_grp_id,
990 p_u_user_name_acted_on,
991 l_initiator_flag,
992 l_requester_flag,
993 l_authorizer_flag,
994 l_personnelist_flag,
995 l_approver_flag,
996 l_reviewer_flag
997 );
998 for name_rec in C_names loop
999 l_user_name_employee_id := name_rec.employee_id ;
1000 l_user_name_emp_first_name := name_rec.first_name;
1001 l_user_name_emp_last_name := name_rec.last_name;
1002 l_user_name_emp_middle_names := name_rec.middle_names;
1003 exit;
1004 end loop;
1005 else
1006 hr_utility.set_message(8301,'GHR_38111_USER_NAME_REQD');
1007 hr_utility.raise_error;
1008 end if;
1009
1010
1011 -- Update the latest record in the routing history for the specific pd id
1012 -- Or ( Modified by vravikan )
1013 -- Insert a new record in the routing history if l_action_taken = 'REOPENED'
1014 -- For reclassifaction of existing classfied PD we need two records one is for 'REOPENED' status
1015 -- another is for routing purposes
1016
1017
1018 if not (nvl(l_action_taken,hr_api.g_varchar2) in ('NOT_ROUTED') and l_reclass_direct_flag = 'Y' )
1019 then
1020 if nvl(l_action_taken,hr_api.g_varchar2) not in ('CANCELED','RECLASSIFIED','CLASSIFIED')
1021 then
1022
1023 hr_utility.set_location('l_action taken is ' || l_action_taken,10);
1024 hr_utility.set_location('l_reclass_direct is ' || l_reclass_direct_flag,10);
1025 if l_action_taken <> 'REOPENED' or
1026 ((l_action_taken = 'REOPENED') and (l_reclass_action_taken = 'NOT_ROUTED')) then
1027
1028 ghr_pdh_upd.upd
1029 (
1030 p_pd_routing_history_id => l_u_pd_routing_history_id,
1031 p_initiator_flag => nvl(l_initiator_flag,'N'),
1032 p_requester_flag => nvl(l_requester_flag,'N'),
1033 p_approver_flag => nvl(l_approver_flag,'N'),
1034 p_reviewer_flag => nvl(l_reviewer_flag,'N'),
1035 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
1036 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
1037 p_approved_flag => nvl(p_u_approved_flag,'N'),
1038 p_user_name => p_u_user_name_acted_on,
1039 p_user_name_employee_id => l_user_name_employee_id,
1040 p_user_name_emp_first_name => l_user_name_emp_first_name,
1041 p_user_name_emp_last_name => l_user_name_emp_last_name,
1042 p_user_name_emp_middle_names => l_user_name_emp_middle_names,
1043 p_date_notification_sent => sysdate,
1044 p_action_taken => l_action_taken,
1045 p_object_version_number => l_u_pdh_object_version_number,
1046 p_validate => p_validate
1047 );
1048 elsif
1049 (l_action_taken = 'REOPENED') and (l_reclass_action_taken <> 'NOT_ROUTED')
1050 then
1051 ghr_pdh_ins.ins
1052 (
1053 p_pd_routing_history_id => p_o_pd_routing_history_id,
1054 p_position_description_id => p_position_description_id,
1055 p_initiator_flag => nvl(l_initiator_flag,'N'),
1056 p_requester_flag => nvl(l_requester_flag,'N'),
1057 p_approver_flag => nvl(l_approver_flag,'N'),
1058 p_reviewer_flag => nvl(l_reviewer_flag,'N') ,
1059 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
1060 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
1061 p_approved_flag => nvl(p_u_approved_flag,'N'),
1062 p_user_name => p_u_user_name_acted_on,
1063 p_user_name_employee_id => l_user_name_employee_id,
1064 p_user_name_emp_first_name => l_user_name_emp_first_name,
1065 p_user_name_emp_last_name => l_user_name_emp_last_name,
1066 p_user_name_emp_middle_names => l_user_name_emp_middle_names,
1067 p_action_taken => l_action_taken,
1068 p_date_notification_sent => sysdate,
1069 p_object_version_number => p_o_pdh_object_version_number,
1070 p_validate => p_validate,
1071 p_item_key => l_item_key
1072 );
1073 l_last_item_key := l_item_key;
1074 end if;
1075 -- if the specific routing_list has already been used,get the next seq. no.from routing_list_members
1076 -- else sequence_number = 1
1077 -- if there are no more sequences, raise an error
1078 -- Insert 2nd record into routing_history for routing details (exception when action_taken = 'NOT_ROUTED')
1079
1080 if nvl(l_action_taken,hr_api.g_varchar2) not in ('NOT_ROUTED') then
1081 l_next_seq_numb := p_i_routing_seq_number;
1082 l_next_groupbox_id := p_i_groupbox_id;
1083 l_next_user_name := p_i_user_name_routed_to;
1084 -- fetch the next sequence number for the specific routing list, when it is not passed
1085 if p_i_routing_list_id is not null and p_i_routing_seq_number is null then
1086 for rout_list_used in cur_rout_list_used loop
1087 l_cur_seq_numb := rout_list_used.routing_seq_number;
1088 exit;
1089 end loop;
1090 if l_cur_seq_numb is null then
1091 l_cur_seq_numb := 0;
1092 end if;
1093 for next_rout_seq_numb in cur_next_rout_seq loop
1094 l_next_seq_numb := next_rout_seq_numb.seq_number;
1095 l_next_groupbox_id := next_rout_seq_numb.groupbox_id;
1096 l_next_user_name := next_rout_seq_numb.user_name;
1097 exit;
1098 end loop;
1099 if l_next_user_name is null then
1100 l_next_user_name := p_i_user_name_routed_to;
1101 end if;
1102 if l_next_groupbox_id is null then
1103 l_next_groupbox_id := p_i_groupbox_id;
1104 end if;
1105 if l_next_seq_numb is null then
1106 hr_utility.set_message(8301, 'GHR_38114_NO_MORE_SEQ_NUMBER');
1107 hr_utility.raise_error;
1108 end if;
1109 end if;
1110
1111
1112
1113 ghr_pdh_ins.ins
1114 (p_pd_routing_history_id => p_i_pd_routing_history_id,
1115 p_position_description_id => p_position_description_id,
1116 p_initiator_flag => 'N',
1117 p_requester_flag => 'N',
1118 p_approver_flag => 'N',
1119 p_reviewer_flag => 'N',
1120 p_authorizer_flag => 'N',
1121 p_approved_flag => 'N',
1122 p_personnelist_flag => 'N',
1123 p_user_name => l_next_user_name,
1124 p_groupbox_id => l_next_groupbox_id,
1125 p_routing_list_id => p_i_routing_list_id,
1126 p_routing_seq_number => l_next_seq_numb,
1127 p_date_notification_sent => sysdate,
1128 p_object_version_number => p_i_pdh_object_version_number,
1129 p_item_key => l_last_item_key,
1130 p_validate => p_validate
1131 );
1132 end if;
1133 end if;
1134
1135 end if;
1136
1137
1138
1139 IF l_action_taken IN ('CANCELED','RECLASSIFIED','CLASSIFIED')
1140 or (nvl(l_action_taken,hr_api.g_varchar2) in ('NOT_ROUTED') and l_reclass_direct_flag = 'Y' )
1141 then
1142 hr_utility.set_location('l_action taken is ' || l_action_taken,10);
1143 hr_utility.set_location('l_reclass_direct is ' || l_reclass_direct_flag,10);
1144 for cur_routing_history_id in C_routing_history_id loop
1145 l_u_pd_routing_history_id := cur_routing_history_id.pd_routing_history_id;
1146 l_u_pdh_object_version_number := cur_routing_history_id.object_version_number;
1147 exit;
1148 end loop;
1149
1150 if l_reclass_direct_flag = 'Y' then
1151
1152 ghr_pdh_ins.ins
1153 (
1154 p_pd_routing_history_id => p_o_pd_routing_history_id,
1155 p_position_description_id => p_position_description_id,
1156 p_initiator_flag => nvl(l_initiator_flag,'N'),
1157 p_requester_flag => nvl(l_requester_flag,'N'),
1158 p_approver_flag => nvl(l_approver_flag,'N'),
1159 p_reviewer_flag => nvl(l_reviewer_flag,'N') ,
1160 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
1161 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
1162 p_approved_flag => nvl(p_u_approved_flag,'N'),
1163 p_user_name => p_u_user_name_acted_on,
1164 p_user_name_employee_id => l_user_name_employee_id,
1165 p_user_name_emp_first_name => l_user_name_emp_first_name,
1166 p_user_name_emp_last_name => l_user_name_emp_last_name ,
1167 p_user_name_emp_middle_names =>l_user_name_emp_middle_names,
1168 p_action_taken => l_action_taken,
1169 p_date_notification_sent => sysdate,
1170 p_object_version_number => p_o_pdh_object_version_number,
1171 p_item_key => l_item_key,
1172
1173 p_validate => false
1174 );
1175 else
1176 ghr_pdh_upd.upd
1177 (
1178 p_pd_routing_history_id => l_u_pd_routing_history_id,
1179 p_initiator_flag => nvl(l_initiator_flag,'N'),
1180 p_requester_flag => nvl(l_requester_flag,'N'),
1181 p_approver_flag => nvl(l_approver_flag,'N'),
1182 p_reviewer_flag => nvl(l_reviewer_flag,'N'),
1183 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
1184 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
1185 p_approved_flag => nvl(p_u_approved_flag,'N'),
1186 p_user_name => p_u_user_name_acted_on,
1187 p_user_name_employee_id => l_user_name_employee_id,
1188 p_user_name_emp_first_name => l_user_name_emp_first_name,
1189 p_user_name_emp_last_name => l_user_name_emp_last_name,
1190 p_user_name_emp_middle_names => l_user_name_emp_middle_names,
1191 p_date_notification_sent => sysdate,
1192 p_action_taken => l_action_taken,
1193 p_object_version_number => l_u_pdh_object_version_number,
1194 p_validate => p_validate
1195 );
1196
1197 end if;
1198
1199 end if;
1200 --
1201 -- Call After Process User Hook
1202 --
1203 begin
1204 ghr_pdi_bk2.update_pdi_a (
1205 p_position_description_id => p_position_description_id,
1206 p_routing_grp_id => p_routing_grp_id,
1207 p_date_from => p_date_from,
1208 p_date_to => p_date_to,
1209 p_opm_cert_num => p_opm_cert_num,
1210 p_flsa => p_flsa,
1211 p_financial_statement => p_financial_statement,
1212 p_subject_to_ia_action => p_subject_to_ia_action,
1213 p_position_status => p_position_status,
1214 p_position_is => p_position_is,
1215 p_position_sensitivity => p_position_sensitivity,
1216 p_competitive_level => p_competitive_level,
1217 p_pd_remarks => p_pd_remarks,
1218 p_position_class_std => p_position_class_std,
1219 p_category => p_category,
1220 p_career_ladder => p_career_ladder,
1221 p_supervisor_name => p_supervisor_name,
1222 p_supervisor_title => p_supervisor_title,
1223 p_supervisor_date => p_supervisor_date,
1224 p_manager_name => p_manager_name,
1225 p_manager_title => p_manager_title,
1226 p_manager_date => p_manager_date,
1227 p_classifier_name => p_classifier_name,
1228 p_classifier_title => p_classifier_title,
1229 p_classifier_date => p_classifier_date,
1230 p_attribute_category => p_attribute_category,
1231 p_attribute1 => p_attribute1,
1232 p_attribute2 => p_attribute2,
1233 p_attribute3 => p_attribute3,
1234 p_attribute4 => p_attribute4,
1235 p_attribute5 => p_attribute5,
1236 p_attribute6 => p_attribute6,
1237 p_attribute7 => p_attribute7,
1238 p_attribute8 => p_attribute8,
1239 p_attribute9 => p_attribute9,
1240 p_attribute10 => p_attribute10,
1241 p_attribute11 => p_attribute11,
1242 p_attribute12 => p_attribute12,
1243 p_attribute13 => p_attribute13,
1244 p_attribute14 => p_attribute14,
1245 p_attribute15 => p_attribute15,
1246 p_attribute16 => p_attribute16,
1247 p_attribute17 => p_attribute17,
1248 p_attribute18 => p_attribute18,
1249 p_attribute19 => p_attribute19,
1250 p_attribute20 => p_attribute20,
1251 p_business_group_id => p_business_group_id,
1252 p_u_approved_flag => p_u_approved_flag,
1253 p_u_user_name_acted_on => p_u_user_name_acted_on,
1254 p_u_action_taken => p_u_action_taken,
1255 p_i_user_name_routed_to => p_i_user_name_routed_to,
1256 p_i_groupbox_id => p_i_groupbox_id,
1257 p_i_routing_list_id => p_i_routing_list_id,
1258 p_i_routing_seq_number => p_i_routing_seq_number,
1259 p_u_pdh_object_version_number => p_u_pdh_object_version_number,
1260 p_i_pd_routing_history_id => p_i_pd_routing_history_id,
1261 p_i_pdh_object_version_number => p_i_pdh_object_version_number,
1262 p_pdi_object_version_number => l_pdi_object_version_number
1263 );
1264 exception
1265 when hr_api.cannot_find_prog_unit then
1266 hr_api.cannot_find_prog_unit_error
1267 (p_module_name => 'update_pdi',
1268 p_hook_type => 'AP'
1269 );
1270 end;
1271 --
1272 -- End of After Process User Hook call
1273 --
1274 IF p_validate THEN
1275 RAISE hr_api.validate_enabled;
1276 END IF;
1277 p_pdi_object_version_number := l_pdi_object_version_number;
1278 hr_utility.set_location('Leaving:'|| l_proc,11);
1279 EXCEPTION
1280 WHEN hr_api.validate_enabled THEN
1281 --
1282 -- As the Validate_Enabled exception has been raised
1283 -- We must rollback to the savepoint
1284 --
1285 ROLLBACK to update_pdi;
1286 --
1287 -- Reset IN OUT parameters and set OUT parameters
1288 --
1289 p_u_pdh_object_version_number := l_initial_u_pdh_ovn;
1290 p_i_pd_routing_history_id := null;
1291 p_i_pdh_object_version_number := null;
1292 p_o_pd_routing_history_id := null;
1293 p_o_pdh_object_version_number := null;
1294 p_pdi_object_version_number := l_initial_pdi_ovn;
1295
1296 when others then
1297 rollback to update_pdi;
1298 --
1299 -- Reset IN OUT parameters and set OUT parameters
1300 --
1301 p_u_pdh_object_version_number := l_initial_u_pdh_ovn;
1302 p_i_pd_routing_history_id := null;
1303 p_i_pdh_object_version_number := null;
1304 p_o_pd_routing_history_id := null;
1305 p_o_pdh_object_version_number := null;
1306 p_pdi_object_version_number := l_initial_pdi_ovn;
1307
1308 raise;
1309 hr_utility.set_location('Leaving:'||l_proc,12);
1310 END update_pdi;
1311
1312 procedure call_workflow
1313 (
1314 p_position_description_id IN ghr_position_descriptions.position_description_id%TYPE,
1315 p_action_taken IN ghr_pd_routing_history.action_taken%TYPE
1316 )
1317 is
1318 l_position_description_id ghr_position_descriptions.position_description_id%TYPE;
1319 l_pdi_object_version_number number := 1;
1320 l_initiator_flag ghr_pd_routing_history.initiator_flag%TYPE;
1321 l_reviewer_flag ghr_pd_routing_history.reviewer_flag%TYPE;
1322 l_requester_flag ghr_pd_routing_history.requester_flag%TYPE;
1323 l_authorizer_flag ghr_pd_routing_history.authorizer_flag%TYPE;
1324 l_approver_flag ghr_pd_routing_history.approver_flag%TYPE;
1325 l_approved_flag ghr_pd_routing_history.approved_flag%TYPE;
1326 l_personnelist_flag ghr_pd_routing_history.personnelist_flag%TYPE;
1327 l_user_name_employee_id per_people_f.person_id%TYPE;
1328 l_user_name_emp_first_name per_people_f.first_name%TYPE;
1329 l_user_name_emp_last_name per_people_f.last_name%TYPE;
1330 l_user_name_emp_middle_names per_people_f.middle_names%TYPE;
1331 l_2_routing_seq_number ghr_pd_routing_history.routing_seq_number%TYPE;
1332 l_forward_to_name ghr_groupboxes.name%TYPE;
1333 l_2_groupbox_id ghr_pd_routing_history.groupbox_id%TYPE;
1334 l_user_name_1 ghr_pd_routing_history.user_name%TYPE;
1335 l_user_name_2 ghr_pd_routing_history.user_name%TYPE;
1336 l_last_action_taken_1 ghr_pd_routing_history.action_taken%TYPE;
1337 l_last_action_taken_2 ghr_pd_routing_history.action_taken%TYPE;
1338 l_item_key_1 ghr_pd_routing_history.item_key%TYPE;
1339 l_item_key_2 ghr_pd_routing_history.item_key%TYPE;
1340 l_pd_routing_history_id_1 ghr_pd_routing_history.pd_routing_history_id%TYPE;
1341 l_pd_routing_history_id_2 ghr_pd_routing_history.pd_routing_history_id%TYPE;
1342 l_cnt_history number;
1343 l_reclass_direct_flag varchar2(1) := 'N';
1344 l_groupbox_id_1 ghr_pd_routing_history.groupbox_id%TYPE;
1345 l_groupbox_id_2 ghr_pd_routing_history.groupbox_id%TYPE;
1346 l_proc varchar2(72) := g_package||'call_work_flow';
1347 l_pd_initiated BOOLEAN;
1348 l_dummy varchar2(1);
1349 Cursor c_history_exists is
1350 select 1
1351 from ghr_pd_routing_history pdh
1352 where pdh.position_description_id = p_position_description_id;
1353 Cursor c_groupbox_name is
1354 select gbx.name
1355 from ghr_groupboxes gbx
1356 where gbx.groupbox_id = l_groupbox_id_1;
1357 Cursor c_item_key_seq is
1358 select ghr_pd_wf_item_key_s.nextval
1359 from dual;
1360 cursor c_cnt_history is
1361 select count(*) cnt
1362 from ghr_pd_routing_history pdh
1363 where pdh.position_description_id = p_position_description_id;
1364 Cursor C_routing_history_id is
1365 select pdh.pd_routing_history_id,
1366 pdh.action_taken,
1367 pdh.item_key,
1368 pdh.groupbox_id,
1369 pdh.user_name
1370 from ghr_pd_routing_history pdh
1371 where pdh.position_description_id = p_position_description_id
1372 order by pdh.pd_routing_history_id desc;
1373 cursor c_routing_grp_id is
1374 select pdi.routing_group_id
1375 from ghr_position_descriptions pdi
1376 where pdi.position_description_id = p_position_description_id;
1377 cursor c_pd_initiated is
1378 select 'X'
1379 from ghr_pd_routing_history pdh
1380 where pdh.position_description_id = p_position_description_id
1381 and action_taken in ( 'INITIATED','AUTHORIZED','REQUESTED','CLASSIFIED');
1382
1383 begin
1384
1385 hr_utility.set_location('Now Entering:'||l_proc, 5);
1386 -- Get the Routing history Count
1387 FOR cnt_history in c_cnt_history LOOP
1388 l_cnt_history := cnt_history.cnt;
1389 END LOOP;
1390 hr_utility.set_location('after count history: #'||to_char(l_cnt_history), 5);
1391 hr_utility.set_location('action taken is '||p_action_taken, 5);
1392 -- Get the last two action takens
1393 if l_cnt_history > 0 then
1394 open c_routing_history_id;
1395 fetch c_routing_history_id into
1396 l_pd_routing_history_id_1,
1397 l_last_action_taken_1,
1398 l_item_key_1,
1399 l_groupbox_id_1,
1400 l_user_name_1;
1401 if l_cnt_history > 1 then
1402 fetch c_routing_history_id into
1403 l_pd_routing_history_id_2,
1404 l_last_action_taken_2,
1405 l_item_key_2,
1406 l_groupbox_id_2,
1407 l_user_name_2;
1408 end if;
1409 end if;
1410 close c_routing_history_id;
1411 hr_utility.set_location('action 1 is '||l_last_action_taken_1, 5);
1412 hr_utility.set_location('action 2 is '||l_last_action_taken_2, 5);
1413 hr_utility.set_location('item key1 is '||l_item_key_1, 5);
1414 hr_utility.set_location('item key2 is '||l_item_key_2, 5);
1415 open c_pd_initiated;
1416 fetch c_pd_initiated into l_dummy;
1417 if c_pd_initiated%FOUND then
1418 l_pd_initiated := TRUE;
1419 else
1420 l_pd_initiated := FALSE;
1421 end if;
1422 -- Setting the reclass_direct_flag
1423 if l_cnt_history > 1 then
1424 if nvl(l_last_action_taken_2,hr_api.g_varchar2)
1425 in ('RECLASSIFIED','CLASSIFIED') then
1426 if nvl(l_last_action_taken_1,hr_api.g_varchar2) = 'RECLASSIFIED' then
1427 l_reclass_direct_flag := 'Y';
1428 end if;
1429 end if;
1430 end if;
1431
1432 -- Deriving the groupbox name to be passed to workflow call
1433 if l_groupbox_id_1 is not null then
1434 for groupbox_name in c_groupbox_name loop
1435 l_forward_to_name := groupbox_name.name;
1436 end loop;
1437 else
1438 l_forward_to_name := l_user_name_1;
1439 end if;
1440 hr_utility.set_location('l_forward_to_name is '||l_forward_to_name, 5);
1441 if p_action_taken in ('REOPENED','INITIATED') or (l_reclass_direct_flag = 'Y' and p_action_taken = 'RECLASSIFIED' ) then
1442 ghr_wf_pd_pkg.StartPDprocess
1443 (p_position_description_id => p_position_description_id,
1444 p_item_key => l_item_key_1,
1445 p_forward_to_name => l_forward_to_name
1446 );
1447 elsif p_action_taken in ('CLASSIFIED') and l_cnt_history = 1 then
1448 ghr_wf_pd_pkg.StartPDprocess
1449 (p_position_description_id => p_position_description_id,
1450 p_item_key => l_item_key_1,
1451 p_forward_to_name => l_forward_to_name
1452 );
1453 elsif p_action_taken in ('REQUESTED','AUTHORIZED') and l_cnt_history = 2 then
1454 ghr_wf_pd_pkg.StartPDprocess
1455 (p_position_description_id => p_position_description_id,
1456 p_item_key => l_item_key_1,
1457 p_forward_to_name => l_forward_to_name
1458 );
1459 else
1460 ghr_wf_pd_pkg.CompleteBlockingOfPD(
1461 p_position_description_id => p_position_description_id );
1462 end if;
1463 end call_workflow;
1464 end ghr_pdi_api;