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