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