1 PACKAGE BODY igf_se_payment_pkg AS
2 /* $Header: IGFSI02B.pls 120.1 2005/10/06 05:34:22 appldev ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: IGF_SE_PAYMENT_PKG
10 | |
11 | NOTES |
12 | |
13 | This package has a flag on the end of some of the procedures called |
14 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
15 | This will control how the who columns are filled in; If you are |
16 | running in runtime mode, they are taken from the profiles, whereas in |
17 | install-time mode they get defaulted with special values to indicate |
18 | that they were inserted by datamerge. |
19 | |
20 | The ADD_ROW routine will see whether a row exists by selecting |
21 | based on the primary key, and updates the row if it exists, |
22 | or inserts the row if it doesn't already exist. |
23 | |
24 | This module is called by AutoInstall (afplss.drv) on install and |
25 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
26 | |
27 | HISTORY |
28 | who when what |
29 | veramach July 2004 FA 151 HR Integration |
30 | Obsoleted ld_cal_type,ld_sequence_number, |
31 | hrs_worked |
32 | Added check_constraints |
33 | brajendr 01-Jul-2002 Bug # 2415194 |
34 | Modified the Message IGF_SE_ERR_PAY_ADJ to |
35 | have the extra token to give more clarity |
36 | |
37 *=======================================================================*/
38
39 l_rowid VARCHAR2(25);
40 old_references igf_se_payment%ROWTYPE;
41 new_references igf_se_payment%ROWTYPE;
42
43 PROCEDURE set_column_values (
44 p_action IN VARCHAR2,
45 x_rowid IN VARCHAR2,
46 x_transaction_id IN NUMBER ,
47 x_payroll_id IN NUMBER ,
48 x_payroll_date IN DATE ,
49 x_auth_id IN NUMBER ,
50 x_person_id IN NUMBER ,
51 x_fund_id IN NUMBER ,
52 x_paid_amount IN NUMBER ,
53 x_org_unit_cd IN VARCHAR2,
54 x_source IN VARCHAR2,
55 x_creation_date IN DATE ,
56 x_created_by IN NUMBER ,
57 x_last_update_date IN DATE ,
58 x_last_updated_by IN NUMBER ,
59 x_last_update_login IN NUMBER
60 ) AS
61 /*
62 || Created By : ssawhney
63 || Created On : 31-DEC-2001
64 || Purpose : Initialises the Old and New references for the columns of the table.
65 || Known limitations, enhancements or remarks :
66 || Change History :
67 || Who When What
68 || (reverse chronological order - newest change first)
69 */
70
71 CURSOR cur_old_ref_values IS
72 SELECT *
73 FROM IGF_SE_PAYMENT
74 WHERE rowid = x_rowid;
75
76 BEGIN
77
78 l_rowid := x_rowid;
79
80 -- Code for setting the Old and New Reference Values.
81 -- Populate Old Values.
82 OPEN cur_old_ref_values;
83 FETCH cur_old_ref_values INTO old_references;
84 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
85 CLOSE cur_old_ref_values;
86 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
87
88 igs_ge_msg_stack.add;
89 app_exception.raise_exception;
90 RETURN;
91 END IF;
92 CLOSE cur_old_ref_values;
93
94 -- Populate New Values.
95 new_references.transaction_id := x_transaction_id;
96 new_references.payroll_id := x_payroll_id;
97 new_references.payroll_date := x_payroll_date;
98 new_references.auth_id := x_auth_id;
99 new_references.person_id := x_person_id;
100 new_references.fund_id := x_fund_id;
101 new_references.paid_amount := x_paid_amount;
102 new_references.org_unit_cd := x_org_unit_cd;
103 new_references.source := x_source;
104
105 IF (p_action = 'UPDATE') THEN
106 new_references.creation_date := old_references.creation_date;
107 new_references.created_by := old_references.created_by;
108 ELSE
109 new_references.creation_date := x_creation_date;
110 new_references.created_by := x_created_by;
111 END IF;
112
113 new_references.last_update_date := x_last_update_date;
114 new_references.last_updated_by := x_last_updated_by;
115 new_references.last_update_login := x_last_update_login;
116
117 END set_column_values;
118
119
120 PROCEDURE check_parent_existance AS
121 /*
122 || Created By : ssawhney
123 || Created On : 31-DEC-2001
124 || Purpose : Checks for the existance of Parent records.
125 || Known limitations, enhancements or remarks :
126 || Change History :
127 || Who When What
128 || (reverse chronological order - newest change first)
129 */
130 BEGIN
131
132 IF NOT igs_pe_person_pkg.get_pk_for_validation (
133 new_references.person_id
134 ) THEN
135 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 END IF;
139
140 END check_parent_existance;
141
142
143 FUNCTION get_pk_for_validation (
144 x_transaction_id IN NUMBER
145 ) RETURN BOOLEAN AS
146 /*
147 || Created By : ssawhney
148 || Created On : 31-DEC-2001
149 || Purpose : Validates the Primary Key of the table.
150 || Known limitations, enhancements or remarks :
151 || Change History :
152 || Who When What
153 || (reverse chronological order - newest change first)
154 */
155 CURSOR cur_rowid IS
156 SELECT rowid
157 FROM igf_se_payment
158 WHERE transaction_id = x_transaction_id
159 FOR UPDATE NOWAIT;
160
161 lv_rowid cur_rowid%RowType;
162
163 BEGIN
164
165 OPEN cur_rowid;
166
167 FETCH cur_rowid INTO lv_rowid;
168
169 IF (cur_rowid%FOUND) THEN
170 CLOSE cur_rowid;
171
172 RETURN(TRUE);
173 ELSE
174 CLOSE cur_rowid;
175
176 RETURN(FALSE);
177 END IF;
178
179 END get_pk_for_validation;
180
181
182 PROCEDURE get_fk_hz_parties (
183 x_party_id IN NUMBER
184 ) AS
185 /*
186 || Created By : ssawhney
187 || Created On : 31-DEC-2001
188 || Purpose : Validates the Foreign Keys for the table.
189 || NOTE : THIS WILL NOT GET EXECUTED AS IGS DOESNT CALL THIS CODE
190 || Known limitations, enhancements or remarks :
191 || Change History :
192 || Bug : 2413695
193 || Desc :IGF Messages Issues
194 || Who When What
195 || mesriniv 14-jun-2002 message name added
196 || (reverse chronological order - newest change first)
197 */
198 CURSOR cur_rowid IS
199 SELECT rowid
200 FROM igf_se_payment
201 WHERE ((person_id = x_party_id));
202
203 lv_rowid cur_rowid%RowType;
204
205 BEGIN
206
207 OPEN cur_rowid;
208 FETCH cur_rowid INTO lv_rowid;
209 IF (cur_rowid%FOUND) THEN
210 CLOSE cur_rowid;
211 fnd_message.set_name ('IGF', 'IGF_SE_SEP_HP_FK');
212 igs_ge_msg_stack.add;
213 app_exception.raise_exception;
214 RETURN;
215 END IF;
216 CLOSE cur_rowid;
217
218 END get_fk_hz_parties;
219
220 PROCEDURE check_constraints AS
221 ------------------------------------------------------------------
222 --Created by : veramach, Oracle India
223 --Date created: 27-Jul-2004
224 --
225 --Purpose:
226 --
227 --
228 --Known limitations/enhancements and/or remarks:
229 --
230 --Change History:
231 --Who When What
232 -------------------------------------------------------------------
233
234 -- Get authorization id
235 CURSOR c_auth(
236 cp_auth_id igf_se_payment.auth_id%TYPE,
237 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE
238 ) IS
239 SELECT 'x'
240 FROM igf_se_auth
241 WHERE auth_id = cp_auth_id
242 AND person_id = cp_person_id
243 AND flag = 'A';
244 l_auth c_auth%ROWTYPE;
245
246 -- Get fund id
247 CURSOR c_fund(
248 cp_auth_id igf_se_payment.auth_id%TYPE,
249 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE
250 ) IS
251 SELECT auth.fund_id
252 FROM igf_se_auth auth
253 WHERE auth_id = cp_auth_id
254 AND person_id = cp_person_id;
255 l_fund igf_aw_fund_mast_all.fund_id%TYPE;
256
257 -- Get lookup meaning
258 CURSOR c_lookup(
259 cp_lookup_type igf_lookups_view.lookup_type%TYPE,
260 cp_lookup_code igf_lookups_view.lookup_code%TYPE
261 ) IS
262 SELECT 'x'
263 FROM igf_lookups_view
264 WHERE lookup_type = cp_lookup_type
265 AND lookup_code = cp_lookup_code
266 AND enabled_flag = 'Y';
267 l_lookup c_lookup%ROWTYPE;
268
269 CURSOR c_pers_num(
270 cp_person_id hz_parties.party_id%TYPE
271 ) IS
272 SELECT party_number
273 FROM hz_parties
274 WHERE party_id = cp_person_id;
275 l_pers_num hz_parties.party_number%TYPE;
276
277 BEGIN
278
279 IF new_references.payroll_date IS NULL THEN
280 fnd_message.set_name('IGF','IGF_SE_PAY_DT_INVALID');
281 igs_ge_msg_stack.add;
282 app_exception.raise_exception;
283 END IF;
284
285 IF new_references.auth_id IS NULL THEN
286 fnd_message.set_name('IGF','IGF_SE_AUTH_ID_NULL');
287 igs_ge_msg_stack.add;
288 app_exception.raise_exception;
289 END IF;
290
291 IF new_references.person_id IS NULL THEN
292 fnd_message.set_name('IGF','IGF_SE_PERSON_ID_NULL');
293 igs_ge_msg_stack.add;
294 app_exception.raise_exception;
295 END IF;
296
297 IF new_references.paid_amount IS NULL THEN
298 fnd_message.set_name('IGF','IGF_SE_PAID_AMT_NULL');
299 igs_ge_msg_stack.add;
300 app_exception.raise_exception;
301 END IF;
302
303 IF new_references.source IS NULL THEN
304 fnd_message.set_name('IGF','IGF_SE_SOURCE_INVALID');
305 igs_ge_msg_stack.add;
306 app_exception.raise_exception;
307 END IF;
308
309 OPEN c_auth(new_references.auth_id,new_references.person_id);
310 FETCH c_auth INTO l_auth;
311 IF c_auth%NOTFOUND THEN
312 CLOSE c_auth;
313 fnd_message.set_name('IGF','IGF_SE_INV_AUTH_PERSON');
314 fnd_message.set_token('AUTH_ID',TO_CHAR(new_references.auth_id));
315
316 l_pers_num := NULL;
317 OPEN c_pers_num(new_references.person_id);
318 FETCH c_pers_num INTO l_pers_num;
319 CLOSE c_pers_num;
320 fnd_message.set_token('PERSON_NUM',l_pers_num);
321 igs_ge_msg_stack.add;
322 app_exception.raise_exception;
323 END IF;
324 CLOSE c_auth;
325
326 l_fund := NULL;
327 OPEN c_fund(new_references.auth_id,new_references.person_id);
328 FETCH c_fund INTO l_fund;
329 CLOSE c_fund;
330 IF l_fund IS NULL THEN
331 fnd_message.set_name('IGF','IGF_SE_NO_VALID_FUND');
332 l_pers_num := NULL;
333 OPEN c_pers_num(new_references.person_id);
334 FETCH c_pers_num INTO l_pers_num;
335 CLOSE c_pers_num;
336 fnd_message.set_token('PERSON_NUM',l_pers_num);
337 igs_ge_msg_stack.add;
338 app_exception.raise_exception;
339 END IF;
340
341 OPEN c_lookup('IGF_SE_SOURCE',new_references.source);
342 FETCH c_lookup INTO l_lookup;
343 IF c_lookup%NOTFOUND THEN
344 CLOSE c_lookup;
345 fnd_message.set_name('IGF','IGF_SE_SOURCE_INVALID');
346 igs_ge_msg_stack.add;
347 app_exception.raise_exception;
348 END IF;
349
350 END check_constraints;
351
352 PROCEDURE before_dml (
353 p_action IN VARCHAR2,
354 x_rowid IN VARCHAR2,
355 x_transaction_id IN NUMBER ,
356 x_payroll_id IN NUMBER ,
357 x_payroll_date IN DATE ,
358 x_auth_id IN NUMBER ,
359 x_person_id IN NUMBER ,
360 x_fund_id IN NUMBER ,
361 x_paid_amount IN NUMBER ,
362 x_org_unit_cd IN VARCHAR2,
363 x_source IN VARCHAR2,
364 x_creation_date IN DATE ,
365 x_created_by IN NUMBER ,
366 x_last_update_date IN DATE ,
367 x_last_updated_by IN NUMBER ,
368 x_last_update_login IN NUMBER
369 ) AS
370 /*
371 || Created By : ssawhney
372 || Created On : 31-DEC-2001
373 || Purpose : Initialises the columns, Checks Constraints, Calls the
374 || Trigger Handlers for the table, before any DML operation.
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 */
380 BEGIN
381
382 set_column_values (
383 p_action,
384 x_rowid,
385 x_transaction_id,
386 x_payroll_id,
387 x_payroll_date,
388 x_auth_id,
389 x_person_id,
390 x_fund_id,
391 x_paid_amount,
392 x_org_unit_cd,
393 x_source,
394 x_creation_date,
395 x_created_by,
396 x_last_update_date,
397 x_last_updated_by,
398 x_last_update_login
399 );
400
401
402 IF (p_action = 'INSERT') THEN
403 -- Call all the procedures related to Before Insert.
404
405 IF ( get_pk_for_validation(
406 new_references.transaction_id
407 )
408 ) THEN
409
410 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
411 igs_ge_msg_stack.add;
412 app_exception.raise_exception;
413 END IF;
414 check_parent_existance;
415 check_constraints;
416 ELSIF (p_action = 'UPDATE') THEN
417 -- Call all the procedures related to Before Update.
418 check_parent_existance;
419 check_constraints;
420 ELSIF (p_action = 'VALIDATE_INSERT') THEN
421 -- Call all the procedures related to Before Insert.
422 IF ( get_pk_for_validation (
423 new_references.transaction_id
424 )
425 ) THEN
426
427 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
428 igs_ge_msg_stack.add;
429 app_exception.raise_exception;
430 END IF;
431 END IF;
432
433 END before_dml;
434
435 PROCEDURE insert_row (
436 x_rowid IN OUT NOCOPY VARCHAR2,
437 x_transaction_id IN OUT NOCOPY NUMBER,
438 x_payroll_id IN NUMBER,
439 x_payroll_date IN DATE,
440 x_auth_id IN NUMBER,
441 x_person_id IN NUMBER,
442 x_fund_id IN NUMBER,
443 x_paid_amount IN NUMBER,
444 x_org_unit_cd IN VARCHAR2,
445 x_source IN VARCHAR2,
446 x_mode IN VARCHAR2
447 ) AS
448 /*
449 || Created By : ssawhney
450 || Created On : 31-DEC-2001
451 || Purpose : Handles the INSERT DML logic for the table.
452 || Known limitations, enhancements or remarks :
453 || Change History :
454 || Who When What
455 || (reverse chronological order - newest change first)
456 */
457 CURSOR c IS
458 SELECT rowid
459 FROM igf_se_payment
460 WHERE transaction_id = x_transaction_id;
461
462 CURSOR c_payment_int IS
463 SELECT rowid , spi.*
464 FROM igf_se_payment_int spi
465 WHERE spi.auth_id =x_auth_id AND
466 spi.payroll_id =x_payroll_id AND
467 spi.status NOT IN ('DONE','ERROR');
468
469 CURSOR c_get_se_errors(
470 c_error_cd igf_se_payment_int.error_code%TYPE
471 ) IS
472 SELECT meaning
473 FROM igf_lookups_view
474 WHERE lookup_type = 'IGF_STUD_EMP_ERROR'
475 AND lookup_code = c_error_cd;
476
477 payment_int_rec c_payment_int%ROWTYPE;
478
479
480 x_last_update_date DATE;
481 x_last_updated_by NUMBER;
482 x_last_update_login NUMBER;
483 x_request_id NUMBER;
484 x_program_id NUMBER;
485 x_program_application_id NUMBER;
486 x_program_update_date DATE;
487 l_status igf_se_payment_int.status%TYPE;
488 l_error igf_se_payment_int.error_code%TYPE;
489 l_error_meaming igf_lookups_view.meaning%TYPE;
490 my_exception EXCEPTION;
491
492 BEGIN
493
494 x_last_update_date := SYSDATE;
495 IF (x_mode = 'I') THEN
496 x_last_updated_by := 1;
497 x_last_update_login := 0;
498 ELSIF (x_mode = 'R') THEN
499 x_last_updated_by := fnd_global.user_id;
500 IF (x_last_updated_by IS NULL) THEN
501 x_last_updated_by := -1;
502 END IF;
503 x_last_update_login := fnd_global.login_id;
504 IF (x_last_update_login IS NULL) THEN
505 x_last_update_login := -1;
506 END IF;
507 x_request_id := fnd_global.conc_request_id;
508 x_program_id := fnd_global.conc_program_id;
509 x_program_application_id := fnd_global.prog_appl_id;
510
511 IF (x_request_id = -1) THEN
512 x_request_id := NULL;
513 x_program_id := NULL;
514 x_program_application_id := NULL;
515 x_program_update_date := NULL;
516 ELSE
517 x_program_update_date := SYSDATE;
518 END IF;
519 ELSE
520 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
521 igs_ge_msg_stack.add;
522 app_exception.raise_exception;
523 END IF;
524
525 SELECT igf_se_payment_s.NEXTVAL
526 INTO x_transaction_id
527 FROM dual;
528
529
530 before_dml(
531 p_action => 'INSERT',
532 x_rowid => x_rowid,
533 x_transaction_id => x_transaction_id,
534 x_payroll_id => x_payroll_id,
535 x_payroll_date => x_payroll_date,
536 x_auth_id => x_auth_id,
537 x_person_id => x_person_id,
538 x_fund_id => x_fund_id,
539 x_paid_amount => x_paid_amount,
540 x_org_unit_cd => x_org_unit_cd,
541 x_source => x_source,
542 x_creation_date => x_last_update_date,
543 x_created_by => x_last_updated_by,
544 x_last_update_date => x_last_update_date,
545 x_last_updated_by => x_last_updated_by,
546 x_last_update_login => x_last_update_login
547 );
548
549 -- issue a savepoint
550 SAVEPOINT se_payment;
551
552 INSERT INTO igf_se_payment (
553 transaction_id,
554 payroll_id,
555 payroll_date,
556 auth_id,
557 person_id,
558 fund_id,
559 paid_amount,
560 org_unit_cd,
561 source,
562 creation_date,
563 created_by,
564 last_update_date,
565 last_updated_by,
566 last_update_login,
567 request_id,
568 program_id,
569 program_application_id,
570 program_update_date
571 ) VALUES (
572 new_references.transaction_id,
573 new_references.payroll_id,
574 new_references.payroll_date,
575 new_references.auth_id,
576 new_references.person_id,
577 new_references.fund_id,
578 new_references.paid_amount,
579 new_references.org_unit_cd,
580 new_references.source,
581 x_last_update_date,
582 x_last_updated_by,
583 x_last_update_date,
584 x_last_updated_by,
585 x_last_update_login ,
586 x_request_id,
587 x_program_id,
588 x_program_application_id,
589 x_program_update_date
590 );
591
592 OPEN c;
593 FETCH c INTO x_rowid;
594 IF (c%NOTFOUND) THEN
595 CLOSE c;
596 RAISE NO_DATA_FOUND;
597 END IF;
598 CLOSE c;
599
600 -- specific code for the logic of this table.
601 -- make a call for adjustment to IGF_AW_AWD_DISB table
602
603 BEGIN
604
605 igf_se_gen_001.payroll_adjust(
606 new_references,
607 l_status,
608 l_error
609 );
610
611
612
613 IF l_status ='DONE' THEN -- that is its a success
614
615 IF new_references.payroll_id IS NOT NULL THEN
616
617 -- this means that the record is coming from IGF_SE_PAYMENT_INT table
618 -- update payment int table with the error code
619 OPEN c_payment_int;
620 FETCH c_payment_int INTO payment_int_rec;
621 IF c_payment_int%FOUND THEN
622 CLOSE c_payment_int;
623
624 BEGIN
625 igf_se_payment_int_pkg.update_row (
626 x_rowid => payment_int_rec.rowid,
627 x_transaction_id => payment_int_rec.transaction_id,
628 x_batch_id => payment_int_rec.batch_id,
629 x_payroll_id => payment_int_rec.payroll_id,
630 x_payroll_date => payment_int_rec.payroll_date,
631 x_auth_id => payment_int_rec.auth_id,
632 x_person_id => payment_int_rec.person_id,
633 x_fund_id => payment_int_rec.fund_id,
634 x_paid_amount => payment_int_rec.paid_amount,
635 x_org_unit_cd => payment_int_rec.org_unit_cd,
636 x_status => l_status,
637 x_error_code => l_error
638 );
639 EXCEPTION
640 WHEN OTHERS THEN
641 fnd_message.set_name('IGF','IGF_SE_ERR_PAYINT_UPD');
642 igs_ge_msg_stack.add;
643 app_exception.raise_exception;
644 END;
645
646 ELSE
647 CLOSE c_payment_int;
648 END IF;
649
650 END IF; -- new payroll if
651
652 ELSIF l_status = 'ERROR' THEN
653 raise my_exception;
654 END IF;
655
656 -- if adjusment is successful then do NULL , return stat would be DONE else
657 -- rollback the transaction and update the PAYMENT_INT table for error.
658 EXCEPTION
659
660 WHEN my_exception THEN
661 IF new_references.payroll_id IS NOT NULL THEN
662
663 -- this means that the record is coming from IGF_SE_PAYMENT_INT table
664 -- update payment int table with the error code
665 OPEN c_payment_int;
666 FETCH c_payment_int INTO payment_int_rec;
667 IF c_payment_int%FOUND THEN
668 CLOSE c_payment_int;
669
670 -- rollback the transaction insert into PAYMENT.
671 -- update the int table
672
673 ROLLBACK TO se_payment;
674
675 BEGIN
676 igf_se_payment_int_pkg.update_row (
677 x_rowid => payment_int_rec.rowid,
678 x_transaction_id => payment_int_rec.transaction_id,
679 x_batch_id => payment_int_rec.batch_id,
680 x_payroll_id => payment_int_rec.payroll_id,
681 x_payroll_date => payment_int_rec.payroll_date,
682 x_auth_id => payment_int_rec.auth_id,
683 x_person_id => payment_int_rec.person_id,
684 x_fund_id => payment_int_rec.fund_id,
685 x_paid_amount => payment_int_rec.paid_amount,
686 x_org_unit_cd => payment_int_rec.org_unit_cd,
687 x_status => l_status,
688 x_error_code => l_error
689 );
690 EXCEPTION
691 WHEN OTHERS THEN
692
693 fnd_message.set_name('IGF','IGF_SE_ERR_PAYINT_UPD');
694 igs_ge_msg_stack.add;
695 app_exception.raise_exception;
696 END;
697
698 ELSE
699 CLOSE c_payment_int;
700 ROLLBACK TO se_payment;
701 END IF;
702 ELSIF new_references.payroll_id IS NULL THEN
703 -- this means that the record is being entered from the screen.
704
705 ROLLBACK TO se_payment;
706
707 -- Get the error code meaning from the lookups and show the exact error to the user
708 OPEN c_get_se_errors( l_error);
709 FETCH c_get_se_errors INTO l_error_meaming;
710 CLOSE c_get_se_errors;
711
712 FND_MESSAGE.SET_NAME('IGF', 'IGF_SE_ERR_PAY_ADJ');
713 FND_MESSAGE.SET_TOKEN('ERROR',l_error_meaming);
714 igs_ge_msg_stack.add;
715 app_exception.raise_exception;
716
717
718 END IF;
719 END; -- exception of calling adjustment
720
721 END insert_row;
722
723
724 PROCEDURE lock_row (
725 x_rowid IN VARCHAR2,
726 x_transaction_id IN NUMBER,
727 x_payroll_id IN NUMBER,
728 x_payroll_date IN DATE,
729 x_auth_id IN NUMBER,
730 x_person_id IN NUMBER,
731 x_fund_id IN NUMBER,
732 x_paid_amount IN NUMBER,
733 x_org_unit_cd IN VARCHAR2,
734 x_source IN VARCHAR2
735 ) AS
736 /*
737 || Created By : ssawhney
738 || Created On : 31-DEC-2001
739 || Purpose : Handles the LOCK mechanism for the table.
740 || Known limitations, enhancements or remarks :
741 || Change History :
742 || Who When What
743 || (reverse chronological order - newest change first)
744 */
745 CURSOR c1 IS
746 SELECT
747 payroll_id,
748 payroll_date,
749 auth_id,
750 person_id,
751 fund_id,
752 paid_amount,
753 org_unit_cd,
754 source
755 FROM igf_se_payment
756 WHERE rowid = x_rowid
757 FOR UPDATE NOWAIT;
758
759 tlinfo c1%ROWTYPE;
760
761 BEGIN
762
763 OPEN c1;
764 FETCH c1 INTO tlinfo;
765 IF (c1%notfound) THEN
766 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
767 igs_ge_msg_stack.add;
768 CLOSE c1;
769 app_exception.raise_exception;
770 RETURN;
771 END IF;
772 CLOSE c1;
773
774 IF (
775 ((tlinfo.payroll_id = x_payroll_id) OR ((tlinfo.payroll_id IS NULL) AND (X_payroll_id IS NULL)))
776 AND (tlinfo.payroll_date = x_payroll_date)
777 AND (tlinfo.auth_id = x_auth_id)
778 AND (tlinfo.person_id = x_person_id)
779 AND (tlinfo.fund_id = x_fund_id)
780 AND (tlinfo.paid_amount = x_paid_amount)
781 AND ((tlinfo.org_unit_cd = x_org_unit_cd) OR ((tlinfo.org_unit_cd IS NULL) AND (X_org_unit_cd IS NULL)))
782 AND ((tlinfo.source = x_source) OR ((tlinfo.source IS NULL) AND (X_source IS NULL)))
783 ) THEN
784 NULL;
785 ELSE
786 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
787 igs_ge_msg_stack.add;
788 app_exception.raise_exception;
789 END IF;
790
791 RETURN;
792
793 END lock_row;
794
795
796 PROCEDURE update_row (
797 x_rowid IN VARCHAR2,
798 x_transaction_id IN NUMBER,
799 x_payroll_id IN NUMBER,
800 x_payroll_date IN DATE,
801 x_auth_id IN NUMBER,
802 x_person_id IN NUMBER,
803 x_fund_id IN NUMBER,
804 x_paid_amount IN NUMBER,
805 x_org_unit_cd IN VARCHAR2,
806 x_source IN VARCHAR2,
807 x_mode IN VARCHAR2
808 ) AS
809 /*
810 || Created By : ssawhney
811 || Created On : 31-DEC-2001
812 || Purpose : Handles the UPDATE DML logic for the table.
813 || Known limitations, enhancements or remarks :
814 || Change History :
815 || Who When What
816 || (reverse chronological order - newest change first)
817 */
818
819 CURSOR c_get_se_errors(
820 c_error_cd igf_se_payment_int.error_code%TYPE
821 ) IS
822 SELECT meaning
823 FROM igf_lookups_view
824 WHERE lookup_type = 'IGF_STUD_EMP_ERROR'
825 AND lookup_code = c_error_cd;
826
827 x_last_update_date DATE ;
828 x_last_updated_by NUMBER;
829 x_last_update_login NUMBER;
830 x_request_id NUMBER;
831 x_program_id NUMBER;
832 x_program_application_id NUMBER;
833 x_program_update_date DATE;
834 l_status igf_se_payment_int.status%TYPE;
835 l_error igf_se_payment_int.error_code%TYPE;
836 l_error_meaming igf_lookups_view.meaning%TYPE;
837 my_exception EXCEPTION;
838
839 CURSOR c_payment_int IS
840 SELECT rowid , spi.*
841 FROM igf_se_payment_int spi
842 WHERE spi.auth_id =x_auth_id AND
843 spi.payroll_id =x_payroll_id AND
844 spi.status NOT IN ('DONE','ERROR');
845
846 payment_int_rec c_payment_int%ROWTYPE;
847
848
849 BEGIN
850
851 x_last_update_date := SYSDATE;
852 IF (X_MODE = 'I') THEN
853 x_last_updated_by := 1;
854 x_last_update_login := 0;
855 ELSIF (x_mode = 'R') THEN
856 x_last_updated_by := fnd_global.user_id;
857 IF x_last_updated_by IS NULL THEN
858 x_last_updated_by := -1;
859 END IF;
860 x_last_update_login := fnd_global.login_id;
861 IF (x_last_update_login IS NULL) THEN
862 x_last_update_login := -1;
863 END IF;
864 ELSE
865 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
866 igs_ge_msg_stack.add;
867 app_exception.raise_exception;
868 END IF;
869
870 before_dml(
871 p_action => 'UPDATE',
872 x_rowid => x_rowid,
873 x_transaction_id => x_transaction_id,
874 x_payroll_id => x_payroll_id,
875 x_payroll_date => x_payroll_date,
876 x_auth_id => x_auth_id,
877 x_person_id => x_person_id,
878 x_fund_id => x_fund_id,
879 x_paid_amount => x_paid_amount,
880 x_org_unit_cd => x_org_unit_cd,
881 x_source => x_source,
882 x_creation_date => x_last_update_date,
883 x_created_by => x_last_updated_by,
884 x_last_update_date => x_last_update_date,
885 x_last_updated_by => x_last_updated_by,
886 x_last_update_login => x_last_update_login
887 );
888
889 IF (x_mode = 'R') THEN
890 x_request_id := fnd_global.conc_request_id;
891 x_program_id := fnd_global.conc_program_id;
892 x_program_application_id := fnd_global.prog_appl_id;
893 IF (x_request_id = -1) THEN
894 x_request_id := old_references.request_id;
895 x_program_id := old_references.program_id;
896 x_program_application_id := old_references.program_application_id;
897 x_program_update_date := old_references.program_update_date;
898 ELSE
899 x_program_update_date := SYSDATE;
900 END IF;
901 END IF;
902
903 -- issue a savepoint
904
905 SAVEPOINT se_payment_upd;
906
907 UPDATE igf_se_payment
908 SET
909 payroll_id = new_references.payroll_id,
910 payroll_date = new_references.payroll_date,
911 auth_id = new_references.auth_id,
912 person_id = new_references.person_id,
913 fund_id = new_references.fund_id,
914 paid_amount = new_references.paid_amount,
915 org_unit_cd = new_references.org_unit_cd,
916 source = new_references.source,
917 last_update_date = x_last_update_date,
918 last_updated_by = x_last_updated_by,
919 last_update_login = x_last_update_login ,
920 request_id = x_request_id,
921 program_id = x_program_id,
922 program_application_id = x_program_application_id,
923 program_update_date = x_program_update_date
924 WHERE rowid = x_rowid;
925
926 IF (SQL%NOTFOUND) THEN
927 RAISE NO_DATA_FOUND;
928 END IF;
929
930 BEGIN
931 -- make a call for adjustment to IGF_AW_AWD_DISB table
932 igf_se_gen_001.payroll_adjust(
933 new_references,
934 l_status,
935 l_error
936 );
937
938 IF l_status ='DONE' THEN -- that is its a success
939
940 IF new_references.payroll_id IS NOT NULL THEN
941
942 -- this means that the record is coming from IGF_SE_PAYMENT_INT table
943 -- update payment int table with the error code
944 OPEN c_payment_int;
945 FETCH c_payment_int INTO payment_int_rec;
946 IF c_payment_int%FOUND THEN
947 CLOSE c_payment_int;
948
949 BEGIN
950 igf_se_payment_int_pkg.update_row(
951 x_rowid => payment_int_rec.rowid,
952 x_transaction_id => payment_int_rec.transaction_id,
953 x_batch_id => payment_int_rec.batch_id,
954 x_payroll_id => payment_int_rec.payroll_id,
955 x_payroll_date => payment_int_rec.payroll_date,
956 x_auth_id => payment_int_rec.auth_id,
957 x_person_id => payment_int_rec.person_id,
958 x_fund_id => payment_int_rec.fund_id,
959 x_paid_amount => payment_int_rec.paid_amount,
960 x_org_unit_cd => payment_int_rec.org_unit_cd,
961 x_status => l_status,
962 x_error_code => l_error
963 );
964 EXCEPTION
965 WHEN OTHERS THEN
966 fnd_message.set_name('IGF','IGF_SE_ERR_PAYINT_UPD');
967 igs_ge_msg_stack.add;
968 app_exception.raise_exception;
969 END; -- new payroll not null
970 ELSE
971 CLOSE c_payment_int;
972 END IF;
973
974 END IF;
975 ELSIF l_status = 'ERROR' THEN
976 raise my_exception;
977 END IF;
978
979 -- if adjusment is successful then do NULL , return stat would be DONE else
980 -- rollback the transaction and update the PAYMENT_INT table for error.
981 EXCEPTION
982
983 WHEN my_exception THEN
984 IF new_references.payroll_id IS NOT NULL THEN
985
986 -- this means that the record is coming from IGF_SE_PAYMENT_INT table
987 -- update payment int table with the error code
988 OPEN c_payment_int;
989 FETCH c_payment_int INTO payment_int_rec;
990 IF c_payment_int%FOUND THEN
991 CLOSE c_payment_int;
992
993 -- rollback the transaction insert into PAYMENT.
994 -- update the int table
995
996 ROLLBACK TO se_payment_upd;
997
998 BEGIN
999 igf_se_payment_int_pkg.update_row(
1000 x_rowid => payment_int_rec.rowid,
1001 x_transaction_id => payment_int_rec.transaction_id,
1002 x_batch_id => payment_int_rec.batch_id,
1003 x_payroll_id => payment_int_rec.payroll_id,
1004 x_payroll_date => payment_int_rec.payroll_date,
1005 x_auth_id => payment_int_rec.auth_id,
1006 x_person_id => payment_int_rec.person_id,
1007 x_fund_id => payment_int_rec.fund_id,
1008 x_paid_amount => payment_int_rec.paid_amount,
1009 x_org_unit_cd => payment_int_rec.org_unit_cd,
1010 x_status => l_status,
1011 x_error_code => l_error
1012 );
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 fnd_message.set_name('IGF','IGF_SE_ERR_PAYINT_UPD');
1016 igs_ge_msg_stack.add;
1017 app_exception.raise_exception;
1018 END;
1019 ELSE
1020 CLOSE c_payment_int;
1021 ROLLBACK TO se_payment_upd;
1022 END IF;
1023
1024 ELSIF new_references.payroll_id IS NULL THEN
1025 -- this means that the record is being entered from the screen.
1026
1027 ROLLBACK TO se_payment_upd;
1028
1029 -- Get the error code meaning from the lookups and show the exact error to the user
1030 OPEN c_get_se_errors( l_error);
1031 FETCH c_get_se_errors INTO l_error_meaming;
1032 CLOSE c_get_se_errors;
1033
1034 FND_MESSAGE.SET_NAME('IGF', 'IGF_SE_ERR_PAY_ADJ');
1035 FND_MESSAGE.SET_TOKEN('ERROR',l_error_meaming);
1036 igs_ge_msg_stack.add;
1037 app_exception.raise_exception;
1038
1039 END IF;
1040 END; -- exception of calling adjustment
1041 END update_row;
1042
1043
1044 PROCEDURE add_row (
1045 x_rowid IN OUT NOCOPY VARCHAR2,
1046 x_transaction_id IN OUT NOCOPY NUMBER,
1047 x_payroll_id IN NUMBER,
1048 x_payroll_date IN DATE,
1049 x_auth_id IN NUMBER,
1050 x_person_id IN NUMBER,
1051 x_fund_id IN NUMBER,
1052 x_paid_amount IN NUMBER,
1053 x_org_unit_cd IN VARCHAR2,
1054 x_source IN VARCHAR2,
1055 x_mode IN VARCHAR2
1056 ) AS
1057 /*
1058 || Created By : ssawhney
1059 || Created On : 31-DEC-2001
1060 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1061 || Known limitations, enhancements or remarks : AVOID USING THIS. DUE TO SPECIFIC LOGIC OF INSERT/UPDATE
1062 || FOR THIS TABLE.
1063 || Change History :
1064 || Who When What
1065 || (reverse chronological order - newest change first)
1066 */
1067 CURSOR c1 IS
1068 SELECT rowid
1069 FROM igf_se_payment
1070 WHERE transaction_id = x_transaction_id;
1071
1072 BEGIN
1073
1074 OPEN c1;
1075 FETCH c1 INTO x_rowid;
1076 IF (c1%NOTFOUND) THEN
1077 CLOSE c1;
1078
1079 insert_row (
1080 x_rowid,
1081 x_transaction_id,
1082 x_payroll_id,
1083 x_payroll_date,
1084 x_auth_id,
1085 x_person_id,
1086 x_fund_id,
1087 x_paid_amount,
1088 x_org_unit_cd,
1089 x_source,
1090 x_mode
1091 );
1092 RETURN;
1093 END IF;
1094 CLOSE c1;
1095
1096 update_row (
1097 x_rowid,
1098 x_transaction_id,
1099 x_payroll_id,
1100 x_payroll_date,
1101 x_auth_id,
1102 x_person_id,
1103 x_fund_id,
1104 x_paid_amount,
1105 x_org_unit_cd,
1106 x_source,
1107 x_mode
1108 );
1109
1110 END add_row;
1111
1112
1113 PROCEDURE delete_row (
1114 x_rowid IN VARCHAR2
1115 ) AS
1116 /*
1117 || Created By : ssawhney
1118 || Created On : 31-DEC-2001
1119 || Purpose : Handles the DELETE DML logic for the table.
1120 || Known limitations, enhancements or remarks :
1121 || Change History :
1122 || Who When What
1123 || (reverse chronological order - newest change first)
1124 */
1125 BEGIN
1126
1127 before_dml (
1128 p_action => 'DELETE',
1129 x_rowid => x_rowid
1130 );
1131
1132 DELETE FROM igf_se_payment
1133 WHERE rowid = x_rowid;
1134
1135 IF (SQL%NOTFOUND) THEN
1136 RAISE NO_DATA_FOUND;
1137 END IF;
1138
1139 END delete_row;
1140
1141
1142 END igf_se_payment_pkg;
[Home] [Help]