[Home] [Help]
PACKAGE BODY: APPS.WSH_BATCH_PROCESS
Source
1 PACKAGE BODY WSH_BATCH_PROCESS as
2 /* $Header: WSHBHPSB.pls 120.10 2006/12/13 18:31:19 rlanka noship $ */
3
4 G_SHIP_CONFIRM CONSTANT VARCHAR2(5) := 'SC';
5 G_AUTO_PACK CONSTANT VARCHAR2(5) := 'AP';
6 g_error_message VARCHAR2(30);
7 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_BATCH_PROCESS';
8
9
10 -- -----------------------------------------------------------------------
11 -- Name
12 -- PROCEDURE Select_Deliveries
13 -- Purpose
14 -- This procedure is called by Ship_Confirm_Deliveries_SRS
15 -- and Auto_Pack_Deliveries_SRS to select deliveries according
16 -- to users entered quiteria.
17 -- Input Parameters:
18 -- p_input_info : this record contains all the input parameters
19 -- p_batch_rec : this record returns the fields for creating batch record in wsh_picking_batches table
20 -- Output Parameters:
21 -- x_selected_del_tab: returns a list of delivery ids which are selected according to the criteria
22 -- x_return_status - Success, Warning, Error, Unexpected Error
23 -- ----------------------------------------------------------------------
24
25 PROCEDURE Select_Deliveries (
26 p_input_info IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
27 p_batch_rec IN OUT NOCOPY WSH_PICKING_BATCHES%ROWTYPE,
28 x_selected_del_tab OUT NOCOPY WSH_BATCH_PROCESS.Del_Info_Tab,
29 x_return_status OUT NOCOPY VARCHAR2)
30 IS
31
32 l_sc_SELECT VARCHAR2(3000) := NULL;
33 l_sc_FROM VARCHAR2(3000) := NULL;
34 l_sc_WHERE VARCHAR2(3000) := NULL;
35 l_sc_EXISTS_BOL VARCHAR2(3000) := NULL;
36 l_sc_EXISTS VARCHAR2(3000) := NULL;
37 l_sc_NOT_EXISTS VARCHAR2(3000) := NULL;
38 l_sc_FINAL VARCHAR2(4000) := NULL;
39 l_sub_str VARCHAR2(2000);
40 l_str_length NUMBER := 0;
41 l_pickup_date_lo DATE;
42 l_pickup_date_hi DATE;
43 l_dropoff_date_lo DATE;
44 l_dropoff_date_hi DATE;
45 i NUMBER := 0;
46 v_delivery_id NUMBER := 0;
47 v_organization_id NUMBER := 0;
48 v_initial_pickup_location_id NUMBER := 0;
49 v_cursorID INTEGER;
50 v_ignore INTEGER;
51 l_debug_on BOOLEAN;
52 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Select_Deliveries';
53
54 BEGIN
55 --
56 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
57 --
58 IF l_debug_on IS NULL
59 THEN
60 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
61 END IF;
62 --
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
72 l_sc_FROM := l_sc_FROM || 'wsh_new_deliveries wnd ';
73
74 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
75 l_sc_WHERE := l_sc_WHERE || 'NVL(wnd.auto_sc_exclude_flag, ''N'') = ''N'' ';
76 ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
77 l_sc_WHERE := l_sc_WHERE || 'NVL(wnd.auto_ap_exclude_flag, ''N'') = ''N'' ';
78 END IF;
79
80 l_sc_WHERE := l_sc_WHERE || 'AND wnd.status_code = ''OP'' ';
81 -- J Inbound Logistics jckwok
82 l_sc_WHERE := l_sc_WHERE || 'AND nvl(wnd.SHIPMENT_DIRECTION , ''O'') IN (''O'', ''IO'') ';
83 -- end of Inbound Logistics changes
84
85 -- R12 MDC changes. Do not select consolidation deliveries. Only standard deliveries to be included
86
87 l_sc_WHERE := l_sc_WHERE || 'AND wnd.DELIVERY_TYPE = ''STANDARD'' ';
88
89 l_sc_EXISTS := l_sc_EXISTS ||' SELECT wdd.delivery_detail_id ';
90 l_sc_EXISTS := l_sc_EXISTS ||' FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda ';
91 l_sc_EXISTS := l_sc_EXISTS ||' WHERE wda.delivery_detail_id = wdd.delivery_detail_id ';
92 l_sc_EXISTS := l_sc_EXISTS ||' AND wda.delivery_id = wnd.delivery_id ';
93 l_sc_EXISTS := l_sc_EXISTS ||' AND wda.delivery_id IS NOT NULL ';
94 l_sc_EXISTS := l_sc_EXISTS ||' AND wdd.container_flag = ''N'' ';
95
96 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
97 l_sc_EXISTS := l_sc_EXISTS ||' AND wdd.released_status in (''Y'', ''X'') ';
98 ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
99 l_sc_EXISTS := l_sc_EXISTS ||' AND wdd.released_status in (''Y'', ''X'', ''R'', ''B'') ';
100 END IF;
101
102 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' SELECT wdd2.delivery_detail_id ';
103 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' FROM wsh_delivery_details wdd2, wsh_delivery_assignments_v wda2 ';
104 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' WHERE wnd.delivery_id = wda2.delivery_id ';
105 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wda2.delivery_id IS NOT NULL ';
106 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wda2.delivery_detail_id = wdd2.delivery_detail_id ';
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
116
117 l_sc_EXISTS := l_sc_EXISTS ||'AND wdd.batch_id = :x_pr_batch_id ';
118
119 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
120 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND (wdd2.released_status in (''R'', ''S'', ''B'', ''C'', ''N'') OR wdd2.batch_id <> :x_pr_batch_id ) ';
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
130 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
131 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.released_status in (''R'', ''S'', ''B'', ''C'', ''N'') ';
132 ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
133 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.released_status in (''S'', ''C'', ''N'') ';
134 END IF;
135
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
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);
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
161 IF (p_input_info.bol_number_lo IS NOT NULL OR p_input_info.bol_number_hi IS NOT NULL) THEN
162 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' SELECT wdi.document_instance_id ';
163 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' FROM wsh_delivery_legs wlg ';
164 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' ,wsh_document_instances wdi ';
165 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' WHERE wnd.delivery_id = wlg.delivery_id ';
166 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wlg.delivery_leg_id = wdi.entity_id ';
167 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wdi.entity_name= ''WSH_DELIVERY_LEGS'' ';
168 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wdi.document_type =''BOL'' ';
169 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wdi.status <> ''CANCELED'' ';
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;
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 ';
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
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
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
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
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
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
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
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
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
250 IF p_input_info.pickup_date_lo IS NOT NULL AND 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
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
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
276 IF p_input_info.dropoff_date_lo IS NOT NULL OR p_input_info.dropoff_date_hi IS NOT NULL THEN
277
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
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);
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
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 || ' ) ';
306 END IF;
307 l_sc_FINAL := l_sc_FINAL || ' AND EXISTS ( '|| l_sc_EXISTS ||' ) AND NOT EXISTS ( '|| l_sc_NOT_EXISTS||' )';
308
309 IF p_input_info.log_level > 0 OR l_debug_on THEN
310 -- print SELECT statement
311 i := 1;
312 l_str_length := length(l_sc_FINAL);
313
314 LOOP
315 IF i > l_str_length THEN
316 EXIT;
317 END IF;
318 l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
322 -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
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;
323 END LOOP;
324 END IF;
325
326 v_CursorID := DBMS_SQL.Open_Cursor;
327
328 DBMS_SQL.Parse(v_CursorID, l_sc_FINAL, DBMS_SQL.v7 );
329
330 DBMS_SQL.Define_Column(v_CursorID, 1, v_delivery_id);
331 DBMS_SQL.Define_Column(v_CursorID, 2, v_organization_id);
332 DBMS_SQL.Define_Column(v_CursorID, 3, v_initial_pickup_location_id);
333
334 IF p_input_info.organization_id IS NOT NULL THEN
335 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_organization_id', p_input_info.organization_id);
336 END IF;
337
338 IF p_input_info.pr_batch_id IS NOT NULL THEN
339 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pr_batch_id', p_input_info.pr_batch_id);
340 END IF;
341
342 IF p_input_info.ap_batch_id IS NOT NULL THEN
343 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ap_batch_id', p_input_info.ap_batch_id);
344 END IF;
345
346 IF p_input_info.delivery_name_lo IS NOT NULL THEN
347 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_delivery_name_lo', p_input_info.delivery_name_lo);
348 END IF;
349
350 IF p_input_info.delivery_name_hi IS NOT NULL THEN
351 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_delivery_name_hi', p_input_info.delivery_name_hi);
352 END IF;
353
354
355 IF p_input_info.bol_number_lo IS NOT NULL THEN
356 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_bol_number_lo', p_input_info.bol_number_lo) ;
357 END IF;
358
359 IF p_input_info.bol_number_hi IS NOT NULL THEN
360 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_bol_number_hi', p_input_info.bol_number_hi);
361 END IF;
362
363
364 IF p_input_info.planned_flag IS NOT NULL THEN
365 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_planned_flag', p_input_info.planned_flag);
366 END IF;
367
368 IF p_input_info.ship_from_loc_id IS NOT NULL THEN
369 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_from_loc_id', p_input_info.ship_from_loc_id);
370 END IF;
371
372 IF p_input_info.ship_to_loc_id IS NOT NULL THEN
373 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_to_loc_id', p_input_info.ship_to_loc_id);
374 END IF;
375
376 IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
377 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_intmed_ship_to_loc_id', p_input_info.intmed_ship_to_loc_id);
378 END IF;
379
380 IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
381 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pooled_ship_to_loc_id', p_input_info.pooled_ship_to_loc_id);
382 END IF;
383
384 IF p_input_info.customer_id IS NOT NULL THEN
385 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_customer_id', p_input_info.customer_id);
386 END IF;
387
388 IF p_input_info.ship_method_code IS NOT NULL THEN
389 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_method_code', p_input_info.ship_method_code);
390 END IF;
391
392 IF p_input_info.fob_code IS NOT NULL THEN
393 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_fob_code', p_input_info.fob_code);
394 END IF;
395
396 IF p_input_info.freight_terms_code IS NOT NULL THEN
397 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_freight_terms_code', p_input_info.freight_terms_code);
398 END IF;
399
400 IF p_input_info.pickup_date_lo IS NOT NULL THEN
401 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pickup_date_lo', l_pickup_date_lo);
402 END IF;
403
404 IF p_input_info.pickup_date_hi IS NOT NULL THEN
405 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pickup_date_hi', l_pickup_date_hi);
406 END IF;
407
408 IF p_input_info.dropoff_date_lo IS NOT NULL THEN
409 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_dropoff_date_lo', l_dropoff_date_lo);
410 END IF;
411
412 IF p_input_info.dropoff_date_hi IS NOT NULL THEN
413 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_dropoff_date_hi', l_dropoff_date_hi);
414 END IF;
415
416 v_ignore := DBMS_SQL.Execute(v_CursorID);
417
418 LOOP
419 IF DBMS_SQL.Fetch_Rows(v_cursorID) = 0 THEN
420 DBMS_SQL.Close_Cursor(v_cursorID);
421 EXIT;
422 ELSE
423 DBMS_SQL.Column_Value(v_CursorID, 1, v_delivery_id);
424 DBMS_SQL.Column_Value(v_CursorID, 2, v_organization_id);
425 DBMS_SQL.Column_Value(v_CursorID, 3, v_initial_pickup_location_id);
426 x_selected_del_tab(x_selected_del_tab.count+1).delivery_id := v_delivery_id;
427 x_selected_del_tab(x_selected_del_tab.count).organization_id := v_organization_id;
428 x_selected_del_tab(x_selected_del_tab.count).initial_pickup_location_id := v_initial_pickup_location_id;
429
430 END IF;
431 END LOOP;
432
433 IF l_debug_on THEN
434 WSH_DEBUG_SV.log(l_module_name, to_char(x_selected_del_tab.count)||' deliveries fetched to be processed');
435 WSH_DEBUG_SV.pop(l_module_name);
436 END IF;
437
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;
447
448 END Select_Deliveries;
449
450
451
452 -- -----------------------------------------------------------------------
453 -- Name
454 -- PROCEDURE Ship_Confirm_A_Delivery
455 --
456 -- Purpose:
460 -- catch the exception when a delivery cannot be locked within a loop and
457 -- This PRIVATE procedure ship confirm a delivery, it is called by
458 -- Ship_Confirm_Batch in a loop.
459 -- It is necessary to make a saperate procedure, othewise it fails to
461 -- it will exit all the way out of the delivery loop so no deliveries can be
462 -- ship confirmed after the exception
463 --
464 -- Input Parameters:
465 -- p_delivery_id - the delivery id to be ship cofirmed
466 -- p_sc_batch_id - Ship Confirm Batch ID, needed to stamp the delivery
467 -- p_ship_confirm_rule_rec - the ship confirm options
468 -- p_log_level - log level for printing debug messages
469 -- p_actual_departure_date - Actual Departure Date on stop
470 --
471 -- Output Parameters:
472 -- x_organization_id - the organization id of the delivery
473 -- x_return_status - Success, Warning, Error, Unexpected Error
474 -- ----------------------------------------------------------------------
475
476 PROCEDURE Ship_Confirm_A_Delivery(
477 p_delivery_id IN NUMBER,
478 p_sc_batch_id IN NUMBER,
479 p_ship_confirm_rule_rec IN G_GET_SHIP_CONFIRM_RULE%ROWTYPE,
480 p_log_level IN NUMBER,
481 p_actual_departure_date IN DATE,
482 x_return_status OUT NOCOPY VARCHAR2) IS
483
484
485 CURSOR get_delivery( c_delivery_id NUMBER ) IS
486 SELECT delivery_id,
487 status_code,
488 planned_flag,
489 initial_pickup_date,
490 organization_id,
491 ship_method_code,
492 initial_pickup_location_id
493 FROM wsh_new_deliveries
494 WHERE delivery_id = c_delivery_id and
495 status_code = 'OP' AND
496 NVL(auto_sc_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
497
498
499 -- bug 4302048: respect rule's ship method defaulting
500 CURSOR c_first_trip_ship_method (x_delivery_id IN NUMBER,
501 x_initial_loc_id IN NUMBER)IS
502 SELECT wt.ship_method_code
503 FROM wsh_delivery_legs dlg,
504 wsh_trip_stops st,
505 wsh_trips wt
506 WHERE dlg.delivery_id = x_delivery_id
507 AND st.stop_id = dlg.pick_up_stop_id
508 AND st.stop_location_id = x_initial_loc_id
509 AND st.trip_id = wt.trip_id
510 AND wt.ship_method_code IS NOT NULL
511 AND rownum = 1;
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;
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;
523 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
524 l_msg_count NUMBER;
525 l_msg_data VARCHAR2(4000);
526 l_debug_on BOOLEAN;
527 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery';
528 delivery_locked EXCEPTION;
529
530 PRAGMA EXCEPTION_INIT(delivery_locked, -00054);
531
532 BEGIN
533
534 --
535 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
536 --
537 IF l_debug_on IS NULL
538 THEN
539 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
540 END IF;
541 --
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
551 IF l_debug_on THEN
552 WSH_DEBUG_SV.log(l_module_name, 'Locking delivery '|| p_delivery_id);
553 END IF;
554
555 OPEN get_delivery(p_delivery_id);
556 FETCH get_delivery INTO l_delivery_rec;
557 IF get_delivery%FOUND THEN
558 IF l_debug_on THEN
559 WSH_DEBUG_SV.log(l_module_name, 'Check if ship confirm is allowed for delivery ID: ' || to_char(l_tmp_del_tab(1)));
560 END IF;
561
562 UPDATE WSH_NEW_DELIVERIES SET BATCH_ID = p_sc_batch_id
563 WHERE delivery_id = p_delivery_id;
564
565 SAVEPOINT beginning_of_loop;
566
567 fnd_msg_pub.initialize; -- clear messages
568 --
569 IF p_actual_departure_date IS NOT NULL THEN
570 l_actual_dep_date := p_actual_departure_date;
571 ELSE
572 l_actual_dep_date := SYSDATE;
573 END IF;
574 --
575 l_rec_attr_tab(1).delivery_id := l_delivery_rec.delivery_id;
576 l_rec_attr_tab(1).status_code := l_delivery_rec.status_code;
577 l_rec_attr_tab(1).planned_flag := l_delivery_rec.planned_flag;
578 l_rec_attr_tab(1).organization_id := l_delivery_rec.organization_id;
579 l_rec_attr_tab(1).ship_method_code := l_delivery_rec.ship_method_code;
580
581 l_action_prms.action_code := 'CONFIRM';
582 l_action_prms.caller := 'WSH_BHPS';
583 l_action_prms.phase := NULL;
584 l_action_prms.action_flag := p_ship_confirm_rule_rec.action_flag;
585 -- 3667595, do not close stops when ship confirm deliveries
589 l_action_prms.report_set_id := p_ship_confirm_rule_rec.report_set_id;
586 l_action_prms.intransit_flag := 'N';
587 l_action_prms.close_trip_flag := 'N';
588 l_action_prms.stage_del_flag := p_ship_confirm_rule_rec.stage_del_flag;
590 l_action_prms.bill_of_lading_flag := p_ship_confirm_rule_rec.ac_bol_flag;
591 l_action_prms.mc_bill_of_lading_flag := p_ship_confirm_rule_rec.mc_bol_flag;
592 l_action_prms.defer_interface_flag := 'Y';
593
594 -- bug 4302048: respect the rule's ship method default flag.
595 IF p_ship_confirm_rule_rec.ship_method_default_flag = 'R' THEN
596 l_action_prms.ship_method_code := p_ship_confirm_rule_rec.ship_method_code;
597 ELSE
598 IF l_debug_on THEN
599 WSH_DEBUG_SV.logmsg(l_module_name,'deriving trip/delivery SM');
600 END IF;
601 OPEN c_first_trip_ship_method(
602 l_delivery_rec.delivery_id,
603 l_delivery_rec.initial_pickup_location_id);
604 FETCH c_first_trip_ship_method INTO l_ship_method_code;
605 IF c_first_trip_ship_method%NOTFOUND THEN
606 l_ship_method_code := l_delivery_rec.ship_method_code;
607 END IF;
608 CLOSE c_first_trip_ship_method;
609 l_action_prms.ship_method_code := l_ship_method_code;
610 END IF;
611 l_action_prms.actual_dep_date := l_actual_dep_date;
612 l_action_prms.send_945_flag := p_ship_confirm_rule_rec.send_945_flag;
613
614 IF l_debug_on THEN
615 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.action_code: ',l_action_prms.action_code);
616 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.caller:',l_action_prms.caller);
617 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.phase: ',l_action_prms.phase);
618 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.action_flag: ',l_action_prms.action_flag);
619 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.intransit_flag: ',l_action_prms.intransit_flag);
620 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.close_trip_flag: ',l_action_prms.close_trip_flag);
621 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.stage_del_flag: ',l_action_prms.stage_del_flag);
622 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.report_set_id: ',l_action_prms.report_set_id);
623 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.bill_of_lading_flag: ',l_action_prms.bill_of_lading_flag);
624 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.mc_bill_of_lading_flag: ',l_action_prms.mc_bill_of_lading_flag);
625 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.defer_interface_flag: ',l_action_prms.defer_interface_flag);
626 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.ship_method_code: ',l_action_prms.ship_method_code);
627 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.actual_dep_date: ',l_action_prms.actual_dep_date);
628 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.send_945_flag: ',l_action_prms.send_945_flag);
629 END IF;
630
631 WSH_DELIVERIES_GRP.Delivery_Action
632 ( p_api_version_number => 1.0,
633 p_init_msg_list => FND_API.G_FALSE,
634 p_commit => FND_API.G_FALSE,
635 p_action_prms => l_action_prms,
636 p_rec_attr_tab => l_rec_attr_tab,
637 x_delivery_out_rec => l_delivery_out_rec,
638 x_defaults_rec => l_defaults_rec,
639 x_return_status => l_return_status,
640 x_msg_count => l_msg_count,
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;
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
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 */
662 IF l_debug_on THEN
663 WSH_DEBUG_SV.POP(l_module_name);
664 END IF;
665
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
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;
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
689 CLOSE c_first_trip_ship_method;
693 IF l_debug_on THEN
690 END IF;
691 WSH_UTIL_CORE.PrintMsg('ERROR: unhandled exception');
692 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_A_Delivery');
694 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: unhandled exception');
695 WSH_DEBUG_SV.POP(l_module_name);
696 END IF;
697
698 END Ship_Confirm_A_Delivery;
699
700
701 -- -----------------------------------------------------------------------
702 -- Name
703 -- PROCEDURE Close_A_Stop
704 --
705 -- Purpose:
706 -- This PRIVATE procedure closes a stop, it is called by
707 -- Ship_Confirm_Batch in a loop.
708 -- It is necessary to make a saperate procedure, othewise it fails to
709 -- catch the exception when a stop cannot be locked within a loop and
710 -- it will exit all the way out of the stop loop so no stop can be
711 -- closed after the exception
712 --
713 -- Input Parameters:
714 -- p_stop_id - the stop id to be closed
715 -- p_actual_date - the actual departure date for the stop
716 -- p_defer_interface_flag - indicate whether to defer interface
717 --
718 -- Output Parameters:
719 -- x_return_status - Success, Warning, Error, Unexpected Error
720 -- ----------------------------------------------------------------------
721
722 PROCEDURE Close_A_Stop (
723 p_stop_id IN NUMBER,
724 p_actual_date IN DATE,
725 p_defer_interface_flag IN VARCHAR2,
726 x_return_status OUT NOCOPY VARCHAR2) IS
727
728 Cursor lock_stop_trip (c_stop_id NUMBER) IS
729 SELECT a.stop_id self_stop_id, b.trip_id, c.stop_id other_stop_id
730 FROM wsh_trip_stops a, wsh_trips b, wsh_trip_stops c
731 WHERE a.trip_id = b.trip_id AND
732 a.stop_id = c_stop_id AND
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);
742 l_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
743 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
744 l_stop_out_rec WSH_TRIP_STOPS_GRP.stopActionOutRecType;
745 l_def_rec WSH_TRIP_STOPS_GRP.default_parameters_rectype;
746 l_msg_count NUMBER;
747 l_msg_data VARCHAR2(2000);
748 l_debug_on BOOLEAN;
749 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop';
750
751 stop_trip_locked EXCEPTION;
752 PRAGMA EXCEPTION_INIT(stop_trip_locked, -00054);
753
754 BEGIN
755
756 --
757 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
758 --
759 IF l_debug_on IS NULL
760 THEN
761 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
762 END IF;
763 --
764 IF l_debug_on THEN
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
774 SAVEPOINT beginning_of_the_procedure;
775
776 IF l_debug_on THEN
777 WSH_DEBUG_SV.log(l_module_name, 'Stop locked, calling WSH_TRIP_STOPS_GRP.Stop_Action for stop '|| to_char(p_stop_id));
778 END IF;
779
780 l_action_prms.action_code := 'UPDATE-STATUS';
781 l_action_prms.stop_action := 'CLOSE';
782 l_action_prms.actual_date := p_actual_date;
783 l_action_prms.defer_interface_flag := 'Y';
784
785 l_action_prms.caller := 'WSH_BHPS';
786 l_action_prms.phase := NULL;
787
788 l_rec_attr_tab(1).stop_id := p_stop_id;
789
790
791 WSH_TRIP_STOPS_GRP.Stop_Action
792 ( p_api_version_number => 1.0,
793 p_init_msg_list => FND_API.G_FALSE,
794 p_commit => FND_API.G_FALSE,
795 p_action_prms => l_action_prms,
796 p_rec_attr_tab => l_rec_attr_tab,
797 x_stop_out_rec => l_stop_out_rec,
798 x_def_rec => l_def_rec,
799 x_return_status => l_return_status,
800 x_msg_count => l_msg_count,
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;
810 ELSE
811 CLOSE lock_stop_trip;
812
813 END IF;
814
815
816 IF l_debug_on THEN
817 WSH_DEBUG_SV.pop(l_module_name);
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));
827 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
831 END IF;
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);
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
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;
844 END Close_A_Stop;
845
846 -- -----------------------------------------------------------------------
847 -- Name
848 -- PROCEDURE Ship_Confirm_Batch
849 --
850 -- Purpose
851 -- This procedure takes a list of delivery IDs and ship confirm these
852 -- deliveries in batch fashion. It calls confirm_delivery once for one
853 -- delivery at a time. If the ship confirm operation is successful,
854 -- it commits the change, otherwise it rollback the change and proceed
855 -- to next delivery. If a delivery cannot be ship confirmed,
856 -- it does not prevent next delivery to be ship confirmed.
857 -- This procedure is called from Automated Ship Confirm SRS or by
858 -- Pick Release SRS.
859 --
860 -- Input Parameters:
861 -- p_del_tab - list of delivery IDs to be ship confimed
862 -- p_sc_batch_id - Ship Confirm Batch ID
863 -- p_log_level - log level for printing debug messages
864 --
865 -- Output Parameters:
866 -- x_return_status - Success, Warning, Error, Unexpected Error
867 -- ----------------------------------------------------------------------
868
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
878 l_batch_creation_date DATE := NULL;
879
880 CURSOR get_sc_batch IS
881 SELECT ship_confirm_rule_id, creation_date, actual_departure_date
882 FROM wsh_picking_batches
883 WHERE batch_id = p_sc_batch_id FOR UPDATE NOWAIT;
884
885 -- 3667595
886 CURSOR get_pick_up_stops IS
887 SELECT DISTINCT wtp.trip_id, wst.stop_sequence_number, wst.stop_id, wst.stop_location_id
888 FROM wsh_new_deliveries wnd,
889 wsh_delivery_legs wlg,
890 wsh_trip_stops wst,
891 wsh_trips wtp
892 WHERE wnd.delivery_id = wlg.delivery_id AND
893 wlg.pick_up_stop_id = wst.stop_id AND
894 wnd.status_code = 'CO' AND
895 wnd.batch_id = p_sc_batch_id AND
896 wtp.trip_id = wst.trip_id AND
897 wst.status_code = 'OP' AND
898 NOT EXISTS (
899 select '1' from wsh_exceptions we where
900 we.delivery_id = wnd.delivery_id AND
901 we.severity = 'ERROR' AND
902 we.status = 'OPEN' AND
903 we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
904
905
906 ORDER BY wtp.trip_id, wst.stop_sequence_number, wst.stop_id ;
907
908 --Modified the following SQL as part of bug 4280371 ( 30-Jun-2005 ).
909 CURSOR get_all_stops IS
910 select wsto.trip_id, wsto.stop_sequence_number, wsto.stop_id , wsto.stop_location_id
911 from wsh_trip_stops wsto
912 , wsh_new_deliveries wnd
913 , wsh_delivery_legs wlg
914 , wsh_trip_stops wst
915 where wnd.batch_id = p_sc_batch_id
916 and wnd.status_code = 'CO'
917 and wnd.delivery_id = wlg.delivery_id
918 and wlg.pick_up_stop_id = wst.stop_id
919 and wnd.INITIAL_PICKUP_LOCATION_ID = wst.STOP_LOCATION_ID
920 and wst.status_code in ( 'OP' , 'AR' )
921 and NOT EXISTS (
922 select '1' from wsh_exceptions we
923 where we.delivery_id = wnd.delivery_id
924 AND we.severity = 'ERROR'
925 AND we.status = 'OPEN'
926 AND we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
927 and wsto.trip_id = wst.trip_id
928 ORDER BY 1, 2, 3;
929
930
931 -- added cursor to check if interface is necessary
932 -- 3667595
933 CURSOR c_batch_stop(p_batch_id NUMBER)IS
934 SELECT wts.stop_id
935 FROM wsh_trip_stops wts,
936 wsh_delivery_legs wdl,
937 wsh_new_deliveries wnd,
938 wsh_picking_batches wpb
939 WHERE p_batch_id IS NOT NULL
940 AND wnd.batch_id = p_batch_id
941 AND wdl.delivery_id = wnd.delivery_id
942 AND wts.stop_id = wdl.pick_up_stop_id
943 AND wts.stop_location_id = wnd.initial_pickup_location_id
944 AND wpb.batch_id = wnd.batch_id
945 AND wts.status_code = 'CL'
946 AND rownum = 1;
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;
959 l_stop_location_ids WSH_UTIL_CORE.Id_Tab_Type;
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;
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;
964 l_debug_on BOOLEAN;
965 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_Batch';
966
967 l_status_code VARCHAR2(30);
968 l_lock_error VARCHAR2(1) := 'N';
969 l_closing_stop_success NUMBER := 0;
970 l_closing_stop_warning NUMBEr := 0;
971 l_closing_stop_failure NUMBER := 0;
972 l_interface_stop_id NUMBER := 0;
973
974 wsh_missing_sc_rule EXCEPTION;
975 wsh_submit_sc_report_err EXCEPTION;
976 delivery_locked EXCEPTION;
977 wsh_missing_sc_batch EXCEPTION;
978 inv_inter_req_submission EXCEPTION;
979
980 PRAGMA EXCEPTION_INIT(delivery_locked, -00054);
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
990
991 -- reset out parameters
992 --
993 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
994 --
995 IF l_debug_on IS NULL
996 THEN
997 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
998 END IF;
999 --
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;
1009
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,
1019 l_actual_departure_date;
1020
1021 IF l_debug_on THEN
1022 WSH_DEBUG_SV.log(l_module_name, 'Batch Creation Date',
1023 to_char(l_batch_creation_date, 'MM-DD-YYYY HH24:MI:SS'));
1024 WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date',
1025 to_char(l_actual_departure_date, 'MM/DD/YYYY HH24:MI:SS'));
1026 END IF;
1027
1028 IF get_sc_batch%NOTFOUND THEN
1029 CLOSE get_sc_batch;
1030 raise wsh_missing_sc_batch;
1031 END IF;
1032
1033 IF l_ship_confirm_rule_id IS NULL THEN
1034 raise wsh_missing_sc_rule;
1035 END IF;
1036
1037 OPEN G_GET_SHIP_CONFIRM_RULE(l_ship_confirm_rule_id);
1038 FETCH G_GET_SHIP_CONFIRM_RULE INTO l_ship_confirm_rule_rec;
1039
1040 IF G_GET_SHIP_CONFIRM_RULE%NOTFOUND THEN
1041 CLOSE G_GET_SHIP_CONFIRM_RULE;
1042 raise wsh_missing_sc_rule;
1043 END IF;
1044
1045
1046 FOR i in 1 .. p_del_tab.count LOOP
1047 BEGIN
1048
1049 Ship_Confirm_A_Delivery(
1050 p_delivery_id => p_del_tab(i).delivery_id,
1051 p_sc_batch_id => p_sc_batch_id,
1052 p_ship_confirm_rule_rec => l_ship_confirm_rule_rec,
1053 p_log_level => p_log_level,
1054 p_actual_departure_date => l_actual_departure_date,
1055 x_return_status => l_return_status);
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);
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);
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)
1090 --{
1087 AND p_commit = FND_API.G_TRUE ) THEN
1088 --{
1089 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
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
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;
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
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
1136 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID '|| to_char(p_del_tab(i).delivery_id)||' is ship confirmed with warnings');
1137 END IF;
1138
1139 ELSE
1140 x_results_summary.failure := x_results_summary.failure + 1;
1141
1142 log_batch_messages(p_sc_batch_id, p_del_tab(i).delivery_id,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1143
1144 IF l_debug_on THEN
1145 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID '|| to_char(p_del_tab(i).delivery_id)||' cannot be ship confirmed');
1146 END IF;
1147
1148
1149 END IF;
1150
1151 -- Following if condition is added for Bugfix #4001135
1152 -- We will commit the data only when this api is called from Concurrent Request.
1153 IF ( P_COMMIT = FND_API.G_TRUE ) THEN
1154 COMMIT;
1155 END IF;
1156
1157 END LOOP;
1158
1159 x_confirmed_del_tab := l_sc_confirmed_dels;
1160
1161
1162 -- Close the manually created Trip-Stops
1163 -- 3667595
1164 IF l_ship_confirm_rule_rec.ac_close_trip_flag = 'Y' THEN
1165 OPEN get_all_stops;
1166
1167 LOOP
1168
1169 FETCH get_all_stops into l_trip_id, l_stop_sequence_number, l_stop_id, l_stop_location_id ;
1170 EXIT WHEN get_all_stops%NOTFOUND;
1171 l_stops_to_close(l_stops_to_close.count+1) := l_stop_id;
1172 l_stop_location_ids(l_stop_location_ids.count+1) := l_stop_location_id;
1173
1174 END LOOP;
1175 CLOSE get_all_stops;
1176
1177 -- 3667595
1178 ELSIF l_ship_confirm_rule_rec.ac_intransit_flag = 'Y' THEN
1179 OPEN get_pick_up_stops ;
1180 LOOP
1181
1182 FETCH get_pick_up_stops into l_trip_id, l_stop_sequence_number, l_stop_id, l_stop_location_id;
1183 EXIT WHEN get_pick_up_stops%NOTFOUND;
1184 l_stops_to_close(l_stops_to_close.count+1) := l_stop_id;
1185 l_stop_location_ids(l_stop_location_ids.count+1) := l_stop_location_id;
1186
1187 END LOOP;
1188 CLOSE get_pick_up_stops;
1189
1190 END IF;
1191
1192 IF l_stops_to_close.count > 0 THEN
1193 IF l_debug_on THEN
1194 WSH_DEBUG_SV.logmsg(l_module_name, 'Closing manually created stops');
1195 END IF;
1196 FOR i in 1..l_stops_to_close.count LOOP
1197 BEGIN
1198 l_lock_error := 'N';
1199
1200 Close_A_Stop(
1201 p_stop_id => l_stops_to_close(i),
1202 p_actual_date => NVL(l_actual_departure_date,SYSDATE),
1203 p_defer_interface_flag => 'Y',
1204 x_return_status => l_return_status);
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
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)));
1218
1215 WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop');
1216 END IF;
1217 END;
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 --{
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
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;
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
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;
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;
1268 log_batch_messages(p_sc_batch_id, NULL , l_stops_to_close(i) , l_stop_location_ids(i), NULL);
1269 ELSE
1270 IF l_debug_on THEN
1271 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to close stop '|| l_stops_to_close(i));
1272 END IF;
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;
1282
1283 -- Following if condition is added for Bugfix #4001135
1284 -- We will commit the data only when this api is called from Concurrent Request.
1285 IF ( P_COMMIT = FND_API.G_TRUE ) THEN
1286 COMMIT;
1287 END IF;
1288
1289 END LOOP;
1290
1291 IF l_debug_on THEN
1292 WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed '|| l_closing_stop_success ||' stops' );
1293 WSH_DEBUG_SV.logmsg(l_module_name,'Closed '|| l_closing_stop_warning ||' stops with warnings' );
1294 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to close '|| l_closing_stop_failure ||' stops' );
1295 END IF;
1296
1297 ELSE
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.logmsg(l_module_name, 'No manually created stops to close');
1300 END IF;
1301 END IF;
1302
1303 IF l_ship_confirm_rule_rec.ac_defer_interface_flag = 'N' THEN
1304 l_interface_stop_id := 0;
1305 -- added cursor to check if interface is necessary
1306 open c_batch_stop ( p_sc_batch_id);
1307 fetch c_batch_stop into l_interface_stop_id;
1308 close c_batch_stop;
1309 IF l_interface_stop_id <> 0 THEN
1310
1311 l_request_id := FND_REQUEST.submit_Request('WSH', 'WSHINTERFACE', '', '', FALSE,
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;
1321 END IF;
1322
1323
1324
1325 CLOSE G_GET_SHIP_CONFIRM_RULE;
1326 CLOSE get_sc_batch;
1327
1328
1329 -- submit Auto Ship Confirm Deliveries Reprot here
1330 x_results_summary.report_req_id := fnd_request.submit_request(
1331 'WSH',
1332 'WSHRDASC','Auto Ship Confirm Report',NULL,FALSE
1333 ,p_sc_batch_id,'','','','','','','','',''
1334 ,'','','','','','','','SC','',''
1335 ,'','','','','','','','','',''
1336 ,'','','','','','','','','',''
1337 ,'','','','','','','','','',''
1338 ,'','','','','','','','','',''
1339 ,'','','','','','','','','',''
1340 ,'','','','','','','','','',''
1341 ,'','','','','','','','','',''
1342 ,'','','','','','','','','','' );
1343
1344 IF x_results_summary.report_req_id =0 THEN
1345 raise WSH_SUBMIT_SC_REPORT_ERR;
1349 WSH_DEBUG_SV.pop(l_module_name);
1346 END IF;
1347
1348 IF l_debug_on THEN
1350 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);
1360 END IF;
1361
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;
1371
1372 WHEN inv_inter_req_submission THEN
1373 IF get_sc_batch%ISOPEN THEN
1374 CLOSE get_sc_batch;
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;
1384
1385 WHEN WSH_SUBMIT_SC_REPORT_ERR THEN
1386 IF get_sc_batch%ISOPEN THEN
1387 CLOSE get_sc_batch;
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;
1397
1398 WHEN delivery_locked THEN
1399 IF get_sc_batch%ISOPEN THEN
1400 CLOSE get_sc_batch;
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;
1410
1411 WHEN others THEN
1412 IF get_sc_batch%ISOPEN THEN
1413 CLOSE get_sc_batch;
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;
1423
1424 END Ship_Confirm_Batch;
1425
1426 -- -----------------------------------------------------------------------
1427 -- Name
1428 -- PROCEDURE Auto_Pack_A_Delivery
1429 --
1430 -- Purpose:
1431 -- This PRIVATE procedure auto pack a delivery, it is called by
1432 -- uto_Pack_Deliveries_Batch in a loop.
1433 -- It is necessary to make a saperate procedure, othewise it fails to
1434 -- catch the exception when a delivery cannot be locked within a loop and
1435 -- it will exit all the way out of the delivery loop so no deliveries can be
1436 -- ship confirmed after the exception occurs.
1437 --
1438 -- Input Parameters:
1439 -- p_delivery_id - the delivery id to be ship cofirmed
1440 -- p_sc_batch_id - Ship Confirm Batch ID, needed to stamp the delivery
1441 -- p_ship_confirm_rule_rec - the ship confirm options
1442 -- p_log_level - log level for printing debug messages
1443 --
1444 -- Output Parameters:
1445 -- x_organization_id - the organization id of the delivery
1446 -- x_return_status - Success, Warning, Error, Unexpected Error
1447 -- ----------------------------------------------------------------------
1448
1449
1450 PROCEDURE Auto_Pack_A_Delivery(
1451 p_delivery_id IN NUMBER,
1452 p_ap_batch_id IN NUMBER,
1453 p_auto_pack_level IN NUMBER,
1454 p_log_level IN NUMBER,
1455 x_return_status OUT NOCOPY VARCHAR2) IS
1456
1457 CURSOR get_delivery( c_delivery_id NUMBER ) IS
1458 SELECT delivery_id,
1459 status_code,
1460 planned_flag,
1461 initial_pickup_date,
1462 organization_id,
1463 ship_method_code
1464 FROM wsh_new_deliveries
1465 WHERE delivery_id = c_delivery_id and
1466 status_code = 'OP' AND
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;
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;
1481 l_msg_count NUMBER;
1482 l_msg_data VARCHAR2(4000);
1483 l_debug_on BOOLEAN;
1484 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery';
1485 WSH_INVALID_AUTO_PACK_LEVEL EXCEPTION;
1486 delivery_locked EXCEPTION;
1487
1491 BEGIN
1488 PRAGMA EXCEPTION_INIT(delivery_locked, -00054);
1489
1490
1492
1493 --
1494 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1495 --
1496 IF l_debug_on IS NULL
1497 THEN
1498 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1499 END IF;
1500 --
1501 IF l_debug_on THEN
1502 WSH_DEBUG_SV.push(l_module_name);
1503 END IF;
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';
1513 ELSIF p_auto_pack_level = 2 THEN
1514 l_action := 'AUTO-PACK-MASTER';
1515 l_pack_cont_flag := 'Y';
1516 ELSE
1517 RAISE WSH_INVALID_AUTO_PACK_LEVEL;
1518 END IF;
1519
1520 IF l_debug_on THEN
1521 WSH_DEBUG_SV.logmsg(l_module_name, 'Locking delivery '|| p_delivery_id);
1522 END IF;
1523
1524
1525 OPEN get_delivery(p_delivery_id);
1526 FETCH get_delivery INTO l_delivery_rec;
1527 IF get_delivery%FOUND THEN
1528
1529
1530 SAVEPOINT beginning_of_loop;
1531
1532 UPDATE WSH_NEW_DELIVERIES SET AP_BATCH_ID = p_ap_batch_id
1533 WHERE delivery_id = p_delivery_id;
1534
1535 fnd_msg_pub.initialize; -- clear messages
1536
1537 /* auto pack a delivery */
1538 l_rec_attr_tab(1).delivery_id := l_delivery_rec.delivery_id;
1539 l_rec_attr_tab(1).status_code := l_delivery_rec.status_code;
1540 l_rec_attr_tab(1).planned_flag := l_delivery_rec.planned_flag;
1541 l_rec_attr_tab(1).organization_id := l_delivery_rec.organization_id;
1542 l_rec_attr_tab(1).ship_method_code := l_delivery_rec.ship_method_code;
1543
1544 l_action_prms.action_code := l_action;
1545 l_action_prms.caller := 'WSH_BHPS';
1546 l_action_prms.phase := NULL;
1547
1548
1549
1550 WSH_DELIVERIES_GRP.Delivery_Action
1551 ( p_api_version_number => 1.0,
1552 p_init_msg_list => FND_API.G_FALSE,
1553 p_commit => FND_API.G_FALSE,
1554 p_action_prms => l_action_prms,
1555 p_rec_attr_tab => l_rec_attr_tab,
1556 x_delivery_out_rec => l_delivery_out_rec,
1557 x_defaults_rec => l_defaults_rec,
1558 x_return_status => l_return_status,
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
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
1577 WSH_DEBUG_SV.pop(l_module_name);
1578 END IF;
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);
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);
1597 END IF;
1598
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));
1608 WSH_DEBUG_SV.pop(l_module_name);
1609 END IF;
1610
1611 END Auto_Pack_A_Delivery;
1612
1613 -- -----------------------------------------------------------------------
1614 -- Name
1615 -- PROCEDURE Auto_Pack_Deliveries_Batch
1616 --
1617 -- Purpose
1618 -- This procedure takes a list of delivery IDs and auto pack these
1619 -- deliveries in batch fashion. It calls auto_pack_deliveries once for one
1620 -- delivery at a time. If the auto pack operation is successful,
1621 -- it commits the change, otherwise it rollback the change and proceed
1622 -- to next delivery. If a delivery cannot be auto packed,
1623 -- it does not prevent next delivery to be auto packed.
1624 -- This procedure is called from Auto Pack Deliveries SRS or by
1625 -- Pick Release SRS.
1626 --
1627 -- Input Parameters:
1628 -- p_del_tab - list of delivery IDs to be ship confimed
1629 -- p_sc_batch_id - Ship Confirm Batch ID
1633 -- x_return_status - Success, Warning, Error, Unexpected Error
1630 -- p_log_level - log level for printing debug messages
1631 --
1632 -- Output Parameters:
1634 -- ----------------------------------------------------------------------
1635
1636 PROCEDURE Auto_Pack_Deliveries_Batch(
1637 p_del_tab IN WSH_BATCH_PROCESS.Del_Info_Tab,
1638 p_ap_batch_id IN NUMBER,
1639 p_auto_pack_level IN NUMBER,
1640 p_log_level IN NUMBER,
1641 x_packed_del_tab OUT NOCOPY WSH_BATCH_PROCESS.Del_Info_Tab,
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;
1651 delivery_locked EXCEPTION;
1652 WSH_INVALID_AUTO_PACK_LEVEL EXCEPTION;
1653 PRAGMA EXCEPTION_INIT(delivery_locked, -54);
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
1663
1664 --
1665 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1666 --
1667 IF l_debug_on IS NULL
1668 THEN
1669 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1670 END IF;
1671 --
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;
1690
1691 FOR i in 1 .. p_del_tab.count LOOP
1692
1693 IF l_debug_on THEN
1694 WSH_DEBUG_SV.logmsg(l_module_name,'Auto pack delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1695 END IF;
1696
1697 BEGIN
1698 Auto_Pack_A_Delivery(
1699 p_delivery_id => p_del_tab(i).delivery_id,
1700 p_ap_batch_id => p_ap_batch_id,
1701 p_auto_pack_level => p_auto_pack_level,
1702 p_log_level => p_log_level,
1703 x_return_status => l_return_status);
1704
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);
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);
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');
1731 END IF;
1732
1733
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 --{
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
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;
1757
1761 l_return_status := l_return_status1;
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
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;
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;
1778
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;
1788
1789 log_batch_messages(p_ap_batch_id, p_del_tab(i).delivery_id, NULL, p_del_tab(i).initial_pickup_location_id, 'W');
1790 IF l_debug_on THEN
1791 WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack completed with warnings for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1792 END IF;
1793
1794 ELSE -- error or unexpected error
1795
1796 x_results_summary.failure := x_results_summary.failure + 1;
1797
1798
1799 IF l_debug_on THEN
1800 WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack failed for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1801 END IF;
1802
1803 log_batch_messages(p_ap_batch_id, p_del_tab(i).delivery_id,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1804
1805 END IF;
1806
1807 -- Following if condition is added for Bugfix #4001135
1808 -- We will commit the data only when this api is called from Concurrent Request.
1809 IF ( P_COMMIT = FND_API.G_TRUE ) THEN
1810 COMMIT;
1811 END IF;
1812
1813 END LOOP;
1814
1815 IF l_debug_on THEN
1816 WSH_DEBUG_SV.logmsg(l_module_name,'All the deliveries have been packed');
1817 END IF;
1818
1819 -- submit Auto Pack Deliveries Reprot here
1820 x_results_summary.report_req_id := fnd_request.submit_request(
1821 'WSH',
1822 'WSHRDAPK','Auto Pack Report',NULL,FALSE
1823 ,p_ap_batch_id,'','','','','','','','',''
1824 ,'','','','','','','AP','','',''
1825 ,'','','','','','','','','',''
1826 ,'','','','','','','','','',''
1827 ,'','','','','','','','','',''
1828 ,'','','','','','','','','',''
1829 ,'','','','','','','','','',''
1830 ,'','','','','','','','','',''
1831 ,'','','','','','','','','',''
1832 ,'','','','','','','','','','' );
1833 IF x_results_summary.report_req_id =0 THEN
1834 raise WSH_SUBMIT_AP_REPORT_ERR;
1835 END IF;
1836
1837 --
1838 IF l_debug_on THEN
1839 WSH_DEBUG_SV.pop(l_module_name);
1840 END IF;
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);
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);
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
1867 END Auto_Pack_Deliveries_Batch;
1868
1869 -- -----------------------------------------------------------------------
1870 -- Name
1871 -- PROCEDURE Confirm_Delivery_SRS
1872 --
1873 -- Purpose
1874 -- This is ship confirm concurrent program launched using standard report submisstion.
1875 -- It takes users defined criteria and construct a dynamic select statement to
1876 -- get a list of delivery IDs to be ship confirmed then call Ship_Confirm_Batch.
1877 -- The actual ship confirm operation is done in Ship_Cofirm_Batch procedure
1878 --
1879 -- Input Parameters:
1880 -- p_ship_confirm_rule_id - Auto Ship Confirm Rule ID
1881 -- p_sc_batch_prefix - Ship Confirm Batch ID
1882 -- p_organization_id - Organization ID
1883 -- p_pr_batch_id - Pick Release Batch ID
1887 -- p_bol_number_lo - BOL Number (Low)
1884 -- p_ap_batch_id - Auto Pack Batch ID
1885 -- p_delivery_name_lo - Delivery Name (Low)
1886 -- p_delivery_name_hi - Delivery Name (High)
1888 -- p_bol_number_hi - BOL Number (High)
1889 -- p_planned_flag - Planned Flag
1890 -- p_ship_from_loc_id - Ship from Location ID
1891 -- p_ship_to_loc_id - Ship to Location ID
1892 -- p_intmed_ship_to_loc_id - Intermediate Ship to Location ID
1893 -- p_pooled_ship_to_loc_id - Pooled Ship to Location ID
1894 -- p_customer_id - Customer ID
1895 -- p_ship_method_code - Ship Method Code
1896 -- p_fob_code - FOB Code
1897 -- p_freight_terms_code - Freight Terms Code
1898 -- p_pickup_date_lo - Pick up Date (Low)
1899 -- p_pickup_date_hi - Pick up Date (High)
1900 -- p_dropoff_date_lo - Drop off Date (Low)
1901 -- p_dropoff_date_hi - Drop off Date (High)
1902 -- p_log_level - Log Level
1903 -- p_actual_departure_date - Actual Departure Date for stop
1904 --
1905 -- Output Parameters:
1906 -- errbug - standard output parameter for a concurrent program
1907 -- retcode - standard output parameter for a concurrent program
1908 -- ----------------------------------------------------------------------
1909
1910
1911 procedure Confirm_Delivery_SRS(
1912 errbuf OUT NOCOPY VARCHAR2,
1913 retcode OUT NOCOPY VARCHAR2,
1914 p_ship_confirm_rule_id IN NUMBER,
1915 p_actual_departure_date IN VARCHAR2,
1916 p_sc_batch_prefix IN VARCHAR2,
1917 p_organization_id IN NUMBER,
1918 p_pr_batch_id IN NUMBER,
1919 p_ap_batch_id IN NUMBER,
1920 p_delivery_name_lo IN VARCHAR2,
1921 p_delivery_name_hi IN VARCHAR2,
1922 p_bol_number_lo IN VARCHAR2,
1923 p_bol_number_hi IN VARCHAR2,
1924 p_planned_flag IN VARCHAR2,
1925 p_ship_from_loc_id IN NUMBER,
1926 p_ship_to_loc_id IN NUMBER,
1927 p_intmed_ship_to_loc_id IN NUMBER,
1928 p_pooled_ship_to_loc_id IN NUMBER,
1929 p_customer_id IN NUMBER,
1930 p_ship_method_code IN VARCHAR2,
1931 p_fob_code IN VARCHAR2,
1932 p_freight_terms_code IN VARCHAR2,
1933 p_pickup_date_lo IN VARCHAR2,
1934 p_pickup_date_hi IN VARCHAR2,
1935 p_dropoff_date_lo IN VARCHAR2,
1936 p_dropoff_date_hi IN VARCHAR2,
1937 p_log_level IN NUMBER) IS
1938
1939 l_completion_status VARCHAR2(30);
1940 l_error_code NUMBER;
1941 l_error_text VARCHAR2(2000);
1942
1943 l_user_id NUMBER := 0;
1944 l_login_id NUMBER := 0;
1945 l_return_status VARCHAR2(30) := NULL;
1946 l_rowid VARCHAR2(30);
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';
1956 WSH_MISSING_SC_RULE EXCEPTION;
1957 WSH_MISSING_SC_BATCH_PREFIX EXCEPTION;
1958 WSH_SC_BATCH_ERR EXCEPTION;
1959 WSH_SELECT_ERR EXCEPTION;
1960 WSH_NO_FUTURE_SHIPDATE EXCEPTION;
1961 l_batch_name VARCHAR2(30);
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 --
1971 l_summary VARCHAR2(32000);
1972 l_detail VARCHAR2(32000);
1973 l_count number;
1974 --
1975 BEGIN
1976
1977 l_delivery_ids.delete;
1978 IF p_log_level IS NULL THEN
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 --
1988 IF l_debug_on IS NULL
1989 THEN
1990 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1991 END IF;
1992 --
1993 IF l_debug_on THEN
1994 WSH_DEBUG_SV.push(l_module_name);
1995 END IF;
1996
1997 l_completion_status := 'NORMAL';
1998
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;
2008
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
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
2026 l_select_criteria.ap_batch_id := p_ap_batch_id;
2023 l_select_criteria.process_mode := G_SHIP_CONFIRM;
2024 l_select_criteria.organization_id := p_organization_id;
2025 l_select_criteria.pr_batch_id := p_pr_batch_id;
2027 l_select_criteria.delivery_name_lo := p_delivery_name_lo;
2028 l_select_criteria.delivery_name_hi := p_delivery_name_hi;
2029 l_select_criteria.bol_number_lo := p_bol_number_lo;
2030 l_select_criteria.bol_number_hi := p_bol_number_hi;
2031 l_select_criteria.planned_flag := p_planned_flag;
2032 l_select_criteria.ship_from_loc_id := p_ship_from_loc_id;
2033 l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
2034 l_select_criteria.intmed_ship_to_loc_id := p_intmed_ship_to_loc_id;
2035 l_select_criteria.pooled_ship_to_loc_id := p_pooled_ship_to_loc_id;
2036 l_select_criteria.customer_id := p_customer_id;
2037 l_select_criteria.ship_method_code := p_ship_method_code;
2038 l_select_criteria.fob_code := p_fob_code;
2039 l_select_criteria.freight_terms_code := p_freight_terms_code;
2040 l_select_criteria.pickup_date_lo := p_pickup_date_lo;
2041 l_select_criteria.pickup_date_hi := p_pickup_date_hi;
2042 l_select_criteria.dropoff_date_lo := p_dropoff_date_lo;
2043 l_select_criteria.dropoff_date_hi := p_dropoff_date_hi;
2044 l_select_criteria.log_level := l_log_level;
2045
2046 Select_Deliveries(
2047 p_input_info => l_select_criteria,
2048 p_batch_rec => l_batch_rec,
2049 x_selected_del_tab => l_selected_del_tab,
2050 x_return_status => l_return_status);
2051
2052 -- should we also pack the input parameters into a record
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
2062
2063 -- deliveryMerge
2064 FOR i in l_selected_del_tab.FIRST .. l_selected_del_tab.LAST LOOP
2065 l_delivery_ids(l_delivery_ids.count+1) := l_selected_del_tab(i).delivery_id;
2066 END LOOP;
2067
2068 -- call adjust_planned_flag to plan the deliveries
2069 -- because during ship confirm, no other delivery
2070 -- detail lines should be appended to the deliveries
2071 IF l_debug_on THEN
2072 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Adjust_Planned_Flag');
2073 END IF;
2074
2075 WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag(
2076 p_delivery_ids => l_delivery_ids,
2077 p_caller => 'WSH_DLMG',
2078 p_force_appending_limit => 'Y',
2079 p_call_lcss => 'N',
2080 x_return_status => l_return_status);
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;
2090 END IF;
2091 commit;
2092
2093 -- insert new record in table WSH_PICKING_BATCHES, with NON_PICKING_FLAG = 'Y',
2094 -- SHIP_CONFIRM_RULE_ID = <p_ship_confirm_rule_id>
2095
2096 l_batch_rec.non_picking_flag := 'Y';
2097 l_batch_rec.ship_confirm_rule_id := p_ship_confirm_rule_id;
2098 l_batch_rec.actual_departure_date :=
2099 FND_DATE.canonical_to_date(p_actual_departure_date);
2100 --
2101 IF l_debug_on THEN
2102 WSH_DEBUG_SV.log(l_module_name, 'Non Picking Flag',
2103 l_batch_rec.non_picking_flag);
2104 WSH_DEBUG_SV.log(l_module_name, 'Ship Confirm Rule ID',
2105 l_batch_rec.ship_confirm_rule_id);
2106 WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date',
2107 l_batch_rec.actual_departure_date);
2108 --
2109 IF l_batch_rec.actual_departure_date IS NULL THEN
2110 wsh_debug_sv.logmsg(l_module_name,
2111 'NULL input parameter p_actual_departure_date');
2112 ELSE
2113 wsh_debug_sv.logmsg(l_module_name,
2114 'NOT NULL input parameter p_actual_departure_date');
2115 END IF;
2116 --
2117 END IF;
2118
2119 -- bug 5117876, direct insert into wsh_picking_batches table is replaced
2120 IF l_debug_on THEN
2121 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2122 END IF;
2123
2124 WSH_PICKING_BATCHES_PKG.Insert_Row(
2125 X_Rowid => l_rowid,
2126 X_Batch_Id => l_batch_rec.batch_id,
2127 P_Creation_Date => SYSDATE,
2128 P_Created_By => l_user_id,
2129 P_Last_Update_Date => SYSDATE,
2130 P_Last_Updated_By => l_user_id,
2131 P_Last_Update_Login => l_login_id,
2132 P_batch_name_prefix => p_sc_batch_prefix,
2136 P_Existing_Rsvs_Only_Flag => NULL,
2133 X_Name => l_batch_rec.name,
2134 P_Backorders_Only_Flag => NULL,
2135 P_Document_Set_Id => NULL,
2137 P_Shipment_Priority_Code => NULL,
2138 P_Ship_Method_Code => l_batch_rec.ship_method_code,
2139 P_Customer_Id => l_batch_rec.customer_id,
2140 P_Order_Header_Id => NULL,
2141 P_Ship_Set_Number => NULL,
2142 P_Inventory_Item_Id => NULL,
2143 P_Order_Type_Id => NULL,
2144 P_From_Requested_Date => NULL,
2145 P_To_Requested_Date => NULL,
2146 P_From_Scheduled_Ship_Date => NULL,
2147 P_To_Scheduled_Ship_Date => NULL,
2148 P_Ship_To_Location_Id => l_batch_rec.ship_to_location_id,
2149 P_Ship_From_Location_Id => l_batch_rec.ship_from_location_id,
2150 P_Trip_Id => NULL,
2151 P_Delivery_Id => NULL,
2152 P_Include_Planned_Lines => NULL,
2153 P_Pick_Grouping_Rule_Id => NULL,
2154 P_pick_sequence_rule_id => NULL,
2155 P_Autocreate_Delivery_Flag => NULL,
2156 P_Attribute_Category => NULL,
2157 P_Attribute1 => NULL,
2158 P_Attribute2 => NULL,
2159 P_Attribute3 => NULL,
2160 P_Attribute4 => NULL,
2161 P_Attribute5 => NULL,
2162 P_Attribute6 => NULL,
2163 P_Attribute7 => NULL,
2164 P_Attribute8 => NULL,
2165 P_Attribute9 => NULL,
2166 P_Attribute10 => NULL,
2167 P_Attribute11 => NULL,
2168 P_Attribute12 => NULL,
2169 P_Attribute13 => NULL,
2170 P_Attribute14 => NULL,
2171 P_Attribute15 => NULL,
2172 P_Autodetail_Pr_Flag => NULL,
2173 P_Carrier_Id => NULL,
2174 P_Trip_Stop_Id => NULL,
2175 P_Default_stage_subinventory => NULL,
2176 P_Default_stage_locator_id => NULL,
2177 P_Pick_from_subinventory => NULL,
2178 P_Pick_from_locator_id => NULL,
2179 P_Auto_pick_confirm_flag => NULL,
2180 P_Delivery_Detail_ID => NULL,
2181 P_Project_ID => NULL,
2182 P_Task_ID => NULL,
2183 P_Organization_Id => l_batch_rec.organization_id,
2184 P_Ship_Confirm_Rule_Id => l_batch_rec.ship_confirm_rule_id,
2185 P_Autopack_Flag => NULL,
2186 P_Autopack_Level => NULL,
2187 P_Task_Planning_Flag => NULL,
2188 P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
2189 p_regionID => NULL,
2190 p_zoneId => NULL,
2191 p_categoryID => NULL,
2192 p_categorySetID => NULL,
2193 p_acDelivCriteria => NULL,
2194 p_RelSubinventory => NULL,
2195 p_actual_departure_date => l_batch_rec.actual_departure_date,
2196 p_allocation_method => NULL,
2197 p_crossdock_criteria_id => NULL,
2198 p_append_flag => NULL,
2199 p_task_priority => NULL,
2200 p_Delivery_Name_Lo => l_batch_rec.delivery_name_lo,
2201 p_Delivery_Name_Hi => l_batch_rec.delivery_name_hi,
2202 p_Bol_Number_Lo => l_batch_rec.bol_number_lo,
2203 p_Bol_Number_Hi => l_batch_rec.bol_number_hi,
2204 p_Intmed_Ship_To_Loc_Id => l_batch_rec.intmed_ship_to_loc_id,
2205 p_Pooled_Ship_To_Loc_Id => l_batch_rec.pooled_ship_to_loc_id,
2206 p_Fob_Code => l_batch_rec.fob_code,
2207 p_Freight_Terms_Code => l_batch_rec.freight_terms_code,
2208 p_Pickup_Date_Lo => l_batch_rec.pickup_date_lo,
2209 p_Pickup_Date_Hi => l_batch_rec.pickup_date_hi,
2210 p_Dropoff_Date_Lo => l_batch_rec.dropoff_date_lo,
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
2220 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_selected_del_tab(k).delivery_id);
2221 END LOOP;
2222 END IF;
2223
2224 Ship_Confirm_Batch(
2225 p_del_tab => l_selected_del_tab,
2226 p_sc_batch_id => l_batch_rec.batch_id,
2227 p_log_level => l_log_level,
2228 x_confirmed_del_tab => l_confirmed_del_tab,
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');
2239
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');
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
2247 RAISE WSH_SC_BATCH_ERR;
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;
2257
2258 errbuf := 'Automated Ship Confirm is completed successfully';
2259 retcode := '0';
2260 IF l_debug_on THEN
2261 WSH_DEBUG_SV.pop(l_module_name);
2262 END IF;
2263
2264
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);
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
2282 WSH_DEBUG_SV.pop(l_module_name);
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
2292 WSH_DEBUG_SV.pop(l_module_name);
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
2302 WSH_DEBUG_SV.pop(l_module_name);
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
2312 WSH_DEBUG_SV.pop(l_module_name);
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
2322 WSH_DEBUG_SV.pop(l_module_name);
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
2332 WSH_DEBUG_SV.pop(l_module_name);
2333 END IF;
2334
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';
2344 IF l_debug_on THEN
2345 WSH_DEBUG_SV.pop(l_module_name);
2346 END IF;
2347
2348 END CONFIRM_DELIVERY_SRS;
2349
2350
2351 -- -----------------------------------------------------------------------
2352 -- Name
2353 -- PROCEDURE Auto_Pack_Deliveries_SRS
2354 --
2355 -- Purpose
2356 -- This is auto pack deliveries concurrent program launched using standard
2357 -- report submisstion.
2358 -- It takes users defined criteria and construct a dynamic select statement to
2359 -- get a list of delivery IDs to be auto packed then call Auto_Pack_Deliveries_Batch.
2363 -- p_auto_pack_level - 1: auto pack , 2: auto pack master
2360 -- The actual auto pack operation is done in Auto_Pack_Deliveries_Batch procedure
2361 --
2362 -- Input Parameters:
2364 -- p_organization_id - Organization ID
2365 -- p_pr_batch_id - Pick Release Batch ID
2366 -- p_delivery_name_lo - Delivery Name (Low)
2367 -- p_delivery_name_hi - Delivery Name (High)
2368 -- p_bol_number_lo - BOL Number (Low)
2369 -- p_bol_number_hi - BOL Number (High)
2370 -- p_planned_flag - Planned Flag
2371 -- p_ship_from_loc_id - Ship from Location ID
2372 -- p_ship_to_loc_id - Ship to Location ID
2373 -- p_intmed_ship_to_loc_id - Intermediate Ship to Location ID
2374 -- p_pooled_ship_to_loc_id - Pooled Ship to Location ID
2375 -- p_customer_id - Customer ID
2376 -- p_ship_method_code - Ship Method Code
2377 -- p_fob_code - FOB Code
2378 -- p_freight_terms_code - Freight Terms Code
2379 -- p_pickup_date_lo - Pick up Date (Low)
2380 -- p_pickup_date_hi - Pick up Date (High)
2381 -- p_dropoff_date_lo - Drop off Date (Low)
2382 -- p_dropoff_date_hi - Drop off Date (High)
2383 -- p_log_level - Log Level
2384 --
2385 -- Output Parameters:
2386 -- errbug - standard output parameter for a concurrent program
2387 -- retcode - standard output parameter for a concurrent program
2388 -- ----------------------------------------------------------------------
2389
2390
2391 procedure Auto_Pack_Deliveries_SRS(
2392 errbuf OUT NOCOPY VARCHAR2,
2393 retcode OUT NOCOPY VARCHAR2,
2394 p_auto_pack_level IN NUMBER,
2395 p_ap_batch_prefix IN VARCHAR2,
2396 p_organization_id IN NUMBER,
2397 p_pr_batch_id IN NUMBER,
2398 p_delivery_name_lo IN VARCHAR2,
2399 p_delivery_name_hi IN VARCHAR2,
2400 p_bol_number_lo IN VARCHAR2,
2401 p_bol_number_hi IN VARCHAR2,
2402 p_planned_flag IN VARCHAR2,
2403 p_ship_from_loc_id IN NUMBER,
2404 p_ship_to_loc_id IN NUMBER,
2405 p_intmed_ship_to_loc_id IN NUMBER,
2406 p_pooled_ship_to_loc_id IN NUMBER,
2407 p_customer_id IN NUMBER,
2408 p_ship_method_code IN VARCHAR2,
2409 p_fob_code IN VARCHAR2,
2410 p_freight_terms_code IN VARCHAR2,
2411 p_pickup_date_lo IN VARCHAR2,
2412 p_pickup_date_hi IN VARCHAR2,
2413 p_dropoff_date_lo IN VARCHAR2,
2414 p_dropoff_date_hi IN VARCHAR2,
2415 p_log_level IN NUMBER ) IS
2416
2417 l_completion_status VARCHAR2(30);
2418 l_error_code NUMBER;
2419 l_error_text VARCHAR2(2000);
2420 l_user_id NUMBER := 0;
2421 l_login_id NUMBER := 0;
2422 l_auto_pack_level NUMBER := 0;
2423 l_log_level NUMBER := 0;
2424 l_return_status VARCHAR2(30) := NULL;
2425 l_rowid VARCHAR2(30);
2426 l_temp BOOLEAN;
2427 l_batch_rec WSH_PICKING_BATCHES%ROWTYPE;
2428 l_debug_on BOOLEAN;
2429 l_packed_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
2430 l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
2431 l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
2432 l_results_summary WSH_BATCH_PROCESS.Results_Summary_Rec;
2433
2434 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_SRS';
2435 WSH_PK_BATCH_ERR EXCEPTION;
2436 WSH_INVALID_AUTO_PACK_LEVEL EXCEPTION;
2437 WSH_SELECT_ERR EXCEPTION;
2438 --
2439 l_batch_name VARCHAR2(30);
2440 --
2441 BEGIN
2442 --
2443 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2444 --
2445 IF l_debug_on IS NULL
2446 THEN
2447 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2448 END IF;
2449 --
2450 IF l_debug_on THEN
2451 WSH_DEBUG_SV.push(l_module_name);
2452 END IF;
2453
2454 l_completion_status := 'NORMAL';
2455
2456 l_packed_del_tab.delete;
2457 -- Fetch user and login information
2458 l_user_id := FND_GLOBAL.USER_ID;
2459 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2460
2461 IF p_auto_pack_level IS NULL THEN
2462 l_auto_pack_level := 1;
2463 ELSE
2464 l_auto_pack_level := p_auto_pack_level;
2465 END IF;
2466
2467 IF p_log_level IS NULL THEN
2468 l_log_level := 0;
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));
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;
2483 l_select_criteria.delivery_name_lo := p_delivery_name_lo;
2484 l_select_criteria.delivery_name_hi := p_delivery_name_hi;
2485 l_select_criteria.bol_number_lo := p_bol_number_lo;
2486 l_select_criteria.bol_number_hi := p_bol_number_hi;
2487 l_select_criteria.planned_flag := p_planned_flag;
2488 l_select_criteria.ship_from_loc_id := p_ship_from_loc_id;
2489 l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
2490 l_select_criteria.intmed_ship_to_loc_id := p_intmed_ship_to_loc_id;
2494 l_select_criteria.fob_code := p_fob_code;
2491 l_select_criteria.pooled_ship_to_loc_id := p_pooled_ship_to_loc_id;
2492 l_select_criteria.customer_id := p_customer_id;
2493 l_select_criteria.ship_method_code := p_ship_method_code;
2495 l_select_criteria.freight_terms_code := p_freight_terms_code;
2496 l_select_criteria.pickup_date_lo := p_pickup_date_lo;
2497 l_select_criteria.pickup_date_hi := p_pickup_date_hi;
2498 l_select_criteria.dropoff_date_lo := p_dropoff_date_lo;
2499 l_select_criteria.dropoff_date_hi := p_dropoff_date_hi;
2500 l_select_criteria.log_level := l_log_level;
2501
2502 Select_Deliveries(
2503 p_input_info => l_select_criteria,
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
2513
2514 -- insert new record in table WSH_PICKING_BATCHES, with NON_PICKING_FLAG = 'Y',
2515 -- SHIP_CONFIRM_RULE_ID = <p_ship_confirm_rule_id>
2516
2517 -- required fields for auto pack batch
2518 l_batch_rec.non_picking_flag := 'Y';
2519 l_batch_rec.autopack_flag := 'Y';
2520 l_batch_rec.autopack_level := l_auto_pack_level;
2521 --
2522 IF l_debug_on THEN
2523 WSH_DEBUG_SV.log(l_module_name, 'Non Picking Flag', l_batch_rec.non_picking_flag);
2524 WSH_DEBUG_SV.log(l_module_name, 'Batch Name', l_batch_rec.name);
2525 WSH_DEBUG_SV.log(l_module_name, 'Auto Pack Flag', l_batch_rec.autopack_flag);
2526 WSH_DEBUG_SV.log(l_module_name, 'Auto Pack Level', l_batch_rec.autopack_level);
2527 END IF;
2528
2529 -- bug 5117876, direct insert into wsh_picking_batches table is replaced
2530 IF l_debug_on THEN
2531 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2532 END IF;
2533
2534 WSH_PICKING_BATCHES_PKG.Insert_Row(
2535 X_Rowid => l_rowid,
2536 X_Batch_Id => l_batch_rec.batch_id,
2537 P_Creation_Date => SYSDATE,
2538 P_Created_By => l_user_id,
2539 P_Last_Update_Date => SYSDATE,
2540 P_Last_Updated_By => l_user_id,
2541 P_Last_Update_Login => l_login_id,
2542 P_batch_name_prefix => p_ap_batch_prefix,
2543 X_Name => l_batch_rec.name,
2544 P_Backorders_Only_Flag => NULL,
2545 P_Document_Set_Id => NULL,
2546 P_Existing_Rsvs_Only_Flag => NULL,
2547 P_Shipment_Priority_Code => NULL,
2548 P_Ship_Method_Code => l_batch_rec.ship_method_code,
2549 P_Customer_Id => l_batch_rec.customer_id,
2550 P_Order_Header_Id => NULL,
2551 P_Ship_Set_Number => NULL,
2552 P_Inventory_Item_Id => NULL,
2553 P_Order_Type_Id => NULL,
2554 P_From_Requested_Date => NULL,
2555 P_To_Requested_Date => NULL,
2556 P_From_Scheduled_Ship_Date => NULL,
2557 P_To_Scheduled_Ship_Date => NULL,
2558 P_Ship_To_Location_Id => l_batch_rec.ship_to_location_id,
2559 P_Ship_From_Location_Id => l_batch_rec.ship_from_location_id,
2560 P_Trip_Id => NULL,
2561 P_Delivery_Id => NULL,
2562 P_Include_Planned_Lines => NULL,
2563 P_Pick_Grouping_Rule_Id => NULL,
2564 P_pick_sequence_rule_id => NULL,
2565 P_Autocreate_Delivery_Flag => NULL,
2566 P_Attribute_Category => NULL,
2567 P_Attribute1 => NULL,
2568 P_Attribute2 => NULL,
2569 P_Attribute3 => NULL,
2570 P_Attribute4 => NULL,
2571 P_Attribute5 => NULL,
2572 P_Attribute6 => NULL,
2573 P_Attribute7 => NULL,
2574 P_Attribute8 => NULL,
2575 P_Attribute9 => NULL,
2576 P_Attribute10 => NULL,
2577 P_Attribute11 => NULL,
2578 P_Attribute12 => NULL,
2579 P_Attribute13 => NULL,
2580 P_Attribute14 => NULL,
2581 P_Attribute15 => NULL,
2582 P_Autodetail_Pr_Flag => NULL,
2583 P_Carrier_Id => NULL,
2584 P_Trip_Stop_Id => NULL,
2585 P_Default_stage_subinventory => NULL,
2586 P_Default_stage_locator_id => NULL,
2587 P_Pick_from_subinventory => NULL,
2588 P_Pick_from_locator_id => NULL,
2589 P_Auto_pick_confirm_flag => NULL,
2590 P_Delivery_Detail_ID => NULL,
2591 P_Project_ID => NULL,
2592 P_Task_ID => NULL,
2593 P_Organization_Id => l_batch_rec.organization_id,
2594 P_Ship_Confirm_Rule_Id => NULL,
2595 P_Autopack_Flag => l_batch_rec.autopack_flag,
2596 P_Autopack_Level => l_batch_rec.autopack_level,
2597 P_Task_Planning_Flag => NULL,
2601 p_categoryID => NULL,
2598 P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
2599 p_regionID => NULL,
2600 p_zoneId => NULL,
2602 p_categorySetID => NULL,
2603 p_acDelivCriteria => NULL,
2604 p_RelSubinventory => NULL,
2605 p_actual_departure_date => NULL,
2606 p_allocation_method => NULL,
2607 p_crossdock_criteria_id => NULL,
2608 p_append_flag => NULL,
2609 p_task_priority => NULL,
2610 p_Delivery_Name_Lo => l_batch_rec.delivery_name_lo,
2611 p_Delivery_Name_Hi => l_batch_rec.delivery_name_hi,
2612 p_Bol_Number_Lo => l_batch_rec.bol_number_lo,
2613 p_Bol_Number_Hi => l_batch_rec.bol_number_hi,
2614 p_Intmed_Ship_To_Loc_Id => l_batch_rec.intmed_ship_to_loc_id,
2615 p_Pooled_Ship_To_Loc_Id => l_batch_rec.pooled_ship_to_loc_id,
2616 p_Fob_Code => l_batch_rec.fob_code,
2617 p_Freight_Terms_Code => l_batch_rec.freight_terms_code,
2618 p_Pickup_Date_Lo => l_batch_rec.pickup_date_lo,
2619 p_Pickup_Date_Hi => l_batch_rec.pickup_date_hi,
2620 p_Dropoff_Date_Lo => l_batch_rec.dropoff_date_lo,
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
2630 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_selected_del_tab(k).delivery_id);
2631 END LOOP;
2632 END IF;
2633
2634 Auto_Pack_Deliveries_Batch(
2635 p_del_tab => l_selected_del_tab,
2636 p_ap_batch_id => l_batch_rec.batch_id,
2637 p_auto_pack_level => p_auto_pack_level,
2638 p_log_level => l_log_level,
2639 x_packed_del_tab => l_packed_del_tab,
2640 x_results_summary => l_results_summary,
2641 x_return_status => l_return_status,
2642 p_commit => FND_API.G_TRUE); -- BugFix #4001135
2643
2644 IF l_debug_on THEN
2645 WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries successfully packed are: ');
2646 FOR k in 1 .. l_packed_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');
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
2664 RAISE WSH_PK_BATCH_ERR;
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;
2674 errbuf := 'Auto Pack Deliveries is completed successfully';
2675 retcode := '0';
2676
2677 IF l_debug_on THEN
2678 WSH_DEBUG_SV.pop(l_module_name);
2679 END IF;
2680
2681
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
2691 WSH_DEBUG_SV.pop(l_module_name);
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
2701 WSH_DEBUG_SV.pop(l_module_name);
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';
2713
2710 IF l_debug_on THEN
2711 WSH_DEBUG_SV.pop(l_module_name);
2712 END IF;
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';
2723 IF l_debug_on THEN
2724 WSH_DEBUG_SV.pop(l_module_name);
2725 END IF;
2726 END Auto_Pack_Deliveries_SRS;
2727
2728
2729
2730 PROCEDURE log_batch_messages(p_batch_id IN NUMBER,
2731 p_delivery_id IN NUMBER,
2732 p_stop_id IN NUMBER,
2733 p_exception_location_id IN NUMBER,
2734 p_error_status IN VARCHAR2) IS
2735
2736 c NUMBER;
2737 i NUMBER;
2738 l_buffer VARCHAR2(4000);
2739 l_index_out NUMBER;
2740 l_return_status VARCHAR2(1);
2741 l_msg_count NUMBER;
2742 l_msg_data VARCHAR2(2000);
2743 l_exception_id NUMBER;
2744 l_error_message wsh_exceptions.error_message%type;
2745
2746 --
2747 l_debug_on BOOLEAN;
2748 --
2749 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_BATCH_MESSAGES';
2750 --
2751
2752
2753 BEGIN
2754 --
2755 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2756 --
2757 IF l_debug_on IS NULL
2758 THEN
2759 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2760 END IF;
2761 --
2762 IF l_debug_on THEN
2763 WSH_DEBUG_SV.push(l_module_name);
2764 --
2765 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
2766 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2767 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2768 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_LOCATION_ID',P_EXCEPTION_LOCATION_ID);
2769 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_STATUS',P_ERROR_STATUS);
2770 END IF;
2771
2772 IF p_batch_id IS NULL THEN
2773 RETURN;
2774 END IF;
2775 -- We populate the error_message column in wsh_exceptions if the exception
2776 -- was logged for autopack. If the exception was logged for ship confirm we
2777 -- leave it as NULL.
2778 /* IF p_error_status = 'W' THEN
2779
2780 IF g_error_message is NULL THEN
2781
2782 g_error_message := substrb(FND_MESSAGE.Get_String('FND', 'FND_MBOX_WARN_CONSTANT'), 1,500);
2783
2784 END IF;
2785
2786 l_error_message := g_error_message;
2787
2788 END IF;*/
2789
2790 l_error_message := p_error_status;
2791
2792 c := FND_MSG_PUB.count_msg;
2793 IF l_debug_on THEN
2794 WSH_DEBUG_SV.logmsg(l_module_name,'COUNT--',c);
2795 END IF;
2796 FOR i in 1..c LOOP
2797 FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
2798 p_msg_index => i,
2799 p_data => l_buffer,
2800 p_msg_index_out => l_index_out);
2801 -- pass only first 2000 characters of l_buffer
2802 -- only for cases when l_buffer is not null
2803 IF l_buffer IS NOT NULL THEN
2804 wsh_xc_util.log_exception(p_api_version => 1.0,
2805 x_return_status => l_return_status,
2806 x_msg_count => l_msg_count,
2807 x_msg_data => l_msg_data,
2808 x_exception_id => l_exception_id,
2809 p_exception_location_id => p_exception_location_id,
2810 p_logged_at_location_id => p_exception_location_id,
2811 p_logging_entity => 'SHIPPER',
2812 p_logging_entity_id => FND_GLOBAL.USER_ID,
2813 p_exception_name => 'WSH_BATCH_MESSAGE',
2814 p_message => substrb(l_buffer,1,2000),
2815 p_trip_stop_id => p_stop_id,
2816 p_delivery_id => p_delivery_id,
2817 p_batch_id => p_batch_id,
2818 p_error_message => l_error_message);
2819 -- Bug 2713285
2820 l_exception_id := null;
2821 END IF;
2822 END LOOP;
2823 --
2824 IF l_debug_on THEN
2825 WSH_DEBUG_SV.pop(l_module_name);
2826 END IF;
2827 --
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;
2837 --
2838
2839 END log_batch_messages;
2840
2841 -- -----------------------------------------------------------------------
2842 -- Name
2843 -- PROCEDURE Select_Delivery_Lines (private within the package)
2844 -- Purpose
2845 -- This procedure select the delivery lines according to the selection criteria and
2846 -- return a list of delivery lines selected.
2847 -- Input Parameters:
2848 -- p_select_criteria This record stores the selection criteria
2849 -- p_autocreate_deliveries Indidate whether to autocreate deliveries for the delivery lines
2850 -- 'Y': do autocreate deliveries , 'SP' shipping parameters
2851 --
2852 -- Output Parameters:
2853 -- x_selected_det_tbl Delivery lines selected
2854 -- x_return_status Return status
2858 p_select_criteria IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
2855 ---- ----------------------------------------------------------------------
2856
2857 PROCEDURE Select_Delivery_Lines (
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
2865 SELECT substrb ( party.party_name, 1, 50 ) customer_name
2866 FROM hz_parties Party, hz_cust_accounts cust_acct
2867 WHERE cust_acct.party_id = party.party_id AND
2868 cust_acct.cust_account_id = c_customer_id;
2869 --TCA View Changes end
2870
2871 l_sc_SELECT VARCHAR2(3000) := NULL;
2872 l_sc_FROM VARCHAR2(3000) := NULL;
2873 l_sc_WHERE VARCHAR2(3000) := NULL;
2874 l_sc_FINAL VARCHAR2(4000) := NULL;
2875 l_sub_str VARCHAR2(2000);
2876 l_msg_string VARCHAR2(80);
2877 l_str_length NUMBER := 0;
2878 l_scheduled_ship_date_lo DATE;
2879 l_scheduled_ship_date_hi DATE;
2880 i NUMBER := 0;
2881 v_delivery_detail_id NUMBER := 0;
2882 v_autocreate_deliveries_flag VARCHAR2(1);
2883 v_cursorID INTEGER;
2884 v_ignore INTEGER;
2885 l_debug_on BOOLEAN;
2886 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Select_Delivery_Lines';
2887
2888 --Perf bug 5218515
2889 v_delivery_detail_id_tbl DBMS_SQL.NUMBER_TABLE;
2890 l_fetch_count NUMBER := 1000;
2891 l_start_index NUMBER := 1;
2892 l_records_fetched NUMBER := 0;
2893 --
2894 -- Bug 5624475 and ECO 5676263
2895 l_line_status VARCHAR2(3);
2896 --
2897 BEGIN
2898 --
2899 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2900 --
2901 IF l_debug_on IS NULL
2902 THEN
2903 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2904 END IF;
2905 --
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
2915 -- p_autocreate_deliveries = 'SP', need to join with wsh_shipping_parameters
2916 l_sc_SELECT := l_sc_SELECT || ' wdd.delivery_detail_id ';
2917 l_sc_FROM := l_sc_FROM || ' wsh_delivery_details wdd , wsh_delivery_assignments_v wda ';
2918 l_sc_FROM := l_sc_FROM || ' , wsh_shipping_parameters wsp ';
2919 l_sc_WHERE := l_sc_WHERE || ' wda.delivery_detail_id = wdd.delivery_detail_id ';
2920 l_sc_WHERE := l_sc_WHERE || ' and wdd.organization_id = wsp.organization_id ';
2921 l_sc_WHERE := l_sc_WHERE || ' and wsp.autocreate_deliveries_flag = ''Y'' ';
2922
2923 ELSIF p_autocreate_deliveries = 'Y' THEN
2924 -- p_autocreate_deliveries = 'Y' do not join with wsh_shipping_parameters
2925 l_sc_SELECT := l_sc_SELECT || ' wdd.delivery_detail_id ';
2926 l_sc_FROM := l_sc_FROM || ' wsh_delivery_details wdd , wsh_delivery_assignments_v wda ';
2927 l_sc_WHERE := l_sc_WHERE || ' wda.delivery_detail_id = wdd.delivery_detail_id ';
2928
2929 ELSE
2930 -- invalud value in p_autocreate_deliveries
2931 IF l_debug_on THEN
2932 WSH_DEBUG_SV.logmsg(l_module_name, ' No delivery lines fetched because p_autocreate_deliveries is: '||p_autocreate_deliveries);
2933 WSH_DEBUG_SV.pop(l_module_name);
2934 END IF;
2935 return;
2936 END IF;
2937
2938 l_sc_WHERE := l_sc_WHERE || ' and wda.delivery_id is null ';
2939 l_sc_WHERE := l_sc_WHERE || ' and wdd.container_flag = ''N'' ';
2940 l_sc_WHERE := l_sc_WHERE || ' and NVL(wdd.line_direction, ''O'') = ''O'' ';
2941
2942
2943 IF p_select_criteria.delivery_lines_status is not NULL THEN
2944 IF p_select_criteria.delivery_lines_status = 'ALL' THEN
2945 l_sc_WHERE := l_sc_WHERE ||'AND wdd.released_status in (''X'', ''R'', ''S'', ''Y'') ';
2946 ELSE
2947 l_sc_WHERE := l_sc_WHERE ||'AND wdd.released_status = :x_released_status ';
2948 END IF;
2949 --
2950 -- Bug 5624475 and ECO 5676263 :
2951 -- Distinguish between Planned for X-dock status and Released to warehouse
2952 --
2953 IF p_select_criteria.delivery_lines_status = 'K' THEN
2954 l_sc_WHERE := l_sc_WHERE || ' AND wdd.move_order_line_id IS NULL ';
2955 ELSIF p_select_criteria.delivery_lines_status = 'S' THEN
2956 l_sc_WHERE := l_sc_WHERE || ' AND wdd.move_order_line_id IS NOT NULL ';
2957 END IF;
2958 -- End Bug 5624475 and ECO 5676263
2959 --
2960 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_STATUS');
2961 l_msg_string := NULL;
2962 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
2963 p_lookup_code => p_select_criteria.delivery_lines_status,
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;
2973 l_sc_WHERE := l_sc_WHERE ||'AND wdd.organization_id = :x_organization_id ';
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
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
2983 IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL OR p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
2984
2985 IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL AND p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
2986 l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
2987 l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
2988
2989 l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled BETWEEN :x_scheduled_ship_date_lo AND :x_scheduled_ship_date_hi ';
2990
2991
2992 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_LO');
2993 FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_LO', to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
2994 FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
2995
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);
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 ';
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
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
3031 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_TO');
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
3041 IF p_select_criteria.source_code = 'BOTH' THEN
3042 l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code in (''OE'', ''OKE'') ';
3043 ELSE
3044 l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code = :x_source_code ';
3045 END IF;
3046
3047 l_msg_string := NULL;
3048 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3049 p_lookup_code => p_select_criteria.source_code,
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
3059
3060 -- put customer id in the where clause if it is part of selection criteria
3061 IF p_select_criteria.customer_id IS NOT NULL THEN
3062 l_sc_WHERE := l_sc_WHERE || 'AND wdd.customer_id = :x_customer_id ';
3063
3064 l_msg_string := NULL;
3065
3066 OPEN get_customer_name(p_select_criteria.customer_id);
3067 FETCH get_customer_name INTO l_msg_string;
3068 CLOSE get_customer_name;
3069
3073
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);
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
3079 l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_method_code = :x_ship_method_code ';
3080 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_METHOD');
3081 l_msg_string := NULL;
3082 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
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));
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
3098 IF p_select_criteria.log_level > 0 OR l_debug_on THEN
3099 -- print SELECT statement if deubg is turned on
3100 i := 1;
3101 l_str_length := length(l_sc_FINAL);
3102
3103 LOOP
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;
3113 END IF;
3114
3115 IF l_debug_on THEN
3116 WSH_DEBUG_SV.logmsg(l_module_name,'Bind variables: ');
3117 WSH_DEBUG_SV.logmsg(l_module_name,'========================');
3118 WSH_DEBUG_SV.log(l_module_name,' x_organization_id', to_char(p_select_criteria.organization_id));
3119 WSH_DEBUG_SV.log(l_module_name,' x_scheduled_ship_date_lo', to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3120 WSH_DEBUG_SV.log(l_module_name,' x_scheduled_ship_date_hi', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3121 WSH_DEBUG_SV.log(l_module_name,' x_source_code', p_select_criteria.source_code);
3122 WSH_DEBUG_SV.log(l_module_name,' x_ship_to_loc_id', to_char(p_select_criteria.ship_to_loc_id));
3123 WSH_DEBUG_SV.log(l_module_name,' x_customer_id', to_char(p_select_criteria.customer_id));
3124 WSH_DEBUG_SV.log(l_module_name,' x_ship_method_code', p_select_criteria.ship_method_code);
3125 WSH_DEBUG_SV.log(l_module_name,' x_released_status', p_select_criteria.delivery_lines_status);
3126 WSH_DEBUG_SV.logmsg(l_module_name,'========================');
3127 END IF;
3128
3129 -- open cursor
3130 v_CursorID := DBMS_SQL.Open_Cursor;
3131
3132 -- parse cursor
3133 DBMS_SQL.Parse(v_CursorID, l_sc_FINAL, DBMS_SQL.v7 );
3134
3135
3136 -- define column
3137 --Perf bug 5218515
3138 DBMS_SQL.Define_Array(v_CursorID, 1, v_delivery_detail_id_tbl ,l_fetch_count ,l_start_index);
3139
3140 --
3141 -- Bug 5624475 and ECO 5676263
3142 IF p_select_criteria.delivery_lines_status IN ('K', 'S') THEN
3143 l_line_status := 'S';
3144 ELSE
3145 l_line_status := p_select_criteria.delivery_lines_status;
3146 END IF;
3147 --
3148 IF l_debug_on THEN
3149 WSH_DEBUG_SV.log(l_module_name, ' l_line_status', l_line_status);
3150 WSH_DEBUG_SV.log(l_module_name, 'p_select_criteria.delivery_lines_status',
3151 p_select_criteria.delivery_lines_status);
3152 END IF;
3153 --
3154 IF p_select_criteria.delivery_lines_status IS NOT NULL AND
3155 p_select_criteria.delivery_lines_status <> 'ALL' THEN
3156 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_released_status', l_line_status); -- Bug 5624475 and ECO 5676263
3157 END IF;
3158
3159 -- bind the variables for organization id
3160 IF p_select_criteria.organization_id IS NOT NULL THEN
3161 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_organization_id', p_select_criteria.organization_id);
3162 END IF;
3163
3164 IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3165 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_scheduled_ship_date_lo', l_scheduled_ship_date_lo);
3166 END IF;
3167
3168 IF p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
3169 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_scheduled_ship_date_hi', l_scheduled_ship_date_hi);
3170 END IF;
3171
3172 IF p_select_criteria.source_code IS NOT NULL
3173 AND p_select_criteria.source_code <> 'BOTH' THEN
3174 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_source_code', p_select_criteria.source_code);
3175 END IF;
3176
3177 IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
3178 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_to_loc_id', p_select_criteria.ship_to_loc_id);
3179 END IF;
3180
3181 IF p_select_criteria.customer_id IS NOT NULL THEN
3182 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_customer_id', p_select_criteria.customer_id);
3183 END IF;
3184
3185 IF p_select_criteria.ship_method_code IS NOT NULL THEN
3189 -- execute the cursor
3186 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_method_code', p_select_criteria.ship_method_code);
3187 END IF;
3188
3190 v_ignore := DBMS_SQL.Execute(v_CursorID);
3191
3192 -- fetching the rows in bulk.
3193 --Perf bug 5218515
3194 Loop
3195 l_records_fetched := dbms_sql.fetch_rows(v_CursorID) ;
3196 dbms_sql.column_value(v_CursorID,1,v_delivery_detail_id_tbl);
3197 IF l_debug_on THEN
3198 WSH_DEBUG_SV.logmsg(l_module_name,' Records Fetched in this pass '|| l_records_fetched);
3199 WSH_DEBUG_SV.logmsg(l_module_name,' l_fetch_count '|| l_fetch_count);
3200 WSH_DEBUG_SV.logmsg(l_module_name,' Record count in v_delivery_detail_id_tbl '||
3201 v_delivery_detail_id_tbl.count);
3202 END IF;
3203 Exit when l_records_fetched <> l_fetch_count ;
3204 End Loop;
3205
3206 If v_delivery_detail_id_tbl.count > 0 then
3207 For i in 1..v_delivery_detail_id_tbl.count
3208 loop
3209 x_selected_det_tbl(i) := v_delivery_detail_id_tbl(i);
3210 If l_debug_on THEN
3211 WSH_DEBUG_SV.logmsg(l_module_name,' Fetched delivery line '|| to_char(x_selected_det_tbl(i)));
3212 End If;
3213 End Loop;
3214 End If;
3215
3216 IF v_cursorID <> 0 THEN
3217 If l_debug_on THEN
3218 WSH_DEBUG_SV.logmsg(l_module_name,' Before closing cursor = '||v_cursorID );
3219 End If;
3220
3221 DBMS_SQL.Close_Cursor(v_cursorID);
3222
3223 If l_debug_on THEN
3224 WSH_DEBUG_SV.logmsg(l_module_name,' After closing cursor = '||v_cursorID );
3225 End If;
3226 END IF;
3227 -- done fetching
3228 -- print debug messages
3229
3230 IF l_debug_on THEN
3231 WSH_DEBUG_SV.logmsg(l_module_name, to_char(x_selected_det_tbl.count)||' delivery lines fetched to be processed');
3232 WSH_DEBUG_SV.pop(l_module_name);
3233 END IF;
3234
3235 EXCEPTION
3236
3237 WHEN others 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;
3247
3248 END Select_Delivery_Lines;
3249
3250 -- -----------------------------------------------------------------------
3251 -- Name
3252 -- PROCEDURE Process_Delivery_Lines_Batch(private within the package)
3253 -- Purpose
3254 -- This procedure works on a list of delivery lines, append these lines to existing
3255 -- deliveries and autocreate deliveries for the lines not appended.
3256 --
3257 -- Input Parameters:
3258 --
3259 -- p_selected_det_tbl List of delivery lines selected
3260 -- p_append_flag Indicate if it needs to append deliveries or not
3261 -- 'Y': append deliveries, 'SP': check the value in shipping parameters
3262 -- p_ac_del_criteria Indicate if deliveries can across order or not
3263 -- 'Y': Within an Order
3264 -- 'N': Across Orders
3265 -- Output Parameters:
3266 -- x_appended_det_num Number of delivery lines appended
3267 -- x_autocreate_del_det_num Number of delivery lines submitted to autocreate deliveries
3268 -- x_new_del_num Number of deliveries created
3269 -- x_msg_count Message count
3270 -- x_msg_data Message data
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,
3280 x_appended_del_num OUT NOCOPY NUMBER,
3281 x_new_del_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);
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;
3294 l_msg_count NUMBER := 0;
3295 l_msg_data VARCHAR2(32767) := NULL;
3296 l_return_status VARCHAR2(1);
3297 l_warning_num NUMBER := 0;
3298 l_number_of_warnings NUMBER := 0;
3299 l_number_of_errors NUMBER := 0;
3300 --
3301 l_debug_on BOOLEAN;
3302 --
3303 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Delivery_Lines_Batch';
3304 --
3305 append_to_deliveries_failed EXCEPTION;
3306 autocreate_delivery_failed EXCEPTION;
3307
3308 BEGIN
3312 IF l_debug_on IS NULL
3309 SAVEPOINT BEFORE_PROCESS_DELIVERIES;
3310 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3311 --
3313 THEN
3314 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3315 END IF;
3316 --
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;
3326 l_unappended_det_tbl.delete;
3327
3328 -- print input parameter if debug is turned on
3329 IF l_debug_on THEN
3330 WSH_DEBUG_SV.logmsg(l_module_name,'Input Parameters:',WSH_DEBUG_SV.C_PROC_LEVEL);
3331 WSH_DEBUG_SV.logmsg(l_module_name,' p_append_flag: '|| p_append_flag,WSH_DEBUG_SV.C_PROC_LEVEL);
3332 WSH_DEBUG_SV.logmsg(l_module_name,' p_ac_del_criteria: '|| p_ac_del_criteria,WSH_DEBUG_SV.C_PROC_LEVEL);
3333 END IF;
3334
3335 -- set append flag
3336 IF p_append_flag = 'SP' THEN
3337 l_append_flag := NULL;
3338 ELSE
3339 l_append_flag := p_append_flag;
3340 END IF;
3341
3342 -- set autocreate delivery criteria
3343 IF p_ac_del_criteria = 'SP' THEN
3344 l_ac_del_criteria := NULL;
3345 ELSE
3346 l_ac_del_criteria := p_ac_del_criteria;
3347 END IF;
3348
3349
3350
3351 -- calling WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries
3352 IF l_append_flag is NULL or l_append_flag = 'Y' THEN
3353 IF l_debug_on THEN
3354 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
3355 END IF;
3356 WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries(
3357 p_delivery_detail_tbl => p_selected_det_tbl,
3358 p_append_flag => l_append_flag,
3359 p_group_by_header => l_ac_del_criteria,
3360 p_commit => FND_API.G_FALSE,
3361 p_lock_rows => FND_API.G_TRUE,
3362 p_check_fte_compatibility => FND_API.G_TRUE,
3363 x_appended_det_tbl => l_appended_det_tbl,
3364 x_unappended_det_tbl => l_unappended_det_tbl,
3365 x_appended_del_tbl => l_appended_del_tbl,
3366 x_return_status => l_return_status);
3367
3368 FND_MSG_PUB.Count_And_Get
3369 (
3370 p_count => l_msg_count,
3371 p_data => l_msg_data,
3372 p_encoded => FND_API.G_FALSE
3373 );
3374 x_appended_det_num := l_appended_det_tbl.count;
3375 x_msg_count := l_msg_count;
3376 x_msg_data := l_msg_data;
3377
3378 IF l_debug_on THEN
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;
3388
3389 ELSIF l_append_flag = 'N' THEN
3390 l_unappended_det_tbl.delete;
3391 l_unappended_det_tbl := p_selected_det_tbl;
3392 ELSE
3393 raise append_to_deliveries_failed;
3394 END IF;
3395
3396 IF l_unappended_det_tbl.count > 0 THEN
3397 IF l_debug_on THEN
3398 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
3399 END IF;
3400
3401 -- calling wsh_interface_grp.delivery_detail_action with action code 'AUTOCREATE-DEL'
3402 l_action_prms.caller := 'WSH_DEPS';
3403 l_action_prms.action_code := 'AUTOCREATE-DEL';
3404 l_action_prms.group_by_header_flag := l_ac_del_criteria;
3405
3406 wsh_interface_grp.delivery_detail_action(
3407 p_api_version_number => 1.0,
3408 p_init_msg_list => FND_API.G_FALSE,
3409 p_commit => FND_API.G_FALSE,
3410 x_return_status => l_return_status,
3411 x_msg_count => l_msg_count,
3412 x_msg_data => l_msg_data,
3413 p_detail_id_tab => l_unappended_det_tbl,
3414 p_action_prms => l_action_prms ,
3415 x_action_out_rec => l_action_out_rec);
3416
3417 IF l_debug_on THEN
3418 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION:'|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
3419 END IF;
3420
3421 x_autocreate_del_det_num := l_unappended_det_tbl.count;
3422 x_appended_del_num := l_appended_del_tbl.count;
3423 x_new_del_num := l_action_out_rec.delivery_id_tab.count;
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;
3433
3430 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3431 l_warning_num := l_warning_num + 1;
3432 END IF;
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;
3440
3441 COMMIT;
3442
3443
3444 IF l_debug_on THEN
3445 WSH_DEBUG_SV.pop(l_module_name);
3446 END IF;
3447
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;
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,
3474 p_data => x_msg_data,
3475 p_encoded => FND_API.G_FALSE
3476 );
3477
3478 IF l_debug_on THEN
3479 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3480 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3481 END IF;
3482
3483 END Process_Delivery_Lines_Batch;
3484
3485 -- -----------------------------------------------------------------------
3486 -- Name
3487 -- PROCEDURE Process_Deliveries_SRS
3488 -- Purpose
3489 -- This procedure is the entry procedure of Process Delivery SRS program
3490 -- It creates consolidated trips for the deliveries if p_entity_type = 'D'
3491 -- (Deliveries Only)
3492 -- and create deliveries for the delivery lines if p_entity_type = 'L' (Delivery Lines Only)
3493 --
3494 -- Input Parameters:
3495 --
3496 -- p_entity_type Entity type, either 'D'(Deliveries Only) or 'L'(Delivery Lines Only)
3497 -- p_delivery_lines_status Delivery Lines selection criteria: Delivery line status
3498 -- p_deliveries_status Deliveries selection criteria: Delivery Status
3499 -- p_scheduled_ship_date_lo Delivery Lines selection criteria: Scheduled Ship Date Start
3500 -- p_scheduled_ship_date_hi Delivery Lines selection criteria: Scheduled Ship Date End
3501 -- p_source_system Delivery Lines selection criteria: Source System
3502 -- p_pickup_date_lo Deliveries selection criteria: Pickup Date Start
3503 -- p_pickup_date_hi Deliveries selection criteria: Pickup Date End
3504 -- p_dropoff_date_lo Deliveries selection criteria: Dropoff Date Start
3505 -- p_dropoff_date_hi Deliveries selection criteria: Dropoff Date End
3506 -- p_organization_id Selection criteria for both delivery lines and deliveries
3507 -- p_customer_id Selection criteria for both delivery lines and deliveries
3508 -- p_ship_to_loc_id Selection criteria for both delivery lines and deliveries
3509 -- p_ship_method_code Selection criteria for both delivery lines and deliveries
3510 -- p_autocreate_deliveries Delivery Lines Only: 'Y': autocreate deliveries, 'SP': Shipping Parameter
3511 -- p_ac_del_criteria Delivery Lines Only: 'Y': Within An order, 'N': Across Order, 'SP': Shipping Parameter
3512 -- p_append_deliveries Delivery Lines Only: 'Y': append deliveries,'N': do not append deliveries 'SP':Shipping Parameter
3513 -- p_grp_ship_method Deliveries Only: 'Y': group deliveries by ship method 'N': do not use ship method to group deliveries
3514 -- p_grp_ship_from Deliveries Only: 'Y': group deliveries by ship from 'N': do not use ship from to group deliveries
3515 -- p_max_number Deliveries Only: Max number of deliveries per trip
3516 -- p_log_level 0: debug log off, 1: debug log on
3517 --
3518 -- Output Parameters:
3519 -- errbuf Starndard parameter
3520 -- retcode Starndard parameter
3521 ---- ----------------------------------------------------------------------
3522 --
3523 PROCEDURE Process_Deliveries_SRS(
3524 errbuf OUT NOCOPY VARCHAR2,
3525 retcode OUT NOCOPY VARCHAR2,
3526 p_entity_type IN VARCHAR2,
3527 p_delivery_lines_status IN VARCHAR2,
3528 p_deliveries_status IN VARCHAR2,
3529 p_scheduled_ship_date_lo IN VARCHAR2,
3530 p_scheduled_ship_date_hi IN VARCHAR2,
3531 p_source_system IN VARCHAR2,
3532 p_pickup_date_lo IN VARCHAR2,
3533 p_pickup_date_hi IN VARCHAR2,
3534 p_dropoff_date_lo IN VARCHAR2,
3535 p_dropoff_date_hi IN VARCHAR2,
3539 p_ship_method_code IN VARCHAR2,
3536 p_organization_id IN NUMBER,
3537 p_customer_id IN VARCHAR2,
3538 p_ship_to_loc_id IN NUMBER,
3540 p_autocreate_deliveries IN VARCHAR2,
3541 p_ac_del_criteria IN VARCHAR2,
3542 p_append_deliveries IN VARCHAR2,
3543 p_grp_ship_method IN VARCHAR2,
3544 p_grp_ship_from IN VARCHAR2,
3545 p_max_del_number IN NUMBER,
3546 p_log_level IN NUMBER ) IS
3547
3548
3549 --TCA View Changes Start
3550 CURSOR get_customer_name(c_customer_id NUMBER) IS
3551 SELECT substrb ( party.party_name, 1, 50 ) customer_name
3552 FROM hz_parties Party, hz_cust_accounts cust_acct
3553 WHERE cust_acct.party_id = party.party_id AND
3554 cust_acct.cust_account_id = c_customer_id;
3555 --TCA View Changes end
3556
3557 CURSOR get_appending_limit (c_organization_id NUMBER) IS
3558 SELECT appending_limit
3559 FROM wsh_shipping_parameters
3560 WHERE organization_id = c_organization_id;
3561
3562 l_completion_status VARCHAR2(30);
3563 l_error_code NUMBER;
3564 l_error_text VARCHAR2(2000);
3565 l_log_level NUMBER := 0;
3566 l_return_status VARCHAR2(30) := NULL;
3567 l_temp BOOLEAN;
3568 l_debug_on BOOLEAN;
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;
3578 l_del_grouped_num NUMBER;
3579 l_trip_num NUMBER;
3580 l_pickup_date_lo DATE;
3581 l_pickup_date_hi DATE;
3582 l_dropoff_date_lo DATE;
3583 l_dropoff_date_hi DATE;
3584 l_scheduled_date_lo DATE;
3585 l_scheduled_date_hi DATE;
3586 l_autocreate_deliveries VARCHAR2(30);
3587 l_append_deliveries VARCHAR2(30);
3588 l_ac_del_criteria VARCHAR2(30);
3589 l_deliveries_status VARCHAR2(30);
3590 l_grp_ship_method VARCHAR2(1);
3591 l_grp_ship_from VARCHAR2(1);
3592 l_appending_limit VARCHAR2(1);
3593 l_msg_string VARCHAR2(80);
3594 l_msg_date DATE;
3595 l_max_del_number NUMBER;
3596 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Deliveries_SRS';
3597 l_buffer VARCHAR2(4000);
3598 l_index_out NUMBER;
3599 WSH_SELECT_ERR EXCEPTION;
3600 Process_Delivery_Lines_ERR EXCEPTION;
3601 Create_Consolidated_Trips_ERR EXCEPTION;
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;
3611 g_new_del_num number :=0;
3612
3613
3614 BEGIN
3615 --
3616 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3617 --
3618 IF l_debug_on IS NULL THEN
3619 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3620 END IF;
3621 --
3622 IF l_debug_on THEN
3623 WSH_DEBUG_SV.push(l_module_name);
3624 WSH_DEBUG_SV.logmsg(l_module_name,'Input Parameters: ',WSH_DEBUG_SV.C_PROC_LEVEL);
3625 WSH_DEBUG_SV.logmsg(l_module_name,'=================================',WSH_DEBUG_SV.C_PROC_LEVEL);
3626 WSH_DEBUG_SV.logmsg(l_module_name,'p_entity_type: '|| p_entity_type,WSH_DEBUG_SV.C_PROC_LEVEL);
3627 WSH_DEBUG_SV.logmsg(l_module_name,'p_append_deliveries: '|| p_append_deliveries,WSH_DEBUG_SV.C_PROC_LEVEL);
3628 END IF;
3629
3630
3631
3632
3633 -- set the completion status to NORMAL
3634 l_completion_status := 'NORMAL';
3635
3636 IF p_log_level IS NULL THEN
3637 l_log_level := 0;
3638 ELSE
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
3648 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PARM');
3649 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3650 FND_FILE.put_line(FND_FILE.output,'====================');
3651
3652 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PROCESSED_ENTITIES');
3653 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3654 p_lookup_code => p_entity_type,
3655 p_lookup_type => 'WSH_PD_ENTITY' );
3656 FND_MESSAGE.SET_TOKEN('ENTITY_TYPE', l_msg_string);
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
3667 FND_FILE.put_line(FND_FILE.output, ' ');
3664 IF p_entity_type = 'D' THEN
3665 --
3666 IF WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
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);
3677 l_dropoff_date_lo := fnd_date.canonical_to_date(p_dropoff_date_lo);
3678 l_dropoff_date_hi := fnd_date.canonical_to_date(p_dropoff_date_hi);
3679
3680 -- bug 3319789
3681 IF p_scheduled_ship_date_lo is not NULL OR
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
3691
3692 -- default value : Set Deliveries Status to 'BOTH'
3693 IF p_deliveries_status IS NULL THEN
3694 l_deliveries_status := 'BOTH';
3695 ELSE
3696 l_deliveries_status := p_deliveries_status;
3697 END IF;
3698
3699 -- default value : Set Group Deliveries by Ship Method to 'Y'
3700 IF p_grp_ship_method IS NULL THEN
3701 l_grp_ship_method := 'Y';
3702 ELSE
3703 l_grp_ship_method := p_grp_ship_method;
3704 END IF;
3705
3706 -- default value : Set Group Deliveries by Ship From Organization to 'Y'
3707 IF p_grp_ship_from IS NULL THEN
3708 l_grp_ship_from := 'Y';
3709 ELSE
3710 l_grp_ship_from := p_grp_ship_from;
3711 END IF;
3712
3713 -- default value : Set Maximum Number of Deliveries per Trip to 50
3714 IF p_max_del_number IS NULL THEN
3715 l_max_del_number := 50;
3716 ELSE
3717 l_max_del_number := p_max_del_number;
3718 END IF;
3719
3720
3721 -- print Input Parameters
3722
3723 IF l_deliveries_status IS NOT NULL THEN
3724 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_STATUS');
3725 l_msg_string := NULL;
3726 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
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');
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');
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
3750 p_pickup_date_lo is NOT NULL THEN
3751 IF l_pickup_date_hi < l_pickup_date_lo THEN
3752 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_RANGE');
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');
3774 END IF;
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'));
3775
3776 -- bug 3332670
3777 IF p_dropoff_date_hi is NOT NULL and
3778 p_dropoff_date_lo is NOT NULL THEN
3779 IF l_dropoff_date_hi < l_dropoff_date_lo THEN
3780 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_RANGE');
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
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
3799 OPEN get_customer_name(p_customer_id);
3800 FETCH get_customer_name INTO l_msg_string;
3801 CLOSE get_customer_name;
3802
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(
3812 p_location_id => p_ship_to_loc_id,
3813 p_format => 'NEW UI CODE'),
3814 1, 80);
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
3824 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_METHOD');
3825 l_msg_string := NULL;
3826 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
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');
3836 l_msg_string := NULL;
3837 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
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');
3847 l_msg_string := NULL;
3848 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
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');
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');
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);
3872 END IF;
3873
3877 p_pickup_start => l_pickup_date_lo,
3874 -- calling WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips to create trips
3875 WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips(
3876 p_deliv_status => l_deliveries_status,
3878 p_pickup_end => l_pickup_date_hi,
3879 p_dropoff_start => l_dropoff_date_lo,
3880 p_dropoff_end => l_dropoff_date_hi,
3881 p_ship_from_org_id => p_organization_id,
3882 p_customer_id => p_customer_id,
3883 p_ship_to_location => p_ship_to_loc_id,
3884 p_ship_method_code => p_ship_method_code,
3885 p_grp_ship_method => l_grp_ship_method,
3886 p_grp_ship_from => l_grp_ship_from,
3887 p_max_num_deliveries => l_max_del_number,
3888 x_TotDeliveries => l_del_num,
3889 x_SuccessDeliv => l_del_grouped_num,
3890 x_Trips => l_trip_num,
3891 x_return_status => l_return_status);
3892
3893 -- print the summary results of auto create trips action
3894 FND_FILE.put_line(FND_FILE.output,' ');
3895 FND_FILE.put_line(FND_FILE.output,' ');
3896 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SUMMARY');
3897 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3898 FND_FILE.put_line(FND_FILE.output,'====================');
3899
3900 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_SELECTED');
3901 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(l_del_num));
3902 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3903
3904 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_GROUPED');
3905 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(l_del_grouped_num));
3906 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3907
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');
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);
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;
3929
3930 FND_FILE.put_line(FND_FILE.output,' ');
3931 FND_FILE.put_line(FND_FILE.output,' ');
3932 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3933
3934 -- print warning or error messages Process_Delivery_Lines_Batch
3935 l_msg_count := FND_MSG_PUB.count_msg;
3936
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,
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;
3955 raise Create_Consolidated_Trips_ERR;
3956 END IF;
3957
3958 END IF; -- TP is not installed
3959 ELSE
3960
3961 IF l_debug_on THEN
3962 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Select_Deliveries_Lines ',WSH_DEBUG_SV.C_PROC_LEVEL);
3963 END IF;
3964
3965 -- bug 3319789
3966 IF p_pickup_date_lo is not NULL OR
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
3976 IF p_dropoff_date_lo is not NULL OR
3977 p_dropoff_date_hi is not NULL THEN
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.');
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);
3982 raise Parameters_ERR;
3983 END IF;
3984
3985 -- set default value of Delivery Lines Status to 'ALL'
3986 IF p_delivery_lines_status IS NULL THEN
3987 l_select_criteria.delivery_lines_status := 'ALL';
3988 ELSE
3989 l_select_criteria.delivery_lines_status := p_delivery_lines_status;
3990 END IF;
3991
3992 -- bug 3332670
3993 IF p_scheduled_ship_date_lo is not NULL AND
3994 p_scheduled_ship_date_hi is not NULL THEN
3995 l_scheduled_date_lo := fnd_date.canonical_to_date(p_scheduled_ship_date_lo);
3996 l_scheduled_date_hi := fnd_date.canonical_to_date(p_scheduled_ship_date_hi);
3997 IF l_scheduled_date_hi < l_scheduled_date_lo THEN
3998 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCH_SHIP_DATE_RANGE');
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
4008 l_select_criteria.scheduled_ship_date_lo := p_scheduled_ship_date_lo;
4009 l_select_criteria.scheduled_ship_date_hi := p_scheduled_ship_date_hi;
4010
4011 -- set default value of Source System to 'BOTH'
4012 IF p_source_system IS NULL THEN
4013 l_select_criteria.source_code := 'BOTH';
4014 ELSE
4015 l_select_criteria.source_code := p_source_system;
4016 END IF;
4017
4018 l_select_criteria.organization_id := p_organization_id;
4019 l_select_criteria.customer_id := p_customer_id;
4020 l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
4021 l_select_criteria.ship_method_code := p_ship_method_code;
4022
4023 IF p_log_level IS NULL THEN
4024 l_select_criteria.log_level := 0;
4025 ELSE
4026 l_select_criteria.log_level := p_log_level;
4027 END IF;
4028
4029 -- set append deliveries and autocreate deliveries crtieria to shipping parameter
4030 -- if autocreate deliveries is shipping parameter
4031
4032 IF p_autocreate_deliveries IS NULL OR p_autocreate_deliveries = 'SP' THEN
4033 l_autocreate_deliveries := 'SP';
4034 l_append_deliveries := 'N';
4035 l_ac_del_criteria := 'SP';
4036 ELSIF p_autocreate_deliveries = 'N' THEN
4037 -- turn off append deliveries and autocreate deliveries criteria if autocreate
4038 -- deliveries is off
4039 l_autocreate_deliveries := 'N';
4040 l_append_deliveries := 'N';
4041 l_ac_del_criteria := 'N';
4042 ELSE
4043 -- get the value of append deliveries and autocreate deliveries criteria from
4044 -- user input
4045 l_autocreate_deliveries := p_autocreate_deliveries;
4046 l_ac_del_criteria := p_ac_del_criteria;
4047 IF p_ac_del_criteria is NULL or p_ac_del_criteria = 'SP' or p_ac_del_criteria = 'Y' THEN
4048 l_append_deliveries := 'N';
4049 ELSE
4050 l_append_deliveries := p_append_deliveries;
4051 END IF;
4052 END IF;
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';
4062 END IF;
4063
4064 IF l_append_deliveries is NULL THEN
4065 IF p_organization_id is not NULL THEN
4066 OPEN get_appending_limit(p_organization_id);
4067 FETCH get_appending_limit INTO l_appending_limit;
4068 IF get_appending_limit%NOTFOUND THEN
4069 CLOSE get_appending_limit;
4070 raise Process_Delivery_Lines_ERR;
4071 END IF;
4072 CLOSE get_appending_limit;
4073 IF l_appending_limit ='N' OR l_appending_limit is NULL THEN
4074 l_append_deliveries := 'N';
4075 ELSE
4076 l_append_deliveries := 'Y';
4077 END IF;
4078 ELSE
4079 l_append_deliveries := 'N';
4080 END IF;
4081 END IF;
4082
4083 IF l_autocreate_deliveries is not NULL THEN
4084 l_msg_string := NULL;
4085 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
4086 p_lookup_code => l_autocreate_deliveries,
4087 p_lookup_type => 'YES_NO');
4088 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_AUTOCREATE_DEL');
4089 FND_MESSAGE.SET_TOKEN('AUTOCREATE_DEL', l_msg_string);
4090 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4091 END IF;
4092
4096 p_lookup_code => l_ac_del_criteria,
4093 IF l_ac_del_criteria is not NULL THEN
4094 l_msg_string := NULL;
4095 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
4097 p_lookup_type => 'WSH_AC_DEL_CRITERIA');
4098 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_AC_DEL_CRITERIA');
4099 FND_MESSAGE.SET_TOKEN('AC_DEL_CRITERIA', l_msg_string);
4100 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4101 END IF;
4102
4103 IF l_append_deliveries is not NULL THEN
4104 l_msg_string := NULL;
4105 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
4106 p_lookup_code => l_append_deliveries,
4107 p_lookup_type => 'YES_NO');
4108 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_APPEND_DEL');
4109 FND_MESSAGE.SET_TOKEN('APPEND_DEL', l_msg_string);
4110 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4111 END IF;
4112
4113 -- populate the selection criteria record and call select_delivery_lines
4114 Select_Delivery_Lines(
4115 p_select_criteria => l_select_criteria,
4116 p_autocreate_deliveries => l_autocreate_deliveries,
4117 x_selected_det_tbl => l_selected_det_tbl,
4118 x_return_status => l_return_status);
4119
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
4129 IF l_selected_det_tbl.count > 0 THEN
4130 --Perf Bug 5215740
4131 l_processed := 0;
4132 Loop
4133 l_inner_loop_count :=0;
4134 <<inner_loop>>
4135 Loop
4136 l_processed := l_processed +1 ;
4137 l_inner_loop_count := l_inner_loop_count + 1;
4138 l_selected_det_tbl_tmp(l_inner_loop_count) := l_selected_det_tbl(l_processed);
4139 If (( l_inner_loop_count = 1000) or
4140 (l_processed = l_selected_det_tbl.count)) then
4141 Exit inner_loop;
4142 End If;
4143 End Loop;
4144
4145 -- have selected some lines for auto create
4146 IF l_debug_on THEN
4147 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery lines selected to be processed are: ');
4148 FOR k in 1 .. l_selected_det_tbl_tmp.count LOOP
4149 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery lines: ' || l_selected_det_tbl_tmp(k));
4150 END LOOP;
4151
4152 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Process_Delivery_Lines_Batch',WSH_DEBUG_SV.C_PROC_LEVEL);
4153 END IF;
4154
4155 -- call Process_delivery_lines_batch to append deliveries and autocreate deliveries
4156 Process_Delivery_Lines_Batch(
4157 p_selected_det_tbl => l_selected_det_tbl_tmp,
4158 p_append_flag => l_append_deliveries,
4159 p_ac_del_criteria => l_ac_del_criteria,
4160 x_appended_det_num => l_appended_det_num,
4161 x_autocreate_del_det_num => l_autocreate_del_det_num,
4162 x_appended_del_num => l_appended_del_num,
4163 x_new_del_num => l_new_del_num,
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');
4173 END IF;
4174 FND_FILE.put_line(FND_FILE.output,' ');
4175 FND_FILE.put_line(FND_FILE.output,' ');
4176 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4177
4178 -- print warning or error messages Process_Delivery_Lines_Batch
4179 l_msg_count := FND_MSG_PUB.count_msg;
4180
4181 IF l_msg_count > 0 THEN
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,
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;
4199 raise Process_Delivery_Lines_ERR;
4200 END IF;
4201
4202 g_selected_det_count := g_selected_det_count + l_selected_det_tbl_tmp.count;
4203 g_appended_det_num := g_appended_det_num + l_appended_det_num;
4204 g_autocreate_del_det_num := g_autocreate_del_det_num + l_autocreate_del_det_num ;
4205 g_appended_del_num := g_appended_del_num + l_appended_del_num;
4206 g_new_del_num := g_new_del_num + l_new_del_num ;
4207
4211 WSH_DEBUG_SV.logmsg(l_module_name,'l_selected_det_tbl_tmp.count = '|| l_selected_det_tbl_tmp.count ,WSH_DEBUG_SV.C_PROC_LEVEL);
4208 IF l_debug_on THEN
4209 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Process_Delivery_Lines_Batch is '|| l_return_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
4210
4212 WSH_DEBUG_SV.logmsg(l_module_name,'g_selected_det_count = '|| g_selected_det_count ,WSH_DEBUG_SV.C_PROC_LEVEL);
4213
4214 WSH_DEBUG_SV.logmsg(l_module_name,'l_appended_det_num = '|| l_appended_det_num,WSH_DEBUG_SV.C_PROC_LEVEL);
4215 WSH_DEBUG_SV.logmsg(l_module_name,'g_appended_det_num = '|| g_appended_det_num,WSH_DEBUG_SV.C_PROC_LEVEL);
4216
4217 WSH_DEBUG_SV.logmsg(l_module_name,'l_autocreate_del_det_num = '|| l_autocreate_del_det_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4218 WSH_DEBUG_SV.logmsg(l_module_name,'g_autocreate_del_det_num = '|| g_autocreate_del_det_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4219
4220 WSH_DEBUG_SV.logmsg(l_module_name,'l_appended_del_num = '|| l_appended_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4221 WSH_DEBUG_SV.logmsg(l_module_name,'g_appended_del_num = '|| g_appended_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4222
4223 WSH_DEBUG_SV.logmsg(l_module_name,'l_new_del_num = '|| l_new_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4224 WSH_DEBUG_SV.logmsg(l_module_name,'g_new_del_num = '|| g_new_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4225 END IF;
4226
4227 exit when l_processed = l_selected_det_tbl.count;
4228 l_selected_det_tbl_tmp.delete;
4229 l_appended_det_num := 0;
4230 l_autocreate_del_det_num := 0;
4231 l_appended_del_num := 0;
4232 l_new_del_num := 0;
4233 End Loop;
4234
4235 -- print results of appending deliveries and autocreate deliveries
4236 -- print the summary results of auto create trips action
4237 FND_FILE.put_line(FND_FILE.output,' ');
4238 FND_FILE.put_line(FND_FILE.output,' ');
4239 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SUMMARY');
4240 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4241 FND_FILE.put_line(FND_FILE.output,'====================');
4242
4243 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
4244 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_selected_det_count));
4245 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4246
4247 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_APPENDED');
4248 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_appended_det_num));
4249 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4250
4251 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_GROUPED');
4252 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_autocreate_del_det_num));
4253 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4254
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');
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
4270 FND_FILE.put_line(FND_FILE.output,' ');
4271 FND_FILE.put_line(FND_FILE.output,' ');
4272 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SUMMARY');
4273 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4274 FND_FILE.put_line(FND_FILE.output,'====================');
4275
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;
4285
4286 END IF;
4287
4288 errbuf := 'Process Deliveries is completed successfully';
4289 retcode := '0';
4290 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4291
4292 IF l_debug_on THEN
4293 WSH_DEBUG_SV.pop(l_module_name);
4294 END IF;
4295
4296
4297 EXCEPTION
4298
4299 WHEN Create_Consolidated_Trips_ERR THEN
4300 l_completion_status := 'WARNING';
4301 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4302 errbuf := 'Process Deliveries completed with warnings';
4303 retcode := '1';
4304 IF l_debug_on THEN
4305 WSH_DEBUG_SV.pop(l_module_name);
4306 END IF;
4307
4308 WHEN Process_Delivery_Lines_ERR THEN
4309 l_completion_status := 'WARNING';
4310 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4311 errbuf := 'Process Deliveries completed with warnings';
4312 retcode := '1';
4313 IF l_debug_on THEN
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';
4323 retcode := '1';
4324 IF l_debug_on THEN
4325 WSH_DEBUG_SV.pop(l_module_name);
4326 END IF;
4327
4328 WHEN Parameters_ERR THEN
4329 l_completion_status := 'WARNING';
4330 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4331 errbuf := 'Process Deliveries is completed with warning';
4332 retcode := '1';
4333 IF l_debug_on THEN
4334 WSH_DEBUG_SV.pop(l_module_name);
4335 END IF;
4336
4337
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';
4347 IF l_debug_on THEN
4348 WSH_DEBUG_SV.pop(l_module_name);
4349 END IF;
4350
4351 END Process_Deliveries_SRS;
4352
4353
4354 END WSH_BATCH_PROCESS;
4355
4356