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