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