1 Package Body ben_cwb_bdgt_chng_pop as
2 /* $Header: bencwbchngpop.pkb 120.0 2005/05/28 13:34 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ben_cwb_bdgt_chng_pop.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------------< CLEAR_BDGT_VALS >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure clear_bdgt_vals
13 (p_validate in boolean default false
14 ,p_grp_per_in_ler_id in number
15 ,p_grp_pl_id in number
16 ,p_current_bdgt_pop in varchar2
17 ,p_lf_evt_ocrd_dt in date
18 ,p_effective_date in date default sysdate
19 ,p_logon_person_id in number
20 ) is
21 --
22 -- Declare cursors and local variables
23 --
24 -- Cursor for getting Population under a Manager depending upon the Pop code
25 --
26 cursor csr_get_population
27 (c_group_per_in_ler_id number,
28 c_group_pl_id number,
29 c_lf_evt_ocrd_dt date) is
30 Select max(Hrchy.emp_per_in_ler_id) GrpLerId,
31 max(Grp.group_pl_id) GrpPlId,
32 max(Grp.group_oipl_id) GrpOiplId,
33 max(Grp.object_version_number) Ovn
34 From ben_cwb_group_hrchy Hrchy,
35 ben_cwb_person_groups Grp,
36 ben_cwb_summary Summ
37 Where Hrchy.mgr_per_in_ler_id = c_group_per_in_ler_id
38 and Hrchy.lvl_num > 0
39 and Grp.group_per_in_ler_id = Hrchy.emp_per_in_ler_id
40 and Grp.group_pl_id = c_group_pl_id
41 and Grp.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
42 and Summ.group_per_in_ler_id = Grp.group_per_in_ler_id
43 and Summ.group_pl_id = Grp.group_pl_id
44 and Summ.group_oipl_id = Grp.group_oipl_id
45 and Summ.lf_evt_ocrd_dt = Grp.lf_evt_ocrd_dt
46 Group by Summ.group_per_in_ler_id,
47 Summ.group_pl_id,
48 Summ.group_oipl_id
49 having sum(Summ.elig_count_all) > 0
50 and (max(dist_bdgt_val) is not null or max(ws_bdgt_val) is not null or
51 max(dist_bdgt_iss_val) is not null or max(ws_bdgt_iss_val) is not null)
52 Order by GrpLerId;
53 --
54 l_lf_evt_ocrd_date date;
55 l_effective_date date;
56 l_current_bdgt_pop varchar2(30);
57 l_proc varchar2(72) := g_package||'clear_bdgt_vals';
58 begin
59 --
60 hr_utility.set_location('Entering:'|| l_proc, 10);
61 --
62 -- Issue a savepoint
63 --
64 savepoint clear_bdgt_vals;
65 --
66 -- Truncate the time portion from all IN date parameters
67 --
68 l_lf_evt_ocrd_date := trunc(p_lf_evt_ocrd_dt);
69 l_effective_date := trunc(p_effective_date);
70 --
71 -- Loop the Cursor and clear the bdgt values for the Population
72 --
73 for Pop in csr_get_population(p_grp_per_in_ler_id,
74 p_grp_pl_id,
75 p_lf_evt_ocrd_dt) loop
76 --
77 ben_cwb_person_groups_api.update_group_budget
78 (p_group_per_in_ler_id => Pop.GrpLerId
79 ,p_group_pl_id => Pop.GrpPlId
80 ,p_group_oipl_id => Pop.GrpOiplId
81 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
82 ,p_dist_bdgt_val => null
83 ,p_ws_bdgt_val => null
84 ,p_dist_bdgt_iss_val => null
85 ,p_ws_bdgt_iss_val => null
86 ,p_ws_bdgt_iss_date => null
87 ,p_dist_bdgt_iss_date => null
88 ,p_bdgt_pop_cd => null
89 ,p_ws_bdgt_val_last_upd_date => l_effective_date
90 ,p_ws_bdgt_val_last_upd_by => p_logon_person_id
91 ,p_dist_bdgt_val_last_upd_date=> l_effective_date
92 ,p_dist_bdgt_val_last_upd_by => p_logon_person_id
93 ,p_object_version_number => Pop.Ovn
94 );
95 end loop;
96 -- End of For Loop (Cursor)
97 --
98 -- Update the Summary Table (Fixed bug# 3544468)
99 ben_cwb_summary_pkg.save_pl_sql_tab;
100 --
101 -- When in validation only mode raise the Validate_Enabled exception
102 --
103 if p_validate then
104 raise hr_api.validate_enabled;
105 end if;
106 --
107 hr_utility.set_location(' Leaving:'|| l_proc, 70);
108 exception
109 when hr_api.validate_enabled then
110 --
111 -- As the Validate_Enabled exception has been raised
112 -- we must rollback to the savepoint
113 --
114 rollback to clear_bdgt_vals;
115 --
116 hr_utility.set_location(' Leaving:'|| l_proc, 80);
117 --
118 when others then
119 --
120 -- A validation or unexpected error has occured
121 --
122 rollback to clear_bdgt_vals;
123 --
124 -- Reset IN OUT parameters and set all
125 -- OUT parameters, including warnings, to null
126 --
127 hr_utility.set_location(' Leaving:'|| l_proc, 90);
128 raise;
129 end clear_bdgt_vals;
130 -- End of Procedure
131 --
132 end ben_cwb_bdgt_chng_pop;
133 -- End of Package
[Home] [Help]