[Home] [Help]
PACKAGE BODY: APPS.PV_CMDASHBOARD_UTIL
Source
1 PACKAGE BODY PV_CMDASHBOARD_UTIL AS
2 /* $Header: pvxvcdub.pls 120.3 2005/09/12 05:32:57 appldev noship $ */
3
4 PROCEDURE get_kpis_detail (
5 p_resource_id IN NUMBER,
6 p_kpi_set IN OUT NOCOPY kpi_tbl_type )
7 IS
8
9 -- Cursor to select all attrbutes details.
10 CURSOR l_sql_text_csr(cv_attribute_id NUMBER) IS
11 SELECT pav.name name, pav.return_type return_type, pav.display_style display_style,
12 pea.sql_text sql_text, pav.enabled_flag enabled_flag
13 FROM pv_entity_attrs pea,
14 pv_attributes_vl pav
15 WHERE entity = 'PARTNER_GROUP_KPI'
16 AND pea.attribute_id = pav.attribute_id
17 AND pav.attribute_id = cv_attribute_id
18 AND pav.enabled_flag = 'Y';
19
20 l_sql_stmt VARCHAR2(4000);
21 l_attr_name VARCHAR2(60);
22 l_return_type VARCHAR2(30);
23 l_display_style VARCHAR2(30);
24 l_currency VARCHAR2(30);
25 l_value NUMBER;
26 l_attribute_id NUMBER;
27 l_resource_id NUMBER := p_resource_id;
28 l_enabled_flag VARCHAR2(1);
29 rec_index NUMBER;
30
31 BEGIN
32
33 l_currency := FND_PROFILE.value('ICX_PREFERRED_CURRENCY');
34
35 FOR rec_index IN p_kpi_set.first..p_kpi_set.last
36 LOOP
37
38 l_attribute_id := p_kpi_set(rec_index).attribute_id;
39
40 -- Get the SQL statement for the given attribute_id.
41
42 OPEN l_sql_text_csr(l_attribute_id );
43 FETCH l_sql_text_csr INTO l_attr_name, l_return_type, l_display_style, l_sql_stmt, l_enabled_flag;
44
45 -- Initialize the common out variables for each attribute.
46 p_kpi_set(rec_index).attribute_id := l_attribute_id ;
47 p_kpi_set(rec_index).attribute_name := l_attr_name ;
48
49 IF ( l_sql_text_csr%FOUND) THEN
50
51 BEGIN
52 IF (l_return_type = 'CURRENCY' ) THEN
53
54 EXECUTE IMMEDIATE l_sql_stmt
55 INTO l_value
56 USING l_currency, l_attribute_id, l_resource_id;
57
58 ELSIF (l_return_type = 'NUMBER') THEN
59
60 EXECUTE IMMEDIATE l_sql_stmt
61 INTO l_value
62 USING l_attribute_id, l_resource_id;
63
64 END IF;
65
66 IF l_value is NULL THEN
67 l_value := 0;
68 END IF;
69
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 l_value := 0;
73 WHEN OTHERS THEN
74 l_value := null;
75 END;
76
77 -- Initialize the out variables for each attribute.
78 p_kpi_set(rec_index).attribute_value := l_value ;
79 p_kpi_set(rec_index).enabled_flag := l_enabled_flag ;
80 p_kpi_set(rec_index).display_style := l_display_style ;
81
82 ELSIF (l_sql_text_csr%NOTFOUND) THEN
83
84 -- Initialize the out variables for each attribute.
85
86 p_kpi_set(rec_index).attribute_value := null ;
87 p_kpi_set(rec_index).enabled_flag := 'N' ;
88 p_kpi_set(rec_index).display_style := null ;
89
90 END IF;
91
92 -- Close the cursor l_sql_text_csr
93 CLOSE l_sql_text_csr;
94
95 END LOOP;
96
97 END get_kpis_detail ;
98
99 END PV_CMDASHBOARD_UTIL;