[Home] [Help]
PACKAGE BODY: APPS.WSH_EXTERNAL_INTERFACE_SV
Source
1 PACKAGE BODY WSH_EXTERNAL_INTERFACE_SV AS
2 /* $Header: WSHEXINB.pls 120.3.12010000.2 2008/08/04 12:30:40 suppal ship $ */
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 END IF;
126
127 IF (p_event_key IS NOT NULL) THEN
128 IF l_debug_on THEN
129 wsh_debug_sv.log (l_module_name,'Event Key Not null', p_event_key);
130 END IF;
131
132 l_warehouse_type := SUBSTRB (p_event_key, 1, 3);
133 ELSIF ( p_organization_id IS NULL ) THEN
134 RAISE wsh_org_event_key_null;
135 ELSE
136 --bugfix 7190832
137 l_otm_installed := WSH_UTIL_CORE.GC3_Is_Installed;
138 --
139 IF l_otm_installed = 'Y' THEN
140
141 WSH_SHIPPING_PARAMS_PVT.Get(
142 p_organization_id => p_organization_id,
143 x_param_info => l_shipping_param_info,
144 x_return_status => x_return_status);
145
146 l_otm_enabled_flag := l_shipping_param_info.otm_enabled;
147
148 IF l_debug_on THEN
149 WSH_DEBUG_SV.log(l_module_name,'After call to WSH_SHIPPING_PARAMS_PVT.Get x_return_status ',x_return_status);
150 WSH_DEBUG_SV.log(l_module_name,'l_shipping_param_info.otm_enabled',l_shipping_param_info.otm_enabled);
151 END IF;
152 --
153 IF (x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
154 Raise wsh_ship_param_failed;
155 END IF;
156 --
157 l_warehouse_type := NULL;
158 END IF ;
159 -- bugfix 7190832
160 IF nvl(l_otm_enabled_flag,'!') = 'N' THEN
161
162 OPEN wh_flag_cur;
163 FETCH wh_flag_cur INTO l_cms_flag, l_tpw_flag;
164 CLOSE wh_flag_cur;
165 IF l_debug_on THEN
166 wsh_debug_sv.log (l_module_name,'TPW Flag', l_tpw_flag);
167 wsh_debug_sv.log (l_module_name,'CMS Flag', l_cms_flag);
168 END IF;
169 IF ( NVL (l_tpw_flag, '!') = 'Y' ) THEN
170 l_warehouse_type := 'TPW';
171 ELSIF ( NVL (l_cms_flag, '!') = 'Y' ) THEN
172 IF ( p_delivery_id IS NOT NULL ) THEN
173 l_entity_name := 'Delivery';
174 l_entity_id := p_delivery_id;
175 open valid_carrier_del_cur;
176 fetch valid_carrier_del_cur into l_manifest_enabled_flag;
177 close valid_carrier_del_cur;
178 ELSIF ( p_delivery_detail_id IS NOT NULL ) THEN
179 l_entity_name := 'Delivery Line';
180 l_entity_id := p_delivery_detail_id;
181 open valid_carrier_det_cur;
182 fetch valid_carrier_det_cur into l_manifest_enabled_flag;
183 close valid_carrier_det_cur;
184 ELSIF (p_carrier_id is NOT NULL) THEN
185 FOR cur IN c_valid_carrier LOOP
186 l_manifest_enabled_flag:=cur.manifesting_enabled_flag;
187 END LOOP;
188 ELSIF (p_ship_method_code is NOT NULL) THEN
189 FOR cur IN c_valid_smc LOOP
190 l_manifest_enabled_flag:=cur.manifesting_enabled_flag;
191 END LOOP;
192 END IF;
193
194 IF l_debug_on THEN
195 wsh_debug_sv.log (l_module_name,'Manifest Enabled Flag', l_manifest_enabled_flag);
196 END IF;
197
198 IF ( nvl( l_manifest_enabled_flag, '!') = 'Y' ) THEN
199 l_warehouse_type := 'CMS';
200 ELSE
201 l_warehouse_type := NULL;
202 IF (p_msg_display = 'Y' ) THEN
203 FND_MESSAGE.Set_Name('WSH', 'WSH_CARR_NOT_MANIFEST_ENABLED');
204 FND_MESSAGE.Set_Token('ENTITY_NAME', l_entity_name);
205 FND_MESSAGE.Set_Token('ENTITY_ID', l_entity_id);
206 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
207 END IF;
208 END IF;
209 END IF ;
210 ELSE
211 l_warehouse_type := NULL;
212 END IF;
213 END IF;
214
215 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
216
217 IF l_debug_on THEN
218 wsh_debug_sv.log (l_module_name, 'Warehouse Type' , l_warehouse_type);
219 wsh_debug_sv.pop (l_module_name);
220 END IF;
221
222 RETURN l_warehouse_type;
223 EXCEPTION
224 WHEN wsh_org_event_key_null THEN
225 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
226 IF l_debug_on THEN
227 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_org_event_key_null exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
228 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_org_event_key_null');
229 END IF;
230 RETURN l_warehouse_type;
231 WHEN wsh_ship_param_failed THEN
232 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
233 IF l_debug_on THEN
234 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_ship_param_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_ship_param_failed');
236 END IF;
237 RETURN l_warehouse_type;
238
239 WHEN OTHERS THEN
240 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
241 IF l_debug_on THEN
242 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
243 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
244 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
245 END IF;
246 RETURN l_warehouse_type;
247 END Get_Warehouse_Type;
248
249
250 /*===========================================================================
251 | |
252 | PROCEDURE NAME Raise_Event |
253 | |
254 | DESCRIPTION This procedure raises an event in Work Flow. It raises |
255 | an appropriate procedure depending on the parameters |
256 | passed. |
257 | |
258 | MODIFICATION HISTORY |
259 | |
260 | 02/20/02 Vijay Nandula Created |
261 | |
262 ============================================================================*/
263
264 PROCEDURE Raise_Event ( P_txn_hist_record IN WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type,
265 P_Cbod_Status IN VARCHAR2,
266 x_return_status IN OUT NOCOPY VARCHAR2)
267 IS
268 l_event_name VARCHAR2 (120);
269 l_Event_Key VARCHAR2 (30);
270
271 l_Return_Status VARCHAR2 (1);
272 l_Transaction_Code VARCHAR2 (100);
273 l_Org_ID NUMBER;
274 l_Party_Site_ID NUMBER;
275 l_txns_id NUMBER;
276
277 l_msg_parameter_list WF_PARAMETER_LIST_T;
278 l_cbod_parameter_list WF_PARAMETER_LIST_T;
279 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
280
281 CURSOR l_new_del_cur (l_trading_partner_ID NUMBER, l_name VARCHAR2)
282 IS
283 SELECT Initial_Pickup_Location_ID,
284 ultimate_dropoff_location_id, --Notification change heali
285 customer_id --Notification change heali
286 FROM wsh_new_deliveries
287 WHERE Organization_ID = l_trading_partner_id
288 AND Name = l_name;
289
290 --Notification change heali
291 CURSOR get_location(p_location_id NUMBER) IS
292 SELECT ui_location_code
293 FROM wsh_locations
294 WHERE wsh_location_id=p_location_id;
295
296 CURSOR get_customer_name(p_customer_id NUMBER) IS
297 SELECT HP.PARTY_NAME
298 FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
299 WHERE HP.PARTY_ID = HCA.PARTY_ID
300 AND HP.PARTY_ID = p_customer_id;
301
302 l_del_name varchar2(240);
303 l_sf_location varchar2(240);
304 l_customer varchar2(240);
305 l_st_location varchar2(240);
306 l_subject varchar2(240);
307 l_ship_to_location_id number;
308 l_customer_id number;
309 --Notification change heali
310
311 wsh_invalid_event_name EXCEPTION;
312 wsh_get_event_key_error EXCEPTION;
313 wsh_invalid_delivery_no EXCEPTION;
314 wsh_update_history EXCEPTION;
315 --
316 l_debug_on BOOLEAN;
317 --
318 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_EVENT';
319 --
320 BEGIN
321 --
322 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
323 --
324 IF l_debug_on IS NULL
325 THEN
326 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
327 END IF;
328 --
329 IF l_debug_on THEN
330 wsh_debug_sv.push(l_module_name);
331 WSH_DEBUG_SV.log(l_module_name,'transaction_id',P_txn_hist_record.transaction_id);
332 WSH_DEBUG_SV.log(l_module_name,'document_type',P_txn_hist_record.document_type);
333 WSH_DEBUG_SV.log(l_module_name,'document_direction',P_txn_hist_record.document_direction);
334 WSH_DEBUG_SV.log(l_module_name,'document_number',P_txn_hist_record.document_number);
335 WSH_DEBUG_SV.log(l_module_name,'orig_document_number',P_txn_hist_record.orig_document_number);
336 WSH_DEBUG_SV.log(l_module_name,'entity_number',P_txn_hist_record.entity_number);
337 WSH_DEBUG_SV.log(l_module_name,'entity_type',P_txn_hist_record.entity_type);
338 WSH_DEBUG_SV.log(l_module_name,'trading_partner_id',P_txn_hist_record.trading_partner_id);
339 WSH_DEBUG_SV.log(l_module_name,'action_type',P_txn_hist_record.action_type);
340 WSH_DEBUG_SV.log(l_module_name,'transaction_status',P_txn_hist_record.transaction_status);
341 WSH_DEBUG_SV.log(l_module_name,'ecx_message_id',P_txn_hist_record.ecx_message_id);
342 WSH_DEBUG_SV.log(l_module_name,'event_name',P_txn_hist_record.event_name);
343 WSH_DEBUG_SV.log(l_module_name,'event_key',P_txn_hist_record.event_key);
344 WSH_DEBUG_SV.log(l_module_name,'item_type',P_txn_hist_record.item_type);
345 WSH_DEBUG_SV.log(l_module_name,'internal_control_number',P_txn_hist_record.internal_control_number);
346 END IF;
347 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
348
349 l_txn_hist_record := P_txn_hist_record;
350
351 -- Get the event name from the Transaction History Table.
352 l_event_name := l_txn_hist_record.Event_Name;
353
354 -- Check if the event name is valid or not.
355
356 IF ( l_event_name NOT IN ('oracle.apps.wsh.sup.ssro',
357 'oracle.apps.wsh.sup.ssai',
358 'oracle.apps.wsh.tpw.ssri',
359 'oracle.apps.wsh.tpw.ssao',
360 'oracle.apps.wsh.tpw.spwf',
361 'oracle.apps.wsh.tpw.scbod',
362 'ORACLE.APPS.FTE.SSNO.CONFIRM') ) THEN
363 RAISE wsh_invalid_event_name;
364 END IF;
365
366 -- Transaction Code is the last 4 or 5 letters after the dot from the Event Name.
367 -- Eg: 'SSRO', 'SSAI' etc
368 l_Transaction_Code := UPPER (SUBSTRB (l_event_name, INSTRB(l_Event_Name, '.', -1) + 1));
369
370 IF l_debug_on THEN
371 wsh_debug_sv.log (l_module_name, 'Transaction Code' , l_Transaction_Code);
372 END IF;
373
374 -- Get the Event Key for Raising an Event.
375 --bmos k proj
376 IF ( l_txn_hist_record.Event_Key IS NULL ) THEN
377 WSH_TRANSACTIONS_UTIL.Get_Event_Key ( l_txn_hist_record.Item_Type,
378 l_txn_hist_record.Orig_Document_Number,
379 l_txn_hist_record.Trading_Partner_ID,
380 l_txn_hist_record.Event_Name,
381 l_txn_hist_record.entity_number,
382 l_Event_Key,
383 l_Return_Status );
384
385 IF l_debug_on THEN
386 wsh_debug_sv.log (l_module_name, 'Return status after get_event_key' , l_Return_Status);
387 END IF;
388
389 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
390 RAISE wsh_get_event_key_error;
391 END IF;
392 -- Assign the value to the Transaction History record.
393 l_txn_hist_record.Event_Key := l_Event_Key;
394 ELSE
395 l_Event_Key := l_txn_hist_record.Event_Key;
396 END IF;
397 IF l_debug_on THEN
398 wsh_debug_sv.log (l_module_name, 'Event Key' , l_Event_Key);
399 wsh_debug_sv.log (l_module_name, 'Trading partner ID ' , l_txn_hist_record.Trading_Partner_ID);
400 wsh_debug_sv.log (l_module_name, 'Entity Number ' , l_txn_hist_record.Entity_Number);
401 END IF;
402
403 IF ( l_Transaction_Code IN ('SSRO', 'SSAO') ) THEN --{
404 OPEN l_New_Del_Cur (l_txn_hist_record.Trading_Partner_ID, l_txn_hist_record.entity_number);
405 FETCH l_New_Del_Cur INTO l_Party_Site_ID,l_ship_to_location_id,l_customer_id;
406 IF ( l_New_Del_Cur % NOTFOUND ) THEN
407 CLOSE l_New_Del_Cur;
408 RAISE wsh_invalid_delivery_no;
409 END IF;
410 CLOSE l_New_Del_Cur;
411 IF l_debug_on THEN
412 wsh_debug_sv.log (l_module_name, 'Party Site ID' , l_Party_Site_ID);
413 END IF;
414 END IF; --}
415
416 IF ( l_Transaction_Code in ('SSRO', 'SSAO') ) THEN --{
417 -- Generate the document number for outgoing documents.
418 SELECT WSH_DOCUMENT_NUMBER_S.NEXTVAL
419 INTO l_txn_hist_record.Document_Number
420 FROM dual;
421 END IF; --}
422
423
424 IF ( l_Transaction_Code IN ('SSRO', 'SSAO') ) THEN --{
425 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_ID',
426 p_value => l_Party_Site_ID,
427 p_parameterlist => l_msg_parameter_list);
428 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_SITE_ID',
429 p_value => l_Party_Site_ID,
430 p_parameterlist => l_msg_parameter_list);
431 WF_EVENT.AddParameterToList (p_name => 'ECX_DOCUMENT_ID',
432 p_value => l_txn_hist_record.Document_Number,
433 p_parameterlist => l_msg_parameter_list);
434
435 WF_EVENT.AddParameterToList (p_name => 'USER_ID',
436 p_value => FND_GLOBAL.USER_ID,
437 p_parameterlist => l_msg_parameter_list);
438 --
439 WF_EVENT.AddParameterToList (p_name => 'APPLICATION_ID',
440 p_value => FND_GLOBAL.RESP_APPL_ID,
441 p_parameterlist => l_msg_parameter_list);
442 --
443 WF_EVENT.AddParameterToList (p_name => 'RESPONSIBILITY_ID',
444 p_value => FND_GLOBAL.RESP_ID,
445 p_parameterlist => l_msg_parameter_list);
446
447 IF ( l_txn_hist_record.document_type = 'SR' ) THEN --{
448 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_TYPE',
449 p_value => 'FTE',
450 p_parameterlist => l_msg_parameter_list);
451
452 --Notification change heali
453 l_del_name:= l_txn_hist_record.entity_number;
454
455 IF (instrb(l_Event_Key,'TPW') <> 0 ) THEN --{
456 IF (p_txn_hist_record.action_type='A' ) THEN
457 FND_MESSAGE.SET_NAME('WSH', 'WSH_SR_TPW_NOTIFY');
458 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
459 l_subject := FND_MESSAGE.get;
460 ELSE
461 FND_MESSAGE.SET_NAME('WSH', 'WSH_SRC_TPW_NOTIFY');
462 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
463 l_subject := FND_MESSAGE.get;
464 END IF;
465 ELSIF (instrb(l_Event_Key,'CMS') <> 0) THEN --}{
466 IF (p_txn_hist_record.action_type='A' ) THEN
467 FND_MESSAGE.SET_NAME('WSH', 'WSH_SR_CMS_NOTIFY');
468 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
469 l_subject := FND_MESSAGE.get;
470 ELSE
471 FND_MESSAGE.SET_NAME('WSH', 'WSH_SRC_CMS_NOTIFY');
472 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
473 l_subject := FND_MESSAGE.get;
474 END IF;
475 END IF; --}
476
477 IF l_debug_on THEN
478 wsh_debug_sv.log (l_module_name, 'l_subject' , l_subject);
479 END IF;
480
481 WF_EVENT.AddParameterToList (p_name => 'SUBJECT',
482 p_value => l_subject,
483 p_parameterlist => l_msg_parameter_list);
484
485
486 IF (wf_core.translate('WF_HEADER_ATTR') = 'Y' ) THEN --{
487
488 OPEN get_location(l_Party_Site_ID);
489 FETCH get_location INTO l_sf_location;
490 CLOSE get_location;
491
492 OPEN get_location(l_ship_to_location_id);
493 FETCH get_location INTO l_st_location;
494 CLOSE get_location;
495
496 OPEN get_customer_name(l_customer_id);
497 FETCH get_customer_name INTO l_customer;
498 CLOSE get_customer_name;
499
500 IF l_debug_on THEN
501 wsh_debug_sv.log (l_module_name, 'l_del_name' ,l_del_name );
502 wsh_debug_sv.log (l_module_name, 'l_sf_location' ,l_sf_location );
503 wsh_debug_sv.log (l_module_name, 'l_customer' ,l_customer );
504 wsh_debug_sv.log (l_module_name, 'l_st_location' ,l_st_location);
505 END IF;
506
507 WF_EVENT.AddParameterToList (p_name => 'DEL_NAME',
508 p_value => l_del_name,
509 p_parameterlist => l_msg_parameter_list);
510
511 WF_EVENT.AddParameterToList (p_name => 'SF_LOCATION',
512 p_value => l_sf_location,
513 p_parameterlist => l_msg_parameter_list);
514
515 WF_EVENT.AddParameterToList (p_name => 'CUSTOMER',
516 p_value => l_customer,
517 p_parameterlist => l_msg_parameter_list);
518
519 WF_EVENT.AddParameterToList (p_name => 'ST_LOCATION',
520 p_value => l_st_location,
521 p_parameterlist => l_msg_parameter_list);
522 END IF; --}
523 --Notification change heali
524
525 ELSE --}{
526 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_TYPE',
527 p_value => 'WSH',
528 p_parameterlist => l_msg_parameter_list);
529 END IF; --}
530 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_SUBTYPE',
531 p_value => 'SSNO',
532 p_parameterlist => l_msg_parameter_list);
533 WF_EVENT.AddParameterToList (p_name => 'USER',
534 p_value => FND_GLOBAL.user_name,
535 p_parameterlist => l_msg_parameter_list);
536 WF_EVENT.AddParameterToList (p_name => 'ECX_PARAMETER1',
537 p_value => l_txn_hist_record.Action_Type,
538 p_parameterlist => l_msg_parameter_list);
539 ELSIF ( l_Transaction_Code = 'SCBOD' ) THEN --}
540 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_TYPE',
541 p_value => 'ECX',
542 p_parameterlist => l_Cbod_parameter_list);
543 WF_EVENT.AddParameterToList (p_name => 'ECX_TRANSACTION_SUBTYPE',
544 p_value => 'CBODO',
545 p_parameterlist => l_Cbod_parameter_list);
546 WF_EVENT.AddParameterToList (p_name => 'ECX_DOCUMENT_ID',
547 p_value => l_txn_hist_record.Internal_Control_Number,
548 p_parameterlist => l_Cbod_parameter_list);
549 WF_EVENT.AddParameterToList (p_name => 'ECX_PARTY_TYPE',
550 p_value => 'I',
551 p_parameterlist => l_Cbod_parameter_list);
552 WF_EVENT.AddParameterToList (p_name => 'CONFIRM_STATUSLVL',
553 p_value => P_Cbod_Status,
554 p_parameterlist => l_Cbod_parameter_list);
555 ELSIF ( l_Transaction_Code = 'CONFIRM' ) THEN
556 WF_EVENT.AddParameterToList (p_name => 'PARAMETER6',
557 p_value => P_Cbod_Status,
558 p_parameterlist => l_Cbod_parameter_list);
559 END IF;
560
561 --bmos k proj
562 IF ( l_event_name NOT IN ('oracle.apps.wsh.tpw.scbod' ,'ORACLE.APPS.FTE.SSNO.CONFIRM','oracle.apps.wsh.tpw.spwf')) THEN
563
564 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
565 l_txns_id,
566 l_return_status );
567
568 IF l_debug_on THEN
569 wsh_debug_sv.log (l_module_name, 'Transaction History return status ' , l_Return_Status);
570 wsh_debug_sv.log (l_module_name, 'Transaction History ID' , l_txns_id);
571 END IF;
572
573 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
574 RAISE wsh_update_history;
575 END IF;
576 -- Commit the data into the Transaction History table for the views.
577 COMMIT;
578 END IF;
579
580 IF l_debug_on THEN
581 wsh_debug_sv.log (l_module_name, 'Before Raising Event');
582 END IF;
583
584 IF ( l_Transaction_Code IN ('SSRO', 'SSAO') ) THEN
585 WF_EVENT.raise ( p_event_name => l_event_name,
586 p_event_key => l_Event_Key,
587 p_parameters => l_msg_parameter_list );
588 ELSIF ( l_Transaction_Code IN ('SCBOD', 'CONFIRM') ) THEN
589 WF_EVENT.raise ( p_event_name => l_event_name,
590 p_event_key => l_Event_Key,
591 p_parameters => l_Cbod_parameter_list );
592 ELSE
593 WF_EVENT.raise ( p_event_name => l_event_name,
594 p_event_key => l_Event_Key );
595 END IF;
596
597 IF l_debug_on THEN
598 wsh_debug_sv.log (l_module_name, 'End of Raising Event');
599 wsh_debug_sv.pop(l_module_name);
600 END IF;
601 EXCEPTION
602 WHEN wsh_invalid_event_name THEN
603 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
604 IF l_debug_on THEN
605 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_event_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
606 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_event_name');
607 END IF;
608
609 WHEN wsh_get_event_key_error THEN
610 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
611 IF l_debug_on THEN
612 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_event_key_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
613 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_event_key_error');
614 END IF;
615
616 WHEN wsh_invalid_delivery_no THEN
617 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_no exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
620 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_no');
621 END IF;
622
623 WHEN wsh_update_history THEN
624 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
625 IF l_debug_on THEN
626 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
627 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
628 END IF;
629
630 WHEN OTHERS THEN
631 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
632 IF l_debug_on THEN
633 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
634 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
635 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
636 END IF;
637 END Raise_Event;
638
639
640 PROCEDURE Validate_Item ( p_concatenated_segments IN VARCHAR2,
641 p_organization_id IN NUMBER,
642 x_inventory_item_id OUT NOCOPY VARCHAR2,
643 x_return_status OUT NOCOPY VARCHAR2
644 )
645
646 IS
647 cursor get_item_id_cur is
648 select inventory_item_id
649 from mtl_system_items_kfv
650 where concatenated_segments = p_concatenated_segments
651 and organization_id = p_organization_id;
652 --
653 l_debug_on BOOLEAN;
654 --
655 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ITEM';
656 --
657 BEGIN
658 --
659 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
660 --
661 IF l_debug_on IS NULL
662 THEN
663 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
664 END IF;
665 --
666 IF l_debug_on THEN
667 wsh_debug_sv.push(l_module_name);
668 wsh_debug_sv.log (l_module_name, 'Item Name ', p_concatenated_segments);
669 wsh_debug_sv.log (l_module_name, 'Org Id ', p_organization_id);
670 END IF;
671
672 IF ( p_concatenated_segments is not null and p_organization_id is not null ) THEN
673 open get_item_id_cur;
674 Fetch get_item_id_cur into x_inventory_item_id;
675
676 IF get_item_id_cur%NOTFOUND THEN
677 x_return_status := wsh_util_core.g_ret_sts_error;
678 ELSE
679 x_return_status := wsh_util_core.g_ret_sts_success;
680 END IF;
681 close get_item_id_cur;
682 ELSE
683 x_return_status := wsh_util_core.g_ret_sts_success;
684 END IF;
685
686 IF l_debug_on THEN
687 wsh_debug_sv.pop (l_module_name);
688 END IF;
689 EXCEPTION
690 WHEN OTHERS THEN
691 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
692 IF get_item_id_cur%ISOPEN THEN
693 close get_item_id_cur;
694 END IF;
695 IF l_debug_on THEN
696 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
697 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
698 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
699 END IF;
700 END Validate_Item;
701
702 PROCEDURE Validate_Ship_To ( p_customer_name IN VARCHAR2,
703 p_location IN VARCHAR2,
704 x_customer_id OUT NOCOPY NUMBER,
705 x_location_id OUT NOCOPY NUMBER,
706 x_return_status OUT NOCOPY VARCHAR2,
707 p_site_use_code IN VARCHAR2 DEFAULT 'SHIP_TO',
708 x_site_use_id OUT NOCOPY NUMBER,
709 p_org_id IN NUMBER DEFAULT NULL
710 )
711 IS
712
713 -- Patchset I : Locations Project. kvenkate.
714 l_loc_rec WSH_MAP_LOCATION_REGION_PKG.loc_rec_type;
715 l_location_id NUMBER;
716 l_return_status VARCHAR2(1);
717
718 cursor get_loc_id_cur is
719 SELECT HL.LOCATION_ID,
720 HCA.CUST_ACCOUNT_ID,
721 HCSU.SITE_USE_ID
722 FROM HZ_CUST_ACCOUNTS HCA,
723 HZ_PARTIES HP,
724 HZ_CUST_SITE_USES_ALL HCSU,
725 HZ_CUST_ACCT_SITES_ALL HCAS,
726 -- ORG_ORGANIZATION_DEFINITIONS OOD,
727 HZ_PARTY_SITES HPS,
728 HZ_LOCATIONS HL
729 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
730 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
731 AND HCSU.SITE_USE_CODE IN ( p_site_use_code, 'SHIP_TO')
732 AND HCSU.STATUS = 'A'
733 --AND HCAS.ORG_ID = HCSU.ORG_ID
734 --AND HCSU.ORG_ID = OOD.OPERATING_UNIT
735 --AND OOD.ORGANIZATION_ID = p_organization_id
736 AND HPS.LOCATION_ID = HL.LOCATION_ID
737 AND HCSU.LOCATION = p_location
738 AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
739 AND HP.PARTY_ID = HCA.PARTY_ID
740 AND HCAS.ORG_ID = NVL(p_org_id , HCAS.ORG_ID)
741 AND HP.PARTY_NAME = p_customer_name
742 ; --bmso
743
744 --bug 3920178 {
745 --use related customer's location
746 CURSOR c_rel_cust_loc_cur IS
747 SELECT HPS.LOCATION_ID,
748 HCA.CUST_ACCOUNT_ID,
749 HCSU.SITE_USE_ID
750 FROM HZ_CUST_SITE_USES_ALL HCSU,
751 HZ_CUST_ACCT_SITES_ALL HCAS,
752 HZ_PARTY_SITES HPS,
753 HZ_CUST_ACCOUNTS HCA,
754 HZ_CUST_ACCT_RELATE_ALL HCAR,
755 HZ_PARTIES HP
756 WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
757 AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
758 AND HCAS.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
759 AND HCSU.SITE_USE_CODE IN (p_site_use_code, 'SHIP_TO')
760 AND HCSU.STATUS = 'A'
761 AND HCAS.STATUS = 'A'
762 AND HCA.STATUS = 'A'
763 AND HCSU.location = p_location
764 AND HCA.CUST_ACCOUNT_ID = HCAR.RELATED_CUST_ACCOUNT_ID
765 AND HCAR.SHIP_TO_FLAG = 'Y'
766 AND NVL(HCAS.ORG_ID, -999) = NVL(HCSU.ORG_ID , -999)
767 AND HCAS.ORG_ID = NVL(p_org_id , HCAS.ORG_ID)
768 AND HP.PARTY_ID = HCA.PARTY_ID
769 AND HP.PARTY_NAME = p_customer_name;
770
771 --bug 3920178 }
772
773
774 --
775 l_debug_on BOOLEAN;
776 --
777 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SHIP_TO';
778 --
779 BEGIN
780 --
781 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
782 --
783 IF l_debug_on IS NULL
784 THEN
785 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
786 END IF;
787 --
788 IF l_debug_on THEN
789 wsh_debug_sv.push(l_module_name, 'Validate_Ship_To');
790 wsh_debug_sv.log (l_module_name, 'Customer Name ', p_customer_name);
791 wsh_debug_sv.log (l_module_name, 'Location ', p_location);
792 wsh_debug_sv.log (l_module_name, 'Site Use Code ', p_site_use_code);
793 wsh_debug_sv.log (l_module_name, 'operating unit ', p_org_id);
794 END IF;
795
796 IF ( p_customer_name is not null and p_location is not null ) THEN
797 IF l_debug_on THEN
798 wsh_debug_sv.logmsg(l_module_name, 'Using get_loc_id_cur');
799 END IF;
800 open get_loc_id_cur;
801 Fetch get_loc_id_cur into l_location_id, x_customer_id, x_site_use_id;
802
803 IF get_loc_id_cur%NOTFOUND THEN
804 --{ bug 3920178 begin
805 IF l_debug_on THEN
806 wsh_debug_sv.logmsg(l_module_name, 'Using c_rel_cust_loc_cur');
807 END IF;
808 OPEN c_rel_cust_loc_cur;
809 FETCH c_rel_cust_loc_cur INTO l_location_id, x_customer_id, x_site_use_id;
810
811 IF c_rel_cust_loc_cur%NOTFOUND THEN
812 l_location_id := NULL;
813 END IF;
814
815 close c_rel_cust_loc_cur;
816 --}
817 END IF;
818 close get_loc_id_cur;
819
820 IF l_debug_on THEN
821 wsh_debug_sv.log(l_module_name, 'l_location_id', l_location_id);
822 END IF;
823
824 IF l_location_id IS NULL THEN
825 --bug 3920178 end
826 raise fnd_api.g_exc_error;
827 ELSE --{
828 -- Patchset I : Locations Project. kvenkate.
829 -- Call Transfer Location API
830
831 IF l_debug_on THEN
832 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MAP_LOCATION_REGION_PKG.TRANSFER_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
833 END IF;
834
835 WSH_MAP_LOCATION_REGION_PKG.Transfer_Location(
836 p_source_type => 'HZ',
837 p_source_location_id => l_location_id,
838 p_online_region_mapping => FALSE,
839 p_transfer_location => TRUE,
840 x_loc_rec => l_loc_rec,
841 x_return_status => l_return_status);
842
843 -- Success or Warning to be treated as success
844 -- Since warning of transfer location not to be treated as invalid ship to
845 IF l_return_status NOT IN(wsh_util_core.g_ret_sts_success, wsh_util_core.g_ret_sts_warning) THEN
846 raise fnd_api.g_exc_error;
847 END IF;
848 x_location_id := l_loc_rec.WSH_LOCATION_ID;
849 x_return_status := wsh_util_core.g_ret_sts_success;
850 END IF; --}
851 -- close get_loc_id_cur; bug 3920178
852 ELSE
853 x_return_status := wsh_util_core.g_ret_sts_success;
854 END IF;
855
856 IF l_debug_on THEN
857 wsh_debug_sv.log(l_module_name, 'Location Id', x_location_id);
858 wsh_debug_sv.log(l_module_name, 'Customer Id', x_customer_id);
859 wsh_debug_sv.log(l_module_name, 'Site Use Id', x_site_use_id);
860 wsh_debug_sv.pop (l_module_name);
861 END IF;
862 EXCEPTION
863 WHEN fnd_api.g_exc_error THEN
864 x_return_status := wsh_util_core.g_ret_sts_error;
865 IF get_loc_id_cur%ISOPEN THEN
866 close get_loc_id_cur;
867 END IF;
868 IF l_debug_on THEN
869 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
870 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
871 END IF;
872 WHEN OTHERS THEN
873 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
874 IF get_loc_id_cur%ISOPEN THEN
875 close get_loc_id_cur;
876 END IF;
877 IF l_debug_on THEN
878 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
879 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
880 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
881 END IF;
882
883 END Validate_Ship_To;
884
885
886 END WSH_EXTERNAL_INTERFACE_SV;