[Home] [Help]
26: if l_point_name is not null then
27: l_opt_name := substr(l_scl_name,1,30) ||':'|| l_point_name;
28: end if;
29: else
30: hr_utility.set_location('invalid scale id '||l_scl_id,3);
31: l_opt_name := nvl(l_opt_name,l_point_name);
32: end if;
33: exception
34: when no_data_found then
38: end;
39: return l_opt_name;
40: exception
41: when others then
42: hr_utility.set_location('issues in building opt name',11);
43: raise;
44: end build_opt_name;
45: procedure elp_writeback(p_crset_id in number,
46: p_elp_id in number,
45: procedure elp_writeback(p_crset_id in number,
46: p_elp_id in number,
47: p_copy_entity_txn_id in number) is
48: begin
49: hr_utility.set_location('crset id is '||p_crset_id,20);
50: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,20);
51: hr_utility.set_location('elp id is '||p_elp_id,20);
52: update ben_copy_entity_results
53: set information279 = p_elp_id
46: p_elp_id in number,
47: p_copy_entity_txn_id in number) is
48: begin
49: hr_utility.set_location('crset id is '||p_crset_id,20);
50: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,20);
51: hr_utility.set_location('elp id is '||p_elp_id,20);
52: update ben_copy_entity_results
53: set information279 = p_elp_id
54: where copy_entity_txn_id = p_copy_entity_txn_id
47: p_copy_entity_txn_id in number) is
48: begin
49: hr_utility.set_location('crset id is '||p_crset_id,20);
50: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,20);
51: hr_utility.set_location('elp id is '||p_elp_id,20);
52: update ben_copy_entity_results
53: set information279 = p_elp_id
54: where copy_entity_txn_id = p_copy_entity_txn_id
55: and table_alias = 'CRRATE'
53: set information279 = p_elp_id
54: where copy_entity_txn_id = p_copy_entity_txn_id
55: and table_alias = 'CRRATE'
56: and information160 = p_crset_id;
57: hr_utility.set_location('num of crrs updated'||sql%rowcount,20);
58: end elp_writeback;
59: procedure end_date_crit(p_elig_prfl_id in number,
60: p_crit_type in varchar2,
61: p_effective_date in date) is
63: l_ovn number;
64: l_esd date;
65: l_eed date;
66: begin
67: hr_utility.set_location('crit passed is'||p_crit_type,100);
68: if p_crit_type ='RL' then
69: select eligy_prfl_rl_id,object_version_number
70: into l_pk,l_ovn
71: from BEN_ELIGY_PRFL_RL_F
70: into l_pk,l_ovn
71: from BEN_ELIGY_PRFL_RL_F
72: where eligy_prfl_id = p_elig_prfl_id
73: and p_effective_date between effective_start_date and effective_end_date;
74: hr_utility.set_location('pk selected is'||l_pk,100);
75:
76: BEN_ELIGY_PROFILE_RULE_API.delete_ELIGY_PROFILE_RULE(
77: p_eligy_prfl_rl_id => l_pk
78: ,p_effective_start_date => l_esd
86: into l_pk,l_ovn
87: from BEN_ELIG_PERF_RTNG_PRTE_F
88: where eligy_prfl_id = p_elig_prfl_id
89: and p_effective_date between effective_start_date and effective_end_date;
90: hr_utility.set_location('pk selected is'||l_pk,100);
91:
92: BEN_ELIG_PERF_RTNG_PRTE_API.delete_ELIG_PERF_RTNG_PRTE(
93: p_elig_perf_rtng_prte_id => l_pk
94: ,p_effective_start_date => l_esd
102: into l_pk,l_ovn
103: from BEN_ELIG_PER_TYP_PRTE_F
104: where eligy_prfl_id = p_elig_prfl_id
105: and p_effective_date between effective_start_date and effective_end_date;
106: hr_utility.set_location('pk selected is'||l_pk,100);
107:
108: BEN_ELIG_PER_TYP_PRTE_API.delete_ELIG_PER_TYP_PRTE(
109: p_elig_per_typ_prte_id => l_pk
110: ,p_effective_start_date => l_esd
118: into l_pk,l_ovn
119: from BEN_ELIG_FL_TM_PT_TM_PRTE_F
120: where eligy_prfl_id = p_elig_prfl_id
121: and p_effective_date between effective_start_date and effective_end_date;
122: hr_utility.set_location('pk selected is'||l_pk,100);
123:
124: BEN_ELIG_FL_TM_PT_TM_PRTE_API.delete_ELIG_FL_TM_PT_TM_PRTE(
125: p_elig_fl_tm_pt_tm_prte_id => l_pk
126: ,p_effective_start_date => l_esd
134: into l_pk,l_ovn
135: from BEN_ELIG_BRGNG_UNIT_PRTE_F
136: where eligy_prfl_id = p_elig_prfl_id
137: and p_effective_date between effective_start_date and effective_end_date;
138: hr_utility.set_location('pk selected is'||l_pk,100);
139:
140: BEN_ELIG_BRGNG_UNIT_PRTE_API.delete_ELIG_BRGNG_UNIT_PRTE(
141: p_elig_brgng_unit_prte_id => l_pk
142: ,p_effective_start_date => l_esd
150: into l_pk,l_ovn
151: from BEN_ELIG_SVC_AREA_PRTE_F
152: where eligy_prfl_id = p_elig_prfl_id
153: and p_effective_date between effective_start_date and effective_end_date;
154: hr_utility.set_location('pk selected is'||l_pk,100);
155:
156: BEN_ELIG_SVC_AREA_PRTE_API.delete_ELIG_SVC_AREA_PRTE(
157: p_elig_svc_area_prte_id => l_pk
158: ,p_effective_start_date => l_esd
166: into l_pk,l_ovn
167: from BEN_ELIG_WK_LOC_PRTE_F
168: where eligy_prfl_id = p_elig_prfl_id
169: and p_effective_date between effective_start_date and effective_end_date;
170: hr_utility.set_location('pk selected is'||l_pk,100);
171:
172: BEN_ELIG_WK_LOC_PRTE_API.delete_ELIG_WK_LOC_PRTE(
173: p_elig_wk_loc_prte_id => l_pk
174: ,p_effective_start_date => l_esd
182: into l_pk,l_ovn
183: from BEN_ELIG_ORG_UNIT_PRTE_F
184: where eligy_prfl_id = p_elig_prfl_id
185: and p_effective_date between effective_start_date and effective_end_date;
186: hr_utility.set_location('pk selected is'||l_pk,100);
187:
188: BEN_ELIG_ORG_UNIT_PRTE_API.delete_ELIG_ORG_UNIT_PRTE(
189: p_elig_org_unit_prte_id => l_pk
190: ,p_effective_start_date => l_esd
198: into l_pk,l_ovn
199: from BEN_ELIG_JOB_PRTE_F
200: where eligy_prfl_id = p_elig_prfl_id
201: and p_effective_date between effective_start_date and effective_end_date;
202: hr_utility.set_location('pk selected is'||l_pk,100);
203:
204: BEN_ELIGY_JOB_PRTE_API.delete_ELIGY_JOB_PRTE(
205: p_elig_job_prte_id => l_pk
206: ,p_effective_start_date => l_esd
209: ,p_effective_date => p_effective_date
210: ,p_datetrack_mode => hr_api.g_delete
211: );
212: else
213: hr_utility.set_location('invalid crit passed',100);
214: end if;
215: end end_date_crit;
216: function get_per_typ_cd(P_PERSON_TYPE_ID in number) return varchar2 is
217: l_per_typ_cd varchar2(30);
242: open csr_crset;
243: fetch csr_crset into l_crset_name;
244: if csr_crset%notfound then
245: close csr_crset;
246: hr_utility.set_location('criteria set doesnot exist',10);
247: else
248: close csr_crset;
249: end if;
250: else
247: else
248: close csr_crset;
249: end if;
250: else
251: hr_utility.set_location('crset passed is null',11);
252: return l_vpf_name;
253: end if;
254: if p_grade_cer_id is not null then
255: hr_utility.set_location('grd short name to be pulled',11);
251: hr_utility.set_location('crset passed is null',11);
252: return l_vpf_name;
253: end if;
254: if p_grade_cer_id is not null then
255: hr_utility.set_location('grd short name to be pulled',11);
256: begin
257: select substr(information102,1,30)
258: into l_grd_name
259: from ben_copy_entity_results
259: from ben_copy_entity_results
260: where copy_entity_result_id = p_grade_cer_id;
261: exception
262: when others then
263: hr_utility.set_location('issue in selecting grd_name',11);
264: raise;
265: end;
266: l_vpf_name := l_grd_name ||'-'||l_crset_name;
267: return l_vpf_name;
265: end;
266: l_vpf_name := l_grd_name ||'-'||l_crset_name;
267: return l_vpf_name;
268: elsif p_point_cer_id is not null then
269: hr_utility.set_location('opt name to be pulled',11);
270: l_opt_name := build_opt_name(p_opt_cer_id => p_point_cer_id);
271: l_vpf_name := l_opt_name ||'-'||l_crset_name;
272: return l_vpf_name;
273: else
270: l_opt_name := build_opt_name(p_opt_cer_id => p_point_cer_id);
271: l_vpf_name := l_opt_name ||'-'||l_crset_name;
272: return l_vpf_name;
273: else
274: hr_utility.set_location('grd and pnt cer null',11);
275: return l_vpf_name;
276: end if;
277: end build_vpf_name;
278: function get_ovn(p_table_name in varchar2,
285: query_str := 'select object_version_number from '
286: || p_table_name
287: || ' where '
288: || p_key_column_name || '= :key_column_value' ;
289: hr_utility.set_location('query is '||substr(query_str,1,50),10);
290: hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
291: if p_effective_date is not null then
292: query_str := query_str || ' and :effective_date'
293: || ' between effective_start_date and effective_end_date';
286: || p_table_name
287: || ' where '
288: || p_key_column_name || '= :key_column_value' ;
289: hr_utility.set_location('query is '||substr(query_str,1,50),10);
290: hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
291: if p_effective_date is not null then
292: query_str := query_str || ' and :effective_date'
293: || ' between effective_start_date and effective_end_date';
294: hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
290: hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
291: if p_effective_date is not null then
292: query_str := query_str || ' and :effective_date'
293: || ' between effective_start_date and effective_end_date';
294: hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
295: EXECUTE IMMEDIATE query_str
296: INTO l_ovn
297: USING p_key_column_value, p_effective_date;
298: else
336: p_pl_id in number,
337: p_opt_id in number) return number is
338: l_oipl_id number;
339: begin
340: hr_utility.set_location('opt id is '||p_opt_id,1);
341: hr_utility.set_location('pl id is '||p_pl_id,2);
342: select oipl_id
343: into l_oipl_id
344: from ben_oipl_f
337: p_opt_id in number) return number is
338: l_oipl_id number;
339: begin
340: hr_utility.set_location('opt id is '||p_opt_id,1);
341: hr_utility.set_location('pl id is '||p_pl_id,2);
342: select oipl_id
343: into l_oipl_id
344: from ben_oipl_f
345: where pl_id = p_pl_id
344: from ben_oipl_f
345: where pl_id = p_pl_id
346: and opt_id = p_opt_id
347: and p_effective_date between effective_start_date and effective_end_date;
348: hr_utility.set_location('oipl is '||l_oipl_id,3);
349: return l_oipl_id;
350: exception
351: when no_data_found then
352: hr_utility.set_location('oipl doesnot exist ',3);
348: hr_utility.set_location('oipl is '||l_oipl_id,3);
349: return l_oipl_id;
350: exception
351: when no_data_found then
352: hr_utility.set_location('oipl doesnot exist ',3);
353: return l_oipl_id;
354: when others then
355: hr_utility.set_location('issues in getting oipl ',4);
356: raise;
351: when no_data_found then
352: hr_utility.set_location('oipl doesnot exist ',3);
353: return l_oipl_id;
354: when others then
355: hr_utility.set_location('issues in getting oipl ',4);
356: raise;
357: end is_oipl_exists;
358: function get_gsp_pt(p_business_group_id in number,
359: p_effective_date in date) return number is
369: open c1;
370: fetch c1 into l_pt_id;
371: if c1%notfound then
372: close c1;
373: hr_utility.set_location('pl_typ not defined ',4);
374: else
375: close c1;
376: end if;
377: return l_pt_id;
380: p_plip_id in number,
381: p_pl_id in number,
382: p_plip_cer_id in number) is
383: begin
384: hr_utility.set_location('plip id is '||p_plip_id,1);
385: hr_utility.set_location('pl id is '||p_pl_id,1);
386: hr_utility.set_location('plip cer id is '||p_plip_cer_id,2);
387: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388: -- update plip row with plip id
381: p_pl_id in number,
382: p_plip_cer_id in number) is
383: begin
384: hr_utility.set_location('plip id is '||p_plip_id,1);
385: hr_utility.set_location('pl id is '||p_pl_id,1);
386: hr_utility.set_location('plip cer id is '||p_plip_cer_id,2);
387: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388: -- update plip row with plip id
389: begin
382: p_plip_cer_id in number) is
383: begin
384: hr_utility.set_location('plip id is '||p_plip_id,1);
385: hr_utility.set_location('pl id is '||p_pl_id,1);
386: hr_utility.set_location('plip cer id is '||p_plip_cer_id,2);
387: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388: -- update plip row with plip id
389: begin
390: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
383: begin
384: hr_utility.set_location('plip id is '||p_plip_id,1);
385: hr_utility.set_location('pl id is '||p_pl_id,1);
386: hr_utility.set_location('plip cer id is '||p_plip_cer_id,2);
387: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388: -- update plip row with plip id
389: begin
390: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
391: update ben_copy_entity_results
386: hr_utility.set_location('plip cer id is '||p_plip_cer_id,2);
387: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388: -- update plip row with plip id
389: begin
390: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
391: update ben_copy_entity_results
392: set information1 = p_plip_id
393: where copy_entity_result_id = p_plip_cer_id;
394: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
390: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
391: update ben_copy_entity_results
392: set information1 = p_plip_id
393: where copy_entity_result_id = p_plip_cer_id;
394: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
395: exception
396: when others then
397: hr_utility.set_location('issues in updating plip ',10);
398: raise;
393: where copy_entity_result_id = p_plip_cer_id;
394: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
395: exception
396: when others then
397: hr_utility.set_location('issues in updating plip ',10);
398: raise;
399: end;
400: -- update oipl rows with pl id
401: begin
398: raise;
399: end;
400: -- update oipl rows with pl id
401: begin
402: hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
403: update ben_copy_entity_results
404: set information261 = p_pl_id
405: where gs_parent_entity_result_id = p_plip_cer_id
406: and table_alias ='COP'
404: set information261 = p_pl_id
405: where gs_parent_entity_result_id = p_plip_cer_id
406: and table_alias ='COP'
407: and copy_entity_txn_id = p_copy_entity_txn_id;
408: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
409: exception
410: when others then
411: hr_utility.set_location('issues in updating plip ',10);
412: raise;
407: and copy_entity_txn_id = p_copy_entity_txn_id;
408: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
409: exception
410: when others then
411: hr_utility.set_location('issues in updating plip ',10);
412: raise;
413: end;
414: -- update epa rows with plip id
415: begin
412: raise;
413: end;
414: -- update epa rows with plip id
415: begin
416: hr_utility.set_location('updating epa for plip:'||p_plip_id,4);
417: update ben_copy_entity_results
418: set information1 = p_plip_id
419: where copy_entity_result_id = p_plip_cer_id;
420: hr_utility.set_location('num of epas updated'||sql%rowcount,20);
416: hr_utility.set_location('updating epa for plip:'||p_plip_id,4);
417: update ben_copy_entity_results
418: set information1 = p_plip_id
419: where copy_entity_result_id = p_plip_cer_id;
420: hr_utility.set_location('num of epas updated'||sql%rowcount,20);
421: exception
422: when others then
423: hr_utility.set_location('issues in updating plip ',10);
424: raise;
419: where copy_entity_result_id = p_plip_cer_id;
420: hr_utility.set_location('num of epas updated'||sql%rowcount,20);
421: exception
422: when others then
423: hr_utility.set_location('issues in updating plip ',10);
424: raise;
425: end;
426: hr_utility.set_location('plip writeback comp ',5);
427: end plip_writeback;
422: when others then
423: hr_utility.set_location('issues in updating plip ',10);
424: raise;
425: end;
426: hr_utility.set_location('plip writeback comp ',5);
427: end plip_writeback;
428: procedure oipl_writeback(p_copy_entity_txn_id in number,
429: p_oipl_id in number,
430: p_oipl_cer_id in number) is
428: procedure oipl_writeback(p_copy_entity_txn_id in number,
429: p_oipl_id in number,
430: p_oipl_cer_id in number) is
431: begin
432: hr_utility.set_location('oipl id is '||p_oipl_id,1);
433: hr_utility.set_location('oipl cer id is '||p_oipl_cer_id,2);
434: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
435: begin
436: hr_utility.set_location('updating oipl:'||p_oipl_id,4);
429: p_oipl_id in number,
430: p_oipl_cer_id in number) is
431: begin
432: hr_utility.set_location('oipl id is '||p_oipl_id,1);
433: hr_utility.set_location('oipl cer id is '||p_oipl_cer_id,2);
434: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
435: begin
436: hr_utility.set_location('updating oipl:'||p_oipl_id,4);
437: update ben_copy_entity_results
430: p_oipl_cer_id in number) is
431: begin
432: hr_utility.set_location('oipl id is '||p_oipl_id,1);
433: hr_utility.set_location('oipl cer id is '||p_oipl_cer_id,2);
434: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
435: begin
436: hr_utility.set_location('updating oipl:'||p_oipl_id,4);
437: update ben_copy_entity_results
438: set information1 = p_oipl_id
432: hr_utility.set_location('oipl id is '||p_oipl_id,1);
433: hr_utility.set_location('oipl cer id is '||p_oipl_cer_id,2);
434: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
435: begin
436: hr_utility.set_location('updating oipl:'||p_oipl_id,4);
437: update ben_copy_entity_results
438: set information1 = p_oipl_id
439: where copy_entity_result_id = p_oipl_cer_id;
440: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
436: hr_utility.set_location('updating oipl:'||p_oipl_id,4);
437: update ben_copy_entity_results
438: set information1 = p_oipl_id
439: where copy_entity_result_id = p_oipl_cer_id;
440: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
441: exception
442: when others then
443: hr_utility.set_location('issues in updating oipl ',10);
444: raise;
439: where copy_entity_result_id = p_oipl_cer_id;
440: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
441: exception
442: when others then
443: hr_utility.set_location('issues in updating oipl ',10);
444: raise;
445: end;
446: hr_utility.set_location('oipl writeback comp ',5);
447: end oipl_writeback;
442: when others then
443: hr_utility.set_location('issues in updating oipl ',10);
444: raise;
445: end;
446: hr_utility.set_location('oipl writeback comp ',5);
447: end oipl_writeback;
448: procedure opt_writeback(p_copy_entity_txn_id in number,
449: p_opt_id in number,
450: p_opt_name in varchar2,
449: p_opt_id in number,
450: p_opt_name in varchar2,
451: p_opt_cer_id in number) is
452: begin
453: hr_utility.set_location('opt id is '||p_opt_id,1);
454: hr_utility.set_location('opt cer id is '||p_opt_cer_id,2);
455: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
456: -- update oipl rows with opt id
457: begin
450: p_opt_name in varchar2,
451: p_opt_cer_id in number) is
452: begin
453: hr_utility.set_location('opt id is '||p_opt_id,1);
454: hr_utility.set_location('opt cer id is '||p_opt_cer_id,2);
455: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
456: -- update oipl rows with opt id
457: begin
458: -- oipl row is updated with opt id
451: p_opt_cer_id in number) is
452: begin
453: hr_utility.set_location('opt id is '||p_opt_id,1);
454: hr_utility.set_location('opt cer id is '||p_opt_cer_id,2);
455: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
456: -- update oipl rows with opt id
457: begin
458: -- oipl row is updated with opt id
459: hr_utility.set_location('updating oipl for opt :'||p_opt_id,4);
455: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
456: -- update oipl rows with opt id
457: begin
458: -- oipl row is updated with opt id
459: hr_utility.set_location('updating oipl for opt :'||p_opt_id,4);
460: update ben_copy_entity_results
461: set information247 = p_opt_id
462: where copy_entity_txn_id = p_copy_entity_txn_id
463: and table_alias = 'COP'
461: set information247 = p_opt_id
462: where copy_entity_txn_id = p_copy_entity_txn_id
463: and table_alias = 'COP'
464: and information262 = p_opt_cer_id;
465: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
466: exception
467: when others then
468: hr_utility.set_location('issues in updating opt to oipl',10);
469: raise;
464: and information262 = p_opt_cer_id;
465: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
466: exception
467: when others then
468: hr_utility.set_location('issues in updating opt to oipl',10);
469: raise;
470: end;
471: -- update abr rows with opt id
472: begin
469: raise;
470: end;
471: -- update abr rows with opt id
472: begin
473: hr_utility.set_location('updating abr for opt:'||p_opt_id,4);
474: update ben_copy_entity_results
475: set information247 = p_opt_id,
476: information170 = p_opt_name
477: where copy_entity_txn_id = p_copy_entity_txn_id
476: information170 = p_opt_name
477: where copy_entity_txn_id = p_copy_entity_txn_id
478: and table_alias = 'ABR'
479: and information278 = p_opt_cer_id;
480: hr_utility.set_location('num of opts updated'||sql%rowcount,20);
481: exception
482: when others then
483: hr_utility.set_location('issues in updating opt to abr',10);
484: raise;
479: and information278 = p_opt_cer_id;
480: hr_utility.set_location('num of opts updated'||sql%rowcount,20);
481: exception
482: when others then
483: hr_utility.set_location('issues in updating opt to abr',10);
484: raise;
485: end;
486: hr_utility.set_location('opt writeback comp ',5);
487: end opt_writeback;
482: when others then
483: hr_utility.set_location('issues in updating opt to abr',10);
484: raise;
485: end;
486: hr_utility.set_location('opt writeback comp ',5);
487: end opt_writeback;
488: procedure pln_writeback(p_copy_entity_txn_id in number,
489: p_pl_id in number,
490: p_pl_name in varchar2,
490: p_pl_name in varchar2,
491: p_pl_cer_id in number,
492: p_plip_cer_id in number) is
493: begin
494: hr_utility.set_location('pln id is '||p_pl_id,1);
495: hr_utility.set_location('pln cer id is '||p_pl_cer_id,2);
496: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
497: -- update plip rows with plan id
498: begin
491: p_pl_cer_id in number,
492: p_plip_cer_id in number) is
493: begin
494: hr_utility.set_location('pln id is '||p_pl_id,1);
495: hr_utility.set_location('pln cer id is '||p_pl_cer_id,2);
496: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
497: -- update plip rows with plan id
498: begin
499: -- plip row is updated with pl id
492: p_plip_cer_id in number) is
493: begin
494: hr_utility.set_location('pln id is '||p_pl_id,1);
495: hr_utility.set_location('pln cer id is '||p_pl_cer_id,2);
496: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
497: -- update plip rows with plan id
498: begin
499: -- plip row is updated with pl id
500: hr_utility.set_location('updating plips for pl :'||p_pl_id,4);
496: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
497: -- update plip rows with plan id
498: begin
499: -- plip row is updated with pl id
500: hr_utility.set_location('updating plips for pl :'||p_pl_id,4);
501: update ben_copy_entity_results
502: set information261 = p_pl_id
503: where copy_entity_txn_id = p_copy_entity_txn_id
504: and table_alias = 'CPP'
502: set information261 = p_pl_id
503: where copy_entity_txn_id = p_copy_entity_txn_id
504: and table_alias = 'CPP'
505: and copy_entity_result_id = p_plip_cer_id;
506: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
507: exception
508: when others then
509: hr_utility.set_location('issues in updating pl to plip',10);
510: raise;
505: and copy_entity_result_id = p_plip_cer_id;
506: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
507: exception
508: when others then
509: hr_utility.set_location('issues in updating pl to plip',10);
510: raise;
511: end;
512: -- update abr rows with pl id
513: begin
510: raise;
511: end;
512: -- update abr rows with pl id
513: begin
514: hr_utility.set_location('updating abr for pl:'||p_pl_id,4);
515: update ben_copy_entity_results
516: set information261 = p_pl_id,
517: information170 = p_pl_name
518: where copy_entity_txn_id = p_copy_entity_txn_id
517: information170 = p_pl_name
518: where copy_entity_txn_id = p_copy_entity_txn_id
519: and table_alias = 'ABR'
520: and information277 = p_pl_cer_id;
521: hr_utility.set_location('num of abrs updated'||sql%rowcount,20);
522: exception
523: when others then
524: hr_utility.set_location('issues in updating pl to abr',10);
525: raise;
520: and information277 = p_pl_cer_id;
521: hr_utility.set_location('num of abrs updated'||sql%rowcount,20);
522: exception
523: when others then
524: hr_utility.set_location('issues in updating pl to abr',10);
525: raise;
526: end;
527: -- update oipl rows with plan id
528: begin
526: end;
527: -- update oipl rows with plan id
528: begin
529: -- oipl row is updated with pl id
530: hr_utility.set_location('updating oipls for pl :'||p_pl_id,4);
531: update ben_copy_entity_results
532: set information261 = p_pl_id
533: where copy_entity_txn_id = p_copy_entity_txn_id
534: and table_alias = 'COP'
532: set information261 = p_pl_id
533: where copy_entity_txn_id = p_copy_entity_txn_id
534: and table_alias = 'COP'
535: and gs_parent_entity_result_id = p_plip_cer_id;
536: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
537: exception
538: when others then
539: hr_utility.set_location('issues in updating pl to oipl',10);
540: raise;
535: and gs_parent_entity_result_id = p_plip_cer_id;
536: hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
537: exception
538: when others then
539: hr_utility.set_location('issues in updating pl to oipl',10);
540: raise;
541: end;
542: -- update pln row
543: begin
543: begin
544: update ben_copy_entity_results
545: set information1 = p_pl_id
546: where copy_entity_result_id = p_pl_cer_id;
547: hr_utility.set_location('num of plans updated'||sql%rowcount,20);
548: exception
549: when others then
550: hr_utility.set_location('issues in updating pln ',10);
551: raise;
546: where copy_entity_result_id = p_pl_cer_id;
547: hr_utility.set_location('num of plans updated'||sql%rowcount,20);
548: exception
549: when others then
550: hr_utility.set_location('issues in updating pln ',10);
551: raise;
552: end;
553: hr_utility.set_location('pln writeback comp ',5);
554: end pln_writeback;
549: when others then
550: hr_utility.set_location('issues in updating pln ',10);
551: raise;
552: end;
553: hr_utility.set_location('pln writeback comp ',5);
554: end pln_writeback;
555: procedure pgm_writeback(p_copy_entity_txn_id in number,
556: p_pgm_id in number,
557: p_pgm_cer_id in number,
556: p_pgm_id in number,
557: p_pgm_cer_id in number,
558: p_ptip_id in number) is
559: begin
560: hr_utility.set_location('pgm id is '||p_pgm_id,1);
561: hr_utility.set_location('pgm cer id is '||p_pgm_cer_id,2);
562: hr_utility.set_location('ptip id is '||p_ptip_id,3);
563: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
564: -- update plip rows with program id
557: p_pgm_cer_id in number,
558: p_ptip_id in number) is
559: begin
560: hr_utility.set_location('pgm id is '||p_pgm_id,1);
561: hr_utility.set_location('pgm cer id is '||p_pgm_cer_id,2);
562: hr_utility.set_location('ptip id is '||p_ptip_id,3);
563: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
564: -- update plip rows with program id
565: begin
558: p_ptip_id in number) is
559: begin
560: hr_utility.set_location('pgm id is '||p_pgm_id,1);
561: hr_utility.set_location('pgm cer id is '||p_pgm_cer_id,2);
562: hr_utility.set_location('ptip id is '||p_ptip_id,3);
563: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
564: -- update plip rows with program id
565: begin
566: -- plip row is updated with pgm id
559: begin
560: hr_utility.set_location('pgm id is '||p_pgm_id,1);
561: hr_utility.set_location('pgm cer id is '||p_pgm_cer_id,2);
562: hr_utility.set_location('ptip id is '||p_ptip_id,3);
563: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
564: -- update plip rows with program id
565: begin
566: -- plip row is updated with pgm id
567: hr_utility.set_location('updating plips for pgm :'||p_pgm_id,4);
563: hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
564: -- update plip rows with program id
565: begin
566: -- plip row is updated with pgm id
567: hr_utility.set_location('updating plips for pgm :'||p_pgm_id,4);
568: update ben_copy_entity_results
569: set information260 = p_pgm_id
570: where copy_entity_txn_id = p_copy_entity_txn_id
571: and table_alias = 'CPP';
568: update ben_copy_entity_results
569: set information260 = p_pgm_id
570: where copy_entity_txn_id = p_copy_entity_txn_id
571: and table_alias = 'CPP';
572: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
573: exception
574: when others then
575: hr_utility.set_location('issues in updating pgm to plip',10);
576: raise;
571: and table_alias = 'CPP';
572: hr_utility.set_location('num of plips updated'||sql%rowcount,20);
573: exception
574: when others then
575: hr_utility.set_location('issues in updating pgm to plip',10);
576: raise;
577: end;
578: -- update cpd rows with program id
579: begin
577: end;
578: -- update cpd rows with program id
579: begin
580: -- cpd row is updated with pgm id
581: hr_utility.set_location('updating cpd for pgm :'||p_pgm_id,4);
582: update ben_copy_entity_results
583: set information260 = p_pgm_id
584: where copy_entity_txn_id = p_copy_entity_txn_id
585: and table_alias = 'CPD';
582: update ben_copy_entity_results
583: set information260 = p_pgm_id
584: where copy_entity_txn_id = p_copy_entity_txn_id
585: and table_alias = 'CPD';
586: hr_utility.set_location('num of cpd updated'||sql%rowcount,20);
587: exception
588: when others then
589: hr_utility.set_location('issues in updating pgm to cpd',10);
590: raise;
585: and table_alias = 'CPD';
586: hr_utility.set_location('num of cpd updated'||sql%rowcount,20);
587: exception
588: when others then
589: hr_utility.set_location('issues in updating pgm to cpd',10);
590: raise;
591: end;
592: -- update pgm row for information1
593: begin
593: begin
594: update ben_copy_entity_results
595: set information1 = p_pgm_id
596: where copy_entity_result_id = p_pgm_cer_id;
597: hr_utility.set_location('num of pgms updated'||sql%rowcount,20);
598: exception
599: when others then
600: hr_utility.set_location('issues in updating pgm ',10);
601: raise;
596: where copy_entity_result_id = p_pgm_cer_id;
597: hr_utility.set_location('num of pgms updated'||sql%rowcount,20);
598: exception
599: when others then
600: hr_utility.set_location('issues in updating pgm ',10);
601: raise;
602: end;
603: hr_utility.set_location('pgm and ptip writeback comp ',5);
604: end pgm_writeback;
599: when others then
600: hr_utility.set_location('issues in updating pgm ',10);
601: raise;
602: end;
603: hr_utility.set_location('pgm and ptip writeback comp ',5);
604: end pgm_writeback;
605: function get_gsp_le(p_oper_code in varchar2,
606: p_bg_id in number,
607: p_effective_date in date) return number is
616: and p_effective_date between effective_start_date and effective_end_date;
617: return l_ler_id;
618: exception
619: when others then
620: hr_utility.set_location('issues in selecting ler',2);
621: raise;
622: end get_gsp_le;
623: procedure pgm_enrl(p_pgm_id in number,
624: p_bg_id in number,
628: l_esd date;
629: l_eed date;
630: l_pet_id number;
631: begin
632: hr_utility.set_location('creating pet for pgm'||p_pgm_id,1);
633: hr_utility.set_location(' BEN_POPL_ENRT_TYP_CYCL_F CREATE_POPL_ENRT_TYP_CYCL ',20);
634: BEN_POPL_ENRT_TYP_CYCL_API.CREATE_POPL_ENRT_TYP_CYCL(
635: P_EFFECTIVE_DATE => p_effective_date
636: ,P_BUSINESS_GROUP_ID => p_bg_id
629: l_eed date;
630: l_pet_id number;
631: begin
632: hr_utility.set_location('creating pet for pgm'||p_pgm_id,1);
633: hr_utility.set_location(' BEN_POPL_ENRT_TYP_CYCL_F CREATE_POPL_ENRT_TYP_CYCL ',20);
634: BEN_POPL_ENRT_TYP_CYCL_API.CREATE_POPL_ENRT_TYP_CYCL(
635: P_EFFECTIVE_DATE => p_effective_date
636: ,P_BUSINESS_GROUP_ID => p_bg_id
637: ,P_ENRT_TYP_CYCL_CD => 'L'
640: ,P_EFFECTIVE_START_DATE => l_esd
641: ,P_EFFECTIVE_END_DATE => l_eed
642: ,P_OBJECT_VERSION_NUMBER => l_ovn
643: );
644: hr_utility.set_location('After per insert ',2);
645: p_pet_id := l_pet_id;
646: end pgm_enrl;
647: procedure upd_pgm_le(p_pgm_id in number,
648: p_cet_id in number,
653: p_syn_rt_st_dt in varchar2) is
654: l_pet_id number;
655: l_ler_id number;
656: begin
657: hr_utility.set_location('updating le_enrl for pgm'||p_pgm_id,1);
658: begin
659: select popl_enrt_typ_cycl_id
660: into l_pet_id
661: from ben_popl_enrt_typ_cycl_f
660: into l_pet_id
661: from ben_popl_enrt_typ_cycl_f
662: where pgm_id = p_pgm_id
663: and p_effective_date between effective_start_date and effective_end_date;
664: hr_utility.set_location('pet is'||l_pet_id,2);
665: exception
666: when others then
667: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
668: (P_MASTER_TXN_ID => p_cet_id,
676: end;
677: l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
678: p_effective_date => p_effective_date,
679: p_oper_code => 'PROG');
680: hr_utility.set_location('prog le is'||l_ler_id,2);
681: if l_ler_id is not null then
682: begin
683: update ben_lee_rsn_f
684: set ENRT_CVG_STRT_DT_CD = p_pro_cvg_st_dt,
685: RT_STRT_DT_CD = p_pro_rt_st_dt
686: where POPL_ENRT_TYP_CYCL_ID = l_pet_id
687: and ler_id = l_ler_id
688: and p_effective_date between effective_start_date and effective_end_date;
689: hr_utility.set_location('prog le enrl updated ',4);
690: exception
691: when others then
692: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
693: (P_MASTER_TXN_ID => p_cet_id,
702: end if;
703: l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
704: p_effective_date => p_effective_date,
705: p_oper_code => 'SYNC');
706: hr_utility.set_location('sync le is'||l_ler_id,2);
707: if l_ler_id is not null then
708: begin
709: update ben_lee_rsn_f
710: set ENRT_CVG_STRT_DT_CD = p_syn_rt_st_dt,
739: l_eed date;
740: l_ler_id number;
741: l_lee_rsn_id number;
742: begin
743: hr_utility.set_location('creating pet for pgm'||p_pgm_id,1);
744: l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
745: p_effective_date => p_effective_date,
746: p_oper_code => 'PROG');
747: if l_ler_id is not null and p_pet_id is not null then
744: l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
745: p_effective_date => p_effective_date,
746: p_oper_code => 'PROG');
747: if l_ler_id is not null and p_pet_id is not null then
748: hr_utility.set_location(' BEN_LEE_RSN_F CREATE_LIFE_EVENT_ENROLL_RSN ',20);
749: BEN_LIFE_EVENT_ENROLL_RSN_API.CREATE_LIFE_EVENT_ENROLL_RSN(
750: P_EFFECTIVE_DATE => p_effective_date
751: ,P_BUSINESS_GROUP_ID => p_bg_id
752: ,P_LEE_RSN_ID => p_lee_rsn_id
767: l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
768: p_effective_date => p_effective_date,
769: p_oper_code => 'SYNC');
770: if l_ler_id is not null and p_pet_id is not null then
771: hr_utility.set_location(' BEN_LEE_RSN_F CREATE_LIFE_EVENT_ENROLL_RSN ',20);
772: BEN_LIFE_EVENT_ENROLL_RSN_API.CREATE_LIFE_EVENT_ENROLL_RSN(
773: P_EFFECTIVE_DATE => p_effective_date
774: ,P_BUSINESS_GROUP_ID => p_bg_id
775: ,P_LEE_RSN_ID => p_lee_rsn_id
797: l_esd date;
798: l_eed date;
799: l_ptip_id number;
800: begin
801: hr_utility.set_location('creating ptip for pgm'||p_pgm_id,1);
802: hr_utility.set_location('pl_typ'||p_pl_typ_id,2);
803: BEN_PLAN_TYPE_IN_PROGRAM_API.CREATE_PLAN_TYPE_IN_PROGRAM(
804: P_EFFECTIVE_DATE => p_effective_date
805: ,P_BUSINESS_GROUP_ID => p_bg_id
798: l_eed date;
799: l_ptip_id number;
800: begin
801: hr_utility.set_location('creating ptip for pgm'||p_pgm_id,1);
802: hr_utility.set_location('pl_typ'||p_pl_typ_id,2);
803: BEN_PLAN_TYPE_IN_PROGRAM_API.CREATE_PLAN_TYPE_IN_PROGRAM(
804: P_EFFECTIVE_DATE => p_effective_date
805: ,P_BUSINESS_GROUP_ID => p_bg_id
806: ,P_PGM_ID => p_PGM_ID
870: ,P_SHORT_NAME => r_CTP.INFORMATION13
871: */
872: );
873: p_ptip_id := l_ptip_id;
874: hr_utility.set_location('ptip is'||p_ptip_id,3);
875: end create_ptip;
876:
877: procedure stage_to_pgi(p_copy_entity_txn_id in number,
878: p_business_group_id in number,
892: l_pgm_id NUMBER;
893: l_pgm_esd DATE;
894: l_pgm_name VARCHAR2 (240);
895: begin
896: hr_utility.set_location('inside pgm_extra_info_update',10);
897:
898: BEGIN
899: SELECT information1, information5, information2
900: INTO l_pgm_id, l_pgm_name, l_pgm_esd
904: and result_type_cd='DISPLAY';
905: EXCEPTION
906: WHEN OTHERS
907: THEN
908: hr_utility.set_location ('issues in getting pgm name', 10);
909: RAISE;
910: END;
911:
912: open c_pgi;
915: exit when c_pgi%notfound;
916:
917:
918: if r_pgi.information174 is null then
919: hr_utility.set_location('insert pgm extra info ',10);
920: ben_pgm_extra_info_api.create_pgm_extra_info
921: ( p_pgm_id => l_pgm_id
922: ,p_information_type => 'PQH_GSP_EXTRA_INFO'
923: ,p_pgi_information_category => 'PQH_GSP_EXTRA_INFO'
928: ,p_pgm_extra_info_id => l_pgm_extra_info_id
929: ,p_object_version_number => l_peit_ovn
930: );
931: else
932: hr_utility.set_location('update pgm extra info',10);
933: l_peit_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PGM_EXTRA_INFO',
934: p_key_column_name => 'PGM_EXTRA_INFO_ID',
935: p_key_column_value => r_pgi.information174);
936: hr_utility.set_location(' ovn is '||l_peit_ovn,30);
932: hr_utility.set_location('update pgm extra info',10);
933: l_peit_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PGM_EXTRA_INFO',
934: p_key_column_name => 'PGM_EXTRA_INFO_ID',
935: p_key_column_value => r_pgi.information174);
936: hr_utility.set_location(' ovn is '||l_peit_ovn,30);
937: ben_pgm_extra_info_api.update_pgm_extra_info
938: ( p_pgm_extra_info_id => r_pgi.information174
939: ,p_object_version_number => l_peit_ovn
940: ,p_pgi_information1 => r_pgi.information11
944: );
945: end if;
946: end loop;
947: close c_pgi;
948: hr_utility.set_location('leaving pgm_extra_info_update',10);
949: exception
950: when others then
951: raise;
952: end stage_to_pgi;
972: 9) var
973: 10) elig prof linkage with objects
974: */
975: begin
976: hr_utility.set_location('inside '||l_proc,10);
977: l_pl_typ_id := get_gsp_pt(p_business_group_id => p_business_group_id,
978: p_effective_date => p_effective_date);
979: hr_utility.set_location('pl typ is '||l_pl_typ_id,1);
980: stage_to_elp(p_copy_entity_txn_id => p_copy_entity_txn_id,
975: begin
976: hr_utility.set_location('inside '||l_proc,10);
977: l_pl_typ_id := get_gsp_pt(p_business_group_id => p_business_group_id,
978: p_effective_date => p_effective_date);
979: hr_utility.set_location('pl typ is '||l_pl_typ_id,1);
980: stage_to_elp(p_copy_entity_txn_id => p_copy_entity_txn_id,
981: p_effective_date => l_effective_date,
982: p_business_group_id => p_business_group_id,
983: p_datetrack_mode => p_datetrack_mode);
980: stage_to_elp(p_copy_entity_txn_id => p_copy_entity_txn_id,
981: p_effective_date => l_effective_date,
982: p_business_group_id => p_business_group_id,
983: p_datetrack_mode => p_datetrack_mode);
984: hr_utility.set_location('elp row update',30);
985: stage_to_opt(p_copy_entity_txn_id => p_copy_entity_txn_id,
986: p_effective_date => l_effective_date,
987: p_business_group_id => p_business_group_id,
988: p_pl_typ_id => l_pl_typ_id,
986: p_effective_date => l_effective_date,
987: p_business_group_id => p_business_group_id,
988: p_pl_typ_id => l_pl_typ_id,
989: p_datetrack_mode => p_datetrack_mode);
990: hr_utility.set_location('opt row updated',40);
991: stage_to_plan(p_copy_entity_txn_id => p_copy_entity_txn_id,
992: p_effective_date => l_effective_date,
993: p_business_group_id => p_business_group_id,
994: p_pl_typ_id => l_pl_typ_id,
992: p_effective_date => l_effective_date,
993: p_business_group_id => p_business_group_id,
994: p_pl_typ_id => l_pl_typ_id,
995: p_datetrack_mode => p_datetrack_mode);
996: hr_utility.set_location('plan row updated',50);
997: stage_to_pgm(p_copy_entity_txn_id => p_copy_entity_txn_id,
998: p_effective_date => l_effective_date,
999: p_business_group_id => p_business_group_id,
1000: p_pl_typ_id => l_pl_typ_id,
998: p_effective_date => l_effective_date,
999: p_business_group_id => p_business_group_id,
1000: p_pl_typ_id => l_pl_typ_id,
1001: p_datetrack_mode => p_datetrack_mode);
1002: hr_utility.set_location('pgm row updated',60);
1003: stage_to_pgi(p_copy_entity_txn_id => p_copy_entity_txn_id,
1004: p_business_group_id => p_business_group_id,
1005: p_effective_date => l_effective_date
1006: ) ;
1003: stage_to_pgi(p_copy_entity_txn_id => p_copy_entity_txn_id,
1004: p_business_group_id => p_business_group_id,
1005: p_effective_date => l_effective_date
1006: ) ;
1007: hr_utility.set_location('pgi row updated',70);
1008: if p_business_area ='PQH_CORPS_TASK_LIST' then
1009: pqh_cpd_hr_to_stage.stage_to_corps(p_copy_entity_txn_id => p_copy_entity_txn_id,
1010: p_effective_date => l_effective_date,
1011: p_business_group_id => p_business_group_id,
1009: pqh_cpd_hr_to_stage.stage_to_corps(p_copy_entity_txn_id => p_copy_entity_txn_id,
1010: p_effective_date => l_effective_date,
1011: p_business_group_id => p_business_group_id,
1012: p_datetrack_mode => p_datetrack_mode);
1013: hr_utility.set_location('cpd row updated',60);
1014: end if;
1015: stage_to_oipl(p_copy_entity_txn_id => p_copy_entity_txn_id,
1016: p_effective_date => l_effective_date,
1017: p_business_group_id => p_business_group_id,
1015: stage_to_oipl(p_copy_entity_txn_id => p_copy_entity_txn_id,
1016: p_effective_date => l_effective_date,
1017: p_business_group_id => p_business_group_id,
1018: p_datetrack_mode => p_datetrack_mode);
1019: hr_utility.set_location('oipl row updated',70);
1020: stage_to_plip(p_copy_entity_txn_id => p_copy_entity_txn_id,
1021: p_effective_date => l_effective_date,
1022: p_business_group_id => p_business_group_id,
1023: p_datetrack_mode => p_datetrack_mode,
1021: p_effective_date => l_effective_date,
1022: p_business_group_id => p_business_group_id,
1023: p_datetrack_mode => p_datetrack_mode,
1024: p_business_area => p_business_area);
1025: hr_utility.set_location('plip row updated',70);
1026: stage_to_abr(p_copy_entity_txn_id => p_copy_entity_txn_id,
1027: p_effective_date => l_effective_date,
1028: p_business_group_id => p_business_group_id,
1029: p_datetrack_mode => p_datetrack_mode);
1026: stage_to_abr(p_copy_entity_txn_id => p_copy_entity_txn_id,
1027: p_effective_date => l_effective_date,
1028: p_business_group_id => p_business_group_id,
1029: p_datetrack_mode => p_datetrack_mode);
1030: hr_utility.set_location('abr row updated',70);
1031: stage_to_vpf(p_copy_entity_txn_id => p_copy_entity_txn_id,
1032: p_effective_date => l_effective_date,
1033: p_business_group_id => p_business_group_id,
1034: p_datetrack_mode => p_datetrack_mode);
1031: stage_to_vpf(p_copy_entity_txn_id => p_copy_entity_txn_id,
1032: p_effective_date => l_effective_date,
1033: p_business_group_id => p_business_group_id,
1034: p_datetrack_mode => p_datetrack_mode);
1035: hr_utility.set_location('var row updated',70);
1036: stage_to_epa(p_copy_entity_txn_id => p_copy_entity_txn_id,
1037: p_effective_date => l_effective_date,
1038: p_business_group_id => p_business_group_id,
1039: p_datetrack_mode => p_datetrack_mode);
1036: stage_to_epa(p_copy_entity_txn_id => p_copy_entity_txn_id,
1037: p_effective_date => l_effective_date,
1038: p_business_group_id => p_business_group_id,
1039: p_datetrack_mode => p_datetrack_mode);
1040: hr_utility.set_location('epa row updated',70);
1041: stage_to_cep(p_copy_entity_txn_id => p_copy_entity_txn_id,
1042: p_effective_date => l_effective_date,
1043: p_business_group_id => p_business_group_id,
1044: p_datetrack_mode => p_datetrack_mode);
1041: stage_to_cep(p_copy_entity_txn_id => p_copy_entity_txn_id,
1042: p_effective_date => l_effective_date,
1043: p_business_group_id => p_business_group_id,
1044: p_datetrack_mode => p_datetrack_mode);
1045: hr_utility.set_location('cep row updated',70);
1046: exception
1047: when others then
1048: hr_utility.set_location('error encountered',420);
1049: raise;
1044: p_datetrack_mode => p_datetrack_mode);
1045: hr_utility.set_location('cep row updated',70);
1046: exception
1047: when others then
1048: hr_utility.set_location('error encountered',420);
1049: raise;
1050: end stage_to_ben;
1051: procedure stage_to_opt(p_copy_entity_txn_id in number,
1052: p_business_group_id in number,
1073: l_effective_date date;
1074: l_message_text varchar2(2000);
1075: l_scl_name varchar2(30);
1076: begin
1077: hr_utility.set_location('inside '||l_proc,1);
1078: for r_opt in c_opt loop
1079: l_opt_id := r_OPT.information1;
1080: l_ovn := r_OPT.information265;
1081: hr_utility.set_location(' l_opt_id '||l_opt_id,2);
1077: hr_utility.set_location('inside '||l_proc,1);
1078: for r_opt in c_opt loop
1079: l_opt_id := r_OPT.information1;
1080: l_ovn := r_OPT.information265;
1081: hr_utility.set_location(' l_opt_id '||l_opt_id,2);
1082: hr_utility.set_location(' point id '||r_opt.information257,3);
1083: if r_opt.dml_operation in ('INSERT','COPIED','UPD_INS') then
1084: l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1085: else
1078: for r_opt in c_opt loop
1079: l_opt_id := r_OPT.information1;
1080: l_ovn := r_OPT.information265;
1081: hr_utility.set_location(' l_opt_id '||l_opt_id,2);
1082: hr_utility.set_location(' point id '||r_opt.information257,3);
1083: if r_opt.dml_operation in ('INSERT','COPIED','UPD_INS') then
1084: l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1085: else
1086: l_effective_date := r_OPT.information2;
1094: -- option may have been created thru step api. get the opt id
1095: l_opt_id := pqh_gsp_hr_to_stage.get_opt_for_point
1096: (p_point_id => r_opt.information257,
1097: p_effective_date => l_effective_date);
1098: hr_utility.set_location('opt found'||l_opt_id,5);
1099: if l_opt_id is null then
1100: hr_utility.set_location(' BEN_OPT_F CREATE_OPTION_DEFINITION ',4);
1101: BEN_OPTION_DEFINITION_API.CREATE_OPTION_DEFINITION(
1102: P_EFFECTIVE_DATE => l_effective_date
1096: (p_point_id => r_opt.information257,
1097: p_effective_date => l_effective_date);
1098: hr_utility.set_location('opt found'||l_opt_id,5);
1099: if l_opt_id is null then
1100: hr_utility.set_location(' BEN_OPT_F CREATE_OPTION_DEFINITION ',4);
1101: BEN_OPTION_DEFINITION_API.CREATE_OPTION_DEFINITION(
1102: P_EFFECTIVE_DATE => l_effective_date
1103: ,P_BUSINESS_GROUP_ID => p_business_group_id
1104: ,P_CMBN_PTIP_OPT_ID => r_OPT.INFORMATION249
1116: ,P_EFFECTIVE_START_DATE => l_effective_start_date
1117: ,P_EFFECTIVE_END_DATE => l_effective_end_date
1118: ,P_OBJECT_VERSION_NUMBER => l_ovn
1119: );
1120: hr_utility.set_location('opt created'||l_opt_id,5);
1121: ben_plan_type_option_type_api.create_plan_type_option_type
1122: (p_pl_typ_opt_typ_id => l_pk
1123: ,p_effective_start_date => l_effective_start_date
1124: ,p_effective_end_date => l_effective_end_date
1132: opt_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1133: p_opt_id => l_opt_id,
1134: p_opt_name => l_opt_name,
1135: p_opt_cer_id => r_OPT.copy_entity_result_id);
1136: hr_utility.set_location('opt wrtback comp',8);
1137: elsif l_opt_id is not null
1138: and r_opt.dml_operation = 'UPDATE'
1139: and r_opt.information257 is not null
1140: and l_opt_name is not null then
1137: elsif l_opt_id is not null
1138: and r_opt.dml_operation = 'UPDATE'
1139: and r_opt.information257 is not null
1140: and l_opt_name is not null then
1141: hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
1142: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1143: if p_datetrack_mode <> 'CORRECTION' then
1144: l_dt_mode := get_update_mode('BEN_OPT_F','OPT_ID', l_opt_id, l_effective_date) ;
1145: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1138: and r_opt.dml_operation = 'UPDATE'
1139: and r_opt.information257 is not null
1140: and l_opt_name is not null then
1141: hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
1142: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1143: if p_datetrack_mode <> 'CORRECTION' then
1144: l_dt_mode := get_update_mode('BEN_OPT_F','OPT_ID', l_opt_id, l_effective_date) ;
1145: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1146: else
1141: hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
1142: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1143: if p_datetrack_mode <> 'CORRECTION' then
1144: l_dt_mode := get_update_mode('BEN_OPT_F','OPT_ID', l_opt_id, l_effective_date) ;
1145: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1146: else
1147: l_dt_mode := p_datetrack_mode;
1148: end if;
1149: l_db_ovn := get_ovn(p_table_name => 'BEN_OPT_F',
1149: l_db_ovn := get_ovn(p_table_name => 'BEN_OPT_F',
1150: p_key_column_name => 'OPT_ID',
1151: p_key_column_value => l_opt_id,
1152: p_effective_date => l_effective_date);
1153: hr_utility.set_location(' ovn is '||l_db_ovn,30);
1154: if l_db_ovn <> l_ovn then
1155: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','OPT');
1156: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1157: fnd_message.set_token('OBJECT ',l_object);
1178: ,P_DATETRACK_MODE => l_dt_mode
1179: );
1180: end if;
1181: elsif r_opt.dml_operation in ('DELETE') then
1182: hr_utility.set_location('nothing needs to be done',100);
1183: else
1184: l_message_text := 'invalid dml_oper is '||r_opt.dml_operation
1185: ||' opt id is '||l_opt_id
1186: ||' opt name is '||l_opt_name
1195: P_MESSAGE_TEXT => l_message_text,
1196: p_effective_date => p_effective_date);
1197: end if;
1198: exception when others then
1199: hr_utility.set_location('issues in writing opt, skipping'||l_proc,100);
1200: raise;
1201: end;
1202: end loop;
1203: hr_utility.set_location('leaving '||l_proc,100);
1199: hr_utility.set_location('issues in writing opt, skipping'||l_proc,100);
1200: raise;
1201: end;
1202: end loop;
1203: hr_utility.set_location('leaving '||l_proc,100);
1204: exception
1205: when others then
1206: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1207: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1236: l_effective_date date;
1237: l_message_text varchar2(2000);
1238: l_dt_mode varchar2(30);
1239: begin
1240: hr_utility.set_location('inside '||l_proc,1);
1241: for r_pln in c_pln loop
1242: l_pl_id := r_pln.information1;
1243: l_ovn := r_pln.information265;
1244: hr_utility.set_location('for pln_id:'||l_pl_id ||'dml '||r_pln.dml_operation,2);
1240: hr_utility.set_location('inside '||l_proc,1);
1241: for r_pln in c_pln loop
1242: l_pl_id := r_pln.information1;
1243: l_ovn := r_pln.information265;
1244: hr_utility.set_location('for pln_id:'||l_pl_id ||'dml '||r_pln.dml_operation,2);
1245: if r_pln.dml_operation in ('INSERT','COPIED','UPD_INS') then
1246: l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1247: else
1248: l_effective_date := r_pln.information2;
1246: l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1247: else
1248: l_effective_date := r_pln.information2;
1249: end if;
1250: hr_utility.set_location('effdt is :'||to_char(l_effective_date,'DD/MM/RRRR'),3);
1251: hr_utility.set_location('pl typ id is :'||p_pl_typ_id,4);
1252: hr_utility.set_location('grd id is :'||r_pln.information294,5);
1253: l_pl_name := nvl(r_PLN.INFORMATION170,r_PLN.INFORMATION5);
1254: begin
1247: else
1248: l_effective_date := r_pln.information2;
1249: end if;
1250: hr_utility.set_location('effdt is :'||to_char(l_effective_date,'DD/MM/RRRR'),3);
1251: hr_utility.set_location('pl typ id is :'||p_pl_typ_id,4);
1252: hr_utility.set_location('grd id is :'||r_pln.information294,5);
1253: l_pl_name := nvl(r_PLN.INFORMATION170,r_PLN.INFORMATION5);
1254: begin
1255: if l_pl_id is null and r_pln.dml_operation in ('INSERT','COPIED','UPD_INS')
1248: l_effective_date := r_pln.information2;
1249: end if;
1250: hr_utility.set_location('effdt is :'||to_char(l_effective_date,'DD/MM/RRRR'),3);
1251: hr_utility.set_location('pl typ id is :'||p_pl_typ_id,4);
1252: hr_utility.set_location('grd id is :'||r_pln.information294,5);
1253: l_pl_name := nvl(r_PLN.INFORMATION170,r_PLN.INFORMATION5);
1254: begin
1255: if l_pl_id is null and r_pln.dml_operation in ('INSERT','COPIED','UPD_INS')
1256: and r_pln.information294 is not null then
1253: l_pl_name := nvl(r_PLN.INFORMATION170,r_PLN.INFORMATION5);
1254: begin
1255: if l_pl_id is null and r_pln.dml_operation in ('INSERT','COPIED','UPD_INS')
1256: and r_pln.information294 is not null then
1257: hr_utility.set_location(' BEN_PL_F CREATE_PLAN ',4);
1258: BEN_PLAN_API.CREATE_PLAN(
1259: P_EFFECTIVE_DATE => l_effective_date
1260: ,P_BUSINESS_GROUP_ID => p_business_group_id
1261: ,P_ACTL_PREM_ID => r_PLN.INFORMATION250
1399: ,P_EFFECTIVE_START_DATE => l_effective_start_date
1400: ,P_EFFECTIVE_END_DATE => l_effective_end_date
1401: ,P_OBJECT_VERSION_NUMBER => l_ovn
1402: );
1403: hr_utility.set_location('After pl insert'||l_pl_id,12);
1404: pln_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1405: p_pl_id => l_pl_id,
1406: p_pl_name => l_pl_name,
1407: p_pl_cer_id => r_PLN.copy_entity_result_id,
1405: p_pl_id => l_pl_id,
1406: p_pl_name => l_pl_name,
1407: p_pl_cer_id => r_PLN.copy_entity_result_id,
1408: p_plip_cer_id => r_PLN.gs_mirror_src_entity_result_id);
1409: hr_utility.set_location('pl wrtback comp ',15);
1410: elsif r_pln.dml_operation = 'UPDATE' and l_pl_id is not null
1411: and r_pln.information294 is not null then
1412: hr_utility.set_location(' BEN_PL_F UPDATE_PLAN ',30);
1413: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1408: p_plip_cer_id => r_PLN.gs_mirror_src_entity_result_id);
1409: hr_utility.set_location('pl wrtback comp ',15);
1410: elsif r_pln.dml_operation = 'UPDATE' and l_pl_id is not null
1411: and r_pln.information294 is not null then
1412: hr_utility.set_location(' BEN_PL_F UPDATE_PLAN ',30);
1413: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1414: if p_datetrack_mode <> 'CORRECTION' then
1415: l_dt_mode := get_update_mode(p_table_name => 'BEN_PL_F',
1416: p_key_column_name => 'PL_ID',
1409: hr_utility.set_location('pl wrtback comp ',15);
1410: elsif r_pln.dml_operation = 'UPDATE' and l_pl_id is not null
1411: and r_pln.information294 is not null then
1412: hr_utility.set_location(' BEN_PL_F UPDATE_PLAN ',30);
1413: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1414: if p_datetrack_mode <> 'CORRECTION' then
1415: l_dt_mode := get_update_mode(p_table_name => 'BEN_PL_F',
1416: p_key_column_name => 'PL_ID',
1417: p_key_column_value => l_pl_id,
1415: l_dt_mode := get_update_mode(p_table_name => 'BEN_PL_F',
1416: p_key_column_name => 'PL_ID',
1417: p_key_column_value => l_pl_id,
1418: p_effective_date => l_effective_date);
1419: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1420: else
1421: l_dt_mode := p_datetrack_mode;
1422: end if;
1423: l_db_ovn := get_ovn(p_table_name => 'BEN_PL_F',
1423: l_db_ovn := get_ovn(p_table_name => 'BEN_PL_F',
1424: p_key_column_name => 'PL_ID',
1425: p_key_column_value => l_pl_id,
1426: p_effective_date => l_effective_date);
1427: hr_utility.set_location(' ovn is '||l_db_ovn,30);
1428: if l_db_ovn <> l_ovn then
1429: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLN');
1430: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1431: fnd_message.set_token('OBJECT ',l_object);
1461: P_MESSAGE_TEXT => l_message_text,
1462: p_effective_date => p_effective_date);
1463: end if;
1464: exception when others then
1465: hr_utility.set_location('issues in writing pln'||l_proc,100);
1466: raise;
1467: end;
1468: end loop;
1469: hr_utility.set_location('leaving '||l_proc,100);
1465: hr_utility.set_location('issues in writing pln'||l_proc,100);
1466: raise;
1467: end;
1468: end loop;
1469: hr_utility.set_location('leaving '||l_proc,100);
1470: exception
1471: when others then
1472: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1473: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1508: l_sal_upd_cd varchar2(30);
1509: l_dflt_step_cd varchar2(30);
1510: l_dt_mode varchar2(30);
1511: begin
1512: hr_utility.set_location('inside '||l_proc,10);
1513: for r_pgm in c_pgm loop
1514: l_pgm_id := r_pgm.information1;
1515: l_ovn := r_pgm.information265;
1516: hr_utility.set_location('for pgm_id:'||l_pgm_id ||'dml '||r_pgm.dml_operation,20);
1512: hr_utility.set_location('inside '||l_proc,10);
1513: for r_pgm in c_pgm loop
1514: l_pgm_id := r_pgm.information1;
1515: l_ovn := r_pgm.information265;
1516: hr_utility.set_location('for pgm_id:'||l_pgm_id ||'dml '||r_pgm.dml_operation,20);
1517: l_effective_date := r_pgm.information2;
1518: l_pgm_cer_id := r_pgm.copy_entity_result_id;
1519: if r_pgm.information16 = 'N' then
1520: l_sal_upd_cd := 'NO_UPDATE' ;
1525: l_dflt_step_cd := r_pgm.information51;
1526: else
1527: l_dflt_step_cd := nvl(r_pgm.information14,'MINSTEP');
1528: end if;
1529: hr_utility.set_location('dflt_step_cd is'||l_dflt_step_cd,3);
1530: hr_utility.set_location('l_sal_upd_cd is'||l_sal_upd_cd,4);
1531: if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
1532: hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
1533: hr_utility.set_location('sal_upd '||l_sal_upd_cd,2);
1526: else
1527: l_dflt_step_cd := nvl(r_pgm.information14,'MINSTEP');
1528: end if;
1529: hr_utility.set_location('dflt_step_cd is'||l_dflt_step_cd,3);
1530: hr_utility.set_location('l_sal_upd_cd is'||l_sal_upd_cd,4);
1531: if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
1532: hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
1533: hr_utility.set_location('sal_upd '||l_sal_upd_cd,2);
1534: hr_utility.set_location(' BEN_PGM_F CREATE_PROGRAM ',20);
1528: end if;
1529: hr_utility.set_location('dflt_step_cd is'||l_dflt_step_cd,3);
1530: hr_utility.set_location('l_sal_upd_cd is'||l_sal_upd_cd,4);
1531: if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
1532: hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
1533: hr_utility.set_location('sal_upd '||l_sal_upd_cd,2);
1534: hr_utility.set_location(' BEN_PGM_F CREATE_PROGRAM ',20);
1535: begin
1536: BEN_PROGRAM_API.CREATE_PROGRAM(
1529: hr_utility.set_location('dflt_step_cd is'||l_dflt_step_cd,3);
1530: hr_utility.set_location('l_sal_upd_cd is'||l_sal_upd_cd,4);
1531: if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
1532: hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
1533: hr_utility.set_location('sal_upd '||l_sal_upd_cd,2);
1534: hr_utility.set_location(' BEN_PGM_F CREATE_PROGRAM ',20);
1535: begin
1536: BEN_PROGRAM_API.CREATE_PROGRAM(
1537: P_EFFECTIVE_DATE => l_effective_date
1530: hr_utility.set_location('l_sal_upd_cd is'||l_sal_upd_cd,4);
1531: if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
1532: hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
1533: hr_utility.set_location('sal_upd '||l_sal_upd_cd,2);
1534: hr_utility.set_location(' BEN_PGM_F CREATE_PROGRAM ',20);
1535: begin
1536: BEN_PROGRAM_API.CREATE_PROGRAM(
1537: P_EFFECTIVE_DATE => l_effective_date
1538: ,P_BUSINESS_GROUP_ID => p_business_group_id
1608: ,P_EFFECTIVE_END_DATE => l_effective_end_date
1609: ,P_OBJECT_VERSION_NUMBER => l_ovn
1610: );
1611: exception when others then
1612: hr_utility.set_location('issues in creating pgm, skipping',100);
1613: raise;
1614: end;
1615: hr_utility.set_location('After pgm insert '||l_pgm_id,1);
1616: create_ptip(p_pgm_id => l_pgm_id,
1611: exception when others then
1612: hr_utility.set_location('issues in creating pgm, skipping',100);
1613: raise;
1614: end;
1615: hr_utility.set_location('After pgm insert '||l_pgm_id,1);
1616: create_ptip(p_pgm_id => l_pgm_id,
1617: p_pl_typ_id => p_pl_typ_id,
1618: p_bg_id => p_business_group_id,
1619: p_effective_date => p_effective_date,
1617: p_pl_typ_id => p_pl_typ_id,
1618: p_bg_id => p_business_group_id,
1619: p_effective_date => p_effective_date,
1620: p_ptip_id => l_ptip_id);
1621: hr_utility.set_location('ptip id is '||l_ptip_id,2);
1622: pgm_enrl(p_pgm_id => l_pgm_id,
1623: p_bg_id => p_business_group_id,
1624: p_effective_date => p_effective_date,
1625: p_pet_id => l_pet_id);
1622: pgm_enrl(p_pgm_id => l_pgm_id,
1623: p_bg_id => p_business_group_id,
1624: p_effective_date => p_effective_date,
1625: p_pet_id => l_pet_id);
1626: hr_utility.set_location('pet id is '||l_pet_id,2);
1627: if l_pet_id is not null then
1628: pgm_le(p_pgm_id => l_pgm_id,
1629: p_bg_id => p_business_group_id,
1630: p_effective_date => p_effective_date,
1632: p_pro_cvg_st_dt => nvl(r_PGM.INFORMATION45,'AED'),
1633: p_pro_rt_st_dt => nvl(r_PGM.INFORMATION47,'AED'),
1634: p_syn_rt_st_dt => nvl(r_PGM.INFORMATION53,'AED'),
1635: p_lee_rsn_id => l_lee_rsn_id) ;
1636: hr_utility.set_location('lee_rsn id is '||l_lee_rsn_id,3);
1637: end if;
1638: pgm_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1639: p_pgm_id => l_pgm_id,
1640: p_pgm_cer_id => l_pgm_cer_id,
1638: pgm_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1639: p_pgm_id => l_pgm_id,
1640: p_pgm_cer_id => l_pgm_cer_id,
1641: p_ptip_id => l_ptip_id);
1642: hr_utility.set_location('pgm writeback comp ',3);
1643: --
1644: elsif r_pgm.dml_operation ='UPDATE' and l_pgm_id is not null then
1645: hr_utility.set_location(' BEN_PGM_F UPDATE_PROGRAM ',30);
1646: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1641: p_ptip_id => l_ptip_id);
1642: hr_utility.set_location('pgm writeback comp ',3);
1643: --
1644: elsif r_pgm.dml_operation ='UPDATE' and l_pgm_id is not null then
1645: hr_utility.set_location(' BEN_PGM_F UPDATE_PROGRAM ',30);
1646: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1647: if p_datetrack_mode <> 'CORRECTION' then
1648: l_dt_mode := get_update_mode(p_table_name => 'BEN_PGM_F',
1649: p_key_column_name => 'PGM_ID',
1642: hr_utility.set_location('pgm writeback comp ',3);
1643: --
1644: elsif r_pgm.dml_operation ='UPDATE' and l_pgm_id is not null then
1645: hr_utility.set_location(' BEN_PGM_F UPDATE_PROGRAM ',30);
1646: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1647: if p_datetrack_mode <> 'CORRECTION' then
1648: l_dt_mode := get_update_mode(p_table_name => 'BEN_PGM_F',
1649: p_key_column_name => 'PGM_ID',
1650: p_key_column_value => l_pgm_id,
1648: l_dt_mode := get_update_mode(p_table_name => 'BEN_PGM_F',
1649: p_key_column_name => 'PGM_ID',
1650: p_key_column_value => l_pgm_id,
1651: p_effective_date => l_effective_date);
1652: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1653: else
1654: l_dt_mode := p_datetrack_mode;
1655: end if;
1656: l_db_ovn := get_ovn(p_table_name => 'BEN_PGM_F',
1656: l_db_ovn := get_ovn(p_table_name => 'BEN_PGM_F',
1657: p_key_column_name => 'PGM_ID',
1658: p_key_column_value => l_pgm_id,
1659: p_effective_date => l_effective_date);
1660: hr_utility.set_location(' ovn is '||l_db_ovn,30);
1661: if l_db_ovn <> l_ovn then
1662: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PGM');
1663: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1664: fnd_message.set_token('OBJECT ',l_object);
1763: P_MESSAGE_TEXT => l_message_text,
1764: p_effective_date => p_effective_date);
1765: end if;
1766: end loop;
1767: hr_utility.set_location('leaving '||l_proc,100);
1768: exception
1769: when others then
1770: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1771: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1802: l_effective_date date;
1803: l_dt_mode varchar2(30);
1804: l_grd_effstdt date; --DN code for BugId: 3242976
1805: begin
1806: hr_utility.set_location('inside '||l_proc,10);
1807: for r_cop in c_cop loop
1808: l_ovn := r_cop.information265;
1809: l_effective_date := r_cop.information2;
1810: if r_cop.INFORMATION261 is null and r_cop.gs_parent_entity_result_id is not null then
1836: and l_pl_id is not null
1837: and l_ovn is null
1838: and l_opt_id is not null then
1839: if l_oipl_id is null then
1840: hr_utility.set_location(' BEN_OIPL_F CREATE_OPTION_IN_PLAN ',20);
1841: --DN: Start code for BugId: 3242976
1842: begin
1843: SELECT grd.date_from
1844: INTO l_grd_effstdt
1893: ,P_EFFECTIVE_START_DATE => l_effective_start_date
1894: ,P_EFFECTIVE_END_DATE => l_effective_end_date
1895: ,P_OBJECT_VERSION_NUMBER => l_ovn
1896: );
1897: hr_utility.set_location('After oipl ins '||l_oipl_id,222);
1898: end if;
1899: oipl_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1900: p_oipl_id => l_oipl_id,
1901: p_oipl_cer_id => r_COP.copy_entity_result_id);
1898: end if;
1899: oipl_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1900: p_oipl_id => l_oipl_id,
1901: p_oipl_cer_id => r_COP.copy_entity_result_id);
1902: hr_utility.set_location('oipl wrtback comp'||l_oipl_id,222);
1903: elsif l_oipl_id is not null and r_cop.dml_operation ='UPDATE'
1904: and l_ovn is not null
1905: and l_pl_id is not null
1906: and l_opt_id is not null then
1903: elsif l_oipl_id is not null and r_cop.dml_operation ='UPDATE'
1904: and l_ovn is not null
1905: and l_pl_id is not null
1906: and l_opt_id is not null then
1907: hr_utility.set_location(' BEN_OIPL_F UPDATE_OPTION_IN_PLAN ',30);
1908: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1909: if p_datetrack_mode <> 'CORRECTION' then
1910: l_dt_mode := get_update_mode(p_table_name => 'BEN_OIPL_F',
1911: p_key_column_name => 'OIPL_ID',
1904: and l_ovn is not null
1905: and l_pl_id is not null
1906: and l_opt_id is not null then
1907: hr_utility.set_location(' BEN_OIPL_F UPDATE_OPTION_IN_PLAN ',30);
1908: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1909: if p_datetrack_mode <> 'CORRECTION' then
1910: l_dt_mode := get_update_mode(p_table_name => 'BEN_OIPL_F',
1911: p_key_column_name => 'OIPL_ID',
1912: p_key_column_value => l_oipl_id,
1913: p_effective_date => l_effective_date);
1914: else
1915: l_dt_mode := p_datetrack_mode;
1916: end if;
1917: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1918: l_db_ovn := get_ovn(p_table_name => 'BEN_OIPL_F',
1919: p_key_column_name => 'OIPL_ID',
1920: p_key_column_value => l_oipl_id,
1921: p_effective_date => l_effective_date);
1918: l_db_ovn := get_ovn(p_table_name => 'BEN_OIPL_F',
1919: p_key_column_name => 'OIPL_ID',
1920: p_key_column_value => l_oipl_id,
1921: p_effective_date => l_effective_date);
1922: hr_utility.set_location(' ovn is '||l_db_ovn,30);
1923: if l_db_ovn <> l_ovn then
1924: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','OIPL');
1925: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1926: fnd_message.set_token('OBJECT ',l_object);
1940: ,P_DATETRACK_MODE => l_dt_mode
1941: );
1942: end if;
1943: elsif l_oipl_id is not null and r_cop.dml_operation in ('INSERT','COPIED','UPD_INS') then
1944: hr_utility.set_location('step api call created oipl'||l_proc,100);
1945: elsif l_oipl_id is not null and r_cop.dml_operation in ('DELETE') then
1946: hr_utility.set_location('oipl is being deleted '||l_proc,100);
1947: else
1948: l_message_text := 'invalid dml_operation is'||r_cop.dml_operation
1942: end if;
1943: elsif l_oipl_id is not null and r_cop.dml_operation in ('INSERT','COPIED','UPD_INS') then
1944: hr_utility.set_location('step api call created oipl'||l_proc,100);
1945: elsif l_oipl_id is not null and r_cop.dml_operation in ('DELETE') then
1946: hr_utility.set_location('oipl is being deleted '||l_proc,100);
1947: else
1948: l_message_text := 'invalid dml_operation is'||r_cop.dml_operation
1949: ||' oipl_id is'||l_oipl_id
1950: ||' pl_id is'||l_pl_id
1959: P_MESSAGE_TEXT => l_message_text,
1960: p_effective_date => p_effective_date);
1961: end if;
1962: end loop;
1963: hr_utility.set_location('leaving '||l_proc,100);
1964: exception
1965: when others then
1966: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1967: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2008: l_effective_start_date date ;
2009: l_effective_end_date date ;
2010: l_effective_date date;
2011: begin
2012: hr_utility.set_location('inside '||l_proc,10);
2013: hr_utility.set_location('bus_area is '||p_business_area,10);
2014: for r_upd_cpp in c_updated_cpp loop
2015: -- api is not called as we don't want to update ovn
2016: update ben_plip_f
2009: l_effective_end_date date ;
2010: l_effective_date date;
2011: begin
2012: hr_utility.set_location('inside '||l_proc,10);
2013: hr_utility.set_location('bus_area is '||p_business_area,10);
2014: for r_upd_cpp in c_updated_cpp loop
2015: -- api is not called as we don't want to update ovn
2016: update ben_plip_f
2017: set ordr_num = null
2038: and copy_entity_result_id = r_CPP.INFORMATION252;
2039: else
2040: l_pl_id := r_CPP.INFORMATION261;
2041: end if;
2042: hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2043: hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2044: hr_utility.set_location('pl_id: '||l_pl_id,20);
2045: --Added by kgowripe for bug#3532412
2046: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2039: else
2040: l_pl_id := r_CPP.INFORMATION261;
2041: end if;
2042: hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2043: hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2044: hr_utility.set_location('pl_id: '||l_pl_id,20);
2045: --Added by kgowripe for bug#3532412
2046: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2047: SELECT information1
2040: l_pl_id := r_CPP.INFORMATION261;
2041: end if;
2042: hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2043: hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2044: hr_utility.set_location('pl_id: '||l_pl_id,20);
2045: --Added by kgowripe for bug#3532412
2046: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2047: SELECT information1
2048: INTO l_corps_definition_id
2056: l_effective_date := r_cpp.information2;
2057: begin
2058: if l_plip_id is null and r_cpp.dml_operation = 'INSERT'
2059: and l_pgm_id is not null and l_pl_id is not null then
2060: hr_utility.set_location(' BEN_PLIP_F CREATE_PLAN_IN_PROGRAM ',20);
2061: BEN_PLAN_IN_PROGRAM_API.CREATE_PLAN_IN_PROGRAM(
2062: P_EFFECTIVE_DATE => l_effective_date
2063: ,P_BUSINESS_GROUP_ID => p_business_group_id
2064: ,P_ALWS_UNRSTRCTD_ENRT_FLAG => nvl(r_CPP.INFORMATION15,'N')
2120: ,P_EFFECTIVE_START_DATE => l_effective_start_date
2121: ,P_EFFECTIVE_END_DATE => l_effective_end_date
2122: ,P_OBJECT_VERSION_NUMBER => l_ovn
2123: );
2124: hr_utility.set_location('After plip ins'||l_plip_id,22);
2125: plip_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2126: p_plip_id => l_plip_id,
2127: p_pl_id => l_pl_id,
2128: p_plip_cer_id => r_CPP.copy_entity_result_id);
2125: plip_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2126: p_plip_id => l_plip_id,
2127: p_pl_id => l_pl_id,
2128: p_plip_cer_id => r_CPP.copy_entity_result_id);
2129: hr_utility.set_location('plip wrtback comp'||l_plip_id,222);
2130: if p_business_area = 'PQH_CORPS_TASK_LIST' then
2131: hr_utility.set_location('going for quota cr'||l_plip_id,222);
2132: pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2133: p_grade_id => r_cpp.information253,
2127: p_pl_id => l_pl_id,
2128: p_plip_cer_id => r_CPP.copy_entity_result_id);
2129: hr_utility.set_location('plip wrtback comp'||l_plip_id,222);
2130: if p_business_area = 'PQH_CORPS_TASK_LIST' then
2131: hr_utility.set_location('going for quota cr'||l_plip_id,222);
2132: pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2133: p_grade_id => r_cpp.information253,
2134: p_corps_definition_id => l_corps_definition_id,
2135: p_corps_extra_info_id => r_cpp.information290,
2141: end if;
2142: elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
2143: and l_ovn is not null and l_pgm_id is not null
2144: and l_pl_id is not null then
2145: hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
2146: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2147: if p_datetrack_mode <> 'CORRECTION' then
2148: l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2149: p_key_column_name => 'PLIP_ID',
2142: elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
2143: and l_ovn is not null and l_pgm_id is not null
2144: and l_pl_id is not null then
2145: hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
2146: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2147: if p_datetrack_mode <> 'CORRECTION' then
2148: l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2149: p_key_column_name => 'PLIP_ID',
2150: p_key_column_value => l_plip_id,
2148: l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2149: p_key_column_name => 'PLIP_ID',
2150: p_key_column_value => l_plip_id,
2151: p_effective_date => l_effective_date);
2152: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2153: else
2154: l_dt_mode := p_datetrack_mode;
2155: end if;
2156: l_db_ovn := get_ovn(p_table_name => 'BEN_PLIP_F',
2156: l_db_ovn := get_ovn(p_table_name => 'BEN_PLIP_F',
2157: p_key_column_name => 'PLIP_ID',
2158: p_key_column_value => l_plip_id,
2159: p_effective_date => l_effective_date);
2160: hr_utility.set_location(' ovn is '||l_db_ovn,30);
2161: if l_db_ovn <> l_ovn then
2162: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLIP');
2163: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2164: fnd_message.set_token('OBJECT ',l_object);
2204: P_MESSAGE_TEXT => l_message_text,
2205: p_effective_date => p_effective_date);
2206: end if;
2207: exception when others then
2208: hr_utility.set_location('issues in writing cpp, skipping'||l_proc,100);
2209: raise;
2210: end;
2211: end loop;
2212: hr_utility.set_location('leaving '||l_proc,100);
2208: hr_utility.set_location('issues in writing cpp, skipping'||l_proc,100);
2209: raise;
2210: end;
2211: end loop;
2212: hr_utility.set_location('leaving '||l_proc,100);
2213: exception
2214: when others then
2215: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2216: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2272: l_pt_cd varchar2(30);
2273: l_esd date;
2274: l_eed date;
2275: begin
2276: hr_utility.set_location('inside '||l_proc,10);
2277: for r_elp in c_elp loop
2278: l_elp_id := r_elp.information161;
2279: hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2280: l_effective_date := r_elp.information2;
2275: begin
2276: hr_utility.set_location('inside '||l_proc,10);
2277: for r_elp in c_elp loop
2278: l_elp_id := r_elp.information161;
2279: hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2280: l_effective_date := r_elp.information2;
2281: if r_elp.information277 is null and r_elp.information161 = l_old_crset_id then
2282: hr_utility.set_location('reusing earlier values ',20);
2283: -- reuse old values
2278: l_elp_id := r_elp.information161;
2279: hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2280: l_effective_date := r_elp.information2;
2281: if r_elp.information277 is null and r_elp.information161 = l_old_crset_id then
2282: hr_utility.set_location('reusing earlier values ',20);
2283: -- reuse old values
2284: l_elp_id := l_old_elp_id;
2285: l_elp_ovn := l_old_elp_ovn;
2286: else
2283: -- reuse old values
2284: l_elp_id := l_old_elp_id;
2285: l_elp_ovn := l_old_elp_ovn;
2286: else
2287: hr_utility.set_location('earlier values cannot be reused',20);
2288: -- new crset_id is to be entered
2289: l_elp_id := r_elp.information277;
2290: l_elp_ovn := r_elp.information265;
2291: l_old_elp_id := '';
2307: l_dml_operation := 'UPDATE';
2308: else
2309: l_dml_operation := '';
2310: end if;
2311: hr_utility.set_location('dml_operation is '||l_dml_operation,4);
2312: if r_elp.information232 is not null then
2313: l_loc_flag := 'Y';
2314: else
2315: l_loc_flag := 'N';
2354: else
2355: l_pr_flag := 'N';
2356: end if;
2357: if l_dml_operation = 'INSERT' then
2358: hr_utility.set_location(' BEN_ELIGY_PRFL_F CREATE_ELIGY_PROFILE ',20);
2359: begin
2360: BEN_ELIGY_PROFILE_API.CREATE_ELIGY_PROFILE(
2361: P_EFFECTIVE_DATE => l_effective_date
2362: ,P_BUSINESS_GROUP_ID => p_business_group_id
2381: );
2382: elp_writeback(p_crset_id => r_elp.information161,
2383: p_elp_id => l_elp_id,
2384: p_copy_entity_txn_id => p_copy_entity_txn_id);
2385: hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2386: if l_rl_flag = 'Y' then
2387: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2388: hr_utility.set_location('rule id is'||r_elp.information235,20);
2389: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2383: p_elp_id => l_elp_id,
2384: p_copy_entity_txn_id => p_copy_entity_txn_id);
2385: hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2386: if l_rl_flag = 'Y' then
2387: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2388: hr_utility.set_location('rule id is'||r_elp.information235,20);
2389: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2390: P_EFFECTIVE_DATE => l_effective_date
2391: ,P_BUSINESS_GROUP_ID => p_business_group_id
2384: p_copy_entity_txn_id => p_copy_entity_txn_id);
2385: hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2386: if l_rl_flag = 'Y' then
2387: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2388: hr_utility.set_location('rule id is'||r_elp.information235,20);
2389: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2390: P_EFFECTIVE_DATE => l_effective_date
2391: ,P_BUSINESS_GROUP_ID => p_business_group_id
2392: ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2399: ,P_OBJECT_VERSION_NUMBER => l_ovn
2400: );
2401: end if;
2402: if l_bu_flag = 'Y' then
2403: hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2404: hr_utility.set_location('bargaining unit cd is'||r_elp.information101,20);
2405: BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2406: P_EFFECTIVE_DATE => l_effective_date
2407: ,P_BUSINESS_GROUP_ID => p_business_group_id
2400: );
2401: end if;
2402: if l_bu_flag = 'Y' then
2403: hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2404: hr_utility.set_location('bargaining unit cd is'||r_elp.information101,20);
2405: BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2406: P_EFFECTIVE_DATE => l_effective_date
2407: ,P_BUSINESS_GROUP_ID => p_business_group_id
2408: ,P_BRGNG_UNIT_CD => r_elp.information101
2415: ,P_OBJECT_VERSION_NUMBER => l_ovn
2416: );
2417: end if;
2418: if l_fp_flag = 'Y' then
2419: hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2420: hr_utility.set_location('fulltime cd is'||r_elp.information102,20);
2421: BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2422: P_EFFECTIVE_DATE => l_effective_date
2423: ,P_BUSINESS_GROUP_ID => p_business_group_id
2416: );
2417: end if;
2418: if l_fp_flag = 'Y' then
2419: hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2420: hr_utility.set_location('fulltime cd is'||r_elp.information102,20);
2421: BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2422: P_EFFECTIVE_DATE => l_effective_date
2423: ,P_BUSINESS_GROUP_ID => p_business_group_id
2424: ,P_ELIGY_PRFL_ID => l_elp_id
2431: ,P_OBJECT_VERSION_NUMBER => l_ovn
2432: );
2433: end if;
2434: if l_pr_flag = 'Y' then
2435: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2436: hr_utility.set_location('event type is'||r_elp.information103,20);
2437: hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2438: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2439: P_EFFECTIVE_DATE => l_effective_date
2432: );
2433: end if;
2434: if l_pr_flag = 'Y' then
2435: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2436: hr_utility.set_location('event type is'||r_elp.information103,20);
2437: hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2438: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2439: P_EFFECTIVE_DATE => l_effective_date
2440: ,P_BUSINESS_GROUP_ID => p_business_group_id
2433: end if;
2434: if l_pr_flag = 'Y' then
2435: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2436: hr_utility.set_location('event type is'||r_elp.information103,20);
2437: hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2438: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2439: P_EFFECTIVE_DATE => l_effective_date
2440: ,P_BUSINESS_GROUP_ID => p_business_group_id
2441: ,P_ELIGY_PRFL_ID => l_elp_id
2450: );
2451: end if;
2452: if l_pt_flag = 'Y' then
2453: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2454: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2455: hr_utility.set_location('per type id is'||r_elp.information236,20);
2456: hr_utility.set_location('per type cd is'||l_pt_cd,20);
2457: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2458: P_EFFECTIVE_DATE => l_effective_date
2451: end if;
2452: if l_pt_flag = 'Y' then
2453: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2454: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2455: hr_utility.set_location('per type id is'||r_elp.information236,20);
2456: hr_utility.set_location('per type cd is'||l_pt_cd,20);
2457: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2458: P_EFFECTIVE_DATE => l_effective_date
2459: ,P_BUSINESS_GROUP_ID => p_business_group_id
2452: if l_pt_flag = 'Y' then
2453: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2454: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2455: hr_utility.set_location('per type id is'||r_elp.information236,20);
2456: hr_utility.set_location('per type cd is'||l_pt_cd,20);
2457: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2458: P_EFFECTIVE_DATE => l_effective_date
2459: ,P_BUSINESS_GROUP_ID => p_business_group_id
2460: ,P_ELIGY_PRFL_ID => l_elp_id
2468: ,P_OBJECT_VERSION_NUMBER => l_ovn
2469: );
2470: end if;
2471: if l_sa_flag = 'Y' then
2472: hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2473: hr_utility.set_location('serv area id is'||r_elp.information237,20);
2474: BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2475: P_EFFECTIVE_DATE => l_effective_date
2476: ,P_BUSINESS_GROUP_ID => p_business_group_id
2469: );
2470: end if;
2471: if l_sa_flag = 'Y' then
2472: hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2473: hr_utility.set_location('serv area id is'||r_elp.information237,20);
2474: BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2475: P_EFFECTIVE_DATE => l_effective_date
2476: ,P_BUSINESS_GROUP_ID => p_business_group_id
2477: ,P_ELIGY_PRFL_ID => l_elp_id
2484: ,P_OBJECT_VERSION_NUMBER => l_ovn
2485: );
2486: end if;
2487: if l_loc_flag = 'Y' then
2488: hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2489: hr_utility.set_location('location id is'||r_elp.information232,20);
2490: BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2491: P_EFFECTIVE_DATE => l_effective_date
2492: ,P_BUSINESS_GROUP_ID => p_business_group_id
2485: );
2486: end if;
2487: if l_loc_flag = 'Y' then
2488: hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2489: hr_utility.set_location('location id is'||r_elp.information232,20);
2490: BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2491: P_EFFECTIVE_DATE => l_effective_date
2492: ,P_BUSINESS_GROUP_ID => p_business_group_id
2493: ,P_ELIGY_PRFL_ID => l_elp_id
2500: ,P_OBJECT_VERSION_NUMBER => l_ovn
2501: );
2502: end if;
2503: if l_org_flag = 'Y' then
2504: hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2505: hr_utility.set_location('org id is'||r_elp.information234,20);
2506: BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2507: P_EFFECTIVE_DATE => l_effective_date
2508: ,P_BUSINESS_GROUP_ID => p_business_group_id
2501: );
2502: end if;
2503: if l_org_flag = 'Y' then
2504: hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2505: hr_utility.set_location('org id is'||r_elp.information234,20);
2506: BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2507: P_EFFECTIVE_DATE => l_effective_date
2508: ,P_BUSINESS_GROUP_ID => p_business_group_id
2509: ,P_ELIGY_PRFL_ID => l_elp_id
2516: ,P_OBJECT_VERSION_NUMBER => l_ovn
2517: );
2518: end if;
2519: if l_job_flag = 'Y' then
2520: hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2521: hr_utility.set_location('org id is'||r_elp.information233,20);
2522: BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2523: P_EFFECTIVE_DATE => l_effective_date
2524: ,P_BUSINESS_GROUP_ID => p_business_group_id
2517: );
2518: end if;
2519: if l_job_flag = 'Y' then
2520: hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2521: hr_utility.set_location('org id is'||r_elp.information233,20);
2522: BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2523: P_EFFECTIVE_DATE => l_effective_date
2524: ,P_BUSINESS_GROUP_ID => p_business_group_id
2525: ,P_ELIGY_PRFL_ID => l_elp_id
2532: ,P_OBJECT_VERSION_NUMBER => l_ovn
2533: );
2534: end if;
2535: exception when others then
2536: hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2537: raise;
2538: end;
2539: hr_utility.set_location('After plsql table ',222);
2540: elsif l_dml_operation = 'UPDATE' then
2535: exception when others then
2536: hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2537: raise;
2538: end;
2539: hr_utility.set_location('After plsql table ',222);
2540: elsif l_dml_operation = 'UPDATE' then
2541: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2542: if p_datetrack_mode <> 'CORRECTION' then
2543: l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2537: raise;
2538: end;
2539: hr_utility.set_location('After plsql table ',222);
2540: elsif l_dml_operation = 'UPDATE' then
2541: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2542: if p_datetrack_mode <> 'CORRECTION' then
2543: l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2544: p_key_column_name => 'ELIGY_PRFL_ID',
2545: p_key_column_value => l_elp_id,
2543: l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2544: p_key_column_name => 'ELIGY_PRFL_ID',
2545: p_key_column_value => l_elp_id,
2546: p_effective_date => l_effective_date);
2547: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2548: else
2549: l_dt_mode := p_datetrack_mode;
2550: end if;
2551: l_db_ovn := get_ovn(p_table_name => 'BEN_ELIGY_PRFL_F',
2551: l_db_ovn := get_ovn(p_table_name => 'BEN_ELIGY_PRFL_F',
2552: p_key_column_name => 'ELIGY_PRFL_ID',
2553: p_key_column_value => l_elp_id,
2554: p_effective_date => l_effective_date);
2555: hr_utility.set_location(' ovn is '||l_db_ovn,30);
2556: if l_db_ovn <> l_ovn then
2557: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','ELP');
2558: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2559: fnd_message.set_token('OBJECT ',l_object);
2559: fnd_message.set_token('OBJECT ',l_object);
2560: fnd_message.set_token('OBJECT_NAME ',r_ELP.INFORMATION151);
2561: fnd_message.raise_error;
2562: else
2563: hr_utility.set_location(' BEN_ELIGY_PRFL_F UPDATE_ELIGY_PROFILE ',30);
2564: begin
2565: BEN_ELIGY_PROFILE_API.UPDATE_ELIGY_PROFILE(
2566: P_EFFECTIVE_DATE => l_effective_date
2567: ,P_BUSINESS_GROUP_ID => p_business_group_id
2582: ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
2583: ,P_DATETRACK_MODE => l_dt_mode
2584: );
2585: exception when others then
2586: hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2587: raise;
2588: end;
2589: end if;
2590: if l_rl_flag = 'Y' and l_old_rl_flag is null and r_elp.information225 is null then
2587: raise;
2588: end;
2589: end if;
2590: if l_rl_flag = 'Y' and l_old_rl_flag is null and r_elp.information225 is null then
2591: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2592: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2593: P_EFFECTIVE_DATE => l_effective_date
2594: ,P_BUSINESS_GROUP_ID => p_business_group_id
2595: ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2606: p_crit_type => 'RL',
2607: p_effective_date => l_effective_date);
2608: end if;
2609: if l_bu_flag = 'Y' and l_old_bu_flag is null and r_elp.information228 is null then
2610: hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2611: BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2612: P_EFFECTIVE_DATE => l_effective_date
2613: ,P_BUSINESS_GROUP_ID => p_business_group_id
2614: ,P_BRGNG_UNIT_CD => r_elp.information101
2625: p_crit_type => 'BU',
2626: p_effective_date => l_effective_date);
2627: end if;
2628: if l_fp_flag = 'Y' and l_old_fp_flag is null and r_elp.information229 is null then
2629: hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2630: BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2631: P_EFFECTIVE_DATE => l_effective_date
2632: ,P_BUSINESS_GROUP_ID => p_business_group_id
2633: ,P_ELIGY_PRFL_ID => l_elp_id
2644: p_crit_type => 'FP',
2645: p_effective_date => l_effective_date);
2646: end if;
2647: if l_pr_flag = 'Y' and l_old_pr_flag is null and r_elp.information230 is null then
2648: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2649: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2650: P_EFFECTIVE_DATE => l_effective_date
2651: ,P_BUSINESS_GROUP_ID => p_business_group_id
2652: ,P_ELIGY_PRFL_ID => l_elp_id
2665: p_effective_date => l_effective_date);
2666: end if;
2667: if l_pt_flag = 'Y' and l_old_pt_flag is null and r_elp.information226 is null then
2668: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2669: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2670: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2671: P_EFFECTIVE_DATE => l_effective_date
2672: ,P_BUSINESS_GROUP_ID => p_business_group_id
2673: ,P_ELIGY_PRFL_ID => l_elp_id
2685: p_crit_type => 'PT',
2686: p_effective_date => l_effective_date);
2687: end if;
2688: if l_sa_flag = 'Y' and l_old_sa_flag is null and r_elp.information227 is null then
2689: hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2690: BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2691: P_EFFECTIVE_DATE => l_effective_date
2692: ,P_BUSINESS_GROUP_ID => p_business_group_id
2693: ,P_ELIGY_PRFL_ID => l_elp_id
2704: p_crit_type => 'SA',
2705: p_effective_date => l_effective_date);
2706: end if;
2707: if l_loc_flag = 'Y' and l_old_loc_flag is null and r_elp.information222 is null then
2708: hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2709: BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2710: P_EFFECTIVE_DATE => l_effective_date
2711: ,P_BUSINESS_GROUP_ID => p_business_group_id
2712: ,P_ELIGY_PRFL_ID => l_elp_id
2723: p_crit_type => 'LOC',
2724: p_effective_date => l_effective_date);
2725: end if;
2726: if l_org_flag = 'Y' and l_old_org_flag is null and r_elp.information224 is null then
2727: hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2728: BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2729: P_EFFECTIVE_DATE => l_effective_date
2730: ,P_BUSINESS_GROUP_ID => p_business_group_id
2731: ,P_ELIGY_PRFL_ID => l_elp_id
2742: p_crit_type => 'ORG',
2743: p_effective_date => l_effective_date);
2744: end if;
2745: if l_job_flag = 'Y' and l_old_job_flag is null and r_elp.information223 is null then
2746: hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2747: BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2748: P_EFFECTIVE_DATE => l_effective_date
2749: ,P_BUSINESS_GROUP_ID => p_business_group_id
2750: ,P_ELIGY_PRFL_ID => l_elp_id
2786: l_old_loc_flag := l_loc_flag;
2787: l_old_org_flag := l_org_flag;
2788: l_old_job_flag := l_job_flag;
2789: end loop;
2790: hr_utility.set_location('leaving '||l_proc,100);
2791: exception
2792: when others then
2793: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2794: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2825: l_epa_id number;
2826: l_pk number;
2827: l_tab varchar2(30);
2828: begin
2829: hr_utility.set_location('inside '||l_proc,10);
2830: for r_cep in c_cep loop
2831: l_cep_id := r_cep.information1;
2832: l_ovn := r_cep.information265;
2833: hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2829: hr_utility.set_location('inside '||l_proc,10);
2830: for r_cep in c_cep loop
2831: l_cep_id := r_cep.information1;
2832: l_ovn := r_cep.information265;
2833: hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2834: if r_cep.gs_mirror_src_entity_result_id is not null then
2835: select information1,table_alias
2836: into l_pk, l_tab
2837: from ben_copy_entity_results
2835: select information1,table_alias
2836: into l_pk, l_tab
2837: from ben_copy_entity_results
2838: where copy_entity_result_id = r_cep.gs_mirror_src_entity_result_id;
2839: hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
2840: if l_tab ='EPA' then
2841: l_epa_id := l_pk;
2842: else
2843: l_epa_id := null;
2847: end if;
2848: l_effective_date := r_cep.information2;
2849: begin
2850: if l_cep_id is null and l_epa_id is not null and l_ovn is null and r_cep.dml_operation = 'INSERT' then
2851: hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F CREATE_PRTN_ELIG_PRFL ',20);
2852: BEN_PRTN_ELIG_PRFL_API.CREATE_PRTN_ELIG_PRFL(
2853: P_EFFECTIVE_DATE => l_effective_date
2854: ,P_BUSINESS_GROUP_ID => p_business_group_id
2855: ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2862: ,P_EFFECTIVE_END_DATE => l_effective_end_date
2863: ,P_OBJECT_VERSION_NUMBER => l_ovn
2864: );
2865: elsif l_cep_id is not null and l_epa_id is not null and l_ovn is not null and r_cep.dml_operation = 'UPDATE' then
2866: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2867: if p_datetrack_mode <> 'CORRECTION' then
2868: l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2869: p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2870: p_key_column_value => l_cep_id,
2868: l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2869: p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2870: p_key_column_value => l_cep_id,
2871: p_effective_date => l_effective_date);
2872: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2873: else
2874: l_dt_mode := p_datetrack_mode;
2875: end if;
2876: l_db_ovn := get_ovn(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2876: l_db_ovn := get_ovn(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2877: p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2878: p_key_column_value => l_cep_id,
2879: p_effective_date => l_effective_date);
2880: hr_utility.set_location(' ovn is '||l_db_ovn,30);
2881: if l_db_ovn <> l_ovn then
2882: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','CEP');
2883: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2884: fnd_message.set_token('OBJECT ',l_object);
2884: fnd_message.set_token('OBJECT ',l_object);
2885: fnd_message.set_token('OBJECT_NAME ','CEP :'||l_cep_id);
2886: fnd_message.raise_error;
2887: else
2888: hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F UPDATE_PRTN_ELIG_PRFL ',30);
2889: BEN_PRTN_ELIG_PRFL_API.UPDATE_PRTN_ELIG_PRFL(
2890: P_EFFECTIVE_DATE => l_effective_date
2891: ,P_BUSINESS_GROUP_ID => p_business_group_id
2892: ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2913: P_MESSAGE_TEXT => l_message_text,
2914: p_effective_date => p_effective_date);
2915: end if;
2916: exception when others then
2917: hr_utility.set_location('issues in writing cep, skipping'||l_proc,100);
2918: raise;
2919: end;
2920: end loop;
2921: hr_utility.set_location('leaving '||l_proc,100);
2917: hr_utility.set_location('issues in writing cep, skipping'||l_proc,100);
2918: raise;
2919: end;
2920: end loop;
2921: hr_utility.set_location('leaving '||l_proc,100);
2922: exception
2923: when others then
2924: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2925: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2952: l_effective_start_date date ;
2953: l_effective_end_date date ;
2954: l_effective_date date;
2955: begin
2956: hr_utility.set_location('inside '||l_proc,10);
2957: for r_abr in c_abr loop
2958: l_pl_id := null;
2959: l_opt_id := null;
2960: l_abr_id := r_abr.information1;
2957: for r_abr in c_abr loop
2958: l_pl_id := null;
2959: l_opt_id := null;
2960: l_abr_id := r_abr.information1;
2961: hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2962: l_effective_date := r_abr.information2;
2963: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2964: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2965: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2959: l_opt_id := null;
2960: l_abr_id := r_abr.information1;
2961: hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2962: l_effective_date := r_abr.information2;
2963: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2964: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2965: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2966: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2967: begin
2960: l_abr_id := r_abr.information1;
2961: hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2962: l_effective_date := r_abr.information2;
2963: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2964: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2965: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2966: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2967: begin
2968: select information1
2962: l_effective_date := r_abr.information2;
2963: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2964: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2965: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2966: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2967: begin
2968: select information1
2969: into l_pl_id
2970: from ben_copy_entity_results
2977: l_pl_id := r_ABR.INFORMATION261;
2978: elsif r_ABR.INFORMATION247 is not null then
2979: l_opt_id := r_ABR.INFORMATION247;
2980: elsif r_ABR.INFORMATION278 is not null and r_ABR.INFORMATION247 is null then
2981: hr_utility.set_location('opt_cer_id :'||r_ABR.INFORMATION278,3);
2982: begin
2983: select information1
2984: into l_opt_id
2985: from ben_copy_entity_results
2988: when others then
2989: l_opt_id := '';
2990: end;
2991: else
2992: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2993: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2994: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2995: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2996: end if;
2989: l_opt_id := '';
2990: end;
2991: else
2992: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2993: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2994: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2995: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2996: end if;
2997: /*
2990: end;
2991: else
2992: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2993: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2994: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2995: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2996: end if;
2997: /*
2998: l_message_text := 'oper is'||r_ABR.dml_operation
2991: else
2992: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2993: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2994: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2995: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2996: end if;
2997: /*
2998: l_message_text := 'oper is'||r_ABR.dml_operation
2999: ||' pl id is '||r_ABR.INFORMATION261
3011: p_effective_date => p_effective_date);
3012: */
3013: begin
3014: if l_abr_id is null and (l_pl_id is not null or l_opt_id is not null) and r_abr.information266 is not null then
3015: hr_utility.set_location(' BEN_ACTY_BASE_RT_F CREATE_ACTY_BASE_RATE ',20);
3016: BEN_ACTY_BASE_RATE_API.CREATE_ACTY_BASE_RATE(
3017: P_EFFECTIVE_DATE => l_effective_date
3018: ,P_BUSINESS_GROUP_ID => p_business_group_id
3019: ,P_ABV_MX_ELCN_VAL_ALWD_FLAG => nvl(r_ABR.INFORMATION27,'N')
3108: ,P_EFFECTIVE_START_DATE => l_effective_start_date
3109: ,P_EFFECTIVE_END_DATE => l_effective_end_date
3110: ,P_OBJECT_VERSION_NUMBER => l_ovn
3111: );
3112: hr_utility.set_location('After plsql table ',222);
3113: update ben_copy_entity_results
3114: set information1 = l_abr_id
3115: where copy_entity_result_id = r_abr.copy_entity_result_id;
3116: hr_utility.set_location('abr id updated '||l_abr_id,222);
3112: hr_utility.set_location('After plsql table ',222);
3113: update ben_copy_entity_results
3114: set information1 = l_abr_id
3115: where copy_entity_result_id = r_abr.copy_entity_result_id;
3116: hr_utility.set_location('abr id updated '||l_abr_id,222);
3117: else
3118: l_message_text := 'invalid oper'||r_ABR.dml_operation
3119: ||' pl id is '||r_ABR.INFORMATION261
3120: ||' abr id is '||l_abr_id
3130: P_MESSAGE_TEXT => l_message_text,
3131: p_effective_date => p_effective_date);
3132: end if;
3133: exception when others then
3134: hr_utility.set_location('issues in writing abr, skipping'||l_proc,100);
3135: raise;
3136: end;
3137: end loop;
3138: hr_utility.set_location('leaving '||l_proc,100);
3134: hr_utility.set_location('issues in writing abr, skipping'||l_proc,100);
3135: raise;
3136: end;
3137: end loop;
3138: hr_utility.set_location('leaving '||l_proc,100);
3139: exception
3140: when others then
3141: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3142: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3173: l_pl_id number;
3174: l_oipl_id number;
3175: l_pgm_id number;
3176: begin
3177: hr_utility.set_location('inside '||l_proc,10);
3178: for r_epa in c_epa loop
3179: l_epa_id := r_epa.information1;
3180: hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3181: hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3176: begin
3177: hr_utility.set_location('inside '||l_proc,10);
3178: for r_epa in c_epa loop
3179: l_epa_id := r_epa.information1;
3180: hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3181: hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3182: if r_epa.gs_mirror_src_entity_result_id is not null then
3183: begin
3184: select information1,table_alias
3177: hr_utility.set_location('inside '||l_proc,10);
3178: for r_epa in c_epa loop
3179: l_epa_id := r_epa.information1;
3180: hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3181: hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3182: if r_epa.gs_mirror_src_entity_result_id is not null then
3183: begin
3184: select information1,table_alias
3185: into l_pk, l_tab
3188: exception
3189: when others then
3190: raise;
3191: end;
3192: hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
3193: if l_tab ='PGM' then
3194: l_pgm_id := l_pk;
3195: l_plip_id := null;
3196: l_oipl_id := null;
3221: end if;
3222: l_effective_date := r_epa.information2;
3223: begin
3224: if l_epa_id is null and l_pk is not null and r_epa.dml_operation = 'INSERT' then
3225: hr_utility.set_location(' BEN_PRTN_ELIG_F CREATE_PARTICIPATION_ELIG ',20);
3226: BEN_PARTICIPATION_ELIG_API.CREATE_PARTICIPATION_ELIG(
3227: P_EFFECTIVE_DATE => l_effective_date
3228: ,P_BUSINESS_GROUP_ID => p_business_group_id
3229: ,P_MX_POE_APLS_CD => r_EPA.INFORMATION17
3269: P_MESSAGE_TEXT => l_message_text,
3270: p_effective_date => p_effective_date);
3271: end if;
3272: exception when others then
3273: hr_utility.set_location('issues in writing epa, skipping'||l_proc,100);
3274: raise;
3275: end;
3276: end loop;
3277: hr_utility.set_location('leaving '||l_proc,100);
3273: hr_utility.set_location('issues in writing epa, skipping'||l_proc,100);
3274: raise;
3275: end;
3276: end loop;
3277: hr_utility.set_location('leaving '||l_proc,100);
3278: exception
3279: when others then
3280: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3281: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3325: l_crr_name varchar2(240);
3326: l_dml_operation varchar2(30);
3327: l_dt_mode varchar2(30);
3328: begin
3329: hr_utility.set_location('inside '||l_proc,10);
3330: for crr_rec in c_crr loop
3331: l_effective_date := crr_rec.information2;
3332: if crr_rec.information278 is null then
3333: hr_utility.set_location('new variable rate is being created'||l_proc,10);
3329: hr_utility.set_location('inside '||l_proc,10);
3330: for crr_rec in c_crr loop
3331: l_effective_date := crr_rec.information2;
3332: if crr_rec.information278 is null then
3333: hr_utility.set_location('new variable rate is being created'||l_proc,10);
3334: if (crr_rec.information230 is null or crr_rec.information230 = l_old_grd_cer_id)
3335: and (crr_rec.information169 is null or crr_rec.information169 = l_old_pnt_cer_id)
3336: and crr_rec.information160 = l_old_crset_id
3337: and l_old_crr_id is not null then
3334: if (crr_rec.information230 is null or crr_rec.information230 = l_old_grd_cer_id)
3335: and (crr_rec.information169 is null or crr_rec.information169 = l_old_pnt_cer_id)
3336: and crr_rec.information160 = l_old_crset_id
3337: and l_old_crr_id is not null then
3338: hr_utility.set_location('reusing prev row pk and ovn',16);
3339: l_crr_id := l_old_crr_id; -- previous row created id can be used
3340: l_crr_ovn := l_old_crr_ovn;
3341: l_abr_id := l_old_abr_id;
3342: l_crr_name := l_old_crr_name;
3340: l_crr_ovn := l_old_crr_ovn;
3341: l_abr_id := l_old_abr_id;
3342: l_crr_name := l_old_crr_name;
3343: else
3344: hr_utility.set_location('nothing to reuse'||l_proc,10);
3345: l_crr_id := crr_rec.information278;
3346: l_crr_ovn := crr_rec.information298;
3347: l_abr_id := '';
3348: l_avr_num := '';
3351: p_grade_cer_id => crr_rec.information230,
3352: p_copy_entity_txn_id => p_copy_entity_txn_id);
3353: end if;
3354: else
3355: hr_utility.set_location('existing vpf is being updated'||l_proc,10);
3356: l_crr_id := crr_rec.information278;
3357: l_crr_ovn := crr_rec.information298;
3358: l_abr_id := '';
3359: l_avr_num := '';
3367: elsif crr_rec.dml_operation = 'UPDATE' then
3368: l_dml_operation := 'UPDATE';
3369: end if;
3370: if l_abr_id is null and crr_rec.information161 is not null then
3371: hr_utility.set_location('abr_id is null ,getting it'||l_proc,10);
3372: begin
3373: select information1
3374: into l_abr_id
3375: from ben_copy_entity_results
3376: where copy_entity_txn_id = p_copy_entity_txn_id
3377: and copy_entity_result_id = crr_rec.information161;
3378: exception
3379: when no_data_found then
3380: hr_utility.set_location('abr id not found'||l_proc,100);
3381: when others then
3382: hr_utility.set_location('issues in getting abr'||l_proc,100);
3383: raise;
3384: end;
3378: exception
3379: when no_data_found then
3380: hr_utility.set_location('abr id not found'||l_proc,100);
3381: when others then
3382: hr_utility.set_location('issues in getting abr'||l_proc,100);
3383: raise;
3384: end;
3385: else
3386: hr_utility.set_location('abr_id is '||l_abr_id,10);
3382: hr_utility.set_location('issues in getting abr'||l_proc,100);
3383: raise;
3384: end;
3385: else
3386: hr_utility.set_location('abr_id is '||l_abr_id,10);
3387: end if;
3388: if l_dml_operation = 'INSERT' and l_abr_id is not null and l_crr_id is null and l_crr_ovn is null then
3389: begin
3390: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
3386: hr_utility.set_location('abr_id is '||l_abr_id,10);
3387: end if;
3388: if l_dml_operation = 'INSERT' and l_abr_id is not null and l_crr_id is null and l_crr_ovn is null then
3389: begin
3390: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
3391: BEN_VRBL_RATE_PROFILE_API.CREATE_VRBL_RATE_PROFILE(
3392: P_EFFECTIVE_DATE => l_effective_date
3393: ,P_BUSINESS_GROUP_ID => p_business_group_id
3394: ,P_ACTY_TYP_CD => 'GSPSA'
3463: ,P_EFFECTIVE_START_DATE => l_esd
3464: ,P_EFFECTIVE_END_DATE => l_eed
3465: ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3466: );
3467: hr_utility.set_location('after vpf insert ',222);
3468: begin
3469: if l_avr_num is null then
3470: hr_utility.set_location('1st crr for abr',222);
3471: begin
3466: );
3467: hr_utility.set_location('after vpf insert ',222);
3468: begin
3469: if l_avr_num is null then
3470: hr_utility.set_location('1st crr for abr',222);
3471: begin
3472: select null
3473: into dummy
3474: from ben_acty_base_rt_f
3477: and l_effective_date between effective_start_date
3478: and effective_end_date;
3479: exception
3480: when no_data_found then
3481: hr_utility.set_location('abr has vrbl flag No',222);
3482: begin
3483: update ben_acty_base_rt_f
3484: set USES_VARBL_RT_FLAG = 'Y'
3485: where acty_base_rt_id = l_abr_id;
3482: begin
3483: update ben_acty_base_rt_f
3484: set USES_VARBL_RT_FLAG = 'Y'
3485: where acty_base_rt_id = l_abr_id;
3486: hr_utility.set_location('updated to Yes',223);
3487: exception
3488: when others then
3489: hr_utility.set_location('issues in updating abr flag to Y',225);
3490: raise;
3485: where acty_base_rt_id = l_abr_id;
3486: hr_utility.set_location('updated to Yes',223);
3487: exception
3488: when others then
3489: hr_utility.set_location('issues in updating abr flag to Y',225);
3490: raise;
3491: end;
3492: when others then
3493: hr_utility.set_location('issues in gettting abr row ',226);
3489: hr_utility.set_location('issues in updating abr flag to Y',225);
3490: raise;
3491: end;
3492: when others then
3493: hr_utility.set_location('issues in gettting abr row ',226);
3494: raise;
3495: end;
3496: select nvl(max(ordr_num),0) + 1
3497: into l_avr_num
3499: where acty_base_rt_id = l_abr_id;
3500: else
3501: l_avr_num := l_avr_num + 1;
3502: end if;
3503: hr_utility.set_location(' BEN_ACTY_VRBL_RT_F CREATE_ACTY_VRBL_RATE ',20);
3504: BEN_ACTY_VRBL_RATE_API.CREATE_ACTY_VRBL_RATE(
3505: P_EFFECTIVE_DATE => l_effective_date
3506: ,P_BUSINESS_GROUP_ID => p_business_group_id
3507: ,P_ACTY_BASE_RT_ID => l_abr_id
3512: ,P_EFFECTIVE_END_DATE => l_eed
3513: ,P_OBJECT_VERSION_NUMBER => l_ovn
3514: );
3515: exception when others then
3516: hr_utility.set_location('issues in writing avr'||l_proc,100);
3517: raise;
3518: end;
3519: if crr_rec.information279 is null and crr_rec.information160 is not null then
3520: -- elp id is null for crrate while crset id is there
3546: P_MESSAGE_TEXT => l_message_text,
3547: p_effective_date => p_effective_date);
3548: else
3549: begin
3550: hr_utility.set_location(' BEN_VRBL_RT_ELIG_PRFL_F CREATE_VRBL_RT_ELIG_PRFL ',20);
3551: BEN_VRBL_RT_ELIG_PRFL_API.CREATE_VRBL_RT_ELIG_PRFL(
3552: P_EFFECTIVE_DATE => p_effective_date -- vep will be created as of Grade ladder effdt
3553: ,P_BUSINESS_GROUP_ID => p_business_group_id
3554: ,P_ELIGY_PRFL_ID => l_elp_id
3576: raise;
3577: end;
3578: end if;
3579: exception when others then
3580: hr_utility.set_location('issues in writing var'||l_proc,100);
3581: raise;
3582: end;
3583: elsif l_dml_operation = 'UPDATE'
3584: and l_abr_id is not null
3583: elsif l_dml_operation = 'UPDATE'
3584: and l_abr_id is not null
3585: and l_crr_id is not null
3586: and l_crr_ovn is not null then
3587: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
3588: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
3589: --if p_datetrack_mode <> 'CORRECTION' then /* Commented out to fix Bug:3964291 */
3590: l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3591: p_key_column_name => 'VRBL_RT_PRFL_ID',
3584: and l_abr_id is not null
3585: and l_crr_id is not null
3586: and l_crr_ovn is not null then
3587: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
3588: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
3589: --if p_datetrack_mode <> 'CORRECTION' then /* Commented out to fix Bug:3964291 */
3590: l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3591: p_key_column_name => 'VRBL_RT_PRFL_ID',
3592: p_key_column_value => l_crr_id,
3590: l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3591: p_key_column_name => 'VRBL_RT_PRFL_ID',
3592: p_key_column_value => l_crr_id,
3593: p_effective_date => l_effective_date);
3594: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
3595: /*else
3596: l_dt_mode := p_datetrack_mode;
3597: end if;*/
3598: l_db_ovn := get_ovn(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3598: l_db_ovn := get_ovn(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3599: p_key_column_name => 'VRBL_RT_PRFL_ID',
3600: p_key_column_value => l_crr_id,
3601: p_effective_date => l_effective_date);
3602: hr_utility.set_location(' ovn is '||l_db_ovn,30);
3603: if l_db_ovn <> l_crr_ovn then
3604: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','VPF');
3605: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
3606: fnd_message.set_token('OBJECT ',l_object);
3617: ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3618: ,P_DATETRACK_MODE => l_dt_mode
3619: );
3620: exception when others then
3621: hr_utility.set_location('issues in updating var'||l_proc,100);
3622: raise;
3623: end;
3624: end if;
3625: else
3645: l_old_grd_cer_id := crr_rec.information230;
3646: l_old_pnt_cer_id := crr_rec.information169;
3647: l_old_crset_id := crr_rec.information160;
3648: end loop;
3649: hr_utility.set_location('leaving '||l_proc,100);
3650: exception
3651: when others then
3652: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3653: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3855: ,p_effective_date date
3856: )
3857: is
3858: begin
3859: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3860:
3861: update ben_copy_entity_results cer
3862: set information263 =
3863: ( select ELIGY_PRFL_ID
3877: and elp.name = cer.information5
3878: and p_effective_date between
3879: elp.effective_start_date and elp.effective_end_date);
3880:
3881: hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
3882: hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3883: end upd_stg_elig_prfl_id ;
3884:
3885: procedure cre_update_elig_prfl(
3878: and p_effective_date between
3879: elp.effective_start_date and elp.effective_end_date);
3880:
3881: hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
3882: hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3883: end upd_stg_elig_prfl_id ;
3884:
3885: procedure cre_update_elig_prfl(
3886: p_copy_entity_txn_id in number
3889: ,p_business_area in varchar2 default 'PQH_GSP_TASK_LIST')
3890: is
3891: l_delete_failed varchar2(10);
3892: begin
3893: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3894: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3895: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3890: is
3891: l_delete_failed varchar2(10);
3892: begin
3893: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3894: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3895: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3898:
3891: l_delete_failed varchar2(10);
3892: begin
3893: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3894: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3895: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3898:
3899: hr_utility.set_location('Calling purge:'||p_business_area,903);
3892: begin
3893: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3894: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3895: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3898:
3899: hr_utility.set_location('Calling purge:'||p_business_area,903);
3900:
3893: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3894: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3895: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3898:
3899: hr_utility.set_location('Calling purge:'||p_business_area,903);
3900:
3901: pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3895: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3898:
3899: hr_utility.set_location('Calling purge:'||p_business_area,903);
3900:
3901: pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3902:
3903: hr_utility.set_location('Done with purge:'||p_business_area,903);
3899: hr_utility.set_location('Calling purge:'||p_business_area,903);
3900:
3901: pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3902:
3903: hr_utility.set_location('Done with purge:'||p_business_area,903);
3904:
3905: -- update the dml operation of those records which have already been
3906: -- taken care by gsp pre push code
3907:
3939:
3940: -- Create all derived factors first
3941:
3942: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLF') then
3943: hr_utility.set_location(' Calling create CLF rows ',999);
3944: BEN_PD_COPY_TO_BEN_ONE.create_CLF_rows(
3945: p_copy_entity_txn_id => p_copy_entity_txn_id
3946: ,p_effective_date => p_effective_date
3947: ,p_reuse_object_flag => 'Y'
3949: );
3950: end if;
3951: --
3952: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('HWF') then
3953: hr_utility.set_location(' Calling create HWF rows ',999);
3954: BEN_PD_COPY_TO_BEN_ONE.create_HWF_rows(
3955: p_copy_entity_txn_id => p_copy_entity_txn_id
3956: ,p_effective_date => p_effective_date
3957: ,p_reuse_object_flag => 'Y'
3959: );
3960: end if;
3961: --
3962: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('AGF') then
3963: hr_utility.set_location(' Calling create AGF rows ',999);
3964: BEN_PD_COPY_TO_BEN_ONE.create_AGF_rows(
3965: p_copy_entity_txn_id => p_copy_entity_txn_id
3966: ,p_effective_date => p_effective_date
3967: ,p_reuse_object_flag => 'Y'
3969: );
3970: end if;
3971: --
3972: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('LSF') then
3973: hr_utility.set_location(' Calling create LSF rows ',999);
3974: BEN_PD_COPY_TO_BEN_ONE.create_LSF_rows(
3975: p_copy_entity_txn_id => p_copy_entity_txn_id
3976: ,p_effective_date => p_effective_date
3977: ,p_reuse_object_flag => 'Y'
3979: );
3980: end if;
3981: --
3982: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('PFF') then
3983: hr_utility.set_location(' Calling create PFF rows ',999);
3984: BEN_PD_COPY_TO_BEN_ONE.create_PFF_rows(
3985: p_copy_entity_txn_id => p_copy_entity_txn_id
3986: ,p_effective_date => p_effective_date
3987: ,p_reuse_object_flag => 'Y'
3989: );
3990: end if;
3991: --
3992: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLA') then
3993: hr_utility.set_location(' Calling create CLA rows ',999);
3994: BEN_PD_COPY_TO_BEN_ONE.create_CLA_rows(
3995: p_copy_entity_txn_id => p_copy_entity_txn_id
3996: ,p_effective_date => p_effective_date
3997: ,p_reuse_object_flag => 'Y'
4003: -- Create ELP Row
4004: --
4005:
4006: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('ELP') then
4007: hr_utility.set_location(' Calling create ELP rows ',999);
4008: BEN_PD_COPY_TO_BEN_ONE.create_ELP_rows(
4009: p_copy_entity_txn_id => p_copy_entity_txn_id
4010: ,p_effective_date => p_effective_date
4011: ,p_reuse_object_flag => 'Y'
4016: --
4017: -- Create elig prf ben rows
4018: --
4019:
4020: hr_utility.set_location(' Calling create all elig prf ben rows',999);
4021: BEN_PD_COPY_TO_BEN_FOUR.create_all_elig_prf_ben_rows(
4022: p_copy_entity_txn_id => p_copy_entity_txn_id
4023: ,p_effective_date => p_effective_date
4024: ,p_reuse_object_flag => 'Y'
4027:
4028: --
4029: -- Update elig_prfl_id of staging records
4030: --
4031: hr_utility.set_location(' Update elig_prfl_id in staging area ',999);
4032: upd_stg_elig_prfl_id(
4033: p_copy_entity_txn_id => p_copy_entity_txn_id
4034: ,p_business_group_id => p_business_group_id
4035: ,p_effective_date => p_effective_date
4039: set dml_operation = 'DELETE'
4040: where cer.copy_entity_txn_id = p_copy_entity_txn_id
4041: and cer.dml_operation = 'GSPDEL';
4042:
4043: hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
4044: end cre_update_elig_prfl ;
4045:
4046:
4047: end pqh_gsp_stage_to_ben;