[Home] [Help]
PACKAGE BODY: APPS.PER_JP_EMPDET_ARCHIVE_PKG
Source
1 package body per_jp_empdet_archive_pkg as
2 /* $Header: pejpearc.pkb 120.34 2011/06/23 04:00:31 keyazawa noship $ */
3 --
4 c_package constant varchar2(30) := 'per_jp_empdet_archive_pkg.';
5 g_debug boolean := hr_utility.debug_enabled;
6 --
7 g_range_person varchar2(80);
8 g_ext_proc_name varchar2(150);
9 --
10 g_proc_type varchar2(30);
11 g_proc_type_assact_id number;
12 --
13 c_trm_ele_set varchar2(80) := 'TRM';
14 --
15 c_com_si_info_elm constant varchar2(80) := 'COM_SI_INFO';
16 c_hi_card_num_iv constant varchar2(80) := 'HI_CARD_NUM';
17 c_basic_pension_num_iv constant varchar2(80) := 'BASIC_PENSION_NUM';
18 c_wpf_members_num_iv constant varchar2(80) := 'WPF_MEMBERS_NUM';
19 --
20 c_com_li_info_elm constant varchar2(80) := 'COM_LI_INFO';
21 c_ei_num_iv constant varchar2(80) := 'EI_NUM';
22 --
23 c_com_hi_quailfy_info_elm constant varchar2(80) := 'COM_HI_QUALIFY_INFO';
24 c_com_wp_quailfy_info_elm constant varchar2(80) := 'COM_WP_QUALIFY_INFO';
25 c_com_wpf_quailfy_info_elm constant varchar2(80) := 'COM_WPF_QUALIFY_INFO';
26 c_com_ei_quailfy_info_elm constant varchar2(80) := 'COM_EI_QUALIFY_INFO';
27 c_qualify_date_iv constant varchar2(80) := 'QUALIFY_DATE';
28 --
29 c_b_trm_ern_bal constant varchar2(80) := 'B_TRM_ERN';
30 c_asg_run_dim constant varchar2(80) := '_ASG_RUN';
31 --
32 c_fuzzy_ele_class_trm constant varchar2(80) := 'TRM';
33 --
34 c_com_si_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_si_info_elm,null,'JP');
35 c_hi_card_num_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_si_info_elm_id,c_hi_card_num_iv);
36 c_basic_pension_num_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_si_info_elm_id,c_basic_pension_num_iv);
37 c_wpf_members_num_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_si_info_elm_id,c_wpf_members_num_iv);
38 --
39 c_com_li_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_li_info_elm,null,'JP');
40 c_ei_num_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_li_info_elm_id,c_ei_num_iv);
41 --
42 c_com_hi_quailfy_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_hi_quailfy_info_elm,null,'JP');
43 c_hi_qualify_date_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_hi_quailfy_info_elm_id,c_qualify_date_iv);
44 --
45 c_com_wp_quailfy_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_wp_quailfy_info_elm,null,'JP');
46 c_wp_qualify_date_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_wp_quailfy_info_elm_id,c_qualify_date_iv);
47 --
48 c_com_wpf_quailfy_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_wpf_quailfy_info_elm,null,'JP');
49 c_wpf_qualify_date_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_wpf_quailfy_info_elm_id,c_qualify_date_iv);
50 --
51 c_com_ei_quailfy_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_ei_quailfy_info_elm,null,'JP');
52 c_ei_qualify_date_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_ei_quailfy_info_elm_id,c_qualify_date_iv);
53 --
54 c_b_trm_ern_bal_run_db_id constant number := hr_jp_id_pkg.defined_balance_id(c_b_trm_ern_bal,c_asg_run_dim,null,'JP');
55 --
56 c_trm_ele_set_id constant number := hr_jp_id_pkg.element_set_id(c_trm_ele_set,null,'JP');
57 --
58 c_jp_pea_flex_num number;
59 c_pai_max constant number := trunc(240/lengthb(to_multi_byte(' ')));
60 c_ext_proc_body varchar2(2000);
61 --
62 type t_number_tbl is table of number index by binary_integer;
63 --
64 type t_per_rec is record(
65 person_id number,
66 assignment_id number,
67 assignment_action_id number,
68 ass_cnt number);
69 type t_per_tbl is table of t_per_rec index by binary_integer;
70 g_per_ind_tbl t_per_tbl;
71 --
72 type t_ass_rec is record(
73 person_id number,
74 assignment_id number,
75 assignment_action_id number,
76 effective_date date,
77 include_or_exclude hr_assignment_set_amendments.include_or_exclude%type);
78 type t_ass_tbl is table of t_ass_rec index by binary_integer;
79 g_ass_tbl t_ass_tbl;
80 g_ass_ind_tbl t_ass_tbl;
81 --
82 -- -------------------------------------------------------------------------
83 -- get_fuzzy_proc_type
84 -- -------------------------------------------------------------------------
85 function get_fuzzy_proc_type(
86 p_assignment_action_id in number)
87 return varchar2
88 is
89 --
90 l_proc_type varchar2(30);
91 --
92 l_classification_name pay_element_classifications.classification_name%type;
93 --
94 cursor csr_classification
95 is
96 select /*+ ORDERED */
97 pec.classification_name
98 from pay_assignment_actions paa,
99 pay_payroll_actions ppa,
100 pay_run_results prr,
101 pay_element_types_f pet,
102 pay_element_classifications pec
103 where paa.assignment_action_id = p_assignment_action_id
104 and ppa.payroll_action_id = paa.payroll_action_id
105 and prr.assignment_action_id = paa.assignment_action_id
106 and pet.element_type_id = prr.element_type_id
107 and ppa.effective_date
108 between pet.effective_start_date and pet.effective_end_date
109 and pec.classification_id = pet.classification_id
110 and pec.classification_name like '%'||c_fuzzy_ele_class_trm||'%';
111 --
112 begin
113 --
114 l_proc_type := g_proc_type;
115 --
116 if g_proc_type_assact_id is null
117 or g_proc_type_assact_id <> p_assignment_action_id then
118 --
119 -- recognize from classification of first record
120 open csr_classification;
121 fetch csr_classification into l_classification_name;
122 close csr_classification;
123 --
124 l_proc_type := null;
125 --
126 if l_classification_name like '%'||c_fuzzy_ele_class_trm||'%' then
127 --
128 l_proc_type := 'TRM';
129 --
130 end if;
131 --
132 g_proc_type_assact_id := p_assignment_action_id;
133 g_proc_type := l_proc_type;
134 --
135 end if;
136 --
137 return l_proc_type;
138 end get_fuzzy_proc_type;
139 --
140 -- -------------------------------------------------------------------------
141 -- set_detail_debug
142 -- -------------------------------------------------------------------------
143 procedure set_detail_debug(
144 p_yn in varchar2)
145 is
146 --
147 -- hidden option for tracking
148 cursor csr_hidden_debug
149 is
150 select parameter_value
151 from pay_action_parameters
152 where parameter_name = 'JP_DEBUG_EMPDET';
153 --
154 l_hidden_debug pay_action_parameters.parameter_value%type;
155 --
156 begin
157 --
158 if p_yn is not null then
159 --
160 per_jp_empdet_archive_pkg.g_detail_debug := p_yn;
161 --
162 else
163 --
164 open csr_hidden_debug;
165 fetch csr_hidden_debug into l_hidden_debug;
166 close csr_hidden_debug;
167 --
168 if l_hidden_debug = 'Y' then
169 --
170 per_jp_empdet_archive_pkg.g_detail_debug := l_hidden_debug;
171 --
172 end if;
173 --
174 end if;
175 --
176 if g_debug then
177 --
178 hr_utility.trace('l_hidden_debug : '||l_hidden_debug);
179 hr_utility.trace('g_detail_debug : '||g_detail_debug);
180 --
181 end if;
182 --
183 end set_detail_debug;
184 --
185 -- -------------------------------------------------------------------------
186 -- sequence of process.
187 -- 1. range_cursor (inc. init_pact, archive_pact)
188 -- 2. assignment_action_creation <= invoked by each ranges
189 -- 3. archinit (inc. init_pact) <= invoked by each threads, start from here in mark-for-retry)
190 -- 4. archive_data (inc. init_assact, archive_assact)
191 -- 5. deinitialize_code
192 -- -------------------------------------------------------------------------
193 -- init_pact
194 -- -------------------------------------------------------------------------
195 procedure init_pact(
196 p_payroll_action_id in number)
197 is
198 --
199 l_proc varchar2(80) := c_package||'init_pact';
200 --
201 l_detail_debug varchar2(1) := per_jp_empdet_archive_pkg.g_detail_debug;
202 --
203 cursor csr_action
204 is
205 select /*+ ORDERED */
206 ppa.business_group_id,
207 ppa.effective_date,
208 ppa.legislative_parameters,
209 pbg.legislation_code
210 from pay_payroll_actions ppa,
211 per_business_groups_perf pbg
212 where ppa.payroll_action_id = p_payroll_action_id
213 and pbg.business_group_id = ppa.business_group_id;
214 --
215 cursor csr_range_person
216 is
217 select parameter_value
218 from pay_action_parameters
219 where parameter_name = 'RANGE_PERSON_ID';
220 --
221 cursor csr_jp_pea_flex_num
222 is
223 select id_flex_num
224 from fnd_id_flex_structures
225 where application_id = 800
226 and id_flex_structure_code = 'JP_EDUC_BKGRD'
227 and id_flex_code = 'PEA';
228 --
229 -- unknown requirement, design..
230 -- why multiple entry, sod/eod exist but sync with old pkg
231 -- fetch only 1st record, so basically only 1 record is available for this.
232 cursor csr_ext_proc_name
233 is
234 select hoi.org_information4 proc_name
235 from hr_organization_information hoi
236 where hoi.organization_id = g_business_group_id
237 and hoi.org_information_context = 'JP_REPORTS_ADDITIONAL_INFO'
238 and hoi.org_information1 = 'JPEMPLDETAILSREPORT'
239 and hoi.org_information3 = 'ADDINFO'
240 and hoi.org_information4 is not null
241 and g_effective_date
242 between nvl(fnd_date.canonical_to_date(hoi.org_information5),hr_api.g_sot) and nvl(fnd_date.canonical_to_date(hoi.org_information6),g_effective_date);
243 --
244 l_csr_action csr_action%rowtype;
245 --
246 begin
247 --
248 set_detail_debug(l_detail_debug);
249 --
250 if g_debug
251 and g_detail_debug = 'Y' then
252 hr_utility.set_location(l_proc,0);
253 end if;
254 --
255 if g_payroll_action_id is null
256 or g_payroll_action_id <> p_payroll_action_id then
257 --
258 if g_debug
259 and g_detail_debug = 'Y' then
260 hr_utility.set_location(l_proc,10);
261 hr_utility.trace('no cache : g_pact_id('||g_payroll_action_id||'),p_pact_id('||p_payroll_action_id||')');
262 end if;
263 --
264 open csr_action;
265 fetch csr_action into l_csr_action;
266 if csr_action%notfound then
267 close csr_action;
268 fnd_message.set_name('PAY','PAY_34985_INVALID_PAY_ACTION');
269 fnd_message.raise_error;
270 end if;
271 close csr_action;
272 --
273 g_payroll_action_id := p_payroll_action_id;
274 g_effective_date := l_csr_action.effective_date;
275 g_business_group_id := l_csr_action.business_group_id;
276 g_legislation_code := l_csr_action.legislation_code;
277 g_inc_org_hier_flag := pay_core_utils.get_parameter('IOH',l_csr_action.legislative_parameters);
278 g_organization_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ORG',l_csr_action.legislative_parameters));
279 g_location_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('LOC',l_csr_action.legislative_parameters));
280 g_inc_term_flag := pay_core_utils.get_parameter('ITE',l_csr_action.legislative_parameters);
281 g_term_date_from := fnd_date.canonical_to_date(pay_core_utils.get_parameter('TEDF',l_csr_action.legislative_parameters));
282 g_term_date_to := fnd_date.canonical_to_date(pay_core_utils.get_parameter('TEDT',l_csr_action.legislative_parameters));
283 g_assignment_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSSET',l_csr_action.legislative_parameters));
284 --
285 g_ass_set_formula_id := null;
286 g_ass_set_amendment_type := null;
287 if g_assignment_set_id is not null then
288 --
289 hr_jp_ast_utility_pkg.get_assignment_set_info(g_assignment_set_id,g_ass_set_formula_id,g_ass_set_amendment_type);
290 --
291 end if;
292 --
293 open csr_range_person;
294 fetch csr_range_person into g_range_person;
295 close csr_range_person;
296 --
297 open csr_jp_pea_flex_num;
298 fetch csr_jp_pea_flex_num into c_jp_pea_flex_num;
299 close csr_jp_pea_flex_num;
300 --
301 open csr_ext_proc_name;
302 fetch csr_ext_proc_name into g_ext_proc_name;
303 close csr_ext_proc_name;
304 --
305 if g_ext_proc_name is not null then
306 --
307 c_ext_proc_body := '
308 (p_assignment_id => :i_assignment_id,
309 p_effective_date => :i_effective_date,
310 x_info1 => :o_extra_info1,
311 x_info2 => :o_extra_info2,
312 x_info3 => :o_extra_info3,
313 x_info4 => :o_extra_info4,
314 x_info5 => :o_extra_info5,
315 x_info6 => :o_extra_info6,
316 x_info7 => :o_extra_info7,
317 x_info8 => :o_extra_info8,
318 x_info9 => :o_extra_info9,
319 x_info10 => :o_extra_info10,
320 x_info11 => :o_extra_info11,
321 x_info12 => :o_extra_info12,
322 x_info13 => :o_extra_info13,
323 x_info14 => :o_extra_info14,
324 x_info15 => :o_extra_info15,
325 x_info16 => :o_extra_info16,
326 x_info17 => :o_extra_info17,
327 x_info18 => :o_extra_info18,
328 x_info19 => :o_extra_info19,
329 x_info20 => :o_extra_info20,
330 x_info21 => :o_extra_info21,
331 x_info22 => :o_extra_info22,
332 x_info23 => :o_extra_info23,
333 x_info24 => :o_extra_info24,
334 x_info25 => :o_extra_info25,
335 x_info26 => :o_extra_info26,
336 x_info27 => :o_extra_info27,
337 x_info28 => :o_extra_info28,
338 x_info29 => :o_extra_info29,
339 x_info30 => :o_extra_info30)';
340 --
341 end if;
342 --
343 end if;
344 --
345 if g_debug then
346 hr_utility.trace('g_payroll_action_id : '||to_char(g_payroll_action_id));
347 hr_utility.trace('g_effective_date : '||to_char(g_effective_date,'YYYY/MM/DD'));
348 hr_utility.trace('g_business_group_id : '||to_char(g_business_group_id));
349 hr_utility.trace('g_legislation_code : '||g_legislation_code);
350 hr_utility.trace('g_inc_org_hier_flag : '||g_inc_org_hier_flag);
351 hr_utility.trace('g_organization_id : '||to_char(g_organization_id));
352 hr_utility.trace('g_location_id : '||to_char(g_location_id));
353 hr_utility.trace('g_inc_term_flag : '||g_inc_term_flag);
354 hr_utility.trace('g_term_date_from : '||to_char(g_term_date_from,'YYYY/MM/DD'));
355 hr_utility.trace('g_term_date_to : '||to_char(g_term_date_to,'YYYY/MM/DD'));
356 hr_utility.trace('g_assignment_set_id : '||to_char(g_assignment_set_id));
357 hr_utility.trace('g_ass_set_formula_id : '||to_char(g_ass_set_formula_id));
358 hr_utility.trace('g_ass_set_amendment_type : '||g_ass_set_amendment_type);
359 hr_utility.trace('g_range_person : '||g_range_person);
360 hr_utility.trace('c_jp_pea_flex_num : '||to_char(c_jp_pea_flex_num));
361 hr_utility.trace('g_ext_proc_name : '||g_ext_proc_name);
362 end if;
363 --
364 if g_debug
365 and g_detail_debug = 'Y' then
366 hr_utility.set_location(l_proc,1000);
367 end if;
368 --
369 end init_pact;
370 --
371 -- -------------------------------------------------------------------------
372 -- archive_pact
373 -- -------------------------------------------------------------------------
374 --procedure archive_pact(
375 -- p_payroll_action_id in number)
376 --is
377 ----
378 -- l_proc varchar2(80) := c_package||'archive_pact';
379 ----
380 --begin
381 ----
382 -- if g_debug
383 -- and g_detail_debug = 'Y' then
384 -- hr_utility.set_location(l_proc,0);
385 -- end if;
386 ----
387 -- -- no create, update pact in mark for retry.
388 ----
389 -- if g_debug
390 -- and g_detail_debug = 'Y' then
391 -- hr_utility.set_location(l_proc,1000);
392 -- end if;
393 ----
394 --end archive_pact;
395 --
396 -- -------------------------------------------------------------------------
397 -- range_cursor
398 -- -------------------------------------------------------------------------
399 procedure range_code(
400 p_payroll_action_id in number,
401 p_sqlstr out nocopy varchar2)
402 is
403 --
404 l_proc varchar2(80) := c_package||'range_code';
405 --
406 l_detail_debug varchar2(1) := per_jp_empdet_archive_pkg.g_detail_debug;
407 --
408 begin
409 --
410 if g_debug then
411 hr_utility.set_location(l_proc,0);
412 end if;
413 --
414 init_pact(p_payroll_action_id);
415 set_detail_debug(l_detail_debug);
416 --
417 if g_debug
418 and g_detail_debug = 'Y' then
419 hr_utility.set_location(l_proc,10);
420 end if;
421 --
422 --archive_pact(p_payroll_action_id);
423 --
424 --if g_debug
425 --and g_detail_debug = 'Y' then
426 -- hr_utility.set_location(l_proc,20);
427 --end if;
428 --
429 -- consider security view, only employee target (ref ppos)
430 -- include any term emp whose final proc date is before effective date
431 p_sqlstr :=
432 'select distinct ppos.person_id
433 from pay_payroll_actions ppa,
434 per_people_f pp,
435 per_periods_of_service ppos
436 where ppa.payroll_action_id = :payroll_action_id
437 and pp.business_group_id = ppa.business_group_id + 0
438 and ppos.person_id = pp.person_id
439 and ppos.business_group_id + 0 = pp.business_group_id
440 and ppos.date_start <= fnd_date.canonical_to_date(''i_effective_date'')
441 order by
442 ppos.person_id';
443 --
444 p_sqlstr := replace(p_sqlstr,'i_effective_date',fnd_date.date_to_canonical(g_effective_date));
445 --
446 if g_debug then
447 hr_utility.set_location(l_proc,1000);
448 end if;
449 --
450 end range_code;
451 --
452 -- -------------------------------------------------------------------------
453 -- assignment_action_creation
454 -- -------------------------------------------------------------------------
455 procedure assignment_action_code(
456 p_payroll_action_id in number,
457 p_start_person_id in number,
458 p_end_person_id in number,
459 p_chunk_number in number)
460 is
461 --
462 l_proc varchar2(80) := c_package||'assignment_action_code';
463 --
464 l_assignment_action_id number;
465 --
466 l_ass_cnt number;
467 l_person_id number;
468 l_assignment_id number;
469 l_ass_tbl_cnt number := 0;
470 --
471 l_ass_id_tbl t_number_tbl;
472 l_ass_id_tbl_cnt number;
473 --
474 -- consider security view
475 -- include term emp if g_inc_term_flag is set even if not exist in process time
476 -- exclude applicant, no other emp etc, through period of service join
477 cursor csr_proc_ass_ppr
478 is
479 select /*+ ORDERED */
480 ppr.person_id,
481 pa.assignment_id,
482 to_date(decode(nvl(g_inc_term_flag,'N'),
483 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
484 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
485 null include_or_exclude
486 from pay_population_ranges ppr,
487 per_periods_of_service ppos,
488 per_assignments_f pa
489 where ppr.payroll_action_id = p_payroll_action_id
490 and ppr.chunk_number = p_chunk_number
491 and ppos.person_id = ppr.person_id
492 and ppos.business_group_id + 0 = g_business_group_id
493 and ((nvl(g_inc_term_flag,'N') = 'N'
494 and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
495 or (nvl(g_inc_term_flag,'N') = 'Y'
496 and ((g_term_date_from is null
497 and g_term_date_to is null)
498 or (ppos.actual_termination_date
499 between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
500 and pa.period_of_service_id = ppos.period_of_service_id
501 and pa.primary_flag = 'Y'
502 and to_date(decode(nvl(g_inc_term_flag,'N'),
503 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
504 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
505 between pa.effective_start_date and pa.effective_end_date
506 and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
507 and (g_organization_id is null
508 or (g_organization_id is not null
509 and nvl(g_inc_org_hier_flag,'Y') = 'Y'
510 and pa.organization_id in (
511 select /*+ ORDERED */
512 distinct pose.organization_id_child
513 from per_organization_structures pos,
514 per_org_structure_versions posv,
515 per_org_structure_elements pose
516 where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
517 and pos.primary_structure_flag = 'Y'
518 and posv.organization_structure_id = pos.organization_structure_id
519 and posv.date_from <= g_effective_date
520 and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
521 and not exists(
522 select null
523 from per_org_structure_versions posv2
524 where posv2.organization_structure_id = pos.organization_structure_id
525 and posv2.date_from <= g_effective_date
526 and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
527 and posv2.version_number > posv.version_number)
528 and pose.org_structure_version_id = posv.org_structure_version_id
529 start with pose.organization_id_parent = g_organization_id
530 connect by prior pose.organization_id_child = pose.organization_id_parent
531 union
532 select hou.organization_id
533 from hr_all_organization_units hou
534 where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
535 and hou.organization_id <> hou.business_group_id
536 union
537 select g_organization_id
538 from dual))
539 or (g_organization_id is not null
540 and nvl(g_inc_org_hier_flag,'Y') = 'N'
541 and pa.organization_id = g_organization_id))
542 order by ppr.person_id;
543 --
544 cursor csr_proc_hasa_ppr
545 is
546 select /*+ ORDERED */
547 ppr.person_id,
548 pa.assignment_id,
549 to_date(decode(nvl(g_inc_term_flag,'N'),
550 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
551 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
552 hasa.include_or_exclude
553 from pay_population_ranges ppr,
554 per_periods_of_service ppos,
555 per_assignments_f pa,
556 hr_assignment_set_amendments hasa
557 where ppr.payroll_action_id = p_payroll_action_id
558 and ppr.chunk_number = p_chunk_number
559 and ppos.person_id = ppr.person_id
560 and ppos.business_group_id + 0 = g_business_group_id
561 and ((nvl(g_inc_term_flag,'N') = 'N'
562 and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
563 or (nvl(g_inc_term_flag,'N') = 'Y'
564 and ((g_term_date_from is null
565 and g_term_date_to is null)
566 or (ppos.actual_termination_date
567 between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
568 and pa.period_of_service_id = ppos.period_of_service_id
569 and pa.primary_flag = 'Y'
570 and to_date(decode(nvl(g_inc_term_flag,'N'),
571 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
572 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
573 between pa.effective_start_date and pa.effective_end_date
574 and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
575 and (g_organization_id is null
576 or (g_organization_id is not null
577 and nvl(g_inc_org_hier_flag,'Y') = 'Y'
578 and pa.organization_id in (
579 select /*+ ORDERED */
580 distinct pose.organization_id_child
581 from per_organization_structures pos,
582 per_org_structure_versions posv,
583 per_org_structure_elements pose
584 where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
585 and pos.primary_structure_flag = 'Y'
586 and posv.organization_structure_id = pos.organization_structure_id
587 and posv.date_from <= g_effective_date
588 and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
589 and not exists(
590 select null
591 from per_org_structure_versions posv2
592 where posv2.organization_structure_id = pos.organization_structure_id
593 and posv2.date_from <= g_effective_date
594 and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
595 and posv2.version_number > posv.version_number)
596 and pose.org_structure_version_id = posv.org_structure_version_id
597 start with pose.organization_id_parent = g_organization_id
598 connect by prior pose.organization_id_child = pose.organization_id_parent
599 union
600 select hou.organization_id
601 from hr_all_organization_units hou
602 where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
603 and hou.organization_id <> hou.business_group_id
604 union
605 select g_organization_id
606 from dual))
607 or (g_organization_id is not null
608 and nvl(g_inc_org_hier_flag,'Y') = 'N'
609 and pa.organization_id = g_organization_id))
610 and to_number(decode(nvl(to_char(g_ass_set_formula_id),'-1'),'-1',
611 decode(nvl(g_ass_set_amendment_type,'X'),'I',hasa.assignment_id,pa.assignment_id),
612 pa.assignment_id)) = pa.assignment_id
613 and hasa.assignment_set_id (+) = g_assignment_set_id
614 and hasa.assignment_id (+) = pa.assignment_id
615 and nvl(hasa.include_or_exclude,'I') <> 'E'
616 order by ppr.person_id;
617 --
618 cursor csr_proc_ass
619 is
620 select /*+ ORDERED */
621 pp.person_id,
622 pa.assignment_id,
623 to_date(decode(nvl(g_inc_term_flag,'N'),
624 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
625 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
626 null include_or_exclude
627 from per_periods_of_service ppos,
628 per_assignments_f pa,
629 per_people_f pp
630 where ppos.person_id
631 between p_start_person_id and p_end_person_id
632 and ppos.business_group_id + 0 = g_business_group_id
633 and ((nvl(g_inc_term_flag,'N') = 'N'
634 and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
635 or (nvl(g_inc_term_flag,'N') = 'Y'
636 and ((g_term_date_from is null
637 and g_term_date_to is null)
638 or (ppos.actual_termination_date
639 between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
640 and pa.period_of_service_id = ppos.period_of_service_id
641 and pa.primary_flag = 'Y'
642 and to_date(decode(nvl(g_inc_term_flag,'N'),
643 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
644 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
645 between pa.effective_start_date and pa.effective_end_date
646 and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
647 and (g_organization_id is null
648 or (g_organization_id is not null
649 and nvl(g_inc_org_hier_flag,'Y') = 'Y'
650 and pa.organization_id in (
651 select /*+ ORDERED */
652 distinct pose.organization_id_child
653 from per_organization_structures pos,
654 per_org_structure_versions posv,
655 per_org_structure_elements pose
656 where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
657 and pos.primary_structure_flag = 'Y'
658 and posv.organization_structure_id = pos.organization_structure_id
659 and posv.date_from <= g_effective_date
660 and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
661 and not exists(
662 select null
663 from per_org_structure_versions posv2
664 where posv2.organization_structure_id = pos.organization_structure_id
665 and posv2.date_from <= g_effective_date
666 and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
667 and posv2.version_number > posv.version_number)
668 and pose.org_structure_version_id = posv.org_structure_version_id
669 start with pose.organization_id_parent = g_organization_id
670 connect by prior pose.organization_id_child = pose.organization_id_parent
671 union
672 select hou.organization_id
673 from hr_all_organization_units hou
674 where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
675 and hou.organization_id <> hou.business_group_id
676 union
677 select g_organization_id
678 from dual))
679 or (g_organization_id is not null
680 and nvl(g_inc_org_hier_flag,'Y') = 'N'
681 and pa.organization_id = g_organization_id))
682 and pp.person_id = pa.person_id
683 and to_date(decode(nvl(g_inc_term_flag,'N'),
684 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
685 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
686 between pp.effective_start_date and pp.effective_end_date
687 order by pp.person_id;
688 --
689 cursor csr_proc_hasa
690 is
691 select /*+ ORDERED */
692 ppos.person_id,
693 pa.assignment_id,
694 to_date(decode(nvl(g_inc_term_flag,'N'),
695 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
696 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
697 hasa.include_or_exclude
698 from per_periods_of_service ppos,
699 per_assignments_f pa,
700 hr_assignment_set_amendments hasa,
701 per_people_f pp
702 where ppos.person_id
703 between p_start_person_id and p_end_person_id
704 and ppos.business_group_id + 0 = g_business_group_id
705 and ((nvl(g_inc_term_flag,'N') = 'N'
706 and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
707 or (nvl(g_inc_term_flag,'N') = 'Y'
708 and ((g_term_date_from is null
709 and g_term_date_to is null)
710 or (ppos.actual_termination_date
711 between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
712 and pa.period_of_service_id = ppos.period_of_service_id
713 and pa.primary_flag = 'Y'
714 and to_date(decode(nvl(g_inc_term_flag,'N'),
715 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
716 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
717 between pa.effective_start_date and pa.effective_end_date
718 and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
719 and (g_organization_id is null
720 or (g_organization_id is not null
721 and nvl(g_inc_org_hier_flag,'Y') = 'Y'
722 and pa.organization_id in (
723 select /*+ ORDERED */
724 distinct pose.organization_id_child
725 from per_organization_structures pos,
726 per_org_structure_versions posv,
727 per_org_structure_elements pose
728 where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
729 and pos.primary_structure_flag = 'Y'
730 and posv.organization_structure_id = pos.organization_structure_id
731 and posv.date_from <= g_effective_date
732 and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
733 and not exists(
734 select null
735 from per_org_structure_versions posv2
736 where posv2.organization_structure_id = pos.organization_structure_id
737 and posv2.date_from <= g_effective_date
738 and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
739 and posv2.version_number > posv.version_number)
740 and pose.org_structure_version_id = posv.org_structure_version_id
741 start with pose.organization_id_parent = g_organization_id
742 connect by prior pose.organization_id_child = pose.organization_id_parent
743 union
744 select hou.organization_id
745 from hr_all_organization_units hou
746 where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
747 and hou.organization_id <> hou.business_group_id
748 union
749 select g_organization_id
750 from dual))
751 or (g_organization_id is not null
752 and nvl(g_inc_org_hier_flag,'Y') = 'N'
753 and pa.organization_id = g_organization_id))
754 and to_number(decode(nvl(to_char(g_ass_set_formula_id),'-1'),'-1',
755 decode(nvl(g_ass_set_amendment_type,'X'),'I',hasa.assignment_id,pa.assignment_id),
756 pa.assignment_id)) = pa.assignment_id
757 and hasa.assignment_set_id (+) = g_assignment_set_id
758 and hasa.assignment_id (+) = pa.assignment_id
759 and nvl(hasa.include_or_exclude,'I') <> 'E'
760 and pp.person_id = pa.person_id
761 and to_date(decode(nvl(g_inc_term_flag,'N'),
762 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
763 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
764 between pp.effective_start_date and pp.effective_end_date
765 order by pp.person_id;
766 --
767 l_csr_proc_ass_ppr csr_proc_ass_ppr%rowtype;
768 l_csr_proc_hasa_ppr csr_proc_hasa_ppr%rowtype;
769 l_csr_proc_ass csr_proc_ass%rowtype;
770 l_csr_proc_hasa csr_proc_hasa%rowtype;
771 --
772 type t_csr_proc_ass_ppr_tbl is table of csr_proc_ass_ppr%rowtype index by binary_integer;
773 type t_csr_proc_hasa_ppr_tbl is table of csr_proc_hasa_ppr%rowtype index by binary_integer;
774 type t_csr_proc_ass_tbl is table of csr_proc_ass%rowtype index by binary_integer;
775 type t_csr_proc_hasa_tbl is table of csr_proc_hasa%rowtype index by binary_integer;
776 --
777 l_csr_proc_ass_ppr_tbl t_csr_proc_ass_ppr_tbl;
778 l_csr_proc_hasa_ppr_tbl t_csr_proc_hasa_ppr_tbl;
779 l_csr_proc_ass_tbl t_csr_proc_ass_tbl;
780 l_csr_proc_hasa_tbl t_csr_proc_hasa_tbl;
781 --
782 l_ass_valid boolean;
783 --
784 l_error_code number;
785 l_error_mesg varchar2(100);
786 l_assact_step number;
787 --
788 begin
789 --
790 if g_debug
791 and g_detail_debug = 'Y' then
792 l_assact_step := 0;
793 hr_utility.set_location(l_proc,0);
794 end if;
795 --
796 -- need set for multiple ranges.
797 init_pact(p_payroll_action_id);
798 --
799 g_ass_tbl.delete;
800 -- no need but for memory release because no set deinitialize_code now
801 g_ass_ind_tbl.delete;
802 g_per_ind_tbl.delete;
803 --
804 if g_debug
805 and g_detail_debug = 'Y' then
806 l_assact_step := 1;
807 hr_utility.set_location(l_proc,10);
808 end if;
809 --
810 if nvl(g_range_person,'N') = 'Y' then
811 --
812 if g_assignment_set_id is not null
813 and g_ass_set_amendment_type is not null
814 and g_ass_set_amendment_type <> 'N' then
815 --
816 if g_debug
817 and g_detail_debug = 'Y' then
818 l_assact_step := 2;
819 end if;
820 --
821 -- #2243411 bulk collect bug fix is available from 9.2
822 open csr_proc_hasa_ppr;
823 loop
824 --
825 fetch csr_proc_hasa_ppr into l_csr_proc_hasa_ppr;
826 exit when csr_proc_hasa_ppr%notfound;
827 --
828 l_ass_tbl_cnt := l_ass_tbl_cnt + 1;
829 g_ass_tbl(l_ass_tbl_cnt).person_id := l_csr_proc_hasa_ppr.person_id;
830 g_ass_tbl(l_ass_tbl_cnt).assignment_id := l_csr_proc_hasa_ppr.assignment_id;
831 g_ass_tbl(l_ass_tbl_cnt).effective_date := l_csr_proc_hasa_ppr.effective_date;
832 g_ass_tbl(l_ass_tbl_cnt).include_or_exclude := l_csr_proc_hasa_ppr.include_or_exclude;
833 --
834 end loop;
835 close csr_proc_hasa_ppr;
836 --
837 --open csr_proc_hasa_ppr;
838 --fetch csr_proc_hasa_ppr bulk collect into l_csr_proc_hasa_ppr_tbl;
839 --close csr_proc_hasa_ppr;
840 --
841 if g_debug
842 and g_detail_debug = 'Y' then
843 l_assact_step := 3;
844 end if;
845 --
846 --if l_csr_proc_hasa_ppr_tbl.count > 0 then
847 ----
848 -- <<loop_hasa_ppr_tbl>>
849 -- for h in 1..l_csr_proc_hasa_ppr_tbl.count loop
850 -- --
851 -- g_ass_tbl(h).person_id := l_csr_proc_hasa_ppr_tbl(h).person_id;
852 -- g_ass_tbl(h).assignment_id := l_csr_proc_hasa_ppr_tbl(h).assignment_id;
853 -- g_ass_tbl(h).effective_date := l_csr_proc_hasa_ppr_tbl(h).effective_date;
854 -- g_ass_tbl(h).include_or_exclude := l_csr_proc_hasa_ppr_tbl(h).include_or_exclude;
855 -- --
856 -- end loop loop_hasa_ppr_tbl;
857 ----
858 --end if;
859 --
860 --if g_debug
861 --and g_detail_debug = 'Y' then
862 -- l_assact_step := 4;
863 -- hr_utility.set_location(l_proc,20);
864 -- hr_utility.trace('l_csr_proc_hasa_ppr_tbl.count : '||to_char(l_csr_proc_hasa_ppr_tbl.count));
865 --end if;
866 --
867 else
868 --
869 if g_debug
870 and g_detail_debug = 'Y' then
871 l_assact_step := 5;
872 end if;
873 --
874 -- #2243411 bulk collect bug fix is available from 9.2
875 open csr_proc_ass_ppr;
876 loop
877 --
878 fetch csr_proc_ass_ppr into l_csr_proc_ass_ppr;
879 exit when csr_proc_ass_ppr%notfound;
880 --
881 l_ass_tbl_cnt := l_ass_tbl_cnt + 1;
882 g_ass_tbl(l_ass_tbl_cnt).person_id := l_csr_proc_ass_ppr.person_id;
883 g_ass_tbl(l_ass_tbl_cnt).assignment_id := l_csr_proc_ass_ppr.assignment_id;
884 g_ass_tbl(l_ass_tbl_cnt).effective_date := l_csr_proc_ass_ppr.effective_date;
885 g_ass_tbl(l_ass_tbl_cnt).include_or_exclude := null;
886 --
887 end loop;
888 close csr_proc_ass_ppr;
889 --
890 --open csr_proc_ass_ppr;
891 --fetch csr_proc_ass_ppr bulk collect into l_csr_proc_ass_ppr_tbl;
892 --close csr_proc_ass_ppr;
893 --
894 if g_debug
895 and g_detail_debug = 'Y' then
896 l_assact_step := 6;
897 end if;
898 --
899 --if l_csr_proc_ass_ppr_tbl.count > 0 then
900 ----
901 -- <<loop_ass_ppr_tbl>>
902 -- for h in 1..l_csr_proc_ass_ppr_tbl.count loop
903 -- --
904 -- g_ass_tbl(h).person_id := l_csr_proc_ass_ppr_tbl(h).person_id;
905 -- g_ass_tbl(h).assignment_id := l_csr_proc_ass_ppr_tbl(h).assignment_id;
906 -- g_ass_tbl(h).effective_date := l_csr_proc_ass_ppr_tbl(h).effective_date;
907 -- g_ass_tbl(h).include_or_exclude := l_csr_proc_ass_ppr_tbl(h).include_or_exclude;
908 -- --
909 -- end loop loop_ass_ppr_tbl;
910 ----
911 --end if;
912 --
913 --if g_debug
914 --and g_detail_debug = 'Y' then
915 -- l_assact_step := 7;
916 -- hr_utility.set_location(l_proc,30);
917 -- hr_utility.trace('l_csr_proc_ass_ppr_tbl.count : '||to_char(l_csr_proc_ass_ppr_tbl.count));
918 --end if;
919 --
920 end if;
921 --
922 else
923 --
924 if g_assignment_set_id is not null
925 and g_ass_set_amendment_type is not null
926 and g_ass_set_amendment_type <> 'N' then
927 --
928 if g_debug
929 and g_detail_debug = 'Y' then
930 l_assact_step := 8;
931 end if;
932 --
933 -- #2243411 bulk collect bug fix is available from 9.2
934 open csr_proc_hasa;
935 loop
936 --
937 fetch csr_proc_hasa into l_csr_proc_hasa;
938 exit when csr_proc_hasa%notfound;
939 --
940 l_ass_tbl_cnt := l_ass_tbl_cnt + 1;
941 g_ass_tbl(l_ass_tbl_cnt).person_id := l_csr_proc_hasa.person_id;
942 g_ass_tbl(l_ass_tbl_cnt).assignment_id := l_csr_proc_hasa.assignment_id;
943 g_ass_tbl(l_ass_tbl_cnt).effective_date := l_csr_proc_hasa.effective_date;
944 g_ass_tbl(l_ass_tbl_cnt).include_or_exclude := l_csr_proc_hasa.include_or_exclude;
945 --
946 end loop;
947 close csr_proc_hasa;
948 --
949 --open csr_proc_hasa;
950 --fetch csr_proc_hasa bulk collect into l_csr_proc_hasa_tbl;
951 --close csr_proc_hasa;
952 --
953 if g_debug
954 and g_detail_debug = 'Y' then
955 l_assact_step := 9;
956 end if;
957 --
958 --if l_csr_proc_hasa_tbl.count > 0 then
959 ----
960 -- <<loop_hasa_tbl>>
961 -- for h in 1..l_csr_proc_hasa_tbl.count loop
962 -- --
963 -- g_ass_tbl(h).person_id := l_csr_proc_hasa_tbl(h).person_id;
964 -- g_ass_tbl(h).assignment_id := l_csr_proc_hasa_tbl(h).assignment_id;
965 -- g_ass_tbl(h).effective_date := l_csr_proc_hasa_tbl(h).effective_date;
966 -- g_ass_tbl(h).include_or_exclude := l_csr_proc_hasa_tbl(h).include_or_exclude;
967 -- --
968 -- end loop loop_hasa_tbl;
969 ----
970 --end if;
971 --
972 --if g_debug
973 --and g_detail_debug = 'Y' then
974 -- l_assact_step := 10;
975 -- hr_utility.set_location(l_proc,40);
976 -- hr_utility.trace('l_csr_proc_hasa_tbl.count : '||to_char(l_csr_proc_hasa_tbl.count));
977 --end if;
978 --
979 else
980 --
981 if g_debug
982 and g_detail_debug = 'Y' then
983 l_assact_step := 11;
984 end if;
985 --
986 -- #2243411 bulk collect bug fix is available from 9.2
987 open csr_proc_ass;
988 loop
989 --
990 fetch csr_proc_ass into l_csr_proc_ass;
991 exit when csr_proc_ass%notfound;
992 --
993 l_ass_tbl_cnt := l_ass_tbl_cnt + 1;
994 g_ass_tbl(l_ass_tbl_cnt).person_id := l_csr_proc_ass.person_id;
995 g_ass_tbl(l_ass_tbl_cnt).assignment_id := l_csr_proc_ass.assignment_id;
996 g_ass_tbl(l_ass_tbl_cnt).effective_date := l_csr_proc_ass.effective_date;
997 g_ass_tbl(l_ass_tbl_cnt).include_or_exclude := null;
998 --
999 end loop;
1000 close csr_proc_ass;
1001 --
1002 --open csr_proc_ass;
1003 --fetch csr_proc_ass bulk collect into l_csr_proc_ass_tbl;
1004 --close csr_proc_ass;
1005 --
1006 if g_debug
1007 and g_detail_debug = 'Y' then
1008 l_assact_step := 12;
1009 end if;
1010 --
1011 --if l_csr_proc_ass_tbl.count > 0 then
1012 ----
1013 -- <<loop_ass_tbl>>
1014 -- for h in 1..l_csr_proc_ass_tbl.count loop
1015 -- --
1016 -- g_ass_tbl(h).person_id := l_csr_proc_ass_tbl(h).person_id;
1017 -- g_ass_tbl(h).assignment_id := l_csr_proc_ass_tbl(h).assignment_id;
1018 -- g_ass_tbl(h).effective_date := l_csr_proc_ass_tbl(h).effective_date;
1019 -- g_ass_tbl(h).include_or_exclude := l_csr_proc_ass_tbl(h).include_or_exclude;
1020 -- --
1021 -- end loop loop_ass_tbl;
1022 ----
1023 --end if;
1024 --
1025 --if g_debug
1026 --and g_detail_debug = 'Y' then
1027 -- l_assact_step := 13;
1028 -- hr_utility.set_location(l_proc,50);
1029 -- hr_utility.trace('l_csr_proc_ass_tbl.count : '||to_char(l_csr_proc_ass_tbl.count));
1030 --end if;
1031 --
1032 end if;
1033 --
1034 end if;
1035 --
1036 if g_debug
1037 and g_detail_debug = 'Y' then
1038 l_assact_step := 14;
1039 hr_utility.set_location(l_proc,60);
1040 hr_utility.trace('g_ass_tbl.count : '||to_char(g_ass_tbl.count));
1041 hr_utility.trace('l_ass_tbl_cnt : '||to_char(l_ass_tbl_cnt));
1042 end if;
1043 --
1044 if g_ass_tbl.count > 0 then
1045 --
1046 if g_debug
1047 and g_detail_debug = 'Y' then
1048 l_assact_step := 15;
1049 end if;
1050 --
1051 <<loop_ass_tbl>>
1052 for i in 1..g_ass_tbl.count loop
1053 --
1054 if (l_person_id is not null
1055 and l_person_id = g_ass_tbl(i).person_id) then
1056 --
1057 l_person_id := g_ass_tbl(i).person_id;
1058 l_assignment_id := g_ass_tbl(i).assignment_id;
1059 l_ass_cnt := l_ass_cnt + 1;
1060 --
1061 else
1062 --
1063 l_person_id := g_ass_tbl(i).person_id;
1064 l_assignment_id := g_ass_tbl(i).assignment_id;
1065 l_ass_cnt := 1;
1066 --
1067 end if;
1068 --
1069 -- override if person_id is same (sort by person_id)
1070 g_per_ind_tbl(l_person_id).person_id := l_person_id;
1071 g_per_ind_tbl(l_person_id).assignment_id := l_assignment_id;
1072 g_per_ind_tbl(l_person_id).ass_cnt := l_ass_cnt;
1073 --
1074 g_ass_ind_tbl(l_assignment_id).person_id := g_ass_tbl(i).person_id;
1075 g_ass_ind_tbl(l_assignment_id).assignment_id := g_ass_tbl(i).assignment_id;
1076 g_ass_ind_tbl(l_assignment_id).effective_date := g_ass_tbl(i).effective_date;
1077 g_ass_ind_tbl(l_assignment_id).include_or_exclude := g_ass_tbl(i).include_or_exclude;
1078 --
1079 end loop loop_ass_tbl;
1080 --
1081 if g_debug
1082 and g_detail_debug = 'Y' then
1083 l_assact_step := 16;
1084 end if;
1085 --
1086 else
1087 --
1088 hr_utility.trace('g_ass_tbl.count is 0 : '||to_char(g_ass_tbl.count));
1089 --
1090 if g_debug
1091 and g_detail_debug = 'Y' then
1092 l_assact_step := 17;
1093 end if;
1094 --
1095 end if;
1096 --
1097 if g_debug
1098 and g_detail_debug = 'Y' then
1099 --
1100 l_assact_step := 18;
1101 hr_utility.set_location(l_proc,70);
1102 hr_utility.trace('p_start_person_id : '||to_char(p_start_person_id));
1103 hr_utility.trace('p_end_person_id : '||to_char(p_end_person_id));
1104 hr_utility.trace('p_chunk_number : '||to_char(p_chunk_number));
1105 --
1106 hr_utility.trace('g_ass_tbl.count : '||to_char(g_ass_tbl.count));
1107 hr_utility.trace('g_per_ind_tbl.count : '||to_char(g_per_ind_tbl.count));
1108 --
1109 if g_ass_tbl.count > 0 then
1110 hr_utility.trace('g_ass_tbl.first : '||to_char(g_ass_tbl.first));
1111 hr_utility.trace('g_ass_tbl.last : '||to_char(g_ass_tbl.last));
1112 hr_utility.trace('g_ass_tbl(g_ass_tbl.first).person_id : '||to_char(g_ass_tbl(g_ass_tbl.first).person_id));
1113 hr_utility.trace('g_ass_tbl(g_ass_tbl.last).person_id : '||to_char(g_ass_tbl(g_ass_tbl.last).person_id));
1114 end if;
1115 --
1116 if g_per_ind_tbl.count > 0 then
1117 hr_utility.trace('g_per_ind_tbl.first : '||to_char(g_per_ind_tbl.first));
1118 hr_utility.trace('g_per_ind_tbl.last : '||to_char(g_per_ind_tbl.last));
1119 hr_utility.trace('g_per_ind_tbl(g_per_ind_tbl.first).person_id : '||to_char(g_per_ind_tbl(g_per_ind_tbl.first).person_id));
1120 hr_utility.trace('g_per_ind_tbl(g_per_ind_tbl.last).person_id : '||to_char(g_per_ind_tbl(g_per_ind_tbl.last).person_id));
1121 end if;
1122 --
1123 end if;
1124 --
1125 if (g_per_ind_tbl.count > 0
1126 and g_ass_tbl.count > 0) then
1127 --
1128 if g_debug
1129 and g_detail_debug = 'Y' then
1130 l_assact_step := 19;
1131 hr_utility.set_location(l_proc,80);
1132 end if;
1133 --
1134 <<loop_per_tbl>>
1135 for j in p_start_person_id..p_end_person_id loop
1136 --
1137 l_person_id := null;
1138 l_ass_id_tbl_cnt := 0;
1139 l_ass_id_tbl.delete;
1140 --
1141 -- check if g_per_ind_tbl exist
1142 begin
1143 --
1144 l_person_id := g_per_ind_tbl(j).person_id;
1145 --
1146 exception
1147 when no_data_found then
1148 --
1149 null;
1150 --
1151 end;
1152 --
1153 if g_debug
1154 and g_detail_debug = 'Y' then
1155 l_assact_step := 20;
1156 end if;
1157 --
1158 -- skip out of range
1159 if l_person_id is not null then
1160 --
1161 if g_debug
1162 and g_detail_debug = 'Y' then
1163 l_assact_step := 21;
1164 end if;
1165 --
1166 -- case same person_id
1167 if g_per_ind_tbl(j).ass_cnt > 1 then
1168 --
1169 if g_debug
1170 and g_detail_debug = 'Y' then
1171 l_assact_step := 22;
1172 end if;
1173 --
1174 <<loop_imp_ass_tbl>>
1175 for s in 1..g_ass_tbl.count loop
1176 --
1177 if g_ass_tbl(s).person_id = g_per_ind_tbl(j).person_id then
1178 --
1179 l_ass_id_tbl_cnt := l_ass_id_tbl_cnt + 1;
1180 l_ass_id_tbl(l_ass_id_tbl_cnt) := g_ass_tbl(s).assignment_id;
1181 --
1182 if l_ass_id_tbl_cnt = g_per_ind_tbl(j).ass_cnt then
1183 exit loop_imp_ass_tbl;
1184 end if;
1185 --
1186 end if;
1187 --
1188 end loop loop_imp_ass_tbl;
1189 --
1190 else
1191 --
1192 if g_debug
1193 and g_detail_debug = 'Y' then
1194 l_assact_step := 23;
1195 end if;
1196 --
1197 l_ass_id_tbl_cnt := l_ass_id_tbl_cnt + 1;
1198 l_ass_id_tbl(l_ass_id_tbl_cnt) := g_per_ind_tbl(j).assignment_id;
1199 --
1200 end if;
1201 --
1202 if g_debug
1203 and g_detail_debug = 'Y' then
1204 l_assact_step := 24;
1205 hr_utility.trace('j person_id : '||to_char(j));
1206 hr_utility.trace('l_ass_id_tbl_cnt : '||to_char(l_ass_id_tbl_cnt));
1207 hr_utility.trace('l_ass_id_tbl.count : '||to_char(l_ass_id_tbl.count));
1208 end if;
1209 --
1210 if l_ass_id_tbl.count > 0 then
1211 --
1212 if g_debug
1213 and g_detail_debug = 'Y' then
1214 l_assact_step := 25;
1215 end if;
1216 --
1217 <<loop_proc_ass_tbl>>
1218 for t in 1..l_ass_id_tbl.count loop
1219 --
1220 l_ass_valid := true;
1221 --
1222 if g_ass_set_formula_id is not null
1223 and g_ass_ind_tbl(l_ass_id_tbl(t)).include_or_exclude is null then
1224 --
1225 if g_debug
1226 and g_detail_debug = 'Y' then
1227 l_assact_step := 26;
1228 end if;
1229 --
1230 l_ass_valid := hr_jp_ast_utility_pkg.formula_validate(
1231 p_formula_id => g_ass_set_formula_id,
1232 p_assignment_id => g_ass_ind_tbl(l_ass_id_tbl(t)).assignment_id,
1233 p_effective_date => g_ass_ind_tbl(l_ass_id_tbl(t)).effective_date,
1234 p_populate_fs => true);
1235 --
1236 end if;
1237 --
1238 if g_debug
1239 and g_detail_debug = 'Y' then
1240 l_assact_step := 27;
1241 end if;
1242 --
1243 if l_ass_valid then
1244 --
1245 if g_debug
1246 and g_detail_debug = 'Y' then
1247 l_assact_step := 28;
1248 end if;
1249 --
1250 select pay_assignment_actions_s.nextval
1251 into l_assignment_action_id
1252 from dual;
1253 --
1254 if g_debug
1255 and g_detail_debug = 'Y' then
1256 l_assact_step := 29;
1257 end if;
1258 --
1259 hr_nonrun_asact.insact(
1260 lockingactid => l_assignment_action_id,
1261 assignid => g_ass_ind_tbl(l_ass_id_tbl(t)).assignment_id,
1262 pactid => p_payroll_action_id,
1263 chunk => p_chunk_number,
1264 greid => null);
1265 --
1266 if g_debug
1267 and g_detail_debug = 'Y' then
1268 l_assact_step := 30;
1269 hr_utility.set_location(l_proc,90);
1270 hr_utility.trace('person_id : '||to_char(g_ass_ind_tbl(l_ass_id_tbl(t)).person_id));
1271 hr_utility.trace('assignment_id : '||to_char(g_ass_ind_tbl(l_ass_id_tbl(t)).assignment_id));
1272 end if;
1273 --
1274 end if;
1275 --
1276 end loop loop_proc_ass_tbl;
1277 --
1278 else
1279 --
1280 if g_debug
1281 and g_detail_debug = 'Y' then
1282 l_assact_step := 31;
1283 end if;
1284 --
1285 hr_utility.trace('l_ass_id_tbl.count is 0 : '||to_char(l_ass_id_tbl.count));
1286 --
1287 end if;
1288 --
1289 if g_debug
1290 and g_detail_debug = 'Y' then
1291 l_assact_step := 32;
1292 end if;
1293 --
1294 end if;
1295 --
1296 if g_debug
1297 and g_detail_debug = 'Y' then
1298 l_assact_step := 33;
1299 end if;
1300 --
1301 end loop loop_per_tbl;
1302 --
1303 if g_debug
1304 and g_detail_debug = 'Y' then
1305 l_assact_step := 34;
1306 end if;
1307 --
1308 else
1309 --
1310 if g_debug
1311 and g_detail_debug = 'Y' then
1312 l_assact_step := 35;
1313 hr_utility.set_location(l_proc,100);
1314 end if;
1315 --
1316 hr_utility.trace('g_per_ind_tbl.count is 0 : '||to_char(g_per_ind_tbl.count));
1317 hr_utility.trace('g_ass_tbl.count is 0 : '||to_char(g_ass_tbl.count));
1318 --
1319 end if;
1320 --
1321 if g_debug
1322 and g_detail_debug = 'Y' then
1323 l_assact_step := 1000;
1324 hr_utility.trace('assact_step : '||to_char(l_assact_step));
1325 hr_utility.set_location(l_proc,1000);
1326 end if;
1327 --
1328 exception
1329 when others then
1330 --
1331 l_error_code := sqlcode;
1332 l_error_mesg := substrb(sqlerrm,1,100);
1333 --
1334 hr_utility.trace('assignment_action_creation error : '||l_error_code||' : '||l_error_mesg);
1335 --
1336 if g_debug
1337 and g_detail_debug = 'Y' then
1338 hr_utility.trace('assact_step : '||to_char(l_assact_step));
1339 end if;
1340 --
1341 raise;
1342 --
1343 end assignment_action_code;
1344 --
1345 -- -------------------------------------------------------------------------
1346 -- archinit
1347 -- -------------------------------------------------------------------------
1348 procedure initialization_code(
1349 p_payroll_action_id in number)
1350 is
1351 --
1352 l_proc varchar2(80) := c_package||'initialization_code';
1353 --
1354 begin
1355 --
1356 if g_debug then
1357 hr_utility.set_location(l_proc,0);
1358 end if;
1359 --
1360 -- invoke for mark for retry.
1361 init_pact(p_payroll_action_id);
1362 --
1363 if g_debug then
1364 --
1365 hr_utility.set_location(l_proc,1000);
1366 --
1367 end if;
1368 --
1369 end initialization_code;
1370 --
1371 -- -------------------------------------------------------------------------
1372 -- init_assact
1373 -- -------------------------------------------------------------------------
1374 --procedure init_assact(
1375 -- p_assignment_action_id in number,
1376 -- p_assignment_id in number)
1377 --is
1378 ----
1379 -- l_proc varchar2(80) := c_package||'init_assact';
1380 ----
1381 --begin
1382 ----
1383 -- if g_debug then
1384 -- hr_utility.set_location(l_proc,0);
1385 -- end if;
1386 -- --
1387 -- if g_debug then
1388 -- hr_utility.set_location(l_proc,1000);
1389 -- end if;
1390 ----
1391 --end init_assact;
1392 --
1393 -- -------------------------------------------------------------------------
1394 -- archive_assact
1395 -- -------------------------------------------------------------------------
1396 procedure archive_assact(
1397 p_assignment_action_id in number,
1398 p_assignment_id in number)
1399 is
1400 --
1401 l_proc varchar2(80) := c_package||'archive_assact';
1402 --
1403 l_person_id number;
1404 l_effective_date_ass date;
1405 l_effective_date_proc date;
1406 --
1407 l_hire_date date;
1408 l_term_type varchar2(1) := 'C'; /* unknown why this is required bug sync with old pkg */
1409 l_term_date date;
1410 l_term_reason varchar2(30);
1411 --l_death_date date;
1412 l_trm_amt number;
1413 --
1414 l_trm_assact_id number;
1415 l_trm_effective_date date;
1416 --
1417 l_emp_hi_num varchar2(60);
1418 l_emp_wp_num varchar2(60);
1419 l_emp_wpf_num varchar2(60);
1420 l_emp_ei_num varchar2(60);
1421 l_emp_hi_qd date;
1422 l_emp_wp_qd date;
1423 l_emp_wpf_qd date;
1424 l_emp_ei_qd date;
1425 --
1426 o_extra_info1 varchar2(240);
1427 o_extra_info2 varchar2(240);
1428 o_extra_info3 varchar2(240);
1429 o_extra_info4 varchar2(240);
1430 o_extra_info5 varchar2(240);
1431 o_extra_info6 varchar2(240);
1432 o_extra_info7 varchar2(240);
1433 o_extra_info8 varchar2(240);
1434 o_extra_info9 varchar2(240);
1435 o_extra_info10 varchar2(240);
1436 o_extra_info11 varchar2(240);
1437 o_extra_info12 varchar2(240);
1438 o_extra_info13 varchar2(240);
1439 o_extra_info14 varchar2(240);
1440 o_extra_info15 varchar2(240);
1441 o_extra_info16 varchar2(240);
1442 o_extra_info17 varchar2(240);
1443 o_extra_info18 varchar2(240);
1444 o_extra_info19 varchar2(240);
1445 o_extra_info20 varchar2(240);
1446 o_extra_info21 varchar2(240);
1447 o_extra_info22 varchar2(240);
1448 o_extra_info23 varchar2(240);
1449 o_extra_info24 varchar2(240);
1450 o_extra_info25 varchar2(240);
1451 o_extra_info26 varchar2(240);
1452 o_extra_info27 varchar2(240);
1453 o_extra_info28 varchar2(240);
1454 o_extra_info29 varchar2(240);
1455 o_extra_info30 varchar2(240);
1456 --
1457 o_action_information_id number;
1458 o_object_version_number number;
1459 --
1460 l_error_code number;
1461 l_error_mesg varchar2(100);
1462 l_archive_step number;
1463 --
1464 cursor csr_emp
1465 is
1466 select /*+ ORDERED */
1467 v.person_id,
1468 v.effective_date_ass,
1469 v.effective_date_proc,
1470 v.employee_number,
1471 v.last_name last_name_kana,
1472 v.first_name first_name_kana,
1473 v.per_information18 last_name_kanji,
1474 v.per_information19 first_name_kanji,
1475 v.date_of_birth,
1476 v.sex,
1477 pac.postal_code,
1478 pac.address_line1 address1,
1479 pac.address_line2 address2,
1480 pac.address_line3 address3,
1481 pac.region_1 address_kana1,
1482 pac.region_2 address_kana2,
1483 pac.region_3 address_kana3,
1484 pac.town_or_city district_code,
1485 pac.country, /* maybe not required */
1486 pj.name job_name,
1487 v.date_start hire_date,
1488 v.date_of_death death_date,
1489 v.actual_termination_date term_date,
1490 v.leaving_reason term_reason,
1491 pphh.phone_number phone_home,
1492 pphm.phone_number phone_mobile,
1493 pphw.phone_number phone_work
1494 from (select /*+ ORDERED */
1495 pp.person_id,
1496 pp.employee_number,
1497 pp.last_name,
1498 pp.first_name,
1499 pp.per_information18,
1500 pp.per_information19,
1501 pp.date_of_birth,
1502 pp.sex,
1503 pp.date_of_death,
1504 pa.job_id,
1505 ppos.date_start,
1506 ppos.actual_termination_date,
1507 ppos.final_process_date,
1508 ppos.leaving_reason,
1509 to_date(decode(nvl(g_inc_term_flag,'N'),
1510 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
1511 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date_ass,
1512 to_date(decode(nvl(g_inc_term_flag,'N'),
1513 'N',to_char(least(nvl(ppos.final_process_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
1514 to_char(nvl(ppos.final_process_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date_proc
1515 from per_all_assignments_f pa,
1516 per_periods_of_service ppos,
1517 per_all_people_f pp
1518 where pa.assignment_id = p_assignment_id
1519 and ppos.period_of_service_id = pa.period_of_service_id
1520 and to_date(decode(nvl(g_inc_term_flag,'N'),
1521 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
1522 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
1523 between pa.effective_start_date and pa.effective_end_date
1524 and pp.person_id = pa.person_id
1525 and to_date(decode(nvl(g_inc_term_flag,'N'),
1526 'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
1527 to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
1528 between pp.effective_start_date and pp.effective_end_date) v,
1529 per_jobs_vl pj,
1530 per_addresses pac,
1531 per_phones pphh,
1532 per_phones pphm,
1533 per_phones pphw
1534 where pj.job_id (+) = v.job_id
1535 and v.effective_date_ass
1536 between pj.date_from (+) and nvl(pj.date_to(+), hr_api.g_eot)
1537 and pac.person_id (+) = v.person_id
1538 and pac.address_type (+) = 'JP_C'
1539 and v.effective_date_ass
1540 between pac.date_from (+) and nvl(pac.date_to(+), hr_api.g_eot)
1541 and pphh.parent_id (+) = v.person_id
1542 and pphh.phone_type (+) = 'H1'
1543 and v.effective_date_ass
1544 between pphh.date_from (+) and nvl(pphh.date_to(+), hr_api.g_eot)
1545 and pphm.parent_id (+) = v.person_id
1546 and pphm.phone_type (+) = 'M'
1547 and v.effective_date_ass
1548 between pphm.date_from (+) and nvl(pphm.date_to(+), hr_api.g_eot)
1549 and pphw.parent_id (+) = v.person_id
1550 and pphw.phone_type (+) = 'W1'
1551 and v.effective_date_ass
1552 between pphw.date_from (+) and nvl(pphw.date_to(+), hr_api.g_eot);
1553 --
1554 cursor csr_trm_assact
1555 is
1556 select /*+ ORDERED */
1557 paa.assignment_action_id,
1558 ppa.effective_date
1559 from pay_assignment_actions paa,
1560 pay_payroll_actions ppa
1561 where paa.assignment_id = p_assignment_id
1562 and paa.action_status = 'C'
1563 and ppa.payroll_action_id = paa.payroll_action_id
1564 and ppa.effective_date <= l_effective_date_proc
1565 and ppa.action_type in ('R','Q')
1566 and nvl(ppa.element_set_id,-1) in (c_trm_ele_set_id,-1)
1567 and decode(ppa.action_type,'R','TRM',nvl(per_jp_empdet_archive_pkg.get_fuzzy_proc_type(paa.assignment_action_id),'X')) = 'TRM'
1568 and not exists(
1569 select /*+ ORDERED */
1570 null
1571 from pay_action_interlocks pai_v,
1572 pay_assignment_actions paa_v,
1573 pay_payroll_actions ppa_v
1574 where pai_v.locked_action_id = paa.assignment_action_id
1575 and paa_v.assignment_action_id = pai_v.locking_action_id
1576 and ppa_v.payroll_action_id = paa.payroll_action_id
1577 and ppa_v.action_type = 'V')
1578 order by paa.action_sequence desc;
1579 --
1580 cursor csr_emp_edc
1581 is
1582 select /*+ ORDERED */
1583 pac.segment8 graduation_date, /* no date validation */
1584 pac.segment3 school_name,
1585 pac.segment4 school_name_kana,
1586 pac.segment5 faculty,
1587 pac.segment6 faculty_kana,
1588 pac.segment7 department
1589 from per_person_analyses ppa,
1590 per_analysis_criteria pac
1591 where ppa.person_id = l_person_id
1592 and nvl(ppa.date_from,l_effective_date_ass) <= l_effective_date_ass
1593 and ppa.id_flex_num = c_jp_pea_flex_num
1594 and pac.analysis_criteria_id = ppa.analysis_criteria_id
1595 and l_effective_date_ass
1596 between nvl(pac.start_date_active,hr_api.g_sot) and nvl(pac.end_date_active,l_effective_date_ass)
1597 and pac.enabled_flag = 'Y';
1598 --
1599 cursor csr_emp_qua
1600 is
1601 select /*+ ORDERED */
1602 pqt.name qualification_type,
1603 pq.title,
1604 pq.grade_attained,
1605 pq.start_date,
1606 pq.end_date,
1607 pq.status,
1608 nvl(pea.establishment,pe.name) establishment,
1609 pq.license_number
1610 from per_qualifications_vl pq,
1611 per_qualification_types_vl pqt,
1612 per_establishment_attendances pea,
1613 per_establishments pe
1614 where pq.person_id = l_person_id
1615 and nvl(pq.start_date,l_effective_date_ass) <= l_effective_date_ass
1616 and pqt.qualification_type_id = pq.qualification_type_id
1617 and pea.attendance_id (+) = pq.attendance_id
1618 and pe.establishment_id (+) = pea.establishment_id;
1619 --
1620 -- sync with old pkg to return eed <= effective_date
1621 cursor csr_emp_ass_ed
1622 is
1623 select /*+ ORDERED */
1624 std_v.assignment_id,
1625 std_v.assignment_number,
1626 std_v.effective_start_date,
1627 etd_v.effective_end_date,
1628 --to_date(decode(sign(hr_api.g_eot - etd_v.effective_end_date),1,to_char(etd_v.effective_end_date,'YYYY/MM/DD'),null),'YYYY/MM/DD') effective_end_date_d,
1629 to_date(decode(sign(l_effective_date_ass - etd_v.effective_end_date),-1,null,to_char(etd_v.effective_end_date,'YYYY/MM/DD')),'YYYY/MM/DD') effective_end_date_d,
1630 std_v.organization_id,
1631 hou.name organization_name,
1632 std_v.job_id,
1633 pj.name job_name,
1634 std_v.position_id,
1635 hap.name position_name,
1636 std_v.grade_id,
1637 pg.name grade_name
1638 from (select rownum std_rownum,
1639 pa_std.assignment_id,
1640 pa_std.assignment_number,
1641 pa_std.organization_id,
1642 pa_std.job_id,
1643 pa_std.position_id,
1644 pa_std.grade_id,
1645 pa_std.effective_start_date,
1646 pa_std.effective_end_date
1647 from per_all_assignments_f pa_std
1648 where pa_std.assignment_id = p_assignment_id
1649 and pa_std.effective_start_date <= l_effective_date_ass
1650 and (exists(
1651 select null
1652 from per_all_assignments_f pa_sp
1653 where pa_sp.assignment_id = pa_std.assignment_id
1654 and pa_sp.effective_start_date <= l_effective_date_ass
1655 and (pa_sp.organization_id <> pa_std.organization_id
1656 or nvl(pa_sp.job_id,-1) <> nvl(pa_std.job_id,-1)
1657 or nvl(pa_sp.position_id,-1) <> nvl(pa_std.position_id,-1)
1658 or nvl(pa_sp.grade_id,-1) <> nvl(pa_std.grade_id,-1))
1659 and (pa_sp.effective_end_date + 1 = pa_std.effective_start_date
1660 or pa_sp.effective_start_date = pa_std.effective_end_date + 1))
1661 or not exists(
1662 select null
1663 from per_all_assignments_f pa_sm
1664 where pa_sm.assignment_id = pa_std.assignment_id
1665 and pa_sm.effective_start_date <= l_effective_date_ass
1666 and pa_sm.effective_start_date < pa_std.effective_start_date))
1667 and not exists(
1668 select null
1669 from per_all_assignments_f pa_se
1670 where pa_se.assignment_id = pa_std.assignment_id
1671 and pa_se.effective_start_date <= l_effective_date_ass
1672 and pa_se.organization_id = pa_std.organization_id
1673 and nvl(pa_se.job_id,-1) = nvl(pa_std.job_id,-1)
1674 and nvl(pa_se.position_id,-1) = nvl(pa_std.position_id,-1)
1675 and nvl(pa_se.grade_id,-1) = nvl(pa_std.grade_id,-1)
1676 and pa_se.effective_end_date = pa_std.effective_start_date -1)
1677 order by pa_std.effective_start_date) std_v,
1678 (select rownum etd_rownum,
1679 pa_etd.assignment_id,
1680 pa_etd.organization_id,
1681 pa_etd.job_id,
1682 pa_etd.position_id,
1683 pa_etd.grade_id,
1684 pa_etd.effective_start_date,
1685 pa_etd.effective_end_date
1686 from per_all_assignments_f pa_etd
1687 where pa_etd.assignment_id = p_assignment_id
1688 and pa_etd.effective_start_date <= l_effective_date_ass
1689 and (exists(
1690 select null
1691 from per_all_assignments_f pa_ep
1692 where pa_ep.assignment_id = pa_etd.assignment_id
1693 and pa_ep.effective_start_date <= l_effective_date_ass
1694 and (pa_ep.organization_id <> pa_etd.organization_id
1695 or nvl(pa_ep.job_id,-1) <> nvl(pa_etd.job_id,-1)
1696 or nvl(pa_ep.position_id,-1) <> nvl(pa_etd.position_id,-1)
1697 or nvl(pa_ep.grade_id,-1) <> nvl(pa_etd.grade_id,-1))
1698 and (pa_ep.effective_end_date + 1 = pa_etd.effective_start_date
1699 or pa_ep.effective_start_date = pa_etd.effective_end_date + 1))
1700 or not exists(
1701 select null
1702 from per_all_assignments_f pa_em
1703 where pa_em.assignment_id = pa_etd.assignment_id
1704 and pa_em.effective_start_date <= l_effective_date_ass
1705 and pa_em.effective_start_date > pa_etd.effective_start_date))
1706 and not exists(
1707 select null
1708 from per_all_assignments_f pa_ee
1709 where pa_ee.assignment_id = pa_etd.assignment_id
1710 and pa_ee.effective_start_date <= l_effective_date_ass
1711 and pa_ee.organization_id = pa_etd.organization_id
1712 and nvl(pa_ee.job_id,-1) = nvl(pa_etd.job_id,-1)
1713 and nvl(pa_ee.position_id,-1) = nvl(pa_etd.position_id,-1)
1714 and nvl(pa_ee.grade_id,-1) = nvl(pa_etd.grade_id,-1)
1715 and pa_ee.effective_start_date = pa_etd.effective_end_date + 1)
1716 order by pa_etd.effective_start_date) etd_v,
1717 hr_organization_units hou,
1718 per_jobs_vl pj,
1719 hr_all_positions_f_vl hap,
1720 per_grades_vl pg
1721 where etd_v.etd_rownum = std_v.std_rownum
1722 and hou.organization_id = std_v.organization_id
1723 and pj.job_id (+) = std_v.job_id
1724 and hap.position_id (+) = std_v.position_id
1725 and pg.grade_id (+) = std_v.grade_id;
1726 --
1727 cursor csr_emp_ass_wr
1728 is
1729 select /*+ ORDERED */
1730 std_v.assignment_id,
1731 std_v.effective_start_date,
1732 etd_v.effective_end_date,
1733 --to_date(decode(sign(hr_api.g_eot - etd_v.effective_end_date),1,to_char(etd_v.effective_end_date,'YYYY/MM/DD'),null),'YYYY/MM/DD') effective_end_date_d,
1734 to_date(decode(sign(l_effective_date_ass - etd_v.effective_end_date),-1,null,to_char(etd_v.effective_end_date,'YYYY/MM/DD')),'YYYY/MM/DD') effective_end_date_d,
1735 std_v.organization_id,
1736 hou.name organization_name,
1737 std_v.job_id,
1738 pj.name job_name,
1739 std_v.position_id,
1740 hap.name position_name
1741 from (select rownum std_rownum,
1742 pa_std.assignment_id,
1743 pa_std.organization_id,
1744 pa_std.job_id,
1745 pa_std.position_id,
1746 pa_std.effective_start_date,
1747 pa_std.effective_end_date
1748 from per_all_assignments_f pa_std
1749 where pa_std.assignment_id = p_assignment_id
1750 and pa_std.effective_start_date <= l_effective_date_ass
1751 and (exists(
1752 select null
1753 from per_all_assignments_f pa_sp
1754 where pa_sp.assignment_id = pa_std.assignment_id
1755 and pa_sp.effective_start_date <= l_effective_date_ass
1756 and (pa_sp.organization_id <> pa_std.organization_id
1757 or nvl(pa_sp.job_id,-1) <> nvl(pa_std.job_id,-1)
1758 or nvl(pa_sp.position_id,-1) <> nvl(pa_std.position_id,-1))
1759 and (pa_sp.effective_end_date + 1 = pa_std.effective_start_date
1760 or pa_sp.effective_start_date = pa_std.effective_end_date + 1))
1761 or not exists(
1762 select null
1763 from per_all_assignments_f pa_sm
1764 where pa_sm.assignment_id = pa_std.assignment_id
1765 and pa_sm.effective_start_date <= l_effective_date_ass
1766 and pa_sm.effective_start_date < pa_std.effective_start_date))
1767 and not exists(
1768 select null
1769 from per_all_assignments_f pa_se
1770 where pa_se.assignment_id = pa_std.assignment_id
1771 and pa_se.effective_start_date <= l_effective_date_ass
1772 and pa_se.organization_id = pa_std.organization_id
1773 and nvl(pa_se.job_id,-1) = nvl(pa_std.job_id,-1)
1774 and nvl(pa_se.position_id,-1) = nvl(pa_std.position_id,-1)
1775 and pa_se.effective_end_date = pa_std.effective_start_date -1)
1776 order by pa_std.effective_start_date) std_v,
1777 (select rownum etd_rownum,
1778 pa_etd.assignment_id,
1779 pa_etd.organization_id,
1780 pa_etd.job_id,
1781 pa_etd.position_id,
1782 pa_etd.effective_start_date,
1783 pa_etd.effective_end_date
1784 from per_all_assignments_f pa_etd
1785 where pa_etd.assignment_id = p_assignment_id
1786 and pa_etd.effective_start_date <= l_effective_date_ass
1787 and (exists(
1788 select null
1789 from per_all_assignments_f pa_ep
1790 where pa_ep.assignment_id = pa_etd.assignment_id
1791 and pa_ep.effective_start_date <= l_effective_date_ass
1792 and (pa_ep.organization_id <> pa_etd.organization_id
1793 or nvl(pa_ep.job_id,-1) <> nvl(pa_etd.job_id,-1)
1794 or nvl(pa_ep.position_id,-1) <> nvl(pa_etd.position_id,-1))
1795 and (pa_ep.effective_end_date + 1 = pa_etd.effective_start_date
1796 or pa_ep.effective_start_date = pa_etd.effective_end_date + 1))
1797 or not exists(
1798 select null
1799 from per_all_assignments_f pa_em
1800 where pa_em.assignment_id = pa_etd.assignment_id
1801 and pa_em.effective_start_date <= l_effective_date_ass
1802 and pa_em.effective_start_date > pa_etd.effective_start_date))
1803 and not exists(
1804 select null
1805 from per_all_assignments_f pa_ee
1806 where pa_ee.assignment_id = pa_etd.assignment_id
1807 and pa_ee.effective_start_date <= l_effective_date_ass
1808 and pa_ee.organization_id = pa_etd.organization_id
1809 and nvl(pa_ee.job_id,-1) = nvl(pa_etd.job_id,-1)
1810 and nvl(pa_ee.position_id,-1) = nvl(pa_etd.position_id,-1)
1811 and pa_ee.effective_start_date = pa_etd.effective_end_date + 1)
1812 order by pa_etd.effective_start_date) etd_v,
1813 hr_organization_units hou,
1814 per_jobs_vl pj,
1815 hr_all_positions_f_vl hap
1816 where etd_v.etd_rownum = std_v.std_rownum
1817 and hou.organization_id = std_v.organization_id
1818 and pj.job_id (+) = std_v.job_id
1819 and hap.position_id (+) = std_v.position_id;
1820 --
1821 -- get organization_name instead of company_name because of rehire
1822 -- requirement is to get organization plus job information
1823 cursor csr_emp_pj_rh
1824 is
1825 select ppos.date_start hire_date,
1826 ppos.actual_termination_date term_date,
1827 pa.effective_start_date,
1828 pa.effective_end_date,
1829 to_date(decode(sign(hr_api.g_eot - pa.effective_end_date),1,to_char(pa.effective_end_date,'YYYY/MM/DD'),null),'YYYY/MM/DD') effective_end_date_d,
1830 pa.organization_id,
1831 hou.name organization_name,
1832 pa.job_id,
1833 pj.name job_name
1834 from per_periods_of_service ppos,
1835 per_all_assignments_f pa,
1836 hr_organization_units hou,
1837 per_jobs_vl pj
1838 where ppos.person_id = l_person_id
1839 and ppos.date_start < l_hire_date
1840 and ppos.actual_termination_date is not null
1841 and pa.period_of_service_id = ppos.period_of_service_id
1842 and ppos.actual_termination_date
1843 between pa.effective_start_date and pa.effective_end_date
1844 and hou.organization_id = pa.organization_id
1845 and pj.job_id (+) = pa.job_id;
1846 --
1847 -- include start_date/end_date null
1848 cursor csr_emp_pj
1849 is
1850 select ppe.employer_name,
1851 ppe.start_date,
1852 ppe.end_date,
1853 ppj.job_name,
1854 ppj.employment_category
1855 from per_previous_employers ppe,
1856 per_previous_jobs ppj
1857 where ppe.person_id = l_person_id
1858 and nvl(ppe.start_date,l_hire_date - 1) < l_hire_date
1859 and ppj.previous_employer_id (+) = ppe.previous_employer_id
1860 and nvl(ppj.start_date,l_hire_date - 1) < l_hire_date
1861 and not exists(
1862 select null
1863 from per_previous_jobs ppj2
1864 where ppj2.previous_employer_id = ppj.previous_employer_id
1865 and ((nvl(ppj2.start_date,l_hire_date) = nvl(ppj.start_date,l_hire_date)
1866 and nvl(ppj2.end_date,l_hire_date) = nvl(ppj.end_date,l_hire_date)
1867 and ppj2.previous_job_id > ppj.previous_job_id)
1868 or (nvl(ppj2.end_date,l_hire_date) > nvl(ppj.end_date,l_hire_date))
1869 or (nvl(ppj2.end_date,l_hire_date) = nvl(ppj.end_date,l_hire_date)
1870 and nvl(ppj2.start_date,l_hire_date) > nvl(ppj.start_date,l_hire_date))));
1871 --
1872 -- this is same routine with pyjpwic.pkb, looks same routine with contact form
1873 -- order by exists for assist archive sequence
1874 -- unknown requirement of age.. age at archive time? report time? (but sync with old pkg)
1875 cursor csr_con
1876 is
1877 select /*+ ORDERED */
1878 pp.last_name last_name_kana,
1879 pp.first_name first_name_kana,
1880 pp.per_information18 last_name_kanji,
1881 pp.per_information19 first_name_kanji,
1882 pp.date_of_birth,
1883 trunc(months_between(nvl(pp.date_of_death,g_effective_date),pp.date_of_birth)/12) age,
1884 pcr.contact_type,
1885 pp.sex,
1886 pcr.primary_contact_flag,
1887 pcr.dependent_flag,
1888 pcr.rltd_per_rsds_w_dsgntr_flag,
1889 fnd_number.canonical_to_number(pcr.cont_information2) sequence,
1890 pcr.cont_information3 household_head,
1891 pcr.cont_information1 si_itax
1892 from per_contact_relationships pcr,
1893 per_all_people_f pp
1894 where pcr.person_id = l_person_id
1895 and l_effective_date_ass
1896 between nvl(pcr.date_start,hr_api.g_sot) and nvl(pcr.date_end,l_effective_date_ass)
1897 and pp.person_id = pcr.contact_person_id
1898 and (l_effective_date_ass
1899 between pp.effective_start_date and pp.effective_end_date
1900 or (pp.effective_start_date = pp.start_date
1901 and not exists(
1902 select null
1903 from per_all_people_f pp2
1904 where pp2.person_id = pp.person_id
1905 and l_effective_date_ass
1906 between pp2.effective_start_date and pp2.effective_end_date)))
1907 order by
1908 decode(pcr.contact_type,'S',1,2),
1909 pp.date_of_birth,
1910 decode(pp.sex,'M',1,'F',2,3),
1911 pp.last_name,
1912 pp.first_name;
1913 --
1914 l_csr_emp csr_emp%rowtype;
1915 l_csr_trm_assact csr_trm_assact%rowtype;
1916 l_csr_emp_edc csr_emp_edc%rowtype;
1917 l_csr_emp_qua csr_emp_qua%rowtype;
1918 l_csr_emp_ass_ed csr_emp_ass_ed%rowtype;
1919 l_csr_emp_ass_wr csr_emp_ass_wr%rowtype;
1920 l_csr_emp_pj_rh csr_emp_pj_rh%rowtype;
1921 l_csr_emp_pj csr_emp_pj%rowtype;
1922 l_csr_con csr_con%rowtype;
1923 --
1924 begin
1925 --
1926 if g_debug then
1927 hr_utility.set_location(l_proc,0);
1928 hr_utility.trace('p_assignment_action_id : '||to_char(p_assignment_action_id));
1929 l_archive_step := 0;
1930 end if;
1931 --
1932 -- ---------------------------------------------
1933 -- get emp information
1934 -- ---------------------------------------------
1935 -- JP_EMPDET_EMP, JP_EMPDET_PHONE
1936 open csr_emp;
1937 fetch csr_emp into l_csr_emp;
1938 close csr_emp;
1939 --
1940 l_person_id := l_csr_emp.person_id;
1941 l_effective_date_ass := l_csr_emp.effective_date_ass;
1942 l_effective_date_proc := l_csr_emp.effective_date_proc;
1943 l_hire_date := l_csr_emp.hire_date;
1944 --
1945 if g_debug
1946 and g_detail_debug = 'Y' then
1947 l_archive_step := 1;
1948 hr_utility.set_location(l_proc,10);
1949 hr_utility.trace('l_person_id : '||to_char(l_person_id));
1950 hr_utility.trace('employee_number : '||l_csr_emp.employee_number);
1951 hr_utility.trace('last_name_kana : '||l_csr_emp.last_name_kana);
1952 hr_utility.trace('first_name_kana : '||l_csr_emp.first_name_kana);
1953 hr_utility.trace('l_effective_date_ass : '||to_char(l_effective_date_ass,'YYYY/MM/DD'));
1954 hr_utility.trace('l_effective_date_proc : '||to_char(l_effective_date_proc,'YYYY/MM/DD'));
1955 hr_utility.trace('l_hire_date : '||to_char(l_hire_date,'YYYY/MM/DD'));
1956 end if;
1957 --
1958 l_emp_hi_num := pay_jp_balance_pkg.get_entry_value_char(c_hi_card_num_iv_id,p_assignment_id,l_effective_date_ass);
1959 l_emp_wp_num := pay_jp_balance_pkg.get_entry_value_char(c_basic_pension_num_iv_id,p_assignment_id,l_effective_date_ass);
1960 l_emp_wpf_num := pay_jp_balance_pkg.get_entry_value_char(c_wpf_members_num_iv_id,p_assignment_id,l_effective_date_ass);
1961 l_emp_ei_num := pay_jp_balance_pkg.get_entry_value_char(c_ei_num_iv_id,p_assignment_id,l_effective_date_ass);
1962 l_emp_hi_qd := pay_jp_balance_pkg.get_entry_value_date(c_hi_qualify_date_iv_id,p_assignment_id,l_effective_date_ass);
1963 l_emp_wp_qd := pay_jp_balance_pkg.get_entry_value_date(c_wp_qualify_date_iv_id,p_assignment_id,l_effective_date_ass);
1964 l_emp_wpf_qd := pay_jp_balance_pkg.get_entry_value_date(c_wpf_qualify_date_iv_id,p_assignment_id,l_effective_date_ass);
1965 l_emp_ei_qd := pay_jp_balance_pkg.get_entry_value_date(c_ei_qualify_date_iv_id,p_assignment_id,l_effective_date_ass);
1966 --
1967 if g_debug
1968 and g_detail_debug = 'Y' then
1969 l_archive_step := 2;
1970 hr_utility.set_location(l_proc,20);
1971 hr_utility.trace('l_emp_hi_num : '||l_emp_hi_num);
1972 hr_utility.trace('l_emp_wp_num : '||l_emp_wp_num);
1973 hr_utility.trace('l_emp_wpf_num : '||l_emp_wpf_num);
1974 hr_utility.trace('l_emp_ei_num : '||l_emp_ei_num);
1975 hr_utility.trace('l_emp_hi_qd : '||to_char(l_emp_hi_qd,'YYYY/MM/DD'));
1976 hr_utility.trace('l_emp_wp_qd : '||to_char(l_emp_wp_qd,'YYYY/MM/DD'));
1977 hr_utility.trace('l_emp_wpf_qd : '||to_char(l_emp_wpf_qd,'YYYY/MM/DD'));
1978 hr_utility.trace('l_emp_ei_qd : '||to_char(l_emp_ei_qd,'YYYY/MM/DD'));
1979 hr_utility.trace('l_csr_emp.term_date : '||to_char(l_csr_emp.term_date,'YYYY/MM/DD'));
1980 hr_utility.trace('g_term_date_to : '||to_char(g_term_date_to,'YYYY/MM/DD'));
1981 hr_utility.trace('g_effective_date : '||to_char(g_effective_date,'YYYY/MM/DD'));
1982 end if;
1983 --
1984 -- exclude future term data
1985 if l_csr_emp.term_date is not null
1986 and l_csr_emp.term_date <= least(nvl(g_term_date_to,g_effective_date),g_effective_date) then
1987 --
1988 if g_debug
1989 and g_detail_debug = 'Y' then
1990 l_archive_step := 3;
1991 end if;
1992 --
1993 l_term_type := 'T';
1994 l_term_date := l_csr_emp.term_date;
1995 l_term_reason := l_csr_emp.term_reason;
1996 -- death date is not used in report, isolated with term data
1997 --l_death_date := l_csr_emp.death_date;
1998 --
1999 open csr_trm_assact;
2000 fetch csr_trm_assact into l_trm_assact_id, l_trm_effective_date;
2001 close csr_trm_assact;
2002 --
2003 if g_debug
2004 and g_detail_debug = 'Y' then
2005 l_archive_step := 4;
2006 hr_utility.trace('l_trm_assact_id : '||to_char(l_trm_assact_id));
2007 end if;
2008 --
2009 if l_trm_assact_id is not null then
2010 --
2011 l_trm_amt := pay_jp_balance_pkg.get_balance_value(c_b_trm_ern_bal_run_db_id,l_trm_assact_id);
2012 --
2013 end if;
2014 --
2015 if g_debug
2016 and g_detail_debug = 'Y' then
2017 l_archive_step := 5;
2018 hr_utility.trace('l_trm_amt : '||to_char(l_trm_amt));
2019 end if;
2020 --
2021 end if;
2022 --
2023 pay_action_information_api.create_action_information(
2024 p_validate => false,
2025 p_action_context_id => p_assignment_action_id,
2026 p_action_context_type => 'AAP',
2027 p_action_information_category => 'JP_EMPDET_EMP',
2028 p_effective_date => g_effective_date,
2029 p_assignment_id => p_assignment_id,
2030 p_action_information1 => substr(l_csr_emp.last_name_kana||' '||l_csr_emp.first_name_kana,1,c_pai_max),
2031 p_action_information2 => substr(l_csr_emp.last_name_kanji||' '||l_csr_emp.first_name_kanji,1,c_pai_max),
2032 p_action_information3 => fnd_date.date_to_canonical(l_csr_emp.date_of_birth),
2033 p_action_information4 => l_csr_emp.sex, /* hr_general.decode_lookup('SEX',l_csr_emp.sex), */
2034 p_action_information5 => l_csr_emp.address1,
2035 p_action_information6 => l_csr_emp.address2,
2036 p_action_information7 => l_csr_emp.address3,
2037 p_action_information8 => l_csr_emp.address_kana1,
2038 p_action_information9 => l_csr_emp.address_kana2,
2039 p_action_information10 => l_csr_emp.address_kana3,
2040 p_action_information11 => l_csr_emp.district_code,
2041 p_action_information12 => l_csr_emp.country, /* maybe not required.. */
2042 p_action_information13 => l_csr_emp.postal_code,
2043 p_action_information14 => fnd_date.date_to_canonical(l_csr_emp.hire_date),
2044 p_action_information15 => l_csr_emp.job_name,
2045 p_action_information16 => fnd_date.date_to_canonical(l_term_date),
2046 p_action_information17 => l_term_reason, /* not sure why this only is code, should convert to decode LEAV_REAS here */
2047 p_action_information18 => fnd_date.date_to_canonical(l_csr_emp.death_date),
2048 p_action_information19 => l_emp_hi_num,
2049 p_action_information20 => l_emp_wp_num,
2050 p_action_information21 => l_emp_wpf_num,
2051 p_action_information22 => l_emp_ei_num,
2052 p_action_information23 => fnd_number.number_to_canonical(l_trm_amt),
2053 p_action_information24 => fnd_date.date_to_canonical(l_trm_effective_date),
2054 p_action_information25 => fnd_date.date_to_canonical(l_emp_hi_qd),
2055 p_action_information26 => fnd_date.date_to_canonical(l_emp_wp_qd),
2056 p_action_information27 => fnd_date.date_to_canonical(l_emp_wpf_qd),
2057 p_action_information28 => fnd_date.date_to_canonical(l_emp_ei_qd),
2058 p_action_information29 => l_term_type,
2059 p_action_information30 => l_csr_emp.employee_number,
2060 p_action_information_id => o_action_information_id,
2061 p_object_version_number => o_object_version_number);
2062 --
2063 if g_debug
2064 and g_detail_debug = 'Y' then
2065 l_archive_step := 6;
2066 end if;
2067 --
2068 pay_action_information_api.create_action_information(
2069 p_validate => false,
2070 p_action_context_id => p_assignment_action_id,
2071 p_action_context_type => 'AAP',
2072 p_action_information_category => 'JP_EMPDET_PHONE',
2073 p_effective_date => g_effective_date,
2074 p_assignment_id => p_assignment_id,
2075 p_action_information1 => l_csr_emp.phone_home,
2076 p_action_information2 => l_csr_emp.phone_mobile,
2077 p_action_information3 => l_csr_emp.phone_work,
2078 p_action_information_id => o_action_information_id,
2079 p_object_version_number => o_object_version_number);
2080 --
2081 if g_debug
2082 and g_detail_debug = 'Y' then
2083 l_archive_step := 7;
2084 end if;
2085 --
2086 -- JP_EMPDET_EDUCATION_DET
2087 open csr_emp_edc;
2088 loop
2089 --
2090 fetch csr_emp_edc into l_csr_emp_edc;
2091 exit when csr_emp_edc%notfound;
2092 --
2093 pay_action_information_api.create_action_information(
2094 p_validate => false,
2095 p_action_context_id => p_assignment_action_id,
2096 p_action_context_type => 'AAP',
2097 p_action_information_category => 'JP_EMPDET_EDUCATION_DET',
2098 p_effective_date => g_effective_date,
2099 p_assignment_id => p_assignment_id,
2100 p_action_information1 => l_csr_emp_edc.school_name,
2101 p_action_information2 => l_csr_emp_edc.school_name_kana,
2102 p_action_information3 => l_csr_emp_edc.faculty,
2103 p_action_information4 => l_csr_emp_edc.faculty_kana,
2104 p_action_information5 => l_csr_emp_edc.department,
2105 p_action_information6 => l_csr_emp_edc.graduation_date, /* no date format */
2106 p_action_information_id => o_action_information_id,
2107 p_object_version_number => o_object_version_number);
2108 --
2109 end loop;
2110 close csr_emp_edc;
2111 --
2112 if g_debug
2113 and g_detail_debug = 'Y' then
2114 l_archive_step := 8;
2115 end if;
2116 --
2117 -- JP_EMPDET_QUALIFICATIONS
2118 open csr_emp_qua;
2119 loop
2120 --
2121 fetch csr_emp_qua into l_csr_emp_qua;
2122 exit when csr_emp_qua%notfound;
2123 --
2124 pay_action_information_api.create_action_information(
2125 p_validate => false,
2126 p_action_context_id => p_assignment_action_id,
2127 p_action_context_type => 'AAP',
2128 p_action_information_category => 'JP_EMPDET_QUALIFICATIONS',
2129 p_effective_date => g_effective_date,
2130 p_assignment_id => p_assignment_id,
2131 p_action_information1 => l_csr_emp_qua.qualification_type,
2132 p_action_information2 => l_csr_emp_qua.title,
2133 p_action_information3 => hr_general.decode_lookup('PER_SUBJECT_STATUSES',l_csr_emp_qua.status), /* l_csr_emp_qua.status, not sure why this only is meaning.. */
2134 p_action_information4 => l_csr_emp_qua.grade_attained,
2135 p_action_information5 => l_csr_emp_qua.establishment,
2136 p_action_information6 => l_csr_emp_qua.license_number, /* no number formant */
2137 p_action_information7 => fnd_date.date_to_canonical(l_csr_emp_qua.start_date),
2138 p_action_information8 => fnd_date.date_to_canonical(l_csr_emp_qua.end_date),
2139 p_action_information_id => o_action_information_id,
2140 p_object_version_number => o_object_version_number);
2141 --
2142 end loop;
2143 close csr_emp_qua;
2144 --
2145 if g_debug
2146 and g_detail_debug = 'Y' then
2147 l_archive_step := 9;
2148 end if;
2149 --
2150 -- ---------------------------------------------
2151 -- get ass information
2152 -- ---------------------------------------------
2153 -- JP_EMPDET_ASSIGNMENTS
2154 -- ass information for employee detail
2155 open csr_emp_ass_ed;
2156 loop
2157 --
2158 fetch csr_emp_ass_ed into l_csr_emp_ass_ed;
2159 exit when csr_emp_ass_ed%notfound;
2160 --
2161 pay_action_information_api.create_action_information(
2162 p_validate => false,
2163 p_action_context_id => p_assignment_action_id,
2164 p_action_context_type => 'AAP',
2165 p_action_information_category => 'JP_EMPDET_ASSIGNMENTS',
2166 p_effective_date => g_effective_date,
2167 p_assignment_id => p_assignment_id,
2168 p_action_information1 => l_csr_emp_ass_ed.organization_name,
2169 p_action_information2 => l_csr_emp_ass_ed.job_name,
2170 p_action_information3 => l_csr_emp_ass_ed.position_name,
2171 p_action_information4 => l_csr_emp_ass_ed.grade_name,
2172 p_action_information5 => fnd_date.date_to_canonical(l_csr_emp_ass_ed.effective_start_date),
2173 p_action_information6 => fnd_date.date_to_canonical(l_csr_emp_ass_ed.effective_end_date_d), /* sync with old pkg, looks report also convert to display date.. */
2174 p_action_information7 => l_csr_emp_ass_ed.assignment_number,
2175 p_action_information_id => o_action_information_id,
2176 p_object_version_number => o_object_version_number);
2177 --
2178 end loop;
2179 close csr_emp_ass_ed;
2180 --
2181 if g_debug
2182 and g_detail_debug = 'Y' then
2183 l_archive_step := 10;
2184 end if;
2185 --
2186 -- JP_EMPDET_JOB
2187 -- ass information for worker register
2188 open csr_emp_ass_wr;
2189 loop
2190 --
2191 fetch csr_emp_ass_wr into l_csr_emp_ass_wr;
2192 exit when csr_emp_ass_wr%notfound;
2193 --
2194 pay_action_information_api.create_action_information(
2195 p_validate => false,
2196 p_action_context_id => p_assignment_action_id,
2197 p_action_context_type => 'AAP',
2198 p_action_information_category => 'JP_EMPDET_JOB',
2199 p_effective_date => g_effective_date,
2200 p_assignment_id => p_assignment_id,
2201 p_action_information1 => l_csr_emp_ass_wr.position_name,
2202 p_action_information2 => l_csr_emp_ass_wr.job_name,
2203 p_action_information3 => fnd_date.date_to_canonical(l_csr_emp_ass_wr.effective_start_date),
2204 p_action_information4 => fnd_date.date_to_canonical(l_csr_emp_ass_wr.effective_end_date_d),
2205 p_action_information5 => l_csr_emp_ass_wr.organization_name,
2206 p_action_information_id => o_action_information_id,
2207 p_object_version_number => o_object_version_number);
2208 --
2209 end loop;
2210 close csr_emp_ass_wr;
2211 --
2212 if g_debug
2213 and g_detail_debug = 'Y' then
2214 l_archive_step := 11;
2215 end if;
2216 --
2217 -- ---------------------------------------------
2218 -- get previous ass information (same company)
2219 -- ---------------------------------------------
2220 -- JP_EMPDET_PREV_JOB, REHIRE
2221 open csr_emp_pj_rh;
2222 loop
2223 --
2224 fetch csr_emp_pj_rh into l_csr_emp_pj_rh;
2225 exit when csr_emp_pj_rh%notfound;
2226 --
2227 pay_action_information_api.create_action_information(
2228 p_validate => false,
2229 p_action_context_id => p_assignment_action_id,
2230 p_action_context_type => 'AAP',
2231 p_action_information_category => 'JP_EMPDET_PREV_JOB',
2232 p_effective_date => g_effective_date,
2233 p_assignment_id => p_assignment_id,
2234 p_action_information1 => l_csr_emp_pj_rh.organization_name,
2235 p_action_information2 => fnd_date.date_to_canonical(l_csr_emp_pj_rh.effective_start_date),
2236 p_action_information3 => fnd_date.date_to_canonical(l_csr_emp_pj_rh.effective_end_date_d),
2237 p_action_information4 => l_csr_emp_pj_rh.job_name,
2238 p_action_information5 => 'REHIRE',
2239 p_action_information_id => o_action_information_id,
2240 p_object_version_number => o_object_version_number);
2241 --
2242 end loop;
2243 close csr_emp_pj_rh;
2244 --
2245 --
2246 if g_debug
2247 and g_detail_debug = 'Y' then
2248 l_archive_step := 12;
2249 end if;
2250 --
2251 -- ---------------------------------------------
2252 -- get previous job information
2253 -- ---------------------------------------------
2254 -- JP_EMPDET_PREV_JOB
2255 open csr_emp_pj;
2256 loop
2257 --
2258 fetch csr_emp_pj into l_csr_emp_pj;
2259 exit when csr_emp_pj%notfound;
2260 --
2261 pay_action_information_api.create_action_information(
2262 p_validate => false,
2263 p_action_context_id => p_assignment_action_id,
2264 p_action_context_type => 'AAP',
2265 p_action_information_category => 'JP_EMPDET_PREV_JOB',
2266 p_effective_date => g_effective_date,
2267 p_assignment_id => p_assignment_id,
2268 p_action_information1 => l_csr_emp_pj.employer_name,
2269 p_action_information2 => fnd_date.date_to_canonical(l_csr_emp_pj.start_date),
2270 p_action_information3 => fnd_date.date_to_canonical(l_csr_emp_pj.end_date),
2271 p_action_information4 => l_csr_emp_pj.job_name,
2272 p_action_information5 => l_csr_emp_pj.employment_category,
2273 p_action_information_id => o_action_information_id,
2274 p_object_version_number => o_object_version_number);
2275 --
2276 end loop;
2277 close csr_emp_pj;
2278 --
2279 if g_debug
2280 and g_detail_debug = 'Y' then
2281 l_archive_step := 13;
2282 end if;
2283 --
2284 -- ---------------------------------------------
2285 -- get extra information
2286 -- ---------------------------------------------
2287 -- JP_EMPDET_CONTACT_INFO
2288 open csr_con;
2289 loop
2290 --
2291 fetch csr_con into l_csr_con;
2292 exit when csr_con%notfound;
2293 --
2294 pay_action_information_api.create_action_information(
2295 p_validate => false,
2296 p_action_context_id => p_assignment_action_id,
2297 p_action_context_type => 'AAP',
2298 p_action_information_category => 'JP_EMPDET_CONTACT_INFO',
2299 p_effective_date => g_effective_date,
2300 p_assignment_id => p_assignment_id,
2301 p_action_information1 => substr(l_csr_con.last_name_kana||' '||l_csr_con.first_name_kana,1,c_pai_max),
2302 p_action_information2 => substr(l_csr_con.last_name_kanji||' '||l_csr_con.first_name_kanji,1,c_pai_max),
2303 p_action_information3 => l_csr_con.contact_type, /* hr_general.decode_lookup('CONTACT',l_csr_con.contact_type), */
2304 p_action_information4 => l_csr_con.sex, /* hr_general.decode_lookup('SEX',l_csr_con.sex), */
2305 p_action_information5 => fnd_date.date_to_canonical(l_csr_con.date_of_birth),
2306 p_action_information6 => fnd_number.number_to_canonical(l_csr_con.age), /* unknown requirement, why age for archive date is stored.. */
2307 p_action_information7 => l_csr_con.primary_contact_flag, /* hr_general.decode_lookup('YES_NO',l_csr_con.primary_contact_flag), */
2308 p_action_information8 => l_csr_con.dependent_flag, /* hr_general.decode_lookup('YES_NO',l_csr_con.dependent_flag), */
2309 p_action_information9 => l_csr_con.rltd_per_rsds_w_dsgntr_flag, /* hr_general.decode_lookup('YES_NO',l_csr_con.rltd_per_rsds_w_dsgntr_flag), */
2310 p_action_information10 => fnd_number.number_to_canonical(l_csr_con.sequence),
2311 p_action_information11 => l_csr_con.household_head, /* hr_general.decode_lookup('YES_NO',l_csr_con.household_head), */
2312 p_action_information12 => l_csr_con.si_itax, /* hr_general.decode_lookup('YES_NO',l_csr_con.si_itax), */
2313 p_action_information_id => o_action_information_id,
2314 p_object_version_number => o_object_version_number);
2315 --
2316 end loop;
2317 close csr_con;
2318 --
2319 if g_debug
2320 and g_detail_debug = 'Y' then
2321 l_archive_step := 14;
2322 end if;
2323 --
2324 -- JP_EMPDET_EXTRA_INFO
2325 if g_ext_proc_name is not null then
2326 --
2327 if g_debug
2328 and g_detail_debug = 'Y' then
2329 l_archive_step := 15;
2330 end if;
2331 --
2332 execute immediate 'begin '||g_ext_proc_name||c_ext_proc_body||'; end;'
2333 using
2334 in p_assignment_id,
2335 in l_effective_date_ass,
2336 out o_extra_info1,
2337 out o_extra_info2,
2338 out o_extra_info3,
2339 out o_extra_info4,
2340 out o_extra_info5,
2341 out o_extra_info6,
2342 out o_extra_info7,
2343 out o_extra_info8,
2344 out o_extra_info9,
2345 out o_extra_info10,
2346 out o_extra_info11,
2347 out o_extra_info12,
2348 out o_extra_info13,
2349 out o_extra_info14,
2350 out o_extra_info15,
2351 out o_extra_info16,
2352 out o_extra_info17,
2353 out o_extra_info18,
2354 out o_extra_info19,
2355 out o_extra_info20,
2356 out o_extra_info21,
2357 out o_extra_info22,
2358 out o_extra_info23,
2359 out o_extra_info24,
2360 out o_extra_info25,
2361 out o_extra_info26,
2362 out o_extra_info27,
2363 out o_extra_info28,
2364 out o_extra_info29,
2365 out o_extra_info30;
2366 --
2367 if g_debug
2368 and g_detail_debug = 'Y' then
2369 l_archive_step := 16;
2370 end if;
2371 --
2372 pay_action_information_api.create_action_information(
2373 p_validate => false,
2374 p_action_context_id => p_assignment_action_id,
2375 p_action_context_type => 'AAP',
2376 p_action_information_category => 'JP_EMPDET_EXTRA_INFO',
2377 p_effective_date => g_effective_date,
2378 p_assignment_id => p_assignment_id,
2379 p_action_information1 => o_extra_info1,
2380 p_action_information2 => o_extra_info2,
2381 p_action_information3 => o_extra_info3,
2382 p_action_information4 => o_extra_info4,
2383 p_action_information5 => o_extra_info5,
2384 p_action_information6 => o_extra_info6,
2385 p_action_information7 => o_extra_info7,
2386 p_action_information8 => o_extra_info8,
2387 p_action_information9 => o_extra_info9,
2388 p_action_information10 => o_extra_info10,
2389 p_action_information11 => o_extra_info11,
2390 p_action_information12 => o_extra_info12,
2391 p_action_information13 => o_extra_info13,
2392 p_action_information14 => o_extra_info14,
2393 p_action_information15 => o_extra_info15,
2394 p_action_information16 => o_extra_info16,
2395 p_action_information17 => o_extra_info17,
2396 p_action_information18 => o_extra_info18,
2397 p_action_information19 => o_extra_info19,
2398 p_action_information20 => o_extra_info20,
2399 p_action_information21 => o_extra_info21,
2400 p_action_information22 => o_extra_info22,
2401 p_action_information23 => o_extra_info23,
2402 p_action_information24 => o_extra_info24,
2403 p_action_information25 => o_extra_info25,
2404 p_action_information26 => o_extra_info26,
2405 p_action_information27 => o_extra_info27,
2406 p_action_information28 => o_extra_info28,
2407 p_action_information29 => o_extra_info29,
2408 p_action_information30 => o_extra_info30,
2409 p_action_information_id => o_action_information_id,
2410 p_object_version_number => o_object_version_number);
2411 --
2412 if g_debug
2413 and g_detail_debug = 'Y' then
2414 l_archive_step := 17;
2415 end if;
2416 --
2417 end if;
2418 --
2419 if g_debug
2420 and g_detail_debug = 'Y' then
2421 l_archive_step := 1000;
2422 hr_utility.trace('archive_step : '||to_char(l_archive_step));
2423 hr_utility.set_location(l_proc,1000);
2424 end if;
2425 --
2426 exception
2427 when others then
2428 --
2429 l_error_code := sqlcode;
2430 l_error_mesg := substrb(sqlerrm,1,100);
2431 --
2432 hr_utility.trace('archive_assact error : '||l_error_code||' : '||l_error_mesg);
2433 --
2434 if g_debug
2435 and g_detail_debug = 'Y' then
2436 hr_utility.trace('archive_step : '||to_char(l_archive_step));
2437 end if;
2438 --
2439 raise;
2440 --
2441 end archive_assact;
2442 --
2443 -- -------------------------------------------------------------------------
2444 -- post_assact
2445 -- -------------------------------------------------------------------------
2446 --procedure post_assact(
2447 -- p_assignment_action_id in number,
2448 -- p_assignment_id in number)
2449 --is
2450 ----
2451 -- l_proc varchar2(80) := c_package||'post_assact';
2452 ----
2453 --begin
2454 ----
2455 -- if g_debug then
2456 -- hr_utility.set_location(l_proc,0);
2457 -- end if;
2458 ----
2459 ----
2460 -- if g_debug then
2461 -- hr_utility.set_location(l_proc,1000);
2462 -- end if;
2463 ----
2464 --end post_assact;
2465 --
2466 -- -------------------------------------------------------------------------
2467 -- archive_data
2468 -- -------------------------------------------------------------------------
2469 procedure archive_code(
2470 p_assignment_action_id in number,
2471 p_effective_date in date)
2472 is
2473 --
2474 l_proc varchar2(80) := c_package||'archive_code';
2475 --
2476 l_assignment_id number;
2477 --
2478 begin
2479 --
2480 if g_debug then
2481 hr_utility.set_location(l_proc,0);
2482 end if;
2483 --
2484 select assignment_id
2485 into l_assignment_id
2486 from pay_assignment_actions
2487 where assignment_action_id = p_assignment_action_id;
2488 --
2489 if g_debug then
2490 hr_utility.set_location(l_proc,10);
2491 hr_utility.trace('archive_data p_assignment_action_id : '||to_char(p_assignment_action_id));
2492 hr_utility.trace('archive_data l_assignment_id : '||to_char(l_assignment_id));
2493 end if;
2494 --
2495 --init_assact(
2496 -- p_assignment_action_id => p_assignment_action_id,
2497 -- p_assignment_id => l_assignment_id);
2498 --
2499 --if g_debug then
2500 -- hr_utility.set_location(l_proc,20);
2501 --end if;
2502 --
2503 archive_assact(
2504 p_assignment_action_id => p_assignment_action_id,
2505 p_assignment_id => l_assignment_id);
2506 --
2507 if g_debug then
2508 hr_utility.set_location(l_proc,20);
2509 end if;
2510 --
2511 --post_assact(
2512 -- p_assignment_action_id => p_assignment_action_id,
2513 -- p_assignment_id => l_assignment_id);
2514 --
2515 if g_debug then
2516 hr_utility.set_location(l_proc,1000);
2517 end if;
2518 --
2519 end archive_code;
2520 --
2521 -- -------------------------------------------------------------------------
2522 -- deinitialize_code
2523 -- -------------------------------------------------------------------------
2524 --procedure deinitialize_code(
2525 -- p_payroll_action_id in number)
2526 --is
2527 ----
2528 -- l_proc varchar2(80) := c_package||'deinitialize_code';
2529 ----
2530 --begin
2531 ----
2532 -- if g_debug then
2533 -- hr_utility.set_location(l_proc,0);
2534 -- end if;
2535 ----
2536 ---- archive_pact(p_payroll_action_id);
2537 ----
2538 -- g_ass_tbl.delete;
2539 -- g_ass_ind_tbl.delete;
2540 -- g_per_ind_tbl.delete;
2541 ----
2542 -- if g_debug then
2543 -- hr_utility.set_location(l_proc,1000);
2544 -- end if;
2545 ----
2546 --end deinitialize_code;
2547 --
2548 end per_jp_empdet_archive_pkg;