[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_SEP_ARCHIVE_PKG
Source
1 package body pay_kr_sep_archive_pkg as
2 /* $Header: pykrsepa.pkb 120.6.12020000.3 2013/02/12 12:12:25 mdubasi ship $ */
3 --
4 -- Constants
5 --
6 g_debug boolean;
7
8 c_range_cursor constant varchar2(3000) := -- 4660184
9 'select distinct paa.person_id
10 from pay_payroll_actions bppa,
11 per_assignments_f paa
12 where bppa.payroll_action_id = :payroll_action_id
13 and paa.business_group_id = bppa.business_group_id
14 and paa.payroll_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(bppa.payroll_action_id, ''PAYROLL_ID'', null))
15 order by 1';
16 -- declared for archiving details related to efile
17
18 TYPE t_arch_rec IS RECORD(item varchar2(50)
19 ,value varchar2(1000));
20 TYPE t_arch_tab IS TABLE OF t_arch_rec INDEX BY BINARY_INTEGER;
21
22 l_arch_tab t_arch_tab;
23
24 p_element_entry_id pay_element_entries.element_entry_id%type;
25
26
27 TYPE t_ele_ent_id_tab IS TABLE OF pay_element_entries.element_entry_id%type INDEX BY BINARY_INTEGER;
28
29 TYPE t_ele_value_tab IS TABLE OF pay_element_entry_values_f.screen_entry_value%type INDEX BY BINARY_INTEGER;
30
31 l_ele_ent_id_tab t_ele_ent_id_tab;
32 l_ele_value_tab t_ele_value_tab;
33 l_ele_ent_id_tab1 t_ele_ent_id_tab;
34 l_ele_value_tab1 t_ele_value_tab;
35 l_ele_ent_id_tab2 t_ele_ent_id_tab;
36 l_ele_value_tab2 t_ele_value_tab;
37 l_ele_ent_id_tab3 t_ele_ent_id_tab;
38
39 -- end of declared for archiving details related to efile
40
41 --------------------------------------------------------------------------------+
42
43 procedure archive_item
44 ( p_item in ff_user_entities.user_entity_name%type,
45 p_context1 in pay_assignment_actions.assignment_action_id%type,
46 p_context2 in pay_element_entries_f.element_entry_id%type,
47 p_value in out NOCOPY ff_archive_items.value%type) is
48 cursor get_user_entity_id(c_user_entity_name in varchar2)
49 is
50 select fue.user_entity_id,
51 dbi.data_type
52 from ff_user_entities fue,
53 ff_database_items dbi
54 where user_entity_name =c_user_entity_name
55 and fue.user_entity_id =dbi.user_entity_id;
56
57 v_user_entity_id ff_user_entities.user_entity_id%type;
58 v_archive_item_id ff_archive_items.archive_item_id%type;
59 v_data_type ff_database_items.data_type%type;
60 v_object_version_number ff_archive_items.object_version_number%type;
61 v_some_warning boolean;
62
63
64 begin
65
66 if g_debug then
67 hr_utility.set_location('Entering : archive_item',1);
68 end if;
69
70 open get_user_entity_id (p_item);
71 fetch get_user_entity_id into v_user_Entity_id,
72 v_data_type;
73 if get_user_entity_id%found then
74 close get_user_entity_id;
75 if substr(P_ITEM,1,9)='X_KR_PREV' then
76 ff_archive_api.create_archive_item
77 (p_validate => false -- boolean in default
78 ,p_archive_item_id => v_archive_item_id -- number out
79 ,p_user_entity_id => v_user_entity_id -- number in
80 ,p_archive_value => p_value -- varchar2 in
81 ,p_archive_type => 'AAP' -- varchar2 in default
82 ,p_action_id => p_context1 -- number in
83 ,p_legislation_code => 'KR' -- varchar2 in
84 ,p_object_version_number => v_object_version_number -- number out
85 ,p_context_name1 => 'ELEMENT_ENTRY_ID' -- varchar2 in default
86 ,p_context1 => p_context2 -- varchar2 in default
87 ,p_some_warning => v_some_warning); -- boolean out
88 else
89 ff_archive_api.create_archive_item
90 (p_validate => false -- boolean in default
91 ,p_archive_item_id => v_archive_item_id -- number out
92 ,p_user_entity_id => v_user_entity_id -- number in
93 ,p_archive_value => p_value -- varchar2 in
94 ,p_archive_type => 'AAP' -- varchar2 in default
95 ,p_action_id => p_context1 -- number in
96 ,p_legislation_code => 'KR' -- varchar2 in
97 ,p_object_version_number => v_object_version_number -- number out
98 ,p_some_warning => v_some_warning); -- boolean out
99 end if;
100 else
101 close get_user_entity_id;
102 if g_debug then
103 hr_utility.set_location('User entity not found :'||p_item,20);
104 end if;
105 end if;
106
107 if g_debug then
108 hr_utility.set_location('Leaving : archive_item',1);
109 end if;
110
111 exception
112 when others then
113
114 if get_user_entity_id%isopen then
115 close get_user_entity_id;
116 hr_utility.set_location('closing..',117);
117 end if;
118
119 hr_utility.set_location('Error in archive_item',20);
120 raise;
121 end archive_item;
122
123 --------------------------------------------------------------------------------+
124
125 ------------------------------------------------------------------------------+
126 Procedure archive_corp_details
127 (p_business_group_id in hr_organization_units.business_group_id%type,
128 p_tax_unit_id in pay_assignment_Actions.tax_unit_id%type,
129 p_assignment_id in pay_assignment_actions.assignment_id%type,
130 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
131 p_assignment_Action_id in pay_assignment_actions.assignment_action_id%type,
132 p_date_earned in date ) is
133
134
135 ------------------------------------------------------------------------------+
136 -- cursor to get corp details
137 ------------------------------------------------------------------------------+
138
139 /* Start of corp details */
140
141 cursor c_corp_details
142 is
143 select ihoi.org_information10 corp_tel_number
144 ,choi.org_information2 corp_number
145 ,choi.org_information1 corp_name
146 ,choi.org_information7 corp_rep_ni
147 ,choi.org_information6 corp_rep_name
148 ,bhoi.org_information1 bp_name
149 ,bhoi.org_information2 bp_number
150 ,bhoi.org_information11 bp_rep_name
151 ,bhoi.org_information12 bp_rep_ni
152 ,ihoi.org_information9 bp_tax_office_code
153 from hr_organization_information bhoi
154 ,hr_organization_information ihoi
155 ,hr_organization_information choi
156 where bhoi.organization_id = p_tax_unit_id
157 and bhoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
158 and choi.organization_id = to_number(bhoi.org_information10)
159 and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
160 and ihoi.organization_id = bhoi.organization_id
161 and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE' ;
162
163 Begin
164
165 -----------------------------------------
166 -- note : the fetch order from the cursor
167 -- should be same as the order
168 -- defined in the pl/sql table below
169 -----------------------------------------
170 l_arch_tab.delete;
171 l_arch_tab(1).item := 'X_KR_CORP_TEL_NUMBER' ;
172 l_arch_tab(2).item := 'X_KR_CORP_NUMBER' ;
173 l_arch_tab(3).item := 'X_KR_CORP_NAME';
174 l_arch_tab(4).item := 'X_KR_CORP_REP_NI' ;
175 l_arch_tab(5).item := 'X_KR_CORP_REP_NAME' ;
176 l_arch_tab(6).item := 'X_KR_BP_NAME';
177 l_arch_tab(7).item := 'X_KR_BP_NUMBER' ;
178 l_arch_tab(8).item := 'X_KR_BP_REP_NAME' ;
179 l_arch_tab(9).item := 'X_KR_BP_REP_NI' ;
180 l_arch_tab(10).item := 'X_KR_BP_TAX_OFFICE_CODE' ;
181
182 if g_debug then
183 hr_utility.set_location('Entering : Archiving corp Details ',1);
184 hr_utility.set_location('Assignments action id is '||p_assignment_action_id,2);
185 end if;
186
187 open c_corp_details ;
188 fetch c_corp_details
189 into l_arch_tab(1).value,
190 l_arch_tab(2).value,
191 l_arch_tab(3).value,
192 l_arch_tab(4).value,
193 l_arch_tab(5).value,
194 l_arch_tab(6).value,
195 l_arch_tab(7).value,
196 l_arch_tab(8).value,
197 l_arch_tab(9).value,
198 l_arch_tab(10).value;
199
200 close c_corp_details ;
201
202 if g_debug then
203 hr_utility.set_location('Creating Archive Item ',3);
204 end if;
205
206 for i in 1..l_arch_tab.count loop
207
208 archive_item (p_item => l_arch_tab(i).item
209 ,p_context1 => p_assignment_action_id
210 ,p_context2 => null
211 ,p_value => l_arch_tab(i).value);
212 end loop;
213
214
215 if g_debug then
216 hr_utility.set_location('Exiting : Archiving corp Details ',4);
217 end if;
218
219 exception
220 when others then
221 hr_utility.set_location('Error in archiving corp details ',10);
222 raise;
223
224 End archive_corp_details;
225
226 /* End of corp details */
227
228 -------------------------------------------------------------------------------+
229
230 -------------------------------------------------------------------------------+
231 Function archive_emp_details
232 (p_business_group_id in hr_organization_units.business_group_id%type,
233 p_tax_unit_id in pay_assignment_Actions.tax_unit_id%type,
234 p_assignment_id in pay_assignment_actions.assignment_id%type,
235 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
236 p_assignment_Action_id in pay_assignment_actions.assignment_action_id%type,
237 p_date_earned in date )
238 return boolean
239 is
240
241 /* Start of current employee details */
242
243 /*
244 Bug 4442482: Sparse Matrix enhancement - Use function PAY_KR_REPORT_PKG.GET_RESULT_VALUE in SELECT to make
245 query return row even when any one of these run result values is non-existent (null).
246 */
247 cursor c_cemp_details
248 is
249 select
250 pap.last_name||first_name emp_name
251 ,pap.nationality nationality
252 ,pap.national_identifier ni
253 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv1.input_value_id) hire_date
254 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv2.input_value_id) leaving_date
255 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv3.input_value_id) prev_hire_date
256 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv4.input_value_id) prev_leaving_date
257 ,fnd_date.date_to_canonical(ppa.date_earned) date_earned
258 ,ft.territory_code country_code
259 from pay_run_results prr1,
260 pay_run_results prr2,
261 pay_payroll_actions ppa,
262 pay_assignment_actions paa,
263 pay_element_types_f pet1,
264 pay_element_types_f pet2,
265 pay_input_values_f piv1,
266 pay_input_values_f piv2,
267 pay_input_values_f piv3,
268 pay_input_values_f piv4,
269 per_people_f pap,
270 per_assignments_f paaf,
271 pay_run_types_f prt,
272 fnd_territories ft,
273 per_addresses adr
274 where ppa.payroll_action_id = p_payroll_action_id
275 and ppa.business_group_id = p_business_group_id
276 and paa.assignment_id = p_assignment_id
277 and ppa.payroll_action_id = paa.payroll_action_id
278 and prr1.assignment_action_id = paa.assignment_action_id
279 and prr1.element_type_id = pet1.element_type_id
280 and pet1.element_name = 'WKPD'
281 and pet1.legislation_code = 'KR'
282 and pet1.element_type_id = piv1.element_type_id
283 and pet1.element_type_id = piv2.element_type_id
284 and pet1.element_type_id = piv3.element_type_id
285 and pet1.element_type_id = piv4.element_type_id
286 and piv1.name = 'H_DATE'
287 and piv2.name = 'L_DATE'
288 and piv3.name = 'PREV_FH_DATE'
289 and piv4.name = 'PREV_LL_DATE'
290 and ppa.effective_date between pet1.effective_start_date and pet1.effective_end_date
291 and ppa.effective_date between piv1.effective_start_date and piv1.effective_end_date
292 and ppa.effective_date between piv2.effective_start_date and piv2.effective_end_date
293 and ppa.effective_date between piv3.effective_start_date and piv3.effective_end_date
294 and ppa.effective_date between piv4.effective_start_date and piv4.effective_end_date
295 and pet2.element_name = 'TAX'
296 and pet2.legislation_code = 'KR'
297 and prr2.element_type_id = pet2.element_type_id
298 and prr2.source_type = 'E'
299 and prr2.assignment_action_id = paa.assignment_action_id
300 and ppa.effective_date between pet2.effective_start_date and pet2.effective_end_date
301 and prt.run_type_name in ('SEP','SEP_I')
302 and prt.run_type_id = ppa.run_type_id
303 and paaf.assignment_id = paa.assignment_id
304 and pap.person_id = paaf.person_id
305 and adr.person_id(+) = pap.person_id
306 and adr.style(+) = 'KR'
307 and adr.address_type(+) = 'KR_C'
308 and adr.country = ft.territory_code(+)
309 and ppa.effective_date between pap.effective_start_date and pap.effective_end_date
310 and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date;
311
312 -- 3627111
313 /*********************************************************
314 * Cursor to get the Non-Statutroy Hire date, Leaving
315 * date, Prev Employer Hire date and Prev Employer
316 * Leaving date of an employee.
317 *********************************************************/
318 /*
319 Bug 4442482: Sparse Matrix enhancement - Use function PAY_KR_REPORT_PKG.GET_RESULT_VALUE in SELECT
320 to make query return row even when any one of these run result values is non-existent
321 (null).
322 */
323
324 cursor c_emp_nonstat_details
325 is
326 select
327 pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv1.input_value_id) ns_hire_date
328 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv2.input_value_id) ns_leaving_date
329 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv3.input_value_id) ns_prev_hire_date
330 ,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv4.input_value_id) ns_prev_leaving_date
331 from pay_run_results prr1,
332 pay_payroll_actions ppa,
333 pay_assignment_actions paa,
334 pay_element_types_f pet1,
335 pay_input_values_f piv1,
336 pay_input_values_f piv2,
337 pay_input_values_f piv3,
338 pay_input_values_f piv4,
339 pay_run_types_f prt
340 where ppa.payroll_action_id = p_payroll_action_id
341 and ppa.business_group_id = p_business_group_id
342 and paa.assignment_id = p_assignment_id
343 and ppa.payroll_action_id = paa.payroll_action_id
344 and prt.run_type_name in ('SEP','SEP_I')
345 and prt.run_type_id = ppa.run_type_id
346 and prr1.assignment_action_id = paa.assignment_action_id
347 and prr1.element_type_id = pet1.element_type_id
348 and pet1.element_name = 'WKPD_NON_STAT_SEP_PAY'
349 and pet1.legislation_code = 'KR'
350 and pet1.element_type_id = piv1.element_type_id
351 and pet1.element_type_id = piv2.element_type_id
352 and pet1.element_type_id = piv3.element_type_id
353 and pet1.element_type_id = piv4.element_type_id
354 and piv1.name = 'H_DATE'
355 and piv2.name = 'L_DATE'
356 and piv3.name = 'PREV_FH_DATE'
357 and piv4.name = 'PREV_LL_DATE'
358 and ppa.effective_date between pet1.effective_start_date and pet1.effective_end_date
359 and ppa.effective_date between piv1.effective_start_date and piv1.effective_end_date
360 and ppa.effective_date between piv2.effective_start_date and piv2.effective_end_date
361 and ppa.effective_date between piv3.effective_start_date and piv3.effective_end_date
362 and ppa.effective_date between piv4.effective_start_date and piv4.effective_end_date;
363
364
365 Begin
366
367 -----------------------------------------
368 -- note : the fetch order from the cursor
369 -- should be same as the order
370 -- defined in the pl/sql table below
371 -----------------------------------------
372 l_arch_tab.delete;
373
374 l_arch_tab(1).item := 'X_KR_EMP_NAME' ;
375 l_arch_tab(2).item := 'X_KR_EMP_NATIONALITY' ;
376 l_arch_tab(3).item := 'X_KR_EMP_NI';
377 l_arch_tab(4).item := 'X_KR_EMP_HIRE_DATE' ;
378 l_arch_tab(5).item := 'X_KR_EMP_LEAVING_DATE' ;
379 l_arch_tab(6).item := 'X_KR_EMP_PREV_HIRE_DATE' ;
380 l_arch_tab(7).item := 'X_KR_EMP_PREV_LEAVING_DATE' ;
381 l_arch_tab(8).item := 'X_KR_EMP_PAID_DATE' ;
382 l_arch_tab(9).item := 'X_KR_EMP_COUNTRY_CODE' ;
383
384 if g_debug then
385 hr_utility.set_location('Entering : Archiving current emp Details ',1);
386 hr_utility.set_location('Assignment action id is '||p_assignment_action_id,2);
387 end if;
388
389 open c_cemp_details ;
390 fetch c_cemp_details
391 into l_arch_tab(1).value,
392 l_arch_tab(2).value,
393 l_arch_tab(3).value,
394 l_arch_tab(4).value,
395 l_arch_tab(5).value,
396 l_arch_tab(6).value,
397 l_arch_tab(7).value,
398 l_arch_tab(8).value,
399 l_arch_tab(9).value;
400
401 if c_cemp_details%NOTFOUND then
402 close c_cemp_details ;
403 return(FALSE);
404 else
405 if g_debug then
406 hr_utility.set_location('Creating Archive Item ',3);
407 end if;
408
409 for i in 1..l_arch_tab.count loop
410
411 archive_item (p_item => l_arch_tab(i).item
412 ,p_context1 => p_assignment_action_id
413 ,p_context2 => null
414 ,p_value => l_arch_tab(i).value);
415 end loop;
416 close c_cemp_details ;
417 end if;
418
419 -- 3627111: Archive Non-statutory Details
420 l_arch_tab.delete;
421 l_arch_tab(1).item := 'X_WKPD_NON_STAT_SEP_PAY_H_DATE' ;
422 l_arch_tab(2).item := 'X_WKPD_NON_STAT_SEP_PAY_L_DATE' ;
423 l_arch_tab(3).item := 'X_WKPD_NON_STAT_SEP_PAY_PREV_H_DATE' ;
424 l_arch_tab(4).item := 'X_WKPD_NON_STAT_SEP_PAY_PREV_L_DATE' ;
425
426 -- 3627111: Fetching Non-Statutory Data from cursor
427 open c_emp_nonstat_details;
428 fetch c_emp_nonstat_details into
429 l_arch_tab(1).value,
430 l_arch_tab(2).value,
431 l_arch_tab(3).value,
432 l_arch_tab(4).value;
433 if c_emp_nonstat_details%NOTFOUND then
434 close c_emp_nonstat_details;
435 -- 3627111: False will not be returned from here because, these Non-Statutory data
436 -- are optional.
437 else
438 for i in 1..l_arch_tab.count loop
439
440 archive_item (p_item => l_arch_tab(i).item
441 ,p_context1 => p_assignment_action_id
442 ,p_context2 => null
443 ,p_value => l_arch_tab(i).value);
444 end loop;
445 close c_emp_nonstat_details ;
446
447 end if;
448
449 if g_debug then
450 hr_utility.set_location('Exiting :Archiving current emp Details',200);
451 end if;
452 return(true);
453
454 exception
455 when others then
456 hr_utility.set_location('Error in archiving emp details ',10);
457 raise;
458
459 End archive_emp_details;
460
461 /* End of current employee details */
462
463 -------------------------------------------------------------------------------+
464
465 -------------------------------------------------------------------------------+
466
467 Procedure archive_prev_emp_details
468 (p_business_group_id in hr_organization_units.business_group_id%type,
469 p_tax_unit_id in pay_assignment_Actions.tax_unit_id%type,
470 p_assignment_id in pay_assignment_actions.assignment_id%type,
471 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
472 p_assignment_Action_id in pay_assignment_actions.assignment_action_id%type,
473 p_date_earned in date ) is
474
475 cursor c_prev_emp_details(v_element_name varchar2,v_piv_name varchar2)
476 is
477 select pee.element_entry_id,
478 peev1.screen_entry_value
479 from pay_element_entries_f pee,
480 pay_element_links_f pel,
481 pay_element_types_f pet,
482 pay_payroll_actions ppa,
483 pay_assignment_actions paa,
484 pay_run_types_f prt,
485 pay_input_values_f piv1,
486 pay_element_entry_values_f peev1
487 where pet.element_name = v_element_name
488 and pel.element_link_id = pee.element_link_id
489 and pet.element_type_id = pel.element_type_id
490 and pel.business_group_id = ppa.business_group_id --new
491 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
492 and paa.action_status = 'C'
493 and ppa.payroll_action_id = paa.payroll_action_id
494 and prt.run_type_id = paa.run_type_id
495 and prt.run_type_name in ('SEP','SEP_I')
496 and ppa.effective_date between prt.effective_start_date and prt.effective_end_date
497 and pet.legislation_code = 'KR'
498 and pee.assignment_id = paa.assignment_id
499 and ppa.date_earned between nvl(pee.effective_start_date,ppa.date_earned)
500 and nvl(pee.effective_end_date,ppa.date_earned)
501 and pee.entry_type = 'E'
502 and pee.element_link_id = pel.element_link_id
503 and piv1.name = v_piv_name --- cursor parameter
504 and piv1.element_type_id = pet.element_type_id
505 and peev1.element_entry_id = pee.element_entry_id
506 and peev1.input_value_id = piv1.input_value_id
507 and ppa.effective_date between piv1.effective_start_date and piv1.effective_end_date
508 and paa.assignment_id = p_assignment_id
509 and ppa.payroll_action_id = p_payroll_action_id
510 and ppa.business_group_id = p_business_group_id
511 and peev1.screen_entry_value is not null; -- Bug# 2826658 Added not to archive null values
512
513 l_iv_name pay_input_values_f.name%TYPE;
514 l_item VARCHAR2(25);
515 l_prepaid_value NUMBER;
516
517 procedure populate_archive_item(p_item varchar2,p_element_name varchar2, p_piv_name varchar2) is
518
519
520 begin
521
522 if g_debug then
523 hr_utility.set_location('Entering : Populate archive item ',1);
524 hr_utility.set_location('Assignments action id is '||p_assignment_action_id,2);
525 end if;
526
527 l_ele_ent_id_tab.delete;
528 l_ele_value_tab.delete;
529
530 open c_prev_emp_details(p_element_name,p_piv_name);
531 fetch c_prev_emp_details bulk collect
532 into
533 l_ele_ent_id_tab,
534 l_ele_value_tab;
535
536 if g_debug then
537 hr_utility.set_location('Creating Archive Item ',3);
538 end if;
539
540 for i in 1..l_ele_ent_id_tab.count loop
541 archive_item(p_item => p_item
542 ,p_context1 => p_assignment_action_id
543 ,p_value => l_ele_value_tab (i)
544 ,p_context2 => l_ele_ent_id_tab(i));
545 end loop;
546
547 close c_prev_emp_details;
548
549 if g_debug then
550 hr_utility.set_location('Exiting : Populate archive item ',200);
551 end if;
552 exception
553
554 when others then
555 hr_utility.set_location('Error in populate archive item ',10);
556 raise;
557
558 end;
559 -- Bug 9247411
560 procedure populate_prev_archive_item(p_item varchar2,p_element_name varchar2, p_piv_name varchar2) is
561
562
563 begin
564
565 if g_debug then
566 hr_utility.set_location('Entering : Populate archive item ',1);
567 hr_utility.set_location('Assignments action id is '||p_assignment_action_id,2);
568 end if;
569
570 l_ele_ent_id_tab.delete;
571 l_ele_value_tab.delete;
572 l_ele_ent_id_tab1.delete;
573 l_ele_value_tab1.delete;
574 l_ele_ent_id_tab2.delete;
575 l_ele_value_tab2.delete;
576 l_ele_ent_id_tab3.delete;
577
578 open c_prev_emp_details('PREV_ER_INFO','BP_NUMBER');
579 fetch c_prev_emp_details bulk collect
580 into
581 l_ele_ent_id_tab1,
582 l_ele_value_tab1;
583 close c_prev_emp_details;
584 open c_prev_emp_details('PREV_SEP_PENS_DTLS','BP_NUMBER');
585 fetch c_prev_emp_details bulk collect
586 into
587 l_ele_ent_id_tab2,
588 l_ele_value_tab2;
589 close c_prev_emp_details;
590
591 for i in 1..l_ele_ent_id_tab2.count loop
592 for j in 1..l_ele_ent_id_tab1.count loop
593 if (l_ele_value_tab2(i) = l_ele_value_tab1(j)) then
594 l_ele_ent_id_tab3(i) := l_ele_ent_id_tab1(j);
595 end if;
596 end loop;
597 end loop;
598
599 open c_prev_emp_details(p_element_name,p_piv_name);
600 fetch c_prev_emp_details bulk collect
601 into
602 l_ele_ent_id_tab,
603 l_ele_value_tab;
604
605 if g_debug then
606 hr_utility.set_location('Creating Archive Item ',3);
607 end if;
608
609 for i in 1..l_ele_ent_id_tab.count loop
610 for j in 1..l_ele_ent_id_tab2.count loop
611 if (l_ele_ent_id_tab(i) = l_ele_ent_id_tab2(j)) then
612
613 archive_item(p_item => p_item
614 ,p_context1 => p_assignment_action_id
615 ,p_value => l_ele_value_tab (i)
616 ,p_context2 => l_ele_ent_id_tab3(j));
617 end if;
618 end loop;
619 end loop;
620
621 close c_prev_emp_details;
622
623 if g_debug then
624 hr_utility.set_location('Exiting : Populate archive item ',200);
625 end if;
626 exception
627
628 when others then
629 hr_utility.set_location('Error in populate archive item ',10);
630 raise;
631
632 end;
633
634 procedure archive_preapid_item_value(p_item varchar2
635 ,p_element_name varchar2
636 ,p_piv_name varchar2
637 ,p_assignment_action_id number) is
638
639 l_prepaid_value NUMBER;
640
641 begin
642
643 if g_debug then
644 hr_utility.set_location('Entering : archive_preapid_item_value',1);
645 end if;
646
647 l_ele_ent_id_tab.delete;
648 l_ele_value_tab.delete;
649
650 open c_prev_emp_details(p_element_name,p_piv_name);
651 fetch c_prev_emp_details bulk collect
652 into
653 l_ele_ent_id_tab,
654 l_ele_value_tab;
655
656 l_prepaid_value := 0;
657
658 for i in 1..l_ele_value_tab.count loop
659 l_prepaid_value := l_prepaid_value + l_ele_value_tab (i);
660 end loop ;
661
662 close c_prev_emp_details;
663
664 archive_item(p_item => p_item
665 ,p_context1 => p_assignment_action_id
666 ,p_context2 => null
667 ,p_value => l_prepaid_value);
668
669 if g_debug then
670 hr_utility.set_location('Leaving : archive_preapid_item_value',1);
671 end if;
672
673 exception
674 when others then
675 hr_utility.set_location('Error in archive_preapid_item_value ',10);
676 raise;
677
678 end archive_preapid_item_value;
679
680 Begin
681
682 if g_debug then
683 hr_utility.set_location('Entering : Archiving prev emp Details ',10);
684 end if;
685
686 populate_archive_item('X_KR_PREV_BP_NUMBER','PREV_ER_INFO','BP_NUMBER');
687 populate_archive_item('X_KR_PREV_BP_NAME','PREV_ER_INFO','BP_NAME');
688 populate_archive_item('X_KR_PREV_HIRE_DATE','PREV_ER_INFO','H_DATE');
689 populate_archive_item('X_KR_PREV_LEAVING_DATE','PREV_ER_INFO','L_DATE');
690 populate_archive_item('X_KR_PREV_FINAL_INT_DATE','PREV_ER_INFO','FINAL_INT_DATE');
691 populate_archive_item('X_KR_PREV_SEP_PAY','PREV_ER_INFO','SEP_PAY');
692 populate_archive_item('X_KR_PREV_NON_TAXABLE_EARNING','PREV_ER_INFO','NON_TAXABLE_EARNING');
693 populate_archive_item('X_KR_PREV_SEP_ALLOWANCE','PREV_ER_INFO','SP_SEP_ALW');
694 populate_archive_item('X_KR_PREV_SEP_INSURANCE','PREV_ER_INFO','SEP_INS');
695 populate_archive_item('X_KR_PREV_SEP_POST_TAX_FLAG','PREV_ER_INFO','ELIGIBLE_POST_TAX_DEDUC_FLAG');
696
697 /*16221828*/
698 populate_archive_item('X_KR_SEP_PEN_FIN_INST','Separation Pension Account Details','Financial Institute');
699 populate_archive_item('X_SEP_DEF_TRANS_DATE','Separation Pay Tax Deferring Details','Transferred Date');
700 populate_archive_item('X_SEP_DEF_MATUR_DATE','Separation Pay Tax Deferring Details','Maturity Date');
701 populate_archive_item('X_KR_TRANS_SEP_FIN_INST','Separation Pay Tax Deferring Details','Transfer Financial Institute');
702 populate_archive_item('X_KR_EXEC_SEP_PAY','Executive Sep Pay Information','Separation Pay');
703 populate_archive_item('X_PREV_ADD_MONTH_STAT','PREV_ER_INFO2','Additional Months for Stat');
704 populate_archive_item('X_PREV_ADD_MONTH_NONSTAT','PREV_ER_INFO2','Additional Months for NonStat');
705
706 populate_prev_archive_item('X_KR_PREV_SEP_TOT_RECEIVED','PREV_SEP_PENS_DTLS','TOTAL_RECEIVED');
707 populate_prev_archive_item('X_KR_PREV_SEP_PRINCIPAL_INTRST','PREV_SEP_PENS_DTLS','PRINCIPAL_INTRST');
708 populate_prev_archive_item('X_KR_PREV_SEP_PERS_CONTRIBUTION','PREV_SEP_PENS_DTLS','PERS_CONTRIBUTION');
709 populate_prev_archive_item('X_KR_PREV_SEP_PENS_EXEM','PREV_SEP_PENS_DTLS','PENS_EXEM');
710 /* Bug 10216334 */
711 populate_prev_archive_item('X_KR_PREV_LUMP_SUM_STAT_ENT','PREV_SEP_PENS_DTLS','LUMP_SUM_AMT_STAT');
712 populate_prev_archive_item('X_KR_PREV_LUMP_SUM_NST_ENT','PREV_SEP_PENS_DTLS','LUMP_SUM_AMT_NST');
713
714 -- Bug 2678508 Incorrect prepaid Tax Logic in Sep Tax receipt
715 -- So archiving the Prev Separaion Pay ITAX ,RTAX,STAX
716
717 archive_preapid_item_value('X_KR_EMP_PREPAID_ITAX','PREV_ER_INFO','ITAX',p_assignment_action_id);
718 archive_preapid_item_value('X_KR_EMP_PREPAID_RTAX','PREV_ER_INFO','RTAX',p_assignment_action_id);
719 archive_preapid_item_value('X_KR_EMP_PREPAID_STAX','PREV_ER_INFO','STAX',p_assignment_action_id);
720
721
722 if g_debug then
723 hr_utility.set_location('Exiting : Archiving prev emp Details ',200);
724 end if;
725
726 End archive_prev_emp_details;
727
728 /* End of prev_emp details */
729
730 -------------------------------------------------------------------------------+
731
732 -------------------------------------------------------------------------------+
733
734 Procedure archive_xdbi
735 (p_business_group_id in hr_organization_units.business_group_id%type,
736 p_tax_unit_id in pay_assignment_Actions.tax_unit_id%type,
737 p_assignment_id in pay_assignment_actions.assignment_id%type,
738 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
739 p_assignment_Action_id in pay_assignment_actions.assignment_action_id%type,
740 p_date_earned in date ) is
741 begin
742
743 /* Corporate Dtails and Prev emp details are archived only if
744 current emp details exist */
745
746 if (archive_emp_details
747 (p_business_group_id,
748 p_tax_unit_id,
749 p_assignment_id,
750 p_payroll_action_id,
751 p_assignment_Action_id,
752 p_date_earned)) then
753
754 archive_corp_details
755 (p_business_group_id,
756 p_tax_unit_id,
757 p_assignment_id,
758 p_payroll_action_id,
759 p_assignment_Action_id,
760 p_date_earned);
761
762
763 archive_prev_emp_details
764 (p_business_group_id,
765 p_tax_unit_id,
766 p_assignment_id,
767 p_payroll_action_id,
768 p_assignment_Action_id,
769 p_date_earned);
770 end if;
771
772 end archive_xdbi;
773 -------------------------------------------------------------------------------+
774
775 --------------------------------------------------------------------------------
776 procedure range_cursor(p_payroll_action_id in number,
777 p_sqlstr out NOCOPY varchar2)
778 --------------------------------------------------------------------------------
779 is
780 begin
781 p_sqlstr := c_range_cursor;
782 end range_cursor;
783 --------------------------------------------------------------------------------
784 procedure assignment_action_creation(p_payroll_action_id in number,
785 p_start_person_id in number,
786 p_end_person_id in number,
787 p_chunk in number)
788 --------------------------------------------------------------------------------
789 is
790 --
791 l_locking_action_id number;
792 --
793 cursor csr_process_assignment
794 is
795 select paa.assignment_id,
796 paa.assignment_action_id,
797 paa.source_action_id,
798 paa.tax_unit_id
799 from pay_run_types_f prt,
800 pay_payroll_actions ppa,
801 pay_assignment_actions paa,
802 per_assignments_f pa,
803 pay_payroll_actions bppa
804 where bppa.payroll_action_id = p_payroll_action_id
805 and pa.business_group_id = bppa.business_group_id
806 and pa.person_id
807 between p_start_person_id and p_end_person_id
808 and bppa.effective_date
809 between pa.effective_start_date and pa.effective_end_date
810 and pa.payroll_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(bppa.payroll_action_id, 'PAYROLL_ID', null))
811 and paa.assignment_id = pa.assignment_id
812 and paa.action_status = 'C'
813 and ppa.payroll_action_id = paa.payroll_action_id
814 and ppa.effective_date
815 /* between trunc(bppa.effective_date,'YYYY') and add_months(trunc(bppa.effective_date,''YYYY''),12) -1 */
816 between trunc(bppa.effective_date,'YYYY') and bppa.effective_date
817 and prt.run_type_id = paa.run_type_id
818 and ppa.effective_date
819 between prt.effective_start_date and prt.effective_end_date
820 and prt.run_type_name like 'SEP%'
821 and prt.run_type_name <> 'SEP_L'
822 and not exists(
823 select 'x'
824 from pay_payroll_actions appa,
825 pay_assignment_actions apaa,
826 pay_action_interlocks pai
827 where pai.locked_action_id = paa.assignment_action_id
828 and apaa.assignment_action_id = pai.locking_action_id
829 and appa.payroll_action_id = apaa.payroll_action_id
830 and appa.action_type = 'X'
831 and appa.report_type = bppa.report_type
832 and trunc(appa.effective_date,'YYYY') = trunc(bppa.effective_date,'YYYY')
833 union all -- 4660184
834 select 'x'
835 from pay_payroll_actions ppa2,
836 pay_run_types_f prt2,
837 pay_assignment_actions paa2
838 where paa2.assignment_id = paa.assignment_id
839 and prt2.run_type_id = paa2.run_type_id
840 and prt2.run_type_id = ppa2.run_type_id
841 and prt2.run_type_name like 'SEP%'
842 and ppa2.payroll_action_id = paa2.payroll_action_id
843 and ppa2.effective_date
844 between trunc(bppa.effective_date,'YYYY') and bppa.effective_date
845 and ppa2.effective_date
846 between prt2.effective_start_date and prt2.effective_end_date
847 and paa2.action_sequence > paa.action_sequence
848 )
849
850 order by pa.assignment_id, paa.action_sequence;
851 --
852 l_csr_process_assignment csr_process_assignment%rowtype;
853 --
854 begin
855 --
856 if g_debug then
857 hr_utility.trace('Start Of assignment_action_creation');
858 end if;
859
860 open csr_process_assignment;
861 loop
862 fetch csr_process_assignment into l_csr_process_assignment;
863 exit when csr_process_assignment%notfound;
864 --
865 -- Insert new Assignment Action for Archive Process
866 --
867 select pay_assignment_actions_s.nextval
868 into l_locking_action_id
869 from dual;
870 --
871 if g_debug then
872 hr_utility.trace(' Locking assignment_action_id ..:'||l_locking_action_id);
873 end if;
874
875 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
876 assignid => l_csr_process_assignment.assignment_id,
877 pactid => p_payroll_action_id,
878 chunk => p_chunk,
879 greid => l_csr_process_assignment.tax_unit_id,
880 prepayid => null,
881 status => 'U');
882 --
883 -- Lock archived Assignemnt Actions
884 --
885 if g_debug then
886 hr_utility.trace('Locked assignment_action_id ..:'||l_csr_process_assignment.assignment_action_id);
887 end if;
888
889 --Bug # 2377251 : added the the code to lock the assignment_action_id (i.e child assignment action id )
890
891 hr_nonrun_asact.insint(lockingactid => l_locking_action_id,
892 lockedactid => l_csr_process_assignment.assignment_action_id);
893
894 if g_debug then
895 hr_utility.trace('Locked source_action_id ..:'||l_csr_process_assignment.source_action_id);
896 end if;
897 --
898 hr_nonrun_asact.insint(lockingactid => l_locking_action_id,
899 lockedactid => l_csr_process_assignment.source_action_id);
900 --
901
902 end loop;
903 close csr_process_assignment;
904
905 if g_debug then
906 hr_utility.trace('End Of assignment_action_creation');
907 end if;
908 --
909 end assignment_action_creation;
910 --------------------------------------------------------------------------------
911 procedure archinit(p_payroll_action_id in number)
912 --------------------------------------------------------------------------------
913 is
914 begin
915 --
916 null;
917 --
918 end archinit;
919 --------------------------------------------------------------------------------
920 procedure archive_data(p_assignment_action_id in number,
921 p_effective_date in date)
922 --------------------------------------------------------------------------------
923 is
924 --
925 l_business_group_id number;
926 l_payroll_id number;
927 l_payroll_action_id number;
928 l_assignment_id number;
929 l_assignment_action_id number;
930 l_date_earned date;
931 l_tax_unit_id number;
932 --
933 l_context_no number;
934 cnt number := 0;
935 --
936 cursor csr_context
937 is
938 select ppa.business_group_id,
939 ppa.payroll_id,
940 ppa.payroll_action_id,
941 paa.assignment_id,
942 paa.assignment_action_id,
943 ppa.date_earned,
944 paa.tax_unit_id
945 from pay_payroll_actions ppa,
946 pay_assignment_actions paa,
947 pay_action_interlocks pai,
948 pay_assignment_actions xpaa
949 where xpaa.assignment_action_id = p_assignment_action_id
950 and pai.locking_action_id = xpaa.assignment_action_id
951 and paa.assignment_action_id = pai.locked_action_id
952 and paa.source_action_id is not null
953 and ppa.payroll_action_id = paa.payroll_action_id;
954 --
955 begin
956 --
957 if g_debug then
958 hr_utility.trace('Start of archive_data');
959 end if;
960
961 l_context_no := pay_archive.g_context_values.sz;
962 for i in 1..l_context_no loop
963 pay_archive.g_context_values.name(i) := NULL;
964 pay_archive.g_context_values.value(i) := NULL;
965 end loop;
966 pay_archive.g_context_values.sz := 0;
967 --
968 open csr_context;
969 fetch csr_context into
970 l_business_group_id,
971 l_payroll_id,
972 l_payroll_action_id,
973 l_assignment_id,
974 l_assignment_action_id,
975 l_date_earned,
976 l_tax_unit_id;
977 close csr_context;
978 --
979 --
980 -- Set Context for DB Item
981 --
982 if l_business_group_id is not null then
983 cnt := cnt + 1;
984 pay_archive.g_context_values.name(cnt) := 'BUSINESS_GROUP_ID';
985 pay_archive.g_context_values.value(cnt) := l_business_group_id;
986 end if;
987 --
988 if l_payroll_id is not null then
989 cnt := cnt + 1;
990 pay_archive.g_context_values.name(cnt) := 'PAYROLL_ID';
991 pay_archive.g_context_values.value(cnt) := l_payroll_id;
992 end if;
993 --
994 if l_payroll_action_id is not null then
995 cnt := cnt + 1;
996 pay_archive.g_context_values.name(cnt) := 'PAYROLL_ACTION_ID';
997 pay_archive.g_context_values.value(cnt) := l_payroll_action_id;
998 end if;
999 --
1000 if l_assignment_id is not null then
1001 cnt := cnt + 1;
1002 pay_archive.g_context_values.name(cnt) := 'ASSIGNMENT_ID';
1003 pay_archive.g_context_values.value(cnt) := l_assignment_id;
1004 end if;
1005 --
1006 if l_assignment_action_id is not null then
1007 cnt := cnt + 1;
1008 pay_archive.g_context_values.name(cnt) := 'ASSIGNMENT_ACTION_ID';
1009 pay_archive.g_context_values.value(cnt) := l_assignment_action_id;
1010 -- Set Assignment Action id to get Balance
1011 pay_archive.balance_aa := l_assignment_action_id;
1012 pay_archive.archive_aa := l_assignment_action_id;
1013 end if;
1014 --
1015 if l_date_earned is not null then
1016 cnt := cnt + 1;
1017 pay_archive.g_context_values.name(cnt) := 'DATE_EARNED';
1018 pay_archive.g_context_values.value(cnt) := fnd_date.date_to_canonical(l_date_earned);
1019 end if;
1020 --
1021 if l_tax_unit_id is not null then
1022 cnt := cnt + 1;
1023 pay_archive.g_context_values.name(cnt) := 'TAX_UNIT_ID';
1024 pay_archive.g_context_values.value(cnt) := l_tax_unit_id;
1025 end if;
1026 --
1027 -- This value is used in pay_archive.archive_dbi
1028 pay_archive.g_context_values.sz := cnt;
1029 --
1030 --
1031 --
1032 if g_debug then
1033 hr_utility.set_location('P_BUSINESS_GROUP_ID ->' ||l_business_group_id,100);
1034 hr_utility.set_location('P_TAX_UNIT_ID ->' ||l_tax_unit_id,200);
1035 hr_utility.set_location('P_ASSIGNMENT_ID ->' ||l_assignment_id,300);
1036 hr_utility.set_location('P_PAYROLL_ACTION_ID ->' ||l_payroll_action_id,400);
1037 hr_utility.set_location('P_ASSIGNMENT_ACTION_ID ->' ||p_assignment_action_id,500);
1038 end if;
1039
1040 --------------------------
1041
1042 --Archive efile related data
1043 --------------------------
1044 if g_debug then
1045 hr_utility.set_location('Calling archive_xdbi',2);
1046 end if;
1047
1048 archive_xdbi ( P_BUSINESS_GROUP_ID =>l_business_group_id
1049 ,P_TAX_UNIT_ID =>l_tax_unit_id
1050 ,P_ASSIGNMENT_ID =>l_assignment_id
1051 ,P_PAYROLL_ACTION_ID =>l_payroll_action_id
1052 ,P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id
1053 ,P_DATE_EARNED =>l_date_earned );
1054
1055 if g_debug then
1056 hr_utility.set_location('Exiting archive_xdbi',3);
1057 hr_utility.trace('End of archive_data');
1058 end if;
1059 end archive_data;
1060
1061 begin
1062 g_debug := hr_utility.debug_enabled;
1063 end pay_kr_sep_archive_pkg;