PACKAGE BODY: APPS.FTP_CONDITION_PVT

Source

1 Package BODY ftp_condition_pvt AS
2 /* $Header: ftpcondb.pls 120.1 2006/08/24 14:42:14 ashukuma noship $ */
3 
4 
5 /**
6   * Function to get object_id from fnd_objects goven a object name.
7   * @param     p_object_name    Object Name
8   * @return    object_id      Object Identifier
9   */
10 FUNCTION get_dim_member_name(p_dimension_id IN NUMBER, p_member_id IN VARCHAR2)
11   RETURN VARCHAR2 IS
12   l_member_table    fem_xdim_dimensions.member_vl_object_name%TYPE;
13   l_member_id_col   fem_xdim_dimensions.member_col%TYPE;
14   l_member_name_col fem_xdim_dimensions.member_name_col%TYPE;
15   l_member_name     VARCHAR2(150);
16   l_select_stmt     VARCHAR2(500);
17   l_cursor_id       INTEGER;
18   l_Dummy           INTEGER;
19 BEGIN
20   -- Open the cursor for processing.
21   l_cursor_id := DBMS_SQL.OPEN_CURSOR;
22 
23   -- Create the query string.
24   SELECT member_vl_object_name, member_col, member_name_col
25   INTO l_member_table, l_member_id_col, l_member_name_col
26   FROM fem_xdim_dimensions
27   WHERE dimension_id = p_dimension_id;
28 
29   l_select_stmt := 'SELECT ' || l_member_name_col ||
30                    ' FROM '  || l_member_table ||
31                    ' WHERE ' || l_member_id_col || '=:member_id';
32 
33   -- Parse the statement
34   DBMS_SQL.PARSE(l_cursor_id, l_select_stmt, DBMS_SQL.NATIVE);
35 
36   -- Bind the input variables.
37   DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':member_id', p_member_id);
38 
39   -- Define the select list items.
40   DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_member_name, 150);
41 
42   l_Dummy := DBMS_SQL.EXECUTE(l_cursor_id);
43 
44   IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
45     RETURN null;
46   ELSE
47     DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_member_name);
48   END IF;
49 DBMS_SQL.CLOSE_CURSOR (l_cursor_id );
50   RETURN l_member_name;
51 END;
52 
53 
54 END;