[Home] [Help]
PACKAGE BODY: APPS.WSH_EXTERNAL_INTERFACE_SV
Source
1 PACKAGE BODY WSH_EXTERNAL_INTERFACE_SV AS
2 /* $Header: WSHEXINB.pls 120.11 2012/01/04 10:10:01 skanduku noship $ */
3
4 /*===========================================================================
5 | |
6 | PROCEDURE NAME Is_FTE_Installed |
7 | |
8 | DESCRIPTION This procedure checks if FTE is installed or not. |
9 | |
10 | |
11 | MODIFICATION HISTORY |
12 | |
13 | 02/20/02 Vijay Nandula Created |
14 | |
15 ============================================================================*/
16
17 --
18 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_EXTERNAL_INTERFACE_SV';
19 --
20
21
22 /*===========================================================================
23 | |
24 | PROCEDURE NAME Get_Warehouse_Type |
25 | |
26 | DESCRIPTION The procedure returns the warehouse type from the |
27 | inventory tables. Used to identify the instance is a |
28 | a Third Party Warehouse or not. |
29 | |
30 | MODIFICATION HISTORY |
31 | |
32 | 02/20/02 Vijay Nandula Created |
33 | |
34 ============================================================================*/
35
36 FUNCTION Get_Warehouse_Type ( p_organization_id IN NUMBER,
37 p_event_key IN VARCHAR2 DEFAULT NULL,
38 x_return_status OUT NOCOPY VARCHAR2,
39 p_delivery_id IN NUMBER DEFAULT NULL,
40 p_delivery_detail_id IN NUMBER DEFAULT NULL,
41 p_carrier_id IN NUMBER DEFAULT NULL,
42 p_ship_method_code IN VARCHAR2 DEFAULT NULL,
43 p_msg_display IN VARCHAR2 DEFAULT 'Y'
44 ) RETURN VARCHAR2
45 IS
46 cursor wh_flag_cur IS
47 select carrier_manifesting_flag,
48 distributed_organization_flag
49 from mtl_parameters
50 where organization_id= p_organization_id;
51
52 cursor valid_carrier_det_cur is
53 select distinct wc.manifesting_enabled_flag
54 from wsh_delivery_details wdd,
55 wsh_carrier_services wcs,
56 wsh_carriers wc,
57 wsh_new_deliveries wnd,
58 wsh_delivery_assignments_v wda
59 where wdd.delivery_detail_id = p_delivery_detail_id
60 and wdd.delivery_detail_id = wda.delivery_detail_id(+)
61 and wda.delivery_id = wnd.delivery_id(+)
62 and ( nvl(wnd.ship_method_code,wdd.ship_method_code) = wcs.ship_method_code
63 or nvl(wnd.carrier_id,wdd.carrier_id) = wcs.carrier_id
64 )
65 and wcs.carrier_id = wc.carrier_id;
66
67 cursor valid_carrier_del_cur is
68 select distinct wc.manifesting_enabled_flag
69 from wsh_new_deliveries wnd,
70 wsh_carrier_services wcs,
71 wsh_carriers wc
72 where wnd.delivery_id = p_delivery_id
73 and ( wnd.ship_method_code = wcs.ship_method_code
74 or wnd.carrier_id = wcs.carrier_id
75 )
76 and wcs.carrier_id = wc.carrier_id;
77
78 cursor c_valid_carrier is
79 select distinct wc.manifesting_enabled_flag
80 from wsh_carrier_services wcs,
81 wsh_carriers wc
82 where wcs.carrier_id=p_carrier_id
83 and wcs.carrier_id = wc.carrier_id;
84
85 cursor c_valid_smc is
86 select distinct wc.manifesting_enabled_flag
87 from wsh_carrier_services wcs,
88 wsh_carriers wc
89 where wcs.ship_method_code = p_ship_method_code
90 and wcs.carrier_id = wc.carrier_id;
91
92 l_warehouse_type VARCHAR2 (3);
93 l_tpw_flag VARCHAR2 (1);
94 l_cms_flag VARCHAR2 (1);
95 l_manifest_enabled_flag VARCHAR2 (1);
96 l_entity_name VARCHAR2 (100);
97 l_entity_id NUMBER;
98 l_otm_enabled_flag VARCHAR2 (1):= 'N';
99 wsh_org_event_key_null EXCEPTION;
100 wsh_ship_param_failed EXCEPTION;
101 l_shipping_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
102 --
103 --
104 l_debug_on BOOLEAN;
105 --
106 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_WAREHOUSE_TYPE';
107 l_otm_installed VARCHAR2(1) ;
108 --
109 BEGIN
110
111 --
112 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
113 --
114 IF l_debug_on IS NULL
115 THEN
116 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
117 END IF;
118 --
119 IF l_debug_on THEN
120 wsh_debug_sv.push(l_module_name,'Get_Warehouse_Type');
121 wsh_debug_sv.log (l_module_name,'Event Key', p_event_key);
122 wsh_debug_sv.log (l_module_name,'Organization id', p_organization_id);
123 wsh_debug_sv.log (l_module_name,'Delivery Id', p_delivery_id);
124 wsh_debug_sv.log (l_module_name,'Delivery Detail Id', p_delivery_detail_id);
125 wsh_debug_sv.log (l_module_name,'Carrier Id', p_carrier_id);
126 wsh_debug_sv.log (l_module_name,'Ship Method Code', p_ship_method_code);
127 END IF;
128
129 --R12.1.1 STANDALONE PROJECT
130 IF (WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D') THEN
131 -- Turn Off Distributed and CMS Modes if Standalone Mode is Set
132 l_warehouse_type := NULL;
133
134 ELSIF (p_event_key IS NOT NULL) THEN
135 IF l_debug_on THEN
136 wsh_debug_sv.log (l_module_name,'Event Key Not null', p_event_key);
137 END IF;
138
139 l_warehouse_type := SUBSTRB (p_event_key, 1, 3);
140 ELSIF ( p_organization_id IS NULL ) THEN
141 RAISE wsh_org_event_key_null;
142 ELSE --{
143 --bugfix 7190832
144 l_otm_installed := WSH_UTIL_CORE.GC3_Is_Installed;
145 --
146 IF l_otm_installed = 'Y' THEN
147
148 WSH_SHIPPING_PARAMS_PVT.Get(
149 p_organization_id => p_organization_id,
150 x_param_info => l_shipping_param_info,
151 x_return_status => x_return_status);
152
153 l_otm_enabled_flag := l_shipping_param_info.otm_enabled;
154
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.log(l_module_name,'After call to WSH_SHIPPING_PARAMS_PVT.Get x_return_status ',x_return_status);
157 WSH_DEBUG_SV.log(l_module_name,'l_shipping_param_info.otm_enabled',l_shipping_param_info.otm_enabled);
158 END IF;
159 --
160 IF (x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
161 Raise wsh_ship_param_failed;
162 END IF;
163 --
164 l_warehouse_type := NULL;
165 END IF ;
166 -- bugfix 7190832
167 IF nvl(l_otm_enabled_flag,'!') = 'N' THEN
168
169 OPEN wh_flag_cur;
170 FETCH wh_flag_cur INTO l_cms_flag, l_tpw_flag;
171 CLOSE wh_flag_cur;
172 IF l_debug_on THEN
173 wsh_debug_sv.log (l_module_name,'TPW Flag', l_tpw_flag);
174 wsh_debug_sv.log (l_module_name,'CMS Flag', l_cms_flag);
175 END IF;
176 IF ( NVL (l_tpw_flag, '!') = 'Y' ) THEN
177 l_warehouse_type := 'TPW';
178 -- TPW - Distributed Organization Changes
179 IF (FND_PROFILE.VALUE('WSH_SR_SOURCE') = 'B') THEN
180 l_warehouse_type := 'TW2';
181 IF l_debug_on THEN
182 wsh_debug_sv.log (l_module_name,'TW2 Flag', 'Y');
183 END IF;
184 END IF;
185 ELSIF ( NVL (l_cms_flag, '!') = 'Y' ) THEN
186 IF ( p_delivery_id IS NOT NULL ) THEN
187 l_entity_name := 'Delivery';
188 l_entity_id := p_delivery_id;
189 open valid_carrier_del_cur;
190 fetch valid_carrier_del_cur into l_manifest_enabled_flag;
191 close valid_carrier_del_cur;
192 ELSIF ( p_delivery_detail_id IS NOT NULL ) THEN
193 l_entity_name := 'Delivery Line';
194 l_entity_id := p_delivery_detail_id;
195 open valid_carrier_det_cur;
196 fetch valid_carrier_det_cur into l_manifest_enabled_flag;
197 close valid_carrier_det_cur;
198 ELSIF (p_carrier_id is NOT NULL) THEN
199 FOR cur IN c_valid_carrier LOOP
200 l_manifest_enabled_flag:=cur.manifesting_enabled_flag;
201 END LOOP;
202 ELSIF (p_ship_method_code is NOT NULL) THEN
203 FOR cur IN c_valid_smc LOOP
204 l_manifest_enabled_flag:=cur.manifesting_enabled_flag;
205 END LOOP;
206 ELSE
207 -- TPW - Distributed changes
208 l_manifest_enabled_flag := 'Y';
209 END IF;
210
211 IF l_debug_on THEN
212 wsh_debug_sv.log (l_module_name,'Manifest Enabled Flag', l_manifest_enabled_flag);
213 END IF;
214
215 IF ( nvl( l_manifest_enabled_flag, '!') = 'Y' ) THEN
216 l_warehouse_type := 'CMS';
217 ELSE
218 l_warehouse_type := NULL;
219 IF (p_msg_display = 'Y' ) THEN
220 FND_MESSAGE.Set_Name('WSH', 'WSH_CARR_NOT_MANIFEST_ENABLED');
221 FND_MESSAGE.Set_Token('ENTITY_NAME', l_entity_name);
222 FND_MESSAGE.Set_Token('ENTITY_ID', l_entity_id);
223 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
224 END IF;
225 END IF;
226 END IF ;
227 ELSE
228 l_warehouse_type := NULL;
229 END IF;
230 END IF; --}
231
232 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
233
234 IF l_debug_on THEN
235 wsh_debug_sv.log (l_module_name, 'Warehouse Type' , l_warehouse_type);
236 wsh_debug_sv.pop (l_module_name);
237 END IF;
238
239 RETURN l_warehouse_type;
240 EXCEPTION
241 WHEN wsh_org_event_key_null THEN
242 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
243 IF l_debug_on THEN
244 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_org_event_key_null exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
245 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_org_event_key_null');
246 END IF;
247 RETURN l_warehouse_type;
248 WHEN wsh_ship_param_failed THEN
249 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_ship_param_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
252 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_ship_param_failed');
253 END IF;
254 RETURN l_warehouse_type;
255
256 WHEN OTHERS THEN
257 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
260 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
261 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
262 END IF;
263 RETURN l_warehouse_type;
264 END Get_Warehouse_Type;
265
266
267 /*===========================================================================
268 | |
269 | PROCEDURE NAME Raise_Event |
270 | |
271 | DESCRIPTION This procedure raises an event in Work Flow. It raises |
272 | an appropriate procedure depending on the parameters |
273 | passed. |
274 | |
275 | MODIFICATION HISTORY |
276 | |
277 | 02/20/02 Vijay Nandula Created |
278 | |
279 ============================================================================*/
280
281 PROCEDURE Raise_Event ( P_txn_hist_record IN WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type,
282 P_Cbod_Status IN VARCHAR2,
283 x_return_status IN OUT NOCOPY VARCHAR2)
284 IS
285 l_event_name VARCHAR2 (120);
286 l_Event_Key VARCHAR2 (30);
287
288 l_Return_Status VARCHAR2 (1);
289 l_Transaction_Code VARCHAR2 (100);
290 l_Org_ID NUMBER;
291 l_Party_Site_ID NUMBER;
292 l_txns_id NUMBER;
293
294 l_msg_parameter_list WF_PARAMETER_LIST_T;
295 l_cbod_parameter_list WF_PARAMETER_LIST_T;
296 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
297 -- LSP PROJECT : Added client_id
298 CURSOR l_new_del_cur (l_name VARCHAR2) IS
299 SELECT Nvl(mcp.trading_partner_site_id, wnd.Initial_Pickup_Location_ID) Tranding_partner_site_id,
300 wnd.ultimate_dropoff_location_id, --Notification change heali
301 wnd.customer_id, --Notification change heali
302 wnd.client_id
303 FROM wsh_new_deliveries wnd,
304 mtl_client_parameters_v mcp
305 WHERE wnd.Name = l_name
306 AND wnd.client_id = mcp.client_id (+);
307 -- LSP PROJECT : end
308 CURSOR l_Batch_Cur (l_name VARCHAR2) IS --Fulfillment Batch XML Project
309 SELECT wdd.ship_from_location_id Tranding_partner_site_id,
310 wdd.ship_to_location_id,
311 wdd.customer_id
312 FROM wsh_delivery_details wdd,
313 wsh_shipment_batches wsb
314 WHERE wsb.name = l_name
315 AND wdd.shipment_batch_id = wsb.batch_id;
316
317 --Notification change heali
318 CURSOR get_location(p_location_id NUMBER) IS
319 SELECT ui_location_code
320 FROM wsh_locations
321 WHERE wsh_location_id=p_location_id;
322
323 CURSOR get_customer_name(p_customer_id NUMBER) IS
324 SELECT HP.PARTY_NAME
325 FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
326 WHERE HP.PARTY_ID = HCA.PARTY_ID
327 AND HP.PARTY_ID = p_customer_id;
328
329 l_del_name varchar2(240);
330 l_sf_location varchar2(240);
331 l_customer varchar2(240);
332 l_st_location varchar2(240);
333 l_subject varchar2(240);
334 l_body_text varchar2(240);--Fulfillment BAtch XML Project
335 l_org_code varchar2(240);--Fulfillment BAtch XML Project
336 l_ship_to_location_id number;
337 l_customer_id number;
338 --Notification change heali
339 --R12.1.1 STANDALONE PROJECT
340 l_wms_deployment_mode varchar2(1);
341 l_client_id NUMBER; -- LSP PROJECT
342
343 wsh_invalid_event_name EXCEPTION;
344 wsh_get_event_key_error EXCEPTION;
345 wsh_invalid_delivery_no EXCEPTION;
346 wsh_invalid_batch_no EXCEPTION; --Fulfillment Batch XML Project
347 wsh_update_history EXCEPTION;
348 --
349 l_debug_on BOOLEAN;
350 --
351 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_EVENT';
352 --
353 BEGIN
354 --
355 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
356 --
357 IF l_debug_on IS NULL
358 THEN
359 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
360 END IF;
361 --
362 IF l_debug_on THEN
363 wsh_debug_sv.push(l_module_name);
364 WSH_DEBUG_SV.log(l_module_name,'transaction_id',P_txn_hist_record.transaction_id);
365 WSH_DEBUG_SV.log(l_module_name,'document_type',P_txn_hist_record.document_type);
366 WSH_DEBUG_SV.log(l_module_name,'document_direction',P_txn_hist_record.document_direction);
367 WSH_DEBUG_SV.log(l_module_name,'document_number',P_txn_hist_record.document_number);
368 WSH_DEBUG_SV.log(l_module_name,'orig_document_number',P_txn_hist_record.orig_document_number);
369 WSH_DEBUG_SV.log(l_module_name,'entity_number',P_txn_hist_record.entity_number);
370 WSH_DEBUG_SV.log(l_module_name,'entity_type',P_txn_hist_record.entity_type);
371 WSH_DEBUG_SV.log(l_module_name,'trading_partner_id',P_txn_hist_record.trading_partner_id);
372 WSH_DEBUG_SV.log(l_module_name,'action_type',P_txn_hist_record.action_type);
373 WSH_DEBUG_SV.log(l_module_name,'transaction_status',P_txn_hist_record.transaction_status);
374 WSH_DEBUG_SV.log(l_module_name,'ecx_message_id',P_txn_hist_record.ecx_message_id);
375 WSH_DEBUG_SV.log(l_module_name,'event_name',P_txn_hist_record.event_name);
376 WSH_DEBUG_SV.log(l_module_name,'event_key',P_txn_hist_record.event_key);
377 WSH_DEBUG_SV.log(l_module_name,'item_type',P_txn_hist_record.item_type);
378 WSH_DEBUG_SV.log(l_module_name,'internal_control_number',P_txn_hist_record.internal_control_number);
379 WSH_DEBUG_SV.log(l_module_name,'client_code',P_txn_hist_record.client_code);
380 END IF;
381 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
382
383 l_txn_hist_record := P_txn_hist_record;
384
385 -- Get the event name from the Transaction History Table.
386 l_event_name := l_txn_hist_record.Event_Name;
387
388 -- Check if the event name is valid or not.
389
390 IF ( l_event_name NOT IN ('oracle.apps.wsh.sup.ssro',
391 'oracle.apps.wsh.sup.ssai',
392 'oracle.apps.wsh.tpw.ssri',
393 'oracle.apps.wsh.tpw.ssao',
394 'oracle.apps.wsh.tpw.spwf',
395 'oracle.apps.wsh.tpw.scbod',
396 'oracle.apps.wsh.batch.bsro', --Fulfillment Batch XML Project
397 --R12.1.1 STANDALONE PROJECT
398 'oracle.apps.wsh.standalone.ssri',
399 'oracle.apps.wsh.standalone.ssao',
400 'oracle.apps.wsh.standalone.scbod',
401 'oracle.apps.wsh.standalone.spwf',
402 'ORACLE.APPS.FTE.SSNO.CONFIRM') ) THEN
403 RAISE wsh_invalid_event_name;
404 END IF;
405
406 -- Transaction Code is the last 4 or 5 letters after the dot from the Event Name.
407 -- Eg: 'SSRO', 'SSAI' etc
408 l_Transaction_Code := UPPER (SUBSTRB (l_event_name, INSTRB(l_Event_Name, '.', -1) + 1));
409
410 IF l_debug_on THEN
411 wsh_debug_sv.log (l_module_name, 'Transaction Code' , l_Transaction_Code);
412 END IF;
413
414 -- Get the Event Key for Raising an Event.
415 --bmos k proj
416 IF ( l_txn_hist_record.Event_Key IS NULL ) THEN
417 WSH_TRANSACTIONS_UTIL.Get_Event_Key ( l_txn_hist_record.Item_Type,
418 l_txn_hist_record.Orig_Document_Number,
419 l_txn_hist_record.Trading_Partner_ID,
420 l_txn_hist_record.Event_Name,
421 l_txn_hist_record.entity_number,
422 l_Event_Key,
423 l_Return_Status );
424
425 IF l_debug_on THEN
426 wsh_debug_sv.log (l_module_name, 'Return status after get_event_key' , l_Return_Status);
427 END IF;
428
429 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
430 RAISE wsh_get_event_key_error;
431 END IF;
432 -- Assign the value to the Transaction History record.
433 l_txn_hist_record.Event_Key := l_Event_Key;
434 ELSE
435 l_Event_Key := l_txn_hist_record.Event_Key;
436 END IF;
437
438 --R12.1.1 STANDALONE PROJECT
439 l_wms_deployment_mode := WMS_DEPLOY.WMS_DEPLOYMENT_MODE;
440
441 IF l_debug_on THEN
442 wsh_debug_sv.log (l_module_name, 'Event Key' , l_Event_Key);
443 wsh_debug_sv.log (l_module_name, 'Trading partner ID ' , l_txn_hist_record.Trading_Partner_ID);
444 wsh_debug_sv.log (l_module_name, 'Entity Number ' , l_txn_hist_record.Entity_Number);
445 wsh_debug_sv.log (l_module_name, 'WMS Deployment Mode ' , l_wms_deployment_mode);
446 END IF;
447
448 l_client_id := NULL; -- LSP PROJECT
449 IF ( l_Transaction_Code IN ('SSRO', 'SSAO') ) THEN --{
450 -- LSP PROJECT : Changed the cursor here.
451 --OPEN l_New_Del_Cur (l_txn_hist_record.Trading_Partner_ID, l_txn_hist_record.entity_number);
452 OPEN l_New_Del_Cur (l_txn_hist_record.entity_number);
453 FETCH l_New_Del_Cur INTO l_Party_Site_ID,l_ship_to_location_id,l_customer_id,l_client_id;
454 IF ( l_New_Del_Cur % NOTFOUND ) THEN
455 CLOSE l_New_Del_Cur;
456 RAISE wsh_invalid_delivery_no;
457 END IF;
458 CLOSE l_New_Del_Cur;
459 IF l_debug_on THEN
460 wsh_debug_sv.log (l_module_name, 'Client_id ', l_client_id);
461 wsh_debug_sv.log (l_module_name, 'Party Site ID ', l_Party_Site_ID);
462 END IF;
463 ELSIF ( l_Transaction_Code IN ('BSRO') ) THEN --Fulfillment Batch XML Project
464 OPEN l_Batch_Cur (l_txn_hist_record.entity_number);
465 FETCH l_Batch_Cur INTO l_Party_Site_ID,l_ship_to_location_id,l_customer_id;
466 IF ( l_Batch_Cur % NOTFOUND ) THEN
467 CLOSE l_Batch_Cur;
468 RAISE wsh_invalid_batch_no;
469 END IF;
470 CLOSE l_Batch_Cur;
471 IF l_debug_on THEN
472 wsh_debug_sv.log (l_module_name, 'Party Site ID ', l_Party_Site_ID);
473 END IF;
474 END IF; --}
475
476 IF ( l_Transaction_Code in ('SSRO', 'SSAO', 'BSRO') ) THEN --{ --Fulfillment Batch XML Project (Added BSRO)
477 -- Generate the document number for outgoing documents.
478 SELECT WSH_DOCUMENT_NUMBER_S.NEXTVAL
479 INTO l_txn_hist_record.Document_Number
480 FROM dual;
481 END IF; --}
482
483
484 IF ( l_Transaction_Code IN ('SSRO', 'SSAO', 'BSRO') ) THEN --{ --Fulfillment Batch XML Project (Added BSRO)
485 -- LSP PROJECT : For LSP mode send party type as 'C' (Customer)
486 IF (l_wms_deployment_mode = 'L' AND l_client_id IS NOT NULL) THEN
487 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_ID',
488 p_value => l_txn_hist_record.Trading_Partner_ID, -- LSP PROJECT Commented.
489 p_parameterlist => l_msg_parameter_list);
490 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_TYPE',
491 p_value => 'C',
492 p_parameterlist => l_msg_parameter_list);
493 ELSE
494 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_ID',
495 p_value => l_Party_Site_ID,
496 p_parameterlist => l_msg_parameter_list);
497 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_TYPE',
498 p_value => 'I',
499 p_parameterlist => l_msg_parameter_list);
500 END IF;
501 -- LSP PROJECT : end
502 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_SITE_ID',
503 p_value => l_Party_Site_ID,
504 p_parameterlist => l_msg_parameter_list);
505
506 WF_EVENT.AddParameterToList (p_name => 'ECX_DOCUMENT_ID',
507 p_value => l_txn_hist_record.Document_Number,
508 p_parameterlist => l_msg_parameter_list);
509
510 WF_EVENT.AddParameterToList (p_name => 'USER_ID',
511 p_value => FND_GLOBAL.USER_ID,
512 p_parameterlist => l_msg_parameter_list);
513 --
514 WF_EVENT.AddParameterToList (p_name => 'APPLICATION_ID',
515 p_value => FND_GLOBAL.RESP_APPL_ID,
516 p_parameterlist => l_msg_parameter_list);
517 --
518 WF_EVENT.AddParameterToList (p_name => 'RESPONSIBILITY_ID',
519 p_value => FND_GLOBAL.RESP_ID,
520 p_parameterlist => l_msg_parameter_list);
521
522 IF ( l_txn_hist_record.document_type = 'SR' ) THEN --{
523 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_TYPE',
524 p_value => 'FTE',
525 p_parameterlist => l_msg_parameter_list);
526
527 --Notification change heali
528 l_del_name:= l_txn_hist_record.entity_number;
529
530 IF (instrb(l_Event_Key,'TPW') <> 0 ) THEN --{
531 IF (p_txn_hist_record.action_type='A' ) THEN
532 FND_MESSAGE.SET_NAME('WSH', 'WSH_SR_TPW_NOTIFY');
533 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
534 l_subject := FND_MESSAGE.get;
535 ELSE
536 FND_MESSAGE.SET_NAME('WSH', 'WSH_SRC_TPW_NOTIFY');
537 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
538 l_subject := FND_MESSAGE.get;
539 END IF;
540 ELSIF (instrb(l_Event_Key,'CMS') <> 0) THEN --}{
541 IF (p_txn_hist_record.action_type='A' ) THEN
542 FND_MESSAGE.SET_NAME('WSH', 'WSH_SR_CMS_NOTIFY');
543 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
544 l_subject := FND_MESSAGE.get;
545 ELSE
546 FND_MESSAGE.SET_NAME('WSH', 'WSH_SRC_CMS_NOTIFY');
547 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
548 l_subject := FND_MESSAGE.get;
549 END IF;
550 --Fulfillment BAtch XML Project
551 ELSIF (instrb(l_Event_Key,'TW2') <> 0) THEN
552 FND_MESSAGE.SET_NAME('WSH', 'WSH_SR_BATCH_NOTIFY');
553 FND_MESSAGE.SET_TOKEN('BATCH_NAME', l_del_name);
554 l_subject := FND_MESSAGE.get;
555 select organization_code into l_org_code from mtl_parameters where organization_id = l_txn_hist_record.Trading_Partner_ID;
556 FND_MESSAGE.SET_NAME('WSH', 'WSH_SR_BATCH_NOTIFY_BODY');
557 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE', l_org_code);
558 FND_MESSAGE.SET_TOKEN('ITEM_KEY', l_Event_Key);
559 l_body_text := FND_MESSAGE.get;
560 /*IF l_debug_on THEN
561 wsh_debug_sv.log (l_module_name, 'l_body_text' , l_body_text);
562 END IF;*/
563 WF_EVENT.AddParameterToList (p_name => 'BODY_TEXT',
564 p_value => l_body_text,
565 p_parameterlist => l_msg_parameter_list);
566 END IF; --}
567
568 /*IF l_debug_on THEN
569 wsh_debug_sv.log (l_module_name, 'l_subject' , l_subject);
570 END IF;*/
571
572 WF_EVENT.AddParameterToList (p_name => 'SUBJECT',
573 p_value => l_subject,
574 p_parameterlist => l_msg_parameter_list);
575
576
577 IF (wf_core.translate('WF_HEADER_ATTR') = 'Y' ) THEN --{
578
579 OPEN get_location(l_Party_Site_ID);
580 FETCH get_location INTO l_sf_location;
581 CLOSE get_location;
582
583 OPEN get_location(l_ship_to_location_id);
584 FETCH get_location INTO l_st_location;
585 CLOSE get_location;
586
587 OPEN get_customer_name(l_customer_id);
588 FETCH get_customer_name INTO l_customer;
589 CLOSE get_customer_name;
590
591 IF l_debug_on THEN
592 wsh_debug_sv.log (l_module_name, 'l_del_name' ,l_del_name );
593 wsh_debug_sv.log (l_module_name, 'l_sf_location' ,l_sf_location );
594 wsh_debug_sv.log (l_module_name, 'l_customer' ,l_customer );
595 wsh_debug_sv.log (l_module_name, 'l_st_location' ,l_st_location);
596 END IF;
597
598 WF_EVENT.AddParameterToList (p_name => 'DEL_NAME',
599 p_value => l_del_name,
600 p_parameterlist => l_msg_parameter_list);
601
602 WF_EVENT.AddParameterToList (p_name => 'SF_LOCATION',
603 p_value => l_sf_location,
604 p_parameterlist => l_msg_parameter_list);
605
606 WF_EVENT.AddParameterToList (p_name => 'CUSTOMER',
607 p_value => l_customer,
608 p_parameterlist => l_msg_parameter_list);
609
610 WF_EVENT.AddParameterToList (p_name => 'ST_LOCATION',
611 p_value => l_st_location,
612 p_parameterlist => l_msg_parameter_list);
613 END IF; --}
614 --Notification change heali
615
616 ELSE --}{
617 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_TYPE',
618 p_value => 'WSH',
619 p_parameterlist => l_msg_parameter_list);
620 END IF; --}
621
622 --R12.1.1 STANDALONE PROJECT
623 -- LSP PROJECT : For LSP pass transation sub type as SSNO-LSP
624 IF (l_wms_deployment_mode = 'L' AND l_client_id IS NOT NULL) THEN
625 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_SUBTYPE',
626 p_value => 'SSNO-LSP',
627 p_parameterlist => l_msg_parameter_list);
628 ELSIF (l_wms_deployment_mode = 'D') THEN
629 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_SUBTYPE',
630 p_value => 'SSNO-STND',
631 p_parameterlist => l_msg_parameter_list);
632 ELSE
633 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_SUBTYPE',
634 p_value => 'SSNO',
635 p_parameterlist => l_msg_parameter_list);
636 END IF;
637
638 WF_EVENT.AddParameterToList (p_name => 'USER',
639 p_value => FND_GLOBAL.user_name,
640 p_parameterlist => l_msg_parameter_list);
641 WF_EVENT.AddParameterToList (p_name => 'ECX_PARAMETER1',
642 p_value => l_txn_hist_record.Action_Type,
643 p_parameterlist => l_msg_parameter_list);
644 ELSIF ( l_Transaction_Code = 'SCBOD' ) THEN --}
645 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_TYPE',
646 p_value => 'ECX',
647 p_parameterlist => l_Cbod_parameter_list);
648 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_SUBTYPE',
649 p_value => 'CBODO',
650 p_parameterlist => l_Cbod_parameter_list);
651 WF_EVENT.AddParameterToList (p_name => 'ECX_DOCUMENT_ID',
652 p_value => l_txn_hist_record.Internal_Control_Number,
653 p_parameterlist => l_Cbod_parameter_list);
654
655 -- LSP PROJECT : For LSP mode send party type as 'C' (Customer)
656 IF (l_wms_deployment_mode = 'L' OR l_txn_hist_record.client_code IS NOT NULL) THEN
657 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_TYPE',
658 p_value => 'C',
659 p_parameterlist => l_Cbod_parameter_list);
660 ELSE
661 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_TYPE',
662 p_value => 'I',
663 p_parameterlist => l_Cbod_parameter_list);
664 END IF;
665 WF_EVENT.AddParameterToList (p_name => 'CONFIRM_STATUSLVL',
666 p_value => P_Cbod_Status,
667 p_parameterlist => l_Cbod_parameter_list);
668 ELSIF ( l_Transaction_Code = 'CONFIRM' ) THEN
669 WF_EVENT.AddParameterToList (p_name => 'PARAMETER6',
670 p_value => P_Cbod_Status,
671 p_parameterlist => l_Cbod_parameter_list);
672 END IF;
673
674 --R12.1.1 STANDALONE PROJECT
675 IF ( l_event_name NOT IN ('oracle.apps.wsh.tpw.scbod' ,'ORACLE.APPS.FTE.SSNO.CONFIRM','oracle.apps.wsh.tpw.spwf', 'oracle.apps.wsh.standalone.scbod', 'oracle.apps.wsh.standalone.spwf')) THEN
676
677 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
678 l_txns_id,
679 l_return_status );
680
681 IF l_debug_on THEN
682 wsh_debug_sv.log (l_module_name, 'Transaction History return status ' , l_Return_Status);
683 wsh_debug_sv.log (l_module_name, 'Transaction History ID' , l_txns_id);
684 END IF;
685
686 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
687 RAISE wsh_update_history;
688 END IF;
689 -- Commit the data into the Transaction History table for the views.
690 COMMIT;
691 END IF;
692
693 IF l_debug_on THEN
694 wsh_debug_sv.log (l_module_name, 'Before Raising Event');
695 END IF;
696
697 IF ( l_Transaction_Code IN ('SSRO', 'SSAO', 'BSRO') ) THEN --Fulfillment Batch XML Project (Added BSRO)
698 WF_EVENT.raise ( p_event_name => l_event_name,
699 p_event_key => l_Event_Key,
700 p_parameters => l_msg_parameter_list );
701 ELSIF ( l_Transaction_Code IN ('SCBOD', 'CONFIRM') ) THEN
702 WF_EVENT.raise ( p_event_name => l_event_name,
703 p_event_key => l_Event_Key,
704 p_parameters => l_Cbod_parameter_list );
705 ELSE
706 WF_EVENT.raise ( p_event_name => l_event_name,
707 p_event_key => l_Event_Key );
708 END IF;
709
710 IF l_debug_on THEN
711 wsh_debug_sv.log (l_module_name, 'End of Raising Event');
712 wsh_debug_sv.pop(l_module_name);
713 END IF;
714 EXCEPTION
715 WHEN wsh_invalid_event_name THEN
716 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
717 IF l_debug_on THEN
718 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_event_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
719 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_event_name');
720 END IF;
721
722 WHEN wsh_get_event_key_error THEN
723 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
724 IF l_debug_on THEN
725 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_event_key_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
726 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_event_key_error');
727 END IF;
728
729 WHEN wsh_invalid_delivery_no THEN
730 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
731 IF l_debug_on THEN
732 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_no exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
733 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_no');
734 END IF;
735
736 WHEN wsh_invalid_batch_no THEN --Fulfillment Batch XML Project
737 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_batch_no exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
740 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_batch_no');
741 END IF; --
742
743 WHEN wsh_update_history THEN
744 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
745 IF l_debug_on THEN
746 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
747 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
748 END IF;
749
750 WHEN OTHERS THEN
751 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
752 IF l_debug_on THEN
753 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
754 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
755 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
756 END IF;
757 END Raise_Event;
758
759
760 PROCEDURE Validate_Item ( p_concatenated_segments IN VARCHAR2,
761 p_organization_id IN NUMBER,
762 x_inventory_item_id OUT NOCOPY VARCHAR2,
763 x_return_status OUT NOCOPY VARCHAR2
764 )
765
766 IS
767 cursor get_item_id_cur is
768 select inventory_item_id
769 from mtl_system_items_kfv
770 where concatenated_segments = p_concatenated_segments
771 and organization_id = p_organization_id;
772 --
773 l_debug_on BOOLEAN;
774 --
775 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ITEM';
776 --
777 BEGIN
778 --
779 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
780 --
781 IF l_debug_on IS NULL
782 THEN
783 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
784 END IF;
785 --
786 IF l_debug_on THEN
787 wsh_debug_sv.push(l_module_name);
788 wsh_debug_sv.log (l_module_name, 'Item Name ', p_concatenated_segments);
789 wsh_debug_sv.log (l_module_name, 'Org Id ', p_organization_id);
790 END IF;
791
792 IF ( p_concatenated_segments is not null and p_organization_id is not null ) THEN
793 open get_item_id_cur;
794 Fetch get_item_id_cur into x_inventory_item_id;
795
796 IF get_item_id_cur%NOTFOUND THEN
797 x_return_status := wsh_util_core.g_ret_sts_error;
798 ELSE
799 x_return_status := wsh_util_core.g_ret_sts_success;
800 END IF;
801 close get_item_id_cur;
802 ELSE
803 x_return_status := wsh_util_core.g_ret_sts_success;
804 END IF;
805
806 IF l_debug_on THEN
807 wsh_debug_sv.pop (l_module_name);
808 END IF;
809 EXCEPTION
810 WHEN OTHERS THEN
811 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
812 IF get_item_id_cur%ISOPEN THEN
813 close get_item_id_cur;
814 END IF;
815 IF l_debug_on THEN
816 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
817 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
818 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
819 END IF;
820 END Validate_Item;
821
822 PROCEDURE Validate_Ship_To ( p_customer_name IN VARCHAR2,
823 p_location IN VARCHAR2,
824 x_customer_id OUT NOCOPY NUMBER,
825 x_location_id OUT NOCOPY NUMBER,
826 x_return_status OUT NOCOPY VARCHAR2,
827 p_site_use_code IN VARCHAR2 DEFAULT 'SHIP_TO',
828 x_site_use_id OUT NOCOPY NUMBER,
829 p_org_id IN NUMBER DEFAULT NULL
830 )
831 IS
832
833 -- Patchset I : Locations Project. kvenkate.
834 l_loc_rec WSH_MAP_LOCATION_REGION_PKG.loc_rec_type;
835 l_location_id NUMBER;
836 l_return_status VARCHAR2(1);
837
838 cursor get_loc_id_cur is
839 SELECT HL.LOCATION_ID,
840 HCA.CUST_ACCOUNT_ID,
841 HCSU.SITE_USE_ID
842 FROM HZ_CUST_ACCOUNTS HCA,
843 HZ_PARTIES HP,
844 HZ_CUST_SITE_USES_ALL HCSU,
845 HZ_CUST_ACCT_SITES_ALL HCAS,
846 -- ORG_ORGANIZATION_DEFINITIONS OOD,
847 HZ_PARTY_SITES HPS,
848 HZ_LOCATIONS HL
849 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
850 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
851 AND HCSU.SITE_USE_CODE IN ( p_site_use_code, 'SHIP_TO')
852 AND HCSU.STATUS = 'A'
853 --AND HCAS.ORG_ID = HCSU.ORG_ID
854 --AND HCSU.ORG_ID = OOD.OPERATING_UNIT
855 --AND OOD.ORGANIZATION_ID = p_organization_id
856 AND HPS.LOCATION_ID = HL.LOCATION_ID
857 AND HCSU.LOCATION = p_location
858 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
859 AND HP.PARTY_ID = HCA.PARTY_ID
860 AND HCAS.ORG_ID = NVL(p_org_id , HCAS.ORG_ID)
861 AND HP.PARTY_NAME = p_customer_name
862 ; --bmso
863
864 --bug 3920178 {
865 --use related customer's location
866 CURSOR c_rel_cust_loc_cur IS
867 SELECT HPS.LOCATION_ID,
868 HCA.CUST_ACCOUNT_ID,
869 HCSU.SITE_USE_ID
870 FROM HZ_CUST_SITE_USES_ALL HCSU,
871 HZ_CUST_ACCT_SITES_ALL HCAS,
872 HZ_PARTY_SITES HPS,
873 HZ_CUST_ACCOUNTS HCA,
874 HZ_CUST_ACCT_RELATE_ALL HCAR,
875 HZ_PARTIES HP
876 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
877 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
878 AND HCAS.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
879 AND HCSU.SITE_USE_CODE IN (p_site_use_code, 'SHIP_TO')
880 AND HCSU.STATUS = 'A'
881 AND HCAS.STATUS = 'A'
882 AND HCA.STATUS = 'A'
883 AND HCSU.location = p_location
884 AND HCA.CUST_ACCOUNT_ID = HCAR.RELATED_CUST_ACCOUNT_ID
885 AND HCAR.SHIP_TO_FLAG = 'Y'
886 AND NVL(HCAS.ORG_ID, -999) = NVL(HCSU.ORG_ID , -999)
887 AND HCAS.ORG_ID = NVL(p_org_id , HCAS.ORG_ID)
888 AND HP.PARTY_ID = HCA.PARTY_ID
889 AND HP.PARTY_NAME = p_customer_name;
890
891 --bug 3920178 }
892
893
894 --
895 l_debug_on BOOLEAN;
896 --
897 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SHIP_TO';
898 --
899 BEGIN
900 --
901 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
902 --
903 IF l_debug_on IS NULL
904 THEN
905 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
906 END IF;
907 --
908 IF l_debug_on THEN
909 wsh_debug_sv.push(l_module_name, 'Validate_Ship_To');
910 wsh_debug_sv.log (l_module_name, 'Customer Name ', p_customer_name);
911 wsh_debug_sv.log (l_module_name, 'Location ', p_location);
912 wsh_debug_sv.log (l_module_name, 'Site Use Code ', p_site_use_code);
913 wsh_debug_sv.log (l_module_name, 'operating unit ', p_org_id);
914 END IF;
915
916 IF ( p_customer_name is not null and p_location is not null ) THEN
917 IF l_debug_on THEN
918 wsh_debug_sv.logmsg(l_module_name, 'Using get_loc_id_cur');
919 END IF;
920 open get_loc_id_cur;
921 Fetch get_loc_id_cur into l_location_id, x_customer_id, x_site_use_id;
922
923 IF get_loc_id_cur%NOTFOUND THEN
924 --{ bug 3920178 begin
925 IF l_debug_on THEN
926 wsh_debug_sv.logmsg(l_module_name, 'Using c_rel_cust_loc_cur');
927 END IF;
928 OPEN c_rel_cust_loc_cur;
929 FETCH c_rel_cust_loc_cur INTO l_location_id, x_customer_id, x_site_use_id;
930
931 IF c_rel_cust_loc_cur%NOTFOUND THEN
932 l_location_id := NULL;
933 END IF;
934
935 close c_rel_cust_loc_cur;
936 --}
937 END IF;
938 close get_loc_id_cur;
939
940 IF l_debug_on THEN
941 wsh_debug_sv.log(l_module_name, 'l_location_id', l_location_id);
942 END IF;
943
944 IF l_location_id IS NULL THEN
945 --bug 3920178 end
946 raise fnd_api.g_exc_error;
947 ELSE --{
948 -- Patchset I : Locations Project. kvenkate.
949 -- Call Transfer Location API
950
951 IF l_debug_on THEN
952 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MAP_LOCATION_REGION_PKG.TRANSFER_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
953 END IF;
954
955 WSH_MAP_LOCATION_REGION_PKG.Transfer_Location(
956 p_source_type => 'HZ',
957 p_source_location_id => l_location_id,
958 p_online_region_mapping => FALSE,
959 p_transfer_location => TRUE,
960 x_loc_rec => l_loc_rec,
961 x_return_status => l_return_status);
962
963 -- Success or Warning to be treated as success
964 -- Since warning of transfer location not to be treated as invalid ship to
965 IF l_return_status NOT IN(wsh_util_core.g_ret_sts_success, wsh_util_core.g_ret_sts_warning) THEN
966 raise fnd_api.g_exc_error;
967 END IF;
968 x_location_id := l_loc_rec.WSH_LOCATION_ID;
969 x_return_status := wsh_util_core.g_ret_sts_success;
970 END IF; --}
971 -- close get_loc_id_cur; bug 3920178
972 ELSE
973 x_return_status := wsh_util_core.g_ret_sts_success;
974 END IF;
975
976 IF l_debug_on THEN
977 wsh_debug_sv.log(l_module_name, 'Location Id', x_location_id);
978 wsh_debug_sv.log(l_module_name, 'Customer Id', x_customer_id);
979 wsh_debug_sv.log(l_module_name, 'Site Use Id', x_site_use_id);
980 wsh_debug_sv.pop (l_module_name);
981 END IF;
982 EXCEPTION
983 WHEN fnd_api.g_exc_error THEN
984 x_return_status := wsh_util_core.g_ret_sts_error;
985 IF get_loc_id_cur%ISOPEN THEN
986 close get_loc_id_cur;
987 END IF;
988 IF l_debug_on THEN
989 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
990 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
991 END IF;
992 WHEN OTHERS THEN
993 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
994 IF get_loc_id_cur%ISOPEN THEN
995 close get_loc_id_cur;
996 END IF;
997 IF l_debug_on THEN
998 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
999 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1000 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1001 END IF;
1002
1003 END Validate_Ship_To;
1004
1005
1006 END WSH_EXTERNAL_INTERFACE_SV;