[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_WF
Source
1 PACKAGE BODY pa_budget_wf AS
2 /* $Header: PAWFBUVB.pls 120.7.12010000.2 2008/09/26 05:24:56 rthumma ship $ */
3
4
5 -- -------------------------------------------------------------------------------------
6 -- Globals
7 -- -------------------------------------------------------------------------------------
8
9 G_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
10
11 -- -------------------------------------------------------------------------------------
12 -- Procedures
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
154 , p_err_stack => p_err_stack
155 );
156
157
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
315 -- procedure to use draft_version_id in lieu of
316 -- project_id, budget_type_code and budget_status_code.
317 --
318 --
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 PA_WORKFLOW_UTILS.Set_Global_Attr
404 (p_item_type => itemtype
405 , p_item_key => itemkey
406 , p_err_code => l_err_code);
407 */
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
456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
453 WF_CORE.CONTEXT('PA_BUDGET_WF','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
454 RAISE;
455
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:
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.
490 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
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
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 PA_WORKFLOW_UTILS.Set_Global_Attr
604 );
601 (p_item_type => itemtype
602 , p_item_key => itemkey
603 , 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
640 --ISSUE: a employee can have several users attached to it. So, this
641 -- Code Retrieves the First User.
642
643
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 l_approver_role := 'APPR_' ||itemtype || itemkey;
680
681 WF_DIRECTORY.CreateAdHocRole( role_name => l_approver_role
682 , role_display_name => l_baseliner_full_name
683 , expiration_date => sysdate+1
684 );
685
686 wf_engine.SetItemAttrText
687 (itemtype => itemtype,
688 itemkey => itemkey,
689 aname => '#FROM_ROLE',
690 avalue => l_approver_role );
691
692 --Bug 6994708
693
694 resultout := wf_engine.eng_completed||':'||'T';
695 ELSE
696
697 CLOSE l_baseliner_user_csr;
698 resultout := wf_engine.eng_completed||':'||'F';
699 END IF;
700 ELSE
701
702 resultout := wf_engine.eng_completed||':'||'F';
703 END IF;
704
705
706 EXCEPTION
707
708 WHEN FND_API.G_EXC_ERROR
709 THEN
710 WF_CORE.CONTEXT('PA_BUDGET_WF','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
711 RAISE;
712
713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
714 THEN
715 WF_CORE.CONTEXT('PA_BUDGET_WF','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
716 RAISE;
717
718 WHEN OTHERS
719 THEN
720 WF_CORE.CONTEXT('PA_BUDGET_WF','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
721 RAISE;
722
723
724 END Select_Budget_Approver;
725
726
727 -- ==================================================
728
729 --Name Verify_Budget_Rules
730 --Type: Procedure
731 --Description: This procedure will call a client extension that will return a
732 -- 'T' or 'F', depending on whether all defined rules were met.
733 --
734 --
735 --Called subprograms: PA_BUDGET_UTILS.Verify_Budget_Rules
736 --PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
737 --
738 --
739 --Notes:
740 --
741 -- * * * R12 MOAC Notes:
742 -- This procedure requires a single project/OU context.
743 --
744 --
745 --History:
746 -- 28-FEB-97 L. de Werker - Created
747 -- 24-JUN-97 jwhite - Updated to latest specs
748 -- 09-SEP-97 jwhite - Updated to latest specs
749 -- 26-SEP-97 jwhite - Updated WF error processing.
753 -- development effort, referenced the following
750 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
751 --
752 -- 03-MAY-01 jwhite - As per the Non-Project Integration
754 -- attributes:
755 -- 1. bgt_intg_flag
756 --
757 -- 23-AUG-02 jwhite - Adapted to the new FP model.
758 --
759 -- 19-JUL-05 jwhite -R12 MOAC Effort
760 -- Added calls to the new Set_Prj_Policy_Context to enforce
761 -- a single project/OU context for the Budget Approval
762 -- Workflow nodes.
763 --
764 --
765 -- IN
766 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
767 -- itemkey - A string generated from the application object's primary key.
768 -- actid - The notIFication process activity(instance id).
769 -- funcmode - Run/Cancel
770 -- OUT
771 -- Resultout - T/F
772 --
773 PROCEDURE Verify_Budget_Rules
774 ( itemtype in varchar2
775 , itemkey in varchar2
776 , actid in number
777 , funcmode in varchar2
778 , resultout out NOCOPY varchar2 --File.Sql.39 bug 4440895
779 )
780
781 IS
782 --
783
784
785
786 -- Local Variables
787 l_workflow_started_by_id NUMBER;
788 l_project_id NUMBER;
789 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
790
791
792 l_warnings_only_flag VARCHAR2(1);
793 l_warnings_only VARCHAR2(1) := 'Y';
794 l_err_msg_count NUMBER := 0;
795 l_mark_as_original pa_budget_versions.current_original_flag%TYPE;
796 l_resource_list_id NUMBER;
797 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
798
799 l_msg_count NUMBER;
800 l_msg_data VARCHAR(2000);
801 l_return_status VARCHAR2(1);
802 l_data VARCHAR2(2000);
803 l_msg_index_out NUMBER;
804 l_api_version_number NUMBER := G_api_version_number;
805
806 l_err_code NUMBER := 0;
807 l_err_stage VARCHAR2(120);
808 l_err_stack VARCHAR2(630);
809
810 l_bgt_intg_flag VARCHAR2(1) := NULL;
811
812 l_fin_plan_type_id NUMBER := NULL;
813 l_version_type pa_budget_versions.version_type%TYPE := NULL;
814 l_draft_version_id NUMBER := NULL;
815
816
817 --
818 BEGIN
819 --
820 -- Return if WF Not Running
821 --
822 IF (funcmode <> wf_engine.eng_run) THEN
823 --
824 resultout := wf_engine.eng_null;
825 RETURN;
826 --
827 END IF;
828 --
829
830 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
831
832 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
833 itemkey => itemkey,
834 aname => 'PROJECT_ID' );
835
836 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
837 itemkey => itemkey,
838 aname => 'WORKFLOW_STARTED_BY_ID' );
839
840 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
841 itemkey => itemkey,
842 aname => 'BUDGET_TYPE_CODE' );
843
844 l_mark_as_original := wf_engine.GetItemAttrText(itemtype => itemtype,
845 itemkey => itemkey,
846 aname => 'MARK_AS_ORIGINAL' );
847
848 l_resource_list_id := wf_engine.GetItemAttrText(itemtype => itemtype,
849 itemkey => itemkey,
850 aname =>'RESOURCE_LIST_ID');
851
852 l_project_type_class_code := wf_engine.GetItemAttrText(itemtype => itemtype,
853 itemkey => itemkey,
854 aname =>'PROJECT_TYPE_CLASS_CODE');
855
856 l_bgt_intg_flag := wf_engine.GetItemAttrText(itemtype => itemtype,
857 itemkey => itemkey,
858 aname =>'BGT_INTG_FLAG');
859
860
861 l_draft_version_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
862 itemkey => itemkey,
863 aname => 'DRAFT_VERSION_ID' );
864
865 l_fin_plan_type_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
866 itemkey => itemkey,
867 aname => 'FIN_PLAN_TYPE_ID' );
868
869 l_version_type := wf_engine.GetItemAttrText(itemtype => itemtype,
870 itemkey => itemkey,
871 aname => 'VERSION_TYPE' );
872
873
874
875
876
877 -- SET GLOBALS -----------------------------------------------------------------
878
879 -- Based on the Responsibility, Intialize the Application
880 /*Commented for bug 5233870
881 PA_WORKFLOW_UTILS.Set_Global_Attr
882 (p_item_type => itemtype
883 , p_item_key => itemkey
884 , p_err_code => l_err_code);
885 */
886
887
888 -- R12 MOAC, 19-JUL-05, jwhite -------------------
889 -- Set Single Project/OU context
890
891 PA_BUDGET_UTILS.Set_Prj_Policy_Context
892 (p_project_id => l_project_id
893 ,x_return_status => l_return_status
897 );
894 ,x_msg_count => l_msg_count
895 ,x_msg_data => l_msg_data
896 ,x_err_code => l_err_code
898
899 IF (l_err_code <> 0)
900 THEN
901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
902 END IF;
903
904 -- -----------------------------------------------
905
906
907
908 -- Populate Package Global for Conditional Budget Integration Processing in the
909 -- Verify_Budget_Rules Procedures.
910 PA_BUDGET_UTILS.G_Bgt_Intg_Flag := l_bgt_intg_flag;
911
912 -- ------------------------------------------------------------------------------------
913 -- NON-WF Verify Budget Rules
914 -- ------------------------------------------------------------------------------------
915
916 -- Edits Here Obsoleted by the FP Model Dev Effort.
917
918
919 -- SUBMISSION RULES -------------------------------------------------------------
920 --dbms_output.put_line('Verify Budget Rules - SUBMIT');
921
922
923 PA_BUDGET_UTILS.VERIFY_BUDGET_RULES
924 (p_draft_version_id => l_draft_version_id
925 , p_mark_as_original => l_mark_as_original
926 , p_event => 'SUBMIT'
927 , p_project_id => l_project_id
928 , p_budget_type_code => l_budget_type_code
929 , p_resource_list_id => l_resource_list_id
930 , p_project_type_class_code => l_project_type_class_code
931 , p_created_by => l_workflow_started_by_id
932 , p_calling_module => 'PAWFBUVB'
933 , p_fin_plan_type_id => l_fin_plan_type_id
934 , p_version_type => l_version_type
935 , p_warnings_only_flag => l_warnings_only_flag
936 , p_err_msg_count => l_err_msg_count
937 , p_err_code => l_err_code
938 , p_err_stage => l_err_stage
939 , p_err_stack => l_err_stack
940 );
941
942 IF (l_err_msg_count > 0 )
943 THEN
944 PA_WORKFLOW_UTILS.Set_Notification_Messages
945 (p_item_type => itemtype
946 , p_item_key => itemkey
947 );
948 IF (l_warnings_only_flag = 'N') THEN
949 l_warnings_only := 'N';
950 END IF;
951 END IF;
952
953
954
955 -- BASELINE RULES -------------------------------------------------------------
956 PA_BUDGET_UTILS.VERIFY_BUDGET_RULES
957 (p_draft_version_id => l_draft_version_id
958 , p_mark_as_original => l_mark_as_original
959 , p_event => 'BASELINE'
960 , p_project_id => l_project_id
961 , p_budget_type_code => l_budget_type_code
962 , p_resource_list_id => l_resource_list_id
963 , p_project_type_class_code => l_project_type_class_code
964 , p_created_by => l_workflow_started_by_id
965 , p_calling_module => 'PAWFBUVB'
966 , p_fin_plan_type_id => l_fin_plan_type_id
967 , p_version_type => l_version_type
968 , p_warnings_only_flag => l_warnings_only_flag
969 , p_err_msg_count => l_err_msg_count
970 , p_err_code => l_err_code
971 , p_err_stage => l_err_stage
972 , p_err_stack => l_err_stack
973 );
974
975
976
977 IF (l_err_msg_count > 0 )
978 THEN
979 PA_WORKFLOW_UTILS.Set_Notification_Messages
980 (p_item_type => itemtype
981 , p_item_key => itemkey
982 );
983 IF (l_warnings_only_flag = 'N') THEN
984 l_warnings_only := 'N';
985 END IF;
986 END IF;
987
988
989 -- ------------------------------------------------------------------------------------
990 -- WORKFLOW Verify Budget Rules
991 -- ------------------------------------------------------------------------------------
992
993 PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
994 (p_item_type => itemtype
995 , p_item_key => itemkey
996 , p_project_id => l_project_id
997 , p_budget_type_code => l_budget_type_code
998 , p_workflow_started_by_id => l_workflow_started_by_id
999 , p_event => 'SUBMIT'
1000 , p_fin_plan_type_id => l_fin_plan_type_id
1001 , p_version_type => l_version_type
1002 , p_warnings_only_flag => l_warnings_only_flag
1003 , p_err_msg_count => l_err_msg_count
1004 );
1005
1006
1007 IF (l_err_msg_count > 0 )
1008 THEN
1009 PA_WORKFLOW_UTILS.Set_Notification_Messages
1010 (p_item_type => itemtype
1011 , p_item_key => itemkey
1012 );
1013 IF (l_warnings_only_flag = 'N') THEN
1014 l_warnings_only := 'N';
1015 END IF;
1016 END IF;
1017
1018 PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1019 (p_item_type => itemtype
1020 , p_item_key => itemkey
1021 , p_project_id => l_project_id
1022 , p_budget_type_code => l_budget_type_code
1023 , p_workflow_started_by_id => l_workflow_started_by_id
1024 , p_event => 'BASELINE'
1025 , p_fin_plan_type_id => l_fin_plan_type_id
1026 , p_version_type => l_version_type
1027 , p_warnings_only_flag => l_warnings_only_flag
1028 , p_err_msg_count => l_err_msg_count
1029 );
1030
1031
1032 IF (l_err_msg_count > 0 )
1033 THEN
1037 );
1034 PA_WORKFLOW_UTILS.Set_Notification_Messages
1035 (p_item_type => itemtype
1036 , p_item_key => itemkey
1038 IF (l_warnings_only_flag = 'N') THEN
1039 l_warnings_only := 'N';
1040 END IF;
1041 END IF;
1042
1043
1044 IF (l_warnings_only = 'Y')
1045 THEN
1046 resultout := wf_engine.eng_completed||':'||'T';
1047 ELSE
1048 resultout := wf_engine.eng_completed||':'||'F';
1049 END IF;
1050
1051 --
1052
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR
1055 THEN
1056 WF_CORE.CONTEXT('PA_BUDGET_WF','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1057 RAISE;
1058
1059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1060 THEN
1061 WF_CORE.CONTEXT('PA_BUDGET_WF','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1062 RAISE;
1063
1064 WHEN OTHERS THEN
1065 WF_CORE.CONTEXT('PA_BUDGET_WF','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1066 RAISE;
1067
1068
1069 END Verify_Budget_Rules;
1070
1071 -- ==================================================###
1072 --Name: Baseline_Budget
1073 --Type: Procedure
1074 --Description: This procedures performs BASELINE verification,
1075 -- baseline functionality via the core baseline
1076 -- procedure, and directly updates the draft budget.
1077 --
1078 --
1079 --
1080 --
1081 --Called subprograms: PA_BUDGET_UTILS.Verify_Budget_Rules
1082 -- , PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1083 -- , PA_BUDGET_UTILS.Baseline_Budget
1084 -- , PA_WORKFLOW_UTILS.Insert_WF_Processes
1085 --
1086 --
1087 --Notes:
1088 --
1089 -- * * * R12 MOAC Notes:
1090 -- This procedure requires a single project/OU context.
1091 --
1092 --
1093 --History:
1094 -- 28-FEB-1997 L. de Werker - Created
1095 -- 24-JUN-97 jwhite - Updated
1096 -- 23-AUG-97 jwhite - Updated to latest specs.
1097 -- 09-SEP-97 jwhite - Updated to lastest specs.
1098 -- 17-SEP-97 jwhite - Added Insert_WF_Processes to
1099 -- Baseline_Budget procedure.
1100 -- 26-SEP-97 jwhite - Updated WF error processing.
1101 -- 21-OCT-97 jwhite - Updated as per Kevin Hudson's code review
1102 --
1103 -- 08-MAY-01 jwhite - As per the Non-Project Integration
1104 -- development effort, substituted the core
1105 -- baseline procedure with the new
1106 -- wrapper baseline_budget procedure.
1107 --
1108 -- 23-AUG-02 jwhite - Adapted to new FP model.
1109 --
1110 -- 16-OCT-02 jwhite - Oops!
1111 -- For BASELINE_BUDGET api, added conditional call logic for
1112 -- the FP Baseline procedure.
1113 --
1114 -- 23-AUG-05 jwhite - R12 SLA Effort, Phase II
1115 -- 1) Changed logic to test for FND_API.G_RET_STS_SUCCESS
1116 -- 2) Populate Notifcation messages for successful budget integration
1117 --
1118 --
1119 -- IN
1120 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1121 -- itemkey - A string generated from the application object's primary key.
1122 -- actid - The notIFication process activity(instance id).
1123 -- funcmode - Run/Cancel
1124 -- OUT
1125 -- Resultout - T/F
1126 --
1127 --
1128 --
1129 PROCEDURE Baseline_Budget
1130 (itemtype IN VARCHAR2
1131 , itemkey IN VARCHAR2
1132 , actid IN NUMBER
1133 , funcmode IN VARCHAR2
1134 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1135 )
1136
1137 IS
1138
1139
1140
1141 -- R11.5.7 Budgets Model -----------------------
1142 CURSOR l_baseline_csr
1143 ( p_project_id NUMBER
1144 , p_budget_type_code VARCHAR2 )
1145
1146 IS
1147 SELECT MAX(budget_version_id)
1148 FROM pa_budget_versions
1149 WHERE project_id = p_project_id
1150 AND budget_type_code = p_budget_type_code
1151 AND budget_status_code = 'B';
1152
1153
1154 -- FP Plan Model -----------------------
1155
1156 -- Note: This cursor can have NO_DATA_FOUND
1157 CURSOR l_fp_baseline_csr ( p_project_id NUMBER
1158 , p_fin_plan_type_id NUMBER
1159 , p_version_type VARCHAR2)
1160 IS
1161 SELECT budget_version_id
1162 , RECORD_VERSION_NUMBER
1163 FROM pa_budget_versions
1164 WHERE project_id = p_project_id
1165 AND current_flag = 'Y'
1166 and fin_plan_type_id = p_fin_plan_type_id
1167 and version_type = p_version_type;
1168
1169
1170 CURSOR l_fp_draft_csr ( p_draft_version_id NUMBER)
1171 IS
1172 SELECT RECORD_VERSION_NUMBER
1173 FROM pa_budget_versions
1174 WHERE budget_version_id = p_draft_version_id;
1175
1176
1177
1178
1179 -- Local Variable Declaration ---------------
1180
1181 l_workflow_started_by_id NUMBER;
1185
1182 l_baseliner_id NUMBER;
1183 l_project_id NUMBER;
1184 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1186 l_row_found NUMBER;
1187 l_baselined_version_id NUMBER;
1188
1189 l_api_version_number NUMBER := G_api_version_number;
1190 l_msg_count NUMBER;
1191 l_msg_data VARCHAR(2000);
1192 l_return_status VARCHAR2(1);
1193 l_pm_product_code pa_projects.pm_product_code%TYPE :='WORKFLOW';
1194 l_data VARCHAR2(2000);
1195 l_msg_index_out NUMBER;
1196
1197
1198 l_warnings_only_flag VARCHAR2(1);
1199 l_warnings_only VARCHAR2(1) := 'Y';
1200 l_err_msg_count NUMBER;
1201 l_mark_as_original pa_budget_versions.current_original_flag%TYPE;
1202 l_resource_list_id NUMBER;
1203 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
1204
1205 l_err_code NUMBER := 0;
1206 l_err_stage VARCHAR2(120) := NULL;
1207 l_err_stack VARCHAR2(630);
1208
1209 l_bgt_intg_flag VARCHAR2(1) := NULL;
1210 l_fck_req_flag VARCHAR2(1) := NULL;
1211
1212 l_fin_plan_type_id NUMBER := NULL;
1213 l_version_type pa_budget_versions.version_type%TYPE := NULL;
1214 l_draft_version_id NUMBER := NULL;
1215 l_baselined_record_number NUMBER := NULL;
1216 l_draft_record_number NUMBER := NULL;
1217 l_fc_version_created_flag VARCHAR2(1);
1218
1219
1220 BEGIN
1221
1222
1223 --
1224 -- Return if WF Not Running
1225 --
1226 IF (funcmode <> wf_engine.eng_run) THEN
1227 --
1228 resultout := wf_engine.eng_null;
1229 RETURN;
1230 --
1231 END IF;
1232 --
1233
1234
1235 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
1236
1237 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1238 itemkey => itemkey,
1239 aname => 'PROJECT_ID' );
1240
1241
1242 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1243 itemkey => itemkey,
1244 aname => 'BUDGET_TYPE_CODE' );
1245
1246 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1247 itemkey => itemkey,
1248 aname => 'WORKFLOW_STARTED_BY_ID' );
1249
1250
1251 l_baseliner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1252 itemkey => itemkey,
1253 aname => 'BUDGET_BASELINER_ID' );
1254
1255 l_mark_as_original := wf_engine.GetItemAttrText(itemtype => itemtype,
1256 itemkey => itemkey,
1257 aname => 'MARK_AS_ORIGINAL' );
1258
1259 l_resource_list_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1260 itemkey => itemkey,
1261 aname =>'RESOURCE_LIST_ID');
1262
1263 l_project_type_class_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1264 itemkey => itemkey,
1265 aname => 'PROJECT_TYPE_CLASS_CODE');
1266
1267 l_bgt_intg_flag := wf_engine.GetItemAttrText(itemtype => itemtype,
1268 itemkey => itemkey,
1269 aname =>'BGT_INTG_FLAG');
1270
1271 l_fck_req_flag := wf_engine.GetItemAttrText(itemtype => itemtype,
1272 itemkey => itemkey,
1273 aname =>'FCK_REQ_FLAG');
1274
1275
1276
1277 l_draft_version_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1278 itemkey => itemkey,
1279 aname => 'DRAFT_VERSION_ID' );
1280
1281 l_fin_plan_type_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1282 itemkey => itemkey,
1283 aname => 'FIN_PLAN_TYPE_ID' );
1284
1285 l_version_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1286 itemkey => itemkey,
1287 aname => 'VERSION_TYPE' );
1288
1289
1290
1291 --Bug 2162949
1292 G_baselined_by_user_id :=l_baseliner_id;
1293
1294 -- SET GLOBALS -----------------------------------------------------------------
1295 /*Commented for bug 5233870
1296 -- Based on the Responsibility, Intialize the Application
1297 PA_WORKFLOW_UTILS.Set_Global_Attr
1298 (p_item_type => itemtype
1299 , p_item_key => itemkey
1300 , p_err_code => l_err_code);
1301 */
1302
1303
1304 -- R12 MOAC, 19-JUL-05, jwhite -------------------
1305 -- Set Single Project/OU context
1306
1307 PA_BUDGET_UTILS.Set_Prj_Policy_Context
1308 (p_project_id => l_project_id
1309 ,x_return_status => l_return_status
1310 ,x_msg_count => l_msg_count
1311 ,x_msg_data => l_msg_data
1312 ,x_err_code => l_err_code
1313 );
1314
1315 IF (l_err_code <> 0)
1316 THEN
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 END IF;
1319
1320 -- -----------------------------------------------
1321
1322
1323
1324 -- Populate Package Global for Conditional Budget Integration Processing in the
1325 -- Verify_Budget_Rules Procedures.
1326 PA_BUDGET_UTILS.G_Bgt_Intg_Flag := l_bgt_intg_flag;
1330
1327
1328
1329
1331 -- ------------------------------------------------------------------------------------
1332 -- NON-WF Verify Budget Rules
1333 -- ------------------------------------------------------------------------------------
1334
1335 PA_BUDGET_UTILS.VERIFY_BUDGET_RULES
1336 (p_draft_version_id => l_draft_version_id
1337 , p_mark_as_original => l_mark_as_original
1338 , p_event => 'BASELINE'
1339 , p_project_id => l_project_id
1340 , p_budget_type_code => l_budget_type_code
1341 , p_resource_list_id => l_resource_list_id
1342 , p_project_type_class_code => l_project_type_class_code
1343 , p_created_by => l_workflow_started_by_id
1344 , p_calling_module => 'PAWFBUVB'
1345 , p_fin_plan_type_id => l_fin_plan_type_id
1346 , p_version_type => l_version_type
1347 , p_warnings_only_flag => l_warnings_only_flag
1348 , p_err_msg_count => l_err_msg_count
1349 , p_err_code => l_err_code
1350 , p_err_stage => l_err_stage
1351 , p_err_stack => l_err_stack
1352 );
1353
1354
1355
1356 IF (l_err_msg_count > 0 )
1357 THEN
1358 PA_WORKFLOW_UTILS.Set_Notification_Messages
1359 (p_item_type => itemtype
1360 , p_item_key => itemkey
1361 );
1362 IF (l_warnings_only_flag = 'N') THEN
1363 l_warnings_only := 'N';
1364 END IF;
1365 END IF;
1366
1367
1368 -- ------------------------------------------------------------------------------------
1369 -- WORKFLOW Verify Budget Rules
1370 -- ------------------------------------------------------------------------------------
1371
1372
1373 PA_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1374 (p_item_type => itemtype
1375 , p_item_key => itemkey
1376 , p_project_id => l_project_id
1377 , p_budget_type_code => l_budget_type_code
1378 , p_workflow_started_by_id => l_workflow_started_by_id
1379 , p_event => 'BASELINE'
1380 , p_fin_plan_type_id => l_fin_plan_type_id
1381 , p_version_type => l_version_type
1382 , p_warnings_only_flag => l_warnings_only_flag
1383 , p_err_msg_count => l_err_msg_count
1384 );
1385
1386
1387 IF (l_err_msg_count > 0 )
1388 THEN
1389 PA_WORKFLOW_UTILS.Set_Notification_Messages
1390 (p_item_type => itemtype
1391 , p_item_key => itemkey
1392 );
1393 IF (l_warnings_only_flag = 'N') THEN
1394 l_warnings_only := 'N';
1395 END IF;
1396 END IF;
1397
1398 -- ---------------------------------------------------------------------------------------
1399 -- BASELINE THIS BUDGET VERSION if only warnings
1400 -- Make sure verify budget rules NOT called again:
1401 -- x_verify_budget_rules => 'N'
1402 -- ---------------------------------------------------------------------------------------
1403
1404 IF (l_warnings_only = 'Y')
1405 THEN
1406
1407
1408 IF (l_budget_type_code IS NULL)
1409 THEN
1410
1411 -- This is a FINANCIAL PLAN Model Entity ----------------------
1412
1413 -- Get IN-parameters for FP Baseline Call
1414
1415 -- Fetch existing baseline FP data, if any
1416 OPEN l_fp_baseline_csr ( l_project_id, l_fin_plan_type_id, l_version_type );
1417 FETCH l_fp_baseline_csr INTO l_baselined_version_id, l_baselined_record_number;
1418 IF (l_fp_baseline_csr%NOTFOUND)
1419 THEN
1420 l_baselined_version_id := NULL;
1421 l_baselined_record_number := NULL;
1422 END IF;
1423 CLOSE l_fp_baseline_csr;
1424
1425
1426 -- Fetch Current Working Version FP Data
1427 OPEN l_fp_draft_csr ( l_draft_version_id );
1428 FETCH l_fp_draft_csr INTO l_draft_record_number;
1429 IF (l_fp_draft_csr%NOTFOUND)
1430 THEN
1431 l_draft_record_number := NULL;
1432 END IF;
1433 CLOSE l_fp_draft_csr;
1434
1435
1436 PA_FIN_PLAN_PUB.Baseline
1437 (p_project_id => l_project_id
1438 , p_budget_version_id => l_draft_version_id
1439 , p_record_version_number => l_draft_record_number
1440 , p_orig_budget_version_id => l_baselined_version_id
1441 , p_orig_record_version_number => l_baselined_record_number
1442 , x_fc_version_created_flag => l_fc_version_created_flag
1443 , x_return_status => l_return_status
1444 , x_msg_count => l_msg_count
1445 , x_msg_data => l_msg_data
1446 );
1447
1448 -- Any message or stutus other than 'S', then initiate Workflow Failure.
1449
1450 IF ( (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1451 OR (l_msg_count > 0)
1452 )
1453 THEN
1454
1458 );
1455 PA_WORKFLOW_UTILS.Set_Notification_Messages
1456 (p_item_type => itemtype
1457 , p_item_key => itemkey
1459
1460 resultout := wf_engine.eng_completed||':'||'F';
1461
1462 /* 4995380: Added code to set the WF status to 'REJECTED'
1463 so that the changes can be done and the Baseline can be submitted
1464 once again, if Baseline fails. */
1465 UPDATE pa_budget_versions
1466 SET budget_status_code = 'W'
1467 , wf_status_code = 'REJECTED'
1468 WHERE budget_version_id = l_draft_version_id;
1469
1470 RETURN ;
1471
1472 END IF; -- FP Baseline Failure
1473
1474
1475
1476 ELSE
1477 -- This is a r11.5.7 B-U-D-G-E-T Model Entity ----------------------
1478
1479
1480 -- Call the following wrapper API, which also performs funds checking if required.
1481
1482 PA_BUDGET_UTILS.Baseline_Budget
1483 ( p_draft_version_id => l_draft_version_id
1484 ,p_project_id => l_project_id
1485 ,p_mark_as_original => l_mark_as_original
1486 ,p_verify_budget_rules => 'N'
1487 ,p_fck_req_flag => l_fck_req_flag
1488 ,x_msg_count => l_msg_count
1489 ,x_msg_data => l_msg_data
1490 ,x_return_status => l_return_status
1491 );
1492
1493
1494 -- With the advent of Non-Project Budget Integration,
1495 -- Application Errors may be returned by the new wrapper Baseline_Budget API.
1496 -- Therefore, original r11.0 code is augmented, accordingly.
1497
1498 -- Begin: R12 SLA Effort, Phase II, 23-AUG-2005, jwhite -------------------
1499
1500 IF ( (l_return_status = FND_API.G_RET_STS_SUCCESS)
1501 AND ( l_fck_req_flag = 'Y') )
1502 THEN
1503 -- Budget Integration must have been successful
1504 -- Following call will display success message for Budget Integration
1505 PA_WORKFLOW_UTILS.Set_Notification_Messages
1506 (p_item_type => itemtype
1507 , p_item_key => itemkey
1508 );
1509
1510
1511 END IF;
1512
1513 -- For error handling, changed 'IF (l_msg_count > 0)' reference to l_return_status
1514
1515 IF ((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_msg_count > 0)) -- Bug 4995380
1516 THEN
1517 -- Error! Baseline Failed. ------------------
1518
1519 --Populate Notification Message Text
1520 PA_WORKFLOW_UTILS.Set_Notification_Messages
1521 (p_item_type => itemtype
1522 , p_item_key => itemkey
1523 );
1524
1525 -- R12 SLA Phase II, 23-AUG-2005, jwhite ----------------------
1526 -- Since baseline will fail more often with new R12 functionality, Need to
1527 -- properly populate draft version semaphores.
1528 --
1529 -- Update WF Status on Draft Budget Version
1530 UPDATE pa_budget_versions SET
1531 budget_status_code = 'W'
1532 ,WF_status_code = 'REJECTED'
1533 WHERE budget_version_id = l_draft_version_id;
1534 -- -------------------------------------------------------------
1535
1536 -- FAIL Workflow and Exit
1537 resultout := wf_engine.eng_completed||':'||'F';
1538 RETURN ;
1539
1540 END IF; -- l_return_status <> FND_API.G_RET_STS_SUCCESS
1541
1542 -- End: R12 SLA Effort, Phase II, 23-AUG-2005, jwhite -------------------
1543
1544
1545 END IF; -- r1157/FP Model Baseline Processing
1546
1547 --
1548 -- Insert a Row into the PA_WF_PROCESSES Table
1549 -- to Record the Workflow Associated with the Baselined
1550 -- Budget
1551 --
1552
1553 IF (l_budget_type_code IS NULL)
1554 THEN
1555 -- FP Plan Model
1556
1557 OPEN l_fp_baseline_csr ( l_project_id, l_fin_plan_type_id, l_version_type );
1558 FETCH l_fp_baseline_csr INTO l_baselined_version_id, l_baselined_record_number;
1559 CLOSE l_fp_baseline_csr;
1560
1561
1562 ELSE
1563 -- R11.5.7 Budgets Model
1564
1565 OPEN l_baseline_csr (l_project_id,l_budget_type_code );
1566 FETCH l_baseline_csr INTO l_baselined_version_id;
1567 CLOSE l_baseline_csr;
1568
1569
1570 END IF;
1571
1572
1573
1574 PA_WORKFLOW_UTILS.Insert_WF_Processes
1575 (p_wf_type_code => 'BUDGET'
1576 ,p_item_type => itemtype
1577 ,p_item_key => itemkey
1581 ,p_err_code => l_err_code
1578 ,p_entity_key1 => to_char(l_draft_version_id)
1579 ,p_entity_key2 => to_char(l_baselined_version_id)
1580 ,p_description => NULL
1582 ,p_err_stage => l_err_stage
1583 ,p_err_stack => l_err_stack
1584 );
1585
1586 IF (l_err_code <> 0)
1587 THEN
1588 WF_CORE.CONTEXT('PA_BUDGET_CORE','BASELINE', itemtype, itemkey, to_char(actid), funcmode);
1589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1590 END IF;
1591
1592 --
1593 -- After Successfully calling BASELINE, Set The Budget_Status_Code
1594 -- Back To 'W' (Working) and wf_status_code to NULL.
1595 --
1596
1597 UPDATE pa_budget_versions SET
1598 budget_status_code = 'W'
1599 , wf_status_code = NULL
1600 WHERE budget_version_id = l_draft_version_id;
1601
1602
1603
1604 END IF ; -- OK to Baseline
1605
1606
1607
1608 IF (l_warnings_only = 'Y')
1609 THEN
1610 resultout := wf_engine.eng_completed||':'||'T';
1611 ELSE
1612 /* 4995380: Added code to set the WF status to 'REJECTED'
1613 so that the changes can be done and the Baseline can be submitted
1614 once again, if there are any errors and l_warnings_only = 'N'. */
1615 UPDATE pa_budget_versions
1616 SET budget_status_code = 'W'
1617 , wf_status_code = 'REJECTED'
1618 WHERE budget_version_id = l_draft_version_id;
1619 resultout := wf_engine.eng_completed||':'||'F';
1620 END IF;
1621
1622 EXCEPTION
1623
1624 WHEN FND_API.G_EXC_ERROR
1625 THEN
1626 WF_CORE.CONTEXT('PA_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1627 RAISE;
1628
1629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1630 THEN
1631 WF_CORE.CONTEXT('PA_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1632 RAISE;
1633
1634 WHEN OTHERS THEN
1635 WF_CORE.CONTEXT('PA_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1636 RAISE;
1637
1638 END Baseline_Budget;
1639
1640 --Name: IS_FEDERAL_ENABLED
1641 --Type: Procedure
1642 --Description: This procedure is used to find if FV_ENABLED(Federal profile) option is enabled
1643 --
1644 --Called subprograms: None
1645 --
1646 --Notes:
1647 -- This is called from PA Budget Baseline Workflow to find if Federal Option is enabled. If yes, and also
1648 -- if the BEM/Third part interface is successful, then a notification is sent to the Budget Approver to
1649 -- inform the Budget Analyst to import the Budget data from Interface tables.
1650
1651 PROCEDURE IS_FEDERAL_ENABLED
1652 (itemtype IN VARCHAR2
1653 , itemkey IN VARCHAR2
1654 , actid IN NUMBER
1655 , funcmode IN VARCHAR2
1656 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1657 )
1658 Is
1659 l_federal_enabled VARCHAR2(1);
1660 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1661
1662 Begin
1663
1664 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1665 itemkey => itemkey,
1666 aname => 'BUDGET_TYPE_CODE' );
1667
1668
1669 l_federal_enabled := NVL(FND_PROFILE.value('FV_ENABLED'), 'N');
1670
1671
1672 If l_federal_enabled = 'Y' and l_budget_type_code is NOT NULL then
1673 resultout := wf_engine.eng_completed||':'||'T';
1674 Else
1675 resultout := wf_engine.eng_completed||':'||'F';
1676 End if;
1677
1678 Return;
1679 End IS_FEDERAL_ENABLED;
1680
1681
1682 -- ====================================================
1683 END pa_budget_wf;