[Home] [Help]
PACKAGE BODY: APPS.JTM_VIEW
Source
1 PACKAGE BODY jtm_view AS
2 /* $Header: jtmviewb.pls 120.1 2005/08/24 02:19:05 saradhak noship $ */
3
4 /* declaration of private global variables */
5 -- simple cache
6 g_key1 VARCHAR2(80) := null; -- profile_option_name in fnd_profile_options_tl
7 g_col1_1 VARCHAR2(240) := null; -- user_profile_option_name
8 g_col1_2 VARCHAR2(240) := null; -- description
9 g_key2_1 VARCHAR2(30) := null; -- lookup_type in fnd_lookup_types_tl
10 g_key2_2 NUMBER(15) := null; -- security_group_id
11 g_key2_3 NUMBER(15) := null; -- view_application_id
12 g_col2_1 VARCHAR2(80) := null; -- meaning
13 g_col2_2 VARCHAR2(240) := null; -- description
14 g_key3_1 NUMBER(10) := null; -- application_id in fnd_descriptive_flexs_tl
15 g_key3_2 VARCHAR2(40) := null; -- descriptive_flexfield_name
16 g_col3_1 VARCHAR2(60) := null; -- title
17 g_col3_2 VARCHAR2(45) := null; -- form_context_prompt
18 g_col3_3 VARCHAR2(240) := null; -- description
19
20 /* declaration of private procedures and functions */
21 PROCEDURE do_fnd_profile_options_tl (p_profile_option_name VARCHAR2);
22 PROCEDURE do_fnd_lookup_types_tl (p_lookup_type VARCHAR2, p_security_group_id NUMBER, p_view_application_id NUMBER);
23 PROCEDURE do_fnd_descriptive_flexs_tl (p_application_id NUMBER, p_descriptive_flexfield_name VARCHAR2);
24
25 FUNCTION get_fnd_profile_options_name (p_profile_option_name VARCHAR2) RETURN VARCHAR2 IS
26 BEGIN
27 do_fnd_profile_options_tl(p_profile_option_name);
28 RETURN g_col1_1;
29 END get_fnd_profile_options_name;
30
31 FUNCTION get_fnd_profile_options_desc (p_profile_option_name VARCHAR2) RETURN VARCHAR2 IS
32 BEGIN
33 do_fnd_profile_options_tl(p_profile_option_name);
34 RETURN g_col1_2;
35 END get_fnd_profile_options_desc;
36
37 FUNCTION get_fnd_lookup_types_mean (p_lookup_type VARCHAR2, p_security_group_id NUMBER, p_view_application_id NUMBER) RETURN VARCHAR2 IS
38 BEGIN
39 do_fnd_lookup_types_tl(p_lookup_type, p_security_group_id, p_view_application_id);
40 RETURN g_col2_1;
41 END get_fnd_lookup_types_mean;
42
43 FUNCTION get_fnd_lookup_types_desc (p_lookup_type VARCHAR2, p_security_group_id NUMBER, p_view_application_id NUMBER) RETURN VARCHAR2 IS
44 BEGIN
45 do_fnd_lookup_types_tl(p_lookup_type, p_security_group_id, p_view_application_id);
46 RETURN g_col2_2;
47 END get_fnd_lookup_types_desc;
48
49 FUNCTION get_fnd_descriptive_flexs_titl (p_application_id VARCHAR2, p_descriptive_flexfield_name VARCHAR2) RETURN VARCHAR2 IS
50 BEGIN
51 do_fnd_descriptive_flexs_tl(p_application_id, p_descriptive_flexfield_name);
52 RETURN g_col3_1;
53 END get_fnd_descriptive_flexs_titl;
54
55 FUNCTION get_fnd_descriptive_flexs_prom (p_application_id VARCHAR2, p_descriptive_flexfield_name VARCHAR2) RETURN VARCHAR2 IS
56 BEGIN
57 do_fnd_descriptive_flexs_tl(p_application_id, p_descriptive_flexfield_name);
58 RETURN g_col3_2;
59 END get_fnd_descriptive_flexs_prom;
60
61 FUNCTION get_fnd_descriptive_flexs_desc (p_application_id VARCHAR2, p_descriptive_flexfield_name VARCHAR2) RETURN VARCHAR2 IS
62 BEGIN
63 do_fnd_descriptive_flexs_tl(p_application_id, p_descriptive_flexfield_name);
64 RETURN g_col3_3;
65 END get_fnd_descriptive_flexs_desc;
66
67 PROCEDURE do_fnd_profile_options_tl (p_profile_option_name VARCHAR2) IS
68 CURSOR c_profile_option IS
69 SELECT user_profile_option_name, description
70 FROM fnd_profile_options_tl
71 WHERE profile_option_name = p_profile_option_name
72 AND language = USERENV('LANG');
73 BEGIN
74 IF g_key1 IS NULL OR g_key1 <> p_profile_option_name THEN
75 g_col1_1 := NULL;
76 g_col1_2 := NULL;
77 OPEN c_profile_option;
78 FETCH c_profile_option INTO g_col1_1, g_col1_2;
79 CLOSE c_profile_option;
80 g_key1 := p_profile_option_name;
81 END IF;
82 END do_fnd_profile_options_tl;
83
84 PROCEDURE do_fnd_lookup_types_tl (p_lookup_type VARCHAR2, p_security_group_id NUMBER, p_view_application_id NUMBER) IS
85 CURSOR c_lookup_type IS
86 SELECT meaning, description
87 FROM fnd_lookup_types_tl
88 WHERE lookup_type = p_lookup_type
89 AND security_group_id = p_security_group_id
90 AND view_application_id = p_view_application_id
91 AND language = USERENV('LANG');
92 BEGIN
93 IF g_key2_1 IS NULL OR g_key2_1 <> p_lookup_type OR
94 g_key2_2 IS NULL OR g_key2_2 <> p_security_group_id OR
95 g_key2_3 IS NULL OR g_key2_3 <> p_view_application_id THEN
96 g_col2_1 := NULL;
97 g_col2_2 := NULL;
98 OPEN c_lookup_type;
99 FETCH c_lookup_type INTO g_col2_1, g_col2_2;
100 CLOSE c_lookup_type;
101 g_key2_1 := p_lookup_type;
102 g_key2_2 := p_security_group_id;
103 g_key2_3 := p_view_application_id;
104 END IF;
105 END do_fnd_lookup_types_tl;
106
107 PROCEDURE do_fnd_descriptive_flexs_tl (p_application_id NUMBER, p_descriptive_flexfield_name VARCHAR2) IS
108 CURSOR c_desc_flex IS
109 SELECT title, form_context_prompt, description
110 FROM fnd_descriptive_flexs_tl
111 WHERE application_id = p_application_id
112 AND descriptive_flexfield_name = p_descriptive_flexfield_name
113 AND language = USERENV('LANG');
114 BEGIN
115 IF g_key3_1 IS NULL OR g_key3_1 <> p_application_id OR
116 g_key3_2 IS NULL OR g_key3_2 <> p_descriptive_flexfield_name THEN
117 g_col3_1 := NULL;
118 g_col3_2 := NULL;
119 g_col3_3 := NULL;
120 OPEN c_desc_flex;
121 FETCH c_desc_flex INTO g_col3_1, g_col3_2, g_col3_3;
122 CLOSE c_desc_flex;
123 g_key3_1 := p_application_id;
124 g_key3_2 := p_descriptive_flexfield_name;
125 END IF;
126 END do_fnd_descriptive_flexs_tl;
127
128 END jtm_view;