[Home] [Help]
PACKAGE BODY: APPS.IGS_OR_REL_PS_TYPE_PKG
Source
1 package body IGS_OR_REL_PS_TYPE_PKG AS
2 /* $Header: IGSOI15B.pls 115.6 2003/06/05 13:01:56 sarakshi ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_OR_REL_PS_TYPE%RowType;
5 new_references IGS_OR_REL_PS_TYPE%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_parent_org_unit_cd IN VARCHAR2 DEFAULT NULL,
11 x_parent_start_dt IN DATE DEFAULT NULL,
12 x_child_org_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_child_start_dt IN DATE DEFAULT NULL,
14 x_our_create_dt IN DATE DEFAULT NULL,
15 x_course_type IN VARCHAR2 DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_OR_REL_PS_TYPE
25 WHERE rowid = x_rowid;
26 BEGIN
27 l_rowid := x_rowid;
28 -- Code for setting the Old and New Reference Values.
29 -- Populate Old Values.
30 Open cur_old_ref_values;
31 Fetch cur_old_ref_values INTO old_references;
32 IF (cur_old_ref_values%NOTFOUND) AND (p_action <> 'INSERT') THEN
33 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
34 IGS_GE_MSG_STACK.ADD;
35 App_Exception.Raise_Exception;
36 Close cur_old_ref_values;
37 Return;
38 END IF;
39 Close cur_old_ref_values;
40 -- Populate New Values.
41 new_references.parent_org_unit_cd := x_parent_org_unit_cd;
42 new_references.parent_start_dt := x_parent_start_dt;
43 new_references.child_org_unit_cd := x_child_org_unit_cd;
44 new_references.child_start_dt := x_child_start_dt;
45 new_references.our_create_dt := x_our_create_dt;
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 END Set_Column_Values;
58
59 PROCEDURE Check_Parent_Existance AS
60 BEGIN
61 IF (((old_references.course_type = new_references.course_type)) OR
62 ((new_references.course_type IS NULL))) THEN
63 NULL;
64 ELSE
65 IF NOT IGS_PS_TYPE_PKG.Get_PK_For_Validation (
66 new_references.course_type
67 )THEN
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69 IGS_GE_MSG_STACK.ADD;
70 App_Exception.Raise_Exception;
71
72 END IF;
73 END IF;
74 IF (((old_references.parent_org_unit_cd = new_references.parent_org_unit_cd) AND
75 (old_references.parent_start_dt = new_references.parent_start_dt) AND
76 (old_references.child_org_unit_cd = new_references.child_org_unit_cd) AND
77 (old_references.child_start_dt = new_references.child_start_dt) AND
78 (old_references.our_create_dt = new_references.our_create_dt)) OR
79 ((new_references.parent_org_unit_cd IS NULL) OR
80 (new_references.parent_start_dt IS NULL) OR
81 (new_references.child_org_unit_cd IS NULL) OR
82 (new_references.child_start_dt IS NULL) OR
83 (new_references.our_create_dt IS NULL))) THEN
84 NULL;
85 ELSE
86 IF NOT IGS_OR_UNIT_REL_PKG.Get_PK_For_Validation (
87 new_references.parent_org_unit_cd,
88 new_references.parent_start_dt,
89 new_references.child_org_unit_cd,
90 new_references.child_start_dt,
91 new_references.our_create_dt
92 )THEN
93 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96
97 END IF;
98 END IF;
99 END Check_Parent_Existance;
100
101 FUNCTION Get_PK_For_Validation (
102 x_parent_org_unit_cd IN VARCHAR2,
103 x_parent_start_dt IN DATE,
104 x_child_org_unit_cd IN VARCHAR2,
105 x_child_start_dt IN DATE,
106 x_our_create_dt IN DATE,
107 x_course_type IN VARCHAR2
108 )RETURN BOOLEAN AS
109 CURSOR cur_rowid IS
110 SELECT rowid
111 FROM IGS_OR_REL_PS_TYPE
112 WHERE parent_org_unit_cd = x_parent_org_unit_cd
113 AND parent_start_dt = x_parent_start_dt
114 AND child_org_unit_cd = x_child_org_unit_cd
115 AND child_start_dt = x_child_start_dt
116 AND our_create_dt = x_our_create_dt
117 AND course_type = x_course_type
118 FOR UPDATE NOWAIT;
119 lv_rowid cur_rowid%RowType;
120 BEGIN
121 Open cur_rowid;
122 Fetch cur_rowid INTO lv_rowid;
123 IF (cur_rowid%FOUND) THEN
124 Close cur_rowid;
125 RETURN(TRUE);
126 ELSE
127 Close cur_rowid;
128 RETURN(FALSE);
129 END IF;
130
131 END Get_PK_For_Validation;
132
133 PROCEDURE GET_FK_IGS_OR_UNIT_REL (
134 x_parent_org_unit_cd IN VARCHAR2,
135 x_parent_start_dt IN DATE,
136 x_child_org_unit_cd IN VARCHAR2,
137 x_child_start_dt IN DATE,
138 x_create_dt IN DATE
139 ) AS
140 CURSOR cur_rowid IS
141 SELECT rowid
142 FROM IGS_OR_REL_PS_TYPE
143 WHERE parent_org_unit_cd = x_parent_org_unit_cd
144 AND parent_start_dt = x_parent_start_dt
145 AND child_org_unit_cd = x_child_org_unit_cd
146 AND child_start_dt = x_child_start_dt
147 AND our_create_dt = x_create_dt ;
148 lv_rowid cur_rowid%RowType;
149 BEGIN
150 Open cur_rowid;
151 Fetch cur_rowid INTO lv_rowid;
152 IF (cur_rowid%FOUND) THEN
153 Close cur_rowid;
154 Fnd_Message.Set_Name ('IGS', 'IGS_OR_OURCT_OUR_FK');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157
158 Return;
159 END IF;
160 Close cur_rowid;
161 END GET_FK_IGS_OR_UNIT_REL;
162
163 PROCEDURE Before_DML (
164 p_action IN VARCHAR2,
165 x_rowid IN VARCHAR2 DEFAULT NULL,
166 x_parent_org_unit_cd IN VARCHAR2 DEFAULT NULL,
167 x_parent_start_dt IN DATE DEFAULT NULL,
168 x_child_org_unit_cd IN VARCHAR2 DEFAULT NULL,
169 x_child_start_dt IN DATE DEFAULT NULL,
170 x_our_create_dt IN DATE DEFAULT NULL,
171 x_course_type IN VARCHAR2 DEFAULT NULL,
172 x_creation_date IN DATE DEFAULT NULL,
173 x_created_by IN NUMBER DEFAULT NULL,
174 x_last_update_date IN DATE DEFAULT NULL,
175 x_last_updated_by IN NUMBER DEFAULT NULL,
176 x_last_update_login IN NUMBER DEFAULT NULL
177 ) AS
178 BEGIN
179 Set_Column_Values (
180 p_action,
181 x_rowid,
182 x_parent_org_unit_cd,
183 x_parent_start_dt,
184 x_child_org_unit_cd,
185 x_child_start_dt,
186 x_our_create_dt,
187 x_course_type,
188 x_creation_date,
189 x_created_by,
190 x_last_update_date,
191 x_last_updated_by,
192 x_last_update_login
193 );
194 IF (p_action = 'INSERT') THEN
195 -- Call all the procedures related to Before Insert.
196
197 IF Get_PK_For_Validation (
198 new_references.parent_org_unit_cd ,
199 new_references.parent_start_dt,
200 new_references.child_org_unit_cd,
201 new_references.child_start_dt,
202 new_references.our_create_dt,
203 new_references.course_type
204 ) THEN
205 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception ;
208
209 END IF;
210 Check_Parent_Existance;
211 Check_Constraints;
212 ELSIF (p_action = 'UPDATE') THEN
213 -- Call all the procedures related to Before Update.
214 Check_Parent_Existance;
215 Check_Constraints;
216 ELSIF (p_action = 'DELETE') THEN
217 -- Call all the procedures related to Before Delete.
218 Null;
219 ELSIF (p_action = 'VALIDATE_INSERT') THEN
220
221 IF Get_PK_For_Validation (
222 new_references.parent_org_unit_cd ,
223 new_references.parent_start_dt,
224 new_references.child_org_unit_cd,
225 new_references.child_start_dt,
226 new_references.our_create_dt,
227 new_references.course_type
228 ) THEN
229 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception ;
232
233 END IF;
234 Check_Constraints;
235
236 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
237
238 Check_Constraints ;
239
240 ELSIF (p_action = 'VALIDATE_DELETE') THEN
241
242 null ;
243
244 END IF;
245 END Before_DML;
246
247 PROCEDURE After_DML (
248 p_action IN VARCHAR2,
249 x_rowid IN VARCHAR2
250 ) AS
251 BEGIN
252 l_rowid := x_rowid;
253 END After_DML;
254
255 procedure INSERT_ROW (
256 X_ROWID in out NOCOPY VARCHAR2,
257 X_PARENT_ORG_UNIT_CD in VARCHAR2,
258 X_PARENT_START_DT in DATE,
259 X_CHILD_ORG_UNIT_CD in VARCHAR2,
260 X_CHILD_START_DT in DATE,
261 X_OUR_CREATE_DT in DATE,
262 X_COURSE_TYPE in VARCHAR2,
263 X_MODE in VARCHAR2 default 'R'
264 ) AS
265 cursor C is select ROWID from IGS_OR_REL_PS_TYPE
266 where PARENT_ORG_UNIT_CD = X_PARENT_ORG_UNIT_CD
267 and PARENT_START_DT = X_PARENT_START_DT
268 and CHILD_ORG_UNIT_CD = X_CHILD_ORG_UNIT_CD
269 and CHILD_START_DT = X_CHILD_START_DT
270 and OUR_CREATE_DT = X_OUR_CREATE_DT
271 and COURSE_TYPE = X_COURSE_TYPE;
272 X_LAST_UPDATE_DATE DATE;
273 X_LAST_UPDATED_BY NUMBER;
274 X_LAST_UPDATE_LOGIN NUMBER;
275 begin
276 X_LAST_UPDATE_DATE := SYSDATE;
277 if(X_MODE = 'I') then
278 X_LAST_UPDATED_BY := 1;
279 X_LAST_UPDATE_LOGIN := 0;
280 elsif (X_MODE = 'R') then
281 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
282 if X_LAST_UPDATED_BY is NULL then
283 X_LAST_UPDATED_BY := -1;
284 end if;
285 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
286 if X_LAST_UPDATE_LOGIN is NULL then
287 X_LAST_UPDATE_LOGIN := -1;
288 end if;
289 else
290 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
291 IGS_GE_MSG_STACK.ADD;
292 app_exception.raise_exception;
293 end if;
294 Before_DML(
295 p_action=>'INSERT',
296 x_rowid=>X_ROWID,
297 x_child_org_unit_cd=>X_CHILD_ORG_UNIT_CD,
298 x_child_start_dt=>X_CHILD_START_DT,
299 x_course_type=>X_COURSE_TYPE,
300 x_our_create_dt=>X_OUR_CREATE_DT,
301 x_parent_org_unit_cd=>X_PARENT_ORG_UNIT_CD,
302 x_parent_start_dt=>X_PARENT_START_DT,
303 x_creation_date=>X_LAST_UPDATE_DATE,
304 x_created_by=>X_LAST_UPDATED_BY,
305 x_last_update_date=>X_LAST_UPDATE_DATE,
306 x_last_updated_by=>X_LAST_UPDATED_BY,
307 x_last_update_login=>X_LAST_UPDATE_LOGIN
308 );
309 insert into IGS_OR_REL_PS_TYPE (
310 PARENT_ORG_UNIT_CD,
311 PARENT_START_DT,
312 CHILD_ORG_UNIT_CD,
313 CHILD_START_DT,
314 OUR_CREATE_DT,
315 COURSE_TYPE,
316 CREATION_DATE,
317 CREATED_BY,
318 LAST_UPDATE_DATE,
319 LAST_UPDATED_BY,
320 LAST_UPDATE_LOGIN
321 ) values (
322 NEW_REFERENCES.PARENT_ORG_UNIT_CD,
323 NEW_REFERENCES.PARENT_START_DT,
324 NEW_REFERENCES.CHILD_ORG_UNIT_CD,
325 NEW_REFERENCES.CHILD_START_DT,
326 NEW_REFERENCES.OUR_CREATE_DT,
327 NEW_REFERENCES.COURSE_TYPE,
328 X_LAST_UPDATE_DATE,
329 X_LAST_UPDATED_BY,
330 X_LAST_UPDATE_DATE,
331 X_LAST_UPDATED_BY,
332 X_LAST_UPDATE_LOGIN
333 );
334 open c;
335 fetch c into X_ROWID;
336 if (c%notfound) then
337 close c;
338 raise no_data_found;
339 end if;
340 close c;
341 After_DML(
342 p_action=>'INSERT',
343 x_rowid=>X_ROWID
344 );
345 end INSERT_ROW;
346
347 procedure LOCK_ROW (
348 X_ROWID in VARCHAR2,
349 X_PARENT_ORG_UNIT_CD in VARCHAR2,
350 X_PARENT_START_DT in DATE,
351 X_CHILD_ORG_UNIT_CD in VARCHAR2,
352 X_CHILD_START_DT in DATE,
353 X_OUR_CREATE_DT in DATE,
354 X_COURSE_TYPE in VARCHAR2
355 ) AS
356 cursor c1 is select rowid
357 from IGS_OR_REL_PS_TYPE
358 where ROWID = X_ROWID
359 for update nowait ;
360 tlinfo c1%rowtype;
361 begin
362 open c1;
363 fetch c1 into tlinfo;
364 if (c1%notfound) then
365 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
366 app_exception.raise_exception;
367 close c1;
368 return;
369 end if;
370 close c1;
371 return;
372 end LOCK_ROW;
373
374 procedure DELETE_ROW (
375 X_ROWID in VARCHAR2
376 ) AS
377 begin
378 Before_DML(
379 p_action=>'DELETE',
380 x_rowid=>X_ROWID
381 );
382 delete from IGS_OR_REL_PS_TYPE
383 where ROWID = X_ROWID ;
384 if (sql%notfound) then
385 raise no_data_found;
386 end if;
387 After_DML(
388 p_action=>'DELETE',
389 x_rowid=>X_ROWID
390 );
391 end DELETE_ROW;
392
393 procedure Check_Constraints (
394 Column_Name in VARCHAR2 DEFAULT NULL ,
395 Column_Value in VARCHAR2 DEFAULT NULL
396 ) AS
397 /*----------------------------------------------------------------------------
398 || Created By : pkpatel
399 || Created On :
400 || Purpose :
401 || Known limitations, enhancements or remarks :
402 || Change History :
403 || Who When What
404 || (reverse chronological order - newest change first)
405 || pkpatel 29-JUL-2002 Bug No: 2461744
406 || Removed the upper check constraint on org_unit_cd, institution_cd and hist_who
407 ----------------------------------------------------------------------------*/
408 BEGIN
409
410 IF Column_Name is null THEN
411 NULL;
412
413 ELSIF upper(Column_name) = 'COURSE_TYPE' THEN
414 new_references.COURSE_TYPE:= COLUMN_VALUE ;
415
416 END IF ;
417
418 IF upper(Column_name) = 'COURSE_TYPE' OR COLUMN_NAME IS NULL THEN
419 IF new_references.COURSE_TYPE<> upper(new_references.COURSE_TYPE) then
420 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
421 IGS_GE_MSG_STACK.ADD;
422 App_Exception.Raise_Exception ;
423 END IF;
424
425 END IF ;
426
427 END Check_Constraints ;
428
429 END IGS_OR_REL_PS_TYPE_PKG;