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