[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERS_RELATION_PKG
Source
1 package body IGS_PE_PERS_RELATION_PKG AS
2 /* $Header: IGSNI31B.pls 120.0 2005/06/01 20:57:59 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PE_PERS_RELATION%RowType;
6 new_references IGS_PE_PERS_RELATION%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_person_id_also_related_to IN NUMBER DEFAULT NULL,
12 x_person_relation_type IN VARCHAR2 DEFAULT NULL,
13 x_creation_date IN DATE DEFAULT NULL,
14 x_created_by IN NUMBER DEFAULT NULL,
15 x_last_update_date IN DATE DEFAULT NULL,
16 x_last_updated_by IN NUMBER DEFAULT NULL,
17 x_last_update_login IN NUMBER DEFAULT NULL
18 ) AS
19
20 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM IGS_PE_PERS_RELATION
23 WHERE rowid = x_rowid;
24
25 BEGIN
26
27 l_rowid := x_rowid;
28
29 -- Code for setting the Old and New Reference Values.
30 -- Populate Old Values.
31 Open cur_old_ref_values;
32 Fetch cur_old_ref_values INTO old_references;
33 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 IGS_GE_MSG_STACK.ADD;
36 Close cur_old_ref_values;
37 App_Exception.Raise_Exception;
38 Return;
39 END IF;
40 Close cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.person_id := x_person_id;
44 new_references.person_id_also_related_to := x_person_id_also_related_to;
45 new_references.person_relation_type := x_person_relation_type;
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
60
61 PROCEDURE Check_Constraints (
62 Column_Name IN VARCHAR2 DEFAULT NULL,
63 Column_Value IN VARCHAR2 DEFAULT NULL
64 )
65 AS
66 BEGIN
67 IF column_name is null then
68 NULL;
69
70 ELSIF upper(Column_name) = 'PERSON_RELATION_TYPE' then
71 new_references.person_relation_type := column_value;
72
73 END IF;
74 IF upper(column_name) = 'PERSON_RELATION_TYPE' OR
75 column_name is null Then
76 IF new_references.person_relation_type <>UPPER(new_references.person_relation_type )Then
77 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
78 IGS_GE_MSG_STACK.ADD;
79 App_Exception.Raise_Exception;
80 END IF;
81 END IF;
82
83 END Check_Constraints;
84 PROCEDURE Check_Parent_Existance AS
85 BEGIN
86
87 IF (((old_references.person_id = new_references.person_id)) OR
88 ((new_references.person_id IS NULL))) THEN
89 NULL;
90 ELSE
91
92 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
93 new_references.person_id) THEN
94 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END IF;
98 END IF;
99
100 IF (((old_references.person_id_also_related_to = new_references.person_id_also_related_to)) OR
101 ((new_references.person_id_also_related_to IS NULL))) THEN
102 NULL;
103 ELSE
104 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
105 new_references.person_id_also_related_to ) THEN
106 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END IF;
110 END IF;
111
112 IF (((old_references.person_relation_type = new_references.person_relation_type)) OR
113 ((new_references.person_relation_type IS NULL))) THEN
114 NULL;
115 ELSE
116
117 IF NOT IGS_PE_PERS_RELN_TYP_PKG.Get_PK_For_Validation (
118 new_references.person_relation_type ) THEN
119 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124
125 END Check_Parent_Existance;
126
127 FUNCTION Get_PK_For_Validation (
128 x_person_id IN NUMBER,
129 x_person_id_also_related_to IN NUMBER,
130 x_person_relation_type IN VARCHAR2
131 ) RETURN BOOLEAN AS
132
133 CURSOR cur_rowid IS
134 SELECT rowid
135 FROM IGS_PE_PERS_RELATION
136 WHERE person_id = x_person_id
137 AND person_id_also_related_to = x_person_id_also_related_to
138 AND person_relation_type = x_person_relation_type
139 FOR UPDATE NOWAIT;
140
141 lv_rowid cur_rowid%RowType;
142
143 BEGIN
144
145 Open cur_rowid;
146 Fetch cur_rowid INTO lv_rowid;
147 IF (cur_rowid%FOUND) THEN
148 Close cur_rowid;
149 Return (TRUE);
150 ELSE
151 Close cur_rowid;
152 Return (FALSE);
153 END IF;
154
155
156 END Get_PK_For_Validation;
157
158 PROCEDURE GET_FK_IGS_PE_PERSON (
159 x_person_id IN NUMBER
160 ) AS
161
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM IGS_PE_PERS_RELATION
165 WHERE person_id = x_person_id OR
166 person_id_also_related_to = x_person_id ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PR_PE_FK');
176 IGS_GE_MSG_STACK.ADD;
177 Close cur_rowid;
178 App_Exception.Raise_Exception;
179 Return;
180 END IF;
181 Close cur_rowid;
182
183 END GET_FK_IGS_PE_PERSON;
184
185
186
187 PROCEDURE GET_FK_IGS_PE_PERS_RELN_TYP (
188 x_person_relation_type IN VARCHAR2
189 ) AS
190
191 CURSOR cur_rowid IS
192 SELECT rowid
193 FROM IGS_PE_PERS_RELATION
194 WHERE person_relation_type = x_person_relation_type ;
195
196 lv_rowid cur_rowid%RowType;
197
198 BEGIN
199
200 Open cur_rowid;
201 Fetch cur_rowid INTO lv_rowid;
202 IF (cur_rowid%FOUND) THEN
203 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PR_PRT_FK');
204 IGS_GE_MSG_STACK.ADD;
205 Close cur_rowid;
206 App_Exception.Raise_Exception;
207 Return;
208 END IF;
209 Close cur_rowid;
210
211 END GET_FK_IGS_PE_PERS_RELN_TYP;
212
213 PROCEDURE Before_DML (
214 p_action IN VARCHAR2,
215 x_rowid IN VARCHAR2 DEFAULT NULL,
216 x_person_id IN NUMBER DEFAULT NULL,
217 x_person_id_also_related_to IN NUMBER DEFAULT NULL,
218 x_person_relation_type IN VARCHAR2 DEFAULT NULL,
219 x_creation_date IN DATE DEFAULT NULL,
220 x_created_by IN NUMBER DEFAULT NULL,
221 x_last_update_date IN DATE DEFAULT NULL,
222 x_last_updated_by IN NUMBER DEFAULT NULL,
223 x_last_update_login IN NUMBER DEFAULT NULL
224 ) AS
225 BEGIN
226
227 Set_Column_Values (
228 p_action,
229 x_rowid,
230 x_person_id,
231 x_person_id_also_related_to,
232 x_person_relation_type,
233 x_creation_date,
234 x_created_by,
235 x_last_update_date,
236 x_last_updated_by,
237 x_last_update_login
238 );
239
240 IF (p_action = 'INSERT') THEN
241 -- Call all the procedures related to Before Insert.
242
243 IF Get_PK_For_Validation (
244 new_references.person_id ,
245 new_references.person_id_also_related_to ,
246 new_references.person_relation_type
247 ) THEN
248 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252
253 Check_Constraints; -- if procedure present
254 Check_Parent_Existance; -- if procedure present
255 ELSIF (p_action = 'UPDATE') THEN
256 -- Call all the procedures related to Before Update.
257
258
259 Check_Constraints; -- if procedure present
260 Check_Parent_Existance; -- if procedure present
261
262 ELSIF (p_action = 'DELETE') THEN
263 -- Call all the procedures related to Before Delete.
264
265 NULL;
266 ELSIF (p_action = 'VALIDATE_INSERT') THEN
267 IF Get_PK_For_Validation (
268 new_references.person_id ,
269 new_references.person_id_also_related_to ,
270 new_references.person_relation_type ) THEN
271 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275
276 Check_Constraints; -- if procedure present
277 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
278 Check_Constraints; -- if procedure present
279
280 ELSIF (p_action = 'VALIDATE_DELETE') THEN
281 NULL;
282 END IF;
283
284 END Before_DML;
285
286 PROCEDURE After_DML (
287 p_action IN VARCHAR2,
288 x_rowid IN VARCHAR2
289 ) AS
290 BEGIN
291
292 l_rowid := x_rowid;
293
294 IF (p_action = 'INSERT') THEN
295 -- Call all the procedures related to After Insert.
296 Null;
297 ELSIF (p_action = 'UPDATE') THEN
298 -- Call all the procedures related to After Update.
299 Null;
300 ELSIF (p_action = 'DELETE') THEN
301 -- Call all the procedures related to After Delete.
302 Null;
303 END IF;
304
305 END After_DML;
306
307 procedure INSERT_ROW (
308 X_ROWID in out NOCOPY VARCHAR2,
309 X_PERSON_ID in NUMBER,
310 X_PERSON_ID_ALSO_RELATED_TO in NUMBER,
311 X_PERSON_RELATION_TYPE in VARCHAR2,
312 X_MODE in VARCHAR2 default 'R'
313 ) AS
314 cursor C is select ROWID from IGS_PE_PERS_RELATION
315 where PERSON_ID = X_PERSON_ID
316 and PERSON_ID_ALSO_RELATED_TO = X_PERSON_ID_ALSO_RELATED_TO
317 and PERSON_RELATION_TYPE = X_PERSON_RELATION_TYPE;
318 X_LAST_UPDATE_DATE DATE;
319 X_LAST_UPDATED_BY NUMBER;
320 X_LAST_UPDATE_LOGIN NUMBER;
321 begin
322 X_LAST_UPDATE_DATE := SYSDATE;
323 if(X_MODE = 'I') then
324 X_LAST_UPDATED_BY := 1;
325 X_LAST_UPDATE_LOGIN := 0;
326 elsif (X_MODE IN ('R', 'S')) then
327 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
328 if X_LAST_UPDATED_BY is NULL then
329 X_LAST_UPDATED_BY := -1;
330 end if;
331 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
332 if X_LAST_UPDATE_LOGIN is NULL then
333 X_LAST_UPDATE_LOGIN := -1;
334 end if;
335 else
336 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
337 IGS_GE_MSG_STACK.ADD;
338 app_exception.raise_exception;
339 end if;
340
341 Before_DML(
342 p_action=>'INSERT',
343 x_rowid=>X_ROWID,
344 x_person_id=>X_PERSON_ID,
345 x_person_id_also_related_to=>X_PERSON_ID_ALSO_RELATED_TO,
346 x_person_relation_type=>X_PERSON_RELATION_TYPE,
347 x_creation_date=>X_LAST_UPDATE_DATE,
348 x_created_by=>X_LAST_UPDATED_BY,
349 x_last_update_date=>X_LAST_UPDATE_DATE,
350 x_last_updated_by=>X_LAST_UPDATED_BY,
351 x_last_update_login=>X_LAST_UPDATE_LOGIN
352 );
353
354 IF (x_mode = 'S') THEN
355 igs_sc_gen_001.set_ctx('R');
356 END IF;
357 insert into IGS_PE_PERS_RELATION (
358 PERSON_ID,
359 PERSON_ID_ALSO_RELATED_TO,
360 PERSON_RELATION_TYPE,
361 CREATION_DATE,
362 CREATED_BY,
363 LAST_UPDATE_DATE,
364 LAST_UPDATED_BY,
365 LAST_UPDATE_LOGIN
366 ) values (
367 NEW_REFERENCES.PERSON_ID,
368 NEW_REFERENCES.PERSON_ID_ALSO_RELATED_TO,
369 NEW_REFERENCES.PERSON_RELATION_TYPE,
370 X_LAST_UPDATE_DATE,
371 X_LAST_UPDATED_BY,
372 X_LAST_UPDATE_DATE,
373 X_LAST_UPDATED_BY,
374 X_LAST_UPDATE_LOGIN
375 );
376 IF (x_mode = 'S') THEN
377 igs_sc_gen_001.unset_ctx('R');
378 END IF;
379
380
381 open c;
382 fetch c into X_ROWID;
383 if (c%notfound) then
384 close c;
385 raise no_data_found;
386 end if;
387 After_DML(
388 p_action => 'INSERT',
389 x_rowid => X_ROWID
390 );
391 close c;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
396 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
397 fnd_message.set_token ('ERR_CD', SQLCODE);
398 igs_ge_msg_stack.add;
399 igs_sc_gen_001.unset_ctx('R');
400 app_exception.raise_exception;
401 ELSE
402 igs_sc_gen_001.unset_ctx('R');
403 RAISE;
404 END IF;
405
406 end INSERT_ROW;
407
408 procedure LOCK_ROW (
409 X_ROWID in VARCHAR2,
410 X_PERSON_ID in NUMBER,
411 X_PERSON_ID_ALSO_RELATED_TO in NUMBER,
412 X_PERSON_RELATION_TYPE in VARCHAR2
413 ) AS
414 cursor c1 is select ROWID
415 from IGS_PE_PERS_RELATION
416 where ROWID = X_ROWID
417 for update nowait;
418 tlinfo c1%rowtype;
419
420 begin
421 open c1;
422 fetch c1 into tlinfo;
423 if (c1%notfound) then
424 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
425
426 close c1;
427 App_Exception.Raise_Exception;
428 return;
429 end if;
430 close c1;
431
432 return;
433 end LOCK_ROW;
434
435
436 procedure DELETE_ROW (
437 X_ROWID in VARCHAR2,
438 x_mode IN VARCHAR2
439 ) AS
440 begin
441 Before_DML(
442 p_action => 'DELETE',
443 x_rowid => X_ROWID
444 );
445 IF (x_mode = 'S') THEN
446 igs_sc_gen_001.set_ctx('R');
447 END IF;
448 delete from IGS_PE_PERS_RELATION
449 where ROWID = X_ROWID;
450 if (sql%notfound) then
451 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
452 igs_ge_msg_stack.add;
453 igs_sc_gen_001.unset_ctx('R');
454 app_exception.raise_exception;
455 end if;
456 IF (x_mode = 'S') THEN
457 igs_sc_gen_001.unset_ctx('R');
458 END IF;
459
460 After_DML(
461 p_action => 'DELETE',
462 x_rowid => X_ROWID
463 );
464 end DELETE_ROW;
465
466 end IGS_PE_PERS_RELATION_PKG;