[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_BR_REMIT
Source
1 PACKAGE BODY ARP_PROCESS_BR_REMIT AS
2 /* $Header: ARBRRMAB.pls 120.9 2010/07/15 13:08:12 dgaurab ship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'ARP_PROCESS_BR_REMIT';
5
6 TYPE CUR_TYP IS REF CURSOR;
7
8 /*-------------- Private procedures used by the package --------------------*/
9
10
11 /*--------------------------- Public procedures --------------------------*/
12
13 /*===========================================================================+
14 | PROCEDURE |
15 | create_remit_batch |
16 | |
17 | DESCRIPTION |
18 | Procedure called during the process create bills receivable |
19 | remittance to create automatically a remittance batch |
20 | |
21 | SCOPE - PUBLIC |
22 | |
23 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
24 | |
25 | ARGUMENTS : IN : |
26 | |
27 | RETURNS : NONE |
28 | |
29 | VERSION : Current version 1.0 |
30 | Initial version 1.0 |
31 | |
32 | NOTES |
33 | |
34 | MODIFICATION HISTORY - Created by Mireille Flahaut - 23/05/2000 |
35 | |
36 +===========================================================================*/
37 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
38
39 PROCEDURE create_remit_batch (
40 p_api_version IN NUMBER ,
41 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
42 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44 x_return_status OUT NOCOPY VARCHAR2 ,
45 x_msg_count OUT NOCOPY NUMBER ,
46 x_msg_data OUT NOCOPY VARCHAR2 ,
47 p_batch_date IN AR_BATCHES.batch_date%TYPE,
48 p_gl_date IN AR_BATCHES.gl_date%TYPE,
49 p_currency_code IN AR_BATCHES.currency_code%TYPE,
50 p_comments IN AR_BATCHES.comments%TYPE,
51 p_attribute_category IN AR_BATCHES.attribute_category%TYPE,
52 p_attribute1 IN AR_BATCHES.attribute1%TYPE,
53 p_attribute2 IN AR_BATCHES.attribute2%TYPE,
54 p_attribute3 IN AR_BATCHES.attribute3%TYPE,
55 p_attribute4 IN AR_BATCHES.attribute4%TYPE,
56 p_attribute5 IN AR_BATCHES.attribute5%TYPE,
57 p_attribute6 IN AR_BATCHES.attribute6%TYPE,
58 p_attribute7 IN AR_BATCHES.attribute7%TYPE,
59 p_attribute8 IN AR_BATCHES.attribute8%TYPE,
60 p_attribute9 IN AR_BATCHES.attribute9%TYPE,
61 p_attribute10 IN AR_BATCHES.attribute10%TYPE,
62 p_media_reference IN AR_BATCHES.media_reference%TYPE,
63 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
64 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
65 p_receipt_class_id IN AR_BATCHES.receipt_class_id%TYPE,
66 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
67 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
68 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE,
69 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
70 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
71 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE,
72 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
73 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
74 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
75 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
76 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
77 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
78 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
79 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
80 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
81 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
82 p_unsigned_flag IN varchar2,
83 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
84 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
85 p_include_unpaid_flag IN varchar2,
86 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
87 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
88 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
89 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
90 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
91 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
92 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
93 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
94 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
95 p_br_sort_criteria IN varchar2,
96 p_br_order IN varchar2,
97 p_drawee_sort_criteria IN varchar2,
98 p_drawee_order IN varchar2,
99 p_batch_id OUT NOCOPY AR_BATCHES.batch_id%TYPE,
100 p_batch_name OUT NOCOPY AR_BATCHES.name%TYPE,
101 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
102 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
103 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
104
105
106 l_api_name CONSTANT varchar2(30) := 'create_remit_batch';
107 l_api_version CONSTANT number := 1.0;
108
109 CUR_BR CUR_TYP;
110 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
111
112 l_batch_rec AR_BATCHES%ROWTYPE;
113 l_batch_id AR_BATCHES.batch_id%TYPE;
114 l_batch_name AR_BATCHES.name%TYPE;
115 l_control_count AR_BATCHES.control_count%TYPE;
116 l_control_amount AR_BATCHES.control_amount%TYPE;
117 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
118
119 l_select_detail varchar2(25000) := NULL;
120 l_field varchar2(30) := NULL;
121
122 total_count number;
123 total_amount number;
124
125 BEGIN
126
127 IF PG_DEBUG in ('Y', 'C') THEN
128 arp_util.debug('ARP_PROCESS_BR_REMIT.create_remit_batch (+)');
129 END IF;
130
131 SAVEPOINT create_remit_batch_PVT;
132
133 -- Standard call to check for call compatability
134 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 -- BR search criteria validation
141 ARP_PROCESS_BR_REMIT.validate_br_search_criteria(
142 p_remit_total_low,
143 p_remit_total_high,
144 p_maturity_date_low,
145 p_maturity_date_high,
146 p_br_number_low,
147 p_br_number_high,
148 p_br_amount_low,
149 p_br_amount_high,
150 p_transaction_type1_id,
151 p_transaction_type2_id,
152 p_unsigned_flag,
153 p_signed_flag,
154 p_drawee_issued_flag,
155 p_include_unpaid_flag,
156 p_drawee_id,
157 p_drawee_number_low,
158 p_drawee_number_high,
159 p_drawee_class1_code,
160 p_drawee_class2_code,
161 p_drawee_class3_code,
162 p_drawee_bank_name,
163 p_drawee_bank_branch_id,
164 p_drawee_branch_city,
165 p_br_sort_criteria,
166 p_br_order,
167 p_drawee_sort_criteria,
168 p_drawee_order);
169
170 -- The necessary BR select statements are built
171 ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (
172 p_transaction_type1_id,
173 p_transaction_type2_id,
174 p_drawee_class1_code,
175 p_drawee_class2_code,
176 p_drawee_class3_code,
177 p_drawee_bank_name,
178 p_drawee_bank_branch_id,
179 p_drawee_branch_city,
180 p_unsigned_flag,
181 p_signed_flag,
182 p_drawee_issued_flag,
183 p_br_sort_criteria,
184 p_br_order,
185 p_drawee_sort_criteria,
186 p_drawee_order,
187 l_select_detail);
188
189 -- IF some BR are selected with the user parameters,
190 -- the remittance is inserted in AR_BATCHES and the insert is committed
191 OPEN CUR_BR FOR l_select_detail
192 USING p_include_unpaid_flag,
193 p_batch_date,
194 p_gl_date,
195 p_currency_code,
196 p_remittance_bank_account_id,
197 p_maturity_date_low,
198 p_maturity_date_high,
199 p_br_number_low,
200 p_br_number_high,
201 p_br_amount_low,
202 p_br_amount_high,
203 p_drawee_id,
204 p_drawee_number_low,
205 p_drawee_number_high;
206
207 TOTAL_COUNT := 0;
208 TOTAL_AMOUNT := 0;
209
210 IF (p_remit_total_high IS NULL) THEN
211 LOOP
212 FETCH CUR_BR INTO l_ps_rec;
213 EXIT WHEN CUR_BR%NOTFOUND;
214 TOTAL_COUNT := TOTAL_COUNT + 1;
215 TOTAL_AMOUNT := TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0);
216 END LOOP;
217 ELSE
218 LOOP
219 FETCH CUR_BR INTO l_ps_rec;
220 EXIT WHEN CUR_BR%NOTFOUND;
221 IF (TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0) <= p_remit_total_high) THEN
222 TOTAL_COUNT := TOTAL_COUNT + 1;
223 TOTAL_AMOUNT := TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0);
224 END IF;
225 END LOOP;
226 END IF;
227
228 CLOSE CUR_BR;
229
230 IF ((p_remit_total_low IS NULL) AND (TOTAL_COUNT > 0)) OR
231 ((p_remit_total_low IS NOT NULL) AND (TOTAL_AMOUNT BETWEEN p_remit_total_low AND p_remit_total_high) AND (TOTAL_COUNT > 0)) THEN
232 -- Some BR have been selected -> Insert of the remittance batch row in the table AR_BATCHES
233 ARP_BR_REMIT_BATCHES.insert_remit(
234 p_batch_date,
235 p_gl_date,
236 p_currency_code,
237 p_comments,
238 p_attribute_category,
239 p_attribute1,
240 p_attribute2,
241 p_attribute3,
242 p_attribute4,
243 p_attribute5,
244 p_attribute6,
245 p_attribute7,
246 p_attribute8,
247 p_attribute9,
248 p_attribute10,
249 p_media_reference,
250 p_receipt_method_id,
251 p_remittance_bank_account_id,
252 p_receipt_class_id,
253 p_remittance_bank_branch_id,
254 p_remit_method_code,
255 p_with_recourse_flag,
256 p_bank_deposit_number,
257 p_auto_print_program_id,
258 p_auto_trans_program_id,
259 l_batch_id,
260 l_batch_name,
261 l_batch_applied_status);
262 IF PG_DEBUG in ('Y', 'C') THEN
263 arp_util.debug('create_remit_batch: ' || 'Commit -- Insertion du Batch '||l_batch_id||' status '||l_batch_applied_status);
264 arp_util.debug('create_remit_batch: ' || 'NB selected BR :'|| TOTAL_COUNT);
265 arp_util.debug('create_remit_batch: ' || 'Remittance total amount :'|| TOTAL_AMOUNT);
266 END IF;
267 ELSE
268 -- NO BR have been selected -> the remittance batch row isn't inserted in the table AR_BATCHES
269 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_REMIT');
270 APP_EXCEPTION.raise_exception;
271 END IF;
272
273 p_batch_id := l_batch_id;
274 p_batch_name := l_batch_name;
275 p_batch_applied_status := l_batch_applied_status;
276 p_control_count := 0;
277 p_control_amount := 0;
278
279 COMMIT;
280
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_util.debug('ARP_PROCESS_BR_REMIT.create_remit_batch (-)');
283 END IF;
284
285
286 EXCEPTION
287
288 WHEN FND_API.G_EXC_ERROR THEN
289 IF PG_DEBUG in ('Y', 'C') THEN
290 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.create_remit_batch');
291 END IF;
292 ROLLBACK TO create_remit_batch_PVT;
293
294 IF CUR_BR%ISOPEN THEN
295 CLOSE CUR_BR;
296 END IF;
297
298 x_return_status := FND_API.G_RET_STS_ERROR;
299
300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
301 IF PG_DEBUG in ('Y', 'C') THEN
302 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.create_remit_batch');
303 END IF;
304 ROLLBACK TO create_remit_batch_PVT;
305
306 IF CUR_BR%ISOPEN THEN
307 CLOSE CUR_BR;
308 END IF;
309
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311
312 WHEN OTHERS THEN
313 IF PG_DEBUG in ('Y', 'C') THEN
314 arp_util.debug('EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.create_remit_batch');
315 arp_util.debug('create_remit_batch: ' || SQLERRM);
316 END IF;
317 ROLLBACK TO create_remit_batch_PVT;
318
319 IF CUR_BR%ISOPEN THEN
320 CLOSE CUR_BR;
321 END IF;
322
323 IF (SQLCODE = -20001) THEN
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 RETURN;
326 END IF;
327
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 RAISE;
330
331
332 END create_remit_batch;
333
334
335
336 /*===========================================================================+
337 | PROCEDURE |
338 | select_and_assign_br_to_remit |
339 | |
340 | DESCRIPTION |
341 | Procedure called during the process create bills receivable |
342 | remittance process, to select the bills and assign them |
343 | to the remittance |
344 | |
345 | SCOPE - PRIVATE |
346 | |
347 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
348 | |
349 | |
350 | |
351 | ARGUMENTS : IN : |
352 | : OUT NOCOPY : NONE |
353 | |
354 | RETURNS : NONE |
355 | |
356 | NOTES |
357 | |
358 | MODIFICATION HISTORY - Created by Mireille Flahaut - 15/06/2000 |
359 | |
360 +===========================================================================*/
361 PROCEDURE select_and_assign_br_to_remit(
362 p_api_version IN NUMBER ,
363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
364 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
365 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
366 x_return_status OUT NOCOPY VARCHAR2 ,
367 x_msg_count OUT NOCOPY NUMBER ,
368 x_msg_data OUT NOCOPY VARCHAR2 ,
369 p_batch_id IN AR_BATCHES.batch_id%TYPE,
370 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
371 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
372 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
373 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
374 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
375 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
376 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
377 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
378 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
379 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
380 p_unsigned_flag IN varchar2,
381 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
382 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
383 p_include_unpaid_flag IN varchar2,
384 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
385 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
386 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
387 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
388 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
389 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
390 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
391 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
392 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
393 p_br_sort_criteria IN varchar2,
394 p_br_order IN varchar2,
395 p_drawee_sort_criteria IN varchar2,
396 p_drawee_order IN varchar2,
397 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
398 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
399 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
400
401 l_api_name CONSTANT varchar2(30) := 'select_and_assign_br_to_remit';
402 l_api_version CONSTANT number := 1.0;
403
404 l_batch_rec AR_BATCHES%ROWTYPE;
405
406 l_select_detail varchar2(25000);
407
408 l_control_count AR_BATCHES.control_count%TYPE;
409 l_control_amount AR_BATCHES.control_amount%TYPE;
410
411 BEGIN
412
413 IF PG_DEBUG in ('Y', 'C') THEN
414 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.select_and_assign_br_to_remit (+)');
415 END IF;
416
417 SAVEPOINT select_and_assign_PVT;
418
419 -- Standard call to check for call compatability
420 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425
426 -- lock and fetch of the batch row
427 l_batch_rec.batch_id := p_batch_id;
428 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
429
430 -- The action Create is enabled only if the batch status is STARTED_CREATION
431 IF l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION') THEN
432 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_RECREATE_REMIT');
433 APP_EXCEPTION.raise_exception;
434 END IF;
435
436 -- BR search criteria validation
437 ARP_PROCESS_BR_REMIT.validate_br_search_criteria(
438 p_remit_total_low,
439 p_remit_total_high,
440 p_maturity_date_low,
441 p_maturity_date_high,
442 p_br_number_low,
443 p_br_number_high,
444 p_br_amount_low,
445 p_br_amount_high,
446 p_transaction_type1_id,
447 p_transaction_type2_id,
448 p_unsigned_flag,
449 p_signed_flag,
450 p_drawee_issued_flag,
451 p_include_unpaid_flag,
452 p_drawee_id,
453 p_drawee_number_low,
454 p_drawee_number_high,
455 p_drawee_class1_code,
456 p_drawee_class2_code,
457 p_drawee_class3_code,
458 p_drawee_bank_name,
459 p_drawee_bank_branch_id,
460 p_drawee_branch_city,
461 p_br_sort_criteria,
462 p_br_order,
463 p_drawee_sort_criteria,
464 p_drawee_order);
465
466 -- The necessary BR select statements are built
467 ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (
468 p_transaction_type1_id,
469 p_transaction_type2_id,
470 p_drawee_class1_code,
471 p_drawee_class2_code,
472 p_drawee_class3_code,
473 p_drawee_bank_name,
474 p_drawee_bank_branch_id,
475 p_drawee_branch_city,
476 p_unsigned_flag,
477 p_signed_flag,
478 p_drawee_issued_flag,
479 p_br_sort_criteria,
480 p_br_order,
481 p_drawee_sort_criteria,
482 p_drawee_order,
483 l_select_detail);
484
485 -- The selected BR are assigned to the remittance by updating the reserved columns in the table AR_PAYMENT_SCHEDULES
486 -- until the parameter remittance maximum amount is reached (If it is filled of course).
487 ARP_PROCESS_BR_REMIT.assign_br_to_remit(
488 l_select_detail,
489 l_batch_rec.batch_id,
490 p_remit_total_high,
491 p_include_unpaid_flag,
492 l_batch_rec.batch_date,
493 l_batch_rec.gl_date,
494 l_batch_rec.currency_code,
495 l_batch_rec.remit_bank_acct_use_id,
496 p_maturity_date_low,
497 p_maturity_date_high,
498 p_br_number_low,
499 p_br_number_high,
500 p_br_amount_low,
501 p_br_amount_high,
502 p_unsigned_flag,
503 p_signed_flag,
504 p_drawee_issued_flag,
505 p_drawee_id,
506 p_drawee_number_low,
507 p_drawee_number_high,
508 l_control_count,
509 l_control_amount);
510
511 -- update the batch row with the control count and the control amount
512 l_batch_rec.control_count := l_control_count;
513 l_batch_rec.control_amount := l_control_amount;
514 l_batch_rec.batch_applied_status := 'COMPLETED_CREATION';
515 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
516
517 p_batch_applied_status := l_batch_rec.batch_applied_status;
518 p_control_count := l_batch_rec.control_count;
519 p_control_amount := l_batch_rec.control_amount;
520
521 IF PG_DEBUG in ('Y', 'C') THEN
522 arp_util.debug('create_remit_batch: ' || 'BR Remittance number :'||l_batch_rec.name);
523 arp_util.debug('create_remit_batch: ' || 'Count :'||l_control_count);
524 arp_util.debug('create_remit_batch: ' || 'Amount :'||l_control_amount);
525 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.select_and_assign_br_to_remit (+)');
526 END IF;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 IF PG_DEBUG in ('Y', 'C') THEN
531 arp_util.debug('create_remit_batch: ' || 'EXCEPTION : ARP_PROGRAM_BR_REMIT.select_and_assign_br_to_remit - ROLLBACK');
532 END IF;
533 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
534 ROLLBACK TO select_and_assign_PVT;
535 RAISE;
536
537 END select_and_assign_br_to_remit;
538
539
540
541 /*===========================================================================+
542 | PROCEDURE |
543 | construct_select_br_for_remit |
544 | |
545 | DESCRIPTION |
546 | Procedure called during the process create bills receivable |
547 | remittance to build the BR select statement according the entered |
548 | criteria |
549 | |
550 | SCOPE - PUBLIC |
551 | |
552 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
553 | |
554 | ARGUMENTS : IN : |
555 | |
556 | RETURNS : NONE |
557 | |
558 | NOTES |
559 | |
560 | MODIFICATION HISTORY - Created by Mireille Flahaut - 20/04/2000 |
561 | |
562 +===========================================================================*/
563 PROCEDURE construct_select_br_for_remit (
564 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
565 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
566 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
567 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
568 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
569 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
570 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
571 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
572 p_unsigned_flag IN varchar2,
573 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
574 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
575 p_br_sort_criteria IN varchar2,
576 p_br_order IN varchar2,
577 p_drawee_sort_criteria IN varchar2,
578 p_drawee_order IN varchar2,
579 p_select_detail OUT NOCOPY varchar2) IS
580
581 l_field varchar2(30) := NULL;
582
583 l_where_clause varchar2(5000);
584 l_order_clause varchar2(5000);
585
586 l_flag_yes varchar2(1) := 'Y';
587 l_flag_no varchar2(1) := 'N';
588
589 l_ps_status_opened AR_BATCHES.status%TYPE := 'OP';
590 l_ps_class AR_PAYMENT_SCHEDULES.CLASS%TYPE := 'BR';
591
592 l_pending_remittance AR_TRANSACTION_HISTORY.status%TYPE := 'PENDING_REMITTANCE';
593 l_unpaid AR_TRANSACTION_HISTORY.status%TYPE := 'UNPAID';
594
595 BEGIN
596
597 IF PG_DEBUG in ('Y', 'C') THEN
598 arp_util.debug('create_remit_batch: ' || '-----------ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (+)-------------');
599 END IF;
600
601 /*-------------------------------------------------------------------------------------*/
602 /* clause Where set up */
603 /*-------------------------------------------------------------------------------------*/
604 l_where_clause := 'WHERE PS.status LIKE '||''''|| l_ps_status_opened || ''' ';
605 l_where_clause := l_where_clause || 'AND PS.class LIKE '||''''|| l_ps_class || ''' ';
606 l_where_clause := l_where_clause || 'AND PS.customer_trx_id = TRX.customer_trx_id ';
607 l_where_clause := l_where_clause || 'AND TRX.drawee_id = CUST.cust_account_id ';
608 l_where_clause := l_where_clause || 'AND CUST.party_id = PARTY.party_id ';
609 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id = TRX_TYPE.cust_trx_type_id ';
610 l_where_clause := l_where_clause || 'AND PS.customer_trx_id = HS.customer_trx_id ';
611 l_where_clause := l_where_clause || 'AND HS.current_record_flag = '||''''|| l_flag_yes || ''' ';
612 l_where_clause := l_where_clause || 'AND TRX.drawee_bank_account_id = ACC.bank_account_id(+) ';
613 l_where_clause := l_where_clause || 'AND ACC.bank_branch_id = BRANCH.branch_party_id(+) ';
614 l_where_clause := l_where_clause || 'AND ((HS.status LIKE '||''''|| l_pending_remittance ||''') '||
615 'OR (HS.status LIKE '||''''|| l_unpaid ||''' '||'AND :include_unpaid_flag = '||''''|| l_flag_yes ||''' )) ';
616 l_where_clause := l_where_clause || 'AND PS.reserved_type IS NULL ';
617 l_where_clause := l_where_clause || 'AND PS.reserved_value IS NULL ';
618 -- Bug9578330
619 l_where_clause := l_where_clause || 'AND HS1.transaction_history_id = ' ||
620 '(SELECT MAX(transaction_history_id) FROM AR_TRANSACTION_HISTORY WHERE customer_trx_id = PS.customer_trx_id ' ||
621 ' AND event NOT IN (''SELECTED_REMITTANCE'', ''DESELECTED_REMITTANCE''))';
622 -- bug6050275
623 /*
624 l_where_clause := l_where_clause || 'AND :batch_date >= HS.trx_date ';
625 l_where_clause := l_where_clause || 'AND :batch_gl_date >= HS.gl_date ';
626 */
627
628 l_where_clause := l_where_clause || 'AND :batch_date >= HS1.trx_date ';
629 l_where_clause := l_where_clause || 'AND :batch_gl_date >= HS1.gl_date ';
630
631 -- Batch parameters
632 l_where_clause := l_where_clause || 'AND PS.invoice_currency_code = :currency_code ';
633 l_where_clause := l_where_clause || 'AND (( TRX.remit_bank_acct_use_id IS NULL) OR '
634 ||'(TRX.remit_bank_acct_use_id = :remittance_bank_account_id AND TRX.override_remit_account_flag = '||''''|| l_flag_no ||''''||') OR '
635 ||'(TRX.override_remit_account_flag = '||''''|| l_flag_yes ||''''||')) ';
636
637 -- BR parameters
638 l_where_clause := l_where_clause || 'AND PS.due_date BETWEEN NVL(:maturity_date_low,PS.due_date) AND NVL(:maturity_date_high,PS.due_date) ';
639 l_where_clause := l_where_clause || 'AND PS.trx_number BETWEEN NVL(:br_number_low,PS.trx_number) AND NVL(:br_number_high,PS.trx_number) ';
640 l_where_clause := l_where_clause || 'AND PS.amount_due_remaining BETWEEN NVL(:br_amount_low,PS.amount_due_remaining) AND NVL(:br_amount_high,PS.amount_due_remaining) ';
641
642 -- Criteria Signed_flag and Drawee_issued_flag
643 -- If both are N, All types (Signed, drawee_issued and unsigned) are selected
644 IF (p_unsigned_flag = 'Y' AND p_signed_flag = 'Y' AND p_drawee_issued_flag = 'Y') THEN
645 NULL;
646 ELSIF (p_unsigned_flag = 'Y' AND p_signed_flag = 'Y') THEN
647 l_where_clause := l_where_clause || 'AND ((TRX_TYPE.signed_flag = ''N'' AND TRX_TYPE.drawee_issued_flag = ''N'') OR (TRX_TYPE.signed_flag = ''Y'')) ';
648 ELSIF (p_unsigned_flag = 'Y' AND p_drawee_issued_flag = 'Y') THEN
649 l_where_clause := l_where_clause || 'AND ((TRX_TYPE.signed_flag = ''N'' AND TRX_TYPE.drawee_issued_flag = ''N'') OR (TRX_TYPE.drawee_issued_flag = ''Y'')) ';
650 ELSIF (p_signed_flag = 'Y' AND p_drawee_issued_flag = 'Y') THEN
651 l_where_clause := l_where_clause || 'AND (TRX_TYPE.signed_flag = ''Y'' OR TRX_TYPE.drawee_issued_flag = ''Y'') ';
652 ELSIF (p_unsigned_flag = 'Y') THEN
653 l_where_clause := l_where_clause || 'AND (TRX_TYPE.signed_flag = ''N'' AND TRX_TYPE.drawee_issued_flag = ''N'') ';
654 ELSIF (p_signed_flag = 'Y') THEN
655 l_where_clause := l_where_clause || 'AND TRX_TYPE.signed_flag = ''Y'' ';
656 ELSIF (p_drawee_issued_flag = 'Y') THEN
657 l_where_clause := l_where_clause || 'AND TRX_TYPE.drawee_issued_flag = ''Y'' ';
658 END IF;
659
660 -- Transaction types
661 IF (p_transaction_type1_id IS NOT NULL) AND (p_transaction_type2_id IS NOT NULL) THEN
662 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id IN ('||p_transaction_type1_id||','||p_transaction_type2_id||') ';
663 ELSIF (p_transaction_type1_id IS NOT NULL) THEN
664 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id IN ('||p_transaction_type1_id||') ';
665 ELSIF (p_transaction_type2_id IS NOT NULL) THEN
666 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id IN ('||p_transaction_type2_id||') ';
667 END IF;
668
669 ----------------------------------------------------
670 -- Drawee parameters
671 ----------------------------------------------------
672
673 l_where_clause := l_where_clause || 'AND TRX.drawee_id LIKE NVL(:drawee_id,TRX.drawee_id) ';
674 l_where_clause := l_where_clause || 'AND CUST.account_number BETWEEN NVL(:drawee_number_low,CUST.account_number) AND NVL(:drawee_number_high,CUST.account_number) ';
675
676 -- the drawee bank account information is optional on a BR
677 IF (p_drawee_bank_name IS NOT NULL) THEN
678 l_where_clause := l_where_clause || 'AND BRANCH.bank_name LIKE '||''''||p_drawee_bank_name||''' ';
679 END IF;
680
681 IF (p_drawee_bank_branch_id IS NOT NULL) THEN
682 l_where_clause := l_where_clause || 'AND BRANCH.branch_party_id = '||p_drawee_bank_branch_id||' ';
683 END IF;
684
685 IF (p_drawee_branch_city IS NOT NULL) THEN
686 l_where_clause := l_where_clause || 'AND BRANCH.city LIKE '||''''||p_drawee_branch_city||''' ';
687 END IF;
688
689 -- Drawee classes
690 IF (p_drawee_class1_code IS NOT NULL) AND (p_drawee_class2_code IS NOT NULL) AND (p_drawee_class3_code IS NOT NULL) THEN
691 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
692 ''''||p_drawee_class1_code||''','''||p_drawee_class2_code||''','''|| p_drawee_class3_code||''') ';
693 ELSIF (p_drawee_class1_code IS NOT NULL) AND (p_drawee_class2_code IS NOT NULL) THEN
694 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
695 ''''||p_drawee_class1_code||''','''||p_drawee_class2_code||''') ';
696 ELSIF (p_drawee_class1_code IS NOT NULL) AND (p_drawee_class3_code IS NOT NULL) THEN
697 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
698 ''''||p_drawee_class1_code||''','''||p_drawee_class3_code||''') ';
699 ELSIF (p_drawee_class2_code IS NOT NULL) AND (p_drawee_class3_code IS NOT NULL) THEN
700 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
701 ''''||p_drawee_class2_code||''','''||p_drawee_class3_code||''') ';
702 ELSIF (p_drawee_class1_code IS NOT NULL) THEN
703 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
704 ''''||p_drawee_class1_code||''') ';
705 ELSIF (p_drawee_class2_code IS NOT NULL) THEN
706 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
707 ''''||p_drawee_class2_code||''') ';
708 ELSIF (p_drawee_class3_code IS NOT NULL) THEN
709 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
710 ''''||p_drawee_class3_code||''') ';
711 END IF;
712
713 IF PG_DEBUG in ('Y', 'C') THEN
714 arp_util.debug('create_remit_batch: ' || 'Where clause completed.');
715 END IF;
716
717
718 /*-------------------------------------------------------------------------------------*/
719 /* clause Order set up */
720 /*-------------------------------------------------------------------------------------*/
721 l_order_clause := 'ORDER BY PS.due_date ASC';
722
723 IF (NOT p_br_sort_criteria IS NULL) THEN
724 l_order_clause := l_order_clause || ',' || p_br_sort_criteria || ' ' || p_br_order;
725 END IF;
726
727 IF (NOT p_drawee_sort_criteria IS NULL) THEN
728 l_order_clause := l_order_clause || ',' || p_drawee_sort_criteria || ' ' || p_drawee_order;
729 END IF;
730
731 IF PG_DEBUG in ('Y', 'C') THEN
732 arp_util.debug('create_remit_batch: ' || 'Order by clause completed.');
733 END IF;
734
735
736 /*-------------------------------------------------------------------------------------*/
737 /* Select */
738 /*-------------------------------------------------------------------------------------*/
739 /* Bug 3424656 Modified the following SELECT to pick all
740 the columns from PS rather than all the columns using
741 the wildcard character. This was giving ORA-00932
742 Inconsitent Datatypes errors in 10g. */
743
744 p_select_detail := 'SELECT PS.* ' ||
745 'FROM AR_PAYMENT_SCHEDULES PS, ' ||
746 'AR_TRANSACTION_HISTORY HS, ' ||
747 'HZ_CUST_ACCOUNTS CUST, ' ||
748 'HZ_PARTIES PARTY, ' ||
749 'RA_CUST_TRX_TYPES TRX_TYPE, ' ||
750 'RA_CUSTOMER_TRX TRX, ' ||
751 'AP_BANK_ACCOUNTS ACC, ' ||
752 'CE_BANK_BRANCHES_V BRANCH, ' ||
753 'AR_TRANSACTION_HISTORY HS1 ' ||
754 l_where_clause ||
755 l_order_clause;
756
757 IF PG_DEBUG in ('Y', 'C') THEN
758 arp_util.debug('create_remit_batch: ' || '-----------ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (-)-------------');
759 END IF;
760
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 IF PG_DEBUG in ('Y', 'C') THEN
765 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.construct_select_br_for_remit ');
766 END IF;
767 RAISE;
768
769 END construct_select_br_for_remit;
770
771
772
773 /*===========================================================================+
774 | PROCEDURE |
775 | validate_br_search_criteria |
776 | |
777 | DESCRIPTION |
778 | Procedure called during the process create bills receivable |
779 | remittance to validate the br search criteria |
780 | |
781 | SCOPE - PUBLIC |
782 | |
783 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
784 | |
785 | ARGUMENTS : IN : |
786 | |
787 | RETURNS : NONE |
788 | |
789 | NOTES |
790 | |
791 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
792 | |
793 | 20-Apr-2004 Debbie Sue Jancis Fixed Bug 3550612, changed the check |
794 | on p_drawee_sort_criteria to check |
795 | for party_name and account_number |
796 | instead of customer name and |
797 | customer number |
798 +===========================================================================*/
799 PROCEDURE validate_br_search_criteria(
800 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
801 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
802 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
803 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
804 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
805 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
806 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
807 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
808 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
809 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
810 p_unsigned_flag IN varchar2,
811 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
812 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
813 p_include_unpaid_flag IN varchar2,
814 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
815 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
816 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
817 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
818 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
819 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
820 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
821 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
822 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
823 p_br_sort_criteria IN varchar2,
824 p_br_order IN varchar2,
825 p_drawee_sort_criteria IN varchar2,
826 p_drawee_order IN varchar2) IS
827
828 l_field varchar2(30) := NULL;
829
830 BEGIN
831
832 IF PG_DEBUG in ('Y', 'C') THEN
833 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.validate_br_search_criteria (+)');
834 END IF;
835
836 -- remittance total amount
837 IF (p_remit_total_low IS NULL) AND (p_remit_total_high IS NULL) THEN
838 IF PG_DEBUG in ('Y', 'C') THEN
839 arp_util.debug('create_remit_batch: ' || 'p_remit_total_low and p_remit_total_high are NULL');
840 END IF;
841 ELSIF (p_remit_total_low IS NULL) OR
842 (p_remit_total_high IS NULL) OR
843 (p_remit_total_high < p_remit_total_low) THEN
844 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_REMIT_AMOUNT');
845 APP_EXCEPTION.raise_exception;
846 END IF;
847
848 -- maturity date
849 IF (p_maturity_date_low IS NULL) AND (p_maturity_date_high IS NULL) THEN
850 IF PG_DEBUG in ('Y', 'C') THEN
851 arp_util.debug('create_remit_batch: ' || 'p_maturity_date_low and p_maturity_date_high are NULL');
852 END IF;
853 ELSIF (p_maturity_date_low IS NULL) OR
854 (p_maturity_date_high IS NULL) OR
855 (p_maturity_date_high < p_maturity_date_low) THEN
856 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_BR_DUE_DATE');
857 APP_EXCEPTION.raise_exception;
858 END IF;
859
860 -- BR number
861 IF (p_br_number_low IS NULL) AND (p_br_number_high IS NULL) THEN
862 IF PG_DEBUG in ('Y', 'C') THEN
863 arp_util.debug('create_remit_batch: ' || 'p_br_number_low and p_br_number_high are NULL');
864 END IF;
865 ELSIF (p_br_number_low IS NULL) OR
866 (p_br_number_high IS NULL) OR
867 (p_br_number_high < p_br_number_low) THEN
868 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_BR_NUMBER');
869 APP_EXCEPTION.raise_exception;
870 END IF;
871
872 -- BR amount
873 IF (p_br_amount_low IS NULL) AND (p_br_amount_high IS NULL) THEN
874 IF PG_DEBUG in ('Y', 'C') THEN
875 arp_util.debug('create_remit_batch: ' || 'p_br_amount_low and p_br_amount_high are NULL');
876 END IF;
877 ELSIF (p_br_amount_low IS NULL) OR
878 (p_br_amount_high IS NULL) OR
879 (p_br_amount_high < p_br_amount_low) THEN
880 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_BR_AMOUNT');
881 APP_EXCEPTION.raise_exception;
882 END IF;
883
884 -- Drawee number
885 IF (p_drawee_number_low IS NULL) AND (p_drawee_number_high IS NULL) THEN
886 IF PG_DEBUG in ('Y', 'C') THEN
887 arp_util.debug('create_remit_batch: ' || 'p_drawee_number_low and p_drawee_number_high are NULL');
888 END IF;
889 ELSIF (p_drawee_number_low IS NULL) OR
890 (p_drawee_number_high IS NULL) OR
891 (p_drawee_number_high < p_drawee_number_low) THEN
892 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_DRAWEE_NUMBER');
893 APP_EXCEPTION.raise_exception;
894 END IF;
895
896 -- parameter unsigned_flag
897 IF (NVL(p_unsigned_flag,'T') NOT IN ('Y','N')) THEN
898 l_field := 'p_unsigned_flag';
899 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
900 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
901 FND_MESSAGE.set_token('PARAMETER', l_field);
902 APP_EXCEPTION.raise_exception;
903 END IF;
904
905 -- parameter signed_flag
906 IF (NVL(p_signed_flag,'T') NOT IN ('Y','N')) THEN
907 l_field := 'p_signed_flag';
908 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
909 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
910 FND_MESSAGE.set_token('PARAMETER', l_field);
911 APP_EXCEPTION.raise_exception;
912 END IF;
913
914 -- parameter drawee_issued_flag
915 IF (NVL(p_drawee_issued_flag,'T') NOT IN ('Y','N')) THEN
916 l_field := 'p_drawee_issued_flag';
917 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
918 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
919 FND_MESSAGE.set_token('PARAMETER', l_field);
920 APP_EXCEPTION.raise_exception;
921 END IF;
922
923 -- parameter include_unpaid_flag
924 IF (NVL(p_include_unpaid_flag,'T') NOT IN ('Y','N')) THEN
925 l_field := 'p_include_unpaid_flag';
926 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
927 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
928 FND_MESSAGE.set_token('PARAMETER', l_field);
929 APP_EXCEPTION.raise_exception;
930 END IF;
931
932 -- parameter br_order
933 IF (NVL(p_br_order,'T') NOT IN ('ASC','DESC')) THEN
934 l_field := 'p_br_order';
935 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
936 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
937 FND_MESSAGE.set_token('PARAMETER', l_field);
938 APP_EXCEPTION.raise_exception;
939 END IF;
940
941 -- parameter drawee_order
942 IF (NVL(p_drawee_order,'T') NOT IN ('ASC','DESC')) THEN
943 l_field := 'p_drawee_order';
944 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
945 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
946 FND_MESSAGE.set_token('PARAMETER', l_field);
947 APP_EXCEPTION.raise_exception;
948 END IF;
949
950 -- parameter BR sort criteria
951 IF p_br_sort_criteria IS NULL THEN
952 IF PG_DEBUG in ('Y', 'C') THEN
953 arp_util.debug('create_remit_batch: ' || 'p_br_sort_criteria is NULL');
954 END IF;
955 ELSIF p_br_sort_criteria NOT IN ('PS.TRX_NUMBER',
956 'PS.AMOUNT_DUE_REMAINING',
957 'TRX_TYPE.NAME') THEN
958 l_field := 'p_br_sort_criteria';
959 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
960 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
961 FND_MESSAGE.set_token('PARAMETER', l_field);
962 APP_EXCEPTION.raise_exception;
963 END IF;
964
965 -- parameter drawee sort criteria
966 IF p_drawee_sort_criteria IS NULL THEN
967 IF PG_DEBUG in ('Y', 'C') THEN
968 arp_util.debug('create_remit_batch: ' || 'p_drawee_sort_criteria is NULL');
969 END IF;
970
971 /* Bug 3550612, should check for party_name and account_number instead of
972 Customer_name and Customer Number */
973 ELSIF p_drawee_sort_criteria NOT IN ('PARTY.PARTY_NAME',
974 'CUST.ACCOUNT_NUMBER',
975 'CUST.CUSTOMER_CLASS_CODE',
976 'BRANCH.BANK_NAME',
977 'BRANCH.BANK_BRANCH_NAME',
978 'BRANCH.CITY') THEN
979 l_field := 'p_drawee_sort_criteria';
980 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
981 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
982 FND_MESSAGE.set_token('PARAMETER', l_field);
983 APP_EXCEPTION.raise_exception;
984 END IF;
985
986 IF PG_DEBUG in ('Y', 'C') THEN
987 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.validate_br_search_criteria (-)');
988 END IF;
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 IF PG_DEBUG in ('Y', 'C') THEN
993 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.validate_br_search_criteria ');
994 END IF;
995 RAISE;
996
997
998 END validate_br_search_criteria;
999
1000 /*===========================================================================+
1001 | PROCEDURE |
1002 | assign_br_to_remit |
1003 | |
1004 | DESCRIPTION |
1005 | Procedure called during the process create bills receivable |
1006 | remittance to select the BR and assign them to the remittance |
1007 | |
1008 | SCOPE - PUBLIC |
1009 | |
1010 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1011 | |
1012 | ARGUMENTS : IN : |
1013 | |
1014 | RETURNS : NONE |
1015 | |
1016 | NOTES |
1017 | |
1018 | MODIFICATION HISTORY - Created by Mireille Flahaut - 30/05/2000 |
1019 | |
1020 +===========================================================================*/
1021 PROCEDURE assign_br_to_remit(
1022 p_select_detail IN varchar2,
1023 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1024 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
1025 p_include_unpaid_flag IN varchar2,
1026 p_batch_date IN AR_BATCHES.batch_date%TYPE,
1027 p_gl_date IN AR_BATCHES.gl_date%TYPE,
1028 p_currency_code IN AR_BATCHES.currency_code%TYPE,
1029 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
1030 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1031 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1032 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1033 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1034 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1035 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1036 p_unsigned_flag IN varchar2,
1037 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
1038 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
1039 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
1040 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1041 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1042 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1043 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE) IS
1044
1045 CUR_BR CUR_TYP;
1046 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
1047
1048 TOTAL_COUNT AR_BATCHES.control_count%TYPE;
1049 TOTAL_AMOUNT AR_BATCHES.control_amount%TYPE;
1050
1051 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
1052
1053 BEGIN
1054
1055 IF PG_DEBUG in ('Y', 'C') THEN
1056 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.assign_br_to_remit(+)');
1057 END IF;
1058
1059 SAVEPOINT assign_br_to_remit_PVT;
1060
1061 OPEN CUR_BR FOR p_select_detail
1062 USING p_include_unpaid_flag,
1063 p_batch_date,
1064 p_gl_date,
1065 p_currency_code,
1066 p_remittance_bank_account_id,
1067 p_maturity_date_low,
1068 p_maturity_date_high,
1069 p_br_number_low,
1070 p_br_number_high,
1071 p_br_amount_low,
1072 p_br_amount_high,
1073 p_drawee_id,
1074 p_drawee_number_low,
1075 p_drawee_number_high;
1076
1077 TOTAL_COUNT := 0;
1078 TOTAL_AMOUNT := 0;
1079
1080 IF (p_remit_total_high IS NULL) THEN
1081 IF PG_DEBUG in ('Y', 'C') THEN
1082 arp_util.debug('create_remit_batch: ' || 'no limit for the remittance');
1083 END IF;
1084 LOOP
1085 FETCH CUR_BR INTO l_ps_rec;
1086 EXIT WHEN CUR_BR%NOTFOUND;
1087 IF PG_DEBUG in ('Y', 'C') THEN
1088 arp_util.debug('create_remit_batch: ' || 'BR '||l_ps_rec.payment_schedule_id||' assign to the remittance '||p_batch_id);
1089 END IF;
1090 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(p_batch_id,l_ps_rec.payment_schedule_id,l_new_status);
1091 TOTAL_COUNT := TOTAL_COUNT + 1;
1092 TOTAL_AMOUNT := TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0);
1093 END LOOP;
1094 ELSE
1095 IF PG_DEBUG in ('Y', 'C') THEN
1096 arp_util.debug('create_remit_batch: ' || 'limit for the remittance '||p_remit_total_high);
1097 END IF;
1098 LOOP
1099 FETCH CUR_BR INTO l_ps_rec;
1100 EXIT WHEN CUR_BR%NOTFOUND;
1101 IF (TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0) <= p_remit_total_high) THEN
1102 IF PG_DEBUG in ('Y', 'C') THEN
1103 arp_util.debug('create_remit_batch: ' || 'BR '||l_ps_rec.payment_schedule_id||' assign to the remittance '||p_batch_id);
1104 END IF;
1105 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(p_batch_id,l_ps_rec.payment_schedule_id,l_new_status);
1106 TOTAL_COUNT := TOTAL_COUNT + 1;
1107 TOTAL_AMOUNT := TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0);
1108 END IF;
1109 END LOOP;
1110 END IF;
1111
1112 CLOSE CUR_BR;
1113
1114 IF PG_DEBUG in ('Y', 'C') THEN
1115 arp_util.debug('create_remit_batch: ' || 'NB remitted BR :'|| TOTAL_COUNT);
1116 arp_util.debug('create_remit_batch: ' || 'Remittance total amount :'|| TOTAL_AMOUNT);
1117 END IF;
1118
1119 p_control_count := TOTAL_COUNT;
1120 p_control_amount := TOTAL_AMOUNT;
1121
1122 IF PG_DEBUG in ('Y', 'C') THEN
1123 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.assign_br_to_remit(-)');
1124 END IF;
1125
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 IF PG_DEBUG in ('Y', 'C') THEN
1129 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.assign_br_to_remit');
1130 END IF;
1131 ROLLBACK TO assign_br_to_remit_PVT;
1132
1133 IF CUR_BR%ISOPEN THEN
1134 CLOSE CUR_BR;
1135 END IF;
1136
1137 RAISE;
1138
1139 END assign_br_to_remit;
1140
1141
1142 /*===========================================================================+
1143 | PROCEDURE |
1144 | add_or_rm_br_to_remit |
1145 | |
1146 | DESCRIPTION |
1147 | Procedure called during the process create bills receivable |
1148 | remittance to attach or detach a BR from a remittance |
1149 | |
1150 | SCOPE - PUBLIC |
1151 | |
1152 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1153 | |
1154 | ARGUMENTS : IN : |
1155 | |
1156 | RETURNS : NONE |
1157 | |
1158 | NOTES |
1159 | |
1160 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
1161 | |
1162 +===========================================================================*/
1163 PROCEDURE add_or_rm_br_to_remit (
1164 p_api_version IN NUMBER ,
1165 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1166 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1167 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1168 x_return_status OUT NOCOPY VARCHAR2 ,
1169 x_msg_count OUT NOCOPY NUMBER ,
1170 x_msg_data OUT NOCOPY VARCHAR2 ,
1171 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1172 p_ps_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
1173 p_action_flag IN varchar2,
1174 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1175 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE) IS
1176
1177 l_api_name CONSTANT varchar2(30) := 'add_or_rm_br_to_remit';
1178 l_api_version CONSTANT number := 1.0;
1179
1180 l_field varchar2(30) := NULL;
1181
1182 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
1183
1184 l_batch_rec AR_BATCHES%ROWTYPE;
1185 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
1186
1187 l_control_count AR_BATCHES.control_count%TYPE;
1188 l_control_amount AR_BATCHES.control_amount%TYPE;
1189 l_br_amount AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
1190
1191 BEGIN
1192
1193 IF PG_DEBUG in ('Y', 'C') THEN
1194 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit(+)');
1195 END IF;
1196
1197 SAVEPOINT add_or_rm_br_to_remit_PVT;
1198
1199 -- Standard call to check for call compatability
1200 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1202 END IF;
1203
1204 x_return_status := FND_API.G_RET_STS_SUCCESS;
1205
1206 -- the flag action value is S for 'Select' or D for 'Deselect' or E for 'Erase'
1207 IF (p_action_flag NOT IN ('S','D','E')) THEN
1208 l_field := 'p_action_flag';
1209 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1210 FND_MESSAGE.set_token('PROCEDURE','add_or_rm_br_to_remit');
1211 FND_MESSAGE.set_token('PARAMETER', l_field);
1212 APP_EXCEPTION.raise_exception;
1213 END IF;
1214
1215 -- the batch id isn't NULL ??
1216 IF (p_batch_id IS NULL) THEN
1217 l_field := 'p_batch_id';
1218 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1219 FND_MESSAGE.set_token('PROCEDURE','add_or_rm_br_to_remit');
1220 FND_MESSAGE.set_token('PARAMETER', l_field);
1221 APP_EXCEPTION.raise_exception;
1222 END IF;
1223
1224 -- fetch the remittance batch row
1225 ARP_CR_BATCHES_PKG.fetch_p(p_batch_id,l_batch_rec);
1226 l_control_count := l_batch_rec.control_count;
1227 l_control_amount := l_batch_rec.control_amount;
1228
1229 -- The remittance has been approved; no changes are allowed
1230 IF (l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL')) THEN
1231 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_UPDATE_REMIT');
1232 APP_EXCEPTION.raise_exception;
1233 END IF;
1234
1235 -- the payment schedule isn't NULL ??
1236 IF (p_ps_id IS NULL) THEN
1237 l_field := 'p_ps_id';
1238 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1239 FND_MESSAGE.set_token('PROCEDURE','add_or_rm_br_to_remit');
1240 FND_MESSAGE.set_token('PARAMETER', l_field);
1241 APP_EXCEPTION.raise_exception;
1242 END IF;
1243
1244 -- fetch the assigned trx payment schedule row
1245 ARP_PS_PKG.fetch_p(p_ps_id,l_ps_rec);
1246 l_br_amount := l_ps_rec.amount_due_remaining;
1247
1248 IF (p_action_flag = 'D') THEN
1249 IF PG_DEBUG in ('Y', 'C') THEN
1250 arp_util.debug('create_remit_batch: ' || 'Action Deselect - Remove the BR '||p_ps_id||' from its remittance');
1251 END IF;
1252 AR_BILLS_MAINTAIN_PUB.DeSelect_BR_Remit(p_ps_id,l_new_status);
1253 l_control_count := l_control_count - 1;
1254 l_control_amount := l_control_amount - l_br_amount;
1255 ELSIF (p_action_flag = 'E') THEN
1256 IF PG_DEBUG in ('Y', 'C') THEN
1257 arp_util.debug('create_remit_batch: ' || 'Action Erase - Remove the BR '||p_ps_id||' from its remittance');
1258 END IF;
1259 AR_BILLS_MAINTAIN_PUB.cancel_br_remit(p_ps_id);
1260 l_control_count := l_control_count - 1;
1261 l_control_amount := l_control_amount - l_br_amount;
1262 ELSE
1263 IF PG_DEBUG in ('Y', 'C') THEN
1264 arp_util.debug('create_remit_batch: ' || 'Action Select - BR '||l_ps_rec.payment_schedule_id||' assign to the remittance '||p_batch_id);
1265 END IF;
1266 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(p_batch_id,p_ps_id,l_new_status);
1267 l_control_count := l_control_count + 1;
1268 l_control_amount := l_control_amount + l_br_amount;
1269 END IF;
1270
1271 -- update the batch row with the control count and the control amount
1272 l_batch_rec.control_count := l_control_count;
1273 l_batch_rec.control_amount := l_control_amount;
1274 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1275
1276 IF FND_API.To_Boolean(p_commit) THEN
1277 IF PG_DEBUG in ('Y', 'C') THEN
1278 arp_util.debug('create_remit_batch: ' || 'commit');
1279 END IF;
1280 COMMIT;
1281 END IF;
1282
1283 p_control_count := l_control_count;
1284 p_control_amount := l_control_amount;
1285
1286 IF PG_DEBUG in ('Y', 'C') THEN
1287 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit(-)');
1288 END IF;
1289
1290 EXCEPTION
1291 WHEN FND_API.G_EXC_ERROR THEN
1292 IF PG_DEBUG in ('Y', 'C') THEN
1293 arp_util.debug('create_remit_batch: ' || 'EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit');
1294 END IF;
1295 x_return_status := FND_API.G_RET_STS_ERROR;
1296 ROLLBACK TO add_or_rm_br_to_remit_PVT;
1297
1298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1299 IF PG_DEBUG in ('Y', 'C') THEN
1300 arp_util.debug('create_remit_batch: ' || 'EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit');
1301 END IF;
1302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1303 ROLLBACK TO add_or_rm_br_to_remit_PVT;
1304
1305 WHEN OTHERS THEN
1306 IF PG_DEBUG in ('Y', 'C') THEN
1307 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit');
1308 arp_util.debug('create_remit_batch: ' || SQLERRM);
1309 END IF;
1310 ROLLBACK TO add_or_rm_br_to_remit_PVT;
1311 IF (SQLCODE = -20001) THEN
1312 x_return_status := FND_API.G_RET_STS_ERROR;
1313 RETURN;
1314 END IF;
1315
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317
1318 END add_or_rm_br_to_remit;
1319
1320
1321 /*===========================================================================+
1322 | PROCEDURE |
1323 | create_remit_batch_conc_req |
1324 | |
1325 | DESCRIPTION |
1326 | Procedure called during the process create bills receivable |
1327 | remittance to submit the BR Remittance concurrent program |
1328 | as a concurrent request |
1329 | |
1330 | SCOPE - PUBLIC |
1331 | |
1332 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1333 | |
1334 | ARGUMENTS : IN : |
1335 | |
1336 | RETURNS : NONE |
1337 | |
1338 | NOTES |
1339 | |
1340 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
1341 | |
1342 +===========================================================================*/
1343 PROCEDURE create_remit_batch_conc_req (
1344 p_api_version IN number,
1345 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1346 p_commit IN varchar2 := FND_API.G_FALSE,
1347 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1348 x_return_status OUT NOCOPY varchar2,
1349 x_msg_count OUT NOCOPY number,
1350 x_msg_data OUT NOCOPY varchar2,
1351 p_create_flag IN varchar2,
1352 p_approve_flag IN varchar2,
1353 p_format_flag IN varchar2,
1354 p_print_flag IN varchar2,
1355 p_cancel_flag IN varchar2,
1356 p_print_bills_flag IN varchar2,
1357 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1358 p_physical_bill IN varchar2,
1359 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
1360 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
1361 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1362 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1363 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1364 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1365 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1366 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1367 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
1368 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
1369 p_unsigned_flag IN varchar2,
1370 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
1371 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
1372 p_include_unpaid_flag IN varchar2,
1373 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
1374 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1375 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1376 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
1377 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
1378 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
1379 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
1380 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
1381 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
1382 p_br_sort_criteria IN varchar2,
1383 p_br_order IN varchar2,
1384 p_drawee_sort_criteria IN varchar2,
1385 p_drawee_order IN varchar2,
1386 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1387 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
1388 p_request_id OUT NOCOPY AR_BATCHES.operation_request_id%TYPE,
1389 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1390
1391 l_api_name CONSTANT varchar2(30) := 'create_remit_batch_conc_req';
1392 l_api_version CONSTANT number := 1.0;
1393
1394 l_program varchar2(30) := 'ARBRRMCP';
1395
1396 l_field varchar2(30) := NULL;
1397
1398 l_batch_rec AR_BATCHES%ROWTYPE;
1399 l_control_count AR_BATCHES.control_count%TYPE;
1400 l_control_amount AR_BATCHES.control_amount%TYPE;
1401 l_request_id AR_BATCHES.operation_request_id%TYPE;
1402 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
1403 l_org_id number;
1404 BEGIN
1405
1406 SAVEPOINT create_conc_req_PVT;
1407
1408 -- Standard call to check for call compatability
1409 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1411 END IF;
1412
1413 IF PG_DEBUG in ('Y', 'C') THEN
1414 arp_util.debug('ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req (+)');
1415 END IF;
1416
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419 --------------------------------------------------
1420 -- validations
1421 --------------------------------------------------
1422 IF p_create_flag <> 'Y' THEN
1423 l_field := 'p_create_flag';
1424 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1425 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1426 FND_MESSAGE.set_token('PARAMETER', l_field);
1427 APP_EXCEPTION.raise_exception;
1428 END IF;
1429
1430
1431 IF p_approve_flag NOT IN ('Y','N') THEN
1432 l_field := 'p_approve_flag';
1433 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1434 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1435 FND_MESSAGE.set_token('PARAMETER', l_field);
1436 APP_EXCEPTION.raise_exception;
1437 END IF;
1438
1439 IF p_format_flag NOT IN ('Y','N') THEN
1440 l_field := 'p_format_flag';
1441 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1442 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1443 FND_MESSAGE.set_token('PARAMETER', l_field);
1444 APP_EXCEPTION.raise_exception;
1445 END IF;
1446
1447 IF p_print_flag NOT IN ('Y','N') THEN
1448 l_field := 'p_print_flag';
1449 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1450 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1451 FND_MESSAGE.set_token('PARAMETER', l_field);
1452 APP_EXCEPTION.raise_exception;
1453 END IF;
1454
1455 IF p_cancel_flag NOT IN ('Y','N') THEN
1456 l_field := 'p_cancel_flag';
1457 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1458 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1459 FND_MESSAGE.set_token('PARAMETER', l_field);
1460 APP_EXCEPTION.raise_exception;
1461 END IF;
1462
1463 IF p_print_bills_flag NOT IN ('Y','N') THEN
1464 l_field := 'p_print_bills_flag';
1465 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1466 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1467 FND_MESSAGE.set_token('PARAMETER', l_field);
1468 APP_EXCEPTION.raise_exception;
1469 END IF;
1470
1471 select org_id into l_org_id
1472 from ar_system_parameters;
1473
1474 -- Fetch of the remittance batch
1475 ARP_CR_BATCHES_PKG.fetch_p(p_batch_id,l_batch_rec);
1476
1477 --MOAC changes
1478 FND_REQUEST.SET_ORG_ID(l_org_id);
1479 -- Submit the BR Remittance auto Create concurrent program as a concurrent request
1480 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1481 application => 'AR',
1482 program =>l_program,
1483 description => NULL,
1484 start_time => NULL,
1485 sub_request => NULL,
1486 argument1 =>p_create_flag,
1487 argument2 =>p_cancel_flag,
1488 argument3 =>p_approve_flag,
1489 argument4 =>p_format_flag,
1490 argument5 =>p_print_flag,
1491 argument6 =>p_print_bills_flag,
1492 argument7 =>p_batch_id,
1493 argument8 =>p_remit_total_low,
1494 argument9 =>p_remit_total_high,
1495 argument10 =>p_maturity_date_low,
1496 argument11 =>p_maturity_date_high,
1497 argument12 =>p_br_number_low,
1498 argument13 =>p_br_number_high,
1499 argument14 =>p_br_amount_low,
1500 argument15 =>p_br_amount_high,
1501 argument16 =>p_transaction_type1_id,
1502 argument17 =>p_transaction_type2_id,
1503 argument18 =>p_unsigned_flag,
1504 argument19 =>p_signed_flag,
1505 argument20 =>p_drawee_issued_flag,
1506 argument21 =>p_include_unpaid_flag,
1507 argument22 =>p_drawee_id,
1508 argument23 =>p_drawee_number_low,
1509 argument24 =>p_drawee_number_high,
1510 argument25 =>p_drawee_class1_code,
1511 argument26 =>p_drawee_class2_code,
1512 argument27 =>p_drawee_class3_code,
1513 argument28 =>p_drawee_bank_name,
1514 argument29 =>p_drawee_bank_branch_id,
1515 argument30 =>p_drawee_branch_city,
1516 argument31 =>p_br_sort_criteria,
1517 argument32 =>p_br_order,
1518 argument33 =>p_drawee_sort_criteria,
1519 argument34 =>p_drawee_order,
1520 argument35 =>p_physical_bill);
1521
1522 IF (l_request_id = 0) THEN
1523 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
1524 FND_MESSAGE.set_token('PROCEDURE','ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1525 APP_EXCEPTION.raise_exception;
1526 ELSE
1527 IF PG_DEBUG in ('Y', 'C') THEN
1528 arp_util.debug('create_remit_batch: ' || 'Submitted Request - '||l_program||'. Request ID ='||to_char(l_request_id));
1529 END IF;
1530 END IF;
1531
1532 p_control_count := l_batch_rec.control_count;
1533 p_control_amount := l_batch_rec.control_amount;
1534 p_request_id := l_request_id;
1535 p_batch_applied_status := l_batch_rec.batch_applied_status;
1536
1537 -- Update the batch row with the request id
1538 l_batch_rec.operation_request_id := l_request_id;
1539 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1540
1541 COMMIT;
1542
1543 IF PG_DEBUG in ('Y', 'C') THEN
1544 arp_util.debug('ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req (-)');
1545 END IF;
1546
1547 EXCEPTION
1548 WHEN FND_API.G_EXC_ERROR THEN
1549 IF PG_DEBUG in ('Y', 'C') THEN
1550 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1551 END IF;
1552 x_return_status := FND_API.G_RET_STS_ERROR;
1553 ROLLBACK TO create_conc_req_PVT;
1554
1555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556 IF PG_DEBUG in ('Y', 'C') THEN
1557 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1558 END IF;
1559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1560 ROLLBACK TO create_conc_req_PVT;
1561
1562 WHEN OTHERS THEN
1563 IF PG_DEBUG in ('Y', 'C') THEN
1564 arp_util.debug('EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1565 arp_util.debug('create_remit_batch: ' || SQLERRM);
1566 END IF;
1567 ROLLBACK TO create_conc_req_PVT;
1568 IF (SQLCODE = -20001) THEN
1569 x_return_status := FND_API.G_RET_STS_ERROR;
1570 RETURN;
1571 END IF;
1572
1573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1574
1575 END create_remit_batch_conc_req;
1576
1577 /*===========================================================================+
1578 | PROCEDURE |
1579 | maintain_remit_batch_conc_req |
1580 | |
1581 | DESCRIPTION
1582 | Procedure called during the process create bills receivable |
1583 | remittance to submit the BR Remittance concurrent program |
1584 | as a concurrent request |
1585 | |
1586 | SCOPE - PUBLIC |
1587 | |
1588 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1589 | |
1590 | ARGUMENTS : IN : |
1591 | |
1592 | RETURNS : NONE |
1593 | |
1594 | NOTES |
1595 | |
1596 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
1597 | |
1598 +===========================================================================*/
1599 PROCEDURE maintain_remit_batch_conc_req (
1600 p_api_version IN number,
1601 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1602 p_commit IN varchar2 := FND_API.G_FALSE,
1603 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1604 x_return_status OUT NOCOPY varchar2,
1605 x_msg_count OUT NOCOPY number,
1606 x_msg_data OUT NOCOPY varchar2,
1607 p_approve_flag IN varchar2,
1608 p_format_flag IN varchar2,
1609 p_print_flag IN varchar2,
1610 p_cancel_flag IN varchar2,
1611 p_print_bills_flag IN varchar2,
1612 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1613 p_physical_bill IN varchar2,
1614 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1615 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
1616 p_request_id OUT NOCOPY AR_BATCHES.operation_request_id%TYPE,
1617 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1618
1619 l_api_name CONSTANT varchar2(30) := 'maintain_remit_batch_conc_req';
1620 l_api_version CONSTANT number := 1.0;
1621
1622 l_program varchar2(30) := 'ARBRRMCP';
1623
1624 l_batch_rec AR_BATCHES%ROWTYPE;
1625 l_control_count AR_BATCHES.control_count%TYPE;
1626 l_control_amount AR_BATCHES.control_amount%TYPE;
1627 l_request_id AR_BATCHES.operation_request_id%TYPE;
1628 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
1629
1630 l_field varchar2(30) := NULL;
1631
1632 p_create_flag varchar2(1) := 'N';
1633 l_org_id number;
1634 BEGIN
1635
1636 IF PG_DEBUG in ('Y', 'C') THEN
1637 arp_util.debug('ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req (+)');
1638 END IF;
1639
1640 SAVEPOINT maintain_conc_req_PVT;
1641
1642 -- Standard call to check for call compatability
1643 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1645 END IF;
1646
1647 x_return_status := FND_API.G_RET_STS_SUCCESS;
1648
1649 --------------------------------------------------
1650 -- validations
1651 --------------------------------------------------
1652 IF p_approve_flag NOT IN ('Y','N') THEN
1653 l_field := 'p_approve_flag';
1654 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1655 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1656 FND_MESSAGE.set_token('PARAMETER', l_field);
1657 APP_EXCEPTION.raise_exception;
1658 END IF;
1659
1660 IF p_format_flag NOT IN ('Y','N') THEN
1661 l_field := 'p_format_flag';
1662 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1663 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1664 FND_MESSAGE.set_token('PARAMETER', l_field);
1665 APP_EXCEPTION.raise_exception;
1666 END IF;
1667
1668 IF p_print_flag NOT IN ('Y','N') THEN
1669 l_field := 'p_print_flag';
1670 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1671 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1672 FND_MESSAGE.set_token('PARAMETER', l_field);
1673 APP_EXCEPTION.raise_exception;
1674 END IF;
1675
1676 IF p_cancel_flag NOT IN ('Y','N') THEN
1677 l_field := 'p_cancel_flag';
1678 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1679 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1680 FND_MESSAGE.set_token('PARAMETER', l_field);
1681 APP_EXCEPTION.raise_exception;
1682 END IF;
1683
1684 IF p_print_bills_flag NOT IN ('Y','N') THEN
1685 l_field := 'p_print_bills_flag';
1686 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1687 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1688 FND_MESSAGE.set_token('PARAMETER', l_field);
1689 APP_EXCEPTION.raise_exception;
1690 END IF;
1691
1692 --MOAC changes
1693 select org_id into l_org_id
1694 from ar_system_parameters;
1695
1696 -- Fetch of the remittance batch
1697 ARP_CR_BATCHES_PKG.fetch_p(p_batch_id,l_batch_rec);
1698
1699 -- Validation
1700 IF (p_cancel_flag = 'Y') AND (l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL','STARTED_CANCELLATION')) THEN
1701 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CANCEL_REMIT');
1702 APP_EXCEPTION.raise_exception;
1703 END IF;
1704
1705 IF (p_approve_flag = 'Y') AND (l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL')) THEN
1706 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_APPROVE_REMIT');
1707 APP_EXCEPTION.raise_exception;
1708 END IF;
1709
1710 --MOAC changes
1711 FND_REQUEST.SET_ORG_ID(l_org_id);
1712
1713 -- Submit the BR Remittance auto Create concurrent program as a concurrent request
1714 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1715 application => 'AR',
1716 program =>'ARBRRMCP',
1717 description => NULL,
1718 start_time => NULL,
1719 sub_request => NULL,
1720 argument1 =>p_create_flag,
1721 argument2 =>p_cancel_flag,
1722 argument3 =>p_approve_flag,
1723 argument4 =>p_format_flag,
1724 argument5 =>p_print_flag,
1725 argument6 =>p_print_bills_flag,
1726 argument7 =>p_batch_id,
1727 argument8 =>'',
1728 argument9 =>'',
1729 argument10 =>'',
1730 argument11 =>'',
1731 argument12 =>'',
1732 argument13 =>'',
1733 argument14 =>'',
1734 argument15 =>'',
1735 argument16 =>'',
1736 argument17 =>'',
1737 argument18 =>'',
1738 argument19 =>'',
1739 argument20 =>'',
1740 argument21 =>'',
1741 argument22 =>'',
1742 argument23 =>'',
1743 argument24 =>'',
1744 argument25 =>'',
1745 argument26 =>'',
1746 argument27 =>'',
1747 argument28 =>'',
1748 argument29 =>'',
1749 argument30 =>'',
1750 argument31 =>'',
1751 argument32 =>'',
1752 argument33 =>'',
1753 argument34 =>'',
1754 argument35 =>p_physical_bill);
1755
1756 IF (l_request_id = 0) THEN
1757 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
1758 FND_MESSAGE.set_token('PROCEDURE','ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1759 APP_EXCEPTION.raise_exception;
1760 ELSE
1761 IF PG_DEBUG in ('Y', 'C') THEN
1762 arp_util.debug('maintain_remit_batch_conc_req: ' || 'Submitted Request - '||l_program||'. Request ID ='||to_char(l_request_id));
1763 END IF;
1764 END IF;
1765
1766 -- Update the batch row with the request id and the batch applied status
1767 l_batch_rec.operation_request_id := l_request_id;
1768
1769 IF (p_cancel_flag = 'Y') THEN
1770 l_batch_rec.batch_applied_status := 'STARTED_CANCELLATION';
1771 ELSIF (p_approve_flag = 'Y') THEN
1772 l_batch_rec.batch_applied_status := 'STARTED_APPROVAL';
1773 ELSIF (p_format_flag = 'Y') AND (l_batch_rec.auto_trans_program_id IS NOT NULL) THEN
1774 l_batch_rec.batch_applied_status := 'STARTED_FORMAT';
1775 END IF;
1776
1777 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1778
1779 p_control_count := l_batch_rec.control_count;
1780 p_control_amount := l_batch_rec.control_amount;
1781 p_request_id := l_request_id;
1782 p_batch_applied_status := l_batch_rec.batch_applied_status;
1783
1784 COMMIT;
1785
1786 IF PG_DEBUG in ('Y', 'C') THEN
1787 arp_util.debug('ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req (-)');
1788 END IF;
1789
1790 EXCEPTION
1791 WHEN FND_API.G_EXC_ERROR THEN
1792 IF PG_DEBUG in ('Y', 'C') THEN
1793 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1794 END IF;
1795 x_return_status := FND_API.G_RET_STS_ERROR;
1796 ROLLBACK TO maintain_conc_req_PVT;
1797
1798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1799 IF PG_DEBUG in ('Y', 'C') THEN
1800 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1801 END IF;
1802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1803 ROLLBACK TO maintain_conc_req_PVT;
1804
1805 WHEN OTHERS THEN
1806 IF PG_DEBUG in ('Y', 'C') THEN
1807 arp_util.debug('EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1808 arp_util.debug('maintain_remit_batch_conc_req: ' || SQLERRM);
1809 END IF;
1810 ROLLBACK TO maintain_conc_req_PVT;
1811 IF (SQLCODE = -20001) THEN
1812 x_return_status := FND_API.G_RET_STS_ERROR;
1813 RETURN;
1814 END IF;
1815
1816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1817
1818 END maintain_remit_batch_conc_req;
1819
1820 /*===========================================================================+
1821 | PROCEDURE |
1822 | update_batch_status_after_create |
1823 | |
1824 | DESCRIPTION |
1825 | Procedure called during the process create bills receivable |
1826 | remittance to update the batch status to Completed_creation |
1827 | if the batch is created manually |
1828 | |
1829 | SCOPE - PUBLIC |
1830 | |
1831 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1832 | |
1833 | ARGUMENTS : IN : |
1834 | |
1835 | RETURNS : NONE |
1836 | |
1837 | NOTES |
1838 | |
1839 | MODIFICATION HISTORY - Created by Mireille Flahaut - 21/09/2000 |
1840 | bug 1407469 : Actions Window Create doesn't update batch applied_status |
1841 | |
1842 +===========================================================================*/
1843 PROCEDURE update_batch_status (
1844 p_api_version IN number,
1845 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1846 p_commit IN varchar2 := FND_API.G_FALSE,
1847 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1848 x_return_status OUT NOCOPY varchar2,
1849 x_msg_count OUT NOCOPY number,
1850 x_msg_data OUT NOCOPY varchar2,
1851 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1852 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1853
1854 l_api_name CONSTANT varchar2(30) := 'update_batch_status';
1855 l_api_version CONSTANT number := 1.0;
1856
1857 l_batch_rec AR_BATCHES%ROWTYPE;
1858
1859 BEGIN
1860
1861 IF PG_DEBUG in ('Y', 'C') THEN
1862 arp_util.debug('ARP_PROCESS_BR_REMIT.update_batch_status (+)');
1863 END IF;
1864
1865 SAVEPOINT update_batch_status_PVT;
1866
1867 -- Standard call to check for call compatability
1868 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1870 END IF;
1871
1872 x_return_status := FND_API.G_RET_STS_SUCCESS;
1873
1874 -- lock and fetch of the batch row
1875 l_batch_rec.batch_id := p_batch_id;
1876 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
1877
1878 p_batch_applied_status := l_batch_rec.batch_applied_status;
1879
1880 -- The batch status is updated only if the batch status is STARTED_CREATION
1881 IF l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION') THEN
1882 return;
1883 END IF;
1884
1885 -- update the batch status to 'COMPLETED_CREATION'
1886 l_batch_rec.batch_applied_status := 'COMPLETED_CREATION';
1887 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1888
1889 p_batch_applied_status := l_batch_rec.batch_applied_status;
1890
1891 commit;
1892
1893 IF PG_DEBUG in ('Y', 'C') THEN
1894 arp_util.debug('ARP_PROCESS_BR_REMIT.update_batch_status (-)');
1895 END IF;
1896
1897 EXCEPTION
1898 WHEN FND_API.G_EXC_ERROR THEN
1899 IF PG_DEBUG in ('Y', 'C') THEN
1900 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_PROCESS_BR_REMIT.update_batch_status');
1901 END IF;
1902 x_return_status := FND_API.G_RET_STS_ERROR;
1903 ROLLBACK TO update_batch_status_PVT;
1904
1905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1906 IF PG_DEBUG in ('Y', 'C') THEN
1907 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_PROCESS_BR_REMIT.update_batch_status');
1908 END IF;
1909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1910 ROLLBACK TO update_batch_status_PVT;
1911
1912 WHEN OTHERS THEN
1913 IF PG_DEBUG in ('Y', 'C') THEN
1914 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_PROCESS_BR_REMIT.update_batch_status');
1915 arp_util.debug('update_batch_status: ' || SQLERRM);
1916 END IF;
1917 ROLLBACK TO update_batch_status_PVT;
1918 IF (SQLCODE = -20001) THEN
1919 x_return_status := FND_API.G_RET_STS_ERROR;
1920 RETURN;
1921 END IF;
1922
1923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1924
1925
1926 END update_batch_status;
1927
1928
1929 /*===========================================================================+
1930 | PROCEDURE |
1931 | test_rollback |
1932 | |
1933 | DESCRIPTION |
1934 | Procedure called during the process create bills receivable |
1935 | remittance to rollback the BR assignment |
1936 | |
1937 | SCOPE - PUBLIC |
1938 | |
1939 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1940 | |
1941 | ARGUMENTS : IN : |
1942 | |
1943 | RETURNS : NONE |
1944 | |
1945 | VERSION : Current version 1.0 |
1946 | Initial version 1.0 |
1947 | |
1948 | NOTES |
1949 | |
1950 | MODIFICATION HISTORY - Created by Mireille Flahaut - 12/07/2000 |
1951 | |
1952 +===========================================================================*/
1953 PROCEDURE test_rollback IS
1954 BEGIN
1955 rollback;
1956 END test_rollback;
1957
1958
1959 /*===========================================================================+
1960 | FUNCTION |
1961 | revision |
1962 | |
1963 | DESCRIPTION |
1964 | This function returns the revision number of this package. |
1965 | |
1966 | SCOPE - PUBLIC |
1967 | |
1968 | RETURNS : Revision number of this package |
1969 | |
1970 | MODIFICATION HISTORY |
1971 | 10 JAN 2001 John HALL Created |
1972 +===========================================================================*/
1973 FUNCTION revision RETURN VARCHAR2 IS
1974 BEGIN
1975 RETURN '$Revision: 120.9 $';
1976 END revision;
1977 --
1978
1979
1980
1981
1982 END ARP_PROCESS_BR_REMIT;
1983