1 PACKAGE BODY pa_client_extn_budget AS
2 /* $Header: PAXBCECB.pls 120.2 2005/08/19 17:08:53 mwasowic ship $ */
3
4 PROCEDURE Calc_Raw_Cost(x_budget_version_id in number,
5 x_project_id in number,
6 x_task_id in number,
7 x_resource_list_member_id in number,
8 x_resource_list_id in number,
9 x_resource_id in number,
10 x_start_date in date,
11 x_end_date in date,
12 x_period_name in varchar2,
13 x_quantity in number,
14 x_raw_cost in out NOCOPY number, --File.Sql.39 bug 4440895
15 x_pm_product_code in varchar2,
16 --x_txn_currency_code IN VARCHAR2 DEFAULT NULL,
17 x_txn_currency_code IN VARCHAR2 ,
18 x_error_code out NOCOPY number, --File.Sql.39 bug 4440895
19 x_error_message out NOCOPY varchar2) --File.Sql.39 bug 4440895
20
21 IS
22 /* start of bug 3736220 */
23 CURSOR get_resource_assignment_id(p_budget_version_id in NUMBER,
24 p_project_id in NUMBER,
25 p_task_id in NUMBER,
26 p_resource_list_member_id in NUMBER)
27 IS
28 SELECT resource_assignment_id FROM pa_resource_assignments
29 WHERE budget_version_id = p_budget_version_id AND
30 project_id = p_project_id AND
31 task_id = p_task_id AND
32 resource_list_member_id = p_resource_list_member_id;
33
34 l_err_code varchar2(30);
35 /* end of bug 3736220 */
36 -- Define your local variables here
37
38 BEGIN
39 -- Initialize the output parameters
40 x_error_code := 0;
41 l_err_code := null; -- Added for bug 3736220
42 -- Enter your business rules to calculate raw cost here
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 -- Add your exception handler here.
47 -- To raise an ORACLE error, assign SQLCODE to x_error_code
48 null;
49 END Calc_Raw_Cost;
50
51 -- =================================================
52
53 PROCEDURE Calc_Burdened_Cost(x_budget_version_id in number,
54 x_project_id in number,
55 x_task_id in number,
56 x_resource_list_member_id in number,
57 x_resource_list_id in number,
58 x_resource_id in number,
59 x_start_date in date,
60 x_end_date in date,
61 x_period_name in varchar2,
62 x_quantity in number,
63 x_raw_cost in number,
64 x_burdened_cost in out NOCOPY number, --File.Sql.39 bug 4440895
65 X_pm_product_code in varchar2,
66 x_txn_currency_code IN VARCHAR2 ,
67 --x_txn_currency_code IN VARCHAR2 DEFAULT NULL,
68 x_error_code out NOCOPY number, --File.Sql.39 bug 4440895
69 x_error_message out NOCOPY varchar2) --File.Sql.39 bug 4440895
70 IS
71 /* start of bug 3736220 */
72 CURSOR get_resource_assignment_id(p_budget_version_id in NUMBER,
73 p_project_id in NUMBER,
74 p_task_id in NUMBER,
75 p_resource_list_member_id in NUMBER)
76 IS
77 SELECT resource_assignment_id FROM pa_resource_assignments
78 WHERE budget_version_id = p_budget_version_id AND
79 project_id = p_project_id AND
80 task_id = p_task_id AND
81 resource_list_member_id = p_resource_list_member_id;
82
83 l_err_code varchar2(30);
84 /* end of bug 3736220 */
85 -- Define your local variables here
86
87 BEGIN
88
89 -- Initialize the output parameters
90
91 x_error_code := 0;
92 l_err_code := null; -- Added for bug 3736220
93
94 -- Add your business rules to calculate burdened cost here
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 -- Add your exception handler here.
99 -- To raise an ORACLE error, assign SQLCODE to x_error_code
100 null;
101 END Calc_Burdened_Cost;
102
103 -- =================================================
104 PROCEDURE Calc_Revenue(x_budget_version_id in number,
105 x_project_id in number,
106 x_task_id in number,
107 x_resource_list_member_id in number,
108 x_resource_list_id in number,
109 x_resource_id in number,
110 x_start_date in date,
111 x_end_date in date,
112 x_period_name in varchar2,
113 x_quantity in number,
114 x_revenue in out NOCOPY number, --File.Sql.39 bug 4440895
115 x_pm_product_code in varchar2,
116 x_txn_currency_code IN VARCHAR2 ,
117 --x_txn_currency_code IN VARCHAR2 DEFAULT NULL,
118 x_error_code out NOCOPY number, --File.Sql.39 bug 4440895
119 x_error_message out NOCOPY varchar2, --File.Sql.39 bug 4440895
120 x_raw_cost IN NUMBER ,
121 x_burdened_cost IN NUMBER )
122 IS
123 /* start of bug 3736220 */
124 CURSOR get_resource_assignment_id(p_budget_version_id in NUMBER,
125 p_project_id in NUMBER,
126 p_task_id in NUMBER,
127 p_resource_list_member_id in NUMBER)
128 IS
129 SELECT resource_assignment_id FROM pa_resource_assignments
130 WHERE budget_version_id = p_budget_version_id AND
131 project_id = p_project_id AND
132 task_id = p_task_id AND
133 resource_list_member_id = p_resource_list_member_id;
134
135 l_err_code varchar2(30);
136 /* end of bug 3736220 */
137 --define your local variables here
138
139 BEGIN
140
141 -- Initialize the output parameters here
142 x_error_code := 0;
143 l_err_code := null; -- Added for bug 3736220
144 -- Add your business rules to calculate revenue here
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 -- Add your exception handler here.
149 -- To raise an ORACLE error, assign SQLCODE to x_error_code
150 null;
151 END Calc_Revenue;
152 -- =================================================
153
154 --Name: Verify_Budget_Rules
155 --Type: Procedure
156 --Description:
157 --
158 --
159 --Called subprograms: none.
160 --
161 --
162 --
163 --History:
164 -- Summer-97 jwhite Created
165 --
166 -- 26-APR-01 jwhite For the Verify_Budget_Rules API,
167 -- added notes and code for global
168 -- G_bgt_intg_flag for GL/PA Budget Integration.
169 --
170 -- 07-AUG-01 jwhite Updated tech doc for Financial Planning. See
171 -- p_budget_type_code notes below.
172 --
173 -- NOTES:
174 --
175 -- IN
176 -- p_workflow_started_by_id - identifies the user that initiated the workflow
177 -- p_event - indicates whether procedure called for
178 -- either a 'SUBMIT' or 'BASELINE'
179 -- event.
180 --
181 -- p_budget_type_code - For r11.5.7 Budgets this code is NOT null.
182 --
183 -- !!! For Financial Planning Plan Types, this IN-parameter is NULL !!!
184 --
185 -- Therefore, if you have special processing for Plan Types,
186 -- you must code the following:
187 --
188 -- IF (p_budget_type_code IS NULL)
189 -- THEN
190 -- Code any financial planning logic.
191 --
192 --
193 -- G_bgt_intg_flag - PA_BUDGET_UTILS.G_Bgt_Intg_Flag
194 -- This package specification global defaults to NULL.
195 -- It may be populated by the Budgets form and other Budget
196 -- APIs for integration budgets. It will NOT be populated
197 -- by Budget and Project Form Copy_Budget functions.
198 --
199 -- The values and meanings for this global are as follows:
200 -- NULL- Budget Integration not enabled
201 -- 'N' - Budget Integration not enabled
202 -- 'G' - GL Budget Integration
203 -- 'C' - CBC Budget Integration
204 --
205 --
206 -- OUT
207 -- p_warnings_only_flag - RETURN 'Y' if ALL triggered edits are warnings. Otherwise,
208 -- if there is at least one hard error, then RETURN 'N'.
209 -- p_err_msg_count - Count of warning and error messages.
210 --
211 -- By using the commented code in the body of this procedure, you may
212 -- add error and warning messages to the message stack.
213 --
214 -- Moreover, error/warning processing in the calling procedure
215 -- will only occur if OUT p_err_msg_count
216 -- parameter is greater than zero.
217 --
218
219 PROCEDURE Verify_Budget_Rules
220 (p_draft_version_id IN NUMBER
221 , p_mark_as_original IN VARCHAR2
222 , p_event IN VARCHAR2
223 , p_project_id IN NUMBER
224 , p_budget_type_code IN VARCHAR2
225 , p_resource_list_id IN NUMBER
226 , p_project_type_class_code IN VARCHAR2
227 , p_created_by IN NUMBER
228 , p_calling_module IN VARCHAR2
229 , p_fin_plan_type_id IN NUMBER
230 --, p_fin_plan_type_id IN NUMBER DEFAULT NULL
231 , p_version_type IN VARCHAR2
232 --, p_version_type IN VARCHAR2 DEFAULT NULL
233 , p_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
234 , p_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
235 , p_error_code OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
236 , p_error_message OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
237 )
238
239 IS
240 --Define Your Local Variables Here
241
242 -- Global Semaphore for Non-Project Budget Integration
243 l_bgt_intg_flag VARCHAR2(1) :=NULL;
244
245
246 BEGIN
247 --dbms_output.put_line('Client Extn VERIFY_BUDGET_RULES - Inside');
248
249 --
250 -- Initialize Local Variable for Non-Project Budget Integration Global.
251 --
252 l_bgt_intg_flag := PA_BUDGET_UTILS.G_Bgt_Intg_Flag;
253
254 --
255 -- Initialize OUT-parameters Here
256 --
257 p_warnings_only_flag := 'Y';
258 p_err_msg_count := 0;
259 p_error_code := 0;
260 --
261 -- Put The Rules That You Want To Check For Here
262 --
263 -- Remember: p_event - SUBMIT for submission validation,
264 -- BASELINE for baseline validation.
265 --
266
267 --
268 -- NOTIFICATION Error/Warning Handling --------------------------
269 --
270 -- For error and warning messages, you must increment the p_err_msg_count
271 -- OUT-parameter before passing control to the calling procedure:
272 --
273 -- p_err_msg_count := FND_MSG_PUB.Count_Msg;
274 --
275 --
276 -- For a hard error, one that you want to force the calling procedure
277 -- to invoke a 'False' or 'Failure' transition:
278 --
279 -- p_warnings_only_flag := 'N';
280 --
281 --
282 -- To display an error or warning message in the workflow notification
283 -- or the Budgets form , you
284 -- must call the following:
285 --
286 -- PA_UTILS.Add_Message
287 --
288 -- For example, a typical call might look like the following:
289 --
290 -- PA_UTILS.Add_Message
291 -- ( p_app_short_name => 'PA'
292 -- , p_msg_name => 'PA_NO_BUDGET_RULES_ATTR'
293 -- );
294 -- You can also define your own tokens for the messages .You can supply
295 -- upto 5 tokens for the PA_UTILS.Add_Message procedure
296 -- ---------------------------------------------------------------------------------------
297
298 --
299 -- Make sure to update the OUT variable for the
300 -- message count
301 --
302
303 p_err_msg_count := FND_MSG_PUB.Count_Msg;
304
305
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 -- Add your exception handler here.
310 -- To raise an ORACLE error, assign SQLCODE to p_error_code
311 NULL;
312 END Verify_Budget_Rules;
313
314
315 -- =================================================
316 /*============================================================================
317 Name: Get_Custom_Layout_Code
318 Type: Procedure
319 Description: This API has been created for the Financial Planning
320 WebADI upload functionality. This is a Client Extension
321 provided to the customers to modify the layout code that
322 is selected by Default for the budget version. This can
323 be used by the users for viewing their Custom Layouts.
324 The details of the parameters are provided below.
325
326 Calling subprograms: pa_fp_webadi_utils.get_metadata_info (Procedure used
327 to return the layout code based on the budget version id).
328
329 IN:
330
331 p_budget_version_id - Budget Version ID for which the layout code needs to be
332 determined.
333 p_layout_code_in - Layout code that is determined by the calling procedure
334 for the above budget version id.
335 OUT
336
337 x_layout_code_out - The customized layout code that the user woule like to
338 view for the budget version id.
339 x_return_status - The returning status to be sent to the calling API.
340 x_msg_count - Count of warning and error messages if any.
341 x_msg_data - Error message data to be sent to the calling API.
342
343 NOTE:
344 By default, this Client Extension API will return the same input layout code
345 to the calling API. The user can modify the code of this API to return the
346 code of the Customized Layout.
347
348 By using the commented code in the body of this procedure, you may
349 add error and warning messages to the message stack.
350
351 Moreover, error/warning processing in the calling procedure
352 will only occur if OUT parameter x_return_status is not FND_API.G_RET_STS_SUCCESS
353 or if we explicitly "RAISE" the exception in the exception portion of the api.
354 ===============================================================================*/
355
356 PROCEDURE Get_Custom_Layout_Code
357 (
358 p_budget_version_id IN NUMBER
359 , p_layout_code_in IN VARCHAR2
360 , x_layout_code_out OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
361 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
362 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
363 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
364 )
365
366 IS
367 /* Use the below variable to check if the PA debug mode is ON before printing
368 the debug messages. */
369
370 L_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
371
372 /* Define any other local variables, here. */
373
374 BEGIN
375
376 /* Initialize out parameters. */
377 x_msg_count := 0;
378 x_return_status := FND_API.G_RET_STS_SUCCESS;
379 pa_debug.set_err_stack('PA_CLIENT_EXTN_BUDGET.Get_Custom_Layout_Code');
380
381 /* Display Errors:
382 To display an error or warning message use PA_UTILS.Add_Message.
383 For example, a typical call might look like the following:
384
385 -- PA_UTILS.Add_Message
386 -- ( p_app_short_name => 'PA'
387 -- ,p_msg_name => 'PA_INVALID_LAYOUT'
388 -- );
389 -- x_return_status := FND_API.G_RET_STS_ERROR;
390 -- x_msg_count := FND_MSG_PUB.count_msg;
391
392 You can define your own message (PA_INVALID_LAYOUT is just an example)
393 and for the messages . Upto 5 tokens can be passed to the PA_UTILS.Add_Message procedure
394 */
395
396 /* To print debug messages, the following statements can be modified and
397 used.
398
399 -- IF l_pa_debug_mode = 'Y' THEN
400 -- pa_debug.g_err_stage:= 'The Layout is invalid.';
401 -- pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
402 -- END IF;
403
404 */
405
406 /* By default the out parameter x_layout_code_out is initialised to
407 in parameter p_layout_code_in.
408 The user can put in their processing logic and arrive at the out
409 parameter x_layout_code_out.
410 */
411
412 x_layout_code_out := p_layout_code_in;
413
414 /* Reset the error stack. */
415 pa_debug.reset_err_stack;
416
417
418 EXCEPTION
419 WHEN OTHERS THEN
420 NULL;
421 -- Following sample exception handling can be used to handle the exception.
422 --
423 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424 -- x_msg_count := 1;
425 -- x_msg_data := SQLERRM;
426 -- FND_MSG_PUB.add_exc_msg
427 -- ( p_pkg_name => 'PA_CLIENT_EXTN_BUDGET'
428 -- ,p_procedure_name => 'Get_Custom_Layout_Code'
429 -- ,p_error_text => sqlerrm);
430 -- IF l_pa_debug_mode = 'Y' THEN
431 -- pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
432 -- pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
433 -- END IF;
434 -- pa_debug.reset_err_stack;
435 -- RAISE;
436 END Get_Custom_Layout_Code;
437
438 -- created tpalaniv For bug 3736220
439 -- This API will stamp the customized errors defined by the Customer within this client extension
440 -- in interface table, so that these client extension error messages will be displayed in Excel,
441 -- when some validation done in client extension fails.
442 -- Also Please Note that,
443 -- 1. p_error_code is equivalent to lookup_code in pa_lookups table.
444 -- 2. Only if this lookup_code is present in pa_lookups table, will the customized client extension error
445 -- message will be displayed
446
447
448 PROCEDURE Stamp_Client_Extn_Errors
449 ( p_resource_assignment_id IN NUMBER
450 , p_error_code IN VARCHAR2
451 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
452 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
453 , x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
454 IS
455
456 PRAGMA AUTONOMOUS_TRANSACTION;
457 BEGIN
458
459 x_msg_count := 0;
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461
462 UPDATE PA_FP_WEBADI_XFACE_TMP
463 SET val_error_code = p_error_code,
464 val_error_flag = 'Y'
465 WHERE resource_assignment_id = p_resource_assignment_id
466 AND
467 NOT EXISTS
468 (SELECT 'Y'
469 FROM PA_FP_WEBADI_XFACE_TMP tmpchk
470 WHERE tmpchk.val_error_code IS NOT NULL
471 AND tmpchk.val_error_flag = 'Y'
472 AND resource_assignment_id = p_resource_assignment_id);
473
474 COMMIT;
475 RETURN;
476 EXCEPTION
477 WHEN OTHERS THEN
478 NULL;
479 -- Following sample exception handling can be used to handle the exception.
480 --
481 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482 -- x_msg_count := 1;
483 -- x_msg_data := SQLERRM;
484 -- FND_MSG_PUB.add_exc_msg
485 -- ( p_pkg_name => 'PA_CLIENT_EXTN_BUDGET'
486 -- ,p_procedure_name => 'Get_Custom_Layout_Code'
487 -- ,p_error_text => sqlerrm);
488 -- RAISE;
489 END Stamp_Client_Extn_Errors;
490
491 END pa_client_extn_budget;
[Home] [Help]