[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_COURSE_PKG
Source
1 package body IGS_PS_COURSE_PKG AS
2 /* $Header: IGSPI03B.pls 115.7 2003/04/16 05:42:12 smanglm ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PS_COURSE%RowType;
7 new_references IGS_PS_COURSE%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 ,
12 x_course_cd IN VARCHAR2 ,
13 x_creation_date IN DATE ,
14 x_created_by IN NUMBER ,
15 x_last_update_date IN DATE ,
16 x_last_updated_by IN NUMBER ,
17 x_last_update_login IN NUMBER
18 ) AS
19 /*----------------------------------------------------------------------------
20 || Created By :
21 || Created On :
22 || Purpose :
23 || Known limitations, enhancements or remarks :
24 || Change History :
25 || Who When What
26 || (reverse chronological order - newest change first)
27 || smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
28 || As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
29 ----------------------------------------------------------------------------*/
30
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_PS_COURSE
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 Open cur_old_ref_values;
44 Fetch cur_old_ref_values INTO old_references;
45 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
46 Close cur_old_ref_values;
47 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
48 IGS_GE_MSG_STACK.ADD;
49 App_Exception.Raise_Exception;
50 Return;
51 END IF;
52 Close cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.course_cd := x_course_cd;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66
67 END Set_Column_Values;
68
69 PROCEDURE Check_Constraints (
70 Column_Name IN VARCHAR2,
71 Column_Value IN VARCHAR2
72 )
73 AS
74 BEGIN
75
76 IF column_name is null then
77 NULL;
78 ELSIF upper(Column_name) = 'COURSE_CD' then
79 new_references.course_cd := column_value;
80 END IF;
81 IF upper(column_name) = 'COURSE_CD' OR
82 column_name is null Then
83 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
84 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
85 IGS_GE_MSG_STACK.ADD;
86 App_Exception.Raise_Exception;
87 END IF;
88 END IF;
89
90 END Check_Constraints;
91
92 PROCEDURE Check_Child_Existance AS
93 BEGIN
94
95 IGS_AD_PS_APPL_PKG.GET_FK_IGS_PS_COURSE (
96 old_references.course_cd
97 );
98
99 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_PS_COURSE (
100 old_references.course_cd
101 );
102
103 IGS_PE_ALTERNATV_EXT_PKG.GET_FK_IGS_PS_COURSE (
104 old_references.course_cd
105 );
106
107 IGS_PS_FEE_TRG_PKG.GET_FK_IGS_PS_COURSE (
108 old_references.course_cd
109 );
110
111 IGS_PS_RU_PKG.GET_FK_IGS_PS_COURSE (
112 old_references.course_cd
113 );
114
115 IGS_PS_VER_PKG.GET_FK_IGS_PS_COURSE (
116 old_references.course_cd
117 );
118
119 IGS_FI_FEE_AS_PKG.GET_FK_IGS_PS_COURSE (
120 old_references.course_cd
121 );
122
123 IGS_AS_NON_ENR_STDOT_PKG.GET_FK_IGS_PS_COURSE (
124 old_references.course_cd
125 );
126
127 IGS_PE_COURSE_EXCL_PKG.GET_FK_IGS_PS_COURSE (
128 old_references.course_cd
129 );
130
131 IGS_PR_OU_PS_PKG.GET_FK_IGS_PS_COURSE (
132 old_references.course_cd
133 );
134
135 IGS_EN_STDNT_PS_ATT_PKG.GET_FK_IGS_PS_COURSE (
136 old_references.course_cd
137 );
138
139 IGS_PR_STDNT_PR_PS_PKG.GET_FK_IGS_PS_COURSE (
140 old_references.course_cd
141 );
142
143 igs_da_cnfg_req_typ_pkg.get_fk_igs_ps_course (
144 x_course_cd => old_references.course_cd
145 );
146
147 igs_da_req_wif_pkg.get_fk_igs_ps_course (
148 x_course_cd => old_references.course_cd
149 );
150 igs_da_req_stdnts_pkg.get_fk_igs_ps_course (
151 x_course_cd => old_references.course_cd
152 );
153
154 END Check_Child_Existance;
155
156 FUNCTION Get_PK_For_Validation (
157 x_course_cd IN VARCHAR2
158 )
159 RETURN BOOLEAN AS
160 /***************************************************************************************
161 Change History
162 WHO WHEN WHAT
163 shtatiko 18-FEB-2003 Enh# 2797116, Removed FOR UPDATE NOWAIT clause is removed
164 from the cursor, cur_rowid.
165 ***************************************************************************************/
166 CURSOR cur_rowid IS
167 SELECT rowid
168 FROM IGS_PS_COURSE
169 WHERE course_cd = x_course_cd;
170
171 lv_rowid cur_rowid%RowType;
172
173 BEGIN
174
175 Open cur_rowid;
176 Fetch cur_rowid INTO lv_rowid;
177 IF (cur_rowid%FOUND) THEN
178 Close cur_rowid;
179 Return (TRUE);
180 ELSE
181 Close cur_rowid;
182 Return (FALSE);
183 END IF;
184
185 END Get_PK_For_Validation;
186
187 PROCEDURE Before_DML (
188 p_action IN VARCHAR2,
189 x_rowid IN VARCHAR2,
190 x_course_cd IN VARCHAR2,
191 x_creation_date IN DATE,
192 x_created_by IN NUMBER,
193 x_last_update_date IN DATE,
194 x_last_updated_by IN NUMBER,
195 x_last_update_login IN NUMBER
196 ) AS
197 BEGIN
198
199 Set_Column_Values (
200 p_action,
201 x_rowid,
202 x_course_cd,
203 x_creation_date,
204 x_created_by,
205 x_last_update_date,
206 x_last_updated_by,
207 x_last_update_login
208 );
209
210 IF (p_action = 'INSERT') THEN
211 -- Call all the procedures related to Before Insert.
212 IF Get_PK_For_Validation ( new_references.course_cd ) THEN
213 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
214 IGS_GE_MSG_STACK.ADD;
215 App_Exception.Raise_Exception;
216 END IF;
217 Check_Constraints;
218 ELSIF (p_action = 'UPDATE') THEN
219 -- Call all the procedures related to Before Update.
220 Check_Constraints;
221 ELSIF (p_action = 'DELETE') THEN
222 -- Call all the procedures related to Before Delete.
223 Check_Child_Existance;
224 ELSIF (p_action = 'VALIDATE_INSERT') THEN
225 IF Get_PK_For_Validation (new_references.course_cd ) THEN
226 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 END IF;
230 Check_Constraints;
231 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
232 Check_Constraints;
233 ELSIF (p_action = 'VALIDATE_DELETE') THEN
234 Check_Child_Existance;
235 END IF;
236
237 END Before_DML;
238
239 PROCEDURE After_DML (
240 p_action IN VARCHAR2,
241 x_rowid IN VARCHAR2
242 ) AS
243 BEGIN
244
245 l_rowid := x_rowid;
246
247 END After_DML;
248
249 procedure INSERT_ROW (
250 X_ROWID in out NOCOPY VARCHAR2,
251 X_COURSE_CD in VARCHAR2,
252 X_MODE in VARCHAR2
253 ) AS
254 cursor C is select ROWID from IGS_PS_COURSE
255 where COURSE_CD = X_COURSE_CD;
256 X_LAST_UPDATE_DATE DATE;
257 X_LAST_UPDATED_BY NUMBER;
258 X_LAST_UPDATE_LOGIN NUMBER;
259 begin
260 X_LAST_UPDATE_DATE := SYSDATE;
261 if(X_MODE = 'I') then
262 X_LAST_UPDATED_BY := 1;
263 X_LAST_UPDATE_LOGIN := 0;
264 elsif (X_MODE = 'R') then
265 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
266 if X_LAST_UPDATED_BY is NULL then
267 X_LAST_UPDATED_BY := -1;
268 end if;
269 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
270 if X_LAST_UPDATE_LOGIN is NULL then
271 X_LAST_UPDATE_LOGIN := -1;
272 end if;
273 else
274 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
275 IGS_GE_MSG_STACK.ADD;
276 app_exception.raise_exception;
277 end if;
278
279 Before_DML (
280 p_action => 'INSERT',
281 x_rowid => X_ROWID,
282 x_course_cd => X_COURSE_CD ,
283 x_creation_date => X_LAST_UPDATE_DATE ,
284 x_created_by => X_LAST_UPDATED_BY ,
285 x_last_update_date => X_LAST_UPDATE_DATE ,
286 x_last_updated_by => X_LAST_UPDATED_BY ,
287 x_last_update_login => X_LAST_UPDATE_LOGIN
288 );
289
290 insert into IGS_PS_COURSE (
291 COURSE_CD,
292 CREATION_DATE,
293 CREATED_BY,
294 LAST_UPDATE_DATE,
295 LAST_UPDATED_BY,
296 LAST_UPDATE_LOGIN
297 ) values (
298 NEW_REFERENCES.COURSE_CD,
299 X_LAST_UPDATE_DATE,
300 X_LAST_UPDATED_BY,
301 X_LAST_UPDATE_DATE,
302 X_LAST_UPDATED_BY,
303 X_LAST_UPDATE_LOGIN
304 );
305
306 open c;
307 fetch c into X_ROWID;
308 if (c%notfound) then
309 close c;
310 raise no_data_found;
311 end if;
312 close c;
313
314 After_DML (
315 p_action => 'INSERT',
316 x_rowid => X_ROWID
317 );
318
319 end INSERT_ROW;
320
321 procedure LOCK_ROW (
322 X_ROWID IN VARCHAR2,
323 X_COURSE_CD in VARCHAR2
324 ) AS
325 cursor c1 is select ROWID
326 from IGS_PS_COURSE
327 where ROWID = X_ROWID
328 for update nowait;
329 tlinfo c1%rowtype;
330
331 begin
332 open c1;
333 fetch c1 into tlinfo;
334 if (c1%notfound) then
335 close c1;
336 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
337 IGS_GE_MSG_STACK.ADD;
338 app_exception.raise_exception;
339 return;
340 end if;
341 close c1;
342
343 return;
344 end LOCK_ROW;
345
346 procedure DELETE_ROW (
347 X_ROWID in VARCHAR2
348 ) AS
349 begin
350
351 Before_DML (
352 p_action => 'DELETE',
353 x_rowid => X_ROWID
354 );
355
356 delete from IGS_PS_COURSE
357 where ROWID = X_ROWID;
358 if (sql%notfound) then
359 raise no_data_found;
360 end if;
361
362 After_DML (
363 p_action => 'DELETE',
364 x_rowid => X_ROWID
365 );
366
367 end DELETE_ROW;
368
369 end IGS_PS_COURSE_PKG;