[Home] [Help]
PACKAGE BODY: APPS.HR_LOCATIONS_PKG
Source
1 PACKAGE BODY HR_LOCATIONS_PKG as
2 /* $Header: peloc01t.pkb 115.5 2002/12/06 11:27:29 pkakar ship $ */
3
4 /*-----------------------------------------------------------------------------
5 -- Name --
6 -- set_ship_to_location --
7 -- Purpose --
8 -- called from within insert and update in this pkg in so that the --
9 -- ship-to-locatin-id is set to be the same as the location-id IF --
10 -- ship-to-location is NULL --
11 -- Arguments --
12 -- see below --
13 ============================================================================*/
14 --
15 procedure set_ship_to_location(p_Purchasing_Ins in varchar2,
16 p_Inventory_Ins in varchar2,
17 p_Ship_To_Location in varchar2,
18 p_Location_Id in number,
19 p_Ship_To_Location_Id in out nocopy number,
20 p_location_code in varchar2 )
21 is
22 begin
23 if (p_Purchasing_Ins = 'Y' or p_Inventory_Ins = 'Y') and
24 p_Ship_To_Location = p_location_code
25 then
26 p_Ship_To_Location_Id := p_Location_Id;
27 else
28 p_Ship_To_Location_Id := p_Ship_To_Location_Id;
29 end if;
30 end;
31 --
32 --
33 /*-----------------------------------------------------------------------------
34 -- Name --
35 -- get_des_rec --
36 -- Purpose --
37 -- Called from post-query because designated receiver is linked to a date --
38 -- tracked table and designated receiver may not exist at session date --
39 -- it assumes that designated reseiver id is NOT NULL
40 -- Persons cannot be deleted off the system so if the person is not seen
41 -- at session date then only a search of person ahead of session date
42 -- need be done.
43 -- Arguments --
44 -- See Below. --
45 -- --
46 ============================================================================*/
47 --
48 --
49 procedure get_des_rec (
50 p_designated_receiver_id in number,
51 p_designated_receiver in out nocopy varchar2) is
52 --
53 cursor c1 is
54 select ppf.full_name
55 from per_people_f ppf
56 , fnd_sessions ses
57 where ses.session_id = userenv('sessionid')
58 and person_id = p_designated_receiver_id
59 and ses.effective_date
60 between ppf.effective_start_date
61 and ppf.effective_end_date;
62 --
63 cursor c2 is
64 select ppf.full_name
65 from per_people_f ppf
66 where ppf.person_id = p_designated_receiver_id
67 and ppf.effective_start_date = (
68 select min(ppf2.effective_start_date)
69 from per_people_f ppf2
70 where ppf2.person_id = ppf.person_id );
71 --
72 begin
73 --
74 hr_utility.set_location('hr_location_pkg.get_des_rec',1);
75 --
76 open c1;
77 --
78 fetch c1 into p_designated_receiver;
79 IF c1%notfound THEN
80 close c1;
81 open c2;
82 hr_utility.set_location('hr_location_pkg.get_des_rec',2);
83 fetch c2 into p_designated_receiver;
84 close c2;
85 END IF;
86 close c1;
87 --
88 end get_des_rec;
89 --
90 /*-----------------------------------------------------------------------------
91 -- Name --
92 -- get_inv_org --
93 -- Purpose --
94 -- Called from post-query because inventory organization is non db --
95 -- and requires an explicit population
96 -- Arguments --
97 -- See Below. --
98 -- --
99 ============================================================================*/
100 --
101 --
102 procedure get_inv_org (
103 p_inventory_orgnization_id in number,
104 p_organization_name out nocopy varchar2) is
105 --
106 cursor c3 is
107 select OOD.ORGANIZATION_CODE ||'-'||
108 OOD.ORGANIZATION_NAME
109 FROM ORG_ORGANIZATION_DEFINITIONS OOD
110 where OOD.ORGANIZATION_ID = p_inventory_orgnization_id ;
111 --
112 begin
113 --
114 open c3;
115 --
116 fetch c3 into p_organization_name;
117 --
118 close c3;
119 --
120 end get_inv_org;
121 --
122 -----------------------------------------------------------------------------
123 -- Name --
124 -- get_start_data --
125 -- Purpose --
126 -- Called from hr_init_forms so that only one journey to the db is --
127 -- made instead of 3
128 -- Arguments --
129 -- See Below. --
130 -- Notes --
131 -- --
132 -----------------------------------------------------------------------------
133 procedure get_start_data (
134 p_resp_appl_id in number,
135 p_legislation_code in varchar2,
136 p_short_name in out nocopy varchar2,
137 p_resp_name in out nocopy varchar2,
138 p_country in out nocopy varchar2,
139 p_territory_code in out nocopy varchar2) is
140 --
141 cursor c4 is
142 select application_name
143 from fnd_application_vl
144 where application_id = p_resp_appl_id;
145 --
146 cursor c5 is
147 SELECT FT.TERRITORY_SHORT_NAME,FT.TERRITORY_CODE
148 FROM FND_TERRITORIES_VL FT,
149 FND_DESCR_FLEX_CONTEXTS FDFC
150 WHERE FT.TERRITORY_CODE = p_legislation_code
151 AND substr(FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE,1,2) = FT.TERRITORY_CODE
152 AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME = 'Address Location'
153 AND FDFC.ENABLED_FLAG = 'Y';
154 --
155 begin
156 --
157 hr_utility.set_location('hr_locations_pkg.get_start_data',1);
158 --
159 if p_resp_appl_id is not null then
160 open c4;
161 --
162 fetch c4 into p_resp_name;
163 --
164 close c4;
165 --
166 end if;
167 --
168 hr_utility.set_location('hr_locations_pkg.get_start_data',2);
169 --
170 open c5;
171 --
172 fetch c5 into p_country,
173 p_territory_code;
174 --
175 close c5;
176 --
177 end get_start_data;
178 -----------------------------------------------------------------------------
179 -- Name --
180 -- validate_insert_location --
181 -- Purpose --
182 -- Arguments --
183 -- See Below. --
184 -- Notes --
185 -- 1. Is the location code unique. --
186 -----------------------------------------------------------------------------
187 --
188 procedure validate_insert_location
189 ( p_location_code varchar2,
190 p_location_id number,
191 p_inventory_organization_id number ) is
192 v_location_id number;
193 --
194 cursor csr_location_exists is
195 SELECT l.location_id
196 from hr_locations l
197 where upper(l.location_code) = upper(p_location_code)
198 and (l.location_id <> p_location_id or
199 p_location_id is null)
200 AND (l.inventory_organization_id = p_inventory_organization_id
201 OR p_inventory_organization_id is NULL);
202 --
203 begin
204 --
205 hr_utility.set_location('hr_location_pkg.validate_insert_location',1);
206 --
207 open csr_location_exists;
208 --
209 fetch csr_location_exists into v_location_id;
210 IF csr_location_exists%found then
211 close csr_location_exists;
212 hr_utility.set_message(801,'PAY_7681_USER_LOC_TABLE_UNIQUE');
213 hr_utility.raise_error;
214 END IF;
215 --
216 close csr_location_exists;
217 --
218 end validate_insert_location;
219 --
220 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
221 X_Location_Id IN OUT NOCOPY NUMBER,
222 X_Entered_By NUMBER,
223 X_Location_Code VARCHAR2,
224 X_Address_Line_1 VARCHAR2,
225 X_Address_Line_2 VARCHAR2,
226 X_Address_Line_3 VARCHAR2,
227 X_Bill_To_Site_Flag VARCHAR2,
228 X_Country VARCHAR2,
229 X_Description VARCHAR2,
230 X_Designated_Receiver_Id NUMBER,
231 X_In_Organization_Flag VARCHAR2,
232 X_Inactive_Date DATE,
233 X_Inventory_Organization_Id NUMBER,
234 X_Office_Site_Flag VARCHAR2,
235 X_Postal_Code VARCHAR2,
236 X_Receiving_Site_Flag VARCHAR2,
237 X_Region_1 VARCHAR2,
238 X_Region_2 VARCHAR2,
239 X_Region_3 VARCHAR2,
240 X_Ship_To_Location_Id IN OUT NOCOPY NUMBER,
241 X_Ship_To_Site_Flag VARCHAR2,
242 X_Style VARCHAR2,
243 X_Tax_Name VARCHAR2,
244 X_Ece_Tp_Location_Code VARCHAR2,
245 X_Telephone_Number_1 VARCHAR2,
246 X_Telephone_Number_2 VARCHAR2,
247 X_Telephone_Number_3 VARCHAR2,
248 X_Town_Or_City VARCHAR2,
249 X_Attribute_Category VARCHAR2,
250 X_Attribute1 VARCHAR2,
251 X_Attribute2 VARCHAR2,
252 X_Attribute3 VARCHAR2,
253 X_Attribute4 VARCHAR2,
254 X_Attribute5 VARCHAR2,
255 X_Attribute6 VARCHAR2,
256 X_Attribute7 VARCHAR2,
257 X_Attribute8 VARCHAR2,
258 X_Attribute9 VARCHAR2,
259 X_Attribute10 VARCHAR2,
260 X_Attribute11 VARCHAR2,
261 X_Attribute12 VARCHAR2,
262 X_Attribute13 VARCHAR2,
263 X_Attribute14 VARCHAR2,
264 X_Attribute15 VARCHAR2,
265 X_Attribute16 VARCHAR2,
266 X_Attribute17 VARCHAR2,
267 X_Attribute18 VARCHAR2,
268 X_Attribute19 VARCHAR2,
269 X_Attribute20 VARCHAR2,
270 X_Last_Update_Date DATE,
271 X_Last_Updated_By NUMBER,
272 X_Last_Update_Login NUMBER,
273 X_Created_By NUMBER,
274 X_Creation_Date DATE ,
275 X_C_Purchasing_Ins VARCHAR2,
276 X_C_Inventory_Ins VARCHAR2,
277 X_Ship_To_Location VARCHAR2,
278 X_Global_Attribute_Category VARCHAR2,
279 X_Global_Attribute1 VARCHAR2,
280 X_Global_Attribute2 VARCHAR2,
281 X_Global_Attribute3 VARCHAR2,
282 X_Global_Attribute4 VARCHAR2,
283 X_Global_Attribute5 VARCHAR2,
284 X_Global_Attribute6 VARCHAR2,
285 X_Global_Attribute7 VARCHAR2,
286 X_Global_Attribute8 VARCHAR2,
287 X_Global_Attribute9 VARCHAR2,
288 X_Global_Attribute10 VARCHAR2,
289 X_Global_Attribute11 VARCHAR2,
290 X_Global_Attribute12 VARCHAR2,
291 X_Global_Attribute13 VARCHAR2,
292 X_Global_Attribute14 VARCHAR2,
293 X_Global_Attribute15 VARCHAR2,
294 X_Global_Attribute16 VARCHAR2,
295 X_Global_Attribute17 VARCHAR2,
296 X_Global_Attribute18 VARCHAR2,
297 X_Global_Attribute19 VARCHAR2,
298 X_Global_Attribute20 VARCHAR2,
299 X_Loc_Information17 VARCHAR2,
300 X_Loc_Information18 VARCHAR2,
301 X_Loc_Information19 VARCHAR2,
302 X_Loc_Information20 VARCHAR2
303 ) IS
304 CURSOR C IS SELECT rowid FROM hr_locations
305 WHERE location_id = X_Location_Id;
306 --
307 CURSOR C2 IS SELECT hr_locations_s.nextval FROM sys.dual;
308 BEGIN
309 hr_locations_pkg.validate_insert_location
310 (X_Location_code,
311 X_Location_id,
312 X_Inventory_Organization_Id );
313 if (X_Location_Id is NULL) then
314 OPEN C2;
315 FETCH C2 INTO X_Location_Id;
316 CLOSE C2;
317 end if;
318 set_ship_to_location(X_C_Purchasing_Ins,
319 X_C_Inventory_Ins,
320 X_Ship_To_Location,
321 X_Location_Id,
322 X_Ship_To_Location_Id,
323 X_Location_Code);
324 --
325 INSERT INTO hr_locations(
326 location_id,
327 entered_by,
328 location_code,
329 address_line_1,
330 address_line_2,
331 address_line_3,
332 bill_to_site_flag,
333 country,
334 description,
335 designated_receiver_id,
336 in_organization_flag,
337 inactive_date,
338 inventory_organization_id,
339 office_site_flag,
340 postal_code,
341 receiving_site_flag,
342 region_1,
343 region_2,
344 region_3,
345 ship_to_location_id,
346 ship_to_site_flag,
347 style,
348 tax_name,
349 ece_tp_location_code,
350 telephone_number_1,
351 telephone_number_2,
352 telephone_number_3,
353 town_or_city,
354 attribute_category,
355 attribute1,
356 attribute2,
357 attribute3,
358 attribute4,
359 attribute5,
360 attribute6,
361 attribute7,
362 attribute8,
363 attribute9,
364 attribute10,
365 attribute11,
366 attribute12,
367 attribute13,
368 attribute14,
369 attribute15,
370 attribute16,
371 attribute17,
372 attribute18,
373 attribute19,
374 attribute20,
375 last_update_date,
376 last_updated_by,
377 last_update_login,
378 created_by,
379 creation_date,
380 global_attribute_category,
381 global_attribute1,
382 global_attribute2,
383 global_attribute3,
384 global_attribute4,
385 global_attribute5,
386 global_attribute6,
387 global_attribute7,
388 global_attribute8,
389 global_attribute9,
390 global_attribute10,
391 global_attribute11,
392 global_attribute12,
393 global_attribute13,
394 global_attribute14,
395 global_attribute15,
396 global_attribute16,
397 global_attribute17,
398 global_attribute18,
399 global_attribute19,
400 global_attribute20,
401 loc_information17,
402 loc_information18,
403 loc_information19,
404 loc_information20
405 ) VALUES (
406 X_Location_Id,
407 X_Entered_By,
408 X_Location_Code,
409 X_Address_Line_1,
410 X_Address_Line_2,
411 X_Address_Line_3,
412 X_Bill_To_Site_Flag,
413 X_Country,
414 X_Description,
415 X_Designated_Receiver_Id,
416 X_In_Organization_Flag,
417 X_Inactive_Date,
418 X_Inventory_Organization_Id,
419 X_Office_Site_Flag,
420 X_Postal_Code,
421 X_Receiving_Site_Flag,
422 X_Region_1,
423 X_Region_2,
424 X_Region_3,
425 X_Ship_To_Location_Id,
426 X_Ship_To_Site_Flag,
427 X_Style,
428 X_Tax_Name,
429 X_Ece_Tp_Location_Code,
430 X_Telephone_Number_1,
431 X_Telephone_Number_2,
432 X_Telephone_Number_3,
433 X_Town_Or_City,
434 X_Attribute_Category,
435 X_Attribute1,
436 X_Attribute2,
437 X_Attribute3,
438 X_Attribute4,
439 X_Attribute5,
440 X_Attribute6,
441 X_Attribute7,
442 X_Attribute8,
443 X_Attribute9,
444 X_Attribute10,
445 X_Attribute11,
446 X_Attribute12,
447 X_Attribute13,
448 X_Attribute14,
449 X_Attribute15,
450 X_Attribute16,
451 X_Attribute17,
452 X_Attribute18,
453 X_Attribute19,
454 X_Attribute20,
455 X_Last_Update_Date,
456 X_Last_Updated_By,
457 X_Last_Update_Login,
458 X_Created_By,
459 X_Creation_Date,
460 X_Global_Attribute_Category,
461 X_Global_Attribute1,
462 X_Global_Attribute2,
463 X_Global_Attribute3,
464 X_Global_Attribute4,
465 X_Global_Attribute5,
466 X_Global_Attribute6,
467 X_Global_Attribute7,
468 X_Global_Attribute8,
469 X_Global_Attribute9,
470 X_Global_Attribute10,
471 X_Global_Attribute11,
472 X_Global_Attribute12,
473 X_Global_Attribute13,
474 X_Global_Attribute14,
475 X_Global_Attribute15,
476 X_Global_Attribute16,
477 X_Global_Attribute17,
478 X_Global_Attribute18,
479 X_Global_Attribute19,
480 X_Global_Attribute20,
481 X_Loc_Information17,
482 X_Loc_Information18,
483 X_Loc_Information19,
484 X_Loc_Information20
485 );
486 --
487 --
488 OPEN C;
489 FETCH C INTO X_Rowid;
490 if (C%NOTFOUND) then
491 CLOSE C;
492 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
493 hr_utility.set_message_token('PROCEDURE',
494 'hr_locations_pkg.insert_row');
495 hr_utility.set_message_token('STEP','2');
496 hr_utility.raise_error;
497 end if;
498 CLOSE C;
499 END Insert_Row;
500 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
501 X_Location_Id NUMBER,
502 X_Entered_By NUMBER,
503 X_Location_Code VARCHAR2,
504 X_Address_Line_1 VARCHAR2,
505 X_Address_Line_2 VARCHAR2,
506 X_Address_Line_3 VARCHAR2,
507 X_Bill_To_Site_Flag VARCHAR2,
508 X_Country VARCHAR2,
509 X_Description VARCHAR2,
510 X_Designated_Receiver_Id NUMBER,
511 X_In_Organization_Flag VARCHAR2,
512 X_Inactive_Date DATE,
513 X_Inventory_Organization_Id NUMBER,
514 X_Office_Site_Flag VARCHAR2,
515 X_Postal_Code VARCHAR2,
516 X_Receiving_Site_Flag VARCHAR2,
517 X_Region_1 VARCHAR2,
518 X_Region_2 VARCHAR2,
519 X_Region_3 VARCHAR2,
520 X_Ship_To_Location_Id NUMBER,
521 X_Ship_To_Site_Flag VARCHAR2,
522 X_Style VARCHAR2,
523 X_Tax_Name VARCHAR2,
524 X_Ece_Tp_Location_Code VARCHAR2,
525 X_Telephone_Number_1 VARCHAR2,
526 X_Telephone_Number_2 VARCHAR2,
527 X_Telephone_Number_3 VARCHAR2,
528 X_Town_Or_City VARCHAR2,
529 X_Attribute_Category VARCHAR2,
530 X_Attribute1 VARCHAR2,
531 X_Attribute2 VARCHAR2,
532 X_Attribute3 VARCHAR2,
533 X_Attribute4 VARCHAR2,
534 X_Attribute5 VARCHAR2,
535 X_Attribute6 VARCHAR2,
536 X_Attribute7 VARCHAR2,
537 X_Attribute8 VARCHAR2,
538 X_Attribute9 VARCHAR2,
539 X_Attribute10 VARCHAR2,
540 X_Attribute11 VARCHAR2,
541 X_Attribute12 VARCHAR2,
542 X_Attribute13 VARCHAR2,
543 X_Attribute14 VARCHAR2,
544 X_Attribute15 VARCHAR2,
545 X_Attribute16 VARCHAR2,
546 X_Attribute17 VARCHAR2,
547 X_Attribute18 VARCHAR2,
548 X_Attribute19 VARCHAR2,
549 X_Attribute20 VARCHAR2,
550 X_Global_Attribute_Category VARCHAR2,
551 X_Global_Attribute1 VARCHAR2,
552 X_Global_Attribute2 VARCHAR2,
553 X_Global_Attribute3 VARCHAR2,
554 X_Global_Attribute4 VARCHAR2,
555 X_Global_Attribute5 VARCHAR2,
556 X_Global_Attribute6 VARCHAR2,
557 X_Global_Attribute7 VARCHAR2,
558 X_GLobal_Attribute8 VARCHAR2,
559 X_Global_Attribute9 VARCHAR2,
560 X_Global_Attribute10 VARCHAR2,
561 X_Global_Attribute11 VARCHAR2,
562 X_Global_Attribute12 VARCHAR2,
563 X_Global_Attribute13 VARCHAR2,
564 X_Global_Attribute14 VARCHAR2,
565 X_Global_Attribute15 VARCHAR2,
566 X_Global_Attribute16 VARCHAR2,
567 X_Global_Attribute17 VARCHAR2,
568 X_Global_Attribute18 VARCHAR2,
569 X_Global_Attribute19 VARCHAR2,
570 X_Global_Attribute20 VARCHAR2,
571 X_Loc_Information17 VARCHAR2,
572 X_Loc_Information18 VARCHAR2,
573 X_Loc_Information19 VARCHAR2,
574 X_Loc_Information20 VARCHAR2
575 ) IS
576 CURSOR C IS
577 SELECT *
578 FROM hr_locations
579 WHERE rowid = chartorowid(X_Rowid)
580 FOR UPDATE of Location_Id NOWAIT;
581 Recinfo C%ROWTYPE;
582 BEGIN
583 OPEN C;
584 FETCH C INTO Recinfo;
585 if (C%NOTFOUND) then
586 CLOSE C;
587
588 end if;
589 CLOSE C;
590
591 recinfo.tax_name := rtrim(recinfo.tax_name);
592 recinfo.ece_tp_location_code := rtrim(recinfo.ece_tp_location_code);
593 recinfo.telephone_number_1 := rtrim(recinfo.telephone_number_1);
594 recinfo.telephone_number_2 := rtrim(recinfo.telephone_number_2);
595 recinfo.telephone_number_3 := rtrim(recinfo.telephone_number_3);
596 recinfo.town_or_city := rtrim(recinfo.town_or_city);
597 recinfo.attribute_category := rtrim(recinfo.attribute_category);
598 recinfo.attribute1 := rtrim(recinfo.attribute1);
599 recinfo.attribute2 := rtrim(recinfo.attribute2);
600 recinfo.attribute3 := rtrim(recinfo.attribute3);
601 recinfo.attribute4 := rtrim(recinfo.attribute4);
602 recinfo.attribute5 := rtrim(recinfo.attribute5);
603 recinfo.attribute6 := rtrim(recinfo.attribute6);
604 recinfo.attribute7 := rtrim(recinfo.attribute7);
605 recinfo.attribute8 := rtrim(recinfo.attribute8);
606 recinfo.attribute9 := rtrim(recinfo.attribute9);
607 recinfo.attribute10 := rtrim(recinfo.attribute10);
608 recinfo.attribute11 := rtrim(recinfo.attribute11);
609 recinfo.attribute12 := rtrim(recinfo.attribute12);
610 recinfo.attribute13 := rtrim(recinfo.attribute13);
611 recinfo.attribute14 := rtrim(recinfo.attribute14);
612 recinfo.attribute15 := rtrim(recinfo.attribute15);
613 recinfo.attribute16 := rtrim(recinfo.attribute16);
614 recinfo.attribute17 := rtrim(recinfo.attribute17);
615 recinfo.attribute18 := rtrim(recinfo.attribute18);
616 recinfo.attribute19 := rtrim(recinfo.attribute19);
617 recinfo.attribute20 := rtrim(recinfo.attribute20);
618 recinfo.location_code := rtrim(recinfo.location_code);
619 recinfo.address_line_1 := rtrim(recinfo.address_line_1);
620 recinfo.address_line_2 := rtrim(recinfo.address_line_2);
621 recinfo.address_line_3 := rtrim(recinfo.address_line_3);
622 recinfo.bill_to_site_flag := rtrim(recinfo.bill_to_site_flag);
623 recinfo.country := rtrim(recinfo.country);
624 recinfo.description := rtrim(recinfo.description);
625 recinfo.in_organization_flag := rtrim(recinfo.in_organization_flag);
626 recinfo.office_site_flag := rtrim(recinfo.office_site_flag);
627 recinfo.postal_code := rtrim(recinfo.postal_code);
628 recinfo.receiving_site_flag := rtrim(recinfo.receiving_site_flag);
629 recinfo.region_1 := rtrim(recinfo.region_1);
630 recinfo.region_2 := rtrim(recinfo.region_2);
631 recinfo.region_3 := rtrim(recinfo.region_3);
632 recinfo.ship_to_site_flag := rtrim(recinfo.ship_to_site_flag);
633 recinfo.style := rtrim(recinfo.style);
634 recinfo.global_attribute_category := rtrim(recinfo.global_attribute_category);
635 recinfo.global_attribute1 := rtrim(recinfo.global_attribute1);
636 recinfo.global_attribute2 := rtrim(recinfo.global_attribute2);
637 recinfo.global_attribute3 := rtrim(recinfo.global_attribute3);
638 recinfo.global_attribute4 := rtrim(recinfo.global_attribute4);
639 recinfo.global_attribute5 := rtrim(recinfo.global_attribute5);
640 recinfo.global_attribute6 := rtrim(recinfo.global_attribute6);
641 recinfo.global_attribute7 := rtrim(recinfo.global_attribute7);
642 recinfo.global_attribute8 := rtrim(recinfo.global_attribute8);
643 recinfo.global_attribute9 := rtrim(recinfo.global_attribute9);
644 recinfo.global_attribute10 := rtrim(recinfo.global_attribute10);
645 recinfo.global_attribute11 := rtrim(recinfo.global_attribute11);
646 recinfo.global_attribute12 := rtrim(recinfo.global_attribute12);
647 recinfo.global_attribute13 := rtrim(recinfo.global_attribute13);
648 recinfo.global_attribute14 := rtrim(recinfo.global_attribute14);
649 recinfo.global_attribute15 := rtrim(recinfo.global_attribute15);
650 recinfo.global_attribute16 := rtrim(recinfo.global_attribute16);
651 recinfo.global_attribute17 := rtrim(recinfo.global_attribute17);
652 recinfo.global_attribute18 := rtrim(recinfo.global_attribute18);
653 recinfo.global_attribute19 := rtrim(recinfo.global_attribute19);
654 recinfo.global_attribute20 := rtrim(recinfo.global_attribute20);
655 recinfo.loc_information17 := rtrim(recinfo.loc_information17); recinfo.loc_information18 := rtrim(recinfo.loc_information18); recinfo.loc_information19 := rtrim(recinfo.loc_information19);
656 recinfo.loc_information20 := rtrim(recinfo.loc_information20);
657 if (
658 ( (Recinfo.location_id = X_Location_Id)
659 OR ( (Recinfo.location_id IS NULL)
660 AND (X_Location_Id IS NULL)))
661 AND ( (Recinfo.entered_by = X_Entered_By)
662 OR ( (Recinfo.entered_by IS NULL)
663 AND (X_Entered_By IS NULL)))
664 AND ( (Recinfo.location_code = X_Location_Code)
665 OR ( (Recinfo.location_code IS NULL)
666 AND (X_Location_Code IS NULL)))
667 AND ( (Recinfo.address_line_1 = X_Address_Line_1)
668 OR ( (Recinfo.address_line_1 IS NULL)
669 AND (X_Address_Line_1 IS NULL)))
670 AND ( (Recinfo.address_line_2 = X_Address_Line_2)
671 OR ( (Recinfo.address_line_2 IS NULL)
672 AND (X_Address_Line_2 IS NULL)))
673 AND ( (Recinfo.address_line_3 = X_Address_Line_3)
674 OR ( (Recinfo.address_line_3 IS NULL)
675 AND (X_Address_Line_3 IS NULL)))
676 AND ( (Recinfo.bill_to_site_flag = X_Bill_To_Site_Flag)
677 OR ( (Recinfo.bill_to_site_flag IS NULL)
678 AND (X_Bill_To_Site_Flag IS NULL)))
679 AND ( (Recinfo.country = X_Country)
680 OR ( (Recinfo.country IS NULL)
681 AND (X_Country IS NULL)))
682 AND ( (Recinfo.description = X_Description)
683 OR ( (Recinfo.description IS NULL)
684 AND (X_Description IS NULL)))
685 AND ( (Recinfo.designated_receiver_id = X_Designated_Receiver_Id)
686 OR ( (Recinfo.designated_receiver_id IS NULL)
687 AND (X_Designated_Receiver_Id IS NULL)))
688 AND ( (Recinfo.in_organization_flag = X_In_Organization_Flag)
689 OR ( (Recinfo.in_organization_flag IS NULL)
690 AND (X_In_Organization_Flag IS NULL)))
691 AND ( (Recinfo.inactive_date = X_Inactive_Date)
692 OR ( (Recinfo.inactive_date IS NULL)
693 AND (X_Inactive_Date IS NULL)))
694 AND ( (Recinfo.inventory_organization_id = X_Inventory_Organization_Id)
695 OR ( (Recinfo.inventory_organization_id IS NULL)
696 AND (X_Inventory_Organization_Id IS NULL)))
697 AND ( (Recinfo.office_site_flag = X_Office_Site_Flag)
698 OR ( (Recinfo.office_site_flag IS NULL)
699 AND (X_Office_Site_Flag IS NULL)))
700 AND ( (Recinfo.postal_code = X_Postal_Code)
701 OR ( (Recinfo.postal_code IS NULL)
702 AND (X_Postal_Code IS NULL)))
703 AND ( (Recinfo.receiving_site_flag = X_Receiving_Site_Flag)
704 OR ( (Recinfo.receiving_site_flag IS NULL)
705 AND (X_Receiving_Site_Flag IS NULL)))
706 AND ( (Recinfo.region_1 = X_Region_1)
707 OR ( (Recinfo.region_1 IS NULL)
708 AND (X_Region_1 IS NULL)))
709 AND ( (Recinfo.region_2 = X_Region_2)
710 OR ( (Recinfo.region_2 IS NULL)
711 AND (X_Region_2 IS NULL)))
712 AND ( (Recinfo.region_3 = X_Region_3)
713 OR ( (Recinfo.region_3 IS NULL)
714 AND (X_Region_3 IS NULL)))
715 AND ( (Recinfo.ship_to_location_id = X_Ship_To_Location_Id)
716 OR ( (Recinfo.ship_to_location_id IS NULL)
717 AND (X_Ship_To_Location_Id IS NULL)))
718 AND ( (Recinfo.ship_to_site_flag = X_Ship_To_Site_Flag)
719 OR ( (Recinfo.ship_to_site_flag IS NULL)
720 AND (X_Ship_To_Site_Flag IS NULL)))
721 AND ( (Recinfo.style = X_Style)
722 OR ( (Recinfo.style IS NULL)
723 AND (X_Style IS NULL)))
724 AND ( (Recinfo.tax_name = X_Tax_Name)
725 OR ( (Recinfo.tax_name IS NULL)
726 AND (X_Tax_Name IS NULL)))
727 AND ( (Recinfo.ece_tp_location_code = X_Ece_Tp_Location_Code)
728 OR ( (Recinfo.ece_tp_location_code IS NULL)
729 AND (X_Ece_Tp_Location_Code IS NULL)))
730 AND ( (Recinfo.telephone_number_1 = X_Telephone_Number_1)
731 OR ( (Recinfo.telephone_number_1 IS NULL)
732 AND (X_Telephone_Number_1 IS NULL)))
733 AND ( (Recinfo.telephone_number_2 = X_Telephone_Number_2)
734 OR ( (Recinfo.telephone_number_2 IS NULL)
735 AND (X_Telephone_Number_2 IS NULL)))
736 AND ( (Recinfo.telephone_number_3 = X_Telephone_Number_3)
737 OR ( (Recinfo.telephone_number_3 IS NULL)
738 AND (X_Telephone_Number_3 IS NULL)))
739 AND ( (Recinfo.town_or_city = X_Town_Or_City)
740 OR ( (Recinfo.town_or_city IS NULL)
741 AND (X_Town_Or_City IS NULL)))
742 AND ( (Recinfo.attribute_category = X_Attribute_Category)
743 OR ( (Recinfo.attribute_category IS NULL)
744 AND (X_Attribute_Category IS NULL)))
745 AND ( (Recinfo.attribute1 = X_Attribute1)
746 OR ( (Recinfo.attribute1 IS NULL)
747 AND (X_Attribute1 IS NULL)))
748 AND ( (Recinfo.attribute2 = X_Attribute2)
749 OR ( (Recinfo.attribute2 IS NULL)
750 AND (X_Attribute2 IS NULL)))
751 AND ( (Recinfo.attribute3 = X_Attribute3)
752 OR ( (Recinfo.attribute3 IS NULL)
753 AND (X_Attribute3 IS NULL)))
754 AND ( (Recinfo.attribute4 = X_Attribute4)
755 OR ( (Recinfo.attribute4 IS NULL)
756 AND (X_Attribute4 IS NULL)))
757 AND ( (Recinfo.attribute5 = X_Attribute5)
758 OR ( (Recinfo.attribute5 IS NULL)
759 AND (X_Attribute5 IS NULL)))
760 AND ( (Recinfo.attribute6 = X_Attribute6)
761 OR ( (Recinfo.attribute6 IS NULL)
762 AND (X_Attribute6 IS NULL)))
763 AND ( (Recinfo.attribute7 = X_Attribute7)
764 OR ( (Recinfo.attribute7 IS NULL)
765 AND (X_Attribute7 IS NULL)))
766 AND ( (Recinfo.attribute8 = X_Attribute8)
767 OR ( (Recinfo.attribute8 IS NULL)
768 AND (X_Attribute8 IS NULL)))
769 AND ( (Recinfo.attribute9 = X_Attribute9)
770 OR ( (Recinfo.attribute9 IS NULL)
771 AND (X_Attribute9 IS NULL)))
772 AND ( (Recinfo.attribute10 = X_Attribute10)
773 OR ( (Recinfo.attribute10 IS NULL)
774 AND (X_Attribute10 IS NULL)))
775 AND ( (Recinfo.attribute11 = X_Attribute11)
776 OR ( (Recinfo.attribute11 IS NULL)
777 AND (X_Attribute11 IS NULL)))
778 AND ( (Recinfo.attribute12 = X_Attribute12)
779 OR ( (Recinfo.attribute12 IS NULL)
780 AND (X_Attribute12 IS NULL)))
781 AND ( (Recinfo.attribute13 = X_Attribute13)
782 OR ( (Recinfo.attribute13 IS NULL)
783 AND (X_Attribute13 IS NULL)))
784 AND ( (Recinfo.attribute14 = X_Attribute14)
785 OR ( (Recinfo.attribute14 IS NULL)
786 AND (X_Attribute14 IS NULL)))
787 AND ( (Recinfo.attribute15 = X_Attribute15)
788 OR ( (Recinfo.attribute15 IS NULL)
789 AND (X_Attribute15 IS NULL)))
790 AND ( (Recinfo.attribute16 = X_Attribute16)
791 OR ( (Recinfo.attribute16 IS NULL)
792 AND (X_Attribute16 IS NULL)))
793 AND ( (Recinfo.attribute17 = X_Attribute17)
794 OR ( (Recinfo.attribute17 IS NULL)
795 AND (X_Attribute17 IS NULL)))
796 AND ( (Recinfo.attribute18 = X_Attribute18)
797 OR ( (Recinfo.attribute18 IS NULL)
798 AND (X_Attribute18 IS NULL)))
799 AND ( (Recinfo.attribute19 = X_Attribute19)
800 OR ( (Recinfo.attribute19 IS NULL)
801 AND (X_Attribute19 IS NULL)))
802 AND ( (Recinfo.attribute20 = X_Attribute20)
803 OR ( (Recinfo.attribute20 IS NULL)
804 AND (X_Attribute20 IS NULL)))
805 AND ( (Recinfo.global_attribute_category = X_Global_Attribute_Category)
806 OR ( (Recinfo.global_attribute_category IS NULL)
807 AND (X_Global_Attribute_Category IS NULL)))
808
809 AND ( (Recinfo.global_attribute1 = X_Global_Attribute1)
810 OR ( (Recinfo.global_attribute1 IS NULL)
811 AND (X_Global_Attribute1 IS NULL)))
812
813 AND ( (Recinfo.global_attribute2 = X_Global_Attribute2)
814 OR ( (Recinfo.global_attribute2 IS NULL)
815 AND (X_Global_Attribute2 IS NULL)))
816
817 AND ( (Recinfo.global_attribute3 = X_Global_Attribute3)
818 OR ( (Recinfo.global_attribute3 IS NULL)
819 AND (X_Global_Attribute3 IS NULL)))
820
821 AND ( (Recinfo.global_attribute4 = X_Global_Attribute4)
822 OR ( (Recinfo.global_attribute4 IS NULL)
823 AND (X_GLobal_Attribute4 IS NULL)))
824
825 AND ( (Recinfo.global_attribute5 = X_Global_Attribute5)
826 OR ( (Recinfo.global_attribute5 IS NULL)
827 AND (X_Global_Attribute5 IS NULL)))
828
829 AND ( (Recinfo.global_attribute6 = X_Global_Attribute6)
830 OR ( (Recinfo.global_attribute6 IS NULL)
831 AND (X_Global_Attribute6 IS NULL)))
832
833 AND ( (Recinfo.global_attribute7 = X_Global_Attribute7)
834 OR ( (Recinfo.global_attribute7 IS NULL)
835 AND (X_Global_Attribute7 IS NULL)))
836
837 AND ( (Recinfo.global_attribute8 = X_Global_Attribute8)
838 OR ( (Recinfo.global_attribute8 IS NULL)
839 AND (X_Global_Attribute8 IS NULL)))
840
841 AND ( (Recinfo.global_attribute9 = X_Global_Attribute9)
842 OR ( (Recinfo.global_attribute9 IS NULL)
843 AND (X_Global_Attribute9 IS NULL)))
844
845 AND ( (Recinfo.global_attribute10 = X_Global_Attribute10)
846 OR ( (Recinfo.global_attribute10 IS NULL)
847 AND (X_Global_Attribute10 IS NULL)))
848
849 AND ( (Recinfo.global_attribute11 = X_Global_Attribute11)
850 OR ( (Recinfo.global_attribute11 IS NULL)
851 AND (X_Global_Attribute11 IS NULL)))
852
853 AND ( (Recinfo.global_attribute12 = X_Global_Attribute12)
854 OR ( (Recinfo.global_attribute12 IS NULL)
855 AND (X_Global_Attribute12 IS NULL)))
856
857 AND ( (Recinfo.global_attribute13 = X_Global_Attribute13)
858 OR ( (Recinfo.global_attribute13 IS NULL)
859 AND (X_Global_Attribute13 IS NULL)))
860
861 AND ( (Recinfo.global_attribute14 = X_Global_Attribute14)
862 OR ( (Recinfo.global_attribute14 IS NULL)
863 AND (X_Global_Attribute14 IS NULL)))
864
865 AND ( (Recinfo.global_attribute15 = X_Global_Attribute15)
866 OR ( (Recinfo.global_attribute15 IS NULL)
867 AND (X_Global_Attribute15 IS NULL)))
868
869 AND ( (Recinfo.global_attribute16 = X_Global_Attribute16)
870 OR ( (Recinfo.global_attribute16 IS NULL)
871 AND (X_Global_Attribute16 IS NULL)))
872
873 AND ( (Recinfo.global_attribute17 = X_Global_Attribute17)
874 OR ( (Recinfo.global_attribute17 IS NULL)
875 AND (X_Global_Attribute17 IS NULL)))
876
877 AND ( (Recinfo.global_attribute18 = X_Global_Attribute18)
878 OR ( (Recinfo.global_attribute18 IS NULL)
879 AND (X_Global_Attribute18 IS NULL)))
880
881 AND ( (Recinfo.global_attribute19 = X_Global_Attribute19)
882 OR ( (Recinfo.global_attribute19 IS NULL)
883 AND (X_Global_Attribute19 IS NULL)))
884
885 AND ( (Recinfo.global_attribute20 = X_Global_Attribute20)
886 OR ( (Recinfo.global_attribute20 IS NULL)
887 AND (X_Global_Attribute20 IS NULL)))
888
889
890 AND ( (Recinfo.loc_information17 = X_Loc_Information17)
891 OR ( (Recinfo.loc_information17 IS NULL)
892 AND (X_Loc_information17 IS NULL)))
893
894 AND ( (Recinfo.loc_information18 = X_Loc_Information18)
895 OR ( (Recinfo.loc_information18 IS NULL)
896 AND (X_Loc_information18 IS NULL)))
897
898 AND ( (Recinfo.loc_information19 = X_Loc_Information19)
899 OR ( (Recinfo.loc_information19 IS NULL)
900 AND (X_Loc_information19 IS NULL)))
901
902 AND ( (Recinfo.loc_information20 = X_Loc_Information20)
903 OR ( (Recinfo.loc_information20 IS NULL)
904 AND (X_Loc_information20 IS NULL)))
905
906 ) then
907
908 return;
909 else
910 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
911 APP_EXCEPTION.RAISE_EXCEPTION;
912 end if;
913 END Lock_Row;
914
915 PROCEDURE Update_Row(X_Rowid VARCHAR2,
916 X_Location_Id NUMBER,
917 X_Entered_By NUMBER,
918 X_Location_Code VARCHAR2,
919 X_Address_Line_1 VARCHAR2,
920 X_Address_Line_2 VARCHAR2,
921 X_Address_Line_3 VARCHAR2,
922 X_Bill_To_Site_Flag VARCHAR2,
923 X_Country VARCHAR2,
924 X_Description VARCHAR2,
925 X_Designated_Receiver_Id NUMBER,
926 X_In_Organization_Flag VARCHAR2,
927 X_Inactive_Date DATE,
928 X_Inventory_Organization_Id NUMBER,
929 X_Office_Site_Flag VARCHAR2,
930 X_Postal_Code VARCHAR2,
931 X_Receiving_Site_Flag VARCHAR2,
932 X_Region_1 VARCHAR2,
933 X_Region_2 VARCHAR2,
934 X_Region_3 VARCHAR2,
935 X_Ship_To_Location_Id IN OUT NOCOPY NUMBER,
936 X_Ship_To_Site_Flag VARCHAR2,
937 X_Style VARCHAR2,
938 X_Tax_Name VARCHAR2,
939 X_Ece_Tp_Location_Code VARCHAR2,
940 X_Telephone_Number_1 VARCHAR2,
941 X_Telephone_Number_2 VARCHAR2,
942 X_Telephone_Number_3 VARCHAR2,
943 X_Town_Or_City VARCHAR2,
944 X_Attribute_Category VARCHAR2,
945 X_Attribute1 VARCHAR2,
946 X_Attribute2 VARCHAR2,
947 X_Attribute3 VARCHAR2,
948 X_Attribute4 VARCHAR2,
949 X_Attribute5 VARCHAR2,
950 X_Attribute6 VARCHAR2,
951 X_Attribute7 VARCHAR2,
952 X_Attribute8 VARCHAR2,
953 X_Attribute9 VARCHAR2,
954 X_Attribute10 VARCHAR2,
955 X_Attribute11 VARCHAR2,
956 X_Attribute12 VARCHAR2,
957 X_Attribute13 VARCHAR2,
958 X_Attribute14 VARCHAR2,
959 X_Attribute15 VARCHAR2,
960 X_Attribute16 VARCHAR2,
961 X_Attribute17 VARCHAR2,
962 X_Attribute18 VARCHAR2,
963 X_Attribute19 VARCHAR2,
964 X_Attribute20 VARCHAR2,
965 X_Last_Update_Date DATE,
966 X_Last_Updated_By NUMBER,
967 X_Last_Update_Login NUMBER ,
968 X_C_Purchasing_Ins VARCHAR2,
969 X_C_Inventory_Ins VARCHAR2,
970 X_Ship_To_Location VARCHAR2,
971 X_Global_Attribute_Category VARCHAR2,
972 X_Global_Attribute1 VARCHAR2,
973 X_Global_Attribute2 VARCHAR2,
974 X_Global_Attribute3 VARCHAR2,
975 X_Global_Attribute4 VARCHAR2,
976 X_Global_Attribute5 VARCHAR2,
977 X_Global_Attribute6 VARCHAR2,
978 X_Global_Attribute7 VARCHAR2,
979 X_Global_Attribute8 VARCHAR2,
980 X_Global_Attribute9 VARCHAR2,
981 X_Global_Attribute10 VARCHAR2,
982 X_Global_Attribute11 VARCHAR2,
983 X_Global_Attribute12 VARCHAR2,
984 X_Global_Attribute13 VARCHAR2,
985 X_Global_Attribute14 VARCHAR2,
986 X_Global_Attribute15 VARCHAR2,
987 X_Global_Attribute16 VARCHAR2,
988 X_Global_Attribute17 VARCHAR2,
989 X_Global_Attribute18 VARCHAR2,
990 X_Global_Attribute19 VARCHAR2,
991 X_Global_Attribute20 VARCHAR2,
992 X_Loc_Information17 VARCHAR2,
993 X_Loc_Information18 VARCHAR2,
994 X_Loc_Information19 VARCHAR2,
995 X_Loc_Information20 VARCHAR2
996 ) IS
997 BEGIN
998 set_ship_to_location(X_C_Purchasing_Ins,
999 X_C_Inventory_Ins,
1000 X_Ship_To_Location,
1001 X_Location_Id,
1002 X_Ship_To_Location_Id,
1003 X_Location_Code);
1004 --
1005 UPDATE hr_locations
1006 SET
1007 location_id = X_Location_Id,
1008 entered_by = X_Entered_By,
1009 location_code = X_Location_Code,
1010 address_line_1 = X_Address_Line_1,
1011 address_line_2 = X_Address_Line_2,
1012 address_line_3 = X_Address_Line_3,
1013 bill_to_site_flag = X_Bill_To_Site_Flag,
1014 country = X_Country,
1015 description = X_Description,
1016 designated_receiver_id = X_Designated_Receiver_Id,
1017 in_organization_flag = X_In_Organization_Flag,
1018 inactive_date = X_Inactive_Date,
1019 inventory_organization_id = X_Inventory_Organization_Id,
1020 office_site_flag = X_Office_Site_Flag,
1021 postal_code = X_Postal_Code,
1022 receiving_site_flag = X_Receiving_Site_Flag,
1023 region_1 = X_Region_1,
1024 region_2 = X_Region_2,
1025 region_3 = X_Region_3,
1026 ship_to_location_id = X_Ship_To_Location_Id,
1027 ship_to_site_flag = X_Ship_To_Site_Flag,
1028 style = X_Style,
1029 tax_name = X_Tax_Name,
1030 ece_tp_location_code = X_Ece_Tp_Location_Code,
1031 telephone_number_1 = X_Telephone_Number_1,
1032 telephone_number_2 = X_Telephone_Number_2,
1033 telephone_number_3 = X_Telephone_Number_3,
1034 town_or_city = X_Town_Or_City,
1035 attribute_category = X_Attribute_Category,
1036 attribute1 = X_Attribute1,
1037 attribute2 = X_Attribute2,
1038 attribute3 = X_Attribute3,
1039 attribute4 = X_Attribute4,
1040 attribute5 = X_Attribute5,
1041 attribute6 = X_Attribute6,
1042 attribute7 = X_Attribute7,
1043 attribute8 = X_Attribute8,
1044 attribute9 = X_Attribute9,
1045 attribute10 = X_Attribute10,
1046 attribute11 = X_Attribute11,
1047 attribute12 = X_Attribute12,
1048 attribute13 = X_Attribute13,
1049 attribute14 = X_Attribute14,
1050 attribute15 = X_Attribute15,
1051 attribute16 = X_Attribute16,
1052 attribute17 = X_Attribute17,
1053 attribute18 = X_Attribute18,
1054 attribute19 = X_Attribute19,
1055 attribute20 = X_Attribute20,
1056 last_update_date = X_Last_Update_Date,
1057 last_updated_by = X_Last_Updated_By,
1058 last_update_login = X_Last_Update_Login,
1059 global_attribute_category = X_Global_Attribute_Category,
1060 global_attribute1 = X_Global_Attribute1,
1061 global_attribute2 = X_Global_Attribute2,
1062 global_attribute3 = X_Global_Attribute3,
1063 global_attribute4 = X_Global_Attribute4,
1064 global_attribute5 = X_Global_Attribute5,
1065 global_attribute6 = X_Global_Attribute6,
1066 global_attribute7 = X_Global_Attribute7,
1067 global_attribute8 = X_Global_Attribute8,
1068 global_attribute9 = X_Global_Attribute9,
1069 global_attribute10 = X_Global_Attribute10,
1070 global_attribute11 = X_Global_Attribute11,
1071 global_attribute12 = X_Global_Attribute12,
1072 global_attribute13 = X_Global_Attribute13,
1073 global_attribute14 = X_Global_Attribute14,
1074 global_attribute15 = X_Global_Attribute15,
1075 global_attribute16 = X_Global_Attribute16,
1076 global_attribute17 = X_Global_Attribute17,
1077 global_attribute18 = X_Global_Attribute18,
1078 global_attribute19 = X_Global_Attribute19,
1079 global_attribute20 = X_Global_Attribute20,
1080 loc_information17 = X_Loc_Information17,
1081 loc_information18 = X_Loc_Information18,
1082 loc_information19 = X_Loc_Information19,
1083 loc_information20 = X_Loc_Information20
1084 WHERE rowid = chartorowid(X_rowid);
1085 --
1086 if (SQL%NOTFOUND) then
1087 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1088 hr_utility.set_message_token('PROCEDURE',
1089 'hr_locations_pkg.update_row');
1090 hr_utility.set_message_token('STEP','1');
1091 hr_utility.raise_error;
1092 end if;
1093 --
1094 END Update_Row;
1095 --
1096 END HR_LOCATIONS_PKG;