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