[Home] [Help]
PACKAGE BODY: APPS.WSH_MAPPING_DATA
Source
1 PACKAGE BODY WSH_MAPPING_DATA AS
2 /* $Header: WSHMAPDB.pls 120.1 2005/07/11 14:34:23 bsadri noship $ */
3
4 /*===========================================================================
5 | |
6 | PROCEDURE NAME Get_Delivery_Info |
7 | |
8 | DESCRIPTION This procedure gets the Delivery Information at the time |
9 | populating the data into the interface tables, when |
10 | processing an inbound XML transaction. |
11 | |
12 | MODIFICATION HISTORY |
13 | |
14 | 02/18/02 Vijay Nandula Created |
15 | |
16 ============================================================================*/
17
18 --
19 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_MAPPING_DATA';
20 --
21 PROCEDURE get_name_number(
22 p_ship_to_site_contact_id IN NUMBER,
23 x_per_ph_number OUT NOCOPY VARCHAR2,
24 x_contact_person_name OUT NOCOPY VARCHAR2,
25 x_return_status OUT NOCOPY VARCHAR2
26 );
27 PROCEDURE Get_Phone_Fax(
28 p_loc_id IN NUMBER,
29 x_phone OUT NOCOPY VARCHAR2,
30 x_fax OUT NOCOPY VARCHAR2,
31 x_url OUT NOCOPY VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2);
33
34 PROCEDURE Get_Delivery_Info ( p_delivery_id IN NUMBER,
35 p_document_type IN VARCHAR2,
36 x_name OUT NOCOPY VARCHAR2,
37 x_arrival_date OUT NOCOPY DATE,
38 x_departure_date OUT NOCOPY DATE,
39 x_vehicle_num_prefix OUT NOCOPY VARCHAR2,
40 x_vehicle_number OUT NOCOPY VARCHAR2,
41 x_route_id OUT NOCOPY VARCHAR2,
42 x_routing_instructions OUT NOCOPY VARCHAR2,
43 x_departure_seal_code OUT NOCOPY VARCHAR2,
44 x_customer_name OUT NOCOPY VARCHAR2,
45 x_customer_number OUT NOCOPY VARCHAR2,
46 x_warehouse_type OUT NOCOPY VARCHAR2,
47 --Bug 3458160
48 x_operator OUT NOCOPY VARCHAR2,
49 x_ship_to_loc_code OUT NOCOPY VARCHAR2,
50 x_cnsgn_cont_per_name OUT NOCOPY VARCHAR2, --4227777
51 x_cnsgn_cont_per_ph OUT NOCOPY VARCHAR2, --4227777
52 x_return_status OUT NOCOPY VARCHAR2
53 )
54 IS
55
56 CURSOR l_src_hdr_no_cur
57 IS
58 SELECT wdd.source_header_number
59 FROM wsh_delivery_details wdd,
60 wsh_delivery_assignments_v wda
61 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
62 AND wda.delivery_id = p_delivery_id
63 AND wdd.container_flag= 'N'
64 AND rownum = 1;
65
66 CURSOR l_del_info_cur
67 IS
68 SELECT name,
69 customer_id,
70 organization_id,
71 ultimate_dropoff_location_id --bug 3920178
72 FROM wsh_new_deliveries
73 WHERE delivery_id = p_delivery_id;
74
75 CURSOR l_get_dates_cur
76 IS
77 SELECT wts1.Actual_Departure_Date,
78 wts2.Actual_Arrival_Date,
79 wt.Vehicle_Num_Prefix,
80 wt.Vehicle_Number,
81 wt.Route_ID,
82 wt.Routing_Instructions,
83 wts1.Departure_Seal_Code,
84 --Bug 3458160
85 wt.operator
86 FROM wsh_delivery_legs wdl,
87 wsh_trip_stops wts1,
88 wsh_trip_stops wts2,
89 wsh_trips wt
90 WHERE wts1.trip_id = wt.trip_id
91 AND wts2.trip_id = wt.trip_id
92 AND wts1.stop_id = wdl.pick_up_stop_id
93 AND wts2.stop_id = wdl.drop_off_stop_id
94 AND wdl.delivery_id = p_delivery_id;
95
96 CURSOR l_cust_cur( p_customer_id IN NUMBER )
97 IS
98 SELECT hp.party_name,
99 hca.account_number
100 FROM hz_parties hp,
101 hz_cust_accounts hca
102 WHERE hca.party_id = hp.party_id
103 AND hca.cust_account_id = p_customer_id;
104
105 l_org_id NUMBER;
106 l_customer_id NUMBER;
107
108 wsh_invalid_delivery_id EXCEPTION;
109
110 --bug 3920178 {
111 cursor l_ship_to_site_use_id_csr( p_delivery_id IN NUMBER) is
112 SELECT wdd.ship_to_site_use_id ship_to_site_use_id , count(*) cnt
113 FROM wsh_delivery_assignments_v wda,
114 wsh_delivery_details wdd
115 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
116 AND wda.delivery_id = p_delivery_id
117 AND wdd.container_flag = 'N'
118 GROUP BY ship_to_site_use_id
119 ORDER BY cnt DESC;
120
121 cursor l_site_use_loc_csr(p_site_use_id IN NUMBER) is
122 SELECT LOCATION, contact_id
123 FROM HZ_CUST_SITE_USES_ALL
124 WHERE site_use_id = p_site_use_id;
125
126 cursor l_cust_ship_to_loc_csr
127 (p_customer_id IN NUMBER,
128 p_ship_to_location_id IN NUMBER,
129 p_org_id IN NUMBER) is
130 SELECT HCSU.LOCATION, HCSU.CONTACT_ID
131 FROM HZ_CUST_SITE_USES_ALL HCSU,
132 HZ_CUST_ACCT_SITES_ALL HCAS,
133 HZ_CUST_ACCOUNTS HCA,
134 HZ_PARTY_SITES HPS
135 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
136 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
137 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
138 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
139 AND HCSU.STATUS = 'A'
140 AND HCAS.STATUS = 'A'
141 AND HCA.STATUS = 'A'
142 AND HPS.LOCATION_ID = p_ship_to_location_id
143 AND HCAS.CUST_ACCOUNT_ID = p_customer_id
144 AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
145 AND HCAS.ORG_ID = HCSU.ORG_ID ;
146 -- removed the nvl from org_id k proj
147
148 cursor l_rel_cust_ship_to_loc_csr
149 (p_customer_id IN NUMBER,
150 p_ship_to_location_id IN NUMBER,
151 p_org_id IN NUMBER) is
152 SELECT HCSU.LOCATION, HCSU.CONTACT_ID
153 FROM HZ_CUST_SITE_USES_ALL HCSU,
154 HZ_CUST_ACCT_SITES_ALL HCAS,
155 HZ_PARTY_SITES HPS,
156 HZ_CUST_ACCOUNTS HCA,
157 HZ_CUST_ACCT_RELATE_ALL HCAR
158 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
159 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
160 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
161 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
162 AND HCSU.STATUS = 'A'
163 AND HCAS.STATUS = 'A'
164 AND HCA.STATUS = 'A'
165 AND HPS.LOCATION_ID = p_ship_to_location_id
166 AND HCA.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
167 AND HCAR.RELATED_CUST_ACCOUNT_ID = p_customer_id
168 AND HCAR.SHIP_TO_FLAG = 'Y'
169 AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
170 AND HCAS.ORG_ID = HCSU.ORG_ID ;
171 -- removed the nvl from org_id k proj
172
173
174
175 l_operating_unit NUMBER;
176 l_ship_to_site_use_id NUMBER;
177 l_ship_to_location_id NUMBER;
178 l_cnt NUMBER;
179 l_ship_to_loc_code VARCHAR2(32767);
180 l_ship_to_site_contact_id NUMBER;
181 -- bug 3920178}
182
183 l_per_first_name VARCHAR2(150);
184 l_per_middle_name VARCHAR2(60);
185 l_per_last_name VARCHAR2(150);
186 l_contact_person_name VARCHAR2(400);
187 l_owner_table_id NUMBER;
188 l_per_ph_number VARCHAR2(60);
189 l_return_status VARCHAR2(2);
190 --
191 l_debug_on BOOLEAN;
192 --
193 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_INFO';
194 --
195 BEGIN
196 --
197 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
198 --
199 IF l_debug_on IS NULL
200 THEN
201 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
202 END IF;
203 --
204 IF l_debug_on THEN
205 wsh_debug_sv.push(l_module_name);
206 wsh_debug_sv.log (l_module_name, 'delivery id' , p_delivery_id);
207 wsh_debug_sv.log (l_module_name, 'document type' , p_document_type);
208 END IF;
209
210 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
211
212 OPEN l_del_info_cur;
213 FETCH l_del_info_cur INTO x_name, l_customer_id, l_org_id,l_ship_to_location_id;--bug 3920178
214 IF ( l_del_info_cur % NOTFOUND) THEN
215 IF l_debug_on THEN
216 wsh_debug_sv.log (l_module_name, 'Error at cursor l_del_info_cur');
217 END IF;
218
219 CLOSE l_del_info_cur;
220 RAISE wsh_invalid_delivery_id;
221 END IF;
222 CLOSE l_del_info_cur;
223
224 open l_cust_cur(l_customer_id);
225 fetch l_cust_cur into x_customer_name, x_customer_number;
226 close l_cust_cur;
227
228 --bug 3920178 {
229 open l_ship_to_site_use_id_csr(p_delivery_id);
230 fetch l_ship_to_site_use_id_csr into l_ship_to_site_use_id, l_cnt;
231 IF l_ship_to_site_use_id_csr%NOTFOUND THEN
232 l_ship_to_site_use_id := -1;
233 END IF;
234 close l_ship_to_site_use_id_csr;
235
236 IF l_debug_on THEN
237 WSH_DEBUG_SV.log(L_MODULE_NAME, 'l_ship_to_site_use_id' , l_ship_to_site_use_id);
238 END IF;
239 IF nvl(l_ship_to_site_use_id, -1) <> -1 THEN
240 --{
241 open l_site_use_loc_csr(l_ship_to_site_use_id);
242 fetch l_site_use_loc_csr into l_ship_to_loc_code, l_ship_to_site_contact_id;
243 IF l_site_use_loc_csr%NOTFOUND THEN
244 l_ship_to_loc_code := NULL;
245 END IF;
246 close l_site_use_loc_csr;
247 --}
248 END IF;
249
250 IF l_ship_to_loc_code IS NULL THEN
251 --{
252 l_operating_unit := WSH_UTIL_CORE.Get_OperatingUnit_Id(p_delivery_id);
253
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.log(l_module_name, 'l_operating_unit' , l_operating_unit);
256 END IF;
257 open l_cust_ship_to_loc_csr
258 (l_customer_id,
259 l_ship_to_location_id,
260 l_operating_unit);
261 fetch l_cust_ship_to_loc_csr into l_ship_to_loc_code, l_ship_to_site_contact_id;
262 IF l_cust_ship_to_loc_csr%NOTFOUND THEN
263 l_ship_to_loc_code := NULL;
264 END IF;
265 close l_cust_ship_to_loc_csr;
266
267 IF l_ship_to_loc_code IS NULL THEN
268 --{
269 IF l_debug_on THEN
270 WSH_DEBUG_SV.logmsg(L_MODULE_NAME, 'Using Customer Relationship Cursor');
271 END IF;
272
273 open l_rel_cust_ship_to_loc_csr
274 (l_customer_id,
275 l_ship_to_location_id,
276 l_operating_unit);
277 fetch l_rel_cust_ship_to_loc_csr into l_ship_to_loc_code,l_ship_to_site_contact_id;
278 close l_rel_cust_ship_to_loc_csr;
279 --}
280 END IF;
281 --}
282 END IF;
283 IF l_debug_on THEN
284 WSH_DEBUG_SV.log(l_module_name, 'l_ship_to_loc_code', l_ship_to_loc_code);
285 END IF;
286
287 IF l_ship_to_loc_code IS NULL THEN
288 raise fnd_api.g_exc_error;
289 END IF;
290
291 IF (l_ship_to_site_contact_id is not null) THEN
292 --{
293 get_name_number(
294 l_ship_to_site_contact_id,
295 l_per_ph_number,
296 l_contact_person_name,
297 l_return_status
298 );
299 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 x_cnsgn_cont_per_name := l_contact_person_name;
304 x_cnsgn_cont_per_ph := l_per_ph_number;
305 --}
306 END IF;
307
308 IF l_debug_on THEN
309 WSH_DEBUG_SV.log (L_MODULE_NAME, 'Contact Person Name ',
310 x_cnsgn_cont_per_name);
311 WSH_DEBUG_SV.log (L_MODULE_NAME, ' Contact Person Phone Number ',
312 x_cnsgn_cont_per_ph);
313 END IF;
314
315
316 x_ship_to_loc_code := l_ship_to_loc_code;
317 --bug 3920178 }
318
319 x_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
320 (
321 p_organization_id =>l_org_id,
322 x_return_status =>x_return_status
323 );
324 IF l_debug_on THEN
325 wsh_debug_sv.log (l_module_name, 'x_warehouse_type,x_return_status',x_warehouse_type||','||x_return_status);
326 END IF;
327
328 IF ( p_document_type = 'SA' ) THEN
329
330 OPEN l_src_hdr_no_cur;
331 FETCH l_src_hdr_no_cur INTO x_name;
332 IF ( l_src_hdr_no_cur % NOTFOUND) THEN
333 IF l_debug_on THEN
334 wsh_debug_sv.log (l_module_name, 'Error at cursor l_src_hdr_no_cur');
335 END IF;
336 CLOSE l_src_hdr_no_cur;
337 RAISE wsh_invalid_delivery_id;
338 END IF;
339 CLOSE l_src_hdr_no_cur;
340
341 OPEN l_get_dates_cur;
342 FETCH l_get_dates_cur
343 INTO x_arrival_date,
344 x_departure_date,
345 x_vehicle_num_prefix,
346 x_vehicle_number,
347 x_route_id,
348 x_routing_instructions,
349 x_departure_seal_code,
350 --Bug 3458160
351 x_operator;
352 IF ( l_get_dates_cur % NOTFOUND ) THEN
353 IF l_debug_on THEN
354 wsh_debug_sv.log (l_module_name, 'Error at cursor l_get_dates_cur');
355 END IF;
356 CLOSE l_get_dates_cur;
357 RAISE wsh_invalid_delivery_id;
358 END IF;
359 CLOSE l_get_dates_cur;
360 IF l_debug_on THEN
361 wsh_debug_sv.log (l_module_name, 'Arrival Date', x_arrival_date);
362 wsh_debug_sv.log (l_module_name, 'Departure Date', x_departure_date);
363 END IF;
364
365
366 END IF;
367
368 IF l_debug_on THEN
369 wsh_debug_sv.log (l_module_name, 'name' , x_name);
370 wsh_debug_sv.pop (l_module_name);
371 END IF;
372 EXCEPTION
373 --bug 3920178
374 WHEN fnd_api.g_exc_error THEN
375 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
376 IF l_debug_on THEN
377 WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_error has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
378 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
379 END IF;
380 WHEN wsh_invalid_delivery_id THEN
381 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
382 IF l_debug_on THEN
383 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
384 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_id');
385 END IF;
386
387 WHEN OTHERS THEN
388 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
389 IF l_debug_on THEN
390 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
391 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
392 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
393 END IF;
394 END Get_Delivery_Info;
395
396
397 /*
398 This procedure Get_Part_Addr_Info, is called by the outbound map(WSHSSNO.xgm),
399 to populate the elements in the PARTNER segment under SHIPMENT level.
400
401 */
402 PROCEDURE Get_Part_Addr_Info(
403 p_partner_type IN VARCHAR2,
404 p_delivery_id IN NUMBER,
405 x_party_name OUT NOCOPY VARCHAR2,
406 x_partner_location OUT NOCOPY VARCHAR2,
407 x_currency OUT NOCOPY VARCHAR2,
408 x_duns_number OUT NOCOPY VARCHAR2,
409 x_intmed_ship_to_location OUT NOCOPY VARCHAR2,
410 x_pooled_ship_to_location OUT NOCOPY VARCHAR2,
411 x_address1 OUT NOCOPY VARCHAR2,
412 x_address2 OUT NOCOPY VARCHAR2,
413 x_address3 OUT NOCOPY VARCHAR2,
414 x_address4 OUT NOCOPY VARCHAR2,
415 x_city OUT NOCOPY VARCHAR2,
416 x_country OUT NOCOPY VARCHAR2,
417 x_county OUT NOCOPY VARCHAR2,
418 x_postal_code OUT NOCOPY VARCHAR2,
419 x_region OUT NOCOPY VARCHAR2,
420 x_state OUT NOCOPY VARCHAR2,
421 x_fax_number OUT NOCOPY VARCHAR2,
422 x_telephone OUT NOCOPY VARCHAR2,
423 x_url OUT NOCOPY VARCHAR2,
424 x_return_status OUT NOCOPY VARCHAR2) IS
425
426 CURSOR loc_ids_cur IS
427 SELECT organization_id,
428 initial_pickup_location_id,
429 ultimate_dropoff_location_id,
430 intmed_ship_to_location_id,
431 pooled_ship_to_location_id,
432 currency_code
433 FROM wsh_new_deliveries
434 WHERE delivery_id = p_delivery_id;
435
436 /* Patchset I: Locations Project. Select address components from
437 wsh_ship_from_org_locations_v */
438 CURSOR ship_from_info_cur(p_org_id NUMBER, p_loc_id NUMBER) IS
439 SELECT
440 WSFL.ORGANIZATION_NAME PARTY_NAME,
441 HL.LOCATION_CODE PARTNER_LOCATION,
442 0 DUNS_NUMBER,
443 NULL INTMED_SHIP_TO_LOCATION,
444 NULL POOLED_SHIP_TO_LOCATION_ID,
445 WSFL.ADDRESS1 ADDRESS1,
446 WSFL.ADDRESS2 ADDRESS2,
447 WSFL.ADDRESS3 ADDRESS3,
448 NULL ADDRESS4,
449 WSFL.CITY CITY,
450 WSFL.COUNTRY COUNTRY,
451 NULL COUNTY,
452 WSFL.POSTAL_CODE POSTAL_CODE,
453 WSFL.PROVINCE REGION,
454 WSFL.STATE STATE,
455 HL.TELEPHONE_NUMBER_2 FAX_NUMBER,
456 HL.TELEPHONE_NUMBER_1 TELEPHONE,
457 NULL URL
458 FROM
459 wsh_ship_from_org_locations_v WSFL,
460 HR_LOCATIONS_ALL HL
461 WHERE
462 WSFL.wsh_location_id = p_loc_id
463 AND WSFL.source_location_id = HL.location_id;
464
465 /* Patchset I: Locations Project. Selecting from wsh_customer_locations_v */
466 CURSOR ship_to_info_cur(p_loc_id NUMBER, p_opUnit_id NUMBER DEFAULT NULL) IS
467 SELECT
468 DISTINCT wclv.CUSTOMER_NAME PARTY_NAME,
469 wclv.LOCATION PARTNER_LOCATION,
470 NULL CURRENCY,
471 wclv.DUNS_NUMBER DUNS_NUMBER,
472 WCLV.ADDRESS1 ADDRESS1,
473 WCLV.ADDRESS2 ADDRESS2,
474 WCLV.ADDRESS3 ADDRESS3,
475 WCLV.ADDRESS4 ADDRESS4,
476 WCLV.CITY CITY,
477 WCLV.COUNTRY COUNTRY,
478 WCLV.COUNTY COUNTY,
479 WCLV.POSTAL_CODE POSTAL_CODE,
480 WCLV.PROVINCE REGION,
481 WCLV.STATE STATE
482 FROM
483 wsh_customer_locations_v wclv
484 WHERE
485 wclv.wsh_location_id = p_loc_id
486 and wclv.org_id = nvl(p_opUnit_id, wclv.org_id)
487 AND wclv.customer_status = 'A'
488 AND wclv.cust_acct_site_status = 'A'
489 AND wclv.site_use_status = 'A'
490 AND wclv.site_use_code = 'SHIP_TO';
491
492 l_organization_id NUMBER;
493 l_init_loc_id NUMBER;
494 l_ult_loc_id NUMBER;
495 l_intmed_loc_id NUMBER;
496 l_pooled_loc_id NUMBER;
497 l_return_status VARCHAR2(30);
498 l_dummy VARCHAR2(360);
499 --
500 -- Patchset I: Locations Project. kvenkate.
501 l_opUnit_id NUMBER;
502
503 l_debug_on BOOLEAN;
504 --
505 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PART_ADDR_INFO';
506 --
507 BEGIN
508 --
509 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
510 --
511 IF l_debug_on IS NULL
512 THEN
513 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
514 END IF;
515 --
516 IF l_debug_on THEN
517 wsh_debug_sv.push(l_module_name);
518 wsh_debug_sv.log (l_module_name, 'Partner Type', p_partner_type);
519 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
520 END IF;
521
522 OPEN loc_ids_cur;
523 FETCH loc_ids_cur INTO l_organization_id, l_init_loc_id, l_ult_loc_id, l_intmed_loc_id, l_pooled_loc_id, x_currency;
524 CLOSE loc_ids_cur;
525
526 IF l_debug_on THEN
527 wsh_debug_sv.logmsg(l_module_name, 'Calling program unit WSH_UTIL_CORE.GET_OPERATINGUNIT_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
528 END IF;
529
530 /* Patchset I: Locations Project. Get the OperatingUnit Id */
531 l_opUnit_id := wsh_util_core.get_OperatingUnit_id(p_delivery_id);
532
533
534 IF l_debug_on THEN
535 wsh_debug_sv.log(l_module_name, 'Operating Unit Id', l_opUnit_id);
536 wsh_debug_sv.log (l_module_name, 'Initial Pickup Location Id', l_init_loc_id);
537 wsh_debug_sv.log (l_module_name, 'Ultimate Dropoff location Id', l_ult_loc_id);
538 wsh_debug_sv.log (l_module_name, 'Intmed ShipTo Location Id', l_intmed_loc_id);
539 wsh_debug_sv.log (l_module_name, 'Pooled ShipTo Location Id', l_pooled_loc_id);
540 END IF;
541
542 IF(p_partner_type = 'ShipFrom') THEN
543 OPEN ship_from_info_cur(l_organization_id, l_init_loc_id);
544 FETCH ship_from_info_cur INTO
545 x_party_name,
546 x_partner_location,
547 x_duns_number,
548 x_intmed_ship_to_location,
549 x_pooled_ship_to_location,
550 x_address1,
551 x_address2,
552 x_address3,
553 x_address4,
554 x_city,
555 x_country,
556 x_county,
557 x_postal_code,
558 x_region,
559 x_state,
560 x_fax_number,
561 x_telephone,
562 x_url;
563 CLOSE ship_from_info_cur;
564
565 ELSIF(p_partner_type = 'ShipTo') THEN
566 /* Patchset I: Locations Project. Use OperatingUnit Id for ShipTo*/
567 OPEN ship_to_info_cur(l_ult_loc_id, l_opUnit_id);
568 FETCH ship_to_info_cur INTO x_party_name,
569 x_partner_location,
570 x_currency,
571 x_duns_number,
572 x_address1,
573 x_address2,
574 x_address3,
575 x_address4,
576 x_city,
577 x_country,
578 x_county,
579 x_postal_code,
580 x_region,
581 x_state;
582 CLOSE ship_to_info_cur;
583
584 OPEN ship_to_info_cur(l_intmed_loc_id);
585 FETCH ship_to_info_cur INTO l_dummy,
586 x_intmed_ship_to_location,
587 l_dummy,
588 l_dummy,
589 l_dummy,
590 l_dummy,
591 l_dummy,
592 l_dummy,
593 l_dummy,
594 l_dummy,
595 l_dummy,
596 l_dummy,
597 l_dummy,
598 l_dummy;
599 CLOSE ship_to_info_cur;
600
601 OPEN ship_to_info_cur(l_pooled_loc_id);
602 FETCH ship_to_info_cur INTO l_dummy,
603 x_pooled_ship_to_location,
604 l_dummy,
605 l_dummy,
606 l_dummy,
607 l_dummy,
608 l_dummy,
609 l_dummy,
610 l_dummy,
611 l_dummy,
612 l_dummy,
613 l_dummy,
614 l_dummy,
615 l_dummy;
616 CLOSE ship_to_info_cur;
617
618 Get_Phone_Fax(
619 p_loc_id => l_ult_loc_id,
620 x_phone => x_telephone,
621 x_fax => x_fax_number,
622 x_url => x_url,
623 x_return_status => l_return_status);
624
625 IF l_debug_on THEN
626 wsh_debug_sv.log (l_module_name, 'x_telephone,x_fax_number', x_telephone||','||x_fax_number);
627 wsh_debug_sv.log (l_module_name, 'x_url', x_url);
628 wsh_debug_sv.log (l_module_name, 'l_return_status', l_return_status);
629 END IF;
630 END IF;
631
632 IF l_debug_on THEN
633 wsh_debug_sv.pop(l_module_name);
634 END IF;
635 EXCEPTION
636 WHEN OTHERS THEN
637 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
638
639 IF l_debug_on THEN
640 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
641 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
642 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
643 END IF;
644 END get_part_addr_info;
645
646 PROCEDURE Get_Phone_Fax(
647 p_loc_id IN NUMBER,
648 x_phone OUT NOCOPY VARCHAR2,
649 x_fax OUT NOCOPY VARCHAR2,
650 x_url OUT NOCOPY VARCHAR2,
651 x_return_status OUT NOCOPY VARCHAR2) IS
652
653 /* Patchset I: Locations Project. Joining with wsh_locations_hz_v */
654 CURSOR phone_fax_cur(l_line_type VARCHAR2) IS
655 SELECT hcp.raw_phone_number,
656 hcp.url
657 FROM hz_party_sites hps,
658 hz_contact_points hcp,
659 wsh_locations_hz_v wlhz
660 WHERE HCP.CONTACT_POINT_TYPE = 'PHONE'
661 AND HCP.PHONE_LINE_TYPE=l_line_type
662 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
663 AND HPS.PARTY_SITE_ID = HCP.OWNER_TABLE_ID
664 AND wlhz.wsh_location_id = p_loc_id
665 AND wlhz.source_location_id = hps.location_id
666 ORDER BY hcp.primary_flag desc;
667
668 --
669 l_debug_on BOOLEAN;
670 --
671 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PHONE_FAX';
672 --
673 BEGIN
674 --
675 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
676 --
677 IF l_debug_on IS NULL
678 THEN
679 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
680 END IF;
681 --
682 IF l_debug_on THEN
683 wsh_debug_sv.push(l_module_name);
684 wsh_debug_sv.log (l_module_name, 'Loc Id', p_loc_id);
685 END IF;
686
687 OPEN phone_fax_cur('GEN');
688 FETCH phone_fax_cur INTO x_phone, x_url;
689 CLOSE phone_fax_cur;
690
691 OPEN phone_fax_cur('FAX');
692 FETCH phone_fax_cur INTO x_fax, x_url;
693 CLOSE phone_fax_cur;
694
695 IF l_debug_on THEN
696 wsh_debug_sv.pop(l_module_name);
697 END IF;
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
702 IF l_debug_on THEN
703 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
704 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
705 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
706 END IF;
707 END Get_Phone_Fax;
708
709 PROCEDURE get_ship_method_code(
710 p_carrier_name IN VARCHAR2,
711 p_service_level IN VARCHAR2,
712 p_mode_of_transport IN VARCHAR2,
713 p_doc_type IN VARCHAR2, -- bug 3479643
714 p_delivery_name IN VARCHAR2, -- bug 3479643
715 x_ship_method_code OUT NOCOPY VARCHAR2,
716 x_return_status OUT NOCOPY VARCHAR2)
717 IS
718
719 -- Bug fix 2930693
720 -- Added nvl to wcs.service_level and wcs.mode_of_transport because they could be NULL in wsh_carrier_services
721 -- bug 3479643
722 -- Created a separate cursor so that for 945 inbound, we can
723 -- use the delivery's ship method to validate the incoming ship method
724 cursor c_ship_method_cur is
725 select wcs.ship_method_code
726 from wsh_carrier_services wcs,
727 wsh_carriers_v wcar
728 where wcar.carrier_name = p_carrier_name
729 and nvl(wcs.service_level, '!') = nvl(p_service_level, '!')
730 and nvl(wcs.mode_of_transport, '!') = nvl(p_mode_of_transport, '!')
731 and wcs.carrier_id = wcar.carrier_id;
732
733 l_ship_method_code VARCHAR2(32767);
734 l_delivery_id NUMBER;
735 l_organization_id NUMBER;
736 l_org_type VARCHAR2(32767);
737 l_return_status VARCHAR2(1);
738 -- bug 3479643
739
740 wsh_invalid_ship_method EXCEPTION;
741 --
742 l_debug_on BOOLEAN;
743 --
744 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SHIP_METHOD_CODE';
745 --
746
747 BEGIN
748 --
749 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
750 --
751 IF l_debug_on IS NULL
752 THEN
753 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
754 END IF;
755 --
756 IF l_debug_on THEN
757 wsh_debug_sv.push(l_module_name);
758 wsh_debug_sv.log (l_module_name, 'Carrier Name', p_carrier_name);
759 wsh_debug_sv.log (l_module_name, 'Service Level', p_service_level);
760 wsh_debug_sv.log (l_module_name, 'Mode of Transport', p_mode_of_transport);
761 wsh_debug_sv.log (l_module_name, 'Document Type', p_doc_type);
762 wsh_debug_sv.log (l_module_name, 'Delivery Name', p_delivery_name);
763 END IF;
764 -- bug 3857041
765 IF (
766 nvl(p_service_level,'!!!!') <> '!!!!'
767 OR
768 nvl(p_mode_of_transport,'!!!!') <> '!!!!'
769 )
770 THEN
771 --{
772 open c_ship_method_cur;
773 fetch c_ship_method_cur into x_ship_method_code;
774 IF ( c_ship_method_cur%NOTFOUND) THEN
775 IF l_debug_on THEN
776 WSH_DEBUG_SV.logmsg(l_module_name,'Error -- Ship Method Not Found');
777 END IF;
778 END IF;
779 close c_ship_method_cur;
780 --}
781 END IF;
782 -- bug 3857041
783
784 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
785 IF l_debug_on THEN
786 wsh_debug_sv.pop(l_module_name);
787 END IF;
788
789 EXCEPTION
790 WHEN OTHERS THEN
791 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
792 IF l_debug_on THEN
793 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
794 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
795 END IF;
796 END get_ship_method_code;
797
798 -- ---------------------------------------------------------------------
799 -- Procedure: Get_Locn_Cust_Info
800 --
801 -- Parameters:
802 --
803 -- Description: This procedure gets the location, party_name, party_number
804 -- that are required for SHIPITEM and SHIPUNIT during
805 -- 940/945 outbound. This procedure to be called from
806 -- the outbound mapping.
807 -- Created: Locations Project. Patchset I. KVENKATE
808 -- -----------------------------------------------------------------------
809
810 PROCEDURE get_locn_cust_info(
811 p_location_id IN NUMBER,
812 p_org_id IN NUMBER,
813 p_customer_id IN NUMBER,
814 x_location OUT NOCOPY VARCHAR2,
815 x_party_name OUT NOCOPY VARCHAR2,
816 x_party_number OUT NOCOPY VARCHAR2,
817 x_return_status OUT NOCOPY VARCHAR2,
818 p_delivery_detail_id IN NUMBER,
819 p_wsn_rowid IN VARCHAR2,
820 p_requested_quantity IN NUMBER,
821 p_fm_serial_number IN VARCHAR2,
822 p_to_serial_number IN VARCHAR2,
823 x_requested_quantity OUT NOCOPY NUMBER,
824 x_shipped_quantity OUT NOCOPY NUMBER,
825 p_site_use_id IN NUMBER,
826 --bug 4227777
827 p_entity_type IN VARCHAR2,
828 x_cnsgn_cont_per_name OUT NOCOPY VARCHAR2,
829 x_cnsgn_cont_per_ph OUT NOCOPY VARCHAR2
830 )
831
832 IS
833
834 l_org_id NUMBER;
835 l_count NUMBER;
836 CURSOR loc_cust_cur (p_loc_id NUMBER, p_org_id NUMBER, p_cust_id NUMBER) IS
837 SELECT HCSU.LOCATION --bug 3920178 , HP.PARTY_NAME, HP.PARTY_NUMBER
838 FROM
839 HZ_CUST_SITE_USES_ALL HCSU,
840 HZ_CUST_ACCT_SITES_ALL HCAS,
841 HZ_PARTY_SITES HPS,
842 HZ_CUST_ACCOUNTS HCA,
843 HZ_PARTIES HP,
844 WSH_LOCATIONS WL1
845 WHERE
846 WL1.wsh_location_id = p_loc_id AND
847 HCA.CUST_ACCOUNT_ID = p_cust_id AND --bugfix 3842898
848 (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id) AND
849 WL1.SOURCE_LOCATION_ID = HPS.LOCATION_ID AND
850 WL1.LOCATION_SOURCE_CODE = 'HZ' AND
851 HCA.PARTY_ID = HP.PARTY_ID AND
852 HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND
853 HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND
854 HCSU.SITE_USE_CODE = 'SHIP_TO' AND
855 HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID AND
856 HCSU.STATUS = 'A' AND
857 HCAS.STATUS = 'A' AND
858 HCA.STATUS = 'A' AND
859 HCAS.ORG_ID = HCSU.ORG_ID AND
860 -- removed the NVL around the org_id k proj
861 --bug 3920178
862 HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
863 ORDER BY
864 HCSU.SITE_USE_CODE;
865
866 CURSOR org_id_cur(p_del_detail_id NUMBER) IS
867 SELECT wdd.org_id org_id, count(*) cnt
868 FROM wsh_delivery_details wdd
869 WHERE wdd.delivery_detail_id IN
870 (SELECT wda.delivery_detail_id
871 FROM wsh_delivery_assignments_v wda
872 START WITH delivery_detail_id = p_del_detail_id
873 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id )
874 GROUP BY org_id
875 HAVING org_id IS NOT NULL
876 ORDER BY cnt desc;
877
878 CURSOR get_rowid_count(cp_delivery_detail_id NUMBER) IS
879 SELECT rowidtochar(min(rowid)),count(*),sum(quantity)
880 FROM wsh_serial_numbers
881 WHERE delivery_detail_id = cp_delivery_detail_id;
882
883 CURSOR get_wsn_qty(cp_wsn_rowid VARCHAR2) IS
884 SELECT quantity
885 FROM wsh_serial_numbers
886 WHERE rowidtochar(rowid) = cp_wsn_rowid;
887
888 --bug 3920178 {
889 cursor l_site_use_loc_csr(p_site_use_id IN NUMBER) is
890 SELECT LOCATION
891 FROM HZ_CUST_SITE_USES_ALL
892 WHERE site_use_id = p_site_use_id;
893
894 cursor l_rel_cust_ship_to_loc_csr
895 (p_customer_id IN NUMBER,
896 p_ship_to_location_id IN NUMBER,
897 p_org_id IN NUMBER) is
898 SELECT HCSU.LOCATION
899 FROM HZ_CUST_SITE_USES_ALL HCSU,
900 HZ_CUST_ACCT_SITES_ALL HCAS,
901 HZ_PARTY_SITES HPS,
902 HZ_CUST_ACCOUNTS HCA,
903 HZ_CUST_ACCT_RELATE_ALL HCAR
904 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
905 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
906 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
907 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
908 AND HCSU.STATUS = 'A'
909 AND HCAS.STATUS = 'A'
910 AND HCA.STATUS = 'A'
911 AND HPS.LOCATION_ID = p_ship_to_location_id
912 AND HCA.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
913 AND HCAR.RELATED_CUST_ACCOUNT_ID = p_customer_id
914 AND HCAR.SHIP_TO_FLAG = 'Y'
915 AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
916 AND HCAS.ORG_ID = HCSU.ORG_ID ;
917 -- removed the nvl from org_id k proj
918
919 CURSOR c_cust_info_cur(p_customer_id IN NUMBER) IS
920 SELECT HP.PARTY_NAME, HP.PARTY_NUMBER
921 FROM HZ_PARTIES HP,
922 HZ_CUST_ACCOUNTS HCA
923 WHERE HP.PARTY_ID = HCA.PARTY_ID
924 AND HCA.CUST_ACCOUNT_ID = p_customer_id;
925
926 l_deliver_to_site_use_id NUMBER;
927 l_location VARCHAR2(32767);
928
929 --bug 3920178 }
930
931
932 l_wsn_rowid VARCHAR2(100);
933 l_wsn_count NUMBER;
934 l_wsn_qty NUMBER;
935 l_wsn_sum_qty NUMBER;
936
937 --bug 4227777
938
939 CURSOR l_get_lines_in_container_csr IS
940 SELECT wda.delivery_detail_id
941 FROM wsh_delivery_assignments_v wda
942 START WITH wda.parent_delivery_detail_id = p_delivery_detail_id
943 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id;
944
945 CURSOR l_get_ship_to_contact_csr (p_detail_id NUMBER)
946 IS
947 SELECT wdd.ship_to_contact_id
948 FROM wsh_delivery_details wdd
949 WHERE wdd.delivery_detail_id = p_detail_id
950 AND wdd.container_flag = 'N';
951
952
953
954 l_per_first_name VARCHAR2(150);
955 l_per_middle_name VARCHAR2(60);
956 l_per_last_name VARCHAR2(150);
957 l_contact_person_name VARCHAR2(360);
958 l_owner_table_id NUMBER;
959
960 l_per_ph_number VARCHAR2(40);
961 l_per_email_addr VARCHAR2(2000);
962
963 l_uniq_ship_to_contact_id NUMBER;
964 l_curr_ship_to_contact_id NUMBER;
965 l_delivery_detail_tab wsh_util_core.id_tab_type;
966 l_ship_to_contact_id_tab wsh_util_core.id_tab_type;
967 l_return_status VARCHAR2(2);
968
969
970 l_debug_on BOOLEAN;
971 --
972 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOCN_CUST_INFO';
973
974 BEGIN
975 --
976 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
977 --
978 IF l_debug_on IS NULL
979 THEN
980 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
981 END IF;
982 --
983 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
984
985 IF l_debug_on THEN
986 wsh_debug_sv.push(l_module_name);
987 wsh_debug_sv.log (l_module_name, 'Location Id', p_location_id);
988 wsh_debug_sv.log(l_module_name, 'Org Id', p_org_id);
989 wsh_debug_sv.log(l_module_name, 'Customer Id', p_customer_id);
990 wsh_debug_sv.log(l_module_name, 'p_wsn_rowid',p_wsn_rowid);
991 wsh_debug_sv.log(l_module_name, 'p_requested_quantity',p_requested_quantity);
992 wsh_debug_sv.log(l_module_name, 'p_fm_serial_number',p_fm_serial_number);
993 wsh_debug_sv.log(l_module_name, 'p_to_serial_number',p_to_serial_number);
994 wsh_debug_sv.log(l_module_name, 'p_delivery_detail_id',p_delivery_detail_id);
995 wsh_debug_sv.log(l_module_name, 'p_site_use_id',p_site_use_id);
996 wsh_debug_sv.log(l_module_name, 'p_entity_type',p_entity_type);
997 END IF;
998
999
1000
1001 --bug 3920178{
1002 IF p_site_use_id IS NOT NULL THEN
1003 open l_site_use_loc_csr(p_site_use_id);
1004 fetch l_site_use_loc_csr INTO l_location;
1005 close l_site_use_loc_csr;
1006 END IF;
1007 if l_debug_on then
1008 wsh_debug_sv.log(l_module_name, 'l_location',l_location);
1009 end if;
1010
1011 IF l_location IS NULL AND
1012 --bug 3920178}
1013 p_location_id IS NOT NULL THEN
1014 IF p_org_id IS NULL THEN
1015 OPEN org_id_cur(p_delivery_detail_id);
1016 FETCH org_id_cur INTO l_org_id, l_count;
1017 CLOSE org_id_cur;
1018 ELSE
1019 l_org_id := p_org_id;
1020 END IF;
1021
1022 IF l_debug_on THEN
1023 wsh_debug_sv.log (l_module_name, 'l_org_id', l_org_id);
1024 END IF;
1025
1026 OPEN loc_cust_cur(p_location_id, l_org_id, p_customer_id);
1027 FETCH loc_cust_cur INTO l_location; --bug 3920178
1028 IF loc_cust_cur%NOTFOUND THEN
1029 --Bug 3920178{
1030 IF l_debug_on THEN
1031 wsh_debug_sv.logmsg(l_module_name, 'Using l_rel_cust_ship_to_loc_csr');
1032 END IF;
1033 OPEN l_rel_cust_ship_to_loc_csr(p_customer_id, p_location_id, l_org_id);
1034 FETCH l_rel_cust_ship_to_loc_csr INTO l_location;
1035 CLOSE l_rel_cust_ship_to_loc_csr;
1036 --Bug 3920178}
1037 END IF;
1038
1039 CLOSE loc_cust_cur;
1040 END IF;
1041 IF p_customer_id IS NOT NULL THEN
1042 OPEN c_cust_info_cur(p_customer_id);
1043 FETCH c_cust_info_Cur INTO x_party_name, x_party_number;
1044 CLOSE c_cust_info_cur;
1045 END IF;
1046
1047 if l_debug_on then
1048 wsh_debug_sv.log(l_module_name, 'l_location',l_location);
1049 end if;
1050
1051 /* bug 4227777
1052 IF l_location IS NULL THEN
1053 raise fnd_api.g_exc_error;
1054 END IF;
1055 */
1056 x_location := l_location;
1057
1058 IF l_debug_on THEN
1059 wsh_debug_sv.log(l_module_name, 'Location', x_location);
1060 wsh_debug_sv.log(l_module_name, 'Party Name', x_party_name);
1061 wsh_debug_sv.log(l_module_name, 'Party Number', x_party_number);
1062 END IF;
1063
1064
1065
1066 IF p_wsn_rowid IS NOT NULL THEN
1067 OPEN get_wsn_qty(p_wsn_rowid);
1068 FETCH get_wsn_qty INTO l_wsn_qty;
1069 CLOSE get_wsn_qty;
1070 x_shipped_quantity := l_wsn_qty;
1071
1072 OPEN get_rowid_count(p_delivery_detail_id);
1073 FETCH get_rowid_count INTO l_wsn_rowid, l_wsn_count,l_wsn_sum_qty;
1074 CLOSE get_rowid_count;
1075
1076 IF l_debug_on THEN
1077 wsh_debug_sv.log(l_module_name, 'l_wsn_qty',l_wsn_qty );
1078 wsh_debug_sv.log(l_module_name, 'l_wsn_rowid',l_wsn_rowid );
1079 wsh_debug_sv.log(l_module_name, 'l_wsn_count',l_wsn_count );
1080 wsh_debug_sv.log(l_module_name, 'l_wsn_sum_qty',l_wsn_sum_qty );
1081 END IF;
1082
1083 IF l_wsn_sum_qty = p_requested_quantity THEN
1084 x_requested_quantity :=l_wsn_qty;
1085 ELSE
1086
1087 IF p_wsn_rowid = l_wsn_rowid THEN
1088 x_requested_quantity:=trunc(p_requested_quantity/l_wsn_count)+ mod(p_requested_quantity,l_wsn_count);
1089 ELSE
1090 x_requested_quantity:=trunc(p_requested_quantity/l_wsn_count);
1091 END IF;
1092 END IF;
1093
1094 IF l_debug_on THEN
1095 wsh_debug_sv.log(l_module_name, 'x_shipped_quantity', x_shipped_quantity);
1096 wsh_debug_sv.log(l_module_name, 'x_requested_quantity', x_requested_quantity);
1097 END IF;
1098 END IF;
1099 --bug 4227777
1100 l_uniq_ship_to_contact_id := null;
1101
1102 IF p_entity_type = 'CONTAINER'
1103 THEN --{
1104 --cursor to fetch all the lines (item/container) within the given container
1105 OPEN l_get_lines_in_container_csr;
1106 FETCH l_get_lines_in_container_csr BULK COLLECT INTO
1107 l_delivery_detail_tab;
1108 CLOSE l_get_lines_in_container_csr;
1109
1110 IF l_delivery_detail_tab.count > 0 THEN --{
1111 FOR k in 1..l_delivery_detail_tab.count
1112 LOOP --{
1113 l_curr_ship_to_contact_id := null;
1114 OPEN l_get_ship_to_contact_csr (l_delivery_detail_tab(k));
1115 FETCH l_get_ship_to_contact_csr INTO l_curr_ship_to_contact_id;
1116
1117 --for container lines this cursor will not fetch any record
1118 IF l_get_ship_to_contact_csr%FOUND THEN --{
1119 l_uniq_ship_to_contact_id := nvl(l_uniq_ship_to_contact_id,l_curr_ship_to_contact_id);
1120
1121 IF l_curr_ship_to_contact_id IS NOT NULL
1122 AND l_uniq_ship_to_contact_id <> l_curr_ship_to_contact_id
1123 THEN
1124 --need not check remaining lines, since lines have different not-null contact IDs
1125 l_uniq_ship_to_contact_id := null;
1126 CLOSE l_get_ship_to_contact_csr;
1127 EXIT;
1128 END IF;
1129 END IF; --}
1130 CLOSE l_get_ship_to_contact_csr;
1131 END LOOP; --}
1132 END IF; --}
1133 ELSIF p_entity_type = 'LINE' THEN --}{
1134 OPEN l_get_ship_to_contact_csr (p_delivery_detail_id);
1135 FETCH l_get_ship_to_contact_csr INTO l_uniq_ship_to_contact_id;
1136 CLOSE l_get_ship_to_contact_csr;
1137 END IF; --}
1138
1139 IF l_debug_on THEN
1140 wsh_debug_sv.log(l_module_name, 'l_uniq_ship_to_contact_id',
1141 l_uniq_ship_to_contact_id);
1142 END IF;
1143 IF l_uniq_ship_to_contact_id IS NOT NULL THEN --{
1144 get_name_number(
1145 l_uniq_ship_to_contact_id,
1146 l_per_ph_number,
1147 l_contact_person_name,
1148 l_return_status
1149 );
1150 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1151 RAISE FND_API.G_EXC_ERROR;
1152 END IF;
1153
1154 x_cnsgn_cont_per_name := l_contact_person_name;
1155 x_cnsgn_cont_per_ph := l_per_ph_number;
1156
1157 END IF; --}
1158
1159
1160 IF l_debug_on THEN
1161 wsh_debug_sv.log(l_module_name, 'x_cnsgn_cont_per_name',
1162 x_cnsgn_cont_per_name);
1163 wsh_debug_sv.log(l_module_name, 'x_cnsgn_cont_per_ph',
1164 x_cnsgn_cont_per_ph);
1165 wsh_debug_sv.pop(l_module_name);
1166 END IF;
1167
1168 EXCEPTION
1169 WHEN FND_API.G_EXC_ERROR THEN
1170 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1171 wsh_util_core.add_message(x_return_status, l_module_name);
1172 --
1173 IF l_debug_on THEN
1174 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1175 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1176 END IF;
1177 WHEN OTHERS THEN
1178 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1179 IF l_debug_on THEN
1180 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1181 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1182 END IF;
1183
1184 END get_locn_cust_info;
1185
1186 --bug 4227777
1187 PROCEDURE get_name_number(
1188 p_ship_to_site_contact_id IN NUMBER,
1189 x_per_ph_number OUT NOCOPY VARCHAR2,
1190 x_contact_person_name OUT NOCOPY VARCHAR2,
1191 x_return_status OUT NOCOPY VARCHAR2
1192 )
1193 IS
1194 l_debug_on BOOLEAN;
1195 --
1196 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1197 || 'GET_NAME_NUMBER';
1198 l_per_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
1199 l_per_middle_name HZ_PARTIES.PERSON_MIDDLE_NAME%TYPE;
1200 l_per_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
1201 l_owner_table_id NUMBER;
1202
1203 cursor l_ship_to_site_contact_csr(p_contact_id IN NUMBER) is
1204 SELECT PER_CONTACT.PERSON_FIRST_NAME,
1205 PER_CONTACT.PERSON_MIDDLE_NAME,
1206 PER_CONTACT.PERSON_LAST_NAME,
1207 PHONE_CONTACT.RAW_PHONE_NUMBER,
1208 HREL.PARTY_ID
1209 from HZ_CUST_ACCOUNT_ROLES HCAR,
1210 HZ_RELATIONSHIPS HREL,
1211 HZ_ORG_CONTACTS HOC,
1212 HZ_CONTACT_POINTS PHONE_CONTACT,
1213 HZ_PARTIES PER_CONTACT
1214 WHERE HCAR.CUST_ACCOUNT_ROLE_ID = p_contact_id
1215 AND HREL.PARTY_ID = HCAR.PARTY_ID
1216 AND HCAR.ROLE_TYPE = 'CONTACT'
1217 AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
1218 AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1219 AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1220 AND HREL.SUBJECT_TYPE = 'PERSON'
1221 AND HREL.DIRECTIONAL_FLAG = 'F'
1222 AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
1223 AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
1224 AND PHONE_CONTACT.OWNER_TABLE_ID(+) = HREL.PARTY_ID
1225 AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
1226 AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
1227 AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y';
1228
1229 cursor l_ship_to_site_ph_csr(p_owner_tbl_id IN NUMBER) is
1230 SELECT RAW_PHONE_NUMBER
1231 FROM HZ_CONTACT_POINTS
1232 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
1233 AND OWNER_TABLE_ID = p_owner_tbl_id
1234 AND CONTACT_POINT_TYPE = 'PHONE'
1235 AND PHONE_LINE_TYPE = 'GEN';
1236
1237 BEGIN
1238 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1239 --
1240 IF l_debug_on IS NULL
1241 THEN
1242 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1243 END IF;
1244 --
1245 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1246 IF l_debug_on THEN
1247 wsh_debug_sv.push(l_module_name);
1248 wsh_debug_sv.log (l_module_name, 'p_ship_to_site_contact_id', p_ship_to_site_contact_id);
1249 END IF;
1250
1251 open l_ship_to_site_contact_csr(p_ship_to_site_contact_id);
1252 fetch l_ship_to_site_contact_csr into l_per_first_name,
1253 l_per_middle_name,
1254 l_per_last_name,
1255 x_per_ph_number,
1256 l_owner_table_id;
1257 close l_ship_to_site_contact_csr;
1258
1259 IF l_per_first_name IS NOT NULL THEN
1260 --{
1261 x_contact_person_name := l_per_first_name || ' ';
1262 --}
1263 END IF;
1264
1265 IF l_per_middle_name IS NOT NULL THEN
1266 --{
1267 x_contact_person_name := x_contact_person_name || l_per_middle_name || ' ';
1268 --}
1269 END IF;
1270
1271 IF l_per_last_name IS NOT NULL THEN
1272 --{
1273 x_contact_person_name := x_contact_person_name || l_per_last_name; --}
1274 END IF;
1275 IF l_debug_on THEN
1276 WSH_DEBUG_SV.log (L_MODULE_NAME,' Contact Person Phone Number ',
1277 x_per_ph_number);
1278 WSH_DEBUG_SV.log (L_MODULE_NAME,' l_owner_table_id ',
1279 l_owner_table_id);
1280 END IF;
1281
1282 IF x_per_ph_number IS NULL
1283 AND l_owner_table_id IS NOT NULL
1284 THEN
1285 --{
1286 open l_ship_to_site_ph_csr(l_owner_table_id);
1287 fetch l_ship_to_site_ph_csr into x_per_ph_number;
1288 close l_ship_to_site_ph_csr;
1289 --}
1290 END IF;
1291
1292
1293 IF l_debug_on THEN
1294 wsh_debug_sv.log (l_module_name, 'x_per_ph_number', x_per_ph_number);
1295 wsh_debug_sv.log (l_module_name, 'x_contact_person_name',
1296 x_contact_person_name);
1297 wsh_debug_sv.log (l_module_name, 'x_return_status', x_return_status);
1298 wsh_debug_sv.pop(l_module_name);
1299 END IF;
1300
1301 EXCEPTION
1302
1303 WHEN OTHERS THEN
1304 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1305 IF l_debug_on THEN
1306 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1307 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1308 END IF;
1309
1310 END get_name_number;
1311
1312 END WSH_MAPPING_DATA;