[Home] [Help]
PACKAGE BODY: APPS.CSP_SHIP_TO_ADDRESS_PVT
Source
1 PACKAGE BODY CSP_SHIP_TO_ADDRESS_PVT AS
2 /*$Header: cspvstab.pls 120.6.12000000.2 2007/04/12 22:15:54 jjalla ship $*/
3 ------------------------------------------------------------------------------------------------------------------------
4 --
5 -- Procedure Name : Main procedure name is ship_to_address_handler. All calling programs should call it directly.
6 -- It can handle the following situations:
7 -- 1. Create an inventory location.
8 -- 2. Modify an inventory location.
9 -- 3. Create site location, party, party site, party site use, customer account, customer profile,
10 -- customer account site, customer account site use, location association, ... for a ship to
11 -- address.
12 -- The ship to address (inventory location) will be set to be linked to a customer. Order Entry
13 -- will pick up the right ship to address and customer at a later time.
14 -- Version : 1.0
15 --
16 -- Modification History
17 -- Person Date Comments
18 -- --------- ----------- ------------------------------------------
19 -- iouyang 01-May-2001 New
20 --
21 ------------------------------------------------------------------------------------------------------------------------
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_SHIP_TO_ADDRESS_PUB';
24 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvstab.pls';
25
26
27 --------------------------------------------------------------------------------
28 --
29 -- Procedure Name : do_create_rs_cust_relation
30 -- Purpose : Create a relationship between a resource and a customer
31 -- in table csp_rs_cust_relations.
32 --
33 PROCEDURE do_rs_cust_relations
34 (p_resource_type IN VARCHAR2
35 ,p_resource_id IN NUMBER
36 ,p_customer_id IN NUMBER) IS
37
38 l_rs_cust_relation_id csp_rs_cust_relations.rs_cust_relation_id%TYPE := NULL;
39 l_resource_type csp_rs_cust_relations.resource_type%TYPE;
40 l_resource_id csp_rs_cust_relations.resource_id%TYPE;
41 l_customer_id csp_rs_cust_relations.customer_id%TYPE;
42 l_CREATED_BY number;
43 l_CREATION_DATE date;
44 l_ATTRIBUTE_CATEGORY varchar2(30);
45 l_ATTRIBUTE1 varchar2(150);
46 l_ATTRIBUTE2 varchar2(150);
47 l_ATTRIBUTE3 varchar2(150);
48 l_ATTRIBUTE4 varchar2(150);
49 l_ATTRIBUTE5 varchar2(150);
50 l_ATTRIBUTE6 varchar2(150);
51 l_ATTRIBUTE7 varchar2(150);
52 l_ATTRIBUTE8 varchar2(150);
53 l_ATTRIBUTE9 varchar2(150);
54 l_ATTRIBUTE10 varchar2(150);
55 l_ATTRIBUTE11 varchar2(150);
56 l_ATTRIBUTE12 varchar2(150);
57 l_ATTRIBUTE13 varchar2(150);
58 l_ATTRIBUTE14 varchar2(150);
59 l_ATTRIBUTE15 varchar2(150);
60
61 CURSOR l_rs_relation_csr IS
62 select rs_cust_relation_id,
63 customer_id,
64 created_by,
65 creation_date,
66 attribute_category,
67 attribute1,
68 attribute2,
69 attribute3,
70 attribute4,
71 attribute5,
72 attribute6,
73 attribute7,
74 attribute8,
75 attribute9,
76 attribute10,
77 attribute11,
78 attribute12,
79 attribute13,
80 attribute14,
81 attribute15
82 from csp_rs_cust_relations
83 where resource_type = p_resource_type and resource_id = p_resource_id;
84
85 BEGIN
86 csp_ship_to_address_pvt.g_rs_cust_relation_id := null;
87 OPEN l_rs_relation_csr;
88 FETCH l_rs_relation_csr INTO l_rs_cust_relation_id,
89 l_customer_id,
90 l_created_by,
91 l_creation_date,
92 l_attribute_category,
93 l_attribute1,
94 l_attribute2,
95 l_attribute3,
96 l_attribute4,
97 l_attribute5,
98 l_attribute6,
99 l_attribute7,
100 l_attribute8,
101 l_attribute9,
102 l_attribute10,
103 l_attribute11,
104 l_attribute12,
105 l_attribute13,
106 l_attribute14,
107 l_attribute15;
108 IF l_rs_relation_csr%FOUND THEN
109 CLOSE l_rs_relation_csr;
110 IF l_customer_id IS NULL THEN
111 csp_rs_cust_relations_pkg.update_row(
112 p_RS_CUST_RELATION_ID => l_rs_cust_relation_id,
113 p_RESOURCE_TYPE => p_resource_type,
114 p_RESOURCE_ID => p_resource_id,
115 p_CUSTOMER_ID => p_customer_id,
116 p_CREATED_BY => l_created_by,
117 p_CREATION_DATE => l_creation_date,
118 p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 1),
119 p_LAST_UPDATE_DATE => sysdate,
120 p_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, 1),
121 p_ATTRIBUTE_CATEGORY => l_attribute_category,
122 p_ATTRIBUTE1 => l_attribute1,
123 p_ATTRIBUTE2 => l_attribute2,
124 p_ATTRIBUTE3 => l_attribute3,
125 p_ATTRIBUTE4 => l_attribute4,
126 p_ATTRIBUTE5 => l_attribute5,
127 p_ATTRIBUTE6 => l_attribute6,
128 p_ATTRIBUTE7 => l_attribute7,
129 p_ATTRIBUTE8 => l_attribute8,
130 p_ATTRIBUTE9 => l_attribute9,
131 p_ATTRIBUTE10 => l_attribute10,
132 p_ATTRIBUTE11 => l_attribute11,
133 p_ATTRIBUTE12 => l_attribute12,
134 p_ATTRIBUTE13 => l_attribute13,
135 p_ATTRIBUTE14 => l_attribute14,
136 p_ATTRIBUTE15 => l_attribute15);
137 END IF;
138 ELSE
139 CLOSE l_rs_relation_csr;
140 csp_rs_cust_relations_pkg.insert_row(
141 px_RS_CUST_RELATION_ID => l_rs_cust_relation_id,
142 p_RESOURCE_TYPE => p_resource_type,
143 p_RESOURCE_ID => p_resource_id,
144 p_CUSTOMER_ID => p_customer_id,
145 p_CREATED_BY => nvl(fnd_global.user_id, 1),
146 p_CREATION_DATE => sysdate,
147 p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 1),
148 p_LAST_UPDATE_DATE => sysdate,
149 p_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, 1),
150 p_ATTRIBUTE_CATEGORY => NULL,
151 p_ATTRIBUTE1 => NULL,
152 p_ATTRIBUTE2 => NULL,
153 p_ATTRIBUTE3 => NULL,
154 p_ATTRIBUTE4 => NULL,
155 p_ATTRIBUTE5 => NULL,
156 p_ATTRIBUTE6 => NULL,
157 p_ATTRIBUTE7 => NULL,
158 p_ATTRIBUTE8 => NULL,
159 p_ATTRIBUTE9 => NULL,
160 p_ATTRIBUTE10 => NULL,
161 p_ATTRIBUTE11 => NULL,
162 p_ATTRIBUTE12 => NULL,
163 p_ATTRIBUTE13 => NULL,
164 p_ATTRIBUTE14 => NULL,
165 p_ATTRIBUTE15 => NULL);
166
167 END IF;
168 csp_ship_to_address_pvt.g_rs_cust_relation_id := l_rs_cust_relation_id;
169
170 END do_rs_cust_relations;
171
172
173
174 --------------------------------------------------------------------------------
175 --
176 -- Procedure Name : do_create_ship_to_location
177 -- Purpose : It takes location code, description, and address fields
178 -- to create a new inventory location.
179 -- Return location_id.
180 --
181 PROCEDURE do_create_ship_to_location
182 (p_location_id OUT NOCOPY NUMBER
183 ,p_style IN VARCHAR2
184 ,p_address_line_1 IN VARCHAR2
185 ,p_address_line_2 IN VARCHAR2
186 ,p_address_line_3 IN VARCHAR2
187 ,p_country IN VARCHAR2
188 ,p_postal_code IN VARCHAR2
189 ,p_region_1 IN VARCHAR2
190 ,p_region_2 IN VARCHAR2
191 ,p_region_3 IN VARCHAR2
192 ,p_town_or_city IN VARCHAR2
193 ,p_tax_name IN VARCHAR2
194 ,p_telephone_number_1 IN VARCHAR2
195 ,p_telephone_number_2 IN VARCHAR2
196 ,p_telephone_number_3 IN VARCHAR2
197 ,p_loc_information13 IN VARCHAR2
198 ,p_loc_information14 IN VARCHAR2
199 ,p_loc_information15 IN VARCHAR2
200 ,p_loc_information16 IN VARCHAR2
201 ,p_loc_information17 IN VARCHAR2
202 ,p_loc_information18 IN VARCHAR2
203 ,p_loc_information19 IN VARCHAR2
204 ,p_loc_information20 IN VARCHAR2
205 ,p_attribute_category IN VARCHAR2
206 ,p_attribute1 IN VARCHAR2
207 ,p_attribute2 IN VARCHAR2
208 ,p_attribute3 IN VARCHAR2
209 ,p_attribute4 IN VARCHAR2
210 ,p_attribute5 IN VARCHAR2
211 ,p_attribute6 IN VARCHAR2
212 ,p_attribute7 IN VARCHAR2
213 ,p_attribute8 IN VARCHAR2
214 ,p_attribute9 IN VARCHAR2
215 ,p_attribute10 IN VARCHAR2
216 ,p_attribute11 IN VARCHAR2
217 ,p_attribute12 IN VARCHAR2
218 ,p_attribute13 IN VARCHAR2
219 ,p_attribute14 IN VARCHAR2
220 ,p_attribute15 IN VARCHAR2
221 ,p_attribute16 IN VARCHAR2
222 ,p_attribute17 IN VARCHAR2
223 ,p_attribute18 IN VARCHAR2
224 ,p_attribute19 IN VARCHAR2
225 ,p_attribute20 IN VARCHAR2
226 ,p_object_version_number OUT NOCOPY NUMBER
227 ,x_return_status OUT NOCOPY VARCHAR2
228 ,x_msg_count OUT NOCOPY NUMBER
229 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
230
231 l_api_version_number CONSTANT NUMBER := 1.0;
232 l_api_name CONSTANT VARCHAR2(30) := 'do_create_ship_to_location';
233 EXCP_USER_DEFINED EXCEPTION;
234
235 l_validate BOOLEAN := false;
236 l_effective_date DATE := sysdate;
237 l_location_id hr_locations_all.location_id%TYPE;
238 l_language hr_locations_all_tl.language%TYPE;
239 l_location_code hr_locations_all.location_code%TYPE := NULL;
240 l_description hr_locations_all.description%TYPE := NULL;
241 l_bill_to_site_flag hr_locations_all.bill_to_site_flag%TYPE := 'Y';
242 l_ship_to_site_flag hr_locations_all.ship_to_site_flag%TYPE := 'Y';
243 l_tp_header_id hr_locations_all.tp_header_id%TYPE := NULL;
244 l_ece_tp_location_code hr_locations_all.ece_tp_location_code%TYPE := NULL;
245 l_designated_receiver_id hr_locations_all.designated_receiver_id%TYPE := NULL;
246 l_in_organization_flag hr_locations_all.in_organization_flag%TYPE := 'Y';
247 l_inactive_date hr_locations_all.inactive_date%TYPE := NULL;
248 l_operating_unit_id NUMBER := NULL;
249 l_inventory_organization_id hr_locations_all.inventory_organization_id%TYPE := NULL;
250 l_office_site_flag hr_locations_all.office_site_flag%TYPE := 'Y';
251 l_receiving_site_flag hr_locations_all.receiving_site_flag%TYPE := 'Y';
252 l_ship_to_location_id hr_locations_all.ship_to_location_id%TYPE := NULL;
253
254 BEGIN
255 SAVEPOINT do_create_ship_to_location_PUB;
256
257 -- Initialize
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259 select userenv('LANG') into l_language from dual;
260
261 -- Assign location code
262 select csp_location_id_s1.NEXTVAL into l_location_code from dual;
263 l_location_code := 'CSP' || l_location_code;
264
265 -- Default location description to address 1.
266 --l_description := p_address_line_1;
267
268 hr_location_api.create_location(
269 p_effective_date => l_effective_date,
270 --p_language_code => l_language,
271 p_location_code => l_location_code,
272 p_description => l_description,
273 p_tp_header_id => l_tp_header_id,
274 p_ece_tp_location_code => l_ece_tp_location_code,
275 p_address_line_1 => p_address_line_1,
276 p_address_line_2 => p_address_line_2,
277 p_address_line_3 => p_address_line_3,
278 p_bill_to_site_flag => l_bill_to_site_flag,
279 p_country => p_country,
280 p_designated_receiver_id => l_designated_receiver_id,
281 p_in_organization_flag => l_in_organization_flag,
282 p_inactive_date => l_inactive_date,
283 p_operating_unit_id => l_operating_unit_id,
284 p_inventory_organization_id => l_inventory_organization_id,
285 p_office_site_flag => l_office_site_flag,
286 p_postal_code => p_postal_code,
287 p_receiving_site_flag => l_receiving_site_flag,
288 p_region_1 => p_region_1,
289 p_region_2 => p_region_2,
290 p_region_3 => p_region_3,
291 p_ship_to_location_id => l_ship_to_location_id,
292 p_ship_to_site_flag => l_ship_to_site_flag,
293 p_style => p_style,
294 p_tax_name => p_tax_name,
295 p_telephone_number_1 => p_telephone_number_1,
296 p_telephone_number_2 => p_telephone_number_2,
297 p_telephone_number_3 => p_telephone_number_3,
298 p_town_or_city => p_town_or_city,
299 p_loc_information13 => p_loc_information13,
300 p_loc_information14 => p_loc_information14,
301 p_loc_information15 => p_loc_information15,
302 p_loc_information16 => p_loc_information16,
303 p_loc_information17 => p_loc_information17,
304 p_loc_information18 => p_loc_information18,
305 p_loc_information19 => p_loc_information19,
306 p_loc_information20 => p_loc_information20,
307 p_location_id => p_location_id,
308 p_object_version_number => p_object_version_number,
309 p_attribute1 => p_attribute1,
310 p_attribute2 => p_attribute2,
311 p_attribute3 => p_attribute3,
312 p_attribute4 => p_attribute4,
313 p_attribute5 => p_attribute5,
314 p_attribute6 => p_attribute6,
315 p_attribute7 => p_attribute7,
316 p_attribute8 => p_attribute8,
317 p_attribute9 => p_attribute9,
318 p_attribute10 => p_attribute10,
319 p_attribute11 => p_attribute11,
320 p_attribute12 => p_attribute12,
321 p_attribute13 => p_attribute13,
322 p_attribute14 => p_attribute14,
323 p_attribute15 => p_attribute15,
324 p_attribute16 => p_attribute16,
325 p_attribute17 => p_attribute17,
326 p_attribute18 => p_attribute18,
327 p_attribute19 => p_attribute19,
328 p_attribute20 => p_attribute20,
329 p_attribute_category => p_attribute_category
330 );
331
332 fnd_msg_pub.count_and_get
333 ( p_count => x_msg_count
334 , p_data => x_msg_data);
335
336 -- Exception Block
337 EXCEPTION
338 WHEN EXCP_USER_DEFINED THEN
339 Rollback to do_create_ship_to_location_PUB;
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
342 P_API_NAME => L_API_NAME
343 ,P_PKG_NAME => G_PKG_NAME
344 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
345 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
346 ,X_MSG_COUNT => x_MSG_COUNT
347 ,X_MSG_DATA => x_MSG_DATA
348 ,X_RETURN_STATUS => x_RETURN_STATUS);
349 WHEN FND_API.G_EXC_ERROR THEN
350 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
351 P_API_NAME => L_API_NAME
352 ,P_PKG_NAME => G_PKG_NAME
353 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
354 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
355 ,X_MSG_COUNT => x_MSG_COUNT
356 ,X_MSG_DATA => x_MSG_DATA
357 ,X_RETURN_STATUS => x_RETURN_STATUS);
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
360 P_API_NAME => L_API_NAME
361 ,P_PKG_NAME => G_PKG_NAME
362 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
363 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
364 ,X_MSG_COUNT => x_MSG_COUNT
365 ,X_MSG_DATA => x_MSG_DATA
366 ,X_RETURN_STATUS => x_RETURN_STATUS);
367 WHEN OTHERS THEN
368 Rollback to do_create_ship_to_location_PUB;
369 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
370 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, false);
371 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, false);
372 FND_MSG_PUB.ADD;
373 fnd_msg_pub.count_and_get
374 ( p_count => x_msg_count
375 , p_data => x_msg_data);
376 x_return_status := FND_API.G_RET_STS_ERROR;
377
378 END do_create_ship_to_location;
379
380
381 --------------------------------------------------------------------------------
382 --
383 -- Procedure Name : do_update_ship_to_location
384 -- Purpose : It updates the address fields based on the location id
385 -- passed in.
386 --
387 PROCEDURE do_update_ship_to_location
388 (p_location_id IN NUMBER
389 ,p_style IN VARCHAR2
390 ,p_address_line_1 IN VARCHAR2
391 ,p_address_line_2 IN VARCHAR2
392 ,p_address_line_3 IN VARCHAR2
393 ,p_country IN VARCHAR2
394 ,p_postal_code IN VARCHAR2
395 ,p_region_1 IN VARCHAR2
396 ,p_region_2 IN VARCHAR2
397 ,p_region_3 IN VARCHAR2
398 ,p_town_or_city IN VARCHAR2
399 ,p_tax_name IN VARCHAR2
400 ,p_telephone_number_1 IN VARCHAR2
401 ,p_telephone_number_2 IN VARCHAR2
402 ,p_telephone_number_3 IN VARCHAR2
403 ,p_loc_information13 IN VARCHAR2
404 ,p_loc_information14 IN VARCHAR2
405 ,p_loc_information15 IN VARCHAR2
406 ,p_loc_information16 IN VARCHAR2
407 ,p_loc_information17 IN VARCHAR2
408 ,p_loc_information18 IN VARCHAR2
409 ,p_loc_information19 IN VARCHAR2
410 ,p_loc_information20 IN VARCHAR2
411 ,p_attribute_category IN VARCHAR2
412 ,p_attribute1 IN VARCHAR2
413 ,p_attribute2 IN VARCHAR2
414 ,p_attribute3 IN VARCHAR2
415 ,p_attribute4 IN VARCHAR2
416 ,p_attribute5 IN VARCHAR2
417 ,p_attribute6 IN VARCHAR2
418 ,p_attribute7 IN VARCHAR2
419 ,p_attribute8 IN VARCHAR2
420 ,p_attribute9 IN VARCHAR2
421 ,p_attribute10 IN VARCHAR2
422 ,p_attribute11 IN VARCHAR2
423 ,p_attribute12 IN VARCHAR2
424 ,p_attribute13 IN VARCHAR2
425 ,p_attribute14 IN VARCHAR2
426 ,p_attribute15 IN VARCHAR2
427 ,p_attribute16 IN VARCHAR2
428 ,p_attribute17 IN VARCHAR2
429 ,p_attribute18 IN VARCHAR2
430 ,p_attribute19 IN VARCHAR2
431 ,p_attribute20 IN VARCHAR2
432 ,p_object_version_number IN OUT NOCOPY NUMBER
433 ,x_return_status OUT NOCOPY VARCHAR2
434 ,x_msg_count OUT NOCOPY NUMBER
435 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
436
437 l_api_version_number CONSTANT NUMBER := 1.0;
438 l_api_name CONSTANT VARCHAR2(30) := 'do_update_ship_to_location';
439 l_language VARCHAR2(4);
440 EXCP_USER_DEFINED EXCEPTION;
441
442 l_object_version_number hr_locations_all.object_version_number%TYPE := NULL;
443
444 BEGIN
445 SAVEPOINT do_update_ship_to_location_PUB;
446
447 -- initialize
448 x_return_status := FND_API.G_RET_STS_SUCCESS;
449 select userenv('LANG') into l_language from dual;
450
451 /* OPEN l_inv_location_csr;
452 FETCH l_inv_location_csr INTO
453 l_location_code,
454 l_tp_header_id,
455 l_ece_tp_location_code,
456 l_bill_to_site_flag,
457 l_designated_receiver_id,
458 l_in_organization_flag,
459 l_inactive_date,
460 l_inventory_organization_id,
461 l_office_site_flag,
462 l_receiving_site_flag,
463 l_ship_to_location_id,
464 l_ship_to_site_flag,
465 l_attribute_category,
466 l_attribute1,
467 l_attribute2,
468 l_attribute3,
469 l_attribute4,
470 l_attribute5,
471 l_attribute6,
472 l_attribute7,
473 l_attribute8,
474 l_attribute9,
475 l_attribute10,
476 l_attribute11,
477 l_attribute12,
478 l_attribute13,
479 l_attribute14,
480 l_attribute15,
481 l_attribute16,
482 l_attribute17,
483 l_attribute18,
484 l_attribute19,
485 l_attribute20,
486 l_global_attribute_category,
487 l_global_attribute1,
488 l_global_attribute2,
489 l_global_attribute3,
490 l_global_attribute4,
491 l_global_attribute5,
492 l_global_attribute6,
493 l_global_attribute7,
494 l_global_attribute8,
495 l_global_attribute9,
496 l_global_attribute10,
497 l_global_attribute11,
498 l_global_attribute12,
499 l_global_attribute13,
500 l_global_attribute14,
501 l_global_attribute15,
502 l_global_attribute16,
503 l_global_attribute17,
504 l_global_attribute18,
505 l_global_attribute19,
506 l_global_attribute20,
507 l_object_version_number;
508 IF l_inv_location_csr%NOTFOUND THEN
509 CLOSE l_inv_location_csr;
510 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INV_LOC_NOT_FOUND');
511 FND_MESSAGE.SET_TOKEN('TSK_ASSGN_ID', p_location_id, TRUE);
512 FND_MSG_PUB.ADD;
513 RAISE EXCP_USER_DEFINED;
514 END IF;
515 CLOSE l_inv_location_csr;
516
517 -- Default location description to address 1.
518 l_description := p_address_line_1;
519 */
520 l_object_version_number := p_object_version_number;
521
522 hr_location_api.update_location
523 (p_validate => false,
524 p_effective_date => sysdate,
525 -- p_language_code => l_language,
526 p_location_id => p_location_id,
527 -- p_location_code => l_location_code,
528 p_description => p_address_line_1,
529 -- p_tp_header_id => l_tp_header_id,
530 -- p_ece_tp_location_code => l_ece_tp_location_code,
531 p_address_line_1 => p_address_line_1,
532 p_address_line_2 => p_address_line_2,
533 p_address_line_3 => p_address_line_3,
534 -- p_bill_to_site_flag => l_bill_to_site_flag,
535 p_country => p_country,
536 -- p_designated_receiver_id => l_designated_receiver_id,
537 -- p_in_organization_flag => l_in_organization_flag,
538 -- p_inactive_date => l_inactive_date,
539 -- p_operating_unit_id => l_operating_unit_id,
540 -- p_inventory_organization_id => l_inventory_organization_id,
541 -- p_office_site_flag => l_office_site_flag,
542 p_postal_code => p_postal_code,
543 -- p_receiving_site_flag => l_receiving_site_flag,
544 p_region_1 => p_region_1,
545 p_region_2 => p_region_2,
546 p_region_3 => p_region_3,
547 -- p_ship_to_location_id => l_ship_to_location_id,
548 -- p_ship_to_site_flag => l_ship_to_site_flag,
549 p_style => p_style,
550 p_tax_name => p_tax_name,
551 p_telephone_number_1 => p_telephone_number_1,
552 p_telephone_number_2 => p_telephone_number_2,
553 p_telephone_number_3 => p_telephone_number_3,
554 p_town_or_city => p_town_or_city,
555 p_loc_information13 => p_loc_information13,
556 p_loc_information14 => p_loc_information14,
557 p_loc_information15 => p_loc_information15,
558 p_loc_information16 => p_loc_information16,
559 p_loc_information17 => p_loc_information17,
560 p_loc_information18 => p_loc_information18,
561 p_loc_information19 => p_loc_information19,
562 p_loc_information20 => p_loc_information20,
563 p_object_version_number => l_object_version_number,
564 p_attribute1 => p_attribute1,
565 p_attribute2 => p_attribute2,
566 p_attribute3 => p_attribute3,
567 p_attribute4 => p_attribute4,
568 p_attribute5 => p_attribute5,
569 p_attribute6 => p_attribute6,
570 p_attribute7 => p_attribute7,
571 p_attribute8 => p_attribute8,
572 p_attribute9 => p_attribute9,
573 p_attribute10 => p_attribute10,
574 p_attribute11 => p_attribute11,
575 p_attribute12 => p_attribute12,
576 p_attribute13 => p_attribute13,
577 p_attribute14 => p_attribute14,
578 p_attribute15 => p_attribute15,
579 p_attribute16 => p_attribute16,
580 p_attribute17 => p_attribute17,
581 p_attribute18 => p_attribute18,
582 p_attribute19 => p_attribute19,
583 p_attribute20 => p_attribute20,
584 p_attribute_category => p_attribute_category
585 );
586
587 p_object_version_number := l_object_version_number;
588 fnd_msg_pub.count_and_get
589 ( p_count => x_msg_count
590 , p_data => x_msg_data);
591
592 -- Exception Block
593 EXCEPTION
594 WHEN EXCP_USER_DEFINED THEN
595 Rollback to do_update_ship_to_location_PUB;
596 x_return_status := FND_API.G_RET_STS_ERROR;
597 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
598 P_API_NAME => L_API_NAME
599 ,P_PKG_NAME => G_PKG_NAME
600 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
601 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
602 ,X_MSG_COUNT => x_MSG_COUNT
603 ,X_MSG_DATA => x_MSG_DATA
604 ,X_RETURN_STATUS => x_RETURN_STATUS);
605 WHEN FND_API.G_EXC_ERROR THEN
606 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
607 P_API_NAME => L_API_NAME
608 ,P_PKG_NAME => G_PKG_NAME
609 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
610 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
611 ,X_MSG_COUNT => x_MSG_COUNT
612 ,X_MSG_DATA => x_MSG_DATA
613 ,X_RETURN_STATUS => x_RETURN_STATUS);
614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
615 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
616 P_API_NAME => L_API_NAME
617 ,P_PKG_NAME => G_PKG_NAME
618 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
619 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
620 ,X_MSG_COUNT => x_MSG_COUNT
621 ,X_MSG_DATA => x_MSG_DATA
622 ,X_RETURN_STATUS => x_RETURN_STATUS);
623 WHEN OTHERS THEN
624 Rollback to do_update_ship_to_location_PUB;
625 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
626 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, false);
627 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, false);
628 FND_MSG_PUB.ADD;
629 fnd_msg_pub.count_and_get
630 ( p_count => x_msg_count
631 , p_data => x_msg_data);
632 x_return_status := FND_API.G_RET_STS_ERROR;
633
634 END do_update_ship_to_location;
635
636
637
638 ------------------------------------------------------------------------------------------
639 -- Procedure Name : do_create_site_use
640 -- Purpose : It will create a ship to site use that link to
641 -- an inventory location.
642 --
643 PROCEDURE do_create_site_use
644 (p_customer_id IN NUMBER
645 ,p_party_id IN NUMBER
646 ,p_address_id IN NUMBER
647 ,p_location_id IN NUMBER
648 ,p_inv_location_id IN NUMBER
649 ,p_primary_flag IN VARCHAR2
650 ,p_status IN VARCHAR2
651 ,x_return_status OUT NOCOPY VARCHAR2
652 ,x_msg_count OUT NOCOPY NUMBER
653 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
654
655 l_api_version_number CONSTANT NUMBER := 1.0;
656 l_api_name CONSTANT VARCHAR2(30) := 'do_create_site_use';
657 l_language VARCHAR2(4);
658 EXCP_USER_DEFINED EXCEPTION;
659
660 s_location_id hr_locations.location_id%TYPE := p_location_id;
661 s_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
662 b_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
663 s_primary_flag hz_cust_site_uses_all.primary_flag%TYPE := NULL;
664 s_status hz_cust_site_uses_all.status%TYPE := NULL;
665
666 CURSOR l_primary_party_site_use_csr IS
667 select primary_per_type
668 from hz_party_site_uses
669 where site_use_type = 'SHIP_TO'
670 and primary_per_type = 'Y'
671 and (party_site_id in
672 (select party_site_id
673 from hz_party_sites where party_id = p_party_id and status = 'A'));
674
675 BEGIN
676 SAVEPOINT do_create_site_use_PUB;
677
678 IF p_primary_flag IS NULL THEN
679 -- Check if there is an existing primary ship to party site use
680 -- Set the primary_per_type to 'Y' only if there is no existing one
681 OPEN l_primary_party_site_use_csr;
682 FETCH l_primary_party_site_use_csr INTO s_primary_flag;
683 IF l_primary_party_site_use_csr%NOTFOUND THEN
684 CLOSE l_primary_party_site_use_csr;
685 s_primary_flag := 'Y';
686 ELSE
687 CLOSE l_primary_party_site_use_csr;
688 s_primary_flag := 'N';
689 END IF;
690 ELSE
691 s_primary_flag := p_primary_flag;
692 END IF;
693
694 IF p_status IS NULL THEN
695 s_status := 'A';
696 ELSE
697 s_status := p_status;
698 END IF;
699
700 s_location_id := null;
701
702 -- Create Bill To site use first for billing purpose.
703 arh_csu_pkg.Insert_Row(
704 X_Site_Use_Id => b_site_use_id,
705 X_Last_Update_Date => sysdate,
706 X_Last_Updated_By => nvl(fnd_global.user_id,1),
707 X_Creation_Date => sysdate,
708 X_Created_By => nvl(fnd_global.user_id,1),
709 X_Site_Use_Code => 'BILL_TO',
710 x_customer_id => p_customer_id,
711 X_Address_Id => p_address_id,
712 X_Primary_Flag => 'N',
713 X_Status => 'A',
714 X_Location => s_location_id,
715 X_Last_Update_Login => nvl(fnd_global.user_id,1),
716 X_Contact_Id => null,
717 X_Bill_To_Site_Use_Id => null,
718 X_Sic_Code => null,
719 X_Payment_Term_Id => null,
720 X_Gsa_Indicator => 'N',
721 X_Ship_Partial => 'N',
722 X_Ship_Via => null,
723 X_Fob_Point => null,
724 X_Order_Type_Id => null,
725 X_Price_List_Id => null,
726 X_Freight_Term => null,
727 X_Warehouse_Id => null,
728 X_Territory_Id => null,
729 X_Tax_Code => null,
730 X_Tax_Reference => null,
731 X_Demand_Class_Code => null,
732 x_inventory_location_id => null,
733 x_inventory_organization_id => null,
734 X_Attribute_Category => null,
735 X_Attribute1 => null,
736 X_Attribute2 => null,
737 X_Attribute3 => null,
738 X_Attribute4 => null,
739 X_Attribute5 => null,
740 X_Attribute6 => null,
741 X_Attribute7 => null,
742 X_Attribute8 => null,
743 X_Attribute9 => null,
744 X_Attribute10 => null,
745 X_Attribute11 => null,
746 X_Attribute12 => null,
747 X_Attribute13 => null,
748 X_Attribute14 => null,
749 X_Attribute15 => null,
750 X_Attribute16 => null,
751 X_Attribute17 => null,
752 X_Attribute18 => null,
753 X_Attribute19 => null,
754 X_Attribute20 => null,
755 X_Attribute21 => null,
756 X_Attribute22 => null,
757 X_Attribute23 => null,
758 X_Attribute24 => null,
759 X_Attribute25 => null,
760 X_Tax_Classification => null,
761 X_Tax_Header_Level_Flag => null,
762 X_Tax_Rounding_Rule => null,
763 X_Global_Attribute_Category => null,
764 X_Global_Attribute1 => null,
765 X_Global_Attribute2 => null,
766 X_Global_Attribute3 => null,
767 X_Global_Attribute4 => null,
768 X_Global_Attribute5 => null,
769 X_Global_Attribute6 => null,
770 X_Global_Attribute7 => null,
771 X_Global_Attribute8 => null,
772 X_Global_Attribute9 => null,
773 X_Global_Attribute10 => null,
774 X_Global_Attribute11 => null,
775 X_Global_Attribute12 => null,
776 X_Global_Attribute13 => null,
777 X_Global_Attribute14 => null,
778 X_Global_Attribute15 => null,
779 X_Global_Attribute16 => null,
780 X_Global_Attribute17 => null,
781 X_Global_Attribute18 => null,
782 X_Global_Attribute19 => null,
783 X_Global_Attribute20 => null,
784 X_Primary_Salesrep_Id => null,
785 X_Finchrg_Receivables_Trx_Id => null,
786 X_GL_ID_Rec => null,
787 X_GL_ID_Rev => null,
788 X_GL_ID_Tax => null,
789 X_GL_ID_Freight => null,
790 X_GL_ID_Clearing => null,
791 X_GL_ID_Unbilled => null,
792 X_GL_ID_Unearned => null,
793 X_GL_ID_Unpaid_rec => null,
794 X_GL_ID_remittance => null,
795 X_GL_ID_factor => null,
796 X_DATES_NEGATIVE_TOLERANCE => null,
797 X_DATES_POSITIVE_TOLERANCE => null,
798 X_DATE_TYPE_PREFERENCE => null,
799 X_OVER_SHIPMENT_TOLERANCE => null,
800 X_UNDER_SHIPMENT_TOLERANCE => null,
801 X_ITEM_CROSS_REF_PREF => null,
802 X_OVER_RETURN_TOLERANCE => null,
803 X_UNDER_RETURN_TOLERANCE => null,
804 X_SHIP_SETS_INCLUDE_LINES_FLAG => 'N',
805 X_ARRIVALSETS_INCL_LINES_FLAG => 'N',
806 X_SCHED_DATE_PUSH_FLAG => 'N',
807 X_INVOICE_QUANTITY_RULE => null,
808 x_msg_count => x_msg_count,
809 x_msg_data => x_msg_data,
810 x_return_status => x_return_status);
811
812 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
813 Rollback to do_create_site_use_PUB;
814 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
815 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
816 FND_MSG_PUB.ADD;*/
817 RAISE EXCP_USER_DEFINED;
818 END IF;
819
820 s_location_id := null;
821
822 -- Create Ship To site use to link to the inventory location and link the bill to location to the bill to site.
823 arh_csu_pkg.Insert_Row(
824 X_Site_Use_Id => s_site_use_id,
825 X_Last_Update_Date => sysdate,
826 X_Last_Updated_By => nvl(fnd_global.user_id,1),
827 X_Creation_Date => sysdate,
828 X_Created_By => nvl(fnd_global.user_id,1),
829 X_Site_Use_Code => 'SHIP_TO',
830 x_customer_id => p_customer_id,
831 X_Address_Id => p_address_id,
832 X_Primary_Flag => s_primary_flag,
833 X_Status => s_status,
834 X_Location => s_location_id,
835 X_Last_Update_Login => nvl(fnd_global.user_id,1),
836 X_Contact_Id => null,
837 -- X_Bill_To_Site_Use_Id => null,
838 X_Bill_To_Site_Use_Id => b_site_use_id,
839 X_Sic_Code => null,
840 X_Payment_Term_Id => null,
841 X_Gsa_Indicator => 'N',
842 X_Ship_Partial => 'N',
843 X_Ship_Via => null,
844 X_Fob_Point => null,
845 X_Order_Type_Id => null,
846 X_Price_List_Id => null,
847 X_Freight_Term => null,
848 X_Warehouse_Id => null,
849 X_Territory_Id => null,
850 X_Tax_Code => null,
851 X_Tax_Reference => null,
852 X_Demand_Class_Code => null,
853 x_inventory_location_id => p_inv_location_id,
854 x_inventory_organization_id => null,
855 X_Attribute_Category => null,
856 X_Attribute1 => null,
857 X_Attribute2 => null,
858 X_Attribute3 => null,
859 X_Attribute4 => null,
860 X_Attribute5 => null,
861 X_Attribute6 => null,
862 X_Attribute7 => null,
863 X_Attribute8 => null,
864 X_Attribute9 => null,
865 X_Attribute10 => null,
866 X_Attribute11 => null,
867 X_Attribute12 => null,
868 X_Attribute13 => null,
869 X_Attribute14 => null,
870 X_Attribute15 => null,
871 X_Attribute16 => null,
872 X_Attribute17 => null,
873 X_Attribute18 => null,
874 X_Attribute19 => null,
875 X_Attribute20 => null,
876 X_Attribute21 => null,
877 X_Attribute22 => null,
878 X_Attribute23 => null,
879 X_Attribute24 => null,
880 X_Attribute25 => null,
881 X_Tax_Classification => null,
882 X_Tax_Header_Level_Flag => null,
883 X_Tax_Rounding_Rule => null,
884 X_Global_Attribute_Category => null,
885 X_Global_Attribute1 => null,
886 X_Global_Attribute2 => null,
887 X_Global_Attribute3 => null,
888 X_Global_Attribute4 => null,
889 X_Global_Attribute5 => null,
890 X_Global_Attribute6 => null,
891 X_Global_Attribute7 => null,
892 X_Global_Attribute8 => null,
893 X_Global_Attribute9 => null,
894 X_Global_Attribute10 => null,
895 X_Global_Attribute11 => null,
896 X_Global_Attribute12 => null,
897 X_Global_Attribute13 => null,
898 X_Global_Attribute14 => null,
899 X_Global_Attribute15 => null,
900 X_Global_Attribute16 => null,
901 X_Global_Attribute17 => null,
902 X_Global_Attribute18 => null,
903 X_Global_Attribute19 => null,
904 X_Global_Attribute20 => null,
905 X_Primary_Salesrep_Id => null,
906 X_Finchrg_Receivables_Trx_Id => null,
907 X_GL_ID_Rec => null,
908 X_GL_ID_Rev => null,
909 X_GL_ID_Tax => null,
910 X_GL_ID_Freight => null,
911 X_GL_ID_Clearing => null,
912 X_GL_ID_Unbilled => null,
913 X_GL_ID_Unearned => null,
914 X_GL_ID_Unpaid_rec => null,
915 X_GL_ID_remittance => null,
916 X_GL_ID_factor => null,
917 X_DATES_NEGATIVE_TOLERANCE => null,
918 X_DATES_POSITIVE_TOLERANCE => null,
919 X_DATE_TYPE_PREFERENCE => null,
920 X_OVER_SHIPMENT_TOLERANCE => null,
921 X_UNDER_SHIPMENT_TOLERANCE => null,
922 X_ITEM_CROSS_REF_PREF => null,
923 X_OVER_RETURN_TOLERANCE => null,
924 X_UNDER_RETURN_TOLERANCE => null,
925 X_SHIP_SETS_INCLUDE_LINES_FLAG => 'N',
926 X_ARRIVALSETS_INCL_LINES_FLAG => 'N',
927 X_SCHED_DATE_PUSH_FLAG => 'N',
928 X_INVOICE_QUANTITY_RULE => null,
929 x_msg_count => x_msg_count,
930 x_msg_data => x_msg_data,
931 x_return_status => x_return_status);
932
933 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
934 Rollback to do_create_site_use_PUB;
935 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
936 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
937 FND_MSG_PUB.ADD;*/
938 RAISE EXCP_USER_DEFINED;
939 END IF;
940
941 -- Exception Block
942 EXCEPTION
943 WHEN EXCP_USER_DEFINED THEN
944 Rollback to do_create_site_use_PUB;
945 x_return_status := FND_API.G_RET_STS_ERROR;
946 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
947 P_API_NAME => L_API_NAME
948 ,P_PKG_NAME => G_PKG_NAME
949 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
950 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
951 ,X_MSG_COUNT => x_MSG_COUNT
952 ,X_MSG_DATA => x_MSG_DATA
953 ,X_RETURN_STATUS => x_RETURN_STATUS);
954 WHEN FND_API.G_EXC_ERROR THEN
955 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
956 P_API_NAME => L_API_NAME
957 ,P_PKG_NAME => G_PKG_NAME
958 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
959 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
960 ,X_MSG_COUNT => x_MSG_COUNT
961 ,X_MSG_DATA => x_MSG_DATA
962 ,X_RETURN_STATUS => x_RETURN_STATUS);
963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
964 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
965 P_API_NAME => L_API_NAME
966 ,P_PKG_NAME => G_PKG_NAME
967 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
968 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
969 ,X_MSG_COUNT => x_MSG_COUNT
970 ,X_MSG_DATA => x_MSG_DATA
971 ,X_RETURN_STATUS => x_RETURN_STATUS);
972 WHEN OTHERS THEN
973 Rollback to do_create_site_use_PUB;
974 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
975 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, false);
976 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, false);
977 FND_MSG_PUB.ADD;
978 fnd_msg_pub.count_and_get
979 ( p_count => x_msg_count
980 , p_data => x_msg_data);
981 x_return_status := FND_API.G_RET_STS_ERROR;
982
983 END do_create_site_use;
984
985
986
987 ------------------------------------------------------------------------------------------
988 -- Procedure Name : do_update_site_use
989 -- Purpose : It will update a ship to site use that link to
990 -- an inventory location.
991 --
992 ------------------------------------------------------------------------------------------
993 -- Procedure Name : do_update_site_use
994 -- Purpose : It will update a ship to site use that link to
995 -- an inventory location.
996 --
997 PROCEDURE do_update_site_use
998 (p_site_use_id IN NUMBER
999 ,p_primary_flag IN VARCHAR2
1000 ,p_status IN VARCHAR2
1001 ,p_customer_id IN NUMBER
1002 ,p_inv_location_id IN NUMBER
1003 ,x_return_status OUT NOCOPY VARCHAR2
1004 ,x_msg_count OUT NOCOPY NUMBER
1005 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1006
1007 l_api_version_number CONSTANT NUMBER := 1.0;
1008 l_api_name CONSTANT VARCHAR2(30) := 'do_update_site_use';
1009 l_language VARCHAR2(4);
1010 EXCP_USER_DEFINED EXCEPTION;
1011
1012 l_site_use_id hz_cust_site_uses_all.site_use_id%TYPE;
1013 s_cust_acct_site_id hz_cust_site_uses_all.cust_acct_site_id%TYPE;
1014 s_creation_date hz_cust_site_uses_all.creation_date%TYPE;
1015 s_created_by hz_cust_site_uses_all.created_by%TYPE;
1016 s_site_use_code hz_cust_site_uses_all.site_use_code%TYPE;
1017 s_primary_flag hz_cust_site_uses_all.primary_flag%TYPE;
1018 s_status hz_cust_site_uses_all.status%TYPE;
1019 s_location hz_cust_site_uses_all.location%TYPE;
1020 s_last_update_login hz_cust_site_uses_all.last_update_login%TYPE;
1021 s_contact_id hz_cust_site_uses_all.contact_id%TYPE;
1022 s_bill_to_site_use_id hz_cust_site_uses_all.bill_to_site_use_id%TYPE;
1023 s_orig_system_reference hz_cust_site_uses_all.orig_system_reference%TYPE;
1024 s_sic_code hz_cust_site_uses_all.sic_code%TYPE;
1025 s_payment_term_id hz_cust_site_uses_all.payment_term_id%TYPE;
1026 s_gsa_indicator hz_cust_site_uses_all.gsa_indicator%TYPE;
1027 s_ship_partial hz_cust_site_uses_all.ship_partial%TYPE;
1028 s_ship_via hz_cust_site_uses_all.ship_via%TYPE;
1029 s_fob_point hz_cust_site_uses_all.fob_point%TYPE;
1030 s_order_type_id hz_cust_site_uses_all.order_type_id%TYPE;
1031 s_price_list_id hz_cust_site_uses_all.price_list_id%TYPE;
1032 s_freight_term hz_cust_site_uses_all.freight_term%TYPE;
1033 s_warehouse_id hz_cust_site_uses_all.warehouse_id%TYPE;
1034 s_territory_id hz_cust_site_uses_all.territory_id%TYPE;
1035 s_attribute_category hz_cust_site_uses_all.attribute_category%TYPE;
1036 s_attribute1 hz_cust_site_uses_all.attribute1%TYPE;
1037 s_attribute2 hz_cust_site_uses_all.attribute2%TYPE;
1038 s_attribute3 hz_cust_site_uses_all.attribute3%TYPE;
1039 s_attribute4 hz_cust_site_uses_all.attribute4%TYPE;
1040 s_attribute5 hz_cust_site_uses_all.attribute5%TYPE;
1041 s_attribute6 hz_cust_site_uses_all.attribute6%TYPE;
1042 s_attribute7 hz_cust_site_uses_all.attribute7%TYPE;
1043 s_attribute8 hz_cust_site_uses_all.attribute8%TYPE;
1044 s_attribute9 hz_cust_site_uses_all.attribute9%TYPE;
1045 s_attribute10 hz_cust_site_uses_all.attribute10%TYPE;
1046 s_request_id hz_cust_site_uses_all.request_id%TYPE;
1047 s_program_application_id hz_cust_site_uses_all.program_application_id%TYPE;
1048 s_program_id hz_cust_site_uses_all.program_id%TYPE;
1049 s_program_update_date hz_cust_site_uses_all.program_update_date%TYPE;
1050 s_tax_reference hz_cust_site_uses_all.tax_reference%TYPE;
1051 s_sort_priority hz_cust_site_uses_all.sort_priority%TYPE;
1052 s_tax_code hz_cust_site_uses_all.tax_code%TYPE;
1053 s_attribute11 hz_cust_site_uses_all.attribute11%TYPE;
1054 s_attribute12 hz_cust_site_uses_all.attribute12%TYPE;
1055 s_attribute13 hz_cust_site_uses_all.attribute13%TYPE;
1056 s_attribute14 hz_cust_site_uses_all.attribute14%TYPE;
1057 s_attribute15 hz_cust_site_uses_all.attribute15%TYPE;
1058 s_attribute16 hz_cust_site_uses_all.attribute16%TYPE;
1059 s_attribute17 hz_cust_site_uses_all.attribute17%TYPE;
1060 s_attribute18 hz_cust_site_uses_all.attribute18%TYPE;
1061 s_attribute19 hz_cust_site_uses_all.attribute19%TYPE;
1062 s_attribute20 hz_cust_site_uses_all.attribute20%TYPE;
1063 s_attribute21 hz_cust_site_uses_all.attribute21%TYPE;
1064 s_attribute22 hz_cust_site_uses_all.attribute22%TYPE;
1065 s_attribute23 hz_cust_site_uses_all.attribute23%TYPE;
1066 s_attribute24 hz_cust_site_uses_all.attribute24%TYPE;
1067 s_attribute25 hz_cust_site_uses_all.attribute25%TYPE;
1068 s_last_accrue_charge_date DATE;
1069 s_snd_last_accrue_charge_date DATE;
1070 s_last_unaccrue_charge_date DATE;
1071 s_snd_last_unaccrue_chrg_date DATE;
1072 s_demand_class_code hz_cust_site_uses_all.demand_class_code%TYPE;
1073 s_org_id hz_cust_site_uses_all.org_id%TYPE;
1074 s_tax_header_level_flag hz_cust_site_uses_all.tax_header_level_flag%TYPE;
1075 s_tax_rounding_rule hz_cust_site_uses_all.tax_rounding_rule%TYPE;
1076 s_wh_update_date hz_cust_site_uses_all.wh_update_date%TYPE;
1077 s_global_attribute1 hz_cust_site_uses_all.global_attribute1%TYPE;
1078 s_global_attribute2 hz_cust_site_uses_all.global_attribute2%TYPE;
1079 s_global_attribute3 hz_cust_site_uses_all.global_attribute3%TYPE;
1080 s_global_attribute4 hz_cust_site_uses_all.global_attribute4%TYPE;
1081 s_global_attribute5 hz_cust_site_uses_all.global_attribute5%TYPE;
1082 s_global_attribute6 hz_cust_site_uses_all.global_attribute6%TYPE;
1083 s_global_attribute7 hz_cust_site_uses_all.global_attribute7%TYPE;
1084 s_global_attribute8 hz_cust_site_uses_all.global_attribute8%TYPE;
1085 s_global_attribute9 hz_cust_site_uses_all.global_attribute9%TYPE;
1086 s_global_attribute10 hz_cust_site_uses_all.global_attribute10%TYPE;
1087 s_global_attribute11 hz_cust_site_uses_all.global_attribute11%TYPE;
1088 s_global_attribute12 hz_cust_site_uses_all.global_attribute12%TYPE;
1089 s_global_attribute13 hz_cust_site_uses_all.global_attribute13%TYPE;
1090 s_global_attribute14 hz_cust_site_uses_all.global_attribute14%TYPE;
1091 s_global_attribute15 hz_cust_site_uses_all.global_attribute15%TYPE;
1092 s_global_attribute16 hz_cust_site_uses_all.global_attribute16%TYPE;
1093 s_global_attribute17 hz_cust_site_uses_all.global_attribute17%TYPE;
1094 s_global_attribute18 hz_cust_site_uses_all.global_attribute18%TYPE;
1095 s_global_attribute19 hz_cust_site_uses_all.global_attribute19%TYPE;
1096 s_global_attribute20 hz_cust_site_uses_all.global_attribute20%TYPE;
1097 s_global_attribute_category hz_cust_site_uses_all.global_attribute_category%TYPE;
1098 s_primary_salesrep_id hz_cust_site_uses_all.primary_salesrep_id%TYPE;
1099 s_finchrg_receivables_trx_id hz_cust_site_uses_all.finchrg_receivables_trx_id%TYPE;
1100 s_dates_negative_tolerance hz_cust_site_uses_all.dates_negative_tolerance%TYPE;
1101 s_dates_positive_tolerance hz_cust_site_uses_all.dates_positive_tolerance%TYPE;
1102 s_date_type_preference hz_cust_site_uses_all.date_type_preference%TYPE;
1103 s_over_shipment_tolerance hz_cust_site_uses_all.over_shipment_tolerance%TYPE;
1104 s_under_shipment_tolerance hz_cust_site_uses_all.under_shipment_tolerance%TYPE;
1105 s_item_cross_ref_pref hz_cust_site_uses_all.item_cross_ref_pref%TYPE;
1106 s_over_return_tolerance hz_cust_site_uses_all.over_return_tolerance%TYPE;
1107 s_under_return_tolerance hz_cust_site_uses_all.under_return_tolerance%TYPE;
1108 s_ship_sets_include_lines_flag hz_cust_site_uses_all.ship_sets_include_lines_flag%TYPE;
1109 s_arv_include_lines_flag hz_cust_site_uses_all.arrivalsets_include_lines_flag%TYPE;
1110 s_sched_date_push_flag hz_cust_site_uses_all.sched_date_push_flag%TYPE;
1111 s_invoice_quantity_rule hz_cust_site_uses_all.invoice_quantity_rule%TYPE;
1112 s_pricing_event hz_cust_site_uses_all.pricing_event%TYPE;
1113 s_gl_id_rec hz_cust_site_uses_all.gl_id_rec%TYPE;
1114 s_gl_id_rev hz_cust_site_uses_all.gl_id_rev%TYPE;
1115 s_gl_id_tax hz_cust_site_uses_all.gl_id_tax%TYPE;
1116 s_gl_id_freight hz_cust_site_uses_all.gl_id_freight%TYPE;
1117 s_gl_id_clearing hz_cust_site_uses_all.gl_id_clearing%TYPE;
1118 s_gl_id_unbilled hz_cust_site_uses_all.gl_id_unbilled%TYPE;
1119 s_gl_id_unearned hz_cust_site_uses_all.gl_id_unearned%TYPE;
1120 s_gl_id_unpaid_rec hz_cust_site_uses_all.gl_id_unpaid_rec%TYPE;
1121 s_gl_id_remittance hz_cust_site_uses_all.gl_id_remittance%TYPE;
1122 s_gl_id_factor hz_cust_site_uses_all.gl_id_factor%TYPE;
1123 s_tax_classification hz_cust_site_uses_all.tax_classification%TYPE;
1124 s_last_update_date DATE;
1125 s_last_updated_by NUMBER;
1126
1127 CURSOR l_cust_site_use_csr IS
1128 select cust_acct_site_id,
1129 last_update_date,
1130 last_updated_by,
1131 creation_date,
1132 created_by,
1133 site_use_code,
1134 primary_flag,
1135 status,
1136 location,
1137 last_update_login,
1138 contact_id,
1139 bill_to_site_use_id,
1140 orig_system_reference,
1141 sic_code,
1142 payment_term_id,
1143 gsa_indicator,
1144 ship_partial,
1145 ship_via,
1146 fob_point,
1147 order_type_id,
1148 price_list_id,
1149 freight_term,
1150 warehouse_id,
1151 territory_id,
1152 attribute_category,
1153 attribute1,
1154 attribute2,
1155 attribute3,
1156 attribute4,
1157 attribute5,
1158 attribute6,
1159 attribute7,
1160 attribute8,
1161 attribute9,
1162 attribute10,
1163 request_id,
1164 program_application_id,
1165 program_id,
1166 program_update_date,
1167 tax_reference,
1168 sort_priority,
1169 tax_code,
1170 attribute11,
1171 attribute12,
1172 attribute13,
1173 attribute14,
1174 attribute15,
1175 attribute16,
1176 attribute17,
1177 attribute18,
1178 attribute19,
1179 attribute20,
1180 attribute21,
1181 attribute22,
1182 attribute23,
1183 attribute24,
1184 attribute25,
1185 last_accrue_charge_date,
1186 second_last_accrue_charge_date,
1187 last_unaccrue_charge_date,
1188 second_last_unaccrue_chrg_date,
1189 demand_class_code,
1190 org_id,
1191 tax_header_level_flag,
1192 tax_rounding_rule,
1193 wh_update_date,
1194 global_attribute1,
1195 global_attribute2,
1196 global_attribute3,
1197 global_attribute4,
1198 global_attribute5,
1199 global_attribute6,
1200 global_attribute7,
1201 global_attribute8,
1202 global_attribute9,
1203 global_attribute10,
1204 global_attribute11,
1205 global_attribute12,
1206 global_attribute13,
1207 global_attribute14,
1208 global_attribute15,
1209 global_attribute16,
1210 global_attribute17,
1211 global_attribute18,
1212 global_attribute19,
1213 global_attribute20,
1214 global_attribute_category,
1215 primary_salesrep_id,
1216 finchrg_receivables_trx_id,
1217 dates_negative_tolerance,
1218 dates_positive_tolerance,
1219 date_type_preference,
1220 over_shipment_tolerance,
1221 under_shipment_tolerance,
1222 item_cross_ref_pref,
1223 over_return_tolerance,
1224 under_return_tolerance,
1225 ship_sets_include_lines_flag,
1226 arrivalsets_include_lines_flag,
1227 sched_date_push_flag,
1228 invoice_quantity_rule,
1229 pricing_event,
1230 gl_id_rec,
1231 gl_id_rev,
1232 gl_id_tax,
1233 gl_id_freight,
1234 gl_id_clearing,
1235 gl_id_unbilled,
1236 gl_id_unearned,
1237 gl_id_unpaid_rec,
1238 gl_id_remittance,
1239 gl_id_factor,
1240 tax_classification
1241 from hz_cust_site_uses
1242 where site_use_id = p_site_use_id and site_use_code = 'SHIP_TO';
1243
1244 CURSOR get_bill_to_site_use_id IS
1245 select bill_to_site_use_id
1246 from hz_cust_site_uses
1247 where site_use_id = p_site_use_id and site_use_code = 'SHIP_TO';
1248
1249 l_Bill_to_site_use_id NUMBER;
1250
1251 CURSOR l_cust_Bill_to_site_use_csr IS
1252 select cust_acct_site_id,
1253 last_update_date,
1254 last_updated_by,
1255 creation_date,
1256 created_by,
1257 site_use_code,
1258 primary_flag,
1259 status,
1260 location,
1261 last_update_login,
1262 contact_id,
1263 bill_to_site_use_id,
1264 orig_system_reference,
1265 sic_code,
1266 payment_term_id,
1267 gsa_indicator,
1268 ship_partial,
1269 ship_via,
1270 fob_point,
1271 order_type_id,
1272 price_list_id,
1273 freight_term,
1274 warehouse_id,
1275 territory_id,
1276 attribute_category,
1277 attribute1,
1278 attribute2,
1279 attribute3,
1280 attribute4,
1281 attribute5,
1282 attribute6,
1283 attribute7,
1284 attribute8,
1285 attribute9,
1286 attribute10,
1287 request_id,
1288 program_application_id,
1289 program_id,
1290 program_update_date,
1291 tax_reference,
1292 sort_priority,
1293 tax_code,
1294 attribute11,
1295 attribute12,
1296 attribute13,
1297 attribute14,
1298 attribute15,
1299 attribute16,
1300 attribute17,
1301 attribute18,
1302 attribute19,
1303 attribute20,
1304 attribute21,
1305 attribute22,
1306 attribute23,
1307 attribute24,
1308 attribute25,
1309 last_accrue_charge_date,
1310 second_last_accrue_charge_date,
1311 last_unaccrue_charge_date,
1312 second_last_unaccrue_chrg_date,
1313 demand_class_code,
1314 org_id,
1315 tax_header_level_flag,
1316 tax_rounding_rule,
1317 wh_update_date,
1318 global_attribute1,
1319 global_attribute2,
1320 global_attribute3,
1321 global_attribute4,
1322 global_attribute5,
1323 global_attribute6,
1324 global_attribute7,
1325 global_attribute8,
1326 global_attribute9,
1327 global_attribute10,
1328 global_attribute11,
1329 global_attribute12,
1330 global_attribute13,
1331 global_attribute14,
1332 global_attribute15,
1333 global_attribute16,
1334 global_attribute17,
1335 global_attribute18,
1336 global_attribute19,
1337 global_attribute20,
1338 global_attribute_category,
1339 primary_salesrep_id,
1340 finchrg_receivables_trx_id,
1341 dates_negative_tolerance,
1342 dates_positive_tolerance,
1343 date_type_preference,
1344 over_shipment_tolerance,
1345 under_shipment_tolerance,
1346 item_cross_ref_pref,
1347 over_return_tolerance,
1348 under_return_tolerance,
1349 ship_sets_include_lines_flag,
1350 arrivalsets_include_lines_flag,
1351 sched_date_push_flag,
1352 invoice_quantity_rule,
1353 pricing_event,
1354 gl_id_rec,
1355 gl_id_rev,
1356 gl_id_tax,
1357 gl_id_freight,
1358 gl_id_clearing,
1359 gl_id_unbilled,
1360 gl_id_unearned,
1361 gl_id_unpaid_rec,
1362 gl_id_remittance,
1363 gl_id_factor,
1364 tax_classification
1365 from hz_cust_site_uses
1366 where site_use_id = l_bill_to_site_use_id and site_use_code = 'BILL_TO';
1367
1368
1369 BEGIN
1370 SAVEPOINT do_update_site_use_PUB;
1371
1372 open get_bill_to_site_use_id;
1373 fetch get_bill_to_site_use_id INTO l_bill_to_site_use_id;
1374 CLose get_bill_to_site_use_id;
1375 if l_bill_to_site_use_id IS NOT NULL and p_status = 'A' THEN
1376 open l_cust_Bill_to_site_use_csr;
1377 fetch l_cust_Bill_to_site_use_csr into
1378 s_cust_acct_site_id,
1379 s_last_update_date,
1380 s_last_updated_by,
1381 s_creation_date,
1382 s_created_by,
1383 s_site_use_code,
1384 s_primary_flag,
1385 s_status,
1386 s_location,
1387 s_last_update_login,
1388 s_contact_id,
1389 s_bill_to_site_use_id,
1390 s_orig_system_reference,
1391 s_sic_code,
1392 s_payment_term_id,
1393 s_gsa_indicator,
1394 s_ship_partial,
1395 s_ship_via,
1396 s_fob_point,
1397 s_order_type_id,
1398 s_price_list_id,
1399 s_freight_term,
1400 s_warehouse_id,
1401 s_territory_id,
1402 s_attribute_category,
1403 s_attribute1,
1404 s_attribute2,
1405 s_attribute3,
1406 s_attribute4,
1407 s_attribute5,
1408 s_attribute6,
1409 s_attribute7,
1410 s_attribute8,
1411 s_attribute9,
1412 s_attribute10,
1413 s_request_id,
1414 s_program_application_id,
1415 s_program_id,
1416 s_program_update_date,
1417 s_tax_reference,
1418 s_sort_priority,
1419 s_tax_code,
1420 s_attribute11,
1421 s_attribute12,
1422 s_attribute13,
1423 s_attribute14,
1424 s_attribute15,
1425 s_attribute16,
1426 s_attribute17,
1427 s_attribute18,
1428 s_attribute19,
1429 s_attribute20,
1430 s_attribute21,
1431 s_attribute22,
1432 s_attribute23,
1433 s_attribute24,
1434 s_attribute25,
1435 s_last_accrue_charge_date,
1436 s_snd_last_accrue_charge_date,
1437 s_last_unaccrue_charge_date,
1438 s_snd_last_unaccrue_chrg_date,
1439 s_demand_class_code,
1440 s_org_id,
1441 s_tax_header_level_flag,
1442 s_tax_rounding_rule,
1443 s_wh_update_date,
1444 s_global_attribute1,
1445 s_global_attribute2,
1446 s_global_attribute3,
1447 s_global_attribute4,
1448 s_global_attribute5,
1449 s_global_attribute6,
1450 s_global_attribute7,
1451 s_global_attribute8,
1452 s_global_attribute9,
1453 s_global_attribute10,
1454 s_global_attribute11,
1455 s_global_attribute12,
1456 s_global_attribute13,
1457 s_global_attribute14,
1458 s_global_attribute15,
1459 s_global_attribute16,
1460 s_global_attribute17,
1461 s_global_attribute18,
1462 s_global_attribute19,
1463 s_global_attribute20,
1464 s_global_attribute_category,
1465 s_primary_salesrep_id,
1466 s_finchrg_receivables_trx_id,
1467 s_dates_negative_tolerance,
1468 s_dates_positive_tolerance,
1469 s_date_type_preference,
1470 s_over_shipment_tolerance,
1471 s_under_shipment_tolerance,
1472 s_item_cross_ref_pref,
1473 s_over_return_tolerance,
1474 s_under_return_tolerance,
1475 s_ship_sets_include_lines_flag,
1476 s_arv_include_lines_flag,
1477 s_sched_date_push_flag,
1478 s_invoice_quantity_rule,
1479 s_pricing_event,
1480 s_gl_id_rec,
1481 s_gl_id_rev,
1482 s_gl_id_tax,
1483 s_gl_id_freight,
1484 s_gl_id_clearing,
1485 s_gl_id_unbilled,
1486 s_gl_id_unearned,
1487 s_gl_id_unpaid_rec,
1488 s_gl_id_remittance,
1489 s_gl_id_factor,
1490 s_tax_classification;
1491 IF l_cust_Bill_to_site_use_csr%FOUND THEN
1492 close l_cust_Bill_to_site_use_csr;
1493
1494 -- Update site use for ship_to
1495 arh_csu_pkg.Update_Row(
1496 X_Site_Use_Id => l_Bill_to_site_use_id,
1497 X_Last_Update_Date => s_last_update_date,
1498 X_Last_Updated_By => nvl(fnd_global.user_id, 1),
1499 X_Site_Use_Code => s_site_use_code,
1500 X_customer_id => p_customer_id,
1501 X_Address_Id => s_cust_acct_site_id,
1502 X_Primary_Flag => p_primary_flag,
1503 X_Status => p_status,
1504 X_Location => s_location,
1505 X_Last_Update_Login => s_last_update_login,
1506 X_Contact_Id => s_Contact_Id,
1507 X_Bill_To_Site_Use_Id => s_Bill_To_Site_Use_Id,
1508 X_Sic_Code => s_Sic_Code,
1509 X_Payment_Term_Id => s_Payment_Term_Id,
1510 X_Gsa_Indicator => s_Gsa_Indicator,
1511 X_Ship_Partial => s_Ship_Partial,
1512 X_Ship_Via => s_Ship_Via,
1513 X_Fob_Point => s_Fob_Point,
1514 X_Order_Type_Id => s_Order_Type_Id,
1515 X_Price_List_Id => s_Price_List_Id,
1516 X_Freight_Term => s_Freight_Term,
1517 X_Warehouse_Id => s_Warehouse_Id,
1518 X_Territory_Id => s_Territory_Id,
1519 X_Tax_Code => s_Tax_Code,
1520 X_Tax_Reference => s_Tax_Reference,
1521 X_Demand_Class_Code => s_Demand_Class_Code,
1522 x_inventory_location_id => p_inv_location_id,
1523 x_inventory_organization_id => null,
1524 X_Attribute_Category => s_attribute_category,
1525 X_Attribute1 => s_attribute1,
1526 X_Attribute2 => s_attribute2,
1527 X_Attribute3 => s_attribute3,
1528 X_Attribute4 => s_attribute4,
1529 X_Attribute5 => s_attribute5,
1530 X_Attribute6 => s_attribute6,
1531 X_Attribute7 => s_attribute7,
1532 X_Attribute8 => s_attribute8,
1533 X_Attribute9 => s_attribute9,
1534 X_Attribute10 => s_attribute10,
1535 X_Attribute11 => s_attribute11,
1536 X_Attribute12 => s_attribute12,
1537 X_Attribute13 => s_attribute13,
1538 X_Attribute14 => s_attribute14,
1539 X_Attribute15 => s_attribute15,
1540 X_Attribute16 => s_attribute16,
1541 X_Attribute17 => s_attribute17,
1542 X_Attribute18 => s_attribute18,
1543 X_Attribute19 => s_attribute19,
1544 X_Attribute20 => s_attribute20,
1545 X_Attribute21 => s_attribute21,
1546 X_Attribute22 => s_attribute22,
1547 X_Attribute23 => s_attribute23,
1548 X_Attribute24 => s_attribute24,
1549 X_Attribute25 => s_attribute25,
1550 X_Tax_Classification => s_Tax_Classification,
1551 X_Tax_Header_Level_Flag => s_Tax_Header_Level_Flag,
1552 X_Tax_Rounding_Rule => s_Tax_Rounding_Rule,
1553 X_Global_Attribute_Category => s_global_attribute_category,
1554 X_Global_Attribute1 => s_global_attribute1,
1555 X_Global_Attribute2 => s_global_attribute2,
1556 X_Global_Attribute3 => s_global_attribute3,
1557 X_Global_Attribute4 => s_global_attribute4,
1558 X_Global_Attribute5 => s_global_attribute5,
1559 X_Global_Attribute6 => s_global_attribute6,
1560 X_Global_Attribute7 => s_global_attribute7,
1561 X_Global_Attribute8 => s_global_attribute8,
1562 X_Global_Attribute9 => s_global_attribute9,
1563 X_Global_Attribute10 => s_global_attribute10,
1564 X_Global_Attribute11 => s_global_attribute11,
1565 X_Global_Attribute12 => s_global_attribute12,
1566 X_Global_Attribute13 => s_global_attribute13,
1567 X_Global_Attribute14 => s_global_attribute14,
1568 X_Global_Attribute15 => s_global_attribute15,
1569 X_Global_Attribute16 => s_global_attribute16,
1570 X_Global_Attribute17 => s_global_attribute17,
1571 X_Global_Attribute18 => s_global_attribute18,
1572 X_Global_Attribute19 => s_global_attribute19,
1573 X_Global_Attribute20 => s_global_attribute20,
1574 X_Primary_Salesrep_Id => s_primary_salesrep_id,
1575 X_Finchrg_Receivables_Trx_Id => s_Finchrg_Receivables_Trx_Id,
1576 X_GL_ID_Rec => s_GL_ID_Rec,
1577 X_GL_ID_Rev => s_GL_ID_Rev,
1578 X_GL_ID_Tax => s_GL_ID_Tax,
1579 X_GL_ID_Freight => s_GL_ID_Freight,
1580 X_GL_ID_Clearing => s_GL_ID_Clearing,
1581 X_GL_ID_Unbilled => s_GL_ID_Unbilled,
1582 X_GL_ID_Unearned => s_GL_ID_Unearned,
1583 X_GL_ID_Unpaid_rec => s_GL_ID_Unpaid_rec,
1584 X_GL_ID_Remittance => s_GL_ID_Remittance,
1585 X_GL_ID_Factor => s_GL_ID_Factor,
1586 X_DATES_NEGATIVE_TOLERANCE => s_DATES_NEGATIVE_TOLERANCE,
1587 X_DATES_POSITIVE_TOLERANCE => s_DATES_POSITIVE_TOLERANCE,
1588 X_DATE_TYPE_PREFERENCE => s_DATE_TYPE_PREFERENCE,
1589 X_OVER_SHIPMENT_TOLERANCE => s_OVER_SHIPMENT_TOLERANCE,
1590 X_UNDER_SHIPMENT_TOLERANCE => s_UNDER_SHIPMENT_TOLERANCE,
1591 X_ITEM_CROSS_REF_PREF => s_ITEM_CROSS_REF_PREF,
1592 X_OVER_RETURN_TOLERANCE => s_OVER_RETURN_TOLERANCE,
1593 X_UNDER_RETURN_TOLERANCE => s_UNDER_RETURN_TOLERANCE,
1594 X_SHIP_SETS_INCLUDE_LINES_FLAG => s_SHIP_SETS_INCLUDE_LINES_FLAG,
1595 X_ARRIVALSETS_INCL_LINES_FLAG => s_arv_include_lines_flag,
1596 X_SCHED_DATE_PUSH_FLAG => s_SCHED_DATE_PUSH_FLAG,
1597 X_INVOICE_QUANTITY_RULE => s_INVOICE_QUANTITY_RULE,
1598 x_msg_count => x_msg_count,
1599 x_msg_data => x_msg_data,
1600 x_return_status => x_return_status);
1601
1602 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1603 Rollback to do_update_site_use_PUB;
1604 /* FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
1605 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
1606 FND_MSG_PUB.ADD;*/
1607 RAISE EXCP_USER_DEFINED;
1608 END IF;
1609 ELSE
1610 close l_cust_Bill_to_site_use_csr;
1611 END IF;
1612 END IF;
1613
1614
1615 l_site_use_id := p_site_use_id;
1616
1617 open l_cust_site_use_csr;
1618 fetch l_cust_site_use_csr into
1619 s_cust_acct_site_id,
1620 s_last_update_date,
1621 s_last_updated_by,
1622 s_creation_date,
1623 s_created_by,
1624 s_site_use_code,
1625 s_primary_flag,
1626 s_status,
1627 s_location,
1628 s_last_update_login,
1629 s_contact_id,
1630 s_bill_to_site_use_id,
1631 s_orig_system_reference,
1632 s_sic_code,
1633 s_payment_term_id,
1634 s_gsa_indicator,
1635 s_ship_partial,
1636 s_ship_via,
1637 s_fob_point,
1638 s_order_type_id,
1639 s_price_list_id,
1640 s_freight_term,
1641 s_warehouse_id,
1642 s_territory_id,
1643 s_attribute_category,
1644 s_attribute1,
1645 s_attribute2,
1646 s_attribute3,
1647 s_attribute4,
1648 s_attribute5,
1649 s_attribute6,
1650 s_attribute7,
1651 s_attribute8,
1652 s_attribute9,
1653 s_attribute10,
1654 s_request_id,
1655 s_program_application_id,
1656 s_program_id,
1657 s_program_update_date,
1658 s_tax_reference,
1659 s_sort_priority,
1660 s_tax_code,
1661 s_attribute11,
1662 s_attribute12,
1663 s_attribute13,
1664 s_attribute14,
1665 s_attribute15,
1666 s_attribute16,
1667 s_attribute17,
1668 s_attribute18,
1669 s_attribute19,
1670 s_attribute20,
1671 s_attribute21,
1672 s_attribute22,
1673 s_attribute23,
1674 s_attribute24,
1675 s_attribute25,
1676 s_last_accrue_charge_date,
1677 s_snd_last_accrue_charge_date,
1678 s_last_unaccrue_charge_date,
1679 s_snd_last_unaccrue_chrg_date,
1680 s_demand_class_code,
1681 s_org_id,
1682 s_tax_header_level_flag,
1683 s_tax_rounding_rule,
1684 s_wh_update_date,
1685 s_global_attribute1,
1686 s_global_attribute2,
1687 s_global_attribute3,
1688 s_global_attribute4,
1689 s_global_attribute5,
1690 s_global_attribute6,
1691 s_global_attribute7,
1692 s_global_attribute8,
1693 s_global_attribute9,
1694 s_global_attribute10,
1695 s_global_attribute11,
1696 s_global_attribute12,
1697 s_global_attribute13,
1698 s_global_attribute14,
1699 s_global_attribute15,
1700 s_global_attribute16,
1701 s_global_attribute17,
1702 s_global_attribute18,
1703 s_global_attribute19,
1704 s_global_attribute20,
1705 s_global_attribute_category,
1706 s_primary_salesrep_id,
1707 s_finchrg_receivables_trx_id,
1708 s_dates_negative_tolerance,
1709 s_dates_positive_tolerance,
1710 s_date_type_preference,
1711 s_over_shipment_tolerance,
1712 s_under_shipment_tolerance,
1713 s_item_cross_ref_pref,
1714 s_over_return_tolerance,
1715 s_under_return_tolerance,
1716 s_ship_sets_include_lines_flag,
1717 s_arv_include_lines_flag,
1718 s_sched_date_push_flag,
1719 s_invoice_quantity_rule,
1720 s_pricing_event,
1721 s_gl_id_rec,
1722 s_gl_id_rev,
1723 s_gl_id_tax,
1724 s_gl_id_freight,
1725 s_gl_id_clearing,
1726 s_gl_id_unbilled,
1727 s_gl_id_unearned,
1728 s_gl_id_unpaid_rec,
1729 s_gl_id_remittance,
1730 s_gl_id_factor,
1731 s_tax_classification;
1732 IF l_cust_site_use_csr%FOUND THEN
1733 close l_cust_site_use_csr;
1734 -- Update site use
1735 arh_csu_pkg.Update_Row(
1736 X_Site_Use_Id => l_site_use_id,
1737 X_Last_Update_Date => s_last_update_date,
1738 X_Last_Updated_By => nvl(fnd_global.user_id, 1),
1739 X_Site_Use_Code => s_site_use_code,
1740 X_customer_id => p_customer_id,
1741 X_Address_Id => s_cust_acct_site_id,
1742 X_Primary_Flag => p_primary_flag,
1743 X_Status => p_status,
1744 X_Location => s_location,
1745 X_Last_Update_Login => s_last_update_login,
1746 X_Contact_Id => s_Contact_Id,
1747 X_Bill_To_Site_Use_Id => s_Bill_To_Site_Use_Id,
1748 X_Sic_Code => s_Sic_Code,
1749 X_Payment_Term_Id => s_Payment_Term_Id,
1750 X_Gsa_Indicator => s_Gsa_Indicator,
1751 X_Ship_Partial => s_Ship_Partial,
1752 X_Ship_Via => s_Ship_Via,
1753 X_Fob_Point => s_Fob_Point,
1754 X_Order_Type_Id => s_Order_Type_Id,
1755 X_Price_List_Id => s_Price_List_Id,
1756 X_Freight_Term => s_Freight_Term,
1757 X_Warehouse_Id => s_Warehouse_Id,
1758 X_Territory_Id => s_Territory_Id,
1759 X_Tax_Code => s_Tax_Code,
1760 X_Tax_Reference => s_Tax_Reference,
1761 X_Demand_Class_Code => s_Demand_Class_Code,
1762 x_inventory_location_id => p_inv_location_id,
1763 x_inventory_organization_id => null,
1764 X_Attribute_Category => s_attribute_category,
1765 X_Attribute1 => s_attribute1,
1766 X_Attribute2 => s_attribute2,
1767 X_Attribute3 => s_attribute3,
1768 X_Attribute4 => s_attribute4,
1769 X_Attribute5 => s_attribute5,
1770 X_Attribute6 => s_attribute6,
1771 X_Attribute7 => s_attribute7,
1772 X_Attribute8 => s_attribute8,
1773 X_Attribute9 => s_attribute9,
1774 X_Attribute10 => s_attribute10,
1775 X_Attribute11 => s_attribute11,
1776 X_Attribute12 => s_attribute12,
1777 X_Attribute13 => s_attribute13,
1778 X_Attribute14 => s_attribute14,
1779 X_Attribute15 => s_attribute15,
1780 X_Attribute16 => s_attribute16,
1781 X_Attribute17 => s_attribute17,
1782 X_Attribute18 => s_attribute18,
1783 X_Attribute19 => s_attribute19,
1784 X_Attribute20 => s_attribute20,
1785 X_Attribute21 => s_attribute21,
1786 X_Attribute22 => s_attribute22,
1787 X_Attribute23 => s_attribute23,
1788 X_Attribute24 => s_attribute24,
1789 X_Attribute25 => s_attribute25,
1790 X_Tax_Classification => s_Tax_Classification,
1791 X_Tax_Header_Level_Flag => s_Tax_Header_Level_Flag,
1792 X_Tax_Rounding_Rule => s_Tax_Rounding_Rule,
1793 X_Global_Attribute_Category => s_global_attribute_category,
1794 X_Global_Attribute1 => s_global_attribute1,
1795 X_Global_Attribute2 => s_global_attribute2,
1796 X_Global_Attribute3 => s_global_attribute3,
1797 X_Global_Attribute4 => s_global_attribute4,
1798 X_Global_Attribute5 => s_global_attribute5,
1799 X_Global_Attribute6 => s_global_attribute6,
1800 X_Global_Attribute7 => s_global_attribute7,
1801 X_Global_Attribute8 => s_global_attribute8,
1802 X_Global_Attribute9 => s_global_attribute9,
1803 X_Global_Attribute10 => s_global_attribute10,
1804 X_Global_Attribute11 => s_global_attribute11,
1805 X_Global_Attribute12 => s_global_attribute12,
1806 X_Global_Attribute13 => s_global_attribute13,
1807 X_Global_Attribute14 => s_global_attribute14,
1808 X_Global_Attribute15 => s_global_attribute15,
1809 X_Global_Attribute16 => s_global_attribute16,
1810 X_Global_Attribute17 => s_global_attribute17,
1811 X_Global_Attribute18 => s_global_attribute18,
1812 X_Global_Attribute19 => s_global_attribute19,
1813 X_Global_Attribute20 => s_global_attribute20,
1814 X_Primary_Salesrep_Id => s_primary_salesrep_id,
1815 X_Finchrg_Receivables_Trx_Id => s_Finchrg_Receivables_Trx_Id,
1816 X_GL_ID_Rec => s_GL_ID_Rec,
1817 X_GL_ID_Rev => s_GL_ID_Rev,
1818 X_GL_ID_Tax => s_GL_ID_Tax,
1819 X_GL_ID_Freight => s_GL_ID_Freight,
1820 X_GL_ID_Clearing => s_GL_ID_Clearing,
1821 X_GL_ID_Unbilled => s_GL_ID_Unbilled,
1822 X_GL_ID_Unearned => s_GL_ID_Unearned,
1823 X_GL_ID_Unpaid_rec => s_GL_ID_Unpaid_rec,
1824 X_GL_ID_Remittance => s_GL_ID_Remittance,
1825 X_GL_ID_Factor => s_GL_ID_Factor,
1826 X_DATES_NEGATIVE_TOLERANCE => s_DATES_NEGATIVE_TOLERANCE,
1827 X_DATES_POSITIVE_TOLERANCE => s_DATES_POSITIVE_TOLERANCE,
1828 X_DATE_TYPE_PREFERENCE => s_DATE_TYPE_PREFERENCE,
1829 X_OVER_SHIPMENT_TOLERANCE => s_OVER_SHIPMENT_TOLERANCE,
1830 X_UNDER_SHIPMENT_TOLERANCE => s_UNDER_SHIPMENT_TOLERANCE,
1831 X_ITEM_CROSS_REF_PREF => s_ITEM_CROSS_REF_PREF,
1832 X_OVER_RETURN_TOLERANCE => s_OVER_RETURN_TOLERANCE,
1833 X_UNDER_RETURN_TOLERANCE => s_UNDER_RETURN_TOLERANCE,
1834 X_SHIP_SETS_INCLUDE_LINES_FLAG => s_SHIP_SETS_INCLUDE_LINES_FLAG,
1835 X_ARRIVALSETS_INCL_LINES_FLAG => s_arv_include_lines_flag,
1836 X_SCHED_DATE_PUSH_FLAG => s_SCHED_DATE_PUSH_FLAG,
1837 X_INVOICE_QUANTITY_RULE => s_INVOICE_QUANTITY_RULE,
1838 x_msg_count => x_msg_count,
1839 x_msg_data => x_msg_data,
1840 x_return_status => x_return_status);
1841
1842 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1843 Rollback to do_update_site_use_PUB;
1844 /* FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
1845 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
1846 FND_MSG_PUB.ADD;*/
1847 RAISE EXCP_USER_DEFINED;
1848 END IF;
1849 ELSE
1850 close l_cust_site_use_csr;
1851 END IF;
1852 if l_bill_to_site_use_id IS NOT NULL and p_status = 'I' THEN
1853 open l_cust_Bill_to_site_use_csr;
1854 fetch l_cust_Bill_to_site_use_csr into
1855 s_cust_acct_site_id,
1856 s_last_update_date,
1857 s_last_updated_by,
1858 s_creation_date,
1859 s_created_by,
1860 s_site_use_code,
1861 s_primary_flag,
1862 s_status,
1863 s_location,
1864 s_last_update_login,
1865 s_contact_id,
1866 s_bill_to_site_use_id,
1867 s_orig_system_reference,
1868 s_sic_code,
1869 s_payment_term_id,
1870 s_gsa_indicator,
1871 s_ship_partial,
1872 s_ship_via,
1873 s_fob_point,
1874 s_order_type_id,
1875 s_price_list_id,
1876 s_freight_term,
1877 s_warehouse_id,
1878 s_territory_id,
1879 s_attribute_category,
1880 s_attribute1,
1881 s_attribute2,
1882 s_attribute3,
1883 s_attribute4,
1884 s_attribute5,
1885 s_attribute6,
1886 s_attribute7,
1887 s_attribute8,
1888 s_attribute9,
1889 s_attribute10,
1890 s_request_id,
1891 s_program_application_id,
1892 s_program_id,
1893 s_program_update_date,
1894 s_tax_reference,
1895 s_sort_priority,
1896 s_tax_code,
1897 s_attribute11,
1898 s_attribute12,
1899 s_attribute13,
1900 s_attribute14,
1901 s_attribute15,
1902 s_attribute16,
1903 s_attribute17,
1904 s_attribute18,
1905 s_attribute19,
1906 s_attribute20,
1907 s_attribute21,
1908 s_attribute22,
1909 s_attribute23,
1910 s_attribute24,
1911 s_attribute25,
1912 s_last_accrue_charge_date,
1913 s_snd_last_accrue_charge_date,
1914 s_last_unaccrue_charge_date,
1915 s_snd_last_unaccrue_chrg_date,
1916 s_demand_class_code,
1917 s_org_id,
1918 s_tax_header_level_flag,
1919 s_tax_rounding_rule,
1920 s_wh_update_date,
1921 s_global_attribute1,
1922 s_global_attribute2,
1923 s_global_attribute3,
1924 s_global_attribute4,
1925 s_global_attribute5,
1926 s_global_attribute6,
1927 s_global_attribute7,
1928 s_global_attribute8,
1929 s_global_attribute9,
1930 s_global_attribute10,
1931 s_global_attribute11,
1932 s_global_attribute12,
1933 s_global_attribute13,
1934 s_global_attribute14,
1935 s_global_attribute15,
1936 s_global_attribute16,
1937 s_global_attribute17,
1938 s_global_attribute18,
1939 s_global_attribute19,
1940 s_global_attribute20,
1941 s_global_attribute_category,
1942 s_primary_salesrep_id,
1943 s_finchrg_receivables_trx_id,
1944 s_dates_negative_tolerance,
1945 s_dates_positive_tolerance,
1946 s_date_type_preference,
1947 s_over_shipment_tolerance,
1948 s_under_shipment_tolerance,
1949 s_item_cross_ref_pref,
1950 s_over_return_tolerance,
1951 s_under_return_tolerance,
1952 s_ship_sets_include_lines_flag,
1953 s_arv_include_lines_flag,
1954 s_sched_date_push_flag,
1955 s_invoice_quantity_rule,
1956 s_pricing_event,
1957 s_gl_id_rec,
1958 s_gl_id_rev,
1959 s_gl_id_tax,
1960 s_gl_id_freight,
1961 s_gl_id_clearing,
1962 s_gl_id_unbilled,
1963 s_gl_id_unearned,
1964 s_gl_id_unpaid_rec,
1965 s_gl_id_remittance,
1966 s_gl_id_factor,
1967 s_tax_classification;
1968 IF l_cust_Bill_to_site_use_csr%FOUND THEN
1969 close l_cust_Bill_to_site_use_csr;
1970
1971 -- Update site use for ship_to
1972 arh_csu_pkg.Update_Row(
1973 X_Site_Use_Id => l_Bill_to_site_use_id,
1974 X_Last_Update_Date => s_last_update_date,
1975 X_Last_Updated_By => nvl(fnd_global.user_id, 1),
1976 X_Site_Use_Code => s_site_use_code,
1977 X_customer_id => p_customer_id,
1978 X_Address_Id => s_cust_acct_site_id,
1979 X_Primary_Flag => p_primary_flag,
1980 X_Status => p_status,
1981 X_Location => s_location,
1982 X_Last_Update_Login => s_last_update_login,
1983 X_Contact_Id => s_Contact_Id,
1984 X_Bill_To_Site_Use_Id => s_Bill_To_Site_Use_Id,
1985 X_Sic_Code => s_Sic_Code,
1986 X_Payment_Term_Id => s_Payment_Term_Id,
1987 X_Gsa_Indicator => s_Gsa_Indicator,
1988 X_Ship_Partial => s_Ship_Partial,
1989 X_Ship_Via => s_Ship_Via,
1990 X_Fob_Point => s_Fob_Point,
1991 X_Order_Type_Id => s_Order_Type_Id,
1992 X_Price_List_Id => s_Price_List_Id,
1993 X_Freight_Term => s_Freight_Term,
1994 X_Warehouse_Id => s_Warehouse_Id,
1995 X_Territory_Id => s_Territory_Id,
1996 X_Tax_Code => s_Tax_Code,
1997 X_Tax_Reference => s_Tax_Reference,
1998 X_Demand_Class_Code => s_Demand_Class_Code,
1999 x_inventory_location_id => p_inv_location_id,
2000 x_inventory_organization_id => null,
2001 X_Attribute_Category => s_attribute_category,
2002 X_Attribute1 => s_attribute1,
2003 X_Attribute2 => s_attribute2,
2004 X_Attribute3 => s_attribute3,
2005 X_Attribute4 => s_attribute4,
2006 X_Attribute5 => s_attribute5,
2007 X_Attribute6 => s_attribute6,
2008 X_Attribute7 => s_attribute7,
2009 X_Attribute8 => s_attribute8,
2010 X_Attribute9 => s_attribute9,
2011 X_Attribute10 => s_attribute10,
2012 X_Attribute11 => s_attribute11,
2013 X_Attribute12 => s_attribute12,
2014 X_Attribute13 => s_attribute13,
2015 X_Attribute14 => s_attribute14,
2016 X_Attribute15 => s_attribute15,
2017 X_Attribute16 => s_attribute16,
2018 X_Attribute17 => s_attribute17,
2019 X_Attribute18 => s_attribute18,
2020 X_Attribute19 => s_attribute19,
2021 X_Attribute20 => s_attribute20,
2022 X_Attribute21 => s_attribute21,
2023 X_Attribute22 => s_attribute22,
2024 X_Attribute23 => s_attribute23,
2025 X_Attribute24 => s_attribute24,
2026 X_Attribute25 => s_attribute25,
2027 X_Tax_Classification => s_Tax_Classification,
2028 X_Tax_Header_Level_Flag => s_Tax_Header_Level_Flag,
2029 X_Tax_Rounding_Rule => s_Tax_Rounding_Rule,
2030 X_Global_Attribute_Category => s_global_attribute_category,
2031 X_Global_Attribute1 => s_global_attribute1,
2032 X_Global_Attribute2 => s_global_attribute2,
2033 X_Global_Attribute3 => s_global_attribute3,
2034 X_Global_Attribute4 => s_global_attribute4,
2035 X_Global_Attribute5 => s_global_attribute5,
2036 X_Global_Attribute6 => s_global_attribute6,
2037 X_Global_Attribute7 => s_global_attribute7,
2038 X_Global_Attribute8 => s_global_attribute8,
2039 X_Global_Attribute9 => s_global_attribute9,
2040 X_Global_Attribute10 => s_global_attribute10,
2041 X_Global_Attribute11 => s_global_attribute11,
2042 X_Global_Attribute12 => s_global_attribute12,
2043 X_Global_Attribute13 => s_global_attribute13,
2044 X_Global_Attribute14 => s_global_attribute14,
2045 X_Global_Attribute15 => s_global_attribute15,
2046 X_Global_Attribute16 => s_global_attribute16,
2047 X_Global_Attribute17 => s_global_attribute17,
2048 X_Global_Attribute18 => s_global_attribute18,
2049 X_Global_Attribute19 => s_global_attribute19,
2050 X_Global_Attribute20 => s_global_attribute20,
2051 X_Primary_Salesrep_Id => s_primary_salesrep_id,
2052 X_Finchrg_Receivables_Trx_Id => s_Finchrg_Receivables_Trx_Id,
2053 X_GL_ID_Rec => s_GL_ID_Rec,
2054 X_GL_ID_Rev => s_GL_ID_Rev,
2055 X_GL_ID_Tax => s_GL_ID_Tax,
2056 X_GL_ID_Freight => s_GL_ID_Freight,
2057 X_GL_ID_Clearing => s_GL_ID_Clearing,
2058 X_GL_ID_Unbilled => s_GL_ID_Unbilled,
2059 X_GL_ID_Unearned => s_GL_ID_Unearned,
2060 X_GL_ID_Unpaid_rec => s_GL_ID_Unpaid_rec,
2061 X_GL_ID_Remittance => s_GL_ID_Remittance,
2062 X_GL_ID_Factor => s_GL_ID_Factor,
2063 X_DATES_NEGATIVE_TOLERANCE => s_DATES_NEGATIVE_TOLERANCE,
2064 X_DATES_POSITIVE_TOLERANCE => s_DATES_POSITIVE_TOLERANCE,
2065 X_DATE_TYPE_PREFERENCE => s_DATE_TYPE_PREFERENCE,
2066 X_OVER_SHIPMENT_TOLERANCE => s_OVER_SHIPMENT_TOLERANCE,
2067 X_UNDER_SHIPMENT_TOLERANCE => s_UNDER_SHIPMENT_TOLERANCE,
2068 X_ITEM_CROSS_REF_PREF => s_ITEM_CROSS_REF_PREF,
2069 X_OVER_RETURN_TOLERANCE => s_OVER_RETURN_TOLERANCE,
2070 X_UNDER_RETURN_TOLERANCE => s_UNDER_RETURN_TOLERANCE,
2071 X_SHIP_SETS_INCLUDE_LINES_FLAG => s_SHIP_SETS_INCLUDE_LINES_FLAG,
2072 X_ARRIVALSETS_INCL_LINES_FLAG => s_arv_include_lines_flag,
2073 X_SCHED_DATE_PUSH_FLAG => s_SCHED_DATE_PUSH_FLAG,
2074 X_INVOICE_QUANTITY_RULE => s_INVOICE_QUANTITY_RULE,
2075 x_msg_count => x_msg_count,
2076 x_msg_data => x_msg_data,
2077 x_return_status => x_return_status);
2078
2079 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2080 Rollback to do_update_site_use_PUB;
2081 /* FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
2082 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
2083 FND_MSG_PUB.ADD;*/
2084 RAISE EXCP_USER_DEFINED;
2085 END IF;
2086 ELSE
2087 close l_cust_Bill_to_site_use_csr;
2088 END IF;
2089 END IF;
2090
2091
2092 -- Exception Block
2093 EXCEPTION
2094 WHEN EXCP_USER_DEFINED THEN
2095 Rollback to do_update_site_use_PUB;
2096 x_return_status := FND_API.G_RET_STS_ERROR;
2097 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2098 P_API_NAME => L_API_NAME
2099 ,P_PKG_NAME => G_PKG_NAME
2100 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2101 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2102 ,X_MSG_COUNT => x_MSG_COUNT
2103 ,X_MSG_DATA => x_MSG_DATA
2104 ,X_RETURN_STATUS => x_RETURN_STATUS);
2105 WHEN FND_API.G_EXC_ERROR THEN
2106 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2107 P_API_NAME => L_API_NAME
2108 ,P_PKG_NAME => G_PKG_NAME
2109 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2110 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2111 ,X_MSG_COUNT => x_MSG_COUNT
2112 ,X_MSG_DATA => x_MSG_DATA
2113 ,X_RETURN_STATUS => x_RETURN_STATUS);
2114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2115 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2116 P_API_NAME => L_API_NAME
2117 ,P_PKG_NAME => G_PKG_NAME
2118 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2119 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2120 ,X_MSG_COUNT => x_MSG_COUNT
2121 ,X_MSG_DATA => x_MSG_DATA
2122 ,X_RETURN_STATUS => x_RETURN_STATUS);
2123 WHEN OTHERS THEN
2124 Rollback to do_update_site_use_PUB;
2125 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2126 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
2127 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
2128 FND_MSG_PUB.ADD;
2129 fnd_msg_pub.count_and_get
2130 ( p_count => x_msg_count
2131 , p_data => x_msg_data);
2132 x_return_status := FND_API.G_RET_STS_ERROR;
2133
2134 END do_update_site_use;
2135 ------------------------------------------------------------------------------------------
2136 -- Procedure Name : ship_to_address_handler
2137 -- Purpose : If address fields is not blank:
2138 -- If location_id is blank, create a new inventory location.
2139 -- else update the existing inventory location with the addresses feed in.
2140 -- If task_assignment_id is not blank, or resource_type and resource_id is not blank,
2141 -- (only use task_assignment_id to retrieve resource_type and resource_id if the
2142 -- resource_type and resource_id pass in are blank).
2143 -- create party, customer, account, site location, party site, party site use,
2144 -- account site, account site use, location association, ...
2145 -- Return error if address fields and task_assignment_id are both blank.
2146
2147 -- The address information passed in must be validated before passing in.
2148 -- It will not create an inventory location if the address is not valid.
2149 -- Required fields are varied depends on the country. The country field
2150 -- is used to check if there is a address passed in.
2151 --
2152 PROCEDURE ship_to_address_handler
2153 (p_task_assignment_id IN NUMBER
2154 ,p_resource_type IN VARCHAR2
2155 ,p_resource_id IN NUMBER
2156 ,p_customer_id OUT NOCOPY NUMBER
2157 ,p_location_id IN OUT NOCOPY NUMBER
2158 ,p_style IN VARCHAR2
2159 ,p_address_line_1 IN VARCHAR2
2160 ,p_address_line_2 IN VARCHAR2
2161 ,p_address_line_3 IN VARCHAR2
2162 ,p_country IN VARCHAR2
2163 ,p_postal_code IN VARCHAR2
2164 ,p_region_1 IN VARCHAR2
2165 ,p_region_2 IN VARCHAR2
2166 ,p_region_3 IN VARCHAR2
2167 ,p_town_or_city IN VARCHAR2
2168 ,p_tax_name IN VARCHAR2
2169 ,p_telephone_number_1 IN VARCHAR2
2170 ,p_telephone_number_2 IN VARCHAR2
2171 ,p_telephone_number_3 IN VARCHAR2
2172 ,p_loc_information13 IN VARCHAR2
2173 ,p_loc_information14 IN VARCHAR2
2174 ,p_loc_information15 IN VARCHAR2
2175 ,p_loc_information16 IN VARCHAR2
2176 ,p_loc_information17 IN VARCHAR2
2177 ,p_loc_information18 IN VARCHAR2
2178 ,p_loc_information19 IN VARCHAR2
2179 ,p_loc_information20 IN VARCHAR2
2180 ,p_timezone IN VARCHAR2
2181 ,p_primary_flag IN VARCHAR2
2182 ,p_status IN VARCHAR2
2183 ,p_object_version_number IN OUT NOCOPY NUMBER
2184 ,p_api_version_number IN NUMBER
2185 ,p_init_msg_list IN VARCHAR2
2186 ,p_commit IN VARCHAR2
2187 ,p_attribute_category IN VARCHAR2
2188 ,p_attribute1 IN VARCHAR2
2189 ,p_attribute2 IN VARCHAR2
2190 ,p_attribute3 IN VARCHAR2
2191 ,p_attribute4 IN VARCHAR2
2192 ,p_attribute5 IN VARCHAR2
2193 ,p_attribute6 IN VARCHAR2
2194 ,p_attribute7 IN VARCHAR2
2195 ,p_attribute8 IN VARCHAR2
2196 ,p_attribute9 IN VARCHAR2
2197 ,p_attribute10 IN VARCHAR2
2198 ,p_attribute11 IN VARCHAR2
2199 ,p_attribute12 IN VARCHAR2
2200 ,p_attribute13 IN VARCHAR2
2201 ,p_attribute14 IN VARCHAR2
2202 ,p_attribute15 IN VARCHAR2
2203 ,p_attribute16 IN VARCHAR2
2204 ,p_attribute17 IN VARCHAR2
2205 ,p_attribute18 IN VARCHAR2
2206 ,p_attribute19 IN VARCHAR2
2207 ,p_attribute20 IN VARCHAR2
2208 ,x_return_status OUT NOCOPY VARCHAR2
2209 ,x_msg_count OUT NOCOPY NUMBER
2210 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2211
2212 l_api_version_number CONSTANT NUMBER := 1.0;
2213 l_api_name CONSTANT VARCHAR2(30) := 'ship_to_address_handler';
2214 EXCP_USER_DEFINED EXCEPTION;
2215
2216 l_customer_profile_class_id AR_CUSTOMER_PROFILE_CLASSES_V.customer_profile_class_id%TYPE := 0; -- internal customer
2217 l_customer_id hz_cust_accounts.cust_account_id%TYPE := NULL;
2218 t_customer_id hz_cust_accounts.cust_account_id%TYPE := NULL;
2219 l_location_id hr_locations_all.location_id%TYPE := NULL;
2220 l_inv_location_id hr_locations_all.location_id%TYPE := NULL;
2221 l_resource_id jtf_task_assignments.resource_id%TYPE := NULL;
2222 l_resource_type jtf_task_assignments.resource_type_code%TYPE := NULL;
2223 l_resource_name jtf_rs_resource_extns_vl.resource_name%TYPE := NULL;
2224 l_party_id hz_parties.party_id%TYPE := NULL;
2225 l_first_name hz_parties.person_first_name%TYPE := NULL;
2226 l_middle_name hz_parties.person_middle_name%TYPE := NULL;
2227 l_last_name hz_parties.person_last_name%TYPE := NULL;
2228 l_account_number hz_cust_accounts.account_number%TYPE := NULL;
2229 l_customer_number hz_cust_accounts.account_number%TYPE := NULL;
2230 l_Attribute_Category hz_cust_accounts.attribute_category%TYPE := NULL;
2231 l_Attribute1 hz_cust_accounts.attribute1%TYPE := NULL;
2232 l_Attribute2 hz_cust_accounts.attribute2%TYPE := NULL;
2233 l_Attribute3 hz_cust_accounts.attribute3%TYPE := NULL;
2234 l_Attribute4 hz_cust_accounts.attribute4%TYPE := NULL;
2235 l_Attribute5 hz_cust_accounts.attribute5%TYPE := NULL;
2236 l_Attribute6 hz_cust_accounts.attribute6%TYPE := NULL;
2237 l_Attribute7 hz_cust_accounts.attribute7%TYPE := NULL;
2238 l_Attribute8 hz_cust_accounts.attribute8%TYPE := NULL;
2239 l_Attribute9 hz_cust_accounts.attribute9%TYPE := NULL;
2240 l_Attribute10 hz_cust_accounts.attribute10%TYPE := NULL;
2241 l_Attribute11 hz_cust_accounts.attribute11%TYPE := NULL;
2242 l_Attribute12 hz_cust_accounts.attribute12%TYPE := NULL;
2243 l_Attribute13 hz_cust_accounts.attribute13%TYPE := NULL;
2244 l_Attribute14 hz_cust_accounts.attribute14%TYPE := NULL;
2245 l_Attribute15 hz_cust_accounts.attribute15%TYPE := NULL;
2246 l_Attribute16 hz_cust_accounts.attribute16%TYPE := NULL;
2247 l_Attribute17 hz_cust_accounts.attribute17%TYPE := NULL;
2248 l_Attribute18 hz_cust_accounts.attribute18%TYPE := NULL;
2249 l_Attribute19 hz_cust_accounts.attribute19%TYPE := NULL;
2250 l_Attribute20 hz_cust_accounts.attribute20%TYPE := NULL;
2251 l_global_attribute_category hz_cust_accounts.global_attribute_category%TYPE := NULL;
2252 l_global_attribute1 hz_cust_accounts.attribute1%TYPE := NULL;
2253 l_global_attribute2 hz_cust_accounts.attribute2%TYPE := NULL;
2254 l_global_attribute3 hz_cust_accounts.attribute3%TYPE := NULL;
2255 l_global_attribute4 hz_cust_accounts.attribute4%TYPE := NULL;
2256 l_global_attribute5 hz_cust_accounts.attribute5%TYPE := NULL;
2257 l_global_attribute6 hz_cust_accounts.attribute6%TYPE := NULL;
2258 l_global_attribute7 hz_cust_accounts.attribute7%TYPE := NULL;
2259 l_global_attribute8 hz_cust_accounts.attribute8%TYPE := NULL;
2260 l_global_attribute9 hz_cust_accounts.attribute9%TYPE := NULL;
2261 l_global_attribute10 hz_cust_accounts.attribute10%TYPE := NULL;
2262 l_global_attribute11 hz_cust_accounts.attribute11%TYPE := NULL;
2263 l_global_attribute12 hz_cust_accounts.attribute12%TYPE := NULL;
2264 l_global_attribute13 hz_cust_accounts.attribute13%TYPE := NULL;
2265 l_global_attribute14 hz_cust_accounts.attribute14%TYPE := NULL;
2266 l_global_attribute15 hz_cust_accounts.attribute15%TYPE := NULL;
2267 l_global_attribute16 hz_cust_accounts.attribute16%TYPE := NULL;
2268 l_global_attribute17 hz_cust_accounts.attribute17%TYPE := NULL;
2269 l_global_attribute18 hz_cust_accounts.attribute18%TYPE := NULL;
2270 l_global_attribute19 hz_cust_accounts.attribute19%TYPE := NULL;
2271 l_global_attribute20 hz_cust_accounts.attribute20%TYPE := NULL;
2272 l_jgzz_attribute_category hz_cust_accounts.global_attribute_category%TYPE := NULL;
2273 l_jgzz_attribute1 hz_cust_accounts.attribute1%TYPE := NULL;
2274 l_jgzz_attribute2 hz_cust_accounts.attribute2%TYPE := NULL;
2275 l_jgzz_attribute3 hz_cust_accounts.attribute3%TYPE := NULL;
2276 l_jgzz_attribute4 hz_cust_accounts.attribute4%TYPE := NULL;
2277 l_jgzz_attribute5 hz_cust_accounts.attribute5%TYPE := NULL;
2278 l_jgzz_attribute6 hz_cust_accounts.attribute6%TYPE := NULL;
2279 l_jgzz_attribute7 hz_cust_accounts.attribute7%TYPE := NULL;
2280 l_jgzz_attribute8 hz_cust_accounts.attribute8%TYPE := NULL;
2281 l_jgzz_attribute9 hz_cust_accounts.attribute9%TYPE := NULL;
2282 l_jgzz_attribute10 hz_cust_accounts.attribute10%TYPE := NULL;
2283 l_jgzz_attribute11 hz_cust_accounts.attribute11%TYPE := NULL;
2284 l_jgzz_attribute12 hz_cust_accounts.attribute12%TYPE := NULL;
2285 l_jgzz_attribute13 hz_cust_accounts.attribute13%TYPE := NULL;
2286 l_jgzz_attribute14 hz_cust_accounts.attribute14%TYPE := NULL;
2287 l_jgzz_attribute15 hz_cust_accounts.attribute15%TYPE := NULL;
2288 l_orig_system_reference hz_cust_accounts.orig_system_reference%TYPE := NULL;
2289 l_status hz_cust_accounts.status%TYPE := 'A'; -- Active
2290 l_customer_type hz_cust_accounts.customer_type%TYPE := 'I'; -- Internal
2291 l_customer_class_code hz_cust_accounts.customer_class_code%TYPE := NULL;
2292 l_primary_salesrep_id hz_cust_accounts.primary_salesrep_id%TYPE := NULL;
2293 l_sales_channel_code hz_cust_accounts.sales_channel_code%TYPE := NULL;
2294 l_order_type_id hz_cust_accounts.order_type_id%TYPE := NULL;
2295 l_price_list_id hz_cust_accounts.price_list_id%TYPE := NULL;
2296 l_category_code hz_cust_accounts.subcategory_code%TYPE := NULL;
2297 l_reference_use_flag VARCHAR2(1) := 'N';
2298 l_tax_code hz_cust_accounts.tax_code%TYPE := NULL;
2299 l_third_party_flag VARCHAR2(1) := 'N';
2300 l_competitor_flag VARCHAR2(1) := 'N';
2301 l_fob_point hz_cust_accounts.fob_point%TYPE := NULL;
2302 l_tax_header_level_flag hz_cust_accounts.tax_header_level_flag%TYPE := NULL;
2303 l_tax_rounding_rule hz_cust_accounts.tax_rounding_rule%TYPE := NULL;
2304 l_account_name hz_cust_accounts.account_name%TYPE := NULL;
2305 l_freight_term hz_cust_accounts.freight_term%TYPE := NULL;
2306 l_ship_partial hz_cust_accounts.ship_partial%TYPE := NULL;
2307 l_ship_via hz_cust_accounts.ship_via%TYPE := NULL;
2308 l_warehouse_id hz_cust_accounts.warehouse_id%TYPE := NULL;
2309 l_payment_term_id hz_cust_accounts.payment_term_id%TYPE := NULL;
2310 l_DATES_NEGATIVE_TOLERANCE hz_cust_accounts.DATES_NEGATIVE_TOLERANCE%TYPE := NULL;
2311 l_DATES_POSITIVE_TOLERANCE hz_cust_accounts.DATES_POSITIVE_TOLERANCE%TYPE := NULL;
2312 l_DATE_TYPE_PREFERENCE hz_cust_accounts.DATE_TYPE_PREFERENCE%TYPE := NULL;
2313 l_OVER_SHIPMENT_TOLERANCE hz_cust_accounts.OVER_SHIPMENT_TOLERANCE%TYPE := NULL;
2314 l_UNDER_SHIPMENT_TOLERANCE hz_cust_accounts.UNDER_SHIPMENT_TOLERANCE%TYPE := NULL;
2315 l_ITEM_CROSS_REF_PREF hz_cust_accounts.ITEM_CROSS_REF_PREF%TYPE := NULL;
2316 l_OVER_RETURN_TOLERANCE hz_cust_accounts.OVER_RETURN_TOLERANCE%TYPE := NULL;
2317 l_UNDER_RETURN_TOLERANCE hz_cust_accounts.UNDER_RETURN_TOLERANCE%TYPE := NULL;
2318 l_SHIP_SETS_INCLUDE_LINES_FLAG hz_cust_accounts.SHIP_SETS_INCLUDE_LINES_FLAG%TYPE := 'N';
2319 l_ARRIVALSETS_INCL_LINES_FLAG hz_cust_accounts.ARRIVALSETS_INCLUDE_LINES_FLAG%TYPE := 'N';
2320 l_SCHED_DATE_PUSH_FLAG hz_cust_accounts.SCHED_DATE_PUSH_FLAG%TYPE := 'N';
2321 l_INVOICE_QUANTITY_RULE hz_cust_accounts.INVOICE_QUANTITY_RULE%TYPE := NULL;
2322 l_party_number hz_parties.party_number%TYPE := NULL;
2323 l_customer_key hz_parties.customer_key%TYPE := NULL;
2324 l_person_profile_id hz_person_profiles.person_profile_id%TYPE := NULL;
2325 l_pre_name_adjunct hz_parties.person_pre_name_adjunct%TYPE := NULL;
2326 l_name_suffix hz_parties.person_name_suffix%TYPE := NULL;
2327 l_tax_reference hz_person_profiles.tax_reference%TYPE := NULL;
2328 l_taxpayer_id hz_parties.jgzz_fiscal_code%TYPE := NULL;
2329 l_party_name_phonetic hz_parties.organization_name_phonetic%TYPE := NULL;
2330 l_customer_profile_id hz_customer_profiles.cust_account_profile_id%TYPE := NULL;
2331 l_collector_id hz_customer_profiles.collector_id%TYPE := NULL;
2332 l_collector_name VARCHAR2(80) := NULL;
2333 l_credit_analyst_id hz_customer_profiles.credit_analyst_id%TYPE := NULL;
2334 l_credit_checking hz_customer_profiles.credit_checking%TYPE := 'Y';
2335 l_next_credit_review_date hz_customer_profiles.next_credit_review_date%TYPE := NULL;
2336 l_tolerance hz_customer_profiles.tolerance%TYPE := 0;
2337 l_discount_terms hz_customer_profiles.discount_terms%TYPE := 'Y';
2338 l_dunning_letters hz_customer_profiles.dunning_letters%TYPE := 'Y';
2339 l_interest_charges hz_customer_profiles.interest_charges%TYPE := 'Y';
2340 l_send_statements hz_customer_profiles.send_statements%TYPE := 'Y';
2341 l_statement_cycle_name VARCHAR2(80) := NULL;
2342 l_standard_terms_name VARCHAR2(80) := NULL;
2343 l_credit_balance_statements hz_customer_profiles.credit_balance_statements%TYPE := 'Y';
2344 l_credit_hold hz_customer_profiles.credit_hold%TYPE := 'N';
2345 l_profile_class_id hz_customer_profiles.profile_class_id%TYPE := 0;
2346 l_site_use_id hz_customer_profiles.site_use_id%TYPE := NULL;
2347 l_credit_rating hz_customer_profiles.credit_rating%TYPE := NULL;
2348 l_risk_code hz_customer_profiles.risk_code%TYPE := NULL;
2349 l_standard_terms hz_customer_profiles.standard_terms%TYPE := NULL;
2350 l_override_terms hz_customer_profiles.override_terms%TYPE := 'Y';
2351 l_dunning_letter_set_id hz_customer_profiles.dunning_letter_set_id%TYPE := NULL;
2352 l_dunning_letter_set_name VARCHAR2(80) := NULL;
2353 l_interest_period_days hz_customer_profiles.interest_period_days%TYPE := NULL;
2354 l_payment_grace_days hz_customer_profiles.payment_grace_days%TYPE := 0;
2355 l_discount_grace_days hz_customer_profiles.discount_grace_days%TYPE := 0;
2356 l_statement_cycle_id hz_customer_profiles.statement_cycle_id%TYPE := NULL;
2357 l_account_status hz_customer_profiles.account_status%TYPE := NULL;
2358 l_percent_collectable hz_customer_profiles.percent_collectable%TYPE := NULL;
2359 l_autocash_hierarchy_id hz_customer_profiles.autocash_hierarchy_id%TYPE := NULL;
2360 l_auto_rec_incl_disputed_flag hz_customer_profiles.auto_rec_incl_disputed_flag%TYPE := 'Y';
2361 l_autocash_hierarchy_name VARCHAR2(80) := NULL;
2362 l_autocash_hierarchy_name_adr VARCHAR2(80) := NULL;
2363 l_tax_printing_option hz_customer_profiles.tax_printing_option%TYPE := NULL;
2364 l_charge_on_fin_charge_flag hz_customer_profiles.charge_on_finance_charge_flag%TYPE := 'N';
2365 l_grouping_rule_id hz_customer_profiles.grouping_rule_id%TYPE := NULL;
2366 l_grouping_rule_name VARCHAR2(80) := NULL;
2367 l_clearing_days hz_customer_profiles.clearing_days%TYPE := NULL;
2368 l_cons_inv_flag hz_customer_profiles.cons_inv_flag%TYPE := 'N';
2369 l_cons_inv_type hz_customer_profiles.cons_inv_type%TYPE := NULL;
2370 l_autocash_hier_id_for_adr hz_customer_profiles.autocash_hierarchy_id_for_adr%TYPE := NULL;
2371 l_lockbox_matching_option hz_customer_profiles.lockbox_matching_option%TYPE := NULL;
2372 l_lockbox_matching_name VARCHAR2(80) := NULL;
2373
2374 s_Address_Id hz_locations.location_id%TYPE := NULL;
2375 s_Status hz_party_sites.Status%TYPE := 'A';
2376 s_Orig_System_Reference hz_locations.Orig_System_Reference%TYPE := NULL;
2377 s_Country hz_locations.Country%TYPE := NULL;
2378 s_Address1 hz_locations.Address1%TYPE := NULL;
2379 s_Address2 hz_locations.Address2%TYPE := NULL;
2380 s_Address3 hz_locations.Address3%TYPE := NULL;
2381 s_Address4 hz_locations.Address4%TYPE := NULL;
2382 s_City hz_locations.City%TYPE := NULL;
2383 s_Postal_Code hz_locations.Postal_Code%TYPE := NULL;
2384 s_State hz_locations.State%TYPE := NULL;
2385 s_Province hz_locations.Province%TYPE := NULL;
2386 s_County hz_locations.County%TYPE := NULL;
2387 s_Last_Update_Login hz_locations.Last_Update_Login%TYPE := nvl(fnd_global.user_id,1);
2388 s_Address_Key hz_locations.Address_Key%TYPE := NULL;
2389 s_Language hz_locations.Language%TYPE := NULL;
2390 s_Attribute_Category hz_locations.Attribute_Category%TYPE := NULL;
2391 s_Attribute1 hz_locations.Attribute1%TYPE := NULL;
2392 s_Attribute2 hz_locations.Attribute2%TYPE := NULL;
2393 s_Attribute3 hz_locations.Attribute3%TYPE := NULL;
2394 s_Attribute4 hz_locations.Attribute4%TYPE := NULL;
2395 s_Attribute5 hz_locations.Attribute5%TYPE := NULL;
2396 s_Attribute6 hz_locations.Attribute6%TYPE := NULL;
2397 s_Attribute7 hz_locations.Attribute7%TYPE := NULL;
2398 s_Attribute8 hz_locations.Attribute8%TYPE := NULL;
2399 s_Attribute9 hz_locations.Attribute9%TYPE := NULL;
2400 s_Attribute10 hz_locations.Attribute10%TYPE := NULL;
2401 s_Attribute11 hz_locations.Attribute11%TYPE := NULL;
2402 s_Attribute12 hz_locations.Attribute12%TYPE := NULL;
2403 s_Attribute13 hz_locations.Attribute13%TYPE := NULL;
2404 s_Attribute14 hz_locations.Attribute14%TYPE := NULL;
2405 s_Attribute15 hz_locations.Attribute15%TYPE := NULL;
2406 s_Attribute16 hz_locations.Attribute16%TYPE := NULL;
2407 s_Attribute17 hz_locations.Attribute17%TYPE := NULL;
2408 s_Attribute18 hz_locations.Attribute18%TYPE := NULL;
2409 s_Attribute19 hz_locations.Attribute19%TYPE := NULL;
2410 s_Attribute20 hz_locations.Attribute20%TYPE := NULL;
2411 s_Global_Attribute_Category hz_locations.Global_Attribute_Category%TYPE := NULL;
2412 s_Global_Attribute1 hz_locations.Global_Attribute1%TYPE := NULL;
2413 s_Global_Attribute2 hz_locations.Global_Attribute2%TYPE := NULL;
2414 s_Global_Attribute3 hz_locations.Global_Attribute3%TYPE := NULL;
2415 s_Global_Attribute4 hz_locations.Global_Attribute4%TYPE := NULL;
2416 s_Global_Attribute5 hz_locations.Global_Attribute5%TYPE := NULL;
2417 s_Global_Attribute6 hz_locations.Global_Attribute6%TYPE := NULL;
2418 s_Global_Attribute7 hz_locations.Global_Attribute7%TYPE := NULL;
2419 s_Global_Attribute8 hz_locations.Global_Attribute8%TYPE := NULL;
2420 s_Global_Attribute9 hz_locations.Global_Attribute9%TYPE := NULL;
2421 s_Global_Attribute10 hz_locations.Global_Attribute10%TYPE := NULL;
2422 s_Global_Attribute11 hz_locations.Global_Attribute11%TYPE := NULL;
2423 s_Global_Attribute12 hz_locations.Global_Attribute12%TYPE := NULL;
2424 s_Global_Attribute13 hz_locations.Global_Attribute13%TYPE := NULL;
2425 s_Global_Attribute14 hz_locations.Global_Attribute14%TYPE := NULL;
2426 s_Global_Attribute15 hz_locations.Global_Attribute15%TYPE := NULL;
2427 s_Global_Attribute16 hz_locations.Global_Attribute16%TYPE := NULL;
2428 s_Global_Attribute17 hz_locations.Global_Attribute17%TYPE := NULL;
2429 s_Global_Attribute18 hz_locations.Global_Attribute18%TYPE := NULL;
2430 s_Global_Attribute19 hz_locations.Global_Attribute19%TYPE := NULL;
2431 s_Global_Attribute20 hz_locations.Global_Attribute20%TYPE := NULL;
2432
2433 s_Address_warning BOOLEAN;
2434 s_Address_Lines_Phonetic hz_locations.Address_Lines_Phonetic%TYPE := NULL;
2435 s_Party_site_id hz_party_sites.Party_site_id%TYPE := NULL;
2436 s_Party_id hz_party_sites.Party_id%TYPE := NULL;
2437 s_Location_id hz_party_sites.Location_id%TYPE := NULL;
2438 s_Party_Site_Number hz_party_sites.Party_Site_Number%TYPE := NULL;
2439 s_Identifying_address_flag hz_party_sites.Identifying_address_flag%TYPE := 'N';
2440 s_Cust_acct_site_id hz_cust_acct_sites_all.Cust_acct_site_id%TYPE := NULL;
2441 s_Cust_account_id hz_cust_acct_sites_all.Cust_account_id%TYPE := NULL;
2442 s_su_Bill_To_Flag hz_cust_acct_sites_all.Bill_To_Flag%TYPE := 'Y';
2443 s_su_Ship_To_Flag hz_cust_acct_sites_all.Ship_To_Flag%TYPE := 'Y';
2444 s_su_Market_Flag hz_cust_acct_sites_all.Market_Flag%TYPE := 'Y';
2445 s_su_stmt_flag VARCHAR2(1) := 'N';
2446 s_su_dun_flag VARCHAR2(1) := 'N';
2447 s_su_legal_flag VARCHAR2(1) := 'N';
2448 s_Customer_Category hz_cust_acct_sites_all.Customer_Category_code%TYPE := NULL;
2449 s_Key_Account_Flag hz_cust_acct_sites_all.Key_Account_Flag%TYPE := 'N';
2450 s_Territory_id hz_cust_acct_sites_all.Territory_id%TYPE := NULL;
2451 s_ece_tp_location_code hz_cust_acct_sites_all.ece_tp_location_code%TYPE := NULL;
2452 s_address_mode fnd_territories.address_style%TYPE := 'STANDARD';
2453 s_territory hz_cust_acct_sites_all.territory%TYPE := NULL;
2454 s_translated_customer_name hz_cust_acct_sites_all.translated_customer_name%TYPE := NULL;
2455 s_sales_tax_geo_code hz_locations.sales_tax_geocode%TYPE := NULL;
2456 s_sale_tax_inside_city_limits hz_locations.sales_tax_inside_city_limits%TYPE := '1';
2457 s_ADDRESSEE VARCHAR2(50) := NULL;
2458 s_shared_party_site VARCHAR2(1) := 'N';
2459 s_update_account_site VARCHAR2(1) := 'N';
2460 s_create_location_party_site VARCHAR2(1) := 'N';
2461 t_site_use_id hz_cust_site_uses_all.site_use_id%TYPE;
2462 t_cust_acct_site_id hz_cust_site_uses_all.cust_acct_site_id%TYPE;
2463 t_primary_flag hz_cust_site_uses_all.primary_flag%TYPE;
2464 t_status hz_cust_site_uses_all.status%TYPE;
2465 t_address_id po_location_associations_all.address_id%TYPE;
2466 t_site_loc_id hr_locations.location_id%TYPE;
2467 t_orig_system_reference hz_locations.orig_system_reference%TYPE;
2468 t_last_update_date DATE;
2469 t_party_site_last_update_date DATE;
2470 t_loc_last_update_date DATE;
2471 t_party_id hz_cust_accounts.party_id%TYPE;
2472 t_party_site_id hz_party_sites.party_site_id%TYPE;
2473 t_party_site_number hz_party_sites.party_site_number%TYPE;
2474 t_Bill_To_Flag hz_cust_acct_sites_all.Bill_To_Flag%TYPE;
2475 t_Ship_To_Flag hz_cust_acct_sites_all.Ship_To_Flag%TYPE;
2476 t_Market_Flag hz_cust_acct_sites_all.Market_Flag%TYPE;
2477 t_Customer_Category hz_cust_acct_sites_all.Customer_Category_code%TYPE;
2478 t_Key_Account_Flag hz_cust_acct_sites_all.Key_Account_Flag%TYPE;
2479 t_territory_id hz_cust_acct_sites_all.territory_id%TYPE;
2480 t_territory hz_cust_acct_sites_all.territory%TYPE;
2481 t_ece_tp_location_code hz_cust_acct_sites_all.ece_tp_location_code%TYPE;
2482 t_translated_customer_name hz_cust_acct_sites_all.translated_customer_name%TYPE;
2483 -- l_sql_str varchar2(1000);
2484 -- l_where_clause varchar2(500);
2485 t_inv_location_id csp_requirement_headers.ship_to_location_id%TYPE;
2486 l_timezone_id csp_requirement_headers.timezone_id%TYPE;
2487 l_rs_cust_relation_id number;
2488 l_process_type varchar2(30);
2489 l_column_NAME varchar2(30);
2490
2491
2492 CURSOR c_party_id IS
2493 select papf.party_id
2494 from jtf_rs_resource_extns jrre,
2495 per_all_people_f papf
2496 where papf.person_id = jrre.source_id
2497 and jrre.resource_id = l_resource_id
2498 and trunc(sysdate) between trunc(papf.effective_start_date) and
2499 trunc(papf.effective_end_date)
2500 and jrre.category = 'EMPLOYEE'
2501 UNION ALL
2502 select hp.party_id
2503 from jtf_rs_resource_extns jrre,
2504 hz_parties hp
2505 where hp.party_id = jrre.source_id
2506 and jrre.resource_id = l_resource_id
2507 and jrre.category = 'PARTY';
2508
2509
2510 CURSOR l_resource_id_csr IS
2511 select resource_type_code, resource_id from jtf_task_assignments where task_assignment_id = p_task_assignment_id and assignee_role = 'ASSIGNEE';
2512
2513 CURSOR l_customer_id_csr IS
2514 select rs_cust_relation_id,
2515 customer_id
2516 from csp_rs_cust_relations
2517 where resource_type = l_resource_type
2518 and resource_id = l_resource_id;
2519
2520 CURSOR l_party_id_csr IS
2521 select party_id from hz_cust_accounts where cust_account_id = l_customer_id;
2522
2523 CURSOR l_requirement_hdr_csr IS
2524 -- select ship_to_location_id from csp_requirement_headers where task_assignment_id = p_task_assignment_id;
2525 select ship_to_location_id,
2526 timezone_id
2527 from csp_requirement_headers
2528 where task_assignment_id = p_task_assignment_id;
2529
2530 CURSOR l_resource_name_csr IS
2531 select resource_name,
2532 source_first_name,
2533 source_middle_name,
2534 source_last_name
2535 from jtf_rs_resource_extns_vl
2536 where category = substr(l_resource_type, 4) and resource_id = l_resource_id;
2537
2538 -- CURSOR l_sql_str_csr IS
2539 -- select 'select '||select_id||' select_id, '||select_name||' select_name '|| 'from '||from_table sql_str, where_clause
2540 -- from jtf_objects_vl where object_code = l_resource_type;
2541
2542 CURSOR l_party_number_csr IS
2543 select hz_parties_s.nextval from dual;
2544
2545 CURSOR l_customer_number_csr IS
2546 select hz_cust_accounts_s.nextval from dual;
2547
2548 CURSOR l_party_site_number_csr IS
2549 select hz_party_sites_s.nextval from dual;
2550
2551 CURSOR l_po_loc_association_csr IS
2552 select p.customer_id,
2553 p.address_id,
2554 p.site_use_id,
2555 ps.location_id,
2556 p.address_id,
2557 ps.identifying_address_flag,
2558 ps.status,
2559 z.orig_system_reference,
2560 ps.party_id,
2561 c.party_site_id,
2562 ps.party_site_number,
2563 c.bill_to_flag,
2564 c.market_flag,
2565 c.ship_to_flag,
2566 c.customer_category_code,
2567 c.key_account_flag,
2568 c.territory_id,
2569 c.territory,
2570 c.ece_tp_location_code,
2571 c.translated_customer_name,
2572 c.last_update_date,
2573 ps.last_update_date,
2574 z.last_update_date
2575 from po_location_associations p, hz_locations z, hz_cust_acct_sites c, hz_party_sites ps
2576 where p.address_id = c.cust_acct_site_id and c.party_site_id = ps.party_site_id and ps.location_id = z.location_id and p.location_id = l_inv_location_id;
2577
2578 CURSOR l_inv_loc_csr IS
2579 select address_line_1, address_line_2, address_line_3, town_or_city, country, postal_code, region_1, region_2, region_3, attribute_category,
2580 attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11,
2581 attribute12, attribute13, attribute14, attribute15, attribute16, attribute17, attribute18, attribute19, attribute20, global_attribute_category,
2582 global_attribute1, global_attribute2, global_attribute3, global_attribute4, global_attribute5, global_attribute6, global_attribute7,
2583 global_attribute8, global_attribute9, global_attribute10, global_attribute11, global_attribute12, global_attribute13, global_attribute14,
2584 global_attribute15, global_attribute16, global_attribute17, global_attribute18, global_attribute19, global_attribute20 from hr_locations where location_id = l_inv_location_id;
2585
2586 CURSOR l_identify_address_flag_csr IS
2587 select identifying_address_flag from hz_party_sites where party_id = l_party_id and identifying_address_flag = 'Y' and status = 'A';
2588
2589 Cursor province_enabled IS
2590 select APPLICATION_COLUMN_NAME
2591 from FND_DESCR_FLEX_COL_USAGE_VL
2592 where DESCRIPTIVE_FLEXFIELD_NAME like 'Address Location'
2593 and upper(END_USER_COLUMN_NAME) like 'PROVINCE'
2594 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_style;
2595
2596
2597
2598
2599 BEGIN
2600 SAVEPOINT ship_to_address_handler_PUB;
2601
2602 -- initialize message list
2603 FND_MSG_PUB.initialize;
2604
2605 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2606 p_api_version_number,
2607 l_api_name,
2608 G_PKG_NAME) THEN
2609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2610 END IF;
2611
2612 x_return_status := FND_API.G_RET_STS_SUCCESS;
2613 select userenv('LANG') into s_language from dual;
2614 csp_ship_to_address_pvt.g_rs_cust_relation_id := null;
2615 csp_ship_to_address_pvt.call_internal_hook('CSP_SHIP_TO_ADDRESS_PVT','SHIP_TO_ADDRESS_HANDLER','B',x_return_status);
2616
2617 -- If task assignment id and addresses are both null, return error.
2618 IF (p_task_assignment_id IS NULL) and (p_country IS NULL) THEN
2619 FND_MESSAGE.SET_NAME ('CSP', 'CSP_TSK_ASSGN_ID_OR_ADDR_REQD');
2620 FND_MSG_PUB.ADD;
2621 RAISE EXCP_USER_DEFINED;
2622 END IF;
2623
2624
2625 -- If address fields are not null:
2626 -- If location_id is null, create a new inventory location.
2627 -- If location_id is not null, update the existing inventory location.
2628 IF p_country IS NOT NULL THEN
2629 BEGIN
2630 IF p_location_id IS NULL THEN
2631 do_create_ship_to_location(
2632 p_location_id => p_location_id,
2633 p_style => p_style,
2634 p_address_line_1 => p_address_line_1,
2635 p_address_line_2 => p_address_line_2,
2636 p_address_line_3 => p_address_line_3,
2637 p_country => p_country,
2638 p_postal_code => p_postal_code,
2639 p_region_1 => p_region_1,
2640 p_region_2 => p_region_2,
2641 p_region_3 => p_region_3,
2642 p_town_or_city => p_town_or_city,
2643 p_tax_name => p_tax_name,
2644 p_telephone_number_1 => p_telephone_number_1,
2645 p_telephone_number_2 => p_telephone_number_2,
2646 p_telephone_number_3 => p_telephone_number_3,
2647 p_loc_information13 => p_loc_information13,
2648 p_loc_information14 => p_loc_information14,
2649 p_loc_information15 => p_loc_information15,
2650 p_loc_information16 => p_loc_information16,
2651 p_loc_information17 => p_loc_information17,
2652 p_loc_information18 => p_loc_information18,
2653 p_loc_information19 => p_loc_information19,
2654 p_loc_information20 => p_loc_information20,
2655 p_object_version_number => p_object_version_number,
2656 p_attribute1 => p_attribute1,
2657 p_attribute2 => p_attribute2,
2658 p_attribute3 => p_attribute3,
2659 p_attribute4 => p_attribute4,
2660 p_attribute5 => p_attribute5,
2661 p_attribute6 => p_attribute6,
2662 p_attribute7 => p_attribute7,
2663 p_attribute8 => p_attribute8,
2664 p_attribute9 => p_attribute9,
2665 p_attribute10 => p_attribute10,
2666 p_attribute11 => p_attribute11,
2667 p_attribute12 => p_attribute12,
2668 p_attribute13 => p_attribute13,
2669 p_attribute14 => p_attribute14,
2670 p_attribute15 => p_attribute15,
2671 p_attribute16 => p_attribute16,
2672 p_attribute17 => p_attribute17,
2673 p_attribute18 => p_attribute18,
2674 p_attribute19 => p_attribute19,
2675 p_attribute20 => p_attribute20,
2676 p_attribute_category => p_attribute_category,
2677 x_return_status => x_return_status,
2678 x_msg_count => x_msg_count,
2679 x_msg_data => x_msg_data);
2680 l_process_type := 'INSERT';
2681 ELSE
2682 do_update_ship_to_location(
2683 p_location_id => p_location_id,
2684 p_style => p_style,
2685 p_address_line_1 => p_address_line_1,
2686 p_address_line_2 => p_address_line_2,
2687 p_address_line_3 => p_address_line_3,
2688 p_country => p_country,
2689 p_postal_code => p_postal_code,
2690 p_region_1 => p_region_1,
2691 p_region_2 => p_region_2,
2692 p_region_3 => p_region_3,
2693 p_town_or_city => p_town_or_city,
2694 p_tax_name => p_tax_name,
2695 p_telephone_number_1 => p_telephone_number_1,
2696 p_telephone_number_2 => p_telephone_number_2,
2697 p_telephone_number_3 => p_telephone_number_3,
2698 p_loc_information13 => p_loc_information13,
2699 p_loc_information14 => p_loc_information14,
2700 p_loc_information15 => p_loc_information15,
2701 p_loc_information16 => p_loc_information16,
2702 p_loc_information17 => p_loc_information17,
2703 p_loc_information18 => p_loc_information18,
2704 p_loc_information19 => p_loc_information19,
2705 p_loc_information20 => p_loc_information20,
2706 p_object_version_number => p_object_version_number,
2707 p_attribute1 => p_attribute1,
2708 p_attribute2 => p_attribute2,
2709 p_attribute3 => p_attribute3,
2710 p_attribute4 => p_attribute4,
2711 p_attribute5 => p_attribute5,
2712 p_attribute6 => p_attribute6,
2713 p_attribute7 => p_attribute7,
2714 p_attribute8 => p_attribute8,
2715 p_attribute9 => p_attribute9,
2716 p_attribute10 => p_attribute10,
2717 p_attribute11 => p_attribute11,
2718 p_attribute12 => p_attribute12,
2719 p_attribute13 => p_attribute13,
2720 p_attribute14 => p_attribute14,
2721 p_attribute15 => p_attribute15,
2722 p_attribute16 => p_attribute16,
2723 p_attribute17 => p_attribute17,
2724 p_attribute18 => p_attribute18,
2725 p_attribute19 => p_attribute19,
2726 p_attribute20 => p_attribute20,
2727 p_attribute_category => p_attribute_category,
2728 x_return_status => x_return_status,
2729 x_msg_count => x_msg_count,
2730 x_msg_data => x_msg_data);
2731 l_process_type := 'UPDATE';
2732 END IF;
2733 l_inv_location_id := p_location_id;
2734 IF x_return_status in ('E','U') THEN
2735 Rollback to ship_to_address_handler_PUB;
2736 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_STA_API_ERROR');
2737 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
2738 FND_MSG_PUB.ADD;*/
2739 RAISE EXCP_USER_DEFINED;
2740 END IF;
2741
2742 END;
2743 END IF;
2744
2745 -- If p_task_assignment_id is not null or (resource_type and resource_id are not null), it will
2746 -- create account if the engineer doesn't exist in the customer table yet, then create sites.
2747 IF (p_task_assignment_id IS NOT NULL) or
2748 (p_resource_type IS NOT NULL and p_resource_id IS NOT NULL) THEN
2749 BEGIN
2750 IF p_resource_type IS NOT NULL and p_resource_id IS NOT NULL THEN
2751 l_resource_type := p_resource_type;
2752 l_resource_id := p_resource_id;
2753 ELSE
2754 -- Use task_assignment_id to retrieve resource_type, and resource_id
2755 OPEN l_resource_id_csr;
2756 FETCH l_resource_id_csr INTO l_resource_type, l_resource_id;
2757 IF l_resource_id_csr%NOTFOUND THEN
2758 CLOSE l_resource_id_csr;
2759 FND_MESSAGE.SET_NAME ('CSP', 'CSP_RESOURCE_ID_MISSING');
2760 FND_MESSAGE.SET_TOKEN('TSK_ASSGN_ID', p_task_assignment_id, false);
2761 FND_MSG_PUB.ADD;
2762 RAISE EXCP_USER_DEFINED;
2763 END IF;
2764 CLOSE l_resource_id_csr;
2765 END IF;
2766
2767 IF p_task_assignment_id IS NOT NULL THEN
2768 -- IF l_inv_location_id IS NULL THEN
2769 -- Use task_assignment_id to retrieve inventory_location_id from csp_requirement_headers
2770 OPEN l_requirement_hdr_csr;
2771 -- FETCH l_requirement_hdr_csr INTO l_inv_location_id;
2772 FETCH l_requirement_hdr_csr INTO t_inv_location_id, l_timezone_id;
2773 IF l_requirement_hdr_csr%NOTFOUND THEN
2774 CLOSE l_requirement_hdr_csr;
2775 -- FND_MESSAGE.SET_NAME ('CSP', 'CSP_INV_LOC_MISSING');
2776 -- FND_MESSAGE.SET_TOKEN('TSK_ASSGN_ID', p_task_assignment_id, TRUE);
2777 -- FND_MSG_PUB.ADD;
2778 -- RAISE EXCP_USER_DEFINED;
2779 t_inv_location_id := null;
2780 l_timezone_id := null;
2781 ELSE
2782 CLOSE l_requirement_hdr_csr;
2783 END IF;
2784 END IF;
2785
2786 IF l_inv_location_id IS NULL THEN
2787 l_inv_location_id := t_inv_location_id;
2788 END IF;
2789
2790 IF p_timezone IS NOT NULL THEN
2791 l_timezone_id := p_timezone;
2792 END IF;
2793
2794 IF l_inv_location_id IS NULL THEN
2795 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INV_LOC_MISSING');
2796 FND_MESSAGE.SET_TOKEN('TSK_ASSGN_ID', p_task_assignment_id, false);
2797 FND_MSG_PUB.ADD;
2798 RAISE EXCP_USER_DEFINED;
2799 END IF;
2800
2801 -- Get party_id if it exists for this resource
2802 open c_party_id;
2803 fetch c_party_id into l_party_id;
2804 close c_party_id;
2805
2806 -- Retrieve customer_id from CSP_RS_CUST_RELATIONS
2807 -- If found, no need to create new customer account for this resource
2808 OPEN l_customer_id_csr;
2809 FETCH l_customer_id_csr INTO l_rs_cust_relation_id,l_customer_id;
2810 csp_ship_to_address_pvt.g_rs_cust_relation_id := l_rs_cust_relation_id;
2811 IF l_customer_id_csr%FOUND and l_customer_id IS NOT NULL THEN
2812 CLOSE l_customer_id_csr;
2813
2814 -- Use the customer_id to retrieve party_id from hz_cust_accounts
2815 OPEN l_party_id_csr;
2816 FETCH l_party_id_csr INTO l_party_id;
2817 IF l_party_id_csr%NOTFOUND THEN
2818 CLOSE l_party_id_csr;
2819 FND_MESSAGE.SET_NAME ('CSP', 'CSP_PARTY_ID_MISSING');
2820 FND_MESSAGE.SET_TOKEN('RESOURCE_ID', l_resource_id, false);
2821 FND_MESSAGE.SET_TOKEN('CUST_ID', l_customer_id, false);
2822 FND_MSG_PUB.ADD;
2823 RAISE EXCP_USER_DEFINED;
2824 END IF;
2825 CLOSE l_party_id_csr;
2826
2827 ELSE
2828 CLOSE l_customer_id_csr;
2829 l_customer_id := null;
2830
2831 -- prepare to create account
2832 -- retrieve resource name information to create new account
2833 OPEN l_resource_name_csr;
2834 FETCH l_resource_name_csr INTO l_resource_name, l_first_name, l_middle_name, l_last_name;
2835 IF l_resource_name_csr%FOUND AND (l_first_name IS NULL and l_last_name IS NULL) THEN
2836 l_first_name := l_resource_name;
2837 l_last_name := l_resource_name;
2838 END IF;
2839 IF l_resource_name_csr%NOTFOUND THEN
2840 -- CLOSE l_resource_name_csr;
2841 -- FND_MESSAGE.SET_NAME ('CSP', 'CSP_RESOURCE_NAME_MISSING');
2842 -- FND_MESSAGE.SET_TOKEN('TSK_ASSGN_ID', p_task_assignment_id, TRUE);
2843 -- FND_MESSAGE.SET_TOKEN('RESOURCE_CODE', l_resource_type, TRUE);
2844 -- FND_MESSAGE.SET_TOKEN('RESOURCE_ID', l_resource_id, TRUE);
2845 -- FND_MSG_PUB.ADD;
2846 -- RAISE EXCP_USER_DEFINED;
2847 l_resource_name := csp_pick_utils.get_object_name(l_resource_type, l_resource_id);
2848 l_first_name := l_resource_name;
2849 l_last_name := l_resource_name;
2850 END IF;
2851 CLOSE l_resource_name_csr;
2852
2853 -- Create profile
2854 -- hzp_cprof_pkg.create_profile_from_class
2855 csp_customer_account_pvt.create_profile_from_class
2856 (x_customer_profile_class_id => l_customer_profile_class_id,
2857 x_customer_profile_id => l_customer_profile_id,
2858 x_customer_id => l_customer_id,
2859 x_site_use_id => l_site_use_id,
2860 x_collector_id => l_collector_id,
2861 x_collector_name => l_collector_name,
2862 x_credit_checking => l_credit_checking,
2863 x_tolerance => l_tolerance,
2864 x_interest_charges => l_interest_charges,
2865 x_charge_on_fin_charge_flag => l_charge_on_fin_charge_flag,
2866 x_interest_period_days => l_interest_period_days,
2867 x_discount_terms => l_discount_terms,
2868 x_discount_grace_days => l_discount_grace_days,
2869 x_statements => l_send_statements,
2870 x_statement_cycle_id => l_statement_cycle_id,
2871 x_statement_cycle_name => l_statement_cycle_name,
2872 x_credit_balance_statements => l_credit_balance_statements,
2873 x_standard_terms => l_standard_terms,
2874 x_standard_terms_name => l_standard_terms_name,
2875 x_override_terms => l_override_terms,
2876 x_payment_grace_days => l_payment_grace_days,
2877 x_dunning_letters => l_dunning_letters,
2878 x_dunning_letter_set_id => l_dunning_letter_set_id,
2879 x_dunning_letter_set_name => l_dunning_letter_set_name,
2880 x_autocash_hierarchy_id => l_autocash_hierarchy_id,
2881 x_autocash_hierarchy_name => l_autocash_hierarchy_name,
2882 x_auto_rec_incl_disputed_flag => l_auto_rec_incl_disputed_flag,
2883 x_tax_printing_option => l_tax_printing_option,
2884 x_grouping_rule_id => l_grouping_rule_id,
2885 x_grouping_rule_name => l_grouping_rule_name,
2886 x_cons_inv_flag => l_cons_inv_flag,
2887 x_cons_inv_type => l_cons_inv_type,
2888 x_attribute_category => l_attribute_category,
2889 x_attribute1 => l_attribute1,
2890 x_attribute2 => l_attribute2,
2891 x_attribute3 => l_attribute3,
2892 x_attribute4 => l_attribute4,
2893 x_attribute5 => l_attribute5,
2894 x_attribute6 => l_attribute6,
2895 x_attribute7 => l_attribute7,
2896 x_attribute8 => l_attribute8,
2897 x_attribute9 => l_attribute9,
2898 x_attribute10 => l_attribute10,
2899 x_attribute11 => l_attribute11,
2900 x_attribute12 => l_attribute12,
2901 x_attribute13 => l_attribute13,
2902 x_attribute14 => l_attribute14,
2903 x_attribute15 => l_attribute15,
2904 x_jgzz_attribute_category => l_jgzz_attribute_category,
2905 x_jgzz_attribute1 => l_jgzz_attribute1,
2906 x_jgzz_attribute2 => l_jgzz_attribute2,
2907 x_jgzz_attribute3 => l_jgzz_attribute3,
2908 x_jgzz_attribute4 => l_jgzz_attribute4,
2909 x_jgzz_attribute5 => l_jgzz_attribute5,
2910 x_jgzz_attribute6 => l_jgzz_attribute6,
2911 x_jgzz_attribute7 => l_jgzz_attribute7,
2912 x_jgzz_attribute8 => l_jgzz_attribute8,
2913 x_jgzz_attribute9 => l_jgzz_attribute9,
2914 x_jgzz_attribute10 => l_jgzz_attribute10,
2915 x_jgzz_attribute11 => l_jgzz_attribute11,
2916 x_jgzz_attribute12 => l_jgzz_attribute12,
2917 x_jgzz_attribute13 => l_jgzz_attribute13,
2918 x_jgzz_attribute14 => l_jgzz_attribute14,
2919 x_jgzz_attribute15 => l_jgzz_attribute15,
2920 x_global_attribute_category => l_global_attribute_category,
2921 x_global_attribute1 => l_global_attribute1,
2922 x_global_attribute2 => l_global_attribute2,
2923 x_global_attribute3 => l_global_attribute3,
2924 x_global_attribute4 => l_global_attribute4,
2925 x_global_attribute5 => l_global_attribute5,
2926 x_global_attribute6 => l_global_attribute6,
2927 x_global_attribute7 => l_global_attribute7,
2928 x_global_attribute8 => l_global_attribute8,
2929 x_global_attribute9 => l_global_attribute9,
2930 x_global_attribute10 => l_global_attribute10,
2931 x_global_attribute11 => l_global_attribute11,
2932 x_global_attribute12 => l_global_attribute12,
2933 x_global_attribute13 => l_global_attribute13,
2934 x_global_attribute14 => l_global_attribute14,
2935 x_global_attribute15 => l_global_attribute15,
2936 x_global_attribute16 => l_global_attribute16,
2937 x_global_attribute17 => l_global_attribute17,
2938 x_global_attribute18 => l_global_attribute18,
2939 x_global_attribute19 => l_global_attribute19,
2940 x_global_attribute20 => l_global_attribute20,
2941 x_lockbox_matching_option => l_lockbox_matching_option,
2942 x_lockbox_matching_name => l_lockbox_matching_name,
2943 x_autocash_hierarchy_id_adr => l_autocash_hierarchy_id,
2944 x_autocash_hierarchy_name_adr => l_autocash_hierarchy_name_adr,
2945 x_return_status => x_return_status,
2946 x_msg_count => x_msg_count,
2947 x_msg_data => x_msg_data);
2948
2949 IF x_return_status in ('E','U') THEN
2950 Rollback to ship_to_address_handler_PUB;
2951 /* FND_MESSAGE.SET_NAME ('CSP', 'CSP_PROF_API_ERROR');
2952 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
2953 FND_MSG_PUB.ADD;*/
2954 RAISE EXCP_USER_DEFINED;
2955 END IF;
2956
2957 -- if auto generate customer number is no, pass the customer number to the API
2958 IF nvl(arp_standard.sysparm.generate_customer_number,'Y') <> 'N' THEN
2959 l_customer_number := NULL;
2960 ELSE
2961 OPEN l_customer_number_csr;
2962 FETCH l_customer_number_csr into l_customer_number;
2963 CLOSE l_customer_number_csr;
2964 END IF;
2965
2966 -- if the party number generation profile is yes or null, pass the party number to the api
2967 IF fnd_profile.value('HZ_GENERATE_PARTY_NUMBER') <> 'N' THEN
2968 l_party_number := NULL;
2969 ELSE
2970 OPEN l_party_number_csr;
2971 FETCH l_party_number_csr into l_party_number;
2972 CLOSE l_party_number_csr;
2973 END IF;
2974
2975 -- hz_acct_create_pkg.insert_person_row
2976 csp_customer_account_pvt.insert_person_row
2977 (c_cust_account_id => l_customer_id,
2978 c_party_id => l_party_id,
2979 c_account_number => l_customer_number,
2980 c_Attribute_Category => null,
2981 c_Attribute1 => null,
2982 c_Attribute2 => null,
2983 c_Attribute3 => null,
2984 c_Attribute4 => null,
2985 c_Attribute5 => null,
2986 c_Attribute6 => null,
2987 c_Attribute7 => null,
2988 c_Attribute8 => null,
2989 c_Attribute9 => null,
2990 c_Attribute10 => null,
2991 c_Attribute11 => null,
2992 c_Attribute12 => null,
2993 c_Attribute13 => null,
2994 c_Attribute14 => null,
2995 c_Attribute15 => null,
2996 c_Attribute16 => null,
2997 c_Attribute17 => null,
2998 c_Attribute18 => null,
2999 c_Attribute19 => null,
3000 c_Attribute20 => null,
3001 c_global_attribute_category => l_global_attribute_category,
3002 c_global_attribute1 => l_global_attribute1,
3003 c_global_attribute2 => l_global_attribute2,
3004 c_global_attribute3 => l_global_attribute3,
3005 c_global_attribute4 => l_global_attribute4,
3006 c_global_attribute5 => l_global_attribute5,
3007 c_global_attribute6 => l_global_attribute6,
3008 c_global_attribute7 => l_global_attribute7,
3009 c_global_attribute8 => l_global_attribute8,
3010 c_global_attribute9 => l_global_attribute9,
3011 c_global_attribute10 => l_global_attribute10,
3012 c_global_attribute11 => l_global_attribute11,
3013 c_global_attribute12 => l_global_attribute12,
3014 c_global_attribute13 => l_global_attribute13,
3015 c_global_attribute14 => l_global_attribute14,
3016 c_global_attribute15 => l_global_attribute15,
3017 c_global_attribute16 => l_global_attribute16,
3018 c_global_attribute17 => l_global_attribute17,
3019 c_global_attribute18 => l_global_attribute18,
3020 c_global_attribute19 => l_global_attribute19,
3021 c_global_attribute20 => l_global_attribute20,
3022 c_orig_system_reference => l_orig_system_reference,
3023 c_status => l_status,
3024 c_customer_type => l_customer_type,
3025 c_customer_class_code => l_customer_class_code,
3026 c_primary_salesrep_id => l_primary_salesrep_id,
3027 c_sales_channel_code => l_sales_channel_code,
3028 c_order_type_id => l_order_type_id,
3029 c_price_list_id => l_price_list_id,
3030 c_category_code => l_category_code,
3031 c_reference_use_flag => l_reference_use_flag,
3032 c_tax_code => l_tax_code,
3033 c_third_party_flag => l_third_party_flag,
3034 c_competitor_flag => l_competitor_flag,
3035 c_fob_point => l_fob_point,
3036 c_tax_header_level_flag => l_tax_header_level_flag,
3037 c_tax_rounding_rule => l_tax_rounding_rule,
3038 c_account_name => l_account_name,
3039 c_freight_term => l_freight_term,
3040 c_ship_partial => l_ship_partial,
3041 c_ship_via => l_ship_via,
3042 c_warehouse_id => l_warehouse_id,
3043 c_payment_term_id => l_payment_term_id,
3044 c_DATES_NEGATIVE_TOLERANCE => l_DATES_NEGATIVE_TOLERANCE,
3045 c_DATES_POSITIVE_TOLERANCE => l_DATES_POSITIVE_TOLERANCE,
3046 c_DATE_TYPE_PREFERENCE => l_DATE_TYPE_PREFERENCE,
3047 c_OVER_SHIPMENT_TOLERANCE => l_OVER_SHIPMENT_TOLERANCE,
3048 c_UNDER_SHIPMENT_TOLERANCE => l_UNDER_SHIPMENT_TOLERANCE,
3049 c_ITEM_CROSS_REF_PREF => l_ITEM_CROSS_REF_PREF,
3050 c_OVER_RETURN_TOLERANCE => l_OVER_RETURN_TOLERANCE,
3051 c_UNDER_RETURN_TOLERANCE => l_UNDER_RETURN_TOLERANCE,
3052 c_SHIP_SETS_INCLUDE_LINES_FLAG => l_SHIP_SETS_INCLUDE_LINES_FLAG,
3053 c_ARRIVALSETS_INCL_LINES_FLAG => l_ARRIVALSETS_INCL_LINES_FLAG,
3054 c_SCHED_DATE_PUSH_FLAG => l_SCHED_DATE_PUSH_FLAG,
3055 c_INVOICE_QUANTITY_RULE => l_INVOICE_QUANTITY_RULE,
3056 t_party_id => l_party_id,
3057 t_party_number => l_party_number,
3058 t_customer_key => l_customer_key,
3059 t_Attribute_Category => l_Attribute_Category,
3060 t_Attribute1 => l_Attribute1,
3061 t_Attribute2 => l_Attribute2,
3062 t_Attribute3 => l_Attribute3,
3063 t_Attribute4 => l_Attribute4,
3064 t_Attribute5 => l_Attribute5,
3065 t_Attribute6 => l_Attribute6,
3066 t_Attribute7 => l_Attribute7,
3067 t_Attribute8 => l_Attribute8,
3068 t_Attribute9 => l_Attribute9,
3069 t_Attribute10 => l_Attribute10,
3070 t_Attribute11 => l_Attribute11,
3071 t_Attribute12 => l_Attribute12,
3072 t_Attribute13 => l_Attribute13,
3073 t_Attribute14 => l_Attribute14,
3074 t_Attribute15 => l_Attribute15,
3075 t_Attribute16 => l_Attribute16,
3076 t_Attribute17 => l_Attribute17,
3077 t_Attribute18 => l_Attribute18,
3078 t_Attribute19 => l_Attribute19,
3079 t_Attribute20 => l_Attribute20,
3080 t_global_attribute_category => l_global_attribute_category,
3081 t_global_attribute1 => l_global_attribute1,
3082 t_global_attribute2 => l_global_attribute2,
3083 t_global_attribute3 => l_global_attribute3,
3084 t_global_attribute4 => l_global_attribute4,
3085 t_global_attribute5 => l_global_attribute5,
3086 t_global_attribute6 => l_global_attribute6,
3087 t_global_attribute7 => l_global_attribute7,
3088 t_global_attribute8 => l_global_attribute8,
3089 t_global_attribute9 => l_global_attribute9,
3090 t_global_attribute10 => l_global_attribute10,
3091 t_global_attribute11 => l_global_attribute11,
3092 t_global_attribute12 => l_global_attribute12,
3093 t_global_attribute13 => l_global_attribute13,
3094 t_global_attribute14 => l_global_attribute14,
3095 t_global_attribute15 => l_global_attribute15,
3096 t_global_attribute16 => l_global_attribute16,
3097 t_global_attribute17 => l_global_attribute17,
3098 t_global_attribute18 => l_global_attribute18,
3099 t_global_attribute19 => l_global_attribute19,
3100 t_global_attribute20 => l_global_attribute20,
3101 o_pre_name_adjunct => l_pre_name_adjunct,
3102 o_first_name => l_first_name,
3103 o_middle_name => l_middle_name,
3104 o_last_name => l_last_name,
3105 o_name_suffix => l_name_suffix,
3106 o_tax_reference => l_tax_reference,
3107 o_taxpayer_id => l_taxpayer_id,
3108 o_party_name_phonetic => l_party_name_phonetic,
3109 p_cust_account_profile_id => l_customer_profile_id,
3110 p_cust_account_id => l_customer_id,
3111 p_status => l_status,
3112 p_collector_id => l_collector_id,
3113 p_credit_analyst_id => l_credit_analyst_id,
3114 p_credit_checking => l_credit_checking,
3115 p_next_credit_review_date => l_next_credit_review_date,
3116 p_tolerance => l_tolerance,
3117 p_discount_terms => l_discount_terms,
3118 p_dunning_letters => l_dunning_letters,
3119 p_interest_charges => l_interest_charges,
3120 p_send_statements => l_send_statements,
3121 p_credit_balance_statements => l_credit_balance_statements,
3122 p_credit_hold => l_credit_hold,
3123 p_profile_class_id => l_customer_profile_class_id,
3124 p_site_use_id => l_site_use_id,
3125 p_credit_rating => l_credit_rating,
3126 p_risk_code => l_risk_code,
3127 p_standard_terms => l_standard_terms,
3128 p_override_terms => l_override_terms,
3129 p_dunning_letter_set_id => l_dunning_letter_set_id,
3130 p_interest_period_days => l_interest_period_days,
3131 p_payment_grace_days => l_payment_grace_days,
3132 p_discount_grace_days => l_discount_grace_days,
3133 p_statement_cycle_id => l_statement_cycle_id,
3134 p_account_status => l_account_status,
3135 p_percent_collectable => l_percent_collectable,
3136 p_autocash_hierarchy_id => l_autocash_hierarchy_id,
3137 p_Attribute_Category => l_Attribute_Category,
3138 p_Attribute1 => l_Attribute1,
3139 p_Attribute2 => l_Attribute2,
3140 p_Attribute3 => l_Attribute3,
3141 p_Attribute4 => l_Attribute4,
3142 p_Attribute5 => l_Attribute5,
3143 p_Attribute6 => l_Attribute6,
3144 p_Attribute7 => l_Attribute7,
3145 p_Attribute8 => l_Attribute8,
3146 p_Attribute9 => l_Attribute9,
3147 p_Attribute10 => l_Attribute10,
3148 p_Attribute11 => l_Attribute11,
3149 p_Attribute12 => l_Attribute12,
3150 p_Attribute13 => l_Attribute13,
3151 p_Attribute14 => l_Attribute14,
3152 p_Attribute15 => l_Attribute15,
3153 p_auto_rec_incl_disputed_flag => l_auto_rec_incl_disputed_flag,
3154 p_tax_printing_option => l_tax_printing_option,
3155 p_charge_on_fin_charge_flag => l_charge_on_fin_charge_flag,
3156 p_grouping_rule_id => l_grouping_rule_id,
3157 p_clearing_days => l_clearing_days,
3158 p_jgzz_attribute_category => l_jgzz_attribute_category,
3159 p_jgzz_attribute1 => l_jgzz_attribute1,
3160 p_jgzz_attribute2 => l_jgzz_attribute2,
3161 p_jgzz_attribute3 => l_jgzz_attribute3,
3162 p_jgzz_attribute4 => l_jgzz_attribute4,
3163 p_jgzz_attribute5 => l_jgzz_attribute5,
3164 p_jgzz_attribute6 => l_jgzz_attribute6,
3165 p_jgzz_attribute7 => l_jgzz_attribute7,
3166 p_jgzz_attribute8 => l_jgzz_attribute8,
3167 p_jgzz_attribute9 => l_jgzz_attribute9,
3168 p_jgzz_attribute10 => l_jgzz_attribute10,
3169 p_jgzz_attribute11 => l_jgzz_attribute11,
3170 p_jgzz_attribute12 => l_jgzz_attribute12,
3171 p_jgzz_attribute13 => l_jgzz_attribute13,
3172 p_jgzz_attribute14 => l_jgzz_attribute14,
3173 p_jgzz_attribute15 => l_jgzz_attribute15,
3174 p_global_attribute_category => l_global_attribute_category,
3175 p_global_attribute1 => l_global_attribute1,
3176 p_global_attribute2 => l_global_attribute2,
3177 p_global_attribute3 => l_global_attribute3,
3178 p_global_attribute4 => l_global_attribute4,
3179 p_global_attribute5 => l_global_attribute5,
3180 p_global_attribute6 => l_global_attribute6,
3181 p_global_attribute7 => l_global_attribute7,
3182 p_global_attribute8 => l_global_attribute8,
3183 p_global_attribute9 => l_global_attribute9,
3184 p_global_attribute10 => l_global_attribute10,
3185 p_global_attribute11 => l_global_attribute11,
3186 p_global_attribute12 => l_global_attribute12,
3187 p_global_attribute13 => l_global_attribute13,
3188 p_global_attribute14 => l_global_attribute14,
3189 p_global_attribute15 => l_global_attribute15,
3190 p_global_attribute16 => l_global_attribute16,
3191 p_global_attribute17 => l_global_attribute17,
3192 p_global_attribute18 => l_global_attribute18,
3193 p_global_attribute19 => l_global_attribute19,
3194 p_global_attribute20 => l_global_attribute20,
3195 p_cons_inv_flag => l_cons_inv_flag,
3196 p_cons_inv_type => l_cons_inv_type,
3197 p_autocash_hier_id_for_adr => l_autocash_hier_id_for_adr,
3198 p_lockbox_matching_option => l_lockbox_matching_option,
3199 o_person_profile_id => l_person_profile_id,
3200 x_msg_count => x_msg_count,
3201 x_msg_data => x_msg_data,
3202 x_return_status => x_return_status);
3203
3204 IF x_return_status in ('E','U') THEN
3205 Rollback to ship_to_address_handler_PUB;
3206 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_CUST_API_ERROR');
3207 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3208 FND_MSG_PUB.ADD;*/
3209 RAISE EXCP_USER_DEFINED;
3210 ELSE
3211 -- Create/update resource customer relationship in csp_rs_cust_relations.
3212 do_rs_cust_relations(l_resource_type, l_resource_id, l_customer_id);
3213 END IF;
3214 END IF;
3215
3216 -- Select inventory location information
3217 OPEN l_inv_loc_csr;
3218 FETCH l_inv_loc_csr INTO s_address1, s_address2, s_address3, s_city, s_country, s_postal_code, s_county, s_state, s_province, s_attribute_category,
3219 s_attribute1, s_attribute2, s_attribute3, s_attribute4, s_attribute5, s_attribute6, s_attribute7, s_attribute8, s_attribute9, s_attribute10, s_attribute11,
3220 s_attribute12, s_attribute13, s_attribute14, s_attribute15, s_attribute16, s_attribute17, s_attribute18, s_attribute19, s_attribute20, s_global_attribute_category,
3221 s_global_attribute1, s_global_attribute2, s_global_attribute3, s_global_attribute4, s_global_attribute5, s_global_attribute6, s_global_attribute7,
3222 s_global_attribute8, s_global_attribute9, s_global_attribute10, s_global_attribute11, s_global_attribute12, s_global_attribute13, s_global_attribute14,
3223 s_global_attribute15, s_global_attribute16, s_global_attribute17, s_global_attribute18, s_global_attribute19, s_global_attribute20;
3224 IF l_inv_loc_csr%NOTFOUND THEN
3225 CLOSE l_inv_loc_csr;
3226 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INV_LOC_NOT_FOUND');
3227 FND_MESSAGE.SET_TOKEN ('LOC_ID', 'l_inv_location_id',false);
3228 FND_MSG_PUB.ADD;
3229 RAISE EXCP_USER_DEFINED;
3230 END IF;
3231 CLOSE l_inv_loc_csr;
3232 /* As part of bug fix following code is included for canadian address ,
3233 Provice is stored in region_1 not in Region_3 of hr_locations_v*/
3234 l_column_NAME := null;
3235 OPEN province_enabled;
3236 FETCH province_enabled INTO l_column_NAME;
3237 CLOSE province_enabled;
3238 IF l_column_NAME IS NOT NULL AND UPPER(l_column_NAME) = 'REGION_1' THEN
3239 s_province := s_county;
3240 s_county := null;
3241 ELSIF l_column_NAME IS NOT NULL AND UPPER(l_column_NAME) = 'REGION_2' THEN
3242 s_province := s_state;
3243 s_state := null;
3244 END IF;
3245 -- Check if the inventory location has been assoicated to another customer site.
3246 -- If it is associated to the same customer, fine. No need to create new site and site use. Just update it.
3247 -- If it is associated to a different customer, stop with error message.
3248 OPEN l_po_loc_association_csr;
3249 FETCH l_po_loc_association_csr INTO
3250 t_customer_id,
3251 t_cust_acct_site_id,
3252 t_site_use_id,
3253 t_site_loc_id,
3254 t_cust_acct_site_id,
3255 t_primary_flag,
3256 t_status,
3257 t_orig_system_reference,
3258 t_party_id,
3259 t_party_site_id,
3260 t_party_site_number,
3261 t_bill_to_flag,
3262 t_market_flag,
3263 t_ship_to_flag,
3264 t_customer_category,
3265 t_key_account_flag,
3266 t_territory_id,
3267 t_territory,
3268 t_ece_tp_location_code,
3269 t_translated_customer_name,
3270 t_last_update_date,
3271 t_party_site_last_update_date,
3272 t_loc_last_update_date;
3273 IF l_po_loc_association_csr%FOUND THEN
3274 CLOSE l_po_loc_association_csr;
3275 IF t_customer_id <> l_customer_id THEN
3276 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INV_LOC_HAS_ASSIGNED');
3277 FND_MESSAGE.SET_TOKEN ('LOC_ID', 'l_inv_location_id',false);
3278 FND_MESSAGE.SET_TOKEN ('CUSTOMER_ID', 't_customer_id',false);
3279 FND_MSG_PUB.ADD;
3280 RAISE EXCP_USER_DEFINED;
3281 ELSE
3282 IF p_status IS NOT NULL THEN
3283 t_status := p_status;
3284 END IF;
3285
3286 IF p_primary_flag IS NOT NULL THEN
3287 t_primary_flag := p_primary_flag;
3288 END IF;
3289
3290 IF l_timezone_id IS NOT NULL THEN
3291 update hz_locations set time_zone = l_timezone_id where location_id = t_site_loc_id;
3292 END IF;
3293
3294 -- If p_primary_flag is not null or p_status is not null, calling from the
3295 -- Resource Ship To Address and Subinventory Assignments form, update it.
3296 IF p_status = 'I' THEN
3297 IF p_primary_flag IS NOT NULL or p_status IS NOT NULL THEN
3298 do_update_site_use
3299 (p_site_use_id => t_site_use_id
3300 ,p_primary_flag => p_primary_flag
3301 ,p_status => p_status
3302 ,p_customer_id => l_customer_id
3303 ,p_inv_location_id => l_inv_location_id
3304 ,x_return_status => x_return_status
3305 ,x_msg_count => x_msg_count
3306 ,x_msg_data => x_msg_data);
3307 END IF;
3308
3309 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3310 Rollback to ship_to_address_handler_PUB;
3311 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
3312 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3313 FND_MSG_PUB.ADD;*/
3314 RAISE EXCP_USER_DEFINED;
3315 END IF;
3316
3317
3318 -- Update the site location and related tables.
3319 arh_addr_pkg.update_row (
3320 X_Address_Id => t_cust_acct_site_id,
3321 X_Last_Update_Date => t_last_update_date,
3322 X_party_site_Last_Update_Date => t_party_site_last_update_date,
3323 X_loc_Last_Update_Date => t_loc_last_update_date,
3324 X_Last_Updated_By => nvl(fnd_global.user_id, 1),
3325 X_Status => t_status,
3326 X_Orig_System_Reference => t_orig_system_reference,
3327 X_Country => s_country,
3328 X_Address1 => s_address1,
3329 X_Address2 => s_address2,
3330 X_Address3 => s_address3,
3331 X_Address4 => s_address4,
3332 X_City => s_city,
3333 X_Postal_Code => s_postal_code,
3334 X_State => s_state,
3335 X_Province => s_province,
3336 X_County => s_county,
3337 X_Last_Update_Login => nvl(fnd_global.user_id,1),
3338 X_Address_Key => s_address_key,
3339 X_Language => null,--s_language,
3340 X_Attribute_Category => null,
3341 X_Attribute1 => null,
3342 X_Attribute2 => null,
3343 X_Attribute3 => null,
3344 X_Attribute4 => null,
3345 X_Attribute5 => null,
3346 X_Attribute6 => null,
3347 X_Attribute7 => null,
3348 X_Attribute8 => null,
3349 X_Attribute9 => null,
3350 X_Attribute10 => null,
3351 X_Attribute11 => null,
3352 X_Attribute12 => null,
3353 X_Attribute13 => null,
3354 X_Attribute14 => null,
3355 X_Attribute15 => null,
3356 X_Attribute16 => null,
3357 X_Attribute17 => null,
3358 X_Attribute18 => null,
3359 X_Attribute19 => null,
3360 X_Attribute20 => null,
3361 X_Address_warning => s_address_warning,
3362 X_Address_Lines_Phonetic => s_address_lines_phonetic,
3363 X_Global_Attribute_Category => s_global_attribute_category,
3364 X_Global_Attribute1 => s_global_attribute1,
3365 X_Global_Attribute2 => s_global_attribute2,
3366 X_Global_Attribute3 => s_global_attribute3,
3367 X_Global_Attribute4 => s_global_attribute4,
3368 X_Global_Attribute5 => s_global_attribute5,
3369 X_Global_Attribute6 => s_global_attribute6,
3370 X_Global_Attribute7 => s_global_attribute7,
3371 X_Global_Attribute8 => s_global_attribute8,
3372 X_Global_Attribute9 => s_global_attribute9,
3373 X_Global_Attribute10 => s_global_attribute10,
3374 X_Global_Attribute11 => s_global_attribute11,
3375 X_Global_Attribute12 => s_global_attribute12,
3376 X_Global_Attribute13 => s_global_attribute13,
3377 X_Global_Attribute14 => s_global_attribute14,
3378 X_Global_Attribute15 => s_global_attribute15,
3379 X_Global_Attribute16 => s_global_attribute16,
3380 X_Global_Attribute17 => s_global_attribute17,
3381 X_Global_Attribute18 => s_global_attribute18,
3382 X_Global_Attribute19 => s_global_attribute19,
3383 X_Global_Attribute20 => s_global_attribute20,
3384 X_Party_site_id => t_party_site_id,
3385 X_Party_id => t_party_id,
3386 X_Location_id => t_site_loc_id,
3387 X_Party_Site_Number => t_party_site_number,
3388 X_Identifying_address_flag => t_primary_flag,
3389 X_Cust_acct_site_id => t_cust_acct_site_id,
3390 X_Cust_account_id => l_customer_id,
3391 X_su_Bill_To_Flag => t_bill_to_flag,
3392 X_su_Ship_To_Flag => t_ship_to_flag,
3393 X_su_Market_Flag => t_market_flag,
3394 X_su_stmt_flag => s_su_stmt_flag,
3395 X_su_dun_flag => s_su_dun_flag,
3396 X_su_legal_flag => s_su_legal_flag,
3397 X_Customer_Category => t_customer_category,
3398 X_Key_Account_Flag => t_key_account_flag,
3399 X_Territory_id => t_territory_id,
3400 X_ece_tp_location_code => t_ece_tp_location_code,
3401 x_address_mode => s_address_mode,
3402 X_Territory => t_territory,
3403 X_Translated_Customer_Name => t_translated_customer_name,
3404 X_Sales_Tax_Geocode => s_sales_tax_geo_code,
3405 X_Sales_Tax_Inside_City_Limits => s_sale_tax_inside_city_limits,
3406 x_ADDRESSEE => s_addressee,
3407 x_msg_count => x_msg_count,
3408 x_msg_data => x_msg_data,
3409 x_return_status => x_return_status);
3410
3411 IF x_return_status in ('E','U') THEN
3412 Rollback to ship_to_address_handler_PUB;
3413 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_ADDR_API_ERROR');
3414 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3415 FND_MSG_PUB.ADD;*/
3416 RAISE EXCP_USER_DEFINED;
3417 END IF;
3418 ELSE
3419 arh_addr_pkg.update_row (
3420 X_Address_Id => t_cust_acct_site_id,
3421 X_Last_Update_Date => t_last_update_date,
3422 X_party_site_Last_Update_Date => t_party_site_last_update_date,
3423 X_loc_Last_Update_Date => t_loc_last_update_date,
3424 X_Last_Updated_By => nvl(fnd_global.user_id, 1),
3425 X_Status => t_status,
3426 X_Orig_System_Reference => t_orig_system_reference,
3427 X_Country => s_country,
3428 X_Address1 => s_address1,
3429 X_Address2 => s_address2,
3430 X_Address3 => s_address3,
3431 X_Address4 => s_address4,
3432 X_City => s_city,
3433 X_Postal_Code => s_postal_code,
3434 X_State => s_state,
3435 X_Province => s_province,
3436 X_County => s_county,
3437 X_Last_Update_Login => nvl(fnd_global.user_id,1),
3438 X_Address_Key => s_address_key,
3439 X_Language => null,--s_language,
3440 X_Attribute_Category => null,
3441 X_Attribute1 => null,
3442 X_Attribute2 => null,
3443 X_Attribute3 => null,
3444 X_Attribute4 => null,
3445 X_Attribute5 => null,
3446 X_Attribute6 => null,
3447 X_Attribute7 => null,
3448 X_Attribute8 => null,
3449 X_Attribute9 => null,
3450 X_Attribute10 => null,
3451 X_Attribute11 => null,
3452 X_Attribute12 => null,
3453 X_Attribute13 => null,
3454 X_Attribute14 => null,
3455 X_Attribute15 => null,
3456 X_Attribute16 => null,
3457 X_Attribute17 => null,
3458 X_Attribute18 => null,
3459 X_Attribute19 => null,
3460 X_Attribute20 => null,
3461 X_Address_warning => s_address_warning,
3462 X_Address_Lines_Phonetic => s_address_lines_phonetic,
3463 X_Global_Attribute_Category => s_global_attribute_category,
3464 X_Global_Attribute1 => s_global_attribute1,
3465 X_Global_Attribute2 => s_global_attribute2,
3466 X_Global_Attribute3 => s_global_attribute3,
3467 X_Global_Attribute4 => s_global_attribute4,
3468 X_Global_Attribute5 => s_global_attribute5,
3469 X_Global_Attribute6 => s_global_attribute6,
3470 X_Global_Attribute7 => s_global_attribute7,
3471 X_Global_Attribute8 => s_global_attribute8,
3472 X_Global_Attribute9 => s_global_attribute9,
3473 X_Global_Attribute10 => s_global_attribute10,
3474 X_Global_Attribute11 => s_global_attribute11,
3475 X_Global_Attribute12 => s_global_attribute12,
3476 X_Global_Attribute13 => s_global_attribute13,
3477 X_Global_Attribute14 => s_global_attribute14,
3478 X_Global_Attribute15 => s_global_attribute15,
3479 X_Global_Attribute16 => s_global_attribute16,
3480 X_Global_Attribute17 => s_global_attribute17,
3481 X_Global_Attribute18 => s_global_attribute18,
3482 X_Global_Attribute19 => s_global_attribute19,
3483 X_Global_Attribute20 => s_global_attribute20,
3484 X_Party_site_id => t_party_site_id,
3485 X_Party_id => t_party_id,
3486 X_Location_id => t_site_loc_id,
3487 X_Party_Site_Number => t_party_site_number,
3488 X_Identifying_address_flag => t_primary_flag,
3489 X_Cust_acct_site_id => t_cust_acct_site_id,
3490 X_Cust_account_id => l_customer_id,
3491 X_su_Bill_To_Flag => t_bill_to_flag,
3492 X_su_Ship_To_Flag => t_ship_to_flag,
3493 X_su_Market_Flag => t_market_flag,
3494 X_su_stmt_flag => s_su_stmt_flag,
3495 X_su_dun_flag => s_su_dun_flag,
3496 X_su_legal_flag => s_su_legal_flag,
3497 X_Customer_Category => t_customer_category,
3498 X_Key_Account_Flag => t_key_account_flag,
3499 X_Territory_id => t_territory_id,
3500 X_ece_tp_location_code => t_ece_tp_location_code,
3501 x_address_mode => s_address_mode,
3502 X_Territory => t_territory,
3503 X_Translated_Customer_Name => t_translated_customer_name,
3504 X_Sales_Tax_Geocode => s_sales_tax_geo_code,
3505 X_Sales_Tax_Inside_City_Limits => s_sale_tax_inside_city_limits,
3506 x_ADDRESSEE => s_addressee,
3507 x_msg_count => x_msg_count,
3508 x_msg_data => x_msg_data,
3509 x_return_status => x_return_status);
3510
3511 IF x_return_status in ('E','U') THEN
3512 Rollback to ship_to_address_handler_PUB;
3513 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_ADDR_API_ERROR');
3514 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3515 FND_MSG_PUB.ADD;*/
3516 RAISE EXCP_USER_DEFINED;
3517 END IF;
3518 IF p_primary_flag IS NOT NULL or p_status IS NOT NULL THEN
3519 do_update_site_use
3520 (p_site_use_id => t_site_use_id
3521 ,p_primary_flag => p_primary_flag
3522 ,p_status => p_status
3523 ,p_customer_id => l_customer_id
3524 ,p_inv_location_id => l_inv_location_id
3525 ,x_return_status => x_return_status
3526 ,x_msg_count => x_msg_count
3527 ,x_msg_data => x_msg_data);
3528 END IF;
3529
3530 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3531 Rollback to ship_to_address_handler_PUB;
3532 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
3533 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3534 FND_MSG_PUB.ADD;*/
3535 RAISE EXCP_USER_DEFINED;
3536 END IF;
3537
3538 END IF;
3539 END IF;
3540 ELSE
3541 CLOSE l_po_loc_association_csr;
3542
3543 -- If the party site number generation profile is yes or null, pass the party site number to the api
3544 IF fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER') <> 'N' THEN
3545 s_party_site_number := NULL;
3546 ELSE
3547 OPEN l_party_site_number_csr;
3548 FETCH l_party_site_number_csr into s_party_site_number;
3549 CLOSE l_party_site_number_csr;
3550 END IF;
3551
3552 IF p_primary_flag is not null THEN
3553 s_identifying_address_flag := p_primary_flag;
3554 ELSE
3555 -- Check if there is an existing party site with identifying address flag set to 'Y'
3556 -- Set the identifying address flag to 'Y' only if there is no existing one
3557 OPEN l_identify_address_flag_csr;
3558 FETCH l_identify_address_flag_csr INTO s_identifying_address_flag;
3559 IF l_identify_address_flag_csr%NOTFOUND THEN
3560 CLOSE l_identify_address_flag_csr;
3561 s_identifying_address_flag := 'Y';
3562 ELSE
3563 CLOSE l_identify_address_flag_csr;
3564 s_identifying_address_flag := 'N';
3565 END IF;
3566 END IF;
3567
3568 IF p_status is not null THEN
3569 s_status := p_status;
3570 END IF;
3571
3572 -- create sites
3573 arh_addr_pkg.insert_row(
3574 X_Address_Id => s_cust_acct_site_id,
3575 X_Last_Update_Date => sysdate,
3576 X_Last_Updated_By => nvl(fnd_global.user_id,1),
3577 X_Creation_Date => sysdate,
3578 X_Created_By => nvl(fnd_global.user_id,1),
3579 X_Status => s_status,
3580 X_Orig_System_Reference => s_orig_system_reference,
3581 X_Country => s_country,
3582 X_Address1 => s_address1,
3583 X_Address2 => s_address2,
3584 X_Address3 => s_address3,
3585 X_Address4 => s_address4,
3586 X_City => s_city,
3587 X_Postal_Code => s_postal_code,
3588 X_State => s_state,
3589 X_Province => s_province,
3590 X_County => s_county,
3591 X_Last_Update_Login => nvl(fnd_global.user_id,1),
3592 X_Address_Key => s_address_key,
3593 X_Language => null,--s_language,
3594 X_Attribute_Category => null,
3595 X_Attribute1 => null,
3596 X_Attribute2 => null,
3597 X_Attribute3 => null,
3598 X_Attribute4 => null,
3599 X_Attribute5 => null,
3600 X_Attribute6 => null,
3601 X_Attribute7 => null,
3602 X_Attribute8 => null,
3603 X_Attribute9 => null,
3604 X_Attribute10 => null,
3605 X_Attribute11 => null,
3606 X_Attribute12 => null,
3607 X_Attribute13 => null,
3608 X_Attribute14 => null,
3609 X_Attribute15 => null,
3610 X_Attribute16 => null,
3611 X_Attribute17 => null,
3612 X_Attribute18 => null,
3613 X_Attribute19 => null,
3614 X_Attribute20 => null,
3615 X_Address_warning => s_address_warning,
3616 X_Address_Lines_Phonetic => s_Address_Lines_Phonetic,
3617 X_Global_Attribute_Category => s_global_attribute_category,
3618 X_Global_Attribute1 => s_global_attribute1,
3619 X_Global_Attribute2 => s_global_attribute2,
3620 X_Global_Attribute3 => s_global_attribute3,
3621 X_Global_Attribute4 => s_global_attribute4,
3622 X_Global_Attribute5 => s_global_attribute5,
3623 X_Global_Attribute6 => s_global_attribute6,
3624 X_Global_Attribute7 => s_global_attribute7,
3625 X_Global_Attribute8 => s_global_attribute8,
3626 X_Global_Attribute9 => s_global_attribute9,
3627 X_Global_Attribute10 => s_global_attribute10,
3628 X_Global_Attribute11 => s_global_attribute11,
3629 X_Global_Attribute12 => s_global_attribute12,
3630 X_Global_Attribute13 => s_global_attribute13,
3631 X_Global_Attribute14 => s_global_attribute14,
3632 X_Global_Attribute15 => s_global_attribute15,
3633 X_Global_Attribute16 => s_global_attribute16,
3634 X_Global_Attribute17 => s_global_attribute17,
3635 X_Global_Attribute18 => s_global_attribute18,
3636 X_Global_Attribute19 => s_global_attribute19,
3637 X_Global_Attribute20 => s_global_attribute20,
3638 X_Party_site_id => s_party_site_id,
3639 X_Party_id => l_party_id,
3640 X_Location_id => s_location_id,
3641 X_Party_Site_Number => s_party_site_number,
3642 X_Identifying_address_flag => s_identifying_address_flag,
3643 X_Cust_acct_site_id => s_address_id,
3644 X_Cust_account_id => l_customer_id,
3645 X_su_Bill_To_Flag => s_su_bill_to_flag,
3646 X_su_Ship_To_Flag => s_su_ship_to_flag,
3647 X_su_Market_Flag => s_su_market_flag,
3648 X_su_stmt_flag => s_su_stmt_flag,
3649 X_su_dun_flag => s_su_dun_flag,
3650 X_su_legal_flag => s_su_legal_flag,
3651 X_Customer_Category => s_customer_category,
3652 X_Key_Account_Flag => s_key_account_flag,
3653 X_Territory_id => s_territory_id,
3654 X_ece_tp_location_code => s_ece_tp_location_code,
3655 x_address_mode => s_address_mode,
3656 x_territory => s_territory,
3657 x_translated_customer_name => s_translated_customer_name,
3658 x_sales_tax_geo_code => s_sales_tax_geo_code,
3659 x_sale_tax_inside_city_limits => s_sale_tax_inside_city_limits,
3660 x_ADDRESSEE => s_addressee,
3661 x_shared_party_site => s_shared_party_site,
3662 x_update_account_site => s_update_account_site,
3663 x_create_location_party_site => s_create_location_party_site,
3664 x_msg_count => x_msg_count,
3665 x_msg_data => x_msg_data,
3666 x_return_status => x_return_status);
3667
3668 IF x_return_status in ('E','U') THEN
3669 Rollback to ship_to_address_handler_PUB;
3670 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_ADDR_API_ERROR');
3671 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3672 FND_MSG_PUB.ADD;*/
3673 RAISE EXCP_USER_DEFINED;
3674 END IF;
3675
3676 IF l_timezone_id IS NOT NULL THEN
3677 update hz_locations set time_zone = l_timezone_id where location_id = s_location_id;
3678 END IF;
3679
3680 -- Create site use to link to the inventory location
3681 do_create_site_use(
3682 p_customer_id => l_customer_id,
3683 p_party_id => l_party_id,
3684 p_address_id => s_address_id,
3685 p_location_id => s_location_id,
3686 p_inv_location_id => l_inv_location_id,
3687 p_primary_flag => s_identifying_address_flag,
3688 p_status => s_status,
3689 x_return_status => x_return_status,
3690 x_msg_count => x_msg_count,
3691 x_msg_data => x_msg_data);
3692
3693 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3694 Rollback to ship_to_address_handler_PUB;
3695 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_API_ERROR');
3696 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3697 FND_MSG_PUB.ADD;*/
3698 RAISE EXCP_USER_DEFINED;
3699 END IF;
3700 END IF;
3701 END;
3702 END IF;
3703
3704 -- Return location_id and customer_id.
3705 p_location_id := l_inv_location_id;
3706 p_customer_id := l_customer_id;
3707
3708 -- -- Create or update resource/customer relationship record in csp_rs_cust_relations
3709 -- do_rs_cust_relations(l_resource_type, l_resource_id, l_resource_name, l_customer_id);
3710 csp_ship_to_address_pvt.g_inv_loc_id := l_inv_location_id;
3711 IF l_process_type = 'INSERT' THEN
3712 csp_ship_to_address_pvt.call_internal_hook('CSP_SHIP_TO_ADDRESS_PVT','SHIP_TO_ADDRESS_HANDLER','A',x_return_status);
3713 ELSIF l_process_type = 'UPDATE' THEN
3714 csp_ship_to_address_pvt.call_internal_hook('CSP_SHIP_TO_ADDRESS_PVT','UPDATE_LOCATION','A',x_return_status);
3715 END IF;
3716 -- If no error occurs, commit.
3717 IF p_commit = FND_API.G_TRUE THEN
3718 COMMIT;
3719 END IF;
3720
3721 fnd_msg_pub.count_and_get
3722 ( p_count => x_msg_count
3723 , p_data => x_msg_data);
3724
3725 -- Exception Block --
3726 EXCEPTION
3727 WHEN EXCP_USER_DEFINED THEN
3728 Rollback to ship_to_address_handler_PUB;
3729 x_return_status := FND_API.G_RET_STS_ERROR;
3730 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3731 P_API_NAME => L_API_NAME
3732 ,P_PKG_NAME => G_PKG_NAME
3733 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3734 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3735 ,X_MSG_COUNT => X_MSG_COUNT
3736 ,X_MSG_DATA => X_MSG_DATA
3737 ,X_RETURN_STATUS => X_RETURN_STATUS);
3738 WHEN FND_API.G_EXC_ERROR THEN
3739 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3740 P_API_NAME => L_API_NAME
3741 ,P_PKG_NAME => G_PKG_NAME
3742 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3743 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3744 ,X_MSG_COUNT => X_MSG_COUNT
3745 ,X_MSG_DATA => X_MSG_DATA
3746 ,X_RETURN_STATUS => X_RETURN_STATUS);
3747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3748 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3749 P_API_NAME => L_API_NAME
3750 ,P_PKG_NAME => G_PKG_NAME
3751 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3752 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3753 ,X_MSG_COUNT => X_MSG_COUNT
3754 ,X_MSG_DATA => X_MSG_DATA
3755 ,X_RETURN_STATUS => X_RETURN_STATUS);
3756 WHEN OTHERS THEN
3757 Rollback to ship_to_address_handler_PUB;
3758 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
3759 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, false);
3760 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, false);
3761 FND_MSG_PUB.ADD;
3762 fnd_msg_pub.count_and_get
3763 ( p_count => x_msg_count
3764 , p_data => x_msg_data);
3765 x_return_status := FND_API.G_RET_STS_ERROR;
3766
3767 END ship_to_address_handler;
3768
3769
3770 ------------------------------------------------------------------------------------------
3771 -- Procedure Name : site_to_invloc_linkage
3772 -- Purpose : This procedure will create a new inventory location and link the
3773 -- site use id passed in to the new inventory location.
3774 -- Note : Before calling this proceudre, make sure the site_use is not linked to
3775 -- any inventory location yet.
3776 --
3777 /* commented for bug
3778
3779 PROCEDURE site_to_invloc_linkage
3780 (p_customer_id IN NUMBER
3781 ,p_address_id IN NUMBER
3782 ,p_site_use_id IN NUMBER
3783 ,p_location_id OUT NOCOPY NUMBER
3784 ,p_style IN VARCHAR2
3785 ,p_address_line_1 IN VARCHAR2
3786 ,p_address_line_2 IN VARCHAR2
3787 ,p_address_line_3 IN VARCHAR2
3788 ,p_country IN VARCHAR2
3789 ,p_postal_code IN VARCHAR2
3790 ,p_region_1 IN VARCHAR2
3791 ,p_region_2 IN VARCHAR2
3792 ,p_region_3 IN VARCHAR2
3793 ,p_town_or_city IN VARCHAR2
3794 ,p_tax_name IN VARCHAR2
3795 ,p_telephone_number_1 IN VARCHAR2
3796 ,p_telephone_number_2 IN VARCHAR2
3797 ,p_telephone_number_3 IN VARCHAR2
3798 ,p_loc_information13 IN VARCHAR2
3799 ,p_loc_information14 IN VARCHAR2
3800 ,p_loc_information15 IN VARCHAR2
3801 ,p_loc_information16 IN VARCHAR2
3802 ,p_loc_information17 IN VARCHAR2
3803 ,p_loc_information18 IN VARCHAR2
3804 ,p_loc_information19 IN VARCHAR2
3805 ,p_loc_information20 IN VARCHAR2
3806 ,p_api_version_number IN NUMBER
3807 ,p_init_msg_list IN VARCHAR2
3808 ,p_attribute_category IN VARCHAR2
3809 ,p_attribute1 IN VARCHAR2
3810 ,p_attribute2 IN VARCHAR2
3811 ,p_attribute3 IN VARCHAR2
3812 ,p_attribute4 IN VARCHAR2
3813 ,p_attribute5 IN VARCHAR2
3814 ,p_attribute6 IN VARCHAR2
3815 ,p_attribute7 IN VARCHAR2
3816 ,p_attribute8 IN VARCHAR2
3817 ,p_attribute9 IN VARCHAR2
3818 ,p_attribute10 IN VARCHAR2
3819 ,p_attribute11 IN VARCHAR2
3820 ,p_attribute12 IN VARCHAR2
3821 ,p_attribute13 IN VARCHAR2
3822 ,p_attribute14 IN VARCHAR2
3823 ,p_attribute15 IN VARCHAR2
3824 ,p_attribute16 IN VARCHAR2
3825 ,p_attribute17 IN VARCHAR2
3826 ,p_attribute18 IN VARCHAR2
3827 ,p_attribute19 IN VARCHAR2
3828 ,p_attribute20 IN VARCHAR2
3829 ,x_return_status OUT NOCOPY VARCHAR2
3830 ,x_msg_count OUT NOCOPY NUMBER
3831 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3832
3833 l_api_version_number CONSTANT NUMBER := 1.0;
3834 l_api_name CONSTANT VARCHAR2(30) := 'site_to_invloc_linkage';
3835 EXCP_USER_DEFINED EXCEPTION;
3836 l_inv_loc_id hr_locations.location_id%type;
3837 l_object_version_number hr_locations.object_version_number%type;
3838
3839 BEGIN
3840 SAVEPOINT site_to_invloc_linkage_PUB;
3841
3842 -- initialize message list
3843 FND_MSG_PUB.initialize;
3844
3845 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3846 p_api_version_number,
3847 l_api_name,
3848 G_PKG_NAME) THEN
3849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3850 END IF;
3851
3852 x_return_status := FND_API.G_RET_STS_SUCCESS;
3853
3854 -- If site use id is null, return error.
3855 IF p_site_use_id IS NULL THEN
3856 FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_ID_REQD');
3857 FND_MSG_PUB.ADD;
3858 RAISE EXCP_USER_DEFINED;
3859 END IF;
3860
3861 -- If address fields are not null, create a new inventory location. Otherwise, return error.
3862 IF p_country IS NOT NULL THEN
3863 do_create_ship_to_location(
3864 p_location_id => p_location_id,
3865 p_style => p_style,
3866 p_address_line_1 => p_address_line_1,
3867 p_address_line_2 => p_address_line_2,
3868 p_address_line_3 => p_address_line_3,
3869 p_country => p_country,
3870 p_postal_code => p_postal_code,
3871 p_region_1 => p_region_1,
3872 p_region_2 => p_region_2,
3873 p_region_3 => p_region_3,
3874 p_town_or_city => p_town_or_city,
3875 p_tax_name => p_tax_name,
3876 p_telephone_number_1 => p_telephone_number_1,
3877 p_telephone_number_2 => p_telephone_number_2,
3878 p_telephone_number_3 => p_telephone_number_3,
3879 p_loc_information13 => p_loc_information13,
3880 p_loc_information14 => p_loc_information14,
3881 p_loc_information15 => p_loc_information15,
3882 p_loc_information16 => p_loc_information16,
3883 p_loc_information17 => p_loc_information17,
3884 p_loc_information18 => p_loc_information18,
3885 p_loc_information19 => p_loc_information19,
3886 p_loc_information20 => p_loc_information20,
3887 p_object_version_number => l_object_version_number,
3888 p_attribute1 => p_attribute1,
3889 p_attribute2 => p_attribute2,
3890 p_attribute3 => p_attribute3,
3891 p_attribute4 => p_attribute4,
3892 p_attribute5 => p_attribute5,
3893 p_attribute6 => p_attribute6,
3894 p_attribute7 => p_attribute7,
3895 p_attribute8 => p_attribute8,
3896 p_attribute9 => p_attribute9,
3897 p_attribute10 => p_attribute10,
3898 p_attribute11 => p_attribute11,
3899 p_attribute12 => p_attribute12,
3900 p_attribute13 => p_attribute13,
3901 p_attribute14 => p_attribute14,
3902 p_attribute15 => p_attribute15,
3903 p_attribute16 => p_attribute16,
3904 p_attribute17 => p_attribute17,
3905 p_attribute18 => p_attribute18,
3906 p_attribute19 => p_attribute19,
3907 p_attribute20 => p_attribute20,
3908 p_attribute_category => p_attribute_category,
3909 x_return_status => x_return_status,
3910 x_msg_count => x_msg_count,
3911 x_msg_data => x_msg_data);
3912
3913 l_inv_loc_id := p_location_id;
3914 IF x_return_status in ('E','U') THEN
3915 Rollback to site_to_invloc_linkage_PUB;
3916 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_STA_API_ERROR');
3917 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
3918 FND_MSG_PUB.ADD;*/
3919 /* RAISE EXCP_USER_DEFINED;
3920 ELSE
3921
3922 -- Create the linkage between the site use and the inventory location.
3923 arp_clas_pkg.insert_po_loc_associations(
3924 p_inventory_location_id => l_inv_loc_id,
3925 p_inventory_organization_id => null,
3926 p_customer_id => p_customer_id,
3927 p_address_id => p_address_id,
3928 p_site_use_id => p_site_use_id);
3929
3930 END IF;
3931 ELSE
3932 Rollback to site_to_invloc_linkage_PUB;
3933 FND_MESSAGE.SET_NAME ('CSP', 'CSP_ADDRESS_REQD');
3934 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data,false);
3935 FND_MSG_PUB.ADD;
3936 RAISE EXCP_USER_DEFINED;
3937 END IF;
3938
3939 EXCEPTION
3940 WHEN EXCP_USER_DEFINED THEN
3941 x_return_status := FND_API.G_RET_STS_ERROR;
3942 fnd_msg_pub.count_and_get
3943 ( p_count => x_msg_count
3944 , p_data => x_msg_data);
3945 WHEN FND_API.G_EXC_ERROR THEN
3946 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3947 P_API_NAME => L_API_NAME
3948 ,P_PKG_NAME => G_PKG_NAME
3949 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3950 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3951 ,X_MSG_COUNT => x_MSG_COUNT
3952 ,X_MSG_DATA => x_MSG_DATA
3953 ,X_RETURN_STATUS => x_RETURN_STATUS);
3954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3955 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3956 P_API_NAME => L_API_NAME
3957 ,P_PKG_NAME => G_PKG_NAME
3958 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3959 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3960 ,X_MSG_COUNT => x_MSG_COUNT
3961 ,X_MSG_DATA => x_MSG_DATA
3962 ,X_RETURN_STATUS => x_RETURN_STATUS);
3963 WHEN OTHERS THEN
3964 Rollback to site_to_invloc_linkage_PUB;
3965 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
3966 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, false);
3967 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, false);
3968 FND_MSG_PUB.ADD;
3969 fnd_msg_pub.count_and_get
3970 ( p_count => x_msg_count
3971 , p_data => x_msg_data);
3972 x_return_status := FND_API.G_RET_STS_ERROR;
3973
3974 END site_to_invloc_linkage; */
3975 PROCEDURE site_to_invloc_linkage
3976 (p_customer_id IN NUMBER
3977 ,p_address_id IN NUMBER
3978 ,p_site_use_id IN NUMBER
3979 ,p_location_id OUT NOCOPY NUMBER
3980 ,p_style IN VARCHAR2
3981 ,p_address_line_1 IN VARCHAR2
3982 ,p_address_line_2 IN VARCHAR2
3983 ,p_address_line_3 IN VARCHAR2
3984 ,p_country IN VARCHAR2
3985 ,p_postal_code IN VARCHAR2
3986 ,p_region_1 IN VARCHAR2
3987 ,p_region_2 IN VARCHAR2
3988 ,p_region_3 IN VARCHAR2
3989 ,p_town_or_city IN VARCHAR2
3990 ,p_tax_name IN VARCHAR2
3991 ,p_telephone_number_1 IN VARCHAR2
3992 ,p_telephone_number_2 IN VARCHAR2
3993 ,p_telephone_number_3 IN VARCHAR2
3994 ,p_loc_information13 IN VARCHAR2
3995 ,p_loc_information14 IN VARCHAR2
3996 ,p_loc_information15 IN VARCHAR2
3997 ,p_loc_information16 IN VARCHAR2
3998 ,p_loc_information17 IN VARCHAR2
3999 ,p_loc_information18 IN VARCHAR2
4000 ,p_loc_information19 IN VARCHAR2
4001 ,p_loc_information20 IN VARCHAR2
4002 ,p_api_version_number IN NUMBER
4003 ,p_init_msg_list IN VARCHAR2
4004 ,p_attribute_category IN VARCHAR2
4005 ,p_attribute1 IN VARCHAR2
4006 ,p_attribute2 IN VARCHAR2
4007 ,p_attribute3 IN VARCHAR2
4008 ,p_attribute4 IN VARCHAR2
4009 ,p_attribute5 IN VARCHAR2
4010 ,p_attribute6 IN VARCHAR2
4011 ,p_attribute7 IN VARCHAR2
4012 ,p_attribute8 IN VARCHAR2
4013 ,p_attribute9 IN VARCHAR2
4014 ,p_attribute10 IN VARCHAR2
4015 ,p_attribute11 IN VARCHAR2
4016 ,p_attribute12 IN VARCHAR2
4017 ,p_attribute13 IN VARCHAR2
4018 ,p_attribute14 IN VARCHAR2
4019 ,p_attribute15 IN VARCHAR2
4020 ,p_attribute16 IN VARCHAR2
4021 ,p_attribute17 IN VARCHAR2
4022 ,p_attribute18 IN VARCHAR2
4023 ,p_attribute19 IN VARCHAR2
4024 ,p_attribute20 IN VARCHAR2
4025 ,x_return_status OUT NOCOPY VARCHAR2
4026 ,x_msg_count OUT NOCOPY NUMBER
4027 ,x_msg_data OUT NOCOPY VARCHAR2) IS
4028
4029 l_api_version_number CONSTANT NUMBER := 1.0;
4030 l_api_name CONSTANT VARCHAR2(30) := 'site_to_invloc_linkage';
4031 EXCP_USER_DEFINED EXCEPTION;
4032 l_inv_loc_id hr_locations.location_id%type;
4033 l_object_version_number hr_locations.object_version_number%type;
4034 l_town_or_city varchar2(30);
4035 l_region_1 varchar2(120);
4036 cursor get_csp_location is
4037 select STYLE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,COUNTRY
4038 ,POSTAL_CODE,REGION_1,REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,TELEPHONE_NUMBER_3,LOC_INFORMATION13
4039 ,LOC_INFORMATION14,LOC_INFORMATION15,LOC_INFORMATION16,LOC_INFORMATION17,LOC_INFORMATION18,LOC_INFORMATION19
4040 ,LOC_INFORMATION20,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
4041 ,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
4042 ,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,TAX_NAME
4043 from hr_locations
4044 where location_code like 'CSP%'
4045 and country = p_country ;
4046
4047 cursor get_country_location is
4048 select STYLE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,COUNTRY
4049 ,POSTAL_CODE,REGION_1,REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,TELEPHONE_NUMBER_3,LOC_INFORMATION13
4050 ,LOC_INFORMATION14,LOC_INFORMATION15,LOC_INFORMATION16,LOC_INFORMATION17,LOC_INFORMATION18,LOC_INFORMATION19
4051 ,LOC_INFORMATION20,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
4052 ,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
4053 ,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,TAX_NAME
4054 from hr_locations
4055 where country = p_country ;
4056
4057 csp_location_rec get_csp_location%ROWTYPE;
4058 BEGIN
4059 SAVEPOINT site_to_invloc_linkage_PUB;
4060
4061 -- initialize message list
4062 FND_MSG_PUB.initialize;
4063
4064 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4065 p_api_version_number,
4066 l_api_name,
4067 G_PKG_NAME) THEN
4068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4069 END IF;
4070
4071 x_return_status := FND_API.G_RET_STS_SUCCESS;
4072
4073 -- If site use id is null, return error.
4074 IF p_site_use_id IS NULL THEN
4075 FND_MESSAGE.SET_NAME ('CSP', 'CSP_SITE_USE_ID_REQD');
4076 FND_MSG_PUB.ADD;
4077 RAISE EXCP_USER_DEFINED;
4078 END IF;
4079 IF fnd_profile.value('CSP_INTRANSIT_LOCATION_USED') = 'Y' THEN
4080 -- If address fields are not null, create a new inventory location. Otherwise, return error.
4081 IF p_country IS NOT NULL THEN
4082 if p_style= 'US' THEN
4083 l_town_or_city := initcap(p_town_or_city);
4084 l_region_1 :=initcap(p_region_1);
4085 else
4086 l_town_or_city := p_town_or_city;
4087 l_region_1 :=p_region_1;
4088 end if;
4089 do_create_ship_to_location(
4090 p_location_id => p_location_id,
4091 p_style => p_style,
4092 p_address_line_1 => p_address_line_1,
4093 p_address_line_2 => p_address_line_2,
4094 p_address_line_3 => p_address_line_3,
4095 p_country => p_country,
4096 p_postal_code => p_postal_code,
4097 p_region_1 => l_region_1,
4098 p_region_2 => p_region_2,
4099 p_region_3 => p_region_3,
4100 p_town_or_city => l_town_or_city,
4101 p_tax_name => p_tax_name,
4102 p_telephone_number_1 => p_telephone_number_1,
4103 p_telephone_number_2 => p_telephone_number_2,
4104 p_telephone_number_3 => p_telephone_number_3,
4105 p_loc_information13 => p_loc_information13,
4106 p_loc_information14 => p_loc_information14,
4107 p_loc_information15 => p_loc_information15,
4108 p_loc_information16 => p_loc_information16,
4109 p_loc_information17 => p_loc_information17,
4110 p_loc_information18 => p_loc_information18,
4111 p_loc_information19 => p_loc_information19,
4112 p_loc_information20 => p_loc_information20,
4113 p_object_version_number => l_object_version_number,
4114 p_attribute1 => p_attribute1,
4115 p_attribute2 => p_attribute2,
4116 p_attribute3 => p_attribute3,
4117 p_attribute4 => p_attribute4,
4118 p_attribute5 => p_attribute5,
4119 p_attribute6 => p_attribute6,
4120 p_attribute7 => p_attribute7,
4121 p_attribute8 => p_attribute8,
4122 p_attribute9 => p_attribute9,
4123 p_attribute10 => p_attribute10,
4124 p_attribute11 => p_attribute11,
4125 p_attribute12 => p_attribute12,
4126 p_attribute13 => p_attribute13,
4127 p_attribute14 => p_attribute14,
4128 p_attribute15 => p_attribute15,
4129 p_attribute16 => p_attribute16,
4130 p_attribute17 => p_attribute17,
4131 p_attribute18 => p_attribute18,
4132 p_attribute19 => p_attribute19,
4133 p_attribute20 => p_attribute20,
4134 p_attribute_category => p_attribute_category,
4135 x_return_status => x_return_status,
4136 x_msg_count => x_msg_count,
4137 x_msg_data => x_msg_data);
4138
4139 l_inv_loc_id := p_location_id;
4140 IF x_return_status in ('E','U') THEN
4141 Rollback to site_to_invloc_linkage_PUB;
4142 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_STA_API_ERROR');
4143 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
4144 FND_MSG_PUB.ADD;*/
4145 RAISE EXCP_USER_DEFINED;
4146 ELSE
4147
4148 -- Create the linkage between the site use and the inventory location.
4149 arp_clas_pkg.insert_po_loc_associations(
4150 p_inventory_location_id => l_inv_loc_id,
4151 p_inventory_organization_id => null,
4152 p_customer_id => p_customer_id,
4153 p_address_id => p_address_id,
4154 p_site_use_id => p_site_use_id);
4155
4156 END IF;
4157 ELSE
4158 Rollback to site_to_invloc_linkage_PUB;
4159 FND_MESSAGE.SET_NAME ('CSP', 'CSP_ADDRESS_REQD');
4160 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
4161 FND_MSG_PUB.ADD;
4162 RAISE EXCP_USER_DEFINED;
4163 END IF;
4164 ELSE
4165 OPEN get_csp_location;
4166 FETCH get_csp_location INTO csp_location_rec;
4167 CLOSE get_csp_location;
4168 IF csp_location_rec.country IS NULL THEN
4169 OPEN get_country_location;
4170 FETCH get_country_location INTO csp_location_rec;
4171 CLOSE get_country_location;
4172 END IF;
4173 csp_location_rec.ADDRESS_LINE_1 := FND_MESSAGE.GET_STRING('CSP', 'CSP_ADDRESS_DESC');
4174
4175 -- If address fields are not null, create a new inventory location. Otherwise, return error.
4176 IF p_country IS NOT NULL THEN
4177 do_create_ship_to_location(
4178 p_location_id => p_location_id,
4179 p_style => csp_location_rec.style,
4180 p_address_line_1 => csp_location_rec.ADDRESS_LINE_1 ,
4181 p_address_line_2 => csp_location_rec.ADDRESS_LINE_2,
4182 p_address_line_3 => csp_location_rec.ADDRESS_LINE_3,
4183 p_country => csp_location_rec.country,
4184 p_postal_code => csp_location_rec.postal_code,
4185 p_region_1 => csp_location_rec.region_1,
4186 p_region_2 => csp_location_rec.region_2,
4187 p_region_3 => csp_location_rec.region_3,
4188 p_town_or_city => csp_location_rec.town_or_city,
4189 p_tax_name => csp_location_rec.tax_name,
4190 p_telephone_number_1 => csp_location_rec.telephone_number_1,
4191 p_telephone_number_2 => csp_location_rec.telephone_number_2,
4192 p_telephone_number_3 =>csp_location_rec.telephone_number_3,
4193 p_loc_information13 => csp_location_rec.loc_information13,
4194 p_loc_information14 => csp_location_rec.loc_information14,
4195 p_loc_information15 => csp_location_rec.loc_information15,
4196 p_loc_information16 => csp_location_rec.loc_information16,
4197 p_loc_information17 => csp_location_rec.loc_information17,
4198 p_loc_information18 => csp_location_rec.loc_information18,
4199 p_loc_information19 => csp_location_rec.loc_information19,
4200 p_loc_information20 => csp_location_rec.loc_information20,
4201 p_object_version_number => l_object_version_number,
4202 p_attribute1 => csp_location_rec.attribute1,
4203 p_attribute2 => csp_location_rec.attribute2,
4204 p_attribute3 => csp_location_rec.attribute3,
4205 p_attribute4 => csp_location_rec.attribute4,
4206 p_attribute5 => csp_location_rec.attribute5,
4207 p_attribute6 => csp_location_rec.attribute6,
4208 p_attribute7 => csp_location_rec.attribute7,
4209 p_attribute8 => csp_location_rec.attribute8,
4210 p_attribute9 => csp_location_rec.attribute9,
4211 p_attribute10 => csp_location_rec.attribute10,
4212 p_attribute11 => csp_location_rec.attribute11,
4213 p_attribute12 => csp_location_rec.attribute12,
4214 p_attribute13 => csp_location_rec.attribute13,
4215 p_attribute14 => csp_location_rec.attribute14,
4216 p_attribute15 => csp_location_rec.attribute15,
4217 p_attribute16 => csp_location_rec.attribute16,
4218 p_attribute17 => csp_location_rec.attribute17,
4219 p_attribute18 => csp_location_rec.attribute18,
4220 p_attribute19 => csp_location_rec.attribute19,
4221 p_attribute20 => csp_location_rec.attribute20,
4222 p_attribute_category => csp_location_rec.attribute_category,
4223 x_return_status => x_return_status,
4224 x_msg_count => x_msg_count,
4225 x_msg_data => x_msg_data);
4226
4227
4228 l_inv_loc_id := p_location_id;
4229 IF x_return_status in ('E','U') THEN
4230 Rollback to site_to_invloc_linkage_PUB;
4231 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_STA_API_ERROR');
4232 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
4233 FND_MSG_PUB.ADD;*/
4234 RAISE EXCP_USER_DEFINED;
4235 ELSE
4236
4237 -- Create the linkage between the site use and the inventory location.
4238 arp_clas_pkg.insert_po_loc_associations(
4239 p_inventory_location_id => l_inv_loc_id,
4240 p_inventory_organization_id => null,
4241 p_customer_id => p_customer_id,
4242 p_address_id => p_address_id,
4243 p_site_use_id => p_site_use_id);
4244
4245 END IF;
4246 ELSE
4247 Rollback to site_to_invloc_linkage_PUB;
4248 /*FND_MESSAGE.SET_NAME ('CSP', 'CSP_ADDRESS_REQD');
4249 FND_MESSAGE.SET_TOKEN ('TEXT', x_msg_data);
4250 FND_MSG_PUB.ADD;*/
4251 RAISE EXCP_USER_DEFINED;
4252 END IF;
4253 END IF;
4254 EXCEPTION
4255 WHEN EXCP_USER_DEFINED THEN
4256 x_return_status := FND_API.G_RET_STS_ERROR;
4257 fnd_msg_pub.count_and_get
4258 ( p_count => x_msg_count
4259 , p_data => x_msg_data);
4260 WHEN FND_API.G_EXC_ERROR THEN
4261 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4262 P_API_NAME => L_API_NAME
4263 ,P_PKG_NAME => G_PKG_NAME
4264 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4265 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4266 ,X_MSG_COUNT => x_MSG_COUNT
4267 ,X_MSG_DATA => x_MSG_DATA
4268 ,X_RETURN_STATUS => x_RETURN_STATUS);
4269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4270 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4271 P_API_NAME => L_API_NAME
4272 ,P_PKG_NAME => G_PKG_NAME
4273 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4274 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4275 ,X_MSG_COUNT => x_MSG_COUNT
4276 ,X_MSG_DATA => x_MSG_DATA
4277 ,X_RETURN_STATUS => x_RETURN_STATUS);
4278 WHEN OTHERS THEN
4279 Rollback to site_to_invloc_linkage_PUB;
4280 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
4281 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
4282 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
4283 FND_MSG_PUB.ADD;
4284 fnd_msg_pub.count_and_get
4285 ( p_count => x_msg_count
4286 , p_data => x_msg_data);
4287 x_return_status := FND_API.G_RET_STS_ERROR;
4288
4289 END site_to_invloc_linkage;
4290 PROCEDURE call_internal_hook (
4291 p_package_name IN VARCHAR2,
4292 p_api_name IN VARCHAR2,
4293 p_processing_type IN VARCHAR2,
4294 x_return_status OUT NOCOPY VARCHAR2
4295 )
4296 IS
4297 CURSOR c1
4298 IS
4299 SELECT hook_package, hook_api
4300 FROM jtf_hooks_data
4301 WHERE package_name = p_package_name
4302 AND api_name = p_api_name
4303 AND execute_flag = 'Y'
4304 AND processing_type = p_processing_type
4305 ORDER BY execution_order;
4306
4307 v_cursorid INTEGER;
4308 v_blockstr VARCHAR2(2000);
4309 v_dummy INTEGER;
4310 BEGIN
4311 x_return_status := fnd_api.g_ret_sts_success;
4312
4313 FOR i IN c1
4314 LOOP
4315 v_cursorid := DBMS_SQL.open_cursor;
4316 v_blockstr :=
4317 ' begin ' || i.hook_package || '.' || i.hook_api || '(:1); end; ';
4318 DBMS_SQL.parse (v_cursorid, v_blockstr, DBMS_SQL.v7);
4319 DBMS_SQL.bind_variable (v_cursorid, ':1', x_return_status, 20);
4320 v_dummy := DBMS_SQL.execute (v_cursorid);
4321 DBMS_SQL.variable_value (v_cursorid, ':1', x_return_status);
4322 DBMS_SQL.close_cursor (v_cursorid);
4323
4324 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
4325 THEN
4326 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_RETURN_STATUS');
4327 fnd_message.set_token (
4328 'P_PROCEDURE',
4329 i.hook_package || '.' || i.hook_api,false
4330 );
4331 fnd_msg_pub.add;
4332 x_return_status := fnd_api.g_ret_sts_unexp_error;
4333 RAISE fnd_api.g_exc_unexpected_error;
4334 x_return_status := fnd_api.g_ret_sts_unexp_error;
4335 RAISE fnd_api.g_exc_unexpected_error;
4336 END IF;
4337
4338 IF x_return_status IS NULL
4339 THEN
4340 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_RETURN_STATUS');
4341 fnd_msg_pub.add;
4342 x_return_status := fnd_api.g_ret_sts_unexp_error;
4343 RAISE fnd_api.g_exc_unexpected_error;
4344 END IF;
4345 END LOOP;
4346 EXCEPTION
4347 WHEN fnd_api.g_exc_unexpected_error
4348 THEN
4349 x_return_status := fnd_api.g_ret_sts_unexp_error;
4350 WHEN OTHERS
4351 THEN
4352 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
4353 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM,false);
4354 fnd_msg_pub.add;
4355 x_return_status := fnd_api.g_ret_sts_unexp_error;
4356 END;
4357
4358 PROCEDURE cust_inv_loc_link
4359 ( p_api_version IN NUMBER
4360 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
4361 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4362 ,px_location_id IN OUT NOCOPY NUMBER
4363 ,p_party_site_id IN NUMBER
4364 ,p_cust_account_id IN NUMBER
4365 ,p_customer_id IN NUMBER
4366 ,p_attribute_category IN VARCHAR2
4367 ,p_attribute1 IN VARCHAR2
4368 ,p_attribute2 IN VARCHAR2
4369 ,p_attribute3 IN VARCHAR2
4370 ,p_attribute4 IN VARCHAR2
4371 ,p_attribute5 IN VARCHAR2
4372 ,p_attribute6 IN VARCHAR2
4373 ,p_attribute7 IN VARCHAR2
4374 ,p_attribute8 IN VARCHAR2
4375 ,p_attribute9 IN VARCHAR2
4376 ,p_attribute10 IN VARCHAR2
4377 ,p_attribute11 IN VARCHAR2
4378 ,p_attribute12 IN VARCHAR2
4379 ,p_attribute13 IN VARCHAR2
4380 ,p_attribute14 IN VARCHAR2
4381 ,p_attribute15 IN VARCHAR2
4382 ,p_attribute16 IN VARCHAR2
4383 ,p_attribute17 IN VARCHAR2
4384 ,p_attribute18 IN VARCHAR2
4385 ,p_attribute19 IN VARCHAR2
4386 ,p_attribute20 IN VARCHAR2
4387 ,x_return_status OUT NOCOPY VARCHAR2
4388 ,x_msg_count OUT NOCOPY NUMBER
4389 ,x_msg_data OUT NOCOPY VARCHAR2
4390 ) IS
4391 l_location_id NUMBER := px_location_id;
4392 l_return_status VARCHAR2(1);
4393 l_msg_count NUMBER;
4394 l_msg_data VARCHAR2(2000);
4395 l_msg VARCHAR2(2000);
4396 l_site_use_id NUMBER;
4397 l_address_id NUMBER;
4398 l_api_version_number NUMBER := 1.0;
4399 l_api_name CONSTANT VARCHAR2(30) := 'cust_inv_loc_link';
4400 --l_party_site_id NUMBER := to_number(Name_In('csp_requirement_headers.party_site_id'));
4401 --l_account_id NUMBER := to_number(Name_In('csp_requirement_headers.account_id'));
4402 l_org_id NUMBER;
4403 l_inv_location_id NUMBER;
4404 l_inv_loc NUMBER;
4405 l_Return_val BOOLEAN := FALSE;
4406 l_pay_application_id CONSTANT NUMBER := 801;
4407 l_status VARCHAR2(10);
4408 l_industry VARCHAR2(10);
4409 l_state VARCHAR2(30);
4410 l_state_code NUMBER;
4411 l_county VARCHAR2(30);
4412 l_county_code NUMBER;
4413 l_city VARCHAR2(30);
4414 l_city_code NUMBER;
4415 l_state_abbrev VARCHAR2(30);
4416 l_county_name VARCHAR2(240);
4417 l_city_name VARCHAr2(240);
4418
4419 CURSOR site_use_cur IS
4420 SELECT c_site_use.site_use_id, c_acct_site.cust_acct_site_id
4421 FROM hz_cust_site_uses_all c_site_use,
4422 hz_cust_acct_sites_all c_acct_site
4423 WHERE c_site_use.site_use_code = 'SHIP_TO'
4424 AND c_site_use.cust_acct_site_id = c_acct_site.cust_acct_site_id
4425 AND c_acct_site.cust_account_id = p_cust_account_id
4426 AND c_acct_site.party_site_id = p_party_site_id
4427 AND c_acct_site.org_id = l_org_id;
4428
4429 CURSOR invloc_check_cur IS
4430 SELECT location_id
4431 FROM po_location_associations_all
4432 WHERE site_use_id = l_site_use_id;
4433
4434 /* CURSOR po_loc_assoc_exists_cur IS
4435 SELECT location_id
4436 FROM po_location_Associations_all
4437 WHERE location_id = l_inv_location_id;
4438 */
4439
4440 CURSOR address_cur IS
4441 SELECT country, address1, address2, address3,
4442 address4, city, postal_code, state,
4443 province, county
4444 FROM hz_locations
4445 WHERE location_id = px_location_id;
4446
4447 address_rec address_cur%ROWTYPE;
4448
4449 BEGIN
4450 SAVEPOINT cust_inv_loc_link_PUB;
4451
4452 IF (px_location_id IS NOT NULL) THEN
4453
4454 /*OPEN po_loc_Assoc_exists_cur;
4455 FETCH po_loc_Assoc_exists_cur INTO l_inv_loc;
4456
4457 IF (po_loc_assoc_exists_cur%NOTFOUND) THEN
4458 */
4459 IF(p_cust_account_id IS NOT NULL and p_party_site_id IS NOT NULL) THEN
4460 BEGIN
4461 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
4462 SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
4463 INTO l_org_id
4464 FROM DUAL;
4465 END;
4466
4467 OPEN site_use_cur;
4468 FETCH site_use_cur INTO l_site_use_id, l_address_id;
4469 CLOSE site_use_cur;
4470
4471 -- do a check to see if inventory location already exists, execute foll.code
4472 -- only if it does not exist. If inventory_location exists,
4473 -- copy that to ship_to_location_id
4474 OPEN invloc_check_cur;
4475 FETCH invloc_check_cur into l_inv_location_id;
4476 OPEN address_cur;
4477 FETCH address_cur INTO address_rec;
4478 CLOSE address_cur;
4479 IF (invloc_check_cur%NOTFOUND) THEN
4480 IF (address_rec.country like 'US%') THEN
4481 l_state := address_rec.state;
4482 l_county := address_rec.county;
4483 l_city := address_rec.city;
4484 BEGIN
4485 select city_name, county_name, state_abbrev
4486 into l_city_name, l_county_name, l_state_abbrev
4487 from pay_us_city_names ct,
4488 pay_us_states st,
4489 pay_us_counties c
4490 where ct.state_code = st.state_code
4491 and ct.county_code = c.county_code
4492 and c.state_code = st.state_code
4493 and st.state_abbrev = l_state
4494 and upper(c.county_name) = upper(l_county)
4495 and upper(ct.city_name) = upper(l_city);
4496 EXCEPTION
4497 when no_data_found then
4498 null;
4499 END;
4500
4501 END IF;
4502
4503 site_to_invloc_linkage(
4504 p_customer_id => p_customer_id,
4505 p_address_id => l_address_id,
4506 p_site_use_id => l_site_use_id,
4507 p_location_id => l_location_id,
4508 p_style => address_Rec.country,
4509 p_address_line_1 => address_rec.address1,
4510 p_address_line_2 => address_rec.address2,
4511 p_address_line_3 => address_rec.address3,
4512 p_country => address_rec.country,
4513 p_postal_code => address_rec.postal_code,
4514 p_region_1 => nvl(l_county_name, address_rec.county),
4515 p_region_2 => nvl(l_state_Abbrev, address_rec.state),
4516 p_region_3 => null,
4517 p_town_or_city => nvl(l_city_name, address_rec.city),
4518 p_tax_name => null,
4519 p_telephone_number_1 => null,
4520 p_telephone_number_2 => null,
4521 p_telephone_number_3 => null,
4522 p_loc_information13 => null,
4523 p_loc_information14 => null,
4524 p_loc_information15 => null,
4525 p_loc_information16 => null,
4526 p_loc_information17 => null,
4527 p_loc_information18 => null,
4528 p_loc_information19 => null,
4529 p_loc_information20 => null,
4530 p_api_version_number => l_api_version_number,
4531 p_init_msg_list => 'T'
4532 ,P_ATTRIBUTE_CATEGORY => null --Name_In('HR_DESC_FLEX.attribute_category')
4533 ,P_ATTRIBUTE1 => null --Name_In('HR_DESC_FLEX.Attribute1')
4534 ,P_ATTRIBUTE2 => null --Name_In('HR_DESC_FLEX.Attribute2')
4535 ,P_ATTRIBUTE3 => null --Name_In('HR_DESC_FLEX.Attribute3')
4536 ,P_ATTRIBUTE4 => null --Name_In('HR_DESC_FLEX.Attribute4')
4537 ,P_ATTRIBUTE5 => null --Name_In('HR_DESC_FLEX.Attribute5')
4538 ,P_ATTRIBUTE6 => null --Name_In('HR_DESC_FLEX.Attribute6')
4539 ,P_ATTRIBUTE7 => null --Name_In('HR_DESC_FLEX.Attribute7')
4540 ,P_ATTRIBUTE8 => null --Name_In('HR_DESC_FLEX.Attribute8')
4541 ,P_ATTRIBUTE9 => null --Name_In('HR_DESC_FLEX.Attribute9')
4542 ,P_ATTRIBUTE10 => null --Name_In('HR_DESC_FLEX.Attribute10')
4543 ,P_ATTRIBUTE11 => null --Name_In('HR_DESC_FLEX.Attribute11')
4544 ,P_ATTRIBUTE12 => null --('HR_DESC_FLEX.Attribute12')
4545 ,P_ATTRIBUTE13 => null --Name_In('HR_DESC_FLEX.Attribute13')
4546 ,P_ATTRIBUTE14 => null --Name_In('HR_DESC_FLEX.Attribute14')
4547 ,P_ATTRIBUTE15 => null --Name_In('HR_DESC_FLEX.Attribute15')
4548 ,P_ATTRIBUTE16 => null --Name_In('HR_DESC_FLEX.Attribute16')
4549 ,P_ATTRIBUTE17 => null --Name_In('HR_DESC_FLEX.Attribute17')
4550 ,P_ATTRIBUTE18 => null --Name_In('HR_DESC_FLEX.Attribute18')
4551 ,P_ATTRIBUTE19 => null --Name_In('HR_DESC_FLEX.Attribute19')
4552 ,P_ATTRIBUTE20 => null --Name_In('HR_DESC_FLEX.Attribute20')
4553 ,x_return_status => l_return_status
4554 ,X_MSG_COUNT => l_msg_count
4555 ,X_MSG_DATA => l_msg_data
4556 );
4557
4558 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4559 RAISE FND_API.G_EXC_ERROR;
4560 END IF;
4561 px_location_id := l_location_id;
4562 ELSE
4563 px_location_id := l_inv_location_id;
4564 END IF;
4565 ELSE
4566 -- not sure how to get cust_site_use_id if cust acct is null
4567 null;
4568 END IF;
4569 END If;
4570 EXCEPTION
4571 WHEN FND_API.G_EXC_ERROR THEN
4572 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4573 P_API_NAME => L_API_NAME
4574 ,P_PKG_NAME => G_PKG_NAME
4575 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4576 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4577 ,X_MSG_COUNT => X_MSG_COUNT
4578 ,X_MSG_DATA => X_MSG_DATA
4579 ,X_RETURN_STATUS => X_RETURN_STATUS);
4580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4581 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4582 P_API_NAME => L_API_NAME
4583 ,P_PKG_NAME => G_PKG_NAME
4584 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4585 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4586 ,X_MSG_COUNT => X_MSG_COUNT
4587 ,X_MSG_DATA => X_MSG_DATA
4588 ,X_RETURN_STATUS => X_RETURN_STATUS);
4589 WHEN OTHERS THEN
4590 Rollback to cust_inv_loc_link_pub;
4591 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
4592 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, false);
4593 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, false);
4594 FND_MSG_PUB.ADD;
4595 fnd_msg_pub.count_and_get
4596 ( p_count => x_msg_count
4597 , p_data => x_msg_data);
4598 x_return_status := FND_API.G_RET_STS_ERROR;
4599
4600 END;
4601
4602 END CSP_SHIP_TO_ADDRESS_PVT;