[Home] [Help]
PACKAGE BODY: APPS.PA_CLIENT_EXTN_BUDGET_WF
Source
1 PACKAGE BODY pa_client_extn_budget_wf AS
2 /* $Header: PAWFBCEB.pls 120.7.12020000.4 2012/09/28 11:31:54 tvala 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
395 WHERE fpt.fin_plan_type_id = l_fin_plan_type_id;
396 --AND fpt.LANGUAGE = USERENV('LANG');Bug 6691634
397 --
398 CURSOR l_fin_attr_csr (p_draft_version_id NUMBER, l_version_type VARCHAR2)
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 CURSOR l_get_change_reason_csr(l_budget_change_reason_code VARCHAR2)
422 IS
423 SELECT MEANING
424 FROM PA_LOOKUPS
425 WHERE LOOKUP_CODE = l_budget_change_reason_code
426 AND LOOKUP_TYPE = 'BUDGET CHANGE REASON'; -- Bug #14623649
427
428
429 ItemType varchar2(30) := 'PABUDWF'; --<----Identifies the workflow process!!!
430 ItemKey varchar2(30);
431
432 l_pm_project_reference pa_projects.pm_project_reference%TYPE;
433 l_pa_project_number pa_projects.segment1%TYPE;
434 l_project_name pa_projects.name%TYPE;
435 l_description pa_projects.description%TYPE;
436 l_project_type pa_projects.project_type%TYPE;
437 l_pm_project_product_code pa_projects.pm_product_code%TYPE;
438 l_carrying_out_org_id NUMBER;
439 l_carrying_out_org_name hr_organization_units.name%TYPE;
440 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
441
442 l_pm_budget_reference pa_budget_versions.pm_budget_reference%TYPE;
443 l_budget_description pa_budget_versions.description%TYPE;
444 l_budget_change_reason_code pa_budget_versions.change_reason_code%TYPE;
445 l_budget_change_reason_meaning pa_lookups.meaning%TYPE;
446 l_budget_entry_method_code pa_budget_versions.budget_entry_method_code%TYPE;
447 l_budget_entry_method pa_budget_entry_methods.budget_entry_method%TYPE;
448 l_pm_budget_product_code pa_budget_versions.pm_product_code%TYPE;
449 l_mark_as_original pa_budget_versions.original_flag%TYPE;
450 l_version_name pa_budget_versions.version_name%TYPE;
451 l_budget_type_code pa_budget_versions.budget_type_code%TYPE;
452
453 l_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE;
454 l_version_type pa_budget_versions.version_type%TYPE;
455 l_fin_plan_type_name pa_fin_plan_types_tl.name%TYPE;
456 l_fin_plan_level pa_lookups.meaning%TYPE;
457 l_fin_plan_time_phase pa_lookups.meaning%TYPE;
458
459
460 l_total_labor_hours NUMBER;
461 l_total_raw_cost NUMBER;
462 l_total_burdened_cost NUMBER;
463 l_total_revenue NUMBER;
464 l_resource_list_id NUMBER;
465 l_resource_list_name pa_resource_lists.name%TYPE;
466 l_resource_list_description pa_resource_lists.description%TYPE;
467 l_budget_type pa_fin_plan_types_tl.name%TYPE; --Bug 6974760 pa_budget_types.budget_type%TYPE;
468 l_wf_started_date DATE;
469
470 l_workflow_started_by_id NUMBER;
471 l_user_name VARCHAR2(240);
472 l_full_name VARCHAR2(400);/*UTF8-from varchar(240) to (400)*/
473 l_resp_id NUMBER;
474 l_row_found VARCHAR2(1);
475
476 l_api_version_number NUMBER := G_api_version_number ;
477 l_msg_count NUMBER;
478 l_msg_data VARCHAR(2000);
479 l_return_status VARCHAR2(1) := NULL;
480 l_data VARCHAR2(2000);
481 l_msg_index_out NUMBER;
482 l_err_code NUMBER := 0;
483 l_err_stage VARCHAR2(100);
484 l_err_stack VARCHAR2(100);
485
486 -- Start Changes for bug 6691634
487 l_url VARCHAR2(2000);
488 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
489 l_template_flag VARCHAR2(1);
490 -- End Changes for bug 6691634
491 l_wf_starter_role varchar2(50); -- Bug 13692127
492 --
493 --
494 BEGIN
495
496 -- Standard BEGIN of API savepoint
497
498 SAVEPOINT START_BUDGET_WF_pvt;
499
500 -- Set API Return Status To Success for Public API and Form Error Processing
501
502 p_err_code := 0;
503
504
505 BEGIN
506
507 --
508 -- Initialize FND Globals for Starting Approve Budget Workflow --------------
509 --
510 -- Please note that these globals will be populated from the calling
511 -- module (AMG procedure, Budgets form or Budget Integration Workflow).
512
513
514 --dbms_output.put_line('Item Key(s/b null): '||itemkey);
515
516 l_workflow_started_by_id := FND_GLOBAL.user_id;
517
518 OPEN l_starter_user_name_csr( l_workflow_started_by_id );
519 FETCH l_starter_user_name_csr INTO l_user_name;
520 CLOSE l_starter_user_name_csr;
521
522 OPEN l_starter_full_name_csr( l_workflow_started_by_id );
523 FETCH l_starter_full_name_csr INTO l_full_name;
524 CLOSE l_starter_full_name_csr;
525
526 l_resp_id := FND_GLOBAL.resp_id;
527
528 -- Based on the Responsibility, Intialize the Application
529 -- Cannot call Set_Global_Attr here because the WF does NOT
530 -- Exist yet.
531 FND_GLOBAL.Apps_Initialize
532 (user_id => l_workflow_started_by_id
533 , resp_id => l_resp_id
534 , resp_appl_id => FND_GLOBAL.resp_appl_id
535 );
536
537
538
539 --
540 -- Populate Workflow IN-Parameters ----------------------------------------------
541 --
542
543 -- Mark-As-Original Flag Set From IN-Parameter
544 l_mark_as_original := p_mark_as_original;
545
546
547 OPEN l_project_csr(p_project_id);
548 FETCH l_project_csr INTO l_pm_project_reference
549 ,l_pa_project_number
550 ,l_project_name
551 ,l_description
552 ,l_project_type
553 ,l_pm_project_product_code
554 ,l_carrying_out_org_id
555 ,l_template_flag; --Bug 6691634
556 CLOSE l_project_csr;
557
558
559 OPEN l_organization_csr( l_carrying_out_org_id );
560 FETCH l_organization_csr INTO l_carrying_out_org_name;
561 CLOSE l_organization_csr;
562
563 OPEN l_project_type_class( l_project_type );
564 FETCH l_project_type_class INTO l_project_type_class_code;
565 CLOSE l_project_type_class;
566
567 OPEN l_budget_csr( p_draft_version_id );
568 FETCH l_budget_csr INTO l_pm_budget_reference
569 ,l_budget_description
570 ,l_budget_change_reason_code
571 ,l_budget_entry_method_code
572 ,l_pm_budget_product_code
573 ,l_total_labor_hours
574 ,l_total_raw_cost
575 ,l_total_burdened_cost
576 ,l_total_revenue
577 ,l_resource_list_id
578 ,l_version_name
579 ,l_budget_type_code
580 ,l_fin_plan_type_id
581 ,l_version_type;
582
583 CLOSE l_budget_csr;
584
585
586 -- Conditional Processing for r11.5.7/FP Models -------------
587 IF (l_fin_plan_type_id IS NULL)
588 THEN
589 -- R11.5.7 Model ----------------------------
590
591
592 -- Not Applicable
593 l_fin_plan_type_name := NULL;
594 l_fin_plan_level := NULL;
595 l_fin_plan_time_phase := NULL;
596
597
598 -- Get Budget Type Name
599 OPEN l_budget_type_csr( p_budget_type_code );
600 FETCH l_budget_type_csr INTO l_budget_type;
601 CLOSE l_budget_type_csr;
602
603 -- Get Budget Entry Method Name
604 OPEN l_BEM_csr( l_budget_entry_method_code );
605 FETCH l_BEM_csr INTO l_budget_entry_method;
606 CLOSE l_BEM_csr;
607
608
609
610
611 ELSE
612 -- FP Model ---------------------------------
613
614
615
616 OPEN l_fin_plan_name_csr (l_fin_plan_type_id);
617 FETCH l_fin_plan_name_csr INTO l_fin_plan_type_name ,l_plan_class_code; -- Bug 6691634
618 CLOSE l_fin_plan_name_csr;
619
620 OPEN l_fin_attr_csr (p_draft_version_id, l_version_type);
621 FETCH l_fin_attr_csr INTO l_fin_plan_level, l_fin_plan_time_phase;
622 CLOSE l_fin_attr_csr;
623
624
625 -- Not Applicable to FP Model, but ...
626
627 -- Used to Display Plan Type Name on PA Default Notifications !!!
628 l_budget_type := l_fin_plan_type_name;
629
630
631 -- Displayed as NULL on Notification
632 l_budget_entry_method_code := NULL; -- BEM Code
633 l_budget_entry_method := NULL; -- BEM Name
634
635
636 END IF; -- l_fin_plan_type_id IS NULL
637
638 -- ----------------------------------------------------------
639
640 OPEN l_resource_list_csr( l_resource_list_id );
641 FETCH l_resource_list_csr INTO l_resource_list_name
642 ,l_resource_list_description;
643 CLOSE l_resource_list_csr;
644
645 OPEN l_wf_started_date_csr;
646 FETCH l_wf_started_date_csr INTO l_wf_started_date;
647 CLOSE l_wf_started_date_csr;
648
649
650
651 SELECT pa_workflow_itemkey_s.nextval
652 INTO itemkey
653 from dual;
654
655 --dbms_output.put_line('Item Key!: '||itemkey);
656
657 EXCEPTION
658
659 WHEN FND_API.G_EXC_ERROR
660 THEN
661 ROLLBACK TO START_BUDGET_WF_pvt;
662 RAISE;
663
664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
665 THEN
666 p_err_code := SQLCODE;
667 ROLLBACK TO START_BUDGET_WF_pvt;
668 RAISE;
669
670 WHEN OTHERS THEN
671 p_err_code := SQLCODE;
672 ROLLBACK TO START_BUDGET_WF_pvt;
673 RAISE;
674
675
676 END;
677
678 BEGIN
679 -- ------------------------------------------------------------------------------------
680 -- INSTANTIATE BUDGET WORKFLOW
681 -- ------------------------------------------------------------------------------------
682 -- NOTE:
683 -- The process name passed here is the root process for the
684 -- 'PA Budget Approval Workflow'.
685 -- ------------------------------------------------------------------------------------
686 --dbms_output.put_line('Call for CreateProcess');
687
688 wf_engine.CreateProcess( ItemType => ItemType,
689 ItemKey => ItemKey,
690 process => 'PRO_BASELINE_BUDGET' );
691
692 --dbms_output.put_line('SetitemAttributes');
693
694
695 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
696 itemkey => itemkey,
697 aname => 'PROJECT_ID',
698 avalue => p_project_id);
699 --
700 wf_engine.SetItemAttrText ( itemtype => itemtype,
701 itemkey => itemkey,
702 aname => 'PM_PROJECT_REFERENCE',
703 avalue => l_pm_project_reference );
704
705 wf_engine.SetItemAttrText ( itemtype => itemtype,
706 itemkey => itemkey,
707 aname => 'PA_PROJECT_NUMBER',
708 avalue => l_pa_project_number );
709
710 wf_engine.SetItemAttrText ( itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'PROJECT_NAME',
713 avalue => l_project_name );
714
715 wf_engine.SetItemAttrText ( itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'PROJECT_DESCRIPTION',
718 avalue => l_description );
719
720 wf_engine.SetItemAttrText ( itemtype => itemtype,
721 itemkey => itemkey,
722 aname => 'PROJECT_TYPE',
723 avalue => l_project_type );
724
725 wf_engine.SetItemAttrText ( itemtype => itemtype,
726 itemkey => itemkey,
727 aname => 'PM_PROJECT_PRODUCT_CODE',
728 avalue => l_pm_project_product_code );
729
730 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
731 itemkey => itemkey,
732 aname => 'CARRYING_OUT_ORG_ID',
733 avalue => l_carrying_out_org_id);
734
735 wf_engine.SetItemAttrText ( itemtype => itemtype,
736 itemkey => itemkey,
737 aname => 'CARRYING_OUT_ORG_NAME',
738 avalue => l_carrying_out_org_name);
739
740 wf_engine.SetItemAttrText ( itemtype => itemtype,
741 itemkey => itemkey,
742 aname => 'PROJECT_TYPE_CLASS_CODE',
743 avalue => l_project_type_class_code);
744
745 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
746 itemkey => itemkey,
747 aname => 'WORKFLOW_STARTED_BY_ID',
748 avalue => l_workflow_started_by_id);
749
750 wf_engine.SetItemAttrText ( itemtype => itemtype,
751 itemkey => itemkey,
752 aname => 'WORKFLOW_STARTED_BY_NAME',
753 avalue => l_user_name);
754
755 wf_engine.SetItemAttrText ( itemtype => itemtype,
756 itemkey => itemkey,
757 aname => 'WORKFLOW_STARTED_BY_FULL_NAME',
758 avalue => l_full_name);
759
760 -- Bug 13692127
761 l_wf_starter_role := 'START_' ||itemtype || itemkey || to_char(sysdate, 'Jsssss');
762
763 WF_DIRECTORY.CreateAdHocRole( role_name => l_wf_starter_role
764 , role_display_name => l_full_name
765 , expiration_date => sysdate+1
766 );
767
768 wf_engine.SetItemAttrText
769 (itemtype => itemtype,
770 itemkey => itemkey,
771 aname => 'WORKFLOW_STARTED_BY_ROLE',
772 avalue => l_wf_starter_role );
773
774 OPEN l_get_change_reason_csr(l_budget_change_reason_code);
775 FETCH l_get_change_reason_csr INTO l_budget_change_reason_meaning;
776 CLOSE l_get_change_reason_csr; -- Bug #14623649
777
778 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
779 itemkey => itemkey,
780 aname => 'RESPONSIBILITY_ID',
781 avalue => l_resp_id);
782
783 wf_engine.SetItemAttrText ( itemtype => itemtype,
784 itemkey => itemkey,
785 aname => 'BUDGET_TYPE_CODE',
786 avalue => l_budget_type_code);
787
788 wf_engine.SetItemAttrText ( itemtype => itemtype,
789 itemkey => itemkey,
790 aname => 'BUDGET_TYPE',
791 avalue => l_budget_type);
792
793 wf_engine.SetItemAttrText ( itemtype => itemtype,
794 itemkey => itemkey,
795 aname => 'PM_BUDGET_REFERENCE',
796 avalue => l_pm_budget_reference);
797
798 wf_engine.SetItemAttrText ( itemtype => itemtype,
799 itemkey => itemkey,
800 aname => 'BUDGET_DESCRIPTION',
801 avalue => l_budget_description);
802
803 wf_engine.SetItemAttrText ( itemtype => itemtype,
804 itemkey => itemkey,
805 aname => 'CHANGE_REASON_CODE',
806 avalue => l_budget_change_reason_code);
807
808 wf_engine.SetItemAttrText ( itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'CHANGE_REASON_MEANING',
811 avalue => l_budget_change_reason_meaning); --Bug #14623649
812
813 wf_engine.SetItemAttrText ( itemtype => itemtype,
814 itemkey => itemkey,
815 aname => 'BUDGET_ENTRY_METHOD',
816 avalue => l_budget_entry_method);
817
818 wf_engine.SetItemAttrText ( itemtype => itemtype,
819 itemkey => itemkey,
820 aname => 'BUDGET_ENTRY_METHOD_CODE',
821 avalue => l_budget_entry_method_code);
822
823 wf_engine.SetItemAttrText ( itemtype => itemtype,
824 itemkey => itemkey,
825 aname => 'PM_BUDGET_PRODUCT_CODE',
826 avalue => l_pm_budget_product_code);
827
828 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
829 itemkey => itemkey,
830 aname => 'TOTAL_LABOR_HOURS',
831 avalue => l_total_labor_hours);
832
833 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'TOTAL_RAW_COST',
836 avalue => l_total_raw_cost);
837
838 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
839 itemkey => itemkey,
840 aname => 'TOTAL_BURDENED_COST',
841 avalue => l_total_burdened_cost);
842
843 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
844 itemkey => itemkey,
845 aname => 'TOTAL_REVENUE',
846 avalue => l_total_revenue);
847
848 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
849 itemkey => itemkey,
850 aname => 'RESOURCE_LIST_ID',
851 avalue => l_resource_list_id);
852
853 wf_engine.SetItemAttrText ( itemtype => itemtype,
854 itemkey => itemkey,
855 aname => 'RESOURCE_LIST_NAME',
856 avalue => l_resource_list_name);
857
858 wf_engine.SetItemAttrText ( itemtype => itemtype,
859 itemkey => itemkey,
860 aname => 'RESOURCE_LIST_DESCRIPTION',
861 avalue => l_resource_list_description);
862
863 wf_engine.SetItemAttrText ( itemtype => itemtype,
864 itemkey => itemkey,
865 aname => 'MARK_AS_ORIGINAL',
866 avalue => l_mark_as_original);
867
868 wf_engine.SetItemAttrText (itemtype => itemtype,
869 itemkey => itemkey,
870 aname => 'WF_STARTED_DATE',
871 avalue => l_wf_started_date);
872
873
874 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
875 itemkey => itemkey,
876 aname => 'DRAFT_VERSION_ID',
877 avalue => p_draft_version_id);
878
879
880
881 -- Budget Integration Attributes ------------------------------------------
882
883 wf_engine.SetItemAttrText (itemtype => itemtype,
884 itemkey => itemkey,
885 aname => 'FCK_REQ_FLAG',
886 avalue => p_fck_req_flag);
887
888 wf_engine.SetItemAttrText (itemtype => itemtype,
889 itemkey => itemkey,
890 aname => 'BGT_INTG_FLAG',
891 avalue => p_bgt_intg_flag);
892
893
894 -- Financial Planning Attributes ------------------------------------------
895
896
897
898
899 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
900 itemkey => itemkey,
901 aname => 'FIN_PLAN_TYPE_ID',
902 avalue => l_fin_plan_type_id);
903
904 wf_engine.SetItemAttrText ( itemtype => itemtype,
905 itemkey => itemkey,
906 aname => 'VERSION_TYPE',
907 avalue => l_version_type);
908
909 wf_engine.SetItemAttrText ( itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'FIN_PLAN_TYPE_NAME',
912 avalue => l_fin_plan_type_name);
913
914 wf_engine.SetItemAttrText ( itemtype => itemtype,
915 itemkey => itemkey,
916 aname => 'FIN_PLAN_LEVEL',
917 avalue => l_fin_plan_level);
918
919 wf_engine.SetItemAttrText ( itemtype => itemtype,
920 itemkey => itemkey,
921 aname => 'FIN_PLAN_TIME_PHASE',
922 avalue => l_fin_plan_time_phase);
923
924
925 -- Added this condition for Bug 8742127
926 IF l_fin_plan_type_id IS NOT NULL THEN
927
928 --bug 6691634
929 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=PJI_VIEW_BDGT_TASK_SUMMARY'
930 ||'&'||'paProjectId='||p_project_id
931 ||'&'||'paFinTypeId='||l_fin_plan_type_id
932 ||'&'||'paPlanClassCode='||l_plan_class_code
933 ||'&'||'paBudgetVersionId='||p_draft_version_id
934 ||'&'||'paVersionType='||l_version_type
935 ||'&'||'paTemplateFlag='||l_template_flag
936 ||'&'||'paCallingPage=paBudgetWF'
937 ||'&'||'addBreadCrumb=Y';
938
939 wf_engine.SetItemAttrText( itemtype
940 , itemkey
941 , 'FINANCIAL_PLAN_URL'
942 , l_url
943 );
944 --bug 6691634
945
946 END IF;
947
948 -- -----------------------------------------------------------------------
949
950
951
952
953 wf_engine.StartProcess( itemtype => itemtype,
954 itemkey => itemkey );
955
956
957 --dbms_output.put_line('AFTER Call for StartProcess');
958
959 -- -----------------------------------------------------------------------------------
960 -- CAUTION: These two OUT-Parameters must be populated
961 -- properly in order for the calling procedures
962 -- to work as designed.
963 -- ------------------------------------------------------------------------------------
964
965 p_item_type := itemtype;
966 p_item_key := itemkey;
967
968 -- -------------------------------------------------------------------------------------
969
970 --
971 EXCEPTION
972
973 WHEN FND_API.G_EXC_ERROR
974 THEN
975 WF_CORE.CONTEXT(' PA_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
976 RAISE;
977
978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
979 THEN
980 WF_CORE.CONTEXT(' PA_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
981 p_err_code := SQLCODE;
982 RAISE;
983
984 WHEN OTHERS
985 THEN
986 WF_CORE.CONTEXT(' PA_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
987 p_err_code := SQLCODE;
988 RAISE;
989
990 END;
991
992 END START_BUDGET_WF;
993
994
995 -- ===================================================
996
997 --Name: Select_Budget_Approver
998 --Type: Procedure
999 --Description: This client extension returns the
1000 -- correct budget approver.
1001 --
1002 --
1003 --Called subprograms:
1004 --
1005 --
1006 --
1007 --History:
1008 -- 24-FEB-97 L. de Werker - Created
1009 -- 24-JUN-97 jwhite - Updated to latest specs.
1010 -- 26-SEP-97 jwhite - Updated WF error processing.
1011 -- 21-OCT-87 jwhite - Updated as per Kevin Hudson's code review
1012 --
1013 -- 08-AUG-02 jwhite - Adapted default logic to also support the new FP model
1014 --
1015 -- IN
1016 -- p_project_id - unique identifier for the project
1017 -- p_budget_type_code - needed to uniquely identify the working budget
1018 -- p_workflow_started_by_id - identifies the user that initiated the workflow
1019 --
1020 -- OUT
1021 -- p_budget_baseliner_id - unique identifier of the employee
1022 -- (employee_id in per_people_f table)
1023 -- that must approver this budget for baselining.
1024 --
1025
1026 PROCEDURE Select_Budget_Approver
1027 (p_item_type IN VARCHAR2
1028 , p_item_key IN VARCHAR2
1029 , p_project_id IN NUMBER
1030 , p_budget_type_code IN VARCHAR2
1031 , p_workflow_started_by_id IN NUMBER
1032 , p_fin_plan_type_id IN NUMBER default NULL
1033 , p_version_type IN VARCHAR2 default NULL
1034 , p_draft_version_id IN NUMBER default NULL
1035 , p_budget_baseliner_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1036 )
1037 --
1038 IS
1039
1040 --
1041 -- Define Your Local Variables Here
1042 --
1043 l_employee_id NUMBER;
1044
1045 -- Selects modified to Cursors for Bug# 7675780
1046 CURSOR c_employee_id(p_workflow_started_by_id NUMBER) IS
1047 SELECT employee_id
1048 FROM fnd_user
1049 WHERE user_id = p_workflow_started_by_id;
1050
1051 CURSOR c_supervisor_id(p_employee_id NUMBER) IS
1052 SELECT supervisor_id
1053 FROM per_assignments_f
1054 WHERE person_id = l_employee_id
1055 AND assignment_type in ('C','E') -- Bug#2911451 + FP.M for 'C'
1056 AND primary_flag = 'Y' -- Bug#2911451
1057 AND TRUNC(sysdate) BETWEEN EFFECTIVE_START_DATE
1058 AND NVL(EFFECTIVE_END_DATE, sysdate);
1059
1060 --
1061 /*
1062 You can use this procedure to add any additional rules to determine
1063 who can approve a project. This procedure is being used by the
1064 Workflow APIs and determine who the approver for a project
1065 should be. By default this procedure fetches the supervisor of the
1066 person who initiated the workflow as the approver.
1067 */
1068
1069 BEGIN
1070
1071 -- Specify Your Business Rules Here
1072 -- Selects modified to Cursors for Bug# 7675780
1073
1074 OPEN c_employee_id(p_workflow_started_by_id);
1075 FETCH c_employee_id INTO l_employee_id;
1076 CLOSE c_employee_id;
1077
1078 OPEN c_supervisor_id(l_employee_id);
1079 FETCH c_supervisor_id INTO p_budget_baseliner_id;
1080 CLOSE c_supervisor_id;
1081
1082 --
1083 --The following algorithm can be used to handle known error conditions
1084 --When this code is used the arguments and there values will be displayed
1085 --in the error message that is send by workflow.
1086 --
1087 --IF <error condition>
1088 --THEN
1089 -- WF_CORE.TOKEN('ARG1', arg1);
1090 -- WF_CORE.TOKEN('ARGn', argn);
1091 -- WF_CORE.RAISE('ERROR_NAME');
1092 --END IF;
1093
1094 EXCEPTION
1095
1096 WHEN OTHERS THEN
1097 WF_CORE.CONTEXT('PA_CLIENT_EXTN_BUDGET_WF','SELECT_BUDGET_APPROVER',
1098 p_item_type, p_item_key);
1099 RAISE;
1100
1101 END Select_Budget_Approver;
1102
1103 -- ==================================================
1104 --Name: Verify_Budget_Rules
1105 --Type: Procedure
1106 --Description: This procedure is for verification rules that may
1107 -- vary by workflow.
1108 --
1109 --
1110 --Called subprograms: none.
1111 --
1112 --
1113 --
1114 --History:
1115 -- 25-FEB-97 L. de Werker - Created
1116 -- 05-SEP-97 jwhite - Updated to latest specs.
1117 -- 26-SEP-97 jwhite - Updated WF error processing.
1118 -- 21-OCT-87 jwhite - Updated as per Kevin Hudson's code review
1119 --
1120 -- 26-APR-01 jwhite - For the Verify_Budget_Rules API,
1121 -- added notes and code for global
1122 -- G_bgt_intg_flag for GL/PA Budget Integration.
1123 --
1124 -- 08-AUG-02 jwhite - Adapted default logic to also support the new FP model
1125 --
1126 -- IN
1127 -- p_item_type - WF item type
1128 -- p_item_key - WF item key
1129 -- p_project_id - unique identifier for the project that needs baselining
1130 -- p_budget_type_code - needed to uniquely identify this working budget
1131 -- p_workflow_started_by_id - identifies the user that initiated the workflow
1132 -- p_event - indicates whether procedure called for
1133 -- either a 'SUBMIT' or 'BASELINE' event.
1134 --
1135 -- G_bgt_intg_flag - PA_BUDGET_UTILS.G_Bgt_Intg_Flag
1136 -- This package specification global defaults to NULL.
1137 -- It may be populated by the Budgets form and other Budget
1138 -- APIs for integration budgets. It will NOT be populated
1139 -- by Budget and Project Form Copy_Budget functions.
1140 --
1141 -- The values and meanings for this global are as follows:
1142 -- NULL or 'N' - Budget Integration not enabled
1143 -- 'G' - GL Budget Integration
1144 -- 'C' - CBC Budget Integration
1145
1146 --
1147 -- OUT
1148 -- p_warnings_only_flag - RETURN 'Y' if ALL triggered edits are warnings. Otherwise,
1149 -- if there is at least one hard error, then RETURN 'N'.
1150 -- p_err_msg_count - Count of warning and error messages.
1151 --
1152 -- NOTES
1153 -- By using the commented code in the body of this procedure, you may
1154 -- add error and warning messages to the message stack.
1155 -- However, the workflow notification will only display
1156 -- ten messages.
1157 --
1158 -- Moreover, error/warning processing in the calling procedure
1159 -- will only occur if OUT p_err_msg_count
1160 -- parameter is greater than zero.
1161 --
1162
1163 PROCEDURE Verify_Budget_Rules
1164 (p_item_type IN VARCHAR2
1165 , p_item_key IN VARCHAR2
1166 , p_project_id IN NUMBER
1167 , p_budget_type_code IN VARCHAR2
1168 , p_workflow_started_by_id IN NUMBER
1169 , p_event IN VARCHAR2
1170 , p_fin_plan_type_id IN NUMBER default NULL
1171 , p_version_type IN VARCHAR2 default NULL
1172 , p_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1173 , p_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1174 )
1175 --
1176 IS
1177 --
1178 -- Declare Variables here
1179
1180 -- Global Semaphore for Non-Project Budget Integration
1181 l_bgt_intg_flag VARCHAR2(1) :=NULL;
1182
1183
1184 BEGIN
1185 --
1186 -- Initialize Local Variable for Non-Project Budget Integration Global.
1187 --
1188 l_bgt_intg_flag := PA_BUDGET_UTILS.G_Bgt_Intg_Flag;
1189
1190 --
1191 -- Initialize OUT-parameters Here.
1192 -- All 'p_' parameters are required.
1193 --
1194 p_warnings_only_flag := 'Y';
1195 p_err_msg_count := 0;
1196
1197 --
1198 -- Put The Rules That You Want To Check For Here
1199 --
1200
1201 --
1202 -- NOTIFICATION Error/Warning Handling --------------------------
1203 --
1204 -- Note: You must call PA_UTILS.Add_Message at least once
1205 -- for the higher-level workflow processing to be invoked.
1206 --
1207 -- For error and warning messages, you must increment the p_err_msg_count
1208 -- OUT-parameter before passing control to the calling procedure:
1209 --
1210 -- p_err_msg_count := FND_MSG_PUB.Count_Msg;
1211 --
1212 --
1213 -- For a hard error, one that you want to force the calling procedure
1214 -- to invoke a 'False' or 'Failure' transition:
1215 --
1216 -- p_warnings_only_flag := 'N';
1217 --
1218 --
1219 -- To display an error or warning message in the workflow notification, you
1220 -- must call the following:
1221 --
1222 -- PA_UTILS.Add_Message
1223 --
1224 -- For example, a typical call might look like the following:
1225 --
1226 -- PA_UTILS.Add_Message
1227 -- ( p_app_short_name => 'PA'
1228 -- , p_msg_name => 'PA_NO_BUDGET_RULES_ATTR'
1229 -- );
1230 -- ---------------------------------------------------------------------------------------
1231
1232 --
1233 -- WF_CORE Error Handling --------------------------------------------------
1234 -- To display errors using the WF_CORE functionality,
1235 -- the following algorithm can be used to handle known error conditions.
1236 -- When this code is used the arguments and there values will be displayed
1237 -- in the workflow monitor.
1238 --
1239 --IF <error condition>
1240 --THEN
1241 -- WF_CORE.TOKEN('ARG1', arg1);
1242 -- WF_CORE.TOKEN('ARGn', argn);
1243 -- WF_CORE.RAISE('ERROR_NAME');
1244 --END IF;
1245 -- ---------------------------------------------------------------------------------------
1246
1247 --
1248 -- Make sure to update the OUT variable for the
1249 -- message count
1250 --
1251 p_err_msg_count := FND_MSG_PUB.Count_Msg;
1252
1253
1254 EXCEPTION
1255
1256 WHEN OTHERS THEN
1257 WF_CORE.CONTEXT('PA_CLIENT_EXTN_BUDGET_WF','VERIFY_BUDGET_RULES', p_item_type, p_item_key);
1258 RAISE;
1259
1260
1261 END Verify_Budget_Rules;
1262 -- =================================================
1263
1264 END pa_client_extn_budget_wf;