[Home] [Help]
PACKAGE BODY: APPS.GMS_CLIENT_EXTN_BUDGET_WF
Source
1 PACKAGE BODY gms_client_extn_budget_wf AS
2 /* $Header: gmsfbceb.pls 120.4 2011/04/08 10:18:04 rrambati ship $ */
3
4 -- -------------------------------------------------------------------------------------
5 -- GLOBALS
6 -- -------------------------------------------------------------------------------------
7
8 G_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
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 -- -------------------------------------------------------------------------------------
13 -- PROCEDURES
14 -- -------------------------------------------------------------------------------------
15
16 --
17 --Name: IS_BUDGET_WF_USED
18 --Type: Procedure
19 --Description: This procedure must return a "T" or "F" depending on whether a workflow
20 -- should be started for this particular budget.
21 --
22 --
23 --Called Subprograms: none.
24 --
25 --Notes:
26 -- This client extension is called directly from the Budgets form and the public
27 -- Baseline_Budget API (actually, from a wrapper with the same name).
28 --
29 -- This extension is NOT called form workflow!
30 --
31 -- Error messages in the form and public API call the 'GMS_WF_CLIENT_EXTN'
32 -- error code. Two tokens are passed to the error message: the name of this
33 -- client extension and the error code.
34 --
35 --
36 --
37 --
38 --History:
39 --
40 --
41 -- IN Parameters
42 -- p_project_id - Unique identifier for the project of the budget for which approval
43 -- is requested.
44 -- p_award_id - Unique identifier for the award of the budget for which approval
45 -- is requested.
46 -- p_budget_type_code - Unique identifier for budget submitted for approval
47 -- p_pm_product_code - The PM vendor's product code stored in gms_budget_versions.
48 --
49 -- OUT NOCOPY Parameters
50 -- p_result - 'T' or 'F' (True/False)
51 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
52 -- x > 0, Business Rule Violated.
53 -- p_err_stage - Standard error message
54 -- p_err_stack - Not used.
55 --
56
57
58
59
60
61 PROCEDURE IS_BUDGET_WF_USED
62 ( p_project_id IN NUMBER
63 , p_award_id IN NUMBER
64 , p_budget_type_code IN VARCHAR2
65 , p_pm_product_code IN VARCHAR2
66 , p_result IN OUT NOCOPY VARCHAR2
67 , p_err_code IN OUT NOCOPY NUMBER
68 , p_err_stage IN OUT NOCOPY VARCHAR2
69 , p_err_stack IN OUT NOCOPY VARCHAR2
70 )
71
72 IS
73 /*
74 You can use this procedure to add/modify the conditions to enable
75 workflow for budget status changes. By default, Oracle Projects enables
76 and launches workflow based on the Budget Type and Project type setup.
77 You can choose to override these conditions with your own conditions
78
79 */
80
81
82 -- check if WF is enabled for the Award Budget
83
84 CURSOR l_award_csr (p_award_id NUMBER)
85 IS
86 SELECT budget_wf_enabled_flag
87 FROM gms_awards
88 WHERE award_id = p_award_id;
89
90 l_budget_wf_enabled_flag gms_awards.budget_wf_enabled_flag%TYPE := 'N';
91
92 BEGIN
93
94 -- dbms_output.put_line('GMS_CLIENT_EXTN_BUDGET_WF.IS_BUDGET_WF_USED - start');
95
96 -- Initialize The Output Parameters
97
98 p_err_code := 0;
99 p_result := 'F';
100
101 -- Enter Your Business Rules Here.Or, Use The
102 -- Provided Default.
103
104 OPEN l_award_csr (p_award_id);
105 FETCH l_award_csr INTO l_budget_wf_enabled_flag;
106 CLOSE l_award_csr ;
107
108 IF (l_budget_wf_enabled_flag = 'Y')
109 THEN
110 p_result := 'T';
111 ELSE
112 p_result := 'F';
113 END IF;
114
115 -- dbms_output.put_line('BUDGET_WF_USED - RESULT = '||p_result);
116
117
118 EXCEPTION
119
120 WHEN OTHERS THEN
121 -- Add your exception handler here.
122 -- To raise an ORACLE error, assign SQLCODE to p_error_code
123 p_err_code := SQLCODE;
124 RAISE;
125
126 END IS_BUDGET_WF_USED;
127 --------------------------------------------------------------------------------------------
128
129 -- Name: START_BUDGET_WF
130 -- Type: Procedure
131 -- Description: This procedure is used to start a workflow process to approve
132 -- and baseline a budget.
133 --
134 --
135 -- Called subprograms: none.
136 --
137 --
138 --
139 -- History:
140 --
141 --
142 -- IN Parameters
143 -- p_project_id - Unique identifier for the project of the budget for which approval
144 -- is requested.
145 -- p_budget_type_code - Unique identifier for budget submitted for approval
146 -- p_mark_as_original - Yes, mark budget as original; N, do not mark. Defaults to 'N'.
147 --
148 -- OUT NOCOPY Parameters
149 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
150 -- x > 0, Business Rule Violated.
151 -- p_err_stage - Standard error message
152 -- p_err_stack - Not used.
153 --
154
155
156 PROCEDURE START_BUDGET_WF
157 (p_draft_version_id IN NUMBER
158 , p_project_id IN NUMBER
159 , p_award_id IN NUMBER
160 , p_budget_type_code IN VARCHAR2
161 , p_mark_as_original IN VARCHAR2
162 , p_item_type OUT NOCOPY VARCHAR2
163 , p_item_key OUT NOCOPY VARCHAR2
164 , p_err_code IN OUT NOCOPY NUMBER
165 , p_err_stage IN OUT NOCOPY VARCHAR2
166 , p_err_stack IN OUT NOCOPY VARCHAR2
167 )
168
169 IS
170 --Notes:
171 -- This client extension is called directly from the Budgets form and the public
172 -- Baseline_Budget API (actually, from a wrapper with the same name).
173 --
174 -- !!!THIS EXTENSION IS NOT CALLED FROM WORKFLOW!!!
175 --
176 -- Error messages in the form and public API call the 'GMS_WF_CLIENT_EXTN'
177 -- error code. Two tokens are passed to the error message: the name of this
178 -- client extension and the error code.
179 --
180 -- CAUTION:
181 --
182 -- This is a working client extension. It is designed to start the
183 -- GMSBUDWF Budget workflow. If you make changes to this
184 -- procedure, you must properly populate the OUT-parameters,
185 -- particularly the p_item_type and p_item_key OUT-parameters.
186 --
187 -- Also, if you want to use a different item type or process ,you must
188 -- change the value for the variable ItemType and the
189 -- change the value for the parameter "process" in the
190 -- call to wf_engine.Create_Process.
191 -- Make sure that you have a thorough understanding
192 -- of the Oracle Workflow product and how to use PL/SQL with Workflow.
193 --
194 CURSOR l_award_csr
195 ( p_award_id NUMBER)
196 IS
197 SELECT award_number
198 , award_short_name
199 FROM gms_awards -- gms_awards_v -- Bug 4004577
200 WHERE award_id = p_award_id;
201
202 --
203
204 CURSOR l_baselined_csr
205 ( p_project_id NUMBER
206 , p_award_id NUMBER
207 , p_budget_type_code VARCHAR2 )
208 IS
209 SELECT 'x'
210 FROM gms_budget_versions
211 WHERE project_id = p_project_id
212 AND award_id = p_award_id
213 AND budget_type_code = p_budget_type_code
214 AND budget_status_code = 'B';
215
216 --
217 CURSOR l_project_csr ( p_project_id NUMBER )
218 IS
219 SELECT pm_project_reference
220 , segment1
221 , name
222 , description
223 , project_type
224 , pm_product_code
225 , carrying_out_organization_id
226 FROM pa_projects
227 WHERE project_id = p_project_id;
228 --
229
230 CURSOR l_organization_csr ( p_carrying_out_organization_id NUMBER )
231 IS
232 SELECT name
233 FROM hr_organization_units
234 WHERE organization_id = p_carrying_out_organization_id;
235 --
236 CURSOR l_project_type_class( p_project_type VARCHAR2)
237 IS
238 SELECT project_type_class_code
239 FROM pa_project_types
240 WHERE project_type = p_project_type;
241 --
242 CURSOR l_starter_user_name_csr( p_starter_user_id NUMBER )
243 IS
244 SELECT user_name
245 FROM fnd_user
246 WHERE user_id = p_starter_user_id;
247 --
248 CURSOR l_starter_full_name_csr(p_starter_user_id NUMBER )
249 IS
250 SELECT p.first_name||' '||p.last_name
251 FROM fnd_user f, per_people_f p /*Bug 5122724 */
252 where p.effective_start_date = (select
253 min(pp.effective_start_date) from per_all_people_f pp where pp.person_id =
254 p.person_id and pp.effective_end_date >=trunc(sysdate)) and
255 ((p.employee_number is not null) or (p.npw_number is not null))
256 and f.user_id = p_starter_user_id
257 and f.employee_id = p.person_id;
258 /* Replaced below sql with above sql for Bug 5122724
259 SELECT e.first_name||' '||e.last_name
260 FROM fnd_user f, per_all_people_f e --pa_employees e Commented for Bug5067575, SQLId:16329634
261 WHERE f.user_id = p_starter_user_id
262 AND f.employee_id = e.person_id
263 AND rownum=1; -- Added for Bug5067575, SQLId:16329634 */
264 --
265 CURSOR l_budget_csr( p_project_id NUMBER
266 ,p_award_id NUMBER
267 ,p_budget_type_code VARCHAR2 )
268 IS
269 SELECT pm_budget_reference
270 , description
271 , change_reason_code
272 , budget_entry_method_code
273 , pm_product_code
274 , labor_quantity
275 , raw_cost
276 , burdened_cost
277 , revenue
278 , resource_list_id
279 , budget_version_id
280 , version_name
281 FROM gms_budget_versions
282 WHERE project_id = p_project_id
283 AND award_id = p_award_id
284 AND budget_type_code = p_budget_type_code
285 --AND budget_status_code = 'S'; -- Modified on 20-May-2000
286 AND budget_status_code in ('S','W');
287 --
288
289 CURSOR l_resource_list_csr( p_resource_list_id NUMBER )
290 IS
291 SELECT name
292 , description
293 FROM pa_resource_lists
294 WHERE resource_list_id = p_resource_list_id;
295 --
296 CURSOR l_budget_type_csr( p_budget_type_code VARCHAR2 )
297 IS
298 SELECT budget_type
299 FROM pa_budget_types
300 WHERE budget_type_code = p_budget_type_code;
301
302 CURSOR l_wf_notification_role_csr(p_award_id NUMBER)
303 IS
304 SELECT user_id, user_name
305 FROM gms_notifications_v
306 WHERE award_id = p_award_id
307 AND event_type = 'BUDGET_BASELINE';
308
309 -- Get System Date for Worflow-Started-Date
310 CURSOR l_wf_started_date_csr
311 IS
312 SELECT sysdate
313 FROM sys.dual;
314
315 ItemType varchar2(30) := 'GMSWF'; --<----Identifies the workflow process!!!
316 ItemKey varchar2(30);
317
318 l_award_id gms_awards.award_id%TYPE;
319 l_award_number gms_awards.award_number%TYPE;
320 l_award_short_name gms_awards.award_short_name%TYPE;
321
322 l_pm_project_reference pa_projects.pm_project_reference%TYPE;
323 l_pa_project_number pa_projects.segment1%TYPE;
324 l_project_name pa_projects.name%TYPE;
325 l_description pa_projects.description%TYPE;
326 l_project_type pa_projects.project_type%TYPE;
327 l_pm_project_product_code pa_projects.pm_product_code%TYPE;
328 l_carrying_out_org_id NUMBER;
329 l_carrying_out_org_name hr_organization_units.name%TYPE;
330 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
331
332 l_pm_budget_reference gms_budget_versions.pm_budget_reference%TYPE;
333 l_budget_description gms_budget_versions.description%TYPE;
334 l_budget_change_reason_code gms_budget_versions.change_reason_code%TYPE;
335 l_budget_entry_method_code gms_budget_versions.budget_entry_method_code%TYPE;
336 l_pm_budget_product_code gms_budget_versions.pm_product_code%TYPE;
337 l_mark_as_original gms_budget_versions.original_flag%TYPE;
338 l_budget_version_id gms_budget_versions.budget_version_id%TYPE;
339 l_version_name gms_budget_versions.version_name%TYPE;
340
341 l_total_labor_hours NUMBER;
342 l_total_raw_cost NUMBER;
343 l_total_burdened_cost NUMBER;
344 l_total_revenue NUMBER;
345 l_resource_list_id NUMBER;
346 l_resource_list_name pa_resource_lists.name%TYPE;
347 l_resource_list_description pa_resource_lists.description%TYPE;
348 l_budget_type pa_budget_types.budget_type%TYPE;
349 l_wf_started_date DATE;
350
351 l_workflow_started_by_id NUMBER;
352 l_starter_name VARCHAR2(240);
353 l_starter_full_name VARCHAR2(240);
354
355 l_user_id NUMBER;
356 l_user_name VARCHAR2(240);
357 l_role_name VARCHAR2(100);
358 l_role_name_disp VARCHAR2(100); -- fix for NOCOPY related same variable passed to 2 paramters.
359 --Start Bug 2204122 Changed the width of l_user_roles to 32000 from 4000--
360 l_user_roles VARCHAR2(32000);
361 --End Bug 2204122--
362 l_resp_id NUMBER;
363 l_resp_appl_id NUMBER;
364 l_row_found VARCHAR2(1);
365
366 l_api_version_number NUMBER := G_api_version_number ;
367 l_msg_count NUMBER;
368 l_msg_data VARCHAR(2000);
369 l_return_status VARCHAR2(1) := NULL;
370 l_data VARCHAR2(2000);
371 l_msg_index_out NUMBER;
372 l_err_code NUMBER := 0;
373 l_err_stage VARCHAR2(100);
374 l_err_stack VARCHAR2(100);
375
376 WF_API_EXCEPTION EXCEPTION;
377 pragma exception_init(WF_API_EXCEPTION, -20002);
378
379
380 BEGIN
381
382 IF L_DEBUG = 'Y' THEN
383 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - start', 'C');
384 END IF;
385
386 -- Standard BEGIN of API savepoint
387
388 SAVEPOINT START_BUDGET_WF_pvt;
389
390 -- Set API Return Status To Success for Public API and Form Error Processing
391
392 p_err_code := 0;
393
394
395 BEGIN
396 -- --------------------------------------------------------------------------------------
397 -- Initialize Globals for Starting Approve Budget Workflow
398 -- --------------------------------------------------------------------------------------
399
400 l_workflow_started_by_id := FND_GLOBAL.user_id;
401
402 OPEN l_starter_user_name_csr( l_workflow_started_by_id );
403 FETCH l_starter_user_name_csr INTO l_starter_name;
404 CLOSE l_starter_user_name_csr;
405
406 OPEN l_starter_full_name_csr( l_workflow_started_by_id );
407 FETCH l_starter_full_name_csr INTO l_starter_full_name;
408 CLOSE l_starter_full_name_csr;
409
410 l_resp_id := FND_GLOBAL.resp_id;
411 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
412
413 -- Based on the Responsibility, Intialize the Application
414 -- Cannot call Set_Global_Attr here because the WF does NOT
415 -- Exist yet.
416
417 FND_GLOBAL.Apps_Initialize
418 (user_id => l_workflow_started_by_id
419 , resp_id => l_resp_id
420 , resp_appl_id => l_resp_appl_id);
421
422
423 -- Mark-As-Original Flag Set From IN-Parameter
424
425 l_mark_as_original := p_mark_as_original;
426
427 -- Bug 4004577
428 If GMS_SECURITY.ALLOW_QUERY(p_award_id) = 'Y' Then
429
430 OPEN l_award_csr (p_award_id);
431 FETCH l_award_csr INTO l_award_number
432 , l_award_short_name;
433 CLOSE l_award_csr;
434
435 End If;
436
437 OPEN l_project_csr(p_project_id);
438 FETCH l_project_csr INTO l_pm_project_reference
439 ,l_pa_project_number
440 ,l_project_name
441 ,l_description
442 ,l_project_type
443 ,l_pm_project_product_code
444 ,l_carrying_out_org_id;
445 CLOSE l_project_csr;
446
447 OPEN l_budget_type_csr( p_budget_type_code );
448 FETCH l_budget_type_csr INTO l_budget_type;
449 CLOSE l_budget_type_csr;
450
451 OPEN l_organization_csr( l_carrying_out_org_id );
452 FETCH l_organization_csr INTO l_carrying_out_org_name;
453 CLOSE l_organization_csr;
454
455 OPEN l_project_type_class( l_project_type );
456 FETCH l_project_type_class INTO l_project_type_class_code;
457 CLOSE l_project_type_class;
458
459 OPEN l_budget_csr( p_project_id, p_award_id, p_budget_type_code );
460 FETCH l_budget_csr INTO l_pm_budget_reference
461 ,l_budget_description
462 ,l_budget_change_reason_code
463 ,l_budget_entry_method_code
464 ,l_pm_budget_product_code
465 ,l_total_labor_hours
466 ,l_total_raw_cost
467 ,l_total_burdened_cost
468 ,l_total_revenue
469 ,l_resource_list_id
470 ,l_budget_version_id
471 ,l_version_name;
472
473 CLOSE l_budget_csr;
474
475 OPEN l_resource_list_csr( l_resource_list_id );
476 FETCH l_resource_list_csr INTO l_resource_list_name
477 ,l_resource_list_description;
478 CLOSE l_resource_list_csr;
479
480 OPEN l_wf_started_date_csr;
481 FETCH l_wf_started_date_csr INTO l_wf_started_date;
482 CLOSE l_wf_started_date_csr;
483
484 --------------------------------------------------------------------------------
485 -- Creating Role and Users required for GMS Workflow process; based on data in
486 -- GMS_NOTIFICATIONS_V table/view.
487
488 OPEN l_wf_notification_role_csr(p_award_id);
489 LOOP
490 FETCH l_wf_notification_role_csr INTO l_user_id, l_user_name;
491 --Start Bug Fix 2204122--
492 --The Exit statement should get executed first, Else the last USERID gets repeated--
493
494 EXIT WHEN l_wf_notification_role_csr%NOTFOUND;
495 --Start Bug Fix 3224843
496 IF GMS_WF_PKG.Excl_Person_From_Notification(p_award_id, l_user_id) = 'N' THEN
497 l_user_roles := (l_user_roles||','||l_user_name);
498 END IF;
499 --End Bug Fix 3224843
500 --exit when l_wf_notification_role_csr%NOTFOUND;
501 --End Bug Fix 2204122--
502 END LOOP;
503 CLOSE l_wf_notification_role_csr;
504
505 -- In order to remove an extra comma that is preceding l_user_roles
506 if substr(l_user_roles, 1, 1) = ','
507 then
508 l_user_roles := substr(l_user_roles, 2, (length(l_user_roles)-1));
509 end if;
510
511 l_role_name := p_award_id||'-BUDGET';
512 l_role_name_disp := l_role_name;
513
514 IF L_DEBUG = 'Y' THEN
515 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - l_role_name = '||l_role_name, 'C');
516 END IF;
517 --Bug 2204122 Commented the following line--
518 --Procedure call_gms_debug called to print the same in the log file--
519 -- gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - l_user_roles = '||l_user_roles, 'C');
520 gms_client_extn_budget_wf.call_gms_debug(p_user_roles => l_user_roles
521 ,p_disp_text =>'GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - l_user_roles = ') ;
522 --End Bug 2204122--
523 IF L_DEBUG = 'Y' THEN
524 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - creating Adhoc role..', 'C');
525 END IF;
526
527 BEGIN
528 wf_directory.CreateAdhocRole( role_name => l_role_name,
529 role_display_name => l_role_name_disp,
530 language => 'AMERICAN',
531 territory => 'AMERICA',
532 notification_preference => 'MAILHTML');
533 EXCEPTION
534 WHEN WF_API_EXCEPTION
535 THEN
536 NULL;
537 END;
538
539 -- Purging all the existing users (if any) in the above created role.
540
541 IF L_DEBUG = 'Y' THEN
542 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - removing users from Adhoc role..', 'C');
543 END IF;
544
545 wf_directory.RemoveUsersFromAdhocRole(role_name => l_role_name);
546
547 -------------------------------------------------------------------------------------------------------------
548 -- If there is atleast one user defined in GMS_NOTIFICATIONS_V for this award
549 -- then
550 -- add the user to the above created role.
551 -- else
552 -- raise exception since WF will fail while looking for users to send notifications to.
553 -------------------------------------------------------------------------------------------------------------
554
555 IF l_user_roles IS NOT NULL
556 THEN
557 IF L_DEBUG = 'Y' THEN
558 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - adding users to Adhoc role..', 'C');
559 END IF;
560 BEGIN
561 --Start Bug 2204122--
562
563 gms_client_extn_budget_wf.call_wf_addusers_to_adhocrole(p_user_roles => l_user_roles ,
564 p_role_name => l_role_name ) ;
565 /*wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
566 role_users => l_user_roles);*/
567 --End Bug 2204122--
568 EXCEPTION
569 WHEN WF_API_EXCEPTION
570 THEN
571 NULL;
572 END ;
573
574 ELSE
575 IF L_DEBUG = 'Y' THEN
576 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - Exception: No users found for this role.', 'C');
577 END IF;
578 gms_error_pkg.gms_message( x_err_name => 'GMS_FND_USER_NOT_CREATED',
579 x_err_code => l_err_code,
580 x_err_buff => l_err_stage);
581
582 APP_EXCEPTION.RAISE_EXCEPTION;
583 END IF;
584
585 --------------------------------------------------------------------------------
586
587 SELECT gms_workflow_itemkey_s.nextval
588 INTO ItemKey
589 from dual;
590
591
592 EXCEPTION
593
594 WHEN FND_API.G_EXC_ERROR
595 THEN
596 ROLLBACK TO START_BUDGET_WF_pvt;
597 RAISE;
598
599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
600 THEN
601 p_err_code := SQLCODE;
602 ROLLBACK TO START_BUDGET_WF_pvt;
603 RAISE;
604
605 WHEN OTHERS THEN
606 p_err_code := SQLCODE;
607 ROLLBACK TO START_BUDGET_WF_pvt;
608 RAISE;
609
610
611 END;
612
613 BEGIN
614 -- ------------------------------------------------------------------------------------
615 -- INSTANTIATE BUDGET WORKFLOW
616 -- ------------------------------------------------------------------------------------
617 -- NOTE:
618 -- The process name passed here is the root process for the
619 -- 'GMS Workflow Process'. The Selector procedure may override
620 -- the process name specified here. However, the default
621 -- GMS_WF procedure does not call the Selector procedure.
622 -- ------------------------------------------------------------------------------------
623
624 IF L_DEBUG = 'Y' THEN
625 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - Calling wf_engine.CreateProcess..', 'C');
626 END IF;
627
628 wf_engine.CreateProcess( ItemType => ItemType,
629 ItemKey => ItemKey,
630 process => 'GMS_WF_PROCESS' );
631
632 IF L_DEBUG = 'Y' THEN
633 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - Setting Item Attributes..', 'C');
634 END IF;
635
636
637 -- attribute GMS_WF_PROCESS is used to select the appropriate branch
638 -- in the workflow process.
639
640 wf_engine.SetItemAttrText ( itemtype => itemtype,
641 itemkey => itemkey,
642 aname => 'GMS_WF_PROCESS',
643 avalue => 'BUDGET' );
644
645 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
646 itemkey => itemkey,
647 aname => 'AWARD_ID',
648 avalue => p_award_id);
649
650 wf_engine.SetItemAttrText ( itemtype => itemtype,
651 itemkey => itemkey,
652 aname => 'AWARD_NUMBER',
653 avalue => l_award_number);
654
655 wf_engine.SetItemAttrText ( itemtype => itemtype,
656 itemkey => itemkey,
657 aname => 'AWARD_SHORT_NAME',
658 avalue => l_award_short_name);
659
660 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
661 itemkey => itemkey,
662 aname => 'PROJECT_ID',
663 avalue => p_project_id);
664
665 wf_engine.SetItemAttrText ( itemtype => itemtype,
666 itemkey => itemkey,
667 aname => 'PM_PROJECT_REFERENCE',
668 avalue => l_pm_project_reference );
669
670 wf_engine.SetItemAttrText ( itemtype => itemtype,
671 itemkey => itemkey,
672 aname => 'PA_PROJECT_NUMBER',
673 avalue => l_pa_project_number );
674
675 wf_engine.SetItemAttrText ( itemtype => itemtype,
676 itemkey => itemkey,
677 aname => 'PROJECT_NAME',
678 avalue => l_project_name );
679
680 wf_engine.SetItemAttrText ( itemtype => itemtype,
681 itemkey => itemkey,
682 aname => 'PROJECT_DESCRIPTION',
683 avalue => l_description );
684
685 wf_engine.SetItemAttrText ( itemtype => itemtype,
686 itemkey => itemkey,
687 aname => 'PROJECT_TYPE',
688 avalue => l_project_type );
689
690 wf_engine.SetItemAttrText ( itemtype => itemtype,
691 itemkey => itemkey,
692 aname => 'PM_PROJECT_PRODUCT_CODE',
693 avalue => l_pm_project_product_code );
694
695 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
696 itemkey => itemkey,
697 aname => 'CARRYING_OUT_ORG_ID',
698 avalue => l_carrying_out_org_id);
699
700 wf_engine.SetItemAttrText ( itemtype => itemtype,
701 itemkey => itemkey,
702 aname => 'CARRYING_OUT_ORG_NAME',
703 avalue => l_carrying_out_org_name);
704
705 wf_engine.SetItemAttrText ( itemtype => itemtype,
706 itemkey => itemkey,
707 aname => 'PROJECT_TYPE_CLASS_CODE',
708 avalue => l_project_type_class_code);
709
710 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'WORKFLOW_STARTED_BY_ID',
713 avalue => l_workflow_started_by_id);
714
715 wf_engine.SetItemAttrText ( itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'WORKFLOW_STARTED_BY_NAME',
718 avalue => l_starter_name);
719
720 wf_engine.SetItemAttrText ( itemtype => itemtype,
721 itemkey => itemkey,
722 aname => 'WORKFLOW_STARTED_BY_FULL_NAME',
723 avalue => l_starter_full_name);
724
725
726 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
727 itemkey => itemkey,
728 aname => 'RESPONSIBILITY_ID',
729 avalue => l_resp_id);
730
731 wf_engine.SetItemAttrText ( itemtype => itemtype,
732 itemkey => itemkey,
733 aname => 'BUDGET_TYPE_CODE',
734 avalue => p_budget_type_code);
735
736 wf_engine.SetItemAttrText ( itemtype => itemtype,
737 itemkey => itemkey,
738 aname => 'BUDGET_TYPE',
739 avalue => l_budget_type);
740
741 wf_engine.SetItemAttrText ( itemtype => itemtype,
742 itemkey => itemkey,
743 aname => 'PM_BUDGET_REFERENCE',
744 avalue => l_pm_budget_reference);
745
746 wf_engine.SetItemAttrText ( itemtype => itemtype,
747 itemkey => itemkey,
748 aname => 'BUDGET_DESCRIPTION',
749 avalue => l_budget_description);
750
751 wf_engine.SetItemAttrText ( itemtype => itemtype,
752 itemkey => itemkey,
753 aname => 'CHANGE_REASON_CODE',
754 avalue => l_budget_change_reason_code);
755
756 wf_engine.SetItemAttrText ( itemtype => itemtype,
757 itemkey => itemkey,
758 aname => 'BUDGET_ENTRY_METHOD',
759 avalue => l_budget_entry_method_code);
760
761 wf_engine.SetItemAttrText ( itemtype => itemtype,
762 itemkey => itemkey,
763 aname => 'PM_BUDGET_PRODUCT_CODE',
764 avalue => l_pm_budget_product_code);
765
766 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
767 itemkey => itemkey,
768 aname => 'TOTAL_LABOR_HOURS',
769 avalue => l_total_labor_hours);
770
771 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
772 itemkey => itemkey,
773 aname => 'TOTAL_RAW_COST',
774 avalue => l_total_raw_cost);
775
776 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
777 itemkey => itemkey,
778 aname => 'TOTAL_BURDENED_COST',
779 avalue => l_total_burdened_cost);
780
781 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
782 itemkey => itemkey,
783 aname => 'RESOURCE_LIST_ID',
784 avalue => l_resource_list_id);
785
786 wf_engine.SetItemAttrText ( itemtype => itemtype,
787 itemkey => itemkey,
788 aname => 'RESOURCE_LIST_NAME',
789 avalue => l_resource_list_name);
790
791 wf_engine.SetItemAttrText ( itemtype => itemtype,
792 itemkey => itemkey,
793 aname => 'RESOURCE_LIST_DESCRIPTION',
794 avalue => l_resource_list_description);
795
796 wf_engine.SetItemAttrText ( itemtype => itemtype,
797 itemkey => itemkey,
798 aname => 'MARK_AS_ORIGINAL',
799 avalue => l_mark_as_original);
800
801
802 wf_engine.SetItemAttrText ( itemtype => itemtype,
803 itemkey => itemkey,
804 aname => 'NOTIF_RECIPIENT_ROLE',
805 avalue => l_role_name);
806
807 wf_engine.SetItemAttrText ( itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'FC_MODE',
810 avalue => 'S');
811
812 wf_engine.SetItemAttrDate ( itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'WF_STARTED_DATE',
815 avalue => l_wf_started_date
816 );
817
818 --
819 IF L_DEBUG = 'Y' THEN
820 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - Calling wf_engine.StartProcess..', 'C');
821 END IF;
822
823 wf_engine.StartProcess( itemtype => itemtype,
824 itemkey => itemkey );
825
826 IF L_DEBUG = 'Y' THEN
827 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF - After wf_engine.StartProcess', 'C');
828 END IF;
829
830 -- -----------------------------------------------------------------------------------
831 -- CAUTION: These two OUT-Parameters must be populated
832 -- properly in order for the calling procedures
833 -- to work as designed.
834 -- ------------------------------------------------------------------------------------
835
836 p_item_type := itemtype;
837 p_item_key := itemkey;
838
839 -- -------------------------------------------------------------------------------------
840
841 --
842 EXCEPTION
843
844 WHEN FND_API.G_EXC_ERROR
845 THEN
846 WF_CORE.CONTEXT(' GMS_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
847 RAISE;
848
849 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
850 THEN
851 WF_CORE.CONTEXT(' GMS_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
852 p_err_code := SQLCODE;
853 RAISE;
854
855 WHEN OTHERS
856 THEN
857 WF_CORE.CONTEXT(' GMS_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF', itemtype, itemkey);
858 p_err_code := SQLCODE;
859 RAISE;
860
861 END;
862
863 END START_BUDGET_WF;
864
865
866 --------------------------------------------------------------------------------------------
867
868 -- Name: START_BUDGET_WF_NTFY_ONLY
869 -- Type: Procedure
870 -- Description:
871 --
872 --
873 --
874 -- Called subprograms: none.
875 --
876 --
877 --
878 -- History:
879 --
880 --
881 -- IN Parameters
882 -- p_project_id - Unique identifier for the project of the budget for which approval
883 -- is requested.
884 -- p_budget_type_code - Unique identifier for budget submitted for approval
885 -- p_mark_as_original - Yes, mark budget as original; N, do not mark. Defaults to 'N'.
886 --
887 -- OUT NOCOPY Parameters
888 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
889 -- x > 0, Business Rule Violated.
890 -- p_err_stage - Standard error message
891 -- p_err_stack - Not used.
892 --
893
894
895 PROCEDURE START_BUDGET_WF_NTFY_ONLY
896 (p_draft_version_id IN NUMBER
897 , p_project_id IN NUMBER
898 , p_award_id IN NUMBER
899 , p_budget_type_code IN VARCHAR2
900 , p_mark_as_original IN VARCHAR2
901 , p_item_type OUT NOCOPY VARCHAR2
902 , p_item_key OUT NOCOPY VARCHAR2
903 , p_err_code IN OUT NOCOPY NUMBER
904 , p_err_stage IN OUT NOCOPY VARCHAR2
905 , p_err_stack IN OUT NOCOPY VARCHAR2
906 )
907
908 IS
909 --Notes:
910 -- This client extension is called directly from the Budgets form and the public
911 -- Baseline_Budget API (actually, from a wrapper with the same name).
912 --
913 -- !!!THIS EXTENSION IS NOT CALLED FROM WORKFLOW!!!
914 --
915 -- Error messages in the form and public API call the 'GMS_WF_CLIENT_EXTN'
916 -- error code. Two tokens are passed to the error message: the name of this
917 -- client extension and the error code.
918 --
919 -- CAUTION:
920 --
921 -- This is a working client extension. It is designed to start the
922 -- GMSBUDWF Budget workflow. If you make changes to this
923 -- procedure, you must properly populate the OUT-parameters,
924 -- particularly the p_item_type and p_item_key OUT-parameters.
925 --
926 -- Also, if you want to use a different item type or process ,you must
927 -- change the value for the variable ItemType and the
928 -- change the value for the parameter "process" in the
929 -- call to wf_engine.Create_Process.
930 -- Make sure that you have a thorough understanding
931 -- of the Oracle Workflow product and how to use PL/SQL with Workflow.
932 --
933 CURSOR l_award_csr
934 ( p_award_id NUMBER)
935 IS
936 SELECT award_number
937 ,award_short_name
938 FROM gms_awards -- gms_awards_v -- Bug 4004577
939 WHERE award_id = p_award_id;
940 --
941
942 CURSOR l_baselined_csr
943 ( p_project_id NUMBER
944 , p_award_id NUMBER
945 , p_budget_type_code VARCHAR2 )
946 IS
947 SELECT 'x'
948 FROM gms_budget_versions
949 WHERE project_id = p_project_id
950 AND award_id = p_award_id
951 AND budget_type_code = p_budget_type_code
952 AND budget_status_code = 'B';
953
954 --
955 CURSOR l_project_csr ( p_project_id NUMBER )
956 IS
957 SELECT pm_project_reference
958 , segment1
959 , name
960 , description
961 , project_type
962 , pm_product_code
963 , carrying_out_organization_id
964 FROM pa_projects
965 WHERE project_id = p_project_id;
966 --
967
968 CURSOR l_organization_csr ( p_carrying_out_organization_id NUMBER )
969 IS
970 SELECT name
971 FROM hr_organization_units
972 WHERE organization_id = p_carrying_out_organization_id;
973 --
974 CURSOR l_project_type_class( p_project_type VARCHAR2)
975 IS
976 SELECT project_type_class_code
977 FROM pa_project_types
978 WHERE project_type = p_project_type;
979 --
980 CURSOR l_starter_user_name_csr( p_starter_user_id NUMBER )
981 IS
982 SELECT user_name
983 FROM fnd_user
984 WHERE user_id = p_starter_user_id;
985 --
986 CURSOR l_starter_full_name_csr(p_starter_user_id NUMBER )
987 IS
988 SELECT p.first_name||' '||p.last_name /* Bug 5122724 */
989 FROM fnd_user f, per_people_f p
990 where p.effective_start_date = (select
991 min(pp.effective_start_date) from per_all_people_f pp where pp.person_id =
992 p.person_id and pp.effective_end_date >=trunc(sysdate)) and
993 ((p.employee_number is not null) or (p.npw_number is not null))
994 and f.user_id = p_starter_user_id
995 and f.employee_id = p.person_id;
996 /* Replaced below sql with above sql for Bug 5122724
997 SELECT e.first_name||' '||e.last_name
998 FROM fnd_user f, per_all_people_f e --pa_employees e Commented for Bug5067575, SQLId:16329634
999 WHERE f.user_id = p_starter_user_id
1000 AND f.employee_id = e.person_id
1001 AND rownum=1; -- Added for Bug5067575, SQLId:16329634 */
1002 --
1003 CURSOR l_budget_csr( p_project_id NUMBER
1004 ,p_award_id NUMBER
1005 ,p_budget_type_code VARCHAR2 )
1006 IS
1007 SELECT pm_budget_reference
1008 , description
1009 , change_reason_code
1010 , budget_entry_method_code
1011 , pm_product_code
1012 , labor_quantity
1013 , raw_cost
1014 , burdened_cost
1015 , revenue
1016 , resource_list_id
1017 , budget_version_id
1018 , version_name
1019 FROM gms_budget_versions
1020 WHERE project_id = p_project_id
1021 AND award_id = p_award_id
1022 AND budget_type_code = p_budget_type_code
1023 --AND budget_status_code = 'S'; -- Modified on 20-May-2000
1024 AND budget_status_code in ('S','W');
1025 --
1026
1027 CURSOR l_resource_list_csr( p_resource_list_id NUMBER )
1028 IS
1029 SELECT name
1030 , description
1031 FROM pa_resource_lists
1032 WHERE resource_list_id = p_resource_list_id;
1033 --
1034 CURSOR l_budget_type_csr( p_budget_type_code VARCHAR2 )
1035 IS
1036 SELECT budget_type
1037 FROM pa_budget_types
1038 WHERE budget_type_code = p_budget_type_code;
1039
1040 CURSOR l_wf_notification_role_csr(p_award_id NUMBER)
1041 IS
1042 SELECT user_id, user_name
1043 FROM gms_notifications_v
1044 WHERE award_id = p_award_id
1045 AND event_type = 'BUDGET_BASELINE';
1046
1047 -- Get System Date for Worflow-Started-Date
1048 CURSOR l_wf_started_date_csr
1049 IS
1050 SELECT sysdate
1051 FROM sys.dual;
1052
1053 ItemType varchar2(30) := 'GMSWF'; --<----Identifies the workflow process!!!
1054 ItemKey varchar2(30);
1055
1056 l_award_id gms_awards.award_id%TYPE;
1057 l_award_number gms_awards.award_number%TYPE;
1058 l_award_short_name gms_awards.award_short_name%TYPE;
1059
1060 l_pm_project_reference pa_projects.pm_project_reference%TYPE;
1061 l_pa_project_number pa_projects.segment1%TYPE;
1062 l_project_name pa_projects.name%TYPE;
1063 l_description pa_projects.description%TYPE;
1064 l_project_type pa_projects.project_type%TYPE;
1065 l_pm_project_product_code pa_projects.pm_product_code%TYPE;
1066 l_carrying_out_org_id NUMBER;
1067 l_carrying_out_org_name hr_organization_units.name%TYPE;
1068 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
1069
1070 l_pm_budget_reference gms_budget_versions.pm_budget_reference%TYPE;
1071 l_budget_description gms_budget_versions.description%TYPE;
1072 l_budget_change_reason_code gms_budget_versions.change_reason_code%TYPE;
1073 l_budget_entry_method_code gms_budget_versions.budget_entry_method_code%TYPE;
1074 l_pm_budget_product_code gms_budget_versions.pm_product_code%TYPE;
1075 l_mark_as_original gms_budget_versions.original_flag%TYPE;
1076 l_budget_version_id gms_budget_versions.budget_version_id%TYPE;
1077 l_version_name gms_budget_versions.version_name%TYPE;
1078
1079 l_total_labor_hours NUMBER;
1080 l_total_raw_cost NUMBER;
1081 l_total_burdened_cost NUMBER;
1082 l_total_revenue NUMBER;
1083 l_resource_list_id NUMBER;
1084 l_resource_list_name pa_resource_lists.name%TYPE;
1085 l_resource_list_description pa_resource_lists.description%TYPE;
1086 l_budget_type pa_budget_types.budget_type%TYPE;
1087 l_wf_started_date DATE;
1088
1089 l_workflow_started_by_id NUMBER;
1090 l_starter_name VARCHAR2(240);
1091 l_starter_full_name VARCHAR2(240);
1092
1093 l_user_id NUMBER;
1094 l_user_name VARCHAR2(240);
1095 l_role_name VARCHAR2(100);
1096 l_role_name_disp VARCHAR2(100); -- for NOCOPY fix to seprate in out paramters.
1097 --Start Bug Fix 2204122 changed the width of var l_user_roles to 32000 from 4000--
1098 l_user_roles VARCHAR2(32000) := NULL;
1099 --End Bug Fix 2204122--
1100 l_resp_id NUMBER;
1101 l_resp_appl_id NUMBER;
1102 l_row_found VARCHAR2(1);
1103
1104 l_api_version_number NUMBER := G_api_version_number ;
1105 l_msg_count NUMBER;
1106 l_msg_data VARCHAR(2000);
1107 l_return_status VARCHAR2(1) := NULL;
1108 l_data VARCHAR2(2000);
1109 l_msg_index_out NUMBER;
1110 l_err_code NUMBER := 0;
1111 l_err_stage VARCHAR2(100);
1112 l_err_stack VARCHAR2(100);
1113
1114 WF_API_EXCEPTION EXCEPTION;
1115 pragma exception_init(WF_API_EXCEPTION, -20002);
1116
1117
1118 BEGIN
1119
1120 IF L_DEBUG = 'Y' THEN
1121 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - start', 'C');
1122 END IF;
1123
1124 -- Standard BEGIN of API savepoint
1125
1126 SAVEPOINT START_BUDGET_WF_NTFY_ONLY_pvt;
1127
1128 -- Set API Return Status To Success for Public API and Form Error Processing
1129
1130 p_err_code := 0;
1131
1132
1133 BEGIN
1134 -- --------------------------------------------------------------------------------------
1135 -- Initialize Globals for Starting Approve Budget Workflow
1136 -- --------------------------------------------------------------------------------------
1137
1138 l_workflow_started_by_id := FND_GLOBAL.user_id;
1139
1140 OPEN l_starter_user_name_csr( l_workflow_started_by_id );
1141 FETCH l_starter_user_name_csr INTO l_starter_name;
1142 CLOSE l_starter_user_name_csr;
1143
1144 OPEN l_starter_full_name_csr( l_workflow_started_by_id );
1145 FETCH l_starter_full_name_csr INTO l_starter_full_name;
1146 CLOSE l_starter_full_name_csr;
1147
1148 l_resp_id := FND_GLOBAL.resp_id;
1149 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
1150
1151 -- Based on the Responsibility, Intialize the Application
1152 -- Cannot call Set_Global_Attr here because the WF does NOT
1153 -- Exist yet.
1154
1155 FND_GLOBAL.Apps_Initialize
1156 (user_id => l_workflow_started_by_id
1157 , resp_id => l_resp_id
1158 , resp_appl_id => l_resp_appl_id);
1159
1160
1161 -- Mark-As-Original Flag Set From IN-Parameter
1162
1163 l_mark_as_original := p_mark_as_original;
1164
1165 -- Bug 4004577
1166 If GMS_SECURITY.ALLOW_QUERY(p_award_id) = 'Y' Then
1167 OPEN l_award_csr (p_award_id);
1168 FETCH l_award_csr INTO l_award_number
1169 , l_award_short_name;
1170 CLOSE l_award_csr;
1171 End If;
1172
1173 OPEN l_project_csr(p_project_id);
1174 FETCH l_project_csr INTO l_pm_project_reference
1175 ,l_pa_project_number
1176 ,l_project_name
1177 ,l_description
1178 ,l_project_type
1179 ,l_pm_project_product_code
1180 ,l_carrying_out_org_id;
1181 CLOSE l_project_csr;
1182
1183 OPEN l_budget_type_csr( p_budget_type_code );
1184 FETCH l_budget_type_csr INTO l_budget_type;
1185 CLOSE l_budget_type_csr;
1186
1187 OPEN l_organization_csr( l_carrying_out_org_id );
1188 FETCH l_organization_csr INTO l_carrying_out_org_name;
1189 CLOSE l_organization_csr;
1190
1191 OPEN l_project_type_class( l_project_type );
1192 FETCH l_project_type_class INTO l_project_type_class_code;
1193 CLOSE l_project_type_class;
1194
1195 OPEN l_budget_csr( p_project_id, p_award_id, p_budget_type_code );
1196 FETCH l_budget_csr INTO l_pm_budget_reference
1197 ,l_budget_description
1198 ,l_budget_change_reason_code
1199 ,l_budget_entry_method_code
1200 ,l_pm_budget_product_code
1201 ,l_total_labor_hours
1202 ,l_total_raw_cost
1203 ,l_total_burdened_cost
1204 ,l_total_revenue
1205 ,l_resource_list_id
1206 ,l_budget_version_id
1207 ,l_version_name;
1208
1209 CLOSE l_budget_csr;
1210
1211 OPEN l_resource_list_csr( l_resource_list_id );
1212 FETCH l_resource_list_csr INTO l_resource_list_name
1213 ,l_resource_list_description;
1214 CLOSE l_resource_list_csr;
1215
1216 OPEN l_wf_started_date_csr;
1217 FETCH l_wf_started_date_csr INTO l_wf_started_date;
1218 CLOSE l_wf_started_date_csr;
1219
1220 --------------------------------------------------------------------------------
1221 -- Creating Role and Users required for GMS Workflow process; based on data in
1222 -- GMS_NOTIFICATIONS_V table/view.
1223
1224 OPEN l_wf_notification_role_csr(p_award_id);
1225 LOOP
1226 FETCH l_wf_notification_role_csr INTO l_user_id, l_user_name;
1227 --start bug fix 2204122--
1228 --Exit statement to be executed first , Else the last user id gets repeated--
1229 EXIT WHEN l_wf_notification_role_csr%NOTFOUND;
1230 --start bug fix 3224843--
1231 IF GMS_WF_PKG.Excl_Person_From_Notification(p_award_id, l_user_id) = 'N' THEN
1232 l_user_roles := l_user_roles||','||l_user_name;
1233 END IF;
1234 --end bug fix 3224843--
1235 --exit when l_wf_notification_role_csr%NOTFOUND;
1236 --end bug fix 2204122--
1237
1238 END LOOP;
1239
1240 CLOSE l_wf_notification_role_csr;
1241
1242
1243 -- In order to remove an extra comma that is preceding l_user_roles
1244 if substr(l_user_roles, 1, 1) = ','
1245 then
1246 l_user_roles := substr(l_user_roles, 2, (length(l_user_roles)-1));
1247 end if;
1248
1249 l_role_name := p_award_id||'-BUDGET';
1250 l_role_name_disp := l_role_name;
1251
1252 IF L_DEBUG = 'Y' THEN
1253 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - l_role_name = '||l_role_name, 'C');
1254 END IF;
1255 --Bug 2204122 Commented the following line as this will be called--
1256 --from gms_client_extn_budget_wf.call_gms_debug--
1257 -- gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - l_user_roles = '||l_user_roles, 'C');
1258
1259 gms_client_extn_budget_wf.call_gms_debug(p_user_roles => l_user_roles
1260 ,p_disp_text => 'GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - l_user_roles = ') ;
1261 --End Bug Fix 2204122--
1262 -- Creating the Adhoc Role to which notifications are to be sent.
1263
1264 IF L_DEBUG = 'Y' THEN
1265 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - creating Adhoc role..', 'C');
1266 END IF;
1267
1268 BEGIN
1269 wf_directory.CreateAdhocRole( role_name => l_role_name,
1270 role_display_name => l_role_name_disp,
1271 language => 'AMERICAN',
1272 territory => 'AMERICA',
1273 notification_preference => 'MAILHTML');
1274 EXCEPTION
1275 WHEN WF_API_EXCEPTION
1276 THEN
1277 NULL;
1278 END;
1279
1280 -- Purging all the existing users (if any) in the above created role.
1281
1282 IF L_DEBUG = 'Y' THEN
1283 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - removing users from Adhoc role..', 'C');
1284 END IF;
1285
1286 wf_directory.RemoveUsersFromAdhocRole(role_name => l_role_name);
1287
1288 -------------------------------------------------------------------------------------------------------------
1289 -- If there is atleast one user defined in GMS_NOTIFICATIONS_V for this award
1290 -- then
1291 -- add the user to the above created role.
1292 -- else
1293 -- raise exception since WF will fail while looking for users to send notifications to.
1294 -------------------------------------------------------------------------------------------------------------
1295
1296 IF l_user_roles IS NOT NULL
1297 THEN
1298 IF L_DEBUG = 'Y' THEN
1299 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - adding users to Adhoc role..', 'C');
1300 END IF;
1301
1302 --Start Bug fix 2204122--
1303 --call procedure call_wf_addusers_to_adhocrole--
1304 BEGIN
1305 gms_client_extn_budget_wf.call_wf_addusers_to_adhocrole(p_user_roles => l_user_roles ,
1306 p_role_name => l_role_name ) ;
1307
1308 EXCEPTION
1309 WHEN WF_API_EXCEPTION
1310 THEN
1311 NULL;
1312 END ;
1313 /*begin
1314 wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
1315 role_users => l_user_roles);
1316 exception
1317 when WF_API_EXCEPTION
1318 then
1319 NULL;
1320 end;*/
1321 --End Bug fix 2204122--
1322 ELSE
1323 IF L_DEBUG = 'Y' THEN
1324 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - Exception: No users found for this role.', 'C');
1325 END IF;
1326 gms_error_pkg.gms_message( x_err_name => 'GMS_FND_USER_NOT_CREATED',
1327 x_err_code => l_err_code,
1328 x_err_buff => l_err_stage);
1329
1330 l_err_code := 4; -- This error code will be used to show a warning.
1331 return;
1332 END IF;
1333
1334 --------------------------------------------------------------------------------
1335
1336 SELECT gms_workflow_itemkey_s.nextval
1337 INTO ItemKey
1338 from dual;
1339
1340
1341 EXCEPTION
1342
1343 WHEN FND_API.G_EXC_ERROR
1344 THEN
1345 ROLLBACK TO START_BUDGET_WF_NTFY_ONLY_pvt;
1346
1347 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1348 THEN
1349 p_err_code := SQLCODE;
1350 ROLLBACK TO START_BUDGET_WF_NTFY_ONLY_pvt;
1351
1352 WHEN OTHERS THEN
1353 p_err_code := SQLCODE;
1354 ROLLBACK TO START_BUDGET_WF_NTFY_ONLY_pvt;
1355
1356 END;
1357
1358 BEGIN
1359 -- ------------------------------------------------------------------------------------
1360 -- INSTANTIATE BUDGET WORKFLOW
1361 -- ------------------------------------------------------------------------------------
1362 -- NOTE:
1363 -- The process name passed here is the root process for the
1364 -- 'GMS Workflow Process'. The Selector procedure may override
1365 -- the process name specified here. However, the default
1366 -- GMS_WF procedure does not call the Selector procedure.
1367 -- ------------------------------------------------------------------------------------
1368
1369 IF L_DEBUG = 'Y' THEN
1370 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - Calling wf_engine.CreateProcess..', 'C');
1371 END IF;
1372
1373 wf_engine.CreateProcess( ItemType => ItemType,
1374 ItemKey => ItemKey,
1375 process => 'GMS_WF_PROCESS' );
1376
1377 IF L_DEBUG = 'Y' THEN
1378 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - Setting Item Attributes..', 'C');
1379 END IF;
1380
1381
1382 -- attribute GMS_WF_PROCESS is used to select the appropriate branch
1383 -- in the workflow process.
1384
1385 wf_engine.SetItemAttrText ( itemtype => itemtype,
1386 itemkey => itemkey,
1387 aname => 'GMS_WF_PROCESS',
1388 avalue => 'BUDGET_NTFY_ONLY' );
1389
1390 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1391 itemkey => itemkey,
1392 aname => 'AWARD_ID',
1393 avalue => p_award_id);
1394
1395 wf_engine.SetItemAttrText ( itemtype => itemtype,
1396 itemkey => itemkey,
1397 aname => 'AWARD_NUMBER',
1398 avalue => l_award_number);
1399
1400 wf_engine.SetItemAttrText ( itemtype => itemtype,
1401 itemkey => itemkey,
1402 aname => 'AWARD_SHORT_NAME',
1403 avalue => l_award_short_name);
1404
1405 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1406 itemkey => itemkey,
1407 aname => 'PROJECT_ID',
1408 avalue => p_project_id);
1409
1410 wf_engine.SetItemAttrText ( itemtype => itemtype,
1411 itemkey => itemkey,
1412 aname => 'PM_PROJECT_REFERENCE',
1413 avalue => l_pm_project_reference );
1414
1415 wf_engine.SetItemAttrText ( itemtype => itemtype,
1416 itemkey => itemkey,
1417 aname => 'PA_PROJECT_NUMBER',
1418 avalue => l_pa_project_number );
1419
1420 wf_engine.SetItemAttrText ( itemtype => itemtype,
1421 itemkey => itemkey,
1422 aname => 'PROJECT_NAME',
1423 avalue => l_project_name );
1424
1425 wf_engine.SetItemAttrText ( itemtype => itemtype,
1426 itemkey => itemkey,
1427 aname => 'PROJECT_DESCRIPTION',
1428 avalue => l_description );
1429
1430 wf_engine.SetItemAttrText ( itemtype => itemtype,
1431 itemkey => itemkey,
1432 aname => 'PROJECT_TYPE',
1433 avalue => l_project_type );
1434
1435 wf_engine.SetItemAttrText ( itemtype => itemtype,
1436 itemkey => itemkey,
1437 aname => 'PM_PROJECT_PRODUCT_CODE',
1438 avalue => l_pm_project_product_code );
1439
1440 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1441 itemkey => itemkey,
1442 aname => 'CARRYING_OUT_ORG_ID',
1443 avalue => l_carrying_out_org_id);
1444
1445 wf_engine.SetItemAttrText ( itemtype => itemtype,
1446 itemkey => itemkey,
1447 aname => 'CARRYING_OUT_ORG_NAME',
1448 avalue => l_carrying_out_org_name);
1449
1450 wf_engine.SetItemAttrText ( itemtype => itemtype,
1451 itemkey => itemkey,
1452 aname => 'PROJECT_TYPE_CLASS_CODE',
1453 avalue => l_project_type_class_code);
1454
1455 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1456 itemkey => itemkey,
1457 aname => 'WORKFLOW_STARTED_BY_ID',
1458 avalue => l_workflow_started_by_id);
1459
1460 wf_engine.SetItemAttrText ( itemtype => itemtype,
1461 itemkey => itemkey,
1462 aname => 'WORKFLOW_STARTED_BY_NAME',
1463 avalue => l_starter_name);
1464
1465 wf_engine.SetItemAttrText ( itemtype => itemtype,
1466 itemkey => itemkey,
1467 aname => 'WORKFLOW_STARTED_BY_FULL_NAME',
1468 avalue => l_starter_full_name);
1469
1470
1471 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1472 itemkey => itemkey,
1473 aname => 'RESPONSIBILITY_ID',
1474 avalue => l_resp_id);
1475
1476 wf_engine.SetItemAttrText ( itemtype => itemtype,
1477 itemkey => itemkey,
1478 aname => 'BUDGET_TYPE_CODE',
1479 avalue => p_budget_type_code);
1480
1481 wf_engine.SetItemAttrText ( itemtype => itemtype,
1482 itemkey => itemkey,
1483 aname => 'BUDGET_TYPE',
1484 avalue => l_budget_type);
1485
1486 wf_engine.SetItemAttrText ( itemtype => itemtype,
1487 itemkey => itemkey,
1488 aname => 'PM_BUDGET_REFERENCE',
1489 avalue => l_pm_budget_reference);
1490
1491 wf_engine.SetItemAttrText ( itemtype => itemtype,
1492 itemkey => itemkey,
1493 aname => 'BUDGET_DESCRIPTION',
1494 avalue => l_budget_description);
1495
1496 wf_engine.SetItemAttrText ( itemtype => itemtype,
1497 itemkey => itemkey,
1498 aname => 'CHANGE_REASON_CODE',
1499 avalue => l_budget_change_reason_code);
1500
1501 wf_engine.SetItemAttrText ( itemtype => itemtype,
1502 itemkey => itemkey,
1503 aname => 'BUDGET_ENTRY_METHOD',
1504 avalue => l_budget_entry_method_code);
1505
1506 wf_engine.SetItemAttrText ( itemtype => itemtype,
1507 itemkey => itemkey,
1508 aname => 'PM_BUDGET_PRODUCT_CODE',
1509 avalue => l_pm_budget_product_code);
1510
1511 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1512 itemkey => itemkey,
1513 aname => 'TOTAL_LABOR_HOURS',
1514 avalue => l_total_labor_hours);
1515
1516 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1517 itemkey => itemkey,
1518 aname => 'TOTAL_RAW_COST',
1519 avalue => l_total_raw_cost);
1520
1521 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1522 itemkey => itemkey,
1523 aname => 'TOTAL_BURDENED_COST',
1524 avalue => l_total_burdened_cost);
1525
1526 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1527 itemkey => itemkey,
1528 aname => 'RESOURCE_LIST_ID',
1529 avalue => l_resource_list_id);
1530
1531 wf_engine.SetItemAttrText ( itemtype => itemtype,
1532 itemkey => itemkey,
1533 aname => 'RESOURCE_LIST_NAME',
1534 avalue => l_resource_list_name);
1535
1536 wf_engine.SetItemAttrText ( itemtype => itemtype,
1537 itemkey => itemkey,
1538 aname => 'RESOURCE_LIST_DESCRIPTION',
1539 avalue => l_resource_list_description);
1540
1541 wf_engine.SetItemAttrText ( itemtype => itemtype,
1542 itemkey => itemkey,
1543 aname => 'MARK_AS_ORIGINAL',
1544 avalue => l_mark_as_original);
1545
1546
1547 wf_engine.SetItemAttrText ( itemtype => itemtype,
1548 itemkey => itemkey,
1549 aname => 'NOTIF_RECIPIENT_ROLE',
1550 avalue => l_role_name);
1551
1552 wf_engine.SetItemAttrText ( itemtype => itemtype,
1553 itemkey => itemkey,
1554 aname => 'FC_MODE',
1555 avalue => 'S');
1556
1557 wf_engine.SetItemAttrDate ( itemtype => itemtype,
1558 itemkey => itemkey,
1559 aname => 'WF_STARTED_DATE',
1560 avalue => l_wf_started_date
1561 );
1562
1563 --
1564 IF L_DEBUG = 'Y' THEN
1565 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - Calling wf_engine.StartProcess..', 'C');
1566 END IF;
1567
1568 wf_engine.StartProcess( itemtype => itemtype,
1569 itemkey => itemkey );
1570
1571 IF L_DEBUG = 'Y' THEN
1572 gms_error_pkg.gms_debug('GMS_CLIENT_EXTN_BUDGET_WF.START_BUDGET_WF_NTFY_ONLY - After wf_engine.StartProcess', 'C');
1573 END IF;
1574
1575 -- -----------------------------------------------------------------------------------
1576 -- CAUTION: These two OUT-Parameters must be populated
1577 -- properly in order for the calling procedures
1578 -- to work as designed.
1579 -- ------------------------------------------------------------------------------------
1580
1581 p_item_type := itemtype;
1582 p_item_key := itemkey;
1583
1584 -- -------------------------------------------------------------------------------------
1585
1586 --
1587 EXCEPTION
1588
1589 WHEN FND_API.G_EXC_ERROR
1590 THEN
1591 WF_CORE.CONTEXT(' GMS_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF_NTFY_ONLY', itemtype, itemkey);
1592 p_err_code := 4;
1593
1594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1595 THEN
1596 WF_CORE.CONTEXT(' GMS_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF_NTFY_ONLY', itemtype, itemkey);
1597 p_err_code := 4;
1598
1599 WHEN OTHERS
1600 THEN
1601 WF_CORE.CONTEXT(' GMS_CLIENT_EXTN_BUDGET_WF ','START_BUDGET_WF_NTFY_ONLY', itemtype, itemkey);
1602 p_err_code := 4;
1603
1604 END;
1605
1606 END START_BUDGET_WF_NTFY_ONLY;
1607
1608
1609 -----------------------------------------------------------------------------------------------------
1610
1611 --Name: Select_Budget_Approver
1612 --Type: Procedure
1613 --Description: This client extension returns the
1614 -- correct budget approver.
1615 --
1616 --
1617 --Called subprograms:
1618 --
1619 --
1620 --
1621 --History:
1622 --
1623 -- IN
1624 -- p_project_id - unique identifier for the project
1625 -- p_award_id - unique identifier for the award
1626 -- p_budget_type_code - needed to uniquely identify the working budget
1627 -- p_workflow_started_by_id - identifies the user that initiated the workflow
1628 --
1629 -- OUT NOCOPY
1630 -- p_budget_baseliner_id - unique identifier of the employee
1631 -- (award_manager_id in gms_awards table)
1632 -- that must approve this budget for baselining.
1633 --
1634
1635 PROCEDURE Select_Budget_Approver
1636 (p_item_type IN VARCHAR2
1637 , p_item_key IN VARCHAR2
1638 , p_project_id IN NUMBER
1639 , p_award_id IN NUMBER
1640 , p_budget_type_code IN VARCHAR2
1641 , p_workflow_started_by_id IN NUMBER
1642 , p_budget_baseliner_id OUT NOCOPY NUMBER
1643 )
1644 --
1645 IS
1646
1647 --
1648 -- Define Your Local Variables Here
1649 --
1650 l_employee_id NUMBER;
1651 --
1652 /*
1653 You can use this procedure to add any additional rules to determine
1654 who can approve a project. This procedure is being used by the
1655 Workflow APIs and determine who the approver for a project
1656 should be. By default this procedure fetches the supervisor of the
1657 person who initiated the workflow as the approver.
1658 */
1659
1660
1661 BEGIN
1662
1663 -- Specify Your Business Rules Here
1664
1665 /*
1666 SELECT employee_id
1667 INTO l_employee_id
1668 FROM fnd_user
1669 WHERE user_id = p_workflow_started_by_id;
1670 */
1671
1672 -- Selecting the active Award Manager for this Award as the Baseliner.
1673
1674 SELECT person_id
1675 INTO p_budget_baseliner_id
1676 FROM gms_personnel
1677 WHERE award_id = p_award_id
1678 AND award_role = 'AM' -- (AM => Award Manager)
1679 AND sysdate BETWEEN START_DATE_ACTIVE
1680 AND NVL(END_DATE_ACTIVE, sysdate);
1681
1682
1683 --
1684 --The following algorithm can be used to handle known error conditions
1685 --When this code is used the arguments and there values will be displayed
1686 --in the error message that is send by workflow.
1687 --
1688 --IF <error condition>
1689 --THEN
1690 -- WF_CORE.TOKEN('ARG1', arg1);
1691 -- WF_CORE.TOKEN('ARGn', argn);
1692 -- WF_CORE.RAISE('ERROR_NAME');
1693 --END IF;
1694
1695 EXCEPTION
1696 WHEN NO_DATA_FOUND THEN
1697 p_budget_baseliner_id := NULL;
1698
1699 WHEN OTHERS THEN
1700 WF_CORE.CONTEXT('GMS_CLIENT_EXTN_BUDGET_WF','SELECT_BUDGET_APPROVER',
1701 p_item_type, p_item_key);
1702 RAISE;
1703
1704 END Select_Budget_Approver;
1705
1706 -- ==================================================
1707 --Name: Verify_Budget_Rules
1708 --Type: Procedure
1709 --Description: This procedure is for verification rules that may
1710 -- vary by workflow.
1711 --
1712 --
1713 --Called subprograms: none.
1714 --
1715 --
1716 --
1717 --History:
1718 --
1719 -- IN
1720 -- p_item_type - WF item type
1721 -- p_item_key - WF item key
1722 -- p_project_id - unique identifier for the project that needs baselining
1723 -- p_award_id - unique identifier for the award that needs baselining
1724 -- p_budget_type_code - needed to uniquely identify this working budget
1725 -- p_workflow_started_by_id - identifies the user that initiated the workflow
1726 -- p_event - indicates whether procedure called for
1727 -- either a 'SUBMIT' or 'BASELINE'
1728 -- event.
1729 --
1730 -- OUT NOCOPY
1731 -- p_warnings_only_flag - RETURN 'Y' if ALL triggered edits are warnings. Otherwise,
1732 -- if there is at least one hard error, then RETURN 'N'.
1733 -- p_err_msg_count - Count of warning and error messages.
1734 --
1735 -- NOTES
1736 -- By using the commented code in the body of this procedure, you may
1737 -- add error and warning messages to the message stack.
1738 -- However, the workflow notification will only display
1739 -- ten messages.
1740 --
1741 -- Moreover, error/warning processing in the calling procedure
1742 -- will only occur if OUT NOCOPY p_err_msg_count
1743 -- parameter is greater than zero.
1744 --
1745
1746 PROCEDURE Verify_Budget_Rules
1747 (p_item_type IN VARCHAR2
1748 , p_item_key IN VARCHAR2
1749 , p_project_id IN NUMBER
1750 , p_award_id IN NUMBER
1751 , p_budget_type_code IN VARCHAR2
1752 , p_workflow_started_by_id IN NUMBER
1753 , p_event IN VARCHAR2
1754 , p_warnings_only_flag OUT NOCOPY VARCHAR2
1755 , p_err_msg_count OUT NOCOPY NUMBER
1756 )
1757 --
1758 IS
1759 --
1760 -- Declare Variables here
1761
1762
1763 BEGIN
1764
1765 --
1766 -- Initialize OUT-parameters Here.
1767 -- All 'p_' parameters are required.
1768 --
1769 p_warnings_only_flag := 'Y';
1770 p_err_msg_count := 0;
1771
1772 --
1773 -- Put The Rules That You Want To Check For Here
1774 --
1775
1776 --
1777 -- NOTIFICATION Error/Warning Handling --------------------------
1778 --
1779 -- Note: You must call PA_UTILS.Add_Message at least once
1780 -- for the higher-level workflow processing to be invoked.
1781 --
1782 -- For error and warning messages, you must increment the p_err_msg_count
1783 -- OUT-parameter before passing control to the calling procedure:
1784 --
1785 -- p_err_msg_count := FND_MSG_PUB.Count_Msg;
1786 --
1787 --
1788 -- For a hard error, one that you want to force the calling procedure
1789 -- to invoke a 'False' or 'Failure' transition:
1790 --
1791 -- p_warnings_only_flag := 'N';
1792 --
1793 --
1794 -- To display an error or warning message in the workflow notification, you
1795 -- must call the following:
1796 --
1797 -- PA_UTILS.Add_Message
1798 --
1799 -- For example, a typical call might look like the following:
1800 --
1801 -- PA_UTILS.Add_Message
1802 -- ( p_app_short_name => 'PA'
1803 -- , p_msg_name => 'PA_NO_BUDGET_RULES_ATTR'
1804 -- );
1805 -- ---------------------------------------------------------------------------------------
1806
1807 --
1808 -- WF_CORE Error Handling --------------------------------------------------
1809 -- To display errors using the WF_CORE functionality,
1810 -- the following algorithm can be used to handle known error conditions.
1811 -- When this code is used the arguments and there values will be displayed
1812 -- in the workflow monitor.
1813 --
1814 --IF <error condition>
1815 --THEN
1816 -- WF_CORE.TOKEN('ARG1', arg1);
1817 -- WF_CORE.TOKEN('ARGn', argn);
1818 -- WF_CORE.RAISE('ERROR_NAME');
1819 --END IF;
1820 -- ---------------------------------------------------------------------------------------
1821
1822 --
1823 -- Make sure to update the OUT NOCOPY variable for the
1824 -- message count
1825 --
1826 p_err_msg_count := FND_MSG_PUB.Count_Msg;
1827
1828
1829 EXCEPTION
1830
1831 WHEN OTHERS THEN
1832 WF_CORE.CONTEXT('GMS_CLIENT_EXTN_BUDGET_WF','VERIFY_BUDGET_RULES', p_item_type, p_item_key);
1833 RAISE;
1834
1835
1836 END Verify_Budget_Rules;
1837
1838 -----------------------------------------------------------------------------------------------
1839
1840 -- =================================================
1841 --Bug fix 2204122 added the following two procedures--
1842 --1. call_gms_debug 2.call_wf_addusers_to_adhocrole--
1843 -------------------------------------------------------------------------
1844 --In Procedure call_gms_debug stream of 255 chars--
1845 --would be passed to procedure gms_error_pkg.gms_debug--
1846 --in a loop till it prints all the USERIDS--
1847
1848 PROCEDURE call_gms_debug
1849 (p_user_roles IN VARCHAR2
1850 ,p_disp_text IN VARCHAR2)
1851
1852 IS
1853
1854 l_user_roles VARCHAR2(32000);
1855 l_user_roles_temp VARCHAR2(150) ;
1856 l_total_char NUMBER ;
1857 l_start_pos NUMBER := 0;
1858 l_char_send NUMBER := 0 ;
1859 l_tot_char_send NUMBER := 0 ;
1860
1861 BEGIN
1862 l_user_roles := p_user_roles ;
1863 l_total_char := LENGTH(l_user_roles) ;
1864 IF l_total_char > 150 THEN
1865 LOOP
1866 l_start_pos := l_start_pos + l_char_send + 1;
1867 l_user_roles_temp := SUBSTR(l_user_roles, l_start_pos , 150 ) ;
1868 l_char_send := LENGTH(l_user_roles_temp) ;
1869 l_tot_char_send := l_tot_char_send + l_char_send ;
1870
1871 IF (l_total_char - l_start_pos >= 150) THEN
1872
1873 l_user_roles_temp := SUBSTR(l_user_roles_temp , 1 , INSTR(l_user_roles_temp,',',-1,1)-1) ;
1874
1875 l_char_send := LENGTH(l_user_roles_temp) ;
1876 l_tot_char_send:= l_tot_char_send + (l_char_send-150 ) ;
1877 IF L_DEBUG = 'Y' THEN
1878 gms_error_pkg.gms_debug(p_disp_text||l_user_roles_temp, 'C');
1879 END IF;
1880
1881 ELSE
1882
1883 IF L_DEBUG = 'Y' THEN
1884 gms_error_pkg.gms_debug(p_disp_text||l_user_roles_temp, 'C');
1885 END IF;
1886 EXIT; -- added for bug 12327457
1887 END IF ;
1888
1889 END LOOP ;
1890
1891 ELSE
1892
1893 IF L_DEBUG = 'Y' THEN
1894 gms_error_pkg.gms_debug(p_disp_text||l_user_roles, 'C');
1895 END IF;
1896
1897 END IF ;
1898
1899 END call_gms_debug ;
1900
1901
1902 --Bug 2204122 Stream of maximum 2000 chars each would be passed--
1903 --to procedure wf_directory.AddUsersToAdhocRole--
1904 --through Procedure call_wf_addusers_to_adhocrole , within a loop to pass all the USERID's--
1905
1906
1907 PROCEDURE call_wf_addusers_to_adhocrole
1908 (p_user_roles IN VARCHAR2
1909 ,p_role_name IN VARCHAR2)
1910
1911 IS
1912 l_disp_text VARCHAR2(100) ;
1913 l_role_name VARCHAR2(100) ;
1914 l_user_roles VARCHAR2(32000);
1915 l_user_roles_temp VARCHAR2(2000) ;
1916 l_total_char NUMBER ;
1917 l_start_pos NUMBER := 0 ;
1918 l_char_send NUMBER := 0 ;
1919 l_tot_char_send NUMBER := 0 ;
1920
1921 BEGIN
1922 l_user_roles := p_user_roles ;
1923 l_role_name := p_role_name ;
1924 l_total_char := LENGTH(l_user_roles) ;
1925
1926 IF l_total_char > 2000 THEN
1927 LOOP
1928 l_start_pos := l_start_pos + l_char_send + 1;
1929 l_user_roles_temp := SUBSTR(l_user_roles, l_start_pos , 2000 ) ;
1930 l_char_send := LENGTH(l_user_roles_temp) ;
1931 l_tot_char_send := l_tot_char_send + l_char_send ;
1932
1933 IF (l_total_char - l_start_pos >= 2000) THEN
1934
1935 l_user_roles_temp := SUBSTR(l_user_roles_temp , 1 , INSTR(l_user_roles_temp,',',-1,1)-1) ;
1936
1937 l_char_send := LENGTH(l_user_roles_temp) ;
1938 l_tot_char_send:= l_tot_char_send + (l_char_send-2000 ) ;
1939 wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
1940 role_users => l_user_roles_temp);
1941 ELSE
1942 wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
1943 role_users => l_user_roles_temp);
1944 END IF ;
1945 END LOOP ;
1946 ELSE
1947 wf_directory.AddUsersToAdhocRole(role_name => l_role_name,
1948 role_users => l_user_roles);
1949 END IF ;
1950
1951 END call_wf_addusers_to_adhocrole;
1952 END gms_client_extn_budget_wf;