[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_GROUP_SET_PKG
Source
1 package body IGS_RU_GROUP_SET_PKG as
2 /* $Header: IGSUI06B.pls 115.8 2003/10/14 10:53:40 nsinha ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_RU_GROUP_SET%RowType;
7 new_references IGS_RU_GROUP_SET%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 ,
12 x_rug_sequence_number IN NUMBER ,
13 x_rud_sequence_number IN NUMBER ,
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 ) as
19
20 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM IGS_RU_GROUP_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_GROUP_SET : P_ACTION INSERT VALIDATE_INSERT : IGSUI06B.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.rug_sequence_number := x_rug_sequence_number;
45 new_references.rud_sequence_number := x_rud_sequence_number;
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 ) as
63 Begin
64 IF Column_Name is null THEN
65 NULL;
66 ELSIF upper(Column_name) = 'RUD_SEQUENCE_NUMBER' THEN
67 new_references.RUD_SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
68
69 ELSIF upper(Column_name) = 'RUG_SEQUENCE_NUMBER' THEN
70 new_references.RUG_SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
71
72 END IF ;
73
74 IF upper(Column_name) = 'RUD_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
75 IF new_references.RUD_SEQUENCE_NUMBER < 1 or new_references.RUD_SEQUENCE_NUMBER > 999999 then
76 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
77 IGS_GE_MSG_STACK.ADD;
78 App_Exception.Raise_Exception ;
79 END IF;
80
81 END IF ;
82
83 IF upper(Column_name) = 'RUG_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
84 IF new_references.RUG_SEQUENCE_NUMBER < 1 or new_references.RUG_SEQUENCE_NUMBER > 999999 then
85 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception ;
88 END IF;
89
90 END IF ;
91
92 END Check_Constraints;
93
94 PROCEDURE Check_Parent_Existance as
95 BEGIN
96
97 IF (((old_references.rud_sequence_number = new_references.rud_sequence_number)) OR
98 ((new_references.rud_sequence_number IS NULL))) THEN
99 NULL;
100 ELSE
101 IF NOT IGS_RU_DESCRIPTION_PKG.Get_PK_For_Validation (
102 new_references.rud_sequence_number
103 ) THEN
104 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
105 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_DESCRIPTION : P_ACTION Check_Parent_Existance .rud_sequence_number : IGSUI06B.PLS');
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109 END IF;
110
111 IF (((old_references.rug_sequence_number = new_references.rug_sequence_number)) OR
112 ((new_references.rug_sequence_number IS NULL))) THEN
113 NULL;
114 ELSE
115 IF NOT IGS_RU_GROUP_PKG.Get_PK_For_Validation (
116 new_references.rug_sequence_number
117 ) THEN
118 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
119 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_GROUP : P_ACTION Check_Parent_Existance .rug_sequence_number : IGSUI06B.PLS');
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124
125 END Check_Parent_Existance;
126
127 FUNCTION Get_PK_For_Validation (
128 x_rug_sequence_number IN NUMBER,
129 x_rud_sequence_number IN NUMBER
130 ) RETURN BOOLEAN as
131
132 CURSOR cur_rowid IS
133 SELECT rowid
134 FROM IGS_RU_GROUP_SET
135 WHERE rug_sequence_number = x_rug_sequence_number
136 AND rud_sequence_number = x_rud_sequence_number
137 FOR UPDATE NOWAIT;
138
139 lv_rowid cur_rowid%RowType;
140
141 BEGIN
142
143 Open cur_rowid;
144 Fetch cur_rowid INTO lv_rowid;
145 IF (cur_rowid%FOUND) THEN
146 Close cur_rowid;
147 Return (TRUE);
148 ELSE
149 Close cur_rowid;
150 Return (FALSE);
151 END IF;
152
153 END Get_PK_For_Validation;
154
155 PROCEDURE GET_FK_IGS_RU_DESCRIPTION (
156 x_sequence_number IN NUMBER
157 ) as
158
159 CURSOR cur_rowid IS
160 SELECT rowid
161 FROM IGS_RU_GROUP_SET
162 WHERE rud_sequence_number = x_sequence_number ;
163
164 lv_rowid cur_rowid%RowType;
165
166 BEGIN
167
168 Open cur_rowid;
169 Fetch cur_rowid INTO lv_rowid;
170 IF (cur_rowid%FOUND) THEN
171 Close cur_rowid;
172 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RGS_RUD_FK');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 Return;
176 END IF;
177 Close cur_rowid;
178
179 END GET_FK_IGS_RU_DESCRIPTION;
180
181 PROCEDURE GET_FK_IGS_RU_GROUP (
182 x_sequence_number IN NUMBER
183 ) as
184
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM IGS_RU_GROUP_SET
188 WHERE rug_sequence_number = x_sequence_number ;
189
190 lv_rowid cur_rowid%RowType;
191
192 BEGIN
193
194 Open cur_rowid;
195 Fetch cur_rowid INTO lv_rowid;
196 IF (cur_rowid%FOUND) THEN
197 Close cur_rowid;
198 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RGS_RUG_FK');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 Return;
202 END IF;
203 Close cur_rowid;
204
205 END GET_FK_IGS_RU_GROUP;
206
207 PROCEDURE Before_DML (
208 p_action IN VARCHAR2,
209 x_rowid IN VARCHAR2 ,
210 x_rug_sequence_number IN NUMBER ,
211 x_rud_sequence_number IN NUMBER ,
212 x_creation_date IN DATE ,
213 x_created_by IN NUMBER ,
214 x_last_update_date IN DATE ,
215 x_last_updated_by IN NUMBER ,
216 x_last_update_login IN NUMBER
217 ) as
218 BEGIN
219
220 Set_Column_Values (
221 p_action,
222 x_rowid,
223 x_rug_sequence_number,
224 x_rud_sequence_number,
225 x_creation_date,
226 x_created_by,
227 x_last_update_date,
228 x_last_updated_by,
229 x_last_update_login
230 );
231
232 IF (p_action = 'INSERT') THEN
233 -- Call all the procedures related to Before Insert.
234 Null;
235 IF Get_PK_For_Validation (
236 new_references.rug_sequence_number ,
237 new_references.rud_sequence_number
238 ) THEN
239 RAISE DUP_VAL_ON_INDEX; -- Changed By: Navin.Sinha On: 10/9/2003 Bug#: 3193855 Fix: Replaced IGS_GE_RECORD_ALREADY_EXISTS with DUP_VAL_ON_INDEX.
240 END IF;
241 Check_Constraints;
242 Check_Parent_Existance;
243 ELSIF (p_action = 'UPDATE') THEN
244 -- Call all the procedures related to Before Update.
245 Check_Constraints;
246 Check_Parent_Existance;
247 ELSIF (p_action = 'VALIDATE_INSERT') THEN
248 IF Get_PK_For_Validation (
249 new_references.rug_sequence_number ,
250 new_references.rud_sequence_number
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 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
258 Check_Constraints;
259 END IF;
260 END Before_DML;
261
262 PROCEDURE After_DML (
263 p_action IN VARCHAR2,
264 x_rowid IN VARCHAR2
265 ) as
266 BEGIN
267
268 l_rowid := x_rowid;
269
270 END After_DML;
271
272 procedure INSERT_ROW (
273 X_ROWID in out NOCOPY VARCHAR2,
274 X_RUD_SEQUENCE_NUMBER in NUMBER,
275 X_RUG_SEQUENCE_NUMBER in NUMBER,
276 X_MODE in VARCHAR2
277 ) as
278 cursor C is select ROWID from IGS_RU_GROUP_SET
279 where RUD_SEQUENCE_NUMBER = X_RUD_SEQUENCE_NUMBER
280 and RUG_SEQUENCE_NUMBER = X_RUG_SEQUENCE_NUMBER;
281 X_LAST_UPDATE_DATE DATE;
282 X_LAST_UPDATED_BY NUMBER;
283 X_LAST_UPDATE_LOGIN NUMBER;
284 begin
285 X_LAST_UPDATE_DATE := SYSDATE;
286 if(X_MODE = 'I') then
287 X_LAST_UPDATED_BY := 1;
288 X_LAST_UPDATE_LOGIN := 0;
289 elsif (X_MODE = 'R') then
290 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
291 if X_LAST_UPDATED_BY is NULL then
292 X_LAST_UPDATED_BY := -1;
293 end if;
294 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
295 if X_LAST_UPDATE_LOGIN is NULL then
296 X_LAST_UPDATE_LOGIN := -1;
297 end if;
298 else
299 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
300 IGS_GE_MSG_STACK.ADD;
301 app_exception.raise_exception;
302 end if;
303
304 Before_DML(
305 p_action=>'INSERT',
306 x_rowid=>X_ROWID,
307 x_rud_sequence_number=>X_RUD_SEQUENCE_NUMBER,
308 x_rug_sequence_number=>X_RUG_SEQUENCE_NUMBER,
309 x_creation_date=>X_LAST_UPDATE_DATE,
310 x_created_by=>X_LAST_UPDATED_BY,
311 x_last_update_date=>X_LAST_UPDATE_DATE,
312 x_last_updated_by=>X_LAST_UPDATED_BY,
313 x_last_update_login=>X_LAST_UPDATE_LOGIN
314 );
315
316 insert into IGS_RU_GROUP_SET (
317 RUG_SEQUENCE_NUMBER,
318 RUD_SEQUENCE_NUMBER,
319 CREATION_DATE,
320 CREATED_BY,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_LOGIN
324 ) values (
325 NEW_REFERENCES.RUG_SEQUENCE_NUMBER,
326 NEW_REFERENCES.RUD_SEQUENCE_NUMBER,
327 X_LAST_UPDATE_DATE,
328 X_LAST_UPDATED_BY,
329 X_LAST_UPDATE_DATE,
330 X_LAST_UPDATED_BY,
331 X_LAST_UPDATE_LOGIN
332 );
333
334 open c;
335 fetch c into X_ROWID;
336 if (c%notfound) then
337 close c;
338 raise no_data_found;
339 end if;
340 close c;
341
342 After_DML (
343 p_action => 'INSERT',
344 x_rowid => X_ROWID);
345
346 end INSERT_ROW;
347
348 procedure LOCK_ROW (
349 X_ROWID in VARCHAR2,
350 X_RUD_SEQUENCE_NUMBER in NUMBER,
351 X_RUG_SEQUENCE_NUMBER in NUMBER
352 ) as
353 cursor c1 is select ROWID
354 from IGS_RU_GROUP_SET
355 where ROWID = X_ROWID for update nowait;
356 tlinfo c1%rowtype;
357
358 begin
359 open c1;
360 fetch c1 into tlinfo;
361 if (c1%notfound) then
362 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
363 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_GROUP_SET : P_ACTION LOCK_ROW : IGSUI06B.PLS');
364 IGS_GE_MSG_STACK.ADD;
365 app_exception.raise_exception;
366 close c1;
367 return;
368 end if;
369 close c1;
370
371 return;
372 end LOCK_ROW;
373
374 procedure DELETE_ROW (
375 X_ROWID in VARCHAR2
376 ) as
377 begin
378
379 Before_DML (
380 p_action => 'DELETE',
381 x_rowid => X_ROWID);
382
383 delete from IGS_RU_GROUP_SET
384 where ROWID = X_ROWID;
385 if (sql%notfound) then
386 raise no_data_found;
387 end if;
388
389 After_DML (
390 p_action => 'DELETE',
391 x_rowid => X_ROWID);
392
393 end DELETE_ROW;
394
395 end IGS_RU_GROUP_SET_PKG;