[Home] [Help]
PACKAGE BODY: APPS.PQP_LOG_ALIEN_DATA_CHANGES
Source
1 PACKAGE BODY pqp_log_alien_data_changes as
2 /* $Header: pquslapc.pkb 115.2 2002/10/28 23:41:34 sshetty ship $*/
3 -----------------------------------------------------------------------------
4 -- CHECK_FOR_CHANGES
5 -----------------------------------------------------------------------------
6 PROCEDURE check_for_changes (p_assignment_id in number
7 ,p_person_id in number
8 ,p_effective_date in date
9 ,p_new_value_char1 in varchar2
10 ,p_old_value_char1 in varchar2
11 ,p_new_value_char2 in varchar2
12 ,p_old_value_char2 in varchar2
13 ,p_new_value_char3 in varchar2
14 ,p_old_value_char3 in varchar2
15 ,p_new_value_char4 in varchar2
16 ,p_old_value_char4 in varchar2
17 ,p_new_value_char5 in varchar2
18 ,p_old_value_char5 in varchar2
19 ,p_new_value_char6 in varchar2
20 ,p_old_value_char6 in varchar2
21 ,p_new_value_char7 in varchar2
22 ,p_old_value_char7 in varchar2
23 ,p_new_value_char8 in varchar2
24 ,p_old_value_char8 in varchar2
25 ,p_new_value_char9 in varchar2
26 ,p_old_value_char9 in varchar2
27 ,p_new_value_char10 in varchar2
28 ,p_old_value_char10 in varchar2
29 ,p_new_value_date1 in date
30 ,p_old_value_date1 in date
31 ,p_new_value_date2 in date
32 ,p_old_value_date2 in date ) IS
33 --
34 -- this procedure accepts old and new values, compares the values
35 -- and if there are any changes, it calls the log events procedure.
36 --
37 l_proc VARCHAR2(60) := 'pqp_log_alien_data_changes.check_for_changes';
38 --
39 BEGIN
40 hr_utility.set_location('Entering: '||l_proc, 10);
41 --
42 -- Log the events only if the process type is WINDSTAR
43 --
44 IF pqp_us_ff_functions.is_windstar
45 (p_person_id => p_person_id
46 ,p_assignment_id => NULL ) = 'TRUE' THEN
47 IF NVL(p_old_value_char1, ' ') <> NVL(p_new_value_char1, ' ') OR
48 NVL(p_old_value_char2, ' ') <> NVL(p_new_value_char2, ' ') OR
49 NVL(p_old_value_char3, ' ') <> NVL(p_new_value_char3, ' ') OR
50 NVL(p_old_value_char4, ' ') <> NVL(p_new_value_char4, ' ') OR
51 NVL(p_old_value_char5, ' ') <> NVL(p_new_value_char5, ' ') OR
52 NVL(p_old_value_char6, ' ') <> NVL(p_new_value_char6, ' ') OR
53 NVL(p_old_value_char7, ' ') <> NVL(p_new_value_char7, ' ') OR
54 NVL(p_old_value_char8, ' ') <> NVL(p_new_value_char8, ' ') OR
55 NVL(p_old_value_char9, ' ') <> NVL(p_new_value_char9, ' ') OR
56 NVL(p_old_value_char10,' ') <> NVL(p_new_value_char10,' ') OR
57 NVL(p_old_value_date1,sysdate) <> NVL(p_new_value_date1,sysdate) OR
58 NVL(p_old_value_date2,sysdate) <> NVL(p_new_value_date2,sysdate) THEN
59 --
60 -- log the event
61 --
62 log_events(p_assignment_id => p_assignment_id
63 ,p_effective_date => p_effective_date);
64 --
65 hr_utility.set_location(l_proc, 20);
66 END IF;
67 END IF;
68 --
69 hr_utility.set_location('Leaving: '||l_proc, 10);
70 --
71 END check_for_changes;
72 -----------------------------------------------------------------------------
73 -- ALIEN_ELEMENT_CHECK
74 -----------------------------------------------------------------------------
75 PROCEDURE alien_element_check (p_assignment_id in number
76 ,p_effective_date in date
77 ,p_element_link_id in number ) IS
78 --
79 -- we need to log the event when a employee gets an alien earnings. This
80 -- procedure checks this and calls the log_events procedure.
81 --
82 CURSOR c_element IS
83 SELECT pet.element_type_id,
84 pet.element_information1 inc_code ---'x'
85 FROM pay_element_types_f PET,
86 pay_element_links_f PEL
87 WHERE PEL.element_link_id = p_element_link_id
88 AND PEL.element_type_id = PET.element_type_id
89 AND p_effective_date BETWEEN PET.effective_start_date AND
90 PET.effective_end_date
91 AND p_effective_date BETWEEN PEL.effective_start_date AND
92 PEL.effective_end_date
93 AND EXISTS (SELECT 'x'
94 FROM pay_element_classifications PEC
95 WHERE PEC.classification_id = PET.classification_id
96 AND PEC.classification_name = 'Alien/Expat Earnings'
97 AND PEC.legislation_code = 'US' );
98
99 CURSOR c_get_curent_code(cp_assignment_id NUMBER
100 ,cp_effective_date DATE
101 ,cp_inc_code VARCHAR2) IS
102 SELECT 'X'
103 FROM pqp_analyzed_alien_details paad
104 ,pqp_analyzed_alien_data pad
105 where pad.analyzed_data_id=paad.analyzed_data_id
106 and pad.tax_year=to_number(to_char(cp_effective_date,'YYYY'))
107 and pad.assignment_id=cp_assignment_id
108 and pad.data_source='PQP_US_ALIEN_WINDSTAR'
109 AND paad.income_code=cp_inc_code;
110
111 l_get_curent_code c_get_curent_code%ROWTYPE;
112 l_exist VARCHAR2(1):='N';
113
114 --
115 l_proc VARCHAR2(60) := 'pqp_log_alien_data_changes.alien_element_check';
116 --
117 BEGIN
118 hr_utility.set_location('Entering: '||l_proc, 10);
119 --
120 l_exist:='N';
121 FOR c_element_rec in c_element LOOP
122 OPEN c_get_curent_code (p_assignment_id,
123 p_effective_date,
124 c_element_rec.inc_code);
125 FETCH c_get_curent_code INTO l_get_curent_code;
126 IF c_get_curent_code%NOTFOUND THEN
127 log_events(p_assignment_id => p_assignment_id
128 ,p_effective_date => p_effective_date);
129 END IF;
130 CLOSE c_get_curent_code;
131 --
132 hr_utility.set_location(l_proc, 20);
133 END LOOP;
134 hr_utility.set_location('Leaving: '||l_proc, 10);
135 --
136 END alien_element_check;
137 -----------------------------------------------------------------------------
138 -- PERSON_LEVEL_CHECK
139 -----------------------------------------------------------------------------
140 PROCEDURE person_level_check
141 (p_person_id in number
142 ,p_table_name in varchar2
143 ,p_effective_date in date
144 ,p_new_value_char1 in varchar2
145 ,p_old_value_char1 in varchar2
146 ,p_new_value_char2 in varchar2
147 ,p_old_value_char2 in varchar2
148 ,p_new_value_char3 in varchar2
149 ,p_old_value_char3 in varchar2
150 ,p_new_value_char4 in varchar2
151 ,p_old_value_char4 in varchar2
152 ,p_new_value_char5 in varchar2
153 ,p_old_value_char5 in varchar2
154 ,p_new_value_char6 in varchar2
155 ,p_old_value_char6 in varchar2
156 ,p_new_value_char7 in varchar2
157 ,p_old_value_char7 in varchar2
158 ,p_new_value_char8 in varchar2
159 ,p_old_value_char8 in varchar2
160 ,p_new_value_char9 in varchar2
161 ,p_old_value_char9 in varchar2
162 ,p_new_value_char10 in varchar2
163 ,p_old_value_char10 in varchar2
164 ,p_new_value_date1 in date
165 ,p_old_value_date1 in date
166 ,p_new_value_date2 in date
167 ,p_old_value_date2 in date ) IS
168 --
169 -- called from all the person related triggers like person, person extra
170 -- info etc. Validates if there are any changes and logs all the
171 -- assignments into the process log
172 --
173 l_proc VARCHAR2(60) := 'pqp_log_alien_data_changes.person_level_check';
174 --
175 l_session_date DATE := p_effective_date;
176 l_continue BOOLEAN := TRUE;
177 l_new_char1 VARCHAR2(80);
178 l_old_char1 VARCHAR2(80);
179 l_new_char2 VARCHAR2(80);
180 l_old_char2 VARCHAR2(80);
181 l_new_char3 VARCHAR2(80);
182 l_old_char3 VARCHAR2(80);
183 l_new_char4 VARCHAR2(80);
184 l_old_char4 VARCHAR2(80);
185 l_new_char5 VARCHAR2(80);
186 l_old_char5 VARCHAR2(80);
187 l_new_char6 VARCHAR2(80);
188 l_old_char6 VARCHAR2(80);
189 l_new_char7 VARCHAR2(80);
190 l_old_char7 VARCHAR2(80);
191 l_new_char8 VARCHAR2(80);
192 l_old_char8 VARCHAR2(80);
193 l_new_char9 VARCHAR2(80);
194 l_old_char9 VARCHAR2(80);
195 l_new_char10 VARCHAR2(80);
196 l_old_char10 VARCHAR2(80);
197 --
198 CURSOR c_session IS
199 SELECT effective_date
200 FROM fnd_sessions
201 WHERE session_id = userenv('sessionid');
202 --
203 CURSOR c_assign IS
204 SELECT assignment_id
205 FROM per_assignments_f
206 WHERE person_id = p_person_id
207 --AND effective_end_date >= NVL(l_session_date, sysdate);
208 AND NVL(l_session_date, sysdate) BETWEEN
209 effective_start_date AND effective_end_date;
210 --
211 BEGIN
212 hr_utility.set_location('Entering: '||l_proc, 10);
213 --
214 -- For address, log the changes only if the primary_flag = 'Y'
215 --
216 IF (p_table_name = 'PER_ADDRESSES' AND
217 p_new_value_char1 = 'N') THEN
218 hr_utility.set_location(l_proc, 20);
219 -- do not do log changes if the address is secondary
220 ELSIF p_table_name = 'PER_PEOPLE_EXTRA_INFO' THEN
221 /*
222 * *** PLEASE NOTE the column mapping ***
223 *
224 * p_new_value_char2 = pei_information5
225 * p_new_value_char3 = pei_information6
226 * p_new_value_char4 = pei_information7
227 * p_new_value_char5 = pei_information8
228 * p_new_value_char6 = pei_information9
229 * p_new_value_char7 = pei_information10
230 * p_new_value_char8 = pei_information11
231 * p_new_value_char9 = pei_information12
232 * p_new_value_char10 = pei_information13
233 */
234 IF p_new_value_char1 = 'PER_US_VISA_DETAILS' THEN
235 hr_utility.set_location(l_proc, 30);
236 l_new_char2 := p_new_value_char2; -- Visa type (info 5)
237 l_old_char2 := p_old_value_char2;
238 l_new_char3 := p_new_value_char3; -- Visa number (info 6)
239 l_old_char3 := p_old_value_char3;
240 l_new_char4 := p_new_value_char4; -- Visa issue date (info 7)
241 l_old_char4 := p_old_value_char4;
242 l_new_char5 := p_new_value_char5; -- Visa expiry date (info 8)
243 l_old_char5 := p_old_value_char5;
244 l_new_char6 := p_new_value_char6; -- Visa category (info 9)
245 l_old_char6 := p_old_value_char6;
246 --
247 ELSIF p_new_value_char1 = 'PER_US_PASSPORT_DETAILS' THEN
248 hr_utility.set_location(l_proc, 40);
249 l_new_char2 := p_new_value_char2; -- country (info 5)
250 l_old_char2 := p_old_value_char2;
251 --
252 ELSIF p_new_value_char1 = 'PER_US_PAYROLL_DETAILS' THEN
253 hr_utility.set_location(l_proc, 50);
254 l_new_char2 := p_new_value_char2; -- Income code (info 5)
255 l_old_char2 := p_old_value_char2;
256 l_new_char3 := p_new_value_char3; -- Prev ER treaty ben amt(info 6)
257 l_old_char3 := p_old_value_char3;
258 l_new_char4 := p_new_value_char4; -- Prev ER treaty ben year (info 7)
259 l_old_char4 := p_old_value_char4;
260 --
261 ELSIF p_new_value_char1 = 'PER_US_ADDITIONAL_DETAILS' THEN
262 hr_utility.set_location(l_proc, 60);
263 l_new_char2 := p_new_value_char2; -- Residency Status (info 5)
264 l_old_char2 := p_old_value_char2;
265 l_new_char3 := p_new_value_char4; -- Resident Status Date (info 8)
266 l_old_char3 := p_old_value_char4;
267 l_new_char4 := p_new_value_char5; -- First entry date (info 8)
268 l_old_char4 := p_old_value_char5;
269 l_new_char5 := p_new_value_char6; -- Tax res country code (info 9)
270 l_old_char5 := p_old_value_char6;
271 l_new_char6 := p_new_value_char9; -- Process Type (info 12)
272 l_old_char6 := p_old_value_char9;
273 --
274 END IF;
275 -- call for each assignment
276 FOR c_rec in c_assign LOOP
277 hr_utility.set_location(l_proc, 70);
278 --
279 FOR c_rec in c_session LOOP
280 l_session_date := c_rec.effective_date;
281 END LOOP;
282 --
283 check_for_changes(p_assignment_id => c_rec.assignment_id
284 ,p_person_id => p_person_id
285 ,p_effective_date => l_session_date
286 ,p_new_value_char1 => l_new_char1
287 ,p_old_value_char1 => l_old_char1
288 ,p_new_value_char2 => l_new_char2
289 ,p_old_value_char2 => l_old_char2
290 ,p_new_value_char3 => l_new_char3
291 ,p_old_value_char3 => l_old_char3
292 ,p_new_value_char4 => l_new_char4
293 ,p_old_value_char4 => l_old_char4
294 ,p_new_value_char5 => l_new_char5
295 ,p_old_value_char5 => l_old_char5
296 ,p_new_value_char6 => l_new_char6
297 ,p_old_value_char6 => l_old_char6
298 ,p_new_value_char7 => l_new_char7
299 ,p_old_value_char7 => l_old_char7 );
300 END LOOP;
301 ELSE
302 hr_utility.set_location(l_proc, 80);
303 IF l_session_date IS NULL THEN
304 FOR c_rec in c_session LOOP
305 hr_utility.set_location(l_proc, 90);
306 l_session_date := c_rec.effective_date;
307 END LOOP;
308 END IF;
309 --
310 FOR c_rec in c_assign LOOP
311 hr_utility.set_location(l_proc, 100);
312 check_for_changes(p_assignment_id => c_rec.assignment_id
313 ,p_person_id => p_person_id
314 ,p_effective_date => l_session_date
315 ,p_new_value_char1 => p_new_value_char1
316 ,p_old_value_char1 => p_old_value_char1
317 ,p_new_value_char2 => p_new_value_char2
318 ,p_old_value_char2 => p_old_value_char2
319 ,p_new_value_char3 => p_new_value_char3
320 ,p_old_value_char3 => p_old_value_char3
321 ,p_new_value_char4 => p_new_value_char4
322 ,p_old_value_char4 => p_old_value_char4
323 ,p_new_value_char5 => p_new_value_char5
324 ,p_old_value_char5 => p_old_value_char5
325 ,p_new_value_char6 => p_new_value_char6
326 ,p_old_value_char6 => p_old_value_char6
327 ,p_new_value_char7 => p_new_value_char7
328 ,p_old_value_char7 => p_old_value_char7
329 ,p_new_value_char8 => p_new_value_char8
330 ,p_old_value_char8 => p_old_value_char8
331 ,p_new_value_char9 => p_new_value_char9
332 ,p_old_value_char9 => p_old_value_char9
333 ,p_new_value_char10 => p_new_value_char10
334 ,p_old_value_char10 => p_old_value_char10
335 ,p_new_value_date1 => p_new_value_date1
336 ,p_old_value_date1 => p_old_value_date1
337 ,p_new_value_date2 => p_new_value_date2
338 ,p_old_value_date2 => p_old_value_date2 );
339 --
340 hr_utility.set_location(l_proc, 110);
341 END LOOP;
342 END IF;
343 hr_utility.set_location('Leaving: '||l_proc, 150);
344 --
345 END person_level_check;
346 -----------------------------------------------------------------------------
347 -- LOG_EVENTS
348 -----------------------------------------------------------------------------
349 PROCEDURE log_events (p_assignment_id in number
350 ,p_effective_date in date ) IS
351 --
352 -- Procedure to check whether the event is already logged, if not it logs
353 -- the event in the table pay_process_events.
354 --
355 CURSOR c_asg_exists is
356 SELECT 'x'
357 FROM pay_process_events
358 WHERE assignment_id = p_assignment_id
359 AND change_type = 'PQP_US_ALIEN_WINDSTAR'
360 AND status in ('N', 'D'); -- NOT_READ, DATA_VALIDATION_FAILED
361 --
362 l_temp varchar2(10);
363 l_dummy1 number;
364 l_dummy2 number;
365 l_proc VARCHAR2(60) := 'pqp_log_alien_data_changes.log_events';
366 --
367 BEGIN
368 hr_utility.set_location('Entering: '||l_proc, 10);
369 --
370 OPEN c_asg_exists;
371 FETCH c_asg_exists into l_temp;
372 IF c_asg_exists%NOTFOUND THEN
373 BEGIN
374 --call pay_process_events API
375 pay_ppe_api.create_process_event
376 (p_validate => FALSE
377 ,p_assignment_id => p_assignment_id
378 ,p_effective_date => p_effective_date
379 ,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
380 ,p_status => 'N'
381 ,p_description => 'PQP event logging'
382 ,p_process_event_id => l_dummy1
383 ,p_object_version_number => l_dummy2 );
384 --
385 hr_utility.set_location(l_proc, 20);
386 --
387 EXCEPTION
388 WHEN OTHERS THEN
389 hr_utility.set_location(l_proc, 50);
390 raise;
391 END;
392 END IF;
393 CLOSE c_asg_exists;
394 --
395 hr_utility.set_location('Leaving: '||l_proc, 100);
396 --
397 END log_events;
398 -----------------------------------------------------------------------------
399 -- LOG_PEI_INSERT_CHANGES
400 -----------------------------------------------------------------------------
401 PROCEDURE log_pei_insert_changes (p_person_id in number
402 ,p_information_type in varchar2
403 ,p_pei_information5 in varchar2
404 ,p_pei_information6 in varchar2
405 ,p_pei_information7 in varchar2
406 ,p_pei_information8 in varchar2
407 ,p_pei_information9 in varchar2
408 ,p_pei_information10 in varchar2
409 ,p_pei_information11 in varchar2
410 ,p_pei_information12 in varchar2
411 ,p_pei_information13 in varchar2 ) IS
412 --
413 -- Procedure which will be called by the PER_PEOPLE_EXTRA_INFO API USER
414 -- HOOKS to check whether the event is already logged.
415 -- Legislative user hook is used due to mutating table problem for
416 -- dynamic triggers on this table.
417 --
418 l_proc VARCHAR2(60) := 'pqp_log_alien_data_changes.log_pei_insert_changes';
419 --
420 BEGIN
421 hr_utility.set_location('Entering: '||l_proc, 10);
422 --
423 person_level_check
424 (p_person_id => p_person_id
425 ,p_table_name => 'PER_PEOPLE_EXTRA_INFO'
426 ,p_effective_date => NULL
427 ,p_new_value_char1 => p_information_type
428 ,p_old_value_char1 => NULL
429 ,p_new_value_char2 => p_pei_information5
430 ,p_old_value_char2 => NULL
431 ,p_new_value_char3 => p_pei_information6
432 ,p_old_value_char3 => NULL
433 ,p_new_value_char4 => p_pei_information7
434 ,p_old_value_char4 => NULL
435 ,p_new_value_char5 => p_pei_information8
436 ,p_old_value_char5 => NULL
437 ,p_new_value_char6 => p_pei_information9
438 ,p_old_value_char6 => NULL
439 ,p_new_value_char7 => p_pei_information10
440 ,p_old_value_char7 => NULL
441 ,p_new_value_char8 => p_pei_information11
442 ,p_old_value_char8 => NULL
443 ,p_new_value_char9 => p_pei_information12
444 ,p_old_value_char9 => NULL
445 ,p_new_value_char10 => p_pei_information13
446 ,p_old_value_char10 => NULL );
447 --
448 hr_utility.set_location('Leaving: '||l_proc, 20);
449 --
450 END log_pei_insert_changes;
451 -----------------------------------------------------------------------------
452 -- LOG_PEI_UPDATE_CHANGES
453 -----------------------------------------------------------------------------
454 PROCEDURE log_pei_update_changes
455 (p_person_id in number
456 ,p_information_type in varchar2
457 ,p_information_type_o in varchar2
458 ,p_pei_information5 in varchar2
459 ,p_pei_information5_o in varchar2
460 ,p_pei_information6 in varchar2
461 ,p_pei_information6_o in varchar2
462 ,p_pei_information7 in varchar2
463 ,p_pei_information7_o in varchar2
464 ,p_pei_information8 in varchar2
465 ,p_pei_information8_o in varchar2
466 ,p_pei_information9 in varchar2
467 ,p_pei_information9_o in varchar2
468 ,p_pei_information10 in varchar2
469 ,p_pei_information10_o in varchar2
470 ,p_pei_information11 in varchar2
471 ,p_pei_information11_o in varchar2
472 ,p_pei_information12 in varchar2
473 ,p_pei_information12_o in varchar2
474 ,p_pei_information13 in varchar2
475 ,p_pei_information13_o in varchar2 ) IS
476 --
477 l_proc VARCHAR2(60) := 'pqp_log_alien_data_changes.log_pei_update_changes';
478 --
479 BEGIN
480 hr_utility.set_location('Entering: '||l_proc, 10);
481 --
482 person_level_check
483 (p_person_id => p_person_id
484 ,p_table_name => 'PER_PEOPLE_EXTRA_INFO'
485 ,p_effective_date => NULL
486 ,p_new_value_char1 => p_information_type
487 ,p_old_value_char1 => p_information_type_o
488 ,p_new_value_char2 => p_pei_information5
489 ,p_old_value_char2 => p_pei_information5_o
490 ,p_new_value_char3 => p_pei_information6
491 ,p_old_value_char3 => p_pei_information6_o
492 ,p_new_value_char4 => p_pei_information7
493 ,p_old_value_char4 => p_pei_information7_o
494 ,p_new_value_char5 => p_pei_information8
495 ,p_old_value_char5 => p_pei_information8_o
496 ,p_new_value_char6 => p_pei_information9
497 ,p_old_value_char6 => p_pei_information9_o
498 ,p_new_value_char7 => p_pei_information10
499 ,p_old_value_char7 => p_pei_information10_o
500 ,p_new_value_char8 => p_pei_information11
501 ,p_old_value_char8 => p_pei_information11_o
502 ,p_new_value_char9 => p_pei_information12
503 ,p_old_value_char9 => p_pei_information12_o
504 ,p_new_value_char10 => p_pei_information13
505 ,p_old_value_char10 => p_pei_information13_o );
506 --
507 hr_utility.set_location('Leaving: '||l_proc, 20);
508 --
509 END log_pei_update_changes;
510 --
511 END pqp_log_alien_data_changes;