1 PACKAGE BODY igs_ps_degrees_pkg AS
2 /* $Header: IGSPI1IB.pls 120.0 2005/06/02 03:51:59 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_degrees%RowType;
5 new_references igs_ps_degrees%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_degree_cd IN VARCHAR2 DEFAULT NULL,
11 x_description IN VARCHAR2 DEFAULT NULL,
12 x_program_type IN VARCHAR2 ,
13 x_closed_ind 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 /*************************************************************
22 Created By :
23 Date Created By :
24 Purpose :
25 Know limitations, enhancements or remarks
26 Change History
27 Who When What
28
29 (reverse chronological order - newest change first)
30 ***************************************************************/
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_PS_DEGREES
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.degree_cd := x_degree_cd;
56 new_references.description := x_description;
57 new_references.program_type := x_program_type;
58 new_references.closed_ind := x_closed_ind;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 PROCEDURE Check_Constraints (
73 Column_Name IN VARCHAR2 DEFAULT NULL,
74 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
75 /*************************************************************
76 Created By :
77 Date Created By :
78 Purpose :
79 Know limitations, enhancements or remarks
80 Change History
81 Who When What
82
83 (reverse chronological order - newest change first)
84 ***************************************************************/
85
86 BEGIN
87
88 IF column_name IS NULL THEN
89 NULL;
90 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
91 new_references.closed_ind := column_value;
92 NULL;
93 END IF;
94
95
96
97 -- The following code checks for check constraints on the Columns.
98 IF Upper(Column_Name) = 'CLOSED_IND' OR
99 Column_Name IS NULL THEN
100 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
101 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106
107
108 END Check_Constraints;
109
110 PROCEDURE check_parent_existance IS
111 /*************************************************************
112 Created By :sarakshi
113 Date Created By :06-Sep-2004
114 Purpose :
115 Know limitations, enhancements or remarks
116 Change History
117 Who When What
118
119 (reverse chronological order - newest change first)
120 ***************************************************************/
121
122 BEGIN
123
124 IF ((old_references.program_type = new_references. program_type) OR
125 (new_references. program_type IS NULL)) THEN
126 NULL;
127 ELSE
128 IF NOT IGS_PS_TYPE_PKG.Get_PK_For_Validation (
129 new_references.program_type ) THEN
130 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135
136 END check_parent_existance;
137
138 PROCEDURE Check_Child_Existance IS
139 /*************************************************************
140 Created By :
141 Date Created By :
142 Purpose :
143 Know limitations, enhancements or remarks
144 Change History
145 Who When What
146
147 (reverse chronological order - newest change first)
148 ***************************************************************/
149
150 BEGIN
151
152 Igs_Ps_Faclty_Degrs_Pkg.Get_FK_Igs_Ps_Degrees (
153 old_references.degree_cd
154 );
155
156 END Check_Child_Existance;
157
158 FUNCTION Get_PK_For_Validation (
159 x_degree_cd IN VARCHAR2
160 ) RETURN BOOLEAN AS
161
162 /*************************************************************
163 Created By :
164 Date Created By :
165 Purpose :
166 Know limitations, enhancements or remarks
167 Change History
168 Who When What
169
170 (reverse chronological order - newest change first)
171 ***************************************************************/
172
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igs_ps_degrees
176 WHERE degree_cd = x_degree_cd
177 FOR UPDATE NOWAIT;
178
179 lv_rowid cur_rowid%RowType;
180
181 BEGIN
182
183 Open cur_rowid;
184 Fetch cur_rowid INTO lv_rowid;
185 IF (cur_rowid%FOUND) THEN
186 Close cur_rowid;
187 Return(TRUE);
188 ELSE
189 Close cur_rowid;
190 Return(FALSE);
191 END IF;
192 END Get_PK_For_Validation;
193
194 PROCEDURE Before_DML (
195 p_action IN VARCHAR2,
196 x_rowid IN VARCHAR2 DEFAULT NULL,
197 x_degree_cd IN VARCHAR2 DEFAULT NULL,
198 x_description IN VARCHAR2 DEFAULT NULL,
199 x_program_type IN VARCHAR2 ,
200 x_closed_ind IN VARCHAR2 DEFAULT NULL,
201 x_creation_date IN DATE DEFAULT NULL,
202 x_created_by IN NUMBER DEFAULT NULL,
203 x_last_update_date IN DATE DEFAULT NULL,
204 x_last_updated_by IN NUMBER DEFAULT NULL,
205 x_last_update_login IN NUMBER DEFAULT NULL
206 ) AS
207 /*************************************************************
208 Created By :
209 Date Created By :
210 Purpose :
211 Know limitations, enhancements or remarks
212 Change History
213 Who When What
214
215 (reverse chronological order - newest change first)
216 ***************************************************************/
217
218 BEGIN
219
220 Set_Column_Values (
221 p_action,
222 x_rowid,
223 x_degree_cd,
224 x_description,
225 x_program_type,
226 x_closed_ind,
227 x_creation_date,
228 x_created_by,
229 x_last_update_date,
230 x_last_updated_by,
231 x_last_update_login
232 );
233
234 IF (p_action = 'INSERT') THEN
235 -- Call all the procedures related to Before Insert.
236 Null;
237 IF Get_Pk_For_Validation(
238 new_references.degree_cd) THEN
239 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
240 IGS_GE_MSG_STACK.ADD;
241 App_Exception.Raise_Exception;
242 END IF;
243 Check_Constraints;
244 check_parent_existance;
245 ELSIF (p_action = 'UPDATE') THEN
246 -- Call all the procedures related to Before Update.
247 Null;
248 Check_Constraints;
249 check_parent_existance;
250 ELSIF (p_action = 'DELETE') THEN
251 -- Call all the procedures related to Before Delete.
252 Null;
253 Check_Child_Existance;
254 ELSIF (p_action = 'VALIDATE_INSERT') THEN
255 -- Call all the procedures related to Before Insert.
256 IF Get_PK_For_Validation (
257 new_references.degree_cd) THEN
258 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 END IF;
262 Check_Constraints;
263 check_parent_existance;
264 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
265 Check_Constraints;
266 check_parent_existance;
267 ELSIF (p_action = 'VALIDATE_DELETE') THEN
268 Check_Child_Existance;
269 END IF;
270
271 END Before_DML;
272
273 PROCEDURE After_DML (
274 p_action IN VARCHAR2,
275 x_rowid IN VARCHAR2
276 ) IS
277 /*************************************************************
278 Created By :
279 Date Created By :
280 Purpose :
281 Know limitations, enhancements or remarks
282 Change History
283 Who When What
284
285 (reverse chronological order - newest change first)
286 ***************************************************************/
287
288 BEGIN
289
290 l_rowid := x_rowid;
291
292 IF (p_action = 'INSERT') THEN
293 -- Call all the procedures related to After Insert.
294 Null;
295 ELSIF (p_action = 'UPDATE') THEN
296 -- Call all the procedures related to After Update.
297 Null;
298 ELSIF (p_action = 'DELETE') THEN
299 -- Call all the procedures related to After Delete.
300 Null;
301 END IF;
302
303 END After_DML;
304
305 procedure INSERT_ROW (
306 X_ROWID in out NOCOPY VARCHAR2,
307 x_DEGREE_CD IN VARCHAR2,
308 x_DESCRIPTION IN VARCHAR2,
309 X_PROGRAM_TYPE IN VARCHAR2 ,
310 x_CLOSED_IND IN VARCHAR2,
311 X_MODE in VARCHAR2 default 'R'
312 ) AS
313 /*************************************************************
314 Created By :
315 Date Created By :
316 Purpose :
317 Know limitations, enhancements or remarks
318 Change History
319 Who When What
320
321 (reverse chronological order - newest change first)
322 ***************************************************************/
323
324 cursor C is select ROWID from IGS_PS_DEGREES
325 where DEGREE_CD= X_DEGREE_CD
326 ;
327 X_LAST_UPDATE_DATE DATE ;
328 X_LAST_UPDATED_BY NUMBER ;
329 X_LAST_UPDATE_LOGIN NUMBER ;
330 begin
331 X_LAST_UPDATE_DATE := SYSDATE;
332 if(X_MODE = 'I') then
333 X_LAST_UPDATED_BY := 1;
334 X_LAST_UPDATE_LOGIN := 0;
335 elsif (X_MODE = 'R') then
336 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
337 if X_LAST_UPDATED_BY is NULL then
338 X_LAST_UPDATED_BY := -1;
339 end if;
340 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
341 if X_LAST_UPDATE_LOGIN is NULL then
342 X_LAST_UPDATE_LOGIN := -1;
343 end if;
344 else
345 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
346 IGS_GE_MSG_STACK.ADD;
347 app_exception.raise_exception;
348 end if;
349 -----
350
351 -----
352 Before_DML(
353 p_action=>'INSERT',
354 x_rowid=>X_ROWID,
355 x_degree_cd=>X_DEGREE_CD,
356 x_description=>X_DESCRIPTION,
357 x_program_type => x_program_type,
358 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
359 x_creation_date=>X_LAST_UPDATE_DATE,
360 x_created_by=>X_LAST_UPDATED_BY,
361 x_last_update_date=>X_LAST_UPDATE_DATE,
362 x_last_updated_by=>X_LAST_UPDATED_BY,
363 x_last_update_login=>X_LAST_UPDATE_LOGIN);
364 insert into IGS_PS_DEGREES (
365 DEGREE_CD
366 ,DESCRIPTION
367 ,PROGRAM_TYPE
368 ,CLOSED_IND
369 ,CREATION_DATE
370 ,CREATED_BY
371 ,LAST_UPDATE_DATE
372 ,LAST_UPDATED_BY
373 ,LAST_UPDATE_LOGIN
374 ) values (
375 NEW_REFERENCES.DEGREE_CD
376 ,NEW_REFERENCES.DESCRIPTION
377 ,NEW_REFERENCES.PROGRAM_TYPE
378 ,NEW_REFERENCES.CLOSED_IND
379 ,X_LAST_UPDATE_DATE
380 ,X_LAST_UPDATED_BY
381 ,X_LAST_UPDATE_DATE
382 ,X_LAST_UPDATED_BY
383 ,X_LAST_UPDATE_LOGIN
384 );
385 open c;
386 fetch c into X_ROWID;
387 if (c%notfound) then
388 close c;
389 raise no_data_found;
390 end if;
391 close c;
392 After_DML (
393 p_action => 'INSERT' ,
394 x_rowid => X_ROWID );
395 end INSERT_ROW;
396 procedure LOCK_ROW (
397 X_ROWID in VARCHAR2,
398 x_DEGREE_CD IN VARCHAR2,
399 x_DESCRIPTION IN VARCHAR2,
400 X_PROGRAM_TYPE IN VARCHAR2 ,
401 x_CLOSED_IND IN VARCHAR2 ) AS
402 /*************************************************************
403 Created By :
404 Date Created By :
405 Purpose :
406 Know limitations, enhancements or remarks
407 Change History
411 ***************************************************************/
408 Who When What
409
410 (reverse chronological order - newest change first)
412
413 cursor c1 is select
414 DESCRIPTION
415 , PROGRAM_TYPE
416 , CLOSED_IND
417 from IGS_PS_DEGREES
418 where ROWID = X_ROWID
419 for update nowait;
420 tlinfo c1%rowtype;
421 begin
422 open c1;
423 fetch c1 into tlinfo;
424 if (c1%notfound) then
425 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
426 IGS_GE_MSG_STACK.ADD;
427 close c1;
428 app_exception.raise_exception;
429 return;
430 end if;
431 close c1;
432 if ( ( tlinfo.DESCRIPTION = X_DESCRIPTION)
433 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
434 AND ((tlinfo.PROGRAM_TYPE = X_PROGRAM_TYPE)
435 OR ((tlinfo.PROGRAM_TYPE is null)
436 AND (X_PROGRAM_TYPE is null)))
437 ) then
438 null;
439 else
440 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
441 IGS_GE_MSG_STACK.ADD;
442 app_exception.raise_exception;
443 end if;
444 return;
445 end LOCK_ROW;
446 Procedure UPDATE_ROW (
447 X_ROWID in VARCHAR2,
448 x_DEGREE_CD IN VARCHAR2,
449 x_DESCRIPTION IN VARCHAR2,
450 X_PROGRAM_TYPE IN VARCHAR2 ,
451 x_CLOSED_IND IN VARCHAR2,
452 X_MODE in VARCHAR2 default 'R'
453 ) AS
454 /*************************************************************
455 Created By :
456 Date Created By :
457 Purpose :
458 Know limitations, enhancements or remarks
459 Change History
460 Who When What
461
462 (reverse chronological order - newest change first)
463 ***************************************************************/
464
465 X_LAST_UPDATE_DATE DATE ;
466 X_LAST_UPDATED_BY NUMBER ;
467 X_LAST_UPDATE_LOGIN NUMBER ;
468 begin
469 X_LAST_UPDATE_DATE := SYSDATE;
470 if(X_MODE = 'I') then
471 X_LAST_UPDATED_BY := 1;
472 X_LAST_UPDATE_LOGIN := 0;
473 elsif (X_MODE = 'R') then
474 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
475 if X_LAST_UPDATED_BY is NULL then
476 X_LAST_UPDATED_BY := -1;
477 end if;
478 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
479 if X_LAST_UPDATE_LOGIN is NULL then
480 X_LAST_UPDATE_LOGIN := -1;
481 end if;
482 else
483 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
484 IGS_GE_MSG_STACK.ADD;
485 app_exception.raise_exception;
486 end if;
487 Before_DML(
488 p_action=>'UPDATE',
489 x_rowid=>X_ROWID,
490 x_degree_cd=>X_DEGREE_CD,
491 x_description=>X_DESCRIPTION,
492 x_program_type=> x_program_type,
493 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
494 x_creation_date=>X_LAST_UPDATE_DATE,
495 x_created_by=>X_LAST_UPDATED_BY,
496 x_last_update_date=>X_LAST_UPDATE_DATE,
497 x_last_updated_by=>X_LAST_UPDATED_BY,
498 x_last_update_login=>X_LAST_UPDATE_LOGIN);
499 update IGS_PS_DEGREES set
500 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
501 PROGRAM_TYPE = NEW_REFERENCES.PROGRAM_TYPE,
502 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
503 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
504 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
505 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
506 where ROWID = X_ROWID;
507 if (sql%notfound) then
508 raise no_data_found;
509 end if;
510
511 After_DML (
512 p_action => 'UPDATE' ,
513 x_rowid => X_ROWID
514 );
515 end UPDATE_ROW;
516 procedure ADD_ROW (
517 X_ROWID in out NOCOPY VARCHAR2,
518 x_DEGREE_CD IN VARCHAR2,
519 x_DESCRIPTION IN VARCHAR2,
520 X_PROGRAM_TYPE IN VARCHAR2 ,
521 x_CLOSED_IND IN VARCHAR2,
522 X_MODE in VARCHAR2 default 'R'
523 ) AS
524 /*************************************************************
525 Created By :
526 Date Created By :
527 Purpose :
528 Know limitations, enhancements or remarks
529 Change History
530 Who When What
531
532 (reverse chronological order - newest change first)
533 ***************************************************************/
534
535 cursor c1 is select ROWID from IGS_PS_DEGREES
536 where DEGREE_CD= X_DEGREE_CD
537 ;
538 begin
539 open c1;
540 fetch c1 into X_ROWID;
541 if (c1%notfound) then
542 close c1;
543 INSERT_ROW (
544 X_ROWID,
545 X_DEGREE_CD,
546 X_DESCRIPTION,
547 X_PROGRAM_TYPE,
548 X_CLOSED_IND,
549 X_MODE );
550 return;
551 end if;
552 close c1;
553 UPDATE_ROW (
554 X_ROWID,
555 X_DEGREE_CD,
556 X_DESCRIPTION,
557 X_PROGRAM_TYPE,
558 X_CLOSED_IND,
559 X_MODE );
560 end ADD_ROW;
561 procedure DELETE_ROW (
565 Created By :
562 X_ROWID in VARCHAR2
563 ) AS
564 /*************************************************************
566 Date Created By :
567 Purpose :
568 Know limitations, enhancements or remarks
569 Change History
570 Who When What
571
572 (reverse chronological order - newest change first)
573 ***************************************************************/
574
575 begin
576 Before_DML (
577 p_action => 'DELETE',
578 x_rowid => X_ROWID
579 );
580 delete from IGS_PS_DEGREES
581 where ROWID = X_ROWID;
582 if (sql%notfound) then
583 raise no_data_found;
584 end if;
585 After_DML (
586 p_action => 'DELETE',
587 x_rowid => X_ROWID
588 );
589 end DELETE_ROW;
590 END igs_ps_degrees_pkg;
[Home] [Help]