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_LANGUAGE_CD_PKG

Source


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