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_PE_GOV_ABRGRESCD_PKG

Source


1 package body IGS_PE_GOV_ABRGRESCD_PKG as
2  /* $Header: IGSNI05B.pls 115.3 2002/11/29 01:14:50 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PE_GOV_ABRGRESCD%RowType;
6   new_references IGS_PE_GOV_ABRGRESCD%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_govt_aborig_torres_cd IN NUMBER DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_closed_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_PE_GOV_ABRGRESCD
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       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36        IGS_GE_MSG_STACK.ADD;
37       Close cur_old_ref_values;
38       App_Exception.Raise_Exception;
39       Return;
40     END IF;
41     Close cur_old_ref_values;
42 
43     -- Populate New Values.
44     new_references.govt_aborig_torres_cd := x_govt_aborig_torres_cd;
45     new_references.description := x_description;
46     new_references.closed_ind := x_closed_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 	-- Set audit details.
68 
69 	-- If being closed, validate aborig/torres codes.
70 	IF p_updating AND
71 	   old_references.closed_ind <> new_references.closed_ind THEN
72 		IF IGS_EN_VAL_GATC.enrp_val_gatc_upd (
73 				new_references.govt_aborig_torres_cd,
74 				new_references.closed_ind,
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 
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) = 'CLOSED_IND' then
95      new_references.closed_ind := column_value;
96  ELSIF upper(Column_name) = 'GOVT_ABORIG_TORRES_CD' then
97      new_references.govt_aborig_torres_cd := IGS_GE_NUMBER.to_num(column_value);
98 END IF;
99 
100 IF upper(column_name) = 'CLOSED_IND' OR
101      column_name is null Then
102      IF  new_references.closed_ind   NOT IN ( 'Y' , 'N' )  Then
103        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
104        IGS_GE_MSG_STACK.ADD;
105        App_Exception.Raise_Exception;
106                    END IF;
107               END IF;
108 
109 IF upper(column_name) = 'GOVT_ABORIG_TORRES_CD' OR
110      column_name is null Then
111      IF new_references.govt_aborig_torres_cd < 0 OR
112           new_references. govt_aborig_torres_cd > 9 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   PROCEDURE Check_Child_Existance as
121   BEGIN
122 
123     IGS_PE_ABORG_TORESCD_PKG.GET_FK_IGS_PE_GOV_ABRGRESCD (
124       old_references.govt_aborig_torres_cd
125       );
126 
127   END Check_Child_Existance;
128 
129   FUNCTION Get_PK_For_Validation (
130     x_govt_aborig_torres_cd IN NUMBER
131     ) RETURN BOOLEAN as
132 
133     CURSOR cur_rowid IS
134       SELECT   rowid
135       FROM     IGS_PE_GOV_ABRGRESCD
136       WHERE    govt_aborig_torres_cd = x_govt_aborig_torres_cd
137       FOR UPDATE NOWAIT;
138 
139     lv_rowid cur_rowid%RowType;
140 
141   BEGIN
142 
143     Open cur_rowid;
144     Fetch cur_rowid INTO lv_rowid;
145      IF (cur_rowid%FOUND) THEN
146        Close cur_rowid;
147        Return (TRUE);
148  ELSE
149        Close cur_rowid;
150        Return (FALSE);
151  END IF;
152 
153   END Get_PK_For_Validation;
154 
155   PROCEDURE Before_DML (
156     p_action IN VARCHAR2,
157     x_rowid IN  VARCHAR2 DEFAULT NULL,
158     x_govt_aborig_torres_cd IN NUMBER DEFAULT NULL,
159     x_description IN VARCHAR2 DEFAULT NULL,
160     x_closed_ind IN VARCHAR2 DEFAULT NULL,
161     x_creation_date IN DATE DEFAULT NULL,
162     x_created_by IN NUMBER DEFAULT NULL,
163     x_last_update_date IN DATE DEFAULT NULL,
164     x_last_updated_by IN NUMBER DEFAULT NULL,
165     x_last_update_login IN NUMBER DEFAULT NULL
166   ) as
167   BEGIN
168 
169     Set_Column_Values (
170       p_action,
171       x_rowid,
172       x_govt_aborig_torres_cd,
173       x_description,
174       x_closed_ind,
175       x_creation_date,
176       x_created_by,
177       x_last_update_date,
178       x_last_updated_by,
179       x_last_update_login
180     );
181 
182     IF (p_action = 'INSERT') THEN
183        -- Call all the procedures related to Before Insert.
184      BeforeRowInsertUpdate1 ( p_inserting => TRUE );
185       IF  Get_PK_For_Validation (
186           new_references.govt_aborig_torres_cd ) THEN
187          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
188          IGS_GE_MSG_STACK.ADD;
189           App_Exception.Raise_Exception;
190       END IF;
191 
192       Check_Constraints; -- if procedure present
193 
194  ELSIF (p_action = 'UPDATE') THEN
195        -- Call all the procedures related to Before Update.
196        BeforeRowInsertUpdate1 ( p_updating => TRUE );
197 
198        Check_Constraints; -- if procedure present
199 
200  ELSIF (p_action = 'DELETE') THEN
201        -- Call all the procedures related to Before Delete.
202 
203        Check_Child_Existance; -- if procedure present
204  ELSIF (p_action = 'VALIDATE_INSERT') THEN
205       IF  Get_PK_For_Validation (
206           new_references.govt_aborig_torres_cd ) THEN
207          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
208          IGS_GE_MSG_STACK.ADD;
209           App_Exception.Raise_Exception;
210       END IF;
211        Check_Constraints; -- if procedure present
212  ELSIF (p_action = 'VALIDATE_UPDATE') THEN
213 
214        Check_Constraints; -- if procedure present
215 
216 ELSIF (p_action = 'VALIDATE_DELETE') THEN
217       Check_Child_Existance; -- if procedure present
218  END IF;
219 
220   END Before_DML;
221 
222   PROCEDURE After_DML (
223     p_action IN VARCHAR2,
224     x_rowid IN VARCHAR2
225   ) as
226   BEGIN
227 
228     l_rowid := x_rowid;
229 
230     IF (p_action = 'INSERT') THEN
231       -- Call all the procedures related to After Insert.
232       Null;
233     ELSIF (p_action = 'UPDATE') THEN
234       -- Call all the procedures related to After Update.
235       Null;
236     ELSIF (p_action = 'DELETE') THEN
237       -- Call all the procedures related to After Delete.
238       Null;
239     END IF;
240 
241   END After_DML;
242 
243 procedure INSERT_ROW (
244   X_ROWID in out NOCOPY VARCHAR2,
245   X_GOVT_ABORIG_TORRES_CD in NUMBER,
246   X_DESCRIPTION in VARCHAR2,
247   X_CLOSED_IND in VARCHAR2,
248   X_MODE in VARCHAR2 default 'R'
249   ) as
250     cursor C is select ROWID from IGS_PE_GOV_ABRGRESCD
251       where GOVT_ABORIG_TORRES_CD = X_GOVT_ABORIG_TORRES_CD;
252     X_LAST_UPDATE_DATE DATE;
253     X_LAST_UPDATED_BY NUMBER;
254     X_LAST_UPDATE_LOGIN NUMBER;
255 begin
256   X_LAST_UPDATE_DATE := SYSDATE;
257   if(X_MODE = 'I') then
258     X_LAST_UPDATED_BY := 1;
259     X_LAST_UPDATE_LOGIN := 0;
260   elsif (X_MODE = 'R') then
261     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
262     if X_LAST_UPDATED_BY is NULL then
263       X_LAST_UPDATED_BY := -1;
264     end if;
265     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
266     if X_LAST_UPDATE_LOGIN is NULL then
267       X_LAST_UPDATE_LOGIN := -1;
268     end if;
269   else
270     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
271     IGS_GE_MSG_STACK.ADD;
272     app_exception.raise_exception;
273   end if;
274    Before_DML(
275   p_action=>'INSERT',
276   x_rowid=>X_ROWID,
277   x_closed_ind=> NVL(X_CLOSED_IND,'N'),
278   x_description=>X_DESCRIPTION,
279   x_govt_aborig_torres_cd=>X_GOVT_ABORIG_TORRES_CD,
280   x_creation_date=>X_LAST_UPDATE_DATE,
281   x_created_by=>X_LAST_UPDATED_BY,
282   x_last_update_date=>X_LAST_UPDATE_DATE,
283   x_last_updated_by=>X_LAST_UPDATED_BY,
284   x_last_update_login=>X_LAST_UPDATE_LOGIN
285   );
286 
287   insert into IGS_PE_GOV_ABRGRESCD (
288     GOVT_ABORIG_TORRES_CD,
289     DESCRIPTION,
290     CLOSED_IND,
291     CREATION_DATE,
292     CREATED_BY,
293     LAST_UPDATE_DATE,
294     LAST_UPDATED_BY,
295     LAST_UPDATE_LOGIN
296   ) values (
297     NEW_REFERENCES.GOVT_ABORIG_TORRES_CD,
298     NEW_REFERENCES.DESCRIPTION,
299     NEW_REFERENCES.CLOSED_IND,
300     X_LAST_UPDATE_DATE,
301     X_LAST_UPDATED_BY,
302     X_LAST_UPDATE_DATE,
303     X_LAST_UPDATED_BY,
304     X_LAST_UPDATE_LOGIN
305   );
306 
307   open c;
308   fetch c into X_ROWID;
309   if (c%notfound) then
310     close c;
311     raise no_data_found;
312   end if;
313   close c;
314  After_DML(
315   p_action => 'INSERT',
316   x_rowid => X_ROWID
317   );
318 end INSERT_ROW;
319 
320 procedure LOCK_ROW (
321   X_ROWID in VARCHAR2,
322   X_GOVT_ABORIG_TORRES_CD in NUMBER,
323   X_DESCRIPTION in VARCHAR2,
324   X_CLOSED_IND in VARCHAR2
325 ) as
326   cursor c1 is select
327       DESCRIPTION,
328       CLOSED_IND
329     from IGS_PE_GOV_ABRGRESCD
330     where ROWID = X_ROWID
331     for update nowait;
332   tlinfo c1%rowtype;
333 
334 begin
335   open c1;
336   fetch c1 into tlinfo;
337   if (c1%notfound) then
338     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
339 
340     close c1;
341     App_Exception.Raise_Exception;
342     return;
343   end if;
344   close c1;
345 
346   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
347       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
348   ) then
349     null;
350   else
351     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
352     app_exception.raise_exception;
353   end if;
354   return;
355 end LOCK_ROW;
356 
357 procedure UPDATE_ROW (
358   X_ROWID in VARCHAR2,
359   X_GOVT_ABORIG_TORRES_CD in NUMBER,
360   X_DESCRIPTION in VARCHAR2,
361   X_CLOSED_IND in VARCHAR2,
362   X_MODE in VARCHAR2 default 'R'
363   ) as
364     X_LAST_UPDATE_DATE DATE;
365     X_LAST_UPDATED_BY NUMBER;
366     X_LAST_UPDATE_LOGIN NUMBER;
367 begin
368   X_LAST_UPDATE_DATE := SYSDATE;
369   if(X_MODE = 'I') then
370     X_LAST_UPDATED_BY := 1;
371     X_LAST_UPDATE_LOGIN := 0;
372   elsif (X_MODE = 'R') then
373     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
374     if X_LAST_UPDATED_BY is NULL then
375       X_LAST_UPDATED_BY := -1;
376     end if;
377     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
378     if X_LAST_UPDATE_LOGIN is NULL then
379       X_LAST_UPDATE_LOGIN := -1;
380     end if;
381   else
382     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
383     IGS_GE_MSG_STACK.ADD;
384     app_exception.raise_exception;
385   end if;
386 Before_DML(
387  p_action=>'UPDATE',
388  x_rowid=>X_ROWID,
389  x_closed_ind=>X_CLOSED_IND,
390  x_description=>X_DESCRIPTION,
391  x_govt_aborig_torres_cd=>X_GOVT_ABORIG_TORRES_CD,
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   update IGS_PE_GOV_ABRGRESCD set
399     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
400     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
401     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
402     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
403     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
404   where ROWID = X_ROWID
405   ;
406   if (sql%notfound) then
407     raise no_data_found;
408   end if;
409 After_DML(
410   p_action => 'UPDATE',
411   x_rowid => X_ROWID
412   );
413 end UPDATE_ROW;
414 
415 procedure ADD_ROW (
416   X_ROWID in out NOCOPY VARCHAR2,
417   X_GOVT_ABORIG_TORRES_CD in NUMBER,
418   X_DESCRIPTION in VARCHAR2,
419   X_CLOSED_IND in VARCHAR2,
420   X_MODE in VARCHAR2 default 'R'
421   ) as
422   cursor c1 is select rowid from IGS_PE_GOV_ABRGRESCD
423      where GOVT_ABORIG_TORRES_CD = X_GOVT_ABORIG_TORRES_CD
424   ;
425 
426 begin
427   open c1;
428   fetch c1 into X_ROWID;
429   if (c1%notfound) then
430     close c1;
431     INSERT_ROW (
432      X_ROWID,
433      X_GOVT_ABORIG_TORRES_CD,
434      X_DESCRIPTION,
435      X_CLOSED_IND,
436      X_MODE);
437     return;
438   end if;
439   close c1;
440   UPDATE_ROW (
441    X_ROWID,
442    X_GOVT_ABORIG_TORRES_CD,
443    X_DESCRIPTION,
444    X_CLOSED_IND,
445    X_MODE);
446 end ADD_ROW;
447 
448 procedure DELETE_ROW (
449  X_ROWID in VARCHAR2
450 ) as
451 begin
452   Before_DML(
453   p_action => 'DELETE',
454   x_rowid => X_ROWID
455   );
456   delete from IGS_PE_GOV_ABRGRESCD
457   where ROWID = X_ROWID;
458   if (sql%notfound) then
459     raise no_data_found;
460   end if;
461 After_DML(
462   p_action => 'DELETE',
463   x_rowid => X_ROWID
464   );
465 end DELETE_ROW;
466 
467 end IGS_PE_GOV_ABRGRESCD_PKG;