[Home] [Help]
PACKAGE BODY: APPS.PQH_CPD_HR_TO_STAGE
Source
1 package body pqh_cpd_hr_to_stage as
2 /* $Header: pqcpdhrs.pkb 120.2 2010/03/04 06:42:35 sidsaxen ship $ */
3 function get_crpth_hier_ver return number is
4 cursor c1 is
5 SELECT gvr.hierarchy_version_id
6 FROM per_gen_hierarchy_versions gvr
7 ,per_gen_hierarchy gh
8 WHERE gh.type = 'CAREER_PATH'
9 AND gh.name = 'Corps Carrer Path ' --the seeded hierarchy name has a space in it.
10 AND gh.hierarchy_id = gvr.hierarchy_id
11 AND gvr.version_number = 1;
12 l_hierarchy_version_id number;
13 begin
14 open c1;
15 fetch c1 into l_hierarchy_version_id;
16 if c1%found then
17 return l_hierarchy_version_id;
18 else
19 hr_utility.set_location('hierarchy doesnot exist',10);
20 return -1;
21 end if;
22 end get_crpth_hier_ver;
23 procedure get_scale_ddf_det(p_scale_id in number,
24 p_information_category out nocopy varchar2,
25 p_information1 out nocopy varchar2,
26 p_information2 out nocopy varchar2) is
27 begin
28 hr_utility.set_location('getting scale ddf det',10);
29 if p_scale_id is not null then
30 select information_category,information1,information2
31 into p_information_category,p_information1,p_information2
32 from per_parent_spines
33 where parent_spine_id = p_scale_id;
34 end if;
35 exception
36 when others then
37 hr_utility.set_location('issues is selecting scale',10);
38 raise;
39 end get_scale_ddf_det;
40
41 procedure pgm_extra_info_update(p_pgm_id in number,
42 p_pgm_extra_info_id in number,
43 p_quota_flag in varchar2,
44 p_appraisal_type in varchar2,
45 p_review_period in number) is
46 l_peit_ovn number;
47 l_pgm_extra_info_id number;
48 begin
49 hr_utility.set_location('inside pgm_extra_info_update',10);
50 if p_pgm_extra_info_id is null then
51 hr_utility.set_location('insert pgm extra info ',10);
52 ben_pgm_extra_info_api.create_pgm_extra_info
53 ( p_pgm_id => p_pgm_id
54 ,p_information_type => 'PQH_FR_CORP_INFO'
55 ,p_pgi_information_category => 'PQH_FR_CORP_INFO'
56 ,p_pgi_information1 => p_quota_flag
57 ,p_pgi_information2 => p_appraisal_type
58 ,p_pgi_information3 => to_char(p_review_period)
59 ,p_pgm_extra_info_id => l_pgm_extra_info_id
60 ,p_object_version_number => l_peit_ovn
61 );
62 else
63 hr_utility.set_location('update pgm extra info',10);
64 l_peit_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PGM_EXTRA_INFO',
65 p_key_column_name => 'PGM_EXTRA_INFO_ID',
66 p_key_column_value => p_pgm_extra_info_id);
67 hr_utility.set_location(' ovn is '||l_peit_ovn,30);
68 ben_pgm_extra_info_api.update_pgm_extra_info
69 ( p_pgm_extra_info_id => p_pgm_extra_info_id
70 ,p_object_version_number => l_peit_ovn
71 ,p_pgi_information1 => p_quota_flag
72 ,p_pgi_information2 => p_appraisal_type
73 ,p_pgi_information3 => to_char(p_review_period)
74 );
75 end if;
76 hr_utility.set_location('leaving pgm_extra_info_update',10);
77 exception
78 when others then
79 raise;
80 end pgm_extra_info_update;
81
82 procedure get_pgm_extra_info(p_pgm_id in number,
83 p_quota_flag out nocopy varchar2,
84 p_appraisal_type out nocopy varchar2,
85 p_review_period out nocopy number,
86 p_pgm_extra_info_id out nocopy number) is
87
88 cursor c1 is
89 SELECT pgm_extra_info_id,pgi_information1,pgi_information2,pgi_information3
90 FROM ben_pgm_extra_info
91 where information_type ='PQH_FR_CORP_INFO'
92 and pgm_id = p_pgm_id;
93 begin
94 hr_utility.set_location('pgm id is'||p_pgm_id,10);
95 if p_pgm_id is not null then
96 for i in c1 loop
97 hr_utility.set_location('assigning value',11);
98 p_pgm_extra_info_id := i.pgm_extra_info_id;
99 p_quota_flag := i.pgi_information1;
100 p_appraisal_type := i.pgi_information2;
101 p_review_period := to_number(i.pgi_information3);
102 end loop;
103
104 end if;
105 exception
106 when no_data_found then
107 hr_utility.set_location('no extra info ',10);
108 when others then
109 hr_utility.set_location('issues is selecting pgm extra info',10);
110 raise;
111 end get_pgm_extra_info;
112
113 procedure get_point_details(p_point_id in number,
114 p_information_category out nocopy varchar2,
115 p_information1 out nocopy varchar2,
116 p_information2 out nocopy varchar2,
117 p_information3 out nocopy varchar2,
118 p_information4 out nocopy varchar2,
119 p_information5 out nocopy varchar2) is
120 begin
121 hr_utility.set_location('getting point ddf det',10);
122 if p_point_id is not null then
123 select information_category,information1,information2,information3,information4,information5
124 into p_information_category,p_information1,p_information2,p_information3,p_information4,p_information5
125 from per_spinal_points
126 where spinal_point_id = p_point_id;
127 end if;
128 exception
129 when others then
130 hr_utility.set_location('issues is selecting point',10);
131 raise;
132 end get_point_details;
133 procedure get_corp(p_pgm_cer_id in number,
134 p_corps_id out nocopy number,
135 p_cet_id out nocopy number) is
136 l_pgm_id number;
137 begin
138 select cer.information1,cer.copy_entity_txn_id,cpd.corps_definition_id
139 into l_pgm_id,p_cet_id,p_corps_id
140 from ben_copy_entity_results cer, pqh_corps_definitions cpd
141 where copy_entity_result_id = p_pgm_cer_id
142 and cpd.ben_pgm_id = cer.information1;
143 exception
144 when no_data_found then
145 p_cet_id := -1;
146 p_corps_id := -1;
147 when others then
148 raise;
149 end get_corp;
150 function build_comb_for_plip(p_cet_id in number,
151 p_comb_grade in varchar2) return varchar2 is
152 l_grade_id number;
153 l_plip_cer_id number;
154 l_grade_pos number;
155 l_comb_grade pqh_corps_extra_info.information30%type;
156 l_comb_plip_cer pqh_corps_extra_info.information30%type;
157 cursor get_plip_cer (p_grade_id number)is
158 select plip.copy_entity_result_id
159 from ben_copy_entity_results plip, ben_copy_entity_results pln
160 where plip.table_alias = 'CPP' -- plip row
161 and pln.table_alias = 'PLN'
162 and plip.information261 = pln.information1
163 and pln.information294 = p_grade_id
164 and pln.information141 = 'PER_GRADES'
165 and plip.copy_entity_txn_id = p_cet_id
166 and pln.copy_entity_txn_id = p_cet_id;
167 begin
168 -- data passed is having grade_ids concatenated
169 -- what we have to pass is plip_cer_ids concatenated for page to read
170 -- this routine will get the grade_ids and for the grade get the plip cer
171 l_comb_grade := p_comb_grade;
172 hr_utility.set_location('inside build_comb_for_plip cet'||p_cet_id,10);
173 loop
174 l_grade_pos := instr(l_comb_grade,',');
175 hr_utility.set_location('sep pos is'||l_grade_pos,11);
176 if l_grade_pos = 0 then
177 l_grade_id := l_comb_grade;
178 else
179 l_grade_id := substr(l_comb_grade,1,l_grade_pos-1);
180 l_comb_grade := substr(l_comb_grade,l_grade_pos+1);
181 end if;
182 hr_utility.set_location('grade is'||l_grade_id,12);
183 open get_plip_cer(l_grade_id);
184 fetch get_plip_cer into l_plip_cer_id;
185 if get_plip_cer%notfound then
186 hr_utility.set_location('plip not found '||l_grade_id,12);
187 close get_plip_cer;
188 else
189 close get_plip_cer;
190 hr_utility.set_location('plip cer is '||l_plip_cer_id,12);
191 if l_comb_plip_cer is null then
192 l_comb_plip_cer := l_plip_cer_id;
193 else
194 l_comb_plip_cer := l_comb_plip_cer||','||l_plip_cer_id;
195 end if;
196 end if;
197 if l_grade_pos = 0 then
198 exit;
199 end if;
200 end loop;
201 return l_comb_plip_cer;
202 end build_comb_for_plip;
203 function build_comb_for_grd(p_comb_plip in varchar2) return varchar2 is
204 l_grade_id number;
205 l_plip_cer_id number;
206 l_plip_pos number;
207 l_comb_grade pqh_corps_extra_info.information30%type;
208 l_comb_plip pqh_corps_extra_info.information30%type;
209 cursor get_grade(p_plip_cer_id number)is
210 select information253
211 from ben_copy_entity_results
212 where copy_entity_result_id = p_plip_cer_id;
213 begin
214 -- data passed is having grade_ids concatenated
215 -- what we have to pass is plip_cer_ids concatenated for page to read
216 -- this routine will get the grade_ids and for the grade get the plip cer
217 l_comb_plip := p_comb_plip;
218 hr_utility.set_location('inside build_comb_for_grd',10);
219 loop
220 l_plip_pos := instr(l_comb_plip,',');
221 hr_utility.set_location('separator pos is'||l_plip_pos,11);
222 if l_plip_pos = 0 then
223 l_plip_cer_id := l_comb_plip;
224 else
225 l_plip_cer_id := substr(l_comb_plip,1,l_plip_pos-1);
226 l_comb_plip := substr(l_comb_plip,l_plip_pos+1);
227 end if;
228 hr_utility.set_location('plip_cer is'||l_plip_cer_id,12);
229 open get_grade(l_plip_cer_id);
230 fetch get_grade into l_grade_id;
231 if get_grade%notfound then
232 close get_grade;
233 else
234 close get_grade;
235 hr_utility.set_location('grade is'||l_grade_id,12);
236 if l_comb_grade is null then
237 l_comb_grade := l_grade_id;
238 else
239 l_comb_grade := l_comb_grade||','||l_grade_id;
240 end if;
241 end if;
242 if nvl(l_plip_pos,0) = 0 then
243 exit;
244 end if;
245 end loop;
246 return l_comb_grade;
247 end build_comb_for_grd;
248 procedure get_grd_quota(p_pgm_cer_id in number,
249 p_grade_id in number,
250 p_corps_definition_id in number,
251 p_cet_id in number,
252 p_perc_quota out nocopy number,
253 p_population_cd out nocopy varchar2,
254 p_comb_grade out nocopy varchar2,
255 p_max_speed_quota out nocopy number,
256 p_avg_speed_quota out nocopy number,
257 p_corps_extra_info_id out nocopy number) is
258 l_comb_grade pqh_corps_extra_info.information30%type;
259 cursor c1 is
260 SELECT corps_extra_info_id,information4,information6,information7,information8,information30
261 FROM pqh_corps_extra_info
262 where information_type ='GRADE'
263 and corps_definition_id = p_corps_definition_id
264 and to_number(information3) = p_grade_id;
265 begin
266 hr_utility.set_location('grade id is'||p_grade_id,10);
267 hr_utility.set_location('cpd id id is'||p_corps_definition_id,10);
268 hr_utility.set_location('pgm_cer id is'||p_pgm_cer_id,10);
269 if p_corps_definition_id is not null then
270 for i in c1 loop
271 hr_utility.set_location('assigning value',11);
272 p_corps_extra_info_id := i.corps_extra_info_id;
273 p_perc_quota := i.information4;
274 p_max_speed_quota := i.information6;
275 p_avg_speed_quota := i.information7;
276 p_population_cd := i.information8;
277 l_comb_grade := i.information30;
278 end loop;
279 if l_comb_grade is not null then
280 hr_utility.set_location('going for building plip cer',11);
281 hr_utility.set_location('comb plip is'||substr(l_comb_grade,1,30),17);
282 hr_utility.set_location('comb plip2 is'||substr(l_comb_grade,31,30),17);
283 p_comb_grade := build_comb_for_plip(p_cet_id => p_cet_id,
284 p_comb_grade => l_comb_grade);
285 hr_utility.set_location('comb grd is'||substr(p_comb_grade,1,30),17);
286 hr_utility.set_location('comb grd2 is'||substr(p_comb_grade,31,30),17);
287 end if;
288 end if;
289 exception
290 when no_data_found then
291 hr_utility.set_location('no quota defined ',10);
292 when others then
293 hr_utility.set_location('issues is selecting quota',10);
294 raise;
295 end get_grd_quota;
296 function check_cdd_row(p_copy_entity_txn_id in number) return varchar2 is
297 l_cdd_exists varchar2(10) := 'N';
298 l_cdd_count number;
299 begin
300 select count(*)
301 into l_cdd_count
302 from ben_copy_entity_results
303 where copy_entity_txn_id = p_copy_entity_txn_id
304 and table_alias = 'CORPS_DOC';
305 if nvl(l_cdd_count,0) > 0 then
306 l_cdd_exists := 'Y';
307 else
308 l_cdd_exists := 'N';
309 end if;
310 return l_cdd_exists;
311 end check_cdd_row;
312 function check_cpd_row(p_copy_entity_txn_id in number) return varchar2 is
313 l_cpd_exists varchar2(10) := 'N';
314 l_cpd_count number;
315 begin
316 select count(*)
317 into l_cpd_count
318 from ben_copy_entity_results
319 where copy_entity_txn_id = p_copy_entity_txn_id
320 and table_alias = 'CPD';
321 if nvl(l_cpd_count,0) > 0 then
322 l_cpd_exists := 'Y';
323 else
324 l_cpd_exists := 'N';
325 end if;
326 return l_cpd_exists;
327 end check_cpd_row;
328 procedure crpaths_to_stage(p_copy_entity_txn_id in number,
329 p_corps_definition_id in number,
330 p_business_group_id in number,
331 p_effective_date in date,
332 p_cpd_cer_id in number) is
333 l_pth_tr_id number;
334 l_pth_tr_name varchar2(80);
335 l_pth_cer_id number;
336 l_pth_cer_ovn number;
337 l_continue boolean := TRUE;
338 l_hierarchy_version_id number;
339 l_step_cer_id number;
340 cursor csr_pth_rec is
341 select *
342 from per_gen_hierarchy_nodes
343 where information4 = p_corps_definition_id
344 and hierarchy_version_id = l_hierarchy_version_id;
345 begin
346 hr_utility.set_location('inside pth create',10);
347 l_hierarchy_version_id := get_crpth_hier_ver;
348 hr_utility.set_location('pth hier ver'||l_hierarchy_version_id,10);
349 if l_hierarchy_version_id is null then
350 l_continue := FALSE;
351 end if;
352 pqh_gsp_hr_to_stage.get_table_route_details
353 (p_table_alias => 'CRPATH',
354 p_table_route_id => l_pth_tr_id,
355 p_table_name => l_pth_tr_name);
356 hr_utility.set_location('pth tr name'||l_pth_tr_name,20);
357 if l_pth_tr_name is null then
358 hr_utility.set_location('pth tr name'||l_pth_tr_name,45);
359 l_continue := FALSE;
360 end if;
361 if p_copy_entity_txn_id is null then
362 hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
363 l_continue := FALSE;
364 end if;
365 if p_cpd_cer_id is null then
366 hr_utility.set_location('cpd cer is '||p_cpd_cer_id,50);
367 l_continue := FALSE;
368 end if;
369 if l_continue then
370 for pth_rec in csr_pth_rec loop
371 if pth_rec.entity_id is not null then
372 -- step id is there, for UI we need step cer id
373 begin
374 select copy_entity_result_id
375 into l_step_cer_id
376 from ben_copy_entity_results
377 where copy_entity_txn_id = p_copy_entity_txn_id
378 and table_alias = 'COP'
379 and information253 = pth_rec.entity_id;
380 exception
381 when others then
382 raise;
383 end;
384 end if;
385 hr_utility.set_location('step cer id is '||l_step_cer_id,10);
386 hr_utility.set_location('hier ver is '||l_hierarchy_version_id,11);
387 begin
388 hr_utility.set_location('cer insert api called',55);
389 ben_copy_entity_results_api.create_copy_entity_results
390 (p_effective_date => p_effective_date
391 ,p_copy_entity_txn_id => p_copy_entity_txn_id
392 ,p_result_type_cd => 'DISPLAY'
393 ,p_table_name => l_pth_tr_name
394 ,p_table_alias => 'CRPATH'
395 ,p_table_route_id => l_pth_tr_id
396 ,p_dml_operation => 'REUSE'
397 ,p_information1 => pth_rec.hierarchy_node_id
398 ,p_information2 => p_effective_date
399 ,p_information234 => pth_rec.entity_id
400 ,p_information229 => pth_rec.information3
401 ,p_information232 => pth_rec.information4
402 ,p_information227 => pth_rec.information9
403 ,p_information100 => pth_rec.information10
404 ,p_information162 => pth_rec.information11
405 ,p_information169 => pth_rec.information12
406 ,p_information174 => pth_rec.information13
407 ,p_information176 => pth_rec.information14
408 ,p_information178 => pth_rec.information15
409 ,p_information180 => pth_rec.information16
410 ,p_information221 => pth_rec.information17
411 ,p_information222 => pth_rec.information18
412 ,p_information223 => pth_rec.information19
413 ,p_information224 => pth_rec.information20
414 ,p_information225 => pth_rec.information21
415 ,p_information226 => pth_rec.information22
416 ,p_information228 => pth_rec.information23
417 ,p_information230 => pth_rec.information30
418 ,p_information298 => pth_rec.object_version_number
419 ,p_gs_mr_src_entity_result_id => l_step_cer_id
420 ,p_information161 => l_step_cer_id
421 ,p_information160 => p_cpd_cer_id
422 ,p_copy_entity_result_id => l_pth_cer_id
423 ,p_object_version_number => l_pth_cer_ovn);
424 exception
425 when others then
426 hr_utility.set_location('some issue in creating pth row ',120);
427 raise;
428 end;
429 end loop;
430 end if;
431 hr_utility.set_location('leaving create pth',55);
432 end crpaths_to_stage;
433 procedure documents_to_stage(p_copy_entity_txn_id in number,
434 p_corps_definition_id in number,
435 p_effective_date in date,
436 p_cpd_cer_id in number) is
437 l_cdd_tr_id number;
438 l_cdd_tr_name varchar2(80);
439 l_cdd_cer_id number;
440 l_cdd_cer_ovn number;
441 l_continue boolean := TRUE;
442 cursor csr_cdd_rec is
443 select *
444 from pqh_corps_extra_info
445 where corps_definition_id = p_corps_definition_id
446 and information_type = 'DOCUMENT';
447 begin
448 hr_utility.set_location('inside cdd create',10);
449 pqh_gsp_hr_to_stage.get_table_route_details
450 (p_table_alias => 'CORPS_DOC',
451 p_table_route_id => l_cdd_tr_id,
452 p_table_name => l_cdd_tr_name);
453 hr_utility.set_location('cdd tr name'||l_cdd_tr_name,20);
454 if l_cdd_tr_name is null then
455 hr_utility.set_location('cdd tr name'||l_cdd_tr_name,45);
456 l_continue := FALSE;
457 end if;
458 if p_copy_entity_txn_id is null then
459 hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
460 l_continue := FALSE;
461 end if;
462 if p_cpd_cer_id is null then
463 hr_utility.set_location('cpd cer is '||p_cpd_cer_id,50);
464 l_continue := FALSE;
465 end if;
466 if l_continue then
467 for cdd_rec in csr_cdd_rec loop
468 begin
469 hr_utility.set_location('cer insert api called',55);
470 ben_copy_entity_results_api.create_copy_entity_results
471 (p_effective_date => p_effective_date
472 ,p_copy_entity_txn_id => p_copy_entity_txn_id
473 ,p_result_type_cd => 'DISPLAY'
474 ,p_table_name => l_cdd_tr_name
475 ,p_table_alias => 'CORPS_DOC'
476 ,p_table_route_id => l_cdd_tr_id
477 ,p_dml_operation => 'REUSE'
478 ,p_information1 => cdd_rec.corps_extra_info_id
479 ,p_information101 => cdd_rec.information_type
480 ,p_information111 => cdd_rec.information3
481 ,p_information112 => cdd_rec.information4
482 ,p_information113 => cdd_rec.information5
483 ,p_information114 => cdd_rec.information6
484 ,p_information115 => cdd_rec.information7
485 ,p_information298 => cdd_rec.object_version_number
486 ,p_gs_mr_src_entity_result_id => p_cpd_cer_id
487 ,p_copy_entity_result_id => l_cdd_cer_id
488 ,p_object_version_number => l_cdd_cer_ovn);
489 exception
490 when others then
491 hr_utility.set_location('some issue in creating cdd row ',120);
492 raise;
493 end;
494 end loop;
495 end if;
496 hr_utility.set_location('leaving create cdd',55);
497 end documents_to_stage;
498 procedure corps_to_stage(p_copy_entity_txn_id in number,
499 p_pgm_id in number,
500 p_effective_date in date,
501 p_pgm_cer_id in number) is
502 cursor csr_cpd_rec is
503 select *
504 from pqh_corps_definitions
505 where ben_pgm_id = p_pgm_id;
506 l_cpd_tr_id number;
507 l_cpd_tr_name varchar2(80);
508 l_cpd_cer_id number;
509 l_cpd_cer_ovn number;
510 l_continue boolean := TRUE;
511 l_starting_plip_cer_id number;
512 l_starting_oipl_cer_id number;
513 l_quota_flag varchar2(30);
514 l_appraisal_type varchar2(30);
515 l_pgm_extra_info_id number;
516 l_review_period number;
517 begin
518 hr_utility.set_location('inside corps_to_stage',10);
519 for cpd_rec in csr_cpd_rec loop
520 pqh_gsp_hr_to_stage.get_table_route_details
521 (p_table_alias => 'CPD',
522 p_table_route_id => l_cpd_tr_id,
523 p_table_name => l_cpd_tr_name);
524 hr_utility.set_location('cpd tr name'||l_cpd_tr_name,20);
525 if l_cpd_tr_name is null then
526 hr_utility.set_location('cpd tr name'||l_cpd_tr_name,45);
527 l_continue := FALSE;
528 end if;
529 if p_copy_entity_txn_id is null then
530 hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
531 l_continue := FALSE;
532 end if;
533 if p_pgm_cer_id is null then
534 hr_utility.set_location('pgm cer is '||p_pgm_cer_id,50);
535 l_continue := FALSE;
536 end if;
537 if cpd_rec.starting_grade_id is not null then
538 hr_utility.set_location('starting grade is '||cpd_rec.starting_grade_id,60);
539 begin
540 select copy_entity_result_id
541 into l_starting_plip_cer_id
542 from ben_copy_entity_results
543 where copy_entity_txn_id = p_copy_entity_txn_id
544 and table_alias = 'CPP'
545 and information253 = cpd_rec.starting_grade_id;
546 exception
547 when no_data_found then
548 hr_utility.set_location('no plip found for the grd'||cpd_rec.starting_grade_id,65);
549 when others then
550 hr_utility.set_location('issues in selecting plip',70);
551 raise;
552 end;
553 if cpd_rec.starting_grade_step_id is not null then
554 hr_utility.set_location('starting grade step is '||cpd_rec.starting_grade_step_id,60);
555 begin
556 select copy_entity_result_id
557 into l_starting_oipl_cer_id
558 from ben_copy_entity_results
559 where copy_entity_txn_id = p_copy_entity_txn_id
560 and table_alias = 'COP'
561 and information253 = cpd_rec.starting_grade_step_id;
562 exception
563 when no_data_found then
564 hr_utility.set_location('no plip found for the grd'||cpd_rec.starting_grade_step_id,65);
565 when others then
566 hr_utility.set_location('issues in selecting oipl',70);
567 raise;
568 end;
569 end if;
570 end if;
571 if l_continue then
572 begin
573 get_pgm_extra_info(p_pgm_id => cpd_rec.ben_pgm_id,
574 p_quota_flag => l_quota_flag,
575 p_appraisal_type => l_appraisal_type,
576 p_review_period => l_review_period,
577 p_pgm_extra_info_id => l_pgm_extra_info_id);
578
579 hr_utility.set_location('quota flag '||l_quota_flag,55);
580 hr_utility.set_location('l_appraisal_type flag '||l_appraisal_type,55);
581 hr_utility.set_location('l_review_period flag '||l_review_period,55);
582 hr_utility.set_location('l_pgm_extra_info_id flag '||l_pgm_extra_info_id,55);
583
584 hr_utility.set_location('cer insert api called',55);
585 ben_copy_entity_results_api.create_copy_entity_results
586 (p_effective_date => p_effective_date
587 ,p_copy_entity_txn_id => p_copy_entity_txn_id
588 ,p_result_type_cd => 'DISPLAY'
589 ,p_table_name => l_cpd_tr_name
590 ,p_table_alias => 'CPD'
591 ,p_table_route_id => l_cpd_tr_id
592 ,p_dml_operation => 'REUSE'
593 ,p_information1 => cpd_rec.corps_definition_id
594 ,p_information2 => cpd_rec.date_from
595 ,p_information3 => cpd_rec.date_to
596 ,p_information4 => cpd_rec.business_group_id
597 ,p_information5 => cpd_rec.name
598 ,p_information11 => cpd_rec.type_of_ps
599 ,p_information14 => cpd_rec.corps_type_cd
600 ,p_information13 => cpd_rec.category_cd
601 ,p_information15 => cpd_rec.normal_hours_frequency
602 ,p_information16 => cpd_rec.minimum_hours_frequency
603 ,p_information17 => cpd_rec.probation_units
604 ,p_information160 => cpd_rec.starting_grade_id
605 ,p_information161 => cpd_rec.starting_grade_step_id
606 ,p_information162 => l_starting_plip_cer_id
607 ,p_information169 => l_starting_oipl_cer_id
608 ,p_information219 => cpd_rec.task_desc
609 ,p_information260 => cpd_rec.ben_pgm_id
610 ,p_information287 => cpd_rec.retirement_age
611 ,p_information288 => cpd_rec.secondment_threshold
612 ,p_information289 => cpd_rec.normal_hours
613 ,p_information290 => cpd_rec.minimum_hours
614 ,p_information291 => cpd_rec.probation_period
615 ,p_information292 => cpd_rec.primary_prof_field_id
616 ,p_information298 => cpd_rec.object_version_number
617 ,p_information307 => cpd_rec.recruitment_end_date
618 ,p_information18 => l_quota_flag
619 ,p_information19 => l_appraisal_type
620 ,p_information174 => l_pgm_extra_info_id
621 ,p_information221 => l_review_period
622 ,p_gs_mr_src_entity_result_id => p_pgm_cer_id
623 ,p_copy_entity_result_id => l_cpd_cer_id
624 ,p_object_version_number => l_cpd_cer_ovn);
625 exception
626 when others then
627 hr_utility.set_location('some issue in creating cpd row ',120);
628 raise;
629 end;
630 end if;
631 if l_cpd_cer_id is not null then
632 hr_utility.set_location('calling documents',55);
633 documents_to_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
634 p_corps_definition_id => cpd_rec.corps_definition_id,
635 p_effective_date => p_effective_date,
636 p_cpd_cer_id => l_cpd_cer_id);
637 hr_utility.set_location('documents done',55);
638 hr_utility.set_location('calling crpaths',55);
639 crpaths_to_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
640 p_corps_definition_id => cpd_rec.corps_definition_id,
641 p_business_group_id => cpd_rec.business_group_id,
642 p_effective_date => p_effective_date,
643 p_cpd_cer_id => l_cpd_cer_id);
644 hr_utility.set_location('crpaths done',55);
645 end if;
646 hr_utility.set_location('leaving create cpd',55);
647 end loop;
648 end corps_to_stage;
649 procedure stage_to_crpaths(p_copy_entity_txn_id in number,
650 p_effective_date in date,
651 p_pgm_id in number,
652 p_business_group_id in number,
653 p_datetrack_mode in varchar2) is
654 cursor csr_crpth_rec is
655 select *
656 from ben_copy_entity_results
657 where copy_entity_txn_id = p_copy_entity_txn_id
658 and table_alias = 'CRPATH'
659 and dml_operation <> 'REUSE';
660 l_pth_ovn number;
661 l_pth_id number;
662 l_cpd_id number;
663 l_entity_id number;
664 l_db_ovn number;
665 l_object varchar2(80);
666 l_message_text varchar2(2000);
667 l_hierarchy_version_id number;
668 begin
669 l_hierarchy_version_id := get_crpth_hier_ver;
670
671 update ben_copy_entity_results
672 set dml_operation = 'DELETE'
673 where copy_entity_txn_id = p_copy_entity_txn_id
674 and table_alias in ('CRPATH')
675 and information104 = 'UNLINK';
676
677 for pth_rec in csr_crpth_rec loop
678 l_pth_id := pth_rec.information1;
679 l_pth_ovn := pth_rec.information298;
680 if pth_rec.information232 is null and pth_rec.information160 is not null then
681 hr_utility.set_location('getting corps id from corp_cer',10);
682 select information1
683 into l_cpd_id
684 from ben_copy_entity_results
685 where copy_entity_result_id = pth_rec.information160;
686 else
687 l_cpd_id := pth_rec.information232;
688 end if;
689 if pth_rec.information234 is null and pth_rec.information161 is not null then
690 hr_utility.set_location('getting step id from oipl_cer',10);
691 select information253
692 into l_entity_id
693 from ben_copy_entity_results
694 where copy_entity_result_id = pth_rec.information161;
695 else
696 l_entity_id := pth_rec.information234;
697 end if;
698 if pth_rec.dml_operation ='INSERT'
699 and l_pth_id is null
700 and l_entity_id is not null
701 and l_hierarchy_version_id is not null
702 and l_cpd_id is not null then
703 per_hierarchy_nodes_api.create_hierarchy_nodes
704 (p_hierarchy_node_id => l_pth_id
705 ,p_business_group_id => p_business_group_id
706 ,p_entity_id => l_entity_id
707 ,p_hierarchy_version_id => l_hierarchy_version_id
708 ,p_object_version_number => l_pth_ovn
709 ,p_node_type => 'CAREER_NODE'
710 ,p_seq => 40
711 ,p_information_category => 'CAREER_NODE'
712 ,p_information3 => pth_rec.information229
713 ,p_information4 => pth_rec.information232
714 ,p_information9 => pth_rec.information227
715 ,p_information10 => pth_rec.information100
716 ,p_information11 => pth_rec.information162
717 ,p_information12 => pth_rec.information169
718 ,p_information13 => pth_rec.information174
719 ,p_information14 => pth_rec.information176
720 ,p_information15 => pth_rec.information178
721 ,p_information16 => pth_rec.information180
722 ,p_information17 => pth_rec.information221
723 ,p_information18 => pth_rec.information222
724 ,p_information19 => pth_rec.information223
725 ,p_information20 => pth_rec.information224
726 ,p_information21 => pth_rec.information225
727 ,p_information22 => pth_rec.information226
728 ,p_information23 => pth_rec.information228
729 ,p_information30 => nvl(pth_rec.information230,p_pgm_id)
730 ,p_effective_date => p_effective_date
731 );
732 elsif pth_rec.dml_operation ='UPDATE'
733 and l_pth_id is not null
734 and l_hierarchy_version_id is not null
735 and l_pth_ovn is not null
736 and l_entity_id is not null
737 and l_cpd_id is not null then
738 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
739 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'PER_GEN_HIERARCHY_NODES',
740 p_key_column_name => 'HIERARCHY_NODE_ID',
741 p_key_column_value => l_pth_id);
742 hr_utility.set_location(' ovn is '||l_db_ovn,30);
743 if l_db_ovn <> l_pth_ovn then
744 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','CCP');
745 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
746 fnd_message.set_token('OBJECT ',l_object);
747 fnd_message.set_token('OBJECT_NAME ',substr(pth_rec.information1,1,30));
748 fnd_message.raise_error;
749 else
750 per_hierarchy_nodes_api.update_hierarchy_nodes
751 (p_hierarchy_node_id => l_pth_id
752 ,p_entity_id => pth_rec.information234
753 ,p_object_version_number => l_pth_ovn
754 ,p_node_type => 'CAREER_NODE'
755 ,p_seq => 40
756 ,p_information_category => 'CAREER_NODE'
757 ,p_information3 => pth_rec.information229
758 ,p_information4 => pth_rec.information232
759 ,p_information9 => pth_rec.information227
760 ,p_information10 => pth_rec.information100
761 ,p_information11 => pth_rec.information162
762 ,p_information12 => pth_rec.information169
763 ,p_information13 => pth_rec.information174
764 ,p_information14 => pth_rec.information176
765 ,p_information15 => pth_rec.information178
766 ,p_information16 => pth_rec.information180
767 ,p_information17 => pth_rec.information221
768 ,p_information18 => pth_rec.information222
769 ,p_information19 => pth_rec.information223
770 ,p_information20 => pth_rec.information224
771 ,p_information21 => pth_rec.information225
772 ,p_information22 => pth_rec.information226
773 ,p_information23 => pth_rec.information228
774 ,p_information30 => pth_rec.information230
775 ,p_effective_date => p_effective_date
776 );
777 end if;
778 elsif pth_rec.dml_operation ='DELETE'
779 and l_pth_id is not null
780 and l_pth_ovn is not null then
781 per_hierarchy_nodes_api.delete_hierarchy_nodes
782 (p_hierarchy_node_id => l_pth_id
783 ,p_object_version_number => l_pth_ovn);
784 else
785 l_message_text := 'invalid dml_oper'||pth_rec.dml_operation
786 ||' pth_ovn:'||l_pth_ovn
787 ||' hier_ver:'||l_hierarchy_version_id
788 ||' l_pth_id:'||l_pth_id
789 ||' l_entity_id:'||l_entity_id
790 ||' l_cpd_id:'||l_cpd_id;
791 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
792 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
793 P_TXN_ID => nvl(l_pth_id,p_copy_entity_txn_id),
794 P_MODULE_CD => 'PQH_GSP_STGBEN',
795 p_context => 'CRPATH',
796 P_MESSAGE_TYPE_CD => 'E',
797 P_MESSAGE_TEXT => l_message_text,
798 p_effective_date => p_effective_date);
799 end if;
800 end loop;
801 end stage_to_crpaths;
802 procedure stage_to_docs(p_copy_entity_txn_id in number,
803 p_effective_date in date,
804 p_business_group_id in number,
805 p_datetrack_mode in varchar2) is
806 cursor csr_doc_rec is
807 select *
808 from ben_copy_entity_results
809 where copy_entity_txn_id = p_copy_entity_txn_id
810 and table_alias = 'CORPS_DOC'
811 and dml_operation <> 'REUSE';
812 l_cdd_ovn number;
813 l_cpd_id number;
814 l_cdd_id number;
815 l_db_ovn number;
816 l_object varchar2(80);
817 l_message_text varchar2(2000);
818 begin
819 for cdd_rec in csr_doc_rec loop
820 l_cdd_id := cdd_rec.information1;
821 l_cdd_ovn := cdd_rec.information298;
822 if cdd_rec.information160 is null then
823 select information1
824 into l_cpd_id
825 from ben_copy_entity_results
826 where copy_entity_result_id = cdd_rec.GS_MIRROR_SRC_ENTITY_RESULT_ID;
827 else
828 l_cpd_id := cdd_rec.information160;
829 end if;
830 if cdd_rec.dml_operation ='INSERT'
831 and l_cdd_id is null
832 and l_cpd_id is not null then
833 pqh_corps_extra_info_api.create_corps_extra_info
834 (p_effective_date => p_effective_date
835 ,p_corps_extra_info_id => l_cdd_id
836 ,p_corps_definition_id => l_cpd_id
837 ,p_information_type => 'DOCUMENT'
838 ,p_information3 => cdd_rec.information111
839 ,p_information4 => cdd_rec.information112
840 ,p_information5 => cdd_rec.information113
841 ,p_information6 => cdd_rec.information114
842 ,p_information7 => cdd_rec.information115
843 ,p_object_version_number => l_cdd_ovn
844 );
845 elsif cdd_rec.dml_operation ='UPDATE'
846 and l_cdd_id is not null
847 and l_cdd_ovn is not null
848 and l_cpd_id is not null then
849 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
850 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'PQH_CORPS_EXTRA_INFO',
851 p_key_column_name => 'CORPS_EXTRA_INFO_ID',
852 p_key_column_value => l_cdd_id);
853 hr_utility.set_location(' ovn is '||l_db_ovn,30);
854 if l_db_ovn <> l_cdd_ovn then
855 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','CDD');
856 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
857 fnd_message.set_token('OBJECT ',l_object);
858 fnd_message.set_token('OBJECT_NAME ',substr(cdd_rec.information113,1,30));
859 fnd_message.raise_error;
860 else
861 pqh_corps_extra_info_api.update_corps_extra_info
862 (p_effective_date => p_effective_date
863 ,p_corps_extra_info_id => l_cdd_id
864 ,p_corps_definition_id => l_cpd_id
865 ,p_information3 => cdd_rec.information111
866 ,p_information4 => cdd_rec.information112
867 ,p_information5 => cdd_rec.information113
868 ,p_information6 => cdd_rec.information114
869 ,p_information7 => cdd_rec.information115
870 ,p_object_version_number => l_cdd_ovn
871 );
872 end if;
873 elsif cdd_rec.dml_operation ='DELETE'
874 and l_cdd_id is not null
875 and l_cdd_ovn is not null then
876 pqh_corps_extra_info_api.delete_corps_extra_info
877 (p_corps_extra_info_id => l_cdd_id
878 ,p_object_version_number => l_cdd_ovn);
879 else
880 l_message_text := 'invalid dml_oper'||cdd_rec.dml_operation
881 ||' cdd_ovn:'||l_cdd_ovn
882 ||' l_cpd_id:'||l_cpd_id;
883 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
884 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
885 P_TXN_ID => nvl(l_cdd_id,p_copy_entity_txn_id),
886 P_MODULE_CD => 'PQH_GSP_STGBEN',
887 p_context => 'CORPS_DOC',
888 P_MESSAGE_TYPE_CD => 'E',
889 P_MESSAGE_TEXT => l_message_text,
890 p_effective_date => p_effective_date);
891 end if;
892 end loop;
893 end stage_to_docs;
894 procedure cpd_writeback(p_copy_entity_txn_id in number,
895 p_cpd_id in number,
896 p_cpd_cer_id in number) is
897 begin
898 hr_utility.set_location('cpd id is '||p_cpd_id,1);
899 hr_utility.set_location('cpd cer id is '||p_cpd_cer_id,2);
900 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
901 -- update corp rows with corps id
902 begin
903 hr_utility.set_location('updating plips for cpd :'||p_cpd_id,4);
904 update ben_copy_entity_results
905 set information1 = p_cpd_id
906 where copy_entity_txn_id = p_copy_entity_txn_id
907 and table_alias = 'CPD';
908 hr_utility.set_location('num of corp updated'||sql%rowcount,20);
909 exception
910 when others then
911 hr_utility.set_location('issues in updating cpd to corp',10);
912 raise;
913 end;
914 -- update crpath rows with corps id
915 begin
916 hr_utility.set_location('updating plips for cpd :'||p_cpd_id,4);
917 update ben_copy_entity_results
918 set information232 = p_cpd_id
919 where copy_entity_txn_id = p_copy_entity_txn_id
920 and table_alias = 'CRPATH';
921 hr_utility.set_location('num of crpaths updated'||sql%rowcount,20);
922 exception
923 when others then
924 hr_utility.set_location('issues in updating cpd to crpaths',10);
925 raise;
926 end;
927 -- update cpp rows with corps id
928 begin
929 hr_utility.set_location('updating plips for cpd :'||p_cpd_id,4);
930 update ben_copy_entity_results
931 set information291 = p_cpd_id
932 where copy_entity_txn_id = p_copy_entity_txn_id
933 and table_alias = 'CPP';
934 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
935 exception
936 when others then
937 hr_utility.set_location('issues in updating cpd to plips',10);
938 raise;
939 end;
940 -- update cdd rows with corps id
941 begin
942 hr_utility.set_location('updating docs for cpd :'||p_cpd_id,4);
943 update ben_copy_entity_results
944 set information160 = p_cpd_id
945 where copy_entity_txn_id = p_copy_entity_txn_id
946 and table_alias = 'CORPS_DOC';
947 hr_utility.set_location('num of docs updated'||sql%rowcount,20);
948 exception
949 when others then
950 hr_utility.set_location('issues in updating cpd to docs',10);
951 raise;
952 end;
953 end cpd_writeback;
954 procedure pop_pri_filiere(p_corps_definition_id in number,
955 p_filiere_id in number,
956 p_effective_date in date) is
957 cursor c1 is select corps_extra_info_id
958 from pqh_corps_extra_info
959 where corps_definition_id = p_corps_definition_id
960 and information4 = p_filiere_id
961 and information_type = 'FILERE';
962 l_cei_id number;
963 l_cei_ovn number;
964 begin
965 open c1;
966 fetch c1 into l_cei_id;
967 if c1%notfound then
968 pqh_corps_extra_info_api.create_corps_extra_info(
969 p_effective_date => p_effective_date
970 ,p_corps_extra_info_id => l_cei_id
971 ,p_corps_definition_id => p_corps_definition_id
972 ,p_information_type => 'FILERE'
973 ,p_information4 => p_filiere_id
974 ,p_object_version_number => l_cei_ovn
975 );
976 end if;
977 close c1;
978 end pop_pri_filiere;
979
980 --
981 PROCEDURE stage_to_corps (
982 p_copy_entity_txn_id IN NUMBER,
983 p_effective_date IN DATE,
984 p_business_group_id IN NUMBER,
985 p_datetrack_mode IN VARCHAR2
986 )
987 IS
988 CURSOR csr_corp_rec
989 IS
990 SELECT *
991 FROM ben_copy_entity_results
992 WHERE copy_entity_txn_id = p_copy_entity_txn_id
993 AND table_alias = 'CPD'
994 AND dml_operation <> 'REUSE';
995
996 l_pgm_id NUMBER;
997 l_pgm_esd DATE;
998 l_pgm_name VARCHAR2 (240);
999 l_cpd_ovn NUMBER;
1000 l_cpd_id NUMBER;
1001 l_db_ovn NUMBER;
1002 l_object VARCHAR2 (80);
1003 l_message_text VARCHAR2 (2000);
1004 l_min_freq VARCHAR2 (30);
1005 l_starting_grade_id NUMBER;
1006 l_starting_step_id NUMBER;
1007 l_plip_cer_id NUMBER;
1008 l_oipl_cer_id NUMBER;
1009
1010 CURSOR c_plip
1011 IS
1012 SELECT copy_entity_result_id, information253
1013 FROM ben_copy_entity_results
1014 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1015 AND table_alias = 'CPP'
1016 AND information104 <> 'UNLINK'
1017 AND result_type_cd = 'DISPLAY'
1018 ORDER BY information263;
1019
1020 CURSOR c_oipl (p_plip_cer_id NUMBER)
1021 IS
1022 SELECT copy_entity_result_id, information253
1023 FROM ben_copy_entity_results
1024 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1025 AND table_alias = 'COP'
1026 AND information104 <> 'UNLINK'
1027 AND gs_parent_entity_result_id = p_plip_cer_id
1028 AND result_type_cd = 'DISPLAY'
1029 ORDER BY information263;
1030
1031 l_status_cd VARCHAR2 (10);
1032 l_updated BOOLEAN;
1033 l_corps_def_id NUMBER;
1034 l_ovn NUMBER;
1035 BEGIN
1036 l_updated := FALSE;
1037
1038 SELECT DECODE (information38, 'A', 'ACTIVE', 'INACTIVE')
1039 INTO l_status_cd
1040 FROM ben_copy_entity_results
1041 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1042 AND table_alias = 'PGM'
1043 AND dml_operation <> 'REUSE';
1044
1045 FOR cpd_rec IN csr_corp_rec
1046 LOOP
1047 l_cpd_id := cpd_rec.information1;
1048 l_cpd_ovn := cpd_rec.information298;
1049 l_updated := TRUE;
1050
1051 BEGIN
1052 SELECT information1, information5, information2
1053 INTO l_pgm_id, l_pgm_name, l_pgm_esd
1054 FROM ben_copy_entity_results
1055 WHERE copy_entity_result_id =
1056 cpd_rec.gs_mirror_src_entity_result_id;
1057 EXCEPTION
1058 WHEN OTHERS
1059 THEN
1060 hr_utility.set_location ('issues in getting pgm name', 10);
1061 RAISE;
1062 END;
1063
1064 hr_utility.set_location ('corps name is ' || l_pgm_name, 10);
1065 hr_utility.set_location ('pgm id is ' || l_pgm_id, 10);
1066
1067 IF cpd_rec.information290 IS NOT NULL
1068 THEN
1069 l_min_freq := cpd_rec.information15;
1070 ELSE
1071 l_min_freq := '';
1072 END IF;
1073
1074 IF cpd_rec.information162 IS NULL
1075 THEN
1076 hr_utility.set_location
1077 ('no value selected, get lowest plip grade',
1078 10
1079 );
1080
1081 OPEN c_plip;
1082
1083 FETCH c_plip
1084 INTO l_plip_cer_id, l_starting_grade_id;
1085
1086 IF c_plip%NOTFOUND
1087 THEN
1088 hr_utility.set_location ('no plip defined', 10);
1089 ELSE
1090 hr_utility.set_location ('plip cer is' || l_plip_cer_id, 10);
1091
1092 OPEN c_oipl (l_plip_cer_id);
1093
1094 FETCH c_oipl
1095 INTO l_oipl_cer_id, l_starting_step_id;
1096
1097 IF c_oipl%NOTFOUND
1098 THEN
1099 hr_utility.set_location ( 'no oipl defined for plip'
1100 || l_plip_cer_id,
1101 30
1102 );
1103 ELSE
1104 hr_utility.set_location ('oipl cer is' || l_oipl_cer_id,
1105 10);
1106 END IF;
1107
1108 CLOSE c_oipl;
1109 END IF;
1110
1111 CLOSE c_plip;
1112 ELSE
1113 hr_utility.set_location ('start plip entered', 10);
1114
1115 IF cpd_rec.information169 IS NULL
1116 THEN
1117 hr_utility.set_location ('start oipl not entered,get lowest',
1118 10
1119 );
1120
1121 OPEN c_oipl (cpd_rec.information162);
1122
1123 FETCH c_oipl
1124 INTO l_oipl_cer_id, l_starting_step_id;
1125
1126 IF c_oipl%NOTFOUND
1127 THEN
1128 hr_utility.set_location ( 'no oipl defined for plip'
1129 || cpd_rec.information162,
1130 30
1131 );
1132 END IF;
1133
1134 CLOSE c_oipl;
1135 ELSE
1136 hr_utility.set_location
1137 ('start plip and oipl entered,get grade step',
1138 50
1139 );
1140
1141 BEGIN
1142 SELECT information253
1143 INTO l_starting_grade_id
1144 FROM ben_copy_entity_results
1145 WHERE copy_entity_result_id = cpd_rec.information162;
1146 EXCEPTION
1147 WHEN OTHERS
1148 THEN
1149 hr_utility.set_location
1150 ('issues in selecting grade for plip',
1151 10
1152 );
1153 END;
1154
1155 BEGIN
1156 SELECT information253
1157 INTO l_starting_step_id
1158 FROM ben_copy_entity_results
1159 WHERE copy_entity_result_id = cpd_rec.information169;
1160 EXCEPTION
1161 WHEN OTHERS
1162 THEN
1163 hr_utility.set_location
1164 ('issues in selecting step for oipl',
1165 10
1166 );
1167 END;
1168 END IF;
1169 END IF;
1170
1171 hr_utility.set_location ('starting grade id is'
1172 || l_starting_grade_id,
1173 100
1174 );
1175 hr_utility.set_location ('starting step id is' || l_starting_step_id,
1176 100
1177 );
1178
1179 IF cpd_rec.dml_operation = 'INSERT'
1180 AND l_cpd_id IS NULL
1181 AND l_pgm_id IS NOT NULL
1182 AND l_pgm_esd IS NOT NULL
1183 AND l_pgm_name IS NOT NULL
1184 THEN
1185 pqh_corps_definitions_api.create_corps_definition
1186 (p_effective_date => p_effective_date,
1187 p_date_from => l_pgm_esd,
1188 p_date_to => cpd_rec.information3,
1189 p_business_group_id => p_business_group_id,
1190 p_name => l_pgm_name,
1191 p_type_of_ps => cpd_rec.information11,
1192 p_corps_type_cd => cpd_rec.information14,
1193 p_category_cd => cpd_rec.information13,
1194 p_normal_hours_frequency => cpd_rec.information15,
1195 p_minimum_hours_frequency => l_min_freq,
1196 p_probation_units => cpd_rec.information17,
1197 p_task_desc => cpd_rec.information219,
1198 p_starting_grade_id => l_starting_grade_id,
1199 p_starting_grade_step_id => l_starting_step_id,
1200 p_retirement_age => cpd_rec.information287,
1201 p_secondment_threshold => cpd_rec.information288,
1202 p_normal_hours => cpd_rec.information289,
1203 p_minimum_hours => cpd_rec.information290,
1204 p_probation_period => cpd_rec.information291,
1205 p_primary_prof_field_id => cpd_rec.information292,
1206 p_recruitment_end_date => cpd_rec.information307,
1207 p_status_cd => l_status_cd,
1208 p_ben_pgm_id => l_pgm_id,
1209 p_corps_definition_id => l_cpd_id,
1210 p_object_version_number => l_cpd_ovn
1211 );
1212 pgm_extra_info_update(p_pgm_id => l_pgm_id,
1213 p_pgm_extra_info_id => cpd_rec.information174,
1214 p_quota_flag => cpd_rec.information18,
1215 p_appraisal_type => cpd_rec.information19,
1216 p_review_period => cpd_rec.information221);
1217
1218 ELSIF ( cpd_rec.dml_operation = 'UPDATE'
1219 OR NVL (cpd_rec.information5, 'X') <> l_pgm_name
1220 )
1221 AND l_cpd_id IS NOT NULL
1222 AND l_cpd_ovn IS NOT NULL
1223 AND l_pgm_name IS NOT NULL
1224 AND l_pgm_id IS NOT NULL
1225 THEN
1226 hr_utility.set_location (' dt mode is ' || p_datetrack_mode, 30);
1227 l_db_ovn :=
1228 pqh_gsp_stage_to_ben.get_ovn
1229 (p_table_name => 'PQH_CORPS_DEFINITIONS',
1230 p_key_column_name => 'CORPS_DEFINITION_ID',
1231 p_key_column_value => l_cpd_id
1232 );
1233 hr_utility.set_location (' ovn is ' || l_db_ovn, 30);
1234
1235 IF l_db_ovn <> l_cpd_ovn
1236 THEN
1237 l_object :=
1238 hr_general.decode_lookup ('PQH_GSP_OBJECT_TYPE', 'CPD');
1239 fnd_message.set_name ('PQH', 'PQH_GSP_OBJ_OVN_INVALID');
1240 fnd_message.set_token ('OBJECT ', l_object);
1241 fnd_message.set_token ('OBJECT_NAME ', cpd_rec.information5);
1242 fnd_message.raise_error;
1243 ELSE
1244 pqh_corps_definitions_api.update_corps_definition
1245 (p_effective_date => p_effective_date,
1246 p_date_from => l_pgm_esd,
1247 p_date_to => cpd_rec.information3,
1248 p_business_group_id => p_business_group_id,
1249 p_name => l_pgm_name,
1250 p_type_of_ps => cpd_rec.information11,
1251 p_corps_type_cd => cpd_rec.information14,
1252 p_category_cd => cpd_rec.information13,
1253 p_normal_hours_frequency => cpd_rec.information15,
1254 p_minimum_hours_frequency => l_min_freq,
1255 p_probation_units => cpd_rec.information17,
1256 p_task_desc => cpd_rec.information219,
1257 p_starting_grade_id => l_starting_grade_id,
1258 p_starting_grade_step_id => l_starting_step_id,
1259 p_retirement_age => cpd_rec.information287,
1260 p_secondment_threshold => cpd_rec.information288,
1261 p_normal_hours => cpd_rec.information289,
1262 p_minimum_hours => cpd_rec.information290,
1263 p_probation_period => cpd_rec.information291,
1264 p_primary_prof_field_id => cpd_rec.information292,
1265 p_recruitment_end_date => cpd_rec.information307,
1266 p_status_cd => l_status_cd,
1267 p_ben_pgm_id => l_pgm_id,
1268 p_corps_definition_id => l_cpd_id,
1269 p_object_version_number => l_cpd_ovn
1270 );
1271 pgm_extra_info_update(p_pgm_id => l_pgm_id,
1272 p_pgm_extra_info_id => cpd_rec.information174,
1273 p_quota_flag => cpd_rec.information18,
1274 p_appraisal_type => cpd_rec.information19,
1275 p_review_period => cpd_rec.information221);
1276 END IF;
1277 ELSE
1278 l_message_text :=
1279 'invalid dml_oper'
1280 || cpd_rec.dml_operation
1281 || ' cpd_ovn:'
1282 || l_cpd_ovn
1283 || ' ben_pgm_id:'
1284 || l_pgm_id
1285 || ' ben_pgm_esd:'
1286 || l_pgm_esd
1287 || ' ben_pgm_name:'
1288 || l_pgm_name
1289 || ' for cpd_id:'
1290 || l_cpd_id;
1291 pqh_gsp_process_log.log_process_dtls
1292 (p_master_txn_id => p_copy_entity_txn_id,
1293 p_txn_id => NVL
1294 (l_cpd_id,
1295 p_copy_entity_txn_id
1296 ),
1297 p_module_cd => 'PQH_GSP_STGBEN',
1298 p_context => 'CPD',
1299 p_message_type_cd => 'E',
1300 p_message_text => l_message_text,
1301 p_effective_date => p_effective_date
1302 );
1303 END IF;
1304
1305 IF l_cpd_id IS NOT NULL AND cpd_rec.information292 IS NOT NULL
1306 THEN
1307 hr_utility.set_location ('going for populating pri filere', 10);
1308 pop_pri_filiere (p_corps_definition_id => l_cpd_id,
1309 p_filiere_id => cpd_rec.information292,
1310 p_effective_date => p_effective_date
1311 );
1312 hr_utility.set_location ('pri filere done', 11);
1313 END IF;
1314
1315 IF l_cpd_id IS NOT NULL AND cpd_rec.copy_entity_result_id IS NOT NULL
1316 THEN
1317 hr_utility.set_location ('going for cpd writeback ', 12);
1318 cpd_writeback (p_copy_entity_txn_id => p_copy_entity_txn_id,
1319 p_cpd_id => l_cpd_id,
1320 p_cpd_cer_id => cpd_rec.copy_entity_result_id
1321 );
1322 hr_utility.set_location ('cpd writeback done', 13);
1323 END IF;
1324 END LOOP;
1325
1326 IF l_updated = FALSE
1327 THEN
1328 SELECT information1, information298
1329 INTO l_corps_def_id, l_cpd_ovn
1330 FROM ben_copy_entity_results
1331 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1332 AND table_alias = 'CPD'
1333 AND dml_operation = 'REUSE';
1334
1335 pqh_corps_definitions_api.update_corps_definition
1336 (p_effective_date => p_effective_date,
1337 p_corps_definition_id => l_corps_def_id,
1338 p_status_cd => l_status_cd,
1339 p_object_version_number => l_cpd_ovn
1340 );
1341 END IF;
1342
1343 stage_to_docs (p_copy_entity_txn_id => p_copy_entity_txn_id,
1344 p_effective_date => p_effective_date,
1345 p_business_group_id => p_business_group_id,
1346 p_datetrack_mode => p_datetrack_mode
1347 );
1348 stage_to_crpaths (p_copy_entity_txn_id => p_copy_entity_txn_id,
1349 p_effective_date => p_effective_date,
1350 p_pgm_id => l_pgm_id,
1351 p_business_group_id => p_business_group_id,
1352 p_datetrack_mode => p_datetrack_mode
1353 );
1354 EXCEPTION
1355 WHEN OTHERS
1356 THEN
1357 hr_utility.set_location ('Error thrown', 13);
1358 pqh_gsp_process_log.log_process_dtls
1359 (p_master_txn_id => p_copy_entity_txn_id,
1360 p_txn_id => NVL
1361 (l_cpd_id,
1362 p_copy_entity_txn_id
1363 ),
1364 p_module_cd => 'PQH_GSP_STGBEN',
1365 p_context => 'CPD',
1366 p_message_type_cd => 'E',
1367 p_message_text => l_message_text,
1368 p_effective_date => p_effective_date
1369 );
1370 RAISE;
1371 END stage_to_corps;
1372
1373 --
1374 procedure grd_quota_update(p_effective_date in date,
1375 p_grade_id in number,
1376 p_corps_definition_id in number,
1377 p_corps_extra_info_id in number,
1378 p_perc_quota in number,
1379 p_population_cd in varchar2,
1380 p_comb_grades in varchar2,
1381 p_max_speed_quota in number,
1382 p_avg_speed_quota in number) is
1383 l_db_ovn number;
1384 l_cei_id number;
1385 l_comp_grd varchar2(2000);
1386 begin
1387 hr_utility.set_location('inside grd_quota_update',10);
1388 if p_comb_grades is not null then
1389 l_comp_grd := build_comb_for_grd(p_comb_plip => p_comb_grades);
1390 end if;
1391 if p_corps_extra_info_id is null then
1392 hr_utility.set_location('insert grd quota ',10);
1393 pqh_corps_extra_info_api.create_corps_extra_info(
1394 p_effective_date => p_effective_date
1395 ,p_corps_extra_info_id => l_cei_id
1396 ,p_corps_definition_id => p_corps_definition_id
1397 ,p_information_type => 'GRADE'
1398 ,p_information3 => p_grade_id
1399 ,p_information4 => p_perc_quota
1400 ,p_information6 => p_max_speed_quota
1401 ,p_information7 => p_avg_speed_quota
1402 ,p_information8 => p_population_cd
1403 ,p_information30 => l_comp_grd
1404 ,p_object_version_number => l_db_ovn
1405 );
1406 else
1407 hr_utility.set_location('update grd quota',10);
1408 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'PQH_CORPS_EXTRA_INFO',
1409 p_key_column_name => 'CORPS_EXTRA_INFO_ID',
1410 p_key_column_value => p_corps_extra_info_id);
1411 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1412 pqh_corps_extra_info_api.update_corps_extra_info(
1413 p_effective_date => p_effective_date
1414 ,p_corps_extra_info_id => p_corps_extra_info_id
1415 ,p_corps_definition_id => p_corps_definition_id
1416 ,p_information_type => 'GRADE'
1417 ,p_information3 => p_grade_id
1418 ,p_information4 => p_perc_quota
1419 ,p_information6 => p_max_speed_quota
1420 ,p_information7 => p_avg_speed_quota
1421 ,p_information8 => p_population_cd
1422 ,p_information30 => l_comp_grd
1423 ,p_object_version_number => l_db_ovn
1424 );
1425 end if;
1426 hr_utility.set_location('leaving grd_quota_update',10);
1427 exception
1428 when others then
1429 raise;
1430 end grd_quota_update;
1431 procedure pull_career_path(p_copy_entity_txn_id in number,
1432 p_step_id in number,
1433 p_effective_date in date,
1434 p_grade_id in number) is
1435 begin
1436 null;
1437 end pull_career_path;
1438 procedure update_point(p_point_id in number,
1439 p_point_ovn in out nocopy number,
1440 p_information_category in varchar2,
1441 p_information1 in varchar2,
1442 p_information2 in varchar2,
1443 p_information3 in varchar2,
1444 p_information4 in varchar2,
1445 p_information5 in varchar2,
1446 p_effective_date in date,
1447 p_business_group_id in number,
1448 p_parent_spine_id in number,
1449 p_sequence in number,
1450 p_spinal_point in varchar2) is
1451 begin
1452 hr_progression_point_api.update_progression_point
1453 (p_effective_date => p_effective_date
1454 ,p_business_group_id => p_business_group_id
1455 ,p_parent_spine_id => p_parent_spine_id
1456 ,p_sequence => p_sequence
1457 ,p_spinal_point => p_spinal_point
1458 ,p_spinal_point_id => p_point_id
1459 ,p_object_version_number => p_point_ovn
1460 ,p_information_category => p_information_category
1461 ,p_information1 => p_information1
1462 ,p_information2 => p_information2
1463 ,p_information3 => p_information3
1464 ,p_information4 => p_information4
1465 ,p_information5 => p_information5
1466 ,p_called_from => 'GSPW' -- added for bug 9328526
1467 );
1468 exception
1469 when others then
1470 hr_utility.set_location('issues in updating point'||p_spinal_point,30);
1471 raise;
1472 end update_point;
1473 procedure create_point(p_point_id out nocopy number,
1474 p_point_ovn out nocopy number,
1475 p_information_category in varchar2,
1476 p_information1 in varchar2,
1477 p_information2 in varchar2,
1478 p_information3 in varchar2,
1479 p_information4 in varchar2,
1480 p_information5 in varchar2,
1481 p_effective_date in date,
1482 p_business_group_id in number,
1483 p_parent_spine_id in number,
1484 p_sequence in number,
1485 p_spinal_point in varchar2) is
1486 begin
1487 hr_progression_point_api.create_progression_point
1488 (p_effective_date => p_effective_date
1489 ,p_business_group_id => p_business_group_id
1490 ,p_parent_spine_id => p_parent_spine_id
1491 ,p_sequence => p_sequence
1492 ,p_spinal_point => p_spinal_point
1493 ,p_spinal_point_id => p_point_id
1494 ,p_object_version_number => p_point_ovn
1495 ,p_information_category => p_information_category
1496 ,p_information1 => p_information1
1497 ,p_information2 => p_information2
1498 ,p_information3 => p_information3
1499 ,p_information4 => p_information4
1500 ,p_information5 => p_information5
1501 );
1502 exception
1503 when others then
1504 hr_utility.set_location('issues in creating point'||p_spinal_point,30);
1505 raise;
1506 end create_point;
1507 procedure create_scale(p_scale_id out nocopy number,
1508 p_scale_ovn out nocopy number,
1509 p_information_category in varchar2,
1510 p_information1 in varchar2,
1511 p_information2 in varchar2,
1512 p_business_group_id in number,
1513 p_name in varchar2,
1514 p_effective_date in date,
1515 p_increment_frequency in number,
1516 p_increment_period in varchar2) is
1517 begin
1518 hr_utility.set_location('inf_cat is'||p_information_category,10);
1519 hr_utility.set_location('inf1 is'||p_information1,10);
1520 hr_utility.set_location('inf2 is'||p_information2,10);
1521 hr_pay_scale_api.create_pay_scale
1522 (p_business_group_id => p_business_group_id
1523 ,p_name => p_name
1524 ,p_effective_date => p_effective_date
1525 ,p_increment_frequency => p_increment_frequency
1526 ,p_increment_period => p_increment_period
1527 ,p_parent_spine_id => p_scale_id
1528 ,p_object_version_number => p_scale_ovn
1529 ,p_information_category => p_information_category
1530 ,p_information1 => p_information1
1531 ,p_information2 => p_information2
1532 ) ;
1533 exception
1534 when others then
1535 hr_utility.set_location('issues in creating scale'||p_name,30);
1536 raise;
1537 end create_scale;
1538 procedure update_scale(p_scale_id in number,
1539 p_scale_ovn in out nocopy number,
1540 p_information_category in varchar2,
1541 p_information1 in varchar2,
1542 p_information2 in varchar2,
1543 p_business_group_id in number,
1544 p_name in varchar2,
1545 p_effective_date in date,
1546 p_increment_frequency in number,
1547 p_increment_period in varchar2) is
1548 begin
1549 hr_pay_scale_api.update_pay_scale
1550 (p_business_group_id => p_business_group_id
1551 ,p_name => p_name
1552 ,p_effective_date => p_effective_date
1553 ,p_increment_frequency => p_increment_frequency
1554 ,p_increment_period => p_increment_period
1555 ,p_parent_spine_id => p_scale_id
1556 ,p_object_version_number => p_scale_ovn
1557 ,p_information_category => p_information_category
1558 ,p_information1 => p_information1
1559 ,p_information2 => p_information2
1560 ,p_called_from => 'GSPW' -- added for bug 9328526
1561 ) ;
1562 exception
1563 when others then
1564 hr_utility.set_location('issues in updating scale'||p_name,30);
1565 raise;
1566 end update_scale;
1567 end pqh_cpd_hr_to_stage;