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