[Home] [Help]
PACKAGE BODY: APPS.IGS_GE_MEASUREMENT_PKG
Source
1 package body IGS_GE_MEASUREMENT_PKG as
2 /* $Header: IGSMI02B.pls 115.3 2002/11/29 01:09:30 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_GE_MEASUREMENT%RowType;
6 new_references IGS_GE_MEASUREMENT%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_measurement_cd IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_measurement_type IN VARCHAR2 DEFAULT NULL,
14 x_closed_ind IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) as
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_GE_MEASUREMENT
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 Close cur_old_ref_values;
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.measurement_cd := x_measurement_cd;
46 new_references.description := x_description;
47 new_references.measurement_type := x_measurement_type;
48 new_references.closed_ind := x_closed_ind;
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 PROCEDURE Check_Constraints(
63 Column_Name IN VARCHAR2 DEFAULT NULL,
64 Column_Value IN VARCHAR2 DEFAULT NULL
65 )as
66 BEGIN
67 IF column_name is null then
68 NULL;
69 ELSIF upper(Column_name) = 'MEASUREMENT_CD' then
70 new_references.measurement_cd := column_value;
71 ELSIF upper(Column_name) = 'MEASUREMENT_TYPE' then
72 new_references.measurement_type := column_value;
73 ELSIF upper(Column_name) = 'CLOSED_IND' then
74 new_references.closed_ind := column_value;
75 END IF;
76
77 IF upper(Column_name) = 'MEASUREMENT_CD' OR column_name is null then
78 IF new_references.measurement_cd <> UPPER(new_references.measurement_cd ) THEN
79 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
80 IGS_GE_MSG_STACK.ADD;
81 App_Exception.Raise_Exception;
82 END IF;
83 END IF;
84 IF upper(Column_name) = 'MEASUREMENT_TYPE' OR column_name is null then
85 IF new_references.measurement_type <> UPPER(new_references.measurement_type ) THEN
86 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
87 IGS_GE_MSG_STACK.ADD;
88 App_Exception.Raise_Exception;
89 END IF;
90 END IF;
91 IF upper(Column_name) = 'CLOSED_IND' OR column_name is null then
92 IF new_references.closed_ind <> UPPER(new_references.closed_ind ) OR
93 new_references.closed_ind NOT IN ( 'Y' , 'N' ) then
94 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END IF;
98 END IF;
99 END Check_Constraints;
100
101 PROCEDURE Check_Child_Existance as
102 BEGIN
103
104 IGS_GR_AWD_CRMN_PKG.GET_FK_IGS_GE_MEASUREMENT (
105 old_references.measurement_cd
106 );
107
108 END Check_Child_Existance;
109
110 FUNCTION GET_PK_FOR_VALIDATION (
111 x_measurement_cd IN VARCHAR2
112 ) RETURN BOOLEAN as
113
114 CURSOR cur_rowid IS
115 SELECT rowid
116 FROM IGS_GE_MEASUREMENT
117 WHERE measurement_cd = x_measurement_cd
118 FOR UPDATE NOWAIT;
119
120 lv_rowid cur_rowid%RowType;
121
122 BEGIN
123
124 Open cur_rowid;
125 Fetch cur_rowid INTO lv_rowid;
126 IF (cur_rowid%FOUND) THEN
127 Close cur_rowid;
128 Return(TRUE);
129 ELSE
130 Close cur_rowid;
131 Return(FALSE);
132 END IF;
133 END Get_PK_For_Validation;
134
135 PROCEDURE Before_DML (
136 p_action IN VARCHAR2,
137 x_rowid IN VARCHAR2 DEFAULT NULL,
138 x_measurement_cd IN VARCHAR2 DEFAULT NULL,
139 x_description IN VARCHAR2 DEFAULT NULL,
140 x_measurement_type IN VARCHAR2 DEFAULT NULL,
141 x_closed_ind IN VARCHAR2 DEFAULT NULL,
142 x_creation_date IN DATE DEFAULT NULL,
143 x_created_by IN NUMBER DEFAULT NULL,
144 x_last_update_date IN DATE DEFAULT NULL,
145 x_last_updated_by IN NUMBER DEFAULT NULL,
146 x_last_update_login IN NUMBER DEFAULT NULL
147 ) as
148 BEGIN
149
150 Set_Column_Values (
151 p_action,
152 x_rowid,
153 x_measurement_cd,
154 x_description,
155 x_measurement_type,
156 x_closed_ind,
157 x_creation_date,
158 x_created_by,
159 x_last_update_date,
160 x_last_updated_by,
161 x_last_update_login
162 );
163
164 IF (p_action = 'INSERT') THEN
165 -- Call all the procedures related to Before Insert.
166 IF GET_PK_FOR_VALIDATION ( new_references.measurement_cd ) THEN
167 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 Check_Constraints;
172 ELSIF (p_action = 'UPDATE') THEN
173 -- Call all the procedures related to Before Update.
174 Check_Constraints;
175 Check_Child_Existance;
176 ELSIF (p_action = 'DELETE') THEN
177 -- Call all the procedures related to Before Delete.
178 Check_Child_Existance;
179 ELSIF (p_action = 'VALIDATE_INSERT') THEN
180 -- Call all the procedures related to Before Insert.
181 IF GET_PK_FOR_VALIDATION ( new_references.measurement_cd ) THEN
182 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186 Check_Constraints;
187 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
188 -- Call all the procedures related to Before Update.
189 Check_Constraints;
190 ELSIF (p_action = 'VALIDATE_DELETE') THEN
191 -- Call all the procedures related to Before Delete.
192 Check_Child_Existance;
193 END IF;
194
195 END Before_DML;
196
197 PROCEDURE After_DML (
198 p_action IN VARCHAR2,
199 x_rowid IN VARCHAR2
200 ) as
201 BEGIN
202
203 l_rowid := x_rowid;
204
205 IF (p_action = 'INSERT') THEN
206 -- Call all the procedures related to After Insert.
207 Null;
208 ELSIF (p_action = 'UPDATE') THEN
209 -- Call all the procedures related to After Update.
210 Null;
211 ELSIF (p_action = 'DELETE') THEN
212 -- Call all the procedures related to After Delete.
213 Null;
214 END IF;
215
216 END After_DML;
217
218
219 procedure INSERT_ROW (
220 X_ROWID in out NOCOPY VARCHAR2,
221 X_MEASUREMENT_CD in VARCHAR2,
222 X_DESCRIPTION in VARCHAR2,
223 X_MEASUREMENT_TYPE in VARCHAR2,
224 X_CLOSED_IND in VARCHAR2,
225 X_MODE in VARCHAR2 default 'R'
226 ) as
227 cursor C is select ROWID from IGS_GE_MEASUREMENT
228 where MEASUREMENT_CD = X_MEASUREMENT_CD;
229 X_LAST_UPDATE_DATE DATE;
230 X_LAST_UPDATED_BY NUMBER;
231 X_LAST_UPDATE_LOGIN NUMBER;
232 begin
233 X_LAST_UPDATE_DATE := SYSDATE;
234 if(X_MODE = 'I') then
235 X_LAST_UPDATED_BY := 1;
236 X_LAST_UPDATE_LOGIN := 0;
237 elsif (X_MODE = 'R') then
238 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
239 if X_LAST_UPDATED_BY is NULL then
240 X_LAST_UPDATED_BY := -1;
241 end if;
242 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
243 if X_LAST_UPDATE_LOGIN is NULL then
244 X_LAST_UPDATE_LOGIN := -1;
245 end if;
246 else
247 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
248 IGS_GE_MSG_STACK.ADD;
249 app_exception.raise_exception;
250 end if;
251
252 Before_DML (
253 p_action => 'INSERT',
254 x_rowid => X_ROWID,
255 x_measurement_cd => X_MEASUREMENT_CD,
256 x_description => X_DESCRIPTION,
257 x_measurement_type => X_MEASUREMENT_TYPE,
258 x_closed_ind => NVL(X_CLOSED_IND,'N'),
259 x_created_by => X_LAST_UPDATED_BY,
260 x_creation_date => X_LAST_UPDATE_DATE,
261 x_last_updated_by => X_LAST_UPDATED_BY,
262 x_last_update_date => X_LAST_UPDATE_DATE,
263 x_last_update_login => X_LAST_UPDATE_LOGIN
264 );
265
266 insert into IGS_GE_MEASUREMENT (
267 MEASUREMENT_CD,
268 DESCRIPTION,
269 MEASUREMENT_TYPE,
270 CLOSED_IND,
271 CREATION_DATE,
272 CREATED_BY,
273 LAST_UPDATE_DATE,
274 LAST_UPDATED_BY,
275 LAST_UPDATE_LOGIN
276 ) values (
277 NEW_REFERENCES.MEASUREMENT_CD,
278 NEW_REFERENCES.DESCRIPTION,
279 NEW_REFERENCES.MEASUREMENT_TYPE,
280 NEW_REFERENCES.CLOSED_IND,
281 X_LAST_UPDATE_DATE,
282 X_LAST_UPDATED_BY,
283 X_LAST_UPDATE_DATE,
284 X_LAST_UPDATED_BY,
285 X_LAST_UPDATE_LOGIN
286 );
287
288 open c;
289 fetch c into X_ROWID;
290 if (c%notfound) then
291 close c;
292 raise no_data_found;
293 end if;
294 close c;
295
296 After_DML (
297 p_action => 'INSERT',
298 x_rowid => X_ROWID
299 );
300 end INSERT_ROW;
301
302 procedure LOCK_ROW (
303 X_ROWID in VARCHAR2,
304 X_MEASUREMENT_CD in VARCHAR2,
305 X_DESCRIPTION in VARCHAR2,
306 X_MEASUREMENT_TYPE in VARCHAR2,
307 X_CLOSED_IND in VARCHAR2
308 ) as
309 cursor c1 is select
310 DESCRIPTION,
311 MEASUREMENT_TYPE,
312 CLOSED_IND
313 from IGS_GE_MEASUREMENT
314 where ROWID = X_ROWID
315 for update nowait;
316 tlinfo c1%rowtype;
317
318 begin
319 open c1;
320 fetch c1 into tlinfo;
321 if (c1%notfound) then
322 close c1;
323 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
324 IGS_GE_MSG_STACK.ADD;
325 app_exception.raise_exception;
326 return;
327 end if;
328 close c1;
329
330 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
331 AND (tlinfo.MEASUREMENT_TYPE = X_MEASUREMENT_TYPE)
332 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
333 ) then
334 null;
335 else
336 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
337 IGS_GE_MSG_sTACK.ADD;
338 app_exception.raise_exception;
339 end if;
340 return;
341 end LOCK_ROW;
342
343 procedure UPDATE_ROW (
344 X_ROWID in VARCHAR2,
345 X_MEASUREMENT_CD in VARCHAR2,
346 X_DESCRIPTION in VARCHAR2,
347 X_MEASUREMENT_TYPE in VARCHAR2,
348 X_CLOSED_IND in VARCHAR2,
349 X_MODE in VARCHAR2 default 'R'
350 ) as
351 X_LAST_UPDATE_DATE DATE;
352 X_LAST_UPDATED_BY NUMBER;
353 X_LAST_UPDATE_LOGIN NUMBER;
354 begin
355 X_LAST_UPDATE_DATE := SYSDATE;
356 if(X_MODE = 'I') then
357 X_LAST_UPDATED_BY := 1;
358 X_LAST_UPDATE_LOGIN := 0;
359 elsif (X_MODE = 'R') then
360 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
361 if X_LAST_UPDATED_BY is NULL then
362 X_LAST_UPDATED_BY := -1;
363 end if;
364 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
365 if X_LAST_UPDATE_LOGIN is NULL then
366 X_LAST_UPDATE_LOGIN := -1;
367 end if;
368 else
369 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
370 IGS_GE_MSG_STACK.ADD;
371 app_exception.raise_exception;
372 end if;
373
374 Before_DML (
375 p_action => 'UPDATE',
376 x_rowid => X_ROWID,
377 x_measurement_cd => X_MEASUREMENT_CD,
378 x_description => X_DESCRIPTION,
379 x_measurement_type => X_MEASUREMENT_TYPE,
380 x_closed_ind => X_CLOSED_IND,
381 x_created_by => X_LAST_UPDATED_BY,
382 x_creation_date => X_LAST_UPDATE_DATE,
383 x_last_updated_by => X_LAST_UPDATED_BY,
384 x_last_update_date => X_LAST_UPDATE_DATE,
385 x_last_update_login => X_LAST_UPDATE_LOGIN
386 );
387
388 update IGS_GE_MEASUREMENT set
389 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
390 MEASUREMENT_TYPE = NEW_REFERENCES.MEASUREMENT_TYPE,
391 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
392 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
393 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
394 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
395 where ROWID = X_ROWID
396 ;
397 if (sql%notfound) then
398 raise no_data_found;
399 end if;
400
401 After_DML (
402 p_action => 'UPDATE',
403 x_rowid => X_ROWID
404 );
405
406 end UPDATE_ROW;
407
408 procedure ADD_ROW (
409 X_ROWID in out NOCOPY VARCHAR2,
410 X_MEASUREMENT_CD in VARCHAR2,
411 X_DESCRIPTION in VARCHAR2,
412 X_MEASUREMENT_TYPE in VARCHAR2,
413 X_CLOSED_IND in VARCHAR2,
414 X_MODE in VARCHAR2 default 'R'
415 ) as
416 cursor c1 is select rowid from IGS_GE_MEASUREMENT
417 where MEASUREMENT_CD = X_MEASUREMENT_CD
418 ;
419
420 begin
421 open c1;
422 fetch c1 into X_ROWID;
423 if (c1%notfound) then
424 close c1;
425 INSERT_ROW (
426 X_ROWID,
427 X_MEASUREMENT_CD,
428 X_DESCRIPTION,
429 X_MEASUREMENT_TYPE,
430 X_CLOSED_IND,
431 X_MODE);
432 return;
433 end if;
434 close c1;
435 UPDATE_ROW (
436 X_ROWID,
437 X_MEASUREMENT_CD,
438 X_DESCRIPTION,
439 X_MEASUREMENT_TYPE,
440 X_CLOSED_IND,
441 X_MODE);
442 end ADD_ROW;
443
444 procedure DELETE_ROW (
445 X_ROWID in VARCHAR2
446 ) as
447 begin
448 Before_DML (
449 p_action => 'DELETE',
450 x_rowid => X_ROWID
451 );
452
453 delete from IGS_GE_MEASUREMENT
454 where ROWID = X_ROWID;
455 if (sql%notfound) then
456 raise no_data_found;
457 end if;
458 After_DML (
459 p_action => 'DELETE',
460 x_rowid => X_ROWID
461 );
462 end DELETE_ROW;
463
464 end IGS_GE_MEASUREMENT_PKG;