DBA Data[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;