[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_PSTA
Source
1 package body pay_fi_archive_psta as
2 /* $Header: pyfipsta.pkb 120.1 2010/10/27 14:20:56 vijranga ship $ */
3 /* Define the Global variables */
4 type lock_rec is record (
5 archive_assact_id number
6 );
7
8 type lock_table is table of lock_rec
9 index by binary_integer;
10
11 g_actid number;
12 g_run_payroll_action_id number;
13 g_package varchar2 (33) := 'PAY_FI_ARCHIVE_PSTA .';
14 g_debug boolean := hr_utility.debug_enabled;
15 g_business_group_id number;
16 g_legal_employer_id number;
17 g_effective_date date;
18 g_year varchar2 (4);
19 g_local_unit_id number;
20 g_archive varchar2 (50);
21 g_payroll_type_code varchar2 (50);
22 g_legal_empl_y_num hr_organization_information.org_information1%type;
23 g_legal_emp_name hr_organization_units.name%type;
24 g_local_unit_sd_no hr_organization_information.org_information1%type;
25 g_local_unit_name hr_organization_units.name%type;
26 g_year_last_date date;
27 g_year_start_date date;
28 g_lock_table lock_table;
29 g_index number := -1;
30 g_index_assact number := -1;
31 g_payroll_id number;
32 g_pay_period_id number;
33 g_pay_period varchar2 (240);
34 g_payroll varchar2 (240);
35 g_pay_period_end_date date;
36 g_pay_period_start_date date;
37 g_period_type per_time_periods.period_type%type;
38 g_time_period_id per_time_periods.time_period_id%type;
39 g_legal_employer_name varchar2 (240);
40 g_person_id number := -1;
41 g_arch_payroll_action_id number;
42 g_payroll_type varchar2 (200);
43 g_emp_local_unit_id varchar2 (30);
44 --
45 --
46 /* GET PARAMETER */
47 function get_parameter (
48 p_parameter_string in varchar2,
49 p_token in varchar2,
50 p_segment_number in number default null
51 )
52 return varchar2 is
53 l_parameter pay_payroll_actions.legislative_parameters%type := null;
54 l_start_pos number;
55 l_delimiter varchar2 (1) := ' ';
56 l_proc varchar2 (240) := g_package || ' get parameter ';
57 begin
58 if g_debug then
59 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
60 end if;
61
62 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
63
64 --
65 if l_start_pos = 0 then
66 l_delimiter := '|';
67 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
68 end if;
69
70 if l_start_pos <> 0 then
71 l_start_pos := l_start_pos + length (p_token || '=');
72 l_parameter := substr (
73 p_parameter_string,
74 l_start_pos,
75 instr (p_parameter_string || ' ', l_delimiter, l_start_pos) - l_start_pos
76 );
77
78 if p_segment_number is not null then
79 l_parameter := ':' || l_parameter || ':';
80 l_parameter := substr (
81 l_parameter,
82 instr (l_parameter, ':', 1, p_segment_number) + 1,
83 instr (l_parameter, ':', 1, p_segment_number + 1) - 1
84 - instr (l_parameter, ':', 1, p_segment_number)
85 );
86 end if;
87 end if;
88
89 --
90 if g_debug then
91 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
92 end if;
93
94 return l_parameter;
95 end;
96 --
97 --
98 /* GET ALL PARAMETERS */
99 procedure get_all_parameters (
100 p_payroll_action_id in number,
101 p_business_group_id out nocopy number,
102 p_legal_employer_id out nocopy number,
103 p_local_unit_id out nocopy number,
104 p_year out nocopy varchar2,
105 p_payroll_type_code out nocopy varchar2,
106 p_payroll_id out nocopy varchar2,
107 p_archive out nocopy varchar2,
108 p_effective_date out nocopy date
109 ) is
110 cursor csr_parameter_info (
111 p_payroll_action_id number
112 ) is
113 select pay_fi_archive_psta.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER_ID'),
114 pay_fi_archive_psta.get_parameter (legislative_parameters, 'ARCHIVE'),
115 pay_fi_archive_psta.get_parameter (legislative_parameters, 'LOCAL_UNIT_ID'),
116 pay_fi_archive_psta.get_parameter (legislative_parameters, 'YEAR_RPT'),
117 pay_fi_archive_psta.get_parameter (legislative_parameters, 'PAYROLL_TYPE'),
118 pay_fi_archive_psta.get_parameter (legislative_parameters, 'PAYROLL_ID'), effective_date, business_group_id
119 from pay_payroll_actions
120 where payroll_action_id = p_payroll_action_id;
121
122 l_proc varchar2 (240) := g_package || ' GET_ALL_PARAMETERS ';
123 --
124 begin
125 fnd_file.put_line (fnd_file.log, 'Entering Get all Parameters');
126 open csr_parameter_info (p_payroll_action_id);
127 fetch csr_parameter_info into p_legal_employer_id,
128 p_archive,
129 p_local_unit_id,
130 p_year,
131 p_payroll_type_code,
132 p_payroll_id,
133 p_effective_date,
134 p_business_group_id;
135 close csr_parameter_info;
136
137 --
138 if g_debug then
139 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS', 30);
140 end if;
141 end get_all_parameters;
142 --
143 --
144 /* Range Code*/
145 procedure range_code (
146 p_payroll_action_id in number,
147 p_sql out nocopy varchar2
148 ) is
149 cursor csr_legal_employer_details (
150 csr_v_legal_employer_id hr_organization_information.organization_id%type
151 ) is
152 select o1.name legal_employer_name, hoi2.org_information1 legal_emp_y_num, hoi2.org_information11
153 from hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
154 where o1.business_group_id = g_business_group_id
155 and hoi1.organization_id = o1.organization_id
156 and hoi1.organization_id = csr_v_legal_employer_id
157 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
158 and hoi1.org_information_context = 'CLASS'
159 and o1.organization_id = hoi2.organization_id
160 and hoi2.org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS';
161
162 l_legal_employer_details csr_legal_employer_details%rowtype;
163
164 cursor csr_local_unit_details (
165 csr_v_local_unit_id hr_organization_information.organization_id%type
166 ) is
167 select o1.name local_unit_name, hoi2.org_information1 local_unit_sd_no, hoi2.org_information7
168 from hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
169 where o1.business_group_id = g_business_group_id
170 and hoi1.organization_id = o1.organization_id
171 and hoi1.organization_id = csr_v_local_unit_id
172 and hoi1.org_information1 = 'FI_LOCAL_UNIT'
173 and hoi1.org_information_context = 'CLASS'
174 and o1.organization_id = hoi2.organization_id
175 and hoi2.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
176
177 cursor csr_all_local_unit_details (
178 csr_v_legal_employer_id hr_organization_information.organization_id%type
179 ) is
180 select hoi_le.org_information1 local_unit_id, hou_lu.name local_unit_name, hoi_lu.org_information1 local_unit_sd_no,
181 hoi_lu.org_information7
182 from hr_all_organization_units hou_le,
183 hr_organization_information hoi_le,
184 hr_all_organization_units hou_lu,
185 hr_organization_information hoi_lu
186 where hoi_le.organization_id = hou_le.organization_id
187 and hou_le.organization_id = csr_v_legal_employer_id
188 and hoi_le.org_information_context = 'FI_LOCAL_UNITS'
189 and hou_lu.organization_id = hoi_le.org_information1
190 and hou_lu.organization_id = hoi_lu.organization_id
191 and hoi_lu.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
192
193 cursor csr_get_org_address (
194 p_organization_id number
195 ) is
196 select style, address_line_1, address_line_2, address_line_3, country, postal_code
197 from hr_organization_units hou, hr_locations hl
198 where hou.organization_id = p_organization_id and hou.location_id = hl.location_id;
199
200 rl_get_org_address csr_get_org_address%rowtype;
201 rg_local_unit_details csr_local_unit_details%rowtype;
202 l_action_info_id number;
203 l_ovn number;
204 l_postal_code hr_locations.postal_code%type;
205 l_country hr_locations.country%type;
206 l_payroll_name pay_payrolls_f.payroll_name%type;
207 begin
208 if g_debug then
209 hr_utility.set_location (' Entering Procedure RANGE_CODE', 10);
210 end if;
211
212 p_sql :=
213 'SELECT DISTINCT person_id
214 FROM per_people_f ppf
215 ,pay_payroll_actions ppa
216 WHERE ppa.payroll_action_id = :payroll_action_id
217 AND ppa.business_group_id = ppf.business_group_id
218 ORDER BY ppf.person_id';
219 --
220 --
221 /* Get the Parameters'value */
222 pay_fi_archive_psta.get_all_parameters (
223 p_payroll_action_id,
224 g_business_group_id,
225 g_legal_employer_id,
226 g_local_unit_id,
227 g_year,
228 g_payroll_type_code,
229 g_payroll_id,
230 g_archive,
231 g_effective_date
232 );
233
234 if g_archive = 'Y' then
235 g_payroll_type := hr_general.decode_lookup ('FI_PROC_PERIOD_TYPE', g_payroll_type_code);
236
237 /* Get the Last Date of the Year */
238 select fnd_date.canonical_to_date (g_year || '12/31')
239 into g_year_last_date
240 from dual;
241
242 /* Get the First Date of the Year */
243 g_year_start_date := add_months (g_year_last_date, -12) + 1;
244
245 /* Get the Payroll Name */
246 if g_payroll_id is not null then
247 select payroll_name
248 into l_payroll_name
249 from pay_payrolls_f
250 where payroll_id = g_payroll_id and g_year_last_date between effective_start_date and effective_end_date;
251 end if;
252
253 g_arch_payroll_action_id := p_payroll_action_id;
254 --
255 --
256 /* Get the legal employer's detail*/
257 open csr_legal_employer_details (g_legal_employer_id);
258 fetch csr_legal_employer_details into l_legal_employer_details;
259 close csr_legal_employer_details;
260 g_legal_emp_name := l_legal_employer_details.legal_employer_name;
261 g_legal_empl_y_num := l_legal_employer_details.legal_emp_y_num;
262
263 /* Get the Local Unit Detail */
264 if g_local_unit_id is not null then
265 open csr_local_unit_details (g_local_unit_id);
266 fetch csr_local_unit_details into rg_local_unit_details;
267 close csr_local_unit_details;
268 g_local_unit_name := rg_local_unit_details.local_unit_name;
269 g_local_unit_sd_no := rg_local_unit_details.local_unit_sd_no;
270 end if;
271
272 /* Archive the Parameters */
273 pay_action_information_api.create_action_information (
274 p_action_information_id => l_action_info_id,
275 p_action_context_id => p_payroll_action_id,
276 p_action_context_type => 'PA',
277 p_object_version_number => l_ovn,
278 p_effective_date => g_effective_date,
279 p_source_id => null,
280 p_source_text => null,
281 p_action_information_category => 'EMEA REPORT DETAILS',
282 p_action_information1 => 'PYFIPSTA',
283 p_action_information2 => g_business_group_id,
284 p_action_information3 => g_legal_employer_id,
285 p_action_information4 => g_legal_emp_name,
286 p_action_information5 => g_legal_empl_y_num,
287 p_action_information6 => g_local_unit_id,
288 p_action_information7 => g_local_unit_name,
289 p_action_information8 => g_local_unit_sd_no,
290 p_action_information9 => g_year,
291 p_action_information10 => g_payroll_type,
292 p_action_information11 => l_payroll_name
293 );
294
295 for i in csr_all_local_unit_details (g_legal_employer_id)
296 loop
297 rl_get_org_address.address_line_1 := null;
298 rl_get_org_address.address_line_2 := null;
299 rl_get_org_address.address_line_3 := null;
300 rl_get_org_address.country := null;
301 rl_get_org_address.postal_code := null;
302 open csr_get_org_address (i.local_unit_id);
303 fetch csr_get_org_address into rl_get_org_address;
304 close csr_get_org_address;
305
306 if rl_get_org_address.style = 'FI' then
307 l_postal_code := hr_general.decode_lookup ('FI_POSTAL_CODE', rl_get_org_address.postal_code);
308 else
309 l_postal_code := rl_get_org_address.postal_code;
310 end if;
311
312 /* Get the Country Name */
313 l_country := pay_fi_archive_psta.get_country_name (rl_get_org_address.country);
314 /* Archive the Local Units Details */
315 pay_action_information_api.create_action_information (
316 p_action_information_id => l_action_info_id,
317 p_action_context_id => p_payroll_action_id,
318 p_action_context_type => 'PA',
319 p_object_version_number => l_ovn,
320 p_effective_date => g_effective_date,
321 p_source_id => null,
322 p_source_text => null,
323 p_action_information_category => 'EMEA REPORT INFORMATION',
324 p_action_information1 => 'PYFIPSTA',
325 p_action_information2 => 'LU_DETAILS',
326 p_action_information3 => g_business_group_id,
327 p_action_information4 => g_legal_employer_id,
328 p_action_information5 => i.local_unit_id,
329 p_action_information6 => i.local_unit_name,
330 p_action_information7 => i.local_unit_sd_no,
331 p_action_information8 => rl_get_org_address.address_line_1,
332 p_action_information9 => rl_get_org_address.address_line_2,
333 p_action_information10 => rl_get_org_address.address_line_3,
334 p_action_information11 => l_country,
335 p_action_information12 => l_postal_code
336 );
337 end loop;
338 --
339 --
340 end if;
341 --
342 --
343 end;
344 --
345 --
346 /* Assignment Action Code*/
347 procedure assignment_action_code (
348 p_payroll_action_id in number,
349 p_start_person in number,
350 p_end_person in number,
351 p_chunk in number
352 ) is
353 l_year_last_date date;
354 l_prepay_action_id number;
355 l_actid number;
356 l_assignment_id number;
357
358 /* Cursor to take all the payroll runs for the given period for given Payroll Type and Payroll */
359 cursor csr_prepaid_assignments_lu (
360 p_payroll_action_id number,
361 p_start_person number,
362 p_end_person number,
363 p_legal_employer_id number,
364 p_local_unit_id number,
365 p_start_date date,
366 p_end_date date
367 ) is
368 select paaf.person_id, paaf.primary_flag, act.assignment_id assignment_id, act.assignment_action_id run_action_id,
369 act1.assignment_action_id
370 prepaid_action_id, appa.effective_date, appa.payroll_action_id,
371 appa2.payroll_action_id payactid, hsck.segment2 local_unit_id
372 from pay_payroll_actions appa,
373 pay_payroll_actions appa2,
374 pay_assignment_actions act,
375 pay_assignment_actions act1,
376 pay_action_interlocks pai,
377 per_all_assignments_f paaf,
378 hr_soft_coding_keyflex hsck,
379 hr_organization_information hoi,
380 pay_payrolls_f ppa
381 where appa.action_type in ('R', 'Q')
382 and act.payroll_action_id = appa.payroll_action_id
383 and act.source_action_id is null -- Master Action
384 and act.action_status IN ('C','S') -- 10229501
385 and act.assignment_action_id = pai.locked_action_id
386 and act1.assignment_action_id = pai.locking_action_id
387 and act1.action_status IN ('C','S') -- 10229501
388 and act1.payroll_action_id = appa2.payroll_action_id
389 and appa2.action_type in ('P', 'U')
390 and paaf.assignment_id = act.assignment_id
391 -- and paaf.assignment_id = p_assignemtn_id
392 and appa.effective_date between paaf.effective_start_date and paaf.effective_end_date
393 and appa.effective_date between p_start_date and p_end_date
394 and paaf.primary_flag = 'Y'
395 and paaf.person_id between p_start_person and p_end_person
396 and ppa.payroll_id = paaf.payroll_id
397 and ppa.payroll_id = nvl (g_payroll_id, ppa.payroll_id)
398 and ppa.period_type = g_payroll_type
399 and g_year_last_date between ppa.effective_start_date and ppa.effective_end_date
400 and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
401 and hsck.segment2 = nvl (to_char (p_local_unit_id), hsck.segment2)
402 and hoi.organization_id = p_legal_employer_id
403 and hoi.org_information_context = 'FI_LOCAL_UNITS'
404 and hoi.org_information1 = hsck.segment2
405 order by person_id, assignment_id, payroll_action_id, prepaid_action_id;
406
407 /* Cursor to get the time period details for the given payroll action id */
408 cursor csr_time_period_details (
409 csr_v_payroll_action_id pay_payroll_actions.payroll_action_id%type
410 ) is
411 select papf.payroll_id, papf.payroll_name, ptp.start_date, ptp.end_date, ptp.period_name, ptp.period_type,
412 ptp.regular_payment_date, ptp.time_period_id
413 from pay_payroll_actions ppa, per_time_periods ptp, pay_all_payrolls_f papf
414 where ptp.time_period_id = ppa.time_period_id
415 and ppa.payroll_id = papf.payroll_id
416 and ppa.payroll_action_id = csr_v_payroll_action_id;
417
418 lr_time_period_details csr_time_period_details%rowtype;
419 l_action_info_id number;
420 l_ovn number;
421
422
423 /* PL/SQL table to take the period and assignment action details for the benefits */
424 type time_period_detail_rec is record (
425 time_period_id per_time_periods.time_period_id%type,
426 pay_period_start_date per_time_periods.start_date%type,
427 pay_period_end_date per_time_periods.end_date%type,
428 pay_period per_time_periods.period_name%type,
429 assignment_action_id number
430 );
431
432 type benefit_time_period is table of time_period_detail_rec
433 index by binary_integer;
434
435 /* PL/SQL table to take the person and his corresponding payroll runs in the given period .The table consists
436 person id,assignment id and the time period details for that person which it self a PL/SQL table */
437
438 type per_period_detail_rec is record (
439 person_id per_all_people_f.person_id%type,
440 assignment_id per_all_assignments_f.assignment_id%type,
441 time_period benefit_time_period
442 );
443
444 type person_period_detail is table of per_period_detail_rec
445 index by binary_integer;
446
447 benefit_person_detail person_period_detail;
448 l_person_index number := -1;
449 l_period_index number := 0;
450 l_previous_person_id number := -1;
451
452 /* Procedure to archive the benefit details :--
453 Details of parameters :-
454 1. benefit_person_period - PL/SQL table to take the person and his corresponding payroll runs in the given period .
455 The table consists person id,assignment id and the time period details for that person which
456 it self a PL/SQL table
457 2.P_payroll_action_id - Payroll action id for the archiving
458
459 Logic for the procedure - First the for the each person in the benefit_type_tab PL/SQL table and after that
460 each benefit will be checked it has some value for the year or not .and if it's some value for then
461 for the that benefit for each period the value will be checked for the change ,and if it's some change
462 then it'll be stored in the PL/SQL table for archiving */
463
464
465
466 procedure archive_benefit_details (
467 benefit_person_period in person_period_detail,
468 p_payroll_action_id in number
469 ) is
470 cursor csr_balance (
471 p_balance_category_name varchar2
472 ) is
473 select pbt.balance_name
474 from pay_balance_types pbt, pay_balance_categories_f pbc
475 where pbc.legislation_code = 'FI'
476 and pbt.balance_category_id = pbc.balance_category_id
477 and pbt.business_group_id = g_business_group_id
478 and pbc.category_name = p_balance_category_name;
479
480 l_monetary_value number := 0;
481
482 /* PL/SQL table to take the Benefit details for final archiving the data */
483 type benefit_archive_details_rec is record (
484 pay_period_start_date per_time_periods.start_date%type,
485 pay_period_end_date per_time_periods.end_date%type,
486 pay_current_end_date per_time_periods.end_date%type,
487 benefit_name varchar2 (240),
488 benfit_value number,
489 assignment_id number,
490 person_id number,
491 assignment_action_id number
492 );
493
494 type benefit_archive_details_tab is table of benefit_archive_details_rec
495 index by binary_integer;
496
497 benefit_archive_details benefit_archive_details_tab;
498 l_old_value number := 0;
499 l_current_value number := 0;
500 l_index number := 0;
501 l_old_period_end_date date;
502 l_period_number number;
503 l_car_old_value varchar2 (240) := ' ';
504 l_car_current_value varchar2 (240);
505 l_old_car_period_end_date date;
506
507 /* PL/SQL table to take all the benefit types */
508 type benefit_type is table of varchar2 (240)
509 index by binary_integer;
510
511 benefit_type_tab benefit_type;
512 l_database_ytd_item_suffix pay_balance_dimensions.database_item_suffix%type;
513 l_database_ptd_item_suffix pay_balance_dimensions.database_item_suffix%type;
514 l_actid number;
515 l_ytd_value number := 0;
516 l_person_last_period number;
517
518 cursor get_car_element_details (
519 p_assignment_id number,
520 p_value_date date,
521 p_input_name varchar2
522 ) is
523 select iv1.name, eev1.effective_start_date, eev1.screen_entry_value screen_entry_value, iv1.lookup_type, uom
524 from per_all_assignments_f asg1,
525 per_all_assignments_f asg2,
526 per_all_people_f per,
527 pay_element_links_f el,
528 pay_element_types_f et,
529 pay_input_values_f iv1,
530 pay_element_entries_f ee,
531 pay_element_entry_values_f eev1
532 where asg1.assignment_id = p_assignment_id
533 and p_value_date between asg1.effective_start_date and asg1.effective_end_date
534 and p_value_date between asg2.effective_start_date and asg2.effective_end_date
535 and p_value_date between per.effective_start_date and per.effective_end_date
536 and per.person_id = asg1.person_id
537 and asg2.person_id = per.person_id
538 and asg2.primary_flag = 'Y'
539 and et.element_name = 'Car Benefit'
540 and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
541 and iv1.element_type_id = et.element_type_id
542 and iv1.name = nvl (p_input_name, iv1.name)
543 and el.business_group_id = per.business_group_id
544 and el.element_type_id = et.element_type_id
545 and ee.assignment_id = asg2.assignment_id
546 and ee.element_link_id = el.element_link_id
547 and eev1.element_entry_id = ee.element_entry_id
548 and eev1.input_value_id = iv1.input_value_id
549 and eev1.screen_entry_value is not null
550 and p_value_date between ee.effective_start_date and ee.effective_end_date
551 and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
552 begin
553 benefit_type_tab (0) := 'Phone Benefit';
554 benefit_type_tab (1) := 'Internet Connection Benefit';
555 benefit_type_tab (2) := 'Housing Benefit';
556 benefit_type_tab (3) := 'Child Care Benefit';
557 benefit_type_tab (4) := 'Tool Benefit';
558 benefit_type_tab (5) := 'Staff Benefit';
559 benefit_type_tab (6) := 'Stock Options Benefit';
560 benefit_type_tab (7) := 'Mortgage Benefit';
561 benefit_type_tab (8) := 'Other Benefits';
562 benefit_type_tab (9) := 'Travel Ticket Benefit';
563 benefit_type_tab (10) := 'Lunch Benefit';
564
565 -- benefit_type_tab (11) := 'Cumulative Car Benefit';
566
567 if benefit_person_detail.count > 0 then
568 if g_local_unit_id is null then
569 l_database_ytd_item_suffix := '_PER_LE_YTD';
570 l_database_ptd_item_suffix := '_PER_LE_PTD';
571 elsif g_local_unit_id is not null then
572 l_database_ytd_item_suffix := '_PER_LU_YTD';
573 l_database_ptd_item_suffix := '_PER_LU_PTD';
574 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', g_local_unit_id);
575 end if;
576
577 for i in benefit_person_period.first .. benefit_person_period.last
578 loop
579 for m in benefit_type_tab.first .. benefit_type_tab.last
580 loop
581 l_ytd_value := 0;
582 l_period_number := benefit_person_period (i).time_period.last;
583 pay_balance_pkg.set_context (
584 'ASSIGNMENT_ACTION_ID',
585 benefit_person_period (i).time_period (l_period_number).assignment_action_id
586 );
587
588 /* To Check the YTD value for the Benefit */
589 if benefit_type_tab (m) = 'Lunch Benefit' then
590 begin
591 l_ytd_value :=
592 get_balance_value (
593 p_balance_name => benefit_type_tab (m),
594 p_assignment_id => benefit_person_period (i).assignment_id,
595 p_database_item_suffix => l_database_ytd_item_suffix,
596 p_bal_date => benefit_person_period (i).time_period (l_period_number).pay_period_end_date
597 );
598 exception
599 when others then
600 l_ytd_value := l_ytd_value;
601 end;
602 elsif benefit_type_tab (m) <> 'Lunch Benefit' then
603 for balance_rec in csr_balance (benefit_type_tab (m))
604 loop
605 begin
606 -- pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID', p_assignment_action_id);
607 l_ytd_value :=
608 l_ytd_value
609 + get_balance_value (
610 p_balance_name => balance_rec.balance_name,
611 p_assignment_id => benefit_person_period (i).assignment_id,
612 p_database_item_suffix => l_database_ytd_item_suffix,
613 p_bal_date => benefit_person_period (i).time_period (l_period_number).pay_period_end_date
614 );
615 exception
616 when others then
617 l_ytd_value := l_ytd_value;
618 end;
619 end loop;
620 end if;
621
622 if l_ytd_value > 0 then
623 l_old_value := 0;
624 l_old_period_end_date := null;
625 l_person_last_period := benefit_person_period (i).time_period.last;
626
627 for j in benefit_person_period (i).time_period.first .. benefit_person_period (i).time_period.last
628 loop
629 l_monetary_value := 0;
630
631 if benefit_type_tab (m) = 'Lunch Benefit' then
632 begin
633 l_monetary_value :=
634 get_balance_value (
635 p_balance_name => 'Lunch Benefit',
636 p_assignment_id => benefit_person_period (i).assignment_id,
637 p_database_item_suffix => l_database_ptd_item_suffix,
638 p_bal_date => benefit_person_period (i).time_period (j).pay_period_end_date
639 );
640 exception
641 when others then
642 l_monetary_value := l_monetary_value;
643 end;
644 elsif benefit_type_tab (m) not in ('Lunch Benefit', 'Car Benefit') then
645 for balance_rec in csr_balance (benefit_type_tab (m))
646 loop
647 begin
648 pay_balance_pkg.set_context (
649 'ASSIGNMENT_ACTION_ID',
650 benefit_person_period (i).time_period (j).pay_period_end_date
651 );
652 l_monetary_value :=
653 l_monetary_value
654 + get_balance_value (
655 p_balance_name => balance_rec.balance_name,
656 p_assignment_id => benefit_person_period (i).assignment_id,
657 p_database_item_suffix => l_database_ptd_item_suffix,
658 p_bal_date => benefit_person_period (i).time_period (j).pay_period_end_date
659 );
660 exception
661 when others then
662 l_monetary_value := l_monetary_value;
663 end;
664 end loop;
665 end if;
666
667 l_current_value := l_monetary_value;
668
669 if l_current_value > 0 and (l_current_value <> l_old_value) then
670 benefit_archive_details (l_index).pay_period_start_date :=
671 benefit_person_period (i).time_period (j).pay_period_start_date;
672
673 if benefit_archive_details.first < l_index
674 and l_old_value <> 0
675 and benefit_archive_details (l_index - 1).benefit_name = benefit_type_tab (m)
676 and benefit_archive_details (l_index - 1).person_id = benefit_person_period (i).person_id then
677 benefit_archive_details (l_index - 1).pay_period_end_date := l_old_period_end_date;
678 end if;
679
680 benefit_archive_details (l_index).benefit_name := benefit_type_tab (m);
681 benefit_archive_details (l_index).benfit_value := l_current_value;
682 benefit_archive_details (l_index).assignment_id := benefit_person_period (i).assignment_id;
683 benefit_archive_details (l_index).person_id := benefit_person_period (i).person_id;
684
685 if benefit_person_period (i).time_period (l_person_last_period).time_period_id =
686 benefit_person_period (i).time_period (j).time_period_id then
687 benefit_archive_details (l_index).pay_period_end_date :=
688 benefit_person_period (i).time_period (j).pay_period_end_date;
689 end if;
690
691 l_index := l_index + 1;
692 elsif l_current_value = 0
693 and (l_current_value <> l_old_value)
694 and benefit_archive_details (l_index - 1).benefit_name = benefit_type_tab (m)
695 and benefit_archive_details (l_index - 1).person_id = benefit_person_period (i).person_id then
696 benefit_archive_details (l_index - 1).pay_period_end_date := l_old_period_end_date;
697 end if;
698
699 l_old_value := l_current_value;
700 l_old_period_end_date := benefit_person_period (i).time_period (j).pay_period_end_date;
701 -- null;
702 end loop;
703 end if;
704 end loop;
705
706 -- For Car Benefit
707 l_car_old_value := ' ';
708 l_old_car_period_end_date := null;
709
710 for j in benefit_person_period (i).time_period.first .. benefit_person_period (i).time_period.last
711 loop
712 for car_element_detail in
713 get_car_element_details (
714 benefit_person_period (i).assignment_id,
715 benefit_person_period (i).time_period (j).pay_period_end_date,
716 'Registration Number'
717 )
718 loop
719 l_car_current_value := car_element_detail.screen_entry_value;
720
721 if l_car_current_value <> ' ' and (l_car_current_value <> l_car_old_value) then
722 benefit_archive_details (l_index).pay_period_start_date :=
723 benefit_person_period (i).time_period (j).pay_period_start_date;
724
725 if benefit_archive_details.first < l_index
726 and l_car_old_value <> ' '
727 and benefit_archive_details (l_index - 1).benefit_name = 'Car Benefit'
728 and benefit_archive_details (l_index - 1).person_id = benefit_person_period (i).person_id then
729 benefit_archive_details (l_index - 1).pay_period_end_date := l_old_car_period_end_date;
730 end if;
731
732 benefit_archive_details (l_index).benefit_name := 'Car Benefit';
733 benefit_archive_details (l_index).assignment_id := benefit_person_period (i).assignment_id;
734 benefit_archive_details (l_index).person_id := benefit_person_period (i).person_id;
735 benefit_archive_details (l_index).pay_current_end_date :=
736 benefit_person_period (i).time_period (j).pay_period_end_date;
737
738 if benefit_person_period (i).time_period (l_person_last_period).time_period_id =
739 benefit_person_period (i).time_period (j).time_period_id then
740 benefit_archive_details (l_index).pay_period_end_date :=
741 benefit_person_period (i).time_period (j).pay_period_end_date;
742 end if;
743
744 l_index := l_index + 1;
745 elsif l_car_current_value is null
746 and (l_car_current_value <> l_car_old_value)
747 and benefit_archive_details (l_index - 1).benefit_name = 'Car Benefit'
748 and benefit_archive_details (l_index - 1).person_id = benefit_person_period (i).person_id then
749 benefit_archive_details (l_index - 1).pay_period_end_date := l_old_car_period_end_date;
750 end if;
751
752 l_car_old_value := l_car_current_value;
753 l_old_car_period_end_date := benefit_person_period (i).time_period (j).pay_period_end_date;
754 end loop;
755 end loop;
756 end loop;
757
758 if benefit_archive_details.count > 0 then
759 for k in benefit_archive_details.first .. benefit_archive_details.last
760 loop
761 if benefit_archive_details (k).benefit_name <> 'Car Benefit' then
762 select pay_assignment_actions_s.nextval
763 into l_actid
764 from dual;
765
766 hr_nonrun_asact.insact (
767 l_actid,
768 benefit_archive_details (k).assignment_id,
769 p_payroll_action_id,
770 p_chunk,
771 null
772 );
773 pay_action_information_api.create_action_information (
774 p_action_information_id => l_action_info_id,
775 p_action_context_id => l_actid, --p_arch_assignment_action_id,
776 p_action_context_type => 'AAP',
777 p_object_version_number => l_ovn,
778 p_effective_date => g_effective_date,
779 p_source_id => null,
780 p_source_text => null,
781 p_action_information_category => 'EMEA REPORT INFORMATION',
782 p_action_information1 => 'PYFIPSTA',
783 p_action_information2 => 'Benefit Details',
784 p_action_information3 => benefit_archive_details (k).person_id,
785 p_action_information5 => benefit_archive_details (k).person_id,
786 p_action_information6 => fnd_date.date_to_canonical (
787 nvl (
788 benefit_archive_details (k).pay_period_start_date,
789 g_year_last_date
790 )
791 ),
792 p_action_information7 => fnd_date.date_to_canonical (
793 benefit_archive_details (k).pay_period_end_date
794 ),
795 p_action_information11 => benefit_archive_details (k).benefit_name,
796 p_action_information12 => fnd_number.number_to_canonical (
797 benefit_archive_details (k).benfit_value
798 ),
799 p_assignment_id => benefit_archive_details (k).assignment_id
800 );
801 else
802 for car_element_detail in get_car_element_details (
803 benefit_archive_details (k).assignment_id,
804 benefit_archive_details (k).pay_current_end_date,
805 null
806 )
807 loop
808 select pay_assignment_actions_s.nextval
809 into l_actid
810 from dual;
811
812 hr_nonrun_asact.insact (
813 l_actid,
814 benefit_archive_details (k).assignment_id,
815 p_payroll_action_id,
816 p_chunk,
817 null
818 );
819
820 if car_element_detail.lookup_type is not null then
821 car_element_detail.screen_entry_value :=
822 hr_general.decode_lookup (
823 car_element_detail.lookup_type,
824 car_element_detail.screen_entry_value
825 );
826 end if;
827
828 pay_action_information_api.create_action_information (
829 p_action_information_id => l_action_info_id,
830 p_action_context_id => l_actid, --p_arch_assignment_action_id,
831 p_action_context_type => 'AAP',
832 p_object_version_number => l_ovn,
833 p_effective_date => g_effective_date,
834 p_source_id => null,
835 p_source_text => null,
836 p_action_information_category => 'EMEA REPORT INFORMATION',
837 p_action_information1 => 'PYFIPSTA',
838 p_action_information2 => 'Car Benefit Details',
839 p_action_information3 => benefit_archive_details (k).person_id,
840 -- p_action_information5 => benefit_archive_details (k).person_id,
841 p_action_information6 => fnd_date.date_to_canonical (
842 benefit_archive_details (k).pay_period_start_date
843 ),
844 p_action_information7 => fnd_date.date_to_canonical (
845 nvl (
846 benefit_archive_details (k).pay_period_end_date,
847 g_year_last_date
848 )
849 ),
850 p_action_information11 => 'Car Benefit',
851 -- p_action_information18 => fnd_number.number_to_canonical (benefit_archive_details (k).time_period_id),
852 p_action_information19 => car_element_detail.name,
853 p_action_information20 => car_element_detail.screen_entry_value,
854 p_action_information22 => car_element_detail.uom,
855 p_assignment_id => benefit_archive_details (k).assignment_id
856 );
857 end loop;
858 end if;
859 end loop;
860 end if;
861
862 end if; --End if benefit_person_detail.count > 0
863 end;
864 -- l_prepay_action_id NUMBER;
865 begin
866 /* Get the Parameters'value */
867 pay_fi_archive_psta.get_all_parameters (
868 p_payroll_action_id,
869 g_business_group_id,
870 g_legal_employer_id,
871 g_local_unit_id,
872 g_year,
873 g_payroll_type_code,
874 g_payroll_id,
875 g_archive,
876 g_effective_date
877 );
878 g_payroll_type := hr_general.decode_lookup ('FI_PROC_PERIOD_TYPE', g_payroll_type_code);
879
880 g_arch_payroll_action_id := p_payroll_action_id;
881
882 --
883 --
884 /* Get the Last Date of the Year */
885 select fnd_date.canonical_to_date (g_year || '12/31')
886 into g_year_last_date
887 from dual;
888
889 /* Get the First Date of the Year */
890 g_year_start_date := add_months (g_year_last_date, -12) + 1;
891
892 for rec_prepaid_assignments in csr_prepaid_assignments_lu (
893 p_payroll_action_id => p_payroll_action_id,
894 p_start_person => p_start_person,
895 p_end_person => p_end_person,
896 p_legal_employer_id => g_legal_employer_id,
897 p_local_unit_id => g_local_unit_id,
898 p_start_date => g_year_start_date,
899 p_end_date => g_year_last_date
900 )
901 loop
902 l_prepay_action_id := 0;
903
904 if l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id then
905 select pay_assignment_actions_s.nextval
906 into l_actid
907 from dual;
908
909 g_run_payroll_action_id := rec_prepaid_assignments.payroll_action_id;
910
911 --
912 --
913 /* Get the Payroll Details */
914 open csr_time_period_details (g_run_payroll_action_id);
915 fetch csr_time_period_details into lr_time_period_details;
916 close csr_time_period_details;
917 --
918 --
919 g_payroll_id := lr_time_period_details.payroll_id;
920 g_pay_period := lr_time_period_details.period_name;
921 g_pay_period_end_date := lr_time_period_details.end_date;
922 g_pay_period_start_date := lr_time_period_details.start_date;
923 g_time_period_id := lr_time_period_details.time_period_id;
924 g_period_type := lr_time_period_details.end_date;
925 g_emp_local_unit_id := rec_prepaid_assignments.local_unit_id;
926 --
927 --
928 /* Generate Assignment Actions */
929 g_index_assact := g_index_assact + 1;
930 g_lock_table (g_index_assact).archive_assact_id := l_actid;
931 hr_nonrun_asact.insact (l_actid, rec_prepaid_assignments.assignment_id, p_payroll_action_id, p_chunk, null);
932
933 --
934 --
935 /* Call the procedure to archive the data */
936 archive_data (
937 p_arch_assignment_action_id => l_actid,
938 p_assignment_action_id => rec_prepaid_assignments.prepaid_action_id,
939 p_assignment_id => rec_prepaid_assignments.assignment_id
940 );
941 end if;
942
943 if rec_prepaid_assignments.person_id <> l_previous_person_id then
944 l_person_index := l_person_index + 1;
945 benefit_person_detail (l_person_index).person_id := rec_prepaid_assignments.person_id;
946 benefit_person_detail (l_person_index).assignment_id := rec_prepaid_assignments.assignment_id;
947 l_period_index := 0;
948 benefit_person_detail (l_person_index).time_period (l_period_index).time_period_id :=
949 lr_time_period_details.time_period_id;
950 benefit_person_detail (l_person_index).time_period (l_period_index).pay_period_start_date :=
951 lr_time_period_details.start_date;
952 benefit_person_detail (l_person_index).time_period (l_period_index).pay_period_end_date :=
953 lr_time_period_details.end_date;
954 benefit_person_detail (l_person_index).time_period (l_period_index).pay_period :=
955 lr_time_period_details.period_name;
956 benefit_person_detail (l_person_index).time_period (l_period_index).assignment_action_id :=
957 rec_prepaid_assignments.prepaid_action_id;
958 l_period_index := l_period_index + 1;
959 l_previous_person_id := rec_prepaid_assignments.person_id;
960 else
961 benefit_person_detail (l_person_index).time_period (l_period_index).time_period_id :=
962 lr_time_period_details.time_period_id;
963 benefit_person_detail (l_person_index).time_period (l_period_index).pay_period_start_date :=
964 lr_time_period_details.start_date;
965 benefit_person_detail (l_person_index).time_period (l_period_index).pay_period_end_date :=
966 lr_time_period_details.end_date;
967 benefit_person_detail (l_person_index).time_period (l_period_index).pay_period :=
968 lr_time_period_details.period_name;
969 benefit_person_detail (l_person_index).time_period (l_period_index).assignment_action_id :=
970 rec_prepaid_assignments.prepaid_action_id;
971 l_period_index := l_period_index + 1;
972 end if;
973
974 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
975 -- l_assignment_id := rec_prepaid_assignments.assignment_id;
976 end loop;
977
978 archive_benefit_details (benefit_person_detail, p_payroll_action_id);
979 end;
980 --
981 --
982 /* Initialization Code*/
983 procedure initialization_code (
984 p_payroll_action_id in number
985 ) is
986 begin
987 null;
988 end;
989 --
990 --
991
992 procedure archive_code (
993 p_assignment_action_id in number,
994 p_effective_date in date
995 ) is
996 begin
997 null;
998 end;
999 /* Archive Code */
1000 procedure archive_data (
1001 p_arch_assignment_action_id in number,
1002 p_assignment_action_id in number,
1003 p_assignment_id in number
1004 ) is
1005 type benefit_expense_element_rec is record (
1006 benefit_expense_type varchar2 (200),
1007 benefit_expense_monetary_value number,
1008 benefit_exp_flag char (1), -- 'B' for Benefit and 'E' for expense
1009 input_value_name pay_input_values_f.name%type,
1010 input_value pay_element_entry_values_f.screen_entry_value%type,
1011 input_value_uom pay_input_values_f.uom%type
1012 );
1013
1014 type benefit_expense_element_table is table of benefit_expense_element_rec
1015 index by binary_integer;
1016
1017 l_benefit_expense_types benefit_expense_element_table;
1018 l_index_benefit_exp_type number := 0;
1019 l_action_info_id number;
1020 l_ovn number;
1021 l_hourly_salaried_code varchar2 (100);
1022 l_tax_card_type_code varchar2 (50);
1023 l_tax_municipality_code varchar2 (100);
1024 l_tax_municipality varchar2 (200);
1025 l_base_rate number (5, 2);
1026 l_additional_rate number (5, 2);
1027 l_yearly_income_limit number (10);
1028 l_actual_tax_days number;
1029 l_year_last_date date;
1030 l_year_start_date date;
1031 l_payroll_flag char (1);
1032 l_payroll_date varchar2 (2);
1033 l_perioud_last_day date;
1034 l_assignment_id number;
1035 l_salary_income number;
1036 l_benefits_in_kind number;
1037 l_notional_salary number;
1038 l_tax_card_type varchar2 (200);
1039
1040 /*Cursor csr_temination_date is
1041 select paaf.effective_start_date - 1 term_dt
1042 from per_all_assignments_f paaf, pay_assignment_actions paa
1043 where paaf.assignment_id = paa.assignment_id
1044 and paa.assignment_action_id = p_arch_assignment_action_id
1045 and assignment_status_type_id = (select assignment_status_type_id
1046 from per_assignment_status_types
1047 where per_system_status = 'TERM_ASSIGN'
1048 and active_flag = 'Y'
1049 and legislation_code is null
1050 and business_group_id is null)
1051 and effective_end_date between g_year_start_date and g_year_last_date;*/
1052 cursor csr_asg_effective_date (
1053 p_asg_id number,
1054 p_end_date date,
1055 p_start_date date,
1056 p_business_group_id number
1057 ) is
1058 select max (effective_end_date) effective_date
1059 from per_all_assignments_f paa
1060 where assignment_id = p_asg_id
1061 and paa.effective_start_date <= p_end_date
1062 and paa.effective_end_date > = p_start_date
1063 and assignment_status_type_id in (select assignment_status_type_id
1064 from per_assignment_status_types
1065 where per_system_status = 'ACTIVE_ASSIGN'
1066 and active_flag = 'Y'
1067 and ( (legislation_code is null and business_group_id is null)
1068 or (business_group_id = p_business_group_id)
1069 ));
1070
1071 cursor csr_employee_detail (
1072 p_effective_date date
1073 ) is
1074 select papf.person_id person_id, paaf.assignment_id, national_identifier, full_name, employee_number,
1075 hourly_salaried_code, paaf.primary_flag, papf.date_of_birth, paaf.job_id, position_id
1076 from per_all_people_f papf, per_all_assignments_f paaf, pay_assignment_actions pac
1077 where pac.assignment_action_id = p_arch_assignment_action_id
1078 and paaf.assignment_id = pac.assignment_id
1079 and paaf.person_id = papf.person_id
1080 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date
1081 and p_effective_date between papf.effective_start_date and papf.effective_end_date;
1082
1083 cursor get_element_details (
1084 p_assignment_id number,
1085 p_element_name varchar2,
1086 p_input_value varchar2,
1087 p_value_date date
1088 ) is
1089 select eev1.screen_entry_value screen_entry_value
1090 from per_all_assignments_f asg1,
1091 per_all_assignments_f asg2,
1092 per_all_people_f per,
1093 pay_element_links_f el,
1094 pay_element_types_f et,
1095 pay_input_values_f iv1,
1096 pay_element_entries_f ee,
1097 pay_element_entry_values_f eev1
1098 where asg1.assignment_id = p_assignment_id
1099 and p_value_date between asg1.effective_start_date and asg1.effective_end_date
1100 and p_value_date between asg2.effective_start_date and asg2.effective_end_date
1101 and p_value_date between per.effective_start_date and per.effective_end_date
1102 and per.person_id = asg1.person_id
1103 and asg2.person_id = per.person_id
1104 and asg2.primary_flag = 'Y'
1105 and et.element_name = p_element_name --'Tax Card'
1106 and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
1107 and iv1.element_type_id = et.element_type_id
1108 and iv1.name = p_input_value
1109 and el.business_group_id = per.business_group_id
1110 and el.element_type_id = et.element_type_id
1111 and ee.assignment_id = asg2.assignment_id
1112 and ee.element_link_id = el.element_link_id
1113 and eev1.element_entry_id = ee.element_entry_id
1114 and eev1.input_value_id = iv1.input_value_id
1115 and p_value_date between ee.effective_start_date and ee.effective_end_date
1116 and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
1117
1118 cursor get_car_element_details (
1119 p_assignment_id number,
1120 p_value_date date
1121 ) is
1122 select iv1.name, eev1.effective_start_date, eev1.screen_entry_value screen_entry_value, iv1.lookup_type, uom
1123 from per_all_assignments_f asg1,
1124 per_all_assignments_f asg2,
1125 per_all_people_f per,
1126 pay_element_links_f el,
1127 pay_element_types_f et,
1128 pay_input_values_f iv1,
1129 pay_element_entries_f ee,
1130 pay_element_entry_values_f eev1
1131 where asg1.assignment_id = p_assignment_id
1132 and p_value_date between asg1.effective_start_date and asg1.effective_end_date
1133 and p_value_date between asg2.effective_start_date and asg2.effective_end_date
1134 and p_value_date between per.effective_start_date and per.effective_end_date
1135 and per.person_id = asg1.person_id
1136 and asg2.person_id = per.person_id
1137 and asg2.primary_flag = 'Y'
1138 and et.element_name = 'Car Benefit'
1139 and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
1140 and iv1.element_type_id = et.element_type_id
1141 --and iv1.name = p_input_value
1142 and el.business_group_id = per.business_group_id
1143 and el.element_type_id = et.element_type_id
1144 and ee.assignment_id = asg2.assignment_id
1145 and ee.element_link_id = el.element_link_id
1146 and eev1.element_entry_id = ee.element_entry_id
1147 and eev1.input_value_id = iv1.input_value_id
1148 and eev1.screen_entry_value is not null
1149 and p_value_date between ee.effective_start_date and ee.effective_end_date
1150 and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
1151
1152 cursor csr_get_benefit_type (
1153 p_assignment_id per_all_assignments_f.assignment_id%type
1154 ) is
1155 select pat.element_type_id, pat.element_name, pat.element_information1 benefit_type_code
1156 from pay_element_classifications pec, pay_element_types_f pat, pay_element_entries_f pet
1157 where pec.classification_name = 'Benefits in Kind'
1158 and pec.legislation_code = 'FI'
1159 and (pat.legislation_code = 'FI' or pat.business_group_id = g_business_group_id)
1160 and pec.classification_id = pat.classification_id
1161 and pat.element_type_id = pet.element_type_id
1162 and pet.assignment_id = p_assignment_id
1163 and g_pay_period_end_date between pat.effective_start_date and pat.effective_end_date
1164 and g_pay_period_end_date between pet.effective_start_date and pet.effective_end_date
1165 order by pat.element_type_id;
1166
1167 cursor csr_person_archived (
1168 p_person_id number
1169 ) is
1170 select 'Y'
1171 from pay_action_information pai, pay_assignment_actions paa
1172 where pai.action_context_id = paa.assignment_action_id
1173 and paa.payroll_action_id = g_arch_payroll_action_id
1174 and action_information1 = 'PYFIPSTA'
1175 and action_information2 = 'PERSON DETAILS'
1176 and action_information3 = to_char (p_person_id);
1177
1178 cursor csr_payroll_archived (
1179 p_person_id number
1180 ) is
1181 select 'Y'
1182 from pay_action_information pai, pay_assignment_actions paa
1183 where pai.action_context_id = paa.assignment_action_id
1184 and paa.payroll_action_id = g_arch_payroll_action_id
1185 and action_information1 = 'PYFIPSTA'
1186 and action_information2 = 'Payroll Details'
1187 and action_information3 = to_char (p_person_id)
1188 and action_information4 = to_char (g_payroll_id)
1189 and action_information5 = to_char (g_pay_period);
1190
1191 cursor csr_primary_address (
1192 p_person_id number,
1193 p_effective_date date
1194 ) is
1195 select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
1196 pa.region_2
1197 r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
1198 pa.address_line3
1199 al3, pa.postal_code postal_code
1200 from per_addresses pa
1201 where pa.primary_flag = 'Y'
1202 and pa.person_id = p_person_id
1203 and p_effective_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
1204
1205 cursor csr_permanent_address (
1206 p_person_id number,
1207 p_effective_date date
1208 ) is
1209 select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
1210 pa.region_2
1211 r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
1212 pa.address_line3
1213 al3, pa.postal_code postal_code
1214 from per_addresses pa
1215 where pa.address_type = 'FI_PR'
1216 and pa.person_id = p_person_id
1217 and p_effective_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
1218
1219 cursor csr_balance (
1220 p_balance_category_name varchar2
1221 ) is
1222 select pbt.balance_name
1223 from pay_balance_types pbt, pay_balance_categories_f pbc
1224 where pbc.legislation_code = 'FI'
1225 and pbt.balance_category_id = pbc.balance_category_id
1226 and pbt.business_group_id = g_business_group_id
1227 and pbc.category_name = p_balance_category_name;
1228
1229 cursor get_global_value (
1230 p_global_name varchar2
1231 ) is
1232 select to_number (nvl (global_value, 0))
1233 from ff_globals_f
1234 where legislation_code = 'FI' and global_name = p_global_name;
1235
1236 cursor csr_get_job (
1237 p_job_id per_jobs.job_id%type,
1238 p_effective_date date
1239 ) is
1240 select name
1241 from per_jobs
1242 where job_id = p_job_id and p_effective_date between date_from and nvl (date_to, p_effective_date);
1243
1244 cursor csr_get_position (
1245 p_position_id hr_positions_f.position_id%type,
1246 p_effective_date date
1247 ) is
1248 select name
1249 from hr_positions_f
1250 where position_id = p_position_id
1251 and p_effective_date between effective_start_date and nvl (effective_end_date, p_effective_date);
1252
1253 l_balance_name pay_balance_types.balance_name%type;
1254 rl_primary_address csr_primary_address%rowtype;
1255 l_postal_code per_addresses.postal_code%type;
1256 l_country per_addresses.country%type;
1257 rl_permanent_address csr_permanent_address%rowtype;
1258 l_permanent_postal_code per_addresses.postal_code%type;
1259 l_permanent_country per_addresses.country%type;
1260 l_payroll_id pay_action_information.action_information3%type;
1261 l_pay_period pay_action_information.action_information4%type;
1262 l_pay_period_start_date pay_action_information.action_information5%type;
1263 l_pay_period_end_date pay_action_information.action_information3%type;
1264 l_period_type pay_action_information.action_information3%type;
1265 l_run_assignment_action_id number;
1266 -- l_benefit_type_code pay_element_types_f.element_information1%type;
1267 l_benefit_type varchar2 (200);
1268 l_benefit_monetary_value number;
1269 l_sal_sub_to_tax number;
1270 l_tax_at_source number;
1271 l_withholding_tax number;
1272 l_net_salary number;
1273 l_person_archived_flag char (1) := 'N';
1274 l_withholding_tax_base number;
1275 l_tax_at_source_base number;
1276 l_deductions_b_tax number;
1277 l_external_expenses number;
1278 l_payroll_archived_flag char (1) := 'N';
1279 l_database_item_suffix pay_balance_dimensions.database_item_suffix%type;
1280 l_tax_amount number;
1281 l_termination_date date;
1282 l_effective_date date;
1283 l_monetary_value number := 0;
1284 l_lunch_benefit number := 0;
1285 l_car_benefit number := 0;
1286 l_pension number := 0;
1287 l_unemployment_insurance number := 0;
1288 l_trade_union_fee number := 0;
1289 l_job_name per_jobs.name%type;
1290 begin
1291 if g_archive = 'Y' then
1292 open csr_asg_effective_date (
1293 p_asg_id => p_assignment_id,
1294 p_end_date => g_year_last_date,
1295 p_start_date => g_year_start_date,
1296 p_business_group_id => g_business_group_id
1297 );
1298 fetch csr_asg_effective_date into l_termination_date;
1299 close csr_asg_effective_date;
1300
1301 if l_termination_date < g_year_last_date then
1302 l_effective_date := l_termination_date;
1303 else
1304 l_effective_date := g_year_last_date;
1305 end if;
1306
1307 for i in csr_employee_detail (l_effective_date)
1308 loop
1309 open csr_person_archived (i.person_id);
1310 fetch csr_person_archived into l_person_archived_flag;
1311 close csr_person_archived;
1312
1313 if l_person_archived_flag = 'N' then --*/ i.primary_flag = 'Y' then
1314 -- g_person_id := i.person_id;
1315 /* Initialize tax card details to null for each assignment */
1316 l_tax_card_type_code := null;
1317 l_tax_card_type := null;
1318 l_tax_municipality := null;
1319 l_tax_municipality_code := null;
1320 l_base_rate := null;
1321 l_additional_rate := null;
1322 l_yearly_income_limit := null;
1323 l_assignment_id := i.assignment_id;
1324 l_benefit_type := null;
1325 l_benefit_monetary_value := null;
1326 l_job_name := null;
1327
1328 --
1329 --
1330 if i.job_id is not null then
1331 open csr_get_job (i.job_id, l_effective_date);
1332 fetch csr_get_job into l_job_name;
1333 close csr_get_job;
1334 elsif i.position_id is not null then
1335 open csr_get_position (i.position_id, l_effective_date);
1336 fetch csr_get_position into l_job_name;
1337 close csr_get_position;
1338 end if;
1339
1340 /* Get the tax card type */
1341 open get_element_details (i.assignment_id, 'Tax Card', 'Tax Card Type', l_effective_date);
1342 fetch get_element_details into l_tax_card_type_code;
1343 close get_element_details;
1344
1345 --
1346 --
1347 if l_tax_card_type_code is not null then
1348 l_tax_card_type := hr_general.decode_lookup ('FI_TAX_CARD_TYPE', l_tax_card_type_code);
1349 else
1350 l_tax_card_type := null;
1351 end if;
1352
1353 /* Get the Tax Municipality */
1354 open get_element_details (i.assignment_id, 'Tax Card', 'Tax Municipality', l_effective_date);
1355 fetch get_element_details into l_tax_municipality_code;
1356 close get_element_details;
1357
1358 --
1359 --
1360 l_tax_municipality := hr_general.decode_lookup ('FI_TAX_MUNICIPALITY', l_tax_municipality_code);
1361 /* Get the Base Rate */
1362 open get_element_details (i.assignment_id, 'Tax Card', 'Base Rate', l_effective_date);
1363 fetch get_element_details into l_base_rate;
1364 close get_element_details;
1365 --
1366 --
1367 /* Get the Additional Rate */
1368 open get_element_details (i.assignment_id, 'Tax Card', 'Additional Rate', l_effective_date);
1369 fetch get_element_details into l_additional_rate;
1370 close get_element_details;
1371
1372 --
1373 --
1374
1375 /* If the Tax Card type is cumulative */
1376 if l_tax_card_type_code = 'C' then
1377 /* Get the Yearly Income Limit */
1378 open get_element_details (i.assignment_id, 'Tax Card', 'Yearly Income Limit', l_effective_date);
1379 fetch get_element_details into l_yearly_income_limit;
1380 close get_element_details;
1381 --
1382 --
1383 /* When Tax Card Type = No Tax Card*/
1384 elsif l_tax_card_type_code = 'NTC' then
1385 l_base_rate := null;
1386 open get_global_value ('FI_PUNITIVE_TAX_PCT');
1387 fetch get_global_value into l_base_rate;
1388 close get_global_value;
1389 --
1390 --
1391 /* When Tax Card Type = Tax-at-Source */
1392 elsif l_tax_card_type_code = 'TS' and l_base_rate is null then
1393 open get_global_value ('FI_TAX_AT_SOURCE_PCT');
1394 fetch get_global_value into l_base_rate;
1395 close get_global_value;
1396 --
1397 --
1398 /* If Tax Card Type is Extra Income then the Base Rate and Additonal Rate will be taken from Tax Element */
1399 elsif l_tax_card_type_code = 'EI' then
1400 l_base_rate := null;
1401 l_additional_rate := null;
1402 open get_element_details (i.assignment_id, 'Tax', 'Extra Income Rate', l_effective_date);
1403 fetch get_element_details into l_base_rate;
1404 close get_element_details;
1405 open get_element_details (i.assignment_id, 'Tax', 'Extra Income Additional Rate', l_effective_date);
1406 fetch get_element_details into l_additional_rate;
1407 close get_element_details;
1408 end if;
1409
1410 /* Set the context for Date Earned as the Effective Date */
1411 pay_balance_pkg.set_context ('DATE_EARNED', fnd_date.date_to_canonical (g_year_last_date));
1412 --
1413 --
1414 /* Set the context forTAX_UNIT_ID as the Legal Employer Id */
1415 pay_balance_pkg.set_context ('TAX_UNIT_ID', g_legal_employer_id);
1416 pay_balance_pkg.set_context ('SOURCE_TEXT', null);
1417 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID', p_assignment_action_id);
1418 pay_balance_pkg.set_context ('ASSIGNMENT_ID', i.assignment_id);
1419
1420 --
1421 --
1422
1423
1424 if g_local_unit_id is null then
1425 l_database_item_suffix := '_PER_LE_YTD';
1426 elsif g_local_unit_id is not null then
1427 l_database_item_suffix := '_PER_LU_YTD';
1428 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', g_local_unit_id);
1429 end if;
1430
1431 /* Get the value for balnace Actual Tax Days */
1432 l_actual_tax_days := get_balance_value (
1433 p_balance_name => 'Actual Tax Days',
1434 p_assignment_id => i.assignment_id,
1435 p_database_item_suffix => l_database_item_suffix,
1436 p_bal_date => l_effective_date
1437 );
1438 l_notional_salary := get_balance_value (
1439 p_balance_name => 'Notional Salary',
1440 p_assignment_id => i.assignment_id,
1441 p_database_item_suffix => l_database_item_suffix,
1442 p_bal_date => l_effective_date
1443 );
1444
1445 /* Get the Type of Benefit in Kind*/
1446 if i.hourly_salaried_code is not null then
1447 l_hourly_salaried_code := hr_general.decode_lookup ('HOURLY_SALARIED_CODE', i.hourly_salaried_code);
1448 else
1449 l_hourly_salaried_code := null;
1450 end if;
1451
1452 open csr_primary_address (i.person_id, l_effective_date);
1453 fetch csr_primary_address into rl_primary_address;
1454 close csr_primary_address;
1455
1456 if rl_primary_address.style = 'FI' then
1457 l_postal_code := hr_general.decode_lookup ('FI_POSTAL_CODE', rl_primary_address.postal_code);
1458 else
1459 l_postal_code := rl_primary_address.postal_code;
1460 end if;
1461
1462 l_country := pay_fi_archive_psta.get_country_name (rl_primary_address.country);
1463 /* Retrieve the Permanent Address*/
1464 open csr_permanent_address (i.person_id, l_effective_date);
1465 fetch csr_permanent_address into rl_permanent_address;
1466 close csr_permanent_address;
1467
1468 if rl_permanent_address.style = 'FI' then
1469 l_permanent_postal_code := hr_general.decode_lookup ('FI_POSTAL_CODE', rl_permanent_address.postal_code);
1470 else
1471 l_permanent_postal_code := rl_permanent_address.postal_code;
1472 end if;
1473
1474 l_permanent_country := pay_fi_archive_psta.get_country_name (rl_permanent_address.country);
1475 /* Archive Person Details */
1476 pay_action_information_api.create_action_information (
1477 p_action_information_id => l_action_info_id,
1478 p_action_context_id => p_arch_assignment_action_id,
1479 p_action_context_type => 'AAP',
1480 p_object_version_number => l_ovn,
1481 p_effective_date => g_effective_date,
1482 p_source_id => null,
1483 p_source_text => null,
1484 p_action_information_category => 'EMEA REPORT INFORMATION',
1485 p_action_information1 => 'PYFIPSTA',
1486 p_action_information2 => 'PERSON DETAILS',
1487 p_action_information3 => i.person_id,
1488 p_action_information4 => i.national_identifier,
1489 p_action_information5 => i.full_name,
1490 p_action_information6 => i.employee_number,
1491 p_action_information7 => l_hourly_salaried_code,
1492 p_action_information8 => l_tax_card_type,
1493 p_action_information9 => l_tax_municipality,
1494 p_action_information10 => fnd_number.number_to_canonical (l_base_rate),
1495 p_action_information11 => fnd_number.number_to_canonical (l_additional_rate),
1496 p_action_information12 => fnd_number.number_to_canonical (l_yearly_income_limit),
1497 p_action_information13 => fnd_number.number_to_canonical (l_actual_tax_days),
1498 p_action_information14 => fnd_number.number_to_canonical (l_notional_salary),
1499 p_action_information15 => rl_primary_address.al1,
1500 p_action_information16 => rl_primary_address.al2,
1501 p_action_information17 => rl_primary_address.al3,
1502 p_action_information18 => l_postal_code,
1503 p_action_information19 => l_country,
1504 p_action_information20 => g_emp_local_unit_id,
1505 p_action_information21 => fnd_date.date_to_canonical (i.date_of_birth),
1506 p_action_information22 => l_job_name,
1507 p_action_information23 => rl_permanent_address.al1,
1508 p_action_information24 => rl_permanent_address.al2,
1509 p_action_information25 => rl_permanent_address.al3,
1510 p_action_information26 => l_permanent_postal_code,
1511 p_action_information27 => l_permanent_country,
1512 p_assignment_id => i.assignment_id
1513 );
1514 /* Call procedure to archive person details
1515 pay_fi_archive_psta.archive_person_address_details (
1516 p_person_id => i.person_id,
1517 p_assignment_action_id => p_arch_assignment_action_id,
1518 p_assignment_id => i.assignment_id
1519 );*/
1520 end if;
1521
1522 open csr_payroll_archived (i.person_id);
1523 fetch csr_payroll_archived into l_payroll_archived_flag;
1524 close csr_payroll_archived;
1525
1526 if l_payroll_archived_flag = 'N' then
1527 /* Set the context for Date Earned as the Effective Date */
1528 pay_balance_pkg.set_context ('DATE_EARNED', g_pay_period_end_date);
1529 --
1530 --
1531 l_database_item_suffix := null;
1532
1533 if g_local_unit_id is null then
1534 l_database_item_suffix := '_PER_LE_PTD';
1535 elsif g_local_unit_id is not null then
1536 l_database_item_suffix := '_PER_LU_PTD';
1537 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', g_local_unit_id);
1538 end if;
1539
1540 l_salary_income := get_balance_value (
1541 p_balance_name => 'Salary Income',
1542 p_assignment_id => i.assignment_id,
1543 p_database_item_suffix => l_database_item_suffix,
1544 p_bal_date => g_pay_period_end_date
1545 );
1546 l_benefits_in_kind := get_balance_value (
1547 p_balance_name => 'Benefits in Kind',
1548 p_assignment_id => i.assignment_id,
1549 p_database_item_suffix => l_database_item_suffix,
1550 p_bal_date => g_pay_period_end_date
1551 );
1552 l_deductions_b_tax := get_balance_value (
1553 p_balance_name => 'Deductions Before Tax',
1554 p_assignment_id => i.assignment_id,
1555 p_database_item_suffix => l_database_item_suffix,
1556 p_bal_date => g_pay_period_end_date
1557 );
1558 /* Standard Deductions */
1559 l_pension := get_balance_value (
1560 p_balance_name => 'Pension',
1561 p_assignment_id => i.assignment_id,
1562 p_database_item_suffix => l_database_item_suffix,
1563 p_bal_date => g_pay_period_end_date
1564 );
1565 l_unemployment_insurance := get_balance_value (
1566 p_balance_name => 'Unemployment Insurance',
1567 p_assignment_id => i.assignment_id,
1568 p_database_item_suffix => l_database_item_suffix,
1569 p_bal_date => g_pay_period_end_date
1570 );
1571 l_trade_union_fee := get_balance_value (
1572 p_balance_name => 'Cumulative Trade Union Membership Fees',
1573 p_assignment_id => i.assignment_id,
1574 p_database_item_suffix => l_database_item_suffix,
1575 p_bal_date => g_pay_period_end_date
1576 );
1577 /* End of Standard Deductions*/
1578 l_external_expenses := get_balance_value (
1579 p_balance_name => 'External Expenses',
1580 p_assignment_id => i.assignment_id,
1581 p_database_item_suffix => l_database_item_suffix,
1582 p_bal_date => g_pay_period_end_date
1583 );
1584 l_withholding_tax_base := get_balance_value (
1585 p_balance_name => 'Withholding Tax Base',
1586 p_assignment_id => i.assignment_id,
1587 p_database_item_suffix => l_database_item_suffix,
1588 p_bal_date => g_pay_period_end_date
1589 );
1590 l_tax_at_source_base := get_balance_value (
1591 p_balance_name => 'Tax at Source Base',
1592 p_assignment_id => i.assignment_id,
1593 p_database_item_suffix => l_database_item_suffix,
1594 p_bal_date => g_pay_period_end_date
1595 );
1596 l_tax_at_source := get_balance_value (
1597 p_balance_name => 'Tax at Source',
1598 p_assignment_id => i.assignment_id,
1599 p_database_item_suffix => l_database_item_suffix,
1600 p_bal_date => g_pay_period_end_date
1601 );
1602 l_withholding_tax := get_balance_value (
1603 p_balance_name => 'Withholding Tax',
1604 p_assignment_id => i.assignment_id,
1605 p_database_item_suffix => l_database_item_suffix,
1606 p_bal_date => g_pay_period_end_date
1607 );
1608 l_net_salary := get_balance_value (
1609 p_balance_name => 'Net Pay',
1610 p_assignment_id => i.assignment_id,
1611 p_database_item_suffix => l_database_item_suffix,
1612 p_bal_date => g_pay_period_end_date
1613 );
1614 l_car_benefit := get_balance_value (
1615 p_balance_name => 'Cumulative Car Benefit',
1616 p_assignment_id => i.assignment_id,
1617 p_database_item_suffix => l_database_item_suffix,
1618 p_bal_date => g_pay_period_end_date
1619 );
1620
1621 if l_withholding_tax_base > 0 then
1622 l_sal_sub_to_tax := nvl (l_withholding_tax_base, 0);
1623 elsif l_tax_at_source_base > 0 then
1624 l_sal_sub_to_tax := nvl (l_tax_at_source_base, 0);
1625 else
1626 l_sal_sub_to_tax := 0;
1627 end if;
1628
1629 if l_withholding_tax > 0 then
1630 l_tax_amount := nvl (l_withholding_tax, 0);
1631 elsif l_tax_at_source > 0 then
1632 l_tax_amount := nvl (l_tax_at_source, 0);
1633 else
1634 l_tax_amount := 0;
1635 end if;
1636
1637 pay_action_information_api.create_action_information (
1638 p_action_information_id => l_action_info_id,
1639 p_action_context_id => p_arch_assignment_action_id,
1640 p_action_context_type => 'AAP',
1641 p_object_version_number => l_ovn,
1642 p_effective_date => g_effective_date,
1643 p_source_id => null,
1644 p_source_text => null,
1645 p_action_information_category => 'EMEA REPORT INFORMATION',
1646 p_action_information1 => 'PYFIPSTA',
1647 p_action_information2 => 'Payroll Details',
1648 p_action_information3 => i.person_id,
1649 p_action_information4 => g_payroll_id,
1650 p_action_information5 => g_pay_period,
1651 p_action_information6 => fnd_date.date_to_canonical (g_pay_period_start_date),
1652 p_action_information7 => fnd_date.date_to_canonical (g_pay_period_end_date),
1653 p_action_information8 => g_period_type,
1654 p_action_information9 => fnd_number.number_to_canonical (l_salary_income),
1655 p_action_information10 => fnd_number.number_to_canonical (l_benefits_in_kind),
1656 /* p_action_information11 => l_benefit_type,
1657 p_action_information12 => fnd_number.number_to_canonical (
1658 nvl (
1659 l_benefit_expense_monetary_value,
1660 0
1661 )
1662 ),*/
1663 p_action_information13 => fnd_number.number_to_canonical (l_sal_sub_to_tax),
1664 p_action_information14 => fnd_number.number_to_canonical (l_tax_amount),
1665 p_action_information15 => fnd_number.number_to_canonical (l_net_salary),
1666 p_action_information16 => fnd_number.number_to_canonical (l_deductions_b_tax),
1667 p_action_information17 => fnd_number.number_to_canonical (l_external_expenses),
1668 p_action_information18 => fnd_number.number_to_canonical (g_time_period_id),
1669 p_action_information19 => fnd_number.number_to_canonical (l_pension),
1670 p_action_information20 => fnd_number.number_to_canonical (l_unemployment_insurance),
1671 p_action_information21 => fnd_number.number_to_canonical (l_trade_union_fee),
1672 p_action_information22 => fnd_number.number_to_canonical (l_car_benefit),
1673 p_assignment_id => i.assignment_id
1674 );
1675 end if;
1676 end loop;
1677 end if;
1678 end;
1679
1680 procedure archive_person_address_details (
1681 p_person_id number,
1682 p_assignment_action_id number,
1683 p_assignment_id number
1684 ) is
1685 /* Cursor to retrieve primary address of Employee */
1686 cursor csr_primary_address (
1687 p_person_id number
1688 ) is
1689 select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
1690 pa.region_2
1691 r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
1692 pa.address_line3
1693 al3, pa.postal_code postal_code
1694 from per_addresses pa
1695 where pa.primary_flag = 'Y'
1696 and pa.person_id = p_person_id
1697 and g_year_last_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
1698
1699 /* Cursor to retrieve permanent address of Employee */
1700 cursor csr_permanent_address (
1701 p_person_id number
1702 ) is
1703 select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
1704 pa.region_2
1705 r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
1706 pa.address_line3
1707 al3, pa.postal_code postal_code
1708 from per_addresses pa
1709 where pa.address_type = 'FI_PR'
1710 and pa.person_id = p_person_id
1711 and g_year_last_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
1712
1713 rl_primary_address csr_primary_address%rowtype;
1714 l_postal_code per_addresses.postal_code%type;
1715 l_country per_addresses.country%type;
1716 rl_permanent_address csr_primary_address%rowtype;
1717 l_permanent_postal_code per_addresses.postal_code%type;
1718 l_permanent_country per_addresses.country%type;
1719 l_action_info_id number;
1720 l_ovn number;
1721 begin
1722 open csr_primary_address (p_person_id);
1723 fetch csr_primary_address into rl_primary_address;
1724 close csr_primary_address;
1725
1726 if rl_primary_address.style = 'FI' then
1727 l_postal_code := hr_general.decode_lookup ('FI_POSTAL_CODE', rl_primary_address.postal_code);
1728 else
1729 l_postal_code := rl_primary_address.postal_code;
1730 end if;
1731
1732 l_country := pay_fi_archive_psta.get_country_name (rl_primary_address.country);
1733 /* Retrieve the Permanent Address*/
1734 open csr_permanent_address (p_person_id);
1735 fetch csr_permanent_address into rl_permanent_address;
1736 close csr_permanent_address;
1737
1738 if rl_permanent_address.style = 'FI' then
1739 l_permanent_postal_code := hr_general.decode_lookup ('FI_POSTAL_CODE', rl_permanent_address.postal_code);
1740 else
1741 l_permanent_postal_code := rl_permanent_address.postal_code;
1742 end if;
1743
1744 l_permanent_country := pay_fi_archive_psta.get_country_name (rl_permanent_address.country);
1745 pay_action_information_api.create_action_information (
1746 p_action_information_id => l_action_info_id,
1747 p_action_context_id => p_assignment_action_id,
1748 p_action_context_type => 'AAP',
1749 p_object_version_number => l_ovn,
1750 p_effective_date => g_effective_date,
1751 p_source_id => null,
1752 p_source_text => null,
1753 p_action_information_category => 'EMEA REPORT INFORMATION',
1754 p_action_information1 => 'PYFIPSTA',
1755 p_action_information2 => 'ADDRESS DETAILS',
1756 p_action_information3 => p_person_id,
1757 p_action_information4 => rl_primary_address.al1,
1758 p_action_information5 => rl_primary_address.al2,
1759 p_action_information6 => rl_primary_address.al3,
1760 p_action_information7 => l_postal_code,
1761 p_action_information8 => l_country,
1762 p_action_information9 => rl_permanent_address.al1,
1763 p_action_information10 => rl_permanent_address.al2,
1764 p_action_information11 => rl_permanent_address.al3,
1765 p_action_information12 => l_permanent_postal_code,
1766 p_action_information13 => l_permanent_country,
1767 p_assignment_id => p_assignment_id
1768 );
1769 end;
1770
1771 function get_country_name (
1772 p_territory_code varchar2
1773 )
1774 return varchar2 is
1775 cursor csr_get_territory_name (
1776 p_territory_code varchar2
1777 ) is
1778 select territory_short_name
1779 from fnd_territories_vl
1780 where territory_code = p_territory_code;
1781
1782 l_country fnd_territories_vl.territory_short_name%type;
1783 begin
1784 if g_debug then
1785 hr_utility.set_location (' Entering Function GET_COUNTRY_NAME', 140);
1786 end if;
1787
1788 open csr_get_territory_name (p_territory_code);
1789 fetch csr_get_territory_name into l_country;
1790 close csr_get_territory_name;
1791 return l_country;
1792
1793 if g_debug then
1794 hr_utility.set_location (' Leaving Function GET_COUNTRY_NAME', 150);
1795 end if;
1796 end get_country_name;
1797
1798 function get_balance_value (
1799 p_balance_name in varchar2,
1800 p_assignment_id in number,
1801 p_database_item_suffix in varchar2,
1802 p_bal_date in date
1803 )
1804 return number is
1805 --
1806 --
1807 /* Cursor to get the defined balance id */
1808 cursor csr_get_defined_balance_id (
1809 csr_v_balance_name ff_database_items.user_name%type
1810 ) is
1811 select defined_balance_id
1812 from pay_balance_types pbt, pay_balance_dimensions pbd, pay_defined_balances pdb
1813 where pbt.balance_name = csr_v_balance_name
1814 and nvl(pbt.business_group_id,g_business_group_id) = g_business_group_id
1815 and pbt.balance_type_id = pdb.balance_type_id
1816 and pbd.database_item_suffix = p_database_item_suffix --'_PER_YTD'
1817 and pbd.legislation_code = 'FI'
1818 and pbd.balance_dimension_id = pdb.balance_dimension_id;
1819
1820 l_get_defined_balance_id number;
1821 begin
1822 /* Get teh defined Balance ID */
1823 open csr_get_defined_balance_id (p_balance_name);
1824 fetch csr_get_defined_balance_id into l_get_defined_balance_id;
1825 close csr_get_defined_balance_id;
1826 --
1827 --
1828 /* Get the Balance value and return it */
1829 --
1830 --
1831 return (pay_balance_pkg.get_value (
1832 p_defined_balance_id => l_get_defined_balance_id,
1833 p_assignment_id => p_assignment_id,
1834 p_virtual_date => p_bal_date --g_effective_date
1835 )
1836 );
1837 exception
1838 when others then
1839 hr_utility.trace ('SQLERR - ' || sqlerrm);
1840 end get_balance_value;
1841 --
1842 --
1843 end pay_fi_archive_psta;