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