[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_EV_FORM_STAT_PKG
Source
1 PACKAGE BODY igs_pe_ev_form_stat_pkg AS
2 /* $Header: IGSNIA6B.pls 120.2 2006/02/17 06:57:13 gmaheswa ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_ev_form_stat%ROWTYPE;
6 new_references igs_pe_ev_form_stat%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ev_form_stat_id IN NUMBER,
12 x_ev_form_id IN NUMBER,
13 x_action_date IN DATE,
14 x_action_type IN VARCHAR2,
15 x_prgm_start_date IN DATE,
16 x_prgm_end_date IN DATE,
17 x_remarks IN VARCHAR2,
18 x_termination_reason IN VARCHAR2,
19 x_end_program_reason IN VARCHAR2,
20 x_creation_date IN DATE,
21 x_created_by IN NUMBER,
22 x_last_update_date IN DATE,
23 x_last_updated_by IN NUMBER,
24 x_last_update_login IN NUMBER
25 ) AS
26 /*
27 || Created By : [email protected]
28 || Created On : 28-NOV-2002
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 igs_pe_ev_form_stat
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.ev_form_stat_id := x_ev_form_stat_id;
60 new_references.ev_form_id := x_ev_form_id;
61 new_references.action_date := x_action_date;
62 new_references.action_type := x_action_type;
63 new_references.prgm_start_date := x_prgm_start_date;
64 new_references.prgm_end_date := x_prgm_end_date;
65 new_references.remarks := x_remarks;
66 new_references.termination_reason := x_termination_reason;
67 new_references.end_program_reason := x_end_program_reason;
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 afterrowinsertupdate(p_insert BOOLEAN ,p_update BOOLEAN) AS
85
86 CURSOR person_type(l_person_id igs_pe_ev_form.person_id%TYPE) IS
87 SELECT typ.rowid,typ.* FROM
88 igs_pe_typ_instances_all typ , igs_pe_person_types per_typ
89 WHERE
90 typ.person_id = l_person_id AND
91 per_typ.system_type = 'EXCHG_VISITOR' AND
92 typ.person_type_code = per_typ.person_type_code AND
93 typ.end_date IS NULL ;
94
95 l_person_type person_type%ROWTYPE;
96 l_end_date DATE;
97 l_end_method igs_pe_typ_instances_all.end_method%TYPE;
98 lv_userid NUMBER := NULL;
99
100 BEGIN
101 IF p_insert = TRUE THEN
102 IF new_references.action_type IN ('ED','TR') THEN
103 DECLARE
104 CURSOR ev_form IS
105 SELECT a.rowid,a.* FROM IGS_PE_EV_FORM a
106 WHERE a.ev_form_id = new_references.ev_form_id;
107 l_ev_form ev_form%ROWTYPE;
108 l_form_status igs_pe_ev_form.form_status%TYPE;
109 BEGIN
110 OPEN ev_form;
111 FETCH ev_form INTO l_ev_form;
112 CLOSE ev_form;
113 IF new_references.action_type = 'ED' THEN
114 l_form_status := 'C';
115 ELSIF new_references.action_type = 'TR' THEN
116 l_form_status := 'T';
117 END IF;
118 igs_pe_ev_form_pkg.update_row (
119 x_rowid => l_ev_form.rowid ,
120 x_ev_form_id => l_ev_form.ev_form_id ,
121 x_person_id => l_ev_form.person_id ,
122 x_print_form => l_ev_form.print_form ,
123 x_form_effective_date => l_ev_form.form_effective_date ,
124 x_form_status => l_form_status ,
125 x_create_reason => l_ev_form.create_reason ,
126 x_is_valid => l_ev_form.is_valid ,
127 x_prgm_sponsor_amt => l_ev_form.prgm_sponsor_amt ,
128 x_govt_org1_amt => l_ev_form.govt_org1_amt ,
129 x_govt_org1_code => l_ev_form.govt_org1_code ,
130 x_govt_org2_amt => l_ev_form.govt_org2_amt ,
131 x_govt_org2_code => l_ev_form.govt_org2_code ,
132 x_intl_org1_amt => l_ev_form.intl_org1_amt ,
133 x_intl_org1_code => l_ev_form.intl_org1_code ,
134 x_intl_org2_amt => l_ev_form.intl_org2_amt ,
135 x_intl_org2_code => l_ev_form.intl_org2_code ,
136 x_ev_govt_amt => l_ev_form.ev_govt_amt ,
137 x_bi_natnl_com_amt => l_ev_form.bi_natnl_com_amt ,
138 x_other_govt_amt => l_ev_form.other_govt_amt ,
139 x_personal_funds_amt => l_ev_form.personal_funds_amt ,
140 x_ev_form_number => l_ev_form.ev_form_number ,
141 x_prgm_start_date => l_ev_form.prgm_start_date ,
142 x_prgm_end_date => l_ev_form.prgm_end_date ,
143 x_last_reprint_date => l_ev_form.last_reprint_date ,
144 x_reprint_reason => l_ev_form.reprint_reason ,
145 x_reprint_remarks => l_ev_form.reprint_remarks ,
146 x_position_code => l_ev_form.position_code ,
147 x_position_remarks => l_ev_form.position_remarks ,
148 x_subject_field_code => l_ev_form.subject_field_code ,
149 x_subject_field_remarks => l_ev_form.subject_field_remarks ,
150 x_matriculation => l_ev_form.matriculation ,
151 x_remarks => l_ev_form.remarks ,
152 x_mode => 'R',
153 x_category_code => l_ev_form.category_code,
154 x_init_prgm_start_date => l_ev_form.init_prgm_start_date,
155 x_govt_org1_othr_name => l_ev_form.govt_org1_othr_name,
156 x_govt_org2_othr_name => l_ev_form.govt_org2_othr_name,
157 x_intl_org1_othr_name => l_ev_form.intl_org1_othr_name,
158 x_intl_org2_othr_name => l_ev_form.intl_org2_othr_name,
159 x_no_show_flag => l_ev_form.no_show_flag,
160 x_other_govt_name => l_ev_form.other_govt_name,
161 x_sevis_school_id => l_ev_form.SEVIS_SCHOOL_IDENTIFIER
162 );
163
164 OPEN person_type(l_ev_form.person_id);
165 FETCH person_type INTO l_person_type;
166 CLOSE person_type;
167
168 l_end_date := new_references.action_date;
169 l_end_method := 'END_EXCHG_VISITOR';
170 lv_userid := fnd_global.user_id;
171
172 igs_pe_typ_instances_pkg.UPDATE_ROW
173 (
174 X_ROWID => l_person_type.rowid,
175 X_PERSON_ID => l_person_type.person_id,
176 X_COURSE_CD => l_person_type.course_cd,
177 X_TYPE_INSTANCE_ID => l_person_type.type_instance_id,
178 X_PERSON_TYPE_CODE => l_person_type.person_type_code,
179 X_CC_VERSION_NUMBER => l_person_type.cc_version_number,
180 X_FUNNEL_STATUS => l_person_type.funnel_status,
181 X_ADMISSION_APPL_NUMBER => l_person_type.admission_appl_number,
182 X_NOMINATED_COURSE_CD => l_person_type.nominated_course_cd,
183 X_NCC_VERSION_NUMBER => l_person_type.ncc_version_number,
184 X_SEQUENCE_NUMBER => l_person_type.sequence_number,
185 X_START_DATE => l_ev_form.form_effective_date,
186 X_END_DATE => l_end_date ,
187 X_CREATE_METHOD => l_person_type.create_method,
188 X_ENDED_BY => lv_userid,
189 X_END_METHOD => l_end_method,
190 X_EMPLMNT_CATEGORY_CODE => l_person_type.emplmnt_category_code
191 );
192
193 END ;
194 END IF;
195
196 ELSIF p_update = TRUE THEN
197 null;
198 END IF;
199 END afterrowinsertupdate;
200
201
202 PROCEDURE check_uniqueness AS
203 /*
204 || Created By : [email protected]
205 || Created On : 28-NOV-2002
206 || Purpose : Handles the Unique Constraint logic defined for the columns.
207 || Known limitations, enhancements or remarks :
208 || Change History :
209 || Who When What
210 || (reverse chronological order - newest change first)
211 */
212 BEGIN
213
214 IF ( get_uk_for_validation (
215 new_references.ev_form_id,
216 new_references.action_date,
217 new_references.action_type
218 )
219 ) THEN
220 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
221 igs_ge_msg_stack.add;
222 app_exception.raise_exception;
223 END IF;
224
225 END check_uniqueness;
226
227
228 PROCEDURE check_parent_existance AS
229 /*
230 || Created By : [email protected]
231 || Created On : 28-NOV-2002
232 || Purpose : Checks for the existance of Parent records.
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 BEGIN
239
240 IF (((old_references.ev_form_id = new_references.ev_form_id)) OR
241 ((new_references.ev_form_id IS NULL))) THEN
242 NULL;
243 ELSIF NOT igs_pe_ev_form_pkg.get_pk_for_validation (
244 new_references.ev_form_id
245 ) THEN
246 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249 END IF;
250
251 END check_parent_existance;
252
253
254 FUNCTION get_pk_for_validation (
255 x_ev_form_stat_id IN NUMBER
256 ) RETURN BOOLEAN AS
257 /*
258 || Created By : [email protected]
259 || Created On : 28-NOV-2002
260 || Purpose : Validates the Primary Key of the table.
261 || Known limitations, enhancements or remarks :
262 || Change History :
263 || Who When What
264 || (reverse chronological order - newest change first)
265 */
266 CURSOR cur_rowid IS
267 SELECT rowid
268 FROM igs_pe_ev_form_stat
269 WHERE ev_form_stat_id = x_ev_form_stat_id
270 FOR UPDATE NOWAIT;
271
272 lv_rowid cur_rowid%RowType;
273
274 BEGIN
275
276 OPEN cur_rowid;
277 FETCH cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 CLOSE cur_rowid;
280 RETURN(TRUE);
281 ELSE
282 CLOSE cur_rowid;
283 RETURN(FALSE);
284 END IF;
285
286 END get_pk_for_validation;
287
288
289 FUNCTION get_uk_for_validation (
290 x_ev_form_id IN NUMBER,
291 x_action_date IN DATE,
292 x_action_type IN VARCHAR2
293 ) RETURN BOOLEAN AS
294 /*
295 || Created By : [email protected]
296 || Created On : 28-NOV-2002
297 || Purpose : Validates the Unique Keys of 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 igs_pe_ev_form_stat
306 WHERE ev_form_id = x_ev_form_id
307 AND action_date = x_action_date
308 AND action_type = x_action_type
309 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
310
311 lv_rowid cur_rowid%RowType;
312
313 BEGIN
314
315 OPEN cur_rowid;
316 FETCH cur_rowid INTO lv_rowid;
317 IF (cur_rowid%FOUND) THEN
318 CLOSE cur_rowid;
319 RETURN (true);
320 ELSE
321 CLOSE cur_rowid;
322 RETURN(FALSE);
323 END IF;
324
328 PROCEDURE get_fk_igs_pe_ev_form (
325 END get_uk_for_validation ;
326
327
329 x_ev_form_id IN NUMBER
330 ) AS
331 /*
332 || Created By : [email protected]
333 || Created On : 28-NOV-2002
334 || Purpose : Validates the Foreign Keys for the table.
335 || Known limitations, enhancements or remarks :
336 || Change History :
337 || Who When What
338 || (reverse chronological order - newest change first)
339 */
340 CURSOR cur_rowid IS
341 SELECT rowid
342 FROM igs_pe_ev_form_stat
343 WHERE ((ev_form_id = x_ev_form_id));
344
345 lv_rowid cur_rowid%RowType;
346
347 BEGIN
348
349 OPEN cur_rowid;
350 FETCH cur_rowid INTO lv_rowid;
351 IF (cur_rowid%FOUND) THEN
352 CLOSE cur_rowid;
353 fnd_message.set_name ('IGS', 'IGS_PE_PEFMS_PEVF_FK');
354 igs_ge_msg_stack.add;
355 app_exception.raise_exception;
356 RETURN;
357 END IF;
358 CLOSE cur_rowid;
359
360 END get_fk_igs_pe_ev_form;
361
362
363 PROCEDURE before_dml (
364 p_action IN VARCHAR2,
365 x_rowid IN VARCHAR2,
366 x_ev_form_stat_id IN NUMBER,
367 x_ev_form_id IN NUMBER,
368 x_action_date IN DATE,
369 x_action_type IN VARCHAR2,
370 x_prgm_start_date IN DATE,
371 x_prgm_end_date IN DATE,
372 x_remarks IN VARCHAR2,
373 x_termination_reason IN VARCHAR2,
374 x_end_program_reason IN VARCHAR2,
375 x_creation_date IN DATE,
376 x_created_by IN NUMBER,
377 x_last_update_date IN DATE,
378 x_last_updated_by IN NUMBER,
379 x_last_update_login IN NUMBER
380 ) AS
381 /*
382 || Created By : [email protected]
383 || Created On : 28-NOV-2002
384 || Purpose : Initialises the columns, Checks Constraints, Calls the
385 || Trigger Handlers for the table, before any DML operation.
386 || Known limitations, enhancements or remarks :
387 || Change History :
388 || Who When What
389 || (reverse chronological order - newest change first)
390 */
391 BEGIN
392
393 set_column_values (
394 p_action,
395 x_rowid,
396 x_ev_form_stat_id,
397 x_ev_form_id,
398 x_action_date,
399 x_action_type,
400 x_prgm_start_date,
401 x_prgm_end_date,
402 x_remarks,
403 x_termination_reason,
404 x_end_program_reason,
405 x_creation_date,
406 x_created_by,
407 x_last_update_date,
408 x_last_updated_by,
409 x_last_update_login
410 );
411
412 IF (p_action = 'INSERT') THEN
413 -- Call all the procedures related to Before Insert.
414 IF ( get_pk_for_validation(
415 new_references.ev_form_stat_id
416 )
417 ) THEN
418 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
419 igs_ge_msg_stack.add;
420 app_exception.raise_exception;
421 END IF;
422 check_uniqueness;
423 check_parent_existance;
424 ELSIF (p_action = 'UPDATE') THEN
425 -- Call all the procedures related to Before Update.
426 check_uniqueness;
427 check_parent_existance;
428 ELSIF (p_action = 'VALIDATE_INSERT') THEN
429 -- Call all the procedures related to Before Insert.
430 IF ( get_pk_for_validation (
431 new_references.ev_form_stat_id
432 )
433 ) THEN
434 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
435 igs_ge_msg_stack.add;
436 app_exception.raise_exception;
437 END IF;
438 check_uniqueness;
439 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
440 check_uniqueness;
441 END IF;
442
443 END before_dml;
444
445
446 PROCEDURE insert_row (
447 x_rowid IN OUT NOCOPY VARCHAR2,
448 x_ev_form_stat_id IN OUT NOCOPY NUMBER,
449 x_ev_form_id IN NUMBER,
450 x_action_date IN DATE,
451 x_action_type IN VARCHAR2,
452 x_prgm_start_date IN DATE,
453 x_prgm_end_date IN DATE,
454 x_remarks IN VARCHAR2,
455 x_termination_reason IN VARCHAR2,
456 x_end_program_reason IN VARCHAR2,
457 x_mode IN VARCHAR2
458 ) AS
459 /*
460 || Created By : [email protected]
461 || Created On : 28-NOV-2002
462 || Purpose : Handles the INSERT DML logic for the table.
463 || Known limitations, enhancements or remarks :
464 || Change History :
468
465 || Who When What
466 || (reverse chronological order - newest change first)
467 */
469 x_last_update_date DATE;
470 x_last_updated_by NUMBER;
471 x_last_update_login NUMBER;
472
473 BEGIN
474
475 x_last_update_date := SYSDATE;
476 IF (x_mode = 'I') THEN
477 x_last_updated_by := 1;
478 x_last_update_login := 0;
479 ELSIF (X_MODE IN ('R', 'S')) THEN
480 x_last_updated_by := fnd_global.user_id;
481 IF (x_last_updated_by IS NULL) THEN
482 x_last_updated_by := -1;
483 END IF;
484 x_last_update_login := fnd_global.login_id;
485 IF (x_last_update_login IS NULL) THEN
486 x_last_update_login := -1;
487 END IF;
488 ELSE
489 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
490 igs_ge_msg_stack.add;
491 app_exception.raise_exception;
492 END IF;
493
494 before_dml(
495 p_action => 'INSERT',
496 x_rowid => x_rowid,
497 x_ev_form_stat_id => x_ev_form_stat_id,
498 x_ev_form_id => x_ev_form_id,
499 x_action_date => x_action_date,
500 x_action_type => x_action_type,
501 x_prgm_start_date => x_prgm_start_date,
502 x_prgm_end_date => x_prgm_end_date,
503 x_remarks => x_remarks,
504 x_termination_reason => x_termination_reason,
505 x_end_program_reason => x_end_program_reason,
506 x_creation_date => x_last_update_date,
507 x_created_by => x_last_updated_by,
508 x_last_update_date => x_last_update_date,
509 x_last_updated_by => x_last_updated_by,
510 x_last_update_login => x_last_update_login
511 );
512
513 IF (x_mode = 'S') THEN
514 igs_sc_gen_001.set_ctx('R');
515 END IF;
516 INSERT INTO igs_pe_ev_form_stat (
517 ev_form_stat_id,
518 ev_form_id,
519 action_date,
520 action_type,
521 prgm_start_date,
522 prgm_end_date,
523 remarks,
524 termination_reason,
525 end_program_reason,
526 creation_date,
527 created_by,
528 last_update_date,
529 last_updated_by,
530 last_update_login
531 ) VALUES (
532 igs_pe_ev_form_stat_s.NEXTVAL,
533 new_references.ev_form_id,
534 new_references.action_date,
535 new_references.action_type,
536 new_references.prgm_start_date,
537 new_references.prgm_end_date,
538 new_references.remarks,
539 new_references.termination_reason,
540 new_references.end_program_reason,
541 x_last_update_date,
542 x_last_updated_by,
543 x_last_update_date,
544 x_last_updated_by,
545 x_last_update_login
546 ) RETURNING ROWID, ev_form_stat_id INTO x_rowid, x_ev_form_stat_id;
547 IF (x_mode = 'S') THEN
548 igs_sc_gen_001.unset_ctx('R');
549 END IF;
550
551
552 afterrowinsertupdate(p_insert => TRUE, p_update => FALSE);
553
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
558 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
559 fnd_message.set_token ('ERR_CD', SQLCODE);
560 igs_ge_msg_stack.add;
561 igs_sc_gen_001.unset_ctx('R');
562 app_exception.raise_exception;
563 ELSE
564 igs_sc_gen_001.unset_ctx('R');
565 RAISE;
566 END IF;
567 END insert_row;
568
569
570 PROCEDURE lock_row (
571 x_rowid IN VARCHAR2,
572 x_ev_form_stat_id IN NUMBER,
573 x_ev_form_id IN NUMBER,
574 x_action_date IN DATE,
575 x_action_type IN VARCHAR2,
576 x_prgm_start_date IN DATE,
577 x_prgm_end_date IN DATE,
578 x_remarks IN VARCHAR2,
579 x_termination_reason IN VARCHAR2,
580 x_end_program_reason IN VARCHAR2
581 ) AS
582 /*
583 || Created By : [email protected]
584 || Created On : 28-NOV-2002
585 || Purpose : Handles the LOCK mechanism for the table.
586 || Known limitations, enhancements or remarks :
587 || Change History :
588 || Who When What
589 || (reverse chronological order - newest change first)
590 */
591 CURSOR c1 IS
592 SELECT
593 ev_form_id,
594 action_date,
595 action_type,
596 prgm_start_date,
597 prgm_end_date,
598 remarks,
599 termination_reason,
600 end_program_reason
601 FROM igs_pe_ev_form_stat
602 WHERE rowid = x_rowid
603 FOR UPDATE NOWAIT;
604
605 tlinfo c1%ROWTYPE;
606
607 BEGIN
608
609 OPEN c1;
613 igs_ge_msg_stack.add;
610 FETCH c1 INTO tlinfo;
611 IF (c1%notfound) THEN
612 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
614 CLOSE c1;
615 app_exception.raise_exception;
616 RETURN;
617 END IF;
618 CLOSE c1;
619
620 IF (
621 (tlinfo.ev_form_id = x_ev_form_id)
622 AND (tlinfo.action_date = x_action_date)
623 AND (tlinfo.action_type = x_action_type)
624 AND ((tlinfo.prgm_start_date = x_prgm_start_date) OR ((tlinfo.prgm_start_date IS NULL) AND (X_prgm_start_date IS NULL)))
625 AND ((tlinfo.prgm_end_date = x_prgm_end_date) OR ((tlinfo.prgm_end_date IS NULL) AND (X_prgm_end_date IS NULL)))
626 AND ((tlinfo.remarks = x_remarks) OR ((tlinfo.remarks IS NULL) AND (X_remarks IS NULL)))
627 AND ((tlinfo.termination_reason = x_termination_reason) OR ((tlinfo.termination_reason IS NULL) AND (X_termination_reason IS NULL)))
628 AND ((tlinfo.end_program_reason = x_end_program_reason) OR ((tlinfo.end_program_reason IS NULL) AND (X_end_program_reason IS NULL)))
629 ) THEN
630 NULL;
631 ELSE
632 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
633 igs_ge_msg_stack.add;
634 app_exception.raise_exception;
635 END IF;
636
637 RETURN;
638
639 END lock_row;
640
641
642 PROCEDURE update_row (
643 x_rowid IN VARCHAR2,
644 x_ev_form_stat_id IN NUMBER,
645 x_ev_form_id IN NUMBER,
646 x_action_date IN DATE,
647 x_action_type IN VARCHAR2,
648 x_prgm_start_date IN DATE,
649 x_prgm_end_date IN DATE,
650 x_remarks IN VARCHAR2,
651 x_termination_reason IN VARCHAR2,
652 x_end_program_reason IN VARCHAR2,
653 x_mode IN VARCHAR2
654 ) AS
655 /*
656 || Created By : [email protected]
657 || Created On : 28-NOV-2002
658 || Purpose : Handles the UPDATE DML logic for the table.
659 || Known limitations, enhancements or remarks :
660 || Change History :
661 || Who When What
662 || (reverse chronological order - newest change first)
663 */
664 x_last_update_date DATE ;
665 x_last_updated_by NUMBER;
666 x_last_update_login NUMBER;
667
668 BEGIN
669
670 x_last_update_date := SYSDATE;
671 IF (X_MODE = 'I') THEN
672 x_last_updated_by := 1;
673 x_last_update_login := 0;
674 ELSIF (X_MODE IN ('R', 'S')) THEN
675 x_last_updated_by := fnd_global.user_id;
676 IF x_last_updated_by IS NULL THEN
677 x_last_updated_by := -1;
678 END IF;
679 x_last_update_login := fnd_global.login_id;
680 IF (x_last_update_login IS NULL) THEN
681 x_last_update_login := -1;
682 END IF;
683 ELSE
684 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
685 igs_ge_msg_stack.add;
686 app_exception.raise_exception;
687 END IF;
688
689 before_dml(
690 p_action => 'UPDATE',
691 x_rowid => x_rowid,
692 x_ev_form_stat_id => x_ev_form_stat_id,
693 x_ev_form_id => x_ev_form_id,
694 x_action_date => x_action_date,
695 x_action_type => x_action_type,
696 x_prgm_start_date => x_prgm_start_date,
697 x_prgm_end_date => x_prgm_end_date,
698 x_remarks => x_remarks,
699 x_termination_reason => x_termination_reason,
700 x_end_program_reason => x_end_program_reason,
701 x_creation_date => x_last_update_date,
702 x_created_by => x_last_updated_by,
703 x_last_update_date => x_last_update_date,
704 x_last_updated_by => x_last_updated_by,
705 x_last_update_login => x_last_update_login
706 );
707
708 IF (x_mode = 'S') THEN
709 igs_sc_gen_001.set_ctx('R');
710 END IF;
711 UPDATE igs_pe_ev_form_stat
712 SET
713 ev_form_id = new_references.ev_form_id,
714 action_date = new_references.action_date,
715 action_type = new_references.action_type,
716 prgm_start_date = new_references.prgm_start_date,
717 prgm_end_date = new_references.prgm_end_date,
718 remarks = new_references.remarks,
719 termination_reason = new_references.termination_reason,
720 end_program_reason = new_references.end_program_reason,
721 last_update_date = x_last_update_date,
722 last_updated_by = x_last_updated_by,
723 last_update_login = x_last_update_login
724 WHERE rowid = x_rowid;
725
726 IF (SQL%NOTFOUND) THEN
727 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
728 igs_ge_msg_stack.add;
729 igs_sc_gen_001.unset_ctx('R');
730 app_exception.raise_exception;
731 END IF;
732 IF (x_mode = 'S') THEN
733 igs_sc_gen_001.unset_ctx('R');
734 END IF;
735
736
737
738 EXCEPTION
739 WHEN OTHERS THEN
740 IF (SQLCODE = (-28115)) THEN
741 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
742 fnd_message.set_token ('ERR_CD', SQLCODE);
743 igs_ge_msg_stack.add;
744 igs_sc_gen_001.unset_ctx('R');
745 app_exception.raise_exception;
746 ELSE
747 igs_sc_gen_001.unset_ctx('R');
748 RAISE;
749 END IF;
750 END update_row;
751
752
753 PROCEDURE add_row (
754 x_rowid IN OUT NOCOPY VARCHAR2,
755 x_ev_form_stat_id IN OUT NOCOPY NUMBER,
756 x_ev_form_id IN NUMBER,
757 x_action_date IN DATE,
758 x_action_type IN VARCHAR2,
759 x_prgm_start_date IN DATE,
760 x_prgm_end_date IN DATE,
761 x_remarks IN VARCHAR2,
762 x_termination_reason IN VARCHAR2,
763 x_end_program_reason IN VARCHAR2,
764 x_mode IN VARCHAR2
765 ) AS
766 /*
767 || Created By : [email protected]
768 || Created On : 28-NOV-2002
769 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
770 || Known limitations, enhancements or remarks :
771 || Change History :
772 || Who When What
773 || (reverse chronological order - newest change first)
774 */
775 CURSOR c1 IS
776 SELECT rowid
777 FROM igs_pe_ev_form_stat
778 WHERE ev_form_stat_id = x_ev_form_stat_id;
779
780 BEGIN
781
782 OPEN c1;
783 FETCH c1 INTO x_rowid;
784 IF (c1%NOTFOUND) THEN
785 CLOSE c1;
786
787 insert_row (
788 x_rowid,
789 x_ev_form_stat_id,
790 x_ev_form_id,
791 x_action_date,
792 x_action_type,
793 x_prgm_start_date,
794 x_prgm_end_date,
795 x_remarks,
796 x_termination_reason,
797 x_end_program_reason,
798 x_mode
799 );
800 RETURN;
801 END IF;
802 CLOSE c1;
803
804 update_row (
805 x_rowid,
806 x_ev_form_stat_id,
807 x_ev_form_id,
808 x_action_date,
809 x_action_type,
810 x_prgm_start_date,
811 x_prgm_end_date,
812 x_remarks,
813 x_termination_reason,
814 x_end_program_reason,
815 x_mode
816 );
817
818 END add_row;
819
820
821 PROCEDURE delete_row (
822 x_rowid IN VARCHAR2,
823 x_mode IN VARCHAR2
824 ) AS
825 /*
826 || Created By : [email protected]
827 || Created On : 28-NOV-2002
828 || Purpose : Handles the DELETE DML logic for the table.
829 || Known limitations, enhancements or remarks :
830 || Change History :
831 || Who When What
832 || (reverse chronological order - newest change first)
833 */
834 BEGIN
835
836 before_dml (
837 p_action => 'DELETE',
838 x_rowid => x_rowid
839 );
840
841 IF (x_mode = 'S') THEN
842 igs_sc_gen_001.set_ctx('R');
843 END IF;
844 DELETE FROM igs_pe_ev_form_stat
845 WHERE rowid = x_rowid;
846
847 IF (SQL%NOTFOUND) THEN
848 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
849 igs_ge_msg_stack.add;
850 igs_sc_gen_001.unset_ctx('R');
851 app_exception.raise_exception;
852 END IF;
853 IF (x_mode = 'S') THEN
854 igs_sc_gen_001.unset_ctx('R');
855 END IF;
856
857
858 END delete_row;
859
860
861 END igs_pe_ev_form_stat_pkg;