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