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