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_RE_S_RES_CAL_CON_PKG

Source


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