[Home] [Help]
PACKAGE BODY: APPS.PAY_PROCESSING_RULE_DATA_PUMP
Source
1 PACKAGE BODY pay_processing_rule_data_pump AS
2 /* $Header: pypprdpm.pkb 115.10 2004/02/25 21:48:19 adkumar noship $ */
3
4 ------------------------------ user_key_to_id ---------------------------------
5 --
6 -- Returns an ID value from hr_pump_batch_line_user_keys alone.
7 -- Utility function to get _ID functions.
8 --
9 Function user_key_to_id(p_user_key_value in varchar2)
10 return number is
11 l_id number;
12 Begin
13 select unique_key_id
14 into l_id
15 from hr_pump_batch_line_user_keys
16 where user_key_value = p_user_key_value;
17 return(l_id);
18 End user_key_to_id;
19 --
20
21 ----------------------------- get_element_type_id -----------------------------
22 --
23 -- Returns the element type id
24 --
25 Function get_element_type_id
26 (p_element_name in varchar2
27 ,p_business_group_id in number
28 ,p_language_code in varchar2
29 ,p_effective_date in date
30 )
31 return number is
32 --
33 l_element_type_id pay_element_types_f.element_type_id%type;
34 --
35 Begin
36
37
38 select et.element_type_id
39 into l_element_type_id
40 from pay_element_types_f et,
41 pay_element_types_f_tl et_tl
42 where et_tl.element_type_id = et.element_type_id
43 and et_tl.element_name = p_element_name
44 and p_effective_date between et.effective_start_date and
45 et.effective_end_date
46 and nvl(legislation_code,
47 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')) =
48 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
49 and nvl(business_group_id,nvl(p_business_group_id,-1)) =
50 nvl(p_business_group_id,-1)
51 and et_tl.language = p_language_code;
52 --
53 return (l_element_type_id);
54 Exception
55 when others then
56 hr_data_pump.fail('get_element_type_id', sqlerrm, p_element_name,
57 p_business_group_id, p_language_code,p_effective_date);
58 raise;
59 End get_element_type_id;
60 --
61 ------------------------------get_formula_id------------------------------------
62 --
63 -- Return the formula id.
64 --
65
66 Function get_formula_id( p_formula_Name in varchar2,
67 p_business_group_id in number
68 ) return number is
69 l_formula_id number ;
70
71 Begin
72
73 Select distinct formula_id
74 into l_formula_id
75 From ff_formulas_f f1,
76 ff_formula_types f2
77 Where f2.formula_type_id = f1.formula_type_id
78 and f2.formula_type_name in('Oracle Payroll' , 'Balance Adjustment')
79 and UPPER(f1.formula_name) = UPPER(p_formula_Name)
80 and nvl(f1.legislation_code,
81 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')) =
82 nvl(HR_API.RETURN_LEGISLATION_CODE(p_business_group_id),'~~nvl~~')
83 and nvl(f1.business_group_id,nvl(p_business_group_id,-1)) =
84 nvl(p_business_group_id,-1) ;
85
86
87 RETURN(l_formula_id);
88
89 Exception
90 When OTHERS Then
91 hr_data_pump.fail('get_formula_id', sqlerrm, p_formula_Name,
92 p_business_group_id);
93 RAISE;
94 End get_formula_id;
95 --
96 --
97 --------------------------- get_assignment_status_type_id -----------------------------
98 --
99 -- Returns the assignment status type id.
100 --
101 Function get_assignment_status_type_id
102 (p_assignment_status in varchar2
103 ,p_business_group_id in number
104 ,p_language_code in varchar2
105 ,p_effective_date in date
106 )
107 return number is
108 --
109 l_ass_status_typ_id per_assignment_status_types.assignment_status_type_id%type;
110
111 invalid_status_stnd exception;
112 invalid_status_badjust exception;
113 l_message varchar2(10000);
114 l_lookup hr_lookups.lookup_code%type;
115 l_meaning hr_lookups.meaning%type;
116 l_standard_lookup hr_lookups.meaning%type;
117 l_balance_lookup hr_lookups.meaning%type;
118
119 cursor c_lookup_type is
120 Select lookup_code, meaning
121 from hr_lookups
122 Where lookup_type = 'NAME_TRANSLATIONS'
123 and lookup_code in ('STANDARD','BAL_ADJUST');
124
125 Begin
126 --
127 open c_lookup_type;
128 loop
129 fetch c_lookup_type into l_lookup,l_meaning;
130 exit when c_lookup_type%notfound;
131 if l_lookup = 'STANDARD' then
132 l_standard_lookup := l_meaning;
133 elsif l_lookup = 'BAL_ADJUST' then
134 l_balance_lookup := l_meaning;
135 end if;
136 end loop;
137 close c_lookup_type;
138
139 --
140 if (UPPER(p_assignment_status) = nvl(UPPER(l_standard_lookup),'-1')) then
141 raise invalid_status_stnd;
142 elsif (UPPER(p_assignment_status) = nvl(UPPER(l_balance_lookup),'-1')) then
143 raise invalid_status_badjust;
144 else
145 select astp.assignment_status_type_id
146 into l_ass_status_typ_id
147 from per_assignment_status_types_tl astpl,
148 per_assignment_status_types astp
149 where astpl.assignment_status_type_id = astp.assignment_status_type_id
150 and UPPER(astpl.user_status) = UPPER(p_assignment_status)
151 and nvl(astp.business_group_id,nvl(p_business_group_id,0))
152 = nvl(p_business_group_id,0)
153 and nvl(astp.legislation_code
154 ,nvl(hr_api.return_legislation_code(p_business_group_id),' '))
155 = nvl(hr_api.return_legislation_code(p_business_group_id),' ');
156 --
157 return(l_ass_status_typ_id);
158 end if;
159 --
160 Exception
161 when invalid_status_stnd then
162 fnd_message.set_name('PAY', 'PAY_33697_SPR_STND_MISMATCH');
163 l_message := fnd_message.get;
164 hr_data_pump.fail('get_assignment_status_type_id', l_message);
165 raise;
166 when invalid_status_badjust then
167 fnd_message.set_name('PAY', 'PAY_33698_SPR_BADJUST_MISMATCH');
168 l_message := fnd_message.get;
169 hr_data_pump.fail('get_assignment_status_type_id', l_message);
170 raise;
171 when others then
172 hr_data_pump.fail('get_assignment_status_type_id', sqlerrm, p_assignment_status,
173 p_business_group_id, p_language_code,p_effective_date);
174 raise;
175
176 End get_assignment_status_type_id;
177 --
178 ------------------------- get_status_processing_rule_ovn -------------------------
179 --
180 -- Returns the object version number of the status processing rule and requires a
181 -- user key.
182 --
183 Function get_status_processing_rule_ovn
184 (p_status_process_rule_user_key in varchar2
185 ,p_effective_date in date
186 )
187 return number is
188 --
189 l_spr_ovn number;
190 --
191 Begin
192 select spr.object_version_number
193 into l_spr_ovn
194 from pay_status_processing_rules_f spr,
195 hr_pump_batch_line_user_keys key
196 where key.user_key_value = p_status_process_rule_user_key
197 and spr.status_processing_rule_id = key.unique_key_id
198 and p_effective_date between spr.effective_start_date
199 and spr.effective_end_date;
200 --
201 return(l_spr_ovn);
202 exception
203 when others then
204 hr_data_pump.fail('get_status_processing_rule_ovn'
205 ,sqlerrm
206 ,p_status_process_rule_user_key
207 ,p_effective_date);
208 raise;
209 End get_status_processing_rule_ovn;
210 --
211 -------------------------- get_status_processing_rule_id -------------------------
212 --
213 -- Returns a status_processing_rule_id and requires a user_key.
214 --
215 function get_status_processing_rule_id
216 (
217 p_status_process_rule_user_key in varchar2
218 ) return number is
219 l_status_processing_rule_id number;
220 begin
221 l_status_processing_rule_id := user_key_to_id(p_status_process_rule_user_key);
222 return(l_status_processing_rule_id);
223 exception
224 when others then
225 hr_data_pump.fail('get_status_processing_rule_id', sqlerrm, p_status_process_rule_user_key);
226 raise;
227 end get_status_processing_rule_id;
228 --
229 END pay_processing_rule_data_pump ;