[Home] [Help]
1: PACKAGE BODY WSH_UTIL_CORE as
2: /* $Header: WSHUTCOB.pls 120.21.12010000.3 2008/08/22 08:06:08 mvudugul ship $ */
3:
4: /* H projects: pricing integration csun
5: add global variable to cache the value so in the same session
52:
53:
54: -- Description: Generic tab of records for passing column information
55: g_loc_desc_Tab Loc_Info_Tab;
56: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_UTIL_CORE';
57:
58: --Cache size constant
59: g_cache_max_size NUMBER := power(2,31);
60: --
59: g_cache_max_size NUMBER := power(2,31);
60: --
61:
62: --Cache for retriving Customer/Supplier/Carrier/Organization from locations.
63: g_customer_from_location WSH_UTIL_CORE.tbl_varchar;
64: g_organization_from_location WSH_UTIL_CORE.tbl_varchar;
65:
66: --
67:
60: --
61:
62: --Cache for retriving Customer/Supplier/Carrier/Organization from locations.
63: g_customer_from_location WSH_UTIL_CORE.tbl_varchar;
64: g_organization_from_location WSH_UTIL_CORE.tbl_varchar;
65:
66: --
67:
68: --
231: END IF;
232: ELSE -- bad format_code
233: FND_MESSAGE.SET_NAME('WSH','WSH_PUB_INVALID_PARAMETER');
234: FND_MESSAGE.Set_Token('PARAMETER',p_format);
235: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,
236: l_module_name);
237: IF l_debug_on THEN
238: WSH_DEBUG_SV.pop(l_module_name);
239: END IF;
266: -- PROCEDURE: Site_Code_to_Site_id
267: -- PURPOSE : Maps site_code to site_id
268: -- ARGUMENTS : p_site_code - site code that needs to be mapped
269: -- p_site_id - site id for the code
270: -- x_return_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS or NOT
271: --
272:
273: PROCEDURE Site_Code_to_Site_id(p_site_code IN VARCHAR2,
274: p_site_id OUT NOCOPY NUMBER,
303: WSH_DEBUG_SV.log(l_module_name,'P_SITE_CODE',P_SITE_CODE);
304: END IF;
305: --
306: p_site_id := NULL;
307: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
308:
309: OPEN get_id(p_site_code);
310: FETCH get_id INTO p_site_id;
311: CLOSE get_id;
311: CLOSE get_id;
312:
313: IF (p_site_id IS NULL) THEN
314: FND_MESSAGE.Set_Name('WSH','WSH_SITE_LOCATION_UNDEFINED');
315: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
316: IF get_id%ISOPEN THEN
317: CLOSE get_id;
318: END IF;
319: END IF;
329: when others then
330: IF get_id%ISOPEN THEN
331: CLOSE get_id;
332: END IF;
333: wsh_util_core.default_handler('WSH_UTIL_CORE.Site_Code_to_Site_id');
334: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
335:
336: --
337: -- Debug Statements
330: IF get_id%ISOPEN THEN
331: CLOSE get_id;
332: END IF;
333: wsh_util_core.default_handler('WSH_UTIL_CORE.Site_Code_to_Site_id');
334: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
335:
336: --
337: -- Debug Statements
338: --
353: -- Arguments: p_mode - 'CUSTOMER SITE', 'VENDOR SITE' or 'ORG'
354: -- p_source_id - organization_id or site_id to convert
355: -- based on p_mode
356: -- x_location_id - Converted to location_id
357: -- x_api_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS
358: -- WSH_UTIL_CORE.G_RET_STS_ERROR
359: -- If Error message can be retrieved using FND_MESSAGE.GET
360: -- Description: Gets location information for a particular inventory
361: -- organization using hr_locations view
354: -- p_source_id - organization_id or site_id to convert
355: -- based on p_mode
356: -- x_location_id - Converted to location_id
357: -- x_api_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS
358: -- WSH_UTIL_CORE.G_RET_STS_ERROR
359: -- If Error message can be retrieved using FND_MESSAGE.GET
360: -- Description: Gets location information for a particular inventory
361: -- organization using hr_locations view
362: --
414: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_ID',P_SOURCE_ID);
415: WSH_DEBUG_SV.log(l_module_name,'p_transfer_location',p_transfer_location);
416: END IF;
417: --
418: x_api_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
419:
420: x_location_id := null;
421:
422: IF p_mode = 'ORG' THEN
440: --Bug 4891887 and 4891881 If call is made from Constraints then ignore the warning
441: IF WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API IS NULL THEN
442: --The following message is modified as part of bug # 4256319
443: FND_MESSAGE.Set_Name('WSH','WSH_DET_NO_LOCATION_FOR_ORG');
444: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
445: wsh_util_core.add_message(x_api_status,l_module_name);
446: ELSE
447: WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API :=NULL;
448: x_location_id :=l_location_id;
441: IF WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API IS NULL THEN
442: --The following message is modified as part of bug # 4256319
443: FND_MESSAGE.Set_Name('WSH','WSH_DET_NO_LOCATION_FOR_ORG');
444: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
445: wsh_util_core.add_message(x_api_status,l_module_name);
446: ELSE
447: WSH_FTE_CONSTRAINT_FRAMEWORK.G_CALLING_API :=NULL;
448: x_location_id :=l_location_id;
449: END IF;
472: l_return_status);
473: WSH_DEBUG_SV.log(l_module_name,'location_id returned ',
474: l_loc_rec.wsh_location_id);
475: END IF;
476: wsh_util_core.api_post_call(
477: p_return_status => l_return_status,
478: x_num_warnings => l_num_warnings,
479: x_num_errors => l_num_errors);
480:
483: END IF;
484:
485: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
486: OR (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
487: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
488: END IF;
489: g_org_Id_Tab(p_source_id) := l_loc_rec.WSH_LOCATION_ID;
490: x_location_id := l_loc_rec.WSH_LOCATION_ID;
491: END IF;
505: CLOSE site_to_loc;
506:
507: IF (l_location_id IS NULL) THEN
508: FND_MESSAGE.Set_Name('WSH','WSH_SITE_LOCATION_UNDEFINED');
509: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
510: wsh_util_core.add_message(x_api_status,l_module_name);
511: IF site_to_loc%ISOPEN THEN
512: CLOSE site_to_loc;
513: END IF;
506:
507: IF (l_location_id IS NULL) THEN
508: FND_MESSAGE.Set_Name('WSH','WSH_SITE_LOCATION_UNDEFINED');
509: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
510: wsh_util_core.add_message(x_api_status,l_module_name);
511: IF site_to_loc%ISOPEN THEN
512: CLOSE site_to_loc;
513: END IF;
514: --
529: p_online_region_mapping => FALSE,
530: x_loc_rec => l_loc_rec,
531: x_return_status => l_return_status
532: );
533: wsh_util_core.api_post_call(
534: p_return_status => l_return_status,
535: x_num_warnings => l_num_warnings,
536: x_num_errors => l_num_errors);
537:
540: END IF;
541:
542: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS )
543: OR (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
544: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
545: END IF;
546: g_cust_Id_Tab(p_source_id) := l_loc_rec.WSH_LOCATION_ID;
547: x_location_id := l_loc_rec.WSH_LOCATION_ID;
548:
559: --
560: RETURN;
561: ELSE
562: FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
563: x_api_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
564: --
565: IF l_debug_on THEN
566: WSH_DEBUG_SV.pop(l_module_name);
567: END IF;
573: WSH_DEBUG_SV.pop(l_module_name);
574: END IF;
575: --
576: EXCEPTION
577: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
578: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
579: fnd_message.set_name('WSH','WSH_XC_INVALID_LOCATION');
580: wsh_util_core.add_message(x_api_status,l_module_name);
581: --
574: END IF;
575: --
576: EXCEPTION
577: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
578: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
579: fnd_message.set_name('WSH','WSH_XC_INVALID_LOCATION');
580: wsh_util_core.add_message(x_api_status,l_module_name);
581: --
582: IF l_debug_on THEN
576: EXCEPTION
577: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
578: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
579: fnd_message.set_name('WSH','WSH_XC_INVALID_LOCATION');
580: wsh_util_core.add_message(x_api_status,l_module_name);
581: --
582: IF l_debug_on THEN
583: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_RET_STS_WARNING');
584: END IF;
583: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_RET_STS_WARNING');
584: END IF;
585: --
586: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
587: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
588: --
589: IF l_debug_on THEN
590: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_EXC_UNEXPECTED_ERROR');
591: END IF;
592: --
593: WHEN FND_API.G_EXC_ERROR THEN
594: IF (NOT p_transfer_location)
595: AND (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
596: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
597: ELSE
598: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
599: END IF;
600: --
594: IF (NOT p_transfer_location)
595: AND (l_loc_rec.WSH_LOCATION_ID IS NULL) THEN
596: x_api_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
597: ELSE
598: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
599: END IF;
600: --
601: IF l_debug_on THEN
602: WSH_DEBUG_SV.log(l_module_name,'x_api_status ',x_api_status);
611: IF site_to_loc%ISOPEN THEN
612: CLOSE site_to_loc;
613: END IF;
614: FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
615: x_api_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
616:
617: --
618: IF l_debug_on THEN
619: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
628: -- PURPOSE: Obtain master organization id for an organization_id
629: -- Arguments: p_org_id - organization_id
630: -- x_master_org_id - Master organization id for input organization_id
631: -- x_return_status -
632: -- WSH_UTIL_CORE.G_RET_STS_SUCCESS
633: -- WSH_UTIL_CORE.G_RET_STS_ERROR
634: -- Notes: Throws exception when fails
635: --
636:
629: -- Arguments: p_org_id - organization_id
630: -- x_master_org_id - Master organization id for input organization_id
631: -- x_return_status -
632: -- WSH_UTIL_CORE.G_RET_STS_SUCCESS
633: -- WSH_UTIL_CORE.G_RET_STS_ERROR
634: -- Notes: Throws exception when fails
635: --
636:
637: PROCEDURE get_master_from_org(
651: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_master_from_org';
652:
653: BEGIN
654:
655: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
656:
657: --
658: IF l_debug_on THEN
659: WSH_DEBUG_SV.push(l_module_name);
685: --
686:
687: EXCEPTION
688: WHEN org_not_found THEN
689: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
690: --
691: IF l_debug_on THEN
692: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:org in mtl_parameters not found');
693: END IF;
695: WHEN others THEN
696: IF c_get_master_from_org%ISOPEN THEN
697: CLOSE c_get_master_from_org;
698: END IF;
699: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_master_from_org');
700: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
701: --
702: IF l_debug_on THEN
703: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
696: IF c_get_master_from_org%ISOPEN THEN
697: CLOSE c_get_master_from_org;
698: END IF;
699: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_master_from_org');
700: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
701: --
702: IF l_debug_on THEN
703: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
704: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
753: l_location_id,
754: l_api_status,
755: p_transfer_location);
756:
757: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
758: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
759: l_location_id := NULL;
760: ELSE
761: RAISE NO_DATA_FOUND;
754: l_api_status,
755: p_transfer_location);
756:
757: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
758: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
759: l_location_id := NULL;
760: ELSE
761: RAISE NO_DATA_FOUND;
762: END IF;
822: l_location_id,
823: l_api_status,
824: p_transfer_location);
825:
826: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
827: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
828: l_location_id := NULL;
829: ELSE
830: RAISE NO_DATA_FOUND;
823: l_api_status,
824: p_transfer_location);
825:
826: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
827: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
828: l_location_id := NULL;
829: ELSE
830: RAISE NO_DATA_FOUND;
831: END IF;
890: l_location_id,
891: l_api_status,
892: p_transfer_location);
893:
894: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
895: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
896: l_location_id := NULL;
897: ELSE
898: RAISE NO_DATA_FOUND;
891: l_api_status,
892: p_transfer_location);
893:
894: IF l_api_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
895: IF l_api_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
896: l_location_id := NULL;
897: ELSE
898: RAISE NO_DATA_FOUND;
899: END IF;
985:
986: IF (get_freight%NOTFOUND) THEN
987: CLOSE get_freight;
988: fnd_message.set_name('WSH','WSH_FREIGHT_CODE_NOT_FOUND');
989: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
990: RAISE NO_DATA_FOUND;
991: END IF;
992:
993: CLOSE get_freight;
1660: EXCEPTION
1661: WHEN OTHERS THEN
1662: dbms_sql.close_cursor(rec_cursor);
1663: FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1664: FND_MESSAGE.Set_Token('PACKAGE','WSH_UTIL_CORE');
1665: FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1666: FND_MESSAGE.Set_Token('ORA_TEXT',query_text);
1667: APP_EXCEPTION.Raise_Exception;
1668: --
1729: EXCEPTION
1730: WHEN OTHERS THEN
1731: dbms_sql.close_cursor(rec_cursor);
1732: FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1733: FND_MESSAGE.Set_Token('PACKAGE','WSH_UTIL_CORE');
1734: FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1735: FND_MESSAGE.Set_Token('ORA_TEXT',query_text);
1736: APP_EXCEPTION.Raise_Exception;
1737: --
1794: l_message_type := 'W';
1795: l_exception_level := WSH_DEBUG_SV.C_ERR_LEVEL;
1796: END IF;
1797:
1798: --IF WSH_UTIL_CORE.G_STORE_MSG_IN_TABLE = FALSE THEN
1799: IF G_STORE_MSG_IN_TABLE = FALSE THEN
1800: IF (l_message_type IS NOT NULL) THEN
1801: msg_buffer := fnd_message.get;
1802: IF msg_buffer is not null THEN
2043: FND_MESSAGE.SET_TOKEN('NUM_ERROR', l_errors);
2044: FND_MESSAGE.SET_TOKEN('NUM_WARN', l_warnings);
2045: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS', l_total - l_errors - l_warnings);
2046:
2047: wsh_util_core.add_message(p_return_status, p_module_name);
2048: END IF;
2049:
2050: IF (l_total = l_errors) THEN
2051: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2047: wsh_util_core.add_message(p_return_status, p_module_name);
2048: END IF;
2049:
2050: IF (l_total = l_errors) THEN
2051: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2052: ELSE
2053: p_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2054: END IF;
2055: ELSE
2049:
2050: IF (l_total = l_errors) THEN
2051: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2052: ELSE
2053: p_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2054: END IF;
2055: ELSE
2056: p_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2057: END IF;
2052: ELSE
2053: p_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2054: END IF;
2055: ELSE
2056: p_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2057: END IF;
2058:
2059: --
2060: -- Debug Statements
2209: FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
2210: FND_MESSAGE.Set_Token('PACKAGE',p_routine_name);
2211: FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
2212: FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
2213: WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_UNEXP_ERROR, p_module_name);
2214:
2215: --
2216: -- Debug Statements
2217: --
2494: --
2495: --tkt
2496: PROCEDURE Delete(
2497: p_type wsh_saved_queries_vl.entity_type%type,
2498: p_rows IN OUT NOCOPY wsh_util_core.id_tab_type,
2499: p_caller IN VARCHAR2,
2500: x_return_status OUT NOCOPY VARCHAR2)
2501: is
2502:
2510: l_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
2511: l_trip_rec WSH_TRIPS_PVT.trip_rec_type;
2512: -- bmso
2513: l_lpn_ids WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2514: l_lpn_id_tab wsh_util_core.id_tab_type;
2515:
2516: l_purged_lpns WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2517:
2518: l_validate_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_VALIDATE;
2516: l_purged_lpns WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2517:
2518: l_validate_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_VALIDATE;
2519: l_delete_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_DELETE;
2520: l_rows_lpns wsh_util_core.id_tab_type;
2521: l_cached_lpns WSH_UTIL_CORE.Id_Tab_Type;
2522: l_msg_data VARCHAR2(32000);
2523: l_msg_count NUMBER;
2524: l_index NUMBER;
2517:
2518: l_validate_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_VALIDATE;
2519: l_delete_mode VARCHAR2(100) := wms_container_grp.G_LPN_PURGE_ACTION_DELETE;
2520: l_rows_lpns wsh_util_core.id_tab_type;
2521: l_cached_lpns WSH_UTIL_CORE.Id_Tab_Type;
2522: l_msg_data VARCHAR2(32000);
2523: l_msg_count NUMBER;
2524: l_index NUMBER;
2525: l_count NUMBER;
2576: num_errors := 0;
2577: num_warnings := 0;
2578: /* added this check for FTE */
2579: /* so we will not do this for each record */
2580: x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2581:
2582: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y'
2583: AND p_type IN ('TRIP','STOP'))THEN
2584: l_fte_flag := 'Y';
2578: /* added this check for FTE */
2579: /* so we will not do this for each record */
2580: x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2581:
2582: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y'
2583: AND p_type IN ('TRIP','STOP'))THEN
2584: l_fte_flag := 'Y';
2585:
2586: END IF;
2633: , p_action => l_validate_mode
2634: , p_lpn_purge_rec => l_lpn_ids
2635:
2636: );
2637: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2639: END IF;
2640: IF l_lpn_ids.lpn_ids.COUNT > 0 THEN
2641:
2663: wsh_trips_grp.get_trip_details_pvt
2664: (p_trip_id => p_rows(i),
2665: x_trip_rec => l_trip_rec,
2666: x_return_status => l_return_status);
2667: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2668: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2669: num_warnings := num_warnings + 1;
2670: ELSE
2671: x_return_status := l_return_status;
2664: (p_trip_id => p_rows(i),
2665: x_trip_rec => l_trip_rec,
2666: x_return_status => l_return_status);
2667: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2668: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2669: num_warnings := num_warnings + 1;
2670: ELSE
2671: x_return_status := l_return_status;
2672: --
2693: p_trip_rec => l_trip_rec,
2694: p_action => 'DELETE',
2695: x_return_status => l_return_status);
2696:
2697: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2698: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2699: num_warnings := num_warnings + 1;
2700: ELSE
2701: x_return_status := l_return_status;
2694: p_action => 'DELETE',
2695: x_return_status => l_return_status);
2696:
2697: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2698: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2699: num_warnings := num_warnings + 1;
2700: ELSE
2701: x_return_status := l_return_status;
2702: --
2722: wsh_trip_stops_grp.get_stop_details_pvt
2723: (p_stop_id => p_rows(i),
2724: x_stop_rec => l_stop_rec,
2725: x_return_status => l_return_status);
2726: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2727: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2728: num_warnings := num_warnings + 1;
2729: ELSE
2730: x_return_status := l_return_status;
2723: (p_stop_id => p_rows(i),
2724: x_stop_rec => l_stop_rec,
2725: x_return_status => l_return_status);
2726: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2727: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2728: num_warnings := num_warnings + 1;
2729: ELSE
2730: x_return_status := l_return_status;
2731: --
2751: (p_stop_rec => l_stop_rec,
2752: p_trip_rec => l_trip_rec,
2753: p_action => 'DELETE',
2754: x_return_status => l_return_status);
2755: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2756: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2757: num_warnings := num_warnings + 1;
2758: ELSE
2759: x_return_status := l_return_status;
2752: p_trip_rec => l_trip_rec,
2753: p_action => 'DELETE',
2754: x_return_status => l_return_status);
2755: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2756: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2757: num_warnings := num_warnings + 1;
2758: ELSE
2759: x_return_status := l_return_status;
2760: --
2813:
2814: -- wsh_delivery_legs_pvt.delete_delivery_leg(null, p_rows(i), cur_return_status);
2815:
2816: fnd_message.set_name('WSH','DEL_DLEG_NOT_IMPLEMENTED_YET');
2817: cur_return_status := wsh_util_core.g_ret_sts_error;
2818: wsh_util_core.add_message(cur_return_status);
2819:
2820: elsif (p_type = 'DLVB') then
2821: --
2814: -- wsh_delivery_legs_pvt.delete_delivery_leg(null, p_rows(i), cur_return_status);
2815:
2816: fnd_message.set_name('WSH','DEL_DLEG_NOT_IMPLEMENTED_YET');
2817: cur_return_status := wsh_util_core.g_ret_sts_error;
2818: wsh_util_core.add_message(cur_return_status);
2819:
2820: elsif (p_type = 'DLVB') then
2821: --
2822: -- Debug Statements
2835: END IF; --}
2836:
2837: IF l_delivery_detail_id IS NOT NULL THEN
2838: WSH_CONTAINER_ACTIONS.delete_containers(p_rows(i), cur_return_status);
2839: IF cur_return_status NOT IN (wsh_util_core.g_ret_sts_success,
2840: wsh_util_core.g_ret_sts_warning)
2841: THEN
2842: update wsh_lpn_purge_tmp
2843: SET eligible_flag = 'N'
2836:
2837: IF l_delivery_detail_id IS NOT NULL THEN
2838: WSH_CONTAINER_ACTIONS.delete_containers(p_rows(i), cur_return_status);
2839: IF cur_return_status NOT IN (wsh_util_core.g_ret_sts_success,
2840: wsh_util_core.g_ret_sts_warning)
2841: THEN
2842: update wsh_lpn_purge_tmp
2843: SET eligible_flag = 'N'
2844: WHERE delivery_detail_id = p_rows(i);
2843: SET eligible_flag = 'N'
2844: WHERE delivery_detail_id = p_rows(i);
2845: END IF;
2846: ELSE
2847: cur_return_status := wsh_util_core.g_ret_sts_error;
2848: END IF;
2849:
2850: end if;
2851:
2848: END IF;
2849:
2850: end if;
2851:
2852: if (cur_return_status <> wsh_util_core.g_ret_sts_success) then
2853:
2854: if (cur_return_status = wsh_util_core.g_ret_sts_error) OR (cur_return_status = wsh_util_core.g_ret_sts_unexp_error) then
2855: p_rows(i) := -p_rows(i);
2856: end if;
2850: end if;
2851:
2852: if (cur_return_status <> wsh_util_core.g_ret_sts_success) then
2853:
2854: if (cur_return_status = wsh_util_core.g_ret_sts_error) OR (cur_return_status = wsh_util_core.g_ret_sts_unexp_error) then
2855: p_rows(i) := -p_rows(i);
2856: end if;
2857:
2858:
2855: p_rows(i) := -p_rows(i);
2856: end if;
2857:
2858:
2859: if (cur_return_status = wsh_util_core.g_ret_sts_warning) then
2860: num_warnings := num_warnings + 1;
2861: else
2862: num_errors := num_errors + 1;
2863: end if;
2885: , p_action => l_delete_mode
2886: , p_lpn_purge_rec => l_purged_lpns
2887:
2888: );
2889: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2890: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2891: END IF;
2892: END IF;
2893: END IF; --}
2914: WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2915: END IF;
2916: --
2917: --
2918: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2919: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2920: num_warnings := num_warnings + 1;
2921: ELSE
2922: x_return_status := l_return_status;
2915: END IF;
2916: --
2917: --
2918: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2919: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2920: num_warnings := num_warnings + 1;
2921: ELSE
2922: x_return_status := l_return_status;
2923: --
2934: -- K LPN CONV. rv
2935: --
2936:
2937: if (num_errors > 0) then
2938: x_return_status := wsh_util_core.g_ret_sts_error;
2939: elsif (num_warnings >0) then
2940: x_return_status := wsh_util_core.g_ret_sts_warning;
2941: end if;
2942:
2936:
2937: if (num_errors > 0) then
2938: x_return_status := wsh_util_core.g_ret_sts_error;
2939: elsif (num_warnings >0) then
2940: x_return_status := wsh_util_core.g_ret_sts_warning;
2941: end if;
2942:
2943: if (p_type = 'TRIP') then
2944: l_message := 'WSH_TRIP_DELETE_SUMMARY';
2951: elsif (p_type = 'DLVB') then
2952: l_message := 'WSH_DLVB_DELETE_SUMMARY';
2953: end if;
2954:
2955: wsh_util_core.add_summary_message(
2956: l_message,
2957: p_rows.count,
2958: num_warnings,
2959: num_errors,
2989: --
2990: IF l_debug_on THEN
2991: WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
2992: END IF;
2993: 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
2994: x_return_status := l_return_status;
2995: END IF;
2996: --}
2997: END IF;
3002: -- LPN CONV. rv
3003: --
3004: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3005: ROLLBACK TO s_delete_savepoint;
3006: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3007:
3008: IF l_debug_on THEN
3009: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle
3010: error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3011: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3012: END IF;
3013: WHEN OTHERS THEN
3014: ROLLBACK TO s_delete_savepoint;
3015: wsh_util_core.default_handler('WSH_UTIL_CORE.DELETE');
3016: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3017:
3018: --
3019: -- Debug Statements
3012: END IF;
3013: WHEN OTHERS THEN
3014: ROLLBACK TO s_delete_savepoint;
3015: wsh_util_core.default_handler('WSH_UTIL_CORE.DELETE');
3016: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3017:
3018: --
3019: -- Debug Statements
3020: --
3313: END IF;
3314: --
3315: x_ledger_id := NULL;
3316: x_func_currency := NULL;
3317: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3318: --
3319: IF p_org_id = G_OPERATING_UNIT_INFO.org_id THEN
3320: x_ledger_id := G_OPERATING_UNIT_INFO.ledger_id;
3321: x_func_currency := G_OPERATING_UNIT_INFO.currency_code;
3354: CLOSE c_ledger_and_func_curr;
3355: END IF;
3356: x_ledger_id := NULL;
3357: x_func_currency := NULL;
3358: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3359: fnd_message.set_name('WSH', 'WSH_LEDGER_ID_NOT_FOUND');
3360: WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3361:
3362: IF l_debug_on THEN
3356: x_ledger_id := NULL;
3357: x_func_currency := NULL;
3358: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3359: fnd_message.set_name('WSH', 'WSH_LEDGER_ID_NOT_FOUND');
3360: WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3361:
3362: IF l_debug_on THEN
3363: WSH_DEBUG_SV.pop(l_module_name);
3364: END IF;
3365:
3366: WHEN others THEN
3367: x_ledger_id := NULL;
3368: x_func_currency := NULL;
3369: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3370: IF c_ledger_and_func_curr%ISOPEN THEN
3371: CLOSE c_ledger_and_func_curr;
3372: END IF;
3373: wsh_util_core.default_handler('WSH_UTIL_CORE.Get_Ledger_id_Func_Currency');
3369: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3370: IF c_ledger_and_func_curr%ISOPEN THEN
3371: CLOSE c_ledger_and_func_curr;
3372: END IF;
3373: wsh_util_core.default_handler('WSH_UTIL_CORE.Get_Ledger_id_Func_Currency');
3374: -- Debug Statements
3375: --
3376: IF l_debug_on THEN
3377: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3390: -- Input Arguments
3391: --
3392: --
3393: PROCEDURE Print_Label(
3394: p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3395: p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3396: x_return_status OUT NOCOPY VARCHAR2)
3397: IS
3398:
3391: --
3392: --
3393: PROCEDURE Print_Label(
3394: p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3395: p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
3396: x_return_status OUT NOCOPY VARCHAR2)
3397: IS
3398:
3399: CURSOR get_delivery_wms(x_delivery_id NUMBER) IS
3453: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID.COUNT',p_delivery_ids.count);
3454: WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID.COUNT',p_stop_ids.count);
3455: END IF;
3456: --
3457: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
3458: l_del_tab.delete;
3459: j := 0;
3460: IF p_delivery_ids.count > 0 THEN
3461: FOR i in 1..p_delivery_ids.count LOOP
3500: p_transaction_id => l_del_tab);
3501:
3502: IF (l_label_return_status <> 'S') THEN
3503: FND_MESSAGE.SET_NAME('WSH','WSH_PRINT_LABEL_ERROR');
3504: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3505: wsh_util_core.add_message(x_return_status);
3506: END IF;
3507: END IF;
3508:
3501:
3502: IF (l_label_return_status <> 'S') THEN
3503: FND_MESSAGE.SET_NAME('WSH','WSH_PRINT_LABEL_ERROR');
3504: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3505: wsh_util_core.add_message(x_return_status);
3506: END IF;
3507: END IF;
3508:
3509: --
3514: END IF;
3515: --
3516: EXCEPTION
3517: WHEN others THEN
3518: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3519: IF get_delivery_wms%ISOPEN THEN
3520: CLOSE get_delivery_wms;
3521: END IF;
3522:
3522:
3523: IF pickup_deliveries_wms%ISOPEN THEN
3524: CLOSE pickup_deliveries_wms;
3525: END IF;
3526: wsh_util_core.default_handler('WSH_UTIL_CORE.Print_Label');
3527:
3528: --
3529: -- Debug Statements
3530: --
3744: --
3745: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_DELIVERY_LEG_ID);
3746: END IF;
3747: --
3748: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
3749: OPEN c_get_trip_name;
3750: FETCH c_get_trip_name INTO l_trip_name, l_reprice_required;
3751: IF c_get_trip_name%NOTFOUND THEN
3752: x_trip_name := NULL;
3767: END IF;
3768: --
3769: EXCEPTION
3770: WHEN others THEN
3771: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3772: wsh_util_core.default_handler('WSH_UTIL_CORE.Print_Label');
3773:
3774: --
3775: -- Debug Statements
3768: --
3769: EXCEPTION
3770: WHEN others THEN
3771: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3772: wsh_util_core.default_handler('WSH_UTIL_CORE.Print_Label');
3773:
3774: --
3775: -- Debug Statements
3776: --
3822: --
3823: --
3824: IF p_return_status IS NULL THEN
3825: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3826: ELSIF p_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3827: x_num_errors := nvl(x_num_errors,0) + 1;
3828: IF p_raise_error_flag THEN
3829: raise FND_API.G_EXC_ERROR;
3830: END IF;
3827: x_num_errors := nvl(x_num_errors,0) + 1;
3828: IF p_raise_error_flag THEN
3829: raise FND_API.G_EXC_ERROR;
3830: END IF;
3831: ELSIF p_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3832: x_num_errors := nvl(x_num_errors,0) + 1;
3833: IF p_raise_error_flag THEN
3834: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3835: END IF;
3832: x_num_errors := nvl(x_num_errors,0) + 1;
3833: IF p_raise_error_flag THEN
3834: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3835: END IF;
3836: ELSIF p_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3837: x_num_warnings := nvl(x_num_warnings,0) + 1;
3838: END IF;
3839: --
3840: --
3915: END IF;
3916: */
3917:
3918: --
3919: IF (p_msg_data IS NOT NULL AND p_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)THEN
3920: add_message(
3921: p_message_type => p_return_status,
3922: p_module_name => p_module_name,
3923: p_error_name => p_msg_data,
4007:
4008: RETURN l_org_id;
4009: EXCEPTION
4010: WHEN OTHERS THEN
4011: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_OPERATINGUNIT_ID',
4012: l_module_name);
4013: IF l_debug_on THEN
4014: WSH_DEBUG_SV.pop(l_module_name);
4015: END IF;
4026: -- p_store_flag
4027: --
4028: PROCEDURE Store_Msg_In_Table (
4029: p_store_flag IN Boolean,
4030: x_msg_rec_count OUT NOCOPY WSH_UTIL_CORE.MsgCountType,
4031: x_return_status OUT NOCOPY VARCHAR2) IS
4032: --
4033: l_debug_on BOOLEAN;
4034: --
4052: WSH_DEBUG_SV.push(l_module_name);
4053: --
4054: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_STORE_FLAG);
4055: END IF;
4056: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
4057: IF (p_store_flag = FALSE) THEN
4058: x_msg_rec_count.e_count := 0;
4059: x_msg_rec_count.w_count := 0;
4060: x_msg_rec_count.u_count := 0;
4091: END IF;
4092: --
4093: EXCEPTION
4094: WHEN others THEN
4095: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4096: WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Store_Msg_In_Table');
4097: --
4098: -- Debug Statements
4099: --
4092: --
4093: EXCEPTION
4094: WHEN others THEN
4095: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4096: WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Store_Msg_In_Table');
4097: --
4098: -- Debug Statements
4099: --
4100: IF l_debug_on THEN
4168: WSH_DEBUG_SV.log(l_module_name, 'p_key', p_key);
4169: WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
4170: END IF;
4171: --
4172: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4173:
4174: IF p_action = 'PUT' THEN --{
4175: IF p_key IS NULL THEN
4176: IF l_debug_on THEN
4200: IF p_key < C_INDEX_LIMIT THEN --{
4201: IF p_cache_tbl.EXISTS(p_key) THEN
4202: p_value := p_cache_tbl(p_key).value;
4203: ELSE
4204: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4205: END IF;
4206: ELSE --}{
4207: l_index := p_cache_ext_tbl.FIRST;
4208: WHILE l_index IS NOT NULL LOOP
4213: END IF;
4214: l_index := p_cache_ext_tbl.NEXT(l_index);
4215: END LOOP;
4216: IF NOT l_found THEN
4217: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4218: END IF;
4219: END IF; --}
4220: END IF;--}
4221: IF l_debug_on THEN
4221: IF l_debug_on THEN
4222: WSH_DEBUG_SV.pop(l_module_name);
4223: END IF;
4224: EXCEPTION
4225: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4226: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4227: --
4228: IF l_debug_on THEN
4229: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4222: WSH_DEBUG_SV.pop(l_module_name);
4223: END IF;
4224: EXCEPTION
4225: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4226: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4227: --
4228: IF l_debug_on THEN
4229: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4230: WSH_DEBUG_SV.C_EXCEP_LEVEL);
4232: END IF;
4233: --
4234:
4235: WHEN FND_API.G_EXC_ERROR THEN
4236: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4237: --
4238: IF l_debug_on THEN
4239: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4240: || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4241: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4242: END IF;
4243: --
4244: WHEN OTHERS THEN
4245: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4246: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4247: --
4248: IF l_debug_on THEN
4249: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4242: END IF;
4243: --
4244: WHEN OTHERS THEN
4245: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4246: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4247: --
4248: IF l_debug_on THEN
4249: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4250: 'Oracle error message is '||
4319: WSH_DEBUG_SV.log(l_module_name, 'p_key', p_key);
4320: WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
4321: END IF;
4322: --
4323: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4324:
4325: IF p_action = 'PUT' THEN --{
4326: IF p_key IS NULL THEN
4327: IF l_debug_on THEN
4351: IF p_key < C_INDEX_LIMIT THEN --{
4352: IF p_cache_tbl.EXISTS(p_key) THEN
4353: p_value := p_cache_tbl(p_key).value;
4354: ELSE
4355: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4356: END IF;
4357: ELSE --}{
4358: l_index := p_cache_ext_tbl.FIRST;
4359: WHILE l_index IS NOT NULL LOOP
4364: END IF;
4365: l_index := p_cache_ext_tbl.NEXT(l_index);
4366: END LOOP;
4367: IF NOT l_found THEN
4368: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4369: END IF;
4370: END IF; --}
4371: END IF;--}
4372: IF l_debug_on THEN
4373: WSH_DEBUG_SV.pop(l_module_name);
4374: END IF;
4375:
4376: EXCEPTION
4377: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4378: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4379: --
4380: IF l_debug_on THEN
4381: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4374: END IF;
4375:
4376: EXCEPTION
4377: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4378: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4379: --
4380: IF l_debug_on THEN
4381: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4382: WSH_DEBUG_SV.C_EXCEP_LEVEL);
4384: END IF;
4385: --
4386:
4387: WHEN FND_API.G_EXC_ERROR THEN
4388: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4389: --
4390: IF l_debug_on THEN
4391: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4392: || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4393: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4394: END IF;
4395: --
4396: WHEN OTHERS THEN
4397: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4398: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4399: --
4400: IF l_debug_on THEN
4401: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4394: END IF;
4395: --
4396: WHEN OTHERS THEN
4397: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4398: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4399: --
4400: IF l_debug_on THEN
4401: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4402: 'Oracle error message is '||
4469: WSH_DEBUG_SV.log(l_module_name, 'p_key', p_key);
4470: WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
4471: END IF;
4472: --
4473: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4474:
4475: IF p_action = 'PUT' THEN --{
4476: IF p_key IS NULL THEN
4477: IF l_debug_on THEN
4501: IF p_key < C_INDEX_LIMIT THEN --{
4502: IF p_cache_tbl.EXISTS(p_key) THEN
4503: p_value := p_cache_tbl(p_key).value;
4504: ELSE
4505: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4506: END IF;
4507: ELSE --}{
4508: l_index := p_cache_ext_tbl.FIRST;
4509: WHILE l_index IS NOT NULL LOOP
4514: END IF;
4515: l_index := p_cache_ext_tbl.NEXT(l_index);
4516: END LOOP;
4517: IF NOT l_found THEN
4518: RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4519: END IF;
4520: END IF; --}
4521: END IF;--}
4522: IF l_debug_on THEN
4523: WSH_DEBUG_SV.pop(l_module_name);
4524: END IF;
4525:
4526: EXCEPTION
4527: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4528: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4529: --
4530: IF l_debug_on THEN
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4524: END IF;
4525:
4526: EXCEPTION
4527: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4528: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4529: --
4530: IF l_debug_on THEN
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Cache is missed',
4532: WSH_DEBUG_SV.C_EXCEP_LEVEL);
4534: END IF;
4535: --
4536:
4537: WHEN FND_API.G_EXC_ERROR THEN
4538: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4539: --
4540: IF l_debug_on THEN
4541: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4542: || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4543: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4544: END IF;
4545: --
4546: WHEN OTHERS THEN
4547: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4548: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4549: --
4550: IF l_debug_on THEN
4551: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4544: END IF;
4545: --
4546: WHEN OTHERS THEN
4547: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4548: wsh_util_core.default_handler('WSH_UTIL_CORE.GET_CACHED_VALUE');
4549: --
4550: IF l_debug_on THEN
4551: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4552: 'Oracle error message is '||
6558: -- a PL/SQL table numbers;
6559: --
6560: PROCEDURE get_idtab_from_string(
6561: p_string IN VARCHAR2,
6562: x_id_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
6563: x_return_status OUT NOCOPY VARCHAR2) IS
6564:
6565: l_new_pos NUMBER;
6566: l_old_pos NUMBER;
6569:
6570: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
6571: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_idtab_from_string';
6572: BEGIN
6573: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6574: --
6575: IF l_debug_on THEN
6576: wsh_debug_sv.push (l_module_name);
6577: END IF;
6597: --
6598:
6599: EXCEPTION
6600: WHEN others THEN
6601: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_IDTAB_FROM_STRING');
6602: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6603: --
6604: IF l_debug_on THEN
6605: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6598:
6599: EXCEPTION
6600: WHEN others THEN
6601: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_IDTAB_FROM_STRING');
6602: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6603: --
6604: IF l_debug_on THEN
6605: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6606: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
6614: -- Purpose : Used to convert a PL/SQL table of numbers to comma-separated list of form '1,2,3,4'
6615: --
6616:
6617: PROCEDURE get_string_from_idtab(
6618: p_id_tab IN WSH_UTIL_CORE.Id_Tab_Type,
6619: x_string OUT NOCOPY VARCHAR2,
6620: x_return_status OUT NOCOPY VARCHAR2) IS
6621:
6622: l_string VARCHAR2(32767);
6623: l_itr NUMBER;
6624: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
6625: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_string_from_idtab';
6626: BEGIN
6627: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6628: --
6629: IF l_debug_on THEN
6630: wsh_debug_sv.push (l_module_name);
6631: END IF;
6655: --
6656:
6657: EXCEPTION
6658: WHEN others THEN
6659: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_STRING_FROM_IDTAB');
6660: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6661: --
6662: IF l_debug_on THEN
6663: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6656:
6657: EXCEPTION
6658: WHEN others THEN
6659: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_STRING_FROM_IDTAB');
6660: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6661: --
6662: IF l_debug_on THEN
6663: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6664: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
6714: p_out_entity_name := 'Line';
6715: p_out_entity_id := p_in_entity_id;
6716: END IF;
6717:
6718: p_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6719:
6720: IF l_debug_on THEN
6721: WSH_DEBUG_SV.push(l_module_name);
6722: END IF;
6722: END IF;
6723: EXCEPTION
6724: WHEN OTHERS
6725: THEN
6726: p_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6727: --
6728: IF l_debug_on THEN
6729: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM);
6730: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6788: wsh_debug_sv.log(l_module_name,'count of stops : ',G_STOP_IDS_STOP_IDS_CACHE.count);
6789: wsh_debug_sv.log(l_module_name,'count of stops2 : ',G_STOP_IDS_STOP_IDS_EXT_CACHE.count);
6790: END IF;
6791: --
6792: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6793: l_num_warnings := 0;
6794: l_num_errors := 0;
6795: --l_stop_ids_tab.delete;
6796:
6835: x_return_status => l_return_status);
6836:
6837: IF l_debug_on THEN
6838: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6839: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6840: END IF;
6841:
6842: wsh_util_core.api_post_call
6843: (
6838: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6839: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6840: END IF;
6841:
6842: wsh_util_core.api_post_call
6843: (
6844: p_return_status => l_return_status,
6845: x_num_warnings => l_num_warnings,
6846: x_num_errors => l_num_errors
6891: x_return_status => l_return_status);
6892:
6893: IF l_debug_on THEN
6894: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6895: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6896: END IF;
6897:
6898: wsh_util_core.api_post_call
6899: (
6894: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6895: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6896: END IF;
6897:
6898: wsh_util_core.api_post_call
6899: (
6900: p_return_status => l_return_status,
6901: x_num_warnings => l_num_warnings,
6902: x_num_errors => l_num_errors
6908: l_ind := G_STOP_IDS_STOP_IDS_EXT_CACHE.NEXT(l_ind);
6909: END LOOP;
6910:
6911: IF l_debug_on THEN
6912: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6913: END IF;
6914:
6915:
6916: --call to reset the global cache tables
6913: END IF;
6914:
6915:
6916: --call to reset the global cache tables
6917: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6918:
6919: IF l_debug_on THEN
6920: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6921: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6917: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6918:
6919: IF l_debug_on THEN
6920: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6921: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6922: END IF;
6923:
6924: wsh_util_core.api_post_call
6925: (
6920: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
6921: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
6922: END IF;
6923:
6924: wsh_util_core.api_post_call
6925: (
6926: p_return_status => l_return_status,
6927: x_num_warnings => l_num_warnings,
6928: x_num_errors => l_num_errors
6930:
6931:
6932: IF l_num_warnings > 0
6933: THEN
6934: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6935: ELSE
6936: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6937: END IF;
6938:
6932: IF l_num_warnings > 0
6933: THEN
6934: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6935: ELSE
6936: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6937: END IF;
6938:
6939: IF l_debug_on THEN
6940: WSH_DEBUG_SV.pop(l_module_name);
6942:
6943:
6944: EXCEPTION
6945: WHEN FND_API.G_EXC_ERROR THEN
6946: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6947: --
6948: -- Debug Statements
6949: --
6950: IF l_debug_on THEN
6948: -- Debug Statements
6949: --
6950: IF l_debug_on THEN
6951: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6952: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6953: END IF;
6954: --
6955: --call to reset the global cache tables
6956: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6952: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6953: END IF;
6954: --
6955: --call to reset the global cache tables
6956: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6957:
6958: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
6959: THEN
6960: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6954: --
6955: --call to reset the global cache tables
6956: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6957:
6958: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
6959: THEN
6960: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6961: END IF;
6962: --
6956: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6957:
6958: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
6959: THEN
6960: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6961: END IF;
6962: --
6963: IF l_debug_on THEN
6964: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
6965: END IF;
6966:
6967:
6968: WHEN others THEN
6969: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6970:
6971: IF l_debug_on THEN
6972: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6973: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6969: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6970:
6971: IF l_debug_on THEN
6972: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6973: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6974: END IF;
6975: --
6976: --call to reset the global cache tables
6977: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6973: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6974: END IF;
6975: --
6976: --call to reset the global cache tables
6977: WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags,x_return_status => l_return_status);
6978:
6979: IF l_debug_on THEN
6980: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6981: END IF;
7013: wsh_debug_sv.push (l_module_name);
7014: wsh_debug_sv.log(l_module_name,'p_reset_flags',p_reset_flags);
7015: END IF;
7016: --
7017: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7018: --
7019: G_STOP_IDS_STOP_IDS_CACHE.delete;
7020: G_STOP_IDS_STOP_IDS_EXT_CACHE.delete;
7021: --
7032: EXCEPTION
7033:
7034:
7035: WHEN others THEN
7036: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7037: --WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Reset_stops_for_load_tender');
7038: --
7039: -- Debug Statements
7040: --
7033:
7034:
7035: WHEN others THEN
7036: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7037: --WSH_UTIL_CORE.Println('Unexpected Error in WSH_UTIL_CORE.Reset_stops_for_load_tender');
7038: --
7039: -- Debug Statements
7040: --
7041: IF l_debug_on THEN
7150: EXCEPTION
7151: --
7152: WHEN OTHERS THEN
7153: --
7154: WSH_UTIL_CORE.default_handler('ValidateActualDepartureDate');
7155: --
7156: IF l_debug_on THEN
7157: WSH_DEBUG_SV.pop(l_module_name, 'Exception: WHEN OTHERS');
7158: END IF;
7230: EXCEPTION
7231: --
7232: WHEN OTHERS THEN
7233: --
7234: WSH_UTIL_CORE.default_handler('GetShipConfirmRule');
7235: --
7236: IF l_debug_on THEN
7237: WSH_DEBUG_SV.pop(l_module_name, 'Exception: WHEN OTHERS');
7238: END IF;
7313:
7314: PROCEDURE get_customer_from_loc(
7315: p_location_id IN NUMBER,
7316: --x_customer_id OUT NOCOPY NUMBER,
7317: x_customer_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
7318: x_return_status OUT NOCOPY VARCHAR2)
7319: IS
7320:
7321: CURSOR c_get_customer(c_location_id IN NUMBER) IS
7327: AND wl.location_source_code = 'HZ'
7328: AND wl.source_location_id = hps.location_id
7329: AND hps.party_site_id = hcas.party_site_id;
7330:
7331: l_customer_id_tab WSH_UTIL_CORE.id_tab_type;
7332: itr NUMBER := 0;
7333: i NUMBER := 0;
7334: l_return_status VARCHAR2(1);
7335: l_cust_string VARCHAR2(2000);
7338: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_customer_from_loc';
7339:
7340: BEGIN
7341:
7342: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7343:
7344: IF l_debug_on THEN
7345: wsh_debug_sv.push(l_module_name);
7346: END IF;
7346: END IF;
7347:
7348: IF ( p_location_id < g_cache_max_size and g_customer_from_location.EXISTS(p_location_id)) THEN
7349:
7350: wsh_util_core.get_idtab_from_string(
7351: p_string => g_customer_from_location(p_location_id),
7352: x_id_tab => l_customer_id_tab,
7353: x_return_status => l_return_status);
7354:
7351: p_string => g_customer_from_location(p_location_id),
7352: x_id_tab => l_customer_id_tab,
7353: x_return_status => l_return_status);
7354:
7355: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7356: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7357: END IF;
7358:
7359: itr := l_customer_id_tab.FIRST;
7396: IF (itr) IS NULL THEN
7397: l_cust_string := '-1';
7398: ELSE
7399:
7400: wsh_util_core.get_string_from_idtab(
7401: p_id_tab => l_customer_id_tab,
7402: x_string => l_cust_string,
7403: x_return_status => l_return_status);
7404:
7405: IF l_debug_on THEN
7406: WSH_DEBUG_SV.logmsg(l_module_name,'Org String '||l_cust_string);
7407: END IF;
7408:
7409: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7410: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7411: END IF;
7412:
7413: END IF;
7446: --
7447:
7448: EXCEPTION
7449: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7450: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7451: --
7452: IF l_debug_on THEN
7453: WSH_DEBUG_SV.pop(l_module_name,'FND_API.EXCEPTION:G_EXC_UNEXPECTED_ERROR');
7454: END IF;
7455: WHEN others THEN
7456: IF c_get_customer%ISOPEN THEN
7457: CLOSE c_get_customer;
7458: END IF;
7459: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_customer_from_loc');
7460: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7461: --
7462: IF l_debug_on THEN
7463: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7456: IF c_get_customer%ISOPEN THEN
7457: CLOSE c_get_customer;
7458: END IF;
7459: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_customer_from_loc');
7460: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7461: --
7462: IF l_debug_on THEN
7463: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7464: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
7477: -- Returns table of organizations for location.
7478: --========================================================================
7479: PROCEDURE get_org_from_location(
7480: p_location_id IN NUMBER,
7481: x_organization_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
7482: x_return_status OUT NOCOPY VARCHAR2)
7483: IS
7484: /*
7485: CURSOR c_get_org_from_loc(c_location_id IN NUMBER) IS
7493: SELECT organization_id
7494: FROM wsh_ship_from_orgs_v
7495: WHERE location_id = c_location_id;
7496:
7497: l_organization_tab WSH_UTIL_CORE.id_tab_type;
7498:
7499: itr NUMBER;
7500: i NUMBER;
7501: l_return_status VARCHAR2(1);
7504: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
7505: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_org_from_location';
7506:
7507: BEGIN
7508: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7509:
7510: IF l_debug_on THEN
7511: wsh_debug_sv.push(l_module_name);
7512: END IF;
7512: END IF;
7513:
7514: IF (p_location_id < g_cache_max_size and g_organization_from_location.EXISTS(p_location_id)) THEN
7515:
7516: wsh_util_core.get_idtab_from_string(
7517: p_string => g_organization_from_location(p_location_id),
7518: x_id_tab => l_organization_tab,
7519: x_return_status => l_return_status);
7520:
7517: p_string => g_organization_from_location(p_location_id),
7518: x_id_tab => l_organization_tab,
7519: x_return_status => l_return_status);
7520:
7521: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7522: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7523: END IF;
7524:
7525: itr := l_organization_tab.FIRST;
7550: IF (itr) IS NULL THEN
7551: l_org_string := '-1';
7552: ELSE
7553:
7554: wsh_util_core.get_string_from_idtab(
7555: p_id_tab => l_organization_tab,
7556: x_string => l_org_string,
7557: x_return_status => l_return_status);
7558:
7559: IF l_debug_on THEN
7560: WSH_DEBUG_SV.logmsg(l_module_name,'Org String '||l_org_string);
7561: END IF;
7562:
7563: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7564: raise FND_API.G_EXC_UNEXPECTED_ERROR;
7565: END IF;
7566:
7567: END IF;
7596: --
7597:
7598: EXCEPTION
7599: WHEN others THEN
7600: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7601:
7602: IF c_get_org_from_loc%ISOPEN THEN
7603: CLOSE c_get_org_from_loc;
7604: END IF;
7602: IF c_get_org_from_loc%ISOPEN THEN
7603: CLOSE c_get_org_from_loc;
7604: END IF;
7605:
7606: WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.get_org_from_loc');
7607:
7608: --
7609: IF l_debug_on THEN
7610: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7687: WSH_DEBUG_SV.log(l_module_name, 'p_entity_id', p_entity_id);
7688: --
7689: END IF;
7690: --
7691: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7692: --
7693:
7694: IF ( p_entity_type = 'DELIVERY' ) THEN
7695: --
7755: END IF;
7756: --
7757: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_INVALID_ENTITY_TYPE' );
7758: FND_MESSAGE.SET_TOKEN ('ENT_TYPE', p_entity_type );
7759: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7760: WSH_UTIL_CORE.add_message(x_return_status);
7761: --
7762: END IF;
7763:
7756: --
7757: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_INVALID_ENTITY_TYPE' );
7758: FND_MESSAGE.SET_TOKEN ('ENT_TYPE', p_entity_type );
7759: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7760: WSH_UTIL_CORE.add_message(x_return_status);
7761: --
7762: END IF;
7763:
7764: --
7767:
7768: IF ( l_error_flag ) THEN
7769: --
7770: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_DET_INVALID_DEL' );
7771: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7772: WSH_UTIL_CORE.add_message(x_return_status);
7773: --
7774: END IF;
7775:
7768: IF ( l_error_flag ) THEN
7769: --
7770: FND_MESSAGE.SET_NAME ( 'WSH', 'WSH_DET_INVALID_DEL' );
7771: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7772: WSH_UTIL_CORE.add_message(x_return_status);
7773: --
7774: END IF;
7775:
7776: --
7782: --
7783:
7784: EXCEPTION
7785: WHEN OTHERS THEN
7786: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
7787: wsh_util_core.default_handler('WSH_DELIVERY_VALIDATIONS.Get_Delivery_Status');
7788: --
7789: IF l_debug_on THEN
7790: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7783:
7784: EXCEPTION
7785: WHEN OTHERS THEN
7786: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
7787: wsh_util_core.default_handler('WSH_DELIVERY_VALIDATIONS.Get_Delivery_Status');
7788: --
7789: IF l_debug_on THEN
7790: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7791: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
7955: WSH_DEBUG_SV.push(l_module_name);
7956: END IF;
7957:
7958: x_curr_conv_type := NULL;
7959: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7960:
7961: -- no cached value, call fnd_profile.value
7962: IF G_CURRENCY_CONVERSION_TYPE IS NULL then
7963: IF l_debug_on THEN
7979: END IF;
7980:
7981: EXCEPTION
7982: when CURR_CONV_TYPE_UNDEFINED_EXP then
7983: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7984: fnd_message.set_name('WSH', 'WSH_CURR_CONV_TYPE_UNDEFINED');
7985: wsh_util_core.add_message(x_return_status);
7986: IF l_debug_on THEN
7987: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
7981: EXCEPTION
7982: when CURR_CONV_TYPE_UNDEFINED_EXP then
7983: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7984: fnd_message.set_name('WSH', 'WSH_CURR_CONV_TYPE_UNDEFINED');
7985: wsh_util_core.add_message(x_return_status);
7986: IF l_debug_on THEN
7987: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
7988: WSH_DEBUG_SV.logmsg(l_module_name, 'WSH: Currency Conversion Type for OTM profile value is not defined.');
7989: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION');
7989: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION');
7990: END IF;
7991:
7992: when OTHERS then
7993: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7994: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_UTIL_CORE.get_currency_conversion_type', l_module_name);
7995:
7996: IF l_debug_on THEN
7997: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
7990: END IF;
7991:
7992: when OTHERS then
7993: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7994: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_UTIL_CORE.get_currency_conversion_type', l_module_name);
7995:
7996: IF l_debug_on THEN
7997: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
7998: WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8050: IF l_debug_on THEN
8051: WSH_DEBUG_SV.push(l_module_name);
8052: END IF;
8053:
8054: l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8055: l_number_of_warnings := 0;
8056: l_number_of_errors := 0;
8057:
8058:
8076: x_first_stop_loc_id => l_first_stop_loc_id,
8077: x_last_stop_loc_id => l_last_stop_loc_id,
8078: p_trip_id => p_trip_id);
8079:
8080: wsh_util_core.api_post_call(
8081: p_return_status =>l_return_status,
8082: x_num_warnings =>l_number_of_warnings,
8083: x_num_errors =>l_number_of_errors,
8084: p_msg_data =>l_msg_data);
8256: IF l_debug_on THEN
8257: WSH_DEBUG_SV.push(l_module_name);
8258: END IF;
8259:
8260: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8261:
8262: IF l_debug_on
8263: THEN
8264: WSH_DEBUG_SV.logmsg(l_module_name,' Getting trip stop information ',
8338: --}
8339: EXCEPTION
8340: --{
8341: WHEN FND_API.G_EXC_ERROR THEN
8342: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8343: IF l_debug_on THEN
8344: WSH_DEBUG_SV.pop(l_module_name);
8345: END IF;
8346:
8349: IF l_debug_on THEN
8350: WSH_DEBUG_SV.pop(l_module_name);
8351: END IF;
8352: WHEN OTHERS THEN
8353: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8354:
8355: IF GET_TRIP_STOPS_PICKUP%ISOPEN THEN
8356: CLOSE GET_TRIP_STOPS_PICKUP;
8357: END IF;
8368: END GET_FIRST_LAST_STOP_INFO;
8369:
8370: /* End of OTM R12 - O/B BPEL */
8371:
8372: END WSH_UTIL_CORE;