[Home] [Help]
PACKAGE BODY: APPS.HR_FORM_ITEM_INFO
Source
1 PACKAGE BODY hr_form_item_info
2 /* $Header: hrfiminf.pkb 115.4 2003/03/11 16:11:50 adhunter ship $ */
3 AS
4 --
5 -- Global variables
6 --
7 g_application_id fnd_application.application_id%TYPE;
8 g_form_id fnd_form.form_id%TYPE;
9 g_form_items t_form_items := t_form_items();
10 --
11 -- -----------------------------------------------------------------------------
12 -- |-------------------------------< form_items >------------------------------|
13 -- -----------------------------------------------------------------------------
14 FUNCTION form_items
15 (p_application_id IN fnd_application.application_id%TYPE
16 ,p_form_id IN fnd_form.form_id%TYPE
17 )
18 RETURN t_form_items
19 IS
20 --
21 CURSOR csr_form_items
22 (p_application_id IN fnd_application.application_id%TYPE
23 ,p_form_id IN fnd_form.form_id%TYPE
24 )
25 IS
26 SELECT fim.form_item_id
27 ,fim.full_item_name
28 ,fim.item_type
29 ,fcn.canvas_name
30 ,ftp.tab_page_name
31 ,fim.radio_button_name
32 ,itp.alignment
33 ,itp.bevel
34 ,itp.case_restriction
35 ,itptl.default_value
36 ,itp.enabled
37 ,itp.format_mask
38 ,itp.height
39 ,itp.information_formula_id
40 ,fim.full_item_name information_parameter_item1 --placeholder value only
41 ,fim.full_item_name information_parameter_item2 --placeholder value only
42 ,fim.full_item_name information_parameter_item3 --placeholder value only
43 ,fim.full_item_name information_parameter_item4 --placeholder value only
44 ,fim.full_item_name information_parameter_item5 --placeholder value only
45 -- ,ip1.full_item_name information_parameter_item1
46 -- ,ip2.full_item_name information_parameter_item2
47 -- ,ip3.full_item_name information_parameter_item3
48 -- ,ip4.full_item_name information_parameter_item4
49 -- ,ip5.full_item_name information_parameter_item5
50 ,itptl.information_prompt
51 ,itp.insert_allowed
52 ,itptl.label
53 ,itp.prompt_alignment_offset
54 ,itp.prompt_display_style
55 ,itp.prompt_edge
56 ,itp.prompt_edge_alignment
57 ,itp.prompt_edge_offset
58 ,itptl.prompt_text
59 ,itp.prompt_text_alignment
60 ,itp.query_allowed
61 ,itp.required
62 ,itptl.tooltip_text
63 ,itp.update_allowed
64 ,itp.validation_formula_id
65 ,fim.full_item_name validation_parameter_item1 --placeholder value only
66 ,fim.full_item_name validation_parameter_item2 --placeholder value only
67 ,fim.full_item_name validation_parameter_item3 --placeholder value only
68 ,fim.full_item_name validation_parameter_item4 --placeholder value only
69 ,fim.full_item_name validation_parameter_item5 --placeholder value only
70 -- ,vp1.full_item_name validation_parameter_item1
71 -- ,vp2.full_item_name validation_parameter_item2
72 -- ,vp3.full_item_name validation_parameter_item3
73 -- ,vp4.full_item_name validation_parameter_item4
74 -- ,vp5.full_item_name validation_parameter_item5
75 ,itp.visible
76 ,itp.width
77 ,itp.x_position
78 ,itp.y_position
79 ,itp.information_category
80 ,itp.information1
81 ,itp.information2
82 ,itp.information3
83 ,itp.information4
84 ,itp.information5
85 ,itp.information6
86 ,itp.information7
87 ,itp.information8
88 ,itp.information9
89 ,itp.information10
90 ,itp.information11
91 ,itp.information12
92 ,itp.information13
93 ,itp.information14
94 ,itp.information15
95 ,itp.information16
96 ,itp.information17
97 ,itp.information18
98 ,itp.information19
99 ,itp.information20
100 ,itp.information21
101 ,itp.information22
102 ,itp.information23
103 ,itp.information24
104 ,itp.information25
105 ,itp.information26
106 ,itp.information27
107 ,itp.information28
108 ,itp.information29
109 ,itp.information30
110 FROM
111 -- hr_form_items_b vp5
112 -- ,hr_form_items_b vp4
113 -- ,hr_form_items_b vp3
114 -- ,hr_form_items_b vp2
115 -- ,hr_form_items_b vp1
116 -- ,hr_form_items_b ip5
117 -- ,hr_form_items_b ip4
118 -- ,hr_form_items_b ip3
119 -- ,hr_form_items_b ip2
120 -- ,hr_form_items_b ip1
121 hr_form_tab_pages_b ftp
122 ,hr_form_canvases_b fcn
123 ,hr_form_items_b fim
124 ,hr_item_properties_b itp
125 ,hr_item_properties_tl itptl
126 WHERE
127 -- vp5.form_item_id (+) = fim.validation_parameter_item_id5
128 -- AND vp4.form_item_id (+) = fim.validation_parameter_item_id4
129 -- AND vp3.form_item_id (+) = fim.validation_parameter_item_id3
130 -- AND vp2.form_item_id (+) = fim.validation_parameter_item_id2
131 -- AND vp1.form_item_id (+) = fim.validation_parameter_item_id1
132 -- AND ip5.form_item_id (+) = fim.information_parameter_item_id5
133 -- AND ip4.form_item_id (+) = fim.information_parameter_item_id4
134 -- AND ip3.form_item_id (+) = fim.information_parameter_item_id3
135 -- AND ip2.form_item_id (+) = fim.information_parameter_item_id2
136 -- AND ip1.form_item_id (+) = fim.information_parameter_item_id1
137 itp.form_item_id (+) = fim.form_item_id
138 AND itptl.item_property_id (+) = itp.item_property_id
139 AND itptl.language (+) = userenv('LANG')
140 AND ftp.form_tab_page_id (+) = fim.form_tab_page_id
141 AND fcn.form_canvas_id = fim.form_canvas_id
142 AND fim.application_id = p_application_id
143 AND fim.form_id = p_form_id;
144
145 --
146 l_form_items t_form_items := t_form_items();
147 --
148 CURSOR csr_info_param_items(p_form_item_id number) IS
149 SELECT fi1.full_item_name
150 ,fi2.full_item_name
151 ,fi3.full_item_name
152 ,fi4.full_item_name
153 ,fi5.full_item_name
154 FROM hr_item_properties_b ip
155 ,hr_form_items_b fi1
156 ,hr_form_items_b fi2
157 ,hr_form_items_b fi3
158 ,hr_form_items_b fi4
159 ,hr_form_items_b fi5
160 WHERE ip.form_item_id = p_form_item_id
161 AND fi1.form_item_id (+) = ip.information_parameter_item_id1
162 AND fi2.form_item_id (+) = ip.information_parameter_item_id2
163 AND fi3.form_item_id (+) = ip.information_parameter_item_id3
164 AND fi4.form_item_id (+) = ip.information_parameter_item_id4
165 AND fi5.form_item_id (+) = ip.information_parameter_item_id5;
166 --
167 CURSOR csr_valid_param_items(p_form_item_id number) IS
168 SELECT fi1.full_item_name
169 ,fi2.full_item_name
170 ,fi3.full_item_name
171 ,fi4.full_item_name
172 ,fi5.full_item_name
173 FROM hr_item_properties_b ip
174 ,hr_form_items_b fi1
175 ,hr_form_items_b fi2
176 ,hr_form_items_b fi3
177 ,hr_form_items_b fi4
178 ,hr_form_items_b fi5
179 WHERE ip.form_item_id = p_form_item_id
180 AND fi1.form_item_id (+) = ip.validation_parameter_item_id1
181 AND fi2.form_item_id (+) = ip.validation_parameter_item_id2
182 AND fi3.form_item_id (+) = ip.validation_parameter_item_id3
183 AND fi4.form_item_id (+) = ip.validation_parameter_item_id4
184 AND fi5.form_item_id (+) = ip.validation_parameter_item_id5;
185 --
186 BEGIN
187 --
188 IF ( p_application_id = nvl(g_application_id,hr_api.g_number)
189 AND p_form_id = nvl(g_form_id,hr_api.g_number))
190 THEN
191 --
192 -- The form items have already been found with a previous call to this
193 -- function. Just return the global variable.
194 --
195 l_form_items := g_form_items;
196 --
197 ELSE
198 --
199 -- The identifiers are different to the previous call to this function, or
200 -- this is the first call to this function.
201 --
202 FOR l_form_item IN csr_form_items
203 (p_application_id => p_application_id
204 ,p_form_id => p_form_id
205 )
206 LOOP
207 l_form_items.EXTEND;
208 --
209 --added for 2832094: only retrieve the parameters if there is a formula
210 --
211 if l_form_item.information_formula_id is not null then
212 open csr_info_param_items(l_form_item.form_item_id);
213 fetch csr_info_param_items into l_form_item.information_parameter_item1
214 ,l_form_item.information_parameter_item2
215 ,l_form_item.information_parameter_item3
216 ,l_form_item.information_parameter_item4
217 ,l_form_item.information_parameter_item5;
218 close csr_info_param_items;
219 else
220 l_form_item.information_parameter_item1:=null;
221 l_form_item.information_parameter_item2:=null;
222 l_form_item.information_parameter_item3:=null;
223 l_form_item.information_parameter_item4:=null;
224 l_form_item.information_parameter_item5:=null;
225 end if;
226 if l_form_item.validation_formula_id is not null then
227 open csr_valid_param_items(l_form_item.form_item_id);
228 fetch csr_valid_param_items into l_form_item.validation_parameter_item1
229 ,l_form_item.validation_parameter_item2
230 ,l_form_item.validation_parameter_item3
231 ,l_form_item.validation_parameter_item4
232 ,l_form_item.validation_parameter_item5;
233 close csr_valid_param_items;
234 else
235 l_form_item.validation_parameter_item1:=null;
236 l_form_item.validation_parameter_item2:=null;
237 l_form_item.validation_parameter_item3:=null;
238 l_form_item.validation_parameter_item4:=null;
239 l_form_item.validation_parameter_item5:=null;
240 end if;
241 --
242 l_form_items(l_form_items.LAST) := l_form_item;
243 --
244 END LOOP;
245 --
246 -- Set the global variables so the values are available to the next call to
247 -- the function.
248 --
249 g_application_id := p_application_id;
250 g_form_id := p_form_id;
251 g_form_items := l_form_items;
252 --
253 END IF;
254 --
255 RETURN(l_form_items);
256 --
257 END form_items;
258 --
259 -- -----------------------------------------------------------------------------
260 -- |-----------------------------< full_item_name >----------------------------|
261 -- -----------------------------------------------------------------------------
262 FUNCTION full_item_name
263 (p_form_item_id IN hr_form_items_b.form_item_id%TYPE
264 )
265 RETURN hr_form_items_b.full_item_name%TYPE
266 IS
267 --
268 CURSOR csr_form_items
269 (p_form_item_id IN hr_form_items_b.form_item_id%TYPE
270 )
271 IS
272 SELECT fim.full_item_name
273 FROM hr_form_items_b fim
274 WHERE fim.form_item_id = p_form_item_id;
275 l_form_item csr_form_items%ROWTYPE;
276 --
277 BEGIN
278 --
279 IF (p_form_item_id IS NULL)
280 THEN
281 NULL;
282 ELSE
283 OPEN csr_form_items
284 (p_form_item_id => p_form_item_id
285 );
286 FETCH csr_form_items INTO l_form_item;
287 CLOSE csr_form_items;
288 END IF;
289 RETURN(l_form_item.full_item_name);
290 --
291 END full_item_name;
292 --
293 END hr_form_item_info;