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_LOCATION_REL_PKG

Source


1 package body IGS_AD_LOCATION_REL_PKG as
2 /* $Header: IGSAI44B.pls 115.4 2003/10/30 13:20:39 rghosh ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_LOCATION_REL%RowType;
6   new_references IGS_AD_LOCATION_REL%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_location_cd IN VARCHAR2 DEFAULT NULL,
12     x_sub_location_cd IN VARCHAR2 DEFAULT NULL,
13     x_dflt_ind 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_AD_LOCATION_REL
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.location_cd := x_location_cd;
45     new_references.sub_location_cd := x_sub_location_cd;
46     new_references.dflt_ind := x_dflt_ind;
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 BeforeRowInsertUpdate1(
61     p_inserting IN BOOLEAN DEFAULT FALSE,
62     p_updating IN BOOLEAN DEFAULT FALSE,
63     p_deleting IN BOOLEAN DEFAULT FALSE
64     ) AS
65 	v_message_name	varchar2(30);
66   BEGIN
67 	-- Validate that inserts are allowed
68 	IF  p_inserting OR p_updating THEN
69 		--<Start lr2>
70 		-- Cannot make a campus (s_location_type = 'CAMPUS') a child of
71 		-- an exam centre (s_location_type = 'EXAM_CTR')
72 		IF  IGS_OR_VAL_LR.assp_val_lr_lr (
73 						new_references.location_cd,
74 						new_references.sub_location_cd,
75 						v_message_name) = FALSE THEN
76 			Fnd_Message.Set_Name('IGS',v_message_name);
77 			IGS_GE_MSG_STACK.ADD;
78                      App_Exception.Raise_Exception;
79 		END IF;
80 	END IF;
81 
82 
83   END BeforeRowInsertUpdate1;
84 
85   PROCEDURE AfterRowInsertUpdate2(
86     p_inserting IN BOOLEAN DEFAULT FALSE,
87     p_updating IN BOOLEAN DEFAULT FALSE,
88     p_deleting IN BOOLEAN DEFAULT FALSE
89     ) AS
90 	v_message_name	varchar2(30);
91 	v_rowid_saved	BOOLEAN := FALSE;
92   BEGIN
93 
94 	-- Validate location relationship.
95   		IF IGS_OR_VAL_LR.orgp_val_lr (new_references.location_cd,
96   				new_references.sub_location_cd,
97   				v_message_name) = FALSE THEN
98   			Fnd_Message.Set_Name('IGS',v_message_name);
99   			IGS_GE_MSG_STACK.ADD;
100                      App_Exception.Raise_Exception;
101   		END IF;
102   		--<Start lr1>
103   		-- Can only set default indicator when parent location is a campus and
104   		-- child location is an exam location. This is not really a mutating
105   		-- trigger but as <lr3> depends on the result and <lr3> is mutating then
106   		-- handle as such.
107   		IF  IGS_OR_VAL_LR.assp_val_lr_dfltslot (
108   						new_references.location_cd,
109   						new_references.sub_location_cd,
110   						new_references.dflt_ind,
111   						v_message_name) = FALSE THEN
112   		     Fnd_Message.Set_Name('IGS',v_message_name);
113   		     IGS_GE_MSG_STACK.ADD;
114                      App_Exception.Raise_Exception;
115   		END IF;
116 
117 
118 	-- Validate IGS_AD_LOCATION relationship.
119 	 -- Save the rowid of the current row.
120 	--IGS_OR_VAL_LR.genp_set_rowid(l_rowid);
121 	--v_rowid_saved := TRUE;   /* This line was commented becuase the variable v_rowid_saved is to be set true */
122                                  /* only if the record has been inserted into the pl/sql table */
123 	-- Cannot call orgp_val_lr because trigger mayl be mutating.
124 
125 
126   END AfterRowInsertUpdate2;
127 
128   procedure Check_Constraints (
129     Column_Name IN VARCHAR2 DEFAULT NULL,
130     Column_Value IN VARCHAR2 DEFAULT NULL
131   )
132   AS
133   BEGIN
134 	IF Column_Name is null then
135 		NULL;
136 	ELSIF upper(Column_Name) = 'DFLT_IND' then
137 		new_references.dflt_ind := column_value;
138 	ELSIF upper(Column_Name) = 'LOCATION_CD' then
139 		new_references.location_cd := column_value;
140 	ELSIF upper(Column_Name) = 'SUB_LOCATION_CD' then
141 		new_references.sub_location_cd := column_value;
142 	END IF;
143 
144 	IF upper(Column_Name) = 'DFLT_IND' OR Column_Name IS NULL THEN
145 		IF new_references.dflt_ind NOT IN ('Y','N') THEN
146 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
147 			IGS_GE_MSG_STACK.ADD;
148 			App_Exception.Raise_Exception;
149 		END IF;
150 	END IF;
151 	IF upper(Column_Name) = 'LOCATION_CD' OR Column_Name IS NULL THEN
152 		IF new_references.location_cd <> UPPER(new_references.location_cd) THEN
153 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
154 			IGS_GE_MSG_STACK.ADD;
155 			App_Exception.Raise_Exception;
156 		END IF;
157 	END IF;
158 	IF upper(Column_Name) = 'SUB_LOCATION_CD' OR Column_Name IS NULL THEN
159 		IF new_references.sub_location_cd <> UPPER(new_references.sub_location_cd) THEN
160 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
161 			IGS_GE_MSG_STACK.ADD;
162 			App_Exception.Raise_Exception;
163 		END IF;
164 	END IF;
165 
166   END Check_Constraints;
167 
168   PROCEDURE Check_Parent_Existance AS
169   BEGIN
170 
171     IF (((old_references.location_cd = new_references.location_cd)) OR
172         ((new_references.location_cd IS NULL))) THEN
173       NULL;
174     ELSE
175       IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
176         new_references.location_cd,
177         'N'
178 	) THEN
179 	Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
180 	IGS_GE_MSG_STACK.ADD;
181 	App_Exception.Raise_Exception;
182 	END IF;
183     END IF;
184 
185     IF (((old_references.sub_location_cd = new_references.sub_location_cd)) OR
186         ((new_references.sub_location_cd IS NULL))) THEN
187       NULL;
188     ELSE
189       IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
190         new_references.sub_location_cd,
191         'N'
192 	) THEN
193 	Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
194 	IGS_GE_MSG_STACK.ADD;
195 	App_Exception.Raise_Exception;
196 	END IF;
197     END IF;
198 
199   END Check_Parent_Existance;
200 
201 FUNCTION Get_PK_For_Validation (
202     x_location_cd IN VARCHAR2,
203     x_sub_location_cd IN VARCHAR2
204 )return BOOLEAN AS
205 
206     CURSOR cur_rowid IS
207       SELECT   rowid
208       FROM     IGS_AD_LOCATION_REL
209       WHERE    location_cd = x_location_cd
210       AND      sub_location_cd = x_sub_location_cd
211       FOR UPDATE NOWAIT;
212 
213     lv_rowid cur_rowid%RowType;
214 
215   BEGIN
216 
217     Open cur_rowid;
218     Fetch cur_rowid INTO lv_rowid;
219     IF (cur_rowid%FOUND) THEN
220       Close cur_rowid;
221       Return(TRUE);
222     ELSE
223       Close cur_rowid;
224       Return(FALSE);
225     END IF;
226 
227   END Get_PK_For_Validation;
228 
229   PROCEDURE GET_FK_IGS_AD_LOCATION (
230     x_location_cd IN VARCHAR2
231     ) AS
232 
233     CURSOR cur_rowid IS
234       SELECT   rowid
235       FROM     IGS_AD_LOCATION_REL
236       WHERE    location_cd = x_location_cd
237          OR    sub_location_cd = x_location_cd ;
238 
239     lv_rowid cur_rowid%RowType;
240 
241   BEGIN
242 
243     Open cur_rowid;
244     Fetch cur_rowid INTO lv_rowid;
245     IF (cur_rowid%FOUND) THEN
246       Close cur_rowid;
247       Fnd_Message.Set_Name ('IGS', 'IGS_AD_LR_LOC_FK');
248       IGS_GE_MSG_STACK.ADD;
249       App_Exception.Raise_Exception;
250       Return;
251     END IF;
252     Close cur_rowid;
253 
254   END GET_FK_IGS_AD_LOCATION;
255 
256 
257   PROCEDURE Before_DML (
258     p_action IN VARCHAR2,
259     x_rowid IN  VARCHAR2 DEFAULT NULL,
260     x_location_cd IN VARCHAR2 DEFAULT NULL,
261     x_sub_location_cd IN VARCHAR2 DEFAULT NULL,
262     x_dflt_ind IN VARCHAR2 DEFAULT NULL,
263     x_creation_date IN DATE DEFAULT NULL,
264     x_created_by IN NUMBER DEFAULT NULL,
265     x_last_update_date IN DATE DEFAULT NULL,
266     x_last_updated_by IN NUMBER DEFAULT NULL,
267     x_last_update_login IN NUMBER DEFAULT NULL
268   ) AS
269   BEGIN
270 
271     Set_Column_Values (
272       p_action,
273       x_rowid,
274       x_location_cd,
275       x_sub_location_cd,
276       x_dflt_ind,
277       x_creation_date,
278       x_created_by,
279       x_last_update_date,
280       x_last_updated_by,
281       x_last_update_login
282     );
283 
284     IF (p_action = 'INSERT') THEN
285       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
286 	IF Get_PK_For_Validation (
287 		new_references.location_cd,
288 		new_references.sub_location_cd
289 	) THEN
290 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
291 		IGS_GE_MSG_STACK.ADD;
292 		App_Exception.Raise_Exception;
293 	END IF;
294 	Check_Constraints;
295       Check_Parent_Existance;
296     ELSIF (p_action = 'UPDATE') THEN
297       BeforeRowInsertUpdate1 ( p_updating => TRUE );
298 	Check_Constraints;
299       Check_Parent_Existance;
300     ELSIF (p_action = 'VALIDATE_INSERT') THEN
301 	IF Get_PK_For_Validation (
302 		new_references.location_cd,
303 		new_references.sub_location_cd
304 	) THEN
305 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
306 		IGS_GE_MSG_STACK.ADD;
307 		App_Exception.Raise_Exception;
308 	END IF;
309 	Check_Constraints;
310     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
311 	Check_Constraints;
312     END IF;
313 
314   END Before_DML;
315 
316   PROCEDURE After_DML (
317     p_action IN VARCHAR2,
318     x_rowid IN VARCHAR2
319   ) AS
320   BEGIN
321 
322     l_rowid := x_rowid;
323 
324     IF (p_action = 'INSERT') THEN
325       AfterRowInsertUpdate2 ( p_inserting => TRUE );
326     ELSIF (p_action = 'UPDATE') THEN
327       AfterRowInsertUpdate2 ( p_updating => TRUE );
328       --AfterStmtInsertUpdate3 ( p_updating => TRUE );
329     END IF;
330 
331   END After_DML;
332 
333 
334 procedure INSERT_ROW (
335   X_ROWID in out NOCOPY VARCHAR2,
336   X_LOCATION_CD in VARCHAR2,
337   X_SUB_LOCATION_CD in VARCHAR2,
338   X_DFLT_IND in VARCHAR2,
339   X_MODE in VARCHAR2 default 'R'
340   ) AS
341     cursor C is select ROWID from IGS_AD_LOCATION_REL
342       where LOCATION_CD = X_LOCATION_CD
343       and SUB_LOCATION_CD = X_SUB_LOCATION_CD;
344     X_LAST_UPDATE_DATE DATE;
345     X_LAST_UPDATED_BY NUMBER;
346     X_LAST_UPDATE_LOGIN NUMBER;
347 begin
348   X_LAST_UPDATE_DATE := SYSDATE;
349   if(X_MODE = 'I') then
350     X_LAST_UPDATED_BY := 1;
351     X_LAST_UPDATE_LOGIN := 0;
352   elsif (X_MODE = 'R') then
353     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
354     if X_LAST_UPDATED_BY is NULL then
355       X_LAST_UPDATED_BY := -1;
356     end if;
357     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
358     if X_LAST_UPDATE_LOGIN is NULL then
359       X_LAST_UPDATE_LOGIN := -1;
360     end if;
361   else
362     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
363     IGS_GE_MSG_STACK.ADD;
364     app_exception.raise_exception;
365   end if;
366 
367    Before_DML(
368     p_action=>'INSERT' ,
369     x_rowid=>X_ROWID ,
370     x_location_cd => X_LOCATION_CD ,
371     x_sub_location_cd => X_SUB_LOCATION_CD ,
372     x_dflt_ind => NVL(X_DFLT_IND,'N') ,
373     x_creation_date=>X_LAST_UPDATE_DATE ,
374     x_created_by=>X_LAST_UPDATED_BY ,
375     x_last_update_date=>X_LAST_UPDATE_DATE ,
376     x_last_updated_by=>X_LAST_UPDATED_BY ,
377     x_last_update_login=> X_LAST_UPDATE_LOGIN
378        );
379 
380 
381   insert into IGS_AD_LOCATION_REL (
382     LOCATION_CD,
383     SUB_LOCATION_CD,
384     DFLT_IND,
385     CREATION_DATE,
386     CREATED_BY,
387     LAST_UPDATE_DATE,
388     LAST_UPDATED_BY,
389     LAST_UPDATE_LOGIN
390   ) values (
391     NEW_REFERENCES.LOCATION_CD,
392     NEW_REFERENCES.SUB_LOCATION_CD,
393     NEW_REFERENCES.DFLT_IND,
394     X_LAST_UPDATE_DATE,
395     X_LAST_UPDATED_BY,
396     X_LAST_UPDATE_DATE,
397     X_LAST_UPDATED_BY,
398     X_LAST_UPDATE_LOGIN
399   );
400 
401   open c;
402   fetch c into X_ROWID;
403   if (c%notfound) then
404     close c;
405     raise no_data_found;
406   end if;
407   close c;
408 
409 After_DML(
410   p_action=>'INSERT',
411   x_rowid=> X_ROWID
412          );
413 
414 end INSERT_ROW;
415 
416 procedure LOCK_ROW (
417   X_ROWID in VARCHAR2 ,
418   X_LOCATION_CD in VARCHAR2,
419   X_SUB_LOCATION_CD in VARCHAR2,
420   X_DFLT_IND in VARCHAR2
421 ) AS
422   cursor c1 is select
423       DFLT_IND
424     from IGS_AD_LOCATION_REL
425     WHERE  ROWID = X_ROWID  for update nowait ;
426   tlinfo c1%rowtype;
427 
428 begin
429   open c1;
430   fetch c1 into tlinfo;
431   if (c1%notfound) then
432     close c1;
433     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
434     IGS_GE_MSG_STACK.ADD;
435     app_exception.raise_exception;
436     return;
437   end if;
438   close c1;
439 
440   if ( (tlinfo.DFLT_IND = X_DFLT_IND)
441   ) then
442     null;
443   else
444     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
445     IGS_GE_MSG_STACK.ADD;
446     app_exception.raise_exception;
447   end if;
448   return;
449 end LOCK_ROW;
450 
451 procedure UPDATE_ROW (
452   X_ROWID in VARCHAR2 ,
453   X_LOCATION_CD in VARCHAR2,
454   X_SUB_LOCATION_CD in VARCHAR2,
455   X_DFLT_IND in VARCHAR2,
456   X_MODE in VARCHAR2 default 'R'
457   ) AS
458     X_LAST_UPDATE_DATE DATE;
459     X_LAST_UPDATED_BY NUMBER;
460     X_LAST_UPDATE_LOGIN NUMBER;
461 begin
462   X_LAST_UPDATE_DATE := SYSDATE;
463   if(X_MODE = 'I') then
464     X_LAST_UPDATED_BY := 1;
465     X_LAST_UPDATE_LOGIN := 0;
466   elsif (X_MODE = 'R') then
467     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
468     if X_LAST_UPDATED_BY is NULL then
469       X_LAST_UPDATED_BY := -1;
470     end if;
471     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
472     if X_LAST_UPDATE_LOGIN is NULL then
473       X_LAST_UPDATE_LOGIN := -1;
474     end if;
475   else
476     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
477     IGS_GE_MSG_STACK.ADD;
478     app_exception.raise_exception;
479   end if;
480 
481  Before_DML(
482     p_action=>'UPDATE' ,
483     x_rowid=>X_ROWID ,
484     x_location_cd => X_LOCATION_CD ,
485     x_sub_location_cd => X_SUB_LOCATION_CD ,
486     x_dflt_ind => X_DFLT_IND ,
487     x_creation_date=>X_LAST_UPDATE_DATE ,
488     x_created_by=>X_LAST_UPDATED_BY ,
489     x_last_update_date=>X_LAST_UPDATE_DATE ,
490     x_last_updated_by=>X_LAST_UPDATED_BY ,
491     x_last_update_login=> X_LAST_UPDATE_LOGIN
492        );
493 
494 
495   update IGS_AD_LOCATION_REL set
496     DFLT_IND = NEW_REFERENCES.DFLT_IND,
497     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
498     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
499     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
500   where ROWID = X_ROWID  ;
501   if (sql%notfound) then
502     raise no_data_found;
503   end if;
504 
505 
506 After_DML(
507   p_action=>'UPDATE',
508   x_rowid=> X_ROWID
509          );
510 
511 end UPDATE_ROW;
512 
513 procedure ADD_ROW (
514   X_ROWID in out NOCOPY VARCHAR2,
515   X_LOCATION_CD in VARCHAR2,
516   X_SUB_LOCATION_CD in VARCHAR2,
517   X_DFLT_IND in VARCHAR2,
518   X_MODE in VARCHAR2 default 'R'
519   ) AS
520   cursor c1 is select rowid from IGS_AD_LOCATION_REL
521      where LOCATION_CD = X_LOCATION_CD
522      and SUB_LOCATION_CD = X_SUB_LOCATION_CD
523   ;
524 
525 begin
526   open c1;
527   fetch c1 into X_ROWID;
528   if (c1%notfound) then
529     close c1;
530     INSERT_ROW (
531      X_ROWID,
532      X_LOCATION_CD,
533      X_SUB_LOCATION_CD,
534      X_DFLT_IND,
535      X_MODE);
536     return;
537   end if;
538   close c1;
539   UPDATE_ROW (
540    X_ROWID  ,
541    X_LOCATION_CD,
542    X_SUB_LOCATION_CD,
543    X_DFLT_IND,
544    X_MODE);
545 end ADD_ROW;
546 
547 procedure DELETE_ROW (
548   X_ROWID in VARCHAR2
549 ) AS
550 begin
551 
552  Before_DML(
553   p_action=>'DELETE',
554   x_rowid=> X_ROWID
555          );
556 
557   delete from IGS_AD_LOCATION_REL
558   where  ROWID = X_ROWID;
559   if (sql%notfound) then
560     raise no_data_found;
561   end if;
562 
563 
564  After_DML(
565   p_action=>'DELETE',
566   x_rowid=> X_ROWID
567          );
568 
569 end DELETE_ROW;
570 
571 end IGS_AD_LOCATION_REL_PKG;