[Home] [Help]
PACKAGE BODY: APPS.WSH_MAPPING_DATA
Source
1 PACKAGE BODY WSH_MAPPING_DATA AS
2 /* $Header: WSHMAPDB.pls 120.1.12010000.4 2010/02/25 16:19:16 sankarun ship $ */
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
22 PROCEDURE get_name_number(
23 p_ship_to_site_contact_id IN NUMBER,
24 x_per_ph_number OUT NOCOPY VARCHAR2,
25 x_contact_person_name OUT NOCOPY VARCHAR2,
26 x_return_status OUT NOCOPY VARCHAR2
27 );
28 PROCEDURE Get_Phone_Fax(
29 p_loc_id IN NUMBER,
30 x_phone OUT NOCOPY VARCHAR2,
31 x_fax OUT NOCOPY VARCHAR2,
32 x_url OUT NOCOPY VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2);
34
35 PROCEDURE Get_Delivery_Info ( p_delivery_id IN NUMBER,
36 p_document_type IN VARCHAR2,
37 x_name OUT NOCOPY VARCHAR2,
38 x_arrival_date OUT NOCOPY DATE,
39 x_departure_date OUT NOCOPY DATE,
40 x_vehicle_num_prefix OUT NOCOPY VARCHAR2,
41 x_vehicle_number OUT NOCOPY VARCHAR2,
42 x_route_id OUT NOCOPY VARCHAR2,
43 x_routing_instructions OUT NOCOPY VARCHAR2,
44 x_departure_seal_code OUT NOCOPY VARCHAR2,
45 x_customer_name OUT NOCOPY VARCHAR2,
46 x_customer_number OUT NOCOPY VARCHAR2,
47 x_warehouse_type OUT NOCOPY VARCHAR2,
48 --Bug 3458160
49 x_operator OUT NOCOPY VARCHAR2,
50 x_ship_to_loc_code OUT NOCOPY VARCHAR2,
51 x_cnsgn_cont_per_name OUT NOCOPY VARCHAR2, --4227777
52 x_cnsgn_cont_per_ph OUT NOCOPY VARCHAR2, --4227777
53 x_return_status OUT NOCOPY VARCHAR2
54 )
55 IS
56
57 CURSOR l_src_hdr_no_cur
58 IS
59 SELECT wdd.source_header_number
60 FROM wsh_delivery_details wdd,
61 wsh_delivery_assignments_v wda
62 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
63 AND wda.delivery_id = p_delivery_id
64 AND wdd.container_flag= 'N'
65 AND rownum = 1;
66
67 CURSOR l_del_info_cur
68 IS
69 SELECT name,
70 customer_id,
71 organization_id,
72 ultimate_dropoff_location_id --bug 3920178
73 FROM wsh_new_deliveries
74 WHERE delivery_id = p_delivery_id;
75
76 CURSOR l_get_dates_cur
77 IS
78 SELECT wts1.Actual_Departure_Date,
79 wts2.Actual_Arrival_Date,
80 wt.Vehicle_Num_Prefix,
81 wt.Vehicle_Number,
82 wt.Route_ID,
83 wt.Routing_Instructions,
84 wts1.Departure_Seal_Code,
85 --Bug 3458160
86 wt.operator
87 FROM wsh_delivery_legs wdl,
88 wsh_trip_stops wts1,
89 wsh_trip_stops wts2,
90 wsh_trips wt
91 WHERE wts1.trip_id = wt.trip_id
92 AND wts2.trip_id = wt.trip_id
93 AND wts1.stop_id = wdl.pick_up_stop_id
94 AND wts2.stop_id = wdl.drop_off_stop_id
95 AND wdl.delivery_id = p_delivery_id;
96
97 CURSOR l_cust_cur( p_customer_id IN NUMBER )
98 IS
99 SELECT hp.party_name,
100 hca.account_number
101 FROM hz_parties hp,
102 hz_cust_accounts hca
103 WHERE hca.party_id = hp.party_id
104 AND hca.cust_account_id = p_customer_id;
105
106 l_org_id NUMBER;
107 l_customer_id NUMBER;
108
109 wsh_invalid_delivery_id EXCEPTION;
110
111 --bug 3920178 {
112 cursor l_ship_to_site_use_id_csr( p_delivery_id IN NUMBER) is
113 SELECT wdd.ship_to_site_use_id ship_to_site_use_id , count(*) cnt
114 FROM wsh_delivery_assignments_v wda,
115 wsh_delivery_details wdd
116 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
117 AND wda.delivery_id = p_delivery_id
118 AND wdd.container_flag = 'N'
119 GROUP BY ship_to_site_use_id
120 ORDER BY cnt DESC;
121
122 cursor l_site_use_loc_csr(p_site_use_id IN NUMBER) is
123 SELECT LOCATION, contact_id
124 FROM HZ_CUST_SITE_USES_ALL
125 WHERE site_use_id = p_site_use_id;
126
127 cursor l_cust_ship_to_loc_csr
128 (p_customer_id IN NUMBER,
129 p_ship_to_location_id IN NUMBER,
130 p_org_id IN NUMBER) is
131 SELECT HCSU.LOCATION, HCSU.CONTACT_ID
132 FROM HZ_CUST_SITE_USES_ALL HCSU,
133 HZ_CUST_ACCT_SITES_ALL HCAS,
134 HZ_CUST_ACCOUNTS HCA,
135 HZ_PARTY_SITES HPS
136 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
137 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
138 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
139 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
140 AND HCSU.STATUS = 'A'
141 AND HCAS.STATUS = 'A'
142 AND HCA.STATUS = 'A'
143 AND HPS.LOCATION_ID = p_ship_to_location_id
144 AND HCAS.CUST_ACCOUNT_ID = p_customer_id
145 AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
146 AND HCAS.ORG_ID = HCSU.ORG_ID ;
147 -- removed the nvl from org_id k proj
148
149 cursor l_rel_cust_ship_to_loc_csr
150 (p_customer_id IN NUMBER,
151 p_ship_to_location_id IN NUMBER,
152 p_org_id IN NUMBER) is
153 SELECT HCSU.LOCATION, HCSU.CONTACT_ID
154 FROM HZ_CUST_SITE_USES_ALL HCSU,
155 HZ_CUST_ACCT_SITES_ALL HCAS,
156 HZ_PARTY_SITES HPS,
157 HZ_CUST_ACCOUNTS HCA,
158 HZ_CUST_ACCT_RELATE_ALL HCAR
159 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
160 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
161 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
162 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
163 AND HCSU.STATUS = 'A'
164 AND HCAS.STATUS = 'A'
165 AND HCA.STATUS = 'A'
166 AND HPS.LOCATION_ID = p_ship_to_location_id
167 AND HCA.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
168 AND HCAR.RELATED_CUST_ACCOUNT_ID = p_customer_id
169 AND HCAR.SHIP_TO_FLAG = 'Y'
170 AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
171 AND HCAS.ORG_ID = HCSU.ORG_ID ;
172 -- removed the nvl from org_id k proj
173
174
175
176 l_operating_unit NUMBER;
177 l_ship_to_site_use_id NUMBER;
178 l_ship_to_location_id NUMBER;
179 l_cnt NUMBER;
180 l_ship_to_loc_code VARCHAR2(32767);
181 l_ship_to_site_contact_id NUMBER;
182 -- bug 3920178}
183
184 l_per_first_name VARCHAR2(150);
185 l_per_middle_name VARCHAR2(60);
186 l_per_last_name VARCHAR2(150);
187 l_contact_person_name VARCHAR2(400);
188 l_owner_table_id NUMBER;
189 l_per_ph_number VARCHAR2(60);
190 l_return_status VARCHAR2(2);
191 --
192 l_debug_on BOOLEAN;
193 --
194 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_INFO';
195 --
196 BEGIN
197 --
198 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
199 --
200 IF l_debug_on IS NULL
201 THEN
202 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
203 END IF;
204 --
205 IF l_debug_on THEN
206 wsh_debug_sv.push(l_module_name);
207 wsh_debug_sv.log (l_module_name, 'delivery id' , p_delivery_id);
208 wsh_debug_sv.log (l_module_name, 'document type' , p_document_type);
209 END IF;
210
211 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
212
213 OPEN l_del_info_cur;
214 FETCH l_del_info_cur INTO x_name, l_customer_id, l_org_id,l_ship_to_location_id;--bug 3920178
215 IF ( l_del_info_cur % NOTFOUND) THEN
216 IF l_debug_on THEN
217 wsh_debug_sv.log (l_module_name, 'Error at cursor l_del_info_cur');
218 END IF;
219
220 CLOSE l_del_info_cur;
221 RAISE wsh_invalid_delivery_id;
222 END IF;
223 CLOSE l_del_info_cur;
224
225 open l_cust_cur(l_customer_id);
226 fetch l_cust_cur into x_customer_name, x_customer_number;
227 close l_cust_cur;
228
229 --bug 3920178 {
230 open l_ship_to_site_use_id_csr(p_delivery_id);
231 fetch l_ship_to_site_use_id_csr into l_ship_to_site_use_id, l_cnt;
232 IF l_ship_to_site_use_id_csr%NOTFOUND THEN
233 l_ship_to_site_use_id := -1;
234 END IF;
235 close l_ship_to_site_use_id_csr;
236
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.log(L_MODULE_NAME, 'l_ship_to_site_use_id' , l_ship_to_site_use_id);
239 END IF;
240 IF nvl(l_ship_to_site_use_id, -1) <> -1 THEN
241 --{
242 open l_site_use_loc_csr(l_ship_to_site_use_id);
243 fetch l_site_use_loc_csr into l_ship_to_loc_code, l_ship_to_site_contact_id;
244 IF l_site_use_loc_csr%NOTFOUND THEN
245 l_ship_to_loc_code := NULL;
246 END IF;
247 close l_site_use_loc_csr;
248 --}
249 END IF;
250
251 IF l_ship_to_loc_code IS NULL THEN
252 --{
253 l_operating_unit := WSH_UTIL_CORE.Get_OperatingUnit_Id(p_delivery_id);
254
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.log(l_module_name, 'l_operating_unit' , l_operating_unit);
257 END IF;
258 open l_cust_ship_to_loc_csr
259 (l_customer_id,
260 l_ship_to_location_id,
261 l_operating_unit);
262 fetch l_cust_ship_to_loc_csr into l_ship_to_loc_code, l_ship_to_site_contact_id;
263 IF l_cust_ship_to_loc_csr%NOTFOUND THEN
264 l_ship_to_loc_code := NULL;
265 END IF;
266 close l_cust_ship_to_loc_csr;
267
268 IF l_ship_to_loc_code IS NULL THEN
269 --{
270 IF l_debug_on THEN
271 WSH_DEBUG_SV.logmsg(L_MODULE_NAME, 'Using Customer Relationship Cursor');
272 END IF;
273
274 open l_rel_cust_ship_to_loc_csr
275 (l_customer_id,
276 l_ship_to_location_id,
277 l_operating_unit);
278 fetch l_rel_cust_ship_to_loc_csr into l_ship_to_loc_code,l_ship_to_site_contact_id;
279 close l_rel_cust_ship_to_loc_csr;
280 --}
281 END IF;
282 --}
283 END IF;
284 IF l_debug_on THEN
285 WSH_DEBUG_SV.log(l_module_name, 'l_ship_to_loc_code', l_ship_to_loc_code);
286 END IF;
287
288 IF l_ship_to_loc_code IS NULL THEN
289 raise fnd_api.g_exc_error;
290 END IF;
291
292 IF (l_ship_to_site_contact_id is not null) THEN
293 --{
294 get_name_number(
295 l_ship_to_site_contact_id,
296 l_per_ph_number,
297 l_contact_person_name,
298 l_return_status
299 );
300 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 x_cnsgn_cont_per_name := l_contact_person_name;
305 x_cnsgn_cont_per_ph := l_per_ph_number;
306 --}
307 END IF;
308
309 IF l_debug_on THEN
310 WSH_DEBUG_SV.log (L_MODULE_NAME, 'Contact Person Name ',
311 x_cnsgn_cont_per_name);
312 WSH_DEBUG_SV.log (L_MODULE_NAME, ' Contact Person Phone Number ',
313 x_cnsgn_cont_per_ph);
314 END IF;
315
316
317 x_ship_to_loc_code := l_ship_to_loc_code;
318 --bug 3920178 }
319
320 x_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
321 (
322 p_organization_id =>l_org_id,
323 x_return_status =>x_return_status
324 );
325 IF l_debug_on THEN
326 wsh_debug_sv.log (l_module_name, 'x_warehouse_type,x_return_status',x_warehouse_type||','||x_return_status);
327 END IF;
328
329 IF ( p_document_type = 'SA' ) THEN
330
331 OPEN l_src_hdr_no_cur;
332 FETCH l_src_hdr_no_cur INTO x_name;
333 IF ( l_src_hdr_no_cur % NOTFOUND) THEN
334 IF l_debug_on THEN
335 wsh_debug_sv.log (l_module_name, 'Error at cursor l_src_hdr_no_cur');
336 END IF;
337 CLOSE l_src_hdr_no_cur;
338 RAISE wsh_invalid_delivery_id;
339 END IF;
340 CLOSE l_src_hdr_no_cur;
341
342 OPEN l_get_dates_cur;
343 FETCH l_get_dates_cur
344 INTO x_arrival_date,
345 x_departure_date,
346 x_vehicle_num_prefix,
347 x_vehicle_number,
348 x_route_id,
349 x_routing_instructions,
350 x_departure_seal_code,
351 --Bug 3458160
352 x_operator;
353 IF ( l_get_dates_cur % NOTFOUND ) THEN
354 IF l_debug_on THEN
355 wsh_debug_sv.log (l_module_name, 'Error at cursor l_get_dates_cur');
356 END IF;
357 CLOSE l_get_dates_cur;
358 RAISE wsh_invalid_delivery_id;
359 END IF;
360 CLOSE l_get_dates_cur;
361 IF l_debug_on THEN
362 wsh_debug_sv.log (l_module_name, 'Arrival Date', x_arrival_date);
363 wsh_debug_sv.log (l_module_name, 'Departure Date', x_departure_date);
364 END IF;
365
366
367 END IF;
368
369 IF l_debug_on THEN
370 wsh_debug_sv.log (l_module_name, 'name' , x_name);
371 wsh_debug_sv.pop (l_module_name);
372 END IF;
373 EXCEPTION
374 --bug 3920178
375 WHEN fnd_api.g_exc_error THEN
376 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
377 IF l_debug_on THEN
378 WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_error has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
379 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
380 END IF;
381 WHEN wsh_invalid_delivery_id THEN
382 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
385 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_id');
386 END IF;
387
388 WHEN OTHERS THEN
389 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
392 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
393 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
394 END IF;
395 END Get_Delivery_Info;
396
397
398 /*
399 This procedure Get_Part_Addr_Info, is called by the outbound map(WSHSSNO.xgm),
400 to populate the elements in the PARTNER segment under SHIPMENT level.
401
402 */
403 PROCEDURE Get_Part_Addr_Info(
404 p_partner_type IN VARCHAR2,
405 p_delivery_id IN NUMBER,
406 x_party_name OUT NOCOPY VARCHAR2,
407 x_partner_location OUT NOCOPY VARCHAR2,
408 x_currency OUT NOCOPY VARCHAR2,
409 x_duns_number OUT NOCOPY VARCHAR2,
410 x_intmed_ship_to_location OUT NOCOPY VARCHAR2,
411 x_pooled_ship_to_location OUT NOCOPY VARCHAR2,
412 x_address1 OUT NOCOPY VARCHAR2,
413 x_address2 OUT NOCOPY VARCHAR2,
414 x_address3 OUT NOCOPY VARCHAR2,
415 x_address4 OUT NOCOPY VARCHAR2,
416 x_city OUT NOCOPY VARCHAR2,
417 x_country OUT NOCOPY VARCHAR2,
418 x_county OUT NOCOPY VARCHAR2,
419 x_postal_code OUT NOCOPY VARCHAR2,
420 x_region OUT NOCOPY VARCHAR2,
421 x_state OUT NOCOPY VARCHAR2,
422 x_fax_number OUT NOCOPY VARCHAR2,
423 x_telephone OUT NOCOPY VARCHAR2,
424 x_url OUT NOCOPY VARCHAR2,
425 x_return_status OUT NOCOPY VARCHAR2) IS
426
427 CURSOR loc_ids_cur IS
428 SELECT organization_id,
429 initial_pickup_location_id,
430 ultimate_dropoff_location_id,
431 intmed_ship_to_location_id,
432 pooled_ship_to_location_id,
433 currency_code
434 FROM wsh_new_deliveries
435 WHERE delivery_id = p_delivery_id;
436
437 /* Patchset I: Locations Project. Select address components from
438 wsh_ship_from_org_locations_v */
439 CURSOR ship_from_info_cur(p_org_id NUMBER, p_loc_id NUMBER) IS
440 SELECT
441 WSFL.ORGANIZATION_NAME PARTY_NAME,
442 HL.LOCATION_CODE PARTNER_LOCATION,
443 0 DUNS_NUMBER,
444 NULL INTMED_SHIP_TO_LOCATION,
445 NULL POOLED_SHIP_TO_LOCATION_ID,
446 WSFL.ADDRESS1 ADDRESS1,
447 WSFL.ADDRESS2 ADDRESS2,
448 WSFL.ADDRESS3 ADDRESS3,
449 NULL ADDRESS4,
450 WSFL.CITY CITY,
451 WSFL.COUNTRY COUNTRY,
452 NULL COUNTY,
453 WSFL.POSTAL_CODE POSTAL_CODE,
454 WSFL.PROVINCE REGION,
455 WSFL.STATE STATE,
456 HL.TELEPHONE_NUMBER_2 FAX_NUMBER,
457 HL.TELEPHONE_NUMBER_1 TELEPHONE,
458 NULL URL
459 FROM
460 wsh_ship_from_org_locations_v WSFL,
461 HR_LOCATIONS_ALL HL
462 WHERE
463 WSFL.wsh_location_id = p_loc_id
464 AND WSFL.source_location_id = HL.location_id;
465
466 /* Patchset I: Locations Project. Selecting from wsh_customer_locations_v */
467 CURSOR ship_to_info_cur(p_loc_id NUMBER, p_opUnit_id NUMBER DEFAULT NULL) IS
468 SELECT
469 DISTINCT wclv.CUSTOMER_NAME PARTY_NAME,
470 wclv.LOCATION PARTNER_LOCATION,
471 NULL CURRENCY,
472 wclv.DUNS_NUMBER DUNS_NUMBER,
473 WCLV.ADDRESS1 ADDRESS1,
474 WCLV.ADDRESS2 ADDRESS2,
475 WCLV.ADDRESS3 ADDRESS3,
476 WCLV.ADDRESS4 ADDRESS4,
477 WCLV.CITY CITY,
478 WCLV.COUNTRY COUNTRY,
479 WCLV.COUNTY COUNTY,
480 WCLV.POSTAL_CODE POSTAL_CODE,
481 WCLV.PROVINCE REGION,
482 WCLV.STATE STATE
483 FROM
484 wsh_customer_locations_v wclv
485 WHERE
486 wclv.wsh_location_id = p_loc_id
487 and wclv.org_id = nvl(p_opUnit_id, wclv.org_id)
488 AND wclv.customer_status = 'A'
489 AND wclv.cust_acct_site_status = 'A'
490 AND wclv.site_use_status = 'A'
491 AND wclv.site_use_code = 'SHIP_TO';
492
493 l_organization_id NUMBER;
494 l_init_loc_id NUMBER;
495 l_ult_loc_id NUMBER;
496 l_intmed_loc_id NUMBER;
497 l_pooled_loc_id NUMBER;
498 l_return_status VARCHAR2(30);
499 l_dummy VARCHAR2(360);
500 --
501 -- Patchset I: Locations Project. kvenkate.
502 l_opUnit_id NUMBER;
503
504 l_debug_on BOOLEAN;
505 --
506 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PART_ADDR_INFO';
507 --
508 BEGIN
509 --
510 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
511 --
512 IF l_debug_on IS NULL
513 THEN
514 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
515 END IF;
516 --
517 IF l_debug_on THEN
518 wsh_debug_sv.push(l_module_name);
519 wsh_debug_sv.log (l_module_name, 'Partner Type', p_partner_type);
520 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
521 END IF;
522
523 OPEN loc_ids_cur;
524 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;
525 CLOSE loc_ids_cur;
526
527 IF l_debug_on THEN
528 wsh_debug_sv.logmsg(l_module_name, 'Calling program unit WSH_UTIL_CORE.GET_OPERATINGUNIT_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
529 END IF;
530
531 /* Patchset I: Locations Project. Get the OperatingUnit Id */
532 l_opUnit_id := wsh_util_core.get_OperatingUnit_id(p_delivery_id);
533
534
535 IF l_debug_on THEN
536 wsh_debug_sv.log(l_module_name, 'Operating Unit Id', l_opUnit_id);
537 wsh_debug_sv.log (l_module_name, 'Initial Pickup Location Id', l_init_loc_id);
538 wsh_debug_sv.log (l_module_name, 'Ultimate Dropoff location Id', l_ult_loc_id);
539 wsh_debug_sv.log (l_module_name, 'Intmed ShipTo Location Id', l_intmed_loc_id);
540 wsh_debug_sv.log (l_module_name, 'Pooled ShipTo Location Id', l_pooled_loc_id);
541 END IF;
542
543 IF(p_partner_type = 'ShipFrom') THEN
544 OPEN ship_from_info_cur(l_organization_id, l_init_loc_id);
545 FETCH ship_from_info_cur INTO
546 x_party_name,
547 x_partner_location,
548 x_duns_number,
549 x_intmed_ship_to_location,
550 x_pooled_ship_to_location,
551 x_address1,
552 x_address2,
553 x_address3,
554 x_address4,
555 x_city,
556 x_country,
557 x_county,
558 x_postal_code,
559 x_region,
560 x_state,
561 x_fax_number,
562 x_telephone,
563 x_url;
564 CLOSE ship_from_info_cur;
565
566 ELSIF(p_partner_type = 'ShipTo') THEN
567 /* Patchset I: Locations Project. Use OperatingUnit Id for ShipTo*/
568 OPEN ship_to_info_cur(l_ult_loc_id, l_opUnit_id);
569 FETCH ship_to_info_cur INTO x_party_name,
570 x_partner_location,
571 x_currency,
572 x_duns_number,
573 x_address1,
574 x_address2,
575 x_address3,
576 x_address4,
577 x_city,
578 x_country,
579 x_county,
580 x_postal_code,
581 x_region,
582 x_state;
583 CLOSE ship_to_info_cur;
584
585 OPEN ship_to_info_cur(l_intmed_loc_id);
586 FETCH ship_to_info_cur INTO l_dummy,
587 x_intmed_ship_to_location,
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 l_dummy;
600 CLOSE ship_to_info_cur;
601
602 OPEN ship_to_info_cur(l_pooled_loc_id);
603 FETCH ship_to_info_cur INTO l_dummy,
604 x_pooled_ship_to_location,
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 l_dummy;
617 CLOSE ship_to_info_cur;
618
619 Get_Phone_Fax(
620 p_loc_id => l_ult_loc_id,
621 x_phone => x_telephone,
622 x_fax => x_fax_number,
623 x_url => x_url,
624 x_return_status => l_return_status);
625
626 IF l_debug_on THEN
627 wsh_debug_sv.log (l_module_name, 'x_telephone,x_fax_number', x_telephone||','||x_fax_number);
628 wsh_debug_sv.log (l_module_name, 'x_url', x_url);
629 wsh_debug_sv.log (l_module_name, 'l_return_status', l_return_status);
630 END IF;
631
632 END IF;
633
634 IF l_debug_on THEN
635 wsh_debug_sv.pop(l_module_name);
636 END IF;
637 EXCEPTION
638 WHEN OTHERS THEN
639 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
640
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
643 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
644 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
645 END IF;
646 END get_part_addr_info;
647
648 PROCEDURE Get_Phone_Fax(
649 p_loc_id IN NUMBER,
650 x_phone OUT NOCOPY VARCHAR2,
651 x_fax OUT NOCOPY VARCHAR2,
652 x_url OUT NOCOPY VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2) IS
654
655 /* Patchset I: Locations Project. Joining with wsh_locations_hz_v */
656 CURSOR phone_fax_cur(l_line_type VARCHAR2) IS
657 SELECT hcp.raw_phone_number,
658 hcp.url
659 FROM hz_party_sites hps,
660 hz_contact_points hcp,
661 wsh_locations_hz_v wlhz
662 WHERE HCP.CONTACT_POINT_TYPE = 'PHONE'
663 AND HCP.PHONE_LINE_TYPE=l_line_type
664 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
665 AND HPS.PARTY_SITE_ID = HCP.OWNER_TABLE_ID
666 AND wlhz.wsh_location_id = p_loc_id
667 AND wlhz.source_location_id = hps.location_id
668 ORDER BY hcp.primary_flag desc;
669
670 --
671 l_debug_on BOOLEAN;
672 --
673 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PHONE_FAX';
674 --
675 BEGIN
676 --
677 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
678 --
679 IF l_debug_on IS NULL
680 THEN
681 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
682 END IF;
683 --
684 IF l_debug_on THEN
685 wsh_debug_sv.push(l_module_name);
686 wsh_debug_sv.log (l_module_name, 'Loc Id', p_loc_id);
687 END IF;
688
689 OPEN phone_fax_cur('GEN');
690 FETCH phone_fax_cur INTO x_phone, x_url;
691 CLOSE phone_fax_cur;
692
693 OPEN phone_fax_cur('FAX');
694 FETCH phone_fax_cur INTO x_fax, x_url;
695 CLOSE phone_fax_cur;
696
697 IF l_debug_on THEN
698 wsh_debug_sv.pop(l_module_name);
699 END IF;
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
704 IF l_debug_on THEN
705 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
706 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
707 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
708 END IF;
709 END Get_Phone_Fax;
710
711 PROCEDURE get_ship_method_code(
712 p_carrier_name IN VARCHAR2,
713 p_service_level IN VARCHAR2,
714 p_mode_of_transport IN VARCHAR2,
715 p_doc_type IN VARCHAR2, -- bug 3479643
716 p_delivery_name IN VARCHAR2, -- bug 3479643
717 x_ship_method_code OUT NOCOPY VARCHAR2,
718 x_return_status OUT NOCOPY VARCHAR2)
719 IS
720
721 -- Bug fix 2930693
722 -- Added nvl to wcs.service_level and wcs.mode_of_transport because they could be NULL in wsh_carrier_services
723 -- bug 3479643
724 -- Created a separate cursor so that for 945 inbound, we can
725 -- use the delivery's ship method to validate the incoming ship method
726 cursor c_ship_method_cur is
727 select wcs.ship_method_code
728 from wsh_carrier_services wcs,
729 wsh_carriers_v wcar
730 where wcar.carrier_name = p_carrier_name
731 and nvl(wcs.service_level, '!') = nvl(p_service_level, '!')
732 and nvl(wcs.mode_of_transport, '!') = nvl(p_mode_of_transport, '!')
733 and wcs.carrier_id = wcar.carrier_id;
734
735 l_ship_method_code VARCHAR2(32767);
736 l_delivery_id NUMBER;
737 l_organization_id NUMBER;
738 l_org_type VARCHAR2(32767);
739 l_return_status VARCHAR2(1);
740 -- bug 3479643
741
742 wsh_invalid_ship_method EXCEPTION;
743 --
744 l_debug_on BOOLEAN;
745 --
746 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SHIP_METHOD_CODE';
747 --
748
749 BEGIN
750 --
751 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
752 --
753 IF l_debug_on IS NULL
754 THEN
755 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
756 END IF;
757 --
758 IF l_debug_on THEN
759 wsh_debug_sv.push(l_module_name);
760 wsh_debug_sv.log (l_module_name, 'Carrier Name', p_carrier_name);
761 wsh_debug_sv.log (l_module_name, 'Service Level', p_service_level);
762 wsh_debug_sv.log (l_module_name, 'Mode of Transport', p_mode_of_transport);
763 wsh_debug_sv.log (l_module_name, 'Document Type', p_doc_type);
764 wsh_debug_sv.log (l_module_name, 'Delivery Name', p_delivery_name);
765 END IF;
766 -- bug 3857041
767 IF (
768 nvl(p_service_level,'!!!!') <> '!!!!'
769 OR
770 nvl(p_mode_of_transport,'!!!!') <> '!!!!'
771 )
772 THEN
773 --{
774 open c_ship_method_cur;
775 fetch c_ship_method_cur into x_ship_method_code;
776 IF ( c_ship_method_cur%NOTFOUND) THEN
777 IF l_debug_on THEN
778 WSH_DEBUG_SV.logmsg(l_module_name,'Error -- Ship Method Not Found');
779 END IF;
780 END IF;
781 close c_ship_method_cur;
782 --}
783 END IF;
784 -- bug 3857041
785
786 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
787 IF l_debug_on THEN
788 wsh_debug_sv.pop(l_module_name);
789 END IF;
790
791 EXCEPTION
792 WHEN OTHERS THEN
793 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
794 IF l_debug_on THEN
795 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
796 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
797 END IF;
798 END get_ship_method_code;
799
800 -- ---------------------------------------------------------------------
801 -- Procedure: Get_Locn_Cust_Info
802 --
803 -- Parameters:
804 --
805 -- Description: This procedure gets the location, party_name, party_number
806 -- that are required for SHIPITEM and SHIPUNIT during
807 -- 940/945 outbound. This procedure to be called from
808 -- the outbound mapping.
809 -- Created: Locations Project. Patchset I. KVENKATE
810 -- -----------------------------------------------------------------------
811
812 PROCEDURE get_locn_cust_info(
813 p_location_id IN NUMBER,
814 p_org_id IN NUMBER,
815 p_customer_id IN NUMBER,
816 x_location OUT NOCOPY VARCHAR2,
817 x_party_name OUT NOCOPY VARCHAR2,
818 x_party_number OUT NOCOPY VARCHAR2,
819 x_return_status OUT NOCOPY VARCHAR2,
820 p_delivery_detail_id IN NUMBER,
821 p_wsn_rowid IN VARCHAR2,
822 p_requested_quantity IN NUMBER,
823 p_fm_serial_number IN VARCHAR2,
824 p_to_serial_number IN VARCHAR2,
825 x_requested_quantity OUT NOCOPY NUMBER,
826 x_shipped_quantity OUT NOCOPY NUMBER,
827 p_site_use_id IN NUMBER,
828 --bug 4227777
829 p_entity_type IN VARCHAR2,
830 x_cnsgn_cont_per_name OUT NOCOPY VARCHAR2,
831 x_cnsgn_cont_per_ph OUT NOCOPY VARCHAR2
832 )
833
834 IS
835
836 l_org_id NUMBER;
837 l_count NUMBER;
838 CURSOR loc_cust_cur (p_loc_id NUMBER, p_org_id NUMBER, p_cust_id NUMBER) IS
839 SELECT HCSU.LOCATION --bug 3920178 , HP.PARTY_NAME, HP.PARTY_NUMBER
840 FROM
841 HZ_CUST_SITE_USES_ALL HCSU,
842 HZ_CUST_ACCT_SITES_ALL HCAS,
843 HZ_PARTY_SITES HPS,
844 HZ_CUST_ACCOUNTS HCA,
845 HZ_PARTIES HP,
846 WSH_LOCATIONS WL1
847 WHERE
848 WL1.wsh_location_id = p_loc_id AND
849 HCA.CUST_ACCOUNT_ID = p_cust_id AND --bugfix 3842898
850 (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id) AND
851 WL1.SOURCE_LOCATION_ID = HPS.LOCATION_ID AND
852 WL1.LOCATION_SOURCE_CODE = 'HZ' AND
853 HCA.PARTY_ID = HP.PARTY_ID AND
854 HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND
855 HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND
856 HCSU.SITE_USE_CODE = 'SHIP_TO' AND
857 HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID AND
858 HCSU.STATUS = 'A' AND
859 HCAS.STATUS = 'A' AND
860 HCA.STATUS = 'A' AND
861 HCAS.ORG_ID = HCSU.ORG_ID AND
862 -- removed the NVL around the org_id k proj
863 --bug 3920178
864 HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
865 ORDER BY
866 HCSU.SITE_USE_CODE;
867
868 CURSOR org_id_cur(p_del_detail_id NUMBER) IS
869 SELECT wdd.org_id org_id, count(*) cnt
870 FROM wsh_delivery_details wdd
871 WHERE wdd.delivery_detail_id IN
872 (SELECT wda.delivery_detail_id
873 FROM wsh_delivery_assignments_v wda
874 START WITH delivery_detail_id = p_del_detail_id
875 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id )
876 GROUP BY org_id
877 HAVING org_id IS NOT NULL
878 ORDER BY cnt desc;
879
880 CURSOR get_rowid_count(cp_delivery_detail_id NUMBER) IS
881 SELECT rowidtochar(min(rowid)),count(*),sum(quantity)
882 FROM wsh_serial_numbers
883 WHERE delivery_detail_id = cp_delivery_detail_id;
884
885 CURSOR get_wsn_qty(cp_wsn_rowid VARCHAR2) IS
886 SELECT quantity
887 FROM wsh_serial_numbers
888 WHERE rowidtochar(rowid) = cp_wsn_rowid;
889
890 --bug 3920178 {
891 cursor l_site_use_loc_csr(p_site_use_id IN NUMBER) is
892 SELECT LOCATION
893 FROM HZ_CUST_SITE_USES_ALL
894 WHERE site_use_id = p_site_use_id;
895
896 cursor l_rel_cust_ship_to_loc_csr
897 (p_customer_id IN NUMBER,
898 p_ship_to_location_id IN NUMBER,
899 p_org_id IN NUMBER) is
900 SELECT HCSU.LOCATION
901 FROM HZ_CUST_SITE_USES_ALL HCSU,
902 HZ_CUST_ACCT_SITES_ALL HCAS,
903 HZ_PARTY_SITES HPS,
904 HZ_CUST_ACCOUNTS HCA,
905 HZ_CUST_ACCT_RELATE_ALL HCAR
906 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
907 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
908 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
909 AND HCSU.SITE_USE_CODE = 'SHIP_TO'
910 AND HCSU.STATUS = 'A'
911 AND HCAS.STATUS = 'A'
912 AND HCA.STATUS = 'A'
913 AND HPS.LOCATION_ID = p_ship_to_location_id
914 AND HCA.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
915 AND HCAR.RELATED_CUST_ACCOUNT_ID = p_customer_id
916 AND HCAR.SHIP_TO_FLAG = 'Y'
917 AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
918 AND HCAS.ORG_ID = HCSU.ORG_ID ;
919 -- removed the nvl from org_id k proj
920
921 CURSOR c_cust_info_cur(p_customer_id IN NUMBER) IS
922 SELECT HP.PARTY_NAME, HP.PARTY_NUMBER
923 FROM HZ_PARTIES HP,
924 HZ_CUST_ACCOUNTS HCA
925 WHERE HP.PARTY_ID = HCA.PARTY_ID
926 AND HCA.CUST_ACCOUNT_ID = p_customer_id;
927
928 l_deliver_to_site_use_id NUMBER;
929 l_location VARCHAR2(32767);
930
931 --bug 3920178 }
932
933
934 l_wsn_rowid VARCHAR2(100);
935 l_wsn_count NUMBER;
936 l_wsn_qty NUMBER;
937 l_wsn_sum_qty NUMBER;
938
939 --bug 4227777
940
941 CURSOR l_get_lines_in_container_csr IS
942 SELECT wda.delivery_detail_id
943 FROM wsh_delivery_assignments_v wda
944 START WITH wda.parent_delivery_detail_id = p_delivery_detail_id
945 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id;
946
947 CURSOR l_get_ship_to_contact_csr (p_detail_id NUMBER)
948 IS
949 SELECT wdd.ship_to_contact_id
950 FROM wsh_delivery_details wdd
951 WHERE wdd.delivery_detail_id = p_detail_id
952 AND wdd.container_flag = 'N';
953
954
955
956 l_per_first_name VARCHAR2(150);
957 l_per_middle_name VARCHAR2(60);
958 l_per_last_name VARCHAR2(150);
959 l_contact_person_name VARCHAR2(360);
960 l_owner_table_id NUMBER;
961
962 l_per_ph_number VARCHAR2(40);
963 l_per_email_addr VARCHAR2(2000);
964
965 l_uniq_ship_to_contact_id NUMBER;
966 l_curr_ship_to_contact_id NUMBER;
967 l_delivery_detail_tab wsh_util_core.id_tab_type;
968 l_ship_to_contact_id_tab wsh_util_core.id_tab_type;
969 l_return_status VARCHAR2(2);
970
971
972 l_debug_on BOOLEAN;
973 --
974 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOCN_CUST_INFO';
975
976 BEGIN
977 --
978 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
979 --
980 IF l_debug_on IS NULL
981 THEN
982 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
983 END IF;
984 --
985 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
986
987 IF l_debug_on THEN
988 wsh_debug_sv.push(l_module_name);
989 wsh_debug_sv.log (l_module_name, 'Location Id', p_location_id);
990 wsh_debug_sv.log(l_module_name, 'Org Id', p_org_id);
991 wsh_debug_sv.log(l_module_name, 'Customer Id', p_customer_id);
992 wsh_debug_sv.log(l_module_name, 'p_wsn_rowid',p_wsn_rowid);
993 wsh_debug_sv.log(l_module_name, 'p_requested_quantity',p_requested_quantity);
994 wsh_debug_sv.log(l_module_name, 'p_fm_serial_number',p_fm_serial_number);
995 wsh_debug_sv.log(l_module_name, 'p_to_serial_number',p_to_serial_number);
996 wsh_debug_sv.log(l_module_name, 'p_delivery_detail_id',p_delivery_detail_id);
997 wsh_debug_sv.log(l_module_name, 'p_site_use_id',p_site_use_id);
998 wsh_debug_sv.log(l_module_name, 'p_entity_type',p_entity_type);
999 END IF;
1000
1001
1002
1003 --bug 3920178{
1004 IF p_site_use_id IS NOT NULL THEN
1005 open l_site_use_loc_csr(p_site_use_id);
1006 fetch l_site_use_loc_csr INTO l_location;
1007 close l_site_use_loc_csr;
1008 END IF;
1009 if l_debug_on then
1010 wsh_debug_sv.log(l_module_name, 'l_location',l_location);
1011 end if;
1012
1013 IF l_location IS NULL AND
1014 --bug 3920178}
1015 p_location_id IS NOT NULL THEN
1016 IF p_org_id IS NULL THEN
1017 OPEN org_id_cur(p_delivery_detail_id);
1018 FETCH org_id_cur INTO l_org_id, l_count;
1019 CLOSE org_id_cur;
1020 ELSE
1021 l_org_id := p_org_id;
1022 END IF;
1023
1024 IF l_debug_on THEN
1025 wsh_debug_sv.log (l_module_name, 'l_org_id', l_org_id);
1026 END IF;
1027
1028 OPEN loc_cust_cur(p_location_id, l_org_id, p_customer_id);
1029 FETCH loc_cust_cur INTO l_location; --bug 3920178
1030 IF loc_cust_cur%NOTFOUND THEN
1031 --Bug 3920178{
1032 IF l_debug_on THEN
1033 wsh_debug_sv.logmsg(l_module_name, 'Using l_rel_cust_ship_to_loc_csr');
1034 END IF;
1035 OPEN l_rel_cust_ship_to_loc_csr(p_customer_id, p_location_id, l_org_id);
1036 FETCH l_rel_cust_ship_to_loc_csr INTO l_location;
1037 CLOSE l_rel_cust_ship_to_loc_csr;
1038 --Bug 3920178}
1039 END IF;
1040
1041 CLOSE loc_cust_cur;
1042 END IF;
1043 IF p_customer_id IS NOT NULL THEN
1044 OPEN c_cust_info_cur(p_customer_id);
1045 FETCH c_cust_info_Cur INTO x_party_name, x_party_number;
1046 CLOSE c_cust_info_cur;
1047 END IF;
1048
1049 if l_debug_on then
1050 wsh_debug_sv.log(l_module_name, 'l_location',l_location);
1051 end if;
1052
1053 /* bug 4227777
1054 IF l_location IS NULL THEN
1055 raise fnd_api.g_exc_error;
1056 END IF;
1057 */
1058 x_location := l_location;
1059
1060 IF l_debug_on THEN
1061 wsh_debug_sv.log(l_module_name, 'Location', x_location);
1062 wsh_debug_sv.log(l_module_name, 'Party Name', x_party_name);
1063 wsh_debug_sv.log(l_module_name, 'Party Number', x_party_number);
1064 END IF;
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 -- R12.1.1 STANDALONE PROJECT
1313 /*===========================================================================
1314 | |
1315 | PROCEDURE NAME Get_Stnd_Delivery_Info |
1316 | |
1317 | DESCRIPTION This procedure gets the Delivery Information at the time |
1318 | populating the data into the interface tables, when |
1319 | processing an Standalone inbound XML transaction. |
1320 | |
1321 | MODIFICATION HISTORY |
1322 | |
1323 | 02/18/09 Leelaraj Created |
1324 | |
1325 ============================================================================*/
1326 PROCEDURE Get_Stnd_Delivery_Info (p_delivery_id IN NUMBER ,
1327 x_name OUT NOCOPY VARCHAR2,
1328 x_org_id OUT NOCOPY NUMBER ,
1329 x_arrival_date OUT NOCOPY DATE ,
1330 x_departure_date OUT NOCOPY DATE ,
1331 x_vehicle_num_prefix OUT NOCOPY VARCHAR2,
1332 x_vehicle_number OUT NOCOPY VARCHAR2,
1333 x_route_id OUT NOCOPY VARCHAR2,
1334 x_routing_instructions OUT NOCOPY VARCHAR2,
1335 x_departure_seal_code OUT NOCOPY VARCHAR2,
1336 x_operator OUT NOCOPY VARCHAR2,
1337 x_ship_to_loc_code OUT NOCOPY VARCHAR2,
1338 x_pack_slip_num OUT NOCOPY VARCHAR2,
1339 x_bill_of_lading_num OUT NOCOPY VARCHAR2,
1340 -- Distributed - TPW Changes
1341 x_customer_name OUT NOCOPY VARCHAR2,
1342 x_return_status OUT NOCOPY VARCHAR2) IS
1343
1344 CURSOR l_del_info_cur
1345 IS
1346 SELECT name,
1347 customer_id,
1348 organization_id,
1349 ultimate_dropoff_location_id
1350 FROM wsh_new_deliveries
1351 WHERE delivery_id = p_delivery_id;
1352
1353 CURSOR l_get_dates_cur
1354 IS
1355 SELECT wts1.Actual_Departure_Date,
1356 wts2.Actual_Arrival_Date,
1357 wt.Vehicle_Num_Prefix,
1358 wt.Vehicle_Number,
1359 wt.Route_ID,
1360 wt.Routing_Instructions,
1361 wts1.Departure_Seal_Code,
1362 wt.operator
1363 FROM wsh_delivery_legs wdl,
1364 wsh_trip_stops wts1,
1365 wsh_trip_stops wts2,
1366 wsh_trips wt
1367 WHERE wts1.trip_id = wt.trip_id
1368 AND wts2.trip_id = wt.trip_id
1369 AND wts1.stop_id = wdl.pick_up_stop_id
1370 AND wts2.stop_id = wdl.drop_off_stop_id
1371 AND wdl.delivery_id = p_delivery_id;
1372
1373 l_org_id NUMBER;
1374 l_customer_id NUMBER;
1375
1376 wsh_invalid_delivery_id EXCEPTION;
1377
1378 CURSOR l_ship_to_site_use_id_csr( c_delivery_id IN NUMBER) is
1379 SELECT wdd.ship_to_site_use_id ship_to_site_use_id , count(*) cnt
1380 FROM wsh_delivery_assignments_v wda,
1381 wsh_delivery_details wdd
1382 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1383 AND wda.delivery_id = c_delivery_id
1384 AND wdd.container_flag = 'N'
1385 GROUP BY ship_to_site_use_id
1386 ORDER BY cnt DESC;
1387
1388 CURSOR l_site_use_loc_csr(c_site_use_id IN NUMBER) is
1389 SELECT location
1390 FROM hz_cust_site_uses_all
1391 WHERE site_use_id = c_site_use_id;
1392
1393 CURSOR l_cust_ship_to_loc_csr
1394 (c_customer_id IN NUMBER,
1395 c_ship_to_location_id IN NUMBER,
1396 c_org_id IN NUMBER) IS
1397 SELECT hcsu.location
1398 FROM hz_cust_site_uses_all hcsu,
1399 hz_cust_acct_sites_all hcas,
1400 hz_cust_accounts hca,
1401 hz_party_sites hps
1402 WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
1403 AND hcas.party_site_id = hps.party_site_id
1404 AND hcas.cust_account_id = hca.cust_account_id
1405 AND hcsu.site_use_code = 'SHIP_TO'
1406 AND hcsu.status = 'A'
1407 AND hcas.status = 'A'
1408 AND hca.status = 'A'
1409 AND hps.location_id = c_ship_to_location_id
1410 AND hcas.cust_account_id = c_customer_id
1411 AND (hcas.org_id IS NULL OR hcas.org_id = c_org_id)
1412 AND hcas.org_id = hcsu.org_id ;
1413
1414 CURSOR l_rel_cust_ship_to_loc_csr
1415 (c_customer_id IN NUMBER,
1416 c_ship_to_location_id IN NUMBER,
1417 c_org_id IN NUMBER) IS
1418 SELECT hcsu.location
1419 FROM hz_cust_site_uses_all hcsu,
1420 hz_cust_acct_sites_all hcas,
1421 hz_party_sites hps,
1422 hz_cust_accounts hca,
1423 hz_cust_acct_relate_all hcar
1424 WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
1425 AND hcas.party_site_id = hps.party_site_id
1426 AND hcas.cust_account_id = hca.cust_account_id
1427 AND hcsu.site_use_code = 'SHIP_TO'
1428 AND hcsu.status = 'A'
1429 AND hcas.status = 'A'
1430 AND hca.status = 'A'
1431 AND hps.location_id = c_ship_to_location_id
1432 AND hca.cust_account_id = hcar.cust_account_id
1433 AND hcar.related_cust_account_id = c_customer_id
1434 AND hcar.ship_to_flag = 'Y'
1435 AND (hcas.org_id IS NULL OR hcas.org_id = c_org_id)
1436 AND hcas.org_id = hcsu.org_id ;
1437
1438 -- Distributed - TPW Changes
1439 CURSOR c_cust_name_cur( c_customer_id IN NUMBER ) IS
1440 SELECT hp.party_name
1441 FROM hz_parties hp,
1442 hz_cust_accounts hca
1443 WHERE hca.party_id = hp.party_id
1444 AND hca.cust_account_id = c_customer_id;
1445
1446 l_operating_unit NUMBER;
1447 l_ship_to_site_use_id NUMBER;
1448 l_ship_to_location_id NUMBER;
1449 l_cnt NUMBER;
1450 l_ship_to_loc_code VARCHAR2(32767);
1451
1452 l_return_status VARCHAR2(2);
1453 --
1454 l_debug_on BOOLEAN;
1455 --
1456 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_STND_DELIVERY_INFO';
1457 --
1458 BEGIN
1459 --
1460 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1461 --
1462 IF l_debug_on IS NULL
1463 THEN
1464 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1465 END IF;
1466 --
1467 IF l_debug_on THEN
1468 wsh_debug_sv.push(l_module_name);
1469 wsh_debug_sv.log (l_module_name, 'delivery id' , p_delivery_id);
1470 END IF;
1471 --
1472 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
1473
1474 OPEN l_del_info_cur;
1475 FETCH l_del_info_cur INTO x_name, l_customer_id, l_org_id,l_ship_to_location_id;
1476 --
1477 IF ( l_del_info_cur % NOTFOUND) THEN
1478 --
1479 IF l_debug_on THEN
1480 wsh_debug_sv.log (l_module_name, 'Error at cursor l_del_info_cur');
1481 END IF;
1482 --
1483 CLOSE l_del_info_cur;
1484 RAISE wsh_invalid_delivery_id;
1485 END IF;
1486 --
1487 CLOSE l_del_info_cur;
1488
1489 l_operating_unit := WSH_UTIL_CORE.Get_OperatingUnit_Id(p_delivery_id);
1490 x_org_id := l_operating_unit;
1491
1492 -- Distributed - TPW Changes
1493 open c_cust_name_cur (l_customer_id);
1494 fetch c_cust_name_cur into x_customer_name;
1495 close c_cust_name_cur;
1496
1497 OPEN l_ship_to_site_use_id_csr(p_delivery_id);
1498 FETCH l_ship_to_site_use_id_csr INTO l_ship_to_site_use_id, l_cnt;
1499 --
1500 IF l_ship_to_site_use_id_csr%NOTFOUND THEN
1501 l_ship_to_site_use_id := -1;
1502 END IF;
1503 --
1504 CLOSE l_ship_to_site_use_id_csr;
1505 --
1506 IF l_debug_on THEN
1507 WSH_DEBUG_SV.log(L_MODULE_NAME, 'l_ship_to_site_use_id' , l_ship_to_site_use_id);
1508 END IF;
1509 --
1510 IF nvl(l_ship_to_site_use_id, -1) <> -1 THEN
1511 --{
1512 OPEN l_site_use_loc_csr(l_ship_to_site_use_id);
1513 FETCH l_site_use_loc_csr INTO l_ship_to_loc_code;
1514 --
1515 IF l_site_use_loc_csr%NOTFOUND THEN
1516 l_ship_to_loc_code := NULL;
1517 END IF;
1518 --
1519 CLOSE l_site_use_loc_csr;
1520 --}
1521 END IF;
1522
1523 IF l_ship_to_loc_code IS NULL THEN
1524 --{
1525 --
1526 IF l_debug_on THEN
1527 WSH_DEBUG_SV.log(l_module_name, 'l_operating_unit' , l_operating_unit);
1528 END IF;
1529 --
1530 OPEN l_cust_ship_to_loc_csr
1531 (l_customer_id,
1532 l_ship_to_location_id,
1533 l_operating_unit);
1534 FETCH l_cust_ship_to_loc_csr INTO l_ship_to_loc_code;
1535 --
1536 IF l_cust_ship_to_loc_csr%NOTFOUND THEN
1537 l_ship_to_loc_code := NULL;
1538 END IF;
1539 --
1540 CLOSE l_cust_ship_to_loc_csr;
1541 --
1542 IF l_ship_to_loc_code IS NULL THEN
1543 --{
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.logmsg(L_MODULE_NAME, 'Using Customer Relationship Cursor');
1546 END IF;
1547
1548 OPEN l_rel_cust_ship_to_loc_csr(
1549 l_customer_id,
1550 l_ship_to_location_id,
1551 l_operating_unit);
1552 FETCH l_rel_cust_ship_to_loc_csr into l_ship_to_loc_code;
1553 CLOSE l_rel_cust_ship_to_loc_csr;
1554 --}
1555 END IF;
1556 --}
1557 END IF;
1558 IF l_debug_on THEN
1559 WSH_DEBUG_SV.log(l_module_name, 'l_ship_to_loc_code', l_ship_to_loc_code);
1560 END IF;
1561
1562 IF l_ship_to_loc_code IS NULL THEN
1563 raise fnd_api.g_exc_error;
1564 END IF;
1565
1566 x_ship_to_loc_code := l_ship_to_loc_code;
1567
1568 OPEN l_get_dates_cur;
1569 FETCH l_get_dates_cur
1570 INTO x_arrival_date,
1571 x_departure_date,
1572 x_vehicle_num_prefix,
1573 x_vehicle_number,
1574 x_route_id,
1575 x_routing_instructions,
1576 x_departure_seal_code,
1577 x_operator;
1578 --
1579 IF ( l_get_dates_cur % NOTFOUND ) THEN
1580 --
1581 IF l_debug_on THEN
1582 wsh_debug_sv.log (l_module_name, 'Error at cursor l_get_dates_cur');
1583 END IF;
1584 --
1585 CLOSE l_get_dates_cur;
1586 RAISE wsh_invalid_delivery_id;
1587 END IF;
1588 --
1589 CLOSE l_get_dates_cur;
1590
1591 BEGIN --{
1592 SELECT packing_slip_number
1593 INTO x_pack_slip_num
1594 FROM wsh_packing_slips_db_v
1595 WHERE delivery_id = p_delivery_id;
1596
1597 SELECT wdi.sequence_number
1598 INTO x_bill_of_lading_num
1599 FROM wsh_new_deliveries wnd,
1600 wsh_delivery_legs wdl,
1601 wsh_trip_stops wts,
1602 wsh_document_instances wdi
1603 WHERE wnd.delivery_id = p_delivery_id
1604 AND wnd.delivery_id = wdl.delivery_id
1605 AND wdl.pick_up_stop_id = wts.stop_id
1606 AND wts.stop_location_id = wnd.initial_pickup_location_id
1607 AND wdi.entity_id = wdl.delivery_leg_id
1608 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
1609 AND wdi.document_type = 'BOL';
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 NULL;
1614 END; --}
1615 --
1616 IF l_debug_on THEN
1617 wsh_debug_sv.log (l_module_name, 'Name' , x_name);
1618 wsh_debug_sv.log (l_module_name, 'Ship To Loc Code' , x_ship_to_loc_code);
1619 wsh_debug_sv.log (l_module_name, 'Arrival Date', x_arrival_date);
1620 wsh_debug_sv.log (l_module_name, 'Departure Date', x_departure_date);
1621 wsh_debug_sv.log (l_module_name, 'Packing Slip Number' , x_pack_slip_num);
1622 wsh_debug_sv.log (l_module_name, 'BOL Number' , x_bill_of_lading_num);
1623 wsh_debug_sv.log (l_module_name, 'Customer Name' , x_customer_name);
1624 wsh_debug_sv.pop (l_module_name);
1625 END IF;
1626 --
1627 EXCEPTION
1628 WHEN fnd_api.g_exc_error THEN
1629 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1630 IF l_debug_on THEN
1631 WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_error has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1632 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
1633 END IF;
1634 WHEN wsh_invalid_delivery_id THEN
1635 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1636 IF l_debug_on THEN
1637 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1638 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_id');
1639 END IF;
1640
1641 WHEN OTHERS THEN
1642 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1643 IF l_debug_on THEN
1644 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1645 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1646 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1647 END IF;
1648 END Get_Stnd_Delivery_Info;
1649
1650
1651 -- R12.1.1 STANDALONE PROJECT
1652 /*===========================================================================
1653 | |
1654 | PROCEDURE NAME Get_Delivery_Detail_Info |
1655 | |
1656 | DESCRIPTION This procedure gets the Delivery Detail Information |
1657 | like open quantity,backorder quantity,locator,order |
1658 | header ,order line number and Ship to contact information.|
1659 | |
1660 | MODIFICATION HISTORY |
1661 | |
1662 | 02/18/09 Leelaraj Created |
1663 | |
1664 ============================================================================*/
1665 PROCEDURE get_delivery_detail_info(
1666 p_src_line_id IN NUMBER ,
1667 p_delivery_detail_id IN NUMBER ,
1668 p_detail_seq_number IN NUMBER ,
1669 p_locator_id IN NUMBER ,
1670 p_wsn_rowid IN VARCHAR2,
1671 p_serial_type IN VARCHAR2 ,
1672 p_requested_quantity IN NUMBER,
1673 x_requested_quantity OUT NOCOPY NUMBER,
1674 x_shipped_quantity OUT NOCOPY NUMBER,
1675 x_open_quantity OUT NOCOPY NUMBER ,
1676 x_bo_quantity OUT NOCOPY NUMBER ,
1677 x_locator_code OUT NOCOPY VARCHAR2,
1678 x_shipto_cont_per_name OUT NOCOPY VARCHAR2,
1679 x_shipto_cont_per_ph OUT NOCOPY VARCHAR2,
1680 x_shipto_cont_per_id OUT NOCOPY NUMBER ,
1681 x_document_type OUT NOCOPY VARCHAR2,
1682 x_document_id OUT NOCOPY NUMBER ,
1683 x_line_number OUT NOCOPY NUMBER ,
1684 x_return_status OUT NOCOPY VARCHAR2) IS
1685
1686 CURSOR l_get_contacts_csr (c_detail_id NUMBER)
1687 IS
1688 SELECT wdd.ship_to_contact_id
1689 FROM wsh_delivery_details wdd
1690 WHERE wdd.delivery_detail_id = c_detail_id
1691 AND wdd.container_flag = 'N';
1692
1693 -- Distributed - TPW Changes
1694 CURSOR get_rowid_count(cp_delivery_detail_id NUMBER) IS
1695 SELECT rowidtochar(min(rowid)),count(*),sum(quantity)
1696 FROM wsh_serial_numbers
1697 WHERE delivery_detail_id = cp_delivery_detail_id;
1698
1699 CURSOR get_wsn_qty(cp_wsn_rowid VARCHAR2) IS
1700 SELECT quantity
1701 FROM wsh_serial_numbers
1702 WHERE rowidtochar(rowid) = cp_wsn_rowid;
1703
1704 CURSOR get_msnt_rowid_count(cp_delivery_detail_id NUMBER) IS
1705 SELECT rowidtochar(min(rowid)),count(*),sum(to_number(SERIAL_PREFIX))
1706 FROM mtl_serial_numbers_temp
1707 WHERE transaction_temp_id IN
1708 (SELECT transaction_temp_id
1709 FROM WSH_DELIVERY_DETAILS
1710 WHERE DELIVERY_DETAIL_ID = cp_delivery_detail_id
1711 AND SOURCE_CODE = 'OE');
1712
1713 CURSOR get_msnt_qty(cp_wsn_rowid VARCHAR2) IS
1714 SELECT to_number(SERIAL_PREFIX)
1715 FROM mtl_serial_numbers_temp
1716 WHERE rowidtochar(rowid) = cp_wsn_rowid;
1717
1718 l_wsn_rowid VARCHAR2(100);
1719 l_wsn_count NUMBER;
1720 l_wsn_qty NUMBER;
1721 l_wsn_sum_qty NUMBER;
1722
1723 l_uniq_ship_to_contact_id NUMBER;
1724 l_curr_ship_to_contact_id NUMBER;
1725 l_delivery_detail_tab wsh_util_core.id_tab_type;
1726 l_ship_to_contact_id_tab wsh_util_core.id_tab_type;
1727 l_return_status VARCHAR2(2);
1728 l_temp1 BOOLEAN;
1729 l_temp2 BOOLEAN;
1730 -- Distributed - TPW Changes
1731 l_source_document_type_id NUMBER;
1732 l_source_document_id NUMBER;
1733 l_source_document_line_id NUMBER;
1734
1735 l_debug_on BOOLEAN;
1736 --
1737 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_DETAIL_INFO';
1738
1739 BEGIN
1740 --
1741 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1742 --
1743 IF l_debug_on IS NULL
1744 THEN
1745 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1746 END IF;
1747 --
1748 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1749
1750 IF l_debug_on THEN
1751 wsh_debug_sv.push(l_module_name);
1752 wsh_debug_sv.log(l_module_name, 'p_src_line_id',p_src_line_id);
1753 wsh_debug_sv.log(l_module_name, 'p_delivery_detail_id',p_delivery_detail_id);
1754 wsh_debug_sv.log(l_module_name, 'p_detail_seq_number',p_detail_seq_number);
1755 wsh_debug_sv.log(l_module_name, 'p_locator_id',p_locator_id);
1756 wsh_debug_sv.log(l_module_name, 'p_serial_type',P_serial_type);
1757 wsh_debug_sv.log(l_module_name, 'p_wsn_rowid',p_wsn_rowid);
1758 wsh_debug_sv.log(l_module_name, 'p_requested_quantity',p_requested_quantity);
1759
1760 END IF;
1761
1762 IF (p_locator_id is not null) THEN --{
1763
1764 begin
1765 select concatenated_segments
1766 into x_locator_code
1767 from mtl_item_locations_kfv
1768 where inventory_location_id = p_locator_id;
1769 exception
1770 when others then
1771 null;
1772 end;
1773 END IF; --}
1774
1775 IF l_debug_on THEN
1776 wsh_debug_sv.log(l_module_name, 'x_locator_code', x_locator_code);
1777 END IF;
1778
1779 IF p_detail_seq_number = 1 THEN --{
1780
1781 select sum(requested_quantity)
1782 into x_open_quantity
1783 from wsh_delivery_details
1784 where source_line_id = p_src_line_id
1785 and source_code = 'OE'
1786 and released_status in ('N','R','S','Y');
1787
1788 select sum(requested_quantity)
1789 into x_bo_quantity
1790 from wsh_delivery_details
1791 where source_line_id = p_src_line_id
1792 and source_code = 'OE'
1793 and released_status = 'B';
1794
1795 IF l_debug_on THEN
1796 wsh_debug_sv.log(l_module_name, 'x_open_quantity', x_open_quantity);
1797 wsh_debug_sv.log(l_module_name, 'x_bo_quantity', x_bo_quantity);
1798 END IF;
1799
1800 END IF; --}
1801
1802 -- Distributed - TPW Changes
1803 IF p_wsn_rowid IS NOT NULL THEN --{
1804
1805 IF P_SERIAL_TYPE = 'WSN' THEN
1806
1807 OPEN get_wsn_qty(p_wsn_rowid);
1808 FETCH get_wsn_qty INTO l_wsn_qty;
1809 CLOSE get_wsn_qty;
1810
1811 x_shipped_quantity := l_wsn_qty;
1812
1813 OPEN get_rowid_count(p_delivery_detail_id);
1814 FETCH get_rowid_count INTO l_wsn_rowid, l_wsn_count,l_wsn_sum_qty;
1815 CLOSE get_rowid_count;
1816
1817 IF l_debug_on THEN
1818 wsh_debug_sv.log(l_module_name, 'l_wsn_qty',l_wsn_qty );
1819 wsh_debug_sv.log(l_module_name, 'l_wsn_rowid',l_wsn_rowid );
1820 wsh_debug_sv.log(l_module_name, 'l_wsn_count',l_wsn_count );
1821 wsh_debug_sv.log(l_module_name, 'l_wsn_sum_qty',l_wsn_sum_qty );
1822 END IF;
1823
1824 IF l_wsn_sum_qty = p_requested_quantity THEN
1825 x_requested_quantity :=l_wsn_qty;
1826 ELSE
1827
1828 IF p_wsn_rowid = l_wsn_rowid THEN
1829 x_requested_quantity:=trunc(p_requested_quantity/l_wsn_count)+ mod(p_requested_quantity,l_wsn_count);
1830 ELSE
1831 x_requested_quantity:=trunc(p_requested_quantity/l_wsn_count);
1832 END IF;
1833 END IF;
1834
1835 IF l_debug_on THEN
1836 wsh_debug_sv.log(l_module_name, 'x_shipped_quantity', x_shipped_quantity);
1837 wsh_debug_sv.log(l_module_name, 'x_requested_quantity', x_requested_quantity);
1838 END IF;
1839
1840 ELSE
1841 OPEN get_msnt_qty(p_wsn_rowid);
1842 FETCH get_msnt_qty INTO l_wsn_qty;
1843 CLOSE get_msnt_qty;
1844 x_shipped_quantity := l_wsn_qty;
1845
1846 OPEN get_msnt_rowid_count(p_delivery_detail_id);
1847 FETCH get_msnt_rowid_count INTO l_wsn_rowid, l_wsn_count,l_wsn_sum_qty;
1848 CLOSE get_msnt_rowid_count;
1849
1850 IF l_debug_on THEN
1851 wsh_debug_sv.log(l_module_name, 'l_msnt_qty',l_wsn_qty );
1852 wsh_debug_sv.log(l_module_name, 'l_msnt_rowid',l_wsn_rowid );
1853 wsh_debug_sv.log(l_module_name, 'l_msnt_count',l_wsn_count );
1854 wsh_debug_sv.log(l_module_name, 'l_msnt_sum_qty',l_wsn_sum_qty );
1855 END IF;
1856
1857 IF l_wsn_sum_qty = p_requested_quantity THEN
1858 x_requested_quantity :=l_wsn_qty;
1859 ELSE
1860
1861 IF p_wsn_rowid = l_wsn_rowid THEN
1862 x_requested_quantity:=trunc(p_requested_quantity/l_wsn_count)+ mod(p_requested_quantity,l_wsn_count);
1863 ELSE
1864 x_requested_quantity:=trunc(p_requested_quantity/l_wsn_count);
1865 END IF;
1866 END IF;
1867
1868 IF l_debug_on THEN
1869 wsh_debug_sv.log(l_module_name, 'x_shipped_quantity', x_shipped_quantity);
1870 wsh_debug_sv.log(l_module_name, 'x_requested_quantity', x_requested_quantity);
1871 END IF;
1872
1873 END IF ;
1874 --
1875 END IF; --}
1876
1877 OPEN l_get_contacts_csr (p_delivery_detail_id);
1878 FETCH l_get_contacts_csr INTO l_uniq_ship_to_contact_id;
1879 CLOSE l_get_contacts_csr;
1880
1881 IF l_debug_on THEN
1882 wsh_debug_sv.log(l_module_name, 'l_uniq_ship_to_contact_id',
1883 l_uniq_ship_to_contact_id);
1884 END IF;
1885
1886 IF l_uniq_ship_to_contact_id IS NOT NULL THEN --{
1887 get_name_number(
1888 l_uniq_ship_to_contact_id,
1889 x_shipto_cont_per_ph,
1890 x_shipto_cont_per_name,
1891 l_return_status
1892 );
1893 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1894 RAISE FND_API.G_EXC_ERROR;
1895 END IF;
1896 x_shipto_cont_per_id := l_uniq_ship_to_contact_id;
1897
1898 END IF; --}
1899
1900 begin
1901 select oh.order_number,
1902 ol.line_number,
1903 nvl(wth.document_type,'SalesOrder'),
1904 -- Distributed - TPW Changes
1905 ol.source_document_type_id,
1906 ol.source_document_id,
1907 ol.source_document_line_id
1908 into x_document_id,
1909 x_line_number,
1910 x_document_type,
1911 -- Distributed - TPW Changes
1912 l_source_document_type_id,
1913 l_source_document_id,
1914 l_source_document_line_id
1915 from oe_order_lines_all ol,
1916 oe_order_headers_all oh,
1917 wsh_transactions_history wth
1918 where ol.line_id = p_src_line_id
1919 and ol.header_id = oh.header_id
1920 and oh.header_id = wth.entity_number (+)
1921 and wth.entity_type(+) = 'ORDER'
1922 and wth.document_type(+) = 'SR'
1923 and wth.document_direction(+) = 'I'
1924 and wth.transaction_status(+) = 'SC'
1925 and rownum < 2;
1926
1927 -- Distributed - TPW Changes
1928 IF l_source_document_type_id = 10 THEN
1929
1930 select ph.segment1,
1931 pl.line_num,
1932 'InternalRequisition'
1933 into x_document_id,
1934 x_line_number,
1935 x_document_type
1936 from po_requisition_headers_all ph,
1937 po_requisition_lines_all pl
1938 where ph.requisition_header_id = pl.requisition_header_id
1939 and pl.requisition_line_id = l_source_document_line_id
1940 and ph.requisition_header_id = l_source_document_id;
1941
1942 END IF;
1943 exception
1944 when others then
1945 null;
1946 end;
1947
1948 IF l_debug_on THEN
1949 wsh_debug_sv.log(l_module_name, 'x_shipto_cont_per_name', x_shipto_cont_per_name);
1950 wsh_debug_sv.log(l_module_name, 'x_shipto_cont_per_ph', x_shipto_cont_per_ph);
1951 wsh_debug_sv.log(l_module_name, 'x_shipto_cont_per_id', x_shipto_cont_per_id);
1952 wsh_debug_sv.log(l_module_name, 'x_document_type', x_document_type);
1953 wsh_debug_sv.log(l_module_name, 'x_document_id', x_document_id);
1954 wsh_debug_sv.log(l_module_name, 'x_line_number', x_line_number);
1955 wsh_debug_sv.pop(l_module_name);
1956 END IF;
1957
1958 EXCEPTION
1959 WHEN FND_API.G_EXC_ERROR THEN
1960 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1961 wsh_util_core.add_message(x_return_status, l_module_name);
1962 --
1963 IF l_debug_on THEN
1964 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1965 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1966 END IF;
1967 WHEN OTHERS THEN
1968 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1969 IF l_debug_on THEN
1970 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1971 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1972 END IF;
1973
1974 END Get_Delivery_Detail_Info;
1975
1976 -- R12.1.1 STANDALONE PROJECT
1977 /*===========================================================================
1978 | |
1979 | PROCEDURE NAME get_detail_part_addr_info |
1980 | |
1981 | DESCRIPTION This procedure gets the Address Information |
1982 | and contact details of the parties. |
1983 | |
1984 | MODIFICATION HISTORY |
1985 | |
1986 | 02/18/09 Leelaraj Created |
1987 | |
1988 ============================================================================*/
1989
1990 PROCEDURE get_detail_part_addr_info(
1991 p_delivery_detail_id IN NUMBER,
1992 p_entity_type IN VARCHAR2,
1993 p_org_id IN NUMBER,
1994 p_partner_type IN VARCHAR2,
1995 x_partner_id OUT NOCOPY NUMBER,
1996 x_partner_name OUT NOCOPY VARCHAR2,
1997 x_partner_location OUT NOCOPY VARCHAR2,
1998 x_duns_number OUT NOCOPY VARCHAR2,
1999 x_address_id OUT NOCOPY NUMBER,
2000 x_address1 OUT NOCOPY VARCHAR2,
2001 x_address2 OUT NOCOPY VARCHAR2,
2002 x_address3 OUT NOCOPY VARCHAR2,
2003 x_address4 OUT NOCOPY VARCHAR2,
2004 x_city OUT NOCOPY VARCHAR2,
2005 x_country OUT NOCOPY VARCHAR2,
2006 x_county OUT NOCOPY VARCHAR2,
2007 x_postal_code OUT NOCOPY VARCHAR2,
2008 x_region OUT NOCOPY VARCHAR2,
2009 x_state OUT NOCOPY VARCHAR2,
2010 x_contact_id OUT NOCOPY NUMBER,
2011 x_contact_name OUT NOCOPY VARCHAR2,
2012 x_contact_telephone OUT NOCOPY VARCHAR2,
2013 x_return_status OUT NOCOPY VARCHAR2) IS
2014
2015
2016 CURSOR c_get_lines_in_container_csr IS
2017 SELECT wda.delivery_detail_id
2018 FROM wsh_delivery_assignments_v wda
2019 START WITH wda.parent_delivery_detail_id = p_delivery_detail_id
2020 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id;
2021
2022 CURSOR c_get_sites_contacts (c_detail_id NUMBER)
2023 IS
2024 SELECT ol.ship_to_org_id,
2025 ol.ship_to_contact_id,
2026 ol.deliver_to_org_id,
2027 ol.deliver_to_contact_id
2028 FROM wsh_delivery_details wdd,
2029 oe_order_lines_all ol
2030 WHERE wdd.delivery_detail_id = c_detail_id
2031 AND wdd.source_line_id = ol.line_id
2032 AND wdd.source_code = 'OE'
2033 AND wdd.container_flag = 'N';
2034
2035
2036 l_ship_to_site_id NUMBER;
2037 l_uniq_ship_to_contact_id NUMBER;
2038 l_curr_ship_to_contact_id NUMBER;
2039 l_uniq_deliver_to_site_id NUMBER;
2040 l_curr_deliver_to_site_id NUMBER;
2041 l_uniq_deliver_to_contact_id NUMBER;
2042 l_curr_deliver_to_contact_id NUMBER;
2043 l_delivery_detail_tab wsh_util_core.id_tab_type;
2044 l_return_status VARCHAR2(2);
2045 l_temp1 BOOLEAN;
2046 l_temp2 BOOLEAN;
2047 l_temp3 BOOLEAN;
2048 l_site_id NUMBER;
2049 l_contact_id NUMBER;
2050
2051
2052 l_debug_on BOOLEAN;
2053 --
2054 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DETAIL_PART_ADDR_INFO';
2055
2056 BEGIN
2057 --
2058 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2059 --
2060 IF l_debug_on IS NULL
2061 THEN
2062 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2063 END IF;
2064 --
2065 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2066
2067 IF l_debug_on THEN
2068 wsh_debug_sv.push(l_module_name);
2069 wsh_debug_sv.log(l_module_name, 'p_delivery_detail_id',p_delivery_detail_id);
2070 wsh_debug_sv.log(l_module_name, 'p_entity_type',p_entity_type);
2071 wsh_debug_sv.log(l_module_name, 'p_org_id',p_org_id);
2072 wsh_debug_sv.log(l_module_name, 'p_partner_type',p_partner_type);
2073 END IF;
2074
2075 IF p_partner_type not in ('ShipTo', 'DeliverTo') THEN
2076 return;
2077 END IF;
2078
2079 l_uniq_ship_to_contact_id := null;
2080 l_uniq_deliver_to_site_id := null;
2081 l_uniq_deliver_to_contact_id := null;
2082
2083 IF p_entity_type = 'CONTAINER' THEN --{
2084 OPEN c_get_lines_in_container_csr;
2085 FETCH c_get_lines_in_container_csr BULK COLLECT INTO l_delivery_detail_tab;
2086 CLOSE c_get_lines_in_container_csr;
2087
2088 IF l_delivery_detail_tab.count > 0 THEN --{
2089
2090 l_temp1 := FALSE;
2091 l_temp2 := FALSE;
2092 l_temp3 := FALSE;
2093
2094 FOR k in 1..l_delivery_detail_tab.count LOOP --{
2095 l_curr_ship_to_contact_id := null;
2096 l_curr_deliver_to_site_id := null;
2097 l_curr_deliver_to_contact_id := null;
2098
2099 OPEN c_get_sites_contacts (l_delivery_detail_tab(k));
2100 FETCH c_get_sites_contacts INTO l_ship_to_site_id, l_curr_ship_to_contact_id,
2101 l_curr_deliver_to_site_id, l_curr_deliver_to_contact_id;
2102 IF c_get_sites_contacts%FOUND THEN --{
2103 l_uniq_ship_to_contact_id := nvl(l_uniq_ship_to_contact_id,l_curr_ship_to_contact_id);
2104 l_uniq_deliver_to_site_id := nvl(l_uniq_deliver_to_site_id,l_curr_deliver_to_site_id);
2105 l_uniq_deliver_to_contact_id := nvl(l_uniq_deliver_to_contact_id,l_curr_deliver_to_contact_id);
2106
2107 IF (NOT l_temp1) AND l_curr_ship_to_contact_id IS NOT NULL
2108 AND l_uniq_ship_to_contact_id <> l_curr_ship_to_contact_id
2109 THEN
2110 l_uniq_ship_to_contact_id := null;
2111 l_temp1 := TRUE;
2112 END IF;
2113
2114 IF (NOT l_temp2) AND l_curr_deliver_to_site_id IS NOT NULL THEN
2115 IF l_uniq_deliver_to_site_id <> l_curr_deliver_to_site_id THEN
2116 l_uniq_deliver_to_site_id := null;
2117 l_uniq_deliver_to_contact_id := null;
2118 l_temp2 := TRUE;
2119 l_temp3 := TRUE;
2120 ELSE
2121 IF (NOT l_temp3) AND l_curr_deliver_to_contact_id IS NOT NULL
2122 AND l_uniq_deliver_to_contact_id <> l_curr_deliver_to_contact_id
2123 THEN
2124 l_uniq_deliver_to_contact_id := null;
2125 l_temp3 := TRUE;
2126 END IF;
2127 END IF;
2128 END IF;
2129
2130 IF l_temp1 and l_temp2 and l_temp3 THEN
2131 CLOSE c_get_sites_contacts;
2132 EXIT;
2133 END IF;
2134 END IF; --}
2135 CLOSE c_get_sites_contacts;
2136 END LOOP; --}
2137 END IF; --}
2138 END IF; --}
2139
2140 IF (p_partner_type = 'ShipTo') THEN
2141 l_site_id := l_ship_to_site_id;
2142 l_contact_id := l_uniq_ship_to_contact_id;
2143 ELSIF (p_partner_type = 'DeliverTo') THEN
2144 l_site_id := l_uniq_deliver_to_site_id;
2145 l_contact_id := l_uniq_deliver_to_contact_id;
2146 END IF;
2147
2148 IF l_debug_on THEN
2149 wsh_debug_sv.log(l_module_name, 'l_site_id', l_site_id);
2150 wsh_debug_sv.log(l_module_name, 'l_contact_id', l_contact_id);
2151 END IF;
2152
2153 IF (l_site_id is not null) THEN
2154 Get_Cust_addr_Info (
2155 p_site_id => l_site_id,
2156 p_contact_id => l_contact_id,
2157 p_org_id => p_org_id,
2158 x_partner_id => x_partner_id,
2159 x_partner_name => x_partner_name,
2160 x_partner_location => x_partner_location,
2161 x_duns_number => x_duns_number,
2162 x_address_id => x_address_id,
2163 x_address1 => x_address1,
2164 x_address2 => x_address2,
2165 x_address3 => x_address3,
2166 x_address4 => x_address4,
2167 x_city => x_city,
2168 x_country => x_country,
2169 x_county => x_county,
2170 x_postal_code => x_postal_code,
2171 x_region => x_region,
2172 x_state => x_state,
2173 x_contact_name => x_contact_name,
2174 x_contact_telephone => x_contact_telephone,
2175 x_return_status => x_return_status);
2176 x_contact_id := l_contact_id;
2177 END IF;
2178
2179 IF l_debug_on THEN
2180 wsh_debug_sv.pop(l_module_name);
2181 END IF;
2182
2183 EXCEPTION
2184 WHEN FND_API.G_EXC_ERROR THEN
2185 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2186 wsh_util_core.add_message(x_return_status, l_module_name);
2187 --
2188 IF l_debug_on THEN
2189 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2190 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2191 END IF;
2192 WHEN OTHERS THEN
2193 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2194 IF l_debug_on THEN
2195 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2196 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2197 END IF;
2198
2199 END Get_Detail_Part_addr_Info;
2200
2201 -- R12.1.1 STANDALONE PROJECT
2202 /*===========================================================================
2203 | |
2204 | PROCEDURE NAME Get_Cust_addr_Info |
2205 | |
2206 | DESCRIPTION This procedure gets the Ship To Address Information |
2207 | of the parties. |
2208 | This is a new API created for Standalone Project. |
2209 | |
2210 | MODIFICATION HISTORY |
2211 | |
2212 | 02/18/09 Leelaraj Created |
2213 | |
2214 ============================================================================*/
2215
2216 PROCEDURE Get_Cust_addr_Info (
2217 p_site_id IN NUMBER,
2218 p_contact_id IN NUMBER,
2219 p_org_id IN NUMBER,
2220 x_partner_id OUT NOCOPY NUMBER,
2221 x_partner_name OUT NOCOPY VARCHAR2,
2222 x_partner_location OUT NOCOPY VARCHAR2,
2223 x_duns_number OUT NOCOPY VARCHAR2,
2224 x_address_id OUT NOCOPY NUMBER,
2225 x_address1 OUT NOCOPY VARCHAR2,
2226 x_address2 OUT NOCOPY VARCHAR2,
2227 x_address3 OUT NOCOPY VARCHAR2,
2228 x_address4 OUT NOCOPY VARCHAR2,
2229 x_city OUT NOCOPY VARCHAR2,
2230 x_country OUT NOCOPY VARCHAR2,
2231 x_county OUT NOCOPY VARCHAR2,
2232 x_postal_code OUT NOCOPY VARCHAR2,
2233 x_region OUT NOCOPY VARCHAR2,
2234 x_state OUT NOCOPY VARCHAR2,
2235 x_contact_name OUT NOCOPY VARCHAR2,
2236 x_contact_telephone OUT NOCOPY VARCHAR2,
2237 x_return_status OUT NOCOPY VARCHAR2) IS
2238
2239 CURSOR c_party_info_cur(c_site_id NUMBER, c_opunit_id NUMBER DEFAULT NULL) IS
2240 SELECT
2241 distinct wclv.customer_id party_id,
2242 wclv.customer_name party_name,
2243 wclv.location partner_location,
2244 wclv.duns_number duns_number,
2245 wclv.site_use_id address_id,
2246 wclv.address1 address1,
2247 wclv.address2 address2,
2248 wclv.address3 address3,
2249 wclv.address4 address4,
2250 wclv.city city,
2251 wclv.country country,
2252 wclv.county county,
2253 wclv.postal_code postal_code,
2254 wclv.province region,
2255 wclv.state state
2256 FROM wsh_customer_locations_v wclv
2257 WHERE wclv.site_use_id = c_site_id
2258 AND wclv.org_id = c_opunit_id
2259 AND wclv.customer_status = 'A'
2260 AND wclv.cust_acct_site_status = 'A'
2261 AND wclv.site_use_status = 'A';
2262
2263 l_return_status VARCHAR2(2);
2264
2265 l_debug_on BOOLEAN;
2266 --
2267 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUST_ADDR_INFO';
2268 --
2269 BEGIN
2270
2271 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2272 IF l_debug_on IS NULL THEN
2273 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2274 END IF;
2275
2276 IF l_debug_on THEN
2277 wsh_debug_sv.push(l_module_name);
2278 wsh_debug_sv.log (l_module_name, 'p_site_id', p_site_id);
2279 wsh_debug_sv.log (l_module_name, 'p_contact_id', p_contact_id);
2280 wsh_debug_sv.log (l_module_name, 'p_org_id', p_org_id);
2281 END IF;
2282
2283 OPEN c_party_info_cur(p_site_id, p_org_id);
2284 FETCH c_party_info_cur INTO
2285 x_partner_id,
2286 x_partner_name,
2287 x_partner_location,
2288 x_duns_number,
2289 x_address_id,
2290 x_address1,
2291 x_address2,
2292 x_address3,
2293 x_address4,
2294 x_city,
2295 x_country,
2296 x_county,
2297 x_postal_code,
2298 x_region,
2299 x_state;
2300 CLOSE c_party_info_cur;
2301
2302 IF (p_contact_id is not null) THEN
2303 get_name_number(
2304 p_contact_id,
2305 x_contact_telephone,
2306 x_contact_name,
2307 l_return_status
2308 );
2309 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2310 RAISE FND_API.G_EXC_ERROR;
2311 END IF;
2312 END IF;
2313
2314 IF l_debug_on THEN
2315 wsh_debug_sv.log(l_module_name, 'x_partner_id', x_partner_id);
2316 wsh_debug_sv.log(l_module_name, 'x_partner_name', x_partner_name);
2317 wsh_debug_sv.log(l_module_name, 'x_partner_location', x_partner_location);
2318 wsh_debug_sv.log(l_module_name, 'x_duns_number', x_duns_number);
2319 wsh_debug_sv.log(l_module_name, 'x_address_id', x_address_id);
2320 wsh_debug_sv.log(l_module_name, 'x_address1', x_address1);
2321 wsh_debug_sv.log(l_module_name, 'x_address2', x_address2);
2322 wsh_debug_sv.log(l_module_name, 'x_address3', x_address3);
2323 wsh_debug_sv.log(l_module_name, 'x_address4', x_address4);
2324 wsh_debug_sv.log(l_module_name, 'x_city', x_city);
2325 wsh_debug_sv.log(l_module_name, 'x_country', x_country);
2326 wsh_debug_sv.log(l_module_name, 'x_county', x_county);
2327 wsh_debug_sv.log(l_module_name, 'x_postal_code', x_postal_code);
2328 wsh_debug_sv.log(l_module_name, 'x_region', x_region);
2329 wsh_debug_sv.log(l_module_name, 'x_state', x_state);
2330 wsh_debug_sv.log(l_module_name, 'x_contact_name', x_contact_name);
2331 wsh_debug_sv.log(l_module_name, 'x_contact_telephone', x_contact_telephone);
2332 wsh_debug_sv.pop(l_module_name);
2333 END IF;
2334
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2338
2339 IF l_debug_on THEN
2340 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2341 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2342 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2343 END IF;
2344 END Get_Cust_addr_Info;
2345
2346 -- R12.1.1 STANDALONE PROJECT
2347 /*===========================================================================
2348 | |
2349 | PROCEDURE NAME get_detail_part_addr_info |
2350 | |
2351 | DESCRIPTION This procedure gets the Ship From, Ship To and |
2352 | Bill To Information of the parties. |
2353 | This is a new API created for Standalone Project. |
2354 | |
2355 | MODIFICATION HISTORY |
2356 | |
2357 | 02/18/09 Leelaraj Created |
2358 | |
2359 ============================================================================*/
2360
2361 PROCEDURE Get_Del_Part_Addr_Info(
2362 p_partner_type IN VARCHAR2,
2363 p_delivery_id IN NUMBER,
2364 p_org_id IN NUMBER,
2365 x_partner_id OUT NOCOPY NUMBER,
2366 x_partner_name OUT NOCOPY VARCHAR2,
2367 x_partner_location OUT NOCOPY VARCHAR2,
2368 x_duns_number OUT NOCOPY VARCHAR2,
2369 x_intmed_ship_to_location OUT NOCOPY VARCHAR2,
2370 x_pooled_ship_to_location OUT NOCOPY VARCHAR2,
2371 x_address_id OUT NOCOPY NUMBER,
2372 x_address1 OUT NOCOPY VARCHAR2,
2373 x_address2 OUT NOCOPY VARCHAR2,
2374 x_address3 OUT NOCOPY VARCHAR2,
2375 x_address4 OUT NOCOPY VARCHAR2,
2376 x_city OUT NOCOPY VARCHAR2,
2377 x_country OUT NOCOPY VARCHAR2,
2378 x_county OUT NOCOPY VARCHAR2,
2379 x_postal_code OUT NOCOPY VARCHAR2,
2380 x_region OUT NOCOPY VARCHAR2,
2381 x_state OUT NOCOPY VARCHAR2,
2382 x_contact_id OUT NOCOPY NUMBER,
2383 x_contact_name OUT NOCOPY VARCHAR2,
2384 x_telephone OUT NOCOPY VARCHAR2,
2385 x_return_status OUT NOCOPY VARCHAR2) IS
2386
2387 CURSOR c_del_info IS
2388 SELECT organization_id,
2389 initial_pickup_location_id,
2390 intmed_ship_to_location_id,
2391 pooled_ship_to_location_id
2392 FROM wsh_new_deliveries
2393 WHERE delivery_id = p_delivery_id;
2394
2395 CURSOR ship_from_info_cur(c_org_id NUMBER, c_loc_id NUMBER) IS
2396 SELECT
2397 wsfl.organization_name party_name,
2398 hl.location_code partner_location,
2399 0 duns_number,
2400 NULL intmed_ship_to_location,
2401 NULL pooled_ship_to_location_id,
2402 wsfl.address1 address1,
2403 wsfl.address2 address2,
2404 wsfl.address3 address3,
2405 NULL address4,
2406 wsfl.city city,
2407 wsfl.country country,
2408 NULL county,
2409 wsfl.postal_code postal_code,
2410 wsfl.province region,
2411 wsfl.state state
2412 FROM wsh_ship_from_org_locations_v wsfl,
2413 hr_locations_all hl
2414 WHERE wsfl.wsh_location_id = c_loc_id
2415 AND wsfl.source_location_id = hl.location_id;
2416
2417 CURSOR ship_to_info_cur(c_loc_id NUMBER, c_opUnit_id NUMBER DEFAULT NULL) IS
2418 SELECT
2419 distinct wclv.customer_name party_name,
2420 wclv.location partner_location,
2421 wclv.duns_number duns_number,
2422 wclv.address1 address1,
2423 wclv.address2 address2,
2424 wclv.address3 address3,
2425 wclv.address4 address4,
2426 wclv.city city,
2427 wclv.country country,
2428 wclv.county county,
2429 wclv.postal_code postal_code,
2430 wclv.province region,
2431 wclv.state state
2432 FROM wsh_customer_locations_v wclv
2433 WHERE wclv.wsh_location_id = c_loc_id
2434 AND wclv.org_id = nvl(c_opUnit_id, wclv.org_id)
2435 AND wclv.customer_status = 'A'
2436 AND wclv.cust_acct_site_status = 'A'
2437 AND wclv.site_use_status = 'A'
2438 AND wclv.site_use_code = 'SHIP_TO';
2439
2440 CURSOR c_del_sites IS
2441 SELECT ol.ship_to_org_id,
2442 ol.invoice_to_org_id
2443 FROM wsh_new_deliveries wnd,
2444 wsh_delivery_assignments_v wda,
2445 wsh_delivery_details wdd,
2446 oe_order_lines_all ol
2447 WHERE wnd.delivery_id = p_delivery_id
2448 AND wnd.delivery_id = wda.delivery_id
2449 AND wda.delivery_detail_id = wdd.delivery_detail_id
2450 AND wdd.source_code = 'OE'
2451 AND wdd.source_line_id = ol.line_id
2452 AND rownum < 2;
2453
2454 CURSOR c_party_info_cur(c_site_id NUMBER, c_opunit_id NUMBER DEFAULT NULL) IS
2455 SELECT
2456 DISTINCT wclv.customer_id party_id,
2457 wclv.customer_name party_name,
2458 wclv.location partner_location,
2459 wclv.duns_number duns_number,
2460 wclv.site_use_id address_id,
2461 wclv.address1 address1,
2462 wclv.address2 address2,
2463 wclv.address3 address3,
2464 wclv.address4 address4,
2465 wclv.city city,
2466 wclv.country country,
2467 wclv.county county,
2468 wclv.postal_code postal_code,
2469 wclv.province region,
2470 wclv.state state
2471 FROM wsh_customer_locations_v wclv
2472 WHERE wclv.site_use_id = c_site_id
2473 AND wclv.org_id = c_opunit_id
2474 AND wclv.customer_status = 'A'
2475 AND wclv.cust_acct_site_status = 'A'
2476 AND wclv.site_use_status = 'A';
2477
2478 l_organization_id NUMBER;
2479 l_init_loc_id NUMBER;
2480 l_ult_loc_id NUMBER;
2481 l_intmed_loc_id NUMBER;
2482 l_pooled_loc_id NUMBER;
2483 l_ship_to_site_id NUMBER;
2484 l_ship_to_contact_id NUMBER;
2485 l_bill_to_site_id NUMBER;
2486 l_bill_to_contact_id NUMBER;
2487 l_return_status VARCHAR2(30);
2488 l_dummy VARCHAR2(360);
2489 --
2490
2491 l_debug_on BOOLEAN;
2492 --
2493 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DEL_PART_ADDR_INFO';
2494 --
2495 BEGIN
2496
2497 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2498 IF l_debug_on IS NULL
2499 THEN
2500 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2501 END IF;
2502
2503 IF l_debug_on THEN
2504 wsh_debug_sv.push(l_module_name);
2505 wsh_debug_sv.log (l_module_name, 'Partner Type', p_partner_type);
2506 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
2507 wsh_debug_sv.log (l_module_name, 'Org Id', p_org_id);
2508 END IF;
2509
2510 OPEN c_del_info;
2511 FETCH c_del_info INTO l_organization_id, l_init_loc_id, l_intmed_loc_id, l_pooled_loc_id;
2512 CLOSE c_del_info;
2513
2514 IF l_debug_on THEN
2515 wsh_debug_sv.log (l_module_name, 'Initial Pickup Location Id', l_init_loc_id);
2516 wsh_debug_sv.log (l_module_name, 'Intmed ShipTo Location Id', l_intmed_loc_id);
2517 wsh_debug_sv.log (l_module_name, 'Pooled ShipTo Location Id', l_pooled_loc_id);
2518 END IF;
2519
2520 IF (p_partner_type = 'ShipFrom') THEN --{
2521 OPEN ship_from_info_cur(l_organization_id, l_init_loc_id);
2522 FETCH ship_from_info_cur INTO
2523 x_partner_name,
2524 x_partner_location,
2525 x_duns_number,
2526 x_intmed_ship_to_location,
2527 x_pooled_ship_to_location,
2528 x_address1,
2529 x_address2,
2530 x_address3,
2531 x_address4,
2532 x_city,
2533 x_country,
2534 x_county,
2535 x_postal_code,
2536 x_region,
2537 x_state;
2538 CLOSE ship_from_info_cur;
2539 x_partner_id := l_organization_id;
2540
2541 ELSIF(p_partner_type = 'ShipTo') THEN
2542
2543 OPEN c_del_sites;
2544 FETCH c_del_sites INTO l_ship_to_site_id, l_bill_to_site_id;
2545 CLOSE c_del_sites;
2546
2547 OPEN c_party_info_cur(l_ship_to_site_id, p_org_id);
2548 FETCH c_party_info_cur INTO
2549 x_partner_id,
2550 x_partner_name,
2551 x_partner_location,
2552 x_duns_number,
2553 x_address_id,
2554 x_address1,
2555 x_address2,
2556 x_address3,
2557 x_address4,
2558 x_city,
2559 x_country,
2560 x_county,
2561 x_postal_code,
2562 x_region,
2563 x_state;
2564 CLOSE c_party_info_cur;
2565
2566 OPEN ship_to_info_cur(l_intmed_loc_id);
2567 FETCH ship_to_info_cur INTO l_dummy,
2568 x_intmed_ship_to_location,
2569 l_dummy,
2570 l_dummy,
2571 l_dummy,
2572 l_dummy,
2573 l_dummy,
2574 l_dummy,
2575 l_dummy,
2576 l_dummy,
2577 l_dummy,
2578 l_dummy,
2579 l_dummy;
2580 CLOSE ship_to_info_cur;
2581
2582 OPEN ship_to_info_cur(l_pooled_loc_id);
2583 FETCH ship_to_info_cur INTO l_dummy,
2584 x_pooled_ship_to_location,
2585 l_dummy,
2586 l_dummy,
2587 l_dummy,
2588 l_dummy,
2589 l_dummy,
2590 l_dummy,
2591 l_dummy,
2592 l_dummy,
2593 l_dummy,
2594 l_dummy,
2595 l_dummy;
2596 CLOSE ship_to_info_cur;
2597
2598 BEGIN
2599 select distinct ol.ship_to_contact_id
2600 into l_ship_to_contact_id
2601 from wsh_new_deliveries wnd,
2602 wsh_delivery_assignments_v wda,
2603 wsh_delivery_details wdd,
2604 oe_order_lines_all ol
2605 where wnd.delivery_id = p_delivery_id
2606 and wnd.delivery_id = wda.delivery_id
2607 and wda.delivery_detail_id = wdd.delivery_detail_id
2608 and wdd.source_code = 'OE'
2609 and wdd.source_line_id = ol.line_id;
2610 EXCEPTION
2611 when others then
2612 l_ship_to_contact_id := null;
2613 END;
2614
2615 IF (l_ship_to_contact_id is not null) THEN
2616 x_contact_id := l_ship_to_contact_id;
2617 get_name_number(
2618 l_ship_to_contact_id,
2619 x_telephone,
2620 x_contact_name,
2621 l_return_status
2622 );
2623 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2624 RAISE FND_API.G_EXC_ERROR;
2625 END IF;
2626 END IF;
2627
2628 IF l_debug_on THEN
2629 wsh_debug_sv.log (l_module_name, 'x_telephone,x_contact_name', x_telephone||','||x_contact_name);
2630 wsh_debug_sv.log (l_module_name, 'l_return_status', l_return_status);
2631 END IF;
2632
2633 ELSIF(p_partner_type = 'BillTo') THEN
2634
2635 OPEN c_del_sites;
2636 FETCH c_del_sites INTO l_ship_to_site_id, l_bill_to_site_id;
2637 CLOSE c_del_sites;
2638
2639 OPEN c_party_info_cur(l_bill_to_site_id, p_org_id);
2640 FETCH c_party_info_cur INTO
2641 x_partner_id,
2642 x_partner_name,
2643 x_partner_location,
2644 x_duns_number,
2645 x_address_id,
2646 x_address1,
2647 x_address2,
2648 x_address3,
2649 x_address4,
2650 x_city,
2651 x_country,
2652 x_county,
2653 x_postal_code,
2654 x_region,
2655 x_state;
2656 CLOSE c_party_info_cur;
2657
2658 BEGIN
2659 select distinct ol.invoice_to_contact_id
2660 into l_bill_to_contact_id
2661 from wsh_new_deliveries wnd,
2662 wsh_delivery_assignments_v wda,
2663 wsh_delivery_details wdd,
2664 oe_order_lines_all ol
2665 where wnd.delivery_id = p_delivery_id
2666 and wnd.delivery_id = wda.delivery_id
2667 and wda.delivery_detail_id = wdd.delivery_detail_id
2668 and wdd.source_code = 'OE'
2669 and wdd.source_line_id = ol.line_id;
2670 EXCEPTION
2671 when others then
2672 l_bill_to_contact_id := null;
2673 END;
2674
2675 IF (l_bill_to_contact_id is not null) THEN
2676 x_contact_id := l_bill_to_contact_id;
2677 get_name_number(
2678 l_bill_to_contact_id,
2679 x_telephone,
2680 x_contact_name,
2681 l_return_status
2682 );
2683 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2684 RAISE FND_API.G_EXC_ERROR;
2685 END IF;
2686 END IF; --}
2687
2688 IF l_debug_on THEN
2689 wsh_debug_sv.log (l_module_name, 'x_telephone,x_contact_name', x_telephone||','||x_contact_name);
2690 wsh_debug_sv.log (l_module_name, 'l_return_status', l_return_status);
2691 END IF;
2692
2693 END IF;
2694
2695 IF l_debug_on THEN
2696 wsh_debug_sv.pop(l_module_name);
2697 END IF;
2698
2699 EXCEPTION
2700 WHEN OTHERS THEN
2701 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2702
2703 IF l_debug_on THEN
2704 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2705 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2706 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2707 END IF;
2708 END get_del_part_addr_info;
2709
2710 -- TPW - Distributed Organization Changes
2711 PROCEDURE get_name_number(
2712 p_contact_id IN NUMBER,
2713 x_per_ph_number OUT NOCOPY VARCHAR2,
2714 x_contact_person_name OUT NOCOPY VARCHAR2,
2715 x_return_status OUT NOCOPY VARCHAR2
2716 )
2717 IS
2718 l_debug_on BOOLEAN;
2719 --
2720 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
2721 || 'GET_NAME_NUMBER';
2722 l_per_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
2723 l_per_middle_name HZ_PARTIES.PERSON_MIDDLE_NAME%TYPE;
2724 l_per_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
2725 l_owner_table_id NUMBER;
2726
2727 cursor l_ship_to_site_contact_csr(p_contact_id IN NUMBER) is
2728 SELECT PER_CONTACT.PERSON_FIRST_NAME,
2729 PER_CONTACT.PERSON_MIDDLE_NAME,
2730 PER_CONTACT.PERSON_LAST_NAME,
2731 PHONE_CONTACT.RAW_PHONE_NUMBER,
2732 HREL.PARTY_ID
2733 from HZ_CUST_ACCOUNT_ROLES HCAR,
2734 HZ_RELATIONSHIPS HREL,
2735 HZ_ORG_CONTACTS HOC,
2736 HZ_CONTACT_POINTS PHONE_CONTACT,
2737 HZ_PARTIES PER_CONTACT
2738 WHERE HCAR.CUST_ACCOUNT_ROLE_ID = p_contact_id
2739 AND HREL.PARTY_ID = HCAR.PARTY_ID
2740 AND HCAR.ROLE_TYPE = 'CONTACT'
2741 AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
2742 AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2743 AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2744 AND HREL.SUBJECT_TYPE = 'PERSON'
2745 AND HREL.DIRECTIONAL_FLAG = 'F'
2746 AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
2747 AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
2748 AND PHONE_CONTACT.OWNER_TABLE_ID(+) = HREL.PARTY_ID
2749 AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
2750 AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
2751 AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y';
2752
2753 cursor l_ship_to_site_ph_csr(p_owner_tbl_id IN NUMBER) is
2754 SELECT RAW_PHONE_NUMBER
2755 FROM HZ_CONTACT_POINTS
2756 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
2757 AND OWNER_TABLE_ID = p_owner_tbl_id
2758 AND CONTACT_POINT_TYPE = 'PHONE'
2759 AND PHONE_LINE_TYPE = 'GEN';
2760
2761 BEGIN
2762 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2763 --
2764 IF l_debug_on IS NULL
2765 THEN
2766 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2767 END IF;
2768 --
2769 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2770 IF l_debug_on THEN
2771 wsh_debug_sv.push(l_module_name);
2772 wsh_debug_sv.log (l_module_name, 'p_contact_id', p_contact_id);
2773 END IF;
2774
2775 open l_ship_to_site_contact_csr(p_contact_id);
2776 fetch l_ship_to_site_contact_csr into l_per_first_name,
2777 l_per_middle_name,
2778 l_per_last_name,
2779 x_per_ph_number,
2780 l_owner_table_id;
2781 close l_ship_to_site_contact_csr;
2782
2783 IF l_per_first_name IS NOT NULL THEN
2784 --{
2785 x_contact_person_name := l_per_first_name || ' ';
2786 --}
2787 END IF;
2788
2789 IF l_per_middle_name IS NOT NULL THEN
2790 --{
2791 x_contact_person_name := x_contact_person_name || l_per_middle_name || ' ';
2792 --}
2793 END IF;
2794
2795 IF l_per_last_name IS NOT NULL THEN
2796 --{
2797 x_contact_person_name := x_contact_person_name || l_per_last_name; --}
2798 END IF;
2799 IF l_debug_on THEN
2800 WSH_DEBUG_SV.log (L_MODULE_NAME,' Contact Person Phone Number ',
2801 x_per_ph_number);
2802 WSH_DEBUG_SV.log (L_MODULE_NAME,' l_owner_table_id ',
2803 l_owner_table_id);
2804 END IF;
2805
2806 IF x_per_ph_number IS NULL
2807 AND l_owner_table_id IS NOT NULL
2808 THEN
2809 --{
2810 open l_ship_to_site_ph_csr(l_owner_table_id);
2811 fetch l_ship_to_site_ph_csr into x_per_ph_number;
2812 close l_ship_to_site_ph_csr;
2813 --}
2814 END IF;
2815
2816
2817 IF l_debug_on THEN
2818 wsh_debug_sv.log (l_module_name, 'x_per_ph_number', x_per_ph_number);
2819 wsh_debug_sv.log (l_module_name, 'x_contact_person_name', x_contact_person_name);
2820 wsh_debug_sv.log (l_module_name, 'x_return_status', x_return_status);
2821 wsh_debug_sv.pop(l_module_name);
2822 END IF;
2823
2824 EXCEPTION
2825 WHEN OTHERS THEN
2826 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2827 IF l_debug_on THEN
2828 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2829 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2830 END IF;
2831 END get_name_number;
2832
2833 PROCEDURE get_ship_method_details(
2834 p_ship_method_code IN VARCHAR2,
2835 x_carrier_code OUT NOCOPY VARCHAR2,
2836 x_service_level OUT NOCOPY VARCHAR2,
2837 x_mode_of_transport OUT NOCOPY VARCHAR2,
2838 x_return_status OUT NOCOPY VARCHAR2 )
2839 IS
2840 --
2841 l_debug_on BOOLEAN;
2842 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Ship_Method_Details';
2843 --
2844 BEGIN
2845 --
2846 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2847 --
2848 IF l_debug_on IS NULL
2849 THEN
2850 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2851 END IF;
2852 --
2853 IF l_debug_on THEN
2854 WSH_DEBUG_SV.push(l_module_name);
2855 WSH_DEBUG_SV.log(l_module_name, 'p_ship_method_code', p_ship_method_code);
2856 END IF;
2857 --
2858 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2859
2860 select wc.freight_code, wcs.service_level, wcs.mode_of_transport
2861 into x_carrier_code, x_service_level, x_mode_of_transport
2862 from wsh_carrier_services wcs,
2863 wsh_carriers wc
2864 where wc.carrier_id = wcs.carrier_id
2865 and wcs.ship_method_code = p_ship_method_code;
2866
2867 --
2868 IF l_debug_on THEN
2869 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
2870 WSH_DEBUG_SV.pop(l_module_name);
2871 END IF;
2872 --
2873 EXCEPTION
2874 WHEN others THEN
2875 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2876 --
2877 IF l_debug_on THEN
2878 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2879 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2880 END IF;
2881 --
2882 END Get_Ship_Method_Details;
2883
2884 PROCEDURE get_batch_addr_info (
2885 p_partner_type IN VARCHAR2,
2886 p_batch_id IN NUMBER,
2887 x_partner_id OUT NOCOPY NUMBER,
2888 x_partner_name OUT NOCOPY VARCHAR2,
2889 x_address_id OUT NOCOPY NUMBER,
2890 x_address1 OUT NOCOPY VARCHAR2,
2891 x_address2 OUT NOCOPY VARCHAR2,
2892 x_address3 OUT NOCOPY VARCHAR2,
2893 x_address4 OUT NOCOPY VARCHAR2,
2894 x_city OUT NOCOPY VARCHAR2,
2895 x_country OUT NOCOPY VARCHAR2,
2896 x_county OUT NOCOPY VARCHAR2,
2897 x_postal_code OUT NOCOPY VARCHAR2,
2898 x_region OUT NOCOPY VARCHAR2,
2899 x_state OUT NOCOPY VARCHAR2,
2900 x_contact_id OUT NOCOPY NUMBER,
2901 x_contact_name OUT NOCOPY VARCHAR2,
2902 x_telephone OUT NOCOPY VARCHAR2,
2903 x_return_status OUT NOCOPY VARCHAR2 )
2904 IS
2905
2906 CURSOR c_ship_from_location(c_organization_id IN NUMBER, c_loc_id IN NUMBER)
2907 IS
2908 select organization_id,
2909 organization_name,
2910 wsh_location_id,
2911 address1,
2912 address2,
2913 address3,
2914 address4,
2915 city,
2916 country,
2917 county,
2918 postal_code,
2919 province,
2920 state
2921 from wsh_ship_from_org_locations_v
2922 where wsh_location_id = c_loc_id
2923 and organization_id = c_organization_id;
2924
2925 CURSOR c_cust_name(c_customer_id IN NUMBER)
2926 IS
2927 SELECT HP.PARTY_NAME
2928 FROM HZ_PARTIES HP,
2929 HZ_CUST_ACCOUNTS HCA
2930 WHERE HP.PARTY_ID = HCA.PARTY_ID
2931 AND HCA.CUST_ACCOUNT_ID = c_customer_id;
2932
2933 CURSOR c_cust_site_location(c_site_use_id IN NUMBER)
2934 IS
2935 select customer_id,
2936 customer_name,
2937 site_use_id,
2938 address1,
2939 address2,
2940 address3,
2941 address4,
2942 city,
2943 country,
2944 county,
2945 postal_code,
2946 province,
2947 state
2948 from wsh_customer_locations_v
2949 where site_use_id = c_site_use_id;
2950
2951 l_organization_id number;
2952 l_ship_from_location_id number;
2953 l_customer_id number;
2954 l_ship_to_site_use_id number;
2955 l_ship_to_contact_id number;
2956 l_invoice_to_site_use_id number;
2957 l_invoice_to_contact_id number;
2958 l_deliver_to_site_use_id number;
2959 l_deliver_to_contact_id number;
2960 l_return_status VARCHAR2(1);
2961
2962 --
2963 l_debug_on BOOLEAN;
2964 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Batch_Addr_Info';
2965 --
2966 BEGIN
2967 --
2968 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2969 --
2970 IF l_debug_on IS NULL
2971 THEN
2972 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2973 END IF;
2974 --
2975 IF l_debug_on THEN
2976 WSH_DEBUG_SV.push(l_module_name);
2977 WSH_DEBUG_SV.log(l_module_name, 'p_partner_type', p_partner_type);
2978 WSH_DEBUG_SV.log(l_module_name, 'p_batch_id', p_batch_id);
2979 END IF;
2980 --
2981 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2982
2983 select organization_id, ship_from_location_id, customer_id,
2984 ship_to_site_use_id, ship_to_contact_id,
2985 invoice_to_site_use_id, invoice_to_contact_id,
2986 deliver_to_site_use_id, deliver_to_contact_id
2987 into l_organization_id, l_ship_from_location_id, l_customer_id,
2988 l_ship_to_site_use_id, l_ship_to_contact_id,
2989 l_invoice_to_site_use_id, l_invoice_to_contact_id,
2990 l_deliver_to_site_use_id, l_deliver_to_contact_id
2991 from wsh_shipment_batches
2992 where batch_id = p_batch_id;
2993
2994 IF p_partner_type = 'ShipFrom' THEN
2995 OPEN c_ship_from_location(l_organization_id, l_ship_from_location_id);
2996 FETCH c_ship_from_location INTO
2997 x_partner_id,
2998 x_partner_name,
2999 x_address_id,
3000 x_address1,
3001 x_address2,
3002 x_address3,
3003 x_address4,
3004 x_city,
3005 x_country,
3006 x_county,
3007 x_postal_code,
3008 x_region,
3009 x_state;
3010 CLOSE c_ship_from_location;
3011 ELSIF p_partner_type = 'SoldTo' THEN
3012 OPEN c_cust_name (l_customer_id);
3013 FETCH c_cust_name INTO x_partner_name;
3014 CLOSE c_cust_name;
3015 x_partner_id := l_customer_id;
3016 ELSIF p_partner_type = 'ShipTo' THEN
3017 OPEN c_cust_site_location(l_ship_to_site_use_id);
3018 FETCH c_cust_site_location INTO
3019 x_partner_id,
3020 x_partner_name,
3021 x_address_id,
3022 x_address1,
3023 x_address2,
3024 x_address3,
3025 x_address4,
3026 x_city,
3027 x_country,
3028 x_county,
3029 x_postal_code,
3030 x_region,
3031 x_state;
3032 CLOSE c_cust_site_location;
3033
3034 IF l_ship_to_contact_id is not null THEN
3035 x_contact_id := l_ship_to_contact_id;
3036 get_name_number(
3037 p_contact_id => l_ship_to_contact_id,
3038 x_per_ph_number => x_telephone,
3039 x_contact_person_name => x_contact_name,
3040 x_return_status => l_return_status );
3041
3042 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3043 RAISE FND_API.G_EXC_ERROR;
3044 END IF;
3045 END IF;
3046 ELSIF p_partner_type = 'BillTo' THEN
3047 OPEN c_cust_site_location(l_invoice_to_site_use_id);
3048 FETCH c_cust_site_location INTO
3049 x_partner_id,
3050 x_partner_name,
3051 x_address_id,
3052 x_address1,
3053 x_address2,
3054 x_address3,
3055 x_address4,
3056 x_city,
3057 x_country,
3058 x_county,
3059 x_postal_code,
3060 x_region,
3061 x_state;
3062 CLOSE c_cust_site_location;
3063
3064 IF l_invoice_to_contact_id is not null THEN
3065 x_contact_id := l_invoice_to_contact_id;
3066 get_name_number(
3067 p_contact_id => l_invoice_to_contact_id,
3068 x_per_ph_number => x_telephone,
3069 x_contact_person_name => x_contact_name,
3070 x_return_status => l_return_status );
3071
3072 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3073 RAISE FND_API.G_EXC_ERROR;
3074 END IF;
3075 END IF;
3076 ELSIF p_partner_type = 'DeliverTo' THEN
3077 OPEN c_cust_site_location(l_deliver_to_site_use_id);
3078 FETCH c_cust_site_location INTO
3079 x_partner_id,
3080 x_partner_name,
3081 x_address_id,
3082 x_address1,
3083 x_address2,
3084 x_address3,
3085 x_address4,
3086 x_city,
3087 x_country,
3088 x_county,
3089 x_postal_code,
3090 x_region,
3091 x_state;
3092 CLOSE c_cust_site_location;
3093
3094 IF l_deliver_to_contact_id is not null THEN
3095 x_contact_id := l_deliver_to_contact_id;
3096 get_name_number(
3097 p_contact_id => l_deliver_to_contact_id,
3098 x_per_ph_number => x_telephone,
3099 x_contact_person_name => x_contact_name,
3100 x_return_status => l_return_status );
3101
3102 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3103 RAISE FND_API.G_EXC_ERROR;
3104 END IF;
3105 END IF;
3106 END IF;
3107
3108 --
3109 IF l_debug_on THEN
3110 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
3111 WSH_DEBUG_SV.pop(l_module_name);
3112 END IF;
3113 --
3114 EXCEPTION
3115 WHEN FND_API.G_EXC_ERROR THEN
3116 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3117 --
3118 IF l_debug_on THEN
3119 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3120 END IF;
3121 --
3122 WHEN others THEN
3123 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3124 --
3125 IF l_debug_on THEN
3126 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3127 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3128 END IF;
3129 --
3130 END Get_Batch_Addr_Info;
3131
3132
3133 PROCEDURE get_detail_line_info (
3134 p_reference_line_id IN NUMBER,
3135 x_line_number OUT NOCOPY NUMBER,
3136 x_line_quantity OUT NOCOPY VARCHAR2,
3137 x_line_quantity_uom OUT NOCOPY VARCHAR2,
3138 x_item_number OUT NOCOPY VARCHAR2,
3139 x_item_description OUT NOCOPY VARCHAR2,
3140 x_unit_selling_price OUT NOCOPY NUMBER,
3141 x_packing_instructions OUT NOCOPY VARCHAR2,
3142 x_shipping_instructions OUT NOCOPY VARCHAR2,
3143 x_request_date OUT NOCOPY DATE,
3144 x_schedule_date OUT NOCOPY DATE,
3145 x_shipment_priority_code OUT NOCOPY VARCHAR2,
3146 x_ship_tolerance_above OUT NOCOPY NUMBER,
3147 x_ship_tolerance_below OUT NOCOPY NUMBER,
3148 x_set_name OUT NOCOPY VARCHAR2,
3149 x_customer_item_number OUT NOCOPY VARCHAR2,
3150 x_cust_po_number OUT NOCOPY VARCHAR2,
3151 x_subinventory OUT NOCOPY VARCHAR2,
3152 x_return_status OUT NOCOPY VARCHAR2) IS
3153
3154 l_return_status VARCHAR2(1);
3155 l_line_set_id NUMBER;
3156 -- Bug 9234726: Added variables.
3157 l_req_qty_uom VARCHAR2(3);
3158 l_inv_item_id NUMBER;
3159
3160 --
3161 l_debug_on BOOLEAN;
3162 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Detail_Line_Info';
3163 --
3164 BEGIN
3165 --
3166 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3167 --
3168 IF l_debug_on IS NULL
3169 THEN
3170 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3171 END IF;
3172 --
3173 IF l_debug_on THEN
3174 WSH_DEBUG_SV.push(l_module_name);
3175 WSH_DEBUG_SV.log(l_module_name, 'p_reference_line_id', p_reference_line_id);
3176 END IF;
3177 --
3178 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3179
3180 select order_quantity_uom,
3181 ol.line_number,
3182 msik.concatenated_segments,
3183 msik.description,
3184 ol.unit_selling_price,
3185 ol.packing_instructions,
3186 ol.shipping_instructions,
3187 ol.request_date,
3188 ol.schedule_ship_date,
3189 ol.shipment_priority_code,
3190 ol.ship_tolerance_above,
3191 ol.ship_tolerance_below,
3192 set_name,
3193 customer_item_number,
3194 ol.cust_po_number,
3195 ol.subinventory,
3196 ol.ordered_quantity,
3197 ol.line_set_id,
3198 -- Bug 9234726: Querying Item Id and Item's Primary UOM
3199 msik.primary_uom_code,
3200 ol.inventory_item_id
3201 into x_line_quantity_uom,
3202 x_line_number,
3203 x_item_number,
3204 x_item_description,
3205 x_unit_selling_price,
3206 x_packing_instructions,
3207 x_shipping_instructions,
3208 x_request_date,
3209 x_schedule_date,
3210 x_shipment_priority_code,
3211 x_ship_tolerance_above,
3212 x_ship_tolerance_below,
3213 x_set_name,
3214 x_customer_item_number,
3215 x_cust_po_number,
3216 x_subinventory,
3217 x_line_quantity,
3218 l_line_set_id,
3219 l_req_qty_uom,
3220 l_inv_item_id
3221 from oe_order_lines_all ol,
3222 mtl_system_items_kfv msik,
3223 oe_sets,
3224 mtl_customer_items
3225 where ol.line_id = p_reference_line_id
3226 and ol.inventory_item_id = msik.inventory_item_id
3227 and ol.ship_from_org_id = msik.organization_id
3228 and ol.ship_set_id = set_id (+)
3229 and decode(ol.item_type_code, 'CUST', ol.ordered_item_id, null) = customer_item_id (+);
3230
3231 if (l_line_set_id is not null) then
3232 select sum(ol1.ordered_quantity)
3233 into x_line_quantity
3234 from oe_order_lines_all ol1
3235 where ol1.line_set_id = l_line_set_id;
3236 end if;
3237
3238 -- Bug 9234726: If Ordered UOM is different from Item's Primary UOM then
3239 -- calculate Unit Selling Price based on Item's Primary UOM.
3240 if ( x_line_quantity_uom <> l_req_qty_uom ) then
3241 --
3242 IF l_debug_on THEN
3243 WSH_DEBUG_SV.logmsg(l_module_name, 'Converting Unit Selling Price based on Primary UOM of Inventory Item');
3244 END IF;
3245 --
3246 x_unit_selling_price := ROUND(x_unit_selling_price * WSH_WV_UTILS.CONVERT_UOM(l_req_qty_uom, x_line_quantity_uom, 1, l_inv_item_id),2);
3247 end if;
3248 --
3249 IF l_debug_on THEN
3250 WSH_DEBUG_SV.logmsg(l_module_name, 'x_line_quantity '||x_line_quantity||' x_line_quantity_uom '||x_line_quantity_uom);
3251 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
3252 WSH_DEBUG_SV.pop(l_module_name);
3253 END IF;
3254 --
3255 EXCEPTION
3256 WHEN FND_API.G_EXC_ERROR THEN
3257 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3258 --
3259 IF l_debug_on THEN
3260 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3261 END IF;
3262 --
3263 WHEN others THEN
3264 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3265 --
3266 IF l_debug_on THEN
3267 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3268 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3269 END IF;
3270 --
3271 END get_detail_line_info;
3272
3273 END WSH_MAPPING_DATA;