1 package body IGS_AS_COURSE_TYPE_PKG as
2 /* $Header: IGSDI01B.pls 115.6 2003/06/05 12:56:57 sarakshi ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_COURSE_TYPE_ALL%RowType;
5 new_references IGS_AS_COURSE_TYPE_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_org_id IN NUMBER DEFAULT NULL,
10 x_ass_id IN NUMBER DEFAULT NULL,
11 x_course_type IN VARCHAR2 DEFAULT NULL,
12 x_creation_date IN DATE DEFAULT NULL,
13 x_created_by IN NUMBER DEFAULT NULL,
14 x_last_update_date IN DATE DEFAULT NULL,
15 x_last_updated_by IN NUMBER DEFAULT NULL,
16 x_last_update_login IN NUMBER DEFAULT NULL
17 ) AS
18 CURSOR cur_old_ref_values IS
19 SELECT *
20 FROM IGS_AS_COURSE_TYPE_ALL
21 WHERE rowid = x_rowid;
22 BEGIN
23 l_rowid := x_rowid;
24 -- Code for setting the Old and New Reference Values.
25 -- Populate Old Values.
26 Open cur_old_ref_values;
27 Fetch cur_old_ref_values INTO old_references;
28 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
29 fnd_message.Set_Name ('FND', 'FORM_RECORD_DELETED');
30 IGS_GE_MSG_STACK.ADD;
31 Close cur_old_ref_values;
32 APP_EXCEPTION.RAISE_EXCEPTION;
33
34 Return;
35 END IF;
36 Close cur_old_ref_values;
37 -- Populate New Values.
38 new_references.ass_id := x_ass_id;
39 new_references.course_type := x_course_type;
40 new_references.org_id := x_org_id;
41 IF (p_action = 'UPDATE') THEN
42 new_references.creation_date := old_references.creation_date;
43 new_references.created_by := old_references.created_by;
44 ELSE
45 new_references.creation_date := x_creation_date;
46 new_references.created_by := x_created_by;
47 END IF;
48 new_references.last_update_date := x_last_update_date;
49 new_references.last_updated_by := x_last_updated_by;
50 new_references.last_update_login := x_last_update_login;
51 END Set_Column_Values;
52 -- Trigger description :-
53 -- "OSS_TST".trg_acot_br_i
54 -- BEFORE INSERT
55 -- ON IGS_AS_COURSE_TYPE
56 -- FOR EACH ROW
57 PROCEDURE BeforeRowInsert1(
58 p_inserting IN BOOLEAN DEFAULT FALSE,
59 p_updating IN BOOLEAN DEFAULT FALSE,
60 p_deleting IN BOOLEAN DEFAULT FALSE
61 ) AS
62 v_message_name VARCHAR2(30);
63 BEGIN
64 -- Validate that inserts are allowed
65 IF p_inserting THEN
66 -- Validate IGS_PS_COURSE type closed indicator
67 IF IGS_AS_VAL_ACOT.crsp_val_cty_closed(new_references.course_type,
68 v_message_name) = FALSE THEN
69 FND_MESSAGE.SET_NAME('IGS',v_message_name);
70 IGS_GE_MSG_STACK.ADD;
71 APP_EXCEPTION.RAISE_EXCEPTION;
72 END IF;
73 END IF;
74 END BeforeRowInsert1;
75 -- Trigger description :-
76 -- "OSS_TST".trg_acot_br_id_upd
77 -- BEFORE INSERT OR DELETE
78 -- ON IGS_AS_COURSE_TYPE
79 -- FOR EACH ROW
80 PROCEDURE BeforeRowInsertDelete2(
81 p_inserting IN BOOLEAN DEFAULT FALSE,
82 p_updating IN BOOLEAN DEFAULT FALSE,
83 p_deleting IN BOOLEAN DEFAULT FALSE
84 ) AS
85 v_message_name VARCHAR2(30);
86 v_ass_id IGS_AS_UNITASS_ITEM.ass_id%TYPE;
87 BEGIN
88 -- Update action date when adding/p_deleting an ACOT record
89 IF p_inserting OR p_deleting THEN
90 IF p_inserting THEN
91 v_ass_id := new_references.ass_id;
92 ELSE
93 v_ass_id := old_references.ass_id;
94 END IF;
95 IF IGS_AS_GEN_005.ASSP_UPD_UAI_ACTION (
96 v_ass_id,
97 v_message_name) = FALSE THEN
98 FND_MESSAGE.SET_NAME('IGS',v_message_name);
99 IGS_GE_MSG_STACK.ADD;
100 APP_EXCEPTION.RAISE_EXCEPTION;
101 END IF;
102 END IF;
103 END BeforeRowInsertDelete2;
104 PROCEDURE Check_Constraints (
105 Column_Name IN VARCHAR2 DEFAULT NULL,
106 Column_Value IN VARCHAR2 DEFAULT NULL
107 )AS
108 BEGIN
109 IF column_name is null then
110 NULL;
111 ELSIF upper(Column_name) = 'COURSE_TYPE' then
112 new_references.COURSE_TYPE := column_value;
113 END IF;
114 IF upper(column_name) = 'COURSE_TYPE' OR
115 column_name is null Then
116 IF new_references.COURSE_TYPE <> UPPER(new_references.COURSE_TYPE) Then
117 fnd_message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
118 IGS_GE_MSG_STACK.ADD;
119 APP_EXCEPTION.RAISE_EXCEPTION;
120 END IF;
121 END IF;
122
123 END Check_Constraints;
124 PROCEDURE Check_Parent_Existance AS
125 BEGIN
126 IF (((old_references.ass_id = new_references.ass_id)) OR
127 ((new_references.ass_id IS NULL))) THEN
128 NULL;
129 ELSIF NOT IGS_AS_ASSESSMNT_ITM_PKG.Get_PK_For_Validation (
130 new_references.ass_id ) THEN
131 FND_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
132 IGS_GE_MSG_STACK.ADD;
133 APP_EXCEPTION.RAISE_EXCEPTION;
134 END IF;
135 IF (((old_references.course_type = new_references.course_type)) OR
136 ((new_references.course_type IS NULL))) THEN
137 NULL;
138 ELSIF NOT IGS_PS_TYPE_PKG.Get_PK_For_Validation (
139 new_references.course_type
140 )THEN
141 FND_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
142 IGS_GE_MSG_STACK.ADD;
143 APP_EXCEPTION.RAISE_EXCEPTION;
144 END IF;
145 END Check_Parent_Existance;
146 FUNCTION Get_PK_For_Validation (
147 x_ass_id IN NUMBER,
148 x_course_type IN VARCHAR2
149 ) RETURN BOOLEAN AS
150 CURSOR cur_rowid IS
151 SELECT rowid
152 FROM IGS_AS_COURSE_TYPE_ALL
153 WHERE ass_id = x_ass_id
154 AND course_type = x_course_type
155 FOR UPDATE NOWAIT;
156 lv_rowid cur_rowid%RowType;
157 BEGIN
158 Open cur_rowid;
159 Fetch cur_rowid INTO lv_rowid;
160 IF (cur_rowid%FOUND) THEN
161 Close cur_rowid;
162 RETURN (TRUE);
163 ELSE
164 Close cur_rowid;
165 RETURN (FALSE);
166 END IF;
167 END Get_PK_For_Validation;
168 PROCEDURE GET_FK_IGS_AS_ASSESSMNT_ITM (
169 x_ass_id IN NUMBER
170 ) AS
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM IGS_AS_COURSE_TYPE_ALL
174 WHERE ass_id = x_ass_id ;
175 lv_rowid cur_rowid%RowType;
176 BEGIN
177 Open cur_rowid;
178 Fetch cur_rowid INTO lv_rowid;
179 IF (cur_rowid%FOUND) THEN
180 fnd_message.Set_Name ('IGS', 'IGS_AS_ACOT_AI_FK');
181 IGS_GE_MSG_STACK.ADD;
182 Close cur_rowid;
183 APP_EXCEPTION.RAISE_EXCEPTION;
184
185 Return;
186 END IF;
187 Close cur_rowid;
188 END GET_FK_IGS_AS_ASSESSMNT_ITM;
189
190 PROCEDURE Before_DML (
191 p_action IN VARCHAR2,
192 x_rowid IN VARCHAR2 DEFAULT NULL,
193 x_org_id IN NUMBER DEFAULT NULL,
194 x_ass_id IN NUMBER DEFAULT NULL,
195 x_course_type IN VARCHAR2 DEFAULT NULL,
196 x_creation_date IN DATE DEFAULT NULL,
197 x_created_by IN NUMBER DEFAULT NULL,
198 x_last_update_date IN DATE DEFAULT NULL,
199 x_last_updated_by IN NUMBER DEFAULT NULL,
200 x_last_update_login IN NUMBER DEFAULT NULL
201 ) AS
202 BEGIN
203 Set_Column_Values (
204 p_action,
205 x_rowid,
206 x_org_id,
207 x_ass_id,
208 x_course_type,
209 x_creation_date,
210 x_created_by,
211 x_last_update_date,
212 x_last_updated_by,
213 x_last_update_login
214 );
215 IF (p_action = 'INSERT') THEN
216 -- Call all the procedures related to Before Insert.
217 BeforeRowInsert1 ( p_inserting => TRUE );
218 BeforeRowInsertDelete2 ( p_inserting => TRUE );
219 IF Get_PK_For_Validation ( NEW_REFERENCES.ass_id ,
220 NEW_REFERENCES.course_type
221 ) THEN
222 fnd_message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
223 IGS_GE_MSG_STACK.ADD;
224 APP_EXCEPTION.RAISE_EXCEPTION;
225 END IF;
226 Check_Constraints;
227 Check_Parent_Existance;
228 ELSIF (p_action = 'UPDATE') THEN
229 -- Call all the procedures related to Before Update.
230
231 Check_Constraints;
232 Check_Parent_Existance;
233 ELSIF (p_action = 'DELETE') THEN
234 -- Call all the procedures related to Before Delete.
235 BeforeRowInsertDelete2 ( p_deleting => TRUE );
236 ELSIF (p_action = 'VALIDATE_INSERT') THEN
237 IF Get_PK_For_Validation (
238 new_references.ass_id ,
239 new_references.course_type) THEN
240 fnd_message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
241 IGS_GE_MSG_STACK.ADD;
242 APP_EXCEPTION.RAISE_EXCEPTION;
243 END IF;
244 Check_Constraints;
245 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
246
247 Check_Constraints;
248
249 END IF;
250 END Before_DML;
251
252 procedure INSERT_ROW (
253 X_ROWID in out NOCOPY VARCHAR2,
254 X_ORG_ID in NUMBER,
255 X_ASS_ID in NUMBER,
256 X_COURSE_TYPE in VARCHAR2,
257 X_MODE in VARCHAR2 default 'R'
258 ) AS
259 cursor C is select ROWID from IGS_AS_COURSE_TYPE_all
260 where ASS_ID = X_ASS_ID
261 and COURSE_TYPE = X_COURSE_TYPE;
262 X_LAST_UPDATE_DATE DATE;
263 X_LAST_UPDATED_BY NUMBER;
264 X_LAST_UPDATE_LOGIN NUMBER;
265 begin
266 X_LAST_UPDATE_DATE := SYSDATE;
267 if(X_MODE = 'I') then
268 X_LAST_UPDATED_BY := 1;
269 X_LAST_UPDATE_LOGIN := 0;
270 elsif (X_MODE = 'R') then
271 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
272 if X_LAST_UPDATED_BY is NULL then
273 X_LAST_UPDATED_BY := -1;
274 end if;
275 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
276 if X_LAST_UPDATE_LOGIN is NULL then
277 X_LAST_UPDATE_LOGIN := -1;
278 end if;
279 else
280 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
281 IGS_GE_MSG_STACK.ADD;
282 APP_EXCEPTION.RAISE_EXCEPTION;
283 end if;
284 Before_DML(
285 p_action=>'INSERT',
286 x_rowid=>X_ROWID,
287 x_org_id => igs_ge_gen_003.get_org_id,
288 x_ass_id=>X_ASS_ID,
289 x_course_type=>X_COURSE_TYPE,
290 x_creation_date=>X_LAST_UPDATE_DATE,
291 x_created_by=>X_LAST_UPDATED_BY,
292 x_last_update_date=>X_LAST_UPDATE_DATE,
293 x_last_updated_by=>X_LAST_UPDATED_BY,
294 x_last_update_login=>X_LAST_UPDATE_LOGIN
295 );
296 insert into IGS_AS_COURSE_TYPE_ALL (
297 ASS_ID,
298 ORG_ID,
299 COURSE_TYPE,
300 CREATION_DATE,
301 CREATED_BY,
302 LAST_UPDATE_DATE,
303 LAST_UPDATED_BY,
304 LAST_UPDATE_LOGIN
305 ) values (
306 NEW_REFERENCES.ASS_ID,
307 NEW_REFERENCES.ORG_ID,
308 NEW_REFERENCES.COURSE_TYPE,
309 X_LAST_UPDATE_DATE,
310 X_LAST_UPDATED_BY,
311 X_LAST_UPDATE_DATE,
312 X_LAST_UPDATED_BY,
313 X_LAST_UPDATE_LOGIN
314 );
315 open c;
316 fetch c into X_ROWID;
317 if (c%notfound) then
318 close c;
319 raise no_data_found;
320 end if;
321 close c;
322
323 end INSERT_ROW;
324 procedure LOCK_ROW (
325 X_ROWID in VARCHAR2,
326 X_ASS_ID in NUMBER,
327 X_COURSE_TYPE in VARCHAR2
328 ) AS
329 cursor c1 is select
330 ROWID
331 from IGS_AS_COURSE_TYPE_ALL
332 where ROWID = X_ROWID for update nowait;
333 tlinfo c1%rowtype;
334 begin
335 open c1;
336 fetch c1 into tlinfo;
337 if (c1%notfound) then
338 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
339 IGS_GE_MSG_STACK.ADD;
340 APP_EXCEPTION.RAISE_EXCEPTION;
341 close c1;
342 return;
343 end if;
344 close c1;
345 return;
346 end LOCK_ROW;
347 procedure DELETE_ROW (
348 X_ROWID in VARCHAR2) AS
349 begin
350 Before_DML(
351 p_action => 'DELETE',
352 x_rowid => X_ROWID
353 );
354 delete from IGS_AS_COURSE_TYPE_ALL
355 where ROWID = X_ROWID;
356
357 if (sql%notfound) then
358 raise no_data_found;
359 end if;
360 end DELETE_ROW;
361 end IGS_AS_COURSE_TYPE_PKG;
[Home] [Help]