1 PACKAGE BODY pay_user_table_data_pump AS
2 /* $Header: pyputdpm.pkb 115.0 2003/10/29 20:51 scchakra noship $ */
3 --
4 ------------------------------ get_user_table_id ------------------------------
5 --
6 -- This is a private function and returns the user table id.
7 --
8 function get_user_table_id
9 (p_user_table_user_key in varchar2)
10 return number is
11
12 l_user_table_id number;
13 begin
14 l_user_table_id := pay_element_data_pump.user_key_to_id
15 (p_user_table_user_key);
16 return(l_user_table_id);
17 exception
18 when others then
19 hr_data_pump.fail('get_user_table_id', sqlerrm, p_user_table_user_key);
20 raise;
21 end get_user_table_id;
22 --
23 ---------------------------- get_user_table_ovn ------------------------------
24 --
25 -- Returns the object version number of the user table and requires a
26 -- user key.
27 --
28 Function get_user_table_ovn
29 (p_user_table_user_key in varchar2)
30 return number is
31 --
32 l_user_table_ovn number;
33 l_user_table_id number;
34 --
35 begin
36 l_user_table_id := get_user_table_id
37 (p_user_table_user_key
38 );
39 select object_version_number
40 into l_user_table_ovn
41 from pay_user_tables
42 where user_table_id = l_user_table_id;
43 --
44 return(l_user_table_ovn);
45 exception
46 when others then
47 hr_data_pump.fail('get_user_table_ovn',
48 sqlerrm,
49 p_user_table_user_key
50 );
51 raise;
52 End get_user_table_ovn;
53 --
54 ---------------------------- get_user_column_ovn ------------------------------
55 --
56 -- Returns the object version number of the user column and requires a
57 -- user key.
58 --
59 Function get_user_column_ovn
60 (p_user_column_user_key in varchar2
61 )
62 return number is
63 --
64 l_user_column_ovn number;
65 l_user_column_id number;
66 --
67 begin
68 l_user_column_id := get_user_column_id
69 (p_user_column_user_key
70 );
71 select object_version_number
72 into l_user_column_ovn
73 from pay_user_columns
74 where user_column_id = l_user_column_id;
75 --
76 return(l_user_column_ovn);
77 exception
78 when others then
79 hr_data_pump.fail('get_user_column_ovn',
80 sqlerrm,
81 p_user_column_user_key
82 );
83 raise;
84 End get_user_column_ovn;
85 --
86 --------------------------- get_user_column_id --------------------------------
87 --
88 -- Returns a user_column_id and requires a user_key.
89 --
90 function get_user_column_id
91 (p_user_column_user_key in varchar2)
92 return number is
93
94 l_user_column_id number;
95 begin
96 l_user_column_id := pay_element_data_pump.user_key_to_id
97 (p_user_column_user_key);
98 return(l_user_column_id);
99 exception
100 when others then
101 hr_data_pump.fail('get_user_column_id', sqlerrm, p_user_column_user_key);
102 raise;
103 end get_user_column_id;
104 --
105 ----------------------------- get_formula_id ----------------------------------
106 --
107 -- Returns a formula_id.
108 --
109 function get_formula_id
110 (p_formula_name in varchar2,
111 p_business_group_id in number
112 ) return number IS
113
114 l_formula_id number ;
115
116 Begin
117
118 select distinct formula_id
119 into l_formula_id
120 from ff_formulas_f f1,
121 ff_formula_types f2
122 where f1.formula_type_id = f2.formula_type_id
123 and f2.formula_type_name = 'User Table Validation'
124 and (business_group_id + 0 = p_business_group_id
125 or (business_group_id is null
126 and legislation_code =
127 hr_api.return_legislation_code(p_business_group_id))
128 or (business_group_id is null
129 and legislation_code is null)
130 )
131 and upper(formula_name) = upper(p_formula_Name);
132
133 return(l_formula_id);
134
135 Exception
136 When OTHERS Then
137 hr_data_pump.fail('get_formula_id',
138 sqlerrm,
139 p_formula_name,
140 p_business_group_id);
141 RAISE;
142 End get_formula_id;
143 --
144 ----------------------------- get_user_row_id ---------------------------------
145 --
146 -- Returns a user_row_id and requires a user_key.
147 --
148 function get_user_row_id
149 (p_user_row_user_key in varchar2)
150 return number is
151
152 l_user_row_id number;
153 begin
154 l_user_row_id := pay_element_data_pump.user_key_to_id
155 (p_user_row_user_key);
156 return(l_user_row_id);
157 exception
158 when others then
159 hr_data_pump.fail('get_user_row_id', sqlerrm, p_user_row_user_key);
160 raise;
161 end get_user_row_id;
162 --
163 ----------------------------- get_user_row_ovn --------------------------------
164 --
165 -- Returns the object version number of the user row and requires a
166 -- user key.
167 --
168 Function get_user_row_ovn
169 (p_user_row_user_key in varchar2
170 ,p_effective_date in date
171 )
172 return number is
173 --
174 l_user_row_ovn number;
175 l_user_row_id number;
176 --
177 begin
178 l_user_row_id := get_user_row_id
179 (p_user_row_user_key
180 );
181 select object_version_number
182 into l_user_row_ovn
183 from pay_user_rows_f
184 where user_row_id = l_user_row_id
185 and p_effective_date between effective_start_date
186 and effective_end_date;
187 --
188 return(l_user_row_ovn);
189 exception
190 when others then
191 hr_data_pump.fail('get_user_row_ovn',
192 sqlerrm,
193 p_user_row_user_key
194 );
195 raise;
196 End get_user_row_ovn;
197 --
198 ------------------------- get_user_column_instance_id -------------------------
199 --
200 -- Returns a user_column_instance_id.
201 --
202 function get_user_column_instance_id
203 (p_user_column_user_key in varchar2
204 ,p_business_group_id in number
205 ,p_user_row_user_key in varchar2
206 ,p_effective_date in date
207 )
208 return number is
209
210 l_user_column_id number;
211 l_user_row_id number;
212 l_user_column_instance_id number;
213
214 begin
215 l_user_column_id := get_user_column_id
216 (p_user_column_user_key
217 );
218 l_user_row_id := get_user_row_id(p_user_row_user_key);
219
220 select user_column_instance_id
221 into l_user_column_instance_id
222 from pay_user_column_instances_f
223 where user_row_id = l_user_row_id
224 and user_column_id = l_user_column_id
225 and p_effective_date between effective_start_date
226 and effective_end_date
227 and (business_group_id + 0 = p_business_group_id
228 or (business_group_id is null
229 and legislation_code =
230 hr_api.return_legislation_code(p_business_group_id))
231 or (business_group_id is null
232 and legislation_code is null)
233 );
234 return(l_user_column_instance_id);
235 exception
236 when others then
237 hr_data_pump.fail('get_user_column_instance_id'
238 ,sqlerrm
239 ,p_user_column_user_key
240 ,p_business_group_id
241 ,p_user_row_user_key
242 ,p_effective_date
243 );
244 raise;
245 end get_user_column_instance_id;
246 --
247 ------------------------ get_user_column_instance_ovn -------------------------
248 --
249 -- Returns the object version number of the user column instance and requires a
250 -- user key.
251 --
252 function get_user_column_instance_ovn
253 (p_user_column_user_key in varchar2
254 ,p_business_group_id in number
255 ,p_user_row_user_key in varchar2
256 ,p_effective_date in date
257 )
258 return number is
259 --
260 l_user_column_instance_ovn number;
261 l_user_column_instance_id number;
262 --
263 begin
264 l_user_column_instance_id := get_user_column_instance_id
265 (p_user_column_user_key
266 ,p_business_group_id
267 ,p_user_row_user_key
268 ,p_effective_date);
269
270 select object_version_number
271 into l_user_column_instance_ovn
272 from pay_user_column_instances_f
273 where user_column_instance_id = l_user_column_instance_id
274 and p_effective_date between effective_start_date
275 and effective_end_date;
276 --
277 return(l_user_column_instance_ovn);
278 exception
279 when others then
280 hr_data_pump.fail('get_user_column_instance_ovn'
281 ,sqlerrm
282 ,p_user_column_user_key
283 ,p_business_group_id
284 ,p_user_row_user_key
285 ,p_effective_date
286 );
287 raise;
288 End get_user_column_instance_ovn;
289 --
290 END pay_user_table_data_pump;
[Home] [Help]