1 Package body pqh_reallocation_pkg as
2 /* $Header: pqrealoc.pkb 115.11 2004/04/20 09:53:40 hsajja noship $ */
3 --
4 -- Function to calculate the reallocation amt for the given position and dates
5 --
6
7 function get_reallocation(p_position_id in number default null
8 ,p_job_id in number default null
9 ,p_grade_id in number default null
10 ,p_organization_id in number default null
11 ,p_budget_entity in varchar2 default 'POSITION'
12 ,p_start_date in date default sysdate
13 ,p_end_date in date default sysdate
14 ,p_effective_date in date default sysdate
15 ,p_system_budget_unit in varchar2
16 ,p_business_group_id in number
17 ) return number is
18 --
19 -- Cursor to fetch the reallocation_amt, budget dates and period_set_name
20 -- of the budget for the given position/job/grade/organization, system budget unit
21 -- and for the given start_date and end_date
22 --
23 /* Re-writing the cursor inline with the revised reallocation functionality
24 *************************************************************************kgowirpe
25 cursor c_reallocation is
26 Select
27 bud.period_set_name,
28 bud.budget_start_date,
29 bud.budget_end_date,
30 bpr.reallocation_amt,
31 decode(p_system_budget_unit,
32 PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit1_id), budget_unit1_aggregate,
33 PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit2_id), budget_unit2_aggregate,
34 PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit3_id), budget_unit3_aggregate) budget_unit_aggregate
35 from
36 pqh_bdgt_pool_realloctions bpr,
37 pqh_budget_pools bpl,
38 pqh_budgets bud,
39 pqh_budget_versions bvr
40 where
41 bpl.pool_id = bpr.pool_id
42 and trunc(p_effective_date) between trunc(bud.budget_start_date) and trunc(bud.budget_end_date)
43 and bud.budget_id = bvr.budget_id
44 and trunc(p_effective_date) between trunc(bvr.date_from) and trunc(bvr.date_to)
45 and bvr.budget_version_id = bpl.budget_version_id
46 and nvl(bud.position_control_flag,'X') = 'Y'
47 and bud.budgeted_entity_cd = p_budget_entity
48 and nvl(p_position_id, nvl(bpr.position_id, -1)) =
49 nvl(bpr.position_id, -1)
50 --
51 -- Commented because no reallocation is possible for entities - job, grade and organization.
52 --
53 -- and nvl(p_organization_id, nvl(bpr.organization_id, -1)) =
54 -- nvl(bpr.organization_id, -1)
55 -- and nvl(p_job_id, nvl(bpr.job_id, -1)) =
56 -- nvl(bpr.job_id, -1)
57 -- and nvl(p_grade_id, nvl(bpr.grade_id, -1)) =
58 -- nvl(bpr.grade_id, -1)
59 and PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(bpl.budget_unit_id) = p_system_budget_unit
60 and (p_effective_date between bvr.date_from and bvr.date_to)
61 and bud.business_group_id = p_business_group_id
62 and ((p_start_date <= budget_start_date
63 and p_end_date >= budget_end_date
64 ) or
65 (p_start_date between budget_start_date and budget_end_date) or
66 (p_end_date between budget_start_date and budget_end_date)
67 )
68 and (
69 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = p_system_budget_unit
70 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_system_budget_unit
71 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_system_budget_unit
72 );
73 ************************************************************************************kgowripe
74 */
75 cursor c_reallocation is
76 Select
77 bud.period_set_name,
78 bud.budget_start_date,
79 bud.budget_end_date,
80 decode(rec_amt.transaction_type,'DD',-1*rec_amt.reallocation_amt,'RD',rec_amt.reallocation_amt) reallocation_amt,
81 decode(p_system_budget_unit,
82 PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit1_id), budget_unit1_aggregate,
83 PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit2_id), budget_unit2_aggregate,
84 PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(budget_unit3_id), budget_unit3_aggregate) budget_unit_aggregate
85 from pqh_budget_pools fld,
86 pqh_budget_pools trnx,
87 pqh_bdgt_pool_realloctions trnx_dtl,
88 pqh_bdgt_pool_realloctions rec_amt,
89 pqh_budgets bud,
90 pqh_budget_versions bvr
91 where trunc(p_effective_date) between trunc(bud.budget_start_date) and trunc(bud.budget_end_date)
92 and nvl(bud.position_control_flag,'X') = 'Y'
93 and bud.budgeted_entity_cd = p_budget_entity
94 and bud.business_group_id = p_business_group_id
95 and ((p_start_date <= budget_start_date
96 and p_end_date >= budget_end_date
97 ) or
98 (p_start_date between budget_start_date and budget_end_date) or
99 (p_end_date between budget_start_date and budget_end_date)
100 )
101 and (
102 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = p_system_budget_unit
103 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_system_budget_unit
104 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_system_budget_unit
105 )
106 and bud.budget_id = bvr.budget_id
107 and trunc(p_effective_date) between trunc(bvr.date_from) and trunc(bvr.date_to)
108 and bvr.budget_version_id = fld.budget_version_id
109 and PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(fld.budget_unit_id) = p_system_budget_unit
110 and fld.pool_id = trnx.parent_pool_id
111 and trnx.pool_id = trnx_dtl.pool_id
112 and trnx_dtl.reallocation_id = rec_amt.txn_detail_id
113 and nvl(p_position_id, nvl(rec_amt.entity_id, -1)) =
114 nvl(rec_amt.entity_id, -1)
115 and nvl(p_organization_id, nvl(rec_amt.entity_id, -1)) =
116 nvl(rec_amt.entity_id, -1)
117 and nvl(p_job_id, nvl(rec_amt.entity_id, -1)) =
118 nvl(rec_amt.entity_id, -1)
119 and nvl(p_grade_id, nvl(rec_amt.entity_id, -1)) =
120 nvl(rec_amt.entity_id, -1);
121 --
122 -- Local Variables
123 --
124 l_total_realloc number;
125 l_prorate_ratio number := 1;
126 calc_start_date date;
127 calc_end_date date;
128 --
129 begin
130 --
131 for l_reallocation in c_reallocation
132 loop
133 if (p_system_budget_unit = 'MONEY' or p_system_budget_unit = 'HOURS'
134 or l_reallocation.budget_unit_aggregate = 'ACCUMULATE') then
135 --
136 calc_start_date := greatest(l_reallocation.budget_start_date, p_start_date);
137 calc_end_date := least(l_reallocation.budget_end_date, p_end_date);
138 --
139 -- Calculate the prorate ratio
140 --
141 l_prorate_ratio := pqh_budgeted_salary_pkg.get_prorate_ratio
142 -- Hima, greates/least functions for p_start_date and p_end_date
143 ( calc_start_date
144 , calc_end_date
145 , l_reallocation.period_set_name
146 , l_reallocation.budget_start_date
147 , l_reallocation.budget_end_date
148 );
149 --
150 -- Calculate the prorated reallocation amount for the current budget and add to the l_total_realloc
151 --
152 l_total_realloc := nvl(l_total_realloc,0) + l_reallocation.reallocation_amt * l_prorate_ratio;
153 else
154 l_total_realloc := nvl(l_total_realloc,0) + l_reallocation.reallocation_amt;
155 --
156 end if;
157 end loop;
158 --
159 -- Return the calculated total reallocation amount
160 --
161 if l_total_realloc is null then
162 return null;
163 else
164 Return(trunc(nvl(l_total_realloc,0),2));
165 end if;
166 --
167 end;
168 --
169 function get_reallocated_money(p_position_id in number
170 ,p_business_group_id in number
171 ,p_type in varchar2 default 'DNTD'
172 ,p_start_date in date default sysdate
173 ,p_end_date in date default sysdate
174 ,p_effective_date in date default sysdate) return number is
175 l_txn_type varchar2(30);
176 cursor c_reallocation is
177 Select bud.period_set_name,
178 bud.budget_start_date,
179 bud.budget_end_date,
180 rec_amt.reallocation_amt,
181 rec_amt.reserved_amt
182 from pqh_budget_pools fld,
183 pqh_budget_pools trnx,
184 pqh_bdgt_pool_realloctions trnx_dtl,
185 pqh_bdgt_pool_realloctions rec_amt,
186 pqh_budgets bud,
187 pqh_budget_versions bvr
188 where trunc(p_effective_date) between bud.budget_start_date and bud.budget_end_date
189 and bud.position_control_flag = 'Y'
190 and bud.budgeted_entity_cd = 'POSITION'
191 and rec_amt.transaction_type = l_txn_type
192 and bud.business_group_id = p_business_group_id
193 and ((p_start_date <= budget_start_date
194 and p_end_date >= budget_end_date
195 ) or
196 (p_start_date between budget_start_date and budget_end_date) or
197 (p_end_date between budget_start_date and budget_end_date)
198 )
199 and ( hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
200 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
201 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY')
202 and bud.budget_id = bvr.budget_id
203 and trunc(p_effective_date) between bvr.date_from and bvr.date_to
204 and bvr.budget_version_id = fld.budget_version_id
205 and PQH_PSF_BUS.GET_SYSTEM_SHARED_TYPE(fld.budget_unit_id) = 'MONEY'
206 and fld.pool_id = trnx.parent_pool_id
207 and trnx.pool_id = trnx_dtl.pool_id
208 and trnx_dtl.reallocation_id = rec_amt.txn_detail_id
209 and rec_amt.entity_id = p_position_id;
210
211 l_total_realloc number := 0;
212 l_total_reserve number := 0;
213 l_prorate_ratio number := 1;
214 calc_start_date date;
215 calc_end_date date;
216 --
217 begin
218 --
219 hr_utility.set_location('mode passed'||p_type,10);
220 if p_type = 'RCVD' then
221 l_txn_type := 'RD';
222 elsif p_type in ('DNTD','RSRVD') then
223 l_txn_type := 'DD';
224 else
225 hr_utility.set_location('wrong mode passed'||p_type,10);
226 end if;
227 for l_reallocation in c_reallocation loop
228 calc_start_date := greatest(l_reallocation.budget_start_date, p_start_date);
229 calc_end_date := least(l_reallocation.budget_end_date, p_end_date);
230 --
231 -- Calculate the prorate ratio
232 --
233 hr_utility.set_location('period set name '||l_reallocation.period_set_name,20);
234 l_prorate_ratio := pqh_budgeted_salary_pkg.get_prorate_ratio
235 ( calc_start_date
236 , calc_end_date
237 , l_reallocation.period_set_name
238 , l_reallocation.budget_start_date
239 , l_reallocation.budget_end_date
240 );
241 hr_utility.set_location('prorate ratio is'||to_char(l_prorate_ratio),20);
242 if p_type in ('RCVD','DNTD') then
243 l_total_realloc := l_total_realloc + (nvl(l_reallocation.reallocation_amt,0) * l_prorate_ratio);
244 hr_utility.set_location('realoc amt is'||to_char(l_reallocation.reallocation_amt),20);
245 hr_utility.set_location('total realloc amt is'||to_char(l_total_realloc),20);
246 elsif p_type = 'RSRVD' then
247 l_total_reserve := l_total_reserve + (nvl(l_reallocation.reserved_amt,0) * l_prorate_ratio);
248 hr_utility.set_location('reserve amt is'||to_char(l_reallocation.reserved_amt),20);
249 hr_utility.set_location('total reserve amt is'||to_char(l_total_reserve),20);
250 end if;
251 end loop;
252
253 if p_type in ('RCVD','DNTD') then
254 return(trunc(l_total_realloc,2));
255 elsif p_type = 'RSRVD' then
256 return(trunc(l_total_reserve,2));
257 end if;
258 --
259 end;
260 --
261 end;
[Home] [Help]