[Home] [Help]
18: l_debug_on BOOLEAN;
19: --
20: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IMPORT_RECORDS';
21: --
22: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
23: l_organization_tab WSH_UTIL_CORE.id_tab_type; --Pick To POD WF Project
24: l_wf_rs VARCHAR2(1); --Pick To POD WF Project
25: l_dbi_rs VARCHAR2(1); -- DBI Project
26:
19: --
20: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IMPORT_RECORDS';
21: --
22: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
23: l_organization_tab WSH_UTIL_CORE.id_tab_type; --Pick To POD WF Project
24: l_wf_rs VARCHAR2(1); --Pick To POD WF Project
25: l_dbi_rs VARCHAR2(1); -- DBI Project
26:
27: BEGIN
38: WSH_DEBUG_SV.push(l_module_name);
39: --
40: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
41: END IF;
42: l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
43: <
44: FOR l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST LOOP
45: IF p_changed_attributes(l_counter).action_flag = 'I' THEN
46: Import_Delivery_Details(
46: Import_Delivery_Details(
47: p_source_line_id => p_changed_attributes(l_counter).source_line_id,
48: p_source_code => p_source_code,
49: x_return_status => l_rs);
50: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
51: EXIT records_loop;
52: END IF;
53:
54: IF (NVL(p_changed_attributes(l_counter).released_status,'@') <> FND_API.G_MISS_CHAR) THEN
100:
101: IF l_debug_on THEN
102: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
103: END IF;
104: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
105: x_return_status := l_dbi_rs;
106: -- just pass this return status to caller API
107: IF l_debug_on THEN
108: WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
126: END IF;
127: --
128: EXCEPTION
129: WHEN OTHERS THEN
130: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
131: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Import_Records',l_module_name);
132: --
133: IF l_debug_on THEN
134: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
127: --
128: EXCEPTION
129: WHEN OTHERS THEN
130: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
131: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Import_Records',l_module_name);
132: --
133: IF l_debug_on THEN
134: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
135: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
149: -- source_line_id desc
150: -- date_requested desc
151: --
152:
153: PROCEDURE sort_splits ( p_index_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type ,
154: p_split_table IN OUT NOCOPY split_Table_tab_type,
155: x_return_status OUT NOCOPY VARCHAR2 )
156: IS
157: l_tmp NUMBER ;
180: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
181: END IF;
182:
183: -- add the original line also in the l_split_table
184: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
185:
186: IF l_debug_on THEN
187: WSH_DEBUG_SV.logmsg(l_module_name,'Inside sort splits ' );
188: END IF;
283:
284:
285: EXCEPTION
286: WHEN OTHERS THEN
287: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
288: IF l_debug_on THEN
289: WSH_DEBUG_SV.logmsg(l_module_name,'Exception in sort_splits' );
290: WSH_DEBUG_SV.logmsg(l_module_name, SQLERRM || ' : ' || SQLCODE );
291: END IF;
310: PROCEDURE Split_Records_Int (
311: p_source_code IN VARCHAR2,
312: p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
313: p_interface_flag IN VARCHAR2,
314: p_index_tab IN WSH_UTIL_CORE.ID_Tab_Type ,
315: p_split_lines IN WSH_USA_ACTIONS_PVT.split_Table_Tab_type ,
316: x_return_status OUT NOCOPY VARCHAR2
317: ) IS
318:
461: l_detailed_qty NUMBER;
462: l_detailed_qty2 NUMBER;
463: l_prev_source_line_id NUMBER;
464:
465: l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete delivery lines pending overpick
466:
467: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
468: l_dbi_rs VARCHAR2(1); -- DBI Project
469:
463: l_prev_source_line_id NUMBER;
464:
465: l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete delivery lines pending overpick
466:
467: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
468: l_dbi_rs VARCHAR2(1); -- DBI Project
469:
470:
471: --
485:
486: l_total_spare_rsv NUMBER ;
487: -- HW OPMCONV - Added Qty2
488: l_total_spare_rsv2 NUMBER ;
489: l_total_rsv_to_split WSH_UTIL_CORE.id_Tab_type ;
490: -- HW OPMCONV - Added Qty2
491: l_total_rsv_to_split2 WSH_UTIL_CORE.id_Tab_type ;
492:
493: l_inventory_item_id NUMBER;
487: -- HW OPMCONV - Added Qty2
488: l_total_spare_rsv2 NUMBER ;
489: l_total_rsv_to_split WSH_UTIL_CORE.id_Tab_type ;
490: -- HW OPMCONV - Added Qty2
491: l_total_rsv_to_split2 WSH_UTIL_CORE.id_Tab_type ;
492:
493: l_inventory_item_id NUMBER;
494: l_organization_id NUMBER;
495: l_last_orig_line_id NUMBER;
495: l_last_orig_line_id NUMBER;
496:
497: l_pickable_flag VARCHAR2(1);
498:
499: l_sdd_tab WSH_UTIL_CORE.Id_Tab_Type ; -- to store delivery details marked as 'R'
500: l_total_reserved_quantity NUMBER ;
501: -- HW OPMCONV - Added Qty2
502: l_total_reserved_quantity2 NUMBER ;
503: l_total_requested_quantity NUMBER ;
526: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
527: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
528: END IF;
529: --
530: l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
531: SAVEPOINT before_splits;
532:
533: l_total_requested_quantity := 0 ;
534: -- HW OPMCONV - Added Qty2
587: --in Rel. to warehouse status as described above, the flow needs to
588: --stop
589: FND_MESSAGE.SET_NAME('WSH', 'WSH_SPLIT_NOT_ALLOWED');
590: FND_MESSAGE.SET_TOKEN('ORDER', c.source_header_number);
591: WSH_UTIL_CORE.add_message(l_rs,l_module_name);
592: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
593: ROLLBACK to before_splits;
594: IF l_debug_on THEN
595: WSH_DEBUG_SV.logmsg(l_module_name,'RAISE ERROR AS SPLIT IS NOT ALLOWED');
588: --stop
589: FND_MESSAGE.SET_NAME('WSH', 'WSH_SPLIT_NOT_ALLOWED');
590: FND_MESSAGE.SET_TOKEN('ORDER', c.source_header_number);
591: WSH_UTIL_CORE.add_message(l_rs,l_module_name);
592: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
593: ROLLBACK to before_splits;
594: IF l_debug_on THEN
595: WSH_DEBUG_SV.logmsg(l_module_name,'RAISE ERROR AS SPLIT IS NOT ALLOWED');
596: WSH_DEBUG_SV.pop(l_module_name);
669: WSH_DEBUG_SV.log(l_module_name,'Inside If l_quantity_to_split2', l_quantity_to_split2);
670: END IF;
671: IF p_changed_attributes(l_counter).ordered_quantity > 0
672: AND l_quantity_to_split <= 0 THEN
673: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
674: FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_CANNOT_SPLIT');
675: WSH_UTIL_CORE.add_message(l_rs,l_module_name);
676: EXIT records_loop;
677: END IF;
671: IF p_changed_attributes(l_counter).ordered_quantity > 0
672: AND l_quantity_to_split <= 0 THEN
673: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
674: FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_CANNOT_SPLIT');
675: WSH_UTIL_CORE.add_message(l_rs,l_module_name);
676: EXIT records_loop;
677: END IF;
678: END IF; -- NOT l_uom_converted
679:
765: x_detailed_qty => l_detailed_qty,
766: x_detailed_qty2 => l_detailed_qty2,
767: x_return_status => x_return_status);
768:
769: IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
770: IF l_debug_on THEN
771: WSH_DEBUG_SV.log(l_module_name,'get_detailed_quantity returned error');
772: EXIT records_loop;
773: END IF;
873:
874: IF l_debug_on THEN
875: WSH_DEBUG_SV.log(l_module_name,'l_rs',l_rs);
876: END IF;
877: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
878: EXIT records_loop;
879: END IF;
880:
881: --bug# 6719369 (replenishment project) (begin) : call WMS for whenever there is split on replenishment requested
892: p_primary_quantity => c.requested_quantity - l_quantity_split,
893: p_split_delivery_detail_id => l_working_detail_id,
894: p_split_source_line_id => p_changed_attributes(l_counter).source_line_id,
895: x_return_status => x_return_status);
896: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
897: --{
898: IF l_debug_on THEN
899: WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
900: WSH_DEBUG_SV.pop(l_module_name);
958: IF l_debug_on THEN
959: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
960: END IF;
961: --{
962: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
963: x_return_status := l_dbi_rs;
964: ROLLBACK to before_splits;
965: -- just pass this return status to caller API
966: IF l_debug_on THEN
1019: x_detailed_qty => l_detailed_qty,
1020: x_detailed_qty2 => l_detailed_qty2,
1021: x_return_status => x_return_status);
1022:
1023: IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1024: IF l_debug_on THEN
1025: WSH_DEBUG_SV.log(l_module_name,'get_detailed_quantity returned error');
1026: EXIT records_loop;
1027: END IF;
1082: p_move_order_line_status => l_move_order_line_status,
1083: p_direction_flag => p_split_lines(i).direction_flag ,
1084: x_return_status => l_rs);
1085:
1086: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1087: EXIT records_loop;
1088: END IF;
1089: ELSE
1090: l_total_unstaged_quantity := l_total_unstaged_quantity + NVL(c.picked_quantity, c.requested_quantity);
1119: p_order_line_id => p_changed_attributes(l_counter).source_line_id,
1120: x_return_status => l_rs,
1121: x_msg_count => l_msg_count,
1122: x_msg_data => l_msg_data);
1123: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1124: IF l_debug_on THEN
1125: WSH_DEBUG_SV.log(l_module_name,'wf_update_after_inv_unreserv returned error');
1126: END IF;
1127: EXIT records_loop;
1135: p_order_line_id => p_changed_attributes(l_counter).original_source_line_id,
1136: x_return_status => l_rs,
1137: x_msg_count => l_msg_count,
1138: x_msg_data => l_msg_data);
1139: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1140: IF l_debug_on THEN
1141: WSH_DEBUG_SV.log(l_module_name,'wf_update_after_inv_unreserv returned error');
1142: END IF;
1143: EXIT records_loop;
1303: x_detailed_qty => l_detailed_qty,
1304: x_detailed_qty2 => l_detailed_qty2,
1305: x_return_status => x_return_status);
1306:
1307: IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1308: IF l_debug_on THEN
1309: WSH_DEBUG_SV.log(l_module_name,'get_detailed_quantity returned error');
1310: EXIT unstaged_rsv_loop;
1311: END IF;
1394: p_move_order_line_status => l_move_order_line_status, -- spltrsv:this needs to be set correctly.
1395: p_direction_flag => p_split_lines(l_counter).direction_flag ,
1396: x_return_status => l_rs);
1397:
1398: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1399: IF l_debug_on THEN
1400: WSH_DEBUG_SV.logmsg(l_module_name,'unepected error after WSH_USA_INV_PVT.Split_Reservation ');
1401: END IF;
1402: exit unstaged_rsv_loop ;
1419: END IF ; --} Action_flag = 'S'
1420: END LOOP; --}
1421:
1422: -- purge delivery details pending overpick
1423: IF l_rs NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1424: AND l_delete_dds.count > 0 THEN
1425: WSH_INTERFACE.Delete_Details(
1426: p_details_id => l_delete_dds,
1427: x_return_status => l_rs
1427: x_return_status => l_rs
1428: );
1429: END IF;
1430:
1431: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1432: ROLLBACK to before_splits;
1433: END IF;
1434:
1435: x_return_status := l_rs;
1441: --
1442: EXCEPTION
1443: WHEN OTHERS THEN
1444: ROLLBACK TO before_splits;
1445: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1446: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_ACTIONS_PVT.Split_Records_Int',l_module_name);
1447: --
1448: IF l_debug_on THEN
1449: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1442: EXCEPTION
1443: WHEN OTHERS THEN
1444: ROLLBACK TO before_splits;
1445: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1446: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_ACTIONS_PVT.Split_Records_Int',l_module_name);
1447: --
1448: IF l_debug_on THEN
1449: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1450: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1459: x_return_status OUT NOCOPY VARCHAR2
1460: ) IS
1461: l_index number ;
1462: l_direction_flag direction_flag_tab_type ;
1463: l_index_tab WSH_UTIL_CORE.ID_Tab_Type ;
1464: l_split_table split_Table_Tab_Type ;
1465: l_smallest_orig_line_id NUMBER ;
1466: l_updates_count NUMBER ;
1467: l_original_line_found BOOLEAN ;
1486: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1487: WSH_DEBUG_SV.logmsg(l_module_name,'Inside Split_Records');
1488: END IF;
1489:
1490: l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1491:
1492: -- Populate p_split_table
1493: l_smallest_orig_line_id := 0 ;
1494: l_updates_count := 0 ;
1565: sort_splits (p_index_tab => l_index_tab ,
1566: p_split_table => l_split_table,
1567: x_return_status => l_return_status );
1568:
1569: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1570: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1571: return ;
1572: END IF;
1573: end if ;
1566: p_split_table => l_split_table,
1567: x_return_status => l_return_status );
1568:
1569: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1570: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1571: return ;
1572: END IF;
1573: end if ;
1574:
1596: p_split_lines => l_split_table,
1597: x_return_status => l_return_status
1598: ) ;
1599:
1600: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1601: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1602: IF l_debug_on THEN
1603: WSH_DEBUG_SV.logmsg(l_module_name,' Error in split_records_int ' );
1604: END IF;
1597: x_return_status => l_return_status
1598: ) ;
1599:
1600: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1601: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1602: IF l_debug_on THEN
1603: WSH_DEBUG_SV.logmsg(l_module_name,' Error in split_records_int ' );
1604: END IF;
1605: END IF;
1610:
1611: EXCEPTION
1612:
1613: WHEN others THEN
1614: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_ACTIONS_PVT.Split_records ');
1615: IF l_debug_on THEN
1616: WSH_DEBUG_SV.logmsg(l_module_name,SQLCODE || ' : ' || SQLERRM );
1617: END IF;
1618: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1614: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_ACTIONS_PVT.Split_records ');
1615: IF l_debug_on THEN
1616: WSH_DEBUG_SV.logmsg(l_module_name,SQLCODE || ' : ' || SQLERRM );
1617: END IF;
1618: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1619:
1620: END split_Records ;
1621:
1622: PROCEDURE Update_Records(
1651: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1652: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1653: END IF;
1654: --
1655: l_rs := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1656: <
1657: FOR l_counter IN p_changed_attributes.FIRST .. p_changed_attributes.LAST LOOP
1658:
1659: IF p_changed_attributes(l_counter).action_flag IN ('S', 'U') THEN
1679: -- bug 5387341: to prevent data corruption, we should return
1680: -- error when update is not allowed, since that means that
1681: -- the delivery detail(s) will not be synchronized with the
1682: -- order line.
1683: IF NVL(l_rs, 'X') <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1684: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
1685: END IF;
1686: END IF;
1687:
1680: -- error when update is not allowed, since that means that
1681: -- the delivery detail(s) will not be synchronized with the
1682: -- order line.
1683: IF NVL(l_rs, 'X') <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1684: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
1685: END IF;
1686: END IF;
1687:
1688: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1684: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
1685: END IF;
1686: END IF;
1687:
1688: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1689: EXIT records_loop;
1690: END IF;
1691: --
1692: END IF; -- p_interface_flag = 'Y'
1697: p_attributes_rec => p_changed_attributes(l_counter),
1698: p_changed_detail => l_changed_detail,
1699: x_return_status => l_rs);
1700:
1701: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1702: EXIT records_loop;
1703: END IF;
1704: --
1705: IF l_debug_on THEN
1720: END IF;
1721: --
1722: EXCEPTION
1723: WHEN OTHERS THEN
1724: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1725: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Update_Records',l_module_name);
1726: --
1727: IF l_debug_on THEN
1728: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1721: --
1722: EXCEPTION
1723: WHEN OTHERS THEN
1724: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1725: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.Update_Records',l_module_name);
1726: --
1727: IF l_debug_on THEN
1728: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1729: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1826: l_wh_type VARCHAR2(3);
1827: l_ignore_for_planning VARCHAR2(1);
1828: l_groupbysmc VARCHAR2(1);
1829: l_groupbycarrier VARCHAR2(1);
1830: l_in_ids wsh_util_core.id_tab_type;
1831: l_datetype VARCHAR2(30);
1832: l_smc wsh_delivery_details.ship_method_code%TYPE;
1833: l_carrierid wsh_delivery_details.carrier_id%TYPE;
1834: l_orgid wsh_delivery_details.organization_id%TYPE;
1840:
1841: l_mark_reprice_flag VARCHAR2(1) := 'N';
1842: l_delivery_detail_id NUMBER ;
1843: m NUMBER := 0;
1844: l_details_marked WSH_UTIL_CORE.Id_Tab_Type;
1845: --4410272
1846: l_tp_details WSH_UTIL_CORE.Id_Tab_Type;
1847: l_return_status VARCHAR2(1);
1848: mark_reprice_error EXCEPTION;
1842: l_delivery_detail_id NUMBER ;
1843: m NUMBER := 0;
1844: l_details_marked WSH_UTIL_CORE.Id_Tab_Type;
1845: --4410272
1846: l_tp_details WSH_UTIL_CORE.Id_Tab_Type;
1847: l_return_status VARCHAR2(1);
1848: mark_reprice_error EXCEPTION;
1849:
1850:
1896: --
1897: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1898: END IF;
1899: --
1900: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1901:
1902: --OTM R12 Start Org-Specific
1903: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1904: IF l_gc3_is_installed IS NULL THEN
1899: --
1900: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1901:
1902: --OTM R12 Start Org-Specific
1903: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1904: IF l_gc3_is_installed IS NULL THEN
1905: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1906: END IF;
1907: IF l_debug_on THEN
1901:
1902: --OTM R12 Start Org-Specific
1903: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1904: IF l_gc3_is_installed IS NULL THEN
1905: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1906: END IF;
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.log(l_module_name,'l_gc3_is_installed',l_gc3_is_installed);
1909: END IF;
1978: -- bug#6407943 (end):Needs to change items org dependent attributes when org changes.
1979:
1980: IF p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM THEN
1981:
1982: WSH_UTIL_CORE.GET_LOCATION_ID('ORG', p_attributes_rec.ship_from_org_id,
1983: l_ship_from_location_id, x_return_status);
1984:
1985: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1986: IF l_debug_on THEN
1981:
1982: WSH_UTIL_CORE.GET_LOCATION_ID('ORG', p_attributes_rec.ship_from_org_id,
1983: l_ship_from_location_id, x_return_status);
1984:
1985: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1986: IF l_debug_on THEN
1987: WSH_DEBUG_SV.log(l_module_name,'get_reservable_flag returned error');
1988: END IF;
1989: raise Update_Failed;
2006: x_pickable_flag => l_pickable_flag);
2007:
2008: IF p_attributes_rec.ship_to_org_id <> FND_API.G_MISS_NUM THEN
2009:
2010: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.ship_to_org_id,
2011: l_ship_to_location_id, x_return_status);
2012:
2013: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2014: IF l_debug_on THEN
2009:
2010: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.ship_to_org_id,
2011: l_ship_to_location_id, x_return_status);
2012:
2013: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2014: IF l_debug_on THEN
2015: WSH_DEBUG_SV.log(l_module_name,'GET_LOCATION_ID failed');
2016: END IF;
2017: raise Update_Failed;
2020: END IF;
2021:
2022: IF (p_attributes_rec.deliver_to_org_id IS NOT NULL)
2023: AND (p_attributes_rec.deliver_to_org_id <> FND_API.G_MISS_NUM) THEN
2024: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.deliver_to_org_id,
2025: l_deliver_to_location_id, x_return_status);
2026:
2027: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2028: IF l_debug_on THEN
2023: AND (p_attributes_rec.deliver_to_org_id <> FND_API.G_MISS_NUM) THEN
2024: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.deliver_to_org_id,
2025: l_deliver_to_location_id, x_return_status);
2026:
2027: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2028: IF l_debug_on THEN
2029: WSH_DEBUG_SV.log(l_module_name,'procedure GET_LOCATION_ID failed');
2030: END IF;
2031: raise Update_Failed;
2035:
2036: IF (p_attributes_rec.intmed_ship_to_org_id IS NOT NULL)
2037: AND (p_attributes_rec.intmed_ship_to_org_id <> FND_API.G_MISS_NUM) THEN
2038: --
2039: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.intmed_ship_to_org_id,
2040: l_intmed_ship_to_location_id, x_return_status);
2041:
2042: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2043: IF l_debug_on THEN
2038: --
2039: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.intmed_ship_to_org_id,
2040: l_intmed_ship_to_location_id, x_return_status);
2041:
2042: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2043: IF l_debug_on THEN
2044: WSH_DEBUG_SV.log(l_module_name,'program GET_LOCATION_ID failed');
2045: END IF;
2046: raise Update_Failed;
2052: l_deliver_to_location_id := l_ship_to_location_id;
2053: ELSIF (l_deliver_to_location_id is NULL) AND (l_ship_to_location_id IS NULL)
2054: AND (p_attributes_rec.ship_to_org_id <> FND_API.G_MISS_NUM) THEN
2055: --
2056: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.ship_to_org_id,
2057: l_deliver_to_location_id, x_return_status);
2058:
2059: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2060: IF l_debug_on THEN
2055: --
2056: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', p_attributes_rec.ship_to_org_id,
2057: l_deliver_to_location_id, x_return_status);
2058:
2059: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2060: IF l_debug_on THEN
2061: WSH_DEBUG_SV.log(l_module_name,'GET_LOCATION_ID failed for ship_to');
2062: END IF;
2063: raise Update_Failed;
2080: WSH_DEBUG_SV.log(l_module_name,'After call to WSH_SHIPPING_PARAMS_PVT.Get l_return_status ',l_return_status);
2081: WSH_DEBUG_SV.log(l_module_name,'l_shipping_param_info.otm_enabled ',l_shipping_param_info.otm_enabled);
2082: END IF;
2083:
2084: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2085: Raise Update_failed;
2086: END IF;
2087: End If;
2088: END IF;
2088: END IF;
2089: --OTM R12 End
2090:
2091: /* J TP Release */
2092: IF (WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y') OR
2093: (l_gc3_is_installed = 'Y')THEN --OTM R12 Org-Specific. Added the second OR condition.
2094: b_ignore:=FALSE;
2095: --find atleast 1 detail with null or 'N' ignore
2096: OPEN c_get_tpdetails;
2137: IF l_debug_on THEN
2138: WSH_DEBUG_SV.log(l_module_name,' Get_Warehouse_Type l_wh_type,l_return_status',l_wh_type||l_return_status);
2139: END IF;
2140:
2141: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2142: IF l_debug_on THEN
2143: WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type failed');
2144: END IF;
2145: raise Update_Failed;
2183: WSH_CARRIERS_GRP.get_carrier_service_mode(
2184: p_carrier_service_inout_rec => l_carrier_rec,
2185: x_return_status => x_return_status);
2186:
2187: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2188: IF l_debug_on THEN
2189: WSH_DEBUG_SV.log(l_module_name,'procedure get_carrier_service_mode failed');
2190: END IF;
2191: raise Update_Failed;
2717: WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
2718: p_entity_type => 'DELIVERY_DETAIL',
2719: p_entity_ids => l_details_marked,
2720: x_return_status => l_return_status);
2721: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2722: raise mark_reprice_error;
2723: END IF;
2724: END IF;
2725: END IF;
2738: p_entity => 'DLVB',
2739: p_entity_ids => l_tp_details,
2740: x_return_status => l_return_status);
2741: -- }
2742: IF (l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2743: WSH_INTERFACE.PrintMsg(name=>'WSH_CALC_CONT_DEL_TPDATES',
2744: txt=>'Error in calculating Container/Delivery TP dates ');
2745: IF l_debug_on THEN
2746: WSH_DEBUG_SV.log(l_module_name,'Error in calculating Container/Delivery TP dates ');
2762:
2763: IF l_debug_on THEN
2764: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2765: END IF;
2766: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2767: x_return_status := l_dbi_rs;
2768: ROLLBACK to before_changes;
2769: -- just pass this return status to caller API
2770: IF l_debug_on THEN
2783: EXCEPTION
2784: WHEN mark_reprice_error then
2785: FND_MESSAGE.Set_Name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
2786: x_return_status := l_return_status;
2787: WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2788: --
2789: IF l_debug_on THEN
2790: WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2791: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
2804: CLOSE c_is_reservable;
2805: END IF;
2806: --
2807: ROLLBACK TO before_changes;
2808: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2809: wsh_util_core.default_handler('WSH_USA_ACTIONS.Update_Attributes',l_module_name);
2810: --
2811: IF l_debug_on THEN
2812: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2805: END IF;
2806: --
2807: ROLLBACK TO before_changes;
2808: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2809: wsh_util_core.default_handler('WSH_USA_ACTIONS.Update_Attributes',l_module_name);
2810: --
2811: IF l_debug_on THEN
2812: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2813: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3194:
3195: --OTM R12
3196: l_delivery_detail_tab WSH_ENTITY_INFO_TAB;
3197: l_delivery_detail_rec WSH_ENTITY_INFO_REC;
3198: l_item_quantity_uom_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3199: l_gc3_is_installed VARCHAR2(1);
3200: --
3201:
3202: --
3219: --
3220: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
3221: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
3222: END IF;
3223: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3224:
3225: --OTM R12, initialize
3226: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3227: IF l_gc3_is_installed IS NULL THEN
3222: END IF;
3223: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3224:
3225: --OTM R12, initialize
3226: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3227: IF l_gc3_is_installed IS NULL THEN
3228: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
3229: END IF;
3230: IF (l_gc3_is_installed = 'Y') THEN
3224:
3225: --OTM R12, initialize
3226: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3227: IF l_gc3_is_installed IS NULL THEN
3228: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
3229: END IF;
3230: IF (l_gc3_is_installed = 'Y') THEN
3231: l_delivery_detail_tab := WSH_ENTITY_INFO_TAB();
3232: l_delivery_detail_tab.EXTEND;
3236: i := 0;
3237: IF (P_SOURCE_CODE <> 'OE') THEN
3238: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SOURCE_CODE');
3239: FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
3240: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3241: WSH_UTIL_CORE.add_message(x_return_status,l_module_name);
3242: --
3243: IF l_debug_on THEN
3244: WSH_DEBUG_SV.pop(l_module_name);
3237: IF (P_SOURCE_CODE <> 'OE') THEN
3238: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SOURCE_CODE');
3239: FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
3240: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3241: WSH_UTIL_CORE.add_message(x_return_status,l_module_name);
3242: --
3243: IF l_debug_on THEN
3244: WSH_DEBUG_SV.pop(l_module_name);
3245: END IF;
3296: FETCH C_SPECIFIC_ITEM_INFO INTO haz_class_id, prim_uom_code, weight_uom, unit_weight,
3297: volume_uom, unit_volume, transactable_flag;
3298: CLOSE c_specific_item_info;
3299: WSH_INTERFACE.PrintMsg(txt=>'Importing order line ' || ln_rec_info.line_id);
3300: WSH_UTIL_CORE.GET_LOCATION_ID('ORG', ln_rec_info.ship_from_org_id,
3301: l_ship_from_location_id, l_location_status);
3302:
3303: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3304: IF l_debug_on THEN
3299: WSH_INTERFACE.PrintMsg(txt=>'Importing order line ' || ln_rec_info.line_id);
3300: WSH_UTIL_CORE.GET_LOCATION_ID('ORG', ln_rec_info.ship_from_org_id,
3301: l_ship_from_location_id, l_location_status);
3302:
3303: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3304: IF l_debug_on THEN
3305: WSH_DEBUG_SV.log(l_module_name,'Failed to get location for line: '||ln_rec_info.line_id||' org:'||ln_rec_info.ship_from_org_id);
3306: END IF;
3307: l_booked_ln_excpn := TRUE;
3308: GOTO start_over;
3309: END IF;
3310: WSH_INTERFACE.PrintMsg(txt=>'From organization:'|| ln_rec_info.ship_from_org_id||' loc:'|| l_ship_from_location_id );
3311:
3312: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.ship_to_org_id,
3313: l_ship_to_location_id, l_location_status);
3314: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3315: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3316: txt=>'Failed to get location for line: '||ln_rec_info.line_id||
3310: WSH_INTERFACE.PrintMsg(txt=>'From organization:'|| ln_rec_info.ship_from_org_id||' loc:'|| l_ship_from_location_id );
3311:
3312: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.ship_to_org_id,
3313: l_ship_to_location_id, l_location_status);
3314: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3315: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3316: txt=>'Failed to get location for line: '||ln_rec_info.line_id||
3317: ' ship to org:'||ln_rec_info.ship_to_org_id);
3318: IF l_debug_on THEN
3323: END IF;
3324: WSH_INTERFACE.PrintMsg(txt=>'To organization:'|| ln_rec_info.ship_to_org_id||' loc:'|| l_ship_to_location_id );
3325:
3326: IF (ln_rec_info.deliver_to_org_id IS NOT NULL) THEN
3327: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.deliver_to_org_id,
3328: l_deliver_to_location_id, l_location_status);
3329: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3330: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3331: txt=> 'Failed to get location for line: '||ln_rec_info.line_id||
3325:
3326: IF (ln_rec_info.deliver_to_org_id IS NOT NULL) THEN
3327: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.deliver_to_org_id,
3328: l_deliver_to_location_id, l_location_status);
3329: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3330: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3331: txt=> 'Failed to get location for line: '||ln_rec_info.line_id||
3332: ' delivery to org:'||ln_rec_info.deliver_to_org_id);
3333: IF l_debug_on THEN
3339: END IF;
3340: WSH_INTERFACE.PrintMsg(txt=>'Deliver to org:'|| ln_rec_info.deliver_to_org_id||' loc:'|| l_deliver_to_location_id );
3341:
3342: IF (ln_rec_info.intmed_ship_to_org_id IS NOT NULL) THEN
3343: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.intmed_ship_to_org_id,
3344: l_intmed_ship_to_location_id, l_location_status);
3345: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3346: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3347: txt=> 'Failed to get location for line: '||ln_rec_info.line_id||
3341:
3342: IF (ln_rec_info.intmed_ship_to_org_id IS NOT NULL) THEN
3343: WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE', ln_rec_info.intmed_ship_to_org_id,
3344: l_intmed_ship_to_location_id, l_location_status);
3345: IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3346: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_NO_LOCATION_FOR_ORG',
3347: txt=> 'Failed to get location for line: '||ln_rec_info.line_id||
3348: ' intermediate ship to org:'||ln_rec_info.intmed_ship_to_org_id);
3349: IF l_debug_on THEN
3409: x_latest_pickup_date => l_delivery_details_info.latest_pickup_date,
3410: x_earliest_dropoff_date => l_delivery_details_info.earliest_dropoff_date,
3411: x_latest_dropoff_date => l_delivery_details_info.latest_dropoff_date
3412: );
3413: IF (l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3414: WSH_INTERFACE.PrintMsg(name=>'WSH_CALC_TP_DATES',
3415: txt=>'Error in calculating TP dates: '||ln_rec_info.order_date_type_code||ln_rec_info.latest_acceptable_date||ln_rec_info.promise_date||ln_rec_info.schedule_arrival_date||ln_rec_info.earliest_acceptable_date||ln_rec_info.earliest_ship_date);
3416: IF l_debug_on THEN
3417: WSH_DEBUG_SV.log(l_module_name,'Error in calculating TP dates: ' ||
3632:
3633: WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Details( l_delivery_details_info, dummy_rowid,
3634: DUMMY_DELIVERY_DETAIL_ID,
3635: l_cr_dt_status );
3636: IF (l_cr_dt_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3637: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_CREATE_DET_FAILED',
3638: txt=>'Create_delivery_details failed for line: ' || ln_rec_info.line_id );
3639: WSH_UTIL_CORE.Get_Messages('N', l_cr_as_summary, l_cr_as_details, l_cr_as_count);
3640: WSH_INTERFACE.PrintMsg(txt=>'Creation of the delivery details failed because of ' || l_cr_as_summary || ':'|| l_cr_as_details);
3635: l_cr_dt_status );
3636: IF (l_cr_dt_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3637: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_CREATE_DET_FAILED',
3638: txt=>'Create_delivery_details failed for line: ' || ln_rec_info.line_id );
3639: WSH_UTIL_CORE.Get_Messages('N', l_cr_as_summary, l_cr_as_details, l_cr_as_count);
3640: WSH_INTERFACE.PrintMsg(txt=>'Creation of the delivery details failed because of ' || l_cr_as_summary || ':'|| l_cr_as_details);
3641: l_booked_ln_excpn := TRUE;
3642: GOTO start_over;
3643: END IF;
3656:
3657: -- Call default container after create a new delivery detail to populate
3658: -- default container
3659: WSH_INTERFACE.Default_Container(dummy_delivery_detail_id, l_return_status);
3660: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3661: WSH_INTERFACE.PrintMsg(txt=>'Default container failed for dd:'||dummy_delivery_detail_id);
3662: END IF;
3663:
3664: l_delivery_assignments_info.delivery_id := NULL;
3669: WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Assignments( l_delivery_assignments_info,
3670: dummy_assgn_rowid,
3671: DUMMY_DELIVERY_ASSIGNMENT_ID,
3672: l_cr_as_status);
3673: IF (l_cr_as_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3674: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_CREATE_AS_FAILED',
3675: txt=>'Create Delivery assignments failed for order line:' || ln_rec_info.line_id);
3676: WSH_UTIL_CORE.Get_Messages('N', l_cr_as_summary, l_cr_as_details, l_cr_as_count);
3677: WSH_INTERFACE.PrintMsg(txt=>'Creation of the delivery assignments failed because of ' || l_cr_as_summary || ':'|| l_cr_as_details);
3672: l_cr_as_status);
3673: IF (l_cr_as_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3674: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_CREATE_AS_FAILED',
3675: txt=>'Create Delivery assignments failed for order line:' || ln_rec_info.line_id);
3676: WSH_UTIL_CORE.Get_Messages('N', l_cr_as_summary, l_cr_as_details, l_cr_as_count);
3677: WSH_INTERFACE.PrintMsg(txt=>'Creation of the delivery assignments failed because of ' || l_cr_as_summary || ':'|| l_cr_as_details);
3678: l_booked_ln_excpn := TRUE;
3679: GOTO start_over;
3680: END IF;
3723: -- we will not fail based on l_return_status here because
3724: -- we do not want to stop the flow
3725: -- if the detail doesn't split, it will be caught later in
3726: -- delivery splitting and will have exception on the detail
3727: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3728: WSH_INTERFACE.PrintMsg(txt=>'Delivery detail split failed for dd:'||dummy_delivery_detail_id);
3729: END IF;
3730:
3731: END IF;
3750: x_msg_count => l_msg_count,
3751: x_msg_data => l_msg_data);
3752:
3753:
3754: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) or (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3755: THEN
3756: --
3757: IF l_debug_on THEN
3758: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE RETURNED ' || L_RETURN_STATUS );
3759: END IF;
3760: --
3761: WSH_INTERFACE.PrintMsg(name=>'WSH_DET_PROCESS_ORDER_FAILED',
3762: txt=>'Update_Shipping_Interface failed for line:' || ln_rec_info.line_id );
3763: WSH_UTIL_CORE.Get_Messages('N',l_summary, l_details, l_get_msg_count);
3764: WSH_INTERFACE.PrintMsg(txt=>'no. of OE messages :'||l_msg_count);
3765: FOR k IN 1 .. l_msg_count LOOP
3766: l_msg_data := OE_MSG_PUB.GET( p_msg_index => k, p_encoded => 'F');
3767: --
3850: WSH_DEBUG_SV.logmsg(l_module_name, 'IN LOCK LINE ID EXCEPTION: WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS' );
3851: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: ' || SQLERRM );
3852: END IF;
3853: --
3854: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
3855: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3856: CLOSE C_OE_LINES_REC_LOCK;
3857: END IF;
3858: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3854: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
3855: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3856: CLOSE C_OE_LINES_REC_LOCK;
3857: END IF;
3858: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3859: when invalid_org THEN
3860: --
3861: IF l_debug_on THEN
3862: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID_ORG' );
3865: fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_ORG');
3866: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3867: CLOSE C_OE_LINES_REC_LOCK;
3868: END IF;
3869: WSH_UTIL_CORE.add_message ('E',l_module_name);
3870: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3871: when invalid_cust_site THEN
3872: --
3873: IF l_debug_on THEN
3866: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3867: CLOSE C_OE_LINES_REC_LOCK;
3868: END IF;
3869: WSH_UTIL_CORE.add_message ('E',l_module_name);
3870: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3871: when invalid_cust_site THEN
3872: --
3873: IF l_debug_on THEN
3874: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID_CUST_SITE' );
3877: fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_SITE');
3878: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3879: CLOSE C_OE_LINES_REC_LOCK;
3880: END IF;
3881: WSH_UTIL_CORE.add_message ('E',l_module_name);
3882: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3883: when invalid_qty_or_uom THEN
3884: --
3885: IF l_debug_on THEN
3878: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3879: CLOSE C_OE_LINES_REC_LOCK;
3880: END IF;
3881: WSH_UTIL_CORE.add_message ('E',l_module_name);
3882: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3883: when invalid_qty_or_uom THEN
3884: --
3885: IF l_debug_on THEN
3886: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID_QTY_OR_UOM' );
3889: fnd_message.set_name('WSH','WSH_QTY_OR_UOM_NOT_VALID');
3890: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3891: CLOSE C_OE_LINES_REC_LOCK;
3892: END IF;
3893: WSH_UTIL_CORE.add_message ('E',l_module_name);
3894: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3895: when process_order_failed THEN
3896: --
3897: IF l_debug_on THEN
3890: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3891: CLOSE C_OE_LINES_REC_LOCK;
3892: END IF;
3893: WSH_UTIL_CORE.add_message ('E',l_module_name);
3894: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3895: when process_order_failed THEN
3896: --
3897: IF l_debug_on THEN
3898: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: PROCESS_ORDER_FAILED' );
3901: fnd_message.set_name('WSH', 'WSH_DET_PROCESS_ORDER_FAILED');
3902: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3903: CLOSE C_OE_LINES_REC_LOCK;
3904: END IF;
3905: WSH_UTIL_CORE.add_message ('E',l_module_name);
3906: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3907: when create_details_failed THEN
3908: --
3909: IF l_debug_on THEN
3902: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3903: CLOSE C_OE_LINES_REC_LOCK;
3904: END IF;
3905: WSH_UTIL_CORE.add_message ('E',l_module_name);
3906: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3907: when create_details_failed THEN
3908: --
3909: IF l_debug_on THEN
3910: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION:CREATE_DETAILS_FAILED' );
3913: fnd_message.set_name('WSH', 'WSH_DET_CREATE_DET_FAILED');
3914: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3915: CLOSE C_OE_LINES_REC_LOCK;
3916: END IF;
3917: WSH_UTIL_CORE.add_message ('E',l_module_name);
3918: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3919: when create_assignments_failed THEN
3920: --
3921: IF l_debug_on THEN
3914: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3915: CLOSE C_OE_LINES_REC_LOCK;
3916: END IF;
3917: WSH_UTIL_CORE.add_message ('E',l_module_name);
3918: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3919: when create_assignments_failed THEN
3920: --
3921: IF l_debug_on THEN
3922: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: CREATE_ASSIGNMENTS_FAILED' );
3925: fnd_message.set_name('WSH', 'WSH_DET_CREATE_AS_FAILED');
3926: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3927: CLOSE C_OE_LINES_REC_LOCK;
3928: END IF;
3929: WSH_UTIL_CORE.add_message ('E',l_module_name);
3930: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3931: WHEN invalid_ship_set THEN -- bug 2373131
3932: --
3933: IF l_debug_on THEN
3926: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3927: CLOSE C_OE_LINES_REC_LOCK;
3928: END IF;
3929: WSH_UTIL_CORE.add_message ('E',l_module_name);
3930: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3931: WHEN invalid_ship_set THEN -- bug 2373131
3932: --
3933: IF l_debug_on THEN
3934: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID SHIP SET' );
3939: fnd_message.set_token('LINE_NUMBER',ln_rec_info.line_number);
3940: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3941: CLOSE C_OE_LINES_REC_LOCK;
3942: END IF;
3943: WSH_UTIL_CORE.add_message ('E',l_module_name);
3944: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3945:
3946: -- HW OPM BUG#:2677054
3947: -- HW OPMCONV. Removed OPM exceptions
3940: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3941: CLOSE C_OE_LINES_REC_LOCK;
3942: END IF;
3943: WSH_UTIL_CORE.add_message ('E',l_module_name);
3944: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3945:
3946: -- HW OPM BUG#:2677054
3947: -- HW OPMCONV. Removed OPM exceptions
3948:
3951: IF l_debug_on THEN
3952: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: OTHERS ' || SQLERRM );
3953: END IF;
3954: --
3955: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
3956: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3957: CLOSE C_OE_LINES_REC_LOCK;
3958: END IF;
3959: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3955: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
3956: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
3957: CLOSE C_OE_LINES_REC_LOCK;
3958: END IF;
3959: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3960: END; -- Lock Check Block (1902176)
3961: END IF; --} --Bugfix 6740363
3962:
3963: END LOOP;
3972: WSH_DEBUG_SV.log(l_module_name, 'l_pull_lines_count',l_pull_lines_count);
3973: END IF;
3974:
3975: IF (l_pull_lines_count = 0 and p_source_line_id IS NOT NULL) THEN
3976: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3977:
3978: fnd_message.set_name('WSH', 'WSH_DET_CREATE_LINE_FAILED');
3979: WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3980:
3975: IF (l_pull_lines_count = 0 and p_source_line_id IS NOT NULL) THEN
3976: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3977:
3978: fnd_message.set_name('WSH', 'WSH_DET_CREATE_LINE_FAILED');
3979: WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3980:
3981:
3982: IF l_debug_on THEN
3983: WSH_DEBUG_SV.logmsg(l_module_name, 'Cursor C_PULL_DELIVERY_DETAIL DID NOT FOUND ANY LINES');
4006: END IF;
4007: IF (C_PULL_ONE_LINE%ISOPEN) THEN
4008: CLOSE C_PULL_ONE_LINE;
4009: END IF;
4010: WSH_UTIL_CORE.add_message ('E',l_module_name);
4011: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4012: when others THEN
4013: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4014: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4007: IF (C_PULL_ONE_LINE%ISOPEN) THEN
4008: CLOSE C_PULL_ONE_LINE;
4009: END IF;
4010: WSH_UTIL_CORE.add_message ('E',l_module_name);
4011: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4012: when others THEN
4013: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4014: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4015: --
4009: END IF;
4010: WSH_UTIL_CORE.add_message ('E',l_module_name);
4011: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4012: when others THEN
4013: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4014: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4015: --
4016: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4017: CLOSE C_OE_LINES_REC_LOCK;
4010: WSH_UTIL_CORE.add_message ('E',l_module_name);
4011: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4012: when others THEN
4013: wsh_util_core.default_handler('WSH_USA_ACTIONS_PVT.IMPORT_DELIVERY_DETAILS',l_module_name);
4014: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4015: --
4016: IF C_OE_LINES_REC_LOCK%ISOPEN THEN
4017: CLOSE C_OE_LINES_REC_LOCK;
4018: END IF;