[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DEF_SS
Source
1 package body pay_jp_def_ss as
2 /* $Header: pyjpdefs.pkb 120.8 2011/02/25 09:40:17 keyazawa ship $ */
3 --
4 -- Constants
5 --
6 g_debug boolean := hr_utility.debug_enabled;
7 c_package constant varchar2(31) := 'pay_jp_def_ss.';
8 c_def_elm constant number := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_PROC', null, 'JP');
9 c_disability_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'DISABLE_TYPE');
10 c_aged_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'ELDER_TYPE');
11 c_widow_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'WIDOW_TYPE');
12 c_working_student_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'WORKING_STUDENT_TYPE');
13 c_spouse_dep_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'SPOUSE_TYPE');
14 c_spouse_disability_type_iv constant varchar2(30) := hr_jp_id_pkg.input_value_id(c_def_elm, 'SPOUSE_DISABLE_TYPE');
15 c_num_deps_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_DEP');
16 c_num_ageds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_ELDER_DEP');
17 c_num_aged_parents_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_ELDER_PARENT_LT');
18 c_num_specifieds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_SPECIFIC_DEP');
19 c_num_junior_deps_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm,'NUM_OF_JUNIOR_DEP');
20 c_num_disableds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_GEN_DISABLED');
21 c_num_svr_disableds_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_SEV_DISABLED');
22 c_num_svr_disableds_lt_iv constant number := hr_jp_id_pkg.input_value_id(c_def_elm, 'NUM_OF_SEV_DISABLED_LT');
23 --
24 c_org_iv_name constant pay_input_values_f.name%type := 'COM_ITX_INFO_WITHHOLD_AGENT_ENTRY_VALUE';
25 c_dep_exm_info_elm constant number := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_INFO',null,'JP');
26 c_dep_sec_sal_flag_iv constant number := hr_jp_id_pkg.input_value_id(c_dep_exm_info_elm,'SECOND_SAL_SUBMIT_FLAG');
27 --
28 -- |---------------------------------------------------------------------------|
29 -- |-----------------------< ee_datetrack_update_mode >------------------------|
30 -- |---------------------------------------------------------------------------|
31 function ee_datetrack_update_mode(
32 p_element_entry_id in number,
33 p_effective_start_date in date,
34 p_effective_end_date in date,
35 p_effective_date in date) return varchar2
36 is
37 l_datetrack_mode varchar2(30);
38 l_exists varchar2(1);
39 cursor csr_future_exists is
40 select 'Y'
41 from dual
42 where exists(
43 select null
44 from pay_element_entries_f
45 where element_entry_id = p_element_entry_id
46 and effective_start_date = p_effective_end_date + 1);
47 begin
48 if p_effective_start_date = p_effective_date then
49 l_datetrack_mode := 'CORRECTION';
50 else
51 open csr_future_exists;
52 fetch csr_future_exists into l_exists;
53 if csr_future_exists%notfound then
54 l_datetrack_mode := 'UPDATE';
55 else
56 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
57 end if;
58 end if;
59 --
60 return l_datetrack_mode;
61 end ee_datetrack_update_mode;
62 -- |---------------------------------------------------------------------------|
63 -- |-----------------------< cei_datetrack_update_mode >-----------------------|
64 -- |---------------------------------------------------------------------------|
65 function cei_datetrack_update_mode(
66 p_contact_extra_info_id in number,
67 p_effective_start_date in date,
68 p_effective_end_date in date,
69 p_effective_date in date) return varchar2
70 is
71 l_datetrack_mode varchar2(30);
72 l_exists varchar2(1);
73 cursor csr_future_exists is
74 select 'Y'
75 from dual
76 where exists(
77 select null
78 from per_contact_extra_info_f
79 where contact_extra_info_id = p_contact_extra_info_id
80 and effective_start_date = p_effective_end_date + 1);
81 begin
82 if p_effective_start_date = p_effective_date then
83 l_datetrack_mode := 'CORRECTION';
84 else
85 open csr_future_exists;
86 fetch csr_future_exists into l_exists;
87 if csr_future_exists%notfound then
88 l_datetrack_mode := 'UPDATE';
89 else
90 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
91 end if;
92 end if;
93 --
94 return l_datetrack_mode;
95 end cei_datetrack_update_mode;
96 -- |---------------------------------------------------------------------------|
97 -- |-----------------------< cei_datetrack_delete_mode >-----------------------|
98 -- |---------------------------------------------------------------------------|
99 function cei_datetrack_delete_mode(
100 p_contact_extra_info_id in number,
101 p_effective_start_date in date,
102 p_effective_end_date in date,
103 p_effective_date in date) return varchar2
104 is
105 l_datetrack_mode varchar2(30);
106 l_exists varchar2(1);
107 cursor csr_past_exists is
108 select 'Y'
109 from dual
110 where exists(
111 select null
112 from per_contact_extra_info_f
113 where contact_extra_info_id = p_contact_extra_info_id
114 and effective_end_date = p_effective_start_date - 1);
115 begin
116 if p_effective_start_date = p_effective_date then
117 open csr_past_exists;
118 fetch csr_past_exists into l_exists;
119 if csr_past_exists%notfound then
120 l_datetrack_mode := 'ZAP';
121 else
122 l_datetrack_mode := 'DELETE';
123 end if;
124 else
125 l_datetrack_mode := 'DELETE';
126 end if;
127 --
128 return l_datetrack_mode;
129 end cei_datetrack_delete_mode;
130 -- |---------------------------------------------------------------------------|
131 -- |-------------------------------< full_name >-------------------------------|
132 -- |---------------------------------------------------------------------------|
133 function full_name(
134 p_person_id in number,
135 p_effective_date in date) return varchar2
136 is
137 l_full_name per_all_people_f.full_name%type;
138 cursor csr_full_name is
139 select trim(per_information18 || ' ' || per_information19)
140 from per_all_people_f
141 where person_id = p_person_id
142 and p_effective_date
143 between effective_start_date and effective_end_date;
144 cursor csr_full_name2 is
145 select trim(per_information18 || ' ' || per_information19)
146 from per_all_people_f
147 where person_id = p_person_id
148 and start_date = effective_start_date;
149 begin
150 if p_person_id is not null then
151 open csr_full_name;
152 fetch csr_full_name into l_full_name;
153 if csr_full_name%notfound then
154 open csr_full_name2;
155 fetch csr_full_name2 into l_full_name;
156 close csr_full_name2;
157 end if;
158 close csr_full_name;
159 end if;
160 --
161 return l_full_name;
162 end full_name;
163 -- |---------------------------------------------------------------------------|
164 -- |----------------------------< insert_session >-----------------------------|
165 -- |---------------------------------------------------------------------------|
166 procedure insert_session(p_effective_date in date)
167 is
168 l_rowid rowid;
169 cursor csr_session is
170 select rowid
171 from fnd_sessions
172 where session_id = userenv('sessionid')
173 for update nowait;
174 begin
175 open csr_session;
176 fetch csr_session into l_rowid;
177 if csr_session%notfound then
178 insert into fnd_sessions(
179 session_id,
180 effective_date)
181 values( userenv('sessionid'),
182 p_effective_date);
183 else
184 update fnd_sessions
185 set effective_date = p_effective_date
186 where rowid = l_rowid;
187 end if;
188 close csr_session;
189 end insert_session;
190 -- |---------------------------------------------------------------------------|
191 -- |----------------------------< delete_session >-----------------------------|
192 -- |---------------------------------------------------------------------------|
193 procedure delete_session
194 is
195 begin
196 delete
197 from fnd_sessions
198 where session_id = userenv('sessionid');
199 end delete_session;
200 -- |---------------------------------------------------------------------------|
201 -- |--------------------------------< changed >--------------------------------|
202 -- |---------------------------------------------------------------------------|
203 function changed(
204 value1 in varchar2,
205 value2 in varchar2) return boolean
206 is
207 begin
208 if nvl(value1, hr_api.g_varchar2) <> nvl(value2, hr_api.g_varchar2) then
209 return true;
210 else
211 return false;
212 end if;
213 end changed;
214 --
215 function changed(
216 value1 in number,
217 value2 in number) return boolean
218 is
219 begin
220 if nvl(value1, hr_api.g_number) <> nvl(value2, hr_api.g_number) then
221 return true;
222 else
223 return false;
224 end if;
225 end changed;
226 --
227 function changed(
228 value1 in date,
229 value2 in date) return boolean
230 is
231 begin
232 if nvl(value1, hr_api.g_date) <> nvl(value2, hr_api.g_date) then
233 return true;
234 else
235 return false;
236 end if;
237 end changed;
238 -- |---------------------------------------------------------------------------|
239 -- |------------------------< check_submission_period >------------------------|
240 -- |---------------------------------------------------------------------------|
241 function check_submission_period(p_action_information_id in number) return date
242 is
243 cursor csr_pact is
244 select submission_period_status,
245 submission_start_date,
246 submission_end_date
247 from pay_jp_def_pact_v pact,
248 pay_assignment_actions paa,
249 pay_jp_def_assact_v assact
250 where assact.action_information_id = p_action_information_id
251 and paa.assignment_action_id = assact.assignment_action_id
252 and pact.payroll_action_id = paa.payroll_action_id;
253 l_pact_rec csr_pact%rowtype;
254 l_sysdate date;
255 begin
256 open csr_pact;
257 fetch csr_pact into l_pact_rec;
258 close csr_pact;
259 --
260 if l_pact_rec.submission_period_status = 'C' then
261 fnd_message.set_name('PAY', 'PAY_JP_DEF_PERIOD_CLOSED');
262 fnd_message.raise_error;
263 end if;
264 --
265 l_sysdate := sysdate;
266 --
267 if l_sysdate < nvl(l_pact_rec.submission_start_date, l_sysdate) then
268 fnd_message.set_name('PAY', 'PAY_JP_DEF_PERIOD_NOT_STARTED');
269 fnd_message.raise_error;
270 end if;
271 --
272 if l_sysdate > nvl(l_pact_rec.submission_end_date, l_sysdate) then
273 fnd_message.set_name('PAY', 'PAY_JP_DEF_PERIOD_EXPIRED');
274 fnd_message.raise_error;
275 end if;
276 --
277 return l_sysdate;
278 end check_submission_period;
279 --
280 procedure check_submission_period(p_action_information_id in number)
281 is
282 l_submission_date date;
283 begin
284 l_submission_date := check_submission_period(p_action_information_id);
285 end check_submission_period;
286 -- |---------------------------------------------------------------------------|
287 -- |------------------------------< get_sqlerrm >------------------------------|
288 -- |---------------------------------------------------------------------------|
289 function get_sqlerrm return varchar2
290 is
291 begin
292 if sqlcode = -20001 then
293 declare
294 l_sqlerrm varchar2(2000) := fnd_message.get;
295 begin
296 if l_sqlerrm is not null then
297 return l_sqlerrm;
298 else
299 return sqlerrm;
300 end if;
301 end;
302 else
303 return sqlerrm;
304 end if;
305 end get_sqlerrm;
306 --
307 --
308 --
309 --
310 --
311 -- |---------------------------------------------------------------------------|
312 -- |----------------------------< transfer_entry >-----------------------------|
313 -- |---------------------------------------------------------------------------|
314 procedure transfer_entry(
315 p_rec in out nocopy pay_jp_def_entry_v%rowtype,
316 p_dep_rec in out nocopy pay_jp_def_entry_dep_v%rowtype,
317 p_business_group_id in number)
318 is
319 c_proc constant varchar2(61) := c_package || '.transfer_entry';
320 l_esd date;
321 l_eed date;
322 l_warning boolean;
323 l_element_link_id number;
324 begin
325 hr_utility.set_location('Entering : ' || c_proc, 10);
326 hr_utility.trace('status : ' || p_rec.status);
327 --
328 if p_rec.status = 'I' then
329 l_element_link_id := hr_entry_api.get_link(
330 p_assignment_id => p_rec.assignment_id,
331 p_element_type_id => c_def_elm,
332 p_session_date => p_rec.effective_date);
333 --
334 pay_element_entry_api.create_element_entry(
335 p_validate => false,
336 p_effective_date => p_rec.effective_date,
337 p_business_group_id => p_business_group_id,
338 p_assignment_id => p_rec.assignment_id,
339 p_element_link_id => l_element_link_id,
340 p_entry_type => 'E',
341 p_input_value_id1 => c_disability_type_iv,
342 p_input_value_id2 => c_aged_type_iv,
343 p_input_value_id3 => c_widow_type_iv,
344 p_input_value_id4 => c_working_student_type_iv,
345 p_input_value_id5 => c_spouse_dep_type_iv,
346 p_input_value_id6 => c_spouse_disability_type_iv,
347 p_input_value_id7 => c_num_deps_iv,
348 p_input_value_id8 => c_num_ageds_iv,
349 p_input_value_id9 => c_num_aged_parents_iv,
350 p_input_value_id10 => c_num_specifieds_iv,
351 p_input_value_id11 => c_num_junior_deps_iv,
352 p_input_value_id12 => c_num_disableds_iv,
353 p_input_value_id13 => c_num_svr_disableds_iv,
354 p_input_value_id14 => c_num_svr_disableds_lt_iv,
355 p_entry_value1 => p_rec.disability_type,
356 p_entry_value2 => p_rec.aged_type,
357 p_entry_value3 => p_rec.widow_type,
358 p_entry_value4 => p_rec.working_student_type,
359 p_entry_value5 => p_rec.spouse_dep_type,
360 p_entry_value6 => p_rec.spouse_disability_type,
361 p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps),
362 p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds),
363 p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt),
364 p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds),
365 p_entry_value11 => fnd_number.number_to_canonical(p_dep_rec.num_junior_deps),
366 p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_disableds),
367 p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds),
368 p_entry_value14 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt),
369 p_element_entry_id => p_rec.element_entry_id,
370 p_object_version_number => p_rec.ee_object_version_number,
371 p_effective_start_date => l_esd,
372 p_effective_end_date => l_eed,
373 p_create_warning => l_warning);
374 --
375 pay_jp_def_api.update_entry(
376 P_VALIDATE => false,
377 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
378 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
379 P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
380 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
381 elsif p_rec.status = 'Q' then
382 if changed(p_rec.disability_type , p_rec.disability_type_o)
383 or changed(p_rec.aged_type , p_rec.aged_type_o)
384 or changed(p_rec.widow_type , p_rec.widow_type_o)
385 or changed(p_rec.working_student_type , p_rec.working_student_type_o)
386 or changed(p_rec.spouse_dep_type , p_rec.spouse_dep_type_o)
387 or changed(p_rec.spouse_disability_type , p_rec.spouse_disability_type_o)
388 or changed(p_rec.num_deps , p_rec.num_deps_o)
389 or changed(p_rec.num_ageds , p_rec.num_ageds_o)
390 or changed(p_rec.num_aged_parents_lt , p_rec.num_aged_parents_lt_o)
391 or changed(p_rec.num_specifieds , p_rec.num_specifieds_o)
392 or changed(p_dep_rec.num_junior_deps, p_dep_rec.num_junior_deps_o)
393 or changed(p_rec.num_disableds , p_rec.num_disableds_o)
394 or changed(p_rec.num_svr_disableds , p_rec.num_svr_disableds_o)
395 or changed(p_rec.num_svr_disableds_lt , p_rec.num_svr_disableds_lt_o) then
396 pay_element_entry_api.update_element_entry(
397 p_validate => false,
398 p_effective_date => p_rec.effective_date,
399 p_business_group_id => p_business_group_id,
400 p_datetrack_update_mode => p_rec.datetrack_update_mode,
401 p_element_entry_id => p_rec.element_entry_id,
402 p_object_version_number => p_rec.ee_object_version_number,
403 p_input_value_id1 => c_disability_type_iv,
404 p_input_value_id2 => c_aged_type_iv,
405 p_input_value_id3 => c_widow_type_iv,
406 p_input_value_id4 => c_working_student_type_iv,
407 p_input_value_id5 => c_spouse_dep_type_iv,
408 p_input_value_id6 => c_spouse_disability_type_iv,
409 p_input_value_id7 => c_num_deps_iv,
410 p_input_value_id8 => c_num_ageds_iv,
411 p_input_value_id9 => c_num_aged_parents_iv,
412 p_input_value_id10 => c_num_specifieds_iv,
413 p_input_value_id11 => c_num_junior_deps_iv,
414 p_input_value_id12 => c_num_disableds_iv,
415 p_input_value_id13 => c_num_svr_disableds_iv,
416 p_input_value_id14 => c_num_svr_disableds_lt_iv,
417 p_entry_value1 => p_rec.disability_type,
418 p_entry_value2 => p_rec.aged_type,
419 p_entry_value3 => p_rec.widow_type,
420 p_entry_value4 => p_rec.working_student_type,
421 p_entry_value5 => p_rec.spouse_dep_type,
422 p_entry_value6 => p_rec.spouse_disability_type,
423 p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps),
424 p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds),
425 p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt),
426 p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds),
427 p_entry_value11 => fnd_number.number_to_canonical(p_dep_rec.num_junior_deps),
428 p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_disableds),
429 p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds),
430 p_entry_value14 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt),
431 -- Aged Type can be defaulted to '0' after 2005/01/01
432 -- even user enterable is "No".
433 p_override_user_ent_chk => 'Y',
434 p_effective_start_date => l_esd,
435 p_effective_end_date => l_eed,
436 p_update_warning => l_warning);
437 --
438 p_rec.status := 'U';
439 --
440 pay_jp_def_api.update_entry(
441 P_VALIDATE => false,
442 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
443 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
444 P_STATUS => p_rec.status,
445 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
446 end if;
447 end if;
448 --
449 hr_utility.set_location('Leaving : ' || c_proc, 20);
450 end transfer_entry;
451 -- |---------------------------------------------------------------------------|
452 -- |----------------------------< rollback_entry >-----------------------------|
453 -- |---------------------------------------------------------------------------|
454 procedure rollback_entry(
455 p_rec in out nocopy pay_jp_def_entry_v%rowtype,
456 p_dep_rec in out nocopy pay_jp_def_entry_dep_v%rowtype,
457 p_business_group_id in number)
458 is
459 c_proc constant varchar2(61) := c_package || '.rollback_entry';
460 l_esd date;
461 l_eed date;
462 l_warning boolean;
463 l_vsd date;
464 l_ved date;
465 begin
466 hr_utility.set_location('Entering : ' || c_proc, 10);
467 hr_utility.trace('status : ' || p_rec.status);
468 --
469 if p_rec.status = 'I' then
470 pay_element_entry_api.delete_element_entry(
471 p_validate => false,
472 p_effective_date => p_rec.effective_date,
473 p_datetrack_delete_mode => 'ZAP',
474 p_element_entry_id => p_rec.element_entry_id,
475 p_object_version_number => p_rec.ee_object_version_number,
476 p_effective_start_date => l_esd,
477 p_effective_end_date => l_eed,
478 p_delete_warning => l_warning);
479 --
480 p_rec.element_entry_id := null;
481 p_rec.ee_object_version_number := null;
482 --
483 pay_jp_def_api.update_entry(
484 P_VALIDATE => false,
485 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
486 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
487 P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
488 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
489 elsif p_rec.status = 'U' then
490 if p_rec.datetrack_update_mode = 'CORRECTION' then
491 pay_element_entry_api.update_element_entry(
492 p_validate => false,
493 p_effective_date => p_rec.effective_date,
494 p_business_group_id => p_business_group_id,
495 p_datetrack_update_mode => p_rec.datetrack_update_mode,
496 p_element_entry_id => p_rec.element_entry_id,
497 p_object_version_number => p_rec.ee_object_version_number,
498 p_input_value_id1 => c_disability_type_iv,
499 p_input_value_id2 => c_aged_type_iv,
500 p_input_value_id3 => c_widow_type_iv,
501 p_input_value_id4 => c_working_student_type_iv,
502 p_input_value_id5 => c_spouse_dep_type_iv,
503 p_input_value_id6 => c_spouse_disability_type_iv,
504 p_input_value_id7 => c_num_deps_iv,
505 p_input_value_id8 => c_num_ageds_iv,
506 p_input_value_id9 => c_num_aged_parents_iv,
507 p_input_value_id10 => c_num_specifieds_iv,
508 p_input_value_id11 => c_num_junior_deps_iv,
509 p_input_value_id12 => c_num_disableds_iv,
510 p_input_value_id13 => c_num_svr_disableds_iv,
511 p_input_value_id14 => c_num_svr_disableds_lt_iv,
512 p_entry_value1 => p_rec.disability_type_o,
513 p_entry_value2 => p_rec.aged_type_o,
514 p_entry_value3 => p_rec.widow_type_o,
515 p_entry_value4 => p_rec.working_student_type_o,
516 p_entry_value5 => p_rec.spouse_dep_type_o,
517 p_entry_value6 => p_rec.spouse_disability_type_o,
518 p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps_o),
519 p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds_o),
520 p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt_o),
521 p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds_o),
522 p_entry_value11 => fnd_number.number_to_canonical(p_dep_rec.num_junior_deps_o),
523 p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_disableds_o),
524 p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_o),
525 p_entry_value14 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt_o),
526 -- Aged Type could be defaulted to '0' after 2005/01/01
527 -- even user enterable is "No".
528 p_override_user_ent_chk => 'Y',
529 p_effective_start_date => l_esd,
530 p_effective_end_date => l_eed,
531 p_update_warning => l_warning);
532 else
533 --
534 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
535 -- should be current OVN - 1. See API for more details.
536 -- !!!!!
537 -- This does not work after expired, because OVN of latest record is updated.
538 -- At first, lock the current record. If locked successfully,
539 -- derive OVN of previous record.
540 --
541 pay_ele_shd.lck(
542 p_effective_date => p_rec.effective_date,
543 p_datetrack_mode => 'CORRECTION',
544 p_element_entry_id => p_rec.element_entry_id,
545 p_object_version_number => p_rec.ee_object_version_number,
546 p_validation_start_date => l_vsd,
547 p_validation_end_date => l_ved);
548 --
549 select object_version_number
550 into p_rec.ee_object_version_number
551 from pay_element_entries_f
552 where element_entry_id = p_rec.element_entry_id
553 and effective_end_date = p_rec.effective_date - 1;
554 --
555 pay_element_entry_api.delete_element_entry(
556 p_validate => false,
557 p_effective_date => p_rec.effective_date - 1,
558 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
559 p_element_entry_id => p_rec.element_entry_id,
560 p_object_version_number => p_rec.ee_object_version_number,
561 p_effective_start_date => l_esd,
562 p_effective_end_date => l_eed,
563 p_delete_warning => l_warning);
564 end if;
565 --
566 p_rec.status := 'Q';
567 --
568 pay_jp_def_api.update_entry(
569 P_VALIDATE => false,
570 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
571 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
572 P_STATUS => p_rec.status,
573 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
574 end if;
575 --
576 hr_utility.set_location('Leaving : ' || c_proc, 20);
577 end rollback_entry;
578 -- |---------------------------------------------------------------------------|
579 -- |-----------------------------< transfer_dep >------------------------------|
580 -- |---------------------------------------------------------------------------|
581 procedure transfer_dep(p_rec in out nocopy pay_jp_def_dep_v%rowtype)
582 is
583 c_proc constant varchar2(61) := c_package || '.transfer_dep';
584 l_esd date;
585 l_eed date;
586 l_effective_date date;
587 begin
588 hr_utility.set_location('Entering : ' || c_proc, 10);
589 hr_utility.trace('status : ' || p_rec.status);
590 --
591 if p_rec.status = 'I' then
592 hr_contact_extra_info_api.create_contact_extra_info(
593 p_validate => false,
594 p_effective_date => p_rec.effective_date,
595 p_contact_relationship_id => p_rec.contact_relationship_id,
596 p_information_type => 'JP_ITAX_DEPENDENT',
597 p_cei_information_category => 'JP_ITAX_DEPENDENT',
598 p_cei_information2 => p_rec.occupation,
599 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income),
600 p_cei_information4 => fnd_date.date_to_canonical(p_rec.change_date),
601 p_cei_information5 => p_rec.change_reason,
602 p_cei_information6 => p_rec.disability_type,
603 p_cei_information7 => p_rec.disability_details,
604 p_cei_information8 => p_rec.dep_type,
605 p_contact_extra_info_id => p_rec.contact_extra_info_id,
606 p_object_version_number => p_rec.cei_object_version_number,
607 p_effective_start_date => l_esd,
608 p_effective_end_date => l_eed);
609 --
610 pay_jp_def_api.update_dep(
611 P_VALIDATE => false,
612 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
613 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
614 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
615 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
616 elsif p_rec.status = 'Q' then
617 if p_rec.change_date is not null
618 or p_rec.change_reason is not null
619 or changed(p_rec.dep_type , p_rec.dep_type_o)
620 or changed(p_rec.occupation , p_rec.occupation_o)
621 or changed(p_rec.estimated_annual_income, p_rec.estimated_annual_income_o)
622 or changed(p_rec.disability_type , p_rec.disability_type_o)
623 or changed(p_rec.disability_details , p_rec.disability_details_o) then
624 hr_contact_extra_info_api.update_contact_extra_info(
625 p_validate => false,
626 p_effective_date => p_rec.effective_date,
627 p_datetrack_update_mode => p_rec.datetrack_update_mode,
628 p_contact_extra_info_id => p_rec.contact_extra_info_id,
629 p_object_version_number => p_rec.cei_object_version_number,
630 p_cei_information4 => fnd_date.date_to_canonical(p_rec.change_date),
631 p_cei_information5 => p_rec.change_reason,
632 p_cei_information8 => p_rec.dep_type,
633 p_cei_information2 => p_rec.occupation,
634 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income),
635 p_cei_information6 => p_rec.disability_type,
636 p_cei_information7 => p_rec.disability_details,
637 p_effective_start_date => l_esd,
638 p_effective_end_date => l_eed);
639 --
640 p_rec.status := 'U';
641 --
642 pay_jp_def_api.update_dep(
643 P_VALIDATE => false,
644 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
645 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
646 P_STATUS => p_rec.status,
647 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
648 end if;
649 elsif p_rec.status = 'D' then
650 if p_rec.datetrack_delete_mode = 'ZAP' then
651 l_effective_date := p_rec.effective_date;
652 else
653 l_effective_date := p_rec.effective_date - 1;
654 end if;
655 --
656 hr_contact_extra_info_api.delete_contact_extra_info(
657 p_validate => false,
658 p_effective_date => l_effective_date,
659 p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
660 p_contact_extra_info_id => p_rec.contact_extra_info_id,
661 p_object_version_number => p_rec.cei_object_version_number,
662 p_effective_start_date => l_esd,
663 p_effective_end_date => l_eed);
664 --
665 if p_rec.datetrack_delete_mode <> 'ZAP' then
666 pay_jp_def_api.update_dep(
667 P_VALIDATE => false,
668 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
669 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
670 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
671 end if;
672 end if;
673 --
674 hr_utility.set_location('Leaving : ' || c_proc, 20);
675 end transfer_dep;
676 -- |---------------------------------------------------------------------------|
677 -- |-----------------------------< rollback_dep >------------------------------|
678 -- |---------------------------------------------------------------------------|
679 procedure rollback_dep(p_rec in out nocopy pay_jp_def_dep_v%rowtype)
680 is
681 c_proc constant varchar2(61) := c_package || '.rollback_dep';
682 l_esd date;
683 l_eed date;
684 l_vsd date;
685 l_ved date;
686 begin
687 hr_utility.set_location('Entering : ' || c_proc, 10);
688 hr_utility.trace('status : ' || p_rec.status);
689 --
690 if p_rec.status = 'I' then
691 hr_contact_extra_info_api.delete_contact_extra_info(
692 p_validate => false,
693 p_effective_date => p_rec.effective_date,
694 p_datetrack_delete_mode => 'ZAP',
695 p_contact_extra_info_id => p_rec.contact_extra_info_id,
696 p_object_version_number => p_rec.cei_object_version_number,
697 p_effective_start_date => l_esd,
698 p_effective_end_date => l_eed);
699 --
700 p_rec.contact_extra_info_id := null;
701 p_rec.cei_object_version_number := null;
702 --
703 pay_jp_def_api.update_dep(
704 P_VALIDATE => false,
705 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
706 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
707 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
708 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
709 elsif p_rec.status = 'U' then
710 if p_rec.datetrack_update_mode = 'CORRECTION' then
711 hr_contact_extra_info_api.update_contact_extra_info(
712 p_validate => false,
713 p_effective_date => p_rec.effective_date,
714 p_datetrack_update_mode => p_rec.datetrack_update_mode,
715 p_contact_extra_info_id => p_rec.contact_extra_info_id,
716 p_object_version_number => p_rec.cei_object_version_number,
717 -- Rollback change_date/change_reason is not supported for CORRECTION mode.
718 p_cei_information8 => p_rec.dep_type_o,
719 p_cei_information2 => p_rec.occupation_o,
720 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income_o),
721 p_cei_information6 => p_rec.disability_type_o,
722 p_cei_information7 => p_rec.disability_details_o,
723 p_effective_start_date => l_esd,
724 p_effective_end_date => l_eed);
725 else
726 --
727 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
728 -- should be current OVN - 1. See API for more details.
729 -- !!!!!
730 -- This does not work after expired, because OVN of latest record is updated.
731 -- At first, lock the current record. If locked successfully,
732 -- derive OVN of previous record.
733 --
734 per_rei_shd.lck(
735 p_effective_date => p_rec.effective_date,
736 p_datetrack_mode => 'CORRECTION',
737 p_contact_extra_info_id => p_rec.contact_extra_info_id,
738 p_object_version_number => p_rec.cei_object_version_number,
739 p_validation_start_date => l_vsd,
740 p_validation_end_date => l_ved);
741 --
742 select object_version_number
743 into p_rec.cei_object_version_number
744 from per_contact_extra_info_f
745 where contact_extra_info_id = p_rec.contact_extra_info_id
746 and effective_end_date = p_rec.effective_date - 1;
747 --
748 hr_contact_extra_info_api.delete_contact_extra_info(
749 p_validate => false,
750 p_effective_date => p_rec.effective_date - 1,
751 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
752 p_contact_extra_info_id => p_rec.contact_extra_info_id,
753 p_object_version_number => p_rec.cei_object_version_number,
754 p_effective_start_date => l_esd,
755 p_effective_end_date => l_eed);
756 end if;
757 --
758 p_rec.status := 'Q';
759 --
760 pay_jp_def_api.update_dep(
761 P_VALIDATE => false,
762 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
763 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
764 P_STATUS => p_rec.status,
765 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
766 elsif p_rec.status = 'D' then
767 if p_rec.datetrack_delete_mode = 'ZAP' then
768 --
769 -- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
770 -- for ZAP case.
771 --
772 hr_contact_extra_info_api.create_contact_extra_info(
773 p_validate => false,
774 p_effective_date => p_rec.effective_date,
775 p_contact_relationship_id => p_rec.contact_relationship_id,
776 p_information_type => 'JP_ITAX_DEPENDENT',
777 p_cei_information_category => 'JP_ITAX_DEPENDENT',
778 p_cei_information2 => p_rec.occupation_o,
779 p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income_o),
780 p_cei_information6 => p_rec.disability_type_o,
781 p_cei_information7 => p_rec.disability_details_o,
782 p_cei_information8 => p_rec.dep_type_o,
783 p_contact_extra_info_id => p_rec.contact_extra_info_id,
784 p_object_version_number => p_rec.cei_object_version_number,
785 p_effective_start_date => l_esd,
786 p_effective_end_date => l_eed);
787 else
788 hr_contact_extra_info_api.delete_contact_extra_info(
789 p_validate => false,
790 p_effective_date => p_rec.effective_date - 1,
791 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
792 p_contact_extra_info_id => p_rec.contact_extra_info_id,
793 p_object_version_number => p_rec.cei_object_version_number,
794 p_effective_start_date => l_esd,
795 p_effective_end_date => l_eed);
796 end if;
797 --
798 pay_jp_def_api.update_dep(
799 P_VALIDATE => false,
800 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
801 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
802 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
803 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
804 end if;
805 --
806 hr_utility.set_location('Leaving : ' || c_proc, 20);
807 end rollback_dep;
808 -- |---------------------------------------------------------------------------|
809 -- |----------------------------< transfer_dep_oe >----------------------------|
810 -- |---------------------------------------------------------------------------|
811 procedure transfer_dep_oe(p_rec in out nocopy pay_jp_def_dep_oe_v%rowtype)
812 is
813 c_proc constant varchar2(61) := c_package || '.transfer_dep_oe';
814 l_esd date;
815 l_eed date;
816 l_effective_date date;
817 begin
818 hr_utility.set_location('Entering : ' || c_proc, 10);
819 hr_utility.trace('status : ' || p_rec.status);
820 --
821 if p_rec.status = 'I' then
822 hr_contact_extra_info_api.create_contact_extra_info(
823 p_validate => false,
824 p_effective_date => p_rec.effective_date,
825 p_contact_relationship_id => p_rec.contact_relationship_id,
826 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
827 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
828 p_cei_information1 => p_rec.occupation,
829 p_cei_information2 => fnd_date.date_to_canonical(p_rec.change_date),
830 p_cei_information3 => p_rec.change_reason,
831 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id),
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_effective_start_date => l_esd,
835 p_effective_end_date => l_eed);
836 --
837 pay_jp_def_api.update_dep_oe(
838 P_VALIDATE => false,
839 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
840 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
841 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
842 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
843 elsif p_rec.status = 'Q' then
844 if p_rec.change_date is not null
845 or p_rec.change_reason is not null
846 or changed(p_rec.occupation , p_rec.occupation_o)
847 or changed(p_rec.oe_contact_relationship_id , p_rec.oe_contact_relationship_id_o) then
848 hr_contact_extra_info_api.update_contact_extra_info(
849 p_validate => false,
850 p_effective_date => p_rec.effective_date,
851 p_datetrack_update_mode => p_rec.datetrack_update_mode,
852 p_contact_extra_info_id => p_rec.contact_extra_info_id,
853 p_object_version_number => p_rec.cei_object_version_number,
854 p_cei_information2 => fnd_date.date_to_canonical(p_rec.change_date),
855 p_cei_information3 => p_rec.change_reason,
856 p_cei_information1 => p_rec.occupation,
857 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id),
858 p_effective_start_date => l_esd,
859 p_effective_end_date => l_eed);
860 --
861 p_rec.status := 'U';
862 --
863 pay_jp_def_api.update_dep_oe(
864 P_VALIDATE => false,
865 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
866 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
867 P_STATUS => p_rec.status,
868 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
869 end if;
870 elsif p_rec.status = 'D' then
871 if p_rec.datetrack_delete_mode = 'ZAP' then
872 l_effective_date := p_rec.effective_date;
873 else
874 l_effective_date := p_rec.effective_date - 1;
875 end if;
876 --
877 hr_contact_extra_info_api.delete_contact_extra_info(
878 p_validate => false,
879 p_effective_date => l_effective_date,
880 p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
881 p_contact_extra_info_id => p_rec.contact_extra_info_id,
882 p_object_version_number => p_rec.cei_object_version_number,
883 p_effective_start_date => l_esd,
884 p_effective_end_date => l_eed);
885 --
886 if p_rec.datetrack_delete_mode <> 'ZAP' then
887 pay_jp_def_api.update_dep(
888 P_VALIDATE => false,
889 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
890 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
891 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
892 end if;
893 end if;
894 --
895 hr_utility.set_location('Leaving : ' || c_proc, 20);
896 end transfer_dep_oe;
897 -- |---------------------------------------------------------------------------|
898 -- |-----------------------------< rollback_dep >------------------------------|
899 -- |---------------------------------------------------------------------------|
900 procedure rollback_dep_oe(p_rec in out nocopy pay_jp_def_dep_oe_v%rowtype)
901 is
902 c_proc constant varchar2(61) := c_package || '.rollback_dep_oe';
903 l_esd date;
904 l_eed date;
905 l_vsd date;
906 l_ved date;
907 begin
908 hr_utility.set_location('Entering : ' || c_proc, 10);
909 hr_utility.trace('status : ' || p_rec.status);
910 --
911 if p_rec.status = 'I' then
912 hr_contact_extra_info_api.delete_contact_extra_info(
913 p_validate => false,
914 p_effective_date => p_rec.effective_date,
915 p_datetrack_delete_mode => 'ZAP',
916 p_contact_extra_info_id => p_rec.contact_extra_info_id,
917 p_object_version_number => p_rec.cei_object_version_number,
918 p_effective_start_date => l_esd,
919 p_effective_end_date => l_eed);
920 --
921 p_rec.contact_extra_info_id := null;
922 p_rec.cei_object_version_number := null;
923 --
924 pay_jp_def_api.update_dep_oe(
925 P_VALIDATE => false,
926 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
927 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
928 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
929 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
930 elsif p_rec.status = 'U' then
931 if p_rec.datetrack_update_mode = 'CORRECTION' then
932 hr_contact_extra_info_api.update_contact_extra_info(
933 p_validate => false,
934 p_effective_date => p_rec.effective_date,
935 p_datetrack_update_mode => p_rec.datetrack_update_mode,
936 p_contact_extra_info_id => p_rec.contact_extra_info_id,
937 p_object_version_number => p_rec.cei_object_version_number,
938 -- Rollback change_date/change_reason is not supported for CORRECTION mode.
939 p_cei_information1 => p_rec.occupation_o,
940 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id_o),
941 p_effective_start_date => l_esd,
942 p_effective_end_date => l_eed);
943 else
944 --
945 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
946 -- should be current OVN - 1. See API for more details.
947 -- !!!!!
948 -- This does not work after expired, because OVN of latest record is updated.
949 -- At first, lock the current record. If locked successfully,
950 -- derive OVN of previous record.
951 --
952 per_rei_shd.lck(
953 p_effective_date => p_rec.effective_date,
954 p_datetrack_mode => 'CORRECTION',
955 p_contact_extra_info_id => p_rec.contact_extra_info_id,
956 p_object_version_number => p_rec.cei_object_version_number,
957 p_validation_start_date => l_vsd,
958 p_validation_end_date => l_ved);
959 --
960 select object_version_number
961 into p_rec.cei_object_version_number
962 from per_contact_extra_info_f
963 where contact_extra_info_id = p_rec.contact_extra_info_id
964 and effective_end_date = p_rec.effective_date - 1;
965 --
966 hr_contact_extra_info_api.delete_contact_extra_info(
967 p_validate => false,
968 p_effective_date => p_rec.effective_date - 1,
969 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
970 p_contact_extra_info_id => p_rec.contact_extra_info_id,
971 p_object_version_number => p_rec.cei_object_version_number,
972 p_effective_start_date => l_esd,
973 p_effective_end_date => l_eed);
974 end if;
975 --
976 p_rec.status := 'Q';
977 --
978 pay_jp_def_api.update_dep_oe(
979 P_VALIDATE => false,
980 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
981 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
982 P_STATUS => p_rec.status,
983 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
984 elsif p_rec.status = 'D' then
985 if p_rec.datetrack_delete_mode = 'ZAP' then
986 --
987 -- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
988 -- for ZAP case.
989 --
990 hr_contact_extra_info_api.create_contact_extra_info(
991 p_validate => false,
992 p_effective_date => p_rec.effective_date,
993 p_contact_relationship_id => p_rec.contact_relationship_id,
994 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
995 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
996 p_cei_information1 => p_rec.occupation_o,
997 p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id_o),
998 p_contact_extra_info_id => p_rec.contact_extra_info_id,
999 p_object_version_number => p_rec.cei_object_version_number,
1000 p_effective_start_date => l_esd,
1001 p_effective_end_date => l_eed);
1002 else
1003 hr_contact_extra_info_api.delete_contact_extra_info(
1004 p_validate => false,
1005 p_effective_date => p_rec.effective_date - 1,
1006 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
1007 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1008 p_object_version_number => p_rec.cei_object_version_number,
1009 p_effective_start_date => l_esd,
1010 p_effective_end_date => l_eed);
1011 end if;
1012 --
1013 pay_jp_def_api.update_dep_oe(
1014 P_VALIDATE => false,
1015 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1016 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1017 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1018 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1019 end if;
1020 --
1021 hr_utility.set_location('Leaving : ' || c_proc, 20);
1022 end rollback_dep_oe;
1023 -- |---------------------------------------------------------------------------|
1024 -- |----------------------------< transfer_dep_os >----------------------------|
1025 -- |---------------------------------------------------------------------------|
1026 procedure transfer_dep_os(p_rec in out nocopy pay_jp_def_dep_os_v%rowtype)
1027 is
1028 c_proc constant varchar2(61) := c_package || '.transfer_dep_os';
1029 l_esd date;
1030 l_eed date;
1031 l_effective_date date;
1032 begin
1033 hr_utility.set_location('Entering : ' || c_proc, 10);
1034 hr_utility.trace('status : ' || p_rec.status);
1035 --
1036 if p_rec.status = 'I' then
1037 hr_contact_extra_info_api.create_contact_extra_info(
1038 p_validate => false,
1039 p_effective_date => p_rec.effective_date,
1040 p_contact_relationship_id => p_rec.contact_relationship_id,
1041 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1042 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1043 p_cei_information1 => p_rec.occupation,
1044 p_cei_information2 => p_rec.os_salary_payer_name,
1045 p_cei_information3 => p_rec.os_salary_payer_address,
1046 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1047 p_object_version_number => p_rec.cei_object_version_number,
1048 p_effective_start_date => l_esd,
1049 p_effective_end_date => l_eed);
1050 --
1051 pay_jp_def_api.update_dep_os(
1052 P_VALIDATE => false,
1053 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1054 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1055 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1056 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1057 elsif p_rec.status = 'Q' then
1058 if changed(p_rec.occupation , p_rec.occupation_o)
1059 or changed(p_rec.os_salary_payer_name , p_rec.os_salary_payer_name_o)
1060 or changed(p_rec.os_salary_payer_address, p_rec.os_salary_payer_address_o) then
1061 p_rec.status := 'U';
1062 --
1063 hr_contact_extra_info_api.update_contact_extra_info(
1064 p_validate => false,
1065 p_effective_date => p_rec.effective_date,
1066 p_datetrack_update_mode => p_rec.datetrack_update_mode,
1067 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1068 p_object_version_number => p_rec.cei_object_version_number,
1069 p_cei_information1 => p_rec.occupation,
1070 p_cei_information2 => p_rec.os_salary_payer_name,
1071 p_cei_information3 => p_rec.os_salary_payer_address,
1072 p_effective_start_date => l_esd,
1073 p_effective_end_date => l_eed);
1074 --
1075 p_rec.status := 'U';
1076 --
1077 pay_jp_def_api.update_dep_os(
1078 P_VALIDATE => false,
1079 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1080 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1081 P_STATUS => p_rec.status,
1082 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1083 end if;
1084 elsif p_rec.status = 'D' then
1085 if p_rec.datetrack_delete_mode = 'ZAP' then
1086 l_effective_date := p_rec.effective_date;
1087 else
1088 l_effective_date := p_rec.effective_date - 1;
1089 end if;
1090 --
1091 hr_contact_extra_info_api.delete_contact_extra_info(
1092 p_validate => false,
1093 p_effective_date => l_effective_date,
1094 p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
1095 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1096 p_object_version_number => p_rec.cei_object_version_number,
1097 p_effective_start_date => l_esd,
1098 p_effective_end_date => l_eed);
1099 --
1100 if p_rec.datetrack_delete_mode <> 'ZAP' then
1101 pay_jp_def_api.update_dep(
1102 P_VALIDATE => false,
1103 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1104 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1105 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1106 end if;
1107 end if;
1108 --
1109 hr_utility.set_location('Leaving : ' || c_proc, 20);
1110 end transfer_dep_os;
1111 -- |---------------------------------------------------------------------------|
1112 -- |-----------------------------< rollback_dep_os >---------------------------|
1113 -- |---------------------------------------------------------------------------|
1114 procedure rollback_dep_os(p_rec in out nocopy pay_jp_def_dep_os_v%rowtype)
1115 is
1116 c_proc constant varchar2(61) := c_package || '.rollback_dep_os';
1117 l_esd date;
1118 l_eed date;
1119 l_vsd date;
1120 l_ved date;
1121 begin
1122 hr_utility.set_location('Entering : ' || c_proc, 10);
1123 hr_utility.trace('status : ' || p_rec.status);
1124 --
1125 if p_rec.status = 'I' then
1126 hr_contact_extra_info_api.delete_contact_extra_info(
1127 p_validate => false,
1128 p_effective_date => p_rec.effective_date,
1129 p_datetrack_delete_mode => 'ZAP',
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_effective_start_date => l_esd,
1133 p_effective_end_date => l_eed);
1134 --
1135 p_rec.contact_extra_info_id := null;
1136 p_rec.cei_object_version_number := null;
1137 --
1138 pay_jp_def_api.update_dep_os(
1139 P_VALIDATE => false,
1140 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1141 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1142 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1143 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1144 elsif p_rec.status = 'U' then
1145 if p_rec.datetrack_update_mode = 'CORRECTION' then
1146 hr_contact_extra_info_api.update_contact_extra_info(
1147 p_validate => false,
1148 p_effective_date => p_rec.effective_date,
1149 p_datetrack_update_mode => p_rec.datetrack_update_mode,
1150 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1151 p_object_version_number => p_rec.cei_object_version_number,
1152 p_cei_information1 => p_rec.occupation_o,
1153 p_cei_information2 => p_rec.os_salary_payer_name_o,
1154 p_cei_information3 => p_rec.os_salary_payer_address_o,
1155 p_effective_start_date => l_esd,
1156 p_effective_end_date => l_eed);
1157 else
1158 --
1159 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
1160 -- should be current OVN - 1. See API for more details.
1161 -- !!!!!
1162 -- This does not work after expired, because OVN of latest record is updated.
1163 -- At first, lock the current record. If locked successfully,
1164 -- derive OVN of previous record.
1165 --
1166 per_rei_shd.lck(
1167 p_effective_date => p_rec.effective_date,
1168 p_datetrack_mode => 'CORRECTION',
1169 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1170 p_object_version_number => p_rec.cei_object_version_number,
1171 p_validation_start_date => l_vsd,
1172 p_validation_end_date => l_ved);
1173 --
1174 select object_version_number
1175 into p_rec.cei_object_version_number
1176 from per_contact_extra_info_f
1177 where contact_extra_info_id = p_rec.contact_extra_info_id
1178 and effective_end_date = p_rec.effective_date - 1;
1179 --
1180 hr_contact_extra_info_api.delete_contact_extra_info(
1181 p_validate => false,
1182 p_effective_date => p_rec.effective_date - 1,
1183 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
1184 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1185 p_object_version_number => p_rec.cei_object_version_number,
1186 p_effective_start_date => l_esd,
1187 p_effective_end_date => l_eed);
1188 end if;
1189 --
1190 p_rec.status := 'Q';
1191 --
1192 pay_jp_def_api.update_dep_os(
1193 P_VALIDATE => false,
1194 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1195 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1196 P_STATUS => p_rec.status,
1197 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1198 elsif p_rec.status = 'D' then
1199 if p_rec.datetrack_delete_mode = 'ZAP' then
1200 --
1201 -- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
1202 -- for ZAP case.
1203 --
1204 hr_contact_extra_info_api.create_contact_extra_info(
1205 p_validate => false,
1206 p_effective_date => p_rec.effective_date,
1207 p_contact_relationship_id => p_rec.contact_relationship_id,
1208 p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1209 p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
1210 p_cei_information1 => p_rec.occupation_o,
1211 p_cei_information2 => p_rec.os_salary_payer_name_o,
1212 p_cei_information3 => p_rec.os_salary_payer_address_o,
1213 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1214 p_object_version_number => p_rec.cei_object_version_number,
1215 p_effective_start_date => l_esd,
1216 p_effective_end_date => l_eed);
1217 else
1218 hr_contact_extra_info_api.delete_contact_extra_info(
1219 p_validate => false,
1220 p_effective_date => p_rec.effective_date - 1,
1221 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
1222 p_contact_extra_info_id => p_rec.contact_extra_info_id,
1223 p_object_version_number => p_rec.cei_object_version_number,
1224 p_effective_start_date => l_esd,
1225 p_effective_end_date => l_eed);
1226 end if;
1227 --
1228 pay_jp_def_api.update_dep_os(
1229 P_VALIDATE => false,
1230 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1231 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1232 P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
1233 P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
1234 end if;
1235 --
1236 hr_utility.set_location('Leaving : ' || c_proc, 20);
1237 end rollback_dep_os;
1238 --
1239 -- -------------------------------------------------------------------------
1240 -- transfer_entry_os
1241 -- -------------------------------------------------------------------------
1242 procedure transfer_entry_os(
1243 p_rec in out nocopy pay_jp_def_entry_os_v%rowtype,
1244 p_business_group_id in number)
1245 is
1246 --
1247 c_proc constant varchar2(61) := c_package ||'.transfer_entry_os';
1248 --
1249 l_esd date;
1250 l_eed date;
1251 l_warning boolean;
1252 l_element_link_id number;
1253 --
1254 begin
1255 --
1256 hr_utility.set_location('Entering : '|| c_proc, 10);
1257 hr_utility.trace('status : '|| p_rec.status);
1258 --
1259 if p_rec.status = 'I' then
1260 --
1261 l_element_link_id := hr_entry_api.get_link(
1262 p_assignment_id => p_rec.assignment_id,
1263 p_element_type_id => c_dep_exm_info_elm,
1264 p_session_date => p_rec.effective_date);
1265 --
1266 pay_element_entry_api.create_element_entry(
1267 p_validate => false,
1268 p_effective_date => p_rec.effective_date,
1269 p_business_group_id => p_business_group_id,
1270 p_assignment_id => p_rec.assignment_id,
1271 p_element_link_id => l_element_link_id,
1272 p_entry_type => 'E',
1273 p_input_value_id1 => c_dep_sec_sal_flag_iv,
1274 p_entry_value1 => p_rec.sec_sal_submit_flag,
1275 p_element_entry_id => p_rec.element_entry_id,
1276 p_object_version_number => p_rec.ee_object_version_number,
1277 p_effective_start_date => l_esd,
1278 p_effective_end_date => l_eed,
1279 p_create_warning => l_warning);
1280 --
1281 pay_jp_def_api.update_entry_os(
1282 p_validate => false,
1283 p_action_information_id => p_rec.action_information_id,
1284 p_object_version_number => p_rec.object_version_number,
1285 p_element_entry_id => p_rec.element_entry_id,
1286 p_ee_object_version_number => p_rec.ee_object_version_number);
1287 --
1288 elsif p_rec.status = 'Q' then
1289 --
1290 if changed(p_rec.sec_sal_submit_flag,p_rec.sec_sal_submit_flag_o) then
1291 --
1292 pay_element_entry_api.update_element_entry(
1293 p_validate => false,
1294 p_effective_date => p_rec.effective_date,
1295 p_business_group_id => p_business_group_id,
1296 p_datetrack_update_mode => p_rec.datetrack_update_mode,
1297 p_element_entry_id => p_rec.element_entry_id,
1298 p_object_version_number => p_rec.ee_object_version_number,
1299 p_input_value_id1 => c_dep_sec_sal_flag_iv,
1300 p_entry_value1 => p_rec.sec_sal_submit_flag,
1301 p_override_user_ent_chk => 'Y',
1302 p_effective_start_date => l_esd,
1303 p_effective_end_date => l_eed,
1304 p_update_warning => l_warning);
1305 --
1306 p_rec.status := 'U';
1307 --
1308 pay_jp_def_api.update_entry_os(
1309 p_validate => false,
1310 p_action_information_id => p_rec.action_information_id,
1311 p_object_version_number => p_rec.object_version_number,
1312 p_status => p_rec.status,
1313 p_ee_object_version_number => p_rec.ee_object_version_number);
1314 --
1315 end if;
1316 --
1317 end if;
1318 --
1319 hr_utility.set_location('Leaving : ' || c_proc, 20);
1320 --
1321 end transfer_entry_os;
1322 --
1323 -- -------------------------------------------------------------------------
1324 -- rollback_entry_os
1325 -- -------------------------------------------------------------------------
1326 procedure rollback_entry_os(
1327 p_rec in out nocopy pay_jp_def_entry_os_v%rowtype,
1328 p_business_group_id in number)
1329 is
1330 --
1331 c_proc constant varchar2(61) := c_package || '.rollback_entry_os';
1332 --
1333 l_esd date;
1334 l_eed date;
1335 l_warning boolean;
1336 l_vsd date;
1337 l_ved date;
1338 --
1339 begin
1340 --
1341 hr_utility.set_location('Entering : '|| c_proc, 10);
1342 hr_utility.trace('status : '|| p_rec.status);
1343 --
1344 if p_rec.status = 'I' then
1345 --
1346 pay_element_entry_api.delete_element_entry(
1347 p_validate => false,
1348 p_effective_date => p_rec.effective_date,
1349 p_datetrack_delete_mode => 'ZAP',
1350 p_element_entry_id => p_rec.element_entry_id,
1351 p_object_version_number => p_rec.ee_object_version_number,
1352 p_effective_start_date => l_esd,
1353 p_effective_end_date => l_eed,
1354 p_delete_warning => l_warning);
1355 --
1356 p_rec.element_entry_id := null;
1357 p_rec.ee_object_version_number := null;
1358 --
1359 pay_jp_def_api.update_entry_os(
1360 P_VALIDATE => false,
1361 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1362 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1363 P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
1364 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
1365 --
1366 elsif p_rec.status = 'U' then
1367 --
1368 if p_rec.datetrack_update_mode = 'CORRECTION' then
1369 --
1370 pay_element_entry_api.update_element_entry(
1371 p_validate => false,
1372 p_effective_date => p_rec.effective_date,
1373 p_business_group_id => p_business_group_id,
1374 p_datetrack_update_mode => p_rec.datetrack_update_mode,
1375 p_element_entry_id => p_rec.element_entry_id,
1376 p_object_version_number => p_rec.ee_object_version_number,
1377 p_input_value_id1 => c_dep_sec_sal_flag_iv,
1378 p_entry_value1 => p_rec.sec_sal_submit_flag_o,
1379 p_override_user_ent_chk => 'Y',
1380 p_effective_start_date => l_esd,
1381 p_effective_end_date => l_eed,
1382 p_update_warning => l_warning);
1383 --
1384 else
1385 --
1386 -- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
1387 -- should be current OVN - 1. See API for more details.
1388 -- !!!!!
1389 -- This does not work after expired, because OVN of latest record is updated.
1390 -- At first, lock the current record. If locked successfully,
1391 -- derive OVN of previous record.
1392 --
1393 pay_ele_shd.lck(
1394 p_effective_date => p_rec.effective_date,
1395 p_datetrack_mode => 'CORRECTION',
1396 p_element_entry_id => p_rec.element_entry_id,
1397 p_object_version_number => p_rec.ee_object_version_number,
1398 p_validation_start_date => l_vsd,
1399 p_validation_end_date => l_ved);
1400 --
1401 select object_version_number
1402 into p_rec.ee_object_version_number
1403 from pay_element_entries_f
1404 where element_entry_id = p_rec.element_entry_id
1405 and effective_end_date = p_rec.effective_date - 1;
1406 --
1407 pay_element_entry_api.delete_element_entry(
1408 p_validate => false,
1409 p_effective_date => p_rec.effective_date - 1,
1410 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
1411 p_element_entry_id => p_rec.element_entry_id,
1412 p_object_version_number => p_rec.ee_object_version_number,
1413 p_effective_start_date => l_esd,
1414 p_effective_end_date => l_eed,
1415 p_delete_warning => l_warning);
1416 --
1417 end if;
1418 --
1419 p_rec.status := 'Q';
1420 --
1421 pay_jp_def_api.update_entry(
1422 P_VALIDATE => false,
1423 P_ACTION_INFORMATION_ID => p_rec.action_information_id,
1424 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
1425 P_STATUS => p_rec.status,
1426 P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
1427 --
1428 end if;
1429 --
1430 hr_utility.set_location('Leaving : ' || c_proc, 20);
1431 --
1432 end rollback_entry_os;
1433 --
1434 -- |---------------------------------------------------------------------------|
1435 -- |------------------------------< lock_assact >------------------------------|
1436 -- |---------------------------------------------------------------------------|
1437 procedure lock_assact(
1438 p_action_information_id in number,
1439 p_object_version_number in number,
1440 p_business_group_id in out nocopy number,
1441 p_rec out nocopy pay_jp_def_assact_v%rowtype)
1442 is
1443 --
1444 cursor csr_bg
1445 is
1446 select pa.business_group_id
1447 from per_all_assignments_f pa
1448 where pa.assignment_id = p_rec.assignment_id
1449 and p_rec.effective_date
1450 between pa.effective_start_date and pa.effective_end_date;
1451 --
1452 begin
1453 --
1454 select *
1455 into p_rec
1456 from pay_jp_def_assact_v
1457 where action_information_id = p_action_information_id
1458 for update nowait;
1459 --
1460 if p_rec.object_version_number <> p_object_version_number then
1461 fnd_message.set_name('FND', 'FND_RECORD_CHANGED_ERROR');
1462 fnd_message.raise_error;
1463 end if;
1464 --
1465 -- assumption is that lock_assact work for same business group against multiple assignments
1466 -- because archive should processed for one session business group
1467 if ((p_business_group_id is null
1468 or p_business_group_id <> g_business_group_id)
1469 and p_rec.action_information_id is not null) then
1470 --
1471 open csr_bg;
1472 fetch csr_bg into p_business_group_id;
1473 close csr_bg;
1474 --
1475 end if;
1476 --
1477 exception
1478 when hr_api.object_locked then
1479 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
1480 fnd_message.raise_error;
1481 when no_data_found then
1482 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
1483 fnd_message.raise_error;
1484 end lock_assact;
1485 --
1486 -- -------------------------------------------------------------------------
1487 -- archive_default
1488 -- -------------------------------------------------------------------------
1489 procedure archive_default(
1490 p_assignment_action_id in number,
1491 p_assignment_id in number,
1492 p_effective_date in date)
1493 is
1494 --
1495 l_proc varchar2(80) := c_package||'archive_default';
1496 --
1497 l_action_information_id number;
1498 l_object_version_number number;
1499 --
1500 l_disability_type pay_jp_def_emp_v.disability_type%type;
1501 l_aged_type pay_jp_def_emp_v.aged_type%type;
1502 l_widow_type pay_jp_def_emp_v.widow_type%type;
1503 l_working_student_type pay_jp_def_emp_v.working_student_type%type;
1504 --
1505 l_itax_organization_id number;
1506 --
1507 cursor csr_entry
1508 is
1509 select /*+ ORDERED */
1510 pee.element_entry_id,
1511 pee.effective_start_date,
1512 pee.effective_end_date,
1513 pee.object_version_number,
1514 peev.input_value_id,
1515 peev.screen_entry_value
1516 from pay_element_links_f pel,
1517 pay_element_entries_f pee,
1518 pay_element_entry_values_f peev
1519 where pel.element_type_id = c_def_elm
1520 and pel.business_group_id + 0 = g_business_group_id
1521 and p_effective_date
1522 between pel.effective_start_date and pel.effective_end_date
1523 and pee.assignment_id = p_assignment_id
1524 and pee.element_link_id = pel.element_link_id
1525 and p_effective_date
1526 between pee.effective_start_date and pee.effective_end_date
1527 and pee.entry_type = 'E'
1528 and peev.element_entry_id = pee.element_entry_id
1529 and peev.effective_start_date = pee.effective_start_date
1530 and peev.effective_end_date = pee.effective_end_date;
1531 --
1532 cursor csr_entry_os
1533 is
1534 select /*+ ORDERED */
1535 pee.element_entry_id,
1536 pee.effective_start_date,
1537 pee.effective_end_date,
1538 pee.object_version_number,
1539 peev.screen_entry_value
1540 from pay_link_input_values_f pliv,
1541 pay_element_entries_f pee,
1542 pay_element_entry_values_f peev
1543 where pliv.input_value_id = c_dep_sec_sal_flag_iv
1544 and p_effective_date
1545 between pliv.effective_start_date and pliv.effective_end_date
1546 and pee.assignment_id = p_assignment_id
1547 and pee.element_link_id = pliv.element_link_id
1548 and p_effective_date
1549 between pee.effective_start_date and pee.effective_end_date
1550 and pee.entry_type = 'E'
1551 and peev.element_entry_id = pee.element_entry_id
1552 and peev.input_value_id = pliv.input_value_id
1553 and peev.effective_start_date = pee.effective_start_date
1554 and peev.effective_end_date = pee.effective_end_date;
1555 --
1556 cursor csr_emp
1557 is
1558 select /*+ ORDERED */
1559 per.person_id,
1560 per.business_group_id,
1561 per.employee_number employee_number,
1562 per.last_name last_name_kana,
1563 per.first_name first_name_kana,
1564 per.per_information18 last_name,
1565 per.per_information19 first_name,
1566 per.date_of_birth,
1567 per.date_of_death,
1568 per.sex,
1569 decode(adrr.address_id, null, adrc.postal_code, adrr.postal_code) postal_code,
1570 trim(substrb(decode(adrr.address_id, null,
1571 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
1572 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) address,
1573 decode(adrr.address_id,null,adrc.town_or_city,adrr.town_or_city) district_code
1574 from per_all_assignments_f asg,
1575 per_all_people_f per,
1576 per_addresses adrr,
1577 per_addresses adrc
1578 where asg.assignment_id = p_assignment_id
1579 and p_effective_date
1580 between asg.effective_start_date and asg.effective_end_date
1581 and per.person_id = asg.person_id
1582 and p_effective_date
1583 between per.effective_start_date and per.effective_end_date
1584 and adrr.person_id(+) = per.person_id
1585 and adrr.address_type(+) = 'JP_R'
1586 and p_effective_date
1587 between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
1588 and adrc.person_id(+) = per.person_id
1589 and adrc.address_type(+) = 'JP_C'
1590 and p_effective_date
1591 between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date);
1592 --
1593 cursor csr_household_head(p_person_id number)
1594 is
1595 select ctr.contact_relationship_id,
1596 ctr.contact_person_id,
1597 ctr.contact_type
1598 from per_contact_relationships ctr
1599 where ctr.person_id = p_person_id
1600 and ctr.cont_information3 = 'Y'
1601 and p_effective_date
1602 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date);
1603 --
1604 cursor csr_married_flag(p_person_id number)
1605 is
1606 select 'Y' married_flag
1607 from dual
1608 where exists(
1609 select null
1610 from per_contact_relationships ctr
1611 where ctr.person_id = p_person_id
1612 and ctr.contact_type = 'S'
1613 and p_effective_date
1614 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date));
1615 --
1616 cursor csr_dep(p_person_id number)
1617 is
1618 select /*+ ORDERED */
1619 cei.contact_extra_info_id,
1620 cei.effective_start_date,
1621 cei.effective_end_date,
1622 cei.object_version_number,
1623 ctr.contact_relationship_id,
1624 per.last_name LAST_NAME_KANA,
1625 per.first_name FIRST_NAME_KANA,
1626 per.per_information18 LAST_NAME,
1627 per.per_information19 FIRST_NAME,
1628 ctr.contact_type,
1629 per.date_of_birth,
1630 per.date_of_death,
1631 trim(substrb(decode(adrr.address_id, null,
1632 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
1633 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) ADDRESS,
1634 cei.cei_information8 DEP_TYPE,
1635 cei.cei_information2 OCCUPATION,
1636 fnd_number.canonical_to_number(cei.cei_information3) ESTIMATED_ANNUAL_INCOME,
1637 cei.cei_information6 DISABILITY_TYPE,
1638 cei.cei_information7 DISABILITY_DETAILS
1639 from per_contact_relationships ctr,
1640 per_contact_extra_info_f cei,
1641 per_addresses adrr,
1642 per_addresses adrc,
1643 per_all_people_f per
1644 where ctr.person_id = p_person_id
1645 and cei.contact_relationship_id = ctr.contact_relationship_id
1646 and cei.cei_information_category = 'JP_ITAX_DEPENDENT'
1647 and p_effective_date
1648 between cei.effective_start_date and cei.effective_end_date
1649 and adrr.person_id(+) = ctr.contact_person_id
1650 and adrr.address_type(+) = 'JP_R'
1651 and p_effective_date
1652 between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
1653 and adrc.person_id(+) = ctr.contact_person_id
1654 and adrc.address_type(+) = 'JP_C'
1655 and p_effective_date
1656 between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date)
1657 and per.person_id = ctr.contact_person_id
1658 /* CEI guarantees that person record exists as of effective_date */
1659 and p_effective_date
1660 between per.effective_start_date and per.effective_end_date
1661 order by
1662 decode(ctr.contact_type, 'S', 1, 2),
1663 per.date_of_birth,
1664 per.full_name;
1665 --
1666 cursor csr_dep_oe(p_person_id number)
1667 is
1668 select /*+ ORDERED */
1669 cei.contact_extra_info_id,
1670 cei.effective_start_date,
1671 cei.effective_end_date,
1672 cei.object_version_number,
1673 ctr.contact_relationship_id,
1674 per.last_name LAST_NAME_KANA,
1675 per.first_name FIRST_NAME_KANA,
1676 per.per_information18 LAST_NAME,
1677 per.per_information19 FIRST_NAME,
1678 ctr.contact_type,
1679 per.date_of_birth,
1680 per.date_of_death,
1681 trim(substrb(decode(adrr.address_id, null,
1682 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
1683 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) ADDRESS,
1684 cei.cei_information1 OCCUPATION,
1685 -- Do not return contact_relationship_id
1686 fnd_number.canonical_to_number(cei.cei_information5) OE_CONTACT_RELATIONSHIP_ID_O,
1687 ctr2.contact_relationship_id OE_CONTACT_RELATIONSHIP_ID,
1688 ctr2.contact_person_id OE_CONTACT_PERSON_ID,
1689 ctr2.contact_type OE_CONTACT_TYPE,
1690 trim(substrb(decode(adrr2.address_id, null,
1691 adrc2.address_line1 || adrc2.address_line2 || adrc2.address_line3,
1692 adrr2.address_line1 || adrr2.address_line2 || adrr2.address_line3), 1, 240)) OE_ADDRESS
1693 from per_contact_relationships ctr,
1694 per_contact_extra_info_f cei,
1695 per_addresses adrr,
1696 per_addresses adrc,
1697 per_all_people_f per,
1698 per_contact_relationships ctr2,
1699 per_addresses adrr2,
1700 per_addresses adrc2
1701 where ctr.person_id = p_person_id
1702 and cei.contact_relationship_id = ctr.contact_relationship_id
1703 and cei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
1704 and p_effective_date
1705 between cei.effective_start_date and cei.effective_end_date
1706 and adrr.person_id(+) = ctr.contact_person_id
1707 and adrr.address_type(+) = 'JP_R'
1708 and p_effective_date
1709 between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
1710 and adrc.person_id(+) = ctr.contact_person_id
1711 and adrc.address_type(+) = 'JP_C'
1712 and p_effective_date
1713 between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date)
1714 and per.person_id = ctr.contact_person_id
1715 /* CEI guarantees that person record exists as of effective_date */
1716 and p_effective_date
1717 between per.effective_start_date and per.effective_end_date
1718 /* No need to check date range of CTR */
1719 and ctr2.contact_relationship_id(+) = fnd_number.canonical_to_number(cei.cei_information5)
1720 and adrr2.person_id(+) = ctr2.contact_person_id
1721 and adrr2.address_type(+) = 'JP_R'
1722 and p_effective_date
1723 between adrr2.date_from(+) and nvl(adrr2.date_to(+), p_effective_date)
1724 and adrc2.person_id(+) = ctr2.contact_person_id
1725 and adrc2.address_type(+) = 'JP_C'
1726 and p_effective_date
1727 between adrc2.date_from(+) and nvl(adrc2.date_to(+), p_effective_date)
1728 order by
1729 decode(ctr.contact_type, 'S', 1, 2),
1730 per.date_of_birth,
1731 per.full_name;
1732 --
1733 cursor csr_dep_os(p_person_id number)
1734 is
1735 select cei.contact_extra_info_id,
1736 cei.effective_start_date,
1737 cei.effective_end_date,
1738 cei.object_version_number,
1739 ctr.contact_relationship_id,
1740 per.last_name LAST_NAME_KANA,
1741 per.first_name FIRST_NAME_KANA,
1742 per.per_information18 LAST_NAME,
1743 per.per_information19 FIRST_NAME,
1744 ctr.contact_type,
1745 per.date_of_birth,
1746 per.date_of_death,
1747 cei.cei_information1 OCCUPATION,
1748 cei.cei_information2 OS_SALARY_PAYER_NAME,
1749 cei.cei_information3 OS_SALARY_PAYER_ADDRESS
1750 from per_all_people_f per,
1751 per_contact_extra_info_f cei,
1752 per_contact_relationships ctr
1753 where ctr.person_id = p_person_id
1754 and cei.contact_relationship_id = ctr.contact_relationship_id
1755 and cei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
1756 and p_effective_date
1757 between cei.effective_start_date and cei.effective_end_date
1758 and per.person_id = ctr.contact_person_id
1759 /* CEI guarantees that person record exists as of effective_date */
1760 and p_effective_date
1761 between per.effective_start_date and per.effective_end_date
1762 order by
1763 decode(ctr.contact_type, 'S', 1, 2),
1764 per.date_of_birth,
1765 per.full_name;
1766 --
1767 cursor csr_legislation_code
1768 is
1769 select legislation_code
1770 from per_business_groups_perf
1771 where business_group_id = g_business_group_id;
1772 --
1773 cursor csr_emp_address(p_district_code in varchar2)
1774 is
1775 select decode(pjsn_v.rep_district_code,null,pjal_act.address_line_1,pjal_rep.address_line_1) district_name
1776 from per_jp_address_lookups pjal_act,
1777 (select pjsn_act.organization_id,
1778 pjsn_act.district_code act_district_code,
1779 substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) rep_district_code
1780 from pay_jp_swot_numbers pjsn_rep,
1781 pay_jp_swot_numbers pjsn_act
1782 where pjsn_rep.organization_id = pjsn_act.organization_id
1783 and pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)) pjsn_v,
1784 per_jp_address_lookups pjal_rep
1785 where substrb(pjal_act.district_code,1,5) = p_district_code
1786 and pjsn_v.organization_id (+) = nvl(l_itax_organization_id,-1)
1787 and substrb(pjsn_v.act_district_code(+),1,5) = substrb(pjal_act.district_code,1,5)
1788 and substrb(pjal_rep.district_code(+),1,5) = pjsn_v.rep_district_code;
1789 --
1790 l_entry_rec pay_jp_def_entry_v%rowtype;
1791 l_entry_dep_rec pay_jp_def_entry_dep_v%rowtype;
1792 l_entry_os_rec pay_jp_def_entry_os_v%rowtype;
1793 --
1794 l_csr_emp csr_emp%rowtype;
1795 l_csr_household_head csr_household_head%rowtype;
1796 l_csr_married_flag csr_married_flag%rowtype;
1797 l_csr_emp_address csr_emp_address%rowtype;
1798 --
1799 begin
1800 --
1801 if g_debug then
1802 hr_utility.set_location(l_proc,0);
1803 end if;
1804 --
1805 l_action_information_id := null;
1806 l_object_version_number := null;
1807 --
1808 l_entry_rec.status := 'I';
1809 for l_rec in csr_entry loop
1810 --
1811 if csr_entry%rowcount = 1 then
1812 --
1813 l_entry_rec.status := 'Q';
1814 l_entry_rec.datetrack_update_mode := ee_datetrack_update_mode(
1815 l_rec.element_entry_id,
1816 l_rec.effective_start_date,
1817 l_rec.effective_end_date,
1818 p_effective_date);
1819 l_entry_rec.element_entry_id := l_rec.element_entry_id;
1820 l_entry_rec.ee_object_version_number := l_rec.object_version_number;
1821 --
1822 end if;
1823 --
1824 if l_rec.input_value_id = c_disability_type_iv then
1825 --
1826 l_entry_rec.disability_type_o := l_rec.screen_entry_value;
1827 --
1828 elsif l_rec.input_value_id = c_aged_type_iv then
1829 --
1830 l_entry_rec.aged_type_o := l_rec.screen_entry_value;
1831 --
1832 elsif l_rec.input_value_id = c_widow_type_iv then
1833 --
1834 l_entry_rec.widow_type_o := l_rec.screen_entry_value;
1835 --
1836 elsif l_rec.input_value_id = c_working_student_type_iv then
1837 --
1838 l_entry_rec.working_student_type_o := l_rec.screen_entry_value;
1839 --
1840 elsif l_rec.input_value_id = c_spouse_dep_type_iv then
1841 --
1842 l_entry_rec.spouse_dep_type_o := l_rec.screen_entry_value;
1843 --
1844 elsif l_rec.input_value_id = c_spouse_disability_type_iv then
1845 --
1846 l_entry_rec.spouse_disability_type_o := l_rec.screen_entry_value;
1847 --
1848 elsif l_rec.input_value_id = c_num_deps_iv then
1849 --
1850 l_entry_rec.num_deps_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1851 --
1852 elsif l_rec.input_value_id = c_num_ageds_iv then
1853 --
1854 l_entry_rec.num_ageds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1855 --
1856 elsif l_rec.input_value_id = c_num_aged_parents_iv then
1857 --
1858 l_entry_rec.num_aged_parents_lt_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1859 --
1860 elsif l_rec.input_value_id = c_num_specifieds_iv then
1861 --
1862 l_entry_rec.num_specifieds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1863 --
1864 elsif l_rec.input_value_id = c_num_junior_deps_iv then
1865 --
1866 l_entry_dep_rec.num_junior_deps_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1867 --
1868 elsif l_rec.input_value_id = c_num_disableds_iv then
1869 --
1870 l_entry_rec.num_disableds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1871 --
1872 elsif l_rec.input_value_id = c_num_svr_disableds_iv then
1873 --
1874 l_entry_rec.num_svr_disableds_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1875 --
1876 elsif l_rec.input_value_id = c_num_svr_disableds_lt_iv then
1877 --
1878 l_entry_rec.num_svr_disableds_lt_o := fnd_number.canonical_to_number(l_rec.screen_entry_value);
1879 --
1880 end if;
1881 --
1882 end loop;
1883 --
1884 if g_debug then
1885 hr_utility.set_location(l_proc,10);
1886 end if;
1887 --
1888 pay_jp_def_api.create_entry(
1889 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
1890 P_ASSIGNMENT_ID => p_assignment_id,
1891 P_EFFECTIVE_DATE => p_effective_date,
1892 P_STATUS => l_entry_rec.status,
1893 P_DATETRACK_UPDATE_MODE => l_entry_rec.datetrack_update_mode,
1894 P_ELEMENT_ENTRY_ID => l_entry_rec.element_entry_id,
1895 P_EE_OBJECT_VERSION_NUMBER => l_entry_rec.ee_object_version_number,
1896 P_DISABILITY_TYPE => '0',
1897 P_DISABILITY_TYPE_O => l_entry_rec.disability_type_o,
1898 P_AGED_TYPE => '0',
1899 P_AGED_TYPE_O => l_entry_rec.aged_type_o,
1900 P_WIDOW_TYPE => '0',
1901 P_WIDOW_TYPE_O => l_entry_rec.widow_type_o,
1902 P_WORKING_STUDENT_TYPE => '0',
1903 P_WORKING_STUDENT_TYPE_O => l_entry_rec.working_student_type_o,
1904 P_SPOUSE_DEP_TYPE => '0',
1905 P_SPOUSE_DEP_TYPE_O => l_entry_rec.spouse_dep_type_o,
1906 P_SPOUSE_DISABILITY_TYPE => '0',
1907 P_SPOUSE_DISABILITY_TYPE_O => l_entry_rec.spouse_disability_type_o,
1908 P_NUM_DEPS => 0,
1909 P_NUM_DEPS_O => l_entry_rec.num_deps_o,
1910 P_NUM_AGEDS => 0,
1911 P_NUM_AGEDS_O => l_entry_rec.num_ageds_o,
1912 P_NUM_AGED_PARENTS_LT => 0,
1913 P_NUM_AGED_PARENTS_LT_O => l_entry_rec.num_aged_parents_lt_o,
1914 P_NUM_SPECIFIEDS => 0,
1915 P_NUM_SPECIFIEDS_O => l_entry_rec.num_specifieds_o,
1916 P_NUM_DISABLEDS => 0,
1917 P_NUM_DISABLEDS_O => l_entry_rec.num_disableds_o,
1918 P_NUM_SVR_DISABLEDS => 0,
1919 P_NUM_SVR_DISABLEDS_O => l_entry_rec.num_svr_disableds_o,
1920 P_NUM_SVR_DISABLEDS_LT => 0,
1921 P_NUM_SVR_DISABLEDS_LT_O => l_entry_rec.num_svr_disableds_lt_o,
1922 P_ACTION_INFORMATION_ID => l_action_information_id,
1923 P_OBJECT_VERSION_NUMBER => l_object_version_number);
1924 --
1925 if to_number(to_char(p_effective_date,'YYYY')) >= 2011 then
1926 --
1927 if g_debug then
1928 hr_utility.set_location(l_proc,15);
1929 end if;
1930 --
1931 l_action_information_id := null;
1932 l_object_version_number := null;
1933 --
1934 pay_jp_def_api.create_entry_dep(
1935 p_assignment_action_id => p_assignment_action_id,
1936 p_effective_date => p_effective_date,
1937 p_assignment_id => p_assignment_id,
1938 p_num_junior_deps => 0,
1939 p_num_junior_deps_o => l_entry_dep_rec.num_junior_deps_o,
1940 p_action_information_id => l_action_information_id,
1941 p_object_version_number => l_object_version_number);
1942 --
1943 end if;
1944 --
1945 if g_debug then
1946 hr_utility.set_location(l_proc,20);
1947 end if;
1948 --
1949 l_action_information_id := null;
1950 l_object_version_number := null;
1951 --
1952 open csr_emp;
1953 fetch csr_emp into l_csr_emp;
1954 close csr_emp;
1955 --
1956 if g_debug then
1957 hr_utility.set_location(l_proc,30);
1958 hr_utility.trace('person_id : '||to_char(l_csr_emp.person_id));
1959 hr_utility.trace('employee_number : '||l_csr_emp.employee_number);
1960 hr_utility.trace('last_name_kana : '||l_csr_emp.last_name_kana);
1961 hr_utility.trace('first_name_kana : '||l_csr_emp.first_name_kana);
1962 end if;
1963 --
1964 open csr_household_head(l_csr_emp.person_id);
1965 fetch csr_household_head into l_csr_household_head;
1966 close csr_household_head;
1967 --
1968 if g_debug then
1969 hr_utility.set_location(l_proc,40);
1970 hr_utility.trace('contact_relationship_id : '||to_char(l_csr_household_head.contact_relationship_id));
1971 end if;
1972 --
1973 open csr_married_flag(l_csr_emp.person_id);
1974 fetch csr_married_flag into l_csr_married_flag;
1975 close csr_married_flag;
1976 --
1977 if g_debug then
1978 hr_utility.set_location(l_proc,50);
1979 hr_utility.trace('married_flag : '||to_char(l_csr_married_flag.married_flag));
1980 end if;
1981 --
1982 --
1983 -- If widower, change the widow_type from "1" to "3".
1984 --
1985 l_disability_type := nvl(l_entry_rec.disability_type_o, '0');
1986 --
1987 -- Obsolete Aged Exemption from 2005/01/01.
1988 if p_effective_date >= to_date('2005/01/01','YYYY/MM/DD') then
1989 --
1990 l_aged_type := '0';
1991 --
1992 else
1993 --
1994 l_aged_type := nvl(l_entry_rec.aged_type_o, '0');
1995 --
1996 end if;
1997 --
1998 l_widow_type := nvl(l_entry_rec.widow_type_o, '0');
1999 if l_widow_type = '1'
2000 and l_csr_emp.sex = 'M' then
2001 --
2002 l_widow_type := '3';
2003 --
2004 end if;
2005 --
2006 l_working_student_type := nvl(l_entry_rec.working_student_type_o, '0');
2007 --
2008 if g_debug then
2009 hr_utility.set_location(l_proc,60);
2010 hr_utility.trace('l_disability_type : '||l_disability_type);
2011 hr_utility.trace('l_aged_type : '||l_aged_type);
2012 hr_utility.trace('l_widow_type : '||l_widow_type);
2013 hr_utility.trace('l_working_student_type : '||l_working_student_type);
2014 end if;
2015 --
2016 if to_number(to_char(p_effective_date,'YYYY')) >= 2011 then
2017 --
2018 if g_leg_business_group_id is null
2019 or g_leg_business_group_id <> g_business_group_id then
2020 --
2021 open csr_legislation_code;
2022 fetch csr_legislation_code into g_legislation_code;
2023 close csr_legislation_code;
2024 --
2025 g_leg_business_group_id := g_business_group_id;
2026 --
2027 end if;
2028 --
2029 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_effective_date));
2030 pay_balance_pkg.set_context('ASSIGNMENT_ID',fnd_number.number_to_canonical(p_assignment_id));
2031 l_itax_organization_id := pay_balance_pkg.run_db_item(c_org_iv_name,g_leg_business_group_id,g_legislation_code);
2032 --
2033 if g_debug then
2034 hr_utility.set_location(l_proc,70);
2035 end if;
2036 --
2037 open csr_emp_address(l_csr_emp.district_code);
2038 fetch csr_emp_address into l_csr_emp_address;
2039 close csr_emp_address;
2040 --
2041 l_entry_os_rec.status := 'I';
2042 for l_rec in csr_entry_os loop
2043 --
2044 if csr_entry_os%rowcount = 1 then
2045 --
2046 l_entry_os_rec.status := 'Q';
2047 l_entry_os_rec.datetrack_update_mode := ee_datetrack_update_mode(
2048 l_rec.element_entry_id,
2049 l_rec.effective_start_date,
2050 l_rec.effective_end_date,
2051 p_effective_date);
2052 l_entry_os_rec.element_entry_id := l_rec.element_entry_id;
2053 l_entry_os_rec.ee_object_version_number := l_rec.object_version_number;
2054 --
2055 end if;
2056 --
2057 l_entry_os_rec.sec_sal_submit_flag := l_rec.screen_entry_value;
2058 l_entry_os_rec.sec_sal_submit_flag_o := l_rec.screen_entry_value;
2059 --
2060 end loop;
2061 --
2062 if g_debug then
2063 hr_utility.set_location(l_proc,80);
2064 end if;
2065 --
2066 l_action_information_id := null;
2067 l_object_version_number := null;
2068 --
2069 pay_jp_def_api.create_entry_os(
2070 p_assignment_action_id => p_assignment_action_id,
2071 p_effective_date => p_effective_date,
2072 p_assignment_id => p_assignment_id,
2073 p_status => l_entry_os_rec.status,
2074 p_datetrack_update_mode => l_entry_os_rec.datetrack_update_mode,
2075 p_element_entry_id => l_entry_os_rec.element_entry_id,
2076 p_ee_object_version_number => l_entry_os_rec.ee_object_version_number,
2077 p_sec_sal_submit_flag => 'N',
2078 p_sec_sal_submit_flag_o => l_entry_os_rec.sec_sal_submit_flag_o,
2079 p_action_information_id => l_action_information_id,
2080 p_object_version_number => l_object_version_number);
2081 --
2082 if g_debug then
2083 hr_utility.set_location(l_proc,90);
2084 end if;
2085 --
2086 end if;
2087 --
2088 if g_debug then
2089 hr_utility.set_location(l_proc,100);
2090 end if;
2091 --
2092 pay_jp_def_api.create_emp(
2093 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
2094 P_ASSIGNMENT_ID => p_assignment_id,
2095 P_EFFECTIVE_DATE => p_effective_date,
2096 P_LAST_NAME_KANA => l_csr_emp.last_name_kana,
2097 P_FIRST_NAME_KANA => l_csr_emp.first_name_kana,
2098 P_LAST_NAME => l_csr_emp.last_name,
2099 P_FIRST_NAME => l_csr_emp.first_name,
2100 P_DATE_OF_BIRTH => l_csr_emp.date_of_birth,
2101 P_DATE_OF_DEATH => l_csr_emp.date_of_death,
2102 P_SEX => l_csr_emp.sex,
2103 P_POSTAL_CODE => l_csr_emp.postal_code,
2104 P_ADDRESS => l_csr_emp.address,
2105 P_HOUSEHOLD_HEAD_CTR_ID => l_csr_household_head.contact_relationship_id,
2106 P_HOUSEHOLD_HEAD_FULL_NAME => full_name(l_csr_household_head.contact_person_id,p_effective_date),
2107 P_HOUSEHOLD_HEAD_CONTACT_TYPE => l_csr_household_head.contact_type,
2108 P_MARRIED_FLAG => nvl(l_csr_married_flag.married_flag,'N'),
2109 P_CHANGE_DATE => null,
2110 P_CHANGE_REASON => null,
2111 P_DISABILITY_TYPE => l_disability_type,
2112 P_DISABILITY_DETAILS => null,
2113 P_AGED_TYPE => l_aged_type,
2114 P_AGED_DETAILS => null,
2115 P_WIDOW_TYPE => l_widow_type,
2116 P_WIDOW_DETAILS => null,
2117 P_WORKING_STUDENT_TYPE => l_working_student_type,
2118 P_WORKING_STUDENT_DETAILS => null,
2119 p_employee_number => l_csr_emp.employee_number,
2120 p_district_name => l_csr_emp_address.district_name,
2121 p_sec_sal_submit_flag => nvl(l_entry_os_rec.sec_sal_submit_flag,'N'),
2122 P_ACTION_INFORMATION_ID => l_action_information_id,
2123 P_OBJECT_VERSION_NUMBER => l_object_version_number);
2124 --
2125 if g_debug then
2126 hr_utility.set_location(l_proc,110);
2127 end if;
2128 --
2129 for l_rec in csr_dep(l_csr_emp.person_id) loop
2130 --
2131 l_action_information_id := null;
2132 l_object_version_number := null;
2133 --
2134 pay_jp_def_api.create_dep(
2135 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
2136 P_ASSIGNMENT_ID => p_assignment_id,
2137 P_EFFECTIVE_DATE => p_effective_date,
2138 P_STATUS => 'Q',
2139 p_datetrack_update_mode => cei_datetrack_update_mode(
2140 l_rec.contact_extra_info_id,
2141 l_rec.effective_start_date,
2142 l_rec.effective_end_date,
2143 p_effective_date),
2144 p_datetrack_delete_mode => cei_datetrack_delete_mode(
2145 l_rec.contact_extra_info_id,
2146 l_rec.effective_start_date,
2147 l_rec.effective_end_date,
2148 p_effective_date),
2149 p_contact_extra_info_id => l_rec.contact_extra_info_id,
2150 p_cei_object_version_number => l_rec.object_version_number,
2151 p_contact_relationship_id => l_rec.contact_relationship_id,
2152 p_last_name_kana => l_rec.last_name_kana,
2153 p_first_name_kana => l_rec.first_name_kana,
2154 p_last_name => l_rec.last_name,
2155 p_first_name => l_rec.first_name,
2156 p_contact_type => l_rec.contact_type,
2157 p_date_of_birth => l_rec.date_of_birth,
2158 p_date_of_death => l_rec.date_of_death,
2159 p_address => l_rec.address,
2160 p_change_date => null,
2161 p_change_reason => null,
2162 p_dep_type => nvl(l_rec.dep_type, '0'),
2163 p_dep_type_o => l_rec.dep_type,
2164 p_occupation => l_rec.occupation,
2165 p_occupation_o => l_rec.occupation,
2166 p_estimated_annual_income => l_rec.estimated_annual_income,
2167 p_estimated_annual_income_o => l_rec.estimated_annual_income,
2168 p_disability_type => nvl(l_rec.disability_type, '0'),
2169 p_disability_type_o => l_rec.disability_type,
2170 p_disability_details => l_rec.disability_details,
2171 p_disability_details_o => l_rec.disability_details,
2172 P_ACTION_INFORMATION_ID => l_action_information_id,
2173 P_OBJECT_VERSION_NUMBER => l_object_version_number);
2174 --
2175 end loop;
2176 --
2177 if g_debug then
2178 hr_utility.set_location(l_proc,120);
2179 end if;
2180 --
2181 for l_rec in csr_dep_oe(l_csr_emp.person_id) loop
2182 --
2183 l_action_information_id := null;
2184 l_object_version_number := null;
2185 --
2186 pay_jp_def_api.create_dep_oe(
2187 p_assignment_action_id => p_assignment_action_id,
2188 P_ASSIGNMENT_ID => p_assignment_id,
2189 P_EFFECTIVE_DATE => p_effective_date,
2190 P_STATUS => 'Q',
2191 p_datetrack_update_mode => cei_datetrack_update_mode(
2192 l_rec.contact_extra_info_id,
2193 l_rec.effective_start_date,
2194 l_rec.effective_end_date,
2195 p_effective_date),
2196 p_datetrack_delete_mode => cei_datetrack_delete_mode(
2197 l_rec.contact_extra_info_id,
2198 l_rec.effective_start_date,
2199 l_rec.effective_end_date,
2200 p_effective_date),
2201 p_contact_extra_info_id => l_rec.contact_extra_info_id,
2202 p_cei_object_version_number => l_rec.object_version_number,
2203 p_contact_relationship_id => l_rec.contact_relationship_id,
2204 p_last_name_kana => l_rec.last_name_kana,
2205 p_first_name_kana => l_rec.first_name_kana,
2206 p_last_name => l_rec.last_name,
2207 p_first_name => l_rec.first_name,
2208 p_contact_type => l_rec.contact_type,
2209 p_date_of_birth => l_rec.date_of_birth,
2210 p_date_of_death => l_rec.date_of_death,
2211 p_address => l_rec.address,
2212 p_change_date => null,
2213 p_change_reason => null,
2214 p_occupation => l_rec.occupation,
2215 p_occupation_o => l_rec.occupation,
2216 p_oe_contact_relationship_id => l_rec.oe_contact_relationship_id,
2217 p_oe_full_name => full_name(
2218 l_rec.oe_contact_person_id,
2219 p_effective_date),
2220 p_oe_contact_type => l_rec.oe_contact_type,
2221 p_oe_address => l_rec.oe_address,
2222 p_oe_contact_relationship_id_o => l_rec.oe_contact_relationship_id_o,
2223 P_ACTION_INFORMATION_ID => l_action_information_id,
2224 p_object_version_number => l_object_version_number);
2225 --
2226 end loop;
2227 --
2228 if g_debug then
2229 hr_utility.set_location(l_proc,130);
2230 end if;
2231 --
2232 if to_number(to_char(p_effective_date,'YYYY')) < 2011 then
2233 --
2234 if g_debug then
2235 hr_utility.set_location(l_proc,140);
2236 end if;
2237 --
2238 for l_rec in csr_dep_os(l_csr_emp.person_id) loop
2239 --
2240 l_action_information_id := null;
2241 l_object_version_number := null;
2242 --
2243 pay_jp_def_api.create_dep_os(
2244 p_assignment_action_id => p_assignment_action_id,
2245 P_ASSIGNMENT_ID => p_assignment_id,
2246 P_EFFECTIVE_DATE => p_effective_date,
2247 P_STATUS => 'Q',
2248 p_datetrack_update_mode => cei_datetrack_update_mode(
2249 l_rec.contact_extra_info_id,
2250 l_rec.effective_start_date,
2251 l_rec.effective_end_date,
2252 p_effective_date),
2253 p_datetrack_delete_mode => cei_datetrack_delete_mode(
2254 l_rec.contact_extra_info_id,
2255 l_rec.effective_start_date,
2256 l_rec.effective_end_date,
2257 p_effective_date),
2258 p_contact_extra_info_id => l_rec.contact_extra_info_id,
2259 p_cei_object_version_number => l_rec.object_version_number,
2260 p_contact_relationship_id => l_rec.contact_relationship_id,
2261 p_last_name_kana => l_rec.last_name_kana,
2262 p_first_name_kana => l_rec.first_name_kana,
2263 p_last_name => l_rec.last_name,
2264 p_first_name => l_rec.first_name,
2265 p_contact_type => l_rec.contact_type,
2266 p_date_of_birth => l_rec.date_of_birth,
2267 p_date_of_death => l_rec.date_of_death,
2268 p_occupation => l_rec.occupation,
2269 p_occupation_o => l_rec.occupation,
2270 p_os_salary_payer_name => l_rec.os_salary_payer_name,
2271 p_os_salary_payer_name_o => l_rec.os_salary_payer_name,
2272 p_os_salary_payer_address => l_rec.os_salary_payer_address,
2273 p_os_salary_payer_address_o => l_rec.os_salary_payer_address,
2274 P_ACTION_INFORMATION_ID => l_action_information_id,
2275 p_object_version_number => l_object_version_number);
2276 --
2277 end loop;
2278 --
2279 end if;
2280 --
2281 if g_debug then
2282 hr_utility.set_location(l_proc,1000);
2283 end if;
2284 --
2285 end archive_default;
2286 --
2287 -- |---------------------------------------------------------------------------|
2288 -- |--------------------------------< do_init >--------------------------------|
2289 -- |---------------------------------------------------------------------------|
2290 procedure do_init(
2291 p_action_information_id in number,
2292 p_object_version_number in out nocopy number)
2293 is
2294 --
2295 c_proc constant varchar2(61) := c_package || 'do_new';
2296 l_assact_rec pay_jp_def_assact_v%rowtype;
2297 --
2298 begin
2299 --
2300 hr_utility.set_location('Entering : ' || c_proc, 10);
2301 --
2302 lock_assact(p_action_information_id, p_object_version_number, g_business_group_id, l_assact_rec);
2303 --
2304 check_submission_period(p_action_information_id);
2305 --
2306 if l_assact_rec.transaction_status not in ('U', 'N') then
2307 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2308 fnd_message.raise_error;
2309 end if;
2310 --
2311 delete
2312 from pay_action_information
2313 where action_context_id = l_assact_rec.assignment_action_id
2314 and action_context_type = 'AAP'
2315 and action_information_category <> 'JP_DEF_ASSACT';
2316 --
2317 archive_default(l_assact_rec.assignment_action_id,l_assact_rec.assignment_id,l_assact_rec.effective_date);
2318 --
2319 l_assact_rec.transaction_status := 'N';
2320 --
2321 pay_jp_def_api.update_assact(
2322 P_VALIDATE => false,
2323 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2324 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2325 P_TRANSACTION_STATUS => l_assact_rec.transaction_status);
2326 --
2327 p_object_version_number := l_assact_rec.object_version_number;
2328 --
2329 hr_utility.set_location('Leaving : ' || c_proc, 20);
2330 --
2331 end do_init;
2332 --
2333 procedure do_init(
2334 p_action_information_id in number,
2335 p_object_version_number in out nocopy number,
2336 p_return_status out nocopy varchar2)
2337 is
2338 l_proc varchar2(61) := c_package || '.do_init';
2339 begin
2340 savepoint do_init;
2341 --
2342 -- Initialise Multiple Message Detection
2343 --
2344 hr_multi_message.enable_message_list;
2345 --
2346 do_init(p_action_information_id, p_object_version_number);
2347 --
2348 p_return_status := hr_multi_message.get_return_status_disable;
2349 exception
2350 when hr_multi_message.error_message_exist then
2351 rollback to do_init;
2352 p_object_version_number := null;
2353 p_return_status := hr_multi_message.get_return_status_disable;
2354 when others then
2355 rollback to do_init;
2356 if hr_multi_message.unexpected_error_add(l_proc) then
2357 raise;
2358 end if;
2359 p_object_version_number := null;
2360 p_return_status := hr_multi_message.get_return_status_disable;
2361 end do_init;
2362 -- |---------------------------------------------------------------------------|
2363 -- |------------------------------< do_finalize >------------------------------|
2364 -- |---------------------------------------------------------------------------|
2365 procedure do_finalize(
2366 p_action_information_id in number,
2367 p_object_version_number in out nocopy number,
2368 p_user_comments in varchar2)
2369 is
2370 c_proc constant varchar2(61) := c_package || 'do_finalize';
2371 --
2372 l_business_group_id number;
2373 --
2374 l_submission_date date;
2375 l_assact_rec pay_jp_def_assact_v%rowtype;
2376 l_entry_rec pay_jp_def_entry_v%rowtype;
2377 l_entry_dep_rec pay_jp_def_entry_dep_v%rowtype;
2378 l_entry_os_rec pay_jp_def_entry_os_v%rowtype;
2379 l_emp_rec pay_jp_def_emp_v%rowtype;
2380 --
2381 cursor csr_entry(p_assignment_action_id in number)
2382 is
2383 select *
2384 from pay_jp_def_entry_v
2385 where assignment_action_id = p_assignment_action_id
2386 for update nowait;
2387 --
2388 cursor csr_entry_dep(p_assignment_action_id in number)
2389 is
2390 select *
2391 from pay_jp_def_entry_dep_v
2392 where assignment_action_id = p_assignment_action_id
2393 for update nowait;
2394 --
2395 cursor csr_entry_os(p_assignment_action_id in number)
2396 is
2397 select *
2398 from pay_jp_def_entry_os_v
2399 where assignment_action_id = p_assignment_action_id
2400 for update nowait;
2401 --
2402 cursor csr_emp(p_assignment_action_id in number)
2403 is
2404 select *
2405 from pay_jp_def_emp_v
2406 where assignment_action_id = p_assignment_action_id;
2407 --
2408 cursor csr_dep(p_assignment_action_id in number)
2409 is
2410 select *
2411 from pay_jp_def_dep_v
2412 where assignment_action_id = p_assignment_action_id
2413 and status <> 'D'
2414 order by decode(contact_type, 'S', 1, 2), date_of_birth, last_name_kana, first_name_kana;
2415 --
2416 begin
2417 --
2418 hr_utility.set_location('Entering : ' || c_proc, 10);
2419 --
2420 lock_assact(p_action_information_id, p_object_version_number, l_business_group_id, l_assact_rec);
2421 --
2422 l_submission_date := check_submission_period(p_action_information_id);
2423 --
2424 if l_assact_rec.transaction_status <> 'N' then
2425 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2426 fnd_message.raise_error;
2427 end if;
2428 --
2429 -- Synchronize JP_DEF_ENTRY
2430 -- Note all child EOs must be validated before calling this procedure
2431 -- because FND_MSB_PUB does not support "primary key" which means
2432 -- OATableBean multi message functionality is not supported in API.
2433 --
2434 open csr_entry(l_assact_rec.assignment_action_id);
2435 fetch csr_entry into l_entry_rec;
2436 close csr_entry;
2437 --
2438 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
2439 --
2440 open csr_entry_dep(l_assact_rec.assignment_action_id);
2441 fetch csr_entry_dep into l_entry_dep_rec;
2442 close csr_entry_dep;
2443 --
2444 open csr_entry_os(l_assact_rec.assignment_action_id);
2445 fetch csr_entry_os into l_entry_os_rec;
2446 close csr_entry_os;
2447 --
2448 end if;
2449 --
2450 open csr_emp(l_assact_rec.assignment_action_id);
2451 fetch csr_emp into l_emp_rec;
2452 close csr_emp;
2453 --
2454 l_entry_rec.disability_type := l_emp_rec.disability_type;
2455 l_entry_rec.aged_type := l_emp_rec.aged_type;
2456 --
2457 if l_emp_rec.widow_type = '3' then
2458 l_entry_rec.widow_type := '1';
2459 else
2460 l_entry_rec.widow_type := l_emp_rec.widow_type;
2461 end if;
2462 --
2463 l_entry_rec.working_student_type := l_emp_rec.working_student_type;
2464 --
2465 if l_emp_rec.married_flag = 'Y' then
2466 l_entry_rec.spouse_dep_type := '1';
2467 else
2468 l_entry_rec.spouse_dep_type := '0';
2469 end if;
2470 --
2471 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
2472 --
2473 l_entry_os_rec.sec_sal_submit_flag := l_emp_rec.sec_sal_submit_flag;
2474 --
2475 end if;
2476 --
2477 --
2478 -- Initialize new values
2479 --
2480 l_entry_rec.spouse_disability_type := '0';
2481 l_entry_rec.num_deps := 0;
2482 l_entry_rec.num_ageds := 0;
2483 l_entry_rec.num_aged_parents_lt := 0;
2484 l_entry_rec.num_specifieds := 0;
2485 l_entry_dep_rec.num_junior_deps := 0;
2486 l_entry_rec.num_disableds := 0;
2487 l_entry_rec.num_svr_disableds := 0;
2488 l_entry_rec.num_svr_disableds_lt := 0;
2489 --
2490 for l_rec in csr_dep(l_assact_rec.assignment_action_id) loop
2491 --
2492 if l_rec.contact_type = 'S' then
2493 --
2494 if l_rec.dep_type = '0' then
2495 l_entry_rec.spouse_dep_type := '2';
2496 elsif l_rec.dep_type = '20' then
2497 l_entry_rec.spouse_dep_type := '3';
2498 end if;
2499 --
2500 if l_rec.disability_type = '10' then
2501 l_entry_rec.spouse_disability_type := '1';
2502 elsif l_rec.disability_type = '20' then
2503 l_entry_rec.spouse_disability_type := '2';
2504 elsif l_rec.disability_type = '30' then
2505 l_entry_rec.spouse_disability_type := '3';
2506 end if;
2507 --
2508 else
2509 --
2510 -- dep_type 5 will be set after 2011
2511 if nvl(l_rec.dep_type,'0') <> '5' then
2512 l_entry_rec.num_deps := l_entry_rec.num_deps + 1;
2513 else
2514 -- include disabled dependent
2515 l_entry_dep_rec.num_junior_deps := l_entry_dep_rec.num_junior_deps + 1;
2516 end if;
2517 --
2518 -- each dep type has been validated at data entry time on sshr
2519 if l_rec.dep_type = '10' then
2520 l_entry_rec.num_specifieds := l_entry_rec.num_specifieds + 1;
2521 elsif l_rec.dep_type = '20' then
2522 l_entry_rec.num_ageds := l_entry_rec.num_ageds + 1;
2523 elsif l_rec.dep_type = '30' then
2524 l_entry_rec.num_aged_parents_lt := l_entry_rec.num_aged_parents_lt + 1;
2525 end if;
2526 --
2527 if l_rec.disability_type = '10' then
2528 l_entry_rec.num_disableds := l_entry_rec.num_disableds + 1;
2529 elsif l_rec.disability_type = '20' then
2530 l_entry_rec.num_svr_disableds := l_entry_rec.num_svr_disableds + 1;
2531 elsif l_rec.disability_type = '30' then
2532 l_entry_rec.num_svr_disableds_lt := l_entry_rec.num_svr_disableds_lt + 1;
2533 end if;
2534 --
2535 end if;
2536 --
2537 end loop;
2538 --
2539 pay_jp_def_api.update_entry(
2540 P_VALIDATE => false,
2541 P_ACTION_INFORMATION_ID => l_entry_rec.action_information_id,
2542 P_OBJECT_VERSION_NUMBER => l_entry_rec.object_version_number,
2543 P_DISABILITY_TYPE => l_entry_rec.disability_type,
2544 P_AGED_TYPE => l_entry_rec.aged_type,
2545 P_WIDOW_TYPE => l_entry_rec.widow_type,
2546 P_WORKING_STUDENT_TYPE => l_entry_rec.working_student_type,
2547 P_SPOUSE_DEP_TYPE => l_entry_rec.spouse_dep_type,
2548 P_SPOUSE_DISABILITY_TYPE => l_entry_rec.spouse_disability_type,
2549 P_NUM_DEPS => l_entry_rec.num_deps,
2550 P_NUM_AGEDS => l_entry_rec.num_ageds,
2551 P_NUM_AGED_PARENTS_LT => l_entry_rec.num_aged_parents_lt,
2552 P_NUM_SPECIFIEDS => l_entry_rec.num_specifieds,
2553 P_NUM_DISABLEDS => l_entry_rec.num_disableds,
2554 P_NUM_SVR_DISABLEDS => l_entry_rec.num_svr_disableds,
2555 P_NUM_SVR_DISABLEDS_LT => l_entry_rec.num_svr_disableds_lt);
2556 --
2557 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
2558 --
2559 pay_jp_def_api.update_entry_dep(
2560 p_validate => false,
2561 p_action_information_id => l_entry_dep_rec.action_information_id,
2562 p_object_version_number => l_entry_dep_rec.object_version_number,
2563 p_num_junior_deps => l_entry_dep_rec.num_junior_deps);
2564 --
2565 pay_jp_def_api.update_entry_os(
2566 p_validate => false,
2567 p_action_information_id => l_entry_os_rec.action_information_id,
2568 p_object_version_number => l_entry_os_rec.object_version_number,
2569 p_sec_sal_submit_flag => l_entry_os_rec.sec_sal_submit_flag);
2570 --
2571 end if;
2572 --
2573 l_assact_rec.transaction_status := 'F';
2574 l_assact_rec.finalized_date := l_submission_date;
2575 l_assact_rec.finalized_by := fnd_global.user_id;
2576 --
2577 pay_jp_def_api.update_assact(
2578 P_VALIDATE => false,
2579 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2580 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2581 P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
2582 P_FINALIZED_DATE => l_assact_rec.finalized_date,
2583 P_FINALIZED_BY => l_assact_rec.finalized_by,
2584 P_USER_COMMENTS => p_user_comments);
2585 --
2586 p_object_version_number := l_assact_rec.object_version_number;
2587 --
2588 hr_utility.set_location('Leaving : ' || c_proc, 20);
2589 --
2590 end do_finalize;
2591 --
2592 procedure do_finalize(
2593 p_action_information_id in number,
2594 p_object_version_number in out nocopy number,
2595 p_user_comments in varchar2,
2596 p_return_status out nocopy varchar2)
2597 is
2598 l_proc varchar2(61) := c_package || '.do_finalize';
2599 begin
2600 savepoint do_finalize;
2601 --
2602 -- Initialise Multiple Message Detection
2603 --
2604 hr_multi_message.enable_message_list;
2605 --
2606 do_finalize(p_action_information_id, p_object_version_number, p_user_comments);
2607 --
2608 p_return_status := hr_multi_message.get_return_status_disable;
2609 exception
2610 when hr_multi_message.error_message_exist then
2611 rollback to do_finalize;
2612 p_object_version_number := null;
2613 p_return_status := hr_multi_message.get_return_status_disable;
2614 when others then
2615 rollback to do_finalize;
2616 if hr_multi_message.unexpected_error_add(l_proc) then
2617 raise;
2618 end if;
2619 p_object_version_number := null;
2620 p_return_status := hr_multi_message.get_return_status_disable;
2621 end do_finalize;
2622 -- |---------------------------------------------------------------------------|
2623 -- |-------------------------------< do_reject >-------------------------------|
2624 -- |---------------------------------------------------------------------------|
2625 procedure do_reject(
2626 p_action_information_id in number,
2627 p_object_version_number in out nocopy number,
2628 p_admin_comments in varchar2)
2629 is
2630 c_proc constant varchar2(61) := c_package || 'do_reject';
2631 --
2632 l_business_group_id number;
2633 --
2634 l_assact_rec pay_jp_def_assact_v%rowtype;
2635 begin
2636 hr_utility.set_location('Entering : ' || c_proc, 10);
2637 --
2638 lock_assact(p_action_information_id, p_object_version_number, l_business_group_id, l_assact_rec);
2639 --
2640 check_submission_period(p_action_information_id);
2641 --
2642 if l_assact_rec.transaction_status not in ('F', 'A') then
2643 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2644 fnd_message.raise_error;
2645 elsif l_assact_rec.transfer_status <> 'U' then
2646 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
2647 fnd_message.raise_error;
2648 end if;
2649 --
2650 delete
2651 from pay_action_information
2652 where action_context_id = l_assact_rec.assignment_action_id
2653 and action_context_type = 'AAP'
2654 and action_information_category <> 'JP_DEF_ASSACT';
2655 --
2656 l_assact_rec.transaction_status := 'U';
2657 l_assact_rec.finalized_date := null;
2658 l_assact_rec.finalized_by := null;
2659 --
2660 pay_jp_def_api.update_assact(
2661 P_VALIDATE => false,
2662 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2663 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2664 P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
2665 P_FINALIZED_DATE => l_assact_rec.finalized_date,
2666 P_FINALIZED_BY => l_assact_rec.finalized_by,
2667 P_ADMIN_COMMENTS => p_admin_comments);
2668 --
2669 p_object_version_number := l_assact_rec.object_version_number;
2670 --
2671 hr_utility.set_location('Leaving : ' || c_proc, 20);
2672 end do_reject;
2673 --
2674 procedure do_reject(
2675 p_action_information_id in number,
2676 p_object_version_number in out nocopy number,
2677 p_admin_comments in varchar2,
2678 p_return_status out nocopy varchar2)
2679 is
2680 l_proc varchar2(61) := c_package || '.do_reject';
2681 begin
2682 savepoint do_reject;
2683 --
2684 -- Initialise Multiple Message Detection
2685 --
2686 hr_multi_message.enable_message_list;
2687 --
2688 do_reject(p_action_information_id, p_object_version_number, p_admin_comments);
2689 --
2690 p_return_status := hr_multi_message.get_return_status_disable;
2691 exception
2692 when hr_multi_message.error_message_exist then
2693 rollback to do_reject;
2694 p_object_version_number := null;
2695 p_return_status := hr_multi_message.get_return_status_disable;
2696 when others then
2697 rollback to do_reject;
2698 if hr_multi_message.unexpected_error_add(l_proc) then
2699 raise;
2700 end if;
2701 p_object_version_number := null;
2702 p_return_status := hr_multi_message.get_return_status_disable;
2703 end do_reject;
2704 -- |---------------------------------------------------------------------------|
2705 -- |-------------------------------< do_return >-------------------------------|
2706 -- |---------------------------------------------------------------------------|
2707 procedure do_return(
2708 p_action_information_id in number,
2709 p_object_version_number in out nocopy number,
2710 p_admin_comments in varchar2)
2711 is
2712 c_proc constant varchar2(61) := c_package || 'do_return';
2713 --
2714 l_business_group_id number;
2715 --
2716 l_assact_rec pay_jp_def_assact_v%rowtype;
2717 begin
2718 hr_utility.set_location('Entering : ' || c_proc, 10);
2719 --
2720 lock_assact(p_action_information_id, p_object_version_number, l_business_group_id, l_assact_rec);
2721 --
2722 check_submission_period(p_action_information_id);
2723 --
2724 if l_assact_rec.transaction_status not in ('F', 'A') then
2725 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2726 fnd_message.raise_error;
2727 elsif l_assact_rec.transfer_status <> 'U' then
2728 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
2729 fnd_message.raise_error;
2730 end if;
2731 --
2732 l_assact_rec.transaction_status := 'N';
2733 l_assact_rec.finalized_date := null;
2734 l_assact_rec.finalized_by := null;
2735 --
2736 pay_jp_def_api.update_assact(
2737 P_VALIDATE => false,
2738 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2739 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2740 P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
2741 P_FINALIZED_DATE => l_assact_rec.finalized_date,
2742 P_FINALIZED_BY => l_assact_rec.finalized_by,
2743 P_ADMIN_COMMENTS => p_admin_comments);
2744 --
2745 p_object_version_number := l_assact_rec.object_version_number;
2746 --
2747 hr_utility.set_location('Leaving : ' || c_proc, 20);
2748 end do_return;
2749 --
2750 procedure do_return(
2751 p_action_information_id in number,
2752 p_object_version_number in out nocopy number,
2753 p_admin_comments in varchar2,
2754 p_return_status out nocopy varchar2)
2755 is
2756 l_proc varchar2(61) := c_package || '.do_return';
2757 begin
2758 savepoint do_return;
2759 --
2760 -- Initialise Multiple Message Detection
2761 --
2762 hr_multi_message.enable_message_list;
2763 --
2764 do_return(p_action_information_id, p_object_version_number, p_admin_comments);
2765 --
2766 p_return_status := hr_multi_message.get_return_status_disable;
2767 exception
2768 when hr_multi_message.error_message_exist then
2769 rollback to do_return;
2770 p_object_version_number := null;
2771 p_return_status := hr_multi_message.get_return_status_disable;
2772 when others then
2773 rollback to do_return;
2774 if hr_multi_message.unexpected_error_add(l_proc) then
2775 raise;
2776 end if;
2777 p_object_version_number := null;
2778 p_return_status := hr_multi_message.get_return_status_disable;
2779 end do_return;
2780 -- |---------------------------------------------------------------------------|
2781 -- |------------------------------< do_approve >-------------------------------|
2782 -- |---------------------------------------------------------------------------|
2783 procedure do_approve(
2784 p_action_information_id in number,
2785 p_object_version_number in out nocopy number)
2786 is
2787 c_proc constant varchar2(61) := c_package || 'do_approve';
2788 --
2789 l_business_group_id number;
2790 --
2791 l_assact_rec pay_jp_def_assact_v%rowtype;
2792 begin
2793 hr_utility.set_location('Entering : ' || c_proc, 10);
2794 --
2795 lock_assact(p_action_information_id, p_object_version_number, l_business_group_id, l_assact_rec);
2796 --
2797 if l_assact_rec.transaction_status <> 'F' then
2798 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
2799 fnd_message.raise_error;
2800 end if;
2801 --
2802 l_assact_rec.transaction_status := 'A';
2803 --
2804 pay_jp_def_api.update_assact(
2805 P_VALIDATE => false,
2806 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
2807 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
2808 P_TRANSACTION_STATUS => l_assact_rec.transaction_status);
2809 --
2810 p_object_version_number := l_assact_rec.object_version_number;
2811 --
2812 hr_utility.set_location('Leaving : ' || c_proc, 20);
2813 end do_approve;
2814 --
2815 procedure do_approve(
2816 p_action_information_id in number,
2817 p_object_version_number in out nocopy number,
2818 p_return_status out nocopy varchar2)
2819 is
2820 l_proc varchar2(61) := c_package || '.do_approve';
2821 begin
2822 savepoint do_approve;
2823 --
2824 -- Initialise Multiple Message Detection
2825 --
2826 hr_multi_message.enable_message_list;
2827 --
2828 do_approve(p_action_information_id, p_object_version_number);
2829 --
2830 p_return_status := hr_multi_message.get_return_status_disable;
2831 exception
2832 when hr_multi_message.error_message_exist then
2833 rollback to do_approve;
2834 p_object_version_number := null;
2835 p_return_status := hr_multi_message.get_return_status_disable;
2836 when others then
2837 rollback to do_approve;
2838 if hr_multi_message.unexpected_error_add(l_proc) then
2839 raise;
2840 end if;
2841 p_object_version_number := null;
2842 p_return_status := hr_multi_message.get_return_status_disable;
2843 end do_approve;
2844 -- |---------------------------------------------------------------------------|
2845 -- |------------------------------< do_approve >-------------------------------|
2846 -- |---------------------------------------------------------------------------|
2847 procedure do_approve(
2848 errbuf out nocopy varchar2,
2849 retcode out nocopy varchar2,
2850 p_payroll_action_id in varchar2)
2851 is
2852 l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
2853 cursor csr_assact is
2854 select aif.action_information_id,
2855 aif.object_version_number
2856 from pay_jp_def_assact_v aif,
2857 pay_assignment_actions paa
2858 where paa.payroll_action_id = l_payroll_action_id
2859 and paa.action_status = 'C'
2860 and aif.assignment_action_id = paa.assignment_action_id
2861 and transaction_status = 'F';
2862 begin
2863 --
2864 -- retcode
2865 -- 0 : Success
2866 -- 1 : Warning
2867 -- 2 : Error
2868 --
2869 retcode := '0';
2870 --
2871 for l_rec in csr_assact loop
2872 begin
2873 do_approve(
2874 p_action_information_id => l_rec.action_information_id,
2875 p_object_version_number => l_rec.object_version_number);
2876 commit;
2877 exception
2878 when others then
2879 retcode := '1';
2880 end;
2881 end loop;
2882 end do_approve;
2883 -- |---------------------------------------------------------------------------|
2884 -- |------------------------------< do_transfer >------------------------------|
2885 -- |---------------------------------------------------------------------------|
2886 procedure do_transfer(
2887 p_action_information_id in number,
2888 p_object_version_number in out nocopy number,
2889 p_create_session in boolean default true)
2890 is
2891 --
2892 c_proc constant varchar2(61) := c_package || 'do_transfer';
2893 --
2894 l_assact_rec pay_jp_def_assact_v%rowtype;
2895 l_entry_rec pay_jp_def_entry_v%rowtype;
2896 l_entry_dep_rec pay_jp_def_entry_dep_v%rowtype;
2897 l_dep_rec pay_jp_def_dep_v%rowtype;
2898 l_dep_oe_rec pay_jp_def_dep_oe_v%rowtype;
2899 l_dep_os_rec pay_jp_def_dep_os_v%rowtype;
2900 --
2901 cursor csr_entry(p_assignment_action_id number)
2902 is
2903 select *
2904 from pay_jp_def_entry_v
2905 where assignment_action_id = p_assignment_action_id
2906 for update nowait;
2907 --
2908 cursor csr_entry_dep(p_assignment_action_id number)
2909 is
2910 select *
2911 from pay_jp_def_entry_dep_v
2912 where assignment_action_id = p_assignment_action_id
2913 for update nowait;
2914 --
2915 cursor csr_entry_os(p_assignment_action_id number)
2916 is
2917 select *
2918 from pay_jp_def_entry_os_v
2919 where assignment_action_id = p_assignment_action_id
2920 for update nowait;
2921 --
2922 --
2923 -- JP_DEF_DEP
2924 --
2925 cursor csr_dep_del(p_assignment_action_id number)
2926 is
2927 select *
2928 from pay_jp_def_dep_v
2929 where assignment_action_id = p_assignment_action_id
2930 and status = 'D'
2931 for update nowait;
2932 --
2933 cursor csr_dep_upd(p_assignment_action_id number)
2934 is
2935 select *
2936 from pay_jp_def_dep_v
2937 where assignment_action_id = p_assignment_action_id
2938 and status = 'Q'
2939 for update nowait;
2940 --
2941 cursor csr_dep_ins(p_assignment_action_id number)
2942 is
2943 select *
2944 from pay_jp_def_dep_v
2945 where assignment_action_id = p_assignment_action_id
2946 and status = 'I'
2947 for update nowait;
2948 --
2949 --
2950 -- JP_DEF_DEP_OE
2951 --
2952 cursor csr_dep_oe_del(p_assignment_action_id number)
2953 is
2954 select *
2955 from pay_jp_def_dep_oe_v
2956 where assignment_action_id = p_assignment_action_id
2957 and status = 'D'
2958 for update nowait;
2959 --
2960 cursor csr_dep_oe_upd(p_assignment_action_id number)
2961 is
2962 select *
2963 from pay_jp_def_dep_oe_v
2964 where assignment_action_id = p_assignment_action_id
2965 and status = 'Q'
2966 for update nowait;
2967 --
2968 cursor csr_dep_oe_ins(p_assignment_action_id number)
2969 is
2970 select *
2971 from pay_jp_def_dep_oe_v
2972 where assignment_action_id = p_assignment_action_id
2973 and status = 'I'
2974 for update nowait;
2975 --
2976 --
2977 -- JP_DEF_DEP_OS
2978 --
2979 cursor csr_dep_os_del(p_assignment_action_id number)
2980 is
2981 select *
2982 from pay_jp_def_dep_os_v
2983 where assignment_action_id = p_assignment_action_id
2984 and status = 'D'
2985 for update nowait;
2986 --
2987 cursor csr_dep_os_upd(p_assignment_action_id number)
2988 is
2989 select *
2990 from pay_jp_def_dep_os_v
2991 where assignment_action_id = p_assignment_action_id
2992 and status = 'Q'
2993 for update nowait;
2994 --
2995 cursor csr_dep_os_ins(p_assignment_action_id number)
2996 is
2997 select *
2998 from pay_jp_def_dep_os_v
2999 where assignment_action_id = p_assignment_action_id
3000 and status = 'I'
3001 for update nowait;
3002 --
3003 begin
3004 --
3005 hr_utility.set_location('Entering : ' || c_proc, 10);
3006 --
3007 if p_create_session then
3008 g_business_group_id := null;
3009 end if;
3010 --
3011 lock_assact(p_action_information_id, p_object_version_number, g_business_group_id, l_assact_rec);
3012 --
3013 if l_assact_rec.transaction_status <> 'A' then
3014 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
3015 fnd_message.raise_error;
3016 elsif l_assact_rec.transfer_status <> 'U' then
3017 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
3018 fnd_message.raise_error;
3019 end if;
3020 --
3021 if p_create_session then
3022 insert_session(l_assact_rec.effective_date);
3023 end if;
3024 --
3025 --
3026 -- Transfer JP_DEF_ENTRY, JP_DEF_ENTRY_DEP, JP_DEF_ENTRY_OS to PAY_ELEMENT_ENTRIES_F
3027 --
3028 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
3029 --
3030 -- assumption, csr_entry_dep (and csr_entry) always return 1 record for assact
3031 -- because element entry is single for one assignment
3032 open csr_entry_dep(l_assact_rec.assignment_action_id);
3033 fetch csr_entry_dep into l_entry_dep_rec;
3034 close csr_entry_dep;
3035 --
3036 for l_rec in csr_entry_os(l_assact_rec.assignment_action_id) loop
3037 --
3038 transfer_entry_os(l_rec,g_business_group_id);
3039 --
3040 end loop;
3041 --
3042 end if;
3043 --
3044 -- l_entry_dep_rec is set after 2011
3045 for l_rec in csr_entry(l_assact_rec.assignment_action_id) loop
3046 transfer_entry(l_rec,l_entry_dep_rec,g_business_group_id);
3047 end loop;
3048 --
3049 --
3050 -- Transfer the followings.
3051 --
3052 -- JP_DEF_DEP --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP
3053 -- JP_DEF_DEP_OE --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OE
3054 -- JP_DEF_DEP_OS --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OS
3055 --
3056 -- The transaction sequence into PER_CONTACT_EXTRA_INFO_F must be
3057 -- at first "Delele", "Update" and at last "Insert" to avoid API errors.
3058 --
3059 -- "Delete" phase
3060 --
3061 for l_rec in csr_dep_del(l_assact_rec.assignment_action_id) loop
3062 transfer_dep(l_rec);
3063 end loop;
3064 --
3065 for l_rec in csr_dep_oe_del(l_assact_rec.assignment_action_id) loop
3066 transfer_dep_oe(l_rec);
3067 end loop;
3068 --
3069 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3070 --
3071 for l_rec in csr_dep_os_del(l_assact_rec.assignment_action_id) loop
3072 transfer_dep_os(l_rec);
3073 end loop;
3074 --
3075 end if;
3076 --
3077 --
3078 -- "Update" phase
3079 --
3080 for l_rec in csr_dep_upd(l_assact_rec.assignment_action_id) loop
3081 transfer_dep(l_rec);
3082 end loop;
3083 --
3084 for l_rec in csr_dep_oe_upd(l_assact_rec.assignment_action_id) loop
3085 transfer_dep_oe(l_rec);
3086 end loop;
3087 --
3088 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3089 --
3090 for l_rec in csr_dep_os_upd(l_assact_rec.assignment_action_id) loop
3091 transfer_dep_os(l_rec);
3092 end loop;
3093 --
3094 end if;
3095 --
3096 --
3097 -- "Insert" phase
3098 --
3099 for l_rec in csr_dep_ins(l_assact_rec.assignment_action_id) loop
3100 transfer_dep(l_rec);
3101 end loop;
3102 --
3103 for l_rec in csr_dep_oe_ins(l_assact_rec.assignment_action_id) loop
3104 transfer_dep_oe(l_rec);
3105 end loop;
3106 --
3107 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3108 --
3109 for l_rec in csr_dep_os_ins(l_assact_rec.assignment_action_id) loop
3110 transfer_dep_os(l_rec);
3111 end loop;
3112 --
3113 end if;
3114 --
3115 if p_create_session then
3116 delete_session;
3117 end if;
3118 --
3119 l_assact_rec.transfer_status := 'T';
3120 --
3121 pay_jp_def_api.update_assact(
3122 P_VALIDATE => false,
3123 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
3124 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
3125 P_TRANSFER_STATUS => l_assact_rec.transfer_status);
3126 --
3127 p_object_version_number := l_assact_rec.object_version_number;
3128 --
3129 hr_utility.set_location('Leaving : ' || c_proc, 20);
3130 --
3131 end do_transfer;
3132 --
3133 procedure do_transfer(
3134 p_action_information_id in number,
3135 p_object_version_number in out nocopy number,
3136 p_return_status out nocopy varchar2)
3137 is
3138 l_proc varchar2(61) := c_package || '.do_transfer';
3139 begin
3140 savepoint do_transfer;
3141 --
3142 -- Initialise Multiple Message Detection
3143 --
3144 hr_multi_message.enable_message_list;
3145 --
3146 do_transfer(p_action_information_id, p_object_version_number);
3147 --
3148 p_return_status := hr_multi_message.get_return_status_disable;
3149 exception
3150 when hr_multi_message.error_message_exist then
3151 rollback to do_transfer;
3152 p_object_version_number := null;
3153 p_return_status := hr_multi_message.get_return_status_disable;
3154 when others then
3155 rollback to do_transfer;
3156 if hr_multi_message.unexpected_error_add(l_proc) then
3157 raise;
3158 end if;
3159 p_object_version_number := null;
3160 p_return_status := hr_multi_message.get_return_status_disable;
3161 end do_transfer;
3162 -- |---------------------------------------------------------------------------|
3163 -- |------------------------------< do_transfer >------------------------------|
3164 -- |---------------------------------------------------------------------------|
3165 procedure do_transfer(
3166 errbuf out nocopy varchar2,
3167 retcode out nocopy varchar2,
3168 p_payroll_action_id in varchar2)
3169 is
3170 l_effective_date date;
3171 l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
3172 --
3173 cursor csr_assact is
3174 select assact.action_information_id,
3175 assact.object_version_number,
3176 per.full_name,
3177 asg.assignment_number
3178 from per_all_people_f per,
3179 per_all_assignments_f asg,
3180 pay_jp_def_assact_v assact,
3181 pay_assignment_actions paa
3182 where paa.payroll_action_id = l_payroll_action_id
3183 and paa.action_status = 'C'
3184 and assact.assignment_action_id = paa.assignment_action_id
3185 and assact.transaction_status = 'A'
3186 and assact.transfer_status = 'U'
3187 and asg.assignment_id = assact.assignment_id
3188 and assact.effective_date
3189 between asg.effective_start_date and asg.effective_end_date
3190 and per.person_id = asg.person_id
3191 and assact.effective_date
3192 between per.effective_start_date and per.effective_end_date;
3193 begin
3194 --
3195 -- retcode
3196 -- 0 : Success
3197 -- 1 : Warning
3198 -- 2 : Error
3199 --
3200 retcode := '0';
3201 --
3202 --
3203 g_business_group_id := null;
3204 --
3205 select effective_date
3206 into l_effective_date
3207 from pay_jp_def_pact_v
3208 where payroll_action_id = l_payroll_action_id;
3209 --
3210 insert_session(l_effective_date);
3211 commit;
3212 --
3213 fnd_file.put_line(fnd_file.output, 'Full Name Assignment Number');
3214 fnd_file.put_line(fnd_file.output, '---------------------------------------- ------------------------------');
3215 fnd_file.put_line(fnd_file.log, 'Full Name Assignment Number');
3216 fnd_file.put_line(fnd_file.log, '---------------------------------------- ------------------------------');
3217 --
3218 for l_rec in csr_assact loop
3219 begin
3220 do_transfer(
3221 p_action_information_id => l_rec.action_information_id,
3222 p_object_version_number => l_rec.object_version_number,
3223 p_create_session => false);
3224 commit;
3225 --
3226 fnd_file.put_line(fnd_file.output, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
3227 exception
3228 when others then
3229 retcode := '1';
3230 fnd_file.put_line(fnd_file.log, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
3231 fnd_file.put_line(fnd_file.log, get_sqlerrm);
3232 end;
3233 end loop;
3234 --
3235 delete_session;
3236 commit;
3237 end do_transfer;
3238 -- |---------------------------------------------------------------------------|
3239 -- |---------------------------< rollback_transfer >---------------------------|
3240 -- |---------------------------------------------------------------------------|
3241 procedure rollback_transfer(
3242 p_action_information_id in number,
3243 p_object_version_number in out nocopy number,
3244 p_create_session in boolean default true)
3245 is
3246 --
3247 c_proc constant varchar2(61) := c_package ||'rollback_transfer';
3248 --
3249 l_assact_rec pay_jp_def_assact_v%rowtype;
3250 l_entry_rec pay_jp_def_entry_v%rowtype;
3251 l_entry_dep_rec pay_jp_def_entry_dep_v%rowtype;
3252 l_entry_os_rec pay_jp_def_entry_os_v%rowtype;
3253 l_dep_rec pay_jp_def_dep_v%rowtype;
3254 l_dep_oe_rec pay_jp_def_dep_oe_v%rowtype;
3255 l_dep_os_rec pay_jp_def_dep_os_v%rowtype;
3256 --
3257 cursor csr_entry(p_assignment_action_id number)
3258 is
3259 select *
3260 from pay_jp_def_entry_v
3261 where assignment_action_id = p_assignment_action_id
3262 for update nowait;
3263 --
3264 cursor csr_entry_dep(p_assignment_action_id number)
3265 is
3266 select *
3267 from pay_jp_def_entry_dep_v
3268 where assignment_action_id = p_assignment_action_id
3269 for update nowait;
3270 --
3271 cursor csr_entry_os(p_assignment_action_id number)
3272 is
3273 select *
3274 from pay_jp_def_entry_os_v
3275 where assignment_action_id = p_assignment_action_id
3276 for update nowait;
3277 --
3278 --
3279 -- JP_DEF_DEP
3280 --
3281 cursor csr_dep_ins(p_assignment_action_id number)
3282 is
3283 select *
3284 from pay_jp_def_dep_v
3285 where assignment_action_id = p_assignment_action_id
3286 and status = 'I'
3287 for update nowait;
3288 --
3289 cursor csr_dep_upd(p_assignment_action_id number)
3290 is
3291 select *
3292 from pay_jp_def_dep_v
3293 where assignment_action_id = p_assignment_action_id
3294 and status = 'U'
3295 for update nowait;
3296 --
3297 cursor csr_dep_del(p_assignment_action_id number)
3298 is
3299 select *
3300 from pay_jp_def_dep_v
3301 where assignment_action_id = p_assignment_action_id
3302 and status = 'D'
3303 for update nowait;
3304 --
3305 --
3306 -- JP_DEF_DEP_OE
3307 --
3308 cursor csr_dep_oe_ins(p_assignment_action_id number)
3309 is
3310 select *
3311 from pay_jp_def_dep_oe_v
3312 where assignment_action_id = p_assignment_action_id
3313 and status = 'I'
3314 for update nowait;
3315 --
3316 cursor csr_dep_oe_upd(p_assignment_action_id number)
3317 is
3318 select *
3319 from pay_jp_def_dep_oe_v
3320 where assignment_action_id = p_assignment_action_id
3321 and status = 'U'
3322 for update nowait;
3323 --
3324 cursor csr_dep_oe_del(p_assignment_action_id number)
3325 is
3326 select *
3327 from pay_jp_def_dep_oe_v
3328 where assignment_action_id = p_assignment_action_id
3329 and status = 'D'
3330 for update nowait;
3331 --
3332 --
3333 -- JP_DEF_DEP_OS
3334 --
3335 cursor csr_dep_os_ins(p_assignment_action_id number)
3336 is
3337 select *
3338 from pay_jp_def_dep_os_v
3339 where assignment_action_id = p_assignment_action_id
3340 and status = 'I'
3341 for update nowait;
3342 --
3343 cursor csr_dep_os_upd(p_assignment_action_id number)
3344 is
3345 select *
3346 from pay_jp_def_dep_os_v
3347 where assignment_action_id = p_assignment_action_id
3348 and status = 'U'
3349 for update nowait;
3350 --
3351 cursor csr_dep_os_del(p_assignment_action_id number)
3352 is
3353 select *
3354 from pay_jp_def_dep_os_v
3355 where assignment_action_id = p_assignment_action_id
3356 and status = 'D'
3357 for update nowait;
3358 --
3359 begin
3360 --
3361 hr_utility.set_location('Entering : ' || c_proc, 10);
3362 --
3363 if p_create_session then
3364 g_business_group_id := null;
3365 end if;
3366 --
3367 lock_assact(p_action_information_id, p_object_version_number, g_business_group_id, l_assact_rec);
3368 --
3369 if l_assact_rec.transfer_status = 'U' then
3370 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_TRANSFERRED');
3371 fnd_message.raise_error;
3372 elsif l_assact_rec.transfer_status = 'E' then
3373 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_EXPIRED');
3374 fnd_message.raise_error;
3375 end if;
3376 --
3377 if p_create_session then
3378 insert_session(l_assact_rec.effective_date);
3379 end if;
3380 --
3381 --
3382 -- Rollback Transfer JP_DEF_ENTRY, JP_DEF_ENTRY_DEP, JP_DEF_ENTRY_OS to PAY_ELEMENT_ENTRIES_F
3383 --
3384 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
3385 --
3386 -- assumption, csr_entry_dep (and csr_entry) always return 1 record for assact
3387 -- because element entry is single for one assignment
3388 open csr_entry_dep(l_assact_rec.assignment_action_id);
3389 fetch csr_entry_dep into l_entry_dep_rec;
3390 close csr_entry_dep;
3391 --
3392 for l_rec in csr_entry_os(l_assact_rec.assignment_action_id) loop
3393 --
3394 rollback_entry_os(l_rec,g_business_group_id);
3395 --
3396 end loop;
3397 --
3398 end if;
3399 --
3400 -- l_entry_dep_rec is set after 2011
3401 for l_rec in csr_entry(l_assact_rec.assignment_action_id) loop
3402 rollback_entry(l_rec,l_entry_dep_rec,g_business_group_id);
3403 end loop;
3404 --
3405 -- Rollback Transfer the followings.
3406 --
3407 -- JP_DEF_DEP --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP
3408 -- JP_DEF_DEP_OE --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OE
3409 -- JP_DEF_DEP_OS --> PER_CONTACT_EXTRA_INFO_F.JP_DEF_DEP_OS
3410 --
3411 -- The transaction sequence into PER_CONTACT_EXTRA_INFO_F must be
3412 -- at first "Insert", "Update" and at last "Delete" to avoid API errors.
3413 --
3414 -- "Insert" phase
3415 --
3416 for l_rec in csr_dep_ins(l_assact_rec.assignment_action_id) loop
3417 rollback_dep(l_rec);
3418 end loop;
3419 --
3420 for l_rec in csr_dep_oe_ins(l_assact_rec.assignment_action_id) loop
3421 rollback_dep_oe(l_rec);
3422 end loop;
3423 --
3424 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3425 --
3426 for l_rec in csr_dep_os_ins(l_assact_rec.assignment_action_id) loop
3427 rollback_dep_os(l_rec);
3428 end loop;
3429 --
3430 end if;
3431 --
3432 --
3433 -- "Update" phase
3434 --
3435 for l_rec in csr_dep_upd(l_assact_rec.assignment_action_id) loop
3436 rollback_dep(l_rec);
3437 end loop;
3438 --
3439 for l_rec in csr_dep_oe_upd(l_assact_rec.assignment_action_id) loop
3440 rollback_dep_oe(l_rec);
3441 end loop;
3442 --
3443 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3444 --
3445 for l_rec in csr_dep_os_upd(l_assact_rec.assignment_action_id) loop
3446 rollback_dep_os(l_rec);
3447 end loop;
3448 --
3449 end if;
3450 --
3451 --
3452 -- "Delete" phase
3453 --
3454 for l_rec in csr_dep_del(l_assact_rec.assignment_action_id) loop
3455 rollback_dep(l_rec);
3456 end loop;
3457 --
3458 for l_rec in csr_dep_oe_del(l_assact_rec.assignment_action_id) loop
3459 rollback_dep_oe(l_rec);
3460 end loop;
3461 --
3462 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3463 --
3464 for l_rec in csr_dep_os_del(l_assact_rec.assignment_action_id) loop
3465 rollback_dep_os(l_rec);
3466 end loop;
3467 --
3468 end if;
3469 --
3470 if p_create_session then
3471 delete_session;
3472 end if;
3473 --
3474 l_assact_rec.transfer_status := 'U';
3475 --
3476 pay_jp_def_api.update_assact(
3477 P_VALIDATE => false,
3478 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
3479 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
3480 P_TRANSFER_STATUS => l_assact_rec.transfer_status);
3481 --
3482 p_object_version_number := l_assact_rec.object_version_number;
3483 --
3484 hr_utility.set_location('Leaving : ' || c_proc, 20);
3485 --
3486 end rollback_transfer;
3487 --
3488 procedure rollback_transfer(
3489 p_action_information_id in number,
3490 p_object_version_number in out nocopy number,
3491 p_return_status out nocopy varchar2)
3492 is
3493 l_proc varchar2(61) := c_package || '.rollback_transfer';
3494 begin
3495 savepoint rollback_transfer;
3496 --
3497 -- Initialise Multiple Message Detection
3498 --
3499 hr_multi_message.enable_message_list;
3500 --
3501 rollback_transfer(p_action_information_id, p_object_version_number);
3502 --
3503 p_return_status := hr_multi_message.get_return_status_disable;
3504 exception
3505 when hr_multi_message.error_message_exist then
3506 rollback to rollback_transfer;
3507 p_object_version_number := null;
3508 p_return_status := hr_multi_message.get_return_status_disable;
3509 when others then
3510 rollback to rollback_transfer;
3511 if hr_multi_message.unexpected_error_add(l_proc) then
3512 raise;
3513 end if;
3514 p_object_version_number := null;
3515 p_return_status := hr_multi_message.get_return_status_disable;
3516 end rollback_transfer;
3517 -- |---------------------------------------------------------------------------|
3518 -- |-------------------------------< do_expire >-------------------------------|
3519 -- |---------------------------------------------------------------------------|
3520 procedure do_expire(
3521 p_action_information_id in number,
3522 p_object_version_number in out nocopy number,
3523 p_expiry_date in date)
3524 is
3525 --
3526 c_proc constant varchar2(61) := c_package ||'do_expire';
3527 --
3528 l_business_group_id number;
3529 --
3530 l_assact_rec pay_jp_def_assact_v%rowtype;
3531 l_year_end_date date;
3532 l_esd date;
3533 l_eed date;
3534 l_warning boolean;
3535 --
3536 cursor csr_entry(p_assignment_action_id number)
3537 is
3538 select v.*
3539 from pay_jp_def_entry_v v
3540 where v.assignment_action_id = p_assignment_action_id
3541 and exists(
3542 select null
3543 from pay_element_entries_f pee
3544 where pee.element_entry_id = v.element_entry_id
3545 and p_expiry_date + 1
3546 between pee.effective_start_date and pee.effective_end_date)
3547 for update nowait;
3548 --
3549 cursor csr_entry_os(p_assignment_action_id number)
3550 is
3551 select v.*
3552 from pay_jp_def_entry_os_v v
3553 where v.assignment_action_id = p_assignment_action_id
3554 and exists(
3555 select null
3556 from pay_element_entries_f pee
3557 where pee.element_entry_id = v.element_entry_id
3558 and p_expiry_date + 1
3559 between pee.effective_start_date and pee.effective_end_date)
3560 for update nowait;
3561 --
3562 cursor csr_dep(p_assignment_action_id number)
3563 is
3564 select v.*
3565 from pay_jp_def_dep_v v
3566 where v.assignment_action_id = p_assignment_action_id
3567 and status <> 'D'
3568 and exists(
3569 select null
3570 from per_contact_extra_info_f cei
3571 where cei.contact_extra_info_id = v.contact_extra_info_id
3572 and p_expiry_date + 1
3573 between cei.effective_start_date and cei.effective_end_date)
3574 for update nowait;
3575 --
3576 cursor csr_dep_oe(p_assignment_action_id number)
3577 is
3578 select v.*
3579 from pay_jp_def_dep_oe_v v
3580 where v.assignment_action_id = p_assignment_action_id
3581 and status <> 'D'
3582 and exists(
3583 select null
3584 from per_contact_extra_info_f cei
3585 where cei.contact_extra_info_id = v.contact_extra_info_id
3586 and p_expiry_date + 1
3587 between cei.effective_start_date and cei.effective_end_date)
3588 for update nowait;
3589 --
3590 cursor csr_dep_os(p_assignment_action_id number)
3591 is
3592 select v.*
3593 from pay_jp_def_dep_os_v v
3594 where v.assignment_action_id = p_assignment_action_id
3595 and status <> 'D'
3596 and exists(
3597 select null
3598 from per_contact_extra_info_f cei
3599 where cei.contact_extra_info_id = v.contact_extra_info_id
3600 and p_expiry_date + 1
3601 between cei.effective_start_date and cei.effective_end_date)
3602 for update nowait;
3603 --
3604 begin
3605 --
3606 hr_utility.set_location('Entering : ' || c_proc, 10);
3607 --
3608 hr_api.mandatory_arg_error(c_proc, 'expiry_date', p_expiry_date);
3609 --
3610 lock_assact(p_action_information_id, p_object_version_number, l_business_group_id, l_assact_rec);
3611 --
3612 if l_assact_rec.transfer_status = 'U' then
3613 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_TRANSFERRED_YET');
3614 fnd_message.raise_error;
3615 elsif l_assact_rec.transfer_status = 'E' then
3616 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_EXPIRED');
3617 fnd_message.raise_error;
3618 end if;
3619 --
3620 l_year_end_date := add_months(trunc(l_assact_rec.effective_date, 'YYYY'), 12) - 1;
3621 --
3622 if p_expiry_date < l_assact_rec.effective_date
3623 or p_expiry_date > l_year_end_date then
3624 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_EXPIRY_DATE');
3625 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_chardate(l_assact_rec.effective_date));
3626 fnd_message.set_token('YEAR_END_DATE', fnd_date.date_to_chardate(l_year_end_date));
3627 fnd_message.raise_error;
3628 end if;
3629 --
3630 insert_session(p_expiry_date);
3631 --
3632 for l_rec in csr_entry(l_assact_rec.assignment_action_id) loop
3633 --
3634 pay_element_entry_api.delete_element_entry(
3635 p_validate => false,
3636 p_effective_date => p_expiry_date,
3637 p_datetrack_delete_mode => 'DELETE',
3638 p_element_entry_id => l_rec.element_entry_id,
3639 p_object_version_number => l_rec.ee_object_version_number,
3640 p_effective_start_date => l_esd,
3641 p_effective_end_date => l_eed,
3642 p_delete_warning => l_warning);
3643 --
3644 pay_jp_def_api.update_entry(
3645 P_VALIDATE => false,
3646 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3647 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3648 P_EE_OBJECT_VERSION_NUMBER => l_rec.ee_object_version_number);
3649 --
3650 end loop;
3651 --
3652 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
3653 --
3654 for l_rec in csr_entry_os(l_assact_rec.assignment_action_id) loop
3655 --
3656 pay_element_entry_api.delete_element_entry(
3657 p_validate => false,
3658 p_effective_date => p_expiry_date,
3659 p_datetrack_delete_mode => 'DELETE',
3660 p_element_entry_id => l_rec.element_entry_id,
3661 p_object_version_number => l_rec.ee_object_version_number,
3662 p_effective_start_date => l_esd,
3663 p_effective_end_date => l_eed,
3664 p_delete_warning => l_warning);
3665 --
3666 pay_jp_def_api.update_entry_os(
3667 p_validate => false,
3668 p_action_information_id => l_rec.action_information_id,
3669 p_object_version_number => l_rec.object_version_number,
3670 p_ee_object_version_number => l_rec.ee_object_version_number);
3671 --
3672 end loop;
3673 --
3674 end if;
3675 --
3676 for l_rec in csr_dep(l_assact_rec.assignment_action_id) loop
3677 --
3678 hr_contact_extra_info_api.delete_contact_extra_info(
3679 p_validate => false,
3680 p_effective_date => p_expiry_date,
3681 p_datetrack_delete_mode => 'DELETE',
3682 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3683 p_object_version_number => l_rec.cei_object_version_number,
3684 p_effective_start_date => l_esd,
3685 p_effective_end_date => l_eed);
3686 --
3687 pay_jp_def_api.update_dep(
3688 P_VALIDATE => false,
3689 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3690 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3691 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3692 --
3693 end loop;
3694 --
3695 for l_rec in csr_dep_oe(l_assact_rec.assignment_action_id) loop
3696 --
3697 hr_contact_extra_info_api.delete_contact_extra_info(
3698 p_validate => false,
3699 p_effective_date => p_expiry_date,
3700 p_datetrack_delete_mode => 'DELETE',
3701 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3702 p_object_version_number => l_rec.cei_object_version_number,
3703 p_effective_start_date => l_esd,
3704 p_effective_end_date => l_eed);
3705 --
3706 pay_jp_def_api.update_dep_oe(
3707 P_VALIDATE => false,
3708 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3709 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3710 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3711 --
3712 end loop;
3713 --
3714 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3715 --
3716 for l_rec in csr_dep_os(l_assact_rec.assignment_action_id) loop
3717 --
3718 hr_contact_extra_info_api.delete_contact_extra_info(
3719 p_validate => false,
3720 p_effective_date => p_expiry_date,
3721 p_datetrack_delete_mode => 'DELETE',
3722 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3723 p_object_version_number => l_rec.cei_object_version_number,
3724 p_effective_start_date => l_esd,
3725 p_effective_end_date => l_eed);
3726 --
3727 pay_jp_def_api.update_dep_os(
3728 P_VALIDATE => false,
3729 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3730 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3731 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3732 --
3733 end loop;
3734 --
3735 end if;
3736 --
3737 delete_session;
3738 --
3739 l_assact_rec.transfer_status := 'E';
3740 l_assact_rec.expiry_date := p_expiry_date;
3741 --
3742 pay_jp_def_api.update_assact(
3743 P_VALIDATE => false,
3744 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
3745 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
3746 P_TRANSFER_STATUS => l_assact_rec.transfer_status,
3747 P_EXPIRY_DATE => l_assact_rec.expiry_date);
3748 --
3749 p_object_version_number := l_assact_rec.object_version_number;
3750 --
3751 hr_utility.set_location('Leaving : ' || c_proc, 20);
3752 --
3753 end do_expire;
3754 --
3755 procedure do_expire(
3756 p_action_information_id in number,
3757 p_object_version_number in out nocopy number,
3758 p_expiry_date in date,
3759 p_return_status out nocopy varchar2)
3760 is
3761 l_proc varchar2(61) := c_package || '.do_expire';
3762 begin
3763 savepoint do_expire;
3764 --
3765 -- Initialise Multiple Message Detection
3766 --
3767 hr_multi_message.enable_message_list;
3768 --
3769 do_expire(p_action_information_id, p_object_version_number, p_expiry_date);
3770 --
3771 p_return_status := hr_multi_message.get_return_status_disable;
3772 exception
3773 when hr_multi_message.error_message_exist then
3774 rollback to do_expire;
3775 p_object_version_number := null;
3776 p_return_status := hr_multi_message.get_return_status_disable;
3777 when others then
3778 rollback to do_expire;
3779 if hr_multi_message.unexpected_error_add(l_proc) then
3780 raise;
3781 end if;
3782 p_object_version_number := null;
3783 p_return_status := hr_multi_message.get_return_status_disable;
3784 end do_expire;
3785 -- |---------------------------------------------------------------------------|
3786 -- |----------------------------< rollback_expire >----------------------------|
3787 -- |---------------------------------------------------------------------------|
3788 procedure rollback_expire(
3789 p_action_information_id in number,
3790 p_object_version_number in out nocopy number)
3791 is
3792 --
3793 c_proc constant varchar2(61) := c_package ||'rollback_expire';
3794 --
3795 l_business_group_id number;
3796 --
3797 l_assact_rec pay_jp_def_assact_v%rowtype;
3798 l_esd date;
3799 l_eed date;
3800 l_warning boolean;
3801 --
3802 cursor csr_entry(
3803 p_assignment_action_id number,
3804 p_expiry_date date)
3805 is
3806 select v.*
3807 from pay_jp_def_entry_v v
3808 where v.assignment_action_id = p_assignment_action_id
3809 and exists(
3810 select null
3811 from pay_element_entries_f pee
3812 where pee.element_entry_id = v.element_entry_id
3813 and p_expiry_date
3814 between pee.effective_start_date and pee.effective_end_date)
3815 for update nowait;
3816 --
3817 cursor csr_entry_os(
3818 p_assignment_action_id number,
3819 p_expiry_date date)
3820 is
3821 select v.*
3822 from pay_jp_def_entry_os_v v
3823 where v.assignment_action_id = p_assignment_action_id
3824 and exists(
3825 select null
3826 from pay_element_entries_f pee
3827 where pee.element_entry_id = v.element_entry_id
3828 and p_expiry_date
3829 between pee.effective_start_date and pee.effective_end_date)
3830 for update nowait;
3831 --
3832 cursor csr_dep(
3833 p_assignment_action_id number,
3834 p_expiry_date date)
3835 is
3836 select v.*
3837 from pay_jp_def_dep_v v
3838 where v.assignment_action_id = p_assignment_action_id
3839 and status <> 'D'
3840 and exists(
3841 select null
3842 from per_contact_extra_info_f cei
3843 where cei.contact_extra_info_id = v.contact_extra_info_id
3844 and p_expiry_date
3845 between cei.effective_start_date and cei.effective_end_date)
3846 for update nowait;
3847 --
3848 cursor csr_dep_oe(
3849 p_assignment_action_id number,
3850 p_expiry_date date)
3851 is
3852 select v.*
3853 from pay_jp_def_dep_oe_v v
3854 where v.assignment_action_id = p_assignment_action_id
3855 and status <> 'D'
3856 and exists(
3857 select null
3858 from per_contact_extra_info_f cei
3859 where cei.contact_extra_info_id = v.contact_extra_info_id
3860 and p_expiry_date
3861 between cei.effective_start_date and cei.effective_end_date)
3862 for update nowait;
3863 --
3864 cursor csr_dep_os(
3865 p_assignment_action_id number,
3866 p_expiry_date date)
3867 is
3868 select v.*
3869 from pay_jp_def_dep_os_v v
3870 where v.assignment_action_id = p_assignment_action_id
3871 and status <> 'D'
3872 and exists(
3873 select null
3874 from per_contact_extra_info_f cei
3875 where cei.contact_extra_info_id = v.contact_extra_info_id
3876 and p_expiry_date
3877 between cei.effective_start_date and cei.effective_end_date)
3878 for update nowait;
3879 --
3880 begin
3881 --
3882 hr_utility.set_location('Entering : ' || c_proc, 10);
3883 --
3884 lock_assact(p_action_information_id, p_object_version_number, l_business_group_id, l_assact_rec);
3885 --
3886 if l_assact_rec.transfer_status <> 'E' then
3887 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_EXPIRED_YET');
3888 fnd_message.raise_error;
3889 end if;
3890 --
3891 insert_session(l_assact_rec.expiry_date);
3892 --
3893 for l_rec in csr_entry(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3894 --
3895 pay_element_entry_api.delete_element_entry(
3896 p_validate => false,
3897 p_effective_date => l_assact_rec.expiry_date,
3898 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3899 p_element_entry_id => l_rec.element_entry_id,
3900 p_object_version_number => l_rec.ee_object_version_number,
3901 p_effective_start_date => l_esd,
3902 p_effective_end_date => l_eed,
3903 p_delete_warning => l_warning);
3904 --
3905 pay_jp_def_api.update_entry(
3906 P_VALIDATE => false,
3907 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3908 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3909 P_EE_OBJECT_VERSION_NUMBER => l_rec.ee_object_version_number);
3910 --
3911 end loop;
3912 --
3913 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) >= 2011 then
3914 --
3915 for l_rec in csr_entry_os(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3916 --
3917 pay_element_entry_api.delete_element_entry(
3918 p_validate => false,
3919 p_effective_date => l_assact_rec.expiry_date,
3920 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3921 p_element_entry_id => l_rec.element_entry_id,
3922 p_object_version_number => l_rec.ee_object_version_number,
3923 p_effective_start_date => l_esd,
3924 p_effective_end_date => l_eed,
3925 p_delete_warning => l_warning);
3926 --
3927 pay_jp_def_api.update_entry_os(
3928 p_validate => false,
3929 p_action_information_id => l_rec.action_information_id,
3930 p_object_version_number => l_rec.object_version_number,
3931 p_ee_object_version_number => l_rec.ee_object_version_number);
3932 --
3933 end loop;
3934 --
3935 end if;
3936 --
3937 for l_rec in csr_dep(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3938 --
3939 hr_contact_extra_info_api.delete_contact_extra_info(
3940 p_validate => false,
3941 p_effective_date => l_assact_rec.expiry_date,
3942 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3943 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3944 p_object_version_number => l_rec.cei_object_version_number,
3945 p_effective_start_date => l_esd,
3946 p_effective_end_date => l_eed);
3947 --
3948 pay_jp_def_api.update_dep(
3949 P_VALIDATE => false,
3950 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3951 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3952 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3953 --
3954 end loop;
3955 --
3956 for l_rec in csr_dep_oe(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3957 --
3958 hr_contact_extra_info_api.delete_contact_extra_info(
3959 p_validate => false,
3960 p_effective_date => l_assact_rec.expiry_date,
3961 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3962 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3963 p_object_version_number => l_rec.cei_object_version_number,
3964 p_effective_start_date => l_esd,
3965 p_effective_end_date => l_eed);
3966 --
3967 pay_jp_def_api.update_dep_oe(
3968 P_VALIDATE => false,
3969 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3970 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3971 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3972 --
3973 end loop;
3974 --
3975 if to_number(to_char(l_assact_rec.effective_date,'YYYY')) < 2011 then
3976 --
3977 for l_rec in csr_dep_os(l_assact_rec.assignment_action_id, l_assact_rec.expiry_date) loop
3978 --
3979 hr_contact_extra_info_api.delete_contact_extra_info(
3980 p_validate => false,
3981 p_effective_date => l_assact_rec.expiry_date,
3982 p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
3983 p_contact_extra_info_id => l_rec.contact_extra_info_id,
3984 p_object_version_number => l_rec.cei_object_version_number,
3985 p_effective_start_date => l_esd,
3986 p_effective_end_date => l_eed);
3987 --
3988 pay_jp_def_api.update_dep_os(
3989 P_VALIDATE => false,
3990 P_ACTION_INFORMATION_ID => l_rec.action_information_id,
3991 P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
3992 P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
3993 --
3994 end loop;
3995 --
3996 end if;
3997 --
3998 delete_session;
3999 --
4000 l_assact_rec.transfer_status := 'T';
4001 l_assact_rec.expiry_date := null;
4002 --
4003 pay_jp_def_api.update_assact(
4004 P_VALIDATE => false,
4005 P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
4006 P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
4007 P_TRANSFER_STATUS => l_assact_rec.transfer_status,
4008 P_EXPIRY_DATE => l_assact_rec.expiry_date);
4009 --
4010 p_object_version_number := l_assact_rec.object_version_number;
4011 --
4012 hr_utility.set_location('Leaving : ' || c_proc, 20);
4013 --
4014 end rollback_expire;
4015 --
4016 procedure rollback_expire(
4017 p_action_information_id in number,
4018 p_object_version_number in out nocopy number,
4019 p_return_status out nocopy varchar2)
4020 is
4021 l_proc varchar2(61) := c_package || '.rollback_expire';
4022 begin
4023 savepoint rollback_expire;
4024 --
4025 -- Initialise Multiple Message Detection
4026 --
4027 hr_multi_message.enable_message_list;
4028 --
4029 rollback_expire(p_action_information_id, p_object_version_number);
4030 --
4031 p_return_status := hr_multi_message.get_return_status_disable;
4032 exception
4033 when hr_multi_message.error_message_exist then
4034 rollback to rollback_expire;
4035 p_object_version_number := null;
4036 p_return_status := hr_multi_message.get_return_status_disable;
4037 when others then
4038 rollback to rollback_expire;
4039 if hr_multi_message.unexpected_error_add(l_proc) then
4040 raise;
4041 end if;
4042 p_object_version_number := null;
4043 p_return_status := hr_multi_message.get_return_status_disable;
4044 end rollback_expire;
4045 --
4046 -- |---------------------------------------------------------------------------|
4047 -- |--------------------------< delete_unfinalized >---------------------------|
4048 -- |---------------------------------------------------------------------------|
4049 procedure delete_unfinalized(
4050 errbuf out nocopy varchar2,
4051 retcode out nocopy varchar2,
4052 p_payroll_action_id in varchar2)
4053 is
4054 l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
4055 cursor csr_assact is
4056 select paa.assignment_action_id
4057 from pay_jp_def_assact_v aif,
4058 pay_assignment_actions paa
4059 where paa.payroll_action_id = l_payroll_action_id
4060 and aif.assignment_action_id(+) = paa.assignment_action_id
4061 and nvl(aif.transaction_status, 'U') not in ('F', 'A');
4062 begin
4063 --
4064 -- retcode
4065 -- 0 : Success
4066 -- 1 : Warning
4067 -- 2 : Error
4068 --
4069 retcode := '0';
4070 --
4071 for l_rec in csr_assact loop
4072 begin
4073 py_rollback_pkg.rollback_ass_action(
4074 p_assignment_action_id => l_rec.assignment_action_id,
4075 p_rollback_mode => 'ROLLBACK',
4076 p_leave_base_table_row => false,
4077 p_all_or_nothing => true,
4078 p_dml_mode => 'FULL');
4079 exception
4080 when others then
4081 retcode := '1';
4082 end;
4083 end loop;
4084 end delete_unfinalized;
4085 --
4086 end pay_jp_def_ss;