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_PERS_RELN_TYP_PKG

Source


1 package body IGS_PE_PERS_RELN_TYP_PKG AS
2   /* $Header: IGSNI30B.pls 115.3 2002/11/29 01:21:11 nsidana ship $ */
3 
4 
5   l_rowid VARCHAR2(25);
6   old_references IGS_PE_PERS_RELN_TYP%RowType;
7   new_references IGS_PE_PERS_RELN_TYP%RowType;
8 
9   PROCEDURE Set_Column_Values (
10     p_action IN VARCHAR2,
11     x_rowid IN VARCHAR2 DEFAULT NULL,
12     x_person_relation_type IN VARCHAR2 DEFAULT NULL,
13     x_description 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_PERS_RELN_TYP
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.person_relation_type := x_person_relation_type;
45     new_references.description := x_description;
46     IF (p_action = 'UPDATE') THEN
47       new_references.creation_date := old_references.creation_date;
48       new_references.created_by := old_references.created_by;
49     ELSE
50       new_references.creation_date := x_creation_date;
51       new_references.created_by := x_created_by;
52     END IF;
53     new_references.last_update_date := x_last_update_date;
54     new_references.last_updated_by := x_last_updated_by;
55     new_references.last_update_login := x_last_update_login;
56 
57   END Set_Column_Values;
58 
59  PROCEDURE Check_Constraints (
60  Column_Name	IN	VARCHAR2	DEFAULT NULL,
61  Column_Value 	IN	VARCHAR2	DEFAULT NULL
62  )
63  AS
64  BEGIN
65     IF  column_name is null then
66      NULL;
67 
68  ELSIF upper(Column_name) = 'PERSON_RELATION_TYPE' then
69      new_references.person_relation_type:= column_value;
70   END IF;
71 
72  IF upper(column_name) = 'PERSON_RELATION_TYPE' OR
73      column_name is null Then
74      IF new_references.person_relation_type <>UPPER(new_references.person_relation_type)  Then
75        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
76        IGS_GE_MSG_STACK.ADD;
77        App_Exception.Raise_Exception;
78                    END IF;
79               END IF;
80  END Check_Constraints;
81 
82 
83   PROCEDURE Check_Child_Existance AS
84   BEGIN
85 
86     IGS_PE_PERS_RELATION_PKG.GET_FK_IGS_PE_PERS_RELN_TYP (
87       old_references.person_relation_type
88       );
89 
90   END Check_Child_Existance;
91 
92   FUNCTION Get_PK_For_Validation (
93     x_person_relation_type IN VARCHAR2
94     ) RETURN BOOLEAN AS
95 
96     CURSOR cur_rowid IS
97       SELECT   rowid
98       FROM     IGS_PE_PERS_RELN_TYP
99       WHERE    person_relation_type = x_person_relation_type
100       FOR UPDATE NOWAIT;
101 
102     lv_rowid cur_rowid%RowType;
103 
104   BEGIN
105 
106     Open cur_rowid;
107     Fetch cur_rowid INTO lv_rowid;
108     IF (cur_rowid%FOUND) THEN
109        Close cur_rowid;
110        Return (TRUE);
111  	ELSE
112        Close cur_rowid;
113        Return (FALSE);
114  END IF;
115 
116   END Get_PK_For_Validation;
117 
118   PROCEDURE Before_DML (
119     p_action IN VARCHAR2,
120     x_rowid IN  VARCHAR2 DEFAULT NULL,
121     x_person_relation_type IN VARCHAR2 DEFAULT NULL,
122     x_description IN VARCHAR2 DEFAULT NULL,
123     x_creation_date IN DATE DEFAULT NULL,
124     x_created_by IN NUMBER DEFAULT NULL,
125     x_last_update_date IN DATE DEFAULT NULL,
126     x_last_updated_by IN NUMBER DEFAULT NULL,
127     x_last_update_login IN NUMBER DEFAULT NULL
128   ) AS
129   BEGIN
130 
131     Set_Column_Values (
132       p_action,
133       x_rowid,
134       x_person_relation_type,
135       x_description,
136       x_creation_date,
137       x_created_by,
138       x_last_update_date,
139       x_last_updated_by,
140       x_last_update_login
141     );
142 
143     IF (p_action = 'INSERT') THEN
144        -- Call all the procedures related to Before Insert.
145 
146       IF  Get_PK_For_Validation (
147           new_references.person_relation_type ) THEN
148          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
149          IGS_GE_MSG_STACK.ADD;
150           App_Exception.Raise_Exception;
151       END IF;
152 
153       Check_Constraints; -- if procedure present
154 
155  ELSIF (p_action = 'UPDATE') THEN
156        -- Call all the procedures related to Before Update.
157 
158 
159        Check_Constraints; -- if procedure present
160 
161  ELSIF (p_action = 'DELETE') THEN
162        -- Call all the procedures related to Before Delete.
163        Check_Child_Existance; -- if procedure present
164  ELSIF (p_action = 'VALIDATE_INSERT') THEN
165       IF  Get_PK_For_Validation (
166           new_references.person_relation_type  ) THEN
167          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
168          IGS_GE_MSG_STACK.ADD;
169           App_Exception.Raise_Exception;
170       END IF;
171 
172       Check_Constraints; -- if procedure present
173  ELSIF (p_action = 'VALIDATE_UPDATE') THEN
174 
175        Check_Constraints; -- if procedure present
176 
177 ELSIF (p_action = 'VALIDATE_DELETE') THEN
178       Check_Child_Existance; -- if procedure present
179  END IF;
180 
181   END Before_DML;
182 
183   PROCEDURE After_DML (
184     p_action IN VARCHAR2,
185     x_rowid IN VARCHAR2
186   ) AS
187   BEGIN
188 
189     l_rowid := x_rowid;
190 
191     IF (p_action = 'INSERT') THEN
192       -- Call all the procedures related to After Insert.
193       Null;
194     ELSIF (p_action = 'UPDATE') THEN
195       -- Call all the procedures related to After Update.
196       Null;
197     ELSIF (p_action = 'DELETE') THEN
198       -- Call all the procedures related to After Delete.
199       Null;
200     END IF;
201 
202   END After_DML;
203 
204 procedure INSERT_ROW (
205   X_ROWID in out NOCOPY VARCHAR2,
206   X_PERSON_RELATION_TYPE in VARCHAR2,
207   X_DESCRIPTION in VARCHAR2,
208   X_MODE in VARCHAR2 default 'R'
209   ) AS
210     cursor C is select ROWID from IGS_PE_PERS_RELN_TYP
211       where PERSON_RELATION_TYPE = X_PERSON_RELATION_TYPE;
212     X_LAST_UPDATE_DATE DATE;
213     X_LAST_UPDATED_BY NUMBER;
214     X_LAST_UPDATE_LOGIN NUMBER;
215 begin
216   X_LAST_UPDATE_DATE := SYSDATE;
217   if(X_MODE = 'I') then
218     X_LAST_UPDATED_BY := 1;
219     X_LAST_UPDATE_LOGIN := 0;
220   elsif (X_MODE = 'R') then
221     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
222     if X_LAST_UPDATED_BY is NULL then
223       X_LAST_UPDATED_BY := -1;
224     end if;
225     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
226     if X_LAST_UPDATE_LOGIN is NULL then
227       X_LAST_UPDATE_LOGIN := -1;
228     end if;
229   else
230     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
231     IGS_GE_MSG_STACK.ADD;
232     app_exception.raise_exception;
233   end if;
234   Before_DML(
235    p_action=>'INSERT',
236    x_rowid=>X_ROWID,
237    x_description=>X_DESCRIPTION,
238    x_person_relation_type=>X_PERSON_RELATION_TYPE,
239    x_creation_date=>X_LAST_UPDATE_DATE,
240    x_created_by=>X_LAST_UPDATED_BY,
241    x_last_update_date=>X_LAST_UPDATE_DATE,
242    x_last_updated_by=>X_LAST_UPDATED_BY,
243    x_last_update_login=>X_LAST_UPDATE_LOGIN
244    );
245 
246   insert into IGS_PE_PERS_RELN_TYP (
247     PERSON_RELATION_TYPE,
248     DESCRIPTION,
249     CREATION_DATE,
250     CREATED_BY,
251     LAST_UPDATE_DATE,
252     LAST_UPDATED_BY,
253     LAST_UPDATE_LOGIN
254   ) values (
255     NEW_REFERENCES.PERSON_RELATION_TYPE,
256     NEW_REFERENCES.DESCRIPTION,
257     X_LAST_UPDATE_DATE,
258     X_LAST_UPDATED_BY,
259     X_LAST_UPDATE_DATE,
260     X_LAST_UPDATED_BY,
261     X_LAST_UPDATE_LOGIN
262   );
263 
264   open c;
265   fetch c into X_ROWID;
266   if (c%notfound) then
267     close c;
268     raise no_data_found;
269   end if;
270   close c;
271  After_DML(
272   p_action => 'INSERT',
273   x_rowid => X_ROWID
274   );
275 end INSERT_ROW;
276 
277 procedure LOCK_ROW (
278   X_ROWID in VARCHAR2,
279   X_PERSON_RELATION_TYPE in VARCHAR2,
280   X_DESCRIPTION in VARCHAR2
281 ) AS
282   cursor c1 is select
283       DESCRIPTION
284     from IGS_PE_PERS_RELN_TYP
285     where ROWID = X_ROWID
286     for update nowait;
287   tlinfo c1%rowtype;
288 
289 begin
290   open c1;
291   fetch c1 into tlinfo;
292   if (c1%notfound) then
293     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
294 
295     close c1;
296     App_Exception.Raise_Exception;
297     return;
298   end if;
299   close c1;
300 
301   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
302   ) then
303     null;
304   else
305     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
306     app_exception.raise_exception;
307   end if;
308   return;
309 end LOCK_ROW;
310 
311 procedure UPDATE_ROW (
312   X_ROWID in VARCHAR2,
313   X_PERSON_RELATION_TYPE in VARCHAR2,
314   X_DESCRIPTION in VARCHAR2,
315   X_MODE in VARCHAR2 default 'R'
316   ) AS
317     X_LAST_UPDATE_DATE DATE;
318     X_LAST_UPDATED_BY NUMBER;
319     X_LAST_UPDATE_LOGIN NUMBER;
320 begin
321   X_LAST_UPDATE_DATE := SYSDATE;
322   if(X_MODE = 'I') then
323     X_LAST_UPDATED_BY := 1;
324     X_LAST_UPDATE_LOGIN := 0;
325   elsif (X_MODE = 'R') then
326     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
327     if X_LAST_UPDATED_BY is NULL then
328       X_LAST_UPDATED_BY := -1;
329     end if;
330     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
331     if X_LAST_UPDATE_LOGIN is NULL then
332       X_LAST_UPDATE_LOGIN := -1;
333     end if;
334   else
335     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
336     IGS_GE_MSG_STACK.ADD;
337     app_exception.raise_exception;
338   end if;
339 
340   Before_DML(
341    p_action=>'UPDATE',
342    x_rowid=>X_ROWID,
343    x_description=>X_DESCRIPTION,
344    x_person_relation_type=>X_PERSON_RELATION_TYPE,
345    x_creation_date=>X_LAST_UPDATE_DATE,
346    x_created_by=>X_LAST_UPDATED_BY,
347    x_last_update_date=>X_LAST_UPDATE_DATE,
348    x_last_updated_by=>X_LAST_UPDATED_BY,
349    x_last_update_login=>X_LAST_UPDATE_LOGIN
350    );
351 
352   update IGS_PE_PERS_RELN_TYP set
353     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
354     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
355     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
356     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
357   where ROWID = X_ROWID
358   ;
359   if (sql%notfound) then
360     raise no_data_found;
361   end if;
362 
363  After_DML(
364   p_action => 'UPDATE',
365   x_rowid => X_ROWID
366   );
367 end UPDATE_ROW;
368 
369 procedure ADD_ROW (
370   X_ROWID in out NOCOPY VARCHAR2,
371   X_PERSON_RELATION_TYPE in VARCHAR2,
372   X_DESCRIPTION in VARCHAR2,
373   X_MODE in VARCHAR2 default 'R'
374   ) AS
375   cursor c1 is select rowid from IGS_PE_PERS_RELN_TYP
376      where PERSON_RELATION_TYPE = X_PERSON_RELATION_TYPE
377   ;
378 
379 begin
380   open c1;
381   fetch c1 into X_ROWID;
382   if (c1%notfound) then
383     close c1;
384     INSERT_ROW (
385      X_ROWID,
386      X_PERSON_RELATION_TYPE,
387      X_DESCRIPTION,
388      X_MODE);
389     return;
390   end if;
391   close c1;
392   UPDATE_ROW (
393    X_ROWID,
394    X_PERSON_RELATION_TYPE,
395    X_DESCRIPTION,
396    X_MODE);
397 end ADD_ROW;
398 
399 procedure DELETE_ROW (
400  X_ROWID in VARCHAR2
401 ) AS
402 begin
403 
404  Before_DML(
405   p_action => 'DELETE',
406   x_rowid => X_ROWID
407   );
408   delete from IGS_PE_PERS_RELN_TYP
409   where ROWID = X_ROWID;
410   if (sql%notfound) then
411     raise no_data_found;
412   end if;
413  After_DML(
414   p_action => 'DELETE',
415   x_rowid => X_ROWID
416   );
417 end DELETE_ROW;
418 
419 end IGS_PE_PERS_RELN_TYP_PKG;