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