[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_EXTRACURR_ACT_PKG
Source
1 PACKAGE BODY Igs_Ad_Extracurr_Act_Pkg AS
2 /* $Header: IGSAI85B.pls 115.18 2003/11/27 13:25:18 gmaheswa ship $ */
3 PROCEDURE insert_row (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_person_interest_id OUT NOCOPY NUMBER,
6 x_person_id IN NUMBER,
7 x_interest_type_code IN VARCHAR2,
8 x_comments IN VARCHAR2,
9 x_start_date IN DATE,
10 x_end_date IN DATE,
11 x_hours_per_week IN NUMBER,
12 x_weeks_per_year IN NUMBER,
13 x_level_of_interest IN VARCHAR2,
14 x_level_of_participation IN VARCHAR2,
15 x_sport_indicator IN VARCHAR2,
16 x_sub_interest_type_code IN VARCHAR2,
17 x_interest_name IN VARCHAR2,
18 x_team IN VARCHAR2,
19 x_wh_update_date IN DATE,
20 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
21 x_last_update_date OUT NOCOPY DATE,
22 x_msg_Data OUT NOCOPY VARCHAR2,
23 x_return_Status OUT NOCOPY VARCHAR2,
24 x_object_version_number IN OUT NOCOPY NUMBER,
25 x_mode IN VARCHAR2 DEFAULT 'R'
26 ) AS
27 p_per_interest_rec Hz_Person_Info_V2Pub.person_interest_rec_type;
28 l_return_status VARCHAR2(1);
29 l_msg_count NUMBER;
30 l_msg_Data VARCHAR2(200);
31 l_person_interest_id NUMBER;
32 l_RowId VARCHAR2(25);
33 l_hz_extracurr_act_id NUMBER;
34
35 tmp_var1 VARCHAR2(2000);
36 tmp_var VARCHAR2(2000);
37 CURSOR c_birth_date_val is SELECT date_of_birth FROM HZ_PERSON_PROFILES
38 WHERE party_id = x_person_id AND effective_end_Date is null;
39 l_date_of_birth HZ_PERSON_PROFILES.DATE_OF_BIRTH%TYPE;
40
41
42 BEGIN
43
44 -- A few validation that need to be done
45 -- added by amuthu during ID prospective applicant part 2 of 1 build
46
47 IF NOT (x_start_date <= x_end_date) THEN
48 Fnd_Message.Set_Name('IGS','IGS_FI_ST_DT_LE_END_DT');
49 IGS_GE_MSG_STACK.ADD;
50 x_msg_data := FND_MESSAGE.GET;
51 l_msg_count :=1;
52 x_return_status := 'E';
53 RETURN;
54 ELSIF NOT (x_start_date <= SYSDATE ) THEN
55 Fnd_Message.Set_Name('IGS','IGS_AD_ST_DT_LT_SYS_DT');
56 IGS_GE_MSG_STACK.ADD;
57 x_msg_data := FND_MESSAGE.GET;
58 l_msg_count :=1;
59 x_return_status := 'E';
60 RETURN;
61 END IF;
62
63 IF x_start_date IS NULL AND x_end_date IS NOT NULL THEN
64 Fnd_Message.Set_Name('IGS','IGS_EN_TS_SDT');
65 IGS_GE_MSG_STACK.ADD;
66 x_msg_data := FND_MESSAGE.GET;
67 x_return_status := 'E';
68 l_msg_count :=1;
69 RETURN;
70 END IF;
71
72 IF NOT (x_end_date >= x_start_date OR x_end_date IS NULL) THEN
73 Fnd_Message.Set_Name('IGS','IGS_GE_END_DT_GE_ST_DATE');
74 IGS_GE_MSG_STACK.ADD;
75 x_msg_data := FND_MESSAGE.GET;
76 x_return_status := 'E';
77 l_msg_count :=1;
78 RETURN;
79 END IF;
80
81 IF NOT (x_hours_per_week >= 0
82 AND x_hours_per_week <= 168 ) THEN
83 Fnd_Message.Set_Name('IGS','IGS_AD_HRS_PER_WEEK');
84 IGS_GE_MSG_STACK.ADD;
85 x_msg_data := FND_MESSAGE.GET;
86 x_return_status := 'E';
87 l_msg_count :=1;
88 RETURN;
89 END IF;
90
91 IF NOT (x_weeks_per_year >= 0
92 AND x_weeks_per_year <= 52 ) THEN
93 Fnd_Message.Set_Name('IGS','IGS_AD_WKS_PER_YEAR');
94 IGS_GE_MSG_STACK.ADD;
95 x_msg_data := FND_MESSAGE.GET;
96 x_return_status := 'E';
97 l_msg_count :=1;
98 RETURN;
99 END IF;
100 OPEN c_birth_date_val; FETCH c_birth_date_val INTO l_date_of_birth; CLOSE c_birth_date_val;
101 IF(x_start_date IS NOT NULL AND l_Date_of_birth IS NOT NULL) THEN
102 IF(x_start_date < l_date_of_birth) THEN
103 Fnd_Message.Set_Name('IGS','IGS_PE_DREC_GT_BTDT');
104 IGS_GE_MSG_STACK.ADD;
105 x_msg_data := FND_MESSAGE.GET;
106 x_return_status := 'E';
107 l_msg_count :=1;
108 RETURN;
109 END IF;
110 END IF;
111
112 p_per_interest_rec.level_of_interest := x_level_of_interest;
113 p_per_interest_rec.level_of_participation := x_level_of_Participation;
114 p_per_interest_rec.interest_type_code := x_interest_type_code;
115 p_per_interest_rec.party_id := x_person_id;
116 p_per_interest_rec.sport_indicator := x_Sport_Indicator;
117 p_per_interest_rec.interest_name := x_Interest_Name;
118 p_per_interest_rec.comments := x_Comments;
119 p_per_interest_rec.sub_interest_type_code := x_Sub_Interest_Type_Code;
120 p_per_interest_rec.team := x_team;
121 p_per_interest_rec.since := x_Start_Date;
122 p_per_interest_rec.created_by_module := 'IGS';
123 p_per_interest_rec.application_id := 8405;
124 -- initialize message count.
125
126 l_msg_count :=0;
127 --gmaheswa: HZ_API ia changed to HZ_PERSON_INFOR_V2PUB from HZ_PER_INFO_PUB.
128 Hz_Person_Info_V2Pub.create_person_interest(
129 p_init_msg_list => FND_API.G_TRUE,
130 p_person_interest_rec => p_per_interest_rec,
131 x_person_interest_id => l_person_interest_id,
132 x_return_status => l_return_status,
133 x_msg_count => l_msg_count,
134 x_msg_data => l_msg_data
135
136 );
137
138
139 x_return_status := l_return_Status;
140
141 IF l_return_status IN ('E','U') THEN
142 IF l_msg_count > 1 THEN
143 FOR i IN 1..l_msg_count LOOP
144 tmp_var := fnd_msg_pub.get(p_msg_index =>i, p_encoded => fnd_api.g_false);
145 tmp_var1 := tmp_var1 || ' '|| tmp_var;
146 END LOOP;
147 x_msg_data := tmp_var1;
148 ELSE
149 x_msg_data := l_msg_data;
150 END IF;
151
152 RETURN;
153
154 ELSE
155
156 x_person_interest_id := l_person_interest_id;
157 x_object_version_number := 1;
158 -- selecting the value of the last_update_date ot pass
159 -- it back to the calling form to enable locking when the record
160 -- is updated immediately after inserting.
161
162 Igs_Ad_Hz_Extracurr_Act_Pkg.Insert_Row(
163 x_rowid => l_rowId,
164 x_hz_extracurr_act_id => l_hz_extracurr_act_id,
165 x_person_interest_id => x_person_interest_id,
166 x_end_date => x_End_Date,
167 x_hours_per_week => x_Hours_per_Week,
168 x_weeks_per_year => x_Weeks_Per_Year,
169 x_activity_source_cd => x_activity_source_cd,
170 x_mode => 'R') ;
171 x_rowid := l_rowId;
172 END IF;
173
174 END insert_row;
175
176
177 PROCEDURE update_row (
178 x_rowid IN VARCHAR2,
179 x_person_interest_id IN NUMBER,
180 x_person_id IN NUMBER,
181 x_interest_type_code IN VARCHAR2,
182 x_comments IN VARCHAR2,
183 x_start_date IN DATE,
184 x_end_date IN DATE,
185 x_hours_per_week IN NUMBER,
186 x_weeks_per_year IN NUMBER,
187 x_level_of_interest IN VARCHAR2,
188 x_level_of_participation IN VARCHAR2,
189 x_sport_indicator IN VARCHAR2,
190 x_sub_interest_type_code IN VARCHAR2,
191 x_interest_name IN VARCHAR2,
192 x_team IN VARCHAR2,
193 x_wh_update_date IN DATE,
194 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
195 x_last_update_date IN OUT NOCOPY DATE,
196 x_msg_Data OUT NOCOPY VARCHAR2,
197 x_return_Status OUT NOCOPY VARCHAR2,
198 x_object_version_number IN OUT NOCOPY NUMBER,
199 x_mode IN VARCHAR2 DEFAULT 'R'
200 ) AS
201 /*
202 || Created By : [email protected]
203 || Created On : 07-SEP-2000
204 || Purpose : Handles the UPDATE DML logic for the table.
205 || Known limitations, enhancements or remarks :
206 || Change History :
207 || Who When What
208 || (reverse chronological order - newest change first)
209 */
210 p_per_interest_rec Hz_Person_Info_V2Pub.person_interest_rec_type;
211 l_return_status VARCHAR2(1);
212 l_msg_count NUMBER;
213 l_msg_Data VARCHAR2(200);
214 l_person_interest_id NUMBER;
215 l_RowId VARCHAR2(25);
216 l_hz_extracurr_act_id NUMBER;
217 lv_Last_Update_Date DATE;
218 CURSOR C1 IS
219 SELECT
220 ROWID, HZ_EXTRACURR_ACT_ID
221 FROM
222 IGS_AD_HZ_EXTRACURR_ACT
223 WHERE
224 PERSON_INTEREST_ID = x_person_interest_id;
225 tmp_var1 VARCHAR2(2000);
226 tmp_var VARCHAR2(2000);
227
228 CURSOR c_birth_date_val is SELECT date_of_birth FROM HZ_PERSON_PROFILES
229 WHERE party_id = x_person_id AND effective_end_Date is null;
230 l_date_of_birth HZ_PERSON_PROFILES.DATE_OF_BIRTH%TYPE;
231
232
233 BEGIN
234
235 -- A few validation that need to be done
236
237 -- The following code checks for check constraints on the Columns.
238
239 IF NOT (x_start_date <= x_end_date) THEN
240 Fnd_Message.Set_Name('IGS','IGS_FI_ST_DT_LE_END_DT');
241 IGS_GE_MSG_STACK.ADD;
242 x_msg_data := FND_MESSAGE.GET;
243 x_return_status := 'E';
244 RETURN;
245 ELSIF NOT (x_start_date <= SYSDATE ) THEN
246 Fnd_Message.Set_Name('IGS','IGS_AD_ST_DT_LT_SYS_DT');
247 IGS_GE_MSG_STACK.ADD;
248 x_msg_data := FND_MESSAGE.GET;
249 x_return_status := 'E';
250 RETURN;
251 END IF;
252
253 IF x_start_date IS NULL AND x_end_date IS NOT NULL THEN
254 Fnd_Message.Set_Name('IGS','IGS_EN_TS_SDT');
255 IGS_GE_MSG_STACK.ADD;
256 x_msg_data := FND_MESSAGE.GET;
257 x_return_status := 'E';
258 RETURN;
259 END IF;
260
261 IF NOT (x_end_date >= x_start_date OR x_end_date IS NULL) THEN
262 Fnd_Message.Set_Name('IGS','IGS_GE_END_DT_GE_ST_DATE');
263 IGS_GE_MSG_STACK.ADD;
264 x_msg_data := FND_MESSAGE.GET;
265 x_return_status := 'E';
266 RETURN;
267 END IF;
268
269 IF NOT (x_hours_per_week >= 0
270 AND x_hours_per_week <= 168 ) THEN
271 Fnd_Message.Set_Name('IGS','IGS_AD_HRS_PER_WEEK');
272 IGS_GE_MSG_STACK.ADD;
273 x_msg_data := FND_MESSAGE.GET;
274 x_return_status := 'E';
275 RETURN;
276 END IF;
277
278 IF NOT (x_weeks_per_year >= 0
279 AND x_weeks_per_year <= 52 ) THEN
280 Fnd_Message.Set_Name('IGS','IGS_AD_WKS_PER_YEAR');
281 IGS_GE_MSG_STACK.ADD;
282 x_msg_data := FND_MESSAGE.GET;
283 x_return_status := 'E';
284 RETURN;
285 END IF;
286 OPEN c_birth_date_val; FETCH c_birth_date_val INTO l_date_of_birth; CLOSE c_birth_date_val;
287 IF(x_start_date IS NOT NULL AND l_Date_of_birth IS NOT NULL) THEN
288 IF(x_start_date < l_date_of_birth) THEN
289 Fnd_Message.Set_Name('IGS','IGS_PE_DREC_GT_BTDT');
290 IGS_GE_MSG_STACK.ADD;
291 x_msg_data := FND_MESSAGE.GET;
292 x_return_status := 'E';
293 l_msg_count :=1;
294 RETURN;
295 END IF;
296 END IF;
297
298
299 p_per_interest_rec.person_interest_id := x_person_interest_id;
300 p_per_interest_rec.level_of_interest := NVL(x_level_of_interest,FND_API.G_MISS_CHAR);
301 p_per_interest_rec.level_of_participation := NVL(x_level_of_Participation,FND_API.G_MISS_CHAR);
302 p_per_interest_rec.interest_type_code := NVL(x_interest_type_code,FND_API.G_MISS_CHAR);
303 p_per_interest_rec.party_id := x_person_id;
304 p_per_interest_rec.sport_indicator := NVL(x_Sport_Indicator,FND_API.G_MISS_CHAR);
305 p_per_interest_rec.interest_name := NVL(x_Interest_Name,FND_API.G_MISS_CHAR);
306 p_per_interest_rec.comments := NVL(x_Comments,FND_API.G_MISS_CHAR);
307 p_per_interest_rec.sub_interest_type_code := NVL(x_Sub_Interest_Type_Code,FND_API.G_MISS_CHAR);
308 p_per_interest_rec.team := NVL(x_team,FND_API.G_MISS_CHAR);
309 p_per_interest_rec.since := NVL(x_Start_Date,FND_API.G_MISS_DATE);
310
311 --gmaheswa: HZ_API ia changed to HZ_PERSON_INFOR_V2PUB from HZ_PER_INFO_PUB.
312 Hz_Person_Info_V2Pub.Update_person_interest(
313 p_init_msg_list => FND_API.G_TRUE,
314 p_person_interest_rec => p_per_interest_rec,
315 p_object_version_number => x_object_version_number,
316 x_return_status => l_return_status,
317 x_msg_count => l_msg_count,
318 x_msg_data => l_msg_data
319
320 );
321
322 x_return_status := l_return_Status;
323
324 IF l_return_status IN ('E','U') THEN
325
326 IF l_msg_count > 1 THEN
327 FOR i IN 1..l_msg_count LOOP
328 tmp_var := fnd_msg_pub.get(p_msg_index =>i, p_encoded => fnd_api.g_false);
329 tmp_var1 := tmp_var1 || ' '|| tmp_var;
330 END LOOP;
331 x_msg_data := tmp_var1;
332 ELSE
333 x_msg_data := l_msg_data;
334 END IF;
335 RETURN;
336
337 ELSE
338
339 OPEN C1;
340 FETCH C1 INTO l_RowId, l_hz_ExtraCurr_Act_Id;
341 CLOSE C1;
342 Igs_Ad_Hz_Extracurr_Act_Pkg.Update_Row(
343 x_rowid => l_rowId,
344 x_hz_extracurr_act_id => l_hz_ExtraCurr_Act_Id,
345 x_person_interest_id => x_person_interest_id,
346 x_end_date => x_End_Date,
347 x_hours_per_week => x_Hours_per_Week,
348 x_weeks_per_year => x_Weeks_Per_Year,
349 x_activity_source_cd => x_activity_source_cd,
350 x_mode => 'R') ;
351 END IF;
352 END update_row;
353
354
355 END Igs_Ad_Extracurr_Act_Pkg;