1 Package Body IGIRMINP AS
2 -- $Header: igirminb.pls 120.3.12000000.1 2007/09/13 04:01:21 mbremkum ship $
3
4 -- from arp_arxcoqit start(1)
5
6 TYPE literal_rec_type IS RECORD (
7 literal_counter NUMBER ,
8 bind_var_name VARCHAR2(1000) ,
9 stripped_value VARCHAR2(1000)
10 );
11
12
13 --
14 -- Stripped where clause literal table
15 --
16 TYPE literal_tbl_type IS TABLE of literal_rec_type
17 INDEX BY BINARY_INTEGER;
18
19 PROCEDURE Build_And_Bind ( p_in_where_clause IN VARCHAR2 ,
20 p_out_where_clause OUT NOCOPY VARCHAR2 ,
21 p_literal_tbl OUT NOCOPY literal_tbl_type ,
22 p_tbl_ctr OUT NOCOPY BINARY_INTEGER );
23
24 -- from arp_arxcoqit end(1)
25
26 CURSOR c_ar_pay_sched ( cp_customer_trx_id in number, cp_status in varchar2) IS
27 SELECT arps.customer_trx_id, arps.payment_schedule_id,
28 arps.terms_sequence_number
29 FROM ar_payment_schedules arps
30 WHERE arps.customer_Trx_id = cp_customer_trx_id
31 AND arps.status = cp_status
32 order by arps.due_date asc
33 ;
34 SUBTYPE ARPSCHED IS c_ar_pay_sched%ROWTYPE;
35 TYPE ARPSCHED_TAB is table of ARPSCHED
36 INDEX BY BINARY_INTEGER;
37
38 PROCEDURE Reschedule ( p_customer_trx_id in number) IS
39 l_arpsched ARPSCHED_TAB;
40 l_idx BINARY_INTEGER;
41 l_idx2 BINARY_INTEGER;
42 /* FUNCTION IsCandidate ( fp_customer_trx_id in number )
43 return boolean is
44 CURSOR c_exists is select distinct 'x'
45 from ar_payment_schedules ps1
46 where exists ( select 'x'
47 from ar_payment_schedules ps2
48 where status = 'CL'
49 and ps2.customer_trx_id = fp_customer_trx_id
50 )
51 and exists ( select 'x'
52 from ar_payment_schedules ps3
53 where status = 'OP'
54 and ps3.customer_trx_id = fp_customer_trx_id
55 )
56 order by ps1.customer_Trx_id
57 ;
58 begin
59 FOR l_exists in C_exists LOOP
60 return TRUE;
61 END LOOP;
62 return FALSE;
63 exception when others then
64 return FALSE;
65 end IsCandidate;*/
66 BEGIN
67
68 if not igi_gen.is_req_installed('INS') then
69 return;
70 end if;
71
72 l_idx := 0;
73 FOR l_sched in c_ar_pay_sched ( p_customer_trx_id , 'OP' ) LOOP
74 l_idx := l_idx + 1;
75 l_arpsched( l_idx ) := l_sched;
76 l_arpsched( l_idx ).terms_sequence_number := l_idx;
77 END LOOP;
78 FOR l_sched in c_ar_pay_sched ( p_customer_trx_id , 'CL' ) LOOP
79 l_idx := l_idx + 1;
80 l_arpsched( l_idx ) := l_sched;
81 l_arpsched( l_idx ).terms_sequence_number := l_idx;
82 END LOOP;
83 if l_idx = 0 THEN
84 return;
85 end if;
86 l_idx2 := l_idx;
87 WHILE l_idx2 >= 1 LOOP
88 UPDATE ar_payment_schedules
89 SET terms_sequence_number = l_arpsched( l_idx2 ).terms_sequence_number
90 WHERE customer_trx_id = l_arpsched( l_idx2 ).customer_trx_id
91 AND payment_schedule_id = l_arpsched( l_idx2 ).payment_schedule_id
92 ;
93 l_idx2 := l_idx2 - 1;
94 END LOOP;
95 EXCEPTION WHEN OTHERS THEN
96 raise_application_error ( -20000, SQLERRM );
97 END Reschedule;
98
99
100
101 /* This Procedure Calculates the entered total balance and the functional total
102 balance for the passed in 'where clause ' */
103
104 --Bug 1563252 : Added the new argument p_from_clause
105 PROCEDURE fold_total( p_where_clause IN varchar2,
106 p_total IN OUT NOCOPY number,
107 p_func_total IN OUT NOCOPY number,
108 p_from_clause IN varchar2 )
109 is
110 l_select_cursor INTEGER;
111 l_ignore INTEGER;
112 l_amount NUMBER;
113 l_func_amount NUMBER;
114 l_count NUMBER;
115 l_ctr BINARY_INTEGER;
116 l_tbl_ctr BINARY_INTEGER;
117 l_literal_tbl literal_tbl_type;
118 l_out_where_clause VARCHAR2(32767);
119 l_actual_bind_var VARCHAR2(2000);
120
121 BEGIN
122
123 l_out_where_clause := '';
124
125 p_total := 0;
126
127 p_func_total := 0;
128
129 -- arp_standard.enable_debug;
130
131 /*-----------------------------------------------------------------------+
132 |Removed the 'WHERE' from the Parse Statement , It now comes in |
133 |p_where_clause before the actual where clause , This is done to take |
134 |care of the Null Where Clause Case. |
135 +-----------------------------------------------------------------------*/
136
137 arp_standard.debug('where clause:' || p_where_clause );
138
139 arp_standard.debug('Opening Cursor');
140
141 l_select_cursor := dbms_sql.open_cursor;
142
143 /*-----------------------------------------------------------------------+
144 |Call the Build and Bind routine to strip where clause from literals and|
145 |numeric constants and replace them with bind variables. |
146 +-----------------------------------------------------------------------*/
147 IF p_where_clause IS NOT NULL THEN
148 Build_And_Bind(p_where_clause, l_out_where_clause, l_literal_tbl, l_tbl_ctr);
149 END IF;
150
151 /*-----------------------------------------------------------------------+
152 |Parse the built statement along with the where clause. |
153 +-----------------------------------------------------------------------*/
154 arp_standard.debug('Parsing statement ');
155 arp_standard.debug(l_out_where_clause);
156 --Bug 1563252 : modified the query string to add the from clause dynamically.
157 dbms_sql.parse(l_select_cursor,
158 'select count( distinct invoice_currency_code ), sum(amount_due_remaining), sum(acctd_amount_due_remaining) from '||p_from_clause||' '||l_out_where_clause,
159 dbms_sql.v7);
160
161 /*-----------------------------------------------------------------------+
162 |Define columns for the select statement. |
163 +-----------------------------------------------------------------------*/
164 arp_standard.debug('Defining Columns +');
165
166 dbms_sql.define_column(l_select_cursor, 1 , l_count);
167 dbms_sql.define_column(l_select_cursor, 2 , l_amount);
168 dbms_sql.define_column(l_select_cursor, 3 , l_func_amount);
169
170 arp_standard.debug('Defining Columns -');
171
172 /*-----------------------------------------------------------------------+
173 |Bind the variables built by Build_And_Bind routine with actual values |
174 +-----------------------------------------------------------------------*/
175 IF ((l_literal_tbl.EXISTS(l_tbl_ctr)) AND (p_where_clause IS NOT NULL)) THEN
176
177 arp_standard.debug('Binding Variables +');
178
179 FOR l_ctr in 1..l_tbl_ctr LOOP
180
181 l_actual_bind_var := '';
182
183 --Bind variables
184 arp_standard.debug('l_literal_tbl('||l_ctr||').bind_var_name = ' || l_literal_tbl(l_ctr).bind_var_name);
185 arp_standard.debug('l_literal_tbl('||l_ctr||').stripped_value = ' || l_literal_tbl(l_ctr).stripped_value);
186
187 l_actual_bind_var := rtrim(ltrim(l_literal_tbl(l_ctr).bind_var_name));
188
189 arp_standard.debug('l_actual_bind_var = '||l_actual_bind_var);
190
191 dbms_sql.bind_variable(l_select_cursor, l_actual_bind_var, l_literal_tbl(l_ctr).stripped_value);
192
193 END LOOP;
194
195 arp_standard.debug('Binding Variables -');
196
197 END IF;
198
199 /*-----------------------------------------------------------------------+
200 |Execute the SQL statement to calculate functional amount and accounted |
201 |amount totals. |
202 +-----------------------------------------------------------------------*/
203 arp_standard.debug('Executing Statement +');
204
205 l_ignore := dbms_sql.execute(l_select_cursor);
206
207 arp_standard.debug('Executing Statement -');
208
209 IF dbms_sql.fetch_rows(l_select_cursor) > 0 then
210
211 /*-----------------------------------------------------------------------+
212 |Fetch the column values, into actual variables |
213 +-----------------------------------------------------------------------*/
214 arp_standard.debug('Fetching column values +');
215
216 dbms_sql.column_value(l_select_cursor, 1, l_count);
217 dbms_sql.column_value(l_select_cursor, 2, l_amount);
218 dbms_sql.column_value(l_select_cursor, 3, l_func_amount);
219
220 arp_standard.debug('l_count '||l_count);
221 arp_standard.debug('l_amount'||l_amount);
222 arp_standard.debug('l_func_amount'||l_func_amount);
223
224 IF l_count = 1 THEN
225 p_total := l_amount;
226 ELSE
227 p_total := to_number(NULL);
228 END IF;
229
230 p_func_total := l_func_amount;
231
232 arp_standard.debug('p_total '||p_total);
233 arp_standard.debug('p_func_total'||p_func_total);
234 arp_standard.debug('Fetching column values -');
235
236 ELSE
237 arp_standard.debug('no rows');
238 END IF;
239
240 /*-----------------------------------------------------------------------+
241 |Finally close the cursor |
242 +-----------------------------------------------------------------------*/
243 arp_standard.debug('Closing Cursor');
244 dbms_sql.close_cursor(l_select_cursor);
245
246 -- arp_standard.enable_debug;
247 EXCEPTION
248 WHEN OTHERS THEN
249 arp_standard.debug( 'Exception:' );
250 END;
251
252
253 /* ===============================================================================
254 | PROCEDURE Build_And_Bind
255 |
256 | DESCRIPTION
257 | Strips a where clause storing the literal values and numeric constants,
258 | replacing them with bind variables. The actual values to be bound later
259 | are stored in a PLSQL table along with the actual bind variable so that
260 | they can be bound later.
261 |
262 | SCOPE - PRIVATE
263 |
264 | PARAMETERS
265 | p_in_where_clause IN Input where clause to be stripped
266 | p_out_where_clause OUT NOCOPY Output where clause containing bind variables
267 | p_literal_tbl OUT NOCOPY Table containing bind variable name and values
268 | p_tbl_ctr OUT NOCOPY Count of bind variables
269 |
270 | Modification History
271 | 16th May 99 Vikram Ahluwalia Created
272 *==============================================================================*/
273 PROCEDURE Build_And_Bind ( p_in_where_clause IN VARCHAR2 ,
274 p_out_where_clause OUT NOCOPY VARCHAR2 ,
275 p_literal_tbl OUT NOCOPY literal_tbl_type ,
276 p_tbl_ctr OUT NOCOPY BINARY_INTEGER ) IS
277
278 l_in_where_clause VARCHAR2(32767) ;
279
280 l_length BINARY_INTEGER ;
281
282 l_ctr BINARY_INTEGER ;
283
284 l_bind_ctr BINARY_INTEGER := 0 ;
285
286 l_bind_var VARCHAR2(1000) ;
287
288 l_temp_cell VARCHAR2(1) ;
289
290 l_prev_cell VARCHAR2(1) ;
291
292 l_actual_where_clause VARCHAR2(32767) ;
293
294 l_balance_clause VARCHAR2(32767) ;
295
296 char_literal_on BOOLEAN := FALSE ;
297
298 num_literal_on BOOLEAN := FALSE ;
299
300 not_bound_flag BOOLEAN := FALSE ;
301
302 l_build_where BOOLEAN := FALSE ;
303
304 l_tbl_ctr BINARY_INTEGER := 0 ;
305
306 l_literal_tbl literal_tbl_type ;
307
308 l_amount NUMBER ;
309
310 l_func_amount NUMBER ;
311
312 l_count NUMBER ;
313
314 l_by_clause_pos BINARY_INTEGER;
315
316 l_actual_length BINARY_INTEGER;
317
318 BEGIN
319
320 arp_standard.debug('l_in_where_clause ' || l_in_where_clause);
321
322 l_in_where_clause := p_in_where_clause;
323
324 /*---------------------------------------------------------------+
325 |Get the length in characters of the where clause as Step 1 |
326 +---------------------------------------------------------------*/
327 select length(l_in_where_clause)
328 into l_actual_length
329 from dual;
330
331 l_by_clause_pos := 0;
332
333 /*--------------------------------------------------------------------+
334 |Strip the 'order by' clause if it is present as part of where clause|
335 +--------------------------------------------------------------------*/
336 select instr(l_in_where_clause, 'order by')
337 into l_by_clause_pos
338 from dual;
339
340 IF (l_by_clause_pos > 0) THEN
341 l_length := l_by_clause_pos -1;
342 ELSE
343
344 /*--------------------------------------------------------------------+
345 |Strip the 'group by clause' if it is present as part of where clause|
346 +--------------------------------------------------------------------*/
347 SELECT INSTR(l_in_where_clause, 'group by')
348 INTO l_by_clause_pos
349 FROM DUAL;
350
351 IF (l_by_clause_pos > 0) THEN
352 l_length := l_by_clause_pos - 1;
353 ELSE
354 l_length := l_actual_length;
355 END IF;
356
357 END IF; --end if l_by_clause_pos > 0
358
359 l_temp_cell := ' ';
360
361 /*----------------------------------------------------------------+
362 |Loop through the where clause storing it into a table as Step 2 |
363 +----------------------------------------------------------------*/
364 FOR l_ctr IN 1..(l_length+1) LOOP
365
366 l_prev_cell := l_temp_cell;
367
368 IF (l_ctr = (l_length + 1)) THEN
369 l_temp_cell := ' ';
370 ELSE
371 select substr(l_in_where_clause, l_ctr, 1)
372 into l_temp_cell
373 from dual;
374 END IF;
375
376 /*----------------------------------------------------------------+
377 |Check for character literals - they use the de-limiter quote |
378 +----------------------------------------------------------------*/
379 IF ((l_temp_cell = '''') AND (NOT num_literal_on)) THEN
380 IF (char_literal_on) THEN
381 char_literal_on := FALSE; --end point
382 ELSE
383 char_literal_on := TRUE; --start point
384 not_bound_flag := TRUE;
385 l_build_where := FALSE;
386 END IF;
387 END IF;
388
389 /*------------------------------------------------------------------------------------+
390 |Check for numeric literals, the check for alphabets A to Z is for database columns |
391 |having names such as col14 so it would not matter if the NLS lang was not english |
392 |as database columns are represented using alphabets A to Z. |
393 +------------------------------------------------------------------------------------*/
394 IF ((l_temp_cell IN ('1','2','3','4','5','6','7','8','9','0'))
395 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
396
397 num_literal_on := TRUE; --start point
398 not_bound_flag := TRUE;
399 l_build_where := FALSE;
400 END IF;
401
402 /*-------------------------------------------------------------------------+
403 |A numeric or character literal requires to be replaced by a bind variable|
404 |the value requires to be stored so that it can be bound later. |
405 +-------------------------------------------------------------------------*/
406 IF (((char_literal_on) OR (num_literal_on)) AND (not_bound_flag)) THEN
407 l_bind_var := '';
408 l_bind_ctr := l_bind_ctr + 1;
409 l_tbl_ctr := l_tbl_ctr + 1;
410 l_bind_var := ' :l_var'||l_bind_ctr||' ';
411
412 l_literal_tbl(l_tbl_ctr).stripped_value := '';
413
414 /*---------------------------------------------------------------------------+
415 |Its possible for a numeric value to be prefixed by a +, - or . so take care|
416 |of that situation. |
417 +---------------------------------------------------------------------------*/
418 IF (num_literal_on) AND l_prev_cell IN (',','.','+','-')THEN
419
420 arp_standard.debug('l_prev_cell = ' || l_prev_cell); --set values for numeric token
421 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || l_prev_cell;
422 ELSE
423 l_actual_where_clause := l_actual_where_clause || l_prev_cell; --Build the previous cell
424 END IF;
425
426 /*--------------------------------------------------------------------------------+
427 |Concatenate the actual bind variable to the where clause to enable binding later|
428 +---------------------------------------------------------------------------------*/
429 IF (num_literal_on) THEN
430 l_actual_where_clause := l_actual_where_clause ||'TO_NUMBER('||l_bind_var||')';
431 ELSE
432 l_actual_where_clause := l_actual_where_clause ||l_bind_var;
433 END IF;
434
435 l_literal_tbl(l_tbl_ctr).literal_counter := l_tbl_ctr;
436 l_literal_tbl(l_tbl_ctr).bind_var_name := l_bind_var ;
437 not_bound_flag := FALSE;
438
439 /*---------------------------------------------------------------------------+
440 |Build the actual where clause, this is also built when the literal value is|
441 |replaced with a bind variable. |
442 +---------------------------------------------------------------------------*/
443 ELSIF (l_build_where) THEN
444
445 l_actual_where_clause := l_actual_where_clause || l_prev_cell; --Build the previous cell
446
447 l_build_where := FALSE;
448
449 END IF;
450
451 /*------------------------------------------------------------------------------+
452 | Save the actual values to be bound to variables later |
453 +------------------------------------------------------------------------------*/
454 IF (((char_literal_on) AND (l_temp_cell <> '''')) OR (num_literal_on)) THEN
455
456 IF ((num_literal_on) AND (l_temp_cell IN (' ',';','(',')','=','!','<','>','*'))) THEN
457 num_literal_on := FALSE; --end point
458 l_build_where := TRUE ; --set the flag so that the actual where clause can be built
459
460 ELSE
461 arp_standard.debug('l_temp_cell = ' || l_temp_cell); --set values
462 l_literal_tbl(l_tbl_ctr).stripped_value := l_literal_tbl(l_tbl_ctr).stripped_value || l_temp_cell;
463
464 END IF;
465
466 ELSIF (l_temp_cell <> '''') THEN
467 l_build_where := TRUE; --set the flag so that the actual where clause can be built
468
469 END IF; --end if character or numeric literal on
470
471 END LOOP; --end loop length of character string
472
473 /*--------------------------------------------------------------------+
474 |Build the final where clause concatenating the order by or group by |
475 +--------------------------------------------------------------------*/
476 IF (l_by_clause_pos > 0) THEN
477
478 SELECT SUBSTR(l_in_where_clause, l_by_clause_pos)
479 INTO l_balance_clause
480 FROM dual;
481
482 l_actual_where_clause := l_actual_where_clause || l_balance_clause;
483
484 END IF; --end if by clause pos greater than 0
485
486 arp_standard.debug('l_actual_where_clause ' || l_actual_where_clause);
487
488 /*---------------------------------------------------------------------------+
489 |In debug mode dump the contents of the table, which helps bind variables |
490 +---------------------------------------------------------------------------*/
491 FOR l_ctr in 1..l_tbl_ctr LOOP
492
493 arp_standard.debug('l_literal_tbl('||l_ctr||').literal_counter = '|| l_literal_tbl(l_ctr).literal_counter);
494 arp_standard.debug('l_literal_tbl('||l_ctr||').bind_var_name = '|| l_literal_tbl(l_ctr).bind_var_name);
495 arp_standard.debug('l_literal_tbl('||l_ctr||').stripped_value = '|| l_literal_tbl(l_ctr).stripped_value);
496
497 END LOOP; --end loop dump debug statements
498
499 p_out_where_clause := l_actual_where_clause;
500
501 p_literal_tbl := l_literal_tbl;
502
503 p_tbl_ctr := l_tbl_ctr;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 arp_standard.debug( 'ARP_ARXCOQIT.Build_And_Bind Exception: OTHERS EXCEPTION');
508 RAISE;
509
510 END Build_And_Bind;
511
512
513 END;
[Home] [Help]