[Home] [Help]
PACKAGE BODY: APPS.ARP_BR_REMIT_BATCHES
Source
4 G_PKG_NAME CONSTANT varchar2(30) := 'ARP_BR_REMIT_BATCHES';
1 PACKAGE BODY ARP_BR_REMIT_BATCHES AS
2 /* $Header: ARBRRM1B.pls 120.4.12010000.6 2009/12/22 13:07:42 pbapna ship $*/
3
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 | PROCEDURE |
734 | delete_remit |
735 | |
736 | DESCRIPTION |
737 | Procedure called during the process delete bills receivable |
738 | remittance to delete the remittance batch row in the table AR_BATCHES |
739 | |
740 | SCOPE - PUBLIC |
741 | |
742 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
743 | |
744 | ARGUMENTS : IN : |
745 | |
746 | RETURNS : NONE |
747 | |
748 | NOTES |
749 | |
750 | MODIFICATION HISTORY - Created as part of bug 9147689 |
751 | |
752 +===========================================================================*/
753
754 PROCEDURE delete_remit( p_batch_id IN ar_batches.batch_id%TYPE ) IS
755 l_id NUMBER;
756 BEGIN
757 -- lock the records.
758 SELECT batch_id
759 INTO l_id
760 FROM ar_batches
761 WHERE batch_id = p_batch_id
762 FOR UPDATE NOWAIT;
763
764 ARP_CR_BATCHES_PKG.delete_p(p_batch_id);
765
766
770 arp_standard.debug('EXCEPTION: ARP_BR_REMIT_BATCHES.delete_remit');
767 EXCEPTION
768 WHEN OTHERS THEN
769 IF PG_DEBUG in ('Y', 'C') THEN
771 END IF;
772 RAISE;
773 END delete_remit;
774
775 /*===========================================================================+
776 | PROCEDURE |
777 | update_remit |
778 | |
779 | DESCRIPTION |
780 | Procedure called during the process create bills receivable |
781 | remittance to update the remittance batch row in the table AR_BATCHES |
782 | |
783 | SCOPE - PUBLIC |
784 | |
785 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
786 | |
787 | ARGUMENTS : IN : |
788 | |
789 | RETURNS : NONE |
790 | |
791 | NOTES |
792 | |
793 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
794 | |
795 +===========================================================================*/
796 PROCEDURE update_remit(
797 p_api_version IN NUMBER ,
798 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
799 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
800 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
801 x_return_status OUT NOCOPY VARCHAR2 ,
802 x_msg_count OUT NOCOPY NUMBER ,
803 x_msg_data OUT NOCOPY VARCHAR2 ,
804 p_batch_id IN AR_BATCHES.batch_id%TYPE,
805 p_status IN AR_BATCHES.status%TYPE,
806 p_control_count IN AR_BATCHES.control_count%TYPE,
807 p_control_amount IN AR_BATCHES.control_amount%TYPE,
808 p_batch_applied_status IN AR_BATCHES.batch_applied_status%TYPE,
809 p_comments IN AR_BATCHES.comments%TYPE,
810 p_media_reference IN AR_BATCHES.media_reference%TYPE,
811 p_operation_request_id IN AR_BATCHES.operation_request_id%TYPE,
812 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
813 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
814 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
815 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
816 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE) IS
817
818 l_api_name CONSTANT varchar2(30) := 'update_remit';
819 l_api_version CONSTANT number := 1.0;
820
821 l_row_id varchar2(20) := NULL;
822 l_field varchar2(30);
823
824 l_batch_rec AR_BATCHES%ROWTYPE;
825
826 BEGIN
827
828 IF PG_DEBUG in ('Y', 'C') THEN
829 arp_util.debug('ARP_BR_REMIT_BATCHES.update_remit (+)');
830 END IF;
831
832 SAVEPOINT update_remit_PVT;
833
834 -- Standard call to check for call compatability
835 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 END IF;
838
839 x_return_status := FND_API.G_RET_STS_SUCCESS;
840
841
842 IF (p_batch_id IS NULL) THEN
843 l_field := 'P_BATCH_ID';
844 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
845 FND_MESSAGE.set_token('PROCEDURE','batch id');
846 FND_MESSAGE.set_token('PARAMETER', l_field);
847 APP_EXCEPTION.raise_exception;
848 END IF;
849
850 -- fetch and lock of the batch row
851 l_batch_rec.batch_id := p_batch_id;
852 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
853
854 l_batch_rec.status := p_status;
855 l_batch_rec.control_count := p_control_count;
856 l_batch_rec.control_amount := p_control_amount;
857 l_batch_rec.batch_applied_status := p_batch_applied_status;
858 l_batch_rec.comments := p_comments;
859 l_batch_rec.media_reference := p_media_reference;
860 l_batch_rec.operation_request_id := p_operation_request_id;
861 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
862 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
863 l_batch_rec.bank_deposit_number := p_bank_deposit_number;
864 l_batch_rec.auto_print_program_id := p_auto_print_program_id;
865 l_batch_rec.auto_trans_program_id := p_auto_trans_program_id;
866
867 ------------------------------------------
868 -- Validation
869 ------------------------------------------
870 arp_br_remit_batches.validate_args_update_remit(l_batch_rec);
871
872 ------------------------------------------
873 -- Call update table handler
874 ------------------------------------------
875 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
876
877 IF PG_DEBUG in ('Y', 'C') THEN
878 arp_util.debug('ARP_BR_REMIT_BATCHES.update_remit (-)');
879 END IF;
880
881 EXCEPTION
882
883 WHEN FND_API.G_EXC_ERROR THEN
884 IF PG_DEBUG in ('Y', 'C') THEN
885 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_BR_REMIT_BATCHES.update_remit');
886 END IF;
890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
887 x_return_status := FND_API.G_RET_STS_ERROR;
888 ROLLBACK TO update_remit_PVT;
889
891 IF PG_DEBUG in ('Y', 'C') THEN
892 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_BR_REMIT_BATCHES.update_remit');
893 END IF;
894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
895 ROLLBACK TO update_remit_PVT;
896
897 WHEN OTHERS THEN
898 IF PG_DEBUG in ('Y', 'C') THEN
899 arp_util.debug('EXCEPTION OTHERS:ARP_BR_REMIT_BATCHES.update_remit');
900 arp_util.debug('update_remit: ' || SQLERRM);
901 END IF;
902 ROLLBACK TO update_remit_PVT;
903 IF (SQLCODE = -20001) THEN
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 RETURN;
906 END IF;
907
908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
909
910
911 END update_remit;
912
913
914 /*===========================================================================+
915 | PROCEDURE |
916 | validate_args_update_remit |
917 | |
918 | DESCRIPTION |
919 | Procedure called during the process create bills receivable |
920 | remittance to validate the remittance batch row |
921 | |
922 | SCOPE - PUBLIC |
923 | |
924 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
925 | |
926 | ARGUMENTS : IN : |
927 | |
928 | RETURNS : NONE |
929 | |
930 | NOTES |
931 | |
932 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
933 | |
934 +===========================================================================*/
935 PROCEDURE validate_args_update_remit(
936 p_batch_rec IN AR_BATCHES%ROWTYPE) IS
937
938 l_row_id varchar2(20) := NULL;
939 l_field varchar2(30) := NULL;
940
941 BEGIN
942
943 IF PG_DEBUG in ('Y', 'C') THEN
944 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_args_update_remit (+)');
945 END IF;
946
947 -- Check that the main columns are filled
948 IF (p_batch_rec.type IS NULL) THEN
949 l_field := 'TYPE';
950 ELSIF (p_batch_rec.currency_code IS NULL) THEN
951 l_field := 'CURRENCY_CODE';
952 ELSIF (p_batch_rec.batch_date IS NULL) THEN
953 l_field := 'BATCH_DATE';
954 ELSIF (p_batch_rec.gl_date IS NULL) THEN
955 l_field := 'GL_DATE';
956 ELSIF (p_batch_rec.remit_method_code IS NULL) THEN
957 l_field := 'REMIT_METHOD_CODE';
958 ELSIF (p_batch_rec.remittance_bank_branch_id IS NULL) THEN
959 l_field := 'REMITTANCE_BANK_BRANCH_ID';
960 ELSIF (p_batch_rec.batch_applied_status IS NULL) THEN
961 l_field := 'BATCH_APPLIED_STATUS';
962 ELSIF (NVL(p_batch_rec.with_recourse_flag,'T') NOT IN ('Y','N')) THEN
963 l_field := 'WITH_RECOURSE_FLAG';
964 END IF;
965
966 IF (l_field IS NOT NULL) THEN
967 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
968 FND_MESSAGE.set_token('PROCEDURE','validate_args_insert_remit');
969 FND_MESSAGE.set_token('PARAMETER', l_field);
970 APP_EXCEPTION.raise_exception;
971 END IF;
972
973 -- Check that the gl date is valid
974 arp_util.validate_gl_date(p_batch_rec.gl_date, NULL, NULL);
975
976 -- Check that the currency is valid
977 arp_br_remit_batches.validate_currency_code(p_batch_rec.currency_code,p_batch_rec.batch_date);
978
979 -- Check that the receipt method is valid
980 arp_br_remit_batches.validate_receipt_method(p_batch_rec.receipt_method_id, p_batch_rec.batch_date);
981
982 -- Check that the remittance bank branch is valid
983 arp_br_remit_batches.validate_remit_bank_branch(p_batch_rec.remittance_bank_branch_id,p_batch_rec.batch_date);
984
985 -- Check that the remittance account is valid
986 arp_br_remit_batches.validate_remit_bank_account(p_batch_rec.remit_bank_acct_use_id,p_batch_rec.batch_date);
987
988 -- Check that the remittance method and the flag with_recouse are consistent
989 arp_br_remit_batches.validate_remit_method_code(p_batch_rec.remit_method_code,p_batch_rec.with_recourse_flag);
990
991 -- Check that the format program is a type 'REMIT_TRANSMIT' program
992 arp_br_remit_batches.validate_auto_program(p_batch_rec.auto_trans_program_id);
993
994 IF PG_DEBUG in ('Y', 'C') THEN
995 arp_util.debug('ARP_BR_REMIT_BATCHES.validate_args_update_remit (-)');
996 END IF;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 IF PG_DEBUG in ('Y', 'C') THEN
1001 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.validate_args_update_remit');
1002 END IF;
1003 RAISE;
1004
1005 END validate_args_update_remit;
1006
1007
1008 /*===========================================================================+
1009 | PROCEDURE |
1010 | cancel_remit |
1014 | remittance to update the remittance batch row in the table AR_BATCHES |
1011 | |
1012 | DESCRIPTION |
1013 | Procedure called during the process create bills receivable |
1015 | |
1016 | SCOPE - PUBLIC |
1017 | |
1018 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1019 | |
1020 | ARGUMENTS : IN : |
1021 | |
1022 | RETURNS : NONE |
1023 | |
1024 | NOTES |
1025 | |
1026 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
1027 | |
1028 +===========================================================================*/
1029 PROCEDURE cancel_remit(
1030 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1031 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1032
1033 l_batch_rec AR_BATCHES%ROWTYPE;
1034 l_ps_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
1035
1036 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
1037
1038
1039 CURSOR cur_br IS
1040 SELECT payment_schedule_id
1041 FROM AR_PAYMENT_SCHEDULES
1042 WHERE reserved_type = 'REMITTANCE'
1043 AND reserved_value = p_batch_id;
1044
1045 BEGIN
1046
1047 IF PG_DEBUG in ('Y', 'C') THEN
1048 arp_util.debug('ARP_BR_REMIT_BATCHES.cancel_remit (+)');
1049 END IF;
1050
1051 -- fetch and lock of the batch row
1052 l_batch_rec.batch_id := p_batch_id;
1053 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
1054
1055 -- The remitted BR are removed from the remittance batch
1056 OPEN cur_br;
1057
1058 LOOP
1059 FETCH cur_br INTO l_ps_id;
1060 EXIT WHEN cur_br%NOTFOUND;
1061 IF PG_DEBUG in ('Y', 'C') THEN
1062 arp_util.debug('cancel_remit: ' || 'BR '|| l_ps_id ||' removed from the remittance '||p_batch_id);
1063 END IF;
1064 AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_ps_id,l_new_status);
1065 END LOOP;
1066
1067 CLOSE cur_br;
1068
1069 -- update the batch row with the batch applied status
1070 l_batch_rec.status := 'CL';
1071 l_batch_rec.batch_applied_status := 'COMPLETED_CANCELLATION';
1072 l_batch_rec.control_count := 0;
1073 l_batch_rec.control_amount := 0;
1074 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1075
1076 COMMIT;
1077
1078 p_batch_applied_status := l_batch_rec.batch_applied_status;
1079
1080 IF PG_DEBUG in ('Y', 'C') THEN
1081 arp_util.debug('ARP_BR_REMIT_BATCHES.cancel_remit (-)');
1082 END IF;
1083
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 IF PG_DEBUG in ('Y', 'C') THEN
1087 arp_util.debug('EXCEPTION OTHERS: ARP_BR_REMIT_BATCHES.cancel_remit');
1088 END IF;
1089
1090 IF cur_br%ISOPEN THEN
1091 CLOSE cur_br;
1092 END IF;
1093
1094 RAISE;
1095
1096
1097 END cancel_remit;
1098
1099
1100 /*===========================================================================+
1101 | PROCEDURE |
1102 | lock_remit |
1103 | |
1104 | DESCRIPTION |
1105 | Procedure called during the process create bills receivable |
1106 | remittance to lock the remittance batch row in the table AR_BATCHES |
1107 | |
1108 | SCOPE - PUBLIC |
1109 | |
1110 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1111 | |
1112 | ARGUMENTS : IN : |
1113 | |
1114 | RETURNS : NONE |
1115 | |
1116 | NOTES |
1117 | |
1118 | MODIFICATION HISTORY - Created by Mireille Flahaut - 22/05/2000 |
1119 | |
1120 +===========================================================================*/
1121 PROCEDURE lock_remit (
1122 p_api_version IN NUMBER ,
1123 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1124 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1125 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1126 x_return_status OUT NOCOPY VARCHAR2 ,
1127 x_msg_count OUT NOCOPY NUMBER ,
1128 x_msg_data OUT NOCOPY VARCHAR2 ,
1129 p_rowid IN varchar2,
1130 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1131 p_batch_name IN AR_BATCHES.name%TYPE,
1132 p_status IN AR_BATCHES.status%TYPE,
1133 p_batch_date IN AR_BATCHES.batch_date%TYPE,
1134 p_gl_date IN AR_BATCHES.gl_date%TYPE,
1135 p_currency_code IN AR_BATCHES.currency_code%TYPE,
1136 p_comments IN AR_BATCHES.comments%TYPE,
1137 p_attribute_category IN AR_BATCHES.attribute_category%TYPE,
1138 p_attribute1 IN AR_BATCHES.attribute1%TYPE,
1139 p_attribute2 IN AR_BATCHES.attribute2%TYPE,
1140 p_attribute3 IN AR_BATCHES.attribute3%TYPE,
1141 p_attribute4 IN AR_BATCHES.attribute4%TYPE,
1142 p_attribute5 IN AR_BATCHES.attribute5%TYPE,
1143 p_attribute6 IN AR_BATCHES.attribute6%TYPE,
1144 p_attribute7 IN AR_BATCHES.attribute7%TYPE,
1145 p_attribute8 IN AR_BATCHES.attribute8%TYPE,
1146 p_attribute9 IN AR_BATCHES.attribute9%TYPE,
1147 p_attribute10 IN AR_BATCHES.attribute10%TYPE,
1148 p_media_reference IN AR_BATCHES.media_reference%TYPE,
1149 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
1150 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
1151 p_receipt_class_id IN AR_BATCHES.receipt_class_id%TYPE,
1152 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
1153 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
1154 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE,
1155 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
1156 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
1157 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE,
1158 p_batch_applied_status IN AR_BATCHES.batch_applied_status%TYPE) IS
1159
1160 l_api_name CONSTANT varchar2(30) := 'lock_remit';
1161 l_api_version CONSTANT number := 1.0;
1162
1163 l_batch_rec AR_BATCHES%ROWTYPE;
1164
1165
1166 BEGIN
1167
1168 IF PG_DEBUG in ('Y', 'C') THEN
1169 arp_util.debug('ARP_BR_REMIT_BATCHES.lock_remit (+)');
1170 END IF;
1171
1172 SAVEPOINT lock_remit_PVT;
1173
1174 -- Standard call to check for call compatability
1175 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178
1179 x_return_status := FND_API.G_RET_STS_SUCCESS;
1180
1181 arp_cr_batches_pkg.fetch_p(p_batch_id,l_batch_rec);
1182
1183 l_batch_rec.name :=p_batch_name;
1184 l_batch_rec.status :=p_status;
1185 l_batch_rec.batch_date :=p_batch_date;
1186 l_batch_rec.gl_date :=p_gl_date;
1187 l_batch_rec.currency_code :=p_currency_code;
1188 l_batch_rec.comments :=p_comments;
1189 l_batch_rec.attribute_category :=p_attribute_category;
1190 l_batch_rec.attribute1 :=p_attribute1;
1191 l_batch_rec.attribute2 :=p_attribute2;
1192 l_batch_rec.attribute3 :=p_attribute3;
1193 l_batch_rec.attribute4 :=p_attribute4;
1194 l_batch_rec.attribute5 :=p_attribute5;
1195 l_batch_rec.attribute6 :=p_attribute6;
1196 l_batch_rec.attribute7 :=p_attribute7;
1197 l_batch_rec.attribute8 :=p_attribute8;
1198 l_batch_rec.attribute9 :=p_attribute9;
1199 l_batch_rec.attribute10 :=p_attribute10;
1200 l_batch_rec.media_reference :=p_media_reference;
1201 l_batch_rec.receipt_method_id :=p_receipt_method_id;
1202 l_batch_rec.remit_bank_acct_use_id :=p_remittance_bank_account_id;
1203 l_batch_rec.receipt_class_id :=p_receipt_class_id;
1204 l_batch_rec.remittance_bank_branch_id :=p_remittance_bank_branch_id;
1205 l_batch_rec.remit_method_code :=p_remit_method_code;
1206 l_batch_rec.with_recourse_flag :=p_with_recourse_flag;
1207 l_batch_rec.bank_deposit_number :=p_bank_deposit_number;
1208 l_batch_rec.auto_print_program_id :=p_auto_print_program_id;
1209 l_batch_rec.auto_trans_program_id :=p_auto_trans_program_id;
1210 l_batch_rec.batch_applied_status :=p_batch_applied_status;
1211
1212 /*-----------------------------------------------+
1213 | Call the Table Handler |
1214 +-----------------------------------------------*/
1215 arp_cr_batches_pkg.lock_p(
1216 p_rowid,
1217 l_batch_rec.set_of_books_id,
1218 l_batch_rec.batch_id,
1219 l_batch_rec.batch_applied_status,
1220 l_batch_rec.batch_date,
1221 l_batch_rec.batch_source_id,
1222 l_batch_rec.comments,
1223 l_batch_rec.control_amount,
1224 l_batch_rec.control_count,
1225 l_batch_rec.exchange_date,
1226 l_batch_rec.exchange_rate,
1227 l_batch_rec.exchange_rate_type,
1228 l_batch_rec.lockbox_batch_name,
1229 l_batch_rec.media_reference,
1230 l_batch_rec.operation_request_id,
1231 l_batch_rec.receipt_class_id,
1232 l_batch_rec.receipt_method_id,
1233 l_batch_rec.remit_method_code,
1234 l_batch_rec.remit_bank_acct_use_id,
1235 l_batch_rec.remittance_bank_branch_id,
1236 l_batch_rec.attribute_category,
1237 l_batch_rec.attribute1,
1238 l_batch_rec.attribute2,
1239 l_batch_rec.attribute3,
1240 l_batch_rec.attribute4,
1241 l_batch_rec.attribute5,
1242 l_batch_rec.attribute6,
1243 l_batch_rec.attribute7,
1244 l_batch_rec.attribute8,
1245 l_batch_rec.attribute9,
1246 l_batch_rec.attribute10,
1247 l_batch_rec.attribute11,
1248 l_batch_rec.attribute12,
1249 l_batch_rec.attribute13,
1250 l_batch_rec.attribute14,
1251 l_batch_rec.attribute15,
1252 l_batch_rec.request_id,
1253 l_batch_rec.transmission_id,
1254 l_batch_rec.bank_deposit_number);
1255
1256
1257 IF PG_DEBUG in ('Y', 'C') THEN
1258 arp_util.debug('ARP_BR_REMIT_BATCHES.lock_remit (-)');
1259 END IF;
1260
1261 EXCEPTION
1262
1263 WHEN FND_API.G_EXC_ERROR THEN
1264 IF PG_DEBUG in ('Y', 'C') THEN
1265 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_BR_REMIT_BATCHES.lock_remit');
1266 END IF;
1267 x_return_status := FND_API.G_RET_STS_ERROR;
1268 ROLLBACK TO lock_remit_PVT;
1269
1270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1271 IF PG_DEBUG in ('Y', 'C') THEN
1272 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_BR_REMIT_BATCHES.lock_remit');
1273 END IF;
1274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1275 ROLLBACK TO lock_remit_PVT;
1276
1277 WHEN OTHERS THEN
1278 IF PG_DEBUG in ('Y', 'C') THEN
1279 arp_util.debug('EXCEPTION OTHERS:ARP_BR_REMIT_BATCHES.lock_remit');
1280 arp_util.debug('lock_remit: ' || SQLERRM);
1281 END IF;
1282 ROLLBACK TO lock_remit_PVT;
1283 IF (SQLCODE = -20001) THEN
1284 x_return_status := FND_API.G_RET_STS_ERROR;
1285 RETURN;
1286 END IF;
1287
1288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1289
1290
1291 END lock_remit;
1292
1293 /*===========================================================================+
1294 | PROCEDURE |
1295 | update_br_remit_batch_to_crh |
1296 | |
1297 | DESCRIPTION |
1298 | This function is used to update the BR remittance batch id on the |
1299 | current cash receipt history record of the receipt created by BR |
1300 | remittance batch. |
1301 | |
1302 | SCOPE - PUBLIC |
1303 | |
1304 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1305 | arp_util.debug |
1306 | |
1307 | ARGUMENTS : IN: p_cr_id Cash receipt id of the receipt to be updated |
1308 | p_batch_id Value to be updated |
1309 | OUT: |
1310 | IN/ OUT: |
1311 | NOTES |
1312 | |
1313 | MODIFICATION HISTORY |
1314 | 5-SEP-2000 Jani Rautiainen Created |
1315 | |
1316 +===========================================================================*/
1317 PROCEDURE update_br_remit_batch_to_crh(p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1318 p_batch_id IN ar_cash_receipt_history.batch_id%TYPE) IS
1319
1320 /*--------------------------------------------+
1321 | Cursor to fetch the current cash receipt |
1322 | history record |
1323 +--------------------------------------------*/
1324 CURSOR receipt_cur IS
1325 SELECT cash_receipt_history_id
1326 FROM ar_cash_receipt_history
1327 WHERE cash_receipt_id = p_cr_id
1328 AND current_record_flag = 'Y';
1329
1330 receipt_rec receipt_cur%ROWTYPE;
1331 l_crh_rec ar_cash_receipt_history%ROWTYPE;
1332 l_crh_id ar_cash_receipt_history.cash_Receipt_history_id%TYPE;
1333
1334 BEGIN
1335 IF PG_DEBUG in ('Y', 'C') THEN
1336 arp_standard.debug('arp_br_remit_batches.update_br_remit_batch_to_crh()+');
1337 END IF;
1338
1339 /*--------------------------------------------+
1340 | If either of the parameters is null, skip |
1341 | processing |
1342 +--------------------------------------------*/
1343 IF p_batch_id is not null and p_cr_id is not null THEN
1344
1345 /*------------------------------------------------+
1346 | Fetch the current cash receipt history record |
1347 +------------------------------------------------*/
1348 OPEN receipt_cur;
1349 FETCH receipt_cur INTO receipt_rec;
1350
1351 IF receipt_cur%NOTFOUND THEN
1352
1353 /*------------------------------------------------+
1354 | Current cash receipt history record cannot be |
1355 | found, raise an error. |
1356 +------------------------------------------------*/
1357 IF PG_DEBUG in ('Y', 'C') THEN
1358 arp_standard.debug('update_br_remit_batch_to_crh: ' || 'current receipt history record cannot be found cr_id = '||to_char(p_cr_id));
1359 END IF;
1360 CLOSE receipt_cur;
1361 APP_EXCEPTION.raise_exception;
1362
1363 END IF;
1364
1365 CLOSE receipt_cur;
1366
1367 /*-----------------------------------+
1368 | Set local record to dummy values |
1369 +-----------------------------------*/
1370 ARP_CR_HISTORY_PKG.set_to_dummy(l_crh_rec);
1371
1372 l_crh_rec.batch_id := p_batch_id;
1373 l_crh_rec.cash_receipt_history_id := receipt_rec.cash_receipt_history_id;
1374
1375 /*-----------------------------------------+
1376 | Update the cash receipt history record |
1377 +-----------------------------------------*/
1378 ARP_CR_HISTORY_PKG.update_p(l_crh_rec,
1379 receipt_rec.cash_receipt_history_id);
1380 END IF;
1381
1382 IF PG_DEBUG in ('Y', 'C') THEN
1383 arp_standard.debug('arp_br_remit_batches.update_br_remit_batch_to_crh()-');
1384 END IF;
1385
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 IF PG_DEBUG in ('Y', 'C') THEN
1389 arp_util.debug('EXCEPTION: arp_br_remit_batches.update_br_remit_batch_to_crh');
1390 END IF;
1391
1392 IF receipt_cur%ISOPEN THEN
1393 CLOSE receipt_cur;
1394 END IF;
1395
1396 RAISE;
1397
1398 END update_br_remit_batch_to_crh;
1399
1400
1401 /*===========================================================================+
1402 | FUNCTION |
1403 | revision |
1404 | |
1405 | DESCRIPTION |
1406 | This function returns the revision number of this package. |
1407 | |
1408 | SCOPE - PUBLIC |
1409 | |
1410 | RETURNS : Revision number of this package |
1411 | |
1412 | MODIFICATION HISTORY |
1413 | 10 JAN 2001 John HALL Created |
1414 +===========================================================================*/
1415 FUNCTION revision RETURN VARCHAR2 IS
1416 BEGIN
1417 RETURN '$Revision: 120.4.12010000.6 $';
1418 END revision;
1419 --
1420
1421
1422
1423 END ARP_BR_REMIT_BATCHES;
1424 --