[Home] [Help]
PACKAGE BODY: APPS.POR_LOAD_LOCATION
Source
1 PACKAGE BODY POR_LOAD_LOCATION as
2 /* $Header: PORLLOCB.pls 115.10 2004/05/25 23:31:34 rwidjaja ship $ */
3
4 PROCEDURE insert_update_location_info (
5 x_location_code IN VARCHAR2,
6 x_business_grp_name IN VARCHAR2,
7 x_effective_date IN DATE,
8 x_description IN VARCHAR2,
9 x_address_style IN VARCHAR2,
10 x_address_line_1 IN VARCHAR2,
11 x_address_line_2 IN VARCHAR2,
12 x_address_line_3 IN VARCHAR2,
13 x_city IN VARCHAR2,
14 x_state IN VARCHAR2,
15 x_county IN VARCHAR2,
16 x_country IN VARCHAR2,
17 x_postal_code IN VARCHAR2,
18 x_telephone_number_1 IN VARCHAR2,
19 x_telephone_number_2 IN VARCHAR2,
20 x_shipToLocation IN VARCHAR2,
21 x_ship_to_flag IN VARCHAR2,
22 x_bill_to_flag IN VARCHAR2,
23 x_receiving_site IN VARCHAR2,
24 x_office_site_flag IN VARCHAR2,
25 x_inv_org IN VARCHAR2,
26 x_tax_name IN VARCHAR2)
27 IS
28
29 l_location_id NUMBER;
30 l_object_version_number NUMBER;
31 l_business_grp_id NUMBER;
32 l_inventory_org_id NUMBER;
33 l_operating_unit_id NUMBER;
34 l_ship_to_loc_id NUMBER;
35 l_address_style_code VARCHAR2(7);
36 l_country_code VARCHAR2(30);
37
38 BEGIN
39
40 l_business_grp_id := get_business_group_id(x_business_grp_name);
41
42 IF (x_shipToLocation IS NOT NULL) THEN
43
44 l_ship_to_loc_id := get_location_id(x_shipToLocation);
45
46 END IF;
47
48 l_inventory_org_id := get_organization_id(x_inv_org);
49
50 l_address_style_code := get_address_style_code( x_address_style);
51
52 l_country_code := get_country_code(x_country);
53
54 select org_id into l_operating_unit_id from financials_system_parameters;
55
56 IF (NOT (get_location_exists(x_location_code,l_business_grp_id))) THEN
57
58 hr_location_api.create_location(
59 p_location_code => x_location_code,
60 p_effective_date => x_effective_date,
61 p_description => x_description,
62 p_address_line_1 => x_address_line_1,
63 p_address_line_2 => x_address_line_2,
64 p_address_line_3 => x_address_line_3,
65 p_country => l_country_code,
66 p_town_or_city => x_city,
67 p_region_2 => x_state,
68 p_region_1 => x_county,
69 p_postal_code => x_postal_code,
70 p_telephone_number_1 => x_telephone_number_1,
71 p_telephone_number_2 => x_telephone_number_2,
72 p_style => l_address_style_code,
73 p_ship_to_site_flag => x_ship_to_flag,
74 p_ship_to_location_id => l_ship_to_loc_id,
75 p_bill_to_site_flag => x_bill_to_flag,
76 p_receiving_site_flag => x_receiving_site,
77 p_office_site_flag => x_office_site_flag,
78 p_inventory_organization_id => l_inventory_org_id,
79 p_operating_unit_id => l_operating_unit_id,
80 p_tax_name => x_tax_name,
81 p_location_id => l_location_id,
82 p_business_group_id => l_business_grp_id,
83 p_object_version_number => l_object_version_number) ;
84
85 ELSE
86 get_location_information (x_location_code, l_location_id, l_object_version_number);
87
88 hr_location_api.update_location(
89 p_location_code => x_location_code,
90 p_effective_date => x_effective_date,
91 p_description => x_description,
92 p_address_line_1 => x_address_line_1,
93 p_address_line_2 => x_address_line_2,
94 p_address_line_3 => x_address_line_3,
95 p_country => l_country_code,
96 p_town_or_city => x_city,
97 p_region_2 => x_state,
98 p_region_1 => x_county,
99 p_postal_code => x_postal_code,
100 p_telephone_number_1 => x_telephone_number_1,
101 p_telephone_number_2 => x_telephone_number_2,
102 p_style => l_address_style_code,
103 p_ship_to_site_flag => x_ship_to_flag,
104 p_ship_to_location_id => l_ship_to_loc_id,
105 p_bill_to_site_flag => x_bill_to_flag,
106 p_receiving_site_flag => x_receiving_site,
107 p_office_site_flag => x_office_site_flag,
108 p_inventory_organization_id => l_inventory_org_id,
109 p_operating_unit_id => l_operating_unit_id,
110 p_tax_name => x_tax_name,
111 p_location_id => l_location_id,
112 p_object_version_number => l_object_version_number) ;
113 END IF;
114
115 COMMIT;
116
117 EXCEPTION
118
119 WHEN OTHERS THEN
120
121 RAISE;
122 END insert_update_location_info;
123
124 FUNCTION get_location_exists (p_location_code IN VARCHAR2, l_business_grp_id IN NUMBER) RETURN BOOLEAN IS
125 l_count NUMBER;
126
127 BEGIN
128
129
130 SELECT 1 INTO l_count
131 FROM hr_locations_all
132 WHERE location_code = p_location_code AND business_group_id = l_business_grp_id;
133
134
135 RETURN true;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 RETURN false;
140
141 END get_location_exists;
142
143 FUNCTION get_business_group_id (p_business_group_name IN VARCHAR2) RETURN NUMBER IS
144 l_business_group_id NUMBER;
145 BEGIN
146
147 SELECT business_group_id INTO l_business_group_id
148 FROM per_business_groups
149 WHERE name = p_business_group_name;
150
151 RETURN l_business_group_id;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 RETURN -1;
155
156 END get_business_group_id;
157
158 PROCEDURE get_location_information (p_location_code IN VARCHAR2, p_location_id OUT NOCOPY NUMBER, p_object_version_number OUT NOCOPY NUMBER) IS
159
160 BEGIN
161
162 SELECT location_id, object_version_number
163 INTO p_location_id, p_object_version_number
164 FROM hr_locations_all
165 WHERE location_code = p_location_code;
166
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 RAISE;
170
171 END get_location_information;
172
173 FUNCTION get_organization_id (p_organization_name IN VARCHAR2) RETURN NUMBER IS
174 l_organization_id NUMBER;
175 BEGIN
176
177 -- this query is written to replace the org_organization_definitions view, which
178 -- causing a performance issue.
179 SELECT HOU.organization_id
180 INTO l_organization_id
181 FROM HR_ORGANIZATION_UNITS HOU,
182 HR_ORGANIZATION_INFORMATION HOI1,
183 HR_ORGANIZATION_INFORMATION HOI2,
184 MTL_PARAMETERS MP,
185 GL_SETS_OF_BOOKS GSOB
186 WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
187 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
188 AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
189 AND HOI1.ORG_INFORMATION1 = 'INV'
190 AND HOI1.ORG_INFORMATION2 = 'Y'
191 AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
192 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
193 AND HOI2.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
194 AND HOU.NAME = p_organization_name;
195
196 RETURN l_organization_id;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 RETURN NULL;
200
201 END get_organization_id;
202
203 FUNCTION get_location_id (p_location_code IN VARCHAR2) RETURN NUMBER IS
204 l_location_id NUMBER;
205 BEGIN
206
207 SELECT location_id INTO l_location_id
208 FROM hr_locations_all
209 WHERE location_code = p_location_code;
210
211 RETURN l_location_id;
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 RETURN NULL;
215
216 END get_location_id;
217
218
219 FUNCTION get_address_style_code (p_address_style IN VARCHAR2) RETURN VARCHAR2 IS
220 l_address_style_code VARCHAR2(7);
221 BEGIN
222
223 SELECT descriptive_flex_context_code INTO l_address_style_code
224 FROM fnd_descr_flex_contexts_vl
225 WHERE descriptive_flexfield_name = 'Address Location'
226 AND enabled_flag = 'Y'
227 AND descriptive_flex_context_code not in ('Global Data Elements')
228 AND (hr_general.chk_geocodes_installed = 'Y' or
229 descriptive_flex_context_code not in ('CA','US'))
230 AND descriptive_flex_context_name = p_address_style;
231
232 RETURN l_address_style_code;
233
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 RETURN NULL;
237
238 END get_address_style_code;
239
240
241 FUNCTION get_country_code (p_country_name IN VARCHAR2) RETURN VARCHAR2 IS
242 l_country_code VARCHAR2(30);
243 BEGIN
244
245 SELECT territory_code INTO l_country_code
246 FROM fnd_territories_vl
247 WHERE territory_short_name = p_country_name;
248
249 RETURN l_country_code;
250
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 RETURN NULL;
254
255 END get_country_code;
256
257 END POR_LOAD_LOCATION;