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