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