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