1 PACKAGE BODY igs_ad_dscp_attr_pkg AS
2 /* $Header: IGSAIE1B.pls 115.5 2003/01/23 04:44:38 knag ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_dscp_attr%ROWTYPE;
6 new_references igs_ad_dscp_attr%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_discrepancy_attr_id IN NUMBER DEFAULT NULL,
12 x_src_cat_id IN NUMBER DEFAULT NULL,
13 x_attribute_name IN VARCHAR2 DEFAULT NULL,
14 x_discrepancy_rule_cd 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 ) AS
21 /*
22 || Created By : pkpatel
23 || Created On : 18-JUN-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_AD_DSCP_ATTR
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.discrepancy_attr_id := x_discrepancy_attr_id;
55 new_references.src_cat_id := x_src_cat_id;
56 new_references.attribute_name := x_attribute_name;
57 new_references.discrepancy_rule_cd := x_discrepancy_rule_cd;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_uniqueness AS
75 /*
76 || Created By : pkpatel
77 || Created On : 18-JUN-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF ( get_uk_for_validation (
87 new_references.src_cat_id,
88 new_references.attribute_name
89 )
90 ) THEN
91 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 END IF;
95
96 END check_uniqueness;
97
98
99 PROCEDURE check_parent_existance AS
100 /*
101 || Created By : pkpatel
102 || Created On : 18-JUN-2001
103 || Purpose : Checks for the existance of Parent records.
104 || Known limitations, enhancements or remarks :
105 || Change History :
106 || Who When What
107 || (reverse chronological order - newest change first)
108 */
109 BEGIN
110
111 IF (((old_references.src_cat_id = new_references.src_cat_id)) OR
112 ((new_references.src_cat_id IS NULL))) THEN
113 NULL;
114 ELSIF NOT igs_ad_source_cat_pkg.get_pk_for_validation (
115 new_references.src_cat_id
116 ) THEN
117 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
118 igs_ge_msg_stack.add;
119 app_exception.raise_exception;
120 END IF;
121
122 END check_parent_existance;
123
124
125 FUNCTION get_pk_for_validation (
126 x_discrepancy_attr_id IN NUMBER
127 ) RETURN BOOLEAN AS
128 /*
129 || Created By : pkpatel
130 || Created On : 18-JUN-2001
131 || Purpose : Validates the Primary Key of the table.
132 || Known limitations, enhancements or remarks :
133 || Change History :
134 || Who When What
135 || (reverse chronological order - newest change first)
136 */
137 CURSOR cur_rowid IS
138 SELECT rowid
139 FROM igs_ad_dscp_attr
140 WHERE discrepancy_attr_id = x_discrepancy_attr_id
141 FOR UPDATE NOWAIT;
142
143 lv_rowid cur_rowid%RowType;
144
145 BEGIN
146
147 OPEN cur_rowid;
148 FETCH cur_rowid INTO lv_rowid;
149 IF (cur_rowid%FOUND) THEN
150 CLOSE cur_rowid;
151 RETURN(TRUE);
152 ELSE
153 CLOSE cur_rowid;
154 RETURN(FALSE);
155 END IF;
156
157 END get_pk_for_validation;
158
159
160 FUNCTION get_uk_for_validation (
161 x_src_cat_id IN NUMBER,
162 x_attribute_name IN VARCHAR2
163 ) RETURN BOOLEAN AS
164 /*
165 || Created By : pkpatel
166 || Created On : 18-JUN-2001
167 || Purpose : Validates the Unique Keys of the table.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igs_ad_dscp_attr
176 WHERE src_cat_id = x_src_cat_id
177 AND attribute_name = x_attribute_name
178 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
179
180 lv_rowid cur_rowid%RowType;
181
182 BEGIN
183
184 OPEN cur_rowid;
185 FETCH cur_rowid INTO lv_rowid;
186 IF (cur_rowid%FOUND) THEN
187 CLOSE cur_rowid;
188 RETURN (true);
189 ELSE
190 CLOSE cur_rowid;
191 RETURN(FALSE);
192 END IF;
193
194 END get_uk_for_validation ;
195
196
197 PROCEDURE get_fk_igs_ad_source_cat_all (
198 x_src_cat_id IN NUMBER
199 ) AS
200 /*
201 || Created By : pkpatel
202 || Created On : 18-JUN-2001
203 || Purpose : Validates the Foreign Keys for the table.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209 CURSOR cur_rowid IS
210 SELECT rowid
211 FROM igs_ad_dscp_attr
212 WHERE ((src_cat_id = x_src_cat_id));
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 OPEN cur_rowid;
219 FETCH cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 CLOSE cur_rowid;
222 fnd_message.set_name ('IGS', 'IGS_AD_ADDA_ADSC_FK');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 RETURN;
226 END IF;
227 CLOSE cur_rowid;
228
229 END get_fk_igs_ad_source_cat_all;
230
231
232 PROCEDURE before_dml (
233 p_action IN VARCHAR2,
234 x_rowid IN VARCHAR2 DEFAULT NULL,
235 x_discrepancy_attr_id IN NUMBER DEFAULT NULL,
236 x_src_cat_id IN NUMBER DEFAULT NULL,
237 x_attribute_name IN VARCHAR2 DEFAULT NULL,
238 x_discrepancy_rule_cd IN VARCHAR2 DEFAULT NULL,
239 x_creation_date IN DATE DEFAULT NULL,
240 x_created_by IN NUMBER DEFAULT NULL,
241 x_last_update_date IN DATE DEFAULT NULL,
242 x_last_updated_by IN NUMBER DEFAULT NULL,
243 x_last_update_login IN NUMBER DEFAULT NULL
244 ) AS
245 /*
246 || Created By : pkpatel
247 || Created On : 18-JUN-2001
248 || Purpose : Initialises the columns, Checks Constraints, Calls the
249 || Trigger Handlers for the table, before any DML operation.
250 || Known limitations, enhancements or remarks :
251 || Change History :
252 || Who When What
253 || (reverse chronological order - newest change first)
254 */
255 BEGIN
256
257 set_column_values (
258 p_action,
259 x_rowid,
260 x_discrepancy_attr_id,
261 x_src_cat_id,
262 x_attribute_name,
263 x_discrepancy_rule_cd,
264 x_creation_date,
265 x_created_by,
266 x_last_update_date,
267 x_last_updated_by,
268 x_last_update_login
269 );
270
271 IF (p_action = 'INSERT') THEN
272 -- Call all the procedures related to Before Insert.
273 IF ( get_pk_for_validation(
274 new_references.discrepancy_attr_id
275 )
276 ) THEN
277 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 END IF;
281 check_uniqueness;
282 check_parent_existance;
283 ELSIF (p_action = 'UPDATE') THEN
284 -- Call all the procedures related to Before Update.
285 check_uniqueness;
286 check_parent_existance;
287 ELSIF (p_action = 'VALIDATE_INSERT') THEN
288 -- Call all the procedures related to Before Insert.
289 IF ( get_pk_for_validation (
290 new_references.discrepancy_attr_id
291 )
292 ) THEN
293 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
294 igs_ge_msg_stack.add;
295 app_exception.raise_exception;
296 END IF;
297 check_uniqueness;
298 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
299 check_uniqueness;
300 END IF;
301
302 END before_dml;
303
304
305 PROCEDURE insert_row (
306 x_rowid IN OUT NOCOPY VARCHAR2,
307 x_discrepancy_attr_id IN OUT NOCOPY NUMBER,
308 x_src_cat_id IN NUMBER,
309 x_attribute_name IN VARCHAR2,
310 x_discrepancy_rule_cd IN VARCHAR2,
311 x_mode IN VARCHAR2 DEFAULT 'R'
312 ) AS
313 /*
314 || Created By : pkpatel
315 || Created On : 18-JUN-2001
316 || Purpose : Handles the INSERT DML logic for the table.
317 || Known limitations, enhancements or remarks :
318 || Change History :
319 || Who When What
320 || (reverse chronological order - newest change first)
321 */
322 CURSOR c IS
323 SELECT rowid
324 FROM igs_ad_dscp_attr
325 WHERE discrepancy_attr_id = x_discrepancy_attr_id;
326
327 x_last_update_date DATE;
328 x_last_updated_by NUMBER;
329 x_last_update_login NUMBER;
330
331 BEGIN
332
333 x_last_update_date := SYSDATE;
334 IF (x_mode = 'I') THEN
335 x_last_updated_by := 1;
336 x_last_update_login := 0;
337 ELSIF (x_mode = 'R') THEN
338 x_last_updated_by := fnd_global.user_id;
339 IF (x_last_updated_by IS NULL) THEN
340 x_last_updated_by := -1;
341 END IF;
342 x_last_update_login := fnd_global.login_id;
343 IF (x_last_update_login IS NULL) THEN
344 x_last_update_login := -1;
345 END IF;
346 ELSE
347 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
348 igs_ge_msg_stack.add;
349 app_exception.raise_exception;
350 END IF;
351
352 x_discrepancy_attr_id := -1;
353 before_dml(
354 p_action => 'INSERT',
355 x_rowid => x_rowid,
356 x_discrepancy_attr_id => x_discrepancy_attr_id,
357 x_src_cat_id => x_src_cat_id,
358 x_attribute_name => x_attribute_name,
359 x_discrepancy_rule_cd => x_discrepancy_rule_cd,
360 x_creation_date => x_last_update_date,
361 x_created_by => x_last_updated_by,
362 x_last_update_date => x_last_update_date,
363 x_last_updated_by => x_last_updated_by,
364 x_last_update_login => x_last_update_login
365 );
366
367 INSERT INTO igs_ad_dscp_attr (
368 discrepancy_attr_id,
369 src_cat_id,
370 attribute_name,
371 discrepancy_rule_cd,
372 creation_date,
373 created_by,
374 last_update_date,
375 last_updated_by,
376 last_update_login
377 ) VALUES (
378 IGS_AD_DSCP_ATTR_S.NEXTVAL,
379 new_references.src_cat_id,
380 new_references.attribute_name,
381 new_references.discrepancy_rule_cd,
382 x_last_update_date,
383 x_last_updated_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login
387 )RETURNING discrepancy_attr_id INTO x_discrepancy_attr_id;
388
389 OPEN c;
390 FETCH c INTO x_rowid;
391 IF (c%NOTFOUND) THEN
392 CLOSE c;
393 RAISE NO_DATA_FOUND;
394 END IF;
395 CLOSE c;
396
397 END insert_row;
398
399
400 PROCEDURE lock_row (
401 x_rowid IN VARCHAR2,
402 x_discrepancy_attr_id IN NUMBER,
403 x_src_cat_id IN NUMBER,
404 x_attribute_name IN VARCHAR2,
405 x_discrepancy_rule_cd IN VARCHAR2
406 ) AS
407 /*
408 || Created By : pkpatel
409 || Created On : 18-JUN-2001
410 || Purpose : Handles the LOCK mechanism for the table.
411 || Known limitations, enhancements or remarks :
412 || Change History :
413 || Who When What
414 || (reverse chronological order - newest change first)
415 */
416 CURSOR c1 IS
417 SELECT
418 src_cat_id,
419 attribute_name,
420 discrepancy_rule_cd
421 FROM igs_ad_dscp_attr
422 WHERE rowid = x_rowid
423 FOR UPDATE NOWAIT;
424
425 tlinfo c1%ROWTYPE;
426
427 BEGIN
428
429 OPEN c1;
430 FETCH c1 INTO tlinfo;
431 IF (c1%notfound) THEN
432 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
433 igs_ge_msg_stack.add;
434 CLOSE c1;
435 app_exception.raise_exception;
436 RETURN;
440 IF (
437 END IF;
438 CLOSE c1;
439
441 (tlinfo.src_cat_id = x_src_cat_id)
442 AND (tlinfo.attribute_name = x_attribute_name)
443 AND (tlinfo.discrepancy_rule_cd = x_discrepancy_rule_cd)
444 ) THEN
445 NULL;
446 ELSE
447 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
448 igs_ge_msg_stack.add;
449 app_exception.raise_exception;
450 END IF;
451
452 RETURN;
453
454 END lock_row;
455
456
457 PROCEDURE update_row (
458 x_rowid IN VARCHAR2,
459 x_discrepancy_attr_id IN NUMBER,
460 x_src_cat_id IN NUMBER,
461 x_attribute_name IN VARCHAR2,
462 x_discrepancy_rule_cd IN VARCHAR2,
463 x_mode IN VARCHAR2 DEFAULT 'R'
464 ) AS
465 /*
466 || Created By : pkpatel
467 || Created On : 18-JUN-2001
468 || Purpose : Handles the UPDATE DML logic for the table.
469 || Known limitations, enhancements or remarks :
470 || Change History :
471 || Who When What
472 || (reverse chronological order - newest change first)
473 */
474 x_last_update_date DATE ;
475 x_last_updated_by NUMBER;
476 x_last_update_login NUMBER;
477
478 BEGIN
479
480 x_last_update_date := SYSDATE;
481 IF (X_MODE = 'I') THEN
482 x_last_updated_by := 1;
483 x_last_update_login := 0;
484 ELSIF (x_mode = 'R') THEN
485 x_last_updated_by := fnd_global.user_id;
486 IF x_last_updated_by IS NULL THEN
487 x_last_updated_by := -1;
488 END IF;
489 x_last_update_login := fnd_global.login_id;
490 IF (x_last_update_login IS NULL) THEN
491 x_last_update_login := -1;
492 END IF;
493 ELSE
494 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
495 igs_ge_msg_stack.add;
496 app_exception.raise_exception;
497 END IF;
498
499 before_dml(
500 p_action => 'UPDATE',
501 x_rowid => x_rowid,
502 x_discrepancy_attr_id => x_discrepancy_attr_id,
503 x_src_cat_id => x_src_cat_id,
504 x_attribute_name => x_attribute_name,
505 x_discrepancy_rule_cd => x_discrepancy_rule_cd,
506 x_creation_date => x_last_update_date,
507 x_created_by => x_last_updated_by,
508 x_last_update_date => x_last_update_date,
509 x_last_updated_by => x_last_updated_by,
510 x_last_update_login => x_last_update_login
511 );
512
513 UPDATE igs_ad_dscp_attr
514 SET
515 src_cat_id = new_references.src_cat_id,
516 attribute_name = new_references.attribute_name,
517 discrepancy_rule_cd = new_references.discrepancy_rule_cd,
518 last_update_date = x_last_update_date,
519 last_updated_by = x_last_updated_by,
520 last_update_login = x_last_update_login
521 WHERE rowid = x_rowid;
522
523 IF (SQL%NOTFOUND) THEN
524 RAISE NO_DATA_FOUND;
525 END IF;
526
527 END update_row;
528
529
530 PROCEDURE add_row (
531 x_rowid IN OUT NOCOPY VARCHAR2,
532 x_discrepancy_attr_id IN OUT NOCOPY NUMBER,
533 x_src_cat_id IN NUMBER,
534 x_attribute_name IN VARCHAR2,
535 x_discrepancy_rule_cd IN VARCHAR2,
536 x_mode IN VARCHAR2 DEFAULT 'R'
537 ) AS
538 /*
539 || Created By : pkpatel
540 || Created On : 18-JUN-2001
541 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
542 || Known limitations, enhancements or remarks :
543 || Change History :
544 || Who When What
545 || (reverse chronological order - newest change first)
546 */
547 CURSOR c1 IS
548 SELECT rowid
549 FROM igs_ad_dscp_attr
550 WHERE discrepancy_attr_id = x_discrepancy_attr_id;
551
552 BEGIN
553
554 OPEN c1;
555 FETCH c1 INTO x_rowid;
556 IF (c1%NOTFOUND) THEN
557 CLOSE c1;
558
559 insert_row (
560 x_rowid,
561 x_discrepancy_attr_id,
562 x_src_cat_id,
563 x_attribute_name,
564 x_discrepancy_rule_cd,
565 x_mode
566 );
567 RETURN;
568 END IF;
569 CLOSE c1;
570
571 update_row (
572 x_rowid,
573 x_discrepancy_attr_id,
574 x_src_cat_id,
575 x_attribute_name,
576 x_discrepancy_rule_cd,
577 x_mode
578 );
579
580 END add_row;
581
582
583 PROCEDURE delete_row (
584 x_rowid IN VARCHAR2
585 ) AS
586 /*
587 || Created By : pkpatel
588 || Created On : 18-JUN-2001
592 || Who When What
589 || Purpose : Handles the DELETE DML logic for the table.
590 || Known limitations, enhancements or remarks :
591 || Change History :
593 || (reverse chronological order - newest change first)
594 */
595 BEGIN
596
597 before_dml (
598 p_action => 'DELETE',
599 x_rowid => x_rowid
600 );
601
602 DELETE FROM igs_ad_dscp_attr
603 WHERE rowid = x_rowid;
604
605 IF (SQL%NOTFOUND) THEN
606 RAISE NO_DATA_FOUND;
607 END IF;
608
609 END delete_row;
610
611
612 END igs_ad_dscp_attr_pkg;
[Home] [Help]