1 PACKAGE BODY ARP_ARXCOQIT AS
2 /* $Header: ARCEQITB.pls 120.8.12020000.3 2012/09/14 07:21:31 ankuagar ship $ */
3
4 /* Package private global variables */
5 TYPE literal_rec_type IS RECORD (
6 literal_counter NUMBER ,
7 bind_var_name VARCHAR2(1000) ,
8 stripped_value VARCHAR2(1000)
9 );
10
11 --
12 -- Stripped where clause literal table
13 --
14 TYPE literal_tbl_type IS TABLE of literal_rec_type
15 INDEX BY BINARY_INTEGER;
16
17 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
18
19 -- 3804333
20 emb_quote BOOLEAN := FALSE;
21
22 PROCEDURE Build_And_Bind ( p_in_where_clause IN VARCHAR2 ,
23 p_out_where_clause OUT NOCOPY VARCHAR2 ,
27 /*1806931
24 p_literal_tbl OUT NOCOPY literal_tbl_type ,
25 p_tbl_ctr OUT NOCOPY BINARY_INTEGER );
26
28 The total amount needs to be calculated only when the receipt
29 had been applied to invoices in the same currency. If the receipt
30 has been applied to invoices in different currencies, the total
31 amount need not be computed. Added the local variable l_cur_count to
32 get the total number of distinct currencies for the application.
33 */
34 procedure history_total( p_where_clause IN varchar2, p_total IN OUT NOCOPY number)is
35 l_select_cursor integer;
36 l_ignore integer;
37 l_amount number;
38 l_cur_count number; /* 1806931 */
39 begin
40 -- arp_standard.enable_debug;
41 IF PG_DEBUG in ('Y', 'C') THEN
42 arp_standard.debug('Build_And_Bind: ' || 'where clause:' || p_where_clause );
43 END IF;
44 /* 1806931 Code Added Begins. */
45 /* Bugfix for 2112098 Check if the where clause passed has a payment_schedule_id=.
46 If yes, then split the where-clause to make use of bind variable.
47 Else, continues to work just as the way it did before this fix*/
48
49 IF instr(p_where_clause,'PAYMENT_SCHEDULE_ID=') = 0 THEN
50
51 l_select_cursor := dbms_sql.open_cursor;
52 dbms_sql.parse(l_select_cursor,
53 'select count(distinct(currency)) from ar_app_adj_v
54 where'|| p_where_clause,dbms_sql.v7);
55 dbms_sql.define_column(l_select_cursor, 1 , l_cur_count);
56 l_ignore := dbms_sql.execute(l_select_cursor);
57 if dbms_sql.fetch_rows(l_select_cursor) > 0 then
58 dbms_sql.column_value(l_select_cursor, 1, l_cur_count);
59 else
60 IF PG_DEBUG in ('Y', 'C') THEN
61 arp_standard.debug('Build_And_Bind: ' || 'no rows');
62 END IF;
63 end if;
64 if (l_cur_count = 1) then
65 dbms_sql.close_cursor(l_select_cursor);
66 /* 1806931 Code added ends. */
67
68 l_select_cursor := dbms_sql.open_cursor;
69 dbms_sql.parse(l_select_cursor,
70 'select sum(total_amount) from ar_app_adj_v
71 where'|| p_where_clause, dbms_sql.v7);
72 dbms_sql.define_column(l_select_cursor, 1 , l_amount);
73 l_ignore := dbms_sql.execute(l_select_cursor);
74 if dbms_sql.fetch_rows(l_select_cursor) > 0 then
75 dbms_sql.column_value(l_select_cursor, 1, l_amount);
76 else
77 IF PG_DEBUG in ('Y', 'C') THEN
78 arp_standard.debug('Build_And_Bind: ' || 'no rows');
79 END IF;
80 end if;
81
82 p_total := l_amount;
83 IF PG_DEBUG in ('Y', 'C') THEN
84 arp_standard.debug('Build_And_Bind: ' || 'p_total =' || to_char(p_total) );
85 arp_standard.debug('Build_And_Bind: ' || 'l_amount =' || to_char(l_amount) );
86 END IF;
87 dbms_sql.close_cursor(l_select_cursor);
88
89 /*1806931 Code Added Begins */
90 else
91 p_total := 0;
92 IF PG_DEBUG in ('Y', 'C') THEN
93 arp_standard.debug('Build_And_Bind: ' || 'p_total =' || to_char(p_total) );
94 END IF;
95 dbms_sql.close_cursor(l_select_cursor);
96 end if;
97 /* 1806931 Code Added Ends */
98 ELSE
99 /* Bugfix for 2112098 begins here. Re-do the cursor with bind var for ps_id
100 if that is the parameter passed. */
101
102 DECLARE
103 l_select_stmt varchar2(1000);
104 l_bind_ps_id NUMBER;
105 l_where_clause varchar2(1000);
106 BEGIN
107 l_where_clause := replace(p_where_clause, ')');
108 l_bind_ps_id := to_number(substr(l_where_clause,22 ));
109
110 l_select_stmt := 'select count(distinct(currency)) from ar_app_adj_v
111 where PAYMENT_SCHEDULE_ID= :ps_id ';
112
113 l_select_cursor := dbms_sql.open_cursor;
114
115 dbms_sql.parse(l_select_cursor, l_select_stmt ,dbms_sql.v7);
116
117 dbms_sql.bind_variable ( l_select_cursor , ':ps_id' , l_bind_ps_id) ;
118
119 dbms_sql.define_column(l_select_cursor, 1 , l_cur_count);
120 l_ignore := dbms_sql.execute(l_select_cursor);
121
122 if dbms_sql.fetch_rows(l_select_cursor) > 0 then
123 dbms_sql.column_value(l_select_cursor, 1, l_cur_count);
124 else
125 IF PG_DEBUG in ('Y', 'C') THEN
126 arp_standard.debug('Build_And_Bind: ' || 'no rows');
127 END IF;
128 end if;
129 if (l_cur_count = 1) then
130 dbms_sql.close_cursor(l_select_cursor);
131
132 l_select_stmt := 'select sum(total_amount) from ar_app_adj_v
133 where PAYMENT_SCHEDULE_ID= :ps_id ';
134 IF PG_DEBUG in ('Y', 'C') THEN
135 arp_standard.debug('Build_And_Bind: ' || l_select_stmt);
136 END IF;
137
138 l_select_cursor := dbms_sql.open_cursor;
139
140 dbms_sql.parse(l_select_cursor,l_select_stmt , dbms_sql.v7);
141
142 dbms_sql.bind_variable ( l_select_cursor , ':ps_id' , l_bind_ps_id );
143
144 dbms_sql.define_column(l_select_cursor, 1 , l_amount);
145 l_ignore := dbms_sql.execute(l_select_cursor);
146 if dbms_sql.fetch_rows(l_select_cursor) > 0 then
147 dbms_sql.column_value(l_select_cursor, 1, l_amount);
148 else
149 IF PG_DEBUG in ('Y', 'C') THEN
150 arp_standard.debug('Build_And_Bind: ' || 'no rows');
151 END IF;
152 end if;
153
154 p_total := l_amount;
155 IF PG_DEBUG in ('Y', 'C') THEN
156 arp_standard.debug('Build_And_Bind: ' || 'p_total =' || to_char(p_total) );
160 else
157 arp_standard.debug('Build_And_Bind: ' || 'l_amount =' || to_char(l_amount) );
158 END IF;
159 dbms_sql.close_cursor(l_select_cursor);
161 p_total := 0;
162 IF PG_DEBUG in ('Y', 'C') THEN
163 arp_standard.debug('Build_And_Bind: ' || 'p_total =' || to_char(p_total) );
164 END IF;
165 dbms_sql.close_cursor(l_select_cursor);
166 end if;
167 END;
168 END IF;
169 /* End of bugfix 2112438 */
170
171 -- arp_standard.enable_debug;
172 EXCEPTION
173 WHEN OTHERS THEN
174 IF PG_DEBUG in ('Y', 'C') THEN
175 arp_standard.debug('Build_And_Bind: ' || 'Exception:'|| to_char(p_total));
176 END IF;
177 end;
178
179 -- Bug No. : 950002 : Removed folder_total and folder_func_total as these are included in fold_total.
180
181 /* This Procedure Calculates the entered total balance and the functional total
182 balance for the passed in 'where clause ' */
183 --Bug 1563252 : Added the new argument p_from_clause
184
185 /*
186 1826455 fbreslin: Add a new parameter p_cur_count. This will pass back to the
187 calling routine the number of distinct currencies that make
188 up the total.
189 */
190
191 procedure fold_total( p_where_clause IN varchar2,
192 p_total IN OUT NOCOPY number,
193 p_func_total IN OUT NOCOPY number,
194 p_from_clause IN varchar2 DEFAULT 'ar_payment_schedules_v',
195 p_cur_count OUT NOCOPY number) is
196 l_select_cursor INTEGER;
197 l_ignore INTEGER;
198 l_amount NUMBER;
199 l_func_amount NUMBER;
200 l_count NUMBER;
201 l_ctr BINARY_INTEGER;
202 l_tbl_ctr BINARY_INTEGER;
203 l_literal_tbl literal_tbl_type;
204 l_out_where_clause VARCHAR2(32767);
205 l_actual_bind_var VARCHAR2(2000);
206
207 -- 3804333
208 l_quote1 NUMBER;
209 l_quote2 NUMBER;
210 l_where_clause VARCHAR2(32767);
211 BEGIN
212
213 l_out_where_clause := '';
214
215 p_total := 0;
216
217 p_func_total := 0;
218
219 -- arp_standard.enable_debug;
220
221 /*-----------------------------------------------------------------------+
222 |Removed the 'WHERE' from the Parse Statement , It now comes in |
223 |p_where_clause before the actual where clause , This is done to take |
224 |care of the Null Where Clause Case. |
225 +-----------------------------------------------------------------------*/
226
227 IF PG_DEBUG in ('Y', 'C') THEN
228 arp_standard.debug('Build_And_Bind: ' || 'where clause:' || p_where_clause );
229 arp_standard.debug('Build_And_Bind: ' || 'Opening Cursor');
230 END IF;
231
232 l_select_cursor := dbms_sql.open_cursor;
233
234 /*-----------------------------------------------------------------------+
235 |Call the Build and Bind routine to strip where clause from literals and|
236 |numeric constants and replace them with bind variables. |
237 +-----------------------------------------------------------------------*/
238 IF p_where_clause IS NOT NULL THEN
239
240 -- Bug 3804333 : need to pre-process p_where_clause to check if trx_number has '
241 l_quote1 := instr(p_where_clause,'''',1,2);
242 l_quote2 := instr(p_where_clause,'''',l_quote1+1,1);
243 IF PG_DEBUG in ('Y', 'C') THEN
244 arp_standard.debug('l_quote1 = ' || to_char(l_quote1) ||
245 ' l_quote2 = ' || to_char(l_quote2));
246 END IF;
247 if l_quote2 - l_quote1 = 1 then
248 -- trx_number has embedded ', temporarily change ' to ^
249 l_where_clause := substrb(p_where_clause,1,l_quote1-1) ||
250 '^' || substrb(p_where_clause, l_quote2+1);
251 emb_quote := TRUE;
252 arp_standard.debug('l_where_clause = ' || l_where_clause);
253 else
254 l_where_clause := p_where_clause;
255 end if;
256
257 Build_And_Bind(l_where_clause, l_out_where_clause, l_literal_tbl, l_tbl_ctr);
258 END IF;
259
260 /*-----------------------------------------------------------------------+
261 |Parse the built statement along with the where clause. |
262 +-----------------------------------------------------------------------*/
263 IF PG_DEBUG in ('Y', 'C') THEN
264 arp_standard.debug('Build_And_Bind: ' || 'Parsing statement ');
265 arp_standard.debug('Build_And_Bind: ' || l_out_where_clause);
266 END IF;
267 --Bug 1563252 : modified the query string to add the from clause dynamically.
268
269 /* 3988361 :If the customer_id is used for selection the use n6 index */
270
271 IF p_from_clause = 'AR_PAYMENT_SCHEDULES_TRX2_V' AND INSTR(l_out_where_clause,'CUSTOMER_ID=') <> 0 AND INSTR(l_out_where_clause,'TRX_NUMBER=') <> 0 THEN
272 dbms_sql.parse(l_select_cursor,
273 'select /*+ INDEX(AR_PAYMENT_SCHEDULES_TRX2_V.ps AR_PAYMENT_SCHEDULES_N11) */
274 count( distinct invoice_currency_code ),
275 sum(amount_due_remaining),
276 sum(acctd_amount_due_remaining)
277 from '||p_from_clause||' '||l_out_where_clause, dbms_sql.v7);
278 ELSIF p_from_clause = 'AR_PAYMENT_SCHEDULES_TRX2_V' AND INSTR(l_out_where_clause,'CUSTOMER_ID=') <> 0 THEN
279 dbms_sql.parse(l_select_cursor,
280 'select /*+ INDEX(AR_PAYMENT_SCHEDULES_TRX2_V.ps AR_PAYMENT_SCHEDULES_N6) */
281 count( distinct invoice_currency_code ),
282 sum(amount_due_remaining),
283 sum(acctd_amount_due_remaining)
287 'select count( distinct invoice_currency_code ),
284 from '||p_from_clause||' '||l_out_where_clause, dbms_sql.v7);
285 ELSE
286 dbms_sql.parse(l_select_cursor,
288 sum(amount_due_remaining),
289 sum(acctd_amount_due_remaining)
290 from '||p_from_clause||' '||l_out_where_clause,
291 dbms_sql.v7);
292 END IF;
293
294 /*-----------------------------------------------------------------------+
295 |Define columns for the select statement. |
296 +-----------------------------------------------------------------------*/
297 IF PG_DEBUG in ('Y', 'C') THEN
298 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns +');
299 END IF;
300
301 dbms_sql.define_column(l_select_cursor, 1 , l_count);
302 dbms_sql.define_column(l_select_cursor, 2 , l_amount);
303 dbms_sql.define_column(l_select_cursor, 3 , l_func_amount);
304
305 IF PG_DEBUG in ('Y', 'C') THEN
306 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns -');
307 END IF;
308
309 /*-----------------------------------------------------------------------+
310 |Bind the variables built by Build_And_Bind routine with actual values |
311 +-----------------------------------------------------------------------*/
312 IF ((l_literal_tbl.EXISTS(l_tbl_ctr)) AND (p_where_clause IS NOT NULL)) THEN
313
314 IF PG_DEBUG in ('Y', 'C') THEN
315 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables +');
316 END IF;
317
318 FOR l_ctr in 1..l_tbl_ctr LOOP
319
320 l_actual_bind_var := '';
321
322 --Bind variables
323 IF PG_DEBUG in ('Y', 'C') THEN
324 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').bind_var_name = ' || l_literal_tbl(l_ctr).bind_var_name);
325 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').stripped_value = ' || l_literal_tbl(l_ctr).stripped_value);
326 END IF;
327
328 l_actual_bind_var := rtrim(ltrim(l_literal_tbl(l_ctr).bind_var_name));
329
330 IF PG_DEBUG in ('Y', 'C') THEN
331 arp_standard.debug('Build_And_Bind: ' || 'l_actual_bind_var = '||l_actual_bind_var);
332 END IF;
333
334 dbms_sql.bind_variable(l_select_cursor, l_actual_bind_var, l_literal_tbl(l_ctr).stripped_value);
335
336 END LOOP;
337
338 IF PG_DEBUG in ('Y', 'C') THEN
339 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables -');
340 END IF;
341
342 END IF;
343
344 /*-----------------------------------------------------------------------+
345 |Execute the SQL statement to calculate functional amount and accounted |
346 |amount totals. |
347 +-----------------------------------------------------------------------*/
348 IF PG_DEBUG in ('Y', 'C') THEN
349 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement +');
350 END IF;
351
352 l_ignore := dbms_sql.execute(l_select_cursor);
353
354 IF PG_DEBUG in ('Y', 'C') THEN
355 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement -');
356 END IF;
357
358 IF dbms_sql.fetch_rows(l_select_cursor) > 0 then
359
360 /*-----------------------------------------------------------------------+
361 |Fetch the column values, into actual variables |
362 +-----------------------------------------------------------------------*/
363 IF PG_DEBUG in ('Y', 'C') THEN
364 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values +');
365 END IF;
366
367 dbms_sql.column_value(l_select_cursor, 1, l_count);
368 dbms_sql.column_value(l_select_cursor, 2, l_amount);
369 dbms_sql.column_value(l_select_cursor, 3, l_func_amount);
370
371 IF PG_DEBUG in ('Y', 'C') THEN
372 arp_standard.debug('Build_And_Bind: ' || 'l_count '||l_count);
373 arp_standard.debug('Build_And_Bind: ' || 'l_amount'||l_amount);
374 arp_standard.debug('Build_And_Bind: ' || 'l_func_amount'||l_func_amount);
375 END IF;
376
377 IF l_count = 1 THEN
378 p_total := l_amount;
379 ELSE
380 p_total := to_number(NULL);
381 END IF;
382
383 p_cur_count := l_count;
384 p_func_total := l_func_amount;
385
386 IF PG_DEBUG in ('Y', 'C') THEN
387 arp_standard.debug('Build_And_Bind: ' || 'p_total '||p_total);
388 arp_standard.debug('Build_And_Bind: ' || 'p_func_total'||p_func_total);
389 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values -');
390 END IF;
391
392 ELSE
393 IF PG_DEBUG in ('Y', 'C') THEN
394 arp_standard.debug('Build_And_Bind: ' || 'no rows');
395 END IF;
396 END IF;
397
398 /*-----------------------------------------------------------------------+
399 |Finally close the cursor |
400 +-----------------------------------------------------------------------*/
401 IF PG_DEBUG in ('Y', 'C') THEN
402 arp_standard.debug('Build_And_Bind: ' || 'Closing Cursor');
403 END IF;
404 dbms_sql.close_cursor(l_select_cursor);
405
406 -- arp_standard.enable_debug;
407 EXCEPTION
408 WHEN OTHERS THEN
409 IF PG_DEBUG in ('Y', 'C') THEN
410 arp_standard.debug('Build_And_Bind: ' || 'Exception:' );
411 END IF;
412 END;
413
414 -- Bug 2089289
415 procedure fold_currency_code( p_where_clause IN varchar2,
416 p_from_clause IN varchar2 DEFAULT 'ar_payment_schedules_v',
417 p_currency_code OUT NOCOPY varchar2) is
418 l_select_cursor INTEGER;
422 l_tbl_ctr BINARY_INTEGER;
419 l_ignore INTEGER;
420 l_currency_code VARCHAR2(15);
421 l_ctr BINARY_INTEGER;
423 l_literal_tbl literal_tbl_type;
424 l_out_where_clause VARCHAR2(32767);
425 l_actual_bind_var VARCHAR2(2000);
426
427 BEGIN
428
429 l_out_where_clause := '';
430 l_currency_code := '';
431
432 -- arp_standard.enable_debug;
433
434 /*-----------------------------------------------------------------------+
435 |Removed the 'WHERE' from the Parse Statement , It now comes in |
436 |p_where_clause before the actual where clause , This is done to take |
437 |care of the Null Where Clause Case. |
438 +-----------------------------------------------------------------------*/
439 IF PG_DEBUG in ('Y', 'C') THEN
440 arp_standard.debug('Build_And_Bind: ' || 'where clause:' || p_where_clause );
441 arp_standard.debug('Build_And_Bind: ' || 'Opening Cursor');
442 END IF;
443
444 l_select_cursor := dbms_sql.open_cursor;
445
446 /*-----------------------------------------------------------------------+
447 |Call the Build and Bind routine to strip where clause from literals and|
448 |numeric constants and replace them with bind variables. |
449 +-----------------------------------------------------------------------*/
450 IF p_where_clause IS NOT NULL THEN
451 Build_And_Bind(p_where_clause, l_out_where_clause, l_literal_tbl, l_tbl_ctr);
452 END IF;
453
454 /*-----------------------------------------------------------------------+
455 |Parse the built statement along with the where clause. |
456 +-----------------------------------------------------------------------*/
457 IF PG_DEBUG in ('Y', 'C') THEN
458 arp_standard.debug('Build_And_Bind: ' || 'Parsing statement ');
459 arp_standard.debug('Build_And_Bind: ' || l_out_where_clause);
460 END IF;
461 dbms_sql.parse(l_select_cursor,
462 'select invoice_currency_code from '||p_from_clause||' '||l_out_where_clause,
463 dbms_sql.v7);
464
465 /*-----------------------------------------------------------------------+
466 |Define columns for the select statement. |
467 +-----------------------------------------------------------------------*/
468
469 IF PG_DEBUG in ('Y', 'C') THEN
470 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns +');
471 END IF;
472 dbms_sql.define_column(l_select_cursor, 1 ,l_currency_code,15);
473 IF PG_DEBUG in ('Y', 'C') THEN
474 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns -');
475 END IF;
476
477 /*-----------------------------------------------------------------------+
478 |Bind the variables built by Build_And_Bind routine with actual values |
479 +-----------------------------------------------------------------------*/
480 IF ((l_literal_tbl.EXISTS(l_tbl_ctr)) AND (p_where_clause IS NOT NULL)) THEN
481 IF PG_DEBUG in ('Y', 'C') THEN
482 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables +');
483 END IF;
484
485 FOR l_ctr in 1..l_tbl_ctr LOOP
486
487 l_actual_bind_var := '';
488
489 --Bind variables
490 IF PG_DEBUG in ('Y', 'C') THEN
491 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').bind_var_name = ' || l_literal_tbl(l_ctr).bind_var_name);
492 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').stripped_value = ' || l_literal_tbl(l_ctr).stripped_value);
493 END IF;
494
495 l_actual_bind_var := rtrim(ltrim(l_literal_tbl(l_ctr).bind_var_name));
496
497 IF PG_DEBUG in ('Y', 'C') THEN
498 arp_standard.debug('Build_And_Bind: ' || 'l_actual_bind_var = '||l_actual_bind_var);
499 END IF;
500
501 dbms_sql.bind_variable(l_select_cursor, l_actual_bind_var, l_literal_tbl(l_ctr).stripped_value);
502
503 END LOOP;
504
505 IF PG_DEBUG in ('Y', 'C') THEN
506 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables -');
507 END IF;
508
509 END IF;
510
511 /*-----------------------------------------------------------------------+
512 |Execute the SQL statement to calculate functional amount and accounted |
513 |amount totals. |
514 +-----------------------------------------------------------------------*/
515 IF PG_DEBUG in ('Y', 'C') THEN
516 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement +');
517 END IF;
518
519 l_ignore := dbms_sql.execute(l_select_cursor);
520
521 IF PG_DEBUG in ('Y', 'C') THEN
522 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement -');
523 END IF;
524
525 IF dbms_sql.fetch_rows(l_select_cursor) > 0 then
526
527 /*-----------------------------------------------------------------------+
528 |Fetch the column values, into actual variables |
529 +-----------------------------------------------------------------------*/
530 IF PG_DEBUG in ('Y', 'C') THEN
531 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values +');
532 END IF;
533
534 dbms_sql.column_value(l_select_cursor, 1, l_currency_code);
535
536 IF PG_DEBUG in ('Y', 'C') THEN
537 arp_standard.debug('Build_And_Bind: ' || 'l_currency_code '||l_currency_code);
538 END IF;
539
540 p_currency_code := l_currency_code;
541
542 IF PG_DEBUG in ('Y', 'C') THEN
543 arp_standard.debug('Build_And_Bind: ' || 'p_currency_code'||p_currency_code);
544 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values -');
545 END IF;
546
547 ELSE
551 END IF;
548 IF PG_DEBUG in ('Y', 'C') THEN
549 arp_standard.debug('Build_And_Bind: ' || 'no rows');
550 END IF;
552
553 /*-----------------------------------------------------------------------+
554 |Finally close the cursor |
555 +-----------------------------------------------------------------------*/
556 IF PG_DEBUG in ('Y', 'C') THEN
557 arp_standard.debug('Build_And_Bind: ' || 'Closing Cursor');
558 END IF;
559 dbms_sql.close_cursor(l_select_cursor);
560
561 -- arp_standard.enable_debug;
562 EXCEPTION
563 WHEN OTHERS THEN
564 IF PG_DEBUG in ('Y', 'C') THEN
565 arp_standard.debug('Build_And_Bind: ' || 'Exception:' );
566 END IF;
567 END;
568
569 -- End bug 2089289
570
571
572 procedure get_date( p_ps_id IN ar_dispute_history.payment_schedule_id%TYPE,
573 p_last_dispute_date IN OUT NOCOPY ar_dispute_history.start_date%TYPE ) is
574 begin
575 select start_date into
576 p_last_dispute_date
577 from ar_dispute_history
578 where payment_schedule_id = p_ps_id and
579 end_date is null;
580 -- arp_standard.enable_debug;
581 EXCEPTION
582 WHEN OTHERS THEN
583 IF PG_DEBUG in ('Y', 'C') THEN
584 arp_standard.debug('Build_And_Bind: ' || 'Exception:'|| to_char(p_last_dispute_date));
585 END IF;
586 end;
587
588
589
590 procedure check_changed( p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
591 p_amount_in_dispute IN ar_payment_schedules.payment_schedule_id%TYPE, p_dispute_amount_changed IN OUT NOCOPY NUMBER ) IS
592 begin
593 select count(*)
594 into p_dispute_amount_changed
595 from ar_payment_schedules
596 where payment_schedule_id = p_ps_id
597 and (( amount_in_dispute <> p_amount_in_dispute) OR
598 (amount_in_dispute is NOT NULL and
599 p_amount_in_dispute IS NULL ) OR
600 ( p_amount_in_dispute IS NOT NULL and
601 amount_in_dispute IS NULL ) );
602 -- arp_standard.enable_debug;
603 EXCEPTION
604 WHEN OTHERS THEN
605 IF PG_DEBUG in ('Y', 'C') THEN
606 arp_standard.debug('Build_And_Bind: ' || 'Exception:'|| to_char(p_dispute_amount_changed));
607 END IF;
608 end;
609
610
611
612 procedure get_flag( p_ps_id IN ar_dispute_history.payment_schedule_id%TYPE,
613 p_ever_in_dispute_flag IN OUT NOCOPY varchar2) IS
614 begin
615 select decode(min(start_date),
616 NULL , 'N',
617 'Y' )
618 into p_ever_in_dispute_flag
619 from ar_dispute_history
620 WHERE payment_schedule_id = p_ps_id;
621 -- arp_standard.enable_debug;
622 EXCEPTION
623 WHEN OTHERS THEN
624 IF PG_DEBUG in ('Y', 'C') THEN
625 arp_standard.debug('Build_And_Bind: ' || 'Exception in ever_in_dispute_flag' );
626 END IF;
627 end;
628
629 procedure get_days_late( p_due_date IN ar_payment_schedules.due_date%TYPE,
630 p_days_late IN OUT NOCOPY number) IS
631 begin
632 select trunc(sysdate) - p_due_date
633 into p_days_late
634 from dual;
635 -- arp_standard.enable_debug;
636 EXCEPTION
637 WHEN OTHERS THEN
638 IF PG_DEBUG in ('Y', 'C') THEN
639 arp_standard.debug('Build_And_Bind: ' || 'Exception in ever_in_dispute_flag' );
640 END IF;
641 end;
642
643 /* ===============================================================================
644 | PROCEDURE Build_And_Bind
645 |
646 | DESCRIPTION
647 | Strips a where clause storing the literal values and numeric constants,
648 | replacing them with bind variables. The actual values to be bound later
649 | are stored in a PLSQL table along with the actual bind variable so that
650 | they can be bound later.
651 |
652 | SCOPE - PRIVATE
653 |
654 | PARAMETERS
655 | p_in_where_clause IN Input where clause to be stripped
656 | p_out_where_clause OUT NOCOPY Output where clause containing bind variables
657 | p_literal_tbl OUT NOCOPY Table containing bind variable name and values
658 | p_tbl_ctr OUT NOCOPY Count of bind variables
659 |
660 | Modification History
661 | 16th May 99 Vikram Ahluwalia Created
662 *==============================================================================*/
663 PROCEDURE Build_And_Bind ( p_in_where_clause IN VARCHAR2 ,
664 p_out_where_clause OUT NOCOPY VARCHAR2 ,
665 p_literal_tbl OUT NOCOPY literal_tbl_type ,
666 p_tbl_ctr OUT NOCOPY BINARY_INTEGER ) IS
667
668 l_in_where_clause VARCHAR2(32767) ;
669
670 l_length BINARY_INTEGER ;
671
672 l_ctr BINARY_INTEGER ;
673
674 l_bind_ctr BINARY_INTEGER := 0 ;
675
676 l_bind_var VARCHAR2(1000) ;
677
678 -- bug2710965 Increased size to (3) for multi-byte characater
679 l_temp_cell VARCHAR2(3) ;
680
681 -- bug2710965 Increased size to (3) for multi-byte characater
682 l_prev_cell VARCHAR2(3) ;
683
684 l_actual_where_clause VARCHAR2(32767) ;
685
686 l_balance_clause VARCHAR2(32767) ;
687
688 char_literal_on BOOLEAN := FALSE ;
689
690 num_literal_on BOOLEAN := FALSE ;
691
692 not_bound_flag BOOLEAN := FALSE ;
693
694 l_build_where BOOLEAN := FALSE ;
695
699
696 l_tbl_ctr BINARY_INTEGER := 0 ;
697
698 l_literal_tbl literal_tbl_type ;
700 l_amount NUMBER ;
701
702 l_func_amount NUMBER ;
703
704 l_count NUMBER ;
705
706 l_by_clause_pos BINARY_INTEGER;
707
708 l_actual_length BINARY_INTEGER;
709
710 BEGIN
711
712 IF PG_DEBUG in ('Y', 'C') THEN
713 arp_standard.debug('Build_And_Bind: ' || 'l_in_where_clause ' || l_in_where_clause);
714 END IF;
715
716 l_in_where_clause := p_in_where_clause;
717
718 /*---------------------------------------------------------------+
719 |Get the length in characters of the where clause as Step 1 |
720 +---------------------------------------------------------------*/
721 select length(l_in_where_clause)
722 into l_actual_length
723 from dual;
724
725 l_by_clause_pos := 0;
726
727 /*--------------------------------------------------------------------+
728 |Strip the 'order by' clause if it is present as part of where clause|
729 +--------------------------------------------------------------------*/
730 select instr(l_in_where_clause, 'order by')
731 into l_by_clause_pos
732 from dual;
733
734 IF (l_by_clause_pos > 0) THEN
735 l_length := l_by_clause_pos -1;
736 ELSE
737
738 /*--------------------------------------------------------------------+
739 |Strip the 'group by clause' if it is present as part of where clause|
740 +--------------------------------------------------------------------*/
741 SELECT INSTR(l_in_where_clause, 'group by')
742 INTO l_by_clause_pos
743 FROM DUAL;
744
745 IF (l_by_clause_pos > 0) THEN
746 l_length := l_by_clause_pos - 1;
747 ELSE
748 l_length := l_actual_length;
749 END IF;
750
751 END IF; --end if l_by_clause_pos > 0
752
753 l_temp_cell := ' ';
754
755 /*----------------------------------------------------------------+
756 |Loop through the where clause storing it into a table as Step 2 |
757 +----------------------------------------------------------------*/
758 FOR l_ctr IN 1..(l_length+1) LOOP
759
760 l_prev_cell := l_temp_cell;
761
762 IF (l_ctr = (l_length + 1)) THEN
763 l_temp_cell := ' ';
764 ELSE
765 select substr(l_in_where_clause, l_ctr, 1)
766 into l_temp_cell
767 from dual;
768 END IF;
769
770 /*----------------------------------------------------------------+
771 |Check for character literals - they use the de-limiter quote |
772 +----------------------------------------------------------------*/
773 IF ((l_temp_cell = '''') AND (NOT num_literal_on)) THEN
774 IF (char_literal_on) THEN
775 char_literal_on := FALSE; --end point
776 ELSE
777 char_literal_on := TRUE; --start point
778 not_bound_flag := TRUE;
779 l_build_where := FALSE;
780 END IF;
781 END IF;
782
783 /*------------------------------------------------------------------------------------+
784 |Check for numeric literals, the check for alphabets A to Z is for database columns |
785 |having names such as col14 so it would not matter if the NLS lang was not english |
786 |as database columns are represented using alphabets A to Z. |
787 +------------------------------------------------------------------------------------*/
788 IF ((l_temp_cell IN ('1','2','3','4','5','6','7','8','9','0'))
789 AND (UPPER(l_prev_cell) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','1','2','3','4','5','6','7','8','9','0')) AND (NOT num_literal_on) AND (NOT char_literal_on)) THEN
790
791 num_literal_on := TRUE; --start point
792 not_bound_flag := TRUE;
793 l_build_where := FALSE;
794 END IF;
795
796 /*-------------------------------------------------------------------------+
797 |A numeric or character literal requires to be replaced by a bind variable|
798 |the value requires to be stored so that it can be bound later. |
799 +-------------------------------------------------------------------------*/
803 l_tbl_ctr := l_tbl_ctr + 1;
800 IF (((char_literal_on) OR (num_literal_on)) AND (not_bound_flag)) THEN
801 l_bind_var := '';
802 l_bind_ctr := l_bind_ctr + 1;
804 l_bind_var := ' :l_var'||l_bind_ctr||' ';
805
806 l_literal_tbl(l_tbl_ctr).stripped_value := '';
807
808 /*---------------------------------------------------------------------------+
809 |Its possible for a numeric value to be prefixed by a +, - or . so take care|
810 |of that situation. |
811 +---------------------------------------------------------------------------*/
812 IF (num_literal_on) AND l_prev_cell IN (',','.','+','-')THEN
813
814 IF PG_DEBUG in ('Y', 'C') THEN
815 arp_standard.debug('Build_And_Bind: ' || 'l_prev_cell = ' || l_prev_cell);
816 END IF; --set values for numeric token
817 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || l_prev_cell;
818 ELSE
819 l_actual_where_clause := l_actual_where_clause || l_prev_cell; --Build the previous cell
820 END IF;
821
822 /*--------------------------------------------------------------------------------+
823 |Concatenate the actual bind variable to the where clause to enable binding later|
824 +---------------------------------------------------------------------------------*/
825 IF (num_literal_on) THEN
826 l_actual_where_clause := l_actual_where_clause ||'TO_NUMBER('||l_bind_var||')';
827 ELSE
828 l_actual_where_clause := l_actual_where_clause ||l_bind_var;
829 END IF;
830
831 l_literal_tbl(l_tbl_ctr).literal_counter := l_tbl_ctr;
832 l_literal_tbl(l_tbl_ctr).bind_var_name := l_bind_var ;
833 not_bound_flag := FALSE;
834
835 /*---------------------------------------------------------------------------+
836 |Build the actual where clause, this is also built when the literal value is|
837 |replaced with a bind variable. |
838 +---------------------------------------------------------------------------*/
839 ELSIF (l_build_where) THEN
840
841 l_actual_where_clause := l_actual_where_clause || l_prev_cell; --Build the previous cell
842
843 l_build_where := FALSE;
844
845 END IF;
846
847 /*------------------------------------------------------------------------------+
848 | Save the actual values to be bound to variables later |
849 +------------------------------------------------------------------------------*/
850 IF (((char_literal_on) AND (l_temp_cell <> '''')) OR (num_literal_on)) THEN
851
852 IF ((num_literal_on) AND (l_temp_cell IN (' ',';','(',')','=','!','<','>','*','^'))) THEN
853 num_literal_on := FALSE; --end point
854 l_build_where := TRUE ; --set the flag so that the actual where clause can be built
855
856 ELSE
857 IF PG_DEBUG in ('Y', 'C') THEN
858 arp_standard.debug('Build_And_Bind: ' || 'l_temp_cell = ' || l_temp_cell);
859 END IF; --set values
860 -- 3804333, determine if there was a ' replaced with ^, now set it back to '
861 if emb_quote and l_temp_cell = '^' then
862 emb_quote := FALSE;
863 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || '''';
864 else
865 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || l_temp_cell;
866 end if;
867
868 END IF;
869
870 ELSIF (l_temp_cell <> '''') THEN
871 l_build_where := TRUE; --set the flag so that the actual where clause can be built
872
873 END IF; --end if character or numeric literal on
874
875 END LOOP; --end loop length of character string
876
877 /*--------------------------------------------------------------------+
878 |Build the final where clause concatenating the order by or group by |
879 +--------------------------------------------------------------------*/
880 IF (l_by_clause_pos > 0) THEN
881
882 SELECT SUBSTR(l_in_where_clause, l_by_clause_pos)
883 INTO l_balance_clause
884 FROM dual;
885
886 l_actual_where_clause := l_actual_where_clause || l_balance_clause;
887
888 END IF; --end if by clause pos greater than 0
889
890 IF PG_DEBUG in ('Y', 'C') THEN
891 arp_standard.debug('Build_And_Bind: ' || 'l_actual_where_clause ' || l_actual_where_clause);
892 END IF;
893
894 /*---------------------------------------------------------------------------+
895 |In debug mode dump the contents of the table, which helps bind variables |
896 +---------------------------------------------------------------------------*/
897 FOR l_ctr in 1..l_tbl_ctr LOOP
898
899 IF PG_DEBUG in ('Y', 'C') THEN
900 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').literal_counter = '|| l_literal_tbl(l_ctr).literal_counter);
901 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').bind_var_name = '|| l_literal_tbl(l_ctr).bind_var_name);
902 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').stripped_value = '|| l_literal_tbl(l_ctr).stripped_value);
903 END IF;
904
905 END LOOP; --end loop dump debug statements
906
907 p_out_where_clause := l_actual_where_clause;
908
909 p_literal_tbl := l_literal_tbl;
910
911 p_tbl_ctr := l_tbl_ctr;
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 IF PG_DEBUG in ('Y', 'C') THEN
916 arp_standard.debug( 'ARP_ARXCOQIT.Build_And_Bind Exception: OTHERS EXCEPTION');
917 END IF;
918 RAISE;
919
920 END Build_And_Bind;
921
922 END ARP_ARXCOQIT;
[Home] [Help]