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_LANG_CD_PKG

Source


1 package body IGS_PE_GOV_LANG_CD_PKG AS
2  /* $Header: IGSNI08B.pls 115.3 2002/11/29 01:15:45 nsidana ship $ */
3 
4 
5 
6 
7 l_rowid VARCHAR2(25);
8 
9   old_references IGS_PE_GOV_LANG_CD%RowType;
10 
11   new_references IGS_PE_GOV_LANG_CD%RowType;
12 
13 
14 
15 PROCEDURE Set_Column_Values (
16 
17     p_action IN VARCHAR2,
18 
19     x_rowid IN VARCHAR2 DEFAULT NULL,
20 
21     x_govt_language_cd IN NUMBER DEFAULT NULL,
22 
23     x_description IN VARCHAR2 DEFAULT NULL,
24 
25     x_closed_ind IN VARCHAR2 DEFAULT NULL,
26 
27     x_creation_date IN DATE DEFAULT NULL,
28 
29     x_created_by IN NUMBER DEFAULT NULL,
30 
31     x_last_update_date IN DATE DEFAULT NULL,
32 
33     x_last_updated_by IN NUMBER DEFAULT NULL,
34 
35     x_last_update_login IN NUMBER DEFAULT NULL
36 
37   ) AS
38 
39 
40 
41     CURSOR cur_old_ref_values IS
42 
43       SELECT   *
44 
45       FROM     IGS_PE_GOV_LANG_CD
46 
47       WHERE    rowid = x_rowid;
48 
49 
50 
51   BEGIN
52 
53 
54 
55     l_rowid := x_rowid;
56 
57 
58 
59     -- Code for setting the Old and New Reference Values.
60 
61     -- Populate Old Values.
62 
63     Open cur_old_ref_values;
64 
65     Fetch cur_old_ref_values INTO old_references;
66 
67     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
68 
69       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
70         IGS_GE_MSG_STACK.ADD;
71 
72 
73       Close cur_old_ref_values;
74       App_Exception.Raise_Exception;
75       Return;
76 
77     END IF;
78 
79     Close cur_old_ref_values;
80 
81 
82 
83     -- Populate New Values.
84 
85     new_references.govt_language_cd := x_govt_language_cd;
86 
87     new_references.description := x_description;
88 
89     new_references.closed_ind := x_closed_ind;
90 
91     IF (p_action = 'UPDATE') THEN
92 
93       new_references.creation_date := old_references.creation_date;
94 
95       new_references.created_by := old_references.created_by;
96 
97     ELSE
98 
99       new_references.creation_date := x_creation_date;
100 
101       new_references.created_by := x_created_by;
102 
103     END IF;
104 
105     new_references.last_update_date := x_last_update_date;
106 
107     new_references.last_updated_by := x_last_updated_by;
108 
109     new_references.last_update_login := x_last_update_login;
110 
111 
112 
113   END Set_Column_Values;
114 
115 
116 
117 PROCEDURE BeforeRowInsertUpdate1(
118 
119     p_inserting IN BOOLEAN DEFAULT FALSE,
120 
121     p_updating IN BOOLEAN DEFAULT FALSE,
122 
123     p_deleting IN BOOLEAN DEFAULT FALSE
124 
125     ) AS
126 
127 	v_message_name  varchar2(30);
128 
129   BEGIN
130 
131 	-- Set audit details.
132 
133 
134 
135 	-- If being closed, validate language codes.
136 
137 	IF p_updating AND
138 
139 	   old_references.closed_ind <> new_references.closed_ind THEN
140 
141 		IF IGS_EN_VAL_GLC.enrp_val_glc_upd (
142 
143 				new_references.govt_language_cd,
144 
145 				new_references.closed_ind,
146 
147 				v_message_name ) = FALSE THEN
148 
149 			Fnd_Message.Set_Name('IGS', v_message_name);
150 			IGS_GE_MSG_STACK.ADD;
151                          App_Exception.Raise_Exception;
152 		END IF;
153 
154 	END IF;
155   END BeforeRowInsertUpdate1;
156 
157 
158 
159 PROCEDURE Check_Constraints (
160  Column_Name	IN	VARCHAR2	DEFAULT NULL,
161  Column_Value 	IN	VARCHAR2	DEFAULT NULL
162  )
163  AS
164  BEGIN
165      IF  column_name is null then
166      NULL;
167  ELSIF upper(Column_name) = 'GOVT_LANGUAGE_CD' then
168      new_references.govt_language_cd  :=IGS_GE_NUMBER.to_num(column_value);
169  ELSIF upper(Column_name) = 'CLOSED_IND' then
170      new_references. closed_ind := column_value;
171 END IF;
172 
173 IF upper(column_name) = 'GOVT_LANGUAGE_CD' OR
174      column_name is null Then
175      IF new_references.govt_language_cd < 00 OR
176           new_references.govt_language_cd > 99 Then
177        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
178        IGS_GE_MSG_STACK.ADD;
179        App_Exception.Raise_Exception;
180                    END IF;
181               END IF;
182 
183 IF upper(column_name) = 'CLOSED_IND' OR
184      column_name is null Then
185      IF  new_references.closed_ind  NOT IN ( 'Y' , 'N' )Then
186        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
187        IGS_GE_MSG_STACK.ADD;
188        App_Exception.Raise_Exception;
189                    END IF;
190               END IF;
191 
192 
193  END Check_Constraints;
194 
195   PROCEDURE Check_Child_Existance AS
196 
197   BEGIN
198 
199 
200 
201     IGS_PE_LANGUAGE_CD_PKG.GET_FK_IGS_PE_GOV_LANG_CD (
202 
203       old_references.govt_language_cd
204 
205       );
206 
207 
208 
209   END Check_Child_Existance;
210 
211 
212 
213   FUNCTION Get_PK_For_Validation (
214 
215     x_govt_language_cd IN NUMBER
216 
217     )  RETURN BOOLEAN AS
218 
219 
220 
221     CURSOR cur_rowid IS
222 
223       SELECT   rowid
224 
225       FROM     IGS_PE_GOV_LANG_CD
226 
227       WHERE    govt_language_cd = x_govt_language_cd
228 
229       FOR UPDATE NOWAIT;
230 
231 
232 
233     lv_rowid cur_rowid%RowType;
234 
235 
236 
237   BEGIN
238     Open cur_rowid;
239     Fetch cur_rowid INTO lv_rowid;
240     IF (cur_rowid%FOUND) THEN
241        Close cur_rowid;
242        Return (TRUE);
243  	ELSE
244        Close cur_rowid;
245        Return (FALSE);
246  END IF;
247   END Get_PK_For_Validation;
248 
249 
250 
251   PROCEDURE Before_DML (
252 
253     p_action IN VARCHAR2,
254 
255     x_rowid IN  VARCHAR2 DEFAULT NULL,
256 
257     x_govt_language_cd IN NUMBER DEFAULT NULL,
258 
259     x_description IN VARCHAR2 DEFAULT NULL,
260 
261     x_closed_ind IN VARCHAR2 DEFAULT NULL,
262 
263     x_creation_date IN DATE DEFAULT NULL,
264 
265     x_created_by IN NUMBER DEFAULT NULL,
266 
267     x_last_update_date IN DATE DEFAULT NULL,
268 
269     x_last_updated_by IN NUMBER DEFAULT NULL,
270 
271     x_last_update_login IN NUMBER DEFAULT NULL
272 
273   ) AS
274 
275   BEGIN
276 
277 
278 
279     Set_Column_Values (
280 
281       p_action,
282 
283       x_rowid,
284 
285       x_govt_language_cd,
286 
287       x_description,
288 
289       x_closed_ind,
290 
291       x_creation_date,
292 
293       x_created_by,
294 
295       x_last_update_date,
296 
297       x_last_updated_by,
298 
299       x_last_update_login
300 
301     );
302 
303 
304 
305      IF (p_action = 'INSERT') THEN
306        -- Call all the procedures related to Before Insert.
307      BeforeRowInsertUpdate1 ( p_inserting => TRUE );
308       IF  Get_PK_For_Validation (
309           new_references.govt_language_cd ) THEN
310          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
311          IGS_GE_MSG_STACK.ADD;
312           App_Exception.Raise_Exception;
313       END IF;
314 
315       Check_Constraints; -- if procedure present
316 
317  ELSIF (p_action = 'UPDATE') THEN
318        -- Call all the procedures related to Before Update.
319        BeforeRowInsertUpdate1 ( p_updating => TRUE );
320 
321        Check_Constraints; -- if procedure present
322 
323  ELSIF (p_action = 'DELETE') THEN
324        -- Call all the procedures related to Before Delete.
325 
326        Check_Child_Existance; -- if procedure present
327  ELSIF (p_action = 'VALIDATE_INSERT') THEN
328       IF  Get_PK_For_Validation (
329           new_references.govt_language_cd ) THEN
330          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
331          IGS_GE_MSG_STACK.ADD;
332           App_Exception.Raise_Exception;
333       END IF;
334 
335       Check_Constraints; -- if procedure present
336  ELSIF (p_action = 'VALIDATE_UPDATE') THEN
337 
338        Check_Constraints; -- if procedure present
339 
340 ELSIF (p_action = 'VALIDATE_DELETE') THEN
341       Check_Child_Existance; -- if procedure present
342  END IF;
343 
344   END Before_DML;
345 
346 
347 
348   PROCEDURE After_DML (
349 
350     p_action IN VARCHAR2,
351 
352     x_rowid IN VARCHAR2
353 
354   ) AS
355 
356   BEGIN
357 
358 
359 
360     l_rowid := x_rowid;
361 
362 
363 
364     IF (p_action = 'INSERT') THEN
365 
366       -- Call all the procedures related to After Insert.
367 
368       Null;
369 
370     ELSIF (p_action = 'UPDATE') THEN
371 
372       -- Call all the procedures related to After Update.
373 
374       Null;
375 
376     ELSIF (p_action = 'DELETE') THEN
377 
378       -- Call all the procedures related to After Delete.
379 
380       Null;
381 
382     END IF;
383 
384 
385 
386   END After_DML;
387 
388 
389 procedure INSERT_ROW (
390   X_ROWID in out NOCOPY VARCHAR2,
391   X_GOVT_LANGUAGE_CD in NUMBER,
392   X_DESCRIPTION in VARCHAR2,
393   X_CLOSED_IND in VARCHAR2,
394   X_MODE in VARCHAR2 default 'R'
395   ) AS
396     cursor C is select ROWID from IGS_PE_GOV_LANG_CD
397       where GOVT_LANGUAGE_CD = X_GOVT_LANGUAGE_CD;
398     X_LAST_UPDATE_DATE DATE;
399     X_LAST_UPDATED_BY NUMBER;
400     X_LAST_UPDATE_LOGIN NUMBER;
401 begin
402   X_LAST_UPDATE_DATE := SYSDATE;
403   if(X_MODE = 'I') then
404     X_LAST_UPDATED_BY := 1;
405     X_LAST_UPDATE_LOGIN := 0;
406   elsif (X_MODE = 'R') then
407     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
408     if X_LAST_UPDATED_BY is NULL then
409       X_LAST_UPDATED_BY := -1;
410     end if;
411     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
412     if X_LAST_UPDATE_LOGIN is NULL then
413       X_LAST_UPDATE_LOGIN := -1;
414     end if;
415   else
416     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
417     IGS_GE_MSG_STACK.ADD;
418     app_exception.raise_exception;
419   end if;
420 
421     Before_DML(
422 
423   p_action=>'INSERT',
424 
425   x_rowid=>X_ROWID,
426 
427   x_closed_ind=> NVL(X_CLOSED_IND,'N'),
428 
429   x_description=>X_DESCRIPTION,
430 
431   x_govt_language_cd=>X_GOVT_LANGUAGE_CD,
432 
433   x_creation_date=>X_LAST_UPDATE_DATE,
434 
435   x_created_by=>X_LAST_UPDATED_BY,
436 
437   x_last_update_date=>X_LAST_UPDATE_DATE,
438 
439   x_last_updated_by=>X_LAST_UPDATED_BY,
440 
441   x_last_update_login=>X_LAST_UPDATE_LOGIN
442 
443   );
444   insert into IGS_PE_GOV_LANG_CD (
445     GOVT_LANGUAGE_CD,
446     DESCRIPTION,
447     CLOSED_IND,
448     CREATION_DATE,
449     CREATED_BY,
450     LAST_UPDATE_DATE,
451     LAST_UPDATED_BY,
452     LAST_UPDATE_LOGIN
453   ) values (
454     NEW_REFERENCES.GOVT_LANGUAGE_CD,
455     NEW_REFERENCES.DESCRIPTION,
456     NEW_REFERENCES.CLOSED_IND,
457     X_LAST_UPDATE_DATE,
458     X_LAST_UPDATED_BY,
459     X_LAST_UPDATE_DATE,
460     X_LAST_UPDATED_BY,
461     X_LAST_UPDATE_LOGIN
462   );
463 
464   open c;
465   fetch c into X_ROWID;
466   if (c%notfound) then
467     close c;
468     raise no_data_found;
469   end if;
470   close c;
471  After_DML(
472 
473   p_action => 'INSERT',
474 
475   x_rowid => X_ROWID
476 
477   );
478 end INSERT_ROW;
479 
480 procedure LOCK_ROW (
481 
482   X_ROWID in VARCHAR2,
483   X_GOVT_LANGUAGE_CD in NUMBER,
484   X_DESCRIPTION in VARCHAR2,
485   X_CLOSED_IND in VARCHAR2
486 ) AS
487   cursor c1 is select
488       DESCRIPTION,
489       CLOSED_IND
490     from IGS_PE_GOV_LANG_CD
491     where ROWID = X_ROWID
492     for update nowait;
493   tlinfo c1%rowtype;
494 
495 begin
496   open c1;
497   fetch c1 into tlinfo;
498   if (c1%notfound) then
499     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
500 
501     close c1;
502     App_Exception.Raise_Exception;
503     return;
504   end if;
505   close c1;
506 
507   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
508       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
509   ) then
510     null;
511   else
512     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
513     app_exception.raise_exception;
514   end if;
515   return;
516 end LOCK_ROW;
517 
518 procedure UPDATE_ROW (
519 
520   X_ROWID in VARCHAR2,
521   X_GOVT_LANGUAGE_CD in NUMBER,
522   X_DESCRIPTION in VARCHAR2,
523   X_CLOSED_IND in VARCHAR2,
524   X_MODE in VARCHAR2 default 'R'
525   ) AS
526     X_LAST_UPDATE_DATE DATE;
527     X_LAST_UPDATED_BY NUMBER;
528     X_LAST_UPDATE_LOGIN NUMBER;
529 begin
530   X_LAST_UPDATE_DATE := SYSDATE;
531   if(X_MODE = 'I') then
532     X_LAST_UPDATED_BY := 1;
533     X_LAST_UPDATE_LOGIN := 0;
534   elsif (X_MODE = 'R') then
535     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
536     if X_LAST_UPDATED_BY is NULL then
537       X_LAST_UPDATED_BY := -1;
538     end if;
539     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
540     if X_LAST_UPDATE_LOGIN is NULL then
541       X_LAST_UPDATE_LOGIN := -1;
542     end if;
543   else
544     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
545     IGS_GE_MSG_STACK.ADD;
546     app_exception.raise_exception;
547   end if;
548 
549  Before_DML(
550 
551   p_action=>'UPDATE',
552 
553   x_rowid=>X_ROWID,
554 
555   x_closed_ind=>X_CLOSED_IND,
556 
557   x_description=>X_DESCRIPTION,
558 
559   x_govt_language_cd=>X_GOVT_LANGUAGE_CD,
560 
561   x_creation_date=>X_LAST_UPDATE_DATE,
562 
563   x_created_by=>X_LAST_UPDATED_BY,
564 
565   x_last_update_date=>X_LAST_UPDATE_DATE,
566 
567   x_last_updated_by=>X_LAST_UPDATED_BY,
568 
569   x_last_update_login=>X_LAST_UPDATE_LOGIN
570 
571   );
572   update IGS_PE_GOV_LANG_CD set
573     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
574     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
575     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
576     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
577     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
578   where ROWID = X_ROWID
579   ;
580   if (sql%notfound) then
581     raise no_data_found;
582   end if;
583 
584 After_DML(
585 
586   p_action => 'UPDATE',
587 
588   x_rowid => X_ROWID
589 
590   );
591 end UPDATE_ROW;
592 
593 procedure ADD_ROW (
594   X_ROWID in out NOCOPY VARCHAR2,
595   X_GOVT_LANGUAGE_CD in NUMBER,
596   X_DESCRIPTION in VARCHAR2,
597   X_CLOSED_IND in VARCHAR2,
598   X_MODE in VARCHAR2 default 'R'
599   ) AS
600   cursor c1 is select rowid from IGS_PE_GOV_LANG_CD
601      where GOVT_LANGUAGE_CD = X_GOVT_LANGUAGE_CD
602   ;
603 
604 begin
605   open c1;
606   fetch c1 into X_ROWID;
607   if (c1%notfound) then
608     close c1;
609     INSERT_ROW (
610      X_ROWID,
611      X_GOVT_LANGUAGE_CD,
612      X_DESCRIPTION,
613      X_CLOSED_IND,
614      X_MODE);
615     return;
616   end if;
617   close c1;
618   UPDATE_ROW (
619 
620    X_ROWID,
621    X_GOVT_LANGUAGE_CD,
622    X_DESCRIPTION,
623    X_CLOSED_IND,
624    X_MODE);
625 end ADD_ROW;
626 
627 procedure DELETE_ROW (
628   X_ROWID in VARCHAR2
629 ) AS
630 begin
631 
632   Before_DML(
633 
634   p_action => 'DELETE',
635 
636   x_rowid => X_ROWID
637 
638   );
639   delete from IGS_PE_GOV_LANG_CD
640   where ROWID = X_ROWID;
641   if (sql%notfound) then
642     raise no_data_found;
643   end if;
644 
645 After_DML(
646 
647   p_action => 'DELETE',
648 
649   x_rowid => X_ROWID
650 
651   );
652 
653 
654 end DELETE_ROW;
655 
656 end IGS_PE_GOV_LANG_CD_PKG;