[Home] [Help]
PACKAGE BODY: APPS.ARP_BR_REMIT_BATCHES
Source
1 PACKAGE BODY ARP_BR_REMIT_BATCHES AS
2 /* $Header: ARBRRM1B.pls 120.4 2004/02/27 19:00:17 mraymond ship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'ARP_BR_REMIT_BATCHES';
5
6 TYPE CUR_TYP IS REF CURSOR;
7
8 /*-------------- Private procedures used by the package --------------------*/
9
10 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
11
12 PROCEDURE validate_args_insert_remit(
13 p_batch_rec IN AR_BATCHES%ROWTYPE);
14
15 PROCEDURE validate_args_update_remit(
16 p_batch_rec IN AR_BATCHES%ROWTYPE);
17
18 PROCEDURE validate_receipt_method(
19 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
20 p_batch_date IN AR_BATCHES.batch_date%TYPE);
21
22 PROCEDURE validate_currency_code(
23 p_currency_code IN AR_BATCHES.currency_code%TYPE,
24 p_batch_date IN AR_BATCHES.batch_date%TYPE);
25
26 PROCEDURE validate_remit_bank_branch(
27 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
28 p_batch_date IN AR_BATCHES.batch_date%TYPE);
29
30 PROCEDURE validate_remit_bank_account(
31 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
32 p_batch_date IN AR_BATCHES.batch_date%TYPE);
33
34 PROCEDURE validate_remit_method_code(
35 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
36 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE);
37
38 PROCEDURE validate_auto_program(
39 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE);
40
41 /*------------------------ Public procedures ------------------------*/
42
43
44 /*===========================================================================+
45 | PROCEDURE |
46 | insert_remit |
47 | |
48 | DESCRIPTION |
49 | Procedure called during the process create bills receivable |
50 | remittance to insert the remittance batch row in the table AR_BATCHES |
51 | |
52 | SCOPE - PUBLIC |
53 | |
54 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
55 | |
56 | ARGUMENTS : IN : |
57 | |
58 | RETURNS : NONE |
59 | |
60 | NOTES |
61 | |
62 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
63 | |
64 +===========================================================================*/
65 PROCEDURE insert_remit(
66 p_batch_date IN AR_BATCHES.batch_date%TYPE,
67 p_gl_date IN AR_BATCHES.gl_date%TYPE,
68 p_currency_code IN AR_BATCHES.currency_code%TYPE,
69 p_comments IN AR_BATCHES.comments%TYPE,
70 p_attribute_category IN AR_BATCHES.attribute_category%TYPE,
71 p_attribute1 IN AR_BATCHES.attribute1%TYPE,
72 p_attribute2 IN AR_BATCHES.attribute2%TYPE,
73 p_attribute3 IN AR_BATCHES.attribute3%TYPE,
74 p_attribute4 IN AR_BATCHES.attribute4%TYPE,
75 p_attribute5 IN AR_BATCHES.attribute5%TYPE,
76 p_attribute6 IN AR_BATCHES.attribute6%TYPE,
77 p_attribute7 IN AR_BATCHES.attribute7%TYPE,
78 p_attribute8 IN AR_BATCHES.attribute8%TYPE,
79 p_attribute9 IN AR_BATCHES.attribute9%TYPE,
80 p_attribute10 IN AR_BATCHES.attribute10%TYPE,
81 p_media_reference IN AR_BATCHES.media_reference%TYPE,
82 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
83 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
84 p_receipt_class_id IN AR_BATCHES.receipt_class_id%TYPE,
85 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
86 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
87 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE,
88 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
89 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
90 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE,
91 p_batch_id OUT NOCOPY AR_BATCHES.batch_id%TYPE,
92 p_batch_name OUT NOCOPY AR_BATCHES.name%TYPE,
93 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
94
95
96 l_batch_rec AR_BATCHES%ROWTYPE;
97 l_row_id varchar2(20) := NULL;
98
99
100 BEGIN
101
102 IF PG_DEBUG in ('Y', 'C') THEN
103 arp_util.debug( 'ARP_BR_REMIT_BATCHES.insert_remit (+)');
104 END IF;
105
106 l_batch_rec.batch_date := p_batch_date;
107 l_batch_rec.gl_date := p_gl_date;
108 l_batch_rec.currency_code := p_currency_code;
109 l_batch_rec.comments := p_comments;
110 l_batch_rec.attribute_category := p_attribute_category;
111 l_batch_rec.attribute1 := p_attribute1;
112 l_batch_rec.attribute2 := p_attribute2;
113 l_batch_rec.attribute3 := p_attribute3;
114 l_batch_rec.attribute4 := p_attribute4;
115 l_batch_rec.attribute5 := p_attribute5;
116 l_batch_rec.attribute6 := p_attribute6;
117 l_batch_rec.attribute7 := p_attribute7;
118 l_batch_rec.attribute8 := p_attribute8;
119 l_batch_rec.attribute9 := p_attribute9;
120 l_batch_rec.attribute10 := p_attribute10;
121 l_batch_rec.media_reference := p_media_reference;
122 l_batch_rec.receipt_method_id := p_receipt_method_id;
123 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
124 l_batch_rec.receipt_class_id := p_receipt_class_id;
125 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
126 l_batch_rec.remit_method_code := p_remit_method_code;
127 l_batch_rec.with_recourse_flag := p_with_recourse_flag;
128 l_batch_rec.bank_deposit_number := p_bank_deposit_number;
129 l_batch_rec.auto_print_program_id := p_auto_print_program_id;
130 l_batch_rec.auto_trans_program_id := p_auto_trans_program_id;
131 l_batch_rec.control_count := 0;
132 l_batch_rec.control_amount := 0;
133
134 l_batch_rec.status := 'OP';
135 l_batch_rec.type := 'BR_REMITTANCE';
136 l_batch_rec.operation_request_id := NULL;
137
138 l_batch_rec.batch_applied_status := 'STARTED_CREATION';
139
140 ------------------------------------------
141 -- Automatic batch numbering
142 ------------------------------------------
143 l_batch_rec.batch_source_id := 1;
144
145 UPDATE ar_batch_sources
146 SET last_batch_num = NVL(last_batch_num,0) + 1
147 WHERE batch_source_id = l_batch_rec.batch_source_id;
148
149 -- Check that the Batch source is valid
150 IF (SQL%ROWCOUNT = 0) THEN
151 FND_MESSAGE.set_name('AR','AR_UPDNA_LAST_BATCH_NO');
152 APP_EXCEPTION.raise_exception;
153 END IF;
154
155 SELECT NVL(last_batch_num,0)
156 INTO l_batch_rec.name
157 FROM ar_batch_sources
158 WHERE batch_source_id = l_batch_rec.batch_source_id;
159
160 IF PG_DEBUG in ('Y', 'C') THEN
161 arp_util.debug( 'ARP_BR_REMIT_BATCHES.insert_remit ... last_batch_num:'||l_batch_rec.name);
162 END IF;
163
164
165 ------------------------------------------
166 -- Validation
167 ------------------------------------------
168 arp_br_remit_batches.validate_args_insert_remit(l_batch_rec);
169
170 ------------------------------------------
171 -- Call insert table handler
172 ------------------------------------------
173 arp_cr_batches_pkg.insert_p(l_batch_rec, l_row_id, p_batch_id);
174
175 p_batch_name := l_batch_rec.name;
176 p_batch_applied_status := l_batch_rec.batch_applied_status;
177
178 IF PG_DEBUG in ('Y', 'C') THEN
179 arp_util.debug( 'ARP_BR_REMIT_BATCHES.insert_remit (-)');
180 END IF;
181
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 IF PG_DEBUG in ('Y', 'C') THEN
186 arp_util.debug( 'EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.insert_remit');
187 END IF;
188 RAISE;
189
190 END insert_remit;
191
192
193 /*===========================================================================+
194 | PROCEDURE |
195 | validate_args_insert_remit |
196 | |
197 | DESCRIPTION |
198 | Procedure called during the process create bills receivable |
199 | remittance to validate the remittance batch row |
200 | |
201 | SCOPE - PUBLIC |
202 | |
203 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
204 | |
205 | ARGUMENTS : IN : |
206 | |
207 | RETURNS : NONE |
208 | |
209 | NOTES |
210 | |
211 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
212 | |
213 +===========================================================================*/
214 PROCEDURE validate_args_insert_remit(
215 p_batch_rec IN AR_BATCHES%ROWTYPE) IS
216
217 l_row_id varchar2(20) := NULL;
218 l_field varchar2(30) := NULL;
219
220 BEGIN
221
222 IF PG_DEBUG in ('Y', 'C') THEN
223 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_args_insert_remit (+)');
224 END IF;
225
226 -- Check that the main columns are filled
227 IF (p_batch_rec.type IS NULL) THEN
228 l_field := 'TYPE';
229 ELSIF (p_batch_rec.currency_code IS NULL) THEN
230 l_field := 'CURRENCY_CODE';
231 ELSIF (p_batch_rec.batch_date IS NULL) THEN
232 l_field := 'BATCH_DATE';
233 ELSIF (p_batch_rec.gl_date IS NULL) THEN
234 l_field := 'GL_DATE';
235 ELSIF (p_batch_rec.remit_method_code IS NULL) THEN
236 l_field := 'REMIT_METHOD_CODE';
237 ELSIF (p_batch_rec.remittance_bank_branch_id IS NULL) THEN
238 l_field := 'REMITTANCE_BANK_BRANCH_ID';
239 ELSIF (p_batch_rec.batch_applied_status IS NULL) THEN
240 l_field := 'BATCH_APPLIED_STATUS';
241 ELSIF (NVL(p_batch_rec.with_recourse_flag,'T') NOT IN ('Y','N')) THEN
242 l_field := 'WITH_RECOURSE_FLAG';
243 END IF;
244
245 IF (l_field IS NOT NULL) THEN
246 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
247 FND_MESSAGE.set_token('PROCEDURE','validate_args_insert_remit');
248 FND_MESSAGE.set_token('PARAMETER', l_field);
249 APP_EXCEPTION.raise_exception;
250 END IF;
251
252 -- Check that the Batch Name is unique for the batch source
253 arp_rw_batches_check_pkg.check_unique_batch_name(l_row_id,
254 p_batch_rec.batch_source_id,
255 p_batch_rec.name,
256 NULL,
257 NULL);
258
259 -- Check that the gl date is valid
260 arp_util.validate_gl_date(p_batch_rec.gl_date, NULL, NULL);
261
262 -- Check that the currency is valid
263 arp_br_remit_batches.validate_currency_code(p_batch_rec.currency_code,p_batch_rec.batch_date);
264
265 -- Check that the receipt method is valid
266 arp_br_remit_batches.validate_receipt_method(p_batch_rec.receipt_method_id, p_batch_rec.batch_date);
267
268 -- Check that the remittance bank branch is valid
269 arp_br_remit_batches.validate_remit_bank_branch(p_batch_rec.remittance_bank_branch_id,p_batch_rec.batch_date);
270
271 -- Check that the remittance account is valid
272 arp_br_remit_batches.validate_remit_bank_account(p_batch_rec.remit_bank_acct_use_id,p_batch_rec.batch_date);
273
274 -- Check that the remittance method and the flag with_recouse are consistent
275 arp_br_remit_batches.validate_remit_method_code(p_batch_rec.remit_method_code,p_batch_rec.with_recourse_flag);
276
277 -- Check that the format program is a type 'REMIT_TRANSMIT' program
278 arp_br_remit_batches.validate_auto_program(p_batch_rec.auto_trans_program_id);
279
280 -- IF media reference is filled, Check that it is unique
281 IF (p_batch_rec.media_reference IS NOT NULL) THEN
282 arp_rw_batches_check_pkg.check_unique_media_ref(l_row_id,
283 p_batch_rec.media_reference,
284 NULL,
285 NULL);
286 END IF;
287
288 IF PG_DEBUG in ('Y', 'C') THEN
289 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_args_insert_remit (-)');
290 END IF;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 IF PG_DEBUG in ('Y', 'C') THEN
295 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_args_insert_remit');
296 END IF;
297 RAISE;
298
299 END validate_args_insert_remit;
300
301
302 /*===========================================================================+
303 | PROCEDURE |
304 | validate_currency |
305 | |
306 | DESCRIPTION |
307 | Procedure called during the validation before inserting a batch row |
308 | |
309 | SCOPE - PUBLIC |
310 | |
311 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
312 | |
313 | ARGUMENTS : IN : |
314 | |
315 | RETURNS : NONE |
316 | |
317 | NOTES |
318 | |
319 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
320 | |
321 +===========================================================================*/
322 PROCEDURE validate_currency_code(
323 p_currency_code IN AR_BATCHES.currency_code%TYPE,
324 p_batch_date IN AR_BATCHES.batch_date%TYPE) IS
325
326 NB number;
327
328 BEGIN
329
330 IF PG_DEBUG in ('Y', 'C') THEN
331 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_currency_code (+)');
332 END IF;
333
334 SELECT COUNT(*)
335 INTO NB
336 FROM FND_CURRENCIES_VL
337 WHERE currency_code = p_currency_code
338 AND p_batch_date BETWEEN NVL(start_date_active,p_batch_date) and NVL(end_date_active,p_batch_date);
339
340 IF (NB = 0) THEN
341 FND_MESSAGE.set_name('AR','AR_BR_INVALID_CURRENCY');
342 APP_EXCEPTION.raise_exception;
343 END IF;
344
345 IF PG_DEBUG in ('Y', 'C') THEN
346 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_currency_code (-)');
347 END IF;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 IF PG_DEBUG in ('Y', 'C') THEN
352 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_currency_code');
353 END IF;
354 RAISE;
355
356 END validate_currency_code;
357
358
359 /*===========================================================================+
360 | PROCEDURE |
361 | validate_receipt_method |
362 | |
363 | DESCRIPTION |
364 | Procedure called during the validation before inserting a batch row |
365 | |
366 | SCOPE - PUBLIC |
367 | |
368 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
369 | |
370 | ARGUMENTS : IN : |
371 | |
372 | RETURNS : NONE |
373 | |
374 | NOTES |
375 | |
376 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
377 | 31/07/00 - MFLAHAUT |
378 | Receipt method on the remittance batch has to have the document sequence |
379 | defined, or the receipt_inherit_inv_num_flag has to be set to 'Y'. |
380 | |
381 +===========================================================================*/
382 PROCEDURE validate_receipt_method(
383 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
384 p_batch_date IN AR_BATCHES.batch_date%TYPE) IS
385
386 CURSOR Receipt_method IS
387 SELECT NVL(MT.receipt_inherit_inv_num_flag,'N'), MT.name
388 FROM AR_RECEIPT_METHODS MT,
389 AR_RECEIPT_CLASSES CL,
390 AR_LOOKUPS LK
391 WHERE MT.receipt_method_id = p_receipt_method_id
392 AND p_batch_date BETWEEN NVL(start_date,p_batch_date) and NVL(end_date,p_batch_date)
393 AND MT.receipt_class_id = CL.receipt_class_id
394 AND CL.creation_method_code = LK.lookup_code
395 AND LK.lookup_type = 'RECEIPT_CREATION_METHOD'
396 AND LK.lookup_code = 'BR_REMIT';
397
398 l_receipt_inherit_inv_num_flag AR_RECEIPT_METHODS.receipt_inherit_inv_num_flag%TYPE;
399 l_receipt_name AR_RECEIPT_METHODS.name%TYPE;
400
401 l_doc_seq_ret_stat VARCHAR2(10);
402 l_doc_sequence_id NUMBER;
403 l_doc_sequence_value NUMBER;
404 l_doc_sequence_type VARCHAR2(50);
405 l_doc_sequence_name VARCHAR2(50);
406 l_db_sequence_name VARCHAR2(50);
407 l_seq_ass_id NUMBER;
408 l_prd_tab_name VARCHAR2(50);
409 l_aud_tab_name VARCHAR2(50);
410 l_msg_flag VARCHAR2(1);
411
412 BEGIN
413
414 IF PG_DEBUG in ('Y', 'C') THEN
415 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_receipt_method (+)');
416 END IF;
417
418 OPEN Receipt_method;
419 FETCH Receipt_method INTO l_receipt_inherit_inv_num_flag, l_receipt_name;
420
421 IF (Receipt_method%NOTFOUND) THEN
422 FND_MESSAGE.set_name('AR','AR_BR_INVALID_RECEIPT_METHOD');
423 APP_EXCEPTION.raise_exception;
424 END IF;
425
426 CLOSE Receipt_method;
427
428 -- IS the receipt method flag receipt_inherit_inv_num_flag set to 'Y' ?
429 IF (l_receipt_inherit_inv_num_flag = 'Y') THEN
430 IF PG_DEBUG in ('Y', 'C') THEN
431 arp_util.debug('validate_receipt_method: ' || 'the flag l_receipt_inherit_inv_num_flag is Y');
432 END IF;
433 return;
434 END IF;
435
436 l_doc_seq_ret_stat := fnd_seqnum.get_seq_info(
437 app_id=>222,
438 cat_code=>l_receipt_name,
439 sob_id=>arp_global.set_of_books_id,
440 met_code=>'M',
441 trx_date=>TRUNC(p_batch_date),
442 docseq_id=>l_doc_sequence_id,
443 docseq_type=>l_doc_sequence_type,
444 docseq_name=>l_doc_sequence_name,
445 db_seq_name=>l_db_sequence_name,
446 seq_ass_id=>l_seq_ass_id,
447 prd_tab_name=>l_prd_tab_name,
448 aud_tab_name=>l_aud_tab_name,
449 msg_flag=>l_msg_flag ,
450 suppress_error=>'N' ,
451 suppress_warn=>'Y');
452
453 IF PG_DEBUG in ('Y', 'C') THEN
454 arp_util.debug('validate_receipt_method: ' || 'Return status :'||l_doc_seq_ret_stat||' for the receipt method '||l_receipt_name);
455 END IF;
456
457 /* If the function returns 'partially used but not assigned' return the exception
458 AR_RAPI_DOC_SEQ_NOT_EXIST_P
459 All other exceptions raised by the get_seq_info procedure will be raised to
460 give the standard information. */
461
462 IF (l_doc_seq_ret_stat = -2) THEN
463 FND_MESSAGE.set_name('AR','AR_RAPI_DOC_SEQ_NOT_EXIST_P');
464 APP_EXCEPTION.raise_exception;
465 END IF;
466
467 IF PG_DEBUG in ('Y', 'C') THEN
468 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_receipt_method (-)');
469 END IF;
470
471 EXCEPTION
472
473 WHEN NO_DATA_FOUND THEN
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_util.debug('exception NO_DATA_FOUND in ARP_BR_REMIT_BATCHES.validate_receipt_method');
476 END IF;
477 FND_MESSAGE.set_name('AR','AR_BR_INVALID_RECEIPT_METHOD');
478
479 IF Receipt_method%ISOPEN THEN
480 CLOSE Receipt_method;
481 END IF;
482
483 RAISE;
484
485 WHEN OTHERS THEN
486 IF PG_DEBUG in ('Y', 'C') THEN
487 arp_util.debug('Unhandled exception in ARP_BR_REMIT_BATCHES.validate_receipt_method');
488 arp_util.debug('validate_receipt_method: ' || 'Return status :'||l_doc_seq_ret_stat||' for the receipt method '||l_receipt_name);
489 END IF;
490
491 IF Receipt_method%ISOPEN THEN
492 CLOSE Receipt_method;
493 END IF;
494
495 RAISE;
496
497 END validate_receipt_method;
498
499
500 /*===========================================================================+
501 | PROCEDURE |
502 | validate_remit_bank_branch |
503 | |
504 | DESCRIPTION |
505 | Procedure called during the validation before inserting a batch row |
506 | |
507 | SCOPE - PUBLIC |
508 | |
509 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
510 | |
511 | ARGUMENTS : IN : |
512 | |
513 | RETURNS : NONE |
514 | |
515 | NOTES |
516 | |
517 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
518 | |
519 +===========================================================================*/
520 PROCEDURE validate_remit_bank_branch(
521 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
522 p_batch_date IN AR_BATCHES.batch_date%TYPE) IS
523
524 NB number;
525
526 BEGIN
527
528 IF PG_DEBUG in ('Y', 'C') THEN
529 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_remit_bank_branch (+)');
530 END IF;
531
532 SELECT COUNT(*)
533 INTO NB
534 FROM ce_bank_branches_v
535 WHERE branch_party_id = p_remittance_bank_branch_id
536 AND p_batch_date BETWEEN p_batch_date and NVL(end_date,p_batch_date);
537
538 IF (NB = 0) THEN
539 FND_MESSAGE.set_name('AR','AR_BR_INVALID_REMIT_BRANCH');
540 APP_EXCEPTION.raise_exception;
541 END IF;
542
543 IF PG_DEBUG in ('Y', 'C') THEN
544 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_remit_bank_branch (-)');
545 END IF;
546
547 EXCEPTION
548 WHEN OTHERS THEN
549 IF PG_DEBUG in ('Y', 'C') THEN
550 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_remit_bank_branch');
551 END IF;
552 RAISE;
553
554 END validate_remit_bank_branch;
555
556
557 /*===========================================================================+
558 | PROCEDURE |
559 | validate_remit_account |
560 | |
561 | DESCRIPTION |
562 | Procedure called during the validation before inserting a batch row |
563 | |
564 | SCOPE - PUBLIC |
565 | |
566 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
567 | |
568 | ARGUMENTS : IN : |
569 | |
570 | RETURNS : NONE |
571 | |
572 | NOTES |
573 | |
574 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
575 | |
576 +===========================================================================*/
577 PROCEDURE validate_remit_bank_account(
578 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
579 p_batch_date IN AR_BATCHES.batch_date%TYPE) IS
580
581 nb NUMBER;
582
583 CURSOR accounts (p_bank_acct_use_id NUMBER, p_date DATE) IS
584 SELECT COUNT(*)
585 FROM ce_bank_accounts cba,
586 ce_bank_acct_uses cbau
587 WHERE cbau.bank_acct_use_id = p_remittance_bank_account_id
588 AND cbau.bank_account_id = cba.bank_account_id
589 AND p_date BETWEEN p_date AND
590 NVL(cbau.end_date,p_date);
591
592 BEGIN
593
594 IF PG_DEBUG in ('Y', 'C') THEN
595 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_remit_bank_account (+)');
596 END IF;
597
598 OPEN accounts(p_remittance_bank_account_id, p_batch_date);
599 FETCH accounts INTO nb;
600 CLOSE accounts;
601
602 IF (NB = 0) THEN
603 FND_MESSAGE.set_name('AR','AR_BR_INVALID_REMIT_ACCOUNT');
604 APP_EXCEPTION.raise_exception;
605 END IF;
606
607 IF PG_DEBUG in ('Y', 'C') THEN
608 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_remit_bank_account (-)');
609 END IF;
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 IF PG_DEBUG in ('Y', 'C') THEN
614 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_remit_bank_account');
615 END IF;
616 RAISE;
617
618 END validate_remit_bank_account;
619
620
621 /*===========================================================================+
622 | PROCEDURE |
623 | validate_remit_method_code |
624 | |
625 | DESCRIPTION |
626 | Procedure called during the validation before inserting a batch row |
627 | |
628 | SCOPE - PUBLIC |
629 | |
630 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
631 | |
632 | ARGUMENTS : IN : |
633 | |
634 | RETURNS : NONE |
635 | |
636 | NOTES |
637 | |
638 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
639 | |
640 +===========================================================================*/
641 PROCEDURE validate_remit_method_code(
642 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
643 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE) IS
644
645
646 BEGIN
647
648 IF PG_DEBUG in ('Y', 'C') THEN
649 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_remit_method_code (+)');
650 END IF;
651
652 -- the with recourse flag must be 'N' with the remittance method Standard
653 IF (p_remit_method_code = 'STANDARD' AND p_with_recourse_flag = 'Y') THEN
654 FND_MESSAGE.set_name('AR','AR_BR_INVALID_REMIT_METHOD');
655 APP_EXCEPTION.raise_exception;
656 END IF;
657
658 IF PG_DEBUG in ('Y', 'C') THEN
659 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_remit_method_code (-)');
660 END IF;
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 IF PG_DEBUG in ('Y', 'C') THEN
665 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_remit_method_code');
666 END IF;
667 RAISE;
668
669 END validate_remit_method_code;
670
671
672
673 /*===========================================================================+
674 | PROCEDURE |
675 | validate_auto_program |
676 | |
677 | DESCRIPTION |
678 | Procedure called during the validation before inserting a batch row |
679 | |
680 | SCOPE - PUBLIC |
681 | |
682 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
683 | |
684 | ARGUMENTS : IN : |
685 | |
686 | RETURNS : NONE |
687 | |
688 | NOTES |
689 | |
690 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
691 | |
692 +===========================================================================*/
693 PROCEDURE validate_auto_program(
694 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE) IS
695
696 NB number;
697
698 BEGIN
699
700 IF PG_DEBUG in ('Y', 'C') THEN
701 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_auto_program (+)');
702 END IF;
703
704 IF p_auto_trans_program_id IS NOT NULL THEN
705
706 SELECT COUNT(*)
707 INTO NB
708 FROM ap_payment_programs
709 WHERE program_id = p_auto_trans_program_id
710 AND program_type like 'REMIT_TRANSMIT';
711
712 IF (NB = 0) THEN
713 FND_MESSAGE.set_name('AR','AR_BR_INVALID_REMIT_PROGRAM');
714 APP_EXCEPTION.raise_exception;
715 END IF;
716
717 END IF;
718
719 IF PG_DEBUG in ('Y', 'C') THEN
720 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_auto_program (-)');
721 END IF;
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 IF PG_DEBUG in ('Y', 'C') THEN
726 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_auto_program');
727 END IF;
728 RAISE;
729
730 END validate_auto_program;
731
732
733 /*===========================================================================+
734 | PROCEDURE |
735 | update_remit |
736 | |
737 | DESCRIPTION |
738 | Procedure called during the process create bills receivable |
739 | remittance to update the remittance batch row in the table AR_BATCHES |
740 | |
741 | SCOPE - PUBLIC |
742 | |
743 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
744 | |
745 | ARGUMENTS : IN : |
746 | |
747 | RETURNS : NONE |
748 | |
749 | NOTES |
750 | |
751 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
752 | |
753 +===========================================================================*/
754 PROCEDURE update_remit(
755 p_api_version IN NUMBER ,
756 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
757 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
758 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
759 x_return_status OUT NOCOPY VARCHAR2 ,
760 x_msg_count OUT NOCOPY NUMBER ,
761 x_msg_data OUT NOCOPY VARCHAR2 ,
762 p_batch_id IN AR_BATCHES.batch_id%TYPE,
763 p_status IN AR_BATCHES.status%TYPE,
764 p_control_count IN AR_BATCHES.control_count%TYPE,
765 p_control_amount IN AR_BATCHES.control_amount%TYPE,
766 p_batch_applied_status IN AR_BATCHES.batch_applied_status%TYPE,
767 p_comments IN AR_BATCHES.comments%TYPE,
768 p_media_reference IN AR_BATCHES.media_reference%TYPE,
769 p_operation_request_id IN AR_BATCHES.operation_request_id%TYPE,
770 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
771 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
772 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
773 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
774 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE) IS
775
776 l_api_name CONSTANT varchar2(30) := 'update_remit';
777 l_api_version CONSTANT number := 1.0;
778
779 l_row_id varchar2(20) := NULL;
780 l_field varchar2(30);
781
782 l_batch_rec AR_BATCHES%ROWTYPE;
783
784 BEGIN
785
786 IF PG_DEBUG in ('Y', 'C') THEN
787 arp_util.debug('ARP_BR_REMIT_BATCHES.update_remit (+)');
788 END IF;
789
790 SAVEPOINT update_remit_PVT;
791
792 -- Standard call to check for call compatability
793 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795 END IF;
796
797 x_return_status := FND_API.G_RET_STS_SUCCESS;
798
799
800 IF (p_batch_id IS NULL) THEN
801 l_field := 'P_BATCH_ID';
802 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
803 FND_MESSAGE.set_token('PROCEDURE','batch id');
804 FND_MESSAGE.set_token('PARAMETER', l_field);
805 APP_EXCEPTION.raise_exception;
806 END IF;
807
808 -- fetch and lock of the batch row
809 l_batch_rec.batch_id := p_batch_id;
810 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
811
812 l_batch_rec.status := p_status;
813 l_batch_rec.control_count := p_control_count;
814 l_batch_rec.control_amount := p_control_amount;
815 l_batch_rec.batch_applied_status := p_batch_applied_status;
816 l_batch_rec.comments := p_comments;
817 l_batch_rec.media_reference := p_media_reference;
818 l_batch_rec.operation_request_id := p_operation_request_id;
819 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
820 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
821 l_batch_rec.bank_deposit_number := p_bank_deposit_number;
822 l_batch_rec.auto_print_program_id := p_auto_print_program_id;
823 l_batch_rec.auto_trans_program_id := p_auto_trans_program_id;
824
825 ------------------------------------------
826 -- Validation
827 ------------------------------------------
828 arp_br_remit_batches.validate_args_update_remit(l_batch_rec);
829
830 ------------------------------------------
831 -- Call update table handler
832 ------------------------------------------
833 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
834
835 IF PG_DEBUG in ('Y', 'C') THEN
836 arp_util.debug('ARP_BR_REMIT_BATCHES.update_remit (-)');
837 END IF;
838
839 EXCEPTION
840
841 WHEN FND_API.G_EXC_ERROR THEN
842 IF PG_DEBUG in ('Y', 'C') THEN
843 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_BR_REMIT_BATCHES.update_remit');
844 END IF;
845 x_return_status := FND_API.G_RET_STS_ERROR;
846 ROLLBACK TO update_remit_PVT;
847
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 IF PG_DEBUG in ('Y', 'C') THEN
850 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_BR_REMIT_BATCHES.update_remit');
851 END IF;
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853 ROLLBACK TO update_remit_PVT;
854
855 WHEN OTHERS THEN
856 IF PG_DEBUG in ('Y', 'C') THEN
857 arp_util.debug('EXCEPTION OTHERS:ARP_BR_REMIT_BATCHES.update_remit');
858 arp_util.debug('update_remit: ' || SQLERRM);
859 END IF;
860 ROLLBACK TO update_remit_PVT;
861 IF (SQLCODE = -20001) THEN
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 RETURN;
864 END IF;
865
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
867
868
869 END update_remit;
870
871
872 /*===========================================================================+
873 | PROCEDURE |
874 | validate_args_update_remit |
875 | |
876 | DESCRIPTION |
877 | Procedure called during the process create bills receivable |
878 | remittance to validate the remittance batch row |
879 | |
880 | SCOPE - PUBLIC |
881 | |
882 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
883 | |
884 | ARGUMENTS : IN : |
885 | |
886 | RETURNS : NONE |
887 | |
888 | NOTES |
889 | |
890 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
891 | |
892 +===========================================================================*/
893 PROCEDURE validate_args_update_remit(
894 p_batch_rec IN AR_BATCHES%ROWTYPE) IS
895
896 l_row_id varchar2(20) := NULL;
897 l_field varchar2(30) := NULL;
898
899 BEGIN
900
901 IF PG_DEBUG in ('Y', 'C') THEN
902 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_args_update_remit (+)');
903 END IF;
904
905 -- Check that the main columns are filled
906 IF (p_batch_rec.type IS NULL) THEN
907 l_field := 'TYPE';
908 ELSIF (p_batch_rec.currency_code IS NULL) THEN
909 l_field := 'CURRENCY_CODE';
910 ELSIF (p_batch_rec.batch_date IS NULL) THEN
911 l_field := 'BATCH_DATE';
912 ELSIF (p_batch_rec.gl_date IS NULL) THEN
913 l_field := 'GL_DATE';
914 ELSIF (p_batch_rec.remit_method_code IS NULL) THEN
915 l_field := 'REMIT_METHOD_CODE';
916 ELSIF (p_batch_rec.remittance_bank_branch_id IS NULL) THEN
917 l_field := 'REMITTANCE_BANK_BRANCH_ID';
918 ELSIF (p_batch_rec.batch_applied_status IS NULL) THEN
919 l_field := 'BATCH_APPLIED_STATUS';
920 ELSIF (NVL(p_batch_rec.with_recourse_flag,'T') NOT IN ('Y','N')) THEN
921 l_field := 'WITH_RECOURSE_FLAG';
922 END IF;
923
924 IF (l_field IS NOT NULL) THEN
925 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
926 FND_MESSAGE.set_token('PROCEDURE','validate_args_insert_remit');
927 FND_MESSAGE.set_token('PARAMETER', l_field);
928 APP_EXCEPTION.raise_exception;
929 END IF;
930
931 -- Check that the gl date is valid
932 arp_util.validate_gl_date(p_batch_rec.gl_date, NULL, NULL);
933
934 -- Check that the currency is valid
935 arp_br_remit_batches.validate_currency_code(p_batch_rec.currency_code,p_batch_rec.batch_date);
936
937 -- Check that the receipt method is valid
938 arp_br_remit_batches.validate_receipt_method(p_batch_rec.receipt_method_id, p_batch_rec.batch_date);
939
940 -- Check that the remittance bank branch is valid
941 arp_br_remit_batches.validate_remit_bank_branch(p_batch_rec.remittance_bank_branch_id,p_batch_rec.batch_date);
942
943 -- Check that the remittance account is valid
944 arp_br_remit_batches.validate_remit_bank_account(p_batch_rec.remit_bank_acct_use_id,p_batch_rec.batch_date);
945
946 -- Check that the remittance method and the flag with_recouse are consistent
947 arp_br_remit_batches.validate_remit_method_code(p_batch_rec.remit_method_code,p_batch_rec.with_recourse_flag);
948
949 -- Check that the format program is a type 'REMIT_TRANSMIT' program
950 arp_br_remit_batches.validate_auto_program(p_batch_rec.auto_trans_program_id);
951
952 IF PG_DEBUG in ('Y', 'C') THEN
953 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_args_update_remit (-)');
954 END IF;
955
956 EXCEPTION
957 WHEN OTHERS THEN
958 IF PG_DEBUG in ('Y', 'C') THEN
959 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_args_update_remit');
960 END IF;
961 RAISE;
962
963 END validate_args_update_remit;
964
965
966 /*===========================================================================+
967 | PROCEDURE |
968 | cancel_remit |
969 | |
970 | DESCRIPTION |
971 | Procedure called during the process create bills receivable |
972 | remittance to update the remittance batch row in the table AR_BATCHES |
973 | |
974 | SCOPE - PUBLIC |
975 | |
976 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
977 | |
978 | ARGUMENTS : IN : |
979 | |
980 | RETURNS : NONE |
981 | |
982 | NOTES |
983 | |
984 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
985 | |
986 +===========================================================================*/
987 PROCEDURE cancel_remit(
988 p_batch_id IN AR_BATCHES.batch_id%TYPE,
989 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
990
991 l_batch_rec AR_BATCHES%ROWTYPE;
992 l_ps_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
993
994 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
995
996
997 CURSOR cur_br IS
998 SELECT payment_schedule_id
999 FROM AR_PAYMENT_SCHEDULES
1000 WHERE reserved_type = 'REMITTANCE'
1001 AND reserved_value = p_batch_id;
1002
1003 BEGIN
1004
1005 IF PG_DEBUG in ('Y', 'C') THEN
1006 arp_util.debug('ARP_BR_REMIT_BATCHES.cancel_remit (+)');
1007 END IF;
1008
1009 -- fetch and lock of the batch row
1010 l_batch_rec.batch_id := p_batch_id;
1011 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
1012
1013 -- The remitted BR are removed from the remittance batch
1014 OPEN cur_br;
1015
1016 LOOP
1017 FETCH cur_br INTO l_ps_id;
1018 EXIT WHEN cur_br%NOTFOUND;
1019 IF PG_DEBUG in ('Y', 'C') THEN
1020 arp_util.debug('cancel_remit: ' || 'BR '|| l_ps_id ||' removed from the remittance '||p_batch_id);
1021 END IF;
1022 AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_ps_id,l_new_status);
1023 END LOOP;
1024
1025 CLOSE cur_br;
1026
1027 -- update the batch row with the batch applied status
1028 l_batch_rec.status := 'CL';
1029 l_batch_rec.batch_applied_status := 'COMPLETED_CANCELLATION';
1030 l_batch_rec.control_count := 0;
1031 l_batch_rec.control_amount := 0;
1032 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1033
1034 COMMIT;
1035
1036 p_batch_applied_status := l_batch_rec.batch_applied_status;
1037
1038 IF PG_DEBUG in ('Y', 'C') THEN
1039 arp_util.debug('ARP_BR_REMIT_BATCHES.cancel_remit (-)');
1040 END IF;
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 IF PG_DEBUG in ('Y', 'C') THEN
1045 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.cancel_remit');
1046 END IF;
1047
1048 IF cur_br%ISOPEN THEN
1049 CLOSE cur_br;
1050 END IF;
1051
1052 RAISE;
1053
1054
1055 END cancel_remit;
1056
1057
1058 /*===========================================================================+
1059 | PROCEDURE |
1060 | lock_remit |
1061 | |
1062 | DESCRIPTION |
1063 | Procedure called during the process create bills receivable |
1064 | remittance to lock the remittance batch row in the table AR_BATCHES |
1065 | |
1066 | SCOPE - PUBLIC |
1067 | |
1068 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1069 | |
1070 | ARGUMENTS : IN : |
1071 | |
1072 | RETURNS : NONE |
1073 | |
1074 | NOTES |
1075 | |
1076 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
1077 | |
1078 +===========================================================================*/
1079 PROCEDURE lock_remit (
1080 p_api_version IN NUMBER ,
1081 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1082 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1083 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1084 x_return_status OUT NOCOPY VARCHAR2 ,
1085 x_msg_count OUT NOCOPY NUMBER ,
1086 x_msg_data OUT NOCOPY VARCHAR2 ,
1087 p_rowid IN varchar2,
1088 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1089 p_batch_name IN AR_BATCHES.name%TYPE,
1090 p_status IN AR_BATCHES.status%TYPE,
1091 p_batch_date IN AR_BATCHES.batch_date%TYPE,
1092 p_gl_date IN AR_BATCHES.gl_date%TYPE,
1093 p_currency_code IN AR_BATCHES.currency_code%TYPE,
1094 p_comments IN AR_BATCHES.comments%TYPE,
1095 p_attribute_category IN AR_BATCHES.attribute_category%TYPE,
1096 p_attribute1 IN AR_BATCHES.attribute1%TYPE,
1097 p_attribute2 IN AR_BATCHES.attribute2%TYPE,
1098 p_attribute3 IN AR_BATCHES.attribute3%TYPE,
1099 p_attribute4 IN AR_BATCHES.attribute4%TYPE,
1100 p_attribute5 IN AR_BATCHES.attribute5%TYPE,
1101 p_attribute6 IN AR_BATCHES.attribute6%TYPE,
1102 p_attribute7 IN AR_BATCHES.attribute7%TYPE,
1103 p_attribute8 IN AR_BATCHES.attribute8%TYPE,
1104 p_attribute9 IN AR_BATCHES.attribute9%TYPE,
1105 p_attribute10 IN AR_BATCHES.attribute10%TYPE,
1106 p_media_reference IN AR_BATCHES.media_reference%TYPE,
1107 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
1108 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
1109 p_receipt_class_id IN AR_BATCHES.receipt_class_id%TYPE,
1110 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
1111 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
1112 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE,
1113 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
1114 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
1115 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE,
1116 p_batch_applied_status IN AR_BATCHES.batch_applied_status%TYPE) IS
1117
1118 l_api_name CONSTANT varchar2(30) := 'lock_remit';
1119 l_api_version CONSTANT number := 1.0;
1120
1121 l_batch_rec AR_BATCHES%ROWTYPE;
1122
1123
1124 BEGIN
1125
1126 IF PG_DEBUG in ('Y', 'C') THEN
1127 arp_util.debug('ARP_BR_REMIT_BATCHES.lock_remit (+)');
1128 END IF;
1129
1130 SAVEPOINT lock_remit_PVT;
1131
1132 -- Standard call to check for call compatability
1133 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137 x_return_status := FND_API.G_RET_STS_SUCCESS;
1138
1139 arp_cr_batches_pkg.fetch_p(p_batch_id,l_batch_rec);
1140
1141 l_batch_rec.name :=p_batch_name;
1142 l_batch_rec.status :=p_status;
1143 l_batch_rec.batch_date :=p_batch_date;
1144 l_batch_rec.gl_date :=p_gl_date;
1145 l_batch_rec.currency_code :=p_currency_code;
1146 l_batch_rec.comments :=p_comments;
1147 l_batch_rec.attribute_category :=p_attribute_category;
1148 l_batch_rec.attribute1 :=p_attribute1;
1149 l_batch_rec.attribute2 :=p_attribute2;
1150 l_batch_rec.attribute3 :=p_attribute3;
1151 l_batch_rec.attribute4 :=p_attribute4;
1152 l_batch_rec.attribute5 :=p_attribute5;
1153 l_batch_rec.attribute6 :=p_attribute6;
1154 l_batch_rec.attribute7 :=p_attribute7;
1155 l_batch_rec.attribute8 :=p_attribute8;
1156 l_batch_rec.attribute9 :=p_attribute9;
1157 l_batch_rec.attribute10 :=p_attribute10;
1158 l_batch_rec.media_reference :=p_media_reference;
1159 l_batch_rec.receipt_method_id :=p_receipt_method_id;
1160 l_batch_rec.remit_bank_acct_use_id :=p_remittance_bank_account_id;
1161 l_batch_rec.receipt_class_id :=p_receipt_class_id;
1162 l_batch_rec.remittance_bank_branch_id :=p_remittance_bank_branch_id;
1163 l_batch_rec.remit_method_code :=p_remit_method_code;
1164 l_batch_rec.with_recourse_flag :=p_with_recourse_flag;
1165 l_batch_rec.bank_deposit_number :=p_bank_deposit_number;
1166 l_batch_rec.auto_print_program_id :=p_auto_print_program_id;
1167 l_batch_rec.auto_trans_program_id :=p_auto_trans_program_id;
1168 l_batch_rec.batch_applied_status :=p_batch_applied_status;
1169
1170 /*-----------------------------------------------+
1171 | Call the Table Handler |
1172 +-----------------------------------------------*/
1173 arp_cr_batches_pkg.lock_p(
1174 p_rowid,
1175 l_batch_rec.set_of_books_id,
1176 l_batch_rec.batch_id,
1177 l_batch_rec.batch_applied_status,
1178 l_batch_rec.batch_date,
1179 l_batch_rec.batch_source_id,
1180 l_batch_rec.comments,
1181 l_batch_rec.control_amount,
1182 l_batch_rec.control_count,
1183 l_batch_rec.exchange_date,
1184 l_batch_rec.exchange_rate,
1185 l_batch_rec.exchange_rate_type,
1186 l_batch_rec.lockbox_batch_name,
1187 l_batch_rec.media_reference,
1188 l_batch_rec.operation_request_id,
1189 l_batch_rec.receipt_class_id,
1190 l_batch_rec.receipt_method_id,
1191 l_batch_rec.remit_method_code,
1192 l_batch_rec.remit_bank_acct_use_id,
1193 l_batch_rec.remittance_bank_branch_id,
1194 l_batch_rec.attribute_category,
1195 l_batch_rec.attribute1,
1196 l_batch_rec.attribute2,
1197 l_batch_rec.attribute3,
1198 l_batch_rec.attribute4,
1199 l_batch_rec.attribute5,
1200 l_batch_rec.attribute6,
1201 l_batch_rec.attribute7,
1202 l_batch_rec.attribute8,
1203 l_batch_rec.attribute9,
1204 l_batch_rec.attribute10,
1205 l_batch_rec.attribute11,
1206 l_batch_rec.attribute12,
1207 l_batch_rec.attribute13,
1208 l_batch_rec.attribute14,
1209 l_batch_rec.attribute15,
1210 l_batch_rec.request_id,
1211 l_batch_rec.transmission_id,
1212 l_batch_rec.bank_deposit_number);
1213
1214
1215 IF PG_DEBUG in ('Y', 'C') THEN
1216 arp_util.debug('ARP_BR_REMIT_BATCHES.lock_remit (-)');
1217 END IF;
1218
1219 EXCEPTION
1220
1221 WHEN FND_API.G_EXC_ERROR THEN
1222 IF PG_DEBUG in ('Y', 'C') THEN
1223 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_BR_REMIT_BATCHES.lock_remit');
1224 END IF;
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 ROLLBACK TO lock_remit_PVT;
1227
1228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1229 IF PG_DEBUG in ('Y', 'C') THEN
1230 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_BR_REMIT_BATCHES.lock_remit');
1231 END IF;
1232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233 ROLLBACK TO lock_remit_PVT;
1234
1235 WHEN OTHERS THEN
1236 IF PG_DEBUG in ('Y', 'C') THEN
1237 arp_util.debug('EXCEPTION OTHERS:ARP_BR_REMIT_BATCHES.lock_remit');
1238 arp_util.debug('lock_remit: ' || SQLERRM);
1239 END IF;
1240 ROLLBACK TO lock_remit_PVT;
1241 IF (SQLCODE = -20001) THEN
1242 x_return_status := FND_API.G_RET_STS_ERROR;
1243 RETURN;
1244 END IF;
1245
1246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1247
1248
1249 END lock_remit;
1250
1251 /*===========================================================================+
1252 | PROCEDURE |
1253 | update_br_remit_batch_to_crh |
1254 | |
1255 | DESCRIPTION |
1256 | This function is used to update the BR remittance batch id on the |
1257 | current cash receipt history record of the receipt created by BR |
1258 | remittance batch. |
1259 | |
1260 | SCOPE - PUBLIC |
1261 | |
1262 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1263 | arp_util.debug |
1264 | |
1265 | ARGUMENTS : IN: p_cr_id Cash receipt id of the receipt to be updated |
1266 | p_batch_id Value to be updated |
1267 | OUT: |
1268 | IN/ OUT: |
1269 | NOTES |
1270 | |
1271 | MODIFICATION HISTORY |
1272 | 5-SEP-2000 Jani Rautiainen Created |
1273 | |
1274 +===========================================================================*/
1275 PROCEDURE update_br_remit_batch_to_crh(p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1276 p_batch_id IN ar_cash_receipt_history.batch_id%TYPE) IS
1277
1278 /*--------------------------------------------+
1279 | Cursor to fetch the current cash receipt |
1280 | history record |
1281 +--------------------------------------------*/
1282 CURSOR receipt_cur IS
1283 SELECT cash_receipt_history_id
1284 FROM ar_cash_receipt_history
1285 WHERE cash_receipt_id = p_cr_id
1286 AND current_record_flag = 'Y';
1287
1288 receipt_rec receipt_cur%ROWTYPE;
1289 l_crh_rec ar_cash_receipt_history%ROWTYPE;
1290 l_crh_id ar_cash_receipt_history.cash_Receipt_history_id%TYPE;
1291
1292 BEGIN
1293 IF PG_DEBUG in ('Y', 'C') THEN
1294 arp_standard.debug('arp_br_remit_batches.update_br_remit_batch_to_crh()+');
1295 END IF;
1296
1297 /*--------------------------------------------+
1298 | If either of the parameters is null, skip |
1299 | processing |
1300 +--------------------------------------------*/
1301 IF p_batch_id is not null and p_cr_id is not null THEN
1302
1303 /*------------------------------------------------+
1304 | Fetch the current cash receipt history record |
1305 +------------------------------------------------*/
1306 OPEN receipt_cur;
1307 FETCH receipt_cur INTO receipt_rec;
1308
1309 IF receipt_cur%NOTFOUND THEN
1310
1311 /*------------------------------------------------+
1312 | Current cash receipt history record cannot be |
1313 | found, raise an error. |
1314 +------------------------------------------------*/
1315 IF PG_DEBUG in ('Y', 'C') THEN
1316 arp_standard.debug('update_br_remit_batch_to_crh: ' || 'current receipt history record cannot be found cr_id = '||to_char(p_cr_id));
1317 END IF;
1318 CLOSE receipt_cur;
1319 APP_EXCEPTION.raise_exception;
1320
1321 END IF;
1322
1323 CLOSE receipt_cur;
1324
1325 /*-----------------------------------+
1326 | Set local record to dummy values |
1327 +-----------------------------------*/
1328 ARP_CR_HISTORY_PKG.set_to_dummy(l_crh_rec);
1329
1330 l_crh_rec.batch_id := p_batch_id;
1331 l_crh_rec.cash_receipt_history_id := receipt_rec.cash_receipt_history_id;
1332
1333 /*-----------------------------------------+
1334 | Update the cash receipt history record |
1335 +-----------------------------------------*/
1336 ARP_CR_HISTORY_PKG.update_p(l_crh_rec,
1337 receipt_rec.cash_receipt_history_id);
1338 END IF;
1339
1340 IF PG_DEBUG in ('Y', 'C') THEN
1341 arp_standard.debug('arp_br_remit_batches.update_br_remit_batch_to_crh()-');
1342 END IF;
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 IF PG_DEBUG in ('Y', 'C') THEN
1347 arp_util.debug('EXCEPTION: arp_br_remit_batches.update_br_remit_batch_to_crh');
1348 END IF;
1349
1350 IF receipt_cur%ISOPEN THEN
1351 CLOSE receipt_cur;
1352 END IF;
1353
1354 RAISE;
1355
1356 END update_br_remit_batch_to_crh;
1357
1358
1359 /*===========================================================================+
1360 | FUNCTION |
1361 | revision |
1362 | |
1363 | DESCRIPTION |
1364 | This function returns the revision number of this package. |
1365 | |
1366 | SCOPE - PUBLIC |
1367 | |
1368 | RETURNS : Revision number of this package |
1369 | |
1370 | MODIFICATION HISTORY |
1371 | 10 JAN 2001 John HALL Created |
1372 +===========================================================================*/
1373 FUNCTION revision RETURN VARCHAR2 IS
1374 BEGIN
1375 RETURN '$Revision: 120.4 $';
1376 END revision;
1377 --
1378
1379
1380
1381 END ARP_BR_REMIT_BATCHES;
1382 --