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