[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_IRAS_AMEND_ARCHIVE
Source
1 package body pay_sg_iras_amend_archive as
2 /* $Header: pysgiraa.pkb 120.0.12010000.4 2009/12/09 02:14:45 jalin noship $ */
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
17 g_previous_person_id per_all_people_f.person_id%type;
18 g_moa_369_date ff_archive_items.value%type;
19 -- Added for bug 5435088 org cursor only need to run once
20 g_name_of_bank ff_archive_items.value%type;
21 g_org_run char(1);
22 g_org_a8a_run char(1);
23 g_iras_method char(1); /* Bug 7415444 , Original or Amendment*/
24 l_counter number;
25 g_a8b_moa_348 number;
26 g_amend_ir8s_m_flag varchar2(1);
27 g_amend_a8a_flag varchar2(1);
28 g_amend_a8b_flag varchar2(1);
29 g_amend_ir8a_flag varchar2(1);
30 g_amend_ir8s_flag varchar2(1);
31 g_amend_ir8s_c_flag varchar2(1);
32
33 --------------------------------------------------------------------------------------------------------
34 -- Bug# 3501927 A8A Balance store rec
35 --------------------------------------------------------------------------------------------------------
36 type ytd_a8a_balance_store_rec is record
37 ( balance_id ff_user_entities.user_entity_id%type,
38 balance_value number );
39 type ytd_a8a_balance_tab is table of ytd_a8a_balance_store_rec index by binary_integer;
40 ytd_a8a_balance_rec ytd_a8a_balance_tab;
41 -- Bug# 3933332
42 g_org_a8a_flag char(1);
43
44 -----------------------------------------------------------------------------
45 -- The SELECT statement in this procedure returns the Person Ids for
46 -- Assignments that require the archive process to create an Assignment
47 -- Action.
48 -- Core Payroll recommends the select has minimal restrictions.
49 -----------------------------------------------------------------------------
50 procedure range_code
51 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
52 p_sql out nocopy varchar2 )
53 is
54 begin
55 if g_debug then
56 hr_utility.set_location('pysgiraa: Start of range_code',1);
57 end if;
58 --
59 p_sql := 'select distinct person_id ' ||
60 'from per_people_f ppf, ' ||
61 'pay_payroll_actions ppa ' ||
62 'where ppa.payroll_action_id = :payroll_action_id ' ||
63 'and ppa.business_group_id = ppf.business_group_id ' ||
64 'order by ppf.person_id';
65 --
66 if g_debug then
67 hr_utility.set_location('pysgiraa: End of range_code',2);
68 end if;
69 end range_code;
70 ----------------------------------------------------------------------------
71 -- Bug 3435334 - Pre-processor process now introduced for this archive.
72 -- Assignment actions are created for all assignments processed by pre-processor
73 ----------------------------------------------------------------------------
74 procedure assignment_action_code
75 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
76 p_start_person_id in per_all_people_f.person_id%type,
77 p_end_person_id in per_all_people_f.person_id%type,
78 p_chunk in number )
79 is
80 v_next_action_id pay_assignment_actions.assignment_action_id%type;
81 v_setup_action_id pay_payroll_actions.payroll_action_id%type;
82 v_assignment_id per_all_assignments_f.assignment_id%type;
83 v_person_id per_all_people_f.person_id%type;
84 v_assignment_set_id hr_assignment_sets.assignment_set_id%type;
85 --
86 cursor get_params(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
87 select pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters)
88 ,pay_core_utils.get_parameter('PERSON_ID',legislative_parameters)
89 ,pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters)
90 from pay_payroll_actions
91 where payroll_action_id = c_payroll_Action_id;
92 --
93 cursor next_action_id is
94 select pay_assignment_actions_s.nextval
95 from dual;
96 --
97 cursor process_assignments
98 (c_setup_action_id in pay_payroll_actions.payroll_action_id%type,
99 c_person_id in per_all_people_f.person_id%type,
100 c_assignment_set_id in hr_assignment_sets.assignment_set_id%type) is
101 select distinct pai.assignment_id
102 from pay_action_information pai
103 where pai.action_context_id = c_setup_action_id
104 and pai.action_context_type = 'AAP'
105 and pai.action_information_category = 'SG_IRAS_AMEND_SETUP'
106 and decode(c_assignment_set_id,null,'Y',
107 decode(hr_assignment_set.ASSIGNMENT_IN_SET(c_assignment_set_id,pai.assignment_id),'Y','Y','N')) = 'Y'
108 and action_information2 between p_start_person_id and p_end_person_id
109 and action_information2 = nvl(c_person_id,action_information2)
110 and exists (SELECT 1
111 FROM hr_organization_information
112 WHERE org_information_context = 'SG_IRAS_DETAILS'
113 AND organization_id = g_legal_entity_id
114 AND org_information1 = g_basis_year);
115
116 cursor csr_archive_action_id(p_assignment_id NUMBER)
117 is
118 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
119 from pay_payroll_actions ppa,
120 pay_assignment_actions paa
121 where ppa.payroll_action_id in (
122 SELECT org_information2
123 FROM hr_organization_information
124 WHERE org_information_context = 'SG_IRAS_DETAILS'
125 AND organization_id = g_legal_entity_id
126 AND org_information1 = g_basis_year)
127 and ppa.payroll_action_id = paa.payroll_action_id
128 and paa.assignment_id = p_assignment_id;
129
130 cursor csr_report_action_id(p_archive_action_id NUMBER) is
131 select intl.locking_action_id report_action_id
132 from pay_action_interlocks intl
133 where intl.locked_action_id = p_archive_action_id
134 and exists
135 ( select null
136 from per_assignments_f paf,
137 pay_assignment_actions mcl,
138 pay_payroll_actions mpl
139 where paf.assignment_id = mcl.assignment_id
140 and mpl.payroll_action_id = mcl.payroll_action_id
141 and mcl.assignment_Action_id = intl.locking_action_id
142 and mpl.effective_date between g_basis_start and g_basis_end
143 and pay_core_utils.get_parameter('LEGAL_ENTITY_ID',mpl.legislative_parameters) = g_legal_entity_id
144 and mpl.report_type in ( 'SG_A8B','SG_IR8A','SG_IR8S','SG_A8A','SG_A_A8A' )
145 and mpl.action_type = 'X'
146 and mcl.action_status = 'C'
147 group by paf.assignment_id
148 ) ;
149
150 begin
151 if g_debug then
152 hr_utility.set_location('pysgiraa: Start of assignment_action_code',3);
153 end if;
154 --
155 initialization_code(p_payroll_action_id);
156
157 open get_params( p_payroll_action_id );
158 fetch get_params into v_setup_action_id,
159 v_person_id,
160 v_assignment_set_id;
161 close get_params;
162 --
163 open process_assignments( v_setup_action_id,
164 v_person_id,
165 v_assignment_set_id) ;
166 loop
167 fetch process_assignments into v_assignment_id;
168 exit when process_assignments%notfound;
169 --
170 if g_debug then
171 hr_utility.set_location('pysgiraa: Before calling hr_nonrun_asact.insact',4);
172 end if;
173 --
174 open next_action_id ;
175 fetch next_action_id into v_next_action_id;
176 close next_action_id;
177 --
178 hr_nonrun_asact.insact( v_next_action_id,
179 v_assignment_id,
180 p_payroll_action_id,
181 p_chunk,
182 null );
183
184
185 for arch_rec in csr_archive_action_id(v_assignment_id) loop
186
187 hr_nonrun_asact.insint(lockingactid => v_next_action_id
188 ,lockedactid => arch_rec.assignment_action_id
189 );
190 for rep_rec in csr_report_action_id(arch_rec.assignment_action_id) loop
191
192 hr_nonrun_asact.insint(lockingactid => v_next_action_id
193 ,lockedactid => rep_rec.report_action_id
194 );
195
196 end loop;
197
198
199 end loop;
200 --
201 if g_debug then
202 hr_utility.set_location('pysgiraa: After calling hr_nonrun_asact.insact',4);
203 end if;
204 end loop;
205 --
206 close process_assignments;
207 --
208 if g_debug then
209 hr_utility.set_location('pysgiraa: End of assignment_action_code',5);
210 end if;
211 end assignment_action_code;
212 ------------------------------------------------------------------------
213 -- Bug 3435334 - Pre-processor process now introduced for this archive.
214 -- Populating PL/SQL table logic with rehire query is removed
215 ------------------------------------------------------------------------
216 procedure initialization_code
217 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
218 is
219 cursor get_params( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type ) is
220 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
221 to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
222 to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
223 pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
224 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
225 pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters),
226 report_type
227 from pay_payroll_actions
228 where payroll_action_id = c_payroll_action_id;
229 ------------------------------------------------------------------------
230 -- Bug 3933332 - Get A8A_Applicable flag
231 ------------------------------------------------------------------------
232 cursor get_org_a8a_applicable
233 is
234 select org_information19
235 from hr_organization_information,
236 pay_payroll_actions
237 where org_information_context ='SG_LEGAL_ENTITY'
238 and organization_id = pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters)
239 and payroll_action_id = p_payroll_action_id;
240 begin
241 if g_debug then
242 hr_utility.set_location('pysgiraa: Start of initialization_code',6);
243 end if;
244 --
245 if g_business_group_id is null then
246 open get_params( p_payroll_action_id );
247 fetch get_params
248 into g_business_group_id,
249 g_basis_start,
250 g_basis_end,
251 g_basis_year,
252 g_legal_entity_id,
253 g_setup_action_id,
254 g_report_type ;
255 close get_params;
256 end if;
257 ------------------------------------------------------------------------
258 -- Bug 3933332 - Get A8A_Applicable flag
259 ------------------------------------------------------------------------
260 if g_org_a8a_run <> 'Y' then
261 open get_org_a8a_applicable;
262 fetch get_org_a8a_applicable into g_org_a8a_flag;
263 close get_org_a8a_applicable;
264 g_org_a8a_run := 'Y';
265 end if;
266
267 if g_debug then
268 hr_utility.set_location('pysgiraa: End of initialization_code',8);
269 end if;
270 end initialization_code;
271 ------------------------------------------------------------------------
272 -- Selects the SRS parameters for the archive and calls other procedures
273 -- to archive the data in groups because depending on the data,
274 -- different parameters are required.
275 ------------------------------------------------------------------------
276 procedure archive_code
277 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
278 p_effective_date in date )
279 is
280 v_person_id per_all_people_f.person_id%type;
281 v_assignment_id per_all_assignments_f.assignment_id%type;
282 v_national_identifier varchar2(50);
283 v_archive_date pay_payroll_actions.effective_date%type;
284 l_person_id per_all_people_f.person_id%type;
285 l_archived_person_id binary_integer;
286
287 ------------------------------------------------------------------------
288 -- Bug 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
289 -- Bug 3260855 - Modified the cusor to fetch only person_id, asg_id instead of
290 -- legislative parameters as global values can be used, which are initialized in Init_code.
291 ------------------------------------------------------------------------
292 cursor get_details( c_assignment_action_id pay_assignment_actions.assignment_action_id%type ) is
293 select pap.person_id,
294 nvl(pap.national_identifier,pap.per_information12),
295 pac.assignment_id
296 from pay_assignment_actions pac,
297 per_assignments_f paa,
298 per_people_f pap
299 where pac.assignment_action_id = c_assignment_action_id
300 and paa.assignment_id = pac.assignment_id
301 and paa.person_id = pap.person_id ;
302
303 begin
304 if g_debug then
305 hr_utility.set_location('pysgiraa: Start of archive_code',10);
306 end if;
307 --
308
309 open get_details ( p_assignment_action_id );
310 fetch get_details into v_person_id,
311 v_national_identifier,
312 v_assignment_id;
313 --
314 if get_details%found then
315 close get_details;
316 --
317 if g_debug then
318 hr_utility.set_location('pysgiraa: Person Id: ' || to_char(v_person_id) ,100);
319 end if;
320 ------------------------------------------------------------------------------------------------
321 -- Because there are different routes for each group of data, a separate procedure
322 -- has been written for each.
323 -- Bug 2640107 : Call the archive procedures only for the latest person id
324 -- in case the employee is rehired with duplicate National Identifier/Income Tax Number
325 -- Bug 3435334 Introduced function employee_if_latest which returns a boolean TRUE/FALSE
326 -- to indicate if an employee needs to be archived / skipped for any rehires.
327 ------------------------------------------------------------------------------------------------
328 if employee_if_latest ( v_national_identifier,
329 v_person_id,
330 g_setup_action_id,
331 g_report_type ) then
332
333 --
334 -- Bug 4688761, only archives once if it has re-hire/multi
335 -- assignments
336 if NOT person_if_archived(v_person_id) then
337
338 --
339 -- Added for bug 4688761, share details should only
340 -- be archived for latest LE with primary defined if
341 -- it has rehired/multi-assignments with diff LE
342 --
343 if pri_if_latest(v_person_id,
344 g_legal_entity_id,
345 g_basis_start,
346 g_basis_end) then
347
348 archive_shares_details ( p_assignment_action_id,
349 v_person_id,
350 g_legal_entity_id,
351 g_basis_start,
352 g_basis_end );
353 end if;
354
355
356 archive_ir8s_c_details ( p_assignment_action_id,
357 v_person_id,
358 g_legal_entity_id,
359 g_business_group_id,
360 g_basis_start,
361 g_basis_end );
362
363 archive_balances ( p_assignment_action_id,
364 v_person_id,
365 g_business_group_id,
366 g_legal_entity_id,
367 g_basis_year );
368
369 end if;
370
371 l_archived_person_id := v_person_id;
372 t_archived_person(l_archived_person_id).person_id:= v_person_id;
373 else
374 if g_debug then
375 hr_utility.trace('The Employee has a duplicate employee so will not be processed');
376 end if;
377 end if;
378 else
379 close get_details;
380 end if;
381 --
382 if g_debug then
383 hr_utility.set_location('pysgiraa: End of archive_code',20);
384 end if;
385 end archive_code;
386 --------------------------------------------------------------------------------------
387 -- Bug#3501927 Added new function to fetch and calculate A8A Balances
388 -- Bug#6349937 Split the large group balances to small groups for
389 -- better performance
390 -- Do not include Obsoleted balances
391 ---------------------------------------------------------------------------------------
392 procedure a8a_balances_value
393 ( p_person_id in per_people_f.person_id%type,
394 p_assct_id in pay_assignment_actions.assignment_action_id%type,
395 p_tax_uid in pay_assignment_actions.tax_unit_id%type,
396 p_person_counter in number )
397 is
398 l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
399 l_context_tab pay_balance_pkg.t_context_tab;
400 l_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
401
402 l_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
403 l_detailed_bal_out_tab1 pay_balance_pkg.t_detailed_bal_out_tab;
404
405 l_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
406 l_detailed_bal_out_tab2 pay_balance_pkg.t_detailed_bal_out_tab;
407
408 l_balance_value_tab3 pay_balance_pkg.t_balance_value_tab;
409 l_detailed_bal_out_tab3 pay_balance_pkg.t_detailed_bal_out_tab;
410
411 l_balance_value_tab4 pay_balance_pkg.t_balance_value_tab;
412 l_detailed_bal_out_tab4 pay_balance_pkg.t_detailed_bal_out_tab;
413
414 l_balance_value_tab5 pay_balance_pkg.t_balance_value_tab;
415 l_detailed_bal_out_tab5 pay_balance_pkg.t_detailed_bal_out_tab;
416
417 l_ytd_a8a_counter number;
418 --
419 cursor ytd_A8A_balances is
420 select fue.user_entity_id,
421 pdb.defined_balance_id def_bal_id
422 from ff_user_entities fue,
423 pay_balance_types pbt,
424 pay_defined_balances pdb,
425 pay_balance_dimensions pbd
426 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
427 and fue.legislation_code = 'SG'
428 and pbt.legislation_code = 'SG'
429 and pbd.legislation_code = pbt.legislation_code
430 and pdb.legislation_code = pbt.legislation_code
431 and pbt.balance_name like 'A8A%'
432 and upper(pbt.reporting_name) not like '%OBSOLETE%'
433 and pbt.balance_type_id = pdb.balance_type_id
434 and pbd.balance_dimension_id = pdb.balance_dimension_id
435 and pbd.dimension_name = '_PER_LE_YTD'
436 order by pbt.balance_name asc;
437 --
438 cursor benefit_inkind_bal is
439 select nvl(pei_information2, l_detailed_bal_out_tab(1).balance_value), --A8A_MOA_500
440 nvl(pei_information3, l_detailed_bal_out_tab(2).balance_value), --A8A_MOA_501
441 nvl(pei_information4, l_detailed_bal_out_tab(3).balance_value), --A8A_MOA_502
442 nvl(pei_information5, l_detailed_bal_out_tab(7).balance_value), --A8A_MOA_506
443 nvl(pei_information6, l_detailed_bal_out_tab(8).balance_value), --A8A_MOA_507
444 nvl(pei_information7, l_detailed_bal_out_tab(9).balance_value), --A8A_MOA_508
445 nvl(pei_information8, l_detailed_bal_out_tab(10).balance_value),--A8A_MOA_509
446 nvl(pei_information9, l_detailed_bal_out_tab(11).balance_value),--A8A_MOA_510
447 nvl(pei_information10,l_detailed_bal_out_tab(12).balance_value),--A8A_MOA_511
448 nvl(pei_information11,l_detailed_bal_out_tab(13).balance_value),--A8A_MOA_512
449 nvl(pei_information12,l_detailed_bal_out_tab(14).balance_value),--A8A_MOA_513
450 nvl(pei_information13,l_detailed_bal_out_tab(15).balance_value),--A8A_MOA_514
451 nvl(pei_information14,l_detailed_bal_out_tab(17).balance_value),--A8A_MOA_516
452 nvl(pei_information15,l_detailed_bal_out_tab(26).balance_value),--A8A_MOA_525
453 nvl(pei_information16,l_detailed_bal_out_tab(27).balance_value),--A8A_MOA_526
454 nvl(pei_information17,l_detailed_bal_out_tab(28).balance_value),--A8A_MOA_527
455 nvl(pei_information22,l_detailed_bal_out_tab(29).balance_value),--A8A_MOA_528
456 nvl(pei_information23,l_detailed_bal_out_tab(30).balance_value),--A8A_MOA_529
457 nvl(pei_information24,l_detailed_bal_out_tab(31).balance_value),--A8A_MOA_530
458 nvl(pei_information18,l_detailed_bal_out_tab(32).balance_value),--A8A_MOA_531
459 nvl(pei_information19,l_detailed_bal_out_tab(33).balance_value),--A8A_MOA_532
460 nvl(pei_information20,l_detailed_bal_out_tab(34).balance_value),--A8A_MOA_533
461 nvl(pei_information21,l_detailed_bal_out_tab(35).balance_value) --A8A_MOA_534
462 from per_people_extra_info pae
463 where person_id = p_person_id
464 and information_type = 'HR_A8A_BENEFITS_IN_KIND_SG'
465 and pei_information1 = g_basis_year;
466 --
467 cursor furniture_exp_bal is
468 select nvl(pei_information2, l_detailed_bal_out_tab(45).balance_value), --A8A_QTY_304
469 nvl(pei_information3, l_detailed_bal_out_tab(46).balance_value), --A8A_QTY_305
470 nvl(pei_information4, l_detailed_bal_out_tab(47).balance_value), --A8A_QTY_306
471 nvl(pei_information5, l_detailed_bal_out_tab(48).balance_value), --A8A_QTY_307
472 nvl(pei_information6, l_detailed_bal_out_tab(49).balance_value), --A8A_QTY_308
473 nvl(pei_information7, l_detailed_bal_out_tab(50).balance_value), --A8A_QTY_309
474 nvl(pei_information8, l_detailed_bal_out_tab(51).balance_value), --A8A_QTY_310
475 nvl(pei_information9, l_detailed_bal_out_tab(52).balance_value), --A8A_QTY_311
476 nvl(pei_information10,l_detailed_bal_out_tab(53).balance_value), --A8A_QTY_312
477 nvl(pei_information11,l_detailed_bal_out_tab(54).balance_value), --A8A_QTY_313
478 nvl(pei_information12,l_detailed_bal_out_tab(55).balance_value), --A8A_QTY_314
479 nvl(pei_information13,l_detailed_bal_out_tab(56).balance_value), --A8A_QTY_315
480 nvl(pei_information14,l_detailed_bal_out_tab(57).balance_value), --A8A_QTY_316
481 nvl(pei_information15,l_detailed_bal_out_tab(58).balance_value), --A8A_QTY_317
482 nvl(pei_information16,l_detailed_bal_out_tab(59).balance_value), --A8A_QTY_318
483 nvl(pei_information17,l_detailed_bal_out_tab(60).balance_value), --A8A_QTY_319
484 nvl(pei_information18,l_detailed_bal_out_tab(61).balance_value), --A8A_QTY_320
485 nvl(pei_information19,l_detailed_bal_out_tab(18).balance_value), --A8A_MOA_517
486 nvl(pei_information20,l_detailed_bal_out_tab(19).balance_value), --A8A_MOA_518
487 nvl(pei_information21,l_detailed_bal_out_tab(20).balance_value), --A8A_MOA_519
488 nvl(pei_information22,l_detailed_bal_out_tab(21).balance_value), --A8A_MOA_520
489 nvl(pei_information23,l_detailed_bal_out_tab(22).balance_value), --A8A_MOA_521
490 nvl(pei_information24,l_detailed_bal_out_tab(23).balance_value), --A8A_MOA_522
491 nvl(pei_information25,l_detailed_bal_out_tab(24).balance_value), --A8A_MOA_523
492 nvl(pei_information26,l_detailed_bal_out_tab(25).balance_value) --A8A_MOA_524
493 from per_people_extra_info pae
494 where person_id = p_person_id
495 and information_type ='HR_A8A_FURN_EXP_SG'
496 and pei_information1 = g_basis_year;
497 --
498 cursor hotel_accom_bal is
499 select nvl(pei_information2, l_detailed_bal_out_tab(62).balance_value), --A8A_QTY_321
500 nvl(pei_information3, l_detailed_bal_out_tab(63).balance_value), --A8A_QTY_322
501 nvl(pei_information4, l_detailed_bal_out_tab(64).balance_value), --A8A_QTY_323
502 nvl(pei_information5, l_detailed_bal_out_tab(65).balance_value), --A8A_QTY_324
503 nvl(pei_information6, l_detailed_bal_out_tab(66).balance_value), --A8A_QTY_325
504 nvl(pei_information7, l_detailed_bal_out_tab(67).balance_value), --A8A_QTY_326
505 nvl(pei_information8, l_detailed_bal_out_tab(68).balance_value), --A8A_QTY_327
506 nvl(pei_information9, l_detailed_bal_out_tab(69).balance_value), --A8A_QTY_328
507 nvl(pei_information10,l_detailed_bal_out_tab(40).balance_value), --A8A_MOA_539
508 nvl(pei_information11,l_detailed_bal_out_tab(41).balance_value), --A8A_QTY_300
509 nvl(pei_information12,l_detailed_bal_out_tab(42).balance_value), --A8A_QTY_301
510 nvl(pei_information13,l_detailed_bal_out_tab(43).balance_value), --A8A_QTY_302
511 nvl(pei_information14,l_detailed_bal_out_tab(44).balance_value), --A8A_QTY_303
512 nvl(pei_information15,l_detailed_bal_out_tab(6).balance_value) --A8A_MOA_505
513 , nvl(pei_information16,l_detailed_bal_out_tab(70).balance_value) --A8A_QTY_329, bug 5435088
514 from per_people_extra_info pae
515 where person_id = p_person_id
516 and information_type ='HR_A8A_HOTEL_ACCOM_SG'
517 and pei_information1 = g_basis_year;
518 --
519 cursor c_globals (p_global_name in varchar2) is
520 select global_value
521 from ff_globals_f
522 where global_name = p_global_name;
523 --
524 l_a8a_person_20 number;
525 l_a8a_child_8_20 number;
526 l_a8a_child_3_7 number;
527 l_a8a_child_3 number;
528
529 /* Bug 5230059 */
530 l_a8a_person_20_a number;
531 l_a8a_child_8_20_a number;
532 l_a8a_child_3_7_a number;
533 l_a8a_child_3_a number;
534
535 l_count number;
536 begin
537 l_ytd_a8a_counter := 1;
538 l_balance_value_tab.delete;
539 l_context_tab.delete;
540 l_detailed_bal_out_tab.delete;
541 --
542 l_balance_value_tab1.delete;
543 l_detailed_bal_out_tab1.delete;
544 --
545 l_balance_value_tab2.delete;
546 l_detailed_bal_out_tab2.delete;
547 --
548 l_balance_value_tab3.delete;
549 l_detailed_bal_out_tab3.delete;
550
551 l_balance_value_tab4.delete;
552 l_detailed_bal_out_tab4.delete;
553
554 l_balance_value_tab5.delete;
555 l_detailed_bal_out_tab5.delete;
556
557 l_count := 14;
558
559 if t_ytd_a8a_balanceid_store.count = 0 then
560 open ytd_a8a_balances;
561 loop
562 fetch ytd_a8a_balances into t_ytd_a8a_balanceid_store(l_ytd_a8a_counter).user_entity_id,
563 t_ytd_a8a_balanceid_store(l_ytd_a8a_counter).defined_balance_id;
564 l_ytd_a8a_counter := l_ytd_a8a_counter + 1;
565 exit when ytd_a8a_balances%NOTFOUND;
566 end loop;
567 close ytd_a8a_balances;
568 end if;
569 --
570
571 for counter in 1..l_count
572 loop
573 l_balance_value_tab1(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter).defined_balance_id;
574 l_balance_value_tab2(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+l_count).defined_balance_id;
575 l_balance_value_tab3(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+2*l_count).defined_balance_id;
576 l_balance_value_tab4(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+3*l_count).defined_balance_id;
577 l_balance_value_tab5(counter).defined_balance_id := t_ytd_a8a_balanceid_store(counter+4*l_count).defined_balance_id;
578
579 l_context_tab(counter).tax_unit_id := p_tax_uid;
580 end loop;
581
582 --
583 if p_assct_id is not null then
584 pay_balance_pkg.get_value( p_assct_id,
585 l_balance_value_tab1,
586 l_context_tab,
587 false,
588 false,
589 l_detailed_bal_out_tab1);
590 pay_balance_pkg.get_value( p_assct_id,
591 l_balance_value_tab2,
592 l_context_tab,
593 false,
594 false,
595 l_detailed_bal_out_tab2);
596 pay_balance_pkg.get_value( p_assct_id,
597 l_balance_value_tab3,
598 l_context_tab,
599 false,
600 false,
601 l_detailed_bal_out_tab3);
602 pay_balance_pkg.get_value( p_assct_id,
603 l_balance_value_tab4,
604 l_context_tab,
605 false,
606 false,
607 l_detailed_bal_out_tab4);
608 pay_balance_pkg.get_value( p_assct_id,
609 l_balance_value_tab5,
610 l_context_tab,
611 false,
612 false,
613 l_detailed_bal_out_tab5);
614 end if;
615 for counter in 1..l_count
616 loop
617 l_detailed_bal_out_tab(counter).balance_value := l_detailed_bal_out_tab1(counter).balance_value;
618 l_detailed_bal_out_tab(counter+l_count).balance_value := l_detailed_bal_out_tab2(counter).balance_value;
619 l_detailed_bal_out_tab(counter+2*l_count).balance_value := l_detailed_bal_out_tab3(counter).balance_value;
620 l_detailed_bal_out_tab(counter+3*l_count).balance_value := l_detailed_bal_out_tab4(counter).balance_value;
621 l_detailed_bal_out_tab(counter+4*l_count).balance_value := l_detailed_bal_out_tab5(counter).balance_value;
622 end loop;
623
624 --
625 open benefit_inkind_bal;
626 fetch benefit_inkind_bal into
627 l_detailed_bal_out_tab(1).balance_value, --A8A_MOA_500
628 l_detailed_bal_out_tab(2).balance_value, --A8A_MOA_501
629 l_detailed_bal_out_tab(3).balance_value, --A8A_MOA_502
630 l_detailed_bal_out_tab(7).balance_value, --A8A_MOA_506
631 l_detailed_bal_out_tab(8).balance_value, --A8A_MOA_507
632 l_detailed_bal_out_tab(9).balance_value, --A8A_MOA_508
633 l_detailed_bal_out_tab(10).balance_value, --A8A_MOA_509
634 l_detailed_bal_out_tab(11).balance_value, --A8A_MOA_510
635 l_detailed_bal_out_tab(12).balance_value, --A8A_MOA_511
636 l_detailed_bal_out_tab(13).balance_value, --A8A_MOA_512
637 l_detailed_bal_out_tab(14).balance_value, --A8A_MOA_513
638 l_detailed_bal_out_tab(15).balance_value, --A8A_MOA_514
639 l_detailed_bal_out_tab(17).balance_value, --A8A_MOA_516
640 l_detailed_bal_out_tab(26).balance_value, --A8A_MOA_525
641 l_detailed_bal_out_tab(27).balance_value, --A8A_MOA_526
642 l_detailed_bal_out_tab(28).balance_value, --A8A_MOA_527
643 l_detailed_bal_out_tab(29).balance_value, --A8A_MOA_528
644 l_detailed_bal_out_tab(30).balance_value, --A8A_MOA_529
645 l_detailed_bal_out_tab(31).balance_value, --A8A_MOA_530
646 l_detailed_bal_out_tab(32).balance_value, --A8A_MOA_531
647 l_detailed_bal_out_tab(33).balance_value, --A8A_MOA_532
648 l_detailed_bal_out_tab(34).balance_value, --A8A_MOA_533
649 l_detailed_bal_out_tab(35).balance_value ; --A8A_MOA_534
650 close benefit_inkind_bal;
651 --
652 open furniture_exp_bal;
653 fetch furniture_exp_bal into
654 l_detailed_bal_out_tab(45).balance_value, --A8A_QTY_304
655 l_detailed_bal_out_tab(46).balance_value, --A8A_QTY_305
656 l_detailed_bal_out_tab(47).balance_value, --A8A_QTY_306
657 l_detailed_bal_out_tab(48).balance_value, --A8A_QTY_307
658 l_detailed_bal_out_tab(49).balance_value, --A8A_QTY_308
659 l_detailed_bal_out_tab(50).balance_value, --A8A_QTY_309
660 l_detailed_bal_out_tab(51).balance_value, --A8A_QTY_310
661 l_detailed_bal_out_tab(52).balance_value, --A8A_QTY_311
662 l_detailed_bal_out_tab(53).balance_value, --A8A_QTY_312
663 l_detailed_bal_out_tab(54).balance_value, --A8A_QTY_313
664 l_detailed_bal_out_tab(55).balance_value, --A8A_QTY_314
665 l_detailed_bal_out_tab(56).balance_value, --A8A_QTY_315
666 l_detailed_bal_out_tab(57).balance_value, --A8A_QTY_316
667 l_detailed_bal_out_tab(58).balance_value, --A8A_QTY_317
668 l_detailed_bal_out_tab(59).balance_value, --A8A_QTY_318
669 l_detailed_bal_out_tab(60).balance_value, --A8A_QTY_319
670 l_detailed_bal_out_tab(61).balance_value, --A8A_QTY_320
671 l_detailed_bal_out_tab(18).balance_value, --A8A_MOA_517
672 l_detailed_bal_out_tab(19).balance_value, --A8A_MOA_518
673 l_detailed_bal_out_tab(20).balance_value, --A8A_MOA_519
674 l_detailed_bal_out_tab(21).balance_value, --A8A_MOA_520
675 l_detailed_bal_out_tab(22).balance_value, --A8A_MOA_521
676 l_detailed_bal_out_tab(23).balance_value, --A8A_MOA_522
677 l_detailed_bal_out_tab(24).balance_value, --A8A_MOA_523
678 l_detailed_bal_out_tab(25).balance_value ; --A8A_MOA_524
679 close furniture_exp_bal ;
680 --
681 open hotel_accom_bal;
682 fetch hotel_accom_bal into
683 l_detailed_bal_out_tab(62).balance_value, --A8A_QTY_321
684 l_detailed_bal_out_tab(63).balance_value, --A8A_QTY_322
685 l_detailed_bal_out_tab(64).balance_value, --A8A_QTY_323
686 l_detailed_bal_out_tab(65).balance_value, --A8A_QTY_324
687 l_detailed_bal_out_tab(66).balance_value, --A8A_QTY_325
688 l_detailed_bal_out_tab(67).balance_value, --A8A_QTY_326
689 l_detailed_bal_out_tab(68).balance_value, --A8A_QTY_327
690 l_detailed_bal_out_tab(69).balance_value, --A8A_QTY_328
691 l_detailed_bal_out_tab(40).balance_value, --A8A_MOA_539
692 l_detailed_bal_out_tab(41).balance_value, --A8A_QTY_300
693 l_detailed_bal_out_tab(42).balance_value, --A8A_QTY_301
694 l_detailed_bal_out_tab(43).balance_value, --A8A_QTY_302
695 l_detailed_bal_out_tab(44).balance_value, --A8A_QTY_303
696 l_detailed_bal_out_tab(6).balance_value, --A8A_MOA_505
697 l_detailed_bal_out_tab(70).balance_value; -- A8A_QTY_329
698
699 close hotel_accom_bal;
700 -------------------------------------------------------------
701 -- Calculation for A8A_MOA_503 (Sum of MOA 517 to 534))
702 -------------------------------------------------------------
703 l_detailed_bal_out_tab(4).balance_value :=
704 l_detailed_bal_out_tab(18).balance_value + l_detailed_bal_out_tab(19).balance_value
705 + l_detailed_bal_out_tab(20).balance_value + l_detailed_bal_out_tab(21).balance_value
706 + l_detailed_bal_out_tab(22).balance_value + l_detailed_bal_out_tab(23).balance_value
707 + l_detailed_bal_out_tab(24).balance_value + l_detailed_bal_out_tab(25).balance_value
708 + l_detailed_bal_out_tab(26).balance_value + l_detailed_bal_out_tab(27).balance_value
709 + l_detailed_bal_out_tab(28).balance_value + l_detailed_bal_out_tab(29).balance_value
710 + l_detailed_bal_out_tab(30).balance_value + l_detailed_bal_out_tab(31).balance_value
711 + l_detailed_bal_out_tab(32).balance_value + l_detailed_bal_out_tab(33).balance_value
712 + l_detailed_bal_out_tab(34).balance_value + l_detailed_bal_out_tab(35).balance_value ;
713 --
714 open c_globals( 'A8A_PERSON_20' );
715 fetch c_globals into l_a8a_person_20;
716 close c_globals;
717 -------------------------------------------------------------
718 -- Calculation for A8A_MOA_535
719 -- (A8A_QTY_321 * Rate * 12 * A8A_QTY_322 /365)
720 -- Bug 7415444, A8A_QTY_322 can not <0
721 -------------------------------------------------------------
722 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;
723 --
724 if l_detailed_bal_out_tab(62).balance_value > 0 and
725 l_detailed_bal_out_tab(63).balance_value > 0 then /*Bug7415444*/
726 if l_a8a_person_20_a between 0 and 1 then
727 l_detailed_bal_out_tab(36).balance_value := 1;
728 else
729 l_detailed_bal_out_tab(36).balance_value := trunc(l_a8a_person_20_a);
730 end if;
731 else
732 l_detailed_bal_out_tab(36).balance_value := 0;
733 end if;
734 --
735 open c_globals('A8A_CHILD_8_20');
736 fetch c_globals into l_a8a_child_8_20;
737 close c_globals;
738 -------------------------------------------------------------
739 -- Calculation for A8A_MOA_536
740 -- (A8A_QTY_323 * Rate * 12 * A8A_QTY_324 /365)
741 -- Bug 5230059
742 -- Bug 7415444, A8A_QTY_324 cannot < 0
743 -------------------------------------------------------------
744 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;
745 --
746 if l_detailed_bal_out_tab(64).balance_value > 0 and
747 l_detailed_bal_out_tab(65).balance_value > 0 then
748 if l_a8a_child_8_20_a between 0 and 1 then
749 l_detailed_bal_out_tab(37).balance_value := 1;
750 else
751 l_detailed_bal_out_tab(37).balance_value := trunc(l_a8a_child_8_20_a);
752 end if;
753 else
754 l_detailed_bal_out_tab(37).balance_value := 0;
755 end if;
756 --
757 open c_globals ('A8A_CHILD_3_7');
758 fetch c_globals into l_a8a_child_3_7;
759 close c_globals;
760 -------------------------------------------------------------
761 -- Calculation for A8A_MOA_537
762 -- (A8A_QTY_325 * rate * 12 * A8A_QTY_326/365)
763 -- Bug 7415444, A8A_QTY_326 cannot < 0
764 -------------------------------------------------------------
765 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;
766 --
767 if l_detailed_bal_out_tab(66).balance_value > 0 and
768 l_detailed_bal_out_tab(67).balance_value > 0 then
769 if l_a8a_child_3_7_a between 0 and 1 then
770 l_detailed_bal_out_tab(38).balance_value := 1;
771 else
772 l_detailed_bal_out_tab(38).balance_value := trunc(l_a8a_child_3_7_a);
773 end if;
774 else
775 l_detailed_bal_out_tab(38).balance_value := 0;
776 end if;
777 --
778 open c_globals('A8A_CHILD_3');
779 fetch c_globals into l_a8a_child_3;
780 close c_globals;
781 -------------------------------------------------------------
782 -- Calculation for A8A_MOA_538
783 -- (A8A_QTY_327 * rate * 12 * A8A_QTY_328/365)
784 -- Bug 5230059
785 -- Bug 7415444, A8A_QTY_328 cannot < 0
786 -------------------------------------------------------------
787 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;
788
789 --
790 if l_detailed_bal_out_tab(68).balance_value > 0 and
791 l_detailed_bal_out_tab(69).balance_value > 0 then
792 if l_a8a_child_3_a between 0 and 1 then
793 l_detailed_bal_out_tab(39).balance_value := 1;
794 else
795 l_detailed_bal_out_tab(39).balance_value := trunc(l_a8a_child_3_a);
796 end if;
797 else
798 l_detailed_bal_out_tab(39).balance_value := 0;
799 end if;
800
801 ------------------------------------------------------------
802 -- Bug 5435088, if the No of employees sharing the Quarter is not zero
803 -- MOA500 and MOA503 is divided by the number of employee sharing
804 -- Removed calculation for bug fix 5644617
805 ------------------------------------------------------------
806
807 -------------------------------------------------------------
808 -- Calculation for A8A_MOA_504 (Sum of MOA 535 to 539)
809 -------------------------------------------------------------
810 l_detailed_bal_out_tab(5).balance_value :=
811 l_detailed_bal_out_tab(36).balance_value + l_detailed_bal_out_tab(37).balance_value
812 + l_detailed_bal_out_tab(38).balance_value + l_detailed_bal_out_tab(39).balance_value
813 + l_detailed_bal_out_tab(40).balance_value;
814 -------------------------------------------------------------
815 -- Calculation for A8A_MOA_515 (MOA 500 + 503 + 504 + 505 + 506 + 507 +508 + 509 + 510 + 511 + 512 + 513 + 514 + 516 )
816 -- Bug#3948951 Moved the code after A8A_MOA_504 balance calculation.
817 -------------------------------------------------------------
818 l_detailed_bal_out_tab(16).balance_value :=
819 l_detailed_bal_out_tab(1).balance_value + l_detailed_bal_out_tab(4).balance_value
820 + l_detailed_bal_out_tab(5).balance_value + l_detailed_bal_out_tab(6).balance_value
821 + l_detailed_bal_out_tab(7).balance_value + l_detailed_bal_out_tab(8).balance_value
822 + l_detailed_bal_out_tab(9).balance_value + l_detailed_bal_out_tab(10).balance_value
823 + l_detailed_bal_out_tab(11).balance_value + l_detailed_bal_out_tab(12).balance_value
824 + l_detailed_bal_out_tab(13).balance_value + l_detailed_bal_out_tab(14).balance_value
825 + l_detailed_bal_out_tab(15).balance_value + l_detailed_bal_out_tab(17).balance_value ;
826 --
827 for counter in 1..l_detailed_bal_out_tab.count
828 loop
829 if p_person_counter = 1 then
830 if l_detailed_bal_out_tab.exists(counter) then
831 ytd_a8a_balance_rec(counter).balance_id := t_ytd_a8a_balanceid_store(counter).user_entity_id;
832 ytd_a8a_balance_rec(counter).balance_value := nvl(l_detailed_bal_out_tab(counter).balance_value,0) ;
833 end if;
834 else
835 if l_detailed_bal_out_tab.exists(counter) then
836 if ytd_a8a_balance_rec.exists(counter) then
837 ytd_a8a_balance_rec(counter).balance_value := nvl(l_detailed_bal_out_tab(counter).balance_value,0)
838 + ytd_a8a_balance_rec(counter).balance_value;
839 end if;
840 end if;
841 end if;
842 end loop;
843 --
844 exception
845 when others then
846 hr_utility.set_location('pysgiraa: Error in a8a_balances_value',10);
847 raise;
848 end;
849
850 ---------------------------------------------------------------------------
851 -- Selects data required to archive the YTD and Month balances. The
852 -- cursors' main purpose is to select the latest action sequence for the
853 -- PERSON (independent of assignment) within the Legal Entity, and pass
854 -- that to pay_balance_pkg.
855 -- Also the User Entity Name must match up to the balance.
856 --
857 -- YTD Balances: All IRAS balances + specific previously seeded balances
858 -- Month Balances: Specific balances required for IR8S as this breaks down
859 -- earnings by month.
860 ---------------------------------------------------------------------------
861 procedure archive_balances
862 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
863 p_person_id in per_all_people_f.person_id%type,
864 p_business_group_id in hr_organization_units.business_group_id%type,
865 p_tax_unit_id in ff_archive_item_contexts.context%type,
866 p_basis_year in varchar2 )
867 is
868 v_run_ass_action_id pay_assignment_actions.assignment_action_id%type;
869 v_date_earned ff_archive_item_contexts.context%type;
870 v_balance_value ff_archive_items.value%type;
871 v_archive_item_id ff_archive_items.archive_item_id%type;
872 v_object_version_number ff_archive_items.object_version_number%type;
873 v_some_warning boolean;
874 --------------------------------------------------------------------------------------
875 --Bug#3933332 Moved the records from package header as these pl/sql table is
876 -- specific to procedure archive_balances()
877 --------------------------------------------------------------------------------------
878 type t_archive_items_tab is table of ff_archive_items.archive_item_id%TYPE index by binary_integer;
879 t_archive_items t_archive_items_tab;
880 t_archive_items_orig t_archive_items_tab;
881 t_archive_items_a8a t_archive_items_tab;
882 t_archive_items_ir8a t_archive_items_tab;
883 t_archive_items_ir8s t_archive_items_tab;
884 t_old_value t_archive_items_tab;
885 --
886 type t_archive_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
887 t_archive_value t_archive_value_tab;
888 t_orig_value t_archive_value_tab;
889 t_flag t_archive_value_tab;
890 t_archive_value_a8a t_archive_value_tab;
891 t_archive_value_ir8a t_archive_value_tab;
892 t_archive_value_ir8s t_archive_value_tab;
893
894 type t_amend_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
895 t_amend_value t_amend_value_tab;
896 t_amend_value_a8a t_amend_value_tab;
897 t_amend_value_ir8a t_amend_value_tab;
898 t_amend_value_ir8s t_amend_value_tab;
899 --
900 type t_date_earned_tab is table of varchar2(30) index by binary_integer;
901 t_date_earned t_date_earned_tab;
902 --
903 type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
904 t_user_entity_id t_user_entity_tab;
905 t_amend_ue_id t_user_entity_tab;
906 t_orig_user_entity_id t_user_entity_tab;
907 t_user_entity_id_ir8a t_user_entity_tab;
908 t_user_entity_id_ir8s t_user_entity_tab;
909 t_user_entity_id_a8a t_user_entity_tab;
910 l_orig_assact_id number;
911
912 --
913 ---------------------------------------------------------------------------------------------------
914 -- This ytd_balances cursor only gets the defined_balance_id and user_entity_id
915 -- Bug 6349937, do not include Obsoleted balances
916 ---------------------------------------------------------------------------------------------------
917 cursor ytd_balances_ir8s is
918 select fue.user_entity_id,
919 pdb.defined_balance_id def_bal_id
920 from ff_user_entities fue,
921 pay_balance_types pbt,
922 pay_defined_balances pdb,
923 pay_balance_dimensions pbd
924 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
925 and fue.legislation_code = 'SG'
926 and pbt.legislation_code = 'SG'
927 and pbd.legislation_code = pbt.legislation_code
928 and pdb.legislation_code = pbt.legislation_code
929 and ( pbt.balance_name in ('Voluntary CPF Liability','CPF Liability',
930 'Voluntary CPF Withheld','CPF Withheld',
931 'Employee CPF Contributions Additional Earnings',
932 'Employee CPF Contributions Ordinary Earnings',
933 'Employer CPF Contributions Additional Earnings',
934 'Employer CPF Contributions Ordinary Earnings',
935 'Additional Earnings','Ordinary Earnings',
936 'Employer Vol CPF Contributions Ordinary Earnings',
937 'Employee Vol CPF Contributions Ordinary Earnings',
938 'Employer Vol CPF Contributions Additional Earnings',
939 'Employee Vol CPF Contributions Additional Earnings')
940 or
941 ( pbt.balance_name like 'IR8S%' ) )
942 and upper(pbt.reporting_name) not like '%OBSOLETE%'
943 and pbt.balance_type_id = pdb.balance_type_id
944 and pbd.balance_dimension_id = pdb.balance_dimension_id
945 and pbd.dimension_name = '_PER_LE_YTD';
946
947 cursor ytd_balances_ir8a is
948 select fue.user_entity_id,
949 pdb.defined_balance_id def_bal_id
950 from ff_user_entities fue,
951 pay_balance_types pbt,
952 pay_defined_balances pdb,
953 pay_balance_dimensions pbd
954 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
955 and fue.legislation_code = 'SG'
956 and pbt.legislation_code = 'SG'
957 and pbd.legislation_code = pbt.legislation_code
958 and pdb.legislation_code = pbt.legislation_code
959 and pbt.balance_name like 'IR8A%'
960 and upper(pbt.reporting_name) not like '%OBSOLETE%'
961 and pbt.balance_type_id = pdb.balance_type_id
962 and pbd.balance_dimension_id = pdb.balance_dimension_id
963 and pbd.dimension_name = '_PER_LE_YTD';
964 ---------------------------------------------------------------------------------------------------
965 -- Bug 2629839. Cursor month_year_action is split into two cursors month_year_action_sequence and
966 -- month_year_action to improve the performance
967 -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
968 -- Cursor month_year_action_sequence
969 ---------------------------------------------------------------------------------------------------
970 cursor month_year_action_sequence
971 ( c_person_id per_all_people_f.person_id%type,
972 c_business_group_id hr_organization_units.business_group_id%type,
973 c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
974 c_basis_year varchar2 )
975 is
976 select /*+ ORDERED USE_NL(pacmax) */
977 max(pacmax.action_sequence) act_seq,
978 to_char(ppamax.effective_date,'MM')
979 from per_assignments_f paamax,
980 pay_assignment_actions pacmax,
981 pay_payroll_actions ppamax
982 where ppamax.business_group_id = c_business_group_id
983 and pacmax.tax_unit_id = c_legal_entity_id
984 and paamax.person_id = c_person_id
985 and paamax.assignment_id = pacmax.assignment_id
986 and ppamax.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
987 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
988 and ppamax.payroll_action_id = pacmax.payroll_action_id
989 and ppamax.action_type in ('R','B','I','Q','V')
990 group by to_char(ppamax.effective_date,'MM')
991 order by to_char(ppamax.effective_date,'MM');
992 ---------------------------------------------------------------------------------------------------
993 -- cursor month_year_action
994 ---------------------------------------------------------------------------------------------------
995 cursor month_year_action
996 ( c_person_id per_all_people_f.person_id%type,
997 c_business_group_id hr_organization_units.business_group_id%type,
998 c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
999 c_basis_year varchar2,
1000 c_action_sequence pay_assignment_actions.action_sequence%type )
1001 is
1002 select /*+ ORDERED USE_NL(pac) */
1003 pac.assignment_action_id assact_id,
1004 decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1005 pac.tax_unit_id tax_uid
1006 from per_assignments_f paa,
1007 pay_assignment_actions pac,
1008 pay_payroll_actions ppa
1009 where ppa.business_group_id = c_business_group_id
1010 and pac.tax_unit_id = c_legal_entity_id
1011 and paa.person_id = c_person_id
1012 and paa.assignment_id = pac.assignment_id
1013 and ppa.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
1014 and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
1015 and ppa.payroll_action_id = pac.payroll_action_id
1016 and pac.action_sequence = c_action_sequence;
1017 ---------------------------------------------------------------------------------------------------
1018 -- this month_balances cursor only gets the defined_balance_id and user_entity_id
1019 -- Bug 3232303- Added 4 new balances.
1020 ---------------------------------------------------------------------------------------------------
1021 cursor month_balances
1022 is
1023 select fue.user_entity_id,
1024 pdb.defined_balance_id def_bal_id
1025 from ff_user_entities fue,
1026 pay_balance_types pbt,
1027 pay_defined_balances pdb,
1028 pay_balance_dimensions pbd
1029 where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_MONTH'
1030 and fue.legislation_code = 'SG'
1031 and pbt.legislation_code = 'SG'
1032 and pbd.legislation_code = pbt.legislation_code
1033 and pdb.legislation_code = pbt.legislation_code
1034 and pbt.balance_name in ('Employee CPF Contributions Additional Earnings',
1035 'Employee CPF Contributions Ordinary Earnings',
1036 'Employer CPF Contributions Additional Earnings',
1037 'Employer CPF Contributions Ordinary Earnings',
1038 'Additional Earnings','Ordinary Earnings',
1039 'Employer Vol CPF Contributions Ordinary Earnings',
1040 'Employee Vol CPF Contributions Ordinary Earnings',
1041 'Employer Vol CPF Contributions Additional Earnings',
1042 'Employee Vol CPF Contributions Additional Earnings',
1043 'IR8S_MOA_403','IR8S_MOA_407','CPF Liability' )
1044 and pbt.balance_type_id = pdb.balance_type_id
1045 and pbd.balance_dimension_id = pdb.balance_dimension_id
1046 and pbd.dimension_name = '_PER_LE_MONTH';
1047 --
1048 ---------------------------------------------------------------------------------------------------
1049 -- Balance Store Record
1050 ---------------------------------------------------------------------------------------------------
1051 --
1052 type ytd_balance_store_rec is record
1053 ( balance_id ff_user_entities.user_entity_id%type,
1054 balance_value number );
1055 type ytd_balance_tab is table of ytd_balance_store_rec index by binary_integer;
1056 ytd_balance_rec_ir8a ytd_balance_tab;
1057 ytd_balance_rec_ir8s ytd_balance_tab;
1058 --
1059 type mtd_balance_store_rec is record
1060 ( balance_id ff_user_entities.user_entity_id%type,
1061 balance_value number,
1062 date_earned varchar2(6),
1063 date_earned_archive varchar2(30),
1064 person_id number,
1065 archive_status varchar2(1) );
1066 type mtd_balance_tab is table of mtd_balance_store_rec index by binary_integer;
1067 mtd_balance_rec mtd_balance_tab;
1068 ---------------------------------------------------------------------------------------------------
1069 -- Bug 3064282 Batch Balance fetch implemented
1070 ---------------------------------------------------------------------------------------------------
1071 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1072 g_context_tab pay_balance_pkg.t_context_tab;
1073 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
1074
1075 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1076 g_detailed_bal_out_tab1 pay_balance_pkg.t_detailed_bal_out_tab;
1077
1078 g_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
1079 g_detailed_bal_out_tab2 pay_balance_pkg.t_detailed_bal_out_tab;
1080 g_balance_value_tab3 pay_balance_pkg.t_balance_value_tab;
1081 g_detailed_bal_out_tab3 pay_balance_pkg.t_detailed_bal_out_tab;
1082 g_balance_value_tab4 pay_balance_pkg.t_balance_value_tab;
1083 g_detailed_bal_out_tab4 pay_balance_pkg.t_detailed_bal_out_tab;
1084 ---------------------------------------------------------------------------------------------------
1085 -- Type to store the person ids with same national_identifier (Bug 2649107)
1086 ---------------------------------------------------------------------------------------------------
1087 type person_id_store_rec is record
1088 ( person_id per_all_people_f.person_id%type );
1089 type person_id_tab is table of person_id_store_rec index by binary_integer;
1090 person_id_rec person_id_tab;
1091 ---------------------------------------------------------------------------------------------------
1092 -- Type to store the months on which payroll is run for a perticular person id
1093 -- Bug: 3205321- Modifed the type of month variable to number. Deleted the cursor which uses the
1094 -- lookup MONTH_CODE.
1095 ---------------------------------------------------------------------------------------------------
1096 type month_store_rec is record
1097 ( month number );
1098 type month_store_tab is table of month_store_rec index by binary_integer;
1099 month_recs month_store_tab;
1100 ---------------------------------------------------------------------------------------------------
1101 -- Local Variables
1102 ---------------------------------------------------------------------------------------------------
1103 l_payroll_mon_counter number;
1104 l_pmon_counter boolean;
1105 month_year_action_sequence_rec month_year_action_sequence%rowtype;
1106 month_year_action_rec month_year_action%rowtype;
1107 per_le_ytd_bal number;
1108 per_le_mtd_bal number;
1109 l_person_id per_all_people_f.person_id%type;
1110 l_ytd_counter_ir8a number;
1111 l_ytd_counter_ir8s number;
1112 l_mon_counter number;
1113 counter number;
1114 icounter number;
1115 l_counter number;
1116 duplicate_exists varchar2(1);
1117 l_mtd_counter number;
1118 l_arch_counter number;
1119 l_asac_cont_id number;
1120 l_tax_cont_id number;
1121 l_date_cont_id number;
1122 l_temp_value VARCHAR2(2000);
1123 l_name_ue VARCHAR2(2000);
1124 l_assignment_id number;
1125 a8a_counter number;
1126
1127 ---------------------------------------------------------------------------------------------------
1128 begin
1129 l_payroll_mon_counter := 1;
1130 l_pmon_counter := false;
1131 l_ytd_counter_ir8a := 1;
1132 l_ytd_counter_ir8s := 1;
1133 l_mon_counter := 1;
1134 l_counter := 1;
1135 duplicate_exists := 'N';
1136 l_arch_counter := 1;
1137 --
1138 if g_debug then
1139 hr_utility.set_location('pysgiraa: Start of archive_balances',10);
1140 end if;
1141 ------------------------------------------------------------------------------------------------
1142 -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
1143 -- in employee_if_latest( ) function
1144 ------------------------------------------------------------------------------------------------
1145 if g_person_id_tab.count > 1 then
1146 for l_person_id in g_person_id_tab.first..g_person_id_tab.last
1147 loop
1148 person_id_rec(l_counter).person_id := g_person_id_tab(l_person_id);
1149 l_counter := l_counter+1;
1150 end loop;
1151 --
1152 duplicate_exists := 'Y';
1153 end if;
1154 --
1155 t_archive_items.delete;
1156 t_user_entity_id.delete;
1157 t_archive_value.delete;
1158 t_date_earned.delete;
1159 ------------------------------------------------------------------------------------------------
1160 -- Populate with the only one person_id if the employee is not
1161 -- duplicated(Bug 2849107)
1162 ------------------------------------------------------------------------------------------------
1163 if duplicate_exists = 'N' then
1164 person_id_rec(l_counter).person_id := p_person_id;
1165 end if;
1166 ------------------------------------------------------------------------------------------------
1167 -- 2556026 Used pl/sql table to store the month_balances values.
1168 -- now month_balances will get executed only once
1169 ------------------------------------------------------------------------------------------------
1170 hr_utility.set_location('pysgiraa: archive_balances ',1110);
1171 if t_month_balanceid_store.count = 0 then
1172 open month_balances;
1173 loop
1174 fetch month_balances into t_month_balanceid_store(l_mon_counter).user_entity_id,
1175 t_month_balanceid_store(l_mon_counter).defined_balance_id;
1176 l_mon_counter := l_mon_counter + 1;
1177 exit when month_balances%NOTFOUND;
1178 end loop;
1179 close month_balances;
1180 end if;
1181 hr_utility.set_location('pysgiraa: archive_balances ',1120);
1182 ------------------------------------------------------------------------------------------------
1183 -- 2556026 Used pl/sql table to store the ytd_balances values.
1184 -- Now ytd_balances will get executed only once
1185 ------------------------------------------------------------------------------------------------
1186 if t_ytd_balanceid_store_ir8a.count = 0 then
1187 open ytd_balances_ir8a;
1188 loop
1189 fetch ytd_balances_ir8a into t_ytd_balanceid_store_ir8a(l_ytd_counter_ir8a).user_entity_id,
1190 t_ytd_balanceid_store_ir8a(l_ytd_counter_ir8a).defined_balance_id;
1191 l_ytd_counter_ir8a := l_ytd_counter_ir8a + 1;
1192 exit when ytd_balances_ir8a%NOTFOUND;
1193 end loop;
1194 close ytd_balances_ir8a;
1195 end if;
1196 hr_utility.set_location('pysgiraa: archive_balances ',1130);
1197 if t_ytd_balanceid_store_ir8s.count = 0 then
1198 open ytd_balances_ir8s;
1199 loop
1200 fetch ytd_balances_ir8s into t_ytd_balanceid_store_ir8s(l_ytd_counter_ir8s).user_entity_id,
1201 t_ytd_balanceid_store_ir8s(l_ytd_counter_ir8s).defined_balance_id;
1202 l_ytd_counter_ir8s := l_ytd_counter_ir8s + 1;
1203 exit when ytd_balances_ir8s%NOTFOUND;
1204 end loop;
1205 close ytd_balances_ir8s;
1206 end if;
1207 ------------------------------------------------------------------------------------------------
1208 -- Bug# 3501927
1209 ------------------------------------------------------------------------------------------------
1210 ytd_a8a_balance_rec.delete;
1211 ------------------------------------------------------------------------------------------------
1212 -- Bug 2629839 : Monthly balances are archived first and then the max assignment
1213 -- action id returned from the month_year_action cursor is used for archiving
1214 -- year balances
1215 ------------------------------------------------------------------------------------------------
1216 if person_id_rec.count > 0 then
1217 for l_person_counter in 1..person_id_rec.last
1218 loop
1219 hr_utility.set_location('pysgiraa: archive_balances ',1140);
1220 if person_id_rec.exists(l_person_counter) then
1221 open month_year_action_sequence( person_id_rec(l_person_counter).person_id,
1222 p_business_group_id,
1223 p_tax_unit_id,
1224 p_basis_year );
1225 loop
1226 fetch month_year_action_sequence into month_year_action_sequence_rec;
1227 exit when month_year_action_sequence%notfound;
1228 --
1229 open month_year_action( person_id_rec(l_person_counter).person_id,
1230 p_business_group_id,
1231 p_tax_unit_id,
1232 p_basis_year,
1233 month_year_action_sequence_rec.act_seq );
1234 --
1235 fetch month_year_action into month_year_action_rec;
1236 if month_year_action%found then
1237 ----------------------------------------------------------------------------------
1238 -- Start Bug 3038605 - Store the months which have payroll runs.
1239 -- Bug: 3205321 - Store Month in MM format in month_recs
1240 ----------------------------------------------------------------------------------
1241 month_recs(l_payroll_mon_counter).month := to_number(to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MM'));
1242 l_payroll_mon_counter := l_payroll_mon_counter+1;
1243 ----------------------------------------------------------------------------------
1244 -- Bulk Balance Fetch for Bug 3064282
1245 ----------------------------------------------------------------------------------
1246 g_balance_value_tab.delete;
1247 g_context_tab.delete;
1248 g_detailed_bal_out_tab.delete;
1249 --
1250 hr_utility.set_location('pysgiraa: archive_balances ',1150);
1251 for counter in 1..t_month_balanceid_store.count
1252 loop
1253 g_balance_value_tab(counter).defined_balance_id := t_month_balanceid_store(counter).defined_balance_id;
1254 g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1255 end loop;
1256 ----------------------------------------------------------------------------------
1257 -- Bug 3223822 - Modified call to the function pay_balance_pkg.get_value
1258 ----------------------------------------------------------------------------------
1259 pay_balance_pkg.get_value( month_year_action_rec.assact_id,
1260 g_balance_value_tab,
1261 g_context_tab,
1262 false,
1263 false,
1264 g_detailed_bal_out_tab );
1265 --
1266 hr_utility.set_location('pysgiraa: archive_balances ',1160);
1267 if duplicate_exists = 'N' then /* Bug 3162955 */
1268 for counter in 1..t_month_balanceid_store.count
1269 loop
1270 if t_month_balanceid_store.exists(counter) then
1271 t_user_entity_id(l_arch_counter) := t_month_balanceid_store(counter).user_entity_id;
1272 t_archive_value(l_arch_counter) := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1273 t_date_earned(l_arch_counter) := month_year_action_rec.date_earned;
1274 l_arch_counter := l_arch_counter + 1;
1275 end if;
1276 end loop;
1277 else
1278 --------------------------------------------------------------------------
1279 -- Bug 3162955 - In case of Rechire with new employee number
1280 -- store the employee details in mtd_balance_rec table without archiving.
1281 --------------------------------------------------------------------------
1282 l_mtd_counter := mtd_balance_rec.count + 1;
1283 for counter in 1..t_month_balanceid_store.count
1284 loop
1285 mtd_balance_rec(l_mtd_counter).balance_id := t_month_balanceid_store(counter).user_entity_id;
1286 mtd_balance_rec(l_mtd_counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1287 mtd_balance_rec(l_mtd_counter).date_earned := to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MMYYYY');
1288 mtd_balance_rec(l_mtd_counter).date_earned_archive := month_year_action_rec.date_earned;
1289 mtd_balance_rec(l_mtd_counter).person_id := person_id_rec(l_person_counter).person_id;
1290 mtd_balance_rec(l_mtd_counter).archive_status := 'Y';
1291 l_mtd_counter := l_mtd_counter + 1;
1292 end loop;
1293 end if;
1294 end if;
1295 close month_year_action;
1296 end loop;
1297 hr_utility.set_location('pysgiraa: archive_balances ',1170);
1298 --
1299 close month_year_action_sequence;
1300 ----------------------------------------------------------------------------------
1301 -- Bulk Balance Fetch for Bug 3064282
1302 ----------------------------------------------------------------------------------
1303 g_balance_value_tab.delete;
1304 g_context_tab.delete;
1305 g_detailed_bal_out_tab.delete;
1306 hr_utility.set_location('pysgiraa: archive_balances ',1180);
1307 --
1308 for counter in 1..t_ytd_balanceid_store_ir8a.count
1309 loop
1310 g_balance_value_tab(counter).defined_balance_id := t_ytd_balanceid_store_ir8a(counter).defined_balance_id;
1311 g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1312 end loop;
1313 hr_utility.set_location('pysgiraa: archive_balances ',1190);
1314 ----------------------------------------------------------------------------------
1315 -- Bug 3223822 - Modified call to the function pay_balance_pkg.get_value
1316 -- Bug 3430277 - Put a condition before function pay_balance_pkg.get_value call.
1317 ----------------------------------------------------------------------------------
1318 if month_year_action_rec.assact_id is not null then
1319 pay_balance_pkg.get_value( month_year_action_rec.assact_id,
1320 g_balance_value_tab,
1321 g_context_tab,
1322 false,
1323 false,
1324 g_detailed_bal_out_tab );
1325 end if;
1326
1327 ----------------------------------------------------------------------------------
1328 -- Bug 3249043 - v_run_ass_action_id is initialized to latest persion assact_id
1329 -- Assign here so cursor variable can be accessed outside of loop
1330 -- Bug# 3328760 - Added g_detailed_bal_out_tab.exists(counter) check
1331 ----------------------------------------------------------------------------------
1332 hr_utility.set_location('pysgiraa: archive_balances ',1200);
1333 for counter in 1..t_ytd_balanceid_store_ir8a.count
1334 loop
1335 if l_person_counter = 1 then
1336 if g_detailed_bal_out_tab.exists(counter) then
1337 ytd_balance_rec_ir8a(counter).balance_id := t_ytd_balanceid_store_ir8a(counter).user_entity_id;
1338 ytd_balance_rec_ir8a(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0) ;
1339 v_run_ass_action_id := month_year_action_rec.assact_id;
1340 end if;
1341 else
1342 if g_detailed_bal_out_tab.exists(counter) then
1343 if ytd_balance_rec_ir8a.exists(counter) then
1344 ytd_balance_rec_ir8a(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0)
1345 + ytd_balance_rec_ir8a(counter).balance_value;
1346 end if;
1347 end if;
1348 end if;
1349 end loop;
1350 hr_utility.set_location('pysgiraa: archive_balances ',1210);
1351 g_balance_value_tab.delete;
1352 g_context_tab.delete;
1353 g_detailed_bal_out_tab.delete;
1354
1355 for counter in 1..t_ytd_balanceid_store_ir8s.count
1356 loop
1357 g_balance_value_tab(counter).defined_balance_id := t_ytd_balanceid_store_ir8s(counter).defined_balance_id;
1358 g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1359 end loop;
1360
1361 ----------------------------------------------------------------------------------
1362 -- Bug 3223822 - Modified call to the function pay_balance_pkg.get_value
1363 -- Bug 3430277 - Put a condition before function pay_balance_pkg.get_value call.
1364 ----------------------------------------------------------------------------------
1365 if month_year_action_rec.assact_id is not null then
1366 pay_balance_pkg.get_value( month_year_action_rec.assact_id,
1367 g_balance_value_tab,
1368 g_context_tab,
1369 false,
1370 false,
1371 g_detailed_bal_out_tab );
1372 end if;
1373
1374 ----------------------------------------------------------------------------------
1375 -- Bug 3249043 - v_run_ass_action_id is initialized to latest persion assact_id
1376 -- Assign here so cursor variable can be accessed outside of loop
1377 -- Bug# 3328760 - Added g_detailed_bal_out_tab.exists(counter) check
1378 ----------------------------------------------------------------------------------
1379 hr_utility.set_location('pysgiraa: archive_balances ',1220);
1380 for counter in 1..t_ytd_balanceid_store_ir8s.count
1381 loop
1382 if l_person_counter = 1 then
1383 if g_detailed_bal_out_tab.exists(counter) then
1384 ytd_balance_rec_ir8s(counter).balance_id := t_ytd_balanceid_store_ir8s(counter).user_entity_id;
1385 ytd_balance_rec_ir8s(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0) ;
1386 v_run_ass_action_id := month_year_action_rec.assact_id;
1387 end if;
1388 else
1389 if g_detailed_bal_out_tab.exists(counter) then
1390 if ytd_balance_rec_ir8s.exists(counter) then
1391 ytd_balance_rec_ir8s(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0)
1392 + ytd_balance_rec_ir8s(counter).balance_value;
1393 end if;
1394 end if;
1395 end if;
1396 end loop;
1397 hr_utility.set_location('pysgiraa: archive_balances ',1230);
1398 --
1399 --
1400 --
1401 ------------------------------------------------------------------------------------
1402 -- Bug#3501927 A8A usablity
1403 -- Bug#3933332 Added one more flag g_org_a8a_flag to check if a8a is applicable.
1404 -------------------------------------------------------------------------------------
1405 if month_year_action_rec.assact_id is NOT NULL and g_org_a8a_flag ='Y' then
1406 a8a_balances_value( person_id_rec(l_person_counter).person_id,
1407 month_year_action_rec.assact_id,
1408 month_year_action_rec.tax_uid,
1409 l_person_counter );
1410 end if;
1411 --
1412 g_balance_value_tab.delete;
1413 g_detailed_bal_out_tab.delete;
1414 end if;
1415 ------------------------------------------------------------------------------------------------
1416 -- Bug# 2858074 - Remove the values in the cursor variables and assign the variables to NULL
1417 -- so that these variables will be populated with values in the next loop
1418 -- Bug# 3328760 - These conditions are moved inside loop.
1419 ------------------------------------------------------------------------------------------------
1420 month_year_action_sequence_rec.act_seq := null;
1421 month_year_action_rec.assact_id := null;
1422 --
1423 hr_utility.set_location('pysgiraa: archive_balances ',1240);
1424 end loop ;
1425 end if;
1426 ------------------------------------------------------------------------------------------------
1427 -- Bug 3162955 Month Balance Implementation
1428 -- Bug 3162955 - Check whether multiple runs in a month exists for the OCBC rehired employee,
1429 -- If there exists multiple runs then sum the balances for the month
1430 -- and then archive the month details only once
1431 ------------------------------------------------------------------------------------------------
1432 if duplicate_exists = 'Y' then
1433 for counter in 1 .. mtd_balance_rec.count
1434 loop
1435 for icounter in 1 .. mtd_balance_rec.count
1436 loop
1437 if mtd_balance_rec(counter).balance_id = mtd_balance_rec(icounter).balance_id and
1438 mtd_balance_rec(counter).date_earned = mtd_balance_rec(icounter).date_earned and
1439 mtd_balance_rec(counter).person_id <> mtd_balance_rec(icounter).person_id and
1440 mtd_balance_rec(counter).archive_status = 'Y' then
1441 mtd_balance_rec(counter).balance_value := mtd_balance_rec(counter).balance_value
1442 + mtd_balance_rec(icounter).balance_value;
1443 mtd_balance_rec(icounter).archive_status := 'N';
1444 end if;
1445 end loop;
1446 end loop;
1447 --
1448 if t_user_entity_id.count >= 0 then
1449 l_arch_counter := t_user_entity_id.count + 1;
1450 else
1451 l_arch_counter := 1;
1452 end if;
1453 --
1454 for counter in 1 .. mtd_balance_rec.count
1455 loop
1456 if mtd_balance_rec(counter).archive_status = 'Y' then
1457 t_user_entity_id(l_arch_counter) := mtd_balance_rec(counter).balance_id;
1458 t_archive_value(l_arch_counter) := nvl(mtd_balance_rec(counter).balance_value,0);
1459 t_date_earned(l_arch_counter) := mtd_balance_rec(counter).date_earned_archive;
1460 l_arch_counter := l_arch_counter + 1;
1461 end if;
1462 end loop;
1463 end if;
1464
1465 hr_utility.set_location('pysgiraa:archive_balances ',1250);
1466 ------------------------------------------------------------------------------------------------
1467 -- Bug 3038605 - Added the following code to archive balances with 0 values for months with no payroll runs
1468 -- Logic Used:
1469 -- Search the pl/sql table month_recs to see if the specified month is already archived.
1470 -- a) If not archived then archive months details with 0 amounts.
1471 -- b) Else reset the flag l_pmon_counter and search for next months
1472 ------------------------------------------------------------------------------------------------
1473 for i in 1..12
1474 loop
1475 ----------------------------------------------------------------------------------------------
1476 -- Search if specified months is already archived
1477 -- Bug 3205321 - Compare month with variable i instead of MON format from lookup MONTH_CODE.
1478 ----------------------------------------------------------------------------------------------
1479 for j in 1..l_payroll_mon_counter-1
1480 loop
1481 if month_recs(j).month = i then
1482 l_pmon_counter := true;
1483 end if;
1484 end loop;
1485 --
1486 if l_pmon_counter = false then
1487 -------------------------------------------------------------------------------------------
1488 -- Archive 0 balance amounts as there are no runs in this perticular month
1489 -------------------------------------------------------------------------------------------
1490 if t_user_entity_id.count >= 0 then
1491 l_arch_counter := t_user_entity_id.count + 1;
1492 else
1493 l_arch_counter := 1;
1494 end if;
1495 --
1496 per_le_mtd_bal := 0;
1497 --
1498 for counter in 1..t_month_balanceid_store.count
1499 loop
1500 if t_month_balanceid_store.exists(counter) then
1501 t_user_entity_id(l_arch_counter) := t_month_balanceid_store(counter).user_entity_id;
1502 t_archive_value(l_arch_counter) := per_le_mtd_bal;
1503 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');
1504 l_arch_counter := l_arch_counter + 1;
1505 end if;
1506 end loop;
1507 else
1508 l_pmon_counter := false;
1509 end if;
1510 end loop;
1511
1512 hr_utility.set_location('pysgiraa: archive_balances ',15);
1513 ------------------------------------------------------------------------------------------------
1514 -- Bug: 3260855 Bulk Insert into ff_archive_items for month balances
1515 ------------------------------------------------------------------------------------------------
1516 select context_id
1517 into l_asac_cont_id
1518 from ff_contexts
1519 where context_name = 'ASSIGNMENT_ACTION_ID' ;
1520 --
1521 select context_id
1522 into l_tax_cont_id
1523 from ff_contexts
1524 where context_name = 'TAX_UNIT_ID' ;
1525 --
1526 select context_id
1527 into l_date_cont_id
1528 from ff_contexts
1529 where context_name = 'DATE_EARNED' ;
1530 --
1531
1532 -- t_user_entity_id Loop start
1533 FOR i IN 1..t_user_entity_id.count LOOP
1534
1535 select user_entity_name
1536 into l_name_ue
1537 from ff_user_entities where user_entity_id = t_user_entity_id(i);
1538
1539 SELECT assignment_id
1540 INTO l_assignment_id
1541 FROM pay_assignment_Actions paa
1542 WHERE paa.assignment_action_id = p_assignment_action_id;
1543
1544 /* past original and amendment */
1545
1546
1547
1548 BEGIN
1549
1550 l_temp_value :='';
1551 SELECT nvl(sum(value),0)
1552 INTO l_temp_value
1553 FROM ff_archive_items arch
1554 WHERE arch.user_entity_id = t_user_entity_id(i)
1555 AND arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1556 from pay_payroll_actions ppa,
1557 pay_assignment_actions paa
1558 where ppa.payroll_action_id in (SELECT org_information2
1559 FROM hr_organization_information
1560 WHERE org_information_context = 'SG_IRAS_DETAILS'
1561 AND organization_id = g_legal_entity_id
1562 AND org_information1 = g_basis_year)
1563 and ppa.payroll_action_id = paa.payroll_action_id
1564 and paa.assignment_id = l_assignment_id)--ORGLEVELPREVSUMITTEDvalues
1565 AND EXISTS (SELECT 1
1566 FROM ff_archive_item_contexts con1
1567 WHERE con1.archive_item_id = arch.archive_item_id
1568 AND con1.context = p_tax_unit_id
1569 AND con1.sequence_no =2)
1570 AND EXISTS (SELECT 1
1571 FROM ff_archive_item_contexts con2
1572 WHERE con2.archive_item_id = arch.archive_item_id
1573 AND fnd_date.canonical_to_date(con2.context) = fnd_date.canonical_to_date(t_date_earned(i))
1574 AND con2.sequence_no =3);
1575 EXCEPTION
1576 WHEN NO_DATA_FOUND
1577 THEN NULL;
1578 END;
1579
1580 t_amend_value(i) := l_temp_value;
1581 END LOOP;
1582 hr_utility.set_location('pysgiraa: archive_balances ',20);
1583 g_amend_ir8s_m_flag :='N';
1584
1585
1586 FOR I IN 1..t_user_entity_id.count LOOP
1587 if (t_archive_value.exists(i) and t_amend_value.exists(i)) then
1588 if (t_archive_value(i) <> t_amend_value(i)) THEN
1589 g_amend_ir8s_m_flag :='Y';
1590 exit;
1591 end if;
1592 else
1593 g_amend_ir8s_m_flag :='Y';
1594 exit;
1595 end if;
1596 end loop;
1597
1598 /* need to archive t_amend_value(i) - t_archive_value(i) .
1599 handle when no data exists, ensure only numbers.
1600 Special handling for fields that dont allow negative*/
1601
1602 hr_utility.set_location('pysgiraa: archive_balances ',30);
1603
1604 l_arch_counter := 1;
1605 --
1606
1607 for counter in 1..ytd_balance_rec_ir8s.count
1608 loop
1609 if ytd_balance_rec_ir8s.exists(counter) then
1610 t_user_entity_id_ir8s(l_arch_counter) := ytd_balance_rec_ir8s(counter).balance_id;
1611 t_archive_value_ir8s(l_arch_counter) := ytd_balance_rec_ir8s(counter).balance_value;
1612 l_arch_counter := l_arch_counter + 1;
1613 end if;
1614 end loop;
1615
1616 l_arch_counter := 1;
1617 for counter in 1..ytd_balance_rec_ir8a.count
1618 loop
1619 if ytd_balance_rec_ir8a.exists(counter) then
1620 t_user_entity_id_ir8a(l_arch_counter) := ytd_balance_rec_ir8a(counter).balance_id;
1621 t_archive_value_ir8a(l_arch_counter) := ytd_balance_rec_ir8a(counter).balance_value;
1622 l_arch_counter := l_arch_counter + 1;
1623 end if;
1624 end loop;
1625 hr_utility.set_location('pysgiraa: archive_balances ',40);
1626 ---------------------------------------------------------------------------------------------------
1627 -- Bug# 3501927 A8A_USABLITY
1628 ---------------------------------------------------------------------------------------------------
1629 --Bug#3933332
1630 l_arch_counter :=1;
1631
1632 if g_org_a8a_flag ='Y' then-- AND A8A ARCHIVE_ITEMS ARE PRESENT ARE IN ORIGINAL ARCHIVE RUN
1633 --
1634 for counter in 1..ytd_a8a_balance_rec.count
1635 loop
1636 if ytd_a8a_balance_rec.exists(counter) then
1637 t_user_entity_id_a8a(l_arch_counter) := ytd_a8a_balance_rec(counter).balance_id;
1638 t_archive_value_a8a(l_arch_counter) := ytd_a8a_balance_rec(counter).balance_value;
1639 l_arch_counter := l_arch_counter + 1;
1640 end if;
1641 end loop;
1642 --
1643 end if;
1644 hr_utility.set_location('pysgiraa: archive_balances ',50);
1645
1646 FOR counter IN 1..t_user_entity_id_ir8a.COUNT LOOP
1647
1648 select user_entity_name
1649 into l_name_ue
1650 from ff_user_entities
1651 where user_entity_id = t_user_entity_id_ir8a(counter);
1652
1653 begin
1654 l_temp_value :='';
1655 select sum(value)
1656 into l_temp_value
1657 from ff_archive_items arch
1658 where arch.user_entity_id = t_user_entity_id_ir8a(counter)
1659 and arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1660 from pay_payroll_actions ppa,
1661 pay_assignment_actions paa
1662 where ppa.payroll_action_id in (SELECT org_information2
1663 FROM hr_organization_information
1664 WHERE org_information_context = 'SG_IRAS_DETAILS'
1665 AND organization_id = g_legal_entity_id
1666 AND org_information1 = g_basis_year)
1667 and ppa.payroll_action_id = paa.payroll_action_id
1668 and paa.assignment_id = l_assignment_id)
1669 and exists (select 1
1670 from ff_archive_item_contexts con1
1671 where con1.archive_item_id = arch.archive_item_id
1672 and con1.context = p_tax_unit_id
1673 and con1.sequence_no =2);
1674
1675 exception
1676 WHEN NO_DATA_FOUND THEN
1677 NULL;
1678 end;
1679
1680 t_amend_value_ir8a(counter) := l_temp_value;
1681
1682
1683 END LOOP;
1684 hr_utility.set_location('pysgiraa: archive_balances ',60);
1685 g_amend_ir8a_flag := 'N';
1686
1687 for counter in 1..t_user_entity_id_ir8a.count
1688 loop
1689 if(t_archive_value_ir8a.exists(counter) and t_amend_value_ir8a.exists(counter)) then
1690 if (t_archive_value_ir8a(counter) <> t_amend_value_ir8a(counter)) THEN
1691 g_amend_ir8a_flag :='Y';
1692 exit;
1693 end if;
1694 else
1695 g_amend_ir8a_flag :='Y';
1696 exit;
1697 end if;
1698
1699 end loop;
1700
1701 hr_utility.set_location('pysgiraa: archive_balances ',70);
1702
1703 FOR counter IN 1..t_user_entity_id_ir8s.COUNT LOOP
1704
1705 select user_entity_name
1706 into l_name_ue
1707 from ff_user_entities
1708 where user_entity_id = t_user_entity_id_ir8s(counter);
1709
1710 begin
1711 l_temp_value :='';
1712 select sum(value)
1713 into l_temp_value
1714 from ff_archive_items arch
1715 where arch.user_entity_id = t_user_entity_id_ir8s(counter)
1716 and arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1717 from pay_payroll_actions ppa,
1718 pay_assignment_actions paa
1719 where ppa.payroll_action_id in (SELECT org_information2
1720 FROM hr_organization_information
1721 WHERE org_information_context = 'SG_IRAS_DETAILS'
1722 AND organization_id = g_legal_entity_id
1723 AND org_information1 = g_basis_year)
1724 and ppa.payroll_action_id = paa.payroll_action_id
1725 and paa.assignment_id = l_assignment_id)
1726 and exists (select 1
1727 from ff_archive_item_contexts con1
1728 where con1.archive_item_id = arch.archive_item_id
1729 and con1.context = p_tax_unit_id
1730 and con1.sequence_no =2);
1731
1732 exception
1733 WHEN NO_DATA_FOUND THEN
1734 NULL;
1735 end;
1736
1737 t_amend_value_ir8s(counter) := l_temp_value;
1738
1739 END LOOP;
1740 hr_utility.set_location('pysgiraa: archive_balances ',80);
1741 g_amend_ir8a_flag := 'N';
1742
1743 for counter in 1..t_user_entity_id_ir8a.count
1744 loop
1745 if(t_archive_value_ir8a.exists(counter) and t_amend_value_ir8a.exists(counter)) then
1746 if (t_archive_value_ir8a(counter) <> t_amend_value_ir8a(counter)) THEN
1747 g_amend_ir8a_flag :='Y';
1748 exit;
1749 end if;
1750 else
1751 g_amend_ir8a_flag :='Y';
1752 exit;
1753 end if;
1754
1755 end loop;
1756
1757 hr_utility.set_location('pysgiraa: archive_balances ',90);
1758 g_amend_ir8s_flag := 'N';
1759
1760 for counter in 1..t_user_entity_id_ir8s.count
1761 loop
1762 if(t_archive_value_ir8s.exists(counter) and t_amend_value_ir8s.exists(counter)) then
1763 if (t_archive_value_ir8s(counter) <> t_amend_value_ir8s(counter)) THEN
1764 g_amend_ir8s_flag :='Y';
1765 exit;
1766 end if;
1767 else
1768 g_amend_ir8s_flag :='Y';
1769 exit;
1770 end if;
1771
1772 end loop;
1773
1774 hr_utility.set_location('pysgiraa: archive_balances ',100);
1775 FOR i in 1..t_user_entity_id_a8a.count LOOP
1776
1777 select user_entity_name
1778 into l_name_ue
1779 from ff_user_entities
1780 where user_entity_id = t_user_entity_id_a8a(i);
1781
1782 begin
1783 l_temp_value :='';
1784 select sum(value)
1785 into l_temp_value
1786 from ff_archive_items arch
1787 where arch.user_entity_id = t_user_entity_id_a8a(i)
1788 and arch.context1 IN( select paa.assignment_action_id
1789 -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1790 from pay_payroll_actions ppa,
1791 pay_assignment_actions paa
1792 where ppa.payroll_action_id in (SELECT org_information2
1793 FROM hr_organization_information
1794 WHERE org_information_context = 'SG_IRAS_DETAILS'
1795 AND organization_id = g_legal_entity_id
1796 AND org_information1 = g_basis_year)
1797 and ppa.payroll_action_id = paa.payroll_action_id
1798 and paa.assignment_id = l_assignment_id)
1799 and exists (select 1
1800 from ff_archive_item_contexts con1
1801 where con1.archive_item_id = arch.archive_item_id
1802 and con1.context = p_tax_unit_id
1803 and con1.sequence_no =2);
1804
1805 exception
1806 WHEN NO_DATA_FOUND THEN
1807 NULL;
1808 end;
1809
1810 t_amend_value_a8a(i) := l_temp_value;
1811
1812 END LOOP;
1813 hr_utility.set_location('pysgiraa: archive_balances ',110);
1814
1815 g_amend_a8a_flag := 'N';
1816 -- ARCHIVE ITEMS
1817
1818 for counter in 1..t_user_entity_id_a8a.COUNT
1819 loop
1820 if (t_archive_value_A8A.exists(counter) and t_amend_value_A8A.exists(counter)) then
1821 if (t_archive_value_A8A(counter) <> t_amend_value_A8A(counter)) THEN
1822 g_amend_a8a_flag :='Y';
1823 exit;
1824 end if;
1825
1826 else
1827 g_amend_a8a_flag :='Y';
1828 exit;
1829 end if;
1830 end loop;
1831
1832 hr_utility.set_location('pysgiraa: archive_balances ',120);
1833
1834 if g_amend_ir8s_c_flag = 'Y' or g_amend_ir8s_m_flag = 'Y' then
1835 g_amend_ir8s_flag := 'Y';
1836 end if;
1837
1838 if(g_amend_ir8a_flag='Y' or g_amend_a8a_flag='Y' or g_amend_ir8s_flag='Y' or g_amend_a8b_flag = 'Y') then
1839
1840 archive_item ('X_IRAS_METHOD', p_assignment_action_id, 'A');
1841
1842 select ue.user_entity_id
1843 bulk collect into t_amend_ue_id
1844 from ff_user_entities ue
1845 where ue.user_entity_name in ('X_IR8A_AMEND_INDICATOR',
1846 'X_A8B_AMEND_INDICATOR',
1847 'X_IR8S_AMEND_INDICATOR',
1848 'X_A8A_AMEND_INDICATOR')
1849 order by ue.user_entity_name;
1850
1851 t_flag(1) := g_amend_a8a_flag;
1852 t_flag(2) := g_amend_a8b_flag;
1853 t_flag(3) := g_amend_ir8a_flag;
1854 t_flag(4) := g_amend_ir8s_flag;
1855
1856 forall counter in 1..t_amend_ue_id.count
1857 insert into ff_archive_items
1858 ( archive_item_id,
1859 user_entity_id,
1860 context1,
1861 value,
1862 archive_type )
1863 values
1864 ( ff_archive_items_s.nextval,
1865 t_amend_ue_id(counter),
1866 p_assignment_action_id,
1867 t_flag(counter),
1868 'AAP' )
1869 returning archive_item_id bulk collect into t_archive_items ;
1870
1871 forall counter in t_archive_items.first..t_archive_items.last
1872 insert into ff_archive_item_contexts
1873 ( archive_item_id,
1874 sequence_no,
1875 context,
1876 context_id )
1877 values
1878 ( t_archive_items(counter),
1879 1,
1880 p_assignment_action_id,
1881 l_asac_cont_id );
1882
1883
1884 t_archive_items.delete;
1885 forall counter in 1..t_user_entity_id.count
1886 insert into ff_archive_items
1887 ( archive_item_id,
1888 user_entity_id,
1889 context1,
1890 value,
1891 archive_type )
1892 values
1893 ( ff_archive_items_s.nextval,
1894 t_user_entity_id(counter),
1895 p_assignment_action_id,
1896 t_archive_value(counter) - t_amend_value(counter) ,-- T_AMEND-VALUE,
1897 'AAP' )
1898 returning archive_item_id bulk collect into t_archive_items ;
1899
1900
1901 forall counter in t_archive_items.first..t_archive_items.last
1902 insert into ff_archive_item_contexts
1903 ( archive_item_id,
1904 sequence_no,
1905 context,
1906 context_id )
1907 values
1908 ( t_archive_items(counter),
1909 1,
1910 p_assignment_action_id,
1911 l_asac_cont_id );
1912 --
1913 forall counter in t_archive_items.first..t_archive_items.last
1914 insert into ff_archive_item_contexts
1915 ( archive_item_id,
1916 sequence_no,
1917 context,
1918 context_id )
1919 values
1920 ( t_archive_items(counter),
1921 2,
1922 p_tax_unit_id,
1923 l_tax_cont_id );
1924 --
1925 forall counter in t_archive_items.first..t_archive_items.last
1926 insert into ff_archive_item_contexts
1927 ( archive_item_id,
1928 sequence_no,
1929 context,
1930 context_id )
1931 values
1932 ( t_archive_items(counter),
1933 3,
1934 t_date_earned(counter),
1935 l_date_cont_id );
1936 --
1937 t_archive_items.delete;
1938
1939 hr_utility.set_location('pysgiraa: archive_balances ',130);
1940
1941 forall counter in 1..t_user_entity_id_ir8a.count
1942 insert into ff_archive_items
1943 ( archive_item_id,
1944 user_entity_id,
1945 context1,
1946 value,
1947 archive_type )
1948 values
1949 ( ff_archive_items_s.nextval,
1950 t_user_entity_id_ir8a(counter),
1951 p_assignment_action_id,
1952 t_archive_value_ir8a(counter) - t_amend_value_ir8a(counter),
1953 'AAP' )
1954 returning archive_item_id bulk collect into t_archive_items_ir8a ;
1955 --
1956
1957
1958
1959 forall counter in t_archive_items_ir8a.first..t_archive_items_ir8a.last
1960 insert into ff_archive_item_contexts
1961 ( archive_item_id,
1962 sequence_no,
1963 context,
1964 context_id )
1965 values
1966 ( t_archive_items_ir8a(counter),
1967 1,
1968 p_assignment_action_id,
1969 l_asac_cont_id );
1970 --
1971 forall counter in t_archive_items_ir8a.first..t_archive_items_ir8a.last
1972 insert into ff_archive_item_contexts
1973 ( archive_item_id,
1974 sequence_no,
1975 context,
1976 context_id )
1977 values
1978 ( t_archive_items_ir8a(counter) ,
1979 2,
1980 p_tax_unit_id,
1981 l_tax_cont_id );
1982 t_archive_items_ir8a.delete;
1983
1984 hr_utility.set_location('pysgiraa: archive_balances ',140);
1985 forall counter in 1..t_user_entity_id_ir8s.count
1986 insert into ff_archive_items
1987 ( archive_item_id,
1988 user_entity_id,
1989 context1,
1990 value,
1991 archive_type )
1992 values
1993 ( ff_archive_items_s.nextval,
1994 t_user_entity_id_ir8s(counter),
1995 p_assignment_action_id,
1996 t_archive_value_ir8s(counter) - t_amend_value_ir8s(counter) ,
1997 'AAP' )
1998 returning archive_item_id bulk collect into t_archive_items_ir8s ;
1999 --
2000
2001
2002
2003 forall counter in t_archive_items_ir8s.first..t_archive_items_ir8s.last
2004 insert into ff_archive_item_contexts
2005 ( archive_item_id,
2006 sequence_no,
2007 context,
2008 context_id )
2009 values
2010 ( t_archive_items_ir8s(counter),
2011 1,
2012 p_assignment_action_id,
2013 l_asac_cont_id );
2014 --
2015 forall counter in t_archive_items_ir8s.first..t_archive_items_ir8s.last
2016 insert into ff_archive_item_contexts
2017 ( archive_item_id,
2018 sequence_no,
2019 context,
2020 context_id )
2021 values
2022 ( t_archive_items_ir8s(counter),
2023 2,
2024 p_tax_unit_id,
2025 l_tax_cont_id );
2026 t_archive_items_ir8s.delete;
2027
2028 hr_utility.set_location('pysgiraa: archive_balances ',150);
2029
2030 forall counter in 1..t_user_entity_id_a8a.count
2031 insert into ff_archive_items
2032 ( archive_item_id,
2033 user_entity_id,
2034 context1,
2035 value,
2036 archive_type )
2037 values
2038 ( ff_archive_items_s.nextval,
2039 t_user_entity_id_a8a(counter),
2040 p_assignment_action_id,
2041 t_archive_value_a8a(counter) - t_amend_value_a8a(counter) ,
2042 'AAP' )
2043 returning archive_item_id bulk collect into t_archive_items_a8a ;
2044 --
2045
2046
2047
2048 forall counter in t_archive_items_a8a.first..t_archive_items_a8a.last
2049 insert into ff_archive_item_contexts
2050 ( archive_item_id,
2051 sequence_no,
2052 context,
2053 context_id )
2054 values
2055 ( t_archive_items_a8a(counter),
2056 1,
2057 p_assignment_action_id,
2058 l_asac_cont_id );
2059 --
2060 forall counter in t_archive_items_a8a.first..t_archive_items_a8a.last
2061 insert into ff_archive_item_contexts
2062 ( archive_item_id,
2063 sequence_no,
2064 context,
2065 context_id )
2066 values
2067 ( t_archive_items_a8a(counter),
2068 2,
2069 p_tax_unit_id,
2070 l_tax_cont_id );
2071 t_archive_items_a8a.delete;
2072 hr_utility.set_location('pysgiraa: archive_balances ',160);
2073
2074 select arch.value,ue.user_entity_id
2075 bulk collect into t_orig_value,t_orig_user_entity_id
2076 from ff_user_entities ue ,
2077 ff_archive_items arch
2078 where ue.user_entity_name in ('X_PER_NATIONAL_IDENTIFIER',
2079 'X_PER_SEX',
2080 'X_PER_DATE_OF_BIRTH',
2081 'X_PER_ADR_TYPE',
2082 'X_PER_ADR_COUNTRY_CODE',
2083 'X_PER_ADR_LINE_1',
2084 'X_PER_ADR_LINE_2',
2085 'X_PER_ADR_LINE_3',
2086 'X_PER_ADR_POSTAL_CODE',
2087 'X_PER_CQ_ADR_LINE_1',
2088 'X_PER_CQ_ADR_LINE_2',
2089 'X_PER_CQ_ADR_LINE_3',
2090 'X_PER_CQ_DATE_FROM',
2091 'X_PER_CQ_DATE_TO',
2092 'X_EMP_TERM_DATE',
2093 'X_EMP_HIRE_DATE',
2094 'X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME',
2095 'X_PEOPLE_FLEXFIELD_SG_SG_PP_COUNTRY',
2096 'X_PEOPLE_FLEXFIELD_SG_SG_PERMIT_TYPE',
2097 'X_PEOPLE_FLEXFIELD_SG_SG_INCOME_TAX_NUMBER',
2098 'X_PER_PERMIT_STATUS_INDICATOR'
2099 ,'X_PER_EE_PAYEE_ID_CHECK',
2100 'X_PEOPLE_FLEXFIELD_SG_SG_PAYEE_ID_TYPE',
2101 'X_PER_NATIONALITY_CODE',
2102 'X_HR_IR8A_INDICATORS_SG_PER_SECTION_45_APPLICABLE',
2103 'X_HR_IR8A_INDICATORS_SG_PER_INCOME_TAX_BORNE_BY_EMPLOYER',
2104 'X_HR_IR8A_INDICATORS_SG_PER_IR8S_APPLICABLE',
2105 'X_HR_IR8A_INDICATORS_SG_EXEMPT'
2106 ,'X_HR_IR8A_INDICATORS_SG_APPR_IRAS',
2107 'X_HR_IR8A_INDICATORS_SG_DATE_OF_APPR_IRAS',
2108 'X_HR_IR8A_FURTHER_DETAILS_SG_PER_RETIREMENT_FUND',
2109 'X_HR_IR8A_FURTHER_DETAILS_SG_PER_DESIGNATED_PENSION',
2110 'X_HR_IRAS_ADDITIONAL_INFO_SG_PER_ADDITIONAL_INFORMATION',
2111 'X_HR_IR8S_INDICATORS_SG_ASG_VOLUNTARY_CPF_OBLIGATORY',
2112 'X_HR_IR8S_INDICATORS_SG_ASG_APPR_CPF',
2113 'X_IR8A_MOA_369_DATE',
2114 'X_HR_IR8A_FURTHER_DETAILS_SG_NAME_OF_BANK',
2115 'X_ASG_DESIGNATION',
2116 'X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY',
2117 'X_ASG_OVERSEAS_DATE_FROM',
2118 'X_ASG_OVERSEAS_DATE_TO',
2119 'X_PER_PAYROLL_DATE',
2120 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME',
2121 'X_SG_LEGAL_ENTITY_SG_ER_INCOME_TAX_NUMBER',
2122 'X_SG_LEGAL_ENTITY_SG_ER_OHQ_STATUS',
2123 'X_SG_LEGAL_ENTITY_SG_ER_IRAS_CATEGORY',
2124 'X_SG_LEGAL_ENTITY_SG_ER_TELEPHONE_NUMBER',
2125 'X_SG_LEGAL_ENTITY_SG_ER_PAYER_ID',
2126 'X_SG_LEGAL_ENTITY_SG_ER_JOB_DES_TYPE',
2127 'X_SG_LEGAL_ENTITY_SG_ER_AUTH_PERSON_EMAIL',
2128 'X_SG_LEGAL_ENTITY_SG_ER_DIVISION',
2129 'X_SG_LEGAL_ENTITY_SG_ER_ID_CHECK',
2130 'X_SG_LEGAL_ENTITY_SG_A8B_INCORP_DATE',
2131 'X_IR8A_MOA_265_DATE_FROM',
2132 'X_IR8A_MOA_265_DATE_TO',
2133 'X_IR8A_MOA_265_INDICATOR',
2134 'X_IR8A_MOA_340_DATE',
2135 'X_ADDITIONAL_EARNINGS_DATE')
2136 and ue.user_entity_id = arch.user_entity_id
2137 AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2138 -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2139 from pay_payroll_actions ppa,
2140 pay_assignment_actions paa
2141 where ppa.payroll_action_id in (SELECT org_information2
2142 FROM hr_organization_information
2143 WHERE org_information_context = 'SG_IRAS_DETAILS'
2144 AND organization_id = g_legal_entity_id
2145 AND org_information1 = g_basis_year)
2146 and ppa.payroll_action_id = paa.payroll_action_id
2147 and ppa.report_type='SG_IRAS_ARCHIVE'
2148 and paa.assignment_id = l_assignment_id) ;
2149
2150
2151 hr_utility.set_location('pysgiraa: archive_balances ',170);
2152 if t_orig_user_entity_id.count >0 then
2153
2154 forall counter in 1..t_orig_user_entity_id.count
2155 insert into ff_archive_items
2156 ( archive_item_id,
2157 user_entity_id,
2158 context1,
2159 value,
2160 archive_type )
2161 values
2162 ( ff_archive_items_s.nextval,
2163 t_orig_user_entity_id(counter),
2164 p_assignment_action_id,
2165 t_orig_value(counter),
2166 'AAP' )
2167 returning archive_item_id bulk collect into t_archive_items_orig ;
2168 --
2169
2170 forall counter in t_archive_items_orig.first..t_archive_items_orig.last
2171 insert into ff_archive_item_contexts
2172 ( archive_item_id,
2173 sequence_no,
2174 context,
2175 context_id )
2176 values
2177 ( t_archive_items_orig(counter),
2178 1,
2179 p_assignment_action_id,
2180 l_asac_cont_id );
2181
2182 end if;
2183 end if;
2184 hr_utility.set_location('pysgiraa: archive_balances ',180);
2185 t_user_entity_id.delete;
2186 t_archive_value.delete;
2187 t_date_earned.delete;
2188
2189
2190 t_archive_value_ir8a.delete;
2191 t_user_entity_id_ir8a.delete;
2192
2193 t_archive_value_ir8s.delete;
2194 t_user_entity_id_ir8s.delete;
2195
2196 t_archive_value_a8a.delete;
2197 t_user_entity_id_a8a.delete;
2198
2199 ------------------------------------------------------------------------------------------------
2200 -- Bug# 2833530 - Added p_person_id as the parameter for the archive_balance_dates for the
2201 -- employees having terminated and rehired in the same financial year
2202 ------------------------------------------------------------------------------------------------
2203
2204 if g_debug then
2205 hr_utility.set_location('pysgiraa: End of archive_balances',100);
2206 end if;
2207 end archive_balances;
2208
2209 ---------------------------------------------------------------------------
2210
2211
2212 ---------------------------------------------------------------------------
2213 procedure archive_shares_details
2214 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2215 p_person_id in per_all_people_f.person_id%type,
2216 p_tax_unit_id in ff_archive_item_contexts.context%type,
2217 p_basis_start in date,
2218 p_basis_end in date )
2219 is
2220
2221 type t_archive_items_tab is table of ff_archive_items.archive_item_id%TYPE index by binary_integer;
2222 t_archive_items_a8b t_archive_items_tab;
2223
2224 type t_archive_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
2225 t_archive_value_a8b t_archive_value_tab;
2226
2227 type t_amend_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
2228 t_amend_value_a8b t_amend_value_tab;
2229
2230 type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
2231 t_user_entity_id_a8b t_user_entity_tab;
2232
2233 type t_user_entity_name_tab is table of ff_user_entities.user_entity_name%TYPE index by binary_integer;
2234 t_user_entity_name_a8b t_user_entity_name_tab;
2235
2236 type t_assignment_extra_info_tab is table of per_assignment_extra_info.assignment_extra_info_id%TYPE index by binary_integer;
2237 t_aeid_a8b t_assignment_extra_info_tab;
2238
2239 type shares_amend_rec is record
2240 ( person_extra_info_id per_people_extra_info.person_extra_info_id%type,
2241 stock_option per_people_extra_info.pei_information1%type,
2242 exercise_price per_people_extra_info.pei_information1%type,
2243 market_exercise_value per_people_extra_info.pei_information1%type,
2244 exercise_date per_people_extra_info.pei_information1%type,
2245 shares_acquired per_people_extra_info.pei_information1%type,
2246 name_of_company per_people_extra_info.pei_information1%type,
2247 rcb per_people_extra_info.pei_information1%type,
2248 company_type per_people_extra_info.pei_information1%type,
2249 market_grant_value per_people_extra_info.pei_information1%type,
2250 grant_type per_people_extra_info.pei_information1%type,
2251 grant_date per_people_extra_info.pei_information1%type,
2252 rec_type varchar2(1));
2253 type shares_value_tab is table of shares_amend_rec index by binary_integer;
2254 t_value_shares shares_value_tab;
2255
2256 l_temp_value VARCHAR2(2000);
2257 archive_value_shares_acquired VARCHAR2(2000);
2258 amend_value_shares_acquired VARCHAR2(2000);
2259 l_name_ue VARCHAR2(2000);
2260 l_assignment_id per_assignments_f.assignment_id%type;
2261
2262 v_moa_305 number;
2263 v_moa_319 number;
2264 v_moa_339 number;
2265 v_moa_601 number;
2266 v_moa_352 number;
2267 v_moa_355 number;
2268 v_moa_358 number;
2269 v_moa_602 number;
2270 v_moa_348 number; /* Bug 7415444 */
2271 v_moa_347 number;
2272 v_grant_type_error char(1);
2273 v_eesop_date_error char(1);
2274 v_csop_date_error char(1);
2275 v_nsop_date_error char(1);
2276 v_esop_count number;
2277 v_eesop_count number;
2278 v_csop_count number;
2279 v_nsop_count number;
2280 v_a8b_data_error char(1);
2281 v_a8b_files char(1);
2282 amend_a8b_flag char(1);
2283 l_count number;
2284 v_er_incorp_date_1 char(10);
2285 v_er_incorp_date_2 char(10);
2286 v_er_incorp_date hr_organization_information.org_information9%type;
2287 v_archive char(1);
2288
2289
2290 ---------------------------------------------------------------------------
2291 -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
2292 -- bug 2691877
2293 -- bug 3501956 - Changed cursor to select information from per_people_extra_info table
2294 -- Bug 4314453 - Modified the cursor to use the table instead of view
2295 -- Bug 5435088 - Added grant type
2296 ---------------------------------------------------------------------------
2297 cursor shares_details
2298 ( c_person_id per_all_people_f.person_id%type,
2299 c_basis_start date,
2300 c_basis_end date )
2301 is
2302 select distinct pei.person_extra_info_id,
2303 pei.pei_information1 stock_option,
2304 pei.pei_information3 exercise_price,
2305 pei.pei_information4 market_exercise_value,
2306 to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
2307 to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
2308 pei.pei_information6 shares_acquired,
2309 hoi1.org_information1 name_of_company,
2310 hoi1.org_information4 RCB,
2311 hoi1.org_information15 company_type,
2312 pei2.pei_information2 market_grant_value,
2313 pei2.pei_information5 grant_type,
2314 decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
2315 to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
2316 pei2.pei_information4 shares_granted
2317 from per_all_people_f pap,
2318 per_people_extra_info pei,
2319 per_people_extra_info pei2,
2320 hr_all_organization_units hou,
2321 hr_organization_information hoi2,
2322 hr_organization_information hoi1
2323 where pap.person_id = c_person_id
2324 and pap.person_id = pei.person_id
2325 and pei.information_type = 'HR_STOCK_EXERCISE_SG'
2326 and pap.person_id = pei2.person_id
2327 and pei.pei_information2 = pei2.person_extra_info_id
2328 and pei2.information_type = 'HR_STOCK_GRANT_SG'
2329 and pei2.pei_information1 = hou.organization_id
2330 and hou.organization_id = hoi1.organization_id(+)
2331 and hou.organization_id = hoi2.organization_id
2332 and hoi1.org_information_context||'' = 'SG_LEGAL_ENTITY'
2333 and hoi2.org_information_context||'' = 'CLASS'
2334 and hoi2.org_information1 = 'HR_LEGAL'
2335 and hoi2.org_information2 = 'Y'
2336 and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */
2337 and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
2338 --
2339
2340 cursor shares_removed
2341 (c_user_entity_id ff_user_entities.user_entity_id%type,
2342 c_assignment_id per_assignment_extra_info.assignment_id%type,
2343 c_person_id per_people_f.person_id%type,
2344 c_tax_unit_id ff_archive_item_contexts.context%type) is
2345
2346 select distinct con2.context person_extra_info_id
2347 from ff_archive_items arch,
2348 ff_archive_item_contexts con2
2349 where arch.user_entity_id = c_user_entity_id
2350 and arch.context1 IN(
2351 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2352 from pay_payroll_actions ppa,
2353 pay_assignment_actions paa
2354 where ppa.payroll_action_id in (
2355 SELECT org_information2
2356 FROM hr_organization_information
2357 WHERE org_information_context = 'SG_IRAS_DETAILS'
2358 AND organization_id = g_legal_entity_id
2359 AND org_information1 = g_basis_year)
2360 and ppa.payroll_action_id = paa.payroll_action_id
2361 and paa.assignment_id = c_assignment_id)
2362 and exists (select 1
2363 from ff_archive_item_contexts con1
2364 where con1.archive_item_id = arch.archive_item_id
2365 and con1.context = c_tax_unit_id
2366 and con1.sequence_no =2)
2367 and arch.archive_item_id = con2.archive_item_id
2368 and con2.sequence_no = 3
2369 and not exists (select 1
2370 from per_people_extra_info pei
2371 where pei.person_id = c_person_id
2372 and pei.person_extra_info_id = con2.context);
2373 begin
2374 v_moa_305 := 0;
2375 v_moa_319 := 0;
2376 v_moa_339 := 0;
2377 v_moa_601 := 0;
2378 v_moa_352 := 0;
2379 v_moa_355 := 0;
2380 v_moa_358 := 0;
2381 v_moa_602 := 0;
2382 v_moa_348 := 0;
2383 v_moa_347 := 0;
2384 v_grant_type_error := 'N';
2385 v_eesop_date_error := 'N';
2386 v_csop_date_error := 'N';
2387 v_nsop_date_error := 'N';
2388 v_a8b_data_error := 'N';
2389 amend_a8b_flag := 'N';
2390 g_amend_a8b_flag := 'N';
2391 v_a8b_files := 'N';
2392 v_esop_count := 0;
2393 v_eesop_count := 0;
2394 v_nsop_count := 0;
2395 v_csop_count := 0;
2396 l_count := 0;
2397 v_archive := 'N';
2398
2399 if g_debug then
2400 hr_utility.set_location('pysgiraa: Start of archive_share_details', 10);
2401 end if;
2402
2403 SELECT assignment_id
2404 INTO l_assignment_id
2405 FROM pay_assignment_Actions paa
2406 WHERE paa.assignment_action_id = p_assignment_action_id;
2407
2408 select arch.value
2409 into v_er_incorp_date
2410 from ff_user_entities ue ,
2411 ff_archive_items arch
2412 where ue.user_entity_name = 'X_SG_LEGAL_ENTITY_SG_A8B_INCORP_DATE'
2413 and ue.user_entity_id = arch.user_entity_id
2414 AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2415 -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2416 from pay_payroll_actions ppa,
2417 pay_assignment_actions paa
2418 where ppa.payroll_action_id in (
2419 SELECT org_information2
2420 FROM hr_organization_information
2421 WHERE org_information_context = 'SG_IRAS_DETAILS'
2422 AND organization_id = g_legal_entity_id
2423 AND org_information1 = g_basis_year)
2424 and ppa.payroll_action_id = paa.payroll_action_id
2425 and ppa.report_type='SG_IRAS_ARCHIVE'
2426 and paa.assignment_id = l_assignment_id);
2427
2428 if v_er_incorp_date is not null then
2429 v_er_incorp_date_1 := to_char(fnd_date.canonical_to_date(v_er_incorp_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'DD');
2430 v_er_incorp_date_2 := to_char(to_number(to_char(fnd_date.canonical_to_date(v_er_incorp_date),'YYYY'))+3)||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'DD');
2431 end if;
2432
2433 select ue.user_entity_id, ue.user_entity_name
2434 bulk collect into t_user_entity_id_a8b, t_user_entity_name_a8b
2435 from ff_user_entities ue
2436 where ue.user_entity_name in (
2437 'X_A8B_COMPANY'
2438 ,'X_A8B_COMPANY_TYPE'
2439 ,'X_A8B_EXERCISED_DATE'
2440 ,'X_A8B_EXER_PRICE'
2441 ,'X_A8B_GRANTED_DATE'
2442 ,'X_A8B_GRANT_TYPE'
2443 ,'X_A8B_MK_EXER_VALUE'
2444 ,'X_A8B_MK_GRANT_VALUE'
2445 ,'X_A8B_OPTION'
2446 ,'X_A8B_RCB'
2447 ,'X_A8B_SHARES_ACQUIRED')
2448 order by ue.user_entity_name desc;
2449
2450 for shares_removed_rec in shares_removed (t_user_entity_id_a8b(1),
2451 l_assignment_id,
2452 p_person_id,
2453 p_tax_unit_id)
2454 loop
2455
2456 begin
2457 l_temp_value :=' ';
2458 select sum(value)
2459 into l_temp_value
2460 from ff_archive_items arch
2461 where arch.user_entity_id = t_user_entity_id_a8b(1)
2462 and arch.context1 IN(
2463 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2464 from pay_payroll_actions ppa,
2465 pay_assignment_actions paa
2466 where ppa.payroll_action_id in (
2467 SELECT org_information2
2468 FROM hr_organization_information
2469 WHERE org_information_context = 'SG_IRAS_DETAILS'
2470 AND organization_id = g_legal_entity_id
2471 AND org_information1 = g_basis_year)
2472 and ppa.payroll_action_id = paa.payroll_action_id
2473 and paa.assignment_id = l_assignment_id)
2474 and exists (select 1
2475 from ff_archive_item_contexts con1
2476 where con1.archive_item_id = arch.archive_item_id
2477 and con1.context = p_tax_unit_id
2478 and con1.sequence_no =2)
2479 and exists (select 1
2480 from ff_archive_item_contexts con2
2481 where con2.archive_item_id = arch.archive_item_id
2482 and con2.context = shares_removed_rec.person_extra_info_id
2483 and con2.sequence_no = 3);
2484 exception
2485 WHEN NO_DATA_FOUND THEN
2486 NULL;
2487 end;
2488
2489 if l_temp_value <> 0 then
2490 g_amend_a8b_flag := 'Y';
2491
2492 select arch.value
2493 bulk collect into t_amend_value_a8b
2494 from ff_user_entities ue ,
2495 ff_archive_items arch
2496 where ue.user_entity_name in ('X_A8B_COMPANY'
2497 ,'X_A8B_RCB'
2498 ,'X_A8B_COMPANY_TYPE'
2499 ,'X_A8B_OPTION'
2500 ,'X_A8B_MK_EXER_VALUE'
2501 ,'X_A8B_MK_GRANT_VALUE'
2502 ,'X_A8B_SHARES_ACQUIRED'
2503 ,'X_A8B_EXER_PRICE'
2504 ,'X_A8B_EXERCISED_DATE'
2505 ,'X_A8B_GRANTED_DATE'
2506 ,'X_A8B_GRANT_TYPE')
2507 and ue.user_entity_id = arch.user_entity_id
2508 AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2509 -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2510 from pay_payroll_actions ppa,
2511 pay_assignment_actions paa
2512 where ppa.payroll_action_id in (SELECT org_information2
2513 FROM hr_organization_information
2514 WHERE org_information_context = 'SG_IRAS_DETAILS'
2515 AND organization_id = g_legal_entity_id
2516 AND org_information1 = g_basis_year)
2517 and ppa.payroll_action_id = paa.payroll_action_id
2518 and ppa.report_type='SG_IRAS_ARCHIVE'
2519 and paa.assignment_id = l_assignment_id)
2520 and exists (select 1
2521 from ff_archive_item_contexts con1
2522 where con1.archive_item_id = arch.archive_item_id
2523 and con1.context = p_tax_unit_id
2524 and con1.sequence_no =2)
2525 and exists (select 1
2526 from ff_archive_item_contexts con2
2527 where con2.archive_item_id = arch.archive_item_id
2528 and con2.context = shares_removed_rec.person_extra_info_id
2529 and con2.sequence_no = 3)
2530 order by ue.user_entity_name desc;
2531
2532 t_amend_value_a8b(1) := 0-l_temp_value;
2533
2534 l_count := l_count +1;
2535 t_value_shares(l_count).person_extra_info_id := shares_removed_rec.person_extra_info_id;
2536 t_value_shares(l_count).shares_acquired := t_amend_value_a8b(1);
2537 t_value_shares(l_count).rcb := t_amend_value_a8b(2);
2538 t_value_shares(l_count).stock_option := t_amend_value_a8b(3);
2539 t_value_shares(l_count).market_grant_value := t_amend_value_a8b(4);
2540 t_value_shares(l_count).market_exercise_value := t_amend_value_a8b(5);
2541 t_value_shares(l_count).grant_type := t_amend_value_a8b(6);
2542 t_value_shares(l_count).grant_date := t_amend_value_a8b(7);
2543 t_value_shares(l_count).exercise_price := t_amend_value_a8b(8);
2544 t_value_shares(l_count).exercise_date := t_amend_value_a8b(9);
2545 t_value_shares(l_count).company_type := t_amend_value_a8b(10);
2546 t_value_shares(l_count).name_of_company := t_amend_value_a8b(11);
2547 t_value_shares(l_count).rec_type := 'A';
2548
2549 end if;
2550 end loop;
2551
2552 for share_rec in shares_details (p_person_id, p_basis_start, p_basis_end)
2553 loop
2554 archive_value_shares_acquired := share_rec.shares_acquired;
2555
2556 begin
2557 l_temp_value := null;
2558 select sum(value)
2559 into l_temp_value
2560 from ff_archive_items arch
2561 where arch.user_entity_id = t_user_entity_id_a8b(1)
2562 and arch.context1 IN(
2563 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2564 from pay_payroll_actions ppa,
2565 pay_assignment_actions paa
2566 where ppa.payroll_action_id in (
2567 SELECT org_information2
2568 FROM hr_organization_information
2569 WHERE org_information_context = 'SG_IRAS_DETAILS'
2570 AND organization_id = g_legal_entity_id
2571 AND org_information1 = g_basis_year)
2572 and ppa.payroll_action_id = paa.payroll_action_id
2573 and paa.assignment_id = l_assignment_id)
2574 and exists (select 1
2575 from ff_archive_item_contexts con1
2576 where con1.archive_item_id = arch.archive_item_id
2577 and con1.context = p_tax_unit_id
2578 and con1.sequence_no =2)
2579 and exists (select 1
2580 from ff_archive_item_contexts con2
2581 where con2.archive_item_id = arch.archive_item_id
2582 and con2.context = share_rec.person_extra_info_id
2583 and con2.sequence_no = 3);
2584 exception
2585 WHEN NO_DATA_FOUND THEN
2586 NULL;
2587 end;
2588 amend_value_shares_acquired := nvl(l_temp_value,0);
2589 if to_number(archive_value_shares_acquired) <> to_number(amend_value_shares_acquired) then
2590 amend_a8b_flag := 'Y';
2591 end if;
2592
2593 if amend_a8b_flag = 'Y' then
2594 l_count := l_count +1;
2595 t_value_shares(l_count).person_extra_info_id := share_rec.person_extra_info_id;
2596 t_value_shares(l_count).shares_acquired := archive_value_shares_acquired - amend_value_shares_acquired;
2597 t_value_shares(l_count).rcb := share_rec.rcb;
2598 t_value_shares(l_count).stock_option := share_rec.stock_option;
2599 t_value_shares(l_count).market_grant_value := share_rec.market_grant_value;
2600 t_value_shares(l_count).market_exercise_value := share_rec.market_exercise_value;
2601 t_value_shares(l_count).grant_type := share_rec.grant_type;
2602 t_value_shares(l_count).grant_date := share_rec.grant_date;
2603 t_value_shares(l_count).exercise_price := share_rec.exercise_price;
2604 t_value_shares(l_count).exercise_date := share_rec.exercise_date;
2605 t_value_shares(l_count).company_type := share_rec.company_type;
2606 t_value_shares(l_count).name_of_company := share_rec.name_of_company;
2607 if to_number(amend_value_shares_acquired) = 0 then
2608 t_value_shares(l_count).rec_type := 'O'; /*Original*/
2609 else
2610 t_value_shares(l_count).rec_type := 'A'; /* Amend */
2611 end if;
2612 g_amend_a8b_flag := 'Y';
2613 end if;
2614
2615 amend_a8b_flag := 'N';
2616 end loop;
2617
2618 for counter in 1..t_value_shares.count
2619 loop
2620
2621 if t_value_shares(counter).stock_option = 'E' then
2622 if v_esop_count < 15 then
2623 if t_value_shares(counter).grant_type = 'P' and to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') < to_date('2002/12/31','YYYY/MM/DD') then
2624 v_moa_305 := (t_value_shares(counter).market_exercise_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired + v_moa_305;
2625 else
2626 v_moa_352 := (t_value_shares(counter).market_exercise_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired + v_moa_352;
2627 end if;
2628 v_esop_count := v_esop_count + 1;
2629 v_archive := 'Y';
2630 end if;
2631 end if;
2632
2633 if t_value_shares(counter).stock_option = 'EE' then
2634 if v_eesop_count < 15 then
2635 if t_value_shares(counter).grant_type = 'P' and to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') < to_date('2002/12/31','YYYY/MM/DD') then
2636 v_moa_319 := trunc((t_value_shares(counter).market_exercise_value - t_value_shares(counter).market_grant_value)*t_value_shares(counter).shares_acquired,2)
2637 + trunc((t_value_shares(counter).market_grant_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired,2) + v_moa_319;
2638 else
2639 v_moa_355 := trunc((t_value_shares(counter).market_exercise_value - t_value_shares(counter).market_grant_value)*t_value_shares(counter).shares_acquired,2)
2640 + trunc((t_value_shares(counter).market_grant_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired,2) + v_moa_355;
2641 end if;
2642 v_eesop_count := v_eesop_count + 1;
2643 v_archive := 'Y';
2644 if v_eesop_date_error = 'N'
2645 and t_value_shares(counter).rec_type = 'O' then
2646 if ((t_value_shares(counter).grant_type = 'P' and
2647 to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD')
2648 < to_date('2000/06/01','YYYY/MM/DD')) or
2649 (t_value_shares(counter).grant_type = 'W' and
2650 to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD')
2651 < to_date('2002/01/01','YYYY/MM/DD'))) then
2652 v_eesop_date_error := 'Y';
2653 end if;
2654 end if;
2655 end if;
2656 end if;
2657
2658 if t_value_shares(counter).stock_option = 'C' then
2659 if v_csop_count < 15 then
2660 if t_value_shares(counter).grant_type = 'P' and to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') < to_date('2002/12/31','YYYY/MM/DD') then
2661 v_moa_339 := trunc((t_value_shares(counter).market_exercise_value - t_value_shares(counter).market_grant_value)*t_value_shares(counter).shares_acquired,2)
2662 + trunc((t_value_shares(counter).market_grant_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired,2) + v_moa_339;
2663 else
2664 v_moa_358 := trunc((t_value_shares(counter).market_exercise_value - t_value_shares(counter).market_grant_value)*t_value_shares(counter).shares_acquired,2)
2665 + trunc((t_value_shares(counter).market_grant_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired,2) + v_moa_358;
2666 end if;
2667 v_csop_count := v_csop_count + 1;
2668 v_archive := 'Y';
2669 if v_csop_date_error = 'N'
2670 and t_value_shares(counter).rec_type = 'O' then
2671 if ((t_value_shares(counter).grant_type = 'P' and
2672 to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD')
2673 < to_date('2001/04/01','YYYY/MM/DD')) or
2674 (t_value_shares(counter).grant_type = 'W' and
2675 to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD')
2676 < to_date('2002/01/01','YYYY/MM/DD'))) then
2677 v_csop_date_error := 'Y';
2678 end if;
2679 end if;
2680 end if;
2681 end if;
2682
2683 if t_value_shares(counter).stock_option = 'N' then
2684 if v_nsop_count < 15 then
2685 if not (t_value_shares(counter).grant_type = 'P' and to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') < to_date('2002/12/31','YYYY/MM/DD')) then
2686 v_moa_348 := (t_value_shares(counter).market_exercise_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired + v_moa_348;
2687 v_moa_347 := (t_value_shares(counter).market_grant_value - t_value_shares(counter).exercise_price) * t_value_shares(counter).shares_acquired + v_moa_347;
2688 end if;
2689 v_nsop_count := v_nsop_count + 1;
2690 v_archive := 'Y';
2691 if v_nsop_date_error = 'N'
2692 and t_value_shares(counter).rec_type = 'O' then
2693 if (to_date(t_value_shares(counter).grant_date,'YYYY/MM/DD') between
2694 to_date('2008/02/16','YYYY/MM/DD') and
2695 to_date('2013/02/15','YYYY/MM/DD')) and
2696 (to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') between
2697 fnd_date.canonical_to_date(v_er_incorp_date_1) and
2698 fnd_date.canonical_to_date(v_er_incorp_date_2)) then
2699 null;
2700 else
2701 v_nsop_date_error := 'Y';
2702 end if;
2703 end if;
2704 end if;
2705 end if;
2706
2707 if t_value_shares(counter).rec_type = 'O' then
2708 if t_value_shares(counter).grant_type is null then
2709 v_grant_type_error := 'Y';
2710 end if;
2711
2712 if v_a8b_data_error = 'N' then
2713 if t_value_shares(counter).shares_acquired <= 0 or (t_value_shares(counter).market_exercise_value - t_value_shares(counter).exercise_price) < 0 then
2714 v_a8b_data_error := 'Y';
2715 end if;
2716 end if;
2717 end if;
2718
2719 if v_archive = 'Y' then
2720 archive_item_3('X_A8B_COMPANY', p_assignment_action_id, t_value_shares(counter).name_of_company, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2721 archive_item_3('X_A8B_RCB', p_assignment_action_id, t_value_shares(counter).RCB, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2722 archive_item_3('X_A8B_COMPANY_TYPE', p_assignment_action_id, t_value_shares(counter).company_type, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2723 archive_item_3('X_A8B_OPTION', p_assignment_action_id, t_value_shares(counter).stock_option, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2724 archive_item_3('X_A8B_MK_EXER_VALUE', p_assignment_action_id, t_value_shares(counter).market_exercise_value, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2725 archive_item_3('X_A8B_MK_GRANT_VALUE', p_assignment_action_id, t_value_shares(counter).market_grant_value, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2726 archive_item_3('X_A8B_SHARES_ACQUIRED', p_assignment_action_id, t_value_shares(counter).shares_acquired, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2727 archive_item_3('X_A8B_EXER_PRICE', p_assignment_action_id, t_value_shares(counter).exercise_price, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2728 archive_item_3('X_A8B_EXERCISED_DATE', p_assignment_action_id, t_value_shares(counter).exercise_date, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2729 archive_item_3('X_A8B_GRANTED_DATE', p_assignment_action_id, t_value_shares(counter).grant_date, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2730 archive_item_3('X_A8B_GRANT_TYPE', p_assignment_action_id, t_value_shares(counter).grant_type, p_tax_unit_id, t_value_shares(counter).person_extra_info_id);
2731 end if;
2732 v_archive := 'N';
2733 end loop;
2734 --
2735 v_moa_601 := v_moa_305 + v_moa_319 + v_moa_339;
2736 v_moa_602 := v_moa_352 + v_moa_355 + v_moa_358 + v_moa_348;
2737
2738 if v_moa_601 <> 0 or v_moa_602 <> 0 then
2739 archive_item_2 ('X_A8B_MOA_601', p_assignment_action_id, v_moa_601, p_tax_unit_id);
2740 archive_item_2 ('X_A8B_MOA_602', p_assignment_action_id, v_moa_602, p_tax_unit_id);
2741 archive_item_2 ('X_A8B_MOA_347', p_assignment_action_id, v_moa_347, p_tax_unit_id);
2742 archive_item ('X_PER_GRANT_TYPE_ERROR', p_assignment_action_id, v_grant_type_error);
2743 archive_item ('X_PER_A8B_NSOP_DATE_ERROR', p_assignment_action_id, v_nsop_date_error);
2744 archive_item ('X_PER_A8B_EESOP_DATE_ERROR', p_assignment_action_id, v_eesop_date_error);
2745 archive_item ('X_PER_A8B_CSOP_DATE_ERROR', p_assignment_action_id, v_csop_date_error);
2746 archive_item ('X_PER_A8B_DATA_ERROR', p_assignment_action_id, v_a8b_data_error);
2747
2748 if v_esop_count > 15 or v_eesop_count > 15
2749 or v_csop_count > 15 or v_nsop_count > 15 then
2750 archive_item ('X_PER_A8B_COUNT_ERROR', p_assignment_action_id, '1');
2751 end if;
2752
2753 if v_moa_348 <> 0 then
2754 g_a8b_moa_348 := g_a8b_moa_348 + v_moa_348;
2755 if v_er_incorp_date is null then
2756 archive_item ('X_PER_A8B_INCORP_DATE_ERROR', p_assignment_action_id, 'Y');
2757 end if;
2758 end if;
2759 end if;
2760
2761 if g_debug then
2762 hr_utility.set_location('pysgiraa: End of archive_share_details', 100);
2763 end if;
2764 end archive_shares_details;
2765
2766
2767 ---------------------------------------------------------------------------
2768 -- Selects information for IR8S C claimed/to be claimed details information,
2769 -- which is entered via assignment extra information screen, bug 3027801
2770 ---------------------------------------------------------------------------
2771 procedure archive_ir8s_c_details
2772 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2773 p_person_id in per_all_people_f.person_id%type,
2774 p_tax_unit_id in ff_archive_item_contexts.context%type,
2775 p_business_group_id in per_assignments_f.business_group_id%type,
2776 p_basis_start in date,
2777 p_basis_end in date) is
2778
2779 /* Type to store the person ids with same national_identifier */
2780
2781 type person_id_store_rec is record
2782 (person_id per_all_people_f.person_id%type);
2783
2784 type person_id_tab is table of person_id_store_rec index by binary_integer;
2785 person_id_rec person_id_tab;
2786
2787 l_person_id per_all_people_f.person_id%type;
2788 l_temp_person_id per_all_people_f.person_id%type;
2789 l_archive_person_id per_all_people_f.person_id%type;
2790 counter number;
2791 l_counter number;
2792 duplicate_exists varchar2(1);
2793 l_ir8s_c_counts number;
2794
2795 begin
2796 l_temp_person_id := NULL;
2797 l_counter := 1;
2798 duplicate_exists := 'N';
2799 --
2800 if g_debug then
2801 hr_utility.set_location('pysgiraa: Start of archive_ir8s_c_details', 10);
2802 end if;
2803 ----------------------------------------------------------------------------------
2804 -- Added for bug 3162319
2805 -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
2806 -- in employee_if_latest( ) function
2807 ----------------------------------------------------------------------------------
2808 if g_person_id_tab.count > 1 then
2809 for l_person_id in g_person_id_tab.first..g_person_id_tab.last
2810 loop
2811 person_id_rec(l_counter).person_id := g_person_id_tab(l_person_id);
2812 l_counter := l_counter+1;
2813 end loop;
2814 --
2815 duplicate_exists :='Y';
2816 end if;
2817 --
2818 if duplicate_exists = 'N' then
2819 person_id_rec(l_counter).person_id := p_person_id;
2820 end if;
2821 --
2822 if person_id_rec.count>0 then
2823 for l_person_counter in 1..person_id_rec.last
2824 loop
2825 if person_id_rec.exists(l_person_counter) then
2826 l_archive_person_id := person_id_rec(1).person_id;
2827 --
2828 archive_ir8s_c_detail_moas(p_assignment_action_id
2829 ,person_id_rec(1).person_id
2830 ,person_id_rec(l_person_counter).person_id
2831 ,p_tax_unit_id
2832 ,p_business_group_id
2833 ,p_basis_start
2834 ,p_basis_end);
2835 end if;
2836 end loop;
2837
2838 end if;
2839 --
2840 if g_debug then
2841 hr_utility.set_location('pysgiraa: End of archive_ir8s_c_details', 100);
2842 end if;
2843 end archive_ir8s_c_details;
2844
2845
2846 procedure archive_ir8s_c_detail_moas
2847 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2848 p_1_person_id in per_all_people_f.person_id%type,
2849 p_person_id in per_all_people_f.person_id%type,
2850 p_tax_unit_id in ff_archive_item_contexts.context%type,
2851 p_business_group_id in per_assignments_f.business_group_id%type,
2852 p_basis_start in date,
2853 p_basis_end in date) is
2854
2855
2856 type t_archive_items_tab is table of ff_archive_items.archive_item_id%TYPE index by binary_integer;
2857 t_archive_items_ir8s_c t_archive_items_tab;
2858
2859 type t_archive_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
2860 t_archive_value_ir8s_c t_archive_value_tab;
2861
2862 type t_amend_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
2863 t_amend_value_ir8s_c t_amend_value_tab;
2864 t_amend_value_ir8s_c1 t_amend_value_tab;
2865
2866 type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
2867 t_user_entity_id_ir8s_c t_user_entity_tab;
2868
2869 type t_user_entity_name_tab is table of ff_user_entities.user_entity_name%TYPE index by binary_integer;
2870 t_user_entity_name_ir8s_c t_user_entity_name_tab;
2871
2872
2873 type t_assignment_extra_info_tab is table of per_assignment_extra_info.assignment_extra_info_id%TYPE index by binary_integer;
2874 t_aeid_ir8s_c t_assignment_extra_info_tab;
2875
2876 type ir8s_c_rec is record
2877 ( assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type,
2878 value number );
2879 type ir8s_c_value_tab is table of ir8s_c_rec index by binary_integer;
2880 value_ir8s_c ir8s_c_value_tab;
2881
2882 l_temp_value VARCHAR2(2000);
2883 l_name_ue VARCHAR2(2000);
2884
2885
2886 cursor ir8s_c_details
2887 (c_person_id per_assignments_f.person_id%type,
2888 c_tax_unit_id ff_archive_item_contexts.context%type,
2889 c_business_group_id per_assignments_f.business_group_id%type,
2890 c_basis_start date,
2891 c_basis_end date) is
2892
2893 select distinct aei.assignment_extra_info_id,
2894 aei.aei_information2 add_wages,
2895 aei.aei_information3 add_wages_from_date,
2896 aei.aei_information4 add_wages_to_date,
2897 aei.aei_information5 pay_date_add_wages,
2898 aei.aei_information6 er_cpf,
2899 aei.aei_information7 er_cpf_interest,
2900 aei.aei_information8 er_cpf_date,
2901 aei.aei_information9 ee_cpf,
2902 aei.aei_information10 ee_cpf_interest,
2903 aei.aei_information11 ee_cpf_date
2904 from per_assignments_f ass,
2905 per_assignment_extra_info aei,
2906 hr_soft_coding_keyflex hsc
2907 where ass.person_id = c_person_id
2908 and ass.assignment_id = aei.assignment_id
2909 and ass.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
2910 and hsc.segment1 = c_tax_unit_id
2911 and ass.business_group_id = c_business_group_id
2912 and ass.assignment_type = 'E' /* Bug 5033609 */
2913 and aei.information_type = 'HR_CPF_CLAIMED_SG'
2914 and aei.aei_information1 = to_char(c_basis_end,'YYYY')
2915 and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
2916 and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
2917 and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
2918 and (ass.effective_start_date <= c_basis_end
2919 and ass.effective_end_date >= c_basis_start);
2920
2921 cursor ir8s_c_removed
2922 (c_user_entity_id ff_user_entities.user_entity_id%type,
2923 c_assignment_id per_assignment_extra_info.assignment_id%type,
2924 c_tax_unit_id ff_archive_item_contexts.context%type) is
2925
2926 select distinct con2.context assignment_extra_info_id
2927 from ff_archive_items arch,
2928 ff_archive_item_contexts con2
2929 where arch.user_entity_id = c_user_entity_id
2930 and arch.context1 IN(
2931 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2932 from pay_payroll_actions ppa,
2933 pay_assignment_actions paa
2934 where ppa.payroll_action_id in (
2935 SELECT org_information2
2936 FROM hr_organization_information
2937 WHERE org_information_context = 'SG_IRAS_DETAILS'
2938 AND organization_id = g_legal_entity_id
2939 AND org_information1 = g_basis_year)
2940 and ppa.payroll_action_id = paa.payroll_action_id
2941 and paa.assignment_id = c_assignment_id)
2942 and exists (select 1
2943 from ff_archive_item_contexts con1
2944 where con1.archive_item_id = arch.archive_item_id
2945 and con1.context = c_tax_unit_id
2946 and con1.sequence_no =2)
2947 and arch.archive_item_id = con2.archive_item_id
2948 and con2.sequence_no = 3
2949 and not exists (select 1
2950 from per_assignment_extra_info aei
2951 where aei.assignment_id = c_assignment_id
2952 and aei.assignment_extra_info_id = con2.context);
2953
2954
2955 v_ir8s_total_moa410 number;
2956 l_assignment_id number;
2957 l_count number;
2958 amend_ir8s_c_flag varchar2(1);
2959
2960 begin
2961 v_ir8s_total_moa410 := 0;
2962 l_count := 0;
2963 g_amend_ir8s_c_flag := 'N';
2964 if g_debug then
2965 hr_utility.set_location('pysgiraa: Start of archive_ir8s_c_detail_moas', 10);
2966 end if;
2967 --
2968 select ue.user_entity_id, ue.user_entity_name
2969 bulk collect into t_user_entity_id_ir8s_c, t_user_entity_name_ir8s_c
2970 from ff_user_entities ue
2971 where ue.user_entity_name in ('X_MOA410',
2972 'X_MOA411',
2973 'X_MOA412',
2974 'X_MOA413',
2975 'X_MOA414')
2976 order by ue.user_entity_name;
2977
2978 SELECT assignment_id
2979 INTO l_assignment_id
2980 FROM pay_assignment_Actions paa
2981 WHERE paa.assignment_action_id = p_assignment_action_id;
2982
2983 amend_ir8s_c_flag := 'N';
2984
2985 for ir8s_c_removed_rec in ir8s_c_removed (t_user_entity_id_ir8s_c(1),
2986 l_assignment_id,
2987 p_tax_unit_id)
2988 loop
2989
2990 for counter in 1..t_user_entity_id_ir8s_c.count
2991 loop
2992
2993 begin
2994 l_temp_value := ' ';
2995 select sum(value)
2996 into l_temp_value
2997 from ff_archive_items arch
2998 where arch.user_entity_id = t_user_entity_id_ir8s_c(counter)
2999 and arch.context1 IN(
3000 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
3001 from pay_payroll_actions ppa,
3002 pay_assignment_actions paa
3003 where ppa.payroll_action_id in (
3004 SELECT org_information2
3005 FROM hr_organization_information
3006 WHERE org_information_context = 'SG_IRAS_DETAILS'
3007 AND organization_id = g_legal_entity_id
3008 AND org_information1 = g_basis_year)
3009 and ppa.payroll_action_id = paa.payroll_action_id
3010 and paa.assignment_id = l_assignment_id)
3011 and exists (select 1
3012 from ff_archive_item_contexts con1
3013 where con1.archive_item_id = arch.archive_item_id
3014 and con1.context = p_tax_unit_id
3015 and con1.sequence_no =2)
3016 and exists (select 1
3017 from ff_archive_item_contexts con2
3018 where con2.archive_item_id = arch.archive_item_id
3019 and con2.context = ir8s_c_removed_rec.assignment_extra_info_id
3020 and con2.sequence_no = 3);
3021 exception
3022 WHEN NO_DATA_FOUND THEN
3023 NULL;
3024 end;
3025 t_amend_value_ir8s_c(counter) := l_temp_value;
3026
3027 if t_amend_value_ir8s_c(counter) <> 0 then
3028 amend_ir8s_c_flag := 'Y';
3029 else
3030 amend_ir8s_c_flag := 'N';
3031 end if;
3032
3033 end loop;
3034
3035 if amend_ir8s_c_flag = 'Y' then
3036 for counter in 1..t_user_entity_id_ir8s_c.count
3037 loop
3038 archive_item_3(t_user_entity_name_ir8s_c(counter),
3039 p_assignment_action_id,
3040 0 - t_amend_value_ir8s_c(counter),
3041 p_tax_unit_id, ir8s_c_removed_rec.assignment_extra_info_id);
3042
3043 end loop;
3044
3045 select arch.value
3046 bulk collect into t_amend_value_ir8s_c1
3047 from ff_user_entities ue ,
3048 ff_archive_items arch
3049 where ue.user_entity_name in ('X_DTM502'
3050 ,'X_DTM503'
3051 ,'X_DTM504'
3052 ,'X_DTM505'
3053 ,'X_DTM506')
3054 and ue.user_entity_id = arch.user_entity_id
3055 AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
3056 -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
3057 from pay_payroll_actions ppa,
3058 pay_assignment_actions paa
3059 where ppa.payroll_action_id in (SELECT org_information2
3060 FROM hr_organization_information
3061 WHERE org_information_context = 'SG_IRAS_DETAILS'
3062 AND organization_id = g_legal_entity_id
3063 AND org_information1 = g_basis_year)
3064 and ppa.payroll_action_id = paa.payroll_action_id
3065 and ppa.report_type='SG_IRAS_ARCHIVE'
3066 and paa.assignment_id = l_assignment_id)
3067 and exists (select 1
3068 from ff_archive_item_contexts con1
3069 where con1.archive_item_id = arch.archive_item_id
3070 and con1.context = p_tax_unit_id
3071 and con1.sequence_no =2)
3072 and exists (select 1
3073 from ff_archive_item_contexts con2
3074 where con2.archive_item_id = arch.archive_item_id
3075 and con2.context = ir8s_c_removed_rec.assignment_extra_info_id
3076 and con2.sequence_no = 3)
3077 order by ue.user_entity_name desc;
3078
3079 archive_item_3('X_DTM502', p_assignment_action_id,
3080 t_amend_value_ir8s_c1(1),
3081 p_tax_unit_id,
3082 ir8s_c_removed_rec.assignment_extra_info_id);
3083 archive_item_3('X_DTM503', p_assignment_action_id,
3084 t_amend_value_ir8s_c1(2),
3085 p_tax_unit_id,
3086 ir8s_c_removed_rec.assignment_extra_info_id);
3087 archive_item_3('X_DTM504', p_assignment_action_id,
3088 t_amend_value_ir8s_c1(3),
3089 p_tax_unit_id,
3090 ir8s_c_removed_rec.assignment_extra_info_id);
3091 archive_item_3('X_DTM505', p_assignment_action_id,
3092 t_amend_value_ir8s_c1(4),
3093 p_tax_unit_id,
3094 ir8s_c_removed_rec.assignment_extra_info_id);
3095 archive_item_3('X_DTM506', p_assignment_action_id,
3096 t_amend_value_ir8s_c1(5),
3097 p_tax_unit_id,
3098 ir8s_c_removed_rec.assignment_extra_info_id);
3099
3100 v_ir8s_total_moa410:=v_ir8s_total_moa410-t_amend_value_ir8s_c(1);
3101 t_amend_value_ir8s_c.delete;
3102 g_amend_ir8s_flag :='Y';
3103 l_count := l_count +1;
3104 end if;
3105
3106 amend_ir8s_c_flag := 'N';
3107 end loop;
3108
3109 for ir8s_c_rec in ir8s_c_details (p_person_id
3110 , p_tax_unit_id
3111 , p_business_group_id
3112 , p_basis_start
3113 , p_basis_end)
3114 loop
3115 t_archive_value_ir8s_c(1) := ir8s_c_rec.add_wages;
3116 t_archive_value_ir8s_c(2) := ir8s_c_rec.er_cpf;
3117 t_archive_value_ir8s_c(3) := ir8s_c_rec.er_cpf_interest;
3118 t_archive_value_ir8s_c(4) := ir8s_c_rec.ee_cpf;
3119 t_archive_value_ir8s_c(5) := ir8s_c_rec.ee_cpf_interest;
3120
3121 for counter in 1..t_user_entity_id_ir8s_c.count
3122 loop
3123 select user_entity_name
3124 into l_name_ue
3125 from ff_user_entities where user_entity_id = t_user_entity_id_ir8s_c(counter);
3126
3127 begin
3128 l_temp_value := ' ';
3129 select sum(value)
3130 into l_temp_value
3131 from ff_archive_items arch
3132 where arch.user_entity_id = t_user_entity_id_ir8s_c(counter)
3133 and arch.context1 IN(
3134 select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
3135 from pay_payroll_actions ppa,
3136 pay_assignment_actions paa
3137 where ppa.payroll_action_id in (
3138 SELECT org_information2
3139 FROM hr_organization_information
3140 WHERE org_information_context = 'SG_IRAS_DETAILS'
3141 AND organization_id = g_legal_entity_id
3142 AND org_information1 = g_basis_year)
3143 and ppa.payroll_action_id = paa.payroll_action_id
3144 and paa.assignment_id = l_assignment_id)
3145 and exists (select 1
3146 from ff_archive_item_contexts con1
3147 where con1.archive_item_id = arch.archive_item_id
3148 and con1.context = p_tax_unit_id
3149 and con1.sequence_no =2)
3150 and exists (select 1
3151 from ff_archive_item_contexts con2
3152 where con2.archive_item_id = arch.archive_item_id
3153 and con2.context = ir8s_c_rec.assignment_extra_info_id
3154 and con2.sequence_no = 3);
3155 exception
3156 WHEN NO_DATA_FOUND THEN
3157 NULL;
3158 end;
3159 t_amend_value_ir8s_c(counter) := nvl(l_temp_value,0);
3160
3161 if t_amend_value_ir8s_c.exists(counter)
3162 and t_archive_value_ir8s_c.exists(counter) then
3163 if t_amend_value_ir8s_c(counter) <>
3164 t_archive_value_ir8s_c(counter) then
3165 amend_ir8s_c_flag := 'Y';
3166 end if;
3167 else
3168 amend_ir8s_c_flag := 'Y';
3169 end if;
3170
3171 end loop;
3172
3173 if amend_ir8s_c_flag = 'Y' then
3174 archive_item('X_IR8S_AMEND_INDICATOR', p_assignment_action_id, 'Y');
3175
3176 for counter in 1..t_user_entity_id_ir8s_c.count
3177 loop
3178 archive_item_3(t_user_entity_name_ir8s_c(counter),
3179 p_assignment_action_id,
3180 t_archive_value_ir8s_c(counter) - t_amend_value_ir8s_c(counter),
3181 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
3182
3183 end loop;
3184 archive_item_3('X_DTM502', p_assignment_action_id,
3185 ir8s_c_rec.add_wages_from_date,
3186 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
3187 archive_item_3('X_DTM503', p_assignment_action_id,
3188 ir8s_c_rec.add_wages_to_date,
3189 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
3190 archive_item_3('X_DTM504', p_assignment_action_id,
3191 ir8s_c_rec.pay_date_add_wages,
3192 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
3193 archive_item_3('X_DTM505', p_assignment_action_id,
3194 ir8s_c_rec.er_cpf_date,
3195 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
3196 archive_item_3('X_DTM506', p_assignment_action_id,
3197 ir8s_c_rec.ee_cpf_date,
3198 p_tax_unit_id, ir8s_c_rec.assignment_extra_info_id);
3199
3200 v_ir8s_total_moa410:=v_ir8s_total_moa410 +t_archive_value_ir8s_c(1)-t_amend_value_ir8s_c(1);
3201 t_amend_value_ir8s_c.delete;
3202 t_archive_value_ir8s_c.delete;
3203 g_amend_ir8s_c_flag :='Y';
3204 l_count := l_count +1;
3205 end if;
3206
3207 amend_ir8s_c_flag := 'N';
3208 end loop;
3209
3210 if l_count >3 then
3211 archive_item ('X_IR8S_C_INVALID_RECORDS', p_assignment_action_id, 'N');
3212 else
3213 archive_item ('X_IR8S_C_INVALID_RECORDS', p_assignment_action_id, 'Y');
3214 end if;
3215 if g_amend_ir8s_c_flag = 'N' then
3216 archive_item_3('X_MOA410', p_assignment_action_id, 0,
3217 p_tax_unit_id,0);
3218 end if;
3219
3220 if v_ir8s_total_moa410 <> 0 then
3221
3222 archive_item('X_IR8S_TOTAL_MOA410', p_assignment_action_id, v_ir8s_total_moa410);
3223 end if;
3224 --
3225 if g_debug then
3226 hr_utility.set_location('pysgiraa: End of archive_ir8s_c_detail_moas', 100);
3227 end if;
3228 end archive_ir8s_c_detail_moas;
3229 ---------------------------------------------------------------------------
3230 -- Calls the archive utility to actually perform the archive of the item.
3231 ---------------------------------------------------------------------------
3232 procedure archive_item
3233 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3234 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3235 p_archive_value in ff_archive_items.value%type )
3236 is
3237 --
3238 v_user_entity_id ff_user_entities.user_entity_id%type;
3239 v_archive_item_id ff_archive_items.archive_item_id%type;
3240 v_object_version_number ff_archive_items.object_version_number%type;
3241 v_some_warning boolean;
3242 ---------------------------------------------------------------------------
3243 -- Cursor User_Entity_ID
3244 ---------------------------------------------------------------------------
3245 cursor user_entity_id
3246 ( c_user_entity_name ff_user_entities.user_entity_name%type )
3247 is
3248 select user_entity_id
3249 from ff_user_entities
3250 where user_entity_name = c_user_entity_name;
3251 --
3252 begin
3253 if g_debug then
3254 hr_utility.set_location('Start of archive_item',10);
3255 end if;
3256 --
3257 open user_entity_id (p_user_entity_name);
3258 fetch user_entity_id into v_user_entity_id;
3259 close user_entity_id;
3260 --
3261 ff_archive_api.create_archive_item
3262 ( p_validate => false
3263 ,p_archive_item_id => v_archive_item_id
3264 ,p_user_entity_id => v_user_entity_id
3265 ,p_archive_value => p_archive_value
3266 ,p_archive_type => 'AAP'
3267 ,p_action_id => p_assignment_action_id
3268 ,p_legislation_code => 'SG'
3269 ,p_object_version_number => v_object_version_number
3270 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
3271 ,p_context1 => p_assignment_action_id
3272 ,p_some_warning => v_some_warning);
3273 --
3274 if g_debug then
3275 hr_utility.set_location('End of archive_item',20);
3276 end if;
3277 end archive_item;
3278
3279 -----------------------------------------------------------------------------
3280 -- Calls the archive utility to actually perform the archive of the item with
3281 -- one another context
3282 -----------------------------------------------------------------------------
3283 procedure archive_item_2
3284 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3285 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3286 p_archive_value in ff_archive_items.value%type,
3287 p_context_value2 in ff_archive_item_contexts.context%type )
3288 is
3289 --
3290 v_user_entity_id ff_user_entities.user_entity_id%type;
3291 v_archive_item_id ff_archive_items.archive_item_id%type;
3292 v_object_version_number ff_archive_items.object_version_number%type;
3293 v_some_warning boolean;
3294 ---------------------------------------------------------------------------
3295 -- Cursor User_Entity_ID
3296 ---------------------------------------------------------------------------
3297 cursor user_entity_id
3298 ( c_user_entity_name ff_user_entities.user_entity_name%type )
3299 is
3300 select user_entity_id
3301 from ff_user_entities
3302 where user_entity_name = c_user_entity_name;
3303 --
3304 begin
3305 if g_debug then
3306 hr_utility.set_location('Start of archive_item_2',10);
3307 end if;
3308 --
3309 open user_entity_id (p_user_entity_name);
3310 fetch user_entity_id into v_user_entity_id;
3311 close user_entity_id;
3312 --
3313 ff_archive_api.create_archive_item
3314 ( p_validate => false
3315 ,p_archive_item_id => v_archive_item_id
3316 ,p_user_entity_id => v_user_entity_id
3317 ,p_archive_value => p_archive_value
3318 ,p_archive_type => 'AAP'
3319 ,p_action_id => p_assignment_action_id
3320 ,p_legislation_code => 'SG'
3321 ,p_object_version_number => v_object_version_number
3322 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
3323 ,p_context1 => p_assignment_action_id
3324 ,p_context_name2 => 'ORGANIZATION_ID'
3325 ,p_context2 => p_context_value2
3326 ,p_some_warning => v_some_warning);
3327 --
3328 if g_debug then
3329 hr_utility.set_location('End of archive_item_2',20);
3330 end if;
3331 end archive_item_2;
3332
3333 -----------------------------------------------------------------------------
3334 -- Calls the archive utility to actually perform the archive of the item with
3335 -- one another context
3336 -----------------------------------------------------------------------------
3337 procedure archive_item_3
3338 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3339 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3340 p_archive_value in ff_archive_items.value%type,
3341 p_context_value2 in ff_archive_item_contexts.context%type,
3342 p_context_value3 in ff_archive_item_contexts.context%type )
3343 is
3344 --
3345 v_user_entity_id ff_user_entities.user_entity_id%type;
3346 v_archive_item_id ff_archive_items.archive_item_id%type;
3347 v_object_version_number ff_archive_items.object_version_number%type;
3348 v_some_warning boolean;
3349 ---------------------------------------------------------------------------
3350 -- Cursor User_Entity_ID
3351 ---------------------------------------------------------------------------
3352 cursor user_entity_id
3353 ( c_user_entity_name ff_user_entities.user_entity_name%type )
3354 is
3355 select user_entity_id
3356 from ff_user_entities
3357 where user_entity_name = c_user_entity_name;
3358 --
3359 begin
3360 if g_debug then
3361 hr_utility.set_location('Start of archive_item_3',10);
3362 end if;
3363 --
3364 open user_entity_id (p_user_entity_name);
3365 fetch user_entity_id into v_user_entity_id;
3366 close user_entity_id;
3367 --
3368 ff_archive_api.create_archive_item
3369 ( p_validate => false
3370 ,p_archive_item_id => v_archive_item_id
3371 ,p_user_entity_id => v_user_entity_id
3372 ,p_archive_value => p_archive_value
3373 ,p_archive_type => 'AAP'
3374 ,p_action_id => p_assignment_action_id
3375 ,p_legislation_code => 'SG'
3376 ,p_object_version_number => v_object_version_number
3377 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
3378 ,p_context1 => p_assignment_action_id
3379 ,p_context_name2 => 'TAX_UNIT_ID'
3380 ,p_context2 => p_context_value2
3381 ,p_context_name3 => 'SOURCE_ID'
3382 ,p_context3 => p_context_value3
3383 ,p_some_warning => v_some_warning );
3384 --
3385 if g_debug then
3386 hr_utility.set_location('End of archive_item_3',20);
3387 end if;
3388 end archive_item_3;
3389 --------------------------------------------------------------------------------
3390 -- Bug 3118540 -
3391 -- Bug 3435334 - This function removes setup action when ran for IRAS Line Archive /
3392 -- initiates SRS 'IR8S Ad Hoc Printed Archive' when ran for IR8S adhoc archive
3393 --------------------------------------------------------------------------------
3394 procedure deinit_code ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
3395 is
3396 l_report_type varchar2(30);
3397 l_rep_req_id number;
3398 v_setup_action pay_payroll_actions.payroll_action_id%type;
3399 begin
3400 l_rep_req_id := 0;
3401 v_setup_action := 0;
3402 if g_debug then
3403 hr_utility.set_location('pysgiraa: Start of deinit_code',10);
3404 end if;
3405 --
3406 select report_type
3407 into l_report_type
3408 from pay_payroll_actions ppa
3409 where ppa.payroll_action_id = p_payroll_action_id ;
3410 --
3411 if l_report_type = 'SG_IRAS_AMEND_ARCHIVE' then
3412 select pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters)
3413 into v_setup_action
3414 from pay_payroll_actions
3415 where payroll_action_id = p_payroll_action_id ;
3416 --------------------------------------------------------
3417 -- Bug: 3910804 Delete data from pay_action_information
3418 -------------------------------------------------------
3419 delete from pay_action_information
3420 where action_context_id = v_setup_action
3421 and action_context_type = 'AAP'
3422 and action_information_category = 'SG_IRAS_AMEND_SETUP';
3423
3424 py_rollback_pkg.rollback_payroll_action( v_setup_action );
3425
3426 --
3427 end if;
3428 exception
3429 when others then
3430 if g_debug then
3431 hr_utility.set_location('pysgiraa: End of deinit_code',10);
3432 end if;
3433 raise;
3434 end deinit_code;
3435 ----------------------------------------------------------------------
3436 -- Bug 3435334 This function returns TRUE if no duplicate exist in
3437 -- system Or if current employee is latest in case duplicates exist in the system
3438 -- For second case it also populates global table with all its previous employement records
3439 ----------------------------------------------------------------------
3440 function employee_if_latest ( p_national_identifier in varchar2,
3441 p_person_id in per_all_people_f.person_id%type,
3442 p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
3443 p_report_type in varchar2 ) return boolean
3444 is
3445 type t_person_start_date_tab is table of per_all_people_f.start_date%type;
3446 g_person_start_date_tab t_person_start_date_tab;
3447 begin
3448 g_person_id_tab.delete;
3449 --
3450 if p_national_identifier is not null and p_report_type <> 'SG_IR8S_ADHOC_REPORT' then
3451 begin
3452 select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)
3453 bulk collect into g_person_id_tab , g_person_start_date_tab
3454 from pay_action_information pai
3455 where pai.action_information1 = p_national_identifier
3456 and pai.action_context_id = p_setup_action_id
3457 and pai.action_context_type = 'AAP'
3458 and pai.action_information_category = 'SG_IRAS_AMEND_SETUP'
3459 order by fnd_date.canonical_to_date(pai.action_information3) desc;
3460 end;
3461 --
3462 if g_person_id_tab.count > 1 then
3463 if g_person_id_tab(1) = p_person_id then
3464 return true;
3465 else
3466 return false;
3467 end if;
3468 else
3469 return true;
3470 end if;
3471 --
3472 else
3473 return true;
3474 end if;
3475 end employee_if_latest ;
3476
3477 -------------------------------------------------------------------------
3478 -- Bug 4688761, this function checks the same person_id has been archived
3479 -------------------------------------------------------------------------
3480
3481 function person_if_archived (p_person_id in per_all_people_f.person_id%type) return boolean
3482 is
3483 l_archived_person_id binary_integer;
3484 begin
3485 if g_debug then
3486 hr_utility.set_location('pysgiraa: Start of person_if_archived',10);
3487 end if;
3488
3489 l_archived_person_id := p_person_id;
3490 if t_archived_person.exists(l_archived_person_id) then
3491 if (t_archived_person(l_archived_person_id).person_id = p_person_id) then
3492 if g_debug then
3493 hr_utility.set_location('End of person_if_archived',20);
3494 end if;
3495 return true;
3496 end if;
3497 end if;
3498 if g_debug then
3499 hr_utility.set_location('End of person_if_archived',20);
3500 end if;
3501 return false;
3502 end person_if_archived;
3503
3504 -------------------------------------------------------------------------
3505 -- Bug 4890964, this function checks the parameter LE if its in the latest
3506 -- primary assignment, it needs for share details.
3507 -------------------------------------------------------------------------
3508
3509 function pri_if_latest
3510 ( p_person_id in per_all_people_f.person_id%type
3511 , p_tax_unit_id in ff_archive_item_contexts.context%type
3512 , p_basis_start in date
3513 , p_basis_end in date) return boolean
3514 is
3515 v_dummy varchar2(1);
3516 cursor pri_latest
3517 ( c_person_id per_all_people_f.person_id%type,
3518 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
3519 c_basis_start date,
3520 c_basis_end date )
3521 is
3522 select 'X'
3523 from per_assignments_f paf,
3524 hr_soft_coding_keyflex hsc
3525 where paf.person_id = c_person_id
3526 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3527 and hsc.segment1 = c_tax_unit_id
3528 and paf.primary_flag = 'Y'
3529 and paf.assignment_type = 'E' /* Bug 5033609 */
3530 and paf.effective_start_date =
3531 (select max(paf1.effective_start_date)
3532 from per_assignments_f paf1 /* Bug 5858566 */
3533 where paf1.person_id = paf.person_id
3534 and paf1.assignment_type = 'E' /* Bug 5033609 */
3535 and paf1.effective_start_date <= c_basis_end
3536 and paf1.effective_end_date >= c_basis_start
3537 and paf1.primary_flag = 'Y')
3538 and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
3539
3540 begin
3541 if g_debug then
3542 hr_utility.set_location('pysgiraa: Start of pri_if_latest',10);
3543 end if;
3544
3545 open pri_latest (p_person_id,
3546 p_tax_unit_id,
3547 p_basis_start,
3548 p_basis_end);
3549 fetch pri_latest into v_dummy;
3550 --
3551 if pri_latest%found then
3552 close pri_latest;
3553 if g_debug then
3554 hr_utility.set_location('End of pri_if_latest',20);
3555 end if;
3556 return TRUE;
3557 end if;
3558 close pri_latest;
3559 if g_debug then
3560 hr_utility.set_location('End of pri_if_latest',20);
3561 end if;
3562 return FALSE;
3563
3564 end pri_if_latest;
3565
3566
3567 -------------------------------------------------------------------------
3568 -- Bug 4890964, with LE, this function gets the assignment with the latest
3569 -- effective_start_date with the primary defined
3570 -------------------------------------------------------------------------
3571
3572 function pri_LE_if_latest
3573 ( p_person_id in per_all_people_f.person_id%type
3574 , p_tax_unit_id in ff_archive_item_contexts.context%type
3575 , p_basis_start in date
3576 , p_basis_end in date) return number
3577 is
3578 v_assignment_id number(10);
3579 cursor pri_latest_LE
3580 ( c_person_id per_all_people_f.person_id%type,
3581 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
3582 c_basis_start date,
3583 c_basis_end date )
3584 is
3585 select paf.assignment_id
3586 from per_assignments_f paf,
3587 hr_soft_coding_keyflex hsc
3588 where paf.person_id = c_person_id
3589 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3590 and hsc.segment1 = c_tax_unit_id
3591 and paf.primary_flag = 'Y'
3592 and paf.assignment_type = 'E' /* Bug 5033609 */
3593 and paf.effective_start_date =
3594 (select max(paf1.effective_start_date)
3595 from per_assignments_f paf1 /* Bug 5858566 */
3596 where paf1.person_id = paf.person_id
3597 and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3598 and paf1.assignment_type = 'E' /* Bug 5033609 */
3599 and paf1.effective_start_date <= c_basis_end
3600 and paf1.effective_end_date >= c_basis_start
3601 and paf1.primary_flag = 'Y')
3602 and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
3603
3604 begin
3605 if g_debug then
3606 hr_utility.set_location('pysgiraa: Start of pri_LE_if_latest',10);
3607 end if;
3608
3609 open pri_latest_LE (p_person_id,
3610 p_tax_unit_id,
3611 p_basis_start,
3612 p_basis_end);
3613 fetch pri_latest_LE into v_assignment_id;
3614 --
3615 if pri_latest_LE%found then
3616 close pri_latest_LE;
3617 if g_debug then
3618 hr_utility.set_location('End of pri_LE_if_latest',20);
3619 end if;
3620 return v_assignment_id;
3621 end if;
3622 close pri_latest_LE;
3623 if g_debug then
3624 hr_utility.set_location('End of pri_LE_if_latest',20);
3625 end if;
3626 return null;
3627
3628 end pri_LE_if_latest;
3629
3630 -------------------------------------------------------------------------
3631 -- Bug 4890964, with LE, this function gets the assignment with the latest
3632 -- effective_start_date if it has no primary defined, and if it has multi
3633 -- same effective_start_date, it will get the max(assignment_id)
3634 -- Bug 6866170, if it has multiple LEs, each LE has multi assignment
3635 -- records, for example, job changes. Both latest assignment of different
3636 -- LE has the same effective_start_date. The issue is in the first
3637 -- assignment that is not a primary assignment, the cursor id_latest_LE did
3638 -- not return an assignment_id.
3639 -------------------------------------------------------------------------
3640
3641 function id_LE_if_latest
3642 ( p_person_id in per_all_people_f.person_id%type
3643 , p_tax_unit_id in ff_archive_item_contexts.context%type
3644 , p_basis_start in date
3645 , p_basis_end in date) return number
3646 is
3647 v_assignment_id number(10);
3648 cursor id_latest_LE
3649 ( c_person_id per_all_people_f.person_id%type,
3650 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
3651 c_basis_start date,
3652 c_basis_end date )
3653 is
3654 select max(paf.assignment_id)
3655 from per_assignments_f paf,
3656 hr_soft_coding_keyflex hsc
3657 where paf.person_id = c_person_id
3658 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3659 and hsc.segment1 = c_tax_unit_id
3660 and paf.assignment_type = 'E'
3661 and paf.effective_start_date = (
3662 select max(paf1.effective_start_date)
3663 from per_assignments_f paf1 /* Bug 5858566 */
3664 where paf1.person_id = paf.person_id
3665 and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3666 and paf1.assignment_type = 'E' /*Bug5033609*/
3667 and paf1.effective_start_date <= c_basis_end
3668 and paf1.effective_end_date >= c_basis_start)
3669 and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
3670
3671 begin
3672 if g_debug then
3673 hr_utility.set_location('pysgiraa: Start of id_LE_if_latest',10);
3674 end if;
3675
3676 open id_latest_LE ( p_person_id
3677 , p_tax_unit_id
3678 , p_basis_start
3679 , p_basis_end);
3680 fetch id_latest_LE into v_assignment_id;
3681 --
3682 if id_latest_LE%found then
3683 close id_latest_LE;
3684 if g_debug then
3685 hr_utility.set_location('End of id_LE_if_latest',20);
3686 end if;
3687 return v_assignment_id;
3688 end if;
3689 close id_latest_LE;
3690 if g_debug then
3691 hr_utility.set_location('End of id_LE_if_latest',20);
3692 end if;
3693 return null;
3694
3695 end id_LE_if_latest;
3696
3697 begin
3698 g_debug := hr_utility.debug_enabled;
3699 g_org_run := 'N';
3700 g_org_a8a_run := 'N';
3701 g_iras_method := 'A';
3702 g_a8b_moa_348 := 0;
3703 end pay_sg_iras_amend_archive;