[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_USER_INIT_EARN
Source
1 PACKAGE BODY pay_ca_user_init_earn AS
2 /* $Header: pycauiet.pkb 120.1.12010000.2 2009/05/15 07:30:16 sneelapa ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1998 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ca_user_init_earn
21 Filename : pycauiet.pkb
22 Change List
23 -----------
24 Date Name Vers Bug No Description
25 ---- ---- ---- ------ -----------
26 10-NOV-98 R.Murthy 115.0 First Created.
27
28 03-JUN-99 R. Murthy 115.4 901531 Update shadow structure
29 also updates the user-entered
30 element description and element
31 reporting name.
32
33 Added a check to see if the
34 element being created has the
35 same name as that of an
36 existing balance (seeded or
37 otherwise). If yes, an error
38 is raised. This prevents
39 users from creating elements
40 with reserved words as names.
41 17-FEB-2000 RThirlby 115.5 Added p_ele_calc_method to
42 procedure create_user_init_
43 earning. Used in update_shadow_
44 element.
45 17-FEB-2000 RThirlby Added new procedure
46 update_jd_level_on_balance.
47 This updates jurisdiction_level
48 on pay_balance_types for all
49 balances.
50 Changes for Flexi date too.
51 29-FEB-2000 RThirlby Added p_ele_eoy_type to
52 procedure create_user_init_
53 earning. Used in update_shadow_
54 element. This parameter inserts
55 the Year End Form for an
56 earning.
57 21-MAR-2000 ACai Update date mask for 11i.
58 31-OCT-2000 JARTHURT 115.9 Update t4a footnote,rl1 footnote
59 and registration number for
60 new reference in element DFF.
61 20-FEB-2001 Ekim 115.10 Added Procedure update_ntg_element
62 to enable 'Net to Gross'
63 functionality and also corrected
64 process_mode.
65 27-FEB-2001 SSattini 115.11 Removed extra comment symbol
66 31-May-2001 VPandya 115.12 Added Hours by Rate functionality
67 01-OCT-2001 mmukherj 115.15,16 Added the functionality of defaul
68 ting element_information9 to 'T'
69 or 'A' for HoursXRate elements.
70 11-APR-2002 SSattini 115.17 Fixed the bug#2304888
71 and also added dbdrv.
72 11-APR-2002 SSattini 115.18 Corrected GSCC complaint.
73 06-JUN-2002 mmukherj 115.19 Changed the defaulting of
74 Regular Earnings Adjustment Rule
75 for 'Earnings' elements
76 to A from T,
77 bugfix #2402284
78 20-Jan-2003 vpandya 115.20,21 Creating skip records in
79 element type usages table. If
80 Tax processing type and Year End
81 form (gre type) is matches with
82 run type name of pay_run_types_f
83 table then one record will be
84 created with inclusion_flag 'Y'
85 and usage_type 'T', and 8 records
86 created with inclusion_flag 'N'
87 and usage_type NULL for other run
88 types. For Non Payroll Payments
89 element, there will be 3 records
90 with 'Y' and 'T', and 6 records
91 with 'N' and NULL. New skip rules
92 will be assigned to new elements
93 are REG_EARNINGS_SKIP and
94 SUPP_EARNINGS_SKIP.
95 18-Feb-2003 vpandya 115.22 Using API for element type usage.
96 20-Mar-2003 vpandya 115.23 For Non-Payroll Payment element,
97 defaulting T4/RL1 Regular trigger
98 for element type usage if form
99 type is blank otherwise it
100 it defaults to form type +
101 Regulae (e.g. T4A/RL2 Regular)
102 01-MAY-2003 mmukherj 115.24 The process mode for non sepcheck
103 element has been changed from 'N'
104 to 'S'. Bugfix: 2811154,2802065
105 23-MAY-2003 pganguly 115.25 2924151 For Base/Special Feature Element
106 the element_information3 is
107 updated to 'DE'(Date Earned).
108 This is only done for Elements
109 with classification 'Earnings',
110 'Supplemental Earnings',
111 'Taxable Benefits'. Also for Sp
112 Feature elements element_infor
113 mation_category, element_infor
114 mation1 will be populated with
115 the value of the Base element
116 for those classifications.
117 20-JUN-2003 vpandya 115.26 The process mode for non sepcheck
118 element has been changed from 'N'
119 to 'S' only for 'Supplemental
120 Earnings'. Using existing
121 variable l_sep_check_create to
122 set process mode.
123 05-AUG-2003 ssouresr 115.28 Saving run balances for _GRE_RUN
124 _GRE_JD_RUN, _ASG_GRE_RUN and
125 _ASG_JD_GRE_RUN on
126 pay_defined_balances
127 05-AUG-2003 ssouresr 115.29 Removed _GRE_RUN and _GRE_JD_RUN from previous change as these
128 dimensions are not required for
129 saving nonseeded balances
130
131 25-SEP-2003 mmukherj 115.30 Bugfix : 2851568.
132 Feed Taxable Benefits for Quebec for all Taxable Benefits Element with Category PHSP.In
133 create_user_init earning a
134 section has been added to feed
135 Taxable Benefits for Quebec
136 balance for PHSP.
137 26-SEP-2003 ssattini 115.31 Added update to set the
138 post_termination_rule to 'Last
139 Standard Process Date' for all
140 recurring elements. Fix for
141 bug#2219028.
142 22-MAR-2004 ssmukher 115.32 Bug#2646705 Enhancement for
143 adding the termination rule
144 27-APR-2004 ssmukher 115.33 Bug#2646705 Replaced the skip rule from
145 REG_EARNINGS_SKIP to REGULAR_EARNINGS_SKIP
146 13-APR-2006 ahanda 115.34 Modfied package ot create a formula result
147 rule to Hours by Rate element
148 EARNINGS_AMOUNT > Pay Value
149
150 13-APR-2006 sneelapa 115.35 Bug 8491239, p_termination_rule parameter
151 should be passed to PAY_SHADOW_ELEMENT_API
152 For updating correct value of Termination Rule.
153
154 */
155 --
156 --
157 ------------------------- create_user_init_earning ----------------------------
158 --
159 FUNCTION create_user_init_earning (
160 p_ele_name in varchar2,
161 p_ele_reporting_name in varchar2,
162 p_ele_description in varchar2 default NULL,
163 p_ele_classification in varchar2,
164 p_ele_category in varchar2 default NULL,
165 p_ele_calc_method in varchar2,
166 p_ele_eoy_type in varchar2,
167 p_ele_t4a_footnote in varchar2,
168 p_ele_rl1_footnote in varchar2,
169 p_ele_registration_number in varchar2,
170 p_ele_ot_earnings in varchar2 default 'N',
171 p_ele_ot_hours in varchar2 default 'N',
172 p_ele_ei_hours in varchar2 default 'N',
173 p_ele_processing_type in varchar2,
174 p_ele_priority in number default NULL,
175 p_ele_standard_link in varchar2 default 'N',
176 p_ele_calc_rule in varchar2,
177 p_ele_calc_rule_code in varchar2 default NULL,
178 p_sep_check_option in varchar2 default 'N',
179 p_reduce_regular in varchar2 default 'N',
180 p_ele_eff_start_date in date default NULL,
181 p_ele_eff_end_date in date default NULL,
182 p_bg_id in number ,
183 p_termination_rule in varchar2 default 'F')--Bug 2646705
184 RETURN NUMBER IS
185
186 --
187 -- cursor to retrieve the element id from element name
188 --
189
190 CURSOR cur_element_type_id(p_element_name VARCHAR2) IS
191 SELECT element_type_id
192 FROM pay_element_types_f
193 WHERE upper(element_name) = upper(p_element_name)
194 AND legislation_code = 'CA';
195
196 --
197 -- cursor to retrieve the Input Value id
198 --
199
200 CURSOR cur_input_id(p_element_name varchar2,
201 p_input_value_name varchar2) is
202 SELECT piv.input_value_id
203 FROM pay_input_values_f piv, pay_element_types_f pet
204 WHERE upper(pet.element_name) = upper(p_element_name)
205 AND pet.element_type_id = piv.element_type_id
206 AND upper(pet.legislation_code) = upper('CA')
207 AND upper(piv.name) = upper(p_input_value_name);
208
209 --
210 CURSOR cur_input_id2(p_element_type_id Number,
211 p_input_value_name varchar2) is
212 SELECT piv.input_value_id
213 FROM pay_input_values_f piv, pay_element_types_f pet
214 WHERE pet.element_type_id = p_element_type_id
215 AND pet.element_type_id = piv.element_type_id
216 /* AND upper(pet.legislation_code) = upper('CA') */
217 AND upper(piv.name) = upper(p_input_value_name);
218
219 --
220 -- Processing rule already exists
221 --
222
223 CURSOR cur_processing_rule_exists(p_element_type_id number) is
224 SELECT status_processing_rule_id
225 FROM pay_status_processing_rules_f pspfr,
226 pay_element_types_f petf
227 WHERE pspfr.element_type_id = petf.element_type_id
228 AND petf.element_type_id = p_element_type_id;
229
230 --
231 -- Creating element type usages for exclusion
232 --
233
234 CURSOR c_ele_tp_usg(cp_busi_grp_id number,
235 cp_ele_name varchar2) is
236 select pet.element_type_id
237 ,pet.element_name
238 ,pet.element_information2
239 ,pet.element_information4
240 ,pet.effective_start_date
241 ,pet.effective_end_date
242 ,pet.legislation_code
243 ,pet.business_group_id
244 from pay_element_types_f pet
245 where ( pet.element_name = cp_ele_name or
246 pet.element_name = cp_ele_name || ' Special Inputs' )
247 and pet.business_group_id = cp_busi_grp_id
248 order by pet.element_name;
249
250
251 CURSOR c_run_tp is
252 select prt.*
253 from pay_run_types_f prt
254 where prt.legislation_code = 'CA'
255 and ( prt.shortname like 'REG_T4%' or
256 prt.shortname like 'NP_T4%' or
257 prt.shortname like 'LS_T4%' );
258
259 CURSOR get_asg_gre_run_dim_id IS
260 SELECT balance_dimension_id
261 FROM pay_balance_dimensions
262 WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
263 AND legislation_code = 'CA';
264
265 CURSOR get_asg_jd_gre_run_dim_id IS
266 SELECT balance_dimension_id
267 FROM pay_balance_dimensions
268 WHERE dimension_name = 'Assignment in JD within GRE Run'
269 AND legislation_code = 'CA';
270
271 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
272 l_asg_jd_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
273
274 --
275 -- Hours by Rate Variables
276 --
277 lv_legislation varchar2(10) := 'CA';
278 lv_element_name varchar2(30) := 'Hours by Rate';
279 lv_input_value_name varchar2(30);
280 lv_result_name varchar2(30);
281 lv_element_type_id number;
282 lv_input_value_id number;
283 lv_proc_rule_id number;
284 lv_formula_result_rule_id number;
285
286 --
287 -- global constants
288 c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
289
290 -- global vars
291 g_inpval_disp_seq NUMBER := 0; -- Display seq counter for input vals.
292 g_eff_start_date DATE;
293 g_eff_end_date DATE;
294
295 -- local vars
296 l_hr_only BOOLEAN;
297 l_reserved VARCHAR2(1) := 'N';
298 l_source_template_id NUMBER(9); -- Source Template ID.
299 l_template_id NUMBER(9); -- Template ID.
300 l_object_version_number NUMBER(9); -- Object Version Number
301 l_sep_check_create VARCHAR2(1);
302
303 l_bg_name VARCHAR2(60); -- Get from bg short name passed in.
304 l_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
305 l_ele_obj_ver_number NUMBER(9); -- Object Version Number
306 l_sf_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
307 l_sf_ele_obj_ver_number NUMBER(9); -- Object Version Number
308 l_si_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
309 l_si_ele_obj_ver_number NUMBER(9); -- Object Version Number
310 l_base_element_type_id NUMBER(9); -- Populated by insertion of element type.
311 l_pay_value_iv_id NUMBER(9); --
312 l_balance_feed_id NUMBER(9); --
313 l_balance_row_id NUMBER(9); --
314 l_balance_type_id NUMBER(9); --
315
316 l_pri_bal_id NUMBER(9); -- Get from pay_shadow_balance_types
317 l_hrs_bal_id NUMBER(9):= NULL; -- Get from pay_shadow_balance_types
318 l_exists number(10) := 0;
319
320 l_ele_repname VARCHAR2(30);
321 l_skip_formula VARCHAR2(80);
322 l_primary_class_id NUMBER(9);
323 l_priority NUMBER(9);
324 l_class_hi_priority NUMBER(9);
325 g_neg_earn_inpval_id NUMBER(9); -- ID of neg earn inpval for bal feed.
326
327 ln_run_type_id NUMBER;
328 lv_earn_shortname varchar2(80);
329 lv_inclusion_flag varchar2(80);
330 lv_usage_type varchar2(80);
331
332 ln_element_type_usage_id number;
333 ln_object_version_number number;
334 ld_effective_start_date date;
335 ld_effective_end_date date;
336 l_roe_allocation_by VARCHAR2(2);
337 l_sf_ele_info_category pay_element_types_f.element_information_category%TYPE;
338 l_sf_ele_category pay_element_types_f.element_information1%TYPE;
339
340 --
341 ---------------------------- get_template_id -------------------------------
342 --
343 FUNCTION get_template_id (p_legislation_code in varchar2,
344 p_calc_rule_code in varchar2)
345 RETURN number IS
346 -- local vars
347 l_template_id NUMBER(9);
348 l_template_name VARCHAR2(80);
349
350 BEGIN
351 --
352 hr_utility.set_location('pay_ca_user_init_earn.get_template_id',1);
353 --
354 if p_calc_rule_code = 'FLT' then
355 l_template_name := 'Flat Amount Earning';
356 --l_template_name := 'Test Flat Amount Earnings';
357 elsif p_calc_rule_code = 'HXR' then
358 l_template_name := 'Hours X Rate Earning';
359 --l_template_name := 'Test Hours X Rate Earnings';
360 elsif p_calc_rule_code = 'PCT' then
361 l_template_name := 'Percent of Earnings Earning';
362 elsif p_calc_rule_code = 'NTG FLT' then
363 l_template_name := 'Net To Gross Earning';
364 else
365 hr_utility.set_location('pay_ca_user_init_earn.get_template_id',2);
366 hr_utility.set_message(801,'HR_XXXXX_INVALID_CALC_RULE_EARN');
367 hr_utility.raise_error;
368 end if;
369 --
370 hr_utility.set_location('pay_ca_user_init_earn.get_template_id',3);
371 hr_utility.trace('Template Name is :'||l_template_name||'****'||
372 'Legislation is :'||p_legislation_code);
373 --
374 select template_id
375 into l_template_id
376 from pay_element_templates
377 where template_name = l_template_name
378 and legislation_code = p_legislation_code
379 and business_group_id is NULL
380 and template_type = 'T';
381 --
382 hr_utility.set_location('pay_ca_user_init_earn.get_template_id',4);
383 --
384 RETURN l_template_id;
385 --
386 END get_template_id;
387
388 --
389 --------------------------- chk_ca_pay_installed ---------------------------
390 --
391 FUNCTION chk_ca_pay_installed
392 RETURN varchar2 IS
393
394 -- local vars
395 l_installed VARCHAR2(1) := 'N';
396
397 BEGIN
398 --
399 hr_utility.set_location('pay_ca_user_init_earn.chk_ca_pay_installed',1);
400 --
401 BEGIN
402 select 'Y'
403 into l_installed
404 from pay_balance_types
405 where upper(balance_name) = 'FED SUBJECT'
406 and legislation_code = 'CA';
407
408 EXCEPTION WHEN NO_DATA_FOUND THEN
409 l_installed := 'N';
410
411 END;
412 --
413 hr_utility.set_location('pay_ca_user_init_earn.chk_ca_pay_installed',2);
414 --
415 RETURN (l_installed);
416 --
417 END chk_ca_pay_installed;
418
419 --
420 --
421 -------------------------- create_user_init_earning Main --------------------
422 --
423 -- Main Procedure
424
425 BEGIN
426 --
427 --
428 --hr_utility.trace_on('y','ORACLE');
429
430 --
431 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',1);
432 --
433 -- Set session date
434 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
435 --
436 g_eff_start_date := NVL(p_ele_eff_start_date, sysdate);
437 g_eff_end_date := NVL(p_ele_eff_end_date, c_end_of_time);
438 --
439 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',2);
440 --
441 ---------------------------- Check Element Name ---------------------------
442 --
443 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',25);
444 --
445 BEGIN
446 select 'Y'
447 into l_reserved
448 from pay_balance_types
449 where upper(p_ele_name) = upper(balance_name)
450 and nvl(legislation_code, 'CA') = 'CA'
451 and nvl(business_group_id, p_bg_id) = p_bg_id;
452
453 EXCEPTION WHEN NO_DATA_FOUND THEN
454 l_reserved := 'N';
455
456 END;
457 --
458 if l_reserved = 'Y' then
459 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',26);
460 hr_utility.set_message(801,'HR_7564_ALL_RES_WORDS');
461 hr_utility.raise_error;
462 end if;
463 --
464 ---------------------------- Get Source Template ID -----------------------
465 --
466 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',3);
467 --
468 l_source_template_id := get_template_id(
469 p_legislation_code => g_template_leg_code,
470 p_calc_rule_code => p_ele_calc_rule_code);
471 --
472 --------------------- Set Separate Check Creation -------------------------
473 --
474 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',4);
475 --
476 if p_ele_classification = 'Supplemental Earnings' then
477 l_sep_check_create := 'Y';
478 l_skip_formula := 'SUPP_EARNINGS_SKIP';
479 else
480 l_sep_check_create := 'N';
481 l_skip_formula := 'REGULAR_EARNINGS_SKIP'; --Bug 2646705 Changed by ssmukher
482 end if;
483 --
484 ---------------------------- Create User Structure ------------------------
485 --
486 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',5);
487 --
488 -- The Configuration Flex segments are as follows:
489 -- Config 1 - exclusion rule - create Separate Check input value if 'Y'
490 -- Config 2 - input value default - update default value for Sep Check IV
491 -- Config 3 - exclusion rule - create balance feeds to OT Earnings if 'Y'
492 -- Config 4 - exclusion rule - create balance feeds to OT Hours if 'Y'
493 -- Config 5 - exclusion rule - create balance feeds to Reg Earnings/Hours if 'Y'
494 -- Config 6 - exclusion rule - create balance feeds to EI Hours if 'Y'
495 -- Config 7 - exclusion rule - create SI elements if 'R', SF always created.
496 --
497 if p_reduce_regular = 'Y' then
498 l_priority := 1501;
499 else
500 l_priority := p_ele_priority;
501 end if;
502 --
503 pay_element_template_api.create_user_structure
504 (p_validate => false
505 ,p_effective_date => p_ele_eff_start_date
506 ,p_business_group_id => p_bg_id
507 ,p_source_template_id => l_source_template_id
508 ,p_base_name => p_ele_name
509 ,p_base_processing_priority => l_priority
510 ,p_configuration_information1 => l_sep_check_create
511 ,p_configuration_information2 => p_sep_check_option
512 ,p_configuration_information3 => p_ele_ot_earnings
513 ,p_configuration_information4 => p_ele_ot_hours
514 ,p_configuration_information5 => p_reduce_regular
515 ,p_configuration_information6 => p_ele_ei_hours
516 ,p_configuration_information7 => p_ele_processing_type
517 ,p_template_id => l_template_id
518 ,p_object_version_number => l_object_version_number);
519 --
520 ---------------------- Get Element Type ID of new Template -----------------
521 --
522 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',6);
523 --
524 select element_type_id, object_version_number
525 into l_element_type_id, l_ele_obj_ver_number
526 from pay_shadow_element_types
527 where template_id = l_template_id
528 and element_name = p_ele_name;
529 --
530 -- NTG elements do not have SF nor SI elements.
531 --
532
533 if p_ele_calc_rule_code <> 'NTG FLT' then
534 select element_type_id, object_version_number
535 into l_sf_element_type_id, l_sf_ele_obj_ver_number
536 from pay_shadow_element_types
537 where template_id = l_template_id
538 and element_name = p_ele_name||' Special Features';
539 end if;
540 --
541 if (p_ele_processing_type = 'R') and (p_ele_calc_rule_code <> 'NTG FLT') then
542 select element_type_id, object_version_number
543 into l_si_element_type_id, l_si_ele_obj_ver_number
544 from pay_shadow_element_types
545 where template_id = l_template_id
546 and element_name = p_ele_name||' Special Inputs';
547 end if;
548 --
549 ---------------------------- Update Shadow Structure ----------------------
550 --
551 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',7);
552 --
553 SELECT
554 DECODE(p_ele_classification,'Earnings','DE',
555 'Supplemental Earnings','DE','Taxable Benefits','DE','')
556 INTO l_roe_allocation_by
557 FROM dual;
558 --
559 SELECT
560 DECODE(p_ele_classification,
561 'Earnings',nvl(p_ele_category, hr_api.g_varchar2),
562 'Supplemental Earnings',nvl(p_ele_category, hr_api.g_varchar2),
563 'Taxable Benefits',nvl(p_ele_category, hr_api.g_varchar2),
564 '')
565 INTO l_sf_ele_category
566 FROM dual;
567 --
568 SELECT
569 DECODE(p_ele_classification,
570 'Earnings', nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2),
571 'Supplemental Earnings', nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2),
572 'Taxable Benefits', nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2),
573 '')
574 INTO l_sf_ele_info_category
575 FROM dual;
576 --
577 -- Update user-specified Classification, Category,
578 -- Processing Type and Standard Link.
579 --
580 pay_shadow_element_api.update_shadow_element
581 (p_validate => false
582 ,p_effective_date => p_ele_eff_start_date
583 ,p_element_type_id => l_element_type_id
584 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
585 ,p_post_termination_rule => p_termination_rule -- bug 8491239
586 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
587 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
588 ,p_description => p_ele_description
589 ,p_reporting_name => p_ele_reporting_name
590 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
591 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
592 ,p_element_information2 => p_ele_calc_method
593 ,p_element_information4 => p_ele_eoy_type
594 ,p_element_information18 => p_ele_t4a_footnote
595 ,p_element_information19 => p_ele_rl1_footnote
596 ,p_element_information20 => p_ele_registration_number
597 -- ,p_element_information10 => l_pri_bal_id
598 -- ,p_element_information12 => l_hrs_bal_id
599 ,p_skip_formula => l_skip_formula
600 ,p_object_version_number => l_ele_obj_ver_number);
601 --
602 -- Update user-specified Classification on Special Features Element.
603 -- Only for Non NTG elements.
604 --
605 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',8);
606 --
607 if p_ele_calc_rule_code <> 'NTG FLT' then
608 pay_shadow_element_api.update_shadow_element
609 (p_validate => false
610 ,p_effective_date => p_ele_eff_start_date
611 ,p_element_type_id => l_sf_element_type_id
612 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
613 ,p_post_termination_rule => p_termination_rule -- bug 8491239
614 ,p_element_information_category => l_sf_ele_info_category
615 ,p_reporting_name => p_ele_reporting_name||' SF'
616 ,p_element_information1 => l_sf_ele_category
617 ,p_element_information3 => l_roe_allocation_by
618 ,p_object_version_number => l_sf_ele_obj_ver_number);
619 end if;
620 --
621 --
622 -- Update user-specified Classification Special Inputs if it exists.
623 -- Only for Non NTG elements.
624 --
625 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',9);
626 --
627 if (p_ele_processing_type = 'R') and (p_ele_calc_rule_code <> 'NTG FLT') then
628 pay_shadow_element_api.update_shadow_element
629 (p_validate => false
630 ,p_effective_date => p_ele_eff_start_date
631 ,p_element_type_id => l_si_element_type_id
632 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
633 ,p_post_termination_rule => p_termination_rule -- bug 8491239
634 ,p_reporting_name => p_ele_reporting_name||' SI'
635 ,p_object_version_number => l_si_ele_obj_ver_number);
636 end if;
637 --
638 ---------------------------- Generate Core Objects ------------------------
639 --
640 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',10);
641 --
642 if chk_ca_pay_installed = 'Y' then
643 l_hr_only := FALSE;
644 else
645 l_hr_only := TRUE;
646 end if;
647 --
648 hr_utility.trace('HR ONLY is :'||chk_ca_pay_installed);
649 --
650 pay_element_template_api.generate_part1
651 (p_validate => false
652 ,p_effective_date => p_ele_eff_start_date
653 ,p_hr_only => l_hr_only
654 ,p_hr_to_payroll => false
655 ,p_template_id => l_template_id);
656 --
657 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',11);
658 --
659 if l_hr_only = FALSE then
660 pay_element_template_api.generate_part2
661 (p_validate => false
662 ,p_effective_date => p_ele_eff_start_date
663 ,p_template_id => l_template_id);
664 --
665 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',12);
666 --
667 end if;
668 --
669 -------------------- Get Element Type ID of Base Element ------------------
670 --
671 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',13);
672 --
673 select element_type_id
674 into l_base_element_type_id
675 from pay_element_types_f
676 where element_name = p_ele_name
677 and business_group_id + 0 = p_bg_id;
678
679 /* Create formula result rules ELEMENT_TYPE_ID_PASSED,HOURS_PASSED and
680 RATE_PASSED only if the element has a calculation rule of hours time rate */
681
682 if p_ele_calc_rule_code = 'HXR' and nvl(p_reduce_regular,'N') <> 'Y' then
683
684 open cur_processing_rule_exists(l_base_element_type_id);
685 fetch cur_processing_rule_exists INTO lv_proc_rule_id;
686 if cur_processing_rule_exists%found then
687 close cur_processing_rule_exists;
688 else
689 hr_utility.trace('Processing rule: '||p_ele_name|| ' does not exist');
690 end if;
691
692 open cur_element_type_id(lv_element_name);
693 fetch cur_element_type_id into lv_element_type_id;
694 close cur_element_type_id;
695
696 lv_input_value_name := 'Element Type Id';
697 lv_result_name := 'ELEMENT_TYPE_ID_PASSED';
698 open cur_input_id(lv_element_name, lv_input_value_name);
699 fetch cur_input_id into lv_input_value_id;
700 close cur_input_id;
701
702 hr_utility.trace(to_char(lv_element_type_id)||' '||to_char(lv_input_value_id));
703
704 lv_formula_result_rule_id :=
705 pay_formula_results.ins_form_res_rule(
706 p_business_group_id => p_bg_id,
707 p_legislation_code => NULL,
708 p_legislation_subgroup => NULL,
709 p_effective_start_date => fnd_date.canonical_to_date(
710 '1901/01/01'),
711 p_effective_end_date => fnd_date.canonical_to_date(
712 '4712/12/31'),
713 p_status_processing_rule_id => lv_proc_rule_id,
714 p_input_value_id => lv_input_value_id,
715 p_result_name => lv_result_name,
716 p_result_rule_type => 'I',
717 p_severity_level => NULL,
718 p_element_type_id => lv_element_type_id);
719
720 hr_utility.trace('Creating Result Rule: '|| lv_result_name);
721
722 lv_input_value_name := 'Hours';
723 lv_result_name := 'HOURS_PASSED';
724 open cur_input_id(lv_element_name, lv_input_value_name);
725 fetch cur_input_id into lv_input_value_id;
726 close cur_input_id;
727
728 lv_formula_result_rule_id :=
729 pay_formula_results.ins_form_res_rule(
730 p_business_group_id => p_bg_id,
731 p_legislation_code => NULL,
732 p_legislation_subgroup => NULL,
733 p_effective_start_date => fnd_date.canonical_to_date(
734 '1901/01/01'),
735 p_effective_end_date => fnd_date.canonical_to_date(
736 '4712/12/31'),
737 p_status_processing_rule_id => lv_proc_rule_id,
738 p_input_value_id => lv_input_value_id,
739 p_result_name => lv_result_name,
740 p_result_rule_type => 'I',
741 p_severity_level => NULL,
742 p_element_type_id => lv_element_type_id);
743
744 hr_utility.trace('Creating Result Rule: '|| lv_result_name);
745
746 lv_input_value_name := 'Rate';
747 lv_result_name := 'RATE_PASSED';
748 open cur_input_id(lv_element_name, lv_input_value_name);
749 fetch cur_input_id into lv_input_value_id;
750 close cur_input_id;
751
752 lv_formula_result_rule_id :=
753 pay_formula_results.ins_form_res_rule(
754 p_business_group_id => p_bg_id,
755 p_legislation_code => NULL,
756 p_legislation_subgroup => NULL,
757 p_effective_start_date => fnd_date.canonical_to_date(
758 '1901/01/01'),
759 p_effective_end_date => fnd_date.canonical_to_date(
760 '4712/12/31'),
761 p_status_processing_rule_id => lv_proc_rule_id,
762 p_input_value_id => lv_input_value_id,
763 p_result_name => lv_result_name,
764 p_result_rule_type => 'I',
765 p_severity_level => NULL,
766 p_element_type_id => lv_element_type_id);
767
768 hr_utility.trace('Creating Result Rule: '|| lv_result_name);
769
770 lv_input_value_name := 'Multiple';
771 lv_result_name := 'MULTIPLE_PASSED';
772 open cur_input_id(lv_element_name, lv_input_value_name);
773 fetch cur_input_id into lv_input_value_id;
774 close cur_input_id;
775
776 lv_formula_result_rule_id :=
777 pay_formula_results.ins_form_res_rule(
778 p_business_group_id => p_bg_id,
779 p_legislation_code => NULL,
780 p_legislation_subgroup => NULL,
781 p_effective_start_date => fnd_date.canonical_to_date(
782 '1901/01/01'),
783 p_effective_end_date => fnd_date.canonical_to_date(
784 '4712/12/31'),
785 p_status_processing_rule_id => lv_proc_rule_id,
786 p_input_value_id => lv_input_value_id,
787 p_result_name => lv_result_name,
788 p_result_rule_type => 'I',
789 p_severity_level => NULL,
790 p_element_type_id => lv_element_type_id);
791
792 lv_input_value_name := 'Pay Value';
793 lv_result_name := 'EARNINGS_AMOUNT';
794 open cur_input_id(lv_element_name, lv_input_value_name);
795 fetch cur_input_id into lv_input_value_id;
796 close cur_input_id;
797
798 lv_formula_result_rule_id :=
799 pay_formula_results.ins_form_res_rule(
800 p_business_group_id => p_bg_id,
801 p_legislation_code => NULL,
802 p_legislation_subgroup => NULL,
803 p_effective_start_date => fnd_date.canonical_to_date(
804 '1901/01/01'),
805 p_effective_end_date => fnd_date.canonical_to_date(
806 '4712/12/31'),
807 p_status_processing_rule_id => lv_proc_rule_id,
808 p_input_value_id => lv_input_value_id,
809 p_result_name => lv_result_name,
810 p_result_rule_type => 'I',
811 p_severity_level => NULL,
812 p_element_type_id => lv_element_type_id);
813
814 hr_utility.trace('Creating Result Rule: '|| lv_result_name);
815
816 end if;
817 --
818 ------------------ Get Balance Type IDs to update Flex Info ---------------
819 --
820 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',14);
821 --
822 BEGIN
823 select ptco.core_object_id
824 into l_pri_bal_id
825 from pay_shadow_balance_types psbt,
826 pay_template_core_objects ptco
827 where psbt.template_id = l_template_id
828 and psbt.balance_name = p_ele_name
829 and ptco.template_id = psbt.template_id
830 and ptco.shadow_object_id = psbt.balance_type_id;
831 --
832 EXCEPTION WHEN NO_DATA_FOUND THEN
833 --
834 -- Is this NTG element?
835 -- NTG template does not have record in pay_template_core_objects.
836 --
837 IF p_ele_calc_rule_code = 'NTG FLT' then
838 select balance_type_id
839 into l_pri_bal_id
840 from pay_shadow_balance_types
841 where template_id = l_template_id
842 and balance_name = p_ele_name;
843 ELSE
844 NULL;
845 END IF;
846 END;
847 --
848 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',15);
849 --
850 BEGIN
851 select ptco.core_object_id
852 into l_hrs_bal_id
853 from pay_shadow_balance_types psbt,
854 pay_template_core_objects ptco
855 where psbt.template_id = l_template_id
856 and psbt.balance_name = p_ele_name||' Hours'
857 and ptco.template_id = psbt.template_id
858 and ptco.shadow_object_id = psbt.balance_type_id;
859 --
860 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',16);
861 --
862 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
863 END;
864 --
865 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',17);
866 --
867 if (p_sep_check_option = 'N' and p_ele_calc_rule_code <> 'NTG FLT') then
868 update pay_element_types_f
869 set element_information10 = l_pri_bal_id,
870 element_information12 = l_hrs_bal_id,
871 process_mode = decode(l_sep_check_create,'Y','S','N')
872 where element_type_id = l_base_element_type_id
873 and business_group_id + 0 = p_bg_id;
874 elsif (p_sep_check_option = 'N' and p_ele_calc_rule_code = 'NTG FLT') then
875 update pay_element_types_f
876 set element_information10 = l_pri_bal_id,
877 element_information12 = l_hrs_bal_id,
878 process_mode = 'P'
879 where element_type_id = l_base_element_type_id
880 and business_group_id + 0 = p_bg_id;
881 --
882 elsif (p_sep_check_option = 'Y') then
883 update pay_element_types_f
884 set element_information10 = l_pri_bal_id,
885 element_information12 = l_hrs_bal_id,
886 process_mode = 'S'
887 where element_type_id = l_base_element_type_id
888 and business_group_id + 0 = p_bg_id;
889 --
890 end if;
891
892 FOR dim IN get_asg_gre_run_dim_id LOOP
893 l_asg_gre_run_dim_id := dim.balance_dimension_id;
894 END LOOP;
895
896 FOR dim IN get_asg_jd_gre_run_dim_id LOOP
897 l_asg_jd_gre_run_dim_id := dim.balance_dimension_id;
898 END LOOP;
899
900 UPDATE pay_defined_balances
901 SET save_run_balance = 'Y'
902 WHERE balance_type_id = l_pri_bal_id
903 AND balance_dimension_id IN
904 (l_asg_gre_run_dim_id,
905 l_asg_jd_gre_run_dim_id)
906 AND business_group_id = p_bg_id;
907
908 /* Fix for Bug#2219028, setting the termination rule to 'Last Standard
909 Process Date' for all Recurring Elements */
910
911 If p_ele_processing_type = 'R' then
912
913 update pay_element_types_f
914 set post_termination_rule = p_termination_rule -- Bug 2646705
915 where element_type_id = l_base_element_type_id
916 and business_group_id + 0 = p_bg_id;
917
918 End if;
919 /* End of bug fix#2219028 */
920 --
921 --
922 -- Added update for jurisdiction level, this needs to be set for all balances
923 -- to '2'. This is currently a hardcoded update to base table as the balance
924 -- apis do not support jurisdiction_level.
925 --
926 update_jd_level_on_balance(l_template_id);
927 --
928 -- Added NTG specific updates.
929 --
930 IF p_ele_calc_rule_code = 'NTG FLT' then
931 update_ntg_element(l_base_element_type_id,
932 p_ele_eff_start_date,
933 p_bg_id);
934 END IF;
935 --
936 --
937 /* Defaulting Values for Regular Earnings Adjustment Rule.
938 Bug #1588225 */
939 /* Changed the defaulting of Earnings to A from T, bugfix #2402284 */
940 if p_ele_classification in ('Supplemental Earnings','Earnings') then
941 /* if p_ele_calc_rule_code = 'HXR' then */
942 if nvl(p_reduce_regular,'N') <> 'Y' then
943
944 if p_ele_classification = 'Earnings' then
945 update pay_element_types_f
946 set element_information9 = 'A'
947 where element_type_id = l_base_element_type_id
948 and business_group_id + 0 = p_bg_id;
949 elsif p_ele_classification = 'Supplemental Earnings' then
950 update pay_element_types_f
951 set element_information9 = 'A'
952 where element_type_id = l_base_element_type_id
953 and business_group_id + 0 = p_bg_id;
954 end if;
955
956 elsif nvl(p_reduce_regular,'N') = 'Y' then
957 update pay_element_types_f
958 set element_information9 = 'R'
959 where element_type_id = l_base_element_type_id
960 and business_group_id + 0 = p_bg_id;
961 end if;
962 /* end if; */
963 end if;
964
965 --
966 -- Updating element_information3 of the Base Element
967 -- to 'DE'(Date Earned') for Elements of Classification
968 -- 'Earnings','Supplemental Earnings','Taxable Benefits'.
969 --
970 IF p_ele_classification in
971 ('Supplemental Earnings','Earnings','Taxable Benefits') then
972 UPDATE pay_element_types_f
973 SET element_information3 = 'DE'
974 WHERE element_type_id = l_base_element_type_id
975 AND business_group_id + 0 = p_bg_id;
976 END IF;
977 --
978
979 --
980 -- Creating element type usages for exclusion
981 --
982
983 begin
984 --hr_utility.trace_on(null,'ELEMENT');
985 for etu in c_ele_tp_usg(p_bg_id, p_ele_name)
986 loop
987
988 hr_utility.trace('etu.element_name : '||etu.element_name);
989 hr_utility.trace('p_ele_name : '||p_ele_name);
990 hr_utility.trace('etu.element_information2 : '||etu.element_information2);
991 hr_utility.trace('etu.element_information4 : '||etu.element_information4);
992
993 if etu.element_name = p_ele_name then
994
995 if p_ele_classification = 'Non-payroll Payments' then
996 if etu.element_information4 is not null then
997 select 'REG_' ||
998 replace(etu.element_information4,'/','_') earn_shortname
999 into lv_earn_shortname
1000 from dual;
1001 else
1002 lv_earn_shortname := 'REG_T4_RL1';
1003 end if;
1004 else
1005 select decode(etu.element_information2, 'R','REG_',
1006 'N','NP_',
1007 'L','LS_', NULL)||
1008 replace(etu.element_information4,'/','_') earn_shortname
1009 into lv_earn_shortname
1010 from dual;
1011 end if;
1012
1013 end if;
1014 hr_utility.trace('ln_run_type_id : '||ln_run_type_id);
1015
1016 for prt in c_run_tp
1017 loop
1018
1019 if instr(prt.shortname, lv_earn_shortname) > 0 then
1020 lv_inclusion_flag := 'N';
1021 lv_usage_type := 'T';
1022 else
1023 lv_inclusion_flag := 'N';
1024 lv_usage_type := NULL;
1025 end if;
1026
1027 pay_element_type_usage_api.create_element_type_usage(
1028 p_effective_date => etu.effective_start_date
1029 ,p_run_type_id => prt.run_type_id
1030 ,p_element_type_id => etu.element_type_id
1031 ,p_business_group_id => etu.business_group_id
1032 ,p_legislation_code => etu.legislation_code
1033 ,p_usage_type => lv_usage_type
1034 ,p_inclusion_flag => lv_inclusion_flag
1035 ,p_element_type_usage_id => ln_element_type_usage_id
1036 ,p_object_version_number => ln_object_version_number
1037 ,p_effective_start_date => ld_effective_start_date
1038 ,p_effective_end_date => ld_effective_end_date);
1039
1040
1041 end loop; -- cursor c_run_tp run_types
1042
1043 end loop; -- cursor c_ele_tp_usg element_type_usages
1044
1045 exception
1046 when others then
1047 null;
1048 end;
1049
1050 begin
1051 /* Bugfix : 2851568. Feed Taxable Benefits for Quebec for all Taxable
1052 Benefits Element with Category PHSP */
1053
1054 hr_utility.trace('1 element type id is '||to_char(l_element_type_id));
1055
1056 select element_type_id
1057 into l_element_type_id
1058 from pay_element_types_f
1059 where business_group_id = p_bg_id
1060 and element_name = p_ele_name;
1061
1062 lv_input_value_name := 'Pay Value';
1063 open cur_input_id2(l_base_element_type_id, lv_input_value_name);
1064 fetch cur_input_id2 into l_pay_value_iv_id;
1065 close cur_input_id2;
1066
1067 hr_utility.trace('2');
1068 -- if p_ele_category = 'Private Health Services Plan' then
1069 if p_ele_category = 'PHSP' then
1070 hr_utility.trace('3 input value id ' || to_char(l_pay_value_iv_id));
1071
1072
1073 select balance_type_id
1074 into l_balance_type_id
1075 from pay_balance_types
1076 where balance_name = 'Taxable Benefits for Quebec';
1077
1078 pay_balance_feeds_f_pkg.insert_row (l_balance_row_id,
1079 l_balance_feed_id,
1080 p_ele_eff_start_date,
1081 p_ele_eff_end_date,
1082 p_bg_id,
1083 'CA',
1084 l_balance_type_id,
1085 l_pay_value_iv_id,
1086 '1',
1087 NULL);
1088
1089 hr_utility.trace('4 input value id ' || to_char(l_balance_feed_id));
1090 end if;
1091 exception
1092 when others then
1093 hr_utility.trace('5');
1094 null;
1095 end;
1096 hr_utility.trace_off;
1097 --
1098 --
1099
1100 ------------------ Conclude Create_User_Init_Earning Main -----------------
1101 --
1102 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_earning',18);
1103 --
1104 RETURN l_base_element_type_id;
1105 --
1106 END create_user_init_earning;
1107 --
1108 --
1109 ------------------------- Deletion procedures -----------------------------
1110 --
1111 PROCEDURE delete_user_init_earning (
1112 p_business_group_id in number,
1113 p_ele_type_id in number,
1114 p_ele_name in varchar2,
1115 p_del_sess_date in date,
1116 p_del_val_start_date in date,
1117 p_del_val_end_date in date) IS
1118 -- local constants
1119 c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
1120
1121 -- local vars
1122 l_del_sess_date DATE := NULL;
1123 l_del_val_start DATE := NULL;
1124 l_del_val_end DATE := NULL;
1125
1126 l_template_id NUMBER(9);
1127 --
1128 BEGIN
1129 -- Populate vars.
1130 l_del_val_end := nvl(p_del_val_end_date, c_end_of_time);
1131 l_del_val_start := nvl(p_del_val_start_date, sysdate);
1132 l_del_sess_date := nvl(p_del_sess_date, sysdate);
1133 --
1134 hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',1);
1135 --
1136 select template_id
1137 into l_template_id
1138 from pay_element_templates
1139 where base_name = p_ele_name
1140 and business_group_id = p_business_group_id
1141 and template_type = 'U';
1142 --
1143 hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',2);
1144 --
1145 begin
1146 delete from pay_element_type_usages_f
1147 where element_type_id in ( select element_type_id
1148 from pay_element_types_f
1149 where ( element_name = p_ele_name or
1150 element_name =
1151 p_ele_name ||' Special Inputs' )
1152 and business_group_id = p_business_group_id );
1153 --
1154 hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',3);
1155 --
1156 exception
1157 when others then
1158 null;
1159 end;
1160
1161 pay_element_template_api.delete_user_structure
1162 (p_validate => false
1163 ,p_drop_formula_packages => true
1164 ,p_template_id => l_template_id);
1165 --
1166 hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',4);
1167 --
1168 END delete_user_init_earning;
1169 ------------------------------------------------------------------------
1170 -- PROCEDURE UPDATE_JD_LEVEL_ON_BALANCE
1171 -- Update for jurisdiction level, this needs to be set for all balances
1172 -- to '2'. This is currently a hardcoded update to base table as the balance
1173 -- apis do not support jurisdiction_level.
1174 ------------------------------------------------------------------------
1175 PROCEDURE UPDATE_JD_LEVEL_ON_BALANCE(p_template_id in number) is
1176 --
1177 CURSOR get_balance_type_ids(p_template_id number) IS
1178 select ptco.core_object_id
1179 from pay_template_core_objects ptco
1180 where ptco.template_id = p_template_id
1181 and ptco.core_object_type = 'BT';
1182 --
1183 BEGIN
1184 --
1185 FOR each_balance in get_balance_type_ids(p_template_id) LOOP
1186 --
1187 UPDATE pay_balance_types
1188 SET jurisdiction_level = 2
1189 WHERE balance_type_id = each_balance.core_object_id;
1190 --
1191 END LOOP;
1192 --
1193 END UPDATE_JD_LEVEL_ON_BALANCE;
1194 ------------------------------------------------------------------------
1195 PROCEDURE update_ntg_element(p_base_element_type_id in NUMBER,
1196 p_ele_eff_start_date in DATE,
1197 p_bg_id in NUMBER) IS
1198 --
1199 CURSOR c_iter_formula_id IS
1200 SELECT formula_id
1201 FROM ff_formulas_f
1202 WHERE formula_name = 'CA_ITER_GROSSUP'
1203 and legislation_code = 'CA';
1204
1205 CURSOR c_input_value_id IS
1206 SELECT input_value_id, name
1207 FROM pay_input_values_f
1208 WHERE element_type_id = p_base_element_type_id;
1209
1210 CURSOR c_seeded_elmt_id IS
1211 SELECT element_type_id
1212 FROM pay_element_types_f
1213 WHERE upper(element_name) = 'FED_GROSSUP_ADJUSTMENT'
1214 AND legislation_code = 'CA';
1215
1216 CURSOR c_seeded_elmt_iv_id(p_seed_ele_type_id number) IS
1217 SELECT input_value_id
1218 FROM pay_input_values_f
1219 WHERE element_type_id = p_seed_ele_type_id
1220 AND upper(name) = 'AMOUNT';
1221
1222 CURSOR c_base_elmt_spr_id IS
1223 SELECT status_processing_rule_id
1224 FROM pay_status_processing_rules_f
1225 WHERE element_type_id = p_base_element_type_id;
1226
1227 l_iter_formula_id NUMBER;
1228 l_iter_rule_id NUMBER;
1229 l_iter_rule_ovn NUMBER;
1230 l_effective_start_date DATE;
1231 l_effective_end_date DATE;
1232 l_insert VARCHAR2(1) := 'N';
1233 l_result_name VARCHAR2(20);
1234 l_iterative_rule_type VARCHAR2(1);
1235 l_iv_id NUMBER;
1236 l_seeded_ele_type_id NUMBER;
1237 l_nextval NUMBER;
1238 l_seeded_input_val_id NUMBER;
1239 l_status_pro_rule_id NUMBER;
1240 l_proc VARCHAR2(50) := 'pay_ca_user_init_earn.update_ntg_element';
1241
1242 BEGIN
1243 --
1244 OPEN c_iter_formula_id;
1245 FETCH c_iter_formula_id into l_iter_formula_id;
1246 IF c_iter_formula_id%NOTFOUND then
1247 hr_utility.set_location(l_proc,10);
1248 hr_utility.set_message(800,'ITERATIVE FORMULA NOT FOUND');
1249 hr_utility.raise_error;
1250 END IF;
1251 CLOSE c_iter_formula_id;
1252 -------------------------
1253 -- Set iterative formula.
1254 -------------------------
1255 UPDATE pay_element_types_f
1256 SET iterative_formula_id = l_iter_formula_id,
1257 iterative_flag = 'Y',
1258 grossup_flag = 'Y'
1259 WHERE element_type_id = p_base_element_type_id
1260 AND business_group_id + 0 = p_bg_id;
1261 --
1262 hr_utility.set_location(l_proc,20);
1263 --
1264 ---------------------------------
1265 -- Set iterative processing rules
1266 ---------------------------------
1267 FOR c_iv_rec in c_input_value_id LOOP
1268 IF c_iv_rec.name = 'Additional Amount'
1269 then l_result_name := 'ADDITIONAL_AMOUNT';
1270 l_iterative_rule_type := 'A';
1271 l_iv_id := c_iv_rec.input_value_id;
1272 l_insert := 'Y';
1273
1274 elsif c_iv_rec.name = 'Low Gross'
1275 then l_result_name := 'LOW_GROSS';
1276 l_iterative_rule_type := 'A';
1277 l_iv_id := c_iv_rec.input_value_id;
1278 l_insert := 'Y';
1279
1280 elsif c_iv_rec.name = 'High Gross'
1281 then l_result_name := 'HIGH_GROSS';
1282 l_iterative_rule_type := 'A';
1283 l_iv_id := c_iv_rec.input_value_id;
1284 l_insert := 'Y';
1285
1286 elsif c_iv_rec.name = 'Remainder'
1287 then l_result_name := 'REMAINDER';
1288 l_iterative_rule_type := 'A';
1289 l_iv_id := c_iv_rec.input_value_id;
1290 l_insert := 'Y';
1291
1292 elsif c_iv_rec.name = 'Pay Value'
1293 -- Using any other Input Value to insert Stopper.
1294 then l_result_name := 'STOPPER';
1295 l_iterative_rule_type := 'S';
1296 l_iv_id := NULL;
1297 l_insert := 'Y';
1298 END IF;
1299 IF l_insert = 'Y' THEN
1300 hr_utility.set_location(l_proc,30);
1301 pay_iterative_rules_api.create_iterative_rule
1302 (
1303 p_effective_date => p_ele_eff_start_date
1304 ,p_element_type_id => p_base_element_type_id
1305 ,p_result_name => l_result_name
1306 ,p_iterative_rule_type => l_iterative_rule_type
1307 ,p_input_value_id => l_iv_id
1308 ,p_severity_level => NULL
1309 ,p_business_group_id => p_bg_id
1310 ,p_legislation_code => 'CA'
1311 ,p_iterative_rule_id => l_iter_rule_id
1312 ,p_object_version_number => l_iter_rule_ovn
1313 ,p_effective_start_date => l_effective_start_date
1314 ,p_effective_end_date => l_effective_end_date
1315 );
1316 END IF;
1317 l_insert := 'N';
1318 END LOOP;
1319 ------------------------------------------------------------------
1320 -- Amount(Desired NTG Amount) needs to feed the seeded element
1321 -- FED_GROSSUP_ADJUSTMENT input value of Amount.
1322 -- Thus need to get the element_type_id of the seeded element
1323 -- and input_value_id of the Amount from the seeded element.
1324 ------------------------------------------------------------------
1325 hr_utility.set_location(l_proc,40);
1326 --
1327 OPEN c_seeded_elmt_id;
1328 FETCH c_seeded_elmt_id into l_seeded_ele_type_id;
1329 IF c_seeded_elmt_id%NOTFOUND then
1330 hr_utility.set_location(l_proc,45);
1331 hr_utility.set_message(800,'FED_GROSSUP_ADJUSTMENT NOT FOUND');
1332 hr_utility.raise_error;
1333 END IF;
1334 CLOSE c_seeded_elmt_id;
1335 --
1336 hr_utility.set_location(l_proc,41);
1337 --
1338 OPEN c_seeded_elmt_iv_id(l_seeded_ele_type_id);
1339 FETCH c_seeded_elmt_iv_id into l_seeded_input_val_id;
1340 IF c_seeded_elmt_iv_id%NOTFOUND then
1341 hr_utility.set_location(l_proc,47);
1342 hr_utility.set_message(800,'INPUT VALUE NOT FOUND');
1343 hr_utility.raise_error;
1344 END IF;
1345 CLOSE c_seeded_elmt_iv_id;
1346 --
1347 hr_utility.set_location(l_proc,42);
1348 --
1349 SELECT pay_formula_result_rules_s.nextval
1350 INTO l_nextval
1351 FROM dual;
1352 --
1353 hr_utility.set_location(l_proc,43);
1354 --
1355 OPEN c_base_elmt_spr_id;
1356 FETCH c_base_elmt_spr_id into l_status_pro_rule_id;
1357 IF c_base_elmt_spr_id%NOTFOUND then
1358 hr_utility.set_location(l_proc,49);
1359 hr_utility.set_message(800,'STATUS PROC RULE NOT FOUND');
1360 hr_utility.raise_error;
1361 END IF;
1362 CLOSE c_base_elmt_spr_id;
1363 --
1364 hr_utility.set_location(l_proc,50);
1365 --
1366 INSERT INTO PAY_FORMULA_RESULT_RULES_F
1367 (formula_result_rule_id,
1368 effective_start_date,
1369 effective_end_date,
1370 business_group_id,
1371 legislation_code,
1372 element_type_id,
1373 status_processing_rule_id,
1374 result_name,
1375 result_rule_type,
1376 input_value_id,
1377 last_update_date,
1378 last_updated_by,
1379 last_update_login,
1380 created_by,
1381 creation_date)
1382 VALUES
1383 (l_nextval,
1384 trunc(TO_DATE('0001/01/01', 'YYYY/MM/DD')),
1385 trunc(TO_DATE('4712/12/31', 'YYYY/MM/DD')),
1386 p_bg_id,
1387 'CA',
1388 l_seeded_ele_type_id,
1389 l_status_pro_rule_id,
1390 'AMOUNT',
1391 'I',
1392 l_seeded_input_val_id,
1393 sysdate,
1394 -1,
1395 -1,
1396 -1,
1397 sysdate);
1398 END update_ntg_element;
1399
1400 END pay_ca_user_init_earn;