[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIPMENT_REQUEST_PUB
Source
1 PACKAGE BODY WSH_SHIPMENT_REQUEST_PUB as
2 /* $Header: WSHSRPBB.pls 120.0.12010000.6 2009/12/03 12:23:19 mvudugul noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SHIPMENT_REQUEST_PUB';
6
7 --========================================================================
8 --PRIVATE APIS
9
10 PROCEDURE Create_Shipment_Request
11 ( p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
12 p_caller IN VARCHAR2,
13 x_return_status OUT NOCOPY VARCHAR2);
14
15 PROCEDURE Query_Shipment_Request
16 ( p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
17 x_interface_errors_info OUT NOCOPY Interface_Errors_Rec_Tab,
18 x_return_status OUT NOCOPY VARCHAR2);
19
20 PROCEDURE Update_Delete_Shipment_Request
21 (
22 p_action_code IN VARCHAR2,
23 p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
24 x_return_status OUT NOCOPY VARCHAR2);
25 --========================================================================
26
27 --========================================================================
28 -- PROCEDURE : Shipment_Request PUBLIC
29 --
30 -- PARAMETERS: p_api_version_number known api version
31 -- p_init_msg_list FND_API.G_TRUE to reset list
32 -- p_action_code 'QUERY', 'CREATE', 'UPDATE' or 'DELETE'
33 -- p_shipment_request_info Attributes for the shipment request entity
34 -- The attributes Documnet_number document revision are
35 -- mandatory parameters in all the cases.
36 -- The parameter 'line_number' should be passed if
37 -- the action is 'QUERY/UPDATE/DELETE' and only
38 -- the user is concerned with only a part of the set of
39 -- delivery details available for the document.
40 -- x_return_status return status
41 -- x_msg_count number of messages in the list
42 -- x_msg_data text of messages
43 -- VERSION : current version 1.0
44 -- initial version 1.0
45 -- COMMENT : Creates or Updates or Deletes Shipment Request
46 -- specified in p_shipment_request_info
47 --========================================================================
48 PROCEDURE Shipment_Request(
49 p_api_version_number IN NUMBER,
50 p_init_msg_list IN VARCHAR2 ,
51 p_action_code IN VARCHAR2 ,
52 p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
53 x_interface_errors_info OUT NOCOPY Interface_Errors_Rec_Tab,
54 p_commit IN VARCHAR2 ,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2) AS
58
59 --
60 l_return_status VARCHAR2(100);
61 l_msg_count NUMBER;
62 l_msg_data VARCHAR2(1000);
63 --
64 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
65 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Shipment_Request';
66 l_invalid_inputs NUMBER;
67 --
68 l_api_version_number CONSTANT NUMBER := 1.0;
69 l_api_name CONSTANT VARCHAR2(30):= 'Shipment_Request';
70 --
71
72 BEGIN
73 --
74 IF l_debug_on THEN
75 wsh_debug_sv.push(l_module_name);
76 END IF;
77 -- Standard call to check for call compatibility
78 IF NOT FND_API.Compatible_API_Call
79 ( l_api_version_number
80 , p_api_version_number
81 , l_api_name
82 , G_PKG_NAME
83 )
84 THEN
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 -- Initialize message stack if required
89 IF FND_API.to_Boolean(p_init_msg_list) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code);
96 WSH_DEBUG_SV.log(l_module_name,'Number of WDDI Records',p_shipment_request_info.shipment_details_tab.count);
97 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
98 WSH_DEBUG_SV.logmsg(l_module_name, '===============================================');
99 WSH_DEBUG_SV.logmsg(l_module_name, '| WNDI RECORD DETAILS |');
100 WSH_DEBUG_SV.logmsg(l_module_name, '===============================================');
101 WSH_DEBUG_SV.log(l_module_name,'document_number', p_shipment_request_info.document_number);
102 WSH_DEBUG_SV.log(l_module_name,'document_revision', p_shipment_request_info.document_revision);
103 WSH_DEBUG_SV.log(l_module_name,'action_type', p_shipment_request_info.action_type);
104 WSH_DEBUG_SV.log(l_module_name,'organization_code', p_shipment_request_info.organization_code);
105 WSH_DEBUG_SV.log(l_module_name,'customer_id', p_shipment_request_info.customer_id);
106 WSH_DEBUG_SV.log(l_module_name,'customer_name', p_shipment_request_info.customer_name);
107 WSH_DEBUG_SV.log(l_module_name,'ship_to_customer_id', p_shipment_request_info.ship_to_customer_id);
108 WSH_DEBUG_SV.log(l_module_name,'ship_to_customer_name', p_shipment_request_info.ship_to_customer_name);
109 WSH_DEBUG_SV.log(l_module_name,'ship_to_address_id', p_shipment_request_info.ship_to_address_id);
110 WSH_DEBUG_SV.log(l_module_name,'ship_to_address1', p_shipment_request_info.ship_to_address1);
111 WSH_DEBUG_SV.log(l_module_name,'ship_to_address2', p_shipment_request_info.ship_to_address2);
112 WSH_DEBUG_SV.log(l_module_name,'ship_to_address3', p_shipment_request_info.ship_to_address3);
113 WSH_DEBUG_SV.log(l_module_name,'ship_to_address4', p_shipment_request_info.ship_to_address4);
114 WSH_DEBUG_SV.log(l_module_name,'ship_to_city', p_shipment_request_info.ship_to_city);
115 WSH_DEBUG_SV.log(l_module_name,'ship_to_state', p_shipment_request_info.ship_to_state);
116 WSH_DEBUG_SV.log(l_module_name,'ship_to_country', p_shipment_request_info.ship_to_country);
117 WSH_DEBUG_SV.log(l_module_name,'ship_to_postal_code', p_shipment_request_info.ship_to_postal_code);
118 WSH_DEBUG_SV.log(l_module_name,'ship_to_contact_id', p_shipment_request_info.ship_to_contact_id);
119 WSH_DEBUG_SV.log(l_module_name,'ship_to_contact_name', p_shipment_request_info.ship_to_contact_name);
120 WSH_DEBUG_SV.log(l_module_name,'ship_to_contact_phone', p_shipment_request_info.ship_to_contact_phone);
121
122 WSH_DEBUG_SV.log(l_module_name,'invoice_to_customer_id', p_shipment_request_info.invoice_to_customer_id);
123 WSH_DEBUG_SV.log(l_module_name,'invoice_to_customer_name', p_shipment_request_info.invoice_to_customer_name);
124 WSH_DEBUG_SV.log(l_module_name,'invoice_to_address_id', p_shipment_request_info.invoice_to_address_id);
125 WSH_DEBUG_SV.log(l_module_name,'invoice_to_address1', p_shipment_request_info.invoice_to_address1);
126 WSH_DEBUG_SV.log(l_module_name,'invoice_to_address2', p_shipment_request_info.invoice_to_address2);
127 WSH_DEBUG_SV.log(l_module_name,'invoice_to_address3', p_shipment_request_info.invoice_to_address3);
128 WSH_DEBUG_SV.log(l_module_name,'invoice_to_address4', p_shipment_request_info.invoice_to_address4);
129 WSH_DEBUG_SV.log(l_module_name,'invoice_to_city', p_shipment_request_info.invoice_to_city);
130 WSH_DEBUG_SV.log(l_module_name,'invoice_to_state', p_shipment_request_info.invoice_to_state);
131 WSH_DEBUG_SV.log(l_module_name,'invoice_to_country', p_shipment_request_info.invoice_to_country);
132 WSH_DEBUG_SV.log(l_module_name,'invoice_to_postal_code', p_shipment_request_info.invoice_to_postal_code);
133 WSH_DEBUG_SV.log(l_module_name,'invoice_to_contact_id', p_shipment_request_info.invoice_to_contact_id);
134 WSH_DEBUG_SV.log(l_module_name,'invoice_to_contact_name', p_shipment_request_info.invoice_to_contact_name);
135 WSH_DEBUG_SV.log(l_module_name,'invoice_to_contact_phone', p_shipment_request_info.invoice_to_contact_phone);
136
137 WSH_DEBUG_SV.log(l_module_name,'deliver_to_customer_id', p_shipment_request_info.deliver_to_customer_id);
138 WSH_DEBUG_SV.log(l_module_name,'deliver_to_customer_name', p_shipment_request_info.deliver_to_customer_name);
139 WSH_DEBUG_SV.log(l_module_name,'deliver_to_address_id', p_shipment_request_info.deliver_to_address_id);
140 WSH_DEBUG_SV.log(l_module_name,'deliver_to_address1', p_shipment_request_info.deliver_to_address1);
141 WSH_DEBUG_SV.log(l_module_name,'deliver_to_address2', p_shipment_request_info.deliver_to_address2);
142 WSH_DEBUG_SV.log(l_module_name,'deliver_to_address3', p_shipment_request_info.deliver_to_address3);
143 WSH_DEBUG_SV.log(l_module_name,'deliver_to_address4', p_shipment_request_info.deliver_to_address4);
144 WSH_DEBUG_SV.log(l_module_name,'deliver_to_city', p_shipment_request_info.deliver_to_city);
145 WSH_DEBUG_SV.log(l_module_name,'deliver_to_state', p_shipment_request_info.deliver_to_state);
146 WSH_DEBUG_SV.log(l_module_name,'deliver_to_country', p_shipment_request_info.deliver_to_country);
147 WSH_DEBUG_SV.log(l_module_name,'deliver_to_postal_code', p_shipment_request_info.deliver_to_postal_code);
148 WSH_DEBUG_SV.log(l_module_name,'deliver_to_contact_id', p_shipment_request_info.deliver_to_contact_id);
149 WSH_DEBUG_SV.log(l_module_name,'deliver_to_contact_name', p_shipment_request_info.deliver_to_contact_name);
150 WSH_DEBUG_SV.log(l_module_name,'deliver_to_contact_phone', p_shipment_request_info.deliver_to_contact_phone);
151
152 WSH_DEBUG_SV.log(l_module_name,'carrier_code', p_shipment_request_info.carrier_code);
153 WSH_DEBUG_SV.log(l_module_name,'service_level', p_shipment_request_info.service_level);
154 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport', p_shipment_request_info.mode_of_transport);
155 WSH_DEBUG_SV.log(l_module_name,'freight_terms_code', p_shipment_request_info.freight_terms_code);
156 WSH_DEBUG_SV.log(l_module_name,'fob_code', p_shipment_request_info.fob_code);
157 WSH_DEBUG_SV.log(l_module_name,'currency_code', p_shipment_request_info.currency_code);
158 WSH_DEBUG_SV.log(l_module_name,'transaction_type_id', p_shipment_request_info.transaction_type_id);
159 WSH_DEBUG_SV.log(l_module_name,'price_list_id', p_shipment_request_info.price_list_id);
160 WSH_DEBUG_SV.log(l_module_name,'client_code', p_shipment_request_info.client_code); -- LSP PROJECT
161
162 IF p_shipment_request_info.shipment_details_tab.count>0 THEN
163 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
164 WSH_DEBUG_SV.logmsg(l_module_name, '===============================================');
165 WSH_DEBUG_SV.logmsg(l_module_name, '| WDDI RECORD DETAILS - ' || i || ' |');
166 WSH_DEBUG_SV.logmsg(l_module_name, '===============================================');
167 WSH_DEBUG_SV.log(l_module_name,'line_number', p_shipment_request_info.shipment_details_tab(i).line_number);
168 WSH_DEBUG_SV.log(l_module_name,'item_number', p_shipment_request_info.shipment_details_tab(i).item_number);
169 WSH_DEBUG_SV.log(l_module_name,'inventory_item_id', p_shipment_request_info.shipment_details_tab(i).inventory_item_id);
170 WSH_DEBUG_SV.log(l_module_name,'item_description', p_shipment_request_info.shipment_details_tab(i).item_description);
171 WSH_DEBUG_SV.log(l_module_name,'requested_quantity', p_shipment_request_info.shipment_details_tab(i).requested_quantity);
172 WSH_DEBUG_SV.log(l_module_name,'requested_quantity_uom', p_shipment_request_info.shipment_details_tab(i).requested_quantity_uom);
173 WSH_DEBUG_SV.log(l_module_name,'customer_item_id', p_shipment_request_info.shipment_details_tab(i).customer_item_id );
174 WSH_DEBUG_SV.log(l_module_name,'customer_item_number', p_shipment_request_info.shipment_details_tab(i).customer_item_number);
175 WSH_DEBUG_SV.log(l_module_name,'date_requested', p_shipment_request_info.shipment_details_tab(i).date_requested);
176 WSH_DEBUG_SV.log(l_module_name,'date_scheduled', p_shipment_request_info.shipment_details_tab(i).date_scheduled);
177 WSH_DEBUG_SV.log(l_module_name,'ship_tolerance_above', p_shipment_request_info.shipment_details_tab(i).ship_tolerance_above);
178 WSH_DEBUG_SV.log(l_module_name,'ship_tolerance_below', p_shipment_request_info.shipment_details_tab(i).ship_tolerance_below);
179 WSH_DEBUG_SV.log(l_module_name,'packing_instructions', p_shipment_request_info.shipment_details_tab(i).packing_instructions);
180 WSH_DEBUG_SV.log(l_module_name,'shipping_instructions', p_shipment_request_info.shipment_details_tab(i).shipping_instructions);
181 WSH_DEBUG_SV.log(l_module_name,'shipment_priority_code', p_shipment_request_info.shipment_details_tab(i).shipment_priority_code);
182 WSH_DEBUG_SV.log(l_module_name,'ship_set_name', p_shipment_request_info.shipment_details_tab(i).ship_set_name);
183 WSH_DEBUG_SV.log(l_module_name,'subinventory', p_shipment_request_info.shipment_details_tab(i).subinventory );
184 WSH_DEBUG_SV.log(l_module_name,'revision', p_shipment_request_info.shipment_details_tab(i).revision );
185 WSH_DEBUG_SV.log(l_module_name,'locator_code', p_shipment_request_info.shipment_details_tab(i).locator_code );
186 WSH_DEBUG_SV.log(l_module_name,'locator_id', p_shipment_request_info.shipment_details_tab(i).locator_id );
187 WSH_DEBUG_SV.log(l_module_name,'lot_number', p_shipment_request_info.shipment_details_tab(i).lot_number );
188 WSH_DEBUG_SV.log(l_module_name,'unit_selling_price', p_shipment_request_info.shipment_details_tab(i).unit_selling_price );
189 WSH_DEBUG_SV.log(l_module_name,'currency_code', p_shipment_request_info.shipment_details_tab(i).currency_code );
190 WSH_DEBUG_SV.log(l_module_name,'earliest_pickup_date', p_shipment_request_info.shipment_details_tab(i).earliest_pickup_date);
191 WSH_DEBUG_SV.log(l_module_name,'latest_pickup_date', p_shipment_request_info.shipment_details_tab(i).latest_pickup_date );
192 WSH_DEBUG_SV.log(l_module_name,'earliest_dropoff_date', p_shipment_request_info.shipment_details_tab(i).earliest_dropoff_date);
193 WSH_DEBUG_SV.log(l_module_name,'latest_dropoff_date', p_shipment_request_info.shipment_details_tab(i).latest_dropoff_date);
194 WSH_DEBUG_SV.log(l_module_name,'cust_po_number', p_shipment_request_info.shipment_details_tab(i).cust_po_number);
195 WSH_DEBUG_SV.log(l_module_name,'source_header_number', p_shipment_request_info.shipment_details_tab(i).source_header_number);
196 WSH_DEBUG_SV.log(l_module_name,'src_requested_quantity', p_shipment_request_info.shipment_details_tab(i).src_requested_quantity);
197 WSH_DEBUG_SV.log(l_module_name,'src_requested_quantity_uom', p_shipment_request_info.shipment_details_tab(i).src_requested_quantity_uom);
198 WSH_DEBUG_SV.log(l_module_name,'source_line_number', p_shipment_request_info.shipment_details_tab(i).source_line_number );
199 END LOOP;
200 END IF;
201
202 END IF;
203 --
204 l_invalid_inputs := 0;
205 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
206 IF p_shipment_request_info.Document_Number IS NULL OR p_shipment_request_info.Document_REVISION is NULL THEN
207 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
208 fnd_message.set_name('WSH', 'WSH_STND_ATTR_MANDATORY');
209 fnd_message.set_token('ATTRIBUTE','DOCUMENT_NUMBER and DOCUMENT_REVISION');
210 wsh_util_core.add_message(x_return_status);
211
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.log(l_module_name, 'Mandatory input parameters have not been passed');
214 END IF;
215 END IF;
216
217 IF p_shipment_request_info.Document_Number <= 0 OR
218 p_shipment_request_info.Document_Number <> trunc(p_shipment_request_info.Document_Number) THEN
219 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
220 fnd_message.set_name('WSH', 'WSH_STND_POSITIVE_INTEGER');
221 fnd_message.set_token('ATTRIBUTE', 'DOCUMENT_NUMBER');
222 wsh_util_core.add_message(x_return_status);
223
224 l_invalid_inputs := 1;
225 END IF;
226 IF p_shipment_request_info.Document_revision <= 0 OR
227 p_shipment_request_info.Document_revision <> trunc(p_shipment_request_info.Document_revision ) THEN
228 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
229 fnd_message.set_name('WSH', 'WSH_STND_POSITIVE_INTEGER');
230 fnd_message.set_token('ATTRIBUTE', 'DOCUMENT_REVISION');
231 wsh_util_core.add_message(x_return_status);
232
233 l_invalid_inputs := 1;
234 END IF;
235
236 IF p_shipment_request_info.ACTION_TYPE NOT IN ('A','C','D') THEN
237 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
238 fnd_message.set_name('WSH', 'WSH_OI_INVALID_ATTRIBUTE');
239 fnd_message.set_token('ATTRIBUTE', 'ACTION_TYPE');
240 wsh_util_core.add_message(x_return_status);
241 IF l_debug_on THEN
242 WSH_DEBUG_SV.log(l_module_name,'Action Type should be A or C or D. Action type',p_shipment_request_info.ACTION_TYPE);
243 END IF;
244 l_invalid_inputs := 1;
245 END IF;
246
247 IF l_invalid_inputs = 1 THEN
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250
251 IF p_action_code = 'CREATE' THEN
252 --
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Shipment_Request with SHIPMENT_REQUEST', WSH_DEBUG_SV.C_PROC_LEVEL);
255 END IF;
256 --
257 Create_Shipment_Request(p_shipment_request_info => p_shipment_request_info,
258 p_caller => 'SHIPMENT_REQUEST',
259 x_return_status => l_return_status);
260
261 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.logmsg(l_module_name, 'The Action '||p_action_code||' failed');
264 END IF;
265 x_return_status := l_return_status;
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268
269
270 ELSIF p_action_code = 'QUERY' THEN
271 --
272 IF l_debug_on THEN
273 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Query_Shipment_Request', WSH_DEBUG_SV.C_PROC_LEVEL);
274 END IF;
275 --
276 Query_Shipment_Request(p_shipment_request_info => p_shipment_request_info,
277 x_interface_errors_info => x_interface_errors_info ,
278 x_return_status => l_return_status);
279 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
280 IF l_debug_on THEN
281 WSH_DEBUG_SV.logmsg(l_module_name, 'The Action '||p_action_code||' failed');
282 END IF;
283 x_return_status := l_return_status;
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286
287 ELSIF p_action_code = 'UPDATE' OR p_action_code ='DELETE' THEN
288 --
289 IF l_debug_on THEN
290 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Update_Delete_Shipment_Request', WSH_DEBUG_SV.C_PROC_LEVEL);
291 END IF;
292 --
293 Update_Delete_Shipment_Request(
294 p_action_code => p_action_code,
295 p_shipment_request_info => p_shipment_request_info,
296 x_return_status => l_return_status);
297 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
298 IF l_debug_on THEN
299 WSH_DEBUG_SV.logmsg(l_module_name, 'The Action '||p_action_code||' failed');
300 END IF;
301 x_return_status := l_return_status;
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304 ELSE
305 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
306 fnd_message.set_name('WSH', 'WSH_PUB_INVALID_ACTION');
307 fnd_message.set_token('ACTION_CODE', p_action_code);
308 wsh_util_core.add_message(x_return_status);
309 IF l_debug_on THEN
310 WSH_DEBUG_SV.log(l_module_name, 'p_action_code should be CREATE/QUERY/UPDATE/DELETE.The current value is',p_action_code);
311 END IF;
312 RAISE FND_API.G_EXC_ERROR;
313 END IF;
314
315 IF p_commit = FND_API.G_TRUE THEN
316 COMMIT;
317 END IF;
318
319 --
320 IF l_debug_on THEN
321 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
322 WSH_DEBUG_SV.pop(l_module_name);
323 END IF;
324 --
325 EXCEPTION
326 WHEN FND_API.G_EXC_ERROR THEN
327 p_shipment_request_info.delivery_interface_id := NULL;
328 p_shipment_request_info.transaction_id := null;
329 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
330 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
331 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
332 END LOOP;
333 END IF;
334 fnd_msg_pub.count_and_get(p_count => x_msg_count,
335 p_data => x_msg_data,
336 p_encoded => fnd_api.g_false);
337 IF l_debug_on THEN
338 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
339 END IF;
340 rollback;
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.log(l_module_name, 'Error performing the action '''|| p_action_code ||''' on Document_Number',p_shipment_request_info.Document_Number);
343 WSH_DEBUG_SV.pop(l_module_name,'FND_API.G_EXC_ERROR');
344 END IF;
345 WHEN others THEN
346 p_shipment_request_info.delivery_interface_id := NULL;
347 p_shipment_request_info.transaction_id := null;
348 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
349 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
350 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
351 END LOOP;
352 END IF;
353 IF l_debug_on THEN
354 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
355 END IF;
356 rollback;
357 fnd_msg_pub.count_and_get(p_count => x_msg_count,
358 p_data => x_msg_data,
359 p_encoded => fnd_api.g_false);
360 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
361 IF l_debug_on THEN
362 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
363 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
364 END IF;
365 END Shipment_Request;
366
367 --========================================================================
368 -- PROCEDURE : Process_Shipment_Requests PUBLIC
369 --
370 -- PARAMETERS: p_api_version_number known api version
371 -- p_init_msg_list FND_API.G_TRUE to reset list
372 -- p_process_mode 'ONLINE' or 'CONCURRENT'
373 -- p_criteria_info Attributes for the Process Shipment Request criteria
374 -- p_log_level 0 or 1 to control the log messages
375 -- x_request_id Concurrent request id when p_process_mode is 'CONCURRENT'
376 -- x_return_status return status
377 -- x_msg_count number of messages in the list
378 -- x_msg_data text of messages
379 -- VERSION : current version 1.0
380 -- initial version 1.0
381 -- COMMENT : Processes Shipment Requests as per criteria
382 -- specified in p_criteria_info
383 --========================================================================
384
385 PROCEDURE Process_Shipment_Requests(
386 p_api_version_number IN NUMBER,
387 p_init_msg_list IN VARCHAR2 ,
388 p_process_mode IN VARCHAR2 ,
389 p_criteria_info IN Criteria_Rec_Type,
390 p_log_level IN NUMBER ,
391 p_commit IN VARCHAR2 ,
392 x_request_id OUT NOCOPY NUMBER,
393 x_return_status OUT NOCOPY VARCHAR2,
394 x_msg_count OUT NOCOPY NUMBER,
395 x_msg_data OUT NOCOPY VARCHAR2) AS
396
397 l_return_status VARCHAR2(100);
398 --
399 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
400 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Shipment_Requests';
401 --
402 CURSOR c_get_date(p_date1 DATE,p_date2 DATE) IS
403 SELECT to_char(p_date1,'YYYY/MM/DD HH24:MI:SS'),to_char(p_date2,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
404
405 l_from_date VARCHAR2(100);
406 l_to_date VARCHAR2(100);
407 --
408 l_api_version_number CONSTANT NUMBER := 1.0;
409 l_api_name CONSTANT VARCHAR2(30):= 'Process_Shipment_Requests';
410 --
411
412
413 BEGIN
414 --
415 IF l_debug_on THEN
416 wsh_debug_sv.push(l_module_name);
417 END IF;
418 -- Standard call to check for call compatibility
419 IF NOT FND_API.Compatible_API_Call
420 ( l_api_version_number
421 , p_api_version_number
422 , l_api_name
423 , G_PKG_NAME
424 )
425 THEN
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END IF;
428
429 -- Initialize message stack if required
430 IF FND_API.to_Boolean(p_init_msg_list) THEN
431 FND_MSG_PUB.initialize;
432 END IF;
433
434 IF l_debug_on THEN
435 --
436 WSH_DEBUG_SV.log(l_module_name,'p_process_mode',p_process_mode);
437 WSH_DEBUG_SV.log(l_module_name,'p_log_level',p_log_level);
438 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
439 --
440 WSH_DEBUG_SV.log(l_module_name,'p_process_mode',p_process_mode);
441 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.shipment_request_status',p_criteria_info.shipment_request_status);
442 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.from_shipment_request_number',p_criteria_info.from_shipment_request_number);
443 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.to_shipment_request_number',p_criteria_info.to_shipment_request_number);
444 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.from_shipment_request_date',p_criteria_info.from_shipment_request_date);
445 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.to_shipment_request_date',p_criteria_info.to_shipment_request_date);
446 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.transaction_id',p_criteria_info.transaction_id);
447 WSH_DEBUG_SV.log(l_module_name,'p_criteria_info.client_code',p_criteria_info.client_code); -- LSP PROJECT
448 END IF;
449 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
450
451 IF p_process_mode = 'ONLINE' THEN
452 OPEN c_get_date(p_criteria_info.from_shipment_request_date,p_criteria_info.from_shipment_request_date);
453 FETCH c_get_date into l_from_date,l_to_date;
454 CLOSE c_get_date;
455
456 --
457 IF l_debug_on THEN
458 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request', WSH_DEBUG_SV.C_PROC_LEVEL);
459 END IF;
460 --
461 -- Passing p_commit as TRUE, irrespective of the value passed to PUBLIC API
462 -- Issue if p_commit value passed FALSE:
463 -- If Process_Shipment_Request fails then it deletes the existing error messages from WIE table(non-autonomous transaction)
464 -- and inserts new error messages into WIE table(Autonomous transaction).
465 -- If rollback is performed in public API then there will be duplicate/extra error messages in WIE table.
466 WSH_SHIPMENT_REQUEST_PKG.Process_Shipment_Request(
467 p_commit_flag => FND_API.G_TRUE,
468 p_transaction_status => p_criteria_info.shipment_request_status,
469 p_client_code => p_criteria_info.client_code, -- LSP PROJECT
470 p_from_document_number => p_criteria_info.from_shipment_request_number,
471 p_to_document_number => p_criteria_info.to_shipment_request_number,
472 p_from_creation_date => l_from_date,
473 p_to_creation_date => l_to_date,
474 p_transaction_id => p_criteria_info.transaction_id,
475 x_return_status => l_return_status );
476
477
478 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
479 FND_MESSAGE.Set_Name('WSH', 'WSH_SUCCESS_PROCESS');
480 WSH_UTIL_CORE.Add_Message(l_return_status);
481 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
482 x_return_status := l_return_status;
483 fnd_msg_pub.count_and_get( p_encoded => 'F'
484 ,p_count => x_msg_count
485 ,p_data => x_msg_data);
486 ELSE
487 --
488 IF l_debug_on THEN
489 WSH_DEBUG_SV.logmsg(l_module_name, 'Process_Shipment_Request completed with error');
490 END IF;
491 --
492 x_return_status := l_return_status;
493 FND_MESSAGE.Set_Name('WSH', 'WSH_STND_SR_PROCESS_ERROR');
494 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
495 raise FND_API.G_EXC_ERROR;
496 END IF;
497
498
499 ELSIF p_process_mode = 'CONCURRENT' THEN
500 --
501 IF l_debug_on THEN
502 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling FND_REQUEST.SUBMIT_REQUEST', WSH_DEBUG_SV.C_PROC_LEVEL);
503 END IF;
504 --
505 x_request_id := FND_REQUEST.SUBMIT_REQUEST(
506 application => 'WSH',
507 program => 'WSHSRINB',
508 description => 'Shipment Request Inbound Interface',
509 start_time => NULL,
510 sub_request => FALSE,
511 argument1 => p_criteria_info.shipment_request_status,
512 argument2 => p_criteria_info.from_shipment_request_number,
513 argument3 => p_criteria_info.to_shipment_request_number,
514 argument4 => l_from_date,
515 argument5 => l_to_date,
516 argument6 => p_criteria_info.transaction_id,
517 argument7 => p_log_level );
518
519 --
520 IF l_debug_on THEN
521 WSH_DEBUG_SV.log(l_module_name, 'Request Id returned from FND_REQUEST.SUBMIT_REQUEST', x_request_id);
522 END IF;
523 --
524 IF (nvl(x_request_id,0) <= 0) THEN
525 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
526 raise FND_API.G_EXC_ERROR;
527 ELSE
528 FND_MESSAGE.Set_Name('WSH', 'WSH_REQUEST_SUBMITTED');
529 FND_MESSAGE.Set_Token('REQUEST_ID', x_request_id);
530 WSH_UTIL_CORE.Add_Message(x_return_status);
531 END IF;
532 ELSE
533 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
534 fnd_message.set_name('WSH', 'WSH_OI_INVALID_ATTRIBUTE');
535 fnd_message.set_token('ATTRIBUTE', 'PROCESS_MODE');
536 wsh_util_core.add_message(x_return_status);
537 IF l_debug_on THEN
538 WSH_DEBUG_SV.logmsg(l_module_name, 'p_process_mode should be ONLINE/CONCURRENT');
539 END IF;
540 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
541 raise FND_API.G_EXC_ERROR;
542 END IF;
543
544 IF p_commit = FND_API.G_TRUE THEN
545 COMMIT;
546 END IF;
547 fnd_msg_pub.count_and_get(p_count => x_msg_count,
548 p_data => x_msg_data,
549 p_encoded => fnd_api.g_false);
550 IF l_debug_on THEN
551 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
552 WSH_DEBUG_SV.pop(l_module_name);
553 END IF;
554
555 EXCEPTION
556 WHEN FND_API.G_EXC_ERROR THEN
557 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
558 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
559 END IF;
560 IF c_get_date%ISOPEN THEN
561 CLOSE c_get_date;
562 END IF;
563 fnd_msg_pub.count_and_get( p_encoded => 'F'
564 ,p_count => x_msg_count
565 ,p_data => x_msg_data);
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
568 END IF;
569 rollback;
570 IF l_debug_on THEN
571 WSH_DEBUG_SV.log(l_module_name, 'Error occured while processing Shipment Request',p_criteria_info.transaction_id);
572 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
573 END IF;
574 WHEN others THEN
575 IF c_get_date%ISOPEN THEN
576 CLOSE c_get_date;
577 END IF;
578 IF l_debug_on THEN
579 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
580 END IF;
581 rollback;
582
583 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
584 IF l_debug_on THEN
585 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
586 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
587 END IF;
588 END Process_Shipment_Requests;
589 --========================================================================
590 -- PROCEDURE : Create_Shipment_Request PRIVATE
591 --
592 -- PARAMETERS:
593 -- p_shipment_request_info Attributes for the create shipment request
594 -- p_caller Either SHIPMENT_REQUEST/UPDATE
595 -- x_return_status return status
596 --
597 -- COMMENT : If p_caller is SHIPMENT_REQUEST then records are inserted into
598 -- WNDI, WDDI, WDAI and WTH tables. If p_caller is UPDATE then
599 -- records are inserted only in WDDI and WDAI tables
600 --========================================================================
601 PROCEDURE Create_Shipment_Request(
602 p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
603 p_caller IN VARCHAR2,
604 x_return_status OUT NOCOPY VARCHAR2) AS
605
606 l_ins_rows NUMBER;
607 l_exists NUMBER;
608 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
609 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Shipment_Request';
610
611 l_del_det_int_tab wsh_util_core.id_tab_type;
612 --Bug8784331
613 l_date_requested wsh_util_core.Date_Tab_Type;
614 l_date_scheduled wsh_util_core.Date_Tab_Type;
615 l_inventory_item_id wsh_util_core.Id_Tab_Type;
616 l_item_number wsh_util_core.tbl_varchar;
617 l_customer_item_id wsh_util_core.Id_Tab_Type;
618 l_customer_item_number wsh_util_core.tbl_varchar;
619 l_requested_quantity wsh_util_core.Id_Tab_Type;
620 l_requested_quantity_uom wsh_util_core.tbl_varchar;
621 l_src_requested_quantity wsh_util_core.Id_Tab_Type;
622 l_src_requested_quantity_uom wsh_util_core.tbl_varchar;
623 l_line_number wsh_util_core.Id_Tab_Type;
624 l_source_line_number wsh_util_core.tbl_varchar;
625 l_source_header_number wsh_util_core.tbl_varchar;
626 l_earliest_pickup_date wsh_util_core.Date_Tab_Type;
627 l_latest_pickup_date wsh_util_core.Date_Tab_Type;
628 l_earliest_dropoff_date wsh_util_core.Date_Tab_Type;
629 l_latest_dropoff_date wsh_util_core.Date_Tab_Type;
630 l_ship_tolerance_above wsh_util_core.Id_Tab_Type;
631 l_ship_tolerance_below wsh_util_core.Id_Tab_Type;
632 l_shipping_instructions wsh_util_core.tbl_varchar;
633 l_packing_instructions wsh_util_core.tbl_varchar;
634 l_shipment_priority_code wsh_util_core.tbl_varchar;
635 l_cust_po_number wsh_util_core.tbl_varchar;
636 l_subinventory wsh_util_core.tbl_varchar;
637 l_locator_id wsh_util_core.Id_Tab_Type;
638 l_locator_code wsh_util_core.tbl_varchar;
639 l_lot_number wsh_util_core.tbl_varchar;
640 l_revision wsh_util_core.tbl_varchar;
641 l_ship_set_name wsh_util_core.tbl_varchar;
642 l_unit_selling_price wsh_util_core.Id_Tab_Type;
643 --Bug8784331
644 BEGIN
645
646 --
647 IF l_debug_on THEN
648 WSH_DEBUG_SV.push(l_module_name);
649 WSH_DEBUG_SV.log(l_module_name,'Caller',p_caller);
650 END IF;
651 --
652 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
653
654 if p_caller ='SHIPMENT_REQUEST' THEN
655 --{
656 select count(*) into l_exists
657 FROM wsh_transactions_history
658 WHERE document_number = to_char(p_shipment_request_info.document_number)
659 and document_revision = p_shipment_request_info.document_revision
660 AND document_type = 'SR'
661 AND document_direction = 'I';
662
663 if l_exists >0 THEN
664 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
665 fnd_message.set_name('WSH', 'WSH_STND_DUP_DOCUMENT');
666 fnd_message.set_token('DOCUMENT_NUMBER', p_shipment_request_info.document_number);
667 fnd_message.set_token('DOCUMENT_REVISION', p_shipment_request_info.document_revision);
668 wsh_util_core.add_message(x_return_status);
669 IF l_debug_on THEN
670 wsh_debug_sv.logmsg(l_module_name, 'The document already exists');
671 END IF;
672 RAISE FND_API.G_EXC_ERROR;
673 END IF;
674
675
676 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
677 IF l_debug_on THEN
678 wsh_debug_sv.logmsg(l_module_name, 'Inserting into WSH_NEW_DEL_INTERFACE');
679 END IF;
680
681 INSERT INTO WSH_NEW_DEL_INTERFACE(
682 DELIVERY_INTERFACE_ID,
683 PLANNED_FLAG,
684 STATUS_CODE,
685 CREATION_DATE,
686 CREATED_BY,
687 LAST_UPDATE_DATE,
688 LAST_UPDATED_BY,
689 last_update_login,
690 program_application_id,
691 program_id,
692 program_update_date,
693 request_id,
694 DELIVERY_TYPE,
695 ORGANIZATION_CODE,
696 CARRIER_CODE,
697 SERVICE_LEVEL,
698 MODE_OF_TRANSPORT,
699 CUSTOMER_ID,
700 CUSTOMER_NAME,
701 SHIP_TO_CUSTOMER_ID,
702 SHIP_TO_CUSTOMER_NAME,
703 SHIP_TO_ADDRESS_ID,
704 SHIP_TO_ADDRESS1,
705 SHIP_TO_ADDRESS2,
706 SHIP_TO_ADDRESS3,
707 SHIP_TO_ADDRESS4,
708 SHIP_TO_CITY,
709 SHIP_TO_STATE,
710 SHIP_TO_COUNTRY,
711 SHIP_TO_POSTAL_CODE,
712 SHIP_TO_CONTACT_ID,
713 SHIP_TO_CONTACT_NAME,
714 SHIP_TO_CONTACT_PHONE,
715 INVOICE_TO_CUSTOMER_ID,
716 INVOICE_TO_CUSTOMER_NAME,
717 INVOICE_TO_ADDRESS_ID,
718 INVOICE_TO_ADDRESS1,
719 INVOICE_TO_ADDRESS2,
720 INVOICE_TO_ADDRESS3,
721 INVOICE_TO_ADDRESS4,
722 INVOICE_TO_CITY,
723 INVOICE_TO_STATE,
724 INVOICE_TO_COUNTRY,
725 INVOICE_TO_POSTAL_CODE,
726 INVOICE_TO_CONTACT_ID,
727 INVOICE_TO_CONTACT_NAME,
728 INVOICE_TO_CONTACT_PHONE,
729 DELIVER_TO_CUSTOMER_ID,
730 DELIVER_TO_CUSTOMER_NAME,
731 DELIVER_TO_ADDRESS_ID,
732 DELIVER_TO_ADDRESS1,
733 DELIVER_TO_ADDRESS2,
734 DELIVER_TO_ADDRESS3,
735 DELIVER_TO_ADDRESS4,
736 DELIVER_TO_CITY,
737 DELIVER_TO_STATE,
738 DELIVER_TO_COUNTRY,
739 DELIVER_TO_POSTAL_CODE,
740 DELIVER_TO_CONTACT_ID,
741 DELIVER_TO_CONTACT_NAME,
742 DELIVER_TO_CONTACT_PHONE,
743 FREIGHT_TERMS_CODE,
744 FOB_CODE,
745 TRANSACTION_TYPE_ID,
746 PRICE_LIST_ID,
747 CURRENCY_CODE,
748 CLIENT_CODE, -- LSP PROJECT
749 INTERFACE_ACTION_CODE)
750 Values(
751 wsh_new_del_interface_s.nextval,
752 'N',
753 'OP',
754 sysdate,
755 FND_GLOBAL.USER_ID,
756 sysdate,
757 FND_GLOBAL.USER_ID,
758 FND_GLOBAL.LOGIN_ID,
759 FND_GLOBAL.PROG_APPL_ID,
760 FND_GLOBAL.Conc_Program_Id,
761 sysdate,
762 FND_GLOBAL.Conc_Request_Id,
763 'I',
764 p_shipment_request_info.organization_code,
765 p_shipment_request_info.CARRIER_CODE,
766 p_shipment_request_info.SERVICE_LEVEL,
767 p_shipment_request_info.MODE_OF_TRANSPORT,
768 p_shipment_request_info.customer_id,
769 p_shipment_request_info.customer_name,
770 p_shipment_request_info.ship_to_customer_id,
771 p_shipment_request_info.ship_to_customer_name,
772 p_shipment_request_info.ship_to_address_id,
773 p_shipment_request_info.ship_to_address1,
774 p_shipment_request_info.ship_to_address2,
775 p_shipment_request_info.ship_to_address3,
776 p_shipment_request_info.ship_to_address4,
777 p_shipment_request_info.ship_to_city,
778 p_shipment_request_info.ship_to_state,
779 p_shipment_request_info.ship_to_country,
780 p_shipment_request_info.ship_to_postal_code,
781 p_shipment_request_info.ship_to_contact_id ,
782 p_shipment_request_info.ship_to_contact_name,
783 p_shipment_request_info.ship_to_contact_phone,
784 p_shipment_request_info.invoice_to_customer_id,
785 p_shipment_request_info.invoice_to_customer_name,
786 p_shipment_request_info.invoice_to_address_id,
787 p_shipment_request_info.invoice_to_address1,
788 p_shipment_request_info.invoice_to_address2,
789 p_shipment_request_info.invoice_to_address3,
790 p_shipment_request_info.invoice_to_address4,
791 p_shipment_request_info.invoice_to_city,
792 p_shipment_request_info.invoice_to_state,
793 p_shipment_request_info.invoice_to_country,
794 p_shipment_request_info.invoice_to_postal_code,
795 p_shipment_request_info.invoice_to_contact_id,
796 p_shipment_request_info.invoice_to_contact_name,
797 p_shipment_request_info.invoice_to_contact_phone,
798 p_shipment_request_info.deliver_to_customer_id,
799 p_shipment_request_info.deliver_to_customer_name,
800 p_shipment_request_info.deliver_to_address_id,
801 p_shipment_request_info.deliver_to_address1,
802 p_shipment_request_info.deliver_to_address2,
803 p_shipment_request_info.deliver_to_address3,
804 p_shipment_request_info.deliver_to_address4,
805 p_shipment_request_info.deliver_to_city,
806 p_shipment_request_info.deliver_to_state,
807 p_shipment_request_info.deliver_to_country,
808 p_shipment_request_info.deliver_to_postal_code,
809 p_shipment_request_info.deliver_to_contact_id,
810 p_shipment_request_info.deliver_to_contact_name,
811 p_shipment_request_info.deliver_to_contact_phone,
812 p_shipment_request_info.freight_terms_code,
813 p_shipment_request_info.fob_code,
814 p_shipment_request_info.transaction_type_id,
815 p_shipment_request_info.price_list_id,
816 p_shipment_request_info.currency_code,
817 p_shipment_request_info.client_code, -- LSP PROJECT
818 '94X_STANDALONE'
819 )
820 RETURNING DELIVERY_INTERFACE_ID INTO p_shipment_request_info.delivery_interface_id;
821 l_ins_rows := sql%rowcount;
822 IF l_debug_on THEN
823 WSH_DEBUG_SV.log(l_module_name, 'Number of records inserted into WSH_NEW_DEL_INTERFACE',l_ins_rows);
824 WSH_DEBUG_SV.log(l_module_name, 'Number of shipment details',p_shipment_request_info.shipment_details_tab.count);
825 END IF;
826 --}
827 END IF;
828
829
830 IF p_shipment_request_info.shipment_details_tab.count > 0 AND
831 p_shipment_request_info.shipment_details_tab.count < 4 THEN
832 --{
833 IF l_debug_on THEN
834 wsh_debug_sv.logmsg(l_module_name, 'Inserting into WSH_DEL_DETAILS_INTERFACE and WSH_DEL_ASSGN_INTERFACE');
835 END IF;
836
837 FOR i IN 1..p_shipment_request_info.shipment_details_tab.count LOOP
838 --{
839 INSERT INTO WSH_DEL_DETAILS_INTERFACE(
840 DELIVERY_DETAIL_INTERFACE_ID,
841 SOURCE_CODE,
842 SOURCE_LINE_ID,
843 CREATION_DATE,
844 CREATED_BY,
845 LAST_UPDATE_DATE,
846 LAST_UPDATED_BY,
847 LAST_UPDATE_LOGIN,
848 PROGRAM_APPLICATION_ID,
849 PROGRAM_ID,
850 PROGRAM_UPDATE_DATE,
851 REQUEST_ID,
852 CONTAINER_FLAG,
853 DATE_REQUESTED,
854 DATE_SCHEDULED,
855 INVENTORY_ITEM_ID,
856 ITEM_NUMBER,
857 CUSTOMER_ITEM_ID,
858 CUSTOMER_ITEM_NUMBER,
859 REQUESTED_QUANTITY,
860 REQUESTED_QUANTITY_UOM,
861 SRC_REQUESTED_QUANTITY,
862 SRC_REQUESTED_QUANTITY_UOM,
863 LINE_NUMBER,
864 SOURCE_LINE_NUMBER,
865 SOURCE_HEADER_NUMBER,
866 EARLIEST_PICKUP_DATE,
867 LATEST_PICKUP_DATE,
868 EARLIEST_DROPOFF_DATE,
869 LATEST_DROPOFF_DATE,
870 SHIP_TOLERANCE_ABOVE,
871 SHIP_TOLERANCE_BELOW,
872 SHIPPING_INSTRUCTIONS,
873 PACKING_INSTRUCTIONS,
874 SHIPMENT_PRIORITY_CODE,
875 CUST_PO_NUMBER,
876 SUBINVENTORY,
877 LOCATOR_ID,
878 LOCATOR_CODE,
879 LOT_NUMBER,
880 REVISION,
881 SHIP_SET_NAME,
882 UNIT_SELLING_PRICE,
883 LINE_DIRECTION,
884 INTERFACE_ACTION_CODE)
885 VALUES(
886 wsh_del_details_interface_s.nextval,
887 'OE',
888 -1,
889 sysdate,
890 FND_GLOBAL.USER_ID,
891 sysdate,
892 FND_GLOBAL.USER_ID,
893 FND_GLOBAL.LOGIN_ID,
894 FND_GLOBAL.PROG_APPL_ID,
895 FND_GLOBAL.Conc_Program_Id,
896 sysdate,
897 FND_GLOBAL.Conc_Request_Id,
898 'N',
899 p_shipment_request_info.shipment_details_tab(i).date_requested,
900 p_shipment_request_info.shipment_details_tab(i).date_scheduled,
901 p_shipment_request_info.shipment_details_tab(i).inventory_item_id,
902 p_shipment_request_info.shipment_details_tab(i).item_number,
903 p_shipment_request_info.shipment_details_tab(i).customer_item_id,
904 p_shipment_request_info.shipment_details_tab(i).customer_item_number,
905 p_shipment_request_info.shipment_details_tab(i).requested_quantity,
906 p_shipment_request_info.shipment_details_tab(i).requested_quantity_uom,
907 p_shipment_request_info.shipment_details_tab(i).src_requested_quantity,
908 p_shipment_request_info.shipment_details_tab(i).src_requested_quantity_uom,
909 p_shipment_request_info.shipment_details_tab(i).line_number,
910 p_shipment_request_info.shipment_details_tab(i).source_line_number,
911 p_shipment_request_info.shipment_details_tab(i).source_header_number,
912 p_shipment_request_info.shipment_details_tab(i).earliest_pickup_date,
913 p_shipment_request_info.shipment_details_tab(i).latest_pickup_date,
914 p_shipment_request_info.shipment_details_tab(i).earliest_dropoff_date,
915 p_shipment_request_info.shipment_details_tab(i).latest_dropoff_date,
916 p_shipment_request_info.shipment_details_tab(i).ship_tolerance_above,
917 p_shipment_request_info.shipment_details_tab(i).ship_tolerance_below,
918 p_shipment_request_info.shipment_details_tab(i).shipping_instructions,
919 p_shipment_request_info.shipment_details_tab(i).packing_instructions,
920 p_shipment_request_info.shipment_details_tab(i).shipment_priority_code,
921 p_shipment_request_info.shipment_details_tab(i).cust_po_number,
922 p_shipment_request_info.shipment_details_tab(i).subinventory,
923 p_shipment_request_info.shipment_details_tab(i).locator_id,
924 p_shipment_request_info.shipment_details_tab(i).locator_code,
925 p_shipment_request_info.shipment_details_tab(i).lot_number,
926 p_shipment_request_info.shipment_details_tab(i).revision,
927 p_shipment_request_info.shipment_details_tab(i).ship_set_name,
928 p_shipment_request_info.shipment_details_tab(i).unit_selling_price,
929 'O',
930 '94X_STANDALONE')
931 RETURNING DELIVERY_DETAIL_INTERFACE_ID INTO p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id;
932
933 INSERT INTO WSH_DEL_ASSGN_INTERFACE(
934 DEL_ASSGN_INTERFACE_ID,
935 DELIVERY_DETAIL_INTERFACE_ID,
936 CREATION_DATE,
937 CREATED_BY,
938 LAST_UPDATE_DATE,
939 LAST_UPDATED_BY,
940 LAST_UPDATE_LOGIN,
941 PROGRAM_APPLICATION_ID,
942 PROGRAM_ID,
943 PROGRAM_UPDATE_DATE,
944 REQUEST_ID,
945 DELIVERY_INTERFACE_ID,
946 INTERFACE_ACTION_CODE
947 )
948 VALUES(
949 wsh_del_assgn_interface_s.nextval,
950 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id,
951 sysdate,
952 FND_GLOBAL.USER_ID,
953 sysdate,
954 FND_GLOBAL.USER_ID,
955 FND_GLOBAL.LOGIN_ID,
956 FND_GLOBAL.PROG_APPL_ID,
957 FND_GLOBAL.Conc_Program_Id,
958 sysdate,
959 FND_GLOBAL.Conc_Request_Id,
960 p_shipment_request_info.delivery_interface_id,
961 '94X_STANDALONE'
962 );
963
964 IF l_debug_on THEN
965 wsh_debug_sv.logmsg(l_module_name, 'Done Inserting into WSH_DEL_DETAILS_INTERFACE and WSH_DEL_ASSGN_INTERFACE'||p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id);
966 END IF;
967
968 --}
969 END LOOP;
970 --}
971 ELSIF p_shipment_request_info.shipment_details_tab.count > 0 THEN
972 --{
973 --Bug8784331
974 FOR i IN 1..p_shipment_request_info.shipment_details_tab.count loop
975 l_date_requested(i) := p_shipment_request_info.shipment_details_tab(i).date_requested;
976 l_date_scheduled(i) := p_shipment_request_info.shipment_details_tab(i).date_scheduled;
977 l_inventory_item_id(i) := p_shipment_request_info.shipment_details_tab(i).inventory_item_id;
978 l_item_number(i) := p_shipment_request_info.shipment_details_tab(i).item_number;
979 l_customer_item_id(i) := p_shipment_request_info.shipment_details_tab(i).customer_item_id;
980 l_customer_item_number(i) := p_shipment_request_info.shipment_details_tab(i).customer_item_number;
981 l_requested_quantity(i) := p_shipment_request_info.shipment_details_tab(i).requested_quantity;
982 l_requested_quantity_uom(i) := p_shipment_request_info.shipment_details_tab(i).requested_quantity_uom;
983 l_src_requested_quantity(i) := p_shipment_request_info.shipment_details_tab(i).src_requested_quantity;
984 l_src_requested_quantity_uom(i) := p_shipment_request_info.shipment_details_tab(i).src_requested_quantity_uom;
985 l_line_number(i) := p_shipment_request_info.shipment_details_tab(i).line_number;
986 l_source_line_number(i) := p_shipment_request_info.shipment_details_tab(i).source_line_number;
987 l_source_header_number(i) := p_shipment_request_info.shipment_details_tab(i).source_header_number;
988 l_earliest_pickup_date(i) := p_shipment_request_info.shipment_details_tab(i).earliest_pickup_date;
989 l_latest_pickup_date(i) := p_shipment_request_info.shipment_details_tab(i).latest_pickup_date;
990 l_earliest_dropoff_date(i) := p_shipment_request_info.shipment_details_tab(i).earliest_dropoff_date;
991 l_latest_dropoff_date(i) := p_shipment_request_info.shipment_details_tab(i).latest_dropoff_date;
992 l_ship_tolerance_above(i) := p_shipment_request_info.shipment_details_tab(i).ship_tolerance_above;
993 l_ship_tolerance_below(i) := p_shipment_request_info.shipment_details_tab(i).ship_tolerance_below;
994 l_shipping_instructions(i) := p_shipment_request_info.shipment_details_tab(i).shipping_instructions;
995 l_packing_instructions(i) := p_shipment_request_info.shipment_details_tab(i).packing_instructions;
996 l_shipment_priority_code(i) := p_shipment_request_info.shipment_details_tab(i).shipment_priority_code;
997 l_cust_po_number(i) := p_shipment_request_info.shipment_details_tab(i).cust_po_number;
998 l_subinventory(i) := p_shipment_request_info.shipment_details_tab(i).subinventory;
999 l_locator_id(i) := p_shipment_request_info.shipment_details_tab(i).locator_id;
1000 l_locator_code(i) := p_shipment_request_info.shipment_details_tab(i).locator_code;
1001 l_lot_number(i) := p_shipment_request_info.shipment_details_tab(i).lot_number;
1002 l_revision(i) := p_shipment_request_info.shipment_details_tab(i).revision;
1003 l_ship_set_name(i) := p_shipment_request_info.shipment_details_tab(i).ship_set_name;
1004 l_unit_selling_price(i) := p_shipment_request_info.shipment_details_tab(i).unit_selling_price;
1005 END LOOP;
1006 --Bug8784331
1007 FORALL i IN 1..p_shipment_request_info.shipment_details_tab.count
1008 INSERT INTO WSH_DEL_DETAILS_INTERFACE(
1009 DELIVERY_DETAIL_INTERFACE_ID,
1010 SOURCE_CODE,
1011 SOURCE_LINE_ID,
1012 CREATION_DATE,
1013 CREATED_BY,
1014 LAST_UPDATE_DATE,
1015 LAST_UPDATED_BY,
1016 LAST_UPDATE_LOGIN,
1017 PROGRAM_APPLICATION_ID,
1018 PROGRAM_ID,
1019 PROGRAM_UPDATE_DATE,
1020 REQUEST_ID,
1021 CONTAINER_FLAG,
1022 DATE_REQUESTED,
1023 DATE_SCHEDULED,
1024 INVENTORY_ITEM_ID,
1025 ITEM_NUMBER,
1026 CUSTOMER_ITEM_ID,
1027 CUSTOMER_ITEM_NUMBER,
1028 REQUESTED_QUANTITY,
1029 REQUESTED_QUANTITY_UOM,
1030 SRC_REQUESTED_QUANTITY,
1031 SRC_REQUESTED_QUANTITY_UOM,
1032 LINE_NUMBER,
1033 SOURCE_LINE_NUMBER,
1034 SOURCE_HEADER_NUMBER,
1035 EARLIEST_PICKUP_DATE,
1036 LATEST_PICKUP_DATE,
1037 EARLIEST_DROPOFF_DATE,
1038 LATEST_DROPOFF_DATE,
1039 SHIP_TOLERANCE_ABOVE,
1040 SHIP_TOLERANCE_BELOW,
1041 SHIPPING_INSTRUCTIONS,
1042 PACKING_INSTRUCTIONS,
1043 SHIPMENT_PRIORITY_CODE,
1044 CUST_PO_NUMBER,
1045 SUBINVENTORY,
1046 LOCATOR_ID,
1047 LOCATOR_CODE,
1048 LOT_NUMBER,
1049 REVISION,
1050 SHIP_SET_NAME,
1051 UNIT_SELLING_PRICE,
1052 LINE_DIRECTION,
1053 INTERFACE_ACTION_CODE)
1054 VALUES(
1055 wsh_del_details_interface_s.nextval,
1056 'OE',
1057 -1,
1058 sysdate,
1059 FND_GLOBAL.USER_ID,
1060 sysdate,
1061 FND_GLOBAL.USER_ID,
1062 FND_GLOBAL.LOGIN_ID,
1063 FND_GLOBAL.PROG_APPL_ID,
1064 FND_GLOBAL.Conc_Program_Id,
1065 sysdate,
1066 FND_GLOBAL.Conc_Request_Id,
1067 'N',
1068 --Bug8784331
1069 l_date_requested(i),
1070 l_date_scheduled(i),
1071 l_inventory_item_id(i),
1072 l_item_number(i),
1073 l_customer_item_id(i),
1074 l_customer_item_number(i),
1075 l_requested_quantity(i),
1076 l_requested_quantity_uom(i),
1077 l_src_requested_quantity(i),
1078 l_src_requested_quantity_uom(i),
1079 l_line_number(i),
1080 l_source_line_number(i),
1081 l_source_header_number(i),
1082 l_earliest_pickup_date(i),
1083 l_latest_pickup_date(i),
1084 l_earliest_dropoff_date(i),
1085 l_latest_dropoff_date(i),
1086 l_ship_tolerance_above(i),
1087 l_ship_tolerance_below(i),
1088 l_shipping_instructions(i),
1089 l_packing_instructions(i),
1090 l_shipment_priority_code(i),
1091 l_cust_po_number(i),
1092 l_subinventory(i),
1093 l_locator_id(i),
1094 l_locator_code(i),
1095 l_lot_number(i),
1096 l_revision(i),
1097 l_ship_set_name(i),
1098 l_unit_selling_price(i),
1099 --Bug8784331
1100 'O',
1101 '94X_STANDALONE')
1102 RETURNING DELIVERY_DETAIL_INTERFACE_ID BULK COLLECT INTO l_del_det_int_tab;
1103 l_ins_rows := sql%rowcount;
1104 IF l_debug_on THEN
1105 WSH_DEBUG_SV.log(l_module_name, 'Number of records inserted into WSH_DEL_DETAILS_INTERFACE',l_ins_rows );
1106 END IF;
1107
1108 FOR I IN 1..l_del_det_int_tab.Count LOOP
1109 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := l_del_det_int_tab(i);
1110 END LOOP;
1111 FORALL i IN 1..p_shipment_request_info.shipment_details_tab.count
1112 INSERT INTO WSH_DEL_ASSGN_INTERFACE(
1113 DEL_ASSGN_INTERFACE_ID,
1114 DELIVERY_DETAIL_INTERFACE_ID,
1115 CREATION_DATE,
1116 CREATED_BY,
1117 LAST_UPDATE_DATE,
1118 LAST_UPDATED_BY,
1119 LAST_UPDATE_LOGIN,
1120 PROGRAM_APPLICATION_ID,
1121 PROGRAM_ID,
1122 PROGRAM_UPDATE_DATE,
1123 REQUEST_ID,
1124 DELIVERY_INTERFACE_ID,
1125 INTERFACE_ACTION_CODE
1126 )
1127 VALUES(
1128 wsh_del_assgn_interface_s.nextval,
1129 l_del_det_int_tab(i), --Bug8784331
1130 sysdate,
1131 FND_GLOBAL.USER_ID,
1132 sysdate,
1133 FND_GLOBAL.USER_ID,
1134 FND_GLOBAL.LOGIN_ID,
1135 FND_GLOBAL.PROG_APPL_ID,
1136 FND_GLOBAL.Conc_Program_Id,
1137 sysdate,
1138 FND_GLOBAL.Conc_Request_Id,
1139 p_shipment_request_info.delivery_interface_id,
1140 '94X_STANDALONE'
1141 );
1142 l_ins_rows := sql%rowcount;
1143 IF l_debug_on THEN
1144 WSH_DEBUG_SV.log(l_module_name, 'Number of records inserted into WSH_DEL_ASSGN_INTERFACE',l_ins_rows );
1145 END IF;
1146 --}
1147 END IF;--(if count >0)
1148
1149 IF p_caller = 'SHIPMENT_REQUEST' THEN
1150 IF l_debug_on THEN
1151 WSH_DEBUG_SV.log(l_module_name, 'Inserting into WSH_TRANSACTIONS_HISTORY' );
1152 END IF;
1153 INSERT INTO WSH_TRANSACTIONS_HISTORY(
1154 TRANSACTION_ID,
1155 DOCUMENT_TYPE,
1156 DOCUMENT_NUMBER,
1157 DOCUMENT_DIRECTION,
1158 TRANSACTION_STATUS,
1159 ACTION_TYPE,
1160 ENTITY_NUMBER,
1161 ENTITY_TYPE,
1162 TRADING_PARTNER_ID,
1163 CREATION_DATE,
1164 CREATED_BY,
1165 LAST_UPDATE_DATE,
1166 LAST_UPDATED_BY,
1167 LAST_UPDATE_LOGIN,
1168 PROGRAM_APPLICATION_ID,
1169 PROGRAM_ID,
1170 PROGRAM_UPDATE_DATE,
1171 REQUEST_ID,
1172 DOCUMENT_REVISION
1173 )
1174 VALUES (
1175 wsh_transaction_s.nextval,
1176 'SR',
1177 p_shipment_request_info.Document_Number,
1178 'I',
1179 'AP',
1180 p_shipment_request_info.ACTION_TYPE,
1181 p_shipment_request_info.delivery_interface_id,
1182 'DLVY_INT',
1183 -1,
1184 sysdate,
1185 FND_GLOBAL.USER_ID,
1186 sysdate,
1187 FND_GLOBAL.USER_ID,
1188 FND_GLOBAL.LOGIN_ID,
1189 FND_GLOBAL.PROG_APPL_ID,
1190 FND_GLOBAL.Conc_Program_Id,
1191 sysdate,
1192 FND_GLOBAL.Conc_Request_Id,
1193 p_shipment_request_info.document_revision )
1194 RETURNING TRANSACTION_ID INTO p_shipment_request_info.transaction_id;
1195 END IF;
1196
1197 IF l_debug_on THEN
1198 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
1199 WSH_DEBUG_SV.pop(l_module_name);
1200 END IF;
1201 EXCEPTION
1202 WHEN FND_API.G_EXC_ERROR THEN
1203 p_shipment_request_info.delivery_interface_id := NULL;
1204 p_shipment_request_info.transaction_id := null;
1205 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
1206 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
1207 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
1208 END LOOP;
1209 END IF;
1210 IF l_debug_on THEN
1211 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
1212 END IF;
1213 rollback;
1214 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.log(l_module_name, 'Error occured while creating shipment request with document number and revision',p_shipment_request_info.document_number||' and '||p_shipment_request_info.document_revision);
1217 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1218 END IF;
1219
1220 WHEN others THEN
1221 p_shipment_request_info.delivery_interface_id := NULL;
1222 p_shipment_request_info.transaction_id := null;
1223 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
1224 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
1225 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
1226 END LOOP;
1227 END IF;
1228 IF l_debug_on THEN
1229 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
1230 END IF;
1231 rollback;
1232 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1233 IF l_debug_on THEN
1234 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1236 END IF;
1237 END Create_Shipment_Request;
1238 --========================================================================
1239 -- PROCEDURE : Query_Shipment_Request PRIVATE
1240 --
1241 -- PARAMETERS:
1242 -- p_shipment_request_info Attributes for the create shipment request
1243 -- x_interface_errors_info Interface errors for WNDI and WDDI records
1244 -- x_return_status return status
1245 --
1246 -- COMMENT : Queries records from WTH, WNDI, WDDI and WIE tables based on
1247 -- document number and document revision passed.
1248 --
1249 --========================================================================
1250 PROCEDURE Query_Shipment_Request(
1251 p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
1252 x_interface_errors_info OUT NOCOPY Interface_Errors_Rec_Tab,
1253 x_return_status OUT NOCOPY VARCHAR2) AS
1254
1255
1256 CURSOR c_get_WIE_Rec IS
1257 SELECT p_shipment_request_info.document_number,p_shipment_request_info.document_revision,NULL,wie.error_message
1258 FROM WSH_INTERFACE_ERRORS WIE
1259 WHERE wie.interface_id = p_shipment_request_info.delivery_interface_id
1260 AND wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
1261 UNION
1262 SELECT p_shipment_request_info.document_number,p_shipment_request_info.document_revision,wddi.line_number,wie.error_message
1263 FROM WSH_INTERFACE_ERRORS WIE,
1264 wsh_del_assgn_interface WDAI,
1265 WSH_DEL_DETAILS_INTERFACE WDDI
1266 WHERE wie.interface_table_name = 'WSH_DEL_DETAILS_INTERFACE'
1267 AND wie.interface_id = WDAI.delivery_detail_interface_id
1268 AND wddi.delivery_detail_interface_id = WDAI.delivery_detail_interface_id
1269 and WDAI.delivery_interface_id = p_shipment_request_info.delivery_interface_id;
1270
1271 CURSOR c_get_WTH_WNDI_Rec IS
1272 SELECT WTH.transaction_id,
1273 WTH.action_type,
1274 WTH.document_number,
1275 WTH.document_revision,
1276 WNDI.delivery_interface_id,
1277 WNDI.organization_code,
1278 WNDI.customer_id,
1279 WNDI.customer_name,
1280 WNDI.ship_to_customer_id,
1281 WNDI.ship_to_customer_name,
1282 WNDI.ship_to_address_id,
1283 WNDI.ship_to_address1,
1284 WNDI.ship_to_address2,
1285 WNDI.ship_to_address3,
1286 WNDI.ship_to_address4,
1287 WNDI.ship_to_city,
1288 WNDI.ship_to_state,
1289 WNDI.ship_to_country,
1290 WNDI.ship_to_postal_code,
1291 WNDI.ship_to_contact_id,
1292 WNDI.ship_to_contact_name,
1293 WNDI.ship_to_contact_phone,
1294 WNDI.invoice_to_customer_id,
1295 WNDI.invoice_to_customer_name,
1296 WNDI.invoice_to_address_id,
1297 WNDI.invoice_to_address1,
1298 WNDI.invoice_to_address2,
1299 WNDI.invoice_to_address3,
1300 WNDI.invoice_to_address4,
1301 WNDI.invoice_to_city,
1302 WNDI.invoice_to_state,
1303 WNDI.invoice_to_country,
1304 WNDI.invoice_to_postal_code,
1305 WNDI.invoice_to_contact_id,
1306 WNDI.invoice_to_contact_name,
1307 WNDI.invoice_to_contact_phone,
1308 WNDI.deliver_to_customer_id,
1309 WNDI.deliver_to_customer_name,
1310 WNDI.deliver_to_address_id,
1311 WNDI.deliver_to_address1,
1312 WNDI.deliver_to_address2,
1313 WNDI.deliver_to_address3,
1314 WNDI.deliver_to_address4,
1315 WNDI.deliver_to_city,
1316 WNDI.deliver_to_state,
1317 WNDI.deliver_to_country,
1318 WNDI.deliver_to_postal_code,
1319 WNDI.deliver_to_contact_id,
1320 WNDI.deliver_to_contact_name,
1321 WNDI.deliver_to_contact_phone,
1322 WNDI.carrier_code,
1323 WNDI.service_level,
1324 WNDI.mode_of_transport,
1325 WNDI.freight_terms_code,
1326 WNDI.fob_code,
1327 WNDI.currency_code,
1328 WNDI.transaction_type_id,
1329 WNDI.price_list_id,
1330 WNDI.client_code -- LSP PROJECT
1331 FROM WSH_TRANSACTIONS_HISTORY WTH,
1332 WSH_NEW_DEL_INTERFACE WNDI
1333 WHERE WTH.document_number = to_char(p_shipment_request_info.document_number)
1334 AND WTH.document_revision = p_shipment_request_info.document_revision
1335 AND WTH.document_type ='SR'
1336 AND WTH.document_direction ='I'
1337 AND to_number(WTH.entity_number) = WNDI.delivery_interface_id
1338 AND WNDI.Interface_action_code = '94X_STANDALONE' ;
1339
1340 CURSOR c_get_WDDI_Rec IS
1341 SELECT WDDI.delivery_detail_interface_id,
1342 WDDI.line_number,
1343 WDDI.item_number,
1344 WDDI.inventory_item_id,
1345 WDDI.item_description,
1346 WDDI.requested_quantity,
1347 WDDI.requested_quantity_uom,
1348 WDDI.customer_item_number,
1349 WDDI.customer_item_id,
1350 WDDI.date_requested,
1351 WDDI.date_scheduled,
1352 WDDI.ship_tolerance_above,
1353 WDDI.ship_tolerance_below,
1354 WDDI.packing_instructions,
1355 WDDI.shipping_instructions,
1356 WDDI.shipment_priority_code,
1357 WDDI.ship_set_name,
1358 WDDI.subinventory,
1359 WDDI.revision,
1360 WDDI.locator_code,
1361 WDDI.locator_id,
1362 WDDI.lot_number,
1363 WDDI.unit_selling_price,
1364 WDDI.currency_code,
1365 WDDI.earliest_pickup_date,
1366 WDDI.latest_pickup_date,
1367 WDDI.earliest_dropoff_date,
1368 WDDI.latest_dropoff_date,
1369 WDDI.cust_po_number,
1370 WDDI.source_header_number,
1371 WDDI.source_line_number,
1372 WDDI.src_requested_quantity,
1373 WDDI.src_requested_quantity_uom
1374 FROM WSH_DEL_DETAILS_INTERFACE WDDI ,
1375 WSH_DEL_ASSGN_INTERFACE WDAI
1376 WHERE WDDI.delivery_detail_interface_id = WDAI.delivery_detail_interface_id
1377 AND WDAI.delivery_interface_id = p_shipment_request_info.delivery_interface_id
1378 AND WDDI.Interface_action_code = '94X_STANDALONE';
1379
1380 l_ins_rows NUMBER;
1381
1382 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Query_Shipment_Request';
1384
1385 BEGIN
1386 --
1387 IF l_debug_on THEN
1388 WSH_DEBUG_SV.push(l_module_name);
1389 WSH_DEBUG_SV.log(l_module_name, 'Document Number',p_shipment_request_info.document_number);
1390 WSH_DEBUG_SV.log(l_module_name, 'Document Revision',p_shipment_request_info.document_revision);
1391 END IF;
1392 --
1393 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1394 OPEN c_get_WTH_WNDI_Rec;
1395 FETCH c_get_WTH_WNDI_Rec INTO p_shipment_request_info.transaction_id,
1396 p_shipment_request_info.action_type,
1397 p_shipment_request_info.document_number,
1398 p_shipment_request_info.document_revision,
1399 p_shipment_request_info.delivery_interface_id,
1400 p_shipment_request_info.organization_code,
1401 p_shipment_request_info.customer_id,
1402 p_shipment_request_info.customer_name,
1403 p_shipment_request_info.ship_to_customer_id,
1404 p_shipment_request_info.ship_to_customer_name,
1405 p_shipment_request_info.ship_to_address_id,
1406 p_shipment_request_info.ship_to_address1,
1407 p_shipment_request_info.ship_to_address2,
1408 p_shipment_request_info.ship_to_address3,
1409 p_shipment_request_info.ship_to_address4,
1410 p_shipment_request_info.ship_to_city,
1411 p_shipment_request_info.ship_to_state,
1412 p_shipment_request_info.ship_to_country,
1413 p_shipment_request_info.ship_to_postal_code,
1414 p_shipment_request_info.ship_to_contact_id,
1415 p_shipment_request_info.ship_to_contact_name,
1416 p_shipment_request_info.ship_to_contact_phone,
1417 p_shipment_request_info.invoice_to_customer_id,
1418 p_shipment_request_info.invoice_to_customer_name,
1419 p_shipment_request_info.invoice_to_address_id,
1420 p_shipment_request_info.invoice_to_address1,
1421 p_shipment_request_info.invoice_to_address2,
1422 p_shipment_request_info.invoice_to_address3,
1423 p_shipment_request_info.invoice_to_address4,
1424 p_shipment_request_info.invoice_to_city,
1425 p_shipment_request_info.invoice_to_state,
1426 p_shipment_request_info.invoice_to_country,
1427 p_shipment_request_info.invoice_to_postal_code,
1428 p_shipment_request_info.invoice_to_contact_id,
1429 p_shipment_request_info.invoice_to_contact_name,
1430 p_shipment_request_info.invoice_to_contact_phone,
1431 p_shipment_request_info.deliver_to_customer_id,
1432 p_shipment_request_info.deliver_to_customer_name,
1433 p_shipment_request_info.deliver_to_address_id,
1434 p_shipment_request_info.deliver_to_address1,
1435 p_shipment_request_info.deliver_to_address2,
1436 p_shipment_request_info.deliver_to_address3,
1437 p_shipment_request_info.deliver_to_address4,
1438 p_shipment_request_info.deliver_to_city,
1439 p_shipment_request_info.deliver_to_state,
1440 p_shipment_request_info.deliver_to_country,
1441 p_shipment_request_info.deliver_to_postal_code,
1442 p_shipment_request_info.deliver_to_contact_id,
1443 p_shipment_request_info.deliver_to_contact_name,
1444 p_shipment_request_info.deliver_to_contact_phone,
1445 p_shipment_request_info.carrier_code,
1446 p_shipment_request_info.service_level,
1447 p_shipment_request_info.mode_of_transport,
1448 p_shipment_request_info.freight_terms_code,
1449 p_shipment_request_info.fob_code,
1450 p_shipment_request_info.currency_code,
1451 p_shipment_request_info.transaction_type_id,
1452 p_shipment_request_info.price_list_id,
1453 p_shipment_request_info.client_code; -- LSP PROJECT
1454
1455 IF c_get_WTH_WNDI_Rec%NOTFOUND THEN
1456 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1457 fnd_message.set_name('WSH', 'WSH_STND_INVALID_DOC');
1458 fnd_message.set_token('DOCUMENT_NUMBER', p_shipment_request_info.document_number);
1459 fnd_message.set_token('DOCUMENT_REVISION', p_shipment_request_info.document_revision);
1460 wsh_util_core.add_message(x_return_status);
1461
1462 IF l_debug_on THEN
1463 WSH_DEBUG_SV.log(l_module_name, 'No Data found');
1464 END IF;
1465 RAISE FND_API.G_EXC_ERROR;
1466 END IF;
1467
1468 l_ins_rows := sql%rowcount;
1469 IF l_debug_on THEN
1470 WSH_DEBUG_SV.log(l_module_name, 'Number of records returned from WTH and WNDI',l_ins_rows);
1471 END IF;
1472 CLOSE c_get_WTH_WNDI_Rec;
1473
1474
1475 OPEN c_get_WDDI_Rec;
1476 FETCH c_get_WDDI_Rec BULK COLLECT INTO p_shipment_request_info.shipment_details_tab;
1477 CLOSE c_get_WDDI_Rec;
1478 l_ins_rows := sql%rowcount;
1479 IF l_debug_on THEN
1480 WSH_DEBUG_SV.log(l_module_name, 'Number of records returned from WDDI',l_ins_rows);
1481 END IF;
1482
1483 OPEN c_get_WIE_Rec;
1484 FETCH c_get_WIE_Rec BULK COLLECT INTO x_interface_errors_info ;
1485 CLOSE c_get_WIE_Rec;
1486 l_ins_rows := sql%rowcount;
1487 IF l_debug_on THEN
1488 WSH_DEBUG_SV.log(l_module_name, 'Number of records returned from WIE',l_ins_rows);
1489 END IF;
1490 --
1491 IF l_debug_on THEN
1492 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
1493 WSH_DEBUG_SV.pop(l_module_name);
1494 END IF;
1495 --
1496
1497 EXCEPTION
1498 WHEN FND_API.G_EXC_ERROR THEN
1499 p_shipment_request_info.delivery_interface_id := NULL;
1500 p_shipment_request_info.transaction_id := null;
1501 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
1502 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
1503 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
1504 END LOOP;
1505 END IF;
1506 IF c_get_WTH_WNDI_Rec%ISOPEN THEN
1507 CLOSE c_get_WTH_WNDI_Rec;
1508 END IF;
1509 IF c_get_WIE_Rec%ISOPEN THEN
1510 CLOSE c_get_WIE_Rec;
1511 END IF;
1512 IF c_get_WDDI_Rec%ISOPEN THEN
1513 CLOSE c_get_WDDI_Rec;
1514 END IF;
1515 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1516 IF l_debug_on THEN
1517 WSH_DEBUG_SV.log(l_module_name, 'Error occured while querying shipment request with document number and revision',p_shipment_request_info.document_number||' and '||p_shipment_request_info.document_revision);
1518 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1519 END IF;
1520
1521 WHEN others THEN
1522 IF c_get_WTH_WNDI_Rec%ISOPEN THEN
1523 CLOSE c_get_WTH_WNDI_Rec;
1524 END IF;
1525 IF c_get_WIE_Rec%ISOPEN THEN
1526 CLOSE c_get_WIE_Rec;
1527 END IF;
1528 IF c_get_WDDI_Rec%ISOPEN THEN
1529 CLOSE c_get_WDDI_Rec;
1530 END IF;
1531 p_shipment_request_info.delivery_interface_id := NULL;
1532 p_shipment_request_info.transaction_id := null;
1533 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
1534 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
1535 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
1536 END LOOP;
1537 END IF;
1538 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1539 IF l_debug_on THEN
1540 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1541 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1542 END IF;
1543 END Query_Shipment_Request;
1544 --========================================================================
1545 -- PROCEDURE : Update_Delete_Shipment_Request PRIVATE
1546 --
1547 -- PARAMETERS:
1548 -- p_action_code UPDATE or DELETE
1549 -- p_shipment_request_info Attributes for the create shipment request
1550 -- x_return_status return status
1551 --
1552 -- COMMENT : Queries records from WTH, WNDI, WDDI and WIE tables based on
1553 -- document number and document revision passed.
1554 --
1555 --========================================================================
1556 PROCEDURE Update_Delete_Shipment_Request(
1557 p_action_code IN VARCHAR2 ,
1558 p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
1559 x_return_status OUT NOCOPY VARCHAR2) AS
1560
1561 CURSOR c_get_Details_DN IS
1562 SELECT wth.document_number,wth.document_revision,wndi.delivery_interface_id,wth.transaction_id
1563 FROM WSH_TRANSACTIONS_HISTORY WTH,
1564 WSH_NEW_DEL_INTERFACE WNDI,
1565 wsh_interface_errors wie
1566 WHERE WTH.document_number = to_char(p_shipment_request_info.document_number)
1567 and WTH.document_revision = p_shipment_request_info.document_revision
1568 and wth.document_type = 'SR'
1569 and wth.document_direction = 'I'
1570 and wie.interface_id(+) = WNDI.delivery_interface_id
1571 and wie.interface_table_name(+) = 'WSH_NEW_DEL_INTERFACE'
1572 and to_number(WTH.entity_number) = WNDI.delivery_interface_id
1573 AND WNDI.Interface_action_code = '94X_STANDALONE'
1574 FOR UPDATE OF wth.transaction_id, wndi.delivery_interface_id ,wie.interface_id NOWAIT;
1575
1576 CURSOR c_get_all_del_det_interface_id IS
1577 SELECT wdai.delivery_detail_interface_id
1578 FROM WSH_DEL_ASSGN_INTERFACE wdai,
1579 wsh_del_details_interface wddi,
1580 wsh_interface_errors wie
1581 WHERE wdai.delivery_interface_id = p_shipment_request_info.delivery_interface_id
1582 and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
1583 and wie.interface_id(+) = wdai.delivery_detail_interface_id
1584 and wie.interface_table_name(+) = 'WSH_DEL_DETAILS_INTERFACE'
1585 FOR UPDATE OF WDAI.delivery_detail_interface_id, wddi.delivery_detail_interface_id,wie.interface_id NOWAIT;
1586
1587 CURSOR c_get_del_det_interface_id(c_line_number VARCHAR2) IS
1588 SELECT wdai.delivery_detail_interface_id
1589 FROM wsh_del_details_interface wddi,
1590 wsh_del_assgn_interface wdai,
1591 wsh_interface_errors wie
1592 WHERE wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
1593 AND wdai.delivery_interface_id= p_shipment_request_info.delivery_interface_id
1594 AND wddi.line_number = c_line_number
1595 and wie.interface_id(+) = wdai.delivery_detail_interface_id
1596 and wie.interface_table_name(+) = 'WSH_DEL_DETAILS_INTERFACE'
1597 FOR UPDATE OF WDAI.delivery_detail_interface_id, wddi.delivery_detail_interface_id,wie.interface_id NOWAIT;
1598
1599 CURSOR C_OPEN_DEL_DET IS
1600 SELECT Count(*)
1601 from wsh_del_assgn_interface wdai
1602 where wdai.delivery_interface_id= p_shipment_request_info.delivery_interface_id;
1603
1604 --
1605 l_delivery_interface_id WSH_NEW_DEL_INTERFACE.delivery_interface_id%TYPE;
1606 l_transaction_id WSH_TRANSACTIONS_HISTORY.Transaction_Id%TYPE;
1607 l_del_det_int_tab wsh_util_core.id_tab_type;
1608 l_document_number WSH_TRANSACTIONS_HISTORY.document_number%TYPE;
1609 l_document_revision WSH_TRANSACTIONS_HISTORY.document_revision%TYPE;
1610 l_count NUMBER;
1611 l_ins_rows NUMBER;
1612 l_ins_count NUMBER :=1;
1613 l_upd_count NUMBER :=1;
1614 l_return_status VARCHAR2(100);
1615 l_tmp_status VARCHAR2(1);
1616 --
1617 l_insert_sr_info Shipment_Request_Rec_Type;
1618 l_update_sr_info Shipment_Request_Rec_Type;
1619 --
1620 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1621 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_Delete_Shipment_Request';
1622 --
1623
1624 --
1625 RECORD_LOCKED EXCEPTION;
1626 PRAGMA EXCEPTION_INIT(record_locked, -54);
1627 --
1628 --Bug8784331
1629 l_date_requested wsh_util_core.Date_Tab_Type;
1630 l_date_scheduled wsh_util_core.Date_Tab_Type;
1631 l_inventory_item_id wsh_util_core.Id_Tab_Type;
1632 l_item_number wsh_util_core.tbl_varchar;
1633 l_customer_item_id wsh_util_core.Id_Tab_Type;
1634 l_customer_item_number wsh_util_core.tbl_varchar;
1635 l_requested_quantity wsh_util_core.Id_Tab_Type;
1636 l_requested_quantity_uom wsh_util_core.tbl_varchar;
1637 l_src_requested_quantity wsh_util_core.Id_Tab_Type;
1638 l_src_requested_quantity_uom wsh_util_core.tbl_varchar;
1639 l_line_number wsh_util_core.Id_Tab_Type;
1640 l_source_line_number wsh_util_core.tbl_varchar;
1641 l_source_header_number wsh_util_core.tbl_varchar;
1642 l_earliest_pickup_date wsh_util_core.Date_Tab_Type;
1643 l_latest_pickup_date wsh_util_core.Date_Tab_Type;
1644 l_earliest_dropoff_date wsh_util_core.Date_Tab_Type;
1645 l_latest_dropoff_date wsh_util_core.Date_Tab_Type;
1646 l_ship_tolerance_above wsh_util_core.Id_Tab_Type;
1647 l_ship_tolerance_below wsh_util_core.Id_Tab_Type;
1648 l_shipping_instructions wsh_util_core.tbl_varchar;
1649 l_packing_instructions wsh_util_core.tbl_varchar;
1650 l_shipment_priority_code wsh_util_core.tbl_varchar;
1651 l_cust_po_number wsh_util_core.tbl_varchar;
1652 l_subinventory wsh_util_core.tbl_varchar;
1653 l_locator_id wsh_util_core.Id_Tab_Type;
1654 l_locator_code wsh_util_core.tbl_varchar;
1655 l_lot_number wsh_util_core.tbl_varchar;
1656 l_revision wsh_util_core.tbl_varchar;
1657 l_ship_set_name wsh_util_core.tbl_varchar;
1658 l_unit_selling_price wsh_util_core.Id_Tab_Type;
1659 l_delivery_detail_interface_id wsh_util_core.Id_Tab_Type;
1660 --Bug8784331
1661
1662
1663 BEGIN
1664
1665 IF l_debug_on THEN
1666 WSH_DEBUG_SV.push(l_module_name);
1667 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code);
1668 WSH_DEBUG_SV.log(l_module_name,'WDDI count',p_shipment_request_info.shipment_details_tab.count);
1669 END IF;
1670
1671
1672 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1673 l_tmp_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1674
1675 IF l_debug_on THEN
1676 wsh_debug_sv.logmsg(l_module_name, 'Taking locks on WTH,WNDI');
1677 END IF;
1678
1679 OPEN c_get_Details_DN;
1680 FETCH c_get_Details_DN INTO l_document_number,l_document_revision,l_delivery_interface_id,l_transaction_id;
1681 IF c_get_Details_DN%NOTFOUND THEN
1682 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1683 fnd_message.set_name('WSH', 'WSH_STND_INVALID_DOC');
1684 fnd_message.set_token('DOCUMENT_NUMBER', p_shipment_request_info.document_number);
1685 fnd_message.set_token('DOCUMENT_REVISION', p_shipment_request_info.document_revision);
1686 wsh_util_core.add_message(x_return_status);
1687 IF l_debug_on THEN
1688 wsh_debug_sv.logmsg(l_module_name, 'Invalid parameters have been passed');
1689 END IF;
1690 RAISE FND_API.G_EXC_ERROR;
1691 END IF;
1692 CLOSE c_get_Details_DN;
1693
1694 p_shipment_request_info.delivery_interface_id := l_delivery_interface_id;
1695
1696 IF p_shipment_request_info.shipment_details_tab.count > 0 THEN
1697 l_insert_sr_info.document_number := p_shipment_request_info.document_number;
1698 l_insert_sr_info.document_revision := p_shipment_request_info.document_revision;
1699 l_insert_sr_info.delivery_interface_id := p_shipment_request_info.delivery_interface_id;
1700
1701 l_ins_count := 1;
1702 l_upd_count := 1;
1703
1704 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
1705
1706 IF p_shipment_request_info.shipment_details_tab(i).line_number IS NULL THEN
1707
1708 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1709 fnd_message.set_name('WSH', 'WSH_STND_ATTR_MANDATORY');
1710 fnd_message.set_token('ATTRIBUTE','LINE_NUMBER');
1711 wsh_util_core.add_message(x_return_status);
1712 IF l_debug_on THEN
1713 wsh_debug_sv.logmsg(l_module_name, 'Line number is a mandatory parameter for delivery_detail_interface records');
1714 END IF;
1715 RAISE FND_API.G_EXC_ERROR;
1716 END IF;
1717 IF l_debug_on THEN
1718 wsh_debug_sv.logmsg(l_module_name, 'Taking locks on WDDI and WDAI for line number :'||p_shipment_request_info.shipment_details_tab(i).line_number);
1719 END IF;
1720 OPEN c_get_del_det_interface_id(p_shipment_request_info.shipment_details_tab(i).line_number);
1721 FETCH c_get_del_det_interface_id INTO l_del_det_int_tab(i);
1722 IF c_get_del_det_interface_id%NOTFOUND THEN
1723 IF p_action_code = 'UPDATE' THEN
1724 --
1725 IF l_debug_on THEN
1726 wsh_debug_sv.logmsg(l_module_name, 'line number ' || p_shipment_request_info.shipment_details_tab(i).line_number || ' does not exist, so insert into WDDI');
1727 END IF;
1728 --
1729 l_insert_sr_info.shipment_details_tab(l_ins_count):= p_shipment_request_info.shipment_details_tab(i);
1730 l_ins_count := l_ins_count + 1;
1731 ELSE
1732 --
1733 IF l_debug_on THEN
1734 wsh_debug_sv.logmsg(l_module_name, 'Error: Line number ' || p_shipment_request_info.shipment_details_tab(i).line_number || ' does not exist');
1735 END IF;
1736 --
1737 l_tmp_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1738 fnd_message.set_name('WSH', 'WSH_STND_INVALID_DOC_LINE');
1739 fnd_message.set_token('LINE_NUMBER', p_shipment_request_info.shipment_details_tab(i).line_number);
1740 wsh_util_core.add_message(l_tmp_status);
1741 END IF;
1742 ELSE
1743 l_update_sr_info.shipment_details_tab(l_upd_count):= p_shipment_request_info.shipment_details_tab(i);
1744 l_upd_count :=l_upd_count + 1;
1745 END IF;
1746 CLOSE c_get_del_det_interface_id;
1747 END LOOP;
1748 ELSE
1749 IF p_action_code ='DELETE' THEN
1750
1751 IF l_debug_on THEN
1752 wsh_debug_sv.logmsg(l_module_name, 'Taking locks on WDDI and WDAI');
1753 END IF;
1754
1755 OPEN c_get_all_del_det_interface_id;
1756 FETCH c_get_all_del_det_interface_id BULK COLLECT INTO l_del_det_int_tab;
1757 CLOSE c_get_all_del_det_interface_id;
1758 END IF;
1759 END IF;
1760
1761 IF l_tmp_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1762 RAISE FND_API.G_EXC_ERROR;
1763 END IF;
1764
1765 IF l_debug_on THEN
1766 wsh_debug_sv.logmsg(l_module_name, 'Successfully locked records of WTH,WNDI and WDDI');
1767 END IF;
1768
1769 IF p_action_code ='UPDATE' THEN
1770 --{
1771 IF l_debug_on THEN
1772 wsh_debug_sv.logmsg(l_module_name, 'Updating WSH_NEW_DEL_INTERFACE');
1773 END IF;
1774 UPDATE WSH_NEW_DEL_INTERFACE
1775 SET last_update_date = sysdate,
1776 last_updated_by = FND_GLOBAL.USER_ID,
1777 last_update_login = FND_GLOBAL.Login_Id,
1778 program_application_id = FND_GLOBAL.Prog_Appl_Id,
1779 program_id = FND_GLOBAL.Conc_Program_Id,
1780 program_update_date = sysdate,
1781 request_id = FND_GLOBAL.Conc_Request_Id,
1782 organization_code = p_shipment_request_info.organization_code,
1783 carrier_code = p_shipment_request_info.carrier_code,
1784 service_level = p_shipment_request_info.service_level,
1785 mode_of_transport = p_shipment_request_info.mode_of_transport,
1786 customer_id = p_shipment_request_info.customer_id,
1787 customer_name = p_shipment_request_info.customer_name,
1788 ship_to_customer_id = p_shipment_request_info.ship_to_customer_id,
1789 ship_to_customer_name = p_shipment_request_info.ship_to_customer_name,
1790 ship_to_address_id = p_shipment_request_info.ship_to_address_id,
1791 ship_to_address1 = p_shipment_request_info.ship_to_address1,
1792 ship_to_address2 = p_shipment_request_info.ship_to_address2,
1793 ship_to_address3 = p_shipment_request_info.ship_to_address3,
1794 ship_to_address4 = p_shipment_request_info.ship_to_address4,
1795 ship_to_city = p_shipment_request_info.ship_to_city,
1796 ship_to_state = p_shipment_request_info.ship_to_state,
1797 ship_to_country = p_shipment_request_info.ship_to_country,
1798 ship_to_postal_code = p_shipment_request_info.ship_to_postal_code,
1799 ship_to_contact_id = p_shipment_request_info.ship_to_contact_id ,
1800 ship_to_contact_name = p_shipment_request_info.ship_to_contact_name,
1801 ship_to_contact_phone = p_shipment_request_info.ship_to_contact_phone,
1802 invoice_to_customer_id = p_shipment_request_info.invoice_to_customer_id,
1803 invoice_to_customer_name = p_shipment_request_info.invoice_to_customer_name,
1804 invoice_to_address_id = p_shipment_request_info.invoice_to_address_id,
1805 invoice_to_address1 = p_shipment_request_info.invoice_to_address1,
1806 invoice_to_address2 = p_shipment_request_info.invoice_to_address2,
1807 invoice_to_address3 = p_shipment_request_info.invoice_to_address3,
1808 invoice_to_address4 = p_shipment_request_info.invoice_to_address4,
1809 invoice_to_city = p_shipment_request_info.invoice_to_city,
1810 invoice_to_state = p_shipment_request_info.invoice_to_state,
1811 invoice_to_country = p_shipment_request_info.invoice_to_country,
1812 invoice_to_postal_code = p_shipment_request_info.invoice_to_postal_code,
1813 invoice_to_contact_id = p_shipment_request_info.invoice_to_contact_id,
1814 invoice_to_contact_name = p_shipment_request_info.invoice_to_contact_name,
1815 invoice_to_contact_phone = p_shipment_request_info.invoice_to_contact_phone,
1816 deliver_to_customer_id = p_shipment_request_info.deliver_to_customer_id,
1817 deliver_to_customer_name = p_shipment_request_info.deliver_to_customer_name,
1818 deliver_to_address_id = p_shipment_request_info.deliver_to_address_id,
1819 deliver_to_address1 = p_shipment_request_info.deliver_to_address1,
1820 deliver_to_address2 = p_shipment_request_info.deliver_to_address2,
1821 deliver_to_address3 = p_shipment_request_info.deliver_to_address3,
1822 deliver_to_address4 = p_shipment_request_info.deliver_to_address4,
1823 deliver_to_city = p_shipment_request_info.deliver_to_city,
1824 deliver_to_state = p_shipment_request_info.deliver_to_state,
1825 deliver_to_country = p_shipment_request_info.deliver_to_country,
1826 deliver_to_postal_code = p_shipment_request_info.deliver_to_postal_code,
1827 deliver_to_contact_id = p_shipment_request_info.deliver_to_contact_id,
1828 deliver_to_contact_name = p_shipment_request_info.deliver_to_contact_name,
1829 deliver_to_contact_phone = p_shipment_request_info.deliver_to_contact_phone,
1830 freight_terms_code = p_shipment_request_info.freight_terms_code,
1831 fob_code = p_shipment_request_info.fob_code,
1832 transaction_type_id = p_shipment_request_info.transaction_type_id,
1833 price_list_id = p_shipment_request_info.price_list_id,
1834 currency_code = p_shipment_request_info.currency_code,
1835 client_code = p_shipment_request_info.client_code -- LSP PROJECT
1836 WHERE delivery_interface_id = l_delivery_interface_id;
1837
1838 l_ins_rows := sql%rowcount;
1839 IF l_debug_on THEN
1840 WSH_DEBUG_SV.log(l_module_name, 'WNDI : Number of records updated',l_ins_rows);
1841 END IF;
1842
1843 IF l_update_sr_info.shipment_details_tab.count > 0 AND
1844 l_update_sr_info.shipment_details_tab.count < 4 THEN
1845 --{
1846 IF l_debug_on THEN
1847 WSH_DEBUG_SV.log(l_module_name, 'Updating in non-bulk mode', l_update_sr_info.shipment_details_tab.count);
1848 END IF;
1849 --
1850 FOR i in 1..l_update_sr_info.shipment_details_tab.count LOOP
1851 --
1852 IF l_debug_on THEN
1853 WSH_DEBUG_SV.log(l_module_name, 'updating delivery_detail_interface_id',l_update_sr_info.shipment_details_tab(i).delivery_detail_interface_id );
1854 END IF;
1855 --
1856 UPDATE WSH_DEL_DETAILS_INTERFACE
1857 SET last_update_date = sysdate,
1858 last_updated_by = FND_GLOBAL.USER_ID,
1859 last_update_login = FND_GLOBAL.Login_Id,
1860 program_application_id = FND_GLOBAL.Prog_Appl_Id,
1861 program_id = FND_GLOBAL.Conc_Program_Id,
1862 program_update_date = sysdate,
1863 request_id = FND_GLOBAL.Conc_Request_Id,
1864 date_requested = l_update_sr_info.shipment_details_tab(i).date_requested,
1865 date_scheduled = l_update_sr_info.shipment_details_tab(i).date_scheduled,
1866 inventory_item_id = l_update_sr_info.shipment_details_tab(i).inventory_item_id,
1867 item_number = l_update_sr_info.shipment_details_tab(i).item_number,
1868 customer_item_id = l_update_sr_info.shipment_details_tab(i).customer_item_id,
1869 customer_item_number = l_update_sr_info.shipment_details_tab(i).customer_item_number,
1870 requested_quantity = l_update_sr_info.shipment_details_tab(i).requested_quantity,
1871 requested_quantity_uom = l_update_sr_info.shipment_details_tab(i).requested_quantity_uom,
1872 src_requested_quantity = l_update_sr_info.shipment_details_tab(i).src_requested_quantity,
1873 src_requested_quantity_uom = l_update_sr_info.shipment_details_tab(i).src_requested_quantity_uom,
1874 line_number = l_update_sr_info.shipment_details_tab(i).line_number,
1875 source_line_number = l_update_sr_info.shipment_details_tab(i).source_line_number,
1876 earliest_pickup_date = l_update_sr_info.shipment_details_tab(i).earliest_pickup_date,
1877 latest_pickup_date = l_update_sr_info.shipment_details_tab(i).latest_pickup_date,
1878 earliest_dropoff_date = l_update_sr_info.shipment_details_tab(i).earliest_dropoff_date,
1879 latest_dropoff_date = l_update_sr_info.shipment_details_tab(i).latest_dropoff_date,
1880 ship_tolerance_above = l_update_sr_info.shipment_details_tab(i).ship_tolerance_above,
1881 ship_tolerance_below = l_update_sr_info.shipment_details_tab(i).ship_tolerance_below,
1882 shipping_instructions = l_update_sr_info.shipment_details_tab(i).shipping_instructions,
1883 packing_instructions = l_update_sr_info.shipment_details_tab(i).packing_instructions,
1884 shipment_priority_code = l_update_sr_info.shipment_details_tab(i).shipment_priority_code,
1885 cust_po_number = l_update_sr_info.shipment_details_tab(i).cust_po_number,
1886 subinventory = l_update_sr_info.shipment_details_tab(i).subinventory,
1887 locator_id = l_update_sr_info.shipment_details_tab(i).locator_id,
1888 locator_code = l_update_sr_info.shipment_details_tab(i).locator_code,
1889 lot_number = l_update_sr_info.shipment_details_tab(i).lot_number,
1890 revision = l_update_sr_info.shipment_details_tab(i).revision,
1891 ship_set_name = l_update_sr_info.shipment_details_tab(i).ship_set_name,
1892 unit_selling_price = l_update_sr_info.shipment_details_tab(i).unit_selling_price
1893 WHERE delivery_detail_interface_id = l_update_sr_info.shipment_details_tab(i).delivery_detail_interface_id;
1894 END LOOP;
1895 --}
1896 ELSIF l_update_sr_info.shipment_details_tab.count > 0 THEN
1897 --{
1898 IF l_debug_on THEN
1899 WSH_DEBUG_SV.log(l_module_name, 'Updating in bulk mode', l_update_sr_info.shipment_details_tab.count);
1900 END IF;
1901 --
1902 --Bug8784331
1903 FOR i in 1..l_update_sr_info.shipment_details_tab.count LOOP
1904 l_date_requested(i) := l_update_sr_info.shipment_details_tab(i).date_requested;
1905 l_date_scheduled(i) := l_update_sr_info.shipment_details_tab(i).date_scheduled;
1906 l_inventory_item_id(i) := l_update_sr_info.shipment_details_tab(i).inventory_item_id;
1907 l_item_number(i) := l_update_sr_info.shipment_details_tab(i).item_number;
1908 l_customer_item_id(i) := l_update_sr_info.shipment_details_tab(i).customer_item_id;
1909 l_customer_item_number(i) := l_update_sr_info.shipment_details_tab(i).customer_item_number;
1910 l_requested_quantity(i) := l_update_sr_info.shipment_details_tab(i).requested_quantity;
1911 l_requested_quantity_uom(i) := l_update_sr_info.shipment_details_tab(i).requested_quantity_uom;
1912 l_src_requested_quantity(i) := l_update_sr_info.shipment_details_tab(i).src_requested_quantity;
1913 l_src_requested_quantity_uom(i) := l_update_sr_info.shipment_details_tab(i).src_requested_quantity_uom;
1914 l_line_number(i) := l_update_sr_info.shipment_details_tab(i).line_number;
1915 l_source_line_number(i) := l_update_sr_info.shipment_details_tab(i).source_line_number;
1916 l_earliest_pickup_date(i) := l_update_sr_info.shipment_details_tab(i).earliest_pickup_date;
1917 l_latest_pickup_date(i) := l_update_sr_info.shipment_details_tab(i).latest_pickup_date;
1918 l_earliest_dropoff_date(i) := l_update_sr_info.shipment_details_tab(i).earliest_dropoff_date;
1919 l_latest_dropoff_date(i) := l_update_sr_info.shipment_details_tab(i).latest_dropoff_date;
1920 l_ship_tolerance_above(i) := l_update_sr_info.shipment_details_tab(i).ship_tolerance_above;
1921 l_ship_tolerance_below(i) := l_update_sr_info.shipment_details_tab(i).ship_tolerance_below;
1922 l_shipping_instructions(i) := l_update_sr_info.shipment_details_tab(i).shipping_instructions ;
1923 l_packing_instructions(i) := l_update_sr_info.shipment_details_tab(i).packing_instructions;
1924 l_shipment_priority_code(i) := l_update_sr_info.shipment_details_tab(i).shipment_priority_code;
1925 l_cust_po_number(i) := l_update_sr_info.shipment_details_tab(i).cust_po_number;
1926 l_subinventory(i) := l_update_sr_info.shipment_details_tab(i).subinventory;
1927 l_locator_id(i) := l_update_sr_info.shipment_details_tab(i).locator_id;
1928 l_locator_code(i) := l_update_sr_info.shipment_details_tab(i).locator_code;
1929 l_lot_number(i) := l_update_sr_info.shipment_details_tab(i).lot_number;
1930 l_revision(i) := l_update_sr_info.shipment_details_tab(i).revision;
1931 l_ship_set_name(i) := l_update_sr_info.shipment_details_tab(i).ship_set_name;
1932 l_unit_selling_price(i) := l_update_sr_info.shipment_details_tab(i).unit_selling_price;
1933 l_delivery_detail_interface_id(i):= l_update_sr_info.shipment_details_tab(i).delivery_detail_interface_id;
1934 END LOOP;
1935 --Bug8784331
1936 FORALL i in 1..l_update_sr_info.shipment_details_tab.count
1937 UPDATE WSH_DEL_DETAILS_INTERFACE
1938 SET last_update_date = sysdate,
1939 last_updated_by = FND_GLOBAL.USER_ID,
1940 last_update_login = FND_GLOBAL.Login_Id,
1941 program_application_id = FND_GLOBAL.Prog_Appl_Id,
1942 program_id = FND_GLOBAL.Conc_Program_Id,
1943 program_update_date = sysdate,
1944 request_id = FND_GLOBAL.Conc_Request_Id,
1945 --Bug8784331
1946 date_requested = l_date_requested(i),
1947 date_scheduled = l_date_scheduled(i) ,
1948 inventory_item_id = l_inventory_item_id(i),
1949 item_number = l_item_number(i),
1950 customer_item_id = l_customer_item_id(i),
1951 customer_item_number = l_customer_item_number(i),
1952 requested_quantity = l_requested_quantity(i),
1953 requested_quantity_uom = l_requested_quantity_uom(i),
1954 src_requested_quantity = l_src_requested_quantity(i) ,
1955 src_requested_quantity_uom = l_src_requested_quantity_uom(i),
1956 line_number = l_line_number(i),
1957 source_line_number = l_source_line_number(i),
1958 earliest_pickup_date = l_earliest_pickup_date(i),
1959 latest_pickup_date = l_latest_pickup_date(i),
1960 earliest_dropoff_date = l_earliest_dropoff_date(i),
1961 latest_dropoff_date = l_latest_dropoff_date(i),
1962 ship_tolerance_above = l_ship_tolerance_above(i) ,
1963 ship_tolerance_below = l_ship_tolerance_below(i),
1964 shipping_instructions = l_shipping_instructions(i) ,
1965 packing_instructions = l_packing_instructions(i),
1966 shipment_priority_code = l_shipment_priority_code(i),
1967 cust_po_number = l_cust_po_number(i),
1968 subinventory = l_subinventory(i),
1969 locator_id = l_locator_id(i),
1970 locator_code = l_locator_code(i),
1971 lot_number = l_lot_number(i),
1972 revision = l_revision(i),
1973 ship_set_name = l_ship_set_name(i),
1974 unit_selling_price = l_unit_selling_price(i)
1975 WHERE delivery_detail_interface_id = l_delivery_detail_interface_id(i) ;
1976 --Bug8784331
1977 --}
1978 END IF;
1979
1980 IF l_insert_sr_info.shipment_details_tab.count > 0 THEN
1981 --
1982 IF l_debug_on THEN
1983 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Shipment_Request with UPDATE', WSH_DEBUG_SV.C_PROC_LEVEL);
1984 END IF;
1985 --
1986 Create_Shipment_Request(p_shipment_request_info => l_insert_sr_info,
1987 p_caller => 'UPDATE',
1988 x_return_status => l_return_status);
1989
1990 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1991 IF l_debug_on THEN
1992 WSH_DEBUG_SV.log(l_module_name, 'The Action of inserting new del detail interface lines failed');
1993 END IF;
1994 RAISE FND_API.G_EXC_ERROR;
1995 END IF;
1996 IF l_debug_on THEN
1997 WSH_DEBUG_SV.log(l_module_name, 'The Action of inserting new del detail interface lines is successful');
1998 END IF;
1999 END IF;
2000 --}
2001 ELSE
2002 --{
2003 IF l_debug_on THEN
2004 wsh_debug_sv.logmsg(l_module_name, 'Deleting records from WTH,WDDI,WDAI,WNDI');
2005 END IF;
2006 ---------
2007 IF l_del_det_int_tab.count > 0 AND l_del_det_int_tab.count < 4 THEN
2008 --
2009 IF l_debug_on THEN
2010 WSH_DEBUG_SV.log(l_module_name, 'Deleting in non-bulk mode', l_del_det_int_tab.count);
2011 END IF;
2012 --
2013 FOR i in 1..l_del_det_int_tab.count LOOP
2014 delete from wsh_del_assgn_interface
2015 where delivery_detail_interface_id = l_del_det_int_tab(i);
2016 l_ins_rows := sql%rowcount;
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.log(l_module_name,i||' WDAI : Number of records Deleted',l_ins_rows);
2019 END IF;
2020
2021
2022 delete from WSH_DEL_DETAILS_INTERFACE
2023 where delivery_detail_interface_id = l_del_det_int_tab(i);
2024 l_ins_rows := sql%rowcount;
2025 IF l_debug_on THEN
2026 WSH_DEBUG_SV.log(l_module_name,i||' WDDI : Number of records Deleted',l_ins_rows);
2027 END IF;
2028
2029 delete from WSH_INTERFACE_ERRORS
2030 where interface_id = l_del_det_int_tab(i)
2031 and interface_table_name = 'WSH_DEL_DETAILS_INTERFACE';
2032 l_ins_rows := sql%rowcount;
2033 IF l_debug_on THEN
2034 WSH_DEBUG_SV.log(l_module_name,i||' WIE : Number of records Deleted',l_ins_rows);
2035 END IF;
2036
2037 END LOOP;
2038
2039 ELSIF l_del_det_int_tab.count > 0 THEN
2040 --
2041 IF l_debug_on THEN
2042 WSH_DEBUG_SV.log(l_module_name, 'Deleting in bulk mode', l_del_det_int_tab.count);
2043 END IF;
2044 --
2045 FORALL i in 1..l_del_det_int_tab.count
2046 delete from wsh_del_assgn_interface
2047 where delivery_detail_interface_id = l_del_det_int_tab(i);
2048
2049 l_ins_rows := sql%rowcount;
2050 IF l_debug_on THEN
2051 WSH_DEBUG_SV.log(l_module_name, 'WDAI : Number of records Deleted',l_ins_rows);
2052 END IF;
2053
2054 FORALL i in 1..l_del_det_int_tab.count
2055 delete from WSH_DEL_DETAILS_INTERFACE
2056 where delivery_detail_interface_id = l_del_det_int_tab(i);
2057
2058 l_ins_rows := sql%rowcount;
2059 IF l_debug_on THEN
2060 WSH_DEBUG_SV.log(l_module_name, 'WDDI : Number of records Deleted',l_ins_rows);
2061 END IF;
2062
2063 FORALL i in 1..l_del_det_int_tab.count
2064 delete from WSH_INTERFACE_ERRORS
2065 where interface_id = l_del_det_int_tab(i)
2066 and interface_table_name= 'WSH_DEL_DETAILS_INTERFACE';
2067 l_ins_rows := sql%rowcount;
2068 IF l_debug_on THEN
2069 WSH_DEBUG_SV.log(l_module_name, 'WIE : Number of records Deleted',l_ins_rows);
2070 END IF;
2071
2072 END IF;
2073 ---------
2074 OPEN C_OPEN_DEL_DET;
2075 FETCH C_OPEN_DEL_DET into l_count;
2076 CLOSE C_OPEN_DEL_DET;
2077
2078 IF l_count = 0 THEN --Will be deleting WNDI and WTH records only iof there are no more detail records for the shipment request
2079 ---------
2080 delete from wsh_new_del_interface where delivery_interface_id = l_delivery_interface_id;
2081 l_ins_rows := sql%rowcount;
2082 IF l_debug_on THEN
2083 WSH_DEBUG_SV.log(l_module_name, 'WNDI : Number of records Deleted',l_ins_rows);
2084 END IF;
2085 ---------
2086 delete from wsh_transactions_history where transaction_id = l_transaction_id;
2087 l_ins_rows := sql%rowcount;
2088 IF l_debug_on THEN
2089 WSH_DEBUG_SV.log(l_module_name, 'WTH : Number of records Deleted',l_ins_rows);
2090 END IF;
2091 ---------
2092 delete from wsh_interface_errors
2093 where interface_id = l_delivery_interface_id
2094 and interface_table_name = 'WSH_NEW_DEL_INTERFACE';
2095 l_ins_rows := sql%rowcount;
2096 IF l_debug_on THEN
2097 WSH_DEBUG_SV.log(l_module_name, 'WIE : Number of records Deleted',l_ins_rows);
2098 END IF;
2099 END IF;
2100
2101 IF l_debug_on THEN
2102 wsh_debug_sv.logmsg(l_module_name, 'Successfully deleted records.');
2103 END IF;
2104 --}
2105 END IF;
2106
2107 IF l_debug_on THEN
2108 WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
2109 WSH_DEBUG_SV.pop(l_module_name);
2110 END IF;
2111 EXCEPTION
2112 WHEN RECORD_LOCKED THEN
2113 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2114 FND_MESSAGE.SET_NAME('WSH','WSH_NO_LOCK');
2115 IF l_debug_on THEN
2116 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
2117 END IF;
2118 rollback;
2119 wsh_util_core.add_message(x_return_status,l_module_name);
2120 IF l_debug_on THEN
2121 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2122 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
2123 END IF;
2124 IF c_get_Details_DN%ISOPEN THEN
2125 CLOSE c_get_Details_DN;
2126 END IF;
2127 IF c_get_all_del_det_interface_id%ISOPEN THEN
2128 CLOSE c_get_all_del_det_interface_id;
2129 END IF;
2130 IF c_get_del_det_interface_id%ISOPEN THEN
2131 CLOSE c_get_del_det_interface_id;
2132 END IF;
2133
2134 WHEN FND_API.G_EXC_ERROR THEN
2135 p_shipment_request_info.delivery_interface_id := NULL;
2136 p_shipment_request_info.transaction_id := null;
2137 IF p_shipment_request_info.shipment_details_tab.count >0 THEN
2138 FOR i in 1..p_shipment_request_info.shipment_details_tab.count LOOP
2139 p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id := NULL;
2140 END LOOP;
2141 END IF;
2142 IF c_get_Details_DN%ISOPEN THEN
2143 CLOSE c_get_Details_DN;
2144 END IF;
2145 IF c_get_del_det_interface_id%ISOPEN THEN
2146 CLOSE c_get_del_det_interface_id;
2147 END IF;
2148 IF l_debug_on THEN
2149 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
2150 END IF;
2151 rollback;
2152 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2153 IF l_debug_on THEN
2154 WSH_DEBUG_SV.log(l_module_name, 'Error occured while updating shipment request with document number and revision',p_shipment_request_info.document_number||' and '||p_shipment_request_info.document_revision);
2155 WSH_DEBUG_SV.pop(l_module_name,'FND_API.G_EXC_ERROR');
2156 END IF;
2157
2158 WHEN others THEN
2159 IF c_get_Details_DN%ISOPEN THEN
2160 CLOSE c_get_Details_DN;
2161 END IF;
2162 IF l_debug_on THEN
2163 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
2164 END IF;
2165 rollback;
2166 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2167 IF l_debug_on THEN
2168 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2169 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2170 END IF;
2171
2172 END Update_Delete_Shipment_Request;
2173
2174 END WSH_SHIPMENT_REQUEST_PUB;