1 PACKAGE BODY IGS_PS_VAL_TR AS
2 /* $Header: IGSPS57B.pls 115.5 2002/12/12 09:48:08 smvk ship $ */
3 --
4 -- Validate teaching responsibility percentage for the IGS_PS_UNIT version
5 FUNCTION crsp_val_tr_perc(
6 p_unit_cd IN VARCHAR2 ,
7 p_version_number IN NUMBER ,
8 p_message_name OUT NOCOPY VARCHAR2,
9 p_b_lgcy_validator IN BOOLEAN )
10 RETURN BOOLEAN AS
11
12 /***********************************************************************************************
13 Created By :
14 Date Created By:
15 Purpose :
16
17 Known limitations,enhancements,remarks:
18 Change History (in reverse chronological order)
19 Who When What
20 smvk 12-Dec-2002 Added a boolean parameter p_b_lgcy_validator to the function call crsp_val_tr_perc.
21 As a part of the Bug # 2696207
22 ********************************************************************************************** */
23
24 gv_teach_respons CHAR;
25 gv_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
26 CURSOR gc_unit_status IS
27 SELECT US.s_unit_status
28 FROM IGS_PS_UNIT_VER UV,
29 IGS_PS_UNIT_STAT US
30 WHERE UV.unit_cd = p_unit_cd AND
31 UV.version_number = p_version_number AND
32 UV.unit_status= US.unit_status;
33 CURSOR gc_teach_respons_exists IS
34 SELECT 'x'
35 FROM IGS_PS_TCH_RESP
36 WHERE unit_cd = p_unit_cd AND
37 version_number = p_version_number;
38 CURSOR cur_user IS
39 SELECT SUM(percentage)
40 FROM IGS_PS_TCH_RESP
41 WHERE unit_cd = p_unit_cd AND
42 version_number = p_version_number;
43
44
45 gv_percent IGS_PS_TCH_RESP.percentage%TYPE;
46
47 BEGIN
48 -- finding the s_unit_status
49 OPEN gc_unit_status;
50 FETCH gc_unit_status INTO gv_unit_status;
51 -- finding IGS_PS_TCH_RESP records
52 OPEN gc_teach_respons_exists;
53 FETCH gc_teach_respons_exists INTO gv_teach_respons;
54 -- Find the sum of all percentages
55
56 OPEN cur_user;
57 FETCH cur_user INTO gv_percent;
58 IF cur_user%NOTFOUND THEN
59 RAISE no_data_found ;
60 END IF;
61 CLOSE cur_user ;
62
63 -- when the percentage totals 100
64 IF gv_percent = 100.00 THEN
65 CLOSE gc_unit_status;
66 CLOSE gc_teach_respons_exists;
67 p_message_name := NULL;
68 RETURN TRUE;
69 ELSE
70 -- when the percentage doesn't total 100 and
71 -- if the IGS_PS_UNIT_STAT.s_unit_status is PLANNED
72 -- and no teaching responsibility records exist
73 IF (gv_unit_status = 'PLANNED' AND gc_teach_respons_exists%NOTFOUND) AND (NOT p_b_lgcy_validator) THEN
74 CLOSE gc_unit_status;
75 CLOSE gc_teach_respons_exists;
76 p_message_name := NULL;
77 RETURN TRUE;
78 ELSE
79 -- when the percentage doesn't total 100 and
80 -- if the IGS_PS_UNIT_STAT.s_unit_status is not PLANNED
81 -- or no teaching responsibility records exist
82 CLOSE gc_unit_status;
83 CLOSE gc_teach_respons_exists;
84 p_message_name := 'IGS_PS_TCHRESP_NOTTOTAL_100';
85 RETURN FALSE;
86 END IF;
87 END IF;
88 EXCEPTION
89 WHEN no_data_found THEN
90 CLOSE cur_user;
91 WHEN OTHERS THEN
92 IF cur_user%ISOPEN THEN
93 CLOSE cur_user;
94 END IF;
95 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
96 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_TR.crsp_val_tr_perc');
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99 END crsp_val_tr_perc;
100
101 END IGS_PS_VAL_TR;
[Home] [Help]