[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DEF_SS
Source
1 package body pay_jp_def_ss as
2 /* $Header: pyjpdefs.pkb 120.1.12000000.5 2007/04/26 07:37:36 ttagawa noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_def_ss.';
7 c_def_elm constant number := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_PROC', null, 'JP');
8 c_disability_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'DISABLE_TYPE');
9 c_aged_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'ELDER_TYPE');
10 c_widow_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'WIDOW_TYPE');
11 c_working_student_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'WORKING_STUDENT_TYPE');
12 c_spouse_dep_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'SPOUSE_TYPE');
13 c_spouse_disability_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'SPOUSE_DISABLE_TYPE');
14 c_num_deps_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_DEP');
15 c_num_ageds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_ELDER_DEP');
16 c_num_aged_parents_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_ELDER_PARENT_LT');
17 c_num_specifieds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_SPECIFIC_DEP');
18 c_num_disableds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_GEN_DISABLED');
19 c_num_svr_disableds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_SEV_DISABLED');
20 c_num_svr_disableds_lt_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_SEV_DISABLED_LT');
21 -- |---------------------------------------------------------------------------|
22 -- |-----------------------< ee_datetrack_update_mode >------------------------|
23 -- |---------------------------------------------------------------------------|
24 function ee_datetrack_update_mode(
25 p_element_entry_id in number,
26 p_effective_start_date in date,
27 p_effective_end_date in date,
28 p_effective_date in date) return varchar2
29 is
30 l_datetrack_mode varchar2(30);
31 l_exists varchar2(1);
32 cursor csr_future_exists is
33 select 'Y'
34 from dual
35 where exists(
36 select null
37 from pay_element_entries_f
38 where element_entry_id = p_element_entry_id
39 and effective_start_date = p_effective_end_date + 1);
40 begin
41 if p_effective_start_date = p_effective_date then
42 l_datetrack_mode := 'CORRECTION';
43 else
44 open csr_future_exists;
45 fetch csr_future_exists into l_exists;
46 if csr_future_exists%notfound then
47 l_datetrack_mode := 'UPDATE';
48 else
49 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
50 end if;
51 end if;
52 --
53 return l_datetrack_mode;
54 end ee_datetrack_update_mode;
55 -- |---------------------------------------------------------------------------|
56 -- |-----------------------< cei_datetrack_update_mode >-----------------------|
57 -- |---------------------------------------------------------------------------|
58 function cei_datetrack_update_mode(
59 p_contact_extra_info_id in number,
60 p_effective_start_date in date,
61 p_effective_end_date in date,
62 p_effective_date in date) return varchar2
63 is
64 l_datetrack_mode varchar2(30);
65 l_exists varchar2(1);
66 cursor csr_future_exists is
67 select 'Y'
68 from dual
69 where exists(
70 select null
71 from per_contact_extra_info_f
72 where contact_extra_info_id = p_contact_extra_info_id
73 and effective_start_date = p_effective_end_date + 1);
74 begin
75 if p_effective_start_date = p_effective_date then
76 l_datetrack_mode := 'CORRECTION';
77 else
78 open csr_future_exists;
79 fetch csr_future_exists into l_exists;
80 if csr_future_exists%notfound then
81 l_datetrack_mode := 'UPDATE';
82 else
83 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
84 end if;
85 end if;
86 --
87 return l_datetrack_mode;
88 end cei_datetrack_update_mode;
89 -- |---------------------------------------------------------------------------|
90 -- |-----------------------< cei_datetrack_delete_mode >-----------------------|
91 -- |---------------------------------------------------------------------------|
92 function cei_datetrack_delete_mode(
93 p_contact_extra_info_id in number,
94 p_effective_start_date in date,
95 p_effective_end_date in date,
96 p_effective_date in date) return varchar2
97 is
98 l_datetrack_mode varchar2(30);
99 l_exists varchar2(1);
100 cursor csr_past_exists is
101 select 'Y'
102 from dual
103 where exists(
104 select null
105 from per_contact_extra_info_f
106 where contact_extra_info_id = p_contact_extra_info_id
107 and effective_end_date = p_effective_start_date - 1);
108 begin
109 if p_effective_start_date = p_effective_date then
110 open csr_past_exists;
111 fetch csr_past_exists into l_exists;
112 if csr_past_exists%notfound then
113 l_datetrack_mode := 'ZAP';
114 else
115 l_datetrack_mode := 'DELETE';
116 end if;
117 else
118 l_datetrack_mode := 'DELETE';
119 end if;
120 --
121 return l_datetrack_mode;
122 end cei_datetrack_delete_mode;
123 -- |---------------------------------------------------------------------------|
124 -- |-------------------------------< full_name >-------------------------------|
125 -- |---------------------------------------------------------------------------|
126 function full_name(
127 p_person_id in number,
128 p_effective_date in date) return varchar2
129 is
130 l_full_name per_all_people_f.full_name%type;
131 cursor csr_full_name is
132 select trim(per_information18 || ' ' || per_information19)
133 from per_all_people_f
134 where person_id = p_person_id
135 and p_effective_date
136 between effective_start_date and effective_end_date;
137 cursor csr_full_name2 is
138 select trim(per_information18 || ' ' || per_information19)
139 from per_all_people_f
140 where person_id = p_person_id
141 and start_date = effective_start_date;
142 begin
143 if p_person_id is not null then
144 open csr_full_name;
145 fetch csr_full_name into l_full_name;
146 if csr_full_name%notfound then
147 open csr_full_name2;
148 fetch csr_full_name2 into l_full_name;
149 close csr_full_name2;
150 end if;
151 close csr_full_name;
152 end if;
153 --
154 return l_full_name;
155 end full_name;
156 -- |---------------------------------------------------------------------------|
157 -- |----------------------------< insert_session >-----------------------------|
158 -- |---------------------------------------------------------------------------|
159 procedure insert_session(p_effective_date in date)
160 is
161 l_rowid rowid;
162 cursor csr_session is
163 select rowid
164 from fnd_sessions
165 where session_id = userenv('sessionid')
166 for update nowait;
167 begin
168 open csr_session;
169 fetch csr_session into l_rowid;
170 if csr_session%notfound then
171 insert into fnd_sessions(
172 session_id,
173 effective_date)
174 values( userenv('sessionid'),
175 p_effective_date);
176 else
177 update fnd_sessions
178 set effective_date = p_effective_date
179 where rowid = l_rowid;
180 end if;
181 close csr_session;
182 end insert_session;
183 -- |---------------------------------------------------------------------------|
184 -- |----------------------------< delete_session >-----------------------------|
185 -- |---------------------------------------------------------------------------|
186 procedure delete_session
187 is
188 begin
189 delete
190 from fnd_sessions
191 where session_id = userenv('sessionid');
192 end delete_session;
193 -- |---------------------------------------------------------------------------|
194 -- |--------------------------------< changed >--------------------------------|
195 -- |---------------------------------------------------------------------------|
196 function changed(
197 value1 in varchar2,
198 value2 in varchar2) return boolean
199 is
200 begin
201 if nvl(value1, hr_api.g_varchar2) <> nvl(value2, hr_api.g_varchar2) then
202 return true;
203 else
204 return false;
205 end if;
206 end changed;
207 --
208 function changed(
209 value1 in number,
210 value2 in number) return boolean
211 is
212 begin
213 if nvl(value1, hr_api.g_number) <> nvl(value2, hr_api.g_number) then
214 return true;
215 else
216 return false;
217 end if;
218 end changed;
219 --
220 function changed(
221 value1 in date,
222 value2 in date) return boolean
223 is
224 begin
225 if nvl(value1, hr_api.g_date) <> nvl(value2, hr_api.g_date) then
226 return true;
227 else
228 return false;
229 end if;
230 end changed;
231 -- |---------------------------------------------------------------------------|
232 -- |------------------------< check_submission_period >------------------------|
233 -- |---------------------------------------------------------------------------|
234 function check_submission_period(p_action_information_id in number) return date
235 is
236 cursor csr_pact is
237 select submission_period_status,
238 submission_start_date,
239 submission_end_date
240 from pay_jp_def_pact_v pact,
241 pay_assignment_actions paa,
242 pay_jp_def_assact_v assact
243 where assact.action_information_id = p_action_information_id
244 and paa.assignment_action_id = assact.assignment_action_id
245 and pact.payroll_action_id = paa.payroll_action_id;
246 l_pact_rec csr_pact%rowtype;
247 l_sysdate date;
248 begin
249 open csr_pact;
250 fetch csr_pact into l_pact_rec;
251 close csr_pact;
252 --
253 if l_pact_rec.submission_period_status = 'C' then
254 fnd_message.set_name('PAY', 'PAY_JP_DEF_PERIOD_CLOSED');
255 fnd_message.raise_error;
256 end if;
257 --
258 l_sysdate := sysdate;
259 --
260 if l_sysdate < nvl(l_pact_rec.submission_start_date, l_sysdate) then
261 fnd_message.set_name('PAY', 'PAY_JP_DEF_PERIOD_NOT_STARTED');
262 fnd_message.raise_error;
263 end if;
264 --
265 if l_sysdate > nvl(l_pact_rec.submission_end_date, l_sysdate) then
266 fnd_message.set_name('PAY', 'PAY_JP_DEF_PERIOD_EXPIRED');
267 fnd_message.raise_error;
268 end if;
269 --
270 return l_sysdate;
271 end check_submission_period;
272 --
273 procedure check_submission_period(p_action_information_id in number)
274 is
275 l_submission_date date;
276 begin
277 l_submission_date := check_submission_period(p_action_information_id);
278 end check_submission_period;
279 -- |---------------------------------------------------------------------------|
280 -- |------------------------------< get_sqlerrm >------------------------------|
281 -- |---------------------------------------------------------------------------|
282 function get_sqlerrm return varchar2
283 is
284 begin
285 if sqlcode = -20001 then
286 declare
287 l_sqlerrm varchar2(2000) := fnd_message.get;
288 begin
289 if l_sqlerrm is not null then
290 return l_sqlerrm;
291 else
292 return sqlerrm;
293 end if;
294 end;
295 else
296 return sqlerrm;
297 end if;
298 end get_sqlerrm;
299 --
300 --
301 --
302 --
303 --
304 -- |---------------------------------------------------------------------------|
305 -- |----------------------------< transfer_entry >-----------------------------|
306 -- |---------------------------------------------------------------------------|
307 procedure transfer_entry(p_rec in out nocopy pay_jp_def_entry_v%rowtype)
308 is
309 c_proc constant varchar2(61) := c_package || '.transfer_entry';
310 l_esd date;
311 l_eed date;
312 l_warning boolean;
313 l_element_link_id number;
314 begin
315 hr_utility.set_location('Entering : ' || c_proc, 10);
316 hr_utility.trace('status : ' || p_rec.status);
317 --
318 if p_rec.status = 'I' then
319 l_element_link_id := hr_entry_api.get_link(
320 p_assignment_id => p_rec.assignment_id,
321 p_element_type_id => c_def_elm,
322 p_session_date => p_rec.effective_date);
323 --
324 pay_element_entry_api.create_element_entry(
325 p_validate => false,
326 p_effective_date => p_rec.effective_date,
327 p_business_group_id => null, -- not used
328 p_assignment_id => p_rec.assignment_id,
329 p_element_link_id => l_element_link_id,
330 p_entry_type => 'E',
331 p_input_value_id1 => c_disability_type_iv,
332 p_input_value_id2 => c_aged_type_iv,
333 p_input_value_id3 => c_widow_type_iv,
334 p_input_value_id4 => c_working_student_type_iv,
335 p_input_value_id5 => c_spouse_dep_type_iv,
336 p_input_value_id6 => c_spouse_disability_type_iv,
337 p_input_value_id7 => c_num_deps_iv,
338 p_input_value_id8 => c_num_ageds_iv,
339 p_input_value_id9 => c_num_aged_parents_iv,
340 p_input_value_id10 => c_num_specifieds_iv,
341 p_input_value_id11 => c_num_disableds_iv,
342 p_input_value_id12 => c_num_svr_disableds_iv,
343 p_input_value_id13 => c_num_svr_disableds_lt_iv,
344 p_entry_value1 => p_rec.disability_type,
345 p_entry_value2 => p_rec.aged_type,
346 p_entry_value3 => p_rec.widow_type,
347 p_entry_value4 => p_rec.working_student_type,
348 p_entry_value5 => p_rec.spouse_dep_type,
349 p_entry_value6 => p_rec.spouse_disability_type,
350 p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps),
351 p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds),
352 p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt),
353 p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds),
354 p_entry_value11 => fnd_number.number_to_canonical(p_rec.num_disableds),
355 p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_svr_disableds),
356 p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt),
357 p_element_entry_id => p_rec.element_entry_id,
358 p_object_version_number => p_rec.ee_object_version_number,
359 p_effective_start_date => l_esd,
360 p_effective_end_date => l_eed,
361 p_create_warning => l_warning);
362 --
363 pay_jp_def_api.update_entry(
364 P_VALIDATE => false,
365 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
366 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
367 P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
368 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
369 elsif p_rec.status = 'Q' then
370 if changed(p_rec.disability_type , p_rec.disability_type_o)
371 or changed(p_rec.aged_type , p_rec.aged_type_o)
372 or changed(p_rec.widow_type , p_rec.widow_type_o)
373 or changed(p_rec.working_student_type , p_rec.working_student_type_o)
374 or changed(p_rec.spouse_dep_type , p_rec.spouse_dep_type_o)
375 or changed(p_rec.spouse_disability_type , p_rec.spouse_disability_type_o)
376 or changed(p_rec.num_deps , p_rec.num_deps_o)
377 or changed(p_rec.num_ageds , p_rec.num_ageds_o)
378 or changed(p_rec.num_aged_parents_lt , p_rec.num_aged_parents_lt_o)
379 or changed(p_rec.num_specifieds , p_rec.num_specifieds_o)
380 or changed(p_rec.num_disableds , p_rec.num_disableds_o)
381 or changed(p_rec.num_svr_disableds , p_rec.num_svr_disableds_o)
382 or changed(p_rec.num_svr_disableds_lt , p_rec.num_svr_disableds_lt_o) then
383 pay_element_entry_api.update_element_entry(
384 p_validate => false,
385 p_effective_date => p_rec.effective_date,
386 p_business_group_id => null, -- not used
387 p_datetrack_update_mode => p_rec.datetrack_update_mode,
388 p_element_entry_id => p_rec.element_entry_id,
389 p_object_version_number => p_rec.ee_object_version_number,
390 p_input_value_id1 => c_disability_type_iv,
391 p_input_value_id2 => c_aged_type_iv,
392 p_input_value_id3 => c_widow_type_iv,
393 p_input_value_id4 => c_working_student_type_iv,
394 p_input_value_id5 => c_spouse_dep_type_iv,
395 p_input_value_id6 => c_spouse_disability_type_iv,
396 p_input_value_id7 => c_num_deps_iv,
397 p_input_value_id8 => c_num_ageds_iv,
398 p_input_value_id9 => c_num_aged_parents_iv,
399 p_input_value_id10 => c_num_specifieds_iv,
400 p_input_value_id11 => c_num_disableds_iv,
401 p_input_value_id12 => c_num_svr_disableds_iv,
402 p_input_value_id13 => c_num_svr_disableds_lt_iv,
403 p_entry_value1 => p_rec.disability_type,
404 p_entry_value2 => p_rec.aged_type,
405 p_entry_value3 => p_rec.widow_type,
406 p_entry_value4 => p_rec.working_student_type,
407 p_entry_value5 => p_rec.spouse_dep_type,
408 p_entry_value6 => p_rec.spouse_disability_type,
409 p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps),
410 p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds),
411 p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt),
412 p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds),
413 p_entry_value11 => fnd_number.number_to_canonical(p_rec.num_disableds),
414 p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_svr_disableds),
415 p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt),
416 -- Aged Type can be defaulted to '0' after 2005/01/01
417 -- even user enterable is "No".
418 p_override_user_ent_chk => 'Y',
419 p_effective_start_date => l_esd,
420 p_effective_end_date => l_eed,
421 p_update_warning => l_warning);
422 --
423 p_rec.status := 'U';
424 --
425 pay_jp_def_api.update_entry(
426 P_VALIDATE => false,
427 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
428 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
429 P_STATUS => p_rec.status,
430 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
431 end if;
432 end if;
433 --
434 hr_utility.set_location('Leaving : ' || c_proc, 20);
435 end transfer_entry;
436 -- |---------------------------------------------------------------------------|
437 -- |----------------------------< rollback_entry >-----------------------------|
438 -- |---------------------------------------------------------------------------|
439 procedure rollback_entry(p_rec in out nocopy pay_jp_def_entry_v%rowtype)
440 is
441 c_proc constant varchar2(61) := c_package || '.rollback_entry';
442 l_esd date;
443 l_eed date;
444 l_warning boolean;
445 l_vsd date;
446 l_ved date;
447 begin
448 hr_utility.set_location('Entering : ' || c_proc, 10);
449 hr_utility.trace('status : ' || p_rec.status);
450 --
451 if p_rec.status = 'I' then
452 pay_element_entry_api.delete_element_entry(
453 p_validate => false,
454 p_effective_date => p_rec.effective_date,
455 p_datetrack_delete_mode => 'ZAP',
456 p_element_entry_id => p_rec.element_entry_id,
457 p_object_version_number => p_rec.ee_object_version_number,
458 p_effective_start_date => l_esd,
459 p_effective_end_date => l_eed,
460 p_delete_warning => l_warning);
461 --
462 p_rec.element_entry_id := null;
463 p_rec.ee_object_version_number := null;
464 --
465 pay_jp_def_api.update_entry(
466 P_VALIDATE => false,
467 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
468 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
469 P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
470 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
471 elsif p_rec.status = 'U' then
472 if p_rec.datetrack_update_mode = 'CORRECTION' then
473 pay_element_entry_api.update_element_entry(
474 p_validate => false,
475 p_effective_date => p_rec.effective_date,
476 p_business_group_id => null, -- not used
477 p_datetrack_update_mode => p_rec.datetrack_update_mode,
478 p_element_entry_id => p_rec.element_entry_id,
479 p_object_version_number => p_rec.ee_object_version_number,
480 p_input_value_id1 => c_disability_type_iv,
481 p_input_value_id2 => c_aged_type_iv,
482 p_input_value_id3 => c_widow_type_iv,
483 p_input_value_id4 => c_working_student_type_iv,
484 p_input_value_id5 => c_spouse_dep_type_iv,
485 p_input_value_id6 => c_spouse_disability_type_iv,
486 p_input_value_id7 => c_num_deps_iv,
487 p_input_value_id8 => c_num_ageds_iv,
488 p_input_value_id9 => c_num_aged_parents_iv,
489 p_input_value_id10 => c_num_specifieds_iv,
490 p_input_value_id11 => c_num_disableds_iv,
491 p_input_value_id12 => c_num_svr_disableds_iv,
492 p_input_value_id13 => c_num_svr_disableds_lt_iv,
493 p_entry_value1 => p_rec.disability_type_o,
494 p_entry_value2 => p_rec.aged_type_o,
495 p_entry_value3 => p_rec.widow_type_o,
496 p_entry_value4 => p_rec.working_student_type_o,
497 p_entry_value5 => p_rec.spouse_dep_type_o,
498 p_entry_value6 => p_rec.spouse_disability_type_o,
499 p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps_o),
500 p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds_o),
501 p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt_o),
502 p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds_o),
503 p_entry_value11 => fnd_number.number_to_canonical(p_rec.num_disableds_o),
504 p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_o),
505 p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt_o),
506 -- Aged Type could be defaulted to '0' after 2005/01/01
507 -- even user enterable is "No".
508 p_override_user_ent_chk => 'Y',
509 p_effective_start_date => l_esd,
510 p_effective_end_date => l_eed,
511 p_update_warning => l_warning);
512 else
513 --
514 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
515 -- should be current OVN - 1. See API for more details.
516 -- !!!!!
517 -- This does not work after expired, because OVN of latest record is updated.
518 -- At first, lock the current record. If locked successfully,
519 -- derive OVN of previous record.
520 --
521 pay_ele_shd.lck(
522 p_effective_date => p_rec.effective_date,
523 p_datetrack_mode => 'CORRECTION',
524 p_element_entry_id => p_rec.element_entry_id,
525 p_object_version_number => p_rec.ee_object_version_number,
526 p_validation_start_date => l_vsd,
527 p_validation_end_date => l_ved);
528 --
529 select object_version_number
530 into p_rec.ee_object_version_number
531 from pay_element_entries_f
532 where element_entry_id = p_rec.element_entry_id
533 and effective_end_date = p_rec.effective_date - 1;
534 --
535 pay_element_entry_api.delete_element_entry(
536 p_validate => false,
537 p_effective_date => p_rec.effective_date - 1,
538 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
539 p_element_entry_id => p_rec.element_entry_id,
540 p_object_version_number => p_rec.ee_object_version_number,
541 p_effective_start_date => l_esd,
542 p_effective_end_date => l_eed,
543 p_delete_warning => l_warning);
544 end if;
545 --
546 p_rec.status := 'Q';
547 --
548 pay_jp_def_api.update_entry(
549 P_VALIDATE => false,
550 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
551 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
552 P_STATUS => p_rec.status,
553 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
554 end if;
555 --
556 hr_utility.set_location('Leaving : ' || c_proc, 20);
557 end rollback_entry;
558 -- |---------------------------------------------------------------------------|
559 -- |-----------------------------< transfer_dep >------------------------------|
560 -- |---------------------------------------------------------------------------|
561 procedure transfer_dep(p_rec in out nocopy pay_jp_def_dep_v%rowtype)
562 is
563 c_proc constant varchar2(61) := c_package || '.transfer_dep';
564 l_esd date;
565 l_eed date;
566 l_effective_date date;
567 begin
568 hr_utility.set_location('Entering : ' || c_proc, 10);
569 hr_utility.trace('status : ' || p_rec.status);
570 --
571 if p_rec.status = 'I' then
572 hr_contact_extra_info_api.create_contact_extra_info(
573 p_validate => false,
574 p_effective_date => p_rec.effective_date,
575 p_contact_relationship_id => p_rec.contact_relationship_id,
576 p_information_type => 'JP_ITAX_DEPENDENT',
577 p_cei_information_category => 'JP_ITAX_DEPENDENT',
578 p_cei_information2 => p_rec.occupation,
579 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income),
580 p_cei_information4 => fnd_date.date_to_canonical(p_rec.change_date),
581 p_cei_information5 => p_rec.change_reason,
582 p_cei_information6 => p_rec.disability_type,
583 p_cei_information7 => p_rec.disability_details,
584 p_cei_information8 => p_rec.dep_type,
585 p_contact_extra_info_id => p_rec.contact_extra_info_id,
586 p_object_version_number => p_rec.cei_object_version_number,
587 p_effective_start_date => l_esd,
588 p_effective_end_date => l_eed);
589 --
590 pay_jp_def_api.update_dep(
591 P_VALIDATE => false,
592 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
593 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
594 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
595 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
596 elsif p_rec.status = 'Q' then
597 if p_rec.change_date is not null
598 or p_rec.change_reason is not null
599 or changed(p_rec.dep_type , p_rec.dep_type_o)
600 or changed(p_rec.occupation , p_rec.occupation_o)
601 or changed(p_rec.estimated_annual_income, p_rec.estimated_annual_income_o)
602 or changed(p_rec.disability_type , p_rec.disability_type_o)
603 or changed(p_rec.disability_details , p_rec.disability_details_o) then
604 hr_contact_extra_info_api.update_contact_extra_info(
605 p_validate => false,
606 p_effective_date => p_rec.effective_date,
607 p_datetrack_update_mode => p_rec.datetrack_update_mode,
608 p_contact_extra_info_id => p_rec.contact_extra_info_id,
609 p_object_version_number => p_rec.cei_object_version_number,
610 p_cei_information4 => fnd_date.date_to_canonical(p_rec.change_date),
611 p_cei_information5 => p_rec.change_reason,
612 p_cei_information8 => p_rec.dep_type,
613 p_cei_information2 => p_rec.occupation,
614 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income),
615 p_cei_information6 => p_rec.disability_type,
616 p_cei_information7 => p_rec.disability_details,
617 p_effective_start_date => l_esd,
618 p_effective_end_date => l_eed);
619 --
620 p_rec.status := 'U';
621 --
622 pay_jp_def_api.update_dep(
623 P_VALIDATE => false,
624 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
625 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
626 P_STATUS => p_rec.status,
627 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
628 end if;
629 elsif p_rec.status = 'D' then
630 if p_rec.datetrack_delete_mode = 'ZAP' then
631 l_effective_date := p_rec.effective_date;
632 else
633 l_effective_date := p_rec.effective_date - 1;
634 end if;
635 --
636 hr_contact_extra_info_api.delete_contact_extra_info(
637 p_validate => false,
638 p_effective_date => l_effective_date,
639 p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
640 p_contact_extra_info_id => p_rec.contact_extra_info_id,
641 p_object_version_number => p_rec.cei_object_version_number,
642 p_effective_start_date => l_esd,
643 p_effective_end_date => l_eed);
644 --
645 if p_rec.datetrack_delete_mode <> 'ZAP' then
646 pay_jp_def_api.update_dep(
647 P_VALIDATE => false,
648 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
649 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
650 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
651 end if;
652 end if;
653 --
654 hr_utility.set_location('Leaving : ' || c_proc, 20);
655 end transfer_dep;
656 -- |---------------------------------------------------------------------------|
657 -- |-----------------------------< rollback_dep >------------------------------|
658 -- |---------------------------------------------------------------------------|
659 procedure rollback_dep(p_rec in out nocopy pay_jp_def_dep_v%rowtype)
660 is
661 c_proc constant varchar2(61) := c_package || '.rollback_dep';
662 l_esd date;
663 l_eed date;
664 l_vsd date;
665 l_ved date;
666 begin
667 hr_utility.set_location('Entering : ' || c_proc, 10);
668 hr_utility.trace('status : ' || p_rec.status);
669 --
670 if p_rec.status = 'I' then
671 hr_contact_extra_info_api.delete_contact_extra_info(
672 p_validate => false,
673 p_effective_date => p_rec.effective_date,
674 p_datetrack_delete_mode => 'ZAP',
675 p_contact_extra_info_id => p_rec.contact_extra_info_id,
676 p_object_version_number => p_rec.cei_object_version_number,
677 p_effective_start_date => l_esd,
678 p_effective_end_date => l_eed);
679 --
680 p_rec.contact_extra_info_id := null;
681 p_rec.cei_object_version_number := null;
682 --
683 pay_jp_def_api.update_dep(
684 P_VALIDATE => false,
685 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
686 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
687 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
688 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
689 elsif p_rec.status = 'U' then
690 if p_rec.datetrack_update_mode = 'CORRECTION' then
691 hr_contact_extra_info_api.update_contact_extra_info(
692 p_validate => false,
693 p_effective_date => p_rec.effective_date,
694 p_datetrack_update_mode => p_rec.datetrack_update_mode,
695 p_contact_extra_info_id => p_rec.contact_extra_info_id,
696 p_object_version_number => p_rec.cei_object_version_number,
697 -- Rollback change_date/change_reason is not supported for CORRECTION mode.
698 p_cei_information8 => p_rec.dep_type_o,
699 p_cei_information2 => p_rec.occupation_o,
700 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income_o),
701 p_cei_information6 => p_rec.disability_type_o,
702 p_cei_information7 => p_rec.disability_details_o,
703 p_effective_start_date => l_esd,
704 p_effective_end_date => l_eed);
705 else
706 --
707 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
708 -- should be current OVN - 1. See API for more details.
709 -- !!!!!
710 -- This does not work after expired, because OVN of latest record is updated.
711 -- At first, lock the current record. If locked successfully,
712 -- derive OVN of previous record.
713 --
714 per_rei_shd.lck(
715 p_effective_date => p_rec.effective_date,
716 p_datetrack_mode => 'CORRECTION',
717 p_contact_extra_info_id => p_rec.contact_extra_info_id,
718 p_object_version_number => p_rec.cei_object_version_number,
719 p_validation_start_date => l_vsd,
720 p_validation_end_date => l_ved);
721 --
722 select object_version_number
723 into p_rec.cei_object_version_number
724 from per_contact_extra_info_f
725 where contact_extra_info_id = p_rec.contact_extra_info_id
726 and effective_end_date = p_rec.effective_date - 1;
727 --
728 hr_contact_extra_info_api.delete_contact_extra_info(
729 p_validate => false,
730 p_effective_date => p_rec.effective_date - 1,
731 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
732 p_contact_extra_info_id => p_rec.contact_extra_info_id,
733 p_object_version_number => p_rec.cei_object_version_number,
734 p_effective_start_date => l_esd,
735 p_effective_end_date => l_eed);
736 end if;
737 --
738 p_rec.status := 'Q';
739 --
740 pay_jp_def_api.update_dep(
741 P_VALIDATE => false,
742 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
743 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
744 P_STATUS => p_rec.status,
745 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
746 elsif p_rec.status = 'D' then
747 if p_rec.datetrack_delete_mode = 'ZAP' then
748 --
749 -- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
750 -- for ZAP case.
751 --
752 hr_contact_extra_info_api.create_contact_extra_info(
753 p_validate => false,
754 p_effective_date => p_rec.effective_date,
755 p_contact_relationship_id => p_rec.contact_relationship_id,
756 p_information_type => 'JP_ITAX_DEPENDENT',
757 p_cei_information_category => 'JP_ITAX_DEPENDENT',
758 p_cei_information2 => p_rec.occupation_o,
759 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income_o),
760 p_cei_information6 => p_rec.disability_type_o,
761 p_cei_information7 => p_rec.disability_details_o,
762 p_cei_information8 => p_rec.dep_type_o,
763 p_contact_extra_info_id => p_rec.contact_extra_info_id,
764 p_object_version_number => p_rec.cei_object_version_number,
765 p_effective_start_date => l_esd,
766 p_effective_end_date => l_eed);
767 else
768 hr_contact_extra_info_api.delete_contact_extra_info(
769 p_validate => false,
770 p_effective_date => p_rec.effective_date - 1,
771 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
772 p_contact_extra_info_id => p_rec.contact_extra_info_id,
773 p_object_version_number => p_rec.cei_object_version_number,
774 p_effective_start_date => l_esd,
775 p_effective_end_date => l_eed);
776 end if;
777 --
778 pay_jp_def_api.update_dep(
779 P_VALIDATE => false,
780 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
781 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
782 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
783 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
784 end if;
785 --
786 hr_utility.set_location('Leaving : ' || c_proc, 20);
787 end rollback_dep;
788 -- |---------------------------------------------------------------------------|
789 -- |----------------------------< transfer_dep_oe >----------------------------|
790 -- |---------------------------------------------------------------------------|
791 procedure transfer_dep_oe(p_rec in out nocopy pay_jp_def_dep_oe_v%rowtype)
792 is
793 c_proc constant varchar2(61) := c_package || '.transfer_dep_oe';
794 l_esd date;
795 l_eed date;
796 l_effective_date date;
797 begin
798 hr_utility.set_location('Entering : ' || c_proc, 10);
799 hr_utility.trace('status : ' || p_rec.status);
800 --
801 if p_rec.status = 'I' then
802 hr_contact_extra_info_api.create_contact_extra_info(
803 p_validate => false,
804 p_effective_date => p_rec.effective_date,
805 p_contact_relationship_id => p_rec.contact_relationship_id,
806 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
807 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
808 p_cei_information1 => p_rec.occupation,
809 p_cei_information2 => fnd_date.date_to_canonical(p_rec.change_date),
810 p_cei_information3 => p_rec.change_reason,
811 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id),
812 p_contact_extra_info_id => p_rec.contact_extra_info_id,
813 p_object_version_number => p_rec.cei_object_version_number,
814 p_effective_start_date => l_esd,
815 p_effective_end_date => l_eed);
816 --
817 pay_jp_def_api.update_dep_oe(
818 P_VALIDATE => false,
819 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
820 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
821 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
822 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
823 elsif p_rec.status = 'Q' then
824 if p_rec.change_date is not null
825 or p_rec.change_reason is not null
826 or changed(p_rec.occupation , p_rec.occupation_o)
827 or changed(p_rec.oe_contact_relationship_id , p_rec.oe_contact_relationship_id_o) then
828 hr_contact_extra_info_api.update_contact_extra_info(
829 p_validate => false,
830 p_effective_date => p_rec.effective_date,
831 p_datetrack_update_mode => p_rec.datetrack_update_mode,
832 p_contact_extra_info_id => p_rec.contact_extra_info_id,
833 p_object_version_number => p_rec.cei_object_version_number,
834 p_cei_information2 => fnd_date.date_to_canonical(p_rec.change_date),
835 p_cei_information3 => p_rec.change_reason,
836 p_cei_information1 => p_rec.occupation,
837 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id),
838 p_effective_start_date => l_esd,
839 p_effective_end_date => l_eed);
840 --
841 p_rec.status := 'U';
842 --
843 pay_jp_def_api.update_dep_oe(
844 P_VALIDATE => false,
845 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
846 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
847 P_STATUS => p_rec.status,
848 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
849 end if;
850 elsif p_rec.status = 'D' then
851 if p_rec.datetrack_delete_mode = 'ZAP' then
852 l_effective_date := p_rec.effective_date;
853 else
854 l_effective_date := p_rec.effective_date - 1;
855 end if;
856 --
857 hr_contact_extra_info_api.delete_contact_extra_info(
858 p_validate => false,
859 p_effective_date => l_effective_date,
860 p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
861 p_contact_extra_info_id => p_rec.contact_extra_info_id,
862 p_object_version_number => p_rec.cei_object_version_number,
863 p_effective_start_date => l_esd,
864 p_effective_end_date => l_eed);
865 --
866 if p_rec.datetrack_delete_mode <> 'ZAP' then
867 pay_jp_def_api.update_dep(
868 P_VALIDATE => false,
869 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
870 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
871 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
872 end if;
873 end if;
874 --
875 hr_utility.set_location('Leaving : ' || c_proc, 20);
876 end transfer_dep_oe;
877 -- |---------------------------------------------------------------------------|
878 -- |-----------------------------< rollback_dep >------------------------------|
879 -- |---------------------------------------------------------------------------|
880 procedure rollback_dep_oe(p_rec in out nocopy pay_jp_def_dep_oe_v%rowtype)
881 is
882 c_proc constant varchar2(61) := c_package || '.rollback_dep_oe';
883 l_esd date;
884 l_eed date;
885 l_vsd date;
886 l_ved date;
887 begin
888 hr_utility.set_location('Entering : ' || c_proc, 10);
889 hr_utility.trace('status : ' || p_rec.status);
890 --
891 if p_rec.status = 'I' then
892 hr_contact_extra_info_api.delete_contact_extra_info(
893 p_validate => false,
894 p_effective_date => p_rec.effective_date,
895 p_datetrack_delete_mode => 'ZAP',
896 p_contact_extra_info_id => p_rec.contact_extra_info_id,
897 p_object_version_number => p_rec.cei_object_version_number,
898 p_effective_start_date => l_esd,
899 p_effective_end_date => l_eed);
900 --
901 p_rec.contact_extra_info_id := null;
902 p_rec.cei_object_version_number := null;
903 --
904 pay_jp_def_api.update_dep_oe(
905 P_VALIDATE => false,
906 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
907 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
908 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
909 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
910 elsif p_rec.status = 'U' then
911 if p_rec.datetrack_update_mode = 'CORRECTION' then
912 hr_contact_extra_info_api.update_contact_extra_info(
913 p_validate => false,
914 p_effective_date => p_rec.effective_date,
915 p_datetrack_update_mode => p_rec.datetrack_update_mode,
916 p_contact_extra_info_id => p_rec.contact_extra_info_id,
917 p_object_version_number => p_rec.cei_object_version_number,
918 -- Rollback change_date/change_reason is not supported for CORRECTION mode.
919 p_cei_information1 => p_rec.occupation_o,
920 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id_o),
921 p_effective_start_date => l_esd,
922 p_effective_end_date => l_eed);
923 else
924 --
925 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
926 -- should be current OVN - 1. See API for more details.
927 -- !!!!!
928 -- This does not work after expired, because OVN of latest record is updated.
929 -- At first, lock the current record. If locked successfully,
930 -- derive OVN of previous record.
931 --
932 per_rei_shd.lck(
933 p_effective_date => p_rec.effective_date,
934 p_datetrack_mode => 'CORRECTION',
935 p_contact_extra_info_id => p_rec.contact_extra_info_id,
936 p_object_version_number => p_rec.cei_object_version_number,
937 p_validation_start_date => l_vsd,
938 p_validation_end_date => l_ved);
939 --
940 select object_version_number
941 into p_rec.cei_object_version_number
942 from per_contact_extra_info_f
943 where contact_extra_info_id = p_rec.contact_extra_info_id
944 and effective_end_date = p_rec.effective_date - 1;
945 --
946 hr_contact_extra_info_api.delete_contact_extra_info(
947 p_validate => false,
948 p_effective_date => p_rec.effective_date - 1,
949 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
950 p_contact_extra_info_id => p_rec.contact_extra_info_id,
951 p_object_version_number => p_rec.cei_object_version_number,
952 p_effective_start_date => l_esd,
953 p_effective_end_date => l_eed);
954 end if;
955 --
956 p_rec.status := 'Q';
957 --
958 pay_jp_def_api.update_dep_oe(
959 P_VALIDATE => false,
960 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
961 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
962 P_STATUS => p_rec.status,
963 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
964 elsif p_rec.status = 'D' then
965 if p_rec.datetrack_delete_mode = 'ZAP' then
966 --
967 -- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
968 -- for ZAP case.
969 --
970 hr_contact_extra_info_api.create_contact_extra_info(
971 p_validate => false,
972 p_effective_date => p_rec.effective_date,
973 p_contact_relationship_id => p_rec.contact_relationship_id,
974 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
975 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
976 p_cei_information1 => p_rec.occupation_o,
977 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id_o),
978 p_contact_extra_info_id => p_rec.contact_extra_info_id,
979 p_object_version_number => p_rec.cei_object_version_number,
980 p_effective_start_date => l_esd,
981 p_effective_end_date => l_eed);
982 else
983 hr_contact_extra_info_api.delete_contact_extra_info(
984 p_validate => false,
985 p_effective_date => p_rec.effective_date - 1,
986 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
987 p_contact_extra_info_id => p_rec.contact_extra_info_id,
988 p_object_version_number => p_rec.cei_object_version_number,
989 p_effective_start_date => l_esd,
990 p_effective_end_date => l_eed);
991 end if;
992 --
993 pay_jp_def_api.update_dep_oe(
994 P_VALIDATE => false,
995 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
996 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
997 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
998 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
999 end if;
1000 --
1001 hr_utility.set_location('Leaving : ' || c_proc, 20);
1002 end rollback_dep_oe;
1003 -- |---------------------------------------------------------------------------|
1004 -- |----------------------------< transfer_dep_os >----------------------------|
1005 -- |---------------------------------------------------------------------------|
1006 procedure transfer_dep_os(p_rec in out nocopy pay_jp_def_dep_os_v%rowtype)
1007 is
1008 c_proc constant varchar2(61) := c_package || '.transfer_dep_os';
1009 l_esd date;
1010 l_eed date;
1011 l_effective_date date;
1012 begin
1013 hr_utility.set_location('Entering : ' || c_proc, 10);
1014 hr_utility.trace('status : ' || p_rec.status);
1015 --
1016 if p_rec.status = 'I' then
1017 hr_contact_extra_info_api.create_contact_extra_info(
1018 p_validate => false,
1019 p_effective_date => p_rec.effective_date,
1020 p_contact_relationship_id => p_rec.contact_relationship_id,
1021 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1022 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1023 p_cei_information1 => p_rec.occupation,
1024 p_cei_information2 => p_rec.os_salary_payer_name,
1025 p_cei_information3 => p_rec.os_salary_payer_address,
1026 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1027 p_object_version_number => p_rec.cei_object_version_number,
1028 p_effective_start_date => l_esd,
1029 p_effective_end_date => l_eed);
1030 --
1031 pay_jp_def_api.update_dep_os(
1032 P_VALIDATE => false,
1033 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1034 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1035 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1036 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1037 elsif p_rec.status = 'Q' then
1038 if changed(p_rec.occupation , p_rec.occupation_o)
1039 or changed(p_rec.os_salary_payer_name , p_rec.os_salary_payer_name_o)
1040 or changed(p_rec.os_salary_payer_address, p_rec.os_salary_payer_address_o) then
1041 p_rec.status := 'U';
1042 --
1043 hr_contact_extra_info_api.update_contact_extra_info(
1044 p_validate => false,
1045 p_effective_date => p_rec.effective_date,
1046 p_datetrack_update_mode => p_rec.datetrack_update_mode,
1047 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1048 p_object_version_number => p_rec.cei_object_version_number,
1049 p_cei_information1 => p_rec.occupation,
1050 p_cei_information2 => p_rec.os_salary_payer_name,
1051 p_cei_information3 => p_rec.os_salary_payer_address,
1052 p_effective_start_date => l_esd,
1053 p_effective_end_date => l_eed);
1054 --
1055 p_rec.status := 'U';
1056 --
1057 pay_jp_def_api.update_dep_os(
1058 P_VALIDATE => false,
1059 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1060 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1061 P_STATUS => p_rec.status,
1062 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1063 end if;
1064 elsif p_rec.status = 'D' then
1065 if p_rec.datetrack_delete_mode = 'ZAP' then
1066 l_effective_date := p_rec.effective_date;
1067 else
1068 l_effective_date := p_rec.effective_date - 1;
1069 end if;
1070 --
1071 hr_contact_extra_info_api.delete_contact_extra_info(
1072 p_validate => false,
1073 p_effective_date => l_effective_date,
1074 p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
1075 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1076 p_object_version_number => p_rec.cei_object_version_number,
1077 p_effective_start_date => l_esd,
1078 p_effective_end_date => l_eed);
1079 --
1080 if p_rec.datetrack_delete_mode <> 'ZAP' then
1081 pay_jp_def_api.update_dep(
1082 P_VALIDATE => false,
1083 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1084 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1085 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1086 end if;
1087 end if;
1088 --
1089 hr_utility.set_location('Leaving : ' || c_proc, 20);
1090 end transfer_dep_os;
1091 -- |---------------------------------------------------------------------------|
1092 -- |-----------------------------< rollback_dep >------------------------------|
1093 -- |---------------------------------------------------------------------------|
1094 procedure rollback_dep_os(p_rec in out nocopy pay_jp_def_dep_os_v%rowtype)
1095 is
1096 c_proc constant varchar2(61) := c_package || '.rollback_dep_os';
1097 l_esd date;
1098 l_eed date;
1099 l_vsd date;
1100 l_ved date;
1101 begin
1102 hr_utility.set_location('Entering : ' || c_proc, 10);
1103 hr_utility.trace('status : ' || p_rec.status);
1104 --
1105 if p_rec.status = 'I' then
1106 hr_contact_extra_info_api.delete_contact_extra_info(
1107 p_validate => false,
1108 p_effective_date => p_rec.effective_date,
1109 p_datetrack_delete_mode => 'ZAP',
1110 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1111 p_object_version_number => p_rec.cei_object_version_number,
1112 p_effective_start_date => l_esd,
1113 p_effective_end_date => l_eed);
1114 --
1115 p_rec.contact_extra_info_id := null;
1116 p_rec.cei_object_version_number := null;
1117 --
1118 pay_jp_def_api.update_dep_os(
1119 P_VALIDATE => false,
1120 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1121 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1122 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1123 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1124 elsif p_rec.status = 'U' then
1125 if p_rec.datetrack_update_mode = 'CORRECTION' then
1126 hr_contact_extra_info_api.update_contact_extra_info(
1127 p_validate => false,
1128 p_effective_date => p_rec.effective_date,
1129 p_datetrack_update_mode => p_rec.datetrack_update_mode,
1130 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1131 p_object_version_number => p_rec.cei_object_version_number,
1132 p_cei_information1 => p_rec.occupation_o,
1133 p_cei_information2 => p_rec.os_salary_payer_name_o,
1134 p_cei_information3 => p_rec.os_salary_payer_address_o,
1135 p_effective_start_date => l_esd,
1136 p_effective_end_date => l_eed);
1137 else
1138 --
1139 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
1140 -- should be current OVN - 1. See API for more details.
1141 -- !!!!!
1142 -- This does not work after expired, because OVN of latest record is updated.
1143 -- At first, lock the current record. If locked successfully,
1144 -- derive OVN of previous record.
1145 --
1146 per_rei_shd.lck(
1147 p_effective_date => p_rec.effective_date,
1148 p_datetrack_mode => 'CORRECTION',
1149 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1150 p_object_version_number => p_rec.cei_object_version_number,
1151 p_validation_start_date => l_vsd,
1152 p_validation_end_date => l_ved);
1153 --
1154 select object_version_number
1155 into p_rec.cei_object_version_number
1156 from per_contact_extra_info_f
1157 where contact_extra_info_id = p_rec.contact_extra_info_id
1158 and effective_end_date = p_rec.effective_date - 1;
1159 --
1160 hr_contact_extra_info_api.delete_contact_extra_info(
1161 p_validate => false,
1162 p_effective_date => p_rec.effective_date - 1,
1163 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
1164 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1165 p_object_version_number => p_rec.cei_object_version_number,
1166 p_effective_start_date => l_esd,
1167 p_effective_end_date => l_eed);
1168 end if;
1169 --
1170 p_rec.status := 'Q';
1171 --
1172 pay_jp_def_api.update_dep_os(
1173 P_VALIDATE => false,
1174 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1175 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1176 P_STATUS => p_rec.status,
1177 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1178 elsif p_rec.status = 'D' then
1179 if p_rec.datetrack_delete_mode = 'ZAP' then
1180 --
1181 -- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
1182 -- for ZAP case.
1183 --
1184 hr_contact_extra_info_api.create_contact_extra_info(
1185 p_validate => false,
1186 p_effective_date => p_rec.effective_date,
1187 p_contact_relationship_id => p_rec.contact_relationship_id,
1188 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1189 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1190 p_cei_information1 => p_rec.occupation_o,
1191 p_cei_information2 => p_rec.os_salary_payer_name_o,
1192 p_cei_information3 => p_rec.os_salary_payer_address_o,
1193 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1194 p_object_version_number => p_rec.cei_object_version_number,
1195 p_effective_start_date => l_esd,
1196 p_effective_end_date => l_eed);
1197 else
1198 hr_contact_extra_info_api.delete_contact_extra_info(
1199 p_validate => false,
1200 p_effective_date => p_rec.effective_date - 1,
1201 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
1202 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1203 p_object_version_number => p_rec.cei_object_version_number,
1204 p_effective_start_date => l_esd,
1205 p_effective_end_date => l_eed);
1206 end if;
1207 --
1208 pay_jp_def_api.update_dep_os(
1209 P_VALIDATE => false,
1210 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1211 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1212 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1213 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1214 end if;
1215 --
1216 hr_utility.set_location('Leaving : ' || c_proc, 20);
1217 end rollback_dep_os;
1218 --
1219 --
1220 --
1221 --
1222 --
1223 -- |---------------------------------------------------------------------------|
1224 -- |------------------------------< lock_assact >------------------------------|
1225 -- |---------------------------------------------------------------------------|
1226 procedure lock_assact(
1227 p_action_information_id in number,
1228 p_object_version_number in number,
1229 p_rec out nocopy pay_jp_def_assact_v%rowtype)
1230 is
1231 begin
1232 select *
1233 into p_rec
1234 from pay_jp_def_assact_v
1235 where action_information_id = p_action_information_id
1236 for update nowait;
1237 --
1238 if p_rec.object_version_number <> p_object_version_number then
1239 fnd_message.set_name('FND', 'FND_RECORD_CHANGED_ERROR');
1240 fnd_message.raise_error;
1241 end if;
1242 exception
1243 when hr_api.object_locked then
1244 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
1245 fnd_message.raise_error;
1246 when no_data_found then
1247 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
1248 fnd_message.raise_error;
1249 end lock_assact;
1250 -- |---------------------------------------------------------------------------|
1251 -- |--------------------------------< do_init >--------------------------------|
1252 -- |---------------------------------------------------------------------------|
1253 procedure do_init(
1254 p_action_information_id in number,
1255 p_object_version_number in out nocopy number)
1256 is
1257 c_proc constant varchar2(61) := c_package || 'do_new';
1258 l_assact_rec pay_jp_def_assact_v%rowtype;
1259 l_emp_rec pay_jp_def_emp_v%rowtype;
1260 l_entry_rec pay_jp_def_entry_v%rowtype;
1261 l_person_id number;
1262 l_business_group_id number;
1263 l_contact_person_id number;
1264 l_action_information_id number;
1265 l_object_version_number number;
1266 --
1267 cursor csr_emp(
1268 p_assignment_id number,
1269 p_effective_date date) is
1270 select per.person_id,
1271 per.business_group_id,
1272 per.last_name last_name_kana,
1273 per.first_name first_name_kana,
1274 per.per_information18 last_name,
1275 per.per_information19 first_name,
1276 per.date_of_birth,
1277 per.date_of_death,
1278 per.sex,
1279 decode(adrr.address_id, null, adrc.postal_code, adrr.postal_code) postal_code,
1280 trim(substrb(decode(adrr.address_id, null,
1281 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
1282 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240))
1283 address
1284 from per_addresses adrc,
1285 per_addresses adrr,
1286 per_all_people_f per,
1287 per_all_assignments_f asg
1288 where asg.assignment_id = p_assignment_id
1289 and p_effective_date
1290 between asg.effective_start_date and asg.effective_end_date
1291 and per.person_id = asg.person_id
1292 and p_effective_date
1293 between per.effective_start_date and per.effective_end_date
1294 and adrr.person_id(+) = per.person_id
1295 and adrr.address_type(+) = 'JP_R'
1296 and p_effective_date
1297 between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
1298 and adrc.person_id(+) = per.person_id
1299 and adrc.address_type(+) = 'JP_C'
1300 and p_effective_date
1301 between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date);
1302 cursor csr_household_head(
1303 p_person_id number,
1304 p_effective_date date) is
1305 select ctr.contact_relationship_id,
1306 ctr.contact_person_id,
1307 ctr.contact_type
1308 from per_contact_relationships ctr
1309 where ctr.person_id = p_person_id
1310 and ctr.cont_information3 = 'Y'
1311 and p_effective_date
1312 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date);
1313 cursor csr_married_flag(
1314 p_person_id number,
1315 p_effective_date date) is
1316 select 'Y'
1317 from dual
1318 where exists(
1319 select null
1320 from per_contact_relationships ctr
1321 where ctr.person_id = p_person_id
1322 and ctr.contact_type = 'S'
1323 and p_effective_date
1324 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date));
1325 --
1326 cursor csr_entry(
1327 p_business_group_id number,
1328 p_assignment_id number,
1329 p_effective_date date) is
1330 select pee.element_entry_id,
1331 pee.effective_start_date,
1332 pee.effective_end_date,
1333 pee.object_version_number,
1334 peev.input_value_id,
1335 peev.screen_entry_value
1336 from pay_element_entry_values_f peev,
1337 pay_element_entries_f pee,
1338 pay_element_links_f pel
1339 where pel.element_type_id = c_def_elm
1340 and pel.business_group_id + 0 = p_business_group_id
1341 and p_effective_date
1342 between pel.effective_start_date and pel.effective_end_date
1343 and pee.assignment_id = p_assignment_id
1344 and pee.element_link_id = pel.element_link_id
1345 and p_effective_date
1346 between pee.effective_start_date and pee.effective_end_date
1347 and pee.entry_type = 'E'
1348 and peev.element_entry_id = pee.element_entry_id
1349 and peev.effective_start_date = pee.effective_start_date
1350 and peev.effective_end_date = pee.effective_end_date;
1351 --
1352 cursor csr_dep(
1353 p_person_id number,
1354 p_effective_date date) is
1355 select cei.contact_extra_info_id,
1356 cei.effective_start_date,
1357 cei.effective_end_date,
1358 cei.object_version_number,
1359 ctr.contact_relationship_id,
1360 per.last_name LAST_NAME_KANA,
1361 per.first_name FIRST_NAME_KANA,
1362 per.per_information18 LAST_NAME,
1363 per.per_information19 FIRST_NAME,
1364 ctr.contact_type,
1365 per.date_of_birth,
1366 per.date_of_death,
1367 trim(substrb(decode(adrr.address_id, null,
1368 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
1369 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240))
1370 ADDRESS,
1371 cei.cei_information8 DEP_TYPE,
1372 cei.cei_information2 OCCUPATION,
1373 fnd_number.canonical_to_number(cei.cei_information3) ESTIMATED_ANNUAL_INCOME,
1374 cei.cei_information6 DISABILITY_TYPE,
1375 cei.cei_information7 DISABILITY_DETAILS
1376 from per_all_people_f per,
1377 per_addresses adrc,
1378 per_addresses adrr,
1379 per_contact_extra_info_f cei,
1380 per_contact_relationships ctr
1381 where ctr.person_id = p_person_id
1382 and cei.contact_relationship_id = ctr.contact_relationship_id
1383 and cei.cei_information_category = 'JP_ITAX_DEPENDENT'
1384 and p_effective_date
1385 between cei.effective_start_date and cei.effective_end_date
1386 and adrr.person_id(+) = ctr.contact_person_id
1387 and adrr.address_type(+) = 'JP_R'
1388 and p_effective_date
1389 between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
1390 and adrc.person_id(+) = ctr.contact_person_id
1391 and adrc.address_type(+) = 'JP_C'
1392 and p_effective_date
1393 between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date)
1394 and per.person_id = ctr.contact_person_id
1395 /* CEI guarantees that person record exists as of effective_date */
1396 and p_effective_date
1397 between per.effective_start_date and per.effective_end_date
1398 order by
1399 decode(ctr.contact_type, 'S', 1, 2),
1400 per.date_of_birth,
1401 per.full_name;
1402 cursor csr_dep_oe(
1403 p_person_id number,
1404 p_effective_date date) is
1405 select cei.contact_extra_info_id,
1406 cei.effective_start_date,
1407 cei.effective_end_date,
1408 cei.object_version_number,
1409 ctr.contact_relationship_id,
1410 per.last_name LAST_NAME_KANA,
1411 per.first_name FIRST_NAME_KANA,
1412 per.per_information18 LAST_NAME,
1413 per.per_information19 FIRST_NAME,
1414 ctr.contact_type,
1415 per.date_of_birth,
1416 per.date_of_death,
1417 trim(substrb(decode(adrr.address_id, null,
1418 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
1419 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240))
1420 ADDRESS,
1421 cei.cei_information1 OCCUPATION,
1422 -- Do not return contact_relationship_id
1423 fnd_number.canonical_to_number(cei.cei_information5) OE_CONTACT_RELATIONSHIP_ID_O,
1424 ctr2.contact_relationship_id OE_CONTACT_RELATIONSHIP_ID,
1425 ctr2.contact_person_id OE_CONTACT_PERSON_ID,
1426 ctr2.contact_type OE_CONTACT_TYPE,
1427 trim(substrb(decode(adrr2.address_id, null,
1428 adrc2.address_line1 || adrc2.address_line2 || adrc2.address_line3,
1429 adrr2.address_line1 || adrr2.address_line2 || adrr2.address_line3), 1, 240))
1430 OE_ADDRESS
1431 from per_addresses adrc2,
1432 per_addresses adrr2,
1433 per_contact_relationships ctr2,
1434 per_all_people_f per,
1435 per_addresses adrc,
1436 per_addresses adrr,
1437 per_contact_extra_info_f cei,
1438 per_contact_relationships ctr
1439 where ctr.person_id = p_person_id
1440 and cei.contact_relationship_id = ctr.contact_relationship_id
1441 and cei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
1442 and p_effective_date
1443 between cei.effective_start_date and cei.effective_end_date
1444 and adrr.person_id(+) = ctr.contact_person_id
1445 and adrr.address_type(+) = 'JP_R'
1446 and p_effective_date
1447 between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
1448 and adrc.person_id(+) = ctr.contact_person_id
1449 and adrc.address_type(+) = 'JP_C'
1450 and p_effective_date
1451 between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date)
1452 and per.person_id = ctr.contact_person_id
1453 /* CEI guarantees that person record exists as of effective_date */
1454 and p_effective_date
1455 between per.effective_start_date and per.effective_end_date
1456 /* No need to check date range of CTR */
1457 and ctr2.contact_relationship_id(+) = fnd_number.canonical_to_number(cei.cei_information5)
1458 and adrr2.person_id(+) = ctr2.contact_person_id
1459 and adrr2.address_type(+) = 'JP_R'
1460 and p_effective_date
1461 between adrr2.date_from(+) and nvl(adrr2.date_to(+), p_effective_date)
1462 and adrc2.person_id(+) = ctr2.contact_person_id
1463 and adrc2.address_type(+) = 'JP_C'
1464 and p_effective_date
1465 between adrc2.date_from(+) and nvl(adrc2.date_to(+), p_effective_date)
1466 order by
1467 decode(ctr.contact_type, 'S', 1, 2),
1468 per.date_of_birth,
1469 per.full_name;
1470 cursor csr_dep_os(
1471 p_person_id number,
1472 p_effective_date date) is
1473 select cei.contact_extra_info_id,
1474 cei.effective_start_date,
1475 cei.effective_end_date,
1476 cei.object_version_number,
1477 ctr.contact_relationship_id,
1478 per.last_name LAST_NAME_KANA,
1479 per.first_name FIRST_NAME_KANA,
1480 per.per_information18 LAST_NAME,
1481 per.per_information19 FIRST_NAME,
1482 ctr.contact_type,
1483 per.date_of_birth,
1484 per.date_of_death,
1485 cei.cei_information1 OCCUPATION,
1486 cei.cei_information2 OS_SALARY_PAYER_NAME,
1487 cei.cei_information3 OS_SALARY_PAYER_ADDRESS
1488 from per_all_people_f per,
1489 per_contact_extra_info_f cei,
1490 per_contact_relationships ctr
1491 where ctr.person_id = p_person_id
1492 and cei.contact_relationship_id = ctr.contact_relationship_id
1493 and cei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
1494 and p_effective_date
1495 between cei.effective_start_date and cei.effective_end_date
1496 and per.person_id = ctr.contact_person_id
1497 /* CEI guarantees that person record exists as of effective_date */
1498 and p_effective_date
1499 between per.effective_start_date and per.effective_end_date
1500 order by
1501 decode(ctr.contact_type, 'S', 1, 2),
1502 per.date_of_birth,
1503 per.full_name;
1504 begin
1505 hr_utility.set_location('Entering : ' || c_proc, 10);
1506 --
1507 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
1508 --
1509 check_submission_period(p_action_information_id);
1510 --
1511 if l_assact_rec.transaction_status not in ('U', 'N') then
1512 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
1513 fnd_message.raise_error;
1514 end if;
1515 --
1516 delete
1517 from pay_action_information
1518 where action_context_id = l_assact_rec.assignment_action_id
1519 and action_context_type = 'AAP'
1520 and action_information_category <> 'JP_DEF_ASSACT';
1521 --
1522 -- JP_DEF_EMP
1523 --
1524 open csr_emp(l_assact_rec.assignment_id, l_assact_rec.effective_date);
1525 fetch csr_emp into
1526 l_person_id,
1527 l_business_group_id,
1528 l_emp_rec.last_name_kana,
1529 l_emp_rec.first_name_kana,
1530 l_emp_rec.last_name,
1531 l_emp_rec.first_name,
1532 l_emp_rec.date_of_birth,
1533 l_emp_rec.date_of_death,
1534 l_emp_rec.sex,
1535 l_emp_rec.postal_code,
1536 l_emp_rec.address;
1537 close csr_emp;
1538 --
1539 open csr_household_head(l_person_id, l_assact_rec.effective_date);
1540 fetch csr_household_head into
1541 l_emp_rec.household_head_ctr_id,
1542 l_contact_person_id,
1543 l_emp_rec.household_head_contact_type;
1544 if csr_household_head%found then
1545 l_emp_rec.household_head_full_name := full_name(l_contact_person_id, l_assact_rec.effective_date);
1546 else
1547 l_emp_rec.household_head_ctr_id := null;
1548 l_emp_rec.household_head_full_name := null;
1549 l_emp_rec.household_head_contact_type := null;
1550 end if;
1551 close csr_household_head;
1552 --
1553 open csr_married_flag(l_person_id, l_assact_rec.effective_date);
1554 fetch csr_married_flag into l_emp_rec.married_flag;
1555 if csr_married_flag%notfound then
1556 l_emp_rec.married_flag := 'N';
1557 end if;
1558 close csr_married_flag;
1559 --
1560 -- JP_DEF_ENTRY
1561 --
1562 l_entry_rec.status := 'I';
1563 for l_rec in csr_entry(l_business_group_id, l_assact_rec.assignment_id, l_assact_rec.effective_date) loop
1564 if csr_entry%rowcount = 1 then
1565 l_entry_rec.status := 'Q';
1566 l_entry_rec.datetrack_update_mode := ee_datetrack_update_mode(l_rec.element_entry_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date);
1567 l_entry_rec.element_entry_id := l_rec.element_entry_id;
1568 l_entry_rec.ee_object_version_number := l_rec.object_version_number;
1569 end if;
1570 --
1571 if l_rec.input_value_id = c_disability_type_iv then
1572 l_entry_rec.disability_type_o := l_rec.screen_entry_value;
1573 elsif l_rec.input_value_id = c_aged_type_iv then
1574 l_entry_rec.aged_type_o := l_rec.screen_entry_value;
1575 elsif l_rec.input_value_id = c_widow_type_iv then
1576 l_entry_rec.widow_type_o := l_rec.screen_entry_value;
1577 elsif l_rec.input_value_id = c_working_student_type_iv then
1578 l_entry_rec.working_student_type_o := l_rec.screen_entry_value;
1579 elsif l_rec.input_value_id = c_spouse_dep_type_iv then
1580 l_entry_rec.spouse_dep_type_o := l_rec.screen_entry_value;
1581 elsif l_rec.input_value_id = c_spouse_disability_type_iv then
1582 l_entry_rec.spouse_disability_type_o := l_rec.screen_entry_value;
1583 elsif l_rec.input_value_id = c_num_deps_iv then
1584 l_entry_rec.num_deps_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1585 elsif l_rec.input_value_id = c_num_ageds_iv then
1586 l_entry_rec.num_ageds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1587 elsif l_rec.input_value_id = c_num_aged_parents_iv then
1588 l_entry_rec.num_aged_parents_lt_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1589 elsif l_rec.input_value_id = c_num_specifieds_iv then
1590 l_entry_rec.num_specifieds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1591 elsif l_rec.input_value_id = c_num_disableds_iv then
1592 l_entry_rec.num_disableds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1593 elsif l_rec.input_value_id = c_num_svr_disableds_iv then
1594 l_entry_rec.num_svr_disableds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1595 elsif l_rec.input_value_id = c_num_svr_disableds_lt_iv then
1596 l_entry_rec.num_svr_disableds_lt_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1597 end if;
1598 end loop;
1599 --
1600 pay_jp_def_api.create_entry(
1601 P_ASSIGNMENT_ACTION_ID => l_assact_rec.assignment_action_id,
1602 P_ASSIGNMENT_ID => l_assact_rec.assignment_id,
1603 P_EFFECTIVE_DATE => l_assact_rec.effective_date,
1604 P_STATUS => l_entry_rec.status,
1605 P_DATETRACK_UPDATE_MODE => l_entry_rec.datetrack_update_mode,
1606 P_ELEMENT_ENTRY_ID => l_entry_rec.element_entry_id,
1607 P_EE_OBJECT_VERSION_NUMBER => l_entry_rec.ee_object_version_number,
1608 P_DISABILITY_TYPE => '0',
1609 P_DISABILITY_TYPE_O => l_entry_rec.disability_type_o,
1610 P_AGED_TYPE => '0',
1611 P_AGED_TYPE_O => l_entry_rec.aged_type_o,
1612 P_WIDOW_TYPE => '0',
1613 P_WIDOW_TYPE_O => l_entry_rec.widow_type_o,
1614 P_WORKING_STUDENT_TYPE => '0',
1615 P_WORKING_STUDENT_TYPE_O => l_entry_rec.working_student_type_o,
1616 P_SPOUSE_DEP_TYPE => '0',
1617 P_SPOUSE_DEP_TYPE_O => l_entry_rec.spouse_dep_type_o,
1618 P_SPOUSE_DISABILITY_TYPE => '0',
1619 P_SPOUSE_DISABILITY_TYPE_O => l_entry_rec.spouse_disability_type_o,
1620 P_NUM_DEPS => 0,
1621 P_NUM_DEPS_O => l_entry_rec.num_deps_o,
1622 P_NUM_AGEDS => 0,
1623 P_NUM_AGEDS_O => l_entry_rec.num_ageds_o,
1624 P_NUM_AGED_PARENTS_LT => 0,
1625 P_NUM_AGED_PARENTS_LT_O => l_entry_rec.num_aged_parents_lt_o,
1626 P_NUM_SPECIFIEDS => 0,
1627 P_NUM_SPECIFIEDS_O => l_entry_rec.num_specifieds_o,
1628 P_NUM_DISABLEDS => 0,
1629 P_NUM_DISABLEDS_O => l_entry_rec.num_disableds_o,
1630 P_NUM_SVR_DISABLEDS => 0,
1631 P_NUM_SVR_DISABLEDS_O => l_entry_rec.num_svr_disableds_o,
1632 P_NUM_SVR_DISABLEDS_LT => 0,
1633 P_NUM_SVR_DISABLEDS_LT_O => l_entry_rec.num_svr_disableds_lt_o,
1634 P_ACTION_INFORMATION_ID => l_action_information_id,
1635 P_OBJECT_VERSION_NUMBER => l_entry_rec.object_version_number);
1636 --
1637 -- If widower, change the widow_type from "1" to "3".
1638 --
1639 l_emp_rec.disability_type := nvl(l_entry_rec.disability_type_o, '0');
1640 -- Obsolete Aged Exemption from 2005/01/01.
1641 if l_assact_rec.effective_date >= to_date('2005/01/01','YYYY/MM/DD') then
1642 l_emp_rec.aged_type := '0';
1643 else
1644 l_emp_rec.aged_type := nvl(l_entry_rec.aged_type_o, '0');
1645 end if;
1646 l_emp_rec.widow_type := nvl(l_entry_rec.widow_type_o, '0');
1647 if l_emp_rec.widow_type = '1' and l_emp_rec.sex = 'M' then
1648 l_emp_rec.widow_type := '3';
1649 end if;
1650 l_emp_rec.working_student_type := nvl(l_entry_rec.working_student_type_o, '0');
1651 --
1652 pay_jp_def_api.create_emp(
1653 P_ASSIGNMENT_ACTION_ID => l_assact_rec.assignment_action_id,
1654 P_ASSIGNMENT_ID => l_assact_rec.assignment_id,
1655 P_EFFECTIVE_DATE => l_assact_rec.effective_date,
1656 P_LAST_NAME_KANA => l_emp_rec.last_name_kana,
1657 P_FIRST_NAME_KANA => l_emp_rec.first_name_kana,
1658 P_LAST_NAME => l_emp_rec.last_name,
1659 P_FIRST_NAME => l_emp_rec.first_name,
1660 P_DATE_OF_BIRTH => l_emp_rec.date_of_birth,
1661 P_DATE_OF_DEATH => l_emp_rec.date_of_death,
1662 P_SEX => l_emp_rec.sex,
1663 P_POSTAL_CODE => l_emp_rec.postal_code,
1664 P_ADDRESS => l_emp_rec.address,
1665 P_HOUSEHOLD_HEAD_CTR_ID => l_emp_rec.household_head_ctr_id,
1666 P_HOUSEHOLD_HEAD_FULL_NAME => l_emp_rec.household_head_full_name,
1667 P_HOUSEHOLD_HEAD_CONTACT_TYPE => l_emp_rec.household_head_contact_type,
1668 P_MARRIED_FLAG => l_emp_rec.married_flag,
1669 P_CHANGE_DATE => null,
1670 P_CHANGE_REASON => null,
1671 P_DISABILITY_TYPE => l_emp_rec.disability_type,
1672 P_DISABILITY_DETAILS => l_emp_rec.disability_details,
1673 P_AGED_TYPE => l_emp_rec.aged_type,
1674 P_AGED_DETAILS => l_emp_rec.aged_details,
1675 P_WIDOW_TYPE => l_emp_rec.widow_type,
1676 P_WIDOW_DETAILS => l_emp_rec.widow_details,
1677 P_WORKING_STUDENT_TYPE => l_emp_rec.working_student_type,
1678 P_WORKING_STUDENT_DETAILS => l_emp_rec.working_student_details,
1679 P_ACTION_INFORMATION_ID => l_action_information_id,
1680 P_OBJECT_VERSION_NUMBER => l_emp_rec.object_version_number);
1681 --
1682 -- JP_DEF_DEP
1683 --
1684 for l_rec in csr_dep(l_person_id, l_assact_rec.effective_date) loop
1685 pay_jp_def_api.create_dep(
1686 P_ASSIGNMENT_ACTION_ID => l_assact_rec.assignment_action_id,
1687 P_ASSIGNMENT_ID => l_assact_rec.assignment_id,
1688 P_EFFECTIVE_DATE => l_assact_rec.effective_date,
1689 P_STATUS => 'Q',
1690 p_datetrack_update_mode => cei_datetrack_update_mode(l_rec.contact_extra_info_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date),
1691 p_datetrack_delete_mode => cei_datetrack_delete_mode(l_rec.contact_extra_info_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date),
1692 p_contact_extra_info_id => l_rec.contact_extra_info_id,
1693 p_cei_object_version_number => l_rec.object_version_number,
1694 p_contact_relationship_id => l_rec.contact_relationship_id,
1695 p_last_name_kana => l_rec.last_name_kana,
1696 p_first_name_kana => l_rec.first_name_kana,
1697 p_last_name => l_rec.last_name,
1698 p_first_name => l_rec.first_name,
1699 p_contact_type => l_rec.contact_type,
1700 p_date_of_birth => l_rec.date_of_birth,
1701 p_date_of_death => l_rec.date_of_death,
1702 p_address => l_rec.address,
1703 p_change_date => null,
1704 p_change_reason => null,
1705 p_dep_type => nvl(l_rec.dep_type, '0'),
1706 p_dep_type_o => l_rec.dep_type,
1707 p_occupation => l_rec.occupation,
1708 p_occupation_o => l_rec.occupation,
1709 p_estimated_annual_income => l_rec.estimated_annual_income,
1710 p_estimated_annual_income_o => l_rec.estimated_annual_income,
1711 p_disability_type => nvl(l_rec.disability_type, '0'),
1712 p_disability_type_o => l_rec.disability_type,
1713 p_disability_details => l_rec.disability_details,
1714 p_disability_details_o => l_rec.disability_details,
1715 P_ACTION_INFORMATION_ID => l_action_information_id,
1716 P_OBJECT_VERSION_NUMBER => l_object_version_number);
1717 end loop;
1718 --
1719 -- JP_DEF_DEP_OE
1720 --
1721 for l_rec in csr_dep_oe(l_person_id, l_assact_rec.effective_date) loop
1722 pay_jp_def_api.create_dep_oe(
1723 p_assignment_action_id => l_assact_rec.assignment_action_id,
1724 P_ASSIGNMENT_ID => l_assact_rec.assignment_id,
1725 P_EFFECTIVE_DATE => l_assact_rec.effective_date,
1726 P_STATUS => 'Q',
1727 p_datetrack_update_mode => cei_datetrack_update_mode(l_rec.contact_extra_info_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date),
1728 p_datetrack_delete_mode => cei_datetrack_delete_mode(l_rec.contact_extra_info_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date),
1729 p_contact_extra_info_id => l_rec.contact_extra_info_id,
1730 p_cei_object_version_number => l_rec.object_version_number,
1731 p_contact_relationship_id => l_rec.contact_relationship_id,
1732 p_last_name_kana => l_rec.last_name_kana,
1733 p_first_name_kana => l_rec.first_name_kana,
1734 p_last_name => l_rec.last_name,
1735 p_first_name => l_rec.first_name,
1736 p_contact_type => l_rec.contact_type,
1737 p_date_of_birth => l_rec.date_of_birth,
1738 p_date_of_death => l_rec.date_of_death,
1739 p_address => l_rec.address,
1740 p_change_date => null,
1741 p_change_reason => null,
1742 p_occupation => l_rec.occupation,
1743 p_occupation_o => l_rec.occupation,
1744 p_oe_contact_relationship_id => l_rec.oe_contact_relationship_id,
1745 p_oe_full_name => full_name(l_rec.oe_contact_person_id, l_assact_rec.effective_date),
1746 p_oe_contact_type => l_rec.oe_contact_type,
1747 p_oe_address => l_rec.oe_address,
1748 p_oe_contact_relationship_id_o => l_rec.oe_contact_relationship_id_o,
1749 P_ACTION_INFORMATION_ID => l_action_information_id,
1750 p_object_version_number => l_object_version_number);
1751 end loop;
1752 --
1753 -- JP_DEF_DEP_OS
1754 --
1755 for l_rec in csr_dep_os(l_person_id, l_assact_rec.effective_date) loop
1756 pay_jp_def_api.create_dep_os(
1757 p_assignment_action_id => l_assact_rec.assignment_action_id,
1758 P_ASSIGNMENT_ID => l_assact_rec.assignment_id,
1759 P_EFFECTIVE_DATE => l_assact_rec.effective_date,
1760 P_STATUS => 'Q',
1761 p_datetrack_update_mode => cei_datetrack_update_mode(l_rec.contact_extra_info_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date),
1762 p_datetrack_delete_mode => cei_datetrack_delete_mode(l_rec.contact_extra_info_id, l_rec.effective_start_date, l_rec.effective_end_date, l_assact_rec.effective_date),
1763 p_contact_extra_info_id => l_rec.contact_extra_info_id,
1764 p_cei_object_version_number => l_rec.object_version_number,
1765 p_contact_relationship_id => l_rec.contact_relationship_id,
1766 p_last_name_kana => l_rec.last_name_kana,
1767 p_first_name_kana => l_rec.first_name_kana,
1768 p_last_name => l_rec.last_name,
1769 p_first_name => l_rec.first_name,
1770 p_contact_type => l_rec.contact_type,
1771 p_date_of_birth => l_rec.date_of_birth,
1772 p_date_of_death => l_rec.date_of_death,
1773 p_occupation => l_rec.occupation,
1774 p_occupation_o => l_rec.occupation,
1775 p_os_salary_payer_name => l_rec.os_salary_payer_name,
1776 p_os_salary_payer_name_o => l_rec.os_salary_payer_name,
1777 p_os_salary_payer_address => l_rec.os_salary_payer_address,
1778 p_os_salary_payer_address_o => l_rec.os_salary_payer_address,
1779 P_ACTION_INFORMATION_ID => l_action_information_id,
1780 p_object_version_number => l_object_version_number);
1781 end loop;
1782 --
1783 l_assact_rec.transaction_status := 'N';
1784 --
1785 pay_jp_def_api.update_assact(
1786 P_VALIDATE => false,
1787 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
1788 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
1789 P_TRANSACTION_STATUS => l_assact_rec.transaction_status);
1790 --
1791 p_object_version_number := l_assact_rec.object_version_number;
1792 --
1793 hr_utility.set_location('Leaving : ' || c_proc, 20);
1794 end do_init;
1795 --
1796 procedure do_init(
1797 p_action_information_id in number,
1798 p_object_version_number in out nocopy number,
1799 p_return_status out nocopy varchar2)
1800 is
1801 l_proc varchar2(61) := c_package || '.do_init';
1802 begin
1803 savepoint do_init;
1804 --
1805 -- Initialise Multiple Message Detection
1806 --
1807 hr_multi_message.enable_message_list;
1808 --
1809 do_init(p_action_information_id, p_object_version_number);
1810 --
1811 p_return_status := hr_multi_message.get_return_status_disable;
1812 exception
1813 when hr_multi_message.error_message_exist then
1814 rollback to do_init;
1815 p_object_version_number := null;
1816 p_return_status := hr_multi_message.get_return_status_disable;
1817 when others then
1818 rollback to do_init;
1819 if hr_multi_message.unexpected_error_add(l_proc) then
1820 raise;
1821 end if;
1822 p_object_version_number := null;
1823 p_return_status := hr_multi_message.get_return_status_disable;
1824 end do_init;
1825 -- |---------------------------------------------------------------------------|
1826 -- |------------------------------< do_finalize >------------------------------|
1827 -- |---------------------------------------------------------------------------|
1828 procedure do_finalize(
1829 p_action_information_id in number,
1830 p_object_version_number in out nocopy number,
1831 p_user_comments in varchar2)
1832 is
1833 c_proc constant varchar2(61) := c_package || 'do_finalize';
1834 l_submission_date date;
1835 l_assact_rec pay_jp_def_assact_v%rowtype;
1836 l_entry_rec pay_jp_def_entry_v%rowtype;
1837 l_emp_rec pay_jp_def_emp_v%rowtype;
1838 --
1839 cursor csr_entry(p_assignment_action_id in number) is
1840 select *
1841 from pay_jp_def_entry_v
1842 where assignment_action_id = p_assignment_action_id
1843 for update nowait;
1844 cursor csr_emp(p_assignment_action_id in number) is
1845 select *
1846 from pay_jp_def_emp_v
1847 where assignment_action_id = p_assignment_action_id;
1848 cursor csr_dep(p_assignment_action_id in number) is
1849 select *
1850 from pay_jp_def_dep_v
1851 where assignment_action_id = p_assignment_action_id
1852 and status <> 'D'
1853 order by decode(contact_type, 'S', 1, 2), date_of_birth, last_name_kana, first_name_kana;
1854 begin
1855 hr_utility.set_location('Entering : ' || c_proc, 10);
1856 --
1857 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
1858 --
1859 l_submission_date := check_submission_period(p_action_information_id);
1860 --
1861 if l_assact_rec.transaction_status <> 'N' then
1862 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
1863 fnd_message.raise_error;
1864 end if;
1865 --
1866 -- Synchronize JP_DEF_ENTRY
1867 -- Note all child EOs must be validated before calling this procedure
1868 -- because FND_MSB_PUB does not support "primary key" which means
1869 -- OATableBean multi message functionality is not supported in API.
1870 --
1871 open csr_entry(l_assact_rec.assignment_action_id);
1872 fetch csr_entry into l_entry_rec;
1873 close csr_entry;
1874 --
1875 open csr_emp(l_assact_rec.assignment_action_id);
1876 fetch csr_emp into l_emp_rec;
1877 close csr_emp;
1878 --
1879 l_entry_rec.disability_type := l_emp_rec.disability_type;
1880 l_entry_rec.aged_type := l_emp_rec.aged_type;
1881 if l_emp_rec.widow_type = '3' then
1882 l_entry_rec.widow_type := '1';
1883 else
1884 l_entry_rec.widow_type := l_emp_rec.widow_type;
1885 end if;
1886 l_entry_rec.working_student_type := l_emp_rec.working_student_type;
1887 --
1888 if l_emp_rec.married_flag = 'Y' then
1889 l_entry_rec.spouse_dep_type := '1';
1890 else
1891 l_entry_rec.spouse_dep_type := '0';
1892 end if;
1893 --
1894 -- Initialize new values
1895 --
1896 l_entry_rec.spouse_disability_type := '0';
1897 l_entry_rec.num_deps := 0;
1898 l_entry_rec.num_ageds := 0;
1899 l_entry_rec.num_aged_parents_lt := 0;
1900 l_entry_rec.num_specifieds := 0;
1901 l_entry_rec.num_disableds := 0;
1902 l_entry_rec.num_svr_disableds := 0;
1903 l_entry_rec.num_svr_disableds_lt := 0;
1904 --
1905 for l_rec in csr_dep(l_assact_rec.assignment_action_id) loop
1906 if l_rec.contact_type = 'S' then
1907 if l_rec.dep_type = '0' then
1908 l_entry_rec.spouse_dep_type := '2';
1909 elsif l_rec.dep_type = '20' then
1910 l_entry_rec.spouse_dep_type := '3';
1911 end if;
1912 --
1913 if l_rec.disability_type = '10' then
1914 l_entry_rec.spouse_disability_type := '1';
1915 elsif l_rec.disability_type = '20' then
1916 l_entry_rec.spouse_disability_type := '2';
1917 elsif l_rec.disability_type = '30' then
1918 l_entry_rec.spouse_disability_type := '3';
1919 end if;
1920 else
1921 l_entry_rec.num_deps := l_entry_rec.num_deps + 1;
1922 --
1923 if l_rec.dep_type = '10' then
1924 l_entry_rec.num_specifieds := l_entry_rec.num_specifieds + 1;
1925 elsif l_rec.dep_type = '20' then
1926 l_entry_rec.num_ageds := l_entry_rec.num_ageds + 1;
1927 elsif l_rec.dep_type = '30' then
1928 l_entry_rec.num_aged_parents_lt := l_entry_rec.num_aged_parents_lt + 1;
1929 end if;
1930 --
1931 if l_rec.disability_type = '10' then
1932 l_entry_rec.num_disableds := l_entry_rec.num_disableds + 1;
1933 elsif l_rec.disability_type = '20' then
1934 l_entry_rec.num_svr_disableds := l_entry_rec.num_svr_disableds + 1;
1935 elsif l_rec.disability_type = '30' then
1936 l_entry_rec.num_svr_disableds_lt := l_entry_rec.num_svr_disableds_lt + 1;
1937 end if;
1938 end if;
1939 end loop;
1940 --
1941 pay_jp_def_api.update_entry(
1942 P_VALIDATE => false,
1943 P_ACTION_INFORMATION_ID => l_entry_rec.action_information_id,
1944 P_OBJECT_VERSION_NUMBER => l_entry_rec.object_version_number,
1945 P_DISABILITY_TYPE => l_entry_rec.disability_type,
1946 P_AGED_TYPE => l_entry_rec.aged_type,
1947 P_WIDOW_TYPE => l_entry_rec.widow_type,
1948 P_WORKING_STUDENT_TYPE => l_entry_rec.working_student_type,
1949 P_SPOUSE_DEP_TYPE => l_entry_rec.spouse_dep_type,
1950 P_SPOUSE_DISABILITY_TYPE => l_entry_rec.spouse_disability_type,
1951 P_NUM_DEPS => l_entry_rec.num_deps,
1952 P_NUM_AGEDS => l_entry_rec.num_ageds,
1953 P_NUM_AGED_PARENTS_LT => l_entry_rec.num_aged_parents_lt,
1954 P_NUM_SPECIFIEDS => l_entry_rec.num_specifieds,
1955 P_NUM_DISABLEDS => l_entry_rec.num_disableds,
1956 P_NUM_SVR_DISABLEDS => l_entry_rec.num_svr_disableds,
1957 P_NUM_SVR_DISABLEDS_LT => l_entry_rec.num_svr_disableds_lt);
1958 --
1959 l_assact_rec.transaction_status := 'F';
1960 l_assact_rec.finalized_date := l_submission_date;
1961 l_assact_rec.finalized_by := fnd_global.user_id;
1962 --
1963 pay_jp_def_api.update_assact(
1964 P_VALIDATE => false,
1965 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
1966 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
1967 P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
1968 P_FINALIZED_DATE => l_assact_rec.finalized_date,
1969 P_FINALIZED_BY => l_assact_rec.finalized_by,
1970 P_USER_COMMENTS => p_user_comments);
1971 --
1972 p_object_version_number := l_assact_rec.object_version_number;
1973 --
1974 hr_utility.set_location('Leaving : ' || c_proc, 20);
1975 end do_finalize;
1976 --
1977 procedure do_finalize(
1978 p_action_information_id in number,
1979 p_object_version_number in out nocopy number,
1980 p_user_comments in varchar2,
1981 p_return_status out nocopy varchar2)
1982 is
1983 l_proc varchar2(61) := c_package || '.do_finalize';
1984 begin
1985 savepoint do_finalize;
1986 --
1987 -- Initialise Multiple Message Detection
1988 --
1989 hr_multi_message.enable_message_list;
1990 --
1991 do_finalize(p_action_information_id, p_object_version_number, p_user_comments);
1992 --
1993 p_return_status := hr_multi_message.get_return_status_disable;
1994 exception
1995 when hr_multi_message.error_message_exist then
1996 rollback to do_finalize;
1997 p_object_version_number := null;
1998 p_return_status := hr_multi_message.get_return_status_disable;
1999 when others then
2000 rollback to do_finalize;
2001 if hr_multi_message.unexpected_error_add(l_proc) then
2002 raise;
2003 end if;
2004 p_object_version_number := null;
2005 p_return_status := hr_multi_message.get_return_status_disable;
2006 end do_finalize;
2007 -- |---------------------------------------------------------------------------|
2008 -- |-------------------------------< do_reject >-------------------------------|
2009 -- |---------------------------------------------------------------------------|
2010 procedure do_reject(
2011 p_action_information_id in number,
2012 p_object_version_number in out nocopy number,
2013 p_admin_comments in varchar2)
2014 is
2015 c_proc constant varchar2(61) := c_package || 'do_reject';
2016 l_assact_rec pay_jp_def_assact_v%rowtype;
2017 begin
2018 hr_utility.set_location('Entering : ' || c_proc, 10);
2019 --
2020 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2021 --
2022 check_submission_period(p_action_information_id);
2023 --
2024 if l_assact_rec.transaction_status not in ('F', 'A') then
2025 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2026 fnd_message.raise_error;
2027 elsif l_assact_rec.transfer_status <> 'U' then
2028 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
2029 fnd_message.raise_error;
2030 end if;
2031 --
2032 delete
2033 from pay_action_information
2034 where action_context_id = l_assact_rec.assignment_action_id
2035 and action_context_type = 'AAP'
2036 and action_information_category <> 'JP_DEF_ASSACT';
2037 --
2038 l_assact_rec.transaction_status := 'U';
2039 l_assact_rec.finalized_date := null;
2040 l_assact_rec.finalized_by := null;
2041 --
2042 pay_jp_def_api.update_assact(
2043 P_VALIDATE => false,
2044 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2045 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2046 P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
2047 P_FINALIZED_DATE => l_assact_rec.finalized_date,
2048 P_FINALIZED_BY => l_assact_rec.finalized_by,
2049 P_ADMIN_COMMENTS => p_admin_comments);
2050 --
2051 p_object_version_number := l_assact_rec.object_version_number;
2052 --
2053 hr_utility.set_location('Leaving : ' || c_proc, 20);
2054 end do_reject;
2055 --
2056 procedure do_reject(
2057 p_action_information_id in number,
2058 p_object_version_number in out nocopy number,
2059 p_admin_comments in varchar2,
2060 p_return_status out nocopy varchar2)
2061 is
2062 l_proc varchar2(61) := c_package || '.do_reject';
2063 begin
2064 savepoint do_reject;
2065 --
2066 -- Initialise Multiple Message Detection
2067 --
2068 hr_multi_message.enable_message_list;
2069 --
2070 do_reject(p_action_information_id, p_object_version_number, p_admin_comments);
2071 --
2072 p_return_status := hr_multi_message.get_return_status_disable;
2073 exception
2074 when hr_multi_message.error_message_exist then
2075 rollback to do_reject;
2076 p_object_version_number := null;
2077 p_return_status := hr_multi_message.get_return_status_disable;
2078 when others then
2079 rollback to do_reject;
2080 if hr_multi_message.unexpected_error_add(l_proc) then
2081 raise;
2082 end if;
2083 p_object_version_number := null;
2084 p_return_status := hr_multi_message.get_return_status_disable;
2085 end do_reject;
2086 -- |---------------------------------------------------------------------------|
2087 -- |-------------------------------< do_return >-------------------------------|
2088 -- |---------------------------------------------------------------------------|
2089 procedure do_return(
2090 p_action_information_id in number,
2091 p_object_version_number in out nocopy number,
2092 p_admin_comments in varchar2)
2093 is
2094 c_proc constant varchar2(61) := c_package || 'do_return';
2095 l_assact_rec pay_jp_def_assact_v%rowtype;
2096 begin
2097 hr_utility.set_location('Entering : ' || c_proc, 10);
2098 --
2099 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2100 --
2101 check_submission_period(p_action_information_id);
2102 --
2103 if l_assact_rec.transaction_status not in ('F', 'A') then
2104 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2105 fnd_message.raise_error;
2106 elsif l_assact_rec.transfer_status <> 'U' then
2107 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
2108 fnd_message.raise_error;
2109 end if;
2110 --
2111 l_assact_rec.transaction_status := 'N';
2112 l_assact_rec.finalized_date := null;
2113 l_assact_rec.finalized_by := null;
2114 --
2115 pay_jp_def_api.update_assact(
2116 P_VALIDATE => false,
2117 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2118 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2119 P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
2120 P_FINALIZED_DATE => l_assact_rec.finalized_date,
2121 P_FINALIZED_BY => l_assact_rec.finalized_by,
2122 P_ADMIN_COMMENTS => p_admin_comments);
2123 --
2124 p_object_version_number := l_assact_rec.object_version_number;
2125 --
2126 hr_utility.set_location('Leaving : ' || c_proc, 20);
2127 end do_return;
2128 --
2129 procedure do_return(
2130 p_action_information_id in number,
2131 p_object_version_number in out nocopy number,
2132 p_admin_comments in varchar2,
2133 p_return_status out nocopy varchar2)
2134 is
2135 l_proc varchar2(61) := c_package || '.do_return';
2136 begin
2137 savepoint do_return;
2138 --
2139 -- Initialise Multiple Message Detection
2140 --
2141 hr_multi_message.enable_message_list;
2142 --
2143 do_return(p_action_information_id, p_object_version_number, p_admin_comments);
2144 --
2145 p_return_status := hr_multi_message.get_return_status_disable;
2146 exception
2147 when hr_multi_message.error_message_exist then
2148 rollback to do_return;
2149 p_object_version_number := null;
2150 p_return_status := hr_multi_message.get_return_status_disable;
2151 when others then
2152 rollback to do_return;
2153 if hr_multi_message.unexpected_error_add(l_proc) then
2154 raise;
2155 end if;
2156 p_object_version_number := null;
2157 p_return_status := hr_multi_message.get_return_status_disable;
2158 end do_return;
2159 -- |---------------------------------------------------------------------------|
2160 -- |------------------------------< do_approve >-------------------------------|
2161 -- |---------------------------------------------------------------------------|
2162 procedure do_approve(
2163 p_action_information_id in number,
2164 p_object_version_number in out nocopy number)
2165 is
2166 c_proc constant varchar2(61) := c_package || 'do_approve';
2167 l_assact_rec pay_jp_def_assact_v%rowtype;
2168 begin
2169 hr_utility.set_location('Entering : ' || c_proc, 10);
2170 --
2171 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2172 --
2173 if l_assact_rec.transaction_status <> 'F' then
2174 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2175 fnd_message.raise_error;
2176 end if;
2177 --
2178 l_assact_rec.transaction_status := 'A';
2179 --
2180 pay_jp_def_api.update_assact(
2181 P_VALIDATE => false,
2182 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2183 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2184 P_TRANSACTION_STATUS => l_assact_rec.transaction_status);
2185 --
2186 p_object_version_number := l_assact_rec.object_version_number;
2187 --
2188 hr_utility.set_location('Leaving : ' || c_proc, 20);
2189 end do_approve;
2190 --
2191 procedure do_approve(
2192 p_action_information_id in number,
2193 p_object_version_number in out nocopy number,
2194 p_return_status out nocopy varchar2)
2195 is
2196 l_proc varchar2(61) := c_package || '.do_approve';
2197 begin
2198 savepoint do_approve;
2199 --
2200 -- Initialise Multiple Message Detection
2201 --
2202 hr_multi_message.enable_message_list;
2203 --
2204 do_approve(p_action_information_id, p_object_version_number);
2205 --
2206 p_return_status := hr_multi_message.get_return_status_disable;
2207 exception
2208 when hr_multi_message.error_message_exist then
2209 rollback to do_approve;
2210 p_object_version_number := null;
2211 p_return_status := hr_multi_message.get_return_status_disable;
2212 when others then
2213 rollback to do_approve;
2214 if hr_multi_message.unexpected_error_add(l_proc) then
2215 raise;
2216 end if;
2217 p_object_version_number := null;
2218 p_return_status := hr_multi_message.get_return_status_disable;
2219 end do_approve;
2220 -- |---------------------------------------------------------------------------|
2221 -- |------------------------------< do_approve >-------------------------------|
2222 -- |---------------------------------------------------------------------------|
2223 procedure do_approve(
2224 errbuf out nocopy varchar2,
2225 retcode out nocopy varchar2,
2226 p_payroll_action_id in varchar2)
2227 is
2228 l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
2229 cursor csr_assact is
2230 select aif.action_information_id,
2231 aif.object_version_number
2232 from pay_jp_def_assact_v aif,
2233 pay_assignment_actions paa
2234 where paa.payroll_action_id = l_payroll_action_id
2235 and paa.action_status = 'C'
2236 and aif.assignment_action_id = paa.assignment_action_id
2237 and transaction_status = 'F';
2238 begin
2239 --
2240 -- retcode
2241 -- 0 : Success
2242 -- 1 : Warning
2243 -- 2 : Error
2244 --
2245 retcode := '0';
2246 --
2247 for l_rec in csr_assact loop
2248 begin
2249 do_approve(
2250 p_action_information_id => l_rec.action_information_id,
2251 p_object_version_number => l_rec.object_version_number);
2252 commit;
2253 exception
2254 when others then
2255 retcode := '1';
2256 end;
2257 end loop;
2258 end do_approve;
2259 -- |---------------------------------------------------------------------------|
2260 -- |------------------------------< do_transfer >------------------------------|
2261 -- |---------------------------------------------------------------------------|
2262 procedure do_transfer(
2263 p_action_information_id in number,
2264 p_object_version_number in out nocopy number,
2265 p_create_session in boolean default true)
2266 is
2267 c_proc constant varchar2(61) := c_package || 'do_transfer';
2268 l_assact_rec pay_jp_def_assact_v%rowtype;
2269 l_entry_rec pay_jp_def_entry_v%rowtype;
2270 l_dep_rec pay_jp_def_dep_v%rowtype;
2271 l_dep_oe_rec pay_jp_def_dep_oe_v%rowtype;
2272 l_dep_os_rec pay_jp_def_dep_os_v%rowtype;
2273 --
2274 cursor csr_entry(p_assignment_action_id number) is
2275 select *
2276 from pay_jp_def_entry_v
2277 where assignment_action_id = p_assignment_action_id
2278 for update nowait;
2279 --
2280 -- JP_DEF_DEP
2281 --
2282 cursor csr_dep_del(p_assignment_action_id number) is
2283 select *
2284 from pay_jp_def_dep_v
2285 where assignment_action_id = p_assignment_action_id
2286 and status = 'D'
2287 for update nowait;
2288 cursor csr_dep_upd(p_assignment_action_id number) is
2289 select *
2290 from pay_jp_def_dep_v
2291 where assignment_action_id = p_assignment_action_id
2292 and status = 'Q'
2293 for update nowait;
2294 cursor csr_dep_ins(p_assignment_action_id number) is
2295 select *
2296 from pay_jp_def_dep_v
2297 where assignment_action_id = p_assignment_action_id
2298 and status = 'I'
2299 for update nowait;
2300 --
2301 -- JP_DEF_DEP_OE
2302 --
2303 cursor csr_dep_oe_del(p_assignment_action_id number) is
2304 select *
2305 from pay_jp_def_dep_oe_v
2306 where assignment_action_id = p_assignment_action_id
2307 and status = 'D'
2308 for update nowait;
2309 cursor csr_dep_oe_upd(p_assignment_action_id number) is
2310 select *
2311 from pay_jp_def_dep_oe_v
2312 where assignment_action_id = p_assignment_action_id
2313 and status = 'Q'
2314 for update nowait;
2315 cursor csr_dep_oe_ins(p_assignment_action_id number) is
2316 select *
2317 from pay_jp_def_dep_oe_v
2318 where assignment_action_id = p_assignment_action_id
2319 and status = 'I'
2320 for update nowait;
2321 --
2322 -- JP_DEF_DEP_OS
2323 --
2324 cursor csr_dep_os_del(p_assignment_action_id number) is
2325 select *
2326 from pay_jp_def_dep_os_v
2327 where assignment_action_id = p_assignment_action_id
2328 and status = 'D'
2329 for update nowait;
2330 cursor csr_dep_os_upd(p_assignment_action_id number) is
2331 select *
2332 from pay_jp_def_dep_os_v
2333 where assignment_action_id = p_assignment_action_id
2334 and status = 'Q'
2335 for update nowait;
2336 cursor csr_dep_os_ins(p_assignment_action_id number) is
2337 select *
2338 from pay_jp_def_dep_os_v
2339 where assignment_action_id = p_assignment_action_id
2340 and status = 'I'
2341 for update nowait;
2342 begin
2343 hr_utility.set_location('Entering : ' || c_proc, 10);
2344 --
2345 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2346 --
2347 if l_assact_rec.transaction_status <> 'A' then
2348 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2349 fnd_message.raise_error;
2350 elsif l_assact_rec.transfer_status <> 'U' then
2351 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
2352 fnd_message.raise_error;
2353 end if;
2354 --
2355 if p_create_session then
2356 insert_session(l_assact_rec.effective_date);
2357 end if;
2358 --
2359 -- Transfer JP_DEF_ENTRY to PAY_ELEMENT_ENTRIES_F
2360 --
2361 for l_rec in csr_entry(l_assact_rec.assignment_action_id) loop
2362 transfer_entry(l_rec);
2363 end loop;
2364 --
2365 -- Transfer the followings.
2366 --
2367 -- JP_DEF_DEP --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP
2368 -- JP_DEF_DEP_OE --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OE
2369 -- JP_DEF_DEP_OS --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OS
2370 --
2371 -- The transaction sequence into PER_CONTACT_EXTRA_INFO_F must be
2372 -- at first "Delele", "Update" and at last "Insert" to avoid API errors.
2373 --
2374 -- "Delete" phase
2375 --
2376 for l_rec in csr_dep_del(l_assact_rec.assignment_action_id) loop
2377 transfer_dep(l_rec);
2378 end loop;
2379 for l_rec in csr_dep_oe_del(l_assact_rec.assignment_action_id) loop
2380 transfer_dep_oe(l_rec);
2381 end loop;
2382 for l_rec in csr_dep_os_del(l_assact_rec.assignment_action_id) loop
2383 transfer_dep_os(l_rec);
2384 end loop;
2385 --
2386 -- "Update" phase
2387 --
2388 for l_rec in csr_dep_upd(l_assact_rec.assignment_action_id) loop
2389 transfer_dep(l_rec);
2390 end loop;
2391 for l_rec in csr_dep_oe_upd(l_assact_rec.assignment_action_id) loop
2392 transfer_dep_oe(l_rec);
2393 end loop;
2394 for l_rec in csr_dep_os_upd(l_assact_rec.assignment_action_id) loop
2395 transfer_dep_os(l_rec);
2396 end loop;
2397 --
2398 -- "Insert" phase
2399 --
2400 for l_rec in csr_dep_ins(l_assact_rec.assignment_action_id) loop
2401 transfer_dep(l_rec);
2402 end loop;
2403 for l_rec in csr_dep_oe_ins(l_assact_rec.assignment_action_id) loop
2404 transfer_dep_oe(l_rec);
2405 end loop;
2406 for l_rec in csr_dep_os_ins(l_assact_rec.assignment_action_id) loop
2407 transfer_dep_os(l_rec);
2408 end loop;
2409 --
2410 if p_create_session then
2411 delete_session;
2412 end if;
2413 --
2414 l_assact_rec.transfer_status := 'T';
2415 --
2416 pay_jp_def_api.update_assact(
2417 P_VALIDATE => false,
2418 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2419 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2420 P_TRANSFER_STATUS => l_assact_rec.transfer_status);
2421 --
2422 p_object_version_number := l_assact_rec.object_version_number;
2423 --
2424 hr_utility.set_location('Leaving : ' || c_proc, 20);
2425 end do_transfer;
2426 --
2427 procedure do_transfer(
2428 p_action_information_id in number,
2429 p_object_version_number in out nocopy number,
2430 p_return_status out nocopy varchar2)
2431 is
2432 l_proc varchar2(61) := c_package || '.do_transfer';
2433 begin
2434 savepoint do_transfer;
2435 --
2436 -- Initialise Multiple Message Detection
2437 --
2438 hr_multi_message.enable_message_list;
2439 --
2440 do_transfer(p_action_information_id, p_object_version_number);
2441 --
2442 p_return_status := hr_multi_message.get_return_status_disable;
2443 exception
2444 when hr_multi_message.error_message_exist then
2445 rollback to do_transfer;
2446 p_object_version_number := null;
2447 p_return_status := hr_multi_message.get_return_status_disable;
2448 when others then
2449 rollback to do_transfer;
2450 if hr_multi_message.unexpected_error_add(l_proc) then
2451 raise;
2452 end if;
2453 p_object_version_number := null;
2454 p_return_status := hr_multi_message.get_return_status_disable;
2455 end do_transfer;
2456 -- |---------------------------------------------------------------------------|
2457 -- |------------------------------< do_transfer >------------------------------|
2458 -- |---------------------------------------------------------------------------|
2459 procedure do_transfer(
2460 errbuf out nocopy varchar2,
2461 retcode out nocopy varchar2,
2462 p_payroll_action_id in varchar2)
2463 is
2464 l_effective_date date;
2465 l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
2466 --
2467 cursor csr_assact is
2468 select assact.action_information_id,
2469 assact.object_version_number,
2470 per.full_name,
2471 asg.assignment_number
2472 from per_all_people_f per,
2473 per_all_assignments_f asg,
2474 pay_jp_def_assact_v assact,
2475 pay_assignment_actions paa
2476 where paa.payroll_action_id = l_payroll_action_id
2477 and paa.action_status = 'C'
2478 and assact.assignment_action_id = paa.assignment_action_id
2479 and assact.transaction_status = 'A'
2480 and assact.transfer_status = 'U'
2481 and asg.assignment_id = assact.assignment_id
2482 and assact.effective_date
2483 between asg.effective_start_date and asg.effective_end_date
2484 and per.person_id = asg.person_id
2485 and assact.effective_date
2486 between per.effective_start_date and per.effective_end_date;
2487 begin
2488 --
2489 -- retcode
2490 -- 0 : Success
2491 -- 1 : Warning
2492 -- 2 : Error
2493 --
2494 retcode := '0';
2495 --
2496 select effective_date
2497 into l_effective_date
2498 from pay_jp_def_pact_v
2499 where payroll_action_id = l_payroll_action_id;
2500 --
2501 insert_session(l_effective_date);
2502 commit;
2503 --
2504 fnd_file.put_line(fnd_file.output, 'Full Name Assignment Number');
2505 fnd_file.put_line(fnd_file.output, '---------------------------------------- ------------------------------');
2506 fnd_file.put_line(fnd_file.log, 'Full Name Assignment Number');
2507 fnd_file.put_line(fnd_file.log, '---------------------------------------- ------------------------------');
2508 --
2509 for l_rec in csr_assact loop
2510 begin
2511 do_transfer(
2512 p_action_information_id => l_rec.action_information_id,
2513 p_object_version_number => l_rec.object_version_number,
2514 p_create_session => false);
2515 commit;
2516 --
2517 fnd_file.put_line(fnd_file.output, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
2518 exception
2519 when others then
2520 retcode := '1';
2521 fnd_file.put_line(fnd_file.log, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
2522 fnd_file.put_line(fnd_file.log, get_sqlerrm);
2523 end;
2524 end loop;
2525 --
2526 delete_session;
2527 commit;
2528 end do_transfer;
2529 -- |---------------------------------------------------------------------------|
2530 -- |---------------------------< rollback_transfer >---------------------------|
2531 -- |---------------------------------------------------------------------------|
2532 procedure rollback_transfer(
2533 p_action_information_id in number,
2534 p_object_version_number in out nocopy number,
2535 p_create_session in boolean default true)
2536 is
2537 c_proc constant varchar2(61) := c_package || 'rollback_transfer';
2538 l_assact_rec pay_jp_def_assact_v%rowtype;
2539 l_entry_rec pay_jp_def_entry_v%rowtype;
2540 l_dep_rec pay_jp_def_dep_v%rowtype;
2541 l_dep_oe_rec pay_jp_def_dep_oe_v%rowtype;
2542 l_dep_os_rec pay_jp_def_dep_os_v%rowtype;
2543 --
2544 cursor csr_entry(p_assignment_action_id number) is
2545 select *
2546 from pay_jp_def_entry_v
2547 where assignment_action_id = p_assignment_action_id
2548 for update nowait;
2549 --
2550 -- JP_DEF_DEP
2551 --
2552 cursor csr_dep_ins(p_assignment_action_id number) is
2553 select *
2554 from pay_jp_def_dep_v
2555 where assignment_action_id = p_assignment_action_id
2556 and status = 'I'
2557 for update nowait;
2558 cursor csr_dep_upd(p_assignment_action_id number) is
2559 select *
2560 from pay_jp_def_dep_v
2561 where assignment_action_id = p_assignment_action_id
2562 and status = 'U'
2563 for update nowait;
2564 cursor csr_dep_del(p_assignment_action_id number) is
2565 select *
2566 from pay_jp_def_dep_v
2567 where assignment_action_id = p_assignment_action_id
2568 and status = 'D'
2569 for update nowait;
2570 --
2571 -- JP_DEF_DEP_OE
2572 --
2573 cursor csr_dep_oe_ins(p_assignment_action_id number) is
2574 select *
2575 from pay_jp_def_dep_oe_v
2576 where assignment_action_id = p_assignment_action_id
2577 and status = 'I'
2578 for update nowait;
2579 cursor csr_dep_oe_upd(p_assignment_action_id number) is
2580 select *
2581 from pay_jp_def_dep_oe_v
2582 where assignment_action_id = p_assignment_action_id
2583 and status = 'U'
2584 for update nowait;
2585 cursor csr_dep_oe_del(p_assignment_action_id number) is
2586 select *
2587 from pay_jp_def_dep_oe_v
2588 where assignment_action_id = p_assignment_action_id
2589 and status = 'D'
2590 for update nowait;
2591 --
2592 -- JP_DEF_DEP_OS
2593 --
2594 cursor csr_dep_os_ins(p_assignment_action_id number) is
2595 select *
2596 from pay_jp_def_dep_os_v
2597 where assignment_action_id = p_assignment_action_id
2598 and status = 'I'
2599 for update nowait;
2600 cursor csr_dep_os_upd(p_assignment_action_id number) is
2601 select *
2602 from pay_jp_def_dep_os_v
2603 where assignment_action_id = p_assignment_action_id
2604 and status = 'U'
2605 for update nowait;
2606 cursor csr_dep_os_del(p_assignment_action_id number) is
2607 select *
2608 from pay_jp_def_dep_os_v
2609 where assignment_action_id = p_assignment_action_id
2610 and status = 'D'
2611 for update nowait;
2612 begin
2613 hr_utility.set_location('Entering : ' || c_proc, 10);
2614 --
2615 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2616 --
2617 if l_assact_rec.transfer_status = 'U' then
2618 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_TRANSFERRED');
2619 fnd_message.raise_error;
2620 elsif l_assact_rec.transfer_status = 'E' then
2621 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_EXPIRED');
2622 fnd_message.raise_error;
2623 end if;
2624 --
2625 if p_create_session then
2626 insert_session(l_assact_rec.effective_date);
2627 end if;
2628 --
2629 -- Rollback Transfer JP_DEF_ENTRY to PAY_ELEMENT_ENTRIES_F
2630 --
2631 for l_rec in csr_entry(l_assact_rec.assignment_action_id) loop
2632 rollback_entry(l_rec);
2633 end loop;
2634 --
2635 -- Rollback Transfer the followings.
2636 --
2637 -- JP_DEF_DEP --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP
2638 -- JP_DEF_DEP_OE --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OE
2639 -- JP_DEF_DEP_OS --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OS
2640 --
2641 -- The transaction sequence into PER_CONTACT_EXTRA_INFO_F must be
2642 -- at first "Insert", "Update" and at last "Delete" to avoid API errors.
2643 --
2644 -- "Insert" phase
2645 --
2646 for l_rec in csr_dep_ins(l_assact_rec.assignment_action_id) loop
2647 rollback_dep(l_rec);
2648 end loop;
2649 for l_rec in csr_dep_oe_ins(l_assact_rec.assignment_action_id) loop
2650 rollback_dep_oe(l_rec);
2651 end loop;
2652 for l_rec in csr_dep_os_ins(l_assact_rec.assignment_action_id) loop
2653 rollback_dep_os(l_rec);
2654 end loop;
2655 --
2656 -- "Update" phase
2657 --
2658 for l_rec in csr_dep_upd(l_assact_rec.assignment_action_id) loop
2659 rollback_dep(l_rec);
2660 end loop;
2661 for l_rec in csr_dep_oe_upd(l_assact_rec.assignment_action_id) loop
2662 rollback_dep_oe(l_rec);
2663 end loop;
2664 for l_rec in csr_dep_os_upd(l_assact_rec.assignment_action_id) loop
2665 rollback_dep_os(l_rec);
2666 end loop;
2667 --
2668 -- "Delete" phase
2669 --
2670 for l_rec in csr_dep_del(l_assact_rec.assignment_action_id) loop
2671 rollback_dep(l_rec);
2672 end loop;
2673 for l_rec in csr_dep_oe_del(l_assact_rec.assignment_action_id) loop
2674 rollback_dep_oe(l_rec);
2675 end loop;
2676 for l_rec in csr_dep_os_del(l_assact_rec.assignment_action_id) loop
2677 rollback_dep_os(l_rec);
2678 end loop;
2679 --
2680 if p_create_session then
2681 delete_session;
2682 end if;
2683 --
2684 l_assact_rec.transfer_status := 'U';
2685 --
2686 pay_jp_def_api.update_assact(
2687 P_VALIDATE => false,
2688 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2689 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2690 P_TRANSFER_STATUS => l_assact_rec.transfer_status);
2691 --
2692 p_object_version_number := l_assact_rec.object_version_number;
2693 --
2694 hr_utility.set_location('Leaving : ' || c_proc, 20);
2695 end rollback_transfer;
2696 --
2697 procedure rollback_transfer(
2698 p_action_information_id in number,
2699 p_object_version_number in out nocopy number,
2700 p_return_status out nocopy varchar2)
2701 is
2702 l_proc varchar2(61) := c_package || '.rollback_transfer';
2703 begin
2704 savepoint rollback_transfer;
2705 --
2706 -- Initialise Multiple Message Detection
2707 --
2708 hr_multi_message.enable_message_list;
2709 --
2710 rollback_transfer(p_action_information_id, p_object_version_number);
2711 --
2712 p_return_status := hr_multi_message.get_return_status_disable;
2713 exception
2714 when hr_multi_message.error_message_exist then
2715 rollback to rollback_transfer;
2716 p_object_version_number := null;
2717 p_return_status := hr_multi_message.get_return_status_disable;
2718 when others then
2719 rollback to rollback_transfer;
2720 if hr_multi_message.unexpected_error_add(l_proc) then
2721 raise;
2722 end if;
2723 p_object_version_number := null;
2724 p_return_status := hr_multi_message.get_return_status_disable;
2725 end rollback_transfer;
2726 -- |---------------------------------------------------------------------------|
2727 -- |-------------------------------< do_expire >-------------------------------|
2728 -- |---------------------------------------------------------------------------|
2729 procedure do_expire(
2730 p_action_information_id in number,
2731 p_object_version_number in out nocopy number,
2732 p_expiry_date in date)
2733 is
2734 c_proc constant varchar2(61) := c_package || 'do_expire';
2735 l_assact_rec pay_jp_def_assact_v%rowtype;
2736 l_year_end_date date;
2737 l_esd date;
2738 l_eed date;
2739 l_warning boolean;
2740 --
2741 cursor csr_entry(p_assignment_action_id number) is
2742 select v.*
2743 from pay_jp_def_entry_v v
2744 where v.assignment_action_id = p_assignment_action_id
2745 and exists(
2746 select null
2747 from pay_element_entries_f pee
2748 where pee.element_entry_id = v.element_entry_id
2749 and p_expiry_date + 1
2750 between pee.effective_start_date and pee.effective_end_date)
2751 for update nowait;
2752 cursor csr_dep(p_assignment_action_id number) is
2753 select v.*
2754 from pay_jp_def_dep_v v
2755 where v.assignment_action_id = p_assignment_action_id
2756 and status <> 'D'
2757 and exists(
2758 select null
2759 from per_contact_extra_info_f cei
2760 where cei.contact_extra_info_id = v.contact_extra_info_id
2761 and p_expiry_date + 1
2762 between cei.effective_start_date and cei.effective_end_date)
2763 for update nowait;
2764 cursor csr_dep_oe(p_assignment_action_id number) is
2765 select v.*
2766 from pay_jp_def_dep_oe_v v
2767 where v.assignment_action_id = p_assignment_action_id
2768 and status <> 'D'
2769 and exists(
2770 select null
2771 from per_contact_extra_info_f cei
2772 where cei.contact_extra_info_id = v.contact_extra_info_id
2773 and p_expiry_date + 1
2774 between cei.effective_start_date and cei.effective_end_date)
2775 for update nowait;
2776 cursor csr_dep_os(p_assignment_action_id number) is
2777 select v.*
2778 from pay_jp_def_dep_os_v v
2779 where v.assignment_action_id = p_assignment_action_id
2780 and status <> 'D'
2781 and exists(
2782 select null
2783 from per_contact_extra_info_f cei
2784 where cei.contact_extra_info_id = v.contact_extra_info_id
2785 and p_expiry_date + 1
2786 between cei.effective_start_date and cei.effective_end_date)
2787 for update nowait;
2788 begin
2789 hr_utility.set_location('Entering : ' || c_proc, 10);
2790 --
2791 hr_api.mandatory_arg_error(c_proc, 'expiry_date', p_expiry_date);
2792 --
2793 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2794 --
2795 if l_assact_rec.transfer_status = 'U' then
2796 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_TRANSFERRED_YET');
2797 fnd_message.raise_error;
2798 elsif l_assact_rec.transfer_status = 'E' then
2799 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_EXPIRED');
2800 fnd_message.raise_error;
2801 end if;
2802 --
2803 l_year_end_date := add_months(trunc(l_assact_rec.effective_date, 'YYYY'), 12) - 1;
2804 if p_expiry_date < l_assact_rec.effective_date
2805 or p_expiry_date > l_year_end_date then
2806 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_EXPIRY_DATE');
2807 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_chardate(l_assact_rec.effective_date));
2808 fnd_message.set_token('YEAR_END_DATE', fnd_date.date_to_chardate(l_year_end_date));
2809 fnd_message.raise_error;
2810 end if;
2811 --
2812 insert_session(p_expiry_date);
2813 --
2814 for l_rec in csr_entry(l_assact_rec.assignment_action_id) loop
2815 pay_element_entry_api.delete_element_entry(
2816 p_validate => false,
2817 p_effective_date => p_expiry_date,
2818 p_datetrack_delete_mode => 'DELETE',
2819 p_element_entry_id => l_rec.element_entry_id,
2820 p_object_version_number => l_rec.ee_object_version_number,
2821 p_effective_start_date => l_esd,
2822 p_effective_end_date => l_eed,
2823 p_delete_warning => l_warning);
2824 --
2825 pay_jp_def_api.update_entry(
2826 P_VALIDATE => false,
2827 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
2828 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
2829 P_EE_OBJECT_VERSION_NUMBER => l_rec.ee_object_version_number);
2830 end loop;
2831 --
2832 for l_rec in csr_dep(l_assact_rec.assignment_action_id) loop
2833 hr_contact_extra_info_api.delete_contact_extra_info(
2834 p_validate => false,
2835 p_effective_date => p_expiry_date,
2836 p_datetrack_delete_mode => 'DELETE',
2837 p_contact_extra_info_id => l_rec.contact_extra_info_id,
2838 p_object_version_number => l_rec.cei_object_version_number,
2839 p_effective_start_date => l_esd,
2840 p_effective_end_date => l_eed);
2841 --
2842 pay_jp_def_api.update_dep(
2843 P_VALIDATE => false,
2844 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
2845 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
2846 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
2847 end loop;
2848 --
2849 for l_rec in csr_dep_oe(l_assact_rec.assignment_action_id) loop
2850 hr_contact_extra_info_api.delete_contact_extra_info(
2851 p_validate => false,
2852 p_effective_date => p_expiry_date,
2853 p_datetrack_delete_mode => 'DELETE',
2854 p_contact_extra_info_id => l_rec.contact_extra_info_id,
2855 p_object_version_number => l_rec.cei_object_version_number,
2856 p_effective_start_date => l_esd,
2857 p_effective_end_date => l_eed);
2858 --
2859 pay_jp_def_api.update_dep_oe(
2860 P_VALIDATE => false,
2861 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
2862 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
2863 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
2864 end loop;
2865 --
2866 for l_rec in csr_dep_os(l_assact_rec.assignment_action_id) loop
2867 hr_contact_extra_info_api.delete_contact_extra_info(
2868 p_validate => false,
2869 p_effective_date => p_expiry_date,
2870 p_datetrack_delete_mode => 'DELETE',
2871 p_contact_extra_info_id => l_rec.contact_extra_info_id,
2872 p_object_version_number => l_rec.cei_object_version_number,
2873 p_effective_start_date => l_esd,
2874 p_effective_end_date => l_eed);
2875 --
2876 pay_jp_def_api.update_dep_os(
2877 P_VALIDATE => false,
2878 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
2879 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
2880 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
2881 end loop;
2882 --
2883 delete_session;
2884 --
2885 l_assact_rec.transfer_status := 'E';
2886 l_assact_rec.expiry_date := p_expiry_date;
2887 --
2888 pay_jp_def_api.update_assact(
2889 P_VALIDATE => false,
2890 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2891 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2892 P_TRANSFER_STATUS => l_assact_rec.transfer_status,
2893 P_EXPIRY_DATE => l_assact_rec.expiry_date);
2894 --
2895 p_object_version_number := l_assact_rec.object_version_number;
2896 --
2897 hr_utility.set_location('Leaving : ' || c_proc, 20);
2898 end do_expire;
2899 --
2900 procedure do_expire(
2901 p_action_information_id in number,
2902 p_object_version_number in out nocopy number,
2903 p_expiry_date in date,
2904 p_return_status out nocopy varchar2)
2905 is
2906 l_proc varchar2(61) := c_package || '.do_expire';
2907 begin
2908 savepoint do_expire;
2909 --
2910 -- Initialise Multiple Message Detection
2911 --
2912 hr_multi_message.enable_message_list;
2913 --
2914 do_expire(p_action_information_id, p_object_version_number, p_expiry_date);
2915 --
2916 p_return_status := hr_multi_message.get_return_status_disable;
2917 exception
2918 when hr_multi_message.error_message_exist then
2919 rollback to do_expire;
2920 p_object_version_number := null;
2921 p_return_status := hr_multi_message.get_return_status_disable;
2922 when others then
2923 rollback to do_expire;
2924 if hr_multi_message.unexpected_error_add(l_proc) then
2925 raise;
2926 end if;
2927 p_object_version_number := null;
2928 p_return_status := hr_multi_message.get_return_status_disable;
2929 end do_expire;
2930 -- |---------------------------------------------------------------------------|
2931 -- |----------------------------< rollback_expire >----------------------------|
2932 -- |---------------------------------------------------------------------------|
2933 procedure rollback_expire(
2934 p_action_information_id in number,
2935 p_object_version_number in out nocopy number)
2936 is
2937 c_proc constant varchar2(61) := c_package || 'rollback_expire';
2938 l_assact_rec pay_jp_def_assact_v%rowtype;
2939 l_esd date;
2940 l_eed date;
2941 l_warning boolean;
2942 --
2943 cursor csr_entry(
2944 p_assignment_action_id number,
2945 p_expiry_date date) is
2946 select v.*
2947 from pay_jp_def_entry_v v
2948 where v.assignment_action_id = p_assignment_action_id
2949 and exists(
2950 select null
2951 from pay_element_entries_f pee
2952 where pee.element_entry_id = v.element_entry_id
2953 and p_expiry_date
2954 between pee.effective_start_date and pee.effective_end_date)
2955 for update nowait;
2956 cursor csr_dep(
2957 p_assignment_action_id number,
2958 p_expiry_date date) is
2959 select v.*
2960 from pay_jp_def_dep_v v
2961 where v.assignment_action_id = p_assignment_action_id
2962 and status <> 'D'
2963 and exists(
2964 select null
2965 from per_contact_extra_info_f cei
2966 where cei.contact_extra_info_id = v.contact_extra_info_id
2967 and p_expiry_date
2968 between cei.effective_start_date and cei.effective_end_date)
2969 for update nowait;
2970 cursor csr_dep_oe(
2971 p_assignment_action_id number,
2972 p_expiry_date date) is
2973 select v.*
2974 from pay_jp_def_dep_oe_v v
2975 where v.assignment_action_id = p_assignment_action_id
2976 and status <> 'D'
2977 and exists(
2978 select null
2979 from per_contact_extra_info_f cei
2980 where cei.contact_extra_info_id = v.contact_extra_info_id
2981 and p_expiry_date
2982 between cei.effective_start_date and cei.effective_end_date)
2983 for update nowait;
2984 cursor csr_dep_os(
2985 p_assignment_action_id number,
2986 p_expiry_date date) is
2987 select v.*
2988 from pay_jp_def_dep_os_v v
2989 where v.assignment_action_id = p_assignment_action_id
2990 and status <> 'D'
2991 and exists(
2992 select null
2993 from per_contact_extra_info_f cei
2994 where cei.contact_extra_info_id = v.contact_extra_info_id
2995 and p_expiry_date
2996 between cei.effective_start_date and cei.effective_end_date)
2997 for update nowait;
2998 begin
2999 hr_utility.set_location('Entering : ' || c_proc, 10);
3000 --
3001 lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
3002 --
3003 if l_assact_rec.transfer_status <> 'E' then
3004 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_EXPIRED_YET');
3005 fnd_message.raise_error;
3006 end if;
3007 --
3008 insert_session(l_assact_rec.expiry_date);
3009 --
3010 for l_rec in csr_entry(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3011 pay_element_entry_api.delete_element_entry(
3012 p_validate => false,
3013 p_effective_date => l_assact_rec.expiry_date,
3014 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3015 p_element_entry_id => l_rec.element_entry_id,
3016 p_object_version_number => l_rec.ee_object_version_number,
3017 p_effective_start_date => l_esd,
3018 p_effective_end_date => l_eed,
3019 p_delete_warning => l_warning);
3020 --
3021 pay_jp_def_api.update_entry(
3022 P_VALIDATE => false,
3023 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3024 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3025 P_EE_OBJECT_VERSION_NUMBER => l_rec.ee_object_version_number);
3026 end loop;
3027 --
3028 for l_rec in csr_dep(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3029 hr_contact_extra_info_api.delete_contact_extra_info(
3030 p_validate => false,
3031 p_effective_date => l_assact_rec.expiry_date,
3032 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3033 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3034 p_object_version_number => l_rec.cei_object_version_number,
3035 p_effective_start_date => l_esd,
3036 p_effective_end_date => l_eed);
3037 --
3038 pay_jp_def_api.update_dep(
3039 P_VALIDATE => false,
3040 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3041 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3042 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3043 end loop;
3044 --
3045 for l_rec in csr_dep_oe(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3046 hr_contact_extra_info_api.delete_contact_extra_info(
3047 p_validate => false,
3048 p_effective_date => l_assact_rec.expiry_date,
3049 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3050 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3051 p_object_version_number => l_rec.cei_object_version_number,
3052 p_effective_start_date => l_esd,
3053 p_effective_end_date => l_eed);
3054 --
3055 pay_jp_def_api.update_dep_oe(
3056 P_VALIDATE => false,
3057 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3058 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3059 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3060 end loop;
3061 --
3062 for l_rec in csr_dep_os(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3063 hr_contact_extra_info_api.delete_contact_extra_info(
3064 p_validate => false,
3065 p_effective_date => l_assact_rec.expiry_date,
3066 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3067 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3068 p_object_version_number => l_rec.cei_object_version_number,
3069 p_effective_start_date => l_esd,
3070 p_effective_end_date => l_eed);
3071 --
3072 pay_jp_def_api.update_dep_os(
3073 P_VALIDATE => false,
3074 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3075 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3076 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3077 end loop;
3078 --
3079 delete_session;
3080 --
3081 l_assact_rec.transfer_status := 'T';
3082 l_assact_rec.expiry_date := null;
3083 --
3084 pay_jp_def_api.update_assact(
3085 P_VALIDATE => false,
3086 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
3087 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
3088 P_TRANSFER_STATUS => l_assact_rec.transfer_status,
3089 P_EXPIRY_DATE => l_assact_rec.expiry_date);
3090 --
3091 p_object_version_number := l_assact_rec.object_version_number;
3092 --
3093 hr_utility.set_location('Leaving : ' || c_proc, 20);
3094 end rollback_expire;
3095 --
3096 procedure rollback_expire(
3097 p_action_information_id in number,
3098 p_object_version_number in out nocopy number,
3099 p_return_status out nocopy varchar2)
3100 is
3101 l_proc varchar2(61) := c_package || '.rollback_expire';
3102 begin
3103 savepoint rollback_expire;
3104 --
3105 -- Initialise Multiple Message Detection
3106 --
3107 hr_multi_message.enable_message_list;
3108 --
3109 rollback_expire(p_action_information_id, p_object_version_number);
3110 --
3111 p_return_status := hr_multi_message.get_return_status_disable;
3112 exception
3113 when hr_multi_message.error_message_exist then
3114 rollback to rollback_expire;
3115 p_object_version_number := null;
3116 p_return_status := hr_multi_message.get_return_status_disable;
3117 when others then
3118 rollback to rollback_expire;
3119 if hr_multi_message.unexpected_error_add(l_proc) then
3120 raise;
3121 end if;
3122 p_object_version_number := null;
3123 p_return_status := hr_multi_message.get_return_status_disable;
3124 end rollback_expire;
3125
3126 --
3127 --
3128 --
3129 --
3130 --
3131 -- |---------------------------------------------------------------------------|
3132 -- |--------------------------< delete_unfinalized >---------------------------|
3133 -- |---------------------------------------------------------------------------|
3134 procedure delete_unfinalized(
3135 errbuf out nocopy varchar2,
3136 retcode out nocopy varchar2,
3137 p_payroll_action_id in varchar2)
3138 is
3139 l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
3140 cursor csr_assact is
3141 select paa.assignment_action_id
3142 from pay_jp_def_assact_v aif,
3143 pay_assignment_actions paa
3144 where paa.payroll_action_id = l_payroll_action_id
3145 and aif.assignment_action_id(+) = paa.assignment_action_id
3146 and nvl(aif.transaction_status, 'U') not in ('F', 'A');
3147 begin
3148 --
3149 -- retcode
3150 -- 0 : Success
3151 -- 1 : Warning
3152 -- 2 : Error
3153 --
3154 retcode := '0';
3155 --
3156 for l_rec in csr_assact loop
3157 begin
3158 py_rollback_pkg.rollback_ass_action(
3159 p_assignment_action_id => l_rec.assignment_action_id,
3160 p_rollback_mode => 'ROLLBACK',
3161 p_leave_base_table_row => false,
3162 p_all_or_nothing => true,
3163 p_dml_mode => 'FULL');
3164 exception
3165 when others then
3166 retcode := '1';
3167 end;
3168 end loop;
3169 end delete_unfinalized;
3170 --
3171 /*
3172 begin
3173 hr_utility.trace_on('F', 'TTAGAWA');
3174 hr_utility.trace('c_package : ' || c_package);
3175 hr_utility.trace('c_def_elm : ' || c_def_elm);
3176 hr_utility.trace('c_disability_type_iv : ' || c_disability_type_iv);
3177 hr_utility.trace('c_aged_type_iv : ' || c_aged_type_iv);
3178 hr_utility.trace('c_widow_type_iv : ' || c_widow_type_iv);
3179 hr_utility.trace('c_working_student_type_iv : ' || c_working_student_type_iv);
3180 hr_utility.trace('c_spouse_dep_type_iv : ' || c_spouse_dep_type_iv);
3181 hr_utility.trace('c_spouse_disability_type_iv : ' || c_spouse_disability_type_iv);
3182 hr_utility.trace('c_num_deps_iv : ' || c_num_deps_iv);
3183 hr_utility.trace('c_num_ageds_iv : ' || c_num_ageds_iv);
3184 hr_utility.trace('c_num_aged_parents_iv : ' || c_num_aged_parents_iv);
3185 hr_utility.trace('c_num_specifieds_iv : ' || c_num_specifieds_iv);
3186 hr_utility.trace('c_num_disableds_iv : ' || c_num_disableds_iv);
3187 hr_utility.trace('c_num_svr_disableds_iv : ' || c_num_svr_disableds_iv);
3188 hr_utility.trace('c_num_svr_disableds_lt_iv : ' || c_num_svr_disableds_lt_iv);
3189 */
3190 end pay_jp_def_ss;