1 PACKAGE BODY ARP_ARXCOQIT AS
2 /* $Header: ARCEQITB.pls 120.8 2006/04/07 17:52:33 kmaheswa 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 ,
24 p_literal_tbl OUT NOCOPY literal_tbl_type ,
25 p_tbl_ctr OUT NOCOPY BINARY_INTEGER );
26
27 /*1806931
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) );
157 arp_standard.debug('Build_And_Bind: ' || 'l_amount =' || to_char(l_amount) );
158 END IF;
159 dbms_sql.close_cursor(l_select_cursor);
160 else
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 THEN
272 dbms_sql.parse(l_select_cursor,
273 'select /*+ INDEX(AR_PAYMENT_SCHEDULES_TRX2_V.ps AR_PAYMENT_SCHEDULES_N6) */
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 ELSE
279 dbms_sql.parse(l_select_cursor,
280 'select count( distinct invoice_currency_code ),
281 sum(amount_due_remaining),
282 sum(acctd_amount_due_remaining)
283 from '||p_from_clause||' '||l_out_where_clause,
284 dbms_sql.v7);
285 END IF;
286
287 /*-----------------------------------------------------------------------+
288 |Define columns for the select statement. |
289 +-----------------------------------------------------------------------*/
290 IF PG_DEBUG in ('Y', 'C') THEN
291 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns +');
292 END IF;
293
294 dbms_sql.define_column(l_select_cursor, 1 , l_count);
295 dbms_sql.define_column(l_select_cursor, 2 , l_amount);
296 dbms_sql.define_column(l_select_cursor, 3 , l_func_amount);
297
298 IF PG_DEBUG in ('Y', 'C') THEN
299 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns -');
300 END IF;
301
302 /*-----------------------------------------------------------------------+
303 |Bind the variables built by Build_And_Bind routine with actual values |
304 +-----------------------------------------------------------------------*/
305 IF ((l_literal_tbl.EXISTS(l_tbl_ctr)) AND (p_where_clause IS NOT NULL)) THEN
306
307 IF PG_DEBUG in ('Y', 'C') THEN
308 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables +');
309 END IF;
310
311 FOR l_ctr in 1..l_tbl_ctr LOOP
312
313 l_actual_bind_var := '';
314
315 --Bind variables
316 IF PG_DEBUG in ('Y', 'C') THEN
317 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').bind_var_name = ' || l_literal_tbl(l_ctr).bind_var_name);
318 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').stripped_value = ' || l_literal_tbl(l_ctr).stripped_value);
319 END IF;
320
321 l_actual_bind_var := rtrim(ltrim(l_literal_tbl(l_ctr).bind_var_name));
322
323 IF PG_DEBUG in ('Y', 'C') THEN
324 arp_standard.debug('Build_And_Bind: ' || 'l_actual_bind_var = '||l_actual_bind_var);
325 END IF;
326
327 dbms_sql.bind_variable(l_select_cursor, l_actual_bind_var, l_literal_tbl(l_ctr).stripped_value);
328
329 END LOOP;
330
331 IF PG_DEBUG in ('Y', 'C') THEN
332 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables -');
333 END IF;
334
335 END IF;
336
337 /*-----------------------------------------------------------------------+
338 |Execute the SQL statement to calculate functional amount and accounted |
339 |amount totals. |
340 +-----------------------------------------------------------------------*/
341 IF PG_DEBUG in ('Y', 'C') THEN
342 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement +');
343 END IF;
344
345 l_ignore := dbms_sql.execute(l_select_cursor);
346
347 IF PG_DEBUG in ('Y', 'C') THEN
348 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement -');
349 END IF;
350
351 IF dbms_sql.fetch_rows(l_select_cursor) > 0 then
352
353 /*-----------------------------------------------------------------------+
354 |Fetch the column values, into actual variables |
355 +-----------------------------------------------------------------------*/
356 IF PG_DEBUG in ('Y', 'C') THEN
357 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values +');
358 END IF;
359
360 dbms_sql.column_value(l_select_cursor, 1, l_count);
361 dbms_sql.column_value(l_select_cursor, 2, l_amount);
362 dbms_sql.column_value(l_select_cursor, 3, l_func_amount);
363
364 IF PG_DEBUG in ('Y', 'C') THEN
365 arp_standard.debug('Build_And_Bind: ' || 'l_count '||l_count);
366 arp_standard.debug('Build_And_Bind: ' || 'l_amount'||l_amount);
367 arp_standard.debug('Build_And_Bind: ' || 'l_func_amount'||l_func_amount);
368 END IF;
369
370 IF l_count = 1 THEN
371 p_total := l_amount;
372 ELSE
373 p_total := to_number(NULL);
374 END IF;
375
376 p_cur_count := l_count;
377 p_func_total := l_func_amount;
378
379 IF PG_DEBUG in ('Y', 'C') THEN
380 arp_standard.debug('Build_And_Bind: ' || 'p_total '||p_total);
384
381 arp_standard.debug('Build_And_Bind: ' || 'p_func_total'||p_func_total);
382 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values -');
383 END IF;
385 ELSE
386 IF PG_DEBUG in ('Y', 'C') THEN
387 arp_standard.debug('Build_And_Bind: ' || 'no rows');
388 END IF;
389 END IF;
390
391 /*-----------------------------------------------------------------------+
392 |Finally close the cursor |
393 +-----------------------------------------------------------------------*/
394 IF PG_DEBUG in ('Y', 'C') THEN
395 arp_standard.debug('Build_And_Bind: ' || 'Closing Cursor');
396 END IF;
397 dbms_sql.close_cursor(l_select_cursor);
398
399 -- arp_standard.enable_debug;
400 EXCEPTION
401 WHEN OTHERS THEN
402 IF PG_DEBUG in ('Y', 'C') THEN
403 arp_standard.debug('Build_And_Bind: ' || 'Exception:' );
404 END IF;
405 END;
406
407 -- Bug 2089289
408 procedure fold_currency_code( p_where_clause IN varchar2,
409 p_from_clause IN varchar2 DEFAULT 'ar_payment_schedules_v',
410 p_currency_code OUT NOCOPY varchar2) is
411 l_select_cursor INTEGER;
412 l_ignore INTEGER;
413 l_currency_code VARCHAR2(15);
414 l_ctr BINARY_INTEGER;
415 l_tbl_ctr BINARY_INTEGER;
416 l_literal_tbl literal_tbl_type;
417 l_out_where_clause VARCHAR2(32767);
418 l_actual_bind_var VARCHAR2(2000);
419
420 BEGIN
421
422 l_out_where_clause := '';
423 l_currency_code := '';
424
425 -- arp_standard.enable_debug;
426
427 /*-----------------------------------------------------------------------+
428 |Removed the 'WHERE' from the Parse Statement , It now comes in |
429 |p_where_clause before the actual where clause , This is done to take |
430 |care of the Null Where Clause Case. |
431 +-----------------------------------------------------------------------*/
432 IF PG_DEBUG in ('Y', 'C') THEN
433 arp_standard.debug('Build_And_Bind: ' || 'where clause:' || p_where_clause );
434 arp_standard.debug('Build_And_Bind: ' || 'Opening Cursor');
435 END IF;
436
437 l_select_cursor := dbms_sql.open_cursor;
438
439 /*-----------------------------------------------------------------------+
440 |Call the Build and Bind routine to strip where clause from literals and|
441 |numeric constants and replace them with bind variables. |
442 +-----------------------------------------------------------------------*/
443 IF p_where_clause IS NOT NULL THEN
444 Build_And_Bind(p_where_clause, l_out_where_clause, l_literal_tbl, l_tbl_ctr);
445 END IF;
446
447 /*-----------------------------------------------------------------------+
448 |Parse the built statement along with the where clause. |
449 +-----------------------------------------------------------------------*/
450 IF PG_DEBUG in ('Y', 'C') THEN
451 arp_standard.debug('Build_And_Bind: ' || 'Parsing statement ');
452 arp_standard.debug('Build_And_Bind: ' || l_out_where_clause);
453 END IF;
454 dbms_sql.parse(l_select_cursor,
455 'select invoice_currency_code from '||p_from_clause||' '||l_out_where_clause,
456 dbms_sql.v7);
457
458 /*-----------------------------------------------------------------------+
459 |Define columns for the select statement. |
460 +-----------------------------------------------------------------------*/
461
462 IF PG_DEBUG in ('Y', 'C') THEN
463 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns +');
464 END IF;
465 dbms_sql.define_column(l_select_cursor, 1 ,l_currency_code,15);
466 IF PG_DEBUG in ('Y', 'C') THEN
467 arp_standard.debug('Build_And_Bind: ' || 'Defining Columns -');
468 END IF;
469
470 /*-----------------------------------------------------------------------+
471 |Bind the variables built by Build_And_Bind routine with actual values |
472 +-----------------------------------------------------------------------*/
473 IF ((l_literal_tbl.EXISTS(l_tbl_ctr)) AND (p_where_clause IS NOT NULL)) THEN
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables +');
476 END IF;
477
478 FOR l_ctr in 1..l_tbl_ctr LOOP
479
480 l_actual_bind_var := '';
481
482 --Bind variables
483 IF PG_DEBUG in ('Y', 'C') THEN
484 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').bind_var_name = ' || l_literal_tbl(l_ctr).bind_var_name);
485 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').stripped_value = ' || l_literal_tbl(l_ctr).stripped_value);
486 END IF;
487
488 l_actual_bind_var := rtrim(ltrim(l_literal_tbl(l_ctr).bind_var_name));
489
490 IF PG_DEBUG in ('Y', 'C') THEN
491 arp_standard.debug('Build_And_Bind: ' || 'l_actual_bind_var = '||l_actual_bind_var);
492 END IF;
493
494 dbms_sql.bind_variable(l_select_cursor, l_actual_bind_var, l_literal_tbl(l_ctr).stripped_value);
495
496 END LOOP;
497
498 IF PG_DEBUG in ('Y', 'C') THEN
499 arp_standard.debug('Build_And_Bind: ' || 'Binding Variables -');
500 END IF;
501
502 END IF;
503
507 +-----------------------------------------------------------------------*/
504 /*-----------------------------------------------------------------------+
505 |Execute the SQL statement to calculate functional amount and accounted |
506 |amount totals. |
508 IF PG_DEBUG in ('Y', 'C') THEN
509 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement +');
510 END IF;
511
512 l_ignore := dbms_sql.execute(l_select_cursor);
513
514 IF PG_DEBUG in ('Y', 'C') THEN
515 arp_standard.debug('Build_And_Bind: ' || 'Executing Statement -');
516 END IF;
517
518 IF dbms_sql.fetch_rows(l_select_cursor) > 0 then
519
520 /*-----------------------------------------------------------------------+
521 |Fetch the column values, into actual variables |
522 +-----------------------------------------------------------------------*/
523 IF PG_DEBUG in ('Y', 'C') THEN
524 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values +');
525 END IF;
526
527 dbms_sql.column_value(l_select_cursor, 1, l_currency_code);
528
529 IF PG_DEBUG in ('Y', 'C') THEN
530 arp_standard.debug('Build_And_Bind: ' || 'l_currency_code '||l_currency_code);
531 END IF;
532
533 p_currency_code := l_currency_code;
534
535 IF PG_DEBUG in ('Y', 'C') THEN
536 arp_standard.debug('Build_And_Bind: ' || 'p_currency_code'||p_currency_code);
537 arp_standard.debug('Build_And_Bind: ' || 'Fetching column values -');
538 END IF;
539
540 ELSE
541 IF PG_DEBUG in ('Y', 'C') THEN
542 arp_standard.debug('Build_And_Bind: ' || 'no rows');
543 END IF;
544 END IF;
545
546 /*-----------------------------------------------------------------------+
547 |Finally close the cursor |
548 +-----------------------------------------------------------------------*/
549 IF PG_DEBUG in ('Y', 'C') THEN
550 arp_standard.debug('Build_And_Bind: ' || 'Closing Cursor');
551 END IF;
552 dbms_sql.close_cursor(l_select_cursor);
553
554 -- arp_standard.enable_debug;
555 EXCEPTION
556 WHEN OTHERS THEN
557 IF PG_DEBUG in ('Y', 'C') THEN
558 arp_standard.debug('Build_And_Bind: ' || 'Exception:' );
559 END IF;
560 END;
561
562 -- End bug 2089289
563
564
565 procedure get_date( p_ps_id IN ar_dispute_history.payment_schedule_id%TYPE,
566 p_last_dispute_date IN OUT NOCOPY ar_dispute_history.start_date%TYPE ) is
567 begin
568 select start_date into
569 p_last_dispute_date
570 from ar_dispute_history
571 where payment_schedule_id = p_ps_id and
572 end_date is null;
573 -- arp_standard.enable_debug;
574 EXCEPTION
575 WHEN OTHERS THEN
576 IF PG_DEBUG in ('Y', 'C') THEN
577 arp_standard.debug('Build_And_Bind: ' || 'Exception:'|| to_char(p_last_dispute_date));
578 END IF;
579 end;
580
581
582
583 procedure check_changed( p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
584 p_amount_in_dispute IN ar_payment_schedules.payment_schedule_id%TYPE, p_dispute_amount_changed IN OUT NOCOPY NUMBER ) IS
585 begin
586 select count(*)
587 into p_dispute_amount_changed
588 from ar_payment_schedules
589 where payment_schedule_id = p_ps_id
590 and (( amount_in_dispute <> p_amount_in_dispute) OR
591 (amount_in_dispute is NOT NULL and
592 p_amount_in_dispute IS NULL ) OR
593 ( p_amount_in_dispute IS NOT NULL and
594 amount_in_dispute IS NULL ) );
595 -- arp_standard.enable_debug;
596 EXCEPTION
597 WHEN OTHERS THEN
598 IF PG_DEBUG in ('Y', 'C') THEN
599 arp_standard.debug('Build_And_Bind: ' || 'Exception:'|| to_char(p_dispute_amount_changed));
600 END IF;
601 end;
602
603
604
605 procedure get_flag( p_ps_id IN ar_dispute_history.payment_schedule_id%TYPE,
606 p_ever_in_dispute_flag IN OUT NOCOPY varchar2) IS
607 begin
608 select decode(min(start_date),
609 NULL , 'N',
610 'Y' )
611 into p_ever_in_dispute_flag
612 from ar_dispute_history
613 WHERE payment_schedule_id = p_ps_id;
614 -- arp_standard.enable_debug;
615 EXCEPTION
616 WHEN OTHERS THEN
617 IF PG_DEBUG in ('Y', 'C') THEN
618 arp_standard.debug('Build_And_Bind: ' || 'Exception in ever_in_dispute_flag' );
619 END IF;
620 end;
621
622 procedure get_days_late( p_due_date IN ar_payment_schedules.due_date%TYPE,
623 p_days_late IN OUT NOCOPY number) IS
624 begin
625 select trunc(sysdate) - p_due_date
626 into p_days_late
627 from dual;
628 -- arp_standard.enable_debug;
629 EXCEPTION
630 WHEN OTHERS THEN
631 IF PG_DEBUG in ('Y', 'C') THEN
632 arp_standard.debug('Build_And_Bind: ' || 'Exception in ever_in_dispute_flag' );
633 END IF;
634 end;
635
636 /* ===============================================================================
637 | PROCEDURE Build_And_Bind
638 |
639 | DESCRIPTION
640 | Strips a where clause storing the literal values and numeric constants,
644 |
641 | replacing them with bind variables. The actual values to be bound later
642 | are stored in a PLSQL table along with the actual bind variable so that
643 | they can be bound later.
645 | SCOPE - PRIVATE
646 |
647 | PARAMETERS
648 | p_in_where_clause IN Input where clause to be stripped
649 | p_out_where_clause OUT NOCOPY Output where clause containing bind variables
650 | p_literal_tbl OUT NOCOPY Table containing bind variable name and values
651 | p_tbl_ctr OUT NOCOPY Count of bind variables
652 |
653 | Modification History
654 | 16th May 99 Vikram Ahluwalia Created
655 *==============================================================================*/
656 PROCEDURE Build_And_Bind ( p_in_where_clause IN VARCHAR2 ,
657 p_out_where_clause OUT NOCOPY VARCHAR2 ,
658 p_literal_tbl OUT NOCOPY literal_tbl_type ,
659 p_tbl_ctr OUT NOCOPY BINARY_INTEGER ) IS
660
661 l_in_where_clause VARCHAR2(32767) ;
662
663 l_length BINARY_INTEGER ;
664
665 l_ctr BINARY_INTEGER ;
666
667 l_bind_ctr BINARY_INTEGER := 0 ;
668
669 l_bind_var VARCHAR2(1000) ;
670
671 -- bug2710965 Increased size to (3) for multi-byte characater
672 l_temp_cell VARCHAR2(3) ;
673
674 -- bug2710965 Increased size to (3) for multi-byte characater
675 l_prev_cell VARCHAR2(3) ;
676
677 l_actual_where_clause VARCHAR2(32767) ;
678
679 l_balance_clause VARCHAR2(32767) ;
680
681 char_literal_on BOOLEAN := FALSE ;
682
683 num_literal_on BOOLEAN := FALSE ;
684
685 not_bound_flag BOOLEAN := FALSE ;
686
687 l_build_where BOOLEAN := FALSE ;
688
689 l_tbl_ctr BINARY_INTEGER := 0 ;
690
691 l_literal_tbl literal_tbl_type ;
692
693 l_amount NUMBER ;
694
695 l_func_amount NUMBER ;
696
697 l_count NUMBER ;
698
699 l_by_clause_pos BINARY_INTEGER;
700
701 l_actual_length BINARY_INTEGER;
702
703 BEGIN
704
705 IF PG_DEBUG in ('Y', 'C') THEN
706 arp_standard.debug('Build_And_Bind: ' || 'l_in_where_clause ' || l_in_where_clause);
707 END IF;
708
709 l_in_where_clause := p_in_where_clause;
710
711 /*---------------------------------------------------------------+
712 |Get the length in characters of the where clause as Step 1 |
713 +---------------------------------------------------------------*/
714 select length(l_in_where_clause)
715 into l_actual_length
716 from dual;
717
718 l_by_clause_pos := 0;
719
720 /*--------------------------------------------------------------------+
721 |Strip the 'order by' clause if it is present as part of where clause|
722 +--------------------------------------------------------------------*/
723 select instr(l_in_where_clause, 'order by')
724 into l_by_clause_pos
725 from dual;
726
727 IF (l_by_clause_pos > 0) THEN
728 l_length := l_by_clause_pos -1;
729 ELSE
730
731 /*--------------------------------------------------------------------+
732 |Strip the 'group by clause' if it is present as part of where clause|
733 +--------------------------------------------------------------------*/
734 SELECT INSTR(l_in_where_clause, 'group by')
735 INTO l_by_clause_pos
736 FROM DUAL;
737
738 IF (l_by_clause_pos > 0) THEN
739 l_length := l_by_clause_pos - 1;
740 ELSE
741 l_length := l_actual_length;
742 END IF;
743
744 END IF; --end if l_by_clause_pos > 0
745
746 l_temp_cell := ' ';
747
748 /*----------------------------------------------------------------+
749 |Loop through the where clause storing it into a table as Step 2 |
750 +----------------------------------------------------------------*/
751 FOR l_ctr IN 1..(l_length+1) LOOP
752
753 l_prev_cell := l_temp_cell;
754
755 IF (l_ctr = (l_length + 1)) THEN
756 l_temp_cell := ' ';
757 ELSE
758 select substr(l_in_where_clause, l_ctr, 1)
759 into l_temp_cell
760 from dual;
761 END IF;
762
763 /*----------------------------------------------------------------+
764 |Check for character literals - they use the de-limiter quote |
765 +----------------------------------------------------------------*/
766 IF ((l_temp_cell = '''') AND (NOT num_literal_on)) THEN
767 IF (char_literal_on) THEN
768 char_literal_on := FALSE; --end point
769 ELSE
770 char_literal_on := TRUE; --start point
771 not_bound_flag := TRUE;
772 l_build_where := FALSE;
773 END IF;
774 END IF;
775
776 /*------------------------------------------------------------------------------------+
777 |Check for numeric literals, the check for alphabets A to Z is for database columns |
781 IF ((l_temp_cell IN ('1','2','3','4','5','6','7','8','9','0'))
778 |having names such as col14 so it would not matter if the NLS lang was not english |
779 |as database columns are represented using alphabets A to Z. |
780 +------------------------------------------------------------------------------------*/
782 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
783
784 num_literal_on := TRUE; --start point
785 not_bound_flag := TRUE;
786 l_build_where := FALSE;
787 END IF;
788
789 /*-------------------------------------------------------------------------+
790 |A numeric or character literal requires to be replaced by a bind variable|
791 |the value requires to be stored so that it can be bound later. |
792 +-------------------------------------------------------------------------*/
793 IF (((char_literal_on) OR (num_literal_on)) AND (not_bound_flag)) THEN
794 l_bind_var := '';
795 l_bind_ctr := l_bind_ctr + 1;
796 l_tbl_ctr := l_tbl_ctr + 1;
797 l_bind_var := ' :l_var'||l_bind_ctr||' ';
798
799 l_literal_tbl(l_tbl_ctr).stripped_value := '';
800
801 /*---------------------------------------------------------------------------+
802 |Its possible for a numeric value to be prefixed by a +, - or . so take care|
803 |of that situation. |
804 +---------------------------------------------------------------------------*/
805 IF (num_literal_on) AND l_prev_cell IN (',','.','+','-')THEN
806
807 IF PG_DEBUG in ('Y', 'C') THEN
808 arp_standard.debug('Build_And_Bind: ' || 'l_prev_cell = ' || l_prev_cell);
809 END IF; --set values for numeric token
810 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || l_prev_cell;
811 ELSE
812 l_actual_where_clause := l_actual_where_clause || l_prev_cell; --Build the previous cell
813 END IF;
814
815 /*--------------------------------------------------------------------------------+
816 |Concatenate the actual bind variable to the where clause to enable binding later|
817 +---------------------------------------------------------------------------------*/
818 IF (num_literal_on) THEN
819 l_actual_where_clause := l_actual_where_clause ||'TO_NUMBER('||l_bind_var||')';
820 ELSE
821 l_actual_where_clause := l_actual_where_clause ||l_bind_var;
822 END IF;
823
824 l_literal_tbl(l_tbl_ctr).literal_counter := l_tbl_ctr;
825 l_literal_tbl(l_tbl_ctr).bind_var_name := l_bind_var ;
826 not_bound_flag := FALSE;
827
828 /*---------------------------------------------------------------------------+
829 |Build the actual where clause, this is also built when the literal value is|
830 |replaced with a bind variable. |
831 +---------------------------------------------------------------------------*/
832 ELSIF (l_build_where) THEN
833
837
834 l_actual_where_clause := l_actual_where_clause || l_prev_cell; --Build the previous cell
835
836 l_build_where := FALSE;
838 END IF;
839
840 /*------------------------------------------------------------------------------+
841 | Save the actual values to be bound to variables later |
842 +------------------------------------------------------------------------------*/
843 IF (((char_literal_on) AND (l_temp_cell <> '''')) OR (num_literal_on)) THEN
844
845 IF ((num_literal_on) AND (l_temp_cell IN (' ',';','(',')','=','!','<','>','*','^'))) THEN
846 num_literal_on := FALSE; --end point
847 l_build_where := TRUE ; --set the flag so that the actual where clause can be built
848
849 ELSE
850 IF PG_DEBUG in ('Y', 'C') THEN
851 arp_standard.debug('Build_And_Bind: ' || 'l_temp_cell = ' || l_temp_cell);
852 END IF; --set values
853 -- 3804333, determine if there was a ' replaced with ^, now set it back to '
854 if emb_quote and l_temp_cell = '^' then
855 emb_quote := FALSE;
856 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || '''';
857 else
858 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || l_temp_cell;
859 end if;
860
861 END IF;
862
863 ELSIF (l_temp_cell <> '''') THEN
864 l_build_where := TRUE; --set the flag so that the actual where clause can be built
865
866 END IF; --end if character or numeric literal on
867
868 END LOOP; --end loop length of character string
869
870 /*--------------------------------------------------------------------+
871 |Build the final where clause concatenating the order by or group by |
872 +--------------------------------------------------------------------*/
873 IF (l_by_clause_pos > 0) THEN
874
875 SELECT SUBSTR(l_in_where_clause, l_by_clause_pos)
876 INTO l_balance_clause
877 FROM dual;
878
879 l_actual_where_clause := l_actual_where_clause || l_balance_clause;
880
881 END IF; --end if by clause pos greater than 0
882
883 IF PG_DEBUG in ('Y', 'C') THEN
884 arp_standard.debug('Build_And_Bind: ' || 'l_actual_where_clause ' || l_actual_where_clause);
885 END IF;
886
887 /*---------------------------------------------------------------------------+
888 |In debug mode dump the contents of the table, which helps bind variables |
889 +---------------------------------------------------------------------------*/
890 FOR l_ctr in 1..l_tbl_ctr LOOP
891
892 IF PG_DEBUG in ('Y', 'C') THEN
893 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').literal_counter = '|| l_literal_tbl(l_ctr).literal_counter);
894 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').bind_var_name = '|| l_literal_tbl(l_ctr).bind_var_name);
895 arp_standard.debug('Build_And_Bind: ' || 'l_literal_tbl('||l_ctr||').stripped_value = '|| l_literal_tbl(l_ctr).stripped_value);
896 END IF;
897
898 END LOOP; --end loop dump debug statements
899
900 p_out_where_clause := l_actual_where_clause;
901
902 p_literal_tbl := l_literal_tbl;
903
904 p_tbl_ctr := l_tbl_ctr;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 IF PG_DEBUG in ('Y', 'C') THEN
909 arp_standard.debug( 'ARP_ARXCOQIT.Build_And_Bind Exception: OTHERS EXCEPTION');
910 END IF;
911 RAISE;
912
913 END Build_And_Bind;
914
915 END ARP_ARXCOQIT;
[Home] [Help]