1 PACKAGE BODY hri_bpl_dim_lvl AS
2 /* $Header: hribdlv.pkb 120.0 2005/05/29 06:52:40 appldev noship $ */
3
4 /******************************************************************************/
5 /* Returns the label for a dimension level value */
6 /* */
7 /* Inputs: */
8 /* p_dim_lvl_name: DIMENSION+LEVEL */
9 /* p_dim_lvl_pk: ID from lookup view */
10 /* p_name_type: e.g. SHORT or LONG */
11 /* */
12 /* The lookup view corresponding to the dimension level is chosen. For the */
13 /* ID given the dimension level value row is queried and the appropriate */
14 /* column returned. */
15 /******************************************************************************/
16 FUNCTION get_value_label(p_dim_lvl_name VARCHAR2,
17 p_dim_lvl_pk VARCHAR2,
18 p_name_type VARCHAR2)
19 RETURN VARCHAR2 IS
20
21 /* Cursor is dynamic as the lookup view changes with */
22 /* the dimension level passed in */
23 TYPE c_dim_lvl_value_csrtype IS REF CURSOR;
24
25 c_dim_lvl_value_csr c_dim_lvl_value_csrtype;
26 l_dim_lvl_value VARCHAR2(240);
27 l_dim_lvl_view VARCHAR2(30);
28
29 BEGIN
30
31 /* Look up the LOV view for the given dimension */
32 l_dim_lvl_view := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
33 (p_dim_lvl_name).viewby_table;
34
35 /* Return the dimension level value from the LOV view */
36 OPEN c_dim_lvl_value_csr FOR
37 'SELECT value
38 FROM ' || l_dim_lvl_view || '
39 WHERE id = :id'
40 USING p_dim_lvl_pk;
41 FETCH c_dim_lvl_value_csr INTO l_dim_lvl_value;
42 CLOSE c_dim_lvl_value_csr;
43
44 /* Return the value */
45 RETURN l_dim_lvl_value;
46
47 END get_value_label;
48
49 END hri_bpl_dim_lvl;