[Home] [Help]
PACKAGE BODY: APPS.HR_DESCR_FLEX_INFO
Source
1 PACKAGE BODY hr_descr_flex_info
2 /* $Header: hrdflinf.pkb 120.0 2005/05/30 23:38:42 appldev noship $ */
3 AS
4 --
5 -- Global variables
6 --
7 g_application_short_name1 fnd_application.application_short_name%TYPE;
8 g_descriptive_flexfield_name1 fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
9 g_field_name_prefix VARCHAR2(30);
10 g_default_context_field_name fnd_descriptive_flexs.default_context_field_name%TYPE;
11 g_context_column_name fnd_descriptive_flexs.context_column_name%TYPE;
12 g_application_id2 fnd_descriptive_flexs.application_id%TYPE;
13 g_descriptive_flexfield_name2 fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
14 g_segments t_segments := t_segments();
15 --
16 -- -----------------------------------------------------------------------------
17 -- |-----------------------< default_context_field_name >----------------------|
18 -- -----------------------------------------------------------------------------
19 FUNCTION default_context_field_name
20 (p_application_short_name IN fnd_application.application_short_name%TYPE
21 ,p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
22 ,p_field_name_prefix IN VARCHAR2 DEFAULT NULL
23 )
24 RETURN fnd_descriptive_flexs.default_context_field_name%TYPE
25 IS
26 --
27 -- Local cursors
28 --
29 CURSOR csr_descriptive_flexs
30 (p_application_short_name IN fnd_application.application_short_name%TYPE
31 ,p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
32 )
33 IS
34 SELECT dfl.default_context_field_name
35 ,dfl.context_column_name
36 FROM fnd_descriptive_flexs dfl
37 ,fnd_application app
38 WHERE dfl.application_id = app.application_id
39 AND app.application_short_name = p_application_short_name
40 AND dfl.descriptive_flexfield_name = p_descriptive_flexfield_name;
41 l_descriptive_flex csr_descriptive_flexs%ROWTYPE;
42 --
43 BEGIN
44 --
45 IF (p_application_short_name = g_application_short_name1)
46 AND (p_descriptive_flexfield_name = g_descriptive_flexfield_name1)
47 AND (NVL(p_field_name_prefix,hr_api.g_varchar2) = NVL(g_field_name_prefix,hr_api.g_varchar2))
48 THEN
49 --
50 -- The last default context field name cached was for this descriptive
51 -- flexfield, so no need to get it from the database again
52 --
53 NULL;
54 --
55 ELSE
56 --
57 -- The last default context field name cached was NOT for this descriptive
58 -- flexfield, so need to get it from the database and cache it
59 --
60 OPEN csr_descriptive_flexs
61 (p_application_short_name => p_application_short_name
62 ,p_descriptive_flexfield_name => p_descriptive_flexfield_name
63 );
64 FETCH csr_descriptive_flexs INTO l_descriptive_flex;
65 CLOSE csr_descriptive_flexs;
66 --
67 IF (l_descriptive_flex.default_context_field_name IS NOT NULL) THEN
68 g_default_context_field_name := l_descriptive_flex.default_context_field_name;
69 ELSIF (l_descriptive_flex.context_column_name IS NOT NULL) THEN
70 g_default_context_field_name := p_field_name_prefix||l_descriptive_flex.context_column_name;
71 ELSE
72 g_default_context_field_name := NULL;
73 END IF;
74 g_context_column_name := p_field_name_prefix||l_descriptive_flex.context_column_name;
75 --
76 g_application_short_name1 := p_application_short_name;
77 g_descriptive_flexfield_name1 := p_descriptive_flexfield_name;
78 g_field_name_prefix := p_field_name_prefix;
79 --
80 END IF;
81 --
82 RETURN(g_default_context_field_name);
83 --
84 END default_context_field_name;
85 --
86 -- -----------------------------------------------------------------------------
87 -- |--------------------------< context_column_name >--------------------------|
88 -- -----------------------------------------------------------------------------
89 FUNCTION context_column_name
90 (p_application_short_name IN fnd_application.application_short_name%TYPE
91 ,p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
92 ,p_field_name_prefix IN VARCHAR2 DEFAULT NULL
93 )
94 RETURN fnd_descriptive_flexs.context_column_name%TYPE
95 IS
96 --
97 -- Local cursors
98 --
99 CURSOR csr_descriptive_flexs
100 (p_application_short_name IN fnd_application.application_short_name%TYPE
101 ,p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
102 )
103 IS
104 SELECT dfl.default_context_field_name
105 ,dfl.context_column_name
106 FROM fnd_descriptive_flexs dfl
107 ,fnd_application app
108 WHERE dfl.application_id = app.application_id
109 AND app.application_short_name = p_application_short_name
110 AND dfl.descriptive_flexfield_name = p_descriptive_flexfield_name;
111 l_descriptive_flex csr_descriptive_flexs%ROWTYPE;
112 --
113 BEGIN
114 IF (p_application_short_name = g_application_short_name1)
115
116 AND (p_descriptive_flexfield_name = g_descriptive_flexfield_name1)
117 AND (NVL(p_field_name_prefix,hr_api.g_varchar2) = NVL(g_field_name_prefix,hr_api.g_varchar2))
118 THEN
119 --
120 -- The last context column name cached was for this descriptive flexfield
121 -- so no need to get it from the database again
122 --
123 NULL;
124 --
125 ELSE
126 --
127 -- The last default context field name cached was NOT for this descriptive
128 -- flexfield, so need to get it from the database and cache it
129 --
130 OPEN csr_descriptive_flexs
131 (p_application_short_name => p_application_short_name
132 ,p_descriptive_flexfield_name => p_descriptive_flexfield_name
133 );
134 FETCH csr_descriptive_flexs INTO l_descriptive_flex;
135 CLOSE csr_descriptive_flexs;
136 --
137 IF (l_descriptive_flex.default_context_field_name IS NOT NULL) THEN
138 g_default_context_field_name := l_descriptive_flex.default_context_field_name;
139 ELSIF (l_descriptive_flex.context_column_name IS NOT NULL) THEN
140 g_default_context_field_name := p_field_name_prefix||l_descriptive_flex.context_column_name;
141 ELSE
142 g_default_context_field_name := NULL;
143 END IF;
144 g_context_column_name := p_field_name_prefix||l_descriptive_flex.context_column_name;
145 --
146 g_application_short_name1 := p_application_short_name;
147 g_descriptive_flexfield_name1 := p_descriptive_flexfield_name;
148 g_field_name_prefix := p_field_name_prefix;
149 --
150 END IF;
151 --
152 RETURN(g_context_column_name);
153 --
154 END context_column_name;
155 --
156 -- -----------------------------------------------------------------------------
157 -- |--------------------------------< segments >-------------------------------|
158 -- -----------------------------------------------------------------------------
159 FUNCTION segments
160 (p_application_id IN fnd_descriptive_flexs.application_id%TYPE
161 ,p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
162 )
163 RETURN t_segments
164 IS
165 --
166 -- Local cursors
167 --
168 CURSOR csr_columns
169 (p_application_id IN fnd_descriptive_flexs.application_id%TYPE
170 ,p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
171 )
172 IS
173 SELECT col.column_name
174 FROM fnd_columns col
175 ,fnd_tables tbl
176 ,fnd_descriptive_flexs flx
177 WHERE col.flexfield_usage_code = 'D'
178 AND col.flexfield_application_id = flx.application_id
179 AND col.flexfield_name = flx.descriptive_flexfield_name
180 AND col.table_id = tbl.table_id
181 AND col.application_id = tbl.application_id
182 AND tbl.application_id = flx.table_application_id
183 AND tbl.table_name = flx.application_table_name
184 AND flx.application_id = p_application_id
185 AND flx.descriptive_flexfield_name = p_descriptive_flexfield_name;
186 --
187 -- Local variables
188 --
189 l_segments t_segments := t_segments();
190 --
191 BEGIN
192 IF (p_application_id = g_application_id2)
193 AND (p_descriptive_flexfield_name = g_descriptive_flexfield_name2)
194 THEN
195 --
196 l_segments := g_segments;
197 --
198 ELSE
199 --
200 FOR l_column in csr_columns
201 (p_application_id => p_application_id
202 ,p_descriptive_flexfield_name => p_descriptive_flexfield_name
203 )
204 LOOP
205 --
206 -- Add segment to table
207 --
208 l_segments.EXTEND;
209 l_segments(l_segments.LAST).column_name := l_column.column_name;
210 --
211 END LOOP;
212 --
213 g_application_id2 := p_application_id;
214 g_descriptive_flexfield_name2 := p_descriptive_flexfield_name;
215 g_segments := l_segments;
216 --
217 END IF;
218 --
219 RETURN(l_segments);
220 --
221 END segments;
222 --
223 END hr_descr_flex_info;