[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_PRINT_CONC
Source
1 PACKAGE BODY AR_BPA_PRINT_CONC AS
2 /* $Header: ARBPPRIB.pls 120.23.12000000.4 2007/10/16 10:48:59 tthangav 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(8096);
11
12 BEGIN
13
14 from_clause := ' FROM ' || cr ||
15 ' ar_payment_schedules_all ps, ' || cr ||
16 ' ra_customer_trx trx, ' || cr ||
17 ' ra_terms_tl t, ' || cr ||
18 ' ra_terms_b b, ' || cr ||
19 ' ra_cust_trx_types_all trx_type, ' || cr ||
20 ' hz_cust_accounts_all b_bill, ' || cr ||
21 ' hz_parties b_bill_party, ' || cr ||
22 ' hz_cust_acct_sites_all a_bill, ' || cr ||
23 ' hz_party_sites a_bill_ps,' || cr ||
24 ' hz_locations a_bill_loc, ' || cr ||
25 ' hz_cust_site_uses_all u_bill ' || cr ||
26 ' WHERE ' || cr ||
27 ' trx.cust_trx_type_id = trx_type.cust_trx_type_id ' || cr ||
28 ' AND trx.org_id = trx_type.org_id ' || cr ||
29 ' AND trx.customer_trx_id = ps.customer_trx_id ' || cr ||
30 ' AND trx.org_id = ps.org_id ' || cr ||
31 ' AND trx.printing_option = ' || '''' || 'PRI' || '''' || cr ||
32 ' AND trx.bill_to_customer_id = b_bill.cust_account_id ' || cr ||
33 ' ANd b_bill.party_id = b_bill_party.party_id ' || cr ||
34 ' AND trx.bill_to_site_use_id = u_bill.site_use_id ' || cr ||
35 ' AND trx.org_id = u_bill.org_id ' || cr ||
36 ' AND u_bill.cust_acct_site_id = a_bill.cust_acct_site_id(+) ' || cr ||
37 ' AND u_bill.org_id = a_bill.org_id(+) ' || cr ||
38 ' AND a_bill.party_site_id = a_bill_ps.party_site_id(+) ' || cr ||
39 ' AND trx.term_id = b.term_id(+) ' || cr ||
40 ' AND trx.term_id = t.term_id(+) ' || cr ||
41 ' AND t.language(+) = userenv (' || '''' || 'LANG' || ''')' || cr ||
42 ' AND b.billing_cycle_id is null ' || cr ||
43 ' AND a_bill_loc.location_id(+) = a_bill_ps.location_id ' ;
44
45 return from_clause;
46
47 END;
48
49 PROCEDURE check_child_request(
50 p_request_id IN OUT NOCOPY NUMBER
51 ) IS
52
53 call_status boolean;
54 rphase varchar2(80);
55 rstatus varchar2(80);
56 dphase varchar2(30);
57 dstatus varchar2(30);
58 message varchar2(240);
59
60 BEGIN
61 call_status := fnd_concurrent.get_request_status(
62 p_request_id,
63 '',
64 '',
65 rphase,
66 rstatus,
67 dphase,
68 dstatus,
69 message);
70
71 fnd_file.put_line( fnd_file.output, arp_standard.fnd_message('AR_BPA_PRINT_CHILD_REQ',
72 'REQ_ID',
73 p_request_id,
74 'PHASE',
75 dphase,
76 'STATUS',
77 dstatus));
78
79 IF ((dphase = 'COMPLETE') and (dstatus = 'NORMAL')) THEN
80 fnd_file.put_line( fnd_file.log, 'child request id: ' || p_request_id || ' complete successfully');
81 ELSE
82 fnd_file.put_line( fnd_file.log, 'child request id: ' || p_request_id || ' did not complete successfully');
83 END IF;
84
85 END;
86
87
88 FUNCTION submit_print_request(
89 p_parent_request_id IN NUMBER,
90 p_worker_id IN NUMBER,
91 p_order_by IN VARCHAR2,
92 p_template_id IN NUMBER,
93 p_stamp_flag IN VARCHAR2,
94 p_child_template_id IN NUMBER,
95 p_locale IN VARCHAR2,
96 p_index_flag IN VARCHAR2,
97 p_nls_lang IN VARCHAR2,
98 p_nls_territory IN VARCHAR2,
99 p_sub_request_flag IN BOOLEAN,
100 p_description IN VARCHAR2 DEFAULT NULL
101 ) RETURN NUMBER IS
102
103 l_options_ok BOOLEAN;
104 m_request_id NUMBER;
105 number_of_copies number;
106 printer VARCHAR2(30);
107 print_style VARCHAR2(30);
108 save_output_flag VARCHAR2(30);
109 save_output_bool BOOLEAN;
110 print_opt_populated BOOLEAN;
111
112 BEGIN
113
114 l_options_ok := FND_REQUEST.SET_OPTIONS (
115 implicit => 'NO'
116 , protected => 'YES'
117 , language => p_nls_lang
118 , territory => p_nls_territory);
119 IF (l_options_ok)
120 THEN
121
122 IF( FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(p_parent_request_id,
123 number_of_copies,
124 print_style,
125 printer,
126 save_output_flag))THEN
127
128 IF (save_output_flag = 'Y') THEN
129 save_output_bool := TRUE;
130 ELSE
131 save_output_bool := FALSE;
132 END IF;
133
134 if (not FND_REQUEST.set_print_options(printer => printer,
135 style => print_style,
136 copies => number_of_copies,
137 save_output => save_output_bool)) then
138 print_opt_populated := false;
139 else
140 print_opt_populated := true;
141 end if;
142
143 End IF;
144
145 m_request_id := FND_REQUEST.SUBMIT_REQUEST(
146 application => 'AR'
147 , program => 'ARBPIPCP'
148 , description => p_description
149 , start_time => ''
150 , sub_request => p_sub_request_flag
151 , argument1 => p_parent_request_id
152 , argument2 => p_worker_id
153 , argument3 => p_order_by
154 , argument4 => p_template_id
155 , argument5 => p_stamp_flag
156 , argument6 => p_child_template_id
157 , argument7 => 222
158 , argument8 => p_locale
159 , argument9 => p_index_flag
160 , argument10 => chr(0)
161 , argument11 => ''
162 , argument12 => ''
163 , argument13 => ''
164 , argument14 => ''
165 , argument15 => ''
166 , argument16 => ''
167 , argument17 => ''
168 , argument18 => ''
169 , argument19 => ''
170 , argument20 => ''
171 , argument21 => ''
172 , argument22 => ''
173 , argument23 => ''
174 , argument24 => ''
175 , argument25 => ''
176 , argument26 => ''
177 , argument27 => ''
178 , argument28 => ''
179 , argument29 => ''
180 , argument30 => ''
181 , argument31 => ''
182 , argument32 => ''
183 , argument33 => ''
184 , argument34 => ''
185 , argument35 => ''
186 , argument36 => ''
187 , argument37 => ''
188 , argument38 => ''
189 , argument39 => ''
190 , argument40 => ''
191 , argument41 => ''
192 , argument42 => ''
193 , argument43 => ''
194 , argument44 => ''
195 , argument45 => ''
196 , argument46 => ''
197 , argument47 => ''
198 , argument48 => ''
199 , argument49 => ''
200 , argument50 => ''
201 , argument51 => ''
202 , argument52 => ''
203 , argument53 => ''
204 , argument54 => ''
205 , argument55 => ''
206 , argument56 => ''
207 , argument57 => ''
208 , argument58 => ''
209 , argument59 => ''
210 , argument61 => ''
211 , argument62 => ''
212 , argument63 => ''
213 , argument64 => ''
214 , argument65 => ''
215 , argument66 => ''
216 , argument67 => ''
217 , argument68 => ''
218 , argument69 => ''
219 , argument70 => ''
220 , argument71 => ''
221 , argument72 => ''
222 , argument73 => ''
223 , argument74 => ''
224 , argument75 => ''
225 , argument76 => ''
226 , argument77 => ''
227 , argument78 => ''
228 , argument79 => ''
229 , argument80 => ''
230 , argument81 => ''
231 , argument82 => ''
232 , argument83 => ''
233 , argument84 => ''
234 , argument85 => ''
235 , argument86 => ''
236 , argument87 => ''
237 , argument88 => ''
238 , argument89 => ''
239 , argument90 => ''
240 , argument91 => ''
241 , argument92 => ''
242 , argument93 => ''
243 , argument94 => ''
244 , argument95 => ''
245 , argument96 => ''
246 , argument97 => ''
247 , argument98 => ''
248 , argument99 => ''
249 , argument100 => '');
250 END IF;
251
252 RETURN m_request_id;
253
254 END;
255
256 PROCEDURE build_where_clause(
257 p_org_id IN NUMBER DEFAULT NULL,
258 p_choice IN VARCHAR2,
259 p_batch_id IN NUMBER DEFAULT NULL,
260 p_cust_trx_class IN VARCHAR2 DEFAULT NULL,
261 p_trx_type_id IN NUMBER DEFAULT NULL,
262 p_trx_number_low IN VARCHAR2 DEFAULT NULL,
263 p_trx_number_high IN VARCHAR2 DEFAULT NULL,
264 p_print_date_low IN DATE DEFAULT NULL,
265 p_print_date_high IN DATE DEFAULT NULL,
266 p_customer_class_code IN VARCHAR2 DEFAULT NULL,
267 p_customer_no_low IN VARCHAR2 DEFAULT NULL,
268 p_customer_no_high IN VARCHAR2 DEFAULT NULL,
269 p_customer_name_low IN VARCHAR2 DEFAULT NULL,
270 p_customer_name_high IN VARCHAR2 DEFAULT NULL,
271 p_installment_no IN NUMBER DEFAULT NULL,
272 p_open_invoice_flag IN VARCHAR2 DEFAULT NULL,
273 p_invoice_list_string IN VARCHAR2 DEFAULT NULL,
274 p_union_flag IN VARCHAR2 DEFAULT NULL,
275 where_clause OUT NOCOPY VARCHAR2) IS
276
277 BEGIN
278
279
280 /*------------------------------------------------------------------------+
281 | Build where clause depending on passed parameters. |
282 | Operating Unit p_org_id |
283 | Transactions to Print p_choice = NEW / ANY / OLD |
284 | Customer Class p_customer_class_code |
285 | (High) Bill TO Customer Name p_customer_name_high |
286 | (Low) Bill TO Customer Name p_customer_name_low |
287 | (High) Bill To Customer Numberp_customer_no_high |
288 | (Low) Bill To Customer Number p_customer_no_low |
289 | Transaction Class p_cust_trx_class |
290 | Transaction Type p_cust_trx_type_id |
291 | (High) Transaction Number p_trx_number_high |
292 | (Low) Transaction Number p_trx_number_low |
293 | Installment Number p_installment_no |
294 | Open Invoices Only p_open_invoice_flag |
295 | Batch p_batch_id |
296 | (High) Print Date p_print_date_high |
297 | (Low) Print Date p_print_date_low |
298 | Order By p_order_by |
299 | Invoice Trx Id List p_invoice_list_string |
300 --------------------------------------------------------------------------*/
301
302 IF (p_choice = 'NEW' ) THEN
303 where_clause :=where_clause || ' AND nvl(trx.printing_pending, ' ||'''' || 'N' ||'''' ||' ) = ' || '''' || 'Y' ||'''' ||
304 ' AND ps.terms_sequence_number > NVL(TRX.LAST_PRINTED_SEQUENCE_NUM,0) ' ;
305 ELSIF (p_choice = 'OLD' ) THEN
306 where_clause :=where_clause || ' AND NVL(trx.last_printed_sequence_num, 0) >= ps.terms_sequence_number '; --bug 6130518
307 END IF;
308
309 IF ( p_org_id is not null ) THEN
310 where_clause :=where_clause || ' AND trx.org_id = :org_id ' ;
311 END IF;
312
313 IF ( p_customer_class_code is not null ) THEN
314 where_clause :=where_clause || ' AND b_bill.customer_class_code = :customer_class_code ' ;
315 END IF;
316
317 IF ( p_customer_name_low is not null and p_customer_name_high is null )THEN
318 where_clause :=where_clause || ' AND b_bill_party.party_name = :customer_name_low ';
319 ELSIF ( (p_customer_name_high is not null) and (p_customer_name_low is null ) ) THEN
320 where_clause :=where_clause || ' AND b_bill_party.party_name = :customer_name_high ';
321 ELSIF ( (p_customer_name_high is not null) and (p_customer_name_low is not null) ) THEN
322 where_clause :=where_clause || ' AND b_bill_party.party_name >= :customer_name_low ';
323 where_clause :=where_clause || ' AND b_bill_party.party_name <= :customer_name_high ';
324 END IF;
325
326 IF ( (p_customer_no_low is not null) and (p_customer_no_high is null) ) THEN
327 where_clause :=where_clause || ' AND b_bill.account_number = :customer_no_low ' ;
328 ELSIF ( (p_customer_no_high is not null) and (p_customer_no_low is null) ) THEN
329 where_clause :=where_clause || ' AND b_bill.account_number = :customer_no_high ';
330 ELSIF ( (p_customer_no_high is not null) and (p_customer_no_low is not null) ) THEN
331 where_clause :=where_clause || ' AND b_bill.account_number >= :customer_no_low ';
332 where_clause :=where_clause || ' AND b_bill.account_number <= :customer_no_high ' ;
333 END IF;
334
335 IF ( p_cust_trx_class is not null ) THEN
336 where_clause :=where_clause || ' AND trx_type.type = :cust_trx_class ';
337 END IF;
338
339 IF ( p_trx_type_id is not null ) THEN
340 where_clause :=where_clause || ' AND trx_type.cust_trx_type_id = :trx_type_id ' ;
341 END IF;
342
343 IF ( (p_trx_number_low is not null) and (p_trx_number_high is null )) THEN
344 where_clause :=where_clause || ' AND trx.trx_number = :trx_number_low ';
348 where_clause :=where_clause || ' AND trx.trx_number >= :trx_number_low ';
345 ELSIF ( (p_trx_number_high is not null) and (p_trx_number_low is null) ) THEN
346 where_clause :=where_clause || ' AND trx.trx_number = :trx_number_high ';
347 ELSIF ( (p_trx_number_high is not null ) and (p_trx_number_low is not null) ) THEN
349 where_clause :=where_clause || ' AND trx.trx_number <= :trx_number_high ';
350 END IF;
351
352 IF ( (p_installment_no is not null) ) THEN
353 where_clause :=where_clause || ' AND ps.terms_sequence_number = :installment_no ' ;
354 END IF;
355
356 IF ( p_open_invoice_flag = 'Y' ) THEN
357 where_clause :=where_clause || ' AND ps.AMOUNT_DUE_REMAINING <> 0 ' ;
358 END IF;
359
360 IF ( p_batch_id is not null ) THEN
361 where_clause := where_clause || ' AND trx.batch_id = :batch_id ' ;
362 END IF;
363
364 IF ( p_invoice_list_string is not null ) THEN
365 where_clause :=where_clause || ' AND trx.customer_trx_id in ( ' || p_invoice_list_string||' )';
366 END IF;
367
368
369 /**********************************************************************************
370 Handle Print Lead Days IF date range is provided. IF the invoice you are printing
371 has a payment term where Print Lead Days is 0, Receivables uses the transaction date
372 to determine IF this transaction falls into the Start and END Date range you specIFy.
373 IF the invoice you are printing has a payment term where Print Lead Days is greater
374 than 0, Receivables uses the FORmula Due Date - Print Lead Days to determine IF this
375 transaction is to be printed
376 ************************************************************************************/
377
378 IF ( (p_print_date_low is not null) or (p_print_date_low is not null) ) THEN
379 IF ( p_union_flag = 'N' ) THEN
380 where_clause := where_clause || ' AND nvl(b.printing_lead_days,0) = 0 ' ;
381 IF ( p_print_date_low is not null and p_print_date_high is not null ) THEN
382 where_clause := where_clause || ' AND trx.TRX_DATE BETWEEN TO_DATE(:print_date_low, ';
383 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
384 where_clause := where_clause || ' AND TO_DATE(:print_date_high, ';
385 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
386 ELSIF ( p_print_date_low is not null and p_print_date_high is null) THEN
387 where_clause := where_clause || 'AND trx.TRX_DATE >= TO_DATE(:print_date_low, ';
388 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
389 ELSIF ( p_print_date_high is not null and p_print_date_low is null ) THEN
390 where_clause := where_clause || 'AND trx.TRX_DATE <= TO_DATE(:print_date_high, ';
391 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
392 END IF;
393 ELSE
394 where_clause :=where_clause || ' AND b.printing_lead_days > 0 ' ;
395 IF ( p_print_date_low is not null and p_print_date_high is not null ) THEN
396 where_clause := where_clause || 'AND ps.DUE_DATE BETWEEN TO_DATE(:print_date_low, ';
397 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
398 where_clause := where_clause || ' + NVL (B.PRINTING_LEAD_DAYS, 0)';
399 where_clause := where_clause || ' AND TO_DATE(:print_date_high, ';
400 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
401 where_clause := where_clause || ' + NVL (B.PRINTING_LEAD_DAYS, 0)';
402 ELSIF ( p_print_date_low is not null and p_print_date_high is null ) THEN
403 where_clause := where_clause || 'AND ps.DUE_DATE >= TO_DATE(:print_date_low, ';
404 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'') + NVL (B.PRINTING_LEAD_DAYS, 0)';
405 ELSIF ( p_print_date_high is not null and p_print_date_low is null ) THEN
406 where_clause := where_clause || 'AND ps.DUE_DATE <= TO_DATE(:print_date_high, ';
407 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'') + NVL (B.PRINTING_LEAD_DAYS, 0)';
408 END IF;
409 END IF;
410 END IF;
411
412
413 END BUILD_WHERE_CLAUSE;
414
415 PROCEDURE BIND_VARIABLES(
416 p_org_id IN NUMBER DEFAULT NULL,
417 p_choice IN VARCHAR2,
418 p_batch_id IN NUMBER DEFAULT NULL,
419 p_cust_trx_class IN VARCHAR2 DEFAULT NULL,
420 p_trx_type_id IN NUMBER DEFAULT NULL,
421 p_trx_number_low IN VARCHAR2 DEFAULT NULL,
422 p_trx_number_high IN VARCHAR2 DEFAULT NULL,
423 p_print_date_low IN DATE DEFAULT NULL,
424 p_print_date_high IN DATE DEFAULT NULL,
425 p_customer_class_code IN VARCHAR2 DEFAULT NULL,
426 p_customer_no_low IN VARCHAR2 DEFAULT NULL,
427 p_customer_no_high IN VARCHAR2 DEFAULT NULL,
428 p_customer_name_low IN VARCHAR2 DEFAULT NULL,
429 p_customer_name_high IN VARCHAR2 DEFAULT NULL,
430 p_installment_no IN NUMBER DEFAULT NULL,
431 p_open_invoice_flag IN VARCHAR2 DEFAULT NULL,
432 p_invoice_list_string IN VARCHAR2 DEFAULT NULL,
433 p_union_flag IN VARCHAR2 DEFAULT NULL,
434 cursor_name IN INTEGER ) IS
435
436 BEGIN
437
438
439 /*------------------------------------------------------------------------+
440 | Bind clause depending on passed parameters. |
441 | Operating Unit p_org_id |
442 | Transactions to Print p_choice = NEW / ANY / OLD |
446 | (High) Bill To Customer Numberp_customer_no_high |
443 | Customer Class p_customer_class_code |
444 | (High) Bill TO Customer Name p_customer_name_high |
445 | (Low) Bill TO Customer Name p_customer_name_low |
447 | (Low) Bill To Customer Number p_customer_no_low |
448 | Transaction Class p_cust_trx_class |
449 | Transaction Type p_cust_trx_type_id |
450 | (High) Transaction Number p_trx_number_high |
451 | (Low) Transaction Number p_trx_number_low |
452 | Installment Number p_installment_no |
453 | Open Invoices Only p_open_invoice_flag |
454 | Batch p_batch_id |
455 | (High) Print Date p_print_date_high |
456 | (Low) Print Date p_print_date_low |
457 | Order By p_order_by |
458 | Invoice Trx Id List p_invoice_list_string |
459 --------------------------------------------------------------------------*/
460
461
462 IF ( p_customer_class_code is not null ) THEN
463 dbms_sql.bind_variable( cursor_name, ':customer_class_code', p_customer_class_code) ;
464 END IF;
465
466 IF ( p_org_id is not null ) THEN
467 dbms_sql.bind_variable( cursor_name, ':org_id', p_org_id) ;
468 END IF;
469
470 IF ( p_customer_name_low is not null and p_customer_name_high is null )THEN
471 dbms_sql.bind_variable( cursor_name, ':customer_name_low', p_customer_name_low);
472 ELSIF ( (p_customer_name_high is not null) and (p_customer_name_low is null ) ) THEN
473 dbms_sql.bind_variable( cursor_name, ':customer_name_high', p_customer_name_high );
474 ELSIF ( (p_customer_name_high is not null) and (p_customer_name_low is not null) ) THEN
475 dbms_sql.bind_variable( cursor_name, ':customer_name_low', p_customer_name_low);
476 dbms_sql.bind_variable( cursor_name,':customer_name_high', p_customer_name_high);
477 END IF;
478
479 IF ( (p_customer_no_low is not null) and (p_customer_no_high is null) ) THEN
480 dbms_sql.bind_variable( cursor_name, ':customer_no_low', p_customer_no_low ) ;
481 ELSIF ( (p_customer_no_high is not null) and (p_customer_no_low is null) ) THEN
482 dbms_sql.bind_variable( cursor_name, ':customer_no_high', p_customer_no_high );
483 ELSIF ( (p_customer_no_high is not null) and (p_customer_no_low is not null) ) THEN
484 dbms_sql.bind_variable( cursor_name, ':customer_no_low', p_customer_no_low ) ;
485 dbms_sql.bind_variable( cursor_name, ':customer_no_high', p_customer_no_high ) ;
486 END IF;
487
488 IF ( p_cust_trx_class is not null ) THEN
489 dbms_sql.bind_variable( cursor_name, ':cust_trx_class', p_cust_trx_class );
490 END IF;
491
492 IF ( p_trx_type_id is not null ) THEN
493 dbms_sql.bind_variable( cursor_name, ':trx_type_id', p_trx_type_id ) ;
494 END IF;
495
496 IF ( (p_trx_number_low is not null) and (p_trx_number_high is null )) THEN
497 dbms_sql.bind_variable( cursor_name, ':trx_number_low', p_trx_number_low);
498 ELSIF ( (p_trx_number_high is not null) and (p_trx_number_low is null) ) THEN
499 dbms_sql.bind_variable( cursor_name, ':trx_number_high', p_trx_number_high);
500 ELSIF ( (p_trx_number_high is not null ) and (p_trx_number_low is not null) ) THEN
501 dbms_sql.bind_variable( cursor_name, ':trx_number_low' , p_trx_number_low);
502 dbms_sql.bind_variable( cursor_name, ':trx_number_high', p_trx_number_high);
503 END IF;
504
505 IF ( (p_installment_no is not null) ) THEN
506 dbms_sql.bind_variable( cursor_name, ':installment_no', p_installment_no) ;
507 END IF;
508
509
510 IF ( p_batch_id is not null ) THEN
511 dbms_sql.bind_variable( cursor_name, ':batch_id', p_batch_id ) ;
512 END IF;
513
514
515 /**********************************************************************************
516 Handle Print Lead Days IF date range is provided. IF the invoice you are printing
517 has a payment term where Print Lead Days is 0, Receivables uses the transaction date
518 to determine IF this transaction falls into the Start and END Date range you specIFy.
519 IF the invoice you are printing has a payment term where Print Lead Days is greater
520 than 0, Receivables uses the FORmula Due Date - Print Lead Days to determine IF this
521 transaction is to be printed
522 ************************************************************************************/
523
524 IF ( (p_print_date_low is not null) or (p_print_date_low is not null) ) THEN
525 IF ( p_union_flag = 'N' ) THEN
526 IF ( p_print_date_low is not null and p_print_date_high is not null ) THEN
527 dbms_sql.bind_variable( cursor_name, ':print_date_low' ,
528 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
529 dbms_sql.bind_variable( cursor_name, ':print_date_high' ,
530 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
531 ELSIF ( p_print_date_low is not null and p_print_date_high is null) THEN
532 dbms_sql.bind_variable( cursor_name, ':print_date_low' ,
536 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
533 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
534 ELSIF ( p_print_date_high is not null and p_print_date_low is null ) THEN
535 dbms_sql.bind_variable( cursor_name, ':print_date_high' ,
537 END IF;
538 ELSE
539 IF ( p_print_date_low is not null and p_print_date_high is not null ) THEN
540 dbms_sql.bind_variable( cursor_name, ':print_date_low' ,
541 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
542 dbms_sql.bind_variable( cursor_name, ':print_date_high' ,
543 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
544 ELSIF ( p_print_date_low is not null and p_print_date_high is null ) THEN
545 dbms_sql.bind_variable( cursor_name, ':print_date_low' ,
546 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
547 ELSIF ( p_print_date_high is not null and p_print_date_low is null ) THEN
548 dbms_sql.bind_variable( cursor_name, ':print_date_high' ,
549 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
550 END IF;
551 END IF;
552 END IF;
553
554
555 END BIND_VARIABLES;
556
557
558 function PRINT_MLS_FUNCTION RETURN VARCHAR2 IS
559
560 -- variables used by build_where_clause
561 p_org_id number := NULL;
562 p_job_size number := NULL;
563 p_choice varchar2(40) := NULL;
564 p_order_by varchar2(20) := NULL;
565 p_batch_id number := NULL;
566 p_cust_trx_class VARCHAR2(30) := NULL;
567 p_trx_type_id number := NULL;
568 p_customer_class_code varchar2(40) := NULL;
569 p_customer_name_low varchar2(360) := NULL;
570 p_customer_name_high varchar2(360) := NULL;
571 p_customer_no_low varchar2(30) := NULL;
572 p_customer_no_high varchar2(30) := NULL;
573 p_trx_number_low varchar2(20) := NULL;
574 p_trx_number_high varchar2(20) := NULL;
575 p_installment_no number := NULL;
576 p_print_date_low date := NULL;
577 p_print_date_high date := NULL;
578 p_open_invoice_flag varchar2(1) := NULL;
579 p_invoice_list_string varchar2(2000) := NULL;
580
581
582 p_where1 varchar2(8096);
583 p_where2 varchar2(8096);
584 filter_exists boolean := false;
585
586 --local variables
587 userenv_lang varchar2(4);
588 base_lang varchar2(4);
589 retval number;
590 parm_number number;
591 parm_name varchar2(80);
592
593 sql_stmt_c number;
594 sql_stmt varchar2(8096);
595 select_stmt varchar2(8096);
596 lang_str varchar2(240);
597
598 TYPE sql_stmt_rec_type IS RECORD
599 (language VARCHAR2(4));
600
601 sql_stmt_rec sql_stmt_rec_type ;
602 l_ignore INTEGER;
603
604 BEGIN
605
606 select substr(userenv('LANG'),1,4)
607 into userenv_lang
608 from dual;
609
610 select language_code
611 into base_lang
612 from fnd_languages
613 where installed_flag = 'B';
614
615 MO_global.init('AR');
616 fnd_file.put_line( fnd_file.log, 'userenv_lang: ' || userenv_lang);
617 fnd_file.put_line( fnd_file.log, 'base_lang: ' || base_lang);
618
619 /* Read in Parameter Values supplied by user */
620 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Operating Unit',parm_number);
621 IF retval = -1 THEN
622 p_org_id := NULL;
623 ELSE
624 p_org_id:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
625 END IF;
626 fnd_file.put_line( fnd_file.log, 'p_org_id: ' || p_org_id);
627
628 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Job Size',parm_number);
629 IF retval = -1 THEN
630 p_job_size:= NULL;
631 ELSE
632 p_job_size:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
633 END IF;
634 fnd_file.put_line( fnd_file.log, 'p_job_size: ' || p_job_size);
635
636 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transactions to Print',parm_number);
637 IF retval = -1 THEN
638 p_choice:= NULL;
639 ELSE
640 p_choice:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
641 END IF;
642 fnd_file.put_line( fnd_file.log, 'p_choice: ' || p_choice);
643
644 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Order By',parm_number);
645 IF retval = -1 THEN
646 p_order_by:= NULL;
647 ELSE
648 p_order_by:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
649 END IF;
650 fnd_file.put_line( fnd_file.log, 'p_order_by: ' || p_order_by);
651
652 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Batch',parm_number);
653 IF retval = -1 THEN
654 p_BATCH_ID := NULL;
655 ELSE
656 p_BATCH_ID := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
657 filter_exists := true;
658 END IF;
659 fnd_file.put_line( fnd_file.log, 'p_BATCH_ID: ' || p_BATCH_ID);
660
661 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transaction Class',parm_number);
662 IF retval = -1 THEN
663 p_cust_trx_class := NULL;
664 ELSE
665 p_cust_trx_class := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
666 END IF;
670 IF retval = -1 THEN
667 fnd_file.put_line( fnd_file.log, 'p_cust_trx_class: ' || p_cust_trx_class);
668
669 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transaction Type',parm_number);
671 p_TRX_TYPE_ID := NULL;
672 ELSE
673 p_TRX_TYPE_ID := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
674 END IF;
675 fnd_file.put_line( fnd_file.log, 'p_TRX_TYPE_ID: ' || p_TRX_TYPE_ID);
676
677 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer Class',parm_number);
678 IF retval = -1 THEN
679 p_customer_class_code:= NULL;
680 ELSE
681 p_customer_class_code:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
682 END IF;
683 fnd_file.put_line( fnd_file.log, 'p_customer_class_code: ' || p_customer_class_code);
684
685 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(From) Bill To Customer Name',parm_number);
686 IF retval = -1 THEN
687 p_customer_name_low:= NULL;
688 ELSE
689 p_customer_name_low:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
690 END IF;
691 fnd_file.put_line( fnd_file.log, 'p_customer_name_low: ' || p_customer_name_low);
692
693 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(To) Bill To Customer Name',parm_number);
694 IF retval = -1 THEN
695 p_CUSTOMER_NAME_HIGH := NULL;
696 ELSE
697 p_CUSTOMER_NAME_HIGH := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
698 END IF;
699 fnd_file.put_line( fnd_file.log, 'p_CUSTOMER_NAME_HIGH: ' || p_CUSTOMER_NAME_HIGH);
700
701 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(Low) Bill To Customer Number',parm_number);
702 IF retval = -1 THEN
703 p_customer_no_low:= NULL;
704 ELSE
705 p_customer_no_low:= FND_REQUEST_INFO.GET_PARAMETER(parm_number);
706 END IF;
707 fnd_file.put_line( fnd_file.log, 'p_customer_no_low: ' || p_customer_no_low);
708
709 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(High) Bill To Customer Number',parm_number);
710 IF retval = -1 THEN
711 p_customer_no_high := NULL;
712 ELSE
713 p_customer_no_high := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
714 END IF;
715 fnd_file.put_line( fnd_file.log, 'p_customer_no_high: ' || p_customer_no_high);
716
717 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(Low) Transaction Number',parm_number);
718 IF retval = -1 THEN
719 p_TRX_NUMBER_LOW := NULL;
720 ELSE
721 p_TRX_NUMBER_LOW := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
722 END IF;
723 fnd_file.put_line( fnd_file.log, 'p_TRX_NUMBER_LOW: ' || p_TRX_NUMBER_LOW);
724
725 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(High) Transaction Number',parm_number);
726 IF retval = -1 THEN
727 p_TRX_NUMBER_HIGH := NULL;
728 ELSE
729 p_TRX_NUMBER_HIGH := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
730 END IF;
731 fnd_file.put_line( fnd_file.log, 'p_TRX_NUMBER_HIGH: ' || p_TRX_NUMBER_HIGH);
732
733 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Installment Number',parm_number);
734 IF retval = -1 THEN
735 p_INSTALLMENT_NO := NULL;
736 ELSE
737 p_INSTALLMENT_NO := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
738 END IF;
739 fnd_file.put_line( fnd_file.log, 'p_INSTALLMENT_NO: ' || p_INSTALLMENT_NO);
740
741 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(Low) Print Date',parm_number);
742 IF retval = -1 THEN
743 p_PRINT_DATE_LOW := NULL;
744 ELSE
745 p_PRINT_DATE_LOW := fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
746 END IF;
747 fnd_file.put_line( fnd_file.log, 'p_PRINT_DATE_LOW: ' || p_PRINT_DATE_LOW);
748
749 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('(High) Print Date',parm_number);
750 IF retval = -1 THEN
751 p_PRINT_DATE_HIGH := NULL;
752 ELSE
753 p_PRINT_DATE_HIGH := fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
754 END IF;
755 fnd_file.put_line( fnd_file.log, 'p_PRINT_DATE_HIGH: ' || p_PRINT_DATE_HIGH);
756
757 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Open Invoices Only',parm_number);
758 IF retval = -1 THEN
759 p_OPEN_INVOICE_FLAG := NULL;
760 ELSE
761 p_OPEN_INVOICE_FLAG := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
762 END IF;
763 fnd_file.put_line( fnd_file.log, 'p_OPEN_INVOICE_FLAG: ' || p_OPEN_INVOICE_FLAG);
764
765 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Invoice Trx Id List',parm_number);
766 IF retval = -1 THEN
767 p_INVOICE_LIST_STRING := NULL;
768 ELSE
769 p_INVOICE_LIST_STRING := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
770 END IF;
771 fnd_file.put_line( fnd_file.log, 'p_INVOICE_LIST_STRING: ' || p_INVOICE_LIST_STRING);
772
773
774 select_stmt :=
775 ' select distinct(nvl(rtrim(substr(a_bill.language,1,4)), ''' || base_lang || ''')) language ' || cr ||
776 build_from_clause;
777
778 AR_BPA_PRINT_CONC.Build_where_clause(
779 p_org_id,
780 p_choice ,
781 p_batch_id ,
782 p_cust_trx_class,
783 p_trx_type_id ,
784 p_trx_number_low ,
785 p_trx_number_high ,
786 p_print_date_low ,
787 p_print_date_high ,
788 p_customer_class_code ,
789 p_customer_no_low ,
790 p_customer_no_high ,
791 p_customer_name_low ,
792 p_customer_name_high ,
796 'N' ,
793 p_installment_no ,
794 p_open_invoice_flag ,
795 p_invoice_list_string ,
797 p_where1 ) ;
798
799 sql_stmt := select_stmt || cr || p_where1;
800
801 IF ( p_PRINT_DATE_LOW IS NOT NULL OR p_PRINT_DATE_HIGH IS NOT NULL ) THEN
802 AR_BPA_PRINT_CONC.Build_where_clause(
803 p_org_id,
804 p_choice ,
805 p_batch_id ,
806 p_cust_trx_class,
807 p_trx_type_id ,
808 p_trx_number_low ,
809 p_trx_number_high ,
810 p_print_date_low ,
811 p_print_date_high ,
812 p_customer_class_code ,
813 p_customer_no_low ,
814 p_customer_no_high ,
815 p_customer_name_low ,
816 p_customer_name_high ,
817 p_installment_no ,
818 p_open_invoice_flag ,
819 p_invoice_list_string ,
820 'Y' ,
821 p_where2 ) ;
822 sql_stmt := sql_stmt || cr || ' UNION ' || cr || select_stmt || cr || p_where2 ;
823 END IF;
824
825 --fnd_file.put_line( fnd_file.log, 'The final sql: ' || sql_stmt);
826 ------------------------------------------------
827 -- Parse sql stmts
828 ------------------------------------------------
829
830 sql_stmt_c:= dbms_sql.open_cursor;
831
832 dbms_sql.parse( sql_stmt_c, sql_stmt , dbms_sql.v7 );
833 bind_variables(
834 p_org_id,
835 p_choice ,
836 p_batch_id ,
837 p_cust_trx_class,
838 p_trx_type_id ,
839 p_trx_number_low ,
840 p_trx_number_high ,
841 p_print_date_low ,
842 p_print_date_high ,
843 p_customer_class_code ,
844 p_customer_no_low ,
845 p_customer_no_high ,
846 p_customer_name_low ,
847 p_customer_name_high ,
848 p_installment_no ,
849 p_open_invoice_flag ,
850 p_invoice_list_string ,
851 'N' ,
852 sql_stmt_c );
853
854 IF ( p_print_date_low IS NOT NULL OR p_print_date_high IS NOT NULL ) THEN
855 bind_variables(
856 p_org_id,
857 p_choice ,
858 p_batch_id ,
859 p_cust_trx_class,
860 p_trx_type_id ,
861 p_trx_number_low ,
862 p_trx_number_high ,
863 p_print_date_low ,
864 p_print_date_high ,
865 p_customer_class_code ,
866 p_customer_no_low ,
867 p_customer_no_high ,
868 p_customer_name_low ,
869 p_customer_name_high ,
870 p_installment_no ,
871 p_open_invoice_flag ,
872 p_invoice_list_string ,
873 'Y' ,
874 sql_stmt_c );
875 END IF;
876
877
878 dbms_sql.define_column( sql_stmt_c, 1, sql_stmt_rec.language, 4);
879
880
881 l_ignore := dbms_sql.execute( sql_stmt_c);
882
883 LOOP
884 IF (dbms_sql.fetch_rows( sql_stmt_c) > 0)
885 THEN
886
887 ------------------------------------------------------
888 -- Get column values
889 ------------------------------------------------------
890 dbms_sql.column_value( sql_stmt_c, 1, sql_stmt_rec.language );
891
892 IF (lang_str is null) THEN
893 lang_str := sql_stmt_rec.language;
894 ELSE
895 lang_str := lang_str || ',' || sql_stmt_rec.language;
896 END IF;
897 ELSE
898 EXIT;
899 END IF;
900 END LOOP;
901
902 IF lang_str IS NULL THEN
903 fnd_file.put_line( fnd_file.log, 'No transactions matched the input parameters.' );
904 ELSE
905 fnd_file.put_line( fnd_file.log, 'lang_str: ' || lang_str);
906 END IF;
907
908 RETURN lang_str;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 fnd_file.put_line( fnd_file.log, sql_stmt);
913 RAISE;
914
915 END PRINT_MLS_FUNCTION ;
916
917
918 PROCEDURE PRINT_INVOICES(
919 errbuf IN OUT NOCOPY VARCHAR2,
920 retcode IN OUT NOCOPY VARCHAR2,
921 p_org_id IN NUMBER,
922 p_job_size IN NUMBER,
923 p_choice IN VARCHAR2,
924 p_order_by IN VARCHAR2,
925 p_batch_id IN NUMBER,
926 p_cust_trx_class IN VARCHAR2,
927 p_trx_type_id IN NUMBER,
928 p_customer_class_code IN VARCHAR2,
929 p_customer_name_low IN VARCHAR2,
930 p_customer_name_high IN VARCHAR2,
931 p_customer_no_low IN VARCHAR2,
932 p_customer_no_high IN VARCHAR2,
933 p_trx_number_low IN VARCHAR2,
934 p_trx_number_high IN VARCHAR2,
935 p_installment_no IN NUMBER,
936 p_print_date_low_in IN VARCHAR2,
937 p_print_date_high_in IN VARCHAR2,
938 p_open_invoice_flag IN VARCHAR2,
939 p_invoice_list_string IN VARCHAR2,
940 p_template_id IN NUMBER,
941 p_child_template_id IN NUMBER,
942 p_locale IN VARCHAR2,
943 p_index_flag IN VARCHAR2
944 ) IS
945 l_job_size INTEGER := 500;
946 p_print_date_low date := NULL;
947 p_print_date_high date := NULL;
951
948 p_where1 varchar2(8096);
949 p_where2 varchar2(8096);
950 filter_exists boolean := false;
952 --local variables
953 base_lang varchar2(4);
954 userenv_lang varchar2(4);
955 retval number;
956 parm_number number;
957 parm_name varchar2(80);
958
959 sql_stmt_c number;
960 sql_stmt varchar2(8096);
961 insert_stmt varchar2(240);
962 select_stmt varchar2(8096);
963
964 inserted_row_counts INTEGER;
965 row_counts_perworker number;
966 divided_worker_counts number := 1;
967
968 -- variable used for concurrent program
969 req_data varchar2(240);
970 l_request_id number; -- child request id
971 m_request_id number; -- parent request id
972
973 l_low_range NUMBER := 1;
974 l_high_range NUMBER := 1;
975 l_worker_id NUMBER := 1;
976
977 cnt_warnings INTEGER := 0;
978 cnt_errors INTEGER := 0;
979 request_status BOOLEAN;
980 return_stat VARCHAR2(2000);
981 l_fail_count NUMBER := 0;
982
983 BEGIN
984
985 MO_global.init('AR');
986 FND_FILE.PUT_LINE( FND_FILE.LOG, 'AR_BPA_PRINT_CONC.print_invoices(+)' );
987 -- to check if the output directory exists
988
989 -- read the variable request_data to check if it is reentering the program
990 req_data := fnd_conc_global.request_data;
991 m_request_id := fnd_global.conc_request_id;
992
993 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_invoices: ' || 'req_data: '|| req_data );
994 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_invoices: ' || 'm_request_id: '|| m_request_id );
995 IF (req_data is null) THEN
996 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_invoices: '
997 || 'Entering print master program at the first time');
998 -- read the user env language
999 select substr(userenv('LANG'),1,4)
1000 into userenv_lang
1001 from dual;
1002
1003 select language_code
1004 into base_lang
1005 from fnd_languages
1006 where installed_flag = 'B';
1007
1008 FND_FILE.PUT_LINE( FND_FILE.LOG, 'userenv_lang: '|| userenv_lang );
1009 fnd_file.put_line( fnd_file.log, 'base_lang: ' || base_lang);
1010
1011 if p_job_size > 0 then l_job_size := p_job_size; end if;
1012 p_print_date_high := fnd_date.canonical_to_date(p_print_date_high_in);
1013 p_print_date_low := fnd_date.canonical_to_date(p_print_date_low_in);
1014 -- print out the input parameters;
1015 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_org_id: '|| p_org_id );
1016 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_job_size: '|| l_job_size );
1017 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_choice: '|| p_choice );
1018 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_order_by: '|| p_order_by );
1019 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_BATCH_ID: '|| p_BATCH_ID );
1020 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_cust_trx_class '|| p_cust_trx_class );
1021 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_TRX_TYPE_ID: '|| p_TRX_TYPE_ID );
1022 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_customer_class_code: '|| p_customer_class_code );
1023 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_customer_name_low: '|| p_customer_name_low );
1024 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_CUSTOMER_NAME_HIGH: '|| p_CUSTOMER_NAME_HIGH );
1025 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_customer_no_low: '|| p_customer_no_low );
1026 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_customer_no_high: '|| p_customer_no_high );
1027 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_TRX_NUMBER_LOW: '|| p_TRX_NUMBER_LOW );
1028 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_TRX_NUMBER_HIGH: '|| p_TRX_NUMBER_HIGH );
1029 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_INSTALLMENT_NO: '|| p_INSTALLMENT_NO );
1030 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_PRINT_DATE_LOW: '|| p_PRINT_DATE_LOW );
1031 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_PRINT_DATE_HIGH: '|| p_PRINT_DATE_HIGH );
1032 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_OPEN_INVOICE_FLAG: '|| p_OPEN_INVOICE_FLAG );
1033 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_INVOICE_LIST_STRING: '|| p_INVOICE_LIST_STRING );
1034 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_TEMPLATE_ID: '|| p_template_id );
1035 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_child_TEMPLATE_ID: '|| p_child_template_id );
1036 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_locale: '|| p_locale);
1037 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p_index_flag: '|| p_index_flag);
1038
1039 -- fetch a list of payment schedule id based on the inputted parameters
1040 -- and insert into the ar_bpa_print_requests table
1041
1042 insert_stmt := ' insert into ar_bpa_print_requests (request_id, payment_schedule_id,
1043 worker_id, created_by, creation_date,last_updated_by, last_update_date)';
1044 select_stmt := ' select ' || m_request_id || ', payment_schedule_id, rownum, 1, sysdate, 1, sysdate from '
1045 || cr ||' ( select ps.payment_schedule_id '|| cr || build_from_clause ||
1046 ' AND nvl(a_bill.language,' || '''' || base_lang || ''') = ' || '''' || userenv_lang || '''' ;
1047
1048 AR_BPA_PRINT_CONC.Build_where_clause(
1049 p_org_id,
1050 p_choice ,
1051 p_batch_id ,
1052 p_cust_trx_class,
1053 p_trx_type_id ,
1054 p_trx_number_low ,
1055 p_trx_number_high ,
1056 p_print_date_low ,
1057 p_print_date_high ,
1058 p_customer_class_code ,
1059 p_customer_no_low ,
1060 p_customer_no_high ,
1061 p_customer_name_low ,
1062 p_customer_name_high ,
1063 p_installment_no ,
1064 p_open_invoice_flag ,
1068
1065 p_invoice_list_string ,
1066 'N' ,
1067 p_where1 ) ;
1069 sql_stmt := insert_stmt || cr || select_stmt || cr || p_where1 || ')';
1070
1071 IF ( p_PRINT_DATE_LOW IS NOT NULL OR p_PRINT_DATE_HIGH IS NOT NULL ) THEN
1072 AR_BPA_PRINT_CONC.Build_where_clause(
1073 p_org_id,
1074 p_choice ,
1075 p_batch_id ,
1076 p_cust_trx_class,
1077 p_trx_type_id ,
1078 p_trx_number_low ,
1079 p_trx_number_high ,
1080 p_print_date_low ,
1081 p_print_date_high ,
1082 p_customer_class_code ,
1083 p_customer_no_low ,
1084 p_customer_no_high ,
1085 p_customer_name_low ,
1086 p_customer_name_high ,
1087 p_installment_no ,
1088 p_open_invoice_flag ,
1089 p_invoice_list_string ,
1090 'Y' ,
1091 p_where2 ) ;
1092 sql_stmt := sql_stmt || cr || ' UNION ALL ' || cr || select_stmt || cr || p_where2 || ')';
1093 END IF;
1094
1095 -- IF p_order_by = 'TRX_NUMBER' THEN
1096 -- sql_stmt := sql_stmt || cr || ' ORDER BY ps.trx_number ' ;
1097 -- ELSIF p_order_by = 'CUSTOMER' THEN
1098 -- sql_stmt := sql_stmt || cr || ' ORDER BY substrb(b_bill_party.party_name,1,50) ' ;
1099 -- ELSIF p_order_by = 'POSTAL_CODE' THEN
1100 -- sql_stmt := sql_stmt || cr || ' ORDER BY a_bill_loc.postal_code ' ;
1101 -- END IF;
1102
1103
1104 --fnd_file.put_line( fnd_file.log, sql_stmt);
1105 ------------------------------------------------
1106 -- Parse sql stmts
1107 ------------------------------------------------
1108
1109 sql_stmt_c:= dbms_sql.open_cursor;
1110
1111 dbms_sql.parse( sql_stmt_c, sql_stmt , dbms_sql.v7 );
1112
1113 bind_variables(
1114 p_org_id,
1115 p_choice ,
1116 p_batch_id ,
1117 p_cust_trx_class,
1118 p_trx_type_id ,
1119 p_trx_number_low ,
1120 p_trx_number_high ,
1121 p_print_date_low ,
1122 p_print_date_high ,
1123 p_customer_class_code ,
1124 p_customer_no_low ,
1125 p_customer_no_high ,
1126 p_customer_name_low ,
1127 p_customer_name_high ,
1128 p_installment_no ,
1129 p_open_invoice_flag ,
1130 p_invoice_list_string ,
1131 'N' ,
1132 sql_stmt_c );
1133
1134 IF ( p_print_date_low IS NOT NULL OR p_print_date_high IS NOT NULL ) THEN
1135 bind_variables(
1136 p_org_id,
1137 p_choice ,
1138 p_batch_id ,
1139 p_cust_trx_class,
1140 p_trx_type_id ,
1141 p_trx_number_low ,
1142 p_trx_number_high ,
1143 p_print_date_low ,
1144 p_print_date_high ,
1145 p_customer_class_code ,
1146 p_customer_no_low ,
1147 p_customer_no_high ,
1148 p_customer_name_low ,
1149 p_customer_name_high ,
1150 p_installment_no ,
1151 p_open_invoice_flag ,
1152 p_invoice_list_string ,
1153 'Y' ,
1154 sql_stmt_c );
1155 END IF;
1156
1157
1158 inserted_row_counts := dbms_sql.execute(sql_stmt_c);
1159 fnd_file.put_line( fnd_file.log, 'inserted row count: ' || inserted_row_counts);
1160
1161 IF inserted_row_counts > 0 THEN
1162
1163 divided_worker_counts := ceil(inserted_row_counts/l_job_size);
1164 row_counts_perworker := ceil(inserted_row_counts/divided_worker_counts);
1165
1166 fnd_file.put_line( fnd_file.log, 'row count per worker: ' || row_counts_perworker);
1167 fnd_file.put_line( fnd_file.log, 'divided worker count: ' || divided_worker_counts);
1168
1169 l_worker_id := 1 ;
1170 l_low_range := 1 ;
1171 l_high_range := row_counts_perworker ;
1172
1173 LOOP
1174 UPDATE ar_bpa_print_requests
1175 SET worker_id = l_worker_id
1176 WHERE request_id = m_request_id
1177 AND worker_id BETWEEN l_low_range AND l_high_range;
1178
1179 IF l_worker_id >= divided_worker_counts THEN
1180 EXIT;
1181 END IF;
1182
1183 l_worker_id := l_worker_id + 1;
1184 l_low_range := l_low_range + row_counts_perworker ;
1185 l_high_range := l_high_range + row_counts_perworker ;
1186
1187 END LOOP;
1188 commit; -- commit the record here
1189
1190
1191 FOR no_of_workers in 1 .. divided_worker_counts
1192 LOOP
1193 l_request_id := AR_BPA_PRINT_CONC.submit_print_request(
1194 m_request_id,
1195 no_of_workers,
1196 p_order_by,
1197 p_template_id,
1198 'Y',
1199 p_child_template_id,
1200 p_locale,
1201 p_index_flag,
1202 '','', TRUE);
1203 IF (l_request_id = 0) THEN
1204 fnd_file.put_line( fnd_file.log, 'can not start for worker_id: ' ||no_of_workers );
1205 FND_MESSAGE.RETRIEVE(return_stat);
1209 commit;
1206 fnd_file.put_line( fnd_file.log, 'Error occured : ' ||return_stat );
1207 l_fail_count := l_fail_count + 1;
1208 ELSE
1210 fnd_file.put_line( fnd_file.log, 'child request id: ' ||
1211 l_request_id || ' started for worker_id: ' ||no_of_workers );
1212 END IF;
1213 END LOOP;
1214
1215 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1216 request_data => to_char(inserted_row_counts));
1217 fnd_file.put_line( fnd_file.log, 'The Master program changed status to pause and wait for child processes');
1218 ELSE
1219 fnd_file.new_line( fnd_file.log,1 );
1220 fnd_file.put_line( fnd_file.log, 'No transactions matched the input parameters.');
1221 fnd_file.new_line( fnd_file.log,1 );
1222 END IF;
1223
1224 ELSE
1225
1226 FND_FILE.PUT_LINE( FND_FILE.LOG, 'print_invoices: '
1227 || 'Entering print master program at the second time');
1228 fnd_file.put_line( fnd_file.output,
1229 arp_standard.fnd_message('AR_BPA_PRINT_OUTPUT_HDR',
1230 'NUM_OF_WORKER',
1231 divided_worker_counts,
1232 'TRX_COUNT',
1233 req_data));
1234
1235 IF divided_worker_counts > 0
1236 THEN
1237 DECLARE
1238 CURSOR child_request_cur(p_request_id IN NUMBER) IS
1239 SELECT request_id, status_code
1240 FROM fnd_concurrent_requests
1241 WHERE parent_request_id = p_request_id;
1242 BEGIN
1243 FOR child_request_rec IN child_request_cur(m_request_id)
1244 LOOP
1245 check_child_request(child_request_rec.request_id);
1246 IF ( child_request_rec.status_code = 'G' OR child_request_rec.status_code = 'X'
1247 OR child_request_rec.status_code ='D' OR child_request_rec.status_code ='T' ) THEN
1248 cnt_warnings := cnt_warnings + 1;
1249 ELSIF ( child_request_rec.status_code = 'E' ) THEN
1250 cnt_errors := cnt_errors + 1;
1251 END IF;
1252 END LOOP;
1253
1254 IF ((cnt_errors > 0) OR ( l_fail_count = divided_worker_counts ))
1255 THEN
1256 request_status := fnd_concurrent.set_completion_status('ERROR', '');
1257 ELSIF ((cnt_warnings > 0) OR (l_fail_count > 0) )
1258 THEN
1259 request_status := fnd_concurrent.set_completion_status('WARNING', '');
1260 ELSE
1261 request_status := fnd_concurrent.set_completion_status('NORMAL', '');
1262 END IF;
1263 END;
1264 END IF;
1265
1266 DELETE FROM ar_bpa_print_requests
1267 WHERE request_id = m_request_id;
1268
1269 COMMIT;
1270
1271 END IF;
1272
1273 FND_FILE.PUT_LINE( FND_FILE.LOG, 'AR_BPA_PRINT_CONC.print_invoices(-)' );
1274
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 RAISE;
1278 END PRINT_INVOICES;
1279
1280 FUNCTION GENXSL_MLS_FUNCTION RETURN VARCHAR2 IS
1281 cursor lang_cur is
1282 select language_code from fnd_languages_vl
1283 where installed_flag in ('I', 'B');
1284 lang_str VARCHAR2(240);
1285
1286 BEGIN
1287 for lang_rec in lang_cur
1288 loop
1289 if ( lang_str is null ) then
1290 lang_str := lang_rec.language_code;
1291 else
1292 lang_str := lang_str ||','|| lang_rec.language_code;
1293 end if;
1294 end loop;
1295 return lang_str;
1296 END GENXSL_MLS_FUNCTION ;
1297
1298
1299 PROCEDURE process_print_request( p_id_list IN VARCHAR2,
1300 x_req_id_list OUT NOCOPY VARCHAR2,
1301 p_list_type IN VARCHAR2,
1302 p_description IN VARCHAR2 ,
1303 p_template_id IN NUMBER,
1304 p_stamp_flag IN VARCHAR2,
1305 p_child_template_id IN NUMBER
1306 )
1307 IS
1308 TYPE lang_cur is REF CURSOR;
1309 lang_cv lang_cur;
1310
1311 lang_selector VARCHAR2(8096);
1312 lang_code VARCHAR2(4);
1313 base_lang VARCHAR2(4);
1314 nls_lang VARCHAR2(30);
1315 nls_terr VARCHAR2(30);
1316
1317 select_stmt VARCHAR2(8096);
1318 select_cur INTEGER;
1319
1320 ps_id dbms_sql.number_table;
1321
1322 inserted_row_counts INTEGER;
1323 fetched_row_count INTEGER;
1324 ignore INTEGER;
1325
1326 row_counts_perworker number;
1327 divided_worker_counts number := 1;
1328
1329 l_request_id number; -- child request id
1330
1331 l_low_range NUMBER := 1;
1332 l_high_range NUMBER := 1;
1333
1334 l_fail_flag VARCHAR2(1) ;
1335
1336 BEGIN
1337
1338 SELECT language_code
1339 INTO base_lang
1340 FROM fnd_languages
1341 WHERE installed_flag = 'B';
1342
1343
1344 IF NVL( p_list_type , 'TRX') = 'TRX' THEN
1348 ELSE
1345 lang_selector := ' select distinct(nvl(rtrim(substr(a_bill.language,1,4)), '''
1346 || base_lang || ''')) language ' || cr || build_from_clause
1347 || ' AND trx.customer_trx_id in ('|| p_id_list || ' )' ;
1349 lang_selector := ' select distinct(nvl(rtrim(substr(a_bill.language,1,4)), '''
1350 || base_lang || ''')) language ' || cr || build_from_clause
1351 || ' AND ps.payment_schedule_id in ('|| p_id_list || ' )' ;
1352 END IF;
1353
1354 OPEN lang_cv FOR lang_selector;
1355
1356 LOOP
1357
1358 FETCH lang_cv INTO lang_code;
1359 EXIT WHEN lang_cv%NOTFOUND;
1360
1361 SELECT nls_language, nls_territory
1362 INTO nls_lang, nls_terr
1363 FROM FND_LANGUAGES
1364 WHERE language_code = lang_code;
1365
1366 IF NVL( p_list_type , 'TRX') = 'TRX' THEN
1367 select_stmt := ' SELECT ps.payment_schedule_id ' || cr || build_from_clause || cr ||
1368 ' AND trx.customer_trx_id in ( ' || p_id_list || ' ) ' || cr ||
1369 ' AND nvl(a_bill.language, ''' || base_lang ||''' ) = :lang_code ' || cr ||
1370 ' ORDER BY ps.trx_number ' ;
1371 ELSE
1372 select_stmt := ' SELECT ps.payment_schedule_id ' || cr || build_from_clause || cr ||
1373 ' AND ps.payment_schedule_id in ('|| p_id_list || ' ) ' || cr ||
1374 ' AND nvl(a_bill.language, ''' || base_lang ||''' ) = :lang_code ' || cr ||
1375 ' ORDER BY ps.trx_number ' ;
1376 END IF;
1377
1378 select_cur := dbms_sql.open_cursor;
1379 dbms_sql.parse( select_cur, select_stmt, dbms_sql.native );
1380
1381 dbms_sql.bind_variable(select_cur,':lang_code', lang_code );
1382 dbms_sql.define_array(select_cur,1,ps_id,500,1 );
1383 ignore := dbms_sql.execute(select_cur);
1384
1385 LOOP
1386 fetched_row_count := dbms_sql.fetch_rows(select_cur);
1387 dbms_sql.column_value(select_cur,1,ps_id);
1388
1389 EXIT WHEN fetched_row_count <> 500 ;
1390 END LOOP;
1391 dbms_sql.close_cursor(select_cur);
1392
1393 inserted_row_counts := ps_id.COUNT ;
1394
1395
1396 divided_worker_counts := ceil(inserted_row_counts/500);
1397 row_counts_perworker := ceil(inserted_row_counts/divided_worker_counts);
1398
1399 l_low_range := 1 ;
1400 l_high_range := row_counts_perworker ;
1401
1402 FOR no_of_workers in 1 .. divided_worker_counts
1403 LOOP
1404
1405 -- When parent request id is passed as -1, child
1406 -- request uses its request id to pick data.
1407
1408 l_request_id := AR_BPA_PRINT_CONC.submit_print_request(
1409 -1,
1410 no_of_workers,
1411 'TRX_NUMBER',
1412 p_template_id,
1413 p_stamp_flag,
1414 p_child_template_id,
1415 '',
1416 '',
1417 nls_lang ,
1418 nls_terr,
1419 FALSE,
1420 p_description);
1421
1422 IF l_request_id = 0
1423 THEN
1424 l_fail_flag := 'Y';
1425 ELSIF x_req_id_list IS NULL THEN
1426 x_req_id_list := l_request_id;
1427 ELSE
1428 x_req_id_list := x_req_id_list ||','|| l_request_id;
1429 END IF;
1430
1431 FORALL i in l_low_range .. l_high_range
1432 INSERT INTO ar_bpa_print_requests ( request_id,
1433 payment_schedule_id,
1434 worker_id,
1435 created_by,
1436 creation_date,
1437 last_updated_by,
1438 last_update_date)
1439 VALUES (l_request_id,
1440 ps_id(i),
1441 no_of_workers ,
1442 1,
1443 sysdate,
1444 1,
1445 sysdate);
1446
1447 COMMIT;
1448 l_low_range := l_low_range + row_counts_perworker;
1449 l_high_range := l_high_range + row_counts_perworker;
1450 END LOOP;
1451 END LOOP;
1452
1453 /* If any time a request failed to submit, then we send the request id
1454 list as zero */
1455 IF l_fail_flag = 'Y' THEN
1456 x_req_id_list := '0';
1457 END IF;
1458
1459 CLOSE lang_cv;
1460 EXCEPTION
1461 WHEN OTHERS THEN
1462 IF dbms_sql.is_open(select_cur) THEN
1463 dbms_sql.close_cursor(select_cur);
1464 END IF;
1465 IF lang_cv%ISOPEN THEN
1466 CLOSE lang_cv;
1467 END IF;
1468 RAISE;
1469 END process_print_request;
1470
1471 PROCEDURE process_multi_print( p_id_list IN VARCHAR2 ,
1472 x_request_id OUT NOCOPY NUMBER,
1473 x_out_status OUT NOCOPY VARCHAR2,
1474 p_list_type IN VARCHAR2
1475 )
1476 IS
1477
1478 l_trx_ps_id NUMBER;
1479
1480 l_request_id number;
1481
1482 l_count NUMBER := 0;
1483 l_iter NUMBER := 0;
1484
1485 l_id_list VARCHAR2(250);
1486
1487 rows NUMBER;
1488 ps_id dbms_sql.number_table;
1489
1490 BEGIN
1491 SELECT fnd_concurrent_requests_s.nextval
1492 INTO l_request_id
1493 FROM dual;
1494
1495 x_request_id := l_request_id;
1496
1497 l_id_list := p_id_list;
1498
1499 WHILE TRUE LOOP
1500 l_iter := l_iter + 1;
1501
1502 l_count := INSTR(l_id_list ,',') ;
1503
1504 IF l_count = 0 THEN
1505 ps_id(l_iter) := TO_NUMBER(l_id_list);
1506 EXIT;
1507 ELSE
1508 ps_id(l_iter) := SUBSTR(l_id_list,1,l_count - 1);
1509 l_id_list:= SUBSTR(l_id_list,l_count+1);
1510 END IF;
1511
1512 END LOOP;
1513
1514 BEGIN
1515 IF NVL( p_list_type , 'TRX') = 'TRX'
1516 THEN
1517 FORALL i IN 1..l_iter
1518 INSERT INTO ar_bpa_print_requests (request_id,
1519 payment_schedule_id,
1520 worker_id,
1521 created_by,
1522 creation_date,
1523 last_updated_by,
1524 last_update_date)
1525 (SELECT l_request_id ,
1526 ps.payment_schedule_id,
1527 1,
1528 1,
1529 sysdate,
1530 1,
1531 sysdate
1532 FROM ra_customer_trx ct ,
1533 ar_payment_schedules ps
1534 WHERE ct.customer_Trx_id = ps_id(i)
1535 AND ps.customer_Trx_id = ct.customer_Trx_id);
1536
1537 rows := SQL%ROWCOUNT;
1538 ELSE
1539 FORALL i IN 1..l_iter
1540 INSERT INTO ar_bpa_print_requests (request_id,
1541 payment_schedule_id,
1542 worker_id,
1543 created_by,
1544 creation_date,
1545 last_updated_by,
1546 last_update_date)
1547 VALUES( l_request_id ,
1548 ps_id(i),
1549 1,
1550 1,
1551 sysdate,
1552 1,
1553 sysdate);
1554
1555 rows := SQL%ROWCOUNT;
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN OTHERS THEN NULL;
1560 END;
1561
1562 COMMIT;
1563
1564 x_out_status := NULL;
1565
1566 EXCEPTION
1567 WHEN OTHERS THEN
1568 x_out_status := SUBSTR(SQLERRM, 1, 100);
1569 END process_multi_print;
1570
1571 END AR_BPA_PRINT_CONC;