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;