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_TYPE_PKG

Source


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