[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_DETAILS_PKG as
2: /* $Header: WSHDDTHB.pls 120.32 2011/05/20 12:39:26 ashimalh ship $ */
3:
4:
5: --
2: /* $Header: WSHDDTHB.pls 120.32 2011/05/20 12:39:26 ashimalh 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;
284:
285: -- 2530743 : To get Default DFF values
286:
287: WSH_FLEXFIELD_UTILS.Get_DFF_Defaults
288: (p_flexfield_name => 'WSH_DELIVERY_DETAILS',
289: p_default_values => l_dff_attribute,
290: p_default_context => l_dff_context,
291: p_update_flag => l_dff_update_flag,
292: x_return_status => l_dff_ret_status);
485:
486: /*** J changes TP release ****/
487: --
488: FORALL i in 1..p_num_of_rec
489: INSERT INTO wsh_delivery_details(
490: source_code,
491: source_header_id,
492: delivery_detail_id,
493: source_line_id,
700: consignee_flag --RTV changes
701: ) VALUES
702: (l_delivery_details_info.source_code,
703: l_delivery_details_info.source_header_id,
704: wsh_delivery_details_s.nextval, --lpn conv
705: decode( l_delivery_details_info.container_flag , 'Y', wsh_delivery_details_s.currval, l_delivery_details_info.source_line_id),
706: l_delivery_details_info.customer_id,
707: l_delivery_details_info.sold_to_contact_id,
708: l_delivery_details_info.inventory_item_id,
701: ) VALUES
702: (l_delivery_details_info.source_code,
703: l_delivery_details_info.source_header_id,
704: wsh_delivery_details_s.nextval, --lpn conv
705: decode( l_delivery_details_info.container_flag , 'Y', wsh_delivery_details_s.currval, l_delivery_details_info.source_line_id),
706: l_delivery_details_info.customer_id,
707: l_delivery_details_info.sold_to_contact_id,
708: l_delivery_details_info.inventory_item_id,
709: l_delivery_details_info.item_description,
836: l_delivery_details_info.released_status,
837: l_delivery_details_info.container_flag,
838: l_delivery_details_info.container_type_code,
839: decode(l_delivery_details_info.container_flag,'Y',
840: nvl(p_container_info_rec.container_names(i), NVL(l_delivery_details_info.container_name,wsh_delivery_details_s.currval)),
841: l_delivery_details_info.container_name),
842: l_delivery_details_info.fill_percent,
843: l_delivery_details_info.gross_weight,
844: l_delivery_details_info.master_serial_number,
923: RETURNING delivery_detail_id BULK COLLECT INTO x_dd_id_tab;
924:
925: --
926: IF l_debug_on THEN
927: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS');
928: END IF;
929: --
930:
931: -- DBI Project
928: END IF;
929: --
930:
931: -- DBI Project
932: -- Insert of wsh_delivery_details, call DBI API after the insert.
933: -- This API will also check for DBI Installed or not
934: IF l_debug_on THEN
935: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',x_dd_id_tab.count);
936: END IF;
969: WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_DELIVERY_DETAILS_BULK ' || SQLERRM );
970: END IF;
971: --
972: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
973: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS_BULK',l_module_name);
974: --
975: IF l_debug_on THEN
976: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
977: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1002: l_container_name varchar2(50);
1003:
1004: CURSOR C_Del_detail_ID
1005: IS
1006: SELECT wsh_delivery_details_s.nextval
1007: FROM sys.dual;
1008:
1009: l_row_count NUMBER;
1010: l_temp_id NUMBER;
1071:
1072: /* Note : J TP release : ignore_for_planning will be defaulted from the old record*/
1073: --
1074: IF l_debug_on THEN
1075: WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE INSERTING INTO WSH_DELIVERY_DETAILS' ||l_dd_id_tab.count );
1076: END IF;
1077: --
1078: FORALL i in 1..p_num_of_rec
1079: INSERT INTO wsh_delivery_details(
1075: WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE INSERTING INTO WSH_DELIVERY_DETAILS' ||l_dd_id_tab.count );
1076: END IF;
1077: --
1078: FORALL i in 1..p_num_of_rec
1079: INSERT INTO wsh_delivery_details(
1080: source_code,
1081: source_header_id,
1082: source_line_id,
1083: customer_id,
1538: decode(p_delivery_detail_rec.shipment_line_number,NULL,wdd.shipment_line_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.shipment_line_number),
1539: decode(p_delivery_detail_rec.reference_line_id,NULL,wdd.reference_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.reference_line_id),
1540: -- TPW - Distributed Organization Changes - End
1541: decode(p_delivery_detail_rec.consignee_flag,NULL,wdd.consignee_flag,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.consignee_flag) --RTV changes
1542: FROM WSH_DELIVERY_DETAILS wdd
1543: WHERE delivery_detail_id = p_delivery_detail_id;
1544:
1545: --
1546: IF l_debug_on THEN
1543: WHERE delivery_detail_id = p_delivery_detail_id;
1544:
1545: --
1546: IF l_debug_on THEN
1547: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
1548: END IF;
1549: --
1550:
1551: x_dd_id_tab := l_dd_id_tab; -- unable to use RETURNING INTO .. BULK COLLECT above
1552:
1553: --BUG#12575789 Called WMS wave plan API for every delivery detail split
1554: SELECT organization_id
1555: INTO p_organization_id
1556: FROM wsh_delivery_details
1557: WHERE delivery_detail_id = p_delivery_detail_id;
1558:
1559: IF l_debug_on THEN
1560: wsh_debug_sv.Logmsg(l_module_name, 'ORG ID - '|| p_organization_id);
1596: END IF;
1597: END IF;
1598:
1599: -- DBI Project
1600: -- Insert of wsh_delivery_details, call DBI API after the insert.
1601: -- This API will also check for DBI Installed or not
1602: IF l_debug_on THEN
1603: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',x_dd_id_tab.count);
1604: END IF;
1628: WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_DD_FROM_OLD_BULK ' || SQLERRM );
1629: END IF;
1630: --
1631: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1632: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DD_FROM_OLD_BULK',l_module_name);
1633: --
1634: IF l_debug_on THEN
1635: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1636: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1755: --
1756: EXCEPTION
1757: WHEN others THEN
1758: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1759: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',l_module_name);
1760:
1761: --
1762: IF l_debug_on THEN
1763: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1778: l_container_name varchar2(50);
1779:
1780: CURSOR C_Del_Detail_Rowid
1781: IS SELECT rowid
1782: FROM wsh_delivery_details
1783: WHERE delivery_detail_id = x_delivery_detail_id;
1784:
1785: CURSOR C_Del_detail_ID
1786: IS
1783: WHERE delivery_detail_id = x_delivery_detail_id;
1784:
1785: CURSOR C_Del_detail_ID
1786: IS
1787: SELECT wsh_delivery_details_s.nextval
1788: FROM sys.dual;
1789:
1790: CURSOR C_Check_Detail_ID
1791: IS SELECT rowid
1788: FROM sys.dual;
1789:
1790: CURSOR C_Check_Detail_ID
1791: IS SELECT rowid
1792: FROM wsh_delivery_details
1793: WHERE delivery_detail_id = x_delivery_detail_id;
1794:
1795: l_row_count NUMBER;
1796: l_temp_id NUMBER;
1820: END IF;
1821: --
1822: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1823: -- make call to Bulk api from here
1824: WSH_DELIVERY_DETAILS_PKG.create_dd_from_old_bulk
1825: ( p_delivery_detail_rec => p_delivery_detail_rec,
1826: p_delivery_detail_id => p_delivery_detail_id,
1827: p_num_of_rec => 1,
1828: x_dd_id_tab => l_dd_id_tab,
1832: x_delivery_detail_id := l_dd_id_tab(1);
1833:
1834: --
1835: IF l_debug_on THEN
1836: WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
1837: END IF;
1838: --
1839: OPEN C_Del_Detail_Rowid;
1840: FETCH C_Del_Detail_Rowid INTO x_row_id;
1856: WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_NEW_DETAIL_FROM_OLD ' || SQLERRM );
1857: END IF;
1858: --
1859: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1860: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_NEW_DETAIL_FROM_OLD',l_module_name);
1861: --
1862: IF l_debug_on THEN
1863: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1864: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1894: wdd.transaction_temp_id,
1895: wda.delivery_id,
1896: nvl(wdd.ignore_for_planning, 'N'), -- OTM R12
1897: nvl(wdd.gross_weight, 0) -- OTM R12 : packing ECO
1898: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
1899: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1900: and wdd.rowid = v_rowid;
1901:
1902: CURSOR get_del_detail_by_id(v_delivery_detail_id NUMBER) IS
1909: wdd.transaction_temp_id,
1910: wda.delivery_id,
1911: nvl(wdd.ignore_for_planning, 'N'), -- OTM R12
1912: nvl(wdd.gross_weight, 0) -- OTM R12 : packing ECO
1913: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
1914: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1915: and wdd.delivery_detail_id = v_delivery_detail_id;
1916:
1917: l_delivery_detail_id NUMBER := NULL;
1921: l_organization_id NUMBER := NULL;
1922: l_subinventory VARCHAR2(10) := NULL;
1923: l_serial_number VARCHAR2(30) := NULL;
1924: l_transaction_temp_id NUMBER := NULL;
1925: l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
1926: l_return_status VARCHAR2(5) := NULL;
1927:
1928: l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI changes
1929: l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1936:
1937: -- OTM R12
1938: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1939: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1940: l_ignore WSH_DELIVERY_DETAILS.IGNORE_FOR_PLANNING%TYPE;
1941: l_is_delivery_empty VARCHAR2(1);
1942: l_gc3_is_installed VARCHAR2(1);
1943: l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE; -- packing ECO
1944: l_call_update VARCHAR2(1);
1939: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1940: l_ignore WSH_DELIVERY_DETAILS.IGNORE_FOR_PLANNING%TYPE;
1941: l_is_delivery_empty VARCHAR2(1);
1942: l_gc3_is_installed VARCHAR2(1);
1943: l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE; -- packing ECO
1944: l_call_update VARCHAR2(1);
1945: -- end of OTM R12
1946:
1947: --
2014: IF l_delivery_detail_id IS NOT NULL THEN
2015: IF l_serial_number IS NOT NULL OR l_transaction_temp_id IS NOT NULL THEN
2016: --
2017: IF l_debug_on THEN
2018: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
2019: END IF;
2020: --
2021: WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
2022: p_delivery_detail_id => l_delivery_detail_id,
2017: IF l_debug_on THEN
2018: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
2019: END IF;
2020: --
2021: WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
2022: p_delivery_detail_id => l_delivery_detail_id,
2023: p_inventory_item_id => l_inventory_item_id,
2024: p_organization_id => l_organization_id,
2025: p_subinventory => l_subinventory,
2027: x_return_status => l_return_status);
2028:
2029: --
2030: IF l_debug_on THEN
2031: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
2032: END IF;
2033: --
2034: WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
2035: p_delivery_detail_id => l_delivery_detail_id,
2030: IF l_debug_on THEN
2031: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
2032: END IF;
2033: --
2034: WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
2035: p_delivery_detail_id => l_delivery_detail_id,
2036: p_serial_number_code => l_inv_controls_rec.serial_code,
2037: p_serial_number => l_serial_number,
2038: p_transaction_temp_id => l_transaction_temp_id,
2048:
2049: -- bug 4416863
2050: IF (l_delivery_id is not null) THEN
2051: IF l_debug_on THEN
2052: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_delivery_details_actions.unassign_detail_from_delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
2053: END IF;
2054: wsh_delivery_details_actions.unassign_detail_from_delivery (
2055: p_detail_id => l_delivery_detail_id,
2056: x_return_status => l_return_status);
2050: IF (l_delivery_id is not null) THEN
2051: IF l_debug_on THEN
2052: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_delivery_details_actions.unassign_detail_from_delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
2053: END IF;
2054: wsh_delivery_details_actions.unassign_detail_from_delivery (
2055: p_detail_id => l_delivery_detail_id,
2056: x_return_status => l_return_status);
2057: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
2058: (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2068: END IF;
2069: -- end bug 4416863
2070:
2071: IF p_cancel_flag = 'Y' THEN
2072: UPDATE wsh_delivery_details
2073: SET move_order_line_id = NULL ,
2074: released_status = 'D',
2075: requested_quantity = 0,
2076: requested_quantity2 = 0,
2105: WSH_DEBUG_SV.log(l_module_name,'Rows Updated',SQL%ROWCOUNT);
2106: END IF;
2107: l_dbi_dml_type := 'UPDATE';
2108: ELSE -- else of cancel_flag='Y'
2109: DELETE FROM wsh_delivery_details
2110: WHERE delivery_detail_id = l_delivery_detail_id;
2111: IF l_debug_on THEN
2112: WSH_DEBUG_SV.log(l_module_name,'Rows deleted from wsh_delivery_details',SQL%ROWCOUNT);
2113: END IF;
2108: ELSE -- else of cancel_flag='Y'
2109: DELETE FROM wsh_delivery_details
2110: WHERE delivery_detail_id = l_delivery_detail_id;
2111: IF l_debug_on THEN
2112: WSH_DEBUG_SV.log(l_module_name,'Rows deleted from wsh_delivery_details',SQL%ROWCOUNT);
2113: END IF;
2114: l_dbi_dml_type := 'DELETE';
2115:
2116: DELETE FROM wsh_delivery_assignments
2177: END IF;-- end of cancel_flag='Y'
2178:
2179: -- DBI Call for either update/delete using l_delivery_detail_id
2180: -- DBI Project
2181: -- Update of wsh_delivery_details where requested_quantity/released_status
2182: -- are changed, call DBI API after the update.
2183: -- This API will also check for DBI Installed or not
2184: IF l_debug_on THEN
2185: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_delivery_detail_id);
2209: --
2210: EXCEPTION
2211: WHEN others THEN
2212: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2213: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',l_module_name);
2214:
2215: -- close all cursors at exception
2216: IF (get_del_detail%ISOPEN) THEN
2217: CLOSE get_del_detail;
2244: IS
2245:
2246: CURSOR C_LOCK_DELIVERY_DETAIL IS
2247: SELECT *
2248: FROM WSH_DELIVERY_DETAILS
2249: WHERE ROWID = p_rowid
2250: FOR UPDATE OF DELIVERY_DETAIL_ID NOWAIT;
2251: recinfo C_LOCK_DELIVERY_DETAIL%ROWTYPE;
2252:
3253: close c_lock_delivery_detail;
3254: end if;
3255:
3256: FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
3257: FND_MESSAGE.Set_Token('PACKAGE', 'WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS');
3258: FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
3259: FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
3260:
3261: --
3310: shipped_quantity, -- OTM R12
3311: -- bug# 6719369 (replenishment project):
3312: released_status,
3313: replenishment_status
3314: FROM wsh_delivery_details
3315: WHERE delivery_detail_id = p_delivery_details_info.delivery_detail_id;
3316:
3317: -- OTM R12 : packing ECO
3318: CURSOR c_get_delivery_info(p_delivery_detail_id IN NUMBER) IS
3333: SELECT wdd.source_line_id,
3334: wdd.requested_quantity,
3335: wdd.requested_quantity2,
3336: wda.delivery_id
3337: FROM wsh_delivery_details wdd,
3338: wsh_delivery_assignments wda
3339: WHERE wdd.delivery_detail_id = p_delivery_details_info.delivery_detail_id
3340: AND wdd.delivery_detail_id = wda.delivery_detail_id;
3341:
3338: wsh_delivery_assignments wda
3339: WHERE wdd.delivery_detail_id = p_delivery_details_info.delivery_detail_id
3340: AND wdd.delivery_detail_id = wda.delivery_detail_id;
3341:
3342: l_cons_source_line_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3343: l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type ; --Stores the dd_ids returned by Consolidate_Source_Line
3344: l_back_order_consolidation VARCHAR2(1) := 'N';
3345: l_req NUMBER;
3346: l_req2 NUMBER;
3349: l_global_param_rec WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3350: -- 6749200 (replenishment project) : end
3351:
3352: l_delivery_id WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE;
3353: l_requested_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
3354: l_shipped_quantity WSH_DELIVERY_DETAILS.SHIPPED_QUANTITY%TYPE;
3355: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3356: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3357: l_gc3_is_installed VARCHAR2(1);
3350: -- 6749200 (replenishment project) : end
3351:
3352: l_delivery_id WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE;
3353: l_requested_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
3354: l_shipped_quantity WSH_DELIVERY_DETAILS.SHIPPED_QUANTITY%TYPE;
3355: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3356: l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3357: l_gc3_is_installed VARCHAR2(1);
3358: -- End of OTM R12
3833: );
3834:
3835: END IF;
3836:
3837: UPDATE wsh_delivery_details
3838: SET
3839: delivery_detail_id = p_delivery_details_info.delivery_detail_id,
3840: unit_weight = l_unit_weight_item,
3841: unit_volume = l_unit_volume_item,
4042: END IF;
4043: --
4044: --
4045: -- DBI Project
4046: -- Update of wsh_delivery_details where requested_quantity/released_status
4047: -- are changed, call DBI API after the update.
4048: -- DBI API checks for DBI Installed also
4049: IF l_debug_on THEN
4050: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_details_info.delivery_detail_id);
4286: END IF;
4287: --
4288: WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec, l_return_status);
4289: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4290: wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
4291: x_return_status := l_return_status;
4292: END IF;
4293: IF (l_global_param_rec.consolidate_bo_lines = 'Y') THEN
4294: --{
4305: --
4306: -- Debug Statements
4307: --
4308: IF l_debug_on THEN
4309: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
4310: END IF;
4311: --
4312: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
4313: p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
4308: IF l_debug_on THEN
4309: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
4310: END IF;
4311: --
4312: WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
4313: p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
4314: x_consolidate_ids => l_cons_dd_ids,
4315: x_return_status => l_return_status);
4316: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )THEN
4376:
4377: WHEN others THEN
4378: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4379: ROLLBACK TO s_before_update_dd;
4380: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_DETAILS',l_module_name);
4381:
4382: IF (c_get_delivery_info%ISOPEN) THEN
4383: CLOSE c_get_delivery_info;
4384: END IF;
4470: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Create_Deliv_assignment_Bulk with 1');
4471: END IF;
4472: -- from this API call Bulk api with value of 1
4473:
4474: WSH_DELIVERY_DETAILS_PKG.Create_Deliv_Assignment_Bulk
4475: (p_delivery_assignments_info => p_delivery_assignments_info,
4476: p_num_of_rec => 1,
4477: p_dd_id_tab => l_dd_id_tab,
4478: x_da_id_tab => l_da_id_tab,
4496: --
4497: EXCEPTION
4498: WHEN others THEN
4499: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4500: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',l_module_name);
4501:
4502: --
4503: IF l_debug_on THEN
4504: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4585: --
4586: EXCEPTION
4587: WHEN others THEN
4588: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4589: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG_.DELETE_DELIVERY_ASSIGNMENTS',l_module_name);
4590:
4591: --
4592: IF l_debug_on THEN
4593: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4687: --
4688: EXCEPTION
4689: WHEN others THEN
4690: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4691: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_ASSIGNMENTS',l_module_name);
4692:
4693: --
4694: IF l_debug_on THEN
4695: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4805: END IF;
4806: --
4807: EXCEPTION
4808: WHEN others THEN
4809: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_ASSIGNMENTS',l_module_name);
4810: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4811: --
4812: IF l_debug_on THEN
4813: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4955: WHEN OTHERS THEN
4956: --
4957: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4958: --
4959: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS_WRAPPER',l_module_name);
4960: --
4961: IF l_debug_on THEN
4962: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4963: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4989: IS
4990:
4991: CURSOR c_lock_details_of_dlvy(p_dlvy_id NUMBER) IS
4992: SELECT wdd.delivery_detail_id
4993: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
4994: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
4995: AND wda.delivery_id = p_dlvy_id
4996: FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
4997:
4996: FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
4997:
4998: CURSOR c_lock_detail(p_detail_id NUMBER) IS
4999: SELECT delivery_detail_id
5000: FROM wsh_delivery_details
5001: WHERE delivery_detail_id = p_delivery_detail_id
5002: FOR UPDATE NOWAIT;
5003:
5004: l_dummy_detail_id NUMBER;
5065: -- Procedure: Table_To_Record
5066: -- Parameters: x_delivery_detail_rec: A record of all attributes of a Delivery detail Record
5067: -- p_delivery_detail_id : delivery_detail_id of the detail that is to be copied
5068: -- Return_Status,
5069: -- Description: This procedure will copy the attributes of a delivery detail in wsh_delivery_details
5070: -- and copy it to a record.
5071:
5072: Procedure Table_To_Record(
5073: p_delivery_detail_id IN NUMBER,
5288: ,shipment_line_number
5289: ,reference_line_id
5290: -- TPW - Distributed Organization Changes - End
5291: ,consignee_flag --RTV chagnes
5292: FROM wsh_delivery_details
5293: WHERE delivery_detail_id = p_delivery_detail_id;
5294:
5295: l_debug_on BOOLEAN;
5296: --
5334: END IF;
5335: --
5336: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
5337: --
5338: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.Table_to_Record',l_module_name);
5339: --
5340: IF l_debug_on THEN
5341: 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');
5342: END IF;
5436: RAISE;
5437:
5438: END LOCK_WDA_NO_COMPARE;
5439:
5440: END WSH_DELIVERY_DETAILS_PKG;
5441: