[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.9.12010000.2 2008/11/12 14:44:03 mgaleti 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) IS
507 l_icr_count NUMBER := 0;
508 l_icr_amount NUMBER := 0;
509 --
510 l_batch_status VARCHAR2( 30 );
511 l_batch_rec ar_batches%ROWTYPE;
512 BEGIN
513 IF PG_DEBUG in ('Y', 'C') THEN
514 arp_util.debug( 'arp_rw_batches_check_pkg.update_batch_status()+' );
515 arp_util.debug( 'Batch ID : '||p_batch_id );
516 END IF;
517 --
518 IF PG_DEBUG in ('Y', 'C') THEN
519 arp_util.debug( 'arp_rw_batches_check_pkg.update_batch_status()-' );
520 END IF;
521 --
522 -- Set batch record values to dummy
523 --
524 arp_cr_batches_pkg.set_to_dummy( l_batch_rec );
525 --
526 -- determine if the batch has any unposted quick cash receipts
527 -- in the AR_INTERIM_CASH_RECEIPTS table
528 --
529 SELECT NVL(SUM(DECODE
530 (
531 icr.status,
532 NULL, 0,
533 'UNAPP', 1,
534 1
535 )),0),
536 NVL(SUM( icr.amount ), 0 )
537 INTO l_icr_count, l_icr_amount
538 FROM ar_interim_cash_receipts icr,
539 ar_batches b
540 WHERE b.batch_id = p_batch_id
541 AND b.batch_id = icr.batch_id(+);
542 --
543 -- select if the batch has the required count and that all the
544 -- cash receipts are 'APP'
545 -- This statement now counts the quickcash receipts
546 -- OOB - when actual does not match control
547 -- OP - when actual matches control but not all receipts applied
548 -- CL - when actual matches control and everything applied
549 --
550 SELECT DECODE
551 (
552 ABS(SIGN(NVL(SUM(DECODE
553 (
554 cr.status,
555 NULL, 0,
556 'REV', 0,
557 1
558 )), 0) +
559 l_icr_count -
560 b.control_count )) +
561 ABS(SIGN(NVL(SUM(DECODE
562 (
563 cr.status,
564 NULL, 0,
565 'REV', 0,
566 cr.amount
567 )),0) +
568 l_icr_amount -
569 b.control_amount )) +
570 ABS(SIGN(NVL(SUM(DECODE
571 (
572 cr.status,
573 'APP', cr.amount,
574 'NSF', cr.amount,
575 'STOP', cr.amount,
576 0
577 )),0) -
578 b.control_amount )) +
579 ABS(SIGN(NVL(SUM(DECODE
580 (
581 cr.status,
582 'APP', 1,
583 'NSF', 1,
584 'STOP', 1,
585 0
586 )),0) -
587 b.control_count )),
588 0, 'CL',
589 DECODE
590 (
591 ABS(SIGN(NVL(SUM(DECODE
592 (
593 cr.status,
594 NULL, 0,
595 'REV', 0,
596 1
597 )),0) +
598 l_icr_count -
599 b.control_count )) +
600 ABS(SIGN(NVL(SUM(DECODE
601 (
602 cr.status,
603 NULL, 0,
604 'REV', 0,
605 cr.amount
606 )),0) +
607 l_icr_amount -
608 b.control_amount )),
609 0, 'OP',
610 'OOB'
611 )
612 )
613 INTO l_batch_status
614 FROM ar_cash_receipt_history crh,
615 ar_cash_receipts cr,
616 ar_batches b
617 WHERE b.batch_id = p_batch_id
618 AND crh.batch_id(+) = b.batch_id
619 AND crh.first_posted_record_flag(+) = 'Y'
620 AND cr.cash_receipt_id(+) = crh.cash_receipt_id
621 GROUP BY b.batch_id,
622 b.control_count,
623 b.control_amount;
624 --
625 l_batch_rec.status := l_batch_status;
626 --
627 -- Update batch status
628 --
629 arp_cr_batches_pkg.update_p( l_batch_rec, p_batch_id );
630 --
631 EXCEPTION
632 WHEN OTHERS THEN
633 IF PG_DEBUG in ('Y', 'C') THEN
634 arp_util.debug(
635 'EXCEPTION: arp_rw_batches_check_pkg.update_batch_status' );
636 END IF;
637 RAISE;
638 END update_batch_status;
639 --
640 /*===========================================================================+
641 | PROCEDURE |
642 | validate_args_update_manual |
643 | |
644 | DESCRIPTION |
645 | Validate arguments passed to update_manual_batch procedure |
646 | |
647 | SCOPE - PRIVATE |
648 | |
649 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
650 | arp_util.debug - debug procedure |
651 | |
652 | ARGUMENTS : IN: |
653 | p_type - Batch Type |
654 | OUT: |
655 | |
656 | RETURNS : NONE |
657 | |
658 | NOTES - |
659 | |
660 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
661 | |
662 +===========================================================================*/
663 PROCEDURE validate_args_update_manual(
664 p_row_id IN VARCHAR2,
665 p_batch_id IN ar_batches.batch_id%TYPE,
666 p_batch_date IN ar_batches.batch_date%TYPE ) IS
667
668 BEGIN
669 IF PG_DEBUG in ('Y', 'C') THEN
670 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_manual()+' );
671 END IF;
672 --
673 IF ( p_row_id IS NULL OR p_batch_id IS NULL OR
674 p_batch_date IS NULL ) THEN
675 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
676 APP_EXCEPTION.raise_exception;
677 END IF;
678 --
679 IF PG_DEBUG in ('Y', 'C') THEN
680 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_manual()-' );
681 END IF;
682 --
683 EXCEPTION
684 WHEN OTHERS THEN
685 IF PG_DEBUG in ('Y', 'C') THEN
686 arp_util.debug(
687 'EXCEPTION: arp_rw_batches_pkg.validate_args_update_manual' );
688 END IF;
689 RAISE;
690 END validate_args_update_manual;
691 --
692 /*===========================================================================+
693 | PROCEDURE |
694 | validate_args_update_remit |
695 | |
696 | DESCRIPTION |
697 | Validate arguments passed to update_remit_batch procedure |
698 | |
699 | SCOPE - PRIVATE |
700 | |
701 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
702 | arp_util.debug - debug procedure |
703 | |
704 | ARGUMENTS : IN: |
705 | p_type - Batch Type |
706 | OUT: |
707 | |
708 | RETURNS : NONE |
709 | |
710 | NOTES - |
711 | |
712 | MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
713 | |
714 +===========================================================================*/
715 PROCEDURE validate_args_update_remit(
716 p_row_id IN VARCHAR2,
717 p_batch_id IN ar_batches.batch_id%TYPE,
718 p_batch_date IN ar_batches.batch_date%TYPE ) IS
719
720 BEGIN
721 IF PG_DEBUG in ('Y', 'C') THEN
722 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_remit()+' );
723 END IF;
724 --
725 IF ( p_row_id IS NULL OR p_batch_id IS NULL OR
726 p_batch_date IS NULL ) THEN
727 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
728 APP_EXCEPTION.raise_exception;
729 END IF;
730 --
731 IF PG_DEBUG in ('Y', 'C') THEN
732 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_remit()-' );
733 END IF;
734 --
735 EXCEPTION
736 WHEN OTHERS THEN
737 IF PG_DEBUG in ('Y', 'C') THEN
738 arp_util.debug(
739 'EXCEPTION: arp_rw_batches_pkg.validate_args_update_remit' );
740 END IF;
741 RAISE;
742 END validate_args_update_remit;
743
744 /*===========================================================================+
745 | PROCEDURE |
746 | validate_args_update_auto |
747 | |
748 | DESCRIPTION |
749 | Validate arguments passed to update_auto_batch procedure |
750 | |
751 | SCOPE - PRIVATE |
752 | |
753 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
754 | arp_util.debug - debug procedure |
755 | |
756 | ARGUMENTS : IN: |
757 | p_type - Batch Type |
758 | OUT: |
759 | |
760 | RETURNS : NONE |
761 | |
762 | NOTES - |
763 | |
764 | MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
765 | |
766 +===========================================================================*/
767 PROCEDURE validate_args_update_auto(
768 p_row_id IN VARCHAR2,
769 p_batch_id IN ar_batches.batch_id%TYPE,
770 p_batch_date IN ar_batches.batch_date%TYPE ) IS
771
772 BEGIN
773 IF PG_DEBUG in ('Y', 'C') THEN
774 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_auto()+' );
775 END IF;
776 --
777 IF ( p_row_id IS NULL OR p_batch_id IS NULL OR
778 p_batch_date IS NULL ) THEN
779 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
780 APP_EXCEPTION.raise_exception;
781 END IF;
782 --
783 IF PG_DEBUG in ('Y', 'C') THEN
784 arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_auto()-' );
785 END IF;
786 --
787 EXCEPTION
788 WHEN OTHERS THEN
789 IF PG_DEBUG in ('Y', 'C') THEN
790 arp_util.debug(
791 'EXCEPTION: arp_rw_batches_pkg.validate_args_update_auto' );
792 END IF;
793 RAISE;
794 END validate_args_update_auto;
795
796 /*===========================================================================+
797 | PROCEDURE |
798 | check_unique_batch_name - Check uniqueness of a batch for a particular |
799 | batch source. |
800 | |
801 | DESCRIPTION |
802 | This function Check uniqueness of a batch for a particular batch source|
803 | |
804 | SCOPE - PUBLIC |
805 | |
806 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
807 | arp_util.debug - debug procedure |
808 | |
809 | ARGUMENTS : IN: |
810 | p_row_id - Row ID |
811 | p_batch_source_id - Batch Source Id |
812 | p_batch_name - Batch Name |
813 | p_module_name - Module that called this procedure |
814 | p_module_version - Version of the module that called this |
815 | procedure |
816 | OUT: |
817 | |
818 | RETURNS : NONE |
819 | |
820 | NOTES - This is an overlaoded procedure |
821 | |
822 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
823 +===========================================================================*/
824 PROCEDURE check_unique_batch_name(
825 p_row_id IN VARCHAR2,
826 p_batch_source_id IN ar_batch_sources.batch_source_id%TYPE,
827 p_batch_name IN ar_batches.name%TYPE,
828 p_module_name IN VARCHAR2,
829 p_module_version IN VARCHAR2 ) IS
830 l_count NUMBER := 0;
831 BEGIN
832 IF PG_DEBUG in ('Y', 'C') THEN
833 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()+' );
834 arp_util.debug( 'Row Id : '||p_row_id );
835 arp_util.debug( 'Batch Source Id : '||TO_CHAR( p_batch_source_id ) );
836 arp_util.debug( 'Batch Name : '||p_batch_name );
837 END IF;
838 --
839 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
840 validate_args_cu_batch_name( p_batch_source_id, p_batch_name );
841 END IF;
842 --
843 SELECT COUNT(*)
844 INTO l_count
845 FROM AR_BATCHES bat
846 WHERE bat.batch_source_id = p_batch_source_id
847 AND bat.name = p_batch_name
848 AND ( p_row_id IS NULL
849 OR bat.rowid <> p_row_id );
850 IF ( l_count <> 0 ) THEN
851 FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
852 APP_EXCEPTION.raise_exception;
853 END IF;
854 --
855 IF PG_DEBUG in ('Y', 'C') THEN
856 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()-' );
857 END IF;
858 --
859 EXCEPTION
860 WHEN OTHERS THEN
861 IF PG_DEBUG in ('Y', 'C') THEN
862 arp_util.debug(
863 'EXCEPTION: arp_rw_batches_pkg.check_unique_batch_name' );
864 END IF;
865 RAISE;
866 END check_unique_batch_name;
867 --
868 /*===========================================================================+
869 | PROCEDURE |
870 | validate_args_cu_batch_name |
871 | |
872 | DESCRIPTION |
873 | Validate arguments passed to check_unique_batch_name procedure |
874 | |
875 | SCOPE - PRIVATE |
876 | |
877 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
878 | arp_util.debug - debug procedure |
879 | |
880 | ARGUMENTS : IN: |
881 | p_batch_source_id - Batch source ID |
882 | p_batch_name - Batch Name |
883 | OUT: |
884 | |
885 | RETURNS : NONE |
886 | |
887 | NOTES - This is an overlaoded procedure |
888 | |
889 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
890 | |
891 +===========================================================================*/
892 PROCEDURE validate_args_cu_batch_name(
893 p_batch_source_id IN ar_batch_sources.batch_source_id%TYPE,
894 p_batch_name IN ar_batches.name%TYPE ) IS
895 BEGIN
896 IF PG_DEBUG in ('Y', 'C') THEN
897 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()+' );
898 END IF;
899 --
900 IF ( p_batch_source_id is NULL OR p_batch_name is NULL ) THEN
901 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
902 APP_EXCEPTION.raise_exception;
903 END IF;
904 --
905 IF PG_DEBUG in ('Y', 'C') THEN
906 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()-' );
907 END IF;
908 --
909 EXCEPTION
910 WHEN OTHERS THEN
911 IF PG_DEBUG in ('Y', 'C') THEN
912 arp_util.debug(
913 'EXCEPTION: arp_rw_batches_pkg.validate_args_cu_batch_name' );
914 END IF;
915 RAISE;
916 END validate_args_cu_batch_name;
917 --
918 /*===========================================================================+
919 | PROCEDURE |
920 | check_unique_batch_name - Check uniqueness of a batch for a particular |
921 | batch source. |
922 | |
923 | DESCRIPTION |
924 | This function Check uniqueness of a batch for a particular batch source|
925 | |
926 | SCOPE - PUBLIC |
927 | |
928 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
929 | arp_util.debug - debug procedure |
930 | |
931 | ARGUMENTS : IN: |
932 | p_row_id - Row ID |
933 | p_batch_source_name - Batch source Name |
934 | p_batch_name - Batch Name |
935 | p_module_name - Module that called this procedure |
936 | p_module_version - Version of the module that called this |
937 | procedure |
938 | OUT: |
939 | |
940 | RETURNS : NONE |
941 | |
942 | NOTES - This is an OVERLAODED procedure |
943 | |
944 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
945 +===========================================================================*/
946 PROCEDURE check_unique_batch_name(
947 p_row_id IN VARCHAR2,
948 p_batch_source_name IN ar_batch_sources.name%TYPE,
949 p_batch_name IN ar_batches.name%TYPE,
950 p_module_name IN VARCHAR2,
951 p_module_version IN VARCHAR2 ) IS
952 l_count NUMBER := 0;
953 BEGIN
954 IF PG_DEBUG in ('Y', 'C') THEN
955 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()+' );
956 arp_util.debug( 'Row Id : '||p_row_id );
957 arp_util.debug( 'Batch Source Name : '||p_batch_source_name );
958 arp_util.debug( 'Batch Name : '||p_batch_name );
959 END IF;
960 --
961 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
962 validate_args_cu_batch_name( p_batch_source_name, p_batch_name );
963 END IF;
964 --
965 SELECT COUNT(*)
966 INTO l_count
967 FROM AR_BATCH_SOURCES bs,
968 AR_BATCHES bat
969 WHERE bat.batch_source_id = bs.batch_source_id
970 AND bs.name = p_batch_source_name
971 AND bat.name = p_batch_name
972 AND ( p_row_id IS NULL
973 OR bat.rowid <> p_row_id );
974 IF ( l_count <> 0 ) THEN
975 FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
976 APP_EXCEPTION.raise_exception;
977 END IF;
978 --
979 IF PG_DEBUG in ('Y', 'C') THEN
980 arp_util.debug( 'arp_rw_batches_pkg.check_unique_batch_name()-' );
981 END IF;
982 --
983 EXCEPTION
984 WHEN OTHERS THEN
985 IF PG_DEBUG in ('Y', 'C') THEN
986 arp_util.debug(
987 'EXCEPTION: arp_rw_batches_pkg.check_unique_batch_name' );
988 END IF;
989 RAISE;
990 END check_unique_batch_name;
991 --
992 /*===========================================================================+
993 | PROCEDURE |
994 | validate_args_cu_batch_name |
995 | |
996 | DESCRIPTION |
997 | Validate arguments passed to check_unique_batch_name procedure |
998 | |
999 | SCOPE - PRIVATE |
1000 | |
1001 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1002 | arp_util.debug - debug procedure |
1003 | |
1004 | ARGUMENTS : IN: |
1005 | p_batch_source_name - Batch source Name |
1006 | p_batch_name - Batch Name |
1007 | OUT: |
1008 | |
1009 | RETURNS : NONE |
1010 | |
1011 | NOTES - This is an OVERLAODED procedure |
1012 | |
1013 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
1014 | |
1015 +===========================================================================*/
1016 PROCEDURE validate_args_cu_batch_name(
1017 p_batch_source_name IN ar_batch_sources.name%TYPE,
1018 p_batch_name IN ar_batches.name%TYPE ) IS
1019 BEGIN
1020 IF PG_DEBUG in ('Y', 'C') THEN
1021 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()+' );
1022 END IF;
1023 --
1024 IF ( p_batch_source_name is NULL OR p_batch_name is NULL ) THEN
1025 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1026 APP_EXCEPTION.raise_exception;
1027 END IF;
1028 --
1029 IF PG_DEBUG in ('Y', 'C') THEN
1030 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_batch_name()-' );
1031 END IF;
1032 --
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 IF PG_DEBUG in ('Y', 'C') THEN
1036 arp_util.debug(
1037 'EXCEPTION: arp_rw_batches_pkg.validate_args_cu_batch_name' );
1038 END IF;
1039 RAISE;
1040 END validate_args_cu_batch_name;
1041 --
1042 /*===========================================================================+
1043 | PROCEDURE |
1044 | check_unique_media_ref - Check uniqueness of a Media reference |
1045 | |
1046 | DESCRIPTION |
1047 | This function Check uniqueness of Media reference in AR_BATCHES table |
1048 | |
1049 | SCOPE - PUBLIC |
1050 | |
1051 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1052 | arp_util.debug - debug procedure |
1053 | |
1054 | ARGUMENTS : IN: |
1055 | p_row_id - Row ID |
1056 | p_module_name - Module that called this procedure |
1057 | p_module_version - Version of the module that called this |
1058 | procedure |
1059 | OUT: |
1060 | |
1061 | RETURNS : NONE |
1062 | |
1063 | NOTES - |
1064 | |
1065 | MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
1066 +===========================================================================*/
1067 PROCEDURE check_unique_media_ref(
1068 p_row_id IN VARCHAR2,
1069 p_media_ref IN ar_batches.media_reference%TYPE,
1070 p_module_name IN VARCHAR2,
1071 p_module_version IN VARCHAR2 ) IS
1072 l_count NUMBER := 0;
1073 BEGIN
1074 IF PG_DEBUG in ('Y', 'C') THEN
1075 arp_util.debug( 'arp_rw_batches_pkg.check_unique_media_ref()+' );
1076 arp_util.debug('check_unique_media_ref: ' || 'Row Id : '||p_row_id );
1077 arp_util.debug('check_unique_media_ref: ' || 'Media Reference : '||p_media_ref );
1078 END IF;
1079 --
1080 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
1081 validate_args_cu_media_ref( p_media_ref );
1082 END IF;
1083 --
1084 SELECT COUNT(*)
1085 INTO l_count
1086 FROM AR_BATCHES bat
1087 WHERE bat.media_reference = p_media_ref
1088 AND ( p_row_id IS NULL
1089 OR bat.rowid <> p_row_id );
1090 IF ( l_count <> 0 ) THEN
1091 FND_MESSAGE.set_name( 'AR', 'AR_DUP_MEDIA_REFERENCE' );
1092 APP_EXCEPTION.raise_exception;
1093 END IF;
1094 --
1095 IF PG_DEBUG in ('Y', 'C') THEN
1096 arp_util.debug( 'arp_rw_batches_pkg.check_unique_media_ref()-' );
1097 END IF;
1098 --
1099 EXCEPTION
1100 WHEN OTHERS THEN
1101 IF PG_DEBUG in ('Y', 'C') THEN
1102 arp_util.debug('check_unique_media_ref: ' ||
1103 'EXCEPTION: arp_rw_batches_pkg.check_unique_media_ref' );
1104 END IF;
1105 RAISE;
1106 END check_unique_media_ref;
1107 --
1108 /*===========================================================================+
1109 | PROCEDURE |
1110 | validate_args_cu_media_ref |
1111 | |
1112 | DESCRIPTION |
1113 | Validate arguments passed to check_unique_media_ref procedure |
1114 | |
1115 | SCOPE - PRIVATE |
1116 | |
1117 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1118 | arp_util.debug - debug procedure |
1119 | |
1120 | ARGUMENTS : IN: |
1121 | p_media_ref - Media reference |
1122 | OUT: |
1123 | |
1124 | RETURNS : NONE |
1125 | |
1126 | NOTES - This is an overlaoded procedure |
1127 | |
1128 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
1129 | |
1130 +===========================================================================*/
1131 PROCEDURE validate_args_cu_media_ref(
1132 p_media_ref IN ar_batches.media_reference%TYPE ) IS
1133 BEGIN
1134 IF PG_DEBUG in ('Y', 'C') THEN
1135 arp_util.debug( 'arp_rw_batches_pkg.validate_args_cu_media_ref()+' );
1136 END IF;
1137 --
1138 IF ( p_media_ref is NULL ) THEN
1139 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
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.validate_args_cu_media_ref()-' );
1145 END IF;
1146 --
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 IF PG_DEBUG in ('Y', 'C') THEN
1150 arp_util.debug('validate_args_cu_media_ref: ' ||
1151 'EXCEPTION: arp_rw_batches_pkg.validate_args_cu_media_ref' );
1152 END IF;
1153 RAISE;
1154 END validate_args_cu_media_ref;
1155 --
1156 /*===========================================================================+
1157 | PROCEDURE |
1158 | post_batch_conc_req - Starts the post batch conc. request. |
1159 | |
1160 | DESCRIPTION |
1161 | This procedure starts the post batch conc. request. |
1162 | |
1163 | SCOPE - PUBLIC |
1164 | |
1165 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1166 | arp_util.debug - debug procedure |
1167 | |
1168 | ARGUMENTS : IN: |
1169 | p_batch_id - Batch Id associated with the batch |
1170 | p_set_of_books_id - set of books id |
1171 | p_transmission_id - Transmission Id if batch was created |
1172 | thro' Lockbox. |
1173 | p_module_name - Module name that called this procedure |
1174 | p_module_version - Version of the module that called this |
1175 | procedure |
1176 | OUT: |
1177 | |
1178 | RETURNS : NONE |
1179 | |
1180 | NOTES - |
1181 | |
1182 | MODIFICATION HISTORY - 11/22/95 - Created by Ganesh Vaidee |
1183 | 18-JAN-96 scleung Added the p_set_of_books_id argument. |
1184 +===========================================================================*/
1185 PROCEDURE post_batch_conc_req( p_batch_id IN ar_batches.batch_id%TYPE,
1186 p_set_of_books_id IN
1187 ar_batches.set_of_books_id%TYPE,
1188 p_transmission_id IN
1189 ar_batches.transmission_id%TYPE,
1190 p_batch_applied_status OUT NOCOPY
1191 ar_batches.batch_applied_status%TYPE,
1192 p_request_id OUT NOCOPY ar_batches.request_id%TYPE,
1193 p_module_name IN VARCHAR2,
1194 p_module_version IN VARCHAR2 ) IS
1195 --
1196 l_request_id ar_batches.request_id%TYPE;
1197 l_org_id number;
1198 BEGIN
1199 IF PG_DEBUG in ('Y', 'C') THEN
1200 arp_standard.debug( 'arp_rw_batches_check_pkg.post_batch_conc_req()+');
1201 END IF;
1202 --
1203 IF PG_DEBUG in ('Y', 'C') THEN
1204 arp_standard.debug( 'Batch Id '||p_batch_id );
1205 arp_standard.debug( 'Set Of Books Id '||p_set_of_books_id );
1206 arp_standard.debug( 'Transmission_id = '||p_transmission_id );
1207 END IF;
1208 --
1209 -- Make sure that a batch id has been passed.
1210 --
1211 IF ( p_batch_id IS NULL ) THEN
1212 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1213 APP_EXCEPTION.raise_exception;
1214 END IF;
1215 --
1216 -- Call the concurrent program
1217 --
1218 --MOAC changes
1219 select org_id into l_org_id from ar_system_parameters;
1220
1221 FND_REQUEST.SET_ORG_ID(l_org_id);
1222 l_request_id := FND_REQUEST.submit_request( 'AR', 'ARCABP',
1223 'Submit Post Batch',
1224 SYSDATE, FALSE,
1225 '1',
1226 p_batch_id,
1227 p_set_of_books_id,
1228 0,
1229 0,
1230 p_transmission_id ,
1231 ARP_GLOBAL.sysparam.ORG_ID);
1232 --
1233 p_request_id := l_request_id;
1234 p_batch_applied_status := 'IN_PROCESS';
1235 --
1236 IF PG_DEBUG in ('Y', 'C') THEN
1237 arp_standard.debug( 'arp_rw_batches_check_pkg.post_batch_conc_req()-');
1238 END IF;
1239 --
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 IF PG_DEBUG in ('Y', 'C') THEN
1243 arp_standard.debug(
1244 'EXCEPTION: arp_rw_batches_check_pkg.post_batch_conc_req' );
1245 END IF;
1246 RAISE;
1247 --
1248 END post_batch_conc_req;
1249 --
1250 /*===========================================================================+
1251 | PROCEDURE |
1252 | get_quick_amount_totals - gets the applied,unapplied,unid. amounts |
1253 | and totals |
1254 | |
1255 | DESCRIPTION |
1256 | This procedure gets the applied,unapplied,unid. amounts and totals |
1257 | |
1258 | SCOPE - PUBLIC |
1259 | |
1260 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1261 | arp_util.debug - debug procedure |
1262 | |
1263 | ARGUMENTS : IN: |
1264 | p_batch_id - Batch Id associated with the batch |
1265 | p_module_name - Module name that called this procedure |
1266 | p_module_version - Version of the module that called this |
1267 | procedure |
1268 | OUT: |
1269 | p_applied_amount_total - applied Amount total |
1270 | p_applied_count_total - applied Amount count |
1271 | |
1272 | RETURNS : NONE |
1273 | |
1274 | NOTES - |
1275 | |
1276 | MODIFICATION HISTORY - 11/28/95 - Created by Ganesh Vaidee |
1277 | 29-JAN-96 Simon Leung Fixed the applied_amount/count logics. |
1278 | 01-APR-96 Simon Leung Added debug message l_break_point. |
1279 | 15-OCT-98 Karen Murphy Cross Currency Lockbox. Modified select |
1280 | statements that look at payment_amount in |
1281 | interim cash receipt lines. Need to consider|
1282 | the amount applied from column as this stores|
1283 | the amount in receipt currency for cross |
1284 | currency applications. |
1285 | 01-JUN-01 Muthuraman. R Added an NVL condition so that Unapplied |
1286 | Receipt counts are correct prior to |
1287 | postquick cash in receipt batches window. |
1288 | 07-JAN-03 K Dhaliwal Bug 2707190 added Claim Amount and Count |
1289 | to get_quick_amount_totals |
1290 +===========================================================================*/
1291 PROCEDURE get_quick_amount_totals( p_batch_id IN ar_batches.batch_id%TYPE,
1292 p_actual_amount_total OUT NOCOPY NUMBER,
1293 p_actual_count_total OUT NOCOPY NUMBER,
1294 p_unidentified_amount_total OUT NOCOPY NUMBER,
1295 p_unidentified_count_total OUT NOCOPY NUMBER,
1296 p_on_account_amount_total OUT NOCOPY NUMBER,
1297 p_on_account_count_total OUT NOCOPY NUMBER,
1298 p_unapplied_amount_total OUT NOCOPY NUMBER,
1299 p_unapplied_count_total OUT NOCOPY NUMBER,
1300 p_applied_amount_total OUT NOCOPY NUMBER,
1301 p_applied_count_total OUT NOCOPY NUMBER,
1302 p_claim_amount_total OUT NOCOPY NUMBER,
1303 p_claim_count_total OUT NOCOPY NUMBER,
1304 p_module_name IN VARCHAR2,
1305 p_module_version IN VARCHAR2 ) IS
1306 --
1307 l_unapplied_amount_total NUMBER := 0;
1308 l_unapplied_count_total NUMBER := 0;
1309 l_applied_amount_total NUMBER := 0;
1310 l_applied_count_total NUMBER := 0;
1311 l_actual_amount_total NUMBER := 0;
1312 l_actual_count_total NUMBER := 0;
1313 l_claim_amount_total_header NUMBER := 0;
1314 l_claim_amount_total_lines NUMBER := 0;
1315 l_onacct_amount_total_header NUMBER := 0;
1316 l_onacct_amount_total_lines NUMBER := 0;
1317 l_break_point VARCHAR2(20);
1318 l_claim_count_total_header NUMBER := 0;
1319 l_claim_count_total_lines NUMBER := 0;
1320 l_onacct_count_total_header NUMBER := 0;
1321 l_onacct_count_total_lines NUMBER := 0;
1322 --
1323 BEGIN
1324 IF PG_DEBUG in ('Y', 'C') THEN
1325 arp_standard.debug( 'arp_rw_batches_check_pkg.get_quick_amount_totals()+');
1326 END IF;
1327 --
1328 IF PG_DEBUG in ('Y', 'C') THEN
1329 arp_standard.debug('get_quick_amount_totals: ' || 'Batch Id '||p_batch_id );
1330 END IF;
1331 --
1332 IF ( p_batch_id IS NULL ) THEN
1333 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1334 APP_EXCEPTION.raise_exception;
1335 END IF;
1336 --
1337 l_break_point := '1st SELECT';
1338 -- Bug #443266
1339 -- Modified this query so that this shows correct on-account
1340 -- receipt count.
1341 --
1342 -- Bug #1560911 mramanat 15/01/2001
1343 -- Added NVL Clause for calculation of applied amount and applied count
1344 -- so that these show up correctly for receipts imported through lockbox.
1345
1346 SELECT SUM( NVL( icr.amount, 0 ) ), COUNT(*),
1347 NVL( SUM( DECODE( icr.special_type,
1348 'CLAIM', icr.amount,
1349 0
1350 )
1351 ), 0
1352 ),
1353 NVL( COUNT( DECODE( icr.special_type,
1354 'CLAIM', 1,
1355 ''
1356 )
1357 ), 0
1358 ),
1359 NVL( SUM( DECODE( icr.special_type,
1360 'ON_ACCOUNT', icr.amount,
1361 0
1362 )
1363 ), 0
1364 ),
1365 NVL( COUNT( DECODE( icr.special_type,
1366 'ON_ACCOUNT', 1,
1367 ''
1368 )
1369 ), 0
1370 ),
1371
1372 NVL( SUM( DECODE( NVL(icr.special_type,'SINGLE'),
1373 'RECEIPT_RULE', icr.amount,
1374 'SINGLE', icr.amount,
1375 0
1376 )
1377 ), 0
1378 ),
1379 NVL( COUNT( DECODE( NVL(icr.special_type,'SINGLE'),
1380 'RECEIPT_RULE', 1,
1381 'SINGLE', 1,
1382 ''
1383 )
1384 ), 0
1385 ),
1386
1387 NVL( SUM( DECODE( icr.special_type,
1388 'UNAPPLIED', icr.amount,
1389 'MULTIPLE', icr.amount,
1390 0
1391 )
1392 ), 0
1393 ),
1394 NVL( COUNT( DECODE( icr.special_type,
1395 'UNAPPLIED', 1,
1396 ''
1397 )
1398 ), 0
1399 ),
1400 NVL( SUM( DECODE( icr.special_type,
1401 'UNIDENTIFIED', icr.amount,
1402 0
1403 )
1404 ), 0
1405 ),
1406 NVL( COUNT( DECODE( icr.special_type,
1407 'UNIDENTIFIED', 1,
1408 ''
1409 )
1410 ), 0
1411 )
1412 INTO l_actual_amount_total,
1413 l_actual_count_total,
1414 l_claim_amount_total_header,
1415 l_claim_count_total_header,
1416 l_onacct_amount_total_header,
1417 l_onacct_count_total_header,
1418 l_applied_amount_total,
1419 l_applied_count_total,
1420 l_unapplied_amount_total,
1421 l_unapplied_count_total,
1422 p_unidentified_amount_total,
1423 p_unidentified_count_total
1424 FROM ar_interim_cash_receipts icr
1425 WHERE icr.batch_id = p_batch_id;
1426 --
1427 --
1428 --
1429 l_break_point := '2nd SELECT';
1430 SELECT l_applied_amount_total + NVL(SUM( nvl(icrl.amount_applied_from, icrl.payment_amount) ),0),
1431 l_unapplied_amount_total - NVL(SUM( nvl(icrl.amount_applied_from, icrl.payment_amount) ),0),
1432 NVL( SUM(decode(icrl.payment_schedule_id,-4,nvl(icrl.amount_applied_from, icrl.payment_amount),0 )),0),
1433 NVL( SUM(decode(icrl.payment_schedule_id,-1,nvl(icrl.amount_applied_from, icrl.payment_amount),0 )),0)
1434 INTO l_applied_amount_total,
1435 l_unapplied_amount_total,
1436 l_claim_amount_total_lines,
1437 l_onacct_amount_total_lines
1438 FROM ar_interim_cash_receipts icr,
1439 ar_interim_cash_receipt_lines icrl
1440 WHERE icrl.cash_receipt_id = icr.cash_receipt_id
1441 AND icr.batch_id = p_batch_id;
1442 --
1443 -- Add to unapplied count if all amounts of receipt has not been paid off
1444 --
1445
1446 /* 01-Jun-2001 Bugfix 1773585.
1447 Added an NVL condition so that Unapplied Receipt counts are correct
1448 prior to postquick cash in receipt batches window.
1449 */
1450
1451 l_break_point := '3rd SELECT';
1452 SELECT NVL(l_applied_count_total,0) +
1453 COUNT( DECODE( SIGN(rec.amount - SUM( nvl(ln.amount_applied_from, ln.payment_amount) ) ),
1454 0, 1, '' )),
1455 NVL(l_unapplied_count_total,0) +
1456 COUNT( DECODE( SIGN(rec.amount - NVL( SUM( nvl(ln.amount_applied_from, ln.payment_amount) ) , 0 )),
1457 1, 1, '' ))
1458 INTO l_applied_count_total,
1459 l_unapplied_count_total
1460 FROM ar_interim_cash_receipt_lines ln,
1461 ar_interim_cash_receipts rec
1462 WHERE rec.batch_id = p_batch_id
1463 AND rec.cash_receipt_id = ln.cash_receipt_id
1464 GROUP BY
1465 rec.cash_receipt_id,
1466 rec.amount;
1467
1468 l_break_point := '4th SELECT';
1469 SELECT sum(sign(CLAIM)) Claims_Count
1470 ,sum(sign(ON_ACC)) On_Account_Count
1471 ,sum(nvl(TRX,0)) + nvl(l_applied_count_total,0) Trx_Count
1472 INTO l_claim_count_total_lines
1473 ,l_onacct_count_total_lines
1474 ,l_applied_count_total
1475 FROM (select sum(decode(payment_schedule_id,-4,1,0)) CLAIM
1476 , SUM(decode(payment_schedule_id,-1,1,0)) ON_ACC
1477 , sum(decode(sign(payment_schedule_id),1,1,0)) TRX
1478 from ar_interim_cash_receipt_lines ln
1479 where ln.batch_id = p_batch_id
1480 );
1481
1482 --
1483 -- Copy local valued to OUT NOCOPY parameters
1484 --
1485 p_claim_amount_total := nvl(l_claim_amount_total_header,0) + nvl(l_claim_amount_total_lines,0);
1486 p_on_account_amount_total := nvl(l_onacct_amount_total_header,0) + nvl(l_onacct_amount_total_lines,0);
1487 p_claim_count_total := nvl(l_claim_count_total_header,0) + nvl(l_claim_count_total_lines,0);
1488 p_on_account_count_total := nvl(l_onacct_count_total_header,0) + nvl(l_onacct_count_total_lines,0);
1489 --
1490 p_actual_amount_total := nvl(l_actual_amount_total,0);
1491 p_actual_count_total := nvl(l_actual_count_total,0);
1492 --
1493 p_applied_amount_total := nvl(l_applied_amount_total,0) - nvl(l_claim_amount_total_lines ,0)- nvl(l_onacct_amount_total_lines,0);
1494 p_applied_count_total := nvl(l_applied_count_total,0);
1495 --
1496 p_unapplied_amount_total := nvl(l_unapplied_amount_total,0);
1497 p_unapplied_count_total := nvl(l_unapplied_count_total,0);
1498 --
1499 IF PG_DEBUG in ('Y', 'C') THEN
1500 arp_standard.debug('Get Quick Cash Totals - Claim Count Header='||to_char(l_claim_count_total_header));
1501 arp_standard.debug('Get Quick Cash Totals - Claim Count Lines='||to_char(l_claim_count_total_lines));
1502 arp_standard.debug('Get Quick Cash Totals - onacct Count Header='||to_char(l_onacct_count_total_header));
1503 arp_standard.debug('Get Quick Cash Totals - Onacct Count Lines='||to_char(l_onacct_count_total_lines));
1504 arp_standard.debug('Get Quick Cash Totals - Claim Count='||to_char(p_claim_count_total));
1505 arp_standard.debug( 'arp_rw_batches_check_pkg.get_quick_amount_totals()-');
1506 END IF;
1507 --
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510 IF PG_DEBUG in ('Y', 'C') THEN
1511 arp_standard.debug('get_quick_amount_totals: ' ||
1512 'EXCEPTION: arp_rw_batches_check_pkg.get_quick_amount_totals '||
1513 l_break_point );
1514 END IF;
1515 RAISE;
1516 --
1517 END get_quick_amount_totals;
1518 --
1519
1520 PROCEDURE get_reg_amount_totals( p_batch_id IN ar_batches.batch_id%TYPE,
1521 p_actual_amount_total OUT NOCOPY NUMBER,
1522 p_actual_count_total OUT NOCOPY NUMBER,
1523 p_unidentified_amount_total OUT NOCOPY NUMBER,
1524 p_unidentified_count_total OUT NOCOPY NUMBER,
1525 p_on_account_amount_total OUT NOCOPY NUMBER,
1526 p_on_account_count_total OUT NOCOPY NUMBER,
1527 p_returned_amount_total OUT NOCOPY NUMBER,
1528 p_returned_count_total OUT NOCOPY NUMBER,
1529 p_reversed_amount_total OUT NOCOPY NUMBER,
1530 p_reversed_count_total OUT NOCOPY NUMBER,
1531 p_unapplied_amount_total OUT NOCOPY NUMBER,
1532 p_unapplied_count_total OUT NOCOPY NUMBER,
1533 p_applied_amount_total OUT NOCOPY NUMBER,
1534 p_applied_count_total OUT NOCOPY NUMBER,
1535 p_claim_amount_total OUT NOCOPY NUMBER,
1536 p_claim_count_total OUT NOCOPY NUMBER,
1537 p_prepayment_amount_total OUT NOCOPY NUMBER,
1538 p_prepayment_count_total OUT NOCOPY NUMBER,
1539 p_misc_amount_total OUT NOCOPY NUMBER,
1540 p_misc_count_total OUT NOCOPY NUMBER,
1541 p_module_name IN VARCHAR2,
1542 p_module_version IN VARCHAR2 ) IS
1543 --
1544 l_unapplied_amount_total NUMBER := 0;
1545 l_unapplied_count_total NUMBER := 0;
1546 l_applied_amount_total NUMBER := 0;
1547 l_applied_count_total NUMBER := 0;
1548 l_actual_amount_total NUMBER := 0;
1549 l_actual_count_total NUMBER := 0;
1550 l_on_account_count_total NUMBER :=0;
1551 l_on_account_amount_total NUMBER :=0;
1552 l_claim_amount_total NUMBER := 0;
1553 l_claim_count_total NUMBER := 0;
1554 l_prepayment_amount_total NUMBER := 0;
1555 l_prepayment_count_total NUMBER := 0;
1556 l_misc_count_total NUMBER := 0;
1557 l_misc_amount_total NUMBER := 0;
1558 l_break_point VARCHAR2(20);
1559 --
1560 BEGIN
1561 IF PG_DEBUG in ('Y', 'C') THEN
1562 arp_standard.debug( 'arp_rw_batches_check_pkg.get_reg_amount_totals()+');
1563 END IF;
1564 --
1565 IF PG_DEBUG in ('Y', 'C') THEN
1566 arp_standard.debug('get_reg_amount_totals: ' || 'Batch Id '||p_batch_id );
1567 END IF;
1568 --
1569 IF ( p_batch_id IS NULL ) THEN
1570 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1571 APP_EXCEPTION.raise_exception;
1572 END IF;
1573 --
1574 --
1575 -- Shiv Ragunat - 10/22/96
1576 -- As part of fix for Bug 398344
1577 -- Actual amount now is sum(cr.amount) for that batch_id
1578 -- Actual Count is count(cr.status) for that batch_id.
1579 --
1580 --
1581 l_break_point := '1st SELECT';
1582 -- Fixed following query for Bug #435632.
1583 --
1584 SELECT NVL( SUM( cr.amount ),0),
1585 NVL( COUNT( cr.status ) ,0 ),
1586 NVL( SUM(DECODE( cr.status, 'NSF', cr.amount, 'STOP', cr.amount,0)),0),
1587 NVL( COUNT(DECODE( cr.status, 'NSF', 1, 'STOP', 1,'')),0),
1588 NVL( SUM(DECODE( cr.status, 'REV', cr.amount, 0)), 0),
1589 NVL( COUNT(DECODE( cr.status, 'REV', 1, '')), 0),
1590 NVL( COUNT(DECODE( cr.status , 'UNAPP', 1, '')),0),
1591 NVL(COUNT (DECODE(cr.status,
1592 'APP', DECODE( cr.type, 'MISC', '', 1),
1593 '')), 0),
1594 NVL( SUM( DECODE( cr.status, 'UNID', cr.amount,0)), 0),
1595 NVL( COUNT(DECODE( cr.status , 'UNID', 1, '')),0),
1596 NVL( SUM( DECODE( cr.type,
1597 'MISC', DECODE( cr.status, 'REV', 0,
1598 'NSF', 0,
1599 'STOP', 0, cr.amount),
1600 0 )), 0),
1601 NVL( COUNT(DECODE( cr.type,
1602 'MISC', DECODE( cr.status, 'REV', '',
1603 'NSF', '',
1604 'STOP', '', 1),
1605 '')), 0)
1606 INTO l_actual_amount_total,
1607 l_actual_count_total,
1608 p_returned_amount_total,
1609 p_returned_count_total,
1610 p_reversed_amount_total,
1611 p_reversed_count_total,
1612 l_unapplied_count_total,
1613 l_applied_count_total,
1614 p_unidentified_amount_total,
1615 p_unidentified_count_total,
1616 l_misc_amount_total,
1617 l_misc_count_total
1618 FROM ar_cash_receipts cr,
1619 ar_cash_receipt_history crh
1620 WHERE cr.cash_receipt_id = crh.cash_receipt_id
1621 AND crh.first_posted_record_flag = 'Y'
1622 AND crh.batch_id = p_batch_id;
1623 --
1624 --
1625 --
1626
1627 l_break_point := '2nd SELECT';
1628 SELECT NVL(SUM(DECODE(ra.STATUS, 'APP', nvl(ra.amount_applied_from, ra.amount_applied),
1629 'ACTIVITY',--Added for bug 1647470
1630 DECODE(ra.applied_payment_schedule_id,
1631 -3,ra.amount_applied
1632 ,Decode(ra.RECEIVABLES_TRX_ID,-16,ra.amount_applied,0)),
1633 0)),0),
1634 NVL( SUM( DECODE( ra.status, 'UNAPP', ra.amount_applied,0)), 0),
1635 NVL(SUM(DECODE(ra.STATUS, 'ACC', ra.amount_applied, 0)),0),
1636 NVL(SUM(DECODE(ra.STATUS, 'OTHER ACC',DECODE(applied_payment_schedule_id,
1637 -4,NVL(ra.amount_applied_from, ra.amount_applied),0),0)),0),
1638 NVL(SUM(DECODE(ra.STATUS, 'OTHER ACC',DECODE(applied_payment_schedule_id,
1639 -7,NVL(ra.amount_applied_from, ra.amount_applied),0),0)),0)
1640 INTO l_applied_amount_total,
1641 l_unapplied_amount_total,
1642 l_on_account_amount_total,
1643 l_claim_amount_total,
1644 l_prepayment_amount_total
1645 FROM ar_receivable_applications ra,
1646 ar_cash_receipt_history crh
1647 WHERE ra.cash_receipt_id = crh.cash_receipt_id
1648 AND crh.first_posted_record_flag = 'Y'
1649 AND crh.batch_id = p_batch_id;
1650 --
1651 -- Added this query for Bug #443266.
1652 -- Get the receipt counts for the fully on-account receipts.
1653 --
1654 l_break_point := '3rd SELECT';
1655 --
1656 Begin
1657 --
1658 SELECT nvl(count(cr.cash_receipt_id), 0)
1659 INTO l_on_account_count_total
1660 FROM ar_cash_receipts cr,
1661 ar_cash_receipt_history crh
1662 WHERE crh.cash_receipt_id = cr.cash_receipt_id
1663 AND crh.first_posted_record_flag = 'Y'
1664 AND crh.batch_id = p_batch_id
1665 AND cr.amount = (SELECT sum(ra.amount_applied)
1666 FROM ar_receivable_applications ra
1667 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1668 AND ra.status = 'ACC'
1669 AND ra.display = 'Y'); -- Fix 1178963
1670 --
1671 exception
1672 when no_data_found then
1673 l_on_account_count_total := 0;
1674 when others then
1675 raise;
1676 End;
1677 --
1678 /* jbeckett 04-apr-01 following query added for deductions */
1679 --
1680 -- Get the receipt count for receipts that are under claim investigation.
1681 --
1682 -- Bug 1811239 - only receipts totally applied to claim investigation
1683
1684
1685 l_break_point := '4th SELECT';
1686 --
1687 Begin
1688 --
1689 --Bug 2645671-Show the count only when the whole receipt is applied to claim
1690 -- Bug 3590163: disabled index on applied_payment_schedule_id
1691
1692 SELECT nvl(count(cr.cash_receipt_id), 0)
1693 INTO l_claim_count_total
1694 FROM ar_cash_receipts cr,
1695 ar_cash_receipt_history crh
1696 WHERE crh.cash_receipt_id = cr.cash_receipt_id
1697 AND crh.first_posted_record_flag = 'Y'
1698 AND crh.batch_id = p_batch_id
1699 AND cr.amount = (SELECT
1700 sum(ra.amount_applied)
1701 FROM ar_receivable_applications ra
1702 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1703 AND ra.status = 'OTHER ACC'
1704 AND ra.applied_payment_schedule_id + 0 = -4
1705 AND ra.display = 'Y');
1706 --
1707 exception
1708 when no_data_found then
1709 l_claim_count_total := 0;
1710 when others then
1711 raise;
1712 End;
1713
1714 l_break_point := '5th SELECT';
1715 --
1716 Begin
1717 --
1718 --Bug 2645671-Show the count only when the whole receipt is applied to prepayment
1719 -- Bug 3590163: disabled index on applied_payment_schedule_id
1720
1721 SELECT nvl(count(cr.cash_receipt_id), 0)
1722 INTO l_prepayment_count_total
1723 FROM ar_cash_receipts cr,
1724 ar_cash_receipt_history crh
1725 WHERE crh.cash_receipt_id = cr.cash_receipt_id
1726 AND crh.first_posted_record_flag = 'Y'
1727 AND crh.batch_id = p_batch_id
1728 AND cr.amount = (SELECT
1729 sum(ra.amount_applied)
1730 FROM ar_receivable_applications ra
1731 WHERE ra.cash_receipt_id = cr.cash_receipt_id
1732 AND ra.status = 'OTHER ACC'
1733 AND ra.applied_payment_schedule_id + 0 = -7
1734 AND ra.display = 'Y');
1735 --
1736 exception
1737 when no_data_found then
1738 l_prepayment_count_total := 0;
1739 when others then
1740 raise;
1741 end;
1742 --
1743 --
1744 -- Copy local valued to OUT NOCOPY pacrmeters
1745 --
1746 p_actual_amount_total := l_actual_amount_total;
1747 p_actual_count_total := l_actual_count_total;
1748 --
1749 p_applied_amount_total := l_applied_amount_total;
1750 -- Bug #443266
1751 -- Reduced the value by l_on_account_count_total, as
1752 -- p_applied_count_total also shows the fully on-account receipts.
1753 --
1754 p_applied_count_total := (l_applied_count_total - l_on_account_count_total
1755 - l_claim_count_total);
1756 --
1757 p_unapplied_amount_total := l_unapplied_amount_total;
1758 p_unapplied_count_total := l_unapplied_count_total;
1759 --
1760 p_on_account_amount_total := l_on_account_amount_total;
1761 -- Bug #443266
1762 -- Earlier p_on_account_count_total was assigned value zero,
1763 -- Now it shows the count of fully on-account receipts.
1764 p_on_account_count_total := l_on_account_count_total;
1765 --
1766 p_claim_amount_total := l_claim_amount_total;
1767 p_claim_count_total := l_claim_count_total;
1768 --
1769 p_prepayment_amount_total := l_prepayment_amount_total;
1770 p_prepayment_count_total := l_prepayment_count_total;
1771 --
1772 p_misc_amount_total := l_misc_amount_total;
1773 p_misc_count_total := l_misc_count_total;
1774 --
1775 IF PG_DEBUG in ('Y', 'C') THEN
1776 arp_standard.debug( 'arp_rw_batches_check_pkg.get_reg_amount_totals()-');
1777 END IF;
1778 --
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781 IF PG_DEBUG in ('Y', 'C') THEN
1782 arp_standard.debug('get_reg_amount_totals: ' ||
1783 'EXCEPTION: arp_rw_batches_check_pkg.get_reg_amount_totals '||
1784 l_break_point );
1785 END IF;
1786 RAISE;
1787 --
1788 END get_reg_amount_totals;
1789
1790 END ARP_RW_BATCHES_CHECK_PKG;