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