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