[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_GRP_MBR_PKG
Source
1 package body IGS_PS_GRP_MBR_PKG AS
2 /* $Header: IGSPI17B.pls 115.4 2003/02/20 10:34:13 shtatiko ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_GRP_MBR%RowType;
6 new_references IGS_PS_GRP_MBR%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_course_group_cd 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_PS_GRP_MBR
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.course_cd := x_course_cd;
45 new_references.version_number := x_version_number;
46 new_references.course_group_cd := x_course_group_cd;
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 -- Trigger description :-
61 -- "OSS_TST".trg_cgm_br_iud
62 -- BEFORE INSERT OR DELETE OR UPDATE
63 -- ON IGS_PS_GRP_MBR
64 -- FOR EACH ROW
65
66 PROCEDURE BeforeRowInsertUpdateDelete1(
67 p_inserting IN BOOLEAN DEFAULT FALSE,
68 p_updating IN BOOLEAN DEFAULT FALSE,
69 p_deleting IN BOOLEAN DEFAULT FALSE
70 ) AS
71 v_message_name varchar2(30);
72 v_course_cd IGS_PS_GRP_MBR.course_cd%TYPE;
73 v_version_number IGS_PS_GRP_MBR.version_number%TYPE;
74 v_course_group_cd IGS_PS_GRP_MBR.course_group_cd%TYPE;
75 BEGIN
76
77 -- Set variables.
78 IF p_deleting THEN
79 v_course_cd := old_references.course_cd;
80 v_version_number := old_references.version_number;
81 v_course_group_cd := old_references.course_group_cd;
82 ELSE -- p_inserting or p_updating
83 v_course_cd := new_references.course_cd;
84 v_version_number := new_references.version_number;
85 v_course_group_cd := new_references.course_group_cd;
86 END IF;
87 -- Validate the insert/update/delete.
88 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
89 v_course_cd,
90 v_version_number,
91 v_message_name) = FALSE THEN
92 Fnd_Message.Set_Name('IGS',v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END IF;
96 -- Validate IGS_PS_COURSE group code. IGS_PS_COURSE group code is not updateable.
97 -- Validation is intentionally done on the delete.
98 IF IGS_PS_VAL_CGM.crsp_val_cgm_crs_grp (
99 v_course_group_cd,
100 v_message_name) = FALSE THEN
101 Fnd_Message.Set_Name('IGS',v_message_name);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105
106
107 END BeforeRowInsertUpdateDelete1;
108
109 PROCEDURE Check_Constraints (
110 Column_Name IN VARCHAR2 DEFAULT NULL,
111 Column_Value IN VARCHAR2 DEFAULT NULL
112 )
113 AS
114 BEGIN
115
116 IF column_name is null then
117 NULL;
118 ELSIF upper(Column_name) = 'COURSE_CD' then
119 new_references.course_cd := column_value;
120 ELSIF upper(Column_name) = 'COURSE_GROUP_CD' then
121 new_references.course_group_cd := column_value;
122 END IF;
123
124 IF upper(column_name) = 'COURSE_CD' OR
125 column_name is null Then
126 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
127 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131 END IF;
132
133 IF upper(column_name) = 'COURSE_GROUP_CD' OR
134 column_name is null Then
135 IF ( new_references.course_group_cd <> UPPER(new_references.course_group_cd) ) Then
136 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF;
140 END IF;
141
142 END Check_Constraints;
143
144 PROCEDURE Check_Parent_Existance AS
145 BEGIN
146
147 IF (((old_references.course_group_cd = new_references.course_group_cd)) OR
148 ((new_references.course_group_cd IS NULL))) THEN
149 NULL;
150 ELSE
151 IF NOT IGS_PS_GRP_PKG.Get_PK_For_Validation (
152 new_references.course_group_cd
153 ) THEN
154 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159
160 IF (((old_references.course_cd = new_references.course_cd) AND
161 (old_references.version_number = new_references.version_number)) OR
162 ((new_references.course_cd IS NULL) OR
163 (new_references.version_number IS NULL))) THEN
164 NULL;
165 ELSE
166 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
167 new_references.course_cd,
168 new_references.version_number
169 ) THEN
170 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175
176 END Check_Parent_Existance;
177
178 FUNCTION Get_PK_For_Validation (
179 x_course_cd IN VARCHAR2,
180 x_version_number IN NUMBER,
181 x_course_group_cd IN VARCHAR2
182 )
183 RETURN BOOLEAN AS
184
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM IGS_PS_GRP_MBR
188 WHERE course_cd = x_course_cd
189 AND version_number = x_version_number
190 AND course_group_cd = x_course_group_cd
191 FOR UPDATE NOWAIT;
192
193 lv_rowid cur_rowid%RowType;
194
195 BEGIN
196
197 Open cur_rowid;
198 Fetch cur_rowid INTO lv_rowid;
199 IF (cur_rowid%FOUND) THEN
200 Close cur_rowid;
201 Return (TRUE);
202 ELSE
203 Close cur_rowid;
204 Return (FALSE);
205 END IF;
206
207 END Get_PK_For_Validation;
208
209 PROCEDURE GET_FK_IGS_PS_VER (
210 x_course_cd IN VARCHAR2,
211 x_version_number IN NUMBER
212 ) AS
213
214 CURSOR cur_rowid IS
215 SELECT rowid
216 FROM IGS_PS_GRP_MBR
217 WHERE course_cd = x_course_cd
218 AND version_number = x_version_number ;
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 Open cur_rowid;
225 Fetch cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 Close cur_rowid;
228 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CGM_CRV_FK');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 Return;
232 END IF;
233 Close cur_rowid;
234
235 END GET_FK_IGS_PS_VER;
236
237 PROCEDURE Before_DML (
238 p_action IN VARCHAR2,
239 x_rowid IN VARCHAR2 DEFAULT NULL,
240 x_course_cd IN VARCHAR2 DEFAULT NULL,
241 x_version_number IN NUMBER DEFAULT NULL,
242 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
243 x_creation_date IN DATE DEFAULT NULL,
244 x_created_by IN NUMBER DEFAULT NULL,
245 x_last_update_date IN DATE DEFAULT NULL,
246 x_last_updated_by IN NUMBER DEFAULT NULL,
247 x_last_update_login IN NUMBER DEFAULT NULL
248 ) AS
249 BEGIN
250
251 Set_Column_Values (
252 p_action,
253 x_rowid,
254 x_course_cd,
255 x_version_number,
256 x_course_group_cd,
257 x_creation_date,
258 x_created_by,
259 x_last_update_date,
260 x_last_updated_by,
261 x_last_update_login
262 );
263
264 IF (p_action = 'INSERT') THEN
265 -- Call all the procedures related to Before Insert.
266 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
267 IF Get_PK_For_Validation (
268 new_references.course_cd,
269 new_references.version_number,
270 new_references.course_group_cd) THEN
271 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275 Check_Constraints;
276 Check_Parent_Existance;
277 ELSIF (p_action = 'UPDATE') THEN
278 -- Call all the procedures related to Before Update.
279 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
280 Check_Constraints;
281 Check_Parent_Existance;
282 ELSIF (p_action = 'DELETE') THEN
283 -- Call all the procedures related to Before Delete.
284 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
285 ELSIF (p_action = 'VALIDATE_INSERT') THEN
286 IF Get_PK_For_Validation (
287 new_references.course_cd,
288 new_references.version_number,
289 new_references.course_group_cd) THEN
290 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 END IF;
294 Check_Constraints;
295 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
296 Check_Constraints;
297 END IF;
298
299 END Before_DML;
300
301 PROCEDURE After_DML (
302 p_action IN VARCHAR2,
303 x_rowid IN VARCHAR2
304 ) AS
305 BEGIN
306
307 l_rowid := x_rowid;
308
309
310 END After_DML;
311
312 procedure INSERT_ROW (
313 X_ROWID in out NOCOPY VARCHAR2,
314 X_COURSE_CD in VARCHAR2,
315 X_COURSE_GROUP_CD in VARCHAR2,
316 X_VERSION_NUMBER in NUMBER,
317 X_MODE in VARCHAR2 default 'R'
318 ) AS
319 cursor C is select ROWID from IGS_PS_GRP_MBR
320 where COURSE_CD = X_COURSE_CD
321 and COURSE_GROUP_CD = X_COURSE_GROUP_CD
322 and VERSION_NUMBER = X_VERSION_NUMBER;
323 X_LAST_UPDATE_DATE DATE;
324 X_LAST_UPDATED_BY NUMBER;
325 X_LAST_UPDATE_LOGIN NUMBER;
326 begin
327 X_LAST_UPDATE_DATE := SYSDATE;
328 if(X_MODE = 'I') then
329 X_LAST_UPDATED_BY := 1;
330 X_LAST_UPDATE_LOGIN := 0;
331 elsif (X_MODE = 'R') then
332 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
333 if X_LAST_UPDATED_BY is NULL then
334 X_LAST_UPDATED_BY := -1;
335 end if;
336 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
337 if X_LAST_UPDATE_LOGIN is NULL then
338 X_LAST_UPDATE_LOGIN := -1;
339 end if;
340 else
341 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
342 IGS_GE_MSG_STACK.ADD;
343 app_exception.raise_exception;
344 end if;
345
346 Before_DML (
347 p_action => 'INSERT',
348 x_rowid => X_ROWID,
349 x_course_cd => X_COURSE_CD,
350 x_version_number => X_VERSION_NUMBER,
351 x_course_group_cd => X_COURSE_GROUP_CD,
352 x_creation_date => X_LAST_UPDATE_DATE ,
353 x_created_by => X_LAST_UPDATED_BY ,
354 x_last_update_date => X_LAST_UPDATE_DATE ,
355 x_last_updated_by => X_LAST_UPDATED_BY ,
356 x_last_update_login => X_LAST_UPDATE_LOGIN
357 );
358
359 insert into IGS_PS_GRP_MBR (
360 COURSE_CD,
361 VERSION_NUMBER,
362 COURSE_GROUP_CD,
363 CREATION_DATE,
364 CREATED_BY,
365 LAST_UPDATE_DATE,
366 LAST_UPDATED_BY,
367 LAST_UPDATE_LOGIN
368 ) values (
369 NEW_REFERENCES.COURSE_CD,
370 NEW_REFERENCES.VERSION_NUMBER,
371 NEW_REFERENCES.COURSE_GROUP_CD,
372 X_LAST_UPDATE_DATE,
373 X_LAST_UPDATED_BY,
374 X_LAST_UPDATE_DATE,
375 X_LAST_UPDATED_BY,
376 X_LAST_UPDATE_LOGIN
377 );
378
379 open c;
380 fetch c into X_ROWID;
381 if (c%notfound) then
382 close c;
383 raise no_data_found;
384 end if;
385 close c;
386 After_DML (
387 p_action => 'INSERT',
388 x_rowid => X_ROWID
389 );
390 end INSERT_ROW;
391
392 procedure LOCK_ROW (
393 X_ROWID IN VARCHAR2,
394 X_COURSE_CD in VARCHAR2,
395 X_COURSE_GROUP_CD in VARCHAR2,
396 X_VERSION_NUMBER in NUMBER
397 ) AS
398 cursor c1 is select ROWID
399 from IGS_PS_GRP_MBR
400 where ROWID = X_ROWID
401 for update nowait;
402 tlinfo c1%rowtype;
403
404 begin
405 open c1;
406 fetch c1 into tlinfo;
407 if (c1%notfound) then
408 close c1;
412 return;
409 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
410 IGS_GE_MSG_STACK.ADD;
411 app_exception.raise_exception;
413 end if;
414 close c1;
415
416 return;
417 end LOCK_ROW;
418
419 procedure DELETE_ROW (
420 X_ROWID in VARCHAR2
421 ) AS
422 begin
423 Before_DML (
424 p_action => 'DELETE',
425 x_rowid => X_ROWID
426 );
427 delete from IGS_PS_GRP_MBR
428 where ROWID = X_ROWID;
429 if (sql%notfound) then
430 raise no_data_found;
431 end if;
432 After_DML (
433 p_action => 'DELETE',
434 x_rowid => X_ROWID
435 );
436 end DELETE_ROW;
437
438 end IGS_PS_GRP_MBR_PKG;