[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ENCMB_EFCTTYP_PKG
Source
1 package body IGS_EN_ENCMB_EFCTTYP_PKG AS
2 /* $Header: IGSEI32B.pls 115.5 2003/02/17 12:31:08 adhawan ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_ENCMB_EFCTTYP%RowType;
6 new_references IGS_EN_ENCMB_EFCTTYP%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
12 x_apply_to_course_ind IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM IGS_EN_ENCMB_EFCTTYP
24 WHERE rowid = x_rowid;
25
26 BEGIN
27
28 l_rowid := x_rowid;
29
30 -- Code for setting the Old and New Reference Values.
31 -- Populate Old Values.
32 Open cur_old_ref_values;
33 Fetch cur_old_ref_values INTO old_references;
34 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ( 'INSERT','VALIDATE_INSERT' )) THEN
35 Close cur_old_ref_values;
36 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37 IGS_GE_MSG_STACK.ADD;
38 App_Exception.Raise_Exception;
39 Return;
40 END IF;
41 Close cur_old_ref_values;
42
43 -- Populate New Values.
44 new_references.s_encmb_effect_type := x_s_encmb_effect_type;
45 new_references.apply_to_course_ind := x_apply_to_course_ind;
46 new_references.closed_ind := x_closed_ind;
47 IF (p_action = 'UPDATE') THEN
48 new_references.creation_date := old_references.creation_date;
49 new_references.created_by := old_references.created_by;
50 ELSE
51 new_references.creation_date := x_creation_date;
52 new_references.created_by := x_created_by;
53 END IF;
54 new_references.last_update_date := x_last_update_date;
55 new_references.last_updated_by := x_last_updated_by;
56 new_references.last_update_login := x_last_update_login;
57
58 END Set_Column_Values;
59
60 procedure Check_constraints(
61 column_name IN VARCHAR2 DEFAULT NULL,
62 column_value IN VARCHAR2 DEFAULT NULL
63 ) AS
64 begin
65 IF column_name is null then
66 NULL;
67 ELSIF upper(column_name) = 'APPLY_TO_COURSE_IND' THEN
68 new_references.apply_to_course_ind := column_value;
69 ELSIF upper(column_name) = 'CLOSED_IND' THEN
70 new_references.closed_ind := column_value;
71 ELSIF upper(column_name) = 'S_ENCMB_EFFECT_TYPE' THEN
72 new_references.s_encmb_effect_type := column_value;
73 END IF;
74
75 IF upper(column_name) = 'APPLY_TO_COURSE_IND' OR
76 Column_name is null THEN
77 IF new_references.apply_to_course_ind NOT IN ('Y','N') THEN
78 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
79 IGS_GE_MSG_STACK.ADD;
80 App_Exception.Raise_Exception;
81 END IF;
82 END IF;
83
84 IF upper(column_name) = 'CLOSED_IND' OR
85 Column_name is null THEN
86 IF new_references.closed_ind NOT IN ('Y','N') THEN
87 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END IF;
91 END IF;
92
93 IF upper(column_name) = 'S_ENCMB_EFFECT_TYPE' OR
94 Column_name is null THEN
95 IF new_references.s_encmb_effect_type <>
96 upper(new_references.s_encmb_effect_type) THEN
97 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102
103 END check_constraints;
104
105 FUNCTION Get_PK_For_Validation (
106 x_s_encmb_effect_type IN VARCHAR2
107 ) RETURN BOOLEAN AS
108
109 CURSOR cur_rowid IS
110 SELECT rowid
111 FROM IGS_EN_ENCMB_EFCTTYP
112 WHERE s_encmb_effect_type = x_s_encmb_effect_type;
113
114
115 lv_rowid cur_rowid%RowType;
116
117 BEGIN
118
119 Open cur_rowid;
120 Fetch cur_rowid INTO lv_rowid;
121 IF (cur_rowid%FOUND) THEN
122 Close cur_rowid;
123 return(TRUE);
124 else
125 Close cur_rowid;
126 Return(FALSE);
127 END IF;
128
129 END Get_PK_For_Validation;
130
131 PROCEDURE Before_DML (
132 p_action IN VARCHAR2,
133 x_rowid IN VARCHAR2 DEFAULT NULL,
134 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
135 x_apply_to_course_ind IN VARCHAR2 DEFAULT NULL,
136 x_closed_ind IN VARCHAR2 DEFAULT NULL,
137 x_creation_date IN DATE DEFAULT NULL,
138 x_created_by IN NUMBER DEFAULT NULL,
139 x_last_update_date IN DATE DEFAULT NULL,
140 x_last_updated_by IN NUMBER DEFAULT NULL,
141 x_last_update_login IN NUMBER DEFAULT NULL
142 ) AS
143 BEGIN
144
145 Set_Column_Values (
146 p_action,
147 x_rowid,
148 x_s_encmb_effect_type,
149 x_apply_to_course_ind,
150 x_closed_ind,
151 x_creation_date,
152 x_created_by,
153 x_last_update_date,
154 x_last_updated_by,
155 x_last_update_login
156 );
157
158 IF (p_action = 'INSERT') THEN
159 -- Call all the procedures related to Before Insert.
160 Null;
161 IF Get_PK_For_Validation (
162 new_references.s_encmb_effect_type
163 ) THEN
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 Check_constraints;
169 ELSIF (p_action = 'UPDATE') THEN
170 -- Call all the procedures related to Before Update.
171 Check_constraints;
172
173
174 ELSIF (p_action = 'VALIDATE_INSERT') then
175 IF Get_PK_For_Validation (
176 new_references.s_encmb_effect_type
177 ) THEN
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182
183 Check_constraints;
184 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
185 Check_constraints;
186 END IF;
187 END Before_DML;
188
189 PROCEDURE After_DML (
190 p_action IN VARCHAR2,
191 x_rowid IN VARCHAR2
192 ) AS
193 BEGIN
194
195 l_rowid := x_rowid;
196
197 IF (p_action = 'INSERT') THEN
198 -- Call all the procedures related to After Insert.
199 Null;
200 ELSIF (p_action = 'UPDATE') THEN
201 -- Call all the procedures related to After Update.
202 Null;
203 END IF;
204
205 END After_DML;
206
207 procedure INSERT_ROW (
208 X_ROWID in out NOCOPY VARCHAR2,
209 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
210 X_APPLY_TO_COURSE_IND in VARCHAR2,
211 X_CLOSED_IND in VARCHAR2,
212 X_MODE in VARCHAR2 default 'R'
213 ) AS
214 cursor C is select ROWID from IGS_EN_ENCMB_EFCTTYP
215 where S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE;
216 X_LAST_UPDATE_DATE DATE;
217 X_LAST_UPDATED_BY NUMBER;
218 X_LAST_UPDATE_LOGIN NUMBER;
219 begin
220 X_LAST_UPDATE_DATE := SYSDATE;
221 if(X_MODE = 'I') then
222 X_LAST_UPDATED_BY := 1;
223 X_LAST_UPDATE_LOGIN := 0;
224 elsif (X_MODE = 'R') then
225 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
226 if X_LAST_UPDATED_BY is NULL then
227 X_LAST_UPDATED_BY := -1;
228 end if;
229 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
230 if X_LAST_UPDATE_LOGIN is NULL then
231 X_LAST_UPDATE_LOGIN := -1;
232 end if;
233 else
234 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
235 IGS_GE_MSG_STACK.ADD;
236 app_exception.raise_exception;
237 end if;
238 Before_DML (
239 p_action =>'INSERT',
240 x_rowid => x_rowid,
241 x_s_encmb_effect_type => x_s_encmb_effect_type,
242 x_apply_to_course_ind => x_apply_to_course_ind,
243 x_closed_ind => x_closed_ind,
244 x_creation_date => X_LAST_UPDATE_date,
245 x_created_by => X_LAST_UPDATED_BY,
246 x_last_update_date => x_last_update_date,
247 x_last_updated_by => x_last_updated_by,
248 x_last_update_login => x_last_update_login
249 );
250 insert into IGS_EN_ENCMB_EFCTTYP (
251 S_ENCMB_EFFECT_TYPE,
252 APPLY_TO_COURSE_IND,
253 CLOSED_IND,
254 CREATION_DATE,
255 CREATED_BY,
256 LAST_UPDATE_DATE,
257 LAST_UPDATED_BY,
258 LAST_UPDATE_LOGIN
259 ) values (
260 new_references.S_ENCMB_EFFECT_TYPE,
261 new_references.APPLY_TO_COURSE_IND,
262 new_references.CLOSED_IND,
263 X_LAST_UPDATE_DATE,
264 X_LAST_UPDATED_BY,
265 X_LAST_UPDATE_DATE,
266 X_LAST_UPDATED_BY,
267 X_LAST_UPDATE_LOGIN
268 );
269
270 open c;
271 fetch c into X_ROWID;
272 if (c%notfound) then
273 close c;
274 raise no_data_found;
275 end if;
276 close c;
277 After_DML (
278 p_action =>'INSERT',
279 x_rowid => x_rowid );
280 end INSERT_ROW;
281
282 procedure LOCK_ROW (
283 X_ROWID in VARCHAR2,
284 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
285 X_APPLY_TO_COURSE_IND in VARCHAR2,
286 X_CLOSED_IND in VARCHAR2
287 ) AS
288 cursor c1 is select
289 APPLY_TO_COURSE_IND,
290 CLOSED_IND
291 from IGS_EN_ENCMB_EFCTTYP
292 where ROWID = X_ROWID for update nowait;
293 tlinfo c1%rowtype;
294
295 begin
296 open c1;
297 fetch c1 into tlinfo;
298 if (c1%notfound) then
299 close c1;
300 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
301 IGS_GE_MSG_STACK.ADD;
302 app_exception.raise_exception;
303 return;
304 end if;
305 close c1;
306
307 if ( (tlinfo.APPLY_TO_COURSE_IND = X_APPLY_TO_COURSE_IND)
308 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
309 ) then
310 null;
311 else
312 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
313 IGS_GE_MSG_STACK.ADD;
314 app_exception.raise_exception;
315 end if;
316 return;
317 end LOCK_ROW;
318
319 procedure UPDATE_ROW (
320 X_ROWID IN VARCHAR2,
321 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
322 X_APPLY_TO_COURSE_IND in VARCHAR2,
323 X_CLOSED_IND in VARCHAR2,
324 X_MODE in VARCHAR2 default 'R'
325 ) AS
326 X_LAST_UPDATE_DATE DATE;
327 X_LAST_UPDATED_BY NUMBER;
328 X_LAST_UPDATE_LOGIN NUMBER;
329 begin
330 X_LAST_UPDATE_DATE := SYSDATE;
331 if(X_MODE = 'I') then
332 X_LAST_UPDATED_BY := 1;
333 X_LAST_UPDATE_LOGIN := 0;
334 elsif (X_MODE = 'R') then
335 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
336 if X_LAST_UPDATED_BY is NULL then
337 X_LAST_UPDATED_BY := -1;
338 end if;
339 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
340 if X_LAST_UPDATE_LOGIN is NULL then
341 X_LAST_UPDATE_LOGIN := -1;
342 end if;
343 else
344 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
345 IGS_GE_MSG_STACK.ADD;
346 app_exception.raise_exception;
347 end if;
348
349 Before_DML (
350 p_action =>'UPDATE',
351 x_rowid => x_rowid,
352 x_s_encmb_effect_type => x_s_encmb_effect_type,
353 x_apply_to_course_ind => x_apply_to_course_ind,
354 x_closed_ind => x_closed_ind,
355 x_creation_date => X_LAST_UPDATE_date,
356 x_created_by => X_LAST_UPDATED_BY,
357 x_last_update_date => x_last_update_date,
358 x_last_updated_by => x_last_updated_by,
359 x_last_update_login => x_last_update_login
360 );
361 update IGS_EN_ENCMB_EFCTTYP set
362 APPLY_TO_COURSE_IND = X_APPLY_TO_COURSE_IND,
363 CLOSED_IND = X_CLOSED_IND,
364 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
365 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
366 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
367 where ROWID = X_ROWID;
368
369 if (sql%notfound) then
370 raise no_data_found;
371 end if;
372 After_DML (
373 p_action =>'UPDATE',
374 x_rowid => x_rowid );
375 end UPDATE_ROW;
376
377 procedure ADD_ROW (
378 X_ROWID in out NOCOPY VARCHAR2,
379 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
380 X_APPLY_TO_COURSE_IND in VARCHAR2,
381 X_CLOSED_IND in VARCHAR2,
382 X_MODE in VARCHAR2 default 'R'
383 ) AS
384 cursor c1 is select rowid from IGS_EN_ENCMB_EFCTTYP
385 where S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE;
386
387 begin
388 open c1;
389 fetch c1 into X_ROWID;
390 if (c1%notfound) then
391 close c1;
392 INSERT_ROW (
393 X_ROWID,
394 X_S_ENCMB_EFFECT_TYPE,
395 X_APPLY_TO_COURSE_IND,
396 X_CLOSED_IND,
397 X_MODE);
398 return;
399 end if;
400 close c1;
401 UPDATE_ROW (
402 X_ROWID,
403 X_S_ENCMB_EFFECT_TYPE,
404 X_APPLY_TO_COURSE_IND,
405 X_CLOSED_IND,
406 X_MODE);
407 end ADD_ROW;
408
409
410 END igs_en_encmb_efcttyp_pkg;