[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_IRAS_ARCHIVE
Source
1 package body pay_sg_iras_archive as
2 /* $Header: pysgirar.pkb 120.19.12010000.4 2008/08/06 08:22:52 ubhat ship $ */
3 -----------------------------------------------------------------------------
4 -- These are PUBLIC procedures that are used within this package.
5 -----------------------------------------------------------------------------
6 g_debug boolean ;
7 g_business_group_id varchar2(20) ;
8 g_basis_end date;
9 g_basis_start date;
10 g_basis_year varchar2(4);
11 g_legal_entity_id varchar2(20);
12 g_person_id per_all_people_f.person_id%type;
13 g_assignment_set_id hr_assignment_sets.assignment_set_id%type;
14 g_setup_action_id pay_payroll_actions.payroll_action_id%type;
15 g_report_type varchar2(30);
16 g_previous_person_id per_all_people_f.person_id%type;
17 g_moa_369_date ff_archive_items.value%type;
18 -- Added for bug 5435088 org cursor only need to run once
19 g_name_of_bank ff_archive_items.value%type;
20 g_org_run char(1);
21 g_org_a8a_run char(1);
22 g_national_identifier per_all_people_f.national_identifier%type;
23 g_legal_entity_name hr_organization_information.org_information1%type;
24 g_er_income_tax_number hr_organization_information.org_information4%type;
25 g_er_auth_person hr_organization_information.org_information10%type;
26 g_er_auth_person_desig hr_organization_information.org_information11%type;
27 g_er_ohq_status hr_organization_information.org_information12%type;
28 g_er_iras_category hr_organization_information.org_information13%type;
29 g_er_telephone_no hr_organization_information.org_information14%type;
30 g_er_payer_id hr_organization_information.org_information15%type;
31 g_er_designation_type hr_organization_information.org_information17%type;
32 g_er_position_seg_type hr_organization_information.org_information18%type;
33 g_er_bonus_date hr_organization_information.org_information8%type;
34 g_er_auth_person_email hr_organization_information.org_information5%type;
35 g_er_division hr_organization_information.org_information8%type;
36 g_er_payer_id_check char(1);
37 l_counter number;
38 --------------------------------------------------------------------------------------------------------
39 -- Bug# 3501927 A8A Balance store rec
40 --------------------------------------------------------------------------------------------------------
41 type ytd_a8a_balance_store_rec is record
42 ( balance_id ff_user_entities.user_entity_id%type,
43 balance_value number );
44 type ytd_a8a_balance_tab is table of ytd_a8a_balance_store_rec index by binary_integer;
45 ytd_a8a_balance_rec ytd_a8a_balance_tab;
46 -- Bug# 3933332
47 g_org_a8a_flag char(1);
48
49 -----------------------------------------------------------------------------
50 -- The SELECT statement in this procedure returns the Person Ids for
51 -- Assignments that require the archive process to create an Assignment
52 -- Action.
53 -- Core Payroll recommends the select has minimal restrictions.
54 -----------------------------------------------------------------------------
55 procedure range_code
56 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
57 p_sql out nocopy varchar2 )
58 is
59 begin
60 if g_debug then
61 hr_utility.set_location(' Start of range_code',1);
62 end if;
63 --
64 p_sql := 'select distinct person_id ' ||
65 'from per_people_f ppf, ' ||
66 'pay_payroll_actions ppa ' ||
67 'where ppa.payroll_action_id = :payroll_action_id ' ||
68 'and ppa.business_group_id = ppf.business_group_id ' ||
69 'order by ppf.person_id';
70 --
71 if g_debug then
72 hr_utility.set_location('End of range_code',2);
73 end if;
74 end range_code;
75 ----------------------------------------------------------------------------
76 -- Bug 3435334 - Pre-processor process now introduced for this archive.
77 -- Assignment actions are created for all assignments processed by pre-processor
78 ----------------------------------------------------------------------------
79 procedure assignment_action_code
80 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
81 p_start_person_id in per_all_people_f.person_id%type,
82 p_end_person_id in per_all_people_f.person_id%type,
83 p_chunk in number )
84 is
85 v_next_action_id pay_assignment_actions.assignment_action_id%type;
86 v_setup_action_id pay_payroll_actions.payroll_action_id%type;
87 v_assignment_id per_all_assignments_f.assignment_id%type;
88 --
89 cursor get_params(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
90 select pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters)
91 from pay_payroll_actions
92 where payroll_action_id = c_payroll_Action_id;
93 --
94 cursor next_action_id is
95 select pay_assignment_actions_s.nextval
96 from dual;
97 --
98 cursor process_assignments ( c_setup_action_id in pay_payroll_actions.payroll_action_id%type ) is
99 select distinct pai.assignment_id
100 from pay_action_information pai
101 where pai.action_context_id = c_setup_action_id
102 and pai.action_context_type = 'AAP'
103 and pai.action_information_category = 'SG_IRAS_SETUP'
104 and action_information2 between p_start_person_id and p_end_person_id ;
105 begin
106 if g_debug then
107 hr_utility.set_location('Start of assignment_action_code',3);
108 end if;
109 --
110 open get_params( p_payroll_action_id );
111 fetch get_params into v_setup_action_id;
112 close get_params;
113 --
114 open process_assignments( v_setup_action_id ) ;
115 loop
116 fetch process_assignments into v_assignment_id;
117 exit when process_assignments%notfound;
118 --
119 if g_debug then
120 hr_utility.set_location('Before calling hr_nonrun_asact.insact',4);
121 end if;
122 --
123 open next_action_id ;
124 fetch next_action_id into v_next_action_id;
125 close next_action_id;
126 --
127 hr_nonrun_asact.insact( v_next_action_id,
128 v_assignment_id,
129 p_payroll_action_id,
130 p_chunk,
131 null );
132 --
133 if g_debug then
134 hr_utility.set_location('After calling hr_nonrun_asact.insact',4);
135 end if;
136 end loop;
137 --
138 close process_assignments;
139 --
140 if g_debug then
141 hr_utility.set_location('End of assignment_action_code',5);
142 end if;
143 end assignment_action_code;
144 ------------------------------------------------------------------------
145 -- Bug 3435334 - Pre-processor process now introduced for this archive.
146 -- Populating PL/SQL table logic with rehire query is removed
147 ------------------------------------------------------------------------
148 procedure initialization_code
149 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
150 is
151 cursor get_params( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type ) is
152 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
153 to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
154 to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
155 pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
156 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
157 pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters),
158 report_type
159 from pay_payroll_actions
160 where payroll_action_id = c_payroll_action_id;
161 ------------------------------------------------------------------------
162 -- Bug 3933332 - Get A8A_Applicable flag
163 ------------------------------------------------------------------------
164 cursor get_org_a8a_applicable
165 is
166 select org_information19
167 from hr_organization_information,
168 pay_payroll_actions
169 where org_information_context ='SG_LEGAL_ENTITY'
170 and organization_id = pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters)
171 and payroll_action_id = p_payroll_action_id;
172 begin
173 if g_debug then
174 hr_utility.set_location('pysgirar: Start of initialization_code',6);
175 end if;
176 --
177 if g_business_group_id is null then
178 open get_params( p_payroll_action_id );
179 fetch get_params
180 into g_business_group_id,
181 g_basis_start,
182 g_basis_end,
183 g_basis_year,
184 g_legal_entity_id,
185 g_setup_action_id,
186 g_report_type ;
187 close get_params;
188 end if;
189 ------------------------------------------------------------------------
190 -- Bug 3933332 - Get A8A_Applicable flag
191 ------------------------------------------------------------------------
192 if g_org_a8a_run <> 'Y' then
193 open get_org_a8a_applicable;
194 fetch get_org_a8a_applicable into g_org_a8a_flag;
195 close get_org_a8a_applicable;
196 g_org_a8a_run := 'Y';
197 end if;
198
199 if g_debug then
200 hr_utility.set_location('pysgirar: End of initialization_code',8);
201 end if;
202 end initialization_code;
203 --------------------------------------------------------------------------------
204 -- Bug: 3118540 - This function is called from SRS 'IR8S Ad Hoc Printed Archive'
205 --------------------------------------------------------------------------------
206 procedure assignment_action_code_adhoc
207 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
208 p_start_person_id in per_all_people_f.person_id%type,
209 p_end_person_id in per_all_people_f.person_id%type,
210 p_chunk in number )
211 is
212 v_next_action_id pay_assignment_actions.assignment_action_id%type;
213 v_person_id per_all_people_f.person_id%type;
214 v_assignment_set_id hr_assignment_sets.assignment_set_id%type;
215 v_business_group_id number;
216 v_basis_start date;
217 v_basis_end date;
218 v_legal_entity_id number;
219 v_basis_year number;
220 v_asg_id per_all_assignments_f.assignment_id%type;
221 ----------------------------------------------------------------------------
222 -- Cursor to get the values of archive parameters
223 ----------------------------------------------------------------------------
224 cursor get_params(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
225 select pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
226 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
227 to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
228 to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
229 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
230 pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
231 pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters)
232 from pay_payroll_actions
233 where payroll_action_id = c_payroll_action_id;
234 ----------------------------------------------------------------------------
235 -- Cursor Next Assignment Action
236 ----------------------------------------------------------------------------
237 cursor next_action_id is
238 select pay_assignment_actions_s.nextval
239 from dual;
240 ----------------------------------------------------------------------------
241 -- Cursor Process_assignments
242 -- Bug: 3404526 - Added max(assignment_id) to pick the latest assignment in case of Normal rehire.
243 -- Bug#3614563 Removed the Business Group id check from inner query to imporove the performence.
244 ----------------------------------------------------------------------------
245 cursor process_assignments
246 ( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
247 c_start_person_id in per_all_people_f.person_id%type,
248 c_end_person_id in per_all_people_f.person_id%type,
249 c_person_id in per_all_people_f.person_id%type,
250 c_assignment_set_id in hr_assignment_sets.assignment_set_id%type,
251 c_business_group_id in number,
252 c_legal_entity_id in number,
253 c_basis_start in date,
254 c_basis_end in date) is
255 select max(paf.assignment_id)
256 from per_assignments_f paf,
257 pay_payroll_actions ppa1
258 where ppa1.payroll_action_id = c_payroll_action_id
259 and paf.person_id between c_start_person_id and c_end_person_id
260 and ppa1.business_group_id = paf.business_group_id
261 and paf.person_id = nvl(c_person_id,paf.person_id)
262 and paf.assignment_type = 'E' /* Bug 5033609 */
263 and decode(c_assignment_set_id,null,'Y',
264 decode(hr_assignment_set.ASSIGNMENT_IN_SET(c_assignment_set_id,paf.assignment_id),'Y','Y','N')) = 'Y'
265 and exists
266 ( select null
267 from pay_payroll_actions ppa,
268 pay_assignment_actions paa
269 where ppa.payroll_action_id = paa.payroll_action_id
270 and paa.assignment_id = paf.assignment_id
271 and paa.tax_unit_id = c_legal_entity_id
272 and ppa.effective_date between c_basis_start and c_basis_end
273 and ppa.action_type in ('R','B','I','Q','V')
274 and ppa.action_status = 'C'
275 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date )
276 group by paf.person_id;
277
278 begin
279 if g_debug then
280 hr_utility.set_location('pysgirar: Start of Assignemnt Action Code Adhoc', 20);
281 end if;
282 --
283 open get_params(p_payroll_action_id);
284 fetch get_params into v_person_id,
285 v_business_group_id,
286 v_basis_start,
287 v_basis_end,
288 v_legal_entity_id,
289 v_basis_year,
290 v_assignment_set_id ;
291 close get_params;
292 --
293 open process_assignments( p_payroll_action_id,
294 p_start_person_id,
295 p_end_person_id,
296 v_person_id,
297 v_assignment_set_id,
298 v_business_group_id,
299 v_legal_entity_id,
300 v_basis_start,
301 v_basis_end );
302 loop
303 fetch process_assignments into v_asg_id;
304 exit when process_assignments%NOTFOUND;
305 --
306 open next_action_id;
307 fetch next_action_id into v_next_action_id;
308 close next_action_id;
309 --
310 hr_nonrun_asact.insact( v_next_action_id,
311 v_asg_id,
312 p_payroll_action_id,
313 p_chunk,
314 null );
315 end loop;
316 close process_assignments;
317 --
318 if g_debug then
319 hr_utility.set_location('pysgirar: End of Assignemnt Action Code Adhoc', 20);
320 end if;
321 exception
322 when others then
323 hr_utility.set_location('pysgirar: Error in assignment action code adhoc',10);
324 raise;
325 end assignment_action_code_adhoc;
326 --------------------------------------------------------------------------------
327 -- Bug: 3118540 - This function is called from SRS 'IR8S Ad Hoc Printed Archive'
328 -- Bug 3435334 - Fetching report_type into g_report_type which helps to identify
329 -- which process is running IR8S adhoc archive/main archive process
330 --------------------------------------------------------------------------------
331 procedure initialization_code_adhoc
332 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
333 is
334 cursor get_params( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type ) is
335 select pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
336 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
337 to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
338 to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
339 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
340 pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
341 pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters),
342 report_type
343 from pay_payroll_actions
344 where payroll_action_id = c_payroll_action_id;
345 --
346 begin
347 if g_debug then
348 hr_utility.set_location('pysgirar: Start of initialization_code',6);
349 end if;
350 ------------------------------------------------------------------------
351 -- Cursor Get Params
352 ------------------------------------------------------------------------
353 if g_business_group_id is null then
354 open get_params( p_payroll_action_id );
355 fetch get_params
356 into g_person_id,
357 g_business_group_id,
358 g_basis_start,
359 g_basis_end,
360 g_legal_entity_id,
361 g_basis_year,
362 g_assignment_set_id,
363 g_report_type ;
364 close get_params;
365 end if;
366 --
367 if g_debug then
368 hr_utility.set_location('pysgirar: End of initialization_code',8);
369 end if;
370 end initialization_code_adhoc;
371 -----------------------------------------------------------------------------------------------------
372 -- Bug: 3118540 - This function is called from the report PYSG8SAD.rdf - 'IR8S Ad Hoc Printed Report'
373 -----------------------------------------------------------------------------------------------------
374 function get_archive_value
375 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
376 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type) return varchar2
377 is
378 cursor csr_get_value( p_user_entity_name varchar2,
379 p_assignment_action_id number ) is
380 select fai.value
381 from ff_archive_items fai,
382 ff_user_entities fue
383 where fai.context1 = p_assignment_action_id
384 and fai.user_entity_id = fue.user_entity_id
385 and fue.user_entity_name = p_user_entity_name;
386 --
387 l_value ff_archive_items.value%type;
388 e_no_value_found exception;
389 begin
390 open csr_get_value ( p_user_entity_name,
391 p_assignment_action_id );
392 fetch csr_get_value into l_value;
393 --
394 if csr_get_value%notfound then
395 l_value := null;
396 close csr_get_value;
397 raise e_no_value_found;
398 else
399 close csr_get_value;
400 end if;
401 --
402 return(l_value);
403 exception
404 when e_no_value_found then
405 If g_debug then
406 hr_utility.set_location('error in get archive value - assignment_action_id:' ||p_assignment_action_id,3);
407 hr_utility.set_location('error in get archive value - user entity name :' ||p_user_entity_name,3);
408 end if;
409 return (null);
410 when others then
411 If g_debug then
412 hr_utility.set_location('error in get archive value - assignment_action_id:' ||p_assignment_action_id,3);
413 hr_utility.set_location('error in get archive value - user entity name :' ||p_user_entity_name,3);
414 end if;
415 return (null);
416 end get_archive_value;
417 ------------------------------------------------------------------------
418 -- Selects the SRS parameters for the archive and calls other procedures
419 -- to archive the data in groups because depending on the data,
420 -- different parameters are required.
421 ------------------------------------------------------------------------
422 procedure archive_code
423 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
424 p_effective_date in date )
425 is
426 v_person_id per_all_people_f.person_id%type;
427 v_assignment_id per_all_assignments_f.assignment_id%type;
428 v_national_identifier varchar2(50);
429 v_archive_date pay_payroll_actions.effective_date%type;
430 l_person_id per_all_people_f.person_id%type;
431 l_archived_person_id binary_integer;
432
433 ------------------------------------------------------------------------
434 -- Bug 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
435 -- Bug 3260855 - Modified the cusor to fetch only person_id, asg_id instead of
436 -- legislative parameters as global values can be used, which are initialized in Init_code.
437 ------------------------------------------------------------------------
438 cursor get_details( c_assignment_action_id pay_assignment_actions.assignment_action_id%type ) is
439 select pap.person_id,
440 nvl(pap.national_identifier,pap.per_information12),
441 pac.assignment_id
442 from pay_assignment_actions pac,
443 per_assignments_f paa,
444 per_people_f pap
445 where pac.assignment_action_id = c_assignment_action_id
446 and paa.assignment_id = pac.assignment_id
447 and paa.person_id = pap.person_id ;
448
449 begin
450 if g_debug then
451 hr_utility.set_location('pysgirar: Start of archive_code',10);
452 end if;
453 --
454
455 open get_details ( p_assignment_action_id );
456 fetch get_details into v_person_id,
457 v_national_identifier,
458 v_assignment_id;
459 --
460 if get_details%found then
461 close get_details;
462 --
463 if g_debug then
464 hr_utility.set_location('pysgirar: Person Id: ' || to_char(v_person_id) ,100);
465 end if;
466 ------------------------------------------------------------------------------------------------
467 -- Because there are different routes for each group of data, a separate procedure
468 -- has been written for each.
469 -- Bug 2640107 : Call the archive procedures only for the latest person id
470 -- in case the employee is rehired with duplicate National Identifier/Income Tax Number
471 -- Bug 3435334 Introduced function employee_if_latest which returns a boolean TRUE/FALSE
472 -- to indicate if an employee needs to be archived / skipped for any rehires.
473 ------------------------------------------------------------------------------------------------
474 if employee_if_latest ( v_national_identifier,
475 v_person_id,
476 g_setup_action_id,
477 g_report_type ) then
478
479 --
480 -- Bug 4688761, only archives once if it has re-hire/multi
481 -- assignments
482 if NOT person_if_archived(v_person_id) then
483
484 archive_balances ( p_assignment_action_id,v_person_id,
485 g_business_group_id,
486 g_legal_entity_id,
487 g_basis_year );
488
489 --
490 archive_person_details ( p_assignment_action_id,
491 v_person_id,
492 g_basis_start,
493 g_basis_end );
494 --
495 archive_person_addresses ( p_assignment_action_id,
496 v_person_id,
497 g_basis_start,
498 g_basis_end );
499 --
500 archive_emp_details ( p_assignment_action_id,
501 v_person_id,
502 g_basis_start,
503 g_basis_end );
504 --
505 archive_people_flex ( p_assignment_action_id,
506 v_person_id,
507 g_basis_start,
508 g_basis_end );
509 --
510 archive_person_cq_addresses ( p_assignment_action_id,
511 v_person_id,
512 g_basis_start,
513 g_basis_end );
514
515 archive_person_eits ( p_assignment_action_id,
516 v_person_id,
517 g_basis_start,
518 g_basis_end );
519
520 archive_org_info ( p_assignment_action_id ,
521 g_business_group_id,
522 g_legal_entity_id,
523 v_person_id,
524 g_basis_start,
525 g_basis_end);
526
527 archive_payroll_date( p_assignment_action_id ,
528 g_business_group_id,
529 g_legal_entity_id,
530 v_person_id,
531 g_basis_year);
532
533 --
534 -- Added for bug 4688761, share details should only
535 -- be archived for latest LE with primary defined if
536 -- it has rehired/multi-assignments with diff LE
537 --
538 if pri_if_latest(v_person_id,
539 g_legal_entity_id,
540 g_basis_start,
541 g_basis_end) then
542
543 archive_shares_details ( p_assignment_action_id,
544 v_person_id,
545 g_legal_entity_id,
546 g_basis_start,
547 g_basis_end );
548 end if;
549
550 archive_os_assignment ( p_assignment_action_id,
551 v_person_id,
552 g_legal_entity_id,
553 g_basis_start,
554 g_basis_end );
555 --
556 -- Added for bug 3027801
557 archive_ir8s_c_details ( p_assignment_action_id,
558 v_person_id,
559 g_legal_entity_id,
560 g_business_group_id,
561 g_basis_start,
562 g_basis_end );
563
564 end if;
565
566 l_archived_person_id := v_person_id;
567 t_archived_person(l_archived_person_id).person_id:= v_person_id;
568 else
569 if g_debug then
570 hr_utility.trace('The Employee has a duplicate employee so will not be processed');
571 end if;
572 end if;
573 else
574 close get_details;
575 end if;
576 --
577 if g_debug then
578 hr_utility.set_location('pysgirar: End of archive_code',20);
579 end if;
580 end archive_code;
581 --------------------------------------------------------------------------------------
582 -- Bug#3501927 Added new function to fetch and calculate A8A Balances
583 -- Bug#6349937 Split the large group balances to small groups for
584 -- better performance
585 -- Do not include Obsoleted balances
586 ---------------------------------------------------------------------------------------
587 procedure a8a_balances_value
588 ( p_person_id in per_people_f.person_id%type,
589 p_assct_id in pay_assignment_actions.assignment_action_id%type,
590 p_tax_uid in pay_assignment_actions.tax_unit_id%type,
591 p_person_counter in number )
592 is
593 l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
594 l_context_tab pay_balance_pkg.t_context_tab;
595 l_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
596
597 l_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
598 l_detailed_bal_out_tab1 pay_balance_pkg.t_detailed_bal_out_tab;
599
600 l_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
601 l_detailed_bal_out_tab2 pay_balance_pkg.t_detailed_bal_out_tab;
602
603 l_balance_value_tab3 pay_balance_pkg.t_balance_value_tab;
604 l_detailed_bal_out_tab3 pay_balance_pkg.t_detailed_bal_out_tab;
605
606 l_balance_value_tab4 pay_balance_pkg.t_balance_value_tab;
607 l_detailed_bal_out_tab4 pay_balance_pkg.t_detailed_bal_out_tab;
608
609 l_balance_value_tab5 pay_balance_pkg.t_balance_value_tab;
610 l_detailed_bal_out_tab5 pay_balance_pkg.t_detailed_bal_out_tab;
611
612 l_ytd_a8a_counter number;
613 --
614 cursor ytd_A8A_balances is
615 select fue.user_entity_id,
616 pdb.defined_balance_id def_bal_id
617 from ff_user_entities fue,
618 pay_balance_types pbt,
619 pay_defined_balances pdb,
620 pay_balance_dimensions pbd
621 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
622 and fue.legislation_code = 'SG'
623 and pbt.legislation_code = 'SG'
624 and pbd.legislation_code = pbt.legislation_code
625 and pdb.legislation_code = pbt.legislation_code
626 and pbt.balance_name like 'A8A%'
627 and upper(pbt.reporting_name) not like '%OBSOLETE%'
628 and pbt.balance_type_id = pdb.balance_type_id
629 and pbd.balance_dimension_id = pdb.balance_dimension_id
630 and pbd.dimension_name = '_PER_LE_YTD'
631 order by pbt.balance_name asc;
632 --
633 cursor benefit_inkind_bal is
634 select nvl(pei_information2, l_detailed_bal_out_tab(1).balance_value), --A8A_MOA_500
635 nvl(pei_information3, l_detailed_bal_out_tab(2).balance_value), --A8A_MOA_501
636 nvl(pei_information4, l_detailed_bal_out_tab(3).balance_value), --A8A_MOA_502
637 nvl(pei_information5, l_detailed_bal_out_tab(7).balance_value), --A8A_MOA_506
638 nvl(pei_information6, l_detailed_bal_out_tab(8).balance_value), --A8A_MOA_507
639 nvl(pei_information7, l_detailed_bal_out_tab(9).balance_value), --A8A_MOA_508
640 nvl(pei_information8, l_detailed_bal_out_tab(10).balance_value),--A8A_MOA_509
641 nvl(pei_information9, l_detailed_bal_out_tab(11).balance_value),--A8A_MOA_510
642 nvl(pei_information10,l_detailed_bal_out_tab(12).balance_value),--A8A_MOA_511
643 nvl(pei_information11,l_detailed_bal_out_tab(13).balance_value),--A8A_MOA_512
644 nvl(pei_information12,l_detailed_bal_out_tab(14).balance_value),--A8A_MOA_513
645 nvl(pei_information13,l_detailed_bal_out_tab(15).balance_value),--A8A_MOA_514
646 nvl(pei_information14,l_detailed_bal_out_tab(17).balance_value),--A8A_MOA_516
647 nvl(pei_information15,l_detailed_bal_out_tab(26).balance_value),--A8A_MOA_525
648 nvl(pei_information16,l_detailed_bal_out_tab(27).balance_value),--A8A_MOA_526
649 nvl(pei_information17,l_detailed_bal_out_tab(28).balance_value),--A8A_MOA_527
650 nvl(pei_information22,l_detailed_bal_out_tab(29).balance_value),--A8A_MOA_528
651 nvl(pei_information23,l_detailed_bal_out_tab(30).balance_value),--A8A_MOA_529
652 nvl(pei_information24,l_detailed_bal_out_tab(31).balance_value),--A8A_MOA_530
653 nvl(pei_information18,l_detailed_bal_out_tab(32).balance_value),--A8A_MOA_531
654 nvl(pei_information19,l_detailed_bal_out_tab(33).balance_value),--A8A_MOA_532
655 nvl(pei_information20,l_detailed_bal_out_tab(34).balance_value),--A8A_MOA_533
656 nvl(pei_information21,l_detailed_bal_out_tab(35).balance_value) --A8A_MOA_534
657 from per_people_extra_info pae
658 where person_id = p_person_id
659 and information_type = 'HR_A8A_BENEFITS_IN_KIND_SG'
660 and pei_information1 = g_basis_year;
661 --
662 cursor furniture_exp_bal is
663 select nvl(pei_information2, l_detailed_bal_out_tab(45).balance_value), --A8A_QTY_304
664 nvl(pei_information3, l_detailed_bal_out_tab(46).balance_value), --A8A_QTY_305
665 nvl(pei_information4, l_detailed_bal_out_tab(47).balance_value), --A8A_QTY_306
666 nvl(pei_information5, l_detailed_bal_out_tab(48).balance_value), --A8A_QTY_307
667 nvl(pei_information6, l_detailed_bal_out_tab(49).balance_value), --A8A_QTY_308
668 nvl(pei_information7, l_detailed_bal_out_tab(50).balance_value), --A8A_QTY_309
669 nvl(pei_information8, l_detailed_bal_out_tab(51).balance_value), --A8A_QTY_310
670 nvl(pei_information9, l_detailed_bal_out_tab(52).balance_value), --A8A_QTY_311
671 nvl(pei_information10,l_detailed_bal_out_tab(53).balance_value), --A8A_QTY_312
672 nvl(pei_information11,l_detailed_bal_out_tab(54).balance_value), --A8A_QTY_313
673 nvl(pei_information12,l_detailed_bal_out_tab(55).balance_value), --A8A_QTY_314
674 nvl(pei_information13,l_detailed_bal_out_tab(56).balance_value), --A8A_QTY_315
675 nvl(pei_information14,l_detailed_bal_out_tab(57).balance_value), --A8A_QTY_316
676 nvl(pei_information15,l_detailed_bal_out_tab(58).balance_value), --A8A_QTY_317
677 nvl(pei_information16,l_detailed_bal_out_tab(59).balance_value), --A8A_QTY_318
678 nvl(pei_information17,l_detailed_bal_out_tab(60).balance_value), --A8A_QTY_319
679 nvl(pei_information18,l_detailed_bal_out_tab(61).balance_value), --A8A_QTY_320
680 nvl(pei_information19,l_detailed_bal_out_tab(18).balance_value), --A8A_MOA_517
681 nvl(pei_information20,l_detailed_bal_out_tab(19).balance_value), --A8A_MOA_518
682 nvl(pei_information21,l_detailed_bal_out_tab(20).balance_value), --A8A_MOA_519
683 nvl(pei_information22,l_detailed_bal_out_tab(21).balance_value), --A8A_MOA_520
684 nvl(pei_information23,l_detailed_bal_out_tab(22).balance_value), --A8A_MOA_521
685 nvl(pei_information24,l_detailed_bal_out_tab(23).balance_value), --A8A_MOA_522
686 nvl(pei_information25,l_detailed_bal_out_tab(24).balance_value), --A8A_MOA_523
687 nvl(pei_information26,l_detailed_bal_out_tab(25).balance_value) --A8A_MOA_524
688 from per_people_extra_info pae
689 where person_id = p_person_id
690 and information_type ='HR_A8A_FURN_EXP_SG'
691 and pei_information1 = g_basis_year;
692 --
693 cursor hotel_accom_bal is
694 select nvl(pei_information2, l_detailed_bal_out_tab(62).balance_value), --A8A_QTY_321
695 nvl(pei_information3, l_detailed_bal_out_tab(63).balance_value), --A8A_QTY_322
696 nvl(pei_information4, l_detailed_bal_out_tab(64).balance_value), --A8A_QTY_323
697 nvl(pei_information5, l_detailed_bal_out_tab(65).balance_value), --A8A_QTY_324
698 nvl(pei_information6, l_detailed_bal_out_tab(66).balance_value), --A8A_QTY_325
699 nvl(pei_information7, l_detailed_bal_out_tab(67).balance_value), --A8A_QTY_326
700 nvl(pei_information8, l_detailed_bal_out_tab(68).balance_value), --A8A_QTY_327
701 nvl(pei_information9, l_detailed_bal_out_tab(69).balance_value), --A8A_QTY_328
702 nvl(pei_information10,l_detailed_bal_out_tab(40).balance_value), --A8A_MOA_539
703 nvl(pei_information11,l_detailed_bal_out_tab(41).balance_value), --A8A_QTY_300
704 nvl(pei_information12,l_detailed_bal_out_tab(42).balance_value), --A8A_QTY_301
705 nvl(pei_information13,l_detailed_bal_out_tab(43).balance_value), --A8A_QTY_302
706 nvl(pei_information14,l_detailed_bal_out_tab(44).balance_value), --A8A_QTY_303
707 nvl(pei_information15,l_detailed_bal_out_tab(6).balance_value) --A8A_MOA_505
708 , nvl(pei_information16,l_detailed_bal_out_tab(70).balance_value) --A8A_QTY_329, bug 5435088
709 from per_people_extra_info pae
710 where person_id = p_person_id
711 and information_type ='HR_A8A_HOTEL_ACCOM_SG'
712 and pei_information1 = g_basis_year;
713 --
714 cursor c_globals (p_global_name in varchar2) is
715 select global_value
716 from ff_globals_f
717 where global_name = p_global_name;
718 --
719 l_a8a_person_20 number;
720 l_a8a_child_8_20 number;
721 l_a8a_child_3_7 number;
722 l_a8a_child_3 number;
723
724 /* Bug 5230059 */
725 l_a8a_person_20_a number;
726 l_a8a_child_8_20_a number;
727 l_a8a_child_3_7_a number;
728 l_a8a_child_3_a number;
729
730 l_count number;
731 begin
732 l_ytd_a8a_counter := 1;
733 l_balance_value_tab.delete;
734 l_context_tab.delete;
735 l_detailed_bal_out_tab.delete;
736 --
737 l_balance_value_tab1.delete;
738 l_detailed_bal_out_tab1.delete;
739 --
740 l_balance_value_tab2.delete;
741 l_detailed_bal_out_tab2.delete;
742 --
743 l_balance_value_tab3.delete;
744 l_detailed_bal_out_tab3.delete;
745
746 l_balance_value_tab4.delete;
747 l_detailed_bal_out_tab4.delete;
748
749 l_balance_value_tab5.delete;
750 l_detailed_bal_out_tab5.delete;
751
752 l_count := 14;
753
754 if t_ytd_a8a_balanceid_store.count = 0 then
755 open ytd_a8a_balances;
756 loop
757 fetch ytd_a8a_balances into t_ytd_a8a_balanceid_store(l_ytd_a8a_counter).user_entity_id,
758 t_ytd_a8a_balanceid_store(l_ytd_a8a_counter).defined_balance_id;
759 l_ytd_a8a_counter := l_ytd_a8a_counter + 1;
760 exit when ytd_a8a_balances%NOTFOUND;
761 end loop;
762 close ytd_a8a_balances;
763 end if;
764 --
765
766 for counter in 1..l_count
767 loop
768 l_balance_value_tab1(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter).defined_balance_id;
769 l_balance_value_tab2(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+l_count).defined_balance_id;
770 l_balance_value_tab3(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+2*l_count).defined_balance_id;
771 l_balance_value_tab4(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+3*l_count).defined_balance_id;
772 l_balance_value_tab5(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+4*l_count).defined_balance_id;
773
774 l_context_tab(counter).tax_unit_id := p_tax_uid;
775 end loop;
776
777 --
778 if p_assct_id is not null then
779 pay_balance_pkg.get_value( p_assct_id,
780 l_balance_value_tab1,
781 l_context_tab,
782 false,
783 false,
784 l_detailed_bal_out_tab1);
785 pay_balance_pkg.get_value( p_assct_id,
786 l_balance_value_tab2,
787 l_context_tab,
788 false,
789 false,
790 l_detailed_bal_out_tab2);
791 pay_balance_pkg.get_value( p_assct_id,
792 l_balance_value_tab3,
793 l_context_tab,
794 false,
795 false,
796 l_detailed_bal_out_tab3);
797 pay_balance_pkg.get_value( p_assct_id,
798 l_balance_value_tab4,
799 l_context_tab,
800 false,
801 false,
802 l_detailed_bal_out_tab4);
803 pay_balance_pkg.get_value( p_assct_id,
804 l_balance_value_tab5,
805 l_context_tab,
806 false,
807 false,
808 l_detailed_bal_out_tab5);
809 end if;
810 for counter in 1..l_count
811 loop
812 l_detailed_bal_out_tab(counter).balance_value := l_detailed_bal_out_tab1(counter).balance_value;
813 l_detailed_bal_out_tab(counter+l_count).balance_value := l_detailed_bal_out_tab2(counter).balance_value;
814 l_detailed_bal_out_tab(counter+2*l_count).balance_value := l_detailed_bal_out_tab3(counter).balance_value;
815 l_detailed_bal_out_tab(counter+3*l_count).balance_value := l_detailed_bal_out_tab4(counter).balance_value;
816 l_detailed_bal_out_tab(counter+4*l_count).balance_value := l_detailed_bal_out_tab5(counter).balance_value;
817 end loop;
818
819 --
820 open benefit_inkind_bal;
821 fetch benefit_inkind_bal into
822 l_detailed_bal_out_tab(1).balance_value, --A8A_MOA_500
823 l_detailed_bal_out_tab(2).balance_value, --A8A_MOA_501
824 l_detailed_bal_out_tab(3).balance_value, --A8A_MOA_502
825 l_detailed_bal_out_tab(7).balance_value, --A8A_MOA_506
826 l_detailed_bal_out_tab(8).balance_value, --A8A_MOA_507
827 l_detailed_bal_out_tab(9).balance_value, --A8A_MOA_508
828 l_detailed_bal_out_tab(10).balance_value, --A8A_MOA_509
829 l_detailed_bal_out_tab(11).balance_value, --A8A_MOA_510
830 l_detailed_bal_out_tab(12).balance_value, --A8A_MOA_511
831 l_detailed_bal_out_tab(13).balance_value, --A8A_MOA_512
832 l_detailed_bal_out_tab(14).balance_value, --A8A_MOA_513
833 l_detailed_bal_out_tab(15).balance_value, --A8A_MOA_514
834 l_detailed_bal_out_tab(17).balance_value, --A8A_MOA_516
835 l_detailed_bal_out_tab(26).balance_value, --A8A_MOA_525
836 l_detailed_bal_out_tab(27).balance_value, --A8A_MOA_526
837 l_detailed_bal_out_tab(28).balance_value, --A8A_MOA_527
838 l_detailed_bal_out_tab(29).balance_value, --A8A_MOA_528
839 l_detailed_bal_out_tab(30).balance_value, --A8A_MOA_529
840 l_detailed_bal_out_tab(31).balance_value, --A8A_MOA_530
841 l_detailed_bal_out_tab(32).balance_value, --A8A_MOA_531
842 l_detailed_bal_out_tab(33).balance_value, --A8A_MOA_532
843 l_detailed_bal_out_tab(34).balance_value, --A8A_MOA_533
844 l_detailed_bal_out_tab(35).balance_value ; --A8A_MOA_534
845 close benefit_inkind_bal;
846 --
847 open furniture_exp_bal;
848 fetch furniture_exp_bal into
849 l_detailed_bal_out_tab(45).balance_value, --A8A_QTY_304
850 l_detailed_bal_out_tab(46).balance_value, --A8A_QTY_305
851 l_detailed_bal_out_tab(47).balance_value, --A8A_QTY_306
852 l_detailed_bal_out_tab(48).balance_value, --A8A_QTY_307
853 l_detailed_bal_out_tab(49).balance_value, --A8A_QTY_308
854 l_detailed_bal_out_tab(50).balance_value, --A8A_QTY_309
855 l_detailed_bal_out_tab(51).balance_value, --A8A_QTY_310
856 l_detailed_bal_out_tab(52).balance_value, --A8A_QTY_311
857 l_detailed_bal_out_tab(53).balance_value, --A8A_QTY_312
858 l_detailed_bal_out_tab(54).balance_value, --A8A_QTY_313
859 l_detailed_bal_out_tab(55).balance_value, --A8A_QTY_314
860 l_detailed_bal_out_tab(56).balance_value, --A8A_QTY_315
861 l_detailed_bal_out_tab(57).balance_value, --A8A_QTY_316
862 l_detailed_bal_out_tab(58).balance_value, --A8A_QTY_317
863 l_detailed_bal_out_tab(59).balance_value, --A8A_QTY_318
864 l_detailed_bal_out_tab(60).balance_value, --A8A_QTY_319
865 l_detailed_bal_out_tab(61).balance_value, --A8A_QTY_320
866 l_detailed_bal_out_tab(18).balance_value, --A8A_MOA_517
867 l_detailed_bal_out_tab(19).balance_value, --A8A_MOA_518
868 l_detailed_bal_out_tab(20).balance_value, --A8A_MOA_519
869 l_detailed_bal_out_tab(21).balance_value, --A8A_MOA_520
870 l_detailed_bal_out_tab(22).balance_value, --A8A_MOA_521
871 l_detailed_bal_out_tab(23).balance_value, --A8A_MOA_522
872 l_detailed_bal_out_tab(24).balance_value, --A8A_MOA_523
873 l_detailed_bal_out_tab(25).balance_value ; --A8A_MOA_524
874 close furniture_exp_bal ;
875 --
876 open hotel_accom_bal;
877 fetch hotel_accom_bal into
878 l_detailed_bal_out_tab(62).balance_value, --A8A_QTY_321
879 l_detailed_bal_out_tab(63).balance_value, --A8A_QTY_322
880 l_detailed_bal_out_tab(64).balance_value, --A8A_QTY_323
881 l_detailed_bal_out_tab(65).balance_value, --A8A_QTY_324
882 l_detailed_bal_out_tab(66).balance_value, --A8A_QTY_325
883 l_detailed_bal_out_tab(67).balance_value, --A8A_QTY_326
884 l_detailed_bal_out_tab(68).balance_value, --A8A_QTY_327
885 l_detailed_bal_out_tab(69).balance_value, --A8A_QTY_328
886 l_detailed_bal_out_tab(40).balance_value, --A8A_MOA_539
887 l_detailed_bal_out_tab(41).balance_value, --A8A_QTY_300
888 l_detailed_bal_out_tab(42).balance_value, --A8A_QTY_301
889 l_detailed_bal_out_tab(43).balance_value, --A8A_QTY_302
890 l_detailed_bal_out_tab(44).balance_value, --A8A_QTY_303
891 l_detailed_bal_out_tab(6).balance_value, --A8A_MOA_505
892 l_detailed_bal_out_tab(70).balance_value; -- A8A_QTY_329
893
894 close hotel_accom_bal;
895 -------------------------------------------------------------
896 -- Calculation for A8A_MOA_503 (Sum of MOA 517 to 534))
897 -------------------------------------------------------------
898 l_detailed_bal_out_tab(4).balance_value :=
899 l_detailed_bal_out_tab(18).balance_value + l_detailed_bal_out_tab(19).balance_value
900 + l_detailed_bal_out_tab(20).balance_value + l_detailed_bal_out_tab(21).balance_value
901 + l_detailed_bal_out_tab(22).balance_value + l_detailed_bal_out_tab(23).balance_value
902 + l_detailed_bal_out_tab(24).balance_value + l_detailed_bal_out_tab(25).balance_value
903 + l_detailed_bal_out_tab(26).balance_value + l_detailed_bal_out_tab(27).balance_value
904 + l_detailed_bal_out_tab(28).balance_value + l_detailed_bal_out_tab(29).balance_value
905 + l_detailed_bal_out_tab(30).balance_value + l_detailed_bal_out_tab(31).balance_value
906 + l_detailed_bal_out_tab(32).balance_value + l_detailed_bal_out_tab(33).balance_value
907 + l_detailed_bal_out_tab(34).balance_value + l_detailed_bal_out_tab(35).balance_value ;
908 --
909 open c_globals( 'A8A_PERSON_20' );
910 fetch c_globals into l_a8a_person_20;
911 close c_globals;
912 -------------------------------------------------------------
913 -- Calculation for A8A_MOA_535
914 -- (A8A_QTY_321 * Rate * 12 * A8A_QTY_322 /365)
915 -------------------------------------------------------------
916 l_a8a_person_20_a := l_detailed_bal_out_tab(62).balance_value * l_a8a_person_20 * 12 * l_detailed_bal_out_tab(63).balance_value / 365;
917 --
918 if l_detailed_bal_out_tab(62).balance_value > 0 and
919 l_detailed_bal_out_tab(63).balance_value <> 0 then
920 if l_a8a_person_20_a between 0 and 1 then
921 l_detailed_bal_out_tab(36).balance_value := 1;
922 elsif l_a8a_person_20_a between -1 and 0 then
923 l_detailed_bal_out_tab(36).balance_value := -1;
924 else
925 l_detailed_bal_out_tab(36).balance_value := trunc(l_a8a_person_20_a);
926 end if;
927 else
928 l_detailed_bal_out_tab(36).balance_value := 0;
929 end if;
930 --
931 open c_globals('A8A_CHILD_8_20');
932 fetch c_globals into l_a8a_child_8_20;
933 close c_globals;
934 -------------------------------------------------------------
935 -- Calculation for A8A_MOA_536
936 -- (A8A_QTY_323 * Rate * 12 * A8A_QTY_324 /365)
937 -- Bug 5230059
938 -------------------------------------------------------------
939 l_a8a_child_8_20_a := l_detailed_bal_out_tab(64).balance_value * l_a8a_child_8_20 * 12 * l_detailed_bal_out_tab(65).balance_value / 365;
940 --
941 if l_detailed_bal_out_tab(64).balance_value > 0 and
942 l_detailed_bal_out_tab(65).balance_value <> 0 then
943 if l_a8a_child_8_20_a between 0 and 1 then
944 l_detailed_bal_out_tab(37).balance_value := 1;
945 elsif l_a8a_child_8_20_a between -1 and 0 then
946 l_detailed_bal_out_tab(37).balance_value := -1;
947 else
948 l_detailed_bal_out_tab(37).balance_value := trunc(l_a8a_child_8_20_a);
949 end if;
950 else
951 l_detailed_bal_out_tab(37).balance_value := 0;
952 end if;
953 --
954 open c_globals ('A8A_CHILD_3_7');
955 fetch c_globals into l_a8a_child_3_7;
956 close c_globals;
957 -------------------------------------------------------------
958 -- Calculation for A8A_MOA_537
959 -- (A8A_QTY_325 * rate * 12 * A8A_QTY_326/365)
960 -------------------------------------------------------------
961 l_a8a_child_3_7_a := l_detailed_bal_out_tab(66).balance_value * l_a8a_child_3_7 * 12 * l_detailed_bal_out_tab(67).balance_value / 365;
962 --
963 if l_detailed_bal_out_tab(66).balance_value > 0 and
964 l_detailed_bal_out_tab(67).balance_value <> 0 then
965 if l_a8a_child_3_7_a between 0 and 1 then
966 l_detailed_bal_out_tab(38).balance_value := 1;
967 elsif l_a8a_child_3_7_a between -1 and 0 then
968 l_detailed_bal_out_tab(38).balance_value := -1;
969 else
970 l_detailed_bal_out_tab(38).balance_value := trunc(l_a8a_child_3_7_a);
971 end if;
972 else
973 l_detailed_bal_out_tab(38).balance_value := 0;
974 end if;
975 --
976 open c_globals('A8A_CHILD_3');
977 fetch c_globals into l_a8a_child_3;
978 close c_globals;
979 -------------------------------------------------------------
980 -- Calculation for A8A_MOA_538
981 -- (A8A_QTY_327 * rate * 12 * A8A_QTY_328/365)
982 -- Bug 5230059
983 -------------------------------------------------------------
984 l_a8a_child_3_a := l_detailed_bal_out_tab(68).balance_value * l_a8a_child_3 * 12 * l_detailed_bal_out_tab(69).balance_value / 365;
985
986 --
987 if l_detailed_bal_out_tab(68).balance_value > 0 and
988 l_detailed_bal_out_tab(69).balance_value <> 0 then
989 if l_a8a_child_3_a between 0 and 1 then
990 l_detailed_bal_out_tab(39).balance_value := 1;
991 elsif l_a8a_child_3_a between -1 and 0 then
992 l_detailed_bal_out_tab(39).balance_value := -1;
993 else
994 l_detailed_bal_out_tab(39).balance_value := trunc(l_a8a_child_3_a);
995 end if;
996 else
997 l_detailed_bal_out_tab(39).balance_value := 0;
998 end if;
999
1000 ------------------------------------------------------------
1001 -- Bug 5435088, if the No of employees sharing the Quarter is not zero
1002 -- MOA500 and MOA503 is divided by the number of employee sharing
1003 -- Removed calculation for bug fix 5644617
1004 ------------------------------------------------------------
1005
1006 -------------------------------------------------------------
1007 -- Calculation for A8A_MOA_504 (Sum of MOA 535 to 539)
1008 -------------------------------------------------------------
1009 l_detailed_bal_out_tab(5).balance_value :=
1010 l_detailed_bal_out_tab(36).balance_value + l_detailed_bal_out_tab(37).balance_value
1011 + l_detailed_bal_out_tab(38).balance_value + l_detailed_bal_out_tab(39).balance_value
1012 + l_detailed_bal_out_tab(40).balance_value;
1013 --
1014 -------------------------------------------------------------
1015 -- Calculation for A8A_MOA_515 (MOA 500 + 503 + 504 + 505 + 506 + 507 +508 + 509 + 510 + 511 + 512 + 513 + 514 + 516 )
1016 -- Bug#3948951 Moved the code after A8A_MOA_504 balance calculation.
1017 -------------------------------------------------------------
1018 l_detailed_bal_out_tab(16).balance_value :=
1019 l_detailed_bal_out_tab(1).balance_value + l_detailed_bal_out_tab(4).balance_value
1020 + l_detailed_bal_out_tab(5).balance_value + l_detailed_bal_out_tab(6).balance_value
1021 + l_detailed_bal_out_tab(7).balance_value + l_detailed_bal_out_tab(8).balance_value
1022 + l_detailed_bal_out_tab(9).balance_value + l_detailed_bal_out_tab(10).balance_value
1023 + l_detailed_bal_out_tab(11).balance_value + l_detailed_bal_out_tab(12).balance_value
1024 + l_detailed_bal_out_tab(13).balance_value + l_detailed_bal_out_tab(14).balance_value
1025 + l_detailed_bal_out_tab(15).balance_value + l_detailed_bal_out_tab(17).balance_value ;
1026 --
1027 for counter in 1..l_detailed_bal_out_tab.count
1028 loop
1029 if p_person_counter = 1 then
1030 if l_detailed_bal_out_tab.exists(counter) then
1031 ytd_a8a_balance_rec(counter).balance_id := t_ytd_a8a_balanceid_store(counter).user_entity_id;
1032 ytd_a8a_balance_rec(counter).balance_value := nvl(l_detailed_bal_out_tab(counter).balance_value,0) ;
1033 end if;
1034 else
1035 if l_detailed_bal_out_tab.exists(counter) then
1036 if ytd_a8a_balance_rec.exists(counter) then
1037 ytd_a8a_balance_rec(counter).balance_value := nvl(l_detailed_bal_out_tab(counter).balance_value,0)
1038 + ytd_a8a_balance_rec(counter).balance_value;
1039 end if;
1040 end if;
1041 end if;
1042 end loop;
1043 --
1044 exception
1045 when others then
1046 hr_utility.set_location('pysgirar: Error in a8a_balances_value',10);
1047 raise;
1048 end;
1049 ---------------------------------------------------------------------------
1050 -- Selects data required to archive the YTD and Month balances. The
1051 -- cursors' main purpose is to select the latest action sequence for the
1052 -- PERSON (independent of assignment) within the Legal Entity, and pass
1053 -- that to pay_balance_pkg.
1054 -- Also the User Entity Name must match up to the balance.
1055 --
1056 -- YTD Balances: All IRAS balances + specific previously seeded balances
1057 -- Month Balances: Specific balances required for IR8S as this breaks down
1058 -- earnings by month.
1059 ---------------------------------------------------------------------------
1060 procedure archive_balances
1061 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1062 p_person_id in per_all_people_f.person_id%type,
1063 p_business_group_id in hr_organization_units.business_group_id%type,
1064 p_tax_unit_id in ff_archive_item_contexts.context%type,
1065 p_basis_year in varchar2 )
1066 is
1067 v_run_ass_action_id pay_assignment_actions.assignment_action_id%type;
1068 v_date_earned ff_archive_item_contexts.context%type;
1069 v_balance_value ff_archive_items.value%type;
1070 v_archive_item_id ff_archive_items.archive_item_id%type;
1071 v_object_version_number ff_archive_items.object_version_number%type;
1072 v_some_warning boolean;
1073 --------------------------------------------------------------------------------------
1074 --Bug#3933332 Moved the records from package header as these pl/sql table is
1075 -- specific to procedure archive_balances()
1076 --------------------------------------------------------------------------------------
1077 type t_archive_items_tab is table of ff_archive_items.archive_item_id%TYPE index by binary_integer;
1078 t_archive_items t_archive_items_tab;
1079 --
1080 type t_archive_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
1081 t_archive_value t_archive_value_tab;
1082 --
1083 type t_date_earned_tab is table of varchar2(30) index by binary_integer;
1084 t_date_earned t_date_earned_tab;
1085 --
1086 type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
1087 t_user_entity_id t_user_entity_tab;
1088 --
1089 ---------------------------------------------------------------------------------------------------
1090 -- This ytd_balances cursor only gets the defined_balance_id and user_entity_id
1091 -- Bug 3232303- Added 4 new balances.
1092 -- Bug 6349937, do not include Obsoleted balances
1093 ---------------------------------------------------------------------------------------------------
1094 cursor ytd_balances is
1095 select fue.user_entity_id,
1096 pdb.defined_balance_id def_bal_id
1097 from ff_user_entities fue,
1098 pay_balance_types pbt,
1099 pay_defined_balances pdb,
1100 pay_balance_dimensions pbd
1101 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
1102 and fue.legislation_code = 'SG'
1103 and pbt.legislation_code = 'SG'
1104 and pbd.legislation_code = pbt.legislation_code
1105 and pdb.legislation_code = pbt.legislation_code
1106 and ( pbt.balance_name in ('Voluntary CPF Liability','CPF Liability',
1107 'Voluntary CPF Withheld','CPF Withheld',
1108 'Employee CPF Contributions Additional Earnings',
1109 'Employee CPF Contributions Ordinary Earnings',
1110 'Employer CPF Contributions Additional Earnings',
1111 'Employer CPF Contributions Ordinary Earnings',
1112 'Additional Earnings','Ordinary Earnings',
1113 'Employer Vol CPF Contributions Ordinary Earnings',
1114 'Employee Vol CPF Contributions Ordinary Earnings',
1115 'Employer Vol CPF Contributions Additional Earnings',
1116 'Employee Vol CPF Contributions Additional Earnings')
1117 or
1118 ( pbt.balance_name like 'IR8%' ) )
1119 and upper(pbt.reporting_name) not like '%OBSOLETE%'
1120 and pbt.balance_type_id = pdb.balance_type_id
1121 and pbd.balance_dimension_id = pdb.balance_dimension_id
1122 and pbd.dimension_name = '_PER_LE_YTD';
1123 ---------------------------------------------------------------------------------------------------
1124 -- Bug 2629839. Cursor month_year_action is split into two cursors month_year_action_sequence and
1125 -- month_year_action to improve the performance
1126 -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
1127 -- Cursor month_year_action_sequence
1128 ---------------------------------------------------------------------------------------------------
1129 cursor month_year_action_sequence
1130 ( c_person_id per_all_people_f.person_id%type,
1131 c_business_group_id hr_organization_units.business_group_id%type,
1132 c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
1133 c_basis_year varchar2 )
1134 is
1135 select /*+ ORDERED USE_NL(pacmax) */
1136 max(pacmax.action_sequence) act_seq,
1137 to_char(ppamax.effective_date,'MM')
1138 from per_assignments_f paamax,
1139 pay_assignment_actions pacmax,
1140 pay_payroll_actions ppamax
1141 where ppamax.business_group_id = c_business_group_id
1142 and pacmax.tax_unit_id = c_legal_entity_id
1143 and paamax.person_id = c_person_id
1144 and paamax.assignment_id = pacmax.assignment_id
1145 and ppamax.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
1146 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
1147 and ppamax.payroll_action_id = pacmax.payroll_action_id
1148 and ppamax.action_type in ('R','B','I','Q','V')
1149 group by to_char(ppamax.effective_date,'MM')
1150 order by to_char(ppamax.effective_date,'MM');
1151 ---------------------------------------------------------------------------------------------------
1152 -- cursor month_year_action
1153 ---------------------------------------------------------------------------------------------------
1154 cursor month_year_action
1155 ( c_person_id per_all_people_f.person_id%type,
1156 c_business_group_id hr_organization_units.business_group_id%type,
1157 c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
1158 c_basis_year varchar2,
1159 c_action_sequence pay_assignment_actions.action_sequence%type )
1160 is
1161 select /*+ ORDERED USE_NL(pac) */
1162 pac.assignment_action_id assact_id,
1163 decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1164 pac.tax_unit_id tax_uid
1165 from per_assignments_f paa,
1166 pay_assignment_actions pac,
1167 pay_payroll_actions ppa
1168 where ppa.business_group_id = c_business_group_id
1169 and pac.tax_unit_id = c_legal_entity_id
1170 and paa.person_id = c_person_id
1171 and paa.assignment_id = pac.assignment_id
1172 and ppa.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
1173 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
1174 and ppa.payroll_action_id = pac.payroll_action_id
1175 and pac.action_sequence = c_action_sequence;
1176 ---------------------------------------------------------------------------------------------------
1177 -- this month_balances cursor only gets the defined_balance_id and user_entity_id
1178 -- Bug 3232303- Added 4 new balances.
1179 ---------------------------------------------------------------------------------------------------
1180 cursor month_balances
1181 is
1182 select fue.user_entity_id,
1183 pdb.defined_balance_id def_bal_id
1184 from ff_user_entities fue,
1185 pay_balance_types pbt,
1186 pay_defined_balances pdb,
1187 pay_balance_dimensions pbd
1188 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_MONTH'
1189 and fue.legislation_code = 'SG'
1190 and pbt.legislation_code = 'SG'
1191 and pbd.legislation_code = pbt.legislation_code
1192 and pdb.legislation_code = pbt.legislation_code
1193 and pbt.balance_name in ('Employee CPF Contributions Additional Earnings',
1194 'Employee CPF Contributions Ordinary Earnings',
1195 'Employer CPF Contributions Additional Earnings',
1196 'Employer CPF Contributions Ordinary Earnings',
1197 'Additional Earnings','Ordinary Earnings',
1198 'Employer Vol CPF Contributions Ordinary Earnings',
1199 'Employee Vol CPF Contributions Ordinary Earnings',
1200 'Employer Vol CPF Contributions Additional Earnings',
1201 'Employee Vol CPF Contributions Additional Earnings',
1202 'IR8S_MOA_403','IR8S_MOA_407','CPF Liability' )
1203 and pbt.balance_type_id = pdb.balance_type_id
1204 and pbd.balance_dimension_id = pdb.balance_dimension_id
1205 and pbd.dimension_name = '_PER_LE_MONTH';
1206 --
1207 ---------------------------------------------------------------------------------------------------
1208 -- Balance Store Record
1209 ---------------------------------------------------------------------------------------------------
1210 --
1211 type ytd_balance_store_rec is record
1212 ( balance_id ff_user_entities.user_entity_id%type,
1213 balance_value number );
1214 type ytd_balance_tab is table of ytd_balance_store_rec index by binary_integer;
1215 ytd_balance_rec ytd_balance_tab;
1216 --
1217 type mtd_balance_store_rec is record
1218 ( balance_id ff_user_entities.user_entity_id%type,
1219 balance_value number,
1220 date_earned varchar2(6),
1221 date_earned_archive varchar2(30),
1222 person_id number,
1223 archive_status varchar2(1) );
1224 type mtd_balance_tab is table of mtd_balance_store_rec index by binary_integer;
1225 mtd_balance_rec mtd_balance_tab;
1226 ---------------------------------------------------------------------------------------------------
1227 -- Bug 3064282 Batch Balance fetch implemented
1228 ---------------------------------------------------------------------------------------------------
1229 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1230 g_context_tab pay_balance_pkg.t_context_tab;
1231 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
1232
1233 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1234 g_detailed_bal_out_tab1 pay_balance_pkg.t_detailed_bal_out_tab;
1235
1236 g_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
1237 g_detailed_bal_out_tab2 pay_balance_pkg.t_detailed_bal_out_tab;
1238 g_balance_value_tab3 pay_balance_pkg.t_balance_value_tab;
1239 g_detailed_bal_out_tab3 pay_balance_pkg.t_detailed_bal_out_tab;
1240 g_balance_value_tab4 pay_balance_pkg.t_balance_value_tab;
1241 g_detailed_bal_out_tab4 pay_balance_pkg.t_detailed_bal_out_tab;
1242 ---------------------------------------------------------------------------------------------------
1243 -- Type to store the person ids with same national_identifier (Bug 2649107)
1244 ---------------------------------------------------------------------------------------------------
1245 type person_id_store_rec is record
1246 ( person_id per_all_people_f.person_id%type );
1247 type person_id_tab is table of person_id_store_rec index by binary_integer;
1248 person_id_rec person_id_tab;
1249 ---------------------------------------------------------------------------------------------------
1250 -- Type to store the months on which payroll is run for a perticular person id
1251 -- Bug: 3205321- Modifed the type of month variable to number. Deleted the cursor which uses the
1252 -- lookup MONTH_CODE.
1253 ---------------------------------------------------------------------------------------------------
1254 type month_store_rec is record
1255 ( month number );
1256 type month_store_tab is table of month_store_rec index by binary_integer;
1257 month_recs month_store_tab;
1258 ---------------------------------------------------------------------------------------------------
1259 -- Local Variables
1260 ---------------------------------------------------------------------------------------------------
1261 l_payroll_mon_counter number;
1262 l_pmon_counter boolean;
1263 month_year_action_sequence_rec month_year_action_sequence%rowtype;
1264 month_year_action_rec month_year_action%rowtype;
1265 per_le_ytd_bal number;
1266 per_le_mtd_bal number;
1267 l_person_id per_all_people_f.person_id%type;
1268 l_ytd_counter number;
1269 l_mon_counter number;
1270 counter number;
1271 icounter number;
1272 l_counter number;
1273 duplicate_exists varchar2(1);
1274 l_mtd_counter number;
1275 l_arch_counter number;
1276 l_asac_cont_id number;
1277 l_tax_cont_id number;
1278 l_date_cont_id number;
1279
1280 ---------------------------------------------------------------------------------------------------
1281 begin
1282 l_payroll_mon_counter := 1;
1283 l_pmon_counter := false;
1284 l_ytd_counter := 1;
1285 l_mon_counter := 1;
1286 l_counter := 1;
1287 duplicate_exists := 'N';
1288 l_arch_counter := 1;
1289 --
1290 if g_debug then
1291 hr_utility.set_location('pysgirar: Start of archive_balances',10);
1292 end if;
1293 ------------------------------------------------------------------------------------------------
1294 -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
1295 -- in employee_if_latest( ) function
1296 ------------------------------------------------------------------------------------------------
1297 if g_person_id_tab.count > 1 then
1298 for l_person_id in g_person_id_tab.first..g_person_id_tab.last
1299 loop
1300 person_id_rec(l_counter).person_id := g_person_id_tab(l_person_id);
1301 l_counter := l_counter+1;
1302 end loop;
1303 --
1304 duplicate_exists := 'Y';
1305 end if;
1306 --
1307 t_archive_items.delete;
1308 t_user_entity_id.delete;
1309 t_archive_value.delete;
1310 t_date_earned.delete;
1311 ------------------------------------------------------------------------------------------------
1312 -- Populate with the only one person_id if the employee is not
1313 -- duplicated(Bug 2849107)
1314 ------------------------------------------------------------------------------------------------
1315 if duplicate_exists = 'N' then
1316 person_id_rec(l_counter).person_id := p_person_id;
1317 end if;
1318 ------------------------------------------------------------------------------------------------
1319 -- 2556026 Used pl/sql table to store the month_balances values.
1320 -- now month_balances will get executed only once
1321 ------------------------------------------------------------------------------------------------
1322 if t_month_balanceid_store.count = 0 then
1323 open month_balances;
1324 loop
1325 fetch month_balances into t_month_balanceid_store(l_mon_counter).user_entity_id,
1326 t_month_balanceid_store(l_mon_counter).defined_balance_id;
1327 l_mon_counter := l_mon_counter + 1;
1328 exit when month_balances%NOTFOUND;
1329 end loop;
1330 close month_balances;
1331 end if;
1332 ------------------------------------------------------------------------------------------------
1333 -- 2556026 Used pl/sql table to store the ytd_balances values.
1334 -- Now ytd_balances will get executed only once
1335 ------------------------------------------------------------------------------------------------
1336 if t_ytd_balanceid_store.count = 0 then
1337 open ytd_balances;
1338 loop
1339 fetch ytd_balances into t_ytd_balanceid_store(l_ytd_counter).user_entity_id,
1340 t_ytd_balanceid_store(l_ytd_counter).defined_balance_id;
1341 l_ytd_counter := l_ytd_counter + 1;
1342 exit when ytd_balances%NOTFOUND;
1343 end loop;
1344 close ytd_balances;
1345 end if;
1346 ------------------------------------------------------------------------------------------------
1347 -- Bug# 3501927
1348 ------------------------------------------------------------------------------------------------
1349 ytd_a8a_balance_rec.delete;
1350 ------------------------------------------------------------------------------------------------
1351 -- Bug 2629839 : Monthly balances are archived first and then the max assignment
1352 -- action id returned from the month_year_action cursor is used for archiving
1353 -- year balances
1354 ------------------------------------------------------------------------------------------------
1355 if person_id_rec.count > 0 then
1356 for l_person_counter in 1..person_id_rec.last
1357 loop
1358 if person_id_rec.exists(l_person_counter) then
1359 open month_year_action_sequence( person_id_rec(l_person_counter).person_id,
1360 p_business_group_id,
1361 p_tax_unit_id,
1362 p_basis_year );
1363 loop
1364 fetch month_year_action_sequence into month_year_action_sequence_rec;
1365 exit when month_year_action_sequence%notfound;
1366 --
1367 open month_year_action( person_id_rec(l_person_counter).person_id,
1368 p_business_group_id,
1369 p_tax_unit_id,
1370 p_basis_year,
1371 month_year_action_sequence_rec.act_seq );
1372 --
1373 fetch month_year_action into month_year_action_rec;
1374 if month_year_action%found then
1375 ----------------------------------------------------------------------------------
1376 -- Start Bug 3038605 - Store the months which have payroll runs.
1377 -- Bug: 3205321 - Store Month in MM format in month_recs
1378 ----------------------------------------------------------------------------------
1379 month_recs(l_payroll_mon_counter).month := to_number(to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MM'));
1380 l_payroll_mon_counter := l_payroll_mon_counter+1;
1381 ----------------------------------------------------------------------------------
1382 -- Bulk Balance Fetch for Bug 3064282
1383 ----------------------------------------------------------------------------------
1384 g_balance_value_tab.delete;
1385 g_context_tab.delete;
1386 g_detailed_bal_out_tab.delete;
1387 --
1388 for counter in 1..t_month_balanceid_store.count
1389 loop
1390 g_balance_value_tab(counter).defined_balance_id := t_month_balanceid_store(counter).defined_balance_id;
1391 g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1392 end loop;
1393 ----------------------------------------------------------------------------------
1394 -- Bug 3223822 - Modified call to the function pay_balance_pkg.get_value
1395 ----------------------------------------------------------------------------------
1396 pay_balance_pkg.get_value( month_year_action_rec.assact_id,
1397 g_balance_value_tab,
1398 g_context_tab,
1399 false,
1400 false,
1401 g_detailed_bal_out_tab );
1402 --
1403 if duplicate_exists = 'N' then /* Bug 3162955 */
1404 for counter in 1..t_month_balanceid_store.count
1405 loop
1406 if t_month_balanceid_store.exists(counter) then
1407 t_user_entity_id(l_arch_counter) := t_month_balanceid_store(counter).user_entity_id;
1408 t_archive_value(l_arch_counter) := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1409 t_date_earned(l_arch_counter) := month_year_action_rec.date_earned;
1410 l_arch_counter := l_arch_counter + 1;
1411 end if;
1412 end loop;
1413 else
1414 --------------------------------------------------------------------------
1415 -- Bug 3162955 - In case of Rechire with new employee number
1416 -- store the employee details in mtd_balance_rec table without archiving.
1417 --------------------------------------------------------------------------
1418 l_mtd_counter := mtd_balance_rec.count + 1;
1419 for counter in 1..t_month_balanceid_store.count
1420 loop
1421 mtd_balance_rec(l_mtd_counter).balance_id := t_month_balanceid_store(counter).user_entity_id;
1422 mtd_balance_rec(l_mtd_counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1423 mtd_balance_rec(l_mtd_counter).date_earned := to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MMYYYY');
1424 mtd_balance_rec(l_mtd_counter).date_earned_archive := month_year_action_rec.date_earned;
1425 mtd_balance_rec(l_mtd_counter).person_id := person_id_rec(l_person_counter).person_id;
1426 mtd_balance_rec(l_mtd_counter).archive_status := 'Y';
1427 l_mtd_counter := l_mtd_counter + 1;
1428 end loop;
1429 end if;
1430 end if;
1431 close month_year_action;
1432 end loop;
1433 --
1434 close month_year_action_sequence;
1435 ----------------------------------------------------------------------------------
1436 -- Bulk Balance Fetch for Bug 3064282
1437 ----------------------------------------------------------------------------------
1438 g_balance_value_tab.delete;
1439 g_context_tab.delete;
1440 g_detailed_bal_out_tab.delete;
1441
1442 --
1443 for counter in 1..t_ytd_balanceid_store.count
1444 loop
1445 g_balance_value_tab(counter).defined_balance_id := t_ytd_balanceid_store(counter).defined_balance_id;
1446 g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1447 end loop;
1448
1449 ----------------------------------------------------------------------------------
1450 -- Bug 3223822 - Modified call to the function pay_balance_pkg.get_value
1451 -- Bug 3430277 - Put a condition before function pay_balance_pkg.get_value call.
1452 ----------------------------------------------------------------------------------
1453 if month_year_action_rec.assact_id is not null then
1454 pay_balance_pkg.get_value( month_year_action_rec.assact_id,
1455 g_balance_value_tab,
1456 g_context_tab,
1457 false,
1458 false,
1459 g_detailed_bal_out_tab );
1460 end if;
1461
1462 ----------------------------------------------------------------------------------
1463 -- Bug 3249043 - v_run_ass_action_id is initialized to latest persion assact_id
1464 -- Assign here so cursor variable can be accessed outside of loop
1465 -- Bug# 3328760 - Added g_detailed_bal_out_tab.exists(counter) check
1466 ----------------------------------------------------------------------------------
1467 for counter in 1..t_ytd_balanceid_store.count
1468 loop
1469 if l_person_counter = 1 then
1470 if g_detailed_bal_out_tab.exists(counter) then
1471 ytd_balance_rec(counter).balance_id := t_ytd_balanceid_store(counter).user_entity_id;
1472 ytd_balance_rec(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0) ;
1473 v_run_ass_action_id := month_year_action_rec.assact_id;
1474 end if;
1475 else
1476 if g_detailed_bal_out_tab.exists(counter) then
1477 if ytd_balance_rec.exists(counter) then
1478 ytd_balance_rec(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0)
1479 + ytd_balance_rec(counter).balance_value;
1480 end if;
1481 end if;
1482 end if;
1483 end loop;
1484 --
1485 --
1486 ------------------------------------------------------------------------------------
1487 -- Bug#3501927 A8A usablity
1488 -- Bug#3933332 Added one more flag g_org_a8a_flag to check if a8a is applicable.
1489 -------------------------------------------------------------------------------------
1490 if month_year_action_rec.assact_id is NOT NULL and g_org_a8a_flag ='Y' then
1491 a8a_balances_value( person_id_rec(l_person_counter).person_id,
1492 month_year_action_rec.assact_id,
1493 month_year_action_rec.tax_uid,
1494 l_person_counter );
1495 end if;
1496 --
1497 g_balance_value_tab.delete;
1498 g_detailed_bal_out_tab.delete;
1499 end if;
1500 ------------------------------------------------------------------------------------------------
1501 -- Bug# 2858074 - Remove the values in the cursor variables and assign the variables to NULL
1502 -- so that these variables will be populated with values in the next loop
1503 -- Bug# 3328760 - These conditions are moved inside loop.
1504 ------------------------------------------------------------------------------------------------
1505 month_year_action_sequence_rec.act_seq := null;
1506 month_year_action_rec.assact_id := null;
1507 --
1508 end loop ;
1509 end if;
1510 ------------------------------------------------------------------------------------------------
1511 -- Bug 3162955 Month Balance Implementation
1512 -- Bug 3162955 - Check whether multiple runs in a month exists for the OCBC rehired employee,
1513 -- If there exists multiple runs then sum the balances for the month
1514 -- and then archive the month details only once
1515 ------------------------------------------------------------------------------------------------
1516 if duplicate_exists = 'Y' then
1517 for counter in 1 .. mtd_balance_rec.count
1518 loop
1519 for icounter in 1 .. mtd_balance_rec.count
1520 loop
1521 if mtd_balance_rec(counter).balance_id = mtd_balance_rec(icounter).balance_id and
1522 mtd_balance_rec(counter).date_earned = mtd_balance_rec(icounter).date_earned and
1523 mtd_balance_rec(counter).person_id <> mtd_balance_rec(icounter).person_id and
1524 mtd_balance_rec(counter).archive_status = 'Y' then
1525 mtd_balance_rec(counter).balance_value := mtd_balance_rec(counter).balance_value
1526 + mtd_balance_rec(icounter).balance_value;
1527 mtd_balance_rec(icounter).archive_status := 'N';
1528 end if;
1529 end loop;
1530 end loop;
1531 --
1532 if t_user_entity_id.count >= 0 then
1533 l_arch_counter := t_user_entity_id.count + 1;
1534 else
1535 l_arch_counter := 1;
1536 end if;
1537 --
1538 for counter in 1 .. mtd_balance_rec.count
1539 loop
1540 if mtd_balance_rec(counter).archive_status = 'Y' then
1541 t_user_entity_id(l_arch_counter) := mtd_balance_rec(counter).balance_id;
1542 t_archive_value(l_arch_counter) := nvl(mtd_balance_rec(counter).balance_value,0);
1543 t_date_earned(l_arch_counter) := mtd_balance_rec(counter).date_earned_archive;
1544 l_arch_counter := l_arch_counter + 1;
1545 end if;
1546 end loop;
1547 end if;
1548 ------------------------------------------------------------------------------------------------
1549 -- Bug 3038605 - Added the following code to archive balances with 0 values for months with no payroll runs
1550 -- Logic Used:
1551 -- Search the pl/sql table month_recs to see if the specified month is already archived.
1552 -- a) If not archived then archive months details with 0 amounts.
1553 -- b) Else reset the flag l_pmon_counter and search for next months
1554 ------------------------------------------------------------------------------------------------
1555 for i in 1..12
1556 loop
1557 ----------------------------------------------------------------------------------------------
1558 -- Search if specified months is already archived
1559 -- Bug 3205321 - Compare month with variable i instead of MON format from lookup MONTH_CODE.
1560 ----------------------------------------------------------------------------------------------
1561 for j in 1..l_payroll_mon_counter-1
1562 loop
1563 if month_recs(j).month = i then
1564 l_pmon_counter := true;
1565 end if;
1566 end loop;
1567 --
1568 if l_pmon_counter = false then
1569 -------------------------------------------------------------------------------------------
1570 -- Archive 0 balance amounts as there are no runs in this perticular month
1571 -------------------------------------------------------------------------------------------
1572 if t_user_entity_id.count >= 0 then
1573 l_arch_counter := t_user_entity_id.count + 1;
1574 else
1575 l_arch_counter := 1;
1576 end if;
1577 --
1578 per_le_mtd_bal := 0;
1579 --
1580 for counter in 1..t_month_balanceid_store.count
1581 loop
1582 if t_month_balanceid_store.exists(counter) then
1583 t_user_entity_id(l_arch_counter) := t_month_balanceid_store(counter).user_entity_id;
1584 t_archive_value(l_arch_counter) := per_le_mtd_bal;
1585 t_date_earned(l_arch_counter) := to_char(last_day(to_date('01-'||to_char(i)||'-'||p_basis_year,'DD-MM-YYYY')),'YYYY/MM/DD HH:MM:SS');
1586 l_arch_counter := l_arch_counter + 1;
1587 end if;
1588 end loop;
1589 else
1590 l_pmon_counter := false;
1591 end if;
1592 end loop;
1593 ------------------------------------------------------------------------------------------------
1594 -- Bug: 3260855 Bulk Insert into ff_archive_items for month balances
1595 ------------------------------------------------------------------------------------------------
1596 select context_id
1597 into l_asac_cont_id
1598 from ff_contexts
1599 where context_name = 'ASSIGNMENT_ACTION_ID' ;
1600 --
1601 select context_id
1602 into l_tax_cont_id
1603 from ff_contexts
1604 where context_name = 'TAX_UNIT_ID' ;
1605 --
1606 select context_id
1607 into l_date_cont_id
1608 from ff_contexts
1609 where context_name = 'DATE_EARNED' ;
1610 --
1611 forall counter in 1..t_user_entity_id.count
1612 insert into ff_archive_items
1613 ( archive_item_id,
1614 user_entity_id,
1615 context1,
1616 value,
1617 archive_type )
1618 values
1619 ( ff_archive_items_s.nextval,
1620 t_user_entity_id(counter),
1621 p_assignment_action_id,
1622 t_archive_value(counter),
1623 'AAP' )
1624 returning archive_item_id bulk collect into t_archive_items ;
1625 --
1626 forall counter in t_archive_items.first..t_archive_items.last
1627 insert into ff_archive_item_contexts
1628 ( archive_item_id,
1629 sequence_no,
1630 context,
1631 context_id )
1632 values
1633 ( t_archive_items(counter),
1634 1,
1635 p_assignment_action_id,
1636 l_asac_cont_id );
1637 --
1638 forall counter in t_archive_items.first..t_archive_items.last
1639 insert into ff_archive_item_contexts
1640 ( archive_item_id,
1641 sequence_no,
1642 context,
1643 context_id )
1644 values
1645 ( t_archive_items(counter),
1646 2,
1647 p_tax_unit_id,
1648 l_tax_cont_id );
1649 --
1650 forall counter in t_archive_items.first..t_archive_items.last
1651 insert into ff_archive_item_contexts
1652 ( archive_item_id,
1653 sequence_no,
1654 context,
1655 context_id )
1656 values
1657 ( t_archive_items(counter),
1658 3,
1659 t_date_earned(counter),
1660 l_date_cont_id );
1661 --
1662 t_archive_items.delete;
1663 t_user_entity_id.delete;
1664 t_archive_value.delete;
1665 t_date_earned.delete;
1666 l_arch_counter := 1;
1667 --
1668 for counter in 1..ytd_balance_rec.count
1669 loop
1670 if ytd_balance_rec.exists(counter) then
1671 t_user_entity_id(l_arch_counter) := ytd_balance_rec(counter).balance_id;
1672 t_archive_value(l_arch_counter) := ytd_balance_rec(counter).balance_value;
1673 l_arch_counter := l_arch_counter + 1;
1674 end if;
1675 end loop;
1676 ---------------------------------------------------------------------------------------------------
1677 -- Bug# 3501927 A8A_USABLITY
1678 ---------------------------------------------------------------------------------------------------
1679 --Bug#3933332
1680 if g_org_a8a_flag ='Y' then
1681 --
1682 for counter in 1..ytd_a8a_balance_rec.count
1683 loop
1684 if ytd_a8a_balance_rec.exists(counter) then
1685 t_user_entity_id(l_arch_counter) := ytd_a8a_balance_rec(counter).balance_id;
1686 t_archive_value(l_arch_counter) := ytd_a8a_balance_rec(counter).balance_value;
1687 l_arch_counter := l_arch_counter + 1;
1688 end if;
1689 end loop;
1690 --
1691 end if;
1692 ------------------------------------------------------------------------------------------------
1693 -- Bug: 3260855 - Bulk Insert into ff_archive_items for ytd balances
1694 ------------------------------------------------------------------------------------------------
1695 forall counter in 1..t_user_entity_id.count
1696 insert into ff_archive_items
1697 ( archive_item_id,
1698 user_entity_id,
1699 context1,
1700 value,
1701 archive_type )
1702 values
1703 ( ff_archive_items_s.nextval,
1704 t_user_entity_id(counter),
1705 p_assignment_action_id,
1706 t_archive_value(counter),
1707 'AAP' )
1708 returning archive_item_id bulk collect into t_archive_items ;
1709 --
1710 forall counter in t_archive_items.first..t_archive_items.last
1711 insert into ff_archive_item_contexts
1712 ( archive_item_id,
1713 sequence_no,
1714 context,
1715 context_id )
1716 values
1717 ( t_archive_items(counter),
1718 1,
1719 p_assignment_action_id,
1720 l_asac_cont_id );
1721 --
1722 forall counter in t_archive_items.first..t_archive_items.last
1723 insert into ff_archive_item_contexts
1724 ( archive_item_id,
1725 sequence_no,
1726 context,
1727 context_id )
1728 values
1729 ( t_archive_items(counter),
1730 2,
1731 p_tax_unit_id,
1732 l_tax_cont_id );
1733 ------------------------------------------------------------------------------------------------
1734 -- Bug# 2833530 - Added p_person_id as the parameter for the archive_balance_dates for the
1735 -- employees having terminated and rehired in the same financial year
1736 ------------------------------------------------------------------------------------------------
1737 archive_balance_dates ( p_person_id,
1738 p_basis_year,
1739 p_business_group_id,
1740 p_assignment_action_id,
1741 v_run_ass_action_id,
1742 p_tax_unit_id );
1743 if g_debug then
1744 hr_utility.set_location('pysgirar: End of archive_balances',100);
1745 end if;
1746 end archive_balances;
1747 ---------------------------------------------------------------------------
1748 -- Copies the standard balance route code, but instead of selecting the run
1749 -- result value, selects the date_earned.
1750 -- Bug#2833530
1751 -- bug 2724020
1752 ---------------------------------------------------------------------------
1753 procedure archive_balance_dates
1754 ( p_person_id in per_all_people_f.person_id%TYPE,
1755 p_basis_year in varchar2,
1756 p_business_group_id in hr_organization_units.business_group_id%type,
1757 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1758 p_run_ass_action_id in pay_assignment_actions.assignment_action_id%type,
1759 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type )
1760 is
1761 --
1762 v_date_from ff_archive_items.value%type;
1763 v_date_to ff_archive_items.value%type;
1764 v_no_of_times number;
1765 v_date_from_old ff_archive_items.value%type;
1766 v_date_to_old ff_archive_items.value%type;
1767 v_no_of_times_old number;
1768 v_no_of_times_411 number;
1769 v_no_of_times_413 number;
1770 v_265_indicator varchar2(1);
1771 v_moa_410_date ff_archive_items.value%type;
1772 v_moa_411_date ff_archive_items.value%type;
1773 v_moa_413_date ff_archive_items.value%type;
1774 l_terminated varchar2(1);
1775 l_prev_ass_id per_all_assignments_f.assignment_id%TYPE;
1776 l_new_ass_id per_all_assignments_f.assignment_id%TYPE;
1777 l_run_ass_action_id pay_assignment_actions.assignment_action_id%TYPE;
1778 l_term_max_assact_id pay_assignment_actions.assignment_action_id%TYPE;
1779 l_person_id per_all_people_f.person_id%type;
1780 v_person_id per_all_people_f.person_id%type;
1781
1782 ---------------------------------------------------------------------------
1783 -- Bug# 2833530
1784 -- Added check_termination and get_max_assactid cursors
1785 -- Bug# 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
1786 ---------------------------------------------------------------------------
1787 cursor check_termination( c_person_id per_all_people_f.person_id%TYPE,
1788 c_basis_year varchar2 )
1789 is
1790 select 'Y',
1791 oldpaaf.assignment_id,
1792 newpaaf.assignment_id
1793 from per_people_f pap,
1794 per_assignments_f oldpaaf,
1795 per_assignments_f newpaaf
1796 where pap.person_id = c_person_id
1797 and pap.person_id = oldpaaf.person_id
1798 and oldpaaf.person_id = newpaaf.person_id
1799 and oldpaaf.assignment_type = 'E' /* Bug 5033609 */
1800 and newpaaf.assignment_type = 'E' /* Bug 5033609 */
1801 and oldpaaf.assignment_id <> newpaaf.assignment_id
1802 and oldpaaf.effective_end_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
1803 and newpaaf.effective_start_date
1804 and newpaaf.effective_start_date between oldpaaf.effective_end_date
1805 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY');
1806 ---------------------------------------------------------------------------
1807 -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
1808 ---------------------------------------------------------------------------
1809 cursor get_max_assactid( c_prev_ass_id per_all_assignments_f.assignment_id%TYPE ,
1810 c_basis_year varchar2,
1811 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
1812 c_business_group_id hr_organization_units.business_group_id%type )
1813 is
1814 select assact1.assignment_action_id
1815 from pay_assignment_actions assact1,
1816 pay_payroll_actions pact1,
1817 per_assignments_f paaf1
1818 where assact1.tax_unit_id = c_tax_unit_id
1819 and paaf1.assignment_id = c_prev_ass_id
1820 and paaf1.assignment_id = assact1.assignment_id
1821 and pact1.payroll_action_id = assact1.payroll_action_id
1822 and paaf1.business_group_id = c_business_group_id
1823 and pact1.action_status = 'C'
1824 and assact1.action_sequence =
1825 ( select max(assact.action_sequence)
1826 from pay_assignment_actions assact,
1827 pay_payroll_actions pact,
1828 per_assignments_f paaf
1829 where paaf.assignment_id = paaf1.assignment_id
1830 and paaf.assignment_id = assact.assignment_id
1831 and pact.payroll_action_id = assact.payroll_action_id
1832 and paaf.business_group_id = paaf1.business_group_id
1833 and assact.tax_unit_id = assact1.tax_unit_id
1834 and pact.action_type in ('Q','R','B')
1835 and pact.action_status = 'C'
1836 and pact.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
1837 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY'));
1838
1839 -----------------------------------------------------------------------------------------
1840 ---Bug#3956870 Function uses run balances to fetch balance details if they are valid
1841 -------------------------------------------------------------------------------------------
1842 --
1843 procedure get_balance_dates ( p_asg_action_id in pay_assignment_actions.assignment_action_id%type,
1844 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type,
1845 p_balance_name in pay_balance_types.balance_name%type,
1846 p_business_group_id in hr_organization_units.business_group_id%type,
1847 p_date_from out nocopy ff_archive_items.value%type,
1848 p_date_to out nocopy ff_archive_items.value%type,
1849 p_no_of_times out nocopy number )
1850 is
1851 c_def_balance_id pay_defined_balances.defined_balance_id%type;
1852 c_run_balance_status pay_balance_validation.run_balance_status%type ;
1853
1854 --
1855 cursor balance_dates_rr
1856 is
1857 select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,
1858 fnd_date.date_to_canonical(max(pact.date_earned)) date_to,
1859 sum(decode(pact.action_type,'V',-1,1)) no_of_times
1860 from pay_run_result_values target,
1861 pay_balance_feeds_f feed,
1862 pay_balance_types pbt,
1863 pay_run_results rr,
1864 pay_assignment_actions assact,
1865 pay_assignment_actions bal_assact,
1866 pay_payroll_actions pact,
1867 pay_payroll_actions bact,
1868 per_assignments_f ass
1869 where bal_assact.assignment_action_id = p_asg_action_id
1870 and bal_assact.payroll_action_id = bact.payroll_action_id
1871 and feed.balance_type_id = pbt.balance_type_id + decode(target.input_value_id,null,0,0)
1872 and pbt.legislation_code = 'SG'
1873 and pbt.balance_name = p_balance_name
1874 and feed.input_value_id = target.input_value_id
1875 and nvl(target.result_value, '0') <> '0'
1876 and target.run_result_id = rr.run_result_id
1877 and rr.assignment_action_id = assact.assignment_action_id
1878 and assact.payroll_action_id = pact.payroll_action_id
1879 and pact.effective_date between feed.effective_start_date and feed.effective_end_date
1880 and rr.status in ('P','PA')
1881 and assact.action_sequence <= bal_assact.action_sequence
1882 and assact.assignment_id = ass.assignment_id
1883 and bal_assact.assignment_id = assact.assignment_id /* added the join for bug#2227759 */
1884 and exists ( select null
1885 from per_assignments_f start_ass
1886 where start_ass.assignment_id = bal_assact.assignment_id
1887 and person_id = ass.person_id )
1888 and pact.effective_date between ass.effective_start_date and ass.effective_end_date
1889 and assact.tax_unit_id = p_tax_unit_id
1890 and pact.effective_date >= trunc(bact.effective_date,'Y');
1891 --
1892 cursor balance_dates_rb
1893 is
1894 select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,
1895 fnd_date.date_to_canonical(max(prb.effective_date)) date_to,
1896 sum(decode(ppa.action_type,'V',-1,1)) no_of_times
1897 from pay_run_balances prb,
1898 pay_assignment_actions ASSACT,
1899 pay_payroll_actions PACT,
1900 per_assignments_f ass,
1901 pay_payroll_actions ppa,
1902 pay_assignment_actions paa
1903 where prb.defined_balance_id = c_def_balance_id
1904 and assact.assignment_action_id = p_asg_action_id
1905 and assact.payroll_action_id = pact.payroll_action_id
1906 and prb.assignment_action_id = paa.assignment_action_id
1907 and ppa.payroll_action_id = paa.payroll_action_id
1908 and prb.action_sequence <= assact.action_sequence
1909 and prb.effective_date <= pact.effective_date
1910 and prb.balance_value <> 0
1911 and ASS.person_id = (select person_id
1912 from per_assignments_f START_ASS
1913 where START_ASS.assignment_id = assact.assignment_id
1914 and rownum = 1)
1915 and prb.effective_date between ASS.effective_start_date
1916 and ASS.effective_end_date
1917 and prb.assignment_id = ass.assignment_id
1918 and prb.tax_unit_id = p_tax_unit_id
1919 and prb.effective_date >= trunc(PACT.effective_date,'Y');
1920
1921 ------
1922 -- 3956870 Included get_balance_id, get_balance_status cursors to fetch balance id and status details.
1923 ------
1924 cursor get_balance_id(c_balance_name pay_balance_types.balance_name%type)
1925 is
1926 select pdb.defined_balance_id
1927 from pay_defined_balances pdb,
1928 pay_balance_types pbt,
1929 pay_balance_dimensions pbd
1930 where pbt.balance_name = c_balance_name
1931 and pbd.dimension_name = '_ASG_LE_RUN'
1932 and pbt.balance_type_id = pdb.balance_type_id
1933 and pbd.balance_dimension_id = pdb.balance_dimension_id
1934 and pdb.legislation_code = 'SG'
1935 and pbt.legislation_code = 'SG'
1936 and pbd.legislation_code = 'SG';
1937
1938 --
1939 cursor get_balance_status(c_def_balance_id pay_defined_balances.defined_balance_id%type,
1940 c_business_group_id hr_organization_units.business_group_id%type )
1941 is
1942 select run_balance_status
1943 from pay_balance_validation
1944 where defined_balance_id = c_def_balance_id
1945 and business_group_id = c_business_group_id;
1946 --
1947 begin
1948 open get_balance_id(p_balance_name);
1949 fetch get_balance_id into c_def_balance_id;
1950 close get_balance_id;
1951 --
1952 begin
1953 -------------------------------------------------------------------------------------------
1954 ---Bug#3956870 Balance status details are stored in the PL/SQL table t_bal_stat_rec
1955 -------------------------------------------------------------------------------------------
1956 c_run_balance_status := 'U';
1957 if t_bal_stat_rec.count > 0 then
1958 for l_dup_count in t_bal_stat_rec.first..t_bal_stat_rec.last
1959 loop
1960 if ( p_business_group_id = t_bal_stat_rec(l_dup_count).business_group_id and
1961 c_def_balance_id = t_bal_stat_rec(l_dup_count).defined_balance_id ) then
1962 c_run_balance_status := t_bal_stat_rec(l_dup_count).run_balance_status;
1963 exit ;
1964 end if;
1965 end loop;
1966 end if;
1967 ---------------------------------------------------------------------------------------------
1968 -- Bug# 3956870 c_run_balance_status will remain as 'U' if the balance status information is not
1969 -- present in the PL/SQL table t_bal_stat_rec
1970 ---------------------------------------------------------------------------------------------
1971 if c_run_balance_status = 'U' then
1972 open get_balance_status(c_def_balance_id,p_business_group_id);
1973 fetch get_balance_status into c_run_balance_status;
1974 close get_balance_status;
1975 l_counter := t_bal_stat_rec.count + 1;
1976 t_bal_stat_rec(l_counter).business_group_id := p_business_group_id;
1977 t_bal_stat_rec(l_counter).defined_balance_id := c_def_balance_id;
1978 t_bal_stat_rec(l_counter).run_balance_status := c_run_balance_status;
1979 end if;
1980 exception
1981 when others then
1982 c_run_balance_status := 'I' ;
1983 end;
1984
1985 --
1986 if c_run_balance_status = 'V' then
1987 open balance_dates_rb;
1988 fetch balance_dates_rb into p_date_from, p_date_to, p_no_of_times ;
1989 close balance_dates_rb;
1990 else
1991 open balance_dates_rr;
1992 fetch balance_dates_rr into p_date_from, p_date_to, p_no_of_times ;
1993 close balance_dates_rr;
1994
1995 end if;
1996 --
1997 end get_balance_dates;
1998 --
1999 --
2000 begin
2001 if g_debug then
2002 hr_utility.set_location('pysgirar: Start of archive_balance_dates',10);
2003 end if;
2004 ---------------------------------------------------------------------------------------
2005 -- Bug 2843586. Added v_no_of_times>0 check along with
2006 -- balance_dates%found check for all the below balances
2007 -- Archive IR8A_MOA_265 dates and indicator
2008 -- Bug#2833530
2009 -- Bug#3933332 Removed check g_rehire_same_person_table.exists(p_person_id)
2010 -- as after enhencement 3435334 this check is not required.
2011 ---------------------------------------------------------------------------------------
2012 v_person_id := p_person_id;
2013 ----------------------------------------------------------------------------------
2014 -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
2015 -- in employee_if_latest() function
2016 ----------------------------------------------------------------------------------
2017 if g_person_id_tab.count > 1 then
2018 v_person_id := g_person_id_tab.last;
2019 end if;
2020 --
2021 open check_termination( v_person_id, p_basis_year );
2022 fetch check_termination into l_terminated, l_prev_ass_id, l_new_ass_id;
2023 close check_termination;
2024 --
2025 if l_terminated = 'Y' then
2026 open get_max_assactid( l_prev_ass_id, p_basis_year, p_tax_unit_id, p_business_group_id );
2027 fetch get_max_assactid into l_term_max_assact_id;
2028 close get_max_assactid;
2029 ---------------------------------------------------------------------------
2030 -- Bug#3956870
2031 ----------------------------------------------------------------------------
2032 get_balance_dates ( p_run_ass_action_id,
2033 p_tax_unit_id,
2034 'IR8A_MOA_265',
2035 p_business_group_id,
2036 v_date_from ,
2037 v_date_to ,
2038 v_no_of_times);
2039 --
2040 get_balance_dates ( l_term_max_assact_id,
2041 p_tax_unit_id,
2042 'IR8A_MOA_265',
2043 p_business_group_id,
2044 v_date_from_old ,
2045 v_date_to_old ,
2046 v_no_of_times_old);
2047 --------------------------------------------------------------------------
2048 if v_date_from is null and v_date_to is null then
2049 v_date_from := v_date_from_old;
2050 v_date_to := v_date_to_old;
2051 elsif v_date_from_old is not null then
2052 v_date_from := v_date_from_old;
2053 end if;
2054 --
2055 v_no_of_times := nvl(v_no_of_times,0) + nvl(v_no_of_times_old,0);
2056 else
2057 ---------------------------------------------------------------------------
2058 -- Bug#3956870
2059 ----------------------------------------------------------------------------
2060 get_balance_dates ( p_run_ass_action_id,
2061 p_tax_unit_id,
2062 'IR8A_MOA_265',
2063 p_business_group_id,
2064 v_date_from ,
2065 v_date_to ,
2066 v_no_of_times);
2067 end if;
2068 ---------------------------------------------------------------------------------------
2069 -- Bug 2651294
2070 ---------------------------------------------------------------------------------------
2071 if (v_no_of_times = 1) then
2072 v_265_indicator := 'O';
2073 elsif (v_no_of_times >= 12) then
2074 v_265_indicator := 'M';
2075 else
2076 v_265_indicator := 'B';
2077 end if;
2078 ---------------------------------------------------------------------------------------
2079 -- Bug#2843586. Archive dates only if v_no_of_times is greater then zero
2080 ---------------------------------------------------------------------------------------
2081 if v_no_of_times > 0 then
2082 archive_item ('X_IR8A_MOA_265_DATE_FROM', p_assignment_action_id, v_date_from);
2083 archive_item ('X_IR8A_MOA_265_DATE_TO', p_assignment_action_id, v_date_to);
2084 archive_item ('X_IR8A_MOA_265_INDICATOR', p_assignment_action_id, v_265_indicator);
2085 end if;
2086 --------------------------------------------------------------------------------------
2087 -- Archive IR8A_MOA_369 dates
2088 --------------------------------------------------------------------------------------
2089 if l_terminated = 'Y' then
2090 ---------------------------------------------------------------------------
2091 -- Bug#3956870
2092 ----------------------------------------------------------------------------
2093 get_balance_dates ( p_run_ass_action_id,
2094 p_tax_unit_id,
2095 'IR8A_MOA_369',
2096 p_business_group_id,
2097 v_date_from ,
2098 v_date_to ,
2099 v_no_of_times);
2100 ------------------------------------------------------------------------------
2101 if v_date_from is null and v_date_to is null then
2102 ---------------------------------------------------------------------------
2103 -- Bug#3956870
2104 ----------------------------------------------------------------------------
2105 get_balance_dates ( l_term_max_assact_id,
2106 p_tax_unit_id,
2107 'IR8A_MOA_369',
2108 p_business_group_id,
2109 v_date_from ,
2110 v_date_to ,
2111 v_no_of_times);
2112 ------------------------------------------------------------------------------
2113
2114 end if;
2115 else
2116 ---------------------------------------------------------------------------
2117 -- Bug#3956870
2118 ----------------------------------------------------------------------------
2119 get_balance_dates ( p_run_ass_action_id,
2120 p_tax_unit_id,
2121 'IR8A_MOA_369',
2122 p_business_group_id,
2123 v_date_from ,
2124 v_date_to ,
2125 v_no_of_times);
2126 ------------------------------------------------------------------------------
2127 end if;
2128 --
2129 -- Bug 5078454, to store the date of balane 369 has into a
2130 -- global value
2131
2132 if v_date_to is not null and v_no_of_times > 0 then
2133 g_moa_369_date := v_date_to;
2134 end if;
2135 --------------------------------------------------------------------------------------
2136 -- Archive IR8A_MOA_340 dates
2137 --------------------------------------------------------------------------------------
2138 if l_terminated = 'Y' then
2139 ---------------------------------------------------------------------------
2140 -- Bug#3956870
2141 ----------------------------------------------------------------------------
2142 get_balance_dates ( p_run_ass_action_id,
2143 p_tax_unit_id,
2144 'IR8A_MOA_340',
2145 p_business_group_id,
2146 v_date_from ,
2147 v_date_to ,
2148 v_no_of_times);
2149 ------------------------------------------------------------------------------
2150 if v_date_from is null and v_date_to is null then
2151 ---------------------------------------------------------------------------
2152 -- Bug#3956870
2153 ----------------------------------------------------------------------------
2154 get_balance_dates ( l_term_max_assact_id,
2155 p_tax_unit_id,
2156 'IR8A_MOA_340',
2157 p_business_group_id,
2158 v_date_from ,
2159 v_date_to ,
2160 v_no_of_times);
2161 ------------------------------------------------------------------------------
2162 end if;
2163 else
2164 ---------------------------------------------------------------------------
2165 -- Bug#3956870
2166 ----------------------------------------------------------------------------
2167 get_balance_dates ( p_run_ass_action_id,
2168 p_tax_unit_id,
2169 'IR8A_MOA_340',
2170 p_business_group_id,
2171 v_date_from ,
2172 v_date_to ,
2173 v_no_of_times);
2174 ------------------------------------------------------------------------------
2175
2176 end if;
2177 --
2178 if v_date_to is not null and v_no_of_times > 0 then
2179 archive_item ('X_IR8A_MOA_340_DATE', p_assignment_action_id, v_date_to);
2180 end if;
2181 --------------------------------------------------------------------------------------
2182 -- Archive Additional Earnings dates
2183 --------------------------------------------------------------------------------------
2184 if l_terminated = 'Y' then
2185 ---------------------------------------------------------------------------
2186 -- Bug#3956870
2187 ----------------------------------------------------------------------------
2188 get_balance_dates ( p_run_ass_action_id,
2189 p_tax_unit_id,
2190 'Additional Earnings',
2191 p_business_group_id,
2192 v_date_from ,
2193 v_date_to ,
2194 v_no_of_times);
2195 ------------------------------------------------------------------------------
2196 if v_date_from is null and v_date_to is null then
2197 ---------------------------------------------------------------------------
2198 -- Bug#3956870
2199 ----------------------------------------------------------------------------
2200 get_balance_dates ( l_term_max_assact_id,
2201 p_tax_unit_id,
2202 'Additional Earnings',
2203 p_business_group_id,
2204 v_date_from ,
2205 v_date_to ,
2206 v_no_of_times);
2207 ------------------------------------------------------------------------------
2208 end if;
2209 else
2210 ---------------------------------------------------------------------------
2211 -- Bug#3956870
2212 ----------------------------------------------------------------------------
2213 get_balance_dates ( p_run_ass_action_id,
2214 p_tax_unit_id,
2215 'Additional Earnings',
2216 p_business_group_id,
2217 v_date_from ,
2218 v_date_to ,
2219 v_no_of_times);
2220 ------------------------------------------------------------------------------
2221
2222 end if;
2223 --
2224 if v_date_to is not null and v_no_of_times > 0 then
2225 archive_item ('X_ADDITIONAL_EARNINGS_DATE', p_assignment_action_id, v_date_to);
2226 end if;
2227 --------------------------------------------------------------------------------------
2228 -- Start new code for bug 2724020
2229 -- Bug No : 2724020 - archive IR8S_MOA_410 balance dates
2230 -- Modified for Bug 3095823 replaced v_date_to with v_moa_410_date
2231 --------------------------------------------------------------------------------------
2232 if l_terminated = 'Y' then
2233 ---------------------------------------------------------------------------
2234 -- Bug#3956870
2235 ----------------------------------------------------------------------------
2236 get_balance_dates ( p_run_ass_action_id,
2237 p_tax_unit_id,
2238 'IR8S_MOA_410',
2239 p_business_group_id,
2240 v_date_from ,
2241 v_moa_410_date ,
2242 v_no_of_times);
2243 ------------------------------------------------------------------------------
2244 if v_date_from is null and v_moa_410_date is null then
2245 ---------------------------------------------------------------------------
2246 -- Bug#3956870
2247 ----------------------------------------------------------------------------
2248 get_balance_dates ( l_term_max_assact_id,
2249 p_tax_unit_id,
2250 'IR8S_MOA_410',
2251 p_business_group_id,
2252 v_date_from ,
2253 v_moa_410_date ,
2254 v_no_of_times);
2255 ------------------------------------------------------------------------------
2256 end if;
2257 else
2258 ---------------------------------------------------------------------------
2259 -- Bug#3956870
2260 ----------------------------------------------------------------------------
2261 get_balance_dates ( p_run_ass_action_id,
2262 p_tax_unit_id,
2263 'IR8S_MOA_410',
2264 p_business_group_id,
2265 v_date_from ,
2266 v_moa_410_date ,
2267 v_no_of_times);
2268 ------------------------------------------------------------------------------
2269
2270 end if;
2271 --
2272 if v_moa_410_date is not null and v_no_of_times > 0 then
2273 archive_item ('X_IR8S_MOA_410_DATE', p_assignment_action_id, v_moa_410_date);
2274 end if;
2275 --------------------------------------------------------------------------------------
2276 -- Start new code for bug 2724020
2277 -- Archive IR8S_MOA_411 dates
2278 --------------------------------------------------------------------------------------
2279 if l_terminated = 'Y' then
2280 ---------------------------------------------------------------------------
2281 -- Bug#3956870
2282 ----------------------------------------------------------------------------
2283 get_balance_dates ( p_run_ass_action_id,
2284 p_tax_unit_id,
2285 'IR8S_MOA_411',
2286 p_business_group_id,
2287 v_date_from ,
2288 v_moa_411_date ,
2289 v_no_of_times_411);
2290 ------------------------------------------------------------------------------
2291 if v_date_from is null and v_moa_411_date is null then
2292 ---------------------------------------------------------------------------
2293 -- Bug#3956870
2294 ----------------------------------------------------------------------------
2295 get_balance_dates ( l_term_max_assact_id,
2296 p_tax_unit_id,
2297 'IR8S_MOA_411',
2298 p_business_group_id,
2299 v_date_from ,
2300 v_moa_411_date ,
2301 v_no_of_times_411);
2302 ------------------------------------------------------------------------------
2303 end if;
2304 else
2305 ---------------------------------------------------------------------------
2306 -- Bug#3956870
2307 ----------------------------------------------------------------------------
2308 get_balance_dates ( p_run_ass_action_id,
2309 p_tax_unit_id,
2310 'IR8S_MOA_411',
2311 p_business_group_id,
2312 v_date_from ,
2313 v_moa_411_date ,
2314 v_no_of_times_411);
2315 ------------------------------------------------------------------------------
2316 end if;
2317 --------------------------------------------------------------------------------------
2318 -- Archive IR8S_MOA_412 dates
2319 --------------------------------------------------------------------------------------
2320 if l_terminated = 'Y' then
2321 ---------------------------------------------------------------------------
2322 -- Bug#3956870
2323 ----------------------------------------------------------------------------
2324 get_balance_dates ( p_run_ass_action_id,
2325 p_tax_unit_id,
2326 'IR8S_MOA_412',
2327 p_business_group_id,
2328 v_date_from ,
2329 v_date_to ,
2330 v_no_of_times);
2331 ------------------------------------------------------------------------------
2332 if v_date_from is null and v_date_to is null then
2333 ---------------------------------------------------------------------------
2334 -- Bug#3956870
2335 ----------------------------------------------------------------------------
2336 get_balance_dates ( l_term_max_assact_id,
2337 p_tax_unit_id,
2338 'IR8S_MOA_412',
2339 p_business_group_id,
2340 v_date_from ,
2341 v_date_to ,
2342 v_no_of_times);
2343 ------------------------------------------------------------------------------
2344 end if;
2345 else
2346 ---------------------------------------------------------------------------
2347 -- Bug#3956870
2348 ----------------------------------------------------------------------------
2349 get_balance_dates ( p_run_ass_action_id,
2350 p_tax_unit_id,
2351 'IR8S_MOA_412',
2352 p_business_group_id,
2353 v_date_from ,
2354 v_date_to ,
2355 v_no_of_times);
2356 ------------------------------------------------------------------------------
2357 end if;
2358 --
2359 if v_date_to is not null and v_moa_411_date is not null then
2360 if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_411_date) then
2361 v_moa_411_date := v_date_to;
2362 else
2363 v_no_of_times := v_no_of_times_411;
2364 end if;
2365 elsif v_date_to is not null and v_moa_411_date is null then
2366 v_moa_411_date := v_date_to;
2367 end if;
2368 --
2369 if v_date_to is null then
2370 v_no_of_times := v_no_of_times_411;
2371 end if;
2372 --
2373 if v_moa_411_date is not null and v_no_of_times > 0 then
2374 archive_item ('X_IR8S_MOA_411_DATE', p_assignment_action_id, v_moa_411_date);
2375 end if;
2376 --------------------------------------------------------------------------------------
2377 -- Archive IR8S_MOA_413 dates
2378 --------------------------------------------------------------------------------------
2379 if l_terminated = 'Y' then
2380 ---------------------------------------------------------------------------
2381 -- Bug#3956870
2382 ----------------------------------------------------------------------------
2383 get_balance_dates ( p_run_ass_action_id,
2384 p_tax_unit_id,
2385 'IR8S_MOA_413',
2386 p_business_group_id,
2387 v_date_from ,
2388 v_moa_413_date ,
2389 v_no_of_times_413);
2390 ------------------------------------------------------------------------------
2391 if v_date_from is null and v_moa_413_date is null then
2392 ---------------------------------------------------------------------------
2393 -- Bug#3956870
2394 ----------------------------------------------------------------------------
2395 get_balance_dates ( l_term_max_assact_id,
2396 p_tax_unit_id,
2397 'IR8S_MOA_413',
2398 p_business_group_id,
2399 v_date_from ,
2400 v_moa_413_date ,
2401 v_no_of_times_413);
2402 ------------------------------------------------------------------------------
2403
2404 end if;
2405 else
2406 ---------------------------------------------------------------------------
2407 -- Bug#3956870
2408 ----------------------------------------------------------------------------
2409 get_balance_dates ( p_run_ass_action_id,
2410 p_tax_unit_id,
2411 'IR8S_MOA_413',
2412 p_business_group_id,
2413 v_date_from ,
2414 v_moa_413_date ,
2415 v_no_of_times_413);
2416 ------------------------------------------------------------------------------
2417 end if;
2418 --------------------------------------------------------------------------------------
2419 -- Archive IR8S_MOA_414 dates
2420 --------------------------------------------------------------------------------------
2421 if l_terminated = 'Y' then
2422 ---------------------------------------------------------------------------
2423 -- Bug#3956870
2424 ----------------------------------------------------------------------------
2425 get_balance_dates ( p_run_ass_action_id,
2426 p_tax_unit_id,
2427 'IR8S_MOA_414',
2428 p_business_group_id,
2429 v_date_from ,
2430 v_date_to ,
2431 v_no_of_times);
2432 ------------------------------------------------------------------------------
2433 if v_date_from is null and v_date_to is null then
2434 ---------------------------------------------------------------------------
2435 -- Bug#3956870
2436 ----------------------------------------------------------------------------
2437 get_balance_dates ( l_term_max_assact_id,
2438 p_tax_unit_id,
2439 'IR8S_MOA_414',
2440 p_business_group_id,
2441 v_date_from ,
2442 v_date_to ,
2443 v_no_of_times);
2444 ------------------------------------------------------------------------------
2445 end if;
2446 end if;
2447 --
2448 ---------------------------------------------------------------------------
2449 -- Bug#3956870
2450 ----------------------------------------------------------------------------
2451 get_balance_dates ( p_run_ass_action_id,
2452 p_tax_unit_id,
2453 'IR8S_MOA_414',
2454 p_business_group_id,
2455 v_date_from ,
2456 v_date_to ,
2457 v_no_of_times);
2458 ------------------------------------------------------------------------------
2459 if v_date_to is not null and v_moa_413_date is not null then
2460 if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_413_date) then
2461 v_moa_413_date := v_date_to;
2462 else
2463 v_no_of_times := v_no_of_times_413;
2464 end if;
2465 elsif v_date_to is not null and v_moa_413_date is null then
2466 v_moa_413_date := v_date_to;
2467 end if;
2468 --
2469 if v_date_to is null then
2470 v_no_of_times := v_no_of_times_413;
2471 end if;
2472 --
2473 if v_moa_413_date is not null and v_no_of_times > 0 then
2474 archive_item ('X_IR8S_MOA_413_DATE', p_assignment_action_id, v_moa_413_date);
2475 end if;
2476 --
2477 if g_debug then
2478 hr_utility.set_location('pysgirar: End of archive_balance_dates',100);
2479 end if;
2480
2481 end archive_balance_dates;
2482 ---------------------------------------------------------------------------
2483 -- Copies the Org Developer DF route code to get Legal Entity information.
2484 ---------------------------------------------------------------------------
2485 procedure archive_org_info
2486 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2487 p_business_group_id in hr_organization_units.business_group_id%type,
2488 p_legal_entity_id in hr_organization_units.organization_id%type,
2489 p_person_id in per_all_people_f.person_id%type,
2490 p_basis_start in date,
2491 p_basis_end in date)
2492 is
2493 --
2494 v_legal_entity_name hr_organization_information.org_information1%type;
2495 v_er_income_tax_number hr_organization_information.org_information4%type;
2496 v_er_auth_person hr_organization_information.org_information10%type;
2497 v_er_auth_person_desig hr_organization_information.org_information11%type;
2498 v_er_ohq_status hr_organization_information.org_information12%type;
2499 v_er_iras_category hr_organization_information.org_information13%type;
2500 v_er_telephone_no hr_organization_information.org_information14%type;
2501 v_er_payer_id hr_organization_information.org_information15%type;
2502 -- Added for bug 3093991
2503 v_er_designation_type hr_organization_information.org_information17%type;
2504 v_er_position_seg_type hr_organization_information.org_information18%type;
2505 -- Added for bug 5078454
2506 v_er_bonus_date hr_organization_information.org_information8%type;
2507 -- Added for bug 5435088
2508 v_er_auth_person_email hr_organization_information.org_information5%type;
2509 v_er_division hr_organization_information.org_information8%type;
2510 l_pri_assignment_id per_all_assignments_f.assignment_id%type;
2511 l_id_assignment_id per_all_assignments_f.assignment_id%type;
2512
2513
2514 --
2515 cursor org_info
2516 ( c_business_group_id hr_organization_units.business_group_id%type,
2517 c_legal_entity_id hr_organization_units.organization_id%type)
2518 is
2519 select target.org_information1,
2520 target.org_information4,
2521 target.org_information10,
2522 target.org_information11,
2523 target.org_information12,
2524 target.org_information13,
2525 target.org_information14,
2526 target.org_information15,
2527 target.org_information17,
2528 target.org_information18,
2529 target.org_information8
2530 from hr_organization_units org,
2531 hr_organization_information target,
2532 hr_soft_coding_keyflex scl
2533 where org.business_group_id = c_business_group_id
2534 and org.organization_id = c_legal_entity_id
2535 and org.organization_id = target.organization_id
2536 and target.org_information_context = 'SG_LEGAL_ENTITY'
2537 and to_char(org.organization_id) = scl.segment1;
2538 --
2539 -- Added for bug 5435088
2540 cursor org_info2
2541 ( c_business_group_id hr_organization_units.business_group_id%type,
2542 c_legal_entity_id hr_organization_units.organization_id%type)
2543 is
2544 select target.org_information5,
2545 target.org_information7
2546 from hr_organization_units org,
2547 hr_organization_information target,
2548 hr_soft_coding_keyflex scl
2549 where org.business_group_id = c_business_group_id
2550 and org.organization_id = c_legal_entity_id
2551 and org.organization_id = target.organization_id
2552 and target.org_information_context = 'SG_LE_IRAS'
2553 and to_char(org.organization_id) = scl.segment1;
2554
2555 begin
2556 if g_debug then
2557 hr_utility.set_location('pysgirar: Start of archive_org_info',10);
2558 end if;
2559 --
2560 hr_utility.trace('g_org_run before:'||g_org_run);
2561 if g_org_run <> 'Y' then
2562 hr_utility.trace('g_org_run: 1'||g_org_run);
2563 open org_info (p_business_group_id, p_legal_entity_id);
2564 fetch org_info into v_legal_entity_name,
2565 v_er_income_tax_number,
2566 v_er_auth_person,
2567 v_er_auth_person_desig,
2568 v_er_ohq_status,
2569 v_er_iras_category,
2570 v_er_telephone_no,
2571 v_er_payer_id,
2572 v_er_designation_type,
2573 v_er_position_seg_type,
2574 v_er_bonus_date;
2575
2576 -- The org information are the same for all employees, bug 5435088
2577 if org_info%found then
2578 g_org_run := 'Y';
2579 g_legal_entity_name := v_legal_entity_name;
2580 g_er_income_tax_number := v_er_income_tax_number;
2581 g_er_auth_person := v_er_auth_person;
2582 g_er_auth_person_desig := v_er_auth_person_desig;
2583 g_er_ohq_status := v_er_ohq_status;
2584 g_er_iras_category := v_er_iras_category;
2585 g_er_telephone_no := v_er_telephone_no;
2586 g_er_payer_id := v_er_payer_id;
2587 g_er_designation_type := v_er_designation_type;
2588 g_er_position_seg_type := v_er_position_seg_type;
2589 g_er_bonus_date := v_er_bonus_date;
2590 g_er_payer_id_check := check_payer_id(v_er_income_tax_number,
2591 v_er_payer_id);
2592 -- Added for bug 5435088
2593 open org_info2 (p_business_group_id, p_legal_entity_id);
2594 fetch org_info2 into v_er_auth_person_email,
2595 v_er_division;
2596 if org_info2%found then
2597 g_er_auth_person_email := v_er_auth_person_email;
2598 g_er_division := v_er_division;
2599 end if;
2600 close org_info2;
2601 end if;
2602 close org_info;
2603 end if;
2604 --
2605 if g_org_run = 'Y' then
2606 archive_item ('X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME', p_assignment_action_id, g_legal_entity_name);
2607 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_INCOME_TAX_NUMBER', p_assignment_action_id, g_er_income_tax_number);
2608 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_AUTH_PERSON', p_assignment_action_id, g_er_auth_person);
2609 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_AUTH_PERSON_DESIG', p_assignment_action_id, g_er_auth_person_desig);
2610 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_OHQ_STATUS', p_assignment_action_id, g_er_ohq_status);
2611 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_IRAS_CATEGORY', p_assignment_action_id, g_er_iras_category);
2612 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_TELEPHONE_NUMBER', p_assignment_action_id, g_er_telephone_no);
2613 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_PAYER_ID', p_assignment_action_id, g_er_payer_id);
2614 -- Added for bug 3093991
2615 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_JOB_DES_TYPE', p_assignment_action_id, g_er_designation_type);
2616 -- Added for bug 5435088
2617 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_AUTH_PERSON_EMAIL', p_assignment_action_id, g_er_auth_person_email);
2618 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_DIVISION', p_assignment_action_id, g_er_division);
2619 archive_item ('X_SG_LEGAL_ENTITY_SG_ER_ID_CHECK', p_assignment_action_id, g_er_payer_id_check);
2620
2621
2622 -- Bug 5078454, if moa369 balance is not zero, then store the date
2623 -- from LE to the global value g_moa_369_date if it is not blank
2624 if g_moa_369_date is not null then
2625 if to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY') = to_char(g_basis_end,'YYYY') then
2626 g_moa_369_date := to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(g_er_bonus_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_bonus_date),'DD');
2627 end if;
2628 end if;
2629
2630 -- Added for bug 4890964, the info. based on the assignment should
2631 -- be archived for latest LE with primary defined, or latest
2632 -- effective start dtae with max assignment_id if it has no primary
2633 -- defined
2634 --
2635 l_pri_assignment_id := pri_LE_if_latest(p_person_id,
2636 p_legal_entity_id,
2637 p_basis_start,
2638 p_basis_end);
2639
2640 if l_pri_assignment_id is not null then
2641
2642 archive_job_designation(p_assignment_action_id,
2643 p_person_id,
2644 l_pri_assignment_id,
2645 p_legal_entity_id,
2646 p_basis_start,
2647 p_basis_end,
2648 g_er_designation_type,
2649 g_er_position_seg_type);
2650
2651 archive_assignment_eits(p_assignment_action_id,
2652 p_person_id,
2653 l_pri_assignment_id,
2654 p_legal_entity_id,
2655 p_basis_start,
2656 p_basis_end);
2657
2658 archive_ass_payment_method(p_assignment_action_id,
2659 p_person_id,
2660 l_pri_assignment_id,
2661 p_legal_entity_id,
2662 p_basis_start,
2663 p_basis_end);
2664
2665 archive_ass_bonus_date_eits(p_assignment_action_id,
2666 p_person_id,
2667 l_pri_assignment_id,
2668 p_legal_entity_id,
2669 p_basis_start,
2670 p_basis_end);
2671
2672 else
2673 l_id_assignment_id := id_LE_if_latest(p_person_id,
2674 p_legal_entity_id,
2675 p_basis_start,
2676 p_basis_end);
2677
2678 if l_id_assignment_id is not null then
2679
2680 archive_job_designation(p_assignment_action_id,
2681 p_person_id,
2682 l_id_assignment_id,
2683 p_legal_entity_id,
2684 p_basis_start,
2685 p_basis_end,
2686 g_er_designation_type,
2687 g_er_position_seg_type);
2688
2689 archive_assignment_eits(p_assignment_action_id,
2690 p_person_id,
2691 l_id_assignment_id,
2692 p_legal_entity_id,
2693 p_basis_start,
2694 p_basis_end );
2695
2696 archive_ass_payment_method(p_assignment_action_id,
2697 p_person_id,
2698 l_pri_assignment_id,
2699 p_legal_entity_id,
2700 p_basis_start,
2701 p_basis_end);
2702
2703 archive_ass_bonus_date_eits(p_assignment_action_id,
2704 p_person_id,
2705 l_pri_assignment_id,
2706 p_legal_entity_id,
2707 p_basis_start,
2708 p_basis_end);
2709
2710 end if;
2711 end if;
2712 end if;
2713 --
2714 if g_debug then
2715 hr_utility.set_location('pysgirar: End of archive_org_info',20);
2716 end if;
2717 end archive_org_info;
2718
2719 --------------------------------------------------------------------------
2720 -- Bug 5435088, Added for payroll date
2721 --------------------------------------------------------------------------
2722 procedure archive_payroll_date
2723 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2724 p_business_group_id in hr_organization_units.business_group_id%type,
2725 p_legal_entity_id in hr_organization_units.organization_id%type,
2726 p_person_id in per_all_people_f.person_id%type,
2727 p_basis_year in varchar2) is
2728
2729 v_payroll_date varchar2(30);
2730 cursor payroll_date
2731 ( c_business_group_id hr_organization_units.business_group_id%type,
2732 c_legal_entity_id hr_organization_units.organization_id%type,
2733 c_person_id per_all_people_f.person_id%type,
2734 c_basis_year varchar2)
2735 is
2736 select fnd_date.date_to_canonical(max(ppamax.effective_date))
2737 from per_assignments_f paamax,
2738 pay_assignment_actions pacmax,
2739 pay_payroll_actions ppamax
2740 where ppamax.business_group_id = c_business_group_id
2741 and pacmax.tax_unit_id = c_legal_entity_id
2742 and paamax.person_id = c_person_id
2743 and paamax.assignment_id = pacmax.assignment_id
2744 and ppamax.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
2745 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
2746 and ppamax.payroll_action_id = pacmax.payroll_action_id
2747 and ppamax.action_type in ('R','B','I','Q','V');
2748
2749 begin
2750 if g_debug then
2751 hr_utility.set_location('pysgirar: Start of archive_payroll_date',10);
2752 end if;
2753 --
2754 open payroll_date (p_business_group_id, p_legal_entity_id, p_person_id, p_basis_year);
2755 fetch payroll_date into v_payroll_date;
2756 --
2757 if payroll_date%found then
2758 archive_item ('X_PER_PAYROLL_DATE', p_assignment_action_id, v_payroll_date);
2759 end if;
2760 --
2761 close payroll_date;
2762 --
2763 if g_debug then
2764 hr_utility.set_location('pysgirar: End of archive_payroll_date',20);
2765 end if;
2766 end archive_payroll_date;
2767
2768 ---------------------------------------------------------------------------
2769 -- Copies the standard Person information route code.
2770 ---------------------------------------------------------------------------
2771 procedure archive_person_details
2772 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2773 p_person_id in per_all_people_f.person_id%type,
2774 p_basis_start in date,
2775 p_basis_end in date )
2776 is
2777 --
2778 v_national_identifier per_all_people_f.national_identifier%type;
2779 v_sex hr_lookups.meaning%type;
2780 v_date_of_birth varchar2(30);
2781 ---------------------------------------------------------------------------
2782 -- Bug# 2920732 - Modified the cursor to use secured view per_people_f
2783 -- Bug 2645599
2784 ---------------------------------------------------------------------------
2785 cursor person_details
2786 ( c_person_id per_all_people_f.person_id%type,
2787 c_basis_start date,
2788 c_basis_end date )
2789 is
2790 select people.national_identifier,
2791 h.meaning,
2792 fnd_date.date_to_canonical(people.date_of_birth)
2793 from per_people_f people,
2794 hr_lookups h
2795 where people.person_id = c_person_id
2796 and people.effective_start_date = (
2797 select max(people1.effective_start_date)
2798 from per_people_f people1
2799 where people1.person_id = people.person_id
2800 and people1.effective_start_date <= c_basis_end
2801 and people1.effective_end_date >= c_basis_start
2802 )
2803 and h.lookup_type (+)= 'SEX'
2804 and h.lookup_code (+)= people.sex
2805 and h.application_id (+)= 800;
2806 --
2807 begin
2808 if g_debug then
2809 hr_utility.set_location('pysgirar: Start of archive_person_details',10);
2810 end if;
2811 --
2812 open person_details (p_person_id, p_basis_start, p_basis_end);
2813 fetch person_details into v_national_identifier, v_sex, v_date_of_birth;
2814 --
2815 if person_details%found then
2816 g_national_identifier := v_national_identifier;
2817 archive_item ('X_PER_NATIONAL_IDENTIFIER', p_assignment_action_id, v_national_identifier);
2818 archive_item ('X_PER_SEX', p_assignment_action_id, v_sex);
2819 archive_item ('X_PER_DATE_OF_BIRTH', p_assignment_action_id, v_date_of_birth);
2820 end if;
2821 --
2822 close person_details;
2823 --
2824 if g_debug then
2825 hr_utility.set_location('pysgirar: End of archive_person_details',20);
2826 end if;
2827 end archive_person_details;
2828 ---------------------------------------------------------------------------
2829 -- Copies the standard Person Address information route code.
2830 ---------------------------------------------------------------------------
2831 procedure archive_person_addresses
2832 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2833 p_person_id in per_all_people_f.person_id%type,
2834 p_basis_start in date,
2835 p_basis_end in date )
2836 is
2837 v_style per_addresses.style%type;
2838 v_address_type per_addresses.address_type%type;
2839 v_address_line_1 per_addresses.address_line1%type;
2840 v_address_line_2 per_addresses.address_line2%type;
2841 v_address_line_3 per_addresses.address_line3%type;
2842 v_postal_code per_addresses.postal_code%type;
2843 v_country per_addresses.country%type;
2844 v_country_code varchar2(3);
2845 ---------------------------------------------------------------------------
2846 -- Padded postal code upto 6 chars, if null then store 999999*/
2847 -- Bug2647074
2848 -- Modified for bug 5435088, added style, type and country
2849 ---------------------------------------------------------------------------
2850 cursor person_address
2851 (c_person_id per_all_people_f.person_id%type,
2852 c_basis_start date,
2853 c_basis_end date) is
2854 select addr.style,
2855 addr.address_type,
2856 addr.address_line1,
2857 addr.address_line2,
2858 addr.address_line3,
2859 lpad(nvl(addr.postal_code,'999999'),6,'0'),
2860 addr.country
2861 from per_addresses addr,
2862 fnd_territories_tl a
2863 where addr.person_id (+)= c_person_id
2864 and addr.primary_flag (+)= 'Y'
2865 and c_basis_end between nvl(addr.date_from, c_basis_start)
2866 and nvl(addr.date_to, c_basis_end) /* Bug 2654499 */
2867 and a.territory_code (+)= addr.country
2868 and a.language (+)= userenv('LANG');
2869
2870 begin
2871 if g_debug then
2872 hr_utility.set_location('pysgirar: Start of archive_person_addresses',10);
2873 end if;
2874 -- Primary Address
2875 open person_address (p_person_id, p_basis_start, p_basis_end);
2876 fetch person_address into v_style,
2877 v_address_type,
2878 v_address_line_1,
2879 v_address_line_2,
2880 v_address_line_3,
2881 v_postal_code,
2882 v_country;
2883 if person_address%found then
2884 -- Added for bug 5435088
2885 if v_country = 'SG' then
2886 if v_style = 'SG' then
2887 archive_item ('X_PER_ADR_TYPE', p_assignment_action_id, 'L');
2888 elsif v_style = 'SG_GLB' then
2889 archive_item ('X_PER_ADR_TYPE', p_assignment_action_id, 'N');
2890 end if;
2891 else
2892 archive_item ('X_PER_ADR_TYPE', p_assignment_action_id, 'F');
2893 if v_country is not null then
2894 v_country_code := get_country_code (v_country);
2895 end if;
2896 archive_item ('X_PER_ADR_COUNTRY_CODE', p_assignment_action_id, v_country_code);
2897 end if;
2898
2899 archive_item ('X_PER_ADR_LINE_1', p_assignment_action_id, v_address_line_1);
2900 archive_item ('X_PER_ADR_LINE_2', p_assignment_action_id, v_address_line_2);
2901 archive_item ('X_PER_ADR_LINE_3', p_assignment_action_id, v_address_line_3);
2902 archive_item ('X_PER_ADR_POSTAL_CODE', p_assignment_action_id, v_postal_code);
2903 -- else
2904 -- archive_item ('X_PER_ADR_POSTAL_CODE', p_assignment_action_id,'999999'); /*Bug#2691831*/
2905 end if;
2906 close person_address;
2907 --
2908 if g_debug then
2909 hr_utility.set_location('pysgirar: End of archive_person_addresses',20);
2910 end if;
2911 end archive_person_addresses;
2912 ---------------------------------------------------------------------------
2913 -- Copies the standard Person Company Quarters address
2914 -- Bug 4688761, to separate from the above procedure
2915 ---------------------------------------------------------------------------
2916 procedure archive_person_cq_addresses
2917 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2918 p_person_id in per_all_people_f.person_id%type,
2919 p_basis_start in date,
2920 p_basis_end in date )
2921 is
2922 v_address_line_1 per_addresses.address_line1%type;
2923 v_address_line_2 per_addresses.address_line2%type;
2924 v_address_line_3 per_addresses.address_line3%type;
2925 v_date_from varchar2(30);
2926 v_date_to varchar2(30);
2927
2928 -- Added for bug 2373475
2929 cursor person_cq_address
2930 (c_person_id per_all_people_f.person_id%type,
2931 c_basis_start date,
2932 c_basis_end date) is
2933 select addr.address_line1,
2934 addr.address_line2,
2935 addr.address_line3,
2936 fnd_date.date_to_canonical(addr.date_from),
2937 fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */
2938 from per_addresses addr,
2939 fnd_territories_tl a
2940 where addr.person_id (+) = c_person_id
2941 and a.territory_code (+)= addr.country
2942 and a.language (+)= userenv('LANG')
2943 and addr.address_type = 'SG_CQ' -- SG specific Company Quarters Address Type
2944 and nvl(addr.date_to, c_basis_end) =
2945 (select max(nvl(date_to, c_basis_end))
2946 from per_addresses
2947 where address_type = 'SG_CQ'
2948 and person_id = addr.person_id
2949 and (date_from <= c_basis_end
2950 and nvl(date_to, c_basis_end) >= c_basis_start));/*Bug 2654499*/
2951
2952 begin
2953 if g_debug then
2954 hr_utility.set_location('pysgirar: Start of archive_person_cq_addresses',10);
2955 end if;
2956
2957 -- Company Quarters Address, bug 2373475
2958 open person_cq_address (p_person_id, p_basis_start, p_basis_end);
2959 fetch person_cq_address into v_address_line_1,
2960 v_address_line_2,
2961 v_address_line_3,
2962 v_date_from,
2963 v_date_to;
2964 if person_cq_address%found then
2965 archive_item ('X_PER_CQ_ADR_LINE_1', p_assignment_action_id, v_address_line_1);
2966 archive_item ('X_PER_CQ_ADR_LINE_2', p_assignment_action_id, v_address_line_2);
2967 archive_item ('X_PER_CQ_ADR_LINE_3', p_assignment_action_id, v_address_line_3);
2968 archive_item ('X_PER_CQ_DATE_FROM', p_assignment_action_id, v_date_from);
2969 archive_item ('X_PER_CQ_DATE_TO', p_assignment_action_id, v_date_to);
2970 end if;
2971 close person_cq_address;
2972 --
2973 if g_debug then
2974 hr_utility.set_location('pysgirar: End of archive_person_cq_addresses',20);
2975 end if;
2976 end archive_person_cq_addresses;
2977
2978 ---------------------------------------------------------------------------
2979 -- Copies the standard Employee information route code.
2980 ---------------------------------------------------------------------------
2981 procedure archive_emp_details
2982 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2983 p_person_id in per_all_people_f.person_id%type,
2984 p_basis_start in date,
2985 p_basis_end in date) is
2986
2987 v_date_start varchar2(30);
2988 v_termination_date varchar2(30);
2989
2990 cursor emp_hire_details
2991 (c_person_id per_all_people_f.person_id%type,
2992 c_basis_end date) is
2993 select fnd_date.date_to_canonical(min(service.date_start))
2994 from per_periods_of_service service
2995 where service.person_id = c_person_id
2996 and service.date_start <= c_basis_end; /*Bug 2668599*/
2997
2998 /* Bug# 2920732 - Modified the cursor to use secured view per_assignments_f */
2999 cursor emp_termination_details
3000 (c_person_id per_all_people_f.person_id%type,
3001 c_basis_start date,
3002 c_basis_end date) is
3003 select fnd_date.date_to_canonical(service.actual_termination_date)
3004 from per_assignments_f assign,
3005 per_periods_of_service service
3006 where service.person_id = c_person_id
3007 and service.period_of_service_id (+)= assign.period_of_service_id
3008 and assign.effective_start_date = (
3009 select max(assign1.effective_start_date)
3010 from per_assignments_f assign1
3011 where assign1.person_id = service.person_id
3012 and assign1.assignment_type = 'E' /* Bug 5033609 */
3013 and (assign1.effective_start_date <= c_basis_end
3014 and assign1.effective_end_date >= c_basis_start)
3015 );/*Bug 2654499*/
3016 begin
3017 if g_debug then
3018 hr_utility.set_location('pysgirar: Start of archive_emp_details',10);
3019 end if;
3020 --
3021 open emp_hire_details (p_person_id, p_basis_end);
3022 fetch emp_hire_details into v_date_start;
3023
3024 if emp_hire_details%found then
3025 archive_item ('X_EMP_HIRE_DATE', p_assignment_action_id, v_date_start);
3026 end if;
3027 close emp_hire_details;
3028
3029 open emp_termination_details (p_person_id, p_basis_start, p_basis_end);
3030 fetch emp_termination_details into v_termination_date;
3031
3032 if emp_termination_details%found then
3033 archive_item ('X_EMP_TERM_DATE', p_assignment_action_id, v_termination_date);
3034 end if;
3035 close emp_termination_details;
3036 --
3037 if g_debug then
3038 hr_utility.set_location('pysgirar: End of archive_emp_details',20);
3039 end if;
3040 end archive_emp_details;
3041 ---------------------------------------------------------------------------
3042 -- Copies the standard Person Developer DF route code.
3043 ---------------------------------------------------------------------------
3044 procedure archive_people_flex
3045 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3046 p_person_id in per_all_people_f.person_id%type,
3047 p_basis_start in date,
3048 p_basis_end in date)
3049 is
3050 --
3051 v_sg_legal_name per_all_people_f.per_information1%type;
3052 v_pp_country per_all_people_f.per_information3%type;
3053 v_permit_type per_all_people_f.per_information6%type;
3054 v_permit_date varchar2(30);
3055 v_income_tax_no per_all_people_f.per_information12%type;
3056 v_payee_id_type per_all_people_f.per_information23%type;
3057 l_payee_id_check char(1);
3058 l_nationality_code varchar2(3);
3059 ---------------------------------------------------------------------------
3060 -- Bug# 2920732 - Modified the cursor to use secured view per_people_f
3061 -- Bug 2645599
3062 -- Bug 5435088, Added Payee ID Type and permit date
3063 ---------------------------------------------------------------------------
3064 cursor emp_details
3065 ( c_person_id per_all_people_f.person_id%type,
3066 c_basis_start date,
3067 c_basis_end date )
3068 is
3069 select people.per_information1,
3070 people.per_information3,
3071 people.per_information6,
3072 to_char(fnd_date.canonical_to_date(people.per_information9),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'MM')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'DD'), -- Bug 5435088
3073 -- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088
3074 people.per_information12,
3075 people.per_information23
3076 from per_people_f people
3077 where people.person_id = c_person_id
3078 and people.effective_start_date = (
3079 select max(people1.effective_start_date)
3080 from per_people_f people1
3081 where people1.person_id = people.person_id
3082 and people1.effective_start_date <= c_basis_end
3083 and people1.effective_end_date >= c_basis_start);
3084 --
3085 begin
3086 if g_debug then
3087 hr_utility.set_location('pysgirar: Start of archive_people_flex',10);
3088 end if;
3089 --
3090 open emp_details (p_person_id, p_basis_start, p_basis_end);
3091 fetch emp_details into v_sg_legal_name, v_pp_country, v_permit_type, v_permit_date, v_income_tax_no, v_payee_id_type;
3092 --
3093 if emp_details%found then
3094 archive_item ('X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME', p_assignment_action_id, v_sg_legal_name);
3095 archive_item ('X_PEOPLE_FLEXFIELD_SG_SG_PP_COUNTRY', p_assignment_action_id, v_pp_country);
3096 archive_item ('X_PEOPLE_FLEXFIELD_SG_SG_PERMIT_TYPE', p_assignment_action_id, v_permit_type);
3097 if v_permit_type = 'PR' then
3098 if to_date(v_permit_date,'YYYY/MM/DD') >= add_months(p_basis_end,-24) then
3099 archive_item ('X_PER_PERMIT_STATUS_INDICATOR', p_assignment_action_id, 'Y');
3100 else
3101 archive_item ('X_PER_PERMIT_STATUS_INDICATOR', p_assignment_action_id, 'N');
3102 end if;
3103 end if;
3104 archive_item ('X_PEOPLE_FLEXFIELD_SG_SG_INCOME_TAX_NUMBER', p_assignment_action_id, v_income_tax_no);
3105 -- Added for bug 5435088
3106 if g_national_identifier is null then
3107 if v_income_tax_no is not null and
3108 v_payee_id_type is not null then
3109 l_payee_id_check := check_payee_id (v_income_tax_no,
3110 v_payee_id_type);
3111 end if;
3112 else
3113 if substr(g_national_identifier, 1, 1) = 'S' or
3114 substr(g_national_identifier, 1, 1) = 'T' then
3115 v_payee_id_type := '1';
3116 elsif substr(g_national_identifier, 1, 1) = 'F' or
3117 substr(g_national_identifier, 1, 1) = 'G' then
3118 v_payee_id_type := '2';
3119 end if;
3120 end if;
3121 archive_item ('X_PER_EE_PAYEE_ID_CHECK', p_assignment_action_id, l_payee_id_check);
3122 archive_item ('X_PEOPLE_FLEXFIELD_SG_SG_PAYEE_ID_TYPE', p_assignment_action_id, v_payee_id_type);
3123
3124 /* Bug 5873476 , fixed PR for 300,Singapore Citizen 301 */
3125 if v_permit_type = 'PR' then
3126 l_nationality_code := '300';
3127 elsif v_permit_type = 'SG' then
3128 l_nationality_code := '301';
3129 else
3130 l_nationality_code := get_country_code(v_pp_country);
3131 end if;
3132
3133 archive_item ('X_PER_NATIONALITY_CODE', p_assignment_action_id, l_nationality_code);
3134
3135 end if;
3136 --
3137 close emp_details;
3138 if g_debug then
3139 hr_utility.set_location('pysgirar: End of archive_people_flex',20);
3140 end if;
3141 end archive_people_flex;
3142
3143 ---------------------------------------------------------------------------
3144 -- Copies the standard Extra Person Information DF route code.
3145 ---------------------------------------------------------------------------
3146 procedure archive_person_eits
3147 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3148 p_person_id in per_all_people_f.person_id%type,
3149 p_basis_start in date,
3150 p_basis_end in date )
3151 is
3152 --
3153 v_section_45_applicable per_people_extra_info.pei_information1%type;
3154 v_income_tax_borne_by_employer per_people_extra_info.pei_information2%type;
3155 v_ir8s_applicable per_people_extra_info.pei_information3%type;
3156 v_exempt_remission per_people_extra_info.pei_information4%type;
3157 v_iras_approval per_people_extra_info.pei_information5%type;
3158 v_approval_date per_people_extra_info.pei_information6%type;
3159 v_retirement_fund per_people_extra_info.pei_information3%type;
3160 v_designated_pension per_people_extra_info.pei_information4%type;
3161 -- Added for bug 5435088
3162 v_name_of_bank per_people_extra_info.pei_information6%type;
3163 v_additional_information per_people_extra_info.pei_information1%type;
3164 ---------------------------------------------------------------------------
3165 -- Bug# 2920732 - Modified the cursor to use the secured view per_people_f
3166 -- Bug 5435088, Removed archiving gratuity_or_comp_info, gains_or_profit_from
3167 -- _shares, remarks
3168 -- Bug 6349937, removed hr_lookups which is not being used
3169 ---------------------------------------------------------------------------
3170 cursor person_eits
3171 ( c_person_id per_all_assignments_f.assignment_id%type,
3172 c_basis_start date,
3173 c_basis_end date )
3174 is
3175 select indicators.pei_information1,
3176 indicators.pei_information2,
3177 indicators.pei_information3,
3178 indicators.pei_information4, -- Exempt Remission
3179 indicators.pei_information5, -- Approval from IRAS
3180 to_char(fnd_date.canonical_to_date(indicators.pei_information6),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(indicators.pei_information6),'MM')||'/'||to_char(fnd_date.canonical_to_date(indicators.pei_information6),'DD'), -- Date of Approval
3181 details.pei_information3,
3182 details.pei_information4,
3183 details.pei_information6, -- Name of bank
3184 info.pei_information1
3185 from per_people_extra_info indicators,
3186 per_people_extra_info info,
3187 per_people_extra_info details,
3188 per_people_f people
3189 where people.person_id = c_person_id
3190 and people.effective_start_date = (
3191 select max(people1.effective_start_date)
3192 from per_people_f people1
3193 where people1.person_id = people.person_id
3194 and people1.effective_start_date <= c_basis_end
3195 and people1.effective_end_date >= c_basis_start)/*Bug 2645599*/
3196 and people.person_id = indicators.person_id(+)
3197 and indicators.information_type(+) = 'HR_IR8A_INDICATORS_SG'
3198 and people.person_id = details.person_id(+)
3199 and details.information_type(+) = 'HR_IR8A_FURTHER_DETAILS_SG'
3200 and people.person_id = info.person_id(+)
3201 and info.information_type(+) = 'HR_IRAS_ADDITIONAL_INFO_SG';
3202 --
3203 begin
3204 if g_debug then
3205 hr_utility.set_location('pysgirar: Start of archive_person_eits',10);
3206 end if;
3207 --
3208 open person_eits (p_person_id, p_basis_start, p_basis_end);
3209 fetch person_eits into v_section_45_applicable,
3210 v_income_tax_borne_by_employer,
3211 v_ir8s_applicable,
3212 v_exempt_remission,
3213 v_iras_approval,
3214 v_approval_date,
3215 v_retirement_fund,
3216 v_designated_pension,
3217 v_name_of_bank,
3218 v_additional_information;
3219 --
3220 if person_eits%found then
3221 archive_item ('X_HR_IR8A_INDICATORS_SG_PER_SECTION_45_APPLICABLE',
3222 p_assignment_action_id, v_section_45_applicable );
3223 archive_item ('X_HR_IR8A_INDICATORS_SG_PER_INCOME_TAX_BORNE_BY_EMPLOYER',
3224 p_assignment_action_id, v_income_tax_borne_by_employer);
3225 archive_item ('X_HR_IR8A_INDICATORS_SG_PER_IR8S_APPLICABLE',
3226 p_assignment_action_id, v_ir8s_applicable);
3227 if v_exempt_remission = 'N' then
3228 v_exempt_remission := null;
3229 end if;
3230 archive_item ('X_HR_IR8A_INDICATORS_SG_EXEMPT',
3231 p_assignment_action_id, v_exempt_remission);
3232 archive_item ('X_HR_IR8A_INDICATORS_SG_APPR_IRAS',
3233 p_assignment_action_id, v_iras_approval);
3234 archive_item ('X_HR_IR8A_INDICATORS_SG_DATE_OF_APPR_IRAS',
3235 p_assignment_action_id, v_approval_date);
3236 archive_item ('X_HR_IR8A_FURTHER_DETAILS_SG_PER_RETIREMENT_FUND',
3237 p_assignment_action_id, v_retirement_fund);
3238 archive_item ('X_HR_IR8A_FURTHER_DETAILS_SG_PER_DESIGNATED_PENSION',
3239 p_assignment_action_id, v_designated_pension);
3240 archive_item ('X_HR_IRAS_ADDITIONAL_INFO_SG_PER_ADDITIONAL_INFORMATION',
3241 p_assignment_action_id, v_additional_information);
3242 g_name_of_bank := v_name_of_bank; -- bug 5435088
3243 end if;
3244 close person_eits;
3245 --
3246 if g_debug then
3247 hr_utility.set_location('pysgirar: End of archive_person_eits',20);
3248 end if;
3249 end archive_person_eits;
3250
3251 ---------------------------------------------------------------------------
3252 -- Copies the standard Extra Assignment Information DF route code. - Bug 2373475
3253 -- Added p_assignment_id as parameter
3254 ---------------------------------------------------------------------------
3255 procedure archive_assignment_eits
3256 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3257 p_person_id in per_all_people_f.person_id%type,
3258 p_assignment_id in per_all_assignments_f.assignment_id%type,
3259 p_legal_entity_id in hr_organization_units.organization_id%type,
3260 p_basis_start in date,
3261 p_basis_end in date )
3262 is
3263 --
3264 v_voluntary_cpf_obligatory per_assignment_extra_info.aei_information2%type;
3265 v_appr_cpf_full per_assignment_extra_info.aei_information3%type;
3266 v_assignment_id per_assignments_f.assignment_id%type;
3267 ---------------------------------------------------------------------------
3268 -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3269 -- Bug# 4688761 - Modified cursor to check the legal entity, and need get the
3270 -- latest primary assignment
3271 -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3272 -- we pass the assignment_id as a parameter
3273 -- Bug 5435088 - Added field Approval from CPF to make full
3274 ---------------------------------------------------------------------------
3275 cursor assignment_eits
3276 ( c_person_id per_all_people_f.person_id%type,
3277 c_assignment_id per_assignments_f.assignment_id%type,
3278 c_legal_entity_id hr_organization_units.organization_id%type,
3279 c_basis_start date,
3280 c_basis_end date )
3281 is
3282 select /*+ USE_NL(aei) */
3283 aei.aei_information2,
3284 aei.aei_information3
3285 from per_assignments_f assign,
3286 per_assignment_extra_info aei,
3287 hr_soft_coding_keyflex hsc
3288 where assign.person_id = c_person_id
3289 and assign.assignment_id = c_assignment_id
3290 and assign.assignment_id = aei.assignment_id
3291 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3292 and hsc.segment1 = c_legal_entity_id
3293 and aei.information_type = 'HR_IR8S_INDICATORS_SG';
3294 --
3295 begin
3296 if g_debug then
3297 hr_utility.set_location('pysgirar: Start of archive_assignment_eits', 10);
3298 end if;
3299 --
3300 open assignment_eits (p_person_id, p_assignment_id, p_legal_entity_id, p_basis_start, p_basis_end);
3301 fetch assignment_eits into v_voluntary_cpf_obligatory,
3302 v_appr_cpf_full;
3303 --
3304 if assignment_eits%found then
3305 archive_item ('X_HR_IR8S_INDICATORS_SG_ASG_VOLUNTARY_CPF_OBLIGATORY',
3306 p_assignment_action_id, v_voluntary_cpf_obligatory);
3307 -- Added for bug 5435088
3308 archive_item ('X_HR_IR8S_INDICATORS_SG_ASG_APPR_CPF',
3309 p_assignment_action_id, v_appr_cpf_full);
3310 end if;
3311 close assignment_eits;
3312 --
3313 if g_debug then
3314 hr_utility.set_location('pysgirar: End of archive_assignment_eits', 20);
3315 end if;
3316 end archive_assignment_eits;
3317
3318 ---------------------------------------------------------------------------
3319 -- Bug 5078454, to get bonus date from the Assignment EIT
3320 -- archive it to DTM161 if it is not blank, otherwise archive the global
3321 -- value g_moa_369_date
3322 ---------------------------------------------------------------------------
3323 procedure archive_ass_bonus_date_eits
3324 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3325 p_person_id in per_all_people_f.person_id%type,
3326 p_assignment_id in per_all_assignments_f.assignment_id%type,
3327 p_legal_entity_id in hr_organization_units.organization_id%type,
3328 p_basis_start in date,
3329 p_basis_end in date )
3330 is
3331 --
3332 v_ass_bonus_date varchar2(10);
3333 v_assignment_id per_assignments_f.assignment_id%type;
3334
3335 cursor ass_bonus_date_eits
3336 ( c_person_id per_all_people_f.person_id%type,
3337 c_assignment_id per_assignments_f.assignment_id%type,
3338 c_legal_entity_id hr_organization_units.organization_id%type,
3339 c_basis_start date,
3340 c_basis_end date )
3341 is
3342 select /*+ USE_NL(aei) */
3343 to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(aei.aei_information1),'MM')||'/'||
3344 to_char(fnd_date.canonical_to_date(aei.aei_information1),'DD')
3345 from per_assignments_f assign,
3346 per_assignment_extra_info aei,
3347 hr_soft_coding_keyflex hsc
3348 where assign.person_id = c_person_id
3349 and assign.assignment_id = c_assignment_id
3350 and assign.assignment_id = aei.assignment_id
3351 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3352 and hsc.segment1 = c_legal_entity_id
3353 and aei.information_type = 'HR_NON_CONT_BONUS_INFO_SG'
3354 and aei.aei_information1 is not NULL
3355 and assign.assignment_type = 'E'
3356 and to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY') = to_char(c_basis_end,'YYYY');
3357
3358 --
3359 begin
3360
3361 if g_debug then
3362 hr_utility.set_location('pysgirar: Start of archive_ass_bonus_date_eits', 10);
3363 end if;
3364 --
3365 open ass_bonus_date_eits (p_person_id, p_assignment_id, p_legal_entity_id, p_basis_start, p_basis_end);
3366 fetch ass_bonus_date_eits into v_ass_bonus_date;
3367 --
3368 if ass_bonus_date_eits%found and g_moa_369_date is not null then
3369 g_moa_369_date := v_ass_bonus_date;
3370 end if;
3371 --
3372 archive_item ('X_IR8A_MOA_369_DATE', p_assignment_action_id, g_moa_369_date);
3373 --
3374 close ass_bonus_date_eits;
3375 --
3376 if g_debug then
3377 hr_utility.set_location('pysgirar: End of archive_ass_bonus_date_eits', 20);
3378 end if;
3379 end archive_ass_bonus_date_eits;
3380
3381 ---------------------------------------------------------------------------
3382 -- Bug 5435088, to get payment method from the assignment
3383 -- if the bank name from EIT is blank, it will archive the bank name from
3384 -- payment method
3385 -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3386 -- payment method
3387 ---------------------------------------------------------------------------
3388 procedure archive_ass_payment_method
3389 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3390 p_person_id in per_all_people_f.person_id%type,
3391 p_assignment_id in per_all_assignments_f.assignment_id%type,
3392 p_legal_entity_id in hr_organization_units.organization_id%type,
3393 p_basis_start in date,
3394 p_basis_end in date)
3395 is
3396 --
3397 v_ass_bank_name varchar2(10);
3398 v_assignment_id per_assignments_f.assignment_id%type;
3399
3400 cursor ass_payment_method
3401 ( c_person_id per_all_people_f.person_id%type,
3402 c_assignment_id per_assignments_f.assignment_id%type,
3403 c_legal_entity_id hr_organization_units.organization_id%type,
3404 c_basis_start in date,
3405 c_basis_end in date)
3406 is
3407
3408 SELECT pea.segment4 bank_name
3409 FROM per_assignments_f assign,
3410 hr_soft_coding_keyflex hsc,
3411 pay_external_accounts pea,
3412 pay_personal_payment_methods_f ppm,
3413 hr_lookups hl
3414 WHERE assign.person_id = c_person_id
3415 AND assign.assignment_id = c_assignment_id
3416 AND assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3417 AND hsc.segment1 = c_legal_entity_id
3418 AND assign.assignment_id = ppm.assignment_id
3419 AND pea.segment3 = hl.lookup_code (+)
3420 AND hl.lookup_type(+) = 'SG_ACCOUNT_TYPE'
3421 AND pea.external_account_id(+) = ppm.external_account_id
3422 AND assign.effective_start_date <= c_basis_end
3423 AND assign.effective_end_date >= c_basis_start
3424 AND ppm.effective_start_date <= c_basis_end
3425 AND ppm.effective_end_date >= c_basis_start
3426 and priority = 1
3427 and ppm.effective_start_date =
3428 (select max(ppm1.effective_start_date)
3429 from pay_personal_payment_methods_f ppm1
3430 where ppm1.assignment_id = ppm.assignment_id
3431 and ppm1.effective_start_date <= c_basis_end
3432 and ppm1.effective_end_date >= c_basis_start); /* Bug 5868910*/
3433 --
3434 begin
3435
3436 if g_debug then
3437 hr_utility.set_location('pysgirar: Start of archive_ass_payment_method', 10);
3438 end if;
3439 --
3440 --
3441 open ass_payment_method (p_person_id, p_assignment_id, p_legal_entity_id, p_basis_start, p_basis_end);
3442 fetch ass_payment_method into v_ass_bank_name;
3443 --
3444 if ass_payment_method%found and g_name_of_bank is null then
3445 g_name_of_bank := v_ass_bank_name;
3446 end if;
3447 --
3448 archive_item ('X_HR_IR8A_FURTHER_DETAILS_SG_NAME_OF_BANK',
3449 p_assignment_action_id, g_name_of_bank);
3450
3451 --
3452 close ass_payment_method;
3453 --
3454 if g_debug then
3455 hr_utility.set_location('pysgirar: End of archive_ass_payment_method', 20);
3456 end if;
3457 end archive_ass_payment_method;
3458
3459
3460 ---------------------------------------------------------------------------
3461 -- Bug 3093991, Select Grade, Job, Position or Job Designation user entered
3462 -- for Job Designation
3463 -- Bug 4890964, added p_assignment_id as parameter
3464 ---------------------------------------------------------------------------
3465 procedure archive_job_designation
3466 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3467 p_person_id in per_all_people_f.person_id%type,
3468 p_assignment_id in per_all_assignments_f.assignment_id%type,
3469 p_legal_entity_id in hr_organization_units.organization_id%type,
3470 p_basis_start in date,
3471 p_basis_end in date,
3472 p_er_designation_type in hr_organization_information.org_information17%type,
3473 p_er_position_seg_type in hr_organization_information.org_information18%type)
3474 is
3475 --
3476 v_designation hr_all_positions_f.name%type;
3477
3478 ---------------------------------------------------------------------------
3479 -- Bug 4688761 - Modified cursor to check the legal entity, and need get the
3480 -- latest primary assignment
3481 -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3482 -- we pass the assignment_id as a parameter
3483 -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3484 -- assignment
3485 ---------------------------------------------------------------------------
3486 cursor grade
3487 ( c_person_id per_all_people_f.person_id%type,
3488 c_assignment_id per_assignments_f.assignment_id%type,
3489 c_legal_entity_id hr_organization_units.organization_id%type,
3490 c_basis_start date,
3491 c_basis_end date) is
3492
3493 select grade.name
3494 from per_assignments_f assign,
3495 per_grades grade,
3496 hr_soft_coding_keyflex hsc
3497 where assign.person_id = c_person_id
3498 and assign.assignment_id = c_assignment_id
3499 and grade.grade_id = assign.grade_id
3500 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3501 and hsc.segment1 = c_legal_entity_id
3502 and assign.assignment_type = 'E'
3503 and assign.effective_start_date =
3504 (select max(paf1.effective_start_date)
3505 from per_assignments_f paf1
3506 where paf1.person_id = assign.person_id
3507 and paf1.assignment_id = assign.assignment_id
3508 and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3509 and paf1.effective_start_date <= c_basis_end
3510 and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
3511
3512 ---------------------------------------------------------------------------
3513 -- Bug 4688761 - Modified cursor to check the legal entity, and need get the
3514 -- latest primary assignment
3515 -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3516 -- we pass the assignment_id as a parameter
3517 -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3518 -- assignment
3519 ---------------------------------------------------------------------------
3520 cursor job
3521 ( c_person_id per_all_people_f.person_id%type,
3522 c_assignment_id per_all_assignments_f.assignment_id%type,
3523 c_legal_entity_id hr_organization_units.organization_id%type,
3524 c_basis_start date,
3525 c_basis_end date) is
3526
3527 select jbt.name
3528 from per_assignments_f assign,
3529 per_jobs_tl jbt,
3530 hr_soft_coding_keyflex hsc
3531 where assign.person_id = c_person_id
3532 and assign.assignment_id = c_assignment_id
3533 and jbt.job_id = assign.job_id
3534 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3535 and hsc.segment1 = c_legal_entity_id
3536 and jbt.language = userenv('LANG')
3537 and assign.assignment_type = 'E'
3538 and assign.effective_start_date =
3539 (select max(paf1.effective_start_date)
3540 from per_assignments_f paf1
3541 where paf1.person_id = assign.person_id
3542 and paf1.assignment_id = assign.assignment_id
3543 and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3544 and paf1.effective_start_date <= c_basis_end
3545 and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
3546
3547 ---------------------------------------------------------------------------
3548 -- Bug 4688761 - Modified cursor to check the legal entity, and need get the
3549 -- latest primary assignment
3550 -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3551 -- we pass the assignment_id as a parameter
3552 -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3553 -- assignment
3554 ---------------------------------------------------------------------------
3555 cursor position
3556 ( c_person_id per_all_people_f.person_id%type,
3557 c_assignment_id per_all_assignments_f.assignment_id%type,
3558 c_legal_entity_id hr_organization_units.organization_id%type,
3559 c_basis_start date,
3560 c_basis_end date) is
3561
3562 select pst.name
3563 from per_assignments_f assign,
3564 hr_all_positions_f_tl pst,
3565 hr_all_positions_f pos,
3566 hr_soft_coding_keyflex hsc
3567 where assign.person_id = c_person_id
3568 and assign.assignment_id = c_assignment_id
3569 and pos.position_id = assign.position_id
3570 and pst.position_id = pos.position_id
3571 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3572 and hsc.segment1 = c_legal_entity_id
3573 and pst.language = userenv('LANG')
3574 and assign.effective_start_date between NVL(pos.effective_start_date,to_date('01-01-1900','DD-MM-YYYY')) and NVL(pos.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
3575 and assign.assignment_type = 'E'
3576 and assign.effective_start_date =
3577 (select max(paf1.effective_start_date)
3578 from per_assignments_f paf1
3579 where paf1.person_id = assign.person_id
3580 and paf1.assignment_id = assign.assignment_id
3581 and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3582 and paf1.effective_start_date <= c_basis_end
3583 and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
3584
3585 ---------------------------------------------------------------------------
3586 -- Bug 4688761 - Modified cursor to get the latest primary assignment
3587 -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3588 -- we pass the assignment_id as a parameter
3589 -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3590 -- assignment
3591 ---------------------------------------------------------------------------
3592
3593 cursor position_seg
3594 ( c_person_id per_all_people_f.person_id%type,
3595 c_assignment_id per_all_assignments_f.assignment_id%type,
3596 c_basis_start date,
3597 c_basis_end date,
3598 c_legal_entity_id hr_organization_units.organization_id%type,
3599 c_er_position_seg_type hr_organization_information.org_information18
3600 %type) is
3601
3602 select decode(fifs.application_column_name, 'SEGMENT1', ppd.segment1,
3603 'SEGMENT2', ppd.segment2,
3604 'SEGMENT3', ppd.segment3,
3605 'SEGMENT4', ppd.segment4,
3606 'SEGMENT5', ppd.segment5,
3607 'SEGMENT6', ppd.segment6,
3608 'SEGMENT7', ppd.segment7,
3609 'SEGMENT8', ppd.segment8,
3610 'SEGMENT9', ppd.segment9,
3611 'SEGMENT10',ppd.segment10,
3612 'SEGMENT11',ppd.segment11,
3613 'SEGMENT12',ppd.segment12,
3614 'SEGMENT13',ppd.segment13,
3615 'SEGMENT14',ppd.segment14,
3616 'SEGMENT15',ppd.segment15,
3617 'SEGMENT16',ppd.segment16,
3618 'SEGMENT17',ppd.segment17,
3619 'SEGMENT18',ppd.segment18,
3620 'SEGMENT19',ppd.segment19,
3621 'SEGMENT20',ppd.segment20,
3622 'SEGMENT21',ppd.segment21,
3623 'SEGMENT22',ppd.segment22,
3624 'SEGMENT23',ppd.segment23,
3625 'SEGMENT24',ppd.segment24,
3626 'SEGMENT25',ppd.segment25,
3627 'SEGMENT26',ppd.segment26,
3628 'SEGMENT27',ppd.segment27,
3629 'SEGMENt28',ppd.segment28,
3630 'SEGMENT29',ppd.segment29,
3631 'SEGMENT30',ppd.segment30)
3632 from per_assignments_f assign,
3633 hr_soft_coding_keyflex hsc,
3634 hr_all_positions_f pos,
3635 hr_all_positions_f_tl pst,
3636 per_position_definitions ppd,
3637 hr_organization_units hou,
3638 hr_organization_information hoi,
3639 fnd_id_flex_segments fifs,
3640 fnd_id_flex_structures fift
3641 where assign.person_id = c_person_id
3642 and assign.assignment_id = c_assignment_id
3643 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3644 and hsc.segment1 = c_legal_entity_id
3645 and assign.position_id = pos.position_id
3646 and pst.position_id = pos.position_id
3647 and pst.language = userenv('LANG')
3648 and (assign.effective_start_date <= c_basis_end
3649 and assign.effective_end_date >= c_basis_start)
3650 and (pos.effective_start_date <= c_basis_end
3651 and pos.effective_end_date >= c_basis_start)
3652 and assign.assignment_type = 'E'
3653 and assign.business_group_id = hou.business_group_id
3654 and hsc.segment1 = hou.organization_id
3655 and hou.business_group_id = hoi.organization_id
3656 and hoi.org_information_context = 'Business Group Information'
3657 and hoi.org_information10 = 'SGD'
3658 and hoi.org_information8 = fift.id_flex_num
3659 and fifs.id_flex_num = fift.id_flex_num
3660 and fifs.application_id = '800'
3661 and fifs.application_id = fift.application_id
3662 and fifs.id_flex_code = 'POS'
3663 and fifs.id_flex_code = fift.id_flex_code
3664 and fifs.segment_name = c_er_position_seg_type
3665 and pos.position_definition_id = ppd.position_definition_id
3666 and assign.effective_start_date =
3667 (select max(paf1.effective_start_date)
3668 from per_assignments_f paf1
3669 where paf1.person_id = assign.person_id
3670 and paf1.assignment_id = assign.assignment_id
3671 and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3672 and paf1.effective_start_date <= c_basis_end
3673 and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
3674
3675 ---------------------------------------------------------------------------
3676 -- Bug 4688761 - Modified cursor to check the legal entity, and need get the
3677 -- latest primary assignment
3678 -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3679 -- we pass the assignment_id as a parameter
3680 ---------------------------------------------------------------------------
3681
3682 cursor other
3683 ( c_person_id per_all_people_f.person_id%type,
3684 c_assignment_id per_all_assignments_f.assignment_id%type,
3685 c_legal_entity_id hr_organization_units.organization_id%type,
3686 c_basis_start date,
3687 c_basis_end date) is
3688
3689 select /*+ USE_NL(aei) */
3690 aei.aei_information1
3691 from per_assignments_f assign,
3692 per_assignment_extra_info aei,
3693 hr_soft_coding_keyflex hsc
3694 where assign.person_id = c_person_id
3695 and assign.assignment_id = c_assignment_id
3696 and assign.assignment_id = aei.assignment_id
3697 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3698 and hsc.segment1 = c_legal_entity_id
3699 and aei.information_type = 'HR_JOB_DESIGNATION_SG'
3700 and aei.aei_information1 is not NULL
3701 and assign.assignment_type = 'E';
3702 --
3703 begin
3704 if g_debug then
3705 hr_utility.set_location('pysgirar: Start of archive_job_designation',10);
3706 end if;
3707 ------------------------------------------------------------------------
3708 -- Check selected Job Designation Type
3709 -- Bug 4688761, added p_legal_eneity_id
3710 ------------------------------------------------------------------------
3711 if p_er_designation_type = 'G' then
3712 open grade (p_person_id
3713 , p_assignment_id
3714 , p_legal_entity_id
3715 , p_basis_start
3716 , p_basis_end);
3717 fetch grade into v_designation;
3718 close grade;
3719
3720 elsif p_er_designation_type = 'J' then
3721 open job (p_person_id
3722 , p_assignment_id
3723 , p_legal_entity_id
3724 , p_basis_start
3725 , p_basis_end);
3726 fetch job into v_designation;
3727 close job;
3728
3729 elsif p_er_designation_type = 'P' then
3730 if p_er_position_seg_type is null then
3731 open position (p_person_id
3732 , p_assignment_id
3733 , p_legal_entity_id
3734 , p_basis_start
3735 , p_basis_end);
3736 fetch position into v_designation;
3737 close position;
3738 else
3739 open position_seg (p_person_id
3740 , p_assignment_id
3741 , p_basis_start
3742 , p_basis_end
3743 , p_legal_entity_id
3744 , p_er_position_seg_type);
3745 fetch position_seg into v_designation;
3746 close position_seg;
3747 end if;
3748
3749 elsif p_er_designation_type = 'O' then
3750 open other (p_person_id
3751 , p_assignment_id
3752 , p_legal_entity_id
3753 , p_basis_start
3754 , p_basis_end);
3755 fetch other into v_designation;
3756 close other;
3757 end if;
3758
3759 archive_item ('X_ASG_DESIGNATION', p_assignment_action_id, v_designation);
3760 --
3761 if g_debug then
3762 hr_utility.set_location('pysgirar: End of archive_job_designation',10);
3763 end if;
3764 end archive_job_designation;
3765 ---------------------------------------------------------------------------
3766 -- Selects information for Overseas Assignments, which is indicated by
3767 -- having the CPF Overseas Post Obligatory Indicator entered for an
3768 -- assignment whose duration is within Basis Year.
3769 ---------------------------------------------------------------------------
3770 procedure archive_os_assignment
3771 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3772 p_person_id in per_all_people_f.person_id%type,
3773 p_legal_entity_id in hr_organization_units.organization_id%type,
3774 p_basis_start in date,
3775 p_basis_end in date )
3776 is
3777 --
3778 v_cpf_overseas_post_obligatory per_assignment_extra_info.aei_information1%type;
3779 v_start_date varchar2(30);
3780 v_end_date varchar2(30);
3781 ---------------------------------------------------------------------------
3782 -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3783 -- Bug# 3257843 -Modified the cursor now it selects minimum effective start date
3784 -- and maximum effective end date of the assignment
3785 -- previously it was selecting last assignemnt of the basis year.
3786 --
3787 -- Bug# 4688761 - Modified cursor to check the legal entity
3788 ---------------------------------------------------------------------------
3789 cursor os_assignment
3790 ( c_person_id per_all_people_f.person_id%type,
3791 c_legal_entity_id hr_organization_units.organization_id%type,
3792 c_basis_start date,
3793 c_basis_end date )
3794 is
3795 select aei.aei_information1,
3796 min(fnd_date.date_to_canonical(assign.effective_start_date)),
3797 max(fnd_date.date_to_canonical(nvl(assign.effective_end_date,c_basis_end)))
3798 from per_assignments_f assign,
3799 per_assignment_extra_info aei,
3800 hr_soft_coding_keyflex hsc
3801 where assign.person_id = c_person_id
3802 and assign.assignment_id = aei.assignment_id
3803 and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3804 and hsc.segment1 = c_legal_entity_id
3805 and aei.information_type = 'HR_IR8S_INDICATORS_SG'
3806 and aei.aei_information1 is not NULL -- CPF overseas post obligatory flag, bug 2261267, 2373475
3807 and assign.effective_start_date <= c_basis_end
3808 and assign.effective_end_date >= c_basis_start
3809 and assign.assignment_type = 'E' /* Bug 5033609 */
3810 group by aei.aei_information1;
3811 --
3812 begin
3813 if g_debug then
3814 hr_utility.set_location('pysgirar: Start of archive_os_assignment',10);
3815 end if;
3816 --
3817 open os_assignment (p_person_id, p_legal_entity_id, p_basis_start, p_basis_end);
3818 fetch os_assignment into v_cpf_overseas_post_obligatory,
3819 v_start_date, v_end_date;
3820 --
3821 if os_assignment%found then
3822 archive_item ('X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY',
3823 p_assignment_action_id, v_cpf_overseas_post_obligatory);
3824 archive_item ('X_ASG_OVERSEAS_DATE_FROM', p_assignment_action_id, v_start_date);
3825 archive_item ('X_ASG_OVERSEAS_DATE_TO', p_assignment_action_id, v_end_date);
3826 end if;
3827 --
3828 close os_assignment;
3829 if g_debug then
3830 hr_utility.set_location('pysgirar: End of archive_os_assignment',20);
3831 end if;
3832 end archive_os_assignment;
3833
3834 ---------------------------------------------------------------------------
3835 -- Selects information for Shares information, which is entered via assignment
3836 -- extra information screen, bug 2475287
3837 ---------------------------------------------------------------------------
3838 procedure archive_shares_details
3839 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3840 p_person_id in per_all_people_f.person_id%type,
3841 p_tax_unit_id in ff_archive_item_contexts.context%type,
3842 p_basis_start in date,
3843 p_basis_end in date )
3844 is
3845 --
3846 v_moa_305 number;
3847 v_moa_319 number;
3848 v_moa_339 number;
3849 v_moa_601 number;
3850 v_moa_352 number;
3851 v_moa_355 number;
3852 v_moa_358 number;
3853 v_moa_602 number;
3854 v_grant_type_error char(1);
3855
3856 ---------------------------------------------------------------------------
3857 -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3858 -- bug 2691877
3859 -- bug 3501956 - Changed cursor to select information from per_people_extra_info table
3860 -- Bug 4314453 - Modified the cursor to use the table instead of view
3861 -- Bug 5435088 - Added grant type
3862 ---------------------------------------------------------------------------
3863 cursor shares_details
3864 ( c_person_id per_all_people_f.person_id%type,
3865 c_basis_start date,
3866 c_basis_end date )
3867 is
3868 select distinct pei.person_extra_info_id,
3869 pei.pei_information1 stock_option,
3870 pei.pei_information3 exercise_price,
3871 pei.pei_information4 market_exercise_value,
3872 to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
3873 to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
3874 pei.pei_information6 shares_acquired,
3875 hoi1.org_information1 name_of_company,
3876 hoi1.org_information4 RCB,
3877 hoi1.org_information15 company_type,
3878 pei2.pei_information2 market_grant_value,
3879 pei2.pei_information5 grant_type,
3880 decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
3881 to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
3882 pei2.pei_information4 shares_granted
3883 from per_all_people_f pap,
3884 per_people_extra_info pei,
3885 per_people_extra_info pei2,
3886 hr_all_organization_units hou,
3887 hr_organization_information hoi2,
3888 hr_organization_information hoi1
3889 where pap.person_id = c_person_id
3890 and pap.person_id = pei.person_id
3891 and pei.information_type = 'HR_STOCK_EXERCISE_SG'
3892 and pap.person_id = pei2.person_id
3893 and pei.pei_information2 = pei2.person_extra_info_id
3894 and pei2.information_type = 'HR_STOCK_GRANT_SG'
3895 and pei2.pei_information1 = hou.organization_id
3896 and hou.organization_id = hoi1.organization_id(+)
3897 and hou.organization_id = hoi2.organization_id
3898 and hoi1.org_information_context||'' = 'SG_LEGAL_ENTITY'
3899 and hoi2.org_information_context||'' = 'CLASS'
3900 and hoi2.org_information1 = 'HR_LEGAL'
3901 and hoi2.org_information2 = 'Y'
3902 and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */
3903 and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
3904 --
3905 begin
3906 v_moa_305 := 0;
3907 v_moa_319 := 0;
3908 v_moa_339 := 0;
3909 v_moa_601 := 0;
3910 v_moa_352 := 0;
3911 v_moa_355 := 0;
3912 v_moa_358 := 0;
3913 v_moa_602 := 0;
3914 v_grant_type_error := 'N';
3915 if g_debug then
3916 hr_utility.set_location('pysgirar: Start of archive_shares_details', 10);
3917 end if;
3918 --
3919 -- modifed for bug 5435088
3920 for share_rec in shares_details (p_person_id, p_basis_start, p_basis_end)
3921 loop
3922 archive_item_3('X_A8B_COMPANY', p_assignment_action_id, share_rec.name_of_company, p_tax_unit_id, share_rec.person_extra_info_id);
3923 archive_item_3('X_A8B_RCB', p_assignment_action_id, share_rec.RCB, p_tax_unit_id, share_rec.person_extra_info_id);
3924 archive_item_3('X_A8B_COMPANY_TYPE', p_assignment_action_id, share_rec.company_type, p_tax_unit_id, share_rec.person_extra_info_id);
3925 archive_item_3('X_A8B_OPTION', p_assignment_action_id, share_rec.stock_option, p_tax_unit_id, share_rec.person_extra_info_id);
3926 archive_item_3('X_A8B_MK_EXER_VALUE', p_assignment_action_id, share_rec.market_exercise_value, p_tax_unit_id, share_rec.person_extra_info_id);
3927 archive_item_3('X_A8B_MK_GRANT_VALUE', p_assignment_action_id, share_rec.market_grant_value, p_tax_unit_id, share_rec.person_extra_info_id);
3928 archive_item_3('X_A8B_SHARES_ACQUIRED', p_assignment_action_id, share_rec.shares_acquired, p_tax_unit_id, share_rec.person_extra_info_id);
3929 archive_item_3('X_A8B_EXER_PRICE', p_assignment_action_id, share_rec.exercise_price, p_tax_unit_id, share_rec.person_extra_info_id);
3930 archive_item_3('X_A8B_EXERCISED_DATE', p_assignment_action_id, share_rec.exercise_date, p_tax_unit_id, share_rec.person_extra_info_id);
3931 archive_item_3('X_A8B_GRANTED_DATE', p_assignment_action_id, share_rec.grant_date, p_tax_unit_id, share_rec.person_extra_info_id);
3932 -- Added for bug 5435088
3933 archive_item_3('X_A8B_GRANT_TYPE', p_assignment_action_id, share_rec.grant_type, p_tax_unit_id, share_rec.person_extra_info_id);
3934 --
3935 if share_rec.grant_type is null then
3936 v_grant_type_error := 'Y';
3937 end if;
3938
3939 if share_rec.grant_type = 'P' and to_date(share_rec.grant_date, 'YYYY/MM/DD') < to_date('2002/12/31','YYYY/MM/DD') then
3940
3941 if share_rec.stock_option = 'E' then
3942 v_moa_305 := (share_rec.market_exercise_value - share_rec.exercise_price) * share_rec.shares_acquired + v_moa_305; /* Bug 3204837 */
3943 elsif share_rec.stock_option = 'EE' then
3944 v_moa_319 := (share_rec.market_exercise_value - share_rec.exercise_price) * share_rec.shares_acquired + v_moa_319;
3945 elsif share_rec.stock_option = 'C' then
3946 v_moa_339 := (share_rec.market_exercise_value - share_rec.exercise_price) * share_rec.shares_acquired + v_moa_339;
3947 end if;
3948 else
3949 if share_rec.stock_option = 'E' then
3950 v_moa_352 := (share_rec.market_exercise_value - share_rec.exercise_price) * share_rec.shares_acquired + v_moa_352; /* Bug 3204837 */
3951 elsif share_rec.stock_option = 'EE' then
3952 v_moa_355 := (share_rec.market_exercise_value - share_rec.exercise_price) * share_rec.shares_acquired + v_moa_355;
3953 elsif share_rec.stock_option = 'C' then
3954 v_moa_358 := (share_rec.market_exercise_value - share_rec.exercise_price) * share_rec.shares_acquired + v_moa_358;
3955 end if;
3956 end if;
3957 end loop;
3958 --
3959 v_moa_601 := v_moa_305 + v_moa_319 + v_moa_339;
3960 v_moa_602 := v_moa_352 + v_moa_355 + v_moa_358;
3961 archive_item_2 ('X_A8B_MOA_601', p_assignment_action_id, v_moa_601, p_tax_unit_id);
3962 archive_item_2 ('X_A8B_MOA_602', p_assignment_action_id, v_moa_602, p_tax_unit_id);
3963 archive_item ('X_PER_GRANT_TYPE_ERROR', p_assignment_action_id, v_grant_type_error);
3964 --
3965
3966 if g_debug then
3967 hr_utility.set_location('pysgirar: End of archive_share_details', 100);
3968 end if;
3969 end archive_shares_details;
3970
3971 ---------------------------------------------------------------------------
3972 -- Selects information for IR8S C claimed/to be claimed details information,
3973 -- which is entered via assignment extra information screen, bug 3027801
3974 ---------------------------------------------------------------------------
3975 procedure archive_ir8s_c_details
3976 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3977 p_person_id in per_all_people_f.person_id%type,
3978 p_tax_unit_id in ff_archive_item_contexts.context%type,
3979 p_business_group_id in per_assignments_f.business_group_id%type,
3980 p_basis_start in date,
3981 p_basis_end in date) is
3982
3983 /* Type to store the person ids with same national_identifier */
3984
3985 type person_id_store_rec is record
3986 (person_id per_all_people_f.person_id%type);
3987
3988 type person_id_tab is table of person_id_store_rec index by binary_integer;
3989 person_id_rec person_id_tab;
3990
3991 cursor ir8s_c_invalid_records
3992 (c_person_id per_all_people_f.person_id%type,
3993 c_tax_unit_id ff_archive_item_contexts.context%type,
3994 c_business_group_id per_assignments_f.business_group_id%type,
3995 c_basis_start date,
3996 c_basis_end date) is
3997
3998 select count(distinct (paei.assignment_extra_info_id))
3999 from per_assignment_extra_info paei,
4000 per_assignments_f paa,
4001 hr_soft_coding_keyflex hsc
4002 where paa.person_id = c_person_id
4003 and paa.assignment_id = paei.assignment_id
4004 and paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4005 and hsc.segment1 = c_tax_unit_id
4006 and paa.business_group_id = c_business_group_id
4007 and paa.assignment_type = 'E' /* Bug 5033609 */
4008 and paei.information_type = 'HR_CPF_CLAIMED_SG'
4009 and paei.aei_information1 = to_char(c_basis_end,'YYYY')
4010 and (paa.effective_start_date <= c_basis_end
4011 and paa.effective_end_date >= c_basis_start);
4012
4013 l_person_id per_all_people_f.person_id%type;
4014 l_temp_person_id per_all_people_f.person_id%type;
4015 l_archive_person_id per_all_people_f.person_id%type;
4016 counter number;
4017 l_counter number;
4018 duplicate_exists varchar2(1);
4019 l_total_counts number;
4020 l_ir8s_c_counts number;
4021
4022 begin
4023 l_temp_person_id := NULL;
4024 l_counter := 1;
4025 duplicate_exists := 'N';
4026 l_total_counts := 0;
4027 --
4028 if g_debug then
4029 hr_utility.set_location('pysgirar: Start of archive_ir8s_c_details', 10);
4030 end if;
4031 ----------------------------------------------------------------------------------
4032 -- Added for bug 3162319
4033 -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
4034 -- in employee_if_latest( ) function
4035 ----------------------------------------------------------------------------------
4036 if g_person_id_tab.count > 1 then
4037 for l_person_id in g_person_id_tab.first..g_person_id_tab.last
4038 loop
4039 person_id_rec(l_counter).person_id := g_person_id_tab(l_person_id);
4040 l_counter := l_counter+1;
4041 end loop;
4042 --
4043 duplicate_exists :='Y';
4044 end if;
4045 --
4046 if duplicate_exists = 'N' then
4047 person_id_rec(l_counter).person_id := p_person_id;
4048 end if;
4049 --
4050 if person_id_rec.count>0 then
4051 l_total_counts := 0;
4052 for l_person_counter in 1..person_id_rec.last
4053 loop
4054 if person_id_rec.exists(l_person_counter) then
4055 l_archive_person_id := person_id_rec(1).person_id;
4056 --
4057 open ir8s_c_invalid_records (
4058 person_id_rec(l_person_counter).person_id,
4059 p_tax_unit_id,
4060 p_business_group_id,
4061 p_basis_start,
4062 p_basis_end);
4063 fetch ir8s_c_invalid_records into l_ir8s_c_counts;
4064
4065 if ir8s_c_invalid_records%found then
4066 l_total_counts := l_total_counts + l_ir8s_c_counts;
4067 end if;
4068 --
4069 close ir8s_c_invalid_records;
4070 archive_ir8s_c_detail_moas(p_assignment_action_id
4071 ,person_id_rec(1).person_id
4072 ,person_id_rec(l_person_counter).person_id
4073 ,p_tax_unit_id
4074 ,p_business_group_id
4075 ,p_basis_start
4076 ,p_basis_end);
4077 end if;
4078 end loop;
4079
4080 if l_total_counts = 0 then
4081 archive_item_3('X_MOA410', p_assignment_action_id, 0, p_tax_unit_id, 0);
4082 archive_item('X_IR8S_TOTAL_MOA410', p_assignment_action_id, 0);
4083 end if;
4084
4085 if l_total_counts >3 then
4086 archive_item ('X_IR8S_C_INVALID_RECORDS',
4087 p_assignment_action_id, 'N');
4088 else
4089 archive_item ('X_IR8S_C_INVALID_RECORDS',
4090 p_assignment_action_id, 'Y');
4091 end if;
4092
4093 end if;
4094 --
4095 if g_debug then
4096 hr_utility.set_location('pysgirar: End of archive_ir8s_c_details', 100);
4097 end if;
4098 end archive_ir8s_c_details;
4099
4100
4101 procedure archive_ir8s_c_detail_moas
4102 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4103 p_1_person_id in per_all_people_f.person_id%type,
4104 p_person_id in per_all_people_f.person_id%type,
4105 p_tax_unit_id in ff_archive_item_contexts.context%type,
4106 p_business_group_id in per_assignments_f.business_group_id%type,
4107 p_basis_start in date,
4108 p_basis_end in date) is
4109
4110 cursor ir8s_c_details
4111 (c_person_id per_assignments_f.person_id%type,
4112 c_tax_unit_id ff_archive_item_contexts.context%type,
4113 c_business_group_id per_assignments_f.business_group_id%type,
4114 c_basis_start date,
4115 c_basis_end date) is
4116
4117 select distinct aei.assignment_extra_info_id,
4118 aei.aei_information2 add_wages,
4119 aei.aei_information3 add_wages_from_date,
4120 aei.aei_information4 add_wages_to_date,
4121 aei.aei_information5 pay_date_add_wages,
4122 aei.aei_information6 er_cpf,
4123 aei.aei_information7 er_cpf_interest,
4124 aei.aei_information8 er_cpf_date,
4125 aei.aei_information9 ee_cpf,
4126 aei.aei_information10 ee_cpf_interest,
4127 aei.aei_information11 ee_cpf_date
4128 from per_assignments_f ass,
4129 per_assignment_extra_info aei,
4130 hr_soft_coding_keyflex hsc
4131 where ass.person_id = c_person_id
4132 and ass.assignment_id = aei.assignment_id
4133 and ass.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4134 and hsc.segment1 = c_tax_unit_id
4135 and ass.business_group_id = c_business_group_id
4136 and ass.assignment_type = 'E' /* Bug 5033609 */
4137 and aei.information_type = 'HR_CPF_CLAIMED_SG'
4138 and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4139 and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4140 and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4141 and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4142 and (ass.effective_start_date <= c_basis_end
4143 and ass.effective_end_date >= c_basis_start);
4144 /* Bug 6020961, removed date in year check for er_cpf_date and ee_cpf_date */
4145
4146
4147 v_ir8s_total_moa410 number;
4148
4149 begin
4150 v_ir8s_total_moa410 := 0;
4151 if g_debug then
4152 hr_utility.set_location('pysgirar: Start of archive_ir8s_c_detail_moas', 10);
4153 end if;
4154 --
4155 for ir8s_c_rec in ir8s_c_details (p_person_id
4156 , p_tax_unit_id
4157 , p_business_group_id
4158 , p_basis_start
4159 , p_basis_end)
4160 loop
4161
4162 archive_item_3('X_MOA410', p_assignment_action_id, ir8s_c_rec.add_wages,
4163 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4164 archive_item_3('X_DTM502', p_assignment_action_id,
4165 ir8s_c_rec.add_wages_from_date,
4166 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4167 archive_item_3('X_DTM503', p_assignment_action_id,
4168 ir8s_c_rec.add_wages_to_date,
4169 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4170 archive_item_3('X_DTM504', p_assignment_action_id,
4171 ir8s_c_rec.pay_date_add_wages,
4172 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4173 archive_item_3('X_MOA411', p_assignment_action_id, ir8s_c_rec.er_cpf,
4174 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4175 archive_item_3('X_MOA412', p_assignment_action_id,
4176 ir8s_c_rec.er_cpf_interest,
4177 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4178 archive_item_3('X_DTM505', p_assignment_action_id,
4179 ir8s_c_rec.er_cpf_date,
4180 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4181 archive_item_3('X_MOA413', p_assignment_action_id, ir8s_c_rec.ee_cpf,
4182 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4183 archive_item_3('X_MOA414', p_assignment_action_id,
4184 ir8s_c_rec.ee_cpf_interest,
4185 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4186 archive_item_3('X_DTM506', p_assignment_action_id,
4187 ir8s_c_rec.ee_cpf_date,
4188 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
4189
4190 v_ir8s_total_moa410 := ir8s_c_rec.add_wages;
4191
4192 end loop;
4193
4194 archive_item('X_IR8S_TOTAL_MOA410', p_assignment_action_id, v_ir8s_total_moa410);
4195 --
4196 if g_debug then
4197 hr_utility.set_location('pysgirar: End of archive_ir8s_c_detail_moas', 100);
4198 end if;
4199 end archive_ir8s_c_detail_moas;
4200 ---------------------------------------------------------------------------
4201 -- Calls the archive utility to actually perform the archive of the item.
4202 ---------------------------------------------------------------------------
4203 procedure archive_item
4204 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
4205 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4206 p_archive_value in ff_archive_items.value%type )
4207 is
4208 --
4209 v_user_entity_id ff_user_entities.user_entity_id%type;
4210 v_archive_item_id ff_archive_items.archive_item_id%type;
4211 v_object_version_number ff_archive_items.object_version_number%type;
4212 v_some_warning boolean;
4213 ---------------------------------------------------------------------------
4214 -- Cursor User_Entity_ID
4215 ---------------------------------------------------------------------------
4216 cursor user_entity_id
4217 ( c_user_entity_name ff_user_entities.user_entity_name%type )
4218 is
4219 select user_entity_id
4220 from ff_user_entities
4221 where user_entity_name = c_user_entity_name;
4222 --
4223 begin
4224 if g_debug then
4225 hr_utility.set_location('Start of archive_item',10);
4226 end if;
4227 --
4228 open user_entity_id (p_user_entity_name);
4229 fetch user_entity_id into v_user_entity_id;
4230 close user_entity_id;
4231 --
4232 ff_archive_api.create_archive_item
4233 ( p_validate => false
4234 ,p_archive_item_id => v_archive_item_id
4235 ,p_user_entity_id => v_user_entity_id
4236 ,p_archive_value => p_archive_value
4237 ,p_archive_type => 'AAP'
4238 ,p_action_id => p_assignment_action_id
4239 ,p_legislation_code => 'SG'
4240 ,p_object_version_number => v_object_version_number
4241 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
4242 ,p_context1 => p_assignment_action_id
4243 ,p_some_warning => v_some_warning);
4244 --
4245 if g_debug then
4246 hr_utility.set_location('End of archive_item',20);
4247 end if;
4248 end archive_item;
4249
4250 -----------------------------------------------------------------------------
4251 -- Calls the archive utility to actually perform the archive of the item with
4252 -- one another context
4253 -----------------------------------------------------------------------------
4254 procedure archive_item_2
4255 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
4256 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4257 p_archive_value in ff_archive_items.value%type,
4258 p_context_value2 in ff_archive_item_contexts.context%type )
4259 is
4260 --
4261 v_user_entity_id ff_user_entities.user_entity_id%type;
4262 v_archive_item_id ff_archive_items.archive_item_id%type;
4263 v_object_version_number ff_archive_items.object_version_number%type;
4264 v_some_warning boolean;
4265 ---------------------------------------------------------------------------
4266 -- Cursor User_Entity_ID
4267 ---------------------------------------------------------------------------
4268 cursor user_entity_id
4269 ( c_user_entity_name ff_user_entities.user_entity_name%type )
4270 is
4271 select user_entity_id
4272 from ff_user_entities
4273 where user_entity_name = c_user_entity_name;
4274 --
4275 begin
4276 if g_debug then
4277 hr_utility.set_location('Start of archive_item_2',10);
4278 end if;
4279 --
4280 open user_entity_id (p_user_entity_name);
4281 fetch user_entity_id into v_user_entity_id;
4282 close user_entity_id;
4283 --
4284 ff_archive_api.create_archive_item
4285 ( p_validate => false
4286 ,p_archive_item_id => v_archive_item_id
4287 ,p_user_entity_id => v_user_entity_id
4288 ,p_archive_value => p_archive_value
4289 ,p_archive_type => 'AAP'
4290 ,p_action_id => p_assignment_action_id
4291 ,p_legislation_code => 'SG'
4292 ,p_object_version_number => v_object_version_number
4293 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
4294 ,p_context1 => p_assignment_action_id
4295 ,p_context_name2 => 'ORGANIZATION_ID'
4296 ,p_context2 => p_context_value2
4297 ,p_some_warning => v_some_warning);
4298 --
4299 if g_debug then
4300 hr_utility.set_location('End of archive_item_2',20);
4301 end if;
4302 end archive_item_2;
4303
4304 -----------------------------------------------------------------------------
4305 -- Calls the archive utility to actually perform the archive of the item with
4306 -- one another context
4307 -----------------------------------------------------------------------------
4308 procedure archive_item_3
4309 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
4310 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4311 p_archive_value in ff_archive_items.value%type,
4312 p_context_value2 in ff_archive_item_contexts.context%type,
4313 p_context_value3 in ff_archive_item_contexts.context%type )
4314 is
4315 --
4316 v_user_entity_id ff_user_entities.user_entity_id%type;
4317 v_archive_item_id ff_archive_items.archive_item_id%type;
4318 v_object_version_number ff_archive_items.object_version_number%type;
4319 v_some_warning boolean;
4320 ---------------------------------------------------------------------------
4321 -- Cursor User_Entity_ID
4322 ---------------------------------------------------------------------------
4323 cursor user_entity_id
4324 ( c_user_entity_name ff_user_entities.user_entity_name%type )
4325 is
4326 select user_entity_id
4327 from ff_user_entities
4328 where user_entity_name = c_user_entity_name;
4329 --
4330 begin
4331 if g_debug then
4332 hr_utility.set_location('Start of archive_item_3',10);
4333 end if;
4334 --
4335 open user_entity_id (p_user_entity_name);
4336 fetch user_entity_id into v_user_entity_id;
4337 close user_entity_id;
4338 --
4339 ff_archive_api.create_archive_item
4340 ( p_validate => false
4341 ,p_archive_item_id => v_archive_item_id
4342 ,p_user_entity_id => v_user_entity_id
4343 ,p_archive_value => p_archive_value
4344 ,p_archive_type => 'AAP'
4345 ,p_action_id => p_assignment_action_id
4346 ,p_legislation_code => 'SG'
4347 ,p_object_version_number => v_object_version_number
4348 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
4349 ,p_context1 => p_assignment_action_id
4350 ,p_context_name2 => 'TAX_UNIT_ID'
4351 ,p_context2 => p_context_value2
4352 ,p_context_name3 => 'SOURCE_ID'
4353 ,p_context3 => p_context_value3
4354 ,p_some_warning => v_some_warning );
4355 --
4356 if g_debug then
4357 hr_utility.set_location('End of archive_item_3',20);
4358 end if;
4359 end archive_item_3;
4360 --------------------------------------------------------------------------------
4361 -- Bug 3118540 -
4362 -- Bug 3435334 - This function removes setup action when ran for IRAS Line Archive /
4363 -- initiates SRS 'IR8S Ad Hoc Printed Archive' when ran for IR8S adhoc archive
4364 --------------------------------------------------------------------------------
4365 procedure deinit_code ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
4366 is
4367 l_report_type varchar2(20);
4368 l_rep_req_id number;
4369 v_setup_action pay_payroll_actions.payroll_action_id%type;
4370 begin
4371 l_rep_req_id := 0;
4372 v_setup_action := 0;
4373 if g_debug then
4374 hr_utility.set_location('pysgirar: Start of deinit_code',10);
4375 end if;
4376 --
4377 select report_type
4378 into l_report_type
4379 from pay_payroll_actions ppa
4380 where ppa.payroll_action_id = p_payroll_action_id ;
4381 --
4382 if l_report_type = 'SG_IR8S_ADHOC_REPORT' then
4383 l_rep_req_id := FND_REQUEST.SUBMIT_REQUEST (
4384 application => 'PAY',
4385 program => 'PYSG8SAD',
4386 argument1 => 'P_ASSIGNMENT_SET_ID=' || g_assignment_set_id,
4387 argument2 => 'P_BASIS_YEAR=' || g_basis_year,
4388 argument3 => 'P_BUSINESS_GROUP_ID='|| g_business_group_id,
4389 argument4 => 'P_LEGAL_ENTITY=' || g_legal_entity_id,
4390 argument5 => 'P_PAYROLL_ACTION_ID=' || p_payroll_action_id,
4391 argument6 => 'P_PERSON_ID=' || g_person_id,
4392 argument7 => 'P_BASIS_START=' || g_basis_start,
4393 argument8 => 'P_BASIS_END=' || g_basis_end );
4394 elsif l_report_type = 'SG_IRAS_ARCHIVE' then
4395 select pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters)
4396 into v_setup_action
4397 from pay_payroll_actions
4398 where payroll_action_id = p_payroll_action_id ;
4399 -------------------------------------------------------
4400 -- Bug: 3910804 Delete data from pay_action_information
4401 -------------------------------------------------------
4402 delete from pay_action_information
4403 where action_context_id = v_setup_action
4404 and action_context_type = 'AAP'
4405 and action_information_category = 'SG_IRAS_SETUP';
4406
4407 py_rollback_pkg.rollback_payroll_action( v_setup_action );
4408
4409 --
4410 end if;
4411 exception
4412 when others then
4413 if g_debug then
4414 hr_utility.set_location('pysgirar: End of deinit_code',10);
4415 end if;
4416 raise;
4417 end deinit_code;
4418 ----------------------------------------------------------------------
4419 -- Bug 3435334 This function returns TRUE if no duplicate exist in
4420 -- system Or if current employee is latest in case duplicates exist in the system
4421 -- For second case it also populates global table with all its previous employement records
4422 ----------------------------------------------------------------------
4423 function employee_if_latest ( p_national_identifier in varchar2,
4424 p_person_id in per_all_people_f.person_id%type,
4425 p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
4426 p_report_type in varchar2 ) return boolean
4427 is
4428 type t_person_start_date_tab is table of per_all_people_f.start_date%type;
4429 g_person_start_date_tab t_person_start_date_tab;
4430 begin
4431 g_person_id_tab.delete;
4432 --
4433 if p_national_identifier is not null and p_report_type <> 'SG_IR8S_ADHOC_REPORT' then
4434 begin
4435 select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)
4436 bulk collect into g_person_id_tab , g_person_start_date_tab
4437 from pay_action_information pai
4438 where pai.action_information1 = p_national_identifier
4439 and pai.action_context_id = p_setup_action_id
4440 and pai.action_context_type = 'AAP'
4441 and pai.action_information_category = 'SG_IRAS_SETUP'
4442 order by fnd_date.canonical_to_date(pai.action_information3) desc;
4443 end;
4444 --
4445 if g_person_id_tab.count > 1 then
4446 if g_person_id_tab(1) = p_person_id then
4447 return true;
4448 else
4449 return false;
4450 end if;
4451 else
4452 return true;
4453 end if;
4454 --
4455 else
4456 return true;
4457 end if;
4458 end employee_if_latest ;
4459
4460 -------------------------------------------------------------------------
4461 -- Bug 4688761, this function checks the same person_id has been archived
4462 -------------------------------------------------------------------------
4463
4464 function person_if_archived (p_person_id in per_all_people_f.person_id%type) return boolean
4465 is
4466 l_archived_person_id binary_integer;
4467 begin
4468 if g_debug then
4469 hr_utility.set_location('pysgirar: Start of person_if_archived',10);
4470 end if;
4471
4472 l_archived_person_id := p_person_id;
4473 if t_archived_person.exists(l_archived_person_id) then
4474 if (t_archived_person(l_archived_person_id).person_id = p_person_id) then
4475 if g_debug then
4476 hr_utility.set_location('End of person_if_archived',20);
4477 end if;
4478 return true;
4479 end if;
4480 end if;
4481 if g_debug then
4482 hr_utility.set_location('End of person_if_archived',20);
4483 end if;
4484 return false;
4485 end person_if_archived;
4486
4487 -------------------------------------------------------------------------
4488 -- Bug 4890964, this function checks the parameter LE if its in the latest
4489 -- primary assignment, it needs for share details.
4490 -------------------------------------------------------------------------
4491
4492 function pri_if_latest
4493 ( p_person_id in per_all_people_f.person_id%type
4494 , p_tax_unit_id in ff_archive_item_contexts.context%type
4495 , p_basis_start in date
4496 , p_basis_end in date) return boolean
4497 is
4498 v_dummy varchar2(1);
4499 cursor pri_latest
4500 ( c_person_id per_all_people_f.person_id%type,
4501 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
4502 c_basis_start date,
4503 c_basis_end date )
4504 is
4505 select 'X'
4506 from per_assignments_f paf,
4507 hr_soft_coding_keyflex hsc
4508 where paf.person_id = c_person_id
4509 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4510 and hsc.segment1 = c_tax_unit_id
4511 and paf.primary_flag = 'Y'
4512 and paf.assignment_type = 'E' /* Bug 5033609 */
4513 and paf.effective_start_date =
4514 (select max(paf1.effective_start_date)
4515 from per_assignments_f paf1 /* Bug 5858566 */
4516 where paf1.person_id = paf.person_id
4517 and paf1.assignment_type = 'E' /* Bug 5033609 */
4518 and paf1.effective_start_date <= c_basis_end
4519 and paf1.effective_end_date >= c_basis_start
4520 and paf1.primary_flag = 'Y')
4521 and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
4522
4523 begin
4524 if g_debug then
4525 hr_utility.set_location('pysgirar: Start of pri_if_latest',10);
4526 end if;
4527
4528 open pri_latest (p_person_id,
4529 p_tax_unit_id,
4530 p_basis_start,
4531 p_basis_end);
4532 fetch pri_latest into v_dummy;
4533 --
4534 if pri_latest%found then
4535 close pri_latest;
4536 if g_debug then
4537 hr_utility.set_location('End of pri_if_latest',20);
4538 end if;
4539 return TRUE;
4540 end if;
4541 close pri_latest;
4542 if g_debug then
4543 hr_utility.set_location('End of pri_if_latest',20);
4544 end if;
4545 return FALSE;
4546
4547 end pri_if_latest;
4548
4549
4550 -------------------------------------------------------------------------
4551 -- Bug 4890964, with LE, this function gets the assignment with the latest
4552 -- effective_start_date with the primary defined
4553 -------------------------------------------------------------------------
4554
4555 function pri_LE_if_latest
4556 ( p_person_id in per_all_people_f.person_id%type
4557 , p_tax_unit_id in ff_archive_item_contexts.context%type
4558 , p_basis_start in date
4559 , p_basis_end in date) return number
4560 is
4561 v_assignment_id number(10);
4562 cursor pri_latest_LE
4563 ( c_person_id per_all_people_f.person_id%type,
4564 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
4565 c_basis_start date,
4566 c_basis_end date )
4567 is
4568 select paf.assignment_id
4569 from per_assignments_f paf,
4570 hr_soft_coding_keyflex hsc
4571 where paf.person_id = c_person_id
4572 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4573 and hsc.segment1 = c_tax_unit_id
4574 and paf.primary_flag = 'Y'
4575 and paf.assignment_type = 'E' /* Bug 5033609 */
4576 and paf.effective_start_date =
4577 (select max(paf1.effective_start_date)
4578 from per_assignments_f paf1 /* Bug 5858566 */
4579 where paf1.person_id = paf.person_id
4580 and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
4581 and paf1.assignment_type = 'E' /* Bug 5033609 */
4582 and paf1.effective_start_date <= c_basis_end
4583 and paf1.effective_end_date >= c_basis_start
4584 and paf1.primary_flag = 'Y')
4585 and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
4586
4587 begin
4588 if g_debug then
4589 hr_utility.set_location('pysgirar: Start of pri_LE_if_latest',10);
4590 end if;
4591
4592 open pri_latest_LE (p_person_id,
4593 p_tax_unit_id,
4594 p_basis_start,
4595 p_basis_end);
4596 fetch pri_latest_LE into v_assignment_id;
4597 --
4598 if pri_latest_LE%found then
4599 close pri_latest_LE;
4600 if g_debug then
4601 hr_utility.set_location('End of pri_LE_if_latest',20);
4602 end if;
4603 return v_assignment_id;
4604 end if;
4605 close pri_latest_LE;
4606 if g_debug then
4607 hr_utility.set_location('End of pri_LE_if_latest',20);
4608 end if;
4609 return null;
4610
4611 end pri_LE_if_latest;
4612
4613 -------------------------------------------------------------------------
4614 -- Bug 4890964, with LE, this function gets the assignment with the latest
4615 -- effective_start_date if it has no primary defined, and if it has multi
4616 -- same effective_start_date, it will get the max(assignment_id)
4617 -- Bug 6866170, if it has multiple LEs, each LE has multi assignment
4618 -- records, for example, job changes. Both latest assignment of different
4619 -- LE has the same effective_start_date. The issue is in the first
4620 -- assignment that is not a primary assignment, the cursor id_latest_LE did
4621 -- not return an assignment_id.
4622 -------------------------------------------------------------------------
4623
4624 function id_LE_if_latest
4625 ( p_person_id in per_all_people_f.person_id%type
4626 , p_tax_unit_id in ff_archive_item_contexts.context%type
4627 , p_basis_start in date
4628 , p_basis_end in date) return number
4629 is
4630 v_assignment_id number(10);
4631 cursor id_latest_LE
4632 ( c_person_id per_all_people_f.person_id%type,
4633 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
4634 c_basis_start date,
4635 c_basis_end date )
4636 is
4637 select max(paf.assignment_id)
4638 from per_assignments_f paf,
4639 hr_soft_coding_keyflex hsc
4640 where paf.person_id = c_person_id
4641 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4642 and hsc.segment1 = c_tax_unit_id
4643 and paf.assignment_type = 'E'
4644 and paf.effective_start_date = (
4645 select max(paf1.effective_start_date)
4646 from per_assignments_f paf1 /* Bug 5858566 */
4647 where paf1.person_id = paf.person_id
4648 and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
4649 and paf1.assignment_type = 'E' /*Bug5033609*/
4650 and paf1.effective_start_date <= c_basis_end
4651 and paf1.effective_end_date >= c_basis_start)
4652 and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
4653
4654 begin
4655 if g_debug then
4656 hr_utility.set_location('pysgirar: Start of id_LE_if_latest',10);
4657 end if;
4658
4659 open id_latest_LE ( p_person_id
4660 , p_tax_unit_id
4661 , p_basis_start
4662 , p_basis_end);
4663 fetch id_latest_LE into v_assignment_id;
4664 --
4665 if id_latest_LE%found then
4666 close id_latest_LE;
4667 if g_debug then
4668 hr_utility.set_location('End of id_LE_if_latest',20);
4669 end if;
4670 return v_assignment_id;
4671 end if;
4672 close id_latest_LE;
4673 if g_debug then
4674 hr_utility.set_location('End of id_LE_if_latest',20);
4675 end if;
4676 return null;
4677
4678 end id_LE_if_latest;
4679 --------------------------------------------------------------------------------
4680 -- Simply check IF a value is numeric, bug 5435088
4681 --------------------------------------------------------------------------------
4682 function check_is_number (p_value in varchar2) return boolean is
4683 l_number_value number;
4684 begin
4685 if g_debug then
4686 hr_utility.set_location('pysgirar: Start of check_is_number',10);
4687 end if;
4688
4689 if p_value is NULL then
4690 if g_debug then
4691 hr_utility.set_location('End of check_is_number',20);
4692 end if;
4693
4694 return TRUE;
4695 else
4696 begin
4697 l_number_value := to_number(p_value);
4698 exception
4699 when value_error then
4700 if g_debug then
4701 hr_utility.set_location('End of check_is_number',20);
4702 end if;
4703
4704 return FALSE;
4705 end;
4706
4707 if g_debug then
4708 hr_utility.set_location('End of check_is_number',20);
4709 end if;
4710
4711 return TRUE;
4712 end if;
4713 end check_is_number;
4714
4715
4716 ---------------------------------------------------------------------------
4717 -- Bug 5435088 The function to check if the payer id is invalid
4718 ---------------------------------------------------------------------------
4719 function check_payer_id (p_er_income_tax_number in varchar2,
4720 p_er_payer_id in varchar2) return char is
4721
4722 l_return varchar2(1);
4723 l_payer_id varchar2(20);
4724 l_payer_id_type varchar2(1);
4725 l_year number;
4726 begin
4727
4728 if g_debug then
4729 hr_utility.set_location('pysgirar: Start of check_payer_id',10);
4730 end if;
4731
4732 l_payer_id := p_er_income_tax_number;
4733 l_payer_id_type := p_er_payer_id;
4734
4735 l_return := 'Z';
4736
4737 if l_payer_id_type = '0' then
4738 l_return := '0';
4739 elsif l_payer_id_type = '7' then
4740 if length(l_payer_id) = 9 and
4741 check_is_number(substr(l_payer_id,1,8)) and
4742 not check_is_number(substr(l_payer_id,9,1)) then
4743 null;
4744 else
4745 l_return := '7';
4746 end if;
4747 elsif l_payer_id_type = '8' then
4748 if length(l_payer_id) = 10 then
4749 l_year := to_number(substr(l_payer_id, 1, 4));
4750 if ((l_year >= 1900 and l_year < 4712) and
4751 check_is_number(substr(l_payer_id, 5, 5)) and
4752 not check_is_number(substr(l_payer_id, 10, 1))) or
4753 (substr(l_payer_id, 1, 1) = 'F' and
4754 check_is_number(substr(l_payer_id, 2, 8)) and
4755 not check_is_number(substr(l_payer_id, 10, 1))) then
4756 null;
4757 else
4758 l_return := '8';
4759 end if;
4760 else
4761 l_return := '8';
4762 end if;
4763 elsif l_payer_id_type = 'A' then
4764 if length(l_payer_id) = 9 and
4765 substr(l_payer_id,1,1) = 'A' and
4766 check_is_number(substr(l_payer_id, 2, 7)) and
4767 not check_is_number(substr(l_payer_id, 9, 1)) then
4768 null;
4769 else
4770 l_return := 'A';
4771 end if;
4772 elsif l_payer_id_type = 'I' then
4773 if length(l_payer_id) = 10 and
4774 substr(l_payer_id, 1, 1) = '4' and
4775 check_is_number(substr(l_payer_id, 2,8)) and
4776 not check_is_number(substr(l_payer_id, 10, 1)) then
4777 null;
4778 else
4779 l_return := 'I';
4780 end if;
4781 elsif l_payer_id_type = 'C' then
4782 if length(l_payer_id) = 12 and
4783 check_is_number(substr(l_payer_id, 1, 11)) and
4784 not check_is_number(substr(l_payer_id, 12,1)) then
4785 null;
4786 else
4787 l_return := 'C';
4788 end if;
4789 elsif l_payer_id_type = 'M' then
4790 if length(l_payer_id) = 8 and
4791 substr(l_payer_id, 1,4) = 'MCST' and
4792 check_is_number(substr(l_payer_id, 5,4)) then
4793 null;
4794 else
4795 l_return := 'M';
4796 end if;
4797 elsif l_payer_id_type = 'G' then
4798 if length(l_payer_id) = 10 and
4799 substr(l_payer_id, 1, 1) = 'M' and
4800 check_is_number(substr(l_payer_id, 2,9)) then
4801 null;
4802 else
4803 l_return := 'G';
4804 end if;
4805 end if;
4806
4807 if g_debug then
4808 hr_utility.set_location('pysgirar: End of check_payer_id',20);
4809 end if;
4810
4811 return l_return;
4812 end check_payer_id;
4813
4814 ---------------------------------------------------------------------------
4815 -- Bug 5435088 The function to check if the payee id is invalid
4816 ---------------------------------------------------------------------------
4817 function check_payee_id (p_ee_income_tax_number in varchar2,
4818 p_payee_id_type in varchar2) return char is
4819
4820 l_return varchar2(1);
4821 l_payee_id varchar2(20);
4822 l_payee_id_type varchar2(1);
4823 l_year number;
4824 begin
4825
4826 if g_debug then
4827 hr_utility.set_location('pysgirar: Start of check_payee_id',10);
4828 end if;
4829
4830 l_payee_id := p_ee_income_tax_number;
4831 l_payee_id_type := p_payee_id_type;
4832
4833 l_return := 'Z';
4834
4835 if l_payee_id_type = '3' then
4836 if length(l_payee_id) = 8 and
4837 check_is_number(substr(l_payee_id, 1, 7)) and
4838 not check_is_number(substr(l_payee_id, 8, 1)) then
4839 null;
4840 else
4841 l_return := '3';
4842 end if;
4843 elsif l_payee_id_type = '5' then
4844 if length(l_payee_id) = 7 or
4845 length(l_payee_id) = 8 or
4846 (length(l_payee_id) = 12 and
4847 check_is_number(l_payee_id)) then
4848 null;
4849 else
4850 l_return := '5';
4851 end if;
4852 elsif l_payee_id_type = '4' then
4853 if length(l_payee_id) = 10 and
4854 check_is_number(substr(l_payee_id, 1, 1)) and
4855 substr(l_payee_id, 2, 1) = ' ' and
4856 check_is_number(substr(l_payee_id, 3, 7)) and
4857 not check_is_number(substr(l_payee_id, 10, 1)) then
4858 null;
4859 else
4860 l_return := '4';
4861 end if;
4862 end if;
4863
4864 if g_debug then
4865 hr_utility.set_location('pysgirar: End of check_payee_id',20);
4866 end if;
4867
4868 return l_return;
4869 end check_payee_id;
4870
4871
4872 ---------------------------------------------------------------------------
4873 -- Bug 5435088 - The function to get country code
4874 ---------------------------------------------------------------------------
4875 function get_country_code (p_country in varchar2) return varchar2
4876 is
4877
4878 l_country_code varchar2(3);
4879
4880 cursor country_code
4881 ( c_country per_addresses.country%type)
4882 is
4883 select meaning
4884 from hr_lookups
4885 where lookup_type = 'SG_COUNTRY_CODE'
4886 and lookup_code = c_country;
4887
4888 begin
4889
4890 if g_debug then
4891 hr_utility.set_location('Start of get_country_code',10);
4892 end if;
4893
4894 if p_country = 'ID' then
4895 l_country_code := '303';
4896 elsif p_country = 'MY' then
4897 l_country_code := '304';
4898 elsif p_country = 'PH' then
4899 l_country_code := '305';
4900 elsif p_country = 'TH' then
4901 l_country_code := '306';
4902 elsif p_country = 'JP' then
4903 l_country_code := '331';
4904 elsif p_country = 'TW' then
4905 l_country_code := '334';
4906 elsif p_country = 'CN' then
4907 l_country_code := '336';
4908 elsif p_country = 'GB' then
4909 l_country_code := '110';
4910 elsif p_country = 'US' then
4911 l_country_code := '503';
4912 elsif p_country = 'AU' then
4913 l_country_code := '701';
4914 elsif p_country = 'NZ' then
4915 l_country_code := '705';
4916 else
4917 open country_code(p_country);
4918 fetch country_code into l_country_code;
4919 if not country_code%found then
4920 l_country_code := '999';
4921 end if;
4922 close country_code;
4923 end if;
4924
4925 if g_debug then
4926 hr_utility.set_location('End of get_country_code',20);
4927 end if;
4928
4929 return l_country_code;
4930
4931 end get_country_code;
4932
4933 begin
4934 g_debug := hr_utility.debug_enabled;
4935 g_org_run := 'N';
4936 g_org_a8a_run := 'N';
4937 end pay_sg_iras_archive;