[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_BR_REMIT
Source
1 PACKAGE BODY ARP_PROCESS_BR_REMIT AS
2 /* $Header: ARBRRMAB.pls 120.7 2005/06/24 22:09:58 apandit 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 l_where_clause := l_where_clause || 'AND :batch_date >= HS.trx_date ';
619 l_where_clause := l_where_clause || 'AND :batch_gl_date >= HS.gl_date ';
620
621 -- Batch parameters
622 l_where_clause := l_where_clause || 'AND PS.invoice_currency_code = :currency_code ';
623 l_where_clause := l_where_clause || 'AND (( TRX.remit_bank_acct_use_id IS NULL) OR '
624 ||'(TRX.remit_bank_acct_use_id = :remittance_bank_account_id AND TRX.override_remit_account_flag = '||''''|| l_flag_no ||''''||') OR '
625 ||'(TRX.override_remit_account_flag = '||''''|| l_flag_yes ||''''||')) ';
626
627 -- BR parameters
628 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) ';
629 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) ';
630 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) ';
631
632 -- Criteria Signed_flag and Drawee_issued_flag
633 -- If both are N, All types (Signed, drawee_issued and unsigned) are selected
634 IF (p_unsigned_flag = 'Y' AND p_signed_flag = 'Y' AND p_drawee_issued_flag = 'Y') THEN
635 NULL;
636 ELSIF (p_unsigned_flag = 'Y' AND p_signed_flag = 'Y') THEN
637 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'')) ';
638 ELSIF (p_unsigned_flag = 'Y' AND p_drawee_issued_flag = 'Y') THEN
639 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'')) ';
640 ELSIF (p_signed_flag = 'Y' AND p_drawee_issued_flag = 'Y') THEN
641 l_where_clause := l_where_clause || 'AND (TRX_TYPE.signed_flag = ''Y'' OR TRX_TYPE.drawee_issued_flag = ''Y'') ';
642 ELSIF (p_unsigned_flag = 'Y') THEN
643 l_where_clause := l_where_clause || 'AND (TRX_TYPE.signed_flag = ''N'' AND TRX_TYPE.drawee_issued_flag = ''N'') ';
644 ELSIF (p_signed_flag = 'Y') THEN
645 l_where_clause := l_where_clause || 'AND TRX_TYPE.signed_flag = ''Y'' ';
646 ELSIF (p_drawee_issued_flag = 'Y') THEN
647 l_where_clause := l_where_clause || 'AND TRX_TYPE.drawee_issued_flag = ''Y'' ';
648 END IF;
649
650 -- Transaction types
651 IF (p_transaction_type1_id IS NOT NULL) AND (p_transaction_type2_id IS NOT NULL) THEN
652 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id IN ('||p_transaction_type1_id||','||p_transaction_type2_id||') ';
653 ELSIF (p_transaction_type1_id IS NOT NULL) THEN
654 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id IN ('||p_transaction_type1_id||') ';
655 ELSIF (p_transaction_type2_id IS NOT NULL) THEN
656 l_where_clause := l_where_clause || 'AND PS.cust_trx_type_id IN ('||p_transaction_type2_id||') ';
657 END IF;
658
659 ----------------------------------------------------
660 -- Drawee parameters
661 ----------------------------------------------------
662
663 l_where_clause := l_where_clause || 'AND TRX.drawee_id LIKE NVL(:drawee_id,TRX.drawee_id) ';
664 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) ';
665
666 -- the drawee bank account information is optional on a BR
667 IF (p_drawee_bank_name IS NOT NULL) THEN
668 l_where_clause := l_where_clause || 'AND BRANCH.bank_name LIKE '||''''||p_drawee_bank_name||''' ';
669 END IF;
670
671 IF (p_drawee_bank_branch_id IS NOT NULL) THEN
672 l_where_clause := l_where_clause || 'AND BRANCH.branch_party_id = '||p_drawee_bank_branch_id||' ';
673 END IF;
674
675 IF (p_drawee_branch_city IS NOT NULL) THEN
676 l_where_clause := l_where_clause || 'AND BRANCH.city LIKE '||''''||p_drawee_branch_city||''' ';
677 END IF;
678
679 -- Drawee classes
680 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
681 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
682 ''''||p_drawee_class1_code||''','''||p_drawee_class2_code||''','''|| p_drawee_class3_code||''') ';
683 ELSIF (p_drawee_class1_code IS NOT NULL) AND (p_drawee_class2_code IS NOT NULL) THEN
684 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
685 ''''||p_drawee_class1_code||''','''||p_drawee_class2_code||''') ';
686 ELSIF (p_drawee_class1_code IS NOT NULL) AND (p_drawee_class3_code IS NOT NULL) THEN
687 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
688 ''''||p_drawee_class1_code||''','''||p_drawee_class3_code||''') ';
689 ELSIF (p_drawee_class2_code IS NOT NULL) AND (p_drawee_class3_code IS NOT NULL) THEN
690 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
691 ''''||p_drawee_class2_code||''','''||p_drawee_class3_code||''') ';
692 ELSIF (p_drawee_class1_code IS NOT NULL) THEN
693 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
694 ''''||p_drawee_class1_code||''') ';
695 ELSIF (p_drawee_class2_code IS NOT NULL) THEN
696 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
697 ''''||p_drawee_class2_code||''') ';
698 ELSIF (p_drawee_class3_code IS NOT NULL) THEN
699 l_where_clause := l_where_clause || 'AND CUST.customer_class_code IN ('||
700 ''''||p_drawee_class3_code||''') ';
701 END IF;
702
703 IF PG_DEBUG in ('Y', 'C') THEN
704 arp_util.debug('create_remit_batch: ' || 'Where clause completed.');
705 END IF;
706
707
708 /*-------------------------------------------------------------------------------------*/
709 /* clause Order set up */
710 /*-------------------------------------------------------------------------------------*/
711 l_order_clause := 'ORDER BY PS.due_date ASC';
712
713 IF (NOT p_br_sort_criteria IS NULL) THEN
714 l_order_clause := l_order_clause || ',' || p_br_sort_criteria || ' ' || p_br_order;
715 END IF;
716
717 IF (NOT p_drawee_sort_criteria IS NULL) THEN
718 l_order_clause := l_order_clause || ',' || p_drawee_sort_criteria || ' ' || p_drawee_order;
719 END IF;
720
721 IF PG_DEBUG in ('Y', 'C') THEN
722 arp_util.debug('create_remit_batch: ' || 'Order by clause completed.');
723 END IF;
724
725
726 /*-------------------------------------------------------------------------------------*/
727 /* Select */
728 /*-------------------------------------------------------------------------------------*/
729 /* Bug 3424656 Modified the following SELECT to pick all
730 the columns from PS rather than all the columns using
731 the wildcard character. This was giving ORA-00932
732 Inconsitent Datatypes errors in 10g. */
733
734 p_select_detail := 'SELECT PS.* ' ||
735 'FROM AR_PAYMENT_SCHEDULES PS, ' ||
736 'AR_TRANSACTION_HISTORY HS, ' ||
737 'HZ_CUST_ACCOUNTS CUST, ' ||
738 'HZ_PARTIES PARTY, ' ||
739 'RA_CUST_TRX_TYPES TRX_TYPE, ' ||
740 'RA_CUSTOMER_TRX TRX, ' ||
741 'AP_BANK_ACCOUNTS ACC, ' ||
742 'CE_BANK_BRANCHES_V BRANCH ' ||
743 l_where_clause ||
744 l_order_clause;
745
746 IF PG_DEBUG in ('Y', 'C') THEN
747 arp_util.debug('create_remit_batch: ' || '-----------ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (-)-------------');
748 END IF;
749
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 IF PG_DEBUG in ('Y', 'C') THEN
754 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.construct_select_br_for_remit ');
755 END IF;
756 RAISE;
757
758 END construct_select_br_for_remit;
759
760
761
762 /*===========================================================================+
763 | PROCEDURE |
764 | validate_br_search_criteria |
765 | |
766 | DESCRIPTION |
767 | Procedure called during the process create bills receivable |
768 | remittance to validate the br search criteria |
769 | |
770 | SCOPE - PUBLIC |
771 | |
772 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
773 | |
774 | ARGUMENTS : IN : |
775 | |
776 | RETURNS : NONE |
777 | |
778 | NOTES |
779 | |
780 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
781 | |
782 | 20-Apr-2004 Debbie Sue Jancis Fixed Bug 3550612, changed the check |
783 | on p_drawee_sort_criteria to check |
784 | for party_name and account_number |
785 | instead of customer name and |
786 | customer number |
787 +===========================================================================*/
788 PROCEDURE validate_br_search_criteria(
789 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
790 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
791 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
792 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
793 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
794 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
795 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
796 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
797 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
798 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
799 p_unsigned_flag IN varchar2,
800 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
801 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
802 p_include_unpaid_flag IN varchar2,
803 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
804 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
805 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
806 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
807 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
808 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
809 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
810 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
811 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
812 p_br_sort_criteria IN varchar2,
813 p_br_order IN varchar2,
814 p_drawee_sort_criteria IN varchar2,
815 p_drawee_order IN varchar2) IS
816
817 l_field varchar2(30) := NULL;
818
819 BEGIN
820
821 IF PG_DEBUG in ('Y', 'C') THEN
822 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.validate_br_search_criteria (+)');
823 END IF;
824
825 -- remittance total amount
826 IF (p_remit_total_low IS NULL) AND (p_remit_total_high IS NULL) THEN
827 IF PG_DEBUG in ('Y', 'C') THEN
828 arp_util.debug('create_remit_batch: ' || 'p_remit_total_low and p_remit_total_high are NULL');
829 END IF;
830 ELSIF (p_remit_total_low IS NULL) OR
831 (p_remit_total_high IS NULL) OR
832 (p_remit_total_high < p_remit_total_low) THEN
833 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_REMIT_AMOUNT');
834 APP_EXCEPTION.raise_exception;
835 END IF;
836
837 -- maturity date
838 IF (p_maturity_date_low IS NULL) AND (p_maturity_date_high IS NULL) THEN
839 IF PG_DEBUG in ('Y', 'C') THEN
840 arp_util.debug('create_remit_batch: ' || 'p_maturity_date_low and p_maturity_date_high are NULL');
841 END IF;
842 ELSIF (p_maturity_date_low IS NULL) OR
843 (p_maturity_date_high IS NULL) OR
844 (p_maturity_date_high < p_maturity_date_low) THEN
845 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_BR_DUE_DATE');
846 APP_EXCEPTION.raise_exception;
847 END IF;
848
849 -- BR number
850 IF (p_br_number_low IS NULL) AND (p_br_number_high IS NULL) THEN
851 IF PG_DEBUG in ('Y', 'C') THEN
852 arp_util.debug('create_remit_batch: ' || 'p_br_number_low and p_br_number_high are NULL');
853 END IF;
854 ELSIF (p_br_number_low IS NULL) OR
855 (p_br_number_high IS NULL) OR
856 (p_br_number_high < p_br_number_low) THEN
857 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_BR_NUMBER');
858 APP_EXCEPTION.raise_exception;
859 END IF;
860
861 -- BR amount
862 IF (p_br_amount_low IS NULL) AND (p_br_amount_high IS NULL) THEN
863 IF PG_DEBUG in ('Y', 'C') THEN
864 arp_util.debug('create_remit_batch: ' || 'p_br_amount_low and p_br_amount_high are NULL');
865 END IF;
866 ELSIF (p_br_amount_low IS NULL) OR
867 (p_br_amount_high IS NULL) OR
868 (p_br_amount_high < p_br_amount_low) THEN
869 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_BR_AMOUNT');
870 APP_EXCEPTION.raise_exception;
871 END IF;
872
873 -- Drawee number
874 IF (p_drawee_number_low IS NULL) AND (p_drawee_number_high IS NULL) THEN
875 IF PG_DEBUG in ('Y', 'C') THEN
876 arp_util.debug('create_remit_batch: ' || 'p_drawee_number_low and p_drawee_number_high are NULL');
877 END IF;
878 ELSIF (p_drawee_number_low IS NULL) OR
879 (p_drawee_number_high IS NULL) OR
880 (p_drawee_number_high < p_drawee_number_low) THEN
881 FND_MESSAGE.set_name('AR','AR_BR_BAD_PARAM_DRAWEE_NUMBER');
882 APP_EXCEPTION.raise_exception;
883 END IF;
884
885 -- parameter unsigned_flag
886 IF (NVL(p_unsigned_flag,'T') NOT IN ('Y','N')) THEN
887 l_field := 'p_unsigned_flag';
888 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
889 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
890 FND_MESSAGE.set_token('PARAMETER', l_field);
891 APP_EXCEPTION.raise_exception;
892 END IF;
893
894 -- parameter signed_flag
895 IF (NVL(p_signed_flag,'T') NOT IN ('Y','N')) THEN
896 l_field := 'p_signed_flag';
897 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
898 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
899 FND_MESSAGE.set_token('PARAMETER', l_field);
900 APP_EXCEPTION.raise_exception;
901 END IF;
902
903 -- parameter drawee_issued_flag
904 IF (NVL(p_drawee_issued_flag,'T') NOT IN ('Y','N')) THEN
905 l_field := 'p_drawee_issued_flag';
906 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
907 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
908 FND_MESSAGE.set_token('PARAMETER', l_field);
909 APP_EXCEPTION.raise_exception;
910 END IF;
911
912 -- parameter include_unpaid_flag
913 IF (NVL(p_include_unpaid_flag,'T') NOT IN ('Y','N')) THEN
914 l_field := 'p_include_unpaid_flag';
915 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
916 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
917 FND_MESSAGE.set_token('PARAMETER', l_field);
918 APP_EXCEPTION.raise_exception;
919 END IF;
920
921 -- parameter br_order
922 IF (NVL(p_br_order,'T') NOT IN ('ASC','DESC')) THEN
923 l_field := 'p_br_order';
924 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
925 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
926 FND_MESSAGE.set_token('PARAMETER', l_field);
927 APP_EXCEPTION.raise_exception;
928 END IF;
929
930 -- parameter drawee_order
931 IF (NVL(p_drawee_order,'T') NOT IN ('ASC','DESC')) THEN
932 l_field := 'p_drawee_order';
933 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
934 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
935 FND_MESSAGE.set_token('PARAMETER', l_field);
936 APP_EXCEPTION.raise_exception;
937 END IF;
938
939 -- parameter BR sort criteria
940 IF p_br_sort_criteria IS NULL THEN
941 IF PG_DEBUG in ('Y', 'C') THEN
942 arp_util.debug('create_remit_batch: ' || 'p_br_sort_criteria is NULL');
943 END IF;
944 ELSIF p_br_sort_criteria NOT IN ('PS.TRX_NUMBER',
945 'PS.AMOUNT_DUE_REMAINING',
946 'TRX_TYPE.NAME') THEN
947 l_field := 'p_br_sort_criteria';
948 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
949 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
950 FND_MESSAGE.set_token('PARAMETER', l_field);
951 APP_EXCEPTION.raise_exception;
952 END IF;
953
954 -- parameter drawee sort criteria
955 IF p_drawee_sort_criteria IS NULL THEN
956 IF PG_DEBUG in ('Y', 'C') THEN
957 arp_util.debug('create_remit_batch: ' || 'p_drawee_sort_criteria is NULL');
958 END IF;
959
960 /* Bug 3550612, should check for party_name and account_number instead of
961 Customer_name and Customer Number */
962 ELSIF p_drawee_sort_criteria NOT IN ('PARTY.PARTY_NAME',
963 'CUST.ACCOUNT_NUMBER',
964 'CUST.CUSTOMER_CLASS_CODE',
965 'BRANCH.BANK_NAME',
966 'BRANCH.BANK_BRANCH_NAME',
967 'BRANCH.CITY') THEN
968 l_field := 'p_drawee_sort_criteria';
969 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
970 FND_MESSAGE.set_token('PROCEDURE','validate_br_search_criteria');
971 FND_MESSAGE.set_token('PARAMETER', l_field);
972 APP_EXCEPTION.raise_exception;
973 END IF;
974
975 IF PG_DEBUG in ('Y', 'C') THEN
976 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.validate_br_search_criteria (-)');
977 END IF;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 IF PG_DEBUG in ('Y', 'C') THEN
982 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.validate_br_search_criteria ');
983 END IF;
984 RAISE;
985
986
987 END validate_br_search_criteria;
988
989 /*===========================================================================+
990 | PROCEDURE |
991 | assign_br_to_remit |
992 | |
993 | DESCRIPTION |
994 | Procedure called during the process create bills receivable |
995 | remittance to select the BR and assign them to the remittance |
996 | |
997 | SCOPE - PUBLIC |
998 | |
999 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1000 | |
1001 | ARGUMENTS : IN : |
1002 | |
1003 | RETURNS : NONE |
1004 | |
1005 | NOTES |
1006 | |
1007 | MODIFICATION HISTORY - Created by Mireille Flahaut - 30/05/2000 |
1008 | |
1009 +===========================================================================*/
1010 PROCEDURE assign_br_to_remit(
1011 p_select_detail IN varchar2,
1012 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1013 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
1014 p_include_unpaid_flag IN varchar2,
1015 p_batch_date IN AR_BATCHES.batch_date%TYPE,
1016 p_gl_date IN AR_BATCHES.gl_date%TYPE,
1017 p_currency_code IN AR_BATCHES.currency_code%TYPE,
1018 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
1019 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1020 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1021 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1022 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1023 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1024 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1025 p_unsigned_flag IN varchar2,
1026 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
1027 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
1028 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
1029 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1030 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1031 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1032 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE) IS
1033
1034 CUR_BR CUR_TYP;
1035 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
1036
1037 TOTAL_COUNT AR_BATCHES.control_count%TYPE;
1038 TOTAL_AMOUNT AR_BATCHES.control_amount%TYPE;
1039
1040 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
1041
1042 BEGIN
1043
1044 IF PG_DEBUG in ('Y', 'C') THEN
1045 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.assign_br_to_remit(+)');
1046 END IF;
1047
1048 SAVEPOINT assign_br_to_remit_PVT;
1049
1050 OPEN CUR_BR FOR p_select_detail
1051 USING p_include_unpaid_flag,
1052 p_batch_date,
1053 p_gl_date,
1054 p_currency_code,
1055 p_remittance_bank_account_id,
1056 p_maturity_date_low,
1057 p_maturity_date_high,
1058 p_br_number_low,
1059 p_br_number_high,
1060 p_br_amount_low,
1061 p_br_amount_high,
1062 p_drawee_id,
1063 p_drawee_number_low,
1064 p_drawee_number_high;
1065
1066 TOTAL_COUNT := 0;
1067 TOTAL_AMOUNT := 0;
1068
1069 IF (p_remit_total_high IS NULL) THEN
1070 IF PG_DEBUG in ('Y', 'C') THEN
1071 arp_util.debug('create_remit_batch: ' || 'no limit for the remittance');
1072 END IF;
1073 LOOP
1074 FETCH CUR_BR INTO l_ps_rec;
1075 EXIT WHEN CUR_BR%NOTFOUND;
1076 IF PG_DEBUG in ('Y', 'C') THEN
1077 arp_util.debug('create_remit_batch: ' || 'BR '||l_ps_rec.payment_schedule_id||' assign to the remittance '||p_batch_id);
1078 END IF;
1079 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(p_batch_id,l_ps_rec.payment_schedule_id,l_new_status);
1080 TOTAL_COUNT := TOTAL_COUNT + 1;
1081 TOTAL_AMOUNT := TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0);
1082 END LOOP;
1083 ELSE
1084 IF PG_DEBUG in ('Y', 'C') THEN
1085 arp_util.debug('create_remit_batch: ' || 'limit for the remittance '||p_remit_total_high);
1086 END IF;
1087 LOOP
1088 FETCH CUR_BR INTO l_ps_rec;
1089 EXIT WHEN CUR_BR%NOTFOUND;
1090 IF (TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0) <= p_remit_total_high) THEN
1091 IF PG_DEBUG in ('Y', 'C') THEN
1092 arp_util.debug('create_remit_batch: ' || 'BR '||l_ps_rec.payment_schedule_id||' assign to the remittance '||p_batch_id);
1093 END IF;
1094 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(p_batch_id,l_ps_rec.payment_schedule_id,l_new_status);
1095 TOTAL_COUNT := TOTAL_COUNT + 1;
1096 TOTAL_AMOUNT := TOTAL_AMOUNT + NVL(l_ps_rec.amount_due_remaining,0);
1097 END IF;
1098 END LOOP;
1099 END IF;
1100
1101 CLOSE CUR_BR;
1102
1103 IF PG_DEBUG in ('Y', 'C') THEN
1104 arp_util.debug('create_remit_batch: ' || 'NB remitted BR :'|| TOTAL_COUNT);
1105 arp_util.debug('create_remit_batch: ' || 'Remittance total amount :'|| TOTAL_AMOUNT);
1106 END IF;
1107
1108 p_control_count := TOTAL_COUNT;
1109 p_control_amount := TOTAL_AMOUNT;
1110
1111 IF PG_DEBUG in ('Y', 'C') THEN
1112 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.assign_br_to_remit(-)');
1113 END IF;
1114
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 IF PG_DEBUG in ('Y', 'C') THEN
1118 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.assign_br_to_remit');
1119 END IF;
1120 ROLLBACK TO assign_br_to_remit_PVT;
1121
1122 IF CUR_BR%ISOPEN THEN
1123 CLOSE CUR_BR;
1124 END IF;
1125
1126 RAISE;
1127
1128 END assign_br_to_remit;
1129
1130
1131 /*===========================================================================+
1132 | PROCEDURE |
1133 | add_or_rm_br_to_remit |
1134 | |
1135 | DESCRIPTION |
1136 | Procedure called during the process create bills receivable |
1137 | remittance to attach or detach a BR from a remittance |
1138 | |
1139 | SCOPE - PUBLIC |
1140 | |
1141 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1142 | |
1143 | ARGUMENTS : IN : |
1144 | |
1145 | RETURNS : NONE |
1146 | |
1147 | NOTES |
1148 | |
1149 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
1150 | |
1151 +===========================================================================*/
1152 PROCEDURE add_or_rm_br_to_remit (
1153 p_api_version IN NUMBER ,
1154 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1155 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1156 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1157 x_return_status OUT NOCOPY VARCHAR2 ,
1158 x_msg_count OUT NOCOPY NUMBER ,
1159 x_msg_data OUT NOCOPY VARCHAR2 ,
1160 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1161 p_ps_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
1162 p_action_flag IN varchar2,
1163 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1164 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE) IS
1165
1166 l_api_name CONSTANT varchar2(30) := 'add_or_rm_br_to_remit';
1167 l_api_version CONSTANT number := 1.0;
1168
1169 l_field varchar2(30) := NULL;
1170
1171 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
1172
1173 l_batch_rec AR_BATCHES%ROWTYPE;
1174 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
1175
1176 l_control_count AR_BATCHES.control_count%TYPE;
1177 l_control_amount AR_BATCHES.control_amount%TYPE;
1178 l_br_amount AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
1179
1180 BEGIN
1181
1182 IF PG_DEBUG in ('Y', 'C') THEN
1183 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit(+)');
1184 END IF;
1185
1186 SAVEPOINT add_or_rm_br_to_remit_PVT;
1187
1188 -- Standard call to check for call compatability
1189 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191 END IF;
1192
1193 x_return_status := FND_API.G_RET_STS_SUCCESS;
1194
1195 -- the flag action value is S for 'Select' or D for 'Deselect' or E for 'Erase'
1196 IF (p_action_flag NOT IN ('S','D','E')) THEN
1197 l_field := 'p_action_flag';
1198 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1199 FND_MESSAGE.set_token('PROCEDURE','add_or_rm_br_to_remit');
1200 FND_MESSAGE.set_token('PARAMETER', l_field);
1201 APP_EXCEPTION.raise_exception;
1202 END IF;
1203
1204 -- the batch id isn't NULL ??
1205 IF (p_batch_id IS NULL) THEN
1206 l_field := 'p_batch_id';
1207 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1208 FND_MESSAGE.set_token('PROCEDURE','add_or_rm_br_to_remit');
1209 FND_MESSAGE.set_token('PARAMETER', l_field);
1210 APP_EXCEPTION.raise_exception;
1211 END IF;
1212
1213 -- fetch the remittance batch row
1214 ARP_CR_BATCHES_PKG.fetch_p(p_batch_id,l_batch_rec);
1215 l_control_count := l_batch_rec.control_count;
1216 l_control_amount := l_batch_rec.control_amount;
1217
1218 -- The remittance has been approved; no changes are allowed
1219 IF (l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL')) THEN
1220 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_UPDATE_REMIT');
1221 APP_EXCEPTION.raise_exception;
1222 END IF;
1223
1224 -- the payment schedule isn't NULL ??
1225 IF (p_ps_id IS NULL) THEN
1226 l_field := 'p_ps_id';
1227 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1228 FND_MESSAGE.set_token('PROCEDURE','add_or_rm_br_to_remit');
1229 FND_MESSAGE.set_token('PARAMETER', l_field);
1230 APP_EXCEPTION.raise_exception;
1231 END IF;
1232
1233 -- fetch the assigned trx payment schedule row
1234 ARP_PS_PKG.fetch_p(p_ps_id,l_ps_rec);
1235 l_br_amount := l_ps_rec.amount_due_remaining;
1236
1237 IF (p_action_flag = 'D') THEN
1238 IF PG_DEBUG in ('Y', 'C') THEN
1239 arp_util.debug('create_remit_batch: ' || 'Action Deselect - Remove the BR '||p_ps_id||' from its remittance');
1240 END IF;
1241 AR_BILLS_MAINTAIN_PUB.DeSelect_BR_Remit(p_ps_id,l_new_status);
1242 l_control_count := l_control_count - 1;
1243 l_control_amount := l_control_amount - l_br_amount;
1244 ELSIF (p_action_flag = 'E') THEN
1245 IF PG_DEBUG in ('Y', 'C') THEN
1246 arp_util.debug('create_remit_batch: ' || 'Action Erase - Remove the BR '||p_ps_id||' from its remittance');
1247 END IF;
1248 AR_BILLS_MAINTAIN_PUB.cancel_br_remit(p_ps_id);
1249 l_control_count := l_control_count - 1;
1250 l_control_amount := l_control_amount - l_br_amount;
1251 ELSE
1252 IF PG_DEBUG in ('Y', 'C') THEN
1253 arp_util.debug('create_remit_batch: ' || 'Action Select - BR '||l_ps_rec.payment_schedule_id||' assign to the remittance '||p_batch_id);
1254 END IF;
1255 AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(p_batch_id,p_ps_id,l_new_status);
1256 l_control_count := l_control_count + 1;
1257 l_control_amount := l_control_amount + l_br_amount;
1258 END IF;
1259
1260 -- update the batch row with the control count and the control amount
1261 l_batch_rec.control_count := l_control_count;
1262 l_batch_rec.control_amount := l_control_amount;
1263 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1264
1265 IF FND_API.To_Boolean(p_commit) THEN
1266 IF PG_DEBUG in ('Y', 'C') THEN
1267 arp_util.debug('create_remit_batch: ' || 'commit');
1268 END IF;
1269 COMMIT;
1270 END IF;
1271
1272 p_control_count := l_control_count;
1273 p_control_amount := l_control_amount;
1274
1275 IF PG_DEBUG in ('Y', 'C') THEN
1276 arp_util.debug('create_remit_batch: ' || 'ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit(-)');
1277 END IF;
1278
1279 EXCEPTION
1280 WHEN FND_API.G_EXC_ERROR THEN
1281 IF PG_DEBUG in ('Y', 'C') THEN
1282 arp_util.debug('create_remit_batch: ' || 'EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit');
1283 END IF;
1284 x_return_status := FND_API.G_RET_STS_ERROR;
1285 ROLLBACK TO add_or_rm_br_to_remit_PVT;
1286
1287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288 IF PG_DEBUG in ('Y', 'C') THEN
1289 arp_util.debug('create_remit_batch: ' || 'EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit');
1290 END IF;
1291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292 ROLLBACK TO add_or_rm_br_to_remit_PVT;
1293
1294 WHEN OTHERS THEN
1295 IF PG_DEBUG in ('Y', 'C') THEN
1296 arp_util.debug('create_remit_batch: ' || 'EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.add_or_rm_br_to_remit');
1297 arp_util.debug('create_remit_batch: ' || SQLERRM);
1298 END IF;
1299 ROLLBACK TO add_or_rm_br_to_remit_PVT;
1300 IF (SQLCODE = -20001) THEN
1301 x_return_status := FND_API.G_RET_STS_ERROR;
1302 RETURN;
1303 END IF;
1304
1305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1306
1307 END add_or_rm_br_to_remit;
1308
1309
1310 /*===========================================================================+
1311 | PROCEDURE |
1312 | create_remit_batch_conc_req |
1313 | |
1314 | DESCRIPTION |
1315 | Procedure called during the process create bills receivable |
1316 | remittance to submit the BR Remittance concurrent program |
1317 | as a concurrent request |
1318 | |
1319 | SCOPE - PUBLIC |
1320 | |
1321 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1322 | |
1323 | ARGUMENTS : IN : |
1324 | |
1325 | RETURNS : NONE |
1326 | |
1327 | NOTES |
1328 | |
1329 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
1330 | |
1331 +===========================================================================*/
1332 PROCEDURE create_remit_batch_conc_req (
1333 p_api_version IN number,
1334 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1335 p_commit IN varchar2 := FND_API.G_FALSE,
1336 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1337 x_return_status OUT NOCOPY varchar2,
1338 x_msg_count OUT NOCOPY number,
1339 x_msg_data OUT NOCOPY varchar2,
1340 p_create_flag IN varchar2,
1341 p_approve_flag IN varchar2,
1342 p_format_flag IN varchar2,
1343 p_print_flag IN varchar2,
1344 p_cancel_flag IN varchar2,
1345 p_print_bills_flag IN varchar2,
1346 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1347 p_physical_bill IN varchar2,
1348 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
1349 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
1350 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1351 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1352 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1353 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
1354 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1355 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
1356 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
1357 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
1358 p_unsigned_flag IN varchar2,
1359 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
1360 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
1361 p_include_unpaid_flag IN varchar2,
1362 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
1363 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1364 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
1365 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
1366 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
1367 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
1368 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
1369 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
1370 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
1371 p_br_sort_criteria IN varchar2,
1372 p_br_order IN varchar2,
1373 p_drawee_sort_criteria IN varchar2,
1374 p_drawee_order IN varchar2,
1375 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1376 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
1377 p_request_id OUT NOCOPY AR_BATCHES.operation_request_id%TYPE,
1378 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1379
1380 l_api_name CONSTANT varchar2(30) := 'create_remit_batch_conc_req';
1381 l_api_version CONSTANT number := 1.0;
1382
1383 l_program varchar2(30) := 'ARBRRMCP';
1384
1385 l_field varchar2(30) := NULL;
1386
1387 l_batch_rec AR_BATCHES%ROWTYPE;
1388 l_control_count AR_BATCHES.control_count%TYPE;
1389 l_control_amount AR_BATCHES.control_amount%TYPE;
1390 l_request_id AR_BATCHES.operation_request_id%TYPE;
1391 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
1392 l_org_id number;
1393 BEGIN
1394
1395 SAVEPOINT create_conc_req_PVT;
1396
1397 -- Standard call to check for call compatability
1398 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401
1402 IF PG_DEBUG in ('Y', 'C') THEN
1403 arp_util.debug('ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req (+)');
1404 END IF;
1405
1406 x_return_status := FND_API.G_RET_STS_SUCCESS;
1407
1408 --------------------------------------------------
1409 -- validations
1410 --------------------------------------------------
1411 IF p_create_flag <> 'Y' THEN
1412 l_field := 'p_create_flag';
1413 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1414 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1415 FND_MESSAGE.set_token('PARAMETER', l_field);
1416 APP_EXCEPTION.raise_exception;
1417 END IF;
1418
1419
1420 IF p_approve_flag NOT IN ('Y','N') THEN
1421 l_field := 'p_approve_flag';
1422 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1423 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1424 FND_MESSAGE.set_token('PARAMETER', l_field);
1425 APP_EXCEPTION.raise_exception;
1426 END IF;
1427
1428 IF p_format_flag NOT IN ('Y','N') THEN
1429 l_field := 'p_format_flag';
1430 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1431 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1432 FND_MESSAGE.set_token('PARAMETER', l_field);
1433 APP_EXCEPTION.raise_exception;
1434 END IF;
1435
1436 IF p_print_flag NOT IN ('Y','N') THEN
1437 l_field := 'p_print_flag';
1438 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1439 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1440 FND_MESSAGE.set_token('PARAMETER', l_field);
1441 APP_EXCEPTION.raise_exception;
1442 END IF;
1443
1444 IF p_cancel_flag NOT IN ('Y','N') THEN
1445 l_field := 'p_cancel_flag';
1446 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1447 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1448 FND_MESSAGE.set_token('PARAMETER', l_field);
1449 APP_EXCEPTION.raise_exception;
1450 END IF;
1451
1452 IF p_print_bills_flag NOT IN ('Y','N') THEN
1453 l_field := 'p_print_bills_flag';
1454 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1455 FND_MESSAGE.set_token('PROCEDURE','create_remit_batch_conc_req');
1456 FND_MESSAGE.set_token('PARAMETER', l_field);
1457 APP_EXCEPTION.raise_exception;
1458 END IF;
1459
1460 select org_id into l_org_id
1461 from ar_system_parameters;
1462
1463 -- Fetch of the remittance batch
1464 ARP_CR_BATCHES_PKG.fetch_p(p_batch_id,l_batch_rec);
1465
1466 --MOAC changes
1467 FND_REQUEST.SET_ORG_ID(l_org_id);
1468 -- Submit the BR Remittance auto Create concurrent program as a concurrent request
1469 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1470 application => 'AR',
1471 program =>l_program,
1472 description => NULL,
1473 start_time => NULL,
1474 sub_request => NULL,
1475 argument1 =>p_create_flag,
1476 argument2 =>p_cancel_flag,
1477 argument3 =>p_approve_flag,
1478 argument4 =>p_format_flag,
1479 argument5 =>p_print_flag,
1480 argument6 =>p_print_bills_flag,
1481 argument7 =>p_batch_id,
1482 argument8 =>p_remit_total_low,
1483 argument9 =>p_remit_total_high,
1484 argument10 =>p_maturity_date_low,
1485 argument11 =>p_maturity_date_high,
1486 argument12 =>p_br_number_low,
1487 argument13 =>p_br_number_high,
1488 argument14 =>p_br_amount_low,
1489 argument15 =>p_br_amount_high,
1490 argument16 =>p_transaction_type1_id,
1491 argument17 =>p_transaction_type2_id,
1492 argument18 =>p_unsigned_flag,
1493 argument19 =>p_signed_flag,
1494 argument20 =>p_drawee_issued_flag,
1495 argument21 =>p_include_unpaid_flag,
1496 argument22 =>p_drawee_id,
1497 argument23 =>p_drawee_number_low,
1498 argument24 =>p_drawee_number_high,
1499 argument25 =>p_drawee_class1_code,
1500 argument26 =>p_drawee_class2_code,
1501 argument27 =>p_drawee_class3_code,
1502 argument28 =>p_drawee_bank_name,
1503 argument29 =>p_drawee_bank_branch_id,
1504 argument30 =>p_drawee_branch_city,
1505 argument31 =>p_br_sort_criteria,
1506 argument32 =>p_br_order,
1507 argument33 =>p_drawee_sort_criteria,
1508 argument34 =>p_drawee_order,
1509 argument35 =>p_physical_bill);
1510
1511 IF (l_request_id = 0) THEN
1512 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
1513 FND_MESSAGE.set_token('PROCEDURE','ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1514 APP_EXCEPTION.raise_exception;
1515 ELSE
1516 IF PG_DEBUG in ('Y', 'C') THEN
1517 arp_util.debug('create_remit_batch: ' || 'Submitted Request - '||l_program||'. Request ID ='||to_char(l_request_id));
1518 END IF;
1519 END IF;
1520
1521 p_control_count := l_batch_rec.control_count;
1522 p_control_amount := l_batch_rec.control_amount;
1523 p_request_id := l_request_id;
1524 p_batch_applied_status := l_batch_rec.batch_applied_status;
1525
1526 -- Update the batch row with the request id
1527 l_batch_rec.operation_request_id := l_request_id;
1528 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1529
1530 COMMIT;
1531
1532 IF PG_DEBUG in ('Y', 'C') THEN
1533 arp_util.debug('ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req (-)');
1534 END IF;
1535
1536 EXCEPTION
1537 WHEN FND_API.G_EXC_ERROR THEN
1538 IF PG_DEBUG in ('Y', 'C') THEN
1539 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1540 END IF;
1541 x_return_status := FND_API.G_RET_STS_ERROR;
1542 ROLLBACK TO create_conc_req_PVT;
1543
1544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1545 IF PG_DEBUG in ('Y', 'C') THEN
1546 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1547 END IF;
1548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1549 ROLLBACK TO create_conc_req_PVT;
1550
1551 WHEN OTHERS THEN
1552 IF PG_DEBUG in ('Y', 'C') THEN
1553 arp_util.debug('EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.create_remit_batch_conc_req');
1554 arp_util.debug('create_remit_batch: ' || SQLERRM);
1555 END IF;
1556 ROLLBACK TO create_conc_req_PVT;
1557 IF (SQLCODE = -20001) THEN
1558 x_return_status := FND_API.G_RET_STS_ERROR;
1559 RETURN;
1560 END IF;
1561
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563
1564 END create_remit_batch_conc_req;
1565
1566 /*===========================================================================+
1567 | PROCEDURE |
1568 | maintain_remit_batch_conc_req |
1569 | |
1570 | DESCRIPTION
1571 | Procedure called during the process create bills receivable |
1572 | remittance to submit the BR Remittance concurrent program |
1573 | as a concurrent request |
1574 | |
1575 | SCOPE - PUBLIC |
1576 | |
1577 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1578 | |
1579 | ARGUMENTS : IN : |
1580 | |
1581 | RETURNS : NONE |
1582 | |
1583 | NOTES |
1584 | |
1585 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/04/2000 |
1586 | |
1587 +===========================================================================*/
1588 PROCEDURE maintain_remit_batch_conc_req (
1589 p_api_version IN number,
1590 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1591 p_commit IN varchar2 := FND_API.G_FALSE,
1592 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1593 x_return_status OUT NOCOPY varchar2,
1594 x_msg_count OUT NOCOPY number,
1595 x_msg_data OUT NOCOPY varchar2,
1596 p_approve_flag IN varchar2,
1597 p_format_flag IN varchar2,
1598 p_print_flag IN varchar2,
1599 p_cancel_flag IN varchar2,
1600 p_print_bills_flag IN varchar2,
1601 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1602 p_physical_bill IN varchar2,
1603 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
1604 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
1605 p_request_id OUT NOCOPY AR_BATCHES.operation_request_id%TYPE,
1606 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1607
1608 l_api_name CONSTANT varchar2(30) := 'maintain_remit_batch_conc_req';
1609 l_api_version CONSTANT number := 1.0;
1610
1611 l_program varchar2(30) := 'ARBRRMCP';
1612
1613 l_batch_rec AR_BATCHES%ROWTYPE;
1614 l_control_count AR_BATCHES.control_count%TYPE;
1615 l_control_amount AR_BATCHES.control_amount%TYPE;
1616 l_request_id AR_BATCHES.operation_request_id%TYPE;
1617 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
1618
1619 l_field varchar2(30) := NULL;
1620
1621 p_create_flag varchar2(1) := 'N';
1622 l_org_id number;
1623 BEGIN
1624
1625 IF PG_DEBUG in ('Y', 'C') THEN
1626 arp_util.debug('ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req (+)');
1627 END IF;
1628
1629 SAVEPOINT maintain_conc_req_PVT;
1630
1631 -- Standard call to check for call compatability
1632 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1634 END IF;
1635
1636 x_return_status := FND_API.G_RET_STS_SUCCESS;
1637
1638 --------------------------------------------------
1639 -- validations
1640 --------------------------------------------------
1641 IF p_approve_flag NOT IN ('Y','N') THEN
1642 l_field := 'p_approve_flag';
1643 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1644 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1645 FND_MESSAGE.set_token('PARAMETER', l_field);
1646 APP_EXCEPTION.raise_exception;
1647 END IF;
1648
1649 IF p_format_flag NOT IN ('Y','N') THEN
1650 l_field := 'p_format_flag';
1651 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1652 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1653 FND_MESSAGE.set_token('PARAMETER', l_field);
1654 APP_EXCEPTION.raise_exception;
1655 END IF;
1656
1657 IF p_print_flag NOT IN ('Y','N') THEN
1658 l_field := 'p_print_flag';
1659 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1660 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1661 FND_MESSAGE.set_token('PARAMETER', l_field);
1662 APP_EXCEPTION.raise_exception;
1663 END IF;
1664
1665 IF p_cancel_flag NOT IN ('Y','N') THEN
1666 l_field := 'p_cancel_flag';
1667 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1668 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1669 FND_MESSAGE.set_token('PARAMETER', l_field);
1670 APP_EXCEPTION.raise_exception;
1671 END IF;
1672
1673 IF p_print_bills_flag NOT IN ('Y','N') THEN
1674 l_field := 'p_print_bills_flag';
1675 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
1676 FND_MESSAGE.set_token('PROCEDURE','maintain_remit_batch_conc_req');
1677 FND_MESSAGE.set_token('PARAMETER', l_field);
1678 APP_EXCEPTION.raise_exception;
1679 END IF;
1680
1681 --MOAC changes
1682 select org_id into l_org_id
1683 from ar_system_parameters;
1684
1685 -- Fetch of the remittance batch
1686 ARP_CR_BATCHES_PKG.fetch_p(p_batch_id,l_batch_rec);
1687
1688 -- Validation
1689 IF (p_cancel_flag = 'Y') AND (l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL','STARTED_CANCELLATION')) THEN
1690 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CANCEL_REMIT');
1691 APP_EXCEPTION.raise_exception;
1692 END IF;
1693
1694 IF (p_approve_flag = 'Y') AND (l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL')) THEN
1695 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_APPROVE_REMIT');
1696 APP_EXCEPTION.raise_exception;
1697 END IF;
1698
1699 --MOAC changes
1700 FND_REQUEST.SET_ORG_ID(l_org_id);
1701
1702 -- Submit the BR Remittance auto Create concurrent program as a concurrent request
1703 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1704 application => 'AR',
1705 program =>'ARBRRMCP',
1706 description => NULL,
1707 start_time => NULL,
1708 sub_request => NULL,
1709 argument1 =>p_create_flag,
1710 argument2 =>p_cancel_flag,
1711 argument3 =>p_approve_flag,
1712 argument4 =>p_format_flag,
1713 argument5 =>p_print_flag,
1714 argument6 =>p_print_bills_flag,
1715 argument7 =>p_batch_id,
1716 argument8 =>'',
1717 argument9 =>'',
1718 argument10 =>'',
1719 argument11 =>'',
1720 argument12 =>'',
1721 argument13 =>'',
1722 argument14 =>'',
1723 argument15 =>'',
1724 argument16 =>'',
1725 argument17 =>'',
1726 argument18 =>'',
1727 argument19 =>'',
1728 argument20 =>'',
1729 argument21 =>'',
1730 argument22 =>'',
1731 argument23 =>'',
1732 argument24 =>'',
1733 argument25 =>'',
1734 argument26 =>'',
1735 argument27 =>'',
1736 argument28 =>'',
1737 argument29 =>'',
1738 argument30 =>'',
1739 argument31 =>'',
1740 argument32 =>'',
1741 argument33 =>'',
1742 argument34 =>'',
1743 argument35 =>p_physical_bill);
1744
1745 IF (l_request_id = 0) THEN
1746 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
1747 FND_MESSAGE.set_token('PROCEDURE','ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1748 APP_EXCEPTION.raise_exception;
1749 ELSE
1750 IF PG_DEBUG in ('Y', 'C') THEN
1751 arp_util.debug('maintain_remit_batch_conc_req: ' || 'Submitted Request - '||l_program||'. Request ID ='||to_char(l_request_id));
1752 END IF;
1753 END IF;
1754
1755 -- Update the batch row with the request id and the batch applied status
1756 l_batch_rec.operation_request_id := l_request_id;
1757
1758 IF (p_cancel_flag = 'Y') THEN
1759 l_batch_rec.batch_applied_status := 'STARTED_CANCELLATION';
1760 ELSIF (p_approve_flag = 'Y') THEN
1761 l_batch_rec.batch_applied_status := 'STARTED_APPROVAL';
1762 ELSIF (p_format_flag = 'Y') AND (l_batch_rec.auto_trans_program_id IS NOT NULL) THEN
1763 l_batch_rec.batch_applied_status := 'STARTED_FORMAT';
1764 END IF;
1765
1766 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1767
1768 p_control_count := l_batch_rec.control_count;
1769 p_control_amount := l_batch_rec.control_amount;
1770 p_request_id := l_request_id;
1771 p_batch_applied_status := l_batch_rec.batch_applied_status;
1772
1773 COMMIT;
1774
1775 IF PG_DEBUG in ('Y', 'C') THEN
1776 arp_util.debug('ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req (-)');
1777 END IF;
1778
1779 EXCEPTION
1780 WHEN FND_API.G_EXC_ERROR THEN
1781 IF PG_DEBUG in ('Y', 'C') THEN
1782 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR: ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1783 END IF;
1784 x_return_status := FND_API.G_RET_STS_ERROR;
1785 ROLLBACK TO maintain_conc_req_PVT;
1786
1787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1788 IF PG_DEBUG in ('Y', 'C') THEN
1789 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1790 END IF;
1791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1792 ROLLBACK TO maintain_conc_req_PVT;
1793
1794 WHEN OTHERS THEN
1795 IF PG_DEBUG in ('Y', 'C') THEN
1796 arp_util.debug('EXCEPTION OTHERS: ARP_PROCESS_BR_REMIT.maintain_remit_batch_conc_req');
1797 arp_util.debug('maintain_remit_batch_conc_req: ' || SQLERRM);
1798 END IF;
1799 ROLLBACK TO maintain_conc_req_PVT;
1800 IF (SQLCODE = -20001) THEN
1801 x_return_status := FND_API.G_RET_STS_ERROR;
1802 RETURN;
1803 END IF;
1804
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806
1807 END maintain_remit_batch_conc_req;
1808
1809 /*===========================================================================+
1810 | PROCEDURE |
1811 | update_batch_status_after_create |
1812 | |
1813 | DESCRIPTION |
1814 | Procedure called during the process create bills receivable |
1815 | remittance to update the batch status to Completed_creation |
1816 | if the batch is created manually |
1817 | |
1818 | SCOPE - PUBLIC |
1819 | |
1820 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1821 | |
1822 | ARGUMENTS : IN : |
1823 | |
1824 | RETURNS : NONE |
1825 | |
1826 | NOTES |
1827 | |
1828 | MODIFICATION HISTORY - Created by Mireille Flahaut - 21/09/2000 |
1829 | bug 1407469 : Actions Window Create doesn't update batch applied_status |
1830 | |
1831 +===========================================================================*/
1832 PROCEDURE update_batch_status (
1833 p_api_version IN number,
1834 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
1835 p_commit IN varchar2 := FND_API.G_FALSE,
1836 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1837 x_return_status OUT NOCOPY varchar2,
1838 x_msg_count OUT NOCOPY number,
1839 x_msg_data OUT NOCOPY varchar2,
1840 p_batch_id IN AR_BATCHES.batch_id%TYPE,
1841 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE) IS
1842
1843 l_api_name CONSTANT varchar2(30) := 'update_batch_status';
1844 l_api_version CONSTANT number := 1.0;
1845
1846 l_batch_rec AR_BATCHES%ROWTYPE;
1847
1848 BEGIN
1849
1850 IF PG_DEBUG in ('Y', 'C') THEN
1851 arp_util.debug('ARP_PROCESS_BR_REMIT.update_batch_status (+)');
1852 END IF;
1853
1854 SAVEPOINT update_batch_status_PVT;
1855
1856 -- Standard call to check for call compatability
1857 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1859 END IF;
1860
1861 x_return_status := FND_API.G_RET_STS_SUCCESS;
1862
1863 -- lock and fetch of the batch row
1864 l_batch_rec.batch_id := p_batch_id;
1865 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
1866
1867 p_batch_applied_status := l_batch_rec.batch_applied_status;
1868
1869 -- The batch status is updated only if the batch status is STARTED_CREATION
1870 IF l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION') THEN
1871 return;
1872 END IF;
1873
1874 -- update the batch status to 'COMPLETED_CREATION'
1875 l_batch_rec.batch_applied_status := 'COMPLETED_CREATION';
1876 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
1877
1878 p_batch_applied_status := l_batch_rec.batch_applied_status;
1879
1880 commit;
1881
1882 IF PG_DEBUG in ('Y', 'C') THEN
1883 arp_util.debug('ARP_PROCESS_BR_REMIT.update_batch_status (-)');
1884 END IF;
1885
1886 EXCEPTION
1887 WHEN FND_API.G_EXC_ERROR THEN
1888 IF PG_DEBUG in ('Y', 'C') THEN
1889 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_PROCESS_BR_REMIT.update_batch_status');
1890 END IF;
1891 x_return_status := FND_API.G_RET_STS_ERROR;
1892 ROLLBACK TO update_batch_status_PVT;
1893
1894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1895 IF PG_DEBUG in ('Y', 'C') THEN
1896 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_PROCESS_BR_REMIT.update_batch_status');
1897 END IF;
1898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1899 ROLLBACK TO update_batch_status_PVT;
1900
1901 WHEN OTHERS THEN
1902 IF PG_DEBUG in ('Y', 'C') THEN
1903 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR:ARP_PROCESS_BR_REMIT.update_batch_status');
1904 arp_util.debug('update_batch_status: ' || SQLERRM);
1905 END IF;
1906 ROLLBACK TO update_batch_status_PVT;
1907 IF (SQLCODE = -20001) THEN
1908 x_return_status := FND_API.G_RET_STS_ERROR;
1909 RETURN;
1910 END IF;
1911
1912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1913
1914
1915 END update_batch_status;
1916
1917
1918 /*===========================================================================+
1919 | PROCEDURE |
1920 | test_rollback |
1921 | |
1922 | DESCRIPTION |
1923 | Procedure called during the process create bills receivable |
1924 | remittance to rollback the BR assignment |
1925 | |
1926 | SCOPE - PUBLIC |
1927 | |
1928 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1929 | |
1930 | ARGUMENTS : IN : |
1931 | |
1932 | RETURNS : NONE |
1933 | |
1934 | VERSION : Current version 1.0 |
1935 | Initial version 1.0 |
1936 | |
1937 | NOTES |
1938 | |
1939 | MODIFICATION HISTORY - Created by Mireille Flahaut - 12/07/2000 |
1940 | |
1941 +===========================================================================*/
1942 PROCEDURE test_rollback IS
1943 BEGIN
1944 rollback;
1945 END test_rollback;
1946
1947
1948 /*===========================================================================+
1949 | FUNCTION |
1950 | revision |
1951 | |
1952 | DESCRIPTION |
1953 | This function returns the revision number of this package. |
1954 | |
1955 | SCOPE - PUBLIC |
1956 | |
1957 | RETURNS : Revision number of this package |
1958 | |
1959 | MODIFICATION HISTORY |
1960 | 10 JAN 2001 John HALL Created |
1961 +===========================================================================*/
1962 FUNCTION revision RETURN VARCHAR2 IS
1963 BEGIN
1964 RETURN '$Revision: 120.7 $';
1965 END revision;
1966 --
1967
1968
1969
1970
1971 END ARP_PROCESS_BR_REMIT;
1972