1 PACKAGE BODY igf_ap_awd_yr_setup AS
2 /* $Header: IGFAP31B.pls 115.1 2003/06/16 14:34:08 brajendr noship $ */
3 /*
4 || Created By : cdcruz
5 || Created On : 01 Jun 2003
6 ||
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || (reverse chronological order - newest change first)
11
12 */
13
14 PROCEDURE p_validate_aw_year ( p_sys_awd_yr IN VARCHAR2, -- System award year
15 p_return_val OUT NOCOPY VARCHAR2)
16 AS
17 /*
18 || Created By : cdcruz
19 || Created On : 01 Jun 2003
20 ||
21 || Known limitations, enhancements or remarks :
22 || Change History :
23 || Who When What
24 ||
25 || (reverse chronological order - newest change first)
26 */
27 -- Initialize the local variables
28
29
30 CURSOR cur_award IS
31 SELECT
32 SUBSTR(sys_award_year,4) code,
33 award_year_status_code status,
34 sys_award_year_mean
35 FROM
36 igf_ap_batch_aw_map_v
37 WHERE
38 sys_award_year like 'LI%'
39 UNION
40 SELECT
41 '20'||SUBSTR(sys_award_year,0,2) code,
42 award_year_status_code status,
43 sys_award_year_mean
44 FROM
45 igf_ap_batch_aw_map_v
46 WHERE
47 Sys_award_year NOT LIKE 'LI%'
48 ORDER BY 1;
49
50 lv_cur_rec cur_award%rowtype;
51
52 lv_prev_year NUMBER;
53 lv_curr_year NUMBER;
54 lv_prev_status igf_ap_batch_aw_map.award_year_status_code%TYPE;
55 lv_curr_status igf_ap_batch_aw_map.award_year_status_code%TYPE;
56 l_valid BOOLEAN ;
57 l_profile_set VARCHAR2(30);
58 l_school VARCHAR2(10) := 'US';
59
60 BEGIN
61
62 lv_prev_year := 0;
63 lv_curr_year := 0;
64 lv_prev_status := NULL;
65 lv_curr_status := NULL;
66 l_valid := TRUE;
67 p_return_val := 'VALID' ;
68
69 l_profile_set := igf_ap_gen.check_profile ;
70
71 IF NVL(l_profile_set,'N') <> 'Y' THEN
72 l_school := 'UK';
73 END IF;
74
75 OPEN cur_award;
76 LOOP
77 FETCH cur_award INTO lv_cur_rec;
78 EXIT WHEN cur_award%NOTFOUND;
79
80 lv_curr_year := TO_NUMBER(lv_cur_rec.code);
81 lv_curr_status := lv_cur_rec.status;
82
83 IF lv_curr_status IS NOT NULL AND
84 lv_prev_status IS NOT NULL THEN
85
86 IF (lv_curr_year - 1) <> lv_prev_year THEN
87
88 fnd_message.set_name('IGF','IGF_AW_SETUP_GAP_E1');
89 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
90 fnd_msg_pub.add;
91 l_valid := FALSE;
92 CLOSE cur_award;
93 EXIT;
94 END IF;
95
96 END IF;
97
98 IF l_school = 'UK' THEN
99 l_valid := TRUE;
100 ELSE
101
102 IF lv_curr_year < 2002 AND lv_curr_status NOT IN ('LE','LA') THEN
103 fnd_message.set_name('IGF','IGF_AW_SETUP_DET_E1');
104 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
105 fnd_msg_pub.add;
106 l_valid := FALSE;
107 CLOSE cur_award;
108 EXIT;
109 END IF;
110
111 IF lv_prev_status = 'LE' THEN
112
113 IF lv_curr_status = 'LE' THEN
114 fnd_message.set_name('IGF','IGF_AW_SETUP_LE_DUP');
115 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
116 fnd_msg_pub.add;
117 l_valid := FALSE;
118 CLOSE cur_award;
119 EXIT;
120
121 END IF;
122
123 ELSIF lv_prev_status = 'LA' THEN
124
125 IF lv_curr_status = 'LE' THEN
126 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E1');
127 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
128 fnd_msg_pub.add;
129 l_valid := FALSE;
130 CLOSE cur_award;
131 EXIT;
132
133 END IF;
134
135 ELSIF lv_prev_status = 'LD' THEN
136 --
137 -- Current Status should not be in ('LA','LE')
138 --
139 IF lv_curr_status IN ('LA') THEN
140 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E2');
141 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
142 fnd_msg_pub.add;
143 l_valid := FALSE;
144 CLOSE cur_award;
145 EXIT;
146 END IF;
147
148 IF lv_curr_status IN ('LE') THEN
149 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E1');
150 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
151 fnd_msg_pub.add;
152 l_valid := FALSE;
153 CLOSE cur_award;
154 EXIT;
155 END IF;
156
157 ELSIF lv_prev_status = 'O' THEN
158 --
159 -- Current Status should not be in ('LA','LE','LD')
160 --
161 IF lv_curr_status IN ('LA','LE','LD') THEN
162
163 IF lv_curr_status IN ('LE') THEN
164 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E1');
165 ELSIF lv_curr_status IN ('LA') THEN
166 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E2');
167 ELSIF lv_curr_status IN ('LD') THEN
168 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E3');
169 END IF;
170
171 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
172 fnd_msg_pub.add;
173 l_valid := FALSE;
174 CLOSE cur_award;
175 EXIT;
176
177 END IF;
178
179 ELSIF lv_prev_status = 'C' THEN
180 --
181 -- Current Status should not be in ('LA','LE','LD')
182 --
183 IF lv_curr_status IN ('LA','LE','LD') THEN
184
185 IF lv_curr_status IN ('LE') THEN
186 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E1');
187 ELSIF lv_curr_status IN ('LA') THEN
188 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E2');
189 ELSIF lv_curr_status IN ('LD') THEN
190 fnd_message.set_name('IGF','IGF_AW_SETUP_ORD_E3');
191 END IF;
192
193 fnd_message.set_token('SYS_AWD_YEAR',lv_cur_rec.sys_award_year_mean);
194 fnd_msg_pub.add;
195 l_valid := FALSE;
196 CLOSE cur_award;
197 EXIT;
198 END IF;
199
200 END IF;
201 END IF;
202
203 lv_prev_status := lv_curr_status;
204 lv_prev_year := lv_curr_year;
205
206 END LOOP;
207
208 IF l_valid THEN
209 p_return_val := 'VALID' ;
210 ELSE
211 p_return_val := 'INVALID' ;
212 END IF;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
217 FND_MESSAGE.SET_TOKEN('NAME','p_validate_aw_year');
218 IGS_GE_MSG_STACK.ADD;
219 APP_EXCEPTION.RAISE_EXCEPTION;
220 END p_validate_aw_year;
221
222 END igf_ap_awd_yr_setup;
[Home] [Help]