[Home] [Help]
PACKAGE BODY: APPS.GHR_TSP_CATCHUP_PLAN_DESIGN
Source
1 PACKAGE BODY ghr_tsp_catchup_plan_design AS
2 /* $Header: ghtspcpd.pkb 120.2 2006/10/23 20:38:40 bgarg noship $ */
3
4 --
5 -- Package Variables
6 --
7 g_package varchar2(100) := 'ghr_tsp_catchup_plan_design.';
8
9 Procedure create_tspc_program_and_plans (p_target_business_group_id in Number) is
10 --
11 l_proc Varchar2(100):= g_package||'create_tspc_program_and_plans';
12 p_validate Number := 0;
13 p_copy_entity_txn_id Number;
14 p_effective_date Date;
15 p_prefix_suffix_cd Varchar2(2);
16 p_prefix_suffix_text Varchar2(2);
17 p_reuse_object_flag Varchar2(1);
18 p_transaction_category_id Number(15);
19 l_effective_start_date Date;
20 l_effective_end_date Date;
21 Nothing_To_Do Exception;
22
23 --
24 Cursor get_txn_category_id is
25 select transaction_category_id
26 from pqh_transaction_categories
27 where short_name = 'BEN_PDCPWZ';
28 Cursor get_copy_txn_id is
29 select copy_entity_txn_id
30 from pqh_copy_entity_txns
31 where transaction_category_id = p_transaction_category_id
32 and context_business_group_id = 0
33 and display_name = 'GHR_TSP_CATCHUP_SEED_PROGRAM_DESIGN';
34 Cursor update_program_status is
35 select * from ben_pgm_f
36 where name = 'Federal Thrift Savings Plan (TSP) Catch Up Contributions'
37 and business_group_id = p_target_business_group_id;
38 --
39 Begin
40
41 hr_utility.set_location('Entering:'|| g_package||l_proc, 5);
42
43 Open get_txn_category_id;
44 Fetch get_txn_category_id into p_transaction_category_id;
45 hr_utility.trace('Transaction Category Id :'|| p_transaction_category_id);
46 hr_utility.set_location('Opening cursor get_copy_txn_id '||l_proc, 10);
47 --dbms_output.put_line('txn category id '||p_transaction_category_id);
48
49 Open get_copy_txn_id;
50 fetch get_copy_txn_id into p_copy_entity_txn_id;
51 If get_copy_txn_id%notfound then
52 Raise Nothing_to_do;
53 End If;
54 hr_utility.trace('Copy entity Txn. Id :'|| p_copy_entity_txn_id);
55 --dbms_output.put_line('copy_entity_txn_id :'||p_copy_entity_txn_id );
56
57
58 -- Set the variables
59 p_effective_date := to_date('12/31/2005','MM/DD//YYYY');
60 p_prefix_suffix_cd := null;
61 p_prefix_suffix_text := null;
62 p_reuse_object_flag := 'Y';
63
64 BEN_PD_COPY_TO_BEN_five.g_ghr_mode := 'TRUE';
65
66 --dbms_output.put_line('now calling..........');
67 BEN_PD_COPY_TO_BEN_TWO.create_stg_to_ben_rows(p_validate,
68 p_copy_entity_txn_id,
69 p_effective_date,
70 p_prefix_suffix_text,
71 p_reuse_object_flag,
72 p_target_business_group_id,
73 p_prefix_suffix_cd);
74 For i in update_program_status Loop
75 ben_Program_api.update_program(
76 p_pgm_id => i.pgm_id
77 ,p_effective_start_date => l_effective_start_date
78 ,p_effective_end_date => l_effective_end_date
79 ,p_pgm_stat_cd => 'A'
80 ,p_object_version_number => i.object_version_number
81 ,p_effective_date => i.effective_start_date
82 ,p_datetrack_mode => 'CORRECTION'
83 );
84 End Loop;
85
86 If get_txn_category_id%ISOPEN then
87 CLOSE get_txn_category_id;
88 End If;
89 If get_copy_txn_id%ISOPEN then
90 CLOSE get_copy_txn_id;
91 End If;
92 hr_utility.set_location('Leaving :'|| g_package||l_proc, 50);
93
94 Exception
95 When Nothing_to_do Then
96 If get_txn_category_id%ISOPEN then
97 CLOSE get_txn_category_id;
98 End If;
99 If get_copy_txn_id%ISOPEN then
100 CLOSE get_copy_txn_id;
101 End If;
102 null;
103 When others then
104 If get_txn_category_id%ISOPEN then
105 CLOSE get_txn_category_id;
106 End If;
107 If get_copy_txn_id%ISOPEN then
108 CLOSE get_copy_txn_id;
109 End If;
110 hr_utility.set_location('Leaving :'|| g_package||l_proc, 70);
111 Raise;
112 End create_tspc_program_and_plans;
113
114
115
116 Procedure populate_tspc_plan_design (p_errbuf OUT NOCOPY VARCHAR2,
117 p_retcode OUT NOCOPY NUMBER,
118 p_target_business_group_id in Number) is
119
120 Cursor c_check_pgm_tsp_exists is
121 select 'Y' tsp_exists from ben_pgm_f
122 where name = 'Federal Thrift Savings Plan (TSP)'
123 and business_group_id = p_target_business_group_id
124 and pgm_stat_cd = 'A';
125
126 Cursor check_pgm_exists is
127 select 'Y' from ben_pgm_f
128 where name = 'Federal Thrift Savings Plan (TSP) Catch Up Contributions'
129 and business_group_id = p_target_business_group_id;
130
131 l_proc varchar2(100):= substr(g_package||'Populate_tspc_plan_design',1,30);
132 p_exists Varchar2(1):= 'N';
133 p_tsp_exists Varchar2(1):= 'N';
134 l_err_msg Varchar2(2000);
135 Nothing_To_Do Exception;
136
137
138 Begin
139 hr_utility.set_location('entering :'|| g_package||l_proc, 10);
140 hr_utility.trace('Business Group Id ' ||p_target_business_group_id);
141 --Check if TSP program is installed before implementing catchUP.
142 --If TSP open is not installed then TSP CatchUP should not run.
143 p_tsp_exists := 'N';
144 For check_pgm_tsp_exists in c_check_pgm_tsp_exists loop
145 p_tsp_exists := check_pgm_tsp_exists.tsp_exists;
146 exit;
147 End loop;
148 If p_tsp_exists = 'N' then
149 l_err_msg := 'Federal Thrift Savings Plan (TSP) program is not installed in the business group '||p_target_business_group_id||'. Please install it before installing Federal Thrift Savings Plan (TSP) Catch Up Contributions Program.';
150 Raise nothing_to_do;
151 End If;
152 -- Check if TSP CatchUP is already installed.
153 -- If Yes, then program should not run.
154 Open check_pgm_exists;
155 Fetch check_pgm_exists into p_exists;
156 If check_pgm_exists%NOTFOUND Then
157 p_exists := 'N';
158 End If;
159 If p_exists = 'Y' then
160 l_err_msg := 'Federal Thrift Savings Plan (TSP) Catch Up Contributions program is already installed in the business_group '||p_target_business_group_id;
161 Raise nothing_to_do;
162 End If;
163
164 savepoint create_tspc_plan_design;
165 --dbms_output.put_line('now starting plan design ' ||p_target_business_group_id);
166 create_tspc_program_and_plans(p_target_business_group_id);
167 hr_utility.trace('After create_program_and_plans....');
168 commit;
169 If check_pgm_exists%ISOPEN then
170 CLOSE check_pgm_exists;
171 End If;
172 hr_utility.set_location('Leaving :'|| g_package||l_proc, 50);
173 Exception
174 When Nothing_to_do Then
175 If check_pgm_exists%ISOPEN then
176 CLOSE check_pgm_exists;
177 End If;
178 ghr_wgi_pkg.create_ghr_errorlog
179 (p_program_name => l_proc,
180 p_log_text => l_err_msg,
181 p_message_name => null,
182 p_log_date => sysdate
183 );
184 commit;
185 When others then
186 If check_pgm_exists%ISOPEN then
187 CLOSE check_pgm_exists;
188 End If;
189 hr_utility.set_location('Leaving :'|| g_package||l_proc, 60);
190 hr_utility.trace('Error ' ||sqlerrm(sqlcode));
191 l_err_msg := substr(p_target_business_group_id||':'||nvl(fnd_message.get,sqlerrm),1,1999) ;
192 rollback to create_tspc_plan_design;
193 ghr_wgi_pkg.create_ghr_errorlog
194 (p_program_name => l_proc,
195 p_log_text => l_err_msg,
196 p_message_name => null,
197 p_log_date => sysdate
198 );
199 commit;
200 End populate_tspc_plan_design;
201
202
203 Procedure get_recs_for_tspc_migration(p_errbuf OUT NOCOPY Varchar2
204 ,p_retcode OUT NOCOPY Number
205 ,p_business_group_id in Number) is
206
207 -- Modified cursor for 11.5.10 Performance changes
208 -- Added link between pay_element_types_f and pay_element_entries_f on element_type_id
209 Cursor c_emp_tspc(c_business_group_id in number, c_element_name in pay_element_types_f.element_name%type) is
210 select
211 e.assignment_id assignment_id,
212 decode(name,'Catch Up Amount','Amount', name) Name,
213 screen_entry_value,
214 e.effective_start_date,
215 g.person_id
216 from pay_element_types_f a,
217 pay_input_values_f b,
218 pay_element_links_f c,
219 pay_link_input_values_f d,
220 pay_element_entries_f e,
221 pay_element_entry_values_f f,
222 per_assignments_f g
223 where a.element_type_id = b.element_type_id
224 and a.element_type_id = c.element_type_id
225 and c.element_link_id = d.element_link_id
226 and b.input_value_id = d.input_value_id
227 and e.element_link_id = c.element_link_id
228 and f.element_entry_id = e.element_entry_id
229 and f.input_value_id = b.input_value_id
230 and a.element_type_id = e.element_type_id
231 and g.business_group_id = c_business_group_id
232 and e.effective_end_date = hr_api.g_eot
233 --and trunc(sysdate) between e.effective_start_date and e.effective_End_date
234 and trunc(e.effective_start_date) between f.effective_start_date and f.effective_End_date
235 and g.assignment_id = e.assignment_id
236 and trunc(e.effective_start_date) between g.effective_start_date and g.effective_end_date
237 and UPPER(a.element_name) = c_element_name
238 and ghr_general.return_number(screen_entry_value) > 0
239 order by 1, 2 desc;
240 l_element_name pay_element_types_f.element_name%type;
241
242 BEGIN
243 -- 11.5.10 Performance Changes
244 l_element_name := NVL(UPPER(pqp_fedhr_uspay_int_utils.return_new_element_name ('TSP Catch Up Contribution',p_business_group_id,sysdate,NULL)),'$Sys_Def$');
245
246 -- set program name
247 ghr_mto_int.set_log_program_name('GHR_TSP_CATCHUP_MIGRATION');
248
249 for emp_rec in c_emp_tspc(p_business_group_id,l_element_name) loop
250 ghr_general.ghr_tsp_catchup_migrate(emp_rec.assignment_id,
251 'Amount',
252 emp_rec.screen_entry_value,
253 emp_rec.effective_start_date,
254 p_business_group_id,
255 emp_rec.person_id);
256 commit;
257 end loop;
258 End get_recs_for_tspc_migration;
259
260
261 Procedure update_alternate_check_date(p_errbuf OUT NOCOPY Varchar2
262 ,p_retcode OUT NOCOPY Number
263 ,p_payroll_id in Number
264 ,p_date_start in Varchar2
265 ,p_date_to in Varchar2
266 ,p_chk_offset in Number) IS
267
268
269 l_proc varchar2(100):= 'Update_Alternate_Chcek_Date';
270 l_date_start Date;
271 l_date_to Date;
272 l_row_id Varchar2(200);
273 l_tprec per_time_periods%rowtype;
274
275 Cursor c_get_rowid is
276 select ROWID
277 from per_time_periods
278 where payroll_id = p_payroll_id
279 --and start_date between nvl(p_date_start,hr_api.g_sot) and nvl(p_date_to,hr_api.g_eot);
280 and start_date between l_date_start and l_date_to ;
281
282 Cursor c_get_details is
283 select * from per_time_periods where rowid = l_row_id;
284 Begin
285 hr_utility.set_location('entering :'|| g_package||l_proc, 10);
286
287 l_date_start := nvl(fnd_date.canonical_to_date(p_date_start),hr_api.g_sot);
288 l_date_to := nvl(fnd_date.canonical_to_date(p_date_to),hr_api.g_eot);
289
290 hr_utility.set_location( g_package||l_proc, 20);
291 for get_rowid in c_get_rowid loop
292 l_row_id := get_rowid.ROWID;
293
294 hr_utility.set_location( g_package||l_proc, 30);
295 for get_details in c_get_details loop
296 l_tprec := get_details;
297 hr_utility.set_location( g_package||l_proc, 40);
298 per_time_periods_pkg.update_row
299 (X_Rowid => l_row_id,
300 X_Time_Period_Id => l_tprec.time_period_id,
301 X_Payroll_Id => l_tprec.payroll_id ,
302 X_End_Date => l_tprec.end_date,
303 X_Period_Name => l_tprec.period_name,
304 X_Period_Num => l_tprec.period_Num,
305 X_Period_Type => l_tprec.period_type,
306 X_Start_Date => l_tprec.start_date,
307 X_Cut_Off_Date => l_tprec.cut_off_date,
308 X_Default_Dd_Date => l_tprec.default_dd_date,
309 X_Description => l_tprec.description,
313 X_Proc_Period_Type => l_tprec.proc_period_type,
310 X_Pay_Advice_Date => l_tprec.pay_advice_date,
311 X_Period_Set_Name => l_tprec.period_set_name,
312 X_Period_Year => l_tprec.period_year,
314 X_Quarter_Num => l_tprec.quarter_num,
315 X_Quickpay_Display_Number => l_tprec.quickpay_display_number,
316 X_Regular_Payment_Date => (l_tprec.end_date + p_chk_offset),
317 X_Run_Display_Number => l_tprec.run_display_number,
318 X_Status => l_tprec.status,
319 X_Year_Number => l_tprec.year_number,
320 X_Attribute_Category => l_tprec.attribute_category,
321 X_Attribute1 => l_tprec.attribute1,
322 X_Attribute2 => l_tprec.attribute2,
323 X_Attribute3 => l_tprec.attribute3,
324 X_Attribute4 => l_tprec.attribute4,
325 X_Attribute5 => l_tprec.attribute5,
326 X_Attribute6 => l_tprec.attribute6,
327 X_Attribute7 => l_tprec.attribute7,
328 X_Attribute8 => l_tprec.attribute8,
329 X_Attribute9 => l_tprec.attribute9,
330 X_Attribute10 => l_tprec.attribute10,
331 X_Attribute11 => l_tprec.attribute11,
332 X_Attribute12 => l_tprec.attribute12,
333 X_Attribute13 => l_tprec.attribute13,
334 X_Attribute14 => l_tprec.attribute14,
335 X_Attribute15 => l_tprec.attribute15,
336 X_Attribute16 => l_tprec.attribute16,
337 X_Attribute17 => l_tprec.attribute17,
338 X_Attribute18 => l_tprec.attribute18,
339 X_Attribute19 => l_tprec.attribute19,
340 X_Attribute20 => l_tprec.attribute20,
341 X_Prd_Information_Category => l_tprec.prd_Information_Category,
342 X_Prd_Information1 => l_tprec.Prd_Information1,
343 X_Prd_Information2 => l_tprec.Prd_Information2,
344 X_Prd_Information3 => l_tprec.Prd_Information3,
345 X_Prd_Information4 => l_tprec.Prd_Information4,
346 X_Prd_Information5 => l_tprec.Prd_Information5,
347 X_Prd_Information6 => l_tprec.Prd_Information6,
348 X_Prd_Information7 => l_tprec.Prd_Information7,
349 X_Prd_Information8 => l_tprec.Prd_Information8,
350 X_Prd_Information9 => l_tprec.Prd_Information9,
351 X_Prd_Information10 => l_tprec.Prd_Information10,
352 X_Prd_Information11 => l_tprec.Prd_Information11,
353 X_Prd_Information12 => l_tprec.Prd_Information12,
354 X_Prd_Information13 => l_tprec.Prd_Information13,
355 X_Prd_Information14 => l_tprec.Prd_Information14,
356 X_Prd_Information15 => l_tprec.Prd_Information15,
357 X_Prd_Information16 => l_tprec.Prd_Information16,
358 X_Prd_Information17 => l_tprec.Prd_Information17,
359 X_Prd_Information18 => l_tprec.Prd_Information18,
360 X_Prd_Information19 => l_tprec.Prd_Information19,
361 X_Prd_Information20 => l_tprec.Prd_Information20,
362 X_Prd_Information21 => l_tprec.Prd_Information21,
363 X_Prd_Information22 => l_tprec.Prd_Information22,
364 X_Prd_Information23 => l_tprec.Prd_Information23,
365 X_Prd_Information24 => l_tprec.Prd_Information24,
366 X_Prd_Information25 => l_tprec.Prd_Information25,
367 X_Prd_Information26 => l_tprec.Prd_Information26,
368 X_Prd_Information27 => l_tprec.Prd_Information27,
369 X_Prd_Information28 => l_tprec.Prd_Information28,
370 X_Prd_Information29 => l_tprec.Prd_Information29,
371 X_Prd_Information30 => l_tprec.Prd_Information30,
372 X_Payslip_view_Date => l_tprec.payslip_view_date
373 );
374 End loop;
375 End Loop;
376 hr_utility.set_location('leaving :'|| g_package||l_proc, 50);
377 Exception
378 when others then
379 hr_utility.set_location('Error Leaving :'|| g_package||l_proc, 100);
380 hr_utility.trace('Error ' ||sqlerrm(sqlcode));
381 End update_alternate_check_date;
382 end ghr_tsp_catchup_plan_design;