[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_APPROVALS_PVT
Source
1 PACKAGE BODY IGW_PROP_APPROVALS_PVT as
2 /* $Header: igwvpapb.pls 115.10 2002/11/14 18:51:47 vmedikon ship $*/
3
4
5
6 Procedure start_approval_process (
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
10 p_proposal_id IN NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2) is
14
15
16 l_return_status VARCHAR2(1);
17 l_error_msg_code VARCHAR2(250);
18 l_msg_count NUMBER;
19 l_data VARCHAR2(250);
20 l_msg_data VARCHAR2(250);
21 l_msg_index_out NUMBER;
22
23 l_short_name VARCHAR2(30);
24 l_message_name VARCHAR2(200);
25
26 BEGIN
27 -- create savepoint if p_commit is true
28 IF p_commit = FND_API.G_TRUE THEN
29 SAVEPOINT start_approval;
30 END IF;
31
32 -- initialize message list if p_init_msg_list is true
33 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
34 fnd_msg_pub.initialize;
35 end if;
36
37 -- initialize return_status to success
38 x_return_status := fnd_api.g_ret_sts_success;
39
40 /*
41 -- first validate user rights
42
43 VALIDATE_LOGGED_USER_RIGHTS
44 (p_proposal_id => p_proposal_id
45 ,x_return_status => x_return_status);
46
47 check_errors;
48 */
49
50 ------------------------------------- value_id conversion ---------------------------------
51
52 -------------------------------------------- validations -----------------------------------------------------
53 -- dbms_output.put_line('before call to start approval');
54 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
55 start_approval (
56 p_proposal_id => p_proposal_id
57 ,p_error_message => l_msg_data
58 ,p_return_status => l_return_status);
59
60 end if;
61
62 -- dbms_output.put_line(l_msg_data);
63 -- dbms_output.put_line(l_message_name);
64
65 if (l_return_status = 'E') then
66 fnd_msg_pub.initialize;
67 fnd_message.parse_encoded(encoded_message => l_msg_data,
68 app_short_name => l_short_name,
69 message_name => l_message_name);
70 -- dbms_output.put_line(l_msg_data);
71 -- dbms_output.put_line(l_message_name);
72
73 fnd_message.set_name(l_short_name, l_message_name);
74 fnd_msg_pub.add;
75 raise fnd_api.g_exc_error;
76 elsif (l_return_status = 'U') then
77 fnd_msg_pub.initialize;
78 fnd_message.parse_encoded(encoded_message => l_msg_data,
79 app_short_name => l_short_name,
80 message_name => l_message_name);
81 -- dbms_output.put_line(l_msg_data);
82 -- dbms_output.put_line(l_message_name);
83
84 fnd_message.set_name('l_short_name', l_message_name);
85 fnd_msg_pub.add;
86 raise fnd_api.g_exc_unexpected_error;
87 elsif (l_return_status = 'S') then
88 update igw_proposals_all set proposal_status = 'I' where proposal_id = p_proposal_id;
89 end if;
90
91
92 -- standard check of p_commit
93 if fnd_api.to_boolean(p_commit) then
94 commit work;
95 end if;
96
97 -- set x_msg_count and x_msg_data
98 x_msg_count := 0;
99 x_msg_data := 'Proposal Submitted for Approval';
100
101
102
103
104 EXCEPTION
105 WHEN FND_API.G_EXC_ERROR THEN
106 IF p_commit = FND_API.G_TRUE THEN
107 ROLLBACK TO start_approval;
108 END IF;
109
110 x_return_status := FND_API.G_RET_STS_ERROR;
111
112 x_msg_count := 1;
113 x_msg_data := l_message_name;
114
115
116
117
118
119 WHEN OTHERS THEN
120 IF p_commit = FND_API.G_TRUE THEN
121 ROLLBACK TO start_approval;
122 END IF;
123
124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
125
126 x_msg_count := 1;
127 x_msg_data := l_message_name;
128
129 /* fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_APPROVALS_PVT',
130 p_procedure_name => 'START_APPROVAL',
131 p_error_text => SUBSTRB(SQLERRM,1,240));
132
133 fnd_msg_pub.count_and_get(p_count => x_msg_count,
134 p_data => x_msg_data);
135 */
136
137 END start_approval_process;
138
139 --------------------------------------------------------------------------------------------------------
140
141 PROCEDURE VALIDATE_LOGGED_USER_RIGHTS
142 (p_proposal_id IN NUMBER
143 ,p_logged_user_id IN NUMBER
144 ,x_return_status OUT NOCOPY VARCHAR2) is
145
146 x VARCHAR2(1);
147 y VARCHAR2(1);
148
149 BEGIN
150 x_return_status:= FND_API.G_RET_STS_SUCCESS;
151
152 select x into y
153 from igw_prop_user_roles ppr,
154 igw_prop_users ppu
155 where ppr.proposal_id = p_proposal_id AND
156 ppr.proposal_id = ppu.proposal_id AND
157 ppr.user_id = ppu.user_id AND
158 ppr.role_id in (0,2) AND
159 ppr.user_id = p_logged_user_id AND
160 sysdate >= ppu.start_date_active AND
161 sysdate <= nvl(ppu.end_date_active, sysdate);
162
163 EXCEPTION
164
165 WHEN NO_DATA_FOUND THEN
166 x_return_status:= FND_API.G_RET_STS_ERROR;
167 fnd_message.set_name('IGW', 'IGW_NO_RIGHTS');
168 fnd_msg_pub.add;
169
170 WHEN too_many_rows THEN
171 NULL;
172
173 WHEN OTHERS THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_APPROVALS_PVT',
176 p_procedure_name => 'VALIDATE_LOGGED_USER_RIGHTS',
177 p_error_text => SUBSTRB(SQLERRM,1,240));
178 raise fnd_api.g_exc_unexpected_error;
179 END VALIDATE_LOGGED_USER_RIGHTS;
180
181 --------------------------------------------------------------------------------------------------------------
182 procedure start_approval(p_proposal_id in number,
183 p_error_message out NOCOPY varchar2,
184 p_return_status out NOCOPY varchar2) is
185
186
187 l_proposal_status varchar2(1);
188 l_budget_complete varchar2(1);
189 l_budget_not_applicable varchar2(1);
190 l_narrative_complete varchar2(1);
191 l_narrative_not_applicable varchar2(1);
192 l_signing_official_id number;
193 l_admin_official_id number;
194
195
196 l_run_id number;
197 l_invalid_flag varchar2(1);
198 l_rules_found varchar2(1);
199 l_error_message varchar2(2000);
200 l_return_status varchar2(1);
201 l_msg_count number;
202 l_count number;
203
204
205 begin
206 -- Assign proposal_id to global variable
207 g_proposal_id := p_proposal_id;
208
209 /*
210 -- do not rout the proposal if not submitted by Proposal Owner
211 -- or the Proposal Manager
212
213 select count(*)
214 into l_count
215 from igw_prop_user_roles ppr,
216 igw_prop_users ppu
217 where ppr.proposal_id = p_proposal_id
218 and ppr.proposal_id = ppu.proposal_id
219 and ppr.user_id = ppu.user_id
220 and (ppr.role_id = 0 or ppr.role_id = 2)
221 and ppu.user_id = fnd_global.user_id
222 and sysdate >= ppu.start_date_active
223 and sysdate <= nvl(ppu.end_date_active,sysdate);
224
225
226 if l_count = 0 then
227
228 fnd_message.set_name('IGW','IGW_ROUT_PR_OWNER_CAN_SUBMIT');
229 p_error_message := fnd_message.get_encoded;
230 p_return_status := 'E';
231 return;
232
233 end if;
234 */
235 -- do not rout the proposal if the proposal_status = 'I' or 'A'
236
237 select ipa.proposal_status,
238 ipa.signing_official_id,
239 ipa.admin_official_id
240 into l_proposal_status,
241 l_signing_official_id,
242 l_admin_official_id
243 from igw_proposals_all ipa
244 where ipa.proposal_id = g_proposal_id;
245
246 -- do not rout the proposal if budget is incomplete
247 select complete,
248 not_applicable
249 into l_budget_complete,
250 l_budget_not_applicable
251 from igw_prop_checklist
252 where document_type_code = 'BUDGETS' and
253 proposal_id = g_proposal_id;
254
255 if l_proposal_status = 'I' then
256
257 fnd_message.set_name('IGW','IGW_ROUT_IPR_CANNOT_SUBMIT');
258 p_error_message := fnd_message.get_encoded;
259 p_return_status := 'E';
260 return;
261
262 elsif l_proposal_status = 'A' then
263
264 fnd_message.set_name('IGW','IGW_ROUT_APR_CANNOT_SUBMIT');
265 p_error_message := fnd_message.get_encoded;
266 p_return_status := 'E';
267 return;
268
269
270 elsif ((l_budget_complete = 'N') AND (l_budget_not_applicable = 'N')) then
271
272 fnd_message.set_name('IGW','IGW_ROUT_BUDGET_INCOMPLETE');
273 p_error_message := fnd_message.get_encoded;
274 p_return_status := 'E';
275 return;
276
277 end if;
278
279 -- get the validation business rules
280
281 -- dbms_output.put_line('Calling Validation ...');
282 get_business_rules('V',l_run_id,l_invalid_flag,l_rules_found,l_error_message);
283
284 if l_invalid_flag = 'Y' then
285 p_error_message := l_error_message;
286 p_return_status := 'E';
287 return;
288 end if;
289
290 --dbms_output.put_line('################### p_return_status ' || p_return_status);
291
292 -- get the routing business rules
293
294 -- dbms_output.put_line('Calling routing ....');
295 get_business_rules('R',l_run_id,l_invalid_flag,l_rules_found,l_error_message);
296
297 -- dbms_output.put_line('# of rules found='||l_rules_found);
298
299 if l_rules_found = 'T' then
300
301 -- get the notification business rules
302 l_invalid_flag := null;
303 l_rules_found := null;
304 l_error_message := null;
305
306 -- dbms_output.put_line('Calling notification .........');
307
308 get_business_rules('N',l_run_id,l_invalid_flag,l_rules_found,l_error_message);
309
310 -- dbms_output.put_line('After Calling notification .........');
311
312 -- insert into wf_local_roles and wf_local_user_roles table
313 populate_local_wf_tables(l_run_id);
314
315 -- dbms_output.put_line('After populate local tables.');
316
317 -- Assign Proposal Signing Official role(role_id=3) to Proposal Signing Official and Administrative Official
318 assign_so_role(l_signing_official_id,l_admin_official_id);
319 -- dbms_output.put_line('After assigning signing official');
320
321 -- commit before invoking the workflow
322 ----commit;
323 -- call workflow
324 -- dbms_output.put_line('Calling workflow .........');
325 igw_workflow.start_workflow(p_proposal_id,l_run_id);
326
327 else
328
329 fnd_message.set_name('IGW','IGW_ROUT_NO_STOPS_FOUND');
330
331 p_error_message := fnd_message.get_encoded;
332 p_return_status := 'E';
333 return;
334 end if;
335
336 p_return_status := 'S';
337
338 -- update the status to I (Approval In-Progress) in igw_prop_approval_runs
339 -- and igw_proposals_all
340
341 /* update igw_prop_approval_runs
342 set status_code = 'I',
343 status_date = sysdate
344 where run_id = l_run_id;
345
346 commit; */
347
348 exception
349 when others then
350
351 -- dbms_output.put_line('Inside when other exception');
352 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','START_APPROVAL');
353
354 p_error_message := fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
355 p_encoded => FND_API.G_TRUE);
356
357 p_return_status := 'U';
358 -- dbms_output.put_line('p_error_message --> ' || p_error_message);
359 -- dbms_output.put_line('p_return_status --> ' || p_return_status );
360 -- raise;
361 end start_approval;
362 --------------------------------------------------------------------------------------------------------------
363 procedure get_business_rules(p_rule_type in varchar2,
364 p_run_id in out NOCOPY number,
365 p_invalid_flag out NOCOPY varchar2,
366 p_rules_found out NOCOPY varchar2,
367 p_error_message out NOCOPY varchar2) is
368
369
370 l_org_id number(15);
371 l_rule_name varchar2(50);
372 l_rule_id number(15);
373 l_map_id number(15);
374 l_valid_flag varchar2(1);
375 l_execute_result varchar2(5);
376 l_org_name hr_all_organization_units.NAME%TYPE;
377 l_map_seq_number number := 0;
378 l_wf_role_name varchar2(100);
379 l_wf_display_role_name varchar2(240);
380
381
382 l_description varchar2(200);
383 l_level_id number(4);
384 l_user_name varchar2(100);
385 l_run_number number(4);
386 l_dummy varchar2(1);
387 l_user_id number(15);
388
389 cursor get_business_rules is
390 select hou.name,
391 pbr.rule_name,
392 pbr.rule_id,
393 pbr.map_id,
394 map.description,
395 pbr.valid_flag
396 from hr_organization_units hou,
397 igw_org_maps_all map,
398 igw_business_rules_all pbr
399 where pbr.organization_id = l_org_id
400 and pbr.map_id = map.map_id(+)
401 and pbr.organization_id = hou.organization_id
402 and nvl(pbr.end_date_active,sysdate) >= sysdate
403 and pbr.rule_type = p_rule_type
404 order by pbr.rule_sequence_number;
405
406
407 cursor get_map_details is
408 select pom.stop_id,
409 pom.user_name,
410 fus.user_id,
411 pom.approver_type
412 from fnd_user fus,
413 igw_org_map_details pom
414 where pom.user_name = fus.user_name
415 and pom.map_id = l_map_id;
416
417 cursor next_run_number is
418 select nvl(max(run_number),0) + 1
419 from igw_prop_approval_runs
420 where proposal_id = g_proposal_id;
421
422
423 cursor user_exists is
424 select 'x'
425 from igw_prop_users
426 where proposal_id = g_proposal_id
427 and user_id = l_user_id;
428
429
430 cursor role_exists(l_role_id in number) is
431 select 'x'
432 from igw_prop_user_roles
433 where proposal_id = g_proposal_id
434 and user_id = l_user_id
435 and role_id = l_role_id;
436
437 begin
438
439 -- get the starting organization_id
440 select lead_organization_id
441 into l_org_id
442 from igw_proposals_all
443 where proposal_id = g_proposal_id;
444 --dbms_output.put_line('Lead Org = '||to_char(l_org_id));
445
446
447 loop
448 open get_business_rules;
449 loop
450 fetch get_business_rules into l_org_name,l_rule_name,l_rule_id,l_map_id,
451 l_description,l_valid_flag;
452
453 if get_business_rules%notfound then
454
455 --dbms_output.put_line('No business rules found .....');
456
457
458 close get_business_rules;
459 exit;
460 end if;
461
462 --dbms_output.put_line('Org Id '||l_org_name||' Rule Id '||to_char(l_rule_id));
463 l_execute_result := execute_business_rule(l_rule_id);
464 --dbms_output.put_line('The output of execute_result='||l_execute_result);
465
466
467 -- If rule_type = 'V' and the function returns 'T', then continue
468 -- If rule_type = 'V' and the function returns 'F', then display error
469 -- If rule_type = 'N' or 'R' and the function returns 'F', then continue
470 -- If rule_type = 'N' or 'R' and the function returns 'T', then
471 -- insert row in igw_prop_maps and igw_prop_map_stops table
472
473 if (p_rule_type = 'V' and l_execute_result = '1=2' and l_valid_flag = 'V') or
474 (p_rule_type = 'V' and l_execute_result = '1=1' and l_valid_flag = 'I') then
475
476
477 fnd_message.set_name('IGW','IGW_ROUT_VALIDATION_FAILED');
478 fnd_message.set_token('RULE_NAME',l_rule_name);
479 fnd_message.set_token('ORGANIZATION_NAME',l_org_name);
480 p_error_message := fnd_message.get_encoded;
481
482 close get_business_rules;
483 p_invalid_flag := 'Y';
484 exit;
485
486
487 elsif (p_rule_type = 'N' or p_rule_type = 'R') and l_execute_result = '1=1' then
488
489 if p_rule_type = 'N' then
490 l_map_seq_number := 1;
491 elsif p_rule_type = 'R' then
492 l_map_seq_number := l_map_seq_number + 1;
493 p_rules_found := 'T';
494 if l_map_seq_number = 1 then
495 select igw_prop_approval_runs_s.nextval
496 into p_run_id
497 from dual;
498
499 open next_run_number;
500 fetch next_run_number into l_run_number;
501 close next_run_number;
502
503 -- insert into igw_prop_approval_runs
504 insert into igw_prop_approval_runs(run_id,proposal_id,
505 run_number,status_code,status_date) values (
506 p_run_id,g_proposal_id,l_run_number,'I',sysdate);
507 end if;
508 end if;
509
510 insert into igw_prop_maps(prop_map_id,proposal_id,description,
511 map_type,map_sequence_number,run_id,approval_status,
512 last_update_date,last_updated_by,creation_date,created_by,
513 last_update_login)
514 values(
515 igw_prop_maps_s.nextval,g_proposal_id,l_description,
516 p_rule_type,l_map_seq_number,p_run_id,null,
517 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
518 fnd_global.login_id);
519
520 --dbms_output.put_line('!!!!!!!!!!!!!!!!Inserted into igw_prop_maps .....'||to_char(l_map_id));
521 for c1 in get_map_details loop
522
523 l_user_id := c1.user_id;
524
525 if p_rule_type = 'N' then
526
527 l_wf_role_name := 'IGW'||'-'||to_char(p_run_id)||'-0-0';
528 fnd_message.set_name('IGW','IGW_ROUT_NOTIFICATION_ROLE');
529 fnd_message.set_token('RUN_ID', p_run_id);
530 l_wf_display_role_name := fnd_message.get;
531
532
533 elsif p_rule_type = 'R' then
534
535 l_wf_role_name := 'IGW'||'-'||to_char(p_run_id)||'-'||
536 to_char(l_map_id)||'-'||to_char(c1.stop_id);
537
538 fnd_message.set_name('IGW','IGW_ROUT_ROUTING_ROLE');
539 fnd_message.set_token('MAP_NAME', l_description);
540 fnd_message.set_token('STOP_ID',c1.stop_id);
541 fnd_message.set_token('RUN_ID', p_run_id);
542 l_wf_display_role_name := fnd_message.get;
543
544 end if;
545
546 insert into igw_prop_map_stops(prop_map_id,stop_id,
547 user_name,wf_role_name,wf_display_role_name,approver_type,
548 approval_status,submission_date,approval_date,comments,
549 last_update_date,last_updated_by,creation_date,created_by,
550 last_update_login)
551 values(
552 igw_prop_maps_s.currval,c1.stop_id,c1.user_name,l_wf_role_name,
553 l_wf_display_role_name,c1.approver_type,null,null,null,null,
554 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
555 fnd_global.login_id);
556
557 --dbms_output.put_line('################Inserted into igw_prop_map_stops .....'||to_char(l_map_id));
558
559
560 -- Assign the user to the proposal if it is not already assigned
561 open user_exists;
562 fetch user_exists into l_dummy;
563 if user_exists%notfound then
564
565 insert into igw_prop_users(proposal_id,user_id,start_date_active,
566 end_date_active,
567 last_update_date,last_updated_by,creation_date, created_by,
568 last_update_login)
569 values(
570 g_proposal_id,l_user_id,sysdate,null,
571 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
572 fnd_global.login_id);
573
574 end if;
575 close user_exists;
576
577 -- Assign Proposal Approver role(role_id=1) to proposal approver and Proposal Viewer role(role_id=4) to the notified user if they don't have the roles
578
579 if p_rule_type = 'N' then
580 open role_exists(4);
581 fetch role_exists into l_dummy;
582 if role_exists%notfound then
583 insert into igw_prop_user_roles(proposal_id,user_id,role_id,
584 last_update_date,last_updated_by,creation_date, created_by,
585 last_update_login)
586 values(
587 g_proposal_id,l_user_id,4,
588 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
589 fnd_global.login_id);
590 end if;
591 close role_exists;
592
593 elsif p_rule_type = 'R' then
594 open role_exists(1);
595 fetch role_exists into l_dummy;
596 if role_exists%notfound then
597 insert into igw_prop_user_roles(proposal_id,user_id,role_id,
598 last_update_date,last_updated_by,creation_date, created_by,
599 last_update_login)
600 values(
601 g_proposal_id,l_user_id,1,
602 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
603 fnd_global.login_id);
604 end if;
605 close role_exists;
606 end if;
607 end loop;
608
609 end if;
610
611
612 end loop;
613 if p_invalid_flag = 'Y' then
614 exit;
615 end if;
616
617 l_org_id := get_parent_org_id(l_org_id);
618 if l_org_id is null then
619 exit;
620 end if;
621
622 end loop;
623
624 exception
625 when others then
626 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','GET_BUSINESS_RULES');
627 raise;
628 end get_business_rules;
629
630
631
632 ----------- function execute_business_rules -----------------------------
633
634 function execute_business_rule(p_rule_id in number)
635 return varchar2 is
636
637
638 l_execute_result varchar2(5);
639 l_select_stmt varchar2(2000);
640 l_loop_count number(4) := 0;
641
642
643 cursor business_rule_lines is
644 select expression_type,
645 lbrackets,
646 lvalue,
647 operator,
648 rvalue,
649 rvalue_id,
650 rbrackets,
651 logical_operator
652 from igw_business_rule_lines
653 where rule_id = p_rule_id
654 order by expression_sequence_number;
655
656 begin
657
658 l_select_stmt := 'select 1 from dual where ';
659
660
661 for c1 in business_rule_lines loop
662
663 l_loop_count := l_loop_count + 1;
664
665
666 --dbms_output.put_line('Calling execute_line....'||c1.lvalue);
667
668
669 l_execute_result := execute_line(c1.expression_type,c1.lvalue,c1.operator,c1.rvalue_id);
670 l_select_stmt := l_select_stmt||c1.lbrackets||l_execute_result||
671 c1.rbrackets||' '||c1.logical_operator||' ';
672
673 --dbms_output.put_line('** Business Rule SQL statement='||l_select_stmt);
674
675 end loop;
676
677 if l_loop_count = 0 then
678 return '1=1';
679 elsif l_loop_count >= 1 then
680 --l_select_stmt := l_select_stmt||';';
681 return execute_dynamic_sql(l_select_stmt);
682 end if;
683
684 exception
685 when others then
686 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','EXECUTE_BUSINESS_RULE');
687 raise;
688
689 end execute_business_rule;
690
691
692 ----------- function execute_line ------------------------------------
693
694 function execute_line(p_expression_type in varchar2,
695 p_lvalue in varchar2,
696 p_operator in varchar2,
697 p_rvalue_id in varchar2)
698 return varchar2 as
699
700
701 cursor get_answer is
702 select answer
703 from igw_prop_questions
704 where question_number = p_lvalue
705 and proposal_id = g_proposal_id;
706
707
708 cursor get_budget_amounts is
709 select total_cost,
710 total_direct_cost,
711 total_indirect_cost,
712 cost_sharing_amount,
713 underrecovery_amount
714 from igw_budgets
715 where proposal_id = g_proposal_id
716 and final_version_flag = 'Y';
717
718
719 cursor get_deadline_date is
720 select to_char(deadline_date,'YYYYMMDD')
721 from igw_proposals_all
722 where proposal_id = g_proposal_id;
723
724
725 cursor get_expenditure_cat_type(exp_cat_flag in varchar2) is
726 select 'x'
727 from igw_budget_details pbd,
728 igw_budget_periods pbp,
729 igw_budgets pbu
730 where pbu.proposal_id = g_proposal_id
731 and pbu.final_version_flag = 'Y'
732 and pbu.proposal_id = pbp.proposal_id
733 and pbu.version_id = pbp.version_id
734 and pbp.proposal_id = pbd.proposal_id
735 and pbp.version_id = pbd.version_id
736 and pbd.expenditure_type = p_rvalue_id
737 and pbd.expenditure_category_flag = exp_cat_flag;
738
739
740 cursor get_lead_org is
741 select 'x'
742 from igw_proposals_all
743 where proposal_id = g_proposal_id
744 and lead_organization_id = p_rvalue_id;
745
746
747 cursor get_overhead_rate_deviation is
748 select 'x'
749 from igw_prop_rates ppr,
750 igw_budgets pbu
751 where pbu.proposal_id = g_proposal_id
752 and pbu.final_version_flag = 'Y'
753 and pbu.proposal_id = ppr.proposal_id
754 and pbu.version_id = ppr.version_id
755 and ppr.applicable_rate <> ppr.institute_rate;
756
757
758 cursor get_pi is
759 select 'x'
760 from igw_prop_persons
761 where proposal_id = g_proposal_id
762 and pi_flag = 'Y'
763 and person_id = p_rvalue_id;
764
765
766 cursor get_special_review_type is
767 select 'x'
768 from igw_prop_special_reviews
769 where proposal_id = g_proposal_id
770 and special_review_code = p_rvalue_id;
771
772
773
774 l_select_stmt varchar2(2000) := 'select 1 from dual where ';
775 l_answer varchar2(1);
776 l_total_cost number(15,2);
777 l_total_direct_cost number(15,2);
778 l_total_indirect_cost number(15,2);
779 l_cost_sharing_amount number(15,2);
780 l_underrecovery_amount number(15,2);
781 l_deadline_date varchar2(15);
782 l_dummy varchar2(1);
783
784
785 begin
786
787 if p_expression_type = 'Q' then
788
789 open get_answer;
790 fetch get_answer into l_answer;
791 if get_answer%notfound then
792 close get_answer;
793 return '1=2';
794 elsif get_answer%found then
795 close get_answer;
796 l_select_stmt := l_select_stmt||l_answer||p_operator||p_rvalue_id;
797
798 --dbms_output.put_line('Question SQL='||l_select_stmt);
799 return execute_dynamic_sql(l_select_stmt);
800 end if;
801
802 elsif p_expression_type = 'C' or p_expression_type = 'F' then
803
804 if p_lvalue = 'TOTAL_COST' OR p_lvalue = 'TOTAL_DIRECT_COST' OR
805 p_lvalue = 'TOTAL_INDIRECT_COST' OR p_lvalue = 'COST_SHARING_AMOUNT' OR
806 p_lvalue = 'UNDERRECOVERY_AMOUNT' then
807
808 open get_budget_amounts;
809 fetch get_budget_amounts into
810 l_total_cost,
811 l_total_direct_cost,
812 l_total_indirect_cost,
813 l_cost_sharing_amount,
814 l_underrecovery_amount;
815
816 if get_budget_amounts%notfound then
817 close get_budget_amounts;
818 return '1=2';
819 elsif get_budget_amounts%found then
820 close get_budget_amounts;
821 if p_lvalue = 'TOTAL_COST' then
822
823 l_select_stmt := l_select_stmt||to_char(l_total_cost)||p_operator||
824 p_rvalue_id;
825
826 elsif p_lvalue = 'TOTAL_DIRECT_COST' then
827
828 l_select_stmt := l_select_stmt||to_char(l_total_direct_cost)||
829 p_operator||p_rvalue_id;
830
831 elsif p_lvalue = 'TOTAL_INDIRECT_COST' then
832
833 l_select_stmt := l_select_stmt||to_char(l_total_indirect_cost)||
834 p_operator||p_rvalue_id;
835
836 elsif p_lvalue = 'COST_SHARING_AMOUNT' then
837
838 l_select_stmt := l_select_stmt||to_char(l_cost_sharing_amount)||
839 p_operator||p_rvalue_id;
840
841 elsif p_lvalue = 'UNDERRECOVERY_AMOUNT' then
842
843 l_select_stmt := l_select_stmt||to_char(l_underrecovery_amount)||
844 p_operator||p_rvalue_id;
845
846 end if;
847
848 --dbms_output.put_line(p_lvalue||' = '||l_select_stmt);
849
850 return execute_dynamic_sql(l_select_stmt);
851 end if;
852
853 elsif p_lvalue = 'DEADLINE_DATE' then
854
855 open get_deadline_date;
856 fetch get_deadline_date into l_deadline_date;
857 if l_deadline_date is null then
858 close get_deadline_date;
859 return '1=2';
860 elsif l_deadline_date is not null then
861 close get_deadline_date;
862 l_select_stmt := l_select_stmt||l_deadline_date||p_operator||p_rvalue_id;
863
864 --dbms_output.put_line('Deadline Date='||l_select_stmt);
865 return execute_dynamic_sql(l_select_stmt);
866 end if;
867
868 elsif p_lvalue = 'EXPENDITURE_TYPE' then
869
870 open get_expenditure_cat_type('N');
871 fetch get_expenditure_cat_type into l_dummy;
872 if get_expenditure_cat_type%notfound then
873
874 close get_expenditure_cat_type;
875 return not_found_string(p_operator);
876
877 elsif get_expenditure_cat_type%found then
878 close get_expenditure_cat_type;
879 return found_string(p_operator);
880
881 end if;
882
883
884 elsif p_lvalue = 'EXPENDITURE_CATEGORY' then
885
886 open get_expenditure_cat_type('Y');
887 fetch get_expenditure_cat_type into l_dummy;
888 if get_expenditure_cat_type%notfound then
889
890 close get_expenditure_cat_type;
891 return not_found_string(p_operator);
892
893 elsif get_expenditure_cat_type%found then
894 close get_expenditure_cat_type;
895 return found_string(p_operator);
896
897 end if;
898
899
900 elsif p_lvalue = 'LEAD_ORGANIZATION' then
901
902 open get_lead_org;
903 fetch get_lead_org into l_dummy;
904 if get_lead_org%notfound then
905
906 close get_lead_org;
907 return not_found_string(p_operator);
908
909 elsif get_lead_org%found then
910 close get_lead_org;
911 return found_string(p_operator);
912 end if;
913
914
915 elsif p_lvalue = 'OVERHEAD_RATE_DEVIATION' then
916
917 open get_overhead_rate_deviation;
918 fetch get_overhead_rate_deviation into l_dummy;
919 if get_overhead_rate_deviation%notfound then
920
921 close get_overhead_rate_deviation;
922 return not_found_string(p_operator);
923 elsif get_overhead_rate_deviation%found then
924 close get_overhead_rate_deviation;
925 return found_string(p_operator);
926 end if;
927
928
929 elsif p_lvalue = 'PI_IS_SPECIFIED_PERSON' then
930
931
932 open get_pi;
933 fetch get_pi into l_dummy;
934 if get_pi%notfound then
935
936 close get_pi;
937 return not_found_string(p_operator);
938 elsif get_pi%found then
939 close get_pi;
940 return found_string(p_operator);
941 end if;
942
943
944 elsif p_lvalue = 'SPECIAL_REVIEW_TYPE' then
945
946
947 open get_special_review_type;
948 fetch get_special_review_type into l_dummy;
949 if get_special_review_type%notfound then
950
951 close get_special_review_type;
952 return not_found_string(p_operator);
953 elsif get_special_review_type%found then
954 close get_special_review_type;
955 return found_string(p_operator);
956 end if;
957
958 end if;
959
960 end if;
961
962 exception
963 when others then
964 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','EXECUTE_LINE');
965 raise;
966 end execute_line;
967
968
969 ----------- function found_string -----------------------------
970
971 function found_string(p_operator in varchar2)
972 return varchar2 is
973
974 begin
975
976 if p_operator = '=' then
977 return '1=1';
978 elsif p_operator = '<>' then
979 return '1=2';
980 end if;
981
982 exception
983 when others then
984 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','FOUND_STRING');
985 raise;
986 end found_string;
987
988
989 ----------- function not_found_string -----------------------------
990 function not_found_string(p_operator in varchar2)
991 return varchar2 is
992
993 begin
994
995 if p_operator = '=' then
996 return '1=2';
997 elsif p_operator = '<>' then
998 return '1=1';
999 end if;
1000
1001 exception
1002 when others then
1003 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','NOT_FOUND_STRING');
1004 raise;
1005 end not_found_string;
1006
1007
1008
1009 ----------- function execute_dynamic_sql -----------------------------
1010 function execute_dynamic_sql(p_select_stmt in varchar2)
1011 return varchar2 as
1012
1013 l_cursor_name integer;
1014 l_rows_fetched integer;
1015 l_dummy_one number(1);
1016
1017 begin
1018
1019 l_cursor_name := dbms_sql.open_cursor;
1020 dbms_sql.parse(l_cursor_name,p_select_stmt,dbms_sql.v7);
1021 dbms_sql.define_column(l_cursor_name,1,l_dummy_one);
1022 l_rows_fetched := dbms_sql.execute_and_fetch(l_cursor_name);
1023 dbms_sql.close_cursor(l_cursor_name);
1024 if l_rows_fetched = 0 then
1025 return '1=2';
1026 else
1027 return '1=1';
1028 end if;
1029
1030 exception
1031 when others then
1032 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','EXECUTE_DYNAMIC_SQL');
1033 raise;
1034 end execute_dynamic_sql;
1035
1036
1037
1038 ----------- function get_parent_org_id -----------------------------
1039 function get_parent_org_id(l_org_id in number)
1040 return number as
1041
1042 cursor get_parent_org is
1043 select poe.organization_id_parent
1044 from per_org_structure_elements poe
1045 where poe.org_structure_version_id = (select apr_org_structure_version_id
1046 from igw_implementations)
1047 and poe.organization_id_child = l_org_id;
1048
1049 l_org_id_parent number(15);
1050
1051
1052 begin
1053 open get_parent_org;
1054 fetch get_parent_org into l_org_id_parent;
1055 close get_parent_org;
1056 return l_org_id_parent;
1057
1058 exception
1059 when others then
1060 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','GET_PARENT_ORG_ID');
1061 raise;
1062 end get_parent_org_id;
1063
1064
1065 ----------- procedure assign_so_role -----------------------------
1066 procedure assign_so_role(p_signing_official_id in number,
1067 p_admin_official_id in number) is
1068
1069 cursor get_user_id(l_person_id in number) is
1070 select user_id
1071 from fnd_user
1072 where employee_id = l_person_id;
1073
1074
1075 cursor so_user_exists(l_user_id in number) is
1076 select 'x'
1077 from igw_prop_users
1078 where proposal_id = g_proposal_id
1079 and user_id = l_user_id;
1080
1081 cursor so_user_role_exists(l_user_id in number, l_role_id in number) is
1082 select 'x'
1083 from igw_prop_user_roles
1084 where proposal_id = g_proposal_id
1085 and user_id = l_user_id
1086 and role_id = l_role_id;
1087
1088
1089 l_sign_off_user_id number(15);
1090 l_admin_off_user_id number(15);
1091 l_dummy varchar2(1);
1092
1093 begin
1094 open get_user_id(p_signing_official_id);
1095 fetch get_user_id into l_sign_off_user_id;
1096 close get_user_id;
1097
1098 open get_user_id(p_admin_official_id);
1099 fetch get_user_id into l_admin_off_user_id;
1100 close get_user_id;
1101
1102 -- Assign the SO to the proposal if it is not already assigned
1103 open so_user_exists(l_sign_off_user_id);
1104 fetch so_user_exists into l_dummy;
1105
1106 if so_user_exists%notfound then
1107
1108 insert into igw_prop_users(proposal_id,user_id,start_date_active,
1109 end_date_active,last_update_date,last_updated_by,creation_date,
1110 created_by,last_update_login)
1111 values(
1112 g_proposal_id,l_sign_off_user_id,sysdate,
1113 null,sysdate,fnd_global.user_id,sysdate,
1114 fnd_global.user_id,fnd_global.login_id);
1115
1116 end if;
1117 close so_user_exists;
1118 -- dbms_output.put_line('After inserting so into prop_users ');
1119
1120 -- Assign the AO to the proposal if it is not already assigned
1121 open so_user_exists(l_admin_off_user_id);
1122 fetch so_user_exists into l_dummy;
1123
1124 if so_user_exists%notfound then
1125
1126 insert into igw_prop_users(proposal_id,user_id,start_date_active,
1127 end_date_active,last_update_date,last_updated_by,creation_date,
1128 created_by,last_update_login)
1129 values(
1130 g_proposal_id,l_admin_off_user_id,sysdate,
1131 null,sysdate,fnd_global.user_id,sysdate,
1132 fnd_global.user_id,fnd_global.login_id);
1133
1134 end if;
1135 close so_user_exists;
1136 -- dbms_output.put_line('After inserting ao into prop_users ');
1137
1138 -- Assign Signing Official role(role_id=3) to Signing Official if it does not already exist and Administrative Official
1139
1140 open so_user_role_exists(l_sign_off_user_id, 3);
1141 fetch so_user_role_exists into l_dummy;
1142 if so_user_role_exists%notfound then
1143 insert into igw_prop_user_roles(proposal_id,user_id,role_id,
1144 last_update_date,last_updated_by,creation_date, created_by,
1145 last_update_login)
1146 values(
1147 g_proposal_id,l_sign_off_user_id,3,
1148 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1149 fnd_global.login_id);
1150 end if;
1151 close so_user_role_exists;
1152
1153
1154 if l_sign_off_user_id <> l_admin_off_user_id then
1155 open so_user_role_exists(l_admin_off_user_id, 3);
1156 fetch so_user_role_exists into l_dummy;
1157 if so_user_role_exists%notfound then
1158 insert into igw_prop_user_roles(proposal_id,user_id,role_id,
1159 last_update_date,last_updated_by,creation_date, created_by,
1160 last_update_login)
1161 values(
1162 g_proposal_id,l_admin_off_user_id,3,
1163 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1164 fnd_global.login_id);
1165 end if;
1166 close so_user_role_exists;
1167
1168 end if;
1169 end assign_so_role;
1170
1171
1172 ----------- procedure populate_local_wf_tables -----------------------------
1173 procedure populate_local_wf_tables(p_run_id in number) is
1174
1175 /*
1176 cursor one_approver_in_stop is
1177 select pms.prop_map_id,
1178 pms.stop_id
1179 from igw_prop_maps pm,
1180 igw_prop_map_stops pms
1181 where pms.prop_map_id = pm.prop_map_id
1182 and pm.run_id = p_run_id
1183 and pm.map_type = 'R'
1184 group by pms.prop_map_id,pms.stop_id
1185 having count(*) = 1;
1186 */
1187 cursor create_local_role is
1188 select distinct
1189 ppms.wf_role_name,
1190 ppms.wf_display_role_name
1191 from igw_prop_maps ppm,
1192 igw_prop_map_stops ppms
1193 where ppm.prop_map_id = ppms.prop_map_id
1194 and ppm.run_id = to_number(p_run_id)
1195 and wf_role_name <> user_name;
1196
1197 cursor assign_user_to_role is
1198 select distinct
1199 ppms.user_name,
1200 ppms.wf_role_name
1201 from igw_prop_maps ppm,
1202 igw_prop_map_stops ppms
1203 where ppm.prop_map_id = ppms.prop_map_id
1204 and ppm.run_id = to_number(p_run_id)
1205 and wf_role_name <> user_name;
1206
1207
1208 l_wf_role_name varchar2(100);
1209 l_wf_display_role_name varchar2(200);
1210 l_user_name varchar2(100);
1211 l_prop_map_id number(15);
1212 l_stop_id number(4);
1213
1214 begin
1215 /*
1216 -- update wf_role_name with user_name whereever there is one
1217 -- approver in a stop.If there is one approver in a stop, local
1218 -- workflow role will not be created and the user_name will be
1219 -- used as the workflow role name
1220 for i in one_approver_in_stop loop
1221
1222 update igw_prop_map_stops
1223 set wf_role_name = user_name
1224 where prop_map_id = i.prop_map_id
1225 and stop_id = i.stop_id;
1226
1227 end loop;
1228 */
1229
1230 -- create local workflow roles
1231 for j in create_local_role loop
1232
1233 wf_directory.createadhocrole(role_name => j.wf_role_name,
1234 role_display_name => j.wf_display_role_name,
1235 expiration_date => null);
1236 end loop;
1237
1238 -- assign users to the local workflow roles
1239 for k in assign_user_to_role loop
1240
1241 wf_directory.adduserstoadhocrole(role_name => k.wf_role_name,
1242 role_users => k.user_name);
1243 end loop;
1244
1245 exception
1246 when others then
1247 fnd_msg_pub.add_exc_msg('IGW_PROPOSAL_APPROVAL','POPULATE_LOCAL_WF_TABLES');
1248 raise;
1249 end populate_local_wf_tables;
1250
1251
1252 END IGW_PROP_APPROVALS_PVT;