[Home] [Help]
PACKAGE BODY: APPS.AR_POSTBATCH_PARALLEL
Source
1 PACKAGE BODY AR_POSTBATCH_PARALLEL AS
2 /* $Header: ARPBMPB.pls 120.0.12010000.3 2008/11/12 14:40:27 mgaleti noship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4 G_ERROR varchar2(1) := 'N';
5 G_USER_ID number;
6 G_CONC_PROGRAM_ID number;
7 G_CONC_REQUEST_ID number;
8 G_PROG_APPL_ID number;
9
10 /*===========================================================================+
11 | PROCEDURE |
12 | submit_postbatch_parallel() - Submit child requests for the processing |
13 | of postbatch through submit_subrequest(). It makes a |
14 | call to update_batch_after_process() to update the |
15 | batch status after all the child requests are completed|
16 | DESCRIPTION |
17 | Submits child requests. |
18 | |
19 | SCOPE - PUBLIC |
20 | |
21 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
22 | arp_util.debug - debug procedure |
23 | arp_standard.debug() - debug procedure |
24 | FND_REQUEST.wait_for_request |
25 | ARGUMENTS : IN: |
26 | p_org_id - Org ID |
27 | p_batch_id - Batch Id |
28 | p_transmission_id - Lockbox transmission ID |
29 | p_total_workers - Number of workers |
30 | |
31 | OUT: P_ERRBUF |
32 | P_RETCODE |
33 | RETURNS : NONE |
34 | |
35 | NOTES - |
36 | |
37 | MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
38 | 01/02/2008 - Modified parameter list. |
39 +===========================================================================*/
40
41 PROCEDURE submit_postbatch_parallel(
42 P_ERRBUF OUT NOCOPY VARCHAR2,
43 P_RETCODE OUT NOCOPY NUMBER,
44 p_org_id IN NUMBER,
45 p_batch_id IN NUMBER,
46 p_transmission_id IN NUMBER,
47 p_total_workers IN NUMBER DEFAULT 1 ) AS
48
49 l_worker_number NUMBER ;
50 l_complete BOOLEAN := FALSE;
51 l_batch_applied_status ar_batches.batch_applied_status%TYPE := 'POSTBATCH_WAITING';
52 l_ct_cnt NUMBER;
53 l_ct_amt NUMBER;
54 l_locked_status VARCHAR2(10);
55 l_batch_id ar_batches.batch_id%TYPE;
56 l_excep_code NUMBER(2);
57 l_matched_claim_creation VARCHAR2(2);
58 l_matched_claim_excl_cm VARCHAR2(2);
59 l_return_status VARCHAR2(1);
60 l_status ar_batches.status%TYPE;
61
62 CURSOR qcbatch IS
63 SELECT ab.name batch_name,
64 abs.name batch_source_name,
65 ab.batch_date,
66 ab.gl_date,
67 ab.deposit_date,
68 ab.status,
69 ab.comments,
70 ab.batch_applied_status,
71 ab.control_count,
72 ab.control_amount,
73 cba.bank_account_name,
74 cba.bank_account_num,
75 ab.currency_code,
76 to_number(to_char(ab.gl_date, 'J')),
77 to_number(to_char(ab.deposit_date, 'J'))
78 FROM ar_batches ab,
79 ar_batch_sources abs,
80 ce_bank_accounts cba,
81 ce_bank_acct_uses_all ba
82 WHERE ab.batch_source_id = abs.batch_source_id
83 AND ab.remit_bank_acct_use_id
84 = ba.bank_acct_use_id (+)
85 AND ba.bank_account_id = cba.bank_account_id (+)
86 AND ab.org_id = ba.org_id
87 AND ab.batch_id = p_batch_id;
88
89
90 CURSOR lbbatch IS
91 SELECT ab.name batch_name,
92 abs.name batch_source_name,
93 ab.batch_date,
94 ab.gl_date,
95 ab.deposit_date,
96 ab.status,
97 ab.comments,
98 ab.batch_applied_status,
99 ab.control_count,
100 ab.control_amount,
101 cba.bank_account_name,
102 cba.bank_account_num,
103 ab.currency_code,
104 to_number(to_char(ab.gl_date, 'J')),
105 to_number(to_char(ab.deposit_date, 'J')),
106 ab.batch_id
107 FROM ar_batches ab,
108 ar_batch_sources abs,
109 ce_bank_accounts cba,
110 ce_bank_acct_uses_all ba
111 WHERE ab.batch_source_id = abs.batch_source_id
112 AND ab.remit_bank_acct_use_id
113 = ba.bank_acct_use_id
114 AND cba.bank_account_id = ba.bank_account_id
115 AND ab.batch_applied_status = l_batch_applied_status
116 AND ab.org_id = ba.org_id
117 AND ab.transmission_id = p_transmission_id
118 ORDER BY ab.batch_id;
119
120 lbr lbbatch%ROWTYPE;
121 qcr qcbatch%ROWTYPE;
122
123 TYPE req_status_typ IS RECORD (
124 request_id NUMBER(15),
125 dev_phase VARCHAR2(255),
126 dev_status VARCHAR2(255),
127 message VARCHAR2(2000),
128 phase VARCHAR2(255),
129 status VARCHAR2(255));
130
131
132 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
133
134 l_req_status_tab req_status_tab_typ;
135
136 /*===========================================================================+
137 | PROCEDURE |
138 | submit_subrequest() - This process submits Postbatch process |
139 | DESCRIPTION |
140 | Submits postbatch requests. |
141 | |
142 | SCOPE - |
143 | |
144 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
145 | arp_util.debug - debug procedure |
146 | FND_REQUEST.submit_request |
147 | ARGUMENTS : IN: |
148 | p_worker_number - Worker Number |
152 | RETURNS : NONE |
149 | p_org_id - Org_id |
150 | |
151 | OUT: None |
153 | |
154 | NOTES - |
155 | |
156 | MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
157 +===========================================================================*/
158
159 PROCEDURE submit_subrequest (p_worker_number IN NUMBER,
160 p_org_id IN NUMBER) IS
161 l_request_id NUMBER(15);
162 BEGIN
163 fnd_file.put_line(FND_FILE.LOG, 'submit_subrequest()+');
164
165 FND_REQUEST.SET_ORG_ID(p_org_id);
166
167 l_request_id := FND_REQUEST.submit_request( 'AR', 'ARCABP',
168 'Submit Post Batch',
169 SYSDATE,
170 FALSE,
171 '1',
172 NVL(p_batch_id, -1),
173 arp_standard.sysparm.set_of_books_id,
174 p_worker_number,
175 p_total_workers,
176 NVL(p_transmission_id, -1),
177 p_org_id);
178
179 IF (l_request_id = 0) THEN
180 arp_util.debug('Can not start for worker_id: ' ||p_worker_number );
181 P_ERRBUF := fnd_Message.get;
182 P_RETCODE := 2;
183 return;
184 ELSE
185 commit;
186 arp_util.debug('child request id: ' ||l_request_id || ' started for worker_id: ' ||p_worker_number );
187 END IF;
188
189 l_req_status_tab(p_worker_number).request_id := l_request_id;
190 arp_util.debug('submit_subrequest()-');
191
192 END submit_subrequest;
193
194 /*===========================================================================+
195 | PROCEDURE |
196 | update_batch_after_process() -This process updates the batch status |
197 | after all the receipts in the batch are processed by ARCABP. |
198 | DESCRIPTION |
199 | Updates the batch_applied_status of the batch to "PROCESSED' |
200 | and status to 'CL'/'OP' |
201 | SCOPE - |
202 | |
203 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
204 | |
205 | ARGUMENTS : IN: |
206 | p_batch_id - Batch ID |
207 | p_batch_applied_status - Batch Applied Status |
208 | p_ct_cnt - Control Count |
209 | p_ct_amt - Control Amount |
210 | OUT: None |
211 | RETURNS : NONE |
212 | |
213 | NOTES - |
214 | |
215 | MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
216 +===========================================================================*/
217
218
219 PROCEDURE update_batch_after_process( p_batch_id ar_batches.batch_id%TYPE,
220 p_batch_applied_status ar_batches.batch_applied_status%TYPE,
221 p_ct_cnt NUMBER,
222 p_ct_amt NUMBER
223 ) AS
224 l_act_app_cnt NUMBER;
225 l_act_app_amt NUMBER;
226 l_err_receipt_cnt NUMBER;
227 BEGIN
228 fnd_file.put_line( FND_FILE.LOG, 'Process_batch_for_update()+');
229
230 IF ( p_batch_applied_status <> 'PROCESSED' AND
231 p_batch_applied_status <> 'POSTBATCH_WAITING') THEN
232
233
234 fnd_file.put_line(FND_FILE.LOG, 'Batch_id : '||p_batch_id||' : Control count : '||p_ct_cnt||' : Control Amount : '||p_ct_amt);
235
236 fnd_file.put_line( FND_FILE.LOG, ' Getting the applied count and applied amount');
237
238 SELECT count(*), nvl(sum(cr.amount),0)
239 INTO l_act_app_cnt, l_act_app_amt
240 FROM ar_cash_receipts cr,
241 ar_cash_receipt_history crh
242 WHERE cr.cash_receipt_id = crh.cash_receipt_id
243 AND crh.batch_id = p_batch_id
244 AND cr.status = 'APP';
245
246 IF (( l_act_app_cnt = p_ct_cnt ) AND ( l_act_app_amt = p_ct_amt )) THEN
247 l_status := 'CL';
248 ELSE
249 l_status := 'OP';
250 END IF;
251
252 SELECT COUNT(1)
253 INTO l_err_receipt_cnt
254 FROM ar_interim_cash_receipts
255 WHERE batch_id = p_batch_id;
256
257 IF ( l_err_receipt_cnt = 0) THEN
258 fnd_file.put_line( FND_FILE.LOG, 'Updating batch_applied_status to PROCESSED.' );
259 UPDATE ar_batches
260 SET batch_applied_status = 'PROCESSED',
261 status = l_status,
262 last_updated_by = G_USER_ID,
263 last_update_date = sysdate,
264 program_id = G_CONC_PROGRAM_ID,
265 request_id = G_CONC_REQUEST_ID,
266 program_application_id = G_PROG_APPL_ID,
267 program_update_date = sysdate
268 WHERE batch_id = p_batch_id;
269 ELSE
273
270 fnd_file.put_line( FND_FILE.LOG, 'Updating batch_applied_status to POSTBATCH_WAITING.' );
271 update_batch_for_rerun( l_status, p_batch_id);
272 END IF;
274 commit;
275 fnd_file.put_line( FND_FILE.LOG, 'End of Posting');
276
277 END IF;
278 END update_batch_after_process;
279
280 BEGIN
281 fnd_file.put_line( FND_FILE.LOG, 'submit_postbatch_parallel()+');
282 /* Initialize the global values */
283 G_USER_ID := FND_GLOBAL.user_id;
284 G_CONC_PROGRAM_ID := FND_GLOBAL.conc_program_id;
285 G_CONC_REQUEST_ID := FND_GLOBAL.conc_request_id;
286 G_PROG_APPL_ID := FND_GLOBAL.prog_appl_id;
287
288 mo_global.init('AR');
289
290 IF p_org_id is not null THEN
291 mo_global.set_policy_context('S', p_org_id);
292 arp_standard.init_standard(p_org_id);
293 END IF;
294
295 IF p_batch_id IS NULL AND p_transmission_id IS NULL THEN
296 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
297 APP_EXCEPTION.raise_exception;
298 END IF;
299
300 IF PG_DEBUG in ('Y', 'C') THEN
301 arp_standard.debug( 'Org_id = '||p_org_id );
302 arp_standard.debug( 'Batch Id = '||p_batch_id );
303 arp_standard.debug( 'Transmission_id = '||p_transmission_id );
304 arp_standard.debug( 'Total Workers = '||p_total_workers );
305 END IF;
306
307 -- Validate batches supplied in the parameters.
308 IF NVL(p_transmission_id,-1) <> -1 THEN
309 -- This is a Lockbox batch.
310 FOR lbr IN lbbatch LOOP
311 IF lbr.batch_applied_status = 'PROCESSED' THEN
312 fnd_file.put( FND_FILE.OUTPUT, '***' || lbr.batch_name ||' : ');
313 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get_string('AR', 'ARCABP_BEEN_PROCESSED'));
314 ELSIF lbr.batch_applied_status <> 'POSTBATCH_WAITING' THEN
315 fnd_file.put( FND_FILE.OUTPUT, '***' || lbr.batch_name ||' : ');
316 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get_string('AR', 'ARCABP_NOT_VALID_BATCH'));
317 ELSE
318 SELECT 'locked'
319 INTO l_locked_status
320 FROM ar_batches
321 WHERE batch_id = lbr.batch_id
322 FOR UPDATE OF batch_applied_status, status,
323 last_update_date, last_updated_by;
324 UPDATE ar_batches
325 SET batch_applied_status = 'IN_PROCESS',
326 last_update_date = sysdate,
327 last_updated_by = G_USER_ID,
328 program_id = G_CONC_PROGRAM_ID,
329 request_id = G_CONC_REQUEST_ID,
330 program_application_id = G_PROG_APPL_ID,
331 program_update_date = sysdate
332 WHERE batch_id = lbr.batch_id;
333 l_batch_applied_status := 'IN_PROCESS';
334 END IF;
335 END LOOP;
336 IF l_batch_applied_status <> 'IN_PROCESS' THEN
337 fnd_file.put_line( FND_FILE.OUTPUT, '**** No Batches To Process ****');
338 goto leave_program;
339 END IF;
340 ELSE
341 -- This is a Quick cash batch.
342 FOR qcr IN qcbatch LOOP
343 IF qcr.batch_applied_status = 'PROCESSED' THEN
344 fnd_file.put( FND_FILE.OUTPUT, '***' || qcr.batch_name ||' : ');
345 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get_string('AR', 'ARCABP_BEEN_PROCESSED'));
346 goto leave_program;
347 ELSIF qcr.batch_applied_status <> 'POSTBATCH_WAITING' THEN
348 fnd_file.put( FND_FILE.OUTPUT, '***' || qcr.batch_name ||' : ');
349 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get_string('AR', 'ARCABP_NOT_VALID_BATCH'));
350 goto leave_program;
351 ELSE
352 SELECT 'locked'
353 INTO l_locked_status
354 FROM ar_batches
355 WHERE batch_id = p_batch_id
356 FOR UPDATE OF batch_applied_status, status,
357 last_update_date, last_updated_by;
358 UPDATE ar_batches
359 SET batch_applied_status = 'IN_PROCESS',
360 last_update_date = sysdate,
361 last_updated_by = G_USER_ID,
362 program_id = G_CONC_PROGRAM_ID,
363 request_id = G_CONC_REQUEST_ID,
364 program_application_id = G_PROG_APPL_ID,
365 program_update_date = sysdate
366 WHERE batch_id = p_batch_id;
367 END IF;
368 END LOOP;
369
370 END IF;
371 /* ------------------------------------------------------------- *
372 * Added for Bug 7141803 *
373 * We may need to gather stats on interim tables based on the *
374 * profile option 'AR_LB_QC_GATHER_STATS'. By default stats will *
375 * always be gathered unless the profile option is set to 'NO' *
376 * ------------------------------------------------------------- */
377 IF nvl(fnd_profile.value_specific('AR_LB_QC_GATHER_STATS',
378 G_USER_ID), 'Y') <> 'N' THEN
379 DECLARE
380 l_schema VARCHAR2(30);
381 l_status VARCHAR2(1);
382 l_industry VARCHAR2(1);
383 l_tname1 VARCHAR2(30) := 'AR_INTERIM_CASH_RECEIPTS_ALL';
384 l_tname2 VARCHAR2(30) := 'AR_INTERIM_CASH_RCPT_LINES_ALL';
385 no_product_info exception;
386 BEGIN
387 IF (NOT fnd_installation.get_app_info(
388 application_short_name=>'AR'
389 , status => l_status
390 , industry => l_industry
391 , oracle_schema => l_schema)) THEN
392 fnd_file.put_line(fnd_file.log, 'EXCEPTION:Failed to get information for AR');
393 RAISE no_product_info;
394 END IF;
395
396 fnd_stats.gather_table_stats(ownname=>l_schema,
397 tabname=>l_tname1);
398 fnd_stats.gather_table_stats(ownname=>l_schema,
399 tabname=>l_tname2);
400 EXCEPTION
401 WHEN OTHERS THEN
402 fnd_file.put_line(fnd_file.log, 'Error in Gather stats' || SQLERRM(SQLCODE));
403 RAISE;
404 END;
408 FOR l_worker_number IN 1..p_total_workers LOOP
405 fnd_file.put_line(fnd_file.LOG, 'AR:ARPBMPB Gathered Stats on Interim Tables');
406 END IF;
407 --Invoke the child programs
409 fnd_file.put_line(FND_FILE.LOG, 'worker # : ' || l_worker_number );
410 submit_subrequest (l_worker_number,p_org_id);
411 END LOOP;
412
413 IF PG_DEBUG in ('Y', 'C') THEN
414 arp_standard.debug ( 'The Master program waits for child processes');
415 END IF;
416
417 -- Wait for the completion of the submitted requests
418 FOR i in 1..p_total_workers LOOP
419
420 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
421 request_id => l_req_status_tab(i).request_id,
422 interval => 30,
423 max_wait =>144000,
424 phase =>l_req_status_tab(i).phase,
425 status =>l_req_status_tab(i).status,
426 dev_phase =>l_req_status_tab(i).dev_phase,
427 dev_status =>l_req_status_tab(i).dev_status,
428 message =>l_req_status_tab(i).message);
429
430 IF l_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
431 P_RETCODE := 2;
432 fnd_file.put_line( FND_FILE.LOG, 'Worker # '|| i||' has a phase '||l_req_status_tab(i).dev_phase);
433 ELSIF l_req_status_tab(i).dev_phase = 'COMPLETE'
434 AND l_req_status_tab(i).dev_status <> 'NORMAL' THEN
435 P_RETCODE := 2;
436 fnd_file.put_line( FND_FILE.LOG, 'Worker # '|| i||' completed with status '||l_req_status_tab(i).dev_status);
437 ELSE
438 fnd_file.put_line( FND_FILE.LOG, 'Worker # '|| i||' completed successfully');
439 END IF;
440
441 END LOOP;
442
443 fnd_file.put( FND_FILE.LOG, 'Return Code : ' || p_retcode);
444
445 IF NVL( p_retcode, -1) = 2 THEN
446 fnd_file.put_line( FND_FILE.LOG, ' - Child program failed.' );
447 ELSE
448 fnd_file.put_line( FND_FILE.LOG, ' - Child programs completed successfully' );
449 END IF;
450
451 IF NVL(p_transmission_id, -1) <> -1 THEN /* Lockbox batch */
452 FOR lbr IN lbbatch LOOP
453 update_batch_after_process( lbr.batch_id,
454 lbr.batch_applied_status,
455 lbr.control_count,
456 lbr.control_amount );
457 END LOOP;
458 ELSE
459 FOR qcr IN qcbatch LOOP /* Quick cash batch */
460 update_batch_after_process( p_batch_id,
461 qcr.batch_applied_status,
462 qcr.control_count,
463 qcr.control_amount );
464 END LOOP;
465 END IF;
466
467 IF NVL( p_transmission_id, -1) <> -1 THEN
468
469 fnd_file.put_line( FND_FILE.LOG, 'Updating transmission status.');
470
471 UPDATE ar_transmissions t
472 SET status = 'CL',
473 last_updated_by = G_USER_ID,
474 last_update_date = trunc(sysdate)
475 WHERE transmission_id = p_transmission_id
476 AND NOT EXISTS ( SELECT 'pending post'
477 FROM ar_batches b
478 WHERE b.transmission_id =
479 t.transmission_id
480 AND batch_applied_status
481 = 'POSTBATCH_WAITING' )
482 AND NOT EXISTS ( SELECT 'pending transfer'
483 FROM ar_payments_interface pi
484 WHERE pi.transmission_id =
485 t.transmission_id);
486 END IF;
487
488 AR_BUS_EVENT_COVER.Raise_PostBatch_Run_Event( G_CONC_REQUEST_ID );
489
490 <<leave_program>>
491 commit;
492 fnd_file.put_line( FND_FILE.LOG, 'submit_postbatch_parallel()-');
493
494 EXCEPTION
495
496 WHEN OTHERS THEN
497 RAISE ;
498
499 END submit_postbatch_parallel;
500
501 /*===========================================================================+
502 | PROCEDURE |
503 | update_batch_for_rerun() - If any error occurs during the postbatch |
504 | process, the batch_applied_status is put back to 'POSTBATCH_WAITING' |
505 | for rerun at later time. |
506 | DESCRIPTION |
507 | Updates batch_applied_Status to 'POSTBATCH_WAITING' |
508 | |
509 | SCOPE - PUBLIC |
510 | |
511 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
512 | ARGUMENTS : IN: |
513 | p_status - Batch Status |
514 | p_batch_id - Batch Id |
515 | |
516 | OUT: None |
517 | RETURNS : NONE |
518 | |
519 | NOTES - |
520 | |
521 | MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
522 +===========================================================================*/
523
524
525 PROCEDURE update_batch_for_rerun( p_status IN ar_batches.status%TYPE,
526 p_batch_id IN NUMBER) AS
527 BEGIN
528 fnd_file.put_line( FND_FILE.LOG, 'update_batch_for_rerun()+');
529
530 UPDATE ar_batches
531 SET batch_applied_status = 'POSTBATCH_WAITING',
532 status = p_status,
533 last_updated_by = G_USER_ID,
534 last_update_date = sysdate,
535 program_id = G_CONC_PROGRAM_ID,
536 request_id = G_CONC_REQUEST_ID,
537 program_application_id = G_PROG_APPL_ID,
538 program_update_date = sysdate
539 WHERE batch_id = p_batch_id;
540
541 commit;
542
543 fnd_file.put_line( FND_FILE.LOG, 'update_batch_for_rerun()-');
544 END update_batch_for_rerun;
545
546 END AR_POSTBATCH_PARALLEL;