1 PACKAGE BODY pay_frr_data_pump AS
2 /* $Header: pyfrrdpm.pkb 115.2 2003/04/09 06:56:36 scchakra noship $ */
3 --
4 ------------------------- get_source_element_type_id --------------------------
5 --
6 -- This is a private function and returns the element type id.
7 --
8 Function get_source_element_type_id
9 (p_element_name in varchar2
10 ,p_business_group_id in number
11 ,p_language_code in varchar2
12 )
13 return number is
14 --
15 l_element_type_id pay_element_types_f.element_type_id%type;
16 --
17 Begin
18 select et.element_type_id
19 into l_element_type_id
20 from pay_element_types_f et,
21 pay_element_types_f_tl et_tl
22 where et_tl.element_name = p_element_name
23 and (et.business_group_id + 0 = p_business_group_id
24 or (et.business_group_id is null
25 and et.legislation_code = hr_api.return_legislation_code(p_business_group_id)
26 ))
27 and et_tl.element_type_id = et.element_type_id
28 and et_tl.language = p_language_code;
29 --
30 return (l_element_type_id);
31 Exception
32 when others then
33 hr_data_pump.fail('get_source_element_type_id', sqlerrm, p_element_name,
34 p_business_group_id, p_language_code);
35 raise;
36 End get_source_element_type_id;
37 --
38 --------------------------- get_ass_status_typ_id -----------------------------
39 --
40 -- This is a private function and returns the assignment status type id.
41 --
42 Function get_ass_status_typ_id
43 (p_user_status in varchar2
44 ,p_business_group_id in number
45 ,p_language_code in varchar2
46 )
47 return number is
48 --
49 l_ass_status_typ_id per_ass_status_type_amends.assignment_status_type_id%type;
50 --
51 Begin
52 If p_user_status is not null
53 and (hr_general.decode_lookup('NAME_TRANSLATIONS','STANDARD')
54 <> p_user_status) then
55 --
56 select pas.assignment_status_type_id
57 into l_ass_status_typ_id
58 from per_assignment_status_types_tl pastl
59 ,per_assignment_status_types pas
60 ,per_ass_status_type_amends asta
61 where pas.assignment_status_type_id = pastl.assignment_status_type_id
62 and pastl.language = p_language_code
63 and nvl(asta.user_status,pastl.user_status) = p_user_status
64 and asta.assignment_status_type_id(+) = pas.assignment_status_type_id
65 and asta.business_group_id(+) = p_business_group_id
66 and nvl(pas.business_group_id,nvl(p_business_group_id,0))
67 = nvl(p_business_group_id,0)
68 and nvl(pas.legislation_code
69 ,nvl(hr_api.return_legislation_code(p_business_group_id),' '))
70 = nvl(hr_api.return_legislation_code(p_business_group_id),' ');
71 --
72 End If;
73 --
74 return(l_ass_status_typ_id);
75 Exception
76 when others then
77 hr_data_pump.fail('get_ass_status_typ_id', sqlerrm, p_user_status,
78 p_business_group_id, p_language_code);
79 raise;
80
81 End get_ass_status_typ_id;
82 --
83 ------------------------ get_status_processing_rule_id ------------------------
84 --
85 -- Returns the status processing rule id.
86 --
87 Function get_status_processing_rule_id
88 (p_source_element_name in varchar2
89 ,p_user_status in varchar2
90 ,p_effective_date in date
91 ,p_business_group_id in number
92 ,p_language_code in varchar2
93 )
94 return number is
95 --
96 l_element_type_id pay_status_processing_rules.element_type_id%type;
97 l_ass_status_typ_id pay_status_processing_rules.assignment_status_type_id%type;
98 l_spr_id pay_status_processing_rules.status_processing_rule_id%type;
99 --
100 Begin
101
102 l_element_type_id := get_source_element_type_id
103 (p_source_element_name
104 ,p_business_group_id
105 ,p_language_code
106 );
107 l_ass_status_typ_id := get_ass_status_typ_id
108 (p_user_status
109 ,p_business_group_id
110 ,p_language_code
111 );
112 Begin
113 select status_processing_rule_id
114 into l_spr_id
115 from pay_status_processing_rules_f spr
116 where spr.element_type_id = l_element_type_id
117 and p_effective_date between spr.effective_start_date
118 and spr.effective_end_date
119 and (spr.business_group_id + 0 = p_business_group_id
120 or (spr.business_group_id is null
121 and spr.legislation_code = hr_api.return_legislation_code(p_business_group_id)
122 ))
123 and nvl(spr.assignment_status_type_id,-1) = nvl(l_ass_status_typ_id,-1);
124 --
125 return(l_spr_id);
126 Exception
127 when others then
128 hr_data_pump.fail('get_status_processing_rule_id'
129 ,sqlerrm
130 ,p_source_element_name
131 ,p_user_status
132 ,p_effective_date
133 ,p_business_group_id
134 ,p_language_code);
135 raise;
136 End;
137 End get_status_processing_rule_id;
138 --
139 ----------------------------- get_element_type_id -----------------------------
140 --
141 -- Returns the element type id
142 --
143 Function get_element_type_id
144 (p_element_name in varchar2
145 ,p_business_group_id in number
146 ,p_language_code in varchar2
147 )
148 return number is
149 --
150 l_element_type_id pay_element_types_f.element_type_id%type;
151 --
152 Begin
153 l_element_type_id := get_source_element_type_id
154 (p_element_name
155 ,p_business_group_id
156 ,p_language_code
157 );
158 return (l_element_type_id);
159 Exception
160 when others then
161 hr_data_pump.fail('get_element_type_id', sqlerrm, p_element_name,
162 p_business_group_id, p_language_code);
163 raise;
164 End get_element_type_id;
165 --
166 ------------------------------ get_input_value_id -----------------------------
167 --
168 -- Returns the input value id.
169 --
170 Function get_input_value_id
171 (p_data_pump_always_call in varchar2
172 ,p_input_value_name in varchar2
173 ,p_source_element_name in varchar2
174 ,p_element_name in varchar2
175 ,p_result_rule_type in varchar2
176 ,p_business_group_id in number
177 ,p_effective_date in date
178 ,p_language_code in varchar2
179 )
180 return number is
181 --
182 l_input_value_id pay_input_values_f.input_value_id%type;
183 --
184 Begin
185 --
186 -- Specific parameters are NULL
187 --
188 If p_input_value_name is null or p_source_element_name is null
189 or p_result_rule_type is null then
190 return null;
191 End If;
192 --
193 -- Specific parameters have HR_API defaults
194 --
195 If p_input_value_name = hr_api.g_varchar2 or p_source_element_name = hr_api.g_varchar2
196 or p_result_rule_type = hr_api.g_varchar2 then
197 return hr_api.g_number;
198 End If;
199 --
200 If p_result_rule_type = 'D' then
201 l_input_value_id := hr_pump_get.get_input_value_id
202 (p_input_value_name
203 ,p_source_element_name
204 ,p_business_group_id
205 ,p_effective_date
206 ,p_language_code
207 );
208 Else
209 If p_element_name is null then
210 return null;
211 Elsif p_element_name = hr_api.g_varchar2 then
212 return hr_api.g_number;
213 Else
214 l_input_value_id := hr_pump_get.get_input_value_id
215 (p_input_value_name
216 ,p_element_name
217 ,p_business_group_id
218 ,p_effective_date
219 ,p_language_code
220 );
221 End If;
222 End If;
223 return (l_input_value_id);
224 --
225 Exception
226 when others then
227 hr_data_pump.fail('get_input_value_id'
228 ,sqlerrm
229 ,p_input_value_name
230 ,p_source_element_name
231 ,p_element_name
232 ,p_result_rule_type
233 ,p_business_group_id
234 ,p_effective_date
235 ,p_language_code);
236 raise;
237
238 End get_input_value_id;
239 --
240 ------------------------- get_formula_result_rule_ovn -------------------------
241 --
242 -- Returns the object version number of the formula result rule and requires a
243 -- user key.
244 --
245 Function get_formula_result_rule_ovn
246 (p_formula_result_rule_user_key in varchar2
247 ,p_effective_date in date
248 )
249 return number is
250 --
251 l_frr_ovn number;
252 --
253 Begin
254 select frr.object_version_number
255 into l_frr_ovn
256 from pay_formula_result_rules_f frr,
257 hr_pump_batch_line_user_keys key
258 where key.user_key_value = p_formula_result_rule_user_key
259 and frr.formula_result_rule_id = key.unique_key_id
260 and p_effective_date between frr.effective_start_date
261 and frr.effective_end_date;
262 --
263 return(l_frr_ovn);
264 exception
265 when others then
266 hr_data_pump.fail('get_formula_result_rule_ovn'
267 ,sqlerrm
268 ,p_formula_result_rule_user_key
269 ,p_effective_date);
270 raise;
271 End get_formula_result_rule_ovn;
272 --
273 -------------------------- get_formula_result_rule_id -------------------------
274 --
275 -- Returns a formula_result_rule_id and requires a user_key.
276 --
277 function get_formula_result_rule_id
278 (
279 p_formula_result_rule_user_key in varchar2
280 ) return number is
281 l_formula_result_rule_id number;
282 begin
283 l_formula_result_rule_id := pay_element_data_pump.user_key_to_id
284 (p_formula_result_rule_user_key);
285 return(l_formula_result_rule_id);
286 exception
287 when others then
288 hr_data_pump.fail('get_formula_result_rule_id', sqlerrm, p_formula_result_rule_user_key);
289 raise;
290 end get_formula_result_rule_id;
291 --
292 END pay_frr_data_pump;
[Home] [Help]