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