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_GOVCITIZEN_CD_PKG

Source


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