[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_UNITSETPSTYPE_PKG
Source
1 package body IGS_EN_UNITSETPSTYPE_PKG AS
2 /* $Header: IGSEI03B.pls 115.5 2003/06/05 13:03:36 sarakshi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_UNITSETPSTYPE%RowType;
6 new_references IGS_EN_UNITSETPSTYPE%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_course_type 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_UNITSETPSTYPE
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36 IGS_GE_MSG_STACK.ADD;
37 Close cur_old_ref_values;
38 App_Exception.Raise_Exception;
39 Return;
40 END IF;
41 Close cur_old_ref_values;
42
43 -- Populate New Values.
44 new_references.unit_set_cd := x_unit_set_cd;
45 new_references.version_number := x_version_number;
46 new_references.course_type := x_course_type;
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_usct_br_iud
62 -- BEFORE INSERT OR DELETE OR UPDATE
63 -- ON IGS_EN_UNITSETPSTYPE
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_unit_set_cd IGS_EN_UNITSETPSTYPE.unit_set_cd%TYPE;
72 v_version_number IGS_EN_UNITSETPSTYPE.version_number%TYPE;
73 v_message_name varchar2(30);
74 BEGIN
75 -- Set variables.
76 IF p_deleting THEN
77 v_unit_set_cd := old_references.unit_set_cd;
78 v_version_number := old_references.version_number;
79 ELSE -- p_inserting or p_updating
80 v_unit_set_cd := new_references.unit_set_cd;
81 v_version_number := new_references.version_number;
82 END IF;
83 -- <usct1>
84 -- Can not alter details when UNIT set is INACTIVE
85 IF IGS_PS_VAL_COUSR.crsp_val_iud_us_dtl (
86 v_unit_set_cd,
87 v_version_number,
88 v_message_name) = FALSE THEN
89 Fnd_Message.Set_Name('IGS', v_message_name);
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception;
92 END IF;
93 IF p_inserting OR p_updating THEN
94 -- <usct2>
95 -- Can not alter details when COURSE type is closed
96 IF IGS_as_VAL_acot.crsp_val_cty_closed (
97 new_references.course_type,
98 v_message_name) = FALSE THEN
99 Fnd_Message.Set_Name('IGS', v_message_name);
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END IF;
103 END IF;
104
105
106 END BeforeRowInsertUpdateDelete1;
107
108 PROCEDURE Check_Constraints (
109 Column_Name IN VARCHAR2 DEFAULT NULL,
110 Column_Value IN VARCHAR2 DEFAULT NULL
111 ) as
112
113 BEGIN
114
115 -- The following code checks for check constraints on the Columns.
116
117 IF column_name is NULL THEN
118 NULL;
119 ELSIF UPPER(column_name) = 'COURSE_TYPE' THEN
120 new_references.course_type := column_value;
121 ELSIF UPPER(column_name) = 'UNIT_SET_CD' THEN
122 new_references.unit_set_cd := column_value;
123 END IF;
124
125
126 IF ((UPPER (column_name) = 'COURSE_TYPE') OR (column_name IS NULL)) THEN
127 IF (new_references.course_type <> UPPER (new_references.course_type)) THEN
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
129 IGS_GE_MSG_STACK.ADD;
130 App_Exception.Raise_Exception;
131 END IF;
132 END IF;
133
134 IF ((UPPER (column_name) = 'UNIT_SET_CD') OR (column_name IS NULL)) THEN
135 IF (new_references.unit_set_cd <> UPPER (new_references.unit_set_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
143 END Check_Constraints;
144
145
146 PROCEDURE Check_Parent_Existance AS
147 BEGIN
148
149 IF (((old_references.course_type = new_references.course_type)) OR
150 ((new_references.course_type IS NULL))) THEN
151 NULL;
152 ELSE
153 IF NOT IGS_PS_TYPE_PKG.Get_PK_For_Validation (
154 new_references.course_type
155 ) THEN
156 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159
160 END IF;
161
162 END IF;
163
164 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
165 (old_references.version_number = new_references.version_number)) OR
166 ((new_references.unit_set_cd IS NULL) OR
167 (new_references.version_number IS NULL))) THEN
168 NULL;
169 ELSE
170 IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
171 new_references.unit_set_cd,
172 new_references.version_number
173 ) THEN
174
175 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178
179 END IF;
180
181 END IF;
182
183 END Check_Parent_Existance;
184
185 FUNCTION Get_PK_For_Validation (
186 x_unit_set_cd IN VARCHAR2,
187 x_version_number IN NUMBER,
188 x_course_type IN VARCHAR2
189 ) RETURN BOOLEAN AS
190
191 CURSOR cur_rowid IS
192 SELECT rowid
193 FROM IGS_EN_UNITSETPSTYPE
194 WHERE unit_set_cd = x_unit_set_cd
195 AND version_number = x_version_number
196 AND course_type = x_course_type
197 FOR UPDATE NOWAIT;
198
199 lv_rowid cur_rowid%RowType;
200
201 BEGIN
202
203 Open cur_rowid;
204 Fetch cur_rowid INTO lv_rowid;
205
206 IF (cur_rowid%FOUND) THEN
207 Close cur_rowid;
208 Return(TRUE);
209 ELSE
210 Close cur_rowid;
211 Return(FALSE);
212 END IF;
213
214
215 END Get_PK_For_Validation;
216
217
218 PROCEDURE GET_FK_IGS_EN_UNIT_SET (
219 x_unit_set_cd IN VARCHAR2,
220 x_version_number IN NUMBER
221 ) AS
222
223 CURSOR cur_rowid IS
224 SELECT rowid
225 FROM IGS_EN_UNITSETPSTYPE
226 WHERE unit_set_cd = x_unit_set_cd
227 AND version_number = x_version_number ;
228
229 lv_rowid cur_rowid%RowType;
230
231 BEGIN
232
233 Open cur_rowid;
234 Fetch cur_rowid INTO lv_rowid;
235 IF (cur_rowid%FOUND) THEN
236 Fnd_Message.Set_Name ('IGS', 'IGS_EN_USCT_US_FK');
237 IGS_GE_MSG_STACK.ADD;
238 Close cur_rowid;
239 App_Exception.Raise_Exception;
240 Return;
241 END IF;
242 Close cur_rowid;
243
244 END GET_FK_IGS_EN_UNIT_SET;
245
246
247 PROCEDURE Before_DML (
248 p_action IN VARCHAR2,
249 x_rowid IN VARCHAR2 DEFAULT NULL,
250 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
251 x_version_number IN NUMBER DEFAULT NULL,
252 x_course_type IN VARCHAR2 DEFAULT NULL,
253 x_creation_date IN DATE DEFAULT NULL,
254 x_created_by IN NUMBER DEFAULT NULL,
255 x_last_update_date IN DATE DEFAULT NULL,
256 x_last_updated_by IN NUMBER DEFAULT NULL,
257 x_last_update_login IN NUMBER DEFAULT NULL
258 ) AS
259 BEGIN
260
261 Set_Column_Values (
262 p_action,
263 x_rowid,
264 x_unit_set_cd,
265 x_version_number,
266 x_course_type,
267 x_creation_date,
268 x_created_by,
269 x_last_update_date,
270 x_last_updated_by,
271 x_last_update_login
272 );
273
274 IF (p_action = 'INSERT') THEN
275 -- Call all the procedures related to Before Insert.
276 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
277 IF Get_PK_For_Validation(
278 new_references.unit_set_cd,
279 new_references.version_number,
280 new_references.course_type
281 ) THEN
282
283 Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286
287 END IF;
288
289 Check_Constraints;
290 Check_Parent_Existance;
291 ELSIF (p_action = 'UPDATE') THEN
292 -- Call all the procedures related to Before Update.
293 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
294 Check_Constraints;
295 Check_Parent_Existance;
296 ELSIF (p_action = 'DELETE') THEN
297 -- Call all the procedures related to Before Delete.
298 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
299 ELSIF (p_action = 'VALIDATE_INSERT') THEN
300 IF Get_PK_For_Validation (
301 new_references.unit_set_cd,
302 new_references.version_number,
303 new_references.course_type
304 ) THEN
305 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
306 IGS_GE_MSG_STACK.ADD;
307 App_Exception.Raise_Exception;
308 END IF;
309 Check_Constraints;
310 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
311 Check_Constraints;
312 ELSIF (p_action = 'VALIDATE_DELETE') THEN
313 null;
314 END IF;
315
316
317 END Before_DML;
318
319 PROCEDURE After_DML (
320 p_action IN VARCHAR2,
321 x_rowid IN VARCHAR2
322 ) AS
323 BEGIN
324
325 l_rowid := x_rowid;
326
327 IF (p_action = 'INSERT') THEN
328 -- Call all the procedures related to After Insert.
329 Null;
330 ELSIF (p_action = 'UPDATE') THEN
331 -- Call all the procedures related to After Update.
332 Null;
333 ELSIF (p_action = 'DELETE') THEN
334 -- Call all the procedures related to After Delete.
335 Null;
336 END IF;
337
338 END After_DML;
339
340
341 procedure INSERT_ROW (
342 X_ROWID in out NOCOPY VARCHAR2,
343 X_UNIT_SET_CD in VARCHAR2,
344 X_VERSION_NUMBER in NUMBER,
345 X_COURSE_TYPE in VARCHAR2,
346 X_MODE in VARCHAR2 default 'R'
347 ) AS
348 cursor C is select ROWID from IGS_EN_UNITSETPSTYPE
349 where UNIT_SET_CD = X_UNIT_SET_CD
350 and VERSION_NUMBER = X_VERSION_NUMBER
351 and COURSE_TYPE = X_COURSE_TYPE;
352 X_LAST_UPDATE_DATE DATE;
353 X_LAST_UPDATED_BY NUMBER;
354 X_LAST_UPDATE_LOGIN NUMBER;
355 begin
356 X_LAST_UPDATE_DATE := SYSDATE;
357 if(X_MODE = 'I') then
358 X_LAST_UPDATED_BY := 1;
359 X_LAST_UPDATE_LOGIN := 0;
360 elsif (X_MODE = 'R') then
361 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
362 if X_LAST_UPDATED_BY is NULL then
363 X_LAST_UPDATED_BY := -1;
364 end if;
365 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
366 if X_LAST_UPDATE_LOGIN is NULL then
367 X_LAST_UPDATE_LOGIN := -1;
368 end if;
369 else
370 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
371 IGS_GE_MSG_STACK.ADD;
372 app_exception.raise_exception;
373 end if;
374
375 Before_DML(
376 p_action => 'INSERT' ,
377 x_rowid => x_rowid ,
378 x_unit_set_cd => x_unit_set_cd ,
379 x_version_number => x_version_number ,
380 x_course_type => x_course_type ,
381 x_creation_date => x_last_update_date ,
382 x_created_by => x_last_updated_by ,
383 x_last_update_date => x_last_update_date ,
384 x_last_updated_by => x_last_updated_by ,
385 x_last_update_login => x_last_updated_by
386 );
387
388 insert into IGS_EN_UNITSETPSTYPE (
389 UNIT_SET_CD,
390 VERSION_NUMBER,
391 COURSE_TYPE,
392 CREATION_DATE,
393 CREATED_BY,
394 LAST_UPDATE_DATE,
395 LAST_UPDATED_BY,
396 LAST_UPDATE_LOGIN
397 ) values (
398 NEW_REFERENCES.UNIT_SET_CD,
399 NEW_REFERENCES.VERSION_NUMBER,
400 NEW_REFERENCES.COURSE_TYPE,
401 X_LAST_UPDATE_DATE,
402 X_LAST_UPDATED_BY,
403 X_LAST_UPDATE_DATE,
404 X_LAST_UPDATED_BY,
405 X_LAST_UPDATE_LOGIN
406 );
407 open c;
408 fetch c into X_ROWID;
409 if (c%notfound) then
410 close c;
411 raise no_data_found;
412 end if;
413 close c;
414
415
416 After_DML(
417 p_action => 'INSERT',
418 x_rowid => X_ROWID
419 );
420
421 end INSERT_ROW;
422
423 procedure LOCK_ROW (
424 X_ROWID IN VARCHAR2,
425 X_UNIT_SET_CD in VARCHAR2,
426 X_VERSION_NUMBER in NUMBER,
427 X_COURSE_TYPE in VARCHAR2
428 ) AS
429 cursor c1 is select
430 ROWID
431 from IGS_EN_UNITSETPSTYPE
432 where ROWID = X_ROWID
433 for update nowait;
434 tlinfo c1%rowtype;
435
436 begin
437 open c1;
438 fetch c1 into tlinfo;
439 if (c1%notfound) then
440 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
441 IGS_GE_MSG_STACK.ADD;
442 close c1;
443 app_exception.raise_exception;
444 return;
445 end if;
446 close c1;
447 return;
448 end LOCK_ROW;
449
450 procedure DELETE_ROW (
451 X_ROWID IN VARCHAR2
452 ) AS
453 begin
454
455 Before_DML(
456 p_action => 'DELETE',
457 x_rowid => X_ROWID
458 );
459 delete from IGS_EN_UNITSETPSTYPE
460 where ROWID = X_ROWID;
461 if (sql%notfound) then
462 raise no_data_found;
463 end if;
464
465 After_DML(
466 p_action => 'DELETE',
467 x_rowid => X_ROWID
468 );
469
470 end DELETE_ROW;
471
472 end IGS_EN_UNITSETPSTYPE_PKG;