[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_WF
Source
1 PACKAGE BODY pa_budget_wf AS
2 /* $Header: PAWFBUVB.pls 120.11.12020000.3 2012/07/31 04:38:12 tvala ship $ */
3
4
5 -- -------------------------------------------------------------------------------------
6 -- Globals
7 -- -------------------------------------------------------------------------------------
8
12 -- Procedures
9 G_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
10
11 -- -------------------------------------------------------------------------------------
13 -- -------------------------------------------------------------------------------------
14
15 -- ===================================================
16
17 --Name: START_BUDGET_WF
18 --Type: Procedure
19 --Description: This procedure is used to start a Budget Approval workflow.
20 --
21 --
22 --Called subprograms: PA_CLIENT_EXTN_BUDGET_WF.Start_Budget_Wf
23 -- , PA_WORKFLOW_UTILS.Insert_WF_Processes
24 --
25 --Notes:
26 -- This wrapper is called DIRECTLY from the Budgets form and the public
27 -- Baseline_Budget API.
28 --
29 -- This wrapper is also called from the Budget Integration Workflow.
30 --
31 -- Error messages in the form and public API call the 'PA_WF_CLIENT_EXTN'
32 -- error code. Two tokens are passed to the error message: the name of this
33 -- client extension and the error code.
34 --
35 --
36 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
37 -- * * * R12 MOAC Specific Notes * * *
38 --
39 -- The Budget Approval Workflow is now explicitly defined
40 -- as a SINGLE PROJECT/OU workflow.
41 --
42 -- Any procedure call for/from the Budget Approval workflow must
43 -- call the PA_BUDGET_UTILS.Set_Prj_Policy_Context procedure to
44 -- set the OU Context to the org_id for the project being
45 -- processed.
46 --
47 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
48 --
49 --
50 --History:
51 -- 08-SEP-97 jwhite Created
52 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
53 --
54 -- 03-MAY-01 jwhite - As per the Non-Project Integration
55 -- development effort, added the following as parameters
56 -- and attributes to Budget Approval Worflow:
57 -- 1. p_fck_req_flag
58 -- 2. p_bgt_intg_flag
59 --
60 -- 08-AUG-02 jwhite - Added new parameters for FP processing.
61 --
62 -- 14-JUL-05 jwhite -R12 MOAC Effort
63 -- Added calls to the new Set_Prj_Policy_Context to enforce
64 -- a single project/OU context for the Budget Approval
65 -- Worklfow nodes.
66 --
67 --
68 --
69 -- IN Parameters
70 -- p_project_id - Unique identifier for the project of the budget for which approval
71 -- is requested.
72 --
73 -- p_budget_type_code - Unique identifier for budget submitted for approval, as per the r11.5.7
74 -- Budgets model.
75 --
76 -- For the new FP model, the p_budget_type_code will be NULL!
77 --
78 -- p_mark_as_original - Yes, mark budget as original; N, do not mark. Defaults to 'N'.
79 --
80 -- OUT Parameters
81 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
82 -- x > 0, Business Rule Violated.
83 -- p_err_stage - Not used.
84 -- p_err_stack - Not used.
85
86
87 PROCEDURE Start_Budget_Wf
88 (p_draft_version_id IN NUMBER
89 , p_project_id IN NUMBER
90 , p_budget_type_code IN VARCHAR2
91 , p_mark_as_original IN VARCHAR2
92 , p_fck_req_flag IN VARCHAR2 DEFAULT NULL
93 , p_bgt_intg_flag IN VARCHAR2 DEFAULT NULL
94 , p_fin_plan_type_id IN NUMBER default NULL
95 , p_version_type IN VARCHAR2 default NULL
96 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
97 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
98 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
99 )
100 --
101 IS
102 --
103
104 -- Local Variables
105
106 l_err_code NUMBER := NULL;
107 l_item_type pa_wf_processes.item_type%TYPE;
108 l_item_key pa_wf_processes.item_key%TYPE;
109
110 -- R12 MOAC, 19-JUL-05, jwhite -------------------
111
112 l_return_status VARCHAR2(1) := NULL;
113 l_msg_count NUMBER := NULL;
114 l_msg_data VARCHAR2(2000) := NULL;
115
116
117 BEGIN
118
119 -- R12 MOAC, 14-JUL-05, jwhite -------------------
120 -- Set Single Project/OU context
121
122 PA_BUDGET_UTILS.Set_Prj_Policy_Context
123 (p_project_id => p_project_id
124 ,x_return_status => l_return_status
125 ,x_msg_count => l_msg_count
126 ,x_msg_data => l_msg_data
127 ,x_err_code => l_err_code
128 );
129
130 IF (l_err_code <> 0)
131 THEN
132 p_err_code := l_err_code;
133 RETURN;
134 END IF;
135
136 -- -----------------------------------------------
137
138
139
140
141 PA_CLIENT_EXTN_BUDGET_WF.Start_Budget_Wf
142 ( p_draft_version_id => p_draft_version_id
143 , p_project_id => p_project_id
144 , p_budget_type_code => p_budget_type_code
145 , p_mark_as_original => p_mark_as_original
146 , p_fck_req_flag => p_fck_req_flag
147 , p_bgt_intg_flag => p_bgt_intg_flag
148 , p_fin_plan_type_id => p_fin_plan_type_id
149 , p_version_type => p_version_type
150 , p_item_type => l_item_type
151 , p_item_key => l_item_key
152 , p_err_code => l_err_code
153 , p_err_stage => p_err_stage
157
154 , p_err_stack => p_err_stack
155 );
156
158 IF (l_err_code = 0)
159 THEN
160 -- Succesful! Log pa_wf_processes table for new workflow.
161
162 PA_WORKFLOW_UTILS.Insert_WF_Processes
163 (p_wf_type_code => 'BUDGET'
164 ,p_item_type => l_item_type
165 ,p_item_key => l_item_key
166 ,p_entity_key1 => to_char(p_draft_version_id)
167 ,p_description => NULL
168 ,p_err_code => p_err_code
169 ,p_err_stage => p_err_stage
170 ,p_err_stack => p_err_stack
171 );
172 ELSE
173 p_err_code := l_err_code;
174
175 END IF;
176
177
178 EXCEPTION
179
180 WHEN OTHERS
181 THEN
182 p_err_code := SQLCODE;
183 RAISE;
184
185
186 END Start_Budget_Wf;
187
188 -- =================================================
189 --
190 --Name: BUDGET_WF_IS_USED
191 --Type: Procedure
192 --Description: This procedure must return a "T" or "F" depending on whether a workflow
193 -- should be started for this particular budget.
194 --
195 --
196 --Called Subprograms: PA_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used
197 --
198 --
199 --Notes:
200 -- This wrapper is called DIRECTLY the public AMG
201 -- Baseline_Budget API.
202 --
203 -- !!! THIS WRAPPER IS NOT CALLED FROM WORKFLOW !!!
204 --
205 -- Error messages in the form and public API call the 'PA_WF_CLIENT_EXTN'
206 -- error code. Two tokens are passed to the error message: the name of this
207 -- client extension and the error code.
208 --
209 -- * * * R12 MOAC Notes:
210 -- Since this procedure is only called from AMG package
211 -- PA_BUDGET_PUB (PAPMBUPB.pls), is NOT necessary to call the
212 -- R12 PA_BUDGET_UTILS.Set_Prj_Policy_Context to set the
213 -- project/OU context.
214 --
215 --
216 --
217 --
218 --History:
219 -- 08-SEP-97 jwhite Created
220 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
221 --
222 -- 08-AUG-02 jwhite - Added new parameters for FP processing.
223 --
224 -- IN Parameters
225 -- p_project_id - Unique identifier for the project of the budget for which approval
226 -- is requested.
227 --
228 -- p_budget_type_code - Unique identifier for budget submitted for approval, as per the r11.5.7
229 -- Budgets model.
230 --
231 -- For the new FP model, the p_budget_type_code will be NULL!
232 --
233 -- p_pm_product_code - The PM vendor's product code stored in pa_budget_versions.
234 --
235 -- OUT Parameters
236 -- p_result - 'T' or 'F' (True/False)
237 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
238 -- x > 0, Business Rule Violated.
239 -- p_err_stage - Standard error message
240 -- p_err_stack - Not used.
241 --
242
243 PROCEDURE Budget_Wf_Is_Used
244 (p_draft_version_id IN NUMBER
245 , p_project_id IN NUMBER
246 , p_budget_type_code IN VARCHAR2
247 , p_pm_product_code IN VARCHAR2
248 , p_fin_plan_type_id IN NUMBER default NULL
249 , p_version_type IN VARCHAR2 default NULL
250 , p_result IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
251 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
252 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
253 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
254 )
255
256 IS
257 --
258
259 BEGIN
260
261 PA_CLIENT_EXTN_BUDGET_WF.BUDGET_WF_IS_USED
262 ( p_draft_version_id => p_draft_version_id
263 , p_project_id => p_project_id
264 , p_budget_type_code => p_budget_type_code
265 , p_pm_product_code => p_pm_product_code
266 , p_fin_plan_type_id => p_fin_plan_type_id
267 , p_version_type => p_version_type
268 , p_result => p_result
269 , p_err_code => p_err_code
270 , p_err_stage => p_err_stage
271 , p_err_stack => p_err_stack
272 );
273
274
275 EXCEPTION
276
277 WHEN OTHERS
278 THEN
279 p_err_code := SQLCODE;
280 RAISE;
281
282
283 END Budget_WF_Is_Used;
284
285 -- =================================================
286 --Name: Reject_Budget
287 --Type: Procedure
288 --Description: This procedure resets a given project-budget status
289 -- to a Working 'Rejected' status.
290 --
291 --
292 --
293 --Called subprograms: none.
294 --
295 --Notes:
296 --
297 -- * * * R12 MOAC Notes:
298 --
299 -- Technically, this procedure does NOT require the single project/OU context
300 -- to be set for the following reasons:
301 -- a) A client extension is not called.
302 -- b) The other code in this procedure does not have an OU dependency.
303 --
304 -- However, to avoid future maintenance issues, I added code to explicitly
305 -- set the single project/OU Context.
306 --
307 --
308 --
309 --History:
310 -- 22-AUG-97 jwhite - Created
311 -- 26-SEP-97 jwhite - Updated WF error processing.
312 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
313 --
314 -- 23-AUG-02 jwhite - As part of implementation of new FP model, converted this node
318 --
315 -- procedure to use draft_version_id in lieu of
316 -- project_id, budget_type_code and budget_status_code.
317 --
319 -- 19-JUL-05 jwhite -R12 MOAC Effort
320 -- Added calls to the new Set_Prj_Policy_Context to enforce
321 -- a single project/OU context for the Budget Approval
322 -- Worklfow nodes.
323 --
324 -- IN
325 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
326 -- itemkey - A string generated from the application object's primary key.
327 -- actid - The notIFication process activity(instance id).
328 -- funcmode - Run/Cancel
329 -- OUT
330 -- Resultout - NULL
331 --
332
333 PROCEDURE Reject_Budget
334 (itemtype IN VARCHAR2
335 , itemkey IN VARCHAR2
336 , actid IN NUMBER
337 , funcmode IN VARCHAR2
338 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
339 )
340 --
341 IS
342 --
343 -- ROW LOCKING
344
345 CURSOR l_lock_budget_csr (p_draft_version_id NUMBER)
346 IS
347 SELECT 'x'
348 FROM pa_budget_versions
349 WHERE budget_version_id = p_draft_version_id
350 FOR UPDATE NOWAIT;
351
352 -- Local Variables
353
354 l_draft_version_id NUMBER;
355
356 l_err_code NUMBER := 0;
357 l_msg_count NUMBER;
358 l_msg_data VARCHAR(2000);
359 l_return_status VARCHAR2(1);
360 l_data VARCHAR2(2000);
361 l_msg_index_out NUMBER;
362 l_api_version_number NUMBER := G_api_version_number;
363
364
365 --R12 MOAC, 19-JUL-05, jwhite
366 l_project_id pa_projects_all.project_id%TYPE := NULL;
367
368
369
370 --
371 BEGIN
372
373 --
374 -- Return if WF Not Running
375 --
376 IF (funcmode <> wf_engine.eng_run) THEN
377 --
378 resultout := wf_engine.eng_null;
379 RETURN;
380 --
381 END IF;
382 --
383
384 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
385
386
387 l_draft_version_id := wf_engine.GetItemAttrNumber(
388 itemtype => itemtype,
389 itemkey => itemkey,
390 aname => 'DRAFT_VERSION_ID'
391 );
392
393 -- R12 MOAC, 19-JUL-05, jwhite
394 -- Project_id Needed for subsequent procedure call
395 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'PROJECT_ID' );
398
399 /*Commented for bug 5233870*/
400 -- SET GLOBALS ------------------------------------------------------------------
401
402 -- Based on the Responsibility, Intialize the Application
403 -- Un Commented for bug 8464143.
404 PA_WORKFLOW_UTILS.Set_Global_Attr
405 (p_item_type => itemtype
406 , p_item_key => itemkey
407 , p_err_code => l_err_code);
408
409
410 -- R12 MOAC, 19-JUL-05, jwhite -------------------
411 -- Set Single Project/OU context
412
413 PA_BUDGET_UTILS.Set_Prj_Policy_Context
414 (p_project_id => l_project_id
415 ,x_return_status => l_return_status
416 ,x_msg_count => l_msg_count
417 ,x_msg_data => l_msg_data
418 ,x_err_code => l_err_code
419 );
420
421 IF (l_err_code <> 0)
422 THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425
426 -- -----------------------------------------------
427
428
429
430 -- REVERT STATUS of Project-Budget to 'Working' , 'REJECTED' -----------------
431
432 -- LOCK Draft Budget Version
433
434 OPEN l_lock_budget_csr(l_draft_version_id);
435 CLOSE l_lock_budget_csr;
436
437 -- UPDATE Draft Budget Version
438
439 UPDATE pa_budget_versions
440 SET budget_status_code = 'W'
441 , WF_status_code = 'REJECTED'
442 WHERE budget_version_id = l_draft_version_id;
443
444
445 --
446 resultout := wf_engine.eng_completed;
447 --
448
449 EXCEPTION
450
451 WHEN FND_API.G_EXC_ERROR
452 THEN
453 WF_CORE.CONTEXT('PA_BUDGET_WF','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
454 RAISE;
455
456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
457 THEN
458 WF_CORE.CONTEXT('PA_BUDGET_WF','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
459 RAISE;
460
461 WHEN OTHERS
462 THEN
463 WF_CORE.CONTEXT('PA_BUDGET_WF','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
464 RAISE;
465
466
467
468 END Reject_Budget;
469
470 -- =================================================
471 --Name: Select_Budget_Approver
472 --Type: Procedure
473 --Description: This procedure will call a client extension that will return the
474 -- correct ID of the person that must approve a budget
475 -- for baselining.
476 --
477 --
478 --Called subprograms: PA_CLIENT_EXTN_BUDGET_WF.select_budget_approver
479 --
480 -- * * * R12 MOAC Notes:
481 --
482 -- This procedure requires a single project/OU context.
483 --
484 --
485 --
486 --History:
490 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
487 -- 28-FEB-97 L. de Werker - Created
488 -- 24-JUN-97 jwhite - Updated to latest specs
489 -- 26-SEP-97 jwhite - Updated WF error processing.
491 --
492 -- 23-AUG-02 jwhite - Adapted to FP Model.
493 --
494 --
495 -- 19-JUL-05 jwhite -R12 MOAC Effort
496 -- Added calls to the new Set_Prj_Policy_Context to enforce
497 -- a single project/OU context for the Budget Approval
498 -- Worklfow nodes.
499 --
500 -- IN
501 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
502 -- itemkey - A string generated from the application object's primary key.
503 -- actid - The notIFication process activity(instance id).
504 -- funcmode - Run/Cancel
505 -- OUT
506 -- Resultout - T/F
507 --
508 PROCEDURE Select_Budget_Approver
509 (itemtype IN VARCHAR2
510 , itemkey IN VARCHAR2
511 , actid IN NUMBER
512 , funcmode IN VARCHAR2
513 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
514 )
515
516 IS
517
518 CURSOR l_baseliner_user_csr( p_baseliner_id NUMBER )
519 IS
520 SELECT f.user_id
521 , f.user_name
522 , e.first_name||' '||e.last_name
523 FROM fnd_user f
524 , pa_employees e
525 WHERE f.employee_id = p_baseliner_id
526 AND f.employee_id = e.person_id
527 AND TRUNC(SYSDATE) BETWEEN f.start_date AND nvl(f.end_date, TRUNC(SYSDATE)+1); --Added : 7688624
528
529 l_workflow_started_by_id NUMBER;
530 l_project_id NUMBER;
531 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
532
533 l_baseliner_employee_id NUMBER;
534
535 l_baseliner_user_id NUMBER;
536 l_baseliner_user_name VARCHAR2(100);
537 l_baseliner_full_name VARCHAR2(400);
538
539 l_err_code NUMBER := 0;
540 l_msg_count NUMBER;
541 l_msg_data VARCHAR(2000);
542 l_return_status VARCHAR2(1);
543 l_data VARCHAR2(2000);
544 l_msg_index_out NUMBER;
545 l_api_version_number NUMBER := G_api_version_number;
546
547
548 l_fin_plan_type_id NUMBER := NULL;
549 l_version_type pa_budget_versions.version_type%TYPE := NULL;
550 l_draft_version_id NUMBER := NULL;
551
552 l_approver_role varchar2(50); -- Bug 6994708
553
554 BEGIN
555 --
556 -- Return if WF Not Running
557 --
558 IF (funcmode <> wf_engine.eng_run) THEN
559 --
560 resultout := wf_engine.eng_null;
561 RETURN;
562 --
563 END IF;
564 --
565
566 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
567
568 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'PROJECT_ID' );
571
572 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'WORKFLOW_STARTED_BY_ID' );
575
576 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
577 itemkey => itemkey,
578 aname => 'BUDGET_TYPE_CODE' );
579
580
581 l_draft_version_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
582 itemkey => itemkey,
583 aname => 'DRAFT_VERSION_ID' );
584
585 l_fin_plan_type_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
586 itemkey => itemkey,
587 aname => 'FIN_PLAN_TYPE_ID' );
588
589 l_version_type := wf_engine.GetItemAttrText(itemtype => itemtype,
590 itemkey => itemkey,
591 aname => 'VERSION_TYPE' );
592
593
594
595
596 /*Commented for bug 5233870*/
597 -- SET GLOBALS ------------------------------------------------------------------
598
599 -- Based on the Responsibility, Intialize the Application
600 -- Un Commented for bug 8464143.
601 PA_WORKFLOW_UTILS.Set_Global_Attr
602 (p_item_type => itemtype
603 , p_item_key => itemkey
604 , p_err_code => l_err_code
605 );
606
607
608 -- R12 MOAC, 19-JUL-05, jwhite -------------------
609 -- Set Single Project/OU context
610
611 PA_BUDGET_UTILS.Set_Prj_Policy_Context
612 (p_project_id => l_project_id
613 ,x_return_status => l_return_status
614 ,x_msg_count => l_msg_count
615 ,x_msg_data => l_msg_data
616 ,x_err_code => l_err_code
617 );
618
619 IF (l_err_code <> 0)
620 THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
622 END IF;
623
624 -- -----------------------------------------------
625
626
627 PA_CLIENT_EXTN_BUDGET_WF.Select_Budget_Approver
628 (p_item_type => itemtype
629 ,p_item_key => itemkey
630 ,p_project_id => l_project_id
631 ,p_budget_type_code => l_budget_type_code
632 ,p_workflow_started_by_id => l_workflow_started_by_id
633 ,p_fin_plan_type_id => l_fin_plan_type_id
634 ,p_version_type => l_version_type
635 ,p_draft_version_id => l_draft_version_id
636 ,p_budget_baseliner_id => l_baseliner_employee_id
637 );
638
639
643
640 --ISSUE: a employee can have several users attached to it. So, this
641 -- Code Retrieves the First User.
642
644 IF (l_baseliner_employee_id IS NOT NULL)
645 THEN
646
647 OPEN l_baseliner_user_csr( l_baseliner_employee_id );
648
649 FETCH l_baseliner_user_csr
650 INTO l_baseliner_user_id
651 ,l_baseliner_user_name
652 ,l_baseliner_full_name;
653
654 IF (l_baseliner_user_csr%FOUND)
655 THEN
656 CLOSE l_baseliner_user_csr;
657
658 wf_engine.SetItemAttrNumber
659 (itemtype => itemtype,
660 itemkey => itemkey,
661 aname => 'BUDGET_BASELINER_ID',
662 avalue => l_baseliner_user_id );
663
664 wf_engine.SetItemAttrText
665 (itemtype => itemtype,
666 itemkey => itemkey,
667 aname => 'BUDGET_BASELINER_NAME',
668 avalue => l_baseliner_user_name);
669
670 wf_engine.SetItemAttrText
671 (itemtype => itemtype,
672 itemkey => itemkey,
673 aname => 'BUDGET_BASELINER_FULL_NAME',
674 avalue => l_baseliner_full_name);
675
676 --Bug 6994708
677 -- For Reminder Notification From will be always the Baseliner
678
679 -- bug 9767688: suffix with variable so that the role is never duplicate.
680 l_approver_role := 'APPR_' ||itemtype || itemkey || to_char(sysdate, 'Jsssss');
681
682 WF_DIRECTORY.CreateAdHocRole( role_name => l_approver_role
683 , role_display_name => l_baseliner_full_name
684 , expiration_date => sysdate+1
685 );
686
687 wf_engine.SetItemAttrText
688 (itemtype => itemtype,
689 itemkey => itemkey,
690 aname => '#FROM_ROLE',
691 avalue => l_approver_role );
692
693 --Bug 6994708
694
695 resultout := wf_engine.eng_completed||':'||'T';
696 ELSE
697
698 CLOSE l_baseliner_user_csr;
699 resultout := wf_engine.eng_completed||':'||'F';
700 END IF;
701 ELSE
702
703 resultout := wf_engine.eng_completed||':'||'F';
704 END IF;
705
706
707 EXCEPTION
708
709 WHEN FND_API.G_EXC_ERROR
710 THEN
711 WF_CORE.CONTEXT('PA_BUDGET_WF','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
712 RAISE;
713
714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
715 THEN
716 WF_CORE.CONTEXT('PA_BUDGET_WF','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
717 RAISE;
718
719 WHEN OTHERS
720 THEN
721 WF_CORE.CONTEXT('PA_BUDGET_WF','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
722 RAISE;
723
724
725 END Select_Budget_Approver;
726
727
728 -- ==================================================
729
730 --Name Verify_Budget_Rules
731 --Type: Procedure
732 --Description: This procedure will call a client extension that will return a
733 -- 'T' or 'F', depending on whether all defined rules were met.
734 --
735 --
736 --Called subprograms: PA_BUDGET_UTILS.Verify_Budget_Rules
737 --PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
738 --
739 --
740 --Notes:
741 --
742 -- * * * R12 MOAC Notes:
743 -- This procedure requires a single project/OU context.
744 --
745 --
746 --History:
747 -- 28-FEB-97 L. de Werker - Created
748 -- 24-JUN-97 jwhite - Updated to latest specs
749 -- 09-SEP-97 jwhite - Updated to latest specs
750 -- 26-SEP-97 jwhite - Updated WF error processing.
751 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
752 --
753 -- 03-MAY-01 jwhite - As per the Non-Project Integration
754 -- development effort, referenced the following
755 -- attributes:
756 -- 1. bgt_intg_flag
757 --
758 -- 23-AUG-02 jwhite - Adapted to the new FP model.
759 --
760 -- 19-JUL-05 jwhite -R12 MOAC Effort
761 -- Added calls to the new Set_Prj_Policy_Context to enforce
762 -- a single project/OU context for the Budget Approval
763 -- Workflow nodes.
764 --
765 --
766 -- IN
767 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
768 -- itemkey - A string generated from the application object's primary key.
769 -- actid - The notIFication process activity(instance id).
770 -- funcmode - Run/Cancel
771 -- OUT
772 -- Resultout - T/F
773 --
774 PROCEDURE Verify_Budget_Rules
775 ( itemtype in varchar2
776 , itemkey in varchar2
777 , actid in number
778 , funcmode in varchar2
779 , resultout out NOCOPY varchar2 --File.Sql.39 bug 4440895
780 )
781
782 IS
783 --
784
785
786
787 -- Local Variables
788 l_workflow_started_by_id NUMBER;
789 l_project_id NUMBER;
790 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
791
792
793 l_warnings_only_flag VARCHAR2(1);
794 l_warnings_only VARCHAR2(1) := 'Y';
795 l_err_msg_count NUMBER := 0;
796 l_mark_as_original pa_budget_versions.current_original_flag%TYPE;
800 l_msg_count NUMBER;
797 l_resource_list_id NUMBER;
798 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
799
801 l_msg_data VARCHAR(2000);
802 l_return_status VARCHAR2(1);
803 l_data VARCHAR2(2000);
804 l_msg_index_out NUMBER;
805 l_api_version_number NUMBER := G_api_version_number;
806
807 l_err_code NUMBER := 0;
808 l_err_stage VARCHAR2(120);
809 l_err_stack VARCHAR2(630);
810
811 l_bgt_intg_flag VARCHAR2(1) := NULL;
812
813 l_fin_plan_type_id NUMBER := NULL;
814 l_version_type pa_budget_versions.version_type%TYPE := NULL;
815 l_draft_version_id NUMBER := NULL;
816
817
818 --
819 BEGIN
820 --
821 -- Return if WF Not Running
822 --
823 IF (funcmode <> wf_engine.eng_run) THEN
824 --
825 resultout := wf_engine.eng_null;
826 RETURN;
827 --
828 END IF;
829 --
830
831 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
832
833 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'PROJECT_ID' );
836
837 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'WORKFLOW_STARTED_BY_ID' );
840
841 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'BUDGET_TYPE_CODE' );
844
845 l_mark_as_original := wf_engine.GetItemAttrText(itemtype => itemtype,
846 itemkey => itemkey,
847 aname => 'MARK_AS_ORIGINAL' );
848
849 l_resource_list_id := wf_engine.GetItemAttrText(itemtype => itemtype,
850 itemkey => itemkey,
851 aname =>'RESOURCE_LIST_ID');
852
853 l_project_type_class_code := wf_engine.GetItemAttrText(itemtype => itemtype,
854 itemkey => itemkey,
855 aname =>'PROJECT_TYPE_CLASS_CODE');
856
857 l_bgt_intg_flag := wf_engine.GetItemAttrText(itemtype => itemtype,
858 itemkey => itemkey,
859 aname =>'BGT_INTG_FLAG');
860
861
862 l_draft_version_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
863 itemkey => itemkey,
864 aname => 'DRAFT_VERSION_ID' );
865
866 l_fin_plan_type_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
867 itemkey => itemkey,
868 aname => 'FIN_PLAN_TYPE_ID' );
869
870 l_version_type := wf_engine.GetItemAttrText(itemtype => itemtype,
871 itemkey => itemkey,
872 aname => 'VERSION_TYPE' );
873
874
875
876
877
878 -- SET GLOBALS -----------------------------------------------------------------
879
880 -- Based on the Responsibility, Intialize the Application
881 /*Commented for bug 5233870*/
882 -- Un Commented for bug 8464143.
883 PA_WORKFLOW_UTILS.Set_Global_Attr
884 (p_item_type => itemtype
885 , p_item_key => itemkey
886 , p_err_code => l_err_code);
887
888
889 -- R12 MOAC, 19-JUL-05, jwhite -------------------
890 -- Set Single Project/OU context
891
892 PA_BUDGET_UTILS.Set_Prj_Policy_Context
893 (p_project_id => l_project_id
894 ,x_return_status => l_return_status
895 ,x_msg_count => l_msg_count
896 ,x_msg_data => l_msg_data
897 ,x_err_code => l_err_code
898 );
899
900 IF (l_err_code <> 0)
901 THEN
902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903 END IF;
904
905 -- -----------------------------------------------
906
907
908
909 -- Populate Package Global for Conditional Budget Integration Processing in the
910 -- Verify_Budget_Rules Procedures.
911 PA_BUDGET_UTILS.G_Bgt_Intg_Flag := l_bgt_intg_flag;
912
913 -- ------------------------------------------------------------------------------------
914 -- NON-WF Verify Budget Rules
915 -- ------------------------------------------------------------------------------------
916
917 -- Edits Here Obsoleted by the FP Model Dev Effort.
918
919
920 -- SUBMISSION RULES -------------------------------------------------------------
921 --dbms_output.put_line('Verify Budget Rules - SUBMIT');
922
923
924 PA_BUDGET_UTILS.VERIFY_BUDGET_RULES
925 (p_draft_version_id => l_draft_version_id
926 , p_mark_as_original => l_mark_as_original
927 , p_event => 'SUBMIT'
928 , p_project_id => l_project_id
929 , p_budget_type_code => l_budget_type_code
930 , p_resource_list_id => l_resource_list_id
931 , p_project_type_class_code => l_project_type_class_code
932 , p_created_by => l_workflow_started_by_id
933 , p_calling_module => 'PAWFBUVB'
934 , p_fin_plan_type_id => l_fin_plan_type_id
935 , p_version_type => l_version_type
936 , p_warnings_only_flag => l_warnings_only_flag
937 , p_err_msg_count => l_err_msg_count
938 , p_err_code => l_err_code
939 , p_err_stage => l_err_stage
940 , p_err_stack => l_err_stack
941 );
942
943 IF (l_err_msg_count > 0 )
944 THEN
945 PA_WORKFLOW_UTILS.Set_Notification_Messages
946 (p_item_type => itemtype
950 l_warnings_only := 'N';
947 , p_item_key => itemkey
948 );
949 IF (l_warnings_only_flag = 'N') THEN
951 END IF;
952 END IF;
953
954
955
956 -- BASELINE RULES -------------------------------------------------------------
957 PA_BUDGET_UTILS.VERIFY_BUDGET_RULES
958 (p_draft_version_id => l_draft_version_id
959 , p_mark_as_original => l_mark_as_original
960 , p_event => 'BASELINE'
961 , p_project_id => l_project_id
962 , p_budget_type_code => l_budget_type_code
963 , p_resource_list_id => l_resource_list_id
964 , p_project_type_class_code => l_project_type_class_code
965 , p_created_by => l_workflow_started_by_id
966 , p_calling_module => 'PAWFBUVB'
967 , p_fin_plan_type_id => l_fin_plan_type_id
968 , p_version_type => l_version_type
969 , p_warnings_only_flag => l_warnings_only_flag
970 , p_err_msg_count => l_err_msg_count
971 , p_err_code => l_err_code
972 , p_err_stage => l_err_stage
973 , p_err_stack => l_err_stack
974 );
975
976
977
978 IF (l_err_msg_count > 0 )
979 THEN
980 PA_WORKFLOW_UTILS.Set_Notification_Messages
981 (p_item_type => itemtype
982 , p_item_key => itemkey
983 );
984 IF (l_warnings_only_flag = 'N') THEN
985 l_warnings_only := 'N';
986 END IF;
987 END IF;
988
989
990 -- ------------------------------------------------------------------------------------
991 -- WORKFLOW Verify Budget Rules
992 -- ------------------------------------------------------------------------------------
993
994 PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
995 (p_item_type => itemtype
996 , p_item_key => itemkey
997 , p_project_id => l_project_id
998 , p_budget_type_code => l_budget_type_code
999 , p_workflow_started_by_id => l_workflow_started_by_id
1000 , p_event => 'SUBMIT'
1001 , p_fin_plan_type_id => l_fin_plan_type_id
1002 , p_version_type => l_version_type
1003 , p_warnings_only_flag => l_warnings_only_flag
1004 , p_err_msg_count => l_err_msg_count
1005 );
1006
1007
1008 IF (l_err_msg_count > 0 )
1009 THEN
1010 PA_WORKFLOW_UTILS.Set_Notification_Messages
1011 (p_item_type => itemtype
1012 , p_item_key => itemkey
1013 );
1014 IF (l_warnings_only_flag = 'N') THEN
1015 l_warnings_only := 'N';
1016 END IF;
1017 END IF;
1018
1019 PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1020 (p_item_type => itemtype
1021 , p_item_key => itemkey
1022 , p_project_id => l_project_id
1023 , p_budget_type_code => l_budget_type_code
1024 , p_workflow_started_by_id => l_workflow_started_by_id
1025 , p_event => 'BASELINE'
1026 , p_fin_plan_type_id => l_fin_plan_type_id
1027 , p_version_type => l_version_type
1028 , p_warnings_only_flag => l_warnings_only_flag
1029 , p_err_msg_count => l_err_msg_count
1030 );
1031
1032
1033 IF (l_err_msg_count > 0 )
1034 THEN
1035 PA_WORKFLOW_UTILS.Set_Notification_Messages
1036 (p_item_type => itemtype
1037 , p_item_key => itemkey
1038 );
1039 IF (l_warnings_only_flag = 'N') THEN
1040 l_warnings_only := 'N';
1041 END IF;
1042 END IF;
1043
1044
1045 IF (l_warnings_only = 'Y')
1046 THEN
1047 resultout := wf_engine.eng_completed||':'||'T';
1048 ELSE
1049 resultout := wf_engine.eng_completed||':'||'F';
1050 END IF;
1051
1052 --
1053
1054 EXCEPTION
1055 WHEN FND_API.G_EXC_ERROR
1056 THEN
1057 WF_CORE.CONTEXT('PA_BUDGET_WF','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1058 RAISE;
1059
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1061 THEN
1062 WF_CORE.CONTEXT('PA_BUDGET_WF','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1063 RAISE;
1064
1065 WHEN OTHERS THEN
1066 WF_CORE.CONTEXT('PA_BUDGET_WF','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1067 RAISE;
1068
1069
1070 END Verify_Budget_Rules;
1071
1072 -- ==================================================###
1073 --Name: Baseline_Budget
1074 --Type: Procedure
1075 --Description: This procedures performs BASELINE verification,
1076 -- baseline functionality via the core baseline
1077 -- procedure, and directly updates the draft budget.
1078 --
1079 --
1080 --
1081 --
1082 --Called subprograms: PA_BUDGET_UTILS.Verify_Budget_Rules
1083 -- , PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1084 -- , PA_BUDGET_UTILS.Baseline_Budget
1085 -- , PA_WORKFLOW_UTILS.Insert_WF_Processes
1086 --
1087 --
1088 --Notes:
1089 --
1090 -- * * * R12 MOAC Notes:
1091 -- This procedure requires a single project/OU context.
1092 --
1093 --
1094 --History:
1095 -- 28-FEB-1997 L. de Werker - Created
1096 -- 24-JUN-97 jwhite - Updated
1097 -- 23-AUG-97 jwhite - Updated to latest specs.
1098 -- 09-SEP-97 jwhite - Updated to lastest specs.
1099 -- 17-SEP-97 jwhite - Added Insert_WF_Processes to
1100 -- Baseline_Budget procedure.
1101 -- 26-SEP-97 jwhite - Updated WF error processing.
1102 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
1103 --
1104 -- 08-MAY-01 jwhite - As per the Non-Project Integration
1105 -- development effort, substituted the core
1109 -- 23-AUG-02 jwhite - Adapted to new FP model.
1106 -- baseline procedure with the new
1107 -- wrapper baseline_budget procedure.
1108 --
1110 --
1111 -- 16-OCT-02 jwhite - Oops!
1112 -- For BASELINE_BUDGET api, added conditional call logic for
1113 -- the FP Baseline procedure.
1114 --
1115 -- 23-AUG-05 jwhite - R12 SLA Effort, Phase II
1116 -- 1) Changed logic to test for FND_API.G_RET_STS_SUCCESS
1117 -- 2) Populate Notifcation messages for successful budget integration
1118 --
1119 --
1120 -- IN
1121 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1122 -- itemkey - A string generated from the application object's primary key.
1123 -- actid - The notIFication process activity(instance id).
1124 -- funcmode - Run/Cancel
1125 -- OUT
1126 -- Resultout - T/F
1127 --
1128 --
1129 --
1130 PROCEDURE Baseline_Budget
1131 (itemtype IN VARCHAR2
1132 , itemkey IN VARCHAR2
1133 , actid IN NUMBER
1134 , funcmode IN VARCHAR2
1135 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1136 )
1137
1138 IS
1139
1140
1141
1142 -- R11.5.7 Budgets Model -----------------------
1143 CURSOR l_baseline_csr
1144 ( p_project_id NUMBER
1145 , p_budget_type_code VARCHAR2 )
1146
1147 IS
1148 SELECT MAX(budget_version_id)
1149 FROM pa_budget_versions
1150 WHERE project_id = p_project_id
1151 AND budget_type_code = p_budget_type_code
1152 AND budget_status_code = 'B';
1153
1154
1155 -- FP Plan Model -----------------------
1156
1157 -- Note: This cursor can have NO_DATA_FOUND
1158 CURSOR l_fp_baseline_csr ( p_project_id NUMBER
1159 , p_fin_plan_type_id NUMBER
1160 , p_version_type VARCHAR2)
1161 IS
1162 SELECT budget_version_id
1163 , RECORD_VERSION_NUMBER
1164 FROM pa_budget_versions
1165 WHERE project_id = p_project_id
1166 AND current_flag = 'Y'
1167 and fin_plan_type_id = p_fin_plan_type_id
1168 and version_type = p_version_type;
1169
1170
1171 CURSOR l_fp_draft_csr ( p_draft_version_id NUMBER)
1172 IS
1173 SELECT RECORD_VERSION_NUMBER
1174 FROM pa_budget_versions
1175 WHERE budget_version_id = p_draft_version_id;
1176
1177 CURSOR l_get_baseliner_userid_csr( p_item_key VARCHAR2)
1178 IS
1179 SELECT f.user_id
1180 FROM fnd_user f
1181 WHERE f.user_name = (select assigned_user FROM (SELECT assigned_user
1182 FROM wf_item_activity_statuses
1183 WHERE item_key = p_item_key
1184 AND activity_result_code = 'APPROVED'
1185 AND assigned_user IS NOT NULL
1186 order by process_activity desc) WHERE rownum <2)
1187 AND TRUNC(SYSDATE) BETWEEN f.start_date AND nvl(f.end_date, TRUNC(SYSDATE)+1); --Bug # 14358128
1188
1189 CURSOR l_baseliner_user_details_csr( p_baseliner_id NUMBER )
1190 IS
1191 SELECT f.user_name
1192 ,e.first_name||' '||e.last_name
1193 FROM fnd_user f
1194 , pa_employees e
1195 WHERE f.user_id = p_baseliner_id
1196 AND f.employee_id = e.person_id
1197 AND TRUNC(SYSDATE) BETWEEN f.start_date AND nvl(f.end_date, TRUNC(SYSDATE)+1); --Added : Bug #14005015
1198
1199
1200
1201 -- Local Variable Declaration ---------------
1202
1203 l_workflow_started_by_id NUMBER;
1204 l_baseliner_id NUMBER;
1205 l_project_id NUMBER;
1206 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1207
1208 l_row_found NUMBER;
1209 l_baselined_version_id NUMBER;
1210
1211 l_api_version_number NUMBER := G_api_version_number;
1212 l_msg_count NUMBER;
1213 l_msg_data VARCHAR(2000);
1214 l_return_status VARCHAR2(1);
1215 l_pm_product_code pa_projects.pm_product_code%TYPE :='WORKFLOW';
1216 l_data VARCHAR2(2000);
1217 l_msg_index_out NUMBER;
1218
1219
1220 l_warnings_only_flag VARCHAR2(1);
1221 l_warnings_only VARCHAR2(1) := 'Y';
1222 l_err_msg_count NUMBER;
1223 l_mark_as_original pa_budget_versions.current_original_flag%TYPE;
1224 l_resource_list_id NUMBER;
1225 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
1226
1227 l_err_code NUMBER := 0;
1228 l_err_stage VARCHAR2(120) := NULL;
1229 l_err_stack VARCHAR2(630);
1230
1231 l_bgt_intg_flag VARCHAR2(1) := NULL;
1232 l_fck_req_flag VARCHAR2(1) := NULL;
1233
1234 l_fin_plan_type_id NUMBER := NULL;
1235 l_version_type pa_budget_versions.version_type%TYPE := NULL;
1236 l_draft_version_id NUMBER := NULL;
1237 l_baselined_record_number NUMBER := NULL;
1238 l_draft_record_number NUMBER := NULL;
1239 l_fc_version_created_flag VARCHAR2(1);
1240 l_resp_id NUMBER ; -- Bug 8464143
1241
1242
1243 l_baseliner_user_name VARCHAR2(100); --Added : Bug #14005015
1244 l_baseliner_full_name VARCHAR2(400);
1245 l_baseliner_role VARCHAR2(50);
1246
1247
1248 BEGIN
1249
1250
1251 --
1252 -- Return if WF Not Running
1253 --
1254 IF (funcmode <> wf_engine.eng_run) THEN
1255 --
1259 END IF;
1256 resultout := wf_engine.eng_null;
1257 RETURN;
1258 --
1260 --
1261
1262
1263 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
1264
1265 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1266 itemkey => itemkey,
1267 aname => 'PROJECT_ID' );
1268
1269
1270 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1271 itemkey => itemkey,
1272 aname => 'BUDGET_TYPE_CODE' );
1273
1274 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1275 itemkey => itemkey,
1276 aname => 'WORKFLOW_STARTED_BY_ID' );
1277
1278
1279 l_baseliner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1280 itemkey => itemkey,
1281 aname => 'BUDGET_BASELINER_ID' );
1282
1283 l_mark_as_original := wf_engine.GetItemAttrText(itemtype => itemtype,
1284 itemkey => itemkey,
1285 aname => 'MARK_AS_ORIGINAL' );
1286
1287 l_resource_list_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1288 itemkey => itemkey,
1289 aname =>'RESOURCE_LIST_ID');
1290
1291 l_project_type_class_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1292 itemkey => itemkey,
1293 aname => 'PROJECT_TYPE_CLASS_CODE');
1294
1295 l_bgt_intg_flag := wf_engine.GetItemAttrText(itemtype => itemtype,
1296 itemkey => itemkey,
1297 aname =>'BGT_INTG_FLAG');
1298
1299 l_fck_req_flag := wf_engine.GetItemAttrText(itemtype => itemtype,
1300 itemkey => itemkey,
1301 aname =>'FCK_REQ_FLAG');
1302
1303
1304
1305 l_draft_version_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1306 itemkey => itemkey,
1307 aname => 'DRAFT_VERSION_ID' );
1308
1309 l_fin_plan_type_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1310 itemkey => itemkey,
1311 aname => 'FIN_PLAN_TYPE_ID' );
1312
1313 l_version_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1314 itemkey => itemkey,
1315 aname => 'VERSION_TYPE' );
1316
1317
1318
1319 --Bug 2162949
1320 G_baselined_by_user_id :=l_baseliner_id;
1321
1322 -- SET GLOBALS -----------------------------------------------------------------
1323 /*Commented for bug 5233870
1324 -- Based on the Responsibility, Intialize the Application
1325 PA_WORKFLOW_UTILS.Set_Global_Attr
1326 (p_item_type => itemtype
1327 , p_item_key => itemkey
1328 , p_err_code => l_err_code);
1329 */
1330
1331 -- Bug 8464143
1332 l_resp_id := wf_engine.GetItemAttrNumber
1333 (itemtype => itemtype,
1334 itemkey => itemkey,
1335 aname => 'RESPONSIBILITY_ID' );
1336
1337 --Bug #13720732
1338 OPEN l_get_baseliner_userid_csr(itemkey);
1339 FETCH l_get_baseliner_userid_csr INTO l_baseliner_id;
1340 CLOSE l_get_baseliner_userid_csr;
1341 G_baselined_by_user_id :=l_baseliner_id;
1342 --Bug #13720732
1343
1344 --Added : Bug #14005015
1345
1346 IF (l_baseliner_id IS NOT NULL)
1347 THEN
1348 OPEN l_baseliner_user_details_csr( l_baseliner_id );
1349
1350 FETCH l_baseliner_user_details_csr
1351 INTO l_baseliner_user_name
1352 ,l_baseliner_full_name;
1353
1354 IF (l_baseliner_user_details_csr%FOUND)
1355 THEN
1356 CLOSE l_baseliner_user_details_csr;
1357
1358 wf_engine.SetItemAttrNumber
1359 (itemtype => itemtype,
1360 itemkey => itemkey,
1361 aname => 'BUDGET_BASELINER_ID',
1362 avalue => l_baseliner_id );
1363
1364 wf_engine.SetItemAttrText
1365 (itemtype => itemtype,
1366 itemkey => itemkey,
1367 aname => 'BUDGET_BASELINER_NAME',
1368 avalue => l_baseliner_user_name);
1369
1370 wf_engine.SetItemAttrText
1371 (itemtype => itemtype,
1372 itemkey => itemkey,
1373 aname => 'BUDGET_BASELINER_FULL_NAME',
1374 avalue => l_baseliner_full_name);
1375
1376 l_baseliner_role := 'APPR_' ||itemtype || itemkey || to_char(sysdate, 'FMMonth DD, YYYY');
1377
1378 WF_DIRECTORY.CreateAdHocRole( role_name => l_baseliner_role
1379 , role_display_name => l_baseliner_full_name
1380 , expiration_date => sysdate+1
1381 );
1382
1383 wf_engine.SetItemAttrText
1384 (itemtype => itemtype,
1385 itemkey => itemkey,
1386 aname => '#FROM_ROLE',
1387 avalue => l_baseliner_role );
1388
1389 ELSE
1390 CLOSE l_baseliner_user_details_csr;
1391 END IF;
1392 END IF;
1393 --Added : Bug #14005015
1394
1395 IF l_baseliner_id is NOT NULL THEN
1396 FND_GLOBAL.Apps_Initialize
1397 ( user_id => l_baseliner_id
1398 , resp_id => l_resp_id
1399 , resp_appl_id => pa_workflow_utils.get_application_id(l_resp_id)
1400 );
1401 ELSE
1405 END IF;
1402 Pa_workflow_utils.Set_Global_Attr (p_item_type => itemtype,
1403 p_item_key => itemkey,
1404 p_err_code => l_err_code);
1406 -- End Bug 8464143
1407
1408
1409 -- R12 MOAC, 19-JUL-05, jwhite -------------------
1410 -- Set Single Project/OU context
1411
1412 PA_BUDGET_UTILS.Set_Prj_Policy_Context
1413 (p_project_id => l_project_id
1414 ,x_return_status => l_return_status
1415 ,x_msg_count => l_msg_count
1416 ,x_msg_data => l_msg_data
1417 ,x_err_code => l_err_code
1418 );
1419
1420 IF (l_err_code <> 0)
1421 THEN
1422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1423 END IF;
1424
1425 -- -----------------------------------------------
1426
1427
1428
1429 -- Populate Package Global for Conditional Budget Integration Processing in the
1430 -- Verify_Budget_Rules Procedures.
1431 PA_BUDGET_UTILS.G_Bgt_Intg_Flag := l_bgt_intg_flag;
1432
1433
1434
1435
1436 -- ------------------------------------------------------------------------------------
1437 -- NON-WF Verify Budget Rules
1438 -- ------------------------------------------------------------------------------------
1439
1440 PA_BUDGET_UTILS.VERIFY_BUDGET_RULES
1441 (p_draft_version_id => l_draft_version_id
1442 , p_mark_as_original => l_mark_as_original
1443 , p_event => 'BASELINE'
1444 , p_project_id => l_project_id
1445 , p_budget_type_code => l_budget_type_code
1446 , p_resource_list_id => l_resource_list_id
1447 , p_project_type_class_code => l_project_type_class_code
1448 , p_created_by => l_workflow_started_by_id
1449 , p_calling_module => 'PAWFBUVB'
1450 , p_fin_plan_type_id => l_fin_plan_type_id
1451 , p_version_type => l_version_type
1452 , p_warnings_only_flag => l_warnings_only_flag
1453 , p_err_msg_count => l_err_msg_count
1454 , p_err_code => l_err_code
1455 , p_err_stage => l_err_stage
1456 , p_err_stack => l_err_stack
1457 );
1458
1459
1460
1461 IF (l_err_msg_count > 0 )
1462 THEN
1463 PA_WORKFLOW_UTILS.Set_Notification_Messages
1464 (p_item_type => itemtype
1465 , p_item_key => itemkey
1466 );
1467 IF (l_warnings_only_flag = 'N') THEN
1468 l_warnings_only := 'N';
1469 END IF;
1470 END IF;
1471
1472
1473 -- ------------------------------------------------------------------------------------
1474 -- WORKFLOW Verify Budget Rules
1475 -- ------------------------------------------------------------------------------------
1476
1477
1478 PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1479 (p_item_type => itemtype
1480 , p_item_key => itemkey
1481 , p_project_id => l_project_id
1482 , p_budget_type_code => l_budget_type_code
1483 , p_workflow_started_by_id => l_workflow_started_by_id
1484 , p_event => 'BASELINE'
1485 , p_fin_plan_type_id => l_fin_plan_type_id
1486 , p_version_type => l_version_type
1487 , p_warnings_only_flag => l_warnings_only_flag
1488 , p_err_msg_count => l_err_msg_count
1489 );
1490
1491
1492 IF (l_err_msg_count > 0 )
1493 THEN
1494 PA_WORKFLOW_UTILS.Set_Notification_Messages
1495 (p_item_type => itemtype
1496 , p_item_key => itemkey
1497 );
1498 IF (l_warnings_only_flag = 'N') THEN
1499 l_warnings_only := 'N';
1500 END IF;
1501 END IF;
1502
1503 -- ---------------------------------------------------------------------------------------
1504 -- BASELINE THIS BUDGET VERSION if only warnings
1505 -- Make sure verify budget rules NOT called again:
1506 -- x_verify_budget_rules => 'N'
1507 -- ---------------------------------------------------------------------------------------
1508
1509 IF (l_warnings_only = 'Y')
1510 THEN
1511
1512
1513 IF (l_budget_type_code IS NULL)
1514 THEN
1515
1516 -- This is a FINANCIAL PLAN Model Entity ----------------------
1517
1518 -- Get IN-parameters for FP Baseline Call
1519
1520 -- Fetch existing baseline FP data, if any
1521 OPEN l_fp_baseline_csr ( l_project_id, l_fin_plan_type_id, l_version_type );
1522 FETCH l_fp_baseline_csr INTO l_baselined_version_id, l_baselined_record_number;
1523 IF (l_fp_baseline_csr%NOTFOUND)
1524 THEN
1525 l_baselined_version_id := NULL;
1526 l_baselined_record_number := NULL;
1527 END IF;
1528 CLOSE l_fp_baseline_csr;
1529
1530
1531 -- Fetch Current Working Version FP Data
1532 OPEN l_fp_draft_csr ( l_draft_version_id );
1533 FETCH l_fp_draft_csr INTO l_draft_record_number;
1534 IF (l_fp_draft_csr%NOTFOUND)
1535 THEN
1536 l_draft_record_number := NULL;
1537 END IF;
1538 CLOSE l_fp_draft_csr;
1539
1540
1541 PA_FIN_PLAN_PUB.Baseline
1542 (p_project_id => l_project_id
1543 , p_budget_version_id => l_draft_version_id
1544 , p_record_version_number => l_draft_record_number
1545 , p_orig_budget_version_id => l_baselined_version_id
1549 , x_msg_count => l_msg_count
1546 , p_orig_record_version_number => l_baselined_record_number
1547 , x_fc_version_created_flag => l_fc_version_created_flag
1548 , x_return_status => l_return_status
1550 , x_msg_data => l_msg_data
1551 );
1552
1553 -- Any message or stutus other than 'S', then initiate Workflow Failure.
1554
1555 IF ( (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1556 OR (l_msg_count > 0)
1557 )
1558 THEN
1559
1560 PA_WORKFLOW_UTILS.Set_Notification_Messages
1561 (p_item_type => itemtype
1562 , p_item_key => itemkey
1563 );
1564
1565 resultout := wf_engine.eng_completed||':'||'F';
1566
1567 /* 4995380: Added code to set the WF status to 'REJECTED'
1568 so that the changes can be done and the Baseline can be submitted
1569 once again, if Baseline fails. */
1570 UPDATE pa_budget_versions
1571 SET budget_status_code = 'W'
1572 , wf_status_code = 'REJECTED'
1573 WHERE budget_version_id = l_draft_version_id;
1574
1575 RETURN ;
1576
1577 END IF; -- FP Baseline Failure
1578
1579
1580
1581 ELSE
1582 -- This is a r11.5.7 B-U-D-G-E-T Model Entity ----------------------
1583
1584
1585 -- Call the following wrapper API, which also performs funds checking if required.
1586
1587 PA_BUDGET_UTILS.Baseline_Budget
1588 ( p_draft_version_id => l_draft_version_id
1589 ,p_project_id => l_project_id
1590 ,p_mark_as_original => l_mark_as_original
1591 ,p_verify_budget_rules => 'N'
1592 ,p_fck_req_flag => l_fck_req_flag
1593 ,x_msg_count => l_msg_count
1594 ,x_msg_data => l_msg_data
1595 ,x_return_status => l_return_status
1596 );
1597
1598
1599 -- With the advent of Non-Project Budget Integration,
1600 -- Application Errors may be returned by the new wrapper Baseline_Budget API.
1601 -- Therefore, original r11.0 code is augmented, accordingly.
1602
1603 -- Begin: R12 SLA Effort, Phase II, 23-AUG-2005, jwhite -------------------
1604
1605 IF ( (l_return_status = FND_API.G_RET_STS_SUCCESS)
1606 AND ( l_fck_req_flag = 'Y') )
1607 THEN
1608 -- Budget Integration must have been successful
1609 -- Following call will display success message for Budget Integration
1610 PA_WORKFLOW_UTILS.Set_Notification_Messages
1611 (p_item_type => itemtype
1612 , p_item_key => itemkey
1613 );
1614
1615
1616 END IF;
1617
1618 -- For error handling, changed 'IF (l_msg_count > 0)' reference to l_return_status
1619
1620 IF ((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_msg_count > 0)) -- Bug 4995380
1621 THEN
1622 -- Error! Baseline Failed. ------------------
1623
1624 --Populate Notification Message Text
1625 PA_WORKFLOW_UTILS.Set_Notification_Messages
1626 (p_item_type => itemtype
1627 , p_item_key => itemkey
1628 );
1629
1630 -- R12 SLA Phase II, 23-AUG-2005, jwhite ----------------------
1631 -- Since baseline will fail more often with new R12 functionality, Need to
1632 -- properly populate draft version semaphores.
1633 --
1634 -- Update WF Status on Draft Budget Version
1635 UPDATE pa_budget_versions SET
1636 budget_status_code = 'W'
1637 ,WF_status_code = 'REJECTED'
1638 WHERE budget_version_id = l_draft_version_id;
1639 -- -------------------------------------------------------------
1640
1641 -- FAIL Workflow and Exit
1642 resultout := wf_engine.eng_completed||':'||'F';
1643 RETURN ;
1644
1645 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1646
1647 -- End: R12 SLA Effort, Phase II, 23-AUG-2005, jwhite -------------------
1648
1649
1650 END IF; -- r1157/FP Model Baseline Processing
1651
1652 --
1653 -- Insert a Row into the PA_WF_PROCESSES Table
1654 -- to Record the Workflow Associated with the Baselined
1655 -- Budget
1656 --
1657
1658 IF (l_budget_type_code IS NULL)
1659 THEN
1660 -- FP Plan Model
1661
1662 OPEN l_fp_baseline_csr ( l_project_id, l_fin_plan_type_id, l_version_type );
1663 FETCH l_fp_baseline_csr INTO l_baselined_version_id, l_baselined_record_number;
1664 CLOSE l_fp_baseline_csr;
1665
1666
1667 ELSE
1668 -- R11.5.7 Budgets Model
1669
1670 OPEN l_baseline_csr (l_project_id,l_budget_type_code );
1671 FETCH l_baseline_csr INTO l_baselined_version_id;
1675 END IF;
1672 CLOSE l_baseline_csr;
1673
1674
1676
1677
1678
1679 PA_WORKFLOW_UTILS.Insert_WF_Processes
1680 (p_wf_type_code => 'BUDGET'
1681 ,p_item_type => itemtype
1682 ,p_item_key => itemkey
1683 ,p_entity_key1 => to_char(l_draft_version_id)
1684 ,p_entity_key2 => to_char(l_baselined_version_id)
1685 ,p_description => NULL
1686 ,p_err_code => l_err_code
1687 ,p_err_stage => l_err_stage
1688 ,p_err_stack => l_err_stack
1689 );
1690
1691 IF (l_err_code <> 0)
1692 THEN
1693 WF_CORE.CONTEXT('PA_BUDGET_CORE','BASELINE', itemtype, itemkey, to_char(actid), funcmode);
1694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1695 END IF;
1696
1697 --
1698 -- After Successfully calling BASELINE, Set The Budget_Status_Code
1699 -- Back To 'W' (Working) and wf_status_code to NULL.
1700 --
1701
1702 UPDATE pa_budget_versions SET
1703 budget_status_code = 'W'
1704 , wf_status_code = NULL
1705 WHERE budget_version_id = l_draft_version_id;
1706
1707
1708
1709 END IF ; -- OK to Baseline
1710
1711
1712
1713 IF (l_warnings_only = 'Y')
1714 THEN
1715 resultout := wf_engine.eng_completed||':'||'T';
1716 ELSE
1717 /* 4995380: Added code to set the WF status to 'REJECTED'
1718 so that the changes can be done and the Baseline can be submitted
1719 once again, if there are any errors and l_warnings_only = 'N'. */
1720 UPDATE pa_budget_versions
1721 SET budget_status_code = 'W'
1722 , wf_status_code = 'REJECTED'
1723 WHERE budget_version_id = l_draft_version_id;
1724 resultout := wf_engine.eng_completed||':'||'F';
1725 END IF;
1726
1727 EXCEPTION
1728
1729 WHEN FND_API.G_EXC_ERROR
1730 THEN
1731 UPDATE pa_budget_versions SET
1732 budget_status_code = 'W'
1733 , wf_status_code = NULL
1734 WHERE budget_version_id = l_draft_version_id;
1735 WF_CORE.CONTEXT('PA_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1736 RAISE;
1737
1738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1739 THEN
1740 UPDATE pa_budget_versions SET
1741 budget_status_code = 'W'
1742 , wf_status_code = NULL
1743 WHERE budget_version_id = l_draft_version_id;
1744 WF_CORE.CONTEXT('PA_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1745 RAISE;
1746
1747 WHEN OTHERS THEN
1748 UPDATE pa_budget_versions SET
1749 budget_status_code = 'W'
1750 , wf_status_code = NULL
1751 WHERE budget_version_id = l_draft_version_id;
1752 WF_CORE.CONTEXT('PA_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1753 RAISE;
1754
1755 END Baseline_Budget;
1756
1757 --Name: IS_FEDERAL_ENABLED
1758 --Type: Procedure
1759 --Description: This procedure is used to find if FV_ENABLED(Federal profile) option is enabled
1760 --
1761 --Called subprograms: None
1762 --
1763 --Notes:
1764 -- This is called from PA Budget Baseline Workflow to find if Federal Option is enabled. If yes, and also
1765 -- if the BEM/Third part interface is successful, then a notification is sent to the Budget Approver to
1766 -- inform the Budget Analyst to import the Budget data from Interface tables.
1767
1768 PROCEDURE IS_FEDERAL_ENABLED
1769 (itemtype IN VARCHAR2
1770 , itemkey IN VARCHAR2
1771 , actid IN NUMBER
1772 , funcmode IN VARCHAR2
1773 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1774 )
1775 Is
1776 l_federal_enabled VARCHAR2(1);
1777 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1778
1779 Begin
1780
1781 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1782 itemkey => itemkey,
1783 aname => 'BUDGET_TYPE_CODE' );
1784
1785
1786 l_federal_enabled := NVL(FND_PROFILE.value('FV_ENABLED'), 'N');
1787
1788
1789 If l_federal_enabled = 'Y' and l_budget_type_code is NOT NULL then
1790 resultout := wf_engine.eng_completed||':'||'T';
1791 Else
1792 resultout := wf_engine.eng_completed||':'||'F';
1793 End if;
1794
1795 Return;
1796 End IS_FEDERAL_ENABLED;
1797
1798
1799 -- ====================================================
1800 END pa_budget_wf;