[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_ISIR_CORR_PKG
Source
1 PACKAGE BODY igf_ap_isir_corr_pkg AS
2 /* $Header: IGFAI21B.pls 120.1 2005/10/28 05:41:00 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_ap_isir_corr_all%ROWTYPE;
6 new_references igf_ap_isir_corr_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_isirc_id IN NUMBER DEFAULT NULL,
12 x_isir_id IN NUMBER DEFAULT NULL,
13 x_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_sar_field_number IN NUMBER DEFAULT NULL,
16 x_original_value IN VARCHAR2 DEFAULT NULL,
17 x_batch_id IN VARCHAR2 DEFAULT NULL,
18 x_corrected_value IN VARCHAR2 DEFAULT NULL,
19 x_correction_status IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26 /*
27 || Created By : kpadiyar
28 || Created On : 11-DEC-2000
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGF_AP_ISIR_CORR_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.isirc_id := x_isirc_id;
60 new_references.isir_id := x_isir_id;
61 new_references.ci_sequence_number := x_ci_sequence_number;
62 new_references.ci_cal_type := x_ci_cal_type;
63 new_references.sar_field_number := x_sar_field_number;
64 new_references.original_value := x_original_value;
65 new_references.batch_id := x_batch_id;
66 new_references.corrected_value := x_corrected_value;
67 new_references.correction_status := x_correction_status;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83
84 PROCEDURE check_uniqueness AS
85 /*
86 || Created By : kpadiyar
87 || Created On : 11-DEC-2000
88 || Purpose : Handles the Unique Constraint logic defined for the columns.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94 BEGIN
95
96 IF ( get_uk_for_validation (
97 new_references.isir_id,
98 new_references.sar_field_number,
99 new_references.correction_status
100 )
101 ) THEN
102 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
103 igs_ge_msg_stack.add;
104 app_exception.raise_exception;
105 END IF;
106
107 END check_uniqueness;
108
109
110 PROCEDURE check_parent_existance AS
111 /*
112 || Created By : kpadiyar
113 || Created On : 11-DEC-2000
114 || Purpose : Checks for the existance of Parent records.
115 || Known limitations, enhancements or remarks :
116 || Change History :
117 || Who When What
118 || (reverse chronological order - newest change first)
119 */
120 BEGIN
121
122 IF (((old_references.isir_id = new_references.isir_id)) OR
123 ((new_references.isir_id IS NULL))) THEN
124 NULL;
125 ELSIF NOT igf_ap_isir_matched_pkg.get_pk_for_validation (
126 new_references.isir_id
127 ) THEN
128 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
129 igs_ge_msg_stack.add;
130 app_exception.raise_exception;
131 END IF;
132
133 IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
134 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
135 ((new_references.ci_cal_type IS NULL) OR
136 (new_references.ci_sequence_number IS NULL))) THEN
137 NULL;
138 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
139 new_references.ci_cal_type,
140 new_references.ci_sequence_number
141 ) THEN
142 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
143 igs_ge_msg_stack.add;
144 app_exception.raise_exception;
145 END IF;
146
147 END check_parent_existance;
148
149
150 FUNCTION get_pk_for_validation (
151 x_isirc_id IN NUMBER
152 ) RETURN BOOLEAN AS
153 /*
154 || Created By : kpadiyar
155 || Created On : 11-DEC-2000
156 || Purpose : Validates the Primary Key of the table.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM igf_ap_isir_corr_all
165 WHERE isirc_id = x_isirc_id
166 FOR UPDATE NOWAIT;
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 OPEN cur_rowid;
173 FETCH cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 CLOSE cur_rowid;
176 RETURN(TRUE);
177 ELSE
178 CLOSE cur_rowid;
179 RETURN(FALSE);
180 END IF;
181
182 END get_pk_for_validation;
183
184
185 FUNCTION get_uk_for_validation (
186 x_isir_id IN NUMBER,
187 x_sar_field_number IN NUMBER,
188 x_correction_status IN VARCHAR2
189 ) RETURN BOOLEAN AS
190 /*
191 || Created By : kpadiyar
192 || Created On : 11-DEC-2000
193 || Purpose : Validates the Unique Keys of the table.
194 || Known limitations, enhancements or remarks :
195 || Change History :
196 || Who When What
197 || (reverse chronological order - newest change first)
198 */
199 CURSOR cur_rowid IS
200 SELECT rowid
201 FROM igf_ap_isir_corr_all
202 WHERE isir_id = x_isir_id
203 AND ((sar_field_number = x_sar_field_number) OR (sar_field_number IS NULL AND x_sar_field_number IS NULL))
204 AND ((correction_status = x_correction_status) OR (correction_status IS NULL AND x_correction_status IS NULL))
205 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 OPEN cur_rowid;
212 FETCH cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 CLOSE cur_rowid;
215 RETURN (true);
216 ELSE
217 CLOSE cur_rowid;
218 RETURN(FALSE);
219 END IF;
220
221 END get_uk_for_validation ;
222
223
224 PROCEDURE get_fk_igf_ap_isir_matched (
225 x_isir_id IN NUMBER
226 ) AS
227 /*
228 || Created By : kpadiyar
229 || Created On : 11-DEC-2000
230 || Purpose : Validates the Foreign Keys for the table.
231 || Known limitations, enhancements or remarks :
232 || Change History :
233 || Who When What
234 || museshad 28-Oct-2005 Bug 4690726
235 || Added cursor c_non_ack_corr_count
236 || (reverse chronological order - newest change first)
237 */
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM igf_ap_isir_corr_all
241 WHERE ((isir_id = x_isir_id));
242
243 lv_rowid cur_rowid%RowType;
244
245 -- Get the number of corrections whose status is not 'ACKNOWLEDGED'
246 -- If the number is 0, all of them can be deleted.
247 -- Else, throw error.
248 CURSOR c_non_ack_corr_count(cp_isir_id NUMBER) IS
249 SELECT count(*) cnt
250 FROM igf_ap_isir_corr_all
251 WHERE isir_id = cp_isir_id
252 AND correction_status <> 'ACKNOWLEDGED';
253
254 l_non_ack_corr_count NUMBER;
255
256 BEGIN
257
258 OPEN cur_rowid;
259 FETCH cur_rowid INTO lv_rowid;
260 IF (cur_rowid%FOUND) THEN
261 OPEN c_non_ack_corr_count(x_isir_id);
262 FETCH c_non_ack_corr_count INTO l_non_ack_corr_count;
263 CLOSE c_non_ack_corr_count;
264 IF l_non_ack_corr_count <> 0 THEN
265 fnd_message.set_name ('IGF', 'IGF_AP_ISIRC_ISIRM_FK');
266 igs_ge_msg_stack.add;
267 app_exception.raise_exception;
268 RETURN;
269 ELSE
270 WHILE cur_rowid%FOUND LOOP
271 igf_ap_isir_corr_pkg.delete_row(lv_rowid.rowid);
272 FETCH cur_rowid INTO lv_rowid;
273 END LOOP;
274 END IF;
275 END IF;
276 CLOSE cur_rowid;
277
278 END get_fk_igf_ap_isir_matched;
279
280
281 PROCEDURE get_fk_igs_ca_inst_all (
282 x_cal_type IN VARCHAR2,
283 x_sequence_number IN NUMBER
284 ) AS
285 /*
286 || Created By : kpadiyar
287 || Created On : 11-DEC-2000
288 || Purpose : Validates the Foreign Keys for the table.
289 || Known limitations, enhancements or remarks :
290 || Change History :
291 || Who When What
292 || (reverse chronological order - newest change first)
293 */
294 CURSOR cur_rowid IS
295 SELECT rowid
296 FROM igf_ap_isir_corr_all
297 WHERE ((ci_cal_type = x_cal_type) AND
298 (ci_sequence_number = x_sequence_number));
299
300 lv_rowid cur_rowid%RowType;
301
302 BEGIN
303
304 OPEN cur_rowid;
305 FETCH cur_rowid INTO lv_rowid;
306 IF (cur_rowid%FOUND) THEN
307 CLOSE cur_rowid;
308 fnd_message.set_name ('IGF', 'IGF_AP_ISIRC_CI_FK');
309 igs_ge_msg_stack.add;
310 app_exception.raise_exception;
311 RETURN;
312 END IF;
313 CLOSE cur_rowid;
314
315 END get_fk_igs_ca_inst_all;
316
317
318 PROCEDURE before_dml (
319 p_action IN VARCHAR2,
320 x_rowid IN VARCHAR2 DEFAULT NULL,
321 x_isirc_id IN NUMBER DEFAULT NULL,
322 x_isir_id IN NUMBER DEFAULT NULL,
323 x_ci_sequence_number IN NUMBER DEFAULT NULL,
324 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
325 x_sar_field_number IN NUMBER DEFAULT NULL,
326 x_original_value IN VARCHAR2 DEFAULT NULL,
327 x_batch_id IN VARCHAR2 DEFAULT NULL,
328 x_corrected_value IN VARCHAR2 DEFAULT NULL,
329 x_correction_status IN VARCHAR2 DEFAULT NULL,
330 x_creation_date IN DATE DEFAULT NULL,
331 x_created_by IN NUMBER DEFAULT NULL,
332 x_last_update_date IN DATE DEFAULT NULL,
333 x_last_updated_by IN NUMBER DEFAULT NULL,
334 x_last_update_login IN NUMBER DEFAULT NULL
335 ) AS
336 /*
337 || Created By : kpadiyar
338 || Created On : 11-DEC-2000
339 || Purpose : Initialises the columns, Checks Constraints, Calls the
340 || Trigger Handlers for the table, before any DML operation.
341 || Known limitations, enhancements or remarks :
342 || Change History :
343 || Who When What
344 || (reverse chronological order - newest change first)
345 */
346 BEGIN
347
348 set_column_values (
349 p_action,
350 x_rowid,
351 x_isirc_id,
352 x_isir_id,
353 x_ci_sequence_number,
354 x_ci_cal_type,
355 x_sar_field_number,
356 x_original_value,
357 x_batch_id,
358 x_corrected_value,
359 x_correction_status,
360 x_creation_date,
361 x_created_by,
362 x_last_update_date,
363 x_last_updated_by,
364 x_last_update_login
365 );
366
370 new_references.isirc_id
367 IF (p_action = 'INSERT') THEN
368 -- Call all the procedures related to Before Insert.
369 IF ( get_pk_for_validation(
371 )
372 ) THEN
373 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
374 igs_ge_msg_stack.add;
375 app_exception.raise_exception;
376 END IF;
377 check_uniqueness;
378 check_parent_existance;
379 ELSIF (p_action = 'UPDATE') THEN
380 -- Call all the procedures related to Before Update.
381 check_uniqueness;
382 check_parent_existance;
383 ELSIF (p_action = 'VALIDATE_INSERT') THEN
384 -- Call all the procedures related to Before Insert.
385 IF ( get_pk_for_validation (
386 new_references.isirc_id
387 )
388 ) THEN
389 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
390 igs_ge_msg_stack.add;
391 app_exception.raise_exception;
392 END IF;
393 check_uniqueness;
394 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
395 check_uniqueness;
396 END IF;
397
398 END before_dml;
399
400
401 PROCEDURE insert_row (
402 x_rowid IN OUT NOCOPY VARCHAR2,
403 x_isirc_id IN OUT NOCOPY NUMBER,
404 x_isir_id IN NUMBER,
405 x_ci_sequence_number IN NUMBER,
406 x_ci_cal_type IN VARCHAR2,
407 x_sar_field_number IN NUMBER,
408 x_original_value IN VARCHAR2,
409 x_batch_id IN VARCHAR2,
410 x_corrected_value IN VARCHAR2,
411 x_correction_status IN VARCHAR2,
412 x_mode IN VARCHAR2 DEFAULT 'R'
413 ) AS
414 /*
415 || Created By : kpadiyar
416 || Created On : 11-DEC-2000
417 || Purpose : Handles the INSERT DML logic for the table.
418 || Known limitations, enhancements or remarks :
419 || Change History :
420 || Who When What
421 || (reverse chronological order - newest change first)
422 */
423 CURSOR c IS
424 SELECT rowid
425 FROM igf_ap_isir_corr_all
426 WHERE isirc_id = x_isirc_id;
427
428 CURSOR cur_seq IS
429 SELECT igf_ap_isir_corr_s.nextval
430 FROM dual;
431
432 x_last_update_date DATE;
433 x_last_updated_by NUMBER;
434 x_last_update_login NUMBER;
435 x_request_id NUMBER;
436 x_program_id NUMBER;
437 x_program_application_id NUMBER;
438 x_program_update_date DATE;
439 l_org_id igf_ap_isir_corr_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
440
441 BEGIN
442
443 OPEN cur_seq;
444 FETCH cur_seq INTO x_isirc_id;
445 CLOSE cur_seq;
446
447 x_last_update_date := SYSDATE;
448 IF (x_mode = 'I') THEN
449 x_last_updated_by := 1;
450 x_last_update_login := 0;
451 ELSIF (x_mode = 'R') THEN
452 x_last_updated_by := fnd_global.user_id;
453 IF (x_last_updated_by IS NULL) THEN
454 x_last_updated_by := -1;
455 END IF;
456 x_last_update_login := fnd_global.login_id;
457 IF (x_last_update_login IS NULL) THEN
458 x_last_update_login := -1;
459 END IF;
460 x_request_id := fnd_global.conc_request_id;
461 x_program_id := fnd_global.conc_program_id;
462 x_program_application_id := fnd_global.prog_appl_id;
463
464 IF (x_request_id = -1) THEN
465 x_request_id := NULL;
466 x_program_id := NULL;
467 x_program_application_id := NULL;
468 x_program_update_date := NULL;
469 ELSE
470 x_program_update_date := SYSDATE;
471 END IF;
472 ELSE
473 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
474 igs_ge_msg_stack.add;
475 app_exception.raise_exception;
476 END IF;
477
478 before_dml(
479 p_action => 'INSERT',
480 x_rowid => x_rowid,
481 x_isirc_id => x_isirc_id,
482 x_isir_id => x_isir_id,
483 x_ci_sequence_number => x_ci_sequence_number,
484 x_ci_cal_type => x_ci_cal_type,
485 x_sar_field_number => x_sar_field_number,
486 x_original_value => x_original_value,
487 x_batch_id => x_batch_id,
488 x_corrected_value => x_corrected_value,
489 x_correction_status => x_correction_status,
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
500 ci_sequence_number,
497 INSERT INTO igf_ap_isir_corr_all (
498 isirc_id,
499 isir_id,
501 ci_cal_type,
502 sar_field_number,
503 original_value,
504 batch_id,
505 corrected_value,
506 correction_status,
507 creation_date,
508 created_by,
509 last_update_date,
510 last_updated_by,
511 last_update_login,
512 request_id,
513 program_id,
514 program_application_id,
515 program_update_date,
516 org_id
517 ) VALUES (
518 new_references.isirc_id,
519 new_references.isir_id,
520 new_references.ci_sequence_number,
521 new_references.ci_cal_type,
522 new_references.sar_field_number,
523 new_references.original_value,
524 new_references.batch_id,
525 new_references.corrected_value,
526 new_references.correction_status,
527 x_last_update_date,
528 x_last_updated_by,
529 x_last_update_date,
530 x_last_updated_by,
531 x_last_update_login ,
532 x_request_id,
533 x_program_id,
534 x_program_application_id,
535 x_program_update_date ,
536 l_org_id
537 );
538
539 OPEN c;
540 FETCH c INTO x_rowid;
541 IF (c%NOTFOUND) THEN
542 CLOSE c;
543 RAISE NO_DATA_FOUND;
544 END IF;
545 CLOSE c;
546
547 END insert_row;
548
549
550 PROCEDURE lock_row (
551 x_rowid IN VARCHAR2,
552 x_isirc_id IN NUMBER,
553 x_isir_id IN NUMBER,
554 x_ci_sequence_number IN NUMBER,
555 x_ci_cal_type IN VARCHAR2,
556 x_sar_field_number IN NUMBER,
557 x_original_value IN VARCHAR2,
558 x_batch_id IN VARCHAR2,
559 x_corrected_value IN VARCHAR2,
560 x_correction_status IN VARCHAR2
561 ) AS
562 /*
563 || Created By : kpadiyar
564 || Created On : 11-DEC-2000
565 || Purpose : Handles the LOCK mechanism for the table.
566 || Known limitations, enhancements or remarks :
567 || Change History :
568 || Who When What
569 || (reverse chronological order - newest change first)
570 */
571 CURSOR c1 IS
572 SELECT
573 isir_id,
574 ci_sequence_number,
575 ci_cal_type,
576 sar_field_number,
577 original_value,
578 batch_id,
579 corrected_value,
580 correction_status,
581 org_id
582 FROM igf_ap_isir_corr_all
583 WHERE rowid = x_rowid
584 FOR UPDATE NOWAIT;
585
586 tlinfo c1%ROWTYPE;
587
588 BEGIN
589
590 OPEN c1;
591 FETCH c1 INTO tlinfo;
592 IF (c1%notfound) THEN
593 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
594 igs_ge_msg_stack.add;
595 CLOSE c1;
596 app_exception.raise_exception;
597 RETURN;
598 END IF;
599 CLOSE c1;
600
601 IF (
602 (tlinfo.isir_id = x_isir_id)
603 AND ((tlinfo.ci_sequence_number = x_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
604 AND ((tlinfo.ci_cal_type = x_ci_cal_type) OR ((tlinfo.ci_cal_type IS NULL) AND (X_ci_cal_type IS NULL)))
605 AND ((tlinfo.sar_field_number = x_sar_field_number) OR ((tlinfo.sar_field_number IS NULL) AND (X_sar_field_number IS NULL)))
606 AND ((tlinfo.original_value = x_original_value) OR ((tlinfo.original_value IS NULL) AND (X_original_value IS NULL)))
607 AND ((tlinfo.batch_id = x_batch_id) OR ((tlinfo.batch_id IS NULL) AND (X_batch_id IS NULL)))
608 AND ((tlinfo.corrected_value = x_corrected_value) OR ((tlinfo.corrected_value IS NULL) AND (X_corrected_value IS NULL)))
609 AND ((tlinfo.correction_status = x_correction_status) OR ((tlinfo.correction_status IS NULL) AND (X_correction_status IS NULL)))
610 ) THEN
611 NULL;
612 ELSE
613 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
614 igs_ge_msg_stack.add;
615 app_exception.raise_exception;
616 END IF;
617
618 RETURN;
619
620 END lock_row;
621
622
623 PROCEDURE update_row (
624 x_rowid IN VARCHAR2,
625 x_isirc_id IN NUMBER,
626 x_isir_id IN NUMBER,
627 x_ci_sequence_number IN NUMBER,
628 x_ci_cal_type IN VARCHAR2,
629 x_sar_field_number IN NUMBER,
630 x_original_value IN VARCHAR2,
631 x_batch_id IN VARCHAR2,
632 x_corrected_value IN VARCHAR2,
633 x_correction_status IN VARCHAR2,
634 x_mode IN VARCHAR2 DEFAULT 'R'
635 ) AS
636 /*
637 || Created By : kpadiyar
641 || Change History :
638 || Created On : 11-DEC-2000
639 || Purpose : Handles the UPDATE DML logic for the table.
640 || Known limitations, enhancements or remarks :
642 || Who When What
643 || (reverse chronological order - newest change first)
644 */
645 x_last_update_date DATE ;
646 x_last_updated_by NUMBER;
647 x_last_update_login NUMBER;
648 x_request_id NUMBER;
649 x_program_id NUMBER;
650 x_program_application_id NUMBER;
651 x_program_update_date DATE;
652
653 BEGIN
654
655 x_last_update_date := SYSDATE;
656 IF (X_MODE = 'I') THEN
657 x_last_updated_by := 1;
658 x_last_update_login := 0;
659 ELSIF (x_mode = 'R') THEN
660 x_last_updated_by := fnd_global.user_id;
661 IF x_last_updated_by IS NULL THEN
662 x_last_updated_by := -1;
663 END IF;
664 x_last_update_login := fnd_global.login_id;
665 IF (x_last_update_login IS NULL) THEN
666 x_last_update_login := -1;
667 END IF;
668 ELSE
669 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
670 igs_ge_msg_stack.add;
671 app_exception.raise_exception;
672 END IF;
673
674 before_dml(
675 p_action => 'UPDATE',
676 x_rowid => x_rowid,
677 x_isirc_id => x_isirc_id,
678 x_isir_id => x_isir_id,
679 x_ci_sequence_number => x_ci_sequence_number,
680 x_ci_cal_type => x_ci_cal_type,
681 x_sar_field_number => x_sar_field_number,
682 x_original_value => x_original_value,
683 x_batch_id => x_batch_id,
684 x_corrected_value => x_corrected_value,
685 x_correction_status => x_correction_status,
686 x_creation_date => x_last_update_date,
687 x_created_by => x_last_updated_by,
688 x_last_update_date => x_last_update_date,
689 x_last_updated_by => x_last_updated_by,
690 x_last_update_login => x_last_update_login
691 );
692
693 IF (x_mode = 'R') THEN
694 x_request_id := fnd_global.conc_request_id;
695 x_program_id := fnd_global.conc_program_id;
696 x_program_application_id := fnd_global.prog_appl_id;
697 IF (x_request_id = -1) THEN
698 x_request_id := old_references.request_id;
699 x_program_id := old_references.program_id;
700 x_program_application_id := old_references.program_application_id;
701 x_program_update_date := old_references.program_update_date;
702 ELSE
703 x_program_update_date := SYSDATE;
704 END IF;
705 END IF;
706
707 UPDATE igf_ap_isir_corr_all
708 SET
709 isir_id = new_references.isir_id,
710 ci_sequence_number = new_references.ci_sequence_number,
711 ci_cal_type = new_references.ci_cal_type,
712 sar_field_number = new_references.sar_field_number,
713 original_value = new_references.original_value,
714 batch_id = new_references.batch_id,
715 corrected_value = new_references.corrected_value,
716 correction_status = new_references.correction_status,
717 last_update_date = x_last_update_date,
718 last_updated_by = x_last_updated_by,
719 last_update_login = x_last_update_login ,
720 request_id = x_request_id,
721 program_id = x_program_id,
722 program_application_id = x_program_application_id,
723 program_update_date = x_program_update_date
724 WHERE rowid = x_rowid;
725
726 IF (SQL%NOTFOUND) THEN
727 RAISE NO_DATA_FOUND;
728 END IF;
729
730 END update_row;
731
732
733 PROCEDURE add_row (
734 x_rowid IN OUT NOCOPY VARCHAR2,
735 x_isirc_id IN OUT NOCOPY NUMBER,
736 x_isir_id IN NUMBER,
737 x_ci_sequence_number IN NUMBER,
738 x_ci_cal_type IN VARCHAR2,
739 x_sar_field_number IN NUMBER,
740 x_original_value IN VARCHAR2,
741 x_batch_id IN VARCHAR2,
742 x_corrected_value IN VARCHAR2,
743 x_correction_status IN VARCHAR2,
744 x_mode IN VARCHAR2 DEFAULT 'R'
745 ) AS
746 /*
747 || Created By : kpadiyar
748 || Created On : 11-DEC-2000
749 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
750 || Known limitations, enhancements or remarks :
751 || Change History :
752 || Who When What
753 || (reverse chronological order - newest change first)
754 */
755 CURSOR c1 IS
756 SELECT rowid
757 FROM igf_ap_isir_corr_all
758 WHERE isirc_id = x_isirc_id;
759
760 BEGIN
761
762 OPEN c1;
763 FETCH c1 INTO x_rowid;
764 IF (c1%NOTFOUND) THEN
765 CLOSE c1;
766
767 insert_row (
768 x_rowid,
769 x_isirc_id,
770 x_isir_id,
771 x_ci_sequence_number,
772 x_ci_cal_type,
773 x_sar_field_number,
774 x_original_value,
775 x_batch_id,
776 x_corrected_value,
777 x_correction_status,
778 x_mode
779 );
780 RETURN;
781 END IF;
782 CLOSE c1;
783
784 update_row (
785 x_rowid,
786 x_isirc_id,
787 x_isir_id,
788 x_ci_sequence_number,
789 x_ci_cal_type,
790 x_sar_field_number,
791 x_original_value,
792 x_batch_id,
793 x_corrected_value,
794 x_correction_status,
795 x_mode
796 );
797
798 END add_row;
799
800
801 PROCEDURE delete_row (
802 x_rowid IN VARCHAR2
803 ) AS
804 /*
805 || Created By : kpadiyar
806 || Created On : 11-DEC-2000
807 || Purpose : Handles the DELETE DML logic for the table.
808 || Known limitations, enhancements or remarks :
809 || Change History :
810 || Who When What
811 || (reverse chronological order - newest change first)
812 */
813 BEGIN
814
815 before_dml (
816 p_action => 'DELETE',
817 x_rowid => x_rowid
818 );
819
820 DELETE FROM igf_ap_isir_corr_all
821 WHERE rowid = x_rowid;
822
823 IF (SQL%NOTFOUND) THEN
824 RAISE NO_DATA_FOUND;
825 END IF;
826
827 END delete_row;
828
829
830 END igf_ap_isir_corr_pkg;