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