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