[Home] [Help]
PACKAGE BODY: APPS.PAY_US_DEDN_TEMPLATE_WRAPPER
Source
1 package body pay_us_dedn_template_wrapper as
2 /* $Header: pytmpdde.pkb 120.1 2005/07/28 18:33:08 rpinjala noship $ */
3
4 g_proc constant varchar2(150) := 'pay_us_dedn_template_wrapper';
5
6 -- =============================================================================
7 -- create_deduction_element:
8 -- =============================================================================
9 function create_deduction_element
10 (p_element_name in varchar2
11 ,p_reporting_name in varchar2
12 ,p_description in varchar2
13 ,p_classification_name in varchar2
14 ,p_ben_class_id in number
15 ,p_category in varchar2
16 ,p_processing_type in varchar2
17 ,p_processing_priority in number
18 ,p_standard_link_flag in varchar2
19 ,p_processing_runtype in varchar2
20 ,p_start_rule in varchar2
21 ,p_stop_rule in varchar2
22 ,p_amount_rule in varchar2
23 ,p_series_ee_bond in varchar2
24 ,p_payroll_table in varchar2
25 ,p_paytab_column in varchar2
26 ,p_rowtype_meaning in varchar2
27 ,p_arrearage in varchar2
28 ,p_deduct_partial in varchar2
29 ,p_employer_match in varchar2
30 ,p_aftertax_component in varchar2
31 ,p_ele_eff_start_date in date
32 ,p_ele_eff_end_date in date
33 ,p_business_group_id in number
34 ,p_srs_plan_type in varchar2
35 ,p_srs_buy_back in varchar2
36 ,p_roth_contribution in varchar2
37 ,p_userra_contribution in varchar2
38 ,p_catchup_processing in varchar2
39 ,p_termination_rule in varchar2
40 )
41 return number is
42
43 l_architecture varchar2(20);
44 l_ele_type_id number;
45 l_proc varchar2(80);
46 l_upgrade_stat varchar2(10);
47 --
48 -- Get the value for US_ADVANCED_WAGE_ATTACHMENT from pay_action_parameters
49 --
50 cursor c_architecture is
51 select parameter_value
52 from pay_action_parameters
53 where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
54 --
55 -- Get the status of UPGRADE GARNISHMENT PROCESS Bug 3549298
56 --
57 cursor c_get_upg_stat(cp_business_group_id number) is
58 select status
59 from pay_upgrade_definitions pud,
60 pay_upgrade_status pus
61 where pud.short_name = 'US_INV_DEDN_UPGRADE'
62 and pus.upgrade_definition_id = pud.upgrade_definition_id
63 and pus.business_group_id = cp_business_group_id;
64
65 begin
66 --
67 l_proc := g_proc||'.create_deduction_element';
68 hr_utility.set_location('Entering: '||l_proc, 5);
69 --
70 -- Involuntary Deductions
71 --
72 if p_classification_name = 'Involuntary Deductions' then
73 -- Bug 3777810
74 l_architecture := null;
75 l_upgrade_stat := null;
76
77 open c_architecture;
78 fetch c_architecture into l_architecture;
79 close c_architecture;
80
81 open c_get_upg_stat(p_business_group_id);
82 fetch c_get_upg_stat into l_upgrade_stat;
83
84 if c_get_upg_stat%FOUND then -- Upgrade Started
85 l_architecture := 'Y';
86 end if;
87
88 close c_get_upg_stat;
89
90 l_architecture := upper(substr(l_architecture,1,1));
91
92 if l_architecture is not null and l_architecture = 'N' then
93 -- Bug 3650283
94 if p_category = 'DCIA' then
95 hr_utility.set_location (l_proc,5);
96 hr_utility.set_message(801,'PAY_US_DCIA_ERROR');
97 hr_utility.raise_error;
98 end if;
99 l_ele_type_id :=
100 hr_us_garn_gen.create_garnishment
101 (p_garn_name => p_element_name
102 ,p_garn_reporting_name => p_reporting_name
103 ,p_garn_description => p_description
104 ,p_category => p_category
105 ,p_bg_id => p_business_group_id
106 ,p_ele_eff_start_date => p_ele_eff_start_date
107 );
108 hr_utility.set_location(l_proc, 15);
109 else
110 l_ele_type_id :=
111 pay_us_user_init_dedn.create_user_init_template
112 (p_ele_name => p_element_name
113 ,p_ele_reporting_name => p_reporting_name
114 ,p_ele_description => p_description
115 ,p_ele_classification => p_classification_name
116 ,p_ele_category => p_category
117 ,p_ele_processing_type => p_processing_type
118 ,p_ele_priority => p_processing_priority
119 ,p_ele_standard_link => p_standard_link_flag
120 ,p_ele_proc_runtype => p_processing_runtype
121 ,p_ele_calc_rule => p_amount_rule
122 ,p_ele_start_rule => p_start_rule
123 ,p_ele_stop_rule => p_stop_rule
124 ,p_ele_partial_deduction => p_deduct_partial
125 ,p_ele_arrearage => p_arrearage
126 ,p_ele_eff_start_date => p_ele_eff_start_date
127 ,p_ele_eff_end_date => p_ele_eff_end_date
128 ,p_employer_match => p_employer_match
129 ,p_after_tax_component => p_aftertax_component
130 ,p_ele_srs_plan_type => p_srs_plan_type
131 ,p_ele_srs_buy_back => p_srs_buy_back
132 ,p_roth_contribution => p_roth_contribution
133 ,p_userra_contribution => p_userra_contribution
134 ,p_bg_id => p_business_group_id
135 ,p_catchup_processing => p_catchup_processing
136 ,p_termination_rule => p_termination_rule
137 ,p_ben_class_id => p_ben_class_id
138 );
139 hr_utility.set_location(l_proc, 20);
140 end if;
141 --
142 -- Pre-Tax Deductions
143 --
144 elsif p_classification_name in ('Pre-Tax Deductions') then
145 /*
146 If the category is not in Health Care 125 and Dependent Care 125 THEN
147 call the new deduction template else
148 call the old deduction driver template
149
150 Changes made by Tarun Mehra for inclusion of Def Comp 401k
151 on 04-Jan-2000
152
153 Changes made by Kumar Thirmiya for inclusion of User Defined Category
154 on 28-Jun-2001
155
156 -- old code
157 -- p_category in ('E','G','Deferred Comp 403b','Deferred Comp 457')
158 THEN
159 -- p_category in ('E','G','D','Deferred Comp 401k','Deferred Comp
160 403b','Deferred Comp 457') THEN
161 */
162
163 /* commented for bug 2400648
164 Dependent Care and Health Care will be using the new template
165 p_classification_name = 'Pre-Tax Deductions' AND
166 p_category not in ('H','S','Health Care 125','Dependent Care 125')
167 */
168
169 l_ele_type_id :=
170 pay_us_user_init_dedn.create_user_init_template
171 (p_ele_name => p_element_name
172 ,p_ele_reporting_name => p_reporting_name
173 ,p_ele_description => p_description
174 ,p_ele_classification => p_classification_name
175 ,p_ele_category => p_category
176 ,p_ele_processing_type => p_processing_type
177 ,p_ele_priority => p_processing_priority
178 ,p_ele_standard_link => p_standard_link_flag
179 ,p_ele_proc_runtype => p_processing_runtype
180 ,p_ele_calc_rule => p_amount_rule
181 ,p_ele_start_rule => p_start_rule
182 ,p_ele_stop_rule => p_stop_rule
183 ,p_ele_partial_deduction => p_deduct_partial
184 ,p_ele_arrearage => p_arrearage
185 ,p_ele_eff_start_date => p_ele_eff_start_date
186 ,p_ele_eff_end_date => p_ele_eff_end_date
187 ,p_employer_match => p_employer_match
188 ,p_after_tax_component => p_aftertax_component
189 ,p_ele_srs_plan_type => p_srs_plan_type
190 ,p_ele_srs_buy_back => p_srs_buy_back
191 ,p_roth_contribution => p_roth_contribution
192 ,p_userra_contribution => p_userra_contribution
193 ,p_bg_id => p_business_group_id
194 ,p_catchup_processing => p_catchup_processing
195 ,p_termination_rule => p_termination_rule
196 ,p_ben_class_id => p_ben_class_id
197 );
198 hr_utility.set_location(l_proc, 30);
199 --
200 -- Voluntary Deductions
201 --
202 else
203 l_ele_type_id :=
204 hr_user_dedn_drv.ins_deduction_template
205 (p_ele_name => p_element_name
206 ,p_ele_reporting_name => p_reporting_name
207 ,p_ele_description => p_description
208 ,p_ele_classification => p_classification_name
209 ,p_ben_class_id => p_ben_class_id
210 ,p_ele_category => p_category
211 ,p_ele_processing_type => p_processing_type
212 ,p_ele_priority => p_processing_priority
213 ,p_ele_standard_link => p_standard_link_flag
214 ,p_ele_proc_runtype => p_processing_runtype
215 ,p_ele_start_rule => p_start_rule
216 ,p_ele_stop_rule => p_stop_rule
217 ,p_ele_ee_bond => p_series_ee_bond
218 ,p_ele_amount_rule => p_amount_rule
219 ,p_ele_paytab_name => p_payroll_table
220 ,p_ele_paytab_col => p_paytab_column
221 ,p_ele_paytab_row_type => p_rowtype_meaning
222 ,p_ele_arrearage => p_arrearage
223 ,p_ele_partial_dedn => p_deduct_partial
224 ,p_mix_flag => null
225 ,p_ele_er_match => p_employer_match
226 ,p_ele_at_component => p_aftertax_component
227 ,p_ele_eff_start_date => p_ele_eff_start_date
228 ,p_ele_eff_end_date => p_ele_eff_end_date
229 ,p_bg_id => p_business_group_id
230 ,p_termination_rule => p_termination_rule
231 );
232
233 hr_utility.set_location(l_proc, 40);
234 --
235 end if;
236 --
237 return (l_ele_type_id);
238 --
239 hr_utility.set_location('Leaving '||l_proc, 50);
240 --
241 END create_deduction_element;
242 --
243 --
244 -- =============================================================================
245 -- delete_deduction_element: To delete a user created template.
246 -- =============================================================================
247 PROCEDURE delete_deduction_element
248 (p_business_group_id in number
249 ,p_element_type_id in number
250 ,p_element_name in varchar2
251 ,p_classification_name in varchar2
252 ,p_category in varchar2
253 ,p_processing_priority in number
254 ,p_amount_rule in varchar2
255 ,p_series_ee_bond in varchar2
256 ,p_arrearage in varchar2
257 ,p_stop_rule in varchar2
258 ,p_calculation_ele_id in number
259 ,p_vol_dedns_baltype_id in number
260 ,p_primary_baltype_id in number
261 ,p_accrued_baltype_id in number
262 ,p_arrears_baltype_id in number
263 ,p_not_taken_baltype_id in number
264 ,p_tobondpurch_baltype_id in number
265 ,p_able_baltype_id in number
266 ,p_additional_baltype_id in number
267 ,p_replacement_baltype_id in number
268 ,p_special_inputs_ele_id in number
269 ,p_special_features_ele_id in number
270 ,p_verifier_ele_id in number
271 ,p_eff_start_date in date
272 ,p_eff_end_date in date
273 ) IS
274 --
275 l_proc varchar2(80);
276 l_template_based number;
277 --
278 cursor c1 is
279 select template_id
280 from pay_element_templates
281 where base_name = p_element_name
282 and business_group_id = p_business_group_id
283 and template_type = 'U';
284 --
285 cursor c2 is
286 select element_type_extra_info_id
287 ,object_version_number
288 from pay_element_type_extra_info
289 where element_type_id = p_element_type_id
290 and information_type = 'PQP_US_PRE_TAX_DEDUCTIONS'
291 and eei_information_category = 'PQP_US_PRE_TAX_DEDUCTIONS';
292 --
293 begin
294 l_proc := g_proc||'.delete_deduction_element';
295 hr_utility.set_location('Entering: '||l_proc, 5);
296
297 if p_classification_name = 'Pre-Tax Deductions' then
298 for c1_rec in c1 loop
299 l_template_based := c1_rec.template_id;
300 end loop;
301 end if;
302 hr_utility.set_location(l_proc, 10);
303 --
304 if p_classification_name = 'Involuntary Deductions' then
305 --
306 hr_us_garn_gen.delete_dedn
307 (p_business_group_id => p_business_group_id
308 ,p_ele_type_id => p_element_type_id
309 ,p_ele_name => p_element_name
310 ,p_ele_priority => p_processing_priority
311 ,p_ele_info_10 => p_primary_baltype_id
312 ,p_ele_info_11 => p_accrued_baltype_id
313 ,p_ele_info_12 => p_arrears_baltype_id
314 ,p_ele_info_13 => p_not_taken_baltype_id
315 ,p_ele_info_14 => p_tobondpurch_baltype_id
316 ,p_ele_info_15 => p_able_baltype_id
317 ,p_ele_info_16 => p_additional_baltype_id
318 ,p_ele_info_17 => p_replacement_baltype_id
319 ,p_ele_info_18 => p_special_inputs_ele_id
320 ,p_ele_info_19 => p_special_features_ele_id
321 ,p_ele_info_20 => p_verifier_ele_id
322 ,p_ele_info_5 => p_calculation_ele_id
323 ,p_ele_info_8 => p_vol_dedns_baltype_id
324 ,p_del_sess_date => p_eff_start_date
325 ,p_del_val_start_date => p_eff_start_date
326 ,p_del_val_end_date => p_eff_end_date
327 );
328 --
329 hr_utility.set_location(l_proc, 20);
330 --
331 elsif p_classification_name = 'Pre-Tax Deductions' and
332 l_template_based is not null then
333
334 pay_us_user_init_dedn.delete_user_init_template
335 (p_business_group_id => p_business_group_id
336 ,p_ele_type_id => p_element_type_id
337 ,p_ele_name => p_element_name
338 ,p_effective_date => p_eff_start_date
339 );
340 -- Delete entry for Catch-up Processing (if present)
341 -- from pay_element_type_extra_info table.
342 for c2_rec in c2 loop
343 pay_element_extra_info_api.delete_element_extra_info
344 (p_validate => false
345 ,p_element_type_extra_info_id => c2_rec.element_type_extra_info_id
346 ,p_object_version_number => c2_rec.object_version_number
347 );
348 end loop;
349 --
350 hr_utility.set_location(l_proc, 30);
351 --
352 else
353 hr_user_init_dedn.do_deletions
354 (p_business_group_id => p_business_group_id
355 ,p_ele_type_id => p_element_type_id
356 ,p_ele_name => p_element_name
357 ,p_ele_priority => p_processing_priority
358 ,p_ele_amount_rule => p_amount_rule
359 ,p_ele_ee_bond => p_series_ee_bond
360 ,p_ele_arrearage => p_arrearage
361 ,p_ele_stop_rule => p_stop_rule
362 ,p_ele_info_10 => p_primary_baltype_id
363 ,p_ele_info_11 => p_accrued_baltype_id
364 ,p_ele_info_12 => p_arrears_baltype_id
365 ,p_ele_info_13 => p_not_taken_baltype_id
366 ,p_ele_info_14 => p_tobondpurch_baltype_id
367 ,p_ele_info_15 => p_able_baltype_id
368 ,p_ele_info_16 => p_additional_baltype_id
369 ,p_ele_info_17 => p_replacement_baltype_id
370 ,p_ele_info_18 => p_special_inputs_ele_id
371 ,p_ele_info_19 => p_special_features_ele_id
372 ,p_ele_info_20 => p_verifier_ele_id
373 ,p_del_sess_date => p_eff_start_date
374 ,p_del_val_start_date => p_eff_start_date
375 ,p_del_val_end_date => p_eff_end_date);
376 --
377 hr_utility.set_location(l_proc, 40);
378 end if;
379 --
380 hr_utility.set_location('Leaving '||l_proc, 50);
381 --
382 end delete_deduction_element;
383 --
384 --
385 END pay_us_dedn_template_wrapper;