[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_PRINT_TRX
Source
4 -- Copyright (c) 2000 Oracle Product Development
1 PACKAGE BODY AR_BPA_PRINT_TRX AS
2 /* $Header: arbpaptb.pls 120.6 2011/03/24 23:04:16 rravikir ship $ */
3 --*************************************************************************
5 -- All rights reserved
6 --*************************************************************************
7 --
8 -- HEADER
9 -- Source control header
10 --
11 -- PROGRAM NAME
12 -- arbpaptb.pls
13 --
14 -- DESCRIPTION
15 -- This script creates the package body of AR_BPA_PRINT_TRX
16 --
17 -- USAGE
18 -- To install sqlplus <apps_user>/<apps_pwd> @arbpaptb.pls
19 -- To execute sqlplus <apps_user>/<apps_pwd> AR_BPA_PRINT_TRX.
20 --
21 -- PROGRAM LIST DESCRIPTION
22 --
23 -- PRINT_INVOICES This function is used to print the selected invoices
24 --
25 -- DEPENDENCIES
26 -- None
27 --
28 -- CALLED BY
29 -- BPA Master Program
30 --
31 -- LAST UPDATE DATE 08-Jun-2007
32 -- Date the program has been modified for the last time.
33 --
34 -- HISTORY
35 -- =======
36 --
37 -- VERSION DATE AUTHOR(S) DESCRIPTION
38 -- ------- ----------- --------------- --------------------------------------
39 -- Draft1A 08-Jun-2007 Sandeep Kumar G Initial Version
40 -- Draft1B 14-Jul-2008 Rakesh Pulla Made changes as per the Bug # 7216665
41 --===========================================================================*/
42
43 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'Y');
44 lv_msg VARCHAR2(240);
45
46 --************************************************************
47 --************************************************************
48
49
50 FUNCTION build_from_clause RETURN VARCHAR2 IS
51 lc_from_clause VARCHAR2(8096);
52
53 BEGIN
54
55 lc_from_clause := ' FROM ' ||
56 ' ar_payment_schedules_all apsa, ' ||
57 ' ra_customer_trx rct, ' ||
58 ' ra_terms rt, ' ||
59 ' ra_cust_trx_types_all rctt, ' ||
60 ' hz_cust_accounts_all hcaa, ' ||
61 ' hz_parties hpar, ' ||
62 ' hz_cust_acct_sites_all hcasa, ' ||
63 ' hz_party_sites hps,' ||
64 ' hz_locations hl, ' ||
65 ' hz_cust_site_uses_all hcsua ' ||
66 ' WHERE ' ||
67 ' rct.cust_trx_type_id = rctt.cust_trx_type_id ' ||
68 ' AND rct.org_id = rctt.org_id ' ||
69 ' AND rct.customer_trx_id = apsa.customer_trx_id(+) ' ||
70 ' AND rct.org_id = apsa.org_id(+) ' ||
71 ' AND rct.printing_option = ' || '''' || 'PRI' || '''' ||
72 ' AND rct.bill_to_customer_id = hcaa.cust_account_id ' ||
73 ' AND hcaa.party_id = hpar.party_id ' ||
74 ' AND rct.bill_to_site_use_id = hcsua.site_use_id ' ||
75 ' AND rct.org_id = hcsua.org_id ' ||
76 ' AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+) ' ||
77 ' AND hcsua.org_id = hcasa.org_id(+) ' ||
78 ' AND hcasa.party_site_id = hps.party_site_id(+) ' ||
79 ' AND rct.term_id = rt.term_id(+) ' ||
80 ' AND rt.billing_cycle_id IS NULL ' ||
81 ' AND hl.location_id(+) = hps.location_id ' ;
82
83 RETURN lc_from_clause;
84
85 END build_from_clause;
86 --************************************************************
87 --************************************************************
88
89 PROCEDURE check_child_request(p_request_id IN OUT NOCOPY NUMBER) IS
90
91 lb_call_status BOOLEAN;
92 lc_rphase VARCHAR2(80);
93 lc_rstatus VARCHAR2(80);
94 lc_dphase VARCHAR2(30);
98 BEGIN
95 lc_dstatus VARCHAR2(30);
96 lc_message VARCHAR2(240);
97
99 lb_call_status := FND_CONCURRENT.get_request_status(
100 p_request_id
101 ,''
102 ,''
103 ,lc_rphase
104 ,lc_rstatus
105 ,lc_dphase
106 ,lc_dstatus
107 ,lc_message);
108
109 IF ((lc_dphase = 'COMPLETE') AND (lc_dstatus = 'NORMAL')) THEN
110 FND_MESSAGE.set_name('FND','SRS-OUTCOME SUCCESS');
111 lv_msg := FND_MESSAGE.get;
112 FND_FILE.put_line(FND_FILE.log, p_request_id || lv_msg);
113
114 ELSE
115 FND_MESSAGE.set_name('FND','SRS-OUTCOME SUCCESS');
116 lv_msg := FND_MESSAGE.get;
117 FND_FILE.put_line(FND_FILE.log, p_request_id ||' not '||lv_msg);
118 END IF;
119
120 END check_child_request;
121
122 --************************************************************
123 --************************************************************
124
125 FUNCTION submit_print_request(p_parent_request_id IN NUMBER
126 ,p_worker_id IN NUMBER
127 ,p_template_id IN NUMBER
128 ,p_choice IN VARCHAR2
129 ,p_stamp_flag IN VARCHAR2
130 ,p_child_template_id IN NUMBER DEFAULT NULL
131 ,p_nls_lang IN VARCHAR2
132 ,p_nls_territory IN VARCHAR2
133 ,p_sub_request_flag IN BOOLEAN
134 ,p_description IN VARCHAR2 DEFAULT NULL)
135 RETURN NUMBER IS
136
137 lb_options_ok BOOLEAN;
138 ln_m_request_id NUMBER;
139 ln_number_of_copies NUMBER;
140 lc_printer VARCHAR2(30);
141 lc_print_style VARCHAR2(30);
142 lc_save_output_flag VARCHAR2(30);
143 lb_save_output_bool BOOLEAN;
144 lb_print_opt_populated BOOLEAN;
145 ln_conc_prog_code VARCHAR2(30);
146 ln_conc_prog_id NUMBER;
147
148 BEGIN
149
150 SELECT fcp.concurrent_program_name
151 ,fcp.concurrent_program_id
152 INTO ln_conc_prog_code
153 ,ln_conc_prog_id
154 FROM fnd_concurrent_programs fcp
155 ,fnd_concurrent_requests fcr
156 WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
157 AND fcr.request_id = p_parent_request_id;
158
159 lb_options_ok := FND_REQUEST.set_options(implicit => 'NO'
160 ,protected => 'YES'
161 ,language => p_nls_lang
162 ,territory => p_nls_territory);
163
164 IF (lb_options_ok) THEN
165
166 IF ln_conc_prog_code='ARBPAIPMP_IL' THEN
167 IF p_choice ='NEW' THEN
168
169 UPDATE fnd_concurrent_requests
170 SET save_output_flag='N'
171 ,number_of_copies=1
175 UPDATE fnd_concurrent_requests
172 WHERE request_id = p_parent_request_id
173 AND concurrent_program_id = ln_conc_prog_id;
174 ELSE
176 SET save_output_flag='Y'
177 WHERE request_id = p_parent_request_id
178 AND concurrent_program_id = ln_conc_prog_id;
179 END IF;
180 END IF;
181
182 IF( FND_CONCURRENT.get_request_print_options(p_parent_request_id
183 ,ln_number_of_copies
184 ,lc_print_style
185 ,lc_printer
186 ,lc_save_output_flag)) THEN
187
188 IF (lc_save_output_flag = 'Y') THEN
189 lb_save_output_bool := TRUE;
190 ELSE
191 lb_save_output_bool := FALSE;
192 END IF;
193
194 IF (NOT FND_REQUEST.set_print_options(printer => lc_printer
195 ,style => lc_print_style
196 ,copies => ln_number_of_copies
197 ,save_output => lb_save_output_bool)) THEN
198 lb_print_opt_populated := FALSE;
199 ELSE
200 lb_print_opt_populated := TRUE;
201 END IF;
202 END IF;
203 ln_m_request_id := FND_REQUEST.submit_request(
204 application=> 'AR'
205 , program => 'ARBPIPCP'
209 , argument1 => p_parent_request_id
206 , description=> p_description
207 , start_time => ''
208 , sub_request=> p_sub_request_flag
210 , argument2 => p_worker_id
211 , argument3 => 'TRX_NUMBER'
212 , argument4 => p_template_id
213 , argument5 => p_stamp_flag
214 , argument6 => p_child_template_id
215 , argument7 => 222
216 , argument8 => 'en-US'
217 , argument9 => 'N'
218 , argument10 => chr(0)
219 , argument11 => '', argument12 => '', argument13 => '', argument14 => '', argument15 => ''
220 , argument16 => '', argument17 => '', argument18 => '', argument19 => '', argument20 => ''
221 , argument21 => '', argument22 => '', argument23 => '', argument24 => '', argument25 => ''
222 , argument26 => '', argument27 => '', argument28 => '', argument29 => '', argument30 => ''
223 , argument31 => '', argument32 => '', argument33 => '', argument34 => '', argument35 => ''
224 , argument36 => '', argument37 => '', argument38 => '', argument39 => '', argument40 => ''
225 , argument41 => '', argument42 => '', argument43 => '', argument44 => '', argument45 => ''
226 , argument46 => '', argument47 => '', argument48 => '', argument49 => '', argument50 => ''
227 , argument51 => '', argument52 => '', argument53 => '', argument54 => '', argument55 => ''
228 , argument56 => '', argument57 => '', argument58 => '', argument59 => '', argument60 => ''
229 , argument61 => '', argument62 => '', argument63 => '', argument64 => '', argument65 => ''
230 , argument66 => '', argument67 => '', argument68 => '', argument69 => '', argument70 => ''
231 , argument71 => '', argument72 => '', argument73 => '', argument74 => '', argument75 => ''
232 , argument76 => '', argument77 => '', argument78 => '', argument79 => '', argument80 => ''
233 , argument81 => '', argument82 => '', argument83 => '', argument84 => '', argument85 => ''
234 , argument86 => '', argument87 => '', argument88 => '', argument89 => '', argument90 => ''
235 , argument91 => '', argument92 => '', argument93 => '', argument94 => '', argument95 => ''
236 , argument96 => '', argument97 => '', argument98 => '', argument99 => '', argument100=> '');
237 END IF;
238 RETURN ln_m_request_id;
239
240 END submit_print_request;
241
242 --************************************************************
243 --************************************************************
244
245 PROCEDURE build_where_clause(p_org_id IN NUMBER DEFAULT NULL
246 ,p_choice IN VARCHAR2
247 ,p_cust_trx_class IN VARCHAR2 DEFAULT NULL
248 ,p_trx_type_id IN NUMBER DEFAULT NULL
249 ,p_trx_number_low IN VARCHAR2 DEFAULT NULL
250 ,p_trx_number_high IN VARCHAR2 DEFAULT NULL
251 ,p_doc_number_low IN VARCHAR2 DEFAULT NULL
252 ,p_doc_number_high IN VARCHAR2 DEFAULT NULL
253 ,p_print_date_low IN DATE DEFAULT NULL
254 ,p_print_date_high IN DATE DEFAULT NULL
255 ,p_customer_no_low IN VARCHAR2 DEFAULT NULL
256 ,p_customer_no_high IN VARCHAR2 DEFAULT NULL
257 ,p_customer_name_low IN VARCHAR2 DEFAULT NULL
258 ,p_customer_name_high IN VARCHAR2 DEFAULT NULL
259 ,p_union_flag IN VARCHAR2 DEFAULT NULL
260 ,where_clause OUT NOCOPY VARCHAR2) IS
261 BEGIN
262
263 /*------------------------------------------------------------------------+
264 | Build where clause depending on passed parameters. |
265 | Operating Unit p_org_id |
266 | Transactions to Print p_choice = NEW / ANY / OLD |
267 | (High) Bill TO Customer Name p_customer_name_high |
268 | (Low) Bill TO Customer Name p_customer_name_low |
269 | (High) Bill To Customer Numberp_customer_no_high |
270 | (Low) Bill To Customer Number p_customer_no_low |
271 | Transaction Class p_cust_trx_class |
272 | Transaction Type p_cust_trx_type_id |
273 | (High) Transaction Number p_trx_number_high |
274 | (Low) Transaction Number p_trx_number_low |
275 | (High) Document Number p_doc_number_high |
276 | (Low) Document Number p_doc_number_low |
277 | (High) Print Date p_print_date_high |
278 | (Low) Print Date p_print_date_low |
279 --------------------------------------------------------------------------*/
280
281 IF (p_choice = 'NEW' ) THEN
282 where_clause :=where_clause || ' AND NVL(rct.printing_pending, ' ||'''' || 'N' ||'''' ||' ) = ' || '''' || 'Y' ||'''' ||
283 ' AND apsa.terms_sequence_number(+) > NVL(rct.last_printed_sequence_num,0) ' ;
284 ELSIF (p_choice = 'OLD' ) THEN
285 -- Changed from apsa.terms_sequence_number to 0 (Zero) because,
289 END IF;
286 -- if it is apsa.terms_sequence_number then the report is not fetching the
287 -- data for the p_choice = 'OLD'
288 where_clause :=where_clause || ' AND NVL(rct.last_printed_sequence_num, 0) > 0 ';
290
291 IF ( p_org_id IS NOT NULL ) THEN
292 where_clause :=where_clause || ' AND rct.org_id = :org_id ' ;
293 END IF;
294
295 IF ( p_customer_name_low IS NOT NULL AND p_customer_name_high IS NULL )THEN
296 where_clause :=where_clause || ' AND hpar.party_name = :customer_name_low ';
297 ELSIF ( (p_customer_name_high IS NOT NULL) AND (p_customer_name_low IS NULL ) ) THEN
298 where_clause :=where_clause || ' AND hpar.party_name = :customer_name_high ';
299 ELSIF ( (p_customer_name_high IS NOT NULL) AND (p_customer_name_low IS NOT NULL) ) THEN
300 where_clause :=where_clause || ' AND hpar.party_name >= :customer_name_low ';
301 where_clause :=where_clause || ' AND hpar.party_name <= :customer_name_high ';
302 END IF;
303
304 IF ( (p_customer_no_low IS NOT NULL) AND (p_customer_no_high IS NULL) ) THEN
305 where_clause :=where_clause || ' AND hcaa.account_number = :customer_no_low ' ;
306 ELSIF ( (p_customer_no_high IS NOT NULL) AND (p_customer_no_low IS NULL) ) THEN
307 where_clause :=where_clause || ' AND hcaa.account_number = :customer_no_high ';
308 ELSIF ( (p_customer_no_high IS NOT NULL) AND (p_customer_no_low IS NOT NULL) ) THEN
309 where_clause :=where_clause || ' AND hcaa.account_number >= :customer_no_low ';
310 where_clause :=where_clause || ' AND hcaa.account_number <= :customer_no_high ' ;
311 END IF;
312
313 IF ( p_cust_trx_class IS NOT NULL ) THEN
314 where_clause :=where_clause || ' AND rctt.type = :cust_trx_class ';
315 END IF;
316
317 IF ( p_trx_type_id IS NOT NULL ) THEN
318 where_clause :=where_clause || ' AND rctt.cust_trx_type_id = :trx_type_id ' ;
319 END IF;
320
321 IF ( (p_trx_number_low IS NOT NULL) AND (p_trx_number_high IS NULL )) THEN
322 where_clause :=where_clause || ' AND rct.trx_number = :trx_number_low ';
323 ELSIF ( (p_trx_number_high IS NOT NULL) AND (p_trx_number_low IS NULL) ) THEN
324 where_clause :=where_clause || ' AND rct.trx_number = :trx_number_high ';
325 ELSIF ( (p_trx_number_high IS NOT NULL ) AND (p_trx_number_low IS NOT NULL) ) THEN
326 where_clause :=where_clause || ' AND rct.trx_number >= :trx_number_low ';
327 where_clause :=where_clause || ' AND rct.trx_number <= :trx_number_high ';
328 END IF;
329
330 IF ( (p_doc_number_low IS NOT NULL) AND (p_doc_number_high IS NULL )) THEN
331 where_clause :=where_clause || ' AND rct.doc_sequence_value = :doc_number_low ';
332 ELSIF ( (p_doc_number_high IS NOT NULL) AND (p_doc_number_low IS NULL) ) THEN
333 where_clause :=where_clause || ' AND rct.doc_sequence_value = :doc_number_high ';
334 ELSIF ( (p_doc_number_high IS NOT NULL ) AND (p_doc_number_low IS NOT NULL) ) THEN
335 where_clause :=where_clause || ' AND rct.doc_sequence_value >= :doc_number_low ';
336 where_clause :=where_clause || ' AND rct.doc_sequence_value <= :doc_number_high ';
337 END IF;
338
339 /**********************************************************************************
340 Handle Print Lead Days IF date range is provided. IF the invoice you are printing
341 has a payment term where Print Lead Days is 0, Receivables uses the transaction date
342 to determine IF this transaction falls into the Start and END Date range you specIFy.
343 IF the invoice you are printing has a payment term where Print Lead Days is greater
344 than 0, Receivables uses the FORmula Due Date - Print Lead Days to determine IF this
345 transaction is to be printed
346 ************************************************************************************/
347
348 IF ( (p_print_date_low IS NOT NULL) or (p_print_date_low IS NOT NULL) ) THEN
349 IF ( p_union_flag = 'N' ) THEN
350 where_clause := where_clause || ' AND nvl(rt.printing_lead_days,0) = 0 ' ;
351 IF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NOT NULL ) THEN
352 where_clause := where_clause || ' AND rct.trx_date BETWEEN TO_DATE(:print_date_low, ';
353 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
354 where_clause := where_clause || ' AND TO_DATE(:print_date_high, ';
355 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
356 ELSIF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NULL) THEN
357 where_clause := where_clause || 'AND rct.trx_date >= TO_DATE(:print_date_low, ';
358 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
359 ELSIF ( p_print_date_high IS NOT NULL AND p_print_date_low IS NULL ) THEN
360 where_clause := where_clause || 'AND rct.trx_date <= TO_DATE(:print_date_high, ';
361 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
362 END IF;
363 ELSE
364 where_clause :=where_clause || ' AND nvl(rt.printing_lead_days,0) > 0 ' ;
365 IF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NOT NULL ) THEN
366 where_clause := where_clause || 'AND nvl(apsa.due_date,sysdate) BETWEEN TO_DATE(:print_date_low, ';
367 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
368 where_clause := where_clause || ' + NVL (rt.printing_lead_days, 0)';
369 where_clause := where_clause || ' AND TO_DATE(:print_date_high, ';
370 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'')';
371 where_clause := where_clause || ' + NVL (rt.printing_lead_days, 0)';
375 ELSIF ( p_print_date_high IS NOT NULL AND p_print_date_low IS NULL ) THEN
372 ELSIF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NULL ) THEN
373 where_clause := where_clause || 'AND nvl(apsa.due_date,sysdate) >= TO_DATE(:print_date_low, ';
374 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'') + NVL (rt.printing_lead_days, 0)';
376 where_clause := where_clause || 'AND nvl(apsa.due_date,sysdate) <= TO_DATE(:print_date_high, ';
377 where_clause := where_clause || '''DD-MM-YYYY-HH24:MI:SS'') + NVL (rt.printing_lead_days, 0)';
378 END IF;
379 END IF;
380 END IF;
381
382 END build_where_clause;
383
384 --************************************************************
385 --************************************************************
386
387 PROCEDURE bind_variables(p_org_id IN NUMBER DEFAULT NULL
388 ,p_choice IN VARCHAR2
389 ,p_cust_trx_class IN VARCHAR2 DEFAULT NULL
390 ,p_trx_type_id IN NUMBER DEFAULT NULL
391 ,p_trx_number_low IN VARCHAR2 DEFAULT NULL
392 ,p_trx_number_high IN VARCHAR2 DEFAULT NULL
393 ,p_doc_number_low IN VARCHAR2 DEFAULT NULL
394 ,p_doc_number_high IN VARCHAR2 DEFAULT NULL
398 ,p_customer_no_high IN VARCHAR2 DEFAULT NULL
395 ,p_print_date_low IN DATE DEFAULT NULL
396 ,p_print_date_high IN DATE DEFAULT NULL
397 ,p_customer_no_low IN VARCHAR2 DEFAULT NULL
399 ,p_customer_name_low IN VARCHAR2 DEFAULT NULL
400 ,p_customer_name_high IN VARCHAR2 DEFAULT NULL
401 ,p_union_flag IN VARCHAR2 DEFAULT NULL
402 ,cursor_name IN INTEGER ) IS
403 BEGIN
404
405 /*------------------------------------------------------------------------+
406 | Bind clause depending on passed parameters. |
407 | Operating Unit p_org_id |
408 | Transactions to Print p_choice = NEW / ANY / OLD |
409 | (High) Bill TO Customer Name p_customer_name_high |
410 | (Low) Bill TO Customer Name p_customer_name_low |
411 | (High) Bill To Customer Numberp_customer_no_high |
412 | (Low) Bill To Customer Number p_customer_no_low |
413 | Transaction Class p_cust_trx_class |
414 | Transaction Type p_cust_trx_type_id |
415 | (High) Transaction Number p_trx_number_high |
416 | (Low) Transaction Number p_trx_number_low |
417 | (High) Document Number p_doc_number_high |
418 | (Low) Document Number p_doc_number_low |
419 | (High) Print Date p_print_date_high |
420 | (Low) Print Date p_print_date_low |
421 --------------------------------------------------------------------------*/
422
423 IF ( p_org_id IS NOT NULL ) THEN
424 DBMS_SQL.bind_variable( cursor_name, ':org_id', p_org_id) ;
425 END IF;
426
427 IF ( p_customer_name_low IS NOT NULL AND p_customer_name_high IS NULL )THEN
428 DBMS_SQL.bind_variable( cursor_name, ':customer_name_low', p_customer_name_low);
429 ELSIF ( (p_customer_name_high IS NOT NULL) AND (p_customer_name_low IS NULL ) ) THEN
430 DBMS_SQL.bind_variable( cursor_name, ':customer_name_high', p_customer_name_high );
431 ELSIF ( (p_customer_name_high IS NOT NULL) AND (p_customer_name_low IS NOT NULL) ) THEN
432 DBMS_SQL.bind_variable( cursor_name, ':customer_name_low', p_customer_name_low);
433 DBMS_SQL.bind_variable( cursor_name,':customer_name_high', p_customer_name_high);
434 END IF;
435
436 IF ( (p_customer_no_low IS NOT NULL) AND (p_customer_no_high IS NULL) ) THEN
437 DBMS_SQL.bind_variable( cursor_name, ':customer_no_low', p_customer_no_low ) ;
438 ELSIF ( (p_customer_no_high IS NOT NULL) AND (p_customer_no_low IS NULL) ) THEN
439 DBMS_SQL.bind_variable( cursor_name, ':customer_no_high', p_customer_no_high );
440 ELSIF ( (p_customer_no_high IS NOT NULL) AND (p_customer_no_low IS NOT NULL) ) THEN
441 DBMS_SQL.bind_variable( cursor_name, ':customer_no_low', p_customer_no_low ) ;
442 DBMS_SQL.bind_variable( cursor_name, ':customer_no_high', p_customer_no_high ) ;
443 END IF;
444
445 IF ( p_cust_trx_class IS NOT NULL ) THEN
446 DBMS_SQL.bind_variable( cursor_name, ':cust_trx_class', p_cust_trx_class );
447 END IF;
448
449 IF ( p_trx_type_id IS NOT NULL ) THEN
450 DBMS_SQL.bind_variable( cursor_name, ':trx_type_id', p_trx_type_id ) ;
451 END IF;
452
453 IF ( (p_trx_number_low IS NOT NULL) AND (p_trx_number_high IS NULL )) THEN
454 DBMS_SQL.bind_variable( cursor_name, ':trx_number_low', p_trx_number_low);
458 DBMS_SQL.bind_variable( cursor_name, ':trx_number_low' , p_trx_number_low);
455 ELSIF ( (p_trx_number_high IS NOT NULL) AND (p_trx_number_low IS NULL) ) THEN
456 DBMS_SQL.bind_variable( cursor_name, ':trx_number_high', p_trx_number_high);
457 ELSIF ( (p_trx_number_high IS NOT NULL ) AND (p_trx_number_low IS NOT NULL) ) THEN
459 DBMS_SQL.bind_variable( cursor_name, ':trx_number_high', p_trx_number_high);
460 END IF;
461
462 IF ( (p_doc_number_low IS NOT NULL) AND (p_doc_number_high IS NULL )) THEN
463 DBMS_SQL.bind_variable( cursor_name, ':doc_number_low', p_doc_number_low);
464 ELSIF ( (p_doc_number_high IS NOT NULL) AND (p_doc_number_low IS NULL) ) THEN
465 DBMS_SQL.bind_variable( cursor_name, ':doc_number_high', p_doc_number_high);
466 ELSIF ( (p_doc_number_high IS NOT NULL ) AND (p_doc_number_low IS NOT NULL) ) THEN
467 DBMS_SQL.bind_variable( cursor_name, ':doc_number_low' , p_doc_number_low);
468 DBMS_SQL.bind_variable( cursor_name, ':doc_number_high', p_doc_number_high);
469 END IF;
470
471 /**********************************************************************************
472 Handle Print Lead Days IF date range is provided. IF the invoice you are printing
473 has a payment term where Print Lead Days is 0, Receivables uses the transaction date
474 to determine IF this transaction falls into the Start and END Date range you specIFy.
475 IF the invoice you are printing has a payment term where Print Lead Days is greater
476 than 0, Receivables uses the FORmula Due Date - Print Lead Days to determine IF this
477 transaction is to be printed
478 ************************************************************************************/
479
480 IF ( (p_print_date_low IS NOT NULL) or (p_print_date_low IS NOT NULL) ) THEN
481 IF ( p_union_flag = 'N' ) THEN
482 IF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NOT NULL ) THEN
483 DBMS_SQL.bind_variable( cursor_name, ':print_date_low' ,
484 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
485 DBMS_SQL.bind_variable( cursor_name, ':print_date_high' ,
486 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
487 ELSIF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NULL) THEN
488 DBMS_SQL.bind_variable( cursor_name, ':print_date_low' ,
489 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
490 ELSIF ( p_print_date_high IS NOT NULL AND p_print_date_low IS NULL ) THEN
491 DBMS_SQL.bind_variable( cursor_name, ':print_date_high' ,
492 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
493 END IF;
494 ELSE
495 IF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NOT NULL ) THEN
496 DBMS_SQL.bind_variable( cursor_name, ':print_date_low' ,
497 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
498 DBMS_SQL.bind_variable( cursor_name, ':print_date_high' ,
499 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
500 ELSIF ( p_print_date_low IS NOT NULL AND p_print_date_high IS NULL ) THEN
501 DBMS_SQL.bind_variable( cursor_name, ':print_date_low' ,
505 TO_CHAR(p_print_date_high ,'DD-MM-YYYY-HH24:MI:SS'));
502 TO_CHAR(p_print_date_low ,'DD-MM-YYYY-HH24:MI:SS'));
503 ELSIF ( p_print_date_high IS NOT NULL AND p_print_date_low IS NULL ) THEN
504 DBMS_SQL.bind_variable( cursor_name, ':print_date_high' ,
506 END IF;
507 END IF;
508 END IF;
509
510 END bind_variables;
511
512 --************************************************************
513 --************************************************************
514
515 PROCEDURE PRINT_INVOICES(errbuf IN OUT NOCOPY VARCHAR2
516 ,retcode IN OUT NOCOPY VARCHAR2
517 ,p_org_id IN NUMBER
518 ,p_job_size IN NUMBER
519 ,p_template_id IN NUMBER
520 ,p_choice IN VARCHAR2
521 ,p_cust_trx_class IN VARCHAR2
522 ,p_trx_type_id IN NUMBER
523 ,p_customer_name_low IN VARCHAR2
524 ,p_customer_name_high IN VARCHAR2
525 ,p_customer_no_low IN VARCHAR2
526 ,p_customer_no_high IN VARCHAR2
527 ,p_trx_number_low IN VARCHAR2
528 ,p_trx_number_high IN VARCHAR2
529 ,p_doc_number_low IN VARCHAR2
530 ,p_doc_number_high IN VARCHAR2
531 ,p_print_date_low_in IN VARCHAR2
532 ,p_print_date_high_in IN VARCHAR2)
533 IS
534 ln_job_size INTEGER := 500;
535 ld_print_date_low DATE := NULL;
536 ld_print_date_high DATE := NULL;
537 lc_where1 VARCHAR2(8096);
538 lc_where2 VARCHAR2(8096);
539 lb_filter_exists BOOLEAN := FALSE;
540
541 --local variables
542 lc_base_lang VARCHAR2(4);
543 lc_userenv_lang VARCHAR2(4);
544 ln_retval NUMBER;
545 ln_parm_number NUMBER;
549 lc_sql_stmt VARCHAR2(8096);
546 lc_parm_name VARCHAR2(80);
547
548 ln_sql_stmt_c NUMBER;
550 lc_insert_stmt VARCHAR2(240);
551 lc_select_stmt VARCHAR2(8096);
552
553 ln_inserted_row_counts INTEGER;
554 ln_row_counts_perworker NUMBER;
555 ln_divided_worker_counts NUMBER := 1;
556
557 -- variable used for concurrent program
558 lc_req_data VARCHAR2(240);
559 ln_request_id NUMBER; -- child request id
560 ln_m_request_id NUMBER; -- parent request id
561
562 ln_low_range NUMBER := 1;
563 ln_high_range NUMBER := 1;
564 ln_worker_id NUMBER := 1;
565
566 ln_cnt_warnings INTEGER := 0;
567 ln_cnt_errors INTEGER := 0;
568 lb_request_status BOOLEAN;
569 lc_return_stat VARCHAR2(2000);
570 ln_fail_count NUMBER := 0;
571
572 ln_test_num NUMBER;
573
574 BEGIN
575
576 MO_GLOBAL.init('AR');
577 -- to check if the output directory exists
578 -- read the variable request_data to check if it is reentering the program
579 lc_req_data := FND_CONC_GLOBAL.request_data;
580 ln_m_request_id := FND_GLOBAL.conc_request_id;
581
582 IF (lc_req_data IS NULL) THEN
583 -- read the user env language
584 SELECT SUBSTR(userenv('LANG'),1,4)
585 INTO lc_userenv_lang
586 FROM SYS.DUAL;
587
588 FND_FILE.put_line(FND_FILE.log,'User Lang ::'||lc_userenv_lang );
589 BEGIN
590 SELECT fl.language_code
591 INTO lc_base_lang
592 FROM fnd_languages fl
593 WHERE fl.installed_flag = 'B';
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 lc_base_lang := 'US';
597 END;
598 FND_FILE.put_line(FND_FILE.log,'Base Lang ::'||lc_base_lang);
599
600 IF p_job_size > 0 THEN
601 ln_job_size := p_job_size;
602 END IF;
603
604 ld_print_date_high := FND_DATE.canonical_to_date(p_print_date_high_in);
605 ld_print_date_low := FND_DATE.canonical_to_date(p_print_date_low_in);
606
607 -- print out the input parameters;
608 -- fetch a list of payment schedule id based on the inputted parameters
609 -- AND insert into the ar_bpa_print_requests table
610
611 lc_insert_stmt := ' INSERT INTO ar_bpa_print_requests (request_id, payment_schedule_id,
612 worker_id, created_by, creation_date,last_updated_by, last_update_date, customer_trx_id)';
613 lc_select_stmt := ' SELECT ' || ln_m_request_id || ', payment_schedule_id, ROWNUM, 1, SYSDATE, 1, SYSDATE, customer_trx_id FROM '
614 ||' ( SELECT apsa.payment_schedule_id, rct.customer_trx_id '|| build_from_clause ||
615 ' AND NVL(hcasa.language,' || '''' || lc_base_lang || ''') = ' || '''' || lc_userenv_lang || '''' ;
616
617 AR_BPA_PRINT_TRX.build_where_clause(p_org_id
618 ,p_choice
619 ,p_cust_trx_class
620 ,p_trx_type_id
621 ,p_trx_number_low
622 ,p_trx_number_high
623 ,p_doc_number_low
624 ,p_doc_number_high
625 ,ld_print_date_low
626 ,ld_print_date_high
627 ,p_customer_no_low
628 ,p_customer_no_high
629 ,p_customer_name_low
630 ,p_customer_name_high
631 ,'N'
632 ,lc_where1) ;
633
634 lc_sql_stmt := lc_insert_stmt || lc_select_stmt || lc_where1 || ')';
635
636 IF ( ld_print_date_low IS NOT NULL OR ld_print_date_high IS NOT NULL ) THEN
637 AR_BPA_PRINT_TRX.build_where_clause(p_org_id
638 ,p_choice
639 ,p_cust_trx_class
640 ,p_trx_type_id
641 ,p_trx_number_low
642 ,p_trx_number_high
643 ,p_doc_number_low
644 ,p_doc_number_high
645 ,ld_print_date_low
646 ,ld_print_date_high
647 ,p_customer_no_low
648 ,p_customer_no_high
649 ,p_customer_name_low
650 ,p_customer_name_high
651 ,'Y'
652 ,lc_where2);
653 lc_sql_stmt := lc_sql_stmt || ' UNION ALL ' || lc_select_stmt || lc_where2 || ')';
654 END IF;
655
656
657 ------------------------------------------------
658 -- Parse sql stmts
659 ------------------------------------------------
660
661 ln_sql_stmt_c:= DBMS_SQL.open_cursor;
662 DBMS_SQL.parse( ln_sql_stmt_c, lc_sql_stmt , DBMS_SQL.v7 );
663 AR_BPA_PRINT_TRX.bind_variables(p_org_id
664 ,p_choice
665 ,p_cust_trx_class
666 ,p_trx_type_id
667 ,p_trx_number_low
668 ,p_trx_number_high
669 ,p_doc_number_low
673 ,p_customer_no_low
670 ,p_doc_number_high
671 ,ld_print_date_low
672 ,ld_print_date_high
674 ,p_customer_no_high
678 ,ln_sql_stmt_c );
675 ,p_customer_name_low
676 ,p_customer_name_high
677 ,'N'
679
680 IF ( ld_print_date_low IS NOT NULL OR ld_print_date_high IS NOT NULL ) THEN
681 AR_BPA_PRINT_TRX.bind_variables(p_org_id
682 ,p_choice
683 ,p_cust_trx_class
684 ,p_trx_type_id
685 ,p_trx_number_low
686 ,p_trx_number_high
687 ,p_doc_number_low
688 ,p_doc_number_high
689 ,ld_print_date_low
690 ,ld_print_date_high
691 ,p_customer_no_low
692 ,p_customer_no_high
693 ,p_customer_name_low
694 ,p_customer_name_high
695 ,'Y'
696 ,ln_sql_stmt_c );
697
698 END IF;
699
700 ln_inserted_row_counts := DBMS_SQL.execute(ln_sql_stmt_c);
701
702 IF ln_inserted_row_counts > 0 THEN
703 ln_divided_worker_counts := ceil(ln_inserted_row_counts/ln_job_size);
704 ln_row_counts_perworker := ceil(ln_inserted_row_counts/ln_divided_worker_counts);
705
706 ln_worker_id := 1 ;
707 ln_low_range := 1 ;
708 ln_high_range := ln_row_counts_perworker ;
709
710 LOOP
711 UPDATE ar_bpa_print_requests
712 SET worker_id = ln_worker_id
713 WHERE request_id = ln_m_request_id
714 AND worker_id BETWEEN ln_low_range AND ln_high_range;
715
716 IF ln_worker_id >= ln_divided_worker_counts THEN
717 FND_MESSAGE.set_name('FND','CONC-DG-EXIT');
718 lv_msg := FND_MESSAGE.get;
719 FND_FILE.put_line(FND_FILE.log,lv_msg);
720
721 EXIT;
722 END IF;
723
724 ln_worker_id := ln_worker_id + 1;
725 ln_low_range := ln_low_range + ln_row_counts_perworker ;
726 ln_high_range := ln_high_range + ln_row_counts_perworker ;
727
728 END LOOP;
729 COMMIT; -- commit the record here
730
731 FOR no_of_workers IN 1 .. ln_divided_worker_counts LOOP
732 ln_request_id := AR_BPA_PRINT_TRX.submit_print_request(
733 ln_m_request_id
734 ,no_of_workers
735 ,p_template_id
736 ,p_choice
737 ,'Y'
738 ,NULL
739 ,'','', TRUE);
740
741 IF (ln_request_id = 0) THEN
742 FND_MESSAGE.retrieve(lc_return_stat);
743 ln_fail_count := ln_fail_count + 1;
744 ELSE
745 COMMIT;
746 END IF;
747
748 END LOOP;
749
750 FND_CONC_GLOBAL.set_req_globals(conc_status => 'PAUSED'
751 ,request_data => to_char(ln_inserted_row_counts));
752 END IF;
753
754 ELSE
755 IF ln_divided_worker_counts > 0 THEN
756 DECLARE
757 CURSOR child_request_cur(p_request_id IN NUMBER) IS
758 SELECT fcr.request_id
759 ,fcr.status_code
760 FROM fnd_concurrent_requests fcr
761 WHERE fcr.parent_request_id = p_request_id;
762 BEGIN
763 FOR child_request_rec IN child_request_cur(ln_m_request_id)
764 LOOP
765
766 check_child_request(child_request_rec.request_id);
767 IF ( child_request_rec.status_code ='G'
768 OR child_request_rec.status_code ='X'
769 OR child_request_rec.status_code ='D'
770 OR child_request_rec.status_code ='T') THEN
771
772 ln_cnt_warnings := ln_cnt_warnings + 1;
773 ELSIF (child_request_rec.status_code = 'E') THEN
774 ln_cnt_errors := ln_cnt_errors + 1;
775 END IF;
776 END LOOP;
777
778 IF ((ln_cnt_errors > 0) OR ( ln_fail_count = ln_divided_worker_counts )) THEN
779 lb_request_status := FND_CONCURRENT.set_completion_status('ERROR', '');
780 ELSIF ((ln_cnt_warnings > 0) OR (ln_fail_count > 0)) THEN
781 lb_request_status := FND_CONCURRENT.set_completion_status('WARNING', '');
782 ELSE
783 lb_request_status := FND_CONCURRENT.set_completion_status('NORMAL', '');
784 END IF;
785 END;
786 END IF;
787
788 DELETE FROM ar_bpa_print_requests
789 WHERE request_id = ln_m_request_id;
790
791 COMMIT;
792 END IF;
793 END print_invoices;
794
795 END ar_bpa_print_trx;