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_GE_S_GEN_CAL_CON_PKG

Source


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