[Home] [Help]
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:
107: l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.container_flag = ''N'' ';
108:
109: IF p_input_info.organization_id IS NOT NULL THEN
110: l_sc_WHERE := l_sc_WHERE ||'AND wnd.organization_id = :x_organization_id ';
111: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_input_info.organization_id);
112: p_batch_rec.organization_id := p_input_info.organization_id;
113: END IF;
114:
115: IF p_input_info.pr_batch_id IS NOT NULL THEN
121: ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
122: 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 ) ';
123: END IF;
124:
125: WSH_UTIL_CORE.PrintMsg(' Pick Release Batch ID: '|| p_input_info.pr_batch_id);
126: p_batch_rec.selected_batch_id := p_input_info.pr_batch_id;
127:
128: ELSE
129:
136: END IF;
137:
138: IF p_input_info.ap_batch_id IS NOT NULL THEN
139: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ap_batch_id = :x_ap_batch_id ';
140: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch ID: '|| p_input_info.ap_batch_id);
141: END IF;
142:
143: IF p_input_info.delivery_name_lo IS NOT NULL OR p_input_info.delivery_name_hi IS NOT NULL THEN
144: IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
142:
143: IF p_input_info.delivery_name_lo IS NOT NULL OR p_input_info.delivery_name_hi IS NOT NULL THEN
144: IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
145: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name BETWEEN :x_delivery_name_lo AND :x_delivery_name_hi ';
146: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
147: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
148: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
149: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
150: ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
144: IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
145: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name BETWEEN :x_delivery_name_lo AND :x_delivery_name_hi ';
146: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
147: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
148: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
149: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
150: ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
151: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name >= :x_delivery_name_lo ';
152: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
148: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
149: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
150: ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
151: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name >= :x_delivery_name_lo ';
152: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
153: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
154: ELSE
155: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name <= :x_delivery_name_hi ';
156: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
152: WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
153: p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
154: ELSE
155: l_sc_WHERE := l_sc_WHERE ||'AND wnd.name <= :x_delivery_name_hi ';
156: WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
157: p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
158: END IF;
159: END IF;
160:
170:
171: IF p_input_info.bol_number_lo IS NOT NULL AND p_input_info.bol_number_hi IS NOT NULL THEN
172: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number BETWEEN :x_bol_number_lo AND :x_bol_number_hi ';
173:
174: WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
175: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
176:
177: WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
178: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
173:
174: WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
175: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
176:
177: WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
178: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
179:
180: ELSIF p_input_info.bol_number_lo IS NOT NULL THEN
181: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number >= :x_bol_number_lo ';
178: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
179:
180: ELSIF p_input_info.bol_number_lo IS NOT NULL THEN
181: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number >= :x_bol_number_lo ';
182: WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
183: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
184:
185: ELSE
186: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number <= :x_bol_number_hi ';
183: p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
184:
185: ELSE
186: l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number <= :x_bol_number_hi ';
187: WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
188: p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
189: END IF;
190:
191: END IF;
192:
193:
194: IF p_input_info.planned_flag IS NOT NULL THEN
195: l_sc_WHERE := l_sc_WHERE || 'AND wnd.planned_flag = :x_planned_flag ';
196: WSH_UTIL_CORE.PrintMsg(' Planned Flag: '|| p_input_info.planned_flag);
197: p_batch_rec.planned_flag := p_input_info.planned_flag;
198: END IF;
199:
200: IF p_input_info.ship_from_loc_id IS NOT NULL THEN
198: END IF;
199:
200: IF p_input_info.ship_from_loc_id IS NOT NULL THEN
201: l_sc_WHERE := l_sc_WHERE || 'AND wnd.initial_pickup_location_id = :x_ship_from_loc_id ';
202: WSH_UTIL_CORE.PrintMsg(' Ship from Location ID: '|| to_char(p_input_info.ship_from_loc_id));
203: p_batch_rec.ship_from_location_id := p_input_info.ship_from_loc_id;
204: END IF;
205:
206: IF p_input_info.ship_to_loc_id IS NOT NULL THEN
204: END IF;
205:
206: IF p_input_info.ship_to_loc_id IS NOT NULL THEN
207: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_location_id = :x_ship_to_loc_id ';
208: WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_input_info.ship_to_loc_id));
209: p_batch_rec.ship_to_location_id := p_input_info.ship_to_loc_id;
210: END IF;
211:
212: IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
210: END IF;
211:
212: IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
213: l_sc_WHERE := l_sc_WHERE || 'AND wnd.intmed_ship_to_location_id = :x_intmed_ship_to_loc_id ';
214: WSH_UTIL_CORE.PrintMsg(' Intermediate Ship to Location ID: '|| to_char(p_input_info.intmed_ship_to_loc_id));
215: p_batch_rec.intmed_ship_to_loc_id := p_input_info.intmed_ship_to_loc_id;
216: END IF;
217:
218: IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
216: END IF;
217:
218: IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
219: l_sc_WHERE := l_sc_WHERE || 'AND wnd.pooled_ship_to_location_id = :x_pooled_ship_to_loc_id ';
220: WSH_UTIL_CORE.PrintMsg(' Pooled Ship to Location ID: '|| p_input_info.pooled_ship_to_loc_id);
221: p_batch_rec.pooled_ship_to_loc_id := p_input_info.pooled_ship_to_loc_id;
222: END IF;
223:
224: IF p_input_info.customer_id IS NOT NULL THEN
222: END IF;
223:
224: IF p_input_info.customer_id IS NOT NULL THEN
225: l_sc_WHERE := l_sc_WHERE || 'AND wnd.customer_id = :x_customer_id ';
226: WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| p_input_info.customer_id);
227: p_batch_rec.customer_id := p_input_info.customer_id;
228: END IF;
229:
230: IF p_input_info.ship_method_code IS NOT NULL THEN
228: END IF;
229:
230: IF p_input_info.ship_method_code IS NOT NULL THEN
231: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ship_method_code = :x_ship_method_code ';
232: WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_input_info.ship_method_code);
233: p_batch_rec.ship_method_code := p_input_info.ship_method_code;
234: END IF;
235:
236: IF p_input_info.fob_code IS NOT NULL THEN
234: END IF;
235:
236: IF p_input_info.fob_code IS NOT NULL THEN
237: l_sc_WHERE := l_sc_WHERE || 'AND wnd.fob_code = :x_fob_code ';
238: WSH_UTIL_CORE.PrintMsg(' FOB Code: '|| p_input_info.fob_code);
239: p_batch_rec.fob_code := p_input_info.fob_code;
240: END IF;
241:
242: IF p_input_info.freight_terms_code IS NOT NULL THEN
241:
242: IF p_input_info.freight_terms_code IS NOT NULL THEN
243: l_sc_WHERE := l_sc_WHERE || 'AND wnd.freight_terms_code = :x_freight_terms_code ';
244:
245: WSH_UTIL_CORE.PrintMsg(' Freight Term Code: '|| p_input_info.freight_terms_code);
246: p_batch_rec.freight_terms_code := p_input_info.freight_terms_code;
247: END IF;
248:
249: IF p_input_info.pickup_date_lo IS NOT NULL OR p_input_info.pickup_date_hi IS NOT NULL THEN
251:
252: l_pickup_date_lo := fnd_date.canonical_to_date(p_input_info.pickup_date_lo);
253: l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
254: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) BETWEEN :x_pickup_date_lo AND :x_pickup_date_hi ';
255: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
256: p_batch_rec.pickup_date_lo := l_pickup_date_lo;
257: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
258: p_batch_rec.pickup_date_hi := l_pickup_date_hi;
259:
253: l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
254: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) BETWEEN :x_pickup_date_lo AND :x_pickup_date_hi ';
255: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
256: p_batch_rec.pickup_date_lo := l_pickup_date_lo;
257: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
258: p_batch_rec.pickup_date_hi := l_pickup_date_hi;
259:
260: ELSIF p_input_info.pickup_date_lo IS NOT NULL THEN
261:
260: ELSIF p_input_info.pickup_date_lo IS NOT NULL THEN
261:
262: l_pickup_date_lo := fnd_date.canonical_to_date(p_input_info.pickup_date_lo);
263: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) >= :x_pickup_date_lo ';
264: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
265: p_batch_rec.pickup_date_lo := l_pickup_date_lo;
266:
267: ELSE
268:
267: ELSE
268:
269: l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
270: l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) <= :x_pickup_date_hi ';
271: WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
272: p_batch_rec.pickup_date_hi := l_pickup_date_hi;
273: END IF;
274: END IF;
275:
278: IF p_input_info.dropoff_date_lo IS NOT NULL AND p_input_info.dropoff_date_hi IS NOT NULL THEN
279: l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
280: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
281: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date BETWEEN :x_dropoff_date_lo AND :x_dropoff_date_hi ';
282: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
283: p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
284: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
285: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
286:
280: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
281: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date BETWEEN :x_dropoff_date_lo AND :x_dropoff_date_hi ';
282: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
283: p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
284: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
285: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
286:
287: ELSIF p_input_info.dropoff_date_lo IS NOT NULL THEN
288: l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
286:
287: ELSIF p_input_info.dropoff_date_lo IS NOT NULL THEN
288: l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
289: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date >= :x_dropoff_date_lo ';
290: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
291: p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
292:
293: ELSE
294: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
292:
293: ELSE
294: l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
295: l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date <= :x_dropoff_date_hi ';
296: WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
297: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
298: END IF;
299: END IF;
300:
297: p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
298: END IF;
299: END IF;
300:
301: WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_input_info.log_level);
302:
303: l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
304: IF length(l_sc_EXISTS_BOL) > 0 THEN
305: l_sc_FINAL := l_sc_FINAL ||'AND EXISTS ( '|| l_sc_EXISTS_BOL || ' ) ';
315: IF i > l_str_length THEN
316: EXIT;
317: END IF;
318: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
319: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
320: WSH_UTIL_CORE.PrintMsg(l_sub_str);
321: i := i + 80;
322: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
323: END LOOP;
316: EXIT;
317: END IF;
318: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
319: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
320: WSH_UTIL_CORE.PrintMsg(l_sub_str);
321: i := i + 80;
322: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
323: END LOOP;
324: END IF;
318: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
319: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
320: WSH_UTIL_CORE.PrintMsg(l_sub_str);
321: i := i + 80;
322: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
323: END LOOP;
324: END IF;
325:
326: v_CursorID := DBMS_SQL.Open_Cursor;
438: EXCEPTION
439:
440: WHEN others THEN
441:
442: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
443: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
444: IF l_debug_on THEN
445: WSH_DEBUG_SV.pop(l_module_name);
446: END IF;
439:
440: WHEN others THEN
441:
442: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
443: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
444: IF l_debug_on THEN
445: WSH_DEBUG_SV.pop(l_module_name);
446: END IF;
447:
512:
513: l_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
514:
515: l_delivery_rec get_delivery%ROWTYPE;
516: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
517: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
518: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
519: l_actual_dep_date DATE ;
520: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
513: l_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
514:
515: l_delivery_rec get_delivery%ROWTYPE;
516: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
517: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
518: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
519: l_actual_dep_date DATE ;
520: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
521: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
514:
515: l_delivery_rec get_delivery%ROWTYPE;
516: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
517: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
518: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
519: l_actual_dep_date DATE ;
520: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
521: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
522: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
542: IF l_debug_on THEN
543: WSH_DEBUG_SV.push(l_module_name);
544: END IF;
545:
546: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
547:
548: l_tmp_del_tab.delete;
549: l_tmp_del_tab(1) := p_delivery_id;
550:
641: x_msg_data => l_msg_data
642: );
643:
644:
645: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
646: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
647: /* error or unexpected error */
648: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
649: ROLLBACK TO beginning_of_loop;
642: );
643:
644:
645: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
646: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
647: /* error or unexpected error */
648: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
649: ROLLBACK TO beginning_of_loop;
650: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
644:
645: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
646: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
647: /* error or unexpected error */
648: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
649: ROLLBACK TO beginning_of_loop;
650: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
651: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
652: END IF;
646: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
647: /* error or unexpected error */
648: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
649: ROLLBACK TO beginning_of_loop;
650: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
651: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
652: END IF;
653:
654:
647: /* error or unexpected error */
648: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
649: ROLLBACK TO beginning_of_loop;
650: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
651: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
652: END IF;
653:
654:
655: ELSE
652: END IF;
653:
654:
655: ELSE
656: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
657: WSH_UTIL_CORE.PrintMsg('Delivery '|| p_delivery_id||' not found or cannot be locked');
658: /* cannot lock the delivery */
659: END IF; -- if delivery exist
660:
653:
654:
655: ELSE
656: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
657: WSH_UTIL_CORE.PrintMsg('Delivery '|| p_delivery_id||' not found or cannot be locked');
658: /* cannot lock the delivery */
659: END IF; -- if delivery exist
660:
661: CLOSE get_delivery; /* unlock the delivery */
666: EXCEPTION
667:
668:
669: WHEN delivery_locked THEN
670: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
671: IF get_delivery%ISOPEN THEN
672: CLOSE get_delivery;
673: END IF;
674: IF c_first_trip_ship_method%ISOPEN THEN
673: END IF;
674: IF c_first_trip_ship_method%ISOPEN THEN
675: CLOSE c_first_trip_ship_method;
676: END IF;
677: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID '||to_char(p_delivery_id));
678: IF l_debug_on THEN
679: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock delivery ID '||to_char(p_delivery_id));
680: WSH_DEBUG_SV.POP(l_module_name);
681: END IF;
680: WSH_DEBUG_SV.POP(l_module_name);
681: END IF;
682:
683: WHEN others THEN
684: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
685: IF get_delivery%ISOPEN THEN
686: CLOSE get_delivery;
687: END IF;
688: IF c_first_trip_ship_method%ISOPEN THEN
687: END IF;
688: IF c_first_trip_ship_method%ISOPEN THEN
689: CLOSE c_first_trip_ship_method;
690: END IF;
691: WSH_UTIL_CORE.PrintMsg('ERROR: unhandled exception');
692: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_A_Delivery');
693: IF l_debug_on THEN
694: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: unhandled exception');
695: WSH_DEBUG_SV.POP(l_module_name);
688: IF c_first_trip_ship_method%ISOPEN THEN
689: CLOSE c_first_trip_ship_method;
690: END IF;
691: WSH_UTIL_CORE.PrintMsg('ERROR: unhandled exception');
692: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_A_Delivery');
693: IF l_debug_on THEN
694: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: unhandled exception');
695: WSH_DEBUG_SV.POP(l_module_name);
696: END IF;
733: c.trip_id = b.trip_id AND
734: a.stop_id <> C.stop_id
735: FOR UPDATE NOWAIT;
736:
737: l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
738: l_self_stop_id NUMBER;
739: l_other_stop_id NUMBER;
740: l_trip_id NUMBER;
741: l_return_status VARCHAR2(1);
765: WSH_DEBUG_SV.push(l_module_name);
766: WSH_DEBUG_SV.log(l_module_name, 'Locking stop '|| to_char(p_stop_id));
767: END IF;
768:
769: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
770: OPEN lock_stop_trip(p_stop_id);
771: FETCH lock_stop_trip INTO l_self_stop_id, l_trip_id , l_other_stop_id;
772: IF lock_stop_trip%FOUND THEN
773:
801: x_msg_data => l_msg_data);
802: x_return_status := l_return_status;
803:
804: CLOSE lock_stop_trip;
805: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
806: x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
807:
808: ROLLBACK TO beginning_of_the_procedure;
809: END IF;
802: x_return_status := l_return_status;
803:
804: CLOSE lock_stop_trip;
805: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
806: x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
807:
808: ROLLBACK TO beginning_of_the_procedure;
809: END IF;
810: ELSE
818: END IF;
819:
820: EXCEPTION
821: WHEN stop_trip_locked THEN
822: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
823: CLOSE lock_stop_trip ;
824: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
825: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
826: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
820: EXCEPTION
821: WHEN stop_trip_locked THEN
822: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
823: CLOSE lock_stop_trip ;
824: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
825: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
826: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
827: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
828: IF l_debug_on THEN
823: CLOSE lock_stop_trip ;
824: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
825: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
826: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
827: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
828: IF l_debug_on THEN
829: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
830: WSH_DEBUG_SV.POP(l_module_name);
831: END IF;
830: WSH_DEBUG_SV.POP(l_module_name);
831: END IF;
832:
833: WHEN others THEN
834: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
835: CLOSE lock_stop_trip;
836: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
837: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
838: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
832:
833: WHEN others THEN
834: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
835: CLOSE lock_stop_trip;
836: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
837: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
838: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
839: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
840: IF l_debug_on THEN
835: CLOSE lock_stop_trip;
836: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
837: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
838: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
839: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
840: IF l_debug_on THEN
841: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
842: WSH_DEBUG_SV.POP(l_module_name);
843: END IF;
869: PROCEDURE Ship_Confirm_Batch(
870: p_del_tab IN WSH_BATCH_PROCESS.Del_Info_Tab,
871: p_sc_batch_id IN NUMBER,
872: p_log_level IN NUMBER,
873: x_confirmed_del_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
874: x_results_summary OUT NOCOPY WSH_BATCH_PROCESS.Results_Summary_Rec,
875: x_return_status OUT NOCOPY VARCHAR2,
876: p_commit IN VARCHAR2) IS -- BugFix #4001135
877:
947:
948:
949:
950: l_ship_confirm_rule_rec G_GET_SHIP_CONFIRM_RULE%ROWTYPE;
951: l_sc_confirmed_dels wsh_util_core.id_tab_type;
952: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
953: l_ship_confirm_rule_id NUMBER := 0;
954: l_trip_id NUMBER := NULL;
955: l_stop_id NUMBER := NULL;
948:
949:
950: l_ship_confirm_rule_rec G_GET_SHIP_CONFIRM_RULE%ROWTYPE;
951: l_sc_confirmed_dels wsh_util_core.id_tab_type;
952: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
953: l_ship_confirm_rule_id NUMBER := 0;
954: l_trip_id NUMBER := NULL;
955: l_stop_id NUMBER := NULL;
956: l_stop_sequence_number NUMBER := NULL;
954: l_trip_id NUMBER := NULL;
955: l_stop_id NUMBER := NULL;
956: l_stop_sequence_number NUMBER := NULL;
957: l_stop_location_id NUMBER := NULL;
958: l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
959: l_stop_location_ids WSH_UTIL_CORE.Id_Tab_Type;
960: l_request_id NUMBER := 0;
961: l_num_warn NUMBER := 0;
962: l_num_error NUMBER := 0;
955: l_stop_id NUMBER := NULL;
956: l_stop_sequence_number NUMBER := NULL;
957: l_stop_location_id NUMBER := NULL;
958: l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
959: l_stop_location_ids WSH_UTIL_CORE.Id_Tab_Type;
960: l_request_id NUMBER := 0;
961: l_num_warn NUMBER := 0;
962: l_num_error NUMBER := 0;
963: l_actual_dep_date DATE := NULL;
981: --
982: --Bugfix 4070732
983: l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
984: l_reset_flags BOOLEAN;
985: l_return_status1 VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
986: --
987: l_actual_departure_date DATE;
988: --
989: BEGIN
1000: x_results_summary.success := 0;
1001: x_results_summary.warning := 0;
1002: x_results_summary.failure := 0;
1003: x_results_summary.report_req_id := 0;
1004: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1005:
1006: IF l_debug_on THEN
1007: WSH_DEBUG_SV.push(l_module_name);
1008: END IF;
1010: l_sc_confirmed_dels.delete;
1011: l_stops_to_close.delete;
1012: l_stop_location_ids.delete;
1013:
1014: WSH_UTIL_CORE.PrintDateTime;
1015:
1016: -- lock ship confirm batch
1017: OPEN get_sc_batch ;
1018: FETCH get_sc_batch INTO l_ship_confirm_rule_id, l_batch_creation_date,
1056:
1057: EXCEPTION
1058:
1059: WHEN delivery_locked THEN
1060: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1061: WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1062: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1063: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1064: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1057: EXCEPTION
1058:
1059: WHEN delivery_locked THEN
1060: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1061: WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1062: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1063: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1064: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1065: log_batch_messages(p_sc_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1060: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1061: WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1062: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1063: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1064: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1065: log_batch_messages(p_sc_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1066: IF l_debug_on THEN
1067: 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));
1068: WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery');
1069: END IF;
1070:
1071: WHEN OTHERS THEN
1072: /* this will catch the exception when failing to obtain the lock on the delivery */
1073: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1074: WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1075: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1076: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1077: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1070:
1071: WHEN OTHERS THEN
1072: /* this will catch the exception when failing to obtain the lock on the delivery */
1073: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1074: WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1075: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1076: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1077: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1078: IF l_debug_on THEN
1073: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1074: WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1075: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1076: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1077: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1078: IF l_debug_on THEN
1079: 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));
1080: WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery');
1081: END IF;
1082: END;
1083:
1084: --
1085: --bug 4070732
1086: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1087: AND p_commit = FND_API.G_TRUE ) THEN
1088: --{
1089: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1090: --{
1085: --bug 4070732
1086: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1087: AND p_commit = FND_API.G_TRUE ) THEN
1088: --{
1089: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1090: --{
1091:
1092: l_reset_flags := FALSE;
1093:
1091:
1092: l_reset_flags := FALSE;
1093:
1094: IF l_debug_on THEN
1095: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1096: END IF;
1097:
1098: BEGIN
1099:
1096: END IF;
1097:
1098: BEGIN
1099:
1100: WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1101: x_return_status => l_return_status1);
1102: IF l_debug_on THEN
1103: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1104: END IF;
1102: IF l_debug_on THEN
1103: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1104: END IF;
1105:
1106: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1107: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1108: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1109: l_return_status := l_return_status1;
1110: END IF;
1103: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1104: END IF;
1105:
1106: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1107: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1108: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1109: l_return_status := l_return_status1;
1110: END IF;
1111: EXCEPTION
1104: END IF;
1105:
1106: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1107: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1108: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1109: l_return_status := l_return_status1;
1110: END IF;
1111: EXCEPTION
1112: WHEN others THEN
1109: l_return_status := l_return_status1;
1110: END IF;
1111: EXCEPTION
1112: WHEN others THEN
1113: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1114: END;
1115:
1116: --}
1117: END IF;
1118: --}
1119: END IF;
1120: --bug 4070732
1121: --
1122: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1123: x_results_summary.success := x_results_summary.success + 1;
1124: l_sc_confirmed_dels(l_sc_confirmed_dels.count+1):= p_del_tab(i).delivery_id;
1125: IF l_debug_on THEN
1126: select status_code into l_status_code from wsh_new_deliveries where delivery_id = p_del_tab(i).delivery_id;
1127: 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);
1128: END IF;
1129:
1130:
1131: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1132: x_results_summary.warning := x_results_summary.warning + 1;
1133: l_sc_confirmed_dels(l_sc_confirmed_dels.count+1):= p_del_tab(i).delivery_id;
1134: log_batch_messages(p_sc_batch_id, p_del_tab(i).delivery_id, NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1135: IF l_debug_on THEN
1205:
1206: EXCEPTION
1207: WHEN others THEN
1208: /* this will catch the exeption when stop and trip cannot be locked */
1209: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1210: l_lock_error := 'Y';
1211:
1212: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1213: IF l_debug_on THEN
1208: /* this will catch the exeption when stop and trip cannot be locked */
1209: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1210: l_lock_error := 'Y';
1211:
1212: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1213: IF l_debug_on THEN
1214: 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)));
1215: WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop');
1216: END IF;
1217: END;
1218:
1219: --
1220: --bug 4070732
1221: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1222: AND p_commit = FND_API.G_TRUE ) THEN
1223: --{
1224: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1225: --{
1220: --bug 4070732
1221: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1222: AND p_commit = FND_API.G_TRUE ) THEN
1223: --{
1224: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1225: --{
1226:
1227: l_reset_flags := FALSE;
1228:
1226:
1227: l_reset_flags := FALSE;
1228:
1229: IF l_debug_on THEN
1230: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1231: END IF;
1232:
1233: BEGIN
1234:
1231: END IF;
1232:
1233: BEGIN
1234:
1235: WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1236: x_return_status => l_return_status1);
1237: IF l_debug_on THEN
1238: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1239: END IF;
1237: IF l_debug_on THEN
1238: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1239: END IF;
1240:
1241: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1242: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1243: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1244: l_return_status := l_return_status1;
1245: END IF;
1238: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1239: END IF;
1240:
1241: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1242: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1243: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1244: l_return_status := l_return_status1;
1245: END IF;
1246: EXCEPTION
1239: END IF;
1240:
1241: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1242: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1243: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1244: l_return_status := l_return_status1;
1245: END IF;
1246: EXCEPTION
1247: WHEN others THEN
1244: l_return_status := l_return_status1;
1245: END IF;
1246: EXCEPTION
1247: WHEN others THEN
1248: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1249: END;
1250:
1251: --}
1252: END IF;
1253: --}
1254: END IF;
1255: --bug 4070732
1256:
1257: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1258:
1259: IF l_debug_on THEN
1260: WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed stop '|| l_stops_to_close(i));
1261: END IF;
1259: IF l_debug_on THEN
1260: WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed stop '|| l_stops_to_close(i));
1261: END IF;
1262: l_closing_stop_success := l_closing_stop_success + 1;
1263: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1264: IF l_debug_on THEN
1265: WSH_DEBUG_SV.logmsg(l_module_name,'Trip stop '|| l_stops_to_close(i) ||' is closed with warnings');
1266: END IF;
1267: l_closing_stop_warning := l_closing_stop_warning + 1;
1273:
1274: IF l_lock_error = 'Y' THEN
1275: FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
1276: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(l_stops_to_close(i)));
1277: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1278: END IF;
1279: l_closing_stop_failure := l_closing_stop_failure + 1;
1280: log_batch_messages(p_sc_batch_id, NULL , l_stops_to_close(i) , l_stop_location_ids(i), NULL);
1281: END IF;
1312: 'ALL', '', '', 0, p_sc_batch_id);
1313: IF (l_request_id = 0) THEN
1314: raise inv_inter_req_submission;
1315: ELSE
1316: WSH_UTIL_CORE.PrintMsg('Interface request submitted for closed stops, request ID: '
1317: || to_char(l_request_id) );
1318: END IF;
1319:
1320: END IF;
1351:
1352: EXCEPTION
1353:
1354: WHEN wsh_missing_sc_batch THEN
1355: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1356: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to find the ship confirm batch ');
1357:
1358: IF l_debug_on THEN
1359: WSH_DEBUG_SV.pop(l_module_name);
1352: EXCEPTION
1353:
1354: WHEN wsh_missing_sc_batch THEN
1355: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1356: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to find the ship confirm batch ');
1357:
1358: IF l_debug_on THEN
1359: WSH_DEBUG_SV.pop(l_module_name);
1360: END IF;
1362: WHEN wsh_missing_sc_rule THEN
1363: IF get_sc_batch%ISOPEN THEN
1364: CLOSE get_sc_batch;
1365: END IF;
1366: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1367: WSH_UTIL_CORE.PrintMsg('ERROR: Ship Confirm Rule is not found or has expired');
1368: IF l_debug_on THEN
1369: WSH_DEBUG_SV.pop(l_module_name);
1370: END IF;
1363: IF get_sc_batch%ISOPEN THEN
1364: CLOSE get_sc_batch;
1365: END IF;
1366: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1367: WSH_UTIL_CORE.PrintMsg('ERROR: Ship Confirm Rule is not found or has expired');
1368: IF l_debug_on THEN
1369: WSH_DEBUG_SV.pop(l_module_name);
1370: END IF;
1371:
1375: END IF;
1376: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1377: CLOSE G_GET_SHIP_CONFIRM_RULE;
1378: END IF;
1379: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1380: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Interface concurrent request ');
1381: IF l_debug_on THEN
1382: WSH_DEBUG_SV.pop(l_module_name);
1383: END IF;
1376: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1377: CLOSE G_GET_SHIP_CONFIRM_RULE;
1378: END IF;
1379: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1380: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Interface concurrent request ');
1381: IF l_debug_on THEN
1382: WSH_DEBUG_SV.pop(l_module_name);
1383: END IF;
1384:
1388: END IF;
1389: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1390: CLOSE G_GET_SHIP_CONFIRM_RULE;
1391: END IF;
1392: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1393: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Auto Ship Confirm Report ');
1394: IF l_debug_on THEN
1395: WSH_DEBUG_SV.pop(l_module_name);
1396: END IF;
1389: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1390: CLOSE G_GET_SHIP_CONFIRM_RULE;
1391: END IF;
1392: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1393: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Auto Ship Confirm Report ');
1394: IF l_debug_on THEN
1395: WSH_DEBUG_SV.pop(l_module_name);
1396: END IF;
1397:
1401: END IF;
1402: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1403: CLOSE G_GET_SHIP_CONFIRM_RULE;
1404: END IF;
1405: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1406: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID ');
1407: IF l_debug_on THEN
1408: WSH_DEBUG_SV.pop(l_module_name);
1409: END IF;
1402: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1403: CLOSE G_GET_SHIP_CONFIRM_RULE;
1404: END IF;
1405: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1406: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID ');
1407: IF l_debug_on THEN
1408: WSH_DEBUG_SV.pop(l_module_name);
1409: END IF;
1410:
1414: END IF;
1415: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1416: CLOSE G_GET_SHIP_CONFIRM_RULE;
1417: END IF;
1418: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_Batch');
1419: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1420: IF l_debug_on THEN
1421: WSH_DEBUG_SV.pop(l_module_name);
1422: END IF;
1415: IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1416: CLOSE G_GET_SHIP_CONFIRM_RULE;
1417: END IF;
1418: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_Batch');
1419: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1420: IF l_debug_on THEN
1421: WSH_DEBUG_SV.pop(l_module_name);
1422: END IF;
1423:
1467: NVL(auto_ap_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
1468:
1469:
1470: l_delivery_rec get_delivery%ROWTYPE;
1471: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1472: l_action VARCHAR2(30) := NULL;
1473: l_pack_cont_flag VARCHAR2(1) := NULL;
1474: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1475: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1470: l_delivery_rec get_delivery%ROWTYPE;
1471: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1472: l_action VARCHAR2(30) := NULL;
1473: l_pack_cont_flag VARCHAR2(1) := NULL;
1474: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1475: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1476: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1477: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1478: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1471: l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1472: l_action VARCHAR2(30) := NULL;
1473: l_pack_cont_flag VARCHAR2(1) := NULL;
1474: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1475: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1476: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1477: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1478: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1479: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1472: l_action VARCHAR2(30) := NULL;
1473: l_pack_cont_flag VARCHAR2(1) := NULL;
1474: l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1475: l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1476: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1477: l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1478: l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1479: l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1480: l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
1504:
1505: l_tmp_del_tab.delete;
1506: l_tmp_del_tab(1) := p_delivery_id;
1507:
1508: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1509:
1510: IF p_auto_pack_level = 1 THEN
1511: l_action := 'AUTO-PACK';
1512: l_pack_cont_flag := 'N';
1559: x_msg_count => l_msg_count,
1560: x_msg_data => l_msg_data
1561: );
1562:
1563: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1564: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1565: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1566: ROLLBACK TO beginning_of_loop;
1567: ELSE
1560: x_msg_data => l_msg_data
1561: );
1562:
1563: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1564: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1565: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1566: ROLLBACK TO beginning_of_loop;
1567: ELSE
1568: x_return_status := l_return_status;
1561: );
1562:
1563: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1564: l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1565: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1566: ROLLBACK TO beginning_of_loop;
1567: ELSE
1568: x_return_status := l_return_status;
1569: END IF;
1568: x_return_status := l_return_status;
1569: END IF;
1570:
1571: ELSE /* cannot lock the delivery */
1572: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1573: END IF; -- if delivery exist
1574:
1575: CLOSE get_delivery;
1576: IF l_debug_on THEN
1579:
1580: EXCEPTION
1581:
1582: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1583: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1584: WSH_UTIL_CORE.PrintMsg('ERROR: invalid auto pack level');
1585: IF l_debug_on THEN
1586: WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Auot Pack Level');
1587: WSH_DEBUG_SV.pop(l_module_name);
1580: EXCEPTION
1581:
1582: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1583: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1584: WSH_UTIL_CORE.PrintMsg('ERROR: invalid auto pack level');
1585: IF l_debug_on THEN
1586: WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Auot Pack Level');
1587: WSH_DEBUG_SV.pop(l_module_name);
1588: END IF;
1588: END IF;
1589:
1590: WHEN delivery_locked THEN
1591: CLOSE get_delivery;
1592: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1593: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1594: IF l_debug_on THEN
1595: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1596: WSH_DEBUG_SV.pop(l_module_name);
1589:
1590: WHEN delivery_locked THEN
1591: CLOSE get_delivery;
1592: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1593: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1594: IF l_debug_on THEN
1595: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1596: WSH_DEBUG_SV.pop(l_module_name);
1597: END IF;
1599:
1600:
1601: WHEN OTHERS THEN
1602: CLOSE get_delivery;
1603: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1604: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1605: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1606: IF l_debug_on THEN
1607: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1600:
1601: WHEN OTHERS THEN
1602: CLOSE get_delivery;
1603: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1604: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1605: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1606: IF l_debug_on THEN
1607: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1608: WSH_DEBUG_SV.pop(l_module_name);
1601: WHEN OTHERS THEN
1602: CLOSE get_delivery;
1603: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1604: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1605: WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1606: IF l_debug_on THEN
1607: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1608: WSH_DEBUG_SV.pop(l_module_name);
1609: END IF;
1642: x_results_summary OUT NOCOPY WSH_BATCH_PROCESS.Results_Summary_Rec,
1643: x_return_status OUT NOCOPY VARCHAR2,
1644: P_COMMIT IN VARCHAR2) IS -- BugFix #4001135
1645:
1646: l_ap_packed_dels wsh_util_core.id_tab_type;
1647: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1648: l_debug_on BOOLEAN;
1649: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_Batch';
1650: WSH_SUBMIT_AP_REPORT_ERR EXCEPTION;
1643: x_return_status OUT NOCOPY VARCHAR2,
1644: P_COMMIT IN VARCHAR2) IS -- BugFix #4001135
1645:
1646: l_ap_packed_dels wsh_util_core.id_tab_type;
1647: l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1648: l_debug_on BOOLEAN;
1649: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_Batch';
1650: WSH_SUBMIT_AP_REPORT_ERR EXCEPTION;
1651: delivery_locked EXCEPTION;
1654:
1655: --Bugfix 4070732
1656: l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1657: l_reset_flags BOOLEAN;
1658: l_return_status1 VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1659:
1660:
1661: BEGIN
1662:
1672: x_results_summary.success := 0;
1673: x_results_summary.warning := 0;
1674: x_results_summary.failure := 0;
1675: x_results_summary.report_req_id := 0;
1676: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1677:
1678: x_packed_del_tab.delete;
1679:
1680: IF l_debug_on THEN
1681: WSH_DEBUG_SV.push(l_module_name);
1682: WSH_DEBUG_SV.logmsg(l_module_name,'AP level'||p_auto_pack_level);
1683: END IF;
1684:
1685: WSH_UTIL_CORE.PrintDateTime;
1686:
1687: IF p_auto_pack_level <> 1 AND p_auto_pack_level <> 2 THEN
1688: RAISE WSH_INVALID_AUTO_PACK_LEVEL;
1689: END IF;
1705:
1706: EXCEPTION
1707:
1708: WHEN delivery_locked THEN
1709: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1710: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1711: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1712: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1713: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1706: EXCEPTION
1707:
1708: WHEN delivery_locked THEN
1709: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1710: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1711: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1712: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1713: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1714: log_batch_messages(p_ap_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1709: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1710: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1711: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1712: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1713: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1714: log_batch_messages(p_ap_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1715:
1716: IF l_debug_on THEN
1717: WSH_DEBUG_SV.logmsg(l_module_name, 'Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1718: WSH_DEBUG_SV.pop('wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery');
1719: END IF;
1720:
1721: WHEN OTHERS THEN
1722: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1723: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1724: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1725: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1726: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1719: END IF;
1720:
1721: WHEN OTHERS THEN
1722: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1723: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1724: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1725: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1726: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1727:
1722: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1723: WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1724: FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1725: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1726: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1727:
1728: IF l_debug_on THEN
1729: WSH_DEBUG_SV.logmsg(l_module_name, 'Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1730: WSH_DEBUG_SV.pop('wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery');
1734: END;
1735:
1736: --
1737: --bug 4070732
1738: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1739: AND p_commit = FND_API.G_TRUE ) THEN
1740: --{
1741: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1742: --{
1737: --bug 4070732
1738: IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1739: AND p_commit = FND_API.G_TRUE ) THEN
1740: --{
1741: IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1742: --{
1743:
1744: l_reset_flags := FALSE;
1745:
1743:
1744: l_reset_flags := FALSE;
1745:
1746: IF l_debug_on THEN
1747: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1748: END IF;
1749:
1750: BEGIN
1751:
1748: END IF;
1749:
1750: BEGIN
1751:
1752: WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1753: x_return_status => l_return_status1);
1754: IF l_debug_on THEN
1755: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1756: END IF;
1754: IF l_debug_on THEN
1755: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1756: END IF;
1757:
1758: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1759: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1760: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1761: l_return_status := l_return_status1;
1762: END IF;
1755: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1756: END IF;
1757:
1758: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1759: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1760: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1761: l_return_status := l_return_status1;
1762: END IF;
1763: EXCEPTION
1756: END IF;
1757:
1758: IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1759: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1760: WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1761: l_return_status := l_return_status1;
1762: END IF;
1763: EXCEPTION
1764: WHEN others THEN
1761: l_return_status := l_return_status1;
1762: END IF;
1763: EXCEPTION
1764: WHEN others THEN
1765: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1766: END;
1767:
1768: --}
1769: END IF;
1769: END IF;
1770: --}
1771: END IF;
1772: --bug 4070732
1773: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1774: x_results_summary.success := x_results_summary.success + 1;
1775: x_packed_del_tab(x_packed_del_tab.count+1).delivery_id := p_del_tab(i).delivery_id;
1776: x_packed_del_tab(x_packed_del_tab.count).organization_id := p_del_tab(i).organization_id;
1777: x_packed_del_tab(x_packed_del_tab.count).initial_pickup_location_id := p_del_tab(i).initial_pickup_location_id;
1779: IF l_debug_on THEN
1780: WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack succeed for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1781: END IF;
1782:
1783: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1784: x_results_summary.warning := x_results_summary.warning + 1;
1785: x_packed_del_tab(x_packed_del_tab.count+1).delivery_id := p_del_tab(i).delivery_id;
1786: x_packed_del_tab(x_packed_del_tab.count).organization_id := p_del_tab(i).organization_id;
1787: x_packed_del_tab(x_packed_del_tab.count).initial_pickup_location_id := p_del_tab(i).initial_pickup_location_id;
1841:
1842: EXCEPTION
1843:
1844: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1845: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1846: FND_MESSAGE.SET_NAME('WSH' , 'WSH_INVALID_AUTO_PACK_LEVEL');
1847: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1848: IF l_debug_on THEN
1849: WSH_DEBUG_SV.pop(l_module_name);
1843:
1844: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1845: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1846: FND_MESSAGE.SET_NAME('WSH' , 'WSH_INVALID_AUTO_PACK_LEVEL');
1847: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1848: IF l_debug_on THEN
1849: WSH_DEBUG_SV.pop(l_module_name);
1850: END IF;
1851:
1849: WSH_DEBUG_SV.pop(l_module_name);
1850: END IF;
1851:
1852: WHEN WSH_SUBMIT_AP_REPORT_ERR THEN
1853: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1854: FND_MESSAGE.SET_NAME('WSH' , 'WSH_SUBMIT_AP_REPORT_ERR');
1855: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1856: IF l_debug_on THEN
1857: WSH_DEBUG_SV.pop(l_module_name);
1851:
1852: WHEN WSH_SUBMIT_AP_REPORT_ERR THEN
1853: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1854: FND_MESSAGE.SET_NAME('WSH' , 'WSH_SUBMIT_AP_REPORT_ERR');
1855: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1856: IF l_debug_on THEN
1857: WSH_DEBUG_SV.pop(l_module_name);
1858: END IF;
1859:
1857: WSH_DEBUG_SV.pop(l_module_name);
1858: END IF;
1859:
1860: WHEN others THEN
1861: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_Deliveries_Batch');
1862: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1863: IF l_debug_on THEN
1864: WSH_DEBUG_SV.pop(l_module_name);
1865: END IF;
1858: END IF;
1859:
1860: WHEN others THEN
1861: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_Deliveries_Batch');
1862: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1863: IF l_debug_on THEN
1864: WSH_DEBUG_SV.pop(l_module_name);
1865: END IF;
1866:
1947: l_log_level NUMBER := 0;
1948: l_temp BOOLEAN;
1949: l_batch_rec WSH_PICKING_BATCHES%ROWTYPE;
1950: l_debug_on BOOLEAN;
1951: l_confirmed_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1952: l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
1953: l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
1954: l_results_summary WSH_BATCH_PROCESS.Results_Summary_Rec;
1955: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Confirm_Delivery_SRS';
1962:
1963: -- deliveryMerge
1964: Adjust_Planned_Flag_Err EXCEPTION;
1965: l_warning_num NUMBER := 0;
1966: l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
1967:
1968: --
1969: -- Bug 5097710
1970: --
1979: l_log_level := 0;
1980: ELSE
1981: l_log_level := p_log_level;
1982: END IF;
1983: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
1984:
1985: --
1986: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1987: --
1999: -- Fetch user and login information
2000: l_user_id := FND_GLOBAL.USER_ID;
2001: l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2002:
2003: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2004:
2005: IF p_ship_confirm_rule_id IS NULL THEN
2006: raise WSH_MISSING_SC_RULE;
2007: END IF;
2009: IF p_sc_batch_prefix IS NULL THEN
2010: raise WSH_MISSING_SC_BATCH_PREFIX;
2011: END IF;
2012:
2013: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2014: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2015: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2016: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2017:
2010: raise WSH_MISSING_SC_BATCH_PREFIX;
2011: END IF;
2012:
2013: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2014: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2015: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2016: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2017:
2018:
2011: END IF;
2012:
2013: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2014: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2015: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2016: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2017:
2018:
2019: IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2012:
2013: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2014: WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2015: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2016: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2017:
2018:
2019: IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2020: raise WSH_NO_FUTURE_SHIPDATE;
2015: WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2016: WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2017:
2018:
2019: IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2020: raise WSH_NO_FUTURE_SHIPDATE;
2021: END IF;
2022:
2023: l_select_criteria.process_mode := G_SHIP_CONFIRM;
2053: -- so I will pack them into record, and
2054: -- this is the record used to populate wsh_picking_batches
2055: -- otherwise i need to have a bunch of if stmt again to check the parameters
2056:
2057: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2058: raise WSH_SELECT_ERR;
2059: END IF;
2060:
2061: IF l_selected_del_tab.count > 0 THEN
2081: IF l_debug_on THEN
2082: WSH_DEBUG_SV.logmsg(l_module_name, 'Return status from Calling Adjust_Planned_Flag:'||l_return_status);
2083: END IF;
2084:
2085: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2086: l_warning_num := l_warning_num + 1;
2087: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2088: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2089: raise Adjust_Planned_Flag_Err;
2083: END IF;
2084:
2085: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2086: l_warning_num := l_warning_num + 1;
2087: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2088: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2089: raise Adjust_Planned_Flag_Err;
2090: END IF;
2091: commit;
2084:
2085: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2086: l_warning_num := l_warning_num + 1;
2087: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2088: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2089: raise Adjust_Planned_Flag_Err;
2090: END IF;
2091: commit;
2092:
2211: p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
2212: p_Planned_Flag => l_batch_rec.planned_flag,
2213: p_Selected_Batch_Id => l_batch_rec.selected_batch_id);
2214:
2215: WSH_UTIL_CORE.PrintMsg('Ship Confirm Batch Name: ' || l_batch_rec.name);
2216:
2217: IF l_debug_on THEN
2218: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be ship confirmed are: ');
2219: FOR k in 1 .. l_selected_del_tab.count LOOP
2229: x_results_summary => l_results_summary,
2230: x_return_status => l_return_status,
2231: p_commit => FND_API.G_TRUE); -- BugFix #4001135
2232:
2233: WSH_UTIL_CORE.PrintDateTime;
2234: WSH_UTIL_CORE.PrintMsg('Summary: ');
2235: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2236: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2230: x_return_status => l_return_status,
2231: p_commit => FND_API.G_TRUE); -- BugFix #4001135
2232:
2233: WSH_UTIL_CORE.PrintDateTime;
2234: WSH_UTIL_CORE.PrintMsg('Summary: ');
2235: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2236: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2231: p_commit => FND_API.G_TRUE); -- BugFix #4001135
2232:
2233: WSH_UTIL_CORE.PrintDateTime;
2234: WSH_UTIL_CORE.PrintMsg('Summary: ');
2235: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2236: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2239:
2232:
2233: WSH_UTIL_CORE.PrintDateTime;
2234: WSH_UTIL_CORE.PrintMsg('Summary: ');
2235: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2236: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2239:
2240: IF l_results_summary.report_req_id > 0 THEN
2233: WSH_UTIL_CORE.PrintDateTime;
2234: WSH_UTIL_CORE.PrintMsg('Summary: ');
2235: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2236: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2239:
2240: IF l_results_summary.report_req_id > 0 THEN
2241: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2234: WSH_UTIL_CORE.PrintMsg('Summary: ');
2235: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2236: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2239:
2240: IF l_results_summary.report_req_id > 0 THEN
2241: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2242: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2237: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2239:
2240: IF l_results_summary.report_req_id > 0 THEN
2241: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2242: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2243: END IF;
2244:
2245: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2238: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2239:
2240: IF l_results_summary.report_req_id > 0 THEN
2241: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2242: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2243: END IF;
2244:
2245: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2246: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2241: WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2242: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2243: END IF;
2244:
2245: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2246: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2247: RAISE WSH_SC_BATCH_ERR;
2248: END IF;
2249:
2242: WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2243: END IF;
2244:
2245: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2246: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2247: RAISE WSH_SC_BATCH_ERR;
2248: END IF;
2249:
2250: ELSE
2248: END IF;
2249:
2250: ELSE
2251: /* no deliveries selected */
2252: WSH_UTIL_CORE.PrintDateTime;
2253: WSH_UTIL_CORE.PrintMsg('Summary: ');
2254: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2255:
2256: END IF;
2249:
2250: ELSE
2251: /* no deliveries selected */
2252: WSH_UTIL_CORE.PrintDateTime;
2253: WSH_UTIL_CORE.PrintMsg('Summary: ');
2254: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2255:
2256: END IF;
2257:
2250: ELSE
2251: /* no deliveries selected */
2252: WSH_UTIL_CORE.PrintDateTime;
2253: WSH_UTIL_CORE.PrintMsg('Summary: ');
2254: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2255:
2256: END IF;
2257:
2258: errbuf := 'Automated Ship Confirm is completed successfully';
2265: EXCEPTION
2266:
2267: WHEN Adjust_Planned_Flag_Err THEN
2268: -- Bug 5097710
2269: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2270: WSH_UTIL_CORE.PrintMsg('Summary:');
2271: WSH_UTIL_CORE.PrintMsg(l_summary);
2272: WSH_UTIL_CORE.PrintMsg('Details:');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2266:
2267: WHEN Adjust_Planned_Flag_Err THEN
2268: -- Bug 5097710
2269: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2270: WSH_UTIL_CORE.PrintMsg('Summary:');
2271: WSH_UTIL_CORE.PrintMsg(l_summary);
2272: WSH_UTIL_CORE.PrintMsg('Details:');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2274: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2267: WHEN Adjust_Planned_Flag_Err THEN
2268: -- Bug 5097710
2269: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2270: WSH_UTIL_CORE.PrintMsg('Summary:');
2271: WSH_UTIL_CORE.PrintMsg(l_summary);
2272: WSH_UTIL_CORE.PrintMsg('Details:');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2274: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2275: --
2268: -- Bug 5097710
2269: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2270: WSH_UTIL_CORE.PrintMsg('Summary:');
2271: WSH_UTIL_CORE.PrintMsg(l_summary);
2272: WSH_UTIL_CORE.PrintMsg('Details:');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2274: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2275: --
2276: l_completion_status := 'WARNING';
2269: wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2270: WSH_UTIL_CORE.PrintMsg('Summary:');
2271: WSH_UTIL_CORE.PrintMsg(l_summary);
2272: WSH_UTIL_CORE.PrintMsg('Details:');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2274: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2275: --
2276: l_completion_status := 'WARNING';
2277: WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2270: WSH_UTIL_CORE.PrintMsg('Summary:');
2271: WSH_UTIL_CORE.PrintMsg(l_summary);
2272: WSH_UTIL_CORE.PrintMsg('Details:');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2274: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2275: --
2276: l_completion_status := 'WARNING';
2277: WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2278: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2273: WSH_UTIL_CORE.PrintMsg(l_detail);
2274: WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2275: --
2276: l_completion_status := 'WARNING';
2277: WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2278: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2279: errbuf := 'Automated Ship Confirm is completed with warning';
2280: retcode := '1';
2281: IF l_debug_on THEN
2283: END IF;
2284:
2285: WHEN WSH_MISSING_SC_RULE THEN
2286: l_completion_status := 'WARNING';
2287: WSH_UTIL_CORE.PrintMsg('Ship Confirm Rule is not found or has expired');
2288: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2289: errbuf := 'Automated Ship Confirm is completed with warning';
2290: retcode := '1';
2291: IF l_debug_on THEN
2293: END IF;
2294:
2295: WHEN WSH_MISSING_SC_BATCH_PREFIX THEN
2296: l_completion_status := 'WARNING';
2297: WSH_UTIL_CORE.PrintMsg('You need to specify Ship Confirm Batch Prefix');
2298: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2299: errbuf := 'Automated Ship Confirm is completed with warning';
2300: retcode := '1';
2301: IF l_debug_on THEN
2303: END IF;
2304:
2305: WHEN WSH_NO_FUTURE_SHIPDATE THEN
2306: l_completion_status := 'WARNING';
2307: 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');
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_SELECT_ERR THEN
2316: l_completion_status := 'WARNING';
2317: WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
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_SC_BATCH_ERR THEN
2326: l_completion_status := 'WARNING';
2327: WSH_UTIL_CORE.PrintMsg('Ship Confirm failed for this batch');
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
2335: WHEN OTHERS THEN
2336: l_completion_status := 'ERROR';
2337: l_error_code := SQLCODE;
2338: l_error_text := SQLERRM;
2339: WSH_UTIL_CORE.PrintMsg('Confirm Delivery SRS failed with unexpected error.');
2340: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2341: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2342: errbuf := 'Automated Ship Confirm failed with unexpected error';
2343: retcode := '2';
2336: l_completion_status := 'ERROR';
2337: l_error_code := SQLCODE;
2338: l_error_text := SQLERRM;
2339: WSH_UTIL_CORE.PrintMsg('Confirm Delivery SRS failed with unexpected error.');
2340: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2341: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2342: errbuf := 'Automated Ship Confirm failed with unexpected error';
2343: retcode := '2';
2344: IF l_debug_on THEN
2469: ELSE
2470: l_log_level := p_log_level;
2471: END IF;
2472:
2473: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2474: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2475:
2476: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2477: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2470: l_log_level := p_log_level;
2471: END IF;
2472:
2473: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2474: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2475:
2476: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2477: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2478: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2472:
2473: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2474: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2475:
2476: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2477: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2478: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2479:
2480: l_select_criteria.process_mode := G_AUTO_PACK;
2473: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2474: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2475:
2476: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2477: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2478: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2479:
2480: l_select_criteria.process_mode := G_AUTO_PACK;
2481: l_select_criteria.organization_id := p_organization_id;
2474: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2475:
2476: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2477: WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2478: WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2479:
2480: l_select_criteria.process_mode := G_AUTO_PACK;
2481: l_select_criteria.organization_id := p_organization_id;
2482: l_select_criteria.pr_batch_id := p_pr_batch_id;
2504: p_batch_rec => l_batch_rec,
2505: x_selected_del_tab => l_selected_del_tab,
2506: x_return_status => l_return_status);
2507:
2508: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2509: raise WSH_SELECT_ERR;
2510: END IF;
2511:
2512: IF l_selected_del_tab.count > 0 THEN
2621: p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
2622: p_Planned_Flag => l_batch_rec.planned_flag,
2623: p_Selected_Batch_Id => l_batch_rec.selected_batch_id);
2624:
2625: WSH_UTIL_CORE.PrintMsg('Auto Pack Batch Name: ' || l_batch_rec.name);
2626:
2627: IF l_debug_on THEN
2628: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be auto packed are: ');
2629: FOR k in 1 .. l_selected_del_tab.count LOOP
2647: WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_packed_del_tab(k).delivery_id);
2648: END LOOP;
2649: END IF;
2650:
2651: WSH_UTIL_CORE.PrintDateTime;
2652: WSH_UTIL_CORE.PrintMsg('Summary: ');
2653: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2648: END LOOP;
2649: END IF;
2650:
2651: WSH_UTIL_CORE.PrintDateTime;
2652: WSH_UTIL_CORE.PrintMsg('Summary: ');
2653: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2649: END IF;
2650:
2651: WSH_UTIL_CORE.PrintDateTime;
2652: WSH_UTIL_CORE.PrintMsg('Summary: ');
2653: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2657: IF l_results_summary.report_req_id > 0 THEN
2650:
2651: WSH_UTIL_CORE.PrintDateTime;
2652: WSH_UTIL_CORE.PrintMsg('Summary: ');
2653: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2657: IF l_results_summary.report_req_id > 0 THEN
2658: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2651: WSH_UTIL_CORE.PrintDateTime;
2652: WSH_UTIL_CORE.PrintMsg('Summary: ');
2653: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2657: IF l_results_summary.report_req_id > 0 THEN
2658: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2659: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2652: WSH_UTIL_CORE.PrintMsg('Summary: ');
2653: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2657: IF l_results_summary.report_req_id > 0 THEN
2658: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2659: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2660: END IF;
2654: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2657: IF l_results_summary.report_req_id > 0 THEN
2658: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2659: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2660: END IF;
2661:
2662: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2655: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2656: WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2657: IF l_results_summary.report_req_id > 0 THEN
2658: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2659: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2660: END IF;
2661:
2662: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2663: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2658: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2659: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2660: END IF;
2661:
2662: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2663: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2664: RAISE WSH_PK_BATCH_ERR;
2665: END IF;
2666:
2659: WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2660: END IF;
2661:
2662: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2663: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2664: RAISE WSH_PK_BATCH_ERR;
2665: END IF;
2666:
2667: ELSE
2665: END IF;
2666:
2667: ELSE
2668: /* not deliveries selected */
2669: WSH_UTIL_CORE.PrintDateTime;
2670: WSH_UTIL_CORE.PrintMsg('Summary: ');
2671: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2672:
2673: END IF;
2666:
2667: ELSE
2668: /* not deliveries selected */
2669: WSH_UTIL_CORE.PrintDateTime;
2670: WSH_UTIL_CORE.PrintMsg('Summary: ');
2671: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2672:
2673: END IF;
2674: errbuf := 'Auto Pack Deliveries is completed successfully';
2667: ELSE
2668: /* not deliveries selected */
2669: WSH_UTIL_CORE.PrintDateTime;
2670: WSH_UTIL_CORE.PrintMsg('Summary: ');
2671: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2672:
2673: END IF;
2674: errbuf := 'Auto Pack Deliveries is completed successfully';
2675: retcode := '0';
2682: EXCEPTION
2683:
2684: WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
2685: l_completion_status := 'WARNING';
2686: WSH_UTIL_CORE.PrintMsg('Invalid Auto Packing Level');
2687: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2688: errbuf := 'Auto Pack Deliveries is completed with warning';
2689: retcode := '1';
2690: IF l_debug_on THEN
2692: END IF;
2693:
2694: WHEN WSH_PK_BATCH_ERR THEN
2695: l_completion_status := 'WARNING';
2696: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries failed for this batch');
2697: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2698: errbuf := 'Auto Pack Deliveries is completed with warning';
2699: retcode := '1';
2700: IF l_debug_on THEN
2702: END IF;
2703:
2704: WHEN WSH_SELECT_ERR THEN
2705: l_completion_status := 'WARNING';
2706: WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
2707: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2708: errbuf := 'Auto Pack Deliveries is completed with warning';
2709: retcode := '1';
2710: IF l_debug_on THEN
2714: WHEN OTHERS THEN
2715: l_completion_status := 'ERROR';
2716: l_error_code := SQLCODE;
2717: l_error_text := SQLERRM;
2718: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries SRS failed with unexpected error.');
2719: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2720: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2721: errbuf := 'Auto Pack Deliveries failed with unexpected error';
2722: retcode := '2';
2715: l_completion_status := 'ERROR';
2716: l_error_code := SQLCODE;
2717: l_error_text := SQLERRM;
2718: WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries SRS failed with unexpected error.');
2719: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2720: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2721: errbuf := 'Auto Pack Deliveries failed with unexpected error';
2722: retcode := '2';
2723: IF l_debug_on THEN
2828:
2829:
2830: EXCEPTION
2831: WHEN OTHERS THEN
2832: WSH_UTIL_CORE.Default_Handler('WSH_BATCH_PROCESS.log_batch_messages');
2833: --
2834: IF l_debug_on THEN
2835: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION: OTHERS');
2836: END IF;
2856:
2857: PROCEDURE Select_Delivery_Lines (
2858: p_select_criteria IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
2859: p_autocreate_deliveries IN VARCHAR2,
2860: x_selected_det_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2861: x_return_status OUT NOCOPY VARCHAR2)
2862: IS
2863: --TCA View Changes Start
2864: CURSOR get_customer_name(c_customer_id NUMBER) IS
2906: IF l_debug_on THEN
2907: WSH_DEBUG_SV.push(l_module_name);
2908: END IF;
2909: -- set default return status to SUCCESS
2910: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2911: -- clear up x_selected_det_tbl table
2912: x_selected_det_tbl.delete;
2913:
2914: IF p_autocreate_deliveries is NULL OR p_autocreate_deliveries='SP' THEN
2964: p_lookup_type => 'WSH_PD_DEL_LINE_STATUS' );
2965: FND_MESSAGE.SET_TOKEN('DETAILS_STATUS', l_msg_string);
2966: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
2967:
2968: WSH_UTIL_CORE.PrintMsg(' Delivery Lines Status: '|| p_select_criteria.delivery_lines_status);
2969: END IF;
2970:
2971: -- put organization in the where clause if it is part of selection criteria
2972: IF p_select_criteria.organization_id IS NOT NULL THEN
2971: -- put organization in the where clause if it is part of selection criteria
2972: IF p_select_criteria.organization_id IS NOT NULL THEN
2973: l_sc_WHERE := l_sc_WHERE ||'AND wdd.organization_id = :x_organization_id ';
2974: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
2975: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_select_criteria.organization_id));
2976: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
2977:
2978: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_select_criteria.organization_id);
2979: END IF;
2974: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
2975: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_select_criteria.organization_id));
2976: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
2977:
2978: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_select_criteria.organization_id);
2979: END IF;
2980:
2981:
2982: -- put scheduled ship date in the where clause if it is part of selection criteria
2996: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_HI');
2997: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
2998: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
2999:
3000: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3001: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3002:
3003: ELSIF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3004: l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
2997: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
2998: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
2999:
3000: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3001: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3002:
3003: ELSIF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3004: l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
3005: l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled >= :x_scheduled_ship_date_lo ';
3006:
3007: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_LO');
3008: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_LO', to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3009: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3010: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3011:
3012: ELSE
3013: l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
3014: l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled <= :x_scheduled_ship_date_hi ';
3014: l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled <= :x_scheduled_ship_date_hi ';
3015: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_HI');
3016: FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3017: FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3018: WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3019: END IF;
3020: END IF;
3021:
3022: -- put ship to location id in the where clause if it is part of selection criteria
3022: -- put ship to location id in the where clause if it is part of selection criteria
3023: IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
3024: l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_to_location_id = :x_ship_to_loc_id ';
3025:
3026: l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3027: p_location_id => p_select_criteria.ship_to_loc_id,
3028: p_format => 'NEW UI CODE'),
3029: 1, 80);
3030:
3032: FND_MESSAGE.SET_TOKEN('SHIP_TO', l_msg_string);
3033:
3034: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3035:
3036: WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_select_criteria.ship_to_loc_id));
3037: END IF;
3038:
3039: -- put source code in the where clause if it is part of selection criteria
3040: IF p_select_criteria.source_code IS NOT NULL THEN
3050: p_lookup_type => 'WSH_PD_SOURCE_SYSTEM' );
3051: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SOURCE_SYSTEM');
3052: FND_MESSAGE.SET_TOKEN('SOURCE_NAME', l_msg_string);
3053: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3054: WSH_UTIL_CORE.PrintMsg(' Source System: '|| p_select_criteria.source_code);
3055: ELSE
3056: l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code in (''OE'', ''OKE'') ';
3057: END IF;
3058:
3070: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_CUSTOMER');
3071: FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_msg_string);
3072: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3073:
3074: WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| p_select_criteria.customer_id);
3075: END IF;
3076:
3077: -- put ship method code in the where clause if it is part of selection criteria
3078: IF p_select_criteria.ship_method_code IS NOT NULL THEN
3083: p_lookup_code => p_select_criteria.ship_method_code,
3084: p_lookup_type => 'SHIP_METHOD' );
3085: FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_msg_string);
3086: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3087: WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_select_criteria.ship_method_code);
3088: END IF;
3089:
3090: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3091: FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_select_criteria.log_level));
3089:
3090: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3091: FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_select_criteria.log_level));
3092: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3093: WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_select_criteria.log_level);
3094:
3095: l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
3096:
3097:
3104: IF i > l_str_length THEN
3105: EXIT;
3106: END IF;
3107: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3108: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3109: WSH_UTIL_CORE.PrintMsg(l_sub_str);
3110: i := i + 80;
3111: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3112: END LOOP;
3105: EXIT;
3106: END IF;
3107: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3108: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3109: WSH_UTIL_CORE.PrintMsg(l_sub_str);
3110: i := i + 80;
3111: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3112: END LOOP;
3113: END IF;
3107: l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3108: -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3109: WSH_UTIL_CORE.PrintMsg(l_sub_str);
3110: i := i + 80;
3111: -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3112: END LOOP;
3113: END IF;
3114:
3115: IF l_debug_on THEN
3238: -- if the cursor is still open then close the cursor
3239: IF v_cursorID <> 0 THEN
3240: DBMS_SQL.Close_Cursor(v_cursorID);
3241: END IF;
3242: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
3243: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3244: IF l_debug_on THEN
3245: WSH_DEBUG_SV.pop(l_module_name);
3246: END IF;
3239: IF v_cursorID <> 0 THEN
3240: DBMS_SQL.Close_Cursor(v_cursorID);
3241: END IF;
3242: wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
3243: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3244: IF l_debug_on THEN
3245: WSH_DEBUG_SV.pop(l_module_name);
3246: END IF;
3247:
3271: -- x_return_status Return status
3272: ---- ----------------------------------------------------------------------
3273:
3274: PROCEDURE Process_Delivery_Lines_Batch(
3275: p_selected_det_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
3276: p_append_flag IN VARCHAR2,
3277: p_ac_del_criteria IN VARCHAR2,
3278: x_appended_det_num OUT NOCOPY NUMBER,
3279: x_autocreate_del_det_num OUT NOCOPY NUMBER,
3282: x_msg_count OUT NOCOPY NUMBER,
3283: x_msg_data OUT NOCOPY VARCHAR2,
3284: x_return_status OUT NOCOPY VARCHAR2) IS
3285:
3286: l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3287: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3288: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3289: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3290: l_append_flag VARCHAR2(1);
3283: x_msg_data OUT NOCOPY VARCHAR2,
3284: x_return_status OUT NOCOPY VARCHAR2) IS
3285:
3286: l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3287: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3288: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3289: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3290: l_append_flag VARCHAR2(1);
3291: l_ac_del_criteria VARCHAR2(1);
3285:
3286: l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3287: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3288: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3289: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3290: l_append_flag VARCHAR2(1);
3291: l_ac_del_criteria VARCHAR2(1);
3292: l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
3293: l_action_out_rec wsh_glbl_var_strct_grp.dd_action_out_rec_type;
3317: IF l_debug_on THEN
3318: WSH_DEBUG_SV.push(l_module_name);
3319: END IF;
3320:
3321: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3322: x_appended_det_num := 0;
3323: x_autocreate_del_det_num := 0;
3324: x_new_del_num := 0;
3325: l_appended_det_tbl.delete;
3379: 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);
3380: END IF;
3381:
3382: -- handle return status from WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries
3383: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3384: raise append_to_deliveries_failed;
3385: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3386: l_warning_num := l_warning_num + 1;
3387: END IF;
3381:
3382: -- handle return status from WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries
3383: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3384: raise append_to_deliveries_failed;
3385: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3386: l_warning_num := l_warning_num + 1;
3387: END IF;
3388:
3389: ELSIF l_append_flag = 'N' THEN
3424: x_msg_count := x_msg_count + l_number_of_warnings + l_number_of_errors;
3425: x_msg_data := x_msg_data || l_msg_data;
3426:
3427: -- handle return status from wsh_interface_grp.delivery_detail_action
3428: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3429: raise autocreate_delivery_failed;
3430: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3431: l_warning_num := l_warning_num + 1;
3432: END IF;
3426:
3427: -- handle return status from wsh_interface_grp.delivery_detail_action
3428: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3429: raise autocreate_delivery_failed;
3430: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3431: l_warning_num := l_warning_num + 1;
3432: END IF;
3433:
3434: IF l_warning_num > 0 THEN
3431: l_warning_num := l_warning_num + 1;
3432: END IF;
3433:
3434: IF l_warning_num > 0 THEN
3435: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3436: END IF;
3437:
3438:
3439: END IF;
3448: EXCEPTION
3449:
3450: WHEN append_to_deliveries_failed THEN
3451: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3452: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3453: IF l_debug_on THEN
3454: WSH_DEBUG_SV.logmsg(l_module_name,'APPEND_TO_DELIVERIES_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3455: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APPEND_TO_DELIVERIES_FAILED');
3456: END IF;
3456: END IF;
3457:
3458: WHEN autocreate_delivery_failed THEN
3459: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3460: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3461: IF l_debug_on THEN
3462: WSH_DEBUG_SV.logmsg(l_module_name,'AUTOCREATE_DELIVERY_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3463: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:AUTOCREATE_DELIVERY_FAILED');
3464: END IF;
3465:
3466: WHEN OTHERS THEN
3467: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3468: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3469: wsh_util_core.add_message(x_return_status, l_module_name);
3470: WSH_UTIL_CORE.default_handler('WSH_BATCH_PROCESS.Process_Delivery_Lines_Batch');
3471: FND_MSG_PUB.Count_And_Get
3472: (
3473: p_count => x_msg_count,
3466: WHEN OTHERS THEN
3467: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3468: ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3469: wsh_util_core.add_message(x_return_status, l_module_name);
3470: WSH_UTIL_CORE.default_handler('WSH_BATCH_PROCESS.Process_Delivery_Lines_Batch');
3471: FND_MSG_PUB.Count_And_Get
3472: (
3473: p_count => x_msg_count,
3474: p_data => x_msg_data,
3569: l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
3570: l_appended_det_num NUMBER;
3571: l_appended_del_num NUMBER;
3572: l_autocreate_del_det_num NUMBER;
3573: l_selected_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3574: l_msg_count NUMBER;
3575: l_msg_data VARCHAR2(32767);
3576: l_new_del_num NUMBER;
3577: l_del_num NUMBER;
3602: Parameters_ERR EXCEPTION;
3603:
3604: l_processed number ;
3605: l_inner_loop_count number ;
3606: l_selected_det_tbl_tmp WSH_UTIL_CORE.Id_Tab_Type;
3607: g_selected_det_count number :=0;
3608: g_appended_det_num number :=0;
3609: g_appended_del_num number :=0;
3610: g_autocreate_del_det_num number :=0;
3639: l_log_level := p_log_level;
3640: END IF;
3641:
3642: -- enable printing of the concurrent request
3643: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
3644: -- set the log level for the whole session
3645: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
3646:
3647:
3641:
3642: -- enable printing of the concurrent request
3643: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
3644: -- set the log level for the whole session
3645: WSH_UTIL_CORE.Set_Log_Level(l_log_level);
3646:
3647:
3648: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PARM');
3649: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3657: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3658:
3659:
3660: -- print required input parameters
3661: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
3662: WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3663:
3664: IF p_entity_type = 'D' THEN
3665: --
3658:
3659:
3660: -- print required input parameters
3661: WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
3662: WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3663:
3664: IF p_entity_type = 'D' THEN
3665: --
3666: IF WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
3662: WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3663:
3664: IF p_entity_type = 'D' THEN
3665: --
3666: IF WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
3667: FND_FILE.put_line(FND_FILE.output, ' ');
3668: FND_FILE.put_line(FND_FILE.output, ' ');
3669: -- print the summary results of auto create trips action
3670: FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_TRIP_CONSOLIDATION');
3668: FND_FILE.put_line(FND_FILE.output, ' ');
3669: -- print the summary results of auto create trips action
3670: FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_TRIP_CONSOLIDATION');
3671: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3672: WSH_UTIL_CORE.PrintMsg('Trip Consolidation is disabled because Transportation Planning is installed. ');
3673: ELSE
3674: --
3675: l_pickup_date_lo := fnd_date.canonical_to_date(p_pickup_date_lo);
3676: l_pickup_date_hi := fnd_date.canonical_to_date(p_pickup_date_hi);
3682: p_scheduled_ship_date_hi is not NULL THEN
3683: FND_MESSAGE.SET_NAME('WSH', 'WSH_SCHD_DATE_NOT_ALLOWED');
3684: FND_FILE.put_line(FND_FILE.output,' ');
3685: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3686: 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.');
3687: raise Parameters_ERR;
3688: END IF;
3689:
3690: -- Deliveries Only, auto create trips
3727: p_lookup_code => p_deliveries_status,
3728: p_lookup_type => 'WSH_PD_DEL_STATUS' );
3729: FND_MESSAGE.SET_TOKEN('DELIVERIES_STATUS', l_msg_string);
3730: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3731: WSH_UTIL_CORE.PrintMsg(' Deliveries Status: '|| l_deliveries_status);
3732: END IF;
3733:
3734: IF p_pickup_date_lo IS NOT NULL THEN
3735: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_LO');
3734: IF p_pickup_date_lo IS NOT NULL THEN
3735: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_LO');
3736: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_LO', to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3737: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3738: WSH_UTIL_CORE.PrintMsg(' Pick Up Date Start: '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3739: END IF;
3740:
3741: IF p_pickup_date_hi IS NOT NULL THEN
3742: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_HI');
3741: IF p_pickup_date_hi IS NOT NULL THEN
3742: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_HI');
3743: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_HI', to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3744: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3745: WSH_UTIL_CORE.PrintMsg(' Pick Up Date End: '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3746: END IF;
3747:
3748: -- bug 3332670
3749: IF p_pickup_date_hi is NOT NULL and
3753: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_LO', to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3754: FND_MESSAGE.SET_TOKEN('PICKUP_DATE_HI', to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3755: FND_FILE.put_line(FND_FILE.output,' ');
3756: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3757: 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'));
3758: raise Parameters_ERR;
3759: END IF;
3760: END IF;
3761:
3762: IF p_dropoff_date_lo IS NOT NULL THEN
3763: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_LO');
3764: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_LO', to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3765: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3766: WSH_UTIL_CORE.PrintMsg(' Drop Off Date Start: '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3767: END IF;
3768:
3769: IF p_dropoff_date_hi IS NOT NULL THEN
3770: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_HI');
3769: IF p_dropoff_date_hi IS NOT NULL THEN
3770: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_HI');
3771: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_HI', to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3772: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3773: WSH_UTIL_CORE.PrintMsg(' Drop Off Date End: '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3774: END IF;
3775:
3776: -- bug 3332670
3777: IF p_dropoff_date_hi is NOT NULL and
3781: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_LO', to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3782: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_HI', to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3783: FND_FILE.put_line(FND_FILE.output,' ');
3784: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3785: 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'));
3786: raise Parameters_ERR;
3787: END IF;
3788: END IF;
3789:
3788: END IF;
3789:
3790: IF p_organization_id IS NOT NULL THEN
3791: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3792: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
3793: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3794: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| to_char(p_organization_id));
3795: END IF;
3796:
3790: IF p_organization_id IS NOT NULL THEN
3791: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3792: FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
3793: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3794: WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| to_char(p_organization_id));
3795: END IF;
3796:
3797: IF p_customer_id IS NOT NULL THEN
3798:
3803: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_CUSTOMER');
3804: FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_msg_string);
3805: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3806: --bug fix 3286811
3807: WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| (p_customer_id));
3808: END IF;
3809:
3810: IF p_ship_to_loc_id IS NOT NULL THEN
3811: l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3807: WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| (p_customer_id));
3808: END IF;
3809:
3810: IF p_ship_to_loc_id IS NOT NULL THEN
3811: l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3812: p_location_id => p_ship_to_loc_id,
3813: p_format => 'NEW UI CODE'),
3814: 1, 80);
3815:
3815:
3816: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_TO');
3817: FND_MESSAGE.SET_TOKEN('SHIP_TO', l_msg_string);
3818: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3819: WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_ship_to_loc_id));
3820: END IF;
3821:
3822: IF p_ship_method_code IS NOT NULL THEN
3823:
3827: p_lookup_code => p_ship_method_code,
3828: p_lookup_type => 'SHIP_METHOD' );
3829: FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_msg_string);
3830: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3831: WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_ship_method_code);
3832: END IF;
3833:
3834: IF l_grp_ship_method IS NOT NULL THEN
3835: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_GRP_SHIP_METHOD');
3838: p_lookup_code => l_grp_ship_method,
3839: p_lookup_type => 'YES_NO' );
3840: FND_MESSAGE.SET_TOKEN('GRP_SHIP_METHOD', l_msg_string);
3841: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3842: WSH_UTIL_CORE.PrintMsg(' Group Deliveries by Ship Method: '|| l_grp_ship_method);
3843: END IF;
3844:
3845: IF l_grp_ship_from IS NOT NULL THEN
3846: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_GRP_SHIP_FROM');
3849: p_lookup_code => l_grp_ship_from,
3850: p_lookup_type => 'YES_NO' );
3851: FND_MESSAGE.SET_TOKEN('GRP_SHIP_FROM', l_msg_string);
3852: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3853: WSH_UTIL_CORE.PrintMsg(' Group Deliveries by Ship from Organization: '|| l_grp_ship_from);
3854: END IF;
3855:
3856: IF l_max_del_number IS NOT NULL THEN
3857: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MAX_DEL_NUMBER');
3856: IF l_max_del_number IS NOT NULL THEN
3857: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MAX_DEL_NUMBER');
3858: FND_MESSAGE.SET_TOKEN('MAX_DEL_NUMBER', to_char(l_max_del_number));
3859: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3860: WSH_UTIL_CORE.PrintMsg(' Maximum Number of Deliveries per Trip: '|| to_char(l_max_del_number));
3861: END IF;
3862:
3863: IF l_log_level IS NOT NULL THEN
3864: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3863: IF l_log_level IS NOT NULL THEN
3864: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3865: FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(l_log_level));
3866: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3867: WSH_UTIL_CORE.PrintMsg(' Log Level: '|| l_log_level );
3868: END IF;
3869:
3870: IF l_debug_on THEN
3871: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips',WSH_DEBUG_SV.C_PROC_LEVEL);
3908: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_TRIPS_CREATED');
3909: FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRIPS', to_char(l_trip_num));
3910: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3911:
3912: WSH_UTIL_CORE.PrintDateTime;
3913: WSH_UTIL_CORE.PrintMsg('Summary: ');
3914: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
3915: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
3916: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
3909: FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRIPS', to_char(l_trip_num));
3910: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3911:
3912: WSH_UTIL_CORE.PrintDateTime;
3913: WSH_UTIL_CORE.PrintMsg('Summary: ');
3914: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
3915: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
3916: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
3917:
3910: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3911:
3912: WSH_UTIL_CORE.PrintDateTime;
3913: WSH_UTIL_CORE.PrintMsg('Summary: ');
3914: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
3915: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
3916: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
3917:
3918: -- print return status of autocreate trips
3911:
3912: WSH_UTIL_CORE.PrintDateTime;
3913: WSH_UTIL_CORE.PrintMsg('Summary: ');
3914: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
3915: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
3916: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
3917:
3918: -- print return status of autocreate trips
3919: IF l_debug_on THEN
3912: WSH_UTIL_CORE.PrintDateTime;
3913: WSH_UTIL_CORE.PrintMsg('Summary: ');
3914: WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
3915: WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
3916: WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
3917:
3918: -- print return status of autocreate trips
3919: IF l_debug_on THEN
3920: 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);
3919: IF l_debug_on THEN
3920: 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);
3921: END IF;
3922:
3923: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3924: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3925: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
3926: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3927: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
3920: 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);
3921: END IF;
3922:
3923: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3924: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3925: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
3926: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3927: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
3928: END IF;
3922:
3923: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3924: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3925: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
3926: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3927: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
3928: END IF;
3929:
3930: FND_FILE.put_line(FND_FILE.output,' ');
3937: IF l_msg_count > 0 THEN
3938: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
3939: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3940: FND_FILE.put_line(FND_FILE.output,'====================');
3941: WSH_UTIL_CORE.PrintMsg('List of Messages: ');
3942: WSH_UTIL_CORE.PrintMsg('====================');
3943: FOR i in 1..l_msg_count LOOP
3944: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
3945: p_msg_index => i,
3938: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
3939: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3940: FND_FILE.put_line(FND_FILE.output,'====================');
3941: WSH_UTIL_CORE.PrintMsg('List of Messages: ');
3942: WSH_UTIL_CORE.PrintMsg('====================');
3943: FOR i in 1..l_msg_count LOOP
3944: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
3945: p_msg_index => i,
3946: p_data => l_buffer,
3946: p_data => l_buffer,
3947: p_msg_index_out => l_index_out);
3948: IF l_buffer IS NOT NULL THEN
3949: FND_FILE.put_line(FND_FILE.output,substrb(l_buffer,1,2000));
3950: WSH_UTIL_CORE.PrintMsg(substrb(l_buffer,1,2000));
3951: END IF;
3952: END LOOP;
3953:
3954: END IF;
3967: p_pickup_date_hi is not NULL THEN
3968: FND_MESSAGE.SET_NAME('WSH', 'WSH_PICKUP_DATE_NOT_ALLOWED');
3969: FND_FILE.put_line(FND_FILE.output,' ');
3970: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3971: 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.');
3972: raise Parameters_ERR;
3973: END IF;
3974:
3975: -- bug 3319789
3977: p_dropoff_date_hi is not NULL THEN
3978: FND_MESSAGE.SET_NAME('WSH', 'WSH_DROPOFF_DATE_NOT_ALLOWED');
3979: FND_FILE.put_line(FND_FILE.output,' ');
3980: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3981: 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.');
3982: raise Parameters_ERR;
3983: END IF;
3984:
3985: -- set default value of Delivery Lines Status to 'ALL'
3999: FND_MESSAGE.SET_TOKEN('SCHEDULED_DATE_LO', to_char(l_scheduled_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
4000: FND_MESSAGE.SET_TOKEN('SCHEDULED_DATE_HI', to_char(l_scheduled_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
4001: FND_FILE.put_line(FND_FILE.output,' ');
4002: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4003: 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'));
4004: raise Parameters_ERR;
4005: END IF;
4006: END IF;
4007:
4053:
4054:
4055: -- turn off append deliveries if TP is installed OR lines are not from OE OR
4056: -- lines status is not Release to Warehouse or Ready to Release
4057: -- IF WSH_UTIL_CORE.TP_Is_Installed = 'Y'
4058: IF p_source_system <> 'OE'
4059: OR p_delivery_lines_status in ('ALL', 'X', 'Y')
4060: OR p_organization_id is NULL THEN
4061: l_append_deliveries := 'N';
4120: IF l_debug_on THEN
4121: WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Select_Deliveries_Lines is '|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
4122: END IF;
4123:
4124: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4125: -- select_delivery_lines failed, exit
4126: raise WSH_SELECT_ERR;
4127: END IF;
4128:
4164: x_msg_count => l_msg_count,
4165: x_msg_data => l_msg_data,
4166: x_return_status => l_return_status);
4167:
4168: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4169: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4170: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4171: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4172: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4165: x_msg_data => l_msg_data,
4166: x_return_status => l_return_status);
4167:
4168: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4169: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4170: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4171: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4172: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4173: END IF;
4167:
4168: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4169: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4170: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4171: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4172: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4173: END IF;
4174: FND_FILE.put_line(FND_FILE.output,' ');
4175: FND_FILE.put_line(FND_FILE.output,' ');
4182: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
4183: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4184: FND_FILE.put_line(FND_FILE.output,'====================');
4185:
4186: WSH_UTIL_CORE.PrintMsg('====================');
4187: FOR i in 1..l_msg_count LOOP
4188: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4189: p_msg_index => i,
4190: p_data => l_buffer,
4190: p_data => l_buffer,
4191: p_msg_index_out => l_index_out);
4192: IF l_buffer IS NOT NULL THEN
4193: FND_FILE.put_line(FND_FILE.output,substrb(l_buffer,1,2000));
4194: WSH_UTIL_CORE.PrintMsg(substrb(l_buffer,1,2000));
4195: END IF;
4196: END LOOP;
4197:
4198: END IF;
4255: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_CREATED');
4256: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(g_new_del_num));
4257: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4258:
4259: WSH_UTIL_CORE.PrintDateTime;
4260: WSH_UTIL_CORE.PrintMsg('Summary: ');
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4256: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(g_new_del_num));
4257: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4258:
4259: WSH_UTIL_CORE.PrintDateTime;
4260: WSH_UTIL_CORE.PrintMsg('Summary: ');
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4264: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4257: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4258:
4259: WSH_UTIL_CORE.PrintDateTime;
4260: WSH_UTIL_CORE.PrintMsg('Summary: ');
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4264: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4265: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4258:
4259: WSH_UTIL_CORE.PrintDateTime;
4260: WSH_UTIL_CORE.PrintMsg('Summary: ');
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4264: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4265: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4266:
4259: WSH_UTIL_CORE.PrintDateTime;
4260: WSH_UTIL_CORE.PrintMsg('Summary: ');
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4264: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4265: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4266:
4267:
4260: WSH_UTIL_CORE.PrintMsg('Summary: ');
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4264: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4265: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4266:
4267:
4268: ELSE
4261: WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4262: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4263: WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4264: WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4265: WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4266:
4267:
4268: ELSE
4269: -- no delivery lines selected
4276: FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
4277: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(l_selected_det_tbl.count));
4278: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4279:
4280: WSH_UTIL_CORE.PrintDateTime;
4281: WSH_UTIL_CORE.PrintMsg('Summary: ');
4282: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4283:
4284: END IF;
4277: FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(l_selected_det_tbl.count));
4278: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4279:
4280: WSH_UTIL_CORE.PrintDateTime;
4281: WSH_UTIL_CORE.PrintMsg('Summary: ');
4282: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4283:
4284: END IF;
4285:
4278: FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4279:
4280: WSH_UTIL_CORE.PrintDateTime;
4281: WSH_UTIL_CORE.PrintMsg('Summary: ');
4282: WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4283:
4284: END IF;
4285:
4286: END IF;
4314: WSH_DEBUG_SV.pop(l_module_name);
4315: END IF;
4316:
4317: WHEN WSH_SELECT_ERR THEN
4318: WSH_UTIL_CORE.PrintMsg('Error Messages: ' || l_msg_data);
4319: l_completion_status := 'ERROR';
4320: WSH_UTIL_CORE.PrintMsg('Failed to select delivery lines for processing');
4321: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4322: errbuf := 'Process Deliveries is completed with warning';
4316:
4317: WHEN WSH_SELECT_ERR THEN
4318: WSH_UTIL_CORE.PrintMsg('Error Messages: ' || l_msg_data);
4319: l_completion_status := 'ERROR';
4320: WSH_UTIL_CORE.PrintMsg('Failed to select delivery lines for processing');
4321: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4322: errbuf := 'Process Deliveries is completed with warning';
4323: retcode := '1';
4324: IF l_debug_on THEN
4338: WHEN OTHERS THEN
4339: l_completion_status := 'ERROR';
4340: l_error_code := SQLCODE;
4341: l_error_text := SQLERRM;
4342: WSH_UTIL_CORE.PrintMsg('Process Deliveries SRS failed with unexpected error.');
4343: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4344: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4345: errbuf := 'Process Deliveries failed with unexpected error';
4346: retcode := '2';
4339: l_completion_status := 'ERROR';
4340: l_error_code := SQLCODE;
4341: l_error_text := SQLERRM;
4342: WSH_UTIL_CORE.PrintMsg('Process Deliveries SRS failed with unexpected error.');
4343: WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4344: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4345: errbuf := 'Process Deliveries failed with unexpected error';
4346: retcode := '2';
4347: IF l_debug_on THEN