[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_STAKEHOLDER_TEMPLATE
Source
1 PACKAGE BODY pqp_gb_stakeholder_template AS
2 /* $Header: pqgbstht.pkb 120.1 2005/05/30 00:12:24 rvishwan noship $ */
3
4 /*========================================================================
5 * CREATE_USER_TEMPLATE
6 *=======================================================================*/
7 FUNCTION create_user_template
8 (p_frm_sd_scheme_name IN VARCHAR2 --'Stakeholder'
9 ,p_frm_sd_contribution_method IN VARCHAR2 --Ex Rule Eme Cntrbn
10 ,p_frm_sd_employee_contribution IN NUMBER
11 ,p_frm_be_element_name IN VARCHAR2
12 ,p_frm_be_reporting_name IN VARCHAR2
13 -- ,p_frm_be_classification -- Always 'Voluntary Deductions'
14 ,p_frm_be_description IN VARCHAR2 DEFAULT NULL
15 ,p_frm_ae_employer_contribution IN VARCHAR2 DEFAULT 'N'--Ex Rule
16 ,p_frm_ae_type IN VARCHAR2 DEFAULT NULL
17 ,p_frm_ae_rate IN NUMBER DEFAULT NULL
18 ,p_frm_ctl_effective_start_date IN DATE DEFAULT NULL
19 ,p_frm_ctl_effective_end_date IN DATE DEFAULT NULL
20 ,p_frm_ctl_business_group_id IN NUMBER
21 )
22 RETURN NUMBER -- Base Element Core Object ID
23 IS
24 /*-------------------------------------------------------------------------
25 The input values are explained below : V-varchar2, D-Date, N-number
26 Input-Name Type Valid Values/Explaination
27 ---------- ---- ----------------------------------
28 p_frm_sd_scheme_name (V) 'Stakeholder Pension' -- Maybe ?
29 p_frm_sd_contribution_method (V) 'P'or'F' Ex Rule Employee Contributn
30 p_frm_sd_employee_contribution (V) Default Amount The Selected Method
31 p_frm_be_element_name (V) Base Element Name = <BASE NAME>
32 p_frm_be_reporting_name (V) Reporting Name
33 -- p_frm_be_classification (V) Always 'Voluntary Deduction'
34 p_frm_be_description (V) Optional Element Description
35 p_frm_ae_employer_contribution (V) Optional Ex Rule Employer Contributn
36 p_frm_ae_type (V) Optional Ex Rule Employer Contributn
37 p_frm_ae_rate (N) Optional Employer Contribution Rate
38 p_frm_ctl_effective_start_date (D) Standard Effective Start Date
39 p_frm_ctl_effective_end_date (D) Standard Effective End Date
40 p_frm_ctl_busines_group_id (N) Standard Business Group Id
41 -------------------------------------------------------------------------*/
42
43 l_proc VARCHAR2(61):= g_proc||'create_user_template';
44
45 l_xx_stkhldr_ovn NUMBER(9);
46
47 l_te_source_id NUMBER(9);
48 l_te_ustrctr_id NUMBER(9);
49
50 l_ex_emrfctr_yn VARCHAR2(150);
51 l_ex_emrpctr_yn VARCHAR2(150);
52
53 l_el_stkcore_id NUMBER(9);
54 l_el_bsuffix_nm VARCHAR2(40);
55 l_el_skpfrml_nm VARCHAR2(80):= NULL; -- Explicit
56
57 l_ee_stkhldr_id NUMBER;
58
59 l_contribution_method_name VARCHAR2(80);
60 l_employer_contribution_type VARCHAR2(80);
61
62
63 CURSOR csr_el_stkhldr_details(p_el_stkhldr_nm VARCHAR2) IS
64 SELECT element_type_id
65 ,object_version_number
66 FROM pay_shadow_element_types
67 WHERE template_id = l_te_ustrctr_id
68 AND element_name = p_el_stkhldr_nm;
69
70 row_el_stkhldr_details csr_el_stkhldr_details%ROWTYPE;
71
72 CURSOR csr_iv_emecntr(p_el_stkhldr_id NUMBER) IS
73 SELECT input_value_id
74 ,object_version_number
75 FROM pay_shadow_input_values
76 WHERE element_type_id = p_el_stkhldr_id
77 AND name = DECODE(p_frm_sd_contribution_method
78 ,'P','Percentage Contribution'
79 ,'F','Flat Rate Contribution'
80 ,NULL);
81
82 row_iv_emecntr csr_iv_emecntr%ROWTYPE;
83
84 CURSOR csr_iv_emrcntr(p_el_stkhldr_id NUMBER) IS
85 SELECT input_value_id
86 ,object_version_number
87 FROM pay_shadow_input_values
88 WHERE element_type_id = p_el_stkhldr_id
89 AND name = DECODE(p_frm_ae_type
90 ,'P','Employers Percentage'
91 ,'F','Employers Factor'
92 ,NULL);
93
94 row_iv_emrcntr csr_iv_emrcntr%ROWTYPE;
95
96
97 CURSOR csr_iv_schname(p_el_stkhldr_id NUMBER) IS
98 SELECT input_value_id
99 ,object_version_number
100 FROM pay_shadow_input_values
101 WHERE element_type_id = p_el_stkhldr_id
102 AND name = 'Scheme Name';
103
104 row_iv_schname csr_iv_schname%ROWTYPE;
105
106
107
108 --
109 -- cursor to fetch the core element id
110 --
111 CURSOR c5 (c_element_name in varchar2) is
112 SELECT ptco.core_object_id
113 FROM pay_shadow_element_types psbt,
114 pay_template_core_objects ptco
115 WHERE psbt.template_id = l_te_ustrctr_id
116 AND psbt.element_name = c_element_name
117 AND ptco.template_id = psbt.template_id
118 AND ptco.shadow_object_id = psbt.element_type_id
119 AND ptco.core_object_type = 'ET';
120
121 --======================================================================
122 -- FUNCTION GET_TEMPLATE_ID
123 --======================================================================
124 FUNCTION get_template_id (p_legislation_code in varchar2 )
125 RETURN number IS
126 --
127 l_template_id NUMBER(9);
128 l_template_name VARCHAR2(80);
129 l_proc varchar2(61) := g_proc||'get_template_id';
130 --
131 CURSOR c4 is
132 SELECT template_id
133 FROM pay_element_templates
134 WHERE template_name = l_template_name
135 AND legislation_code = p_legislation_code
136 AND template_type = 'T'
137 AND business_group_id is NULL;
138 --
139 BEGIN
140 --
141 hr_utility.set_location('Entering: '||l_proc, 10);
142 --
143 l_template_name := 'PQP STAKEHOLDER PENSION';
144 --
145 hr_utility.set_location(l_proc, 30);
146 --
147 for c4_rec in c4 loop
148 l_template_id := c4_rec.template_id;
149 end loop;
150 --
151 hr_utility.set_location('Leaving: '||l_proc, 100);
152 --
153 RETURN l_template_id;
154 --
155 END get_template_id;
156
157
158
159 --=======================================================================
160 -- FUNCTION GET_OBJECT_ID
161 --=======================================================================
162
163 FUNCTION get_object_id (p_object_type in varchar2,
164 p_object_name in varchar2)
165 RETURN NUMBER is
166 --
167 l_object_id NUMBER := NULL;
168 l_proc varchar2(61) := g_proc||'get_object_id';
169 --
170 CURSOR c2 (c_object_name varchar2) is
171 SELECT element_type_id
172 FROM pay_element_types_f
173 WHERE element_name = c_object_name
174 AND business_group_id = p_frm_ctl_business_group_id;
175 --
176 CURSOR c3 (c_object_name in varchar2) is
177 SELECT ptco.core_object_id
178 FROM pay_shadow_balance_types psbt,
179 pay_template_core_objects ptco
180 WHERE psbt.template_id = l_te_ustrctr_id
181 AND psbt.balance_name = c_object_name
182 AND ptco.template_id = psbt.template_id
183 AND ptco.shadow_object_id = psbt.balance_type_id;
184 --
185 BEGIN
186 hr_utility.set_location('Entering: '||l_proc, 10);
187 --
188 if p_object_type = 'ELE' then
189 for c2_rec in c2 (p_object_name) loop
190 l_object_id := c2_rec.element_type_id; -- element id
191 end loop;
192 elsif p_object_type = 'BAL' then
193 for c3_rec in c3 (p_object_name) loop
194 l_object_id := c3_rec.core_object_id; -- balance id
195 end loop;
196 end if;
197 --
198 hr_utility.set_location('Leaving: '||l_proc, 50);
199 --
200 RETURN l_object_id;
201 --
202 END get_object_id;
203 --
204 --============================================================================
205 -- MAIN FUNCTION
206 --============================================================================
207
208 /*-------------------------------------------------------------------------
209 The input values are explained below : V-varchar2, D-Date, N-number
210 Input-Name Type Valid Values/Explaination
211 ---------- ---- ---------------------------------
212 p_frm_sd_scheme_name (V) 'Stakeholder Pension' -- Maybe ?
213 p_frm_sd_contribution_method (V) 'P'or'F' Ex Rule Employee Contributn
214 p_frm_sd_employee_contribution (V) Default Amount The Selected Method
215 p_frm_be_element_name (V) Base Element Name = <BASE NAME>
216 p_frm_be_reporting_name (V) Reporting Name
217 -- p_frm_be_classification (V) Always 'Voluntary Deduction'
218 p_frm_be_description (V) Optional Element Description
219 p_frm_ae_employer_contribution (V) Optional Ex Rule Employer Contributn
220 p_frm_ae_type (V) Optional Ex Rule Employer Contributn
221 p_frm_ae_rate (N) Optional Employer Contribution Rate
222 p_frm_ctl_effective_start_date (D) Standard Effective Start Date
223 p_frm_ctl_effective_end_date (D) Standard Effective End Date
224 p_frm_ctl_busines_group_id (N) Standard Business Group Id
225 -------------------------------------------------------------------------*/
226
227
228
229 BEGIN
230
231 hr_utility.set_location('Entering : '||l_proc, 10);
232
233
234
235 --------------------------- Set session date ---------------------------------
236
237 pay_db_pay_setup.set_session_date
238 (NVL(p_frm_ctl_effective_start_date, TRUNC(SYSDATE))
239 );
240
241 hr_utility.set_location(l_proc, 20);
242
243
244
245 -------------------------- Get Source Template ID ----------------------------
246
247 l_te_source_id := get_template_id
248 (p_legislation_code => 'GB'
249 );
250
251 hr_utility.set_location(l_proc, 30);
252
253
254
255
256 ------------------ Setup Flags For The Exclusion Rules -----------------------
257
258 -- If the user has checked employer contribution only then create the
259 -- input values for the employer contribution.
260
261 IF p_frm_ae_employer_contribution = 'Y' THEN
262
263 IF p_frm_ae_type = 'P'/*ercentage*/ THEN
264
265 l_ex_emrfctr_yn := 'N'; -- Exclude Input Value For Factor
266 l_ex_emrpctr_yn := 'Y'; -- Create Input Value For Percentage
267
268 ELSE
269
270 l_ex_emrfctr_yn := 'Y'; -- Create Input Value For Factor
271 l_ex_emrpctr_yn := 'N'; -- Exclude Input Value For Percentage
272
273 END IF;
274
275 ELSE -- p_frm_ae_employer_contribution is 'N'
276
277 l_ex_emrfctr_yn := 'N'; -- Exclude Input Value For Factor
278 l_ex_emrpctr_yn := 'N'; -- Exclude Input Value For Percentage
279
280 END IF;
281
282 -- Flat rate deductions should be applied only once in a period.
283 IF p_frm_sd_contribution_method = 'F'/*lat Rate*/ THEN
284
285 l_el_skpfrml_nm := 'ONCE_EACH_PERIOD';
286
287 --Percentage calculation should be applied to all runs in the period.
288 --Hence leave skip formula name as the default ie NULL.
289
290 END IF;
291
292
293
294 pay_element_template_api.create_user_structure
295 (p_validate => FALSE
296 ,p_effective_date => p_frm_ctl_effective_start_date
297 ,p_business_group_id => p_frm_ctl_business_group_id
298 ,p_source_template_id => l_te_source_id
299 ,p_base_name => p_frm_be_element_name
300 ,p_configuration_information1 => p_frm_sd_contribution_method
301 ,p_configuration_information2 => l_ex_emrpctr_yn
302 ,p_configuration_information3 => l_ex_emrfctr_yn
303 ,p_configuration_information4 => p_frm_ae_employer_contribution
304 ,p_template_id => l_te_ustrctr_id -- Returned User Struct ID
305 ,p_object_version_number => l_xx_stkhldr_ovn
306 );
307
308 hr_utility.set_location(l_proc, 40);
309
310
311
312 ---------------------------- Update Shadow Structure --------------------------
313
314
315 -- Update reporting name and description on the base element
316
317 l_el_bsuffix_nm := ' Stakeholder Pension';
318 OPEN csr_el_stkhldr_details(p_frm_be_element_name||l_el_bsuffix_nm);
319 -- LOOP
320 FETCH csr_el_stkhldr_details INTO row_el_stkhldr_details;
321 -- EXIT WHEN csr_el_stkhldr_details%NOTFOUND;
322 pay_shadow_element_api.update_shadow_element
323 (p_validate => FALSE
324 ,p_effective_date => p_frm_ctl_effective_start_date
325 ,p_element_type_id => row_el_stkhldr_details.element_type_id
326 -- ,p_element_name => p_frm_be_element_name
327 ,p_reporting_name => p_frm_be_reporting_name
328 ,p_description => p_frm_be_description
329 ,p_skip_formula => l_el_skpfrml_nm
330 ,p_object_version_number => row_el_stkhldr_details.object_version_number
331 );
332 -- END LOOP;
333 CLOSE csr_el_stkhldr_details;
334
335 hr_utility.set_location(l_proc, 50);
336
337
338 -- Update the input values of the base element with user defaults.
339
340 -- Update the employee contribution input value
341 OPEN csr_iv_emecntr(row_el_stkhldr_details.element_type_id);
342 FETCH csr_iv_emecntr INTO row_iv_emecntr;
343 -- IF csr_iv_emecntr%NOTFOUND THEN
344 -- -- Common Fatal Error Out
345 -- hr_utility.set_message(8303, 'PQP_STKTEST_EMECNTR_NOT_FOUND');
346 -- hr_utility.raise_error;
347 -- ELSE
348 pay_siv_upd.upd
349 (p_effective_date => p_frm_ctl_effective_start_date
350 ,p_input_value_id => row_iv_emecntr.input_value_id
351 ,p_element_type_id => row_el_stkhldr_details.element_type_id
352 -- ,p_display_sequence => -- in number
353 -- ,p_generate_db_items_flag => -- in varchar2
354 -- ,p_hot_default_flag => -- in varchar2
355 -- ,p_mandatory_flag => -- in varchar2
356 -- ,p_name => -- in varchar2
357 -- ,p_uom => -- in varchar2
358 -- ,p_lookup_type => -- in varchar2
359 ,p_default_value => p_frm_sd_employee_contribution
360 -- ,p_max_value => -- in varchar2
361 -- ,p_min_value => -- in varchar2
362 -- ,p_warning_or_error => -- in varchar2
363 -- ,p_default_value_column => -- in varchar2
364 -- ,p_exclusion_rule_id => -- in number
365 ,p_object_version_number => row_iv_emecntr.object_version_number --inout
366 );
367 -- END IF;
368 CLOSE csr_iv_emecntr;
369
370 hr_utility.set_location(l_proc, 60);
371
372
373 -- And if required update the employer contribution input value
374
375 IF p_frm_ae_employer_contribution = 'Y' THEN
376
377 hr_utility.set_location(l_proc, 70);
378
379 OPEN csr_iv_emrcntr(row_el_stkhldr_details.element_type_id);
380 FETCH csr_iv_emrcntr INTO row_iv_emrcntr;
381 -- IF csr_iv_emrcntr%NOTFOUND THEN
382 -- -- Common Fatal Error Out
383 -- hr_utility.set_message(8303, 'PQP_STKTEST_EMRCNTR_NOT_FOUND');
384 -- hr_utility.raise_error;
385 -- ELSE
386 pay_siv_upd.upd
387 (p_effective_date => p_frm_ctl_effective_start_date
388 ,p_input_value_id => row_iv_emrcntr.input_value_id
389 ,p_element_type_id => row_el_stkhldr_details.element_type_id
390 -- ,p_display_sequence => -- in number
391 -- ,p_generate_db_items_flag => -- in varchar2
392 -- ,p_hot_default_flag => -- in varchar2
393 -- ,p_mandatory_flag => -- in varchar2
394 -- ,p_name => -- in varchar2
395 -- ,p_uom => -- in varchar2
396 -- ,p_lookup_type => -- in varchar2
397 ,p_default_value => p_frm_ae_rate -- varchar2
398 -- ,p_max_value => -- in varchar2
399 -- ,p_min_value => -- in varchar2
400 -- ,p_warning_or_error => -- in varchar2
401 -- ,p_default_value_column => -- in varchar2
402 -- ,p_exclusion_rule_id => -- in number
403 ,p_object_version_number => row_iv_emrcntr.object_version_number
404 );
405 -- END IF;
406 CLOSE csr_iv_emrcntr;
407
408 END IF;
409
410 hr_utility.set_location(l_proc, 80);
411
412
413 -- Update the scheme name input value
414 OPEN csr_iv_schname(row_el_stkhldr_details.element_type_id);
415 FETCH csr_iv_schname INTO row_iv_schname;
416 -- IF csr_iv_schname%NOTFOUND THEN
417 -- -- Common Fatal Error Out
418 -- hr_utility.set_message(8303, 'PQP_STKTEST_EMECNTR_NOT_FOUND');
419 -- hr_utility.raise_error;
420 -- ELSE
421 pay_siv_upd.upd
422 (p_effective_date => p_frm_ctl_effective_start_date
423 ,p_input_value_id => row_iv_schname.input_value_id
424 ,p_element_type_id => row_el_stkhldr_details.element_type_id
425 -- ,p_display_sequence => -- in number
426 -- ,p_generate_db_items_flag => -- in varchar2
427 -- ,p_hot_default_flag => -- in varchar2
428 -- ,p_mandatory_flag => -- in varchar2
429 -- ,p_name => -- in varchar2
430 -- ,p_uom => -- in varchar2
431 -- ,p_lookup_type => -- in varchar2
432 ,p_default_value => p_frm_sd_scheme_name
433 -- ,p_max_value => -- in varchar2
434 -- ,p_min_value => -- in varchar2
435 -- ,p_warning_or_error => -- in varchar2
436 -- ,p_default_value_column => -- in varchar2
437 -- ,p_exclusion_rule_id => -- in number
438 ,p_object_version_number => row_iv_schname.object_version_number --inout
439 );
440 -- END IF;
441 CLOSE csr_iv_schname;
442
443
444 hr_utility.set_location(l_proc, 90);
445
446
447
448 -------------------------- Generate Core Objects -----------------------------
449
450 pay_element_template_api.generate_part1
451 (p_validate => FALSE
452 ,p_effective_date => p_frm_ctl_effective_start_date
453 ,p_hr_only => FALSE
454 ,p_hr_to_payroll => FALSE
455 ,p_template_id => l_te_ustrctr_id
456 );
457
458 hr_utility.set_location(l_proc, 100);
459
460 pay_element_template_api.generate_part2
461 (p_validate => FALSE
462 ,p_effective_date => p_frm_ctl_effective_start_date
463 ,p_template_id => l_te_ustrctr_id
464 );
465
466
467 hr_utility.set_location(l_proc, 110);
468
469 l_el_stkcore_id := get_object_id
470 ('ELE'
471 ,p_frm_be_element_name||l_el_bsuffix_nm
472 );
473
474 hr_utility.set_location(l_proc, 120);
475
476 IF p_frm_ae_employer_contribution = 'Y' THEN
477 --
478 hr_utility.set_location(l_proc, 130);
479
480 l_employer_contribution_type := p_frm_ae_type;
481 --
482 END IF;
483
484 hr_utility.set_location(l_proc, 140);
485
486 pay_element_extra_info_api.create_element_extra_info
487 (p_element_type_id => l_el_stkcore_id
488 ,p_information_type => 'PQP_GB_STAKEHOLDER_INFORMATION'
489 ,p_eei_information_category => 'PQP_GB_STAKEHOLDER_INFORMATION'
490 ,p_eei_information1 => p_frm_sd_scheme_name
491 ,p_eei_information2 => p_frm_sd_contribution_method
492 ,p_eei_information3 => p_frm_ae_rate
493 ,p_eei_information4 => l_employer_contribution_type
494 ,p_eei_information5 => p_frm_sd_employee_contribution
495 ,p_element_type_extra_info_id => l_ee_stkhldr_id
496 ,p_object_version_number => l_xx_stkhldr_ovn);
497
498 hr_utility.set_location('Leaving : '||l_proc, 150);
499
500 RETURN l_el_stkcore_id;
501
502 END create_user_template;
503
504 --==========================================================================
505 -- Deletion procedure
506 --==========================================================================
507 --
508 PROCEDURE delete_user_template
509 (p_frm_ctl_business_group_id IN NUMBER
510 ,p_frm_ctl_element_type_id IN NUMBER
511 ,p_frm_be_element_name IN VARCHAR2
512 ,p_frm_ctl_effective_start_date IN DATE
513 )
514 IS
515 --
516 l_te_ustrctr_id NUMBER(9);
517 l_proc VARCHAR2(61):=g_proc||'delete_user_template';
518 l_ee_stkhldr_id NUMBER;
519 l_ee_stkhldr_ovn NUMBER;
520 --
521 CURSOR csr_ee_stkhldr is
522 SELECT element_type_extra_info_id
523 FROM pay_element_type_extra_info petei
524 WHERE element_type_id = p_frm_ctl_element_type_id ;
525
526
527 CURSOR csr_te_stkhldr IS
528 SELECT template_id
529 FROM pay_element_templates
530 WHERE base_name = p_frm_be_element_name
531 AND business_group_id = p_frm_ctl_business_group_id
532 AND template_type = 'U';
533 --
534 BEGIN
535 --
536 hr_utility.set_location('Entering :'||l_proc, 10);
537 --
538
539 OPEN csr_ee_stkhldr;
540 LOOP
541 FETCH csr_ee_stkhldr INTO l_ee_stkhldr_id ;
542 EXIT WHEN csr_ee_stkhldr%NOTFOUND;
543
544 pay_element_extra_info_api.delete_element_extra_info
545 (p_validate => FALSE
546 ,p_element_type_extra_info_id => l_ee_stkhldr_id
547 ,p_object_version_number => l_ee_stkhldr_ovn
548 );
549
550 END LOOP;
551 CLOSE csr_ee_stkhldr;
552
553 hr_utility.set_location(l_proc, 20);
554
555 FOR csr_te_stkhldr_rec IN csr_te_stkhldr LOOP
556 l_te_ustrctr_id := csr_te_stkhldr_rec.template_id;
557 END LOOP;
558
559 hr_utility.set_location(l_proc, 30);
560
561 pay_element_template_api.delete_user_structure
562 (p_validate => FALSE
563 ,p_drop_formula_packages => TRUE
564 ,p_template_id => l_te_ustrctr_id
565 );
566
567 hr_utility.set_location('Leaving :'||l_proc, 40);
568
569 END delete_user_template;
570 --
571 END pqp_gb_stakeholder_template;