[Home] [Help]
PACKAGE BODY: APPS.PQP_RIW_LOCATION_WRAPPER
Source
1 package body PQP_RIW_LOCATION_WRAPPER as
2 /* $Header: pqpriwlcwr.pkb 120.1 2010/05/18 11:41:27 rpahune noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_package varchar2(33) := 'pqp_riw_location_wrapper.';
8 g_location_rec hr_locations_all%rowtype;
12 --==========================================================================
9 g_style varchar2(7);
10 --==========================================================================
11 --Default Record Values
13 function Default_Location_Rec
14 return hr_locations_all%rowtype is
15 l_proc_name constant varchar2(150) := g_package||'Default_Locations_Rec';
16 l_location_rec hr_locations_all%rowtype;
17 Begin
18 hr_utility.set_location('Entering Default Rec' , 25);
19
20 l_location_rec.LOCATION_CODE := hr_api.g_varchar2;
21 l_location_rec.ADDRESS_LINE_1 := hr_api.g_varchar2;
22 l_location_rec.ADDRESS_LINE_2 := hr_api.g_varchar2;
23 l_location_rec.ADDRESS_LINE_3 := hr_api.g_varchar2;
24 l_location_rec.BILL_TO_SITE_FLAG := hr_api.g_varchar2;
25 l_location_rec.COUNTRY := hr_api.g_varchar2;
26 l_location_rec.DESCRIPTION := hr_api.g_varchar2;
27 l_location_rec.DESIGNATED_RECEIVER_ID := hr_api.g_number;
28 l_location_rec.IN_ORGANIZATION_FLAG := hr_api.g_varchar2;
29 l_location_rec.INACTIVE_DATE := hr_api.g_date;
30 l_location_rec.INVENTORY_ORGANIZATION_ID := hr_api.g_number;
31 l_location_rec.OFFICE_SITE_FLAG := hr_api.g_varchar2;
32 l_location_rec.POSTAL_CODE := hr_api.g_varchar2;
33 l_location_rec.RECEIVING_SITE_FLAG := hr_api.g_varchar2;
34 l_location_rec.REGION_1 := hr_api.g_varchar2;
35 l_location_rec.REGION_2 := hr_api.g_varchar2;
36 l_location_rec.REGION_3 := hr_api.g_varchar2;
37 l_location_rec.SHIP_TO_LOCATION_ID := hr_api.g_number;
38 l_location_rec.SHIP_TO_SITE_FLAG := hr_api.g_varchar2;
39 l_location_rec.STYLE := g_style;
40 l_location_rec.TAX_NAME := hr_api.g_varchar2;
41 l_location_rec.TELEPHONE_NUMBER_1 := hr_api.g_varchar2;
42 l_location_rec.TELEPHONE_NUMBER_2 := hr_api.g_varchar2;
43 l_location_rec.TELEPHONE_NUMBER_3 := hr_api.g_varchar2;
44 l_location_rec.TOWN_OR_CITY := hr_api.g_varchar2;
45 l_location_rec.ATTRIBUTE_CATEGORY := hr_api.g_varchar2;
46 l_location_rec.ATTRIBUTE1 := hr_api.g_varchar2;
47 l_location_rec.ATTRIBUTE2 := hr_api.g_varchar2;
48 l_location_rec.ATTRIBUTE3 := hr_api.g_varchar2;
49 l_location_rec.ATTRIBUTE4 := hr_api.g_varchar2;
50 l_location_rec.ATTRIBUTE5 := hr_api.g_varchar2;
51 l_location_rec.ATTRIBUTE6 := hr_api.g_varchar2;
52 l_location_rec.ATTRIBUTE7 := hr_api.g_varchar2;
53 l_location_rec.ATTRIBUTE8 := hr_api.g_varchar2;
54 l_location_rec.ATTRIBUTE9 := hr_api.g_varchar2;
55 l_location_rec.ATTRIBUTE10 := hr_api.g_varchar2;
56 l_location_rec.ATTRIBUTE11 := hr_api.g_varchar2;
57 l_location_rec.ATTRIBUTE12 := hr_api.g_varchar2;
58 l_location_rec.ATTRIBUTE13 := hr_api.g_varchar2;
59 l_location_rec.ATTRIBUTE14 := hr_api.g_varchar2;
60 l_location_rec.ATTRIBUTE15 := hr_api.g_varchar2;
61 l_location_rec.ATTRIBUTE16 := hr_api.g_varchar2;
62 l_location_rec.ATTRIBUTE17 := hr_api.g_varchar2;
63 l_location_rec.ATTRIBUTE18 := hr_api.g_varchar2;
64 l_location_rec.ATTRIBUTE19 := hr_api.g_varchar2;
65 l_location_rec.ATTRIBUTE20 := hr_api.g_varchar2;
66 l_location_rec.LAST_UPDATE_DATE := hr_api.g_date;
67 l_location_rec.LAST_UPDATED_BY := hr_api.g_number;
68 l_location_rec.LAST_UPDATE_LOGIN := hr_api.g_number;
69 l_location_rec.CREATED_BY := hr_api.g_number;
70 l_location_rec.CREATION_DATE := hr_api.g_date;
71 l_location_rec.OBJECT_VERSION_NUMBER := hr_api.g_number;
72 l_location_rec.TP_HEADER_ID := hr_api.g_number;
73 l_location_rec.ECE_TP_LOCATION_CODE := hr_api.g_varchar2;
74 l_location_rec.GLOBAL_ATTRIBUTE_CATEGORY := hr_api.g_varchar2;
75 l_location_rec.GLOBAL_ATTRIBUTE1 := hr_api.g_varchar2;
76 l_location_rec.GLOBAL_ATTRIBUTE2 := hr_api.g_varchar2;
77 l_location_rec.GLOBAL_ATTRIBUTE3 := hr_api.g_varchar2;
78 l_location_rec.GLOBAL_ATTRIBUTE4 := hr_api.g_varchar2;
79 l_location_rec.GLOBAL_ATTRIBUTE5 := hr_api.g_varchar2;
80 l_location_rec.GLOBAL_ATTRIBUTE6 := hr_api.g_varchar2;
81 l_location_rec.GLOBAL_ATTRIBUTE7 := hr_api.g_varchar2;
82 l_location_rec.GLOBAL_ATTRIBUTE8 := hr_api.g_varchar2;
83 l_location_rec.GLOBAL_ATTRIBUTE9 := hr_api.g_varchar2;
84 l_location_rec.GLOBAL_ATTRIBUTE10 := hr_api.g_varchar2;
85 l_location_rec.GLOBAL_ATTRIBUTE11 := hr_api.g_varchar2;
86 l_location_rec.GLOBAL_ATTRIBUTE12 := hr_api.g_varchar2;
87 l_location_rec.GLOBAL_ATTRIBUTE13 := hr_api.g_varchar2;
88 l_location_rec.GLOBAL_ATTRIBUTE14 := hr_api.g_varchar2;
89 l_location_rec.GLOBAL_ATTRIBUTE15 := hr_api.g_varchar2;
90 l_location_rec.GLOBAL_ATTRIBUTE16 := hr_api.g_varchar2;
91 l_location_rec.GLOBAL_ATTRIBUTE17 := hr_api.g_varchar2;
92 l_location_rec.GLOBAL_ATTRIBUTE18 := hr_api.g_varchar2;
93 l_location_rec.GLOBAL_ATTRIBUTE19 := hr_api.g_varchar2;
94 l_location_rec.GLOBAL_ATTRIBUTE20 := hr_api.g_varchar2;
95 l_location_rec.BUSINESS_GROUP_ID := hr_api.g_number;
96 l_location_rec.LOC_INFORMATION13 := hr_api.g_varchar2;
97 l_location_rec.LOC_INFORMATION14 := hr_api.g_varchar2;
98 l_location_rec.LOC_INFORMATION15 := hr_api.g_varchar2;
99 l_location_rec.LOC_INFORMATION16 := hr_api.g_varchar2;
100 l_location_rec.LOC_INFORMATION17 := hr_api.g_varchar2;
101 l_location_rec.LOC_INFORMATION18 := hr_api.g_varchar2;
102 l_location_rec.LOC_INFORMATION19 := hr_api.g_varchar2;
103 l_location_rec.LOC_INFORMATION20 := hr_api.g_varchar2;
104 l_location_rec.DERIVED_LOCALE := hr_api.g_varchar2;
105 l_location_rec.LEGAL_ADDRESS_FLAG := hr_api.g_varchar2;
106 l_location_rec.TIMEZONE_CODE := hr_api.g_varchar2;
107
108 return l_location_rec;
109
110 exception
111 when others then
112 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
113 raise;
114
115 end Default_Location_Rec;
116
117 -- =============================================================================
118 -- Get_Record_Values:
119 -- =============================================================================
120 function Get_Record_Values
121 (p_interface_code in varchar2 default null)
122 return hr_locations_all%rowtype is
123
124 cursor bne_cols(c_interface_code in varchar2) is
125 select lower(bic.interface_col_name) interface_col_name
126 from bne_interface_cols_b bic
127 where bic.interface_code = c_interface_code
128 and bic.display_flag ='Y';
129 --and bic.interface_col_type <> 2;
130
131 -- To query cols which are not displayed (DFF segments)
132 cursor bne_cols_no_disp(c_interface_code in varchar2) is
133 select lower(bic.interface_col_name) interface_col_name
134 from bne_interface_cols_b bic
135 where bic.interface_code = c_interface_code
136 and bic.display_flag ='N';
137
138 l_location_rec hr_locations_all%rowtype;
139 col_name varchar2(150);
140 l_proc_name constant varchar2(150) := g_package||'Get_Record_Values';
141
142 Begin
143 l_location_rec := Default_Location_Rec;
144
145 for col_rec in bne_cols (p_interface_code)
146 loop
147 case col_rec.interface_col_name
148 when 'p_location_code' then
149 l_location_rec.location_code := g_location_rec.location_code;
150 when 'p_description' then
151 l_location_rec.description := g_location_rec.description;
152 when 'p_timezone_code' then
153 l_location_rec.timezone_code := g_location_rec.timezone_code;
154 when 'p_tp_header_id' then
155 l_location_rec.tp_header_id := g_location_rec.tp_header_id;
156 when 'p_ece_tp_location_code' then
157 l_location_rec.ece_tp_location_code := g_location_rec.ece_tp_location_code;
158 when 'p_bill_to_site_flag' then
159 l_location_rec.bill_to_site_flag := g_location_rec.bill_to_site_flag;
160 when 'p_designated_receiver_id' then
161 l_location_rec.designated_receiver_id := g_location_rec.designated_receiver_id;
162 when 'p_in_organization_flag' then
163 l_location_rec.in_organization_flag := g_location_rec.in_organization_flag;
164 when 'p_inactive_date' then
165 l_location_rec.inactive_date := g_location_rec.inactive_date;
166 when 'p_inventory_organization_id' then
167 l_location_rec.inventory_organization_id := g_location_rec.inventory_organization_id;
168 when 'p_office_site_flag' then
169 l_location_rec.office_site_flag := g_location_rec.office_site_flag;
170 when 'p_receiving_site_flag' then
171 l_location_rec.receiving_site_flag := g_location_rec.receiving_site_flag;
172 when 'p_ship_to_location_id' then
173 l_location_rec.ship_to_location_id := g_location_rec.ship_to_location_id;
174 when 'p_ship_to_site_flag' then
175 l_location_rec.ship_to_site_flag := g_location_rec.ship_to_site_flag;
176 when 'style' then
177 l_location_rec.style := g_location_rec.style;
178 if l_location_rec.style is not null then
179 for col_rec1 in bne_cols_no_disp(p_interface_code) loop
180 case col_rec1.interface_col_name
181 when 'address_line_1' then
182 l_location_rec.address_line_1 := g_location_rec.address_line_1;
183 when 'address_line_2' then
184 l_location_rec.address_line_2 := g_location_rec.address_line_2;
185 when 'address_line_3' then
186 l_location_rec.address_line_3 := g_location_rec.address_line_3;
187 when 'country' then
188 l_location_rec.country := g_location_rec.country;
189 when 'postal_code' then
190 l_location_rec.postal_code := g_location_rec.postal_code;
191 when 'region_1' then
192 l_location_rec.region_1 := g_location_rec.region_1;
193 when 'region_2' then
194 l_location_rec.region_2 := g_location_rec.region_2;
195 when 'region_3' then
196 l_location_rec.region_3 := g_location_rec.region_3;
197 when 'telephone_number_1' then
198 l_location_rec.telephone_number_1 := g_location_rec.telephone_number_1;
199 when 'telephone_number_2' then
200 l_location_rec.telephone_number_2 := g_location_rec.telephone_number_2;
201 when 'telephone_number_3' then
202 l_location_rec.telephone_number_3 := g_location_rec.telephone_number_3;
203 when 'town_or_city' then
207 when 'loc_information14' then
204 l_location_rec.town_or_city := g_location_rec.town_or_city;
205 when 'loc_information13' then
206 l_location_rec.loc_information13 := g_location_rec.loc_information13;
208 l_location_rec.loc_information14 := g_location_rec.loc_information14;
209 when 'loc_information15' then
210 l_location_rec.loc_information15 := g_location_rec.loc_information15;
211 when 'loc_information16' then
212 l_location_rec.loc_information16 := g_location_rec.loc_information16;
213 when 'loc_information17' then
214 l_location_rec.loc_information17 := g_location_rec.loc_information17;
215 when 'loc_information18' then
216 l_location_rec.loc_information18 := g_location_rec.loc_information18;
217 when 'loc_information19' then
218 l_location_rec.loc_information19 := g_location_rec.loc_information19;
219 when 'loc_information20' then
220 l_location_rec.loc_information20 := g_location_rec.loc_information20;
221 else
222 null;
223 end case;
224 end loop;
225 end if;
226 when 'p_tax_name' then
227 l_location_rec.tax_name := g_location_rec.tax_name;
228 when 'attribute_category' then
229 l_location_rec.attribute_category := g_location_rec.attribute_category;
230 if l_location_rec.attribute_category is not null then
231 for col_rec1 in bne_cols_no_disp(p_interface_code) loop
232 case col_rec1.interface_col_name
233 when 'attribute1' then
234 l_location_rec.attribute1 := g_location_rec.attribute1;
235 when 'attribute2' then
236 l_location_rec.attribute2 := g_location_rec.attribute2;
237 when 'attribute3' then
238 l_location_rec.attribute3 := g_location_rec.attribute3;
239 when 'attribute4' then
240 l_location_rec.attribute4 := g_location_rec.attribute4;
241 when 'attribute5' then
242 l_location_rec.attribute5 := g_location_rec.attribute5;
243 when 'attribute6' then
244 l_location_rec.attribute6 := g_location_rec.attribute6;
245 when 'attribute7' then
246 l_location_rec.attribute7 := g_location_rec.attribute7;
247 when 'attribute8' then
248 l_location_rec.attribute8 := g_location_rec.attribute8;
249 when 'attribute9' then
250 l_location_rec.attribute9 := g_location_rec.attribute9;
251 when 'attribute10' then
252 l_location_rec.attribute10 := g_location_rec.attribute10;
253 when 'attribute11' then
254 l_location_rec.attribute11 := g_location_rec.attribute11;
255 when 'attribute12' then
256 l_location_rec.attribute12 := g_location_rec.attribute12;
257 when 'attribute13' then
258 l_location_rec.attribute13 := g_location_rec.attribute13;
259 when 'attribute14' then
260 l_location_rec.attribute14 := g_location_rec.attribute14;
261 when 'attribute15' then
262 l_location_rec.attribute15 := g_location_rec.attribute15;
263 when 'attribute16' then
264 l_location_rec.attribute16 := g_location_rec.attribute16;
265 when 'attribute17' then
266 l_location_rec.attribute17 := g_location_rec.attribute17;
267 when 'attribute18' then
268 l_location_rec.attribute18 := g_location_rec.attribute18;
269 when 'attribute19' then
270 l_location_rec.attribute19 := g_location_rec.attribute19;
271 when 'attribute20' then
272 l_location_rec.attribute20 := g_location_rec.attribute20;
273 else
274 null;
275 end case;
276 end loop;
277 end if;
278 when 'global_attribute_category' then
279 l_location_rec.global_attribute_category := g_location_rec.global_attribute_category;
280 if l_location_rec.attribute_category is not null then
281 for col_rec1 in bne_cols_no_disp(p_interface_code) loop
282 case col_rec1.interface_col_name
283 when 'global_attribute1' then
284 l_location_rec.global_attribute1 := g_location_rec.global_attribute1;
285 when 'global_attribute2' then
286 l_location_rec.global_attribute2 := g_location_rec.global_attribute2;
287 when 'global_attribute3' then
288 l_location_rec.global_attribute3 := g_location_rec.global_attribute3;
289 when 'global_attribute4' then
290 l_location_rec.global_attribute4 := g_location_rec.global_attribute4;
291 when 'global_attribute5' then
292 l_location_rec.global_attribute5 := g_location_rec.global_attribute5;
293 when 'global_attribute6' then
294 l_location_rec.global_attribute6 := g_location_rec.global_attribute6;
295 when 'global_attribute7' then
296 l_location_rec.global_attribute7 := g_location_rec.global_attribute7;
297 when 'global_attribute8' then
298 l_location_rec.global_attribute8 := g_location_rec.global_attribute8;
299 when 'global_attribute9' then
300 l_location_rec.global_attribute9 := g_location_rec.global_attribute9;
301 when 'global_attribute10' then
302 l_location_rec.global_attribute10 := g_location_rec.global_attribute10;
303 when 'global_attribute11' then
304 l_location_rec.global_attribute11 := g_location_rec.global_attribute11;
305 when 'global_attribute12' then
306 l_location_rec.global_attribute12 := g_location_rec.global_attribute12;
307 when 'global_attribute13' then
308 l_location_rec.global_attribute13 := g_location_rec.global_attribute13;
309 when 'global_attribute14' then
310 l_location_rec.global_attribute14 := g_location_rec.global_attribute14;
311 when 'global_attribute15' then
312 l_location_rec.global_attribute15 := g_location_rec.global_attribute15;
313 when 'global_attribute16' then
314 l_location_rec.global_attribute16 := g_location_rec.global_attribute16;
315 when 'global_attribute17' then
316 l_location_rec.global_attribute17 := g_location_rec.global_attribute17;
317 when 'global_attribute18' then
318 l_location_rec.global_attribute18 := g_location_rec.global_attribute18;
319 when 'global_attribute19' then
320 l_location_rec.global_attribute19 := g_location_rec.global_attribute19;
321 when 'global_attribute20' then
322 l_location_rec.global_attribute20 := g_location_rec.global_attribute20;
323 else
324 null;
325 end case;
326 end loop;
327 end if;
328 else
329 null;
330 end case;
331 end loop;
332 return l_location_rec;
333 end Get_Record_Values;
334
335
336
337 PROCEDURE INSUPD_LOCATION
338 (p_effective_date IN DATE
339 ,p_language_code IN VARCHAR2 DEFAULT hr_api.userenv_lang
340 ,p_location_code IN VARCHAR2
341 ,p_description IN VARCHAR2 DEFAULT NULL
342 ,p_timezone_code IN VARCHAR2 DEFAULT NULL
343 ,p_tp_header_id IN NUMBER DEFAULT NULL
344 ,p_ece_tp_location_code IN VARCHAR2 DEFAULT NULL
345 ,p_address_line_1 IN VARCHAR2 DEFAULT NULL
346 ,p_address_line_2 IN VARCHAR2 DEFAULT NULL
347 ,p_address_line_3 IN VARCHAR2 DEFAULT NULL
348 ,p_bill_to_site_flag IN VARCHAR2 DEFAULT 'Y'
349 ,p_country IN VARCHAR2 DEFAULT NULL
350 ,p_designated_receiver_id IN NUMBER DEFAULT NULL
351 ,p_in_organization_flag IN VARCHAR2 DEFAULT 'Y'
352 ,p_inactive_date IN DATE DEFAULT NULL
353 ,p_operating_unit_id IN NUMBER DEFAULT NULL
354 ,p_inventory_organization_id IN NUMBER DEFAULT NULL
355 ,p_office_site_flag IN VARCHAR2 DEFAULT 'Y'
356 ,p_postal_code IN VARCHAR2 DEFAULT NULL
357 ,p_receiving_site_flag IN VARCHAR2 DEFAULT 'Y'
358 ,p_region_1 IN VARCHAR2 DEFAULT NULL
359 ,p_region_2 IN VARCHAR2 DEFAULT NULL
360 ,p_region_3 IN VARCHAR2 DEFAULT NULL
361 ,p_ship_to_location_id IN NUMBER DEFAULT NULL
362 ,p_ship_to_site_flag IN VARCHAR2 DEFAULT 'Y'
363 ,p_style IN VARCHAR2 DEFAULT NULL
364 ,p_tax_name IN VARCHAR2 DEFAULT NULL
365 ,p_telephone_number_1 IN VARCHAR2 DEFAULT NULL
366 ,p_telephone_number_2 IN VARCHAR2 DEFAULT NULL
367 ,p_telephone_number_3 IN VARCHAR2 DEFAULT NULL
368 ,p_town_or_city IN VARCHAR2 DEFAULT NULL
369 ,p_loc_information13 IN VARCHAR2 DEFAULT NULL
370 ,p_loc_information14 IN VARCHAR2 DEFAULT NULL
371 ,p_loc_information15 IN VARCHAR2 DEFAULT NULL
372 ,p_loc_information16 IN VARCHAR2 DEFAULT NULL
373 ,p_loc_information17 IN VARCHAR2 DEFAULT NULL
374 ,p_loc_information18 IN VARCHAR2 DEFAULT NULL
375 ,p_loc_information19 IN VARCHAR2 DEFAULT NULL
376 ,p_loc_information20 IN VARCHAR2 DEFAULT NULL
377 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
378 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
379 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
380 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
381 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
382 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
383 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
384 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
385 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
386 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
387 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
388 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
389 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
390 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
391 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
392 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
393 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
394 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
395 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
396 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
397 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
398 ,p_global_attribute_category IN VARCHAR2 DEFAULT NULL
399 ,p_global_attribute1 IN VARCHAR2 DEFAULT NULL
400 ,p_global_attribute2 IN VARCHAR2 DEFAULT NULL
401 ,p_global_attribute3 IN VARCHAR2 DEFAULT NULL
402 ,p_global_attribute4 IN VARCHAR2 DEFAULT NULL
403 ,p_global_attribute5 IN VARCHAR2 DEFAULT NULL
404 ,p_global_attribute6 IN VARCHAR2 DEFAULT NULL
405 ,p_global_attribute7 IN VARCHAR2 DEFAULT NULL
406 ,p_global_attribute8 IN VARCHAR2 DEFAULT NULL
407 ,p_global_attribute9 IN VARCHAR2 DEFAULT NULL
408 ,p_global_attribute10 IN VARCHAR2 DEFAULT NULL
409 ,p_global_attribute11 IN VARCHAR2 DEFAULT NULL
410 ,p_global_attribute12 IN VARCHAR2 DEFAULT NULL
411 ,p_global_attribute13 IN VARCHAR2 DEFAULT NULL
412 ,p_global_attribute14 IN VARCHAR2 DEFAULT NULL
413 ,p_global_attribute15 IN VARCHAR2 DEFAULT NULL
414 ,p_global_attribute16 IN VARCHAR2 DEFAULT NULL
415 ,p_global_attribute17 IN VARCHAR2 DEFAULT NULL
416 ,p_global_attribute18 IN VARCHAR2 DEFAULT NULL
417 ,p_global_attribute19 IN VARCHAR2 DEFAULT NULL
418 ,p_global_attribute20 IN VARCHAR2 DEFAULT NULL
419 ,p_business_group_id IN NUMBER DEFAULT NULL
420 ,p_crt_upd IN VARCHAR2 DEFAULT NULL
421 ,p_migration_flag IN VARCHAR2 DEFAULT NULL
422 ,p_interface_code IN VARCHAR2 DEFAULT NULL
423 ,p_location_id IN NUMBER DEFAULT NULL
424 ,p_global_flag IN VARCHAR2 DEFAULT NULL) IS
425
426
427 cursor c_location_rec(c_location_code in varchar2, c_business_group_id in number) is
428 select location_id from hr_locations_all where location_code = c_location_code
429 and business_group_id = c_business_group_id;
430
431 l_location_id number;
432 l_object_version_number number;
433 l_create_flag number;
434
435 e_upl_not_allowed exception; -- when mode is 'View Only'
436 e_crt_not_allowed exception; -- when mode is 'Update Only'
437 g_upl_err_msg varchar2(100) := 'Upload NOT allowed.';
438 g_crt_err_msg varchar2(100) := 'Creating NOT allowed.';
439 l_proc varchar2(72) := g_package ||'PQP_RIW_LOCATION_WRAPPER';
440 l_migration_flag varchar2(10);
441 l_business_group_id number(10);
442
443 temp_rec hr_locations_all%rowtype;
444
445 begin
446 hr_utility.set_location('Entered PQP_RIW_LOCATION_WRAPPER.INSUPD_LOCATION', 5);
447
448 l_migration_flag := p_migration_flag;
449
450 if p_global_flag = 'Y' then
451 l_business_group_id := null;
452 else
453 l_business_group_id := p_business_group_id;
454 end if;
455
456 if (p_crt_upd = 'D') then
457 raise e_upl_not_allowed; -- View only flag is enabled but Trying to Upload
458 end if;
459
460 open c_location_rec(p_location_code, p_business_group_id);
461 fetch c_location_rec into l_location_id;
462 if c_location_rec%notfound then
463 if l_migration_flag = 'N' then
464 if p_location_id is null then
465 l_create_flag := 1;
466 else
467 l_create_flag := 2;
468 l_location_id := p_location_id;
469 end if;
470 else
471 l_create_flag := 1;
472 end if;
473 close c_location_rec;
474 else
475 l_create_flag := 2;
476 close c_location_rec;
477 end if;
478
479 if(l_create_flag = 1) then
480 if (p_crt_upd = 'U') then
481 raise e_crt_not_allowed; -- Update only flag is enabled but Trying to Create
482 end if;
483 hr_location_api.create_location
484 (p_validate => false
485 ,p_effective_date => p_effective_date
486 ,p_language_code => p_language_code
487 ,p_location_code => p_location_code
488 ,p_description => p_description
489 ,p_timezone_code => p_timezone_code
490 ,p_tp_header_id => p_tp_header_id
491 ,p_ece_tp_location_code => p_ece_tp_location_code
492 ,p_address_line_1 => p_address_line_1
493 ,p_address_line_2 => p_address_line_2
494 ,p_address_line_3 => p_address_line_3
495 ,p_bill_to_site_flag => p_bill_to_site_flag
496 ,p_country => p_country
497 ,p_designated_receiver_id => p_designated_receiver_id
498 ,p_in_organization_flag => p_in_organization_flag
499 ,p_inactive_date => p_inactive_date
500 ,p_operating_unit_id => p_operating_unit_id
501 ,p_inventory_organization_id => p_inventory_organization_id
502 ,p_office_site_flag => p_office_site_flag
503 ,p_postal_code => p_postal_code
504 ,p_receiving_site_flag => p_receiving_site_flag
505 ,p_region_1 => p_region_1
506 ,p_region_2 => p_region_2
507 ,p_region_3 => p_region_3
508 ,p_ship_to_location_id => p_ship_to_location_id
509 ,p_ship_to_site_flag => p_ship_to_site_flag
510 ,p_style => p_style
511 ,p_tax_name => p_tax_name
512 ,p_telephone_number_1 => p_telephone_number_1
513 ,p_telephone_number_2 => p_telephone_number_2
514 ,p_telephone_number_3 => p_telephone_number_3
515 ,p_town_or_city => p_town_or_city
516 ,p_loc_information13 => p_loc_information13
517 ,p_loc_information14 => p_loc_information14
518 ,p_loc_information15 => p_loc_information15
519 ,p_loc_information16 => p_loc_information16
520 ,p_loc_information17 => p_loc_information17
521 ,p_loc_information18 => p_loc_information18
522 ,p_loc_information19 => p_loc_information19
523 ,p_loc_information20 => p_loc_information20
524 ,p_attribute_category => p_attribute_category
525 ,p_attribute1 => p_attribute1
526 ,p_attribute2 => p_attribute2
527 ,p_attribute3 => p_attribute3
528 ,p_attribute4 => p_attribute4
529 ,p_attribute5 => p_attribute5
530 ,p_attribute6 => p_attribute6
531 ,p_attribute7 => p_attribute7
532 ,p_attribute8 => p_attribute8
533 ,p_attribute9 => p_attribute9
534 ,p_attribute10 => p_attribute10
535 ,p_attribute11 => p_attribute11
536 ,p_attribute12 => p_attribute12
537 ,p_attribute13 => p_attribute13
538 ,p_attribute14 => p_attribute14
539 ,p_attribute15 => p_attribute15
540 ,p_attribute16 => p_attribute16
541 ,p_attribute17 => p_attribute17
542 ,p_attribute18 => p_attribute18
543 ,p_attribute19 => p_attribute19
544 ,p_attribute20 => p_attribute20
545 ,p_global_attribute_category => p_global_attribute_category
546 ,p_global_attribute1 => p_global_attribute1
547 ,p_global_attribute2 => p_global_attribute2
548 ,p_global_attribute3 => p_global_attribute3
549 ,p_global_attribute4 => p_global_attribute4
550 ,p_global_attribute5 => p_global_attribute5
551 ,p_global_attribute6 => p_global_attribute6
552 ,p_global_attribute7 => p_global_attribute7
553 ,p_global_attribute8 => p_global_attribute8
554 ,p_global_attribute9 => p_global_attribute9
555 ,p_global_attribute10 => p_global_attribute10
556 ,p_global_attribute11 => p_global_attribute11
557 ,p_global_attribute12 => p_global_attribute12
558 ,p_global_attribute13 => p_global_attribute13
559 ,p_global_attribute14 => p_global_attribute14
560 ,p_global_attribute15 => p_global_attribute15
561 ,p_global_attribute16 => p_global_attribute16
562 ,p_global_attribute17 => p_global_attribute17
563 ,p_global_attribute18 => p_global_attribute18
564 ,p_global_attribute19 => p_global_attribute19
565 ,p_global_attribute20 => p_global_attribute20
566 ,p_business_group_id => l_business_group_id
567 ,p_location_id => l_location_id
568 ,p_object_version_number => l_object_version_number
569 );
570 else
571
572 select style into g_style from hr_locations_all where location_id = l_location_id;
573
574 g_location_rec.location_code := p_location_code;
575 g_location_rec.description := p_description;
576 g_location_rec.timezone_code := p_timezone_code;
577 g_location_rec.tp_header_id := p_tp_header_id;
578 g_location_rec.ece_tp_location_code := p_ece_tp_location_code;
579 g_location_rec.address_line_1 := p_address_line_1;
580 g_location_rec.address_line_2 := p_address_line_2;
581 g_location_rec.address_line_3 := p_address_line_3;
582 g_location_rec.bill_to_site_flag := p_bill_to_site_flag;
583 g_location_rec.country := p_country;
584 g_location_rec.designated_receiver_id := p_designated_receiver_id;
585 g_location_rec.in_organization_flag := p_in_organization_flag;
586 g_location_rec.inactive_date := p_inactive_date;
587 g_location_rec.inventory_organization_id := p_inventory_organization_id;
588 g_location_rec.office_site_flag := p_office_site_flag;
589 g_location_rec.postal_code := p_postal_code;
590 g_location_rec.receiving_site_flag := p_receiving_site_flag;
591 g_location_rec.region_1 := p_region_1 ;
592 g_location_rec.region_2 := p_region_2 ;
593 g_location_rec.region_3 := p_region_3 ;
594 g_location_rec.ship_to_location_id := p_ship_to_location_id;
595 g_location_rec.ship_to_site_flag := p_ship_to_site_flag;
596 g_location_rec.style := p_style;
597 g_location_rec.tax_name := p_tax_name;
598 g_location_rec.telephone_number_1 := p_telephone_number_1 ;
599 g_location_rec.telephone_number_2 := p_telephone_number_2;
600 g_location_rec.telephone_number_3 := p_telephone_number_3;
601 g_location_rec.town_or_city := p_town_or_city ;
602 g_location_rec.loc_information13 := p_loc_information13;
603 g_location_rec.loc_information14 := p_loc_information14;
604 g_location_rec.loc_information15 := p_loc_information15;
605 g_location_rec.loc_information16 := p_loc_information16;
606 g_location_rec.loc_information17 := p_loc_information17;
607 g_location_rec.loc_information18 := p_loc_information18;
608 g_location_rec.loc_information19 := p_loc_information19;
609 g_location_rec.loc_information20 := p_loc_information20;
610 g_location_rec.attribute_category := p_attribute_category;
611 g_location_rec.attribute1 := p_attribute1;
612 g_location_rec.attribute2 := p_attribute2;
613 g_location_rec.attribute3 := p_attribute3;
614 g_location_rec.attribute4 := p_attribute4;
615 g_location_rec.attribute5 := p_attribute5;
616 g_location_rec.attribute6 := p_attribute6;
617 g_location_rec.attribute7 := p_attribute7;
618 g_location_rec.attribute8 := p_attribute8;
619 g_location_rec.attribute9 := p_attribute9;
620 g_location_rec.attribute10 := p_attribute10;
621 g_location_rec.attribute11 := p_attribute11;
622 g_location_rec.attribute12 := p_attribute12;
623 g_location_rec.attribute13 := p_attribute13;
624 g_location_rec.attribute14 := p_attribute14;
625 g_location_rec.attribute15 := p_attribute15;
626 g_location_rec.attribute16 := p_attribute16;
627 g_location_rec.attribute17 := p_attribute17;
628 g_location_rec.attribute18 := p_attribute18;
629 g_location_rec.attribute19 := p_attribute19;
630 g_location_rec.attribute20 := p_attribute20;
631 g_location_rec.global_attribute_category := p_global_attribute_category;
632 g_location_rec.global_attribute1 := p_global_attribute1;
633 g_location_rec.global_attribute2 := p_global_attribute2;
634 g_location_rec.global_attribute3 := p_global_attribute3;
635 g_location_rec.global_attribute4 := p_global_attribute4;
636 g_location_rec.global_attribute5 := p_global_attribute5;
637 g_location_rec.global_attribute6 := p_global_attribute6;
638 g_location_rec.global_attribute7 := p_global_attribute7;
639 g_location_rec.global_attribute8 := p_global_attribute8;
640 g_location_rec.global_attribute9 := p_global_attribute9;
641 g_location_rec.global_attribute10 := p_global_attribute10;
642 g_location_rec.global_attribute11 := p_global_attribute11;
643 g_location_rec.global_attribute12 := p_global_attribute12;
644 g_location_rec.global_attribute13 := p_global_attribute13;
645 g_location_rec.global_attribute14 := p_global_attribute14;
646 g_location_rec.global_attribute15 := p_global_attribute15;
647 g_location_rec.global_attribute16 := p_global_attribute16;
648 g_location_rec.global_attribute17 := p_global_attribute17;
649 g_location_rec.global_attribute18 := p_global_attribute18;
650 g_location_rec.global_attribute19 := p_global_attribute19;
651 g_location_rec.global_attribute20 := p_global_attribute20;
652
653
654 temp_rec := Get_Record_Values(p_interface_code => p_interface_code);
655
656 select object_version_number into l_object_version_number from
657 hr_locations_all where location_id = l_location_id;
658
659 hr_location_api.update_location(
660 p_validate => false
661 ,p_effective_date => p_effective_date
662 ,p_location_id => l_location_id
663 ,p_language_code => p_language_code
664 ,p_location_code => temp_rec.location_code
665 ,p_description => temp_rec.description
666 ,p_timezone_code => temp_rec.timezone_code
667 ,p_tp_header_id => temp_rec.tp_header_id
668 ,p_ece_tp_location_code => temp_rec.ece_tp_location_code
669 ,p_address_line_1 => temp_rec.address_line_1
670 ,p_address_line_2 => temp_rec.address_line_2
671 ,p_address_line_3 => temp_rec.address_line_3
672 ,p_bill_to_site_flag => temp_rec.bill_to_site_flag
673 ,p_country => temp_rec.country
674 ,p_designated_receiver_id => temp_rec.designated_receiver_id
675 ,p_in_organization_flag => temp_rec.in_organization_flag
676 ,p_inactive_date => temp_rec.inactive_date
677 ,p_operating_unit_id => p_operating_unit_id
678 ,p_inventory_organization_id => temp_rec.inventory_organization_id
679 ,p_office_site_flag => temp_rec.office_site_flag
680 ,p_postal_code => temp_rec.postal_code
681 ,p_receiving_site_flag => temp_rec.receiving_site_flag
682 ,p_region_1 => temp_rec.region_1
683 ,p_region_2 => temp_rec.region_2
684 ,p_region_3 => temp_rec.region_3
685 ,p_ship_to_location_id => temp_rec.ship_to_location_id
686 ,p_ship_to_site_flag => temp_rec.ship_to_site_flag
687 ,p_style => temp_rec.style
688 ,p_tax_name => temp_rec.tax_name
689 ,p_telephone_number_1 => temp_rec.telephone_number_1
690 ,p_telephone_number_2 => temp_rec.telephone_number_2
691 ,p_telephone_number_3 => temp_rec.telephone_number_3
692 ,p_town_or_city => temp_rec.town_or_city
693 ,p_loc_information13 => temp_rec.loc_information13
694 ,p_loc_information14 => temp_rec.loc_information14
695 ,p_loc_information15 => temp_rec.loc_information15
696 ,p_loc_information16 => temp_rec.loc_information16
697 ,p_loc_information17 => temp_rec.loc_information17
698 ,p_loc_information18 => temp_rec.loc_information18
699 ,p_loc_information19 => temp_rec.loc_information19
700 ,p_loc_information20 => temp_rec.loc_information20
701 ,p_attribute_category => temp_rec.attribute_category
702 ,p_attribute1 => temp_rec.attribute1
703 ,p_attribute2 => temp_rec.attribute2
704 ,p_attribute3 => temp_rec.attribute3
705 ,p_attribute4 => temp_rec.attribute4
706 ,p_attribute5 => temp_rec.attribute5
707 ,p_attribute6 => temp_rec.attribute6
708 ,p_attribute7 => temp_rec.attribute7
709 ,p_attribute8 => temp_rec.attribute8
710 ,p_attribute9 => temp_rec.attribute9
711 ,p_attribute10 => temp_rec.attribute10
712 ,p_attribute11 => temp_rec.attribute11
713 ,p_attribute12 => temp_rec.attribute12
714 ,p_attribute13 => temp_rec.attribute13
715 ,p_attribute14 => temp_rec.attribute14
716 ,p_attribute15 => temp_rec.attribute15
717 ,p_attribute16 => temp_rec.attribute16
718 ,p_attribute17 => temp_rec.attribute17
719 ,p_attribute18 => temp_rec.attribute18
720 ,p_attribute19 => temp_rec.attribute19
721 ,p_attribute20 => temp_rec.attribute20
722 ,p_global_attribute_category => temp_rec.global_attribute_category
723 ,p_global_attribute1 => temp_rec.global_attribute1
724 ,p_global_attribute2 => temp_rec.global_attribute2
725 ,p_global_attribute3 => temp_rec.global_attribute3
726 ,p_global_attribute4 => temp_rec.global_attribute4
727 ,p_global_attribute5 => temp_rec.global_attribute5
728 ,p_global_attribute6 => temp_rec.global_attribute6
729 ,p_global_attribute7 => temp_rec.global_attribute7
730 ,p_global_attribute8 => temp_rec.global_attribute8
731 ,p_global_attribute9 => temp_rec.global_attribute9
732 ,p_global_attribute10 => temp_rec.global_attribute10
733 ,p_global_attribute11 => temp_rec.global_attribute11
734 ,p_global_attribute12 => temp_rec.global_attribute12
735 ,p_global_attribute13 => temp_rec.global_attribute13
736 ,p_global_attribute14 => temp_rec.global_attribute14
737 ,p_global_attribute15 => temp_rec.global_attribute15
738 ,p_global_attribute16 => temp_rec.global_attribute16
739 ,p_global_attribute17 => temp_rec.global_attribute17
740 ,p_global_attribute18 => temp_rec.global_attribute18
741 ,p_global_attribute19 => temp_rec.global_attribute19
742 ,p_global_attribute20 => temp_rec.global_attribute20
743 ,p_object_version_number => l_object_version_number
744 );
745
746 end if;
747
748
749
750 exception
751 -- when hr_multi_message.error_message_exist then
752 --
753 -- Catch the Multiple Message List exception which
754 -- indicates API processing has been aborted because
755 -- at least one message exists in the list.
756 --
757 --rollback to enrollment_proc;
758 --
759 -- Reset IN OUT parameters and set OUT parameters
760 --
761 -- p_object_version_number := null;
762 -- p_finance_line_id := l_finance_line_id;
763 -- p_return_status := hr_multi_message.get_return_status_disable;
764 -- hr_utility.set_location(' Leaving:' || l_proc, 30);
765 -- when others then
766 --
767 -- When Multiple Message Detection is enabled catch
768 -- any Application specific or other unexpected
769 -- exceptions. Adding appropriate details to the
770 -- Multiple Message List. Otherwise re-raise the
771 -- error.
772 --
773 -- rollback to enrollment_proc;
774
775
776 when e_upl_not_allowed then
777 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
778 hr_utility.set_message_token('GENERIC_TOKEN',g_upl_err_msg);
779 hr_utility.set_location('Leaving: ' || l_proc, 90);
780 hr_utility.raise_error;
781 when e_crt_not_allowed then
782 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
783 hr_utility.set_message_token('GENERIC_TOKEN',g_crt_err_msg);
784 hr_utility.set_location('Leaving: ' || l_proc, 100);
785 hr_utility.raise_error;
786 when others then
787 --l_error_msg := Substr(SQLERRM,1,2000);
788 hr_utility.set_location('SQLCODE :' || SQLCODE,90);
789 hr_utility.set_location('SQLERRM :' || SQLERRM,90);
790 --hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
791 --hr_utility.set_message_token('GENERIC_TOKEN',substr(l_error_msg,1,500) );
792 hr_utility.set_location('Leaving: ' || l_proc, 110);
793 hr_utility.raise_error;
794 --if hr_multi_message.unexpected_error_add(l_proc) then
795 -- hr_utility.set_location(' Leaving:' || l_proc,40);
796 --raise;
797 --end if;
798 --
799 -- Reset IN OUT and set OUT parameters
800 --
801 -- p_object_version_number := null;
802 -- p_finance_line_id := l_finance_line_id;
803 --p_return_status := hr_multi_message.get_return_status_disable;
804 -- hr_utility.set_location(' Leaving:' || l_proc,50);
805
806
807 end INSUPD_LOCATION;
808 end PQP_RIW_LOCATION_WRAPPER;