[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_INTM_RCONDS_PKG
Source
1 PACKAGE BODY igs_en_intm_rconds_pkg AS
2 /* $Header: IGSEI80B.pls 120.0 2006/04/10 04:55:42 bdeviset noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_intm_rconds%ROWTYPE;
6 new_references igs_en_intm_rconds%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_return_condition IN VARCHAR2,
12 x_description IN VARCHAR2,
13 x_closed_flag IN VARCHAR2,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) AS
20 /*
21 || Created By :
22 || Created On : 12-MAR-2006
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM igs_en_intm_rconds
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 OPEN cur_old_ref_values;
42 FETCH cur_old_ref_values INTO old_references;
43 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
44 CLOSE cur_old_ref_values;
45 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
46 igs_ge_msg_stack.add;
47 app_exception.raise_exception;
48 RETURN;
49 END IF;
50 CLOSE cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.return_condition := x_return_condition;
54 new_references.description := x_description;
55 new_references.closed_flag := x_closed_flag;
56
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END set_column_values;
70
71
72 FUNCTION get_pk_for_validation (
73 x_return_condition IN VARCHAR2
74 ) RETURN BOOLEAN AS
75 /*
76 || Created By :
77 || Created On : 12-MAR-2006
78 || Purpose : Validates the Primary Key of the table.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 CURSOR cur_rowid IS
85 SELECT rowid
86 FROM igs_en_intm_rconds
87 WHERE return_condition = x_return_condition
88 FOR UPDATE NOWAIT;
89
90 lv_rowid cur_rowid%RowType;
91
92 BEGIN
93
94 OPEN cur_rowid;
95 FETCH cur_rowid INTO lv_rowid;
96 IF (cur_rowid%FOUND) THEN
97 CLOSE cur_rowid;
98 RETURN(TRUE);
99 ELSE
100 CLOSE cur_rowid;
101 RETURN(FALSE);
102 END IF;
103
104 END get_pk_for_validation;
105
106
107 PROCEDURE before_dml (
108 p_action IN VARCHAR2,
109 x_rowid IN VARCHAR2,
110 x_return_condition IN VARCHAR2,
111 x_description IN VARCHAR2,
112 x_closed_flag IN VARCHAR2,
113 x_creation_date IN DATE,
114 x_created_by IN NUMBER,
115 x_last_update_date IN DATE,
116 x_last_updated_by IN NUMBER,
117 x_last_update_login IN NUMBER
118 ) AS
119 /*
120 || Created By :
121 || Created On : 12-MAR-2006
122 || Purpose : Initialises the columns, Checks Constraints, Calls the
123 || Trigger Handlers for the table, before any DML operation.
124 || Known limitations, enhancements or remarks :
125 || Change History :
126 || Who When What
127 || (reverse chronological order - newest change first)
128 */
129 BEGIN
130
131 set_column_values (
132 p_action,
133 x_rowid,
134 x_return_condition,
135 x_description,
136 x_closed_flag,
137 x_creation_date,
138 x_created_by,
139 x_last_update_date,
140 x_last_updated_by,
141 x_last_update_login
142 );
143
144 IF (p_action = 'INSERT') THEN
145 -- Call all the procedures related to Before Insert.
146 IF ( get_pk_for_validation(
147 new_references.return_condition
148 )
149 ) THEN
150 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
151 igs_ge_msg_stack.add;
152 app_exception.raise_exception;
153 END IF;
154 ELSIF (p_action = 'VALIDATE_INSERT') THEN
155 -- Call all the procedures related to Before Insert.
156 IF ( get_pk_for_validation (
157 new_references.return_condition
158 )
159 ) THEN
160 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
161 igs_ge_msg_stack.add;
162 app_exception.raise_exception;
163 END IF;
164 END IF;
165
166 END before_dml;
167
168
169 PROCEDURE insert_row (
170 x_rowid IN OUT NOCOPY VARCHAR2,
171 x_return_condition IN VARCHAR2,
172 x_description IN VARCHAR2,
173 x_closed_flag IN VARCHAR2,
174 x_mode IN VARCHAR2
175 ) AS
176 /*
177 || Created By :
178 || Created On : 12-MAR-2006
179 || Purpose : Handles the INSERT DML logic for the table.
180 || Known limitations, enhancements or remarks :
181 || Change History :
182 || Who When What
183 || (reverse chronological order - newest change first)
184 */
185
186 x_last_update_date DATE;
187 x_last_updated_by NUMBER;
188 x_last_update_login NUMBER;
189
190 BEGIN
191
192 x_last_update_date := SYSDATE;
193 IF (x_mode = 'I') THEN
194 x_last_updated_by := 1;
195 x_last_update_login := 0;
196 ELSIF (x_mode = 'R') THEN
197 x_last_updated_by := fnd_global.user_id;
198 IF (x_last_updated_by IS NULL) THEN
199 x_last_updated_by := -1;
200 END IF;
201 x_last_update_login := fnd_global.login_id;
202 IF (x_last_update_login IS NULL) THEN
203 x_last_update_login := -1;
204 END IF;
205 ELSE
206 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
207 fnd_message.set_token ('ROUTINE', 'IGS_EN_INTM_RCONDS_PKG.INSERT_ROW');
208 igs_ge_msg_stack.add;
209 app_exception.raise_exception;
210 END IF;
211
212 -- new_references.org_id := igs_ge_gen_003.get_org_id;
213
214 before_dml(
215 p_action => 'INSERT',
216 x_rowid => x_rowid,
217 x_return_condition => x_return_condition,
218 x_description => x_description,
219 x_closed_flag => x_closed_flag,
220 x_creation_date => x_last_update_date,
221 x_created_by => x_last_updated_by,
222 x_last_update_date => x_last_update_date,
223 x_last_updated_by => x_last_updated_by,
224 x_last_update_login => x_last_update_login
225 );
226
227 INSERT INTO igs_en_intm_rconds (
228 return_condition,
229 description,
230 closed_flag,
231 creation_date,
232 created_by,
233 last_update_date,
234 last_updated_by,
235 last_update_login
236 ) VALUES (
237 new_references.return_condition,
238 new_references.description,
239 new_references.closed_flag,
240 x_last_update_date,
241 x_last_updated_by,
242 x_last_update_date,
243 x_last_updated_by,
244 x_last_update_login
245 ) RETURNING ROWID INTO x_rowid;
246
247 END insert_row;
248
249
250 PROCEDURE lock_row (
251 x_rowid IN VARCHAR2,
252 x_return_condition IN VARCHAR2,
253 x_description IN VARCHAR2,
254 x_closed_flag IN VARCHAR2
255 ) AS
256 /*
257 || Created By :
258 || Created On : 12-MAR-2006
259 || Purpose : Handles the LOCK mechanism for the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265 CURSOR c1 IS
266 SELECT
267 description,
268 closed_flag
269 FROM igs_en_intm_rconds
270 WHERE rowid = x_rowid
271 FOR UPDATE NOWAIT;
272
273 tlinfo c1%ROWTYPE;
274
275 BEGIN
276
277 OPEN c1;
278 FETCH c1 INTO tlinfo;
279 IF (c1%notfound) THEN
280 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
281 igs_ge_msg_stack.add;
282 CLOSE c1;
283 app_exception.raise_exception;
284 RETURN;
285 END IF;
286 CLOSE c1;
287
288 IF (
289 (tlinfo.description = x_description)
290 AND (tlinfo.closed_flag = x_closed_flag)
291 ) THEN
292 NULL;
293 ELSE
294 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
295 igs_ge_msg_stack.add;
296 app_exception.raise_exception;
297 END IF;
298
299 RETURN;
300
301 END lock_row;
302
303
304 PROCEDURE update_row (
305 x_rowid IN VARCHAR2,
306 x_return_condition IN VARCHAR2,
307 x_description IN VARCHAR2,
308 x_closed_flag IN VARCHAR2,
309 x_mode IN VARCHAR2
310 ) AS
311 /*
312 || Created By :
313 || Created On : 12-MAR-2006
314 || Purpose : Handles the UPDATE DML logic for the table.
315 || Known limitations, enhancements or remarks :
316 || Change History :
317 || Who When What
318 || (reverse chronological order - newest change first)
319 */
320 x_last_update_date DATE ;
321 x_last_updated_by NUMBER;
322 x_last_update_login NUMBER;
323
324 BEGIN
325
326 x_last_update_date := SYSDATE;
327 IF (X_MODE = 'I') THEN
328 x_last_updated_by := 1;
329 x_last_update_login := 0;
330 ELSIF (x_mode = 'R') THEN
331 x_last_updated_by := fnd_global.user_id;
332 IF x_last_updated_by IS NULL THEN
333 x_last_updated_by := -1;
334 END IF;
335 x_last_update_login := fnd_global.login_id;
336 IF (x_last_update_login IS NULL) THEN
337 x_last_update_login := -1;
338 END IF;
339 ELSE
340 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
341 fnd_message.set_token ('ROUTINE', 'IGS_EN_INTM_RCONDS_PKG.UPDATE_ROW');
342 igs_ge_msg_stack.add;
343 app_exception.raise_exception;
344 END IF;
345
346 before_dml(
347 p_action => 'UPDATE',
348 x_rowid => x_rowid,
349 x_return_condition => x_return_condition,
350 x_description => x_description,
351 x_closed_flag => x_closed_flag,
352 x_creation_date => x_last_update_date,
353 x_created_by => x_last_updated_by,
354 x_last_update_date => x_last_update_date,
355 x_last_updated_by => x_last_updated_by,
356 x_last_update_login => x_last_update_login
357 );
358
359 UPDATE igs_en_intm_rconds
360 SET
361 description = new_references.description,
362 closed_flag = new_references.closed_flag,
363 last_update_date = x_last_update_date,
364 last_updated_by = x_last_updated_by,
365 last_update_login = x_last_update_login
366 WHERE rowid = x_rowid;
367
368 IF (SQL%NOTFOUND) THEN
369 RAISE NO_DATA_FOUND;
370 END IF;
371
372 END update_row;
373
374
375 PROCEDURE add_row (
376 x_rowid IN OUT NOCOPY VARCHAR2,
377 x_return_condition IN VARCHAR2,
378 x_description IN VARCHAR2,
379 x_closed_flag IN VARCHAR2,
380 x_mode IN VARCHAR2
381 ) AS
382 /*
383 || Created By :
384 || Created On : 12-MAR-2006
385 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
386 || Known limitations, enhancements or remarks :
387 || Change History :
388 || Who When What
389 || (reverse chronological order - newest change first)
390 */
391 CURSOR c1 IS
392 SELECT rowid
393 FROM igs_en_intm_rconds
394 WHERE return_condition = x_return_condition;
395
396 BEGIN
397
398 OPEN c1;
399 FETCH c1 INTO x_rowid;
400 IF (c1%NOTFOUND) THEN
401 CLOSE c1;
402
403 insert_row (
404 x_rowid,
405 x_return_condition,
406 x_description,
407 x_closed_flag,
408 x_mode
409 );
410 RETURN;
411 END IF;
412 CLOSE c1;
413
414 update_row (
415 x_rowid,
416 x_return_condition,
417 x_description,
418 x_closed_flag,
419 x_mode
420 );
421
422 END add_row;
423
424
425 PROCEDURE delete_row (
426 x_rowid IN VARCHAR2
427 ) AS
428 /*
429 || Created By :
430 || Created On : 12-MAR-2006
431 || Purpose : Handles the DELETE DML logic for the table.
432 || Known limitations, enhancements or remarks :
433 || Change History :
434 || Who When What
435 || (reverse chronological order - newest change first)
436 */
437 BEGIN
438
439 before_dml (
440 p_action => 'DELETE',
441 x_rowid => x_rowid
442 );
443
444 DELETE FROM igs_en_intm_rconds
445 WHERE rowid = x_rowid;
446
447 IF (SQL%NOTFOUND) THEN
448 RAISE NO_DATA_FOUND;
449 END IF;
450
451 END delete_row;
452
453
454 END igs_en_intm_rconds_pkg;