1 PACKAGE BODY arp_run AS
2 -- $Header: ARTERRPB.pls 120.11 2011/07/07 19:13:40 mraymond ship $
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 l_has_enqueue_run BOOLEAN := FALSE;
249
250 TYPE req_status_typ IS RECORD (
251 request_id NUMBER(15),
252 dev_phase VARCHAR2(255),
253 dev_status VARCHAR2(255),
254 message VARCHAR2(2000),
255 phase VARCHAR2(255),
256 status VARCHAR2(255));
257
258 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
259
260 l_req_status_tab req_status_tab_typ;
261
262 PROCEDURE submit_subrequest (p_worker_num IN NUMBER,
263 p_org_id IN NUMBER ) AS
264
265 BEGIN
266 --
267 arp_util.print_fcn_label('submit_subrequest()+');
268
269 fnd_request.set_org_id(p_org_id);
270 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
271 application=>l_appl_short,
272 program=>l_program,
273 sub_request=>FALSE,
274 argument1=>p_worker_num,
275 argument2=>p_report_mode,
276 argument3=>p_org_id
277 ) ;
278
279 IF PG_DEBUG in ('Y', 'C') THEN
280 arp_standard.debug('Submitted child request no. ['|| p_worker_num ||'] : ' || l_reqid);
281 END IF;
282
283 commit;
284
285 l_req_data := l_req_data || l_reqid;
286
287 IF p_worker_num < p_total_workers THEN
288 l_req_data := l_req_data || ',';
289 END IF;
290
291 l_req_status_tab(p_worker_num).request_id := l_reqid;
292
293 arp_util.print_fcn_label('submit_subrequest()-');
294
295 END submit_subrequest;
296
297 BEGIN -- Submit_control
298
299 --
300 IF PG_DEBUG in ('Y', 'C') THEN
301 arp_standard.debug('submit_control()+');
302 END IF;
303
304 --
305 -- Wait for 1 Sec to check for messages
306 --
307 BEGIN
308 arp_queue.dequeue(p_msg=>l_msg,
309 p_browse=>TRUE,
310 p_first=>TRUE);
311 EXCEPTION
312 WHEN g_no_more_msgs THEN
313 IF PG_DEBUG in ('Y', 'C') THEN
314 arp_standard.debug('No More messages left. Check for any unprocessed Transactions.');
315 END IF;
316 enq_trans;
317 l_has_enqueue_run := TRUE;
318 commit;
319 arp_queue.dequeue(p_msg=>l_msg,
320 p_browse=>TRUE,
321 p_first=>TRUE);
322 WHEN OTHERS THEN
323 RAISE;
324 END;
325
326 IF PG_DEBUG in ('Y', 'C') THEN
327 arp_standard.debug('Found some messages..');
328 END IF;
329
330 l_req_data := NULL;
331
332 l_req_status_tab.DELETE;
333
334 --
335 -- Submit Minimum possible workers
336 --
337
338 FOR l_worker_number IN 1..MIN_WORKERS LOOP
339
340 submit_subrequest (l_worker_number, p_org_id);
341
342 END LOOP;
343
344 --
345 -- Based on the load startup additional workers
346 --
347
348 <<add_worker>>
349 FOR l_worker_number IN MIN_WORKERS_1..p_total_workers
350 LOOP
351 -- Check the status of Worker no 1
352
353 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
354 request_id=>l_req_status_tab(1).request_id,
355 interval=>p_interval,
356 max_wait=>p_max_wait,
357 phase=>l_req_status_tab(1).phase,
358 status=>l_req_status_tab(1).status,
359 dev_phase=>l_req_status_tab(1).dev_phase,
360 dev_status=>l_req_status_tab(1).dev_status,
361 message=>l_req_status_tab(1).message);
362
363 IF l_req_status_tab(1).dev_phase <> 'COMPLETE' THEN
364 IF PG_DEBUG in ('Y', 'C') THEN
365 arp_standard.debug('Starting additional workers..');
366 END IF;
367 submit_subrequest (l_worker_number, p_org_id);
368 ELSE
369 IF l_req_status_tab(1).dev_status IN ('TERMINATED', 'CANCELLED', 'ERROR') THEN
370 IF PG_DEBUG in ('Y', 'C') THEN
371 arp_standard.debug('Worker was terminated / cancelled / errored out..');
372 arp_standard.debug('Shutting down the master process.');
373 END IF;
374
375 RAISE worker_error;
376 ELSE
377 IF PG_DEBUG in ('Y', 'C') THEN
378 arp_standard.debug( 'Continue..');
379 END IF;
380 END IF;
381 END IF;
382
383 END LOOP add_worker;
384
385 --{BUG#5336931 - All REVREC sub requests have to end before the Master finishes
386 l_max_workers := l_req_status_tab.COUNT;
387
388 FOR i IN 1 .. l_max_workers LOOP
389 LOOP
390 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
391 request_id=>l_req_status_tab(i).request_id,
392 interval=>p_interval,
393 max_wait=>p_max_wait,
394 phase=>l_req_status_tab(i).phase,
395 status=>l_req_status_tab(i).status,
396 dev_phase=>l_req_status_tab(i).dev_phase,
397 dev_status=>l_req_status_tab(i).dev_status,
398 message=>l_req_status_tab(i).message);
399 EXIT WHEN (l_req_status_tab(i).dev_phase = 'COMPLETE');
400 END LOOP;
401 EXIT WHEN i = p_max_workers;
402 END LOOP;
403
404 /* 12387915 - Run enq_trans at least once at end to
405 insure we don't miss anything */
406 IF l_has_enqueue_run
407 THEN
408 IF PG_DEBUG in ('Y','C')
409 THEN
410 arp_debug.debug('Skipping unconditional enq_trans call');
411 END IF;
412 ELSE
413 IF PG_DEBUG in ('Y','C')
414 THEN
415 arp_debug.debug('Calling enq_trans unconditionally');
416 END IF;
417 enq_trans;
418 END IF;
419
420 IF PG_DEBUG in ('Y', 'C') THEN
421 arp_standard.debug( 'Req Data : ' || l_req_data);
422 arp_standard.debug( 'submit_control()-');
423 END IF;
424
425 RETURN SUCCESS ;
426
427 EXCEPTION
428 WHEN g_no_more_msgs THEN
429 IF PG_DEBUG in ('Y', 'C') THEN
430 arp_standard.debug( 'No More messages left. Shutting down the master.');
431 END IF;
432 RETURN SUCCESS;
433 WHEN worker_error THEN
434 IF PG_DEBUG in ('Y', 'C') THEN
435 arp_standard.debug( 'Worker was terminated / cancelled');
436 END IF;
437 RETURN SUCCESS ;
438 WHEN OTHERS THEN
439 IF PG_DEBUG in ('Y', 'C') THEN
440 arp_standard.debug( 'ARXRRSPW:' || SQLERRM(SQLCODE));
441 END IF;
442 RETURN FAILURE ;
443 END submit_control;
444
445 /*--------------------------------------------------------------------------*
446 | Processing cycle |
447 *--------------------------------------------------------------------------*/
448 BEGIN
449 select org_id
450 into l_org_id
451 from ar_system_parameters;
452
453 --
454 retcode := SUCCESS;
455 --
456 IF PG_DEBUG in ('Y', 'C') THEN
457 arp_standard.debug('arp_run.rev_rec_master()+');
458 END IF;
459 --
460 l_req_data := fnd_conc_global.request_data;
461 --
462 IF l_req_data IS NULL THEN -- First Time
463 --
464 IF PG_DEBUG in ('Y', 'C') THEN
465 arp_standard.debug( 'First Time..');
466 END IF;
467 --
468 -- Refresh AR Periods
469 --
470 arp_auto_rule.refresh(errbuf, retcode);
471 --
472 ELSE
473 --
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_standard.debug( 'Completed Requests :' || l_req_data);
476 END IF;
477 --
478 END IF;
479
480 retcode := submit_control (l_total_workers, l_org_id);
481
482 commit;
483
484 IF PG_DEBUG in ('Y', 'C') THEN
485 arp_standard.debug('arp_run.rev_rec_master()-');
486 END IF;
487
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 errbuf := 'EXCEPTION:' || SQLERRM(SQLCODE);
492 retcode := FAILURE;
493 IF PG_DEBUG in ('Y', 'C') THEN
494 arp_standard.debug( errbuf);
495 END IF;
496
497 END rev_rec_master;
498
499 /* Bug 2217161 - This routine is called behind the scenes of the
500 Credit Transactions form as a submitted job. This is to resolve
501 some significant performance problems related to invoice accounting
502 when crediting invoices with rules. The procedure will delete
503 all non-posted REV/UNEARN/UNBILL lines (non-model ones). It will
504 then call the arp_credit_memo_module.credit_transactions function
505 to rebuild them (correctly) based on the invoice's distributions. */
506
507 PROCEDURE build_credit_distributions (errbuf OUT NOCOPY VARCHAR2,
508 retcode OUT NOCOPY NUMBER,
509 p_customer_trx_id IN NUMBER,
510 p_prev_trx_id IN NUMBER) AS
511
512 l_failure_count NUMBER;
513
514 BEGIN
515 IF PG_DEBUG in ('Y', 'C') THEN
516 arp_standard.debug('arp_run.build_credit_distributions()+');
517 END IF;
518
519 retcode := SUCCESS;
520
521 arp_credit_memo_module.credit_transactions(
522 p_customer_trx_id,
523 null,
524 p_prev_trx_id,
525 null,
526 null,
527 l_failure_count);
528
529 /* Gotta commit the results */
530 commit;
531
532 IF PG_DEBUG in ('Y', 'C') THEN
533 arp_standard.debug('arp_run.build_credit_distributions()-');
534 END IF;
535
536 EXCEPTION
537 WHEN arp_credit_memo_module.no_ccid THEN
538 IF PG_DEBUG in ('Y', 'C') THEN
539 arp_standard.debug('EXCEPTION: credit memo module exception : no_ccid');
540 END IF;
541 RAISE;
542 WHEN NO_DATA_FOUND THEN
543 IF PG_DEBUG in ('Y', 'C') THEN
544 arp_standard.debug('EXCEPTION: credit memo module exception : no_data_found');
545 END IF;
546 null;
547 WHEN app_exception.application_exception THEN
548 IF PG_DEBUG in ('Y', 'C') THEN
549 arp_standard.debug('build_credit_distributions: ' || 'credit memo module exception : app_exception ');
550 END IF;
551 RAISE;
552 WHEN OTHERS THEN
553 RAISE;
554
555 END build_credit_distributions;
556
557 /* Bug 2967037 - Added logic to submit reporting sets of books based on the
558 primary sob defined by/for this operating unit. This routine submits
559 all associated rsobs for a given psob_id. The remaining parameters
560 are all fed directly into the call to ARGLTP */
561
562 PROCEDURE submit_mrc_posting (p_psob_id IN NUMBER,
563 p_gl_start_date IN DATE,
564 p_gl_end_date IN DATE,
565 p_gl_posted_date IN DATE,
566 p_summary_flag IN VARCHAR2,
567 p_journal_import IN VARCHAR2,
568 p_posting_days_per_cycle IN NUMBER,
569 p_posting_control_id IN NUMBER,
570 p_debug_flag IN VARCHAR2,
571 p_org_id IN NUMBER,
572 retcode OUT NOCOPY NUMBER) AS
573
574 l_acctg_sob_list gl_ca_utility_pkg.r_sob_list;
575 l_req_id NUMBER;
576 l_sob_type VARCHAR2(1);
577 BEGIN
578 IF PG_DEBUG in ('Y', 'C') THEN
579 arp_standard.debug('arp_run.submit_mrc_posting()+');
580 arp_standard.debug(' Submitting for primary sob : ' ||
581 arp_global.sysparam.set_of_books_id);
582
583 END IF;
584
585 /* Initialize list */
586 l_acctg_sob_list := gl_ca_utility_pkg.r_sob_list();
587
588 gl_ca_utility_pkg.get_associated_sobs(
589 p_sob_id => arp_global.sysparam.set_of_books_id,
590 p_appl_id => 222,
591 p_org_id => arp_global.sysparam.org_id,
592 p_sob_list => l_acctg_sob_list);
593
594 /* Initialize retcode */
595 retcode := 0;
596
597 FOR l_index IN 1 .. l_acctg_sob_list.COUNT LOOP
598
599 gl_ca_utility_pkg.get_sob_type(
600 l_acctg_sob_list(l_index).r_sob_id,
601 l_sob_type);
602
603 IF (l_sob_type = 'R')
604 THEN
605
606 /* Submit for RSOB
607 NOTE: For clarity, I converted all parameters to their
608 correct types in ARXPRGLP. However, some of them
609 must be converted back to char for the pro*C call */
610
611 l_req_id := fnd_request.submit_request('AR', 'ARGLTP',
612 NULL, NULL, FALSE,
613 fnd_date.date_to_canonical(p_gl_start_date),
614 fnd_date.date_to_canonical(p_gl_end_date),
615 fnd_date.date_to_canonical(p_gl_posted_date),
616 'N',
617 p_summary_flag,
618 p_journal_import,
619 to_char(p_posting_days_per_cycle),
620 '',
621 p_debug_flag,
622 to_char(p_org_id),
623 to_char(l_acctg_sob_list(l_index).r_sob_id),
624 chr(0),
625 '','','','','','','','','','',
626 '','','','','','','','','','',
627 '','','','','','','','','','',
628 '','','','','','','','','','',
629 '','','','','','','','','','',
630 '','','','','','','','','','',
631 '','','','','','','','','','',
632 '','','','','','','','','','',
633 '','','','','','','','');
634
635 IF (l_req_id IS NOT NULL AND l_req_id <> 0)
636 THEN
637 IF PG_DEBUG in ('Y', 'C') THEN
638 arp_standard.debug(' SUCCESS: sob_id = ' ||
639 l_acctg_sob_list(l_index).r_sob_id ||
640 ' req_id = ' || l_req_id);
641 END IF;
642
643 ELSE
644 /* Set retcode to -1 to indicate that at least
645 one submission failed */
646 retcode := -1;
647 arp_standard.debug(' EXCEPTION: sob_id = ' ||
648 l_acctg_sob_list(l_index).r_sob_id);
649 END IF;
650
651 END IF;
652
653 END LOOP;
654
655 IF PG_DEBUG in ('Y', 'C') THEN
656 arp_standard.debug('arp_run.submit_mrc_posting()-');
657 END IF;
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 arp_standard.debug('EXCEPTION: arp_run.submit_mrc_posting()');
662 /* retcode to -1 means ARGLTP will end in WARNING */
663 retcode := -1;
664 END submit_mrc_posting;
665
666 END arp_run;
[Home] [Help]