1 PACKAGE BODY PER_BF_GEN_DATA_PUMP AS
2 /* $Header: pebgendp.pkb 115.6 2002/09/05 12:56:36 apholt noship $ */
3
4 -- Declare local variables
5 --
6 l_package_name VARCHAR2(30) DEFAULT 'PER_BF_GEN_DATA_PUMP.';
7 -- -------------------------------------------------------------------------
8 -- --------------------< get_input_value_id >-------------------------------
9 -- -------------------------------------------------------------------------
10 FUNCTION get_input_value_id
11 (p_reporting_name IN VARCHAR2
12 ,p_business_group_id IN NUMBER
13 ,p_effective_date IN DATE)
14 RETURN BINARY_INTEGER
15 IS
16 l_input_value_id NUMBER DEFAULT null;
17 BEGIN
18 IF p_reporting_name IS NOT NULL THEN
19 SELECT input_value_id
20 INTO l_input_value_id
21 FROM pay_input_values_f iv
22 , pay_element_types_f et
23 WHERE et.reporting_name = p_reporting_name
24 AND et.business_group_id = p_business_group_id
25 AND et.element_type_id = iv.element_type_id
26 AND iv.display_sequence=1
27 AND iv.name <> 'Pay Value'
28 AND p_effective_date
29 BETWEEN et.effective_start_date AND et.effective_end_date
30 AND p_effective_date
31 BETWEEN iv.effective_start_date AND iv.effective_end_date;
32 END IF;
33 --
34 RETURN (l_input_value_id);
35 EXCEPTION
36 WHEN OTHERS THEN
37 -- General Datapump fail procedure
38 hr_data_pump.fail('get_input_value_id'
39 ,sqlerrm
40 ,p_reporting_name
41 ,p_business_group_id
42 ,p_effective_date);
43 RAISE;
44 END get_input_value_id;
45 -- -------------------------------------------------------------------------
46 -- --------------------< get_balance_type_id >------------------------------
47 -- -------------------------------------------------------------------------
48 FUNCTION get_balance_type_id
49 (p_balance_type_name IN VARCHAR2
50 ,p_business_group_id IN NUMBER
51 ,p_effective_date IN DATE)
52 RETURN BINARY_INTEGER
53 IS
54 --
55 -- Cursor to get the balance_type_id
56 --
57 CURSOR csr_get_balance_type_id IS
58 SELECT balance_type_id
59 FROM per_bf_balance_types
60 WHERE internal_name = p_balance_type_name
61 AND business_group_id = p_business_group_id
62 AND p_effective_date
63 BETWEEN NVL(date_from,to_date('01-01-0001','DD-MM-YYYY'))
64 AND NVL(date_to,to_date('31-12-4712','DD-MM-YYYY'));
65 --
66 l_balance_type_id NUMBER;
67 BEGIN
68 --
69 OPEN csr_get_balance_type_id;
70 FETCH csr_get_balance_type_id INTO l_balance_type_id;
71 --
72 IF csr_get_balance_type_id%FOUND THEN
73 --
74 CLOSE csr_get_balance_type_id;
75 --
76 RETURN (l_balance_type_id);
77 --
78 ELSE
79 --
80 CLOSE csr_get_balance_type_id;
81 --
82 -- No ID has been found so raise an error.
83 --
84 RAISE_APPLICATION_ERROR (-20000,
85 'Cannot find balance_type_id');
86 --
87 END IF;
88 --
89 EXCEPTION
90 WHEN OTHERS THEN
91 -- General Datapump fail procedure
92 hr_data_pump.fail('get_balance_type_id'
93 ,sqlerrm
94 ,p_balance_type_name
95 ,p_business_group_id
96 ,p_effective_date);
97 RAISE;
98 END get_balance_type_id;
99 -- -------------------------------------------------------------------------
100 -- --------------------< get_payroll_id >-----------------------------------
101 -- -------------------------------------------------------------------------
102 FUNCTION get_payroll_id
103 (
104 p_payroll_name in varchar2,
105 p_business_group_id in number,
106 p_effective_date in date
107 )
108 RETURN BINARY_INTEGER IS
109 l_payroll_id BINARY_INTEGER;
110 BEGIN
111 SELECT pay.payroll_id
112 INTO l_payroll_id
113 FROM pay_payrolls_f pay
114 WHERE pay.payroll_name = p_payroll_name
115 AND pay.business_group_id + 0 = p_business_group_id
116 AND p_effective_date BETWEEN
117 pay.effective_start_date AND pay.effective_end_date;
118 RETURN(l_payroll_id);
119 EXCEPTION
120 WHEN OTHERS THEN
121 hr_data_pump.fail('get_payroll_id'
122 , sqlerrm
123 , p_payroll_name
124 , p_business_group_id
125 , p_effective_date);
126 RAISE;
127 END get_payroll_id;
128 -- -------------------------------------------------------------------------
129 -- --------------------< get_payroll_run_id >-------------------------------
130 -- -------------------------------------------------------------------------
131 FUNCTION get_payroll_run_id
132 (p_payroll_run_user_key IN VARCHAR2)
133 RETURN BINARY_INTEGER
134 IS
135 l_payroll_run_id NUMBER;
136 BEGIN
137 SELECT unique_key_id
138 INTO l_payroll_run_id
139 FROM hr_pump_batch_line_user_keys
140 WHERE user_key_value = p_payroll_run_user_key;
141 --
142 RETURN (l_payroll_run_id);
143 --
144 EXCEPTION
145 WHEN OTHERS THEN
146 -- General Datapump fail procedure
147 hr_data_pump.fail('get_payroll_run_id'
148 ,sqlerrm
149 ,p_payroll_run_user_key);
150 RAISE;
151 END get_payroll_run_id;
152 --
153 -- -------------------------------------------------------------------------
154 -- --------------------< get_assignment_id >--------------------------------
155 -- -------------------------------------------------------------------------
156 FUNCTION get_assignment_id
157 (p_employee_number IN VARCHAR2
158 ,p_business_group_id IN NUMBER
159 ,p_effective_date IN DATE)
160 RETURN BINARY_INTEGER
161 IS
162 l_assignment_id NUMBER;
163 BEGIN
164 SELECT pa.assignment_id
165 INTO l_assignment_id
166 FROM per_all_assignments_f pa
167 , per_all_people_f pp
168 WHERE pp.employee_number = p_employee_number
169 AND pp.business_group_id = p_business_group_id
170 AND pp.person_id = pa.person_id
171 AND pa.primary_flag = 'Y'
172 AND pa.assignment_type = 'E'
173 AND p_effective_date
174 BETWEEN pa.effective_start_date
175 AND pa.effective_end_date
176 AND p_effective_date
177 BETWEEN pp.effective_start_date
178 AND pp.effective_end_date;
179 --
180 RETURN (l_assignment_id);
181 --
182 EXCEPTION
183 WHEN OTHERS THEN
184 -- General Datapump fail procedure
185 hr_data_pump.fail('get_assignment_id'
186 ,sqlerrm
187 ,p_employee_number
188 ,p_effective_date);
189 RAISE;
190 END get_assignment_id;
191 -- -------------------------------------------------------------------------
192 -- ------------------< get_personal_payment_method_id >---------------------
193 -- -------------------------------------------------------------------------
194 FUNCTION get_personal_payment_method_id
195 (p_employee_number IN VARCHAR2
196 ,p_business_group_id IN NUMBER
197 ,p_effective_date IN DATE
198 ,p_org_payment_method_name IN VARCHAR2)
199 RETURN BINARY_INTEGER
200 IS
201 l_personal_payment_method_id NUMBER;
202 BEGIN
203 SELECT personal_payment_method_id
204 INTO l_personal_payment_method_id
205 FROM PAY_PERSONAL_PAYMENT_METHODS_F ppm
206 , PAY_ORG_PAYMENT_METHODS_F_TL opm
207 , PER_ALL_PEOPLE_F pp
208 , PER_ALL_ASSIGNMENTS_F asg
209 WHERE opm.org_payment_method_id = ppm.org_payment_method_id
210 AND opm.org_payment_method_name = p_org_payment_method_name
211 AND pp.employee_number = p_employee_number
212 AND pp.business_group_id = p_business_group_id
213 AND pp.person_id = asg.person_id
214 AND asg.primary_flag = 'Y'
215 AND asg.assignment_id = ppm.assignment_id
216 AND p_effective_date
217 BETWEEN pp.effective_start_date AND pp.effective_end_date
218 AND p_effective_date
219 BETWEEN asg.effective_start_date AND asg.effective_end_date
220 AND p_effective_date
221 BETWEEN ppm.effective_start_date AND ppm.effective_end_date
222 AND rownum=1
223 ORDER BY priority;
224 --
225 RETURN (l_personal_payment_method_id);
226 EXCEPTION
227 WHEN OTHERS THEN
228 -- General Datapump fail procedure
229 hr_data_pump.fail('get_personal_payment_method_id'
230 ,sqlerrm
231 ,p_employee_number
232 ,p_org_payment_method_name
233 ,p_effective_date);
234 RAISE;
235 END get_personal_payment_method_id;
236 END PER_BF_GEN_DATA_PUMP;
[Home] [Help]