1 PACKAGE CSP_SHIP_TO_ADDRESS_PVT AUTHID CURRENT_USER AS
2 /*$Header: cspvstas.pls 120.3.12020000.5 2013/02/21 09:59:25 vmandava ship $*/
3 --Start of comments
4 --
5 -- API name : CSP_SHIP_TO_ADDRESS_PVT
6 -- Type : Private
7 -- Purpose : Add an inventory location.
8 -- Modify an inventory location.
9 -- Create site location, inventory location, party, party site, party site use, customer account,
10 -- customer profile, customer account site, customer account site use, location association
11 -- for a ship to address (customer address, engineer address, or a special ship to address).
12 -- It will create records in related tables so Order Entry can pick up the right ship to address and
13 -- customer at the later time.
14 -- Modification History
15 -- Person Date Comments
16 -- --------- ----------- ------------------------------------------
17 -- iouyang 01-May-2001 New
18 --
19
20
21 -- Create locations, party, party site, party site use, customer, customer account site,
22 -- customer account site use, ... OE can utilize these records later for Spares Orders.
23 -- All calls should go through ship_to_address_handler. It will create/modify inventory location
24 -- (ship to address) and create/modify customer profile, party site, site locationa, and site uses.
25
26 -- The address information passed in must be validated before passing in. It will not create
27 -- an inventory location if the address is not valid. Required fields are varied depends on
28 -- the country. The country field is used to check if there is a address passed in.
29
30 g_rs_cust_relation_id number;
31 g_inv_loc_id number;
32
33 PROCEDURE ship_to_address_handler
34 (p_task_assignment_id IN NUMBER
35 ,p_resource_type IN VARCHAR2
36 ,p_resource_id IN NUMBER
37 ,p_customer_id OUT NOCOPY NUMBER
38 ,p_location_id IN OUT NOCOPY NUMBER
39 ,p_style IN VARCHAR2
40 ,p_address_line_1 IN VARCHAR2
41 ,p_address_line_2 IN VARCHAR2
42 ,p_address_line_3 IN VARCHAR2
43 ,p_country IN VARCHAR2
44 ,p_postal_code IN VARCHAR2
45 ,p_region_1 IN VARCHAR2
46 ,p_region_2 IN VARCHAR2
47 ,p_region_3 IN VARCHAR2
48 ,p_town_or_city IN VARCHAR2
49 ,p_tax_name IN VARCHAR2
50 ,p_telephone_number_1 IN VARCHAR2
51 ,p_telephone_number_2 IN VARCHAR2
52 ,p_telephone_number_3 IN VARCHAR2
53 ,p_loc_information13 IN VARCHAR2
54 ,p_loc_information14 IN VARCHAR2
55 ,p_loc_information15 IN VARCHAR2
56 ,p_loc_information16 IN VARCHAR2
57 ,p_loc_information17 IN VARCHAR2
58 ,p_loc_information18 IN VARCHAR2
59 ,p_loc_information19 IN VARCHAR2
60 ,p_loc_information20 IN VARCHAR2
61 ,p_timezone IN VARCHAR2
62 ,p_primary_flag IN VARCHAR2
63 ,p_status IN VARCHAR2
64 ,p_object_version_number IN OUT NOCOPY NUMBER
65 ,p_api_version_number IN NUMBER
66 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
67 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
68 ,p_attribute_category IN VARCHAR2
69 ,p_attribute1 IN VARCHAR2
70 ,p_attribute2 IN VARCHAR2
71 ,p_attribute3 IN VARCHAR2
72 ,p_attribute4 IN VARCHAR2
73 ,p_attribute5 IN VARCHAR2
74 ,p_attribute6 IN VARCHAR2
75 ,p_attribute7 IN VARCHAR2
76 ,p_attribute8 IN VARCHAR2
77 ,p_attribute9 IN VARCHAR2
78 ,p_attribute10 IN VARCHAR2
79 ,p_attribute11 IN VARCHAR2
80 ,p_attribute12 IN VARCHAR2
81 ,p_attribute13 IN VARCHAR2
82 ,p_attribute14 IN VARCHAR2
83 ,p_attribute15 IN VARCHAR2
84 ,p_attribute16 IN VARCHAR2
85 ,p_attribute17 IN VARCHAR2
86 ,p_attribute18 IN VARCHAR2
87 ,p_attribute19 IN VARCHAR2
88 ,p_attribute20 IN VARCHAR2
89 ,p_bill_to_create IN VARCHAR2 := 'Y'
90 ,p_province IN VARCHAR2 DEFAULT NULL
91 ,p_address_lines_phonetic IN VARCHAR2 DEFAULT NULL
92 ,p_address_line_4 IN VARCHAR2 DEFAULT NULL
93 ,x_return_status OUT NOCOPY VARCHAR2
94 ,x_msg_count OUT NOCOPY NUMBER
95 ,x_msg_data OUT NOCOPY VARCHAR2
96 );
97
98 PROCEDURE ship_to_address_handler
99 (p_resource_type IN VARCHAR2
100 ,p_resource_id IN NUMBER
101 ,p_location_id IN OUT NOCOPY NUMBER
102 ,p_timezone IN VARCHAR2
103 ,p_primary_flag IN VARCHAR2
104 ,p_status IN VARCHAR2
105 ,x_return_status OUT NOCOPY VARCHAR2
106 ,x_msg_count OUT NOCOPY NUMBER
107 ,x_msg_data OUT NOCOPY VARCHAR2
108 );
109
110
111 -- Create a new inventory location as a ship to address.
112 PROCEDURE do_create_ship_to_location
113 (p_location_id OUT NOCOPY NUMBER
114 ,p_style IN VARCHAR2
115 ,p_address_line_1 IN VARCHAR2
116 ,p_address_line_2 IN VARCHAR2
117 ,p_address_line_3 IN VARCHAR2
118 ,p_country IN VARCHAR2
119 ,p_postal_code IN VARCHAR2
120 ,p_region_1 IN VARCHAR2
121 ,p_region_2 IN VARCHAR2
122 ,p_region_3 IN VARCHAR2
123 ,p_town_or_city IN VARCHAR2
124 ,p_tax_name IN VARCHAR2
125 ,p_telephone_number_1 IN VARCHAR2
126 ,p_telephone_number_2 IN VARCHAR2
127 ,p_telephone_number_3 IN VARCHAR2
128 ,p_loc_information13 IN VARCHAR2
129 ,p_loc_information14 IN VARCHAR2
130 ,p_loc_information15 IN VARCHAR2
131 ,p_loc_information16 IN VARCHAR2
132 ,p_loc_information17 IN VARCHAR2
133 ,p_loc_information18 IN VARCHAR2
134 ,p_loc_information19 IN VARCHAR2
135 ,p_loc_information20 IN VARCHAR2
136 ,p_attribute_category IN VARCHAR2
137 ,p_attribute1 IN VARCHAR2
138 ,p_attribute2 IN VARCHAR2
139 ,p_attribute3 IN VARCHAR2
140 ,p_attribute4 IN VARCHAR2
141 ,p_attribute5 IN VARCHAR2
142 ,p_attribute6 IN VARCHAR2
143 ,p_attribute7 IN VARCHAR2
144 ,p_attribute8 IN VARCHAR2
145 ,p_attribute9 IN VARCHAR2
146 ,p_attribute10 IN VARCHAR2
147 ,p_attribute11 IN VARCHAR2
148 ,p_attribute12 IN VARCHAR2
149 ,p_attribute13 IN VARCHAR2
150 ,p_attribute14 IN VARCHAR2
151 ,p_attribute15 IN VARCHAR2
152 ,p_attribute16 IN VARCHAR2
153 ,p_attribute17 IN VARCHAR2
154 ,p_attribute18 IN VARCHAR2
155 ,p_attribute19 IN VARCHAR2
156 ,p_attribute20 IN VARCHAR2
157 ,p_object_version_number OUT NOCOPY NUMBER
158 ,x_return_status OUT NOCOPY VARCHAR2
159 ,x_msg_count OUT NOCOPY NUMBER
160 ,x_msg_data OUT NOCOPY VARCHAR2
161 );
162
163
164 -- Update an existing inventory location.
165 PROCEDURE do_update_ship_to_location
166 (p_location_id IN NUMBER
167 ,p_style IN VARCHAR2
168 ,p_address_line_1 IN VARCHAR2
169 ,p_address_line_2 IN VARCHAR2
170 ,p_address_line_3 IN VARCHAR2
171 ,p_country IN VARCHAR2
172 ,p_postal_code IN VARCHAR2
173 ,p_region_1 IN VARCHAR2
174 ,p_region_2 IN VARCHAR2
175 ,p_region_3 IN VARCHAR2
176 ,p_town_or_city IN VARCHAR2
177 ,p_tax_name IN VARCHAR2
178 ,p_telephone_number_1 IN VARCHAR2
179 ,p_telephone_number_2 IN VARCHAR2
180 ,p_telephone_number_3 IN VARCHAR2
181 ,p_loc_information13 IN VARCHAR2
182 ,p_loc_information14 IN VARCHAR2
183 ,p_loc_information15 IN VARCHAR2
184 ,p_loc_information16 IN VARCHAR2
185 ,p_loc_information17 IN VARCHAR2
186 ,p_loc_information18 IN VARCHAR2
187 ,p_loc_information19 IN VARCHAR2
188 ,p_loc_information20 IN VARCHAR2
189 ,p_attribute_category IN VARCHAR2
190 ,p_attribute1 IN VARCHAR2
191 ,p_attribute2 IN VARCHAR2
192 ,p_attribute3 IN VARCHAR2
193 ,p_attribute4 IN VARCHAR2
194 ,p_attribute5 IN VARCHAR2
195 ,p_attribute6 IN VARCHAR2
196 ,p_attribute7 IN VARCHAR2
197 ,p_attribute8 IN VARCHAR2
198 ,p_attribute9 IN VARCHAR2
199 ,p_attribute10 IN VARCHAR2
200 ,p_attribute11 IN VARCHAR2
201 ,p_attribute12 IN VARCHAR2
202 ,p_attribute13 IN VARCHAR2
203 ,p_attribute14 IN VARCHAR2
204 ,p_attribute15 IN VARCHAR2
205 ,p_attribute16 IN VARCHAR2
206 ,p_attribute17 IN VARCHAR2
207 ,p_attribute18 IN VARCHAR2
208 ,p_attribute19 IN VARCHAR2
209 ,p_attribute20 IN VARCHAR2
210 ,p_object_version_number IN OUT NOCOPY NUMBER
211 ,x_return_status OUT NOCOPY VARCHAR2
212 ,x_msg_count OUT NOCOPY NUMBER
213 ,x_msg_data OUT NOCOPY VARCHAR2
214 );
215
216
217 -- Create rsource and customer relationship
218 PROCEDURE do_rs_cust_relations
219 (p_resource_type IN VARCHAR2
220 ,p_resource_id IN NUMBER
221 ,p_customer_id IN NUMBER
222 );
223
224
225
226 -- Create site use and link it to an inventory location.
227 PROCEDURE do_create_site_use
228 (p_customer_id IN NUMBER
229 ,p_party_id IN NUMBER
230 ,p_address_id IN NUMBER
231 ,p_location_id IN NUMBER
232 ,p_inv_location_id IN NUMBER
233 ,p_primary_flag IN VARCHAR2
234 ,p_status IN VARCHAR2
235 ,p_bill_to_create IN VARCHAR2 := 'Y'
236 ,x_return_status OUT NOCOPY VARCHAR2
237 ,x_msg_count OUT NOCOPY NUMBER
238 ,x_msg_data OUT NOCOPY VARCHAR2
239 );
240
241
242 -- Update site use.
243 PROCEDURE do_update_site_use
244 (p_site_use_id IN NUMBER
245 ,p_primary_flag IN VARCHAR2
246 ,p_status IN VARCHAR2
247 ,p_customer_id IN NUMBER
248 ,p_inv_location_id IN NUMBER
249 ,x_return_status OUT NOCOPY VARCHAR2
250 ,x_msg_count OUT NOCOPY NUMBER
251 ,x_msg_data OUT NOCOPY VARCHAR2 );
252
253
254 -- This procedure will create a new inventory location and link the
255 -- site use id passed in to the new inventory location.
256 -- Before calling this proceudre, make sure the site_use is not linked to
257 -- any inventory location yet.
258 --
259 PROCEDURE site_to_invloc_linkage
260 (p_customer_id IN NUMBER
261 ,p_address_id IN NUMBER
262 ,p_site_use_id IN NUMBER
263 ,p_location_id OUT NOCOPY NUMBER
264 ,p_style IN VARCHAR2
265 ,p_address_line_1 IN VARCHAR2
266 ,p_address_line_2 IN VARCHAR2
267 ,p_address_line_3 IN VARCHAR2
268 ,p_country IN VARCHAR2
269 ,p_postal_code IN VARCHAR2
270 ,p_region_1 IN VARCHAR2
271 ,p_region_2 IN VARCHAR2
272 ,p_region_3 IN VARCHAR2
273 ,p_town_or_city IN VARCHAR2
274 ,p_tax_name IN VARCHAR2
275 ,p_telephone_number_1 IN VARCHAR2
276 ,p_telephone_number_2 IN VARCHAR2
277 ,p_telephone_number_3 IN VARCHAR2
278 ,p_loc_information13 IN VARCHAR2
279 ,p_loc_information14 IN VARCHAR2
280 ,p_loc_information15 IN VARCHAR2
281 ,p_loc_information16 IN VARCHAR2
282 ,p_loc_information17 IN VARCHAR2
283 ,p_loc_information18 IN VARCHAR2
284 ,p_loc_information19 IN VARCHAR2
285 ,p_loc_information20 IN VARCHAR2
286 ,p_api_version_number IN NUMBER
287 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
288 ,p_attribute_category IN VARCHAR2
289 ,p_attribute1 IN VARCHAR2
290 ,p_attribute2 IN VARCHAR2
291 ,p_attribute3 IN VARCHAR2
292 ,p_attribute4 IN VARCHAR2
293 ,p_attribute5 IN VARCHAR2
294 ,p_attribute6 IN VARCHAR2
295 ,p_attribute7 IN VARCHAR2
296 ,p_attribute8 IN VARCHAR2
297 ,p_attribute9 IN VARCHAR2
298 ,p_attribute10 IN VARCHAR2
299 ,p_attribute11 IN VARCHAR2
300 ,p_attribute12 IN VARCHAR2
301 ,p_attribute13 IN VARCHAR2
302 ,p_attribute14 IN VARCHAR2
303 ,p_attribute15 IN VARCHAR2
304 ,p_attribute16 IN VARCHAR2
305 ,p_attribute17 IN VARCHAR2
306 ,p_attribute18 IN VARCHAR2
307 ,p_attribute19 IN VARCHAR2
308 ,p_attribute20 IN VARCHAR2
309 ,x_return_status OUT NOCOPY VARCHAR2
310 ,x_msg_count OUT NOCOPY NUMBER
311 ,x_msg_data OUT NOCOPY VARCHAR2
312 );
313 PROCEDURE call_internal_hook (
314 p_package_name IN VARCHAR2,
315 p_api_name IN VARCHAR2,
316 p_processing_type IN VARCHAR2,
317 x_return_status OUT NOCOPY VARCHAR2
318 );
319
320 PROCEDURE cust_inv_loc_link
321 ( p_api_version IN NUMBER
322 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
323 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
324 ,px_location_id IN OUT NOCOPY NUMBER
325 ,p_party_site_id IN NUMBER
326 ,p_cust_account_id IN NUMBER
327 ,p_customer_id IN NUMBER
328 ,p_org_id IN NUMBER := NULL
329 ,p_attribute_category IN VARCHAR2
330 ,p_attribute1 IN VARCHAR2
331 ,p_attribute2 IN VARCHAR2
332 ,p_attribute3 IN VARCHAR2
333 ,p_attribute4 IN VARCHAR2
334 ,p_attribute5 IN VARCHAR2
335 ,p_attribute6 IN VARCHAR2
336 ,p_attribute7 IN VARCHAR2
337 ,p_attribute8 IN VARCHAR2
338 ,p_attribute9 IN VARCHAR2
339 ,p_attribute10 IN VARCHAR2
340 ,p_attribute11 IN VARCHAR2
341 ,p_attribute12 IN VARCHAR2
342 ,p_attribute13 IN VARCHAR2
343 ,p_attribute14 IN VARCHAR2
344 ,p_attribute15 IN VARCHAR2
345 ,p_attribute16 IN VARCHAR2
346 ,p_attribute17 IN VARCHAR2
347 ,p_attribute18 IN VARCHAR2
348 ,p_attribute19 IN VARCHAR2
349 ,p_attribute20 IN VARCHAR2
350 ,x_return_status OUT NOCOPY VARCHAR2
351 ,x_msg_count OUT NOCOPY NUMBER
352 ,x_msg_data OUT NOCOPY VARCHAR2
353 );
354
355 -- bug # 8333969
356 PROCEDURE rs_primary_ship_to_addr
357 (
358 p_api_version IN NUMBER
359 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
360 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
361 ,p_rs_type IN VARCHAR2
362 ,p_rs_id IN NUMBER
363 ,p_rs_site_use_id IN NUMBER
364 ,x_return_status OUT NOCOPY VARCHAR2
365 ,x_msg_count OUT NOCOPY NUMBER
366 ,x_msg_data OUT NOCOPY VARCHAR2
367 );
368
369 PROCEDURE rs_inactivate_ship_to
370 (
371 p_api_version IN NUMBER
372 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
373 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
374 ,p_rs_type IN VARCHAR2
375 ,p_rs_id IN NUMBER
376 ,p_rs_site_use_id IN NUMBER
377 ,x_return_status OUT NOCOPY VARCHAR2
378 ,x_msg_count OUT NOCOPY NUMBER
379 ,x_msg_data OUT NOCOPY VARCHAR2
380 );
381
382 procedure copy_hz_cust_site (
383 p_src_org_id IN NUMBER
384 ,p_dest_org_id IN NUMBER
385 ,p_cust_site_id IN NUMBER
386 ,p_hr_location_id IN NUMBER
387 ,p_customer_id IN NUMBER
388 ,x_return_status OUT NOCOPY VARCHAR2
389 ,x_msg_count OUT NOCOPY NUMBER
390 ,x_msg_data OUT NOCOPY VARCHAR2
391 );
392
393 END CSP_SHIP_TO_ADDRESS_PVT;