[Home] [Help]
1: PACKAGE BODY WSH_UTIL_CORE as
2: /* $Header: WSHUTCOB.pls 120.27.12020000.3 2013/04/09 11:46:05 suppal ship $ */
3:
4: /* H projects: pricing integration csun
5: add global variable to cache the value so in the same session
56:
57:
58: -- Description: Generic tab of records for passing column information
59: g_loc_desc_Tab Loc_Info_Tab;
60: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_UTIL_CORE';
61:
62: --Cache size constant
63: g_cache_max_size NUMBER := power(2,31);
64: --
63: g_cache_max_size NUMBER := power(2,31);
64: --
65:
66: --Cache for retriving Customer/Supplier/Carrier/Organization from locations.
67: g_customer_from_location WSH_UTIL_CORE.tbl_varchar;
68: g_organization_from_location WSH_UTIL_CORE.tbl_varchar;
69:
70: --
71:
64: --
65:
66: --Cache for retriving Customer/Supplier/Carrier/Organization from locations.
67: g_customer_from_location WSH_UTIL_CORE.tbl_varchar;
68: g_organization_from_location WSH_UTIL_CORE.tbl_varchar;
69:
70: --
71:
72: --
235: END IF;
236: ELSE -- bad format_code
237: FND_MESSAGE.SET_NAME('WSH','WSH_PUB_INVALID_PARAMETER');
238: FND_MESSAGE.Set_Token('PARAMETER',p_format);
239: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,
240: l_module_name);
241: IF l_debug_on THEN
242: WSH_DEBUG_SV.pop(l_module_name);
243: END IF;
270: -- PROCEDURE: Site_Code_to_Site_id
271: -- PURPOSE : Maps site_code to site_id
272: -- ARGUMENTS : p_site_code - site code that needs to be mapped
273: -- p_site_id - site id for the code
274: -- x_return_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS or NOT
275: --
276:
277: PROCEDURE Site_Code_to_Site_id(p_site_code IN VARCHAR2,
278: p_site_id OUT NOCOPY NUMBER,
307: WSH_DEBUG_SV.log(l_module_name,'P_SITE_CODE',P_SITE_CODE);
308: END IF;
309: --
310: p_site_id := NULL;
311: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
312:
313: OPEN get_id(p_site_code);
314: FETCH get_id INTO p_site_id;
315: CLOSE get_id;
315: CLOSE get_id;
316:
317: IF (p_site_id IS NULL) THEN
318: FND_MESSAGE.Set_Name('WSH','WSH_SITE_LOCATION_UNDEFINED');
319: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
320: IF get_id%ISOPEN THEN
321: CLOSE get_id;
322: END IF;
323: END IF;
333: when others then
334: IF get_id%ISOPEN THEN
335: CLOSE get_id;
336: END IF;
337: wsh_util_core.default_handler('WSH_UTIL_CORE.Site_Code_to_Site_id');
338: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
339:
340: --
341: -- Debug Statements
334: IF get_id%ISOPEN THEN
335: CLOSE get_id;
336: END IF;
337: wsh_util_core.default_handler('WSH_UTIL_CORE.Site_Code_to_Site_id');
338: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
339:
340: --
341: -- Debug Statements
342: --
357: -- Arguments: p_mode - 'CUSTOMER SITE', 'VENDOR SITE' or 'ORG'
358: -- p_source_id - organization_id or site_id to convert
359: -- based on p_mode
360: -- x_location_id - Converted to location_id
361: -- x_api_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS
362: -- WSH_UTIL_CORE.G_RET_STS_ERROR
363: -- If Error message can be retrieved using FND_MESSAGE.GET
364: -- Description: Gets location information for a particular inventory
365: -- organization using hr_locations view
358: -- p_source_id - organization_id or site_id to convert
359: -- based on p_mode
360: -- x_location_id - Converted to location_id
361: -- x_api_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS
362: -- WSH_UTIL_CORE.G_RET_STS_ERROR
363: -- If Error message can be retrieved using FND_MESSAGE.GET
364: -- Description: Gets location information for a particular inventory
365: -- organization using hr_locations view
366: --
420: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_ID',P_SOURCE_ID);
421: WSH_DEBUG_SV.log(l_module_name,'p_transfer_location',p_transfer_location);
422: END IF;
423: --
424: x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
425:
426: x_location_id := null;
427:
428: l_source_id_mod := MOD(p_source_id,C_INDEX_LIMIT); -- bug 8514165
448: --Bug 4891887 and 4891881 If call is made from Constraints then ignore the warning
449: IF WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API IS NULL THEN
450: --The following message is modified as part of bug # 4256319
451: FND_MESSAGE.Set_Name('WSH','WSH_DET_NO_LOCATION_FOR_ORG');
452: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
453: wsh_util_core.add_message(x_api_status,l_module_name);
454: ELSE
455: WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API :=NULL;
456: x_location_id :=l_location_id;
449: IF WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API IS NULL THEN
450: --The following message is modified as part of bug # 4256319
451: FND_MESSAGE.Set_Name('WSH','WSH_DET_NO_LOCATION_FOR_ORG');
452: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
453: wsh_util_core.add_message(x_api_status,l_module_name);
454: ELSE
455: WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API :=NULL;
456: x_location_id :=l_location_id;
457: END IF;
480: l_return_status);
481: WSH_DEBUG_SV.log(l_module_name,'location_id returned ',
482: l_loc_rec.wsh_location_id);
483: END IF;
484: wsh_util_core.api_post_call(
485: p_return_status => l_return_status,
486: x_num_warnings => l_num_warnings,
487: x_num_errors => l_num_errors);
488:
491: END IF;
492:
493: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
494: OR (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
495: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
496: END IF;
497: g_org_Id_Tab(l_source_id_mod) := l_loc_rec.WSH_LOCATION_ID; --bug 8514165 replaced p_source_id with l_source_id_mod
498: x_location_id := l_loc_rec.WSH_LOCATION_ID;
499: END IF;
513: CLOSE site_to_loc;
514:
515: IF (l_location_id IS NULL) THEN
516: FND_MESSAGE.Set_Name('WSH','WSH_SITE_LOCATION_UNDEFINED');
517: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
518: wsh_util_core.add_message(x_api_status,l_module_name);
519: IF site_to_loc%ISOPEN THEN
520: CLOSE site_to_loc;
521: END IF;
514:
515: IF (l_location_id IS NULL) THEN
516: FND_MESSAGE.Set_Name('WSH','WSH_SITE_LOCATION_UNDEFINED');
517: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
518: wsh_util_core.add_message(x_api_status,l_module_name);
519: IF site_to_loc%ISOPEN THEN
520: CLOSE site_to_loc;
521: END IF;
522: --
537: p_online_region_mapping => FALSE,
538: x_loc_rec => l_loc_rec,
539: x_return_status => l_return_status
540: );
541: wsh_util_core.api_post_call(
542: p_return_status => l_return_status,
543: x_num_warnings => l_num_warnings,
544: x_num_errors => l_num_errors);
545:
548: END IF;
549:
550: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS )
551: OR (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
552: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
553: END IF;
554: g_cust_Id_Tab(l_source_id_mod) := l_loc_rec.WSH_LOCATION_ID; --bug 8514165 replaced p_source_id with l_source_id_mod
555: x_location_id := l_loc_rec.WSH_LOCATION_ID;
556:
567: --
568: RETURN;
569: ELSE
570: FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
571: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
572: --
573: IF l_debug_on THEN
574: WSH_DEBUG_SV.pop(l_module_name);
575: END IF;
581: WSH_DEBUG_SV.pop(l_module_name);
582: END IF;
583: --
584: EXCEPTION
585: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
586: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
587: fnd_message.set_name('WSH','WSH_XC_INVALID_LOCATION');
588: wsh_util_core.add_message(x_api_status,l_module_name);
589: --
582: END IF;
583: --
584: EXCEPTION
585: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
586: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
587: fnd_message.set_name('WSH','WSH_XC_INVALID_LOCATION');
588: wsh_util_core.add_message(x_api_status,l_module_name);
589: --
590: IF l_debug_on THEN
584: EXCEPTION
585: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
586: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
587: fnd_message.set_name('WSH','WSH_XC_INVALID_LOCATION');
588: wsh_util_core.add_message(x_api_status,l_module_name);
589: --
590: IF l_debug_on THEN
591: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_RET_STS_WARNING');
592: END IF;
591: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_RET_STS_WARNING');
592: END IF;
593: --
594: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
595: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
596: --
597: IF l_debug_on THEN
598: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_EXC_UNEXPECTED_ERROR');
599: END IF;
600: --
601: WHEN FND_API.G_EXC_ERROR THEN
602: IF (NOT p_transfer_location)
603: AND (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
604: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
605: ELSE
606: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
607: END IF;
608: --
602: IF (NOT p_transfer_location)
603: AND (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
604: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
605: ELSE
606: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
607: END IF;
608: --
609: IF l_debug_on THEN
610: WSH_DEBUG_SV.log(l_module_name,'x_api_status ',x_api_status);
619: IF site_to_loc%ISOPEN THEN
620: CLOSE site_to_loc;
621: END IF;
622: FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
623: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
624:
625: --
626: IF l_debug_on THEN
627: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
636: -- PURPOSE: Obtain master organization id for an organization_id
637: -- Arguments: p_org_id - organization_id
638: -- x_master_org_id - Master organization id for input organization_id
639: -- x_return_status -
640: -- WSH_UTIL_CORE.G_RET_STS_SUCCESS
641: -- WSH_UTIL_CORE.G_RET_STS_ERROR
642: -- Notes: Throws exception when fails
643: --
644:
637: -- Arguments: p_org_id - organization_id
638: -- x_master_org_id - Master organization id for input organization_id
639: -- x_return_status -
640: -- WSH_UTIL_CORE.G_RET_STS_SUCCESS
641: -- WSH_UTIL_CORE.G_RET_STS_ERROR
642: -- Notes: Throws exception when fails
643: --
644:
645: PROCEDURE get_master_from_org(
659: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_master_from_org';
660:
661: BEGIN
662:
663: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
664:
665: --
666: IF l_debug_on THEN
667: WSH_DEBUG_SV.push(l_module_name);
693: --
694:
695: EXCEPTION
696: WHEN org_not_found THEN
697: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
698: --
699: IF l_debug_on THEN
700: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:org in mtl_parameters not found');
701: END IF;
703: WHEN others THEN
704: IF c_get_master_from_org%ISOPEN THEN
705: CLOSE c_get_master_from_org;
706: END IF;
707: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_master_from_org');
708: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
709: --
710: IF l_debug_on THEN
711: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
704: IF c_get_master_from_org%ISOPEN THEN
705: CLOSE c_get_master_from_org;
706: END IF;
707: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_master_from_org');
708: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
709: --
710: IF l_debug_on THEN
711: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
712: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
761: l_location_id,
762: l_api_status,
763: p_transfer_location);
764:
765: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
766: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
767: l_location_id := NULL;
768: ELSE
769: RAISE NO_DATA_FOUND;
762: l_api_status,
763: p_transfer_location);
764:
765: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
766: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
767: l_location_id := NULL;
768: ELSE
769: RAISE NO_DATA_FOUND;
770: END IF;
830: l_location_id,
831: l_api_status,
832: p_transfer_location);
833:
834: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
835: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
836: l_location_id := NULL;
837: ELSE
838: RAISE NO_DATA_FOUND;
831: l_api_status,
832: p_transfer_location);
833:
834: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
835: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
836: l_location_id := NULL;
837: ELSE
838: RAISE NO_DATA_FOUND;
839: END IF;
898: l_location_id,
899: l_api_status,
900: p_transfer_location);
901:
902: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
903: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
904: l_location_id := NULL;
905: ELSE
906: RAISE NO_DATA_FOUND;
899: l_api_status,
900: p_transfer_location);
901:
902: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
903: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
904: l_location_id := NULL;
905: ELSE
906: RAISE NO_DATA_FOUND;
907: END IF;
993:
994: IF (get_freight%NOTFOUND) THEN
995: CLOSE get_freight;
996: fnd_message.set_name('WSH','WSH_FREIGHT_CODE_NOT_FOUND');
997: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
998: RAISE NO_DATA_FOUND;
999: END IF;
1000:
1001: CLOSE get_freight;
1668: EXCEPTION
1669: WHEN OTHERS THEN
1670: dbms_sql.close_cursor(rec_cursor);
1671: FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1672: FND_MESSAGE.Set_Token('PACKAGE','WSH_UTIL_CORE');
1673: FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1674: FND_MESSAGE.Set_Token('ORA_TEXT',query_text);
1675: APP_EXCEPTION.Raise_Exception;
1676: --
1737: EXCEPTION
1738: WHEN OTHERS THEN
1739: dbms_sql.close_cursor(rec_cursor);
1740: FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1741: FND_MESSAGE.Set_Token('PACKAGE','WSH_UTIL_CORE');
1742: FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1743: FND_MESSAGE.Set_Token('ORA_TEXT',query_text);
1744: APP_EXCEPTION.Raise_Exception;
1745: --
1802: l_message_type := 'W';
1803: l_exception_level := WSH_DEBUG_SV.C_ERR_LEVEL;
1804: END IF;
1805:
1806: --IF WSH_UTIL_CORE.G_STORE_MSG_IN_TABLE = FALSE THEN
1807: IF G_STORE_MSG_IN_TABLE = FALSE THEN
1808: IF (l_message_type IS NOT NULL) THEN
1809: msg_buffer := fnd_message.get;
1810: IF msg_buffer is not null THEN
2051: FND_MESSAGE.SET_TOKEN('NUM_ERROR', l_errors);
2052: FND_MESSAGE.SET_TOKEN('NUM_WARN', l_warnings);
2053: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS', l_total - l_errors - l_warnings);
2054:
2055: wsh_util_core.add_message(p_return_status, p_module_name);
2056: END IF;
2057:
2058: IF (l_total = l_errors) THEN
2059: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2055: wsh_util_core.add_message(p_return_status, p_module_name);
2056: END IF;
2057:
2058: IF (l_total = l_errors) THEN
2059: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2060: ELSE
2061: p_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2062: END IF;
2063: ELSE
2057:
2058: IF (l_total = l_errors) THEN
2059: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2060: ELSE
2061: p_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2062: END IF;
2063: ELSE
2064: p_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2065: END IF;
2060: ELSE
2061: p_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2062: END IF;
2063: ELSE
2064: p_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2065: END IF;
2066:
2067: --
2068: -- Debug Statements
2217: FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
2218: FND_MESSAGE.Set_Token('PACKAGE',p_routine_name);
2219: FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
2220: FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
2221: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_UNEXP_ERROR, p_module_name);
2222:
2223: --
2224: -- Debug Statements
2225: --
2530: --
2531: --tkt
2532: PROCEDURE Delete(
2533: p_type wsh_saved_queries_vl.entity_type%type,
2534: p_rows IN OUT NOCOPY wsh_util_core.id_tab_type,
2535: p_caller IN VARCHAR2,
2536: x_return_status OUT NOCOPY VARCHAR2)
2537: is
2538:
2546: l_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
2547: l_trip_rec WSH_TRIPS_PVT.trip_rec_type;
2548: -- bmso
2549: l_lpn_ids WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2550: l_lpn_id_tab wsh_util_core.id_tab_type;
2551:
2552: l_purged_lpns WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2553:
2554: l_validate_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_VALIDATE;
2552: l_purged_lpns WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2553:
2554: l_validate_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_VALIDATE;
2555: l_delete_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_DELETE;
2556: l_rows_lpns wsh_util_core.id_tab_type;
2557: l_cached_lpns WSH_UTIL_CORE.Id_Tab_Type;
2558: l_msg_data VARCHAR2(32000);
2559: l_msg_count NUMBER;
2560: l_index NUMBER;
2553:
2554: l_validate_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_VALIDATE;
2555: l_delete_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_DELETE;
2556: l_rows_lpns wsh_util_core.id_tab_type;
2557: l_cached_lpns WSH_UTIL_CORE.Id_Tab_Type;
2558: l_msg_data VARCHAR2(32000);
2559: l_msg_count NUMBER;
2560: l_index NUMBER;
2561: l_count NUMBER;
2612: num_errors := 0;
2613: num_warnings := 0;
2614: /* added this check for FTE */
2615: /* so we will not do this for each record */
2616: x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2617:
2618: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y'
2619: AND p_type IN ('TRIP','STOP'))THEN
2620: l_fte_flag := 'Y';
2614: /* added this check for FTE */
2615: /* so we will not do this for each record */
2616: x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2617:
2618: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y'
2619: AND p_type IN ('TRIP','STOP'))THEN
2620: l_fte_flag := 'Y';
2621:
2622: END IF;
2669: , p_action => l_validate_mode
2670: , p_lpn_purge_rec => l_lpn_ids
2671:
2672: );
2673: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2674: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2675: END IF;
2676: IF l_lpn_ids.lpn_ids.COUNT > 0 THEN
2677:
2699: wsh_trips_grp.get_trip_details_pvt
2700: (p_trip_id => p_rows(i),
2701: x_trip_rec => l_trip_rec,
2702: x_return_status => l_return_status);
2703: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2704: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2705: num_warnings := num_warnings + 1;
2706: ELSE
2707: x_return_status := l_return_status;
2700: (p_trip_id => p_rows(i),
2701: x_trip_rec => l_trip_rec,
2702: x_return_status => l_return_status);
2703: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2704: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2705: num_warnings := num_warnings + 1;
2706: ELSE
2707: x_return_status := l_return_status;
2708: --
2729: p_trip_rec => l_trip_rec,
2730: p_action => 'DELETE',
2731: x_return_status => l_return_status);
2732:
2733: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2734: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2735: num_warnings := num_warnings + 1;
2736: ELSE
2737: x_return_status := l_return_status;
2730: p_action => 'DELETE',
2731: x_return_status => l_return_status);
2732:
2733: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2734: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2735: num_warnings := num_warnings + 1;
2736: ELSE
2737: x_return_status := l_return_status;
2738: --
2758: wsh_trip_stops_grp.get_stop_details_pvt
2759: (p_stop_id => p_rows(i),
2760: x_stop_rec => l_stop_rec,
2761: x_return_status => l_return_status);
2762: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2763: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2764: num_warnings := num_warnings + 1;
2765: ELSE
2766: x_return_status := l_return_status;
2759: (p_stop_id => p_rows(i),
2760: x_stop_rec => l_stop_rec,
2761: x_return_status => l_return_status);
2762: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2763: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2764: num_warnings := num_warnings + 1;
2765: ELSE
2766: x_return_status := l_return_status;
2767: --
2787: (p_stop_rec => l_stop_rec,
2788: p_trip_rec => l_trip_rec,
2789: p_action => 'DELETE',
2790: x_return_status => l_return_status);
2791: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2792: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2793: num_warnings := num_warnings + 1;
2794: ELSE
2795: x_return_status := l_return_status;
2788: p_trip_rec => l_trip_rec,
2789: p_action => 'DELETE',
2790: x_return_status => l_return_status);
2791: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2792: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2793: num_warnings := num_warnings + 1;
2794: ELSE
2795: x_return_status := l_return_status;
2796: --
2849:
2850: -- wsh_delivery_legs_pvt.delete_delivery_leg(null, p_rows(i), cur_return_status);
2851:
2852: fnd_message.set_name('WSH','DEL_DLEG_NOT_IMPLEMENTED_YET');
2853: cur_return_status := wsh_util_core.g_ret_sts_error;
2854: wsh_util_core.add_message(cur_return_status);
2855:
2856: elsif (p_type = 'DLVB') then
2857: --
2850: -- wsh_delivery_legs_pvt.delete_delivery_leg(null, p_rows(i), cur_return_status);
2851:
2852: fnd_message.set_name('WSH','DEL_DLEG_NOT_IMPLEMENTED_YET');
2853: cur_return_status := wsh_util_core.g_ret_sts_error;
2854: wsh_util_core.add_message(cur_return_status);
2855:
2856: elsif (p_type = 'DLVB') then
2857: --
2858: -- Debug Statements
2871: END IF; --}
2872:
2873: IF l_delivery_detail_id IS NOT NULL THEN
2874: WSH_CONTAINER_ACTIONS.delete_containers(p_rows(i), cur_return_status);
2875: IF cur_return_status NOT IN (wsh_util_core.g_ret_sts_success,
2876: wsh_util_core.g_ret_sts_warning)
2877: THEN
2878: update wsh_lpn_purge_tmp
2879: SET eligible_flag = 'N'
2872:
2873: IF l_delivery_detail_id IS NOT NULL THEN
2874: WSH_CONTAINER_ACTIONS.delete_containers(p_rows(i), cur_return_status);
2875: IF cur_return_status NOT IN (wsh_util_core.g_ret_sts_success,
2876: wsh_util_core.g_ret_sts_warning)
2877: THEN
2878: update wsh_lpn_purge_tmp
2879: SET eligible_flag = 'N'
2880: WHERE delivery_detail_id = p_rows(i);
2879: SET eligible_flag = 'N'
2880: WHERE delivery_detail_id = p_rows(i);
2881: END IF;
2882: ELSE
2883: cur_return_status := wsh_util_core.g_ret_sts_error;
2884: END IF;
2885:
2886: end if;
2887:
2884: END IF;
2885:
2886: end if;
2887:
2888: if (cur_return_status <> wsh_util_core.g_ret_sts_success) then
2889:
2890: if (cur_return_status = wsh_util_core.g_ret_sts_error) OR (cur_return_status = wsh_util_core.g_ret_sts_unexp_error) then
2891: p_rows(i) := -p_rows(i);
2892: end if;
2886: end if;
2887:
2888: if (cur_return_status <> wsh_util_core.g_ret_sts_success) then
2889:
2890: if (cur_return_status = wsh_util_core.g_ret_sts_error) OR (cur_return_status = wsh_util_core.g_ret_sts_unexp_error) then
2891: p_rows(i) := -p_rows(i);
2892: end if;
2893:
2894:
2891: p_rows(i) := -p_rows(i);
2892: end if;
2893:
2894:
2895: if (cur_return_status = wsh_util_core.g_ret_sts_warning) then
2896: num_warnings := num_warnings + 1;
2897: else
2898: num_errors := num_errors + 1;
2899: end if;
2921: , p_action => l_delete_mode
2922: , p_lpn_purge_rec => l_purged_lpns
2923:
2924: );
2925: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2926: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2927: END IF;
2928: END IF;
2929: END IF; --}
2950: WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2951: END IF;
2952: --
2953: --
2954: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2955: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2956: num_warnings := num_warnings + 1;
2957: ELSE
2958: x_return_status := l_return_status;
2951: END IF;
2952: --
2953: --
2954: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2955: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2956: num_warnings := num_warnings + 1;
2957: ELSE
2958: x_return_status := l_return_status;
2959: --
2970: -- K LPN CONV. rv
2971: --
2972:
2973: if (num_errors > 0) then
2974: x_return_status := wsh_util_core.g_ret_sts_error;
2975: elsif (num_warnings >0) then
2976: x_return_status := wsh_util_core.g_ret_sts_warning;
2977: end if;
2978:
2972:
2973: if (num_errors > 0) then
2974: x_return_status := wsh_util_core.g_ret_sts_error;
2975: elsif (num_warnings >0) then
2976: x_return_status := wsh_util_core.g_ret_sts_warning;
2977: end if;
2978:
2979: if (p_type = 'TRIP') then
2980: l_message := 'WSH_TRIP_DELETE_SUMMARY';
2987: elsif (p_type = 'DLVB') then
2988: l_message := 'WSH_DLVB_DELETE_SUMMARY';
2989: end if;
2990:
2991: wsh_util_core.add_summary_message(
2992: l_message,
2993: p_rows.count,
2994: num_warnings,
2995: num_errors,
3025: --
3026: IF l_debug_on THEN
3027: WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
3028: END IF;
3029: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3030: x_return_status := l_return_status;
3031: END IF;
3032: --}
3033: END IF;
3038: -- LPN CONV. rv
3039: --
3040: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3041: ROLLBACK TO s_delete_savepoint;
3042: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3043:
3044: IF l_debug_on THEN
3045: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle
3046: error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3047: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3048: END IF;
3049: WHEN OTHERS THEN
3050: ROLLBACK TO s_delete_savepoint;
3051: wsh_util_core.default_handler('WSH_UTIL_CORE.DELETE');
3052: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3053:
3054: --
3055: -- Debug Statements
3048: END IF;
3049: WHEN OTHERS THEN
3050: ROLLBACK TO s_delete_savepoint;
3051: wsh_util_core.default_handler('WSH_UTIL_CORE.DELETE');
3052: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3053:
3054: --
3055: -- Debug Statements
3056: --
3349: END IF;
3350: --
3351: x_ledger_id := NULL;
3352: x_func_currency := NULL;
3353: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3354: --
3355: IF p_org_id = G_OPERATING_UNIT_INFO.org_id THEN
3356: x_ledger_id := G_OPERATING_UNIT_INFO.ledger_id;
3357: x_func_currency := G_OPERATING_UNIT_INFO.currency_code;
3390: CLOSE c_ledger_and_func_curr;
3391: END IF;
3392: x_ledger_id := NULL;
3393: x_func_currency := NULL;
3394: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3395: fnd_message.set_name('WSH', 'WSH_LEDGER_ID_NOT_FOUND');
3396: WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3397:
3398: IF l_debug_on THEN
3392: x_ledger_id := NULL;
3393: x_func_currency := NULL;
3394: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3395: fnd_message.set_name('WSH', 'WSH_LEDGER_ID_NOT_FOUND');
3396: WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3397:
3398: IF l_debug_on THEN
3399: WSH_DEBUG_SV.pop(l_module_name);
3400: END IF;
3401:
3402: WHEN others THEN
3403: x_ledger_id := NULL;
3404: x_func_currency := NULL;
3405: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3406: IF c_ledger_and_func_curr%ISOPEN THEN
3407: CLOSE c_ledger_and_func_curr;
3408: END IF;
3409: wsh_util_core.default_handler('WSH_UTIL_CORE.Get_Ledger_id_Func_Currency');
3405: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3406: IF c_ledger_and_func_curr%ISOPEN THEN
3407: CLOSE c_ledger_and_func_curr;
3408: END IF;
3409: wsh_util_core.default_handler('WSH_UTIL_CORE.Get_Ledger_id_Func_Currency');
3410: -- Debug Statements
3411: --
3412: IF l_debug_on THEN
3413: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3426: -- Input Arguments
3427: --
3428: --
3429: PROCEDURE Print_Label(
3430: p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3431: p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3432: x_return_status OUT NOCOPY VARCHAR2)
3433: IS
3434:
3427: --
3428: --
3429: PROCEDURE Print_Label(
3430: p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3431: p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3432: x_return_status OUT NOCOPY VARCHAR2)
3433: IS
3434:
3435: CURSOR get_delivery_wms(x_delivery_id NUMBER) IS
3489: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID.COUNT',p_delivery_ids.count);
3490: WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID.COUNT',p_stop_ids.count);
3491: END IF;
3492: --
3493: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
3494: l_del_tab.delete;
3495: j := 0;
3496: IF p_delivery_ids.count > 0 THEN
3497: FOR i in 1..p_delivery_ids.count LOOP
3536: p_transaction_id => l_del_tab);
3537:
3538: IF (l_label_return_status <> 'S') THEN
3539: FND_MESSAGE.SET_NAME('WSH','WSH_PRINT_LABEL_ERROR');
3540: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3541: wsh_util_core.add_message(x_return_status);
3542: END IF;
3543: END IF;
3544:
3537:
3538: IF (l_label_return_status <> 'S') THEN
3539: FND_MESSAGE.SET_NAME('WSH','WSH_PRINT_LABEL_ERROR');
3540: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3541: wsh_util_core.add_message(x_return_status);
3542: END IF;
3543: END IF;
3544:
3545: --
3550: END IF;
3551: --
3552: EXCEPTION
3553: WHEN others THEN
3554: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3555: IF get_delivery_wms%ISOPEN THEN
3556: CLOSE get_delivery_wms;
3557: END IF;
3558:
3558:
3559: IF pickup_deliveries_wms%ISOPEN THEN
3560: CLOSE pickup_deliveries_wms;
3561: END IF;
3562: wsh_util_core.default_handler('WSH_UTIL_CORE.Print_Label');
3563:
3564: --
3565: -- Debug Statements
3566: --
3780: --
3781: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_DELIVERY_LEG_ID);
3782: END IF;
3783: --
3784: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
3785: OPEN c_get_trip_name;
3786: FETCH c_get_trip_name INTO l_trip_name, l_reprice_required;
3787: IF c_get_trip_name%NOTFOUND THEN
3788: x_trip_name := NULL;
3803: END IF;
3804: --
3805: EXCEPTION
3806: WHEN others THEN
3807: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3808: wsh_util_core.default_handler('WSH_UTIL_CORE.Print_Label');
3809:
3810: --
3811: -- Debug Statements
3804: --
3805: EXCEPTION
3806: WHEN others THEN
3807: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3808: wsh_util_core.default_handler('WSH_UTIL_CORE.Print_Label');
3809:
3810: --
3811: -- Debug Statements
3812: --
3858: --
3859: --
3860: IF p_return_status IS NULL THEN
3861: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3862: ELSIF p_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3863: x_num_errors := nvl(x_num_errors,0) + 1;
3864: IF p_raise_error_flag THEN
3865: raise FND_API.G_EXC_ERROR;
3866: END IF;
3863: x_num_errors := nvl(x_num_errors,0) + 1;
3864: IF p_raise_error_flag THEN
3865: raise FND_API.G_EXC_ERROR;
3866: END IF;
3867: ELSIF p_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3868: x_num_errors := nvl(x_num_errors,0) + 1;
3869: IF p_raise_error_flag THEN
3870: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3871: END IF;
3868: x_num_errors := nvl(x_num_errors,0) + 1;
3869: IF p_raise_error_flag THEN
3870: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3871: END IF;
3872: ELSIF p_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3873: x_num_warnings := nvl(x_num_warnings,0) + 1;
3874: END IF;
3875: --
3876: --
3951: END IF;
3952: */
3953:
3954: --
3955: IF (p_msg_data IS NOT NULL AND p_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)THEN
3956: add_message(
3957: p_message_type => p_return_status,
3958: p_module_name => p_module_name,
3959: p_error_name => p_msg_data,
4043:
4044: RETURN l_org_id;
4045: EXCEPTION
4046: WHEN OTHERS THEN
4047: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_OPERATINGUNIT_ID',
4048: l_module_name);
4049: IF l_debug_on THEN
4050: WSH_DEBUG_SV.pop(l_module_name);
4051: END IF;
4062: -- p_store_flag
4063: --
4064: PROCEDURE Store_Msg_In_Table (
4065: p_store_flag IN Boolean,
4066: x_msg_rec_count OUT NOCOPY WSH_UTIL_CORE.MsgCountType,
4067: x_return_status OUT NOCOPY VARCHAR2) IS
4068: --
4069: l_debug_on BOOLEAN;
4070: --
4088: WSH_DEBUG_SV.push(l_module_name);
4089: --
4090: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_STORE_FLAG);
4091: END IF;
4092: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
4093: IF (p_store_flag = FALSE) THEN
4094: x_msg_rec_count.e_count := 0;
4095: x_msg_rec_count.w_count := 0;
4096: x_msg_rec_count.u_count := 0;
4127: END IF;
4128: --
4129: EXCEPTION
4130: WHEN others THEN
4131: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4132: WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Store_Msg_In_Table');
4133: --
4134: -- Debug Statements
4135: --
4128: --
4129: EXCEPTION
4130: WHEN others THEN
4131: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4132: WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Store_Msg_In_Table');
4133: --
4134: -- Debug Statements
4135: --
4136: IF l_debug_on THEN
4204: WSH_DEBUG_SV.log(l_module_name, 'p_key', p_key);
4205: WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
4206: END IF;
4207: --
4208: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4209:
4210: IF p_action = 'PUT' THEN --{
4211: IF p_key IS NULL THEN
4212: IF l_debug_on THEN
4236: IF p_key < C_INDEX_LIMIT THEN --{
4237: IF p_cache_tbl.EXISTS(p_key) THEN
4238: p_value := p_cache_tbl(p_key).value;
4239: ELSE
4240: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4241: END IF;
4242: ELSE --}{
4243: l_index := p_cache_ext_tbl.FIRST;
4244: WHILE l_index IS NOT NULL LOOP
4249: END IF;
4250: l_index := p_cache_ext_tbl.NEXT(l_index);
4251: END LOOP;
4252: IF NOT l_found THEN
4253: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4254: END IF;
4255: END IF; --}
4256: END IF;--}
4257: IF l_debug_on THEN
4257: IF l_debug_on THEN
4258: WSH_DEBUG_SV.pop(l_module_name);
4259: END IF;
4260: EXCEPTION
4261: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4262: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4263: --
4264: IF l_debug_on THEN
4265: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4258: WSH_DEBUG_SV.pop(l_module_name);
4259: END IF;
4260: EXCEPTION
4261: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4262: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4263: --
4264: IF l_debug_on THEN
4265: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4266: WSH_DEBUG_SV.C_EXCEP_LEVEL);
4268: END IF;
4269: --
4270:
4271: WHEN FND_API.G_EXC_ERROR THEN
4272: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4273: --
4274: IF l_debug_on THEN
4275: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4276: || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4277: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4278: END IF;
4279: --
4280: WHEN OTHERS THEN
4281: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4282: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4283: --
4284: IF l_debug_on THEN
4285: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4278: END IF;
4279: --
4280: WHEN OTHERS THEN
4281: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4282: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4283: --
4284: IF l_debug_on THEN
4285: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4286: 'Oracle error message is '||
4355: WSH_DEBUG_SV.log(l_module_name, 'p_key', p_key);
4356: WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
4357: END IF;
4358: --
4359: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4360:
4361: IF p_action = 'PUT' THEN --{
4362: IF p_key IS NULL THEN
4363: IF l_debug_on THEN
4387: IF p_key < C_INDEX_LIMIT THEN --{
4388: IF p_cache_tbl.EXISTS(p_key) THEN
4389: p_value := p_cache_tbl(p_key).value;
4390: ELSE
4391: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4392: END IF;
4393: ELSE --}{
4394: l_index := p_cache_ext_tbl.FIRST;
4395: WHILE l_index IS NOT NULL LOOP
4400: END IF;
4401: l_index := p_cache_ext_tbl.NEXT(l_index);
4402: END LOOP;
4403: IF NOT l_found THEN
4404: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4405: END IF;
4406: END IF; --}
4407: END IF;--}
4408: IF l_debug_on THEN
4409: WSH_DEBUG_SV.pop(l_module_name);
4410: END IF;
4411:
4412: EXCEPTION
4413: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4414: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4415: --
4416: IF l_debug_on THEN
4417: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4410: END IF;
4411:
4412: EXCEPTION
4413: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4414: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4415: --
4416: IF l_debug_on THEN
4417: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4418: WSH_DEBUG_SV.C_EXCEP_LEVEL);
4420: END IF;
4421: --
4422:
4423: WHEN FND_API.G_EXC_ERROR THEN
4424: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4425: --
4426: IF l_debug_on THEN
4427: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4428: || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4429: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4430: END IF;
4431: --
4432: WHEN OTHERS THEN
4433: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4434: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4435: --
4436: IF l_debug_on THEN
4437: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4430: END IF;
4431: --
4432: WHEN OTHERS THEN
4433: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4434: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4435: --
4436: IF l_debug_on THEN
4437: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4438: 'Oracle error message is '||
4505: WSH_DEBUG_SV.log(l_module_name, 'p_key', p_key);
4506: WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
4507: END IF;
4508: --
4509: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4510:
4511: IF p_action = 'PUT' THEN --{
4512: IF p_key IS NULL THEN
4513: IF l_debug_on THEN
4537: IF p_key < C_INDEX_LIMIT THEN --{
4538: IF p_cache_tbl.EXISTS(p_key) THEN
4539: p_value := p_cache_tbl(p_key).value;
4540: ELSE
4541: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4542: END IF;
4543: ELSE --}{
4544: l_index := p_cache_ext_tbl.FIRST;
4545: WHILE l_index IS NOT NULL LOOP
4550: END IF;
4551: l_index := p_cache_ext_tbl.NEXT(l_index);
4552: END LOOP;
4553: IF NOT l_found THEN
4554: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4555: END IF;
4556: END IF; --}
4557: END IF;--}
4558: IF l_debug_on THEN
4559: WSH_DEBUG_SV.pop(l_module_name);
4560: END IF;
4561:
4562: EXCEPTION
4563: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4564: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4565: --
4566: IF l_debug_on THEN
4567: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4560: END IF;
4561:
4562: EXCEPTION
4563: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4564: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4565: --
4566: IF l_debug_on THEN
4567: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4568: WSH_DEBUG_SV.C_EXCEP_LEVEL);
4570: END IF;
4571: --
4572:
4573: WHEN FND_API.G_EXC_ERROR THEN
4574: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4575: --
4576: IF l_debug_on THEN
4577: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4578: || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4579: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4580: END IF;
4581: --
4582: WHEN OTHERS THEN
4583: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4584: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4585: --
4586: IF l_debug_on THEN
4587: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4580: END IF;
4581: --
4582: WHEN OTHERS THEN
4583: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4584: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4585: --
4586: IF l_debug_on THEN
4587: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4588: 'Oracle error message is '||
6594: -- a PL/SQL table numbers;
6595: --
6596: PROCEDURE get_idtab_from_string(
6597: p_string IN VARCHAR2,
6598: x_id_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
6599: x_return_status OUT NOCOPY VARCHAR2) IS
6600:
6601: l_new_pos NUMBER;
6602: l_old_pos NUMBER;
6605:
6606: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
6607: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_idtab_from_string';
6608: BEGIN
6609: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6610: --
6611: IF l_debug_on THEN
6612: wsh_debug_sv.push (l_module_name);
6613: END IF;
6633: --
6634:
6635: EXCEPTION
6636: WHEN others THEN
6637: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_IDTAB_FROM_STRING');
6638: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6639: --
6640: IF l_debug_on THEN
6641: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6634:
6635: EXCEPTION
6636: WHEN others THEN
6637: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_IDTAB_FROM_STRING');
6638: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6639: --
6640: IF l_debug_on THEN
6641: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6642: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
6650: -- Purpose : Used to convert a PL/SQL table of numbers to comma-separated list of form '1,2,3,4'
6651: --
6652:
6653: PROCEDURE get_string_from_idtab(
6654: p_id_tab IN WSH_UTIL_CORE.Id_Tab_Type,
6655: x_string OUT NOCOPY VARCHAR2,
6656: x_return_status OUT NOCOPY VARCHAR2) IS
6657:
6658: l_string VARCHAR2(32767);
6659: l_itr NUMBER;
6660: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
6661: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_string_from_idtab';
6662: BEGIN
6663: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6664: --
6665: IF l_debug_on THEN
6666: wsh_debug_sv.push (l_module_name);
6667: END IF;
6691: --
6692:
6693: EXCEPTION
6694: WHEN others THEN
6695: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_STRING_FROM_IDTAB');
6696: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6697: --
6698: IF l_debug_on THEN
6699: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6692:
6693: EXCEPTION
6694: WHEN others THEN
6695: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_STRING_FROM_IDTAB');
6696: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6697: --
6698: IF l_debug_on THEN
6699: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6700: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
6750: p_out_entity_name := 'Line';
6751: p_out_entity_id := p_in_entity_id;
6752: END IF;
6753:
6754: p_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6755:
6756: IF l_debug_on THEN
6757: WSH_DEBUG_SV.push(l_module_name);
6758: END IF;
6758: END IF;
6759: EXCEPTION
6760: WHEN OTHERS
6761: THEN
6762: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6763: --
6764: IF l_debug_on THEN
6765: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM);
6766: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6824: wsh_debug_sv.log(l_module_name,'count of stops : ',G_STOP_IDS_STOP_IDS_CACHE.count);
6825: wsh_debug_sv.log(l_module_name,'count of stops2 : ',G_STOP_IDS_STOP_IDS_EXT_CACHE.count);
6826: END IF;
6827: --
6828: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6829: l_num_warnings := 0;
6830: l_num_errors := 0;
6831: --l_stop_ids_tab.delete;
6832:
6871: x_return_status => l_return_status);
6872:
6873: IF l_debug_on THEN
6874: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6875: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6876: END IF;
6877:
6878: wsh_util_core.api_post_call
6879: (
6874: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6875: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6876: END IF;
6877:
6878: wsh_util_core.api_post_call
6879: (
6880: p_return_status => l_return_status,
6881: x_num_warnings => l_num_warnings,
6882: x_num_errors => l_num_errors
6927: x_return_status => l_return_status);
6928:
6929: IF l_debug_on THEN
6930: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6931: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6932: END IF;
6933:
6934: wsh_util_core.api_post_call
6935: (
6930: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6931: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6932: END IF;
6933:
6934: wsh_util_core.api_post_call
6935: (
6936: p_return_status => l_return_status,
6937: x_num_warnings => l_num_warnings,
6938: x_num_errors => l_num_errors
6944: l_ind := G_STOP_IDS_STOP_IDS_EXT_CACHE.NEXT(l_ind);
6945: END LOOP;
6946:
6947: IF l_debug_on THEN
6948: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6949: END IF;
6950:
6951:
6952: --call to reset the global cache tables
6949: END IF;
6950:
6951:
6952: --call to reset the global cache tables
6953: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6954:
6955: IF l_debug_on THEN
6956: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6957: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6953: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6954:
6955: IF l_debug_on THEN
6956: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6957: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6958: END IF;
6959:
6960: wsh_util_core.api_post_call
6961: (
6956: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6957: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6958: END IF;
6959:
6960: wsh_util_core.api_post_call
6961: (
6962: p_return_status => l_return_status,
6963: x_num_warnings => l_num_warnings,
6964: x_num_errors => l_num_errors
6966:
6967:
6968: IF l_num_warnings > 0
6969: THEN
6970: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6971: ELSE
6972: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6973: END IF;
6974:
6968: IF l_num_warnings > 0
6969: THEN
6970: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6971: ELSE
6972: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6973: END IF;
6974:
6975: IF l_debug_on THEN
6976: WSH_DEBUG_SV.pop(l_module_name);
6978:
6979:
6980: EXCEPTION
6981: WHEN FND_API.G_EXC_ERROR THEN
6982: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6983: --
6984: -- Debug Statements
6985: --
6986: IF l_debug_on THEN
6984: -- Debug Statements
6985: --
6986: IF l_debug_on THEN
6987: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6988: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6989: END IF;
6990: --
6991: --call to reset the global cache tables
6992: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6988: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6989: END IF;
6990: --
6991: --call to reset the global cache tables
6992: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6993:
6994: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
6995: THEN
6996: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6990: --
6991: --call to reset the global cache tables
6992: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6993:
6994: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
6995: THEN
6996: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6997: END IF;
6998: --
6992: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6993:
6994: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
6995: THEN
6996: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6997: END IF;
6998: --
6999: IF l_debug_on THEN
7000: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
7001: END IF;
7002:
7003:
7004: WHEN others THEN
7005: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7006:
7007: IF l_debug_on THEN
7008: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7009: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
7005: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7006:
7007: IF l_debug_on THEN
7008: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7009: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
7010: END IF;
7011: --
7012: --call to reset the global cache tables
7013: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
7009: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
7010: END IF;
7011: --
7012: --call to reset the global cache tables
7013: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
7014:
7015: IF l_debug_on THEN
7016: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7017: END IF;
7049: wsh_debug_sv.push (l_module_name);
7050: wsh_debug_sv.log(l_module_name,'p_reset_flags',p_reset_flags);
7051: END IF;
7052: --
7053: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7054: --
7055: G_STOP_IDS_STOP_IDS_CACHE.delete;
7056: G_STOP_IDS_STOP_IDS_EXT_CACHE.delete;
7057: --
7068: EXCEPTION
7069:
7070:
7071: WHEN others THEN
7072: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7073: --WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Reset_stops_for_load_tender');
7074: --
7075: -- Debug Statements
7076: --
7069:
7070:
7071: WHEN others THEN
7072: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7073: --WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Reset_stops_for_load_tender');
7074: --
7075: -- Debug Statements
7076: --
7077: IF l_debug_on THEN
7186: EXCEPTION
7187: --
7188: WHEN OTHERS THEN
7189: --
7190: WSH_UTIL_CORE.default_handler('ValidateActualDepartureDate');
7191: --
7192: IF l_debug_on THEN
7193: WSH_DEBUG_SV.pop(l_module_name, 'Exception: WHEN OTHERS');
7194: END IF;
7266: EXCEPTION
7267: --
7268: WHEN OTHERS THEN
7269: --
7270: WSH_UTIL_CORE.default_handler('GetShipConfirmRule');
7271: --
7272: IF l_debug_on THEN
7273: WSH_DEBUG_SV.pop(l_module_name, 'Exception: WHEN OTHERS');
7274: END IF;
7349:
7350: PROCEDURE get_customer_from_loc(
7351: p_location_id IN NUMBER,
7352: --x_customer_id OUT NOCOPY NUMBER,
7353: x_customer_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
7354: x_return_status OUT NOCOPY VARCHAR2)
7355: IS
7356:
7357: CURSOR c_get_customer(c_location_id IN NUMBER) IS
7363: AND wl.location_source_code = 'HZ'
7364: AND wl.source_location_id = hps.location_id
7365: AND hps.party_site_id = hcas.party_site_id;
7366:
7367: l_customer_id_tab WSH_UTIL_CORE.id_tab_type;
7368: itr NUMBER := 0;
7369: i NUMBER := 0;
7370: l_return_status VARCHAR2(1);
7371: l_cust_string VARCHAR2(2000);
7374: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_customer_from_loc';
7375:
7376: BEGIN
7377:
7378: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7379:
7380: IF l_debug_on THEN
7381: wsh_debug_sv.push(l_module_name);
7382: END IF;
7382: END IF;
7383:
7384: IF ( p_location_id < g_cache_max_size and g_customer_from_location.EXISTS(p_location_id)) THEN
7385:
7386: wsh_util_core.get_idtab_from_string(
7387: p_string => g_customer_from_location(p_location_id),
7388: x_id_tab => l_customer_id_tab,
7389: x_return_status => l_return_status);
7390:
7387: p_string => g_customer_from_location(p_location_id),
7388: x_id_tab => l_customer_id_tab,
7389: x_return_status => l_return_status);
7390:
7391: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7392: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7393: END IF;
7394:
7395: itr := l_customer_id_tab.FIRST;
7432: IF (itr) IS NULL THEN
7433: l_cust_string := '-1';
7434: ELSE
7435:
7436: wsh_util_core.get_string_from_idtab(
7437: p_id_tab => l_customer_id_tab,
7438: x_string => l_cust_string,
7439: x_return_status => l_return_status);
7440:
7441: IF l_debug_on THEN
7442: WSH_DEBUG_SV.logmsg(l_module_name,'Org String '||l_cust_string);
7443: END IF;
7444:
7445: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7446: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7447: END IF;
7448:
7449: END IF;
7482: --
7483:
7484: EXCEPTION
7485: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7486: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7487: --
7488: IF l_debug_on THEN
7489: WSH_DEBUG_SV.pop(l_module_name,'FND_API.EXCEPTION:G_EXC_UNEXPECTED_ERROR');
7490: END IF;
7491: WHEN others THEN
7492: IF c_get_customer%ISOPEN THEN
7493: CLOSE c_get_customer;
7494: END IF;
7495: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_customer_from_loc');
7496: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7497: --
7498: IF l_debug_on THEN
7499: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7492: IF c_get_customer%ISOPEN THEN
7493: CLOSE c_get_customer;
7494: END IF;
7495: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_customer_from_loc');
7496: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7497: --
7498: IF l_debug_on THEN
7499: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7500: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
7513: -- Returns table of organizations for location.
7514: --========================================================================
7515: PROCEDURE get_org_from_location(
7516: p_location_id IN NUMBER,
7517: x_organization_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
7518: x_return_status OUT NOCOPY VARCHAR2)
7519: IS
7520: /*
7521: CURSOR c_get_org_from_loc(c_location_id IN NUMBER) IS
7529: SELECT organization_id
7530: FROM wsh_ship_from_orgs_v
7531: WHERE location_id = c_location_id;
7532:
7533: l_organization_tab WSH_UTIL_CORE.id_tab_type;
7534:
7535: itr NUMBER;
7536: i NUMBER;
7537: l_return_status VARCHAR2(1);
7540: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
7541: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_org_from_location';
7542:
7543: BEGIN
7544: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7545:
7546: IF l_debug_on THEN
7547: wsh_debug_sv.push(l_module_name);
7548: END IF;
7548: END IF;
7549:
7550: IF (p_location_id < g_cache_max_size and g_organization_from_location.EXISTS(p_location_id)) THEN
7551:
7552: wsh_util_core.get_idtab_from_string(
7553: p_string => g_organization_from_location(p_location_id),
7554: x_id_tab => l_organization_tab,
7555: x_return_status => l_return_status);
7556:
7553: p_string => g_organization_from_location(p_location_id),
7554: x_id_tab => l_organization_tab,
7555: x_return_status => l_return_status);
7556:
7557: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7558: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7559: END IF;
7560:
7561: itr := l_organization_tab.FIRST;
7586: IF (itr) IS NULL THEN
7587: l_org_string := '-1';
7588: ELSE
7589:
7590: wsh_util_core.get_string_from_idtab(
7591: p_id_tab => l_organization_tab,
7592: x_string => l_org_string,
7593: x_return_status => l_return_status);
7594:
7595: IF l_debug_on THEN
7596: WSH_DEBUG_SV.logmsg(l_module_name,'Org String '||l_org_string);
7597: END IF;
7598:
7599: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7600: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7601: END IF;
7602:
7603: END IF;
7632: --
7633:
7634: EXCEPTION
7635: WHEN others THEN
7636: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7637:
7638: IF c_get_org_from_loc%ISOPEN THEN
7639: CLOSE c_get_org_from_loc;
7640: END IF;
7638: IF c_get_org_from_loc%ISOPEN THEN
7639: CLOSE c_get_org_from_loc;
7640: END IF;
7641:
7642: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_org_from_loc');
7643:
7644: --
7645: IF l_debug_on THEN
7646: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7723: WSH_DEBUG_SV.log(l_module_name, 'p_entity_id', p_entity_id);
7724: --
7725: END IF;
7726: --
7727: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7728: --
7729:
7730: IF ( p_entity_type = 'DELIVERY' ) THEN
7731: --
7791: END IF;
7792: --
7793: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_INVALID_ENTITY_TYPE' );
7794: FND_MESSAGE.SET_TOKEN ('ENT_TYPE', p_entity_type );
7795: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7796: WSH_UTIL_CORE.add_message(x_return_status);
7797: --
7798: END IF;
7799:
7792: --
7793: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_INVALID_ENTITY_TYPE' );
7794: FND_MESSAGE.SET_TOKEN ('ENT_TYPE', p_entity_type );
7795: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7796: WSH_UTIL_CORE.add_message(x_return_status);
7797: --
7798: END IF;
7799:
7800: --
7803:
7804: IF ( l_error_flag ) THEN
7805: --
7806: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_DET_INVALID_DEL' );
7807: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7808: WSH_UTIL_CORE.add_message(x_return_status);
7809: --
7810: END IF;
7811:
7804: IF ( l_error_flag ) THEN
7805: --
7806: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_DET_INVALID_DEL' );
7807: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7808: WSH_UTIL_CORE.add_message(x_return_status);
7809: --
7810: END IF;
7811:
7812: --
7818: --
7819:
7820: EXCEPTION
7821: WHEN OTHERS THEN
7822: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
7823: wsh_util_core.default_handler('WSH_DELIVERY_VALIDATIONS.Get_Delivery_Status');
7824: --
7825: IF l_debug_on THEN
7826: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7819:
7820: EXCEPTION
7821: WHEN OTHERS THEN
7822: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
7823: wsh_util_core.default_handler('WSH_DELIVERY_VALIDATIONS.Get_Delivery_Status');
7824: --
7825: IF l_debug_on THEN
7826: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7827: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
7991: WSH_DEBUG_SV.push(l_module_name);
7992: END IF;
7993:
7994: x_curr_conv_type := NULL;
7995: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7996:
7997: -- no cached value, call fnd_profile.value
7998: IF G_CURRENCY_CONVERSION_TYPE IS NULL then
7999: IF l_debug_on THEN
8015: END IF;
8016:
8017: EXCEPTION
8018: when CURR_CONV_TYPE_UNDEFINED_EXP then
8019: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8020: fnd_message.set_name('WSH', 'WSH_CURR_CONV_TYPE_UNDEFINED');
8021: wsh_util_core.add_message(x_return_status);
8022: IF l_debug_on THEN
8023: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
8017: EXCEPTION
8018: when CURR_CONV_TYPE_UNDEFINED_EXP then
8019: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8020: fnd_message.set_name('WSH', 'WSH_CURR_CONV_TYPE_UNDEFINED');
8021: wsh_util_core.add_message(x_return_status);
8022: IF l_debug_on THEN
8023: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
8024: WSH_DEBUG_SV.logmsg(l_module_name, 'WSH: Currency Conversion Type for OTM profile value is not defined.');
8025: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION');
8025: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION');
8026: END IF;
8027:
8028: when OTHERS then
8029: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8030: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_UTIL_CORE.get_currency_conversion_type', l_module_name);
8031:
8032: IF l_debug_on THEN
8033: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
8026: END IF;
8027:
8028: when OTHERS then
8029: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8030: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_UTIL_CORE.get_currency_conversion_type', l_module_name);
8031:
8032: IF l_debug_on THEN
8033: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
8034: WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8086: IF l_debug_on THEN
8087: WSH_DEBUG_SV.push(l_module_name);
8088: END IF;
8089:
8090: l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8091: l_number_of_warnings := 0;
8092: l_number_of_errors := 0;
8093:
8094:
8112: x_first_stop_loc_id => l_first_stop_loc_id,
8113: x_last_stop_loc_id => l_last_stop_loc_id,
8114: p_trip_id => p_trip_id);
8115:
8116: wsh_util_core.api_post_call(
8117: p_return_status =>l_return_status,
8118: x_num_warnings =>l_number_of_warnings,
8119: x_num_errors =>l_number_of_errors,
8120: p_msg_data =>l_msg_data);
8292: IF l_debug_on THEN
8293: WSH_DEBUG_SV.push(l_module_name);
8294: END IF;
8295:
8296: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8297:
8298: IF l_debug_on
8299: THEN
8300: WSH_DEBUG_SV.logmsg(l_module_name,' Getting trip stop information ',
8374: --}
8375: EXCEPTION
8376: --{
8377: WHEN FND_API.G_EXC_ERROR THEN
8378: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8379: IF l_debug_on THEN
8380: WSH_DEBUG_SV.pop(l_module_name);
8381: END IF;
8382:
8385: IF l_debug_on THEN
8386: WSH_DEBUG_SV.pop(l_module_name);
8387: END IF;
8388: WHEN OTHERS THEN
8389: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8390:
8391: IF GET_TRIP_STOPS_PICKUP%ISOPEN THEN
8392: CLOSE GET_TRIP_STOPS_PICKUP;
8393: END IF;
8475: --
8476: RETURN l_org_id;
8477: EXCEPTION
8478: WHEN OTHERS THEN
8479: WSH_UTIL_CORE.Default_Handler('WSH_SHIPMENT_REQUEST_PKG.Get_Operating_Unit');
8480: --
8481: IF l_debug_on THEN
8482: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8483: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8605: return l_fulfillment_base;
8606: END Get_Line_Fulfillment_Base;
8607:
8608:
8609: END WSH_UTIL_CORE;