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