[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PA_CLIENT_EXTN_BUDGET_WF
Source
1 PACKAGE BODY pa_client_extn_budget_wf AS
2 /* $Header: PAWFBCEB.pls 120.3.12010000.3 2008/09/11 11:13:09 rballamu ship $ */
3
4 -- -------------------------------------------------------------------------------------
5 -- GLOBALS
6 -- -------------------------------------------------------------------------------------
7
8 G_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
9
10 -- -------------------------------------------------------------------------------------
11 -- PROCEDURES
12 -- -------------------------------------------------------------------------------------
13
14 --
15 --Name: BUDGET_WF_IS_USED
16 --Type: Procedure
17 --Description: This procedure must return a "T" or "F" depending on whether a workflow
18 -- should be started for this particular budget.
19 --
20 --
21 --Called Subprograms: none.
22 --
23 --Notes:
24 -- This client extension is called directly from the Budgets form and the public
25 -- Baseline_Budget API (actually, from a wrapper with the same name).
26 --
27 -- This extension is NOT called form workflow!
28 --
29 -- Error messages in the form and public API call the 'PA_WF_CLIENT_EXTN'
30 -- error code. Two tokens are passed to the error message: the name of this
31 -- client extension and the error code.
32 --
33 --
34 --
35 --
36 --History:
37 -- 24-FEB-1997 L. de Werker - Created
38 -- 24-JUN-97 jwhite - Updated to latest specs.
39 -- 29-JUL-97 jwhite - Updated to specs directed by jlowell.
40 -- 12-AUG -97 jwhite - Ditto; added check for enable flags
41 -- from pa_project_types and
42 -- pa_budget_types.
43 -- 21-OCT-87 jwhite - Updated as per Kevin Hudson's code review
44 --
45 -- 08-AUG-02 jwhite - Adapted default logic to also support the new FP model.
46 --
47 --
48 -- IN Parameters
49 -- p_project_id - Unique identifier for the project of the budget for which approval
50 -- is requested.
51 -- p_budget_type_code - Unique identifier for budget submitted for approval
52 -- p_pm_product_code - The PM vendor's product code stored in pa_budget_versions.
53 --
54 -- OUT Parameters
55 -- p_result - 'T' or 'F' (True/False)
56 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
57 -- x > 0, Business Rule Violated.
58 -- p_err_stage - Standard error message
59 -- p_err_stack - Not used.
60 --
61
62 PROCEDURE BUDGET_WF_IS_USED
63 (p_draft_version_id IN NUMBER
64 , p_project_id IN NUMBER
65 , p_budget_type_code IN VARCHAR2
66 , p_pm_product_code IN VARCHAR2
67 , p_fin_plan_type_id IN NUMBER default NULL
68 , p_version_type IN VARCHAR2 default NULL
69 , p_result IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
70 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
71 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
72 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
73 )
74
75 IS
76 /*
77 You can use this procedure to add/modify the conditions to enable
78 workflow for budget status changes. By default,Oracle Projects enables
79 and launches workflow based on the Budget Type and Project type setup.
80 You can choose to override these conditions with your own conditions
81
82 */
83
84 -- Define your local variables and cursors here
85
86 CURSOR l_project_types_csr (p_project_id NUMBER)
87 IS
88 SELECT pt.enable_budget_wf_flag
89 FROM pa_projects p, pa_project_types pt
90 WHERE p.project_id = p_project_id
91 AND p.project_type = pt.project_type;
92
93 CURSOR l_budget_types_csr (p_budget_type_code VARCHAR2)
94 IS
95 SELECT b.enable_wf_flag
96 FROM pa_budget_types b
97 WHERE b.budget_type_code = p_budget_type_code;
98
99 CURSOR l_plan_types_csr (p_fin_plan_type_id NUMBER)
100 IS
101 SELECT pl.enable_wf_flag
102 FROM pa_fin_plan_types_b pl
103 WHERE pl.fin_plan_type_id = p_fin_plan_type_id;
104
105
106
107 l_enable_budget_wf_flag pa_project_types.enable_budget_wf_flag%TYPE := 'N';
108 l_enable_wf_flag pa_budget_types.enable_wf_flag%TYPE := 'N';
109
110
111 BEGIN
112
113
114 -- Initialize The Output Parameters
115
116 p_err_code := 0;
117 p_result := 'F';
118
119 -- Enter Your Business Rules Here.Or, Use The
120 -- Provided Default.
121
122 OPEN l_project_types_csr (p_project_id);
123 FETCH l_project_types_csr INTO l_enable_budget_wf_flag;
124 CLOSE l_project_types_csr ;
125
126
127 IF (p_budget_type_code IS NULL)
128 THEN
129 -- FP model
130 OPEN l_plan_types_csr (p_fin_plan_type_id);
131 FETCH l_plan_types_csr INTO l_enable_wf_flag;
132 CLOSE l_plan_types_csr;
133
134 ELSE
135 -- r11.5.7 Budgets Model
136 OPEN l_budget_types_csr (p_budget_type_code);
137 FETCH l_budget_types_csr INTO l_enable_wf_flag;
138 CLOSE l_budget_types_csr;
139
140 END IF;
141
142
143 IF (
144 ( l_enable_budget_wf_flag = 'Y')
145 AND (l_enable_wf_flag = 'Y')
146 )
147 THEN
148 p_result := 'T';
149 ELSE
150 p_result := 'F';
151 END IF;
152
153 --dbms_output.put_line('BUDGET_WF_USED - RESULT'||p_result);
154
155
156 EXCEPTION
157
158 WHEN OTHERS THEN
159 -- Add your exception handler here.
160 -- To raise an ORACLE error, assign SQLCODE to p_error_code
161 p_err_code := SQLCODE;
162 RAISE;
163
164 END BUDGET_WF_IS_USED;
165 -- ===================================================
166 --
167 --Name: START_BUDGET_WF
168 --Type: Procedure
169 --Description: This procedure is used to start the Budget Approval workflow.
170 --
171 --Notes:
172 --
173 -- Calling Objects ------------------------------
174 --
175 -- This procedure is called from the PA_BUDGET_WF.Start_Budget_WF. In turn,
176 -- the PA_BUDGET_WF.Start_Budget_WF called from the following objects:
177 -- 1) Budgets form
178 -- 2) AMG Baseline_Budget API
179 -- 3) Budget Integration Workflow
180 --
181 --
182 -- Error Messaging -----------------------------
183 --
184 -- Error messages in the form and public API call the 'PA_WF_CLIENT_EXTN'
185 -- error code. Two tokens are passed to the error message: the name of this
186 -- client extension and the error code.
187 --
188 --
189 -- Financial Planning ---------------------------
190 --
191 -- This procedure has been modified to support both the r11.5.7 Budgets Model
192 -- and the Financial Planning Model:
193 --
194 -- CRITICAL NOTES-1
195 -- 1) This procedure now drives off of the p_draft_version_id IN-parameter.
196 -- The default logic ignores the p_budget_type_code IN-parameter.
197 --
198 -- 2) The p_draf_version_id IN-parameter is now passed to the
199 -- workflow. The workflow now drives off
200 -- of the p_draft_version_id IN-parameter.
201 --
202 -- 3) Although p_fin_plan_type_id and p_version_type can be passed as
203 -- IN-parameters, the default logic ignores them.
204 --
205 -- 4) The FP parameters that are loaded into the workflow are populated
206 -- from the draft_budget_version record.
207 --
208 -- 5) Conditional logic has been added for the r11.5.7 Budget and FP
209 -- model processing.
210 --
211 --
212 --
213 --
214 --Called subprograms: none.
215 --
216 --
217 --
218 --History:
219 -- 28-FEB-97 L. de Werker - Created
220 -- 26-JUN-97 jwhite - Updated to lastest specs
221 -- 29-JUL-97 jwhite - Updated to specs as directed by jlowell
222 -- 08-SEP-97 jwhite - Added item_type and item_key
223 -- parameters and code as part of
224 -- changes to encapsulate procedure
225 -- in wrapper.
226 -- 21-OCT-87 jwhite - Updated as per Kevin Hudson's code review
227 -- 04-NOV-97 jwhite - Added workflow-started-date
228 -- to Start_Budget_WF procedure.
229 -- 25-NOV-97 jwhite - Replaced call to set_global_info
230 -- with FND_GLOBAL.Apps_Initialize.
231 -- Did not call Set_Global_Attr because
232 -- the WF does NOT exist yet.
233 --
234 -- 03-MAY-01 jwhite - As per the Non-Project Integration
235 -- development effort, added the following
236 -- parameters and attributes to Start_Budget_WF:
237 -- 1. p_fck_req_flag
238 -- 2. p_bgt_intg_flag
239 --
240 -- 08-AUG-02 jwhite - Adapted default logic to also support the new FP model.
241 -- See desription above for modifications.
242 --
243 -- 14-OCT-02 jwhite - As part of supporting both r11.5.7 Budgets
244 -- and FP model in the notifications, modified code to
245 -- conditional populate budget/FP name and FP planning
246 -- elements for display in notifications.
247 --
248 -- Also, noticed that the BEM was being populated
249 -- with the CODE, NOT the name. Fixed this. Added
250 -- a cursor and a budget_entry_method_code attribute
251 -- to procedure and workflow.
252 --
253 -- 01-NOV-02 jwhite - Bug 2651400
254 -- Fixed typo for CLOSE cursor l_fin_attr_csr
255 --
256 --
257 --
258 -- IN Parameters
259 -- p_project_id - Unique identifier for the project of the budget for which approval
260 -- is requested.
261 -- p_budget_type_code - Unique identifier for budget submitted for approval
262 -- p_mark_as_original - Yes, mark budget as original; N, do not mark. Defaults to 'N'.
263 -- p_fck_req_flag - Null or N, then funds check processing is not required. Y, if required.
264 -- p_bgt_intg_flag - Null or N, then no budgetary controls. Y, if budgetary controls.
265 --
266 -- OUT Parameters
267 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
268 -- x > 0, Business Rule Violated.
269 -- p_err_stage - Standard error message
270 -- p_err_stack - Not used.
271 --
272
273
274 PROCEDURE START_BUDGET_WF
275 (p_draft_version_id IN NUMBER
276 , p_project_id IN NUMBER
277 , p_budget_type_code IN VARCHAR2
278 , p_mark_as_original IN VARCHAR2
279 , p_fck_req_flag IN VARCHAR2 DEFAULT NULL
280 , p_bgt_intg_flag IN VARCHAR2 DEFAULT NULL
281 , p_fin_plan_type_id IN NUMBER DEFAULT NULL
282 , p_version_type IN VARCHAR2 DEFAULT NULL
283 , p_item_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
284 , p_item_key OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
285 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
286 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
287 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
288 )
289
290 IS
291
292 --
293 -- CAUTION:
294 --
295 -- This is a working client extension. It is designed to start the
296 -- PABUDWF Budget Approval workflow. If you make changes to this
297 -- procedure, you must properly populate the OUT-parameters,
298 -- particularly the p_item_type and p_item_key OUT-parameters.
299 --
300 -- Also, if you want to use a different item type or process ,you must
301 -- change the value for the variable ItemType and the
302 -- change the value for the parameter "process" in the
303 -- call to wf_engine.Create_Process.
304 -- Make sure that you have a thorough understanding
305 -- of the Oracle Workflow product and how to use PL/SQL with Workflow.
306 --
307
308
309
310
311 CURSOR l_project_csr ( p_project_id NUMBER )
312 IS
313 SELECT pm_project_reference
314 , segment1
315 , name
316 , description
317 , project_type
318 , pm_product_code
319 , carrying_out_organization_id
320 , template_flag --Bug 6691634
321 FROM pa_projects
322 WHERE project_id = p_project_id;
323 --
324 CURSOR l_organization_csr ( p_carrying_out_organization_id NUMBER )
325 IS
326 SELECT name
327 FROM hr_organization_units
328 WHERE organization_id = p_carrying_out_organization_id;
329 --
330 CURSOR l_project_type_class( p_project_type VARCHAR2)
331 IS
332 SELECT project_type_class_code
333 FROM pa_project_types
334 WHERE project_type = p_project_type;
335 --
336 CURSOR l_starter_user_name_csr( p_starter_user_id NUMBER )
337 IS
338 SELECT user_name
339 FROM fnd_user
340 WHERE user_id = p_starter_user_id;
341 --
342 CURSOR l_starter_full_name_csr(p_starter_user_id NUMBER )
343 IS
344 SELECT e.first_name||' '||e.last_name
345 FROM fnd_user f, per_all_people_f e
346 WHERE f.user_id = p_starter_user_id
347 AND f.employee_id = e.person_id
348 AND e.effective_start_date = (SELECT MIN(pap.effective_start_date) --Bug 5102146.
349 FROM per_all_people_f pap
350 WHERE pap.person_id = e.person_id
351 AND pap.effective_end_date >= TRUNC(SYSDATE));
352 --
353 CURSOR l_budget_csr( p_draft_version_id NUMBER )
354 IS
355 SELECT pm_budget_reference
356 ,description
357 ,change_reason_code
358 ,budget_entry_method_code
359 ,pm_product_code
360 ,labor_quantity
361 ,raw_cost
362 ,burdened_cost
363 ,revenue
364 ,resource_list_id
365 ,version_name
366 ,budget_type_code
367 ,fin_plan_type_id
368 ,version_type
369 FROM pa_budget_versions
370 WHERE budget_version_id = p_draft_version_id
371 AND budget_status_code = 'S';
372 --
373 CURSOR l_resource_list_csr( p_resource_list_id NUMBER )
374 IS
375 SELECT name
376 , description
377 FROM pa_resource_lists
378 WHERE resource_list_id = p_resource_list_id;
379 --
380 CURSOR l_budget_type_csr( p_budget_type_code VARCHAR2 )
381 IS
382 SELECT budget_type
383 FROM pa_budget_types
384 WHERE budget_type_code = p_budget_type_code;
385 --
386 CURSOR l_wf_started_date_csr
387 IS
388 SELECT sysdate
389 FROM dual;
390 --
391 CURSOR l_fin_plan_name_csr (l_fin_plan_type_id NUMBER)
392 IS
393 SELECT name, plan_class_code --Bug 6691634
394 FROM pa_fin_plan_types_vl fpt
398 CURSOR l_fin_attr_csr (p_draft_version_id NUMBER, l_version_type VARCHAR2)
395 WHERE fpt.fin_plan_type_id = l_fin_plan_type_id;
396 --AND fpt.LANGUAGE = USERENV('LANG');Bug 6691634
397 --
399 IS
400 SELECT l1.meaning
401 , l2.meaning
402 FROM pa_proj_fp_options fo
403 , pa_lookups l1
404 , pa_lookups l2
405 WHERE fo.fin_plan_version_id = p_draft_version_id
406 AND l1.lookup_code = decode(l_version_type, 'COST', fo.cost_fin_plan_level_code
407 ,'REVENUE', fo.revenue_fin_plan_level_code
408 ,'ALL', fo.all_fin_plan_level_code, NULL)
409 AND l1.lookup_type = 'BUDGET ENTRY LEVEL'
410 AND l2.lookup_code = decode(l_version_type, 'COST', fo.cost_time_phased_code
411 ,'REVENUE', fo.revenue_time_phased_code
412 ,'ALL', fo.all_time_phased_code, NULL)
413 AND l2.lookup_type = 'BUDGET TIME PHASED TYPE';
414 --
415 CURSOR l_bem_csr( l_budget_entry_method_code VARCHAR2 )
416 IS
417 SELECT budget_entry_method
418 FROM pa_budget_entry_methods m
419 WHERE m.budget_entry_method_code = l_budget_entry_method_code;
420
421
422
423 ItemType varchar2(30) := 'PABUDWF'; --<----Identifies the workflow process!!!
424 ItemKey varchar2(30);
425
426 l_pm_project_reference pa_projects.pm_project_reference%TYPE;
427 l_pa_project_number pa_projects.segment1%TYPE;
428 l_project_name pa_projects.name%TYPE;
429 l_description pa_projects.description%TYPE;
430 l_project_type pa_projects.project_type%TYPE;
431 l_pm_project_product_code pa_projects.pm_product_code%TYPE;
432 l_carrying_out_org_id NUMBER;
433 l_carrying_out_org_name hr_organization_units.name%TYPE;
434 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
435
436 l_pm_budget_reference pa_budget_versions.pm_budget_reference%TYPE;
437 l_budget_description pa_budget_versions.description%TYPE;
438 l_budget_change_reason_code pa_budget_versions.change_reason_code%TYPE;
439 l_budget_entry_method_code pa_budget_versions.budget_entry_method_code%TYPE;
440 l_budget_entry_method pa_budget_entry_methods.budget_entry_method%TYPE;
441 l_pm_budget_product_code pa_budget_versions.pm_product_code%TYPE;
442 l_mark_as_original pa_budget_versions.original_flag%TYPE;
443 l_version_name pa_budget_versions.version_name%TYPE;
444 l_budget_type_code pa_budget_versions.budget_type_code%TYPE;
445
446 l_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE;
447 l_version_type pa_budget_versions.version_type%TYPE;
448 l_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE;
449 l_fin_plan_level pa_lookups.meaning%TYPE;
450 l_fin_plan_time_phase pa_lookups.meaning%TYPE;
451
452
453 l_total_labor_hours NUMBER;
454 l_total_raw_cost NUMBER;
455 l_total_burdened_cost NUMBER;
456 l_total_revenue NUMBER;
457 l_resource_list_id NUMBER;
458 l_resource_list_name pa_resource_lists.name%TYPE;
459 l_resource_list_description pa_resource_lists.description%TYPE;
460 l_budget_type pa_fin_plan_types_tl.name%TYPE; --Bug 6974760 pa_budget_types.budget_type%TYPE;
461 l_wf_started_date DATE;
462
463 l_workflow_started_by_id NUMBER;
464 l_user_name VARCHAR2(240);
465 l_full_name VARCHAR2(400);/*UTF8-from varchar(240) to (400)*/
466 l_resp_id NUMBER;
467 l_row_found VARCHAR2(1);
468
469 l_api_version_number NUMBER := G_api_version_number ;
470 l_msg_count NUMBER;
471 l_msg_data VARCHAR(2000);
472 l_return_status VARCHAR2(1) := NULL;
473 l_data VARCHAR2(2000);
474 l_msg_index_out NUMBER;
475 l_err_code NUMBER := 0;
476 l_err_stage VARCHAR2(100);
477 l_err_stack VARCHAR2(100);
478
479 -- Start Changes for bug 6691634
480 l_url VARCHAR2(2000);
481 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
482 l_template_flag VARCHAR2(1);
483 -- End Changes for bug 6691634
484 --
485 --
486 BEGIN
487
488 -- Standard BEGIN of API savepoint
489
490 SAVEPOINT START_BUDGET_WF_pvt;
491
492 -- Set API Return Status To Success for Public API and Form Error Processing
493
494 p_err_code := 0;
495
496
497 BEGIN
498
499 --
500 -- Initialize FND Globals for Starting Approve Budget Workflow --------------
501 --
502 -- Please note that these globals will be populated from the calling
503 -- module (AMG procedure, Budgets form or Budget Integration Workflow).
504
505
506 --dbms_output.put_line('Item Key(s/b null): '||itemkey);
507
508 l_workflow_started_by_id := FND_GLOBAL.user_id;
509
510 OPEN l_starter_user_name_csr( l_workflow_started_by_id );
511 FETCH l_starter_user_name_csr INTO l_user_name;
512 CLOSE l_starter_user_name_csr;
513
514 OPEN l_starter_full_name_csr( l_workflow_started_by_id );
515 FETCH l_starter_full_name_csr INTO l_full_name;
516 CLOSE l_starter_full_name_csr;
517
518 l_resp_id := FND_GLOBAL.resp_id;
519
520 -- Based on the Responsibility, Intialize the Application
521 -- Cannot call Set_Global_Attr here because the WF does NOT
525 , resp_id => l_resp_id
522 -- Exist yet.
523 FND_GLOBAL.Apps_Initialize
524 (user_id => l_workflow_started_by_id
526 , resp_appl_id => FND_GLOBAL.resp_appl_id
527 );
528
529
530
531 --
532 -- Populate Workflow IN-Parameters ----------------------------------------------
533 --
534
535 -- Mark-As-Original Flag Set From IN-Parameter
536 l_mark_as_original := p_mark_as_original;
537
538
539 OPEN l_project_csr(p_project_id);
540 FETCH l_project_csr INTO l_pm_project_reference
541 ,l_pa_project_number
542 ,l_project_name
543 ,l_description
544 ,l_project_type
545 ,l_pm_project_product_code
546 ,l_carrying_out_org_id
547 ,l_template_flag; --Bug 6691634
548 CLOSE l_project_csr;
549
550
551 OPEN l_organization_csr( l_carrying_out_org_id );
552 FETCH l_organization_csr INTO l_carrying_out_org_name;
553 CLOSE l_organization_csr;
554
555 OPEN l_project_type_class( l_project_type );
556 FETCH l_project_type_class INTO l_project_type_class_code;
557 CLOSE l_project_type_class;
558
559 OPEN l_budget_csr( p_draft_version_id );
560 FETCH l_budget_csr INTO l_pm_budget_reference
561 ,l_budget_description
562 ,l_budget_change_reason_code
563 ,l_budget_entry_method_code
564 ,l_pm_budget_product_code
565 ,l_total_labor_hours
566 ,l_total_raw_cost
567 ,l_total_burdened_cost
568 ,l_total_revenue
569 ,l_resource_list_id
570 ,l_version_name
571 ,l_budget_type_code
572 ,l_fin_plan_type_id
573 ,l_version_type;
574
575 CLOSE l_budget_csr;
576
577
578 -- Conditional Processing for r11.5.7/FP Models -------------
579 IF (l_fin_plan_type_id IS NULL)
580 THEN
581 -- R11.5.7 Model ----------------------------
582
583
584 -- Not Applicable
585 l_fin_plan_type_name := NULL;
586 l_fin_plan_level := NULL;
587 l_fin_plan_time_phase := NULL;
588
589
590 -- Get Budget Type Name
591 OPEN l_budget_type_csr( p_budget_type_code );
592 FETCH l_budget_type_csr INTO l_budget_type;
593 CLOSE l_budget_type_csr;
594
595 -- Get Budget Entry Method Name
596 OPEN l_BEM_csr( l_budget_entry_method_code );
597 FETCH l_BEM_csr INTO l_budget_entry_method;
598 CLOSE l_BEM_csr;
599
600
601
602
603 ELSE
604 -- FP Model ---------------------------------
605
606
607
608 OPEN l_fin_plan_name_csr (l_fin_plan_type_id);
609 FETCH l_fin_plan_name_csr INTO l_fin_plan_type_name ,l_plan_class_code; -- Bug 6691634
610 CLOSE l_fin_plan_name_csr;
611
612 OPEN l_fin_attr_csr (p_draft_version_id, l_version_type);
613 FETCH l_fin_attr_csr INTO l_fin_plan_level, l_fin_plan_time_phase;
614 CLOSE l_fin_attr_csr;
615
616
617 -- Not Applicable to FP Model, but ...
618
619 -- Used to Display Plan Type Name on PA Default Notifications !!!
620 l_budget_type := l_fin_plan_type_name;
621
622
623 -- Displayed as NULL on Notification
624 l_budget_entry_method_code := NULL; -- BEM Code
625 l_budget_entry_method := NULL; -- BEM Name
626
627
628 END IF; -- l_fin_plan_type_id IS NULL
629
630 -- ----------------------------------------------------------
631
632 OPEN l_resource_list_csr( l_resource_list_id );
633 FETCH l_resource_list_csr INTO l_resource_list_name
634 ,l_resource_list_description;
635 CLOSE l_resource_list_csr;
636
637 OPEN l_wf_started_date_csr;
638 FETCH l_wf_started_date_csr INTO l_wf_started_date;
639 CLOSE l_wf_started_date_csr;
640
641
642
643 SELECT pa_workflow_itemkey_s.nextval
644 INTO itemkey
645 from dual;
646
647 --dbms_output.put_line('Item Key!: '||itemkey);
648
649 EXCEPTION
650
651 WHEN FND_API.G_EXC_ERROR
652 THEN
653 ROLLBACK TO START_BUDGET_WF_pvt;
654 RAISE;
655
656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
657 THEN
658 p_err_code := SQLCODE;
659 ROLLBACK TO START_BUDGET_WF_pvt;
660 RAISE;
661
662 WHEN OTHERS THEN
663 p_err_code := SQLCODE;
664 ROLLBACK TO START_BUDGET_WF_pvt;
665 RAISE;
666
667
668 END;
669
670 BEGIN
671 -- ------------------------------------------------------------------------------------
672 -- INSTANTIATE BUDGET WORKFLOW
673 -- ------------------------------------------------------------------------------------
674 -- NOTE:
675 -- The process name passed here is the root process for the
676 -- 'PA Budget Approval Workflow'.
680 wf_engine.CreateProcess( ItemType => ItemType,
677 -- ------------------------------------------------------------------------------------
678 --dbms_output.put_line('Call for CreateProcess');
679
681 ItemKey => ItemKey,
682 process => 'PRO_BASELINE_BUDGET' );
683
684 --dbms_output.put_line('SetitemAttributes');
685
686
687 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'PROJECT_ID',
690 avalue => p_project_id);
691 --
692 wf_engine.SetItemAttrText ( itemtype => itemtype,
693 itemkey => itemkey,
694 aname => 'PM_PROJECT_REFERENCE',
695 avalue => l_pm_project_reference );
696
697 wf_engine.SetItemAttrText ( itemtype => itemtype,
698 itemkey => itemkey,
699 aname => 'PA_PROJECT_NUMBER',
700 avalue => l_pa_project_number );
701
702 wf_engine.SetItemAttrText ( itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'PROJECT_NAME',
705 avalue => l_project_name );
706
707 wf_engine.SetItemAttrText ( itemtype => itemtype,
708 itemkey => itemkey,
709 aname => 'PROJECT_DESCRIPTION',
710 avalue => l_description );
711
712 wf_engine.SetItemAttrText ( itemtype => itemtype,
713 itemkey => itemkey,
714 aname => 'PROJECT_TYPE',
715 avalue => l_project_type );
716
717 wf_engine.SetItemAttrText ( itemtype => itemtype,
718 itemkey => itemkey,
719 aname => 'PM_PROJECT_PRODUCT_CODE',
720 avalue => l_pm_project_product_code );
721
722 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
723 itemkey => itemkey,
724 aname => 'CARRYING_OUT_ORG_ID',
725 avalue => l_carrying_out_org_id);
726
727 wf_engine.SetItemAttrText ( itemtype => itemtype,
728 itemkey => itemkey,
729 aname => 'CARRYING_OUT_ORG_NAME',
730 avalue => l_carrying_out_org_name);
731
732 wf_engine.SetItemAttrText ( itemtype => itemtype,
733 itemkey => itemkey,
734 aname => 'PROJECT_TYPE_CLASS_CODE',
735 avalue => l_project_type_class_code);
736
737 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
738 itemkey => itemkey,
739 aname => 'WORKFLOW_STARTED_BY_ID',
740 avalue => l_workflow_started_by_id);
741
742 wf_engine.SetItemAttrText ( itemtype => itemtype,
743 itemkey => itemkey,
744 aname => 'WORKFLOW_STARTED_BY_NAME',
745 avalue => l_user_name);
746
747 wf_engine.SetItemAttrText ( itemtype => itemtype,
748 itemkey => itemkey,
749 aname => 'WORKFLOW_STARTED_BY_FULL_NAME',
750 avalue => l_full_name);
751
752
753 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
754 itemkey => itemkey,
755 aname => 'RESPONSIBILITY_ID',
756 avalue => l_resp_id);
757
758 wf_engine.SetItemAttrText ( itemtype => itemtype,
759 itemkey => itemkey,
760 aname => 'BUDGET_TYPE_CODE',
761 avalue => l_budget_type_code);
762
763 wf_engine.SetItemAttrText ( itemtype => itemtype,
764 itemkey => itemkey,
765 aname => 'BUDGET_TYPE',
766 avalue => l_budget_type);
767
768 wf_engine.SetItemAttrText ( itemtype => itemtype,
769 itemkey => itemkey,
770 aname => 'PM_BUDGET_REFERENCE',
771 avalue => l_pm_budget_reference);
772
773 wf_engine.SetItemAttrText ( itemtype => itemtype,
774 itemkey => itemkey,
775 aname => 'BUDGET_DESCRIPTION',
776 avalue => l_budget_description);
777
778 wf_engine.SetItemAttrText ( itemtype => itemtype,
779 itemkey => itemkey,
780 aname => 'CHANGE_REASON_CODE',
781 avalue => l_budget_change_reason_code);
782
783 wf_engine.SetItemAttrText ( itemtype => itemtype,
784 itemkey => itemkey,
785 aname => 'BUDGET_ENTRY_METHOD',
786 avalue => l_budget_entry_method);
787
788 wf_engine.SetItemAttrText ( itemtype => itemtype,
789 itemkey => itemkey,
790 aname => 'BUDGET_ENTRY_METHOD_CODE',
791 avalue => l_budget_entry_method_code);
792
793 wf_engine.SetItemAttrText ( itemtype => itemtype,
794 itemkey => itemkey,
795 aname => 'PM_BUDGET_PRODUCT_CODE',
796 avalue => l_pm_budget_product_code);
797
798 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
799 itemkey => itemkey,
800 aname => 'TOTAL_LABOR_HOURS',
801 avalue => l_total_labor_hours);
802
803 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
804 itemkey => itemkey,
805 aname => 'TOTAL_RAW_COST',
806 avalue => l_total_raw_cost);
807
808 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'TOTAL_BURDENED_COST',
811 avalue => l_total_burdened_cost);
812
813 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
817
814 itemkey => itemkey,
815 aname => 'TOTAL_REVENUE',
816 avalue => l_total_revenue);
818 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
819 itemkey => itemkey,
820 aname => 'RESOURCE_LIST_ID',
821 avalue => l_resource_list_id);
822
823 wf_engine.SetItemAttrText ( itemtype => itemtype,
824 itemkey => itemkey,
825 aname => 'RESOURCE_LIST_NAME',
826 avalue => l_resource_list_name);
827
828 wf_engine.SetItemAttrText ( itemtype => itemtype,
829 itemkey => itemkey,
830 aname => 'RESOURCE_LIST_DESCRIPTION',
831 avalue => l_resource_list_description);
832
833 wf_engine.SetItemAttrText ( itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'MARK_AS_ORIGINAL',
836 avalue => l_mark_as_original);
837
838 wf_engine.SetItemAttrText (itemtype => itemtype,
839 itemkey => itemkey,
840 aname => 'WF_STARTED_DATE',
841 avalue => l_wf_started_date);
842
843
844 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
845 itemkey => itemkey,
846 aname => 'DRAFT_VERSION_ID',
847 avalue => p_draft_version_id);
848
849
850
851 -- Budget Integration Attributes ------------------------------------------
852
853 wf_engine.SetItemAttrText (itemtype => itemtype,
854 itemkey => itemkey,
855 aname => 'FCK_REQ_FLAG',
856 avalue => p_fck_req_flag);
857
858 wf_engine.SetItemAttrText (itemtype => itemtype,
859 itemkey => itemkey,
860 aname => 'BGT_INTG_FLAG',
861 avalue => p_bgt_intg_flag);
862
863
864 -- Financial Planning Attributes ------------------------------------------
865
866
867
868
869 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
870 itemkey => itemkey,
871 aname => 'FIN_PLAN_TYPE_ID',
872 avalue => l_fin_plan_type_id);
873
874 wf_engine.SetItemAttrText ( itemtype => itemtype,
875 itemkey => itemkey,
876 aname => 'VERSION_TYPE',
877 avalue => l_version_type);
878
879 wf_engine.SetItemAttrText ( itemtype => itemtype,
880 itemkey => itemkey,
881 aname => 'FIN_PLAN_TYPE_NAME',
882 avalue => l_fin_plan_type_name);
883
884 wf_engine.SetItemAttrText ( itemtype => itemtype,
885 itemkey => itemkey,
886 aname => 'FIN_PLAN_LEVEL',
887 avalue => l_fin_plan_level);
888
889 wf_engine.SetItemAttrText ( itemtype => itemtype,
890 itemkey => itemkey,
891 aname => 'FIN_PLAN_TIME_PHASE',
892 avalue => l_fin_plan_time_phase);
893
894
895
896 --bug 6691634
897 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=PJI_VIEW_BDGT_TASK_SUMMARY'
898 ||'&'||'paProjectId='||p_project_id
899 ||'&'||'paFinTypeId='||l_fin_plan_type_id
900 ||'&'||'paPlanClassCode='||l_plan_class_code
901 ||'&'||'paBudgetVersionId='||p_draft_version_id
902 ||'&'||'paVersionType='||l_version_type
903 ||'&'||'paTemplateFlag='||l_template_flag
904 ||'&'||'paCallingPage=paBudgetWF'
905 ||'&'||'addBreadCrumb=Y';
906
907 wf_engine.SetItemAttrText( itemtype
908 , itemkey
909 , 'FINANCIAL_PLAN_URL'
910 , l_url
911 );
912 --bug 6691634
913
914 -- -----------------------------------------------------------------------
915
916
917
918
919 wf_engine.StartProcess( itemtype => itemtype,
920 itemkey => itemkey );
921
922
923 --dbms_output.put_line('AFTER Call for StartProcess');
924
925 -- -----------------------------------------------------------------------------------
926 -- CAUTION: These two OUT-Parameters must be populated
927 -- properly in order for the calling procedures
928 -- to work as designed.
929 -- ------------------------------------------------------------------------------------
930
931 p_item_type := itemtype;
932 p_item_key := itemkey;
933
934 -- -------------------------------------------------------------------------------------
935
936 --
937 EXCEPTION
938
939 WHEN FND_API.G_EXC_ERROR
940 THEN
941 WF_CORE.CONTEXT(' PA_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
942 RAISE;
943
944 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
945 THEN
946 WF_CORE.CONTEXT(' PA_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
947 p_err_code := SQLCODE;
948 RAISE;
949
950 WHEN OTHERS
951 THEN
952 WF_CORE.CONTEXT(' PA_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
953 p_err_code := SQLCODE;
954 RAISE;
955
956 END;
957
958 END START_BUDGET_WF;
959
960
964 --Type: Procedure
961 -- ===================================================
962
963 --Name: Select_Budget_Approver
965 --Description: This client extension returns the
966 -- correct budget approver.
967 --
968 --
969 --Called subprograms:
970 --
971 --
972 --
973 --History:
974 -- 24-FEB-97 L. de Werker - Created
975 -- 24-JUN-97 jwhite - Updated to latest specs.
976 -- 26-SEP-97 jwhite - Updated WF error processing.
977 -- 21-OCT-87 jwhite - Updated as per Kevin Hudson's code review
978 --
979 -- 08-AUG-02 jwhite - Adapted default logic to also support the new FP model
980 --
981 -- IN
982 -- p_project_id - unique identifier for the project
983 -- p_budget_type_code - needed to uniquely identify the working budget
984 -- p_workflow_started_by_id - identifies the user that initiated the workflow
985 --
986 -- OUT
987 -- p_budget_baseliner_id - unique identifier of the employee
988 -- (employee_id in per_people_f table)
989 -- that must approver this budget for baselining.
990 --
991
992 PROCEDURE Select_Budget_Approver
993 (p_item_type IN VARCHAR2
994 , p_item_key IN VARCHAR2
995 , p_project_id IN NUMBER
996 , p_budget_type_code IN VARCHAR2
997 , p_workflow_started_by_id IN NUMBER
998 , p_fin_plan_type_id IN NUMBER default NULL
999 , p_version_type IN VARCHAR2 default NULL
1000 , p_draft_version_id IN NUMBER default NULL
1001 , p_budget_baseliner_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1002 )
1003 --
1004 IS
1005
1006 --
1007 -- Define Your Local Variables Here
1008 --
1009 l_employee_id NUMBER;
1010 --
1011 /*
1012 You can use this procedure to add any additional rules to determine
1013 who can approve a project. This procedure is being used by the
1014 Workflow APIs and determine who the approver for a project
1015 should be. By default this procedure fetches the supervisor of the
1016 person who initiated the workflow as the approver.
1017 */
1018
1019 BEGIN
1020
1021 -- Specify Your Business Rules Here
1022
1023 SELECT employee_id
1024 INTO l_employee_id
1025 FROM fnd_user
1026 WHERE user_id = p_workflow_started_by_id;
1027
1028 SELECT supervisor_id
1029 INTO p_budget_baseliner_id
1030 FROM per_assignments_f
1031 WHERE person_id = l_employee_id
1032 AND assignment_type in ('C','E') /* Bug#2911451 + FP.M for 'C' */
1033 AND primary_flag = 'Y' /* Bug#2911451 */
1034 AND TRUNC(sysdate) BETWEEN EFFECTIVE_START_DATE
1035 AND NVL(EFFECTIVE_END_DATE, sysdate);
1036
1037
1038
1039 --
1040 --The following algorithm can be used to handle known error conditions
1041 --When this code is used the arguments and there values will be displayed
1042 --in the error message that is send by workflow.
1043 --
1044 --IF <error condition>
1045 --THEN
1046 -- WF_CORE.TOKEN('ARG1', arg1);
1047 -- WF_CORE.TOKEN('ARGn', argn);
1048 -- WF_CORE.RAISE('ERROR_NAME');
1049 --END IF;
1050
1051 EXCEPTION
1052
1053 WHEN OTHERS THEN
1054 WF_CORE.CONTEXT('PA_CLIENT_EXTN_BUDGET_WF','SELECT_BUDGET_APPROVER',
1055 p_item_type, p_item_key);
1056 RAISE;
1057
1058 END Select_Budget_Approver;
1059
1060 -- ==================================================
1061 --Name: Verify_Budget_Rules
1062 --Type: Procedure
1063 --Description: This procedure is for verification rules that may
1064 -- vary by workflow.
1065 --
1066 --
1067 --Called subprograms: none.
1068 --
1069 --
1070 --
1071 --History:
1072 -- 25-FEB-97 L. de Werker - Created
1073 -- 05-SEP-97 jwhite - Updated to latest specs.
1074 -- 26-SEP-97 jwhite - Updated WF error processing.
1075 -- 21-OCT-87 jwhite - Updated as per Kevin Hudson's code review
1076 --
1077 -- 26-APR-01 jwhite - For the Verify_Budget_Rules API,
1078 -- added notes and code for global
1079 -- G_bgt_intg_flag for GL/PA Budget Integration.
1080 --
1081 -- 08-AUG-02 jwhite - Adapted default logic to also support the new FP model
1082 --
1083 -- IN
1084 -- p_item_type - WF item type
1085 -- p_item_key - WF item key
1086 -- p_project_id - unique identifier for the project that needs baselining
1087 -- p_budget_type_code - needed to uniquely identify this working budget
1088 -- p_workflow_started_by_id - identifies the user that initiated the workflow
1089 -- p_event - indicates whether procedure called for
1090 -- either a 'SUBMIT' or 'BASELINE' event.
1091 --
1092 -- G_bgt_intg_flag - PA_BUDGET_UTILS.G_Bgt_Intg_Flag
1093 -- This package specification global defaults to NULL.
1094 -- It may be populated by the Budgets form and other Budget
1095 -- APIs for integration budgets. It will NOT be populated
1096 -- by Budget and Project Form Copy_Budget functions.
1097 --
1098 -- The values and meanings for this global are as follows:
1102
1099 -- NULL or 'N' - Budget Integration not enabled
1100 -- 'G' - GL Budget Integration
1101 -- 'C' - CBC Budget Integration
1103 --
1104 -- OUT
1105 -- p_warnings_only_flag - RETURN 'Y' if ALL triggered edits are warnings. Otherwise,
1106 -- if there is at least one hard error, then RETURN 'N'.
1107 -- p_err_msg_count - Count of warning and error messages.
1108 --
1109 -- NOTES
1110 -- By using the commented code in the body of this procedure, you may
1111 -- add error and warning messages to the message stack.
1112 -- However, the workflow notification will only display
1113 -- ten messages.
1114 --
1115 -- Moreover, error/warning processing in the calling procedure
1116 -- will only occur if OUT p_err_msg_count
1117 -- parameter is greater than zero.
1118 --
1119
1120 PROCEDURE Verify_Budget_Rules
1121 (p_item_type IN VARCHAR2
1122 , p_item_key IN VARCHAR2
1123 , p_project_id IN NUMBER
1124 , p_budget_type_code IN VARCHAR2
1125 , p_workflow_started_by_id IN NUMBER
1126 , p_event IN VARCHAR2
1127 , p_fin_plan_type_id IN NUMBER default NULL
1128 , p_version_type IN VARCHAR2 default NULL
1129 , p_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1130 , p_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1131 )
1132 --
1133 IS
1134 --
1135 -- Declare Variables here
1136
1137 -- Global Semaphore for Non-Project Budget Integration
1138 l_bgt_intg_flag VARCHAR2(1) :=NULL;
1139
1140
1141 BEGIN
1142 --
1143 -- Initialize Local Variable for Non-Project Budget Integration Global.
1144 --
1145 l_bgt_intg_flag := PA_BUDGET_UTILS.G_Bgt_Intg_Flag;
1146
1147 --
1148 -- Initialize OUT-parameters Here.
1149 -- All 'p_' parameters are required.
1150 --
1151 p_warnings_only_flag := 'Y';
1152 p_err_msg_count := 0;
1153
1154 --
1155 -- Put The Rules That You Want To Check For Here
1156 --
1157
1158 --
1159 -- NOTIFICATION Error/Warning Handling --------------------------
1160 --
1161 -- Note: You must call PA_UTILS.Add_Message at least once
1162 -- for the higher-level workflow processing to be invoked.
1163 --
1164 -- For error and warning messages, you must increment the p_err_msg_count
1165 -- OUT-parameter before passing control to the calling procedure:
1166 --
1167 -- p_err_msg_count := FND_MSG_PUB.Count_Msg;
1168 --
1169 --
1170 -- For a hard error, one that you want to force the calling procedure
1171 -- to invoke a 'False' or 'Failure' transition:
1172 --
1173 -- p_warnings_only_flag := 'N';
1174 --
1175 --
1176 -- To display an error or warning message in the workflow notification, you
1177 -- must call the following:
1178 --
1179 -- PA_UTILS.Add_Message
1180 --
1181 -- For example, a typical call might look like the following:
1182 --
1183 -- PA_UTILS.Add_Message
1184 -- ( p_app_short_name => 'PA'
1185 -- , p_msg_name => 'PA_NO_BUDGET_RULES_ATTR'
1186 -- );
1187 -- ---------------------------------------------------------------------------------------
1188
1189 --
1190 -- WF_CORE Error Handling --------------------------------------------------
1191 -- To display errors using the WF_CORE functionality,
1192 -- the following algorithm can be used to handle known error conditions.
1193 -- When this code is used the arguments and there values will be displayed
1194 -- in the workflow monitor.
1195 --
1196 --IF <error condition>
1197 --THEN
1198 -- WF_CORE.TOKEN('ARG1', arg1);
1199 -- WF_CORE.TOKEN('ARGn', argn);
1200 -- WF_CORE.RAISE('ERROR_NAME');
1201 --END IF;
1202 -- ---------------------------------------------------------------------------------------
1203
1204 --
1205 -- Make sure to update the OUT variable for the
1206 -- message count
1207 --
1208 p_err_msg_count := FND_MSG_PUB.Count_Msg;
1209
1210
1211 EXCEPTION
1212
1213 WHEN OTHERS THEN
1214 WF_CORE.CONTEXT('PA_CLIENT_EXTN_BUDGET_WF','VERIFY_BUDGET_RULES', p_item_type, p_item_key);
1215 RAISE;
1216
1217
1218 END Verify_Budget_Rules;
1219 -- =================================================
1220
1221 END pa_client_extn_budget_wf;