eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WSH_NEW_DELIVERIES_PVT dependencies on WSH_NEW_DELIVERIES

Line 1: PACKAGE BODY WSH_NEW_DELIVERIES_PVT as

1: PACKAGE BODY WSH_NEW_DELIVERIES_PVT as
2: /* $Header: WSHDETHB.pls 120.15.12010000.2 2008/10/17 13:33:29 anvarshn ship $ */
3:
4: --
5: -- Package internal global variables

Line 28: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_NEW_DELIVERIES_PVT';

24: --
25:
26:
27: --
28: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_NEW_DELIVERIES_PVT';
29: --
30: PROCEDURE Create_Delivery
31: (p_delivery_info IN Delivery_Rec_Type,
32: x_rowid OUT NOCOPY VARCHAR2,

Line 39: SELECT wsh_new_deliveries_s.nextval

35: x_return_status OUT NOCOPY VARCHAR2
36: ) IS
37:
38: CURSOR get_next_delivery IS
39: SELECT wsh_new_deliveries_s.nextval
40: FROM sys.dual;
41:
42: CURSOR count_delivery_rows (v_delivery_name VARCHAR2) IS
43: SELECT delivery_id

Line 44: FROM wsh_new_deliveries

40: FROM sys.dual;
41:
42: CURSOR count_delivery_rows (v_delivery_name VARCHAR2) IS
43: SELECT delivery_id
44: FROM wsh_new_deliveries
45: WHERE name = v_delivery_name;
46:
47: l_delivery_name VARCHAR2(30);
48: l_temp_id NUMBER;

Line 158: INSERT INTO wsh_new_deliveries

154: CLOSE count_delivery_rows;
155:
156: END IF;
157:
158: INSERT INTO wsh_new_deliveries
159: (
160: delivery_id
161: ,name
162: ,planned_flag

Line 462: ,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT

458: ,p_delivery_info.REASON_OF_TRANSPORT
459: ,p_delivery_info.DESCRIPTION
460: -- bug 3667348
461: --OTM R12
462: ,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
463: ,1
464: --
465: )
466: RETURNING rowid

Line 506: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',l_module_name);

502: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DUPLICATE_NAME');
503: END IF;
504: --
505: WHEN others THEN
506: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',l_module_name);
507: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
508: --
509: IF l_debug_on THEN
510: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);

Line 541: FROM wsh_new_deliveries

537: weight_uom_code,
538: volume_uom_code,
539: wv_frozen_flag,
540: organization_id
541: FROM wsh_new_deliveries
542: WHERE delivery_id = p_delivery_info.delivery_id;
543:
544: CURSOR c_iscarriersmcchanged IS
545: SELECT organization_id, name

Line 546: FROM wsh_new_deliveries

542: WHERE delivery_id = p_delivery_info.delivery_id;
543:
544: CURSOR c_iscarriersmcchanged IS
545: SELECT organization_id, name
546: FROM wsh_new_deliveries
547: WHERE delivery_id = p_delivery_info.delivery_id
548: and (carrier_id <> p_delivery_info.carrier_id
549: OR ship_method_code <> p_delivery_info.ship_method_code);
550:

Line 584: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;

580: l_gross_weight NUMBER;
581: l_net_weight NUMBER;
582:
583: --OTM R12
584: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
585: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
586: l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
587: l_tms_update VARCHAR2(1);
588: l_trip_not_found VARCHAR2(1);

Line 585: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;

581: l_net_weight NUMBER;
582:
583: --OTM R12
584: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
585: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
586: l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
587: l_tms_update VARCHAR2(1);
588: l_trip_not_found VARCHAR2(1);
589: l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;

Line 590: l_tms_version_number WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER%TYPE;

586: l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
587: l_tms_update VARCHAR2(1);
588: l_trip_not_found VARCHAR2(1);
589: l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
590: l_tms_version_number WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER%TYPE;
591: l_gc3_is_installed VARCHAR2(1);
592: l_sysdate DATE;
593: api_return_fail EXCEPTION;
594: --

Line 837: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN

833: WSH_DEBUG_SV.log(l_module_name,'l_delivery_info latest_dropoff_date', l_delivery_info.latest_dropoff_date);
834: END IF;
835:
836: IF (NVL(l_delivery_info.tms_interface_flag,
837: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
838: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
839: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN

Line 838: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,

834: END IF;
835:
836: IF (NVL(l_delivery_info.tms_interface_flag,
837: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
838: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
839: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
842: l_tms_update := 'Y';

Line 839: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,

835:
836: IF (NVL(l_delivery_info.tms_interface_flag,
837: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
838: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
839: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
842: l_tms_update := 'Y';
843: l_delivery_info_tab(1) := l_delivery_info;

Line 840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,

836: IF (NVL(l_delivery_info.tms_interface_flag,
837: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
838: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
839: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
842: l_tms_update := 'Y';
843: l_delivery_info_tab(1) := l_delivery_info;
844: l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;

Line 841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN

837: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
838: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
839: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
842: l_tms_update := 'Y';
843: l_delivery_info_tab(1) := l_delivery_info;
844: l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
845: l_tms_version_number := NVL(l_delivery_info.tms_version_number, 1) + 1;

Line 844: l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;

840: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
842: l_tms_update := 'Y';
843: l_delivery_info_tab(1) := l_delivery_info;
844: l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
845: l_tms_version_number := NVL(l_delivery_info.tms_version_number, 1) + 1;
846: ELSE
847: l_tms_update := 'N';
848: END IF;

Line 854: UPDATE wsh_new_deliveries

850: END IF;
851: END IF;
852: --END OTM R12
853:
854: UPDATE wsh_new_deliveries
855: SET
856: delivery_id = p_delivery_info.delivery_id
857: ,name = p_delivery_info.name
858: ,planned_flag = p_delivery_info.planned_flag

Line 1003: NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT))

999: -- bug 3667348
1000: --OTM R12
1001: ,TMS_INTERFACE_FLAG = DECODE(l_tms_update,
1002: 'Y', l_new_interface_flag_tab(1),
1003: NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT))
1004: ,TMS_VERSION_NUMBER = DECODE(l_tms_update,
1005: 'Y', l_tms_version_number,
1006: NVL(tms_version_number, 1))
1007: --

Line 1227: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',l_module_name);

1223: END IF;
1224: --
1225:
1226: WHEN others THEN
1227: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',l_module_name);
1228: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1229: IF l_debug_on THEN
1230: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1231: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

Line 1259: FROM wsh_new_deliveries

1255: ) IS
1256:
1257: CURSOR get_del_id_rowid (v_rowid VARCHAR2) IS
1258: SELECT delivery_id
1259: FROM wsh_new_deliveries
1260: WHERE rowid = v_rowid;
1261:
1262: CURSOR get_delivery_legs (v_delivery_id NUMBER) IS
1263: SELECT delivery_leg_id, parent_delivery_leg_id

Line 1363: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));

1359: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1360: x_return_status := l_return_status;
1361: ROLLBACK TO SAVEPOINT unassign_details;
1362: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_DET_ERROR');
1363: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1364: FND_MESSAGE.SET_TOKEN('DET_NAME',dt.delivery_detail_id);
1365: wsh_util_core.add_message(x_return_status,l_module_name);
1366: --
1367: IF l_debug_on THEN

Line 1425: DELETE FROM wsh_new_deliveries

1421: 'Freight cost rows deleted', SQL%ROWCOUNT);
1422: END IF;
1423:
1424: -- delete the delivery
1425: DELETE FROM wsh_new_deliveries
1426: WHERE delivery_id = l_delivery_id;
1427: --
1428:
1429: IF l_debug_on THEN

Line 1465: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));

1461: CLOSE detail_info;
1462: END IF;
1463:
1464: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_ERROR');
1465: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1466: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1467: wsh_util_core.add_message(x_return_status,l_module_name);
1468: --
1469: IF l_debug_on THEN

Line 1490: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.DELETE_DELIVERY',l_module_name);

1486: IF (detail_info%ISOPEN) THEN
1487: CLOSE detail_info;
1488: END IF;
1489:
1490: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.DELETE_DELIVERY',l_module_name);
1491: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1492: --
1493: IF l_debug_on THEN
1494: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);

Line 1517: FROM wsh_new_deliveries

1513: ) IS
1514:
1515: CURSOR lock_row IS
1516: SELECT *
1517: FROM wsh_new_deliveries
1518: WHERE rowid = p_rowid
1519: FOR UPDATE OF delivery_id NOWAIT;
1520:
1521: Recinfo lock_row%ROWTYPE;

Line 2198: FROM wsh_new_deliveries

2194: --OTM R12
2195: TMS_INTERFACE_FLAG,
2196: TMS_VERSION_NUMBER
2197: --
2198: FROM wsh_new_deliveries
2199: WHERE delivery_id = p_delivery_id;
2200:
2201: others EXCEPTION;
2202:

Line 2255: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',l_module_name);

2251: IF (delivery_record%ISOPEN) THEN
2252: CLOSE delivery_record;
2253: END IF;
2254:
2255: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',l_module_name);
2256: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2257: --
2258: IF l_debug_on THEN
2259: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);

Line 2278: FROM wsh_new_deliveries

2274: ) RETURN VARCHAR2 IS
2275:
2276: CURSOR get_name IS
2277: SELECT name
2278: FROM wsh_new_deliveries
2279: WHERE delivery_id = p_delivery_id;
2280:
2281: x_name VARCHAR2(30);
2282:

Line 2338: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.GET_NAME',l_module_name);

2334:
2335: EXCEPTION
2336:
2337: WHEN others THEN
2338: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.GET_NAME',l_module_name);
2339: --
2340: IF l_debug_on THEN
2341: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2342: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

Line 2398: wsh_new_deliveries_pvt.lock_dlvy_no_compare(p_delivery_id => p_rec_attr_tab(l_index).delivery_id);

2394: lock_delivery(p_rowid => p_rec_attr_tab(l_index).rowid,
2395: p_delivery_info => p_rec_attr_tab(l_index)
2396: );
2397: else
2398: wsh_new_deliveries_pvt.lock_dlvy_no_compare(p_delivery_id => p_rec_attr_tab(l_index).delivery_id);
2399: end if;
2400:
2401: -- Bug fix 2657182
2402: -- Need to lock the related entities - lines, containers and delivery legs

Line 2547: FROM wsh_new_deliveries wnd

2543: IS
2544:
2545: CURSOR c_lock_dlvy(p_dlvy_id NUMBER) IS
2546: SELECT wnd.delivery_id
2547: FROM wsh_new_deliveries wnd
2548: WHERE wnd.delivery_id = p_dlvy_id
2549: FOR UPDATE NOWAIT;
2550:
2551: l_dummy_dlvy_id NUMBER;

Line 2640: SELECT wsh_new_deliveries_s.nextval

2636: -- Generate delivery ID
2637: --
2638: CURSOR get_next_delivery
2639: IS
2640: SELECT wsh_new_deliveries_s.nextval
2641: FROM dual;
2642: --
2643: CURSOR dlvy_csr(p_delivery_id NUMBER)
2644: IS

Line 2646: FROM wsh_new_deliveries

2642: --
2643: CURSOR dlvy_csr(p_delivery_id NUMBER)
2644: IS
2645: SELECT rowid
2646: FROM wsh_new_deliveries
2647: WHERE delivery_id = p_delivery_id;
2648: --
2649: CURSOR leg_csr(p_delivery_id NUMBER)
2650: IS

Line 2658: FROM wsh_new_deliveries

2654: --
2655: --
2656: CURSOR count_delivery_rows (p_delivery_name VARCHAR2) IS
2657: SELECT delivery_id
2658: FROM wsh_new_deliveries
2659: WHERE name = p_delivery_name;
2660: --
2661: l_delivery_id NUMBER;
2662: l_delivery_name VARCHAR2(30);

Line 2749: INSERT INTO WSH_NEW_DELIVERIES

2745: END IF;
2746: --}
2747: --
2748: --
2749: INSERT INTO WSH_NEW_DELIVERIES
2750: (
2751: DELIVERY_ID,
2752: NAME,
2753: PLANNED_FLAG,

Line 3050: FROM WSH_NEW_DELIVERIES WND

3046: --OTM R12
3047: DECODE(p_delivery_rec.TMS_INTERFACE_FLAG,NULL,WND.TMS_INTERFACE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TMS_INTERFACE_FLAG),
3048: DECODE(p_delivery_rec.TMS_VERSION_NUMBER,NULL,WND.TMS_VERSION_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.TMS_VERSION_NUMBER)
3049: --
3050: FROM WSH_NEW_DELIVERIES WND
3051: WHERE delivery_id = p_delivery_id;
3052: --
3053: --
3054: IF SQL%ROWCOUNT = 0

Line 3248: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CLONE',l_module_name);

3244: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3245: END IF;
3246: WHEN others THEN
3247: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3248: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CLONE',l_module_name);
3249: --
3250: IF l_debug_on THEN
3251: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3252: END IF;

Line 3261: -- Description: This procedure will copy the attributes of a delivery in wsh_new_deliveries

3257: -- Bug 3292364
3258: -- Procedure: Table_To_Record
3259: -- Parameters: x_delivery_rec: A record of all attributes of a Delivery Record
3260: -- p_delivery_id : delivery_id of the delivery that is to be copied
3261: -- Description: This procedure will copy the attributes of a delivery in wsh_new_deliveries
3262: -- and copy it to a record.
3263:
3264: PROCEDURE Table_to_Record (p_delivery_id IN NUMBER,
3265: x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,

Line 3265: x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,

3261: -- Description: This procedure will copy the attributes of a delivery in wsh_new_deliveries
3262: -- and copy it to a record.
3263:
3264: PROCEDURE Table_to_Record (p_delivery_id IN NUMBER,
3265: x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
3266: x_return_status OUT NOCOPY VARCHAR2) IS
3267:
3268: CURSOR c_tbl_rec (p_delivery_id in NUMBER) IS
3269: SELECT DELIVERY_ID

Line 3438: FROM wsh_new_deliveries

3434: --OTM R12
3435: ,TMS_INTERFACE_FLAG
3436: ,TMS_VERSION_NUMBER
3437: --
3438: FROM wsh_new_deliveries
3439: WHERE delivery_id = p_delivery_id;
3440:
3441: l_debug_on BOOLEAN;
3442: --

Line 3484: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.Table_to_Record',l_module_name);

3480: END IF;
3481: --
3482: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
3483: --
3484: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.Table_to_Record',l_module_name);
3485: --
3486: IF l_debug_on THEN
3487: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3488: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

Line 3516: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;

3512:
3513: l_num_error NUMBER;
3514: l_num_warn NUMBER;
3515: l_return_status VARCHAR2(1);
3516: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3517: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3518:
3519: l_new_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3520: l_new_tms_version_number_tab WSH_UTIL_CORE.ID_TAB_TYPE;

Line 3517: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;

3513: l_num_error NUMBER;
3514: l_num_warn NUMBER;
3515: l_return_status VARCHAR2(1);
3516: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3517: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3518:
3519: l_new_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3520: l_new_tms_version_number_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3521: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;

Line 3644: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,

3640: -- the interface_flag is changed from other status to DR or UR or CR
3641: IF (p_tms_interface_flag_tab(i) IS NULL) THEN
3642: --assume regular update
3643: IF (l_delivery_info.tms_interface_flag IN
3644: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3645: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3646: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;

Line 3645: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,

3641: IF (p_tms_interface_flag_tab(i) IS NULL) THEN
3642: --assume regular update
3643: IF (l_delivery_info.tms_interface_flag IN
3644: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3645: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3646: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3649: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;

Line 3646: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,

3642: --assume regular update
3643: IF (l_delivery_info.tms_interface_flag IN
3644: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3645: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3646: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3649: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3650: ELSE

Line 3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN

3643: IF (l_delivery_info.tms_interface_flag IN
3644: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3645: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3646: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3649: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3650: ELSE
3651: l_new_tms_interface_flag_tab(l_count) := NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT);

Line 3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;

3644: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3645: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3646: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3649: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3650: ELSE
3651: l_new_tms_interface_flag_tab(l_count) := NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT);
3652: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);

Line 3651: l_new_tms_interface_flag_tab(l_count) := NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT);

3647: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3649: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3650: ELSE
3651: l_new_tms_interface_flag_tab(l_count) := NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT);
3652: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3653: END IF;
3654: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3655: AND l_delivery_info.tms_interface_flag IN

Line 3654: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED

3650: ELSE
3651: l_new_tms_interface_flag_tab(l_count) := NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT);
3652: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3653: END IF;
3654: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3655: AND l_delivery_info.tms_interface_flag IN
3656: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3657: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3658: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR

Line 3656: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,

3652: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3653: END IF;
3654: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3655: AND l_delivery_info.tms_interface_flag IN
3656: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3657: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3658: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3659: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3660: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);

Line 3657: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN

3653: END IF;
3654: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3655: AND l_delivery_info.tms_interface_flag IN
3656: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3657: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3658: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3659: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3660: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3661: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED

Line 3659: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;

3655: AND l_delivery_info.tms_interface_flag IN
3656: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3657: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3658: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3659: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3660: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3661: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3662: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3663: --DP stays in DP

Line 3661: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED

3657: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3658: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3659: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3660: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3661: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3662: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3663: --DP stays in DP
3664: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3665: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);

Line 3662: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN

3658: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3659: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3660: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3661: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3662: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3663: --DP stays in DP
3664: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3665: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3666: ELSIF (p_tms_interface_flag_tab(i) IN

Line 3667: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,

3663: --DP stays in DP
3664: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3665: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3666: ELSIF (p_tms_interface_flag_tab(i) IN
3667: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,

Line 3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,

3664: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3665: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3666: ELSIF (p_tms_interface_flag_tab(i) IN
3667: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,

Line 3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,

3665: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3666: ELSIF (p_tms_interface_flag_tab(i) IN
3667: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3673: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)

Line 3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,

3666: ELSIF (p_tms_interface_flag_tab(i) IN
3667: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3673: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3674: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN

Line 3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,

3667: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3673: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3674: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3675: --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,

Line 3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,

3668: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3673: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3674: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3675: --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,
3676: --do not increment the version

Line 3673: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)

3669: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3673: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3674: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3675: --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,
3676: --do not increment the version
3677: l_new_tms_interface_flag_tab(l_count) := p_tms_interface_flag_tab(i);

Line 3713: UPDATE wsh_new_deliveries

3709: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)
3710: AND (l_delivery_info_tab.COUNT > 0)) THEN
3711:
3712: FORALL j IN l_delivery_info_tab.FIRST..l_delivery_info_tab.LAST
3713: UPDATE wsh_new_deliveries
3714: SET
3715: TMS_VERSION_NUMBER = l_new_tms_version_number_tab(j)
3716: ,TMS_INTERFACE_FLAG = l_new_tms_interface_flag_tab(j)
3717: ,last_update_date = SYSDATE

Line 3791: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);

3787: END IF;
3788:
3789: WHEN others THEN
3790: ROLLBACK TO tms_update;
3791: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);
3792: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3793: IF l_debug_on THEN
3794: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3795: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

Line 3801: END WSH_NEW_DELIVERIES_PVT;

3797:
3798: END UPDATE_TMS_INTERFACE_FLAG;
3799: --END OTM R12
3800:
3801: END WSH_NEW_DELIVERIES_PVT;