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