[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_BFPRI_CONC
Source
1 PACKAGE BODY AR_BPA_BFPRI_CONC AS
2 /* $Header: ARBPBFBB.pls 120.5.12010000.4 2009/05/19 10:32:18 pbapna ship $ */
3
4 cr CONSTANT char(1) := '
5 ';
6
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'Y');
8
9 FUNCTION build_from_clause RETURN VARCHAR2 IS
10 from_clause VARCHAR2(1024);
11
12 BEGIN
13
14 /*Bug8486880 Added party_site,loc tables with where conditions*/
15
16 from_clause := ' FROM ' || cr ||
17 ' ar_cons_inv cons, ' || cr ||
18 ' hz_cust_acct_sites_all a_bill, ' || cr ||
19 ' hz_cust_site_uses_all u_bill, ' || cr ||
20 ' hz_cust_accounts_all cust, ' || cr ||
21 ' hz_party_sites party_site, ' || cr ||
22 ' hz_locations loc' || cr ||
23 ' WHERE ' || cr ||
24 ' cust.cust_account_id = cons.customer_id ' || cr ||
25 ' AND u_bill.site_use_id = cons.site_use_id ' || cr ||
26 ' AND u_bill.org_id = cons.org_id ' || cr ||
27 ' AND a_bill.cust_acct_site_id = u_bill.cust_acct_site_id ' || cr ||
28 ' AND a_bill.org_id = u_bill.org_id ' || cr ||
29 ' AND a_bill.party_site_id = party_site.party_site_id ' || cr ||
30 ' AND loc.location_id = party_site.location_id ' || cr ||
31 ' AND cons.bill_level_flag in (' || '''A'', ''S'')' || cr ||
32 ' AND cons.status <> ' || '''REJECTED''' || cr ||
33 ' AND u_bill.PRIMARY_FLAG = decode(bill_level_flag, '|| '''A'', ''Y'', u_bill.PRIMARY_FLAG) ';
34
35 return from_clause;
36
37 END;
38
39 PROCEDURE check_child_request(
40 p_request_id IN OUT NOCOPY NUMBER
41 ) IS
42
43 call_status boolean;
44 rphase varchar2(80);
45 rstatus varchar2(80);
46 dphase varchar2(30);
47 dstatus varchar2(30);
48 message varchar2(240);
49
50 BEGIN
51 call_status := fnd_concurrent.get_request_status(
52 p_request_id,
53 '',
54 '',
55 rphase,
56 rstatus,
57 dphase,
58 dstatus,
59 message);
60
61 fnd_file.put_line( fnd_file.output, arp_standard.fnd_message('AR_BPA_PRINT_CHILD_REQ',
62 'REQ_ID',
63 p_request_id,
64 'PHASE',
65 dphase,
66 'STATUS',
67 dstatus));
68
69 IF ((dphase = 'COMPLETE') and (dstatus = 'NORMAL')) THEN
70 fnd_file.put_line( fnd_file.log, 'child request id: ' || p_request_id || ' complete successfully');
71 ELSE
72 fnd_file.put_line( fnd_file.log, 'child request id: ' || p_request_id || ' did not complete successfully');
73 END IF;
74
75 END;
76
77 FUNCTION submit_print_request(
78 p_parent_request_id IN NUMBER,
79 p_worker_id IN NUMBER,
80 p_order_by IN VARCHAR2,
81 p_template_id IN NUMBER,
82 p_stamp_flag IN VARCHAR2,
83 p_child_template_id IN NUMBER,
84 p_nls_lang IN VARCHAR2,
85 p_nls_territory IN VARCHAR2,
86 p_sub_request_flag IN BOOLEAN,
87 p_description IN VARCHAR2 DEFAULT NULL
88 ) RETURN NUMBER IS
89
90 l_options_ok BOOLEAN;
91 m_request_id NUMBER;
92
93 BEGIN
94
95 l_options_ok := FND_REQUEST.SET_OPTIONS (
96 implicit => 'NO'
97 , protected => 'YES'
98 , language => p_nls_lang
99 , territory => p_nls_territory);
100 IF (l_options_ok)
101 THEN
102
103 m_request_id := FND_REQUEST.SUBMIT_REQUEST(
104 application => 'AR'
105 , program => 'ARBPIPCP'
106 , description => p_description
107 , start_time => ''
108 , sub_request => p_sub_request_flag
109 , argument1 => p_parent_request_id
110 , argument2 => p_worker_id
111 , argument3 => p_order_by
112 , argument4 => p_template_id
113 , argument5 => p_stamp_flag
114 , argument6 => p_child_template_id
115 , argument7 => 223
116 , argument8 => chr(0)
117 , argument9 => ''
118 , argument10 => ''
119 , argument11 => ''
120 , argument12 => ''
121 , argument13 => ''
122 , argument14 => ''
126 , argument18 => ''
123 , argument15 => ''
124 , argument16 => ''
125 , argument17 => ''
127 , argument19 => ''
128 , argument20 => ''
129 , argument21 => ''
130 , argument22 => ''
131 , argument23 => ''
132 , argument24 => ''
133 , argument25 => ''
134 , argument26 => ''
135 , argument27 => ''
136 , argument28 => ''
137 , argument29 => ''
138 , argument30 => ''
139 , argument31 => ''
140 , argument32 => ''
141 , argument33 => ''
142 , argument34 => ''
143 , argument35 => ''
144 , argument36 => ''
145 , argument37 => ''
146 , argument38 => ''
147 , argument39 => ''
148 , argument40 => ''
149 , argument41 => ''
150 , argument42 => ''
151 , argument43 => ''
152 , argument44 => ''
153 , argument45 => ''
154 , argument46 => ''
155 , argument47 => ''
156 , argument48 => ''
157 , argument49 => ''
158 , argument50 => ''
159 , argument51 => ''
160 , argument52 => ''
161 , argument53 => ''
162 , argument54 => ''
163 , argument55 => ''
164 , argument56 => ''
165 , argument57 => ''
166 , argument58 => ''
167 , argument59 => ''
168 , argument61 => ''
169 , argument62 => ''
170 , argument63 => ''
171 , argument64 => ''
172 , argument65 => ''
173 , argument66 => ''
174 , argument67 => ''
175 , argument68 => ''
176 , argument69 => ''
177 , argument70 => ''
178 , argument71 => ''
179 , argument72 => ''
180 , argument73 => ''
181 , argument74 => ''
182 , argument75 => ''
183 , argument76 => ''
184 , argument77 => ''
185 , argument78 => ''
186 , argument79 => ''
187 , argument80 => ''
188 , argument81 => ''
189 , argument82 => ''
190 , argument83 => ''
191 , argument84 => ''
192 , argument85 => ''
193 , argument86 => ''
194 , argument87 => ''
195 , argument88 => ''
196 , argument89 => ''
197 , argument90 => ''
198 , argument91 => ''
199 , argument92 => ''
200 , argument93 => ''
201 , argument94 => ''
202 , argument95 => ''
203 , argument96 => ''
204 , argument97 => ''
205 , argument98 => ''
206 , argument99 => ''
207 , argument100 => '');
208 END IF;
209
210 RETURN m_request_id;
211
212 END;
213
214 PROCEDURE build_where_clause(
215 p_org_id IN NUMBER DEFAULT NULL,
216 p_cust_num_low IN VARCHAR2 DEFAULT NULL,
217 p_cust_num_high IN VARCHAR2 DEFAULT NULL,
218 p_bill_site_low IN NUMBER DEFAULT NULL,
219 p_bill_site_high IN NUMBER DEFAULT NULL,
220 p_bill_date_low IN DATE DEFAULT NULL,
221 p_bill_date_high IN DATE DEFAULT NULL,
222 p_bill_num_low IN VARCHAR2 DEFAULT NULL,
223 p_bill_num_high IN VARCHAR2 DEFAULT NULL,
224 p_request_id IN NUMBER DEFAULT NULL,
225 where_clause OUT NOCOPY VARCHAR2) IS
226
227 BEGIN
228 IF ( p_org_id is not null ) THEN
229 where_clause :=where_clause || ' AND cons.org_id = :org_id ' || cr;
230 END IF;
231
232 IF ( (p_cust_num_low is not null) and (p_cust_num_high is null) ) THEN
233 where_clause :=where_clause || ' AND cust.account_number = :cust_num_low ' || cr;
234 ELSIF ( (p_cust_num_high is not null) and (p_cust_num_low is null) ) THEN
235 where_clause :=where_clause || ' AND cust.account_number = :cust_num_high ' || cr;
236 ELSIF ( (p_cust_num_high is not null) and (p_cust_num_low is not null) ) THEN
237 where_clause :=where_clause || ' AND cust.account_number >= :cust_num_low ' || cr;
238 where_clause :=where_clause || ' AND cust.account_number <= :cust_num_high ' || cr;
239 END IF;
240
241 IF ( (p_bill_site_low is not null) and (p_bill_site_high is null) ) THEN
242 where_clause :=where_clause || ' AND cons.site_use_id = :bill_site_low ' || cr;
243 ELSIF ( (p_bill_site_high is not null) and (p_bill_site_low is null) ) THEN
244 where_clause :=where_clause || ' AND cons.site_use_id = :bill_site_high ' || cr;
245 ELSIF ( (p_bill_site_high is not null) and (p_bill_site_low is not null) ) THEN
246 where_clause :=where_clause || ' AND cons.site_use_id >= :bill_site_low ' || cr;
247 where_clause :=where_clause || ' AND cons.site_use_id <= :bill_site_high ' || cr;
248 END IF;
249
250 where_clause :=where_clause || ' AND billing_date between nvl(:bill_date_low, billing_date) and nvl(:bill_date_high, billing_date) ' || cr;
251
252 IF ( (p_bill_num_low is not null) and (p_bill_num_high is null) ) THEN
256 ELSIF ( (p_bill_num_low is not null) and (p_bill_num_high is not null) ) THEN
253 where_clause :=where_clause || ' AND cons.cons_billing_number = :bill_num_low ' || cr;
254 ELSIF ( (p_bill_num_high is not null) and (p_bill_num_low is null) ) THEN
255 where_clause :=where_clause || ' AND cons.cons_billing_number = :bill_num_high ' || cr;
257 where_clause :=where_clause || ' AND cons.cons_billing_number >= :bill_num_low ' || cr;
258 where_clause :=where_clause || ' AND cons.cons_billing_number <= :bill_num_high ' || cr;
259 END IF;
260
261 IF ( p_request_id is not null ) THEN
262 where_clause := where_clause || ' AND cons.concurrent_request_id = :concurrent_request_id ' || cr;
263 END IF;
264
265 END BUILD_WHERE_CLAUSE;
266
267 PROCEDURE BIND_VARIABLES(
268 p_org_id IN NUMBER DEFAULT NULL,
269 p_cust_num_low IN VARCHAR2 DEFAULT NULL,
270 p_cust_num_high IN VARCHAR2 DEFAULT NULL,
271 p_bill_site_low IN NUMBER DEFAULT NULL,
272 p_bill_site_high IN NUMBER DEFAULT NULL,
273 p_bill_date_low IN DATE DEFAULT NULL,
274 p_bill_date_high IN DATE DEFAULT NULL,
275 p_bill_num_low IN VARCHAR2 DEFAULT NULL,
276 p_bill_num_high IN VARCHAR2 DEFAULT NULL,
277 p_request_id IN NUMBER DEFAULT NULL,
278 cursor_name IN INTEGER ) IS
279
280 BEGIN
281
282 IF ( p_org_id is not null ) THEN
283 dbms_sql.bind_variable( cursor_name, ':org_id', p_org_id) ;
284 END IF;
285
286 IF ( p_cust_num_low is not null ) THEN
287 dbms_sql.bind_variable( cursor_name, ':cust_num_low', p_cust_num_low ) ;
288 END IF;
289 IF ( p_cust_num_high is not null ) THEN
290 dbms_sql.bind_variable( cursor_name, ':cust_num_high', p_cust_num_high ) ;
291 END IF;
292
293 IF ( p_bill_site_low is not null ) THEN
294 dbms_sql.bind_variable( cursor_name, ':bill_site_low', p_bill_site_low ) ;
295 END IF;
296 IF ( p_bill_site_high is not null ) THEN
297 dbms_sql.bind_variable( cursor_name, ':bill_site_high', p_bill_site_high ) ;
298 END IF;
299
300 dbms_sql.bind_variable( cursor_name, ':bill_date_low', p_bill_date_low ) ;
301 dbms_sql.bind_variable( cursor_name, ':bill_date_high', p_bill_date_high ) ;
302
303 IF ( p_bill_num_low is not null ) THEN
304 dbms_sql.bind_variable( cursor_name, ':bill_num_low', p_bill_num_low ) ;
305 END IF;
306 IF ( p_bill_num_high is not null ) THEN
307 dbms_sql.bind_variable( cursor_name, ':bill_num_high', p_bill_num_high ) ;
308 END IF;
309
310 IF ( p_request_id is not null ) THEN
311 dbms_sql.bind_variable( cursor_name, ':concurrent_request_id', p_request_id ) ;
312 END IF;
313
314 END BIND_VARIABLES;
315
316
317 function PRINT_MLS_FUNCTION RETURN VARCHAR2 IS
318
319 -- variables used by build_where_clause
320 p_org_id number := NULL;
321 p_job_size number := NULL;
322 p_cust_num_low varchar2(30) := NULL;
323 p_cust_num_high varchar2(30) := NULL;
324 p_bill_site_low number := NULL;
325 p_bill_site_high number := NULL;
326 p_bill_date_low date := NULL;
327 p_bill_date_high date := NULL;
328 p_bill_num_low varchar2(30) := NULL;
329 p_bill_num_high varchar2(30) := NULL;
330 p_request_id NUMBER := NULL;
331
332 p_where varchar2(1024);
333
334 --local variables
335 userenv_lang varchar2(4);
336 --base_lang varchar2(4);/*Bug8486880*/
337 retval number;
338 parm_number number;
339 parm_name varchar2(80);
340
341 sql_stmt_c number;
342 sql_stmt varchar2(2048);
343 select_stmt varchar2(1000);
344 lang_str varchar2(240);
345
346 TYPE sql_stmt_rec_type IS RECORD
347 (language VARCHAR2(4));
348
349 sql_stmt_rec sql_stmt_rec_type ;
350 l_ignore INTEGER;
351
352 BEGIN
353
354 select substr(userenv('LANG'),1,4)
355 into userenv_lang
356 from dual;
357
358 /* Bug8486880
359 select language_code
360 into base_lang
361 from fnd_languages
362 where installed_flag = 'B';
363 */
364
365 MO_global.init('AR');
366 fnd_file.put_line( fnd_file.log, 'userenv_lang: ' || userenv_lang);
367 --fnd_file.put_line( fnd_file.log, 'base_lang: ' || base_lang);/*Bug8486880*/
368
369 /* Read in Parameter Values supplied by user */
370 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Operating Unit',parm_number);
371 IF retval = -1 THEN
372 p_org_id := NULL;
373 ELSE
374 p_org_id:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
375 END IF;
376 fnd_file.put_line( fnd_file.log, 'p_org_id: ' || p_org_id);
377
378 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Job Size',parm_number);
379 IF retval = -1 THEN
380 p_job_size:= NULL;
381 ELSE
382 p_job_size:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
383 END IF;
384 fnd_file.put_line( fnd_file.log, 'p_job_size: ' || p_job_size);
385
386 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer Number Low',parm_number);
387 IF retval = -1 THEN
388 p_cust_num_low:= NULL;
389 ELSE
390 p_cust_num_low:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
391 END IF;
392 fnd_file.put_line( fnd_file.log, 'p_cust_num_low: ' || p_cust_num_low);
393
394 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer Number High',parm_number);
395 IF retval = -1 THEN
396 p_cust_num_high:= NULL;
397 ELSE
398 p_cust_num_high:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
399 END IF;
400 fnd_file.put_line( fnd_file.log, 'p_cust_num_high: ' || p_cust_num_high);
404 p_bill_site_low:= NULL;
401
402 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Location Low',parm_number);
403 IF retval = -1 THEN
405 ELSE
406 p_bill_site_low:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
407 END IF;
408 fnd_file.put_line( fnd_file.log, 'p_bill_site_low: ' || p_bill_site_low);
409
410 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Location High',parm_number);
411 IF retval = -1 THEN
412 p_bill_site_high:= NULL;
413 ELSE
414 p_bill_site_high:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
415 END IF;
416 fnd_file.put_line( fnd_file.log, 'p_bill_site_high: ' || p_bill_site_high);
417
418 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Billing Date Low',parm_number);
419 IF retval = -1 THEN
420 p_bill_date_low:= NULL;
421 ELSE
422 p_bill_date_low:= fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
423 END IF;
424 fnd_file.put_line( fnd_file.log, 'p_bill_date_low: ' || p_bill_date_low);
425
426 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Billing Date High',parm_number);
427 IF retval = -1 THEN
428 p_bill_date_high:= NULL;
429 ELSE
430 p_bill_date_high:= fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
431 END IF;
432 fnd_file.put_line( fnd_file.log, 'p_bill_date_high: ' || p_bill_date_high);
433
434 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Billing Number Low',parm_number);
435 IF retval = -1 THEN
436 p_bill_num_low:= NULL;
437 ELSE
438 p_bill_num_low:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
439 END IF;
440 fnd_file.put_line( fnd_file.log, 'p_bill_num_low: ' || p_bill_num_low);
441
442 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Billing Number High',parm_number);
443 IF retval = -1 THEN
444 p_bill_num_high:= NULL;
445 ELSE
446 p_bill_num_high:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
447 END IF;
448 fnd_file.put_line( fnd_file.log, 'p_bill_num_high: ' || p_bill_num_high);
449
450 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Concurrent Request ID',parm_number);
451 IF retval = -1 THEN
452 p_request_id:= NULL;
453 ELSE
454 p_request_id:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
455 END IF;
456 fnd_file.put_line( fnd_file.log, 'p_request_id: ' || p_request_id);
457
458 /*Bug8486880*/
459 select_stmt :=
460 ' select distinct(nvl(rtrim(substr(loc.language,1,4)), ''' || userenv_lang || ''')) language ' || cr ||
461 build_from_clause;
462
463 Build_where_clause(
464 p_org_id,
465 p_cust_num_low ,
466 p_cust_num_high ,
467 p_bill_site_low,
468 p_bill_site_high,
469 p_bill_date_low,
470 p_bill_date_high,
471 p_bill_num_low,
472 p_bill_num_high,
473 p_request_id,
474 p_where) ;
475
476 sql_stmt := select_stmt || cr || p_where;
477
478 -- fnd_file.put_line( fnd_file.log, 'The final sql: ' || sql_stmt);
479 ------------------------------------------------
480 -- Parse sql stmts
481 ------------------------------------------------
482
483 sql_stmt_c:= dbms_sql.open_cursor;
484
485 dbms_sql.parse( sql_stmt_c, sql_stmt , dbms_sql.v7 );
486 bind_variables(
487 p_org_id,
488 p_cust_num_low ,
489 p_cust_num_high ,
490 p_bill_site_low,
491 p_bill_site_high,
492 p_bill_date_low,
493 p_bill_date_high,
494 p_bill_num_low,
495 p_bill_num_high,
496 p_request_id,
497 sql_stmt_c);
498
499 dbms_sql.define_column( sql_stmt_c, 1, sql_stmt_rec.language, 4);
500
501 l_ignore := dbms_sql.execute( sql_stmt_c);
502
503 LOOP
504 IF (dbms_sql.fetch_rows( sql_stmt_c) > 0)
505 THEN
506
507 ------------------------------------------------------
508 -- Get column values
509 ------------------------------------------------------
510 dbms_sql.column_value( sql_stmt_c, 1, sql_stmt_rec.language );
511
512 IF (lang_str is null) THEN
513 lang_str := sql_stmt_rec.language;
514 ELSE
515 lang_str := lang_str || ',' || sql_stmt_rec.language;
516 END IF;
517 ELSE
518 EXIT;
519 END IF;
520 END LOOP;
521
522 IF lang_str IS NULL THEN
523 fnd_file.put_line( fnd_file.log, 'No documents matched the input parameters.' );
524 ELSE
525 fnd_file.put_line( fnd_file.log, 'lang_str: ' || lang_str);
526 END IF;
527
528 RETURN lang_str;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 fnd_file.put_line( fnd_file.log, sql_stmt);
533 RAISE;
534
535 END PRINT_MLS_FUNCTION ;
536
537 PROCEDURE PRINT_BILLS(
538 errbuf IN OUT NOCOPY VARCHAR2,
539 retcode IN OUT NOCOPY VARCHAR2,
540 p_org_id IN NUMBER,
541 p_job_size IN NUMBER,
542 p_cust_num_low IN VARCHAR2,
543 p_cust_num_high IN VARCHAR2,
544 p_bill_site_low IN NUMBER,
545 p_bill_site_high IN NUMBER,
546 p_bill_date_low_in IN VARCHAR2,
547 p_bill_date_high_in IN VARCHAR2,
548 p_bill_num_low IN VARCHAR2,
549 p_bill_num_high IN VARCHAR2,
550 p_request_id IN NUMBER,
551 p_template_id IN NUMBER
552 ) IS
553 l_job_size INTEGER := 500;
554 p_bill_date_low date := NULL;
555 p_bill_date_high date := NULL;
556 p_where varchar2(1024);
557
561 retval number;
558 --local variables
559 --base_lang varchar2(4);/*Bug8486880*/
560 userenv_lang varchar2(4);
562 parm_number number;
563 parm_name varchar2(80);
564
565 sql_stmt_c number;
566 sql_stmt varchar2(2048);
567 insert_stmt varchar2(240);
568 select_stmt varchar2(2048);
569
570 inserted_row_counts INTEGER;
571 row_counts_perworker number;
572 divided_worker_counts number := 1;
573
574 -- variable used for concurrent program
575 req_data varchar2(240);
576 l_request_id number; -- child request id
577 m_request_id number; -- parent request id
578
579 l_low_range NUMBER := 1;
580 l_high_range NUMBER := 1;
581 l_worker_id NUMBER := 1;
582
583 cnt_warnings INTEGER := 0;
584 cnt_errors INTEGER := 0;
585 request_status BOOLEAN;
586 return_stat VARCHAR2(2000);
587 l_fail_count NUMBER := 0;
588
589 BEGIN
590
591 MO_global.init('AR');
592 FND_FILE.PUT_LINE( FND_FILE.LOG, 'AR_BPA_BFPRI_CONC.print_bills(+)' );
593
594 -- read the variable request_data to check if it is reentering the program
595 req_data := fnd_conc_global.request_data;
596 m_request_id := fnd_global.conc_request_id;
597
598 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_bills: ' || 'req_data: '|| req_data );
599 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_bills: ' || 'm_request_id: '|| m_request_id );
600 IF (req_data is null) THEN
601 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_bills: '
602 || 'Entering print master program at the first time');
603 -- read the user env language
604 select substr(userenv('LANG'),1,4)
605 into userenv_lang
606 from dual;
607
608 /*Bug8486880
609 select language_code
610 into base_lang
611 from fnd_languages
612 where installed_flag = 'B';*/
613
614 FND_FILE.PUT_LINE( FND_FILE.LOG, 'userenv_lang: '|| userenv_lang );
615 --fnd_file.put_line( fnd_file.log, 'base_lang: ' || base_lang);
616
617 if p_job_size > 0 then l_job_size := p_job_size; end if;
618 p_bill_date_high := fnd_date.canonical_to_date(p_bill_date_high_in);
619 p_bill_date_low := fnd_date.canonical_to_date(p_bill_date_low_in);
620 -- print out the input parameters;
621 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_org_id: '|| p_org_id );
622 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_job_size: '|| l_job_size );
623 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_cust_num_low: '|| p_cust_num_low );
624 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_cust_num_high: '|| p_cust_num_high );
625 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_bill_site_low: '|| p_bill_site_low );
626 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_bill_site_high: '|| p_bill_site_high );
627 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_bill_date_low: '|| p_bill_date_low );
628 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_bill_date_high: '|| p_bill_date_high );
629 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_bill_num_low: '|| p_bill_num_low );
630 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_bill_num_high: '|| p_bill_num_high );
631 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_request_id: '|| p_request_id );
632 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_template_id: '|| p_template_id );
633
634 -- fetch a list of billing document id based on the inputted parameters
635 -- and insert into the ar_bpa_print_requests table
636
637 insert_stmt := ' insert into ar_bpa_print_requests (request_id, payment_schedule_id,
638 worker_id, created_by, creation_date,last_updated_by, last_update_date)';
639
640 /*Bug 8486880*/
641 select_stmt := ' select ' || m_request_id || ', to_number(cons_billing_number), rownum, 1, sysdate, 1, sysdate from '
642 || cr ||' ( select cons.cons_billing_number '|| cr || build_from_clause ||
643 ' AND nvl(loc.language,' || '''' || userenv_lang || ''') = ' || '''' || userenv_lang || '''' ;
644
645 Build_where_clause(
646 p_org_id,
647 p_cust_num_low ,
648 p_cust_num_high ,
649 p_bill_site_low,
650 p_bill_site_high,
651 p_bill_date_low,
652 p_bill_date_high,
653 p_bill_num_low,
654 p_bill_num_high,
655 p_request_id,
656 p_where) ;
657
658
659 sql_stmt := insert_stmt || cr || select_stmt || cr || p_where || ')';
660
661 -- fnd_file.put_line( fnd_file.log, sql_stmt);
662 ------------------------------------------------
663 -- Parse sql stmts
664 ------------------------------------------------
665
666 sql_stmt_c:= dbms_sql.open_cursor;
667
668 dbms_sql.parse( sql_stmt_c, sql_stmt , dbms_sql.v7 );
669
670 bind_variables(
671 p_org_id,
672 p_cust_num_low ,
673 p_cust_num_high ,
674 p_bill_site_low,
675 p_bill_site_high,
676 p_bill_date_low,
677 p_bill_date_high,
678 p_bill_num_low,
679 p_bill_num_high,
680 p_request_id,
681 sql_stmt_c);
682
683 inserted_row_counts := dbms_sql.execute(sql_stmt_c);
684 fnd_file.put_line( fnd_file.log, 'inserted row count: ' || inserted_row_counts);
685
686 IF inserted_row_counts > 0 THEN
687
688 -- update the last printed date for all the transactions that are being printed.
689 -- bug 6955957
690 update ra_customer_trx_all trx set trx.printing_last_printed = sysdate where trx.trx_number in
691 (select cons.trx_number from ar_cons_inv_trx_all cons, ar_bpa_print_requests pri
692 where pri.request_id = m_request_id
696 divided_worker_counts := ceil(inserted_row_counts/l_job_size);
693 and pri.payment_schedule_id = cons.cons_inv_id
694 );
695
697 row_counts_perworker := ceil(inserted_row_counts/divided_worker_counts);
698
699 fnd_file.put_line( fnd_file.log, 'row count per worker: ' || row_counts_perworker);
700 fnd_file.put_line( fnd_file.log, 'divided worker count: ' || divided_worker_counts);
701
702 l_worker_id := 1 ;
703 l_low_range := 1 ;
704 l_high_range := row_counts_perworker ;
705
706 LOOP
707 UPDATE ar_bpa_print_requests
708 SET worker_id = l_worker_id
709 WHERE request_id = m_request_id
710 AND worker_id BETWEEN l_low_range AND l_high_range;
711
712 IF l_worker_id >= divided_worker_counts THEN
713 EXIT;
714 END IF;
715
716 l_worker_id := l_worker_id + 1;
717 l_low_range := l_low_range + row_counts_perworker ;
718 l_high_range := l_high_range + row_counts_perworker ;
719
720 END LOOP;
721 commit; -- commit the record here
722
723 FOR no_of_workers in 1 .. divided_worker_counts
724 LOOP
725 l_request_id := submit_print_request(
726 m_request_id,
727 no_of_workers,
728 '',
729 p_template_id,
730 'Y',
731 '',
732 '','', TRUE);
733 IF (l_request_id = 0) THEN
734 fnd_file.put_line( fnd_file.log, 'can not start for worker_id: ' ||no_of_workers );
735 FND_MESSAGE.RETRIEVE(return_stat);
736 fnd_file.put_line( fnd_file.log, 'Error occured : ' ||return_stat );
737 l_fail_count := l_fail_count + 1;
738 ELSE
739 commit;
740 fnd_file.put_line( fnd_file.log, 'child request id: ' ||
741 l_request_id || ' started for worker_id: ' ||no_of_workers );
742 END IF;
743 END LOOP;
744
745 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
746 request_data => to_char(inserted_row_counts));
747 fnd_file.put_line( fnd_file.log, 'The Master program changed status to pause and wait for child processes');
748 ELSE
749 fnd_file.new_line( fnd_file.log,1 );
750 fnd_file.put_line( fnd_file.log, 'No bills matched the input parameters.');
751 fnd_file.new_line( fnd_file.log,1 );
752 END IF;
753
754 ELSE
755
756 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_bills: '
757 || 'Entering print master program at the second time');
758 fnd_file.put_line( fnd_file.output,
759 arp_standard.fnd_message('AR_BPA_PRINT_OUTPUT_HDR',
760 'NUM_OF_WORKER',
761 divided_worker_counts,
762 'TRX_COUNT',
763 req_data));
764
765 IF divided_worker_counts > 0
766 THEN
767 DECLARE
768 CURSOR child_request_cur(p_request_id IN NUMBER) IS
769 SELECT request_id, status_code
770 FROM fnd_concurrent_requests
771 WHERE parent_request_id = p_request_id;
772 BEGIN
773 FOR child_request_rec IN child_request_cur(m_request_id)
774 LOOP
775 check_child_request(child_request_rec.request_id);
776 IF ( child_request_rec.status_code = 'G' OR child_request_rec.status_code = 'X'
777 OR child_request_rec.status_code ='D' OR child_request_rec.status_code ='T' ) THEN
778 cnt_warnings := cnt_warnings + 1;
779 ELSIF ( child_request_rec.status_code = 'E' ) THEN
780 cnt_errors := cnt_errors + 1;
781 END IF;
782 END LOOP;
783
784 IF ((cnt_errors > 0) OR ( l_fail_count = divided_worker_counts ))
785 THEN
786 request_status := fnd_concurrent.set_completion_status('ERROR', '');
787 ELSIF ((cnt_warnings > 0) OR (l_fail_count > 0) )
788 THEN
789 request_status := fnd_concurrent.set_completion_status('WARNING', '');
790 ELSE
791 request_status := fnd_concurrent.set_completion_status('NORMAL', '');
792 END IF;
793 END;
794 END IF;
795
796 DELETE FROM ar_bpa_print_requests
797 WHERE request_id = m_request_id;
798
799 COMMIT;
800
801 END IF;
802
803 FND_FILE.PUT_LINE( FND_FILE.LOG, 'AR_BPA_BFPRI_CONC.print_bills(-)' );
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 RAISE;
808 END PRINT_BILLS;
809
810 PROCEDURE process_print_request( p_id_list IN VARCHAR2,
811 x_req_id_list OUT NOCOPY VARCHAR2,
812 p_description IN VARCHAR2 ,
813 p_template_id IN NUMBER,
814 p_stamp_flag IN VARCHAR2
815 )
816 IS
817 TYPE lang_cur is REF CURSOR;
818 lang_cv lang_cur;
819
820 lang_selector VARCHAR2(1024);
821 lang_code VARCHAR2(4);
822 base_lang VARCHAR2(4);
823 nls_lang VARCHAR2(30);
824 nls_terr VARCHAR2(30);
825
829 ps_id dbms_sql.number_table;
826 select_stmt VARCHAR2(1024);
827 select_cur INTEGER;
828
830
831 inserted_row_counts INTEGER;
832 fetched_row_count INTEGER;
833 ignore INTEGER;
834
835 row_counts_perworker number;
836 divided_worker_counts number := 1;
837
838 l_request_id number; -- child request id
839
840 l_low_range NUMBER := 1;
841 l_high_range NUMBER := 1;
842
843 l_fail_flag VARCHAR2(1) ;
844
845 BEGIN
846
847 SELECT language_code
848 INTO base_lang
849 FROM fnd_languages
850 WHERE installed_flag = 'B';
851
852
853 lang_selector := ' select distinct(nvl(rtrim(substr(a_bill.language,1,4)), '''
854 || base_lang || ''')) language ' || cr || build_from_clause
855 || ' AND cons.cons_inv_id in ('|| p_id_list || ' )' ;
856
857
858 OPEN lang_cv FOR lang_selector;
859
860 LOOP
861
862 FETCH lang_cv INTO lang_code;
863 EXIT WHEN lang_cv%NOTFOUND;
864
865 SELECT nls_language, nls_territory
866 INTO nls_lang, nls_terr
867 FROM FND_LANGUAGES
868 WHERE language_code = lang_code;
869
870 select_stmt := ' SELECT to_number(cons.cons_billing_number) ' || cr || build_from_clause || cr ||
871 ' AND cons.cons_inv_id in ('|| p_id_list || ' ) ' || cr ||
872 ' AND nvl(a_bill.language, ''' || base_lang ||''' ) = :lang_code ';
873
874 select_cur := dbms_sql.open_cursor;
875 dbms_sql.parse( select_cur, select_stmt, dbms_sql.native );
876
877 dbms_sql.bind_variable(select_cur,':lang_code', lang_code );
878 dbms_sql.define_array(select_cur,1,ps_id,500,1 );
879 ignore := dbms_sql.execute(select_cur);
880
881 LOOP
882 fetched_row_count := dbms_sql.fetch_rows(select_cur);
883 dbms_sql.column_value(select_cur,1,ps_id);
884
885 EXIT WHEN fetched_row_count <> 500 ;
886 END LOOP;
887 dbms_sql.close_cursor(select_cur);
888
889 inserted_row_counts := ps_id.COUNT ;
890
891 divided_worker_counts := ceil(inserted_row_counts/500);
892 row_counts_perworker := ceil(inserted_row_counts/divided_worker_counts);
893
894 l_low_range := 1 ;
895 l_high_range := row_counts_perworker ;
896
897
898 FOR no_of_workers in 1 .. divided_worker_counts
899 LOOP
900
901 -- When parent request id is passed as -1, child
902 -- request uses its request id to pick data.
903
904 l_request_id := submit_print_request(
905 -1,
906 no_of_workers,
907 '',
908 p_template_id,
909 p_stamp_flag,
910 '',
911 nls_lang ,
912 nls_terr,
913 FALSE,
914 p_description);
915
916 IF l_request_id = 0
917 THEN
918 l_fail_flag := 'Y';
919 ELSIF x_req_id_list IS NULL THEN
920 x_req_id_list := l_request_id;
921 ELSE
922 x_req_id_list := x_req_id_list ||','|| l_request_id;
923 END IF;
924
925 FORALL i in l_low_range .. l_high_range
926 INSERT INTO ar_bpa_print_requests (
927 request_id,
928 payment_schedule_id,
929 worker_id,
930 created_by,
931 creation_date,
932 last_updated_by,
933 last_update_date)
934 VALUES (l_request_id,
935 ps_id(i),
936 no_of_workers ,
937 1,
938 sysdate,
939 1,
940 sysdate);
941
942 COMMIT;
943 l_low_range := l_low_range + row_counts_perworker;
944 l_high_range := l_high_range + row_counts_perworker;
945 END LOOP;
946 END LOOP;
947
948 /* If any time a request failed to submit, then we send the request id
949 list as zero */
950 IF l_fail_flag = 'Y' THEN
951 x_req_id_list := '0';
952 END IF;
953
954 CLOSE lang_cv;
955 EXCEPTION
956 WHEN OTHERS THEN
957 IF dbms_sql.is_open(select_cur) THEN
958 dbms_sql.close_cursor(select_cur);
959 END IF;
960 IF lang_cv%ISOPEN THEN
961 CLOSE lang_cv;
962 END IF;
963 RAISE;
964 END process_print_request;
965
966 END AR_BPA_BFPRI_CONC;