[Home] [Help]
PACKAGE BODY: APPS.ARP_RW_BATCHES_CHECK_PKG
Source
1 PACKAGE BODY ARP_RW_BATCHES_CHECK_PKG AS
2 /* $Header: ARERBA1B.pls 120.18 2010/12/29 15:46:11 qioliu ship $ */
3 --
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE validate_args_cu_batch_name(
7 p_batch_source_id IN ar_batch_sources.batch_source_id%TYPE,
8 p_batch_name IN ar_batches.name%TYPE );
9 --
10 PROCEDURE validate_args_cu_batch_name(
11 p_batch_source_name IN ar_batch_sources.name%TYPE,
12 p_batch_name IN ar_batches.name%TYPE );
13 --
14 PROCEDURE validate_args_cu_media_ref(
15 p_media_ref IN ar_batches.media_reference%TYPE );
16 --
17 PROCEDURE validate_args_update_manual(
18 p_row_id IN VARCHAR2,
19 p_batch_id IN ar_batches.batch_id%TYPE,
20 p_batch_date IN ar_batches.batch_date%TYPE );
21 --
22 PROCEDURE validate_args_update_remit(
23 p_row_id IN VARCHAR2,
24 p_batch_id IN ar_batches.batch_id%TYPE,
25 p_batch_date IN ar_batches.batch_date%TYPE );
26 --
27 PROCEDURE validate_args_update_auto(
28 p_row_id IN VARCHAR2,
29 p_batch_id IN ar_batches.batch_id%TYPE,
30 p_batch_date IN ar_batches.batch_date%TYPE );
31 --
32 /*===========================================================================+
33 | PROCEDURE |
34 | update_manual_batch - Updates a row in AR_BATCHES after checking for|
35 | uniqueness |
36 | |
37 | DESCRIPTION |
38 | This function updates a row in AR_BATCHES table after checking for |
39 | uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE |
40 | |
41 | SCOPE - PUBLIC |
42 | |
43 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
44 | arp_util.debug - debug procedure |
45 | |
46 | ARGUMENTS : IN: |
47 | p_row_id - Row ID |
48 | p_batch_source_id - Batch Source Id |
49 | p_batch_name - Batch Name |
50 | p_module_name - Module that called this procedure |
51 | p_module_version - Version of the module that called this |
52 | procedure |
53 | OUT: |
54 | |
55 | RETURNS : NONE |
56 | |
57 | NOTES - This procedure calls the check_unique_batch_name and |
58 | check_unique_media_ref and arp_rw_icr_pkg.update_batch procedures |
59 | |
60 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
61 | 19-MAR-96 Simon Leung Call update_batch to update batch status. |
62 | 27-NOV-96 Karen Lawrance Bug fix #423518. Added GL Date, Deposit |
63 | Date and Batch Source to update list. |
64 +===========================================================================*/
65 PROCEDURE update_manual_batch(
66 p_row_id IN VARCHAR2,
67 p_batch_id IN ar_batches.batch_id%TYPE,
68 p_batch_source_id IN ar_batches.batch_source_id%TYPE,
69 p_batch_date IN ar_batches.batch_date%TYPE,
70 p_gl_date IN ar_batches.gl_date%TYPE,
71 p_deposit_date IN ar_batches.batch_date%TYPE,
72 p_currency_code IN ar_batches.currency_code%TYPE,
73 p_comments IN ar_batches.comments%TYPE,
74 p_control_amount IN ar_batches.control_amount%TYPE,
75 p_control_count IN ar_batches.control_count%TYPE,
76 p_exchange_date IN ar_batches.exchange_date%TYPE,
77 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
78 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
79 p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
80 p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
81 p_remittance_bank_account_id
82 IN ar_batches.remit_bank_acct_use_id%type,
83 p_remittance_bank_branch_id
84 IN ar_batches.remittance_bank_branch_id%TYPE,
85 p_attribute_category IN ar_batches.attribute_category%TYPE,
86 p_attribute1 IN ar_batches.attribute1%TYPE,
87 p_attribute2 IN ar_batches.attribute2%TYPE,
88 p_attribute3 IN ar_batches.attribute3%TYPE,
89 p_attribute4 IN ar_batches.attribute4%TYPE,
90 p_attribute5 IN ar_batches.attribute5%TYPE,
91 p_attribute6 IN ar_batches.attribute6%TYPE,
92 p_attribute7 IN ar_batches.attribute7%TYPE,
93 p_attribute8 IN ar_batches.attribute8%TYPE,
94 p_attribute9 IN ar_batches.attribute9%TYPE,
95 p_attribute10 IN ar_batches.attribute10%TYPE,
96 p_attribute11 IN ar_batches.attribute11%TYPE,
97 p_attribute12 IN ar_batches.attribute12%TYPE,
98 p_attribute13 IN ar_batches.attribute13%TYPE,
99 p_attribute14 IN ar_batches.attribute14%TYPE,
100 p_attribute15 IN ar_batches.attribute15%TYPE,
101 p_module_name IN VARCHAR2,
102 p_module_version IN VARCHAR2 ) IS
103 --
104 l_batch_rec ar_batches%ROWTYPE;
105 BEGIN
106 IF PG_DEBUG in ('Y', 'C') THEN
107 arp_util.debug( 'arp_rw_batches_pkg.update_manual_batch()+' );
108 arp_util.debug( 'Row Id : '||p_row_id );
109 arp_util.debug( 'Batch ID : '||p_batch_id );
110 arp_util.debug( 'Batch Date : '||TO_CHAR( p_batch_date ) );
111 END IF;
112 --
113 -- Set local batch record fields to DUMMY
114 --
115 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
116 --
117 -- Populate the local batch record, so that it can be passed to update
118 -- table handler
119 l_batch_rec.batch_id := p_batch_id;
120 l_batch_rec.batch_source_id := p_batch_source_id;
121 l_batch_rec.batch_date := p_batch_date;
122 l_batch_rec.gl_date := p_gl_date;
123 l_batch_rec.deposit_date := p_deposit_date;
124 l_batch_rec.currency_code := p_currency_code;
125 l_batch_rec.comments := p_comments;
126 l_batch_rec.control_amount := p_control_amount;
127 l_batch_rec.control_count := p_control_count;
128 l_batch_rec.exchange_date := p_exchange_date;
129 l_batch_rec.exchange_rate := p_exchange_rate;
130 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
131 l_batch_rec.receipt_class_id := p_receipt_class_id;
132 l_batch_rec.receipt_method_id := p_receipt_method_id;
133 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
134 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
135 l_batch_rec.attribute_category := p_attribute_category;
136 l_batch_rec.attribute1 := p_attribute1;
137 l_batch_rec.attribute2 := p_attribute2;
138 l_batch_rec.attribute3 := p_attribute3;
139 l_batch_rec.attribute4 := p_attribute4;
140 l_batch_rec.attribute5 := p_attribute5;
141 l_batch_rec.attribute6 := p_attribute6;
142 l_batch_rec.attribute7 := p_attribute7;
143 l_batch_rec.attribute8 := p_attribute8;
144 l_batch_rec.attribute9 := p_attribute9;
145 l_batch_rec.attribute10 := p_attribute10;
146 l_batch_rec.attribute11 := p_attribute11;
147 l_batch_rec.attribute12 := p_attribute12;
148 l_batch_rec.attribute13 := p_attribute13;
149 l_batch_rec.attribute14 := p_attribute14;
150 l_batch_rec.attribute15 := p_attribute15;
151 --
152 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
153 validate_args_update_manual( p_row_id, l_batch_rec.batch_id,
154 l_batch_rec.batch_date );
155 END IF;
156 --
157 -- call update table handler
158 --
159 arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
160 --
161 -- update the batch status
162 --
163 arp_rw_batches_check_pkg.update_batch_status( l_batch_rec.batch_id );
164 --
165 IF PG_DEBUG in ('Y', 'C') THEN
166 arp_util.debug( 'arp_rw_batches_pkg.update_manual_batch()-' );
167 END IF;
168 --
169 EXCEPTION
170 WHEN OTHERS THEN
171 IF PG_DEBUG in ('Y', 'C') THEN
172 arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.update_manual_batch' );
173 END IF;
174 RAISE;
175 END update_manual_batch;
176 --
177 /*===========================================================================+
178 | PROCEDURE |
179 | update_remit_batch |
180 | |
181 | DESCRIPTION |
182 | This function updates a row in AR_BATCHES table after checking for |
183 | uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE. Used for |
184 | Remittance Batches only. |
185 | |
186 | SCOPE - PUBLIC |
187 | |
188 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
189 | arp_util.debug - debug procedure |
190 | |
191 | ARGUMENTS : IN: |
192 | p_row_id - Row ID |
193 | p_batch_source_id - Batch Source Id |
194 | p_batch_name - Batch Name |
195 | p_module_name - Module that called this procedure |
196 | p_module_version - Version of the module that called |
197 | this procedure |
198 | OUT: |
199 | |
200 | RETURNS : NONE |
201 | |
202 | NOTES - This procedure calls the check_unique_batch_name and check_ |
203 | unique_media_ref and arp_rw_icr_pkg.update_batch procedures. |
204 | |
205 | MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
206 +===========================================================================*/
207 PROCEDURE update_remit_batch(
208 p_row_id IN VARCHAR2,
209 p_batch_id IN ar_batches.batch_id%TYPE,
210 p_batch_source_id IN ar_batches.batch_source_id%TYPE,
211 p_batch_date IN ar_batches.batch_date%TYPE,
212 p_gl_date IN ar_batches.gl_date%TYPE,
213 p_deposit_date IN ar_batches.batch_date%TYPE,
214 p_currency_code IN ar_batches.currency_code%TYPE,
215 p_comments IN ar_batches.comments%TYPE,
216 p_control_amount IN ar_batches.control_amount%TYPE,
217 p_control_count IN ar_batches.control_count%TYPE,
218 p_exchange_date IN ar_batches.exchange_date%TYPE,
219 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
220 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
221 p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
222 p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
223 p_remittance_bank_account_id
224 IN ar_batches.remit_bank_acct_use_id%type,
225 p_remittance_bank_branch_id
226 IN ar_batches.remittance_bank_branch_id%TYPE,
227 p_media_reference IN ar_batches.media_reference%TYPE,
228 p_bank_deposit_number IN ar_batches.bank_deposit_number%TYPE,
229 p_request_id IN ar_batches.request_id%TYPE,
230 p_operation_request_id IN ar_batches.operation_request_id%TYPE,
231 p_attribute_category IN ar_batches.attribute_category%TYPE,
232 p_attribute1 IN ar_batches.attribute1%TYPE,
233 p_attribute2 IN ar_batches.attribute2%TYPE,
234 p_attribute3 IN ar_batches.attribute3%TYPE,
235 p_attribute4 IN ar_batches.attribute4%TYPE,
236 p_attribute5 IN ar_batches.attribute5%TYPE,
237 p_attribute6 IN ar_batches.attribute6%TYPE,
238 p_attribute7 IN ar_batches.attribute7%TYPE,
239 p_attribute8 IN ar_batches.attribute8%TYPE,
240 p_attribute9 IN ar_batches.attribute9%TYPE,
241 p_attribute10 IN ar_batches.attribute10%TYPE,
242 p_attribute11 IN ar_batches.attribute11%TYPE,
243 p_attribute12 IN ar_batches.attribute12%TYPE,
244 p_attribute13 IN ar_batches.attribute13%TYPE,
245 p_attribute14 IN ar_batches.attribute14%TYPE,
246 p_attribute15 IN ar_batches.attribute15%TYPE,
247 p_module_name IN VARCHAR2,
248 p_module_version IN VARCHAR2 ) IS
249
250 l_batch_rec ar_batches%ROWTYPE;
251 BEGIN
252 IF PG_DEBUG in ('Y', 'C') THEN
253 arp_util.debug( 'arp_rw_batches_pkg.update_remit_batch()+' );
254 arp_util.debug( 'Row Id : '||p_row_id );
255 arp_util.debug( 'Batch ID : '||p_batch_id );
256 arp_util.debug( 'Batch Date : '||TO_CHAR( p_batch_date ) );
257 END IF;
258
259 -- Set local batch record fields to DUMMY. This clears out NOCOPY any unwanted
260 -- values from a previous update.
261
262 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
263
264
265 -- Populate the local batch record, so that it can be passed to update
266 -- table handler. Local variables are populated with the values passed
267 -- in through the parameters.
268
269 l_batch_rec.batch_id := p_batch_id;
270 l_batch_rec.batch_source_id := p_batch_source_id;
271 l_batch_rec.batch_date := p_batch_date;
272 l_batch_rec.gl_date := p_gl_date;
273 l_batch_rec.deposit_date := p_deposit_date;
274 l_batch_rec.currency_code := p_currency_code;
275 l_batch_rec.comments := p_comments;
276 l_batch_rec.control_amount := p_control_amount;
277 l_batch_rec.control_count := p_control_count;
278 l_batch_rec.exchange_date := p_exchange_date;
279 l_batch_rec.exchange_rate := p_exchange_rate;
280 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
281 l_batch_rec.receipt_class_id := p_receipt_class_id;
282 l_batch_rec.receipt_method_id := p_receipt_method_id;
283 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
284 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
285 l_batch_rec.media_reference := p_media_reference;
286 l_batch_rec.bank_deposit_number := p_bank_deposit_number;
287 l_batch_rec.request_id := p_request_id;
288 l_batch_rec.operation_request_id := p_operation_request_id;
289 l_batch_rec.attribute_category := p_attribute_category;
290 l_batch_rec.attribute1 := p_attribute1;
291 l_batch_rec.attribute2 := p_attribute2;
292 l_batch_rec.attribute3 := p_attribute3;
293 l_batch_rec.attribute4 := p_attribute4;
294 l_batch_rec.attribute5 := p_attribute5;
295 l_batch_rec.attribute6 := p_attribute6;
296 l_batch_rec.attribute7 := p_attribute7;
297 l_batch_rec.attribute8 := p_attribute8;
298 l_batch_rec.attribute9 := p_attribute9;
299 l_batch_rec.attribute10 := p_attribute10;
300 l_batch_rec.attribute11 := p_attribute11;
301 l_batch_rec.attribute12 := p_attribute12;
302 l_batch_rec.attribute13 := p_attribute13;
303 l_batch_rec.attribute14 := p_attribute14;
304 l_batch_rec.attribute15 := p_attribute15;
305
306 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
307 validate_args_update_remit( p_row_id, l_batch_rec.batch_id,
308 l_batch_rec.batch_date );
309 END IF;
310
311
312 -- Call update table handler to update the record in AR_BATCHES.
313
314 arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
315
316
317 IF PG_DEBUG in ('Y', 'C') THEN
318 arp_util.debug( 'arp_rw_batches_pkg.update_remit_batch()-' );
319 END IF;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 IF PG_DEBUG in ('Y', 'C') THEN
324 arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.update_remit_batch' );
325 END IF;
326 RAISE;
327 END update_remit_batch;
328 ---
329
330 /*===========================================================================+
331 | PROCEDURE |
332 | update_auto_batch |
333 | |
334 | DESCRIPTION |
335 | This function updates a row in AR_BATCHES table after checking for |
336 | uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE. Used for |
337 | Automatic Batches only. |
338 | |
339 | SCOPE - PUBLIC |
340 | |
341 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
342 | arp_util.debug - debug procedure |
343 | |
344 | ARGUMENTS : IN: |
345 | p_row_id - Row ID |
346 | p_batch_source_id - Batch Source Id |
347 | p_batch_name - Batch Name |
348 | p_module_name - Module that called this procedure |
349 | p_module_version - Version of the module that called |
350 | this procedure |
351 | OUT: |
352 | |
353 | RETURNS : NONE |
354 | |
355 | NOTES - This procedure calls the check_unique_batch_name and check_ |
356 | unique_media_ref and arp_rw_icr_pkg.update_batch procedures. |
357 | |
358 | MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
359 +===========================================================================*/
360 PROCEDURE update_auto_batch(
361 p_row_id IN VARCHAR2,
362 p_batch_id IN ar_batches.batch_id%TYPE,
363 p_batch_source_id IN ar_batches.batch_source_id%TYPE,
364 p_batch_date IN ar_batches.batch_date%TYPE,
365 p_gl_date IN ar_batches.gl_date%TYPE,
366 p_deposit_date IN ar_batches.batch_date%TYPE,
367 p_currency_code IN ar_batches.currency_code%TYPE,
368 p_comments IN ar_batches.comments%TYPE,
369 p_control_amount IN ar_batches.control_amount%TYPE,
370 p_control_count IN ar_batches.control_count%TYPE,
371 p_exchange_date IN ar_batches.exchange_date%TYPE,
372 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
373 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
374 p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
375 p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
376 p_remittance_bank_account_id
377 IN ar_batches.remit_bank_acct_use_id%type,
378 p_remittance_bank_branch_id
379 IN ar_batches.remittance_bank_branch_id%TYPE,
380 p_media_reference IN ar_batches.media_reference%TYPE,
381 p_bank_deposit_number IN ar_batches.bank_deposit_number%TYPE,
382 p_request_id IN ar_batches.request_id%TYPE,
383 p_operation_request_id IN ar_batches.operation_request_id%TYPE,
384 p_attribute_category IN ar_batches.attribute_category%TYPE,
385 p_attribute1 IN ar_batches.attribute1%TYPE,
386 p_attribute2 IN ar_batches.attribute2%TYPE,
387 p_attribute3 IN ar_batches.attribute3%TYPE,
388 p_attribute4 IN ar_batches.attribute4%TYPE,
389 p_attribute5 IN ar_batches.attribute5%TYPE,
390 p_attribute6 IN ar_batches.attribute6%TYPE,
391 p_attribute7 IN ar_batches.attribute7%TYPE,
392 p_attribute8 IN ar_batches.attribute8%TYPE,
393 p_attribute9 IN ar_batches.attribute9%TYPE,
394 p_attribute10 IN ar_batches.attribute10%TYPE,
395 p_attribute11 IN ar_batches.attribute11%TYPE,
396 p_attribute12 IN ar_batches.attribute12%TYPE,
397 p_attribute13 IN ar_batches.attribute13%TYPE,
398 p_attribute14 IN ar_batches.attribute14%TYPE,
399 p_attribute15 IN ar_batches.attribute15%TYPE,
400 p_module_name IN VARCHAR2,
401 p_module_version IN VARCHAR2 ) IS
402
403 l_batch_rec ar_batches%ROWTYPE;
404 BEGIN
405 IF PG_DEBUG in ('Y', 'C') THEN
406 arp_util.debug( 'arp_rw_batches_pkg.update_auto_batch()+' );
407 arp_util.debug( 'Row Id : '||p_row_id );
408 arp_util.debug( 'Batch ID : '||p_batch_id );
409 arp_util.debug( 'Batch Date : '||TO_CHAR( p_batch_date ) );
410 END IF;
411
412 -- Set local batch record fields to DUMMY. This clears out NOCOPY any unwanted
413 -- values from a previous update.
414
415 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
416
417
418 -- Populate the local batch record, so that it can be passed to update
419 -- table handler. Local variables are populated with the values passed
420 -- in through the parameters.
421
422 l_batch_rec.batch_id := p_batch_id;
423 l_batch_rec.batch_source_id := p_batch_source_id;
424 l_batch_rec.batch_date := p_batch_date;
425 l_batch_rec.gl_date := p_gl_date;
426 l_batch_rec.deposit_date := p_deposit_date;
427 l_batch_rec.currency_code := p_currency_code;
428 l_batch_rec.comments := p_comments;
429 l_batch_rec.control_amount := p_control_amount;
430 l_batch_rec.control_count := p_control_count;
431 l_batch_rec.exchange_date := p_exchange_date;
432 l_batch_rec.exchange_rate := p_exchange_rate;
433 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
434 l_batch_rec.receipt_class_id := p_receipt_class_id;
435 l_batch_rec.receipt_method_id := p_receipt_method_id;
436 l_batch_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
437 l_batch_rec.remittance_bank_branch_id := p_remittance_bank_branch_id;
438 l_batch_rec.media_reference := p_media_reference;
439 l_batch_rec.bank_deposit_number := p_bank_deposit_number;
440 l_batch_rec.request_id := p_request_id;
441 l_batch_rec.operation_request_id := p_operation_request_id;
442 l_batch_rec.attribute_category := p_attribute_category;
443 l_batch_rec.attribute1 := p_attribute1;
444 l_batch_rec.attribute2 := p_attribute2;
445 l_batch_rec.attribute3 := p_attribute3;
446 l_batch_rec.attribute4 := p_attribute4;
447 l_batch_rec.attribute5 := p_attribute5;
448 l_batch_rec.attribute6 := p_attribute6;
449 l_batch_rec.attribute7 := p_attribute7;
450 l_batch_rec.attribute8 := p_attribute8;
451 l_batch_rec.attribute9 := p_attribute9;
452 l_batch_rec.attribute10 := p_attribute10;
453 l_batch_rec.attribute11 := p_attribute11;
454 l_batch_rec.attribute12 := p_attribute12;
455 l_batch_rec.attribute13 := p_attribute13;
456 l_batch_rec.attribute14 := p_attribute14;
457 l_batch_rec.attribute15 := p_attribute15;
458
459 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
460 validate_args_update_auto( p_row_id, l_batch_rec.batch_id,
461 l_batch_rec.batch_date );
462 END IF;
463
464
465 -- Call update table handler to update the record in AR_BATCHES.
466
467 arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
468
469
470 IF PG_DEBUG in ('Y', 'C') THEN
471 arp_util.debug( 'arp_rw_batches_pkg.update_auto_batch()-' );
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 IF PG_DEBUG in ('Y', 'C') THEN
477 arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.update_auto_batch' );
478 END IF;
479 RAISE;
480 END update_auto_batch;
481
482
483 /*===========================================================================+
484 | PROCEDURE |
485 | update_batch_status - Update the receipt batch with the status |
486 | |
487 | DESCRIPTION |
488 | Update the receipt batch with the status |
489 | |
490 | SCOPE - PUBLIC |
491 | |
492 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
493 | arp_util.debug - debug procedure |
494 | |
495 | ARGUMENTS : IN: |
496 | p_batch_id - Batch Id |
497 | |
498 | RETURNS : NONE |
499 | |
500 | NOTES - This procedure will be called by update_row, insert_row procedure |
501 | and update_manual_batch procedure. |
502 | |
503 | MODIFICATION HISTORY - 08/09/95 - Created by Ganesh Vaidee |
504 | |
505 +===========================================================================*/
506 PROCEDURE update_batch_status( p_batch_id IN ar_batches.batch_id%TYPE,
507 p_called_from IN VARCHAR2 DEFAULT NULL) IS
508 l_icr_count NUMBER := 0;
509 l_icr_amount NUMBER := 0;
510 --
511 l_batch_status VARCHAR2( 30 );
512 l_batch_rec ar_batches%ROWTYPE;
513 --Bug7194951
514 l_exists VARCHAR2(1);
515 l_bat_id ar_batches.batch_id%TYPE;
516 l_type ar_batches.type%TYPE;
517 BEGIN
518 IF PG_DEBUG in ('Y', 'C') THEN
519 arp_util.debug( 'arp_rw_batches_check_pkg.update_batch_status()+' );
520 arp_util.debug( 'Batch ID : '||p_batch_id );
521 END IF;
522 --
523 IF PG_DEBUG in ('Y', 'C') THEN
524 arp_util.debug( 'arp_rw_batches_check_pkg.update_batch_status()-' );
525 END IF;
526 --
527 -- Set batch record values to dummy
528 --
529 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
530 --
531 -- determine if the batch has any unposted quick cash receipts
532 -- in the AR_INTERIM_CASH_RECEIPTS table
533 --
534 SELECT NVL(SUM(DECODE
535 (
536 icr.status,
537 NULL, 0,
538 'UNAPP', 1,
539 1
540 )),0),
541 NVL(SUM( icr.amount ), 0 )
542 INTO l_icr_count, l_icr_amount
543 FROM ar_interim_cash_receipts icr,
544 ar_batches b
545 WHERE b.batch_id = p_batch_id
546 AND b.batch_id = icr.batch_id(+);
547 --
548 -- select if the batch has the required count and that all the
549 -- cash receipts are 'APP'
550 -- This statement now counts the quickcash receipts
551 -- OOB - when actual does not match control
552 -- OP - when actual matches control but not all receipts applied
553 -- CL - when actual matches control and everything applied
554 --
555 -- Bug 8944419 changed logic for creating batch status
556 SELECT DECODE
557 (
558 ABS((NVL(SUM(DECODE
559 (
560 cr.status,
561 NULL, 0,
562 'REV', 0, 'CC_CHARGEBACK_REV',0,
563 1
564 )), 0) +
565 l_icr_count -
566 b.control_count )) +
567 ABS((NVL(SUM(DECODE
568 (
569 cr.status,
570 NULL, 0,
571 'REV', 0, 'CC_CHARGEBACK_REV',0,
572 cr.amount
573 )),0) +
574 l_icr_amount -
575 b.control_amount )) + --Qiong fix bug 10252186 changed from - to +
576 ABS((NVL(SUM(DECODE
577 (
578 cr.status,
579 'APP', cr.amount,
580 'NSF', cr.amount,
581 'STOP', cr.amount,
582 0
583 )),0) -
584 b.control_amount )) + --Qiong fix bug 10252186 changed from - to +
585 ABS((NVL(SUM(DECODE
586 (
587 cr.status,
588 'APP', 1,
589 'NSF', 1,
590 'STOP', 1,
591 0
592 )),0) -
593 b.control_count )),
594 0, 'CL',
595 DECODE
596 (
597 ABS(SIGN(NVL(SUM(DECODE
598 (
599 cr.status,
600 NULL, 0,
601 /* 'REV', 1, 'CC_CHARGEBACK_REV',1,*/
602 1
603 )),0) +
604 l_icr_count -
605 b.control_count )) +
606 ABS(SIGN(NVL(SUM(DECODE
607 (
608 cr.status,
609 NULL, 0,
610 /* 'REV', cr.amount, 'CC_CHARGEBACK_REV',cr.amount,*/
611 cr.amount
612 )),0) +
613 l_icr_amount -
614 b.control_amount )),
615 0, 'OP',
616 'OOB'
617 )
618 )
619 INTO l_batch_status
620 FROM ar_cash_receipt_history crh,
621 ar_cash_receipts cr,
622 ar_batches b
623 WHERE b.batch_id = p_batch_id
624 AND crh.batch_id(+) = b.batch_id
625 AND crh.first_posted_record_flag(+) = 'Y'
626 AND cr.cash_receipt_id(+) = crh.cash_receipt_id
627 GROUP BY b.batch_id,
628 b.control_count,
629 b.control_amount;
630 --
631 l_batch_rec.status := l_batch_status;
632 --
633 -- Update batch status
634 --
635 --Bug7194951 Changes Start Here (FP of 7138001 and 7146916)
636
637 /* Chages are put to block updation of status field for remittace batch.
638 Earlier changes for bug 7138001 were made to avoid completion of
639 prepayment matching program in error due to lock of batch of remittance with
640 different program running in concurrent mode.
641 This resulted in perofrmance due to lock contention.
642 Finally prevented updation of batch for status as currently
643 status field is not significant for remittance batch. Prevented for call from prepayment only
644 Also didnot get for lock in case of exisitng code to avoid any regression*/
645
646 arp_standard.debug('arp_rw_batches_check_pkg.update_batch_status: Before locking');
647 l_exists := NULL;
648 BEGIN
649 SELECT 'Y',TYPE
650 INTO l_exists,l_type
651 FROM ar_batches
652 WHERE batch_id = p_batch_id;
653 EXCEPTION
654 WHEN NO_DATA_FOUND THEN
655 NULL;
656 arp_standard.debug('arp_rw_batches_check_pkg.update_batch_status: No data with new status');
657 WHEN OTHERS THEN
658 arp_standard.debug( 'EXCEPTION: arp_rw_batches_check_pkg.update_batch_status lock batch' );
659 RAISE;
660 END;
661
662 IF nvl(p_called_from,'NONE') = 'PREPAYMENT' AND l_type = 'REMITTANCE' THEN
663 l_exists := NULL;
664 END IF;
665 IF nvl(l_exists,'N') = 'Y' THEN
666 /*
667 SELECT batch_id
668 INTO l_bat_id
669 FROM ar_batches
670 WHERE batch_id = p_batch_id
671 FOR UPDATE OF STATUS;
672 */
673 arp_standard.debug('arp_rw_batches_check_pkg.update_batch_status: Before calling update for batch');
674 arp_cr_batches_pkg.update_p( l_batch_rec, p_batch_id );
675 END IF;
676 --Bug7194951 Changes End Here (FP of 7138001 and 7146916)
677
678 --
679 EXCEPTION
680 WHEN OTHERS THEN
681 IF PG_DEBUG in ('Y', 'C') THEN
682 arp_util.debug(
683 'EXCEPTION: arp_rw_batches_check_pkg.update_batch_status' );
684 END IF;
685 RAISE;
686 END update_batch_status;
687 --
688 /*===========================================================================+
689 | PROCEDURE |
690 | validate_args_update_manual |
691 | |
692 | DESCRIPTION |
693 | Validate arguments passed to update_manual_batch procedure |
694 | |
695 | SCOPE - PRIVATE |
696 | |
697 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
698 | arp_util.debug - debug procedure |
699 | |
700 | ARGUMENTS : IN: |
701 | p_type - Batch Type |
702 | OUT: |
703 | |
704 | RETURNS : NONE |
705 | |
706 | NOTES - |
707 | |
708 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
709 | |
710 +===========================================================================*/
711 PROCEDURE validate_args_update_manual(
712 p_row_id IN VARCHAR2,
713 p_batch_id IN ar_batches.batch_id%TYPE,
714 p_batch_date IN ar_batches.batch_date%TYPE ) IS
715
716 BEGIN
717 IF PG_DEBUG in ('Y', 'C') THEN
718 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_manual()+' );
719 END IF;
720 --
721 IF ( p_row_id IS NULL OR p_batch_id IS NULL OR
722 p_batch_date IS NULL ) THEN
723 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
724 APP_EXCEPTION.raise_exception;
725 END IF;
726 --
727 IF PG_DEBUG in ('Y', 'C') THEN
728 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_manual()-' );
729 END IF;
730 --
731 EXCEPTION
732 WHEN OTHERS THEN
733 IF PG_DEBUG in ('Y', 'C') THEN
734 arp_util.debug(
735 'EXCEPTION: arp_rw_batches_pkg.validate_args_update_manual' );
736 END IF;
737 RAISE;
738 END validate_args_update_manual;
739 --
740 /*===========================================================================+
741 | PROCEDURE |
742 | validate_args_update_remit |
743 | |
744 | DESCRIPTION |
745 | Validate arguments passed to update_remit_batch procedure |
746 | |
747 | SCOPE - PRIVATE |
748 | |
749 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
750 | arp_util.debug - debug procedure |
751 | |
752 | ARGUMENTS : IN: |
753 | p_type - Batch Type |
754 | OUT: |
755 | |
756 | RETURNS : NONE |
757 | |
758 | NOTES - |
759 | |
760 | MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
761 | |
762 +===========================================================================*/
763 PROCEDURE validate_args_update_remit(
764 p_row_id IN VARCHAR2,
765 p_batch_id IN ar_batches.batch_id%TYPE,
766 p_batch_date IN ar_batches.batch_date%TYPE ) IS
767
768 BEGIN
769 IF PG_DEBUG in ('Y', 'C') THEN
770 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_remit()+' );
771 END IF;
772 --
773 IF ( p_row_id IS NULL OR p_batch_id IS NULL OR
774 p_batch_date IS NULL ) THEN
775 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
776 APP_EXCEPTION.raise_exception;
777 END IF;
778 --
779 IF PG_DEBUG in ('Y', 'C') THEN
780 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_remit()-' );
781 END IF;
782 --
783 EXCEPTION
784 WHEN OTHERS THEN
785 IF PG_DEBUG in ('Y', 'C') THEN
786 arp_util.debug(
787 'EXCEPTION: arp_rw_batches_pkg.validate_args_update_remit' );
788 END IF;
789 RAISE;
790 END validate_args_update_remit;
791
792 /*===========================================================================+
793 | PROCEDURE |
794 | validate_args_update_auto |
795 | |
796 | DESCRIPTION |
797 | Validate arguments passed to update_auto_batch procedure |
798 | |
799 | SCOPE - PRIVATE |
800 | |
801 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
802 | arp_util.debug - debug procedure |
803 | |
804 | ARGUMENTS : IN: |
805 | p_type - Batch Type |
806 | OUT: |
807 | |
808 | RETURNS : NONE |
809 | |
810 | NOTES - |
811 | |
812 | MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
813 | |
814 +===========================================================================*/
815 PROCEDURE validate_args_update_auto(
816 p_row_id IN VARCHAR2,
817 p_batch_id IN ar_batches.batch_id%TYPE,
818 p_batch_date IN ar_batches.batch_date%TYPE ) IS
819
820 BEGIN
821 IF PG_DEBUG in ('Y', 'C') THEN
822 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_auto()+' );
823 END IF;
824 --
825 IF ( p_row_id IS NULL OR p_batch_id IS NULL OR
826 p_batch_date IS NULL ) THEN
827 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
828 APP_EXCEPTION.raise_exception;
829 END IF;
830 --
831 IF PG_DEBUG in ('Y', 'C') THEN
832 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_auto()-' );
833 END IF;
834 --
835 EXCEPTION
836 WHEN OTHERS THEN
837 IF PG_DEBUG in ('Y', 'C') THEN
838 arp_util.debug(
839 'EXCEPTION: arp_rw_batches_pkg.validate_args_update_auto' );
840 END IF;
841 RAISE;
842 END validate_args_update_auto;
843
844 /*===========================================================================+
845 | PROCEDURE |
846 | check_unique_batch_name - Check uniqueness of a batch for a particular |
847 | batch source. |
848 | |
849 | DESCRIPTION |
850 | This function Check uniqueness of a batch for a particular batch source|
851 | |
852 | SCOPE - PUBLIC |
853 | |
854 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
855 | arp_util.debug - debug procedure |
856 | |
857 | ARGUMENTS : IN: |
858 | p_row_id - Row ID |
859 | p_batch_source_id - Batch Source Id |
860 | p_batch_name - Batch Name |
861 | p_module_name - Module that called this procedure |
862 | p_module_version - Version of the module that called this |
863 | procedure |
864 | OUT: |
865 | |
866 | RETURNS : NONE |
867 | |
868 | NOTES - This is an overlaoded procedure |
869 | |
870 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
871 +===========================================================================*/
872 PROCEDURE check_unique_batch_name(
873 p_row_id IN VARCHAR2,
874 p_batch_source_id IN ar_batch_sources.batch_source_id%TYPE,
875 p_batch_name IN ar_batches.name%TYPE,
876 p_module_name IN VARCHAR2,
877 p_module_version IN VARCHAR2 ) IS
878 l_count NUMBER := 0;
879 BEGIN
880 IF PG_DEBUG in ('Y', 'C') THEN
881 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()+' );
882 arp_util.debug( 'Row Id : '||p_row_id );
883 arp_util.debug( 'Batch Source Id : '||TO_CHAR( p_batch_source_id ) );
884 arp_util.debug( 'Batch Name : '||p_batch_name );
885 END IF;
886 --
887 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
888 validate_args_cu_batch_name( p_batch_source_id, p_batch_name );
889 END IF;
890 --
891 SELECT COUNT(*)
892 INTO l_count
893 FROM AR_BATCHES bat
894 WHERE bat.batch_source_id = p_batch_source_id
895 AND bat.name = p_batch_name
896 AND ( p_row_id IS NULL
897 OR bat.rowid <> p_row_id );
898 IF ( l_count <> 0 ) THEN
899 FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
900 APP_EXCEPTION.raise_exception;
901 END IF;
902 --
903 IF PG_DEBUG in ('Y', 'C') THEN
904 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()-' );
905 END IF;
906 --
907 EXCEPTION
908 WHEN OTHERS THEN
909 IF PG_DEBUG in ('Y', 'C') THEN
910 arp_util.debug(
911 'EXCEPTION: arp_rw_batches_pkg.check_unique_batch_name' );
912 END IF;
913 RAISE;
914 END check_unique_batch_name;
915 --
916 /*===========================================================================+
917 | PROCEDURE |
918 | validate_args_cu_batch_name |
919 | |
920 | DESCRIPTION |
921 | Validate arguments passed to check_unique_batch_name procedure |
922 | |
923 | SCOPE - PRIVATE |
924 | |
925 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
926 | arp_util.debug - debug procedure |
927 | |
928 | ARGUMENTS : IN: |
929 | p_batch_source_id - Batch source ID |
930 | p_batch_name - Batch Name |
931 | OUT: |
932 | |
933 | RETURNS : NONE |
934 | |
935 | NOTES - This is an overlaoded procedure |
936 | |
937 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
938 | |
939 +===========================================================================*/
940 PROCEDURE validate_args_cu_batch_name(
941 p_batch_source_id IN ar_batch_sources.batch_source_id%TYPE,
942 p_batch_name IN ar_batches.name%TYPE ) IS
943 BEGIN
944 IF PG_DEBUG in ('Y', 'C') THEN
945 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()+' );
946 END IF;
947 --
948 IF ( p_batch_source_id is NULL OR p_batch_name is NULL ) THEN
949 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
950 APP_EXCEPTION.raise_exception;
951 END IF;
952 --
953 IF PG_DEBUG in ('Y', 'C') THEN
954 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()-' );
955 END IF;
956 --
957 EXCEPTION
958 WHEN OTHERS THEN
959 IF PG_DEBUG in ('Y', 'C') THEN
960 arp_util.debug(
961 'EXCEPTION: arp_rw_batches_pkg.validate_args_cu_batch_name' );
962 END IF;
963 RAISE;
964 END validate_args_cu_batch_name;
965 --
966 /*===========================================================================+
967 | PROCEDURE |
968 | check_unique_batch_name - Check uniqueness of a batch for a particular |
969 | batch source. |
970 | |
971 | DESCRIPTION |
972 | This function Check uniqueness of a batch for a particular batch source|
973 | |
974 | SCOPE - PUBLIC |
975 | |
976 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
977 | arp_util.debug - debug procedure |
978 | |
979 | ARGUMENTS : IN: |
980 | p_row_id - Row ID |
981 | p_batch_source_name - Batch source Name |
982 | p_batch_name - Batch Name |
983 | p_module_name - Module that called this procedure |
984 | p_module_version - Version of the module that called this |
985 | procedure |
986 | OUT: |
987 | |
988 | RETURNS : NONE |
989 | |
990 | NOTES - This is an OVERLAODED procedure |
991 | |
992 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
993 +===========================================================================*/
994 PROCEDURE check_unique_batch_name(
995 p_row_id IN VARCHAR2,
996 p_batch_source_name IN ar_batch_sources.name%TYPE,
997 p_batch_name IN ar_batches.name%TYPE,
998 p_module_name IN VARCHAR2,
999 p_module_version IN VARCHAR2 ) IS
1000 l_count NUMBER := 0;
1001 BEGIN
1002 IF PG_DEBUG in ('Y', 'C') THEN
1003 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()+' );
1004 arp_util.debug( 'Row Id : '||p_row_id );
1005 arp_util.debug( 'Batch Source Name : '||p_batch_source_name );
1006 arp_util.debug( 'Batch Name : '||p_batch_name );
1007 END IF;
1008 --
1009 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
1010 validate_args_cu_batch_name( p_batch_source_name, p_batch_name );
1011 END IF;
1012 --
1013 SELECT COUNT(*)
1014 INTO l_count
1015 FROM AR_BATCH_SOURCES bs,
1016 AR_BATCHES bat
1017 WHERE bat.batch_source_id = bs.batch_source_id
1018 AND bs.name = p_batch_source_name
1019 AND bat.name = p_batch_name
1020 AND ( p_row_id IS NULL
1021 OR bat.rowid <> p_row_id );
1022 IF ( l_count <> 0 ) THEN
1023 FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
1024 APP_EXCEPTION.raise_exception;
1025 END IF;
1026 --
1027 IF PG_DEBUG in ('Y', 'C') THEN
1028 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()-' );
1029 END IF;
1030 --
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 IF PG_DEBUG in ('Y', 'C') THEN
1034 arp_util.debug(
1035 'EXCEPTION: arp_rw_batches_pkg.check_unique_batch_name' );
1036 END IF;
1037 RAISE;
1038 END check_unique_batch_name;
1039 --
1040 /*===========================================================================+
1041 | PROCEDURE |
1042 | validate_args_cu_batch_name |
1043 | |
1044 | DESCRIPTION |
1045 | Validate arguments passed to check_unique_batch_name procedure |
1046 | |
1047 | SCOPE - PRIVATE |
1048 | |
1049 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1050 | arp_util.debug - debug procedure |
1051 | |
1052 | ARGUMENTS : IN: |
1053 | p_batch_source_name - Batch source Name |
1054 | p_batch_name - Batch Name |
1055 | OUT: |
1056 | |
1057 | RETURNS : NONE |
1058 | |
1059 | NOTES - This is an OVERLAODED procedure |
1060 | |
1061 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
1062 | |
1063 +===========================================================================*/
1064 PROCEDURE validate_args_cu_batch_name(
1065 p_batch_source_name IN ar_batch_sources.name%TYPE,
1066 p_batch_name IN ar_batches.name%TYPE ) IS
1067 BEGIN
1068 IF PG_DEBUG in ('Y', 'C') THEN
1069 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()+' );
1070 END IF;
1071 --
1072 IF ( p_batch_source_name is NULL OR p_batch_name is NULL ) THEN
1073 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1074 APP_EXCEPTION.raise_exception;
1075 END IF;
1076 --
1077 IF PG_DEBUG in ('Y', 'C') THEN
1078 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()-' );
1079 END IF;
1080 --
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 IF PG_DEBUG in ('Y', 'C') THEN
1084 arp_util.debug(
1085 'EXCEPTION: arp_rw_batches_pkg.validate_args_cu_batch_name' );
1086 END IF;
1087 RAISE;
1088 END validate_args_cu_batch_name;
1089 --
1090 /*===========================================================================+
1091 | PROCEDURE |
1092 | check_unique_media_ref - Check uniqueness of a Media reference |
1093 | |
1094 | DESCRIPTION |
1095 | This function Check uniqueness of Media reference in AR_BATCHES table |
1096 | |
1097 | SCOPE - PUBLIC |
1098 | |
1099 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1100 | arp_util.debug - debug procedure |
1101 | |
1102 | ARGUMENTS : IN: |
1103 | p_row_id - Row ID |
1104 | p_module_name - Module that called this procedure |
1105 | p_module_version - Version of the module that called this |
1106 | procedure |
1107 | OUT: |
1108 | |
1109 | RETURNS : NONE |
1110 | |
1111 | NOTES - |
1112 | |
1113 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
1114 +===========================================================================*/
1115 PROCEDURE check_unique_media_ref(
1116 p_row_id IN VARCHAR2,
1117 p_media_ref IN ar_batches.media_reference%TYPE,
1118 p_module_name IN VARCHAR2,
1119 p_module_version IN VARCHAR2 ) IS
1120 l_count NUMBER := 0;
1121 BEGIN
1122 IF PG_DEBUG in ('Y', 'C') THEN
1123 arp_util.debug( 'arp_rw_batches_pkg.check_unique_media_ref()+' );
1124 arp_util.debug('check_unique_media_ref: ' || 'Row Id : '||p_row_id );
1125 arp_util.debug('check_unique_media_ref: ' || 'Media Reference : '||p_media_ref );
1126 END IF;
1127 --
1128 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
1129 validate_args_cu_media_ref( p_media_ref );
1130 END IF;
1131 --
1132 SELECT COUNT(*)
1133 INTO l_count
1134 FROM AR_BATCHES bat
1135 WHERE bat.media_reference = p_media_ref
1136 AND ( p_row_id IS NULL
1137 OR bat.rowid <> p_row_id );
1138 IF ( l_count <> 0 ) THEN
1139 FND_MESSAGE.set_name( 'AR', 'AR_DUP_MEDIA_REFERENCE' );
1140 APP_EXCEPTION.raise_exception;
1141 END IF;
1142 --
1143 IF PG_DEBUG in ('Y', 'C') THEN
1144 arp_util.debug( 'arp_rw_batches_pkg.check_unique_media_ref()-' );
1145 END IF;
1146 --
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 IF PG_DEBUG in ('Y', 'C') THEN
1150 arp_util.debug('check_unique_media_ref: ' ||
1151 'EXCEPTION: arp_rw_batches_pkg.check_unique_media_ref' );
1152 END IF;
1153 RAISE;
1154 END check_unique_media_ref;
1155 --
1156 /*===========================================================================+
1157 | PROCEDURE |
1158 | validate_args_cu_media_ref |
1159 | |
1160 | DESCRIPTION |
1161 | Validate arguments passed to check_unique_media_ref procedure |
1162 | |
1163 | SCOPE - PRIVATE |
1164 | |
1165 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1166 | arp_util.debug - debug procedure |
1167 | |
1168 | ARGUMENTS : IN: |
1169 | p_media_ref - Media reference |
1170 | OUT: |
1171 | |
1172 | RETURNS : NONE |
1173 | |
1174 | NOTES - This is an overlaoded procedure |
1175 | |
1176 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
1177 | |
1178 +===========================================================================*/
1179 PROCEDURE validate_args_cu_media_ref(
1180 p_media_ref IN ar_batches.media_reference%TYPE ) IS
1181 BEGIN
1182 IF PG_DEBUG in ('Y', 'C') THEN
1183 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_media_ref()+' );
1184 END IF;
1185 --
1186 IF ( p_media_ref is NULL ) THEN
1187 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1188 APP_EXCEPTION.raise_exception;
1189 END IF;
1190 --
1191 IF PG_DEBUG in ('Y', 'C') THEN
1192 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_media_ref()-' );
1193 END IF;
1194 --
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 IF PG_DEBUG in ('Y', 'C') THEN
1198 arp_util.debug('validate_args_cu_media_ref: ' ||
1199 'EXCEPTION: arp_rw_batches_pkg.validate_args_cu_media_ref' );
1200 END IF;
1201 RAISE;
1202 END validate_args_cu_media_ref;
1203 --
1204 /*===========================================================================+
1205 | PROCEDURE |
1206 | post_batch_conc_req - Starts the post batch conc. request. |
1207 | |
1208 | DESCRIPTION |
1209 | This procedure starts the post batch conc. request. |
1210 | |
1211 | SCOPE - PUBLIC |
1212 | |
1213 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1214 | arp_util.debug - debug procedure |
1215 | |
1216 | ARGUMENTS : IN: |
1217 | p_batch_id - Batch Id associated with the batch |
1218 | p_set_of_books_id - set of books id |
1219 | p_transmission_id - Transmission Id if batch was created |
1220 | thro' Lockbox. |
1221 | p_module_name - Module name that called this procedure |
1222 | p_module_version - Version of the module that called this |
1223 | procedure |
1224 | OUT: |
1225 | |
1226 | RETURNS : NONE |
1227 | |
1228 | NOTES - |
1229 | |
1230 | MODIFICATION HISTORY - 11/22/95 - Created by Ganesh Vaidee |
1231 | 18-JAN-96 scleung Added the p_set_of_books_id argument. |
1232 | 16-FEB-10 aghoraka Added the p_app_unearn_disc as argument. |
1233 +===========================================================================*/
1234 PROCEDURE post_batch_conc_req( p_batch_id IN ar_batches.batch_id%TYPE,
1235 p_set_of_books_id IN
1236 ar_batches.set_of_books_id%TYPE,
1237 p_transmission_id IN
1238 ar_batches.transmission_id%TYPE,
1239 p_app_unearn_disc IN
1240 VARCHAR2 DEFAULT 'N',
1241 p_batch_applied_status OUT NOCOPY
1242 ar_batches.batch_applied_status%TYPE,
1243 p_request_id OUT NOCOPY ar_batches.request_id%TYPE,
1244 p_module_name IN VARCHAR2,
1245 p_module_version IN VARCHAR2 ) IS
1246 --
1247 l_request_id ar_batches.request_id%TYPE;
1248 l_org_id number;
1249 BEGIN
1250 IF PG_DEBUG in ('Y', 'C') THEN
1251 arp_standard.debug( 'arp_rw_batches_check_pkg.post_batch_conc_req()+');
1252 END IF;
1253 --
1254 IF PG_DEBUG in ('Y', 'C') THEN
1255 arp_standard.debug( 'Batch Id '||p_batch_id );
1256 arp_standard.debug( 'Set Of Books Id '||p_set_of_books_id );
1257 arp_standard.debug( 'Transmission_id = '||p_transmission_id );
1258 END IF;
1259 --
1260 -- Make sure that a batch id has been passed.
1261 --
1262 IF ( p_batch_id IS NULL ) THEN
1263 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1264 APP_EXCEPTION.raise_exception;
1265 END IF;
1266 --
1267 -- Call the concurrent program
1268 --
1269 --MOAC changes
1270 select org_id into l_org_id from ar_system_parameters;
1271 /* Added p_app_unearn_disc for ER 9288902 */
1272 FND_REQUEST.SET_ORG_ID(l_org_id);
1273 l_request_id := FND_REQUEST.submit_request( 'AR', 'ARCABP',
1274 'Submit Post Batch',
1275 SYSDATE, FALSE,
1276 '1',
1277 p_batch_id,
1278 p_set_of_books_id,
1279 0,
1280 0,
1281 NVL(p_app_unearn_disc, 'N'),
1282 p_transmission_id ,
1283 ARP_GLOBAL.sysparam.ORG_ID);
1284 --
1285 p_request_id := l_request_id;
1286 p_batch_applied_status := 'IN_PROCESS';
1287 --
1288 IF PG_DEBUG in ('Y', 'C') THEN
1289 arp_standard.debug( 'arp_rw_batches_check_pkg.post_batch_conc_req()-');
1290 END IF;
1291 --
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF PG_DEBUG in ('Y', 'C') THEN
1295 arp_standard.debug(
1296 'EXCEPTION: arp_rw_batches_check_pkg.post_batch_conc_req' );
1297 END IF;
1298 RAISE;
1299 --
1300 END post_batch_conc_req;
1301 --
1302 /*===========================================================================+
1303 | PROCEDURE |
1304 | get_quick_amount_totals - gets the applied,unapplied,unid. amounts |
1305 | and totals |
1306 | |
1307 | DESCRIPTION |
1308 | This procedure gets the applied,unapplied,unid. amounts and totals |
1309 | |
1310 | SCOPE - PUBLIC |
1311 | |
1312 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1313 | arp_util.debug - debug procedure |
1314 | |
1315 | ARGUMENTS : IN: |
1316 | p_batch_id - Batch Id associated with the batch |
1317 | p_module_name - Module name that called this procedure |
1318 | p_module_version - Version of the module that called this |
1319 | procedure |
1320 | OUT: |
1321 | p_applied_amount_total - applied Amount total |
1322 | p_applied_count_total - applied Amount count |
1323 | |
1324 | RETURNS : NONE |
1325 | |
1326 | NOTES - |
1327 | |
1328 | MODIFICATION HISTORY - 11/28/95 - Created by Ganesh Vaidee |
1329 | 29-JAN-96 Simon Leung Fixed the applied_amount/count logics. |
1330 | 01-APR-96 Simon Leung Added debug message l_break_point. |
1331 | 15-OCT-98 Karen Murphy Cross Currency Lockbox. Modified select |
1332 | statements that look at payment_amount in |
1333 | interim cash receipt lines. Need to consider|
1334 | the amount applied from column as this stores|
1335 | the amount in receipt currency for cross |
1336 | currency applications. |
1337 | 01-JUN-01 Muthuraman. R Added an NVL condition so that Unapplied |
1338 | Receipt counts are correct prior to |
1339 | postquick cash in receipt batches window. |
1340 | 07-JAN-03 K Dhaliwal Bug 2707190 added Claim Amount and Count |
1341 | to get_quick_amount_totals |
1342 +===========================================================================*/
1343 PROCEDURE get_quick_amount_totals( p_batch_id IN ar_batches.batch_id%TYPE,
1344 p_actual_amount_total OUT NOCOPY NUMBER,
1345 p_actual_count_total OUT NOCOPY NUMBER,
1346 p_unidentified_amount_total OUT NOCOPY NUMBER,
1347 p_unidentified_count_total OUT NOCOPY NUMBER,
1348 p_on_account_amount_total OUT NOCOPY NUMBER,
1349 p_on_account_count_total OUT NOCOPY NUMBER,
1350 p_unapplied_amount_total OUT NOCOPY NUMBER,
1351 p_unapplied_count_total OUT NOCOPY NUMBER,
1352 p_applied_amount_total OUT NOCOPY NUMBER,
1353 p_applied_count_total OUT NOCOPY NUMBER,
1354 p_claim_amount_total OUT NOCOPY NUMBER,
1355 p_claim_count_total OUT NOCOPY NUMBER,
1356 p_module_name IN VARCHAR2,
1357 p_module_version IN VARCHAR2 ) IS
1358 --
1359 l_unapplied_amount_total NUMBER := 0;
1360 l_unapplied_count_total NUMBER := 0;
1361 l_applied_amount_total NUMBER := 0;
1362 l_applied_count_total NUMBER := 0;
1363 l_actual_amount_total NUMBER := 0;
1364 l_actual_count_total NUMBER := 0;
1365 l_claim_amount_total_header NUMBER := 0;
1366 l_claim_amount_total_lines NUMBER := 0;
1367 l_onacct_amount_total_header NUMBER := 0;
1368 l_onacct_amount_total_lines NUMBER := 0;
1369 l_break_point VARCHAR2(20);
1370 l_claim_count_total_header NUMBER := 0;
1371 l_claim_count_total_lines NUMBER := 0;
1372 l_onacct_count_total_header NUMBER := 0;
1373 l_onacct_count_total_lines NUMBER := 0;
1374 --
1375 BEGIN
1376 IF PG_DEBUG in ('Y', 'C') THEN
1377 arp_standard.debug( 'arp_rw_batches_check_pkg.get_quick_amount_totals()+');
1378 END IF;
1379 --
1380 IF PG_DEBUG in ('Y', 'C') THEN
1381 arp_standard.debug('get_quick_amount_totals: ' || 'Batch Id '||p_batch_id );
1382 END IF;
1383 --
1384 IF ( p_batch_id IS NULL ) THEN
1385 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1386 APP_EXCEPTION.raise_exception;
1387 END IF;
1388 --
1389 l_break_point := '1st SELECT';
1390 -- Bug #443266
1391 -- Modified this query so that this shows correct on-account
1392 -- receipt count.
1393 --
1394 -- Bug #1560911 mramanat 15/01/2001
1395 -- Added NVL Clause for calculation of applied amount and applied count
1396 -- so that these show up correctly for receipts imported through lockbox.
1397
1398 SELECT SUM( NVL( icr.amount, 0 ) ), COUNT(*),
1399 NVL( SUM( DECODE( icr.special_type,
1400 'CLAIM', icr.amount,
1401 0
1402 )
1403 ), 0
1404 ),
1405 NVL( COUNT( DECODE( icr.special_type,
1406 'CLAIM', 1,
1407 ''
1408 )
1409 ), 0
1410 ),
1411 NVL( SUM( DECODE( icr.special_type,
1412 'ON_ACCOUNT', icr.amount,
1413 0
1414 )
1415 ), 0
1416 ),
1417 NVL( COUNT( DECODE( icr.special_type,
1418 'ON_ACCOUNT', 1,
1419 ''
1420 )
1421 ), 0
1422 ),
1423
1424 NVL( SUM( DECODE( NVL(icr.special_type,'SINGLE'),
1425 'RECEIPT_RULE', icr.amount,
1426 'SINGLE', icr.amount,
1427 0
1428 )
1429 ), 0
1430 ),
1431 NVL( COUNT( DECODE( NVL(icr.special_type,'SINGLE'),
1432 'RECEIPT_RULE', 1,
1433 'SINGLE', 1,
1434 ''
1435 )
1436 ), 0
1437 ),
1438
1439 NVL( SUM( DECODE( icr.special_type,
1440 'UNAPPLIED', icr.amount,
1441 'MULTIPLE', icr.amount,
1442 0
1443 )
1444 ), 0
1445 ),
1446 NVL( COUNT( DECODE( icr.special_type,
1447 'UNAPPLIED', 1,
1448 ''
1449 )
1450 ), 0
1451 ),
1452 NVL( SUM( DECODE( icr.special_type,
1453 'UNIDENTIFIED', icr.amount,
1454 0
1455 )
1456 ), 0
1457 ),
1458 NVL( COUNT( DECODE( icr.special_type,
1459 'UNIDENTIFIED', 1,
1460 ''
1461 )
1462 ), 0
1463 )
1464 INTO l_actual_amount_total,
1465 l_actual_count_total,
1466 l_claim_amount_total_header,
1467 l_claim_count_total_header,
1468 l_onacct_amount_total_header,
1469 l_onacct_count_total_header,
1470 l_applied_amount_total,
1471 l_applied_count_total,
1472 l_unapplied_amount_total,
1473 l_unapplied_count_total,
1474 p_unidentified_amount_total,
1475 p_unidentified_count_total
1476 FROM ar_interim_cash_receipts icr
1477 WHERE icr.batch_id = p_batch_id;
1478 --
1479 --
1480 --
1481 l_break_point := '2nd SELECT';
1482 SELECT l_applied_amount_total + NVL(SUM( nvl(icrl.amount_applied_from, icrl.payment_amount) ),0),
1483 l_unapplied_amount_total - NVL(SUM( nvl(icrl.amount_applied_from, icrl.payment_amount) ),0),
1484 NVL( SUM(decode(icrl.payment_schedule_id,-4,nvl(icrl.amount_applied_from, icrl.payment_amount),0 )),0),
1485 NVL( SUM(decode(icrl.payment_schedule_id,-1,nvl(icrl.amount_applied_from, icrl.payment_amount),0 )),0)
1486 INTO l_applied_amount_total,
1487 l_unapplied_amount_total,
1488 l_claim_amount_total_lines,
1489 l_onacct_amount_total_lines
1490 FROM ar_interim_cash_receipts icr,
1491 ar_interim_cash_receipt_lines icrl
1492 WHERE icrl.cash_receipt_id = icr.cash_receipt_id
1493 AND icr.batch_id = p_batch_id;
1494 --
1495 -- Add to unapplied count if all amounts of receipt has not been paid off
1496 --
1497
1498 /* 01-Jun-2001 Bugfix 1773585.
1499 Added an NVL condition so that Unapplied Receipt counts are correct
1500 prior to postquick cash in receipt batches window.
1501 */
1502
1503 l_break_point := '3rd SELECT';
1504 SELECT NVL(l_applied_count_total,0) +
1505 COUNT( DECODE( SIGN(rec.amount - SUM( nvl(ln.amount_applied_from, ln.payment_amount) ) ),
1506 0, 1, '' )),
1507 NVL(l_unapplied_count_total,0) +
1508 COUNT( DECODE( SIGN(rec.amount - NVL( SUM( nvl(ln.amount_applied_from, ln.payment_amount) ) , 0 )),
1509 1, 1, '' ))
1510 INTO l_applied_count_total,
1511 l_unapplied_count_total
1512 FROM ar_interim_cash_receipt_lines ln,
1513 ar_interim_cash_receipts rec
1514 WHERE rec.batch_id = p_batch_id
1515 AND rec.cash_receipt_id = ln.cash_receipt_id
1516 GROUP BY
1517 rec.cash_receipt_id,
1518 rec.amount;
1519
1520 l_break_point := '4th SELECT';
1521 SELECT sum(sign(CLAIM)) Claims_Count
1522 ,sum(sign(ON_ACC)) On_Account_Count
1523 --,sum(nvl(TRX,0)) + nvl(l_applied_count_total,0) Trx_Count
1524 INTO l_claim_count_total_lines
1525 ,l_onacct_count_total_lines
1526 --,l_applied_count_total
1527 FROM (select sum(decode(payment_schedule_id,-4,1,0)) CLAIM
1528 , SUM(decode(payment_schedule_id,-1,1,0)) ON_ACC
1529 , sum(decode(sign(payment_schedule_id),1,1,0)) TRX
1530 from ar_interim_cash_receipt_lines ln
1531 where ln.batch_id = p_batch_id
1532 );
1533
1534 --
1535 -- Copy local valued to OUT NOCOPY parameters
1536 --
1537 p_claim_amount_total := nvl(l_claim_amount_total_header,0) + nvl(l_claim_amount_total_lines,0);
1538 p_on_account_amount_total := nvl(l_onacct_amount_total_header,0) + nvl(l_onacct_amount_total_lines,0);
1539 p_claim_count_total := nvl(l_claim_count_total_header,0) + nvl(l_claim_count_total_lines,0);
1540 p_on_account_count_total := nvl(l_onacct_count_total_header,0) + nvl(l_onacct_count_total_lines,0);
1541 --
1542 p_actual_amount_total := nvl(l_actual_amount_total,0);
1543 p_actual_count_total := nvl(l_actual_count_total,0);
1544 --
1545 p_applied_amount_total := nvl(l_applied_amount_total,0) - nvl(l_claim_amount_total_lines ,0)- nvl(l_onacct_amount_total_lines,0);
1546 p_applied_count_total := nvl(l_applied_count_total,0);
1547 --
1548 p_unapplied_amount_total := nvl(l_unapplied_amount_total,0);
1549 p_unapplied_count_total := nvl(l_unapplied_count_total,0);
1550 --
1551 IF PG_DEBUG in ('Y', 'C') THEN
1552 arp_standard.debug('Get Quick Cash Totals - Claim Count Header='||to_char(l_claim_count_total_header));
1553 arp_standard.debug('Get Quick Cash Totals - Claim Count Lines='||to_char(l_claim_count_total_lines));
1554 arp_standard.debug('Get Quick Cash Totals - onacct Count Header='||to_char(l_onacct_count_total_header));
1555 arp_standard.debug('Get Quick Cash Totals - Onacct Count Lines='||to_char(l_onacct_count_total_lines));
1556 arp_standard.debug('Get Quick Cash Totals - Claim Count='||to_char(p_claim_count_total));
1557 arp_standard.debug( 'arp_rw_batches_check_pkg.get_quick_amount_totals()-');
1558 END IF;
1559 --
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 IF PG_DEBUG in ('Y', 'C') THEN
1563 arp_standard.debug('get_quick_amount_totals: ' ||
1564 'EXCEPTION: arp_rw_batches_check_pkg.get_quick_amount_totals '||
1565 l_break_point );
1566 END IF;
1567 RAISE;
1568 --
1569 END get_quick_amount_totals;
1570 --
1571
1572 PROCEDURE get_reg_amount_totals( p_batch_id IN ar_batches.batch_id%TYPE,
1573 p_actual_amount_total OUT NOCOPY NUMBER,
1574 p_actual_count_total OUT NOCOPY NUMBER,
1575 p_unidentified_amount_total OUT NOCOPY NUMBER,
1576 p_unidentified_count_total OUT NOCOPY NUMBER,
1577 p_on_account_amount_total OUT NOCOPY NUMBER,
1578 p_on_account_count_total OUT NOCOPY NUMBER,
1579 p_returned_amount_total OUT NOCOPY NUMBER,
1580 p_returned_count_total OUT NOCOPY NUMBER,
1581 p_reversed_amount_total OUT NOCOPY NUMBER,
1582 p_reversed_count_total OUT NOCOPY NUMBER,
1583 p_unapplied_amount_total OUT NOCOPY NUMBER,
1584 p_unapplied_count_total OUT NOCOPY NUMBER,
1585 p_applied_amount_total OUT NOCOPY NUMBER,
1586 p_applied_count_total OUT NOCOPY NUMBER,
1587 p_claim_amount_total OUT NOCOPY NUMBER,
1588 p_claim_count_total OUT NOCOPY NUMBER,
1589 p_prepayment_amount_total OUT NOCOPY NUMBER,
1590 p_prepayment_count_total OUT NOCOPY NUMBER,
1591 p_misc_amount_total OUT NOCOPY NUMBER,
1592 p_misc_count_total OUT NOCOPY NUMBER,
1593 p_module_name IN VARCHAR2,
1594 p_module_version IN VARCHAR2 ) IS
1595 --
1596 l_unapplied_amount_total NUMBER := 0;
1597 l_unapplied_count_total NUMBER := 0;
1598 l_applied_amount_total NUMBER := 0;
1599 l_applied_count_total NUMBER := 0;
1600 l_actual_amount_total NUMBER := 0;
1601 l_actual_count_total NUMBER := 0;
1602 l_on_account_count_total NUMBER :=0;
1603 l_on_account_amount_total NUMBER :=0;
1604 l_claim_amount_total NUMBER := 0;
1605 l_claim_count_total NUMBER := 0;
1606 l_prepayment_amount_total NUMBER := 0;
1607 l_prepayment_count_total NUMBER := 0;
1608 l_misc_count_total NUMBER := 0;
1609 l_misc_amount_total NUMBER := 0;
1610 l_break_point VARCHAR2(20);
1611 --
1612 BEGIN
1613 IF PG_DEBUG in ('Y', 'C') THEN
1614 arp_standard.debug( 'arp_rw_batches_check_pkg.get_reg_amount_totals()+');
1615 END IF;
1616 --
1617 IF PG_DEBUG in ('Y', 'C') THEN
1618 arp_standard.debug('get_reg_amount_totals: ' || 'Batch Id '||p_batch_id );
1619 END IF;
1620 --
1621 IF ( p_batch_id IS NULL ) THEN
1622 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1623 APP_EXCEPTION.raise_exception;
1624 END IF;
1625 --
1626 --
1627 -- Shiv Ragunat - 10/22/96
1628 -- As part of fix for Bug 398344
1629 -- Actual amount now is sum(cr.amount) for that batch_id
1630 -- Actual Count is count(cr.status) for that batch_id.
1631 --
1632 --
1633 l_break_point := '1st SELECT';
1634 -- Fixed following query for Bug #435632.
1635 --
1636 -- Bug 8944419 Included receipt reversed using newly added reversal type
1637 -- in Reversed category as per PM input.
1638 SELECT NVL( SUM( cr.amount ),0),
1639 NVL( COUNT( cr.status ) ,0 ),
1640 NVL( SUM(DECODE( cr.status, 'NSF', cr.amount, 'STOP', cr.amount,0)),0),
1641 NVL( COUNT(DECODE( cr.status, 'NSF', 1, 'STOP', 1,'')),0),
1642 NVL( SUM(DECODE( cr.status, 'REV', cr.amount, 'CC_CHARGEBACK_REV', cr.amount, 0)), 0),
1643 NVL( COUNT(DECODE( cr.status, 'REV', 1, 'CC_CHARGEBACK_REV', 1, '')), 0),
1644 NVL( COUNT(DECODE( cr.status , 'UNAPP', 1, '')),0),
1645 NVL(COUNT (DECODE(cr.status,
1646 'APP', DECODE( cr.type, 'MISC', '', 1),
1647 '')), 0),
1648 NVL( SUM( DECODE( cr.status, 'UNID', cr.amount,0)), 0),
1649 NVL( COUNT(DECODE( cr.status , 'UNID', 1, '')),0),
1650 NVL( SUM( DECODE( cr.type,
1651 'MISC', DECODE( cr.status, 'REV', 0,
1652 'NSF', 0,
1653 'STOP', 0,
1654 'CC_CHARGEBACK_REV', 0,cr.amount), --For9821276
1655 0 )), 0),
1656 NVL( COUNT(DECODE( cr.type,
1657 'MISC', DECODE( cr.status, 'REV', '',
1658 'NSF', '',
1659 'STOP', '',
1660 'CC_CHARGEBACK_REV', '',1), --For9821276
1661 '')), 0)
1662 INTO l_actual_amount_total,
1663 l_actual_count_total,
1664 p_returned_amount_total,
1665 p_returned_count_total,
1666 p_reversed_amount_total,
1667 p_reversed_count_total,
1668 l_unapplied_count_total,
1669 l_applied_count_total,
1670 p_unidentified_amount_total,
1671 p_unidentified_count_total,
1672 l_misc_amount_total,
1673 l_misc_count_total
1674 FROM ar_cash_receipts cr,
1675 ar_cash_receipt_history crh
1676 WHERE cr.cash_receipt_id = crh.cash_receipt_id
1677 AND crh.first_posted_record_flag = 'Y'
1678 AND crh.batch_id = p_batch_id;
1679 --
1680 --
1681 --
1682
1683 l_break_point := '2nd SELECT';
1684 SELECT NVL(SUM(DECODE(ra.STATUS, 'APP', nvl(ra.amount_applied_from, ra.amount_applied),
1685 'ACTIVITY',--Added for bug 1647470
1686 DECODE(ra.applied_payment_schedule_id,
1687 -3,ra.amount_applied,
1688 -8,ra.amount_applied
1689 ,Decode(ra.RECEIVABLES_TRX_ID,-16,ra.amount_applied,0)),
1690 0)),0),
1691 NVL( SUM( DECODE( ra.status, 'UNAPP', ra.amount_applied,0)), 0),
1692 NVL(SUM(DECODE(ra.STATUS, 'ACC', ra.amount_applied, 0)),0),
1693 NVL(SUM(DECODE(ra.STATUS, 'OTHER ACC',DECODE(applied_payment_schedule_id,
1694 -4,NVL(ra.amount_applied_from, ra.amount_applied),0),0)),0),
1695 NVL(SUM(DECODE(ra.STATUS, 'OTHER ACC',DECODE(applied_payment_schedule_id,
1696 -7,NVL(ra.amount_applied_from, ra.amount_applied),0),0)),0)
1697 INTO l_applied_amount_total,
1698 l_unapplied_amount_total,
1699 l_on_account_amount_total,
1700 l_claim_amount_total,
1701 l_prepayment_amount_total
1702 FROM ar_receivable_applications ra,
1703 ar_cash_receipt_history crh
1704 WHERE ra.cash_receipt_id = crh.cash_receipt_id
1705 AND crh.first_posted_record_flag = 'Y'
1706 AND crh.batch_id = p_batch_id;
1707 --
1708 -- Added this query for Bug #443266.
1709 -- Get the receipt counts for the fully on-account receipts.
1710 --
1711 l_break_point := '3rd SELECT';
1712 --
1713 Begin
1714 --
1715 SELECT nvl(count(cr.cash_receipt_id), 0)
1716 INTO l_on_account_count_total
1717 FROM ar_cash_receipts cr,
1718 ar_cash_receipt_history crh
1719 WHERE crh.cash_receipt_id = cr.cash_receipt_id
1720 AND crh.first_posted_record_flag = 'Y'
1721 AND crh.batch_id = p_batch_id
1722 AND cr.amount = (SELECT sum(ra.amount_applied)
1723 FROM ar_receivable_applications ra
1724 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1725 AND ra.status = 'ACC'
1726 AND ra.display = 'Y'); -- Fix 1178963
1727 --
1728 exception
1729 when no_data_found then
1730 l_on_account_count_total := 0;
1731 when others then
1732 raise;
1733 End;
1734 --
1735 /* jbeckett 04-apr-01 following query added for deductions */
1736 --
1737 -- Get the receipt count for receipts that are under claim investigation.
1738 --
1739 -- Bug 1811239 - only receipts totally applied to claim investigation
1740
1741
1742 l_break_point := '4th SELECT';
1743 --
1744 Begin
1745 --
1746 --Bug 2645671-Show the count only when the whole receipt is applied to claim
1747 -- Bug 3590163: disabled index on applied_payment_schedule_id
1748
1749 SELECT nvl(count(cr.cash_receipt_id), 0)
1750 INTO l_claim_count_total
1751 FROM ar_cash_receipts cr,
1752 ar_cash_receipt_history crh
1753 WHERE crh.cash_receipt_id = cr.cash_receipt_id
1754 AND crh.first_posted_record_flag = 'Y'
1755 AND crh.batch_id = p_batch_id
1756 AND cr.amount = (SELECT
1757 sum(ra.amount_applied)
1758 FROM ar_receivable_applications ra
1759 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1760 AND ra.status = 'OTHER ACC'
1761 AND ra.applied_payment_schedule_id + 0 = -4
1762 AND ra.display = 'Y');
1763 --
1764 exception
1765 when no_data_found then
1766 l_claim_count_total := 0;
1767 when others then
1768 raise;
1769 End;
1770
1771 l_break_point := '5th SELECT';
1772 --
1773 Begin
1774 --
1775 --Bug 2645671-Show the count only when the whole receipt is applied to prepayment
1776 -- Bug 3590163: disabled index on applied_payment_schedule_id
1777
1778 SELECT nvl(count(cr.cash_receipt_id), 0)
1779 INTO l_prepayment_count_total
1780 FROM ar_cash_receipts cr,
1781 ar_cash_receipt_history crh
1782 WHERE crh.cash_receipt_id = cr.cash_receipt_id
1783 AND crh.first_posted_record_flag = 'Y'
1784 AND crh.batch_id = p_batch_id
1785 AND cr.amount = (SELECT
1786 sum(ra.amount_applied)
1787 FROM ar_receivable_applications ra
1788 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1789 AND ra.status = 'OTHER ACC'
1790 AND ra.applied_payment_schedule_id + 0 = -7
1791 AND ra.display = 'Y');
1792 --
1793 exception
1794 when no_data_found then
1795 l_prepayment_count_total := 0;
1796 when others then
1797 raise;
1798 end;
1799 --
1800 --
1801 -- Copy local valued to OUT NOCOPY pacrmeters
1802 --
1803 p_actual_amount_total := l_actual_amount_total;
1804 p_actual_count_total := l_actual_count_total;
1805 --
1806 p_applied_amount_total := l_applied_amount_total;
1807 -- Bug #443266
1808 -- Reduced the value by l_on_account_count_total, as
1809 -- p_applied_count_total also shows the fully on-account receipts.
1810 --
1811 p_applied_count_total := (l_applied_count_total - l_on_account_count_total
1812 - l_claim_count_total);
1813 --
1814 p_unapplied_amount_total := l_unapplied_amount_total;
1815 p_unapplied_count_total := l_unapplied_count_total;
1816 --
1817 p_on_account_amount_total := l_on_account_amount_total;
1818 -- Bug #443266
1819 -- Earlier p_on_account_count_total was assigned value zero,
1820 -- Now it shows the count of fully on-account receipts.
1821 p_on_account_count_total := l_on_account_count_total;
1822 --
1823 p_claim_amount_total := l_claim_amount_total;
1824 p_claim_count_total := l_claim_count_total;
1825 --
1826 p_prepayment_amount_total := l_prepayment_amount_total;
1827 p_prepayment_count_total := l_prepayment_count_total;
1828 --
1829 p_misc_amount_total := l_misc_amount_total;
1830 p_misc_count_total := l_misc_count_total;
1831 --
1832 IF PG_DEBUG in ('Y', 'C') THEN
1833 arp_standard.debug( 'arp_rw_batches_check_pkg.get_reg_amount_totals()-');
1834 END IF;
1835 --
1836 EXCEPTION
1837 WHEN OTHERS THEN
1838 IF PG_DEBUG in ('Y', 'C') THEN
1839 arp_standard.debug('get_reg_amount_totals: ' ||
1840 'EXCEPTION: arp_rw_batches_check_pkg.get_reg_amount_totals '||
1841 l_break_point );
1842 END IF;
1843 RAISE;
1844 --
1845 END get_reg_amount_totals;
1846
1847 END ARP_RW_BATCHES_CHECK_PKG;