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