[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_SELECT_CONTROL
Source
4 /*---------------------------------------------------------------------------+
1 PACKAGE BODY ARP_TRX_SELECT_CONTROL AS
2 /* $Header: ARPLTSCB.pls 120.4.12020000.2 2012/07/24 14:48:05 ashlkuma ship $ */
3
5 | PUBLIC PROCEDURE |
6 | build_where_clause - Returns two where clauses depends on the |
7 | input parameters |
8 | |
9 | DESCRIPTION |
10 | See the algorithm follows |
11 | |
12 | REQUIRES |
13 | |
14 | EXCEPTIONS RAISED |
15 | ARP_STANDARD.AR_ERROR_NUMBER ( in arp_standard.fnd_message ) |
16 | |
17 | KNOWN BUGS |
18 | |
19 | NOTES |
20 | |
21 | HISTORY |
22 | 12 May 93 Charles Huang Created |
23 | 19-FEB-99 Victoria Smith Added parameter p_call_from to identify |
24 | if procedure is called from MLS function |
25 | or invoice print program |
26 | 09-Nov-00 Debbie Jancis Modified for tca uptake. Removed all |
27 | references of ar/ra customer tables and |
28 | replaced with hz counterparts. |
29 +---------------------------------------------------------------------------*/
30
31 PROCEDURE build_where_clause (
32 P_choice IN varchar2,
33 P_open_invoice IN varchar2,
34 P_cust_trx_type_id IN number,
35 p_cust_trx_class IN varchar2,
36 P_installment_number IN number,
37 P_dates_low IN date,
38 P_dates_high IN date,
39 P_customer_id IN number,
40 P_customer_class_code IN varchar2,
41 P_trx_number_low IN varchar2,
42 P_trx_number_high IN varchar2,
43 P_batch_id IN number,
44 P_customer_trx_id IN number,
45 p_adj_number_low in varchar2,
46 p_adj_number_high in varchar2,
47 p_adj_dates_low in date,
48 p_adj_dates_high in date,
49 P_where1 OUT NOCOPY varchar2,
50 P_where2 OUT NOCOPY varchar2,
51 p_table1 OUT NOCOPY varchar2,
52 p_table2 OUT NOCOPY varchar2,
53 p_call_from IN varchar2 default 'INV'
54 ) IS
55
56
57 /*
58 ---------------------------------------------------------------------------------------
59 | |
60 | This procedure return 2 where clauses ( P_where1, P_where2 ) |
61 | base on the following input parameters |
62 | |
63 | P_choice mandatory varchar2 |
64 | IN ( 'NEW', 'SEL', 'ONE', 'BATCH', 'ADJ' ) |
65 | P_open_invoice mandatory varchar2 |
66 | in ( 'Y', 'N' ) |
67 | P_cust_trx_type_id optional number; |
68 | P_installment_number optional number; |
69 | P_dates_low optional date; |
73 | P_trx_number_low optional varchar2; |
70 | P_dates_high optional date; |
71 | P_customer_id optional number; |
72 | P_customer_class_code optional varchar2; |
74 | P_trx_number_high optional varchar2; |
75 | P_batch_id optional number, |
76 | mandatory if P_choice = 'BATCH'; |
77 | P_customer_trx_id optional number, |
78 | mandatory if P_choice = 'ONE'; |
79 | |
80 | part 1 : drivers on trx_dates ( Rel 9 ) |
81 | selects invoices without payment schedules or |
82 | invoices with null or 0 value in print lead days |
83 | part 2 : drivers on due_dates ( Rel 9 ) |
84 | selects invoices with positive print lead days and |
85 | payment schedules |
86 | |
87 | P_where1 := |
88 | FROM RA_CUSTOMERS B, |
89 | RA_TERMS_LINES TL, |
90 | RA_TERMS T, |
91 | AR_PAYMENT_SCHEDULES P, |
92 | RA_CUSTOMER_TRX A, |
93 | RA_CUST_TRX_TYPES TYPES, |
94 | AR_LOOKUPS L_TYPES |
95 | WHERE A.COMPLETE_FLAG = 'Y' |
96 | AND A.PRINTING_OPTION IN ('PRI', 'REP') |
97 | AND A.BILL_TO_CUSTOMER_ID = B.CUST_ACCOUNT_ID |
98 | AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID(+) |
99 | AND A.TERM_ID = TL.TERM_ID |
100 | AND A.TERM_ID = T.TERM_ID |
101 | AND A.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID; |
102 | AND L_TYPES.LOOKUP_TYPE = 'INV/CM/ADJ'; |
103 | AND L_TYPES.LOOKUP_CODE = DECODE( TYPES.TYPE,'DEP','INV',TYPES.TYPE) |
104 | AND NVL(P.TERMS_SEQUENCE_NUMBER,TL.SEQUENCE_NUM)=TL.SEQUENCE_NUM |
105 | AND DECODE(P.PAYMENT_SCHEDULE_ID,'',0, NVL(T.PRINTING_LEAD_DAYS,0))=0 |
106 | AND B.CUST_ACCOUNT_ID = NVL(:P_customer_id,B.CUST_ACCOUNT_ID) |
107 | AND A.CUST_TRX_TYPE_ID = NVL(:P_cust_trx_type_id, A.CUST_TRX_TYPE_ID) |
108 | AND TL.SEQUENCE_NUM = NVL(:P_installment_number ,TL.SEQUENCE_NUM) |
109 | AND DECODE(:P_open_invoice,'Y', |
110 | DECODE(A.PREVIOUS_CUSTOMER_TRX_ID||A.INITIAL_CUSTOMER_TRX_ID, |
111 | '', NVL(P.AMOUNT_DUE_REMAINING,1),1),1) <> 0 |
112 | AND A.TRX_DATE BETWEEN |
113 | TO_DATE(NVL(:P_dates_low, TO_CHAR(TO_DATE('1721424','J'), |
114 | 'DD-MON-YYYY')),'DD-MON-YYYY') |
115 | AND TO_DATE(NVL(:P_dates_high, TO_CHAR(TO_DATE('2853311','J'), |
116 | 'DD-MON-YYYY')),'DD-MON-YYYY') |
117 | AND B.CUSTOMER_CLASS_CODE |
118 | = NVL(:P_customer_class_code, B.CUSTOMER_CLASS_CODE) |
119 | |
120 | P_where2 := |
121 | FROM RA_CUSTOMERS B, |
122 | RA_TERMS T, |
123 | AR_PAYMENT_SCHEDULES S, |
124 | RA_CUSTOMER_TRX A, |
125 | RA_CUST_TRX_TYPES TYPES, |
126 | AR_LOOKUPS L_TYPES |
127 | WHERE A.COMPLETE_FLAG = 'Y' |
128 | AND A.PRINTING_OPTION IN ('PRI', 'REP') |
129 | AND A.BILL_TO_CUSTOMER_ID = B.CUST_ACCOUNT_ID |
130 | AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID |
131 | AND A.TERM_ID = T.TERM_ID |
132 | AND A.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID; |
133 | AND L_TYPES.LOOKUP_TYPE = 'INV/CM/ADJ'; |
134 | AND L_TYPES.LOOKUP_CODE = DECODE( TYPES.TYPE,'DEP','INV',TYPES.TYPE) |
135 | AND B.CUST_ACCOUNT_ID = NVL(:P_customer_id,B.CUST_ACCOUNT_ID) |
136 | AND A.CUST_TRX_TYPE_ID = NVL(:P_cust_trx_type_id, A.CUST_TRX_TYPE_ID) |
137 | AND P.TERMS_SEQUENCE_NUMBER = |
138 | NVL(:P_installment_number ,P.TERMS_SEQUENCE_NUMBER) |
139 | AND DECODE(:P_open_invoice,'Y', |
140 | DECODE(A.PREVIOUS_CUSTOMER_TRX_ID||A.INITIAL_CUSTOMER_TRX_ID, |
141 | '', P.AMOUNT_DUE_REMAINING,1),1) <> 0 |
142 | AND P.DUE_DATE||'' BETWEEN |
143 | TO_DATE(NVL(:P_dates_low,TO_CHAR(TO_DATE('1721424','J'), |
144 | 'DD-MON-YYYY')),'DD-MON-YYYY') |
145 | + nvl (T.PRINTING_LEAD_DAYS, 0) |
146 | AND TO_DATE(NVL(:P_dates_high, TO_CHAR(TO_DATE('2853311','J'), |
147 | 'DD-MON-YYYY')),'DD-MON-YYYY') |
148 | + nvl (T.PRINTING_LEAD_DAYS, 0) |
149 | AND B.CUSTOMER_CLASS_CODE |
150 | = NVL(:P_customer_class_code, B.CUSTOMER_CLASS_CODE) |
151 | |
152 | |
153 | |
154 | (1) Print New Invoices |
155 | P_choice mandatory = 'NEW' |
156 | P_open_invoice mandatory varchar2 in ( 'Y', 'N' ) |
157 | P_cust_trx_type_id optional number; |
158 | P_installment_number optional number; |
159 | P_dates_low optional date; |
160 | P_dates_high optional date; |
161 | P_customer_class_code optional varchar2; |
162 | |
163 | P_where1 := P_where1 || |
164 | AND A.PRINTING_PENDING = 'Y' |
165 | AND TL.SEQUENCE_NUM > NVL(A.LAST_PRINTED_SEQUENCE_NUM,0) |
166 | |
167 | P_where2 := P_where2 || |
168 | AND A.PRINTING_PENDING = 'Y' |
169 | AND P.TERMS_SEQUENCE_NUMBER > NVL(A.LAST_PRINTED_SEQUENCE_NUM,0) |
170 | |
171 | |
172 | (2) Print Selected invoices ( with trx number low and high ) |
173 | P_choice mandatory varchar2 = 'SEL' |
174 | P_open_invoice mandatory varchar2 in ( 'Y', 'N' ) |
178 | P_dates_high optional date; |
175 | P_cust_trx_type_id optional number; |
176 | P_installment_number optional number; |
177 | P_dates_low optional date; |
179 | P_trx_number_low varchar2 not null; |
180 | P_trx_number_high varchar2 not null; |
181 | P_customer_id optional number; |
182 | P_customer_class_code optional varchar2; |
183 | |
184 | P_where1 := P_where1 || |
185 | AND A.TRX_NUMBER BETWEEN :P_trx_number_low AND :P_trx_number_high |
186 | |
187 | P_where2 := P_where2 || |
188 | AND A.TRX_NUMBER BETWEEN :P_trx_number_low AND :P_trx_number_high |
189 | |
190 | |
191 | (3) Print Selected invoices |
192 | P_choice mandatory varchar2 = 'SEL' |
193 | P_open_invoice mandatory varchar2 in ( 'Y', 'N' ) |
194 | P_cust_trx_type_id optional number; |
195 | P_installment_number optional number; |
196 | P_dates_low optional date; |
197 | P_dates_high optional date; |
198 | P_trx_number_low optional varchar2; |
199 | P_trx_number_high optional varchar2; |
200 | P_customer_id optional number; |
201 | P_customer_class_code optional varchar2; |
202 | |
203 | |
204 | (4) Print Batch: ( with batch_id ) |
205 | P_choice mandatory varchar2 = 'BATCH' |
206 | P_open_invoice mandatory varchar2 in ( 'Y', 'N' ) |
207 | P_cust_trx_type_id optional number; |
208 | P_batch_id mandatory number; |
209 | P_customer_class_code optional varchar2; |
210 | |
211 | P_where1 := P_where1 || |
212 | AND A.BATCH_ID = :P_batch_id |
213 | |
214 | P_where2 := P_where2 || |
215 | AND A.BATCH_ID = :P_batch_id |
216 | |
217 | |
218 | (5) Print One: ( with customer_trx_id ) |
219 | P_choice mandatory varchar2 = 'ONE' |
220 | P_open_invoice mandatory varchar2 in ( 'Y', 'N' ) |
221 | P_cust_trx_type_id optional number; |
222 | P_customer_trx_id mandatory number; |
223 | |
224 | P_where1 := P_where1 || |
225 | AND A.CUSTOMER_TRX_ID = :P_customer_trx_id |
226 | |
227 | P_where2 := P_where2 || |
228 | AND A.CUSTOMER_TRX_ID = :P_customer_trx_id |
229 | |
230 | (6) p_adjustments:- |
231 | Extra Tables: ar_adjustments adj |
232 | |
233 | p_adj_number_low |
234 | p_adj_number_high |
235 | p_trx_number_low |
236 | p_trx_number_high |
237 | |
238 | P_where1 := P_where1 || |
239 | and adj.adjustment_number between p_adj_number_low and :p_adj_number_high |
240 | and adj.apply_date between :p_adj_date_low and :p_adj_date_high |
241 | and a.customer_trx_id = adj.customer_trx_id |
242 | AND nvl(TL.SEQUENCE_NUM,1) = 1 |
243 | |
244 | P_where2 := P_where2 || |
245 | and adj.adjustment_number between p_adj_number_low and :p_adj_number_high |
246 | and adj.apply_date between :p_adj_date_low and :p_adj_date_high |
247 | and a.customer_trx_id = adj.customer_trx_id |
248 | AND nvl(TL.SEQUENCE_NUM,1) = 1 |
249 | |
250 | (7) Otherwise |
251 | P_where1 = '' |
252 | P_where2 = '' |
253 | |
254 | return P_where1, P_where2. |
255 | |
256 ---------------------------------------------------------------------------------------*/
257
258 /*----------------------------------------------------------------------+
259 | Declare local variables |
260 +----------------------------------------------------------------------*/
261 where1 varchar2(8096);
262 where2 varchar2(8096);
263 where3 varchar2(8096);
264 table1 varchar2(8096);
265 table2 varchar2(8096);
266
267 cr char(1);
268 dates_low date;
269 dates_high date;
270 adj_dates_low date;
271 adj_dates_high date;
272 adj_number_low varchar2(80);
273 adj_number_high varchar2(80);
274 trx_number_low varchar2(80);
275 trx_number_high varchar2(80);
276
277 sel_new number := 1;
278 sel_inv number := 2;
279 sel_sel number := 3;
280 sel_one number := 4;
281 sel_batch number := 5;
282 sel_adj number := 6;
283 choice number;
284
285 p_userenv_lang varchar2(4);
286 language_code varchar2(4);
287 p_base_lang varchar2(4);
288
289 BEGIN
290 /*----------------------------------------------------------------------+
291 | initialization |
292 +----------------------------------------------------------------------*/
293 where1 := '';
294 where2 := '';
295 cr := '
296 ';
297
298
299 /*----------------------------------------------------------------------+
300 | make dates_low := P_dates_low 00:00:00 |
301 | make dates_high := P_dates_high 23:59:59 |
302 +----------------------------------------------------------------------*/
303 dates_low := trunc(P_dates_low);
304 dates_high := arp_standard.ceil(P_dates_high);
305
306 adj_dates_low := trunc( p_adj_dates_low );
307 adj_dates_high := arp_standard.ceil( p_adj_dates_high);
308
309
310 /*----------------------------------------------------------------------+
311 | Protect every occurance of single quote in trx/adj number params|
312 +----------------------------------------------------------------------*/
313
314 trx_number_low := replace( p_trx_number_low, '''', '''''') ;
318
315 trx_number_high := replace( p_trx_number_high, '''', '''''') ;
316 adj_number_low := replace( p_adj_number_low, '''', '''''') ;
317 adj_number_high := replace( p_adj_number_high, '''', '''''') ;
319 /*----------------------------------------------------------------------+
320 | Check mandatory parameters |
321 +----------------------------------------------------------------------*/
322 if P_choice is NULL then
323 arp_standard.fnd_message( 'AR_MAND_PARAMETER_NULL',
324 'PARAM', 'P_choice' );
325 end if;
326
327 if P_open_invoice is NULL then
328 arp_standard.fnd_message( 'AR_MAND_PARAMETER_NULL',
329 'PARAM', 'P_open_invoice' );
330 end if;
331
332 select language_code
333 into p_base_lang
334 from fnd_languages
335 where installed_flag = 'B';
336
337 select userenv('LANG')
338 into p_userenv_lang
339 from dual;
340
341 if p_userenv_lang is null then
342 language_code := p_base_lang;
343 else
344 language_code := p_userenv_lang;
345 end if;
346
347 /*----------------------------------------------------------------------+
348 | Define Tables and aliases |
349 +----------------------------------------------------------------------*/
350
351 table1 :=
352
353 ' AR_ADJUSTMENTS COM_ADJ, ' || cr ||
354 ' AR_PAYMENT_SCHEDULES P, ' || cr ||
355 ' RA_CUST_TRX_LINE_GL_DIST REC, ' || cr ||
356 ' RA_CUSTOMER_TRX A, ' || cr ||
357 ' HZ_CUST_ACCOUNTS B, ' || cr ||
358 ' RA_TERMS T, ' || cr ||
359 ' RA_TERMS_LINES TL, ' || cr ||
360 ' RA_CUST_TRX_TYPES TYPES, ' || cr ||
361 ' AR_LOOKUPS L_TYPES, ' || cr ||
362 ' HZ_PARTIES PARTY, ' || cr || -- bug 1630907
363 ' HZ_CUST_ACCT_SITES A_BILL, ' || cr ||
364 ' HZ_PARTY_SITES PARTY_SITE, ' || cr ||
365 ' HZ_LOCATIONS LOC, ' || cr ||
366 ' HZ_CUST_SITE_USES U_BILL ' || cr;
367
368 table2 :=
369
370 ' RA_TERMS_LINES TL, ' || cr ||
371 ' RA_CUST_TRX_TYPES TYPES, ' || cr ||
372 ' AR_LOOKUPS L_TYPES, ' || cr ||
373 ' HZ_CUST_ACCOUNTS B, ' || cr ||
374 ' HZ_PARTIES PARTY, ' || cr || --bug 1630907
375 ' HZ_CUST_SITE_USES U_BILL, ' || cr ||
376 ' HZ_CUST_ACCT_SITES A_BILL, ' || cr ||
377 ' HZ_PARTY_SITES PARTY_SITE, ' || cr ||
378 ' HZ_LOCATIONS LOC, ' || cr ||
379 ' AR_ADJUSTMENTS COM_ADJ, ' || cr ||
380 ' RA_CUSTOMER_TRX A, ' || cr ||
381 ' AR_PAYMENT_SCHEDULES P, ' || cr ||
382 ' RA_TERMS T ' || cr;
383
384
385
386 /*----------------------------------------------------------------------+
387 | Determine which clause to use |
388 +----------------------------------------------------------------------*/
389 if P_choice = 'NEW' then
390 choice := sel_new;
391 elsif p_choice = 'ADJ' then
392 choice := sel_adj;
393 elsif P_choice = 'SEL' then
394 if trx_number_low is not NULL and trx_number_high is not NULL then
395 choice := sel_inv;
396 else
397 choice := sel_sel;
398 end if;
399 elsif P_choice = 'ONE' then
400 begin
401 if P_customer_trx_id is NULL then
402 arp_standard.fnd_message( 'AR_MAND_PARAMETER_NULL',
403 'PARAM', 'P_customer_trx_id' );
404 end if;
405 choice := sel_one;
406 end;
407 elsif P_choice = 'BATCH' then
408 begin
409 if P_batch_id is NULL then
410 arp_standard.fnd_message( 'AR_MAND_PARAMETER_NULL',
411 'PARAM', 'P_batch_id' );
412 end if;
413 choice := sel_batch;
414 end;
415 else
416 arp_standard.fnd_message( 'AR_RAXINV_INVALID_PARAMETERS',
417 'PARAM', 'P_choice' );
418 end if;
419
420 where1 := where1 || 'A.BILL_TO_CUSTOMER_ID = B.CUST_ACCOUNT_ID';
421
422 /* Join to receivable record */
423
424 where1 := where1 || cr || 'AND REC.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID';
425 where1 := where1 || cr || 'AND REC.LATEST_REC_FLAG = ''Y'' ';
426 where1 := where1 || cr || 'AND REC.ACCOUNT_CLASS = ''REC'' ';
427
428 /* Join to ar_adjustments for children of commitments. */
429
430 where1 := where1 || cr || 'AND P.PAYMENT_SCHEDULE_ID + DECODE(P.CLASS, ';
431 where1 := where1 || cr || ' ''INV'', 0,';
432 where1 := where1 || cr || ' '''')';
433 where1 := where1 || cr || ' = COM_ADJ.PAYMENT_SCHEDULE_ID(+)';
434 where1 := where1 || cr || 'AND COM_ADJ.SUBSEQUENT_TRX_ID IS NULL';
435 where1 := where1 || cr || 'AND ''C'' = COM_ADJ.ADJUSTMENT_TYPE(+)';
436
437 if P_Choice <> 'ADJ' THEN
438 where1 := where1 || cr || 'AND A.COMPLETE_FLAG = ''Y''';
439
440 /* bug 762450 :
441 if P_Choice <> 'SEL' THEN
442 where1 := where1 || cr || 'AND A.PRINTING_OPTION IN (''PRI'', ''REP'')';
443 end if;
444 */
445 end if;
446
447 where1 := where1 || cr || 'AND A.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID';
448 where1 := where1 || cr || 'AND L_TYPES.LOOKUP_TYPE = ''INV/CM/ADJ''';
449 -- bug 762450 :
450 -- where1 := where1 || cr || 'AND TYPES.DEFAULT_PRINTING_OPTION = ''PRI''';
451 where1 := where1 || cr || 'AND A.PRINTING_OPTION IN (''PRI'', ''REP'')';
455 where1 := where1 || cr || 'AND L_TYPES.LOOKUP_CODE = ''ADJ''';
452
453 if p_choice = 'ADJ'
454 then
456 else
457 where1 := where1 || cr || 'AND L_TYPES.LOOKUP_CODE = ';
458 where1 := where1 || cr || 'DECODE( TYPES.TYPE,''DEP'',''INV'', TYPES.TYPE)';
459
460 end if;
461 where1 := where1 || cr || 'AND NVL(P.TERMS_SEQUENCE_NUMBER,nvl(TL.SEQUENCE_NUM,0))=nvl(TL.SEQUENCE_NUM,nvl(p.terms_sequence_number,0))';
462 where1 := where1 || cr || 'AND DECODE(P.PAYMENT_SCHEDULE_ID,'''',0, NVL(T.PRINTING_LEAD_DAYS,0))=0';
463 where1 := where1 || cr || 'AND A.BILL_TO_SITE_USE_ID = U_BILL.SITE_USE_ID';
464 where1 := where1 || cr || 'AND U_BILL.CUST_ACCT_SITE_ID = A_BILL.CUST_ACCT_SITE_ID';
465 where1 := where1 || cr || 'AND A_BILL.party_site_id = party_site.party_site_id';
466 where1 := where1 || cr || 'AND B.PARTY_ID = PARTY.PARTY_ID'; -- bug 1630907
467 where1 := where1 || cr || 'AND loc.location_id = party_site.location_id';
468
469 /*Bug 8448291,chaging p_base_lang to language_code*/
470 if p_call_from = 'INV' then
471 where1 := where1 || cr || 'AND NVL(LOC.LANGUAGE,''' || language_code || ''') = ''' || language_code || '''';
472 end if;
473
474 if substr(upper(p_open_invoice),1,1) = 'Y'
475 then
476 -- Bug Fix 359960, where Credit memos are printed irrespective of whether they are open
477 -- or not is not what the documentation says. CM's like all other debit items should be
478 -- dependent on the p_open_invoice parameter. Hence reverting back the changes made for
479 -- Bug 359960. Changes made for bug 1639132.
480
481 where1 := where1 || cr || 'AND NVL(P.AMOUNT_DUE_REMAINING,0) <> 0';
482
483 end if;
484
485 if P_customer_id is not NULL then
486 where1 := where1 || cr || 'AND B.CUST_ACCOUNT_ID = ' || P_customer_id;
487 end if;
488 if P_cust_trx_type_id is not NULL then
489 where1 := where1 || cr || 'AND A.CUST_TRX_TYPE_ID = ' || P_cust_trx_type_id;
490 end if;
491
492 if p_cust_trx_class is not null then
493 where1 := where1 || cr || 'AND TYPES.TYPE = ''' || p_cust_trx_class
494 || '''';
495 end if;
496
497 where1 := where1 || cr || 'AND A.TERM_ID = TL.TERM_ID(+)';
498 where1 := where1 || cr || 'AND A.TERM_ID = T.TERM_ID(+)';
499 where1 := where1 || cr || 'AND T.billing_cycle_id is null ';
500
501 if P_installment_number is not NULL then
502 where1 := where1 || cr || 'AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID';
503 where1 := where1 || cr || 'AND NVL(TL.SEQUENCE_NUM, 1) = '
504 || P_installment_number;
505 else
506 where1 := where1 || cr || 'AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID(+)';
507
508 end if;
509
510 if dates_low is not NULL and dates_high is not NULL then
511 where1 := where1 || cr || 'AND A.TRX_DATE BETWEEN TO_DATE(''';
512 where1 := where1 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
513 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
514 where1 := where1 || cr || ' AND TO_DATE(''';
515 where1 := where1 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
516 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
517 elsif dates_low is not NULL then
518 where1 := where1 || cr || 'AND A.TRX_DATE >= TO_DATE(''';
519 where1 := where1 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
520 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
521 elsif dates_high is not NULL then
522 where1 := where1 || cr || 'AND A.TRX_DATE <= TO_DATE(''';
523 where1 := where1 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
524 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
525 end if;
526
527 if P_customer_class_code is not NULL then
528 where1 := where1 || cr || 'AND B.CUSTOMER_CLASS_CODE = ''' || P_customer_class_code || '''';
529 end if;
530
531 where2 := where2 || 'A.BILL_TO_CUSTOMER_ID = B.CUST_ACCOUNT_ID';
532
533 /* Join to ar_adjyustments for children of commitments. */
534 where2 := where2 || cr || 'AND P.PAYMENT_SCHEDULE_ID + DECODE(P.CLASS, ';
535 where2 := where2 || cr || ' ''INV'', 0,';
536 where2 := where2 || cr || ' '''')';
537 where2 := where2 || cr || ' = COM_ADJ.PAYMENT_SCHEDULE_ID(+)';
538
539 where2 := where2 || cr || 'AND COM_ADJ.SUBSEQUENT_TRX_ID IS NULL';
540 where2 := where2 || cr || 'AND ''C'' = COM_ADJ.ADJUSTMENT_TYPE(+)';
541
542 if P_Choice <> 'ADJ' THEN
543 where2 := where2 || cr || 'AND A.COMPLETE_FLAG = ''Y''';
544
545 /* BUG 762450
546 if P_Choice <> 'SEL' THEN
547 where2 := where2 || cr || 'AND A.PRINTING_OPTION IN (''PRI'', ''REP'')';
548 end if;
549 */
550
551 end if;
552
553 where2 := where2 || cr || 'AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID';
554 where2 := where2 || cr || 'AND A.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID';
555 where2 := where2 || cr || 'AND L_TYPES.LOOKUP_TYPE = ''INV/CM/ADJ''';
556
557 -- bug 762450
558 -- where2 := where2 || cr || 'AND TYPES.DEFAULT_PRINTING_OPTION = ''PRI''';
559 where2 := where2 || cr || 'AND A.PRINTING_OPTION IN (''PRI'', ''REP'')';
560
561 if p_choice = 'ADJ'
562 then
563 where2 := where2 || cr || 'AND L_TYPES.LOOKUP_CODE = ''ADJ''';
564 else
565 where2 := where2 || cr || 'AND L_TYPES.LOOKUP_CODE = ';
566 where2 := where2 || cr || 'DECODE( TYPES.TYPE,''DEP'',''INV'', TYPES.TYPE)';
567
568 end if;
569
570 where2 := where2 || cr || 'AND NVL(T.PRINTING_LEAD_DAYS,0) > 0';
571 where2 := where2 || cr || 'AND A.BILL_TO_SITE_USE_ID = U_BILL.SITE_USE_ID';
575 where2 := where2 || cr || 'AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID';
572 where2 := where2 || cr || 'AND U_BILL.CUST_ACCT_SITE_ID = A_BILL.CUST_ACCT_SITE_ID';
573 where2 := where2 || cr || 'AND A_BILL.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID';
574 where2 := where2 || cr || 'AND B.PARTY_ID = PARTY.PARTY_ID'; -- bug 1630907
576
577 /*Bug 8448291,chaging p_base_lang to language_code*/
581
578 if p_call_from = 'INV' then
579 where2 := where2 || cr || 'AND NVL(LOC.LANGUAGE,''' || language_code || ''') = ''' || language_code || '''';
580 end if;
582 where2 := where2 || cr || 'AND NVL(P.TERMS_SEQUENCE_NUMBER,TL.SEQUENCE_NUM)=TL.SEQUENCE_NUM';
583
584 if substr(upper(p_open_invoice),1,1) = 'Y'
585 then
586 -- Bug Fix 359960, where Credit memos are printed irrespective of whether they are open
587 -- or not is not what the documentation says. CM's like all other debit items should be
588 -- dependent on the p_open_invoice parameter. Hence reverting back the changes made for
589 -- Bug 359960. Changes made for bug 1639132.
590
591 where2 := where2 || cr || 'AND NVL(P.AMOUNT_DUE_REMAINING,0) <> 0';
592 end if;
593
594 if P_customer_id is not NULL then
595 where2 := where2 || cr || 'AND B.CUST_ACCOUNT_ID = ' || P_customer_id;
596 end if;
597
598 if P_cust_trx_type_id is not NULL then
599 where2 := where2 || cr || 'AND A.CUST_TRX_TYPE_ID = ' || P_cust_trx_type_id;
600 end if;
601
602 if p_cust_trx_class is not null then
603 where2 := where2 || cr || 'AND TYPES.TYPE = ''' || p_cust_trx_class || '''';
604 end if;
605
606
607 if P_installment_number is not NULL then
608 where2 := where2 || cr || 'AND T.TERM_ID = A.TERM_ID' ;
609 where2 := where2 || cr || 'AND TL.TERM_ID = T.TERM_ID' ;
610 where2 := where2 || cr || 'AND P.TERMS_SEQUENCE_NUMBER = ' || P_installment_number ;
611 else
612 where2 := where2 || cr || 'AND T.TERM_ID = P.TERM_ID' ;
613 where2 := where2 || cr || 'AND TL.TERM_ID(+) = T.TERM_ID' ;
614 end if;
615 where2 := where2 || cr || 'AND T.billing_cycle_id is null ';
616
617 if dates_low is not NULL and dates_high is not NULL then
618 where2 := where2 || cr || 'AND P.DUE_DATE BETWEEN TO_DATE(''';
619 where2 := where2 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
620 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
621 where2 := where2 || cr || ' + NVL (T.PRINTING_LEAD_DAYS, 0)';
622 where2 := where2 || cr || ' AND TO_DATE(''';
623 where2 := where2 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
624 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
625 where2 := where2 || cr || ' + NVL (T.PRINTING_LEAD_DAYS, 0)';
626 elsif dates_low is not NULL then
627 where2 := where2 || cr || 'AND P.DUE_DATE >= TO_DATE(''';
628 where2 := where2 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
629 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'') + NVL (T.PRINTING_LEAD_DAYS, 0)';
630 elsif dates_high is not NULL then
631 where2 := where2 || cr || 'AND P.DUE_DATE <= TO_DATE(''';
632 where2 := where2 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
633 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'') + NVL (T.PRINTING_LEAD_DAYS, 0)';
634 end if;
635 if P_customer_class_code is not NULL then
636 where2 := where2 || cr || 'AND B.CUSTOMER_CLASS_CODE = ''' || P_customer_class_code || '''';
637 end if;
638
639 if choice = sel_new then
640 goto sel_new_invoice;
641 elsif choice = sel_inv then
642 goto sel_selected_inv;
643 elsif choice = sel_sel then
644 goto sel_selected;
645 elsif choice = sel_batch then
646 goto sel_batch_invoice;
647 elsif choice = sel_one then
648 goto sel_one_invoice;
649 elsif choice = sel_adj then
650 goto sel_adj_adjustments;
651 end if;
652
653
654 /*----------------------------------------------------------------------+
655 | select new invoices |
656 +----------------------------------------------------------------------*/
657 <<sel_new_invoice>>
658
659 where1 := where1 || cr || 'AND A.PRINTING_PENDING = ''Y''';
660 where1 := where1 || cr ||
661 'AND NVL(TL.SEQUENCE_NUM, 1) > NVL(A.LAST_PRINTED_SEQUENCE_NUM,0)';
662
663 where2 := where2 || cr || 'AND A.PRINTING_PENDING = ''Y''';
664 where2 := where2 || cr || 'AND P.TERMS_SEQUENCE_NUMBER > NVL(A.LAST_PRINTED_SEQUENCE_NUM,0)';
665
666 /*----------------------------------------------------------------------+
667 | select selected invoices |
668 +----------------------------------------------------------------------*/
669 <<sel_selected_inv>>
670
671 where3 := '';
672 if trx_number_low is not NULL and trx_number_high is not NULL then
673 where3 := where3 || cr || 'AND A.TRX_NUMBER BETWEEN';
674 where3 := where3 || ' ''' || trx_number_low || '''';
675 where3 := where3 || ' AND ''' || trx_number_high || '''';
676 elsif trx_number_low is not NULL then
677 where3 := where3 || cr || 'AND A.TRX_NUMBER >= ''' || trx_number_low || '''';
678 elsif trx_number_high is not NULL then
679 where3 := where3 || cr || 'AND A.TRX_NUMBER <= ''' || trx_number_high || '''';
680 end if;
681
682 where1 := where1 || where3;
683 where2 := where2 || where3;
684
685 goto ok_exit;
686
687
688 /*----------------------------------------------------------------------+
689 | otherwise |
693 goto ok_exit;
690 +----------------------------------------------------------------------*/
691 <<sel_selected>>
692
694
695
696
697 /*----------------------------------------------------------------------+
698 | select by batch |
699 +----------------------------------------------------------------------*/
700 <<sel_batch_invoice>>
701
702 if P_batch_id is not NULL then
703 where1 := where1 || cr || 'AND A.BATCH_ID = ' || P_batch_id;
704 where2 := where2 || cr || 'AND A.BATCH_ID = ' || P_batch_id;
705 end if;
706
707 goto ok_exit;
708
709
710 /*----------------------------------------------------------------------+
711 | select one invoice |
712 +----------------------------------------------------------------------*/
713 <<sel_one_invoice>>
714
715 if P_customer_trx_id is not NULL then
716 where1 := where1 || cr || 'AND A.CUSTOMER_TRX_ID = ' || P_customer_trx_id;
717 where2 := where2 || cr || 'AND A.CUSTOMER_TRX_ID = ' || P_customer_trx_id;
718 end if;
719
720 goto ok_exit;
721
722
723 /*----------------------------------------------------------------------+
724 | select adjustments |
725 +----------------------------------------------------------------------*/
726 <<sel_adj_adjustments>>
727
728 table1 := table1 || ', AR_ADJUSTMENTS ADJ' || cr ;
729 table2 := table2 || ', AR_ADJUSTMENTS ADJ' || cr ;
730
731 where3 := cr || 'and a.customer_trx_id = adj.customer_trx_id ' ;
732
733 if adj_number_low is not null or adj_number_high is not null
734 then
735 begin
736 if adj_number_low is not NULL and adj_number_high is not NULL then
737 where3 := where3 || cr || 'AND ADJ.ADJUSTMENT_NUMBER BETWEEN';
738 where3 := where3 || ' ''' || adj_number_low || '''';
739 where3 := where3 || ' AND ''' || adj_number_high || '''';
740 elsif adj_number_low is not NULL then
741 where3 := where3 || cr || 'AND ADJ.ADJUSTMENT_NUMBER >= ''' || adj_number_low || '''';
742 elsif adj_number_high is not NULL then
743 where3 := where3 || cr || 'AND ADJ.ADJUSTMENT_NUMBER <= ''' || adj_number_high || '''';
744 end if;
745 end;
746 end if;
747
748 if adj_dates_low is not null or adj_dates_high is not null
749 then
750 begin
751
752 if adj_dates_low is not NULL and adj_dates_high is not NULL then
753
754 where3 := where3 || cr || 'AND ADJ.APPLY_DATE BETWEEN TO_DATE(''';
755 where3 := where3 || TO_CHAR(adj_dates_low,'DD-MM-YYYY-HH24:MI:SS');
756 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
757 where3 := where3 || cr || ' AND TO_DATE(''';
758 where3 := where3 || TO_CHAR(adj_dates_high,'DD-MM-YYYY-HH24:MI:SS');
759 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
760
761 elsif adj_dates_low is not NULL then
762
763 where3 := where3 || cr || 'AND ADJ.APPLY_DATE >= TO_DATE(''';
764 where3 := where3 || TO_CHAR(adj_dates_low,'DD-MM-YYYY-HH24:MI:SS');
765 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
766
767 elsif adj_dates_high is not NULL then
768
769 where3 := where3 || cr || 'AND ADJ.APPLY_DATE <= TO_DATE(''';
770 where3 := where3 || TO_CHAR(adj_dates_high,'DD-MM-YYYY-HH24:MI:SS');
771 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
772
773 end if;
774 end;
775 end if;
776
777 where3 := where3 || cr || 'AND ADJ.STATUS = ''A''';
778
779 where1 := where1 || where3;
780 where2 := where2 || where3;
781
782
783 goto sel_selected_inv;
784
785
786 /*----------------------------------------------------------------------+
787 | exit successfully |
788 +----------------------------------------------------------------------*/
789 <<ok_exit>>
790 P_where1 := where1;
791 P_where2 := where2;
792 p_table1 := table1;
793 p_table2 := table2;
794 END; /* end of procedure build_where_clause */
795
796 end ARP_TRX_SELECT_CONTROL;