1 PACKAGE BODY IGS_PS_VAL_AW AS
2 /* $Header: IGSPS12B.pls 115.5 2003/06/09 04:41:10 smvk ship $ */
3
4 -- Validate update to IGS_PS_AWD type.
5 FUNCTION crsp_val_aw_upd(
6 p_award_cd IGS_PS_AWD.award_cd%TYPE ,
7 p_new_award_type IGS_PS_AWD.s_award_type%TYPE ,
8 p_old_award_type IGS_PS_AWD.s_award_type%TYPE ,
9 p_message_name OUT NOCOPY VARCHAR2)
10 RETURN BOOLEAN AS
11 BEGIN -- crsp_val_aw_upd
12 -- This routine validates changes to IGS_PS_AWD records. It checks;
13 -- change of system IGS_PS_AWD type does not invalidate IGS_GR_GRADUAND,
14 -- IGS_PS_COURSE IGS_PS_AWD or special IGS_PS_AWD details
15 /***************************************************************
16 Created By :
17 Date Created By :
18 Purpose :
19 Known Limitations,Enhancements or Remarks:
20 Change History :
21 Who When What
22 smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_caw to select open program awards only.
23 ***************************************************************/
24
25 DECLARE
26 CURSOR c_caw IS
27 SELECT 'x'
28 FROM IGS_PS_AWARD caw
29 WHERE caw.award_cd = p_award_cd
30 AND caw.closed_ind = 'N';
31 CURSOR c_gr IS
32 SELECT 'x'
33 FROM IGS_GR_GRADUAND gr
34 WHERE gr.award_cd = p_award_cd;
35 CURSOR c_spa IS
36 SELECT 'x'
37 FROM IGS_GR_SPECIAL_AWARD spa
38 WHERE spa.award_cd = p_award_cd;
39 v_c_exists VARCHAR2(1);
40 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
41 cst_honorary CONSTANT VARCHAR2(10) := 'HONORARY';
42 BEGIN
43 -- Set the default message number
44 p_message_name := NULL;
45 -- Check Parameters.
46 IF p_award_cd IS NULL OR
47 p_new_award_type IS NULL OR
48 p_old_award_type IS NULL THEN
49 RETURN TRUE;
50 END IF;
51 IF p_new_award_type = p_old_award_type THEN
52 RETURN TRUE;
53 END IF;
54 IF p_old_award_type = cst_course THEN
55 OPEN c_caw;
56 FETCH c_caw INTO v_c_exists;
57 IF c_caw%FOUND THEN
58 CLOSE c_caw;
59 p_message_name := 'IGS_GR_COUR_REC_IN_USE_UPD_NA';
60 RETURN FALSE;
61 END IF;
62 CLOSE c_caw;
63 ELSIF p_old_award_type = cst_honorary THEN
64 OPEN c_gr;
65 FETCH c_gr INTO v_c_exists;
66 IF c_gr%FOUND THEN
67 CLOSE c_gr;
68 p_message_name := 'IGS_GR_GRAD_REC_IN_USE_UPD_NA';
69 RETURN FALSE;
70 END IF;
71 CLOSE c_gr;
72 ELSE --special IGS_PS_AWDs
73 OPEN c_spa;
74 FETCH c_spa INTO v_c_exists;
75 IF c_spa%FOUND THEN
76 CLOSE c_spa;
77 p_message_name := 'IGS_GR_SPEC_REC_IN_USE_UPD_NA';
78 RETURN FALSE;
79 END IF;
80 CLOSE c_spa;
81 END IF;
82 -- Return the default value
83 RETURN TRUE;
84 EXCEPTION
85 WHEN OTHERS THEN
86 IF c_caw %ISOPEN THEN
87 CLOSE c_caw;
88 END IF;
89 IF c_gr %ISOPEN THEN
90 CLOSE c_gr;
91 END IF;
92 IF c_spa %ISOPEN THEN
93 CLOSE c_spa;
94 END IF;
95 App_Exception.Raise_Exception;
96 END;
97 EXCEPTION
98 WHEN OTHERS THEN
99 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
100 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_AW.crsp_val_aw_upd');
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END crsp_val_aw_upd;
104
105 -- Validate a testamur type is not closed.
106 FUNCTION crsp_val_tt_closed(
107 p_testamur_type IGS_GR_TESTAMUR_TYPE.testamur_type%TYPE ,
108 p_message_name OUT NOCOPY VARCHAR2)
109 RETURN BOOLEAN AS
110 BEGIN -- crsp_val_tt_closed
111 -- Validate if the testamur type is closed.
112 DECLARE
113 CURSOR c_tt IS
114 SELECT 'x'
115 FROM IGS_GR_TESTAMUR_TYPE tt
116 WHERE testamur_type = p_testamur_type AND
117 closed_ind = 'Y';
118 v_tt_exists VARCHAR2(1);
119 BEGIN
120 -- Set the default message number
121 p_message_name := NULL;
122 OPEN c_tt;
123 FETCH c_tt INTO v_tt_exists;
124 IF c_tt%FOUND THEN
125 CLOSE c_tt;
126 p_message_name := 'IGS_PS_TESTAMUR_TYPE_CLOSED';
127 RETURN FALSE;
128 END IF;
129 CLOSE c_tt;
130 -- Return the default value
131 RETURN TRUE;
132 EXCEPTION
133 WHEN OTHERS THEN
134 IF c_tt%ISOPEN THEN
135 CLOSE c_tt;
136 END IF;
137 App_Exception.Raise_Exception;
138 END;
139 EXCEPTION
140 WHEN OTHERS THEN
141 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
142 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_AW.crsp_val_tt_closed');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END crsp_val_tt_closed;
146
147 END IGS_PS_VAL_AW;
[Home] [Help]