1 package body PAY_PAYWIEEH_PKG as
2 /* $Header: paywieeh.pkb 115.1 99/07/17 05:40:01 porting ship $ */
3 --
4 --
5 --
6 --
7 --------------------------------------------------------------------------------
8 /*
9 +==============================================================================+
10 | Copyright (c) 1994 Oracle Corporation |
11 | Redwood Shores, California, USA |
12 | All rights reserved. |
13 +==============================================================================+
14 Name
15 Element Entry History Form Package
16 Purpose
17 To provide information required by the PAYWIEEH form
18 History
19 --
20 25 Oct 94 N Simpson Created
21 40.1 24 Nov 94 R Fine Suppressed index on business_group_id
22 ------??-----
23 40.5 26 Jun 97 M.Lisiecki Changed GET_ENTRY_VALUE_DETAILS
24 cursor set_of_entry_values to improve
25 performance.
26 115.1 23 Feb 99 J. Moyano MLS changes
27 */
28 --------------------------------------------------------------------------------
29 procedure GET_ENTRY_VALUE_DETAILS (
30 --
31 -- Returns the element entry values along with all their inherited properties
32 -- for each element entry selected by a query in the form
33 --
34 p_element_entry_id number,
35 p_element_link_id number,
36 p_effective_date date,
37 p_name1 in out varchar2,
38 p_name2 in out varchar2,
39 p_name3 in out varchar2,
40 p_name4 in out varchar2,
41 p_name5 in out varchar2,
42 p_name6 in out varchar2,
43 p_name7 in out varchar2,
44 p_name8 in out varchar2,
45 p_name9 in out varchar2,
46 p_name10 in out varchar2,
47 p_name11 in out varchar2,
48 p_name12 in out varchar2,
49 p_name13 in out varchar2,
50 p_name14 in out varchar2,
51 p_name15 in out varchar2,
52 p_uom1 in out varchar2,
53 p_uom2 in out varchar2,
54 p_uom3 in out varchar2,
55 p_uom4 in out varchar2,
56 p_uom5 in out varchar2,
57 p_uom6 in out varchar2,
58 p_uom7 in out varchar2,
59 p_uom8 in out varchar2,
60 p_uom9 in out varchar2,
61 p_uom10 in out varchar2,
62 p_uom11 in out varchar2,
63 p_uom12 in out varchar2,
64 p_uom13 in out varchar2,
65 p_uom14 in out varchar2,
66 p_uom15 in out varchar2,
67 p_screen_entry_value1 in out varchar2,
68 p_screen_entry_value2 in out varchar2,
69 p_screen_entry_value3 in out varchar2,
70 p_screen_entry_value4 in out varchar2,
71 p_screen_entry_value5 in out varchar2,
72 p_screen_entry_value6 in out varchar2,
73 p_screen_entry_value7 in out varchar2,
74 p_screen_entry_value8 in out varchar2,
75 p_screen_entry_value9 in out varchar2,
76 p_screen_entry_value10 in out varchar2,
77 p_screen_entry_value11 in out varchar2,
78 p_screen_entry_value12 in out varchar2,
79 p_screen_entry_value13 in out varchar2,
80 p_screen_entry_value14 in out varchar2,
81 p_screen_entry_value15 in out varchar2) is
82 --
83 -- Bug 510150. Changed cursor's from clause to prevent it from using
84 -- pay_input_values_f as a driving table and prevented usage of index
85 -- PAY_LINK_INPUT_VALUES_F_N2 iby adding 0 to the link.input_value_id
86 -- column, to improve performance.26-Jun-1997.mlisieck.
87
88 cursor SET_OF_ENTRY_VALUES is
89 select type_tl.name,
90 type.uom,
91 decode (type.lookup_type,
92 null, decode (type.hot_default_flag,
93 'N', entry.screen_entry_value,
94 'Y', nvl (entry.screen_entry_value, nvl (link.default_value, type.default_value))),
95 hr_general.decode_lookup (type.lookup_type,
96 decode (type.hot_default_flag,
97 'N', entry.screen_entry_value,
98 'Y', nvl (entry.screen_entry_value,
99 nvl (link.default_value,
100 type.default_value))))) SCREEN_ENTRY_VALUE
101 from pay_input_values_f_tl TYPE_TL,
102 pay_input_values_f TYPE,
103 pay_element_entry_values_f ENTRY,
104 pay_link_input_values_f LINK
105 where link.element_link_id = p_element_link_id
106 and entry.element_entry_id = p_element_entry_id
107 and link.input_value_id = entry.input_value_id + 0
108 and type.input_value_id = entry.input_value_id
109 and type_tl.input_value_id = type.input_value_id
110 and userenv('LANG') = type_tl.language
111 and p_effective_date = entry.effective_start_date
112 and entry.effective_start_date between link.effective_start_date
113 and link.effective_end_date
114 and entry.effective_start_date between type.effective_start_date
115 and type.effective_end_date
116 order by type.display_sequence, type_tl.name;
117 --
118 entry_value_number integer;
119 --
120 begin
121 --
122 -- Retrieve all the existing element entry values for the element entry
123 --
124 for fetched_entry_value in set_of_entry_values LOOP
125 --
126 entry_value_number := set_of_entry_values%rowcount; -- loop index flag
127 --
128 if entry_value_number = 1 then
129 --
130 p_uom1 := fetched_entry_value.uom;
131 p_name1 := fetched_entry_value.name;
132 p_screen_entry_value1 := fetched_entry_value.screen_entry_value;
133 --
134 elsif entry_value_number = 2 then
135 --
136 p_uom2 := fetched_entry_value.uom;
137 p_name2 := fetched_entry_value.name;
138 p_screen_entry_value2 := fetched_entry_value.screen_entry_value;
139 --
140 elsif entry_value_number = 3 then
141 --
142 p_uom3 := fetched_entry_value.uom;
143 p_name3 := fetched_entry_value.name;
144 p_screen_entry_value3 := fetched_entry_value.screen_entry_value;
145 --
146 elsif entry_value_number = 4 then
147 --
148 p_uom4 := fetched_entry_value.uom;
149 p_name4 := fetched_entry_value.name;
150 p_screen_entry_value4 := fetched_entry_value.screen_entry_value;
151 --
152 elsif entry_value_number = 5 then
153 --
154 p_uom5 := fetched_entry_value.uom;
155 p_name5 := fetched_entry_value.name;
156 p_screen_entry_value5 := fetched_entry_value.screen_entry_value;
157 --
158 elsif entry_value_number = 6 then
159 --
160 p_uom6 := fetched_entry_value.uom;
161 p_name6 := fetched_entry_value.name;
162 p_screen_entry_value6 := fetched_entry_value.screen_entry_value;
163 --
164 elsif entry_value_number = 7 then
165 --
166 p_uom7 := fetched_entry_value.uom;
167 p_name7 := fetched_entry_value.name;
168 p_screen_entry_value7 := fetched_entry_value.screen_entry_value;
169 --
170 elsif entry_value_number = 8 then
171 --
172 p_uom8 := fetched_entry_value.uom;
173 p_name8 := fetched_entry_value.name;
174 p_screen_entry_value8 := fetched_entry_value.screen_entry_value;
175 --
176 elsif entry_value_number = 9 then
177 --
178 p_uom9 := fetched_entry_value.uom;
179 p_name9 := fetched_entry_value.name;
180 p_screen_entry_value9 := fetched_entry_value.screen_entry_value;
181 --
182 elsif entry_value_number = 10 then
183 --
184 p_uom10 := fetched_entry_value.uom;
185 p_name10 := fetched_entry_value.name;
186 p_screen_entry_value10 := fetched_entry_value.screen_entry_value;
187 --
188 elsif entry_value_number = 11 then
189 --
190 p_uom11 := fetched_entry_value.uom;
191 p_name11 := fetched_entry_value.name;
192 p_screen_entry_value11 := fetched_entry_value.screen_entry_value;
193 --
194 elsif entry_value_number = 12 then
195 --
196 p_uom12 := fetched_entry_value.uom;
197 p_name12 := fetched_entry_value.name;
198 p_screen_entry_value12 := fetched_entry_value.screen_entry_value;
199 --
200 elsif entry_value_number = 13 then
201 --
202 p_uom13 := fetched_entry_value.uom;
203 p_name13 := fetched_entry_value.name;
204 p_screen_entry_value13 := fetched_entry_value.screen_entry_value;
205 --
206 elsif entry_value_number = 14 then
207 --
208 p_uom14 := fetched_entry_value.uom;
209 p_name14 := fetched_entry_value.name;
210 p_screen_entry_value14 := fetched_entry_value.screen_entry_value;
211 --
212 elsif entry_value_number = 15 then
213 --
214 p_uom15 := fetched_entry_value.uom;
215 p_name15 := fetched_entry_value.name;
216 p_screen_entry_value15 := fetched_entry_value.screen_entry_value;
217 --
218 end if;
219 --
220 end loop;
221 --
222 end get_entry_value_details;
223 --------------------------------------------------------------------------------
224 procedure populate_context_items (
225 --
226 --******************************************************************************
227 -- Populate form initialisation information
228 --******************************************************************************
229 --
230 p_business_group_id in number, -- User's business group
231 p_cost_allocation_structure in out varchar2-- Keyflex structure
232 ) is
233 --
234 -- Define how to retrieve Keyflex structure information
235 --
236 cursor keyflex_structure is
237 select cost_allocation_structure
238 from per_business_groups_perf
239 where business_group_id + 0 = p_business_group_id;
240 --
241 begin
242 --
243 -- Fetch Keyflex information
244 --
245 open keyflex_structure;
246 fetch keyflex_structure into p_cost_allocation_structure;
247 close keyflex_structure;
248 --
249 end populate_context_items;
250 --------------------------------------------------------------------------------
251 end PAY_PAYWIEEH_PKG;