[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_EV_FORM_PKG
Source
1 PACKAGE BODY igs_pe_ev_form_pkg AS
2 /* $Header: IGSNI51B.pls 120.2 2006/02/17 06:54:11 gmaheswa ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_ev_form%ROWTYPE;
6 new_references igs_pe_ev_form%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ev_form_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_print_form IN VARCHAR2,
14 x_form_effective_date IN DATE,
15 x_form_status IN VARCHAR2,
16 x_create_reason IN VARCHAR2,
17 x_is_valid IN VARCHAR2,
18 x_prgm_sponsor_amt IN NUMBER,
19 x_govt_org1_amt IN NUMBER,
20 x_govt_org1_code IN VARCHAR2,
21 x_govt_org2_amt IN NUMBER,
22 x_govt_org2_code IN VARCHAR2,
23 x_intl_org1_amt IN NUMBER,
24 x_intl_org1_code IN VARCHAR2,
25 x_intl_org2_amt IN NUMBER,
26 x_intl_org2_code IN VARCHAR2,
27 x_ev_govt_amt IN NUMBER,
28 x_bi_natnl_com_amt IN NUMBER,
29 x_other_govt_amt IN NUMBER,
30 x_personal_funds_amt IN NUMBER,
31 x_ev_form_number IN VARCHAR2,
32 x_prgm_start_date IN DATE,
33 x_prgm_end_date IN DATE,
34 x_last_reprint_date IN DATE,
35 x_reprint_reason IN VARCHAR2,
36 x_reprint_remarks IN VARCHAR2,
37 x_position_code IN NUMBER,
38 x_position_remarks IN VARCHAR2,
39 x_subject_field_code IN VARCHAR2,
40 x_subject_field_remarks IN VARCHAR2,
41 x_matriculation IN VARCHAR2,
42 x_remarks IN VARCHAR2,
43 x_creation_date IN DATE,
44 x_created_by IN NUMBER,
45 x_last_update_date IN DATE,
46 x_last_updated_by IN NUMBER,
47 x_last_update_login IN NUMBER,
48 x_category_code IN VARCHAR2,
49 x_init_prgm_start_date IN DATE,
50 x_govt_org1_othr_name IN VARCHAR2,
51 x_govt_org2_othr_name IN VARCHAR2 ,
52 x_intl_org1_othr_name IN VARCHAR2 ,
53 x_intl_org2_othr_name IN VARCHAR2 ,
54 x_no_show_flag IN VARCHAR2 ,
55 x_other_govt_name IN VARCHAR2 ,
56 x_sevis_school_id IN NUMBER
57 ) AS
58 /*
59 || Created By : [email protected]
60 || Created On : 28-NOV-2002
61 || Purpose : Initialises the Old and New references for the columns of the table.
62 || Known limitations, enhancements or remarks :
63 || Change History :
64 || Who When What
65 || (reverse chronological order - newest change first)
66 */
67
68 CURSOR cur_old_ref_values IS
69 SELECT *
70 FROM igs_pe_ev_form
71 WHERE rowid = x_rowid;
72
73 BEGIN
74
75 l_rowid := x_rowid;
76
77 -- Code for setting the Old and New Reference Values.
78 -- Populate Old Values.
79 OPEN cur_old_ref_values;
80 FETCH cur_old_ref_values INTO old_references;
81 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
82 CLOSE cur_old_ref_values;
83 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
84 igs_ge_msg_stack.add;
85 app_exception.raise_exception;
86 RETURN;
87 END IF;
88 CLOSE cur_old_ref_values;
89
90 -- Populate New Values.
91 new_references.ev_form_id := x_ev_form_id;
92 new_references.person_id := x_person_id;
93 new_references.print_form := x_print_form;
94 new_references.form_effective_date := x_form_effective_date;
95 new_references.form_status := x_form_status;
96 new_references.create_reason := x_create_reason;
97 new_references.is_valid := x_is_valid;
98 new_references.prgm_sponsor_amt := x_prgm_sponsor_amt;
99 new_references.govt_org1_amt := x_govt_org1_amt;
100 new_references.govt_org1_code := x_govt_org1_code;
101 new_references.govt_org2_amt := x_govt_org2_amt;
102 new_references.govt_org2_code := x_govt_org2_code;
103 new_references.intl_org1_amt := x_intl_org1_amt;
104 new_references.intl_org1_code := x_intl_org1_code;
105 new_references.intl_org2_amt := x_intl_org2_amt;
106 new_references.intl_org2_code := x_intl_org2_code;
107 new_references.ev_govt_amt := x_ev_govt_amt;
108 new_references.bi_natnl_com_amt := x_bi_natnl_com_amt;
109 new_references.other_govt_amt := x_other_govt_amt;
110 new_references.personal_funds_amt := x_personal_funds_amt;
111 new_references.ev_form_number := x_ev_form_number;
112 new_references.prgm_start_date := x_prgm_start_date;
113 new_references.prgm_end_date := x_prgm_end_date;
114 new_references.last_reprint_date := x_last_reprint_date;
115 new_references.reprint_reason := x_reprint_reason;
116 new_references.reprint_remarks := x_reprint_remarks;
117 new_references.position_code := x_position_code;
118 new_references.position_remarks := x_position_remarks;
119 new_references.subject_field_code := x_subject_field_code;
120 new_references.subject_field_remarks := x_subject_field_remarks;
121 new_references.matriculation := x_matriculation;
122 new_references.remarks := x_remarks;
123 new_references.category_code := x_category_code;
124 new_references.init_prgm_start_date := x_init_prgm_start_date;
125 new_references.govt_org1_othr_name := x_govt_org1_othr_name;
126 new_references.govt_org2_othr_name := x_govt_org2_othr_name;
127 new_references.intl_org1_othr_name := x_intl_org1_othr_name;
128 new_references.intl_org2_othr_name := x_intl_org2_othr_name;
129 new_references.no_show_flag := x_no_show_flag;
130 new_references.other_govt_name := x_other_govt_name;
131 new_references.SEVIS_SCHOOL_IDENTIFIER := x_sevis_school_id;
132
133 IF (p_action = 'UPDATE') THEN
134 new_references.creation_date := old_references.creation_date;
135 new_references.created_by := old_references.created_by;
136 ELSE
137 new_references.creation_date := x_creation_date;
138 new_references.created_by := x_created_by;
139 END IF;
140
141 new_references.last_update_date := x_last_update_date;
142 new_references.last_updated_by := x_last_updated_by;
143 new_references.last_update_login := x_last_update_login;
144
145 END set_column_values;
146
147 PROCEDURE afterrowinsertupdatedel(p_insert BOOLEAN ,p_update BOOLEAN,p_delete BOOLEAN) AS
148
149 CURSOR per_type(cp_system_type IGS_PE_PERSON_TYPES.system_type%TYPE,cp_closed_ind igs_pe_person_types.closed_ind%TYPE) IS
150 SELECT person_type_code FROM
151 IGS_PE_PERSON_TYPES
152 WHERE system_type = cp_system_type
153 AND closed_ind = cp_closed_ind;
154
155 CURSOR person_type (cp_system_type igs_pe_person_types.system_type%TYPE)IS
156 SELECT typ.rowid,typ.* FROM
157 igs_pe_typ_instances_all typ , igs_pe_person_types per_typ
158 WHERE
159 typ.person_id = old_references.person_id AND
160 per_typ.system_type = cp_system_type AND
161 typ.person_type_code = per_typ.person_type_code AND
162 typ.end_date IS NULL ;
163
164 l_person_type person_type%ROWTYPE;
165 l_per_type per_type%ROWTYPE;
166 lv_rowid VARCHAR2(25);
167 l_type_instance_id NUMBER(15);
168
169 BEGIN
170 OPEN per_type('EXCHG_VISITOR','N');
171 FETCH per_type INTO l_per_type;
172 IF per_type%FOUND THEN
173 IF p_insert = TRUE THEN
174 igs_pe_typ_instances_pkg.insert_row
175 (
176 X_ROWID => lv_rowid,
177 X_PERSON_ID => new_references.person_id,
178 X_COURSE_CD => null,
179 X_TYPE_INSTANCE_ID => l_type_instance_id,
180 X_PERSON_TYPE_CODE => l_per_type.person_type_code,
181 X_CC_VERSION_NUMBER => null,
182 X_FUNNEL_STATUS => null,
183 X_ADMISSION_APPL_NUMBER => null,
184 X_NOMINATED_COURSE_CD => null,
185 X_NCC_VERSION_NUMBER => null,
186 X_SEQUENCE_NUMBER => null,
187 X_START_DATE => new_references.form_effective_date,
188 X_END_DATE => null,
189 X_CREATE_METHOD => 'CREATE_EXCHG_VISITOR',
190 X_ENDED_BY => null,
191 X_END_METHOD => null,
192 X_ORG_ID => null,
193 X_EMPLMNT_CATEGORY_CODE => null
194 );
195
196 ELSIF p_update = TRUE THEN
197
198 IF (new_references.form_effective_date <> old_references.form_effective_date ) THEN
199 OPEN person_type('EXCHG_VISITOR');
200 FETCH person_type INTO l_person_type;
201 CLOSE person_type;
202
203 igs_pe_typ_instances_pkg.UPDATE_ROW
204 (
205 X_ROWID => l_person_type.rowid,
206 X_PERSON_ID => l_person_type.person_id,
207 X_COURSE_CD => l_person_type.course_cd,
208 X_TYPE_INSTANCE_ID => l_person_type.type_instance_id,
209 X_PERSON_TYPE_CODE => l_person_type.person_type_code,
210 X_CC_VERSION_NUMBER => l_person_type.cc_version_number,
211 X_FUNNEL_STATUS => l_person_type.funnel_status,
212 X_ADMISSION_APPL_NUMBER => l_person_type.admission_appl_number,
213 X_NOMINATED_COURSE_CD => l_person_type.nominated_course_cd,
214 X_NCC_VERSION_NUMBER => l_person_type.ncc_version_number,
215 X_SEQUENCE_NUMBER => l_person_type.sequence_number,
216 X_START_DATE => new_references.form_effective_date,
217 X_END_DATE => l_person_type.end_date,
218 X_CREATE_METHOD => l_person_type.create_method,
219 X_ENDED_BY => l_person_type.ended_by,
220 X_END_METHOD => l_person_type.end_method,
221 X_EMPLMNT_CATEGORY_CODE => l_person_type.emplmnt_category_code
222 );
223 END IF;
224 ELSIF p_delete = TRUE THEN
225 OPEN person_type('EXCHG_VISITOR');
226 FETCH person_type INTO l_person_type;
227 CLOSE person_type;
228 igs_pe_typ_instances_pkg.DELETE_ROW(l_person_type.rowid);
229 END IF;
230 CLOSE per_type;
231 END IF;
232 END afterrowinsertupdatedel;
233
234 PROCEDURE check_child_existance AS
235 /*
236 || Created By : [email protected]
237 || Created On : 28-NOV-2002
238 || Purpose : Checks for the existance of Child records.
239 || Known limitations, enhancements or remarks :
240 || Change History :
241 || Who When What
242 || (reverse chronological order - newest change first)
243 */
244 BEGIN
245
246 igs_pe_ev_form_stat_pkg.get_fk_igs_pe_ev_form (
247 old_references.ev_form_id
248 );
249
250 END check_child_existance;
251
252 PROCEDURE Check_Parent_Existance AS
253 /*************************************************************
254 Created By : npalanis.
255 Date Created By : 29/Nov/2002
256 Purpose :
257 Know limitations, enhancements or remarks
258 Change History
259 Who When What
260
261 (reverse chronological order - newest change first)
262 ***************************************************************/
263
264 BEGIN
265 IF (((old_references.person_id = new_references.person_id)) OR
266 ((new_references.person_id IS NULL))) THEN
267 NULL;
268 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
269 new_references.person_id
270 ) THEN
271 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275
276
277 END Check_Parent_Existance;
278
279 FUNCTION get_pk_for_validation (
280 x_ev_form_id IN NUMBER
281 ) RETURN BOOLEAN AS
282 /*
283 || Created By : [email protected]
284 || Created On : 28-NOV-2002
285 || Purpose : Validates the Primary Key of the table.
286 || Known limitations, enhancements or remarks :
287 || Change History :
288 || Who When What
289 || (reverse chronological order - newest change first)
290 */
291 CURSOR cur_rowid IS
292 SELECT rowid
293 FROM igs_pe_ev_form
294 WHERE ev_form_id = x_ev_form_id
295 FOR UPDATE NOWAIT;
296
297 lv_rowid cur_rowid%RowType;
298
299 BEGIN
300
301 OPEN cur_rowid;
302 FETCH cur_rowid INTO lv_rowid;
303 IF (cur_rowid%FOUND) THEN
304 CLOSE cur_rowid;
305 RETURN(TRUE);
306 ELSE
307 CLOSE cur_rowid;
308 RETURN(FALSE);
309 END IF;
310
311 END get_pk_for_validation;
312
313
314 PROCEDURE before_dml (
315 p_action IN VARCHAR2,
316 x_rowid IN VARCHAR2,
317 x_ev_form_id IN NUMBER,
318 x_person_id IN NUMBER,
319 x_print_form IN VARCHAR2,
320 x_form_effective_date IN DATE,
321 x_form_status IN VARCHAR2,
322 x_create_reason IN VARCHAR2,
323 x_is_valid IN VARCHAR2,
324 x_prgm_sponsor_amt IN NUMBER,
325 x_govt_org1_amt IN NUMBER,
326 x_govt_org1_code IN VARCHAR2,
327 x_govt_org2_amt IN NUMBER,
328 x_govt_org2_code IN VARCHAR2,
329 x_intl_org1_amt IN NUMBER,
330 x_intl_org1_code IN VARCHAR2,
331 x_intl_org2_amt IN NUMBER,
332 x_intl_org2_code IN VARCHAR2,
333 x_ev_govt_amt IN NUMBER,
334 x_bi_natnl_com_amt IN NUMBER,
335 x_other_govt_amt IN NUMBER,
336 x_personal_funds_amt IN NUMBER,
337 x_ev_form_number IN VARCHAR2,
338 x_prgm_start_date IN DATE,
339 x_prgm_end_date IN DATE,
340 x_last_reprint_date IN DATE,
341 x_reprint_reason IN VARCHAR2,
342 x_reprint_remarks IN VARCHAR2,
343 x_position_code IN NUMBER,
344 x_position_remarks IN VARCHAR2,
345 x_subject_field_code IN VARCHAR2,
346 x_subject_field_remarks IN VARCHAR2,
347 x_matriculation IN VARCHAR2,
348 x_remarks IN VARCHAR2,
349 x_creation_date IN DATE,
350 x_created_by IN NUMBER,
351 x_last_update_date IN DATE,
352 x_last_updated_by IN NUMBER,
353 x_last_update_login IN NUMBER ,
354 x_category_code IN VARCHAR2,
355 x_init_prgm_start_date IN DATE,
356 x_govt_org1_othr_name IN VARCHAR2,
357 x_govt_org2_othr_name IN VARCHAR2 ,
358 x_intl_org1_othr_name IN VARCHAR2 ,
359 x_intl_org2_othr_name IN VARCHAR2 ,
360 x_no_show_flag IN VARCHAR2 ,
361 x_other_govt_name IN VARCHAR2 ,
362 x_sevis_school_id IN NUMBER
363 ) AS
364 /*
365 || Created By : [email protected]
366 || Created On : 28-NOV-2002
367 || Purpose : Initialises the columns, Checks Constraints, Calls the
368 || Trigger Handlers for the table, before any DML operation.
369 || Known limitations, enhancements or remarks :
370 || Change History :
371 || Who When What
372 || (reverse chronological order - newest change first)
373 */
374 BEGIN
375
376 set_column_values (
377 p_action,
378 x_rowid,
379 x_ev_form_id,
380 x_person_id,
381 x_print_form,
382 x_form_effective_date,
383 x_form_status,
384 x_create_reason,
385 x_is_valid,
386 x_prgm_sponsor_amt,
387 x_govt_org1_amt,
388 x_govt_org1_code,
389 x_govt_org2_amt,
390 x_govt_org2_code,
391 x_intl_org1_amt,
392 x_intl_org1_code,
393 x_intl_org2_amt,
394 x_intl_org2_code,
395 x_ev_govt_amt,
396 x_bi_natnl_com_amt,
397 x_other_govt_amt,
398 x_personal_funds_amt,
399 x_ev_form_number,
400 x_prgm_start_date,
401 x_prgm_end_date,
402 x_last_reprint_date,
403 x_reprint_reason,
404 x_reprint_remarks,
405 x_position_code,
406 x_position_remarks,
407 x_subject_field_code,
408 x_subject_field_remarks,
409 x_matriculation,
410 x_remarks,
411 x_creation_date,
412 x_created_by,
413 x_last_update_date,
414 x_last_updated_by,
415 x_last_update_login ,
416 x_category_code,
417 x_init_prgm_start_date,
418 x_govt_org1_othr_name,
419 x_govt_org2_othr_name,
420 x_intl_org1_othr_name,
421 x_intl_org2_othr_name,
422 x_no_show_flag,
423 x_other_govt_name,
424 x_sevis_school_id
425 );
426
427 IF (p_action = 'INSERT') THEN
428 -- Call all the procedures related to Before Insert.
429 IF ( get_pk_for_validation(
430 new_references.ev_form_id
431 )
432 ) THEN
433 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
434 igs_ge_msg_stack.add;
435 app_exception.raise_exception;
436 END IF;
437 check_parent_existance;
438 ELSIF (p_action = 'UPDATE') THEN
439 -- Call all the procedures related to Before Update.
440 check_parent_existance;
441 ELSIF (p_action = 'DELETE') THEN
442 -- Call all the procedures related to Before Delete.
443 check_child_existance;
444 ELSIF (p_action = 'VALIDATE_INSERT') THEN
445 -- Call all the procedures related to Before Insert.
446 IF ( get_pk_for_validation (
447 new_references.ev_form_id
448 )
449 ) THEN
450 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
451 igs_ge_msg_stack.add;
452 app_exception.raise_exception;
453 END IF;
454 ELSIF (p_action = 'VALIDATE_DELETE') THEN
455 check_child_existance;
456 END IF;
457
458 END before_dml;
459
460
461 PROCEDURE insert_row (
462 x_rowid IN OUT NOCOPY VARCHAR2,
463 x_ev_form_id IN OUT NOCOPY NUMBER,
464 x_person_id IN NUMBER,
465 x_print_form IN VARCHAR2,
466 x_form_effective_date IN DATE,
467 x_form_status IN VARCHAR2,
468 x_create_reason IN VARCHAR2,
469 x_is_valid IN VARCHAR2,
470 x_prgm_sponsor_amt IN NUMBER,
471 x_govt_org1_amt IN NUMBER,
472 x_govt_org1_code IN VARCHAR2,
473 x_govt_org2_amt IN NUMBER,
474 x_govt_org2_code IN VARCHAR2,
475 x_intl_org1_amt IN NUMBER,
476 x_intl_org1_code IN VARCHAR2,
477 x_intl_org2_amt IN NUMBER,
478 x_intl_org2_code IN VARCHAR2,
479 x_ev_govt_amt IN NUMBER,
480 x_bi_natnl_com_amt IN NUMBER,
481 x_other_govt_amt IN NUMBER,
482 x_personal_funds_amt IN NUMBER,
483 x_ev_form_number IN VARCHAR2,
484 x_prgm_start_date IN DATE,
485 x_prgm_end_date IN DATE,
486 x_last_reprint_date IN DATE,
487 x_reprint_reason IN VARCHAR2,
488 x_reprint_remarks IN VARCHAR2,
489 x_position_code IN NUMBER,
490 x_position_remarks IN VARCHAR2,
491 x_subject_field_code IN VARCHAR2,
492 x_subject_field_remarks IN VARCHAR2,
493 x_matriculation IN VARCHAR2,
494 x_remarks IN VARCHAR2,
495 x_mode IN VARCHAR2 ,
496 x_category_code IN VARCHAR2,
497 x_init_prgm_start_date IN DATE,
498 x_govt_org1_othr_name IN VARCHAR2,
499 x_govt_org2_othr_name IN VARCHAR2 ,
500 x_intl_org1_othr_name IN VARCHAR2 ,
501 x_intl_org2_othr_name IN VARCHAR2 ,
502 x_no_show_flag IN VARCHAR2 ,
503 x_other_govt_name IN VARCHAR2 ,
504 x_sevis_school_id IN NUMBER
505 ) AS
506 /*
507 || Created By : [email protected]
508 || Created On : 28-NOV-2002
509 || Purpose : Handles the INSERT DML logic for the table.
510 || Known limitations, enhancements or remarks :
511 || Change History :
512 || Who When What
513 || (reverse chronological order - newest change first)
514 */
515
516 x_last_update_date DATE;
517 x_last_updated_by NUMBER;
518 x_last_update_login NUMBER;
519
520 BEGIN
521
522 x_last_update_date := SYSDATE;
523 IF (x_mode = 'I') THEN
524 x_last_updated_by := 1;
525 x_last_update_login := 0;
526 ELSIF (X_MODE IN ('R', 'S')) THEN
527 x_last_updated_by := fnd_global.user_id;
528 IF (x_last_updated_by IS NULL) THEN
529 x_last_updated_by := -1;
530 END IF;
531 x_last_update_login := fnd_global.login_id;
532 IF (x_last_update_login IS NULL) THEN
533 x_last_update_login := -1;
534 END IF;
535 ELSE
536 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
537 igs_ge_msg_stack.add;
538 app_exception.raise_exception;
539 END IF;
540
541 before_dml(
542 p_action => 'INSERT',
543 x_rowid => x_rowid,
544 x_ev_form_id => x_ev_form_id,
545 x_person_id => x_person_id,
546 x_print_form => x_print_form,
547 x_form_effective_date => x_form_effective_date,
548 x_form_status => x_form_status,
549 x_create_reason => x_create_reason,
550 x_is_valid => x_is_valid,
551 x_prgm_sponsor_amt => x_prgm_sponsor_amt,
552 x_govt_org1_amt => x_govt_org1_amt,
553 x_govt_org1_code => x_govt_org1_code,
554 x_govt_org2_amt => x_govt_org2_amt,
555 x_govt_org2_code => x_govt_org2_code,
556 x_intl_org1_amt => x_intl_org1_amt,
557 x_intl_org1_code => x_intl_org1_code,
558 x_intl_org2_amt => x_intl_org2_amt,
559 x_intl_org2_code => x_intl_org2_code,
560 x_ev_govt_amt => x_ev_govt_amt,
561 x_bi_natnl_com_amt => x_bi_natnl_com_amt,
562 x_other_govt_amt => x_other_govt_amt,
563 x_personal_funds_amt => x_personal_funds_amt,
564 x_ev_form_number => x_ev_form_number,
565 x_prgm_start_date => x_prgm_start_date,
566 x_prgm_end_date => x_prgm_end_date,
567 x_last_reprint_date => x_last_reprint_date,
568 x_reprint_reason => x_reprint_reason,
569 x_reprint_remarks => x_reprint_remarks,
570 x_position_code => x_position_code,
571 x_position_remarks => x_position_remarks,
572 x_subject_field_code => x_subject_field_code,
573 x_subject_field_remarks => x_subject_field_remarks,
574 x_matriculation => x_matriculation,
575 x_remarks => x_remarks,
576 x_creation_date => x_last_update_date,
577 x_created_by => x_last_updated_by,
578 x_last_update_date => x_last_update_date,
579 x_last_updated_by => x_last_updated_by,
580 x_last_update_login => x_last_update_login ,
581 x_category_code => x_category_code,
582 x_init_prgm_start_date => x_init_prgm_start_date,
583 x_govt_org1_othr_name => x_govt_org1_othr_name,
584 x_govt_org2_othr_name => x_govt_org2_othr_name,
585 x_intl_org1_othr_name => x_intl_org1_othr_name,
586 x_intl_org2_othr_name => x_intl_org2_othr_name,
587 x_no_show_flag => x_no_show_flag,
588 x_other_govt_name => x_other_govt_name,
589 x_sevis_school_id => x_sevis_school_id
590 );
591
592 IF (x_mode = 'S') THEN
593 igs_sc_gen_001.set_ctx('R');
594 END IF;
595 INSERT INTO igs_pe_ev_form (
596 ev_form_id,
597 person_id,
598 print_form,
599 form_effective_date,
600 form_status,
601 create_reason,
602 is_valid,
603 prgm_sponsor_amt,
604 govt_org1_amt,
605 govt_org1_code,
606 govt_org2_amt,
607 govt_org2_code,
608 intl_org1_amt,
609 intl_org1_code,
610 intl_org2_amt,
611 intl_org2_code,
612 ev_govt_amt,
613 bi_natnl_com_amt,
614 other_govt_amt,
615 personal_funds_amt,
616 ev_form_number,
617 prgm_start_date,
618 prgm_end_date,
619 last_reprint_date,
620 reprint_reason,
621 reprint_remarks,
622 position_code,
623 position_remarks,
624 subject_field_code,
625 subject_field_remarks,
626 matriculation,
627 remarks,
628 govt_org1_othr_name,
629 govt_org2_othr_name,
630 intl_org1_othr_name,
631 intl_org2_othr_name,
632 creation_date,
633 created_by,
634 last_update_date,
635 last_updated_by,
636 last_update_login,
637 category_code,
638 init_prgm_start_date,
639 no_show_flag,
640 other_govt_name,
641 SEVIS_SCHOOL_IDENTIFIER
642 ) VALUES (
643 igs_pe_ev_form_s.NEXTVAL,
644 new_references.person_id,
645 new_references.print_form,
646 new_references.form_effective_date,
647 new_references.form_status,
648 new_references.create_reason,
649 new_references.is_valid,
650 new_references.prgm_sponsor_amt,
651 new_references.govt_org1_amt,
652 new_references.govt_org1_code,
653 new_references.govt_org2_amt,
654 new_references.govt_org2_code,
655 new_references.intl_org1_amt,
656 new_references.intl_org1_code,
657 new_references.intl_org2_amt,
658 new_references.intl_org2_code,
659 new_references.ev_govt_amt,
660 new_references.bi_natnl_com_amt,
661 new_references.other_govt_amt,
662 new_references.personal_funds_amt,
663 new_references.ev_form_number,
664 new_references.prgm_start_date,
665 new_references.prgm_end_date,
666 new_references.last_reprint_date,
667 new_references.reprint_reason,
668 new_references.reprint_remarks,
669 new_references.position_code,
670 new_references.position_remarks,
671 new_references.subject_field_code,
672 new_references.subject_field_remarks,
673 new_references.matriculation,
674 new_references.remarks,
675 new_references.govt_org1_othr_name,
676 new_references.govt_org2_othr_name,
677 new_references.intl_org1_othr_name,
678 new_references.intl_org2_othr_name,
679 x_last_update_date,
680 x_last_updated_by,
681 x_last_update_date,
682 x_last_updated_by,
683 x_last_update_login ,
684 new_references.category_code,
685 new_references.init_prgm_start_date,
686 new_references.no_show_flag,
687 new_references.other_govt_name,
688 new_references.SEVIS_SCHOOL_IDENTIFIER
689 ) RETURNING ROWID, ev_form_id INTO x_rowid, x_ev_form_id;
690 IF (x_mode = 'S') THEN
691 igs_sc_gen_001.unset_ctx('R');
692 END IF;
693
694
695 afterrowinsertupdatedel(TRUE,FALSE,FALSE);
696
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
701 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
702 fnd_message.set_token ('ERR_CD', SQLCODE);
703 igs_ge_msg_stack.add;
704 igs_sc_gen_001.unset_ctx('R');
705 app_exception.raise_exception;
706 ELSE
707 igs_sc_gen_001.unset_ctx('R');
708 RAISE;
709 END IF;
710 END insert_row;
711
712
713 PROCEDURE lock_row (
714 x_rowid IN VARCHAR2,
715 x_ev_form_id IN NUMBER,
716 x_person_id IN NUMBER,
717 x_print_form IN VARCHAR2,
718 x_form_effective_date IN DATE,
719 x_form_status IN VARCHAR2,
720 x_create_reason IN VARCHAR2,
721 x_is_valid IN VARCHAR2,
722 x_prgm_sponsor_amt IN NUMBER,
723 x_govt_org1_amt IN NUMBER,
724 x_govt_org1_code IN VARCHAR2,
725 x_govt_org2_amt IN NUMBER,
726 x_govt_org2_code IN VARCHAR2,
727 x_intl_org1_amt IN NUMBER,
728 x_intl_org1_code IN VARCHAR2,
729 x_intl_org2_amt IN NUMBER,
730 x_intl_org2_code IN VARCHAR2,
731 x_ev_govt_amt IN NUMBER,
732 x_bi_natnl_com_amt IN NUMBER,
733 x_other_govt_amt IN NUMBER,
734 x_personal_funds_amt IN NUMBER,
735 x_ev_form_number IN VARCHAR2,
736 x_prgm_start_date IN DATE,
737 x_prgm_end_date IN DATE,
738 x_last_reprint_date IN DATE,
739 x_reprint_reason IN VARCHAR2,
740 x_reprint_remarks IN VARCHAR2,
741 x_position_code IN NUMBER,
742 x_position_remarks IN VARCHAR2,
743 x_subject_field_code IN VARCHAR2,
744 x_subject_field_remarks IN VARCHAR2,
745 x_matriculation IN VARCHAR2,
746 x_remarks IN VARCHAR2,
747 x_category_code IN VARCHAR2,
748 x_init_prgm_start_date IN DATE,
749 x_govt_org1_othr_name IN VARCHAR2,
750 x_govt_org2_othr_name IN VARCHAR2,
751 x_intl_org1_othr_name IN VARCHAR2 ,
752 x_intl_org2_othr_name IN VARCHAR2 ,
753 x_no_show_flag IN VARCHAR2,
754 x_other_govt_name IN VARCHAR2,
755 x_sevis_school_id IN NUMBER
756 ) AS
757 /*
758 || Created By : [email protected]
759 || Created On : 28-NOV-2002
760 || Purpose : Handles the LOCK mechanism for the table.
761 || Known limitations, enhancements or remarks :
762 || Change History :
763 || Who When What
764 || (reverse chronological order - newest change first)
765 */
766 CURSOR c1 IS
767 SELECT
768 person_id,
769 print_form,
770 form_effective_date,
771 form_status,
772 create_reason,
773 is_valid,
774 prgm_sponsor_amt,
775 govt_org1_amt,
776 govt_org1_code,
777 govt_org2_amt,
778 govt_org2_code,
779 intl_org1_amt,
780 intl_org1_code,
781 intl_org2_amt,
782 intl_org2_code,
783 ev_govt_amt,
784 bi_natnl_com_amt,
785 other_govt_amt,
786 personal_funds_amt,
787 ev_form_number,
788 prgm_start_date,
789 prgm_end_date,
790 last_reprint_date,
791 reprint_reason,
792 reprint_remarks,
793 position_code,
794 position_remarks,
795 subject_field_code,
796 subject_field_remarks,
797 matriculation,
798 remarks,
799 category_code,
800 init_prgm_start_date,
801 govt_org1_othr_name,
802 govt_org2_othr_name,
803 intl_org1_othr_name,
804 intl_org2_othr_name,
805 no_show_flag,
806 other_govt_name,
807 SEVIS_SCHOOL_IDENTIFIER
808 FROM igs_pe_ev_form
809 WHERE rowid = x_rowid
810 FOR UPDATE NOWAIT;
811
812 tlinfo c1%ROWTYPE;
813
814 BEGIN
815
816 OPEN c1;
817 FETCH c1 INTO tlinfo;
818 IF (c1%notfound) THEN
819 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
820 igs_ge_msg_stack.add;
821 CLOSE c1;
822 app_exception.raise_exception;
823 RETURN;
824 END IF;
825 CLOSE c1;
826
827 IF (
828 (tlinfo.person_id = x_person_id)
829 AND (tlinfo.print_form = x_print_form)
830 AND (tlinfo.form_effective_date = x_form_effective_date)
831 AND (tlinfo.form_status = x_form_status)
832 AND (tlinfo.create_reason = x_create_reason)
833 AND (tlinfo.is_valid = x_is_valid)
834 AND ((tlinfo.prgm_sponsor_amt = x_prgm_sponsor_amt) OR ((tlinfo.prgm_sponsor_amt IS NULL) AND (X_prgm_sponsor_amt IS NULL)))
835 AND ((tlinfo.govt_org1_amt = x_govt_org1_amt) OR ((tlinfo.govt_org1_amt IS NULL) AND (X_govt_org1_amt IS NULL)))
836 AND ((tlinfo.govt_org1_code = x_govt_org1_code) OR ((tlinfo.govt_org1_code IS NULL) AND (X_govt_org1_code IS NULL)))
837 AND ((tlinfo.govt_org2_amt = x_govt_org2_amt) OR ((tlinfo.govt_org2_amt IS NULL) AND (X_govt_org2_amt IS NULL)))
838 AND ((tlinfo.govt_org2_code = x_govt_org2_code) OR ((tlinfo.govt_org2_code IS NULL) AND (X_govt_org2_code IS NULL)))
839 AND ((tlinfo.intl_org1_amt = x_intl_org1_amt) OR ((tlinfo.intl_org1_amt IS NULL) AND (X_intl_org1_amt IS NULL)))
840 AND ((tlinfo.intl_org1_code = x_intl_org1_code) OR ((tlinfo.intl_org1_code IS NULL) AND (X_intl_org1_code IS NULL)))
841 AND ((tlinfo.intl_org2_amt = x_intl_org2_amt) OR ((tlinfo.intl_org2_amt IS NULL) AND (X_intl_org2_amt IS NULL)))
842 AND ((tlinfo.intl_org2_code = x_intl_org2_code) OR ((tlinfo.intl_org2_code IS NULL) AND (X_intl_org2_code IS NULL)))
843 AND ((tlinfo.ev_govt_amt = x_ev_govt_amt) OR ((tlinfo.ev_govt_amt IS NULL) AND (X_ev_govt_amt IS NULL)))
844 AND ((tlinfo.bi_natnl_com_amt = x_bi_natnl_com_amt) OR ((tlinfo.bi_natnl_com_amt IS NULL) AND (X_bi_natnl_com_amt IS NULL)))
845 AND ((tlinfo.other_govt_amt = x_other_govt_amt) OR ((tlinfo.other_govt_amt IS NULL) AND (X_other_govt_amt IS NULL)))
846 AND ((tlinfo.personal_funds_amt = x_personal_funds_amt) OR ((tlinfo.personal_funds_amt IS NULL) AND (X_personal_funds_amt IS NULL)))
847 AND ((tlinfo.ev_form_number = x_ev_form_number) OR ((tlinfo.ev_form_number IS NULL) AND (X_ev_form_number IS NULL)))
848 AND (tlinfo.prgm_start_date = x_prgm_start_date)
849 AND (tlinfo.prgm_end_date = x_prgm_end_date)
850 AND ((tlinfo.last_reprint_date = x_last_reprint_date) OR ((tlinfo.last_reprint_date IS NULL) AND (X_last_reprint_date IS NULL)))
851 AND ((tlinfo.reprint_reason = x_reprint_reason) OR ((tlinfo.reprint_reason IS NULL) AND (X_reprint_reason IS NULL)))
852 AND ((tlinfo.reprint_remarks = x_reprint_remarks) OR ((tlinfo.reprint_remarks IS NULL) AND (X_reprint_remarks IS NULL)))
853 AND (tlinfo.position_code = x_position_code)
854 AND ((tlinfo.position_remarks = x_position_remarks) OR ((tlinfo.position_remarks IS NULL) AND (X_position_remarks IS NULL)))
855 AND (tlinfo.subject_field_code = x_subject_field_code)
856 AND ((tlinfo.subject_field_remarks = x_subject_field_remarks) OR ((tlinfo.subject_field_remarks IS NULL) AND (X_subject_field_remarks IS NULL)))
857 AND ((tlinfo.matriculation = x_matriculation) OR ((tlinfo.matriculation IS NULL) AND (X_matriculation IS NULL)))
858 AND ((tlinfo.remarks = x_remarks) OR ((tlinfo.remarks IS NULL) AND (X_remarks IS NULL)))
859 AND ((tlinfo.category_code = x_category_code) OR ((tlinfo.category_code IS NULL) AND (x_category_code IS NULL)))
860 AND ((tlinfo.init_prgm_start_date = x_init_prgm_start_date) OR ((tlinfo.init_prgm_start_date IS NULL) AND (x_init_prgm_start_date IS NULL )))
861 AND ((tlinfo.govt_org1_othr_name = x_govt_org1_othr_name) OR ((tlinfo.govt_org1_othr_name IS NULL) AND (x_govt_org1_othr_name IS NULL )))
862 AND ((tlinfo.govt_org2_othr_name = x_govt_org2_othr_name) OR ((tlinfo.govt_org2_othr_name IS NULL) AND (x_govt_org2_othr_name IS NULL )) )
863 AND ((tlinfo.intl_org1_othr_name = x_intl_org1_othr_name) OR ((tlinfo.intl_org1_othr_name IS NULL) AND (x_intl_org1_othr_name IS NULL )))
864 AND ((tlinfo.intl_org2_othr_name = x_intl_org2_othr_name) OR ((tlinfo.intl_org2_othr_name IS NULL) AND (x_intl_org2_othr_name IS NULL )))
865 AND ((tlinfo.no_show_flag = x_no_show_flag) OR ((tlinfo.no_show_flag IS NULL) AND (x_no_show_flag IS NULL )))
866 AND ((tlinfo.other_govt_name = x_other_govt_name) OR ((tlinfo.other_govt_name IS NULL) AND (x_other_govt_name IS NULL )))
867 AND ((tlinfo.SEVIS_SCHOOL_IDENTIFIER = x_sevis_school_id) OR ((tlinfo.SEVIS_SCHOOL_IDENTIFIER IS NULL) AND (x_sevis_school_id IS NULL )))
868 ) THEN
869 NULL;
870 ELSE
871 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
872 igs_ge_msg_stack.add;
873 app_exception.raise_exception;
874 END IF;
875
876 RETURN;
877
878 END lock_row;
879
880
881 PROCEDURE update_row (
882 x_rowid IN VARCHAR2,
883 x_ev_form_id IN NUMBER,
884 x_person_id IN NUMBER,
885 x_print_form IN VARCHAR2,
886 x_form_effective_date IN DATE,
887 x_form_status IN VARCHAR2,
888 x_create_reason IN VARCHAR2,
889 x_is_valid IN VARCHAR2,
890 x_prgm_sponsor_amt IN NUMBER,
891 x_govt_org1_amt IN NUMBER,
892 x_govt_org1_code IN VARCHAR2,
893 x_govt_org2_amt IN NUMBER,
894 x_govt_org2_code IN VARCHAR2,
895 x_intl_org1_amt IN NUMBER,
896 x_intl_org1_code IN VARCHAR2,
897 x_intl_org2_amt IN NUMBER,
898 x_intl_org2_code IN VARCHAR2,
899 x_ev_govt_amt IN NUMBER,
900 x_bi_natnl_com_amt IN NUMBER,
901 x_other_govt_amt IN NUMBER,
902 x_personal_funds_amt IN NUMBER,
903 x_ev_form_number IN VARCHAR2,
904 x_prgm_start_date IN DATE,
905 x_prgm_end_date IN DATE,
906 x_last_reprint_date IN DATE,
907 x_reprint_reason IN VARCHAR2,
908 x_reprint_remarks IN VARCHAR2,
909 x_position_code IN NUMBER,
910 x_position_remarks IN VARCHAR2,
911 x_subject_field_code IN VARCHAR2,
912 x_subject_field_remarks IN VARCHAR2,
913 x_matriculation IN VARCHAR2,
914 x_remarks IN VARCHAR2,
915 x_mode IN VARCHAR2 ,
916 x_category_code IN VARCHAR2,
917 x_init_prgm_start_date IN DATE,
918 x_govt_org1_othr_name IN VARCHAR2,
919 x_govt_org2_othr_name IN VARCHAR2 ,
920 x_intl_org1_othr_name IN VARCHAR2 ,
921 x_intl_org2_othr_name IN VARCHAR2 ,
922 x_no_show_flag IN VARCHAR2 ,
923 x_other_govt_name IN VARCHAR2 ,
924 x_sevis_school_id IN NUMBER
925 ) AS
926 /*
927 || Created By : [email protected]
928 || Created On : 28-NOV-2002
929 || Purpose : Handles the UPDATE DML logic for the table.
930 || Known limitations, enhancements or remarks :
931 || Change History :
932 || Who When What
933 || (reverse chronological order - newest change first)
934 */
935 x_last_update_date DATE ;
936 x_last_updated_by NUMBER;
937 x_last_update_login NUMBER;
938
939 BEGIN
940
941 x_last_update_date := SYSDATE;
942 IF (X_MODE = 'I') THEN
943 x_last_updated_by := 1;
944 x_last_update_login := 0;
945 ELSIF (X_MODE IN ('R', 'S')) THEN
946 x_last_updated_by := fnd_global.user_id;
947 IF x_last_updated_by IS NULL THEN
948 x_last_updated_by := -1;
949 END IF;
950 x_last_update_login := fnd_global.login_id;
951 IF (x_last_update_login IS NULL) THEN
952 x_last_update_login := -1;
953 END IF;
954 ELSE
955 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
956 igs_ge_msg_stack.add;
957 app_exception.raise_exception;
958 END IF;
959
960 before_dml(
961 p_action => 'UPDATE',
962 x_rowid => x_rowid,
963 x_ev_form_id => x_ev_form_id,
964 x_person_id => x_person_id,
965 x_print_form => x_print_form,
966 x_form_effective_date => x_form_effective_date,
967 x_form_status => x_form_status,
968 x_create_reason => x_create_reason,
969 x_is_valid => x_is_valid,
970 x_prgm_sponsor_amt => x_prgm_sponsor_amt,
971 x_govt_org1_amt => x_govt_org1_amt,
972 x_govt_org1_code => x_govt_org1_code,
973 x_govt_org2_amt => x_govt_org2_amt,
974 x_govt_org2_code => x_govt_org2_code,
975 x_intl_org1_amt => x_intl_org1_amt,
976 x_intl_org1_code => x_intl_org1_code,
977 x_intl_org2_amt => x_intl_org2_amt,
978 x_intl_org2_code => x_intl_org2_code,
979 x_ev_govt_amt => x_ev_govt_amt,
980 x_bi_natnl_com_amt => x_bi_natnl_com_amt,
981 x_other_govt_amt => x_other_govt_amt,
982 x_personal_funds_amt => x_personal_funds_amt,
983 x_ev_form_number => x_ev_form_number,
984 x_prgm_start_date => x_prgm_start_date,
985 x_prgm_end_date => x_prgm_end_date,
986 x_last_reprint_date => x_last_reprint_date,
987 x_reprint_reason => x_reprint_reason,
988 x_reprint_remarks => x_reprint_remarks,
989 x_position_code => x_position_code,
990 x_position_remarks => x_position_remarks,
991 x_subject_field_code => x_subject_field_code,
992 x_subject_field_remarks => x_subject_field_remarks,
993 x_matriculation => x_matriculation,
994 x_remarks => x_remarks,
995 x_creation_date => x_last_update_date,
996 x_created_by => x_last_updated_by,
997 x_last_update_date => x_last_update_date,
998 x_last_updated_by => x_last_updated_by,
999 x_last_update_login => x_last_update_login ,
1000 x_category_code => x_category_code,
1001 x_init_prgm_start_date => x_init_prgm_start_date,
1002 x_govt_org1_othr_name => x_govt_org1_othr_name,
1003 x_govt_org2_othr_name => x_govt_org2_othr_name,
1004 x_intl_org1_othr_name => x_intl_org1_othr_name,
1005 x_intl_org2_othr_name => x_intl_org2_othr_name,
1006 x_no_show_flag => x_no_show_flag,
1007 x_other_govt_name => x_other_govt_name,
1008 x_sevis_school_id => x_sevis_school_id
1009 );
1010
1011 IF (x_mode = 'S') THEN
1012 igs_sc_gen_001.set_ctx('R');
1013 END IF;
1014 UPDATE igs_pe_ev_form
1015 SET
1016 person_id = new_references.person_id,
1017 print_form = new_references.print_form,
1018 form_effective_date = new_references.form_effective_date,
1019 form_status = new_references.form_status,
1020 create_reason = new_references.create_reason,
1021 is_valid = new_references.is_valid,
1022 prgm_sponsor_amt = new_references.prgm_sponsor_amt,
1023 govt_org1_amt = new_references.govt_org1_amt,
1024 govt_org1_code = new_references.govt_org1_code,
1025 govt_org2_amt = new_references.govt_org2_amt,
1026 govt_org2_code = new_references.govt_org2_code,
1027 intl_org1_amt = new_references.intl_org1_amt,
1028 intl_org1_code = new_references.intl_org1_code,
1029 intl_org2_amt = new_references.intl_org2_amt,
1030 intl_org2_code = new_references.intl_org2_code,
1031 ev_govt_amt = new_references.ev_govt_amt,
1032 bi_natnl_com_amt = new_references.bi_natnl_com_amt,
1033 other_govt_amt = new_references.other_govt_amt,
1034 personal_funds_amt = new_references.personal_funds_amt,
1035 ev_form_number = new_references.ev_form_number,
1036 prgm_start_date = new_references.prgm_start_date,
1037 prgm_end_date = new_references.prgm_end_date,
1038 last_reprint_date = new_references.last_reprint_date,
1039 reprint_reason = new_references.reprint_reason,
1040 reprint_remarks = new_references.reprint_remarks,
1041 position_code = new_references.position_code,
1042 position_remarks = new_references.position_remarks,
1043 subject_field_code = new_references.subject_field_code,
1044 subject_field_remarks = new_references.subject_field_remarks,
1045 matriculation = new_references.matriculation,
1046 remarks = new_references.remarks,
1047 govt_org1_othr_name = new_references.govt_org1_othr_name,
1048 govt_org2_othr_name = new_references.govt_org2_othr_name,
1049 intl_org1_othr_name = new_references.intl_org1_othr_name,
1050 intl_org2_othr_name = new_references.intl_org2_othr_name,
1051 last_update_date = x_last_update_date,
1052 last_updated_by = x_last_updated_by,
1053 last_update_login = x_last_update_login ,
1054 category_code = x_category_code,
1055 init_prgm_start_date = x_init_prgm_start_date,
1056 no_show_flag = x_no_show_flag,
1057 other_govt_name = new_references.other_govt_name,
1058 SEVIS_SCHOOL_IDENTIFIER = new_references.SEVIS_SCHOOL_IDENTIFIER
1059 WHERE rowid = x_rowid;
1060
1061 IF (SQL%NOTFOUND) THEN
1062 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1063 igs_ge_msg_stack.add;
1064 igs_sc_gen_001.unset_ctx('R');
1065 app_exception.raise_exception;
1066 END IF;
1067 IF (x_mode = 'S') THEN
1068 igs_sc_gen_001.unset_ctx('R');
1069 END IF;
1070
1071
1072 afterrowinsertupdatedel(FALSE,TRUE,FALSE);
1073
1074
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 IF (SQLCODE = (-28115)) THEN
1078 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1079 fnd_message.set_token ('ERR_CD', SQLCODE);
1080 igs_ge_msg_stack.add;
1081 igs_sc_gen_001.unset_ctx('R');
1082 app_exception.raise_exception;
1083 ELSE
1084 igs_sc_gen_001.unset_ctx('R');
1085 RAISE;
1086 END IF;
1087 END update_row;
1088
1089
1090 PROCEDURE add_row (
1091 x_rowid IN OUT NOCOPY VARCHAR2,
1092 x_ev_form_id IN OUT NOCOPY NUMBER,
1093 x_person_id IN NUMBER,
1094 x_print_form IN VARCHAR2,
1095 x_form_effective_date IN DATE,
1096 x_form_status IN VARCHAR2,
1097 x_create_reason IN VARCHAR2,
1098 x_is_valid IN VARCHAR2,
1099 x_prgm_sponsor_amt IN NUMBER,
1100 x_govt_org1_amt IN NUMBER,
1101 x_govt_org1_code IN VARCHAR2,
1102 x_govt_org2_amt IN NUMBER,
1103 x_govt_org2_code IN VARCHAR2,
1104 x_intl_org1_amt IN NUMBER,
1105 x_intl_org1_code IN VARCHAR2,
1106 x_intl_org2_amt IN NUMBER,
1107 x_intl_org2_code IN VARCHAR2,
1108 x_ev_govt_amt IN NUMBER,
1109 x_bi_natnl_com_amt IN NUMBER,
1110 x_other_govt_amt IN NUMBER,
1111 x_personal_funds_amt IN NUMBER,
1112 x_ev_form_number IN VARCHAR2,
1113 x_prgm_start_date IN DATE,
1114 x_prgm_end_date IN DATE,
1115 x_last_reprint_date IN DATE,
1116 x_reprint_reason IN VARCHAR2,
1117 x_reprint_remarks IN VARCHAR2,
1118 x_position_code IN NUMBER,
1119 x_position_remarks IN VARCHAR2,
1120 x_subject_field_code IN VARCHAR2,
1121 x_subject_field_remarks IN VARCHAR2,
1122 x_matriculation IN VARCHAR2,
1123 x_remarks IN VARCHAR2,
1124 x_mode IN VARCHAR2 ,
1125 x_category_code IN VARCHAR2,
1126 x_init_prgm_start_date IN DATE,
1127 x_govt_org1_othr_name IN VARCHAR2,
1128 x_govt_org2_othr_name IN VARCHAR2 ,
1129 x_intl_org1_othr_name IN VARCHAR2 ,
1130 x_intl_org2_othr_name IN VARCHAR2 ,
1131 x_no_show_flag IN VARCHAR2 ,
1132 x_other_govt_name IN VARCHAR2,
1133 x_sevis_school_id IN NUMBER
1134 ) AS
1135 /*
1136 || Created By : [email protected]
1137 || Created On : 28-NOV-2002
1138 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1139 || Known limitations, enhancements or remarks :
1140 || Change History :
1141 || Who When What
1142 || (reverse chronological order - newest change first)
1143 */
1144 CURSOR c1 IS
1145 SELECT rowid
1146 FROM igs_pe_ev_form
1147 WHERE ev_form_id = x_ev_form_id;
1148
1149 BEGIN
1150
1151 OPEN c1;
1152 FETCH c1 INTO x_rowid;
1153 IF (c1%NOTFOUND) THEN
1154 CLOSE c1;
1155
1156 insert_row (
1157 x_rowid,
1158 x_ev_form_id,
1159 x_person_id,
1160 x_print_form,
1161 x_form_effective_date,
1162 x_form_status,
1163 x_create_reason,
1164 x_is_valid,
1165 x_prgm_sponsor_amt,
1166 x_govt_org1_amt,
1167 x_govt_org1_code,
1168 x_govt_org2_amt,
1169 x_govt_org2_code,
1170 x_intl_org1_amt,
1171 x_intl_org1_code,
1172 x_intl_org2_amt,
1173 x_intl_org2_code,
1174 x_ev_govt_amt,
1175 x_bi_natnl_com_amt,
1176 x_other_govt_amt,
1177 x_personal_funds_amt,
1178 x_ev_form_number,
1179 x_prgm_start_date,
1180 x_prgm_end_date,
1181 x_last_reprint_date,
1182 x_reprint_reason,
1183 x_reprint_remarks,
1184 x_position_code,
1185 x_position_remarks,
1186 x_subject_field_code,
1187 x_subject_field_remarks,
1188 x_matriculation,
1189 x_remarks,
1190 x_mode ,
1191 x_category_code,
1192 x_init_prgm_start_date,
1193 x_govt_org1_othr_name,
1194 x_govt_org2_othr_name,
1195 x_intl_org1_othr_name,
1196 x_intl_org2_othr_name,
1197 x_no_show_flag,
1198 x_other_govt_name,
1199 x_sevis_school_id
1200 );
1201 RETURN;
1202 END IF;
1203 CLOSE c1;
1204
1205 update_row (
1206 x_rowid,
1207 x_ev_form_id,
1208 x_person_id,
1209 x_print_form,
1210 x_form_effective_date,
1211 x_form_status,
1212 x_create_reason,
1213 x_is_valid,
1214 x_prgm_sponsor_amt,
1215 x_govt_org1_amt,
1216 x_govt_org1_code,
1217 x_govt_org2_amt,
1218 x_govt_org2_code,
1219 x_intl_org1_amt,
1220 x_intl_org1_code,
1221 x_intl_org2_amt,
1222 x_intl_org2_code,
1223 x_ev_govt_amt,
1224 x_bi_natnl_com_amt,
1225 x_other_govt_amt,
1226 x_personal_funds_amt,
1227 x_ev_form_number,
1228 x_prgm_start_date,
1229 x_prgm_end_date,
1230 x_last_reprint_date,
1231 x_reprint_reason,
1232 x_reprint_remarks,
1233 x_position_code,
1234 x_position_remarks,
1235 x_subject_field_code,
1236 x_subject_field_remarks,
1237 x_matriculation,
1238 x_remarks,
1239 x_mode ,
1240 x_category_code,
1241 x_init_prgm_start_date,
1242 x_govt_org1_othr_name,
1243 x_govt_org2_othr_name,
1244 x_intl_org1_othr_name,
1245 x_intl_org2_othr_name,
1246 x_no_show_flag,
1247 x_other_govt_name,
1248 x_sevis_school_id
1249 );
1250
1251 END add_row;
1252
1253
1254 PROCEDURE delete_row (
1255 x_rowid IN VARCHAR2,
1256 x_mode IN VARCHAR2
1257 ) AS
1258 /*
1259 || Created By : [email protected]
1260 || Created On : 28-NOV-2002
1261 || Purpose : Handles the DELETE DML logic for the table.
1262 || Known limitations, enhancements or remarks :
1263 || Change History :
1264 || Who When What
1265 || (reverse chronological order - newest change first)
1266 */
1267 BEGIN
1268
1269 before_dml (
1270 p_action => 'DELETE',
1271 x_rowid => x_rowid
1272 );
1273
1274 IF (x_mode = 'S') THEN
1275 igs_sc_gen_001.set_ctx('R');
1276 END IF;
1277 DELETE FROM igs_pe_ev_form
1278 WHERE rowid = x_rowid;
1279
1280 IF (SQL%NOTFOUND) THEN
1281 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1282 igs_ge_msg_stack.add;
1283 igs_sc_gen_001.unset_ctx('R');
1284 app_exception.raise_exception;
1285 END IF;
1286 IF (x_mode = 'S') THEN
1287 igs_sc_gen_001.unset_ctx('R');
1288 END IF;
1289
1290
1291 afterrowinsertupdatedel(FALSE,FALSE,TRUE);
1292
1293 END delete_row;
1294
1295
1296 END igs_pe_ev_form_pkg;