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