1 PACKAGE BODY HR_ENTRY_DISPLAY as
2 /* $Header: pyentdis.pkb 120.0 2005/05/29 04:36 appldev noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 hr_entry_display
11 Purpose
12 This package is used for maintaining entry level display utilities.
13
14 Notes
15 This was originally used for forms 2.3 usage and extended to more generic
16 usage.
17
18 Used by all 2.3 forms that display element entries. Element entries are
19 displayed horizontally to aid data entry and therefore 2.3 forms cannot
20 provide this without using special routines to fetch element entries which
21 are then displayed using a loop within the form NB. the 4.0 forms that
22 display element entries can use native 4.0 forms functionality to display
23 the entries horizontally.
24 History
25 04-Mar-94 J.S.Hobbs 40.0 Date created.
26 29-Jun-94 D.C.Harris 40.1 get_input_value_details element entry
27 values select tuned to use
28 PAY_ELEMENT_ENTRY_VALUES_F_N50 index.
29 25-Feb-99 J. Moyano 115.1 MLS changes. Procedure affected:
30 get_input_value_details.
31 16-Sep-04 T.Habara 115.2 Added original_entry_name().
32 ============================================================================*/
33 --
34 -----------------------------------------------------------------------------
35 -- Name --
36 -- original_entry_name --
37 -- Purpose --
38 -- This function is used for displaying the original entry name for --
39 -- the specified element entry id. --
40 -- --
41 -- Arguments --
42 -- See below. --
43 -- Notes --
44 -- None. --
45 -----------------------------------------------------------------------------
46 --
47 FUNCTION original_entry_name
48 (p_original_entry_id in number
49 ) return varchar2
50 is
51 l_element_name pay_element_types_f_tl.element_name%type;
52 --
53 cursor csr_element_name
54 is
55 select
56 pettl.element_name
57 from
58 pay_element_entries_f pee
59 ,pay_element_links_f pel
60 ,pay_element_types_f_tl pettl
61 where
62 pee.element_entry_id = p_original_entry_id
63 and pel.element_link_id = pee.element_link_id
64 and pee.effective_start_date between pel.effective_start_date
65 and pel.effective_end_date
66 and pettl.element_type_id = pel.element_type_id
67 and pettl.language = userenv('lang')
68 ;
69 begin
70 --
71 if p_original_entry_id is null then
72 return null;
73 end if;
74
75 --
76 -- Identify the element name for the entry.
77 --
78 open csr_element_name;
79 fetch csr_element_name into l_element_name;
80 close csr_element_name;
81
82 --
83 -- Return the entry name as "<Element Name> (<Entry ID>)".
84 --
85 if l_element_name is not null then
86 return (l_element_name || ' (' || p_original_entry_id || ')');
87 else
88 return null;
89 end if;
90 --
91 end original_entry_name;
92 --
93 -----------------------------------------------------------------------------
94 -- Name --
95 -- count_input_values (obsolete) --
96 -- Purpose --
97 -- This procedure is used for entry form(s) population. It counts how --
98 -- many input values are defined for the specified element type and --
99 -- also set a loop counter value to 1. --
100 -- Arguments --
101 -- See below. --
102 -- Notes --
103 -- This is no longer used. --
104 -----------------------------------------------------------------------------
105 --
106 --
107 PROCEDURE count_input_values
108 (
109 p_element_type_id in number,
110 p_session_date in date,
111 p_number_of_input_values out nocopy number,
112 p_population_loop_counter out nocopy number
113 ) is
114 --
115 begin
116 --
117 hr_utility.set_location('hr_entry_display.count_input_values', 1);
118 --
119 -- Need to determine how many input values have been defined for the element.
120 -- This value is used to control a loop which is used to select each input
121 -- value definition in the correct order a row at a time.
122 begin
123 SELECT COUNT(*)
124 INTO p_number_of_input_values
125 FROM PAY_INPUT_VALUES_F IV
126 WHERE IV.ELEMENT_TYPE_ID = p_element_type_id
127 AND p_session_date
128 BETWEEN IV.EFFECTIVE_START_DATE AND IV.EFFECTIVE_END_DATE;
129 end;
130 --
131 -- Initialise the loop counter.
132 p_population_loop_counter := 1;
133 --
134 end count_input_values;
135 --
136 -----------------------------------------------------------------------------
137 -- Name --
138 -- get_input_value_details (obsolete) --
139 -- Purpose --
140 -- This procedure is used for selecting input value details and --
141 -- assocated entry values for the entry forms(s). --
142 -- Arguments --
143 -- See below. --
144 -- Notes --
145 -- This is no longer used. --
146 -----------------------------------------------------------------------------
147 --
148 PROCEDURE get_input_value_details
149 (
150 p_element_type_id in number,
151 p_element_link_id in number,
152 p_session_date in date,
153 p_input_currency_code in varchar2,
154 p_input_value_id1 in number,
155 p_input_value_id2 in number,
156 p_input_value_id3 in number,
157 p_input_value_id4 in number,
158 p_input_value_id5 in number,
159 p_input_value_id6 in number,
160 p_element_entry_id in number,
161 p_input_value_id out nocopy number,
162 p_input_name out nocopy varchar2,
163 p_default_value out nocopy varchar2,
164 p_mandatory_flag out nocopy varchar2,
165 p_uom out nocopy varchar2,
166 p_warning_or_error out nocopy varchar2,
167 p_hot_default_flag out nocopy varchar2,
168 p_lookup_type out nocopy varchar2,
169 p_formula_id out nocopy number,
170 p_database_format_value out nocopy varchar2,
171 p_screen_format_value out nocopy varchar2
172 ) is
173 --
174 v_uom varchar2(30);
175 v_select_input_value_id number;
176 v_screen_format_value varchar2(60);
177 v_database_format_value varchar2(60);
178 v_hot_default_flag varchar2(30);
179 v_lookup_type varchar2(30);
180 --
181 begin
182 --
183 hr_utility.set_location('hr_entry_display.get_input_value_details', 1);
184 --
185 -- Select the input_value_id of the required input value as specified
186 -- by the ordering criteria.
187 -- The inner sub-query works by selecting the minimum order sequence of
188 -- any input values which have not already been selected. Because the
189 -- DISPLAY_SEQUENCE is optional, the parameter is defaulted to 0 to be used
190 -- to ensure that a row is always returned from the inner query.
191 -- The outer query will then select the minimum input value for the
192 -- order sequence which has not already been selected.
193 begin
194 SELECT HIV1.INPUT_VALUE_ID,
195 HIV1_TL.NAME,
196 HIV1.MANDATORY_FLAG,
197 HIV1.UOM,
198 DECODE(HIV1.HOT_DEFAULT_FLAG,'Y',
199 NVL(HLIV1.WARNING_OR_ERROR,HIV1.WARNING_OR_ERROR),
200 HIV1.WARNING_OR_ERROR),
201 HIV1.HOT_DEFAULT_FLAG,
202 HIV1.LOOKUP_TYPE,
203 HIV1.FORMULA_ID
204 INTO v_select_input_value_id,
205 p_input_name,
206 p_mandatory_flag,
207 v_uom,
208 p_warning_or_error,
209 v_hot_default_flag,
210 v_lookup_type,
211 p_formula_id
212 FROM PAY_INPUT_VALUES_F_TL HIV1_TL,
213 PAY_INPUT_VALUES_F HIV1,
214 PAY_LINK_INPUT_VALUES_F HLIV1
215 WHERE HIV1_TL.INPUT_VALUE_ID = HIV1.INPUT_VALUE_ID
216 and userenv('LANG') = HIV1_TL.language
217 AND p_session_date
218 BETWEEN HLIV1.EFFECTIVE_START_DATE
219 AND HLIV1.EFFECTIVE_END_DATE
220 AND HLIV1.INPUT_VALUE_ID = HIV1.INPUT_VALUE_ID
221 AND HLIV1.ELEMENT_LINK_ID = p_element_link_id
222 AND HIV1.ROWID =
223 (SELECT MIN(IV1.ROWID)
224 FROM PAY_INPUT_VALUES_F IV1
225 WHERE IV1.ELEMENT_TYPE_ID = p_element_type_id
226 AND p_session_date
227 BETWEEN IV1.EFFECTIVE_START_DATE
228 AND IV1.EFFECTIVE_END_DATE
229 AND IV1.INPUT_VALUE_ID NOT IN
230 (NVL(p_input_value_id1,0),
231 NVL(p_input_value_id2,0),
232 NVL(p_input_value_id3,0),
233 NVL(p_input_value_id4,0),
234 NVL(p_input_value_id5,0),
235 NVL(p_input_value_id6,0))
236 AND IV1.DISPLAY_SEQUENCE =
237 (SELECT MIN(IV2.DISPLAY_SEQUENCE)
238 FROM PAY_INPUT_VALUES_F IV2
239 WHERE IV2.ELEMENT_TYPE_ID = p_element_type_id
240 AND p_session_date
241 BETWEEN IV2.EFFECTIVE_START_DATE
242 AND IV2.EFFECTIVE_END_DATE
243 AND IV2.INPUT_VALUE_ID NOT IN
244 (NVL(p_input_value_id1,0),
245 NVL(p_input_value_id2,0),
246 NVL(p_input_value_id3,0),
247 NVL(p_input_value_id4,0),
248 NVL(p_input_value_id5,0),
249 NVL(p_input_value_id6,0))));
250 exception
251 when NO_DATA_FOUND then
252 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
253 hr_utility.set_message_token('PROCEDURE',
254 'hr_entry_display.get_input_value_details');
255 hr_utility.set_message_token('STEP','2');
256 hr_utility.raise_error;
257 end;
258 --
259 p_input_value_id := v_select_input_value_id;
260 p_hot_default_flag := v_hot_default_flag;
261 p_uom := v_uom;
262 p_lookup_type := v_lookup_type;
263 --
264 -- Now that we have select the correct input_value_id and the input value
265 -- details the entry value can now be selected provided the element entry id
266 -- exists.
267 if p_element_entry_id is not null then
268 --
269 hr_utility.set_location('hr_entry_display.get_input_value_details', 3);
270 begin
271 SELECT HEEV1.SCREEN_ENTRY_VALUE
272 INTO v_database_format_value
273 FROM PAY_ELEMENT_ENTRY_VALUES_F HEEV1
274 WHERE HEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
275 AND HEEV1.INPUT_VALUE_ID + 0 = v_select_input_value_id
276 AND p_session_date
277 BETWEEN HEEV1.EFFECTIVE_START_DATE AND HEEV1.EFFECTIVE_END_DATE;
278 exception
279 when NO_DATA_FOUND then
280 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
281 hr_utility.set_message_token('PROCEDURE',
282 'hr_entry_display.get_input_value_details');
283 hr_utility.set_message_token('STEP','3');
284 hr_utility.raise_error;
285 end;
286 end if;
287 --
288 -- derive default value.
289 -- If the entry value is being selected then
290 -- If the entry value is null and hot defaulted then derived default
291 -- Else format selected entry value.
292 -- Else
293 -- derive defaults.
294 if p_element_entry_id is not null then
295 --
296 if (v_hot_default_flag = 'Y' and
297 v_database_format_value is null) then
298 --
299 hr_entry.derive_default_value
300 (p_element_link_id,
301 v_select_input_value_id,
302 p_session_date,
303 p_input_currency_code,
304 'DEF',
305 v_screen_format_value,
306 v_database_format_value);
307 --
308 -- As the entry value is not using a default we must get the screen format
309 -- value.
310 elsif v_database_format_value is not null then
311 --
312 if v_lookup_type is not null then
313 --
314 hr_utility.set_location('hr_entry_display.get_input_value_details', 4);
315 begin
316 select h.meaning
317 into v_screen_format_value
318 from hr_lookups h
319 where h.lookup_type = v_lookup_type
320 and h.lookup_code = v_database_format_value;
321 exception
322 when NO_DATA_FOUND then
323 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
324 hr_utility.set_message_token('PROCEDURE',
325 'hr_entry_display.get_input_value_details');
326 hr_utility.set_message_token('STEP','4');
327 hr_utility.raise_error;
328 end;
329 --
330 else
331 --
332 hr_chkfmt.changeformat
333 (v_database_format_value,
334 v_screen_format_value,
335 v_uom,
336 p_input_currency_code);
337 --
338 end if;
339 --
340 end if;
341 --
342 else
343 --
344 -- As we are not selecting entry values then we must select the default
345 -- value.
346 hr_entry.derive_default_value
347 (p_element_link_id,
348 v_select_input_value_id,
349 p_session_date,
350 p_input_currency_code,
351 'DEF',
352 v_screen_format_value,
353 v_database_format_value);
354 --
355 end if;
356 --
357 -- Set the p_screen_format_value, p_database_format_value
358 p_screen_format_value := v_screen_format_value;
359 p_database_format_value := v_database_format_value;
360 --
361 end get_input_value_details;
362 --
363 END HR_ENTRY_DISPLAY;
[Home] [Help]