eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IEX_METRIC_PVT

Source


1 PACKAGE BODY IEX_METRIC_PVT AS
2 /* $Header: iexvmtrb.pls 120.9.12010000.3 2010/04/30 04:03:28 nkanchan ship $ */
3 PG_DEBUG NUMBER;
4 --BEGIN-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
5   TYPE curr_rec_type IS RECORD (
6      set_of_books_id   ar_system_parameters.set_of_books_id%TYPE           ,
7      base_currency     gl_sets_of_books.currency_code%TYPE                 ,
8      base_precision    fnd_currencies.precision%type                       ,
9      base_min_acc_unit fnd_currencies.minimum_accountable_unit%type        ,
10      past_year_from    DATE,
11      past_year_to      DATE
12   );
13 
14   g_curr_rec curr_rec_type;
15 --END-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
16 
17   PROCEDURE Get_Metric_Info
18       (p_api_version      	IN  NUMBER,
19        p_init_msg_list    	IN  VARCHAR2,
20        p_commit           	IN  VARCHAR2,
21        p_validation_level 	IN  NUMBER,
22        x_return_status    	OUT NOCOPY VARCHAR2,
23        x_msg_count        	OUT NOCOPY NUMBER,
24        x_msg_data         	OUT NOCOPY VARCHAR2,
25        p_party_id               IN  NUMBER,
26        p_cust_account_id        IN  NUMBER,
27        p_customer_site_use_id   IN  NUMBER,
28        p_delinquency_id         IN  NUMBER,
29        p_filter_by_object       IN  VARCHAR2,
30        x_metric_id_tbl          OUT NOCOPY Metric_ID_Tbl_Type,
31        x_metric_name_tbl        OUT NOCOPY Metric_Name_Tbl_Type,
32        x_metric_value_tbl       OUT NOCOPY Metric_Value_Tbl_Type,
33        x_metric_rating_tbl      OUT NOCOPY Metric_Rating_Tbl_Type)
34   IS
35 
36     CURSOR c_metric IS
37       SELECT score_comp_type_id, score_comp_name, score_comp_value
38       FROM iex_score_comp_types_vl
39       WHERE active_flag = 'Y'
40       AND metric_flag = 'Y'
41       AND jtf_object_code = NVL(p_filter_by_object, jtf_object_code)
42       ORDER BY display_order;
43 
44     l_current_row NUMBER := 0;
45 
46     l_str VARCHAR2(2000);
47     l_str2 VARCHAR2(2000);
48     l_party_count NUMBER;
49     l_acc_count NUMBER;
50     l_billto_count NUMBER;
51     p_del_count NUMBER;
52     l_start NUMBER;
53     l_str_result VARCHAR2(2000);
54     l_sqlcursor NUMBER;
55 
56     -- l_value VARCHAR2(100); -- bug 5695898 by ehuh 3/2/07
57     l_value VARCHAR2(1000);   -- bug 5695898 by ehuh 3/2/07
58     l_dummy INTEGER;
59 
60     CURSOR c_rating(x_score_comp_type_id NUMBER) IS
61       SELECT low_from, low_to, medium_from, medium_to, high_from, high_to
62       FROM iex_metric_ratings
63       WHERE score_comp_type_id = x_score_comp_type_id;
64 
65     r_rating c_rating%ROWTYPE;
66     l_current_status VARCHAR2(1);
67 --BEGIN-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
68     l_current_value_num NUMBER;
69 --END-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
70 --Begin bug#5208170 schekuri 29-May-2006
71    CURSOR c_currency IS
72    SELECT  gll.currency_code,
73            c.precision,
74            c.minimum_accountable_unit,
75 	   TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
76            TRUNC(sysdate) pastYearTo
77     FROM    ar_system_parameters    sp,
78             gl_ledgers_public_v     gll,
79 	    fnd_currencies     c
80     WHERE   gll.ledger_id = sp.set_of_books_id
81     AND    gll.currency_code   = c.currency_code;
82 --End bug#5208170 schekuri 29-May-2006
83   BEGIN
84 	IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
85             IEX_DEBUG_PUB.logMessage('Object ID values: ' ||  p_party_id || p_cust_account_id || p_customer_site_use_id || p_delinquency_id);
86 
87 	END IF;
88     x_return_status := 'S';
89     --Begin bug#5208170 schekuri 29-May-2006
90     open c_currency;
91     fetch c_currency into IEX_COLL_IND.g_curr_rec.base_currency,
92 	IEX_COLL_IND.g_curr_rec.base_precision,
93 	IEX_COLL_IND.g_curr_rec.base_min_acc_unit,
94 	IEX_COLL_IND.g_curr_rec.past_year_from,
95 	IEX_COLL_IND.g_curr_rec.past_year_to;
96     close c_currency;
97     --End bug#5208170 schekuri 29-May-2006
98 
99 
100     FOR r_metric IN c_metric LOOP
101       l_current_status := 'S';
102       l_current_row := l_current_row + 1;
103 
104       x_metric_id_tbl(l_current_row) := r_metric.score_comp_type_id;
105       x_metric_name_tbl(l_current_row) := r_metric.score_comp_name;
106       l_str := r_metric.score_comp_value;
107       l_str_result := '';
108 
109       l_party_count := 0;
110       l_acc_count := 0;
111       l_billto_count := 0;
112       p_del_count := 0;
113       l_start := 1;
114 
115       IF UPPER(l_str) like 'CALL %' THEN
116         l_str := REPLACE(UPPER(l_str), 'CALL ', '');
117 
118         l_str := SUBSTRB(l_str, 1, INSTRB(l_str,')',1,1));
119 
120         l_str:= l_str || '; END; ';
121 
122         LOOP
123           l_start := NVL(INSTRB(l_str,':',1,1),0);
124           IF l_start > 0 THEN
125             l_str2 := SUBSTRB(l_str,INSTRB(l_str,':',1,1),(INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+2-INSTRB(l_str,':',1,1)+1));
126             IF UPPER(l_str2) = ':PARTY_ID' THEN
127               l_party_count := l_party_count + 1;
128               l_str2 := ':B_PARTY_ID_' || l_party_count;
129             ELSIF UPPER(l_str2) = ':CUST_ACCOUNT_ID' THEN
130               l_acc_count := l_acc_count + 1;
131               l_str2 := ':B_CUST_ACCOUNT_ID_' || l_acc_count;
132             ELSIF UPPER(l_str2) = ':CUSTOMER_SITE_USE_ID' THEN
133               l_billto_count := l_billto_count + 1;
134               l_str2 := ':B_CUSTOMER_SITE_USE_ID_' || l_billto_count;
135             ELSIF UPPER(l_str2) = ':DELINQUENCY_ID' THEN
136               p_del_count := p_del_count + 1;
137               l_str2 := ':B_DELINQUENCY_ID_' || p_del_count;
138             END IF;
139             l_str_result := l_str_result || SUBSTRB(l_str, 1, INSTRB(l_str,':',1,1)-1) || l_str2;
140 
141             l_str := SUBSTRB(l_str, INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+3);
142           ELSE
143             l_str_result := l_str_result || l_str;
144           END IF;
145           EXIT WHEN l_start < 1;
146         END LOOP;
147         l_str_result := 'BEGIN :l_result := ' || l_str_result;
148 
149 
150 	IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
151             IEX_DEBUG_PUB.logMessage('result: ' ||  l_str_result);
152         END IF;
153 
154         l_sqlcursor := DBMS_SQL.OPEN_CURSOR;
155         BEGIN
156           DBMS_SQL.PARSE(l_sqlcursor, l_str_result, 1);
157           -- DBMS_SQL.BIND_VARIABLE_CHAR(l_sqlcursor,'l_result', '0', 2000); -- bug 5695898 by ehuh 3/2/07
158           DBMS_SQL.BIND_VARIABLE_CHAR(l_sqlcursor,'l_result', '0', 1000);    -- bug 5695898 by ehuh 3/2/07
159           --DBMS_SQL.DEFINE_COLUMN(l_sqlcursor, 1, l_count);
160 
161           IF p_party_id > 0 THEN
162 
163             FOR i IN 1..l_party_count LOOP
164 		DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_PARTY_ID_'||i, p_party_id);
165             END LOOP;
166           END IF;
167 
168           IF p_cust_account_id > 0 THEN
169 
170             FOR i IN 1..l_acc_count LOOP
171               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUST_ACCOUNT_ID_'||i, p_cust_account_id);
172             END LOOP;
173           END IF;
174 
175           IF p_customer_site_use_id > 0 THEN
176 
177             FOR i IN 1..l_billto_count LOOP
178               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUSTOMER_SITE_USE_ID_'||i, p_customer_site_use_id);
179             END LOOP;
180           END IF;
181 
182           IF p_delinquency_id > 0 THEN
183 
184             FOR i IN 1..p_del_count LOOP
185               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_DELINQUENCY_ID_'||i, p_delinquency_id);
186             END LOOP;
187           END IF;
188 
189 
190 
191 
192 
193           l_dummy := DBMS_SQL.EXECUTE(l_sqlcursor);
194 
195           DBMS_SQL.VARIABLE_VALUE_CHAR(l_sqlcursor, 'l_result', l_value);
196           x_metric_value_tbl(l_current_row) := RTRIM(l_value, ' ');
197 
198 	IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
199             IEX_DEBUG_PUB.logMessage('Metrics value: ' ||  x_metric_value_tbl(l_current_row));
200         END IF;
201 
202         EXCEPTION
203           WHEN OTHERS THEN
204             l_current_status := 'E';
205             x_metric_value_tbl(l_current_row) := 'NA';
206 
207         END;
208         DBMS_SQL.CLOSE_CURSOR(l_sqlcursor);
209       ELSE
210         LOOP
211           l_start := NVL(INSTRB(l_str,':',1,1),0);
212           IF l_start > 0 THEN
213             l_str2 := SUBSTRB(l_str,INSTRB(l_str,':',1,1),(INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+2-INSTRB(l_str,':',1,1)+1));
214             IF UPPER(l_str2) = ':PARTY_ID' THEN
215               l_party_count := l_party_count + 1;
216               l_str2 := ':B_PARTY_ID_' || l_party_count;
217             ELSIF UPPER(l_str2) = ':CUST_ACCOUNT_ID' THEN
218               l_acc_count := l_acc_count + 1;
219               l_str2 := ':B_CUST_ACCOUNT_ID_' || l_acc_count;
220             ELSIF UPPER(l_str2) = ':CUSTOMER_SITE_USE_ID' THEN
221               l_billto_count := l_billto_count + 1;
222               l_str2 := ':B_CUSTOMER_SITE_USE_ID_' || l_billto_count;
223             ELSIF UPPER(l_str2) = ':DELINQUENCY_ID' THEN
224               p_del_count := p_del_count + 1;
225               l_str2 := ':B_DELINQUENCY_ID_' || p_del_count;
226             END IF;
227             l_str_result := l_str_result || SUBSTRB(l_str, 1, INSTRB(l_str,':',1,1)-1) || l_str2;
228 
229             l_str := SUBSTRB(l_str, INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+3);
230           ELSE
231             l_str_result := l_str_result || l_str;
232           END IF;
233 
234 	  IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
235             IEX_DEBUG_PUB.logMessage('result: ' ||  l_str_result);
236         END IF;
237 
238           EXIT WHEN l_start < 1;
239         END LOOP;
240 
241         l_sqlcursor := DBMS_SQL.OPEN_CURSOR;
242 
243         BEGIN
244           DBMS_SQL.PARSE(l_sqlcursor, l_str_result, 1);
245           DBMS_SQL.DEFINE_COLUMN(l_sqlcursor, 1, l_value, 200);
246 
247           IF p_party_id > 0 THEN
248             FOR i IN 1..l_party_count LOOP
249               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_PARTY_ID_'||i, p_party_id);
250             END LOOP;
251           END IF;
252 
253           IF p_cust_account_id > 0 THEN
254             FOR i IN 1..l_acc_count LOOP
255               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUST_ACCOUNT_ID_'||i, p_cust_account_id);
256             END LOOP;
257           END IF;
258 
259           IF p_customer_site_use_id > 0 THEN
260             FOR i IN 1..l_billto_count LOOP
261               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUSTOMER_SITE_USE_ID_'||i, p_customer_site_use_id);
262             END LOOP;
263           END IF;
264 
265           IF p_delinquency_id > 0 THEN
266             FOR i IN 1..p_del_count LOOP
267               DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_DELINQUENCY_ID_'||i, p_delinquency_id);
268             END LOOP;
269           END IF;
270 
271           l_dummy := DBMS_SQL.EXECUTE(l_sqlcursor);
272 
273           IF DBMS_SQL.FETCH_ROWS(l_sqlcursor) > 0 THEN
274             DBMS_SQL.COLUMN_VALUE(l_sqlcursor,1,l_value);
275           END IF;
276 
277           x_metric_value_tbl(l_current_row) := l_value;
278 
279 	  IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
280             IEX_DEBUG_PUB.logMessage('Metrics value: ' ||  x_metric_value_tbl(l_current_row));
281           END IF;
282 
283         EXCEPTION
284           WHEN OTHERS THEN
285             l_current_status := 'E';
286             x_metric_value_tbl(l_current_row) := 'NA';
287 
288         END;
289         DBMS_SQL.CLOSE_CURSOR(l_sqlcursor);
290       END IF;
291 
292       IF (l_current_status = 'S') AND (x_metric_value_tbl(l_current_row) <> 'NA') THEN
293 --BEGIN-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
294         BEGIN
295           OPEN c_rating(r_metric.score_comp_type_id);
296           FETCH c_rating INTO r_rating;
297 
298           IF c_rating%FOUND THEN
299             CLOSE c_rating;
300 	    --Begin bug#5208170 schekuri 29-May-2006
301             BEGIN
302 	          l_current_value_num := TO_NUMBER(x_metric_value_tbl(l_current_row),
303 	                                     fnd_currency.get_format_mask(iex_coll_ind.g_curr_rec.base_currency, 30));
304             -- Begin fix bug #5360519-JYPARK-07/06/2006-if value is negative and non-amount format, getting number value
305             EXCEPTION
306               WHEN OTHERS THEN
307                 l_current_value_num := x_metric_value_tbl(l_current_row);
308             END;
309             -- End fix bug #5360519-JYPARK-07/06/2006-if value is negative and non-amount format, getting number value
310             --l_current_value_num := TO_NUMBER(x_metric_value_tbl(l_current_row), fnd_currency.get_format_mask(g_curr_rec.base_currency, 30));
311 	    --End bug#5208170 schekuri 29-May-2006
312 
313 --          IF(x_metric_value_tbl(l_current_row) >= r_rating.low_from) AND
314 --            (x_metric_value_tbl(l_current_row) <= r_rating.low_to) THEN
315 --            x_metric_rating_tbl(l_current_row) := 'LOW';
316 --          ELSIF(x_metric_value_tbl(l_current_row) >= r_rating.medium_from) AND
317 --            (x_metric_value_tbl(l_current_row) <= r_rating.medium_to) THEN
318 --            x_metric_rating_tbl(l_current_row) := 'MEDIUM';
319 --          ELSIF(x_metric_value_tbl(l_current_row) >= r_rating.high_from) AND
320 --            (x_metric_value_tbl(l_current_row) <= r_rating.high_to) THEN
321 --            x_metric_rating_tbl(l_current_row) := 'HIGH';
322 --          ELSE
323 --            x_metric_rating_tbl(l_current_row) := '';
324 --          END IF;
325 
326             IF(l_current_value_num >= r_rating.low_from) AND
327               (l_current_value_num <= r_rating.low_to) THEN
328               x_metric_rating_tbl(l_current_row) := 'LOW';
329             ELSIF(l_current_value_num >= r_rating.medium_from) AND
330               (l_current_value_num <= r_rating.medium_to) THEN
331               x_metric_rating_tbl(l_current_row) := 'MEDIUM';
332             ELSIF(l_current_value_num >= r_rating.high_from) AND
333               (l_current_value_num <= r_rating.high_to) THEN
334               x_metric_rating_tbl(l_current_row) := 'HIGH';
335             ELSE
336               x_metric_rating_tbl(l_current_row) := '';
337             END IF;
338           ELSE
339             CLOSE c_rating;
340             x_metric_rating_tbl(l_current_row) := '';
341           END IF;
342         EXCEPTION
343         WHEN OTHERS THEN
344           x_metric_rating_tbl(l_current_row) := '';
345         END;
346 --END-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
347       ELSE
348         x_metric_rating_tbl(l_current_row) := '';
349       END IF;
350     END LOOP;
351 
352 --BEGIN-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
353   EXCEPTION
354   WHEN OTHERS THEN
355      null;
356   END Get_Metric_Info;
357 --END-FIX BUG#4375831-05/18/2005-JYPARK-fix error ORA-06521 because x_metric_value_tbl has charater value so convert to numeric value
358   PROCEDURE Test_Metric
359       (
360        p_filter_id         	IN  NUMBER,
361        p_score_comp_type_id IN  NUMBER,
362        x_return_status    	OUT NOCOPY VARCHAR2,
363        x_metric_value       OUT NOCOPY VARCHAR2)
364   IS
365     CURSOR c_metric IS
366       SELECT score_comp_value
367       FROM iex_score_comp_types_b
368       WHERE score_comp_type_id = p_score_comp_type_id;
369 
370     l_sql_stmt VARCHAR2(2000);
371 
372     l_str VARCHAR2(2000);
373     l_str2 VARCHAR2(2000);
374     l_party_count NUMBER;
375     l_acc_count NUMBER;
376     l_billto_count NUMBER;
377     p_del_count NUMBER;
378     l_start NUMBER;
379     l_str_result VARCHAR2(2000);
380     l_sqlcursor NUMBER;
381     --  l_value VARCHAR2(100); Modified by nkanchan for bug # 8848670
382     l_value VARCHAR2(1000);
383     l_dummy INTEGER;
384 
385     l_current_status VARCHAR2(1);
386   BEGIN
387 
388     x_return_status := 'S';
389 
390     OPEN c_metric;
391     FETCH c_metric INTO l_sql_stmt;
392     IF c_metric%FOUND THEN
393       l_current_status := 'S';
394 
395       l_str := l_sql_stmt;
396       l_str_result := '';
397 
398       l_party_count := 0;
399       l_acc_count := 0;
400       l_billto_count := 0;
401       p_del_count := 0;
402       l_start := 1;
403 
404       IF UPPER(l_str) like 'CALL %' THEN
405         l_str := REPLACE(UPPER(l_str), 'CALL ', '');
406 
407         l_str := SUBSTRB(l_str, 1, INSTRB(l_str,')',1,1));
408 
409         l_str:= l_str || '; END; ';
410 
411         LOOP
412           l_start := NVL(INSTRB(l_str,':',1,1),0);
413           IF l_start > 0 THEN
414             l_str2 := SUBSTRB(l_str,INSTRB(l_str,':',1,1),(INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+2-INSTRB(l_str,':',1,1)+1));
415             IF UPPER(l_str2) = ':PARTY_ID' THEN
416               l_party_count := l_party_count + 1;
417               l_str2 := ':B_PARTY_ID_' || l_party_count;
418             ELSIF UPPER(l_str2) = ':CUST_ACCOUNT_ID' THEN
419               l_acc_count := l_acc_count + 1;
420               l_str2 := ':B_CUST_ACCOUNT_ID_' || l_acc_count;
421             ELSIF UPPER(l_str2) = ':CUSTOMER_SITE_USE_ID' THEN
422               l_billto_count := l_billto_count + 1;
423               l_str2 := ':B_CUSTOMER_SITE_USE_ID_' || l_billto_count;
424             ELSIF UPPER(l_str2) = ':DELINQUENCY_ID' THEN
425               p_del_count := p_del_count + 1;
426               l_str2 := ':B_DELINQUENCY_ID_' || p_del_count;
427             END IF;
428             l_str_result := l_str_result || SUBSTRB(l_str, 1, INSTRB(l_str,':',1,1)-1) || l_str2;
429 
430             l_str := SUBSTRB(l_str, INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+3);
431           ELSE
432             l_str_result := l_str_result || l_str;
433           END IF;
434           EXIT WHEN l_start < 1;
435         END LOOP;
436         l_str_result := 'BEGIN :l_result := ' || l_str_result;
437 
438         l_sqlcursor := DBMS_SQL.OPEN_CURSOR;
439         BEGIN
440           DBMS_SQL.PARSE(l_sqlcursor, l_str_result, 1);
441           -- Changed value from 2000 to 1000 by nkanchan for bug # 8848670
442           DBMS_SQL.BIND_VARIABLE_CHAR(l_sqlcursor,'l_result', '0', 1000);
443           --DBMS_SQL.DEFINE_COLUMN(l_sqlcursor, 1, l_count);
444 
445           FOR i IN 1..l_party_count LOOP
446             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_PARTY_ID_'||i, p_filter_id);
447           END LOOP;
448 
449           FOR i IN 1..l_acc_count LOOP
450             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUST_ACCOUNT_ID_'||i, p_filter_id);
451           END LOOP;
452 
453           FOR i IN 1..l_billto_count LOOP
454             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUSTOMER_SITE_USE_ID_'||i, p_filter_id);
455           END LOOP;
456 
457           FOR i IN 1..p_del_count LOOP
458             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_DELINQUENCY_ID_'||i, p_filter_id);
459           END LOOP;
460 
461           l_dummy := DBMS_SQL.EXECUTE(l_sqlcursor);
462 
463           DBMS_SQL.VARIABLE_VALUE_CHAR(l_sqlcursor, 'l_result', l_value);
464           x_metric_value := RTRIM(l_value, ' ');
465 
466 
467         EXCEPTION
468           WHEN OTHERS THEN
469             x_return_status := 'E';
470             x_metric_value := iex_utilities.get_lookup_meaning('IEX_METRIC_STATUS', 'NA');
471             fnd_message.set_name ('IEX', 'JTF_CHK_UNKNOWN_ERROR');
472             fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
473             fnd_msg_pub.add;
474         END;
475         DBMS_SQL.CLOSE_CURSOR(l_sqlcursor);
476       ELSE
477         LOOP
478           l_start := NVL(INSTRB(l_str,':',1,1),0);
479           IF l_start > 0 THEN
480             l_str2 := SUBSTRB(l_str,INSTRB(l_str,':',1,1),(INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+2-INSTRB(l_str,':',1,1)+1));
481             IF UPPER(l_str2) = ':PARTY_ID' THEN
482               l_party_count := l_party_count + 1;
483               l_str2 := ':B_PARTY_ID_' || l_party_count;
484             ELSIF UPPER(l_str2) = ':CUST_ACCOUNT_ID' THEN
485               l_acc_count := l_acc_count + 1;
486               l_str2 := ':B_CUST_ACCOUNT_ID_' || l_acc_count;
487             ELSIF UPPER(l_str2) = ':CUSTOMER_SITE_USE_ID' THEN
488               l_billto_count := l_billto_count + 1;
489               l_str2 := ':B_CUSTOMER_SITE_USE_ID_' || l_billto_count;
490             ELSIF UPPER(l_str2) = ':DELINQUENCY_ID' THEN
491               p_del_count := p_del_count + 1;
492               l_str2 := ':B_DELINQUENCY_ID_' || p_del_count;
493             END IF;
494             l_str_result := l_str_result || SUBSTRB(l_str, 1, INSTRB(l_str,':',1,1)-1) || l_str2;
495 
496             l_str := SUBSTRB(l_str, INSTRB(UPPER(l_str),'_ID',INSTRB(l_str,':',1,1),1)+3);
497           ELSE
498             l_str_result := l_str_result || l_str;
499           END IF;
500           EXIT WHEN l_start < 1;
501         END LOOP;
502 
503 
504         l_sqlcursor := DBMS_SQL.OPEN_CURSOR;
505 
506         BEGIN
507           DBMS_SQL.PARSE(l_sqlcursor, l_str_result, 1);
508           DBMS_SQL.DEFINE_COLUMN(l_sqlcursor, 1, l_value, 200);
509 
510           FOR i IN 1..l_party_count LOOP
511             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_PARTY_ID_'||i, p_filter_id);
512           END LOOP;
513 
514           FOR i IN 1..l_acc_count LOOP
515             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUST_ACCOUNT_ID_'||i, p_filter_id);
516           END LOOP;
517 
518           FOR i IN 1..l_billto_count LOOP
519             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_CUSTOMER_SITE_USE_ID_'||i, p_filter_id);
520           END LOOP;
521 
522           FOR i IN 1..p_del_count LOOP
523             DBMS_SQL.BIND_VARIABLE(l_sqlcursor,'B_DELINQUENCY_ID_'||i, p_filter_id);
524           END LOOP;
525 
526           l_dummy := DBMS_SQL.EXECUTE(l_sqlcursor);
527 
528           IF DBMS_SQL.FETCH_ROWS(l_sqlcursor) > 0 THEN
529             DBMS_SQL.COLUMN_VALUE(l_sqlcursor,1,l_value);
530           END IF;
531 
532           x_metric_value := l_value;
533 
534         EXCEPTION
535           WHEN OTHERS THEN
536             x_return_status := 'E';
537             x_metric_value := iex_utilities.get_lookup_meaning('IEX_METRIC_STATUS', 'NA');
538             fnd_message.set_name ('IEX', 'IEX_METRIC_SQL_ERROR');
539             fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
540             fnd_msg_pub.add;
541         END;
542         DBMS_SQL.CLOSE_CURSOR(l_sqlcursor);
543       END IF;
544     END IF;
545     CLOSE c_metric;
546 
547   END Test_Metric;
548 BEGIN
549   --Strat MOAC
550   --Replaced view gl_sets_of_books with gl_ledgers_public_v
551   --Begin bug#5208170 schekuri 29-May-2006
552   --Removed this because it's not being used anywhere
553   /*SELECT gll.currency_code,
554          c.precision,
555          c.minimum_accountable_unit
556     INTO   g_curr_rec.base_currency,
557            g_curr_rec.base_precision,
558            g_curr_rec.base_min_acc_unit
559     FROM   ar_system_parameters   sysp,
560            gl_ledgers_public_v    gll,
561            fnd_currencies     c
562    WHERE  gll.ledger_id = sysp.set_of_books_id
563      AND    gll.currency_code   = c.currency_code;*/
564  --End bug#5208170 schekuri 29-May-2006
565   /*SELECT sob.currency_code,
566          c.precision,
567          c.minimum_accountable_unit
568     INTO   g_curr_rec.base_currency,
569            g_curr_rec.base_precision,
570            g_curr_rec.base_min_acc_unit
571     FROM   ar_system_parameters   sysp,
572            gl_sets_of_books     sob,
573            fnd_currencies     c
574    WHERE  sob.set_of_books_id = sysp.set_of_books_id
575      AND    sob.currency_code   = c.currency_code;*/
576    --End MOAC
577 
578   -- Past Year From and To
579   --Begin bug#5208170 schekuri 29-May-2006
580   --Removed this because it's not being used anywhere
581  /* SELECT  TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
582           TRUNC(sysdate) pastYearTo
583     INTO  g_curr_rec.past_year_from,
584           g_curr_rec.past_year_to
585     FROM  dual;*/
586     NULL;
587     --End bug#5208170 schekuri 29-May-2006
588 EXCEPTION
589   WHEN OTHERS THEN
590     NULL;
591 
592 END;