1 PACKAGE BODY igf_ap_td_item_inst_pkg AS
2 /* $Header: IGFAI15B.pls 120.8 2005/09/01 06:30:42 appldev ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGF_AP_TD_ITEM_INST_PKG |
11 | |
12 | NOTES |
13 | |
14 | This package has a flag on the end of some of the procedures called |
15 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
16 | This will control how the who columns are filled in; If you are |
17 | running in runtime mode, they are taken from the profiles, whereas in |
18 | install-time mode they get defaulted with special values to indicate |
19 | that they were inserted by datamerge. |
20 | |
21 | The ADD_ROW routine will see whether a row exists by selecting |
22 | based on the primary key, and updates the row if it exists, |
23 | or inserts the row if it doesn't already exist. |
24 | |
25 | This module is called by AutoInstall (afplss.drv) on install and |
26 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
27 | |
28 | HISTORY |
29 | bkkumar #2858504 Added legacy_ record_flag in the tbh calls |
30 | 04-jun-2003 |
31 *=======================================================================*/
32
33 l_rowid VARCHAR2(25);
34 old_references igf_ap_td_item_inst_all%ROWTYPE;
35 new_references igf_ap_td_item_inst_all%ROWTYPE;
36
37 PROCEDURE set_column_values (
38 p_action IN VARCHAR2,
39 x_rowid IN VARCHAR2 DEFAULT NULL,
40 x_base_id IN NUMBER DEFAULT NULL,
41 x_item_sequence_number IN NUMBER DEFAULT NULL,
42 x_status IN VARCHAR2 DEFAULT NULL,
43 x_status_date IN DATE DEFAULT NULL,
44 x_add_date IN DATE DEFAULT NULL,
45 x_corsp_date IN DATE DEFAULT NULL,
46 x_corsp_count IN NUMBER DEFAULT NULL,
47 x_inactive_flag IN VARCHAR2 DEFAULT NULL,
48 x_freq_attempt IN NUMBER DEFAULT NULL,
49 x_max_attempt IN NUMBER DEFAULT NULL,
50 x_required_for_application IN VARCHAR2 DEFAULT NULL,
51 x_legacy_record_flag IN VARCHAR2,
52 x_creation_date IN DATE DEFAULT NULL,
53 x_created_by IN NUMBER DEFAULT NULL,
54 x_last_update_date IN DATE DEFAULT NULL,
55 x_last_updated_by IN NUMBER DEFAULT NULL,
56 x_last_update_login IN NUMBER DEFAULT NULL,
57 x_clprl_id IN NUMBER DEFAULT NULL
58 ) AS
59 /*
60 || Created By : rasingh
61 || Created On : 13-NOV-2000
62 || Purpose : Initialises the Old and New references for the columns of the table.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 || (reverse chronological order - newest change first)
67 */
68
69 CURSOR cur_old_ref_values IS
70 SELECT *
71 FROM IGF_AP_TD_ITEM_INST_ALL
72 WHERE rowid = x_rowid;
73
74 BEGIN
75
76 l_rowid := x_rowid;
77
78 -- Code for setting the Old and New Reference Values.
79 -- Populate Old Values.
80 OPEN cur_old_ref_values;
81 FETCH cur_old_ref_values INTO old_references;
82 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
83 CLOSE cur_old_ref_values;
84 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
85 igs_ge_msg_stack.add;
86 app_exception.raise_exception;
87 RETURN;
88 END IF;
89 CLOSE cur_old_ref_values;
90
91 -- Populate New Values.
92 new_references.base_id := x_base_id;
93 new_references.item_sequence_number := x_item_sequence_number;
94 new_references.status := x_status;
95 new_references.status_date := x_status_date;
96 new_references.add_date := x_add_date;
97 new_references.corsp_date := x_corsp_date;
98 new_references.corsp_count := x_corsp_count;
99 new_references.inactive_flag := x_inactive_flag;
100 new_references.freq_attempt := x_freq_attempt;
101 new_references.max_attempt := x_max_attempt;
102 new_references.required_for_application := x_required_for_application;
103 new_references.legacy_record_flag := x_legacy_record_flag;
104 new_references.clprl_id := x_clprl_id;
105
106 IF (p_action = 'UPDATE') THEN
107 new_references.creation_date := old_references.creation_date;
108 new_references.created_by := old_references.created_by;
109 ELSE
110 new_references.creation_date := x_creation_date;
111 new_references.created_by := x_created_by;
112 END IF;
113
114 new_references.last_update_date := x_last_update_date;
115 new_references.last_updated_by := x_last_updated_by;
116 new_references.last_update_login := x_last_update_login;
117
118 END set_column_values;
119
120
121 PROCEDURE check_parent_existance AS
122 /*
123 || Created By : rasingh
124 || Created On : 13-NOV-2000
125 || Purpose : Checks for the existance of Parent records.
126 || Known limitations, enhancements or remarks :
127 || Change History :
128 || Who When What
129 || (reverse chronological order - newest change first)
130 */
131 BEGIN
132
133 IF (((old_references.base_id = new_references.base_id)) OR
134 ((new_references.base_id IS NULL))) THEN
135 NULL;
136 ELSIF NOT igf_ap_fa_base_rec_pkg.get_pk_for_validation (
137 new_references.base_id
138 ) THEN
139 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
140 igs_ge_msg_stack.add;
141 app_exception.raise_exception;
142 END IF;
143
144 IF (((old_references.item_sequence_number = new_references.item_sequence_number)) OR
145 ((new_references.item_sequence_number IS NULL))) THEN
146 NULL;
147 ELSIF NOT igf_ap_td_item_mst_pkg.get_pk_for_validation (
148 new_references.item_sequence_number
149 ) THEN
150 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
151 igs_ge_msg_stack.add;
152 app_exception.raise_exception;
153 END IF;
154
155 END check_parent_existance;
156
157
158 FUNCTION get_pk_for_validation (
159 x_base_id IN NUMBER,
160 x_item_sequence_number IN NUMBER
161 ) RETURN BOOLEAN AS
162 /*
163 || Created By : rasingh
164 || Created On : 13-NOV-2000
165 || Purpose : Validates the Primary Key of the table.
166 || Known limitations, enhancements or remarks :
167 || Change History :
168 || Who When What
169 || (reverse chronological order - newest change first)
170 */
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM igf_ap_td_item_inst_all
174 WHERE base_id = x_base_id
175 AND item_sequence_number = x_item_sequence_number
176 FOR UPDATE NOWAIT;
177
178 lv_rowid cur_rowid%RowType;
179
180 BEGIN
181
182 OPEN cur_rowid;
183 FETCH cur_rowid INTO lv_rowid;
184 IF (cur_rowid%FOUND) THEN
185 CLOSE cur_rowid;
186 RETURN(TRUE);
187 ELSE
188 CLOSE cur_rowid;
189 RETURN(FALSE);
190 END IF;
191
192 END get_pk_for_validation;
193
194
195 PROCEDURE check_uniqueness AS
196 /*
197 || Created By : masehgal
198 || Created On : 26-APR-2002
199 || Purpose : Validates the uniqueness for the table.
200 || Known limitations, enhancements or remarks :
201 || Change History :
202 || Who When What
203 || (reverse chronological order - newest change first)
204 */
205 BEGIN
206
207 IF ( get_uk_for_validation (
208 new_references.base_id ,
209 new_references.item_sequence_number
210 )
211 ) THEN
212 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
213 igs_ge_msg_stack.add;
214 app_exception.raise_exception;
215 END IF;
216
217 END check_uniqueness;
218
219
220 FUNCTION get_uk_for_validation (
221 x_base_id IN NUMBER ,
222 x_item_sequence_number IN NUMBER
223 ) RETURN BOOLEAN AS
224 /*
225 || Created By : masehgal
226 || Created On : 26-APR-2002
227 || Purpose : Validates the uniqueness for the table.
228 || Known limitations, enhancements or remarks :
229 || Change History :
230 || Who When What
231 || (reverse chronological order - newest change first)
232 */
233 CURSOR cur_rowid IS
234 SELECT rowid
235 FROM igf_ap_td_item_inst_all
236 WHERE base_id = x_base_id
237 AND item_sequence_number = x_item_sequence_number
238 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
239
240 lv_rowid cur_rowid%RowType;
241
242 BEGIN
243
244 OPEN cur_rowid;
245 FETCH cur_rowid INTO lv_rowid;
246 IF (cur_rowid%FOUND) THEN
247 CLOSE cur_rowid;
248 RETURN (true);
249 ELSE
250 CLOSE cur_rowid;
251 RETURN(FALSE);
252 END IF;
253
254 END get_uk_for_validation ;
255
256
257 PROCEDURE get_fk_igf_ap_fa_base_rec (
258 x_base_id IN NUMBER
259 ) AS
260 /*
261 || Created By : rasingh
262 || Created On : 13-NOV-2000
263 || Purpose : Validates the Foreign Keys for the table.
264 || Known limitations, enhancements or remarks :
265 || Change History :
266 || Who When What
267 || (reverse chronological order - newest change first)
268 */
269 CURSOR cur_rowid IS
270 SELECT rowid
271 FROM igf_ap_td_item_inst_all
272 WHERE ((base_id = x_base_id));
273
274 lv_rowid cur_rowid%RowType;
275
276 BEGIN
277
278 OPEN cur_rowid;
279 FETCH cur_rowid INTO lv_rowid;
280 IF (cur_rowid%FOUND) THEN
281 CLOSE cur_rowid;
282 fnd_message.set_name ('IGF', 'IGF_AP_TDII_FA_DETAIL_FK');
283 igs_ge_msg_stack.add;
284 app_exception.raise_exception;
285 RETURN;
286 END IF;
287 CLOSE cur_rowid;
288
289 END get_fk_igf_ap_fa_base_rec;
290
291 PROCEDURE get_fk_igf_ap_td_item_mst (
292 x_todo_number IN NUMBER
293 ) AS
294 /*
295 || Created By : rasingh
296 || Created On : 13-NOV-2000
297 || Purpose : Validates the Foreign Keys for the table.
298 || Known limitations, enhancements or remarks :
299 || Change History :
300 || Who When What
301 || (reverse chronological order - newest change first)
302 */
303 CURSOR cur_rowid IS
304 SELECT rowid
305 FROM igf_ap_td_item_inst_all
306 WHERE ((item_sequence_number = x_todo_number));
307
308 lv_rowid cur_rowid%RowType;
309
310 BEGIN
311
312 OPEN cur_rowid;
313 FETCH cur_rowid INTO lv_rowid;
314 IF (cur_rowid%FOUND) THEN
315 CLOSE cur_rowid;
316 fnd_message.set_name ('IGF', 'IGF_AP_TDII_TDI_FK');
317 igs_ge_msg_stack.add;
318 app_exception.raise_exception;
319 RETURN;
320 END IF;
321 CLOSE cur_rowid;
322
323 END get_fk_igf_ap_td_item_mst;
324
325
326 PROCEDURE before_dml (
327 p_action IN VARCHAR2,
328 x_rowid IN VARCHAR2 DEFAULT NULL,
329 x_base_id IN NUMBER DEFAULT NULL,
330 x_item_sequence_number IN NUMBER DEFAULT NULL,
331 x_status IN VARCHAR2 DEFAULT NULL,
332 x_status_date IN DATE DEFAULT NULL,
333 x_add_date IN DATE DEFAULT NULL,
334 x_corsp_date IN DATE DEFAULT NULL,
335 x_corsp_count IN NUMBER DEFAULT NULL,
336 x_inactive_flag IN VARCHAR2 DEFAULT NULL,
337 x_freq_attempt IN NUMBER DEFAULT NULL,
338 x_max_attempt IN NUMBER DEFAULT NULL,
339 x_required_for_application IN VARCHAR2 DEFAULT NULL,
340 x_legacy_record_flag IN VARCHAR2 ,
341 x_creation_date IN DATE DEFAULT NULL,
342 x_created_by IN NUMBER DEFAULT NULL,
343 x_last_update_date IN DATE DEFAULT NULL,
344 x_last_updated_by IN NUMBER DEFAULT NULL,
345 x_last_update_login IN NUMBER DEFAULT NULL,
346 x_clprl_id IN NUMBER DEFAULT NULL
347 ) AS
348 /*
349 || Created By : rasingh
350 || Created On : 13-NOV-2000
351 || Purpose : Initialises the columns, Checks Constraints, Calls the
352 || Trigger Handlers for the table, before any DML operation.
353 || Known limitations, enhancements or remarks :
354 || Change History :
355 || Who When What
356 || masehgal 26-Apr-2002 # 2303509 Added call to check Uniqueness
357 || (reverse chronological order - newest change first)
358 */
359 BEGIN
360
361 set_column_values (
362 p_action,
363 x_rowid,
364 x_base_id,
365 x_item_sequence_number,
366 x_status,
367 x_status_date,
368 x_add_date,
369 x_corsp_date,
370 x_corsp_count,
371 x_inactive_flag,
372 x_freq_attempt,
373 x_max_attempt,
374 x_required_for_application,
375 x_legacy_record_flag,
376 x_creation_date,
377 x_created_by,
378 x_last_update_date,
379 x_last_updated_by,
380 x_last_update_login,
381 x_clprl_id
382 );
383
384 IF (p_action = 'INSERT') THEN
385 -- Call all the procedures related to Before Insert.
386 IF ( get_pk_for_validation(
387 new_references.base_id,
388 new_references.item_sequence_number
389 )
390 ) THEN
391 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
392 igs_ge_msg_stack.add;
393 app_exception.raise_exception;
394 END IF;
395 check_parent_existance;
396
397 -- Added call to check uniqueness
398 check_uniqueness ;
399
400 ELSIF (p_action = 'UPDATE') THEN
401 -- Call all the procedures related to Before Update.
402 check_parent_existance;
403
404 ELSIF (p_action = 'VALIDATE_INSERT') THEN
405 -- Call all the procedures related to Before Insert.
406 IF ( get_pk_for_validation (
407 new_references.base_id,
408 new_references.item_sequence_number
409 )
410 ) THEN
411 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
412 igs_ge_msg_stack.add;
413 app_exception.raise_exception;
414 END IF;
415
416 -- Added call to check uniqueness
417 check_uniqueness ;
418
419 END IF;
420
421 END before_dml;
422
423 PROCEDURE after_dml(
424 p_action IN VARCHAR2
425 ) AS
426 /*
427 || Created By :
428 || Created On :
429 || Purpose : Initialises the columns, Checks Constraints, Calls the
430 || Trigger Handlers for the table, before any DML operation.
431 || Known limitations, enhancements or remarks :
432 || Change History :
433 || Who When What
434 ||
435 || (reverse chronological order - newest change first)
436 */
437 CURSOR c_inst_appl(
438 cp_base_id NUMBER,
439 cp_item_sequence_number NUMBER
440 ) IS
441 SELECT appl_setup.*
442 FROM igf_ap_appl_setup_all appl_setup,
443 igf_ap_td_item_mst_all td_mst,
444 igf_ap_td_item_inst_all td_inst
445 WHERE td_mst.ci_cal_type = appl_setup.ci_cal_type
446 AND td_mst.ci_sequence_number = appl_setup.ci_sequence_number
447 AND td_mst.application_code = appl_setup.application_code
448 AND td_mst.todo_number = td_inst.item_sequence_number
449 AND td_inst.base_id = cp_base_id
450 AND td_inst.item_sequence_number = cp_item_sequence_number
451 AND appl_setup.question_id NOT IN (SELECT question_id
452 FROM igf_ap_st_inst_appl_all
453 WHERE base_id = td_inst.base_id);
454
455 l_inst_appl c_inst_appl%ROWTYPE;
456
457 lv_rowid VARCHAR2(25) := NULL;
458 lv_inst_app_id NUMBER := NULL;
459
460 lv_base_id NUMBER;
461 lv_item_sequence_number NUMBER;
462
463 CURSOR c_appl_status(
464 cp_base_id NUMBER,
465 cp_application_code VARCHAR2
466 ) IS
467 SELECT appl.rowid row_id,
468 appl.*
469 FROM igf_ap_appl_status_all appl
470 WHERE appl.base_id = cp_base_id
471 AND appl.application_code = cp_application_code;
472 l_appl_status c_appl_status%ROWTYPE;
473
474 -- Get application code
475 CURSOR c_appl_code(
476 cp_item_sequence_number NUMBER
477 ) IS
478 SELECT application_code,
479 system_todo_type_code
480 FROM igf_ap_td_item_mst_all
481 WHERE todo_number = cp_item_sequence_number;
482 l_appl_code c_appl_code%ROWTYPE;
483
484 BEGIN
485
486 OPEN c_appl_code(new_references.item_sequence_number);
487 FETCH c_appl_code INTO l_appl_code;
488 CLOSE c_appl_code;
489
490 IF l_appl_code.system_todo_type_code = 'INSTAPP' THEN
491 IF p_action = 'INSERT' THEN
492 /*
493 For an institutional application to do item, on insert,
494 create all the questions in the IGF_AP_ST_INST_APPL_ALL,
495 and create a record for the status also.
496 */
497 lv_base_id := new_references.base_id;
498 lv_item_sequence_number := new_references.item_sequence_number;
499
500 FOR l_inst_appl IN c_inst_appl(lv_base_id, lv_item_sequence_number) LOOP
501 igf_ap_st_inst_appl_pkg.insert_row(
502 x_rowid => lv_rowid,
503 x_inst_app_id => lv_inst_app_id,
504 x_base_id => lv_base_id,
505 x_question_id => l_inst_appl.question_id,
506 x_question_value => NULL,
507 x_application_code => l_inst_appl.application_code,
508 x_mode => 'R'
509 );
510 END LOOP;
511
512 lv_rowid := NULL;
513
514 IF l_appl_code.application_code IS NOT NULL THEN
515 igf_ap_appl_status_pkg.insert_row(
516 x_rowid => lv_rowid,
517 x_base_id => lv_base_id,
518 x_application_code => l_appl_code.application_code,
519 x_application_status_code => new_references.status,
520 x_mode => 'R'
521 );
522 END IF;
523 END IF;
524
525 IF p_action = 'UPDATE' THEN
526 IF NVL(old_references.status,'*') <> NVL(new_references.status,'**') THEN
527 /*
528 On update of a institutional application to do item, update the status of the to do item also
529 */
530
531 OPEN c_appl_status(new_references.base_id,l_appl_code.application_code);
532 FETCH c_appl_status INTO l_appl_status;
533 CLOSE c_appl_status;
534
535 igf_ap_appl_status_pkg.update_row(
536 x_rowid => l_appl_status.row_id,
537 x_base_id => l_appl_status.base_id,
538 x_application_code => l_appl_status.application_code,
539 x_application_status_code => new_references.status,
540 x_mode => 'R'
541 );
542 END IF;
543 IF old_references.inactive_flag IS NOT NULL AND
544 new_references.inactive_flag IS NOT NULL AND
545 old_references.inactive_flag = 'Y' AND
546 new_references.inactive_flag = 'N' THEN
547 /*
548 On updating a to do from inactive to active, create the questions once again
549 */
550 lv_base_id := new_references.base_id;
551 lv_item_sequence_number := new_references.item_sequence_number;
552
553 FOR l_inst_appl IN c_inst_appl(lv_base_id, lv_item_sequence_number) LOOP
554 igf_ap_st_inst_appl_pkg.insert_row(
555 x_rowid => lv_rowid,
556 x_inst_app_id => lv_inst_app_id,
557 x_base_id => lv_base_id,
558 x_question_id => l_inst_appl.question_id,
559 x_question_value => NULL,
560 x_application_code => l_inst_appl.application_code,
561 x_mode => 'R'
562 );
563 END LOOP;
564 lv_rowid := NULL;
565
566 IF l_appl_code.application_code IS NOT NULL THEN
567 igf_ap_appl_status_pkg.add_row(
568 x_rowid => lv_rowid,
569 x_base_id => lv_base_id,
570 x_application_code => l_appl_code.application_code,
571 x_application_status_code => new_references.status,
572 x_mode => 'R'
573 );
574 END IF;
575 END IF;
576 END IF;
577 END IF;
578 END after_dml;
579
580 PROCEDURE insert_row (
581 x_rowid IN OUT NOCOPY VARCHAR2,
582 x_base_id IN NUMBER,
583 x_item_sequence_number IN NUMBER,
584 x_status IN VARCHAR2,
585 x_status_date IN DATE,
586 x_add_date IN DATE,
587 x_corsp_date IN DATE,
588 x_corsp_count IN NUMBER,
589 x_inactive_flag IN VARCHAR2,
590 x_freq_attempt IN NUMBER DEFAULT NULL,
591 x_max_attempt IN NUMBER DEFAULT NULL,
592 x_required_for_application IN VARCHAR2 DEFAULT NULL,
593 x_mode IN VARCHAR2 DEFAULT 'R',
594 x_legacy_record_flag IN VARCHAR2,
595 x_clprl_id IN NUMBER
596 ) AS
597 /*
598 || Created By : rasingh
599 || Created On : 13-NOV-2000
600 || Purpose : Handles the INSERT DML logic for the table.
601 || Known limitations, enhancements or remarks :
602 || Change History :
603 || Who When What
604 || (reverse chronological order - newest change first)
605 */
606 CURSOR c IS
607 SELECT rowid
608 FROM igf_ap_td_item_inst_all
609 WHERE base_id = x_base_id
610 AND item_sequence_number = x_item_sequence_number;
611
612 x_last_update_date DATE;
613 x_last_updated_by NUMBER;
614 x_last_update_login NUMBER;
615
616 l_org_id igf_ap_td_item_inst_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
617
618 BEGIN
619
620 x_last_update_date := SYSDATE;
621 IF (x_mode = 'I') THEN
622 x_last_updated_by := 1;
623 x_last_update_login := 0;
624 ELSIF (x_mode = 'R') THEN
625 x_last_updated_by := fnd_global.user_id;
626 IF (x_last_updated_by IS NULL) THEN
627 x_last_updated_by := -1;
628 END IF;
629 x_last_update_login := fnd_global.login_id;
630 IF (x_last_update_login IS NULL) THEN
631 x_last_update_login := -1;
632 END IF;
633 ELSE
634 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
635 igs_ge_msg_stack.add;
636 app_exception.raise_exception;
637 END IF;
638
639 before_dml(
640 p_action => 'INSERT',
641 x_rowid => x_rowid,
642 x_base_id => x_base_id,
643 x_item_sequence_number => x_item_sequence_number,
644 x_status => x_status,
645 x_status_date => x_status_date,
646 x_add_date => x_add_date,
647 x_corsp_date => x_corsp_date,
648 x_corsp_count => x_corsp_count,
649 x_inactive_flag => x_inactive_flag,
650 x_freq_attempt => x_freq_attempt,
651 x_max_attempt => x_max_attempt,
652 x_required_for_application => x_required_for_application,
653 x_legacy_record_flag => x_legacy_record_flag,
654 x_creation_date => x_last_update_date,
655 x_created_by => x_last_updated_by,
656 x_last_update_date => x_last_update_date,
657 x_last_updated_by => x_last_updated_by,
658 x_last_update_login => x_last_update_login,
659 x_clprl_id => x_clprl_id
660 );
661
662 INSERT INTO igf_ap_td_item_inst_all (
663 base_id,
664 item_sequence_number,
665 status,
666 status_date,
667 add_date,
668 corsp_date,
669 corsp_count,
670 inactive_flag,
671 freq_attempt,
672 max_attempt,
673 required_for_application,
674 legacy_record_flag,
675 creation_date,
676 created_by,
677 last_update_date,
678 last_updated_by,
679 last_update_login,
680 org_id,
681 clprl_id
682 ) VALUES (
683 new_references.base_id,
684 new_references.item_sequence_number,
685 new_references.status,
686 new_references.status_date,
687 new_references.add_date,
688 new_references.corsp_date,
689 new_references.corsp_count,
690 new_references.inactive_flag,
691 new_references.freq_attempt,
692 new_references.max_attempt,
693 new_references.required_for_application,
694 new_references.legacy_record_flag,
695 x_last_update_date,
696 x_last_updated_by,
697 x_last_update_date,
698 x_last_updated_by,
699 x_last_update_login,
700 l_org_id,
701 new_references.clprl_id
702 );
703
704 OPEN c;
705 FETCH c INTO x_rowid;
706 IF (c%NOTFOUND) THEN
707 CLOSE c;
708 RAISE NO_DATA_FOUND;
709 END IF;
710 CLOSE c;
711
712 after_dml(p_action => 'INSERT');
713
714 END insert_row;
715
716
717 PROCEDURE lock_row (
718 x_rowid IN VARCHAR2,
719 x_base_id IN NUMBER,
720 x_item_sequence_number IN NUMBER,
721 x_status IN VARCHAR2,
722 x_status_date IN DATE,
723 x_add_date IN DATE,
724 x_corsp_date IN DATE,
725 x_corsp_count IN NUMBER,
726 x_inactive_flag IN VARCHAR2,
727 x_freq_attempt IN NUMBER DEFAULT NULL,
728 x_max_attempt IN NUMBER DEFAULT NULL,
729 x_required_for_application IN VARCHAR2 DEFAULT NULL,
730 x_legacy_record_flag IN VARCHAR2,
731 x_clprl_id IN NUMBER
732 ) AS
733 /*
734 || Created By : rasingh
735 || Created On : 13-NOV-2000
736 || Purpose : Handles the LOCK mechanism for the table.
737 || Known limitations, enhancements or remarks :
738 || Change History :
739 || Who When What
740 || (reverse chronological order - newest change first)
741 */
742 CURSOR c1 IS
743 SELECT
744 status,
745 status_date,
746 add_date,
747 corsp_date,
748 corsp_count,
749 inactive_flag,
750 freq_attempt,
751 max_attempt,
752 required_for_application,
753 org_id,
754 legacy_record_flag,
755 clprl_id
756 FROM igf_ap_td_item_inst_all
757 WHERE rowid = x_rowid
758 FOR UPDATE NOWAIT;
759
760 tlinfo c1%ROWTYPE;
761
762
763 BEGIN
764
765 OPEN c1;
766 FETCH c1 INTO tlinfo;
767 IF (c1%notfound) THEN
768 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
769 igs_ge_msg_stack.add;
770 CLOSE c1;
771 app_exception.raise_exception;
772 RETURN;
773 END IF;
774 CLOSE c1;
775
776 IF (
777 ((tlinfo.status = x_status) OR ((tlinfo.status IS NULL) AND (X_status IS NULL)))
778 AND ((tlinfo.status_date = x_status_date) OR ((tlinfo.status_date IS NULL) AND (X_status_date IS NULL)))
779 AND ((tlinfo.add_date = x_add_date) OR ((tlinfo.add_date IS NULL) AND (X_add_date IS NULL)))
780 AND ((tlinfo.corsp_date = x_corsp_date) OR ((tlinfo.corsp_date IS NULL) AND (X_corsp_date IS NULL)))
781 AND ((tlinfo.corsp_count = x_corsp_count) OR ((tlinfo.corsp_count IS NULL) AND (X_corsp_count IS NULL)))
782 AND ((tlinfo.inactive_flag = x_inactive_flag) OR ((tlinfo.inactive_flag IS NULL) AND (X_inactive_flag IS NULL)))
783 AND ((tlinfo.freq_attempt = x_freq_attempt) OR ((tlinfo.freq_attempt IS NULL) AND (x_freq_attempt IS NULL)))
784 AND ((tlinfo.max_attempt = x_max_attempt) OR ((tlinfo.max_attempt IS NULL) AND (X_max_attempt IS NULL)))
785 AND ((tlinfo.required_for_application = x_required_for_application) OR ((tlinfo.required_for_application IS NULL) AND (x_required_for_application IS NULL)))
786 AND ((tlinfo.legacy_record_flag = x_legacy_record_flag) OR ((tlinfo.legacy_record_flag IS NULL) AND (x_legacy_record_flag IS NULL)))
787 AND ((tlinfo.clprl_id = x_clprl_id) OR ((tlinfo.clprl_id IS NULL) AND (x_clprl_id IS NULL)))
788 ) THEN
789 NULL;
790 ELSE
791 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
792 igs_ge_msg_stack.add;
793 app_exception.raise_exception;
794 END IF;
795
796 RETURN;
797
798 END lock_row;
799
800
801 PROCEDURE update_row (
802 x_rowid IN VARCHAR2,
803 x_base_id IN NUMBER,
804 x_item_sequence_number IN NUMBER,
805 x_status IN VARCHAR2,
806 x_status_date IN DATE,
807 x_add_date IN DATE,
808 x_corsp_date IN DATE,
809 x_corsp_count IN NUMBER,
810 x_inactive_flag IN VARCHAR2,
811 x_freq_attempt IN NUMBER DEFAULT NULL,
812 x_max_attempt IN NUMBER DEFAULT NULL,
813 x_required_for_application IN VARCHAR2 DEFAULT NULL,
814 x_mode IN VARCHAR2 DEFAULT 'R' ,
815 x_legacy_record_flag IN VARCHAR2,
816 x_clprl_id IN NUMBER
817 ) AS
818 /*
819 || Created By : rasingh
820 || Created On : 13-NOV-2000
821 || Purpose : Handles the UPDATE DML logic for the table.
822 || Known limitations, enhancements or remarks :
823 || Change History :
824 || Who When What
825 || (reverse chronological order - newest change first)
826 */
827 x_last_update_date DATE ;
828 x_last_updated_by NUMBER;
829 x_last_update_login NUMBER;
830
831
832 BEGIN
833
834 x_last_update_date := SYSDATE;
835 IF (X_MODE = 'I') THEN
836 x_last_updated_by := 1;
837 x_last_update_login := 0;
838 ELSIF (x_mode = 'R') THEN
839 x_last_updated_by := fnd_global.user_id;
840 IF x_last_updated_by IS NULL THEN
841 x_last_updated_by := -1;
842 END IF;
843 x_last_update_login := fnd_global.login_id;
844 IF (x_last_update_login IS NULL) THEN
845 x_last_update_login := -1;
846 END IF;
847 ELSE
848 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
849 igs_ge_msg_stack.add;
850 app_exception.raise_exception;
851 END IF;
852
853 before_dml(
854 p_action => 'UPDATE',
855 x_rowid => x_rowid,
856 x_base_id => x_base_id,
857 x_item_sequence_number => x_item_sequence_number,
858 x_status => x_status,
859 x_status_date => x_status_date,
860 x_add_date => x_add_date,
861 x_corsp_date => x_corsp_date,
862 x_corsp_count => x_corsp_count,
863 x_inactive_flag => x_inactive_flag,
864 x_freq_attempt => x_freq_attempt,
865 x_max_attempt => x_max_attempt,
866 x_required_for_application => x_required_for_application,
867 x_legacy_record_flag => x_legacy_record_flag,
868 x_creation_date => x_last_update_date,
869 x_created_by => x_last_updated_by,
870 x_last_update_date => x_last_update_date,
871 x_last_updated_by => x_last_updated_by,
872 x_last_update_login => x_last_update_login,
873 x_clprl_id => x_clprl_id
874 );
875
876 UPDATE igf_ap_td_item_inst_all
877 SET
878 status = new_references.status,
879 status_date = new_references.status_date,
880 add_date = new_references.add_date,
881 corsp_date = new_references.corsp_date,
882 corsp_count = new_references.corsp_count,
883 inactive_flag = new_references.inactive_flag,
884 freq_attempt = new_references.freq_attempt,
885 max_attempt = new_references.max_attempt,
886 required_for_application = new_references.required_for_application,
887 legacy_record_flag = new_references.legacy_record_flag,
888 last_update_date = x_last_update_date,
889 last_updated_by = x_last_updated_by,
890 last_update_login = x_last_update_login,
891 clprl_id = new_references.clprl_id
892 WHERE rowid = x_rowid;
893
894 IF (SQL%NOTFOUND) THEN
895 RAISE NO_DATA_FOUND;
896 END IF;
897
898 after_dml(p_action => 'UPDATE');
899
900 END update_row;
901
902
903 PROCEDURE add_row (
904 x_rowid IN OUT NOCOPY VARCHAR2,
905 x_base_id IN NUMBER,
906 x_item_sequence_number IN NUMBER,
907 x_status IN VARCHAR2,
908 x_status_date IN DATE,
909 x_add_date IN DATE,
910 x_corsp_date IN DATE,
911 x_corsp_count IN NUMBER,
912 x_inactive_flag IN VARCHAR2,
913 x_freq_attempt IN NUMBER DEFAULT NULL,
914 x_max_attempt IN NUMBER DEFAULT NULL,
915 x_required_for_application IN VARCHAR2 DEFAULT NULL,
916 x_mode IN VARCHAR2 DEFAULT 'R',
917 x_legacy_record_flag IN VARCHAR2,
918 x_clprl_id IN NUMBER
919 ) AS
920 /*
921 || Created By : rasingh
922 || Created On : 13-NOV-2000
923 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
924 || Known limitations, enhancements or remarks :
925 || Change History :
926 || Who When What
927 || (reverse chronological order - newest change first)
928 */
929 CURSOR c1 IS
930 SELECT rowid
931 FROM igf_ap_td_item_inst_all
932 WHERE base_id = x_base_id
933 AND item_sequence_number = x_item_sequence_number;
934
935 BEGIN
936
937 OPEN c1;
938 FETCH c1 INTO x_rowid;
939 IF (c1%NOTFOUND) THEN
940 CLOSE c1;
941
942 insert_row (
943 x_rowid,
944 x_base_id,
945 x_item_sequence_number,
946 x_status,
947 x_status_date,
948 x_add_date,
949 x_corsp_date,
950 x_corsp_count,
951 x_inactive_flag,
952 x_freq_attempt,
953 x_max_attempt,
954 x_required_for_application,
955 x_mode,
956 x_legacy_record_flag,
957 x_clprl_id
958 );
959 RETURN;
960 END IF;
961 CLOSE c1;
962
963 update_row (
964 x_rowid,
965 x_base_id,
966 x_item_sequence_number,
967 x_status,
968 x_status_date,
969 x_add_date,
970 x_corsp_date,
971 x_corsp_count,
972 x_inactive_flag,
973 x_freq_attempt,
974 x_max_attempt,
975 x_required_for_application,
976 x_mode,
977 x_legacy_record_flag,
978 x_clprl_id
979 );
980
981 END add_row;
982
983
984 PROCEDURE delete_row (
985 x_rowid IN VARCHAR2
986 ) AS
987 /*
988 || Created By : rasingh
989 || Created On : 13-NOV-2000
990 || Purpose : Handles the DELETE DML logic for the table.
991 || Known limitations, enhancements or remarks :
992 || Change History :
993 || Who When What
994 || (reverse chronological order - newest change first)
995 */
996 BEGIN
997
998 before_dml (
999 p_action => 'DELETE',
1000 x_rowid => x_rowid
1001 );
1002
1003 DELETE FROM igf_ap_td_item_inst_all
1004 WHERE rowid = x_rowid;
1005
1006 IF (SQL%NOTFOUND) THEN
1007 RAISE NO_DATA_FOUND;
1008 END IF;
1009
1010 END delete_row;
1011
1012
1013 END igf_ap_td_item_inst_pkg;
[Home] [Help]