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_AUS_SEC_ED_SC_PKG

Source


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