[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_MAPPING_SETUP
Source
1 PACKAGE BODY hr_h2pi_mapping_setup AS
2 /* $Header: hrh2piim.pkb 120.0 2005/05/31 00:39:30 appldev noship $ */
3
4 g_package VARCHAR2(33) := ' hr_h2pi_mapping_setup.';
5
6 PROCEDURE mapping_setup(p_from_client_id NUMBER) IS
7
8 l_proc VARCHAR2(72) := g_package||'mapping_setup';
9 --
10 -- Declaring the local variables to store the Source (HR side) and
11 -- Destination(payroll side) Business Group Id and Name.
12 --
13 l_from_client_id NUMBER(15);
14 l_to_business_group_id hr_all_organization_units.business_group_id%type;
15 --
16 BEGIN
17 hr_utility.set_location('Entering:'|| l_proc, 10);
18 l_from_client_id := p_from_client_id;
19 l_to_business_group_id := hr_h2pi_upload.g_to_business_group_id;
20 --
21 -- Inserts the mapping records for 'HR_LOCATIONS_ALL' table.
22 --
23 INSERT INTO hr_h2pi_id_mapping
24 (from_id, to_id, to_business_group_id, table_name)
25 SELECT loc1.location_id,
26 loc2.location_id,
27 l_to_business_group_id,
28 'HR_LOCATIONS_ALL'
29 FROM hr_h2pi_locations loc1,
30 hr_h2pi_locations_v loc2
31 WHERE loc1.client_id = l_from_client_id
32 AND loc2.business_group_id = l_to_business_group_id
33 AND loc1.location_code = loc2.location_code
34 AND NOT EXISTS (SELECT 1
35 FROM hr_h2pi_id_mapping map
36 WHERE (map.from_id = loc1.location_id
37 OR map.to_id = loc2.location_id)
38 AND map.table_name = 'HR_LOCATIONS_ALL'
39 AND map.to_business_group_id = l_to_business_group_id);
40 --
41 -- Inserts the mapping records for 'PER_PAY_BASES' table.
42 --
43 hr_utility.set_location(l_proc, 20);
44 INSERT INTO hr_h2pi_id_mapping
45 (from_id, to_id, to_business_group_id, table_name)
46 SELECT ppb1.pay_basis_id,
47 ppb2.pay_basis_id,
48 l_to_business_group_id,
49 'PER_PAY_BASES'
50 FROM hr_h2pi_pay_bases ppb1,
51 hr_h2pi_pay_bases_v ppb2
52 WHERE ppb1.client_id = l_from_client_id
53 AND ppb2.business_group_id = l_to_business_group_id
54 AND ppb1.name = ppb2.name
55 AND NOT EXISTS (SELECT 1
56 FROM hr_h2pi_id_mapping map
57 WHERE (map.from_id = ppb1.pay_basis_id
58 OR map.to_id = ppb2.pay_basis_id)
59 AND map.table_name = 'PER_PAY_BASES'
60 AND map.to_business_group_id = l_to_business_group_id);
61 --
62 -- Inserts the mapping records for 'HR_ALL_ORGANIZATION_UNITS' table.
63 --
64 hr_utility.set_location(l_proc, 30);
65 INSERT INTO hr_h2pi_id_mapping
66 (from_id, to_id, to_business_group_id, table_name)
67 SELECT v1.from_org_id,
68 v2.to_org_id,
69 l_to_business_group_id,
70 'HR_ALL_ORGANIZATION_UNITS'
71 FROM ( SELECT distinct org.organization_id from_org_id,
72 org.business_group_id,
73 org.name
74 FROM hr_h2pi_bg_and_gre org
75 WHERE org.client_id = l_from_client_id) v1,
76 ( SELECT distinct org.organization_id to_org_id,
77 org.business_group_id,
78 org.name
79 FROM hr_h2pi_bg_and_gre_v org
80 WHERE org.business_group_id = l_to_business_group_id) v2
81 WHERE v1.name = v2.name
82 AND NOT EXISTS (SELECT 1
83 FROM hr_h2pi_id_mapping map
84 WHERE (map.from_id = v1.from_org_id
85 OR map.to_id = v2.to_org_id)
86 AND map.table_name = 'HR_ALL_ORGANIZATION_UNITS'
87 AND map.to_business_group_id = l_to_business_group_id);
88 --
89 -- Inserts the mapping records for 'HR_ORGANIZATION_INFORMATION' table.
90 --
91 hr_utility.set_location(l_proc, 40);
92 INSERT INTO hr_h2pi_id_mapping
93 (from_id, to_id, to_business_group_id, table_name)
94 SELECT v1.from_org_info_id,
95 v2.to_org_info_id,
96 l_to_business_group_id,
97 'HR_ORGANIZATION_INFORMATION'
98 FROM ( SELECT distinct ogi.org_information_id from_org_info_id,
99 org.business_group_id,
100 org.name org_name,
101 ogi.org_information_context org_info_name
102 FROM hr_h2pi_bg_and_gre org,
103 hr_h2pi_organization_info ogi
104 WHERE org.organization_id = ogi.organization_id
105 AND ogi.org_information_context <> 'CLASS'
106 AND org.client_id = l_from_client_id) v1,
107 ( SELECT distinct ogi.org_information_id to_org_info_id,
108 org.business_group_id,
109 org.name org_name,
110 ogi.org_information_context org_info_name
111 FROM hr_h2pi_bg_and_gre_v org,
112 hr_h2pi_organization_info_v ogi
113 WHERE org.organization_id = ogi.organization_id
114 AND ogi.org_information_context <> 'CLASS'
115 AND org.business_group_id = l_to_business_group_id) v2
116 WHERE v1.org_name = v2.org_name
117 AND v1.org_info_name = v2.org_info_name
118 AND NOT EXISTS (SELECT 1
119 FROM hr_h2pi_id_mapping map
120 WHERE (map.from_id = v1.from_org_info_id
121 OR map.to_id = v2.to_org_info_id)
122 AND map.table_name = 'HR_ORGANIZATION_INFORMATION'
123 AND map.to_business_group_id = l_to_business_group_id);
124
125 --
126 -- Inserts the mapping records for 'PAY_ALL_PAYROLLS_F' table.
127 --
128 hr_utility.set_location(l_proc, 50);
129 INSERT INTO hr_h2pi_id_mapping
130 (from_id, to_id, to_business_group_id, table_name)
131 SELECT pay1.payroll_id,
132 pay2.payroll_id,
133 l_to_business_group_id,
134 'PAY_ALL_PAYROLLS_F'
135 FROM hr_h2pi_payrolls pay1,
136 hr_h2pi_payrolls_v pay2
137 WHERE pay1.payroll_name = pay2.payroll_name
138 AND pay1.client_id = l_from_client_id
139 AND pay2.business_group_id = l_to_business_group_id
140 AND NOT EXISTS (SELECT 1
141 FROM hr_h2pi_id_mapping map
142 WHERE (map.from_id = pay1.payroll_id
143 OR map.to_id = pay2.payroll_id)
144 AND map.table_name = 'PAY_ALL_PAYROLLS_F'
145 AND map.to_business_group_id = l_to_business_group_id);
146 --
147 -- Inserts the mapping records for 'PAY_ELEMENT_TYPES_F' table.
148 --
149 hr_utility.set_location(l_proc, 60);
150 INSERT INTO hr_h2pi_id_mapping
151 (from_id, to_id, to_business_group_id, table_name)
152 SELECT et1.element_type_id,
153 et2.element_type_id,
154 l_to_business_group_id,
155 'PAY_ELEMENT_TYPES_F'
156 FROM hr_h2pi_element_types et1,
157 pay_element_types_f et2
158 WHERE ((et2.business_group_id IS NULL
159 AND et2.legislation_code = 'US')
160 OR (et2.business_group_id = l_to_business_group_id
161 AND et2.attribute2 = 'Y'))
162 AND et1.client_id = l_from_client_id
163 AND et1.element_name = et2.element_name
164 AND NOT EXISTS (SELECT 1
165 FROM hr_h2pi_id_mapping map
166 WHERE (map.from_id = et1.element_type_id
167 OR map.to_id = et2.element_type_id)
168 AND map.table_name = 'PAY_ELEMENT_TYPES_F'
169 AND map.to_business_group_id = l_to_business_group_id);
170 --
171 -- Inserts the mapping records for 'PAY_INPUT_VALUES_F' table.
172 --
173 hr_utility.set_location(l_proc, 70);
174 INSERT INTO hr_h2pi_id_mapping
175 (from_id, to_id, to_business_group_id, table_name)
176 SELECT iv1.input_value_id,
177 iv2.input_value_id,
178 l_to_business_group_id,
179 'PAY_INPUT_VALUES_F'
180 FROM hr_h2pi_input_values iv1,
181 pay_input_values_f iv2,
182 hr_h2pi_element_types et1,
183 pay_element_types_f et2
184 WHERE ((et2.business_group_id IS NULL
185 AND et2.legislation_code = 'US')
186 OR (et2.business_group_id = l_to_business_group_id
187 AND et2.attribute2 = 'Y'))
188 AND et1.client_id = l_from_client_id
189 AND iv1.name = iv2.name
190 AND et1.element_name = et2.element_name
191 AND iv1.element_type_id = et1.element_type_id
192 AND iv2.element_type_id = et2.element_type_id
193 AND NOT EXISTS (SELECT 1
194 FROM hr_h2pi_id_mapping map
195 WHERE (map.from_id = iv1.input_value_id
196 OR map.to_id = iv2.input_value_id)
197 AND map.table_name = 'PAY_INPUT_VALUES_F'
198 AND map.to_business_group_id = l_to_business_group_id);
199 --
200 -- Inserts the mapping records for 'PAY_ELEMENT_LINKS_F' table.
201 --
202 hr_utility.set_location(l_proc, 80);
203 INSERT INTO hr_h2pi_id_mapping
204 (from_id, to_id, to_business_group_id, table_name)
205 SELECT el1.element_link_id,
206 el2.element_link_id,
207 l_to_business_group_id,
208 'PAY_ELEMENT_LINKS_F'
209 FROM hr_h2pi_element_links el1,
210 pay_element_links_f el2,
211 hr_h2pi_element_types et1,
212 pay_element_types_f et2
213 WHERE ((et2.business_group_id IS NULL
214 AND et2.legislation_code = 'US')
215 OR (et2.business_group_id = l_to_business_group_id
216 AND et2.attribute2 = 'Y'))
217 AND el1.client_id = l_from_client_id
218 AND el1.element_type_id = et1.element_type_id
219 AND el2.element_type_id = et2.element_type_id
220 AND el2.business_group_id = l_to_business_group_id
221 AND et1.client_id = l_from_client_id
222 AND et1.element_name = et2.element_name
223 AND NOT EXISTS (SELECT 1
224 FROM hr_h2pi_id_mapping map
225 WHERE (map.from_id = el1.element_link_id
226 OR map.to_id = el2.element_link_id)
227 AND map.table_name = 'PAY_ELEMENT_LINKS_F'
228 AND map.to_business_group_id = l_to_business_group_id);
229 --
230 -- Inserts the mapping records for 'PAY_ORG_PAYMENT_METHODS_F' table.
231 --
232 hr_utility.set_location(l_proc, 90);
233 INSERT INTO hr_h2pi_id_mapping
234 (from_id, to_id, to_business_group_id, table_name)
235 SELECT opm1.org_payment_method_id,
236 opm2.org_payment_method_id,
237 l_to_business_group_id,
238 'PAY_ORG_PAYMENT_METHODS_F'
239 FROM hr_h2pi_org_payment_methods opm1,
240 hr_h2pi_org_payment_methods_v opm2
241 WHERE opm1.client_id = l_from_client_id
242 AND opm2.business_group_id = l_to_business_group_id
243 AND opm1.org_payment_method_name = opm2.org_payment_method_name
244 AND NOT EXISTS (SELECT 1
245 FROM hr_h2pi_id_mapping map
246 WHERE (map.from_id = opm1.org_payment_method_id
247 OR map.to_id = opm2.org_payment_method_id)
248 AND map.table_name = 'PAY_ORG_PAYMENT_METHODS_F'
249 AND map.to_business_group_id = l_to_business_group_id);
250 hr_utility.set_location('Leaving:'|| l_proc, 100);
251 COMMIT;
252 END mapping_setup;
253 --
254 PROCEDURE mapping_id_upload (p_errbuf OUT NOCOPY VARCHAR2,
255 p_retcode OUT NOCOPY NUMBER,
256 p_file_name VARCHAR2,
257 p_business_group_id NUMBER) IS
258
259
260 l_proc VARCHAR2(72) := g_package||'mapping_id_upload';
261
262 l_from_business_group_id hr_all_organization_units.organization_id%TYPE;
263 l_from_client_id hr_all_organization_units.organization_id%TYPE;
264
265 BEGIN
266 hr_utility.set_location('Entering:'|| l_proc, 10);
267 hr_h2pi_upload.g_request_id :=
268 hr_h2pi_main_upload.get_request_id('H2PI_MAPPING_ID_SETUP');
269 hr_h2pi_upload.g_to_business_group_id := p_business_group_id;
270 l_from_client_id := hr_h2pi_upload.get_from_client_id;
271
272 hr_utility.set_location(l_proc, 20);
273 hr_utility.set_location(p_file_name, 20);
274 hr_h2pi_upload.upload(p_errbuf => p_errbuf,
275 p_retcode => p_retcode,
276 p_file_name => p_file_name);
277
278 hr_utility.set_location(l_proc, 30);
279 mapping_setup(l_from_client_id);
280 hr_h2pi_validate.validate_bg_and_gre(l_from_client_id);
281 hr_h2pi_validate.validate_pay_basis(l_from_client_id);
282 hr_h2pi_validate.validate_payroll(l_from_client_id);
283 hr_h2pi_validate.validate_org_payment_method(l_from_client_id);
284 hr_h2pi_validate.validate_element_type(l_from_client_id);
285 hr_h2pi_validate.validate_element_link(l_from_client_id);
286 hr_utility.set_location(l_proc, 40);
287 IF hr_h2pi_error.check_for_errors THEN
288 hr_utility.set_location(l_proc, 50);
289 hr_h2pi_error.generate_error_report;
290 END IF;
291 hr_utility.set_location(l_proc, 60);
292 hr_h2pi_main_upload.clear_staging_tables(l_from_client_id);
293
294 hr_utility.set_location('Leaving:'|| l_proc, 70);
295 END;
296
297 END hr_h2pi_mapping_setup;