63: IF l_debug_on THEN
64: WSH_DEBUG_SV.push(l_module_name);
65: END IF;
66:
67: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
68: x_selected_del_tab.delete;
69:
70: l_sc_SELECT := l_sc_SELECT || ' wnd.delivery_id , wnd.organization_id, wnd.initial_pickup_location_id ';
71:
116: l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.container_flag = ''N'' ';
117:
118: IF p_input_info.organization_id IS NOT NULL THEN
119: l_sc_WHERE := l_sc_WHERE ||'AND wnd.organization_id = :x_organization_id ';
120: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_input_info.organization_id);
121: p_batch_rec.organization_id := p_input_info.organization_id;
122: END IF;
123:
124: IF p_input_info.pr_batch_id IS NOT NULL THEN
130: ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
131: l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND (wdd2.released_status in (''S'', ''C'' , ''N'') OR NVL(wdd2.batch_id, -999) <> :x_pr_batch_id ) ';
132: END IF;
133:
134: WSH_UTIL_CORE.PrintMsg(' Pick Release Batch ID: '|| p_input_info.pr_batch_id);
135: p_batch_rec.selected_batch_id := p_input_info.pr_batch_id;
136:
137: ELSE
138:
146:
147: /*Modified R12.1.1 LSP PROJECT*/
148: IF p_input_info.client_id IS NOT NULL THEN
149: l_sc_WHERE := l_sc_WHERE || 'AND wnd.client_id = :x_client_id ';
150: WSH_UTIL_CORE.PrintMsg(' Client ID: '|| p_input_info.client_id);
151: p_batch_rec.client_id := p_input_info.client_id; -- Assign to OUT Parameter
152: END IF;
153: /*Modified R12.1.1 LSP PROJECT*/
154:
153: /*Modified R12.1.1 LSP PROJECT*/
154:
155: IF p_input_info.ap_batch_id IS NOT NULL THEN
156: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ap_batch_id = :x_ap_batch_id ';
157: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch ID: '|| p_input_info.ap_batch_id);
158: END IF;
159:
160: IF p_input_info.delivery_name_lo IS NOT NULL OR p_input_info.delivery_name_hi IS NOT NULL THEN
161: IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
159:
160: IF p_input_info.delivery_name_lo IS NOT NULL OR p_input_info.delivery_name_hi IS NOT NULL THEN
161: IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
162: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name BETWEEN :x_delivery_name_lo AND :x_delivery_name_hi ';
163: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
164: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
165: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
166: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
167: ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
161: IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
162: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name BETWEEN :x_delivery_name_lo AND :x_delivery_name_hi ';
163: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
164: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
165: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
166: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
167: ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
168: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name >= :x_delivery_name_lo ';
169: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
165: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
166: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
167: ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
168: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name >= :x_delivery_name_lo ';
169: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
170: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
171: ELSE
172: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name <= :x_delivery_name_hi ';
173: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
169: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
170: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
171: ELSE
172: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name <= :x_delivery_name_hi ';
173: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
174: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
175: END IF;
176: END IF;
177:
187:
188: IF p_input_info.bol_number_lo IS NOT NULL AND p_input_info.bol_number_hi IS NOT NULL THEN
189: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number BETWEEN :x_bol_number_lo AND :x_bol_number_hi ';
190:
191: WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
192: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
193:
194: WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
195: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
190:
191: WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
192: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
193:
194: WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
195: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
196:
197: ELSIF p_input_info.bol_number_lo IS NOT NULL THEN
198: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number >= :x_bol_number_lo ';
195: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
196:
197: ELSIF p_input_info.bol_number_lo IS NOT NULL THEN
198: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number >= :x_bol_number_lo ';
199: WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
200: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
201:
202: ELSE
203: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number <= :x_bol_number_hi ';
200: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
201:
202: ELSE
203: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number <= :x_bol_number_hi ';
204: WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
205: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
206: END IF;
207:
208: END IF;
209:
210:
211: IF p_input_info.planned_flag IS NOT NULL THEN
212: l_sc_WHERE := l_sc_WHERE || 'AND wnd.planned_flag = :x_planned_flag ';
213: WSH_UTIL_CORE.PrintMsg(' Planned Flag: '|| p_input_info.planned_flag);
214: p_batch_rec.planned_flag := p_input_info.planned_flag;
215: END IF;
216:
217: IF p_input_info.ship_from_loc_id IS NOT NULL THEN
215: END IF;
216:
217: IF p_input_info.ship_from_loc_id IS NOT NULL THEN
218: l_sc_WHERE := l_sc_WHERE || 'AND wnd.initial_pickup_location_id = :x_ship_from_loc_id ';
219: WSH_UTIL_CORE.PrintMsg(' Ship from Location ID: '|| to_char(p_input_info.ship_from_loc_id));
220: p_batch_rec.ship_from_location_id := p_input_info.ship_from_loc_id;
221: END IF;
222:
223: IF p_input_info.ship_to_loc_id IS NOT NULL THEN
221: END IF;
222:
223: IF p_input_info.ship_to_loc_id IS NOT NULL THEN
224: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_location_id = :x_ship_to_loc_id ';
225: WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_input_info.ship_to_loc_id));
226: p_batch_rec.ship_to_location_id := p_input_info.ship_to_loc_id;
227: END IF;
228:
229: IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
227: END IF;
228:
229: IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
230: l_sc_WHERE := l_sc_WHERE || 'AND wnd.intmed_ship_to_location_id = :x_intmed_ship_to_loc_id ';
231: WSH_UTIL_CORE.PrintMsg(' Intermediate Ship to Location ID: '|| to_char(p_input_info.intmed_ship_to_loc_id));
232: p_batch_rec.intmed_ship_to_loc_id := p_input_info.intmed_ship_to_loc_id;
233: END IF;
234:
235: IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
233: END IF;
234:
235: IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
236: l_sc_WHERE := l_sc_WHERE || 'AND wnd.pooled_ship_to_location_id = :x_pooled_ship_to_loc_id ';
237: WSH_UTIL_CORE.PrintMsg(' Pooled Ship to Location ID: '|| p_input_info.pooled_ship_to_loc_id);
238: p_batch_rec.pooled_ship_to_loc_id := p_input_info.pooled_ship_to_loc_id;
239: END IF;
240:
241: IF p_input_info.customer_id IS NOT NULL THEN
240:
241: IF p_input_info.customer_id IS NOT NULL THEN
242: l_sc_WHERE := l_sc_WHERE || 'AND wnd.customer_id = :x_customer_id ';
243: -- RTV Changes
244: WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| SubStr(p_input_info.customer_id,3));
245: p_batch_rec.customer_id := SubStr(p_input_info.customer_id,3);
246: END IF;
247:
248: IF p_input_info.ship_method_code IS NOT NULL THEN
246: END IF;
247:
248: IF p_input_info.ship_method_code IS NOT NULL THEN
249: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ship_method_code = :x_ship_method_code ';
250: WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_input_info.ship_method_code);
251: p_batch_rec.ship_method_code := p_input_info.ship_method_code;
252: END IF;
253:
254: IF p_input_info.fob_code IS NOT NULL THEN
252: END IF;
253:
254: IF p_input_info.fob_code IS NOT NULL THEN
255: l_sc_WHERE := l_sc_WHERE || 'AND wnd.fob_code = :x_fob_code ';
256: WSH_UTIL_CORE.PrintMsg(' FOB Code: '|| p_input_info.fob_code);
257: p_batch_rec.fob_code := p_input_info.fob_code;
258: END IF;
259:
260: IF p_input_info.freight_terms_code IS NOT NULL THEN
259:
260: IF p_input_info.freight_terms_code IS NOT NULL THEN
261: l_sc_WHERE := l_sc_WHERE || 'AND wnd.freight_terms_code = :x_freight_terms_code ';
262:
263: WSH_UTIL_CORE.PrintMsg(' Freight Term Code: '|| p_input_info.freight_terms_code);
264: p_batch_rec.freight_terms_code := p_input_info.freight_terms_code;
265: END IF;
266:
267: IF p_input_info.pickup_date_lo IS NOT NULL OR p_input_info.pickup_date_hi IS NOT NULL THEN
269:
270: l_pickup_date_lo := fnd_date.canonical_to_date(p_input_info.pickup_date_lo);
271: l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
272: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) BETWEEN :x_pickup_date_lo AND :x_pickup_date_hi ';
273: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
274: p_batch_rec.pickup_date_lo := l_pickup_date_lo;
275: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
276: p_batch_rec.pickup_date_hi := l_pickup_date_hi;
277:
271: l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
272: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) BETWEEN :x_pickup_date_lo AND :x_pickup_date_hi ';
273: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
274: p_batch_rec.pickup_date_lo := l_pickup_date_lo;
275: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
276: p_batch_rec.pickup_date_hi := l_pickup_date_hi;
277:
278: ELSIF p_input_info.pickup_date_lo IS NOT NULL THEN
279:
278: ELSIF p_input_info.pickup_date_lo IS NOT NULL THEN
279:
280: l_pickup_date_lo := fnd_date.canonical_to_date(p_input_info.pickup_date_lo);
281: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) >= :x_pickup_date_lo ';
282: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
283: p_batch_rec.pickup_date_lo := l_pickup_date_lo;
284:
285: ELSE
286:
285: ELSE
286:
287: l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
288: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) <= :x_pickup_date_hi ';
289: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
290: p_batch_rec.pickup_date_hi := l_pickup_date_hi;
291: END IF;
292: END IF;
293:
296: IF p_input_info.dropoff_date_lo IS NOT NULL AND p_input_info.dropoff_date_hi IS NOT NULL THEN
297: l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
298: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
299: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date BETWEEN :x_dropoff_date_lo AND :x_dropoff_date_hi ';
300: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
301: p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
302: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
303: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
304:
298: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
299: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date BETWEEN :x_dropoff_date_lo AND :x_dropoff_date_hi ';
300: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
301: p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
302: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
303: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
304:
305: ELSIF p_input_info.dropoff_date_lo IS NOT NULL THEN
306: l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
304:
305: ELSIF p_input_info.dropoff_date_lo IS NOT NULL THEN
306: l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
307: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date >= :x_dropoff_date_lo ';
308: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
309: p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
310:
311: ELSE
312: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
310:
311: ELSE
312: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
313: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date <= :x_dropoff_date_hi ';
314: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
315: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
316: END IF;
317: END IF;
318:
315: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
316: END IF;
317: END IF;
318:
319: WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_input_info.log_level);
320:
321: l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
322: IF length(l_sc_EXISTS_BOL) > 0 THEN
323: l_sc_FINAL := l_sc_FINAL ||'AND EXISTS ( '|| l_sc_EXISTS_BOL || ' ) ';
333: IF i > l_str_length THEN
334: EXIT;
335: END IF;
336: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
337: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
338: WSH_UTIL_CORE.PrintMsg(l_sub_str);
339: i := i + 80;
340: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
341: END LOOP;
334: EXIT;
335: END IF;
336: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
337: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
338: WSH_UTIL_CORE.PrintMsg(l_sub_str);
339: i := i + 80;
340: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
341: END LOOP;
342: END IF;
336: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
337: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
338: WSH_UTIL_CORE.PrintMsg(l_sub_str);
339: i := i + 80;
340: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
341: END LOOP;
342: END IF;
343:
344: v_CursorID := DBMS_SQL.Open_Cursor;
462: EXCEPTION
463:
464: WHEN others THEN
465:
466: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
467: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
468: IF l_debug_on THEN
469: WSH_DEBUG_SV.pop(l_module_name);
470: END IF;
463:
464: WHEN others THEN
465:
466: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
467: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
468: IF l_debug_on THEN
469: WSH_DEBUG_SV.pop(l_module_name);
470: END IF;
471:
536:
537: l_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
538:
539: l_delivery_rec get_delivery%ROWTYPE;
540: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
541: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
542: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
543: l_actual_dep_date DATE ;
544: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
537: l_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
538:
539: l_delivery_rec get_delivery%ROWTYPE;
540: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
541: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
542: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
543: l_actual_dep_date DATE ;
544: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
545: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
538:
539: l_delivery_rec get_delivery%ROWTYPE;
540: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
541: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
542: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
543: l_actual_dep_date DATE ;
544: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
545: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
546: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
566: IF l_debug_on THEN
567: WSH_DEBUG_SV.push(l_module_name);
568: END IF;
569:
570: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
571:
572: l_tmp_del_tab.delete;
573: l_tmp_del_tab(1) := p_delivery_id;
574:
665: x_msg_data => l_msg_data
666: );
667:
668:
669: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
670: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
671: /* error or unexpected error */
672: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673: ROLLBACK TO beginning_of_loop;
666: );
667:
668:
669: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
670: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
671: /* error or unexpected error */
672: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673: ROLLBACK TO beginning_of_loop;
674: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
668:
669: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
670: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
671: /* error or unexpected error */
672: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673: ROLLBACK TO beginning_of_loop;
674: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
675: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
676: END IF;
670: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
671: /* error or unexpected error */
672: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673: ROLLBACK TO beginning_of_loop;
674: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
675: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
676: END IF;
677:
678:
671: /* error or unexpected error */
672: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673: ROLLBACK TO beginning_of_loop;
674: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
675: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
676: END IF;
677:
678:
679: ELSE
676: END IF;
677:
678:
679: ELSE
680: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
681: WSH_UTIL_CORE.PrintMsg('Delivery '|| p_delivery_id||' not found or cannot be locked');
682: /* cannot lock the delivery */
683: END IF; -- if delivery exist
684:
677:
678:
679: ELSE
680: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
681: WSH_UTIL_CORE.PrintMsg('Delivery '|| p_delivery_id||' not found or cannot be locked');
682: /* cannot lock the delivery */
683: END IF; -- if delivery exist
684:
685: CLOSE get_delivery; /* unlock the delivery */
690: EXCEPTION
691:
692:
693: WHEN delivery_locked THEN
694: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
695: IF get_delivery%ISOPEN THEN
696: CLOSE get_delivery;
697: END IF;
698: IF c_first_trip_ship_method%ISOPEN THEN
697: END IF;
698: IF c_first_trip_ship_method%ISOPEN THEN
699: CLOSE c_first_trip_ship_method;
700: END IF;
701: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID '||to_char(p_delivery_id));
702: IF l_debug_on THEN
703: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock delivery ID '||to_char(p_delivery_id));
704: WSH_DEBUG_SV.POP(l_module_name);
705: END IF;
704: WSH_DEBUG_SV.POP(l_module_name);
705: END IF;
706:
707: WHEN others THEN
708: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
709: IF get_delivery%ISOPEN THEN
710: CLOSE get_delivery;
711: END IF;
712: IF c_first_trip_ship_method%ISOPEN THEN
711: END IF;
712: IF c_first_trip_ship_method%ISOPEN THEN
713: CLOSE c_first_trip_ship_method;
714: END IF;
715: WSH_UTIL_CORE.PrintMsg('ERROR: unhandled exception');
716: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_A_Delivery');
717: IF l_debug_on THEN
718: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: unhandled exception');
719: WSH_DEBUG_SV.POP(l_module_name);
712: IF c_first_trip_ship_method%ISOPEN THEN
713: CLOSE c_first_trip_ship_method;
714: END IF;
715: WSH_UTIL_CORE.PrintMsg('ERROR: unhandled exception');
716: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_A_Delivery');
717: IF l_debug_on THEN
718: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: unhandled exception');
719: WSH_DEBUG_SV.POP(l_module_name);
720: END IF;
757: c.trip_id = b.trip_id AND
758: a.stop_id <> C.stop_id
759: FOR UPDATE NOWAIT;
760:
761: l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
762: l_self_stop_id NUMBER;
763: l_other_stop_id NUMBER;
764: l_trip_id NUMBER;
765: l_return_status VARCHAR2(1);
789: WSH_DEBUG_SV.push(l_module_name);
790: WSH_DEBUG_SV.log(l_module_name, 'Locking stop '|| to_char(p_stop_id));
791: END IF;
792:
793: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
794: OPEN lock_stop_trip(p_stop_id);
795: FETCH lock_stop_trip INTO l_self_stop_id, l_trip_id , l_other_stop_id;
796: IF lock_stop_trip%FOUND THEN
797:
825: x_msg_data => l_msg_data);
826: x_return_status := l_return_status;
827:
828: CLOSE lock_stop_trip;
829: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
830: x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
831:
832: ROLLBACK TO beginning_of_the_procedure;
833: END IF;
826: x_return_status := l_return_status;
827:
828: CLOSE lock_stop_trip;
829: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
830: x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
831:
832: ROLLBACK TO beginning_of_the_procedure;
833: END IF;
834: ELSE
842: END IF;
843:
844: EXCEPTION
845: WHEN stop_trip_locked THEN
846: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
847: CLOSE lock_stop_trip ;
848: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
849: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
850: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
844: EXCEPTION
845: WHEN stop_trip_locked THEN
846: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
847: CLOSE lock_stop_trip ;
848: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
849: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
850: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
851: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
852: IF l_debug_on THEN
847: CLOSE lock_stop_trip ;
848: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
849: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
850: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
851: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
852: IF l_debug_on THEN
853: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
854: WSH_DEBUG_SV.POP(l_module_name);
855: END IF;
854: WSH_DEBUG_SV.POP(l_module_name);
855: END IF;
856:
857: WHEN others THEN
858: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
859: CLOSE lock_stop_trip;
860: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
861: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
862: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
856:
857: WHEN others THEN
858: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
859: CLOSE lock_stop_trip;
860: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
861: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
862: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
863: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
864: IF l_debug_on THEN
859: CLOSE lock_stop_trip;
860: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
861: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
862: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
863: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
864: IF l_debug_on THEN
865: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
866: WSH_DEBUG_SV.POP(l_module_name);
867: END IF;
893: PROCEDURE Ship_Confirm_Batch(
894: p_del_tab IN WSH_BATCH_PROCESS.Del_Info_Tab,
895: p_sc_batch_id IN NUMBER,
896: p_log_level IN NUMBER,
897: x_confirmed_del_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
898: x_results_summary OUT NOCOPY WSH_BATCH_PROCESS.Results_Summary_Rec,
899: x_return_status OUT NOCOPY VARCHAR2,
900: p_commit IN VARCHAR2) IS -- BugFix #4001135
901:
971:
972:
973:
974: l_ship_confirm_rule_rec G_GET_SHIP_CONFIRM_RULE%ROWTYPE;
975: l_sc_confirmed_dels wsh_util_core.id_tab_type;
976: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
977: l_ship_confirm_rule_id NUMBER := 0;
978: l_trip_id NUMBER := NULL;
979: l_stop_id NUMBER := NULL;
972:
973:
974: l_ship_confirm_rule_rec G_GET_SHIP_CONFIRM_RULE%ROWTYPE;
975: l_sc_confirmed_dels wsh_util_core.id_tab_type;
976: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
977: l_ship_confirm_rule_id NUMBER := 0;
978: l_trip_id NUMBER := NULL;
979: l_stop_id NUMBER := NULL;
980: l_stop_sequence_number NUMBER := NULL;
978: l_trip_id NUMBER := NULL;
979: l_stop_id NUMBER := NULL;
980: l_stop_sequence_number NUMBER := NULL;
981: l_stop_location_id NUMBER := NULL;
982: l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
983: l_stop_location_ids WSH_UTIL_CORE.Id_Tab_Type;
984: l_request_id NUMBER := 0;
985: l_num_warn NUMBER := 0;
986: l_num_error NUMBER := 0;
979: l_stop_id NUMBER := NULL;
980: l_stop_sequence_number NUMBER := NULL;
981: l_stop_location_id NUMBER := NULL;
982: l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
983: l_stop_location_ids WSH_UTIL_CORE.Id_Tab_Type;
984: l_request_id NUMBER := 0;
985: l_num_warn NUMBER := 0;
986: l_num_error NUMBER := 0;
987: l_actual_dep_date DATE := NULL;
1005: --
1006: --Bugfix 4070732
1007: l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1008: l_reset_flags BOOLEAN;
1009: l_return_status1 VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1010: --
1011: l_actual_departure_date DATE;
1012: --
1013: BEGIN
1024: x_results_summary.success := 0;
1025: x_results_summary.warning := 0;
1026: x_results_summary.failure := 0;
1027: x_results_summary.report_req_id := 0;
1028: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1029:
1030: IF l_debug_on THEN
1031: WSH_DEBUG_SV.push(l_module_name);
1032: END IF;
1034: l_sc_confirmed_dels.delete;
1035: l_stops_to_close.delete;
1036: l_stop_location_ids.delete;
1037:
1038: WSH_UTIL_CORE.PrintDateTime;
1039:
1040: -- lock ship confirm batch
1041: OPEN get_sc_batch ;
1042: FETCH get_sc_batch INTO l_ship_confirm_rule_id, l_batch_creation_date,
1080:
1081: EXCEPTION
1082:
1083: WHEN delivery_locked THEN
1084: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1085: WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1086: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1087: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1088: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1081: EXCEPTION
1082:
1083: WHEN delivery_locked THEN
1084: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1085: WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1086: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1087: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1088: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1089: log_batch_messages(p_sc_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1084: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1085: WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1086: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1087: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1088: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1089: log_batch_messages(p_sc_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1090: IF l_debug_on THEN
1091: WSH_DEBUG_SV.logmsg('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery', 'ERROR: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1092: WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery');
1093: END IF;
1094:
1095: WHEN OTHERS THEN
1096: /* this will catch the exception when failing to obtain the lock on the delivery */
1097: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1098: WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1099: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1100: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1101: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1094:
1095: WHEN OTHERS THEN
1096: /* this will catch the exception when failing to obtain the lock on the delivery */
1097: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1098: WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1099: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1100: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1101: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1102: IF l_debug_on THEN
1097: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1098: WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1099: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1100: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1101: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1102: IF l_debug_on THEN
1103: WSH_DEBUG_SV.logmsg('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery', 'ERROR: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1104: WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery');
1105: END IF;
1106: END;
1107:
1108: --
1109: --bug 4070732
1110: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1111: AND p_commit = FND_API.G_TRUE ) THEN
1112: --{
1113: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1114: --{
1109: --bug 4070732
1110: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1111: AND p_commit = FND_API.G_TRUE ) THEN
1112: --{
1113: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1114: --{
1115:
1116: l_reset_flags := FALSE;
1117:
1115:
1116: l_reset_flags := FALSE;
1117:
1118: IF l_debug_on THEN
1119: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1120: END IF;
1121:
1122: BEGIN
1123:
1120: END IF;
1121:
1122: BEGIN
1123:
1124: WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1125: x_return_status => l_return_status1);
1126: IF l_debug_on THEN
1127: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1128: END IF;
1126: IF l_debug_on THEN
1127: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1128: END IF;
1129:
1130: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1131: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1132: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1133: l_return_status := l_return_status1;
1134: END IF;
1127: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1128: END IF;
1129:
1130: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1131: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1132: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1133: l_return_status := l_return_status1;
1134: END IF;
1135: EXCEPTION
1128: END IF;
1129:
1130: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1131: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1132: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1133: l_return_status := l_return_status1;
1134: END IF;
1135: EXCEPTION
1136: WHEN others THEN
1133: l_return_status := l_return_status1;
1134: END IF;
1135: EXCEPTION
1136: WHEN others THEN
1137: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1138: END;
1139:
1140: --}
1141: END IF;
1142: --}
1143: END IF;
1144: --bug 4070732
1145: --
1146: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1147: x_results_summary.success := x_results_summary.success + 1;
1148: l_sc_confirmed_dels(l_sc_confirmed_dels.count+1):= p_del_tab(i).delivery_id;
1149: IF l_debug_on THEN
1150: select status_code into l_status_code from wsh_new_deliveries where delivery_id = p_del_tab(i).delivery_id;
1151: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID ' || to_char(p_del_tab(i).delivery_id)||' is ship confirmed successfully with status '|| l_status_code);
1152: END IF;
1153:
1154:
1155: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1156: x_results_summary.warning := x_results_summary.warning + 1;
1157: l_sc_confirmed_dels(l_sc_confirmed_dels.count+1):= p_del_tab(i).delivery_id;
1158: log_batch_messages(p_sc_batch_id, p_del_tab(i).delivery_id, NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1159: IF l_debug_on THEN
1229:
1230: EXCEPTION
1231: WHEN others THEN
1232: /* this will catch the exeption when stop and trip cannot be locked */
1233: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1234: l_lock_error := 'Y';
1235:
1236: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1237: IF l_debug_on THEN
1232: /* this will catch the exeption when stop and trip cannot be locked */
1233: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1234: l_lock_error := 'Y';
1235:
1236: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1237: IF l_debug_on THEN
1238: WSH_DEBUG_SV.logmsg('wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop', 'ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1239: WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop');
1240: END IF;
1241: END;
1242:
1243: --
1244: --bug 4070732
1245: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1246: AND p_commit = FND_API.G_TRUE ) THEN
1247: --{
1248: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1249: --{
1244: --bug 4070732
1245: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1246: AND p_commit = FND_API.G_TRUE ) THEN
1247: --{
1248: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1249: --{
1250:
1251: l_reset_flags := FALSE;
1252:
1250:
1251: l_reset_flags := FALSE;
1252:
1253: IF l_debug_on THEN
1254: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1255: END IF;
1256:
1257: BEGIN
1258:
1255: END IF;
1256:
1257: BEGIN
1258:
1259: WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1260: x_return_status => l_return_status1);
1261: IF l_debug_on THEN
1262: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1263: END IF;
1261: IF l_debug_on THEN
1262: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1263: END IF;
1264:
1265: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1266: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1267: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1268: l_return_status := l_return_status1;
1269: END IF;
1262: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1263: END IF;
1264:
1265: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1266: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1267: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1268: l_return_status := l_return_status1;
1269: END IF;
1270: EXCEPTION
1263: END IF;
1264:
1265: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1266: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1267: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1268: l_return_status := l_return_status1;
1269: END IF;
1270: EXCEPTION
1271: WHEN others THEN
1268: l_return_status := l_return_status1;
1269: END IF;
1270: EXCEPTION
1271: WHEN others THEN
1272: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1273: END;
1274:
1275: --}
1276: END IF;
1277: --}
1278: END IF;
1279: --bug 4070732
1280:
1281: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1282:
1283: IF l_debug_on THEN
1284: WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed stop '|| l_stops_to_close(i));
1285: END IF;
1283: IF l_debug_on THEN
1284: WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed stop '|| l_stops_to_close(i));
1285: END IF;
1286: l_closing_stop_success := l_closing_stop_success + 1;
1287: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1288: IF l_debug_on THEN
1289: WSH_DEBUG_SV.logmsg(l_module_name,'Trip stop '|| l_stops_to_close(i) ||' is closed with warnings');
1290: END IF;
1291: l_closing_stop_warning := l_closing_stop_warning + 1;
1297:
1298: IF l_lock_error = 'Y' THEN
1299: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
1300: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(l_stops_to_close(i)));
1301: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1302: END IF;
1303: l_closing_stop_failure := l_closing_stop_failure + 1;
1304: log_batch_messages(p_sc_batch_id, NULL , l_stops_to_close(i) , l_stop_location_ids(i), NULL);
1305: END IF;
1336: 'ALL', '', '', 0, p_sc_batch_id);
1337: IF (l_request_id = 0) THEN
1338: raise inv_inter_req_submission;
1339: ELSE
1340: WSH_UTIL_CORE.PrintMsg('Interface request submitted for closed stops, request ID: '
1341: || to_char(l_request_id) );
1342: END IF;
1343:
1344: END IF;
1375:
1376: EXCEPTION
1377:
1378: WHEN wsh_missing_sc_batch THEN
1379: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1380: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to find the ship confirm batch ');
1381:
1382: IF l_debug_on THEN
1383: WSH_DEBUG_SV.pop(l_module_name);
1376: EXCEPTION
1377:
1378: WHEN wsh_missing_sc_batch THEN
1379: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1380: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to find the ship confirm batch ');
1381:
1382: IF l_debug_on THEN
1383: WSH_DEBUG_SV.pop(l_module_name);
1384: END IF;
1386: WHEN wsh_missing_sc_rule THEN
1387: IF get_sc_batch%ISOPEN THEN
1388: CLOSE get_sc_batch;
1389: END IF;
1390: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1391: WSH_UTIL_CORE.PrintMsg('ERROR: Ship Confirm Rule is not found or has expired');
1392: IF l_debug_on THEN
1393: WSH_DEBUG_SV.pop(l_module_name);
1394: END IF;
1387: IF get_sc_batch%ISOPEN THEN
1388: CLOSE get_sc_batch;
1389: END IF;
1390: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1391: WSH_UTIL_CORE.PrintMsg('ERROR: Ship Confirm Rule is not found or has expired');
1392: IF l_debug_on THEN
1393: WSH_DEBUG_SV.pop(l_module_name);
1394: END IF;
1395:
1399: END IF;
1400: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1401: CLOSE G_GET_SHIP_CONFIRM_RULE;
1402: END IF;
1403: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1404: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Interface concurrent request ');
1405: IF l_debug_on THEN
1406: WSH_DEBUG_SV.pop(l_module_name);
1407: END IF;
1400: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1401: CLOSE G_GET_SHIP_CONFIRM_RULE;
1402: END IF;
1403: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1404: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Interface concurrent request ');
1405: IF l_debug_on THEN
1406: WSH_DEBUG_SV.pop(l_module_name);
1407: END IF;
1408:
1412: END IF;
1413: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1414: CLOSE G_GET_SHIP_CONFIRM_RULE;
1415: END IF;
1416: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1417: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Auto Ship Confirm Report ');
1418: IF l_debug_on THEN
1419: WSH_DEBUG_SV.pop(l_module_name);
1420: END IF;
1413: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1414: CLOSE G_GET_SHIP_CONFIRM_RULE;
1415: END IF;
1416: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1417: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Auto Ship Confirm Report ');
1418: IF l_debug_on THEN
1419: WSH_DEBUG_SV.pop(l_module_name);
1420: END IF;
1421:
1425: END IF;
1426: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1427: CLOSE G_GET_SHIP_CONFIRM_RULE;
1428: END IF;
1429: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1430: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID ');
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.pop(l_module_name);
1433: END IF;
1426: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1427: CLOSE G_GET_SHIP_CONFIRM_RULE;
1428: END IF;
1429: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1430: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID ');
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.pop(l_module_name);
1433: END IF;
1434:
1438: END IF;
1439: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1440: CLOSE G_GET_SHIP_CONFIRM_RULE;
1441: END IF;
1442: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_Batch');
1443: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1444: IF l_debug_on THEN
1445: WSH_DEBUG_SV.pop(l_module_name);
1446: END IF;
1439: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1440: CLOSE G_GET_SHIP_CONFIRM_RULE;
1441: END IF;
1442: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_Batch');
1443: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1444: IF l_debug_on THEN
1445: WSH_DEBUG_SV.pop(l_module_name);
1446: END IF;
1447:
1491: NVL(auto_ap_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
1492:
1493:
1494: l_delivery_rec get_delivery%ROWTYPE;
1495: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1496: l_action VARCHAR2(30) := NULL;
1497: l_pack_cont_flag VARCHAR2(1) := NULL;
1498: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1499: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1494: l_delivery_rec get_delivery%ROWTYPE;
1495: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1496: l_action VARCHAR2(30) := NULL;
1497: l_pack_cont_flag VARCHAR2(1) := NULL;
1498: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1499: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1500: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1501: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1502: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1495: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1496: l_action VARCHAR2(30) := NULL;
1497: l_pack_cont_flag VARCHAR2(1) := NULL;
1498: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1499: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1500: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1501: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1502: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1503: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1496: l_action VARCHAR2(30) := NULL;
1497: l_pack_cont_flag VARCHAR2(1) := NULL;
1498: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1499: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1500: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1501: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1502: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1503: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1504: l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
1528:
1529: l_tmp_del_tab.delete;
1530: l_tmp_del_tab(1) := p_delivery_id;
1531:
1532: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1533:
1534: IF p_auto_pack_level = 1 THEN
1535: l_action := 'AUTO-PACK';
1536: l_pack_cont_flag := 'N';
1583: x_msg_count => l_msg_count,
1584: x_msg_data => l_msg_data
1585: );
1586:
1587: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1588: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1589: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1590: ROLLBACK TO beginning_of_loop;
1591: ELSE
1584: x_msg_data => l_msg_data
1585: );
1586:
1587: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1588: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1589: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1590: ROLLBACK TO beginning_of_loop;
1591: ELSE
1592: x_return_status := l_return_status;
1585: );
1586:
1587: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1588: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1589: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1590: ROLLBACK TO beginning_of_loop;
1591: ELSE
1592: x_return_status := l_return_status;
1593: END IF;
1592: x_return_status := l_return_status;
1593: END IF;
1594:
1595: ELSE /* cannot lock the delivery */
1596: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1597: END IF; -- if delivery exist
1598:
1599: CLOSE get_delivery;
1600: IF l_debug_on THEN
1603:
1604: EXCEPTION
1605:
1606: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1607: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1608: WSH_UTIL_CORE.PrintMsg('ERROR: invalid auto pack level');
1609: IF l_debug_on THEN
1610: WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Auot Pack Level');
1611: WSH_DEBUG_SV.pop(l_module_name);
1604: EXCEPTION
1605:
1606: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1607: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1608: WSH_UTIL_CORE.PrintMsg('ERROR: invalid auto pack level');
1609: IF l_debug_on THEN
1610: WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Auot Pack Level');
1611: WSH_DEBUG_SV.pop(l_module_name);
1612: END IF;
1612: END IF;
1613:
1614: WHEN delivery_locked THEN
1615: CLOSE get_delivery;
1616: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1617: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1618: IF l_debug_on THEN
1619: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1620: WSH_DEBUG_SV.pop(l_module_name);
1613:
1614: WHEN delivery_locked THEN
1615: CLOSE get_delivery;
1616: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1617: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1618: IF l_debug_on THEN
1619: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1620: WSH_DEBUG_SV.pop(l_module_name);
1621: END IF;
1623:
1624:
1625: WHEN OTHERS THEN
1626: CLOSE get_delivery;
1627: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1628: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1629: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1630: IF l_debug_on THEN
1631: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1624:
1625: WHEN OTHERS THEN
1626: CLOSE get_delivery;
1627: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1628: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1629: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1630: IF l_debug_on THEN
1631: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1632: WSH_DEBUG_SV.pop(l_module_name);
1625: WHEN OTHERS THEN
1626: CLOSE get_delivery;
1627: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1628: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1629: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1630: IF l_debug_on THEN
1631: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1632: WSH_DEBUG_SV.pop(l_module_name);
1633: END IF;
1666: x_results_summary OUT NOCOPY WSH_BATCH_PROCESS.Results_Summary_Rec,
1667: x_return_status OUT NOCOPY VARCHAR2,
1668: P_COMMIT IN VARCHAR2) IS -- BugFix #4001135
1669:
1670: l_ap_packed_dels wsh_util_core.id_tab_type;
1671: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1672: l_debug_on BOOLEAN;
1673: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_Batch';
1674: WSH_SUBMIT_AP_REPORT_ERR EXCEPTION;
1667: x_return_status OUT NOCOPY VARCHAR2,
1668: P_COMMIT IN VARCHAR2) IS -- BugFix #4001135
1669:
1670: l_ap_packed_dels wsh_util_core.id_tab_type;
1671: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1672: l_debug_on BOOLEAN;
1673: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_Batch';
1674: WSH_SUBMIT_AP_REPORT_ERR EXCEPTION;
1675: delivery_locked EXCEPTION;
1678:
1679: --Bugfix 4070732
1680: l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1681: l_reset_flags BOOLEAN;
1682: l_return_status1 VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1683:
1684:
1685: BEGIN
1686:
1696: x_results_summary.success := 0;
1697: x_results_summary.warning := 0;
1698: x_results_summary.failure := 0;
1699: x_results_summary.report_req_id := 0;
1700: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1701:
1702: x_packed_del_tab.delete;
1703:
1704: IF l_debug_on THEN
1705: WSH_DEBUG_SV.push(l_module_name);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'AP level'||p_auto_pack_level);
1707: END IF;
1708:
1709: WSH_UTIL_CORE.PrintDateTime;
1710:
1711: IF p_auto_pack_level <> 1 AND p_auto_pack_level <> 2 THEN
1712: RAISE WSH_INVALID_AUTO_PACK_LEVEL;
1713: END IF;
1729:
1730: EXCEPTION
1731:
1732: WHEN delivery_locked THEN
1733: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1734: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1735: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1736: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1737: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1730: EXCEPTION
1731:
1732: WHEN delivery_locked THEN
1733: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1734: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1735: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1736: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1737: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1738: log_batch_messages(p_ap_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1733: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1734: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1735: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1736: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1737: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1738: log_batch_messages(p_ap_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1739:
1740: IF l_debug_on THEN
1741: WSH_DEBUG_SV.logmsg(l_module_name, 'Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1742: WSH_DEBUG_SV.pop('wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery');
1743: END IF;
1744:
1745: WHEN OTHERS THEN
1746: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1747: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1748: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1749: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1750: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1743: END IF;
1744:
1745: WHEN OTHERS THEN
1746: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1747: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1748: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1749: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1750: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1751:
1746: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1747: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1748: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1749: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1750: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1751:
1752: IF l_debug_on THEN
1753: WSH_DEBUG_SV.logmsg(l_module_name, 'Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1754: WSH_DEBUG_SV.pop('wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery');
1758: END;
1759:
1760: --
1761: --bug 4070732
1762: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1763: AND p_commit = FND_API.G_TRUE ) THEN
1764: --{
1765: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1766: --{
1761: --bug 4070732
1762: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1763: AND p_commit = FND_API.G_TRUE ) THEN
1764: --{
1765: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1766: --{
1767:
1768: l_reset_flags := FALSE;
1769:
1767:
1768: l_reset_flags := FALSE;
1769:
1770: IF l_debug_on THEN
1771: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1772: END IF;
1773:
1774: BEGIN
1775:
1772: END IF;
1773:
1774: BEGIN
1775:
1776: WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1777: x_return_status => l_return_status1);
1778: IF l_debug_on THEN
1779: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1780: END IF;
1778: IF l_debug_on THEN
1779: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1780: END IF;
1781:
1782: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1783: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1784: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1785: l_return_status := l_return_status1;
1786: END IF;
1779: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1780: END IF;
1781:
1782: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1783: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1784: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1785: l_return_status := l_return_status1;
1786: END IF;
1787: EXCEPTION
1780: END IF;
1781:
1782: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1783: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1784: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1785: l_return_status := l_return_status1;
1786: END IF;
1787: EXCEPTION
1788: WHEN others THEN
1785: l_return_status := l_return_status1;
1786: END IF;
1787: EXCEPTION
1788: WHEN others THEN
1789: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1790: END;
1791:
1792: --}
1793: END IF;
1793: END IF;
1794: --}
1795: END IF;
1796: --bug 4070732
1797: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1798: x_results_summary.success := x_results_summary.success + 1;
1799: x_packed_del_tab(x_packed_del_tab.count+1).delivery_id := p_del_tab(i).delivery_id;
1800: x_packed_del_tab(x_packed_del_tab.count).organization_id := p_del_tab(i).organization_id;
1801: x_packed_del_tab(x_packed_del_tab.count).initial_pickup_location_id := p_del_tab(i).initial_pickup_location_id;
1803: IF l_debug_on THEN
1804: WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack succeed for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1805: END IF;
1806:
1807: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1808: x_results_summary.warning := x_results_summary.warning + 1;
1809: x_packed_del_tab(x_packed_del_tab.count+1).delivery_id := p_del_tab(i).delivery_id;
1810: x_packed_del_tab(x_packed_del_tab.count).organization_id := p_del_tab(i).organization_id;
1811: x_packed_del_tab(x_packed_del_tab.count).initial_pickup_location_id := p_del_tab(i).initial_pickup_location_id;
1865:
1866: EXCEPTION
1867:
1868: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1869: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1870: FND_MESSAGE.SET_NAME('WSH' , 'WSH_INVALID_AUTO_PACK_LEVEL');
1871: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1872: IF l_debug_on THEN
1873: WSH_DEBUG_SV.pop(l_module_name);
1867:
1868: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1869: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1870: FND_MESSAGE.SET_NAME('WSH' , 'WSH_INVALID_AUTO_PACK_LEVEL');
1871: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1872: IF l_debug_on THEN
1873: WSH_DEBUG_SV.pop(l_module_name);
1874: END IF;
1875:
1873: WSH_DEBUG_SV.pop(l_module_name);
1874: END IF;
1875:
1876: WHEN WSH_SUBMIT_AP_REPORT_ERR THEN
1877: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1878: FND_MESSAGE.SET_NAME('WSH' , 'WSH_SUBMIT_AP_REPORT_ERR');
1879: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1880: IF l_debug_on THEN
1881: WSH_DEBUG_SV.pop(l_module_name);
1875:
1876: WHEN WSH_SUBMIT_AP_REPORT_ERR THEN
1877: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1878: FND_MESSAGE.SET_NAME('WSH' , 'WSH_SUBMIT_AP_REPORT_ERR');
1879: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1880: IF l_debug_on THEN
1881: WSH_DEBUG_SV.pop(l_module_name);
1882: END IF;
1883:
1881: WSH_DEBUG_SV.pop(l_module_name);
1882: END IF;
1883:
1884: WHEN others THEN
1885: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_Deliveries_Batch');
1886: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1887: IF l_debug_on THEN
1888: WSH_DEBUG_SV.pop(l_module_name);
1889: END IF;
1882: END IF;
1883:
1884: WHEN others THEN
1885: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_Deliveries_Batch');
1886: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1887: IF l_debug_on THEN
1888: WSH_DEBUG_SV.pop(l_module_name);
1889: END IF;
1890:
1974: l_log_level NUMBER := 0;
1975: l_temp BOOLEAN;
1976: l_batch_rec WSH_PICKING_BATCHES%ROWTYPE;
1977: l_debug_on BOOLEAN;
1978: l_confirmed_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1979: l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
1980: l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
1981: l_results_summary WSH_BATCH_PROCESS.Results_Summary_Rec;
1982: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Confirm_Delivery_SRS';
1989:
1990: -- deliveryMerge
1991: Adjust_Planned_Flag_Err EXCEPTION;
1992: l_warning_num NUMBER := 0;
1993: l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
1994:
1995: --
1996: -- Bug 5097710
1997: --
2006: l_log_level := 0;
2007: ELSE
2008: l_log_level := p_log_level;
2009: END IF;
2010: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2011:
2012: --
2013: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2014: --
2026: -- Fetch user and login information
2027: l_user_id := FND_GLOBAL.USER_ID;
2028: l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2029:
2030: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2031:
2032: IF p_ship_confirm_rule_id IS NULL THEN
2033: raise WSH_MISSING_SC_RULE;
2034: END IF;
2036: IF p_sc_batch_prefix IS NULL THEN
2037: raise WSH_MISSING_SC_BATCH_PREFIX;
2038: END IF;
2039:
2040: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2041: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2042: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2043: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2044:
2037: raise WSH_MISSING_SC_BATCH_PREFIX;
2038: END IF;
2039:
2040: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2041: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2042: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2043: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2044:
2045:
2038: END IF;
2039:
2040: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2041: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2042: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2043: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2044:
2045:
2046: IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2039:
2040: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2041: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2042: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2043: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2044:
2045:
2046: IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2047: raise WSH_NO_FUTURE_SHIPDATE;
2042: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2043: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2044:
2045:
2046: IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2047: raise WSH_NO_FUTURE_SHIPDATE;
2048: END IF;
2049:
2050: l_select_criteria.process_mode := G_SHIP_CONFIRM;
2081: -- so I will pack them into record, and
2082: -- this is the record used to populate wsh_picking_batches
2083: -- otherwise i need to have a bunch of if stmt again to check the parameters
2084:
2085: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2086: raise WSH_SELECT_ERR;
2087: END IF;
2088:
2089: IF l_selected_del_tab.count > 0 THEN
2109: IF l_debug_on THEN
2110: WSH_DEBUG_SV.logmsg(l_module_name, 'Return status from Calling Adjust_Planned_Flag:'||l_return_status);
2111: END IF;
2112:
2113: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2114: l_warning_num := l_warning_num + 1;
2115: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2116: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2117: raise Adjust_Planned_Flag_Err;
2111: END IF;
2112:
2113: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2114: l_warning_num := l_warning_num + 1;
2115: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2116: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2117: raise Adjust_Planned_Flag_Err;
2118: END IF;
2119: commit;
2112:
2113: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2114: l_warning_num := l_warning_num + 1;
2115: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2116: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2117: raise Adjust_Planned_Flag_Err;
2118: END IF;
2119: commit;
2120:
2241: p_Selected_Batch_Id => l_batch_rec.selected_batch_id,
2242: p_client_id => l_batch_rec.client_id); -- Modified R12.1.1 LSP PROJECT
2243:
2244:
2245: WSH_UTIL_CORE.PrintMsg('Ship Confirm Batch Name: ' || l_batch_rec.name);
2246:
2247: IF l_debug_on THEN
2248: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be ship confirmed are: ');
2249: FOR k in 1 .. l_selected_del_tab.count LOOP
2259: x_results_summary => l_results_summary,
2260: x_return_status => l_return_status,
2261: p_commit => FND_API.G_TRUE); -- BugFix #4001135
2262:
2263: WSH_UTIL_CORE.PrintDateTime;
2264: WSH_UTIL_CORE.PrintMsg('Summary: ');
2265: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2260: x_return_status => l_return_status,
2261: p_commit => FND_API.G_TRUE); -- BugFix #4001135
2262:
2263: WSH_UTIL_CORE.PrintDateTime;
2264: WSH_UTIL_CORE.PrintMsg('Summary: ');
2265: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2261: p_commit => FND_API.G_TRUE); -- BugFix #4001135
2262:
2263: WSH_UTIL_CORE.PrintDateTime;
2264: WSH_UTIL_CORE.PrintMsg('Summary: ');
2265: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269:
2262:
2263: WSH_UTIL_CORE.PrintDateTime;
2264: WSH_UTIL_CORE.PrintMsg('Summary: ');
2265: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269:
2270: IF l_results_summary.report_req_id > 0 THEN
2263: WSH_UTIL_CORE.PrintDateTime;
2264: WSH_UTIL_CORE.PrintMsg('Summary: ');
2265: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269:
2270: IF l_results_summary.report_req_id > 0 THEN
2271: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2264: WSH_UTIL_CORE.PrintMsg('Summary: ');
2265: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269:
2270: IF l_results_summary.report_req_id > 0 THEN
2271: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2272: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2267: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269:
2270: IF l_results_summary.report_req_id > 0 THEN
2271: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2272: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2273: END IF;
2274:
2275: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2268: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269:
2270: IF l_results_summary.report_req_id > 0 THEN
2271: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2272: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2273: END IF;
2274:
2275: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2276: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2271: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2272: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2273: END IF;
2274:
2275: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2276: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2277: RAISE WSH_SC_BATCH_ERR;
2278: END IF;
2279:
2272: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2273: END IF;
2274:
2275: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2276: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2277: RAISE WSH_SC_BATCH_ERR;
2278: END IF;
2279:
2280: ELSE
2278: END IF;
2279:
2280: ELSE
2281: /* no deliveries selected */
2282: WSH_UTIL_CORE.PrintDateTime;
2283: WSH_UTIL_CORE.PrintMsg('Summary: ');
2284: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2285:
2286: END IF;
2279:
2280: ELSE
2281: /* no deliveries selected */
2282: WSH_UTIL_CORE.PrintDateTime;
2283: WSH_UTIL_CORE.PrintMsg('Summary: ');
2284: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2285:
2286: END IF;
2287:
2280: ELSE
2281: /* no deliveries selected */
2282: WSH_UTIL_CORE.PrintDateTime;
2283: WSH_UTIL_CORE.PrintMsg('Summary: ');
2284: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2285:
2286: END IF;
2287:
2288: errbuf := 'Automated Ship Confirm is completed successfully';
2295: EXCEPTION
2296:
2297: WHEN Adjust_Planned_Flag_Err THEN
2298: -- Bug 5097710
2299: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2300: WSH_UTIL_CORE.PrintMsg('Summary:');
2301: WSH_UTIL_CORE.PrintMsg(l_summary);
2302: WSH_UTIL_CORE.PrintMsg('Details:');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2296:
2297: WHEN Adjust_Planned_Flag_Err THEN
2298: -- Bug 5097710
2299: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2300: WSH_UTIL_CORE.PrintMsg('Summary:');
2301: WSH_UTIL_CORE.PrintMsg(l_summary);
2302: WSH_UTIL_CORE.PrintMsg('Details:');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2304: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2297: WHEN Adjust_Planned_Flag_Err THEN
2298: -- Bug 5097710
2299: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2300: WSH_UTIL_CORE.PrintMsg('Summary:');
2301: WSH_UTIL_CORE.PrintMsg(l_summary);
2302: WSH_UTIL_CORE.PrintMsg('Details:');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2304: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2305: --
2298: -- Bug 5097710
2299: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2300: WSH_UTIL_CORE.PrintMsg('Summary:');
2301: WSH_UTIL_CORE.PrintMsg(l_summary);
2302: WSH_UTIL_CORE.PrintMsg('Details:');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2304: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2305: --
2306: l_completion_status := 'WARNING';
2299: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2300: WSH_UTIL_CORE.PrintMsg('Summary:');
2301: WSH_UTIL_CORE.PrintMsg(l_summary);
2302: WSH_UTIL_CORE.PrintMsg('Details:');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2304: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2305: --
2306: l_completion_status := 'WARNING';
2307: WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2300: WSH_UTIL_CORE.PrintMsg('Summary:');
2301: WSH_UTIL_CORE.PrintMsg(l_summary);
2302: WSH_UTIL_CORE.PrintMsg('Details:');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2304: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2305: --
2306: l_completion_status := 'WARNING';
2307: WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2308: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2303: WSH_UTIL_CORE.PrintMsg(l_detail);
2304: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2305: --
2306: l_completion_status := 'WARNING';
2307: WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2308: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2309: errbuf := 'Automated Ship Confirm is completed with warning';
2310: retcode := '1';
2311: IF l_debug_on THEN
2313: END IF;
2314:
2315: WHEN WSH_MISSING_SC_RULE THEN
2316: l_completion_status := 'WARNING';
2317: WSH_UTIL_CORE.PrintMsg('Ship Confirm Rule is not found or has expired');
2318: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2319: errbuf := 'Automated Ship Confirm is completed with warning';
2320: retcode := '1';
2321: IF l_debug_on THEN
2323: END IF;
2324:
2325: WHEN WSH_MISSING_SC_BATCH_PREFIX THEN
2326: l_completion_status := 'WARNING';
2327: WSH_UTIL_CORE.PrintMsg('You need to specify Ship Confirm Batch Prefix');
2328: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2329: errbuf := 'Automated Ship Confirm is completed with warning';
2330: retcode := '1';
2331: IF l_debug_on THEN
2333: END IF;
2334:
2335: WHEN WSH_NO_FUTURE_SHIPDATE THEN
2336: l_completion_status := 'WARNING';
2337: WSH_UTIL_CORE.PrintMsg('No Lines were selected for Ship Confirmation because Allow Future Ship Date Parameter is disabled and Actual Ship Date is greater than current system date');
2338: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2339: errbuf := 'Automated Ship Confirm is completed with warning';
2340: retcode := '1';
2341: IF l_debug_on THEN
2343: END IF;
2344:
2345: WHEN WSH_SELECT_ERR THEN
2346: l_completion_status := 'WARNING';
2347: WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
2348: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2349: errbuf := 'Automated Ship Confirm is completed with warning';
2350: retcode := '1';
2351: IF l_debug_on THEN
2353: END IF;
2354:
2355: WHEN WSH_SC_BATCH_ERR THEN
2356: l_completion_status := 'WARNING';
2357: WSH_UTIL_CORE.PrintMsg('Ship Confirm failed for this batch');
2358: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2359: errbuf := 'Automated Ship Confirm is completed with warning';
2360: retcode := '1';
2361: IF l_debug_on THEN
2365: WHEN OTHERS THEN
2366: l_completion_status := 'ERROR';
2367: l_error_code := SQLCODE;
2368: l_error_text := SQLERRM;
2369: WSH_UTIL_CORE.PrintMsg('Confirm Delivery SRS failed with unexpected error.');
2370: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2371: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2372: errbuf := 'Automated Ship Confirm failed with unexpected error';
2373: retcode := '2';
2366: l_completion_status := 'ERROR';
2367: l_error_code := SQLCODE;
2368: l_error_text := SQLERRM;
2369: WSH_UTIL_CORE.PrintMsg('Confirm Delivery SRS failed with unexpected error.');
2370: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2371: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2372: errbuf := 'Automated Ship Confirm failed with unexpected error';
2373: retcode := '2';
2374: IF l_debug_on THEN
2499: ELSE
2500: l_log_level := p_log_level;
2501: END IF;
2502:
2503: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2504: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2505:
2506: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2507: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2500: l_log_level := p_log_level;
2501: END IF;
2502:
2503: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2504: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2505:
2506: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2507: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2508: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2502:
2503: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2504: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2505:
2506: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2507: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2508: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2509:
2510: l_select_criteria.process_mode := G_AUTO_PACK;
2503: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2504: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2505:
2506: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2507: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2508: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2509:
2510: l_select_criteria.process_mode := G_AUTO_PACK;
2511: l_select_criteria.organization_id := p_organization_id;
2504: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2505:
2506: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2507: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2508: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2509:
2510: l_select_criteria.process_mode := G_AUTO_PACK;
2511: l_select_criteria.organization_id := p_organization_id;
2512: l_select_criteria.pr_batch_id := p_pr_batch_id;
2534: p_batch_rec => l_batch_rec,
2535: x_selected_del_tab => l_selected_del_tab,
2536: x_return_status => l_return_status);
2537:
2538: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2539: raise WSH_SELECT_ERR;
2540: END IF;
2541:
2542: IF l_selected_del_tab.count > 0 THEN
2651: p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
2652: p_Planned_Flag => l_batch_rec.planned_flag,
2653: p_Selected_Batch_Id => l_batch_rec.selected_batch_id);
2654:
2655: WSH_UTIL_CORE.PrintMsg('Auto Pack Batch Name: ' || l_batch_rec.name);
2656:
2657: IF l_debug_on THEN
2658: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be auto packed are: ');
2659: FOR k in 1 .. l_selected_del_tab.count LOOP
2677: WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_packed_del_tab(k).delivery_id);
2678: END LOOP;
2679: END IF;
2680:
2681: WSH_UTIL_CORE.PrintDateTime;
2682: WSH_UTIL_CORE.PrintMsg('Summary: ');
2683: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2678: END LOOP;
2679: END IF;
2680:
2681: WSH_UTIL_CORE.PrintDateTime;
2682: WSH_UTIL_CORE.PrintMsg('Summary: ');
2683: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2679: END IF;
2680:
2681: WSH_UTIL_CORE.PrintDateTime;
2682: WSH_UTIL_CORE.PrintMsg('Summary: ');
2683: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687: IF l_results_summary.report_req_id > 0 THEN
2680:
2681: WSH_UTIL_CORE.PrintDateTime;
2682: WSH_UTIL_CORE.PrintMsg('Summary: ');
2683: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687: IF l_results_summary.report_req_id > 0 THEN
2688: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2681: WSH_UTIL_CORE.PrintDateTime;
2682: WSH_UTIL_CORE.PrintMsg('Summary: ');
2683: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687: IF l_results_summary.report_req_id > 0 THEN
2688: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2689: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2682: WSH_UTIL_CORE.PrintMsg('Summary: ');
2683: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687: IF l_results_summary.report_req_id > 0 THEN
2688: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2689: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2690: END IF;
2684: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687: IF l_results_summary.report_req_id > 0 THEN
2688: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2689: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2690: END IF;
2691:
2692: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2685: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687: IF l_results_summary.report_req_id > 0 THEN
2688: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2689: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2690: END IF;
2691:
2692: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2693: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2688: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2689: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2690: END IF;
2691:
2692: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2693: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2694: RAISE WSH_PK_BATCH_ERR;
2695: END IF;
2696:
2689: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2690: END IF;
2691:
2692: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2693: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2694: RAISE WSH_PK_BATCH_ERR;
2695: END IF;
2696:
2697: ELSE
2695: END IF;
2696:
2697: ELSE
2698: /* not deliveries selected */
2699: WSH_UTIL_CORE.PrintDateTime;
2700: WSH_UTIL_CORE.PrintMsg('Summary: ');
2701: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2702:
2703: END IF;
2696:
2697: ELSE
2698: /* not deliveries selected */
2699: WSH_UTIL_CORE.PrintDateTime;
2700: WSH_UTIL_CORE.PrintMsg('Summary: ');
2701: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2702:
2703: END IF;
2704: errbuf := 'Auto Pack Deliveries is completed successfully';
2697: ELSE
2698: /* not deliveries selected */
2699: WSH_UTIL_CORE.PrintDateTime;
2700: WSH_UTIL_CORE.PrintMsg('Summary: ');
2701: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2702:
2703: END IF;
2704: errbuf := 'Auto Pack Deliveries is completed successfully';
2705: retcode := '0';
2712: EXCEPTION
2713:
2714: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
2715: l_completion_status := 'WARNING';
2716: WSH_UTIL_CORE.PrintMsg('Invalid Auto Packing Level');
2717: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2718: errbuf := 'Auto Pack Deliveries is completed with warning';
2719: retcode := '1';
2720: IF l_debug_on THEN
2722: END IF;
2723:
2724: WHEN WSH_PK_BATCH_ERR THEN
2725: l_completion_status := 'WARNING';
2726: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries failed for this batch');
2727: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2728: errbuf := 'Auto Pack Deliveries is completed with warning';
2729: retcode := '1';
2730: IF l_debug_on THEN
2732: END IF;
2733:
2734: WHEN WSH_SELECT_ERR THEN
2735: l_completion_status := 'WARNING';
2736: WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
2737: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2738: errbuf := 'Auto Pack Deliveries is completed with warning';
2739: retcode := '1';
2740: IF l_debug_on THEN
2744: WHEN OTHERS THEN
2745: l_completion_status := 'ERROR';
2746: l_error_code := SQLCODE;
2747: l_error_text := SQLERRM;
2748: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries SRS failed with unexpected error.');
2749: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2750: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2751: errbuf := 'Auto Pack Deliveries failed with unexpected error';
2752: retcode := '2';
2745: l_completion_status := 'ERROR';
2746: l_error_code := SQLCODE;
2747: l_error_text := SQLERRM;
2748: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries SRS failed with unexpected error.');
2749: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2750: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2751: errbuf := 'Auto Pack Deliveries failed with unexpected error';
2752: retcode := '2';
2753: IF l_debug_on THEN
2858:
2859:
2860: EXCEPTION
2861: WHEN OTHERS THEN
2862: WSH_UTIL_CORE.Default_Handler('WSH_BATCH_PROCESS.log_batch_messages');
2863: --
2864: IF l_debug_on THEN
2865: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION: OTHERS');
2866: END IF;
2886:
2887: PROCEDURE Select_Delivery_Lines (
2888: p_select_criteria IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
2889: p_autocreate_deliveries IN VARCHAR2,
2890: x_selected_det_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2891: x_return_status OUT NOCOPY VARCHAR2)
2892: IS
2893: --TCA View Changes Start
2894: --RTV changes
2949: IF l_debug_on THEN
2950: WSH_DEBUG_SV.push(l_module_name);
2951: END IF;
2952: -- set default return status to SUCCESS
2953: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2954: -- clear up x_selected_det_tbl table
2955: x_selected_det_tbl.delete;
2956:
2957: IF p_autocreate_deliveries is NULL OR p_autocreate_deliveries='SP' THEN
3007: p_lookup_type => 'WSH_PD_DEL_LINE_STATUS' );
3008: FND_MESSAGE.SET_TOKEN('DETAILS_STATUS', l_msg_string);
3009: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3010:
3011: WSH_UTIL_CORE.PrintMsg(' Delivery Lines Status: '|| p_select_criteria.delivery_lines_status);
3012: END IF;
3013:
3014: -- put organization in the where clause if it is part of selection criteria
3015: IF p_select_criteria.organization_id IS NOT NULL THEN
3014: -- put organization in the where clause if it is part of selection criteria
3015: IF p_select_criteria.organization_id IS NOT NULL THEN
3016: l_sc_WHERE := l_sc_WHERE ||'AND wdd.organization_id = :x_organization_id ';
3017: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3018: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_select_criteria.organization_id));
3019: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3020:
3021: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_select_criteria.organization_id);
3022: END IF;
3017: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3018: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_select_criteria.organization_id));
3019: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3020:
3021: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_select_criteria.organization_id);
3022: END IF;
3023:
3024:
3025: -- put scheduled ship date in the where clause if it is part of selection criteria
3039: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_HI');
3040: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3041: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3042:
3043: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3044: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3045:
3046: ELSIF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3047: l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
3040: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3041: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3042:
3043: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3044: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3045:
3046: ELSIF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3047: l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
3048: l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled >= :x_scheduled_ship_date_lo ';
3049:
3050: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_LO');
3051: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_LO', to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3052: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3053: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3054:
3055: ELSE
3056: l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
3057: l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled <= :x_scheduled_ship_date_hi ';
3057: l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled <= :x_scheduled_ship_date_hi ';
3058: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_HI');
3059: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3060: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3061: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3062: END IF;
3063: END IF;
3064:
3065: -- put ship to location id in the where clause if it is part of selection criteria
3065: -- put ship to location id in the where clause if it is part of selection criteria
3066: IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
3067: l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_to_location_id = :x_ship_to_loc_id ';
3068:
3069: l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3070: p_location_id => p_select_criteria.ship_to_loc_id,
3071: p_format => 'NEW UI CODE'),
3072: 1, 80);
3073:
3075: FND_MESSAGE.SET_TOKEN('SHIP_TO', l_msg_string);
3076:
3077: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3078:
3079: WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_select_criteria.ship_to_loc_id));
3080: END IF;
3081:
3082: -- put source code in the where clause if it is part of selection criteria
3083: IF p_select_criteria.source_code IS NOT NULL THEN
3089: p_lookup_type => 'WSH_PD_SOURCE_SYSTEM' );
3090: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SOURCE_SYSTEM');
3091: FND_MESSAGE.SET_TOKEN('SOURCE_NAME', l_msg_string);
3092: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3093: WSH_UTIL_CORE.PrintMsg(' Source System: '|| p_select_criteria.source_code);
3094: ELSE
3095: l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code = :x_source_code ';
3096: l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3097: p_lookup_code => p_select_criteria.source_code,
3098: p_lookup_type => 'SOURCE_SYSTEM' );
3099: FND_MESSAGE.SET_NAME('WSH', 'SOURCE_SYSTEM');
3100: FND_MESSAGE.SET_TOKEN('SOURCE_NAME', l_msg_string);
3101: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3102: WSH_UTIL_CORE.PrintMsg(' Source System: '|| p_select_criteria.source_code);
3103: END IF;
3104:
3105: l_msg_string := NULL;
3106: ELSE
3129: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_CUSTOMER');
3130: FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_msg_string);
3131: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3132:
3133: WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| SubStr(p_select_criteria.customer_id,3)); --RTV changes
3134: END IF;
3135:
3136: -- put ship method code in the where clause if it is part of selection criteria
3137: IF p_select_criteria.ship_method_code IS NOT NULL THEN
3142: p_lookup_code => p_select_criteria.ship_method_code,
3143: p_lookup_type => 'SHIP_METHOD' );
3144: FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_msg_string);
3145: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3146: WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_select_criteria.ship_method_code);
3147: END IF;
3148:
3149: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3150: FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_select_criteria.log_level));
3148:
3149: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3150: FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_select_criteria.log_level));
3151: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3152: WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_select_criteria.log_level);
3153: --
3154: -- LSP PROJECT : put client id in the where clause if it is part of selection criteria
3155: IF p_select_criteria.client_id IS NOT NULL THEN
3156: l_sc_WHERE := l_sc_WHERE || 'AND wdd.client_id = :x_client_id ';
3160: x_client_id => l_client_id,
3161: x_client_name => l_msg_string,
3162: x_client_code => l_client_code,
3163: x_return_status => x_return_status);
3164: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3165: IF l_debug_on THEN
3166: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in WMS_DEPLOY.GET_CLIENT_DETAILS');
3167: RETURN;
3168: END IF;
3169: END IF;
3170: FND_MESSAGE.SET_NAME('WSH', 'WSH_CLIENT');
3171: FND_MESSAGE.SET_TOKEN('CLIENT_NAME', l_msg_string);
3172: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3173: WSH_UTIL_CORE.PrintMsg(' Client ID: '|| p_select_criteria.client_id);
3174: END IF;
3175: -- LSP PROJECT : end
3176: --
3177:
3187: IF i > l_str_length THEN
3188: EXIT;
3189: END IF;
3190: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3191: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3192: WSH_UTIL_CORE.PrintMsg(l_sub_str);
3193: i := i + 80;
3194: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3195: END LOOP;
3188: EXIT;
3189: END IF;
3190: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3191: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3192: WSH_UTIL_CORE.PrintMsg(l_sub_str);
3193: i := i + 80;
3194: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3195: END LOOP;
3196: END IF;
3190: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3191: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3192: WSH_UTIL_CORE.PrintMsg(l_sub_str);
3193: i := i + 80;
3194: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3195: END LOOP;
3196: END IF;
3197:
3198: IF l_debug_on THEN
3328: -- if the cursor is still open then close the cursor
3329: IF v_cursorID <> 0 THEN
3330: DBMS_SQL.Close_Cursor(v_cursorID);
3331: END IF;
3332: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
3333: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3334: IF l_debug_on THEN
3335: WSH_DEBUG_SV.pop(l_module_name);
3336: END IF;
3329: IF v_cursorID <> 0 THEN
3330: DBMS_SQL.Close_Cursor(v_cursorID);
3331: END IF;
3332: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
3333: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3334: IF l_debug_on THEN
3335: WSH_DEBUG_SV.pop(l_module_name);
3336: END IF;
3337:
3361: -- x_return_status Return status
3362: ---- ----------------------------------------------------------------------
3363:
3364: PROCEDURE Process_Delivery_Lines_Batch(
3365: p_selected_det_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
3366: p_append_flag IN VARCHAR2,
3367: p_ac_del_criteria IN VARCHAR2,
3368: x_appended_det_num OUT NOCOPY NUMBER,
3369: x_autocreate_del_det_num OUT NOCOPY NUMBER,
3372: x_msg_count OUT NOCOPY NUMBER,
3373: x_msg_data OUT NOCOPY VARCHAR2,
3374: x_return_status OUT NOCOPY VARCHAR2) IS
3375:
3376: l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3377: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3378: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3379: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3380: l_append_flag VARCHAR2(1);
3373: x_msg_data OUT NOCOPY VARCHAR2,
3374: x_return_status OUT NOCOPY VARCHAR2) IS
3375:
3376: l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3377: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3378: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3379: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3380: l_append_flag VARCHAR2(1);
3381: l_ac_del_criteria VARCHAR2(1);
3375:
3376: l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3377: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3378: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3379: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3380: l_append_flag VARCHAR2(1);
3381: l_ac_del_criteria VARCHAR2(1);
3382: l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
3383: l_action_out_rec wsh_glbl_var_strct_grp.dd_action_out_rec_type;
3407: IF l_debug_on THEN
3408: WSH_DEBUG_SV.push(l_module_name);
3409: END IF;
3410:
3411: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3412: x_appended_det_num := 0;
3413: x_autocreate_del_det_num := 0;
3414: x_new_del_num := 0;
3415: l_appended_det_tbl.delete;
3462: WSH_DEBUG_SV.logmsg(l_module_name,'Return status from DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries: '|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
3463: END IF;
3464:
3465: -- handle return status from WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries
3466: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3467: raise append_to_deliveries_failed;
3468: --Bug 10320421 following code commented. same as done in pick release while calling Append_to_Deliveries()
3469: /*ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3470: l_warning_num := l_warning_num + 1;
3465: -- handle return status from WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries
3466: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3467: raise append_to_deliveries_failed;
3468: --Bug 10320421 following code commented. same as done in pick release while calling Append_to_Deliveries()
3469: /*ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3470: l_warning_num := l_warning_num + 1;
3471: */
3472: END IF;
3473:
3502: x_msg_count := x_msg_count + l_number_of_warnings + l_number_of_errors;
3503: x_msg_data := x_msg_data || l_msg_data;
3504:
3505: -- handle return status from wsh_interface_grp.delivery_detail_action
3506: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3507: raise autocreate_delivery_failed;
3508: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3509: l_warning_num := l_warning_num + 1;
3510: END IF;
3504:
3505: -- handle return status from wsh_interface_grp.delivery_detail_action
3506: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3507: raise autocreate_delivery_failed;
3508: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3509: l_warning_num := l_warning_num + 1;
3510: END IF;
3511:
3512: IF l_warning_num > 0 THEN
3509: l_warning_num := l_warning_num + 1;
3510: END IF;
3511:
3512: IF l_warning_num > 0 THEN
3513: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3514: END IF;
3515:
3516:
3517: END IF;
3526: EXCEPTION
3527:
3528: WHEN append_to_deliveries_failed THEN
3529: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3530: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3531: IF l_debug_on THEN
3532: WSH_DEBUG_SV.logmsg(l_module_name,'APPEND_TO_DELIVERIES_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3533: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APPEND_TO_DELIVERIES_FAILED');
3534: END IF;
3534: END IF;
3535:
3536: WHEN autocreate_delivery_failed THEN
3537: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3538: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3539: IF l_debug_on THEN
3540: WSH_DEBUG_SV.logmsg(l_module_name,'AUTOCREATE_DELIVERY_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3541: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:AUTOCREATE_DELIVERY_FAILED');
3542: END IF;
3543:
3544: WHEN OTHERS THEN
3545: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3546: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3547: wsh_util_core.add_message(x_return_status, l_module_name);
3548: WSH_UTIL_CORE.default_handler('WSH_BATCH_PROCESS.Process_Delivery_Lines_Batch');
3549: FND_MSG_PUB.Count_And_Get
3550: (
3551: p_count => x_msg_count,
3544: WHEN OTHERS THEN
3545: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3546: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3547: wsh_util_core.add_message(x_return_status, l_module_name);
3548: WSH_UTIL_CORE.default_handler('WSH_BATCH_PROCESS.Process_Delivery_Lines_Batch');
3549: FND_MSG_PUB.Count_And_Get
3550: (
3551: p_count => x_msg_count,
3552: p_data => x_msg_data,
3658: l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
3659: l_appended_det_num NUMBER;
3660: l_appended_del_num NUMBER;
3661: l_autocreate_del_det_num NUMBER;
3662: l_selected_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3663: l_msg_count NUMBER;
3664: l_msg_data VARCHAR2(32767);
3665: l_new_del_num NUMBER;
3666: l_del_num NUMBER;
3692: Parameters_ERR EXCEPTION;
3693:
3694: l_processed number ;
3695: l_inner_loop_count number ;
3696: l_selected_det_tbl_tmp WSH_UTIL_CORE.Id_Tab_Type;
3697: g_selected_det_count number :=0;
3698: g_appended_det_num number :=0;
3699: g_appended_del_num number :=0;
3700: g_autocreate_del_det_num number :=0;
3752: l_log_level := p_log_level;
3753: END IF;
3754:
3755: -- enable printing of the concurrent request
3756: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
3757: -- set the log level for the whole session
3758: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
3759:
3760:
3754:
3755: -- enable printing of the concurrent request
3756: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
3757: -- set the log level for the whole session
3758: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
3759:
3760:
3761: FND_MESSAGE.SET_NAME('WSH','WSH_PD_PARM');
3762: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3770: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3771:
3772:
3773: -- print required input parameters
3774: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
3775: WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3776:
3777: IF p_entity_type = 'D' THEN
3778: --
3771:
3772:
3773: -- print required input parameters
3774: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
3775: WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3776:
3777: IF p_entity_type = 'D' THEN
3778: --
3779: IF WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
3775: WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3776:
3777: IF p_entity_type = 'D' THEN
3778: --
3779: IF WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
3780: FND_FILE.put_line(FND_FILE.output, ' ');
3781: FND_FILE.put_line(FND_FILE.output, ' ');
3782: -- print the summary results of auto create trips action
3783: FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_TRIP_CONSOLIDATION');
3781: FND_FILE.put_line(FND_FILE.output, ' ');
3782: -- print the summary results of auto create trips action
3783: FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_TRIP_CONSOLIDATION');
3784: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3785: WSH_UTIL_CORE.PrintMsg('Trip Consolidation is disabled because Transportation Planning is installed. ');
3786: ELSE
3787: --
3788: l_pickup_date_lo := fnd_date.canonical_to_date(p_pickup_date_lo);
3789: l_pickup_date_hi := fnd_date.canonical_to_date(p_pickup_date_hi);
3795: p_scheduled_ship_date_hi is not NULL THEN
3796: FND_MESSAGE.SET_NAME('WSH', 'WSH_SCHD_DATE_NOT_ALLOWED');
3797: FND_FILE.put_line(FND_FILE.output,' ');
3798: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3799: WSH_UTIL_CORE.PrintMsg('Scheduled Ship Date parameters are not applicable for entity Deliveries Only. Please do not enter Sheduled Ship Date range when selecting entity Deliveries Only.');
3800: raise Parameters_ERR;
3801: END IF;
3802:
3803: -- Deliveries Only, auto create trips
3840: p_lookup_code => p_deliveries_status,
3841: p_lookup_type => 'WSH_PD_DEL_STATUS' );
3842: FND_MESSAGE.SET_TOKEN('DELIVERIES_STATUS', l_msg_string);
3843: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3844: WSH_UTIL_CORE.PrintMsg(' Deliveries Status: '|| l_deliveries_status);
3845: END IF;
3846:
3847: IF p_pickup_date_lo IS NOT NULL THEN
3848: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_LO');
3847: IF p_pickup_date_lo IS NOT NULL THEN
3848: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_LO');
3849: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_LO', to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3850: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3851: WSH_UTIL_CORE.PrintMsg(' Pick Up Date Start: '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3852: END IF;
3853:
3854: IF p_pickup_date_hi IS NOT NULL THEN
3855: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_HI');
3854: IF p_pickup_date_hi IS NOT NULL THEN
3855: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_HI');
3856: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_HI', to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3857: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3858: WSH_UTIL_CORE.PrintMsg(' Pick Up Date End: '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3859: END IF;
3860:
3861: -- bug 3332670
3862: IF p_pickup_date_hi is NOT NULL and
3866: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_LO', to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3867: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_HI', to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3868: FND_FILE.put_line(FND_FILE.output,' ');
3869: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3870: WSH_UTIL_CORE.PrintMsg('The end date of Pick Up Date range '||to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS')||' should not precede the start date '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3871: raise Parameters_ERR;
3872: END IF;
3873: END IF;
3874:
3875: IF p_dropoff_date_lo IS NOT NULL THEN
3876: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_LO');
3877: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_LO', to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3878: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3879: WSH_UTIL_CORE.PrintMsg(' Drop Off Date Start: '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3880: END IF;
3881:
3882: IF p_dropoff_date_hi IS NOT NULL THEN
3883: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_HI');
3882: IF p_dropoff_date_hi IS NOT NULL THEN
3883: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_HI');
3884: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_HI', to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3885: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3886: WSH_UTIL_CORE.PrintMsg(' Drop Off Date End: '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3887: END IF;
3888:
3889: -- bug 3332670
3890: IF p_dropoff_date_hi is NOT NULL and
3894: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_LO', to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3895: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_HI', to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3896: FND_FILE.put_line(FND_FILE.output,' ');
3897: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3898: WSH_UTIL_CORE.PrintMsg('The end date of Drop Off Date range '||to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS')||' should not precede the start date '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3899: raise Parameters_ERR;
3900: END IF;
3901: END IF;
3902:
3908: x_client_name => l_msg_string,
3909: x_client_code => l_client_code,
3910: x_return_status => l_return_status);
3911:
3912: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3913: --{
3914: IF l_debug_on THEN
3915: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in WMS_DEPLOY.GET_CLIENT_DETAILS');
3916: END IF;
3918:
3919: FND_MESSAGE.SET_NAME('WSH', 'WSH_CLIENT');
3920: FND_MESSAGE.SET_TOKEN('CLIENT_NAME', l_msg_string);
3921: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3922: WSH_UTIL_CORE.PrintMsg(' Client ID: '|| (p_client_id));
3923: END IF;
3924: /*Modified R12.1.1 LSP PROJECT*/
3925:
3926: IF p_organization_id IS NOT NULL THEN
3924: /*Modified R12.1.1 LSP PROJECT*/
3925:
3926: IF p_organization_id IS NOT NULL THEN
3927: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3928: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
3929: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3930: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| to_char(p_organization_id));
3931: END IF;
3932:
3926: IF p_organization_id IS NOT NULL THEN
3927: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3928: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
3929: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3930: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| to_char(p_organization_id));
3931: END IF;
3932:
3933: IF p_customer_id IS NOT NULL THEN
3934:
3939: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_CUSTOMER');
3940: FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_msg_string);
3941: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3942: --bug fix 3286811
3943: WSH_UTIL_CORE.PrintMsg(' Customer ID: '||(SubStr(p_customer_id,3))); --RTV changes
3944: END IF;
3945:
3946: IF p_ship_to_loc_id IS NOT NULL THEN
3947: l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3943: WSH_UTIL_CORE.PrintMsg(' Customer ID: '||(SubStr(p_customer_id,3))); --RTV changes
3944: END IF;
3945:
3946: IF p_ship_to_loc_id IS NOT NULL THEN
3947: l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3948: p_location_id => p_ship_to_loc_id,
3949: p_format => 'NEW UI CODE'),
3950: 1, 80);
3951:
3951:
3952: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_TO');
3953: FND_MESSAGE.SET_TOKEN('SHIP_TO', l_msg_string);
3954: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3955: WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_ship_to_loc_id));
3956: END IF;
3957:
3958: IF p_ship_method_code IS NOT NULL THEN
3959:
3963: p_lookup_code => p_ship_method_code,
3964: p_lookup_type => 'SHIP_METHOD' );
3965: FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_msg_string);
3966: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3967: WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_ship_method_code);
3968: END IF;
3969:
3970: IF l_grp_ship_method IS NOT NULL THEN
3971: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_GRP_SHIP_METHOD');
3974: p_lookup_code => l_grp_ship_method,
3975: p_lookup_type => 'YES_NO' );
3976: FND_MESSAGE.SET_TOKEN('GRP_SHIP_METHOD', l_msg_string);
3977: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3978: WSH_UTIL_CORE.PrintMsg(' Group Deliveries by Ship Method: '|| l_grp_ship_method);
3979: END IF;
3980:
3981: IF l_grp_ship_from IS NOT NULL THEN
3982: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_GRP_SHIP_FROM');
3985: p_lookup_code => l_grp_ship_from,
3986: p_lookup_type => 'YES_NO' );
3987: FND_MESSAGE.SET_TOKEN('GRP_SHIP_FROM', l_msg_string);
3988: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3989: WSH_UTIL_CORE.PrintMsg(' Group Deliveries by Ship from Organization: '|| l_grp_ship_from);
3990: END IF;
3991:
3992: IF l_max_del_number IS NOT NULL THEN
3993: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MAX_DEL_NUMBER');
3992: IF l_max_del_number IS NOT NULL THEN
3993: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MAX_DEL_NUMBER');
3994: FND_MESSAGE.SET_TOKEN('MAX_DEL_NUMBER', to_char(l_max_del_number));
3995: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3996: WSH_UTIL_CORE.PrintMsg(' Maximum Number of Deliveries per Trip: '|| to_char(l_max_del_number));
3997: END IF;
3998:
3999: IF l_log_level IS NOT NULL THEN
4000: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3999: IF l_log_level IS NOT NULL THEN
4000: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
4001: FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(l_log_level));
4002: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4003: WSH_UTIL_CORE.PrintMsg(' Log Level: '|| l_log_level );
4004: END IF;
4005:
4006: IF l_debug_on THEN
4007: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips',WSH_DEBUG_SV.C_PROC_LEVEL);
4045: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_TRIPS_CREATED');
4046: FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRIPS', to_char(l_trip_num));
4047: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4048:
4049: WSH_UTIL_CORE.PrintDateTime;
4050: WSH_UTIL_CORE.PrintMsg('Summary: ');
4051: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
4052: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
4053: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
4046: FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRIPS', to_char(l_trip_num));
4047: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4048:
4049: WSH_UTIL_CORE.PrintDateTime;
4050: WSH_UTIL_CORE.PrintMsg('Summary: ');
4051: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
4052: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
4053: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
4054:
4047: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4048:
4049: WSH_UTIL_CORE.PrintDateTime;
4050: WSH_UTIL_CORE.PrintMsg('Summary: ');
4051: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
4052: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
4053: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
4054:
4055: -- print return status of autocreate trips
4048:
4049: WSH_UTIL_CORE.PrintDateTime;
4050: WSH_UTIL_CORE.PrintMsg('Summary: ');
4051: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
4052: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
4053: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
4054:
4055: -- print return status of autocreate trips
4056: IF l_debug_on THEN
4049: WSH_UTIL_CORE.PrintDateTime;
4050: WSH_UTIL_CORE.PrintMsg('Summary: ');
4051: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
4052: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
4053: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
4054:
4055: -- print return status of autocreate trips
4056: IF l_debug_on THEN
4057: WSH_DEBUG_SV.logmsg(l_module_name,'Return status from WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips is '|| l_return_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
4056: IF l_debug_on THEN
4057: WSH_DEBUG_SV.logmsg(l_module_name,'Return status from WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips is '|| l_return_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
4058: END IF;
4059:
4060: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4061: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4062: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
4063: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4064: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
4057: WSH_DEBUG_SV.logmsg(l_module_name,'Return status from WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips is '|| l_return_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
4058: END IF;
4059:
4060: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4061: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4062: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
4063: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4064: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
4065: END IF;
4059:
4060: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4061: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4062: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
4063: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4064: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
4065: END IF;
4066:
4067: FND_FILE.put_line(FND_FILE.output,' ');
4074: IF l_msg_count > 0 THEN
4075: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
4076: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4077: FND_FILE.put_line(FND_FILE.output,'====================');
4078: WSH_UTIL_CORE.PrintMsg('List of Messages: ');
4079: WSH_UTIL_CORE.PrintMsg('====================');
4080: FOR i in 1..l_msg_count LOOP
4081: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4082: p_msg_index => i,
4075: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
4076: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4077: FND_FILE.put_line(FND_FILE.output,'====================');
4078: WSH_UTIL_CORE.PrintMsg('List of Messages: ');
4079: WSH_UTIL_CORE.PrintMsg('====================');
4080: FOR i in 1..l_msg_count LOOP
4081: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4082: p_msg_index => i,
4083: p_data => l_buffer,
4083: p_data => l_buffer,
4084: p_msg_index_out => l_index_out);
4085: IF l_buffer IS NOT NULL THEN
4086: FND_FILE.put_line(FND_FILE.output,substrb(l_buffer,1,2000));
4087: WSH_UTIL_CORE.PrintMsg(substrb(l_buffer,1,2000));
4088: END IF;
4089: END LOOP;
4090:
4091: END IF;
4104: p_pickup_date_hi is not NULL THEN
4105: FND_MESSAGE.SET_NAME('WSH', 'WSH_PICKUP_DATE_NOT_ALLOWED');
4106: FND_FILE.put_line(FND_FILE.output,' ');
4107: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4108: WSH_UTIL_CORE.PrintMsg('Pick Up Date parameters are not applicable for entity Delivery Lines Only. Please do not enter Pick Up Date range when selecting entity Delivery Lines Only.');
4109: raise Parameters_ERR;
4110: END IF;
4111:
4112: -- bug 3319789
4114: p_dropoff_date_hi is not NULL THEN
4115: FND_MESSAGE.SET_NAME('WSH', 'WSH_DROPOFF_DATE_NOT_ALLOWED');
4116: FND_FILE.put_line(FND_FILE.output,' ');
4117: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4118: WSH_UTIL_CORE.PrintMsg('Drop Off Date parameters are not applicable for entity Delivery Lines Only. Please do not enter Drop Off Date range when selecting entity Delivery Lines Only.');
4119: raise Parameters_ERR;
4120: END IF;
4121:
4122: -- set default value of Delivery Lines Status to 'ALL'
4136: FND_MESSAGE.SET_TOKEN('SCHEDULED_DATE_LO', to_char(l_scheduled_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
4137: FND_MESSAGE.SET_TOKEN('SCHEDULED_DATE_HI', to_char(l_scheduled_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
4138: FND_FILE.put_line(FND_FILE.output,' ');
4139: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4140: WSH_UTIL_CORE.PrintMsg('The end date of Scheduled Ship Date range '||to_char(l_scheduled_date_hi, 'DD-MON-YYYY HH24:MI:SS')||' should not precede the start date '|| to_char(l_scheduled_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
4141: raise Parameters_ERR;
4142: END IF;
4143: END IF;
4144:
4191:
4192:
4193: -- turn off append deliveries if TP is installed OR lines are not from OE OR
4194: -- lines status is not Release to Warehouse or Ready to Release
4195: -- IF WSH_UTIL_CORE.TP_Is_Installed = 'Y'
4196: IF p_source_system <> 'OE'
4197: OR p_delivery_lines_status in ('ALL', 'X', 'Y')
4198: OR p_organization_id is NULL THEN
4199: l_append_deliveries := 'N';
4262: IF l_debug_on THEN
4263: WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Select_Deliveries_Lines is '|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
4264: END IF;
4265:
4266: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4267: -- select_delivery_lines failed, exit
4268: raise WSH_SELECT_ERR;
4269: END IF;
4270: --Bug 10320421
4317: x_msg_count => l_msg_count,
4318: x_msg_data => l_msg_data,
4319: x_return_status => l_return_status);
4320:
4321: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4322: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4323: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4324: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4325: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4318: x_msg_data => l_msg_data,
4319: x_return_status => l_return_status);
4320:
4321: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4322: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4323: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4324: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4325: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4326: END IF;
4320:
4321: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4322: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4323: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4324: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4325: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4326: END IF;
4327: FND_FILE.put_line(FND_FILE.output,' ');
4328: FND_FILE.put_line(FND_FILE.output,' ');
4335: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
4336: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4337: FND_FILE.put_line(FND_FILE.output,'====================');
4338:
4339: WSH_UTIL_CORE.PrintMsg('====================');
4340: FOR i in 1..l_msg_count LOOP
4341: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4342: p_msg_index => i,
4343: p_data => l_buffer,
4343: p_data => l_buffer,
4344: p_msg_index_out => l_index_out);
4345: IF l_buffer IS NOT NULL THEN
4346: FND_FILE.put_line(FND_FILE.output,substrb(l_buffer,1,2000));
4347: WSH_UTIL_CORE.PrintMsg(substrb(l_buffer,1,2000));
4348: END IF;
4349: END LOOP;
4350:
4351: END IF;
4408: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_CREATED');
4409: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(g_new_del_num));
4410: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4411:
4412: WSH_UTIL_CORE.PrintDateTime;
4413: WSH_UTIL_CORE.PrintMsg('Summary: ');
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4409: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(g_new_del_num));
4410: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4411:
4412: WSH_UTIL_CORE.PrintDateTime;
4413: WSH_UTIL_CORE.PrintMsg('Summary: ');
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4410: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4411:
4412: WSH_UTIL_CORE.PrintDateTime;
4413: WSH_UTIL_CORE.PrintMsg('Summary: ');
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4418: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4411:
4412: WSH_UTIL_CORE.PrintDateTime;
4413: WSH_UTIL_CORE.PrintMsg('Summary: ');
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4418: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4419:
4412: WSH_UTIL_CORE.PrintDateTime;
4413: WSH_UTIL_CORE.PrintMsg('Summary: ');
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4418: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4419:
4420:
4413: WSH_UTIL_CORE.PrintMsg('Summary: ');
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4418: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4419:
4420:
4421: ELSE
4414: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4418: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4419:
4420:
4421: ELSE
4422: -- no delivery lines selected
4429: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
4430: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(l_selected_det_tbl.count));
4431: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4432:
4433: WSH_UTIL_CORE.PrintDateTime;
4434: WSH_UTIL_CORE.PrintMsg('Summary: ');
4435: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4436:
4437: END IF;
4430: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(l_selected_det_tbl.count));
4431: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4432:
4433: WSH_UTIL_CORE.PrintDateTime;
4434: WSH_UTIL_CORE.PrintMsg('Summary: ');
4435: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4436:
4437: END IF;
4438:
4431: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4432:
4433: WSH_UTIL_CORE.PrintDateTime;
4434: WSH_UTIL_CORE.PrintMsg('Summary: ');
4435: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4436:
4437: END IF;
4438:
4439: END IF;
4467: WSH_DEBUG_SV.pop(l_module_name);
4468: END IF;
4469:
4470: WHEN WSH_SELECT_ERR THEN
4471: WSH_UTIL_CORE.PrintMsg('Error Messages: ' || l_msg_data);
4472: l_completion_status := 'ERROR';
4473: WSH_UTIL_CORE.PrintMsg('Failed to select delivery lines for processing');
4474: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4475: errbuf := 'Process Deliveries is completed with warning';
4469:
4470: WHEN WSH_SELECT_ERR THEN
4471: WSH_UTIL_CORE.PrintMsg('Error Messages: ' || l_msg_data);
4472: l_completion_status := 'ERROR';
4473: WSH_UTIL_CORE.PrintMsg('Failed to select delivery lines for processing');
4474: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4475: errbuf := 'Process Deliveries is completed with warning';
4476: retcode := '1';
4477: IF l_debug_on THEN
4491: WHEN OTHERS THEN
4492: l_completion_status := 'ERROR';
4493: l_error_code := SQLCODE;
4494: l_error_text := SQLERRM;
4495: WSH_UTIL_CORE.PrintMsg('Process Deliveries SRS failed with unexpected error.');
4496: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4497: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4498: errbuf := 'Process Deliveries failed with unexpected error';
4499: retcode := '2';
4492: l_completion_status := 'ERROR';
4493: l_error_code := SQLCODE;
4494: l_error_text := SQLERRM;
4495: WSH_UTIL_CORE.PrintMsg('Process Deliveries SRS failed with unexpected error.');
4496: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4497: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4498: errbuf := 'Process Deliveries failed with unexpected error';
4499: retcode := '2';
4500: IF l_debug_on THEN