1 PACKAGE BODY igs_co_cat_pkg AS
2 /* $Header: IGSLI02B.pls 115.5 2002/11/29 01:02:31 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_cat%ROWTYPE;
6 new_references igs_co_cat%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_correspondence_cat IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 /*
21 || Created By : [email protected]
22 || Created On : 14-DEC-2000
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_CO_CAT
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.correspondence_cat := x_correspondence_cat;
54 new_references.description := x_description;
55 new_references.closed_ind := x_closed_ind;
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 PROCEDURE check_constraints (
73 column_name IN VARCHAR2 DEFAULT NULL,
74 column_value IN VARCHAR2 DEFAULT NULL
75 ) AS
76 /*
77 || Created By : [email protected]
78 || Created On : 14-DEC-2000
79 || Purpose : Handles the Check Constraint logic for the the columns.
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84 */
85 BEGIN
86
87 IF (column_name IS NULL) THEN
88 NULL;
89 ELSIF (UPPER(column_name) = 'CLOSED_IND') THEN
90 new_references.closed_ind := column_value;
91 END IF;
92
93 IF (UPPER(column_name) = 'CLOSED_IND' OR column_name IS NULL) THEN
94 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
95 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
96 igs_ge_msg_stack.add;
97 app_exception.raise_exception;
98 END IF;
99 END IF;
100
101 END check_constraints;
102
103
104 PROCEDURE check_child_existance IS
105 /*
106 || Created By : [email protected]
107 || Created On : 14-DEC-2000
108 || Purpose : Checks for the existance of Child records.
109 || Known limitations, enhancements or remarks :
110 || Change History :
111 || Who When What
112 || (reverse chronological order - newest change first)
113 */
114 BEGIN
115
116 igs_co_cat_itm_pkg.get_fk_igs_co_cat (
117 old_references.correspondence_cat
118 );
119
120 igs_co_cat_map_pkg.get_fk_igs_co_cat (
121 old_references.correspondence_cat
122 );
123
124 END check_child_existance;
125
126
127 FUNCTION get_pk_for_validation (
128 x_correspondence_cat IN VARCHAR2
129 ) RETURN BOOLEAN AS
130 /*
131 || Created By : [email protected]
132 || Created On : 14-DEC-2000
133 || Purpose : Validates the Primary Key of the table.
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 */
139 CURSOR cur_rowid IS
140 SELECT rowid
141 FROM igs_co_cat
142 WHERE correspondence_cat = x_correspondence_cat
143 FOR UPDATE NOWAIT;
144
145 lv_rowid cur_rowid%RowType;
146
147 BEGIN
148
149 OPEN cur_rowid;
150 FETCH cur_rowid INTO lv_rowid;
151 IF (cur_rowid%FOUND) THEN
152 CLOSE cur_rowid;
153 RETURN(TRUE);
154 ELSE
155 CLOSE cur_rowid;
156 RETURN(FALSE);
157 END IF;
158
159 END get_pk_for_validation;
160
161
162 PROCEDURE before_dml (
163 p_action IN VARCHAR2,
164 x_rowid IN VARCHAR2 DEFAULT NULL,
165 x_correspondence_cat IN VARCHAR2 DEFAULT NULL,
166 x_description IN VARCHAR2 DEFAULT NULL,
167 x_closed_ind IN VARCHAR2 DEFAULT NULL,
168 x_creation_date IN DATE DEFAULT NULL,
169 x_created_by IN NUMBER DEFAULT NULL,
170 x_last_update_date IN DATE DEFAULT NULL,
171 x_last_updated_by IN NUMBER DEFAULT NULL,
172 x_last_update_login IN NUMBER DEFAULT NULL
173 ) AS
174 /*
175 || Created By : [email protected]
176 || Created On : 14-DEC-2000
177 || Purpose : Initialises the columns, Checks Constraints, Calls the
178 || Trigger Handlers for the table, before any DML operation.
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184 BEGIN
185
186 set_column_values (
187 p_action,
188 x_rowid,
189 x_correspondence_cat,
190 x_description,
191 x_closed_ind,
192 x_creation_date,
193 x_created_by,
194 x_last_update_date,
195 x_last_updated_by,
196 x_last_update_login
197 );
198
199 IF (p_action = 'INSERT') THEN
200 -- Call all the procedures related to Before Insert.
201 IF ( get_pk_for_validation(
202 new_references.correspondence_cat
203 )
204 ) THEN
205 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
206 igs_ge_msg_stack.add;
207 app_exception.raise_exception;
208 END IF;
209 check_constraints;
210 ELSIF (p_action = 'UPDATE') THEN
211 -- Call all the procedures related to Before Update.
212 check_constraints;
213 ELSIF (p_action = 'DELETE') THEN
214 -- Call all the procedures related to Before Delete.
215 check_child_existance;
216 ELSIF (p_action = 'VALIDATE_INSERT') THEN
217 -- Call all the procedures related to Before Insert.
218 IF ( get_pk_for_validation (
219 new_references.correspondence_cat
220 )
221 ) THEN
222 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 END IF;
226 check_constraints;
227 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
228 check_constraints;
229 ELSIF (p_action = 'VALIDATE_DELETE') THEN
230 check_child_existance;
231 END IF;
232
233 END before_dml;
234
235
236 PROCEDURE insert_row (
237 x_rowid IN OUT NOCOPY VARCHAR2,
238 x_correspondence_cat IN VARCHAR2,
239 x_description IN VARCHAR2,
240 x_closed_ind IN VARCHAR2,
241 x_mode IN VARCHAR2 DEFAULT 'R'
242 ) AS
243 /*
244 || Created By : [email protected]
245 || Created On : 14-DEC-2000
246 || Purpose : Handles the INSERT DML logic for the table.
247 || Known limitations, enhancements or remarks :
248 || Change History :
249 || Who When What
250 || (reverse chronological order - newest change first)
251 */
252 CURSOR c IS
253 SELECT rowid
254 FROM igs_co_cat
255 WHERE correspondence_cat = x_correspondence_cat;
256
257 x_last_update_date DATE;
258 x_last_updated_by NUMBER;
259 x_last_update_login NUMBER;
260
261 BEGIN
262
263 x_last_update_date := SYSDATE;
264 IF (x_mode = 'I') THEN
265 x_last_updated_by := 1;
266 x_last_update_login := 0;
267 ELSIF (x_mode = 'R') THEN
268 x_last_updated_by := fnd_global.user_id;
269 IF (x_last_updated_by IS NULL) THEN
270 x_last_updated_by := -1;
271 END IF;
272 x_last_update_login := fnd_global.login_id;
273 IF (x_last_update_login IS NULL) THEN
274 x_last_update_login := -1;
275 END IF;
276 ELSE
277 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 END IF;
281
282 before_dml(
283 p_action => 'INSERT',
284 x_rowid => x_rowid,
285 x_correspondence_cat => x_correspondence_cat,
286 x_description => x_description,
287 x_closed_ind => x_closed_ind,
288 x_creation_date => x_last_update_date,
289 x_created_by => x_last_updated_by,
290 x_last_update_date => x_last_update_date,
291 x_last_updated_by => x_last_updated_by,
292 x_last_update_login => x_last_update_login
293 );
294
295 INSERT INTO igs_co_cat (
296 correspondence_cat,
297 description,
298 closed_ind,
299 creation_date,
300 created_by,
301 last_update_date,
302 last_updated_by,
303 last_update_login
304 ) VALUES (
305 new_references.correspondence_cat,
306 new_references.description,
307 new_references.closed_ind,
308 x_last_update_date,
309 x_last_updated_by,
310 x_last_update_date,
311 x_last_updated_by,
312 x_last_update_login
313 );
314
315 OPEN c;
316 FETCH c INTO x_rowid;
317 IF (c%NOTFOUND) THEN
318 CLOSE c;
319 RAISE NO_DATA_FOUND;
320 END IF;
321 CLOSE c;
322
323 END insert_row;
324
325
326 PROCEDURE lock_row (
327 x_rowid IN VARCHAR2,
328 x_correspondence_cat IN VARCHAR2,
329 x_description IN VARCHAR2,
330 x_closed_ind IN VARCHAR2
331 ) AS
332 /*
333 || Created By : [email protected]
334 || Created On : 14-DEC-2000
335 || Purpose : Handles the LOCK mechanism for the table.
336 || Known limitations, enhancements or remarks :
337 || Change History :
338 || Who When What
339 || (reverse chronological order - newest change first)
340 */
341 CURSOR c1 IS
342 SELECT
343 description,
344 closed_ind
345 FROM igs_co_cat
346 WHERE rowid = x_rowid
347 FOR UPDATE NOWAIT;
348
349 tlinfo c1%ROWTYPE;
350
351 BEGIN
352
353 OPEN c1;
354 FETCH c1 INTO tlinfo;
355 IF (c1%notfound) THEN
356 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
357 igs_ge_msg_stack.add;
358 CLOSE c1;
359 app_exception.raise_exception;
360 RETURN;
361 END IF;
362 CLOSE c1;
363
364 IF (
365 (tlinfo.description = x_description)
366 AND (tlinfo.closed_ind = x_closed_ind)
367 ) THEN
368 NULL;
369 ELSE
370 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374
375 RETURN;
376
377 END lock_row;
378
379
383 x_description IN VARCHAR2,
380 PROCEDURE update_row (
381 x_rowid IN VARCHAR2,
382 x_correspondence_cat IN VARCHAR2,
384 x_closed_ind IN VARCHAR2,
385 x_mode IN VARCHAR2 DEFAULT 'R'
386 ) AS
387 /*
388 || Created By : [email protected]
389 || Created On : 14-DEC-2000
390 || Purpose : Handles the UPDATE DML logic for the table.
391 || Known limitations, enhancements or remarks :
392 || Change History :
393 || Who When What
394 || (reverse chronological order - newest change first)
395 */
396 x_last_update_date DATE ;
397 x_last_updated_by NUMBER;
398 x_last_update_login NUMBER;
399
400 BEGIN
401
402 x_last_update_date := SYSDATE;
403 IF (X_MODE = 'I') THEN
404 x_last_updated_by := 1;
405 x_last_update_login := 0;
406 ELSIF (x_mode = 'R') THEN
407 x_last_updated_by := fnd_global.user_id;
408 IF x_last_updated_by IS NULL THEN
409 x_last_updated_by := -1;
410 END IF;
411 x_last_update_login := fnd_global.login_id;
412 IF (x_last_update_login IS NULL) THEN
413 x_last_update_login := -1;
414 END IF;
415 ELSE
416 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
417 igs_ge_msg_stack.add;
418 app_exception.raise_exception;
419 END IF;
420
421 before_dml(
422 p_action => 'UPDATE',
423 x_rowid => x_rowid,
424 x_correspondence_cat => x_correspondence_cat,
425 x_description => x_description,
426 x_closed_ind => x_closed_ind,
427 x_creation_date => x_last_update_date,
428 x_created_by => x_last_updated_by,
429 x_last_update_date => x_last_update_date,
430 x_last_updated_by => x_last_updated_by,
431 x_last_update_login => x_last_update_login
432 );
433
434 UPDATE igs_co_cat
435 SET
436 description = new_references.description,
437 closed_ind = new_references.closed_ind,
438 last_update_date = x_last_update_date,
439 last_updated_by = x_last_updated_by,
440 last_update_login = x_last_update_login
441 WHERE rowid = x_rowid;
442
443 IF (SQL%NOTFOUND) THEN
444 RAISE NO_DATA_FOUND;
445 END IF;
446
447 END update_row;
448
449
450 PROCEDURE add_row (
451 x_rowid IN OUT NOCOPY VARCHAR2,
452 x_correspondence_cat IN VARCHAR2,
453 x_description IN VARCHAR2,
454 x_closed_ind IN VARCHAR2,
458 || Created By : [email protected]
459 || Created On : 14-DEC-2000
460 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
461 || Known limitations, enhancements or remarks :
462 || Change History :
463 || Who When What
464 || (reverse chronological order - newest change first)
465 */
466 CURSOR c1 IS
467 SELECT rowid
468 FROM igs_co_cat
469 WHERE correspondence_cat = x_correspondence_cat;
470
471 BEGIN
472
473 OPEN c1;
474 FETCH c1 INTO x_rowid;
475 IF (c1%NOTFOUND) THEN
476 CLOSE c1;
477
478 insert_row (
479 x_rowid,
480 x_correspondence_cat,
481 x_description,
482 x_closed_ind,
483 x_mode
484 );
485 RETURN;
486 END IF;
487 CLOSE c1;
488
489 update_row (
490 x_rowid,
491 x_correspondence_cat,
492 x_description,
493 x_closed_ind,
494 x_mode
495 );
496
497 END add_row;
498
499
500 PROCEDURE delete_row (
501 x_rowid IN VARCHAR2
502 ) AS
503 /*
504 || Created By : [email protected]
505 || Created On : 14-DEC-2000
506 || Purpose : Handles the DELETE DML logic for the table.
507 || Known limitations, enhancements or remarks :
508 || Change History :
509 || Who When What
510 || (reverse chronological order - newest change first)
511 */
512 BEGIN
513
514 before_dml (
515 p_action => 'DELETE',
516 x_rowid => x_rowid
517 );
518
519 DELETE FROM igs_co_cat
520 WHERE rowid = x_rowid;
521
522 IF (SQL%NOTFOUND) THEN
523 RAISE NO_DATA_FOUND;
524 END IF;
525
526 END delete_row;
527
528
529 END igs_co_cat_pkg;
457 /*
455 x_mode IN VARCHAR2 DEFAULT 'R'
456 ) AS
[Home] [Help]