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