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.20 2010/08/23 10:40:41 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 463: ,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT

459: ,p_delivery_info.REASON_OF_TRANSPORT
460: ,p_delivery_info.DESCRIPTION
461: -- bug 3667348
462: --OTM R12
463: ,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
464: ,1
465: , p_delivery_info.client_id -- LSP PROJECT --Modified R12.1.1 LSP PROJECT(rminocha)
466: , decode(p_delivery_info.consignee_flag,'C',NULL,p_delivery_info.consignee_flag) -- RTV changes
467: )

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

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

Line 543: FROM wsh_new_deliveries

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

Line 548: FROM wsh_new_deliveries

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

Line 586: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;

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

Line 587: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;

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

Line 592: l_tms_version_number WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER%TYPE;

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

Line 840: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN

836: WSH_DEBUG_SV.log(l_module_name,'l_delivery_info client_id', p_delivery_info.client_id); -- Modified R12.1.1 LSP PROJECT
837: END IF;
838:
839: IF (NVL(l_delivery_info.tms_interface_flag,
840: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
841: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
842: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
843: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
844: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN

Line 841: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,

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

Line 842: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,

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

Line 843: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,

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

Line 844: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN

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

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

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

Line 857: UPDATE wsh_new_deliveries

853: END IF;
854: END IF;
855: --END OTM R12
856:
857: UPDATE wsh_new_deliveries
858: SET
859: delivery_id = p_delivery_info.delivery_id
860: ,name = p_delivery_info.name
861: ,planned_flag = p_delivery_info.planned_flag

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

1002: -- bug 3667348
1003: --OTM R12
1004: ,TMS_INTERFACE_FLAG = DECODE(l_tms_update,
1005: 'Y', l_new_interface_flag_tab(1),
1006: NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT))
1007: ,TMS_VERSION_NUMBER = DECODE(l_tms_update,
1008: 'Y', l_tms_version_number,
1009: NVL(tms_version_number, 1))
1010: ,CLIENT_ID = p_delivery_info.client_id -- LSP PROJECT --Modified R12.1.1 LSP PROJECT(rminocha)

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

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

Line 1264: FROM wsh_new_deliveries

1260: ) IS
1261:
1262: CURSOR get_del_id_rowid (v_rowid VARCHAR2) IS
1263: SELECT delivery_id
1264: FROM wsh_new_deliveries
1265: WHERE rowid = v_rowid;
1266:
1267: CURSOR get_delivery_legs (v_delivery_id NUMBER) IS
1268: SELECT delivery_leg_id, parent_delivery_leg_id

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

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

Line 1430: DELETE FROM wsh_new_deliveries

1426: 'Freight cost rows deleted', SQL%ROWCOUNT);
1427: END IF;
1428:
1429: -- delete the delivery
1430: DELETE FROM wsh_new_deliveries
1431: WHERE delivery_id = l_delivery_id;
1432: --
1433:
1434: IF l_debug_on THEN

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

1466: CLOSE detail_info;
1467: END IF;
1468:
1469: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_ERROR');
1470: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1471: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1472: wsh_util_core.add_message(x_return_status,l_module_name);
1473: --
1474: IF l_debug_on THEN

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

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

Line 1522: FROM wsh_new_deliveries

1518: ) IS
1519:
1520: CURSOR lock_row IS
1521: SELECT *
1522: FROM wsh_new_deliveries
1523: WHERE rowid = p_rowid
1524: FOR UPDATE OF delivery_id NOWAIT;
1525:
1526: Recinfo lock_row%ROWTYPE;

Line 2218: FROM wsh_new_deliveries

2214: PENDING_ADVICE_FLAG,
2215: CLIENT_ID, -- LSP PROJECT --Modified R12.1.1 LSP PROJECT(rminocha)
2216: NULL , -- client_code , LSP PRO
2217: CONSIGNEE_FLAG --RTV changes
2218: FROM wsh_new_deliveries
2219: WHERE delivery_id = p_delivery_id;
2220:
2221: others EXCEPTION;
2222:

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

2271: IF (delivery_record%ISOPEN) THEN
2272: CLOSE delivery_record;
2273: END IF;
2274:
2275: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',l_module_name);
2276: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2277: --
2278: IF l_debug_on THEN
2279: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);

Line 2298: FROM wsh_new_deliveries

2294: ) RETURN VARCHAR2 IS
2295:
2296: CURSOR get_name IS
2297: SELECT name
2298: FROM wsh_new_deliveries
2299: WHERE delivery_id = p_delivery_id;
2300:
2301: x_name VARCHAR2(30);
2302:

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

2354:
2355: EXCEPTION
2356:
2357: WHEN others THEN
2358: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.GET_NAME',l_module_name);
2359: --
2360: IF l_debug_on THEN
2361: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2362: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

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

2414: lock_delivery(p_rowid => p_rec_attr_tab(l_index).rowid,
2415: p_delivery_info => p_rec_attr_tab(l_index)
2416: );
2417: else
2418: wsh_new_deliveries_pvt.lock_dlvy_no_compare(p_delivery_id => p_rec_attr_tab(l_index).delivery_id);
2419: end if;
2420:
2421: -- Bug fix 2657182
2422: -- Need to lock the related entities - lines, containers and delivery legs

Line 2567: FROM wsh_new_deliveries wnd

2563: IS
2564:
2565: CURSOR c_lock_dlvy(p_dlvy_id NUMBER) IS
2566: SELECT wnd.delivery_id
2567: FROM wsh_new_deliveries wnd
2568: WHERE wnd.delivery_id = p_dlvy_id
2569: FOR UPDATE NOWAIT;
2570:
2571: l_dummy_dlvy_id NUMBER;

Line 2660: SELECT wsh_new_deliveries_s.nextval

2656: -- Generate delivery ID
2657: --
2658: CURSOR get_next_delivery
2659: IS
2660: SELECT wsh_new_deliveries_s.nextval
2661: FROM dual;
2662: --
2663: CURSOR dlvy_csr(p_delivery_id NUMBER)
2664: IS

Line 2666: FROM wsh_new_deliveries

2662: --
2663: CURSOR dlvy_csr(p_delivery_id NUMBER)
2664: IS
2665: SELECT rowid
2666: FROM wsh_new_deliveries
2667: WHERE delivery_id = p_delivery_id;
2668: --
2669: CURSOR leg_csr(p_delivery_id NUMBER)
2670: IS

Line 2678: FROM wsh_new_deliveries

2674: --
2675: --
2676: CURSOR count_delivery_rows (p_delivery_name VARCHAR2) IS
2677: SELECT delivery_id
2678: FROM wsh_new_deliveries
2679: WHERE name = p_delivery_name;
2680: --
2681: l_delivery_id NUMBER;
2682: l_delivery_name VARCHAR2(30);

Line 2769: INSERT INTO WSH_NEW_DELIVERIES

2765: END IF;
2766: --}
2767: --
2768: --
2769: INSERT INTO WSH_NEW_DELIVERIES
2770: (
2771: DELIVERY_ID,
2772: NAME,
2773: PLANNED_FLAG,

Line 3070: FROM WSH_NEW_DELIVERIES WND

3066: --OTM R12
3067: DECODE(p_delivery_rec.TMS_INTERFACE_FLAG,NULL,WND.TMS_INTERFACE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TMS_INTERFACE_FLAG),
3068: DECODE(p_delivery_rec.TMS_VERSION_NUMBER,NULL,WND.TMS_VERSION_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.TMS_VERSION_NUMBER)
3069: --
3070: FROM WSH_NEW_DELIVERIES WND
3071: WHERE delivery_id = p_delivery_id;
3072: --
3073: --
3074: IF SQL%ROWCOUNT = 0

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

3264: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3265: END IF;
3266: WHEN others THEN
3267: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3268: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CLONE',l_module_name);
3269: --
3270: IF l_debug_on THEN
3271: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3272: END IF;

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

3277: -- Bug 3292364
3278: -- Procedure: Table_To_Record
3279: -- Parameters: x_delivery_rec: A record of all attributes of a Delivery Record
3280: -- p_delivery_id : delivery_id of the delivery that is to be copied
3281: -- Description: This procedure will copy the attributes of a delivery in wsh_new_deliveries
3282: -- and copy it to a record.
3283:
3284: PROCEDURE Table_to_Record (p_delivery_id IN NUMBER,
3285: x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,

Line 3285: x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,

3281: -- Description: This procedure will copy the attributes of a delivery in wsh_new_deliveries
3282: -- and copy it to a record.
3283:
3284: PROCEDURE Table_to_Record (p_delivery_id IN NUMBER,
3285: x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
3286: x_return_status OUT NOCOPY VARCHAR2) IS
3287:
3288: CURSOR c_tbl_rec (p_delivery_id in NUMBER) IS
3289: SELECT DELIVERY_ID

Line 3462: FROM wsh_new_deliveries

3458: ,pending_advice_flag
3459: ,client_id -- LSP PROJECT -- --Modified R12.1.1 LSP PROJECT(rminocha)
3460: ,NULL -- client_code LSP PROJECT
3461: ,consignee_flag --RTV changes
3462: FROM wsh_new_deliveries
3463: WHERE delivery_id = p_delivery_id;
3464:
3465: l_debug_on BOOLEAN;
3466: --

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

3504: END IF;
3505: --
3506: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
3507: --
3508: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.Table_to_Record',l_module_name);
3509: --
3510: IF l_debug_on THEN
3511: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3512: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

Line 3540: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;

3536:
3537: l_num_error NUMBER;
3538: l_num_warn NUMBER;
3539: l_return_status VARCHAR2(1);
3540: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3541: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3542:
3543: l_new_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3544: l_new_tms_version_number_tab WSH_UTIL_CORE.ID_TAB_TYPE;

Line 3541: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;

3537: l_num_error NUMBER;
3538: l_num_warn NUMBER;
3539: l_return_status VARCHAR2(1);
3540: l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3541: l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3542:
3543: l_new_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3544: l_new_tms_version_number_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3545: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;

Line 3668: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,

3664: -- the interface_flag is changed from other status to DR or UR or CR
3665: IF (p_tms_interface_flag_tab(i) IS NULL) THEN
3666: --assume regular update
3667: IF (l_delivery_info.tms_interface_flag IN
3668: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;

Line 3669: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,

3665: IF (p_tms_interface_flag_tab(i) IS NULL) THEN
3666: --assume regular update
3667: IF (l_delivery_info.tms_interface_flag IN
3668: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3673: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;

Line 3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,

3666: --assume regular update
3667: IF (l_delivery_info.tms_interface_flag IN
3668: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3673: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3674: ELSE

Line 3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN

3667: IF (l_delivery_info.tms_interface_flag IN
3668: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3673: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3674: ELSE
3675: 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 3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;

3668: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3669: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3670: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3673: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3674: ELSE
3675: 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);
3676: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);

Line 3675: 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);

3671: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3672: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3673: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3674: ELSE
3675: 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);
3676: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3677: END IF;
3678: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3679: AND l_delivery_info.tms_interface_flag IN

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

3674: ELSE
3675: 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);
3676: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3677: END IF;
3678: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3679: AND l_delivery_info.tms_interface_flag IN
3680: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3681: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3682: --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 3680: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,

3676: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3677: END IF;
3678: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3679: AND l_delivery_info.tms_interface_flag IN
3680: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3681: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3682: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3683: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3684: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);

Line 3681: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN

3677: END IF;
3678: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3679: AND l_delivery_info.tms_interface_flag IN
3680: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3681: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3682: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3683: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3684: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3685: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED

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

3679: AND l_delivery_info.tms_interface_flag IN
3680: (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3681: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3682: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3683: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3684: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3685: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3686: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3687: --DP stays in DP

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

3681: WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3682: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3683: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3684: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3685: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3686: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3687: --DP stays in DP
3688: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3689: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);

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

3682: --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3683: l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3684: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3685: ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3686: AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3687: --DP stays in DP
3688: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3689: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3690: ELSIF (p_tms_interface_flag_tab(i) IN

Line 3691: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,

3687: --DP stays in DP
3688: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3689: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3690: ELSIF (p_tms_interface_flag_tab(i) IN
3691: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,

Line 3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,

3688: l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3689: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3690: ELSIF (p_tms_interface_flag_tab(i) IN
3691: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,

Line 3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,

3689: l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3690: ELSIF (p_tms_interface_flag_tab(i) IN
3691: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3697: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)

Line 3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,

3690: ELSIF (p_tms_interface_flag_tab(i) IN
3691: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3697: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3698: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN

Line 3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,

3691: (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3697: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3698: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3699: --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,

Line 3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,

3692: WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3697: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3698: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3699: --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,
3700: --do not increment the version

Line 3697: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)

3693: WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3694: WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3695: WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3696: WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3697: WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3698: OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3699: --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,
3700: --do not increment the version
3701: l_new_tms_interface_flag_tab(l_count) := p_tms_interface_flag_tab(i);

Line 3737: UPDATE wsh_new_deliveries

3733: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)
3734: AND (l_delivery_info_tab.COUNT > 0)) THEN
3735:
3736: FORALL j IN l_delivery_info_tab.FIRST..l_delivery_info_tab.LAST
3737: UPDATE wsh_new_deliveries
3738: SET
3739: TMS_VERSION_NUMBER = l_new_tms_version_number_tab(j)
3740: ,TMS_INTERFACE_FLAG = l_new_tms_interface_flag_tab(j)
3741: ,last_update_date = SYSDATE

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

3811: END IF;
3812:
3813: WHEN others THEN
3814: ROLLBACK TO tms_update;
3815: wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);
3816: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3817: IF l_debug_on THEN
3818: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3819: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');

Line 3825: END WSH_NEW_DELIVERIES_PVT;

3821:
3822: END UPDATE_TMS_INTERFACE_FLAG;
3823: --END OTM R12
3824:
3825: END WSH_NEW_DELIVERIES_PVT;