[Home] [Help]
PACKAGE BODY: APPS.AR_BR_REMIT_IMPORT_API_PUB
Source
1 PACKAGE BODY AR_BR_REMIT_IMPORT_API_PUB AS
2 /* $Header: ARBRIMRB.pls 120.4 2004/04/07 23:42:00 anukumar ship $ */
3 /* =======================================================================
4 | Global Data Types |
5 * ======================================================================*/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IMPREMAPI';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14
15
16 /*---------------------------------------------------------------------------------------------------------------------------|
17 | PROCEDURE : Dummy_Remittance |
18 | |
19 | DESCRIPTION : |
20 | This procedure will examine a remittance batch to see if it is empty. If it is empty it will cancel it by caling the |
21 | routine ARP_BR_REMIT_BATCHES.cancel_remit(p_reserved_value,l_batch_applied_status). |
22 | |
23 |---------------------------------------------------------------------------------------------------------------------------*/
24
25 PROCEDURE Dummy_Remittance (p_reserved_value ar_payment_schedules.reserved_value%TYPE,x_return_status IN OUT NOCOPY VARCHAR2) is
26 count_brs number;
27 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
28 BEGIN
29 --Check if the remittance is empty, i.e. has no Br's assiged to i,
30
31 arp_standard.debug ('impremapi.dummy_remittance()+');
32
33 select count(*)
34 into count_brs
35 from ar_transaction_history
36 where batch_id = p_reserved_value
37 and current_record_flag = 'Y';
38
39 select batch_applied_status
40 into l_batch_applied_status
41 from ar_batches
42 where batch_id = p_reserved_value;
43
44 if count_brs = 0 and l_batch_applied_status in ('COMPLETED_CREATION','STARTED_CREATION') THEN
45 ARP_BR_REMIT_BATCHES.cancel_remit(p_reserved_value,l_batch_applied_status);
46 end if;
47
48 arp_standard.debug ('impremapi.dummy_remittance()-');
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 x_return_status := FND_API.G_RET_STS_ERROR;
53 ROLLBACK TO IMPORT_MAIN;
54 arp_standard.debug('EXCEPTION OTHERS: impremapi.Dummy_Remittance');
55 app_exception.raise_exception;
56 END;
57
58
59 /*---------------------------------------------------------------------------------------------------------------------------|
60 | PROCEDURE : Check_BR_and_Batch_Status |
61 | |
62 | DESCRIPTION : |
63 | Check that the BR is assigned to an approved remittance. |
64 |---------------------------------------------------------------------------------------------------------------------------*/
65 PROCEDURE Check_BR_and_Batch_Status (p_internal_reference IN RA_CUSTOMER_TRX.Customer_trx_id%TYPE,
66 p_reserved_value OUT NOCOPY AR_PAYMENT_SCHEDULES.reserved_value%TYPE,
67 p_payment_schedule_id OUT NOCOPY AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
68 p_media_reference OUT NOCOPY AR_BATCHES.media_reference%TYPE)
69 is
70
71 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
72 l_status AR_BATCHES.status%TYPE;
73 Invalid_Remittance_Status Exception;
74
75 BEGIN
76
77 arp_standard.debug ('impremapi.Check_BR_and_Batch_Status()+');
78
79 /*---------------------------------------------------------------------------------------
80 | Firstly check if the BR is currently assigned to an approved batch |
81 --------------------------------------------------------------------------------------*/
82
83 select max(reserved_value), -- remittance batch id null check
84 max(payment_schedule_id)
85 into p_reserved_value,
86 p_payment_schedule_id
87 from ar_payment_schedules
88 where customer_trx_id = p_internal_reference
89 and reserved_type = 'REMITTANCE';
90
91 IF p_reserved_value is null THEN
92 fnd_message.set_name('AR_BR','AR_BR_TRX_INVALID');
93 app_exception.raise_exception;
94 END IF;
95
96 Select batch_applied_status,
97 status,
98 media_reference
99 into l_batch_applied_status,
100 l_status,
101 p_media_reference
102 from ar_batches
103 where batch_id = p_reserved_value
104 and type = 'BR_REMITTANCE';
105
106 IF l_batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION') THEN
107 RAISE invalid_remittance_status;
108 END IF;
109
110 IF l_status <> 'OP' THEN
111 RAISE invalid_remittance_status;
112 END IF;
113
114 arp_standard.debug ('impremapi.Check_BR_and_Batch_Status()-');
115
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 ROLLBACK TO IMPORT_MAIN;
119 fnd_message.set_name ('AR_BR','AR_BR_TRX_INVALID');
120 arp_standard.debug ('EXCEPTION NO_DATA impremapi.Check_BR_and_Batch_Status');
121 app_exception.raise_exception;
122
123 WHEN invalid_remittance_status THEN
124 ROLLBACK TO IMPORT_MAIN;
125 fnd_message.set_name ('AR_BR','AR_BR_TRX_INVALID');
126 arp_standard.debug ('EXCEPTION invalid_remittance_status.Check_BR_and_Batch_Status');
127 app_exception.raise_exception;
128
129 WHEN OTHERS THEN
130 ROLLBACK TO IMPORT_MAIN;
131 arp_standard.debug ('EXCEPTION WHEN_OTHERS impremapi.Check_BR_and_Batch_Status');
132 app_exception.raise_exception;
133 END;
134
135 /*---------------------------------------------------------------------------------------------------------------------------|
136 | PROCEDURE : compare_old_versus_new_values |
137 | |
138 | DESCRIPTION : |
139 | a. Compares values on the database to values being supplied. For e.g. compares bank account details and if these differ the|
140 | n reports an appropriate error. |
141 | |
142 | CALLS TO EXTERNAL PROCEDURES : |
143 | |
144 | |
145 |----------------------------------------------------------------------------------------------------------------------------*/
146 PROCEDURE compare_old_versus_new_values (
147 p_media_reference IN ar_batches.media_reference%TYPE,
148 p_remittance_accounting_Date IN ar_batches.gl_date%TYPE,
149 p_remittance_method IN ar_batches.remit_method_code%TYPE,
150 p_with_recourse_flag IN ar_batches.with_recourse_flag%TYPE,
151 p_payment_method IN ar_receipt_methods.name%TYPE,
152 p_remittance_date IN ar_batches.batch_date%TYPE,
153 p_Currency_code IN ar_batches.currency_code%TYPE,
154 p_remittance_bnk_acct_number IN ce_bank_accounts.bank_account_num%TYPE,
155 l_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
156 l_batch_id OUT NOCOPY ar_batches.batch_id%TYPE
157 ) IS
158
159 l_remit_bank_acct_use_id ce_bank_acct_uses_all.bank_acct_use_id%TYPE;
160 l_currency_code ar_batches.currency_code%TYPE;
161 l_gl_date ar_batches.gl_date%type;
162 l_with_recourse_flag ar_batches.with_recourse_flag%TYPE;
163 l_batch_date ar_batches.batch_date%TYPE;
164 l_receipt_method_id ar_batches.receipt_method_id%TYPE;
165 l_remit_method_code ar_batches.remit_method_code%TYPE;
166 l_media_reference ar_batches.media_reference%TYPE;
167 l_reserved_value ar_payment_schedules.reserved_value%TYPE;
168 l_count NUMBER;
169 l_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE;
170 l_payment_method ar_receipt_methods.name%TYPE;
171 l_bank_account_num ce_bank_accounts.bank_account_num%TYPE;
172
173 BEGIN
174
175 arp_standard.debug ('impremapi.Compare_old_versus_new_values ()+');
176
177 select remit_bank_acct_use_id,
178 currency_code,
179 gl_date,
180 with_recourse_flag,
181 batch_date,
182 receipt_method_id,
183 remit_method_code,
184 batch_applied_status,
185 batch_id
186
187 into
188
189 l_remit_bank_acct_use_id,
190 l_currency_code,
191 l_gl_date,
192 l_with_recourse_flag,
193 l_batch_date,
194 l_receipt_method_id,
195 l_remit_method_code,
196 l_batch_applied_status,
197 l_batch_id
198
199 from ar_batches
200 where media_reference = p_media_reference
201 and gl_date = p_remittance_accounting_Date
202 and type = 'BR_REMITTANCE';
203
204
205 SELECT cba.bank_account_num INTO l_bank_account_num
206 FROM ce_bank_accounts cba,
207 ce_bank_acct_uses cbau
208 WHERE cbau.bank_acct_use_id =l_remit_bank_acct_use_id
209 AND cbau.bank_account_id = cba.bank_account_id;
210
211 If l_bank_account_num <> p_remittance_bnk_acct_number THEN
212 fnd_message.set_name
213 ( 'AR_BR','AR_BR_INVALID_PARAMETER');
214 FND_MESSAGE.set_token('PARAMETER',' bank account ');
215 app_exception.raise_exception;
216 END IF;
217
218 IF l_currency_code<>p_currency_code THEN
219 fnd_message.set_name
220 ('AR_BR','AR_BR_INVALID_PARAMETER');
221 FND_MESSAGE.set_token('PARAMETER',' currency code ');
222 app_exception.raise_exception;
223 END IF;
224
225 IF l_gl_date<>p_remittance_accounting_date THEN
226 fnd_message.set_name
227 ('AR_BR','AR_BR_INVALID_PARAMETER');
228 FND_MESSAGE.set_token('PARAMETER',' accounting date ');
229 app_exception.raise_exception;
230 END IF;
231
232 IF l_with_recourse_flag<>p_with_recourse_flag THEN
233 fnd_message.set_name
234 ('AR_BR','AR_BR_INVALID_PARAMETER');
235 FND_MESSAGE.set_token('PARAMETER',' recourse flag ');
236 app_exception.raise_exception;
237 END IF;
238
239
240 IF l_batch_date<>p_remittance_date THEN
241 fnd_message.set_name
242 ('AR_BR','AR_BR_INVALID_PARAMETER');
243 FND_MESSAGE.set_token('PARAMETER',' remittance date ');
244 app_exception.raise_exception;
245 END IF;
246
247 BEGIN
248 select name
249
250 into l_payment_method
251
252 from ar_receipt_methods
253 where receipt_method_id = l_receipt_method_id;
254
255
256 EXCEPTION
257 WHEN NO_DATA_FOUND THEN
258 fnd_message.set_name ( 'AR', 'AR_BR_INVALID_RECEIPT_METHOD');
259 app_exception.raise_exception;
260
261 END;
262
263 IF l_payment_method <> p_payment_method THEN
264 fnd_message.set_name
265 ('AR_BR','AR_BR_INVALID_PARAMETER');
266 FND_MESSAGE.set_token('PARAMETER',' payment method ');
267 app_exception.raise_exception;
268 END IF;
269
270 arp_standard.debug ('impremapi.Compare_old_versus_new_values ()-');
271
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 ROLLBACK TO IMPORT_MAIN;
275 arp_standard.debug ('EXCEPTION NO_DATA impremapi.Compare_old_versus_new_values');
276 app_exception.raise_exception;
277
278 WHEN OTHERS THEN
279 ROLLBACK TO IMPORT_MAIN;
280 arp_standard.debug ('EXCEPTION WHEN OTHERS impremapi.Compare_old_versus_new_values');
281 app_exception.raise_exception;
282
283 END;
284
285 /*---------------------------------------------------------------------------------------------------------------------------|
286 | PROCEDURE : Existing_Remittance |
287 | |
288 | DESCRIPTION : |
289 | a. Compares values on the database to values being supplied. For e.g. compares bank account details and if these differ the|
290 | n reports an appropriate error. |
291 | b. Checks that the BR supplied exists and if it does not reports an error. |
292 | c. Checks that the remittance is unapproved and if it is not reports an error. |
293 | d. If the BR is valid : |
294 | Checks whether the BR is already assigned to the remittance being imported |
295 | If it is assigned |
296 | does nothing |
297 | else |
298 | Calls the Assign_BR_To_Remittance procedure |
299 |e. If the BR is assigned to a different remittance |
300 | removes that assignment |
301 | makes a new assignment to the new remittance calling Assign_BR_To_Remittance |
302 |f. If there existed a previous assignment to a different remittance |
303 | calls the procedure Dummy_Remittance |
304 | |
305 | CALLS TO EXTERNAL PROCEDURES : |
306 | AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_payment_schedule_id,x_return_status); |
307 | AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(l_batch_id,l_payment_schedule_id,x_return_status); |
308 | |
309 |---------------------------------------------------------------------------------------------------------------------------*/
310
311 PROCEDURE existing_remittance (
312 p_media_reference IN ar_batches.media_reference%TYPE,
313 p_remittance_accounting_Date IN ar_batches.gl_date%TYPE,
314 p_internal_reference IN ra_customer_trx.customer_trx_id%TYPE,
315 p_remittance_method IN ar_batches.remit_method_code%TYPE,
316 p_with_recourse_flag IN ar_batches.with_recourse_flag%TYPE,
317 p_payment_method IN ar_receipt_methods.name%TYPE,
318 p_remittance_date IN ar_batches.batch_date%TYPE,
319 p_currency_code IN ar_batches.currency_code%TYPE,
320 p_remittance_bnk_acct_number IN ce_bank_accounts.bank_account_num%TYPE,
321 x_return_status IN OUT NOCOPY VARCHAR2
322 ) IS
323
324 l_media_reference AR_BATCHES.media_reference%TYPE;
325 l_reserved_value AR_PAYMENT_SCHEDULES.reserved_value%TYPE;
326 l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
327 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
328 l_batch_id AR_BATCHES.batch_id%TYPE;
329
330 Invalid_Br Exception;
331 invalid_Remittance_status Exception;
332
333 BEGIN
334
335 arp_standard.debug ('impremapi.Existing_Remittance ()+');
336
337 compare_old_versus_new_values(p_media_reference,
338 p_remittance_accounting_Date,
339 p_remittance_method,
340 p_with_recourse_flag,
341 p_payment_method,
342 p_remittance_date,
343 p_Currency_code,
344 p_remittance_bnk_acct_number,
345 l_batch_applied_status,
346 l_batch_id);
347
348 Check_BR_and_batch_status (p_internal_reference,l_reserved_value,l_payment_schedule_id,l_media_reference);
349
350 /*----------------------------------------------------------------------------------------
351 | if the media references differ then deassign from the existing remittance and assign |
352 | to the new one |
353 |---------------------------------------------------------------------------------------*/
354
355 IF l_media_reference <> p_media_reference or l_media_reference is null then
356 arp_standard.debug ('impremapi.Existing_Remittance - call deselect');
357 AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_payment_schedule_id,x_return_status);
358 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(l_batch_id,l_payment_schedule_id,x_return_status);
359 dummy_remittance(l_reserved_value, x_return_status);
360 END IF;
361
362 arp_standard.debug ('impremapi.Existing_Remittance ()-');
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 x_return_status := FND_API.G_RET_STS_ERROR;
367 ROLLBACK TO IMPORT_MAIN;
368 arp_standard.debug ('EXCEPTION WHEN OTHERS impremapi.Existing_Remittance');
369 app_exception.raise_exception;
370
371 END;
372
373 /*---------------------------------------------------------------------------------------------------------------------------|
374 | PROCEDURE : New_Remittance |
375 | |
376 | DESCRIPTION :
377 | |
378 | Validate the bank details supplied and ensure the receipt method is valid. Also check that the remittance print and |
379 | transmission programs are of the correct type. |
380 | |
381 | Insert the remittance into the databse by calling ARP_BR_REMIT_BATCHES.insert_remit. |
382 | |
383 | If the BR was already selected to a remittance then deselect it from that remittance. Check if the remittance is empty and |
384 | if it is then cancel it. |
385 | |
386 | CALLS TO EXTERNAL PROCEDURES :
387 | |
388 | ARP_BR_REMIT_BATCHES.insert_remit |
389 | AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit |
390 | |
391 |---------------------------------------------------------------------------------------------------------------------------*/
392
393 PROCEDURE new_remittance(
394 p_media_reference IN ar_batches.media_reference%TYPE,
395 p_remittance_accounting_date IN ar_batches.gl_date%TYPE,
396 p_remittance_Date IN ar_batches.batch_date%TYPE,
397 p_internal_reference IN ra_customer_trx.Customer_trx_id%TYPE,
398 p_with_recourse_flag IN ar_batches.with_recourse_flag%TYPE,
399 p_currency_code IN ar_batches.currency_code%TYPE,
400 p_remittance_method IN ar_batches.remit_method_code%TYPE,
401 p_remittance_bnk_branch_number IN ce_bank_branches_v.branch_number%TYPE,
402 p_remittance_bank_number IN ce_bank_branches_v.bank_number%TYPE,
403 p_remittance_bnk_acct_number IN ce_bank_accounts.bank_account_num%TYPE,
404 p_payment_method IN ar_receipt_methods.name%TYPE,
405 x_batch_name OUT NOCOPY ar_batches.name%TYPE,
406 x_return_status IN OUT NOCOPY VARCHAR2
407 ) IS
408
409 l_receipt_class_id ar_receipt_methods.receipt_class_id%TYPE;
410 l_auto_print_program_id ar_receipt_methods.auto_print_program_id%TYPE;
411 l_auto_trans_program_id ar_receipt_methods.auto_trans_program_id%TYPE;
412 l_bank_branch_id ce_bank_branches_v.branch_party_id%TYPE;
413 l_payment_method ar_batches.receipt_method_id%TYPE;
414 l_reserved_value ar_payment_schedules.reserved_value%TYPE;
415 l_batch_id ar_batches.batch_id%TYPE := null;
416 l_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE;
417 l_receipt_method_id ra_customer_trx.receipt_method_id%TYPE;
418 l_remit_bank_acct_use_id ce_bank_acct_uses_all.bank_acct_use_id%TYPE;
419 l_media_reference ar_batches.media_reference%TYPE;
420
421 x_batch_id ar_batches.batch_id%TYPE;
422 x_batch_applied_status ar_batches.batch_applied_status%TYPE;
423
424 CURSOR branch (p_bank_number NUMBER, p_branch_number NUMBER) IS
425 SELECT branch_party_id
426 FROM ce_bank_branches_v cbb
427 WHERE cbb.bank_number = p_bank_number
428 AND cbb.branch_number = p_branch_number;
429
430 CURSOR account (p_branch_id NUMBER,
431 p_bank_account_number NUMBER,
432 p_currency_code VARCHAR2) IS
433 SELECT cbau.bank_acct_use_id
434 FROM ce_bank_accounts cba, ce_bank_acct_uses cbau
435 WHERE cba.bank_account_id = cbau.bank_account_id
436 AND cba.bank_branch_id = p_branch_id
437 AND cba.bank_account_num = p_bank_account_number
438 AND cba.currency_code = p_currency_code
439 AND cba.account_classification = 'INTERNAL';
440
441 BEGIN
442
443 arp_standard.debug ('impremapi.New_Remittance ()+');
444
445 check_br_and_batch_status (p_internal_reference,
446 l_reserved_value,l_payment_schedule_id,l_media_reference);
447
448 /*----------------------------------------------------------------------------
449 | Setup data parameters for the create remittance procedure
450 ----------------------------------------------------------------------------*/
451
452 BEGIN
453
454 OPEN branch (p_remittance_bank_number, p_remittance_bnk_branch_number);
455 FETCH branch INTO l_bank_branch_id;
456 CLOSE branch;
457
458 EXCEPTION
459 WHEN NO_DATA_FOUND THEN
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 ROLLBACK TO IMPORT_MAIN;
462 arp_standard.debug('EXCEPTION Branch no_data : ' ||
463 'impremapi.New_Remittance');
464 fnd_message.set_name ( 'AR', 'AR_MUST_ENTER_REMIT_BANK');
465 app_exception.raise_exception;
466 END;
467
468 BEGIN
469
470 OPEN account (l_bank_branch_id,
471 p_remittance_bnk_branch_number,
472 p_currency_code);
473 FETCH account INTO l_remit_bank_acct_use_id;
474 CLOSE account;
475
476 EXCEPTION
477 WHEN NO_DATA_FOUND THEN
478 x_return_status := FND_API.G_RET_STS_ERROR;
479 ROLLBACK TO IMPORT_MAIN;
480 arp_standard.debug('EXCEPTION Bank acc id no_data : ' ||
481 'impremapi.New_Remittance');
482 fnd_message.set_name ( 'AR', 'Invalid Remittance Bank Branch');
483 app_exception.raise_exception;
484 END;
485
486 BEGIN
487 select receipt_method_id,
488 nvl(auto_print_program_id,0),
489 nvl(auto_trans_program_id,0),
490 receipt_class_id
491 into l_receipt_method_id
492 ,l_auto_print_program_id
493 ,l_auto_trans_program_id
494 ,l_receipt_class_id
495 from ar_receipt_methods
496 where name = p_payment_method;
497
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 x_return_status := FND_API.G_RET_STS_ERROR;
501 ROLLBACK TO IMPORT_MAIN;
502 arp_standard.debug('EXCEPTION Receipt Method : impremapi.New_Remittance');
503 fnd_message.set_name('AR','AR_BR_INVALID_REMIT_PROGRAM');
504 app_exception.raise_exception;
505
506 END;
507
508 BEGIN
509 select nvl(auto_print_program_id,0),
510 nvl(auto_trans_program_id,0)
511
512 into l_auto_print_program_id,
513 l_auto_trans_program_id
514
515 from ar_receipt_methods
516 where receipt_method_id = l_receipt_method_id;
517
518
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 ROLLBACK TO IMPORT_MAIN;
523 arp_standard.debug('EXCEPTION Receipt Method Progs : impremapi.New_Remittance');
524 fnd_message.set_name ( 'AR', 'AR_BR_INVALID_RECEIPT_METHOD');
525 app_exception.raise_exception;
526 END;
527
528 l_batch_id := null;
529
530 arp_standard.debug('Call insert_remit : impremapi.New_Remittance');
531
532 ARP_BR_REMIT_BATCHES.insert_remit(
533 p_remittance_date,
534 p_remittance_accounting_date,
535 p_currency_code,
536 null,
537 null,
538 null,
539 null,
540 null,
541 null,
542 null,
543 null,
544 null,
545 null,
546 null,
547 null,
548 p_media_reference,
549 l_receipt_method_id,
550 l_remit_bank_acct_use_id,
551 l_receipt_class_id,
552 l_bank_branch_id,
553 p_remittance_method,
554 p_with_recourse_flag,
555 null, -- p_bank_deposit_number
556 l_auto_print_program_id,
557 l_auto_trans_program_id,
558 x_batch_id,
559 x_batch_name,
560 x_batch_applied_status);
561
562 arp_standard.debug ('impremapi.New_Remittance Called Insert_remit');
563
564 /*---------------------------------------------------------------------------------------
565 | Deselect the BR from the batch it is currently assigned to |
566 ---------------------------------------------------------------------------------------*/
567
568 IF l_reserved_value is not null THEN
569 arp_standard.debug ('impremapi.New_Remittance : call Deselect_BR_Remit');
570 AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_payment_schedule_id,x_return_status);
571 arp_standard.debug ('impremapi.New_Remittance : called Deselect_BR_Remit');
572 dummy_remittance(l_reserved_value, x_return_status);
573 END IF;
574
575 /*---------------------------------------------------------------------------------------
576 | Assign the BR to the new remittance batch. |
577 ---------------------------------------------------------------------------------------*/
578 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(x_batch_id,l_payment_schedule_id,x_return_status);
579
580 arp_standard.debug ('impremapi.New_Remittance ()-');
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 x_return_status := FND_API.G_RET_STS_ERROR;
585 ROLLBACK TO IMPORT_MAIN;
586 arp_standard.debug('EXCEPTION OTHERS: impremapi.New_Remittance');
587 app_exception.raise_exception;
588 END;
589
590 /*---------------------------------------------------------------------------------------------------------------------------|
591 | FUNCTION : |
592 | Remittance_Exists |
593 | |
594 | DESCRIPTION : |
595 | Check if the remittance identified by the media reference (p_media_reference) exists |
596 | If it exists check the status of the batch to ensure it is approved. |
597 | |
598 | CALLS TO EXTERNAL PROCEDURES ; |
599 | |
600 |---------------------------------------------------------------------------------------------------------------------------*/
601 FUNCTION Remittance_Exists (p_media_reference AR_BATCHES.media_reference%TYPE) return boolean
602 IS
603
604 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
605 l_status AR_BATCHES.status%TYPE;
606
607 BEGIN
608
609 arp_standard.debug ('impremapi.remittance_Exists()+');
610
611 select batch_applied_status,
612 status
613 into
614 l_batch_applied_status,
615 l_status
616 from ar_batches
617 where media_reference = p_media_reference;
618
619 IF l_batch_applied_status in ('STARTED_CREATION', 'COMPLETED_CREATION')
620 AND l_status in ('OP','NB') THEN
621 return true;
622 ELSE
623 return false;
624 END IF;
625
626 arp_standard.debug ('impremapi.remittance_Exists()-');
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 arp_standard.debug ('EXCEPTION NO_DATA : impremapi.remittance_Exists');
631 return false;
632
633
634 WHEN OTHERS THEN
635 arp_standard.debug('EXCEPTION OTHERS: impremapi.remittance_Exists');
636 app_exception.raise_exception;
637
638 END;
639
640 /*---------------------------------------------------------------------------------------------------------------------------|
641 | PROCEDURE : |
642 | Import_Remittance_Main |
643 | |
644 | DESCRIPTION : |
645 | Declares global variables. |
646 | a. Carries out NOCOPY standard api functions : check version |
647 | set up error message stack |
648 | Setup Default data |
649 | initiate AR_DEBUG facility |
650 | b. Determines whether the remittance being imported exists already and calls the relevant subprogram |
651 | CALLS TO EXTERNAL PROCEDURES : |
652 | |
653 |---------------------------------------------------------------------------------------------------------------------------*/
654
655 PROCEDURE import_remittance_main (
656 p_api_version IN NUMBER,
657 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
658 p_commit IN VARCHAR2, -- := FND_API.G_FALSE,
659 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
660 x_return_status OUT NOCOPY VARCHAR2,
661 x_msg_count OUT NOCOPY NUMBER,
662 x_msg_data OUT NOCOPY VARCHAR2,
663 p_remittance_bank_number IN ce_bank_branches_v.bank_number%TYPE,
664 p_remittance_bnk_branch_number IN ce_bank_branches_v.branch_number%TYPE,
665 p_remittance_bnk_acct_number IN ce_bank_accounts.bank_account_num%TYPE,
666 p_media_reference IN ar_batches.media_reference%TYPE,
667 p_remittance_method IN ar_batches.remit_method_code%TYPE,
668 -- 'STANDARD' , 'FACTORING' etc.
669 p_with_recourse_flag IN ar_batches.with_recourse_flag%TYPE,
670 p_payment_method IN ar_receipt_methods.name%TYPE,
671 p_remittance_date IN ar_batches.batch_date%TYPE,
672 p_remittance_accounting_date IN ar_batches.gl_date%TYPE,
673 p_currency_code IN ar_batches.currency_code%TYPE,
674 p_internal_reference IN ra_customer_trx.Customer_trx_id%TYPE,
675 p_org_id IN Number default null,
676 p_remittance_name OUT NOCOPY ar_batches.name%TYPE
677 ) IS
678
679 l_api_name CONSTANT VARCHAR2(30) := 'IMPREMAPI';
680 l_api_version CONSTANT NUMBER := 1.0;
681 l_count NUMBER(10);
682 l_org_return_status VARCHAR2(1);
683 l_org_id NUMBER;
684
685 BEGIN
686
687 arp_standard.debug('Impremapi.Import_Remittance_Main (+)');
688
689 -- Standard start of API savepoint
690
691 SAVEPOINT IMPORT_MAIN;
692
693 -- Standard call to check for call compatibility
694 IF NOT FND_API.Compatible_API_Call(
695 l_api_version,
696 p_api_version,
697 l_api_name,
698 G_PKG_NAME )
699 THEN
700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
701 END IF;
702
703 -- Initialize message list if p_init_msg_list is set to TRUE
704
705 IF FND_API.to_Boolean( p_init_msg_list ) THEN
706 FND_MSG_PUB.initialize;
707 END IF;
708
709
710 -- Initialize return status to SUCCESS
711
712 x_return_status := FND_API.G_RET_STS_SUCCESS;
713
714
715
716 /* SSA change */
717 l_org_id := p_org_id;
718 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
719 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
720 p_return_status =>l_org_return_status);
721 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
724 p_count => x_msg_count,
725 p_data => x_msg_data);
726 ELSE
727 -- Start Of API Body
728
729
730 /*---------------------------------------------------------------------------------------
731 | Check whether the remittance exists |
732 ---------------------------------------------------------------------------------------*/
733
734 IF p_media_reference is null THEN
735 x_return_status := FND_API.G_RET_STS_ERROR;
736 arp_standard.debug('Impremapi.Import_Remittance_Main media_ref null');
737 --bug 1644533
738 fnd_message.set_name('AR_BR','AR_BR_MEDIA_REF_NULL');
739 app_exception.raise_exception;
740 end if;
741
742 /*---------------------------------------------------------------------------------------
743 | if the remittance does not exist, call the new_remittance procedure else call the |
744 | existing_remittance procedure. |
745 ---------------------------------------------------------------------------------------*/
746
747 IF not remittance_exists (p_media_reference) THEN
748 arp_standard.debug('Impremapi.Import_Remittance_Main new remittance');
749 new_remittance (p_media_reference,p_remittance_accounting_date,p_remittance_Date,p_internal_reference,
750 p_with_recourse_flag,p_currency_code,p_remittance_method,p_remittance_bnk_branch_number,
751 p_remittance_bank_number,p_remittance_bnk_acct_number,p_payment_method,p_remittance_name,
752 x_return_status);
753 else
754 arp_standard.debug('Impremapi.Import_Remittance_Main existing remittance');
755 existing_remittance (p_media_reference,p_remittance_accounting_Date,p_internal_reference,p_remittance_method,
756 p_with_recourse_flag,p_payment_method,p_remittance_date,p_Currency_code,
757 p_remittance_bnk_acct_number,x_return_status);
758 end if;
759
760 /*-----------------------------------------------+
761 | Standard check of p_commit |
762 ------------------------------------------------*/
763
764 IF FND_API.To_Boolean( p_commit ) THEN arp_standard.debug('committing');
765 Commit;
766 END IF;
767 END IF;
768
769 arp_standard.debug('Impremapi.Import_Remittance_Main (-)');
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 ROLLBACK TO IMPORT_MAIN;
774 arp_standard.debug('EXCEPTION OTHERS: impremapi.Import_Remittance_Main');
775 RAISE;
776
777 END;
778
779 END AR_BR_REMIT_IMPORT_API_PUB;
780