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_ST_CITIZENSHP_CD_PKG

Source


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