[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_OU_CO_PKG
Source
1 PACKAGE BODY igs_co_ou_co_pkg AS
2 /* $Header: IGSLI14B.pls 115.12 2002/11/29 01:05:49 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to IGS_CO_VAL_OC.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2(25);
10 old_references igs_co_ou_co_all%ROWTYPE;
11 new_references igs_co_ou_co_all%ROWTYPE;
12
13 PROCEDURE set_column_values (
14 p_action IN VARCHAR2,
15 x_rowid IN VARCHAR2 DEFAULT NULL,
16 x_person_id IN NUMBER DEFAULT NULL,
17 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
18 x_reference_number IN NUMBER DEFAULT NULL,
19 x_issue_dt IN DATE DEFAULT NULL,
20 x_addr_type IN VARCHAR2 DEFAULT NULL,
21 x_tracking_id IN NUMBER DEFAULT NULL,
22 x_comments IN VARCHAR2 DEFAULT NULL,
23 x_dt_sent IN DATE DEFAULT NULL,
24 x_unknown_return_dt IN DATE DEFAULT NULL,
25 x_letter_reference_number IN NUMBER DEFAULT NULL,
26 x_spl_sequence_number IN NUMBER DEFAULT NULL,
27 x_org_id IN NUMBER DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL
33 ) AS
34 /*
35 || Created By : [email protected]
36 || Created On : 14-DEC-2000
37 || Purpose : Initialises the Old and New references for the columns of the table.
38 || Known limitations, enhancements or remarks :
39 || Change History :
40 || Who When What
41 || (reverse chronological order - newest change first)
42 */
43
44 CURSOR cur_old_ref_values IS
45 SELECT *
46 FROM IGS_CO_OU_CO_ALL
47 WHERE rowid = x_rowid;
48
49 BEGIN
50
51 l_rowid := x_rowid;
52
53 -- Code for setting the Old and New Reference Values.
54 -- Populate Old Values.
55 OPEN cur_old_ref_values;
56 FETCH cur_old_ref_values INTO old_references;
57 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
58 CLOSE cur_old_ref_values;
59 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
60 igs_ge_msg_stack.add;
61 app_exception.raise_exception;
62 RETURN;
63 END IF;
64 CLOSE cur_old_ref_values;
65
66 -- Populate New Values.
67 new_references.person_id := x_person_id;
68 new_references.correspondence_type := x_correspondence_type;
69 new_references.reference_number := x_reference_number;
70 new_references.issue_dt := x_issue_dt;
71 new_references.addr_type := x_addr_type;
72 new_references.tracking_id := x_tracking_id;
73 new_references.comments := x_comments;
74 new_references.dt_sent := x_dt_sent;
75 new_references.unknown_return_dt := x_unknown_return_dt;
76 new_references.letter_reference_number := x_letter_reference_number;
77 new_references.spl_sequence_number := x_spl_sequence_number;
78 new_references.org_id := x_org_id;
79
80 IF (p_action = 'UPDATE') THEN
81 new_references.creation_date := old_references.creation_date;
82 new_references.created_by := old_references.created_by;
83 ELSE
84 new_references.creation_date := x_creation_date;
85 new_references.created_by := x_created_by;
86 END IF;
87
88 new_references.last_update_date := x_last_update_date;
89 new_references.last_updated_by := x_last_updated_by;
90 new_references.last_update_login := x_last_update_login;
91
92 END set_column_values;
93
94
95 PROCEDURE check_constraints (
96 column_name IN VARCHAR2 DEFAULT NULL,
97 column_value IN VARCHAR2 DEFAULT NULL
98 ) AS
99 /*
100 || Created By : [email protected]
101 || Created On : 14-DEC-2000
102 || Purpose : Handles the Check Constraint logic for the the columns.
103 || Known limitations, enhancements or remarks :
104 || Change History :
105 || Who When What
106 || (reverse chronological order - newest change first)
107 */
108 BEGIN
109
110 IF (column_name IS NULL) THEN
111 NULL;
112 ELSIF (UPPER(column_name) = 'REFERENCE_NUMBER') THEN
113 new_references.reference_number := igs_ge_number.to_num (column_value);
114 END IF;
115
116 IF (UPPER(column_name) = 'REFERENCE_NUMBER' OR column_name IS NULL) THEN
117 IF NOT (new_references.reference_number BETWEEN 1
118 AND 999999) THEN
119 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123 END IF;
124
125 END check_constraints;
126
127 PROCEDURE get_fk_igs_co_s_ltr (
128 x_correspondence_type IN VARCHAR2,
129 x_letter_reference_number IN NUMBER
130 ) AS
131 /*
132 || Created By : [email protected]
133 || Created On : 19-DEC-2000
134 || Purpose : Validates the Foreign Keys for the table.
135 || Known limitations, enhancements or remarks :
136 || Change History :
137 || Who When What
138 || (reverse chronological order - newest change first)
139 */
140 CURSOR cur_rowid IS
141 SELECT rowid
142 FROM igs_co_ou_co_all
143 WHERE ((correspondence_type = x_correspondence_type) AND
144 (spl_sequence_number = x_letter_reference_number));
145
146 lv_rowid cur_rowid%RowType;
147
148 BEGIN
149
150 OPEN cur_rowid;
151 FETCH cur_rowid INTO lv_rowid;
152 IF (cur_rowid%FOUND) THEN
153 CLOSE cur_rowid;
154 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 RETURN;
158 END IF;
159 CLOSE cur_rowid;
160
161 END get_fk_igs_co_s_ltr;
162
163
164 PROCEDURE check_child_existance IS
165 /*
166 || Created By : [email protected]
167 || Created On : 14-DEC-2000
168 || Purpose : Checks for the existance of Child records.
169 || Known limitations, enhancements or remarks :
170 || Change History :
171 || Who When What
172 || (reverse chronological order - newest change first)
173 */
174 BEGIN
175
176 igs_co_ou_co_ref_pkg.get_fk_igs_co_ou_co (
177 old_references.person_id,
178 old_references.correspondence_type,
179 old_references.reference_number,
180 old_references.issue_dt
181 );
182
183 END check_child_existance;
184
185 PROCEDURE BeforeRowInsertUpdate1(
186 p_inserting IN BOOLEAN DEFAULT FALSE,
187 p_updating IN BOOLEAN DEFAULT FALSE,
188 p_deleting IN BOOLEAN DEFAULT FALSE
189 ) AS
190 v_created_date IGS_CO_ITM_ALL.create_dt%TYPE;
191 v_message_name varchar2(30);
192 CURSOR c_cor_item IS
193 SELECT create_dt
194 FROM IGS_CO_ITM_ALL
195 WHERE correspondence_type = new_references.correspondence_type AND
196 reference_number = new_references.reference_number;
197 BEGIN
198 -- If trigger has not been disabled, perform required processing
199 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_CO_OU_CO_ALL') THEN
200 -- Fetch the Cor Item Created Date and validate it
201 OPEN c_cor_item;
202 FETCH c_cor_item INTO v_created_date;
203 CLOSE c_cor_item;
204 IF IGS_CO_VAL_OC.corp_val_oc_dateseq(
205 v_created_date,
206 new_references.issue_dt,
207 new_references.dt_sent,
208 new_references.unknown_return_dt,
209 v_message_name) = FALSE THEN
210 Fnd_Message.Set_Name('IGS',v_message_name);
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 END IF;
214 END IF;
215
216
217 END BeforeRowInsertUpdate1;
218
219
220 FUNCTION get_pk_for_validation (
221 x_person_id IN NUMBER,
222 x_correspondence_type IN VARCHAR2,
223 x_reference_number IN NUMBER,
224 x_issue_dt IN DATE
225 ) RETURN BOOLEAN AS
226 /*
227 || Created By : [email protected]
228 || Created On : 14-DEC-2000
229 || Purpose : Validates the Primary Key of the table.
230 || Known limitations, enhancements or remarks :
231 || Change History :
232 || Who When What
233 || (reverse chronological order - newest change first)
234 */
235 CURSOR cur_rowid IS
236 SELECT rowid
237 FROM igs_co_ou_co_all
238 WHERE person_id = x_person_id
239 AND correspondence_type = x_correspondence_type
240 AND reference_number = x_reference_number
241 AND issue_dt = x_issue_dt
242 FOR UPDATE NOWAIT;
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 OPEN cur_rowid;
249 FETCH cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 CLOSE cur_rowid;
252 RETURN(TRUE);
253 ELSE
254 CLOSE cur_rowid;
255 RETURN(FALSE);
256 END IF;
257
258 END get_pk_for_validation;
259
260
261 PROCEDURE before_dml (
262 p_action IN VARCHAR2,
263 x_rowid IN VARCHAR2 DEFAULT NULL,
264 x_person_id IN NUMBER DEFAULT NULL,
265 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
266 x_reference_number IN NUMBER DEFAULT NULL,
267 x_issue_dt IN DATE DEFAULT NULL,
268 x_addr_type IN VARCHAR2 DEFAULT NULL,
269 x_tracking_id IN NUMBER DEFAULT NULL,
270 x_comments IN VARCHAR2 DEFAULT NULL,
271 x_dt_sent IN DATE DEFAULT NULL,
272 x_unknown_return_dt IN DATE DEFAULT NULL,
273 x_letter_reference_number IN NUMBER DEFAULT NULL,
274 x_spl_sequence_number IN NUMBER DEFAULT NULL,
275 x_org_id IN NUMBER DEFAULT NULL,
276 x_creation_date IN DATE DEFAULT NULL,
277 x_created_by IN NUMBER DEFAULT NULL,
278 x_last_update_date IN DATE DEFAULT NULL,
279 x_last_updated_by IN NUMBER DEFAULT NULL,
280 x_last_update_login IN NUMBER DEFAULT NULL
281 ) AS
282 /*
283 || Created By : [email protected]
284 || Created On : 14-DEC-2000
285 || Purpose : Initialises the columns, Checks Constraints, Calls the
286 || Trigger Handlers for the table, before any DML operation.
287 || Known limitations, enhancements or remarks :
288 || Change History :
289 || Who When What
290 || (reverse chronological order - newest change first)
291 */
292 BEGIN
293
294 set_column_values (
295 p_action,
296 x_rowid,
297 x_person_id,
298 x_correspondence_type,
299 x_reference_number,
300 x_issue_dt,
301 x_addr_type,
302 x_tracking_id,
303 x_comments,
304 x_dt_sent,
305 x_unknown_return_dt,
306 x_letter_reference_number,
307 x_spl_sequence_number,
308 x_org_id,
309 x_creation_date,
310 x_created_by,
311 x_last_update_date,
312 x_last_updated_by,
313 x_last_update_login
314 );
315
316 IF (p_action = 'INSERT') THEN
317 -- Call all the procedures related to Before Insert.
318 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
319
320 IF ( get_pk_for_validation(
321 new_references.person_id,
322 new_references.correspondence_type,
323 new_references.reference_number,
324 new_references.issue_dt
325 )
326 ) THEN
327 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
328 igs_ge_msg_stack.add;
329 app_exception.raise_exception;
330 END IF;
331 check_constraints;
332 ELSIF (p_action = 'UPDATE') THEN
333 -- Call all the procedures related to Before Update.
334 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
335 check_constraints;
336 ELSIF (p_action = 'DELETE') THEN
337 -- Call all the procedures related to Before Delete.
338 check_child_existance;
339 ELSIF (p_action = 'VALIDATE_INSERT') THEN
340 -- Call all the procedures related to Before Insert.
341 IF ( get_pk_for_validation (
342 new_references.person_id,
343 new_references.correspondence_type,
344 new_references.reference_number,
345 new_references.issue_dt
346 )
347 ) THEN
348 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352 check_constraints;
353 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
354 check_constraints;
355 ELSIF (p_action = 'VALIDATE_DELETE') THEN
356 check_child_existance;
357 END IF;
358
359 END before_dml;
360
361
362 PROCEDURE insert_row (
363 x_rowid IN OUT NOCOPY VARCHAR2,
364 x_person_id IN NUMBER,
365 x_correspondence_type IN VARCHAR2,
366 x_reference_number IN NUMBER,
367 x_issue_dt IN OUT NOCOPY DATE,
368 x_addr_type IN VARCHAR2,
369 x_tracking_id IN NUMBER,
370 x_comments IN VARCHAR2,
371 x_dt_sent IN DATE,
372 x_unknown_return_dt IN DATE,
373 x_letter_reference_number IN NUMBER,
374 x_spl_sequence_number IN NUMBER,
375 x_org_id IN NUMBER,
376 x_mode IN VARCHAR2 DEFAULT 'R'
377 ) AS
378 /*
379 || Created By : [email protected]
380 || Created On : 14-DEC-2000
381 || Purpose : Handles the INSERT DML logic for the table.
382 || Known limitations, enhancements or remarks :
383 || Change History :
384 || Who When What
385 || (reverse chronological order - newest change first)
386 */
387 CURSOR c IS
388 SELECT rowid
389 FROM igs_co_ou_co_all
390 WHERE person_id = x_person_id
391 AND correspondence_type = x_correspondence_type
392 AND reference_number = x_reference_number
393 AND issue_dt = new_references.issue_dt;
394
395 x_last_update_date DATE;
396 x_last_updated_by NUMBER;
397 x_last_update_login NUMBER;
398 x_request_id NUMBER;
399 x_program_id NUMBER;
400 x_program_application_id NUMBER;
401 x_program_update_date DATE;
402
403 BEGIN
404
405 x_last_update_date := SYSDATE;
406 IF (x_mode = 'I') THEN
407 x_last_updated_by := 1;
408 x_last_update_login := 0;
409 ELSIF (x_mode = 'R') THEN
410 x_last_updated_by := fnd_global.user_id;
411 IF (x_last_updated_by IS NULL) THEN
412 x_last_updated_by := -1;
413 END IF;
414 x_last_update_login := fnd_global.login_id;
415 IF (x_last_update_login IS NULL) THEN
416 x_last_update_login := -1;
417 END IF;
418 x_request_id := fnd_global.conc_request_id;
419 x_program_id := fnd_global.conc_program_id;
420 x_program_application_id := fnd_global.prog_appl_id;
421
422 IF (x_request_id = -1) THEN
423 x_request_id := NULL;
424 x_program_id := NULL;
425 x_program_application_id := NULL;
426 x_program_update_date := NULL;
427 ELSE
428 x_program_update_date := SYSDATE;
429 END IF;
430 ELSE
431 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
432 igs_ge_msg_stack.add;
433 app_exception.raise_exception;
434 END IF;
435
436 before_dml(
437 p_action => 'INSERT',
438 x_rowid => x_rowid,
439 x_person_id => x_person_id,
440 x_correspondence_type => x_correspondence_type,
441 x_reference_number => x_reference_number,
442 x_issue_dt => NVL (x_issue_dt,sysdate ),
443 x_addr_type => x_addr_type,
444 x_tracking_id => x_tracking_id,
445 x_comments => x_comments,
446 x_dt_sent => x_dt_sent,
447 x_unknown_return_dt => x_unknown_return_dt,
448 x_letter_reference_number => x_letter_reference_number,
449 x_spl_sequence_number => x_spl_sequence_number,
450 x_org_id => igs_ge_gen_003.get_org_id,
451 x_creation_date => x_last_update_date,
452 x_created_by => x_last_updated_by,
453 x_last_update_date => x_last_update_date,
454 x_last_updated_by => x_last_updated_by,
455 x_last_update_login => x_last_update_login
456 );
457
458 INSERT INTO igs_co_ou_co_all (
459 person_id,
460 correspondence_type,
461 reference_number,
462 issue_dt,
463 addr_type,
464 tracking_id,
465 comments,
466 dt_sent,
467 unknown_return_dt,
468 letter_reference_number,
469 spl_sequence_number,
470 org_id,
471 creation_date,
472 created_by,
473 last_update_date,
474 last_updated_by,
475 last_update_login,
476 request_id,
477 program_id,
478 program_application_id,
479 program_update_date
480 ) VALUES (
481 new_references.person_id,
482 new_references.correspondence_type,
483 new_references.reference_number,
484 new_references.issue_dt,
485 new_references.addr_type,
486 new_references.tracking_id,
487 new_references.comments,
488 new_references.dt_sent,
489 new_references.unknown_return_dt,
490 new_references.letter_reference_number,
491 new_references.spl_sequence_number,
492 new_references.org_id,
493 x_last_update_date,
494 x_last_updated_by,
495 x_last_update_date,
496 x_last_updated_by,
497 x_last_update_login ,
498 x_request_id,
499 x_program_id,
500 x_program_application_id,
501 x_program_update_date
502 );
503
504 OPEN c;
505 FETCH c INTO x_rowid;
506 IF (c%NOTFOUND) THEN
507 CLOSE c;
508 RAISE NO_DATA_FOUND;
509 END IF;
510 CLOSE c;
511
512 END insert_row;
513
514
515 PROCEDURE lock_row (
516 x_rowid IN VARCHAR2,
517 x_person_id IN NUMBER,
518 x_correspondence_type IN VARCHAR2,
519 x_reference_number IN NUMBER,
520 x_issue_dt IN DATE,
521 x_addr_type IN VARCHAR2,
522 x_tracking_id IN NUMBER,
523 x_comments IN VARCHAR2,
524 x_dt_sent IN DATE,
525 x_unknown_return_dt IN DATE,
526 x_letter_reference_number IN NUMBER,
527 x_spl_sequence_number IN NUMBER
528 ) AS
529 /*
530 || Created By : [email protected]
531 || Created On : 14-DEC-2000
532 || Purpose : Handles the LOCK mechanism for the table.
533 || Known limitations, enhancements or remarks :
534 || Change History :
535 || Who When What
536 || (reverse chronological order - newest change first)
537 */
538 CURSOR c1 IS
539 SELECT
540 addr_type,
541 tracking_id,
542 comments,
543 dt_sent,
544 unknown_return_dt,
545 letter_reference_number,
546 spl_sequence_number
547
548 FROM igs_co_ou_co_all
549 WHERE rowid = x_rowid
550 FOR UPDATE NOWAIT;
551
552 tlinfo c1%ROWTYPE;
553
554 BEGIN
555
556 OPEN c1;
557 FETCH c1 INTO tlinfo;
558 IF (c1%notfound) THEN
559 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
560 igs_ge_msg_stack.add;
561 CLOSE c1;
562 app_exception.raise_exception;
563 RETURN;
564 END IF;
565 CLOSE c1;
566
567 IF (
568 ((tlinfo.addr_type = x_addr_type) OR ((tlinfo.addr_type IS NULL) AND (X_addr_type IS NULL)))
569 AND ((tlinfo.tracking_id = x_tracking_id) OR ((tlinfo.tracking_id IS NULL) AND (X_tracking_id IS NULL)))
570 AND ((tlinfo.comments = x_comments) OR ((tlinfo.comments IS NULL) AND (X_comments IS NULL)))
571 AND ((tlinfo.dt_sent = x_dt_sent) OR ((tlinfo.dt_sent IS NULL) AND (X_dt_sent IS NULL)))
572 AND ((tlinfo.unknown_return_dt = x_unknown_return_dt) OR ((tlinfo.unknown_return_dt IS NULL) AND (X_unknown_return_dt IS NULL)))
573 AND ((tlinfo.letter_reference_number = x_letter_reference_number) OR ((tlinfo.letter_reference_number IS NULL) AND (X_letter_reference_number IS NULL)))
574 AND ((tlinfo.spl_sequence_number = x_spl_sequence_number) OR ((tlinfo.spl_sequence_number IS NULL) AND (X_spl_sequence_number IS NULL)))
575
576 ) THEN
577 NULL;
578 ELSE
579 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
580 igs_ge_msg_stack.add;
581 app_exception.raise_exception;
582 END IF;
583
584 RETURN;
585
586 END lock_row;
587
588
589 PROCEDURE update_row (
590 x_rowid IN VARCHAR2,
591 x_person_id IN NUMBER,
592 x_correspondence_type IN VARCHAR2,
593 x_reference_number IN NUMBER,
594 x_issue_dt IN DATE,
595 x_addr_type IN VARCHAR2,
596 x_tracking_id IN NUMBER,
597 x_comments IN VARCHAR2,
598 x_dt_sent IN DATE,
599 x_unknown_return_dt IN DATE,
600 x_letter_reference_number IN NUMBER,
601 x_spl_sequence_number IN NUMBER,
602 x_mode IN VARCHAR2 DEFAULT 'R'
603 ) AS
604 /*
605 || Created By : [email protected]
606 || Created On : 14-DEC-2000
607 || Purpose : Handles the UPDATE DML logic for the table.
608 || Known limitations, enhancements or remarks :
609 || Change History :
610 || Who When What
611 || (reverse chronological order - newest change first)
612 */
613 x_last_update_date DATE ;
614 x_last_updated_by NUMBER;
615 x_last_update_login NUMBER;
616 x_request_id NUMBER;
617 x_program_id NUMBER;
618 x_program_application_id NUMBER;
619 x_program_update_date DATE;
620
621 BEGIN
622
623 x_last_update_date := SYSDATE;
624 IF (X_MODE = 'I') THEN
625 x_last_updated_by := 1;
626 x_last_update_login := 0;
627 ELSIF (x_mode = 'R') THEN
628 x_last_updated_by := fnd_global.user_id;
629 IF x_last_updated_by IS NULL THEN
630 x_last_updated_by := -1;
631 END IF;
632 x_last_update_login := fnd_global.login_id;
633 IF (x_last_update_login IS NULL) THEN
634 x_last_update_login := -1;
635 END IF;
636 ELSE
637 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
638 igs_ge_msg_stack.add;
639 app_exception.raise_exception;
640 END IF;
641
642 before_dml(
643 p_action => 'UPDATE',
644 x_rowid => x_rowid,
645 x_person_id => x_person_id,
646 x_correspondence_type => x_correspondence_type,
647 x_reference_number => x_reference_number,
648 x_issue_dt => NVL (x_issue_dt,sysdate ),
649 x_addr_type => x_addr_type,
650 x_tracking_id => x_tracking_id,
651 x_comments => x_comments,
652 x_dt_sent => x_dt_sent,
653 x_unknown_return_dt => x_unknown_return_dt,
654 x_letter_reference_number => x_letter_reference_number,
655 x_spl_sequence_number => x_spl_sequence_number,
656 x_creation_date => x_last_update_date,
657 x_created_by => x_last_updated_by,
658 x_last_update_date => x_last_update_date,
659 x_last_updated_by => x_last_updated_by,
660 x_last_update_login => x_last_update_login
661 );
662
663 IF (x_mode = 'R') THEN
664 x_request_id := fnd_global.conc_request_id;
665 x_program_id := fnd_global.conc_program_id;
666 x_program_application_id := fnd_global.prog_appl_id;
667 IF (x_request_id = -1) THEN
668 x_request_id := old_references.request_id;
669 x_program_id := old_references.program_id;
670 x_program_application_id := old_references.program_application_id;
671 x_program_update_date := old_references.program_update_date;
672 ELSE
673 x_program_update_date := SYSDATE;
674 END IF;
675 END IF;
676
677 UPDATE igs_co_ou_co_all
678 SET
679 addr_type = new_references.addr_type,
680 tracking_id = new_references.tracking_id,
681 comments = new_references.comments,
682 dt_sent = new_references.dt_sent,
683 unknown_return_dt = new_references.unknown_return_dt,
684 letter_reference_number = new_references.letter_reference_number,
685 spl_sequence_number = new_references.spl_sequence_number,
686 last_update_date = x_last_update_date,
687 last_updated_by = x_last_updated_by,
688 last_update_login = x_last_update_login ,
689 request_id = x_request_id,
690 program_id = x_program_id,
691 program_application_id = x_program_application_id,
692 program_update_date = x_program_update_date
693 WHERE rowid = x_rowid;
694
695 IF (SQL%NOTFOUND) THEN
696 RAISE NO_DATA_FOUND;
697 END IF;
698
699 END update_row;
700
701
702 PROCEDURE add_row (
703 x_rowid IN OUT NOCOPY VARCHAR2,
704 x_person_id IN NUMBER,
705 x_correspondence_type IN VARCHAR2,
706 x_reference_number IN NUMBER,
707 x_issue_dt IN OUT NOCOPY DATE,
708 x_addr_type IN VARCHAR2,
709 x_tracking_id IN NUMBER,
710 x_comments IN VARCHAR2,
711 x_dt_sent IN DATE,
712 x_unknown_return_dt IN DATE,
713 x_letter_reference_number IN NUMBER,
714 x_spl_sequence_number IN NUMBER,
715 x_org_id IN NUMBER,
716 x_mode IN VARCHAR2 DEFAULT 'R'
717 ) AS
718 /*
719 || Created By : [email protected]
720 || Created On : 14-DEC-2000
721 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
722 || Known limitations, enhancements or remarks :
723 || Change History :
724 || Who When What
725 || (reverse chronological order - newest change first)
726 */
727 CURSOR c1 IS
728 SELECT rowid
729 FROM igs_co_ou_co_all
730 WHERE person_id = x_person_id
731 AND correspondence_type = x_correspondence_type
732 AND reference_number = x_reference_number
733 AND issue_dt = NVL (x_issue_dt,SYSDATE);
734
735 BEGIN
736
737 OPEN c1;
738 FETCH c1 INTO x_rowid;
739 IF (c1%NOTFOUND) THEN
740 CLOSE c1;
741
742 insert_row (
743 x_rowid,
744 x_person_id,
745 x_correspondence_type,
746 x_reference_number,
747 x_issue_dt,
748 x_addr_type,
749 x_tracking_id,
750 x_comments,
751 x_dt_sent,
752 x_unknown_return_dt,
753 x_letter_reference_number,
754 x_spl_sequence_number,
755 x_org_id,
756 x_mode
757 );
758 RETURN;
759 END IF;
760 CLOSE c1;
761
762 update_row (
763 x_rowid,
764 x_person_id,
765 x_correspondence_type,
766 x_reference_number,
767 x_issue_dt,
768 x_addr_type,
769 x_tracking_id,
770 x_comments,
771 x_dt_sent,
772 x_unknown_return_dt,
773 x_letter_reference_number,
774 x_spl_sequence_number,
775 x_mode
776 );
777
778 END add_row;
779
780
781 PROCEDURE GET_FK_IGS_CO_ADDR_TYPE (
782 x_addr_type IN VARCHAR2
783 ) AS
784
785 CURSOR cur_rowid IS
786 SELECT rowid
787 FROM IGS_CO_OU_CO_ALL
788 WHERE addr_type = x_addr_type ;
789
790 lv_rowid cur_rowid%RowType;
791
792 BEGIN
793
794 Open cur_rowid;
795 Fetch cur_rowid INTO lv_rowid;
796 IF (cur_rowid%FOUND) THEN
797 Close cur_rowid;
798 Fnd_Message.Set_Name ('IGS', 'IGS_CO_OC_ADT_FK');
799 IGS_GE_MSG_STACK.ADD;
800 App_Exception.Raise_Exception;
801 Return;
802 END IF;
803 Close cur_rowid;
804
805 END GET_FK_IGS_CO_ADDR_TYPE;
806
807 PROCEDURE GET_FK_IGS_CO_ITM (
808 x_correspondence_type IN VARCHAR2,
809 x_reference_number IN NUMBER
810 ) AS
811
812 CURSOR cur_rowid IS
813 SELECT rowid
814 FROM IGS_CO_OU_CO_ALL
815 WHERE correspondence_type = x_correspondence_type
816 AND reference_number = x_reference_number ;
817
818 lv_rowid cur_rowid%RowType;
819
820 BEGIN
821
822 Open cur_rowid;
823 Fetch cur_rowid INTO lv_rowid;
824 IF (cur_rowid%FOUND) THEN
825 Close cur_rowid;
826 Fnd_Message.Set_Name ('IGS', 'IGS_CO_OC_CORI_FK');
827 IGS_GE_MSG_STACK.ADD;
828 App_Exception.Raise_Exception;
829 Return;
830 END IF;
831 Close cur_rowid;
832
833 END GET_FK_IGS_CO_ITM;
834
835 PROCEDURE GET_FK_IGS_PE_PERSON (
836 x_person_id IN NUMBER
837 ) AS
838
839 CURSOR cur_rowid IS
840 SELECT rowid
841 FROM IGS_CO_OU_CO_ALL
842 WHERE person_id = x_person_id ;
843
844 lv_rowid cur_rowid%RowType;
845
846 BEGIN
847
848 Open cur_rowid;
849 Fetch cur_rowid INTO lv_rowid;
850 IF (cur_rowid%FOUND) THEN
851 Close cur_rowid;
852 Fnd_Message.Set_Name ('IGS', 'IGS_CO_OC_PE_FK');
853 IGS_GE_MSG_STACK.ADD;
854 App_Exception.Raise_Exception;
855 Return;
856 END IF;
857 Close cur_rowid;
858
859 END GET_FK_IGS_PE_PERSON;
860
861 PROCEDURE GET_FK_IGS_TR_ITEM (
862 x_tracking_id IN NUMBER
863 ) AS
864
865 CURSOR cur_rowid IS
866 SELECT rowid
867 FROM IGS_CO_OU_CO_ALL
868 WHERE tracking_id = x_tracking_id ;
869
870 lv_rowid cur_rowid%RowType;
871
872 BEGIN
873
874 Open cur_rowid;
875 Fetch cur_rowid INTO lv_rowid;
876 IF (cur_rowid%FOUND) THEN
877 Close cur_rowid;
878 Fnd_Message.Set_Name ('IGS', 'IGS_CO_OC_TRI_FK');
879 IGS_GE_MSG_STACK.ADD;
880 App_Exception.Raise_Exception;
881 Return;
882 END IF;
883 Close cur_rowid;
884
885 END GET_FK_IGS_TR_ITEM;
886
887
888
889 PROCEDURE delete_row (
890 x_rowid IN VARCHAR2
891 ) AS
892 /*
893 || Created By : [email protected]
894 || Created On : 14-DEC-2000
895 || Purpose : Handles the DELETE DML logic for the table.
896 || Known limitations, enhancements or remarks :
897 || Change History :
898 || Who When What
899 || (reverse chronological order - newest change first)
900 */
901 BEGIN
902
903 before_dml (
904 p_action => 'DELETE',
905 x_rowid => x_rowid
906 );
907
908 DELETE FROM igs_co_ou_co_all
909 WHERE rowid = x_rowid;
910
911 IF (SQL%NOTFOUND) THEN
912 RAISE NO_DATA_FOUND;
913 END IF;
914
915 END delete_row;
916
917
918 END igs_co_ou_co_pkg;