[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CL_BATCH_PKG
Source
1 PACKAGE BODY igf_sl_cl_batch_pkg AS
2 /* $Header: IGFLI21B.pls 120.0 2005/06/02 15:52:14 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_cl_batch_all%ROWTYPE;
6 new_references igf_sl_cl_batch_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_cbth_id IN NUMBER DEFAULT NULL,
12 x_batch_id IN VARCHAR2 DEFAULT NULL,
13 x_file_creation_date IN DATE DEFAULT NULL,
14 x_file_trans_date IN DATE DEFAULT NULL,
15 x_file_ident_code IN VARCHAR2 DEFAULT NULL,
16 x_recipient_id IN VARCHAR2 DEFAULT NULL,
17 x_recip_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
18 x_source_id IN VARCHAR2 DEFAULT NULL,
19 x_source_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
20 x_send_resp IN VARCHAR2 DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL,
26 x_record_count_num IN NUMBER DEFAULT NULL,
27 x_total_net_disb_amt IN NUMBER DEFAULT NULL,
28 x_total_net_eft_amt IN NUMBER DEFAULT NULL,
29 x_total_net_non_eft_amt IN NUMBER DEFAULT NULL,
30 x_total_reissue_amt IN NUMBER DEFAULT NULL,
31 x_total_cancel_amt IN NUMBER DEFAULT NULL,
32 x_total_deficit_amt IN NUMBER DEFAULT NULL,
33 x_total_net_cancel_amt IN NUMBER DEFAULT NULL,
34 x_total_net_out_cancel_amt IN NUMBER DEFAULT NULL
35 ) AS
36 /*
37 || Created By : prchandr
38 || Created On : 02-NOV-2000
39 || Purpose : Initialises the Old and New references for the columns of the table.
40 || Known limitations, enhancements or remarks :
41 || Change History :
42 || Who When What
43 || (reverse chronological order - newest change first)
44 */
45
46 CURSOR cur_old_ref_values IS
47 SELECT *
48 FROM igf_sl_cl_batch_all
49 WHERE rowid = x_rowid;
50
51 BEGIN
52
53 l_rowid := x_rowid;
54
55 -- Code for setting the Old and New Reference Values.
56 -- Populate Old Values.
57 OPEN cur_old_ref_values;
58 FETCH cur_old_ref_values INTO old_references;
59 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
60 CLOSE cur_old_ref_values;
61 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
62 igs_ge_msg_stack.add;
63 app_exception.raise_exception;
64 RETURN;
65 END IF;
66 CLOSE cur_old_ref_values;
67
68 -- Populate New Values.
69 new_references.cbth_id := x_cbth_id;
70 new_references.batch_id := x_batch_id;
71 new_references.file_creation_date := x_file_creation_date;
72 new_references.file_trans_date := x_file_trans_date;
73 new_references.file_ident_code := x_file_ident_code;
74 new_references.recipient_id := x_recipient_id;
75 new_references.recip_non_ed_brc_id := x_recip_non_ed_brc_id;
76 new_references.source_id := x_source_id;
77 new_references.source_non_ed_brc_id := x_source_non_ed_brc_id;
78 new_references.send_resp := x_send_resp;
79
80 new_references.record_count_num := x_record_count_num;
81 new_references.total_net_disb_amt := x_total_net_disb_amt;
82 new_references.total_net_eft_amt := x_total_net_eft_amt;
83 new_references.total_net_non_eft_amt := x_total_net_non_eft_amt;
84 new_references.total_reissue_amt := x_total_reissue_amt;
85 new_references.total_cancel_amt := x_total_cancel_amt;
86 new_references.total_deficit_amt := x_total_deficit_amt;
87 new_references.total_net_cancel_amt := x_total_net_cancel_amt;
88 new_references.total_net_out_cancel_amt := x_total_net_out_cancel_amt;
89
90 IF (p_action = 'UPDATE') THEN
91 new_references.creation_date := old_references.creation_date;
92 new_references.created_by := old_references.created_by;
93 ELSE
94 new_references.creation_date := x_creation_date;
95 new_references.created_by := x_created_by;
96 END IF;
97
98 new_references.last_update_date := x_last_update_date;
99 new_references.last_updated_by := x_last_updated_by;
100 new_references.last_update_login := x_last_update_login;
101
102 END set_column_values;
103
104
105 PROCEDURE check_child_existance IS
106 /*
107 || Created By : prchandr
108 || Created On : 02-NOV-2000
109 || Purpose : Checks for the existance of Child records.
110 || Known limitations, enhancements or remarks :
111 || Change History :
112 || Who When What
113 || (reverse chronological order - newest change first)
114 */
115 BEGIN
116
117 igf_sl_cl_resp_r1_pkg.get_fk_igf_sl_cl_batch (
118 old_references.cbth_id
119 );
120
121 END check_child_existance;
122
123
124 FUNCTION get_pk_for_validation (
125 x_cbth_id IN NUMBER
126 ) RETURN BOOLEAN AS
127 /*
128 || Created By : prchandr
129 || Created On : 02-NOV-2000
130 || Purpose : Validates the Primary Key of the table.
131 || Known limitations, enhancements or remarks :
132 || Change History :
133 || Who When What
134 || (reverse chronological order - newest change first)
135 */
136 CURSOR cur_rowid IS
137 SELECT rowid
138 FROM igf_sl_cl_batch_all
139 WHERE cbth_id = x_cbth_id
140 FOR UPDATE NOWAIT;
141
142 lv_rowid cur_rowid%RowType;
143
144 BEGIN
145
146 OPEN cur_rowid;
147 FETCH cur_rowid INTO lv_rowid;
148 IF (cur_rowid%FOUND) THEN
149 CLOSE cur_rowid;
150 RETURN(TRUE);
151 ELSE
152 CLOSE cur_rowid;
153 RETURN(FALSE);
154 END IF;
155
156 END get_pk_for_validation;
157
158
159 PROCEDURE before_dml (
160 p_action IN VARCHAR2,
161 x_rowid IN VARCHAR2 DEFAULT NULL,
162 x_cbth_id IN NUMBER DEFAULT NULL,
163 x_batch_id IN VARCHAR2 DEFAULT NULL,
164 x_file_creation_date IN DATE DEFAULT NULL,
165 x_file_trans_date IN DATE DEFAULT NULL,
166 x_file_ident_code IN VARCHAR2 DEFAULT NULL,
167 x_recipient_id IN VARCHAR2 DEFAULT NULL,
168 x_recip_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
169 x_source_id IN VARCHAR2 DEFAULT NULL,
170 x_source_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
171 x_send_resp IN VARCHAR2 DEFAULT NULL,
172 x_creation_date IN DATE DEFAULT NULL,
173 x_created_by IN NUMBER DEFAULT NULL,
174 x_last_update_date IN DATE DEFAULT NULL,
175 x_last_updated_by IN NUMBER DEFAULT NULL,
176 x_last_update_login IN NUMBER DEFAULT NULL,
177 x_record_count_num IN NUMBER DEFAULT NULL,
178 x_total_net_disb_amt IN NUMBER DEFAULT NULL,
179 x_total_net_eft_amt IN NUMBER DEFAULT NULL,
180 x_total_net_non_eft_amt IN NUMBER DEFAULT NULL,
181 x_total_reissue_amt IN NUMBER DEFAULT NULL,
182 x_total_cancel_amt IN NUMBER DEFAULT NULL,
183 x_total_deficit_amt IN NUMBER DEFAULT NULL,
184 x_total_net_cancel_amt IN NUMBER DEFAULT NULL,
185 x_total_net_out_cancel_amt IN NUMBER DEFAULT NULL
186 ) AS
187 /*
188 || Created By : prchandr
189 || Created On : 02-NOV-2000
190 || Purpose : Initialises the columns, Checks Constraints, Calls the
191 || Trigger Handlers for the table, before any DML operation.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 BEGIN
198
199 set_column_values (
200 p_action,
201 x_rowid,
202 x_cbth_id,
203 x_batch_id,
204 x_file_creation_date,
205 x_file_trans_date,
206 x_file_ident_code,
207 x_recipient_id,
208 x_recip_non_ed_brc_id,
209 x_source_id,
210 x_source_non_ed_brc_id,
211 x_send_resp,
212 x_creation_date,
213 x_created_by,
214 x_last_update_date,
215 x_last_updated_by,
216 x_last_update_login,
217 x_record_count_num,
218 x_total_net_disb_amt,
219 x_total_net_eft_amt,
220 x_total_net_non_eft_amt ,
221 x_total_reissue_amt,
222 x_total_cancel_amt,
223 x_total_deficit_amt,
224 x_total_net_cancel_amt,
225 x_total_net_out_cancel_amt
226 );
227
228 IF (p_action = 'INSERT') THEN
229 -- Call all the procedures related to Before Insert.
230 IF ( get_pk_for_validation(
231 new_references.cbth_id
232 )
233 ) THEN
234 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
235 igs_ge_msg_stack.add;
236 app_exception.raise_exception;
237 END IF;
238 ELSIF (p_action = 'DELETE') THEN
239 -- Call all the procedures related to Before Delete.
240 check_child_existance;
241 ELSIF (p_action = 'VALIDATE_INSERT') THEN
242 -- Call all the procedures related to Before Insert.
243 IF ( get_pk_for_validation (
244 new_references.cbth_id
245 )
246 ) THEN
247 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
248 igs_ge_msg_stack.add;
249 app_exception.raise_exception;
250 END IF;
251 ELSIF (p_action = 'VALIDATE_DELETE') THEN
252 check_child_existance;
253 END IF;
254
255 END before_dml;
256
257
258 PROCEDURE insert_row (
259 x_rowid IN OUT NOCOPY VARCHAR2,
260 x_cbth_id IN OUT NOCOPY NUMBER,
261 x_batch_id IN VARCHAR2,
262 x_file_creation_date IN DATE,
263 x_file_trans_date IN DATE,
264 x_file_ident_code IN VARCHAR2,
265 x_recipient_id IN VARCHAR2,
266 x_recip_non_ed_brc_id IN VARCHAR2,
267 x_source_id IN VARCHAR2,
268 x_source_non_ed_brc_id IN VARCHAR2,
269 x_send_resp IN VARCHAR2,
270 x_mode IN VARCHAR2 DEFAULT 'R',
271 x_record_count_num IN NUMBER DEFAULT NULL,
272 x_total_net_disb_amt IN NUMBER DEFAULT NULL,
273 x_total_net_eft_amt IN NUMBER DEFAULT NULL,
274 x_total_net_non_eft_amt IN NUMBER DEFAULT NULL,
275 x_total_reissue_amt IN NUMBER DEFAULT NULL,
276 x_total_cancel_amt IN NUMBER DEFAULT NULL,
277 x_total_deficit_amt IN NUMBER DEFAULT NULL,
278 x_total_net_cancel_amt IN NUMBER DEFAULT NULL,
279 x_total_net_out_cancel_amt IN NUMBER DEFAULT NULL
280 ) AS
281 /*
282 || Created By : prchandr
283 || Created On : 02-NOV-2000
284 || Purpose : Handles the INSERT DML logic for the table.
285 || Known limitations, enhancements or remarks :
286 || Change History :
287 || Who When What
288 || (reverse chronological order - newest change first)
289 */
290 CURSOR c IS
291 SELECT rowid
292 FROM igf_sl_cl_batch_all
293 WHERE cbth_id = x_cbth_id;
294
295 x_last_update_date DATE;
296 x_last_updated_by NUMBER;
297 x_last_update_login NUMBER;
298 x_request_id NUMBER;
299 x_program_id NUMBER;
300 x_program_application_id NUMBER;
301 x_program_update_date DATE;
302 l_org_id igf_sl_cl_batch_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
303
304 BEGIN
305
306 x_last_update_date := SYSDATE;
307 IF (x_mode = 'I') THEN
308 x_last_updated_by := 1;
309 x_last_update_login := 0;
310 ELSIF (x_mode = 'R') THEN
311 x_last_updated_by := fnd_global.user_id;
312 IF (x_last_updated_by IS NULL) THEN
313 x_last_updated_by := -1;
314 END IF;
315 x_last_update_login := fnd_global.login_id;
316 IF (x_last_update_login IS NULL) THEN
317 x_last_update_login := -1;
318 END IF;
319 x_request_id := fnd_global.conc_request_id;
320 x_program_id := fnd_global.conc_program_id;
321 x_program_application_id := fnd_global.prog_appl_id;
322
323 IF (x_request_id = -1) THEN
324 x_request_id := NULL;
325 x_program_id := NULL;
326 x_program_application_id := NULL;
327 x_program_update_date := NULL;
328 ELSE
329 x_program_update_date := SYSDATE;
330 END IF;
331 ELSE
332 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
333 igs_ge_msg_stack.add;
334 app_exception.raise_exception;
335 END IF;
336
337 SELECT igf_sl_cl_batch_s.nextval
338 INTO x_cbth_id
339 FROM dual;
340
341 before_dml(
342 p_action => 'INSERT',
343 x_rowid => x_rowid,
344 x_cbth_id => x_cbth_id,
345 x_batch_id => x_batch_id,
346 x_file_creation_date => x_file_creation_date,
347 x_file_trans_date => x_file_trans_date,
348 x_file_ident_code => x_file_ident_code,
349 x_recipient_id => x_recipient_id,
350 x_recip_non_ed_brc_id => x_recip_non_ed_brc_id,
351 x_source_id => x_source_id,
352 x_source_non_ed_brc_id => x_source_non_ed_brc_id,
353 x_send_resp => x_send_resp,
354 x_creation_date => x_last_update_date,
355 x_created_by => x_last_updated_by,
356 x_last_update_date => x_last_update_date,
357 x_last_updated_by => x_last_updated_by,
358 x_last_update_login => x_last_update_login,
359 x_record_count_num => x_record_count_num,
360 x_total_net_disb_amt => x_total_net_disb_amt,
364 x_total_cancel_amt => x_total_cancel_amt,
361 x_total_net_eft_amt => x_total_net_eft_amt,
362 x_total_net_non_eft_amt => x_total_net_non_eft_amt,
363 x_total_reissue_amt => x_total_reissue_amt,
365 x_total_deficit_amt => x_total_deficit_amt,
366 x_total_net_cancel_amt => x_total_net_cancel_amt,
367 x_total_net_out_cancel_amt => x_total_net_out_cancel_amt
368 );
369
370 INSERT INTO igf_sl_cl_batch_all (
371 cbth_id,
372 batch_id,
373 file_creation_date,
374 file_trans_date,
375 file_ident_code,
376 recipient_id,
377 recip_non_ed_brc_id,
378 source_id,
379 source_non_ed_brc_id,
380 send_resp,
381 creation_date,
382 created_by,
383 last_update_date,
384 last_updated_by,
385 last_update_login,
386 request_id,
387 program_id,
388 program_application_id,
389 program_update_date,
390 org_id,
391 record_count_num,
392 total_net_disb_amt,
393 total_net_eft_amt,
394 total_net_non_eft_amt,
395 total_reissue_amt,
396 total_cancel_amt,
397 total_deficit_amt,
398 total_net_cancel_amt,
399 total_net_out_cancel_amt
400 ) VALUES (
401 new_references.cbth_id,
402 new_references.batch_id,
403 new_references.file_creation_date,
404 new_references.file_trans_date,
405 new_references.file_ident_code,
406 new_references.recipient_id,
407 new_references.recip_non_ed_brc_id,
408 new_references.source_id,
409 new_references.source_non_ed_brc_id,
410 new_references.send_resp,
411 x_last_update_date,
412 x_last_updated_by,
413 x_last_update_date,
414 x_last_updated_by,
415 x_last_update_login ,
416 x_request_id,
417 x_program_id,
418 x_program_application_id,
419 x_program_update_date,
420 l_org_id,
421 new_references.record_count_num,
422 new_references.total_net_disb_amt,
423 new_references.total_net_eft_amt,
424 new_references.total_net_non_eft_amt ,
425 new_references.total_reissue_amt,
426 new_references.total_cancel_amt,
427 new_references.total_deficit_amt,
428 new_references.total_net_cancel_amt,
429 new_references.total_net_out_cancel_amt
430 );
431
432 OPEN c;
433 FETCH c INTO x_rowid;
434 IF (c%NOTFOUND) THEN
435 CLOSE c;
436 RAISE NO_DATA_FOUND;
437 END IF;
438 CLOSE c;
439
440 END insert_row;
441
442
443 PROCEDURE lock_row (
444 x_rowid IN VARCHAR2,
445 x_cbth_id IN NUMBER,
446 x_batch_id IN VARCHAR2,
447 x_file_creation_date IN DATE,
448 x_file_trans_date IN DATE,
449 x_file_ident_code IN VARCHAR2,
450 x_recipient_id IN VARCHAR2,
451 x_recip_non_ed_brc_id IN VARCHAR2,
452 x_source_id IN VARCHAR2,
453 x_source_non_ed_brc_id IN VARCHAR2,
454 x_send_resp IN VARCHAR2,
455 x_record_count_num IN NUMBER,
456 x_total_net_disb_amt IN NUMBER,
457 x_total_net_eft_amt IN NUMBER,
458 x_total_net_non_eft_amt IN NUMBER,
459 x_total_reissue_amt IN NUMBER,
460 x_total_cancel_amt IN NUMBER,
461 x_total_deficit_amt IN NUMBER,
462 x_total_net_cancel_amt IN NUMBER,
463 x_total_net_out_cancel_amt IN NUMBER
464 ) AS
465 /*
466 || Created By : prchandr
467 || Created On : 02-NOV-2000
468 || Purpose : Handles the LOCK mechanism for the table.
469 || Known limitations, enhancements or remarks :
470 || Change History :
471 || Who When What
472 || (reverse chronological order - newest change first)
473 */
474 CURSOR c1 IS
475 SELECT
476 batch_id,
477 file_creation_date,
478 file_trans_date,
479 file_ident_code,
480 recipient_id,
481 recip_non_ed_brc_id,
482 source_id,
483 source_non_ed_brc_id,
484 send_resp,
485 org_id
486 FROM igf_sl_cl_batch_all
487 WHERE rowid = x_rowid
488 FOR UPDATE NOWAIT;
489
490 tlinfo c1%ROWTYPE;
491
492 BEGIN
493
494 OPEN c1;
495 FETCH c1 INTO tlinfo;
496 IF (c1%notfound) THEN
497 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
498 igs_ge_msg_stack.add;
499 CLOSE c1;
500 app_exception.raise_exception;
501 RETURN;
502 END IF;
503 CLOSE c1;
504
505 IF (
506 ((tlinfo.batch_id = x_batch_id) OR ((tlinfo.batch_id IS NULL) AND (X_batch_id IS NULL)))
507 AND ((tlinfo.file_creation_date = x_file_creation_date) OR ((tlinfo.file_creation_date IS NULL) AND (X_file_creation_date IS NULL)))
508 AND ((tlinfo.file_trans_date = x_file_trans_date) OR ((tlinfo.file_trans_date IS NULL) AND (X_file_trans_date IS NULL)))
509 AND ((tlinfo.file_ident_code = x_file_ident_code) OR ((tlinfo.file_ident_code IS NULL) AND (X_file_ident_code IS NULL)))
510 AND ((tlinfo.recipient_id = x_recipient_id) OR ((tlinfo.recipient_id IS NULL) AND (X_recipient_id IS NULL)))
511 AND ((tlinfo.recip_non_ed_brc_id = x_recip_non_ed_brc_id) OR ((tlinfo.recip_non_ed_brc_id IS NULL) AND (X_recip_non_ed_brc_id IS NULL)))
512 AND ((tlinfo.source_id = x_source_id) OR ((tlinfo.source_id IS NULL) AND (X_source_id IS NULL)))
513 AND ((tlinfo.source_non_ed_brc_id = x_source_non_ed_brc_id) OR ((tlinfo.source_non_ed_brc_id IS NULL) AND (X_source_non_ed_brc_id IS NULL)))
514 AND ((tlinfo.send_resp = x_send_resp) OR ((tlinfo.send_resp IS NULL) AND (X_send_resp IS NULL)))
515 ) THEN
516 NULL;
517 ELSE
518 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
519 igs_ge_msg_stack.add;
520 app_exception.raise_exception;
521 END IF;
522
523 RETURN;
524
525 END lock_row;
526
530 x_cbth_id IN NUMBER,
527
528 PROCEDURE update_row (
529 x_rowid IN VARCHAR2,
531 x_batch_id IN VARCHAR2,
532 x_file_creation_date IN DATE,
533 x_file_trans_date IN DATE,
534 x_file_ident_code IN VARCHAR2,
535 x_recipient_id IN VARCHAR2,
536 x_recip_non_ed_brc_id IN VARCHAR2,
537 x_source_id IN VARCHAR2,
538 x_source_non_ed_brc_id IN VARCHAR2,
539 x_send_resp IN VARCHAR2,
540 x_mode IN VARCHAR2 DEFAULT 'R',
541 x_record_count_num IN NUMBER DEFAULT NULL,
542 x_total_net_disb_amt IN NUMBER DEFAULT NULL,
543 x_total_net_eft_amt IN NUMBER DEFAULT NULL,
544 x_total_net_non_eft_amt IN NUMBER DEFAULT NULL,
545 x_total_reissue_amt IN NUMBER DEFAULT NULL,
546 x_total_cancel_amt IN NUMBER DEFAULT NULL,
547 x_total_deficit_amt IN NUMBER DEFAULT NULL,
548 x_total_net_cancel_amt IN NUMBER DEFAULT NULL,
549 x_total_net_out_cancel_amt IN NUMBER DEFAULT NULL
550 ) AS
551 /*
552 || Created By : prchandr
553 || Created On : 02-NOV-2000
554 || Purpose : Handles the UPDATE DML logic for the table.
555 || Known limitations, enhancements or remarks :
556 || Change History :
557 || Who When What
558 || (reverse chronological order - newest change first)
559 */
560 x_last_update_date DATE ;
561 x_last_updated_by NUMBER;
562 x_last_update_login NUMBER;
563 x_request_id NUMBER;
564 x_program_id NUMBER;
565 x_program_application_id NUMBER;
566 x_program_update_date DATE;
567
568 BEGIN
569
570 x_last_update_date := SYSDATE;
571 IF (X_MODE = 'I') THEN
572 x_last_updated_by := 1;
573 x_last_update_login := 0;
574 ELSIF (x_mode = 'R') THEN
575 x_last_updated_by := fnd_global.user_id;
576 IF x_last_updated_by IS NULL THEN
577 x_last_updated_by := -1;
578 END IF;
579 x_last_update_login := fnd_global.login_id;
580 IF (x_last_update_login IS NULL) THEN
581 x_last_update_login := -1;
582 END IF;
583 ELSE
584 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
585 igs_ge_msg_stack.add;
586 app_exception.raise_exception;
587 END IF;
588
589 before_dml(
590 p_action => 'UPDATE',
591 x_rowid => x_rowid,
592 x_cbth_id => x_cbth_id,
593 x_batch_id => x_batch_id,
594 x_file_creation_date => x_file_creation_date,
595 x_file_trans_date => x_file_trans_date,
596 x_file_ident_code => x_file_ident_code,
597 x_recipient_id => x_recipient_id,
598 x_recip_non_ed_brc_id => x_recip_non_ed_brc_id,
599 x_source_id => x_source_id,
600 x_source_non_ed_brc_id => x_source_non_ed_brc_id,
601 x_send_resp => x_send_resp,
602 x_creation_date => x_last_update_date,
603 x_created_by => x_last_updated_by,
604 x_last_update_date => x_last_update_date,
605 x_last_updated_by => x_last_updated_by,
606 x_last_update_login => x_last_update_login,
607 x_record_count_num => x_record_count_num,
608 x_total_net_disb_amt => x_total_net_disb_amt,
609 x_total_net_eft_amt => x_total_net_eft_amt,
610 x_total_net_non_eft_amt => x_total_net_non_eft_amt,
611 x_total_reissue_amt => x_total_reissue_amt,
612 x_total_cancel_amt => x_total_cancel_amt,
613 x_total_deficit_amt => x_total_deficit_amt,
614 x_total_net_cancel_amt => x_total_net_cancel_amt,
615 x_total_net_out_cancel_amt => x_total_net_out_cancel_amt
616 );
617
618 IF (x_mode = 'R') THEN
619 x_request_id := fnd_global.conc_request_id;
620 x_program_id := fnd_global.conc_program_id;
621 x_program_application_id := fnd_global.prog_appl_id;
622 IF (x_request_id = -1) THEN
623 x_request_id := old_references.request_id;
624 x_program_id := old_references.program_id;
625 x_program_application_id := old_references.program_application_id;
626 x_program_update_date := old_references.program_update_date;
627 ELSE
628 x_program_update_date := SYSDATE;
629 END IF;
630 END IF;
631
632 UPDATE igf_sl_cl_batch_all
633 SET
634 batch_id = new_references.batch_id,
635 file_creation_date = new_references.file_creation_date,
636 file_trans_date = new_references.file_trans_date,
637 file_ident_code = new_references.file_ident_code,
638 recipient_id = new_references.recipient_id,
639 recip_non_ed_brc_id = new_references.recip_non_ed_brc_id,
640 source_id = new_references.source_id,
641 source_non_ed_brc_id = new_references.source_non_ed_brc_id,
645 last_update_login = x_last_update_login ,
642 send_resp = new_references.send_resp,
643 last_update_date = x_last_update_date,
644 last_updated_by = x_last_updated_by,
646 request_id = x_request_id,
647 program_id = x_program_id,
648 program_application_id = x_program_application_id,
649 program_update_date = x_program_update_date,
650 record_count_num = new_references.record_count_num,
651 total_net_disb_amt = new_references.total_net_disb_amt,
652 total_net_eft_amt = new_references.total_net_eft_amt,
653 total_net_non_eft_amt = new_references.total_net_non_eft_amt ,
654 total_reissue_amt = new_references.total_reissue_amt,
655 total_cancel_amt = new_references.total_cancel_amt,
656 total_deficit_amt = new_references.total_deficit_amt,
657 total_net_cancel_amt = new_references.total_net_cancel_amt,
658 total_net_out_cancel_amt = new_references.total_net_out_cancel_amt
659
660
661 WHERE rowid = x_rowid;
662
663 IF (SQL%NOTFOUND) THEN
664 RAISE NO_DATA_FOUND;
665 END IF;
666
667 END update_row;
668
669
670 PROCEDURE add_row (
671 x_rowid IN OUT NOCOPY VARCHAR2,
672 x_cbth_id IN OUT NOCOPY NUMBER,
673 x_batch_id IN VARCHAR2,
674 x_file_creation_date IN DATE,
675 x_file_trans_date IN DATE,
676 x_file_ident_code IN VARCHAR2,
677 x_recipient_id IN VARCHAR2,
678 x_recip_non_ed_brc_id IN VARCHAR2,
679 x_source_id IN VARCHAR2,
680 x_source_non_ed_brc_id IN VARCHAR2,
681 x_send_resp IN VARCHAR2,
682 x_mode IN VARCHAR2 DEFAULT 'R',
683 x_record_count_num IN NUMBER DEFAULT NULL,
684 x_total_net_disb_amt IN NUMBER DEFAULT NULL,
685 x_total_net_eft_amt IN NUMBER DEFAULT NULL,
686 x_total_net_non_eft_amt IN NUMBER DEFAULT NULL,
687 x_total_reissue_amt IN NUMBER DEFAULT NULL,
688 x_total_cancel_amt IN NUMBER DEFAULT NULL,
689 x_total_deficit_amt IN NUMBER DEFAULT NULL,
690 x_total_net_cancel_amt IN NUMBER DEFAULT NULL,
691 x_total_net_out_cancel_amt IN NUMBER DEFAULT NULL
692 ) AS
693 /*
694 || Created By : prchandr
695 || Created On : 02-NOV-2000
696 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
697 || Known limitations, enhancements or remarks :
698 || Change History :
699 || Who When What
700 || (reverse chronological order - newest change first)
701 */
702 CURSOR c1 IS
703 SELECT rowid
704 FROM igf_sl_cl_batch_all
705 WHERE cbth_id = x_cbth_id;
706
707 BEGIN
708
709 OPEN c1;
710 FETCH c1 INTO x_rowid;
711 IF (c1%NOTFOUND) THEN
712 CLOSE c1;
713
714 insert_row (
715 x_rowid,
716 x_cbth_id,
717 x_batch_id,
718 x_file_creation_date,
719 x_file_trans_date,
720 x_file_ident_code,
721 x_recipient_id,
722 x_recip_non_ed_brc_id,
723 x_source_id,
724 x_source_non_ed_brc_id,
725 x_send_resp,
726 x_mode,
727 x_record_count_num,
728 x_total_net_disb_amt,
729 x_total_net_eft_amt,
730 x_total_net_non_eft_amt ,
731 x_total_reissue_amt,
732 x_total_cancel_amt,
733 x_total_deficit_amt,
734 x_total_net_cancel_amt,
735 x_total_net_out_cancel_amt
736 );
737 RETURN;
738 END IF;
739 CLOSE c1;
740
741 update_row (
742 x_rowid,
743 x_cbth_id,
744 x_batch_id,
745 x_file_creation_date,
746 x_file_trans_date,
747 x_file_ident_code,
748 x_recipient_id,
749 x_recip_non_ed_brc_id,
750 x_source_id,
751 x_source_non_ed_brc_id,
752 x_send_resp,
753 x_mode,
754 x_record_count_num,
755 x_total_net_disb_amt,
756 x_total_net_eft_amt,
757 x_total_net_non_eft_amt ,
758 x_total_reissue_amt,
759 x_total_cancel_amt,
760 x_total_deficit_amt,
761 x_total_net_cancel_amt,
762 x_total_net_out_cancel_amt
763 );
764
765 END add_row;
766
767
768 PROCEDURE delete_row (
769 x_rowid IN VARCHAR2
770 ) AS
771 /*
772 || Created By : prchandr
773 || Created On : 02-NOV-2000
774 || Purpose : Handles the DELETE DML logic for the table.
775 || Known limitations, enhancements or remarks :
776 || Change History :
777 || Who When What
778 || (reverse chronological order - newest change first)
779 */
780 BEGIN
781
782 before_dml (
783 p_action => 'DELETE',
784 x_rowid => x_rowid
785 );
786
787 DELETE FROM igf_sl_cl_batch_all
788 WHERE rowid = x_rowid;
789
793
790 IF (SQL%NOTFOUND) THEN
791 RAISE NO_DATA_FOUND;
792 END IF;
794 END delete_row;
795
796
797 END igf_sl_cl_batch_pkg;