[Home] [Help]
PACKAGE BODY: APPS.PAY_US_LOC_CHANGE
Source
1 package body pay_us_loc_change as
2 /* $Header: pyuslocu.pkb 120.2 2007/06/07 13:47:44 sudedas noship $ */
3
4
5 /* Script to get the vaule of person's assignment detail
6 insert the values into temporary table and delete the values from
7 temporary table once the data is printed.
8 */
9
10 PROCEDURE cnt_print_report
11 IS
12 l6_wait BOOLEAN;
13 l6_phase VARCHAR2(30);
14 l6_status VARCHAR2(30);
15 l6_dev_phase VARCHAR2(30);
16 l6_dev_status VARCHAR2(30);
17 l6_message VARCHAR2(255);
18 l_rep_req_id NUMBER;
19
20 l_session_id number;
21
22 BEGIN
23 hr_utility.trace('Entering the cnt_print_report');
24 l_rep_req_id := fnd_request.submit_request(application => 'PAY',
25 program => 'LOCCHNREP');
26
27 select userenv('sessionid')
28 into l_session_id
29 from dual;
30
31 IF(l_rep_req_id = 0) THEN
32 hr_utility.trace(' Error While Location change Report' );
33 hr_utility.raise_error;
34 ELSE
35 hr_utility.trace(' Concurrent Request Id (Report Spool Request) : '
36 ||to_char(l_rep_req_id));
37 END IF; /* if l_rep_req_id */
38
39 COMMIT;
40
41 /* Wait for report request completion */
42 hr_utility.trace('Waiting for the application to get completed ');
43
44 /* Check for Report Request Status */
45
46 l6_wait := fnd_concurrent.wait_for_request
47 (request_id => l_rep_req_id
48 ,interval => 1
49 ,phase => l6_phase
50 ,status => l6_status
51 ,dev_phase => l6_dev_phase
52 ,dev_status => l6_dev_status
53 ,message => l6_message);
54
55 hr_utility.trace('Wait completed,Printing output based on the result');
56
57 IF NOT (l6_dev_phase = 'COMPLETE' and l6_dev_status = 'NORMAL') THEN
58 hr_utility.trace('SQL Report Exited with error') ;
59 ELSE
60 hr_utility.trace('SQL Report Successful');
61 END IF; /* l6_dev_phase */
62
63
64 DELETE
65 FROM pay_us_rpt_totals
66 WHERE attribute20 = 'LOCATION_CHANGE'
67 AND organization_id = l_session_id;
68
69 COMMIT;
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 hr_utility.trace('Exception : No data Found');
73 WHEN OTHERS THEN
74 hr_utility.trace('Excption : When Others');
75 END cnt_print_report;
76
77
78
79 PROCEDURE get_insert_values ( p_proc_name VARCHAR2,
80 p_assignment_id NUMBER,
81 p_location_id NUMBER,
82 p_gre_name IN OUT NOCOPY VARCHAR2,
83 p_full_name IN OUT NOCOPY VARCHAR2,
84 p_assignment_number IN OUT NOCOPY VARCHAR2,
85 p_location_code IN OUT NOCOPY VARCHAR2)
86 IS
87
88 /* cursor to get the person's assignment detail */
89
90 CURSOR csr_assignment_tax_detail
91 IS
92 SELECT hou.Name,
93 ppf.full_name,
94 paf.assignment_number
95 FROM
96 hr_organization_units hou,
97 hr_soft_coding_keyflex hsck,
98 per_people_f ppf,
99 per_assignments_f paf
100 WHERE
101 paf.assignment_id = p_assignment_id
102 AND paf.person_id = ppf.person_id
103 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
104 AND hsck.segment1 = hou.organization_id
105 -- Changing the cursor to pick up the most recent date
106 AND ppf.effective_start_date = (select max(ppf_inner.effective_start_date)
107 from per_people_f ppf_inner
108 where ppf_inner.person_id = ppf.person_id) ;
109 /* cursor to get location code */
110
111 CURSOR csr_location_code
112 IS
113 SELECT location_code
114 FROM hr_locations
115 WHERE location_id = p_location_id;
116
117 l_proc_name varchar2(50) := 'get_insert_value';
118
119 BEGIN
120 hr_utility.trace('In proc : get_insert_values');
121 hr_utility.trace('Assignment Id : ' || to_char(p_assignment_id));
122 hr_utility.trace('Location Id : ' || to_char(p_location_id));
123
124 IF p_proc_name = 'LOCATION_CHANGE' THEN
125
126 hr_utility.trace('Entering : ' || l_proc_name);
127
128 /* get the detail of the person */
129
130 OPEN csr_assignment_tax_detail;
131
132 FETCH csr_assignment_tax_detail
133 INTO p_gre_name,
134 p_full_name,
135 p_assignment_number;
136
137 IF csr_assignment_tax_detail%NOTFOUND THEN
138 hr_utility.trace('csr_assignment_tax_datail_failed');
139 END IF;
140
141
142 CLOSE csr_assignment_tax_detail;
143
144
145 /* get the location Code for the location id */
146
147 OPEN csr_location_code;
148
149 FETCH csr_location_code
150 INTO p_location_code;
151
152
153 END IF;
154 hr_utility.trace('GRE NAME : ' || p_gre_name);
155 hr_utility.trace('Full NAME : ' || p_full_name);
156 hr_utility.trace('Assignment Number: ' || p_assignment_number);
157 hr_utility.trace('Location Code : ' || p_location_code);
158
159 hr_utility.trace('Leaving : ' || l_proc_name);
160 EXCEPTION
161 WHEN OTHERS THEN
162 hr_utility.trace('Leaving : ' || l_proc_name || ' With Erorr' );
163
164 END get_insert_values ;
165
166
167 /* Mapping of the Temp. table and value are as follows
168 tax_unit_name - GRE_NAME
169 location_code - LOCATION_code
170 full_name - Attribute1
171 EMP_assignement_no - ATTRUBITE2
172 effective_start_date- ATTRIBUTE3
173 effective_end_date - ATTRIBUTE4
174 error message - ATTRIBUTE5
175 */
176
177 procedure put_into_temp_table(
178 p_tax_unit_name in varchar2,
179 p_location_code in varchar2,
180 p_emp_full_name in varchar2,
181 p_assignment_number in varchar2,
182 p_effective_start_date in date,
183 p_effective_end_date in date,
184 p_error in varchar2
185 )
186 IS
187 l_success_failure_indicator number;
188 l_proc_name varchar2(50) := 'put_into_temp_table';
189 l_session_id number;
190
191 BEGIN
192 hr_utility.trace('Entering :' || l_proc_name);
193
194 select userenv('sessionid')
195 into l_session_id
199 INTO pay_us_rpt_totals
196 from dual;
197
198 INSERT
200 (
201 organization_id,
202 gre_name,
203 location_name,
204 attribute1,
205 attribute2,
206 attribute3,
207 attribute4,
208 attribute5,
209 attribute20
210 )
211 VALUES (
212 l_session_id,
213 p_tax_unit_name,
214 p_location_code,
215 p_emp_full_name,
216 p_assignment_number,
217 to_char(p_effective_start_date,'mm/dd/yyyy'),
218 to_char(p_effective_end_date,'mm/dd/yyyy'),
219 p_error,
220 'LOCATION_CHANGE');
221
222
223 hr_utility.trace('Leaving : ' || l_proc_name);
224 EXCEPTION
225
226 WHEN others THEN
227 hr_utility.trace('Error in the Instertion into temporary Table');
228 END put_into_temp_table;
229
230
231 procedure update_tax(errbuf OUT NOCOPY VARCHAR2,
232 retcode OUT NOCOPY NUMBER,
233 p_location_id in number) is
234
235 l_ret_code number;
236 l_ret_text varchar2(1000);
237 l_assignment_id per_assignments_f.assignment_id%type;
238 l_gre_name hr_tax_units_v.name%type;
239 l_location_code hr_locations.location_code%type;
240 l_full_name per_people_f.full_name%type;
241 l_assignment_number per_assignments_f.assignment_number%type;
242 l_location_id number;
243 l_error varchar2(1000);
244 l_assignment_status varchar2(1);
245
246 l_tbl_location_id number;
247 l_tbl_start_date date;
248 l_tbl_end_date date;
249 l_tmp_location_id number;
250
251 cur_location_id number;
252 cur_ovr_location_id number;
253 cur_start_date date;
254 cur_end_date date;
255
256
257
258 /* Get the employees who have the location. */
259 /* Rmonge 18-NOV-2002 Modifying cursor to add ASSIGMENT_TYPE */
260 /* We need to check if the assignment being process is a Benefits Assignment.*/
261 /* If it is then, we do not want to process the record */
262
263
264 cursor csr_get_employee (p_loc_id number) is
265 select /*+ index(hsck HR_SOFT_CODING_KEYFLEX_PK) */ paf.assignment_id assignment_id,
266 paf.person_id,
267 max(paf.effective_end_date) effective_end_date,
268 min(paf.effective_start_date) effective_start_date,
269 paf.business_group_id,
270 paf.assignment_type
271 from per_all_assignments_f paf,
272 hr_soft_coding_keyflex hsck
273 where (paf.location_id = p_loc_id
274 or hsck.segment18 = to_char(p_loc_id)) -- #3056158
275 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
276 group by assignment_id,
277 paf.person_id,
278 paf.business_group_id,
279 paf.assignment_type
280 ;
281
282 Cursor csr_assignment_locations(p_assignment_id number,
283 p_def_date date) is
284 select paf.location_id,
285 hsck.segment18,
286 paf.effective_start_date,
287 paf.effective_end_date
288 from per_all_assignments_f paf,
289 hr_soft_coding_keyflex hsck
290 where paf.assignment_id = p_assignment_id
291 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
292 and effective_start_date >= p_def_date
293 order by effective_start_date;
294
295 begin
296 --hr_utility.trace_on(NULL,'PYLC');
297 hr_utility.trace('In Proc. UPDATE_TAX');
298
299 for emp_rec in csr_get_employee(p_location_id) loop
300 l_error := ' '; --bug 3265603
301
302 /* Rmonge 18-JAN-2002 BUG 2110744 */
303
304 HR_UTILITY.TRACE('THE ASSIGNMENT TYPE IS '||emp_rec.assignment_type);
305 if emp_rec.assignment_type <> 'B' Then
306
307 HR_UTILITY.TRACE('Assignment Type is not B');
308
309 l_tmp_location_id := null;
310 l_tbl_location_id := null;
311 l_tbl_start_date := null;
312 l_tbl_end_date := null;
313
314 Open csr_assignment_locations(emp_rec.assignment_id,
315 emp_rec.effective_start_date);
316 Fetch csr_assignment_locations
317 into cur_location_id,
318 cur_ovr_location_id,
319 cur_start_date,
320 cur_end_date;
321
322 if csr_assignment_locations%FOUND then
323 --
324
325 if cur_location_id = p_location_id or cur_ovr_location_id = p_location_id then
326 l_tmp_location_id := p_location_id;
327 else
328 l_tmp_location_id := cur_location_id;
329 end if;
330
331
332 l_tbl_location_id := l_tmp_location_id;
333 l_tbl_start_date := cur_start_date;
334 l_tbl_end_date := cur_end_date;
335 While csr_assignment_locations%FOUND loop
336 --
337 hr_utility.set_location('update_tax', 55);
338 --
339 --
340 -- Store all assignment records.
341 --
342 Fetch csr_assignment_locations
343 into cur_location_id,
344 cur_ovr_location_id,
345 cur_start_date,
346 cur_end_date;
347
348 if csr_assignment_locations%FOUND then
349 hr_utility.trace('Assignment location found ');
350 hr_utility.trace('Cur location id is ' || to_char(cur_location_id));
351 hr_utility.trace('P_location_id is ' || to_char(p_location_id));
352 hr_utility.trace('cur_ovr_location_id is '||to_char(cur_ovr_location_id));
353
354 if cur_location_id = p_location_id or cur_ovr_location_id = p_location_id then
355 l_tmp_location_id := p_location_id;
356 else
357 l_tmp_location_id := cur_location_id;
358 end if;
359 hr_utility.trace('l_tbl_location_id is '|| to_char(l_tbl_location_id));
360
361 if l_tbl_location_id <> l_tmp_location_id then
362 hr_utility.trace('l_tbl_location_id <> l_tmp_location id ');
363
364 if l_tbl_location_id = p_location_id then
365 hr_utility.trace('l_tbl_location_id = p_location_id ');
366 begin
367
368 pay_us_emp_dt_tax_rules.default_tax_with_validation(
369 p_assignment_id => emp_rec.assignment_id,
370 p_person_id => emp_rec.person_id,
371 p_effective_start_date => l_tbl_start_date,
372 p_effective_end_date => l_tbl_end_date,
373 p_session_date => l_tbl_start_date,
374 p_business_group_id => emp_rec.business_group_id,
375 p_from_form => 'Assignment',
376 p_mode => 'CORRECTION',
377 p_location_id => p_location_id,
378 p_return_code => l_ret_code,
379 p_return_text => l_ret_text);
380
381 l_error := null;
382
383 exception
384 When others then
385 l_error := SQLERRM;
386 end;
387 end if;
388 l_tbl_location_id := l_tmp_location_id;
389 l_tbl_start_date := cur_start_date;
390 l_tbl_end_date := cur_end_date;
391 else
392 l_tbl_end_date := cur_end_date;
393 end if;
394 else
395 hr_utility.trace('l_tbl_location_id = p_location_id');
396
397 if l_tbl_location_id = p_location_id then
398 begin
399 pay_us_emp_dt_tax_rules.default_tax_with_validation(
400 p_assignment_id => emp_rec.assignment_id,
401 p_person_id => emp_rec.person_id,
402 p_effective_start_date => l_tbl_start_date,
403 p_effective_end_date => l_tbl_end_date,
404 p_session_date => l_tbl_start_date,
405 p_business_group_id => emp_rec.business_group_id,
406 p_from_form => 'Assignment',
407 p_mode => 'CORRECTION',
408 p_location_id => p_location_id,
409 p_return_code => l_ret_code,
410 p_return_text => l_ret_text);
411 exception
412 When others then
413 l_error := SQLERRM;
414 end;
415
416 end if;
417 end if;
418 End loop;
419 end if;
420 close csr_assignment_locations;
421
422 get_insert_values(
423 'LOCATION_CHANGE',
424 emp_rec.assignment_id,
425 p_location_id,
426 l_gre_name,
427 l_full_name,
428 l_assignment_number,
429 l_location_code);
430
431 put_into_temp_table(p_tax_unit_name => l_gre_name ,
432 p_location_code => l_location_code ,
433 p_emp_full_name => l_full_name ,
434 p_assignment_number => l_assignment_number,
435 p_effective_start_date => emp_rec.effective_start_date ,
436 p_effective_end_date => emp_rec.effective_end_date ,
437 p_error => l_error);
438
439 end if; /* assignment_type <> 'B' */
440 end loop;
441
442
443 cnt_print_report;
444 end update_tax;
445 end pay_us_loc_change;