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