[Home] [Help]
PACKAGE BODY: APPS.GMS_WF_PKG
Source
1 PACKAGE BODY gms_wf_pkg AS
2 /* $Header: gmsfbuvb.pls 120.8.12010000.3 2009/10/08 11:55:07 byeturi ship $ */
3
4
5 -- -------------------------------------------------------------------------------------
6 -- Globals
7 -- -------------------------------------------------------------------------------------
8
9 -- To check on, whether to print debug messages in log file or not
10 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
11
12 G_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
13
14 -- Bug 5162777 : The l_budget_versions_csr is moved out of the procedure Baseline_Budget.
15 -- Cursor for Verify Budget Rules and Core.Baseline Call
16
17 CURSOR l_budget_versions_csr
18 ( p_project_id NUMBER
19 , p_award_id NUMBER
20 , p_budget_type_code VARCHAR2 )
21
22 IS
23 SELECT budget_version_id
24 FROM gms_budget_versions
25 WHERE project_id = p_project_id
26 AND award_id = p_award_id
27 AND budget_type_code = p_budget_type_code
28 AND budget_status_code in ('S','W');
29
30 -- -------------------------------------------------------------------------------------
31 -- Procedures
32 -- -------------------------------------------------------------------------------------
33
34 --Name: START_BUDGET_WF
35 --Type: Procedure
36 --Description: This procedure is used to start a budget workflow process.
37 --
38 --
39 --Called subprograms: GMS_CLIENT_EXTN_BUDGET_WF.Start_Budget_Wf
40 -- , GMS_WORKFLOW_UTILS.Insert_WF_Processes
41 --
42 --Notes:
43 -- This wrapper is called DIRECTLY from the Budgets form and the public
44 -- Baseline_Budget API.
45 --
46 -- !!! This wrapper is NOT CALLED FROM WORKFLOW !!!
47 --
48 -- Error messages in the form and public API call the 'GMS_WF_CLIENT_EXTN'
49 -- error code. Two tokens are passed to the error message: the name of this
50 -- client extension and the error code.
51 --
52 --
53 --
54 --History:
55 --
56 -- IN Parameters
57 -- p_project_id - Unique identifier for the project of the budget for which approval
58 -- is requested.
59 -- p_budget_type_code - Unique identifier for budget submitted for approval
60 -- p_mark_as_original - Yes, mark budget as original; N, do not mark. Defaults to 'N'.
61 --
62 -- OUT NOCOPY Parameters
63 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
64 -- x > 0, Business Rule Violated.
65 -- p_err_stage - Standard error message
66 -- p_err_stack - Not used.
67
68 -- -------------------
69 -- FUNCTION
70 -- Bug 3465169 : This function returns the Burden amount calculated
71 -- for input parameters burdenable_raw_cost,expenditure_type
72 -- organization_id and ind_compiled_set_id.
73 -- This is introduced for performance fix inorder to avoid
74 -- a join with gms_commitment_encumbered_v .
75
76 FUNCTION Get_Burden_amount (p_expenditure_type VARCHAR2,
77 p_organization_id NUMBER,
78 p_ind_compiled_set_id NUMBER,
79 p_burdenable_raw_cost NUMBER)
80 RETURN NUMBER IS
81 CURSOR C_get_burden_amount IS
82 SELECT SUM (p_burdenable_raw_cost * NVL(cm.compiled_multiplier,0))
83 FROM pa_ind_rate_sch_revisions irsr,
84 pa_ind_cost_codes icc,
85 pa_cost_base_exp_types cbet,
86 pa_ind_compiled_sets ics,
87 pa_compiled_multipliers cm
88 WHERE irsr.cost_plus_structure = cbet.cost_plus_structure AND
89 icc.ind_cost_code = cm.ind_cost_code AND
90 cbet.cost_base = cm.cost_base AND
91 ics.cost_base = cbet.cost_base AND
92 cbet.cost_base_type = 'INDIRECT COST' AND
93 cbet.expenditure_type = p_expenditure_type AND
94 ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id AND
95 ics.organization_id = p_organization_id AND
96 ics.ind_compiled_set_id = p_ind_compiled_set_id AND
97 cm.ind_compiled_set_id = p_ind_compiled_set_id ;
98
99 l_burden_amount NUMBER;
100 BEGIN
101
102 OPEN C_get_burden_amount;
103 FETCH C_get_burden_amount INTO l_burden_amount;
104 CLOSE C_get_burden_amount;
105
106 RETURN NVL(l_burden_amount,0);
107
108 END Get_Burden_amount;
109
110 PROCEDURE Start_Budget_Wf
111 (p_draft_version_id IN NUMBER
112 , p_project_id IN NUMBER
113 , p_award_id IN NUMBER
114 , p_budget_type_code IN VARCHAR2
115 , p_mark_as_original IN VARCHAR2
116 , p_err_code IN OUT NOCOPY NUMBER
117 , p_err_stage IN OUT NOCOPY VARCHAR2
118 , p_err_stack IN OUT NOCOPY VARCHAR2
119 )
120 --
121 IS
122 --
123
124 -- Local Variables
125
126 l_err_code NUMBER;
127 l_item_type gms_wf_processes.item_type%TYPE;
128 l_item_key gms_wf_processes.item_key%TYPE;
129
130
131
132 BEGIN
133
134 GMS_CLIENT_EXTN_BUDGET_WF.Start_Budget_Wf
135 ( p_draft_version_id => p_draft_version_id
136 , p_project_id => p_project_id
137 , p_award_id => p_award_id
138 , p_budget_type_code => p_budget_type_code
139 , p_mark_as_original => p_mark_as_original
140 , p_item_type => l_item_type
141 , p_item_key => l_item_key
142 , p_err_code => l_err_code
143 , p_err_stage => p_err_stage
144 , p_err_stack => p_err_stack
145 );
146
147
148 IF (l_err_code = 0)
149 THEN
150 -- Succesful! Log gms_wf_processes table for new workflow.
151
152 GMS_WORKFLOW_UTILS.Insert_WF_Processes
153 (p_wf_type_code => 'BUDGET'
154 ,p_item_type => l_item_type
155 ,p_item_key => l_item_key
156 ,p_entity_key1 => to_char(p_draft_version_id)
157 ,p_description => NULL
158 ,p_err_code => p_err_code
159 ,p_err_stage => p_err_stage
160 ,p_err_stack => p_err_stack
161 );
162 ELSE
163 p_err_code := l_err_code;
164
165 END IF;
166
167
168 EXCEPTION
169
170 WHEN OTHERS
171 THEN
172 p_err_code := SQLCODE;
173 RAISE;
174
175
176 END Start_Budget_Wf;
177
178 -- ===================================================
179
180 --Name: START_BUDGET_WF_NTFY_ONLY
181 --
182 --Type: Procedure
183 --
184 --Description: This procedure is used to send a notification
185 -- when a budget is baselined.
186 --
187 --
188 --Called subprograms: GMS_CLIENT_EXTN_BUDGET_WF.Start_Budget_Wf_Ntfy_Only
189 -- , GMS_WORKFLOW_UTILS.Insert_WF_Processes
190 --
191 --Notes:
192 -- This wrapper is called DIRECTLY from the Budgets form and the public
193 -- Baseline_Budget API.
194 --
195 -- !!! This wrapper is NOT CALLED FROM WORKFLOW !!!
196 --
197 -- Error messages in the form and public API call the 'GMS_WF_CLIENT_EXTN'
198 -- error code. Two tokens are passed to the error message: the name of this
199 -- client extension and the error code.
200 --
201 --
202 --
203 --History:
204 --
205 -- IN Parameters
206 -- p_project_id - Unique identifier for the project of the budget for which approval
207 -- is requested.
208 -- p_budget_type_code - Unique identifier for budget submitted for approval
209 -- p_mark_as_original - Yes, mark budget as original; N, do not mark. Defaults to 'N'.
210 --
211 -- OUT NOCOPY Parameters
212 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
213 -- x > 0, Business Rule Violated.
214 -- p_err_stage - Standard error message
215 -- p_err_stack - Not used.
216
217
218 PROCEDURE Start_Budget_Wf_Ntfy_Only
219 (p_draft_version_id IN NUMBER
220 , p_project_id IN NUMBER
221 , p_award_id IN NUMBER
222 , p_budget_type_code IN VARCHAR2
223 , p_mark_as_original IN VARCHAR2
224 , p_err_code IN OUT NOCOPY NUMBER
225 , p_err_stage IN OUT NOCOPY VARCHAR2
226 , p_err_stack IN OUT NOCOPY VARCHAR2
227 ) IS
228
229 -- Local Variables
230
231 l_err_code NUMBER;
232 l_item_type gms_wf_processes.item_type%TYPE;
233 l_item_key gms_wf_processes.item_key%TYPE;
234
235
236
237 BEGIN
238
239 GMS_CLIENT_EXTN_BUDGET_WF.Start_Budget_Wf_Ntfy_Only
240 ( p_draft_version_id => p_draft_version_id
241 , p_project_id => p_project_id
242 , p_award_id => p_award_id
243 , p_budget_type_code => p_budget_type_code
244 , p_mark_as_original => p_mark_as_original
245 , p_item_type => l_item_type
246 , p_item_key => l_item_key
247 , p_err_code => l_err_code
248 , p_err_stage => p_err_stage
249 , p_err_stack => p_err_stack
250 );
251
252
253 IF (l_err_code = 0)
254 THEN
255 -- Succesful! Log gms_wf_processes table for new workflow.
256
257 GMS_WORKFLOW_UTILS.Insert_WF_Processes
258 (p_wf_type_code => 'BUDGET_NTFY_ONLY'
259 ,p_item_type => l_item_type
260 ,p_item_key => l_item_key
261 ,p_entity_key1 => to_char(p_draft_version_id)
262 ,p_description => NULL
263 ,p_err_code => p_err_code
264 ,p_err_stage => p_err_stage
265 ,p_err_stack => p_err_stack
266 );
267 ELSE
268 p_err_code := l_err_code;
269
270 END IF;
271
272
273 EXCEPTION
274
275 WHEN OTHERS
276 THEN
277 -- Modified for Bug: 2510024
278 p_err_code := 4;
279
280 END Start_Budget_Wf_Ntfy_Only;
281
282
283
284 -- =================================================
285 --
286 -- Name: IS_BUDGET_WF_USED
287 -- Type: Procedure
288 -- Description: This procedure must return a "T" or "F" depending on whether a workflow
289 -- should be started for this particular budget.
290 --
291 --
292 -- Called Subprograms: GMS_CLIENT_EXTN_BUDGET_WF.Budget_Wf_Is_Used
293 --
294 --
295 --Notes:
296 -- This wrapper is called DIRECTLY from the Budgets form and the public
297 -- Baseline_Budget API.
298 --
299 -- !!! THIS WRAPPER IS NOT CALLED FROM WORKFLOW !!!
300 --
301 -- Error messages in the form and public API call the 'GMS_WF_CLIENT_EXTN'
302 -- error code. Two tokens are passed to the error message: the name of this
303 -- client extension and the error code.
304 --
305 --
306 --
307 --
308 --History:
309 --
310 -- IN Parameters
311 -- p_project_id - Unique identifier for the project of the budget for which approval
312 -- is requested.
313 -- p_award_id - Unique identifier for the award of the budget for which approval
314 -- is requested.
315 -- p_budget_type_code - Unique identifier for budget submitted for approval
316 -- p_pm_product_code - The PM vendor's product code stored in gms_budget_versions.
317 --
318 -- OUT NOCOPY Parameters
319 -- p_result - 'T' or 'F' (True/False)
320 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
321 -- x > 0, Business Rule Violated.
322 -- p_err_stage - Standard error message
323 -- p_err_stack - Not used.
324 --
325
326 PROCEDURE Is_Budget_WF_Used
327 ( p_project_id IN NUMBER
328 , p_award_id IN NUMBER
329 , p_budget_type_code IN VARCHAR2
330 , p_pm_product_code IN VARCHAR2
331 , p_result IN OUT NOCOPY VARCHAR2
332 , p_err_code IN OUT NOCOPY NUMBER
333 , p_err_stage IN OUT NOCOPY VARCHAR2
334 , p_err_stack IN OUT NOCOPY VARCHAR2
335 )
336
337 IS
338 --
339
340 BEGIN
341
342 GMS_CLIENT_EXTN_BUDGET_WF.IS_BUDGET_WF_USED
343 ( p_project_id => p_project_id
344 , p_award_id => p_award_id
345 , p_budget_type_code => p_budget_type_code
346 , p_pm_product_code => p_pm_product_code
347 , p_result => p_result
348 , p_err_code => p_err_code
349 , p_err_stage => p_err_stage
350 , p_err_stack => p_err_stack
351 );
352
353
354 EXCEPTION
355
356 WHEN OTHERS
357 THEN
358 p_err_code := SQLCODE;
359 RAISE;
360
361
362 END Is_Budget_WF_Used;
363
364 -- =================================================
365 -- Name: Reject_Budget
366 -- Type: Procedure
367 -- Description: This procedure resets a given project-budget status
368 -- to a 'Working', 'Rejected'.
369 --
370 --
371 --
372 -- Called subprograms: NONE
373 --
374 --
375 --
376 -- History:
377 --
378 -- IN
379 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
380 -- itemkey - A string generated from the application object's primary key.
381 -- actid - The notIFication process activity(instance id).
382 -- funcmode - Run/Cancel
383 -- OUT NOCOPY
384 -- Resultout - NULL
385 --
386
387 PROCEDURE Reject_Budget
388 (itemtype IN VARCHAR2
389 , itemkey IN VARCHAR2
390 , actid IN NUMBER
391 , funcmode IN VARCHAR2
392 , resultout OUT NOCOPY VARCHAR2
393 )
394 --
395 IS
396 --
397 -- ROW LOCKING
398
399 CURSOR l_lock_budget_csr (p_project_id NUMBER, p_award_id NUMBER, p_budget_type_code VARCHAR2)
400 IS
401 SELECT 'x'
402 FROM gms_budget_versions
403 WHERE project_id = p_project_id
404 AND award_id = p_award_id
405 AND budget_type_code = p_budget_type_code
406 AND budget_status_code = 'S'
407 FOR UPDATE NOWAIT;
408
409 -- Local Variables
410
411 l_project_id NUMBER;
412 l_award_id NUMBER;
413 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
414
415 l_err_code NUMBER := 0;
416 l_msg_count NUMBER;
417 l_msg_data VARCHAR(2000);
418 l_return_status VARCHAR2(1);
419 l_data VARCHAR2(2000);
420 l_msg_index_out NUMBER;
421 l_api_version_number NUMBER := G_api_version_number;
422
423
424 --
425 BEGIN
426 -- Return if WF Not Running
427
428 IF (funcmode <> wf_engine.eng_run) THEN
429 resultout := wf_engine.eng_null;
430 RETURN;
431 END IF;
432
433
434 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
435
436
437 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
438 itemkey => itemkey,
439 aname => 'PROJECT_ID' );
440
441 l_award_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'AWARD_ID' );
444
445 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'BUDGET_TYPE_CODE' );
448
449 -- SET GLOBALS ------------------------------------------------------------------
450
451 -- Based on the Responsibility, Intialize the Application
452 GMS_WORKFLOW_UTILS.Set_Global_Attr
453 (p_item_type => itemtype
454 , p_item_key => itemkey
455 , p_err_code => l_err_code);
456 --Setting OU Context
457 GMS_BUDGET_UTILS.Set_Award_Policy_Context (p_award_id => l_award_id
458 ,x_return_status => l_return_status
459 ,x_msg_count => l_msg_count
460 ,x_msg_data => l_msg_data
461 ,x_err_code => l_err_code
462 );
463
464 IF (l_err_code <> 0)
465 THEN
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467 END IF;
468
469
470
471 -- REVERT STATUS of Project-Budget to 'Working' , 'REJECTED' -----------------
472
473 -- LOCK Draft Budget Version
474
475 OPEN l_lock_budget_csr(l_project_id, l_award_id, l_budget_type_code);
476 CLOSE l_lock_budget_csr;
477
478 -- UPDATE Draft Budget Version
479
480 UPDATE gms_budget_versions
481 SET budget_status_code = 'W', WF_status_code = 'REJECTED'
482 WHERE project_id = l_project_id
483 AND award_id = l_award_id
484 AND budget_type_code = l_budget_type_code
485 AND budget_status_code = 'S';
486
487
488 resultout := wf_engine.eng_completed;
489
490
491 EXCEPTION
492
493 WHEN FND_API.G_EXC_ERROR
494 THEN
495 WF_CORE.CONTEXT('GMS_WF_PKG','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
496 RAISE;
497
498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
499 THEN
500 WF_CORE.CONTEXT('GMS_WF_PKG','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
501 RAISE;
502
503 WHEN OTHERS
504 THEN
505 WF_CORE.CONTEXT('GMS_WF_PKG','REJECT_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
506 RAISE;
507
508
509
510 END Reject_Budget;
511
512 -- =================================================
513 -- Name: Select_Budget_Approver
514 -- Type: Procedure
515 -- Description: This procedure will call a client extension that will return the
516 -- correct ID of the person that must approve a budget
517 -- for baselining.
518 --
519 --
520 -- Called subprograms: GMS_CLIENT_EXTN_BUDGET_WF.select_budget_approver
521 --
522 --
523 --
524 --History:
525 --
526 -- IN
527 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
528 -- itemkey - A string generated from the application object's primary key.
529 -- actid - The notIFication process activity(instance id).
530 -- funcmode - Run/Cancel
531 -- OUT NOCOPY
532 -- Resultout - T/F
533 --
534 PROCEDURE Select_Budget_Approver
535 (itemtype IN VARCHAR2
536 , itemkey IN VARCHAR2
537 , actid IN NUMBER
538 , funcmode IN VARCHAR2
539 , resultout OUT NOCOPY VARCHAR2
540 )
541
542 IS
543 --
544 CURSOR l_baseliner_user_csr( p_baseliner_id NUMBER )
545 IS
546 SELECT f.user_id
547 , f.user_name
548 , p.first_name||' '||p.last_name
549 FROM fnd_user f, per_people_f p /*Bug 5122724 */
550 WHERE p.effective_start_date = (SELECT min(pp.effective_start_date)
551 FROM per_all_people_f pp where pp.person_id = p.person_id
552 AND pp.effective_end_date >=trunc(sysdate))
553 AND ((p.employee_number is not null) OR (p.npw_number is not null))
554 AND f.employee_id = p_baseliner_id
555 AND f.employee_id = p.person_id;
556 --
557
558 l_workflow_started_by_id NUMBER;
559 l_project_id NUMBER;
560 l_award_id NUMBER;
561 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
562
563 l_baseliner_employee_id NUMBER;
564
565 l_baseliner_user_id NUMBER;
566 l_baseliner_user_name VARCHAR2(100);
567 l_baseliner_full_name VARCHAR2(240);
568
569 l_err_code NUMBER := 0;
570 l_msg_count NUMBER;
571 l_msg_data VARCHAR(2000);
572 l_return_status VARCHAR2(1);
573 l_data VARCHAR2(2000);
574 l_msg_index_out NUMBER;
575 l_api_version_number NUMBER := G_api_version_number;
576
577 --
578 BEGIN
579 --
580 -- Return if WF Not Running
581 --
582 IF (funcmode <> wf_engine.eng_run) THEN
583 --
584 resultout := wf_engine.eng_null;
585 RETURN;
586 --
587 END IF;
588 --
589
590 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
591
592 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
593 itemkey => itemkey,
594 aname => 'PROJECT_ID' );
595
596 l_award_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
597 itemkey => itemkey,
598 aname => 'AWARD_ID' );
599
600 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
601 itemkey => itemkey,
602 aname => 'WORKFLOW_STARTED_BY_ID' );
603
604 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
605 itemkey => itemkey,
606 aname => 'BUDGET_TYPE_CODE' );
607
608 -- SET GLOBALS ------------------------------------------------------------------
609
610 -- Based on the Responsibility, Intialize the Application
611 GMS_WORKFLOW_UTILS.Set_Global_Attr
612 (p_item_type => itemtype
613 , p_item_key => itemkey
614 , p_err_code => l_err_code);
615
616 --Setting the OU Context
617 GMS_BUDGET_UTILS.Set_Award_Policy_Context
618 (p_award_id => l_award_id
619 ,x_return_status => l_return_status
620 ,x_msg_count => l_msg_count
621 ,x_msg_data => l_msg_data
622 ,x_err_code => l_err_code
623 );
624
625 IF (l_err_code <> 0)
626 THEN
627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628 END IF;
629
630
631 GMS_CLIENT_EXTN_BUDGET_WF.Select_Budget_Approver
632 (p_item_type => itemtype
633 ,p_item_key => itemkey
634 ,p_project_id => l_project_id
635 ,p_award_id => l_award_id
636 ,p_budget_type_code => l_budget_type_code
637 ,p_workflow_started_by_id => l_workflow_started_by_id
638 ,p_budget_baseliner_id => l_baseliner_employee_id
639 );
640
641
642 --ISSUE: a employee can have several users attached to it. So, this
643 -- Code Retrieves the First User.
644
645
646 IF (l_baseliner_employee_id IS NOT NULL)
647 THEN
648
649 OPEN l_baseliner_user_csr( l_baseliner_employee_id );
650 FETCH l_baseliner_user_csr
651 INTO l_baseliner_user_id
652 ,l_baseliner_user_name
653 ,l_baseliner_full_name;
654
655 IF (l_baseliner_user_csr%FOUND) 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 resultout := wf_engine.eng_completed||':'||'T';
677 ELSE
678
679 CLOSE l_baseliner_user_csr;
680 resultout := wf_engine.eng_completed||':'||'F';
681 END IF;
682 ELSE
683
684 resultout := wf_engine.eng_completed||':'||'F';
685 END IF;
686
687
688 EXCEPTION
689
690 WHEN FND_API.G_EXC_ERROR
691 THEN
692 WF_CORE.CONTEXT('GMS_WF_PKG','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
693 RAISE;
694
695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
696 THEN
697 WF_CORE.CONTEXT('GMS_WF_PKG','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
698 RAISE;
699
700 WHEN OTHERS
701 THEN
702 WF_CORE.CONTEXT('GMS_WF_PKG','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
703 RAISE;
704
705
706 END Select_Budget_Approver;
707
708
709 -- ==================================================
710 --Name Verify_Budget_Rules
711 --Type: Procedure
712 --Description: This procedure will call a client extension that will return a
713 -- 'T' or 'F', depending on whether all defined rules were met.
714 --
715 --
716 --Called subprograms: GMS_BUDGET_UTILS.Verify_Budget_Rules
717 -- GMS_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
718 --
719 --
720 --
721 --History:
722 --
723 -- IN
724 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
725 -- itemkey - A string generated from the application object's primary key.
726 -- actid - The notIFication process activity(instance id).
727 -- funcmode - Run/Cancel
728 -- OUT NOCOPY
729 -- Resultout - T/F
730 --
731 PROCEDURE Verify_Budget_Rules
732 ( itemtype in varchar2
733 , itemkey in varchar2
734 , actid in number
735 , funcmode in varchar2
736 , resultout out NOCOPY varchar2
737 )
738
739 IS
740 --
741
742 -- Cursor for Verify_Budget_Rules
743 CURSOR l_budget_rules_csr(p_project_id NUMBER, p_award_id NUMBER, p_budget_type_code VARCHAR2)
744 IS
745 SELECT v.budget_version_id
746 FROM gms_budget_versions v
747 WHERE v.project_id = p_project_id
748 AND v.award_id = p_award_id
749 AND v.budget_type_code = p_budget_type_code
750 AND v.budget_status_code in ('S','W');
751
752
753 -- Local Variables
754 l_workflow_started_by_id NUMBER;
755 l_project_id NUMBER;
756 l_award_id NUMBER;
757 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
758
759
760 l_warnings_only_flag VARCHAR2(1);
761 l_warnings_only VARCHAR2(1) := 'Y';
762 l_err_msg_count NUMBER := 0;
763 l_budget_version_id NUMBER;
764 l_mark_as_original gms_budget_versions.current_original_flag%TYPE;
765 l_resource_list_id NUMBER;
766 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
767
768 l_msg_count NUMBER;
769 l_msg_data VARCHAR(2000);
770 l_return_status VARCHAR2(1);
771 l_data VARCHAR2(2000);
772 l_msg_index_out NUMBER;
773 l_api_version_number NUMBER := G_api_version_number;
774
775 l_err_code NUMBER := 0;
776 l_err_stage VARCHAR2(120);
777 l_err_stack VARCHAR2(630);
778
779
780
781 --
782 BEGIN
783 --
784 -- Return if WF Not Running
785 --
786 IF (funcmode <> wf_engine.eng_run) THEN
787 --
788 resultout := wf_engine.eng_null;
789 RETURN;
790 --
791 END IF;
792 --
793
794 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
795
796 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
797 itemkey => itemkey,
798 aname => 'PROJECT_ID' );
799
800 l_award_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
801 itemkey => itemkey,
802 aname => 'AWARD_ID' );
803
804 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
805 itemkey => itemkey,
806 aname => 'WORKFLOW_STARTED_BY_ID' );
807
808 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'BUDGET_TYPE_CODE' );
811
812 l_mark_as_original := wf_engine.GetItemAttrText(itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'MARK_AS_ORIGINAL' );
815
816 l_resource_list_id := wf_engine.GetItemAttrText(itemtype => itemtype,
817 itemkey => itemkey,
818 aname =>'RESOURCE_LIST_ID');
819
820 l_project_type_class_code := wf_engine.GetItemAttrText(itemtype => itemtype,
821 itemkey => itemkey,
822 aname =>'PROJECT_TYPE_CLASS_CODE');
823
824 -- SET GLOBALS -----------------------------------------------------------------
825
826 -- Based on the Responsibility, Intialize the Application
827 GMS_WORKFLOW_UTILS.Set_Global_Attr
828 (p_item_type => itemtype
829 , p_item_key => itemkey
830 , p_err_code => l_err_code);
831
832
833
834 -- ------------------------------------------------------------------------------------
835 -- NON-WF Verify Budget Rules
836 -- ------------------------------------------------------------------------------------
837
838 -- Retrieve Required IN-parameters for Verify_Budget_Rules Calls
839
840 OPEN l_budget_rules_csr(l_project_id, l_award_id, l_budget_type_code);
841
842
843 FETCH l_budget_rules_csr INTO l_budget_version_id;
844
845 IF ( l_budget_rules_csr%NOTFOUND)
846 THEN
847
848 -- jjj - use gms_messages utility instead of PA's
849
850 PA_UTILS.Add_Message
851 ( p_app_short_name => 'GMS'
852 , p_msg_name => 'GMS_NO_BUDGET_RULES_ATTR'
853 );
854
855 GMS_WORKFLOW_UTILS.Set_Notification_Messages
856 (p_item_type => itemtype
857 , p_item_key => itemkey
858 );
859
860 resultout := wf_engine.eng_completed||':'||'F';
861 CLOSE l_budget_rules_csr;
862 RETURN;
863 END IF;
864
865 CLOSE l_budget_rules_csr;
866
867
868 -- SUBMISSION RULES -------------------------------------------------------------
869 --dbms_output.put_line('Verify Budget Rules - SUBMIT');
870
871
872 GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES
873 (p_draft_version_id => l_budget_version_id
874 , p_mark_as_original => l_mark_as_original
875 , p_event => 'SUBMIT'
876 , p_project_id => l_project_id
877 , p_award_id => l_award_id
878 , p_budget_type_code => l_budget_type_code
879 , p_resource_list_id => l_resource_list_id
880 , p_project_type_class_code => l_project_type_class_code
881 , p_created_by => l_workflow_started_by_id
882 , p_calling_module => 'GMSFBUVB'
883 , p_warnings_only_flag => l_warnings_only_flag
884 , p_err_msg_count => l_err_msg_count
885 , p_err_code => l_err_code
886 , p_err_stage => l_err_stage
887 , p_err_stack => l_err_stack
888 );
889
890 IF (l_err_msg_count > 0 )
891 THEN
892 GMS_WORKFLOW_UTILS.Set_Notification_Messages
893 (p_item_type => itemtype
894 , p_item_key => itemkey
895 );
896 IF (l_warnings_only_flag = 'N') THEN
897 l_warnings_only := 'N';
898 END IF;
899 END IF;
900
901
902
903 -- BASELINE RULES -------------------------------------------------------------
904
905 GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES
906 (p_draft_version_id => l_budget_version_id
907 , p_mark_as_original => l_mark_as_original
908 , p_event => 'BASELINE'
909 , p_project_id => l_project_id
910 , p_award_id => l_award_id
911 , p_budget_type_code => l_budget_type_code
912 , p_resource_list_id => l_resource_list_id
913 , p_project_type_class_code => l_project_type_class_code
914 , p_created_by => l_workflow_started_by_id
915 , p_calling_module => 'GMSFBUVB'
916 , p_warnings_only_flag => l_warnings_only_flag
917 , p_err_msg_count => l_err_msg_count
918 , p_err_code => l_err_code
919 , p_err_stage => l_err_stage
920 , p_err_stack => l_err_stack
921 );
922
923 IF (l_err_msg_count > 0 )
924 THEN
925 GMS_WORKFLOW_UTILS.Set_Notification_Messages
926 (p_item_type => itemtype
927 , p_item_key => itemkey
928 );
929 IF (l_warnings_only_flag = 'N') THEN
930 l_warnings_only := 'N';
931 END IF;
932 END IF;
933
934
935 -- ------------------------------------------------------------------------------------
936 -- WORKFLOW Verify Budget Rules
937 -- ------------------------------------------------------------------------------------
938
939 GMS_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
940 (p_item_type => itemtype
941 , p_item_key => itemkey
942 , p_project_id => l_project_id
943 , p_award_id => l_award_id
944 , p_budget_type_code => l_budget_type_code
945 , p_workflow_started_by_id => l_workflow_started_by_id
946 , p_event => 'SUBMIT'
947 , p_warnings_only_flag => l_warnings_only_flag
948 , p_err_msg_count => l_err_msg_count
949 );
950
951
952 IF (l_err_msg_count > 0 )
953 THEN
954 GMS_WORKFLOW_UTILS.Set_Notification_Messages
955 (p_item_type => itemtype
956 , p_item_key => itemkey
957 );
958 IF (l_warnings_only_flag = 'N') THEN
959 l_warnings_only := 'N';
960 END IF;
961 END IF;
962
963 GMS_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
964 (p_item_type => itemtype
965 , p_item_key => itemkey
966 , p_project_id => l_project_id
967 , p_award_id => l_award_id
968 , p_budget_type_code => l_budget_type_code
969 , p_workflow_started_by_id => l_workflow_started_by_id
970 , p_event => 'BASELINE'
971 , p_warnings_only_flag => l_warnings_only_flag
972 , p_err_msg_count => l_err_msg_count
973 );
974
975
976 IF (l_err_msg_count > 0 )
977 THEN
978 GMS_WORKFLOW_UTILS.Set_Notification_Messages
979 (p_item_type => itemtype
980 , p_item_key => itemkey
981 );
982 IF (l_warnings_only_flag = 'N') THEN
983 l_warnings_only := 'N';
984 END IF;
985 END IF;
986
987
988 IF (l_warnings_only = 'Y')
989 THEN
990 resultout := wf_engine.eng_completed||':'||'T';
991 ELSE
992 resultout := wf_engine.eng_completed||':'||'F';
993 END IF;
994
995 --
996
997 EXCEPTION
998
999 WHEN FND_API.G_EXC_ERROR
1000 THEN
1001 WF_CORE.CONTEXT('GMS_WF_PKG','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1002 RAISE;
1003
1004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1005 THEN
1006 WF_CORE.CONTEXT('GMS_WF_PKG','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1007 RAISE;
1008
1009 WHEN OTHERS THEN
1010 WF_CORE.CONTEXT('GMS_WF_PKG','VERIFY_BUDGET_RULES', itemtype, itemkey, to_char(actid), funcmode);
1011 RAISE;
1012
1013
1014 END Verify_Budget_Rules;
1015
1016 -- ==================================================
1017 --Name: Baseline_Budget
1018 --Type: Procedure
1019 --Description: This procedures performs BASELINE verification,
1020 -- baseline functionality via the core baseline
1021 -- procedure, and directly updates the draft budget.
1022 --
1023 --
1024 --
1025 --
1026 --Called subprograms: GMS_BUDGET_UTILS.Verify_Budget_Rules
1027 -- , GMS_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1028 -- , GMS_BUDGET_CORE.Baseline
1029 -- , GMS_WORKFLOW_UTILS.Insert_WF_Processes
1030 --
1031 --
1032 --
1033 --History:
1034 --
1035 -- IN
1036 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1037 -- itemkey - A string generated from the application object's primary key.
1038 -- actid - The notIFication process activity(instance id).
1039 -- funcmode - Run/Cancel
1040 -- OUT NOCOPY
1041 -- Resultout - T/F
1042 --
1043 --
1044 PROCEDURE Baseline_Budget
1045 (itemtype IN VARCHAR2
1046 , itemkey IN VARCHAR2
1047 , actid IN NUMBER
1048 , funcmode IN VARCHAR2
1049 , resultout OUT NOCOPY VARCHAR2
1050 )
1051 IS
1052
1053 -- Cursor for Insert_WF_Processes Call
1054 CURSOR l_baseline_csr
1055 ( p_project_id NUMBER
1056 , p_award_id NUMBER
1057 , p_budget_type_code VARCHAR2 )
1058
1059 IS
1060 SELECT MAX(budget_version_id)
1061 FROM gms_budget_versions
1062 WHERE project_id = p_project_id
1063 AND award_id = p_award_id
1064 AND budget_type_code = p_budget_type_code
1065 AND budget_status_code = 'B';
1066
1067 -- Bug 5162777 : The cursors l_time_phased_type_csr and l_grp_resource_type_csr are removed as they are never used in this procedure.
1068
1069
1070 l_workflow_started_by_id NUMBER;
1071 l_baseliner_id NUMBER;
1072 l_project_id NUMBER;
1073 l_award_id NUMBER;
1074 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1075 l_time_phased_type_code VARCHAR2(30);
1076
1077 l_row_found NUMBER;
1078 l_budget_version_id NUMBER;
1079 l_baselined_version_id NUMBER;
1080 l_draft_version_id NUMBER;
1081
1082 l_app_short_name VARCHAR2(30);
1083 l_count NUMBER; -- used by the Budgetary Control Setup process.
1084 l_entry_level_code VARCHAR2(30); -- used by the Budgetary Control Setup process.
1085 l_group_resource_type_id NUMBER; -- used by the Budgetary Control Setup process.
1086
1087 l_api_version_number NUMBER := G_api_version_number;
1088 l_msg_count NUMBER;
1089 l_msg_data VARCHAR(2000);
1090 l_return_status VARCHAR2(1);
1091 l_pm_product_code pa_projects.pm_product_code%TYPE :='WORKFLOW';
1092 l_data VARCHAR2(2000);
1093 l_msg_index_out NUMBER;
1094
1095 l_warnings_only_flag VARCHAR2(1);
1096 l_warnings_only VARCHAR2(1) := 'Y';
1097 l_err_msg_count NUMBER;
1098 l_mark_as_original gms_budget_versions.current_original_flag%TYPE;
1099 l_resource_list_id NUMBER;
1100 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
1101
1102 l_err_code NUMBER := 0;
1103 l_err_stage VARCHAR2(120) := NULL;
1104 l_err_stack VARCHAR2(630);
1105
1106
1107
1108 --
1109 BEGIN
1110
1111
1112 --
1113 -- Return if WF Not Running
1114 --
1115 IF (funcmode <> wf_engine.eng_run) THEN
1116 --
1117 resultout := wf_engine.eng_null;
1118 RETURN;
1119 --
1120 END IF;
1121 --
1122
1123
1124 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
1125
1126 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1127 itemkey => itemkey,
1128 aname => 'PROJECT_ID' );
1129
1130 l_award_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1131 itemkey => itemkey,
1132 aname => 'AWARD_ID' );
1133
1134 l_budget_type_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1135 itemkey => itemkey,
1136 aname => 'BUDGET_TYPE_CODE' );
1137
1138 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1139 itemkey => itemkey,
1140 aname => 'WORKFLOW_STARTED_BY_ID' );
1141
1142
1143 l_baseliner_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1144 itemkey => itemkey,
1145 aname => 'BUDGET_BASELINER_ID' );
1146
1147 l_mark_as_original := wf_engine.GetItemAttrText(itemtype => itemtype,
1148 itemkey => itemkey,
1149 aname => 'MARK_AS_ORIGINAL' );
1150
1151 l_resource_list_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1152 itemkey => itemkey,
1153 aname =>'RESOURCE_LIST_ID');
1154
1155 l_project_type_class_code := wf_engine.GetItemAttrText(itemtype => itemtype,
1156 itemkey => itemkey,
1157 aname =>'PROJECT_TYPE_CLASS_CODE');
1158
1159 -- SET GLOBALS -----------------------------------------------------------------
1160
1161 -- Based on the Responsibility, Intialize the Application
1162 GMS_WORKFLOW_UTILS.Set_Global_Attr
1163 (p_item_type => itemtype
1164 , p_item_key => itemkey
1165 , p_err_code => l_err_code);
1166
1167 --Setting OU Context
1168 GMS_BUDGET_UTILS.Set_Award_Policy_Context
1169 (p_award_id => l_award_id
1170 ,x_return_status => l_return_status
1171 ,x_msg_count => l_msg_count
1172 ,x_msg_data => l_msg_data
1173 ,x_err_code => l_err_code
1174 );
1175
1176 IF (l_err_code <> 0)
1177 THEN
1178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1179 END IF;
1180
1181 -- Get The Budget Version ID Associated With This Project/Award/Budget_Type_Code Combination
1182
1183 OPEN l_budget_versions_csr ( l_project_id, l_award_id, l_budget_type_code );
1184 FETCH l_budget_versions_csr INTO l_budget_version_id;
1185
1186
1187 IF ( l_budget_versions_csr%NOTFOUND)
1188 THEN
1189
1190 PA_UTILS.Add_Message
1191 ( p_app_short_name => 'GMS'
1192 , p_msg_name => 'GMS_NO_BUDGET_RULES_ATTR'
1193 );
1194
1195 GMS_WORKFLOW_UTILS.Set_Notification_Messages
1196 (p_item_type => itemtype
1197 , p_item_key => itemkey
1198 );
1199 resultout := wf_engine.eng_completed||':'||'F';
1200 CLOSE l_budget_versions_csr;
1201 RETURN ;
1202 END IF;
1203
1204 CLOSE l_budget_versions_csr;
1205
1206 -- ------------------------------------------------------------------------------------
1207 -- NON-WF Verify Budget Rules
1208 -- ------------------------------------------------------------------------------------
1209
1210 GMS_BUDGET_UTILS.VERIFY_BUDGET_RULES
1211 (p_draft_version_id => l_budget_version_id
1212 , p_mark_as_original => l_mark_as_original
1213 , p_event => 'BASELINE'
1214 , p_project_id => l_project_id
1215 , p_award_id => l_award_id
1216 , p_budget_type_code => l_budget_type_code
1217 , p_resource_list_id => l_resource_list_id
1218 , p_project_type_class_code => l_project_type_class_code
1219 , p_created_by => l_workflow_started_by_id
1220 , p_calling_module => 'GMSFBUVB'
1221 , p_warnings_only_flag => l_warnings_only_flag
1222 , p_err_msg_count => l_err_msg_count
1223 , p_err_code => l_err_code
1224 , p_err_stage => l_err_stage
1225 , p_err_stack => l_err_stack
1226 );
1227
1228 IF (l_err_msg_count > 0 )
1229 THEN
1230 GMS_WORKFLOW_UTILS.Set_Notification_Messages
1231 (p_item_type => itemtype
1232 , p_item_key => itemkey
1233 );
1234 IF (l_warnings_only_flag = 'N') THEN
1235 l_warnings_only := 'N';
1236 END IF;
1237 END IF;
1238
1239
1240 -- ------------------------------------------------------------------------------------
1241 -- WORKFLOW Verify Budget Rules
1242 -- ------------------------------------------------------------------------------------
1243
1244 GMS_CLIENT_EXTN_BUDGET_WF.Verify_Budget_Rules
1245 (p_item_type => itemtype
1246 , p_item_key => itemkey
1247 , p_project_id => l_project_id
1248 , p_award_id => l_award_id
1249 , p_budget_type_code => l_budget_type_code
1250 , p_workflow_started_by_id => l_workflow_started_by_id
1251 , p_event => 'BASELINE'
1252 , p_warnings_only_flag => l_warnings_only_flag
1253 , p_err_msg_count => l_err_msg_count
1254 );
1255
1256
1257 IF (l_err_msg_count > 0 )
1258 THEN
1259 GMS_WORKFLOW_UTILS.Set_Notification_Messages
1260 (p_item_type => itemtype
1261 , p_item_key => itemkey
1262 );
1263 IF (l_warnings_only_flag = 'N') THEN
1264 l_warnings_only := 'N';
1265 END IF;
1266 END IF;
1267
1268 -- ---------------------------------------------------------------------------------------
1269 -- BASELINE THIS BUDGET VERSION
1270 -- Make sure verify budget rules NOT called again:
1271 -- x_verify_budget_rules => 'N'
1272 -- ---------------------------------------------------------------------------------------
1273
1274 IF (l_warnings_only = 'Y')
1275 THEN
1276
1277 GMS_BUDGET_CORE.Baseline ( x_draft_version_id => l_budget_version_id
1278 ,x_mark_as_original => l_mark_as_original
1279 ,x_verify_budget_rules => 'N'
1280 ,x_err_code => l_err_code
1281 ,x_err_stage => l_err_stage
1282 ,x_err_stack => l_err_stack
1283 );
1284
1285
1286 -- All Errors Should Be Unexpected Errors. However,
1287 -- Oracle Errors will be Captured by the Procedure
1288 -- Exception. This Code can only Capture
1289 -- Business Errors.
1290
1291 IF (l_err_code <> 0)
1292 THEN
1293 WF_CORE.CONTEXT('GMS_WF_PKG','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1294 RAISE FND_API.G_EXC_ERROR;
1295 END IF;
1296
1297
1298 --
1299 -- Insert a Row into the GMS_WF_PROCESSES Table
1300 -- to Record the Workflow Associated with the Baselined
1301 -- Budget
1302 --
1303
1304 OPEN l_baseline_csr ( l_project_id, l_award_id, l_budget_type_code );
1305 FETCH l_baseline_csr INTO l_baselined_version_id;
1306
1307 -- Extensive Error Checking Not Required Because The Baselined Version Was Just
1308 -- Created By The gms_Budget_Core.Baseline Call.
1309
1310
1311 GMS_WORKFLOW_UTILS.Insert_WF_Processes
1312 (p_wf_type_code => 'BUDGET'
1313 ,p_item_type => itemtype
1314 ,p_item_key => itemkey
1315 ,p_entity_key1 => to_char(l_budget_version_id)
1316 ,p_entity_key2 => to_char(l_baselined_version_id)
1317 ,p_description => NULL
1318 ,p_err_code => l_err_code
1319 ,p_err_stage => l_err_stage
1320 ,p_err_stack => l_err_stack
1321 );
1322
1323 IF (l_err_code <> 0)
1324 THEN
1325 WF_CORE.CONTEXT('GMS_WF_PKG','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1327 END IF;
1328
1329 END IF ; -- OK to Baseline
1330
1331
1332
1333 IF (l_warnings_only = 'Y')
1334 THEN
1335 resultout := wf_engine.eng_completed||':'||'T';
1336 ELSE
1337 resultout := wf_engine.eng_completed||':'||'F';
1338 END IF;
1339
1340 EXCEPTION
1341
1342 WHEN FND_API.G_EXC_ERROR
1343 THEN
1344 WF_CORE.CONTEXT('GMS_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1345 RAISE;
1346
1347 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1348 THEN
1349 WF_CORE.CONTEXT('GMS_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1350 RAISE;
1351
1352 WHEN OTHERS THEN
1353 WF_CORE.CONTEXT('GMS_BUDGET_WF','BASELINE_BUDGET', itemtype, itemkey, to_char(actid), funcmode);
1354 RAISE;
1355
1356 END Baseline_Budget;
1357
1358 ----------------------------------------------------------------------------------------
1359 -- Name: Select_WF_Process
1360 -- Type: Procedure
1361 -- Description: This procedure is used to select the branch of the WF process
1362 -- (Budget/Installment/Report) based on the Item_Attribute that
1363 -- is sent from the calling program.
1364 --
1365 --History:
1366 --
1367 -- IN
1368 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1369 -- itemkey - A string generated from the application object's primary key.
1370 -- actid - The notIFication process activity(instance id).
1371 -- funcmode - Run/Cancel
1372 -- OUT NOCOPY
1373 -- Resultout - BUDGET or INSTALLMENT or REPORT
1374 --
1375 --
1376 PROCEDURE select_wf_process ( itemtype in varchar2,
1377 itemkey in varchar2,
1378 actid in number,
1379 funcmode in varchar2,
1380 resultout out NOCOPY varchar2 )
1381 is
1382 x_gms_wf_process varchar2(25);
1383 begin
1384
1385 if (funcmode <> wf_engine.eng_run) then
1386
1387 resultout := wf_engine.eng_null;
1388 return;
1389
1390 end if;
1391
1392 x_gms_wf_process := wf_engine.GetItemAttrText
1393 ( itemtype => itemtype,
1394 itemkey => itemkey,
1395 aname => 'GMS_WF_PROCESS');
1396 if x_gms_wf_process = 'BUDGET' then
1397 resultout := 'COMPLETE:BUDGET';
1398 elsif x_gms_wf_process = 'BUDGET_NTFY_ONLY' then
1399 resultout := 'COMPLETE:BUDGET_NTFY_ONLY';
1400 elsif x_gms_wf_process = 'INSTALLMENT' then
1401 resultout := 'COMPLETE:INSTALLMENT';
1402 elsif x_gms_wf_process = 'REPORT' then
1403 resultout := 'COMPLETE:REPORT';
1404 --Start : Build of the installment closeout Notification Bug # 1969587
1405 elsif x_gms_wf_process = 'INSTALLMENT_CLOSEOUT' then
1406 resultout := 'COMPLETE:INSTALLMENT_CLOSEOUT';
1407 --Start : Build of the installment closeout Notification Bug # 1969587
1408 end if;
1409
1410
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 wf_core.context('GMS_BUDGET_WF', 'SELECT_WF_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
1414 raise;
1415 end select_wf_process;
1416 ----------------------------------------------------------------------------------------
1417
1418 -- Name: Funds_check
1419 -- Type: Procedure
1420 -- Description: This procedure is used to invoke the GMS Funds check process from
1421 -- the GMS Workflow process
1422 --History:
1423 --
1424 -- IN
1425 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1426 -- itemkey - A string generated from the application object's primary key.
1427 -- actid - The notIFication process activity(instance id).
1428 -- funcmode - Run/Cancel
1429 -- OUT NOCOPY
1430 -- Resultout - COMPLETE:FUNDSCHECK_PASS or COMPLETE:FUNDSCHECK_FAIL
1431 --
1432 --
1433
1434 PROCEDURE Funds_check
1435 (itemtype IN VARCHAR2
1436 , itemkey IN VARCHAR2
1437 , actid IN NUMBER
1438 , funcmode IN VARCHAR2
1439 , resultout OUT NOCOPY VARCHAR2
1440 )
1441
1442 IS
1443
1444 l_workflow_started_by_id NUMBER;
1445 l_project_id NUMBER;
1446 l_award_id NUMBER;
1447 l_mode VARCHAR2(3);
1448 l_retcode VARCHAR2(1);
1449 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1450 l_baselined_version_id NUMBER;
1451 l_prev_baselined_version_id NUMBER;
1452 l_budget_version_id NUMBER;
1453 l_prev_entry_level_code pa_budget_entry_methods.entry_level_code%type;
1454 l_time_phased_type_code VARCHAR2(30);
1455 l_count NUMBER;
1456 l_packet_id NUMBER;
1457 l_app_short_name VARCHAR2(30);
1458 l_group_resource_type_id NUMBER;
1459 l_entry_level_code VARCHAR2(30);
1460 l_resource_list_id NUMBER;
1461
1462
1463 l_baseliner_employee_id NUMBER;
1464
1465 l_baseliner_user_id NUMBER;
1466 l_baseliner_user_name VARCHAR2(100);
1467 l_baseliner_full_name VARCHAR2(240);
1468
1469 l_err_code VARCHAR2(630);
1470 l_err_stage VARCHAR2(630);
1471
1472 l_msg_count NUMBER;
1473 l_msg_data VARCHAR(2000);
1474 l_return_status VARCHAR2(1);
1475 l_data VARCHAR2(2000);
1476 l_msg_index_out NUMBER;
1477 l_api_version_number NUMBER := G_api_version_number;
1478
1479 l_user_profile_value1 VARCHAR2(30);
1480 l_set_profile_success1 BOOLEAN := FALSE;
1481 l_user_profile_value2 VARCHAR2(30);
1482 l_set_profile_success2 BOOLEAN := FALSE;
1483
1484
1485
1486 -- Cursor for Summarizing Project Budgets -- 24-May-2000
1487
1488 CURSOR l_time_phased_type_csr ( p_budget_version_id NUMBER)
1489 IS
1490 SELECT pbem.time_phased_type_code,
1491 pbem.entry_level_code
1492 FROM gms_budget_versions gbv,
1493 pa_budget_entry_methods pbem
1494 WHERE gbv.budget_version_id = p_budget_version_id
1495 AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
1496
1497 -- Cursor for Budgetary Control Default Setup -- 25-May-2000
1498
1499 CURSOR l_grp_resource_type_csr ( p_budget_version_id NUMBER)
1500 IS
1501 SELECT prl.group_resource_type_id,
1502 gbv.resource_list_id
1503 FROM gms_budget_versions gbv,
1504 pa_resource_lists prl
1505 WHERE gbv.budget_version_id = p_budget_version_id
1506 AND gbv.resource_list_id = prl.resource_list_id;
1507
1508
1509 BEGIN
1510 -- Return if WF Not Running
1511
1512 IF (funcmode <> wf_engine.eng_run) THEN
1513 resultout := wf_engine.eng_null;
1514 RETURN;
1515 END IF;
1516
1517 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
1518
1519 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1520 itemkey => itemkey,
1521 aname => 'PROJECT_ID' );
1522
1523 l_award_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1524 itemkey => itemkey,
1525 aname => 'AWARD_ID' );
1526
1527 l_budget_type_code := wf_engine.GetItemAttrText( itemtype => itemtype,
1528 itemkey => itemkey,
1529 aname => 'BUDGET_TYPE_CODE' );
1530
1531 l_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
1532 itemkey => itemkey,
1533 aname => 'FC_MODE' );
1534
1535
1536 -- SET GLOBALS ------------------------------------------------------------------
1537
1538 -- Based on the Responsibility, Intialize the Application
1539 GMS_WORKFLOW_UTILS.Set_Global_Attr
1540 (p_item_type => itemtype
1541 , p_item_key => itemkey
1542 , p_err_code => l_err_code);
1543
1544 --Setting the OU Context
1545 GMS_BUDGET_UTILS.Set_Award_Policy_Context (p_award_id => l_award_id
1546 ,x_return_status => l_return_status
1547 ,x_msg_count => l_msg_count
1548 ,x_msg_data => l_msg_data
1549 ,x_err_code => l_err_code
1550 );
1551
1552 IF (l_err_code <> 0)
1553 THEN
1554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1555 END IF;
1556
1557 if l_mode = 'B' then
1558
1559 ----------- DERIVING THE BUDGET_VERSION_ID OF THE PREVIOUSLY BASELINED BUDGET -----------------
1560
1561 -- Bug 2386041
1562 begin
1563 -- First get the budget_version_id of the previously baselined budget. In case there is an error we need to set the current_flag
1564 -- for this line to Y
1565
1566
1567 select bv.budget_version_id,
1568 bem.entry_level_code
1569 into l_prev_baselined_version_id,
1570 l_prev_entry_level_code
1571 from gms_budget_versions bv,
1572 pa_budget_entry_methods bem
1573 where bv.award_id = l_award_id
1574 and bv.project_id = l_project_id
1575 and bv.budget_type_code = l_budget_type_code
1576 and bv.budget_status_code = 'B'
1577 and bv.current_flag = 'R'
1578 and bv.budget_entry_method_code = bem.budget_entry_method_code;
1579
1580 exception
1581 when NO_DATA_FOUND then
1582 -- this means that there did not exist any baselined budget earlier
1583 l_prev_baselined_version_id := null;
1584 l_prev_entry_level_code := null;
1585
1586 when OTHERS then
1587 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1589 end;
1590 -- Bug 2386041
1591
1592 ---------------------------------------------------------------------------------------------------------
1593
1594 /* Bug 5162777: The budgetary control records are created before invoking fundscheck process. */
1595 ----------------------- START OF BC RECORD CREATION -------------------------
1596
1597 OPEN l_budget_versions_csr ( l_project_id, l_award_id, l_budget_type_code );
1598 FETCH l_budget_versions_csr INTO l_budget_version_id;
1599 Close l_budget_versions_csr ;
1600
1601 open l_grp_resource_type_csr( p_budget_version_id => l_budget_version_id);
1602 fetch l_grp_resource_type_csr into l_group_resource_type_id, l_resource_list_id;
1603 close l_grp_resource_type_csr;
1604
1605 open l_time_phased_type_csr( l_budget_version_id );
1606 fetch l_time_phased_type_csr into l_time_phased_type_code, l_entry_level_code;
1607 close l_time_phased_type_csr;
1608
1609
1610 gms_budg_cont_setup.bud_ctrl_create(p_project_id => l_project_id
1611 ,p_award_id => l_award_id
1612 ,p_prev_entry_level_code => l_prev_entry_level_code
1613 ,p_entry_level_code => l_entry_level_code
1614 ,p_resource_list_id => l_resource_list_id
1615 ,p_group_resource_type_id => l_group_resource_type_id
1616 ,x_err_code => l_err_code
1617 ,x_err_stage => l_err_stage);
1618
1619
1620 if l_err_code <> 0
1621 then
1622 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1623 RAISE FND_API.G_EXC_ERROR;
1624 -- Bug 2386041
1625 end if;
1626
1627
1628 --------------------------------------------------------------------------------------------------
1629
1630 end if;
1631
1632 -- Calling the Fundcheck process....
1633
1634 GMS_BUDGET_BALANCE.update_gms_balance( x_project_id => l_project_id,
1635 x_award_id => l_award_id,
1636 x_mode => l_mode,
1637 errbuf => l_err_code,
1638 retcode => l_retcode);
1639
1640 IF l_retcode = 'S'
1641 THEN
1642 resultout := 'COMPLETE:FUNDSCHECK_PASS';
1643
1644
1645 -- 29-May-2000------------------------------------------------------------------------------------
1646 -- if Funds check (during baselining, only) was successful then we have to:
1647 -- 1. set the current_flag = 'N' for the previously baselined budget whose current_flag was set to 'R' earlier,
1648 -- 2. set the current_flag = 'Y' for the newly created budget,
1649 -- 3. Summarize the Project Budget,
1650 -- 4. Run the default setup for Budgetary Control (if budget is baselined for the first time) and
1651 -- 5. set the budget_status_code = 'W' and wf_status_code = NULL for the budget that was 'Submitted'.
1652 -- 6. call gms_sweeper -- added for Bug: 1666853
1653
1654 if l_mode = 'B'
1655 then
1656
1657
1658
1659 --------------------------------------------------------------------------------------------------
1660
1661 -- 1. set the current_flag = 'N' for the previously baselined budget.
1662
1663 update gms_budget_versions
1664 set current_flag = 'N'
1665 where award_id = l_award_id
1666 and project_id = l_project_id
1667 and budget_type_code = l_budget_type_code
1668 and budget_status_code = 'B'
1669 and current_flag = 'R';
1670
1671 --------------------------------------------------------------------------------------------------
1672
1673 -- 2. set the current_flag = 'Y' for the newly created budget.
1674
1675 -- Corrected the query for Bug:2542827
1676
1677 update gms_budget_versions
1678 set current_flag = 'Y'
1679 where budget_version_id = ( select max(budget_version_id)
1680 from gms_budget_versions
1681 where award_id = l_award_id
1682 and project_id = l_project_id
1683 and budget_type_code = l_budget_type_code);
1684
1685
1686 --------------------------------------------------------------------------------------------------
1687 -- After updating the newly created budget we have to get the budget_version_id of this budget
1688 -- which is going to be used by the Project Budget Summarization and Default Budgetary Control
1689 -- Setup programs
1690
1691 begin
1692 select budget_version_id
1693 into l_baselined_version_id
1694 from gms_budget_versions
1695 where award_id = l_award_id
1696 and project_id = l_project_id
1697 and budget_type_code = l_budget_type_code
1698 and budget_status_code = 'B'
1699 and current_flag = 'Y';
1700
1701 exception
1702 when OTHERS
1703 then
1704 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode,'1');
1705 RAISE FND_API.G_EXC_ERROR;
1706 end;
1707
1708
1709 -- 3. Summarize the Project Budget.
1710
1711
1712 -- Bug 2386041
1713 l_user_profile_value1 := fnd_profile.value_specific(
1714 NAME => 'PA_SUPER_PROJECT',
1715 USER_ID => fnd_global.user_id,
1716 RESPONSIBILITY_ID => fnd_global.resp_id,
1717 APPLICATION_ID => fnd_global.resp_appl_id);
1718
1719 if ((l_user_profile_value1 = 'N') OR (l_user_profile_value1 is null)) then
1720
1721 BEGIN
1722
1723 SELECT profile_option_value
1724 INTO l_user_profile_value1
1725 FROM fnd_profile_options p,
1726 fnd_profile_option_values v
1727 WHERE p.profile_option_name = 'PA_SUPER_PROJECT'
1728 AND v.profile_option_id = p.profile_option_id
1729 AND v.level_id = 10004
1730 AND v.level_value = fnd_global.user_id;
1731
1732 EXCEPTION
1733
1734 WHEN no_data_found THEN
1735 l_user_profile_value1 := null;
1736
1737 WHEN others THEN
1738 l_user_profile_value1 := null;
1739
1740 END;
1741
1742 l_set_profile_success1 := fnd_profile.save(
1743 X_NAME => 'PA_SUPER_PROJECT',
1744 X_VALUE => 'Y',
1745 X_LEVEL_NAME => 'USER',
1746 X_LEVEL_VALUE => fnd_global.user_id);
1747 end if;
1748
1749 l_user_profile_value2 := fnd_profile.value_specific(
1750 NAME => 'PA_SUPER_PROJECT_VIEW',
1751 USER_ID => fnd_global.user_id,
1752 RESPONSIBILITY_ID => fnd_global.resp_id,
1753 APPLICATION_ID => fnd_global.resp_appl_id);
1754
1755
1756 if ((l_user_profile_value2 = 'N') OR (l_user_profile_value2 is null)) then
1757
1758 BEGIN
1759
1760 SELECT profile_option_value
1761 INTO l_user_profile_value2
1762 FROM fnd_profile_options p,
1763 fnd_profile_option_values v
1764 WHERE p.profile_option_name = 'PA_SUPER_PROJECT_VIEW'
1765 AND v.profile_option_id = p.profile_option_id
1766 AND v.level_id = 10004
1767 AND v.level_value = fnd_global.user_id;
1768
1769 EXCEPTION
1770
1771 WHEN no_data_found THEN
1772 l_user_profile_value2 := null;
1773
1774 WHEN others THEN
1775 l_user_profile_value2 := null;
1776
1777 END;
1778
1779 l_set_profile_success2 := fnd_profile.save(
1780 X_NAME => 'PA_SUPER_PROJECT_VIEW',
1781 X_VALUE => 'Y',
1782 X_LEVEL_NAME => 'USER',
1783 X_LEVEL_VALUE => fnd_global.user_id);
1784
1785
1786 end if;
1787 -- Bug 2386041
1788
1789
1790 gms_summarize_budgets.summarize_baselined_versions( x_project_id => l_project_id
1791 , x_time_phased_type_code => l_time_phased_type_code
1792 , x_app_short_name => l_app_short_name -- out NOCOPY variable
1793 , RETCODE => l_return_status
1794 , ERRBUF => l_err_stage );
1795
1796 -- Bug 2386041
1797
1798 if (l_set_profile_success1 = TRUE) then
1799 l_set_profile_success1 := fnd_profile.save('PA_SUPER_PROJECT', l_user_profile_value1, 'USER', fnd_global.user_id);
1800 end if;
1801
1802 if (l_set_profile_success2 = TRUE) then
1803 l_set_profile_success2 := fnd_profile.save('PA_SUPER_PROJECT_VIEW', l_user_profile_value2, 'USER', fnd_global.user_id);
1804 end if;
1805 -- Bug 2386041
1806
1807 if l_return_status <> 'S'
1808 then
1809 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1810 -- Bug 2386041
1811 update gms_budget_versions
1812 set current_flag = 'Y'
1813 where budget_version_id = l_prev_baselined_version_id;
1814
1815 update gms_budget_versions
1816 set current_flag = 'N'
1817 where budget_version_id = l_baselined_version_id;
1818 RAISE FND_API.G_EXC_ERROR;
1819 -- Bug 2386041
1820 end if;
1821
1822
1823 -- 5. set the budget_status_code = 'W' and wf_status_code = NULL for the budget that was 'Submitted'.
1824
1825 update gms_budget_versions
1826 set budget_status_code = 'W',
1827 wf_status_code = NULL
1828 where award_id = l_award_id
1829 and project_id = l_project_id
1830 and budget_type_code = l_budget_type_code
1831 and budget_status_code = 'S';
1832
1833 --------------------------------------------------------------------------------------------------
1834 -- 6. call gms_sweeper -- added for Bug: 1666853 ...
1835
1836 -- get the packet id for the budget and pass it on to the sweeper process.
1837 -- locking issue addressed as the scope of locking is limited to the packet.
1838 -- if there are no transactions then no point calling sweeper process. We'll skip it.
1839 -- Bug : 2821482.
1840
1841 -- changes for 2821482 begin...
1842
1843 begin
1844 select distinct packet_id
1845 into l_packet_id
1846 from gms_bc_packets
1847 where budget_version_id = l_baselined_version_id;
1848 exception
1849 -- no data found can occur when there are no transactions
1850 -- for the award.
1851 when no_data_found then
1852 l_packet_id := null;
1853 null;
1854 end;
1855 -- bug 2821482 changes end.
1856
1857 if l_packet_id is not null then --> bug 2821482.
1858
1859 gms_sweeper.upd_act_enc_bal(ERRBUF => l_err_stage,
1860 retcode => l_err_code,
1861 x_mode => 'B',
1862 x_packet_id => l_packet_id, --> bug 2821482
1863 x_project_id => l_project_id,
1864 x_award_id => l_award_id);
1865
1866 if l_err_code <> 0 then -- Checking for 0 (zero) instead of 'S' for Bug:2464800
1867
1868 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1869 -- Bug 2386041
1870 update gms_budget_versions
1871 set current_flag = 'Y'
1872 where budget_version_id = l_prev_baselined_version_id;
1873
1874 update gms_budget_versions
1875 set current_flag = 'N'
1876 where budget_version_id = l_baselined_version_id;
1877
1878 RAISE FND_API.G_EXC_ERROR;
1879 -- Bug 2386041
1880 end if;
1881
1882 end if; --> l_packet_id is not null. Bug 2821482
1883 -- ... for Bug: 1666853
1884 --------------------------------------------------------------------------------------------------
1885
1886 elsif l_mode = 'S'
1887 then
1888 -- Budget Status is set to 'Submitted' and FC_MODE is set to 'B' since the Funds check process
1889 -- for baselining looks for budget_status_code = 'S' and FC_MODE = 'B'
1890
1891 update gms_budget_versions
1892 set budget_status_code = 'S'
1893 where award_id = l_award_id
1894 and project_id = l_project_id
1895 and budget_type_code = l_budget_type_code
1896 and budget_status_code = 'W';
1897
1898
1899 wf_engine.SetItemAttrText(itemtype => itemtype,
1900 itemkey => itemkey,
1901 aname => 'FC_MODE',
1902 avalue => 'B');
1903
1904
1905
1906 end if; -- (l_mode = 'B')
1907
1908 ELSE
1909 -- if Funds check failed then the previously baselined budget (whose current_flag was set to 'R' earlier) should be restored
1910
1911 if l_mode = 'B'
1912 then
1913 update gms_budget_versions
1914 set current_flag = 'Y'
1915 where award_id = l_award_id
1916 and project_id = l_project_id
1917 and budget_type_code = l_budget_type_code
1918 and budget_status_code = 'B'
1919 and current_flag = 'R';
1920 end if;
1921
1922 resultout := 'COMPLETE:FUNDSCHECK_FAIL';
1923 END IF;
1924
1925
1926 EXCEPTION
1927
1928 WHEN FND_API.G_EXC_ERROR
1929 THEN
1930 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1931 resultout := 'COMPLETE:FUNDSCHECK_FAIL';
1932
1933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1934 THEN
1935 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1936 resultout := 'COMPLETE:FUNDSCHECK_FAIL';
1937
1938 WHEN OTHERS
1939 THEN
1940 WF_CORE.CONTEXT('GMS_WF_PKG','FUNDS_CHECK', itemtype, itemkey, to_char(actid), funcmode);
1941 resultout := 'COMPLETE:FUNDSCHECK_FAIL';
1942
1943 END Funds_check;
1944
1945 ----------------------------------------------------------------------------------------
1946
1947 PROCEDURE Chk_Baselined_Budget_Exists
1948 (itemtype IN VARCHAR2
1949 , itemkey IN VARCHAR2
1950 , actid IN NUMBER
1951 , funcmode IN VARCHAR2
1952 , resultout OUT NOCOPY VARCHAR2
1953 )
1954 IS
1955
1956 l_project_id NUMBER;
1957 l_award_id NUMBER;
1958 l_budget_type_code pa_budget_types.budget_type_code%TYPE;
1959 l_budget_version_id NUMBER;
1960
1961 l_err_code NUMBER;
1962 l_err_stage VARCHAR2(120);
1963 l_err_stack VARCHAR2(630);
1964 l_msg_count NUMBER;
1965 l_msg_data VARCHAR(2000);
1966 l_return_status VARCHAR2(1);
1967
1968 begin
1969
1970 IF (funcmode <> wf_engine.eng_run)
1971 THEN
1972 resultout := wf_engine.eng_null;
1973 RETURN;
1974 END IF;
1975 --
1976
1977 -- GET BUDGET ITEM ATTRIBUTES for Subsequent Processing -----------------------
1978
1979 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1980 itemkey => itemkey,
1981 aname => 'PROJECT_ID' );
1982
1983 l_award_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1984 itemkey => itemkey,
1985 aname => 'AWARD_ID' );
1986
1987 l_budget_type_code := wf_engine.GetItemAttrText( itemtype => itemtype,
1988 itemkey => itemkey,
1989 aname => 'BUDGET_TYPE_CODE' );
1990
1991
1992 -- SET GLOBALS ------------------------------------------------------------------
1993
1994 -- Based on the Responsibility, Intialize the Application
1995
1996 GMS_WORKFLOW_UTILS.Set_Global_Attr
1997 (p_item_type => itemtype
1998 , p_item_key => itemkey
1999 , p_err_code => l_err_code);
2000
2001 --Setting OU Context
2002 GMS_BUDGET_UTILS.Set_Award_Policy_Context (p_award_id => l_award_id
2003 ,x_return_status => l_return_status
2004 ,x_msg_count => l_msg_count
2005 ,x_msg_data => l_msg_data
2006 ,x_err_code => l_err_code
2007 );
2008
2009 IF (l_err_code <> 0)
2010 THEN
2011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2012 END IF;
2013
2014
2015 GMS_BUDGET_UTILS.get_baselined_version_id (
2016 x_project_id => l_project_id,
2017 x_award_id => l_award_id,
2018 x_budget_type_code => l_budget_type_code,
2019 x_budget_version_id => l_budget_version_id,
2020 x_err_code => l_err_code,
2021 x_err_stage => l_err_stage,
2022 x_err_stack => l_err_stack);
2023
2024 IF l_err_code <> 0
2025 THEN
2026 -- baselined version doesn't exist
2027
2028 -- Since a baselined budget doesn't exist Funds checking for Submit process
2029 -- should be bypassed and so the FC_MODE is being set to 'B'.
2030
2031 wf_engine.SetItemAttrText(itemtype => itemtype,
2032 itemkey => itemkey,
2033 aname => 'FC_MODE',
2034 avalue => 'B');
2035
2036 update gms_budget_versions
2037 set budget_status_code = 'S'
2038 where award_id = l_award_id
2039 and project_id = l_project_id
2040 and budget_type_code = l_budget_type_code
2041 and budget_status_code = 'W';
2042
2043 -- the above update is being explicitly commited since the budget_status_code (S) is required
2044 -- to enable/disable the control in the Award Budget Form
2045
2046 commit;
2047 resultout := 'COMPLETE:NO';
2048 return;
2049 ELSE
2050 -- baselined version exists
2051 resultout := 'COMPLETE:YES';
2052 return;
2053 END IF;
2054
2055 end;
2056
2057 ----------------------------------------------------------------------------------
2058
2059 PROCEDURE start_report_wf_process( x_award_id IN NUMBER
2060 ,x_award_number IN VARCHAR2
2061 ,x_award_short_name IN VARCHAR2
2062 ,x_installment_number IN VARCHAR2
2063 ,x_report_name IN VARCHAR2
2064 ,x_report_due_date IN VARCHAR2
2065 ,x_funding_source_name IN VARCHAR2
2066 ,x_role IN VARCHAR2
2067 ,x_err_code OUT NOCOPY NUMBER
2068 ,x_err_stage OUT NOCOPY VARCHAR2)
2069 IS
2070 ItemType varchar2(30) := 'GMSWF';
2071 ItemKey number;
2072 x_workflow_started_by_id number := FND_GLOBAL.User_Id;
2073 x_user_name varchar2(100) := FND_GLOBAL.User_Name;
2074 x_full_name varchar2(65);
2075 x_wf_started_date date := SYSDATE;
2076
2077 cursor l_starter_full_name_csr
2078 is
2079 select p.first_name||' '||p.last_name /*Bug 5122724 */
2080 from fnd_user f, per_people_f p
2081 where p.effective_start_date = (select min(pp.effective_start_date)
2082 from per_all_people_f pp
2083 where pp.person_id = p.person_id
2084 and pp.effective_end_date >=trunc(sysdate))
2085 and ((p.employee_number is not null) OR (p.npw_number is not null))
2086 and user_id = FND_GLOBAL.User_Id
2087 and f.employee_id = p.person_id;
2088
2089
2090
2091 BEGIN
2092 x_err_code := 0;
2093
2094 select gms_workflow_itemkey_s.nextval
2095 into ItemKey
2096 from dual;
2097 open l_starter_full_name_csr;
2098 fetch l_starter_full_name_csr into x_full_name;
2099 IF (l_starter_full_name_csr%NOTFOUND)
2100 THEN
2101 x_err_code := 10;
2102 close l_starter_full_name_csr;
2103 return;
2104 END IF;
2105
2106 close l_starter_full_name_csr;
2107
2108 wf_engine.CreateProcess( ItemType => ItemType,
2109 ItemKey => ItemKey,
2110 process => 'GMS_WF_PROCESS' );
2111
2112 -- Attribute GMS_WF_PROCESS is used to select the appropriate branch
2113 -- in the workflow process.
2114
2115
2116 wf_engine.SetItemAttrText ( itemtype => itemtype,
2117 itemkey => itemkey,
2118 aname => 'GMS_WF_PROCESS',
2119 avalue => 'REPORT');
2120
2121 wf_engine.SetItemAttrText ( itemtype => itemtype,
2122 itemkey => itemkey,
2123 aname => 'AWARD_NUMBER',
2124 avalue => x_award_number);
2125
2126 wf_engine.SetItemAttrText ( itemtype => itemtype,
2127 itemkey => itemkey,
2128 aname => 'AWARD_SHORT_NAME',
2129 avalue => x_award_short_name);
2130 -- Added below call to pass Installment Number attribute Bug 2286855
2131 wf_engine.SetItemAttrText ( itemtype => itemtype,
2132 itemkey => itemkey,
2133 aname => 'INSTALL_NUM',
2134 avalue => x_installment_number);
2135
2136 wf_engine.SetItemAttrText ( itemtype => itemtype,
2137 itemkey => itemkey,
2138 aname => 'REPORT_NAME',
2139 avalue => x_report_name);
2140
2141 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2142 itemkey => itemkey,
2143 aname => 'REPORT_DUE_DATE',
2144 avalue => x_report_due_date);
2145
2146 wf_engine.SetItemAttrText ( itemtype => itemtype,
2147 itemkey => itemkey,
2148 aname => 'FUNDING_SOURCE_NAME',
2149 avalue => x_funding_source_name);
2150
2151 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2152 itemkey => itemkey,
2153 aname => 'WORKFLOW_STARTED_BY_ID',
2154 avalue => x_workflow_started_by_id);
2155
2156 wf_engine.SetItemAttrText ( itemtype => itemtype,
2157 itemkey => itemkey,
2158 aname => 'WORKFLOW_STARTED_BY_NAME',
2159 avalue => x_user_name);
2160
2161 wf_engine.SetItemAttrText ( itemtype => itemtype,
2162 itemkey => itemkey,
2163 aname => 'NOTIF_RECIPIENT_ROLE',
2164 avalue => x_role);
2165
2166 wf_engine.SetItemAttrText ( itemtype => itemtype,
2167 itemkey => itemkey,
2168 aname => 'WORKFLOW_STARTED_BY_FULL_NAME',
2169 avalue => x_full_name);
2170
2171 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2172 itemkey => itemkey,
2173 aname => 'WF_STARTED_DATE',
2174 avalue => x_wf_started_date
2175 );
2176
2177
2178 wf_engine.StartProcess( itemtype => itemtype,
2179 itemkey => itemkey );
2180
2181
2182 -- p_item_type := itemtype;
2183 -- p_item_key := itemkey;
2184
2185 -- Added Exception for Bug:2662848
2186
2187 Exception
2188 when OTHERS then
2189 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
2190 x_token_name1 => 'SQLCODE',
2191 x_token_val1 => sqlcode,
2192 x_token_name2 => 'SQLERRM',
2193 x_token_val2 => sqlerrm,
2194 x_err_code => x_err_code,
2195 x_err_buff => x_err_stage);
2196
2197 APP_EXCEPTION.RAISE_EXCEPTION;
2198 end start_report_wf_process;
2199
2200 ----------------------------------------------------------------------------------------
2201 PROCEDURE Schedule_Notification( ERRBUF OUT NOCOPY Varchar2
2202 ,RETCODE OUT NOCOPY Varchar2
2203 ,p_offset_days IN NUMBER)
2204 IS
2205 l_offset_days NUMBER; -- Bug 1868293
2206
2207 /* cursor c1 is
2208 select ga.award_id,
2209 ga.award_number,
2210 ga.award_short_name,
2211 gi.installment_num,
2212 grv.report_name,
2213 grv.due_date,
2214 ga.funding_source_short_name,
2215 grv.report_id --bug 2282107
2216 from gms_awards_v ga, gms_installments gi, gms_reports_v grv
2217 where grv.installment_id = gi.installment_id
2218 and gi.award_id = ga.award_id
2219 and grv.due_date = trunc(sysdate) + l_offset_days -- Bug 1868293
2220 and ga.status <> 'CLOSED' --Changed from 'ACTIVE' to fix bug 2200837
2221 and gi.active_flag = 'Y'
2222 and ga.award_template_flag ='DEFERRED'; */-- commentedout to fix bug 2660430
2223
2224 --following cursor is re-structured of above query to fix bug 2660430
2225 cursor c1 is
2226 select ga.award_id,
2227 ga.award_number,
2228 ga.award_short_name,
2229 gi.installment_num,
2230 grt.report_name,
2231 gr.due_date,
2232 substrb(party.party_name,1,50) funding_source_short_name,
2233 gr.report_id --bug 2282107
2234 from gms_awards ga,
2235 gms_installments gi,
2236 gms_reports gr,
2237 gms_report_templates grt,
2238 hz_parties party,
2239 hz_cust_accounts cust_acct
2240 where ga.award_template_flag ='DEFERRED'
2241 and ga.status <> 'CLOSED' --Changed from 'ACTIVE' to fix bug 2200837
2242 and ga.award_id = gi.award_id
2243 and gi.active_flag = 'Y'
2244 and gr.installment_id = gi.installment_id
2245 and gr.report_template_id = grt.report_template_id
2246 and gr.due_date = trunc(sysdate) + l_offset_days -- Bug 1868293
2247 and ga.funding_source_id =cust_acct.cust_account_id(+)
2248 and cust_acct.party_id = party.party_id;
2249
2250
2251 cursor c2 (p_award_id NUMBER,
2252 p_event_type VARCHAR2)
2253 is
2254 select user_id, user_name
2255 from gms_notifications_v
2256 where event_type like p_event_type
2257 and award_id = p_award_id;
2258
2259 l_award_id number;
2260 l_award_number varchar2(15);
2261 l_award_short_name varchar2(30);
2262 l_installment_num varchar2(15);
2263 l_report_name varchar2(60);
2264 l_report_due_date date;
2265 l_funding_source_name varchar2(255);
2266 l_user_id number;
2267 l_user_name varchar2(240);
2268 l_role_name varchar2(100);
2269 l_role_name_disp varchar2(100); -- NOCOPY fix
2270 l_report_id number; --bug 2282107
2271 --start bug fix 2204122 changed the width of var l_user_roles to 32000 from 4000--
2272 l_user_roles varchar2(32000) := NULL;
2273 --end bug fix 2204122--
2274 l_err_code number;
2275 l_err_stage varchar2(630);
2276
2277 WF_API_EXCEPTION exception;
2278 pragma exception_init(WF_API_EXCEPTION, -20002);
2279
2280 begin
2281
2282 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
2283
2284 IF L_DEBUG = 'Y' THEN
2285 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - start', 'C');
2286 END IF;
2287
2288 -- Start of code added for Bug 1868293
2289 l_offset_days := p_offset_days;
2290
2291 If l_offset_days IS NULL Then
2292 FND_PROFILE.GET('GMS_NOTIFICATION_OFFSET_DAYS',l_offset_days);
2293
2294 If l_offset_days IS NULL Then
2295 IF L_DEBUG = 'Y' THEN
2296 gms_error_pkg.gms_debug('Profile GMS_NOTIFICATION_OFFSET_DAYS is undefined', 'C');
2297 END IF;
2298 Elsif (l_offset_days < 0) Then
2299 IF L_DEBUG = 'Y' THEN
2300 gms_error_pkg.gms_debug('Invalid value for Profile GMS_NOTIFICATION_OFFSET_DAYS', 'C');
2301 END IF;
2302 End If;
2303 End If;
2304
2305
2306 If (l_offset_days >= 0) Then -- End of code added for Bug 1868293
2307 IF L_DEBUG = 'Y' THEN
2308 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - Offset Days = '||to_char(l_offset_days), 'C');
2309 END IF;
2310
2311 open c1;
2312 loop
2313 fetch c1 into
2314 l_award_id,
2315 l_award_number,
2316 l_award_short_name,
2317 l_installment_num,
2318 l_report_name,
2319 l_report_due_date,
2320 l_funding_source_name,
2321 l_report_id; --bug 2282107
2322
2323 exit when c1%NOTFOUND;
2324
2325 open c2 (l_award_id
2326 ,'REPORT%');
2327 loop
2328 fetch c2 into
2329 l_user_id,
2330 l_user_name;
2331 exit when c2%notfound;
2332 ---bug# 3224843---
2333 IF Excl_Person_From_Notification(l_award_id, l_user_id) = 'N' THEN
2334 l_user_roles := l_user_roles||','||l_user_name;
2335 END IF;
2336 ---bug# 3224843---
2337 end loop;
2338 close c2;
2339 -- In order to remove an extra comma (,) in the starting of l_user_roles
2340 if substr(l_user_roles,1,1) = ','
2341 then
2342 l_user_roles := substr(l_user_roles, 2, (length(l_user_roles) - 1));
2343 end if;
2344
2345 l_role_name := l_award_id||'-'||l_report_id; -- change from l_report_name bug 2282107
2346 l_role_name_disp:= l_role_name ;
2347
2348 IF L_DEBUG = 'Y' THEN
2349 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - role_name = '||l_role_name, 'C');
2350 END IF;
2351 --start bug fix 2204122--
2352 --Commented the following line as this is called from procedure call_gms_debug--
2353 --gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - user_roles = '||l_user_roles, 'C');
2354 gms_client_extn_budget_wf.call_gms_debug(p_user_roles => l_user_roles
2355 ,p_disp_text =>'GMS_WF_PKG.SCHEDULE_NOTIFICATION - user_roles = ') ;
2356 --end bug fix 2204122--
2357
2358 ----------------------------------------------------------------------------
2359 begin
2360 wf_directory.CreateAdhocRole(
2361 role_name => l_role_name,
2362 role_display_name => l_role_name_disp,
2363 language => 'AMERICAN', -- jjj
2364 territory => 'AMERICA', -- jjj
2365 notification_preference => 'MAILHTML'
2366 );
2367 exception
2368 when WF_API_EXCEPTION
2369 then
2370 NULL;
2371 end;
2372 IF L_DEBUG = 'Y' THEN
2373 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - created adhoc role', 'C');
2374 END IF;
2375 ----------------------------------------------------------------------------
2376
2377 wf_directory.RemoveUsersFromAdhocRole(role_name => l_role_name);
2378
2379 IF L_DEBUG = 'Y' THEN
2380 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - removed users from adhoc role', 'C');
2381 END IF;
2382
2383 begin
2384 --start bug fix 2204122--
2385
2386 gms_client_extn_budget_wf.call_wf_addusers_to_adhocrole( p_user_roles => l_user_roles
2387 ,p_role_name => l_role_name);
2388
2389 -- wf_directory.AddUsersToAdhocRole( role_name => l_role_name
2390 -- ,role_users => l_user_roles);
2391 --end bug fix 2204122--
2392
2393 exception
2394 when WF_API_EXCEPTION
2395 then
2396 NULL;
2397 end;
2398
2399 IF L_DEBUG = 'Y' THEN
2400 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - added users to adhoc role', 'C');
2401 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - Calling start_report_wf_process...', 'C');
2402 END IF;
2403
2404 gms_wf_pkg.start_report_wf_process(
2405 x_award_id => l_award_id,
2406 x_award_number => l_award_number,
2407 x_award_short_name => l_award_short_name,
2408 x_installment_number => l_installment_num,
2409 x_report_name => l_report_name,
2410 x_report_due_date => l_report_due_date,
2411 x_funding_source_name => l_funding_source_name,
2412 x_role => l_role_name,
2413 x_err_code => l_err_code,
2414 x_err_stage => l_err_stage);
2415
2416 IF L_DEBUG = 'Y' THEN
2417 gms_error_pkg.gms_debug('GMS_WF_PKG.SCHEDULE_NOTIFICATION - After start_report_wf_process...', 'C');
2418 END IF;
2419 --start bug fix 2204122--
2420 --As new set of userid's will get stored in l_user_roles. reinitializing the variable--
2421 l_user_roles := NULL ;
2422 --end bug fix 2204122--
2423 end loop;
2424 close c1;
2425 End if; -- if (l_offset_days >= 0) Then -- Bug 1868293
2426 exception
2427 when VALUE_ERROR then -- Added, Bug 1868293
2428 -- Added RETCODE and ERRBUF for Bug:2464800
2429 ERRBUF := 'The following error occured : '||sqlerrm;
2430 RETCODE := '2';
2431 IF L_DEBUG = 'Y' THEN
2432 gms_error_pkg.gms_debug('Invalid value for Profile GMS_NOTIFICATION_OFFSET_DAYS', 'C');
2433 END IF;
2434
2435 when others
2436 then
2437 -- Changed RETCODE to 2 for Bug:2464800
2438 ERRBUF := 'The following error occured : '||sqlerrm;
2439 RETCODE := '2';
2440
2441 end Schedule_Notification;
2442 ------------------------------------------------------------------------------------
2443
2444 PROCEDURE Init_Installment_WF(x_award_id IN NUMBER
2445 ,x_installment_id IN NUMBER)
2446 IS
2447 l_user_id number;
2448 l_user_name varchar2(240);
2449 l_role_name varchar2(100);
2450 l_role_name_disp varchar2(100); -- NOCOPY related fix
2451 --start bug fix 2204122 changed the width of var l_user_roles to 32000 from 4000--
2452 l_user_roles varchar2(32000) := NULL;
2453 --end bug fix 2204122--
2454 l_err_code number;
2455 l_err_stage varchar2(630);
2456 l_wf_threshold_orig number; -- Added for Bug:1457961
2457
2458 WF_API_EXCEPTION exception;
2459 pragma exception_init(WF_API_EXCEPTION, -20002);
2460
2461 cursor c1 (p_award_id IN NUMBER)
2462 is
2463 select user_id, user_name
2464 from gms_notifications_v
2465 where event_type = 'INSTALLMENT_ACTIVE'
2466 and award_id = p_award_id;
2467
2468 begin
2469 SAVEPOINT create_installment_wf;
2470
2471
2472 open c1 (x_award_id);
2473 loop
2474 fetch c1 into
2475 l_user_id,
2476 l_user_name;
2477 exit when c1%notfound;
2478 ---bug# 3224843---
2479 IF Excl_Person_From_Notification(x_award_id, l_user_id) = 'N' THEN
2480 l_user_roles := l_user_roles||','||l_user_name;
2481 END IF;
2482 ---bug# 3224843---
2483 end loop;
2484
2485 close c1;
2486
2487 -- In order to remove an extra comma (,) in the starting of l_user_roles
2488
2489 if substr(l_user_roles,1,1) = ','
2490 then
2491 l_user_roles := substr(l_user_roles, 2, (length(l_user_roles) - 1));
2492 end if;
2493
2494 l_role_name := to_char(x_award_id)||'-'||to_char(x_installment_id)||'-INSTALLMENT';
2495 l_role_name_disp := l_role_name ;
2496
2497 ----------------------------------------------------------------------------
2498 begin
2499 wf_directory.CreateAdhocRole(
2500 role_name => l_role_name,
2501 role_display_name => l_role_name_disp,
2502 language => 'AMERICAN',
2503 territory => 'AMERICA',
2504 notification_preference => 'MAILHTML'
2505 );
2506 exception
2507 when WF_API_EXCEPTION
2508 then
2509 NULL;
2510 end;
2511 ----------------------------------------------------------------------------
2512
2513 wf_directory.RemoveUsersFromAdhocRole(role_name => l_role_name);
2514
2515 begin
2516 --start bug fix 2204122--
2517 gms_client_extn_budget_wf.call_wf_addusers_to_adhocrole(p_user_roles => l_user_roles
2518 ,p_role_name => l_role_name);
2519
2520 -- wf_directory.AddUsersToAdhocRole( role_name => l_role_name
2521 -- ,role_users => l_user_roles);
2522 --end bug fix 2204122--
2523 exception
2524 when WF_API_EXCEPTION
2525 then
2526 NULL;
2527 end;
2528
2529 -- The WF threshold logic has been added for Bug: 1457961
2530 -- the threshold is lowered to -1 so that the process is taken over
2531 -- by the background engine, which in turn is started by the concurrent
2532 -- process (Workflow Background Process) on regular intervals.
2533 -- The threshold is set back to the original threshold after calling
2534 -- the start_installment_wf procedure.
2535
2536 -- l_wf_threshold_orig := wf_engine.threshold;
2537 -- wf_engine.threshold := -1;
2538
2539 gms_wf_pkg.start_installment_wf( x_award_id => x_award_id
2540 ,x_install_id => x_installment_id
2541 ,x_role => l_role_name
2542 ,x_err_code => l_err_code
2543 ,x_err_stage => l_err_stage);
2544
2545 -- wf_engine.threshold := l_wf_threshold_orig;
2546
2547 if l_err_code <> 0
2548 then
2549 gms_error_pkg.gms_message(x_err_name => 'GMS_START_INSTALL_WF_FAIL',
2550 x_err_code => l_err_code,
2551 x_err_buff => l_err_stage);
2552
2553 APP_EXCEPTION.RAISE_EXCEPTION;
2554 end if;
2555
2556 EXCEPTION
2557 WHEN OTHERS
2558 THEN
2559 ROLLBACK to create_installment_wf;
2560
2561 END Init_Installment_WF;
2562 ------------------------------------------------------------------------------------
2563 PROCEDURE start_installment_wf( x_award_id IN NUMBER
2564 ,x_install_id IN NUMBER
2565 ,x_role IN VARCHAR2
2566 ,x_err_code OUT NOCOPY NUMBER
2567 ,x_err_stage OUT NOCOPY VARCHAR2)
2568 IS
2569 ItemType varchar2(30) := 'GMSWF';
2570 ItemKey number;
2571
2572 l_award_number varchar2(15);
2573 l_award_short_name varchar2(30);
2574 l_funding_source_name varchar2(255);
2575
2576 -- Bug Fix 2225725
2577 --l_install_number number;
2578 l_install_number gms_installments.installment_num%TYPE;
2579 -- End of Fix
2580
2581 l_install_start_date date;
2582 l_install_end_date date;
2583 l_install_issue_date date;
2584 l_install_close_date date;
2585 l_install_direct_cost number;
2586 l_install_indirect_cost number;
2587 l_install_total_amount number;
2588 l_install_description varchar(250);
2589
2590 l_workflow_started_by_id number := FND_GLOBAL.User_Id;
2591 l_user_name varchar2(100) := FND_GLOBAL.User_Name;
2592 l_full_name varchar2(65);
2593 l_wf_started_date date := SYSDATE;
2594
2595 cursor l_starter_full_name_csr
2596 is
2597 select p.first_name||' '||p.last_name /*Bug 5122724 */
2598 from fnd_user f, per_people_f p
2599 where p.effective_start_date = (select min(pp.effective_start_date)
2600 from per_all_people_f pp
2601 where pp.person_id = p.person_id
2602 and pp.effective_end_date >=trunc(sysdate))
2603 and ((p.employee_number is not null) OR (p.npw_number is not null))
2604 and user_id = FND_GLOBAL.User_Id
2605 and f.employee_id = p.person_id;
2606
2607 cursor l_installment_detail_csr
2608 is
2609 select ga.award_number,
2610 ga.award_short_name,
2611 substrb(party.party_name,1,50),
2612 gi.installment_num,
2613 gi.start_date_active,
2614 gi.end_date_active,
2615 gi.issue_date,
2616 gi.close_date,
2617 gi.direct_cost,
2618 gi.indirect_cost,
2619 (nvl(gi.direct_cost,0) + nvl(gi.indirect_cost,0)),
2620 gi.description
2621 from gms_awards ga,
2622 gms_installments gi, hz_parties party,
2623 hz_cust_accounts cust_acct
2624 where gi.award_id = ga.award_id
2625 and cust_acct.cust_account_id(+) = ga.funding_source_id
2626 and cust_acct.party_id = party.party_id
2627 and gi.installment_id = x_install_id
2628 and ga.award_id = x_award_id;
2629
2630 BEGIN
2631
2632 x_err_code := 0;
2633
2634 select gms_workflow_itemkey_s.nextval
2635 into ItemKey
2636 from dual;
2637
2638 open l_starter_full_name_csr;
2639 fetch l_starter_full_name_csr into l_full_name;
2640 close l_starter_full_name_csr;
2641
2642 open l_installment_detail_csr;
2643 fetch l_installment_detail_csr
2644 into
2645 l_award_number,
2646 l_award_short_name,
2647 l_funding_source_name,
2648 l_install_number,
2649 l_install_start_date,
2650 l_install_end_date,
2651 l_install_issue_date,
2652 l_install_close_date,
2653 l_install_direct_cost,
2654 l_install_indirect_cost,
2655 l_install_total_amount,
2656 l_install_description;
2657
2658 IF (l_installment_detail_csr%NOTFOUND)
2659 THEN
2660 x_err_code := 10;
2661 x_err_stage := 'GMS_INVALID_INSTALLMENT';
2662 fnd_message.set_name('GMS','GMS_INVALID_INSTALLMENT');
2663 app_exception.raise_exception;
2664 close l_installment_detail_csr;
2665 return;
2666 END IF;
2667
2668 close l_installment_detail_csr;
2669
2670
2671 wf_engine.CreateProcess( ItemType => ItemType,
2672 ItemKey => ItemKey,
2673 process => 'GMS_WF_PROCESS' );
2674
2675 -- Attribute GMS_WF_PROCESS is used to select the appropriate branch
2676 -- in the workflow process.
2677
2678
2679 wf_engine.SetItemAttrText ( itemtype => itemtype,
2680 itemkey => itemkey,
2681 aname => 'GMS_WF_PROCESS',
2682 avalue => 'INSTALLMENT');
2683
2684 wf_engine.SetItemAttrText ( itemtype => itemtype,
2685 itemkey => itemkey,
2686 aname => 'AWARD_NUMBER',
2687 avalue => l_award_number);
2688
2689 wf_engine.SetItemAttrText ( itemtype => itemtype,
2690 itemkey => itemkey,
2691 aname => 'AWARD_SHORT_NAME',
2692 avalue => l_award_short_name);
2693
2694 wf_engine.SetItemAttrText ( itemtype => itemtype,
2695 itemkey => itemkey,
2696 aname => 'FUNDING_SOURCE_NAME',
2697 avalue => l_funding_source_name);
2698
2699 wf_engine.SetItemAttrText ( itemtype => itemtype,
2700 itemkey => itemkey,
2701 aname => 'INSTALL_NUM',
2702 avalue => l_install_number);
2703
2704 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2705 itemkey => itemkey,
2706 aname => 'INSTALL_START_DATE',
2707 avalue => l_install_start_date);
2708
2709 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2710 itemkey => itemkey,
2711 aname => 'INSTALL_END_DATE',
2712 avalue => l_install_end_date);
2713
2714 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2715 itemkey => itemkey,
2716 aname => 'INSTALL_ISSUE_DATE',
2717 avalue => l_install_issue_date);
2718
2719 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2720 itemkey => itemkey,
2721 aname => 'INSTALL_CLOSE_DATE',
2722 avalue => l_install_close_date);
2723
2724 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2725 itemkey => itemkey,
2726 aname => 'INSTALL_DIRECT_COST',
2727 avalue => l_install_direct_cost);
2728
2729 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2730 itemkey => itemkey,
2731 aname => 'INSTALL_INDIRECT_COST',
2732 avalue => l_install_indirect_cost);
2733
2734 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2735 itemkey => itemkey,
2736 aname => 'INSTALL_TOTAL_AMOUNT',
2737 avalue => l_install_total_amount);
2738
2739 wf_engine.SetItemAttrText ( itemtype => itemtype,
2740 itemkey => itemkey,
2741 aname => 'INSTALL_DESCRIPTION',
2742 avalue => l_install_description);
2743
2744 wf_engine.SetItemAttrText ( itemtype => itemtype,
2745 itemkey => itemkey,
2746 aname => 'NOTIF_RECIPIENT_ROLE',
2747 avalue => x_role);
2748
2749 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2750 itemkey => itemkey,
2751 aname => 'WORKFLOW_STARTED_BY_ID',
2752 avalue => l_workflow_started_by_id);
2753
2754 wf_engine.SetItemAttrText ( itemtype => itemtype,
2755 itemkey => itemkey,
2756 aname => 'WORKFLOW_STARTED_BY_NAME',
2757 avalue => l_user_name);
2758
2759 wf_engine.SetItemAttrText ( itemtype => itemtype,
2760 itemkey => itemkey,
2761 aname => 'WORKFLOW_STARTED_BY_FULL_NAME',
2762 avalue => l_full_name);
2763
2764 wf_engine.SetItemAttrDate ( itemtype => itemtype,
2765 itemkey => itemkey,
2766 aname => 'WF_STARTED_DATE',
2767 avalue => l_wf_started_date
2768 );
2769
2770
2771 wf_engine.StartProcess( itemtype => itemtype,
2772 itemkey => itemkey );
2773
2774
2775 -- p_item_type := itemtype;
2776 -- p_item_key := itemkey;
2777
2778 -- Added Exception for Bug:2662848
2779
2780 exception
2781 when OTHERS then
2782 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
2783 x_token_name1 => 'SQLCODE',
2784 x_token_val1 => sqlcode,
2785 x_token_name2 => 'SQLERRM',
2786 x_token_val2 => sqlerrm,
2787 x_err_code => x_err_code,
2788 x_err_buff => x_err_stage);
2789
2790 APP_EXCEPTION.RAISE_EXCEPTION;
2791
2792 end start_installment_wf;
2793
2794 ------------------------------------------------------------------------------------
2795 --Start : Build of the installment closeout Notification Bug # 1969587
2796 /*======================================================================================================================
2797 Following Logic selects Open commitments associated with the particular award,project and task combination and frames
2798 a message which gets displayed as text in the notification.
2799 ======================================================================================================================*/
2800
2801 PROCEDURE Get_Inst_Open_Commitments ( document_id IN VARCHAR2
2802 ,display_type IN VARCHAR2
2803 ,document IN OUT NOCOPY VARCHAR2
2804 ,document_type IN OUT NOCOPY VARCHAR2) IS
2805
2806
2807 l_item_type wf_items.item_type%TYPE;
2808 l_item_key wf_items.item_key%TYPE;
2809 l_document_id VARCHAR2(100);
2810 l_document VARCHAR2(32000) := '';
2811 l_commitment_number VARCHAR2(50);
2812 l_commit_document_type VARCHAR2(3);
2813 l_commit_document_type_desc VARCHAR2(80);
2814 l_amount NUMBER;
2815 l_award_id gms_awards.award_id%TYPE;
2816 l_project_id gms_encumbrance_items.project_id%TYPE;
2817 l_task_id gms_encumbrance_items.task_id%TYPE;
2818 l_installment_end_date gms_installments.start_date_active%TYPE;
2819 l_installment_start_date gms_installments.end_date_active%TYPE;
2820 l_installment_id gms_installments.installment_id%TYPE;
2821 l_header gms_lookups.meaning%TYPE;
2822 l_award_number gms_awards.award_number%TYPE;
2823 l_installment_number gms_installments.installment_num%TYPE;
2824
2825 l_installment_end_date_text varchar2(60); /*Added for bug:7538344 */
2826 l_user_id number; /*Added for bug:7538344 */
2827
2828 -- Declare a variable to create a new line.
2829 NL VARCHAR2(1) := fnd_global.newline;
2830 -- Cursor to fetch all the open commitments attached associated with the award ,project and task combination
2831
2832 CURSOR lookups_cursor (type VARCHAR2) IS
2833 SELECT meaning
2834 FROM gms_lookups
2835 WHERE lookup_type='GMS_COMMT_TYPE'
2836 AND lookup_code = type ;
2837
2838
2839 CURSOR lookup_document (header VARCHAR2) IS
2840 SELECT meaning
2841 FROM gms_lookups
2842 WHERE lookup_type='GMS_DOC_NOTIF'
2843 AND lookup_code = header ;
2844
2845 /*
2846 -- Bug 3465169 : Modified the below sql to fix issue 'Sharable memory is greater than 1000000.'
2847 -- Fix : The below cursor was using gms_status_commitments_v which inturn
2848 -- fires gms_commitment_encumbered_v twice i.e once for fetching raw line
2849 -- and once for burden line.
2850 -- Hence modified the sql to directly use gms_status_commitments_v for fetching
2851 -- raw data and calculate burden for each raw line.
2852 -- The option of directly calling base tables was not feasible as there was
2853 -- no much performance improvement and also would result in code duplication
2854 -- as the below sql needs AP,REQ,PO and burden calculation logic.
2855
2856 CURSOR open_commitments( p_installment_id VARCHAR2
2857 ,p_award_id NUMBER
2858 ,p_installment_start_date DATE
2859 ,p_installment_end_date DATE ) IS
2860 SELECT gscv.commitment_number
2861 ,gscv.document_type
2862 ,SUM(gscv.burdened_cost)
2863 FROM gms_status_commitments_v gscv ,gms_summary_project_fundings gmpf
2864 WHERE gmpf.installment_id = p_installment_id
2865 AND gscv.award_id = p_award_id
2866 AND gscv.project_id = gmpf.project_id
2867 AND gscv.task_id = nvl(gmpf.task_id,gscv.task_id)
2868 AND gscv.expenditure_item_date BETWEEN p_installment_start_date AND p_installment_end_date
2869 AND gscv.document_type IN ('AP','PO','REQ')
2870 GROUP BY document_type , gscv.project_id , gscv.task_id , award_id , commitment_number ; */
2871 --For Bug 4948033:SQL Repository :Modified the Select statement so that
2872 --Shared memory gets reduced
2873 --
2874
2875
2876 CURSOR open_commitments( p_installment_id VARCHAR2
2877 ,p_award_id NUMBER
2878 ,p_installment_start_date DATE
2879 ,p_installment_end_date DATE ) IS
2880 SELECT cmt.cmt_number
2881 ,cmt.document_type
2882 ,PA_CURRENCY.ROUND_CURRENCY_AMT(SUM (cmt.acct_raw_cost +
2883 DECODE(nvl(cmt.ind_compiled_set_id,0),0,0,
2884 DECODE(NVL(cmt.burdenable_raw_cost,0),0,0,
2885 gms_wf_pkg.Get_Burden_amount(cmt.expenditure_type,
2886 cmt.organization_id,
2887 cmt.ind_compiled_set_id,
2888 cmt.burdenable_raw_cost)
2889 )
2890 )
2891 )
2892 )
2893 FROM gms_commitment_encumbered_v cmt
2894 WHERE cmt.award_id = p_award_id
2895 AND (cmt.project_id,cmt.task_id) IN (SELECT gmpf.project_id,nvl(gmpf.task_id,cmt.task_id)
2896 FROM gms_summary_project_fundings gmpf
2897 WHERE gmpf.installment_id =p_installment_id )
2898 AND cmt.expenditure_item_date BETWEEN p_installment_start_date AND p_installment_end_date
2899 AND cmt.document_type IN ('AP','PO','REQ')
2900 GROUP BY document_type , cmt.project_id , cmt.task_id , cmt.award_id , cmt.cmt_number ;
2901
2902 BEGIN
2903
2904 -- Get the values of all the attributes used in this procedure to generate an message.
2905 l_item_type:= substr(document_id, 1, instr(document_id, ':') - 1);
2906 l_item_key := substr(document_id, instr(document_id, ':') + 1,length(document_id) - 2);
2907 l_document_id := wf_engine.GetItemAttrNumber
2908 (itemtype => l_item_type,
2909 itemkey => l_item_key,
2910 aname => 'DOCUMENT_ID');
2911 l_Award_number := wf_engine.GetItemAttrText
2912 (itemtype => l_item_type,
2913 itemkey => l_item_key,
2914 aname => 'AWARD_NUMBER');
2915 l_Award_id := wf_engine.GetItemAttrNumber
2916 (itemtype => l_item_type,
2917 itemkey => l_item_key,
2918 aname => 'AWARD_ID');
2919 l_Installment_id := wf_engine.GetItemAttrNumber
2920 (itemtype => l_item_type,
2921 itemkey => l_item_key,
2922 aname => 'INSTALL_ID');
2923 l_Installment_number := wf_engine.GetItemAttrText
2924 (itemtype => l_item_type,
2925 itemkey => l_item_key,
2926 aname => 'INSTALL_NUM');
2927
2928 l_Installment_End_date:= wf_engine.GetItemAttrDate
2929 (itemtype => l_item_type,
2930 itemkey => l_item_key,
2931 aname => 'INSTALL_END_DATE');
2932
2933 l_Installment_start_date:= wf_engine.GetItemAttrDate
2934 (itemtype => l_item_type,
2935 itemkey => l_item_key,
2936 aname => 'INSTALL_START_DATE');
2937
2938
2939 --Changes for bug:7538344 starts here
2940 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
2941 or (FND_RELEASE.MAJOR_VERSION > 12) then
2942
2943 begin
2944
2945 --Obtain the user_id based on the unique user_name
2946 SELECT USER_ID
2947 INTO l_user_id
2948 FROM FND_USER
2949 WHERE user_name = FND_GLOBAL.User_Name;
2950 exception
2951 when NO_DATA_FOUND then
2952 l_user_id := to_number(null);
2953 end;
2954 if (display_type=wf_notification.doc_html) then
2955 l_Installment_End_date_text := to_char(l_Installment_End_date,
2956 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',l_user_id),
2957 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || ''''); /*Added for bug:8974271*/
2958 else
2959 l_Installment_End_date_text := to_char(l_Installment_End_date,
2960 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',l_user_id),
2961 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id), 'GREGORIAN') || ''''); /*Added for bug:8974271*/
2962 end if;
2963 else
2964 l_Installment_End_date_text := to_char(l_Installment_End_date);
2965 end if;
2966 --Changes for bug:7538344 Ends here
2967
2968 -- Thel message that's going to be printed in the notification is concatenated to the l_document variable.
2969 -- based on the different conditions the document to be printed is framed.
2970
2971 IF (display_type = 'text/html') THEN
2972
2973 l_document := NL || NL || '<!-- INSTALLMENT_CLOSEOUT -->'|| NL || NL || '<P><B>';
2974
2975 -- Set the tokens of the message.
2976 fnd_message.set_name('GMS', 'GMS_WF_INSTALL_END_DATE');
2977 fnd_message.set_token('INSTALLMENT_NUMBER', l_installment_number);
2978 fnd_message.set_token('AWARD_NUMBER', l_award_number);
2979 fnd_message.set_token('INSTALLMENT_END_DATE', l_installment_end_date_text); --added for bug:7538344
2980 l_document := l_document || fnd_message.get|| NL;
2981 l_document := l_document || '</P></B>' || NL;
2982
2983 -- Set the tokens of the message
2984 l_document := l_document || '<P><B>' ;
2985 fnd_message.set_name('GMS', 'GMS_WF_NOTIFY_INSTALL_CLSOUT');
2986 -- Commented the following token as part of bug fix 2049763
2987 -- fnd_message.set_token('INSTALLMENT_NUMBER', l_installment_number);
2988 l_document := l_document || fnd_message.get|| NL;
2989 l_document := l_document || '</B></P>' || NL;
2990
2991 -- Open the cursor and fetch values .if no data is found than nothing will be printed else the
2992 -- GMS_WF_NOTIFY_OPEN_COIMMITMENTS gets printed and also the open commitments will be printed in a table format
2993
2994 OPEN open_commitments(l_installment_id ,
2995 l_award_id ,
2996 l_installment_start_date,
2997 l_installment_end_date) ;
2998
2999 FETCH open_commitments INTO l_commitment_number ,
3000 l_commit_document_type,
3001 l_amount ;
3002 IF (open_commitments%FOUND ) THEN
3003 l_document := l_document || '<P><B>' ;
3004 l_document := l_document || fnd_message.get_string('GMS', 'GMS_WF_NOTIFY_OPEN_COMMT')|| NL;
3005 l_document := l_document || '<P></B>' || NL;
3006 l_document := l_document || '<TABLE border=1 cellpadding=2 cellspacing=1>' || NL;
3007 l_document := l_document || '<TR>';
3008
3009 OPEN lookup_document('DOCN');
3010 FETCH lookup_document INTO l_header;
3011 CLOSE lookup_document;
3012
3013 l_document := l_document || '<TH>' || l_header || '</TH>' || NL;
3014 OPEN lookup_document('DOCT');
3015 FETCH lookup_document INTO l_header;
3016 CLOSE lookup_document;
3017 l_document := l_document || '<TH>' || l_header || '</TH>' || NL;
3018 OPEN lookup_document('AMT');
3019 FETCH lookup_document INTO l_header ;
3020 CLOSE lookup_document;
3021 l_document := l_document || '<TH>' || l_header || '</TH>' || NL;
3022 l_document := l_document || '</TR>' || NL;
3023 LOOP
3024 l_document := l_document || '<TR>' || NL;
3025 l_document := l_document || '<TD nowrap align=center>' || l_commitment_number || '</TD>' || NL;
3026
3027 -- Based on the Document type the following text will be printed under the document type header.
3028
3029 OPEN lookups_cursor(l_commit_document_type);
3030 FETCH lookups_cursor into l_commit_document_type_desc;
3031 CLOSE lookups_cursor;
3032
3033 l_document := l_document || '<TD nowrap>' || l_commit_document_type_desc || '</TD>' || NL;
3034 l_document := l_document || '<TD nowrap>' || l_amount || '</TD>' || NL;
3035 l_document := l_document || '</TR>' || NL;
3036
3037 FETCH open_commitments INTO l_commitment_number
3038 ,l_commit_document_type
3039 ,l_amount;
3040 EXIT WHEN open_commitments%NOTFOUND;
3041 END LOOP;
3042 document_type := 'text/html';
3043 l_document := l_document || '</TABLE></P>' || NL;
3044 END IF;
3045 CLOSE open_commitments;
3046 document := l_document;
3047
3048 -- If the display type is text/plain
3049
3050 ELSIF (display_type = 'text/plain') THEN
3051
3052 fnd_message.set_name('GMS','GMS_WF_INSTALL_END_DATE');
3053 fnd_message.set_token('INSTALLMENT_NUMBER', l_installment_number);
3054 fnd_message.set_token('AWARD_NUMBER', l_award_number);
3055 fnd_message.set_token('INSTALLMENT_END_DATE', l_installment_end_date_text ); --added for bug:7538344
3056 l_document := l_document || fnd_message.get || NL;
3057
3058 l_document := l_document || NL;
3059 fnd_message.set_name('GMS', 'GMS_WF_NOTIFY_INSTALL_CLSOUT');
3060 -- Commented the following token as part of bug fix 2049763
3061 -- fnd_message.set_token('INSTALLMENT_NUMBER', l_installment_number);
3062 l_document := l_document || fnd_message.get || NL;
3063
3064 OPEN open_commitments(l_installment_id
3065 ,l_award_id
3066 ,l_installment_start_date
3067 ,l_installment_end_date);
3068
3069 FETCH open_commitments INTO l_commitment_number
3070 ,l_commit_document_type
3071 ,l_amount;
3072 l_document := l_document || NL;
3073 IF (open_commitments%found ) THEN
3074 l_document := l_document || fnd_message.get_string('GMS', 'GMS_WF_NOTIFY_OPEN_COMMT')|| NL;
3075 l_document := l_document || NL;
3076
3077 OPEN lookup_document('DOCN');
3078 FETCH lookup_document INTO l_header;
3079 CLOSE lookup_document;
3080
3081 l_document := l_document ||rpad(l_header,50);
3082
3083 OPEN lookup_document('DOCT');
3084 FETCH lookup_document INTO l_header ;
3085 CLOSE lookup_document;
3086
3087 l_document := l_document || rpad(l_header,16);
3088
3089 OPEN lookup_document('AMT');
3090 FETCH lookup_document INTO l_header;
3091 CLOSE lookup_document;
3092
3093 l_document := l_document || l_header ;
3094
3095 l_document := l_document || NL ;
3096
3097 --Rpad is used for handling the padding of text
3098
3099 LOOP
3100 l_document := l_document || NL ;
3101 l_document := l_document || rpad(l_commitment_number,50);
3102
3103 OPEN lookups_cursor(l_commit_document_type);
3104 FETCH lookups_cursor into l_commit_document_type_desc;
3105 CLOSE lookups_cursor;
3106
3107 l_document := l_document || rpad(l_commit_document_type_desc,16);
3108 l_document := l_document || l_amount;
3109 l_document := l_document || NL;
3110 FETCH open_commitments INTO l_commitment_number ,l_commit_document_type, l_amount;
3111 EXIT WHEN open_commitments%NOTFOUND;
3112 END LOOP;
3113 END IF;
3114 document_type := 'text/plain';
3115 CLOSE open_commitments;
3116 l_document := l_document;
3117 document := l_document;
3118 END IF;
3119 END Get_Inst_Open_Commitments;
3120
3121
3122 /*===================================================================================================
3123 Following Procedure Set the values of the workflow attributes and the starts the work flow process
3124 ====================================================================================================*/
3125
3126 PROCEDURE Start_Inst_Clsout_wf_Process (
3127 x_award_id IN NUMBER
3128 , x_installment_id IN NUMBER
3129 , x_role IN VARCHAR2
3130 , x_err_code OUT NOCOPY NUMBER
3131 , x_err_stage OUT NOCOPY VARCHAR2 ) IS
3132
3133
3134
3135 ItemType VARCHAR2(30) := 'GMSWF';
3136 ItemKey NUMBER;
3137 l_installment_end_date gms_installments.end_date_active%TYPE;
3138 l_installment_start_date gms_installments.start_date_active%TYPE;
3139 l_award_number gms_awards.award_number%TYPE;
3140 l_installment_number gms_installments.installment_num%TYPE;
3141
3142 CURSOR award_cursor IS
3143 SELECT award_number
3144 FROM gms_awards
3145 WHERE award_id = x_award_id;
3146
3147 CURSOR installment_cursor is
3148 SELECT installment_num
3149 ,end_date_active
3150 ,start_date_active
3151 FROM gms_installments
3152 WHERE installment_id = x_installment_id;
3153
3154
3155 BEGIN
3156 x_err_code := 0;
3157 SELECT gms_workflow_itemkey_s.NEXTVAL INTO ItemKey FROM DUAL;
3158
3159 OPEN award_cursor;
3160 FETCH award_cursor INTO l_award_number;
3161 close award_cursor;
3162
3163 OPEN installment_cursor;
3164 FETCH installment_cursor INTO l_installment_number,l_installment_end_date, l_installment_start_date;
3165 CLOSE installment_cursor;
3166
3167 -- Creating the workflow Process
3168
3169 wf_engine.CreateProcess( ItemType => ItemType,
3170 ItemKey => ItemKey,
3171 process => 'GMS_WF_PROCESS' );
3172
3173 -- Attribute GMS_WF_PROCESS is used to select the appropriate branch
3174 -- in the workflow process.
3175 -- Set the values for all the attributes used for this workflow process
3176
3177 wf_engine.SetItemAttrText ( itemtype => itemtype,
3178 itemkey => itemkey,
3179 aname => 'GMS_WF_PROCESS',
3180 avalue => 'INSTALLMENT_CLOSEOUT');
3181
3182 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
3183 itemkey => itemkey,
3184 aname => 'AWARD_ID',
3185 avalue => x_award_id);
3186
3187 wf_engine.SetItemAttrText ( itemtype => itemtype,
3188 itemkey => itemkey,
3189 aname => 'AWARD_NUMBER',
3190 avalue => l_award_number);
3191
3192 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
3193 itemkey => itemkey,
3194 aname => 'INSTALL_ID',
3195 avalue => x_installment_id);
3196
3197 wf_engine.SetItemAttrText ( itemtype => itemtype,
3198 itemkey => itemkey,
3199 aname => 'INSTALL_NUM',
3200 avalue => l_installment_number);
3201
3202 wf_engine.SetItemAttrDate ( itemtype => itemtype,
3203 itemkey => itemkey,
3204 aname => 'INSTALL_END_DATE',
3205 avalue => l_installment_end_date);
3206
3207 wf_engine.SetItemAttrDate ( itemtype => itemtype,
3208 itemkey => itemkey,
3209 aname => 'INSTALL_START_DATE',
3210 avalue => l_installment_start_date);
3211
3212 wf_engine.SetItemAttrText ( itemtype => itemtype,
3213 itemkey => itemkey,
3214 aname => 'DOCUMENT_ID',
3215 avalue => ItemType ||':'||to_char(ItemKey));
3216
3217 wf_engine.SetItemAttrtext( itemtype => itemtype,
3218 itemkey => itemkey,
3219 aname => 'CLOSEOUT_MESSAGE',
3220 avalue => 'PLSQL:GMS_WF_PKG.Get_Inst_Open_Commitments/'||itemtype||':'||to_char(itemkey));
3221
3222 wf_engine.SetItemAttrText ( itemtype => itemtype,
3223 itemkey => itemkey,
3224 aname => 'NOTIF_RECIPIENT_ROLE',
3225 avalue => x_role);
3226 -- Start the work flow process
3227
3228 wf_engine.StartProcess ( itemtype => itemtype,
3229 itemkey => itemkey );
3230 -- Added Exception for Bug:2662848
3231 EXCEPTION
3232 when OTHERS then
3233 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
3234 x_token_name1 => 'SQLCODE',
3235 x_token_val1 => sqlcode,
3236 x_token_name2 => 'SQLERRM',
3237 x_token_val2 => sqlerrm,
3238 x_err_code => x_err_code,
3239 x_err_buff => x_err_stage);
3240
3241 APP_EXCEPTION.RAISE_EXCEPTION;
3242
3243 END Start_Inst_Clsout_wf_Process;
3244
3245
3246
3247 /*========================================================================================================
3248 Following Logic selects installments for which notification are to be generated
3249 and then calls the procedure which kicks of the workflow process in loop for each installment selected.
3250 ========================================================================================================*/
3251
3252 PROCEDURE Notify_Installment_Closeout(
3253 ERRBUF OUT NOCOPY VARCHAR2
3254 ,RETCODE OUT NOCOPY VARCHAR2
3255 ,p_offset_days IN NUMBER ) IS
3256
3257 --Cursor to selects all the installments which are going to get closed by the offset number of days
3258 CURSOR award_install_cursor is
3259 SELECT ga.award_id ,
3260 gi.installment_id
3261 FROM gms_awards ga,
3262 gms_installments gi
3263 WHERE gi.award_id = ga.award_id
3264 AND trunc(gi.end_date_active )= trunc(SYSDATE) + p_offset_days
3265 AND ga.status <> 'CLOSED' -- Change from 'ACTIVE' to fix bug 2200585
3266 AND gi.active_flag = 'Y'
3267 AND ga.award_template_flag ='DEFERRED'; --Added to fix bug 2200585
3268 -- AND ga.budget_wf_enabled_flag = 'Y' Commented out NOCOPY to fix bug 2200585
3269
3270 --Cursor to select corresponding user id attached to each award personnel .
3271 CURSOR gms_notification_cursor ( p_award_id NUMBER) IS
3272 SELECT gn.user_id ,
3273 fu.user_name
3274 FROM gms_notifications gn, fnd_user fu
3275 WHERE gn.user_id = fu.user_id
3276 AND event_type = 'INSTALLMENT_CLOSEOUT'
3277 AND award_id = p_award_id;
3278
3279
3280
3281 -- Declare the variables Used during the workflow process
3282
3283 l_award_id gms_awards.award_id%TYPE;
3284 l_installment_id gms_installments.installment_id%TYPE;
3285 l_user_id fnd_user.user_id%TYPE;
3286 l_user_name fnd_user.user_name%TYPE;
3287 l_role_name VARCHAR2(1000);
3288 l_role_name_disp VARCHAR2(1000); -- Fix for NOCOPY related issues.
3289 --start bug fix 2204122 changed the width of var to 32000 from 4000--
3290 l_role_users VARCHAR2(32000) := NULL;
3291 --end bug fix 2204122--
3292
3293 l_offset_days NUMBER;
3294
3295 -- Declare variable used to handle errors
3296 l_err_code NUMBER;
3297 l_err_stage VARCHAR2(630);
3298 WF_API_EXCEPTION EXCEPTION;
3299 PRAGMA EXCEPTION_INIT(WF_API_EXCEPTION, -20002);
3300
3301 BEGIN
3302 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
3303
3304 l_offset_days := p_offset_days;
3305
3306 -- If the value of the parameter passed from the concurrent request is null then pick up the value
3307 -- set at profile option
3308
3309 IF l_offset_days IS NULL THEN
3310 FND_PROFILE.GET('GMS_NOTIFICATION_OFFSET_DAYS', l_offset_days);
3311 END IF;
3312 IF (l_offset_days >= 0) THEN
3313
3314 -- After a valid offset days is supplied .Open the cursor which returns all the Installments that are going to be ended by offset
3315 -- number of days. Loop is created for each Installment.
3316
3317 OPEN award_install_cursor ;
3318 LOOP
3319 FETCH award_install_cursor INTO l_award_id , l_installment_id;
3320 EXIT WHEN award_install_cursor%NOTFOUND;
3321
3322 -- For each Installment id open the cursor c2 which will fetch all the persons who are attached to this award and
3323 -- concatenates with the l_role_users variable.
3324
3325 l_role_users := NULL; -- Bug 6137699: Base Bug 6034495
3326
3327 OPEN gms_notification_cursor (l_award_id);
3328
3329 LOOP
3330 FETCH gms_notification_cursor INTO l_user_id,l_user_name;
3331 EXIT WHEN gms_notification_cursor%NOTFOUND;
3332 ---bug# 3224843---
3333 IF Excl_Person_From_Notification(l_award_id, l_user_id) = 'N' THEN
3334 l_role_users := l_role_users ||','||l_user_name;
3335 END IF;
3336 ---bug# 3224843---
3337
3338 END LOOP;
3339
3340 CLOSE gms_notification_cursor;
3341
3342
3343
3344 -- Use the following logic to remove an extra comma (,) in the starting of l_role_users
3345
3346 IF SUBSTR(l_role_users,1,1) = ',' THEN
3347 l_role_users:=SUBSTR(l_role_users,2,(LENGTH(l_role_users)-1));
3348 END IF;
3349
3350 l_role_name := l_installment_id||'-'|| 'INSTALLMENT_CLOSEOUT';
3351 l_role_name_disp := l_role_name ;
3352
3353
3354 --create an Adhoc role
3355 --language and territory are not passed .These parameters will be resolved based on the sessions setting.
3356
3357 BEGIN
3358 wf_directory.CreateAdhocRole(
3359 role_name => l_role_name,
3360 role_display_name => l_role_name_disp,
3361 notification_preference => 'MAILHTML'
3362 );
3363 EXCEPTION
3364 WHEN WF_API_EXCEPTION THEN NULL;
3365 END;
3366
3367 -- Delete the users attached to the New role created calling the following function :
3368 wf_directory.RemoveUsersFromAdhocRole(role_name => l_role_name);
3369
3370 --Add all the users retrieved from the cursor 2 to the new role created
3371 BEGIN
3372 --start bug fix 2204122--
3373 gms_client_extn_budget_wf.call_wf_addusers_to_adhocrole(p_user_roles => l_role_users
3374 ,p_role_name => l_role_name) ;
3375
3376 -- wf_directory.AddUsersToAdhocRole( role_name => l_role_name
3377 -- ,role_users => l_user_roles);
3378 --end bug fix 2204122--
3379 /* wf_directory.AddUsersToAdhocRole( role_name => l_role_name
3380 ,role_users => l_role_users);*/
3381 EXCEPTION
3382 WHEN WF_API_EXCEPTION THEN NULL;
3383 END;
3384
3385
3386 -- Call the procedure which starts the concurrent process
3387 Start_Inst_Clsout_Wf_Process( x_award_id => l_award_id,
3388 x_installment_id => l_installment_id,
3389 x_role => l_role_name,
3390 x_err_code => l_err_code,
3391 x_err_stage => l_err_stage);
3392
3393 END LOOP;
3394 CLOSE award_install_cursor;
3395 END IF;
3396 EXCEPTION
3397 WHEN VALUE_ERROR THEN
3398 -- Added RETCODE and ERRBUF for Bug:2464800
3399 RETCODE := '2';
3400 ERRBUF := 'The following error occured : '||sqlerrm;
3401 IF L_DEBUG = 'Y' THEN
3402 gms_error_pkg.gms_debug('Invalid value for Profile GMS_NOTIFICATION_OFFSET_DAYS', 'C');
3403 END IF;
3404 WHEN OTHERS THEN
3405 -- Changed RETCODE to 2 for Bug:2464800
3406 RETCODE := '2';
3407 ERRBUF := 'The following error occured : '||sqlerrm;
3408
3409 END Notify_Installment_Closeout;
3410 --End : Build of the installment closeout Notification Bug # 1969587
3411
3412 -- -------------------
3413 -- FUNCTION
3414 -- -------------------
3415 -- This function prevents notifying inactive Key Members
3416 FUNCTION Excl_Person_From_Notification
3417 (p_award_id NUMBER, p_user_id NUMBER)
3418 RETURN VARCHAR2 IS
3419 --This function returns either 'Y' to exclude person from getting notifications
3420 --OR 'N' to receive notifications.
3421 ------bug# 3224843 ----
3422 --This checks if person exists in personnal tab
3423 --user_id will always bring a unique person_id
3424 cursor chk_person_exists ( p_person_id Number )
3425 is
3426 select 1
3427 from gms_personnel gmsp
3428 where gmsp.award_id = p_award_id
3429 and gmsp.person_id = p_person_id;
3430 /*******
3431 and gmsp.person_id in
3432 (select fndu.employee_id
3433 from fnd_user fndu
3434 where fndu.user_id = p_user_id) ;
3435 *******/ --commented as per bug# 3495840 fix
3436
3437 --Bug# 3495840
3438 --Check to see the person an ACTIVE user and a HR person
3439 --user_id will always bring a unique person_id
3440 --Also, beware that employee_id could be blank too
3441 --blank employee_id should be rejected
3442 cursor is_user_active
3443 is
3444 select fndu.employee_id /*Bug 5122724 */
3445 from fnd_user fndu
3446 ,per_people_f p
3447 where p.effective_start_date = (select min(pp.effective_start_date)
3448 from per_all_people_f pp
3449 where pp.person_id = p.person_id
3450 and pp.effective_end_date >=trunc(sysdate))
3451 and ((p.employee_number is not null) OR (p.npw_number is not null))
3452 and p.person_id = fndu.employee_id
3453 and fndu.user_id = p_user_id;
3454 /*and trunc(sysdate) between start_date
3455 and nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))*/
3456
3457 --Only person active will receive notification
3458 cursor chk_person_active ( p_person_id NUMBER)
3459 is
3460 select 1
3461 from gms_personnel
3462 where person_id = p_person_id
3463 and award_id = p_award_id
3464 and trunc(sysdate) between start_date_active
3465 and nvl(end_date_active,to_date('12/31/4712','MM/DD/YYYY'));
3466
3467
3468 --local variable
3469 l_person_id NUMBER;
3470 l_count NUMBER;
3471
3472 BEGIN
3473
3474 --Bug# 3495840
3475 --
3476 --Check if the person is an ACTIVE system user as of run date
3477 -- and if the person is an ACTIVE employee or a contingent worker
3478 --
3479 OPEN is_user_active;
3480 FETCH is_user_active INTO l_person_id;
3481 CLOSE is_user_active;
3482
3483 --Exclude, as user is not active
3484 IF l_person_id IS NULL THEN
3485 RETURN 'Y';
3486 END IF;
3487 --
3488 --Finally, if the person is end-dated in personnel tab of
3489 --award window then do not send notification to this person
3490 --
3491
3492 --Check if person is listed in award
3493 OPEN chk_person_exists ( l_person_id );
3494 FETCH chk_person_exists INTO l_count;
3495 CLOSE chk_person_exists;
3496 --person exists in award
3497 IF l_count is NOT NULL THEN
3498 OPEN chk_person_active ( l_person_id );
3499 FETCH chk_person_active INTO l_count;
3500 l_count := chk_person_active%ROWCOUNT;
3501 CLOSE chk_person_active;
3502
3503 --person not active
3504 IF l_count = 0 THEN
3505 RETURN 'Y';
3506 END IF;
3507 END IF;
3508 --do not exclude
3509 RETURN 'N';
3510 END Excl_Person_From_Notification;
3511 END gms_wf_pkg;