1 package pqh_budget AUTHID CURRENT_USER as
2 /* $Header: pqprochg.pkh 120.1.12000000.1 2007/01/16 23:24:08 appldev noship $ */
3
4 procedure lock_worksheet_detail(p_worksheet_detail_id in number,
5 p_object_version_number in number default null,
6 p_status out nocopy varchar2) ;
7 procedure lock_children(p_worksheet_detail_id in number,
8 p_transaction_category_id in number,
9 p_status in out nocopy varchar2,
13 p_status in out nocopy varchar2,
10 p_working_users in out nocopy varchar2) ;
11 procedure lock_all_children(p_worksheet_detail_id in number,
12 p_transaction_category_id in number,
14 p_working_users in out nocopy varchar2) ;
15 procedure complete_workflow(p_worksheet_detail_id in number,
16 p_transaction_category_id in number,
17 p_result_status in varchar2,
18 p_wks_object_version_number out nocopy number,
19 p_wkd_object_version_number out nocopy number) ;
20
21 procedure post_changes(p_worksheet_detail_id in number,
22 p_budget_style_cd in varchar2,
23 p_unit1_aggregate in varchar2,
24 p_unit2_aggregate in varchar2,
25 p_unit3_aggregate in varchar2);
26
27 procedure propagate_worksheet_changes (p_change_mode in varchar2,
28 p_worksheet_detail_id in number,
29 p_budget_style_cd in varchar2,
30 p_new_wks_unit1_value in number,
31 p_new_wks_unit2_value in number,
32 p_new_wks_unit3_value in number,
33 p_unit1_precision in number,
34 p_unit2_precision in number,
35 p_unit3_precision in number,
36 p_unit1_aggregate in varchar2,
37 p_unit2_aggregate in varchar2,
38 p_unit3_aggregate in varchar2,
39 p_wks_unit1_available in out nocopy number,
40 p_wks_unit2_available in out nocopy number,
41 p_wks_unit3_available in out nocopy number,
42 p_object_version_number in out nocopy number);
43
44 procedure propagate_budget_changes (p_change_mode in varchar2,
45 p_worksheet_detail_id in number,
46 p_new_bgt_unit1_value in number,
47 p_new_bgt_unit2_value in number,
48 p_new_bgt_unit3_value in number,
49 p_unit1_precision in number,
50 p_unit2_precision in number,
51 p_unit3_precision in number,
52 p_unit1_aggregate in varchar2,
53 p_unit2_aggregate in varchar2,
54 p_unit3_aggregate in varchar2,
55 p_bgt_unit1_available in out nocopy number,
56 p_bgt_unit2_available in out nocopy number,
57 p_bgt_unit3_available in out nocopy number);
58
59 procedure propagate_period_changes (p_change_mode in varchar2,
60 p_worksheet_period_id in number,
61 p_new_prd_unit1_value in number,
62 p_new_prd_unit2_value in number,
63 p_new_prd_unit3_value in number,
64 p_unit1_precision in number,
65 p_unit2_precision in number,
66 p_unit3_precision in number,
67 p_prd_unit1_available in out nocopy number,
68 p_prd_unit2_available in out nocopy number,
69 p_prd_unit3_available in out nocopy number);
70
71 procedure delete_delegate(p_worksheet_detail_id in number);
72
73 procedure delete_delegate_chk(p_worksheet_detail_id in number,
74 p_status_flag out nocopy number) ;
75
76 procedure delegate_adjustment( p_delegate_org_id in number,
77 p_parent_wd_id in number,
78 p_delegate_wd_id in number,
79 p_delegate_ovn in out nocopy number,
80 p_org_str_id in number,
81 p_budget_style_cd in varchar2,
82 p_del_budget_unit1_value in out nocopy number,
83 p_del_budget_unit2_value in out nocopy number,
84 p_del_budget_unit3_value in out nocopy number,
85 p_del_budget_unit1_available in out nocopy number,
86 p_del_budget_unit2_available in out nocopy number,
87 p_del_budget_unit3_available in out nocopy number,
88 p_wks_budget_unit1_value in out nocopy number,
89 p_wks_budget_unit2_value in out nocopy number,
90 p_wks_budget_unit3_value in out nocopy number,
91 p_wks_budget_unit1_available in out nocopy number,
92 p_wks_budget_unit2_available in out nocopy number,
93 p_wks_budget_unit3_available in out nocopy number) ;
94
95 procedure delegate_delete_adjustment(p_parent_wd_id in number,
96 p_delegate_wd_id in number,
97 p_budget_style_cd in varchar2,
98 p_budget_unit1_value in out nocopy number,
99 p_budget_unit2_value in out nocopy number,
100 p_budget_unit3_value in out nocopy number,
101 p_budget_unit1_available in out nocopy number,
102 p_budget_unit2_available in out nocopy number,
106 p_budgeted_entity_cd in varchar,
103 p_budget_unit3_available in out nocopy number);
104
105 procedure insert_from_budget(p_budget_version_id in number,
107 p_worksheet_id in number,
108 p_business_group_id in number,
109 p_start_organization_id in number,
110 p_parent_worksheet_detail_id in number,
111 p_worksheet_unit1_available in out nocopy number,
112 p_worksheet_unit2_available in out nocopy number,
113 p_worksheet_unit3_available in out nocopy number,
114 p_worksheet_unit1_value in out nocopy number,
115 p_worksheet_unit2_value in out nocopy number,
116 p_worksheet_unit3_value in out nocopy number,
117 p_org_hier_ver in number,
118 p_copy_budget_periods in varchar2,
119 p_budget_style_cd in varchar,
120 p_rows_inserted out nocopy number) ;
121
122 procedure populate_bud_grades(p_parent_worksheet_detail_id in number,
123 p_worksheet_id in number,
124 p_business_group_id in number,
125 p_rows_inserted out nocopy number) ;
126
127 procedure populate_bud_jobs(p_parent_worksheet_detail_id in number,
128 p_worksheet_id in number,
129 p_business_group_id in number,
130 p_rows_inserted out nocopy number) ;
131
132 procedure populate_bud_positions(p_parent_worksheet_detail_id in number,
133 p_worksheet_id in number,
134 p_org_hier_ver in number,
135 p_start_organization_id in number,
136 p_business_group_id in number,
137 p_rows_inserted out nocopy number) ;
138 procedure populate_bud_organizations(p_parent_worksheet_detail_id in number,
139 p_worksheet_id in number,
140 p_org_hier_ver in number,
141 p_start_organization_id in number,
142 p_business_group_id in number,
143 p_rows_inserted out nocopy number) ;
144
145 procedure populate_del_orgs(p_parent_worksheet_detail_id in number,
146 p_wks_propagation_method in varchar2,
147 p_worksheet_id in number,
148 p_start_organization_id in number,
149 p_org_hier_ver in number,
150 p_rows_inserted out nocopy number) ;
151
152 procedure copy_all_budget_details(p_worksheet_id in number);
153
154 procedure copy_budget_details(p_budget_detail_id in number,
155 p_worksheet_detail_id in number,
156 p_unit1_aggregate in varchar2,
157 p_unit2_aggregate in varchar2,
158 p_unit3_aggregate in varchar2,
159 p_unit1_precision in number,
160 p_unit2_precision in number,
161 p_unit3_precision in number,
162 p_budget_unit1_value in number,
163 p_budget_unit2_value in number,
164 p_budget_unit3_value in number,
165 p_budget_unit1_available in out nocopy number,
166 p_budget_unit2_available in out nocopy number,
167 p_budget_unit3_available in out nocopy number) ;
168
169 procedure pop_bud_tables(p_budget_version_id in number,
170 p_budgeted_entity_cd in varchar) ;
171
172 procedure pop_bud_tables(p_parent_worksheet_detail_id in number,
173 p_budgeted_entity_cd in varchar) ;
174
175 procedure pop_del_tables(p_parent_worksheet_detail_id in number);
176
177 procedure insert_org_is_del(p_org_id number) ;
178 procedure delete_org_is_del(p_org_id number) ;
179 procedure calc_org_is_del(p_str out nocopy varchar2) ;
180 procedure delete_org_is_del ;
181 function already_delegated_org(p_org_id number) return varchar2 ;
182 procedure insert_org_is_bud(p_org_id number) ;
183 procedure delete_org_is_bud(p_org_id number) ;
184 procedure calc_org_is_bud(p_str out nocopy varchar2) ;
185 procedure delete_org_is_bud ;
186 function already_budgeted_org(p_org_id number) return varchar2 ;
187 procedure insert_pos_is_bud(p_pos_id number) ;
188 procedure delete_pos_is_bud(p_pos_id number) ;
189 procedure calc_pos_is_bud(p_str out nocopy varchar2) ;
190 procedure delete_pos_is_bud ;
191 function already_budgeted_pos(p_pos_id number) return varchar2 ;
192 procedure insert_pot_is_bud(p_pot_id number) ;
193 procedure delete_pot_is_bud(p_pot_id number) ;
194 procedure calc_pot_is_bud(p_str out nocopy varchar2) ;
195 procedure delete_pot_is_bud ;
196 function already_budgeted_pot(p_pot_id number) return varchar2 ;
197 procedure insert_job_is_bud(p_job_id number) ;
198 procedure delete_job_is_bud(p_job_id number) ;
199 procedure calc_job_is_bud(p_str out nocopy varchar2) ;
200 procedure delete_job_is_bud ;
201 function already_budgeted_job(p_job_id number) return varchar2 ;
202 procedure insert_grd_is_bud(p_grd_id number) ;
203 procedure delete_grd_is_bud(p_grd_id number) ;
204 procedure calc_grd_is_bud(p_str out nocopy varchar2) ;
205 procedure delete_grd_is_bud ;
206 function already_budgeted_grd(p_grd_id number) return varchar2 ;
210 p_budgeted_entity varchar2 ) return varchar2 ;
207 function already_budgeted(p_position_id number,
208 p_job_id number,
209 p_organization_id number,
211 function already_budgeted_pot(p_position_transaction_id number,
212 p_job_id number,
213 p_organization_id number,
214 p_budgeted_entity varchar2 ) return varchar2 ;
215 procedure init_prd_tab(p_budget_id in number) ;
216 procedure add_prd(p_prd_start_date in date,
217 p_prd_end_date in date,
218 p_unit1_aggregate in varchar2,
219 p_unit2_aggregate in varchar2,
220 p_unit3_aggregate in varchar2,
221 p_prd_unit1_value in number,
222 p_prd_unit2_value in number,
223 p_prd_unit3_value in number ) ;
224 procedure chk_unit_sum(p_unit1_sum_value out nocopy number,
225 p_unit2_sum_value out nocopy number,
226 p_unit3_sum_value out nocopy number) ;
227 procedure chk_unit_avg(p_unit1_avg_value out nocopy number,
228 p_unit2_avg_value out nocopy number,
229 p_unit3_avg_value out nocopy number) ;
230 procedure chk_unit_max(p_unit1_max_value out nocopy number,
231 p_unit2_max_value out nocopy number,
232 p_unit3_max_value out nocopy number) ;
233 procedure sub_prd(p_prd_start_date in date,
234 p_prd_end_date in date,
235 p_unit1_aggregate in varchar2,
236 p_unit2_aggregate in varchar2,
237 p_unit3_aggregate in varchar2,
238 p_prd_unit1_value in number,
239 p_prd_unit2_value in number,
240 p_prd_unit3_value in number ) ;
241 procedure add_budgetrow(p_worksheet_detail_id in number,
242 p_unit1_aggregate in varchar2,
243 p_unit2_aggregate in varchar2,
244 p_unit3_aggregate in varchar2) ;
245 procedure sub_budgetrow(p_worksheet_detail_id in number,
246 p_unit1_aggregate in varchar2,
247 p_unit2_aggregate in varchar2,
248 p_unit3_aggregate in varchar2);
249 procedure add_budgetrow(p_budget_detail_id in number,
250 p_unit1_aggregate in varchar2,
251 p_unit2_aggregate in varchar2,
252 p_unit3_aggregate in varchar2) ;
253 procedure sub_budgetrow(p_budget_detail_id in number,
254 p_unit1_aggregate in varchar2,
255 p_unit2_aggregate in varchar2,
256 p_unit3_aggregate in varchar2);
257 PROCEDURE bgt_chg_bgt_available(p_unit1_aggregate in varchar2,
258 p_unit2_aggregate in varchar2,
259 p_unit3_aggregate in varchar2,
260 p_unit1_value in number,
261 p_unit2_value in number,
262 p_unit3_value in number,
263 p_unit1_precision in number,
264 p_unit2_precision in number,
265 p_unit3_precision in number,
266 p_unit1_available out nocopy number,
267 p_unit2_available out nocopy number,
268 p_unit3_available out nocopy number ) ;
269 PROCEDURE prd_chg_bgt_available(p_unit1_aggregate in varchar2,
270 p_unit2_aggregate in varchar2,
271 p_unit3_aggregate in varchar2,
272 p_prd_start_date in date,
273 p_prd_end_date in date,
274 p_unit1_value in number,
275 p_unit2_value in number,
276 p_unit3_value in number,
277 p_bgt_unit1_value in number,
278 p_bgt_unit2_value in number,
279 p_bgt_unit3_value in number,
280 p_unit1_precision in number,
281 p_unit2_precision in number,
282 p_unit3_precision in number,
283 p_unit1_available in out nocopy number,
284 p_unit2_available in out nocopy number,
285 p_unit3_available in out nocopy number ) ;
286 function get_prdtab_count return number ;
287 procedure get_prdtab_values(p_num in number,
288 p_start_date out nocopy date,
289 p_unit1 out nocopy number,
290 p_unit2 out nocopy number,
291 p_unit3 out nocopy number) ;
292 procedure insert_worksheet_detail(
293 p_worksheet_id in number,
294 p_organization_id in number default null,
295 p_job_id in number default null,
296 p_position_id in number default null,
297 p_grade_id in number default null,
298 p_position_transaction_id in number default null,
299 p_budget_detail_id in number default null,
300 p_parent_worksheet_detail_id in number default null,
301 p_user_id in number default null,
302 p_action_cd in varchar2 default null,
303 p_budget_unit1_percent in number default null,
304 p_budget_unit1_value in number default null,
305 p_budget_unit2_percent in number default null,
306 p_budget_unit2_value in number default null,
307 p_budget_unit3_percent in number default null,
308 p_budget_unit3_value in number default null,
312 p_status in varchar2 default null,
309 p_budget_unit1_value_type_cd in varchar2 default null,
310 p_budget_unit2_value_type_cd in varchar2 default null,
311 p_budget_unit3_value_type_cd in varchar2 default null,
313 p_budget_unit1_available in number default null,
314 p_budget_unit2_available in number default null,
315 p_budget_unit3_available in number default null,
316 p_old_unit1_value in number default null,
317 p_old_unit2_value in number default null,
318 p_old_unit3_value in number default null,
319 p_defer_flag in varchar2 default null,
320 p_propagation_method in varchar2 default null,
321 p_worksheet_detail_id out nocopy number,
322 p_copy_budget_periods in varchar2 default 'N'
323 ) ;
324 Procedure update_worksheet_detail
325 (
326 p_effective_date in date,
327 p_worksheet_detail_id in number,
328 p_worksheet_id in number default hr_api.g_number,
329 p_organization_id in number default hr_api.g_number,
330 p_job_id in number default hr_api.g_number,
331 p_position_id in number default hr_api.g_number,
332 p_grade_id in number default hr_api.g_number,
333 p_position_transaction_id in number default hr_api.g_number,
334 p_budget_detail_id in number default hr_api.g_number,
335 p_parent_worksheet_detail_id in number default hr_api.g_number,
336 p_user_id in number default hr_api.g_number,
337 p_action_cd in varchar2 default hr_api.g_varchar2,
338 p_budget_unit1_percent in number default hr_api.g_number,
339 p_budget_unit1_value in number default hr_api.g_number,
340 p_budget_unit2_percent in number default hr_api.g_number,
341 p_budget_unit2_value in number default hr_api.g_number,
342 p_budget_unit3_percent in number default hr_api.g_number,
343 p_budget_unit3_value in number default hr_api.g_number,
344 p_object_version_number in out nocopy number,
345 p_budget_unit1_value_type_cd in varchar2 default hr_api.g_varchar2,
346 p_budget_unit2_value_type_cd in varchar2 default hr_api.g_varchar2,
347 p_budget_unit3_value_type_cd in varchar2 default hr_api.g_varchar2,
348 p_status in varchar2 default hr_api.g_varchar2,
349 p_budget_unit1_available in number default hr_api.g_number,
350 p_budget_unit2_available in number default hr_api.g_number,
351 p_budget_unit3_available in number default hr_api.g_number,
352 p_old_unit1_value in number default hr_api.g_number,
353 p_old_unit2_value in number default hr_api.g_number,
354 p_old_unit3_value in number default hr_api.g_number,
355 p_defer_flag in varchar2 default hr_api.g_varchar2,
356 p_propagation_method in varchar2 default hr_api.g_varchar2
357 ) ;
358 procedure copy_budget_periods(p_budget_detail_id in number,
359 p_worksheet_detail_id in number,
360 p_copy_budget_periods in varchar2,
361 p_budget_unit1_value in number,
362 p_budget_unit2_value in number,
363 p_budget_unit3_value in number);
364 procedure insert_from_budget(p_budget_version_id in number,
365 p_budgeted_entity_cd in varchar,
366 p_worksheet_id in number,
367 p_business_group_id in number,
368 p_start_organization_id in number,
369 p_parent_worksheet_detail_id in number,
370 p_org_hier_ver in number,
371 p_copy_budget_periods in varchar2,
372 p_rows_inserted out nocopy number) ;
373 FUNCTION get_currency_cd (p_budget_id in number) RETURN varchar2 ;
374 --
375 /*
376 procedure calculates the budget detail available values
377 */
378 PROCEDURE calculate_bgt_det_available(p_unit1_aggregate in varchar2,
379 p_unit2_aggregate in varchar2,
380 p_unit3_aggregate in varchar2,
381 p_bgt_unit1_value in number,
382 p_bgt_unit2_value in number,
383 p_bgt_unit3_value in number,
384 p_unit1_precision in number,
385 p_unit2_precision in number,
386 p_unit3_precision in number,
387 p_unit1_available in out nocopy number,
388 p_unit2_available in out nocopy number,
389 p_unit3_available in out nocopy number );
390 --
391 -- Add Budget Row used in Position form
392 --
393 procedure add_budgetrow(p_budget_detail_id in number,
394 p_unit1_aggregate in varchar2,
395 p_unit2_aggregate in varchar2,
396 p_unit3_aggregate in varchar2,
397 p_budget_id in number);
398 --
399 procedure add_budgetrow(p_worksheet_detail_id in number,
400 p_unit1_aggregate in varchar2,
401 p_unit2_aggregate in varchar2,
402 p_unit3_aggregate in varchar2,
403 p_budget_id in number);
404 --
405 end pqh_budget;
[Home] [Help]