[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_INTG_WF
Source
1 PACKAGE BODY pa_budget_intg_wf AS
2 /* $Header: PAWFBUIB.pls 120.4 2007/02/06 10:12:21 dthakker ship $ */
3
4 -- FORWARD DECLARATIONS ------------------------------------------------
5
6 PROCEDURE Set_Nf_Error_Msg_Attr (p_item_type IN VARCHAR2,
7 p_item_key IN VARCHAR2,
8 p_msg_count IN NUMBER,
9 p_msg_data IN VARCHAR2
10 ) ;
11
12 PROCEDURE Set_WF_Status_Code (p_draft_version_id IN NUMBER
13 , p_wf_result_code IN VARCHAR2
14 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
15 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
16 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 );
18
19
20 -- ---------------------------------------------------------------------
21 -- ---------------------------------------------------------------------
22
23 --
24 --
25 --Name: Start_Budget_Intg_WF
26 --Type: Procedure
27 --Description: This procedure must return a "T" or "F" depending
28 -- on whether the workflow runs successfully (no Oracle system
29 -- errors) or fails with Oracle errors.
30 --
31 --
32 --
33 --Called Subprograms: pa_budget_wf.start_budget_wf (Budget Approval workflow)
34 -- pa_budget_utils.baseline_budget (wrapper Baseline procedure)
35 --
36 --
37 --Notes:
38 -- This procedure is called from the Budgets form for Submission and
39 -- Baseline Integration processing. Depending on various parameters, this
40 -- procedure either calls the Budget Approval workflow or the new
41 -- wrapper Baseline_Budget API.
42 --
43 --
44 --
45 --History:
46 -- 07-MAY-01 jwhite -Created
47 --
48 --
49 -- 14-JUL-05 jwhite -R12 MOAC Effort
50 -- Added call to the new Set_Prj_Policy_Context to enforce
51 -- a single project/OU context.
52 --
53
54 PROCEDURE Start_Budget_Intg_WF
55 (p_draft_version_id IN NUMBER
56 , p_project_id IN NUMBER
57 , p_budget_type_code IN VARCHAR2
58 , p_mark_as_original IN VARCHAR2
59 , p_budget_wf_flag IN VARCHAR2
60 , p_bgt_intg_flag IN VARCHAR2
61 , p_fck_req_flag IN VARCHAR2
62 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
63 , x_msg_data OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
64 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
65 )
66 IS
67
68
69 -- Project Information
70 CURSOR l_projects_csr(p_project_id NUMBER)
71 IS
72 SELECT p.project_id project_id,
73 p.name project_name,
74 p.segment1 project_number,
75 o.name project_organization
76 FROM pa_projects_all p,
77 hr_all_organization_units o
78 WHERE p.project_id = p_project_id
79 AND p.carrying_out_organization_id = o.organization_id;
80
81 -- Budget Information
82 CURSOR l_budget_type_csr( p_budget_type_code VARCHAR2 )
83 IS
84 SELECT budget_type
85 FROM pa_budget_types
86 WHERE budget_type_code = p_budget_type_code;
87
88
89 l_return_status VARCHAR2(1);
90 l_error_message_code VARCHAR2(30);
91 l_msg_count NUMBER ;
92 l_msg_index_out NUMBER ;
93 l_msg_data VARCHAR2(2000);
94 l_data VARCHAR2(2000);
95 l_err_code NUMBER := 0;
96 l_err_stage VARCHAR2(2000);
97 l_err_stack VARCHAR2(2000);
98
99 l_itemkey VARCHAR2(30) := NULL;
100 l_responsibility_id NUMBER;
101 l_resp_appl_id NUMBER;
102 l_wf_started_date DATE;
103 l_wf_started_by_id NUMBER;
104 l_wf_started_by_username VARCHAR2(100):= NULL; /* Modified length from 30 to 100 for bug 2933743 */
105 l_wf_item_type VARCHAR2(30) := 'PAWFBUI';
106 l_save_threshold NUMBER := NULL;
107
108 l_project_manager_person_id NUMBER := NULL;
109 l_project_manager_name VARCHAR2(200) := NULL;
110 l_project_manager_uname VARCHAR2(200) := NULL;
111 l_project_party_id NUMBER := NULL;
112 l_project_role_id NUMBER := NULL;
113 l_project_role_name VARCHAR2(80):= NULL;
114 l_budget_type pa_budget_types.budget_type%TYPE;
115 l_mark_as_original pa_budget_versions.original_flag%TYPE;
116
117
118 l_projects_rec l_projects_csr%ROWTYPE;
119
120
121 BEGIN
122
123 -- Initialize Workflow, Messaging and Apps Globals ---------------------------
124
125 --pa_fck_util.debug_msg('PAWFBUIB: BEGIN PA_BUDGET_INTG_WF.Start_Budget_Intg_WF');
126
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128
129 fnd_msg_pub.initialize;
130
131 x_msg_count := 0;
132
133 -- Reserve Unique Item Key to Launch WF
134 SELECT pa_workflow_itemkey_s.nextval
135 INTO l_itemkey
136 FROM dual;
137
138
139 --pa_fck_util.debug_msg('PAWFBUIB: l_itemkey: '||l_itemkey);
140
141 -- Setup Apps Environement
142 l_wf_started_by_id := FND_GLOBAL.user_id;
143 l_responsibility_id := FND_GLOBAL.resp_id;
144 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
145 l_wf_started_by_username := FND_GLOBAL.USER_NAME;
146
147 FND_GLOBAL.Apps_Initialize ( user_id => l_wf_started_by_id
148 , resp_id => l_responsibility_id
149 , resp_appl_id => l_resp_appl_id
150 );
151
152 -- R12 MOAC, 14-JUL-05, jwhite -------------------
153 -- Set Single Project/OU context
154
155 PA_BUDGET_UTILS.Set_Prj_Policy_Context
156 (p_project_id => p_project_id
157 ,x_return_status => l_return_status
158 ,x_msg_count => l_msg_count
159 ,x_msg_data => l_msg_data
160 ,x_err_code => l_err_code
161 );
162
163 IF (l_err_code <> 0)
164 THEN
165 x_msg_count := l_msg_count;
166 x_msg_data := l_msg_data;
167 x_return_status := l_return_status;
168 RETURN;
169 END IF;
170
171 -- -----------------------------------------------
172
173
174
175
176
177 -- Find Required Information ---------------------------------------
178 -- Validation is unnecessary as calling module will have already done the validation.
179
180 -- Mark-As-Original Flag Set From IN-Parameter
181 l_mark_as_original := p_mark_as_original;
182
183 -- Get Project Info
184 OPEN l_projects_csr(p_project_id);
185 FETCH l_projects_csr INTO l_projects_rec;
186 CLOSE l_projects_csr;
187
188 -- Get Budget Type
189 OPEN l_budget_type_csr( p_budget_type_code );
190 FETCH l_budget_type_csr INTO l_budget_type;
191 CLOSE l_budget_type_csr;
192
193
194 -- Get the project manager details
195 -- It is OK, if a project manager is not found.
196 pa_project_parties_utils.get_curr_proj_mgr_details
197 (p_project_id => l_projects_rec.project_id
198 ,x_manager_person_id => l_project_manager_person_id
199 ,x_manager_name => l_project_manager_name
200 ,x_project_party_id => l_project_party_id
201 ,x_project_role_id => l_project_role_id
202 ,x_project_role_name => l_project_role_name
203 ,x_return_status => l_return_status
204 ,x_error_message_code => l_error_message_code );
205
206
207 -- Set Up Workflow Environment ----------------------------------------------
208
209
210
211 -- Set Workflow Thresold for Deferred Processing
212 l_save_threshold := wf_engine.threshold ;
213 wf_engine.threshold := -1 ;
214
215
216 -- Create Workflow!
217 wf_engine.CreateProcess ( ItemType => l_wf_item_type
218 , ItemKey => l_itemkey
219 , process => 'PA_BUDGET_INTG_MP'
220 );
221
222
223
224 -- Pass Workflow IN-parameters
225 wf_engine.SetItemAttrNumber
226 ( itemtype => l_wf_item_type
227 , itemkey => l_itemkey
228 , aname => 'PROJECT_ID'
229 , avalue => l_projects_rec.project_id
230 );
231
232 wf_engine.SetItemAttrText
233 ( itemtype => l_wf_item_type
234 , itemkey => l_itemkey
235 , aname => 'PROJECT_NUMBER'
236 , avalue => l_projects_rec.project_number
237 );
238
239 wf_engine.SetItemAttrText
240 ( itemtype => l_wf_item_type
241 , itemkey => l_itemkey
242 , aname => 'PROJECT_NAME'
243 , avalue => l_projects_rec.project_name
244 );
245
246 wf_engine.SetItemAttrText
247 ( itemtype => l_wf_item_type
248 , itemkey => l_itemkey
249 , aname => 'PROJECT_ORGANIZATION'
250 , avalue => l_projects_rec.project_organization
251 );
252
253 wf_engine.SetItemAttrText
254 ( itemtype => l_wf_item_type
255 , itemkey => l_itemkey
256 , aname => 'PROJECT_MANAGER_NAME'
257 , avalue => l_project_manager_name
258 );
259
260 wf_engine.SetItemAttrText
261 (itemtype => l_wf_item_type
262 , itemkey => l_itemkey
263 , aname => 'DRAFT_VERSION_ID'
264 , avalue => p_draft_version_id
265 );
266
267 wf_engine.SetItemAttrText
268 (itemtype => l_wf_item_type
269 , itemkey => l_itemkey
270 , aname => 'BUDGET_TYPE_CODE'
271 , avalue => p_budget_type_code
272 );
273
274 wf_engine.SetItemAttrText
275 (itemtype => l_wf_item_type
276 , itemkey => l_itemkey
277 , aname => 'BUDGET_TYPE'
278 , avalue => l_budget_type
279 );
280
281 wf_engine.SetItemAttrText
282 (itemtype => l_wf_item_type
283 , itemkey => l_itemkey
284 , aname => 'MARK_AS_ORIGINAL'
285 , avalue => l_mark_as_original
286 );
287
288 wf_engine.SetItemAttrText (itemtype => l_wf_item_type
289 , itemkey => l_itemkey
290 , aname => 'BUDGET_WF_FLAG'
291 , avalue => p_budget_wf_flag);
292
293
294 wf_engine.SetItemAttrText (itemtype => l_wf_item_type
295 , itemkey => l_itemkey
296 , aname => 'FCK_REQ_FLAG'
297 , avalue => p_fck_req_flag);
298
299 wf_engine.SetItemAttrText (itemtype => l_wf_item_type
300 , itemkey => l_itemkey
301 , aname => 'BGT_INTG_FLAG'
302 , avalue => p_bgt_intg_flag);
303
304 wf_engine.SetItemAttrText
305 ( itemtype => l_wf_item_type
306 , itemkey => l_itemkey
307 , aname => 'WORKFLOW_STARTED_BY_UNAME'
308 , avalue => l_wf_started_by_username
309 );
310
311 wf_engine.SetItemAttrText
312 (itemtype =>l_wf_item_type
313 , itemkey => l_itemkey
314 , aname => 'PROJECT_RESOURCE_ADMINISTRATOR'
315 , avalue => 'PASYSADMIN'
316 );
317
318 wf_engine.SetItemAttrNumber
319 ( itemtype => l_wf_item_type
320 , itemkey => l_itemkey
321 , aname => 'WORKFLOW_STARTED_BY_UID'
322 , avalue => l_wf_started_by_id
323 );
324
325 wf_engine.SetItemAttrNumber
326 ( itemtype => l_wf_item_type
327 , itemkey => l_itemkey
328 , aname => 'WORKFLOW_STARTED_BY_RESP_ID'
329 , avalue => l_responsibility_id
330 );
331
332 wf_engine.SetItemAttrNumber
333 ( itemtype => l_wf_item_type
334 , itemkey => l_itemkey
335 , aname => 'WORKFLOW_STARTED_BY_APPL_ID'
336 , avalue => l_resp_appl_id
337 );
338
339 --pa_fck_util.debug_msg('PAWFBUIB: Call wf_engine.StartProcess');
340
341
342 -- Start Workflow!!!
343 wf_engine.StartProcess ( itemtype => l_wf_item_type
344 , itemkey => l_itemkey
345 );
346
347 -- -----------------------------------------------------------------
348
349
350 -- Post-Processing Stage ------------------------------------------
351 --pa_fck_util.debug_msg('PAWFBUIB: Restore Environment');
352
353 wf_engine.threshold := l_save_threshold;
354 x_msg_count := fnd_msg_pub.count_msg;
355
356 --pa_fck_util.debug_msg('PAWFBUIB: Log WF Process History');
357 -- Archive the Workflow Key for Subsequent Reporting Purposes
358 PA_WORKFLOW_UTILS.Insert_WF_Processes
359 (p_wf_type_code => 'BUDGET_INTEGRATION'
360 ,p_item_type => l_wf_item_type
361 ,p_item_key => l_itemkey
362 ,p_entity_key1 => to_char(p_project_id)
363 ,p_entity_key2 => to_char(p_draft_version_id)
364 ,p_description => NULL
365 ,p_err_code => l_err_code
366 ,p_err_stage => l_err_stage
367 ,p_err_stack => l_err_stack
368 );
369
370 --pa_fck_util.debug_msg('PAWFBUIB: END PA_BUDGET_INTG_WF.Start_Budget_Intg_WF');
371
372
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 wf_engine.threshold := l_save_threshold;
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 FND_MSG_PUB.Add_Exc_Msg
379 ( p_pkg_name => 'PA_BUDGET_INTG_WF'
380 , p_procedure_name => 'START_BUDGET_INTG_WF'
381 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
382 );
383 FND_MSG_PUB.Count_And_Get
384 (p_count => x_msg_count
385 , p_data => x_msg_data
386 );
387
388
389 END Start_Budget_Intg_WF;
390
391
392 --Name: Process_Bgt_Intg
393 --Type: Procedure
394 --Description: The logic for this node is complex. Basically, this
398 -- accordingly.
395 -- node peforms edits and calls for funds checking Integration budgets.
396 --
397 -- If successful, the Resultout = 'T'. Other notification text varies,
399 --
400 -- Otherwise, the Resultout = 'F' and one or more
401 -- error messages, up to five messages, is displayed on the
402 -- notification. Other notification text varies,
403 -- accordingly.
404 --
405 --
406 --Called subprograms:
407 --
408 --History:
409 -- 14-MAY-01 jwhite Created
410 --
411 -- 13-JUN-01 jwhite For the 31-MAY-01 fix, rewrote
412 -- code track when the Budget Approval
413 -- WF is actually fired.
414 --
415 -- 08-APR-02 jwhite Bug 2310429
416 -- Made changes to gracefully handle ORA
417 -- error conditions and alert the user via
418 -- notification:
419 -- 1) For exception processing,
420 -- moved the WF_CORE code to end
421 -- of code.
422 --
423 -- 14-JUL-05 jwhite -R12 MOAC Effort
424 -- Added call to the new Set_Prj_Policy_Context to enforce
425 -- a single project/OU context.
426 --
427 --
428 --
429 PROCEDURE Process_Bgt_Intg(itemtype IN VARCHAR2
430 , itemkey IN VARCHAR2
431 , actid IN NUMBER
432 , funcmode IN VARCHAR2
433 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
434 )
435 IS
436
437 l_return_status VARCHAR2(1) := NULL;
438 l_msg_count NUMBER := NULL;
439 l_msg_data VARCHAR2(2000) := NULL;
440 l_msg_index_out NUMBER := NULL;
441
442 l_err_code NUMBER := 0; -- R12 MOAC: changed to 0 from NULL
443 l_err_stage VARCHAR2(120) := NULL;
444 l_err_stack VARCHAR2(630) := NULL;
445
446 l_project_id NUMBER := NULL;
447 l_draft_version_id NUMBER := NULL;
448 l_budget_type_code pa_budget_types.budget_type_code%TYPE := NULL;
449 l_mark_as_original pa_budget_versions.original_flag%TYPE := NULL;
450 l_budget_wf_flag VARCHAR2(1) := NULL;
451 l_bgt_intg_flag VARCHAR2(1) := NULL;
452 l_fck_req_flag VARCHAR2(1) := NULL;
453 l_dual_bdgt_cntrl_flag VARCHAR2(1) := NULL;
454 l_cc_budget_version_id NUMBER := NULL;
455
456 l_wf_started_by_username VARCHAR2(100) := NULL; /* Modified length from 30 to 100 for bug 2933743 */
457 l_msg_subj_text VARCHAR2(2000) := NULL;
458 l_msg_desc_text VARCHAR2(2000) := NULL;
459
460 --Apps Environement Variables
461 l_wf_started_by_id NUMBER;
462 l_wf_started_by_resp_id NUMBER;
463 l_wf_started_by_appl_id NUMBER;
464
465
466 l_bgt_appr_wf_fired VARCHAR(2) := 'N';
467 l_msg_count2 NUMBER := NULL;
468 l_msg_data2 VARCHAR2(2000) := NULL;
469 l_return_status2 VARCHAR2(1) := NULL;
470
471
472 CURSOR ccbgtver_csr
473 IS
474 SELECT b.budget_version_id
475 FROM PA_BUDGETARY_CONTROL_OPTIONS bc,
476 PA_BUDGET_VERSIONS b
477 WHERE bc.project_id = b.project_id
478 AND bc.external_budget_code = 'CC'
479 AND bc.budget_type_code = b.budget_type_code
480 AND b.project_id = l_project_id
481 AND b.budget_status_code = 'S';
482
483
484 BEGIN
485
486 -- Return if WF Engine Not Running
487 IF (funcmode <> wf_engine.eng_run) THEN
488 resultout := wf_engine.eng_null;
489 RETURN;
490 END IF;
491
492 --pa_fck_util.debug_msg('PAWFBUIB: BEGIN PA_BUDGET_INTG_WF.Process_Bgt_Intg');
493
494 -- Setup Environment ------------------------------------------------
495
496 -- Assume Success
497 resultout := wf_engine.eng_completed||':'||'T';
498
499
500 -- Get Starting Apps Environment and Initialize
501 l_wf_started_by_id := wf_engine.GetItemAttrNumber
502 ( itemtype => itemtype
503 , itemkey => itemkey
504 , aname => 'WORKFLOW_STARTED_BY_UID'
505 );
506
507 l_wf_started_by_resp_id := wf_engine.GetItemAttrNumber
508 ( itemtype => itemtype
509 , itemkey => itemkey
510 , aname => 'WORKFLOW_STARTED_BY_RESP_ID'
511 );
512
513 l_wf_started_by_appl_id := wf_engine.GetItemAttrNumber
514 ( itemtype => itemtype
515 , itemkey => itemkey
516 , aname => 'WORKFLOW_STARTED_BY_APPL_ID'
517 );
518
519
520 FND_GLOBAL.Apps_Initialize ( user_id => l_wf_started_by_id
524 -- -----------------------------------------------------------------
521 , resp_id => l_wf_started_by_resp_id
522 , resp_appl_id => l_wf_started_by_appl_id
523 );
525
526
527
528 -- Get Required Runtime Parmeters from WF --------------------------
529
530 l_project_id := wf_engine.GetItemAttrNumber
531 ( itemtype => itemtype
532 , itemkey => itemkey
533 , aname => 'PROJECT_ID'
534 );
535
536 l_draft_version_id := wf_engine.GetItemAttrNumber
537 ( itemtype => itemtype
538 , itemkey => itemkey
539 , aname => 'DRAFT_VERSION_ID'
540 );
541
542 l_budget_type_code := wf_engine.GetItemAttrText
543 ( itemtype => itemtype
544 , itemkey => itemkey
545 , aname => 'BUDGET_TYPE_CODE'
546 );
547
548 l_mark_as_original := wf_engine.GetItemAttrText
549 ( itemtype => itemtype
550 , itemkey => itemkey
551 , aname => 'MARK_AS_ORIGINAL'
552 );
553
554 l_budget_wf_flag := wf_engine.GetItemAttrText
555 ( itemtype => itemtype
556 , itemkey => itemkey
557 , aname => 'BUDGET_WF_FLAG'
558 );
559
560 l_fck_req_flag := wf_engine.GetItemAttrText
561 ( itemtype => itemtype
562 , itemkey => itemkey
563 , aname => 'FCK_REQ_FLAG'
564 );
565
566 l_bgt_intg_flag := wf_engine.GetItemAttrText
567 ( itemtype => itemtype
568 , itemkey => itemkey
569 , aname => 'BGT_INTG_FLAG'
570 );
571
572
573 -- R12 MOAC, 14-JUL-05, jwhite -------------------
574 -- Set Single Project/OU context
575
576 PA_BUDGET_UTILS.Set_Prj_Policy_Context
577 (p_project_id => l_project_id
578 ,x_return_status => l_return_status
579 ,x_msg_count => l_msg_count
580 ,x_msg_data => l_msg_data
581 ,x_err_code => l_err_code
582 );
583
584 IF (l_err_code <> 0)
585 THEN
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END IF;
588
589 -- -----------------------------------------------
590
591
592
593
594
595 -- Processing Logic -----------------------------------------------
596
597 IF (nvl(l_budget_wf_flag,'N') = 'Y')
598 THEN
599
600 -- R12 SLA Effort
601 -- CHECK_BASELINE functionality is no longer suppored
602
603 /*
604 -- Budget Approval Workflow ENABLED !!!
605 PA_Budget_Fund_Pkg.Check_Or_Reserve_Funds
606 (p_project_id => l_project_id
607 , p_budget_version_id => l_draft_version_id
608 , p_calling_mode => 'CHECK_BASELINE'
609 , x_dual_bdgt_cntrl_flag => l_dual_bdgt_cntrl_flag
610 , x_cc_budget_version_id => l_cc_budget_version_id
611 , x_return_status => l_return_status
612 , x_msg_data => l_msg_data
613 , x_msg_count => l_msg_count
614 );
615
616 IF (nvl(l_msg_count,0) > 0)
617 THEN
618 -- Validations for Funds Check Failed.
619 -- Do Not Do Anything. Just Drop Down to Next Subsection of Code
620 NULL;
621 ELSE
622 -- R12 SLA Effort
623
624 */
625 -- Call Budget Approval Workflow!!!
626
627 PA_BUDGET_WF.Start_Budget_WF
628 (p_draft_version_id => l_draft_version_id
629 , p_project_id => l_project_id
630 , p_budget_type_code => l_budget_type_code
631 , p_mark_as_original => l_mark_as_original
632 , p_fck_req_flag => l_fck_req_flag
633 , p_bgt_intg_flag => l_bgt_intg_flag
634 , p_err_code => l_err_code
635 , p_err_stage => l_err_stage
636 , p_err_stack => l_err_stack
637 );
638
639 -- Keep track of Budget Approval WF Fired for Subsequent Conditional
640 -- Processing.
641 l_bgt_appr_wf_fired := 'Y';
642
643 IF (l_err_code <> 0 )
644 THEN
645 -- Process ORA Error:
646 IF (l_err_code > 0)
647 THEN
648 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
649 THEN
653 FND_MESSAGE.SET_TOKEN('ERRMSG', l_err_stage);
650 FND_MESSAGE.SET_NAME('PA','PA_WF_CLIENT_EXTN');
651 FND_MESSAGE.SET_TOKEN('EXTNAME', 'PA_BUDGET_WF.START_BUDGET_WF');
652 FND_MESSAGE.SET_TOKEN('ERRCODE',l_err_code);
654 FND_MSG_PUB.add;
655 END IF;
656 l_return_status := FND_API.G_RET_STS_ERROR;
657
658 ELSE
659 -- Process App Error
660 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
661 THEN
662 FND_MSG_PUB.add_exc_msg
663 (p_pkg_name => 'PA_BUDGET_WF'
664 , p_procedure_name => 'START_BUDGET_WF'
665 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0') );
666 END IF;
667 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668 END IF; --(l_err_code > 0)
669
670 END IF; --(l_err_code <> 0 )
671
672 -- R12 SLA Effort
673 -- END IF; --(nvl(l_msg_count,0) > 0)
674
675 ELSE
676 -- DISABLED: Budget Approval Workflow !!!
677 -- Do NOT Call Workflow. Instead, Call WRAPPER Baseline_Budget API
678
679 -- bug 1929871, 19-SEP-2001, jwhite --------------------------
680 -- Add cursor to get commitment budget version id for subsequent update
681
682 -- For subsequent Commitment Budget Budget Version Update, Get the
683 -- commimtment budget version id, IF ANY.
684
685 OPEN ccbgtver_csr;
686 FETCH ccbgtver_csr INTO l_cc_budget_version_id;
687 IF ccbgtver_csr%NOTFOUND
688 THEN
689 l_cc_budget_version_id := NULL;
690 END IF;
691 CLOSE ccbgtver_csr;
692
693
694 -- ---------------------------------------------------------------
695
696 -- Call WRAPPER Baseline Budget API
697 -- Pass 'N' for p_verify_budget_rules because checking should have already been done
698 -- in the Budgets form
699
700
701
702
703 PA_BUDGET_UTILS.Baseline_Budget
704 ( p_draft_version_id => l_draft_version_id
705 ,p_project_id => l_project_id
706 ,p_mark_as_original => l_mark_as_original
707 ,p_verify_budget_rules => 'N'
708 ,p_fck_req_flag => l_fck_req_flag
709 ,x_msg_count => l_msg_count
710 ,x_msg_data => l_msg_data
711 ,x_return_status => l_return_status
712 );
713
714
715 END IF; --(nvl(l_budget_wf_flag,'N') = 'Y')
716
717
718 -- Notification Procesing ------------------------------------------------
719 --pa_fck_util.debug_msg('PAWFBUIB: Notification Environment');
720 --pa_fck_util.debug_msg('PAWFBUIB: -- l_return_status: '||l_return_status);
721 --pa_fck_util.debug_msg('PAWFBUIB: -- l_msg_count: '||to_char(l_msg_count));
722 --pa_fck_util.debug_msg('PAWFBUIB: -- l_msg_data: '||l_msg_data);
723
724
725 -- IF Budget Approval WF N-O-T F-I-R-E-D,
726 -- Conditionally Update the Budget Version WF Status Code
727
728 IF (nvl(l_bgt_appr_wf_fired,'N') <> 'Y')
729 THEN
730 -- The following can only have ORA errors, which will
731 -- cancel the workflow. ORA errors should be rare, however.
732
733 -- bug 1929871, 19-SEP-2001, jwhite --------------------------
734 -- Added code to update the commitment budget, IF ANY
735 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
736 THEN
737 -- Success!
738
739 -- Update Draft Cost Budget Version Id
740 Set_wf_status_code (p_draft_version_id => l_draft_version_id
741 , p_wf_result_code => 'SUCCESS'
742 , x_msg_count => l_msg_count2
743 , x_msg_data => l_msg_data2
744 , x_return_status => l_return_status2
745 );
746
747 -- Update Draft Commitment Budget Version Id, IF ANY
748 IF ( nvl(l_CC_Budget_Version_id,0) > 0 )
749 THEN
750 Set_wf_status_code (p_draft_version_id => l_cc_budget_version_id
751 , p_wf_result_code => 'SUCCESS'
752 , x_msg_count => l_msg_count2
753 , x_msg_data => l_msg_data2
754 , x_return_status => l_return_status2
755 );
756 END IF;
757 ELSE
758 -- Failure!
759
760 -- Update Draft Cost Budget Version Id
761 Set_wf_status_code (p_draft_version_id => l_draft_version_id
762 , p_wf_result_code => 'FAILURE'
763 , x_msg_count => l_msg_count2
764 , x_msg_data => l_msg_data2
768 -- Update Draft Commitment Budget Version Id, IF ANY
765 , x_return_status => l_return_status2
766 );
767
769 IF ( nvl(l_CC_Budget_Version_id,0) > 0 )
770 THEN
771 Set_wf_status_code (p_draft_version_id => l_cc_budget_version_id
772 , p_wf_result_code => 'FAILURE'
773 , x_msg_count => l_msg_count2
774 , x_msg_data => l_msg_data2
775 , x_return_status => l_return_status2
776 );
777 END IF;
778
779 END IF;
780 -- ----------------------------------------------------
781
782 END IF; --NOT FIRED, Budget Approval WF; Update WF Status Code
783
784
785 -- Conditionally Process Notification Messages
786 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
787 THEN
788 -- Success!
789
790 FND_MESSAGE.SET_NAME ('PA','PA_NFSUBJ_BU_INTG_SUCCESS');
791 l_msg_subj_text := FND_MESSAGE.GET;
792
793 FND_MESSAGE.SET_NAME ('PA','PA_NFDESC_BU_INTG_SUCCESS');
794 l_msg_desc_text := FND_MESSAGE.GET;
795
796 wf_engine.SetItemAttrText
797 ( itemtype => itemtype
798 , itemkey => itemkey
799 , aname => 'MSG_SUBJECT_FYI'
800 , avalue => l_msg_subj_text
801 );
802
803 wf_engine.SetItemAttrText
804 ( itemtype => itemtype
805 , itemkey => itemkey
806 , aname => 'MSG_DESCRIPTION_FYI'
807 , avalue => l_msg_desc_text
808 );
809
810 ELSE
811 -- Errors! Note that the WF ONLY marked as FAILURE if ORA error
812
813 FND_MESSAGE.SET_NAME ('PA','PA_NFSUBJ_BU_INTG_FAILURE');
814 l_msg_subj_text := FND_MESSAGE.GET;
815
816 FND_MESSAGE.SET_NAME ('PA','PA_NFDESC_BU_INTG_FAILURE');
817 l_msg_desc_text := FND_MESSAGE.GET;
818
819 wf_engine.SetItemAttrText
820 ( itemtype => itemtype
821 , itemkey => itemkey
822 , aname => 'MSG_SUBJECT_FYI'
823 , avalue => l_msg_subj_text
824 );
825
826 wf_engine.SetItemAttrText
827 ( itemtype => itemtype
828 , itemkey => itemkey
829 , aname => 'MSG_DESCRIPTION_FYI'
830 , avalue => l_msg_desc_text
831 );
832
833 -- Populate Errors for Notification
834 set_nf_error_msg_attr (p_item_type => itemtype,
835 p_item_key => itemkey,
836 p_msg_count => l_msg_count,
837 p_msg_data => l_msg_data
838 );
839
840 -- Hard ORA error. WF FAILURE! Notification will Route to Projects Sys Admin.
841 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
842 THEN
843
844 -- Set WF Status for FAILURE. Route to Sys Admin
845 resultout := wf_engine.eng_completed||':'||'F';
846
847 END IF; -- Hard ORA error
848
849 END IF; --Conditionally Populate NF MSG and Errors
850
851 --pa_fck_util.debug_msg('PAWFBUIB: END PA_BUDGET_INTG_WF.Process_Bgt_Intg');
852
853 EXCEPTION
854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN -- R12 MOAC: Added unexpected error exception
855 WF_CORE.CONTEXT
856 ('PA_BUDGET_INTG_MP',
857 'Process_Bgt_Intg',
858 itemtype,
859 itemkey,
860 to_char(actid),
861 funcmode);
862 RAISE;
863 WHEN OTHERS THEN
864 Set_wf_status_code (p_draft_version_id => l_draft_version_id
865 , p_wf_result_code => 'FAILURE'
866 , x_msg_count => l_msg_count2
867 , x_msg_data => l_msg_data2
868 , x_return_status => l_return_status2
869 );
870 WF_CORE.CONTEXT
871 ('PA_BUDGET_INTG_MP',
872 'Process_Bgt_Intg',
873 itemtype,
874 itemkey,
875 to_char(actid),
876 funcmode);
877 RAISE;
878
879
880 END Process_Bgt_Intg;
881
882
883
884 --Name: set_nf_error_msg_attr
885 --Type: Procedure
886 --Description: This procedure populates the notificatin error message fields.
887 --
888 --Called subprograms: None.
889 --
890 --History:
891 -- 26-MAR-01 jwhite Cloned from PA_ASGMT_WFSTD package
892 --
893 -- 11-JUL-01 jwhite Bug 1877119.
894 -- For a FAILURE transition, rewrote
895 -- error processing code to treat
899 -- Also, replaced fnd_message.set_encoded
896 -- NULL or zero (0) msg_count as
897 -- msg_count = 1.
898 --
900 -- with set_message.
901 --
902 -- 08-APR-02 jwhite Bug 2310429
903 -- Made changes to gracefully handle ORA
904 -- error conditions and alert the user via
905 -- notification:
906 -- 1) Converted exception RAISE to RETURN
907 -- 2) Added "IF length(p_msg_data) < 31" condition
908 -- to handle ORA error messages.
909 --
910
911 PROCEDURE set_nf_error_msg_attr (p_item_type IN VARCHAR2,
912 p_item_key IN VARCHAR2,
913 p_msg_count IN NUMBER,
914 p_msg_data IN VARCHAR2 ) IS
915
916 l_project_id NUMBER := NULL;
917 l_msg_index_out NUMBER ;
918 l_msg_data VARCHAR2(2000) := NULL;
919 l_data VARCHAR2(2000) := NULL;
920 l_item_attr_name VARCHAR2(30);
921 l_msg_err_text VARCHAR2(100);
922
923 BEGIN
924
925
926
927 IF ( (nvl(p_msg_count,0) = 0 )
928 OR (p_msg_count = 1) )
929 THEN
930 IF p_msg_data IS NOT NULL
931 THEN
932
933 IF length(p_msg_data) < 31
934 THEN
935
936 FND_MESSAGE.SET_NAME ('PA',p_msg_data);
937 l_data := FND_MESSAGE.GET;
938
939 END IF;
940
941
942 IF (l_data IS NULL)
943 THEN
944 l_data := p_msg_data;
945 END IF;
946
947 wf_engine.SetItemAttrText
948 ( itemtype => p_item_type
949 , itemkey => p_item_key
950 , aname => 'ERROR_COMMENTS_1'
951 , avalue => l_data
952 );
953 END IF;
954 RETURN ;
955 END IF;
956
957
958 IF p_msg_count > 1 THEN
959 FOR i in 1..p_msg_count
960 LOOP
961 IF i > 5 THEN
962 EXIT;
963 END IF;
964 pa_interface_utils_pub.get_messages
965 (p_encoded => FND_API.G_FALSE,
966 p_msg_index => i,
967 p_msg_count => p_msg_count ,
968 p_msg_data => p_msg_data ,
969 p_data => l_data,
970 p_msg_index_out => l_msg_index_out );
971
972 l_item_attr_name := 'ERROR_COMMENTS_'||i;
973
974 wf_engine.SetItemAttrText
975 ( itemtype => p_item_type
976 , itemkey => p_item_key
977 , aname => l_item_attr_name
978 , avalue => l_data
979 );
980 END LOOP;
981 END IF;
982
983
984
985 EXCEPTION
986 WHEN OTHERS THEN RETURN;
987
988 END set_nf_error_msg_attr;
989
990
991
992 --Name: set_wf_status_code
993 --Type: Procedure
994 --Description: This procedure conditionally populates budget version
995 -- wf_status_code.
996 --
997 --Called subprograms: None.
998 --
999 --Notes
1000 -- Given the NOWAIT status, ORA errors are not expected and, therefore,
1001 -- not explicitly handled.
1002 --
1003 --History:
1004 -- 23-MAY-01 jwhite Created
1005 --
1006
1007
1008 PROCEDURE set_wf_status_code (p_draft_version_id IN NUMBER
1009 , p_wf_result_code IN VARCHAR2
1010 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1011 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1012 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1013 ) IS
1014
1015
1016 BEGIN
1017
1018 -- Assume Success
1019 x_return_status := FND_API.G_RET_STS_SUCCESS;
1020
1021 -- Conditionally Populate wf_status_code
1022 IF (p_wf_result_code = 'SUCCESS')
1023 THEN
1024 UPDATE pa_budget_versions
1025 SET budget_status_code = 'W', WF_status_code = NULL
1026 WHERE budget_version_id = p_draft_version_id;
1027 ELSE
1028 --FAILURE: Reject!
1029 UPDATE pa_budget_versions
1030 SET budget_status_code = 'W', WF_status_code = 'REJECTED'
1031 WHERE budget_version_id = p_draft_version_id;
1032 END IF;
1033
1034 EXCEPTION
1035 WHEN OTHERS
1036 THEN
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 FND_MSG_PUB.Add_Exc_Msg
1039 ( p_pkg_name => 'PA_BUDGET_INTG_WF'
1040 , p_procedure_name => 'SET_WF_STATUS_CODE'
1041 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
1042 );
1043 FND_MSG_PUB.Count_And_Get
1044 (p_count => x_msg_count ,
1045 p_data => x_msg_data );
1046 RETURN;
1047
1048
1049
1053 END pa_budget_intg_wf;
1050 END set_wf_status_code;
1051 -- =================================================
1052
1054