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