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_COUNTRYCD_PKG

Source


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