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