eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_AD_BASIS_FOR_AD_PKG

Source


1 package body IGS_AD_BASIS_FOR_AD_PKG as
2  /* $Header: IGSAI69B.pls 115.6 2003/10/30 13:16:28 akadam ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_BASIS_FOR_AD%RowType;
6   new_references IGS_AD_BASIS_FOR_AD%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_govt_basis_for_adm_type IN VARCHAR2 DEFAULT NULL,
14     x_closed_ind IN VARCHAR2 DEFAULT NULL,
15     x_creation_date IN DATE DEFAULT NULL,
16     x_created_by IN NUMBER DEFAULT NULL,
17     x_last_update_date IN DATE DEFAULT NULL,
18     x_last_updated_by IN NUMBER DEFAULT NULL,
19     x_last_update_login IN NUMBER DEFAULT NULL
20   ) as
21 
22     CURSOR cur_old_ref_values IS
23       SELECT   *
24       FROM     IGS_AD_BASIS_FOR_AD
25       WHERE    rowid = x_rowid;
26 
27   BEGIN
28 
29     l_rowid := x_rowid;
30 
31     Open cur_old_ref_values;
32     Fetch cur_old_ref_values INTO old_references;
33     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
34       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35       IGS_GE_MSG_STACK.ADD;
36       App_Exception.Raise_Exception;
37       Close cur_old_ref_values;
38       Return;
39     END IF;
40     Close cur_old_ref_values;
41 
42     new_references.basis_for_admission_type := x_basis_for_admission_type;
43     new_references.description := x_description;
44     new_references.govt_basis_for_adm_type := x_govt_basis_for_adm_type;
45     new_references.closed_ind := x_closed_ind;
46     IF (p_action = 'UPDATE') THEN
47       new_references.creation_date := old_references.creation_date;
48       new_references.created_by := old_references.created_by;
49     ELSE
50       new_references.creation_date := x_creation_date;
51       new_references.created_by := x_created_by;
52     END IF;
53     new_references.last_update_date := x_last_update_date;
54     new_references.last_updated_by := x_last_updated_by;
55     new_references.last_update_login := x_last_update_login;
56 
57   END Set_Column_Values;
58 
59   PROCEDURE BeforeRowInsertUpdate1(
60     p_inserting IN BOOLEAN DEFAULT FALSE,
61     p_updating IN BOOLEAN DEFAULT FALSE,
62     p_deleting IN BOOLEAN DEFAULT FALSE
63     ) as
64 	v_message_name	VARCHAR2(30);
65   BEGIN
66 	-- Validate Government Basis for Admission Type.
67 	IF p_inserting OR ((old_references.govt_basis_for_adm_type <>
68 				 new_references.govt_basis_for_adm_type) OR
69 			(old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N')) THEN
70 		IF IGS_AD_VAL_BFA.admp_val_gbfat_clsd(
71 					new_references.govt_basis_for_adm_type,
72 					v_message_name) = FALSE THEN
73 		         Fnd_Message.Set_Name('IGS',v_message_name);
74 		         IGS_GE_MSG_STACK.ADD;
75                      App_Exception.Raise_Exception;
76 		END IF;
77 	END IF;
78 
79 
80   END BeforeRowInsertUpdate1;
81 
82   PROCEDURE Check_Constraints (
83     Column_Name IN VARCHAR2 DEFAULT NULL,
84     Column_Value IN VARCHAR2 DEFAULT NULL
85   )
86   as
87   BEGIN
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 	ELSIF upper(Column_Name) = 'BASIS_FOR_ADMISSION_TYPE' then
93 		new_references.basis_for_admission_type := column_value;
94 	ELSIF upper(Column_Name) = 'GOVT_BASIS_FOR_ADM_TYPE' then
95 		new_references.govt_basis_for_adm_type := column_value;
96 	END IF;
97 
98 	IF upper(Column_Name) = 'CLOSED_IND' OR Column_Name IS NULL THEN
99 		IF new_references.closed_ind NOT IN ('Y','N') THEN
100 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
101 			IGS_GE_MSG_STACK.ADD;
102 			App_Exception.Raise_Exception;
103 		END IF;
104 	END IF;
105 	IF upper(Column_Name) = 'BASIS_FOR_ADMISSION_TYPE' OR Column_Name IS NULL THEN
106 		IF new_references.basis_for_admission_type <> UPPER(new_references.basis_for_admission_type) THEN
107 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
108 			IGS_GE_MSG_STACK.ADD;
109 			App_Exception.Raise_Exception;
110 		END IF;
111 	END IF;
112 	IF upper(Column_Name) = 'GOVT_BASIS_FOR_ADM_TYPE' OR Column_Name IS NULL THEN
113 		IF new_references.govt_basis_for_adm_type <> UPPER(new_references.govt_basis_for_adm_type) THEN
114 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
115 			IGS_GE_MSG_STACK.ADD;
116 			App_Exception.Raise_Exception;
117 		END IF;
118 	END IF;
119 
120   END Check_Constraints;
121 
122   PROCEDURE Check_Parent_Existance as
123   BEGIN
124 
125     IF (((old_references.govt_basis_for_adm_type = new_references.govt_basis_for_adm_type)) OR
126         ((new_references.govt_basis_for_adm_type IS NULL))) THEN
127       NULL;
128     ELSE
129       IF NOT IGS_AD_GOV_BAS_FR_TY_PKG.Get_PK_For_Validation (
130         new_references.govt_basis_for_adm_type,
131          'N' ) THEN
132 	Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
133 	IGS_GE_MSG_STACK.ADD;
134 	App_Exception.Raise_Exception;
135 	END IF;
136     END IF;
137 
138   END Check_Parent_Existance;
139 
140   PROCEDURE Check_Child_Existance as
141   BEGIN
142 
143     IGS_AD_CD_PKG.GET_FK_IGS_AD_BASIS_FOR_AD (
144       old_references.basis_for_admission_type
145       );
146 
147     IGS_AD_PS_APPL_PKG.GET_FK_IGS_AD_BASIS_FOR_AD (
148       old_references.basis_for_admission_type
149       );
150 
151   END Check_Child_Existance;
152 
153 FUNCTION Get_PK_For_Validation (
154     x_basis_for_admission_type IN VARCHAR2,
155     x_closed_ind IN VARCHAR2
156 )return BOOLEAN as
157 
158     CURSOR cur_rowid IS
159       SELECT   rowid
160       FROM     IGS_AD_BASIS_FOR_AD
161       WHERE    basis_for_admission_type = x_basis_for_admission_type AND
162                closed_ind = NVL(x_closed_ind,closed_ind);
163 
164     lv_rowid cur_rowid%RowType;
165 
166   BEGIN
167 
168     Open cur_rowid;
169     Fetch cur_rowid INTO lv_rowid;
170     IF (cur_rowid%FOUND) THEN
171       Close cur_rowid;
172       Return(TRUE);
173     ELSE
174       Close cur_rowid;
175       Return(FALSE);
176     END IF;
177 
178   END Get_PK_For_Validation;
179 
180   PROCEDURE get_fk_igs_ad_gov_bas_fr_ty (
181     x_govt_basis_for_adm_type IN VARCHAR2
182     ) as
183 
184     CURSOR cur_rowid IS
185       SELECT   rowid
186       FROM     IGS_AD_BASIS_FOR_AD
187       WHERE    govt_basis_for_adm_type = x_govt_basis_for_adm_type ;
188 
189     lv_rowid cur_rowid%RowType;
190 
191   BEGIN
192 
193     Open cur_rowid;
194     Fetch cur_rowid INTO lv_rowid;
195     IF (cur_rowid%FOUND) THEN
196       Close cur_rowid;
197       Fnd_Message.Set_Name ('IGS', 'IGS_AD_BFA_GBFAT_FK');
198       IGS_GE_MSG_STACK.ADD;
199       App_Exception.Raise_Exception;
200       Return;
201     END IF;
202     Close cur_rowid;
203 
204   END get_fk_igs_ad_gov_bas_fr_ty;
205 
206   PROCEDURE Before_DML (
207     p_action IN VARCHAR2,
208     x_rowid IN VARCHAR2 DEFAULT NULL,
209     x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
210     x_description IN VARCHAR2 DEFAULT NULL,
211     x_govt_basis_for_adm_type IN VARCHAR2 DEFAULT NULL,
212     x_closed_ind IN VARCHAR2 DEFAULT NULL,
213     x_creation_date IN DATE DEFAULT NULL,
214     x_created_by IN NUMBER DEFAULT NULL,
215     x_last_update_date IN DATE DEFAULT NULL,
216     x_last_updated_by IN NUMBER DEFAULT NULL,
217     x_last_update_login IN NUMBER DEFAULT NULL
218   ) as
219   BEGIN
220 
221     Set_Column_Values (
222       p_action,
223       x_rowid,
224       x_basis_for_admission_type,
225       x_description,
226       x_govt_basis_for_adm_type,
227       x_closed_ind,
228       x_creation_date,
229       x_created_by,
230       x_last_update_date,
231       x_last_updated_by,
232       x_last_update_login
233     );
234 
235     IF (p_action = 'INSERT') THEN
236       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
237 	IF Get_PK_For_Validation (
238 		new_references.basis_for_admission_type
239 	) THEN
240 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
241 		IGS_GE_MSG_STACK.ADD;
242 		App_Exception.Raise_Exception;
243 	END IF;
244 	  Check_Constraints;
245       Check_Parent_Existance;
246     ELSIF (p_action = 'UPDATE') THEN
247       BeforeRowInsertUpdate1 ( p_updating => TRUE );
248 	  Check_Constraints;
249       Check_Parent_Existance;
250     ELSIF (p_action = 'DELETE') THEN
251       Check_Child_Existance;
252     ELSIF (p_action = 'VALIDATE_INSERT') THEN
253 	  IF Get_PK_For_Validation (
254 		new_references.basis_for_admission_type
255 	  ) THEN
256 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
257 		IGS_GE_MSG_STACK.ADD;
258 		App_Exception.Raise_Exception;
259 	  END IF;
260 	  Check_Constraints;
261     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
262 	  Check_Constraints;
263     ELSIF (p_action = 'VALIDATE_DELETE') THEN
264       Check_Child_Existance;
265     END IF;
266 
267   END Before_DML;
268 
269   PROCEDURE After_DML (
270     p_action IN VARCHAR2,
271     x_rowid IN VARCHAR2
272   ) as
273   BEGIN
274 
275     l_rowid := x_rowid;
276 
277  END After_DML;
278 
279 procedure INSERT_ROW (
280   X_ROWID in out NOCOPY VARCHAR2,
281   X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
282   X_DESCRIPTION in VARCHAR2,
283   X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
284   X_CLOSED_IND in VARCHAR2,
285   X_MODE in VARCHAR2 default 'R'
286   ) as
287     cursor C is select ROWID from IGS_AD_BASIS_FOR_AD
288       where BASIS_FOR_ADMISSION_TYPE = X_BASIS_FOR_ADMISSION_TYPE;
289     X_LAST_UPDATE_DATE DATE;
290     X_LAST_UPDATED_BY NUMBER;
291     X_LAST_UPDATE_LOGIN NUMBER;
292 begin
293   X_LAST_UPDATE_DATE := SYSDATE;
294   if(X_MODE = 'I') then
295     X_LAST_UPDATED_BY := 1;
296     X_LAST_UPDATE_LOGIN := 0;
297   elsif (X_MODE = 'R') then
298     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
299     if X_LAST_UPDATED_BY is NULL then
300       X_LAST_UPDATED_BY := -1;
301     end if;
302     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
303     if X_LAST_UPDATE_LOGIN is NULL then
304       X_LAST_UPDATE_LOGIN := -1;
305     end if;
306   else
307     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
308     IGS_GE_MSG_STACK.ADD;
309     app_exception.raise_exception;
310   end if;
311 
312   Before_DML(
313    p_action=>'INSERT',
314    x_rowid=>X_ROWID,
315    x_basis_for_admission_type=>X_BASIS_FOR_ADMISSION_TYPE,
316    x_closed_ind=>Nvl(X_CLOSED_IND, 'N'),
317    x_description=>X_DESCRIPTION,
318    x_govt_basis_for_adm_type=>X_GOVT_BASIS_FOR_ADM_TYPE,
319    x_creation_date=>X_LAST_UPDATE_DATE,
320    x_created_by=>X_LAST_UPDATED_BY,
321    x_last_update_date=>X_LAST_UPDATE_DATE,
322    x_last_updated_by=>X_LAST_UPDATED_BY,
323    x_last_update_login=>X_LAST_UPDATE_LOGIN
324    );
325 
326   insert into IGS_AD_BASIS_FOR_AD (
327     BASIS_FOR_ADMISSION_TYPE,
328     DESCRIPTION,
329     GOVT_BASIS_FOR_ADM_TYPE,
330     CLOSED_IND,
331     CREATION_DATE,
332     CREATED_BY,
333     LAST_UPDATE_DATE,
334     LAST_UPDATED_BY,
335     LAST_UPDATE_LOGIN
336   ) values (
337     NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
338     NEW_REFERENCES.DESCRIPTION,
339     NEW_REFERENCES.GOVT_BASIS_FOR_ADM_TYPE,
340     NEW_REFERENCES.CLOSED_IND,
341     X_LAST_UPDATE_DATE,
342     X_LAST_UPDATED_BY,
343     X_LAST_UPDATE_DATE,
344     X_LAST_UPDATED_BY,
345     X_LAST_UPDATE_LOGIN
346   );
347 
348   open c;
349   fetch c into X_ROWID;
350   if (c%notfound) then
351     close c;
352     raise no_data_found;
353   end if;
354   close c;
355 
356   After_DML (
357     p_action => 'INSERT',
358     x_rowid => X_ROWID);
359 
360 end INSERT_ROW;
361 
362 procedure LOCK_ROW (
363   X_ROWID in VARCHAR2,
364   X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
365   X_DESCRIPTION in VARCHAR2,
366   X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
367   X_CLOSED_IND in VARCHAR2
368 ) as
369   cursor c1 is select
370       DESCRIPTION,
371       GOVT_BASIS_FOR_ADM_TYPE,
372       CLOSED_IND
373     from IGS_AD_BASIS_FOR_AD
374     where ROWID = X_ROWID for update nowait;
375   tlinfo c1%rowtype;
376 
377 begin
378   open c1;
379   fetch c1 into tlinfo;
380   if (c1%notfound) then
381     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
382 IGS_GE_MSG_STACK.ADD;
383     app_exception.raise_exception;
384     close c1;
385     return;
386   end if;
387   close c1;
388 
389   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
390       AND ((tlinfo.GOVT_BASIS_FOR_ADM_TYPE = X_GOVT_BASIS_FOR_ADM_TYPE)
391            OR ((tlinfo.GOVT_BASIS_FOR_ADM_TYPE is null)
392                AND (X_GOVT_BASIS_FOR_ADM_TYPE is null)))
393       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
394   ) then
395     null;
396   else
397     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
398 IGS_GE_MSG_STACK.ADD;
399     app_exception.raise_exception;
400   end if;
401   return;
402 end LOCK_ROW;
403 
404 procedure UPDATE_ROW (
405   X_ROWID in VARCHAR2,
406   X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
407   X_DESCRIPTION in VARCHAR2,
408   X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
409   X_CLOSED_IND in VARCHAR2,
410   X_MODE in VARCHAR2 default 'R'
411   ) as
412     X_LAST_UPDATE_DATE DATE;
413     X_LAST_UPDATED_BY NUMBER;
414     X_LAST_UPDATE_LOGIN NUMBER;
415 begin
416   X_LAST_UPDATE_DATE := SYSDATE;
417   if(X_MODE = 'I') then
418     X_LAST_UPDATED_BY := 1;
419     X_LAST_UPDATE_LOGIN := 0;
420   elsif (X_MODE = 'R') then
421     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
422     if X_LAST_UPDATED_BY is NULL then
423       X_LAST_UPDATED_BY := -1;
424     end if;
425     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
426     if X_LAST_UPDATE_LOGIN is NULL then
427       X_LAST_UPDATE_LOGIN := -1;
428     end if;
429   else
430     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
431     IGS_GE_MSG_STACK.ADD;
432     app_exception.raise_exception;
433   end if;
434 
435   Before_DML(
436    p_action=>'UPDATE',
437    x_rowid=>X_ROWID,
438    x_basis_for_admission_type=>X_BASIS_FOR_ADMISSION_TYPE,
439    x_closed_ind=>X_CLOSED_IND,
440    x_description=>X_DESCRIPTION,
441    x_govt_basis_for_adm_type=>X_GOVT_BASIS_FOR_ADM_TYPE,
442    x_creation_date=>X_LAST_UPDATE_DATE,
443    x_created_by=>X_LAST_UPDATED_BY,
444    x_last_update_date=>X_LAST_UPDATE_DATE,
445    x_last_updated_by=>X_LAST_UPDATED_BY,
446    x_last_update_login=>X_LAST_UPDATE_LOGIN
447    );
448 
449   update IGS_AD_BASIS_FOR_AD set
450     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
451     GOVT_BASIS_FOR_ADM_TYPE = NEW_REFERENCES.GOVT_BASIS_FOR_ADM_TYPE,
452     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
453     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
454     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
455     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
456   where ROWID = X_ROWID
457   ;
458   if (sql%notfound) then
459     raise no_data_found;
460   end if;
461 
462   After_DML (
463     p_action => 'UPDATE',
464     x_rowid => X_ROWID);
465 
466 end UPDATE_ROW;
467 
468 procedure ADD_ROW (
469   X_ROWID in out NOCOPY VARCHAR2,
470   X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
471   X_DESCRIPTION in VARCHAR2,
472   X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
473   X_CLOSED_IND in VARCHAR2,
474   X_MODE in VARCHAR2 default 'R'
475   ) as
476   cursor c1 is select rowid from IGS_AD_BASIS_FOR_AD
477      where BASIS_FOR_ADMISSION_TYPE = X_BASIS_FOR_ADMISSION_TYPE
478   ;
479 begin
480   open c1;
481   fetch c1 into X_ROWID;
482   if (c1%notfound) then
483     close c1;
484     INSERT_ROW (
485      X_ROWID,
486      X_BASIS_FOR_ADMISSION_TYPE,
487      X_DESCRIPTION,
488      X_GOVT_BASIS_FOR_ADM_TYPE,
489      X_CLOSED_IND,
490      X_MODE);
491     return;
492   end if;
493   close c1;
494   UPDATE_ROW (
495    X_ROWID,
496    X_BASIS_FOR_ADMISSION_TYPE,
497    X_DESCRIPTION,
498    X_GOVT_BASIS_FOR_ADM_TYPE,
499    X_CLOSED_IND,
500    X_MODE);
501 end ADD_ROW;
502 
503 procedure DELETE_ROW (
504   X_ROWID in VARCHAR2
505 ) as
506 begin
507 
508   Before_DML (
509     p_action => 'DELETE',
510     x_rowid => X_ROWID);
511 
512   delete from IGS_AD_BASIS_FOR_AD
513   where ROWID = X_ROWID;
514   if (sql%notfound) then
515     raise no_data_found;
516   end if;
517 
518   After_DML (
519     p_action => 'DELETE',
520     x_rowid => X_ROWID);
521 
522 end DELETE_ROW;
523 
524 end IGS_AD_BASIS_FOR_AD_PKG;