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