PACKAGE BODY: APPS.HR_LOCATION_RECORD

Source

1 PACKAGE BODY hr_location_record AS
2 /* $Header: hrlocqry.pkb 120.0.12010000.4 2009/01/22 09:45:52 srgnanas noship $ */
3 PROCEDURE get_location_details(p_query_options  IN  location_input_rectype
4                               ,p_locations      OUT NOCOPY location_tabletype)
5 IS
6 CURSOR  c_location(p_location_id hr_locations.location_id%TYPE
7                   ,p_start_location_id hr_locations.location_id%TYPE
8                   ,p_end_location_id hr_locations.location_id%TYPE
9                   ,p_bg_id       hr_locations.business_group_id%TYPE) IS
10 SELECT  loc.location_id,
11         lot.location_code,
12         'HR',
13         loc.business_group_id + 0 business_group_id,
14         lot.description,
15         loc.ship_to_location_id,
16         loc.ship_to_site_flag,
17         loc.receiving_site_flag,
18         loc.bill_to_site_flag,
19         loc.in_organization_flag,
20         loc.office_site_flag,
21         loc.designated_receiver_id,
22         loc.inventory_organization_id,
23         loc.tax_name,
24         loc.inactive_date,
25         loc.style,
26         loc.address_line_1,
27         loc.address_line_2,
28         loc.address_line_3,
29         loc.town_or_city,
30         loc.country,
31         loc.postal_code,
32         loc.region_1,
33         loc.region_2,
34         loc.region_3,
35         loc.telephone_number_1,
36         loc.telephone_number_2,
37         loc.telephone_number_3,
38         loc.loc_information13,
39         loc.loc_information14,
40         loc.loc_information15,
41         loc.loc_information16,
42         loc.loc_information17,
43         loc.loc_information18,
44         loc.loc_information19,
45         loc.loc_information20,
46         loc.attribute_category,
47         loc.attribute1,
48         loc.attribute2,
49         loc.attribute3,
50         loc.attribute4,
51         loc.attribute5,
52         loc.attribute6,
53         loc.attribute7,
54         loc.attribute8,
55         loc.attribute9,
56         loc.attribute10,
57         loc.attribute11,
58         loc.attribute12,
59         loc.attribute13,
60         loc.attribute14,
61         loc.attribute15,
62         loc.attribute16,
63         loc.attribute17,
64         loc.attribute18,
65         loc.attribute19,
66         loc.attribute20,
67         loc.global_attribute_category,
68         loc.global_attribute1,
69         loc.global_attribute2,
70         loc.global_attribute3,
71         loc.global_attribute4,
72         loc.global_attribute5,
73         loc.global_attribute6,
74         loc.global_attribute7,
75         loc.global_attribute8,
76         loc.global_attribute9,
77         loc.global_attribute10,
78         loc.global_attribute11,
79         loc.global_attribute12,
80         loc.global_attribute13,
81         loc.global_attribute14,
82         loc.global_attribute15,
83         loc.global_attribute16,
84         loc.global_attribute17,
85         loc.global_attribute18,
86         loc.global_attribute19,
87         loc.global_attribute20,
88         loc.last_update_date,
89         loc.last_updated_by,
90         loc.last_update_login,
91         loc.created_by,
92         loc.creation_date,
93         loc.entered_by,
94         loc.tp_header_id,
95         loc.ece_tp_location_code,
96         loc.object_version_number,
97         loc.legal_address_flag,
98         loc.timezone_code
99 FROM    hr_locations_all loc,
100         hr_locations_all_tl lot
101 WHERE   nvl(p_location_id, loc.LOCATION_ID) = loc.LOCATION_ID
102 AND     loc.LOCATION_ID BETWEEN nvl(p_start_location_id, loc.LOCATION_ID)
103         AND  nvl(p_end_location_id, loc.LOCATION_ID)
104 AND     ((p_bg_id IS NULL) OR (p_bg_id IS NOT NULL AND p_bg_id = loc.BUSINESS_GROUP_ID))
105 AND     loc.location_id = lot.location_id
106 AND     lot.language = userenv('LANG');
107 
108 l_location_rec      location_rectype;
109 l_location_tbl      location_tabletype;
110 l_count             NUMBER := 1;
111 BEGIN
112     l_location_tbl := location_tabletype();
113 
114     OPEN c_location(p_location_id => p_query_options.location_id
115                    ,p_start_location_id => p_query_options.start_location_id
116                    ,p_end_location_id => p_query_options.end_location_id
117                    ,p_bg_id       => p_query_options.business_group_id);
118 
119     LOOP
120         FETCH c_location INTO l_location_rec;
121         IF c_location%NOTFOUND THEN
122             EXIT;
123         END IF;
124         l_location_tbl.EXTEND(1);
125         l_location_tbl(l_count) := l_location_rec;
126         l_count := l_count + 1;
127     END LOOP;
128 
129     CLOSE c_location;
130 
131     p_locations := l_location_tbl;
132 END get_location_details;
133 END hr_location_record;