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