[Home] [Help]
PACKAGE BODY: APPS.ARP_RW_BATCHES_PKG
Source
1 PACKAGE BODY ARP_RW_BATCHES_PKG AS
2 /* $Header: ARERBATB.pls 120.13.12010000.2 2009/08/25 05:58:20 nproddut ship $ */
3 --
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6
7 PROCEDURE validate_args_insert_manual(
8 p_row_id IN VARCHAR2,
9 p_batch_id IN ar_batches.batch_id%TYPE,
10 p_type IN ar_batches.type%TYPE,
11 p_batch_source_id IN ar_batches.batch_source_id%TYPE,
12 p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
13 p_name IN ar_batches.name%TYPE,
14 p_currency_code IN ar_batches.currency_code%TYPE,
15 p_gl_date IN ar_batches.gl_date%TYPE,
16 p_batch_date IN ar_batches.batch_date%TYPE,
17 p_batch_applied_status IN ar_batches.batch_applied_status%TYPE,
18 p_auto_batch_numbering IN ar_batch_sources.auto_batch_numbering%TYPE);
19 --
20 PROCEDURE validate_args_insert_remit(
21 p_row_id IN VARCHAR2,
22 p_batch_id IN ar_batches.batch_id%TYPE,
23 p_type IN ar_batches.type%TYPE,
24 p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
25 p_currency_code IN ar_batches.currency_code%TYPE,
26 p_gl_date IN ar_batches.gl_date%TYPE,
27 p_batch_date IN ar_batches.batch_date%TYPE,
28 p_remit_method_code IN ar_batches.remit_method_code%TYPE,
29 p_remittance_bank_branch_id IN
30 ar_batches.remittance_bank_branch_id%TYPE,
31 p_batch_applied_status IN ar_batches.batch_applied_status%TYPE );
32 --
33 PROCEDURE validate_args_insert_auto(
34 p_row_id IN VARCHAR2,
35 p_batch_id IN ar_batches.batch_id%TYPE,
36 p_type IN ar_batches.type%TYPE,
37 p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
38 p_name IN ar_batches.name%TYPE,
39 p_currency_code IN ar_batches.currency_code%TYPE,
40 p_batch_date IN ar_batches.batch_date%TYPE,
41 p_receipt_class_id IN
42 ar_batches.receipt_class_id%TYPE,
43 p_receipt_method_id IN
44 ar_batches.receipt_method_id%TYPE,
45 p_batch_applied_status IN ar_batches.batch_applied_status%TYPE );
46 --
47 /*===========================================================================+
48 | PROCEDURE |
49 | insert_manual_batch - Insert a row AR_BATCHES table after checking for|
50 | uniqueness |
51 | |
52 | DESCRIPTION |
53 | This function inserts a row in AR_BATCHES table after checking for |
54 | uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE |
55 | |
56 | SCOPE - PUBLIC |
57 | |
58 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
59 | arp_util.debug - debug procedure |
60 | |
61 | ARGUMENTS : IN: |
62 | p_row_id - Row ID |
63 | p_batch_source_id - Batch Source Id |
64 | p_batch_name - Batch Name |
65 | p_module_name - Module that called this procedure |
66 | p_module_version - Version of the module that called this |
67 | procedure |
68 | OUT: |
69 | |
70 | RETURNS : NONE |
71 | |
72 | NOTES - This procedure calls the check_unique_batch_name and |
73 | check_unique_media_ref procedures |
74 | |
75 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
76 | 19-MAR-96 Simon Leung Set batch status to 'NB' for new batch. |
77 +===========================================================================*/
78 PROCEDURE insert_manual_batch(
79 p_row_id IN OUT NOCOPY VARCHAR2,
80 p_batch_type IN VARCHAR2,
81 p_batch_id IN OUT NOCOPY ar_batches.batch_id%TYPE,
82 p_batch_source_id IN ar_batches.batch_source_id%TYPE,
83 p_batch_date IN ar_batches.batch_date%TYPE,
84 p_currency_code IN ar_batches.currency_code%TYPE,
85 p_name IN OUT NOCOPY ar_batches.name%TYPE,
86 p_comments IN ar_batches.comments%TYPE,
87 p_control_amount IN ar_batches.control_amount%TYPE,
88 p_control_count IN ar_batches.control_count%TYPE,
89 p_deposit_date IN ar_batches.deposit_date%TYPE,
90 p_exchange_date IN ar_batches.exchange_date%TYPE,
91 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
92 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
93 p_gl_date IN ar_batches.gl_date%TYPE,
94 p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
95 p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
96 p_remittance_bank_account_id
97 IN ar_batches.remit_bank_acct_use_id%TYPE,
98 p_remittance_bank_branch_id
99 IN ar_batches.remittance_bank_branch_id%TYPE,
100 p_attribute_category IN ar_batches.attribute_category%TYPE,
101 p_attribute1 IN ar_batches.attribute1%TYPE,
102 p_attribute2 IN ar_batches.attribute2%TYPE,
103 p_attribute3 IN ar_batches.attribute3%TYPE,
104 p_attribute4 IN ar_batches.attribute4%TYPE,
105 p_attribute5 IN ar_batches.attribute5%TYPE,
106 p_attribute6 IN ar_batches.attribute6%TYPE,
107 p_attribute7 IN ar_batches.attribute7%TYPE,
108 p_attribute8 IN ar_batches.attribute8%TYPE,
109 p_attribute9 IN ar_batches.attribute9%TYPE,
110 p_attribute10 IN ar_batches.attribute10%TYPE,
111 p_attribute11 IN ar_batches.attribute11%TYPE,
112 p_attribute12 IN ar_batches.attribute12%TYPE,
113 p_attribute13 IN ar_batches.attribute13%TYPE,
114 p_attribute14 IN ar_batches.attribute14%TYPE,
115 p_attribute15 IN ar_batches.attribute15%TYPE,
116 p_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
117 p_module_name IN VARCHAR2,
118 p_module_version IN VARCHAR2 ) IS
119 --
120 l_batch_rec ar_batches%ROWTYPE;
121 l_auto_batch_numbering ar_batch_sources.auto_batch_numbering%TYPE;
122 l_request_id ar_batches.request_id%TYPE;
123 l_batch_applied_status ar_batches.batch_applied_status%TYPE;
124 --
125 BEGIN
126 IF PG_DEBUG in ('Y', 'C') THEN
127 arp_util.debug('validate_args_insert_manual: ' || 'arp_rw_batches_pkg.insert_manual_batch()+' );
128 arp_util.debug('validate_args_insert_manual: ' || 'Row Id : '||p_row_id );
129 arp_util.debug('validate_args_insert_manual: ' || 'Batch Id : '||p_batch_id );
130 arp_util.debug('validate_args_insert_manual: ' || 'Batch Source Id : '||TO_CHAR( p_batch_source_id ) );
131 arp_util.debug('validate_args_insert_manual: ' || 'Batch Name : '||p_name );
132 arp_util.debug('validate_args_insert_manual: ' || 'GL Date : '||p_gl_date );
133 END IF;
134 --
135 -- Populate local batch record to be passed onto insert table handler
136 --
137 l_batch_rec.type := 'MANUAL';
138 l_batch_rec.status := 'NB';
139 --
140 IF ( p_batch_type = 'MANUAL_REGULAR' ) THEN
141 l_batch_rec.batch_applied_status := 'PROCESSED';
142 ELSE
143 l_batch_rec.batch_applied_status := 'POSTBATCH_WAITING';
144 END IF;
145 --
146 l_batch_rec.batch_date := p_batch_date;
147 l_batch_rec.batch_source_id := p_batch_source_id;
148 l_batch_rec.set_of_books_id := arp_global.set_of_books_id;
149 l_batch_rec.currency_code := p_currency_code;
150 l_batch_rec.name := p_name;
151 l_batch_rec.comments := p_comments;
152 l_batch_rec.control_amount := p_control_amount;
153 l_batch_rec.control_count := p_control_count;
154 l_batch_rec.deposit_date := p_deposit_date;
155 l_batch_rec.exchange_date := p_exchange_date;
156 l_batch_rec.exchange_rate := p_exchange_rate;
157 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
158 l_batch_rec.gl_date := p_gl_date;
159 l_batch_rec.receipt_class_id := p_receipt_class_id;
160 l_batch_rec.receipt_method_id := p_receipt_method_id;
161 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
162 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
163 l_batch_rec.attribute_category := p_attribute_category;
164 l_batch_rec.attribute1 := p_attribute1;
165 l_batch_rec.attribute2 := p_attribute2;
166 l_batch_rec.attribute3 := p_attribute3;
167 l_batch_rec.attribute4 := p_attribute4;
168 l_batch_rec.attribute5 := p_attribute5;
169 l_batch_rec.attribute6 := p_attribute6;
170 l_batch_rec.attribute7 := p_attribute7;
171 l_batch_rec.attribute8 := p_attribute8;
172 l_batch_rec.attribute9 := p_attribute9;
173 l_batch_rec.attribute10 := p_attribute10;
174 l_batch_rec.attribute11 := p_attribute11;
175 l_batch_rec.attribute12 := p_attribute12;
176 l_batch_rec.attribute13 := p_attribute13;
177 l_batch_rec.attribute14 := p_attribute14;
178 l_batch_rec.attribute15 := p_attribute15;
179 --
180 -- Get the batch numbering type from AR_BATCH_SOURCES
181 --
182 SELECT auto_batch_numbering
183 INTO l_auto_batch_numbering
184 FROM ar_batch_sources
185 WHERE batch_source_id = l_batch_rec.batch_source_id;
186 --
187 IF PG_DEBUG in ('Y', 'C') THEN
188 arp_util.debug('validate_args_insert_manual: ' || 'Auto Batch Num : '||l_auto_batch_numbering );
189 END IF;
190 --
191 -- Make sure that the row_id is null and a batch type is passed in
192 --
193 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
194 validate_args_insert_manual( p_row_id,
195 l_batch_rec.batch_id,
196 l_batch_rec.type,
197 l_batch_rec.batch_source_id,
198 l_batch_rec.set_of_books_id,
199 l_batch_rec.name,
200 l_batch_rec.currency_code,
201 l_batch_rec.gl_date,
202 l_batch_rec.batch_date,
203 l_batch_rec.batch_applied_status,
204 l_auto_batch_numbering );
205 END IF;
206 --
207 -- If automatic batch numbering is set to ON, then get the next
208 -- batch name from LAST_BATCH_NUM from AR_BATCH_SOURCES
209 --
210 IF ( l_auto_batch_numbering = 'AUTOMATIC' ) THEN
211 UPDATE ar_batch_sources
212 SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
213 WHERE batch_source_id = l_batch_rec.batch_source_id;
214 --
215 IF ( SQL%ROWCOUNT = 0 ) THEN
216 FND_MESSAGE.set_name( 'AR', 'AR_UPDNA_LAST_BATCH_NO' );
217 APP_EXCEPTION.raise_exception;
218 END IF;
219 --
220 SELECT NVL( LAST_BATCH_NUM, 0 )
221 INTO l_batch_rec.name
222 FROM ar_batch_sources
223 WHERE batch_source_id = l_batch_rec.batch_source_id;
224 --
225 p_name := l_batch_rec.name;
226 ELSE
227 l_batch_rec.name := p_name;
228 END IF;
229 --
230 -- Check for valid GL date
231 --
232 arp_util.validate_gl_date( l_batch_rec.gl_date,
233 NULL, NULL );
234 --
235 --
236 -- Call Check Unique Batch Name is unique for a given batch source id
237 --
238 arp_rw_batches_check_pkg.check_unique_batch_name( p_row_id,
239 l_batch_rec.batch_source_id,
240 l_batch_rec.name,
241 NULL,
242 NULL );
243 --
244 -- Call insert table handler
245 --
246 arp_cr_batches_pkg.insert_p( l_batch_rec,
247 p_row_id,
248 p_batch_id );
249 --
250 -- Populate output batch applied status
251 --
252 p_batch_applied_status := l_batch_rec.batch_applied_status;
253 --
254 IF PG_DEBUG in ('Y', 'C') THEN
255 arp_util.debug('validate_args_insert_manual: ' || 'arp_rw_batches_pkg.insert_manual_batch()-' );
256 END IF;
257 --
258 EXCEPTION
259 WHEN OTHERS THEN
260 IF PG_DEBUG in ('Y', 'C') THEN
261 arp_util.debug('validate_args_insert_manual: ' || 'EXCEPTION: arp_rw_batches_pkg.insert_manual_batch' );
262 END IF;
263 RAISE;
264 END insert_manual_batch;
265 --
266 /*===========================================================================+
267 | PROCEDURE |
268 | validate_args_insert_manual |
269 | |
270 | DESCRIPTION |
271 | Validate arguments passed to insert_manual_batch procedure |
272 | |
273 | SCOPE - PRIVATE |
274 | |
275 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
276 | arp_util.debug - debug procedure |
277 | |
278 | ARGUMENTS : IN: |
279 | p_type - Batch Type |
280 | OUT: |
281 | |
282 | RETURNS : NONE |
283 | |
284 | NOTES - |
285 | |
286 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
287 | |
288 +===========================================================================*/
289 PROCEDURE validate_args_insert_manual(
290 p_row_id IN VARCHAR2,
291 p_batch_id IN ar_batches.batch_id%TYPE,
292 p_type IN ar_batches.type%TYPE,
293 p_batch_source_id IN ar_batches.batch_source_id%TYPE,
294 p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
295 p_name IN ar_batches.name%TYPE,
296 p_currency_code IN ar_batches.currency_code%TYPE,
297 p_gl_date IN ar_batches.gl_date%TYPE,
298 p_batch_date IN ar_batches.batch_date%TYPE,
299 p_batch_applied_status IN ar_batches.batch_applied_status%TYPE,
300 p_auto_batch_numbering IN ar_batch_sources.auto_batch_numbering%TYPE) IS
301 l_field VARCHAR2(30);
302 BEGIN
303 IF PG_DEBUG in ('Y', 'C') THEN
304 arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_manual()+' );
305 END IF;
306 --
307 IF ( p_row_id IS NOT NULL ) THEN
308 l_field := 'ROW_ID';
309 ELSIF ( p_batch_id IS NOT NULL ) THEN
310 l_field := 'BATCH_ID';
311 ELSIF ( p_type IS NULL ) THEN
312 l_field := 'TYPE';
313 ELSIF ( p_set_of_books_id IS NULL ) THEN
314 l_field := 'SET_OF_BOOKS_ID';
315 ELSIF ( p_batch_date IS NULL ) THEN
316 l_field := 'BATCH_DATE';
317 ELSIF ( p_batch_applied_status IS NULL ) THEN
318 l_field := 'BATCH_APPLIED_STATUS';
319 ELSE
320 l_field := NULL;
321 END IF;
322 --
323 IF ( p_auto_batch_numbering = 'AUTOMATIC' ) THEN
324 IF ( p_name IS NOT NULL ) THEN
325 l_field := 'NAME';
326 END IF;
327 ELSE
328 IF ( p_name IS NULL ) THEN
329 l_field := 'NAME';
330 END IF;
331 END IF;
332 --
333 IF ( l_field IS NOT NULL ) THEN
334 FND_MESSAGE.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
335 FND_MESSAGE.set_token('PROCEDURE', 'VALIDATE_ARGS_INSERT_MANUAL');
336 FND_MESSAGE.set_token('PARAMETER', l_field);
337 APP_EXCEPTION.raise_exception;
338 END IF;
339 --
340 IF PG_DEBUG in ('Y', 'C') THEN
341 arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_manual()-' );
342 END IF;
343 --
344 EXCEPTION
345 WHEN OTHERS THEN
346 IF PG_DEBUG in ('Y', 'C') THEN
347 arp_util.debug('validate_args_insert_manual: ' ||
348 'EXCEPTION: arp_rw_batches_pkg.validate_args_insert_manual' );
349 END IF;
350 RAISE;
351 END validate_args_insert_manual;
352 --
353 /*===========================================================================+
354 | PROCEDURE |
355 | insert_auto_batch - Insert a row AR_BATCHES table after checking for|
356 | uniqueness |
357 | |
358 | DESCRIPTION |
359 | This function inserts a row in AR_BATCHES table after checking for |
360 | uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE |
361 | |
362 | SCOPE - PUBLIC |
363 | |
364 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
365 | arp_util.debug - debug procedure |
366 | |
367 | ARGUMENTS : IN: |
368 | p_row_id - Row ID |
369 | p_batch_source_id - Batch Source Id |
370 | p_batch_name - Batch Name |
371 | p_module_name - Module that called this procedure |
372 | p_module_version - Version of the module that called this |
373 | procedure |
374 | OUT: |
375 | |
376 | RETURNS : NONE |
377 | |
378 | NOTES - This procedure calls the check_unique_batch_name and |
379 | check_unique_media_ref procedures |
380 | |
381 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
382 | 09-FEB-96 Simon Leung If confirm is required, gl_date should be |
383 | null. |
384 | 16-DEC-03 K Mahajan Added Batch-related validations to the |
385 | insert_auto_batch procedure as part |
386 | of the changes for bug 3167260 |
387 +===========================================================================*/
388 PROCEDURE insert_auto_batch(
389 p_row_id IN OUT NOCOPY VARCHAR2,
390 p_batch_id IN OUT NOCOPY ar_batches.batch_id%TYPE,
391 p_batch_date IN ar_batches.batch_date%TYPE,
392 p_currency_code IN ar_batches.currency_code%TYPE,
393 p_name IN OUT NOCOPY ar_batches.name%TYPE,
394 p_comments IN ar_batches.comments%TYPE,
395 p_exchange_date IN ar_batches.exchange_date%TYPE,
396 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
397 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
398 p_gl_date IN ar_batches.gl_date%TYPE,
399 p_media_reference IN ar_batches.media_reference%TYPE,
400 p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
401 p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
402 p_attribute_category IN ar_batches.attribute_category%TYPE,
403 p_attribute1 IN ar_batches.attribute1%TYPE,
404 p_attribute2 IN ar_batches.attribute2%TYPE,
405 p_attribute3 IN ar_batches.attribute3%TYPE,
406 p_attribute4 IN ar_batches.attribute4%TYPE,
407 p_attribute5 IN ar_batches.attribute5%TYPE,
408 p_attribute6 IN ar_batches.attribute6%TYPE,
409 p_attribute7 IN ar_batches.attribute7%TYPE,
410 p_attribute8 IN ar_batches.attribute8%TYPE,
411 p_attribute9 IN ar_batches.attribute9%TYPE,
412 p_attribute10 IN ar_batches.attribute10%TYPE,
413 p_attribute11 IN ar_batches.attribute11%TYPE,
414 p_attribute12 IN ar_batches.attribute12%TYPE,
415 p_attribute13 IN ar_batches.attribute13%TYPE,
416 p_attribute14 IN ar_batches.attribute14%TYPE,
417 p_attribute15 IN ar_batches.attribute15%TYPE,
418 p_call_conc_req IN VARCHAR2,
419 p_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
420 p_request_id OUT NOCOPY ar_batches.request_id%TYPE,
421 p_module_name IN VARCHAR2,
422 p_module_version IN VARCHAR2,
423 p_bank_account_low IN VARCHAR2,
424 p_bank_account_high IN VARCHAR2 ) IS
425 --
426 l_batch_rec ar_batches%ROWTYPE;
427 l_batch_applied_status ar_batches.batch_applied_status%TYPE;
428 l_request_id ar_batches.request_id%TYPE;
429 l_confirm_required VARCHAR2(1);
430 CURSOR c_receipt_class IS
431 SELECT confirm_flag
432 FROM ar_receipt_classes
433 WHERE receipt_class_id = p_receipt_class_id;
434
435 CURSOR c_valid_receipt_class IS
436 SELECT rc.receipt_class_id
437 FROM ar_receipt_classes rc
438 WHERE rc.receipt_class_id = l_batch_rec.receipt_class_id
439 AND rc.creation_method_code = 'AUTOMATIC';
440
441
442 CURSOR c_valid_receipt_method IS
443 SELECT rm.receipt_method_id
444 FROM ar_receipt_methods rm
445 WHERE rm.receipt_method_id = l_batch_rec.receipt_method_id
446 AND rm.receipt_class_id = l_batch_rec.receipt_class_id
447 AND l_batch_rec.batch_date between rm.start_date and nvl(rm.end_date, l_batch_rec.batch_date)
448 AND EXISTS (SELECT 1
449 FROM ar_receipt_method_accounts rma,
450 ce_bank_accounts cba,
451 ce_bank_acct_uses ba
452 WHERE rma.receipt_method_id = l_batch_rec.receipt_method_id
453 AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
454 AND cba.bank_account_id = ba.bank_account_id
455 AND cba.currency_code = decode( cba.receipt_multi_currency_flag, 'Y', cba.currency_code,l_batch_rec.currency_code)
456 AND l_batch_rec.batch_date <= nvl(ba.end_date, l_batch_rec.batch_date)
457 AND l_batch_rec.batch_date between rma.start_date and nvl(rma.end_date, l_batch_rec.batch_date));
458
459 l_valid NUMBER;
460 l_func_curr_code VARCHAR2(15);
461
462 BEGIN
463 IF PG_DEBUG in ('Y', 'C') THEN
464 arp_util.debug( 'arp_rw_batches_pkg.insert_auto_batch()+' );
465 arp_util.debug('insert_auto_batch: ' || 'Row Id : '||p_row_id );
466 arp_util.debug('insert_auto_batch: ' || 'Batch Id : '||p_batch_id );
467 arp_util.debug('insert_auto_batch: ' || 'GL Date : '||p_gl_date );
468 arp_util.debug('insert_auto_batch: ' || 'Bank_account_low : '||p_bank_account_low );
469 arp_util.debug('insert_auto_batch: ' || 'Bank_account_high : '||p_bank_account_high );
470 END IF;
471
472 --
473 -- Populate local batch record to be passed onto insert table handler
474 --
475 l_batch_rec.type := 'CREATION';
476 l_batch_rec.batch_applied_status := 'STARTED_CREATION';
477 l_batch_rec.batch_date := p_batch_date;
478 l_batch_rec.set_of_books_id := arp_global.set_of_books_id;
479 l_batch_rec.currency_code := p_currency_code;
480 l_batch_rec.comments := p_comments;
481 l_batch_rec.exchange_date := p_exchange_date;
482 l_batch_rec.exchange_rate := p_exchange_rate;
483 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
484 l_batch_rec.gl_date := p_gl_date;
485 l_batch_rec.media_reference := p_media_reference;
486 -- l_batch_rec.operation_request_id := FND_GLOBAL.request_id;
487 l_batch_rec.receipt_class_id := p_receipt_class_id;
488 l_batch_rec.receipt_method_id := p_receipt_method_id;
489 l_batch_rec.attribute_category := p_attribute_category;
490 l_batch_rec.attribute1 := p_attribute1;
491 l_batch_rec.attribute2 := p_attribute2;
492 l_batch_rec.attribute3 := p_attribute3;
493 l_batch_rec.attribute4 := p_attribute4;
494 l_batch_rec.attribute5 := p_attribute5;
495 l_batch_rec.attribute6 := p_attribute6;
496 l_batch_rec.attribute7 := p_attribute7;
497 l_batch_rec.attribute8 := p_attribute8;
498 l_batch_rec.attribute9 := p_attribute9;
499 l_batch_rec.attribute10 := p_attribute10;
500 l_batch_rec.attribute11 := p_attribute11;
501 l_batch_rec.attribute12 := p_attribute12;
502 l_batch_rec.attribute13 := p_attribute13;
503 l_batch_rec.attribute14 := p_attribute14;
504 l_batch_rec.attribute15 := p_attribute15;
505 --
506 -- Make sure that the row_id is null and a batch type is passed in
507 --
508 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
509 validate_args_insert_auto( p_row_id,
510 l_batch_rec.batch_id,
511 l_batch_rec.type,
512 l_batch_rec.set_of_books_id,
513 l_batch_rec.name,
514 l_batch_rec.currency_code,
515 l_batch_rec.batch_date,
516 l_batch_rec.receipt_class_id,
517 l_batch_rec.receipt_method_id,
518 l_batch_rec.batch_applied_status );
519 END IF;
520 --
521 OPEN c_receipt_class;
522 FETCH c_receipt_class INTO l_confirm_required;
523 CLOSE c_receipt_class;
524 --
525
526 /* bug3451722 */
527 IF ( l_confirm_required = 'Y' and p_module_name = 'AUTORECSRS') THEN
528 l_batch_rec.gl_date := null;
529 END IF;
530 IF (l_confirm_required = 'Y') THEN
531 --bug 5465097
532 l_batch_rec.gl_date := null;
533 ELSE
534 -- Check for valid GL date
535 --
536 arp_util.validate_gl_date( l_batch_rec.gl_date,
537 NULL, NULL );
538 END IF;
539
540 -- check Receipt Class ID is valid
541 if (l_batch_rec.receipt_class_id is not null) then
542 open c_valid_receipt_class;
543 fetch c_valid_receipt_class into l_valid;
544 if c_valid_receipt_class%NOTFOUND then
545 close c_valid_receipt_class;
546 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
547 fnd_message.set_token('PROCEDURE', 'INSERT_AUTO_BATCH');
548 fnd_message.set_token('PARAMETER', 'RECEIPT_CLASS_ID' );
549 app_exception.raise_exception;
550 end if;
551 close c_valid_receipt_class;
552 end if;
553
554 -- check Receipt Payment Method ID is valid
555 if (l_batch_rec.receipt_method_id is not null) then
556 open c_valid_receipt_method;
557 fetch c_valid_receipt_method into l_valid;
558 if c_valid_receipt_method%NOTFOUND then
559 close c_valid_receipt_method;
560 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
561 fnd_message.set_token('PROCEDURE', 'INSERT_AUTO_BATCH');
562 fnd_message.set_token('PARAMETER', 'RECEIPT_METHOD_ID' );
563 app_exception.raise_exception;
564 end if;
565 close c_valid_receipt_method;
566 end if;
567
568 -- check if Exchange Rates are required and derive them, if needed
569 l_func_curr_code := arpcurr.getfunctcurr(l_batch_rec.set_of_books_id);
570 if (l_func_curr_code <> l_batch_rec.currency_code) then
571 if (l_batch_rec.exchange_date is null) then
572 l_batch_rec.exchange_date := l_batch_rec.batch_date;
573 end if;
574 if (l_batch_rec.exchange_rate_type is null) then
575 l_batch_rec.exchange_rate_type := fnd_profile.value('AR_DEFAULT_EXCHANGE_RATE_TYPE');
576 end if;
577 if (l_batch_rec.exchange_rate is null) then
578 l_batch_rec.exchange_rate := arpcurr.getrate(l_batch_rec.currency_code,
579 l_func_curr_code, l_batch_rec.exchange_date, l_batch_rec.exchange_rate_type);
580 if ( gl_currency_api.is_fixed_rate(l_batch_rec.currency_code,
581 l_func_curr_code, l_batch_rec.exchange_date) = 'Y') then l_batch_rec.exchange_rate_type := 'EMU FIXED';
582 end if;
583 end if;
584 if (nvl(l_batch_rec.exchange_rate, -1) = -1) then
585 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
586 fnd_message.set_token('PROCEDURE', 'INSERT_AUTO_BATCH');
587 fnd_message.set_token('PARAMETER', 'EXCHANGE_RATE(_TYPE)' );
588 app_exception.raise_exception;
589 end if;
590 end if;
591
592 --
593 -- Call Check Unique Media Reference procedure, if the Batch Type is
594 -- not 'MANUAL' and media reference is not NULL
595 --
596 IF ( l_batch_rec.media_reference IS NOT NULL ) THEN
597 arp_rw_batches_check_pkg.check_unique_media_ref( p_row_id,
598 l_batch_rec.media_reference,
599 NULL, NULL );
600 END IF;
601 --
602 -- IF type is not manual, then set batch_source_id to 1 and
603 -- get the batch source name to populate the form field
604 --
605 l_batch_rec.batch_source_id := 1;
606 --
607 -- automatic batch numbering is set to ON, so get the next
608 -- batch name from LAST_BATCH_NUM from AR_BATCH_SOURCES
609 --
610 UPDATE ar_batch_sources
611 SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
612 WHERE batch_source_id = l_batch_rec.batch_source_id;
613 --
614 IF ( SQL%ROWCOUNT = 0 ) THEN
615 FND_MESSAGE.set_name( 'AR', 'AR_UPDNA_LAST_BATCH_NO' );
616 APP_EXCEPTION.raise_exception;
617 END IF;
618 --
619 SELECT NVL( LAST_BATCH_NUM, 0 )
620 INTO l_batch_rec.name
621 FROM ar_batch_sources
622 WHERE batch_source_id = l_batch_rec.batch_source_id;
623 --
624 p_name := l_batch_rec.name;
625 --
626 -- Call Check Unique Batch Name is unique for a given batch source id
627 --
628 arp_rw_batches_check_pkg.check_unique_batch_name( p_row_id,
629 l_batch_rec.batch_source_id,
630 l_batch_rec.name,
631 NULL,
632 NULL );
633 --
634 -- Call insert table handler
635 --
636 arp_cr_batches_pkg.insert_p( l_batch_rec,
637 p_row_id,
638 p_batch_id );
639 --
640 -- Call the concurrent program to create receipts under the batch,
641 -- if the procedure is not called from BOE
642 --
643 --
644 IF ( p_call_conc_req = 'Y' ) THEN
645 arp_process_boe.create_auto_batch_conc_req(
646 'Y',
647 'N',
648 'N',
649 p_batch_id,
650 NULL,
651 NULL,
652 NULL,
653 NULL,
654 NULL,
655 NULL,
656 NULL,
657 NULL,
658 NULL,
659 NULL,
660 NULL,
661 NULL,
662 NULL,
663 NULL,
664 NULL,
665 NULL,
666 NULL,
667 NULL,
668 l_request_id, l_batch_applied_status,
669 p_module_name, p_module_version,
670 p_bank_account_low, p_bank_account_high );
671 --
672 -- Populate operation_request_id
673 --
674 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
675 p_request_id := l_request_id;
676 l_batch_rec.operation_request_id := l_request_id;
677 arp_cr_batches_pkg.update_p( l_batch_rec, p_batch_id );
678 END IF;
679 --
680 p_batch_applied_status := l_batch_applied_status;
681 --
682 IF PG_DEBUG in ('Y', 'C') THEN
683 arp_util.debug( 'arp_rw_batches_pkg.insert_auto_batch()-' );
684 END IF;
685 --
686 EXCEPTION
687 WHEN OTHERS THEN
688 IF PG_DEBUG in ('Y', 'C') THEN
689 arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.insert_auto_batch' );
690 END IF;
691 RAISE;
692 END insert_auto_batch;
693 --
694 /*===========================================================================+
695 | PROCEDURE |
696 | validate_args_insert_auto |
697 | |
698 | DESCRIPTION |
699 | Validate arguments passed to insert_auto_batch procedure |
700 | |
701 | SCOPE - PRIVATE |
702 | |
703 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
704 | arp_util.debug - debug procedure |
705 | |
706 | ARGUMENTS : IN: |
707 | p_type - Batch Type |
708 | OUT: |
709 | |
710 | RETURNS : NONE |
711 | |
712 | NOTES - |
713 | |
714 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
715 | |
716 +===========================================================================*/
717 PROCEDURE validate_args_insert_auto(
718 p_row_id IN VARCHAR2,
719 p_batch_id IN ar_batches.batch_id%TYPE,
720 p_type IN ar_batches.type%TYPE,
721 p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
722 p_name IN ar_batches.name%TYPE,
723 p_currency_code IN ar_batches.currency_code%TYPE,
724 p_batch_date IN ar_batches.batch_date%TYPE,
725 p_receipt_class_id IN
726 ar_batches.receipt_class_id%TYPE,
727 p_receipt_method_id IN
728 ar_batches.receipt_method_id%TYPE,
729 p_batch_applied_status IN ar_batches.batch_applied_status%TYPE ) IS
730 l_field VARCHAR2(30);
731 BEGIN
732 IF PG_DEBUG in ('Y', 'C') THEN
733 arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_auto()+' );
734 END IF;
735 --
736 IF ( p_row_id IS NOT NULL ) THEN
737 l_field := 'ROW_ID';
738 ELSIF ( p_batch_id IS NOT NULL ) THEN
739 l_field := 'BATCH_ID';
740 ELSIF ( p_type IS NULL ) THEN
741 l_field := 'TYPE';
742 ELSIF ( p_set_of_books_id IS NULL ) THEN
743 l_field := 'SET_OF_BOOKS_ID';
744 ELSIF ( p_currency_code IS NULL ) THEN
745 l_field := 'CURRENCY_CODE';
746 ELSIF ( p_batch_date IS NULL ) THEN
747 l_field := 'BATCH_DATE';
748 ELSIF ( p_batch_applied_status IS NULL ) THEN
749 l_field := 'BATCH_APPLIED_STATUS';
750 ELSIF ( p_receipt_class_id IS NULL ) THEN
751 l_field := 'RECEIPT_CLASS_ID';
752 ELSIF ( p_receipt_method_id IS NULL ) THEN
753 l_field := 'RECEIPT_METHOD_ID';
754 ELSE
755 l_field := NULL;
756 END IF;
757 --
758 IF ( l_field IS NOT NULL ) THEN
759 FND_MESSAGE.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
760 FND_MESSAGE.set_token('PROCEDURE', 'VALIDATE_ARGS_INSERT_AUTO');
761 FND_MESSAGE.set_token('PARAMETER', l_field );
762 APP_EXCEPTION.raise_exception;
763 END IF;
764 --
765 IF PG_DEBUG in ('Y', 'C') THEN
766 arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_auto()-' );
767 END IF;
768 --
769 EXCEPTION
770 WHEN OTHERS THEN
771 IF PG_DEBUG in ('Y', 'C') THEN
772 arp_util.debug('validate_args_insert_auto: ' ||
773 'EXCEPTION: arp_rw_batches_pkg.validate_args_insert_auto' );
774 END IF;
775 RAISE;
776 END validate_args_insert_auto;
777 --
778 /*===========================================================================+
779 | PROCEDURE |
780 | insert_remit_batch - Insert a row AR_BATCHES table after checking for|
781 | uniqueness |
782 | |
783 | DESCRIPTION |
784 | This function inserts a row in AR_BATCHES table after checking for |
785 | uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE |
786 | |
787 | SCOPE - PUBLIC |
788 | |
789 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
790 | arp_util.debug - debug procedure |
791 | |
792 | ARGUMENTS : IN: |
793 | p_row_id - Row ID |
794 | p_batch_name - Batch Name |
795 | p_module_name - Module that called this procedure |
796 | p_module_version - Version of the module that called this |
797 | procedure |
798 | OUT: |
799 | |
800 | RETURNS : NONE |
801 | |
802 | NOTES - This procedure calls the check_unique_batch_name and |
803 | check_unique_media_ref procedures |
804 | |
805 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
806 | 25-JAN-96 Simon Leung Implemented logic to set batch_applied_status|
807 | for auto-creation and manual-creation batch |
808 | Removed logic to submit conc req process. |
809 | 09-FEB-96 Simon Leung If the bank is a clearing house, no remit |
810 | bank account should be specified. |
811 | 13-MAY-96 Simon Leung Added new parameter receipt_class_id. |
812 | 16-DEC-03 K Mahajan Added validations for Auto-Remit SRS program |
813 | as part of the fix for bug / enh. 3167260 |
814 | 12-JAN-04 M Ryzhikova Modified fix for 3157260 to incorporate |
815 | consolidated bank accounts changes |
816 +===========================================================================*/
817 PROCEDURE insert_remit_batch(
818 p_row_id IN OUT NOCOPY VARCHAR2,
819 p_batch_id IN OUT NOCOPY ar_batches.batch_id%TYPE,
820 p_batch_date IN ar_batches.batch_date%TYPE,
821 p_currency_code IN ar_batches.currency_code%TYPE,
822 p_name IN OUT NOCOPY ar_batches.name%TYPE,
823 p_comments IN ar_batches.comments%TYPE,
824 p_exchange_date IN ar_batches.exchange_date%TYPE,
825 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
826 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
827 p_gl_date IN ar_batches.gl_date%TYPE,
828 p_media_reference IN ar_batches.media_reference%TYPE,
829 p_remit_method_code IN ar_batches.remit_method_code%TYPE,
830 p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
831 p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
832 p_remittance_bank_account_id
833 IN ar_batches.remit_bank_acct_use_id%TYPE,
834 p_remittance_bank_branch_id
835 IN ar_batches.remittance_bank_branch_id%TYPE,
836 p_bank_deposit_number IN ar_batches.bank_deposit_number%TYPE,
837 p_attribute_category IN ar_batches.attribute_category%TYPE,
838 p_attribute1 IN ar_batches.attribute1%TYPE,
839 p_attribute2 IN ar_batches.attribute2%TYPE,
840 p_attribute3 IN ar_batches.attribute3%TYPE,
841 p_attribute4 IN ar_batches.attribute4%TYPE,
842 p_attribute5 IN ar_batches.attribute5%TYPE,
843 p_attribute6 IN ar_batches.attribute6%TYPE,
844 p_attribute7 IN ar_batches.attribute7%TYPE,
845 p_attribute8 IN ar_batches.attribute8%TYPE,
846 p_attribute9 IN ar_batches.attribute9%TYPE,
847 p_attribute10 IN ar_batches.attribute10%TYPE,
848 p_attribute11 IN ar_batches.attribute11%TYPE,
849 p_attribute12 IN ar_batches.attribute12%TYPE,
850 p_attribute13 IN ar_batches.attribute13%TYPE,
851 p_attribute14 IN ar_batches.attribute14%TYPE,
852 p_attribute15 IN ar_batches.attribute15%TYPE,
853 p_auto_creation IN VARCHAR2,
854 p_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
855 p_module_name IN VARCHAR2,
856 p_module_version IN VARCHAR2 ) IS
857 --
858 l_batch_rec ar_batches%ROWTYPE;
859 l_institution_type VARCHAR2(30);
860 CURSOR c_bank_branches IS
861 SELECT bank_institution_type
862 FROM ce_bank_branches_v
863 WHERE branch_party_id = p_remittance_bank_branch_id;
864
865 CURSOR c_valid_receipt_class IS
866 SELECT rc.receipt_class_id
867 FROM ar_receipt_classes rc
868 WHERE rc.receipt_class_id = l_batch_rec.receipt_class_id
869 AND rc.remit_flag = 'Y'
870 AND rc.creation_method_code not in ('BR','BR_REMIT')
871 AND (rc.remit_method_code = l_batch_rec.remit_method_code
872 OR rc.remit_method_code = 'STANDARD_AND_FACTORING');
873
874 CURSOR c_valid_receipt_method IS
875 SELECT rm.receipt_method_id
876 FROM ar_receipt_methods rm
877 WHERE rm.receipt_method_id = l_batch_rec.receipt_method_id
878 AND rm.receipt_class_id = l_batch_rec.receipt_class_id
879 AND l_batch_rec.batch_date between rm.start_date and nvl(rm.end_date, l_batch_rec.batch_date);
880
881
882 CURSOR c_valid_bank_branch IS
883 SELECT distinct bb.branch_party_id
884 FROM ce_bank_branches_v bb
885 WHERE bb.branch_party_id = l_batch_rec.remittance_bank_branch_id
886 AND ( bb.branch_party_id in
887 (SELECT cba.bank_branch_id
888 FROM ce_bank_accounts cba,
889 ce_bank_acct_uses ba
890 WHERE cba.bank_account_id = ba.bank_account_id
891 AND ba.bank_acct_use_id =l_batch_rec.remit_bank_acct_use_id)
892 AND l_batch_rec.remit_bank_acct_use_id is not null)
893 OR (bb.bank_institution_type = 'CLEARING HOUSE'
894 AND l_batch_rec.remit_bank_acct_use_id is null
895 AND EXISTS (SELECT 1
896 FROM ar_receipt_method_accounts rma2,
897 ce_bank_accounts cba2,
898 ce_bank_acct_uses ba2,
899 ce_bank_branches_v bb2,
900 hz_parties CHParty,
901 hz_parties RelParty,
902 hz_relationships BCRel,
903 hz_contact_points Eft
904 WHERE rma2.receipt_method_id = l_batch_rec.receipt_method_id
905 AND rma2.remit_bank_acct_use_id = ba2.bank_acct_use_id
906 AND cba2.bank_account_id = ba2.bank_account_id
907 AND cba2.currency_code = l_batch_rec.currency_code
908 AND cba2.bank_branch_id = bb2.branch_party_id
909 AND BCRel.subject_id = bb2.branch_party_id
910 AND BCRel.subject_type = 'ORGANIZATION'
911 AND BCRel.subject_table_name = 'HZ_PARTIES'
912 AND BCRel.object_id = CHParty.party_id
913 AND BCRel.object_type = 'ORGANIZATION'
914 AND BCRel.object_table_name = 'HZ_PARTIES'
915 AND BCRel.relationship_type = 'CLEARINGHOUSE_BANK'
916 AND BCRel.relationship_code = 'USES_CLEARINGHOUSE'
917 AND BCRel.status = 'A'
918 AND RelParty.party_id = BCRel.party_id
919 AND Eft.owner_table_name(+) = 'HZ_PARTIES'
920 AND Eft.owner_table_id(+) = CHParty.party_id
921 AND Eft.contact_point_type(+) = 'EFT'
922 AND Eft.status(+) = 'A'
923 AND CHParty.party_id = bb.branch_party_id));
924
925 CURSOR c_valid_bank_account IS
926 SELECT ba.bank_acct_use_id
927 FROM ce_bank_accounts cba,
928 ce_bank_acct_uses ba,
929 ce_bank_branches_v br,
930 ar_receipt_method_accounts rma
931 WHERE ba.bank_acct_use_id = l_batch_rec.remit_bank_acct_use_id
932 AND rma.receipt_method_id = nvl(l_batch_rec.receipt_method_id, rma.receipt_method_id)
933 AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
934 AND cba.bank_account_id = ba.bank_account_id
935 AND cba.bank_branch_id = l_batch_rec.remittance_bank_branch_id
936 AND cba.bank_branch_id = br.branch_party_id
937 AND (cba.currency_code = l_batch_rec.currency_code
938 OR cba.receipt_multi_currency_flag = 'Y')
939 AND l_batch_rec.batch_date <= nvl(ba.end_date, l_batch_rec.batch_date)
940 AND l_batch_rec.batch_date between rma.start_date and nvl(rma.end_date, l_batch_rec.batch_date);
941
942 l_valid NUMBER;
943
944 BEGIN
945 IF PG_DEBUG in ('Y', 'C') THEN
946 arp_util.debug( 'arp_rw_batches_pkg.insert_remit_batch()+' );
947 arp_util.debug('insert_remit_batch: ' || 'Row Id : '||p_row_id );
948 arp_util.debug('insert_remit_batch: ' || 'Batch Id : '||p_batch_id );
949 arp_util.debug('insert_remit_batch: ' || 'GL Date : '||p_gl_date );
950 END IF;
951 --
952 -- Populate local batch record to be passed onto insert table handler
953 --
954 l_batch_rec.type := 'REMITTANCE';
955 --
956 -- For automatic creation, set status to STARTED_CREATION and let
957 -- the concurrent process set the status to COMPLETED_CREATION when
958 -- it finishes. For manual creation, set it to COMPLETED_CREATION.
959 --
960 IF ( p_auto_creation = 'Y' ) THEN
961 l_batch_rec.batch_applied_status := 'STARTED_CREATION';
962 ELSE
963 l_batch_rec.batch_applied_status := 'COMPLETED_CREATION';
964 END IF;
965 --
966 l_batch_rec.batch_date := p_batch_date;
967 l_batch_rec.set_of_books_id := arp_global.set_of_books_id;
968 l_batch_rec.currency_code := p_currency_code;
969 l_batch_rec.comments := p_comments;
970 l_batch_rec.exchange_date := p_exchange_date;
971 l_batch_rec.exchange_rate := p_exchange_rate;
972 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
973 l_batch_rec.gl_date := p_gl_date;
974 l_batch_rec.media_reference := p_media_reference;
975 l_batch_rec.remit_method_code := p_remit_method_code;
976 l_batch_rec.receipt_class_id := p_receipt_class_id;
977 l_batch_rec.receipt_method_id := p_receipt_method_id;
978 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
979 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
980 l_batch_rec.attribute_category := p_attribute_category;
981 l_batch_rec.attribute1 := p_attribute1;
982 l_batch_rec.attribute2 := p_attribute2;
983 l_batch_rec.attribute3 := p_attribute3;
984 l_batch_rec.attribute4 := p_attribute4;
985 l_batch_rec.attribute5 := p_attribute5;
986 l_batch_rec.attribute6 := p_attribute6;
987 l_batch_rec.attribute7 := p_attribute7;
988 l_batch_rec.attribute8 := p_attribute8;
989 l_batch_rec.attribute9 := p_attribute9;
990 l_batch_rec.attribute10 := p_attribute10;
991 l_batch_rec.attribute11 := p_attribute11;
992 l_batch_rec.attribute12 := p_attribute12;
993 l_batch_rec.attribute13 := p_attribute13;
994 l_batch_rec.attribute14 := p_attribute14;
995 l_batch_rec.attribute15 := p_attribute15;
996 l_batch_rec.bank_deposit_number := p_bank_deposit_number;
997 --
998 -- Make sure that the row_id is null and a batch type is passed in
999 --
1000 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
1001 validate_args_insert_remit( p_row_id,
1002 l_batch_rec.batch_id,
1003 l_batch_rec.type,
1004 l_batch_rec.set_of_books_id,
1005 l_batch_rec.currency_code,
1006 l_batch_rec.gl_date,
1007 l_batch_rec.batch_date,
1008 l_batch_rec.remit_method_code,
1009 l_batch_rec.remittance_bank_branch_id,
1010 l_batch_rec.batch_applied_status );
1011 END IF;
1012 --
1013 OPEN c_bank_branches;
1014 FETCH c_bank_branches INTO l_institution_type;
1015 CLOSE c_bank_branches;
1016 --
1017 IF ( l_institution_type = 'CLEARING HOUSE' ) THEN
1018 IF ( l_batch_rec.remit_bank_acct_use_id IS NOT NULL ) THEN
1019 FND_MESSAGE.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1020 FND_MESSAGE.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
1021 FND_MESSAGE.set_token('PARAMETER', 'REMITTANCE_BANK_ACCOUNT_ID' );
1022 APP_EXCEPTION.raise_exception;
1023 END IF;
1024 ELSIF (l_batch_rec.remit_bank_acct_use_id IS NULL) THEN
1025 FND_MESSAGE.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1026 FND_MESSAGE.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
1027 FND_MESSAGE.set_token('PARAMETER', 'REMITTANCE_BANK_ACCOUNT_ID' );
1028 APP_EXCEPTION.raise_exception;
1029 END IF;
1030 -- Check for valid GL date
1031 --
1032 arp_util.validate_gl_date( l_batch_rec.gl_date,
1033 NULL, NULL );
1034
1035
1036 -- check Receipt Class ID is valid
1037 if (l_batch_rec.receipt_class_id is not null) then
1038 open c_valid_receipt_class;
1039 fetch c_valid_receipt_class into l_valid;
1040 if c_valid_receipt_class%NOTFOUND then
1041 close c_valid_receipt_class;
1042 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1043 fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
1044 fnd_message.set_token('PARAMETER', 'RECEIPT_CLASS_ID' );
1045 app_exception.raise_exception;
1046 end if;
1047 close c_valid_receipt_class;
1048 end if;
1049
1050 -- check Receipt Payment Method ID is valid
1051 if (l_batch_rec.receipt_method_id is not null) then
1052 open c_valid_receipt_method;
1053 fetch c_valid_receipt_method into l_valid;
1054 if c_valid_receipt_method%NOTFOUND then
1055 close c_valid_receipt_method;
1056 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1057 fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
1058 fnd_message.set_token('PARAMETER', 'RECEIPT_METHOD_ID' );
1059 app_exception.raise_exception;
1060 end if;
1061 close c_valid_receipt_method;
1062 end if;
1063
1064 -- check Bank Branch ID is valid
1065 if (l_batch_rec.remittance_bank_branch_id is not null) then
1066 open c_valid_bank_branch ;
1067 fetch c_valid_bank_branch into l_valid;
1068 if c_valid_bank_branch%NOTFOUND then
1069 close c_valid_bank_branch;
1070 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1071 fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
1072 fnd_message.set_token('PARAMETER', 'BANK_BRANCH_ID' );
1073 app_exception.raise_exception;
1074 end if;
1075 close c_valid_bank_branch;
1076 end if;
1077
1078 -- check Bank Account ID is valid
1079 if (l_batch_rec.remit_bank_acct_use_id is not null) then
1080 open c_valid_bank_account;
1081 fetch c_valid_bank_account into l_valid;
1082 if c_valid_bank_account%NOTFOUND then
1083 close c_valid_bank_account;
1084 fnd_message.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1085 fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
1086 fnd_message.set_token('PARAMETER', 'BANK_ACCOUNT_ID' );
1087 app_exception.raise_exception;
1088 end if;
1089 close c_valid_bank_account;
1090 end if;
1091
1092
1093 --
1094 -- Call Check Unique Media Reference procedure, if the Batch Type is
1095 -- not 'MANUAL' and media reference is not NULL
1096 --
1097 IF ( l_batch_rec.media_reference IS NOT NULL ) THEN
1098 arp_rw_batches_check_pkg.check_unique_media_ref( p_row_id,
1099 l_batch_rec.media_reference,
1100 NULL, NULL );
1101 END IF;
1102 --
1103 -- IF type is not manual, then set batch_source_id to 1 and
1104 -- get the batch source name to populate the form field
1105 --
1106 l_batch_rec.batch_source_id := 1;
1107 --
1108 -- automatic batch numbering is set to ON, so get the next
1109 -- batch name from LAST_BATCH_NUM from AR_BATCH_SOURCES
1110 --
1111 UPDATE ar_batch_sources
1112 SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
1113 WHERE batch_source_id = l_batch_rec.batch_source_id;
1114 --
1115 IF ( SQL%ROWCOUNT = 0 ) THEN
1116 FND_MESSAGE.set_name( 'AR', 'AR_UPDNA_LAST_BATCH_NO' );
1117 APP_EXCEPTION.raise_exception;
1118 END IF;
1119 --
1120 SELECT NVL( LAST_BATCH_NUM, 0 )
1121 INTO l_batch_rec.name
1122 FROM ar_batch_sources
1123 WHERE batch_source_id = l_batch_rec.batch_source_id;
1124 --
1125 p_name := l_batch_rec.name;
1126 p_batch_applied_status := l_batch_rec.batch_applied_status;
1127 --
1128 -- Call Check Unique Batch Name is unique for a given batch source id
1129 --
1130 arp_rw_batches_check_pkg.check_unique_batch_name( p_row_id,
1131 l_batch_rec.batch_source_id,
1132 l_batch_rec.name,
1133 NULL,
1134 NULL );
1135 --
1136 -- Call insert table handler
1137 --
1138 arp_cr_batches_pkg.insert_p( l_batch_rec,
1139 p_row_id,
1140 p_batch_id );
1141 --
1142 --
1143 IF PG_DEBUG in ('Y', 'C') THEN
1144 arp_util.debug( 'arp_rw_batches_pkg.insert_remit_batch()-' );
1145 END IF;
1146 --
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 IF PG_DEBUG in ('Y', 'C') THEN
1150 arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.insert_remit_batch' );
1151 END IF;
1152 RAISE;
1153 END insert_remit_batch;
1154 --
1155 /*===========================================================================+
1156 | PROCEDURE |
1157 | validate_args_insert_remit |
1158 | |
1159 | DESCRIPTION |
1160 | Validate arguments passed to insert_remit_remit procedure |
1161 | |
1162 | SCOPE - PRIVATE |
1163 | |
1164 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1165 | arp_util.debug - debug procedure |
1166 | |
1167 | ARGUMENTS : IN: |
1168 | p_type - Batch Type |
1169 | OUT: |
1170 | |
1171 | RETURNS : NONE |
1172 | |
1173 | NOTES - |
1174 | |
1175 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
1176 | |
1177 +===========================================================================*/
1178 PROCEDURE validate_args_insert_remit(
1179 p_row_id IN VARCHAR2,
1180 p_batch_id IN ar_batches.batch_id%TYPE,
1181 p_type IN ar_batches.type%TYPE,
1182 p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
1183 p_currency_code IN ar_batches.currency_code%TYPE,
1184 p_gl_date IN ar_batches.gl_date%TYPE,
1185 p_batch_date IN ar_batches.batch_date%TYPE,
1186 p_remit_method_code IN ar_batches.remit_method_code%TYPE,
1187 p_remittance_bank_branch_id IN
1188 ar_batches.remittance_bank_branch_id%TYPE,
1189 p_batch_applied_status IN ar_batches.batch_applied_status%TYPE ) IS
1190 l_field VARCHAR2(30);
1191 BEGIN
1192 IF PG_DEBUG in ('Y', 'C') THEN
1193 arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_remit()+' );
1194 END IF;
1195 --
1196 IF ( p_row_id IS NOT NULL ) THEN
1197 l_field := 'ROW_ID';
1198 ELSIF ( p_batch_id IS NOT NULL ) THEN
1199 l_field := 'BATCH_ID';
1200 ELSIF ( p_type IS NULL ) THEN
1201 l_field := 'TYPE';
1202 ELSIF ( p_set_of_books_id IS NULL ) THEN
1203 l_field := 'SET_OF_BOOKS_ID';
1204 ELSIF ( p_currency_code IS NULL ) THEN
1205 l_field := 'CURRENCY_CODE';
1206 ELSIF ( p_gl_date IS NULL ) THEN
1207 l_field := 'GL_DATE';
1208 ELSIF ( p_batch_date IS NULL ) THEN
1209 l_field := 'BATCH_DATE';
1210 ELSIF ( p_batch_applied_status IS NULL ) THEN
1211 l_field := 'BATCH_APPLIED_STATUS';
1212 ELSIF ( p_remit_method_code IS NULL ) THEN
1213 l_field := 'REMIT_METHOD_CODE';
1214 ELSIF ( p_remittance_bank_branch_id IS NULL ) THEN
1215 l_field := 'REMITTANCE_BANK_BRANCH_ID';
1216 ELSIF ( p_remit_method_code IS NULL ) THEN
1217 l_field := 'REMIT_METHOD_CODE';
1218 ELSIF ( p_remittance_bank_branch_id IS NULL ) THEN
1219 l_field := 'REMITTANCE_BANK_BRANCH_ID';
1220 ELSE
1221 l_field := NULL;
1222 END IF;
1223 --
1224 IF ( l_field IS NOT NULL ) THEN
1225 FND_MESSAGE.set_name( 'AR', 'AR_PROCEDURE_VALID_ARGS_FAIL' );
1226 FND_MESSAGE.set_token('PROCEDURE', 'VALIDATE_ARGS_INSERT_REMIT');
1227 FND_MESSAGE.set_token('PARAMETER', l_field );
1228 APP_EXCEPTION.raise_exception;
1229 END IF;
1230 --
1231 IF PG_DEBUG in ('Y', 'C') THEN
1232 arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_remit()-' );
1233 END IF;
1234 --
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 IF PG_DEBUG in ('Y', 'C') THEN
1238 arp_util.debug('validate_args_insert_remit: ' ||
1239 'EXCEPTION: arp_rw_batches_pkg.validate_args_insert_remit' );
1240 END IF;
1241 RAISE;
1242 END validate_args_insert_remit;
1243 --
1244 PROCEDURE delete_batch( p_batch_id IN ar_batches.batch_id%TYPE ) IS
1245 l_id NUMBER;
1246 BEGIN
1247 -- lock the records.
1248 SELECT batch_id
1249 INTO l_id
1250 FROM ar_batches
1251 WHERE batch_id = p_batch_id
1252 FOR UPDATE;
1253
1254 -- detach the transactions assoicated with the batch and delete the batch.
1255 UPDATE ar_payment_schedules
1256 SET selected_for_receipt_batch_id = NULL
1257 WHERE selected_for_receipt_batch_id = p_batch_id;
1258
1259 -- Call the table handler for ar_batches instead of doing delete here
1260 -- Bug: 2028370
1261 ARP_CR_BATCHES_PKG.delete_p(p_batch_id);
1262
1263 -- DELETE ar_batches
1264 -- WHERE batch_id = p_batch_id;
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 IF PG_DEBUG in ('Y', 'C') THEN
1269 arp_standard.debug('EXCEPTION: arp_rw_batches_pkg.delete_batch');
1270 END IF;
1271 RAISE;
1272 END delete_batch;
1273
1274
1275 PROCEDURE default_batch_source_pay_mthds(p_batch_source_name IN OUT NOCOPY ar_batch_sources.name%TYPE,
1276 p_batch_date IN ar_batch_sources.start_date_active%TYPE,
1277 p_batch_source_id IN OUT NOCOPY ar_batch_sources.batch_source_id%TYPE,
1278 p_batch_number OUT NOCOPY ar_batch_sources.auto_batch_numbering%TYPE,
1279 p_rec_class_id OUT NOCOPY ar_receipt_classes.receipt_class_id%TYPE,
1280 p_rec_class_name OUT NOCOPY ar_receipt_classes.name%TYPE,
1281 p_pay_method_id OUT NOCOPY ar_receipt_methods.receipt_method_id%TYPE,
1282 p_pay_method_name OUT NOCOPY ar_receipt_methods.name%TYPE,
1283 p_bank_name OUT NOCOPY ce_bank_branches_v.bank_name%TYPE,
1284 p_bank_account_num OUT NOCOPY ce_bank_accounts.bank_account_num%TYPE,
1285 p_bank_account_id OUT NOCOPY ce_bank_accounts.bank_account_id%TYPE,
1286 p_currency_code IN OUT NOCOPY ce_bank_accounts.currency_code%TYPE,
1287 p_bank_branch_name OUT NOCOPY ce_bank_branches_v.bank_branch_name%TYPE,
1288 p_bank_branch_id OUT NOCOPY ce_bank_accounts.bank_branch_id%TYPE,
1289 p_override_remit_flag OUT NOCOPY ar_receipt_method_accounts.override_remit_account_flag%TYPE,
1290 p_remit_flag OUT NOCOPY ar_receipt_classes.remit_flag%TYPE,
1291 p_creation_status OUT NOCOPY ar_receipt_classes.creation_status%TYPE,
1292 p_meaning OUT NOCOPY ar_lookups.meaning%TYPE) IS
1293
1294 l_batch_source_id ar_batch_sources.batch_source_id%TYPE;
1295 l_batch_number ar_batch_sources.auto_batch_numbering%TYPE;
1296 l_rec_class_id ar_receipt_classes.receipt_class_id%TYPE;
1297 l_rec_class_name ar_receipt_classes.name%TYPE;
1298 l_pay_method_id ar_receipt_methods.receipt_method_id%TYPE;
1299 l_pay_method_name ar_receipt_methods.name%TYPE;
1300 l_bank_name ce_bank_branches_v.bank_name%TYPE;
1301 l_bank_account_num ce_bank_accounts.bank_account_num%TYPE;
1302 l_bank_account_id ce_bank_accounts.bank_account_id%TYPE;
1303 l_currency_code ce_bank_accounts.currency_code%TYPE;
1304 l_bank_branch_name ce_bank_branches_v.bank_branch_name%TYPE;
1305 l_bank_branch_id ce_bank_accounts.bank_branch_id%TYPE;
1306 l_override_remit_flag ar_receipt_method_accounts.override_remit_account_flag%TYPE;
1307 l_remit_flag ar_receipt_classes.remit_flag%TYPE;
1308 l_creation_status ar_receipt_classes.creation_status%TYPE;
1309 l_meaning ar_lookups.meaning%TYPE;
1310 l_num NUMBER;
1311 --Bug fix 5647335 starts
1312 l_default_org_id NUMBER;
1313 l_defaulted_flag VARCHAR2(3);
1314 l_org_id NUMBER;
1315 l_count NUMBER;
1316 cursor c is
1317 SELECT bs.batch_source_id,
1318 bs.auto_batch_numbering,
1319 bs.default_receipt_class_id,
1320 bs.default_receipt_method_id,
1321 bs.default_remit_bank_account_id,
1322 bs.org_id
1323 FROM ar_batch_sources bs
1324 WHERE bs.name = p_batch_source_name
1325 AND bs.type <> 'AUTOMATIC'
1326 AND p_batch_date BETWEEN bs.start_date_active AND NVL(bs.end_date_active,p_batch_date);
1327 BEGIN
1328 l_rec_class_name:=NULL;
1329 l_pay_method_name:=NULL;
1330 l_bank_name:=NULL;
1331 l_bank_account_num:=NULL;
1332 l_bank_branch_name:=NULL;
1333 l_bank_branch_id:=NULL;
1334 l_creation_status:=NULL;
1335 l_override_remit_flag:=NULL;
1336 l_remit_flag:=NULL;
1337 l_org_id := NULL;
1338 l_default_org_id := fnd_profile.value('DEFAULT_ORG_ID');
1339 l_defaulted_flag := 'N';
1340 IF PG_DEBUG IN ('Y', 'C') THEN
1341 arp_debug.debug('arp_rw_batches_pkg.default_batch_source_pay_mthds()+');
1342 arp_debug.debug(' default_org_id: ' || l_default_org_id);
1343 arp_debug.debug(' p_batch_source_name: ' || p_batch_source_name);
1344 END IF;
1345 /* Fetch Data from Default Batch Source */
1346 BEGIN
1347 --Bug fix 5647335 starts
1348 FOR bsource in c LOOP
1349 IF(C%ROWCOUNT>1) THEN
1350 l_defaulted_flag := 'N';
1351 IF (l_default_org_id IS NULL) THEN
1352 p_batch_source_name := NULL;
1353 l_rec_class_id:=NULL;
1354 ELSE
1355 IF (l_org_id = l_default_org_id) THEN
1356 l_defaulted_flag := 'Y';
1357 EXIT;
1358 END IF;
1359 END IF;
1360 END IF;
1361 l_batch_source_id := bsource.batch_source_id;
1362 l_batch_number := bsource.auto_batch_numbering;
1363 l_rec_class_id := bsource.default_receipt_class_id;
1364 l_pay_method_id := bsource.default_receipt_method_id;
1365 l_bank_account_id := bsource.default_remit_bank_account_id;
1366 l_org_id := bsource.org_id;
1367 l_defaulted_flag := 'Y';
1368 END LOOP;
1369 IF (l_defaulted_flag <> 'Y') THEN
1370 p_batch_source_name:=NULL;
1371 l_rec_class_id:=NULL;
1372 END IF;
1373 IF PG_DEBUG IN ('Y', 'C') THEN
1374 arp_debug.debug(' l_batch_source_id: ' || l_batch_source_id);
1375 arp_debug.debug(' l_batch_number: ' || l_batch_number);
1376 arp_debug.debug(' l_rec_class_id: ' || l_rec_class_id);
1377 arp_debug.debug(' l_bank_account_id: ' || l_bank_account_id);
1378 arp_debug.debug(' l_pay_method_id: ' || l_pay_method_id);
1379 arp_debug.debug(' l_org_id: ' || l_org_id);
1380 END IF;
1381 --Bug fix 5647335 ends
1382 --Commenting out sql query as part of bug fix 5647335
1383 /*SELECT bs.batch_source_id,
1384 bs.auto_batch_numbering,
1385 bs.default_receipt_class_id,
1386 bs.default_receipt_method_id,
1387 bs.default_remit_bank_account_id
1388 INTO l_batch_source_id,
1389 l_batch_number,
1390 l_rec_class_id,
1391 l_pay_method_id,
1392 l_bank_account_id
1393 FROM ar_batch_sources bs
1394 WHERE bs.name = p_batch_source_name
1395 AND bs.type <> 'AUTOMATIC'
1396 AND p_batch_date BETWEEN bs.start_date_active AND NVL(bs.end_date_active,p_batch_date);*/
1397 EXCEPTION
1398 /*WHEN NO_DATA_FOUND THEN
1399 p_batch_source_name:=NULL;
1400 l_rec_class_id:=NULL;
1401 WHEN TOO_MANY_ROWS THEN --Added for Bug:5477927
1402 p_batch_source_name := NULL;
1403 l_rec_class_id := NULL;*/
1404 WHEN OTHERS THEN
1405 p_batch_source_name:=NULL;
1406 l_rec_class_id:=NULL;
1407 RAISE;
1408 END;
1409
1410 /* We can attach PM , Bank Account and RC to batch source
1411 or RC only or RC AND PM only
1412 1. If Bank Account is defaulted then first validate it
1413 2. IF PM is defaulted then validate as such atleast 1 bank account
1414 should exists for that PM.
1415 3. Then check for RC to be defaulted.
1416 4. else do not default RC. */
1417
1418 /* Validating default Bank Account id */
1419 IF l_bank_account_id IS NOT NULL THEN
1420 BEGIN
1421 SELECT
1422 bb.bank_name,
1423 cba.bank_account_num,
1424 cba.currency_code,
1425 bb.bank_branch_name,
1426 bb.branch_party_id,
1427 rma.override_remit_account_flag
1428 INTO
1429 l_bank_name,
1430 l_bank_account_num,
1431 l_currency_code,
1432 l_bank_branch_name,
1433 l_bank_branch_id,
1434 l_override_remit_flag
1435 FROM ce_bank_accounts cba,
1436 ce_bank_branches_v bb,
1437 ar_receipt_method_accounts rma,
1438 ce_bank_acct_uses_ou_v ba
1439 WHERE cba.bank_account_id = l_bank_account_id
1440 AND rma.receipt_method_id = l_pay_method_id
1441 AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
1442 AND cba.bank_account_id = ba.bank_account_id
1443 AND cba.bank_branch_id = bb.branch_party_id
1444 AND p_batch_date between rma.start_date and NVL(rma.end_date,p_batch_date)
1445 AND to_Date(p_batch_date,'DD/MM/RRRR') <
1446 NVL(cba.end_date,to_date(p_batch_date,'DD/MM/RRRR')+1)
1447 AND p_batch_date <=NVL(bb.end_date,p_batch_date)
1448 AND ROWNUM <=1;
1449 EXCEPTION
1450 WHEN NO_DATA_FOUND THEN
1451 l_bank_account_id:=NULL;
1452 l_bank_name:=NULL;
1453 l_bank_account_num:=NULL;
1454 l_currency_code:=NULL;
1455 l_bank_branch_name:=NULL;
1456 l_bank_branch_id:=NULL;
1457 l_override_remit_flag:=NULL;
1458 END;
1459 END IF;
1460 /* Validate PM */
1461 IF l_pay_method_id IS NOT NULL THEN
1462 BEGIN
1463 SELECT
1464 rc.name,
1465 rm.name,
1466 rc.creation_status,
1467 l.meaning,
1468 rc.remit_flag
1469 INTO
1470 l_rec_class_name,
1471 l_pay_method_name,
1472 l_creation_status,
1473 l_meaning,
1474 l_remit_flag
1475 FROM ar_receipt_classes rc,
1476 ar_receipt_methods rm,
1477 ar_lookups l
1478 WHERE rc.receipt_class_id=l_rec_class_id
1479 AND rc.receipt_class_id = rm.receipt_class_id
1480 AND rm.receipt_method_id = l_pay_method_id
1481 AND p_batch_date between rm.start_date and NVL(rm.end_date,p_batch_date)
1482 AND rc.creation_status = l.lookup_code(+)
1483 AND l.lookup_type(+) = 'RECEIPT_CREATION_STATUS'
1484 AND EXISTS
1485 (SELECT
1486 rma.receipt_method_id
1487 FROM ce_bank_accounts cba,
1488 ce_bank_branches_v bb,
1489 ar_receipt_method_accounts rma,
1490 ce_bank_acct_uses_ou_v ba
1491 WHERE rma.receipt_method_id = l_pay_method_id
1492 AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
1493 AND cba.bank_account_id = ba.bank_account_id
1494 AND (cba.currency_code=NVL(l_currency_code,p_currency_code)
1495 OR cba.receipt_multi_currency_flag='Y')
1496 AND bb.branch_party_id = cba.bank_branch_id
1497 AND p_batch_date between rma.start_date and NVL(rma.end_date,p_batch_date)
1498 AND to_date(p_batch_date,'DD/MM/RRRR') <
1499 NVL(cba.end_date,to_date(p_batch_date,'DD/MM/RRRR')+1)
1500 AND p_batch_date <=NVL(bb.end_date,p_batch_date))
1501 AND ROWNUM <=1;
1502 EXCEPTION
1503 WHEN NO_DATA_FOUND THEN
1504 l_pay_method_name:=NULL;
1505 l_pay_method_id:=NULL;
1506 l_bank_account_id:=NULL;
1507 l_bank_name:=NULL;
1508 l_bank_account_num:=NULL;
1509 l_currency_code:=NULL;
1510 l_bank_branch_name:=NULL;
1511 l_bank_branch_id:=NULL;
1512 l_override_remit_flag:=NULL;
1513 l_rec_class_name:=NULL;
1514 l_creation_status:=NULL;
1515 l_meaning:=NULL;
1516 l_remit_flag:=NULL;
1517 END;
1518 END IF;
1519 /* Validate RC such that 1 PM exists atleast */
1520 IF l_rec_class_id is NOT NULL AND l_rec_class_name is NULL THEN
1521 BEGIN
1522 SELECT
1523 rc.name,
1524 rc.creation_status,
1525 l.meaning,
1526 rc.remit_flag
1527 INTO
1528 l_rec_class_name,
1529 l_creation_status,
1530 l_meaning,
1531 l_remit_flag
1532 FROM ar_receipt_classes rc,
1533 ar_lookups l
1534 WHERE rc.receipt_class_id=l_rec_class_id
1535 AND rc.creation_status = l.lookup_code(+)
1536 AND l.lookup_type(+) = 'RECEIPT_CREATION_STATUS'
1537 AND EXISTS
1538 (SELECT
1539 rm.receipt_class_id
1540 FROM ar_receipt_methods rm,
1541 ce_bank_accounts cba,
1542 ce_bank_branches_v bb,
1543 ar_receipt_method_accounts rma,
1544 ce_bank_acct_uses ba
1545 WHERE rm.receipt_class_id = l_rec_class_id
1546 AND rm.receipt_method_id = rma.receipt_method_id
1547 AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
1548 AND cba.bank_account_id = ba.bank_account_id
1549 AND (cba.currency_code=NVL(l_currency_code,p_currency_code) OR
1550 cba.receipt_multi_currency_flag='Y')
1551 AND bb.branch_party_id = cba.bank_branch_id
1552 AND p_batch_date between rm.start_date and
1553 NVL(rm.end_date,p_batch_date)
1554 AND p_batch_date between
1555 rma.start_date and NVL(rma.end_date,p_batch_date)
1556 AND to_Date(p_batch_date,'DD/MM/RRRR') <
1557 NVL(cba.end_date,to_date(p_batch_date,'DD/MM/RRRR')+1)
1558 AND p_batch_date <=NVL(bb.end_date,p_batch_date))
1559 AND ROWNUM <=1;
1560
1561 EXCEPTION
1562 WHEN NO_DATA_FOUND THEN
1563 l_pay_method_name:=NULL;
1564 l_pay_method_id:=NULL;
1565 l_bank_account_id:=NULL;
1566 l_bank_name:=NULL;
1567 l_bank_account_num:=NULL;
1568 l_currency_code:=NULL;
1569 l_bank_branch_name:=NULL;
1570 l_bank_branch_id:=NULL;
1571 l_override_remit_flag:=NULL;
1572 l_rec_class_name:=NULL;
1573 l_creation_status:=NULL;
1574 l_meaning:=NULL;
1575 l_remit_flag:=NULL;
1576 END;
1577 END IF;
1578
1579 /* Assign the values fetched from Various select stmts to parameter variables */
1580
1581 IF p_batch_source_name IS NOT NULL THEN
1582 p_batch_number:=l_batch_number;
1583 p_batch_source_id:=l_batch_source_id;
1584 END IF;
1585 IF l_rec_class_name IS NOT NULL AND l_rec_class_id is NOT NULL THEN
1586 p_rec_class_id:=l_rec_class_id;
1587 p_rec_class_name:=l_rec_class_name;
1588 p_remit_flag :=l_remit_flag;
1589 p_creation_status:=l_creation_status;
1590 p_meaning:=l_meaning;
1591 END IF;
1592 IF l_pay_method_name IS NOT NULL and l_pay_method_id IS NOT NULL THEN
1593 p_pay_method_id:=l_pay_method_id;
1594 p_pay_method_name:=l_pay_method_name;
1595 END IF;
1596 IF l_bank_name is NOT NULL AND l_bank_account_id IS NOT NULL THEN
1597 p_bank_name:=l_bank_name;
1598 p_bank_account_num :=l_bank_account_num;
1599 p_bank_account_id :=l_bank_account_id;
1600 IF l_currency_code is NOT NULL THEN
1601 p_currency_code :=l_currency_code;
1602 END IF;
1603 p_bank_branch_name:=l_bank_branch_name;
1604 p_bank_branch_id :=l_bank_branch_id;
1605 p_override_remit_flag :=l_override_remit_flag;
1606 END IF;
1607 IF PG_DEBUG IN ('Y', 'C') THEN
1608 arp_debug.debug('arp_rw_batches_pkg.default_batch_source_pay_mthds()-');
1609 END IF;
1610
1611 END default_batch_source_pay_mthds;
1612
1613
1614 /**The lock is released at the end of current session or explicit call out to
1615 release_lock function
1616 */
1617 FUNCTION request_lock(p_batch_id NUMBER,
1618 x_message OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
1619 PRAGMA AUTONOMOUS_TRANSACTION;
1620
1621 l_lock_name VARCHAR2(50);
1622 l_lock_handle VARCHAR2(256);
1623 v_result NUMBER;
1624
1625 BEGIN
1626 IF PG_DEBUG IN ('Y', 'C') THEN
1627 arp_debug.debug('arp_rw_batches_pkg.request_lock()+');
1628 arp_debug.debug('p_batch_id '||p_batch_id);
1629 END IF;
1630
1631 l_lock_name := 'AR_RECEIPTS_BATCH_'||p_batch_id;
1632
1633 DBMS_LOCK.allocate_unique( l_lock_name,
1634 l_lock_handle);
1635
1636 v_result := dbms_lock.request( l_lock_handle,
1637 dbms_lock.x_mode,
1638 0);
1639 CASE v_result
1640 WHEN 0 THEN x_message := 'success';
1641 WHEN 1 THEN x_message := 'timeout';
1642 WHEN 2 THEN x_message := 'deadlock';
1643 WHEN 3 THEN x_message := 'parameter error';
1644 WHEN 4 THEN x_message := 'already own lock specified by ''id'' or ''lockhandle''';
1645 WHEN 5 THEN x_message := 'illegal lockhandle';
1646 END CASE;
1647
1648 IF PG_DEBUG IN ('Y', 'C') THEN
1649 arp_debug.debug('v_result '||v_result);
1650 arp_debug.debug('arp_rw_batches_pkg.request_lock()-');
1651 END IF;
1652
1653 IF v_result IN (0,4) THEN
1654 RETURN TRUE;
1655 END IF;
1656
1657 RETURN FALSE;
1658
1659 END request_lock;
1660
1661
1662
1663 FUNCTION release_lock(p_batch_id NUMBER,
1664 x_message OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
1665 PRAGMA AUTONOMOUS_TRANSACTION;
1666
1667 l_lock_name VARCHAR2(50);
1668 l_lock_handle VARCHAR2(256);
1669 v_result NUMBER;
1670 BEGIN
1671 IF PG_DEBUG IN ('Y', 'C') THEN
1672 arp_debug.debug('arp_rw_batches_pkg.release_lock()+');
1673 arp_debug.debug('p_batch_id '||p_batch_id);
1674 END IF;
1675
1676 l_lock_name := 'AR_RECEIPTS_BATCH_'||p_batch_id;
1677
1678 DBMS_LOCK.allocate_unique( l_lock_name,
1679 l_lock_handle);
1680
1681 v_result := dbms_lock.release( l_lock_handle );
1682
1683 CASE v_result
1684 WHEN 0 THEN x_message := 'success';
1685 WHEN 3 THEN x_message := 'parameter error';
1686 WHEN 4 THEN x_message := 'don''t own lock specified by ''id'' or ''lockhandle''';
1687 WHEN 5 THEN x_message := 'illegal lockhandle';
1688 END CASE;
1689
1690 IF PG_DEBUG IN ('Y', 'C') THEN
1691 arp_debug.debug('v_result '||v_result);
1692 arp_debug.debug('arp_rw_batches_pkg.release_lock()-');
1693 END IF;
1694
1695 IF v_result = 0 THEN
1696 RETURN TRUE;
1697 END IF;
1698
1699 RETURN FALSE;
1700
1701 END release_lock;
1702
1703
1704
1705 END ARP_RW_BATCHES_PKG;
1706