[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_USER_INIT_DEDN
Source
1 PACKAGE BODY pay_ca_user_init_dedn AS
2 /* $Header: pycauidt.pkb 120.0.12010000.3 2009/05/18 11:44:52 sapalani ship $ */
3 /*
4 */
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1998 Oracle Corporation. *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation, *
18 * 500 Oracle Parkway, Redwood City, CA, 94065. *
19 * *
20 ******************************************************************
21
22 Name : pay_ca_user_init_dedn
23 Filename : pycauidt.pkb
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ---- ---- ---- ------ -----------
28 10-NOV-98 R.Murthy 115.0 First Created.
29 27-APR-99 M.Mukherjee Passed the paramether p_description
30 to update_shadow_element procedure
31 07-JUN-99 R. Murthy 115.4 Created exclusion rule for start
32 rule - if it is 'Earnings Threshold',
33 the template engine creates the input
34 value 'Threshold Amount', and the
35 element uses the skip rule
36 'THRESHOLD_SKIP_RULE'.
37 Update shadow structure also updates
38 the user-entered element reporting
39 name and benefit classification.
40 Added a check to see if the element
41 being created has the same name as
42 that of an existing balance (seeded or
43 otherwise). If yes, an error is
44 raised. This prevents users from
45 creating elements with reserved
46 words as names.
47 05-APR-00 RThirlby 115.6 Added call to new procedure
48 pay_ca_user_init_earn.update_jd_level-
49 _on_balance. This updates jurisdiction_
50 level on pay_balance_types for all
51 balances. Call added to create user-
52 _init_deduction. NB. THIS WILL NEED TO
53 BE ADDED TO CREATE_USER_INIT_GARNISH-
54 MENT WHEN GARNISHMENTS ARE ADDED TO
55 R11i.
56 11-APR-00 ACai 115.7 Replaced the code with Ver. 110.14 to include garnishment process for R11i.
57
58 11-APR-00 mmukherj 115.8 Added update and insert of footnote
59 and registration no for Year end info
60 11-APR-02 ssattini 115.9 Fixed the bug#2304888 and also
61 also added dbdrv.
62 11-APR-2002 SSattini 115.10 Corrected GSCC complaint.
63 18-FEB-2003 vpandya 115.11 ,12 Creating element type usages for
64 deduction elements which has tax proc
65 type is 'Regular'.
66 05-AUG-2003 ssouresr 115.13 Saving run balances for _GRE_RUN
67 _GRE_JD_RUN, _ASG_GRE_RUN and
68 _ASG_JD_GRE_RUN on pay_defined_balances
69 05-AUG-2003 ssouresr 115.14 Removed _GRE_RUN and _GRE_JD_RUN from
70 previous change as these dimensions are
71 not required for saving nonseeded
72 balances
76 recurring elements. Fix for
73 26-SEP-2003 ssattini 115.15 Added update to set the
74 post_termination_rule to 'Last
75 Standard Process Date' for all
77 bug#2219028.
78 18-MAY-2009 sapalani 115.16 5676728 Removed the skip formula
79 FREQ_RULE_SKIP_FORMULA from
80 deduction elements.
81 */
82
83 --
84 --
85 ----------------- create_element_type_usages -----------------
86 --
87 --
88
89 PROCEDURE create_element_type_usages (
90 p_element_name in varchar2,
91 p_bg_id in number,
92 p_ele_proc_run_type in varchar2 ) IS
93 cursor c_ele_tp_usg is
94 select pet.element_type_id
95 ,prt.run_type_id
96 ,'N' inclusion_flag
97 ,pet.effective_start_date
98 ,pet.effective_end_date
99 ,pet.legislation_code
100 ,pet.business_group_id
101 ,NULL usage_type
102 from pay_element_types_f pet
103 ,pay_run_types_f prt
104 where pet.element_name = p_element_name
105 and pet.business_group_id = p_bg_id
106 and prt.legislation_code = 'CA'
107 and ( prt.shortname like 'REG%' or
108 prt.shortname like 'NP%' or
109 prt.shortname like 'LS%' )
110 and prt.shortname <> 'REG_T4_RL1'
111 and prt.run_method = 'C'
112 and nvl(prt.srs_flag,'N') <> 'Y';
113
114 ln_element_type_usage_id number;
115 ln_object_version_number number;
116 ld_effective_start_date date;
117 ld_effective_end_date date;
118
119 BEGIN
120
121 for etu in c_ele_tp_usg loop
122 pay_element_type_usage_api.create_element_type_usage(
123 p_effective_date => etu.effective_start_date
124 ,p_run_type_id => etu.run_type_id
125 ,p_element_type_id => etu.element_type_id
126 ,p_business_group_id => etu.business_group_id
127 ,p_legislation_code => etu.legislation_code
128 ,p_usage_type => etu.usage_type
129 ,p_inclusion_flag => etu.inclusion_flag
130 ,p_element_type_usage_id => ln_element_type_usage_id
131 ,p_object_version_number => ln_object_version_number
132 ,p_effective_start_date => ld_effective_start_date
133 ,p_effective_end_date => ld_effective_end_date);
134 end loop;
135
136 Exception
137 when others then
138 null;
139 END create_element_type_usages;
140 --
141 --
142 ------------------------- create_user_init_deduction ----------------------------
143 --
144 FUNCTION create_user_init_deduction (
145 p_ele_name in varchar2,
146 p_ele_reporting_name in varchar2,
147 p_ele_description in varchar2 default NULL,
148 p_ele_classification in varchar2,
149 p_ben_class_id in number,
150 p_ele_category in varchar2 default NULL,
151 p_ele_processing_type in varchar2,
152 p_ele_priority in number default NULL,
153 p_ele_standard_link in varchar2 default 'N',
154 p_ele_proc_runtype in varchar2,
155 p_ele_start_rule in varchar2,
156 p_ele_stop_rule in varchar2,
157 p_ele_calc_rule in varchar2,
158 p_ele_calc_rule_code in varchar2,
159 p_ele_insuff_funds in varchar2,
160 p_ele_insuff_funds_code in varchar2,
161 p_ele_t4a_footnote in varchar2,
162 p_ele_rl1_footnote in varchar2,
163 p_ele_registration_number in varchar2,
164 p_ele_eff_start_date in date default NULL,
165 p_ele_eff_end_date in date default NULL,
166 p_bg_id in number) RETURN NUMBER IS
167 --
168
169 CURSOR get_asg_gre_run_dim_id IS
170 SELECT balance_dimension_id
171 FROM pay_balance_dimensions
172 WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
173 AND legislation_code = 'CA';
174
175 CURSOR get_asg_jd_gre_run_dim_id IS
176 SELECT balance_dimension_id
177 FROM pay_balance_dimensions
178 WHERE dimension_name = 'Assignment in JD within GRE Run'
179 AND legislation_code = 'CA';
180
181 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
182 l_asg_jd_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
183
184
185 -- global constants
186 c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
187
188 -- global vars
189 g_eff_start_date DATE;
190 g_eff_end_date DATE;
191
192 -- local vars
193 l_hr_only BOOLEAN;
194 l_reserved VARCHAR2(1) := 'N';
195 l_source_template_id NUMBER(9); -- Source Template ID.
196 l_template_id NUMBER(9); -- Template ID.
197 l_object_version_number NUMBER(9); -- Object Version Number
198 l_arrearage_create VARCHAR2(1);
199 l_total_owed_create VARCHAR2(1);
200 l_threshold_amt_create VARCHAR2(1);
201
202 l_bg_name VARCHAR2(60); -- Get from bg short name passed in.
203 l_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
204 l_ele_obj_ver_number NUMBER(9); -- Object Version Number
208 l_si_ele_obj_ver_number NUMBER(9); -- Object Version Number
205 l_sf_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
206 l_sf_ele_obj_ver_number NUMBER(9); -- Object Version Number
207 l_si_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
209 l_base_element_type_id NUMBER(9); -- Populated by insertion of element type.
210
211 l_pri_bal_id NUMBER(9); -- Get from pay_balance_types
212 l_accr_bal_id NUMBER(9); -- Get from pay_balance_types
213 l_arr_bal_id NUMBER(9); -- Get from pay_balance_types
214 l_not_taken_bal_id NUMBER(9); -- Get from pay_balance_types
215 l_proc_run_type VARCHAR2(30); -- User-specified
216
217 l_ele_repname VARCHAR2(30);
218 l_ben_class_name VARCHAR2(80);
219 l_skip_formula VARCHAR2(80);
220 l_primary_class_id NUMBER(9);
221 l_class_lo_priority NUMBER(9);
222 l_class_hi_priority NUMBER(9);
223
224 --
225 ---------------------------- get_template_id -------------------------------
226 --
227 FUNCTION get_template_id (p_legislation_code in varchar2,
228 p_calc_rule_code in varchar2)
229 RETURN number IS
230 -- local vars
231 l_template_id NUMBER(9);
232 l_template_name VARCHAR2(80);
233
234 BEGIN
235 --
236 hr_utility.set_location('pay_ca_user_init_dedn.get_template_id',1);
237 --
238 if p_calc_rule_code = 'FLT' then
239 l_template_name := 'Flat Amount Deduction';
240 --l_template_name := 'Test Flat Amount Deductions';
241 elsif p_calc_rule_code = 'PCT' then
242 l_template_name := 'Percent of Earnings Deduction';
243 else
244 hr_utility.set_location('pay_ca_user_init_dedn.get_template_id',2);
245 hr_utility.set_message(801,'HR_XXXXX_INVALID_CALC_RULE_DEDN');
246 hr_utility.raise_error;
247 end if;
248 --
249 hr_utility.set_location('pay_ca_user_init_dedn.get_template_id',3);
250 hr_utility.trace('Template Name is :'||l_template_name||'****'||
251 'Legislation is :'||p_legislation_code);
252 --
253 select template_id
254 into l_template_id
255 from pay_element_templates
256 where template_name = l_template_name
257 and legislation_code = p_legislation_code
258 and business_group_id is NULL
259 and template_type = 'T';
260 --
261 hr_utility.set_location('pay_ca_user_init_dedn.get_template_id',4);
262 --
263 RETURN l_template_id;
264 --
265 END get_template_id;
266
267 --
268 --------------------------- chk_ca_pay_installed ---------------------------
269 --
270 FUNCTION chk_ca_pay_installed
271 RETURN varchar2 IS
272
273 -- local vars
274 l_installed VARCHAR2(1) := 'N';
275
276 BEGIN
277 --
278 hr_utility.set_location('pay_ca_user_init_dedn.chk_ca_pay_installed',1);
279 --
280 BEGIN
281 select 'Y'
282 into l_installed
283 from pay_balance_types
284 where upper(balance_name) = 'FED SUBJECT'
285 and legislation_code = 'CA';
286
287 EXCEPTION WHEN NO_DATA_FOUND THEN
288 l_installed := 'N';
289
290 END;
291 --
292 hr_utility.set_location('pay_ca_user_init_dedn.chk_ca_pay_installed',2);
293 --
294 RETURN (l_installed);
295 --
296 END chk_ca_pay_installed;
297
298 --
299 --
300 ------------------------ create_user_init_deduction Main --------------------
301 --
302 -- Main Procedure
303
304 BEGIN
305 --
306 -- hr_utility.trace_on('Y', 'RANJANA');
307 --
308 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',1);
309 --
310 -- Set session date
311 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
312 --
313 g_eff_start_date := NVL(p_ele_eff_start_date, sysdate);
314 g_eff_end_date := NVL(p_ele_eff_end_date, c_end_of_time);
315 --
316 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',2);
317 --
318 ---------------------------- Check Element Name ---------------------------
319 --
320 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_deduction',25);
321 --
322 BEGIN
323 select 'Y'
324 into l_reserved
325 from pay_balance_types
326 where upper(p_ele_name) = upper(balance_name)
327 and nvl(legislation_code, 'CA') = 'CA'
328 and nvl(business_group_id, p_bg_id) = p_bg_id;
329
330 EXCEPTION WHEN NO_DATA_FOUND THEN
331 l_reserved := 'N';
332
333 END;
334 --
335 if l_reserved = 'Y' then
336 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_deduction',26);
337 hr_utility.set_message(801,'HR_7564_ALL_RES_WORDS');
338 hr_utility.raise_error;
339 end if;
340 --
341 ---------------------------- Get Source Template ID -----------------------
342 --
343 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',3);
344 --
345 l_source_template_id := get_template_id(
346 p_legislation_code => g_template_leg_code,
347 p_calc_rule_code => p_ele_calc_rule_code);
348 --
349 ------------------------ Set Arrearage Creation ---------------------------
350 --
351 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',4);
352 --
353 if p_ele_insuff_funds_code in ('A', 'APD') then
354 l_arrearage_create := 'Y';
355 else
356 l_arrearage_create := 'N';
357 end if;
358 --
359 ------------------------ Set Total Owed Creation --------------------------
360 --
361 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',5);
362 --
363 if UPPER(p_ele_stop_rule) = 'TOTAL REACHED' then
364 l_total_owed_create := 'Y';
365 else
369 if UPPER(p_ele_start_rule) = 'ET' then
366 l_total_owed_create := 'N';
367 end if;
368 --
370 l_threshold_amt_create := 'Y';
371 l_skip_formula := 'THRESHOLD_SKIP_FORMULA';
372 else
373 l_threshold_amt_create := 'N';
374 l_skip_formula := null; --'FREQ_RULE_SKIP_FORMULA'; --Bug 5676728
375 end if;
376 --
377 ---------------------------- Create User Structure ------------------------
378 --
379 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',6);
380 --
381 -- The Configuration Flex segments are as follows:
382 -- Config 1 - exclusion rule - create Arrearage related structures if 'Y'
383 -- Config 2 - exclusion rule - create Total Owed related structures if 'Y'
384 -- Config 3 - exclusion rule - create SI and SF elements if 'R', no if 'N'
385 -- Config 4 - exclusion rule - create Earnings Threshold Input Value if 'Y'
386 --
387 pay_element_template_api.create_user_structure
388 (p_validate => false
389 ,p_effective_date => p_ele_eff_start_date
390 ,p_business_group_id => p_bg_id
391 ,p_source_template_id => l_source_template_id
392 ,p_base_name => p_ele_name
393 ,p_base_processing_priority => p_ele_priority
394 ,p_configuration_information1 => l_arrearage_create
395 ,p_configuration_information2 => l_total_owed_create
396 ,p_configuration_information3 => p_ele_processing_type
397 ,p_configuration_information4 => l_threshold_amt_create
398 ,p_template_id => l_template_id
399 ,p_object_version_number => l_object_version_number);
400 --
401 ---------------------- Get Element Type ID of new Template-----------------
402 --
403 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',7);
404 --
405 select element_type_id, object_version_number
406 into l_element_type_id, l_ele_obj_ver_number
407 from pay_shadow_element_types
408 where template_id = l_template_id
409 and element_name = p_ele_name;
410 --
411 select element_type_id, object_version_number
412 into l_sf_element_type_id, l_sf_ele_obj_ver_number
413 from pay_shadow_element_types
414 where template_id = l_template_id
415 and element_name = p_ele_name||' Special Features';
416 --
417 if p_ele_processing_type = 'R' then
418 select element_type_id, object_version_number
419 into l_si_element_type_id, l_si_ele_obj_ver_number
420 from pay_shadow_element_types
421 where template_id = l_template_id
422 and element_name = p_ele_name||' Special Inputs';
423 end if;
424 --
425 ---------------------------- Update Shadow Structure ----------------------
426 --
427 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',8);
428 --
429 if p_ben_class_id IS NOT NULL then
430 select benefit_classification_name
431 into l_ben_class_name
432 from ben_benefit_classifications
433 where benefit_classification_id = p_ben_class_id;
434 end if;
435 --
436 -- Update user-specified Classification, Processing Type and Standard Link.
437 --
438 pay_shadow_element_api.update_shadow_element
439 (p_validate => false
440 ,p_effective_date => p_ele_eff_start_date
441 ,p_element_type_id => l_element_type_id
442 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
443 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
444 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
445 ,p_description => p_ele_description
446 ,p_reporting_name => p_ele_reporting_name
447 ,p_benefit_classification_name => l_ben_class_name
448 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
449 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
450 ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
451 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
452 -- ,p_element_information18 => nvl(p_ele_t4a_footnote, hr_api.g_varchar2)
453 -- ,p_element_information19 => nvl(p_ele_rl1_footnote, hr_api.g_varchar2)
454 -- ,p_element_information20 => nvl(p_ele_registration_number, hr_api.g_varchar2)
455 -- ,p_element_information10 => l_pri_bal_id
456 -- ,p_element_information11 => l_accr_bal_id
457 -- ,p_element_information12 => l_arr_bal_id
458 -- ,p_element_information13 => l_not_taken_bal_id
459 ,p_skip_formula => l_skip_formula
460 ,p_object_version_number => l_ele_obj_ver_number);
461 --
462 --
463 -- Update user-specified Classification on Special Features Element.
464 --
465 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',9);
466 --
467 pay_shadow_element_api.update_shadow_element
468 (p_validate => false
469 ,p_effective_date => p_ele_eff_start_date
470 ,p_element_type_id => l_sf_element_type_id
471 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
472 ,p_reporting_name => p_ele_reporting_name||' SF'
473 ,p_object_version_number => l_sf_ele_obj_ver_number);
474 --
475 --
476 -- Update user-specified Classification Special Inputs if it exists.
477 --
478 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',10);
479 --
480 if p_ele_processing_type = 'R' then
481 pay_shadow_element_api.update_shadow_element
482 (p_validate => false
486 ,p_reporting_name => p_ele_reporting_name||' SI'
483 ,p_effective_date => p_ele_eff_start_date
484 ,p_element_type_id => l_si_element_type_id
485 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
487 ,p_object_version_number => l_si_ele_obj_ver_number);
488 end if;
489 --
490 ---------------------------- Generate Core Objects ------------------------
491 --
492 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',11);
493 --
494 if chk_ca_pay_installed = 'Y' then
495 l_hr_only := FALSE;
496 else
497 l_hr_only := TRUE;
498 end if;
499 --
500 hr_utility.trace('HR ONLY is :'||chk_ca_pay_installed);
501 --
502 pay_element_template_api.generate_part1
503 (p_validate => false
504 ,p_effective_date => p_ele_eff_start_date
505 ,p_hr_only => l_hr_only
506 ,p_hr_to_payroll => false
507 ,p_template_id => l_template_id);
508 --
509 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',12);
510 --
511 if l_hr_only = FALSE then
512 pay_element_template_api.generate_part2
513 (p_validate => false
514 ,p_effective_date => p_ele_eff_start_date
515 ,p_template_id => l_template_id);
516 end if;
517 --
518 -------------------- Get Element Type ID of Base Element ------------------
519 --
520 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',13);
521 --
522 select element_type_id
523 into l_base_element_type_id
524 from pay_element_types_f
525 where element_name = p_ele_name
526 and business_group_id + 0 = p_bg_id;
527 --
528 ------------------ Get Balance Type IDs to update Flex Info ---------------
529 --
530 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',14);
531 --
532 select ptco.core_object_id
533 into l_pri_bal_id
534 from pay_shadow_balance_types psbt,
535 pay_template_core_objects ptco
536 where psbt.template_id = l_template_id
537 and psbt.balance_name = p_ele_name
538 and ptco.template_id = psbt.template_id
539 and ptco.shadow_object_id = psbt.balance_type_id;
540 --
541 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',15);
542 --
543 if l_arrearage_create = 'Y' then
544 select ptco.core_object_id
545 into l_arr_bal_id
546 from pay_shadow_balance_types psbt,
547 pay_template_core_objects ptco
548 where psbt.template_id = l_template_id
549 and psbt.balance_name = p_ele_name||' Arrears'
550 and ptco.template_id = psbt.template_id
551 and ptco.shadow_object_id = psbt.balance_type_id;
552
553 --
554 select ptco.core_object_id
555 into l_not_taken_bal_id
556 from pay_shadow_balance_types psbt,
557 pay_template_core_objects ptco
558 where psbt.template_id = l_template_id
559 and psbt.balance_name = p_ele_name||' Not Taken'
560 and ptco.template_id = psbt.template_id
561 and ptco.shadow_object_id = psbt.balance_type_id;
562 else
563 l_not_taken_bal_id := '';
564 l_arr_bal_id := '';
565 end if;
566 --
567 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',16);
568 --
569 if l_total_owed_create = 'Y' then
570 select ptco.core_object_id
571 into l_accr_bal_id
572 from pay_shadow_balance_types psbt,
573 pay_template_core_objects ptco
574 where psbt.template_id = l_template_id
575 and psbt.balance_name = p_ele_name||' Accrued'
576 and ptco.template_id = psbt.template_id
577 and ptco.shadow_object_id = psbt.balance_type_id;
578 else
579 l_accr_bal_id := '';
580 end if;
581 --
582 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',17);
583 --
584 update pay_element_types_f
585 set element_information10 = l_pri_bal_id,
586 element_information11 = l_accr_bal_id,
587 element_information12 = l_arr_bal_id,
588 element_information13 = l_not_taken_bal_id,
589 element_information18 = p_ele_t4a_footnote,
590 element_information19 = p_ele_rl1_footnote,
591 element_information20 = p_ele_registration_number
592 where element_type_id = l_base_element_type_id
593 and business_group_id + 0 = p_bg_id;
594 --
595
596 /* Fix for Bug#2219028, setting the termination rule to 'Last Standard
597 Process Date' for all Recurring Elements */
598
599 If p_ele_processing_type = 'R' then
600
601 update pay_element_types_f
602 set post_termination_rule = 'L'
603 where element_type_id = l_base_element_type_id
604 and business_group_id + 0 = p_bg_id;
605
606 End if;
607 /* End of fix for bug#2219028 */
608
609 FOR dim IN get_asg_gre_run_dim_id LOOP
610 l_asg_gre_run_dim_id := dim.balance_dimension_id;
611 END LOOP;
612
613 FOR dim IN get_asg_jd_gre_run_dim_id LOOP
614 l_asg_jd_gre_run_dim_id := dim.balance_dimension_id;
615 END LOOP;
616
617 UPDATE pay_defined_balances
618 SET save_run_balance = 'Y'
619 WHERE balance_type_id = l_pri_bal_id
620 AND balance_dimension_id IN
621 (l_asg_gre_run_dim_id,
622 l_asg_jd_gre_run_dim_id)
626 IF p_ele_proc_runtype = 'REG' THEN
623 AND business_group_id = p_bg_id;
624 --
625
627
628 create_element_type_usages (
629 p_element_name => p_ele_name,
630 p_bg_id => p_bg_id,
631 p_ele_proc_run_type => p_ele_proc_runtype);
632
633 create_element_type_usages (
634 p_element_name => p_ele_name || ' Special Inputs',
635 p_bg_id => p_bg_id,
636 p_ele_proc_run_type => p_ele_proc_runtype);
637
638 END IF;
639 --
640 --
641 ------------------ Update jurisdiction_level on balances --------------------
642 --
643 -- Added update for jurisdiction level, this needs to be set for all balances
644 -- to '2'. This is currently a hardcoded update to base table as the balance
645 -- apis do not support jurisdiction_level.
646 --
647 pay_ca_user_init_earn.update_jd_level_on_balance(l_template_id);
648 --
649 ------------------ Conclude Create_User_Init_Deduction Main -----------------
650 --
651 hr_utility.set_location('pay_ca_user_init_dedn.create_user_init_deduction',18);
652 --
653 RETURN l_base_element_type_id;
654 --
655 END create_user_init_deduction;
656 --
657 --
658 --
659 ------------------------- create_user_init_garnishment -------------------------
660 --
661 --
662 FUNCTION create_user_init_garnishment (
663 p_ele_name in varchar2,
664 p_ele_reporting_name in varchar2,
665 p_ele_description in varchar2 default NULL,
666 p_ele_classification in varchar2,
667 p_ben_class_id in number,
668 p_ele_category in varchar2 default NULL,
669 p_ele_processing_type in varchar2,
670 p_ele_priority in number default NULL,
671 p_ele_standard_link in varchar2 default 'N',
672 p_ele_proc_runtype in varchar2,
673 p_ele_start_rule in varchar2,
674 p_ele_stop_rule in varchar2,
675 p_ele_calc_rule in varchar2,
676 p_ele_calc_rule_code in varchar2,
677 p_ele_insuff_funds in varchar2,
678 p_ele_insuff_funds_code in varchar2,
679 p_ele_t4a_footnote in varchar2,
680 p_ele_rl1_footnote in varchar2,
681 p_ele_registration_number in varchar2,
682 p_ele_eff_start_date in date default NULL,
683 p_ele_eff_end_date in date default NULL,
684 p_bg_id in number) RETURN NUMBER IS
685 --
686
687 CURSOR get_asg_gre_run_dim_id IS
688 SELECT balance_dimension_id
689 FROM pay_balance_dimensions
690 WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
691 AND legislation_code = 'CA';
692
693 CURSOR get_asg_jd_gre_run_dim_id IS
694 SELECT balance_dimension_id
695 FROM pay_balance_dimensions
696 WHERE dimension_name = 'Assignment in JD within GRE Run'
697 AND legislation_code = 'CA';
698
699 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
700 l_asg_jd_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
701
702 -- global constants
703 c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
704
705 -- global vars
706 g_eff_start_date DATE;
707 g_eff_end_date DATE;
708
709 -- local vars
710 l_hr_only BOOLEAN;
711 l_reserved VARCHAR2(1) := 'N';
712 l_source_template_id NUMBER(9); -- Source Template ID.
713 l_template_id NUMBER(9); -- Template ID.
714 l_object_version_number NUMBER(9); -- Object Version Number
715
716 l_bg_name VARCHAR2(60); -- Get from bg short name passed in.
717
718 l_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
719 l_ele_obj_ver_number NUMBER(9); -- Object Version Number
720 l_sf_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
721 l_sf_ele_obj_ver_number NUMBER(9); -- Object Version Number
722 l_sf2_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
723 l_sf2_ele_obj_ver_number NUMBER(9); -- Object Version Number
724 l_si_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
725 l_si_ele_obj_ver_number NUMBER(9); -- Object Version Number
726 l_ver_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
727 l_ver_ele_obj_ver_number NUMBER(9); -- Object Version Number
728 l_fee_element_type_id NUMBER(9); -- Get from pay_shadow_element_types
729 l_fee_ele_obj_ver_number NUMBER(9); -- Object Version Number
730 l_base_element_type_id NUMBER(9); -- Populated by insertion of element type.
731
732 l_pri_bal_id NUMBER(9); -- Get from pay_balance_types
733 l_accr_bal_id NUMBER(9); -- Get from pay_balance_types
734 l_arr_bal_id NUMBER(9); -- Get from pay_balance_types
735 l_repl_bal_id NUMBER(9); -- Get from pay_balance_types
736 l_addl_bal_id NUMBER(9); -- Get from pay_balance_types
737 l_fees_bal_id NUMBER(9); -- Get from pay_balance_types
738 l_not_taken_bal_id NUMBER(9); -- Get from pay_balance_types
739 l_proc_run_type VARCHAR2(30); -- User-specified
740
741 l_ele_repname VARCHAR2(30);
742 l_ben_class_name VARCHAR2(80);
743 l_skip_formula VARCHAR2(80):= 'WAT_SKIP';
744 l_primary_class_id NUMBER(9);
745 l_class_lo_priority NUMBER(9);
746 l_class_hi_priority NUMBER(9);
747 --
751 RETURN number IS
748 ---------------------------- get_template_id -------------------------------
749 --
750 FUNCTION get_template_id (p_legislation_code in varchar2)
752 -- local vars
753 l_template_id NUMBER(9);
754 l_template_name VARCHAR2(80);
755
756 BEGIN
757 --
758 hr_utility.set_location('pay_ca_user_init_garn.get_template_id',1);
759 --
760 l_template_name := 'Generic Involuntary Deduction';
761 --
762 hr_utility.set_location('pay_ca_user_init_garn.get_template_id',3);
763 hr_utility.trace('Template Name is :'||l_template_name||'****'||
764 'Legislation is :'||p_legislation_code);
765 --
766 select template_id
767 into l_template_id
768 from pay_element_templates
769 where template_name = l_template_name
770 and legislation_code = p_legislation_code
771 and business_group_id is NULL
772 and template_type = 'T';
773 --
774 hr_utility.set_location('pay_ca_user_init_garn.get_template_id',4);
775 --
776 RETURN l_template_id;
777 --
778 END get_template_id;
779
780 --
781 --------------------------- chk_ca_pay_installed ---------------------------
782 --
783 FUNCTION chk_ca_pay_installed
784 RETURN varchar2 IS
785
786 -- local vars
787 l_installed VARCHAR2(1) := 'N';
788
789 BEGIN
790 --
791 hr_utility.set_location('pay_ca_user_init_garn.chk_ca_pay_installed',1);
792 --
793 BEGIN
794 select 'Y'
795 into l_installed
796 from pay_balance_types
797 where upper(balance_name) = 'FED SUBJECT'
798 and legislation_code = 'CA';
799
800 EXCEPTION WHEN NO_DATA_FOUND THEN
801 l_installed := 'N';
802
803 END;
804 --
805 hr_utility.set_location('pay_ca_user_init_garn.chk_ca_pay_installed',2);
806 --
807 RETURN (l_installed);
808 --
809 END chk_ca_pay_installed;
810
811 --
812 --
813 ------------------------ create_user_init_garnishment Main --------------------
814 --
815 -- Main Procedure
816
817 BEGIN
818 --
819 -- hr_utility.trace_on('Y', 'RANJANA');
820 --
821 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',1);
822 --
823 -- Set session date
824 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
825 --
826 g_eff_start_date := NVL(p_ele_eff_start_date, sysdate);
827 g_eff_end_date := NVL(p_ele_eff_end_date, c_end_of_time);
828 --
829 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',2);
830 --
831 ---------------------------- Check Element Name ---------------------------
832 --
833 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_garnishment',25);
834 --
835 BEGIN
836 select 'Y'
837 into l_reserved
838 from pay_balance_types
839 where upper(p_ele_name) = upper(balance_name)
840 and nvl(legislation_code, 'CA') = 'CA'
841 and nvl(business_group_id, p_bg_id) = p_bg_id;
842
843 EXCEPTION WHEN NO_DATA_FOUND THEN
844 l_reserved := 'N';
845
846 END;
847 --
848 if l_reserved = 'Y' then
849 hr_utility.set_location('pay_ca_user_init_earn.create_user_init_garnishment',26);
850
851 hr_utility.set_message(801,'HR_7564_ALL_RES_WORDS');
852 hr_utility.raise_error;
853 end if;
854 --
855 ---------------------------- Get Source Template ID -----------------------
856 --
857 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',3);
858 --
859 l_source_template_id := get_template_id(
860 p_legislation_code => g_template_leg_code);
861 --
862 ---------------------- Get Element Type ID of new Template-----------------
863 --
864 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',7);
865 --
866 pay_element_template_api.create_user_structure
867 (p_validate => false
868 ,p_effective_date => p_ele_eff_start_date
869 ,p_business_group_id => p_bg_id
870 ,p_source_template_id => l_source_template_id
871 ,p_base_name => p_ele_name
872 ,p_base_processing_priority => p_ele_priority
873 -- ,p_configuration_information1 => l_arrearage_create
874 -- ,p_configuration_information2 => l_total_owed_create
875 -- ,p_configuration_information3 => p_ele_processing_type
876 -- ,p_configuration_information4 => l_threshold_amt_create
877 ,p_template_id => l_template_id
878 ,p_object_version_number => l_object_version_number);
879 --
880 select element_type_id, object_version_number
881 into l_element_type_id, l_ele_obj_ver_number
882 from pay_shadow_element_types
883 where template_id = l_template_id
884 and element_name = p_ele_name;
885 --
886 select element_type_id, object_version_number
887 into l_sf_element_type_id, l_sf_ele_obj_ver_number
888 from pay_shadow_element_types
889 where template_id = l_template_id
890 and element_name = p_ele_name||' Special Features';
891 --
892 select element_type_id, object_version_number
893 into l_sf2_element_type_id, l_sf2_ele_obj_ver_number
894 from pay_shadow_element_types
895 where template_id = l_template_id
896 and element_name = p_ele_name||' Special Features 2';
897 --
898 select element_type_id, object_version_number
899 into l_fee_element_type_id, l_fee_ele_obj_ver_number
900 from pay_shadow_element_types
901 where template_id = l_template_id
902 and element_name = p_ele_name||' Fees';
903 --
907 where template_id = l_template_id
904 select element_type_id, object_version_number
905 into l_ver_element_type_id, l_ver_ele_obj_ver_number
906 from pay_shadow_element_types
908 and element_name = p_ele_name||' Verifier';
909 --
910 select element_type_id, object_version_number
911 into l_si_element_type_id, l_si_ele_obj_ver_number
912 from pay_shadow_element_types
913 where template_id = l_template_id
914 and element_name = p_ele_name||' Special Inputs';
915 --
916 ---------------------------- Update Shadow Structure ----------------------
917 --
918 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',8);
919 --
920 -- Update user-specified Classification, Processing Type and Standard Link.
921 --
922 pay_shadow_element_api.update_shadow_element
923 (p_validate => false
924 ,p_effective_date => p_ele_eff_start_date
925 ,p_element_type_id => l_element_type_id
926 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
927 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
928 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
929 ,p_description => p_ele_description
930 ,p_reporting_name => p_ele_reporting_name
931 ,p_benefit_classification_name => l_ben_class_name
932 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
933 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
934 -- ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
935 ,p_element_information2 => 'INV_DEDN' /* Default Value as all insuff funds prcessing on entry_information */
936 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
937 ,p_element_information6 => upper('P3') /* Creating all with a low priority default */
938 -- ,p_element_information18 => nvl(p_ele_t4a_footnote, hr_api.g_varchar2)
939 -- ,p_element_information19 => nvl(p_ele_rl1_footnote, hr_api.g_varchar2)
940 -- ,p_element_information20 => nvl(p_ele_registration_number, hr_api.g_varchar2)
941 -- ,p_element_information10 => l_pri_bal_id
942 -- ,p_element_information11 => l_accr_bal_id
943 -- ,p_element_information12 => l_arr_bal_id
944 -- ,p_element_information13 => l_not_taken_bal_id
945 ,p_skip_formula => l_skip_formula
946 ,p_object_version_number => l_ele_obj_ver_number);
947 --
948 --
949 -- Update user-specified Classification on ISpecial Features Element.
950 --
951 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',9);
952
953
954 --
955 pay_shadow_element_api.update_shadow_element
956 (p_validate => false
957 ,p_effective_date => p_ele_eff_start_date
958 ,p_element_type_id => l_sf_element_type_id
959 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
960 ,p_reporting_name => p_ele_reporting_name||' SF'
961 ,p_object_version_number => l_sf_ele_obj_ver_number
962 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
963 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
964 -- ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
965 ,p_element_information2 => 'INV_DEDN' /* Default Value as all insuff funds prcessing on entry_information */
966 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
967 ,p_element_information6 => upper('P3') /* Creating all with a low priority default */
968 );
969 --
970 pay_shadow_element_api.update_shadow_element
971 (p_validate => false
972 ,p_effective_date => p_ele_eff_start_date
973 ,p_element_type_id => l_sf2_element_type_id
974 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
975 ,p_reporting_name => p_ele_reporting_name||' SF 2'
976 ,p_object_version_number => l_sf2_ele_obj_ver_number
977 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
978 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
979 -- ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
980 ,p_element_information2 => 'INV_DEDN' /* Default Value as all insuff funds prcessing on entry_information */
981 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
982 ,p_element_information6 => upper('P3') /* Creating all with a low priority default */
983 );
984 --
985 --
986 -- Update user-specified Classification Special Inputs if it exists.
987 --
988 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',11);
989
990 --
991 pay_shadow_element_api.update_shadow_element
992 (p_validate => false
993 ,p_effective_date => p_ele_eff_start_date
994 ,p_element_type_id => l_si_element_type_id
995 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
996 ,p_reporting_name => p_ele_reporting_name||' SI'
997 ,p_object_version_number => l_si_ele_obj_ver_number
998 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
999 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1000 -- ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
1001 ,p_element_information2 => 'INV_DEDN' /* Default Value as all insuff funds prcessing on entry_information */
1002 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1006 --
1003 ,p_element_information6 => upper('P3') /* Creating all with a low priority default */
1004 );
1005 --
1007 -- Update user-specified Classification on Fees Element.
1008 --
1009 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',12);
1010
1011 --
1012 pay_shadow_element_api.update_shadow_element
1013 (p_validate => false
1014 ,p_effective_date => p_ele_eff_start_date
1015 ,p_element_type_id => l_fee_element_type_id
1016 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1017 ,p_reporting_name => p_ele_reporting_name||' Fees'
1018 ,p_object_version_number => l_fee_ele_obj_ver_number
1019 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
1020 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1021 -- ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
1022 ,p_element_information2 => 'INV_DEDN' /* Default Value as all insuff funds prcessing on entry_information */
1023 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1024 ,p_element_information6 => upper('P3') /* Creating all with a low priority default */
1025 );
1026 --
1027 --
1028 -- Update user-specified Classification on, last but not least, Verifier Element.
1029 --
1030 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',13);
1031
1032 --
1033 pay_shadow_element_api.update_shadow_element
1034 (p_validate => false
1035 ,p_effective_date => p_ele_eff_start_date
1036 ,p_element_type_id => l_ver_element_type_id
1037 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
1038 ,p_reporting_name => p_ele_reporting_name||' Verifier'
1039 ,p_object_version_number => l_ver_ele_obj_ver_number
1040 ,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
1041 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
1042 -- ,p_element_information2 => nvl(p_ele_insuff_funds_code, hr_api.g_varchar2)
1043 ,p_element_information2 => 'INV_DEDN' /* Default Value as all insuff funds prcessing on entry_information */
1044 ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1045 ,p_element_information6 => upper('P3') /* Creating all with a low priority default */
1046 );
1047 --
1048 --
1049 ---------------------------- Generate Core Objects ------------------------
1050 --
1051 --
1052 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',14);
1053
1054 --
1055 if chk_ca_pay_installed = 'Y' then
1056 l_hr_only := FALSE;
1057 else
1058 l_hr_only := TRUE;
1059 end if;
1060 --
1061 hr_utility.trace('HR ONLY is :'||chk_ca_pay_installed);
1062 --
1063 pay_element_template_api.generate_part1
1064 (p_validate => false
1065 ,p_effective_date => p_ele_eff_start_date
1066 ,p_hr_only => l_hr_only
1067 ,p_hr_to_payroll => false
1068 ,p_template_id => l_template_id);
1069 --
1070 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',12);
1071
1072 --
1073 if l_hr_only = FALSE then
1074 pay_element_template_api.generate_part2
1075 (p_validate => false
1076 ,p_effective_date => p_ele_eff_start_date
1077 ,p_template_id => l_template_id);
1078 end if;
1079 --
1080 -------------------- Get Element Type ID of Base Element ------------------
1081 --
1082 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',13);
1083
1084 --
1085 select element_type_id
1086 into l_base_element_type_id
1087 from pay_element_types_f
1088 where element_name = p_ele_name
1089 and business_group_id + 0 = p_bg_id;
1090 --
1091 select element_type_id
1092 into l_fee_element_type_id
1093 from pay_element_types_f
1094 where element_name = p_ele_name||' Fees'
1095 and business_group_id + 0 = p_bg_id;
1096 --
1097 ------------------ Get Balance Type IDs to update Flex Info ---------------
1098 --
1099 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',14);
1100
1101 --
1102 select ptco.core_object_id
1103 into l_pri_bal_id
1104 from pay_shadow_balance_types psbt,
1105 pay_template_core_objects ptco
1106 where psbt.template_id = l_template_id
1107 and psbt.balance_name = p_ele_name
1108 and ptco.template_id = psbt.template_id
1109 and ptco.shadow_object_id = psbt.balance_type_id;
1110 --
1111 select ptco.core_object_id
1112 into l_repl_bal_id
1113 from pay_shadow_balance_types psbt,
1114 pay_template_core_objects ptco
1115 where psbt.template_id = l_template_id
1116 and psbt.balance_name = p_ele_name||' Replacement'
1117 and ptco.template_id = psbt.template_id
1118 and ptco.shadow_object_id = psbt.balance_type_id;
1119 --
1120 select ptco.core_object_id
1121 into l_addl_bal_id
1122 from pay_shadow_balance_types psbt,
1123 pay_template_core_objects ptco
1124 where psbt.template_id = l_template_id
1125 and psbt.balance_name = p_ele_name||' Additional'
1126 and ptco.template_id = psbt.template_id
1127 and ptco.shadow_object_id = psbt.balance_type_id;
1128 --
1129 select ptco.core_object_id
1130 into l_not_taken_bal_id
1131 from pay_shadow_balance_types psbt,
1132 pay_template_core_objects ptco
1133 where psbt.template_id = l_template_id
1137 --
1134 and psbt.balance_name = p_ele_name||' Not Taken'
1135 and ptco.template_id = psbt.template_id
1136 and ptco.shadow_object_id = psbt.balance_type_id;
1138 select ptco.core_object_id
1139 into l_accr_bal_id
1140 from pay_shadow_balance_types psbt,
1141 pay_template_core_objects ptco
1142 where psbt.template_id = l_template_id
1143 and psbt.balance_name = p_ele_name||' Accrued'
1144 and ptco.template_id = psbt.template_id
1145 and ptco.shadow_object_id = psbt.balance_type_id;
1146 --
1147 select ptco.core_object_id
1148 into l_fees_bal_id
1149 from pay_shadow_balance_types psbt,
1150 pay_template_core_objects ptco
1151 where psbt.template_id = l_template_id
1152 and psbt.balance_name = p_ele_name||' Fees'
1153 and ptco.template_id = psbt.template_id
1154 and ptco.shadow_object_id = psbt.balance_type_id;
1155 --
1156 select ptco.core_object_id
1157 into l_arr_bal_id
1158 from pay_shadow_balance_types psbt,
1159 pay_template_core_objects ptco
1160 where psbt.template_id = l_template_id
1161 and psbt.balance_name = p_ele_name||' Arrears'
1162 and ptco.template_id = psbt.template_id
1163 and ptco.shadow_object_id = psbt.balance_type_id;
1164 --
1165 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',17);
1166
1167 --
1168 update pay_element_types_f
1169 set element_information10 = l_pri_bal_id,
1170 element_information11 = l_accr_bal_id,
1171 element_information12 = l_arr_bal_id,
1172 element_information13 = l_not_taken_bal_id,
1173 element_information17 = l_repl_bal_id,
1174 element_information16 = l_addl_bal_id,
1175 element_information15 = l_fees_bal_id,
1176 element_information18 = p_ele_t4a_footnote,
1177 element_information19 = p_ele_rl1_footnote,
1178 element_information20 = p_ele_registration_number
1179 where element_type_id = l_base_element_type_id
1180 and business_group_id + 0 = p_bg_id;
1181 --
1182 update pay_element_types_f
1183 set element_information10 = l_fees_bal_id
1184 where element_type_id = l_fee_element_type_id
1185 and business_group_id + 0 = p_bg_id;
1186 --
1187 --
1188 FOR dim IN get_asg_gre_run_dim_id LOOP
1189 l_asg_gre_run_dim_id := dim.balance_dimension_id;
1190 END LOOP;
1191
1192 FOR dim IN get_asg_jd_gre_run_dim_id LOOP
1193 l_asg_jd_gre_run_dim_id := dim.balance_dimension_id;
1194 END LOOP;
1195
1196 UPDATE pay_defined_balances
1197 SET save_run_balance = 'Y'
1198 WHERE balance_type_id IN
1199 (l_pri_bal_id,
1200 l_fees_bal_id)
1201 AND balance_dimension_id IN
1202 (l_asg_gre_run_dim_id,
1203 l_asg_jd_gre_run_dim_id)
1204 AND business_group_id = p_bg_id;
1205
1206 IF p_ele_proc_runtype = 'REG' THEN
1207
1208 create_element_type_usages (
1209 p_element_name => p_ele_name,
1210 p_bg_id => p_bg_id,
1211 p_ele_proc_run_type => p_ele_proc_runtype);
1212
1213 create_element_type_usages (
1214 p_element_name => p_ele_name || ' Special Inputs',
1215 p_bg_id => p_bg_id,
1216 p_ele_proc_run_type => p_ele_proc_runtype);
1217
1218 END IF;
1219 --
1220 --
1221 ------------------ Update jurisdiction_level on balances --------------------
1222 --
1223 -- Added update for jurisdiction level, this needs to be set for all balances
1224 -- to '2'. This is currently a hardcoded update to base table as the balance
1225 -- apis do not support jurisdiction_level.
1226 --
1227 pay_ca_user_init_earn.update_jd_level_on_balance(l_template_id);
1228 --
1229 ------------------ Conclude Create_User_Init_Deduction Main -----------------
1230 --
1231 hr_utility.set_location('pay_ca_user_init_garn.create_user_init_garnishment',18);
1232 --
1233 RETURN l_base_element_type_id;
1234 --
1235 END create_user_init_garnishment;
1236 --
1237 --
1238 ------------------------- Deletion procedures -----------------------------
1239 ---------------------- delete_user_init_deduction -------------------------
1240 --
1241 PROCEDURE delete_user_init_deduction (
1242 p_business_group_id in number,
1243 p_ele_type_id in number,
1244 p_ele_name in varchar2,
1245 p_ele_priority in number,
1246 p_ele_info_10 in varchar2 default null,
1247 p_ele_info_12 in varchar2 default null,
1248 p_del_sess_date in date,
1249 p_del_val_start_date in date,
1250 p_del_val_end_date in date) IS
1251 -- local constants
1252 c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
1253
1254 -- local vars
1255 l_del_sess_date DATE := NULL;
1256 l_del_val_start DATE := NULL;
1257 l_del_val_end DATE := NULL;
1258
1259 l_template_id NUMBER(9);
1260 --
1261 BEGIN
1262 -- Populate vars.
1263 l_del_val_end := nvl(p_del_val_end_date, c_end_of_time);
1264 l_del_val_start := nvl(p_del_val_start_date, sysdate);
1265 l_del_sess_date := nvl(p_del_sess_date, sysdate);
1266 --
1267 hr_utility.set_location('pay_ca_user_init_dedn.delete_user_init_deduction',1);
1268 --
1269 select template_id
1270 into l_template_id
1271 from pay_element_templates
1272 where base_name = p_ele_name
1273 and business_group_id = p_business_group_id
1274 and template_type = 'U';
1275 --
1276 hr_utility.set_location('pay_ca_user_init_dedn.delete_user_init_deduction',2);
1277 --
1278 begin
1279 delete from pay_element_type_usages_f
1280 where element_type_id in ( select element_type_id
1281 from pay_element_types_f
1282 where element_name = p_ele_name
1283 and business_group_id = p_business_group_id );
1284 --
1285 hr_utility.set_location('pay_ca_user_init_dedn.delete_user_init_deduction',3);
1286 --
1287 exception
1288 when others then
1289 null;
1290 end;
1291 pay_element_template_api.delete_user_structure
1292 (p_validate => false
1293 ,p_drop_formula_packages => true
1294 ,p_template_id => l_template_id);
1295 --
1296 hr_utility.set_location('pay_ca_user_init_dedn.delete_user_init_deduction',4);
1297 --
1298 END delete_user_init_deduction;
1299 --
1300 ------------------------- Deletion procedures -----------------------------
1301 ---------------------- delete_user_init_garnishment -----------------------
1302 --
1303 PROCEDURE delete_user_init_garnishment (
1304 p_business_group_id in number,
1305 p_ele_type_id in number,
1306 p_ele_name in varchar2,
1307 p_ele_priority in number,
1308 p_ele_info_10 in varchar2 default null,
1309
1310 p_ele_info_12 in varchar2 default null,
1311
1312 p_del_sess_date in date,
1313 p_del_val_start_date in date,
1314 p_del_val_end_date in date) IS
1315 -- local constants
1316 c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
1317
1318 -- local vars
1319 l_del_sess_date DATE := NULL;
1320 l_del_val_start DATE := NULL;
1321 l_del_val_end DATE := NULL;
1322
1323 l_template_id NUMBER(9);
1324 --
1325 BEGIN
1326 -- Populate vars.
1327 l_del_val_end := nvl(p_del_val_end_date, c_end_of_time);
1328 l_del_val_start := nvl(p_del_val_start_date, sysdate);
1329 l_del_sess_date := nvl(p_del_sess_date, sysdate);
1330 --
1331 hr_utility.set_location('pay_ca_user_init_garn.delete_user_init_garnishment',1);
1332 --
1333 select template_id
1334 into l_template_id
1335 from pay_element_templates
1336 where base_name = p_ele_name
1337 and business_group_id = p_business_group_id
1338 and template_type = 'U';
1339 --
1340 hr_utility.set_location('pay_ca_user_init_garn.delete_user_init_garnishment',2);
1341 --
1342 begin
1343 delete from pay_element_type_usages_f
1344 where element_type_id in ( select element_type_id
1345 from pay_element_types_f
1346 where element_name = p_ele_name
1347 and business_group_id = p_business_group_id );
1348 --
1349 hr_utility.set_location('pay_ca_user_init_dedn.delete_user_init_deduction',3
1350 );
1351 --
1352 exception
1353 when others then
1354 null;
1355 end;
1356
1357 pay_element_template_api.delete_user_structure
1358 (p_validate => false
1359 ,p_drop_formula_packages => true
1360 ,p_template_id => l_template_id);
1361 --
1362 hr_utility.set_location('pay_ca_user_init_garn.delete_user_init_garnishment',3);
1363 --
1364 END delete_user_init_garnishment;
1365 --
1366 END pay_ca_user_init_dedn;