[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_SELECT_CONTROL
Source
1 PACKAGE BODY ARP_TRX_SELECT_CONTROL AS
2 /* $Header: ARPLTSCB.pls 115.11 2002/11/15 02:43:54 anukumar ship $ */
3
4 /*---------------------------------------------------------------------------+
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; |
70 | P_dates_high optional date; |
71 | P_customer_id optional number; |
72 | P_customer_class_code optional varchar2; |
73 | P_trx_number_low 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' ) |
175 | P_cust_trx_type_id optional number; |
176 | P_installment_number optional number; |
177 | P_dates_low optional date; |
178 | P_dates_high 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, '''', '''''') ;
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, '''', '''''') ;
318
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 /*----------------------------------------------------------------------+
351 table1 :=
348 | Define Tables and aliases |
349 +----------------------------------------------------------------------*/
350
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'')';
452
453 if p_choice = 'ADJ'
454 then
455 where1 := where1 || cr || 'AND L_TYPES.LOOKUP_CODE = ''ADJ''';
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';
468 if p_call_from = 'INV' then
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';
469 where1 := where1 || cr || 'AND NVL(LOC.LANGUAGE,''' || p_base_lang || ''') = ''' || language_code || '''';
470 end if;
471
472 if substr(upper(p_open_invoice),1,1) = 'Y'
473 then
474 -- Bug Fix 359960, where Credit memos are printed irrespective of whether they are open
475 -- or not is not what the documentation says. CM's like all other debit items should be
476 -- dependent on the p_open_invoice parameter. Hence reverting back the changes made for
477 -- Bug 359960. Changes made for bug 1639132.
478
479 where1 := where1 || cr || 'AND NVL(P.AMOUNT_DUE_REMAINING,0) <> 0';
480
481 end if;
482
483 if P_customer_id is not NULL then
484 where1 := where1 || cr || 'AND B.CUST_ACCOUNT_ID = ' || P_customer_id;
485 end if;
486 if P_cust_trx_type_id is not NULL then
487 where1 := where1 || cr || 'AND A.CUST_TRX_TYPE_ID = ' || P_cust_trx_type_id;
488 end if;
489
490 if p_cust_trx_class is not null then
491 where1 := where1 || cr || 'AND TYPES.TYPE = ''' || p_cust_trx_class
492 || '''';
493 end if;
494
495 where1 := where1 || cr || 'AND A.TERM_ID = TL.TERM_ID(+)';
496 where1 := where1 || cr || 'AND A.TERM_ID = T.TERM_ID(+)';
497
498 if P_installment_number is not NULL then
499 where1 := where1 || cr || 'AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID';
500 where1 := where1 || cr || 'AND NVL(TL.SEQUENCE_NUM, 1) = '
501 || P_installment_number;
502 else
503 where1 := where1 || cr || 'AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID(+)';
504
505 end if;
506
507 if dates_low is not NULL and dates_high is not NULL then
508 where1 := where1 || cr || 'AND A.TRX_DATE BETWEEN TO_DATE(''';
509 where1 := where1 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
510 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
511 where1 := where1 || cr || ' AND TO_DATE(''';
512 where1 := where1 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
513 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
514 elsif dates_low is not NULL then
515 where1 := where1 || cr || 'AND A.TRX_DATE >= TO_DATE(''';
516 where1 := where1 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
517 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
518 elsif dates_high is not NULL then
519 where1 := where1 || cr || 'AND A.TRX_DATE <= TO_DATE(''';
520 where1 := where1 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
521 where1 := where1 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
522 end if;
523
524 if P_customer_class_code is not NULL then
525 where1 := where1 || cr || 'AND B.CUSTOMER_CLASS_CODE = ''' || P_customer_class_code || '''';
526 end if;
527
528 where2 := where2 || 'A.BILL_TO_CUSTOMER_ID = B.CUST_ACCOUNT_ID';
529
530 /* Join to ar_adjyustments for children of commitments. */
531 where2 := where2 || cr || 'AND P.PAYMENT_SCHEDULE_ID + DECODE(P.CLASS, ';
532 where2 := where2 || cr || ' ''INV'', 0,';
533 where2 := where2 || cr || ' '''')';
534 where2 := where2 || cr || ' = COM_ADJ.PAYMENT_SCHEDULE_ID(+)';
535
536 where2 := where2 || cr || 'AND COM_ADJ.SUBSEQUENT_TRX_ID IS NULL';
537 where2 := where2 || cr || 'AND ''C'' = COM_ADJ.ADJUSTMENT_TYPE(+)';
538
539 if P_Choice <> 'ADJ' THEN
540 where2 := where2 || cr || 'AND A.COMPLETE_FLAG = ''Y''';
541
542 /* BUG 762450
543 if P_Choice <> 'SEL' THEN
544 where2 := where2 || cr || 'AND A.PRINTING_OPTION IN (''PRI'', ''REP'')';
545 end if;
546 */
547
548 end if;
549
550 where2 := where2 || cr || 'AND A.CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID';
551 where2 := where2 || cr || 'AND A.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID';
552 where2 := where2 || cr || 'AND L_TYPES.LOOKUP_TYPE = ''INV/CM/ADJ''';
553
554 -- bug 762450
555 -- where2 := where2 || cr || 'AND TYPES.DEFAULT_PRINTING_OPTION = ''PRI''';
556 where2 := where2 || cr || 'AND A.PRINTING_OPTION IN (''PRI'', ''REP'')';
557
558 if p_choice = 'ADJ'
559 then
560 where2 := where2 || cr || 'AND L_TYPES.LOOKUP_CODE = ''ADJ''';
561 else
562 where2 := where2 || cr || 'AND L_TYPES.LOOKUP_CODE = ';
563 where2 := where2 || cr || 'DECODE( TYPES.TYPE,''DEP'',''INV'', TYPES.TYPE)';
564
565 end if;
566
567 where2 := where2 || cr || 'AND NVL(T.PRINTING_LEAD_DAYS,0) > 0';
568 where2 := where2 || cr || 'AND A.BILL_TO_SITE_USE_ID = U_BILL.SITE_USE_ID';
569 where2 := where2 || cr || 'AND U_BILL.CUST_ACCT_SITE_ID = A_BILL.CUST_ACCT_SITE_ID';
570 where2 := where2 || cr || 'AND A_BILL.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID';
571 where2 := where2 || cr || 'AND B.PARTY_ID = PARTY.PARTY_ID'; -- bug 1630907
572 where2 := where2 || cr || 'AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID';
573
574 if p_call_from = 'INV' then
575 where2 := where2 || cr || 'AND NVL(LOC.LANGUAGE,''' || p_base_lang || ''') = ''' || language_code || '''';
576 end if;
580 then
577 where2 := where2 || cr || 'AND NVL(P.TERMS_SEQUENCE_NUMBER,TL.SEQUENCE_NUM)=TL.SEQUENCE_NUM';
578
579 if substr(upper(p_open_invoice),1,1) = 'Y'
581 -- Bug Fix 359960, where Credit memos are printed irrespective of whether they are open
582 -- or not is not what the documentation says. CM's like all other debit items should be
583 -- dependent on the p_open_invoice parameter. Hence reverting back the changes made for
584 -- Bug 359960. Changes made for bug 1639132.
585
586 where2 := where2 || cr || 'AND NVL(P.AMOUNT_DUE_REMAINING,0) <> 0';
587 end if;
588
589 if P_customer_id is not NULL then
590 where2 := where2 || cr || 'AND B.CUST_ACCOUNT_ID = ' || P_customer_id;
591 end if;
592
593 if P_cust_trx_type_id is not NULL then
594 where2 := where2 || cr || 'AND A.CUST_TRX_TYPE_ID = ' || P_cust_trx_type_id;
595 end if;
596
597 if p_cust_trx_class is not null then
598 where2 := where2 || cr || 'AND TYPES.TYPE = ''' || p_cust_trx_class || '''';
599 end if;
600
601
602 if P_installment_number is not NULL then
603 where2 := where2 || cr || 'AND T.TERM_ID = A.TERM_ID' ;
604 where2 := where2 || cr || 'AND TL.TERM_ID = T.TERM_ID' ;
605 where2 := where2 || cr || 'AND P.TERMS_SEQUENCE_NUMBER = ' || P_installment_number ;
606 else
607 where2 := where2 || cr || 'AND T.TERM_ID = P.TERM_ID' ;
608 where2 := where2 || cr || 'AND TL.TERM_ID(+) = T.TERM_ID' ;
609 end if;
610
611 if dates_low is not NULL and dates_high is not NULL then
612 where2 := where2 || cr || 'AND P.DUE_DATE BETWEEN TO_DATE(''';
613 where2 := where2 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
614 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
615 where2 := where2 || cr || ' + NVL (T.PRINTING_LEAD_DAYS, 0)';
616 where2 := where2 || cr || ' AND TO_DATE(''';
617 where2 := where2 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
618 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
619 where2 := where2 || cr || ' + NVL (T.PRINTING_LEAD_DAYS, 0)';
620 elsif dates_low is not NULL then
621 where2 := where2 || cr || 'AND P.DUE_DATE >= TO_DATE(''';
622 where2 := where2 || TO_CHAR(dates_low,'DD-MM-YYYY-HH24:MI:SS');
623 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'') + NVL (T.PRINTING_LEAD_DAYS, 0)';
624 elsif dates_high is not NULL then
625 where2 := where2 || cr || 'AND P.DUE_DATE <= TO_DATE(''';
626 where2 := where2 || TO_CHAR(dates_high,'DD-MM-YYYY-HH24:MI:SS');
627 where2 := where2 || ''',''DD-MM-YYYY-HH24:MI:SS'') + NVL (T.PRINTING_LEAD_DAYS, 0)';
628 end if;
629 if P_customer_class_code is not NULL then
630 where2 := where2 || cr || 'AND B.CUSTOMER_CLASS_CODE = ''' || P_customer_class_code || '''';
631 end if;
632
633 if choice = sel_new then
634 goto sel_new_invoice;
635 elsif choice = sel_inv then
636 goto sel_selected_inv;
637 elsif choice = sel_sel then
638 goto sel_selected;
639 elsif choice = sel_batch then
640 goto sel_batch_invoice;
641 elsif choice = sel_one then
642 goto sel_one_invoice;
643 elsif choice = sel_adj then
644 goto sel_adj_adjustments;
645 end if;
646
647
648 /*----------------------------------------------------------------------+
649 | select new invoices |
650 +----------------------------------------------------------------------*/
651 <<sel_new_invoice>>
652
653 where1 := where1 || cr || 'AND A.PRINTING_PENDING = ''Y''';
654 where1 := where1 || cr ||
655 'AND NVL(TL.SEQUENCE_NUM, 1) > NVL(A.LAST_PRINTED_SEQUENCE_NUM,0)';
656
657 where2 := where2 || cr || 'AND A.PRINTING_PENDING = ''Y''';
658 where2 := where2 || cr || 'AND P.TERMS_SEQUENCE_NUMBER > NVL(A.LAST_PRINTED_SEQUENCE_NUM,0)';
659
660 /*----------------------------------------------------------------------+
661 | select selected invoices |
662 +----------------------------------------------------------------------*/
663 <<sel_selected_inv>>
664
665 where3 := '';
666 if trx_number_low is not NULL and trx_number_high is not NULL then
667 where3 := where3 || cr || 'AND A.TRX_NUMBER BETWEEN';
668 where3 := where3 || ' ''' || trx_number_low || '''';
669 where3 := where3 || ' AND ''' || trx_number_high || '''';
670 elsif trx_number_low is not NULL then
671 where3 := where3 || cr || 'AND A.TRX_NUMBER >= ''' || trx_number_low || '''';
672 elsif trx_number_high is not NULL then
673 where3 := where3 || cr || 'AND A.TRX_NUMBER <= ''' || trx_number_high || '''';
674 end if;
675
676 where1 := where1 || where3;
677 where2 := where2 || where3;
678
679 goto ok_exit;
680
681
682 /*----------------------------------------------------------------------+
683 | otherwise |
684 +----------------------------------------------------------------------*/
685 <<sel_selected>>
686
687 goto ok_exit;
688
689
690
691 /*----------------------------------------------------------------------+
692 | select by batch |
693 +----------------------------------------------------------------------*/
694 <<sel_batch_invoice>>
695
696 if P_batch_id is not NULL then
697 where1 := where1 || cr || 'AND A.BATCH_ID = ' || P_batch_id;
698 where2 := where2 || cr || 'AND A.BATCH_ID = ' || P_batch_id;
699 end if;
700
704 /*----------------------------------------------------------------------+
701 goto ok_exit;
702
703
705 | select one invoice |
706 +----------------------------------------------------------------------*/
707 <<sel_one_invoice>>
708
709 if P_customer_trx_id is not NULL then
710 where1 := where1 || cr || 'AND A.CUSTOMER_TRX_ID = ' || P_customer_trx_id;
711 where2 := where2 || cr || 'AND A.CUSTOMER_TRX_ID = ' || P_customer_trx_id;
712 end if;
713
714 goto ok_exit;
715
716
717 /*----------------------------------------------------------------------+
718 | select adjustments |
719 +----------------------------------------------------------------------*/
720 <<sel_adj_adjustments>>
721
722 table1 := table1 || ', AR_ADJUSTMENTS ADJ' || cr ;
723 table2 := table2 || ', AR_ADJUSTMENTS ADJ' || cr ;
724
725 where3 := cr || 'and a.customer_trx_id = adj.customer_trx_id ' ;
726
727 if adj_number_low is not null or adj_number_high is not null
728 then
729 begin
730 if adj_number_low is not NULL and adj_number_high is not NULL then
731 where3 := where3 || cr || 'AND ADJ.ADJUSTMENT_NUMBER BETWEEN';
732 where3 := where3 || ' ''' || adj_number_low || '''';
733 where3 := where3 || ' AND ''' || adj_number_high || '''';
734 elsif adj_number_low is not NULL then
735 where3 := where3 || cr || 'AND ADJ.ADJUSTMENT_NUMBER >= ''' || adj_number_low || '''';
736 elsif adj_number_high is not NULL then
737 where3 := where3 || cr || 'AND ADJ.ADJUSTMENT_NUMBER <= ''' || adj_number_high || '''';
738 end if;
739 end;
740 end if;
741
742 if adj_dates_low is not null or adj_dates_high is not null
743 then
744 begin
745
746 if adj_dates_low is not NULL and adj_dates_high is not NULL then
747
748 where3 := where3 || cr || 'AND ADJ.APPLY_DATE BETWEEN TO_DATE(''';
749 where3 := where3 || TO_CHAR(adj_dates_low,'DD-MM-YYYY-HH24:MI:SS');
750 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
751 where3 := where3 || cr || ' AND TO_DATE(''';
752 where3 := where3 || TO_CHAR(adj_dates_high,'DD-MM-YYYY-HH24:MI:SS');
753 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
754
755 elsif adj_dates_low is not NULL then
756
757 where3 := where3 || cr || 'AND ADJ.APPLY_DATE >= TO_DATE(''';
758 where3 := where3 || TO_CHAR(adj_dates_low,'DD-MM-YYYY-HH24:MI:SS');
759 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
760
761 elsif adj_dates_high is not NULL then
762
763 where3 := where3 || cr || 'AND ADJ.APPLY_DATE <= TO_DATE(''';
764 where3 := where3 || TO_CHAR(adj_dates_high,'DD-MM-YYYY-HH24:MI:SS');
765 where3 := where3 || ''',''DD-MM-YYYY-HH24:MI:SS'')';
766
767 end if;
768 end;
769 end if;
770
771 where3 := where3 || cr || 'AND ADJ.STATUS = ''A''';
772
773 where1 := where1 || where3;
774 where2 := where2 || where3;
775
776
777 goto sel_selected_inv;
778
779
780 /*----------------------------------------------------------------------+
781 | exit successfully |
782 +----------------------------------------------------------------------*/
783 <<ok_exit>>
784 P_where1 := where1;
785 P_where2 := where2;
786 p_table1 := table1;
787 p_table2 := table2;
788 END; /* end of procedure build_where_clause */
789
790 end ARP_TRX_SELECT_CONTROL;