1 PACKAGE BODY gms_award_status AS
2 /* $Header: gmsawrlb.pls 120.1 2005/07/26 14:20:58 appldev ship $ */
3
4 FUNCTION gms_primary_member (x_award_id IN NUMBER)
5 RETURN NUMBER IS
6
7 l_personnel_id NUMBER;
8 l_award_id NUMBER;
9 l_award_role VARCHAR2(30);
10
11 CURSOR award_primary_member IS
12 SELECT personnel_id
13 FROM gms_personnel
14 WHERE award_id = x_award_id
15 AND award_role = l_award_role
16 ORDER BY end_date_active desc;
17
18 BEGIN
19
20 --initializing all local variables to NULL.
21 l_personnel_id := NULL;
22 l_award_id := NULL;
23 l_award_role := NULL;
24
25 --checking if valid award_id is entered as input.
26 BEGIN
27 SELECT award_id
28 INTO l_award_id
29 FROM gms_awards
30 WHERE award_id = x_award_id;
31 EXCEPTION
32 WHEN NO_DATA_FOUND THEN
33 RETURN(-8888);
34 WHEN OTHERS THEN
35 RETURN(-9999);
36 END;
37
38 -- processing comes here only when valid award_id is entered as input.
39 -- checking for 'PI's for that Award.
40
41 l_award_role := 'PI';
42 OPEN award_primary_member;
43 FETCH award_primary_member INTO l_personnel_id;
44 CLOSE award_primary_member;
45
46 -- processing comes here only when valid award_id is entered
47 -- as input and award_role 'PI' is not defined.
48
49 IF l_personnel_id IS NULL THEN
50 l_award_role := 'AM';
51 OPEN award_primary_member;
52 FETCH award_primary_member INTO l_personnel_id;
53 CLOSE award_primary_member;
54 END IF;
55
56 RETURN (NVL(l_personnel_id, 0));
57
58 END gms_primary_member;
59
60 END GMS_AWARD_STATUS; --End of Package Body
[Home] [Help]