[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_CATEGORISE_PKG
Source
1 package body IGS_PS_CATEGORISE_PKG AS
2 /* $Header: IGSPI09B.pls 115.8 2002/11/29 01:54:36 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_CATEGORISE_ALL%RowType;
6 new_references IGS_PS_CATEGORISE_ALL%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_cat 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 x_org_id IN NUMBER DEFAULT NULL
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_PS_CATEGORISE_ALL
25 WHERE rowid = x_rowid;
26
27 BEGIN
28
29 l_rowid := x_rowid;
30
31 -- Code for setting the Old and New Reference Values.
32 -- Populate Old Values.
33 Open cur_old_ref_values;
34 Fetch cur_old_ref_values INTO old_references;
35 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
36 Close cur_old_ref_values;
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.course_cd := x_course_cd;
46 new_references.version_number := x_version_number;
47 new_references.course_cat := x_course_cat;
48 IF (p_action = 'UPDATE') THEN
49 new_references.creation_date := old_references.creation_date;
50 new_references.created_by := old_references.created_by;
51 ELSE
52 new_references.creation_date := x_creation_date;
53 new_references.created_by := x_created_by;
54 END IF;
55 new_references.last_update_date := x_last_update_date;
56 new_references.last_updated_by := x_last_updated_by;
57 new_references.last_update_login := x_last_update_login;
58 new_references.org_id := x_org_id;
59
60 END Set_Column_Values;
61
62 -- Trigger description :-
63 -- "OSS_TST".trg_crc_br_iud
64 -- BEFORE INSERT OR DELETE OR UPDATE
65 -- ON IGS_PS_CATEGORISE_ALL
66 -- FOR EACH ROW
67
68 PROCEDURE BeforeRowInsertUpdateDelete1(
69 p_inserting IN BOOLEAN DEFAULT FALSE,
70 p_updating IN BOOLEAN DEFAULT FALSE,
71 p_deleting IN BOOLEAN DEFAULT FALSE
72 ) AS
73 v_message_name varchar2(30);
74 v_course_cd IGS_PS_CATEGORISE_ALL.course_cd%TYPE;
75 v_version_number IGS_PS_CATEGORISE_ALL.version_number%TYPE;
76 BEGIN
77
78 -- Set variables.
79 IF p_deleting THEN
80 v_course_cd := old_references.course_cd;
81 v_version_number := old_references.version_number;
82 ELSE -- p_inserting or p_updating
83 v_course_cd := new_references.course_cd;
84 v_version_number := new_references.version_number;
85 END IF;
86 -- Validate the insert/update/delete.
87 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
88 v_course_cd,
89 v_version_number,
90 v_message_name) = FALSE THEN
91 Fnd_Message.Set_Name('IGS',v_message_name);
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END IF;
95 -- Validate IGS_PS_COURSE category. IGS_PS_COURSE category is not updateable.
96 IF p_inserting THEN
97 IF IGS_PS_VAL_CRC.crsp_val_crc_crs_cat (
98 new_references.course_cat,
99 v_message_name) = FALSE THEN
100 Fnd_Message.Set_Name('IGS',v_message_name);
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END IF;
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_CAT' then
119 new_references.course_cat := column_value;
120 ELSIF upper(Column_name) = 'COURSE_CD' then
121 new_references.course_cd := column_value;
122 END IF;
123
124 IF upper(column_name) = 'COURSE_CAT' OR
125 column_name is null Then
126 IF ( new_references.course_cat <> UPPER(new_references.course_cat) ) 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_CD' OR
134 column_name is null Then
135 IF ( new_references.course_cd <> UPPER(new_references.course_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_cat = new_references.course_cat)) OR
148 ((new_references.course_cat IS NULL))) THEN
149 NULL;
150 ELSE
151 IF NOT IGS_PS_CAT_PKG.Get_PK_For_Validation (
152 new_references.course_cat
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_cat IN VARCHAR2
182 )
183 RETURN BOOLEAN AS
184
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM IGS_PS_CATEGORISE_ALL
188 WHERE course_cd = x_course_cd
189 AND version_number = x_version_number
190 AND course_cat = x_course_cat
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_CAT (
210 x_course_cat IN VARCHAR2
211 ) AS
212
213 CURSOR cur_rowid IS
214 SELECT rowid
215 FROM IGS_PS_CATEGORISE_ALL
216 WHERE course_cat = x_course_cat ;
217
218 lv_rowid cur_rowid%RowType;
219
220 BEGIN
221
222 Open cur_rowid;
223 Fetch cur_rowid INTO lv_rowid;
224 IF (cur_rowid%FOUND) THEN
225 Close cur_rowid;
226 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CRC_CCA_FK');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 Return;
230 END IF;
231 Close cur_rowid;
232
233 END GET_FK_IGS_PS_CAT;
234
235 PROCEDURE GET_FK_IGS_PS_VER (
236 x_course_cd IN VARCHAR2,
237 x_version_number IN NUMBER
238 ) AS
239
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM IGS_PS_CATEGORISE_ALL
243 WHERE course_cd = x_course_cd
244 AND version_number = x_version_number ;
245
246 lv_rowid cur_rowid%RowType;
247
248 BEGIN
249
250 Open cur_rowid;
251 Fetch cur_rowid INTO lv_rowid;
252 IF (cur_rowid%FOUND) THEN
253 Close cur_rowid;
254 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CRC_CRV_FK');
255 IGS_GE_MSG_STACK.ADD;
256 App_Exception.Raise_Exception;
257 Return;
258 END IF;
259 Close cur_rowid;
260
261 END GET_FK_IGS_PS_VER;
262
263 PROCEDURE Before_DML (
264 p_action IN VARCHAR2,
265 x_rowid IN VARCHAR2 DEFAULT NULL,
266 x_course_cd IN VARCHAR2 DEFAULT NULL,
267 x_version_number IN NUMBER DEFAULT NULL,
268 x_course_cat IN VARCHAR2 DEFAULT NULL,
269 x_creation_date IN DATE DEFAULT NULL,
270 x_created_by IN NUMBER DEFAULT NULL,
271 x_last_update_date IN DATE DEFAULT NULL,
272 x_last_updated_by IN NUMBER DEFAULT NULL,
273 x_last_update_login IN NUMBER DEFAULT NULL,
274 x_org_id IN NUMBER DEFAULT NULL
275 ) AS
276 ------------------------------------------------------------------
277 --Known limitations/enhancements and/or remarks:
278 --
279 --Change History:
280 --Who When What
281 --smadathi 22-Oct-2001 Fix for Bug 2058405 . The message
282 -- IGS_GE_MULTI_ORG_DUP_RECORD is replaced
283 -- by IGS_GE_MULTI_ORG_DUP_REC
284 ------------------------------------------------------------------
285 BEGIN
286
287 Set_Column_Values (
288 p_action,
289 x_rowid,
290 x_course_cd,
291 x_version_number,
292 x_course_cat,
293 x_creation_date,
294 x_created_by,
295 x_last_update_date,
296 x_last_updated_by,
297 x_last_update_login,
298 x_org_id
299 );
300
301 IF (p_action = 'INSERT') THEN
302 -- Call all the procedures related to Before Insert.
303 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
304 IF Get_PK_For_Validation (
305 new_references.course_cd,
306 new_references.version_number,
307 new_references.course_cat ) THEN
308 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END IF;
312 Check_Constraints;
313 Check_Parent_Existance;
314 ELSIF (p_action = 'UPDATE') THEN
315 -- Call all the procedures related to Before Update.
316 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
317 Check_Constraints;
318 Check_Parent_Existance;
319 ELSIF (p_action = 'DELETE') THEN
320 -- Call all the procedures related to Before Delete.
321 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
322 ELSIF (p_action = 'VALIDATE_INSERT') THEN
323 IF Get_PK_For_Validation (
324 new_references.course_cd,
325 new_references.version_number,
326 new_references.course_cat ) THEN
327 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 END IF;
331 Check_Constraints;
332 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
333 Check_Constraints;
334 END IF;
335
336 END Before_DML;
337
338 PROCEDURE After_DML (
339 p_action IN VARCHAR2,
340 x_rowid IN VARCHAR2
341 ) AS
342 BEGIN
343
344 l_rowid := x_rowid;
345
346 END After_DML;
347
348 procedure INSERT_ROW (
349 X_ROWID in out NOCOPY VARCHAR2,
350 X_COURSE_CD in VARCHAR2,
351 X_VERSION_NUMBER in NUMBER,
352 X_COURSE_CAT in VARCHAR2,
353 X_MODE in VARCHAR2 default 'R',
354 X_ORG_ID in NUMBER
355 ) AS
356 cursor C is select ROWID from IGS_PS_CATEGORISE_ALL
357 where COURSE_CD = X_COURSE_CD
358 and VERSION_NUMBER = X_VERSION_NUMBER
359 and COURSE_CAT = X_COURSE_CAT;
360 X_LAST_UPDATE_DATE DATE;
361 X_LAST_UPDATED_BY NUMBER;
362 X_LAST_UPDATE_LOGIN NUMBER;
363 begin
364 X_LAST_UPDATE_DATE := SYSDATE;
365 if(X_MODE = 'I') then
366 X_LAST_UPDATED_BY := 1;
367 X_LAST_UPDATE_LOGIN := 0;
368 elsif (X_MODE = 'R') then
369 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
370 if X_LAST_UPDATED_BY is NULL then
371 X_LAST_UPDATED_BY := -1;
372 end if;
373 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
374 if X_LAST_UPDATE_LOGIN is NULL then
375 X_LAST_UPDATE_LOGIN := -1;
376 end if;
377 else
378 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
379 IGS_GE_MSG_STACK.ADD;
380 app_exception.raise_exception;
381 end if;
382
383 Before_DML (
384 p_action => 'INSERT',
385 x_rowid => X_ROWID,
386 x_course_cd => X_COURSE_CD,
387 x_version_number => X_VERSION_NUMBER,
388 x_course_cat => X_COURSE_CAT,
389 x_creation_date => X_LAST_UPDATE_DATE ,
390 x_created_by => X_LAST_UPDATED_BY ,
391 x_last_update_date => X_LAST_UPDATE_DATE ,
392 x_last_updated_by => X_LAST_UPDATED_BY ,
393 x_last_update_login => X_LAST_UPDATE_LOGIN,
394 x_org_id => igs_ge_gen_003.get_org_id
395 );
396
397 insert into IGS_PS_CATEGORISE_ALL (
398 COURSE_CD,
399 VERSION_NUMBER,
400 COURSE_CAT,
401 CREATION_DATE,
402 CREATED_BY,
403 LAST_UPDATE_DATE,
404 LAST_UPDATED_BY,
405 LAST_UPDATE_LOGIN,
406 ORG_ID
407 ) values (
408 NEW_REFERENCES.COURSE_CD,
409 NEW_REFERENCES.VERSION_NUMBER,
410 NEW_REFERENCES.COURSE_CAT,
411 X_LAST_UPDATE_DATE,
412 X_LAST_UPDATED_BY,
413 X_LAST_UPDATE_DATE,
414 X_LAST_UPDATED_BY,
415 X_LAST_UPDATE_LOGIN,
416 NEW_REFERENCES.ORG_ID
417 );
418
419 open c;
420 fetch c into X_ROWID;
421 if (c%notfound) then
422 close c;
423 raise no_data_found;
424 end if;
425 close c;
426
427 After_DML (
428 p_action => 'INSERT',
429 x_rowid => X_ROWID
430 );
431
432 end INSERT_ROW;
433
434 procedure LOCK_ROW (
435 X_ROWID IN VARCHAR2,
436 X_COURSE_CD in VARCHAR2,
437 X_VERSION_NUMBER in NUMBER,
438 X_COURSE_CAT in VARCHAR2
439
440 ) AS
441 cursor c1 is select ROWID
442 from IGS_PS_CATEGORISE_ALL
443 where ROWID = X_ROWID
444 for update nowait;
445 tlinfo c1%rowtype;
446
447 begin
448 open c1;
449 fetch c1 into tlinfo;
450 if (c1%notfound) then
451 close c1;
452 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
453 IGS_GE_MSG_STACK.ADD;
454 app_exception.raise_exception;
455 return;
456 end if;
457 close c1;
458
459 return;
460
461 end LOCK_ROW;
462
463 procedure DELETE_ROW (
464 X_ROWID in VARCHAR2
465 ) AS
466 begin
467
468 Before_DML (
469 p_action => 'DELETE',
470 x_rowid => X_ROWID
471 );
472
473 delete from IGS_PS_CATEGORISE_ALL
474 where ROWID = X_ROWID;
475 if (sql%notfound) then
476 raise no_data_found;
477 end if;
478
479 After_DML (
480 p_action => 'DELETE',
481 x_rowid => X_ROWID
482 );
483
484 end DELETE_ROW;
485
486 end IGS_PS_CATEGORISE_PKG;