1 PACKAGE pa_budget_utils AUTHID CURRENT_USER AS
2 -- $Header: PAXBUBUS.pls 120.4.12020000.3 2013/04/19 09:55:45 krkondur ship $
3
4
5 ---------------------------------------------------------
6 --- GLOBAL VARIABLES
7 ---------------------------------------------------------
8
9 g_entry_level_code varchar2(1);
10
11
12 -- Verify_Budget_Rules API ------------------------------
13 -- Added this global, which is populated by the Budgets form via a function call to this package.
14
15 G_Bgt_Intg_Flag VARCHAR2(1) :=NULL;
16
17
18 -- Get_Project_Currency_Info API ------------------------
19 -- These globals will be populated by the API when the p_project_id IN-parameter varies from the
20 -- G_Project_Id global.
21
22 G_Project_id pa_projects_all.project_id%TYPE := -1;
23
24 G_Projfunc_Currency_Code pa_projects_all.projfunc_currency_code%TYPE := NULL;
25
26 G_Project_Currency_Code pa_projects_all.project_currency_code%TYPE := NULL;
27
28 G_Txn_Currency_Code pa_projects_all.projfunc_currency_code%TYPE := NULL;
29
30
31
32
33
34
35
36
37
38
39
40 ---------------------------------------------------------
41 --- FUNCTIONS AND PROCEDURES
42 ---------------------------------------------------------
43
44
45 /****************************************************************
46 This function returns a value 'Y' if the UOM passed is a currency
47 UOM. Otherwise it returns 'N'.
48 ******************************************************************/
49 Function Check_Currency_Uom (x_uom_code in varchar2) return varchar2 ;
50 -- pragma RESTRICT_REFERENCES (Check_Currency_Uom, WNDS, WNPS );
51
52 /****************************************************************
53 This function returns the value of budget amount code associated
54 with the budget type. Budget Amount Code determines whether its a
55 cost or a revenue budget.
56 ******************************************************************/
57 Function get_budget_amount_code(x_budget_type_code in varchar2) return varchar2 ;
58 -- pragma RESTRICT_REFERENCES ( get_budget_amount_code, WNDS, WNPS );
59
60
61
62 procedure get_draft_version_id (x_project_id in number,
63 x_budget_type_code in varchar2,
64 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
65 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
66 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
67 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
68
69 procedure get_baselined_version_id (x_project_id in number,
70 x_budget_type_code in varchar2,
71 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
75
72 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
73 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
74 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
76 procedure get_original_version_id (x_project_id in number,
77 x_budget_type_code in varchar2,
78 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
79 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
80 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
81 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
82
83 procedure get_default_resource_list_id (x_project_id in number,
84 x_budget_type_code in varchar2,
85 x_resource_list_id in out NOCOPY number, --File.Sql.39 bug 4440895
86 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
87 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
88 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
89
90 procedure get_default_entry_method_code (x_project_id in number,
91 x_budget_type_code in varchar2,
92 x_budget_entry_method_code in out NOCOPY varchar2, --File.Sql.39 bug 4440895
93 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
94 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
95 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
96
97 function get_budget_type_code (x_budget_type in varchar2) return varchar2;
98
99 function get_budget_entry_method_code (x_budget_entry_method in varchar2)
100 return varchar2;
101
102 function get_change_reason_code (x_meaning in varchar2) return varchar2;
103
104 function check_proj_budget_exists (x_project_id in number,
105 x_budget_status_code IN varchar2,
106 x_budget_type_code IN varchar2 default NULL,
107 x_fin_plan_type_id IN NUMBER default NULL,
108 x_version_type IN VARCHAR2 default NULL
109 ) return number;
110
111 function check_task_budget_exists (x_task_id in number,
112 x_budget_status_code IN varchar2,
113 x_budget_type_code IN varchar2 default NULL,
114 x_fin_plan_type_id IN NUMBER default NULL,
115 x_version_type IN VARCHAR2 default NULL
116 ) return number;
117
118 function check_resource_member_level(x_resource_list_member_id in number,
119 x_parent_member_id in number,
120 x_budget_version_id in number,
121 x_task_id in number)
122 return number;
123
124 /*-------------------------------------------------------------------+
125 | The get_proj_budget_amount is to get proper budget amount for the |
126 | given project, budget type, and budget version type. |
127 | |
128 | Parameters: |
129 | |
130 | 1. x_project_id project id |
131 | 2. x_budget_type budget type code |
132 | 3. x_which_version 'DRAFT', 'ORIGINAL', or 'CURRENT'. |
133 | 4. x_revenue_amount budget revenue |
134 | 5. x_raw_cost budget raw cost |
135 | 6. x_burdened_cost budget burdened cost |
136 | 7. x_labor_quantity budget labor quantity |
137 | |
138 | The (x_project_id, x_budget_type, x_which_version) input values |
139 | must be given. The x_which_version value must be one of the |
140 | above three values (DRAFT/ORIGINAL/CURRENT). |
141 | |
142 | If there is no budget for the given project or you pass in bad |
143 | values, the procedure will return $0 budget amount back to the |
144 | caller. The calling module should handle the error handling. |
145 +-------------------------------------------------------------------*/
146 procedure get_proj_budget_amount(
147 x_project_id in number,
148 x_budget_type in varchar2,
149 x_which_version in varchar2,
150 x_revenue_amount out NOCOPY real, --File.Sql.39 bug 4440895
151 x_raw_cost out NOCOPY real, --File.Sql.39 bug 4440895
152 x_burdened_cost out NOCOPY real, --File.Sql.39 bug 4440895
153 x_labor_quantity out NOCOPY real); --File.Sql.39 bug 4440895
154
155 /*-------------------------------------------------------------------+
156 | The get_task_budget_amount is to get proper budget amount for the |
157 | given project, budget type, and budget version type. |
158 | |
159 | Parameters: |
160 | |
161 | 1. x_project_id project id |
162 | 2. x_task_id top task id or lowest level task id |
163 | 3. x_budget_type budget type code |
164 | 4. x_which_version 'DRAFT', 'ORIGINAL', or 'CURRENT'. |
165 | 5. x_revenue_amount budget revenue |
169 | |
166 | 6. x_raw_cost budget raw cost |
167 | 7. x_burdened_cost budget burdened cost |
168 | 8. x_labor_quantity budget labor quantity |
170 | The (x_project_id, x_budget_type, x_which_version) input values |
171 | must be given. The x_which_version value must be one of the |
172 | above three values (DRAFT/ORIGINAL/CURRENT). |
173 | |
174 | If a mid-level task id is given, it will return $0 budget amount. |
175 | The calling module should handle the error handling. |
176 | If there is no budget for the given project or you pass in bad |
177 | values, the procedure will return $0 budget amount back to the |
178 | caller. The calling module should handle the error handling. |
179 +-------------------------------------------------------------------*/
180 procedure get_task_budget_amount(
181 x_project_id in number,
182 x_task_id in number,
183 x_budget_type in varchar2,
184 x_which_version in varchar2,
185 x_revenue_amount out NOCOPY real, --File.Sql.39 bug 4440895
186 x_raw_cost out NOCOPY real, --File.Sql.39 bug 4440895
187 x_burdened_cost out NOCOPY real, --File.Sql.39 bug 4440895
188 x_labor_quantity out NOCOPY real); --File.Sql.39 bug 4440895
189
190 procedure delete_draft (x_budget_version_id in number,
191 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
192 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
193 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
194
195 procedure create_draft (x_project_id in number,
196 x_budget_type_code in varchar2,
197 x_version_name in varchar2,
198 x_description in varchar2,
199 x_resource_list_id in number,
200 x_change_reason_code in varchar2,
201 x_budget_entry_method_code in varchar2,
202 x_attribute_category in varchar2,
203 x_attribute1 in varchar2,
204 x_attribute2 in varchar2,
205 x_attribute3 in varchar2,
206 x_attribute4 in varchar2,
207 x_attribute5 in varchar2,
208 x_attribute6 in varchar2,
209 x_attribute7 in varchar2,
210 x_attribute8 in varchar2,
211 x_attribute9 in varchar2,
212 x_attribute10 in varchar2,
213 x_attribute11 in varchar2,
214 x_attribute12 in varchar2,
215 x_attribute13 in varchar2,
216 x_attribute14 in varchar2,
217 x_attribute15 in varchar2,
218 x_budget_version_id in out NOCOPY number, --File.Sql.39 bug 4440895
219 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
220 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
221 x_err_stack in out NOCOPY varchar2, --File.Sql.39 bug 4440895
222 x_pm_product_code in varchar2 default null,
223 x_pm_budget_reference in varchar2 default null
224 );
225
226 procedure create_line (x_budget_version_id in number,
227 x_project_id in number,
228 x_task_id in number,
229 x_resource_list_member_id in number,
230 x_cbs_element_id in number default null, --Bug 16604257
231 x_description in varchar2,
232 x_start_date in date,
233 x_end_date in date,
234 x_period_name in varchar2,
235 x_quantity in out NOCOPY number, --File.Sql.39 bug 4440895
236 x_unit_of_measure in varchar2,
237 x_track_as_labor_flag in varchar2,
238 x_raw_cost in out NOCOPY number, --File.Sql.39 bug 4440895
239 x_burdened_cost in out NOCOPY number, --File.Sql.39 bug 4440895
240 x_revenue in out NOCOPY number, --File.Sql.39 bug 4440895
241 x_change_reason_code in varchar2,
242 x_attribute_category in varchar2,
243 x_attribute1 in varchar2,
244 x_attribute2 in varchar2,
245 x_attribute3 in varchar2,
246 x_attribute4 in varchar2,
247 x_attribute5 in varchar2,
248 x_attribute6 in varchar2,
249 x_attribute7 in varchar2,
250 x_attribute8 in varchar2,
251 x_attribute9 in varchar2,
255 x_attribute13 in varchar2,
252 x_attribute10 in varchar2,
253 x_attribute11 in varchar2,
254 x_attribute12 in varchar2,
256 x_attribute14 in varchar2,
257 x_attribute15 in varchar2,
258 -- Bug Fix: 4569365. Removed MRC code.
259 -- x_mrc_flag in varchar2, /* FPB2: MRC */
260 x_pm_product_code in varchar2 default null,
261 x_pm_budget_line_reference in varchar2 default null,
262 x_quantity_source varchar2 default 'M',
263 x_raw_cost_source varchar2 default 'M',
264 x_burdened_cost_source varchar2 default 'M',
265 x_revenue_source varchar2 default 'M',
266 x_resource_assignment_id in out NOCOPY number, --File.Sql.39 bug 4440895
267 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
268 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
269 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
270 );
271
272 procedure summerize_project_totals (x_budget_version_id in number,
273 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
274 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
275 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
276
277 PROCEDURE Verify_Budget_Rules
278 (p_draft_version_id IN NUMBER
279 , p_mark_as_original IN VARCHAR2
280 , p_event IN VARCHAR2
281 , p_project_id IN NUMBER
282 , p_budget_type_code IN VARCHAR2
283 , p_resource_list_id IN NUMBER
284 , p_project_type_class_code IN VARCHAR2
285 , p_created_by IN NUMBER
286 , p_calling_module IN VARCHAR2
287 , p_fin_plan_type_id IN NUMBER DEFAULT NULL
288 , p_version_type IN VARCHAR2 DEFAULT NULL
289 , p_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
290 , p_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
291 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
292 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
293 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
294 );
295
296
297
298
299 PROCEDURE Baseline_Budget
300 (p_draft_version_id IN NUMBER
301 , p_project_id IN NUMBER
302 , p_mark_as_original IN VARCHAR2
303 , p_fck_req_flag IN VARCHAR2 DEFAULT NULL
304 , p_verify_budget_rules IN VARCHAR2 DEFAULT 'N'
305 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
306 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
307 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
308 );
309
310 /****************************************************************
311 How to use API - get_project_task_totals:
312 This API can be used to get the totals at the Project Level
313 or at the task level. If x_task_id is passed as a null value then
314 project level totals are fetched. Otherwise task level totals are
315 fetched. For task level totals, first the task level is determined.
316 If the task level is top or intermediate level , then the amounts
317 are rolled from the child tasks.
318 ******************************************************************/
319 procedure get_project_task_totals(x_budget_version_id in number,
320 x_task_id in number,
321 x_quantity_total in out NOCOPY number, --File.Sql.39 bug 4440895
322 x_raw_cost_total in out NOCOPY number, --File.Sql.39 bug 4440895
323 x_burdened_cost_total in out NOCOPY number, --File.Sql.39 bug 4440895
324 x_revenue_total in out NOCOPY number, --File.Sql.39 bug 4440895
325 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
326 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
327 x_err_stack in out NOCOPY varchar2) ; --File.Sql.39 bug 4440895
328
329
330
331 Procedure set_entry_level_code(x_entry_level_code in varchar2);
332
333 Function get_entry_level_code return varchar2;
334 -- pragma RESTRICT_REFERENCES ( get_entry_level_code, WNDS, WNPS );
335
336
337 Procedure Get_Version_Approved_Code
338 (
339 p_budget_version_id IN NUMBER
340 , x_approved_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
341 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
342 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
343 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
344 );
345
346
347 Procedure Get_Project_Currency_Info
348 (
349 p_project_id IN NUMBER
350 , x_projfunc_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
351 , x_project_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
352 , x_txn_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
353 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
354 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
355 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
356 );
357
358
359 Procedure Get_Approved_FP_Info
360 (
361 p_project_id IN NUMBER
362 , x_ac_plan_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
363 , x_ar_plan_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
364 , x_ac_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
365 , x_ar_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
366 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
367 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
368 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
369 );
370
371 FUNCTION check_baseline_funding( x_project_id IN NUMBER )
372 RETURN NUMBER;
373
374
375 -- R12 MOAC, 19-JUL-05, jwhite
376 -- Added the procedure to set single project/OU context.
377 -- Had to add x_err_code to list to accomodate historical procedure standard
378 -- used by the Budget Approval workflow.
379
380 Procedure Set_Prj_Policy_Context
381 (
382 p_project_id IN NUMBER
383 , x_msg_count OUT NOCOPY NUMBER
384 , x_msg_data OUT NOCOPY VARCHAR2
385 , x_return_status OUT NOCOPY VARCHAR2
386 , x_err_code OUT NOCOPY NUMBER
387 );
388
389
390
391
392 END pa_budget_utils ;
[Home] [Help]