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