[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
2039: and copy_entity_result_id = r_CPP.INFORMATION252;
2040: else
2041: l_pl_id := r_CPP.INFORMATION261;
2042: end if;
2043: hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2044: hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2045: hr_utility.set_location('pl_id: '||l_pl_id,20);
2046: --Added by kgowripe for bug#3532412
2047: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2040: else
2041: l_pl_id := r_CPP.INFORMATION261;
2042: end if;
2043: hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2044: hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2045: hr_utility.set_location('pl_id: '||l_pl_id,20);
2046: --Added by kgowripe for bug#3532412
2047: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2048: SELECT information1
2041: l_pl_id := r_CPP.INFORMATION261;
2042: end if;
2043: hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2044: hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2045: hr_utility.set_location('pl_id: '||l_pl_id,20);
2046: --Added by kgowripe for bug#3532412
2047: if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2048: SELECT information1
2049: INTO l_corps_definition_id
2057: l_effective_date := r_cpp.information2;
2058: begin
2059: if l_plip_id is null and r_cpp.dml_operation = 'INSERT'
2060: and l_pgm_id is not null and l_pl_id is not null then
2061: hr_utility.set_location(' BEN_PLIP_F CREATE_PLAN_IN_PROGRAM ',20);
2062: BEN_PLAN_IN_PROGRAM_API.CREATE_PLAN_IN_PROGRAM(
2063: P_EFFECTIVE_DATE => l_effective_date
2064: ,P_BUSINESS_GROUP_ID => p_business_group_id
2065: ,P_ALWS_UNRSTRCTD_ENRT_FLAG => nvl(r_CPP.INFORMATION15,'N')
2121: ,P_EFFECTIVE_START_DATE => l_effective_start_date
2122: ,P_EFFECTIVE_END_DATE => l_effective_end_date
2123: ,P_OBJECT_VERSION_NUMBER => l_ovn
2124: );
2125: hr_utility.set_location('After plip ins'||l_plip_id,22);
2126: plip_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2127: p_plip_id => l_plip_id,
2128: p_pl_id => l_pl_id,
2129: p_plip_cer_id => r_CPP.copy_entity_result_id);
2126: plip_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2127: p_plip_id => l_plip_id,
2128: p_pl_id => l_pl_id,
2129: p_plip_cer_id => r_CPP.copy_entity_result_id);
2130: hr_utility.set_location('plip wrtback comp'||l_plip_id,222);
2131: if p_business_area = 'PQH_CORPS_TASK_LIST' then
2132: hr_utility.set_location('going for quota cr'||l_plip_id,222);
2133: pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2134: p_grade_id => r_cpp.information253,
2128: p_pl_id => l_pl_id,
2129: p_plip_cer_id => r_CPP.copy_entity_result_id);
2130: hr_utility.set_location('plip wrtback comp'||l_plip_id,222);
2131: if p_business_area = 'PQH_CORPS_TASK_LIST' then
2132: hr_utility.set_location('going for quota cr'||l_plip_id,222);
2133: pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2134: p_grade_id => r_cpp.information253,
2135: p_corps_definition_id => l_corps_definition_id,
2136: p_corps_extra_info_id => r_cpp.information290,
2142: end if;
2143: elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
2144: and l_ovn is not null and l_pgm_id is not null
2145: and l_pl_id is not null then
2146: hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
2147: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2148: if p_datetrack_mode <> 'CORRECTION' then
2149: l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2150: p_key_column_name => 'PLIP_ID',
2143: elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
2144: and l_ovn is not null and l_pgm_id is not null
2145: and l_pl_id is not null then
2146: hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
2147: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2148: if p_datetrack_mode <> 'CORRECTION' then
2149: l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2150: p_key_column_name => 'PLIP_ID',
2151: p_key_column_value => l_plip_id,
2149: l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2150: p_key_column_name => 'PLIP_ID',
2151: p_key_column_value => l_plip_id,
2152: p_effective_date => l_effective_date);
2153: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2154: else
2155: l_dt_mode := p_datetrack_mode;
2156: end if;
2157: l_db_ovn := get_ovn(p_table_name => 'BEN_PLIP_F',
2157: l_db_ovn := get_ovn(p_table_name => 'BEN_PLIP_F',
2158: p_key_column_name => 'PLIP_ID',
2159: p_key_column_value => l_plip_id,
2160: p_effective_date => l_effective_date);
2161: hr_utility.set_location(' ovn is '||l_db_ovn,30);
2162: if l_db_ovn <> l_ovn then
2163: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLIP');
2164: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2165: fnd_message.set_token('OBJECT ',l_object);
2205: P_MESSAGE_TEXT => l_message_text,
2206: p_effective_date => p_effective_date);
2207: end if;
2208: exception when others then
2209: hr_utility.set_location('issues in writing cpp, skipping'||l_proc,100);
2210: raise;
2211: end;
2212: end loop;
2213: hr_utility.set_location('leaving '||l_proc,100);
2209: hr_utility.set_location('issues in writing cpp, skipping'||l_proc,100);
2210: raise;
2211: end;
2212: end loop;
2213: hr_utility.set_location('leaving '||l_proc,100);
2214: exception
2215: when others then
2216: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2217: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2273: l_pt_cd varchar2(30);
2274: l_esd date;
2275: l_eed date;
2276: begin
2277: hr_utility.set_location('inside '||l_proc,10);
2278: for r_elp in c_elp loop
2279: l_elp_id := r_elp.information161;
2280: hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2281: l_effective_date := r_elp.information2;
2276: begin
2277: hr_utility.set_location('inside '||l_proc,10);
2278: for r_elp in c_elp loop
2279: l_elp_id := r_elp.information161;
2280: hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2281: l_effective_date := r_elp.information2;
2282: if r_elp.information277 is null and r_elp.information161 = l_old_crset_id then
2283: hr_utility.set_location('reusing earlier values ',20);
2284: -- reuse old values
2279: l_elp_id := r_elp.information161;
2280: hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2281: l_effective_date := r_elp.information2;
2282: if r_elp.information277 is null and r_elp.information161 = l_old_crset_id then
2283: hr_utility.set_location('reusing earlier values ',20);
2284: -- reuse old values
2285: l_elp_id := l_old_elp_id;
2286: l_elp_ovn := l_old_elp_ovn;
2287: else
2284: -- reuse old values
2285: l_elp_id := l_old_elp_id;
2286: l_elp_ovn := l_old_elp_ovn;
2287: else
2288: hr_utility.set_location('earlier values cannot be reused',20);
2289: -- new crset_id is to be entered
2290: l_elp_id := r_elp.information277;
2291: l_elp_ovn := r_elp.information265;
2292: l_old_elp_id := '';
2308: l_dml_operation := 'UPDATE';
2309: else
2310: l_dml_operation := '';
2311: end if;
2312: hr_utility.set_location('dml_operation is '||l_dml_operation,4);
2313: if r_elp.information232 is not null then
2314: l_loc_flag := 'Y';
2315: else
2316: l_loc_flag := 'N';
2355: else
2356: l_pr_flag := 'N';
2357: end if;
2358: if l_dml_operation = 'INSERT' then
2359: hr_utility.set_location(' BEN_ELIGY_PRFL_F CREATE_ELIGY_PROFILE ',20);
2360: begin
2361: BEN_ELIGY_PROFILE_API.CREATE_ELIGY_PROFILE(
2362: P_EFFECTIVE_DATE => l_effective_date
2363: ,P_BUSINESS_GROUP_ID => p_business_group_id
2382: );
2383: elp_writeback(p_crset_id => r_elp.information161,
2384: p_elp_id => l_elp_id,
2385: p_copy_entity_txn_id => p_copy_entity_txn_id);
2386: hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2387: if l_rl_flag = 'Y' then
2388: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2389: hr_utility.set_location('rule id is'||r_elp.information235,20);
2390: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2384: p_elp_id => l_elp_id,
2385: p_copy_entity_txn_id => p_copy_entity_txn_id);
2386: hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2387: if l_rl_flag = 'Y' then
2388: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2389: hr_utility.set_location('rule id is'||r_elp.information235,20);
2390: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2391: P_EFFECTIVE_DATE => l_effective_date
2392: ,P_BUSINESS_GROUP_ID => p_business_group_id
2385: p_copy_entity_txn_id => p_copy_entity_txn_id);
2386: hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2387: if l_rl_flag = 'Y' then
2388: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2389: hr_utility.set_location('rule id is'||r_elp.information235,20);
2390: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2391: P_EFFECTIVE_DATE => l_effective_date
2392: ,P_BUSINESS_GROUP_ID => p_business_group_id
2393: ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2400: ,P_OBJECT_VERSION_NUMBER => l_ovn
2401: );
2402: end if;
2403: if l_bu_flag = 'Y' then
2404: hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2405: hr_utility.set_location('bargaining unit cd is'||r_elp.information101,20);
2406: BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2407: P_EFFECTIVE_DATE => l_effective_date
2408: ,P_BUSINESS_GROUP_ID => p_business_group_id
2401: );
2402: end if;
2403: if l_bu_flag = 'Y' then
2404: hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2405: hr_utility.set_location('bargaining unit cd is'||r_elp.information101,20);
2406: BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2407: P_EFFECTIVE_DATE => l_effective_date
2408: ,P_BUSINESS_GROUP_ID => p_business_group_id
2409: ,P_BRGNG_UNIT_CD => r_elp.information101
2416: ,P_OBJECT_VERSION_NUMBER => l_ovn
2417: );
2418: end if;
2419: if l_fp_flag = 'Y' then
2420: hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2421: hr_utility.set_location('fulltime cd is'||r_elp.information102,20);
2422: BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2423: P_EFFECTIVE_DATE => l_effective_date
2424: ,P_BUSINESS_GROUP_ID => p_business_group_id
2417: );
2418: end if;
2419: if l_fp_flag = 'Y' then
2420: hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2421: hr_utility.set_location('fulltime cd is'||r_elp.information102,20);
2422: BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2423: P_EFFECTIVE_DATE => l_effective_date
2424: ,P_BUSINESS_GROUP_ID => p_business_group_id
2425: ,P_ELIGY_PRFL_ID => l_elp_id
2432: ,P_OBJECT_VERSION_NUMBER => l_ovn
2433: );
2434: end if;
2435: if l_pr_flag = 'Y' then
2436: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2437: hr_utility.set_location('event type is'||r_elp.information103,20);
2438: hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2439: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2440: P_EFFECTIVE_DATE => l_effective_date
2433: );
2434: end if;
2435: if l_pr_flag = 'Y' then
2436: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2437: hr_utility.set_location('event type is'||r_elp.information103,20);
2438: hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2439: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2440: P_EFFECTIVE_DATE => l_effective_date
2441: ,P_BUSINESS_GROUP_ID => p_business_group_id
2434: end if;
2435: if l_pr_flag = 'Y' then
2436: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2437: hr_utility.set_location('event type is'||r_elp.information103,20);
2438: hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2439: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2440: P_EFFECTIVE_DATE => l_effective_date
2441: ,P_BUSINESS_GROUP_ID => p_business_group_id
2442: ,P_ELIGY_PRFL_ID => l_elp_id
2451: );
2452: end if;
2453: if l_pt_flag = 'Y' then
2454: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2455: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2456: hr_utility.set_location('per type id is'||r_elp.information236,20);
2457: hr_utility.set_location('per type cd is'||l_pt_cd,20);
2458: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2459: P_EFFECTIVE_DATE => l_effective_date
2452: end if;
2453: if l_pt_flag = 'Y' then
2454: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2455: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2456: hr_utility.set_location('per type id is'||r_elp.information236,20);
2457: hr_utility.set_location('per type cd is'||l_pt_cd,20);
2458: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2459: P_EFFECTIVE_DATE => l_effective_date
2460: ,P_BUSINESS_GROUP_ID => p_business_group_id
2453: if l_pt_flag = 'Y' then
2454: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2455: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2456: hr_utility.set_location('per type id is'||r_elp.information236,20);
2457: hr_utility.set_location('per type cd is'||l_pt_cd,20);
2458: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2459: P_EFFECTIVE_DATE => l_effective_date
2460: ,P_BUSINESS_GROUP_ID => p_business_group_id
2461: ,P_ELIGY_PRFL_ID => l_elp_id
2469: ,P_OBJECT_VERSION_NUMBER => l_ovn
2470: );
2471: end if;
2472: if l_sa_flag = 'Y' then
2473: hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2474: hr_utility.set_location('serv area id is'||r_elp.information237,20);
2475: BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2476: P_EFFECTIVE_DATE => l_effective_date
2477: ,P_BUSINESS_GROUP_ID => p_business_group_id
2470: );
2471: end if;
2472: if l_sa_flag = 'Y' then
2473: hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2474: hr_utility.set_location('serv area id is'||r_elp.information237,20);
2475: BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2476: P_EFFECTIVE_DATE => l_effective_date
2477: ,P_BUSINESS_GROUP_ID => p_business_group_id
2478: ,P_ELIGY_PRFL_ID => l_elp_id
2485: ,P_OBJECT_VERSION_NUMBER => l_ovn
2486: );
2487: end if;
2488: if l_loc_flag = 'Y' then
2489: hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2490: hr_utility.set_location('location id is'||r_elp.information232,20);
2491: BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2492: P_EFFECTIVE_DATE => l_effective_date
2493: ,P_BUSINESS_GROUP_ID => p_business_group_id
2486: );
2487: end if;
2488: if l_loc_flag = 'Y' then
2489: hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2490: hr_utility.set_location('location id is'||r_elp.information232,20);
2491: BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2492: P_EFFECTIVE_DATE => l_effective_date
2493: ,P_BUSINESS_GROUP_ID => p_business_group_id
2494: ,P_ELIGY_PRFL_ID => l_elp_id
2501: ,P_OBJECT_VERSION_NUMBER => l_ovn
2502: );
2503: end if;
2504: if l_org_flag = 'Y' then
2505: hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2506: hr_utility.set_location('org id is'||r_elp.information234,20);
2507: BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2508: P_EFFECTIVE_DATE => l_effective_date
2509: ,P_BUSINESS_GROUP_ID => p_business_group_id
2502: );
2503: end if;
2504: if l_org_flag = 'Y' then
2505: hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2506: hr_utility.set_location('org id is'||r_elp.information234,20);
2507: BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2508: P_EFFECTIVE_DATE => l_effective_date
2509: ,P_BUSINESS_GROUP_ID => p_business_group_id
2510: ,P_ELIGY_PRFL_ID => l_elp_id
2517: ,P_OBJECT_VERSION_NUMBER => l_ovn
2518: );
2519: end if;
2520: if l_job_flag = 'Y' then
2521: hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2522: hr_utility.set_location('org id is'||r_elp.information233,20);
2523: BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2524: P_EFFECTIVE_DATE => l_effective_date
2525: ,P_BUSINESS_GROUP_ID => p_business_group_id
2518: );
2519: end if;
2520: if l_job_flag = 'Y' then
2521: hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2522: hr_utility.set_location('org id is'||r_elp.information233,20);
2523: BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2524: P_EFFECTIVE_DATE => l_effective_date
2525: ,P_BUSINESS_GROUP_ID => p_business_group_id
2526: ,P_ELIGY_PRFL_ID => l_elp_id
2533: ,P_OBJECT_VERSION_NUMBER => l_ovn
2534: );
2535: end if;
2536: exception when others then
2537: hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2538: raise;
2539: end;
2540: hr_utility.set_location('After plsql table ',222);
2541: elsif l_dml_operation = 'UPDATE' then
2536: exception when others then
2537: hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2538: raise;
2539: end;
2540: hr_utility.set_location('After plsql table ',222);
2541: elsif l_dml_operation = 'UPDATE' then
2542: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2543: if p_datetrack_mode <> 'CORRECTION' then
2544: l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2538: raise;
2539: end;
2540: hr_utility.set_location('After plsql table ',222);
2541: elsif l_dml_operation = 'UPDATE' then
2542: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2543: if p_datetrack_mode <> 'CORRECTION' then
2544: l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2545: p_key_column_name => 'ELIGY_PRFL_ID',
2546: p_key_column_value => l_elp_id,
2544: l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2545: p_key_column_name => 'ELIGY_PRFL_ID',
2546: p_key_column_value => l_elp_id,
2547: p_effective_date => l_effective_date);
2548: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2549: else
2550: l_dt_mode := p_datetrack_mode;
2551: end if;
2552: l_db_ovn := get_ovn(p_table_name => 'BEN_ELIGY_PRFL_F',
2552: l_db_ovn := get_ovn(p_table_name => 'BEN_ELIGY_PRFL_F',
2553: p_key_column_name => 'ELIGY_PRFL_ID',
2554: p_key_column_value => l_elp_id,
2555: p_effective_date => l_effective_date);
2556: hr_utility.set_location(' ovn is '||l_db_ovn,30);
2557: if l_db_ovn <> l_ovn then
2558: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','ELP');
2559: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2560: fnd_message.set_token('OBJECT ',l_object);
2560: fnd_message.set_token('OBJECT ',l_object);
2561: fnd_message.set_token('OBJECT_NAME ',r_ELP.INFORMATION151);
2562: fnd_message.raise_error;
2563: else
2564: hr_utility.set_location(' BEN_ELIGY_PRFL_F UPDATE_ELIGY_PROFILE ',30);
2565: begin
2566: BEN_ELIGY_PROFILE_API.UPDATE_ELIGY_PROFILE(
2567: P_EFFECTIVE_DATE => l_effective_date
2568: ,P_BUSINESS_GROUP_ID => p_business_group_id
2583: ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
2584: ,P_DATETRACK_MODE => l_dt_mode
2585: );
2586: exception when others then
2587: hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2588: raise;
2589: end;
2590: end if;
2591: if l_rl_flag = 'Y' and l_old_rl_flag is null and r_elp.information225 is null then
2588: raise;
2589: end;
2590: end if;
2591: if l_rl_flag = 'Y' and l_old_rl_flag is null and r_elp.information225 is null then
2592: hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2593: BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2594: P_EFFECTIVE_DATE => l_effective_date
2595: ,P_BUSINESS_GROUP_ID => p_business_group_id
2596: ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2607: p_crit_type => 'RL',
2608: p_effective_date => l_effective_date);
2609: end if;
2610: if l_bu_flag = 'Y' and l_old_bu_flag is null and r_elp.information228 is null then
2611: hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2612: BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2613: P_EFFECTIVE_DATE => l_effective_date
2614: ,P_BUSINESS_GROUP_ID => p_business_group_id
2615: ,P_BRGNG_UNIT_CD => r_elp.information101
2626: p_crit_type => 'BU',
2627: p_effective_date => l_effective_date);
2628: end if;
2629: if l_fp_flag = 'Y' and l_old_fp_flag is null and r_elp.information229 is null then
2630: hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2631: BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2632: P_EFFECTIVE_DATE => l_effective_date
2633: ,P_BUSINESS_GROUP_ID => p_business_group_id
2634: ,P_ELIGY_PRFL_ID => l_elp_id
2645: p_crit_type => 'FP',
2646: p_effective_date => l_effective_date);
2647: end if;
2648: if l_pr_flag = 'Y' and l_old_pr_flag is null and r_elp.information230 is null then
2649: hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2650: BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2651: P_EFFECTIVE_DATE => l_effective_date
2652: ,P_BUSINESS_GROUP_ID => p_business_group_id
2653: ,P_ELIGY_PRFL_ID => l_elp_id
2666: p_effective_date => l_effective_date);
2667: end if;
2668: if l_pt_flag = 'Y' and l_old_pt_flag is null and r_elp.information226 is null then
2669: l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2670: hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2671: BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2672: P_EFFECTIVE_DATE => l_effective_date
2673: ,P_BUSINESS_GROUP_ID => p_business_group_id
2674: ,P_ELIGY_PRFL_ID => l_elp_id
2686: p_crit_type => 'PT',
2687: p_effective_date => l_effective_date);
2688: end if;
2689: if l_sa_flag = 'Y' and l_old_sa_flag is null and r_elp.information227 is null then
2690: hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2691: BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2692: P_EFFECTIVE_DATE => l_effective_date
2693: ,P_BUSINESS_GROUP_ID => p_business_group_id
2694: ,P_ELIGY_PRFL_ID => l_elp_id
2705: p_crit_type => 'SA',
2706: p_effective_date => l_effective_date);
2707: end if;
2708: if l_loc_flag = 'Y' and l_old_loc_flag is null and r_elp.information222 is null then
2709: hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2710: BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2711: P_EFFECTIVE_DATE => l_effective_date
2712: ,P_BUSINESS_GROUP_ID => p_business_group_id
2713: ,P_ELIGY_PRFL_ID => l_elp_id
2724: p_crit_type => 'LOC',
2725: p_effective_date => l_effective_date);
2726: end if;
2727: if l_org_flag = 'Y' and l_old_org_flag is null and r_elp.information224 is null then
2728: hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2729: BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2730: P_EFFECTIVE_DATE => l_effective_date
2731: ,P_BUSINESS_GROUP_ID => p_business_group_id
2732: ,P_ELIGY_PRFL_ID => l_elp_id
2743: p_crit_type => 'ORG',
2744: p_effective_date => l_effective_date);
2745: end if;
2746: if l_job_flag = 'Y' and l_old_job_flag is null and r_elp.information223 is null then
2747: hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2748: BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2749: P_EFFECTIVE_DATE => l_effective_date
2750: ,P_BUSINESS_GROUP_ID => p_business_group_id
2751: ,P_ELIGY_PRFL_ID => l_elp_id
2787: l_old_loc_flag := l_loc_flag;
2788: l_old_org_flag := l_org_flag;
2789: l_old_job_flag := l_job_flag;
2790: end loop;
2791: hr_utility.set_location('leaving '||l_proc,100);
2792: exception
2793: when others then
2794: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2795: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2826: l_epa_id number;
2827: l_pk number;
2828: l_tab varchar2(30);
2829: begin
2830: hr_utility.set_location('inside '||l_proc,10);
2831: for r_cep in c_cep loop
2832: l_cep_id := r_cep.information1;
2833: l_ovn := r_cep.information265;
2834: hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2830: hr_utility.set_location('inside '||l_proc,10);
2831: for r_cep in c_cep loop
2832: l_cep_id := r_cep.information1;
2833: l_ovn := r_cep.information265;
2834: hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2835: if r_cep.gs_mirror_src_entity_result_id is not null then
2836: select information1,table_alias
2837: into l_pk, l_tab
2838: from ben_copy_entity_results
2836: select information1,table_alias
2837: into l_pk, l_tab
2838: from ben_copy_entity_results
2839: where copy_entity_result_id = r_cep.gs_mirror_src_entity_result_id;
2840: hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
2841: if l_tab ='EPA' then
2842: l_epa_id := l_pk;
2843: else
2844: l_epa_id := null;
2848: end if;
2849: l_effective_date := r_cep.information2;
2850: begin
2851: 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
2852: hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F CREATE_PRTN_ELIG_PRFL ',20);
2853: BEN_PRTN_ELIG_PRFL_API.CREATE_PRTN_ELIG_PRFL(
2854: P_EFFECTIVE_DATE => l_effective_date
2855: ,P_BUSINESS_GROUP_ID => p_business_group_id
2856: ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2863: ,P_EFFECTIVE_END_DATE => l_effective_end_date
2864: ,P_OBJECT_VERSION_NUMBER => l_ovn
2865: );
2866: 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
2867: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2868: if p_datetrack_mode <> 'CORRECTION' then
2869: l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2870: p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2871: p_key_column_value => l_cep_id,
2869: l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2870: p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2871: p_key_column_value => l_cep_id,
2872: p_effective_date => l_effective_date);
2873: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2874: else
2875: l_dt_mode := p_datetrack_mode;
2876: end if;
2877: l_db_ovn := get_ovn(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2877: l_db_ovn := get_ovn(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2878: p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2879: p_key_column_value => l_cep_id,
2880: p_effective_date => l_effective_date);
2881: hr_utility.set_location(' ovn is '||l_db_ovn,30);
2882: if l_db_ovn <> l_ovn then
2883: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','CEP');
2884: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2885: fnd_message.set_token('OBJECT ',l_object);
2885: fnd_message.set_token('OBJECT ',l_object);
2886: fnd_message.set_token('OBJECT_NAME ','CEP :'||l_cep_id);
2887: fnd_message.raise_error;
2888: else
2889: hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F UPDATE_PRTN_ELIG_PRFL ',30);
2890: BEN_PRTN_ELIG_PRFL_API.UPDATE_PRTN_ELIG_PRFL(
2891: P_EFFECTIVE_DATE => l_effective_date
2892: ,P_BUSINESS_GROUP_ID => p_business_group_id
2893: ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2914: P_MESSAGE_TEXT => l_message_text,
2915: p_effective_date => p_effective_date);
2916: end if;
2917: exception when others then
2918: hr_utility.set_location('issues in writing cep, skipping'||l_proc,100);
2919: raise;
2920: end;
2921: end loop;
2922: hr_utility.set_location('leaving '||l_proc,100);
2918: hr_utility.set_location('issues in writing cep, skipping'||l_proc,100);
2919: raise;
2920: end;
2921: end loop;
2922: hr_utility.set_location('leaving '||l_proc,100);
2923: exception
2924: when others then
2925: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2926: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2953: l_effective_start_date date ;
2954: l_effective_end_date date ;
2955: l_effective_date date;
2956: begin
2957: hr_utility.set_location('inside '||l_proc,10);
2958: for r_abr in c_abr loop
2959: l_pl_id := null;
2960: l_opt_id := null;
2961: l_abr_id := r_abr.information1;
2958: for r_abr in c_abr loop
2959: l_pl_id := null;
2960: l_opt_id := null;
2961: l_abr_id := r_abr.information1;
2962: hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2963: l_effective_date := r_abr.information2;
2964: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2965: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2966: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2960: l_opt_id := null;
2961: l_abr_id := r_abr.information1;
2962: hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2963: l_effective_date := r_abr.information2;
2964: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2965: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2966: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2967: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2968: begin
2961: l_abr_id := r_abr.information1;
2962: hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2963: l_effective_date := r_abr.information2;
2964: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2965: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2966: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2967: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2968: begin
2969: select information1
2963: l_effective_date := r_abr.information2;
2964: hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2965: hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2966: if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2967: hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2968: begin
2969: select information1
2970: into l_pl_id
2971: from ben_copy_entity_results
2978: l_pl_id := r_ABR.INFORMATION261;
2979: elsif r_ABR.INFORMATION247 is not null then
2980: l_opt_id := r_ABR.INFORMATION247;
2981: elsif r_ABR.INFORMATION278 is not null and r_ABR.INFORMATION247 is null then
2982: hr_utility.set_location('opt_cer_id :'||r_ABR.INFORMATION278,3);
2983: begin
2984: select information1
2985: into l_opt_id
2986: from ben_copy_entity_results
2989: when others then
2990: l_opt_id := '';
2991: end;
2992: else
2993: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2994: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2995: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2996: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2997: end if;
2990: l_opt_id := '';
2991: end;
2992: else
2993: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2994: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2995: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2996: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2997: end if;
2998: /*
2991: end;
2992: else
2993: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2994: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2995: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2996: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2997: end if;
2998: /*
2999: l_message_text := 'oper is'||r_ABR.dml_operation
2992: else
2993: hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2994: hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2995: hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2996: hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2997: end if;
2998: /*
2999: l_message_text := 'oper is'||r_ABR.dml_operation
3000: ||' pl id is '||r_ABR.INFORMATION261
3012: p_effective_date => p_effective_date);
3013: */
3014: begin
3015: 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
3016: hr_utility.set_location(' BEN_ACTY_BASE_RT_F CREATE_ACTY_BASE_RATE ',20);
3017: BEN_ACTY_BASE_RATE_API.CREATE_ACTY_BASE_RATE(
3018: P_EFFECTIVE_DATE => l_effective_date
3019: ,P_BUSINESS_GROUP_ID => p_business_group_id
3020: ,P_ABV_MX_ELCN_VAL_ALWD_FLAG => nvl(r_ABR.INFORMATION27,'N')
3109: ,P_EFFECTIVE_START_DATE => l_effective_start_date
3110: ,P_EFFECTIVE_END_DATE => l_effective_end_date
3111: ,P_OBJECT_VERSION_NUMBER => l_ovn
3112: );
3113: hr_utility.set_location('After plsql table ',222);
3114: update ben_copy_entity_results
3115: set information1 = l_abr_id
3116: where copy_entity_result_id = r_abr.copy_entity_result_id;
3117: hr_utility.set_location('abr id updated '||l_abr_id,222);
3113: hr_utility.set_location('After plsql table ',222);
3114: update ben_copy_entity_results
3115: set information1 = l_abr_id
3116: where copy_entity_result_id = r_abr.copy_entity_result_id;
3117: hr_utility.set_location('abr id updated '||l_abr_id,222);
3118: else
3119: l_message_text := 'invalid oper'||r_ABR.dml_operation
3120: ||' pl id is '||r_ABR.INFORMATION261
3121: ||' abr id is '||l_abr_id
3131: P_MESSAGE_TEXT => l_message_text,
3132: p_effective_date => p_effective_date);
3133: end if;
3134: exception when others then
3135: hr_utility.set_location('issues in writing abr, skipping'||l_proc,100);
3136: raise;
3137: end;
3138: end loop;
3139: hr_utility.set_location('leaving '||l_proc,100);
3135: hr_utility.set_location('issues in writing abr, skipping'||l_proc,100);
3136: raise;
3137: end;
3138: end loop;
3139: hr_utility.set_location('leaving '||l_proc,100);
3140: exception
3141: when others then
3142: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3143: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3174: l_pl_id number;
3175: l_oipl_id number;
3176: l_pgm_id number;
3177: begin
3178: hr_utility.set_location('inside '||l_proc,10);
3179: for r_epa in c_epa loop
3180: l_epa_id := r_epa.information1;
3181: hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3182: hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3177: begin
3178: hr_utility.set_location('inside '||l_proc,10);
3179: for r_epa in c_epa loop
3180: l_epa_id := r_epa.information1;
3181: hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3182: hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3183: if r_epa.gs_mirror_src_entity_result_id is not null then
3184: begin
3185: select information1,table_alias
3178: hr_utility.set_location('inside '||l_proc,10);
3179: for r_epa in c_epa loop
3180: l_epa_id := r_epa.information1;
3181: hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3182: hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3183: if r_epa.gs_mirror_src_entity_result_id is not null then
3184: begin
3185: select information1,table_alias
3186: into l_pk, l_tab
3189: exception
3190: when others then
3191: raise;
3192: end;
3193: hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
3194: if l_tab ='PGM' then
3195: l_pgm_id := l_pk;
3196: l_plip_id := null;
3197: l_oipl_id := null;
3222: end if;
3223: l_effective_date := r_epa.information2;
3224: begin
3225: if l_epa_id is null and l_pk is not null and r_epa.dml_operation = 'INSERT' then
3226: hr_utility.set_location(' BEN_PRTN_ELIG_F CREATE_PARTICIPATION_ELIG ',20);
3227: BEN_PARTICIPATION_ELIG_API.CREATE_PARTICIPATION_ELIG(
3228: P_EFFECTIVE_DATE => l_effective_date
3229: ,P_BUSINESS_GROUP_ID => p_business_group_id
3230: ,P_MX_POE_APLS_CD => r_EPA.INFORMATION17
3270: P_MESSAGE_TEXT => l_message_text,
3271: p_effective_date => p_effective_date);
3272: end if;
3273: exception when others then
3274: hr_utility.set_location('issues in writing epa, skipping'||l_proc,100);
3275: raise;
3276: end;
3277: end loop;
3278: hr_utility.set_location('leaving '||l_proc,100);
3274: hr_utility.set_location('issues in writing epa, skipping'||l_proc,100);
3275: raise;
3276: end;
3277: end loop;
3278: hr_utility.set_location('leaving '||l_proc,100);
3279: exception
3280: when others then
3281: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3282: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3326: l_crr_name varchar2(240);
3327: l_dml_operation varchar2(30);
3328: l_dt_mode varchar2(30);
3329: begin
3330: hr_utility.set_location('inside '||l_proc,10);
3331: for crr_rec in c_crr loop
3332: l_effective_date := crr_rec.information2;
3333: if crr_rec.information278 is null then
3334: hr_utility.set_location('new variable rate is being created'||l_proc,10);
3330: hr_utility.set_location('inside '||l_proc,10);
3331: for crr_rec in c_crr loop
3332: l_effective_date := crr_rec.information2;
3333: if crr_rec.information278 is null then
3334: hr_utility.set_location('new variable rate is being created'||l_proc,10);
3335: if (crr_rec.information230 is null or crr_rec.information230 = l_old_grd_cer_id)
3336: and (crr_rec.information169 is null or crr_rec.information169 = l_old_pnt_cer_id)
3337: and crr_rec.information160 = l_old_crset_id
3338: and l_old_crr_id is not null then
3335: if (crr_rec.information230 is null or crr_rec.information230 = l_old_grd_cer_id)
3336: and (crr_rec.information169 is null or crr_rec.information169 = l_old_pnt_cer_id)
3337: and crr_rec.information160 = l_old_crset_id
3338: and l_old_crr_id is not null then
3339: hr_utility.set_location('reusing prev row pk and ovn',16);
3340: l_crr_id := l_old_crr_id; -- previous row created id can be used
3341: l_crr_ovn := l_old_crr_ovn;
3342: l_abr_id := l_old_abr_id;
3343: l_crr_name := l_old_crr_name;
3341: l_crr_ovn := l_old_crr_ovn;
3342: l_abr_id := l_old_abr_id;
3343: l_crr_name := l_old_crr_name;
3344: else
3345: hr_utility.set_location('nothing to reuse'||l_proc,10);
3346: l_crr_id := crr_rec.information278;
3347: l_crr_ovn := crr_rec.information298;
3348: l_abr_id := '';
3349: l_avr_num := '';
3352: p_grade_cer_id => crr_rec.information230,
3353: p_copy_entity_txn_id => p_copy_entity_txn_id);
3354: end if;
3355: else
3356: hr_utility.set_location('existing vpf is being updated'||l_proc,10);
3357: l_crr_id := crr_rec.information278;
3358: l_crr_ovn := crr_rec.information298;
3359: l_abr_id := '';
3360: l_avr_num := '';
3368: elsif crr_rec.dml_operation = 'UPDATE' then
3369: l_dml_operation := 'UPDATE';
3370: end if;
3371: if l_abr_id is null and crr_rec.information161 is not null then
3372: hr_utility.set_location('abr_id is null ,getting it'||l_proc,10);
3373: begin
3374: select information1
3375: into l_abr_id
3376: from ben_copy_entity_results
3377: where copy_entity_txn_id = p_copy_entity_txn_id
3378: and copy_entity_result_id = crr_rec.information161;
3379: exception
3380: when no_data_found then
3381: hr_utility.set_location('abr id not found'||l_proc,100);
3382: when others then
3383: hr_utility.set_location('issues in getting abr'||l_proc,100);
3384: raise;
3385: end;
3379: exception
3380: when no_data_found then
3381: hr_utility.set_location('abr id not found'||l_proc,100);
3382: when others then
3383: hr_utility.set_location('issues in getting abr'||l_proc,100);
3384: raise;
3385: end;
3386: else
3387: hr_utility.set_location('abr_id is '||l_abr_id,10);
3383: hr_utility.set_location('issues in getting abr'||l_proc,100);
3384: raise;
3385: end;
3386: else
3387: hr_utility.set_location('abr_id is '||l_abr_id,10);
3388: end if;
3389: 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
3390: begin
3391: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
3387: hr_utility.set_location('abr_id is '||l_abr_id,10);
3388: end if;
3389: 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
3390: begin
3391: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
3392: BEN_VRBL_RATE_PROFILE_API.CREATE_VRBL_RATE_PROFILE(
3393: P_EFFECTIVE_DATE => l_effective_date
3394: ,P_BUSINESS_GROUP_ID => p_business_group_id
3395: ,P_ACTY_TYP_CD => 'GSPSA'
3464: ,P_EFFECTIVE_START_DATE => l_esd
3465: ,P_EFFECTIVE_END_DATE => l_eed
3466: ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3467: );
3468: hr_utility.set_location('after vpf insert ',222);
3469: begin
3470: if l_avr_num is null then
3471: hr_utility.set_location('1st crr for abr',222);
3472: begin
3467: );
3468: hr_utility.set_location('after vpf insert ',222);
3469: begin
3470: if l_avr_num is null then
3471: hr_utility.set_location('1st crr for abr',222);
3472: begin
3473: select null
3474: into dummy
3475: from ben_acty_base_rt_f
3478: and l_effective_date between effective_start_date
3479: and effective_end_date;
3480: exception
3481: when no_data_found then
3482: hr_utility.set_location('abr has vrbl flag No',222);
3483: begin
3484: update ben_acty_base_rt_f
3485: set USES_VARBL_RT_FLAG = 'Y'
3486: where acty_base_rt_id = l_abr_id;
3483: begin
3484: update ben_acty_base_rt_f
3485: set USES_VARBL_RT_FLAG = 'Y'
3486: where acty_base_rt_id = l_abr_id;
3487: hr_utility.set_location('updated to Yes',223);
3488: exception
3489: when others then
3490: hr_utility.set_location('issues in updating abr flag to Y',225);
3491: raise;
3486: where acty_base_rt_id = l_abr_id;
3487: hr_utility.set_location('updated to Yes',223);
3488: exception
3489: when others then
3490: hr_utility.set_location('issues in updating abr flag to Y',225);
3491: raise;
3492: end;
3493: when others then
3494: hr_utility.set_location('issues in gettting abr row ',226);
3490: hr_utility.set_location('issues in updating abr flag to Y',225);
3491: raise;
3492: end;
3493: when others then
3494: hr_utility.set_location('issues in gettting abr row ',226);
3495: raise;
3496: end;
3497: select nvl(max(ordr_num),0) + 1
3498: into l_avr_num
3500: where acty_base_rt_id = l_abr_id;
3501: else
3502: l_avr_num := l_avr_num + 1;
3503: end if;
3504: hr_utility.set_location(' BEN_ACTY_VRBL_RT_F CREATE_ACTY_VRBL_RATE ',20);
3505: BEN_ACTY_VRBL_RATE_API.CREATE_ACTY_VRBL_RATE(
3506: P_EFFECTIVE_DATE => l_effective_date
3507: ,P_BUSINESS_GROUP_ID => p_business_group_id
3508: ,P_ACTY_BASE_RT_ID => l_abr_id
3513: ,P_EFFECTIVE_END_DATE => l_eed
3514: ,P_OBJECT_VERSION_NUMBER => l_ovn
3515: );
3516: exception when others then
3517: hr_utility.set_location('issues in writing avr'||l_proc,100);
3518: raise;
3519: end;
3520: if crr_rec.information279 is null and crr_rec.information160 is not null then
3521: -- elp id is null for crrate while crset id is there
3547: P_MESSAGE_TEXT => l_message_text,
3548: p_effective_date => p_effective_date);
3549: else
3550: begin
3551: hr_utility.set_location(' BEN_VRBL_RT_ELIG_PRFL_F CREATE_VRBL_RT_ELIG_PRFL ',20);
3552: BEN_VRBL_RT_ELIG_PRFL_API.CREATE_VRBL_RT_ELIG_PRFL(
3553: P_EFFECTIVE_DATE => p_effective_date -- vep will be created as of Grade ladder effdt
3554: ,P_BUSINESS_GROUP_ID => p_business_group_id
3555: ,P_ELIGY_PRFL_ID => l_elp_id
3577: raise;
3578: end;
3579: end if;
3580: exception when others then
3581: hr_utility.set_location('issues in writing var'||l_proc,100);
3582: raise;
3583: end;
3584: elsif l_dml_operation = 'UPDATE'
3585: and l_abr_id is not null
3584: elsif l_dml_operation = 'UPDATE'
3585: and l_abr_id is not null
3586: and l_crr_id is not null
3587: and l_crr_ovn is not null then
3588: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
3589: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
3590: --if p_datetrack_mode <> 'CORRECTION' then /* Commented out to fix Bug:3964291 */
3591: l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3592: p_key_column_name => 'VRBL_RT_PRFL_ID',
3585: and l_abr_id is not null
3586: and l_crr_id is not null
3587: and l_crr_ovn is not null then
3588: hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
3589: hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
3590: --if p_datetrack_mode <> 'CORRECTION' then /* Commented out to fix Bug:3964291 */
3591: l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3592: p_key_column_name => 'VRBL_RT_PRFL_ID',
3593: p_key_column_value => l_crr_id,
3591: l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3592: p_key_column_name => 'VRBL_RT_PRFL_ID',
3593: p_key_column_value => l_crr_id,
3594: p_effective_date => l_effective_date);
3595: hr_utility.set_location(' dt mode is '||l_dt_mode,30);
3596: /*else
3597: l_dt_mode := p_datetrack_mode;
3598: end if;*/
3599: l_db_ovn := get_ovn(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3599: l_db_ovn := get_ovn(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3600: p_key_column_name => 'VRBL_RT_PRFL_ID',
3601: p_key_column_value => l_crr_id,
3602: p_effective_date => l_effective_date);
3603: hr_utility.set_location(' ovn is '||l_db_ovn,30);
3604: if l_db_ovn <> l_crr_ovn then
3605: l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','VPF');
3606: fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
3607: fnd_message.set_token('OBJECT ',l_object);
3618: ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3619: ,P_DATETRACK_MODE => l_dt_mode
3620: );
3621: exception when others then
3622: hr_utility.set_location('issues in updating var'||l_proc,100);
3623: raise;
3624: end;
3625: end if;
3626: else
3646: l_old_grd_cer_id := crr_rec.information230;
3647: l_old_pnt_cer_id := crr_rec.information169;
3648: l_old_crset_id := crr_rec.information160;
3649: end loop;
3650: hr_utility.set_location('leaving '||l_proc,100);
3651: exception
3652: when others then
3653: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3654: (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3856: ,p_effective_date date
3857: )
3858: is
3859: begin
3860: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3861:
3862: update ben_copy_entity_results cer
3863: set information263 =
3864: ( select ELIGY_PRFL_ID
3878: and elp.name = cer.information5
3879: and p_effective_date between
3880: elp.effective_start_date and elp.effective_end_date);
3881:
3882: hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
3883: hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3884: end upd_stg_elig_prfl_id ;
3885:
3886: procedure cre_update_elig_prfl(
3879: and p_effective_date between
3880: elp.effective_start_date and elp.effective_end_date);
3881:
3882: hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
3883: hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3884: end upd_stg_elig_prfl_id ;
3885:
3886: procedure cre_update_elig_prfl(
3887: p_copy_entity_txn_id in number
3890: ,p_business_area in varchar2 default 'PQH_GSP_TASK_LIST')
3891: is
3892: l_delete_failed varchar2(10);
3893: begin
3894: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3895: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3896: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3891: is
3892: l_delete_failed varchar2(10);
3893: begin
3894: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3895: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3896: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3899:
3892: l_delete_failed varchar2(10);
3893: begin
3894: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3895: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3896: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3899:
3900: hr_utility.set_location('Calling purge:'||p_business_area,903);
3893: begin
3894: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3895: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3896: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3899:
3900: hr_utility.set_location('Calling purge:'||p_business_area,903);
3901:
3894: hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3895: hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3896: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3899:
3900: hr_utility.set_location('Calling purge:'||p_business_area,903);
3901:
3902: pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3896: hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897: hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898: hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3899:
3900: hr_utility.set_location('Calling purge:'||p_business_area,903);
3901:
3902: pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3903:
3904: hr_utility.set_location('Done with purge:'||p_business_area,903);
3900: hr_utility.set_location('Calling purge:'||p_business_area,903);
3901:
3902: pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3903:
3904: hr_utility.set_location('Done with purge:'||p_business_area,903);
3905:
3906: -- update the dml operation of those records which have already been
3907: -- taken care by gsp pre push code
3908:
3940:
3941: -- Create all derived factors first
3942:
3943: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLF') then
3944: hr_utility.set_location(' Calling create CLF rows ',999);
3945: BEN_PD_COPY_TO_BEN_ONE.create_CLF_rows(
3946: p_copy_entity_txn_id => p_copy_entity_txn_id
3947: ,p_effective_date => p_effective_date
3948: ,p_reuse_object_flag => 'Y'
3950: );
3951: end if;
3952: --
3953: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('HWF') then
3954: hr_utility.set_location(' Calling create HWF rows ',999);
3955: BEN_PD_COPY_TO_BEN_ONE.create_HWF_rows(
3956: p_copy_entity_txn_id => p_copy_entity_txn_id
3957: ,p_effective_date => p_effective_date
3958: ,p_reuse_object_flag => 'Y'
3960: );
3961: end if;
3962: --
3963: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('AGF') then
3964: hr_utility.set_location(' Calling create AGF rows ',999);
3965: BEN_PD_COPY_TO_BEN_ONE.create_AGF_rows(
3966: p_copy_entity_txn_id => p_copy_entity_txn_id
3967: ,p_effective_date => p_effective_date
3968: ,p_reuse_object_flag => 'Y'
3970: );
3971: end if;
3972: --
3973: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('LSF') then
3974: hr_utility.set_location(' Calling create LSF rows ',999);
3975: BEN_PD_COPY_TO_BEN_ONE.create_LSF_rows(
3976: p_copy_entity_txn_id => p_copy_entity_txn_id
3977: ,p_effective_date => p_effective_date
3978: ,p_reuse_object_flag => 'Y'
3980: );
3981: end if;
3982: --
3983: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('PFF') then
3984: hr_utility.set_location(' Calling create PFF rows ',999);
3985: BEN_PD_COPY_TO_BEN_ONE.create_PFF_rows(
3986: p_copy_entity_txn_id => p_copy_entity_txn_id
3987: ,p_effective_date => p_effective_date
3988: ,p_reuse_object_flag => 'Y'
3990: );
3991: end if;
3992: --
3993: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLA') then
3994: hr_utility.set_location(' Calling create CLA rows ',999);
3995: BEN_PD_COPY_TO_BEN_ONE.create_CLA_rows(
3996: p_copy_entity_txn_id => p_copy_entity_txn_id
3997: ,p_effective_date => p_effective_date
3998: ,p_reuse_object_flag => 'Y'
4004: -- Create ELP Row
4005: --
4006:
4007: if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('ELP') then
4008: hr_utility.set_location(' Calling create ELP rows ',999);
4009: BEN_PD_COPY_TO_BEN_ONE.create_ELP_rows(
4010: p_copy_entity_txn_id => p_copy_entity_txn_id
4011: ,p_effective_date => p_effective_date
4012: ,p_reuse_object_flag => 'Y'
4017: --
4018: -- Create elig prf ben rows
4019: --
4020:
4021: hr_utility.set_location(' Calling create all elig prf ben rows',999);
4022: BEN_PD_COPY_TO_BEN_FOUR.create_all_elig_prf_ben_rows(
4023: p_copy_entity_txn_id => p_copy_entity_txn_id
4024: ,p_effective_date => p_effective_date
4025: ,p_reuse_object_flag => 'Y'
4028:
4029: --
4030: -- Update elig_prfl_id of staging records
4031: --
4032: hr_utility.set_location(' Update elig_prfl_id in staging area ',999);
4033: upd_stg_elig_prfl_id(
4034: p_copy_entity_txn_id => p_copy_entity_txn_id
4035: ,p_business_group_id => p_business_group_id
4036: ,p_effective_date => p_effective_date
4040: set dml_operation = 'DELETE'
4041: where cer.copy_entity_txn_id = p_copy_entity_txn_id
4042: and cer.dml_operation = 'GSPDEL';
4043:
4044: hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
4045: end cre_update_elig_prfl ;
4046:
4047:
4048: end pqh_gsp_stage_to_ben;