[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_1098T_DATA_PKG
Source
1 PACKAGE BODY igs_fi_1098t_data_pkg AS
2 /* $Header: IGSSIE8B.pls 120.0 2005/09/09 19:11:39 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_1098t_data%ROWTYPE;
6 new_references igs_fi_1098t_data%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_object_version_number IN NUMBER,
12 x_stu_1098t_id IN NUMBER,
13 x_tax_year_name IN VARCHAR2,
14 x_party_id IN NUMBER,
15 x_extract_date IN DATE,
16 x_party_name IN VARCHAR2,
17 x_taxid IN VARCHAR2,
18 x_stu_name_control IN VARCHAR2,
19 x_country IN VARCHAR2,
20 x_address1 IN VARCHAR2,
21 x_address2 IN VARCHAR2,
22 x_refund_amt IN NUMBER,
23 x_half_time_flag IN VARCHAR2,
24 x_grad_flag IN VARCHAR2,
25 x_special_data_entry IN VARCHAR2,
26 x_status_code IN VARCHAR2,
27 x_error_code IN VARCHAR2,
28 x_file_name IN VARCHAR2,
29 x_irs_filed_flag IN VARCHAR2,
30 x_correction_flag IN VARCHAR2,
31 x_correction_type_code IN VARCHAR2,
32 x_stmnt_print_flag IN VARCHAR2,
33 x_override_flag IN VARCHAR2,
34 x_address3 IN VARCHAR2,
35 x_address4 IN VARCHAR2,
36 x_city IN VARCHAR2,
37 x_postal_code IN VARCHAR2,
38 x_state IN VARCHAR2,
39 x_province IN VARCHAR2,
40 x_county IN VARCHAR2,
41 x_delivery_point_code IN VARCHAR2,
42 x_payment_amt IN NUMBER,
43 x_billed_amt IN NUMBER,
44 x_adj_amt IN NUMBER,
45 x_fin_aid_amt IN NUMBER,
46 x_fin_aid_adj_amt IN NUMBER,
47 x_next_acad_flag IN VARCHAR2,
48 x_batch_id IN NUMBER,
49 x_creation_date IN DATE,
50 x_created_by IN NUMBER,
51 x_last_update_date IN DATE,
52 x_last_updated_by IN NUMBER,
53 x_last_update_login IN NUMBER
54 ) AS
55 /*
56 || Created By : [email protected]
57 || Created On : 01-MAY-2005
58 || Purpose : Initialises the Old and New references for the columns of the table.
59 || Known limitations, enhancements or remarks :
60 || Change History :
61 || Who When What
62 || (reverse chronological order - newest change first)
63 */
64
65 CURSOR cur_old_ref_values IS
66 SELECT *
67 FROM igs_fi_1098t_data
68 WHERE rowid = x_rowid;
69
70 BEGIN
71
72 l_rowid := x_rowid;
73
74 -- Code for setting the Old and New Reference Values.
75 -- Populate Old Values.
76 OPEN cur_old_ref_values;
77 FETCH cur_old_ref_values INTO old_references;
78 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
79 CLOSE cur_old_ref_values;
80 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
81 igs_ge_msg_stack.add;
82 app_exception.raise_exception;
83 RETURN;
84 END IF;
85 CLOSE cur_old_ref_values;
86
87 -- Populate New Values.
88 new_references.object_version_number := x_object_version_number;
89 new_references.stu_1098t_id := x_stu_1098t_id;
90 new_references.tax_year_name := x_tax_year_name;
91 new_references.party_id := x_party_id;
92 new_references.extract_date := x_extract_date;
93 new_references.party_name := x_party_name;
94 new_references.taxid := x_taxid;
95 new_references.stu_name_control := x_stu_name_control;
96 new_references.country := x_country;
97 new_references.address1 := x_address1;
98 new_references.address2 := x_address2;
99 new_references.refund_amt := x_refund_amt;
100 new_references.half_time_flag := x_half_time_flag;
101 new_references.grad_flag := x_grad_flag;
102 new_references.special_data_entry := x_special_data_entry;
103 new_references.status_code := x_status_code;
104 new_references.error_code := x_error_code;
105 new_references.file_name := x_file_name;
106 new_references.irs_filed_flag := x_irs_filed_flag;
107 new_references.correction_flag := x_correction_flag;
108 new_references.correction_type_code := x_correction_type_code;
109 new_references.stmnt_print_flag := x_stmnt_print_flag;
110 new_references.override_flag := x_override_flag;
111 new_references.address3 := x_address3;
112 new_references.address4 := x_address4;
113 new_references.city := x_city;
114 new_references.postal_code := x_postal_code;
115 new_references.state := x_state;
116 new_references.province := x_province;
117 new_references.county := x_county;
118 new_references.delivery_point_code := x_delivery_point_code;
119 new_references.payment_amt := x_payment_amt;
120 new_references.billed_amt := x_billed_amt;
121 new_references.adj_amt := x_adj_amt;
122 new_references.fin_aid_amt := x_fin_aid_amt;
123 new_references.fin_aid_adj_amt := x_fin_aid_adj_amt;
124 new_references.next_acad_flag := x_next_acad_flag;
125 new_references.batch_id := x_batch_id;
126
127 IF (p_action = 'UPDATE') THEN
128 new_references.creation_date := old_references.creation_date;
129 new_references.created_by := old_references.created_by;
130 ELSE
131 new_references.creation_date := x_creation_date;
132 new_references.created_by := x_created_by;
133 END IF;
134
135 new_references.last_update_date := x_last_update_date;
136 new_references.last_updated_by := x_last_updated_by;
137 new_references.last_update_login := x_last_update_login;
138
139 END set_column_values;
140
141
142 PROCEDURE check_parent_existance AS
143 /*
144 || Created By : [email protected]
145 || Created On : 01-MAY-2005
146 || Purpose : Checks for the existance of Parent records.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152
153 CURSOR cur_hzp(cp_party_id hz_parties.party_id%TYPE) IS
154 SELECT 'x'
155 FROM hz_parties
156 WHERE party_id = cp_party_id;
157
158 l_var VARCHAR2(1);
159 BEGIN
160
161 IF (((old_references.batch_id = new_references.batch_id)) OR
162 ((new_references.batch_id IS NULL))) THEN
163 NULL;
164 ELSIF NOT igs_fi_1098t_batchs_pkg.get_pk_for_validation (
165 new_references.batch_id
166 ) THEN
167 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END IF;
171
172 IF (((old_references.party_id = new_references.party_id)) OR
173 ((new_references.party_id IS NULL))) THEN
174 NULL;
175 ELSE
176 OPEN cur_hzp(new_references.party_id);
177 FETCH cur_hzp INTO l_var;
178 IF cur_hzp%NOTFOUND THEN
179 CLOSE cur_hzp;
180 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
181 igs_ge_msg_stack.add;
182 app_exception.raise_exception;
183 END IF;
184 CLOSE cur_hzp;
185 END IF;
186
187 END check_parent_existance;
188
189
190 PROCEDURE check_child_existance AS
191 /*
192 || Created By : [email protected]
193 || Created On : 01-MAY-2005
194 || Purpose : Checks for the existance of Child records.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 BEGIN
201
202 null;
203 END check_child_existance;
204
205
206 FUNCTION get_pk_for_validation (
207 x_stu_1098t_id IN NUMBER
208 ) RETURN BOOLEAN AS
209 /*
210 || Created By : [email protected]
211 || Created On : 01-MAY-2005
212 || Purpose : Validates the Primary Key of the table.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igs_fi_1098t_data
221 WHERE stu_1098t_id = x_stu_1098t_id
222 FOR UPDATE NOWAIT;
223
224 lv_rowid cur_rowid%RowType;
225
226 BEGIN
227
228 OPEN cur_rowid;
229 FETCH cur_rowid INTO lv_rowid;
230 IF (cur_rowid%FOUND) THEN
231 CLOSE cur_rowid;
232 RETURN(TRUE);
233 ELSE
234 CLOSE cur_rowid;
235 RETURN(FALSE);
236 END IF;
237
238 END get_pk_for_validation;
239
240
241 PROCEDURE before_dml (
242 p_action IN VARCHAR2,
243 x_rowid IN VARCHAR2,
244 x_object_version_number IN NUMBER,
245 x_stu_1098t_id IN NUMBER,
246 x_tax_year_name IN VARCHAR2,
247 x_party_id IN NUMBER,
248 x_extract_date IN DATE,
249 x_party_name IN VARCHAR2,
250 x_taxid IN VARCHAR2,
251 x_stu_name_control IN VARCHAR2,
252 x_country IN VARCHAR2,
253 x_address1 IN VARCHAR2,
254 x_address2 IN VARCHAR2,
255 x_refund_amt IN NUMBER,
256 x_half_time_flag IN VARCHAR2,
257 x_grad_flag IN VARCHAR2,
258 x_special_data_entry IN VARCHAR2,
259 x_status_code IN VARCHAR2,
260 x_error_code IN VARCHAR2,
261 x_file_name IN VARCHAR2,
262 x_irs_filed_flag IN VARCHAR2,
263 x_correction_flag IN VARCHAR2,
264 x_correction_type_code IN VARCHAR2,
265 x_stmnt_print_flag IN VARCHAR2,
266 x_override_flag IN VARCHAR2,
267 x_address3 IN VARCHAR2,
268 x_address4 IN VARCHAR2,
269 x_city IN VARCHAR2,
270 x_postal_code IN VARCHAR2,
271 x_state IN VARCHAR2,
272 x_province IN VARCHAR2,
273 x_county IN VARCHAR2,
274 x_delivery_point_code IN VARCHAR2,
275 x_payment_amt IN NUMBER,
276 x_billed_amt IN NUMBER,
277 x_adj_amt IN NUMBER,
278 x_fin_aid_amt IN NUMBER,
279 x_fin_aid_adj_amt IN NUMBER,
280 x_next_acad_flag IN VARCHAR2,
281 x_batch_id IN NUMBER,
282 x_creation_date IN DATE,
283 x_created_by IN NUMBER,
284 x_last_update_date IN DATE,
285 x_last_updated_by IN NUMBER,
286 x_last_update_login IN NUMBER
287 ) AS
288 /*
289 || Created By : [email protected]
290 || Created On : 01-MAY-2005
291 || Purpose : Initialises the columns, Checks Constraints, Calls the
292 || Trigger Handlers for the table, before any DML operation.
296 || (reverse chronological order - newest change first)
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
297 */
298 BEGIN
299
300 set_column_values (
301 p_action,
302 x_rowid,
303 x_object_version_number,
304 x_stu_1098t_id,
305 x_tax_year_name,
306 x_party_id,
307 x_extract_date,
308 x_party_name,
309 x_taxid,
310 x_stu_name_control,
311 x_country,
312 x_address1,
313 x_address2,
314 x_refund_amt,
315 x_half_time_flag,
316 x_grad_flag,
317 x_special_data_entry,
318 x_status_code,
319 x_error_code,
320 x_file_name,
321 x_irs_filed_flag,
322 x_correction_flag,
323 x_correction_type_code,
324 x_stmnt_print_flag,
325 x_override_flag,
326 x_address3,
327 x_address4,
328 x_city,
329 x_postal_code,
330 x_state,
331 x_province,
332 x_county,
333 x_delivery_point_code,
334 x_payment_amt,
335 x_billed_amt,
336 x_adj_amt,
337 x_fin_aid_amt,
338 x_fin_aid_adj_amt,
339 x_next_acad_flag,
340 x_batch_id,
341 x_creation_date,
342 x_created_by,
343 x_last_update_date,
344 x_last_updated_by,
345 x_last_update_login
346 );
347
348 IF (p_action = 'INSERT') THEN
349 -- Call all the procedures related to Before Insert.
353 ) THEN
350 IF ( get_pk_for_validation(
351 new_references.stu_1098t_id
352 )
354 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
355 igs_ge_msg_stack.add;
356 app_exception.raise_exception;
357 END IF;
358 check_parent_existance;
359 ELSIF (p_action = 'UPDATE') THEN
360 -- Call all the procedures related to Before Update.
361 check_parent_existance;
362 ELSIF (p_action = 'DELETE') THEN
363 -- Call all the procedures related to Before Delete.
364 check_child_existance;
365 ELSIF (p_action = 'VALIDATE_INSERT') THEN
366 -- Call all the procedures related to Before Insert.
367 IF ( get_pk_for_validation (
368 new_references.stu_1098t_id
369 )
370 ) THEN
371 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
372 igs_ge_msg_stack.add;
373 app_exception.raise_exception;
374 END IF;
375 ELSIF (p_action = 'VALIDATE_DELETE') THEN
376 check_child_existance;
377 END IF;
378
379 END before_dml;
380
381
382 PROCEDURE insert_row (
383 x_rowid IN OUT NOCOPY VARCHAR2,
384 x_stu_1098t_id IN OUT NOCOPY NUMBER,
385 x_tax_year_name IN VARCHAR2,
386 x_party_id IN NUMBER,
387 x_extract_date IN DATE,
388 x_party_name IN VARCHAR2,
389 x_taxid IN VARCHAR2,
390 x_stu_name_control IN VARCHAR2,
391 x_country IN VARCHAR2,
392 x_address1 IN VARCHAR2,
393 x_address2 IN VARCHAR2,
394 x_refund_amt IN NUMBER,
395 x_half_time_flag IN VARCHAR2,
396 x_grad_flag IN VARCHAR2,
397 x_special_data_entry IN VARCHAR2,
398 x_status_code IN VARCHAR2,
399 x_error_code IN VARCHAR2,
400 x_file_name IN VARCHAR2,
401 x_irs_filed_flag IN VARCHAR2,
402 x_correction_flag IN VARCHAR2,
403 x_correction_type_code IN VARCHAR2,
404 x_stmnt_print_flag IN VARCHAR2,
405 x_override_flag IN VARCHAR2,
406 x_address3 IN VARCHAR2,
407 x_address4 IN VARCHAR2,
408 x_city IN VARCHAR2,
409 x_postal_code IN VARCHAR2,
410 x_state IN VARCHAR2,
411 x_province IN VARCHAR2,
412 x_county IN VARCHAR2,
413 x_delivery_point_code IN VARCHAR2,
414 x_payment_amt IN NUMBER,
415 x_billed_amt IN NUMBER,
416 x_adj_amt IN NUMBER,
417 x_fin_aid_amt IN NUMBER,
418 x_fin_aid_adj_amt IN NUMBER,
419 x_next_acad_flag IN VARCHAR2,
420 x_batch_id IN NUMBER,
421 x_mode IN VARCHAR2
422 ) AS
423 /*
424 || Created By : [email protected]
425 || Created On : 01-MAY-2005
426 || Purpose : Handles the INSERT DML logic for the table.
427 || Known limitations, enhancements or remarks :
428 || Change History :
429 || Who When What
430 || (reverse chronological order - newest change first)
431 */
432
433 x_last_update_date DATE;
434 x_last_updated_by NUMBER;
435 x_last_update_login NUMBER;
436 x_request_id NUMBER;
437 x_program_id NUMBER;
438 x_program_application_id NUMBER;
439 x_program_update_date DATE;
440
441 BEGIN
442
443 x_last_update_date := SYSDATE;
444 IF (x_mode = 'I') THEN
445 x_last_updated_by := 1;
446 x_last_update_login := 0;
447 ELSIF (x_mode = 'R') THEN
448 x_last_updated_by := fnd_global.user_id;
449 IF (x_last_updated_by IS NULL) THEN
450 x_last_updated_by := -1;
451 END IF;
452 x_last_update_login := fnd_global.login_id;
453 IF (x_last_update_login IS NULL) THEN
454 x_last_update_login := -1;
455 END IF;
456 x_request_id := fnd_global.conc_request_id;
457 x_program_id := fnd_global.conc_program_id;
458 x_program_application_id := fnd_global.prog_appl_id;
459
460 IF (x_request_id = -1) THEN
461 x_request_id := NULL;
462 x_program_id := NULL;
463 x_program_application_id := NULL;
467 END IF;
464 x_program_update_date := NULL;
465 ELSE
466 x_program_update_date := SYSDATE;
468 ELSE
469 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
470 fnd_message.set_token ('ROUTINE', 'IGS_FI_1098T_DATA_PKG.INSERT_ROW');
471 igs_ge_msg_stack.add;
472 app_exception.raise_exception;
473 END IF;
474
475 x_stu_1098t_id := NULL;
476
477 before_dml(
478 p_action => 'INSERT',
479 x_rowid => x_rowid,
480 x_object_version_number => 1,
481 x_stu_1098t_id => x_stu_1098t_id,
482 x_tax_year_name => x_tax_year_name,
483 x_party_id => x_party_id,
484 x_extract_date => x_extract_date,
485 x_party_name => x_party_name,
486 x_taxid => x_taxid,
487 x_stu_name_control => x_stu_name_control,
488 x_country => x_country,
489 x_address1 => x_address1,
490 x_address2 => x_address2,
491 x_refund_amt => x_refund_amt,
492 x_half_time_flag => x_half_time_flag,
493 x_grad_flag => x_grad_flag,
494 x_special_data_entry => x_special_data_entry,
495 x_status_code => x_status_code,
496 x_error_code => x_error_code,
497 x_file_name => x_file_name,
498 x_irs_filed_flag => x_irs_filed_flag,
499 x_correction_flag => x_correction_flag,
500 x_correction_type_code => x_correction_type_code,
501 x_stmnt_print_flag => x_stmnt_print_flag,
502 x_override_flag => x_override_flag,
503 x_address3 => x_address3,
504 x_address4 => x_address4,
505 x_city => x_city,
506 x_postal_code => x_postal_code,
507 x_state => x_state,
508 x_province => x_province,
509 x_county => x_county,
510 x_delivery_point_code => x_delivery_point_code,
511 x_payment_amt => x_payment_amt,
512 x_billed_amt => x_billed_amt,
513 x_adj_amt => x_adj_amt,
514 x_fin_aid_amt => x_fin_aid_amt,
515 x_fin_aid_adj_amt => x_fin_aid_adj_amt,
516 x_next_acad_flag => x_next_acad_flag,
517 x_batch_id => x_batch_id,
518 x_creation_date => x_last_update_date,
519 x_created_by => x_last_updated_by,
520 x_last_update_date => x_last_update_date,
521 x_last_updated_by => x_last_updated_by,
522 x_last_update_login => x_last_update_login
523 );
524
525 INSERT INTO igs_fi_1098t_data (
526 object_version_number,
527 stu_1098t_id,
528 tax_year_name,
529 party_id,
530 extract_date,
531 party_name,
532 taxid,
533 stu_name_control,
534 country,
535 address1,
536 address2,
537 refund_amt,
538 half_time_flag,
539 grad_flag,
540 special_data_entry,
541 status_code,
542 error_code,
543 file_name,
544 irs_filed_flag,
545 correction_flag,
546 correction_type_code,
547 stmnt_print_flag,
548 override_flag,
549 address3,
550 address4,
551 city,
552 postal_code,
553 state,
554 province,
555 county,
556 delivery_point_code,
557 payment_amt,
558 billed_amt,
559 adj_amt,
560 fin_aid_amt,
561 fin_aid_adj_amt,
562 next_acad_flag,
563 batch_id,
564 creation_date,
565 created_by,
566 last_update_date,
567 last_updated_by,
568 last_update_login,
569 request_id,
570 program_id,
571 program_application_id,
572 program_update_date
573 ) VALUES (
574 new_references.object_version_number,
575 igs_fi_1098t_data_s.NEXTVAL,
576 new_references.tax_year_name,
577 new_references.party_id,
578 new_references.extract_date,
579 new_references.party_name,
580 new_references.taxid,
581 new_references.stu_name_control,
582 new_references.country,
583 new_references.address1,
584 new_references.address2,
585 new_references.refund_amt,
586 new_references.half_time_flag,
587 new_references.grad_flag,
588 new_references.special_data_entry,
589 new_references.status_code,
590 new_references.error_code,
591 new_references.file_name,
592 new_references.irs_filed_flag,
593 new_references.correction_flag,
594 new_references.correction_type_code,
595 new_references.stmnt_print_flag,
596 new_references.override_flag,
597 new_references.address3,
598 new_references.address4,
599 new_references.city,
600 new_references.postal_code,
601 new_references.state,
602 new_references.province,
603 new_references.county,
604 new_references.delivery_point_code,
605 new_references.payment_amt,
606 new_references.billed_amt,
607 new_references.adj_amt,
608 new_references.fin_aid_amt,
609 new_references.fin_aid_adj_amt,
610 new_references.next_acad_flag,
611 new_references.batch_id,
612 x_last_update_date,
613 x_last_updated_by,
614 x_last_update_date,
615 x_last_updated_by,
616 x_last_update_login ,
617 x_request_id,
618 x_program_id,
619 x_program_application_id,
620 x_program_update_date
621 ) RETURNING ROWID, stu_1098t_id INTO x_rowid, x_stu_1098t_id;
622
623 END insert_row;
624
625
626 PROCEDURE lock_row (
627 x_rowid IN VARCHAR2,
628 x_stu_1098t_id IN NUMBER,
629 x_tax_year_name IN VARCHAR2,
630 x_party_id IN NUMBER,
631 x_extract_date IN DATE,
632 x_party_name IN VARCHAR2,
633 x_taxid IN VARCHAR2,
634 x_stu_name_control IN VARCHAR2,
635 x_country IN VARCHAR2,
636 x_address1 IN VARCHAR2,
637 x_address2 IN VARCHAR2,
638 x_refund_amt IN NUMBER,
639 x_half_time_flag IN VARCHAR2,
640 x_grad_flag IN VARCHAR2,
641 x_special_data_entry IN VARCHAR2,
642 x_status_code IN VARCHAR2,
643 x_error_code IN VARCHAR2,
644 x_file_name IN VARCHAR2,
645 x_irs_filed_flag IN VARCHAR2,
646 x_correction_flag IN VARCHAR2,
647 x_correction_type_code IN VARCHAR2,
648 x_stmnt_print_flag IN VARCHAR2,
649 x_override_flag IN VARCHAR2,
650 x_address3 IN VARCHAR2,
651 x_address4 IN VARCHAR2,
652 x_city IN VARCHAR2,
653 x_postal_code IN VARCHAR2,
654 x_state IN VARCHAR2,
655 x_province IN VARCHAR2,
656 x_county IN VARCHAR2,
657 x_delivery_point_code IN VARCHAR2,
658 x_payment_amt IN NUMBER,
659 x_billed_amt IN NUMBER,
660 x_adj_amt IN NUMBER,
661 x_fin_aid_amt IN NUMBER,
662 x_fin_aid_adj_amt IN NUMBER,
663 x_next_acad_flag IN VARCHAR2,
664 x_batch_id IN NUMBER
665 ) AS
666 /*
667 || Created By : [email protected]
668 || Created On : 01-MAY-2005
669 || Purpose : Handles the LOCK mechanism for the table.
670 || Known limitations, enhancements or remarks :
671 || Change History :
672 || Who When What
673 || (reverse chronological order - newest change first)
674 */
675 CURSOR c1 IS
676 SELECT
677 object_version_number,
678 tax_year_name,
679 party_id,
680 extract_date,
681 party_name,
682 taxid,
683 stu_name_control,
684 country,
685 address1,
686 address2,
687 refund_amt,
688 half_time_flag,
689 grad_flag,
690 special_data_entry,
691 status_code,
692 error_code,
693 file_name,
694 irs_filed_flag,
695 correction_flag,
696 correction_type_code,
697 stmnt_print_flag,
698 override_flag,
699 address3,
700 address4,
701 city,
702 postal_code,
703 state,
704 province,
705 county,
706 delivery_point_code,
707 payment_amt,
708 billed_amt,
709 adj_amt,
710 fin_aid_amt,
711 fin_aid_adj_amt,
712 next_acad_flag,
713 batch_id
714 FROM igs_fi_1098t_data
715 WHERE rowid = x_rowid
716 FOR UPDATE NOWAIT;
717
718 tlinfo c1%ROWTYPE;
719
720 BEGIN
721
722 OPEN c1;
723 FETCH c1 INTO tlinfo;
724 IF (c1%notfound) THEN
725 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
726 igs_ge_msg_stack.add;
727 CLOSE c1;
728 app_exception.raise_exception;
729 RETURN;
730 END IF;
731 CLOSE c1;
732
733 IF ((tlinfo.tax_year_name = x_tax_year_name)
734 AND (tlinfo.party_id = x_party_id)
735 AND (tlinfo.extract_date = x_extract_date)
736 AND (tlinfo.party_name = x_party_name)
737 AND (tlinfo.taxid = x_taxid)
738 AND ((tlinfo.stu_name_control = x_stu_name_control) OR ((tlinfo.stu_name_control IS NULL) AND (X_stu_name_control IS NULL)))
739 AND (tlinfo.country = x_country)
740 AND ((tlinfo.address1 = x_address1) OR ((tlinfo.address1 IS NULL) AND (X_address1 IS NULL)))
741 AND ((tlinfo.address2 = x_address2) OR ((tlinfo.address2 IS NULL) AND (X_address2 IS NULL)))
742 AND (tlinfo.refund_amt = x_refund_amt)
743 AND (tlinfo.half_time_flag = x_half_time_flag)
744 AND (tlinfo.grad_flag = x_grad_flag)
745 AND ((tlinfo.special_data_entry = x_special_data_entry) OR ((tlinfo.special_data_entry IS NULL) AND (X_special_data_entry IS NULL)))
746 AND (tlinfo.status_code = x_status_code)
747 AND ((tlinfo.error_code = x_error_code) OR ((tlinfo.error_code IS NULL) AND (X_error_code IS NULL)))
748 AND ((tlinfo.file_name = x_file_name) OR ((tlinfo.file_name IS NULL) AND (X_file_name IS NULL)))
749 AND (tlinfo.irs_filed_flag = x_irs_filed_flag)
750 AND (tlinfo.correction_flag = x_correction_flag)
751 AND ((tlinfo.correction_type_code = x_correction_type_code) OR ((tlinfo.correction_type_code IS NULL) AND (X_correction_type_code IS NULL)))
752 AND (tlinfo.stmnt_print_flag = x_stmnt_print_flag)
753 AND (tlinfo.override_flag = x_override_flag)
754 AND ((tlinfo.address3 = x_address3) OR ((tlinfo.address3 IS NULL) AND (X_address3 IS NULL)))
755 AND ((tlinfo.address4 = x_address4) OR ((tlinfo.address4 IS NULL) AND (X_address4 IS NULL)))
756 AND (tlinfo.city = x_city)
757 AND (tlinfo.postal_code = x_postal_code)
758 AND (tlinfo.state = x_state)
759 AND ((tlinfo.province = x_province) OR ((tlinfo.province IS NULL) AND (X_province IS NULL)))
760 AND ((tlinfo.county = x_county) OR ((tlinfo.county IS NULL) AND (X_county IS NULL)))
761 AND ((tlinfo.delivery_point_code = x_delivery_point_code) OR ((tlinfo.delivery_point_code IS NULL) AND (X_delivery_point_code IS NULL)))
762 AND ((tlinfo.payment_amt = x_payment_amt) OR ((tlinfo.payment_amt IS NULL) AND (X_payment_amt IS NULL)))
763 AND (tlinfo.billed_amt = x_billed_amt)
764 AND (tlinfo.adj_amt = x_adj_amt)
765 AND (tlinfo.fin_aid_amt = x_fin_aid_amt)
766 AND (tlinfo.fin_aid_adj_amt = x_fin_aid_adj_amt)
767 AND (tlinfo.next_acad_flag = x_next_acad_flag)
768 AND ((tlinfo.batch_id = x_batch_id) OR ((tlinfo.batch_id IS NULL) AND (X_batch_id IS NULL)))
769 ) THEN
770 NULL;
771 ELSE
772 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
773 igs_ge_msg_stack.add;
774 app_exception.raise_exception;
775 END IF;
776
777 RETURN;
778
779 END lock_row;
780
781
782 PROCEDURE update_row (
783 x_rowid IN VARCHAR2,
784 x_stu_1098t_id IN NUMBER,
785 x_tax_year_name IN VARCHAR2,
786 x_party_id IN NUMBER,
787 x_extract_date IN DATE,
788 x_party_name IN VARCHAR2,
789 x_taxid IN VARCHAR2,
790 x_stu_name_control IN VARCHAR2,
791 x_country IN VARCHAR2,
792 x_address1 IN VARCHAR2,
793 x_address2 IN VARCHAR2,
794 x_refund_amt IN NUMBER,
795 x_half_time_flag IN VARCHAR2,
796 x_grad_flag IN VARCHAR2,
797 x_special_data_entry IN VARCHAR2,
798 x_status_code IN VARCHAR2,
799 x_error_code IN VARCHAR2,
800 x_file_name IN VARCHAR2,
801 x_irs_filed_flag IN VARCHAR2,
802 x_correction_flag IN VARCHAR2,
803 x_correction_type_code IN VARCHAR2,
804 x_stmnt_print_flag IN VARCHAR2,
805 x_override_flag IN VARCHAR2,
806 x_address3 IN VARCHAR2,
807 x_address4 IN VARCHAR2,
808 x_city IN VARCHAR2,
809 x_postal_code IN VARCHAR2,
810 x_state IN VARCHAR2,
811 x_province IN VARCHAR2,
812 x_county IN VARCHAR2,
813 x_delivery_point_code IN VARCHAR2,
814 x_payment_amt IN NUMBER,
815 x_billed_amt IN NUMBER,
816 x_adj_amt IN NUMBER,
817 x_fin_aid_amt IN NUMBER,
818 x_fin_aid_adj_amt IN NUMBER,
819 x_next_acad_flag IN VARCHAR2,
820 x_batch_id IN NUMBER,
821 x_mode IN VARCHAR2
822 ) AS
823 /*
824 || Created By : [email protected]
825 || Created On : 01-MAY-2005
826 || Purpose : Handles the UPDATE DML logic for the table.
827 || Known limitations, enhancements or remarks :
828 || Change History :
829 || Who When What
830 || (reverse chronological order - newest change first)
831 */
832 x_last_update_date DATE ;
833 x_last_updated_by NUMBER;
834 x_last_update_login NUMBER;
835 x_request_id NUMBER;
836 x_program_id NUMBER;
837 x_program_application_id NUMBER;
838 x_program_update_date DATE;
839
840 CURSOR cur_1098t_data(cp_rowid varchar2) IS
841 SELECT object_version_number
842 FROM igs_fi_1098t_data
843 WHERE rowid = cp_rowid
844 FOR UPDATE NOWAIT;
845
846 l_n_object_version_number igs_fi_1098t_data.object_version_number%TYPE;
847
848 BEGIN
849
850 OPEN cur_1098t_data(x_rowid);
851 FETCH cur_1098t_data INTO l_n_object_version_number;
852 CLOSE cur_1098t_data;
853
854 x_last_update_date := SYSDATE;
855 IF (X_MODE = 'I') THEN
856 x_last_updated_by := 1;
857 x_last_update_login := 0;
858 ELSIF (x_mode = 'R') THEN
859 x_last_updated_by := fnd_global.user_id;
860 IF x_last_updated_by IS NULL THEN
861 x_last_updated_by := -1;
862 END IF;
863 x_last_update_login := fnd_global.login_id;
864 IF (x_last_update_login IS NULL) THEN
865 x_last_update_login := -1;
866 END IF;
867 ELSE
868 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
869 fnd_message.set_token ('ROUTINE', 'IGS_FI_1098T_DATA_PKG.UPDATE_ROW');
870 igs_ge_msg_stack.add;
871 app_exception.raise_exception;
872 END IF;
873
874 l_n_object_version_number := l_n_object_version_number + 1;
875
876
877
878 before_dml(
879 p_action => 'UPDATE',
880 x_rowid => x_rowid,
881 x_object_version_number => l_n_object_version_number,
882 x_stu_1098t_id => x_stu_1098t_id,
883 x_tax_year_name => x_tax_year_name,
884 x_party_id => x_party_id,
885 x_extract_date => x_extract_date,
886 x_party_name => x_party_name,
887 x_taxid => x_taxid,
888 x_stu_name_control => x_stu_name_control,
889 x_country => x_country,
890 x_address1 => x_address1,
891 x_address2 => x_address2,
892 x_refund_amt => x_refund_amt,
893 x_half_time_flag => x_half_time_flag,
894 x_grad_flag => x_grad_flag,
895 x_special_data_entry => x_special_data_entry,
896 x_status_code => x_status_code,
897 x_error_code => x_error_code,
898 x_file_name => x_file_name,
899 x_irs_filed_flag => x_irs_filed_flag,
900 x_correction_flag => x_correction_flag,
901 x_correction_type_code => x_correction_type_code,
902 x_stmnt_print_flag => x_stmnt_print_flag,
903 x_override_flag => x_override_flag,
904 x_address3 => x_address3,
905 x_address4 => x_address4,
906 x_city => x_city,
907 x_postal_code => x_postal_code,
908 x_state => x_state,
909 x_province => x_province,
910 x_county => x_county,
911 x_delivery_point_code => x_delivery_point_code,
912 x_payment_amt => x_payment_amt,
913 x_billed_amt => x_billed_amt,
914 x_adj_amt => x_adj_amt,
915 x_fin_aid_amt => x_fin_aid_amt,
916 x_fin_aid_adj_amt => x_fin_aid_adj_amt,
917 x_next_acad_flag => x_next_acad_flag,
918 x_batch_id => x_batch_id,
919 x_creation_date => x_last_update_date,
920 x_created_by => x_last_updated_by,
921 x_last_update_date => x_last_update_date,
922 x_last_updated_by => x_last_updated_by,
923 x_last_update_login => x_last_update_login
924 );
925
926 IF (x_mode = 'R') THEN
927 x_request_id := fnd_global.conc_request_id;
928 x_program_id := fnd_global.conc_program_id;
929 x_program_application_id := fnd_global.prog_appl_id;
930 IF (x_request_id = -1) THEN
931 x_request_id := old_references.request_id;
932 x_program_id := old_references.program_id;
933 x_program_application_id := old_references.program_application_id;
934 x_program_update_date := old_references.program_update_date;
935 ELSE
936 x_program_update_date := SYSDATE;
937 END IF;
938 END IF;
939
940 UPDATE igs_fi_1098t_data
941 SET
942 object_version_number = new_references.object_version_number,
943 tax_year_name = new_references.tax_year_name,
944 party_id = new_references.party_id,
945 extract_date = new_references.extract_date,
946 party_name = new_references.party_name,
947 taxid = new_references.taxid,
948 stu_name_control = new_references.stu_name_control,
949 country = new_references.country,
950 address1 = new_references.address1,
951 address2 = new_references.address2,
952 refund_amt = new_references.refund_amt,
953 half_time_flag = new_references.half_time_flag,
954 grad_flag = new_references.grad_flag,
955 special_data_entry = new_references.special_data_entry,
956 status_code = new_references.status_code,
957 error_code = new_references.error_code,
958 file_name = new_references.file_name,
959 irs_filed_flag = new_references.irs_filed_flag,
960 correction_flag = new_references.correction_flag,
961 correction_type_code = new_references.correction_type_code,
962 stmnt_print_flag = new_references.stmnt_print_flag,
963 override_flag = new_references.override_flag,
964 address3 = new_references.address3,
965 address4 = new_references.address4,
966 city = new_references.city,
967 postal_code = new_references.postal_code,
968 state = new_references.state,
969 province = new_references.province,
970 county = new_references.county,
971 delivery_point_code = new_references.delivery_point_code,
972 payment_amt = new_references.payment_amt,
973 billed_amt = new_references.billed_amt,
974 adj_amt = new_references.adj_amt,
975 fin_aid_amt = new_references.fin_aid_amt,
976 fin_aid_adj_amt = new_references.fin_aid_adj_amt,
977 next_acad_flag = new_references.next_acad_flag,
978 batch_id = new_references.batch_id,
979 last_update_date = x_last_update_date,
980 last_updated_by = x_last_updated_by,
981 last_update_login = x_last_update_login ,
982 request_id = x_request_id,
983 program_id = x_program_id,
984 program_application_id = x_program_application_id,
985 program_update_date = x_program_update_date
986 WHERE rowid = x_rowid;
987
988 IF (SQL%NOTFOUND) THEN
989 RAISE NO_DATA_FOUND;
990 END IF;
991
992 END update_row;
993
994
995 PROCEDURE add_row (
996 x_rowid IN OUT NOCOPY VARCHAR2,
997 x_stu_1098t_id IN OUT NOCOPY NUMBER,
998 x_tax_year_name IN VARCHAR2,
999 x_party_id IN NUMBER,
1000 x_extract_date IN DATE,
1001 x_party_name IN VARCHAR2,
1002 x_taxid IN VARCHAR2,
1003 x_stu_name_control IN VARCHAR2,
1004 x_country IN VARCHAR2,
1005 x_address1 IN VARCHAR2,
1006 x_address2 IN VARCHAR2,
1007 x_refund_amt IN NUMBER,
1008 x_half_time_flag IN VARCHAR2,
1009 x_grad_flag IN VARCHAR2,
1010 x_special_data_entry IN VARCHAR2,
1011 x_status_code IN VARCHAR2,
1012 x_error_code IN VARCHAR2,
1013 x_file_name IN VARCHAR2,
1014 x_irs_filed_flag IN VARCHAR2,
1015 x_correction_flag IN VARCHAR2,
1016 x_correction_type_code IN VARCHAR2,
1017 x_stmnt_print_flag IN VARCHAR2,
1018 x_override_flag IN VARCHAR2,
1019 x_address3 IN VARCHAR2,
1020 x_address4 IN VARCHAR2,
1021 x_city IN VARCHAR2,
1022 x_postal_code IN VARCHAR2,
1023 x_state IN VARCHAR2,
1024 x_province IN VARCHAR2,
1025 x_county IN VARCHAR2,
1026 x_delivery_point_code IN VARCHAR2,
1027 x_payment_amt IN NUMBER,
1028 x_billed_amt IN NUMBER,
1029 x_adj_amt IN NUMBER,
1030 x_fin_aid_amt IN NUMBER,
1031 x_fin_aid_adj_amt IN NUMBER,
1032 x_next_acad_flag IN VARCHAR2,
1033 x_batch_id IN NUMBER,
1034 x_mode IN VARCHAR2
1035 ) AS
1036 /*
1037 || Created By : [email protected]
1038 || Created On : 01-MAY-2005
1039 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1040 || Known limitations, enhancements or remarks :
1041 || Change History :
1042 || Who When What
1043 || (reverse chronological order - newest change first)
1044 */
1045 CURSOR c1 IS
1046 SELECT rowid
1047 FROM igs_fi_1098t_data
1048 WHERE stu_1098t_id = x_stu_1098t_id;
1049
1050 BEGIN
1051
1052 OPEN c1;
1053 FETCH c1 INTO x_rowid;
1054 IF (c1%NOTFOUND) THEN
1055 CLOSE c1;
1056
1057 insert_row (
1058 x_rowid,
1059 x_stu_1098t_id,
1060 x_tax_year_name,
1061 x_party_id,
1062 x_extract_date,
1063 x_party_name,
1064 x_taxid,
1065 x_stu_name_control,
1066 x_country,
1067 x_address1,
1068 x_address2,
1069 x_refund_amt,
1070 x_half_time_flag,
1071 x_grad_flag,
1072 x_special_data_entry,
1073 x_status_code,
1074 x_error_code,
1075 x_file_name,
1076 x_irs_filed_flag,
1077 x_correction_flag,
1078 x_correction_type_code,
1079 x_stmnt_print_flag,
1080 x_override_flag,
1081 x_address3,
1082 x_address4,
1083 x_city,
1084 x_postal_code,
1085 x_state,
1086 x_province,
1087 x_county,
1088 x_delivery_point_code,
1089 x_payment_amt,
1090 x_billed_amt,
1091 x_adj_amt,
1092 x_fin_aid_amt,
1093 x_fin_aid_adj_amt,
1094 x_next_acad_flag,
1095 x_batch_id,
1096 x_mode
1097 );
1098 RETURN;
1099 END IF;
1100 CLOSE c1;
1101
1102 update_row (
1103 x_rowid,
1104 x_stu_1098t_id,
1105 x_tax_year_name,
1106 x_party_id,
1107 x_extract_date,
1108 x_party_name,
1109 x_taxid,
1110 x_stu_name_control,
1111 x_country,
1112 x_address1,
1113 x_address2,
1114 x_refund_amt,
1115 x_half_time_flag,
1116 x_grad_flag,
1117 x_special_data_entry,
1118 x_status_code,
1119 x_error_code,
1120 x_file_name,
1121 x_irs_filed_flag,
1122 x_correction_flag,
1123 x_correction_type_code,
1124 x_stmnt_print_flag,
1125 x_override_flag,
1126 x_address3,
1127 x_address4,
1128 x_city,
1129 x_postal_code,
1130 x_state,
1131 x_province,
1132 x_county,
1133 x_delivery_point_code,
1134 x_payment_amt,
1135 x_billed_amt,
1136 x_adj_amt,
1137 x_fin_aid_amt,
1138 x_fin_aid_adj_amt,
1139 x_next_acad_flag,
1140 x_batch_id,
1141 x_mode
1142 );
1143
1144 END add_row;
1145
1146
1147 PROCEDURE delete_row (
1148 x_rowid IN VARCHAR2
1149 ) AS
1150 /*
1151 || Created By : [email protected]
1152 || Created On : 01-MAY-2005
1153 || Purpose : Handles the DELETE DML logic for the table.
1154 || Known limitations, enhancements or remarks :
1155 || Change History :
1156 || Who When What
1157 || (reverse chronological order - newest change first)
1158 */
1159 BEGIN
1160
1161 before_dml (
1162 p_action => 'DELETE',
1163 x_rowid => x_rowid
1164 );
1165
1166 DELETE FROM igs_fi_1098t_data
1167 WHERE rowid = x_rowid;
1168
1169 IF (SQL%NOTFOUND) THEN
1170 RAISE NO_DATA_FOUND;
1171 END IF;
1172
1173 END delete_row;
1174
1175
1176 END igs_fi_1098t_data_pkg;