[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_DETAILS_PKG as
2: /* $Header: WSHDDTHB.pls 120.21.12010000.2 2008/08/22 08:02:18 mvudugul ship $ */
3:
4:
5: --
2: /* $Header: WSHDDTHB.pls 120.21.12010000.2 2008/08/22 08:02:18 mvudugul ship $ */
3:
4:
5: --
6: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_PKG';
7: --
8: --
9: -- Procedure: Create_Delivery_Details
10: -- Parameters: All Attributes of a Delivery Detail Record,
25:
26:
27: CURSOR C_Del_Detail_Rowid
28: IS SELECT rowid
29: FROM wsh_delivery_details
30: WHERE delivery_detail_id = x_delivery_detail_id;
31:
32: l_row_count NUMBER;
33: l_container_name varchar2(50);
40:
41: -- bug 3022644 - Cursor to check whether container name is already exist
42: CURSOR Get_Exist_Cont(v_cont_name VARCHAR2,v_wms_flag NUMBER) IS
43: SELECT NVL(MAX(1),0) FROM DUAL
44: WHERE EXISTS ( SELECT 1 FROM WSH_DELIVERY_DETAILS
45: WHERE container_name = v_cont_name
46: AND container_flag = 'Y'
47: -- LPN reuse project
48: AND released_status = Decode(v_wms_flag,'Y','X',released_status));
107: l_container_info_rec.lpn_ids(1) := p_delivery_details_info.lpn_id;
108: l_container_info_rec.container_names(1) :=
109: p_delivery_details_info.container_name;
110:
111: WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Details_Bulk
112: (p_delivery_details_info => p_delivery_details_info,
113: p_num_of_rec => 1,
114: -- lpn conv
115: p_container_info_rec => l_container_info_rec,
120: x_delivery_detail_id := l_dd_id_tab(1);
121:
122: --
123: IF l_debug_on THEN
124: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
125: END IF;
126: --
127: OPEN C_Del_Detail_Rowid;
128: FETCH C_Del_Detail_Rowid INTO x_rowid;
140: EXCEPTION
141: WHEN others THEN
142: --
143: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
144: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS',l_module_name);
145: --
146: IF l_debug_on THEN
147: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
148: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
171:
172: /*
173: CURSOR C_Del_Detail_Rowid
174: IS SELECT rowid
175: FROM wsh_delivery_details
176: WHERE delivery_detail_id = x_delivery_detail_id;
177: */
178:
179: /* lpn conv
178:
179: /* lpn conv
180: CURSOR C_Del_detail_ID
181: IS
182: SELECT wsh_delivery_details_s.nextval
183: FROM sys.dual;
184:
185: */
186:
188: CURSOR c_get_ship_sets(c_p_set_id NUMBER,
189: c_p_source_code VARCHAR,
190: c_p_source_header_id NUMBER) IS
191: SELECT distinct ignore_for_planning
192: FROM wsh_delivery_details
193: WHERE
194: ship_set_id = c_p_set_id and
195: source_code = c_p_source_code and
196: source_header_id = c_p_source_header_id;
282:
283: -- 2530743 : To get Default DFF values
284:
285: WSH_FLEXFIELD_UTILS.Get_DFF_Defaults
286: (p_flexfield_name => 'WSH_DELIVERY_DETAILS',
287: p_default_values => l_dff_attribute,
288: p_default_context => l_dff_context,
289: p_update_flag => l_dff_update_flag,
290: x_return_status => l_dff_ret_status);
475:
476: /*** J changes TP release ****/
477: --
478: FORALL i in 1..p_num_of_rec
479: INSERT INTO wsh_delivery_details(
480: source_code,
481: source_header_id,
482: delivery_detail_id,
483: source_line_id,
679: release_revision_number
680: ) VALUES
681: (l_delivery_details_info.source_code,
682: l_delivery_details_info.source_header_id,
683: wsh_delivery_details_s.nextval, --lpn conv
684: decode( l_delivery_details_info.container_flag , 'Y', wsh_delivery_details_s.currval, l_delivery_details_info.source_line_id),
685: l_delivery_details_info.customer_id,
686: l_delivery_details_info.sold_to_contact_id,
687: l_delivery_details_info.inventory_item_id,
680: ) VALUES
681: (l_delivery_details_info.source_code,
682: l_delivery_details_info.source_header_id,
683: wsh_delivery_details_s.nextval, --lpn conv
684: decode( l_delivery_details_info.container_flag , 'Y', wsh_delivery_details_s.currval, l_delivery_details_info.source_line_id),
685: l_delivery_details_info.customer_id,
686: l_delivery_details_info.sold_to_contact_id,
687: l_delivery_details_info.inventory_item_id,
688: l_delivery_details_info.item_description,
813: l_delivery_details_info.released_status,
814: l_delivery_details_info.container_flag,
815: l_delivery_details_info.container_type_code,
816: decode(l_delivery_details_info.container_flag,'Y',
817: nvl(p_container_info_rec.container_names(i), NVL(l_delivery_details_info.container_name,wsh_delivery_details_s.currval)),
818: l_delivery_details_info.container_name),
819: l_delivery_details_info.fill_percent,
820: l_delivery_details_info.gross_weight,
821: l_delivery_details_info.master_serial_number,
889: RETURNING delivery_detail_id BULK COLLECT INTO x_dd_id_tab;
890:
891: --
892: IF l_debug_on THEN
893: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS');
894: END IF;
895: --
896:
897: -- DBI Project
894: END IF;
895: --
896:
897: -- DBI Project
898: -- Insert of wsh_delivery_details, call DBI API after the insert.
899: -- This API will also check for DBI Installed or not
900: IF l_debug_on THEN
901: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',x_dd_id_tab.count);
902: END IF;
935: WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_DELIVERY_DETAILS_BULK ' || SQLERRM );
936: END IF;
937: --
938: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
939: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS_BULK',l_module_name);
940: --
941: IF l_debug_on THEN
942: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
943: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
968: l_container_name varchar2(50);
969:
970: CURSOR C_Del_detail_ID
971: IS
972: SELECT wsh_delivery_details_s.nextval
973: FROM sys.dual;
974:
975: l_row_count NUMBER;
976: l_temp_id NUMBER;
1018:
1019: /* Note : J TP release : ignore_for_planning will be defaulted from the old record*/
1020: --
1021: IF l_debug_on THEN
1022: WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE INSERTING INTO WSH_DELIVERY_DETAILS' ||l_dd_id_tab.count );
1023: END IF;
1024: --
1025: FORALL i in 1..p_num_of_rec
1026: INSERT INTO wsh_delivery_details(
1022: WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE INSERTING INTO WSH_DELIVERY_DETAILS' ||l_dd_id_tab.count );
1023: END IF;
1024: --
1025: FORALL i in 1..p_num_of_rec
1026: INSERT INTO wsh_delivery_details(
1027: source_code,
1028: source_header_id,
1029: source_line_id,
1030: customer_id,
1447: /*WSH: IB ASUTAR*/
1448: decode(p_delivery_detail_rec.po_revision_number,NULL,wdd.po_revision_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.po_revision_number),
1449: decode(p_delivery_detail_rec.release_revision_number,NULL,wdd.release_revision_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.release_revision_number),
1450: decode(p_delivery_detail_rec.replenishment_status,NULL,wdd.replenishment_status,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.replenishment_status) --bug# 6719369 (replenishment project)
1451: FROM WSH_DELIVERY_DETAILS wdd
1452: WHERE delivery_detail_id = p_delivery_detail_id;
1453:
1454: --
1455: IF l_debug_on THEN
1452: WHERE delivery_detail_id = p_delivery_detail_id;
1453:
1454: --
1455: IF l_debug_on THEN
1456: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
1457: END IF;
1458: --
1459:
1460: x_dd_id_tab := l_dd_id_tab; -- unable to use RETURNING INTO .. BULK COLLECT above
1459:
1460: x_dd_id_tab := l_dd_id_tab; -- unable to use RETURNING INTO .. BULK COLLECT above
1461:
1462: -- DBI Project
1463: -- Insert of wsh_delivery_details, call DBI API after the insert.
1464: -- This API will also check for DBI Installed or not
1465: IF l_debug_on THEN
1466: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',x_dd_id_tab.count);
1467: END IF;
1491: WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_DD_FROM_OLD_BULK ' || SQLERRM );
1492: END IF;
1493: --
1494: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1495: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DD_FROM_OLD_BULK',l_module_name);
1496: --
1497: IF l_debug_on THEN
1498: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1499: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1618: --
1619: EXCEPTION
1620: WHEN others THEN
1621: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1622: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',l_module_name);
1623:
1624: --
1625: IF l_debug_on THEN
1626: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1641: l_container_name varchar2(50);
1642:
1643: CURSOR C_Del_Detail_Rowid
1644: IS SELECT rowid
1645: FROM wsh_delivery_details
1646: WHERE delivery_detail_id = x_delivery_detail_id;
1647:
1648: CURSOR C_Del_detail_ID
1649: IS
1646: WHERE delivery_detail_id = x_delivery_detail_id;
1647:
1648: CURSOR C_Del_detail_ID
1649: IS
1650: SELECT wsh_delivery_details_s.nextval
1651: FROM sys.dual;
1652:
1653: CURSOR C_Check_Detail_ID
1654: IS SELECT rowid
1651: FROM sys.dual;
1652:
1653: CURSOR C_Check_Detail_ID
1654: IS SELECT rowid
1655: FROM wsh_delivery_details
1656: WHERE delivery_detail_id = x_delivery_detail_id;
1657:
1658: l_row_count NUMBER;
1659: l_temp_id NUMBER;
1683: END IF;
1684: --
1685: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1686: -- make call to Bulk api from here
1687: WSH_DELIVERY_DETAILS_PKG.create_dd_from_old_bulk
1688: ( p_delivery_detail_rec => p_delivery_detail_rec,
1689: p_delivery_detail_id => p_delivery_detail_id,
1690: p_num_of_rec => 1,
1691: x_dd_id_tab => l_dd_id_tab,
1695: x_delivery_detail_id := l_dd_id_tab(1);
1696:
1697: --
1698: IF l_debug_on THEN
1699: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
1700: END IF;
1701: --
1702: OPEN C_Del_Detail_Rowid;
1703: FETCH C_Del_Detail_Rowid INTO x_row_id;
1719: WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_NEW_DETAIL_FROM_OLD ' || SQLERRM );
1720: END IF;
1721: --
1722: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1723: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_NEW_DETAIL_FROM_OLD',l_module_name);
1724: --
1725: IF l_debug_on THEN
1726: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1727: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1757: wdd.transaction_temp_id,
1758: wda.delivery_id,
1759: nvl(wdd.ignore_for_planning, 'N'), -- OTM R12
1760: nvl(wdd.gross_weight, 0) -- OTM R12 : packing ECO
1761: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
1762: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1763: and wdd.rowid = v_rowid;
1764:
1765: CURSOR get_del_detail_by_id(v_delivery_detail_id NUMBER) IS
1772: wdd.transaction_temp_id,
1773: wda.delivery_id,
1774: nvl(wdd.ignore_for_planning, 'N'), -- OTM R12
1775: nvl(wdd.gross_weight, 0) -- OTM R12 : packing ECO
1776: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
1777: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1778: and wdd.delivery_detail_id = v_delivery_detail_id;
1779:
1780: l_delivery_detail_id NUMBER := NULL;
1784: l_organization_id NUMBER := NULL;
1785: l_subinventory VARCHAR2(10) := NULL;
1786: l_serial_number VARCHAR2(30) := NULL;
1787: l_transaction_temp_id NUMBER := NULL;
1788: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
1789: l_return_status VARCHAR2(5) := NULL;
1790:
1791: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI changes
1792: l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1799:
1800: -- OTM R12
1801: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1802: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1803: l_ignore WSH_DELIVERY_DETAILS.IGNORE_FOR_PLANNING%TYPE;
1804: l_is_delivery_empty VARCHAR2(1);
1805: l_gc3_is_installed VARCHAR2(1);
1806: l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE; -- packing ECO
1807: l_call_update VARCHAR2(1);
1802: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1803: l_ignore WSH_DELIVERY_DETAILS.IGNORE_FOR_PLANNING%TYPE;
1804: l_is_delivery_empty VARCHAR2(1);
1805: l_gc3_is_installed VARCHAR2(1);
1806: l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE; -- packing ECO
1807: l_call_update VARCHAR2(1);
1808: -- end of OTM R12
1809:
1810: --
1877: IF l_delivery_detail_id IS NOT NULL THEN
1878: IF l_serial_number IS NOT NULL OR l_transaction_temp_id IS NOT NULL THEN
1879: --
1880: IF l_debug_on THEN
1881: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
1882: END IF;
1883: --
1884: WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
1885: p_delivery_detail_id => l_delivery_detail_id,
1880: IF l_debug_on THEN
1881: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
1882: END IF;
1883: --
1884: WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
1885: p_delivery_detail_id => l_delivery_detail_id,
1886: p_inventory_item_id => l_inventory_item_id,
1887: p_organization_id => l_organization_id,
1888: p_subinventory => l_subinventory,
1890: x_return_status => l_return_status);
1891:
1892: --
1893: IF l_debug_on THEN
1894: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1895: END IF;
1896: --
1897: WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
1898: p_delivery_detail_id => l_delivery_detail_id,
1893: IF l_debug_on THEN
1894: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1895: END IF;
1896: --
1897: WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
1898: p_delivery_detail_id => l_delivery_detail_id,
1899: p_serial_number_code => l_inv_controls_rec.serial_code,
1900: p_serial_number => l_serial_number,
1901: p_transaction_temp_id => l_transaction_temp_id,
1911:
1912: -- bug 4416863
1913: IF (l_delivery_id is not null) THEN
1914: IF l_debug_on THEN
1915: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_delivery_details_actions.unassign_detail_from_delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
1916: END IF;
1917: wsh_delivery_details_actions.unassign_detail_from_delivery (
1918: p_detail_id => l_delivery_detail_id,
1919: x_return_status => l_return_status);
1913: IF (l_delivery_id is not null) THEN
1914: IF l_debug_on THEN
1915: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_delivery_details_actions.unassign_detail_from_delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
1916: END IF;
1917: wsh_delivery_details_actions.unassign_detail_from_delivery (
1918: p_detail_id => l_delivery_detail_id,
1919: x_return_status => l_return_status);
1920: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
1921: (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1931: END IF;
1932: -- end bug 4416863
1933:
1934: IF p_cancel_flag = 'Y' THEN
1935: UPDATE wsh_delivery_details
1936: SET move_order_line_id = NULL ,
1937: released_status = 'D',
1938: requested_quantity = 0,
1939: requested_quantity2 = 0,
1966: WSH_DEBUG_SV.log(l_module_name,'Rows Updated',SQL%ROWCOUNT);
1967: END IF;
1968: l_dbi_dml_type := 'UPDATE';
1969: ELSE -- else of cancel_flag='Y'
1970: DELETE FROM wsh_delivery_details
1971: WHERE delivery_detail_id = l_delivery_detail_id;
1972: IF l_debug_on THEN
1973: WSH_DEBUG_SV.log(l_module_name,'Rows deleted from wsh_delivery_details',SQL%ROWCOUNT);
1974: END IF;
1969: ELSE -- else of cancel_flag='Y'
1970: DELETE FROM wsh_delivery_details
1971: WHERE delivery_detail_id = l_delivery_detail_id;
1972: IF l_debug_on THEN
1973: WSH_DEBUG_SV.log(l_module_name,'Rows deleted from wsh_delivery_details',SQL%ROWCOUNT);
1974: END IF;
1975: l_dbi_dml_type := 'DELETE';
1976:
1977: DELETE FROM wsh_delivery_assignments
2042: END IF;-- end of cancel_flag='Y'
2043:
2044: -- DBI Call for either update/delete using l_delivery_detail_id
2045: -- DBI Project
2046: -- Update of wsh_delivery_details where requested_quantity/released_status
2047: -- are changed, call DBI API after the update.
2048: -- This API will also check for DBI Installed or not
2049: IF l_debug_on THEN
2050: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_delivery_detail_id);
2074: --
2075: EXCEPTION
2076: WHEN others THEN
2077: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2078: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',l_module_name);
2079:
2080: -- close all cursors at exception
2081: IF (get_del_detail%ISOPEN) THEN
2082: CLOSE get_del_detail;
2109: IS
2110:
2111: CURSOR C_LOCK_DELIVERY_DETAIL IS
2112: SELECT *
2113: FROM WSH_DELIVERY_DETAILS
2114: WHERE ROWID = p_rowid
2115: FOR UPDATE OF DELIVERY_DETAIL_ID NOWAIT;
2116: recinfo C_LOCK_DELIVERY_DETAIL%ROWTYPE;
2117:
3118: close c_lock_delivery_detail;
3119: end if;
3120:
3121: FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
3122: FND_MESSAGE.Set_Token('PACKAGE', 'WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS');
3123: FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
3124: FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
3125:
3126: --
3175: shipped_quantity, -- OTM R12
3176: -- bug# 6719369 (replenishment project):
3177: released_status,
3178: replenishment_status
3179: FROM wsh_delivery_details
3180: WHERE delivery_detail_id = p_delivery_details_info.delivery_detail_id;
3181:
3182: -- OTM R12 : packing ECO
3183: CURSOR c_get_delivery_info(p_delivery_detail_id IN NUMBER) IS
3198: SELECT wdd.source_line_id,
3199: wdd.requested_quantity,
3200: wdd.requested_quantity2,
3201: wda.delivery_id
3202: FROM wsh_delivery_details wdd,
3203: wsh_delivery_assignments wda
3204: WHERE wdd.delivery_detail_id = p_delivery_details_info.delivery_detail_id
3205: AND wdd.delivery_detail_id = wda.delivery_detail_id;
3206:
3203: wsh_delivery_assignments wda
3204: WHERE wdd.delivery_detail_id = p_delivery_details_info.delivery_detail_id
3205: AND wdd.delivery_detail_id = wda.delivery_detail_id;
3206:
3207: l_cons_source_line_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3208: l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type ; --Stores the dd_ids returned by Consolidate_Source_Line
3209: l_back_order_consolidation VARCHAR2(1) := 'N';
3210: l_req NUMBER;
3211: l_req2 NUMBER;
3214: l_global_param_rec WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3215: -- 6749200 (replenishment project) : end
3216:
3217: l_delivery_id WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE;
3218: l_requested_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
3219: l_shipped_quantity WSH_DELIVERY_DETAILS.SHIPPED_QUANTITY%TYPE;
3220: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3221: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3222: l_gc3_is_installed VARCHAR2(1);
3215: -- 6749200 (replenishment project) : end
3216:
3217: l_delivery_id WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE;
3218: l_requested_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
3219: l_shipped_quantity WSH_DELIVERY_DETAILS.SHIPPED_QUANTITY%TYPE;
3220: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3221: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3222: l_gc3_is_installed VARCHAR2(1);
3223: -- End of OTM R12
3698: );
3699:
3700: END IF;
3701:
3702: UPDATE wsh_delivery_details
3703: SET
3704: delivery_detail_id = p_delivery_details_info.delivery_detail_id,
3705: unit_weight = l_unit_weight_item,
3706: unit_volume = l_unit_volume_item,
3907: END IF;
3908: --
3909: --
3910: -- DBI Project
3911: -- Update of wsh_delivery_details where requested_quantity/released_status
3912: -- are changed, call DBI API after the update.
3913: -- DBI API checks for DBI Installed also
3914: IF l_debug_on THEN
3915: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_details_info.delivery_detail_id);
4151: END IF;
4152: --
4153: WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec, l_return_status);
4154: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4155: wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
4156: x_return_status := l_return_status;
4157: END IF;
4158: IF (l_global_param_rec.consolidate_bo_lines = 'Y') THEN
4159: --{
4170: --
4171: -- Debug Statements
4172: --
4173: IF l_debug_on THEN
4174: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
4175: END IF;
4176: --
4177: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
4178: p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
4173: IF l_debug_on THEN
4174: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
4175: END IF;
4176: --
4177: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
4178: p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
4179: x_consolidate_ids => l_cons_dd_ids,
4180: x_return_status => l_return_status);
4181: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )THEN
4241:
4242: WHEN others THEN
4243: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4244: ROLLBACK TO s_before_update_dd;
4245: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_DETAILS',l_module_name);
4246:
4247: IF (c_get_delivery_info%ISOPEN) THEN
4248: CLOSE c_get_delivery_info;
4249: END IF;
4335: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Create_Deliv_assignment_Bulk with 1');
4336: END IF;
4337: -- from this API call Bulk api with value of 1
4338:
4339: WSH_DELIVERY_DETAILS_PKG.Create_Deliv_Assignment_Bulk
4340: (p_delivery_assignments_info => p_delivery_assignments_info,
4341: p_num_of_rec => 1,
4342: p_dd_id_tab => l_dd_id_tab,
4343: x_da_id_tab => l_da_id_tab,
4361: --
4362: EXCEPTION
4363: WHEN others THEN
4364: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4365: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',l_module_name);
4366:
4367: --
4368: IF l_debug_on THEN
4369: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4450: --
4451: EXCEPTION
4452: WHEN others THEN
4453: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4454: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG_.DELETE_DELIVERY_ASSIGNMENTS',l_module_name);
4455:
4456: --
4457: IF l_debug_on THEN
4458: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4552: --
4553: EXCEPTION
4554: WHEN others THEN
4555: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4556: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_ASSIGNMENTS',l_module_name);
4557:
4558: --
4559: IF l_debug_on THEN
4560: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4670: END IF;
4671: --
4672: EXCEPTION
4673: WHEN others THEN
4674: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_ASSIGNMENTS',l_module_name);
4675: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4676: --
4677: IF l_debug_on THEN
4678: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4820: WHEN OTHERS THEN
4821: --
4822: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4823: --
4824: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS_WRAPPER',l_module_name);
4825: --
4826: IF l_debug_on THEN
4827: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4828: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4854: IS
4855:
4856: CURSOR c_lock_details_of_dlvy(p_dlvy_id NUMBER) IS
4857: SELECT wdd.delivery_detail_id
4858: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
4859: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
4860: AND wda.delivery_id = p_dlvy_id
4861: FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
4862:
4861: FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
4862:
4863: CURSOR c_lock_detail(p_detail_id NUMBER) IS
4864: SELECT delivery_detail_id
4865: FROM wsh_delivery_details
4866: WHERE delivery_detail_id = p_delivery_detail_id
4867: FOR UPDATE NOWAIT;
4868:
4869: l_dummy_detail_id NUMBER;
4930: -- Procedure: Table_To_Record
4931: -- Parameters: x_delivery_detail_rec: A record of all attributes of a Delivery detail Record
4932: -- p_delivery_detail_id : delivery_detail_id of the detail that is to be copied
4933: -- Return_Status,
4934: -- Description: This procedure will copy the attributes of a delivery detail in wsh_delivery_details
4935: -- and copy it to a record.
4936:
4937: Procedure Table_To_Record(
4938: p_delivery_detail_id IN NUMBER,
5137: ,service_level
5138: ,po_revision_number
5139: ,release_revision_number
5140: ,replenishment_status --bug# 6689448 (replenishment project)
5141: FROM wsh_delivery_details
5142: WHERE delivery_detail_id = p_delivery_detail_id;
5143:
5144: l_debug_on BOOLEAN;
5145: --
5182: END IF;
5183: --
5184: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
5185: --
5186: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.Table_to_Record',l_module_name);
5187: --
5188: IF l_debug_on THEN
5189: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL); WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5190: END IF;
5284: RAISE;
5285:
5286: END LOCK_WDA_NO_COMPARE;
5287:
5288: END WSH_DELIVERY_DETAILS_PKG;
5289: