1 PACKAGE BODY arp_run AS
2 -- $Header: ARTERRPB.pls 120.10 2006/06/16 18:58:16 hyu arrt008.sql $
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 g_debug_flag VARCHAR2(4):= NVL(arp_standard.pg_prf_enable_debug, 'N');
6
7 g_no_more_msgs EXCEPTION;
8
9 PRAGMA EXCEPTION_INIT(g_no_more_msgs, -25228);
10
11 SUCCESS CONSTANT NUMBER:=0;
12 WARNING CONSTANT NUMBER:=1;
13 FAILURE CONSTANT NUMBER:=2;
14
15 PROCEDURE enq_trans AS
16 CURSOR c01 IS
17 SELECT
18 DISTINCT ctl.customer_trx_id,
19 ct.trx_number
20 FROM
21 ra_customer_trx ct,
22 ra_customer_trx_lines ctl
23 WHERE
24 ctl.autorule_complete_flag = 'N'
25 AND ct.customer_trx_id = ctl.customer_trx_id
26 AND ct.complete_flag = 'Y';
27
28 BEGIN
29 --
30 arp_util.print_fcn_label('arp_run.enq_trans()+');
31 --
32 FOR c01_rec IN c01 LOOP
33 -- Put the message in the queue
34 arp_queue.enqueue(system.ar_rev_rec_typ(c01_rec.customer_trx_id,
35 NVL(arp_global.sysparam.org_id, 0), 'ARTERRPB', c01_rec.trx_number));
36 --
37 END LOOP;
38 --
39 arp_util.print_fcn_label('arp_run.enq_trans()-');
40 --
41 EXCEPTION
42 WHEN OTHERS THEN
43 arp_standard.debug('EXCEPTION: ' ||SQLERRM(SQLCODE));
44 RAISE;
45 END enq_trans;
46
47
48 PROCEDURE revenue_recognition(errbuf OUT NOCOPY VARCHAR2,
49 retcode OUT NOCOPY NUMBER,
50 p_worker_number IN NUMBER,
51 p_report_mode IN VARCHAR2,
52 p_org_id IN NUMBER) AS
53 l_msg SYSTEM.AR_REV_REC_TYP;
54 l_reqid NUMBER := 0;
55 l_req_data VARCHAR2(2000);
56 --
57 l_nq_opts DBMS_AQ.ENQUEUE_OPTIONS_T;
58 l_msg_prop DBMS_AQ.MESSAGE_PROPERTIES_T;
59 --
60 l_recipients DBMS_AQ.AQ$_RECIPIENT_LIST_T;
61 --
62 l_msg_id RAW(16);
63 l_dq_success BOOLEAN :=FALSE;
64
65
66 i NUMBER := 1;
67 l_total_dists NUMBER := 0;
68
69 l_org_id NUMBER;
70
71 BEGIN
72
73 arp_util.print_fcn_label('arp_run.revenue_recognition()+');
74
75 select org_id
76 into l_org_id
77 from ar_system_parameters;
78
79 retcode := SUCCESS;
80
81 l_req_data := fnd_conc_global.request_data;
82
83 IF l_req_data IS NULL THEN -- First Time
84
85 IF PG_DEBUG in ('Y', 'C') THEN
86 arp_standard.debug('My worker number is ' || p_worker_number);
87 END IF;
88
89 <<rev_rec_loop>>
90 LOOP
91
92 l_dq_success := FALSE;
93
94 arp_queue.dequeue(p_msg=>l_msg);
95
96 l_dq_success := TRUE;
97
98 -- And print it.
99
100 IF PG_DEBUG in ('Y', 'C') THEN
101 arp_standard.debug('revenue_recognition: ' || i || '> Processing Trx number : <' || l_msg.trx_number || '> Trx Id <' ||
102 l_msg.customer_trx_id || '> Created From : <' || l_msg.created_from || ' ' ||l_msg.org_id );
103 END IF;
104 --
105 i := i + 1;
106 --
107 <<create_dists>>
108 BEGIN
109 /* Bug 2649674 - added p_continue_on_error set to 'Y' */
110 l_total_dists := l_total_dists +
111 arp_auto_rule.create_distributions(p_commit=>'Y',
112 p_debug =>g_debug_flag,
113 p_trx_id=>l_msg.customer_trx_id,
114 p_suppress_round=>NULL,
115 p_continue_on_error=>'Y');
116 EXCEPTION
117 WHEN OTHERS THEN
118 IF PG_DEBUG in ('Y', 'C') THEN
119 arp_standard.debug('EXCEPTION: Could not create distribution for Trx no.' || l_msg.trx_number);
120 arp_standard.debug(SQLERRM(SQLCODE));
121 END IF;
122 END create_dists;
123 --
124 --
125 commit;
126 --
127 IF PG_DEBUG in ('Y', 'C') THEN
128 arp_standard.debug(' Total dists created so far : ' || l_total_dists);
129 END IF;
130
131 END LOOP rev_rec_loop;
132
133 ELSE
134 errbuf := 'Completed the report..';
135 IF PG_DEBUG in ('Y', 'C') THEN
136 arp_standard.debug('revenue_recognition: ' || errbuf);
137 END IF;
138 END IF;
139
140 arp_util.print_fcn_label('arp_run.revenue_recognition()-');
141
142 EXCEPTION
143 WHEN g_no_more_msgs THEN
144 -- End of the queue reached.
145 IF PG_DEBUG in ('Y', 'C') THEN
146 arp_standard.debug('No More messages left. Shutting down the worker..');
147 arp_standard.debug('Submitting the report..');
148 END IF;
149 fnd_request.set_org_id(l_org_id);
150 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
151 application=>'AR',
152 program=>'ARBARL_NON_SRS2',
153 sub_request=>TRUE,
154 argument1=>'P_COA=' ||
155 arp_standard.gl_chart_of_accounts_id , -- P_COA
156 argument2=>'P_RUN_AUTO_RULE=N', -- P_RUN_AUTO_RULE
157 argument3=>'P_COMMIT_AT_END=Y', -- P_COMMIT_AT_END
158 argument4=>'P_DEBUG_FLAG='|| g_debug_flag ,-- P_DEBUG_FLAG
159 argument5=>'P_CONTINUE_ON_ERROR=Y', -- P_CONTINUE_ON_ERROR
160 argument6=>'P_USER_ID=' ||
161 arp_standard.profile.user_id, -- P_USER_ID
162 argument7=>'CONC_REQUEST_ID=' ||
163 arp_standard.profile.request_id, -- P_CONC_REQUEST_ID
164 argument8=>'P_REPORT_MODE=' ||
165 p_report_mode -- P_REPORT_MODE
166 ) ;
167 IF PG_DEBUG in ('Y', 'C') THEN
168 arp_standard.debug('Request Id :' || l_reqid);
169 END IF;
170
171 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
172 request_data => to_char(l_reqid)) ;
173
174 --
175 commit;
176 --
177 --
178 errbuf := 'Report submitted!';
179
180 WHEN OTHERS THEN
181 ROLLBACK;
182 errbuf := 'EXCEPTION:' ||SQLERRM(SQLCODE);
183 IF PG_DEBUG in ('Y', 'C') THEN
184 arp_standard.debug('revenue_recognition: ' || errbuf);
185 END IF;
186 retcode := WARNING;
187 --
188 -- Put the dequeued message back in the queue
189 --
190 IF l_dq_success THEN
191 BEGIN
192 --
193 IF PG_DEBUG in ('Y', 'C') THEN
194 arp_standard.debug('Putting the last message back in the queue');
195 END IF;
196 --
197 arp_queue.enqueue(p_msg=>l_msg);
198 --
199 commit;
200 --
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF PG_DEBUG in ('Y', 'C') THEN
204 arp_standard.debug('EXCEPTION:' || SQLERRM(SQLCODE));
205 arp_standard.debug('Unable to enqueue the message last message');
206 END IF;
207 retcode := FAILURE;
208 RAISE;
209 END;
210 --
211 END IF;
212
213 END revenue_recognition;
214
215 PROCEDURE rev_rec_master (errbuf OUT NOCOPY VARCHAR2,
216 retcode OUT NOCOPY NUMBER,
217 p_report_mode IN VARCHAR2 := 'S',
218 p_max_workers IN NUMBER := 2,
219 p_interval IN NUMBER :=60,
220 p_max_wait IN NUMBER := 180,
221 p_org_id IN NUMBER) AS
222
223 -- Constants
224
225 MAX_WORKERS CONSTANT NUMBER := 15; -- Limitation because of size of request_data (255)
226 MIN_WORKERS CONSTANT NUMBER := 2;
227 MIN_WORKERS_1 CONSTANT NUMBER := MIN_WORKERS + 1;
228
229 -- Variables
230
231 l_total_workers NUMBER := LEAST(p_max_workers, MAX_WORKERS); -- total number of workers
232 l_req_data VARCHAR2(2000);
233 l_msg system.AR_REV_REC_TYP;
234 worker_error EXCEPTION;
235 l_org_id NUMBER;
236 l_max_workers NUMBER;
237 -- Functions
238
239 FUNCTION submit_control (
240 p_total_workers IN NUMBER := MIN_WORKERS,
241 p_org_id IN NUMBER
242 ) RETURN INTEGER IS
243 l_worker_number NUMBER;
244 l_reqid NUMBER;
245 l_program VARCHAR2(30) := 'ARTERRPW' ;
246 l_appl_short VARCHAR2(30) := 'AR' ;
247 l_complete BOOLEAN := FALSE;
248
249 TYPE req_status_typ IS RECORD (
250 request_id NUMBER(15),
251 dev_phase VARCHAR2(255),
252 dev_status VARCHAR2(255),
253 message VARCHAR2(2000),
254 phase VARCHAR2(255),
255 status VARCHAR2(255));
256
257 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
258
259 l_req_status_tab req_status_tab_typ;
260
261 PROCEDURE submit_subrequest (p_worker_num IN NUMBER,
262 p_org_id IN NUMBER ) AS
263
264 BEGIN
265 --
266 arp_util.print_fcn_label('submit_subrequest()+');
267
268 fnd_request.set_org_id(p_org_id);
269 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
270 application=>l_appl_short,
271 program=>l_program,
272 sub_request=>FALSE,
273 argument1=>p_worker_num,
274 argument2=>p_report_mode,
275 argument3=>p_org_id
276 ) ;
277
278 IF PG_DEBUG in ('Y', 'C') THEN
279 arp_standard.debug('Submitted child request no. ['|| p_worker_num ||'] : ' || l_reqid);
280 END IF;
281
282 commit;
283
284 l_req_data := l_req_data || l_reqid;
285
286 IF p_worker_num < p_total_workers THEN
287 l_req_data := l_req_data || ',';
288 END IF;
289
290 l_req_status_tab(p_worker_num).request_id := l_reqid;
291
292 arp_util.print_fcn_label('submit_subrequest()-');
293
294 END submit_subrequest;
295
296 BEGIN -- Submit_control
297
298 --
299 IF PG_DEBUG in ('Y', 'C') THEN
300 arp_standard.debug('submit_control()+');
301 END IF;
302
303 --
304 -- Wait for 1 Sec to check for messages
305 --
306 BEGIN
307 arp_queue.dequeue(p_msg=>l_msg,
308 p_browse=>TRUE,
309 p_first=>TRUE);
310 EXCEPTION
311 WHEN g_no_more_msgs THEN
312 IF PG_DEBUG in ('Y', 'C') THEN
313 arp_standard.debug('No More messages left. Check for any unprocessed Transactions.');
314 END IF;
315 enq_trans;
316 commit;
317 arp_queue.dequeue(p_msg=>l_msg,
318 p_browse=>TRUE,
319 p_first=>TRUE);
320 WHEN OTHERS THEN
321 RAISE;
322 END;
323
324 IF PG_DEBUG in ('Y', 'C') THEN
325 arp_standard.debug('Found some messages..');
326 END IF;
327
328 l_req_data := NULL;
329
330 l_req_status_tab.DELETE;
331
332 --
333 -- Submit Minimum possible workers
334 --
335
336 FOR l_worker_number IN 1..MIN_WORKERS LOOP
337
338 submit_subrequest (l_worker_number, p_org_id);
339
340 END LOOP;
341
342 --
343 -- Based on the load startup additional workers
344 --
345
346 <<add_worker>>
347 FOR l_worker_number IN MIN_WORKERS_1..p_total_workers
348 LOOP
349 -- Check the status of Worker no 1
350
351 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
352 request_id=>l_req_status_tab(1).request_id,
353 interval=>p_interval,
354 max_wait=>p_max_wait,
355 phase=>l_req_status_tab(1).phase,
356 status=>l_req_status_tab(1).status,
357 dev_phase=>l_req_status_tab(1).dev_phase,
358 dev_status=>l_req_status_tab(1).dev_status,
359 message=>l_req_status_tab(1).message);
360
361 IF l_req_status_tab(1).dev_phase <> 'COMPLETE' THEN
362 IF PG_DEBUG in ('Y', 'C') THEN
363 arp_standard.debug('Starting additional workers..');
364 END IF;
365 submit_subrequest (l_worker_number, p_org_id);
366 ELSE
367 IF l_req_status_tab(1).dev_status IN ('TERMINATED', 'CANCELLED', 'ERROR') THEN
368 IF PG_DEBUG in ('Y', 'C') THEN
369 arp_standard.debug('Worker was terminated / cancelled / errored out..');
370 arp_standard.debug('Shutting down the master process.');
371 END IF;
372
373 RAISE worker_error;
374 ELSE
375 IF PG_DEBUG in ('Y', 'C') THEN
376 arp_standard.debug( 'Continue..');
377 END IF;
378 END IF;
379 END IF;
380
381 END LOOP add_worker;
382
383 --{BUG#5336931 - All REVREC sub requests have to end before the Master finishes
384 l_max_workers := l_req_status_tab.COUNT;
385
386 FOR i IN 1 .. l_max_workers LOOP
387 LOOP
388 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
389 request_id=>l_req_status_tab(i).request_id,
390 interval=>p_interval,
391 max_wait=>p_max_wait,
392 phase=>l_req_status_tab(i).phase,
393 status=>l_req_status_tab(i).status,
394 dev_phase=>l_req_status_tab(i).dev_phase,
395 dev_status=>l_req_status_tab(i).dev_status,
396 message=>l_req_status_tab(i).message);
397 EXIT WHEN (l_req_status_tab(i).dev_phase = 'COMPLETE');
398 END LOOP;
399 EXIT WHEN i = p_max_workers;
400 END LOOP;
401 --}
402 IF PG_DEBUG in ('Y', 'C') THEN
403 arp_standard.debug( 'Req Data : ' || l_req_data);
404 arp_standard.debug( 'submit_control()-');
405 END IF;
406
407 RETURN SUCCESS ;
408
409 EXCEPTION
410 WHEN g_no_more_msgs THEN
411 IF PG_DEBUG in ('Y', 'C') THEN
412 arp_standard.debug( 'No More messages left. Shutting down the master.');
413 END IF;
414 RETURN SUCCESS;
415 WHEN worker_error THEN
416 IF PG_DEBUG in ('Y', 'C') THEN
417 arp_standard.debug( 'Worker was terminated / cancelled');
418 END IF;
419 RETURN SUCCESS ;
420 WHEN OTHERS THEN
421 IF PG_DEBUG in ('Y', 'C') THEN
422 arp_standard.debug( 'ARXRRSPW:' || SQLERRM(SQLCODE));
423 END IF;
424 RETURN FAILURE ;
425 END submit_control;
426
427 /*--------------------------------------------------------------------------*
428 | Processing cycle |
429 *--------------------------------------------------------------------------*/
430 BEGIN
431 select org_id
432 into l_org_id
433 from ar_system_parameters;
434
435 --
436 retcode := SUCCESS;
437 --
438 IF PG_DEBUG in ('Y', 'C') THEN
439 arp_standard.debug('arp_run.rev_rec_master()+');
440 END IF;
441 --
442 l_req_data := fnd_conc_global.request_data;
443 --
444 IF l_req_data IS NULL THEN -- First Time
445 --
446 IF PG_DEBUG in ('Y', 'C') THEN
447 arp_standard.debug( 'First Time..');
448 END IF;
449 --
450 -- Refresh AR Periods
451 --
452 arp_auto_rule.refresh(errbuf, retcode);
453 --
454 ELSE
455 --
456 IF PG_DEBUG in ('Y', 'C') THEN
457 arp_standard.debug( 'Completed Requests :' || l_req_data);
458 END IF;
459 --
460 END IF;
461
462 retcode := submit_control (l_total_workers, l_org_id);
463
464 commit;
465
466 IF PG_DEBUG in ('Y', 'C') THEN
467 arp_standard.debug('arp_run.rev_rec_master()-');
468 END IF;
469
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 errbuf := 'EXCEPTION:' || SQLERRM(SQLCODE);
474 retcode := FAILURE;
475 IF PG_DEBUG in ('Y', 'C') THEN
476 arp_standard.debug( errbuf);
477 END IF;
478
479 END rev_rec_master;
480
481 /* Bug 2217161 - This routine is called behind the scenes of the
482 Credit Transactions form as a submitted job. This is to resolve
483 some significant performance problems related to invoice accounting
484 when crediting invoices with rules. The procedure will delete
485 all non-posted REV/UNEARN/UNBILL lines (non-model ones). It will
486 then call the arp_credit_memo_module.credit_transactions function
487 to rebuild them (correctly) based on the invoice's distributions. */
488
489 PROCEDURE build_credit_distributions (errbuf OUT NOCOPY VARCHAR2,
490 retcode OUT NOCOPY NUMBER,
491 p_customer_trx_id IN NUMBER,
492 p_prev_trx_id IN NUMBER) AS
493
494 l_failure_count NUMBER;
495
496 BEGIN
497 IF PG_DEBUG in ('Y', 'C') THEN
498 arp_standard.debug('arp_run.build_credit_distributions()+');
499 END IF;
500
501 retcode := SUCCESS;
502
503 arp_credit_memo_module.credit_transactions(
504 p_customer_trx_id,
505 null,
506 p_prev_trx_id,
507 null,
508 null,
509 l_failure_count);
510
511 /* Gotta commit the results */
512 commit;
513
514 IF PG_DEBUG in ('Y', 'C') THEN
515 arp_standard.debug('arp_run.build_credit_distributions()-');
516 END IF;
517
518 EXCEPTION
519 WHEN arp_credit_memo_module.no_ccid THEN
520 IF PG_DEBUG in ('Y', 'C') THEN
521 arp_standard.debug('EXCEPTION: credit memo module exception : no_ccid');
522 END IF;
523 RAISE;
524 WHEN NO_DATA_FOUND THEN
525 IF PG_DEBUG in ('Y', 'C') THEN
526 arp_standard.debug('EXCEPTION: credit memo module exception : no_data_found');
527 END IF;
528 null;
529 WHEN app_exception.application_exception THEN
530 IF PG_DEBUG in ('Y', 'C') THEN
531 arp_standard.debug('build_credit_distributions: ' || 'credit memo module exception : app_exception ');
532 END IF;
533 RAISE;
534 WHEN OTHERS THEN
535 RAISE;
536
537 END build_credit_distributions;
538
539 /* Bug 2967037 - Added logic to submit reporting sets of books based on the
540 primary sob defined by/for this operating unit. This routine submits
541 all associated rsobs for a given psob_id. The remaining parameters
542 are all fed directly into the call to ARGLTP */
543
544 PROCEDURE submit_mrc_posting (p_psob_id IN NUMBER,
545 p_gl_start_date IN DATE,
546 p_gl_end_date IN DATE,
547 p_gl_posted_date IN DATE,
548 p_summary_flag IN VARCHAR2,
549 p_journal_import IN VARCHAR2,
550 p_posting_days_per_cycle IN NUMBER,
551 p_posting_control_id IN NUMBER,
552 p_debug_flag IN VARCHAR2,
553 p_org_id IN NUMBER,
554 retcode OUT NOCOPY NUMBER) AS
555
556 l_acctg_sob_list gl_ca_utility_pkg.r_sob_list;
557 l_req_id NUMBER;
558 l_sob_type VARCHAR2(1);
559 BEGIN
560 IF PG_DEBUG in ('Y', 'C') THEN
561 arp_standard.debug('arp_run.submit_mrc_posting()+');
562 arp_standard.debug(' Submitting for primary sob : ' ||
563 arp_global.sysparam.set_of_books_id);
564
565 END IF;
566
567 /* Initialize list */
568 l_acctg_sob_list := gl_ca_utility_pkg.r_sob_list();
569
570 gl_ca_utility_pkg.get_associated_sobs(
571 p_sob_id => arp_global.sysparam.set_of_books_id,
572 p_appl_id => 222,
573 p_org_id => arp_global.sysparam.org_id,
574 p_sob_list => l_acctg_sob_list);
575
576 /* Initialize retcode */
577 retcode := 0;
578
579 FOR l_index IN 1 .. l_acctg_sob_list.COUNT LOOP
580
581 gl_ca_utility_pkg.get_sob_type(
582 l_acctg_sob_list(l_index).r_sob_id,
583 l_sob_type);
584
585 IF (l_sob_type = 'R')
586 THEN
587
588 /* Submit for RSOB
589 NOTE: For clarity, I converted all parameters to their
590 correct types in ARXPRGLP. However, some of them
591 must be converted back to char for the pro*C call */
592
593 l_req_id := fnd_request.submit_request('AR', 'ARGLTP',
594 NULL, NULL, FALSE,
595 fnd_date.date_to_canonical(p_gl_start_date),
596 fnd_date.date_to_canonical(p_gl_end_date),
597 fnd_date.date_to_canonical(p_gl_posted_date),
598 'N',
599 p_summary_flag,
600 p_journal_import,
601 to_char(p_posting_days_per_cycle),
602 '',
603 p_debug_flag,
604 to_char(p_org_id),
605 to_char(l_acctg_sob_list(l_index).r_sob_id),
606 chr(0),
607 '','','','','','','','','','',
608 '','','','','','','','','','',
609 '','','','','','','','','','',
610 '','','','','','','','','','',
611 '','','','','','','','','','',
612 '','','','','','','','','','',
613 '','','','','','','','','','',
614 '','','','','','','','','','',
615 '','','','','','','','');
616
617 IF (l_req_id IS NOT NULL AND l_req_id <> 0)
618 THEN
619 IF PG_DEBUG in ('Y', 'C') THEN
620 arp_standard.debug(' SUCCESS: sob_id = ' ||
621 l_acctg_sob_list(l_index).r_sob_id ||
622 ' req_id = ' || l_req_id);
623 END IF;
624
625 ELSE
626 /* Set retcode to -1 to indicate that at least
627 one submission failed */
628 retcode := -1;
629 arp_standard.debug(' EXCEPTION: sob_id = ' ||
630 l_acctg_sob_list(l_index).r_sob_id);
631 END IF;
632
633 END IF;
634
635 END LOOP;
636
637 IF PG_DEBUG in ('Y', 'C') THEN
638 arp_standard.debug('arp_run.submit_mrc_posting()-');
639 END IF;
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 arp_standard.debug('EXCEPTION: arp_run.submit_mrc_posting()');
644 /* retcode to -1 means ARGLTP will end in WARNING */
645 retcode := -1;
646 END submit_mrc_posting;
647
648 END arp_run;
[Home] [Help]