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