[Home] [Help]
PACKAGE BODY: APPS.WSH_BATCH_PROCESS
Source
1 PACKAGE BODY WSH_BATCH_PROCESS as
2 /* $Header: WSHBHPSB.pls 120.18 2011/04/20 08:19:19 ashimalh ship $ */
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 --RTV changes
89 IF (p_input_info.customer_id is not null) THEN
90 IF SubStr(p_input_info.customer_id,1,1) = 'V' THEN
91 l_sc_WHERE := l_sc_WHERE || 'AND wnd.consignee_flag = ''V'' ';
92 ELSE
93 l_sc_WHERE := l_sc_WHERE || 'AND nvl(wnd.consignee_flag,''C'') = ''C'' ';
94 END IF;
95 END IF;
96 --RTV changes
97
98 l_sc_EXISTS := l_sc_EXISTS ||' SELECT wdd.delivery_detail_id ';
99 l_sc_EXISTS := l_sc_EXISTS ||' FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda ';
103 l_sc_EXISTS := l_sc_EXISTS ||' AND wdd.container_flag = ''N'' ';
100 l_sc_EXISTS := l_sc_EXISTS ||' WHERE wda.delivery_detail_id = wdd.delivery_detail_id ';
101 l_sc_EXISTS := l_sc_EXISTS ||' AND wda.delivery_id = wnd.delivery_id ';
102 l_sc_EXISTS := l_sc_EXISTS ||' AND wda.delivery_id IS NOT NULL ';
104
105 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
106 l_sc_EXISTS := l_sc_EXISTS ||' AND wdd.released_status in (''Y'', ''X'') ';
107 ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
108 l_sc_EXISTS := l_sc_EXISTS ||' AND wdd.released_status in (''Y'', ''X'', ''R'', ''B'') ';
109 END IF;
110
111 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' SELECT wdd2.delivery_detail_id ';
112 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' FROM wsh_delivery_details wdd2, wsh_delivery_assignments_v wda2 ';
113 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' WHERE wnd.delivery_id = wda2.delivery_id ';
114 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wda2.delivery_id IS NOT NULL ';
115 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wda2.delivery_detail_id = wdd2.delivery_detail_id ';
116 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.container_flag = ''N'' ';
117
118 IF p_input_info.organization_id IS NOT NULL THEN
119 l_sc_WHERE := l_sc_WHERE ||'AND wnd.organization_id = :x_organization_id ';
120 WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_input_info.organization_id);
121 p_batch_rec.organization_id := p_input_info.organization_id;
122 END IF;
123
124 IF p_input_info.pr_batch_id IS NOT NULL THEN
125
126 l_sc_EXISTS := l_sc_EXISTS ||'AND wdd.batch_id = :x_pr_batch_id ';
127
128 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
129 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 ) ';
130 ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
131 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND (wdd2.released_status in (''S'', ''C'' , ''N'') OR NVL(wdd2.batch_id, -999) <> :x_pr_batch_id ) ';
132 END IF;
133
134 WSH_UTIL_CORE.PrintMsg(' Pick Release Batch ID: '|| p_input_info.pr_batch_id);
135 p_batch_rec.selected_batch_id := p_input_info.pr_batch_id;
136
137 ELSE
138
139 IF p_input_info.process_mode = G_SHIP_CONFIRM THEN
140 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.released_status in (''R'', ''S'', ''B'', ''C'', ''N'') ';
141 ELSIF p_input_info.process_mode = G_AUTO_PACK THEN
142 l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' AND wdd2.released_status in (''S'', ''C'', ''N'') ';
143 END IF;
144
145 END IF;
146
147 /*Modified R12.1.1 LSP PROJECT*/
148 IF p_input_info.client_id IS NOT NULL THEN
149 l_sc_WHERE := l_sc_WHERE || 'AND wnd.client_id = :x_client_id ';
150 WSH_UTIL_CORE.PrintMsg(' Client ID: '|| p_input_info.client_id);
151 p_batch_rec.client_id := p_input_info.client_id; -- Assign to OUT Parameter
152 END IF;
153 /*Modified R12.1.1 LSP PROJECT*/
154
155 IF p_input_info.ap_batch_id IS NOT NULL THEN
156 l_sc_WHERE := l_sc_WHERE || 'AND wnd.ap_batch_id = :x_ap_batch_id ';
157 WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch ID: '|| p_input_info.ap_batch_id);
158 END IF;
159
160 IF p_input_info.delivery_name_lo IS NOT NULL OR p_input_info.delivery_name_hi IS NOT NULL THEN
161 IF p_input_info.delivery_name_lo IS NOT NULL AND p_input_info.delivery_name_hi IS NOT NULL THEN
162 l_sc_WHERE := l_sc_WHERE ||'AND wnd.name BETWEEN :x_delivery_name_lo AND :x_delivery_name_hi ';
163 WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
164 p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
165 WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
166 p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
167 ELSIF p_input_info.delivery_name_lo IS NOT NULL THEN
168 l_sc_WHERE := l_sc_WHERE ||'AND wnd.name >= :x_delivery_name_lo ';
169 WSH_UTIL_CORE.PrintMsg(' Delivery Name (Low): '|| p_input_info.delivery_name_lo);
170 p_batch_rec.delivery_name_lo := p_input_info.delivery_name_lo;
171 ELSE
172 l_sc_WHERE := l_sc_WHERE ||'AND wnd.name <= :x_delivery_name_hi ';
173 WSH_UTIL_CORE.PrintMsg(' Delivery Name (High): '|| p_input_info.delivery_name_hi);
174 p_batch_rec.delivery_name_hi := p_input_info.delivery_name_hi;
175 END IF;
176 END IF;
177
178 IF (p_input_info.bol_number_lo IS NOT NULL OR p_input_info.bol_number_hi IS NOT NULL) THEN
179 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' SELECT wdi.document_instance_id ';
180 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' FROM wsh_delivery_legs wlg ';
181 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' ,wsh_document_instances wdi ';
182 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' WHERE wnd.delivery_id = wlg.delivery_id ';
183 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wlg.delivery_leg_id = wdi.entity_id ';
184 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wdi.entity_name= ''WSH_DELIVERY_LEGS'' ';
185 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wdi.document_type =''BOL'' ';
186 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' AND wdi.status <> ''CANCELED'' ';
187
188 IF p_input_info.bol_number_lo IS NOT NULL AND p_input_info.bol_number_hi IS NOT NULL THEN
189 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number BETWEEN :x_bol_number_lo AND :x_bol_number_hi ';
190
191 WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
192 p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
193
194 WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
195 p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
196
197 ELSIF p_input_info.bol_number_lo IS NOT NULL THEN
198 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number >= :x_bol_number_lo ';
199 WSH_UTIL_CORE.PrintMsg(' BOL Number (Low): '|| p_input_info.bol_number_lo);
200 p_batch_rec.bol_number_lo := p_input_info.bol_number_lo;
201
202 ELSE
203 l_sc_EXISTS_BOL := l_sc_EXISTS_BOL || 'AND wdi.sequence_number <= :x_bol_number_hi ';
204 WSH_UTIL_CORE.PrintMsg(' BOL Number (High): '|| p_input_info.bol_number_hi);
205 p_batch_rec.bol_number_hi := p_input_info.bol_number_hi;
206 END IF;
207
208 END IF;
209
210
211 IF p_input_info.planned_flag IS NOT NULL THEN
212 l_sc_WHERE := l_sc_WHERE || 'AND wnd.planned_flag = :x_planned_flag ';
213 WSH_UTIL_CORE.PrintMsg(' Planned Flag: '|| p_input_info.planned_flag);
214 p_batch_rec.planned_flag := p_input_info.planned_flag;
215 END IF;
216
217 IF p_input_info.ship_from_loc_id IS NOT NULL THEN
218 l_sc_WHERE := l_sc_WHERE || 'AND wnd.initial_pickup_location_id = :x_ship_from_loc_id ';
219 WSH_UTIL_CORE.PrintMsg(' Ship from Location ID: '|| to_char(p_input_info.ship_from_loc_id));
220 p_batch_rec.ship_from_location_id := p_input_info.ship_from_loc_id;
221 END IF;
222
223 IF p_input_info.ship_to_loc_id IS NOT NULL THEN
224 l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_location_id = :x_ship_to_loc_id ';
225 WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_input_info.ship_to_loc_id));
226 p_batch_rec.ship_to_location_id := p_input_info.ship_to_loc_id;
227 END IF;
228
229 IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
230 l_sc_WHERE := l_sc_WHERE || 'AND wnd.intmed_ship_to_location_id = :x_intmed_ship_to_loc_id ';
231 WSH_UTIL_CORE.PrintMsg(' Intermediate Ship to Location ID: '|| to_char(p_input_info.intmed_ship_to_loc_id));
232 p_batch_rec.intmed_ship_to_loc_id := p_input_info.intmed_ship_to_loc_id;
233 END IF;
234
235 IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
236 l_sc_WHERE := l_sc_WHERE || 'AND wnd.pooled_ship_to_location_id = :x_pooled_ship_to_loc_id ';
237 WSH_UTIL_CORE.PrintMsg(' Pooled Ship to Location ID: '|| p_input_info.pooled_ship_to_loc_id);
238 p_batch_rec.pooled_ship_to_loc_id := p_input_info.pooled_ship_to_loc_id;
239 END IF;
240
241 IF p_input_info.customer_id IS NOT NULL THEN
242 l_sc_WHERE := l_sc_WHERE || 'AND wnd.customer_id = :x_customer_id ';
243 -- RTV Changes
244 WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| SubStr(p_input_info.customer_id,3));
245 p_batch_rec.customer_id := SubStr(p_input_info.customer_id,3);
246 END IF;
247
248 IF p_input_info.ship_method_code IS NOT NULL THEN
249 l_sc_WHERE := l_sc_WHERE || 'AND wnd.ship_method_code = :x_ship_method_code ';
250 WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_input_info.ship_method_code);
251 p_batch_rec.ship_method_code := p_input_info.ship_method_code;
252 END IF;
253
254 IF p_input_info.fob_code IS NOT NULL THEN
255 l_sc_WHERE := l_sc_WHERE || 'AND wnd.fob_code = :x_fob_code ';
256 WSH_UTIL_CORE.PrintMsg(' FOB Code: '|| p_input_info.fob_code);
257 p_batch_rec.fob_code := p_input_info.fob_code;
258 END IF;
259
260 IF p_input_info.freight_terms_code IS NOT NULL THEN
261 l_sc_WHERE := l_sc_WHERE || 'AND wnd.freight_terms_code = :x_freight_terms_code ';
262
263 WSH_UTIL_CORE.PrintMsg(' Freight Term Code: '|| p_input_info.freight_terms_code);
264 p_batch_rec.freight_terms_code := p_input_info.freight_terms_code;
265 END IF;
266
267 IF p_input_info.pickup_date_lo IS NOT NULL OR p_input_info.pickup_date_hi IS NOT NULL THEN
268 IF p_input_info.pickup_date_lo IS NOT NULL AND p_input_info.pickup_date_hi IS NOT NULL THEN
269
270 l_pickup_date_lo := fnd_date.canonical_to_date(p_input_info.pickup_date_lo);
271 l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
272 l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) BETWEEN :x_pickup_date_lo AND :x_pickup_date_hi ';
273 WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
274 p_batch_rec.pickup_date_lo := l_pickup_date_lo;
275 WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
276 p_batch_rec.pickup_date_hi := l_pickup_date_hi;
277
278 ELSIF p_input_info.pickup_date_lo IS NOT NULL THEN
279
280 l_pickup_date_lo := fnd_date.canonical_to_date(p_input_info.pickup_date_lo);
281 l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) >= :x_pickup_date_lo ';
282 WSH_UTIL_CORE.PrintMsg(' Pick-up Date (Low): '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
283 p_batch_rec.pickup_date_lo := l_pickup_date_lo;
284
285 ELSE
286
287 l_pickup_date_hi := fnd_date.canonical_to_date(p_input_info.pickup_date_hi);
288 l_sc_WHERE := l_sc_WHERE || 'AND NVL(wnd.initial_pickup_date, sysdate) <= :x_pickup_date_hi ';
289 WSH_UTIL_CORE.PrintMsg(' Pick-up Date (High): '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
290 p_batch_rec.pickup_date_hi := l_pickup_date_hi;
291 END IF;
292 END IF;
293
294 IF p_input_info.dropoff_date_lo IS NOT NULL OR p_input_info.dropoff_date_hi IS NOT NULL THEN
295
296 IF p_input_info.dropoff_date_lo IS NOT NULL AND p_input_info.dropoff_date_hi IS NOT NULL THEN
297 l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
298 l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
299 l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date BETWEEN :x_dropoff_date_lo AND :x_dropoff_date_hi ';
300 WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
301 p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
302 WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
303 p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
304
305 ELSIF p_input_info.dropoff_date_lo IS NOT NULL THEN
306 l_dropoff_date_lo := fnd_date.canonical_to_date(p_input_info.dropoff_date_lo);
307 l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date >= :x_dropoff_date_lo ';
308 WSH_UTIL_CORE.PrintMsg(' Drop-off Date (Low): '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
309 p_batch_rec.dropoff_date_lo := l_dropoff_date_lo;
310
311 ELSE
312 l_dropoff_date_hi := fnd_date.canonical_to_date(p_input_info.dropoff_date_hi);
313 l_sc_WHERE := l_sc_WHERE || 'AND wnd.ultimate_dropoff_date <= :x_dropoff_date_hi ';
317 END IF;
314 WSH_UTIL_CORE.PrintMsg(' Drop-off Date (High): '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
315 p_batch_rec.dropoff_date_hi := l_dropoff_date_hi;
316 END IF;
318
319 WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_input_info.log_level);
320
321 l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
322 IF length(l_sc_EXISTS_BOL) > 0 THEN
323 l_sc_FINAL := l_sc_FINAL ||'AND EXISTS ( '|| l_sc_EXISTS_BOL || ' ) ';
324 END IF;
325 l_sc_FINAL := l_sc_FINAL || ' AND EXISTS ( '|| l_sc_EXISTS ||' ) AND NOT EXISTS ( '|| l_sc_NOT_EXISTS||' )';
326
327 IF p_input_info.log_level > 0 OR l_debug_on THEN
328 -- print SELECT statement
329 i := 1;
330 l_str_length := length(l_sc_FINAL);
331
332 LOOP
333 IF i > l_str_length THEN
334 EXIT;
335 END IF;
336 l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
337 -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
338 WSH_UTIL_CORE.PrintMsg(l_sub_str);
339 i := i + 80;
340 -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
341 END LOOP;
342 END IF;
343
344 v_CursorID := DBMS_SQL.Open_Cursor;
345
346 DBMS_SQL.Parse(v_CursorID, l_sc_FINAL, DBMS_SQL.v7 );
347
348 DBMS_SQL.Define_Column(v_CursorID, 1, v_delivery_id);
349 DBMS_SQL.Define_Column(v_CursorID, 2, v_organization_id);
350 DBMS_SQL.Define_Column(v_CursorID, 3, v_initial_pickup_location_id);
351
352 /* Modified R12.1.1 LSP PROJECT*/
353 IF p_input_info.client_id IS NOT NULL THEN
354 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_client_id', p_input_info.client_id); --Modified R12.1.1 LSP PROJECT
355 END IF;
356 /* Modified R12.1.1 LSP PROJECT*/
357
358 IF p_input_info.organization_id IS NOT NULL THEN
359 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_organization_id', p_input_info.organization_id);
360 END IF;
361
362 IF p_input_info.pr_batch_id IS NOT NULL THEN
363 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pr_batch_id', p_input_info.pr_batch_id);
364 END IF;
365
366 IF p_input_info.ap_batch_id IS NOT NULL THEN
367 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ap_batch_id', p_input_info.ap_batch_id);
368 END IF;
369
370 IF p_input_info.delivery_name_lo IS NOT NULL THEN
371 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_delivery_name_lo', p_input_info.delivery_name_lo);
372 END IF;
373
374 IF p_input_info.delivery_name_hi IS NOT NULL THEN
375 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_delivery_name_hi', p_input_info.delivery_name_hi);
376 END IF;
377
378
379 IF p_input_info.bol_number_lo IS NOT NULL THEN
380 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_bol_number_lo', p_input_info.bol_number_lo) ;
381 END IF;
382
383 IF p_input_info.bol_number_hi IS NOT NULL THEN
384 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_bol_number_hi', p_input_info.bol_number_hi);
385 END IF;
386
387
388 IF p_input_info.planned_flag IS NOT NULL THEN
389 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_planned_flag', p_input_info.planned_flag);
390 END IF;
391
392 IF p_input_info.ship_from_loc_id IS NOT NULL THEN
393 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_from_loc_id', p_input_info.ship_from_loc_id);
394 END IF;
395
396 IF p_input_info.ship_to_loc_id IS NOT NULL THEN
397 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_to_loc_id', p_input_info.ship_to_loc_id);
398 END IF;
399
400 IF p_input_info.intmed_ship_to_loc_id IS NOT NULL THEN
401 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_intmed_ship_to_loc_id', p_input_info.intmed_ship_to_loc_id);
402 END IF;
403
404 IF p_input_info.pooled_ship_to_loc_id IS NOT NULL THEN
405 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pooled_ship_to_loc_id', p_input_info.pooled_ship_to_loc_id);
406 END IF;
407
408 IF p_input_info.customer_id IS NOT NULL THEN
409 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_customer_id', to_number(substr(p_input_info.customer_id,3))); --RTV changes
410 END IF;
411
412 IF p_input_info.ship_method_code IS NOT NULL THEN
413 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_method_code', p_input_info.ship_method_code);
414 END IF;
415
416 IF p_input_info.fob_code IS NOT NULL THEN
417 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_fob_code', p_input_info.fob_code);
418 END IF;
419
420 IF p_input_info.freight_terms_code IS NOT NULL THEN
421 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_freight_terms_code', p_input_info.freight_terms_code);
422 END IF;
423
424 IF p_input_info.pickup_date_lo IS NOT NULL THEN
425 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pickup_date_lo', l_pickup_date_lo);
426 END IF;
427
428 IF p_input_info.pickup_date_hi IS NOT NULL THEN
429 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_pickup_date_hi', l_pickup_date_hi);
430 END IF;
431
432 IF p_input_info.dropoff_date_lo IS NOT NULL THEN
433 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_dropoff_date_lo', l_dropoff_date_lo);
434 END IF;
435
436 IF p_input_info.dropoff_date_hi IS NOT NULL THEN
437 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_dropoff_date_hi', l_dropoff_date_hi);
438 END IF;
439
440 v_ignore := DBMS_SQL.Execute(v_CursorID);
441
442 LOOP
443 IF DBMS_SQL.Fetch_Rows(v_cursorID) = 0 THEN
444 DBMS_SQL.Close_Cursor(v_cursorID);
445 EXIT;
446 ELSE
447 DBMS_SQL.Column_Value(v_CursorID, 1, v_delivery_id);
448 DBMS_SQL.Column_Value(v_CursorID, 2, v_organization_id);
449 DBMS_SQL.Column_Value(v_CursorID, 3, v_initial_pickup_location_id);
450 x_selected_del_tab(x_selected_del_tab.count+1).delivery_id := v_delivery_id;
454 END IF;
451 x_selected_del_tab(x_selected_del_tab.count).organization_id := v_organization_id;
452 x_selected_del_tab(x_selected_del_tab.count).initial_pickup_location_id := v_initial_pickup_location_id;
453
455 END LOOP;
456
457 IF l_debug_on THEN
458 WSH_DEBUG_SV.log(l_module_name, to_char(x_selected_del_tab.count)||' deliveries fetched to be processed');
459 WSH_DEBUG_SV.pop(l_module_name);
460 END IF;
461
462 EXCEPTION
463
464 WHEN others THEN
465
466 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
467 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
468 IF l_debug_on THEN
469 WSH_DEBUG_SV.pop(l_module_name);
470 END IF;
471
472 END Select_Deliveries;
473
474
475
476 -- -----------------------------------------------------------------------
477 -- Name
478 -- PROCEDURE Ship_Confirm_A_Delivery
479 --
480 -- Purpose:
481 -- This PRIVATE procedure ship confirm a delivery, it is called by
482 -- Ship_Confirm_Batch in a loop.
483 -- It is necessary to make a saperate procedure, othewise it fails to
484 -- catch the exception when a delivery cannot be locked within a loop and
485 -- it will exit all the way out of the delivery loop so no deliveries can be
486 -- ship confirmed after the exception
487 --
488 -- Input Parameters:
489 -- p_delivery_id - the delivery id to be ship cofirmed
490 -- p_sc_batch_id - Ship Confirm Batch ID, needed to stamp the delivery
491 -- p_ship_confirm_rule_rec - the ship confirm options
492 -- p_log_level - log level for printing debug messages
493 -- p_actual_departure_date - Actual Departure Date on stop
494 --
495 -- Output Parameters:
496 -- x_organization_id - the organization id of the delivery
497 -- x_return_status - Success, Warning, Error, Unexpected Error
498 -- ----------------------------------------------------------------------
499
500 PROCEDURE Ship_Confirm_A_Delivery(
501 p_delivery_id IN NUMBER,
502 p_sc_batch_id IN NUMBER,
503 p_ship_confirm_rule_rec IN G_GET_SHIP_CONFIRM_RULE%ROWTYPE,
504 p_log_level IN NUMBER,
505 p_actual_departure_date IN DATE,
506 x_return_status OUT NOCOPY VARCHAR2) IS
507
508
509 CURSOR get_delivery( c_delivery_id NUMBER ) IS
510 SELECT delivery_id,
511 status_code,
512 planned_flag,
513 initial_pickup_date,
514 organization_id,
515 ship_method_code,
516 initial_pickup_location_id
517 FROM wsh_new_deliveries
518 WHERE delivery_id = c_delivery_id and
519 status_code = 'OP' AND
520 NVL(auto_sc_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
521
522
523 -- bug 4302048: respect rule's ship method defaulting
524 CURSOR c_first_trip_ship_method (x_delivery_id IN NUMBER,
525 x_initial_loc_id IN NUMBER)IS
526 SELECT wt.ship_method_code
527 FROM wsh_delivery_legs dlg,
528 wsh_trip_stops st,
529 wsh_trips wt
530 WHERE dlg.delivery_id = x_delivery_id
531 AND st.stop_id = dlg.pick_up_stop_id
532 AND st.stop_location_id = x_initial_loc_id
533 AND st.trip_id = wt.trip_id
534 AND wt.ship_method_code IS NOT NULL
535 AND rownum = 1;
536
537 l_ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE;
538
539 l_delivery_rec get_delivery%ROWTYPE;
540 l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
541 l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
542 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
543 l_actual_dep_date DATE ;
544 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
545 l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
546 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
547 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
548 l_msg_count NUMBER;
549 l_msg_data VARCHAR2(4000);
550 l_debug_on BOOLEAN;
551 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery';
552 delivery_locked EXCEPTION;
553
554 PRAGMA EXCEPTION_INIT(delivery_locked, -00054);
555
556 BEGIN
557
558 --
559 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
560 --
561 IF l_debug_on IS NULL
562 THEN
563 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
564 END IF;
565 --
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.push(l_module_name);
568 END IF;
569
570 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
571
572 l_tmp_del_tab.delete;
573 l_tmp_del_tab(1) := p_delivery_id;
574
575 IF l_debug_on THEN
576 WSH_DEBUG_SV.log(l_module_name, 'Locking delivery '|| p_delivery_id);
577 END IF;
578
579 OPEN get_delivery(p_delivery_id);
580 FETCH get_delivery INTO l_delivery_rec;
581 IF get_delivery%FOUND THEN
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.log(l_module_name, 'Check if ship confirm is allowed for delivery ID: ' || to_char(l_tmp_del_tab(1)));
584 END IF;
585
586 UPDATE WSH_NEW_DELIVERIES SET BATCH_ID = p_sc_batch_id
587 WHERE delivery_id = p_delivery_id;
588
589 SAVEPOINT beginning_of_loop;
590
591 fnd_msg_pub.initialize; -- clear messages
592 --
593 IF p_actual_departure_date IS NOT NULL THEN
594 l_actual_dep_date := p_actual_departure_date;
595 ELSE
599 l_rec_attr_tab(1).delivery_id := l_delivery_rec.delivery_id;
596 l_actual_dep_date := SYSDATE;
597 END IF;
598 --
600 l_rec_attr_tab(1).status_code := l_delivery_rec.status_code;
601 l_rec_attr_tab(1).planned_flag := l_delivery_rec.planned_flag;
602 l_rec_attr_tab(1).organization_id := l_delivery_rec.organization_id;
603 l_rec_attr_tab(1).ship_method_code := l_delivery_rec.ship_method_code;
604
605 l_action_prms.action_code := 'CONFIRM';
606 l_action_prms.caller := 'WSH_BHPS';
607 l_action_prms.phase := NULL;
608 l_action_prms.action_flag := p_ship_confirm_rule_rec.action_flag;
609 -- 3667595, do not close stops when ship confirm deliveries
610 l_action_prms.intransit_flag := 'N';
611 l_action_prms.close_trip_flag := 'N';
612 l_action_prms.stage_del_flag := p_ship_confirm_rule_rec.stage_del_flag;
613 l_action_prms.report_set_id := p_ship_confirm_rule_rec.report_set_id;
614 l_action_prms.bill_of_lading_flag := p_ship_confirm_rule_rec.ac_bol_flag;
615 l_action_prms.mc_bill_of_lading_flag := p_ship_confirm_rule_rec.mc_bol_flag;
616 l_action_prms.defer_interface_flag := 'Y';
617
618 -- bug 4302048: respect the rule's ship method default flag.
619 IF p_ship_confirm_rule_rec.ship_method_default_flag = 'R' THEN
620 l_action_prms.ship_method_code := p_ship_confirm_rule_rec.ship_method_code;
621 ELSE
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.logmsg(l_module_name,'deriving trip/delivery SM');
624 END IF;
625 OPEN c_first_trip_ship_method(
626 l_delivery_rec.delivery_id,
627 l_delivery_rec.initial_pickup_location_id);
628 FETCH c_first_trip_ship_method INTO l_ship_method_code;
629 IF c_first_trip_ship_method%NOTFOUND THEN
630 l_ship_method_code := l_delivery_rec.ship_method_code;
631 END IF;
632 CLOSE c_first_trip_ship_method;
633 l_action_prms.ship_method_code := l_ship_method_code;
634 END IF;
635 l_action_prms.actual_dep_date := l_actual_dep_date;
636 l_action_prms.send_945_flag := p_ship_confirm_rule_rec.send_945_flag;
637
638 IF l_debug_on THEN
639 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.action_code: ',l_action_prms.action_code);
640 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.caller:',l_action_prms.caller);
641 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.phase: ',l_action_prms.phase);
642 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.action_flag: ',l_action_prms.action_flag);
643 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.intransit_flag: ',l_action_prms.intransit_flag);
644 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.close_trip_flag: ',l_action_prms.close_trip_flag);
645 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.stage_del_flag: ',l_action_prms.stage_del_flag);
646 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.report_set_id: ',l_action_prms.report_set_id);
647 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.bill_of_lading_flag: ',l_action_prms.bill_of_lading_flag);
648 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.mc_bill_of_lading_flag: ',l_action_prms.mc_bill_of_lading_flag);
649 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.defer_interface_flag: ',l_action_prms.defer_interface_flag);
650 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.ship_method_code: ',l_action_prms.ship_method_code);
651 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.actual_dep_date: ',l_action_prms.actual_dep_date);
652 WSH_DEBUG_SV.log(l_module_name,'l_action_prms.send_945_flag: ',l_action_prms.send_945_flag);
653 END IF;
654
655 WSH_DELIVERIES_GRP.Delivery_Action
656 ( p_api_version_number => 1.0,
657 p_init_msg_list => FND_API.G_FALSE,
658 p_commit => FND_API.G_FALSE,
659 p_action_prms => l_action_prms,
660 p_rec_attr_tab => l_rec_attr_tab,
661 x_delivery_out_rec => l_delivery_out_rec,
662 x_defaults_rec => l_defaults_rec,
663 x_return_status => l_return_status,
664 x_msg_count => l_msg_count,
665 x_msg_data => l_msg_data
666 );
667
668
669 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
670 l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
671 /* error or unexpected error */
672 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673 ROLLBACK TO beginning_of_loop;
674 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
675 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
676 END IF;
677
678
679 ELSE
680 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
681 WSH_UTIL_CORE.PrintMsg('Delivery '|| p_delivery_id||' not found or cannot be locked');
682 /* cannot lock the delivery */
683 END IF; -- if delivery exist
684
685 CLOSE get_delivery; /* unlock the delivery */
686 IF l_debug_on THEN
687 WSH_DEBUG_SV.POP(l_module_name);
688 END IF;
689
690 EXCEPTION
691
692
693 WHEN delivery_locked THEN
694 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
695 IF get_delivery%ISOPEN THEN
696 CLOSE get_delivery;
697 END IF;
698 IF c_first_trip_ship_method%ISOPEN THEN
699 CLOSE c_first_trip_ship_method;
700 END IF;
704 WSH_DEBUG_SV.POP(l_module_name);
701 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID '||to_char(p_delivery_id));
702 IF l_debug_on THEN
703 WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock delivery ID '||to_char(p_delivery_id));
705 END IF;
706
707 WHEN others THEN
708 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
709 IF get_delivery%ISOPEN THEN
710 CLOSE get_delivery;
711 END IF;
712 IF c_first_trip_ship_method%ISOPEN THEN
713 CLOSE c_first_trip_ship_method;
714 END IF;
715 WSH_UTIL_CORE.PrintMsg('ERROR: unhandled exception');
716 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_A_Delivery');
717 IF l_debug_on THEN
718 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: unhandled exception');
719 WSH_DEBUG_SV.POP(l_module_name);
720 END IF;
721
722 END Ship_Confirm_A_Delivery;
723
724
725 -- -----------------------------------------------------------------------
726 -- Name
727 -- PROCEDURE Close_A_Stop
728 --
729 -- Purpose:
730 -- This PRIVATE procedure closes a stop, it is called by
731 -- Ship_Confirm_Batch in a loop.
732 -- It is necessary to make a saperate procedure, othewise it fails to
733 -- catch the exception when a stop cannot be locked within a loop and
734 -- it will exit all the way out of the stop loop so no stop can be
735 -- closed after the exception
736 --
737 -- Input Parameters:
738 -- p_stop_id - the stop id to be closed
739 -- p_actual_date - the actual departure date for the stop
740 -- p_defer_interface_flag - indicate whether to defer interface
741 --
742 -- Output Parameters:
743 -- x_return_status - Success, Warning, Error, Unexpected Error
744 -- ----------------------------------------------------------------------
745
746 PROCEDURE Close_A_Stop (
747 p_stop_id IN NUMBER,
748 p_actual_date IN DATE,
749 p_defer_interface_flag IN VARCHAR2,
750 x_return_status OUT NOCOPY VARCHAR2) IS
751
752 Cursor lock_stop_trip (c_stop_id NUMBER) IS
753 SELECT a.stop_id self_stop_id, b.trip_id, c.stop_id other_stop_id
754 FROM wsh_trip_stops a, wsh_trips b, wsh_trip_stops c
755 WHERE a.trip_id = b.trip_id AND
756 a.stop_id = c_stop_id AND
757 c.trip_id = b.trip_id AND
758 a.stop_id <> C.stop_id
759 FOR UPDATE NOWAIT;
760
761 l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
762 l_self_stop_id NUMBER;
763 l_other_stop_id NUMBER;
764 l_trip_id NUMBER;
765 l_return_status VARCHAR2(1);
766 l_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
767 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
768 l_stop_out_rec WSH_TRIP_STOPS_GRP.stopActionOutRecType;
769 l_def_rec WSH_TRIP_STOPS_GRP.default_parameters_rectype;
770 l_msg_count NUMBER;
771 l_msg_data VARCHAR2(2000);
772 l_debug_on BOOLEAN;
773 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop';
774
775 stop_trip_locked EXCEPTION;
776 PRAGMA EXCEPTION_INIT(stop_trip_locked, -00054);
777
778 BEGIN
779
780 --
781 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
782 --
783 IF l_debug_on IS NULL
784 THEN
785 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
786 END IF;
787 --
788 IF l_debug_on THEN
789 WSH_DEBUG_SV.push(l_module_name);
790 WSH_DEBUG_SV.log(l_module_name, 'Locking stop '|| to_char(p_stop_id));
791 END IF;
792
793 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
794 OPEN lock_stop_trip(p_stop_id);
795 FETCH lock_stop_trip INTO l_self_stop_id, l_trip_id , l_other_stop_id;
796 IF lock_stop_trip%FOUND THEN
797
798 SAVEPOINT beginning_of_the_procedure;
799
800 IF l_debug_on THEN
801 WSH_DEBUG_SV.log(l_module_name, 'Stop locked, calling WSH_TRIP_STOPS_GRP.Stop_Action for stop '|| to_char(p_stop_id));
802 END IF;
803
804 l_action_prms.action_code := 'UPDATE-STATUS';
805 l_action_prms.stop_action := 'CLOSE';
806 l_action_prms.actual_date := p_actual_date;
807 l_action_prms.defer_interface_flag := 'Y';
808
809 l_action_prms.caller := 'WSH_BHPS';
810 l_action_prms.phase := NULL;
811
812 l_rec_attr_tab(1).stop_id := p_stop_id;
813
814
815 WSH_TRIP_STOPS_GRP.Stop_Action
816 ( p_api_version_number => 1.0,
817 p_init_msg_list => FND_API.G_FALSE,
818 p_commit => FND_API.G_FALSE,
819 p_action_prms => l_action_prms,
820 p_rec_attr_tab => l_rec_attr_tab,
821 x_stop_out_rec => l_stop_out_rec,
822 x_def_rec => l_def_rec,
823 x_return_status => l_return_status,
824 x_msg_count => l_msg_count,
825 x_msg_data => l_msg_data);
826 x_return_status := l_return_status;
827
828 CLOSE lock_stop_trip;
829 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
830 x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
831
832 ROLLBACK TO beginning_of_the_procedure;
833 END IF;
834 ELSE
835 CLOSE lock_stop_trip;
836
837 END IF;
838
839
840 IF l_debug_on THEN
841 WSH_DEBUG_SV.pop(l_module_name);
842 END IF;
843
844 EXCEPTION
845 WHEN stop_trip_locked THEN
846 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
850 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
847 CLOSE lock_stop_trip ;
848 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
849 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
851 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
852 IF l_debug_on THEN
853 WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
854 WSH_DEBUG_SV.POP(l_module_name);
855 END IF;
856
857 WHEN others THEN
858 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
859 CLOSE lock_stop_trip;
860 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
861 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
862 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_stop_id));
863 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
864 IF l_debug_on THEN
865 WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock stop and trip, stop ID '||to_char(p_stop_id));
866 WSH_DEBUG_SV.POP(l_module_name);
867 END IF;
868 END Close_A_Stop;
869
870 -- -----------------------------------------------------------------------
871 -- Name
872 -- PROCEDURE Ship_Confirm_Batch
873 --
874 -- Purpose
875 -- This procedure takes a list of delivery IDs and ship confirm these
876 -- deliveries in batch fashion. It calls confirm_delivery once for one
877 -- delivery at a time. If the ship confirm operation is successful,
878 -- it commits the change, otherwise it rollback the change and proceed
879 -- to next delivery. If a delivery cannot be ship confirmed,
880 -- it does not prevent next delivery to be ship confirmed.
881 -- This procedure is called from Automated Ship Confirm SRS or by
882 -- Pick Release SRS.
883 --
884 -- Input Parameters:
885 -- p_del_tab - list of delivery IDs to be ship confimed
886 -- p_sc_batch_id - Ship Confirm Batch ID
887 -- p_log_level - log level for printing debug messages
888 --
889 -- Output Parameters:
890 -- x_return_status - Success, Warning, Error, Unexpected Error
891 -- ----------------------------------------------------------------------
892
893 PROCEDURE Ship_Confirm_Batch(
894 p_del_tab IN WSH_BATCH_PROCESS.Del_Info_Tab,
895 p_sc_batch_id IN NUMBER,
896 p_log_level IN NUMBER,
897 x_confirmed_del_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
898 x_results_summary OUT NOCOPY WSH_BATCH_PROCESS.Results_Summary_Rec,
899 x_return_status OUT NOCOPY VARCHAR2,
900 p_commit IN VARCHAR2) IS -- BugFix #4001135
901
902 l_batch_creation_date DATE := NULL;
903
904 CURSOR get_sc_batch IS
905 SELECT ship_confirm_rule_id, creation_date, actual_departure_date
906 FROM wsh_picking_batches
907 WHERE batch_id = p_sc_batch_id FOR UPDATE NOWAIT;
908
909 -- 3667595
910 CURSOR get_pick_up_stops IS
911 SELECT DISTINCT wtp.trip_id, wst.stop_sequence_number, wst.stop_id, wst.stop_location_id
912 FROM wsh_new_deliveries wnd,
913 wsh_delivery_legs wlg,
914 wsh_trip_stops wst,
915 wsh_trips wtp
916 WHERE wnd.delivery_id = wlg.delivery_id AND
917 wlg.pick_up_stop_id = wst.stop_id AND
918 wnd.status_code = 'CO' AND
919 wnd.batch_id = p_sc_batch_id AND
920 wtp.trip_id = wst.trip_id AND
921 wst.status_code = 'OP' AND
922 NOT EXISTS (
923 select '1' from wsh_exceptions we where
924 we.delivery_id = wnd.delivery_id AND
925 we.severity = 'ERROR' AND
926 we.status = 'OPEN' AND
927 we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
928
929
930 ORDER BY wtp.trip_id, wst.stop_sequence_number, wst.stop_id ;
931
932 --Modified the following SQL as part of bug 4280371 ( 30-Jun-2005 ).
933 CURSOR get_all_stops IS
934 select wsto.trip_id, wsto.stop_sequence_number, wsto.stop_id , wsto.stop_location_id
935 from wsh_trip_stops wsto
936 , wsh_new_deliveries wnd
937 , wsh_delivery_legs wlg
938 , wsh_trip_stops wst
939 where wnd.batch_id = p_sc_batch_id
940 and wnd.status_code = 'CO'
941 and wnd.delivery_id = wlg.delivery_id
942 and wlg.pick_up_stop_id = wst.stop_id
943 and wnd.INITIAL_PICKUP_LOCATION_ID = wst.STOP_LOCATION_ID
944 and wst.status_code in ( 'OP' , 'AR' )
945 and NOT EXISTS (
946 select '1' from wsh_exceptions we
947 where we.delivery_id = wnd.delivery_id
948 AND we.severity = 'ERROR'
949 AND we.status = 'OPEN'
950 AND we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
951 and wsto.trip_id = wst.trip_id
952 ORDER BY 1, 2, 3;
953
954
955 -- added cursor to check if interface is necessary
956 -- 3667595
957 CURSOR c_batch_stop(p_batch_id NUMBER)IS
958 SELECT wts.stop_id
959 FROM wsh_trip_stops wts,
960 wsh_delivery_legs wdl,
961 wsh_new_deliveries wnd,
962 wsh_picking_batches wpb
963 WHERE p_batch_id IS NOT NULL
964 AND wnd.batch_id = p_batch_id
965 AND wdl.delivery_id = wnd.delivery_id
966 AND wts.stop_id = wdl.pick_up_stop_id
967 AND wts.stop_location_id = wnd.initial_pickup_location_id
968 AND wpb.batch_id = wnd.batch_id
969 AND wts.status_code = 'CL'
970 AND rownum = 1;
971
972
973
974 l_ship_confirm_rule_rec G_GET_SHIP_CONFIRM_RULE%ROWTYPE;
975 l_sc_confirmed_dels wsh_util_core.id_tab_type;
976 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
980 l_stop_sequence_number NUMBER := NULL;
977 l_ship_confirm_rule_id NUMBER := 0;
978 l_trip_id NUMBER := NULL;
979 l_stop_id NUMBER := NULL;
981 l_stop_location_id NUMBER := NULL;
982 l_stops_to_close WSH_UTIL_CORE.Id_Tab_Type;
983 l_stop_location_ids WSH_UTIL_CORE.Id_Tab_Type;
984 l_request_id NUMBER := 0;
985 l_num_warn NUMBER := 0;
986 l_num_error NUMBER := 0;
987 l_actual_dep_date DATE := NULL;
988 l_debug_on BOOLEAN;
989 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_Batch';
990
991 l_status_code VARCHAR2(30);
992 l_lock_error VARCHAR2(1) := 'N';
993 l_closing_stop_success NUMBER := 0;
994 l_closing_stop_warning NUMBEr := 0;
995 l_closing_stop_failure NUMBER := 0;
996 l_interface_stop_id NUMBER := 0;
997
998 wsh_missing_sc_rule EXCEPTION;
999 wsh_submit_sc_report_err EXCEPTION;
1000 delivery_locked EXCEPTION;
1001 wsh_missing_sc_batch EXCEPTION;
1002 inv_inter_req_submission EXCEPTION;
1003
1004 PRAGMA EXCEPTION_INIT(delivery_locked, -00054);
1005 --
1006 --Bugfix 4070732
1007 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1008 l_reset_flags BOOLEAN;
1009 l_return_status1 VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1010 --
1011 l_actual_departure_date DATE;
1012 --
1013 BEGIN
1014
1015 -- reset out parameters
1016 --
1017 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1018 --
1019 IF l_debug_on IS NULL
1020 THEN
1021 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1022 END IF;
1023 --
1024 x_results_summary.success := 0;
1025 x_results_summary.warning := 0;
1026 x_results_summary.failure := 0;
1027 x_results_summary.report_req_id := 0;
1028 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1029
1030 IF l_debug_on THEN
1031 WSH_DEBUG_SV.push(l_module_name);
1032 END IF;
1033
1034 l_sc_confirmed_dels.delete;
1035 l_stops_to_close.delete;
1036 l_stop_location_ids.delete;
1037
1038 WSH_UTIL_CORE.PrintDateTime;
1039
1040 -- lock ship confirm batch
1041 OPEN get_sc_batch ;
1042 FETCH get_sc_batch INTO l_ship_confirm_rule_id, l_batch_creation_date,
1043 l_actual_departure_date;
1044
1045 IF l_debug_on THEN
1046 WSH_DEBUG_SV.log(l_module_name, 'Batch Creation Date',
1047 to_char(l_batch_creation_date, 'MM-DD-YYYY HH24:MI:SS'));
1048 WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date',
1049 to_char(l_actual_departure_date, 'MM/DD/YYYY HH24:MI:SS'));
1050 END IF;
1051
1052 IF get_sc_batch%NOTFOUND THEN
1053 CLOSE get_sc_batch;
1054 raise wsh_missing_sc_batch;
1055 END IF;
1056
1057 IF l_ship_confirm_rule_id IS NULL THEN
1058 raise wsh_missing_sc_rule;
1059 END IF;
1060
1061 OPEN G_GET_SHIP_CONFIRM_RULE(l_ship_confirm_rule_id);
1062 FETCH G_GET_SHIP_CONFIRM_RULE INTO l_ship_confirm_rule_rec;
1063
1064 IF G_GET_SHIP_CONFIRM_RULE%NOTFOUND THEN
1065 CLOSE G_GET_SHIP_CONFIRM_RULE;
1066 raise wsh_missing_sc_rule;
1067 END IF;
1068
1069
1070 FOR i in 1 .. p_del_tab.count LOOP
1071 BEGIN
1072
1073 Ship_Confirm_A_Delivery(
1074 p_delivery_id => p_del_tab(i).delivery_id,
1075 p_sc_batch_id => p_sc_batch_id,
1076 p_ship_confirm_rule_rec => l_ship_confirm_rule_rec,
1077 p_log_level => p_log_level,
1078 p_actual_departure_date => l_actual_departure_date,
1079 x_return_status => l_return_status);
1080
1081 EXCEPTION
1082
1083 WHEN delivery_locked THEN
1084 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1085 WSH_UTIL_CORE.PrintMsg('ERROR2: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1086 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1087 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1088 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1089 log_batch_messages(p_sc_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1090 IF l_debug_on THEN
1091 WSH_DEBUG_SV.logmsg('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery', 'ERROR: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1092 WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery');
1093 END IF;
1094
1095 WHEN OTHERS THEN
1096 /* this will catch the exception when failing to obtain the lock on the delivery */
1097 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1098 WSH_UTIL_CORE.PrintMsg('ERROR3: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1099 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1100 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1101 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1102 IF l_debug_on THEN
1103 WSH_DEBUG_SV.logmsg('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery', 'ERROR: Failed to lock delivery ID '||to_char(p_del_tab(i).delivery_id));
1104 WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Ship_Confirm_A_Delivery');
1105 END IF;
1106 END;
1107
1108 --
1109 --bug 4070732
1110 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1111 AND p_commit = FND_API.G_TRUE ) THEN
1112 --{
1113 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1117
1114 --{
1115
1116 l_reset_flags := FALSE;
1118 IF l_debug_on THEN
1119 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1120 END IF;
1121
1122 BEGIN
1123
1124 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1125 x_return_status => l_return_status1);
1126 IF l_debug_on THEN
1127 WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1128 END IF;
1129
1130 IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1131 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1132 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1133 l_return_status := l_return_status1;
1134 END IF;
1135 EXCEPTION
1136 WHEN others THEN
1137 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1138 END;
1139
1140 --}
1141 END IF;
1142 --}
1143 END IF;
1144 --bug 4070732
1145 --
1146 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1147 x_results_summary.success := x_results_summary.success + 1;
1148 l_sc_confirmed_dels(l_sc_confirmed_dels.count+1):= p_del_tab(i).delivery_id;
1149 IF l_debug_on THEN
1150 select status_code into l_status_code from wsh_new_deliveries where delivery_id = p_del_tab(i).delivery_id;
1151 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID ' || to_char(p_del_tab(i).delivery_id)||' is ship confirmed successfully with status '|| l_status_code);
1152 END IF;
1153
1154
1155 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1156 x_results_summary.warning := x_results_summary.warning + 1;
1157 l_sc_confirmed_dels(l_sc_confirmed_dels.count+1):= p_del_tab(i).delivery_id;
1158 log_batch_messages(p_sc_batch_id, p_del_tab(i).delivery_id, NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1159 IF l_debug_on THEN
1160 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID '|| to_char(p_del_tab(i).delivery_id)||' is ship confirmed with warnings');
1161 END IF;
1162
1163 ELSE
1164 x_results_summary.failure := x_results_summary.failure + 1;
1165
1166 log_batch_messages(p_sc_batch_id, p_del_tab(i).delivery_id,NULL, p_del_tab(i).initial_pickup_location_id, NULL);
1167
1168 IF l_debug_on THEN
1169 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID '|| to_char(p_del_tab(i).delivery_id)||' cannot be ship confirmed');
1170 END IF;
1171
1172
1173 END IF;
1174
1175 -- Following if condition is added for Bugfix #4001135
1176 -- We will commit the data only when this api is called from Concurrent Request.
1177 IF ( P_COMMIT = FND_API.G_TRUE ) THEN
1178 COMMIT;
1179 END IF;
1180
1181 END LOOP;
1182
1183 x_confirmed_del_tab := l_sc_confirmed_dels;
1184
1185
1186 -- Close the manually created Trip-Stops
1187 -- 3667595
1188 IF l_ship_confirm_rule_rec.ac_close_trip_flag = 'Y' THEN
1189 OPEN get_all_stops;
1190
1191 LOOP
1192
1193 FETCH get_all_stops into l_trip_id, l_stop_sequence_number, l_stop_id, l_stop_location_id ;
1194 EXIT WHEN get_all_stops%NOTFOUND;
1195 l_stops_to_close(l_stops_to_close.count+1) := l_stop_id;
1196 l_stop_location_ids(l_stop_location_ids.count+1) := l_stop_location_id;
1197
1198 END LOOP;
1199 CLOSE get_all_stops;
1200
1201 -- 3667595
1202 ELSIF l_ship_confirm_rule_rec.ac_intransit_flag = 'Y' THEN
1203 OPEN get_pick_up_stops ;
1204 LOOP
1205
1206 FETCH get_pick_up_stops into l_trip_id, l_stop_sequence_number, l_stop_id, l_stop_location_id;
1207 EXIT WHEN get_pick_up_stops%NOTFOUND;
1208 l_stops_to_close(l_stops_to_close.count+1) := l_stop_id;
1209 l_stop_location_ids(l_stop_location_ids.count+1) := l_stop_location_id;
1210
1211 END LOOP;
1212 CLOSE get_pick_up_stops;
1213
1214 END IF;
1215
1216 IF l_stops_to_close.count > 0 THEN
1217 IF l_debug_on THEN
1218 WSH_DEBUG_SV.logmsg(l_module_name, 'Closing manually created stops');
1219 END IF;
1220 FOR i in 1..l_stops_to_close.count LOOP
1221 BEGIN
1222 l_lock_error := 'N';
1223
1224 Close_A_Stop(
1225 p_stop_id => l_stops_to_close(i),
1226 p_actual_date => NVL(l_actual_departure_date,SYSDATE),
1227 p_defer_interface_flag => 'Y',
1228 x_return_status => l_return_status);
1229
1230 EXCEPTION
1231 WHEN others THEN
1232 /* this will catch the exeption when stop and trip cannot be locked */
1233 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1234 l_lock_error := 'Y';
1235
1236 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1237 IF l_debug_on THEN
1238 WSH_DEBUG_SV.logmsg('wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop', 'ERROR: Failed to lock stop and trip, stop ID '||to_char(l_stops_to_close(i)));
1239 WSH_DEBUG_SV.POP('wsh.plsql.' || G_PKG_NAME || '.' || 'Close_A_Stop');
1240 END IF;
1241 END;
1242
1243 --
1244 --bug 4070732
1245 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1246 AND p_commit = FND_API.G_TRUE ) THEN
1247 --{
1248 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1249 --{
1250
1254 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1251 l_reset_flags := FALSE;
1252
1253 IF l_debug_on THEN
1255 END IF;
1256
1257 BEGIN
1258
1259 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1260 x_return_status => l_return_status1);
1261 IF l_debug_on THEN
1262 WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1263 END IF;
1264
1265 IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1266 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1267 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1268 l_return_status := l_return_status1;
1269 END IF;
1270 EXCEPTION
1271 WHEN others THEN
1272 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1273 END;
1274
1275 --}
1276 END IF;
1277 --}
1278 END IF;
1279 --bug 4070732
1280
1281 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1282
1283 IF l_debug_on THEN
1284 WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed stop '|| l_stops_to_close(i));
1285 END IF;
1286 l_closing_stop_success := l_closing_stop_success + 1;
1287 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1288 IF l_debug_on THEN
1289 WSH_DEBUG_SV.logmsg(l_module_name,'Trip stop '|| l_stops_to_close(i) ||' is closed with warnings');
1290 END IF;
1291 l_closing_stop_warning := l_closing_stop_warning + 1;
1292 log_batch_messages(p_sc_batch_id, NULL , l_stops_to_close(i) , l_stop_location_ids(i), NULL);
1293 ELSE
1294 IF l_debug_on THEN
1295 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to close stop '|| l_stops_to_close(i));
1296 END IF;
1297
1298 IF l_lock_error = 'Y' THEN
1299 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
1300 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(l_stops_to_close(i)));
1301 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1302 END IF;
1303 l_closing_stop_failure := l_closing_stop_failure + 1;
1304 log_batch_messages(p_sc_batch_id, NULL , l_stops_to_close(i) , l_stop_location_ids(i), NULL);
1305 END IF;
1306
1307 -- Following if condition is added for Bugfix #4001135
1308 -- We will commit the data only when this api is called from Concurrent Request.
1309 IF ( P_COMMIT = FND_API.G_TRUE ) THEN
1310 COMMIT;
1311 END IF;
1312
1313 END LOOP;
1314
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.logmsg(l_module_name,'Successfully closed '|| l_closing_stop_success ||' stops' );
1317 WSH_DEBUG_SV.logmsg(l_module_name,'Closed '|| l_closing_stop_warning ||' stops with warnings' );
1318 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to close '|| l_closing_stop_failure ||' stops' );
1319 END IF;
1320
1321 ELSE
1322 IF l_debug_on THEN
1323 WSH_DEBUG_SV.logmsg(l_module_name, 'No manually created stops to close');
1324 END IF;
1325 END IF;
1326
1327 IF l_ship_confirm_rule_rec.ac_defer_interface_flag = 'N' THEN
1328 l_interface_stop_id := 0;
1329 -- added cursor to check if interface is necessary
1330 open c_batch_stop ( p_sc_batch_id);
1331 fetch c_batch_stop into l_interface_stop_id;
1332 close c_batch_stop;
1333 IF l_interface_stop_id <> 0 THEN
1334
1335 l_request_id := FND_REQUEST.submit_Request('WSH', 'WSHINTERFACE', '', '', FALSE,
1336 'ALL', '', '', 0, p_sc_batch_id);
1337 IF (l_request_id = 0) THEN
1338 raise inv_inter_req_submission;
1339 ELSE
1340 WSH_UTIL_CORE.PrintMsg('Interface request submitted for closed stops, request ID: '
1341 || to_char(l_request_id) );
1342 END IF;
1343
1344 END IF;
1345 END IF;
1346
1347
1348
1349 CLOSE G_GET_SHIP_CONFIRM_RULE;
1350 CLOSE get_sc_batch;
1351
1352
1353 -- submit Auto Ship Confirm Deliveries Reprot here
1354 x_results_summary.report_req_id := fnd_request.submit_request(
1355 'WSH',
1356 'WSHRDASC','Auto Ship Confirm Report',NULL,FALSE
1357 ,p_sc_batch_id,'','','','','','','','',''
1358 ,'','','','','','','','SC','',''
1359 ,'','','','','','','','','',''
1360 ,'','','','','','','','','',''
1361 ,'','','','','','','','','',''
1362 ,'','','','','','','','','',''
1363 ,'','','','','','','','','',''
1364 ,'','','','','','','','','',''
1365 ,'','','','','','','','','',''
1366 ,'','','','','','','','','','' );
1367
1368 IF x_results_summary.report_req_id =0 THEN
1369 raise WSH_SUBMIT_SC_REPORT_ERR;
1370 END IF;
1371
1372 IF l_debug_on THEN
1373 WSH_DEBUG_SV.pop(l_module_name);
1374 END IF;
1375
1376 EXCEPTION
1377
1378 WHEN wsh_missing_sc_batch THEN
1379 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1380 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to find the ship confirm batch ');
1381
1382 IF l_debug_on THEN
1383 WSH_DEBUG_SV.pop(l_module_name);
1384 END IF;
1385
1386 WHEN wsh_missing_sc_rule THEN
1387 IF get_sc_batch%ISOPEN THEN
1388 CLOSE get_sc_batch;
1389 END IF;
1390 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1394 END IF;
1391 WSH_UTIL_CORE.PrintMsg('ERROR: Ship Confirm Rule is not found or has expired');
1392 IF l_debug_on THEN
1393 WSH_DEBUG_SV.pop(l_module_name);
1395
1396 WHEN inv_inter_req_submission THEN
1397 IF get_sc_batch%ISOPEN THEN
1398 CLOSE get_sc_batch;
1399 END IF;
1400 IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1401 CLOSE G_GET_SHIP_CONFIRM_RULE;
1402 END IF;
1403 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1404 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Interface concurrent request ');
1405 IF l_debug_on THEN
1406 WSH_DEBUG_SV.pop(l_module_name);
1407 END IF;
1408
1409 WHEN WSH_SUBMIT_SC_REPORT_ERR THEN
1410 IF get_sc_batch%ISOPEN THEN
1411 CLOSE get_sc_batch;
1412 END IF;
1413 IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1414 CLOSE G_GET_SHIP_CONFIRM_RULE;
1415 END IF;
1416 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1417 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Auto Ship Confirm Report ');
1418 IF l_debug_on THEN
1419 WSH_DEBUG_SV.pop(l_module_name);
1420 END IF;
1421
1422 WHEN delivery_locked THEN
1423 IF get_sc_batch%ISOPEN THEN
1424 CLOSE get_sc_batch;
1425 END IF;
1426 IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1427 CLOSE G_GET_SHIP_CONFIRM_RULE;
1428 END IF;
1429 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1430 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery ID ');
1431 IF l_debug_on THEN
1432 WSH_DEBUG_SV.pop(l_module_name);
1433 END IF;
1434
1435 WHEN others THEN
1436 IF get_sc_batch%ISOPEN THEN
1437 CLOSE get_sc_batch;
1438 END IF;
1439 IF G_GET_SHIP_CONFIRM_RULE%ISOPEN THEN
1440 CLOSE G_GET_SHIP_CONFIRM_RULE;
1441 END IF;
1442 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Ship_Confirm_Batch');
1443 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1444 IF l_debug_on THEN
1445 WSH_DEBUG_SV.pop(l_module_name);
1446 END IF;
1447
1448 END Ship_Confirm_Batch;
1449
1450 -- -----------------------------------------------------------------------
1451 -- Name
1452 -- PROCEDURE Auto_Pack_A_Delivery
1453 --
1454 -- Purpose:
1455 -- This PRIVATE procedure auto pack a delivery, it is called by
1456 -- uto_Pack_Deliveries_Batch in a loop.
1457 -- It is necessary to make a saperate procedure, othewise it fails to
1458 -- catch the exception when a delivery cannot be locked within a loop and
1459 -- it will exit all the way out of the delivery loop so no deliveries can be
1460 -- ship confirmed after the exception occurs.
1461 --
1462 -- Input Parameters:
1463 -- p_delivery_id - the delivery id to be ship cofirmed
1464 -- p_sc_batch_id - Ship Confirm Batch ID, needed to stamp the delivery
1465 -- p_ship_confirm_rule_rec - the ship confirm options
1466 -- p_log_level - log level for printing debug messages
1467 --
1468 -- Output Parameters:
1469 -- x_organization_id - the organization id of the delivery
1470 -- x_return_status - Success, Warning, Error, Unexpected Error
1471 -- ----------------------------------------------------------------------
1472
1473
1474 PROCEDURE Auto_Pack_A_Delivery(
1475 p_delivery_id IN NUMBER,
1476 p_ap_batch_id IN NUMBER,
1477 p_auto_pack_level IN NUMBER,
1478 p_log_level IN NUMBER,
1479 x_return_status OUT NOCOPY VARCHAR2) IS
1480
1481 CURSOR get_delivery( c_delivery_id NUMBER ) IS
1482 SELECT delivery_id,
1483 status_code,
1484 planned_flag,
1485 initial_pickup_date,
1486 organization_id,
1487 ship_method_code
1488 FROM wsh_new_deliveries
1489 WHERE delivery_id = c_delivery_id and
1490 status_code = 'OP' AND
1491 NVL(auto_ap_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
1492
1493
1494 l_delivery_rec get_delivery%ROWTYPE;
1495 l_tmp_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1496 l_action VARCHAR2(30) := NULL;
1497 l_pack_cont_flag VARCHAR2(1) := NULL;
1498 l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
1499 l_cont_instance_tab WSH_UTIL_CORE.Id_Tab_Type;
1500 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1501 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1502 l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1503 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1504 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
1505 l_msg_count NUMBER;
1506 l_msg_data VARCHAR2(4000);
1507 l_debug_on BOOLEAN;
1508 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery';
1509 WSH_INVALID_AUTO_PACK_LEVEL EXCEPTION;
1510 delivery_locked EXCEPTION;
1511
1515 BEGIN
1512 PRAGMA EXCEPTION_INIT(delivery_locked, -00054);
1513
1514
1516
1517 --
1518 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1519 --
1520 IF l_debug_on IS NULL
1521 THEN
1522 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1523 END IF;
1524 --
1525 IF l_debug_on THEN
1526 WSH_DEBUG_SV.push(l_module_name);
1527 END IF;
1528
1529 l_tmp_del_tab.delete;
1530 l_tmp_del_tab(1) := p_delivery_id;
1531
1532 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1533
1534 IF p_auto_pack_level = 1 THEN
1535 l_action := 'AUTO-PACK';
1536 l_pack_cont_flag := 'N';
1537 ELSIF p_auto_pack_level = 2 THEN
1538 l_action := 'AUTO-PACK-MASTER';
1539 l_pack_cont_flag := 'Y';
1540 ELSE
1541 RAISE WSH_INVALID_AUTO_PACK_LEVEL;
1542 END IF;
1543
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.logmsg(l_module_name, 'Locking delivery '|| p_delivery_id);
1546 END IF;
1547
1548
1549 OPEN get_delivery(p_delivery_id);
1550 FETCH get_delivery INTO l_delivery_rec;
1551 IF get_delivery%FOUND THEN
1552
1553
1554 SAVEPOINT beginning_of_loop;
1555
1556 UPDATE WSH_NEW_DELIVERIES SET AP_BATCH_ID = p_ap_batch_id
1557 WHERE delivery_id = p_delivery_id;
1558
1559 fnd_msg_pub.initialize; -- clear messages
1560
1561 /* auto pack a delivery */
1562 l_rec_attr_tab(1).delivery_id := l_delivery_rec.delivery_id;
1563 l_rec_attr_tab(1).status_code := l_delivery_rec.status_code;
1564 l_rec_attr_tab(1).planned_flag := l_delivery_rec.planned_flag;
1565 l_rec_attr_tab(1).organization_id := l_delivery_rec.organization_id;
1566 l_rec_attr_tab(1).ship_method_code := l_delivery_rec.ship_method_code;
1567
1568 l_action_prms.action_code := l_action;
1569 l_action_prms.caller := 'WSH_BHPS';
1570 l_action_prms.phase := NULL;
1571
1572
1573
1574 WSH_DELIVERIES_GRP.Delivery_Action
1575 ( p_api_version_number => 1.0,
1576 p_init_msg_list => FND_API.G_FALSE,
1577 p_commit => FND_API.G_FALSE,
1578 p_action_prms => l_action_prms,
1579 p_rec_attr_tab => l_rec_attr_tab,
1580 x_delivery_out_rec => l_delivery_out_rec,
1581 x_defaults_rec => l_defaults_rec,
1582 x_return_status => l_return_status,
1583 x_msg_count => l_msg_count,
1584 x_msg_data => l_msg_data
1585 );
1586
1587 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
1588 l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1589 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1590 ROLLBACK TO beginning_of_loop;
1591 ELSE
1592 x_return_status := l_return_status;
1593 END IF;
1594
1595 ELSE /* cannot lock the delivery */
1596 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1597 END IF; -- if delivery exist
1598
1599 CLOSE get_delivery;
1600 IF l_debug_on THEN
1601 WSH_DEBUG_SV.pop(l_module_name);
1602 END IF;
1603
1604 EXCEPTION
1605
1606 WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1607 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1608 WSH_UTIL_CORE.PrintMsg('ERROR: invalid auto pack level');
1609 IF l_debug_on THEN
1610 WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Auot Pack Level');
1611 WSH_DEBUG_SV.pop(l_module_name);
1612 END IF;
1613
1614 WHEN delivery_locked THEN
1615 CLOSE get_delivery;
1616 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1617 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1618 IF l_debug_on THEN
1619 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1620 WSH_DEBUG_SV.pop(l_module_name);
1621 END IF;
1622
1623
1624
1625 WHEN OTHERS THEN
1626 CLOSE get_delivery;
1627 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1628 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_A_Delivery');
1629 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1630 IF l_debug_on THEN
1631 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: Failed to lock delivery '|| to_char(p_delivery_id));
1632 WSH_DEBUG_SV.pop(l_module_name);
1633 END IF;
1634
1635 END Auto_Pack_A_Delivery;
1636
1637 -- -----------------------------------------------------------------------
1638 -- Name
1639 -- PROCEDURE Auto_Pack_Deliveries_Batch
1640 --
1641 -- Purpose
1642 -- This procedure takes a list of delivery IDs and auto pack these
1643 -- deliveries in batch fashion. It calls auto_pack_deliveries once for one
1644 -- delivery at a time. If the auto pack operation is successful,
1645 -- it commits the change, otherwise it rollback the change and proceed
1646 -- to next delivery. If a delivery cannot be auto packed,
1647 -- it does not prevent next delivery to be auto packed.
1648 -- This procedure is called from Auto Pack Deliveries SRS or by
1649 -- Pick Release SRS.
1650 --
1651 -- Input Parameters:
1652 -- p_del_tab - list of delivery IDs to be ship confimed
1656 -- Output Parameters:
1653 -- p_sc_batch_id - Ship Confirm Batch ID
1654 -- p_log_level - log level for printing debug messages
1655 --
1657 -- x_return_status - Success, Warning, Error, Unexpected Error
1658 -- ----------------------------------------------------------------------
1659
1660 PROCEDURE Auto_Pack_Deliveries_Batch(
1661 p_del_tab IN WSH_BATCH_PROCESS.Del_Info_Tab,
1662 p_ap_batch_id IN NUMBER,
1663 p_auto_pack_level IN NUMBER,
1664 p_log_level IN NUMBER,
1665 x_packed_del_tab OUT NOCOPY WSH_BATCH_PROCESS.Del_Info_Tab,
1666 x_results_summary OUT NOCOPY WSH_BATCH_PROCESS.Results_Summary_Rec,
1667 x_return_status OUT NOCOPY VARCHAR2,
1668 P_COMMIT IN VARCHAR2) IS -- BugFix #4001135
1669
1670 l_ap_packed_dels wsh_util_core.id_tab_type;
1671 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1672 l_debug_on BOOLEAN;
1673 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_Batch';
1674 WSH_SUBMIT_AP_REPORT_ERR EXCEPTION;
1675 delivery_locked EXCEPTION;
1676 WSH_INVALID_AUTO_PACK_LEVEL EXCEPTION;
1677 PRAGMA EXCEPTION_INIT(delivery_locked, -54);
1678
1679 --Bugfix 4070732
1683
1680 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1681 l_reset_flags BOOLEAN;
1682 l_return_status1 VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1684
1685 BEGIN
1686
1687
1688 --
1689 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1690 --
1691 IF l_debug_on IS NULL
1692 THEN
1693 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1694 END IF;
1695 --
1696 x_results_summary.success := 0;
1700 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1697 x_results_summary.warning := 0;
1698 x_results_summary.failure := 0;
1699 x_results_summary.report_req_id := 0;
1701
1702 x_packed_del_tab.delete;
1703
1704 IF l_debug_on THEN
1705 WSH_DEBUG_SV.push(l_module_name);
1706 WSH_DEBUG_SV.logmsg(l_module_name,'AP level'||p_auto_pack_level);
1707 END IF;
1708
1709 WSH_UTIL_CORE.PrintDateTime;
1710
1711 IF p_auto_pack_level <> 1 AND p_auto_pack_level <> 2 THEN
1712 RAISE WSH_INVALID_AUTO_PACK_LEVEL;
1713 END IF;
1714
1715 FOR i in 1 .. p_del_tab.count LOOP
1716
1717 IF l_debug_on THEN
1718 WSH_DEBUG_SV.logmsg(l_module_name,'Auto pack delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1719 END IF;
1720
1721 BEGIN
1722 Auto_Pack_A_Delivery(
1723 p_delivery_id => p_del_tab(i).delivery_id,
1724 p_ap_batch_id => p_ap_batch_id,
1725 p_auto_pack_level => p_auto_pack_level,
1726 p_log_level => p_log_level,
1727 x_return_status => l_return_status);
1728
1729
1730 EXCEPTION
1731
1732 WHEN delivery_locked THEN
1733 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1734 WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1735 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1736 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1737 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1738 log_batch_messages(p_ap_batch_id, NULL ,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1739
1740 IF l_debug_on THEN
1741 WSH_DEBUG_SV.logmsg(l_module_name, 'Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1742 WSH_DEBUG_SV.pop('wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery');
1743 END IF;
1744
1745 WHEN OTHERS THEN
1746 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1747 WSH_UTIL_CORE.PrintMsg('ERROR => Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1748 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
1749 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(p_del_tab(i).delivery_id));
1750 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1751
1752 IF l_debug_on THEN
1753 WSH_DEBUG_SV.logmsg(l_module_name, 'Failed to lock delivery '|| to_char(p_del_tab(i).delivery_id));
1754 WSH_DEBUG_SV.pop('wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_A_Delivery');
1755 END IF;
1756
1757
1758 END;
1759
1760 --
1761 --bug 4070732
1762 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
1763 AND p_commit = FND_API.G_TRUE ) THEN
1764 --{
1765 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1766 --{
1767
1768 l_reset_flags := FALSE;
1769
1770 IF l_debug_on THEN
1771 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1772 END IF;
1773
1774 BEGIN
1775
1776 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
1777 x_return_status => l_return_status1);
1778 IF l_debug_on THEN
1779 WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
1780 END IF;
1781
1782 IF l_return_status1 IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1783 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1784 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1785 l_return_status := l_return_status1;
1786 END IF;
1787 EXCEPTION
1788 WHEN others THEN
1789 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1790 END;
1791
1792 --}
1793 END IF;
1794 --}
1795 END IF;
1796 --bug 4070732
1797 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1798 x_results_summary.success := x_results_summary.success + 1;
1799 x_packed_del_tab(x_packed_del_tab.count+1).delivery_id := p_del_tab(i).delivery_id;
1800 x_packed_del_tab(x_packed_del_tab.count).organization_id := p_del_tab(i).organization_id;
1801 x_packed_del_tab(x_packed_del_tab.count).initial_pickup_location_id := p_del_tab(i).initial_pickup_location_id;
1802
1803 IF l_debug_on THEN
1804 WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack succeed for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1805 END IF;
1806
1807 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1808 x_results_summary.warning := x_results_summary.warning + 1;
1809 x_packed_del_tab(x_packed_del_tab.count+1).delivery_id := p_del_tab(i).delivery_id;
1810 x_packed_del_tab(x_packed_del_tab.count).organization_id := p_del_tab(i).organization_id;
1811 x_packed_del_tab(x_packed_del_tab.count).initial_pickup_location_id := p_del_tab(i).initial_pickup_location_id;
1812
1813 log_batch_messages(p_ap_batch_id, p_del_tab(i).delivery_id, NULL, p_del_tab(i).initial_pickup_location_id, 'W');
1814 IF l_debug_on THEN
1815 WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack completed with warnings for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1816 END IF;
1817
1818 ELSE -- error or unexpected error
1819
1820 x_results_summary.failure := x_results_summary.failure + 1;
1821
1822
1826
1823 IF l_debug_on THEN
1824 WSH_DEBUG_SV.logmsg(l_module_name,'Auto Pack failed for delivery ID: ' || to_char(p_del_tab(i).delivery_id));
1825 END IF;
1827 log_batch_messages(p_ap_batch_id, p_del_tab(i).delivery_id,NULL, p_del_tab(i).initial_pickup_location_id, 'E');
1828
1829 END IF;
1830
1831 -- Following if condition is added for Bugfix #4001135
1832 -- We will commit the data only when this api is called from Concurrent Request.
1836
1833 IF ( P_COMMIT = FND_API.G_TRUE ) THEN
1834 COMMIT;
1835 END IF;
1837 END LOOP;
1838
1839 IF l_debug_on THEN
1840 WSH_DEBUG_SV.logmsg(l_module_name,'All the deliveries have been packed');
1841 END IF;
1842
1843 -- submit Auto Pack Deliveries Reprot here
1844 x_results_summary.report_req_id := fnd_request.submit_request(
1845 'WSH',
1846 'WSHRDAPK','Auto Pack Report',NULL,FALSE
1847 ,p_ap_batch_id,'','','','','','','','',''
1848 ,'','','','','','','AP','','',''
1849 ,'','','','','','','','','',''
1850 ,'','','','','','','','','',''
1851 ,'','','','','','','','','',''
1852 ,'','','','','','','','','',''
1853 ,'','','','','','','','','',''
1854 ,'','','','','','','','','',''
1855 ,'','','','','','','','','',''
1856 ,'','','','','','','','','','' );
1857 IF x_results_summary.report_req_id =0 THEN
1858 raise WSH_SUBMIT_AP_REPORT_ERR;
1859 END IF;
1860
1861 --
1862 IF l_debug_on THEN
1863 WSH_DEBUG_SV.pop(l_module_name);
1864 END IF;
1865
1866 EXCEPTION
1867
1868 WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
1869 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1870 FND_MESSAGE.SET_NAME('WSH' , 'WSH_INVALID_AUTO_PACK_LEVEL');
1871 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1872 IF l_debug_on THEN
1873 WSH_DEBUG_SV.pop(l_module_name);
1874 END IF;
1875
1876 WHEN WSH_SUBMIT_AP_REPORT_ERR THEN
1877 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1878 FND_MESSAGE.SET_NAME('WSH' , 'WSH_SUBMIT_AP_REPORT_ERR');
1879 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1880 IF l_debug_on THEN
1881 WSH_DEBUG_SV.pop(l_module_name);
1882 END IF;
1883
1884 WHEN others THEN
1885 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Auto_Pack_Deliveries_Batch');
1886 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1887 IF l_debug_on THEN
1888 WSH_DEBUG_SV.pop(l_module_name);
1889 END IF;
1890
1891 END Auto_Pack_Deliveries_Batch;
1892
1893 -- -----------------------------------------------------------------------
1894 -- Name
1895 -- PROCEDURE Confirm_Delivery_SRS
1896 --
1897 -- Purpose
1898 -- This is ship confirm concurrent program launched using standard report submisstion.
1899 -- It takes users defined criteria and construct a dynamic select statement to
1900 -- get a list of delivery IDs to be ship confirmed then call Ship_Confirm_Batch.
1901 -- The actual ship confirm operation is done in Ship_Cofirm_Batch procedure
1902 --
1903 -- Input Parameters:
1904 -- p_ship_confirm_rule_id - Auto Ship Confirm Rule ID
1905 -- p_sc_batch_prefix - Ship Confirm Batch ID
1906 -- p_client_id - Client ID for LSP -- Modified R12.1.1 LSP PROJECT (rminocha)
1907 -- p_organization_id - Organization ID
1908 -- p_pr_batch_id - Pick Release Batch ID
1909 -- p_ap_batch_id - Auto Pack Batch ID
1910 -- p_delivery_name_lo - Delivery Name (Low)
1911 -- p_delivery_name_hi - Delivery Name (High)
1912 -- p_bol_number_lo - BOL Number (Low)
1913 -- p_bol_number_hi - BOL Number (High)
1914 -- p_planned_flag - Planned Flag
1915 -- p_ship_from_loc_id - Ship from Location ID
1916 -- p_ship_to_loc_id - Ship to Location ID
1917 -- p_intmed_ship_to_loc_id - Intermediate Ship to Location ID
1918 -- p_pooled_ship_to_loc_id - Pooled Ship to Location ID
1919 -- p_customer_id - Customer ID
1920 -- p_ship_method_code - Ship Method Code
1921 -- p_fob_code - FOB Code
1922 -- p_freight_terms_code - Freight Terms Code
1923 -- p_pickup_date_lo - Pick up Date (Low)
1924 -- p_pickup_date_hi - Pick up Date (High)
1925 -- p_dropoff_date_lo - Drop off Date (Low)
1926 -- p_dropoff_date_hi - Drop off Date (High)
1927 -- p_log_level - Log Level
1928 -- p_actual_departure_date - Actual Departure Date for stop
1929 --
1930 -- Output Parameters:
1931 -- errbug - standard output parameter for a concurrent program
1932 -- retcode - standard output parameter for a concurrent program
1933 -- ----------------------------------------------------------------------
1934
1935
1936 procedure Confirm_Delivery_SRS(
1937 errbuf OUT NOCOPY VARCHAR2,
1938 retcode OUT NOCOPY VARCHAR2,
1939 p_ship_confirm_rule_id IN NUMBER,
1940 p_actual_departure_date IN VARCHAR2,
1941 p_sc_batch_prefix IN VARCHAR2,
1942 p_deploy_mode IN VARCHAR2, -- Modified R12.1.1 LSP PROJECT
1943 p_client_id IN NUMBER, -- Modified R12.1.1 LSP PROJECT (rminocha)
1944 p_organization_id IN NUMBER,
1945 p_pr_batch_id IN NUMBER,
1946 p_ap_batch_id IN NUMBER,
1947 p_delivery_name_lo IN VARCHAR2,
1948 p_delivery_name_hi IN VARCHAR2,
1949 p_bol_number_lo IN VARCHAR2,
1950 p_bol_number_hi IN VARCHAR2,
1951 p_planned_flag IN VARCHAR2,
1952 p_ship_from_loc_id IN NUMBER,
1953 p_ship_to_loc_id IN NUMBER,
1954 p_intmed_ship_to_loc_id IN NUMBER,
1955 p_pooled_ship_to_loc_id IN NUMBER,
1956 p_customer_id IN VARCHAR2, --RTV changes
1957 p_ship_method_code IN VARCHAR2,
1958 p_fob_code IN VARCHAR2,
1959 p_freight_terms_code IN VARCHAR2,
1960 p_pickup_date_lo IN VARCHAR2,
1961 p_pickup_date_hi IN VARCHAR2,
1962 p_dropoff_date_lo IN VARCHAR2,
1963 p_dropoff_date_hi IN VARCHAR2,
1964 p_log_level IN NUMBER) IS
1965
1966 l_completion_status VARCHAR2(30);
1967 l_error_code NUMBER;
1968 l_error_text VARCHAR2(2000);
1969
1970 l_user_id NUMBER := 0;
1971 l_login_id NUMBER := 0;
1972 l_return_status VARCHAR2(30) := NULL;
1973 l_rowid VARCHAR2(30);
1974 l_log_level NUMBER := 0;
1975 l_temp BOOLEAN;
1976 l_batch_rec WSH_PICKING_BATCHES%ROWTYPE;
1977 l_debug_on BOOLEAN;
1978 l_confirmed_del_tab WSH_UTIL_CORE.Id_Tab_Type;
1979 l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
1980 l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
1981 l_results_summary WSH_BATCH_PROCESS.Results_Summary_Rec;
1982 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Confirm_Delivery_SRS';
1983 WSH_MISSING_SC_RULE EXCEPTION;
1984 WSH_MISSING_SC_BATCH_PREFIX EXCEPTION;
1985 WSH_SC_BATCH_ERR EXCEPTION;
1986 WSH_SELECT_ERR EXCEPTION;
1987 WSH_NO_FUTURE_SHIPDATE EXCEPTION;
1988 l_batch_name VARCHAR2(30);
1989
1990 -- deliveryMerge
1994
1991 Adjust_Planned_Flag_Err EXCEPTION;
1992 l_warning_num NUMBER := 0;
1993 l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
1995 --
1996 -- Bug 5097710
1997 --
1998 l_summary VARCHAR2(32000);
1999 l_detail VARCHAR2(32000);
2000 l_count number;
2001 --
2002 BEGIN
2003
2004 l_delivery_ids.delete;
2005 IF p_log_level IS NULL THEN
2006 l_log_level := 0;
2007 ELSE
2008 l_log_level := p_log_level;
2009 END IF;
2010 WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2011
2012 --
2013 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2014 --
2015 IF l_debug_on IS NULL
2016 THEN
2017 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2018 END IF;
2019 --
2020 IF l_debug_on THEN
2021 WSH_DEBUG_SV.push(l_module_name);
2022 END IF;
2023
2024 l_completion_status := 'NORMAL';
2025
2026 -- Fetch user and login information
2027 l_user_id := FND_GLOBAL.USER_ID;
2028 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2029
2030 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2031
2032 IF p_ship_confirm_rule_id IS NULL THEN
2033 raise WSH_MISSING_SC_RULE;
2034 END IF;
2035
2036 IF p_sc_batch_prefix IS NULL THEN
2037 raise WSH_MISSING_SC_BATCH_PREFIX;
2038 END IF;
2039
2040 WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2041 WSH_UTIL_CORE.PrintMsg(' Auto Ship Confirm Rule ID: '|| p_ship_confirm_rule_id);
2042 WSH_UTIL_CORE.PrintMsg(' Ship Confirm Batch Prefix: ' || p_sc_batch_prefix );
2043 WSH_UTIL_CORE.PrintMsg(' Actual Departure Date: ' || p_actual_departure_date);
2044
2045
2046 IF NOT WSH_UTIL_CORE.ValidateActualDepartureDate(p_ship_confirm_rule_id, FND_DATE.CANONICAL_TO_DATE(p_actual_departure_date)) THEN
2047 raise WSH_NO_FUTURE_SHIPDATE;
2048 END IF;
2049
2050 l_select_criteria.process_mode := G_SHIP_CONFIRM;
2051 l_select_criteria.client_id := p_client_id; --Modified R12.1.1 LSP PROJECT
2052 l_select_criteria.organization_id := p_organization_id;
2053 l_select_criteria.pr_batch_id := p_pr_batch_id;
2054 l_select_criteria.ap_batch_id := p_ap_batch_id;
2055 l_select_criteria.delivery_name_lo := p_delivery_name_lo;
2056 l_select_criteria.delivery_name_hi := p_delivery_name_hi;
2060 l_select_criteria.ship_from_loc_id := p_ship_from_loc_id;
2057 l_select_criteria.bol_number_lo := p_bol_number_lo;
2058 l_select_criteria.bol_number_hi := p_bol_number_hi;
2059 l_select_criteria.planned_flag := p_planned_flag;
2061 l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
2062 l_select_criteria.intmed_ship_to_loc_id := p_intmed_ship_to_loc_id;
2063 l_select_criteria.pooled_ship_to_loc_id := p_pooled_ship_to_loc_id;
2064 l_select_criteria.customer_id := p_customer_id;
2065 l_select_criteria.ship_method_code := p_ship_method_code;
2066 l_select_criteria.fob_code := p_fob_code;
2067 l_select_criteria.freight_terms_code := p_freight_terms_code;
2068 l_select_criteria.pickup_date_lo := p_pickup_date_lo;
2069 l_select_criteria.pickup_date_hi := p_pickup_date_hi;
2070 l_select_criteria.dropoff_date_lo := p_dropoff_date_lo;
2071 l_select_criteria.dropoff_date_hi := p_dropoff_date_hi;
2072 l_select_criteria.log_level := l_log_level;
2073
2074 Select_Deliveries(
2075 p_input_info => l_select_criteria,
2076 p_batch_rec => l_batch_rec,
2077 x_selected_del_tab => l_selected_del_tab,
2078 x_return_status => l_return_status);
2079
2080 -- should we also pack the input parameters into a record
2081 -- so I will pack them into record, and
2082 -- this is the record used to populate wsh_picking_batches
2083 -- otherwise i need to have a bunch of if stmt again to check the parameters
2084
2085 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2086 raise WSH_SELECT_ERR;
2087 END IF;
2088
2089 IF l_selected_del_tab.count > 0 THEN
2090
2091 -- deliveryMerge
2092 FOR i in l_selected_del_tab.FIRST .. l_selected_del_tab.LAST LOOP
2093 l_delivery_ids(l_delivery_ids.count+1) := l_selected_del_tab(i).delivery_id;
2094 END LOOP;
2095
2096 -- call adjust_planned_flag to plan the deliveries
2097 -- because during ship confirm, no other delivery
2098 -- detail lines should be appended to the deliveries
2099 IF l_debug_on THEN
2100 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Adjust_Planned_Flag');
2101 END IF;
2102
2103 WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag(
2104 p_delivery_ids => l_delivery_ids,
2105 p_caller => 'WSH_DLMG',
2106 p_force_appending_limit => 'Y',
2107 p_call_lcss => 'N',
2108 x_return_status => l_return_status);
2109 IF l_debug_on THEN
2110 WSH_DEBUG_SV.logmsg(l_module_name, 'Return status from Calling Adjust_Planned_Flag:'||l_return_status);
2111 END IF;
2112
2113 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2114 l_warning_num := l_warning_num + 1;
2115 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or
2116 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2117 raise Adjust_Planned_Flag_Err;
2118 END IF;
2119 commit;
2120
2121 -- insert new record in table WSH_PICKING_BATCHES, with NON_PICKING_FLAG = 'Y',
2122 -- SHIP_CONFIRM_RULE_ID = <p_ship_confirm_rule_id>
2123
2124 l_batch_rec.non_picking_flag := 'Y';
2125 l_batch_rec.ship_confirm_rule_id := p_ship_confirm_rule_id;
2126 l_batch_rec.actual_departure_date :=
2127 FND_DATE.canonical_to_date(p_actual_departure_date);
2128 --
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.log(l_module_name, 'Non Picking Flag',
2131 l_batch_rec.non_picking_flag);
2132 WSH_DEBUG_SV.log(l_module_name, 'Ship Confirm Rule ID',
2133 l_batch_rec.ship_confirm_rule_id);
2134 WSH_DEBUG_SV.log(l_module_name, 'Actual Departure Date',
2135 l_batch_rec.actual_departure_date);
2136 --
2137 IF l_batch_rec.actual_departure_date IS NULL THEN
2138 wsh_debug_sv.logmsg(l_module_name,
2139 'NULL input parameter p_actual_departure_date');
2140 ELSE
2141 wsh_debug_sv.logmsg(l_module_name,
2142 'NOT NULL input parameter p_actual_departure_date');
2143 END IF;
2144 --
2148 IF l_debug_on THEN
2145 END IF;
2146
2147 -- bug 5117876, direct insert into wsh_picking_batches table is replaced
2149 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2150 END IF;
2154 X_Batch_Id => l_batch_rec.batch_id,
2151
2152 WSH_PICKING_BATCHES_PKG.Insert_Row(
2153 X_Rowid => l_rowid,
2155 P_Creation_Date => SYSDATE,
2156 P_Created_By => l_user_id,
2157 P_Last_Update_Date => SYSDATE,
2158 P_Last_Updated_By => l_user_id,
2159 P_Last_Update_Login => l_login_id,
2160 P_batch_name_prefix => p_sc_batch_prefix,
2161 X_Name => l_batch_rec.name,
2162 P_Backorders_Only_Flag => NULL,
2163 P_Document_Set_Id => NULL,
2164 P_Existing_Rsvs_Only_Flag => NULL,
2165 P_Shipment_Priority_Code => NULL,
2166 P_Ship_Method_Code => l_batch_rec.ship_method_code,
2167 P_Customer_Id => l_batch_rec.customer_id,
2168 P_Order_Header_Id => NULL,
2169 P_Ship_Set_Number => NULL,
2170 P_Inventory_Item_Id => NULL,
2171 P_Order_Type_Id => NULL,
2172 P_From_Requested_Date => NULL,
2173 P_To_Requested_Date => NULL,
2174 P_From_Scheduled_Ship_Date => NULL,
2175 P_To_Scheduled_Ship_Date => NULL,
2176 P_Ship_To_Location_Id => l_batch_rec.ship_to_location_id,
2177 P_Ship_From_Location_Id => l_batch_rec.ship_from_location_id,
2178 P_Trip_Id => NULL,
2179 P_Delivery_Id => NULL,
2180 P_Include_Planned_Lines => NULL,
2181 P_Pick_Grouping_Rule_Id => NULL,
2182 P_pick_sequence_rule_id => NULL,
2183 P_Autocreate_Delivery_Flag => NULL,
2184 P_Attribute_Category => NULL,
2185 P_Attribute1 => NULL,
2186 P_Attribute2 => NULL,
2187 P_Attribute3 => NULL,
2188 P_Attribute4 => NULL,
2189 P_Attribute5 => NULL,
2190 P_Attribute6 => NULL,
2191 P_Attribute7 => NULL,
2192 P_Attribute8 => NULL,
2193 P_Attribute9 => NULL,
2194 P_Attribute10 => NULL,
2195 P_Attribute11 => NULL,
2196 P_Attribute12 => NULL,
2197 P_Attribute13 => NULL,
2198 P_Attribute14 => NULL,
2199 P_Attribute15 => NULL,
2200 P_Autodetail_Pr_Flag => NULL,
2201 P_Carrier_Id => NULL,
2202 P_Trip_Stop_Id => NULL,
2203 P_Default_stage_subinventory => NULL,
2204 P_Default_stage_locator_id => NULL,
2205 P_Pick_from_subinventory => NULL,
2206 P_Pick_from_locator_id => NULL,
2207 P_Auto_pick_confirm_flag => NULL,
2208 P_Delivery_Detail_ID => NULL,
2209 P_Project_ID => NULL,
2210 P_Task_ID => NULL,
2211 P_Organization_Id => l_batch_rec.organization_id,
2212 P_Ship_Confirm_Rule_Id => l_batch_rec.ship_confirm_rule_id,
2213 P_Autopack_Flag => NULL,
2214 P_Autopack_Level => NULL,
2215 P_Task_Planning_Flag => NULL,
2216 P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
2217 p_regionID => NULL,
2218 p_zoneId => NULL,
2219 p_categoryID => NULL,
2220 p_categorySetID => NULL,
2221 p_acDelivCriteria => NULL,
2222 p_RelSubinventory => NULL,
2223 p_actual_departure_date => l_batch_rec.actual_departure_date,
2224 p_allocation_method => NULL,
2225 p_crossdock_criteria_id => NULL,
2226 p_append_flag => NULL,
2227 p_task_priority => NULL,
2228 p_Delivery_Name_Lo => l_batch_rec.delivery_name_lo,
2229 p_Delivery_Name_Hi => l_batch_rec.delivery_name_hi,
2230 p_Bol_Number_Lo => l_batch_rec.bol_number_lo,
2231 p_Bol_Number_Hi => l_batch_rec.bol_number_hi,
2232 p_Intmed_Ship_To_Loc_Id => l_batch_rec.intmed_ship_to_loc_id,
2233 p_Pooled_Ship_To_Loc_Id => l_batch_rec.pooled_ship_to_loc_id,
2234 p_Fob_Code => l_batch_rec.fob_code,
2235 p_Freight_Terms_Code => l_batch_rec.freight_terms_code,
2236 p_Pickup_Date_Lo => l_batch_rec.pickup_date_lo,
2237 p_Pickup_Date_Hi => l_batch_rec.pickup_date_hi,
2238 p_Dropoff_Date_Lo => l_batch_rec.dropoff_date_lo,
2239 p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
2240 p_Planned_Flag => l_batch_rec.planned_flag,
2241 p_Selected_Batch_Id => l_batch_rec.selected_batch_id,
2242 p_client_id => l_batch_rec.client_id); -- Modified R12.1.1 LSP PROJECT
2243
2244
2245 WSH_UTIL_CORE.PrintMsg('Ship Confirm Batch Name: ' || l_batch_rec.name);
2246
2247 IF l_debug_on THEN
2248 WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be ship confirmed are: ');
2249 FOR k in 1 .. l_selected_del_tab.count LOOP
2250 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_selected_del_tab(k).delivery_id);
2251 END LOOP;
2252 END IF;
2253
2254 Ship_Confirm_Batch(
2255 p_del_tab => l_selected_del_tab,
2256 p_sc_batch_id => l_batch_rec.batch_id,
2257 p_log_level => l_log_level,
2261 p_commit => FND_API.G_TRUE); -- BugFix #4001135
2258 x_confirmed_del_tab => l_confirmed_del_tab,
2259 x_results_summary => l_results_summary,
2260 x_return_status => l_return_status,
2262
2263 WSH_UTIL_CORE.PrintDateTime;
2264 WSH_UTIL_CORE.PrintMsg('Summary: ');
2265 WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2266 WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully ship confirmed');
2267 WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been ship confirmed with warnings');
2268 WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be ship confirmed');
2269
2270 IF l_results_summary.report_req_id > 0 THEN
2274
2271 WSH_UTIL_CORE.PrintMsg('Ship Confirm Report request ID: '|| to_char(l_results_summary.report_req_id));
2272 WSH_UTIL_CORE.PrintMsg('Please see Ship Confirm Report for details');
2273 END IF;
2275 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2276 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2277 RAISE WSH_SC_BATCH_ERR;
2278 END IF;
2279
2280 ELSE
2281 /* no deliveries selected */
2282 WSH_UTIL_CORE.PrintDateTime;
2283 WSH_UTIL_CORE.PrintMsg('Summary: ');
2284 WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
2285
2286 END IF;
2287
2288 errbuf := 'Automated Ship Confirm is completed successfully';
2289 retcode := '0';
2290 IF l_debug_on THEN
2291 WSH_DEBUG_SV.pop(l_module_name);
2292 END IF;
2293
2294
2295 EXCEPTION
2296
2297 WHEN Adjust_Planned_Flag_Err THEN
2298 -- Bug 5097710
2299 wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2300 WSH_UTIL_CORE.PrintMsg('Summary:');
2301 WSH_UTIL_CORE.PrintMsg(l_summary);
2302 WSH_UTIL_CORE.PrintMsg('Details:');
2303 WSH_UTIL_CORE.PrintMsg(l_detail);
2304 WSH_UTIL_CORE.PrintMsg('No. of Errors : ' || l_count);
2305 --
2306 l_completion_status := 'WARNING';
2307 WSH_UTIL_CORE.PrintMsg('Adjust Planned Flag error');
2308 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2309 errbuf := 'Automated Ship Confirm is completed with warning';
2310 retcode := '1';
2311 IF l_debug_on THEN
2312 WSH_DEBUG_SV.pop(l_module_name);
2313 END IF;
2314
2315 WHEN WSH_MISSING_SC_RULE THEN
2316 l_completion_status := 'WARNING';
2317 WSH_UTIL_CORE.PrintMsg('Ship Confirm Rule is not found or has expired');
2318 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2319 errbuf := 'Automated Ship Confirm is completed with warning';
2320 retcode := '1';
2321 IF l_debug_on THEN
2322 WSH_DEBUG_SV.pop(l_module_name);
2323 END IF;
2324
2325 WHEN WSH_MISSING_SC_BATCH_PREFIX THEN
2326 l_completion_status := 'WARNING';
2327 WSH_UTIL_CORE.PrintMsg('You need to specify Ship Confirm Batch Prefix');
2328 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2329 errbuf := 'Automated Ship Confirm is completed with warning';
2330 retcode := '1';
2331 IF l_debug_on THEN
2332 WSH_DEBUG_SV.pop(l_module_name);
2333 END IF;
2334
2335 WHEN WSH_NO_FUTURE_SHIPDATE THEN
2336 l_completion_status := 'WARNING';
2337 WSH_UTIL_CORE.PrintMsg('No Lines were selected for Ship Confirmation because Allow Future Ship Date Parameter is disabled and Actual Ship Date is greater than current system date');
2338 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2339 errbuf := 'Automated Ship Confirm is completed with warning';
2340 retcode := '1';
2341 IF l_debug_on THEN
2342 WSH_DEBUG_SV.pop(l_module_name);
2343 END IF;
2344
2345 WHEN WSH_SELECT_ERR THEN
2346 l_completion_status := 'WARNING';
2347 WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
2348 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2349 errbuf := 'Automated Ship Confirm is completed with warning';
2350 retcode := '1';
2351 IF l_debug_on THEN
2352 WSH_DEBUG_SV.pop(l_module_name);
2353 END IF;
2354
2355 WHEN WSH_SC_BATCH_ERR THEN
2356 l_completion_status := 'WARNING';
2357 WSH_UTIL_CORE.PrintMsg('Ship Confirm failed for this batch');
2358 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2359 errbuf := 'Automated Ship Confirm is completed with warning';
2360 retcode := '1';
2361 IF l_debug_on THEN
2362 WSH_DEBUG_SV.pop(l_module_name);
2363 END IF;
2364
2368 l_error_text := SQLERRM;
2365 WHEN OTHERS THEN
2366 l_completion_status := 'ERROR';
2367 l_error_code := SQLCODE;
2369 WSH_UTIL_CORE.PrintMsg('Confirm Delivery SRS failed with unexpected error.');
2370 WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2371 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2372 errbuf := 'Automated Ship Confirm failed with unexpected error';
2373 retcode := '2';
2374 IF l_debug_on THEN
2375 WSH_DEBUG_SV.pop(l_module_name);
2376 END IF;
2377
2378 END CONFIRM_DELIVERY_SRS;
2379
2380
2381 -- -----------------------------------------------------------------------
2382 -- Name
2383 -- PROCEDURE Auto_Pack_Deliveries_SRS
2384 --
2385 -- Purpose
2386 -- This is auto pack deliveries concurrent program launched using standard
2387 -- report submisstion.
2388 -- It takes users defined criteria and construct a dynamic select statement to
2389 -- get a list of delivery IDs to be auto packed then call Auto_Pack_Deliveries_Batch.
2390 -- The actual auto pack operation is done in Auto_Pack_Deliveries_Batch procedure
2391 --
2392 -- Input Parameters:
2393 -- p_auto_pack_level - 1: auto pack , 2: auto pack master
2394 -- p_organization_id - Organization ID
2395 -- p_pr_batch_id - Pick Release Batch ID
2396 -- p_delivery_name_lo - Delivery Name (Low)
2397 -- p_delivery_name_hi - Delivery Name (High)
2398 -- p_bol_number_lo - BOL Number (Low)
2399 -- p_bol_number_hi - BOL Number (High)
2400 -- p_planned_flag - Planned Flag
2401 -- p_ship_from_loc_id - Ship from Location ID
2402 -- p_ship_to_loc_id - Ship to Location ID
2403 -- p_intmed_ship_to_loc_id - Intermediate Ship to Location ID
2404 -- p_pooled_ship_to_loc_id - Pooled Ship to Location ID
2405 -- p_customer_id - Customer ID
2406 -- p_ship_method_code - Ship Method Code
2407 -- p_fob_code - FOB Code
2408 -- p_freight_terms_code - Freight Terms Code
2409 -- p_pickup_date_lo - Pick up Date (Low)
2410 -- p_pickup_date_hi - Pick up Date (High)
2411 -- p_dropoff_date_lo - Drop off Date (Low)
2412 -- p_dropoff_date_hi - Drop off Date (High)
2413 -- p_log_level - Log Level
2414 --
2415 -- Output Parameters:
2416 -- errbug - standard output parameter for a concurrent program
2417 -- retcode - standard output parameter for a concurrent program
2418 -- ----------------------------------------------------------------------
2419
2420
2421 procedure Auto_Pack_Deliveries_SRS(
2422 errbuf OUT NOCOPY VARCHAR2,
2423 retcode OUT NOCOPY VARCHAR2,
2427 p_pr_batch_id IN NUMBER,
2424 p_auto_pack_level IN NUMBER,
2425 p_ap_batch_prefix IN VARCHAR2,
2426 p_organization_id IN NUMBER,
2428 p_delivery_name_lo IN VARCHAR2,
2429 p_delivery_name_hi IN VARCHAR2,
2430 p_bol_number_lo IN VARCHAR2,
2431 p_bol_number_hi IN VARCHAR2,
2432 p_planned_flag IN VARCHAR2,
2433 p_ship_from_loc_id IN NUMBER,
2434 p_ship_to_loc_id IN NUMBER,
2435 p_intmed_ship_to_loc_id IN NUMBER,
2436 p_pooled_ship_to_loc_id IN NUMBER,
2437 p_customer_id IN VARCHAR2, --RTV changes
2438 p_ship_method_code IN VARCHAR2,
2439 p_fob_code IN VARCHAR2,
2440 p_freight_terms_code IN VARCHAR2,
2441 p_pickup_date_lo IN VARCHAR2,
2442 p_pickup_date_hi IN VARCHAR2,
2443 p_dropoff_date_lo IN VARCHAR2,
2444 p_dropoff_date_hi IN VARCHAR2,
2445 p_log_level IN NUMBER ) IS
2446
2447 l_completion_status VARCHAR2(30);
2448 l_error_code NUMBER;
2449 l_error_text VARCHAR2(2000);
2450 l_user_id NUMBER := 0;
2451 l_login_id NUMBER := 0;
2452 l_auto_pack_level NUMBER := 0;
2453 l_log_level NUMBER := 0;
2454 l_return_status VARCHAR2(30) := NULL;
2455 l_rowid VARCHAR2(30);
2456 l_temp BOOLEAN;
2457 l_batch_rec WSH_PICKING_BATCHES%ROWTYPE;
2458 l_debug_on BOOLEAN;
2459 l_packed_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
2460 l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
2461 l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
2462 l_results_summary WSH_BATCH_PROCESS.Results_Summary_Rec;
2463
2464 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Auto_Pack_Deliveries_SRS';
2465 WSH_PK_BATCH_ERR EXCEPTION;
2466 WSH_INVALID_AUTO_PACK_LEVEL EXCEPTION;
2467 WSH_SELECT_ERR EXCEPTION;
2468 --
2469 l_batch_name VARCHAR2(30);
2470 --
2471 BEGIN
2472 --
2473 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2474 --
2475 IF l_debug_on IS NULL
2476 THEN
2480 IF l_debug_on THEN
2477 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2478 END IF;
2479 --
2481 WSH_DEBUG_SV.push(l_module_name);
2482 END IF;
2483
2484 l_completion_status := 'NORMAL';
2485
2486 l_packed_del_tab.delete;
2487 -- Fetch user and login information
2488 l_user_id := FND_GLOBAL.USER_ID;
2489 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2490
2491 IF p_auto_pack_level IS NULL THEN
2492 l_auto_pack_level := 1;
2493 ELSE
2494 l_auto_pack_level := p_auto_pack_level;
2495 END IF;
2496
2497 IF p_log_level IS NULL THEN
2498 l_log_level := 0;
2499 ELSE
2500 l_log_level := p_log_level;
2501 END IF;
2502
2503 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2504 WSH_UTIL_CORE.Set_Log_Level(l_log_level);
2505
2506 WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
2507 WSH_UTIL_CORE.PrintMsg(' Auto Pack Level: '||to_char(p_auto_pack_level));
2508 WSH_UTIL_CORE.PrintMsg(' Auto Pack Batch Prefix: '|| p_ap_batch_prefix);
2509
2510 l_select_criteria.process_mode := G_AUTO_PACK;
2511 l_select_criteria.organization_id := p_organization_id;
2512 l_select_criteria.pr_batch_id := p_pr_batch_id;
2513 l_select_criteria.delivery_name_lo := p_delivery_name_lo;
2514 l_select_criteria.delivery_name_hi := p_delivery_name_hi;
2515 l_select_criteria.bol_number_lo := p_bol_number_lo;
2516 l_select_criteria.bol_number_hi := p_bol_number_hi;
2517 l_select_criteria.planned_flag := p_planned_flag;
2518 l_select_criteria.ship_from_loc_id := p_ship_from_loc_id;
2519 l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
2520 l_select_criteria.intmed_ship_to_loc_id := p_intmed_ship_to_loc_id;
2521 l_select_criteria.pooled_ship_to_loc_id := p_pooled_ship_to_loc_id;
2522 l_select_criteria.customer_id := p_customer_id;
2523 l_select_criteria.ship_method_code := p_ship_method_code;
2524 l_select_criteria.fob_code := p_fob_code;
2525 l_select_criteria.freight_terms_code := p_freight_terms_code;
2526 l_select_criteria.pickup_date_lo := p_pickup_date_lo;
2527 l_select_criteria.pickup_date_hi := p_pickup_date_hi;
2528 l_select_criteria.dropoff_date_lo := p_dropoff_date_lo;
2529 l_select_criteria.dropoff_date_hi := p_dropoff_date_hi;
2530 l_select_criteria.log_level := l_log_level;
2531
2532 Select_Deliveries(
2533 p_input_info => l_select_criteria,
2534 p_batch_rec => l_batch_rec,
2535 x_selected_del_tab => l_selected_del_tab,
2536 x_return_status => l_return_status);
2537
2538 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2539 raise WSH_SELECT_ERR;
2540 END IF;
2541
2542 IF l_selected_del_tab.count > 0 THEN
2543
2544 -- insert new record in table WSH_PICKING_BATCHES, with NON_PICKING_FLAG = 'Y',
2545 -- SHIP_CONFIRM_RULE_ID = <p_ship_confirm_rule_id>
2546
2547 -- required fields for auto pack batch
2548 l_batch_rec.non_picking_flag := 'Y';
2549 l_batch_rec.autopack_flag := 'Y';
2550 l_batch_rec.autopack_level := l_auto_pack_level;
2551 --
2552 IF l_debug_on THEN
2553 WSH_DEBUG_SV.log(l_module_name, 'Non Picking Flag', l_batch_rec.non_picking_flag);
2554 WSH_DEBUG_SV.log(l_module_name, 'Batch Name', l_batch_rec.name);
2555 WSH_DEBUG_SV.log(l_module_name, 'Auto Pack Flag', l_batch_rec.autopack_flag);
2556 WSH_DEBUG_SV.log(l_module_name, 'Auto Pack Level', l_batch_rec.autopack_level);
2557 END IF;
2558
2559 -- bug 5117876, direct insert into wsh_picking_batches table is replaced
2560 IF l_debug_on THEN
2561 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2562 END IF;
2563
2564 WSH_PICKING_BATCHES_PKG.Insert_Row(
2565 X_Rowid => l_rowid,
2566 X_Batch_Id => l_batch_rec.batch_id,
2567 P_Creation_Date => SYSDATE,
2568 P_Created_By => l_user_id,
2569 P_Last_Update_Date => SYSDATE,
2570 P_Last_Updated_By => l_user_id,
2571 P_Last_Update_Login => l_login_id,
2572 P_batch_name_prefix => p_ap_batch_prefix,
2573 X_Name => l_batch_rec.name,
2574 P_Backorders_Only_Flag => NULL,
2575 P_Document_Set_Id => NULL,
2576 P_Existing_Rsvs_Only_Flag => NULL,
2580 P_Order_Header_Id => NULL,
2577 P_Shipment_Priority_Code => NULL,
2578 P_Ship_Method_Code => l_batch_rec.ship_method_code,
2579 P_Customer_Id => l_batch_rec.customer_id,
2581 P_Ship_Set_Number => NULL,
2582 P_Inventory_Item_Id => NULL,
2583 P_Order_Type_Id => NULL,
2587 P_To_Scheduled_Ship_Date => NULL,
2584 P_From_Requested_Date => NULL,
2585 P_To_Requested_Date => NULL,
2586 P_From_Scheduled_Ship_Date => NULL,
2588 P_Ship_To_Location_Id => l_batch_rec.ship_to_location_id,
2589 P_Ship_From_Location_Id => l_batch_rec.ship_from_location_id,
2590 P_Trip_Id => NULL,
2591 P_Delivery_Id => NULL,
2592 P_Include_Planned_Lines => NULL,
2593 P_Pick_Grouping_Rule_Id => NULL,
2594 P_pick_sequence_rule_id => NULL,
2595 P_Autocreate_Delivery_Flag => NULL,
2596 P_Attribute_Category => NULL,
2597 P_Attribute1 => NULL,
2598 P_Attribute2 => NULL,
2599 P_Attribute3 => NULL,
2600 P_Attribute4 => NULL,
2601 P_Attribute5 => NULL,
2602 P_Attribute6 => NULL,
2603 P_Attribute7 => NULL,
2604 P_Attribute8 => NULL,
2605 P_Attribute9 => NULL,
2606 P_Attribute10 => NULL,
2607 P_Attribute11 => NULL,
2608 P_Attribute12 => NULL,
2609 P_Attribute13 => NULL,
2610 P_Attribute14 => NULL,
2611 P_Attribute15 => NULL,
2612 P_Autodetail_Pr_Flag => NULL,
2613 P_Carrier_Id => NULL,
2614 P_Trip_Stop_Id => NULL,
2615 P_Default_stage_subinventory => NULL,
2616 P_Default_stage_locator_id => NULL,
2617 P_Pick_from_subinventory => NULL,
2618 P_Pick_from_locator_id => NULL,
2619 P_Auto_pick_confirm_flag => NULL,
2620 P_Delivery_Detail_ID => NULL,
2621 P_Project_ID => NULL,
2622 P_Task_ID => NULL,
2623 P_Organization_Id => l_batch_rec.organization_id,
2624 P_Ship_Confirm_Rule_Id => NULL,
2625 P_Autopack_Flag => l_batch_rec.autopack_flag,
2626 P_Autopack_Level => l_batch_rec.autopack_level,
2627 P_Task_Planning_Flag => NULL,
2628 P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
2629 p_regionID => NULL,
2630 p_zoneId => NULL,
2631 p_categoryID => NULL,
2632 p_categorySetID => NULL,
2633 p_acDelivCriteria => NULL,
2634 p_RelSubinventory => NULL,
2635 p_actual_departure_date => NULL,
2636 p_allocation_method => NULL,
2637 p_crossdock_criteria_id => NULL,
2638 p_append_flag => NULL,
2639 p_task_priority => NULL,
2640 p_Delivery_Name_Lo => l_batch_rec.delivery_name_lo,
2641 p_Delivery_Name_Hi => l_batch_rec.delivery_name_hi,
2642 p_Bol_Number_Lo => l_batch_rec.bol_number_lo,
2643 p_Bol_Number_Hi => l_batch_rec.bol_number_hi,
2644 p_Intmed_Ship_To_Loc_Id => l_batch_rec.intmed_ship_to_loc_id,
2645 p_Pooled_Ship_To_Loc_Id => l_batch_rec.pooled_ship_to_loc_id,
2646 p_Fob_Code => l_batch_rec.fob_code,
2647 p_Freight_Terms_Code => l_batch_rec.freight_terms_code,
2648 p_Pickup_Date_Lo => l_batch_rec.pickup_date_lo,
2649 p_Pickup_Date_Hi => l_batch_rec.pickup_date_hi,
2650 p_Dropoff_Date_Lo => l_batch_rec.dropoff_date_lo,
2651 p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
2652 p_Planned_Flag => l_batch_rec.planned_flag,
2653 p_Selected_Batch_Id => l_batch_rec.selected_batch_id);
2654
2655 WSH_UTIL_CORE.PrintMsg('Auto Pack Batch Name: ' || l_batch_rec.name);
2656
2657 IF l_debug_on THEN
2658 WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be auto packed are: ');
2659 FOR k in 1 .. l_selected_del_tab.count LOOP
2660 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_selected_del_tab(k).delivery_id);
2661 END LOOP;
2662 END IF;
2663
2664 Auto_Pack_Deliveries_Batch(
2665 p_del_tab => l_selected_del_tab,
2666 p_ap_batch_id => l_batch_rec.batch_id,
2667 p_auto_pack_level => p_auto_pack_level,
2668 p_log_level => l_log_level,
2669 x_packed_del_tab => l_packed_del_tab,
2670 x_results_summary => l_results_summary,
2671 x_return_status => l_return_status,
2672 p_commit => FND_API.G_TRUE); -- BugFix #4001135
2673
2674 IF l_debug_on THEN
2675 WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries successfully packed are: ');
2676 FOR k in 1 .. l_packed_del_tab.count LOOP
2677 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_packed_del_tab(k).delivery_id);
2678 END LOOP;
2679 END IF;
2680
2681 WSH_UTIL_CORE.PrintDateTime;
2682 WSH_UTIL_CORE.PrintMsg('Summary: ');
2683 WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2684 WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.success)||' deliveries have been successfully packed');
2685 WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.warning)||' deliveries have been packed with warnings');
2686 WSH_UTIL_CORE.PrintMsg(to_char(l_results_summary.failure)||' deliveries cannot be packed');
2687 IF l_results_summary.report_req_id > 0 THEN
2688 WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries Report request ID: '|| to_char(l_results_summary.report_req_id));
2689 WSH_UTIL_CORE.PrintMsg('Please see Auto Pack Deliveries Report for details');
2690 END IF;
2691
2695 END IF;
2692 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) AND
2693 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2694 RAISE WSH_PK_BATCH_ERR;
2696
2697 ELSE
2698 /* not deliveries selected */
2699 WSH_UTIL_CORE.PrintDateTime;
2700 WSH_UTIL_CORE.PrintMsg('Summary: ');
2701 WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
2702
2703 END IF;
2704 errbuf := 'Auto Pack Deliveries is completed successfully';
2705 retcode := '0';
2706
2707 IF l_debug_on THEN
2708 WSH_DEBUG_SV.pop(l_module_name);
2709 END IF;
2710
2711
2712 EXCEPTION
2713
2714 WHEN WSH_INVALID_AUTO_PACK_LEVEL THEN
2715 l_completion_status := 'WARNING';
2716 WSH_UTIL_CORE.PrintMsg('Invalid Auto Packing Level');
2717 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2718 errbuf := 'Auto Pack Deliveries is completed with warning';
2719 retcode := '1';
2720 IF l_debug_on THEN
2721 WSH_DEBUG_SV.pop(l_module_name);
2722 END IF;
2723
2724 WHEN WSH_PK_BATCH_ERR THEN
2725 l_completion_status := 'WARNING';
2726 WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries failed for this batch');
2727 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2728 errbuf := 'Auto Pack Deliveries is completed with warning';
2729 retcode := '1';
2730 IF l_debug_on THEN
2731 WSH_DEBUG_SV.pop(l_module_name);
2732 END IF;
2733
2734 WHEN WSH_SELECT_ERR THEN
2735 l_completion_status := 'WARNING';
2736 WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
2737 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2738 errbuf := 'Auto Pack Deliveries is completed with warning';
2739 retcode := '1';
2740 IF l_debug_on THEN
2741 WSH_DEBUG_SV.pop(l_module_name);
2742 END IF;
2743
2744 WHEN OTHERS THEN
2745 l_completion_status := 'ERROR';
2746 l_error_code := SQLCODE;
2747 l_error_text := SQLERRM;
2748 WSH_UTIL_CORE.PrintMsg('Auto Pack Deliveries SRS failed with unexpected error.');
2749 WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
2750 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2751 errbuf := 'Auto Pack Deliveries failed with unexpected error';
2752 retcode := '2';
2753 IF l_debug_on THEN
2754 WSH_DEBUG_SV.pop(l_module_name);
2755 END IF;
2756 END Auto_Pack_Deliveries_SRS;
2757
2758
2759
2760 PROCEDURE log_batch_messages(p_batch_id IN NUMBER,
2761 p_delivery_id IN NUMBER,
2762 p_stop_id IN NUMBER,
2763 p_exception_location_id IN NUMBER,
2764 p_error_status IN VARCHAR2) IS
2765
2766 c NUMBER;
2767 i NUMBER;
2768 l_buffer VARCHAR2(4000);
2769 l_index_out NUMBER;
2770 l_return_status VARCHAR2(1);
2771 l_msg_count NUMBER;
2772 l_msg_data VARCHAR2(2000);
2773 l_exception_id NUMBER;
2774 l_error_message wsh_exceptions.error_message%type;
2775
2776 --
2777 l_debug_on BOOLEAN;
2778 --
2779 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_BATCH_MESSAGES';
2780 --
2781
2782
2783 BEGIN
2784 --
2785 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2786 --
2787 IF l_debug_on IS NULL
2788 THEN
2789 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2790 END IF;
2791 --
2792 IF l_debug_on THEN
2793 WSH_DEBUG_SV.push(l_module_name);
2794 --
2795 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
2796 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2797 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2798 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_LOCATION_ID',P_EXCEPTION_LOCATION_ID);
2799 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_STATUS',P_ERROR_STATUS);
2800 END IF;
2801
2802 IF p_batch_id IS NULL THEN
2803 RETURN;
2804 END IF;
2805 -- We populate the error_message column in wsh_exceptions if the exception
2806 -- was logged for autopack. If the exception was logged for ship confirm we
2807 -- leave it as NULL.
2808 /* IF p_error_status = 'W' THEN
2809
2810 IF g_error_message is NULL THEN
2811
2812 g_error_message := substrb(FND_MESSAGE.Get_String('FND', 'FND_MBOX_WARN_CONSTANT'), 1,500);
2813
2814 END IF;
2815
2816 l_error_message := g_error_message;
2817
2818 END IF;*/
2819
2820 l_error_message := p_error_status;
2821
2822 c := FND_MSG_PUB.count_msg;
2823 IF l_debug_on THEN
2824 WSH_DEBUG_SV.logmsg(l_module_name,'COUNT--',c);
2825 END IF;
2826 FOR i in 1..c LOOP
2827 FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
2828 p_msg_index => i,
2829 p_data => l_buffer,
2830 p_msg_index_out => l_index_out);
2831 -- pass only first 2000 characters of l_buffer
2832 -- only for cases when l_buffer is not null
2833 IF l_buffer IS NOT NULL THEN
2834 wsh_xc_util.log_exception(p_api_version => 1.0,
2835 x_return_status => l_return_status,
2836 x_msg_count => l_msg_count,
2837 x_msg_data => l_msg_data,
2838 x_exception_id => l_exception_id,
2839 p_exception_location_id => p_exception_location_id,
2840 p_logged_at_location_id => p_exception_location_id,
2841 p_logging_entity => 'SHIPPER',
2845 p_trip_stop_id => p_stop_id,
2842 p_logging_entity_id => FND_GLOBAL.USER_ID,
2843 p_exception_name => 'WSH_BATCH_MESSAGE',
2844 p_message => substrb(l_buffer,1,2000),
2846 p_delivery_id => p_delivery_id,
2847 p_batch_id => p_batch_id,
2848 p_error_message => l_error_message);
2849 -- Bug 2713285
2850 l_exception_id := null;
2851 END IF;
2852 END LOOP;
2853 --
2854 IF l_debug_on THEN
2855 WSH_DEBUG_SV.pop(l_module_name);
2856 END IF;
2857 --
2858
2859
2860 EXCEPTION
2861 WHEN OTHERS THEN
2862 WSH_UTIL_CORE.Default_Handler('WSH_BATCH_PROCESS.log_batch_messages');
2863 --
2864 IF l_debug_on THEN
2865 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION: OTHERS');
2866 END IF;
2867 --
2868
2869 END log_batch_messages;
2870
2871 -- -----------------------------------------------------------------------
2872 -- Name
2873 -- PROCEDURE Select_Delivery_Lines (private within the package)
2874 -- Purpose
2875 -- This procedure select the delivery lines according to the selection criteria and
2876 -- return a list of delivery lines selected.
2877 -- Input Parameters:
2878 -- p_select_criteria This record stores the selection criteria
2879 -- p_autocreate_deliveries Indidate whether to autocreate deliveries for the delivery lines
2880 -- 'Y': do autocreate deliveries , 'SP' shipping parameters
2881 --
2882 -- Output Parameters:
2883 -- x_selected_det_tbl Delivery lines selected
2884 -- x_return_status Return status
2885 ---- ----------------------------------------------------------------------
2886
2887 PROCEDURE Select_Delivery_Lines (
2888 p_select_criteria IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
2889 p_autocreate_deliveries IN VARCHAR2,
2890 x_selected_det_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2891 x_return_status OUT NOCOPY VARCHAR2)
2892 IS
2893 --TCA View Changes Start
2894 --RTV changes
2895 CURSOR get_customer_name(c_customer_id varchar2) IS
2896 SELECT substrb ( party.party_name, 1, 50 ) customer_name
2897 FROM hz_parties Party, hz_cust_accounts cust_acct
2898 WHERE cust_acct.party_id = party.party_id AND
2899 cust_acct.cust_account_id = to_number(SubStr(c_customer_id,3))
2900 AND SubStr(c_customer_id,1,1) = 'C'
2901 UNION
2902 SELECT substrb ( party.party_name, 1, 50 ) customer_name
2903 FROM hz_parties Party, po_vendors pov
2904 WHERE pov.party_id = party.party_id
2905 AND pov.vendor_id = to_number(SubStr(c_customer_id,3))
2906 AND SubStr(c_customer_id,1,1) = 'V';
2907 --RTV changes
2908 --TCA View Changes end
2909
2910 l_sc_SELECT VARCHAR2(3000) := NULL;
2911 l_sc_FROM VARCHAR2(3000) := NULL;
2912 l_sc_WHERE VARCHAR2(3000) := NULL;
2913 l_sc_FINAL VARCHAR2(4000) := NULL;
2914 l_sub_str VARCHAR2(2000);
2915 l_msg_string VARCHAR2(80);
2916 l_str_length NUMBER := 0;
2917 l_scheduled_ship_date_lo DATE;
2918 l_scheduled_ship_date_hi DATE;
2919 i NUMBER := 0;
2920 v_delivery_detail_id NUMBER := 0;
2921 v_autocreate_deliveries_flag VARCHAR2(1);
2922 v_cursorID INTEGER;
2923 v_ignore INTEGER;
2924 l_debug_on BOOLEAN;
2925 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Select_Delivery_Lines';
2926
2927 --Perf bug 5218515
2928 v_delivery_detail_id_tbl DBMS_SQL.NUMBER_TABLE;
2929 l_fetch_count NUMBER := 1000;
2930 l_start_index NUMBER := 1;
2931 l_records_fetched NUMBER := 0;
2932 --
2933 -- Bug 5624475 and ECO 5676263
2934 l_line_status VARCHAR2(3);
2935 -- LSP PROJECT : Begin
2936 l_client_id NUMBER;
2937 l_client_code VARCHAR2(10);
2938 -- LSP PROJECT : End
2939 --
2940 BEGIN
2941 --
2942 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2943 --
2944 IF l_debug_on IS NULL
2945 THEN
2946 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2947 END IF;
2948 --
2949 IF l_debug_on THEN
2950 WSH_DEBUG_SV.push(l_module_name);
2951 END IF;
2952 -- set default return status to SUCCESS
2953 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2954 -- clear up x_selected_det_tbl table
2955 x_selected_det_tbl.delete;
2956
2957 IF p_autocreate_deliveries is NULL OR p_autocreate_deliveries='SP' THEN
2958 -- p_autocreate_deliveries = 'SP', need to join with wsh_shipping_parameters
2959 l_sc_SELECT := l_sc_SELECT || ' wdd.delivery_detail_id ';
2960 l_sc_FROM := l_sc_FROM || ' wsh_delivery_details wdd , wsh_delivery_assignments_v wda ';
2961 l_sc_FROM := l_sc_FROM || ' , wsh_shipping_parameters wsp ';
2962 l_sc_WHERE := l_sc_WHERE || ' wda.delivery_detail_id = wdd.delivery_detail_id ';
2963 l_sc_WHERE := l_sc_WHERE || ' and wdd.organization_id = wsp.organization_id ';
2964 l_sc_WHERE := l_sc_WHERE || ' and wsp.autocreate_deliveries_flag = ''Y'' ';
2965
2966 ELSIF p_autocreate_deliveries = 'Y' THEN
2967 -- p_autocreate_deliveries = 'Y' do not join with wsh_shipping_parameters
2968 l_sc_SELECT := l_sc_SELECT || ' wdd.delivery_detail_id ';
2969 l_sc_FROM := l_sc_FROM || ' wsh_delivery_details wdd , wsh_delivery_assignments_v wda ';
2970 l_sc_WHERE := l_sc_WHERE || ' wda.delivery_detail_id = wdd.delivery_detail_id ';
2971
2972 ELSE
2973 -- invalud value in p_autocreate_deliveries
2977 END IF;
2974 IF l_debug_on THEN
2975 WSH_DEBUG_SV.logmsg(l_module_name, ' No delivery lines fetched because p_autocreate_deliveries is: '||p_autocreate_deliveries);
2976 WSH_DEBUG_SV.pop(l_module_name);
2978 return;
2979 END IF;
2980
2981 l_sc_WHERE := l_sc_WHERE || ' and wda.delivery_id is null ';
2982 l_sc_WHERE := l_sc_WHERE || ' and wdd.container_flag = ''N'' ';
2983 l_sc_WHERE := l_sc_WHERE || ' and NVL(wdd.line_direction, ''O'') = ''O'' ';
2984
2985
2986 IF p_select_criteria.delivery_lines_status is not NULL THEN
2987 IF p_select_criteria.delivery_lines_status = 'ALL' THEN
2988 l_sc_WHERE := l_sc_WHERE ||'AND wdd.released_status in (''X'', ''R'', ''S'', ''Y'' , ''B'') '; --Bugfix 9129793 added Backorder status
2989 ELSE
2990 l_sc_WHERE := l_sc_WHERE ||'AND wdd.released_status = :x_released_status ';
2991 END IF;
2992 --
2993 -- Bug 5624475 and ECO 5676263 :
2994 -- Distinguish between Planned for X-dock status and Released to warehouse
2995 --
2996 IF p_select_criteria.delivery_lines_status = 'K' THEN
2997 l_sc_WHERE := l_sc_WHERE || ' AND wdd.move_order_line_id IS NULL ';
2998 ELSIF p_select_criteria.delivery_lines_status = 'S' THEN
2999 l_sc_WHERE := l_sc_WHERE || ' AND wdd.move_order_line_id IS NOT NULL ';
3000 END IF;
3001 -- End Bug 5624475 and ECO 5676263
3002 --
3003 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_STATUS');
3004 l_msg_string := NULL;
3005 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3006 p_lookup_code => p_select_criteria.delivery_lines_status,
3007 p_lookup_type => 'WSH_PD_DEL_LINE_STATUS' );
3008 FND_MESSAGE.SET_TOKEN('DETAILS_STATUS', l_msg_string);
3009 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3010
3011 WSH_UTIL_CORE.PrintMsg(' Delivery Lines Status: '|| p_select_criteria.delivery_lines_status);
3012 END IF;
3013
3014 -- put organization in the where clause if it is part of selection criteria
3015 IF p_select_criteria.organization_id IS NOT NULL THEN
3016 l_sc_WHERE := l_sc_WHERE ||'AND wdd.organization_id = :x_organization_id ';
3017 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3018 FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_select_criteria.organization_id));
3019 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3020
3021 WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_select_criteria.organization_id);
3022 END IF;
3023
3024
3025 -- put scheduled ship date in the where clause if it is part of selection criteria
3026 IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL OR p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
3027
3028 IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL AND p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
3029 l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
3030 l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
3031
3032 l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled BETWEEN :x_scheduled_ship_date_lo AND :x_scheduled_ship_date_hi ';
3033
3034
3035 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_LO');
3036 FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_LO', to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3037 FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3038
3039 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_HI');
3040 FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3041 FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3042
3043 WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3044 WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3045
3046 ELSIF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3047 l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
3048 l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled >= :x_scheduled_ship_date_lo ';
3049
3050 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_LO');
3051 FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_LO', to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3052 FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3053 WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date Start: '|| to_char(l_scheduled_ship_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3054
3055 ELSE
3056 l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
3057 l_sc_WHERE := l_sc_WHERE ||'AND wdd.date_scheduled <= :x_scheduled_ship_date_hi ';
3058 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCHD_SHIP_DATE_HI');
3059 FND_MESSAGE.SET_TOKEN('SCHD_SHIP_DATE_HI', to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3060 FND_FILE.put_line(FND_FILE.output, FND_MESSAGE.GET);
3061 WSH_UTIL_CORE.PrintMsg(' Scheduled Ship Date End: '|| to_char(l_scheduled_ship_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3062 END IF;
3063 END IF;
3064
3065 -- put ship to location id in the where clause if it is part of selection criteria
3066 IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
3067 l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_to_location_id = :x_ship_to_loc_id ';
3068
3069 l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3070 p_location_id => p_select_criteria.ship_to_loc_id,
3071 p_format => 'NEW UI CODE'),
3072 1, 80);
3073
3074 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_TO');
3075 FND_MESSAGE.SET_TOKEN('SHIP_TO', l_msg_string);
3076
3077 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3078
3082 -- put source code in the where clause if it is part of selection criteria
3079 WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_select_criteria.ship_to_loc_id));
3080 END IF;
3081
3083 IF p_select_criteria.source_code IS NOT NULL THEN
3084 l_msg_string := NULL;
3085 IF p_select_criteria.source_code = 'ALL' THEN
3086 l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code not in (''PO'', ''WSH'') ';
3087 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3088 p_lookup_code => p_select_criteria.source_code,
3089 p_lookup_type => 'WSH_PD_SOURCE_SYSTEM' );
3090 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SOURCE_SYSTEM');
3091 FND_MESSAGE.SET_TOKEN('SOURCE_NAME', l_msg_string);
3092 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3093 WSH_UTIL_CORE.PrintMsg(' Source System: '|| p_select_criteria.source_code);
3094 ELSE
3095 l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code = :x_source_code ';
3096 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3097 p_lookup_code => p_select_criteria.source_code,
3098 p_lookup_type => 'SOURCE_SYSTEM' );
3099 FND_MESSAGE.SET_NAME('WSH', 'SOURCE_SYSTEM');
3100 FND_MESSAGE.SET_TOKEN('SOURCE_NAME', l_msg_string);
3101 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3102 WSH_UTIL_CORE.PrintMsg(' Source System: '|| p_select_criteria.source_code);
3103 END IF;
3104
3105 l_msg_string := NULL;
3106 ELSE
3107 l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code not in (''PO'', ''WSH'') ';
3108 END IF;
3109
3110
3111 -- put customer id in the where clause if it is part of selection criteria
3112 IF p_select_criteria.customer_id IS NOT NULL THEN
3113 --RTV changes
3114 IF SubStr(p_select_criteria.customer_id,1,1) = 'V' THEN
3115 l_sc_WHERE := l_sc_WHERE || 'AND wdd.consignee_flag = ''V'' ';
3116 ELSE
3117 l_sc_WHERE := l_sc_WHERE || 'AND nvl(wdd.consignee_flag,''C'') = ''C'' ';
3118 END IF;
3119 --RTV changes
3120
3121 l_sc_WHERE := l_sc_WHERE || 'AND wdd.customer_id = :x_customer_id ';
3122
3123 l_msg_string := NULL;
3124
3125 OPEN get_customer_name(p_select_criteria.customer_id);
3126 FETCH get_customer_name INTO l_msg_string;
3127 CLOSE get_customer_name;
3128
3129 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_CUSTOMER');
3130 FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_msg_string);
3131 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3132
3133 WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| SubStr(p_select_criteria.customer_id,3)); --RTV changes
3134 END IF;
3135
3136 -- put ship method code in the where clause if it is part of selection criteria
3137 IF p_select_criteria.ship_method_code IS NOT NULL THEN
3138 l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_method_code = :x_ship_method_code ';
3139 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_METHOD');
3140 l_msg_string := NULL;
3141 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3142 p_lookup_code => p_select_criteria.ship_method_code,
3143 p_lookup_type => 'SHIP_METHOD' );
3144 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_msg_string);
3145 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3146 WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_select_criteria.ship_method_code);
3147 END IF;
3148
3149 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
3150 FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_select_criteria.log_level));
3151 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3152 WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_select_criteria.log_level);
3153 --
3154 -- LSP PROJECT : put client id in the where clause if it is part of selection criteria
3155 IF p_select_criteria.client_id IS NOT NULL THEN
3156 l_sc_WHERE := l_sc_WHERE || 'AND wdd.client_id = :x_client_id ';
3157 l_msg_string := NULL;
3158 l_client_id := p_select_criteria.client_id;
3159 wms_deploy.get_client_details(
3160 x_client_id => l_client_id,
3161 x_client_name => l_msg_string,
3162 x_client_code => l_client_code,
3163 x_return_status => x_return_status);
3164 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3165 IF l_debug_on THEN
3166 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in WMS_DEPLOY.GET_CLIENT_DETAILS');
3167 RETURN;
3168 END IF;
3169 END IF;
3170 FND_MESSAGE.SET_NAME('WSH', 'WSH_CLIENT');
3171 FND_MESSAGE.SET_TOKEN('CLIENT_NAME', l_msg_string);
3172 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3173 WSH_UTIL_CORE.PrintMsg(' Client ID: '|| p_select_criteria.client_id);
3174 END IF;
3175 -- LSP PROJECT : end
3176 --
3177
3178 l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
3179
3180
3181 IF p_select_criteria.log_level > 0 OR l_debug_on THEN
3182 -- print SELECT statement if deubg is turned on
3183 i := 1;
3184 l_str_length := length(l_sc_FINAL);
3185
3186 LOOP
3187 IF i > l_str_length THEN
3188 EXIT;
3189 END IF;
3190 l_sub_str := SUBSTR(l_sc_FINAL, i , 80);
3191 -- l_sub_str := SUBSTR(l_sc_FINAL, i , WSH_UTIL_CORE.G_MAX_LENGTH);
3192 WSH_UTIL_CORE.PrintMsg(l_sub_str);
3193 i := i + 80;
3194 -- i := i + WSH_UTIL_CORE.G_MAX_LENGTH;
3195 END LOOP;
3196 END IF;
3197
3198 IF l_debug_on THEN
3199 WSH_DEBUG_SV.logmsg(l_module_name,'Bind variables: ');
3200 WSH_DEBUG_SV.logmsg(l_module_name,'========================');
3201 WSH_DEBUG_SV.log(l_module_name,' x_organization_id', to_char(p_select_criteria.organization_id));
3205 WSH_DEBUG_SV.log(l_module_name,' x_ship_to_loc_id', to_char(p_select_criteria.ship_to_loc_id));
3202 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'));
3203 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'));
3204 WSH_DEBUG_SV.log(l_module_name,' x_source_code', p_select_criteria.source_code);
3206 WSH_DEBUG_SV.log(l_module_name,' x_customer_id', substr(p_select_criteria.customer_id,3)); --RTV changes
3207 WSH_DEBUG_SV.log(l_module_name,' x_ship_method_code', p_select_criteria.ship_method_code);
3208 WSH_DEBUG_SV.log(l_module_name,' x_released_status', p_select_criteria.delivery_lines_status);
3209 WSH_DEBUG_SV.log(l_module_name,' x_client_id', p_select_criteria.client_id); -- LSP PROJECT
3210 WSH_DEBUG_SV.logmsg(l_module_name,'========================');
3211 END IF;
3212
3213 -- open cursor
3214 v_CursorID := DBMS_SQL.Open_Cursor;
3215
3216 -- parse cursor
3217 DBMS_SQL.Parse(v_CursorID, l_sc_FINAL, DBMS_SQL.v7 );
3218
3219
3220 -- define column
3221 --Perf bug 5218515
3222 DBMS_SQL.Define_Array(v_CursorID, 1, v_delivery_detail_id_tbl ,l_fetch_count ,l_start_index);
3223
3224 --
3225 -- Bug 5624475 and ECO 5676263
3226 IF p_select_criteria.delivery_lines_status IN ('K', 'S') THEN
3227 l_line_status := 'S';
3228 ELSE
3229 l_line_status := p_select_criteria.delivery_lines_status;
3230 END IF;
3231 --
3232 IF l_debug_on THEN
3233 WSH_DEBUG_SV.log(l_module_name, ' l_line_status', l_line_status);
3234 WSH_DEBUG_SV.log(l_module_name, 'p_select_criteria.delivery_lines_status',
3235 p_select_criteria.delivery_lines_status);
3236 END IF;
3237 --
3238 IF p_select_criteria.delivery_lines_status IS NOT NULL AND
3239 p_select_criteria.delivery_lines_status <> 'ALL' THEN
3240 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_released_status', l_line_status); -- Bug 5624475 and ECO 5676263
3241 END IF;
3242
3243 -- bind the variables for organization id
3244 IF p_select_criteria.organization_id IS NOT NULL THEN
3245 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_organization_id', p_select_criteria.organization_id);
3246 END IF;
3247
3248 IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
3249 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_scheduled_ship_date_lo', l_scheduled_ship_date_lo);
3250 END IF;
3251
3252 IF p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
3253 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_scheduled_ship_date_hi', l_scheduled_ship_date_hi);
3254 END IF;
3255
3256 IF p_select_criteria.source_code IS NOT NULL
3257 AND p_select_criteria.source_code <> 'ALL' THEN
3258 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_source_code', p_select_criteria.source_code);
3259 END IF;
3260
3261 IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
3262 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_to_loc_id', p_select_criteria.ship_to_loc_id);
3263 END IF;
3264
3265 IF p_select_criteria.customer_id IS NOT NULL THEN
3266 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_customer_id', to_number(SubStr(p_select_criteria.customer_id,3))); -- RTV changes
3267 END IF;
3268
3269 IF p_select_criteria.ship_method_code IS NOT NULL THEN
3270 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_method_code', p_select_criteria.ship_method_code);
3271 END IF;
3272
3273 -- LSP PROJECT : begin
3274 IF p_select_criteria.client_id IS NOT NULL THEN
3275 DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_client_id', p_select_criteria.client_id);
3276 END IF;
3277 -- LSP PROJECT : end
3278
3279 -- execute the cursor
3280 v_ignore := DBMS_SQL.Execute(v_CursorID);
3281
3282 -- fetching the rows in bulk.
3283 --Perf bug 5218515
3284 Loop
3285 l_records_fetched := dbms_sql.fetch_rows(v_CursorID) ;
3286 dbms_sql.column_value(v_CursorID,1,v_delivery_detail_id_tbl);
3287 IF l_debug_on THEN
3288 WSH_DEBUG_SV.logmsg(l_module_name,' Records Fetched in this pass '|| l_records_fetched);
3289 WSH_DEBUG_SV.logmsg(l_module_name,' l_fetch_count '|| l_fetch_count);
3290 WSH_DEBUG_SV.logmsg(l_module_name,' Record count in v_delivery_detail_id_tbl '||
3291 v_delivery_detail_id_tbl.count);
3292 END IF;
3293 Exit when l_records_fetched <> l_fetch_count ;
3294 End Loop;
3295
3296 If v_delivery_detail_id_tbl.count > 0 then
3297 For i in 1..v_delivery_detail_id_tbl.count
3298 loop
3299 x_selected_det_tbl(i) := v_delivery_detail_id_tbl(i);
3300 If l_debug_on THEN
3301 WSH_DEBUG_SV.logmsg(l_module_name,' Fetched delivery line '|| to_char(x_selected_det_tbl(i)));
3302 End If;
3303 End Loop;
3304 End If;
3305
3306 IF v_cursorID <> 0 THEN
3307 If l_debug_on THEN
3308 WSH_DEBUG_SV.logmsg(l_module_name,' Before closing cursor = '||v_cursorID );
3309 End If;
3310
3311 DBMS_SQL.Close_Cursor(v_cursorID);
3312
3313 If l_debug_on THEN
3314 WSH_DEBUG_SV.logmsg(l_module_name,' After closing cursor = '||v_cursorID );
3315 End If;
3316 END IF;
3317 -- done fetching
3318 -- print debug messages
3319
3320 IF l_debug_on THEN
3321 WSH_DEBUG_SV.logmsg(l_module_name, to_char(x_selected_det_tbl.count)||' delivery lines fetched to be processed');
3322 WSH_DEBUG_SV.pop(l_module_name);
3323 END IF;
3324
3325 EXCEPTION
3326
3327 WHEN others THEN
3328 -- if the cursor is still open then close the cursor
3329 IF v_cursorID <> 0 THEN
3330 DBMS_SQL.Close_Cursor(v_cursorID);
3331 END IF;
3335 WSH_DEBUG_SV.pop(l_module_name);
3332 wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
3333 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3334 IF l_debug_on THEN
3336 END IF;
3337
3338 END Select_Delivery_Lines;
3339
3340 -- -----------------------------------------------------------------------
3341 -- Name
3342 -- PROCEDURE Process_Delivery_Lines_Batch(private within the package)
3343 -- Purpose
3344 -- This procedure works on a list of delivery lines, append these lines to existing
3345 -- deliveries and autocreate deliveries for the lines not appended.
3346 --
3347 -- Input Parameters:
3348 --
3349 -- p_selected_det_tbl List of delivery lines selected
3350 -- p_append_flag Indicate if it needs to append deliveries or not
3351 -- 'Y': append deliveries, 'SP': check the value in shipping parameters
3352 -- p_ac_del_criteria Indicate if deliveries can across order or not
3353 -- 'Y': Within an Order
3354 -- 'N': Across Orders
3355 -- Output Parameters:
3356 -- x_appended_det_num Number of delivery lines appended
3357 -- x_autocreate_del_det_num Number of delivery lines submitted to autocreate deliveries
3358 -- x_new_del_num Number of deliveries created
3359 -- x_msg_count Message count
3360 -- x_msg_data Message data
3361 -- x_return_status Return status
3362 ---- ----------------------------------------------------------------------
3363
3364 PROCEDURE Process_Delivery_Lines_Batch(
3365 p_selected_det_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
3366 p_append_flag IN VARCHAR2,
3367 p_ac_del_criteria IN VARCHAR2,
3368 x_appended_det_num OUT NOCOPY NUMBER,
3369 x_autocreate_del_det_num OUT NOCOPY NUMBER,
3370 x_appended_del_num OUT NOCOPY NUMBER,
3371 x_new_del_num OUT NOCOPY NUMBER,
3372 x_msg_count OUT NOCOPY NUMBER,
3373 x_msg_data OUT NOCOPY VARCHAR2,
3374 x_return_status OUT NOCOPY VARCHAR2) IS
3375
3376 l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
3377 l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
3378 l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
3379 l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3380 l_append_flag VARCHAR2(1);
3381 l_ac_del_criteria VARCHAR2(1);
3382 l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
3383 l_action_out_rec wsh_glbl_var_strct_grp.dd_action_out_rec_type;
3384 l_msg_count NUMBER := 0;
3385 l_msg_data VARCHAR2(32767) := NULL;
3386 l_return_status VARCHAR2(1);
3387 l_warning_num NUMBER := 0;
3388 l_number_of_warnings NUMBER := 0;
3389 l_number_of_errors NUMBER := 0;
3390 --
3391 l_debug_on BOOLEAN;
3392 --
3393 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Delivery_Lines_Batch';
3394 --
3395 append_to_deliveries_failed EXCEPTION;
3396 autocreate_delivery_failed EXCEPTION;
3397
3398 BEGIN
3399 SAVEPOINT BEFORE_PROCESS_DELIVERIES;
3400 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3401 --
3402 IF l_debug_on IS NULL
3403 THEN
3404 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3405 END IF;
3406 --
3407 IF l_debug_on THEN
3408 WSH_DEBUG_SV.push(l_module_name);
3409 END IF;
3410
3411 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3412 x_appended_det_num := 0;
3413 x_autocreate_del_det_num := 0;
3414 x_new_del_num := 0;
3415 l_appended_det_tbl.delete;
3416 l_unappended_det_tbl.delete;
3417
3418 -- print input parameter if debug is turned on
3419 IF l_debug_on THEN
3420 WSH_DEBUG_SV.logmsg(l_module_name,'Input Parameters:',WSH_DEBUG_SV.C_PROC_LEVEL);
3421 WSH_DEBUG_SV.logmsg(l_module_name,' p_append_flag: '|| p_append_flag,WSH_DEBUG_SV.C_PROC_LEVEL);
3422 WSH_DEBUG_SV.logmsg(l_module_name,' p_ac_del_criteria: '|| p_ac_del_criteria,WSH_DEBUG_SV.C_PROC_LEVEL);
3423 END IF;
3424
3425 -- set append flag
3426 IF p_append_flag = 'SP' THEN
3427 l_append_flag := NULL;
3428 ELSE
3429 l_append_flag := p_append_flag;
3430 END IF;
3431
3432 -- set autocreate delivery criteria
3433 IF p_ac_del_criteria = 'SP' THEN
3434 l_ac_del_criteria := NULL;
3435 ELSE
3436 l_ac_del_criteria := p_ac_del_criteria;
3437 END IF;
3438 --Bug 10320421 removed condition. now Append_to_Deliveries() will be called in all cases.
3439 WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries(
3440 p_delivery_detail_tbl => p_selected_det_tbl,
3441 p_append_flag => l_append_flag,
3442 p_group_by_header => l_ac_del_criteria,
3443 p_commit => FND_API.G_FALSE,
3444 p_lock_rows => FND_API.G_TRUE,
3445 p_check_fte_compatibility => FND_API.G_TRUE,
3446 x_appended_det_tbl => l_appended_det_tbl,
3447 x_unappended_det_tbl => l_unappended_det_tbl,
3448 x_appended_del_tbl => l_appended_del_tbl,
3449 x_return_status => l_return_status);
3450
3451 FND_MSG_PUB.Count_And_Get
3452 (
3453 p_count => l_msg_count,
3454 p_data => l_msg_data,
3455 p_encoded => FND_API.G_FALSE
3456 );
3457 x_appended_det_num := l_appended_det_tbl.count;
3458 x_msg_count := l_msg_count;
3459 x_msg_data := l_msg_data;
3460
3461 IF l_debug_on THEN
3465 -- handle return status from WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries
3462 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries: '|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
3463 END IF;
3464
3466 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3467 raise append_to_deliveries_failed;
3468 --Bug 10320421 following code commented. same as done in pick release while calling Append_to_Deliveries()
3469 /*ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3470 l_warning_num := l_warning_num + 1;
3471 */
3472 END IF;
3473
3474 IF l_unappended_det_tbl.count > 0 THEN
3475 IF l_debug_on THEN
3476 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
3477 END IF;
3478
3479 -- calling wsh_interface_grp.delivery_detail_action with action code 'AUTOCREATE-DEL'
3480 l_action_prms.caller := 'WSH_DEPS';
3481 l_action_prms.action_code := 'AUTOCREATE-DEL';
3482 l_action_prms.group_by_header_flag := l_ac_del_criteria;
3483
3484 wsh_interface_grp.delivery_detail_action(
3485 p_api_version_number => 1.0,
3486 p_init_msg_list => FND_API.G_FALSE,
3487 p_commit => FND_API.G_FALSE,
3488 x_return_status => l_return_status,
3489 x_msg_count => l_msg_count,
3490 x_msg_data => l_msg_data,
3491 p_detail_id_tab => l_unappended_det_tbl,
3492 p_action_prms => l_action_prms ,
3493 x_action_out_rec => l_action_out_rec);
3494
3495 IF l_debug_on THEN
3496 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);
3497 END IF;
3498
3499 x_autocreate_del_det_num := l_unappended_det_tbl.count;
3500 x_appended_del_num := l_appended_del_tbl.count;
3501 x_new_del_num := l_action_out_rec.delivery_id_tab.count;
3502 x_msg_count := x_msg_count + l_number_of_warnings + l_number_of_errors;
3503 x_msg_data := x_msg_data || l_msg_data;
3504
3505 -- handle return status from wsh_interface_grp.delivery_detail_action
3506 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3507 raise autocreate_delivery_failed;
3508 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3509 l_warning_num := l_warning_num + 1;
3510 END IF;
3511
3512 IF l_warning_num > 0 THEN
3513 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3514 END IF;
3515
3516
3517 END IF;
3518
3519 COMMIT;
3520
3521
3522 IF l_debug_on THEN
3523 WSH_DEBUG_SV.pop(l_module_name);
3524 END IF;
3525
3526 EXCEPTION
3527
3528 WHEN append_to_deliveries_failed THEN
3529 ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3530 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3531 IF l_debug_on THEN
3532 WSH_DEBUG_SV.logmsg(l_module_name,'APPEND_TO_DELIVERIES_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3533 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APPEND_TO_DELIVERIES_FAILED');
3534 END IF;
3535
3536 WHEN autocreate_delivery_failed THEN
3537 ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3538 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3539 IF l_debug_on THEN
3540 WSH_DEBUG_SV.logmsg(l_module_name,'AUTOCREATE_DELIVERY_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3541 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:AUTOCREATE_DELIVERY_FAILED');
3542 END IF;
3543
3544 WHEN OTHERS THEN
3545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3546 ROLLBACK TO BEFORE_PROCESS_DELIVERIES;
3547 wsh_util_core.add_message(x_return_status, l_module_name);
3548 WSH_UTIL_CORE.default_handler('WSH_BATCH_PROCESS.Process_Delivery_Lines_Batch');
3549 FND_MSG_PUB.Count_And_Get
3550 (
3551 p_count => x_msg_count,
3552 p_data => x_msg_data,
3553 p_encoded => FND_API.G_FALSE
3554 );
3555
3556 IF l_debug_on THEN
3557 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3558 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3559 END IF;
3560
3561 END Process_Delivery_Lines_Batch;
3562
3563 -- -----------------------------------------------------------------------
3564 -- Name
3565 -- PROCEDURE Process_Deliveries_SRS
3566 -- Purpose
3567 -- This procedure is the entry procedure of Process Delivery SRS program
3568 -- It creates consolidated trips for the deliveries if p_entity_type = 'D'
3569 -- (Deliveries Only)
3570 -- and create deliveries for the delivery lines if p_entity_type = 'L' (Delivery Lines Only)
3571 --
3572 -- Input Parameters:
3573 --
3574 -- p_entity_type Entity type, either 'D'(Deliveries Only) or 'L'(Delivery Lines Only)
3575 -- p_delivery_lines_status Delivery Lines selection criteria: Delivery line status
3576 -- p_deliveries_status Deliveries selection criteria: Delivery Status
3577 -- p_scheduled_ship_date_lo Delivery Lines selection criteria: Scheduled Ship Date Start
3578 -- p_scheduled_ship_date_hi Delivery Lines selection criteria: Scheduled Ship Date End
3579 -- p_source_system Delivery Lines selection criteria: Source System
3580 -- p_pickup_date_lo Deliveries selection criteria: Pickup Date Start
3584 -- p_client_id Selection criteria for both delivery lines and deliveries ,Client for LSP --Modified R12.1.1 LSP PROJECT
3581 -- p_pickup_date_hi Deliveries selection criteria: Pickup Date End
3582 -- p_dropoff_date_lo Deliveries selection criteria: Dropoff Date Start
3583 -- p_dropoff_date_hi Deliveries selection criteria: Dropoff Date End
3585 -- p_organization_id Selection criteria for both delivery lines and deliveries
3586 -- p_customer_id Selection criteria for both delivery lines and deliveries
3587 -- p_ship_to_loc_id Selection criteria for both delivery lines and deliveries
3588 -- p_ship_method_code Selection criteria for both delivery lines and deliveries
3589 -- p_autocreate_deliveries Delivery Lines Only: 'Y': autocreate deliveries, 'SP': Shipping Parameter
3590 -- p_ac_del_criteria Delivery Lines Only: 'Y': Within An order, 'N': Across Order, 'SP': Shipping Parameter
3591 -- p_append_deliveries Delivery Lines Only: 'Y': append deliveries,'N': do not append deliveries 'SP':Shipping Parameter
3592 -- p_grp_ship_method Deliveries Only: 'Y': group deliveries by ship method 'N': do not use ship method to group deliveries
3593 -- p_grp_ship_from Deliveries Only: 'Y': group deliveries by ship from 'N': do not use ship from to group deliveries
3594 -- p_max_number Deliveries Only: Max number of deliveries per trip
3595 -- p_log_level 0: debug log off, 1: debug log on
3596 --
3597 -- Output Parameters:
3598 -- errbuf Starndard parameter
3599 -- retcode Starndard parameter
3600 ---- ----------------------------------------------------------------------
3601 --
3602 PROCEDURE Process_Deliveries_SRS(
3603 errbuf OUT NOCOPY VARCHAR2,
3604 retcode OUT NOCOPY VARCHAR2,
3605 p_entity_type IN VARCHAR2,
3606 p_delivery_lines_status IN VARCHAR2,
3607 p_deliveries_status IN VARCHAR2,
3608 p_scheduled_ship_date_lo IN VARCHAR2,
3609 p_scheduled_ship_date_hi IN VARCHAR2,
3610 p_source_system IN VARCHAR2,
3611 p_pickup_date_lo IN VARCHAR2,
3612 p_pickup_date_hi IN VARCHAR2,
3613 p_dropoff_date_lo IN VARCHAR2,
3614 p_dropoff_date_hi IN VARCHAR2,
3615 p_deploy_mode IN VARCHAR2, -- Modified R12.1.1 LSP PROJECT
3616 p_client_id IN NUMBER, --Modified R12.1.1 LSP PROJECT
3617 p_organization_id IN NUMBER,
3618 p_customer_id IN VARCHAR2,
3619 p_ship_to_loc_id IN NUMBER,
3620 p_ship_method_code IN VARCHAR2,
3621 p_autocreate_deliveries IN VARCHAR2,
3622 p_ac_del_criteria IN VARCHAR2,
3623 p_append_deliveries IN VARCHAR2,
3624 p_grp_ship_method IN VARCHAR2,
3625 p_grp_ship_from IN VARCHAR2,
3626 p_max_del_number IN NUMBER,
3627 p_log_level IN NUMBER ) IS
3628
3629
3630 --TCA View Changes Start
3631 --RTV changes
3632 CURSOR get_customer_name(c_customer_id varchar2) IS
3633 SELECT substrb ( party.party_name, 1, 50 ) customer_name
3634 FROM hz_parties Party, hz_cust_accounts cust_acct
3635 WHERE cust_acct.party_id = party.party_id AND
3636 cust_acct.cust_account_id = to_number(SubStr(c_customer_id,3))
3637 AND SubStr(c_customer_id,1,1) = 'C'
3638 UNION
3639 SELECT substrb ( party.party_name, 1, 50 ) customer_name
3640 FROM hz_parties Party, po_vendors pov
3641 WHERE pov.party_id = party.party_id
3642 AND pov.vendor_id = to_number(SubStr(c_customer_id,3))
3643 AND SubStr(c_customer_id,1,1) = 'V';
3644 --RTV changes --TCA View Changes end
3645
3646 CURSOR get_appending_limit (c_organization_id NUMBER) IS
3647 SELECT appending_limit,autocreate_del_orders_flag --Bug 10353854
3648 FROM wsh_shipping_parameters
3649 WHERE organization_id = c_organization_id;
3650
3651 l_completion_status VARCHAR2(30);
3652 l_error_code NUMBER;
3653 l_error_text VARCHAR2(2000);
3654 l_log_level NUMBER := 0;
3655 l_return_status VARCHAR2(30) := NULL;
3656 l_temp BOOLEAN;
3657 l_debug_on BOOLEAN;
3658 l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
3659 l_appended_det_num NUMBER;
3660 l_appended_del_num NUMBER;
3661 l_autocreate_del_det_num NUMBER;
3662 l_selected_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
3663 l_msg_count NUMBER;
3664 l_msg_data VARCHAR2(32767);
3665 l_new_del_num NUMBER;
3666 l_del_num NUMBER;
3667 l_del_grouped_num NUMBER;
3668 l_trip_num NUMBER;
3669 l_pickup_date_lo DATE;
3670 l_pickup_date_hi DATE;
3671 l_dropoff_date_lo DATE;
3672 l_dropoff_date_hi DATE;
3673 l_scheduled_date_lo DATE;
3674 l_scheduled_date_hi DATE;
3675 l_autocreate_deliveries VARCHAR2(30);
3676 l_append_deliveries VARCHAR2(30);
3677 l_ac_del_criteria VARCHAR2(30);
3678 l_deliveries_status VARCHAR2(30);
3679 l_grp_ship_method VARCHAR2(1);
3680 l_grp_ship_from VARCHAR2(1);
3681 l_appending_limit VARCHAR2(1);
3682 l_sp_ac_del_criteria VARCHAR2(1); --Bug 10353854
3683 l_msg_string VARCHAR2(80);
3684 l_msg_date DATE;
3685 l_max_del_number NUMBER;
3686 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Deliveries_SRS';
3687 l_buffer VARCHAR2(4000);
3688 l_index_out NUMBER;
3689 WSH_SELECT_ERR EXCEPTION;
3690 Process_Delivery_Lines_ERR EXCEPTION;
3691 Create_Consolidated_Trips_ERR EXCEPTION;
3692 Parameters_ERR EXCEPTION;
3693
3694 l_processed number ;
3695 l_inner_loop_count number ;
3696 l_selected_det_tbl_tmp WSH_UTIL_CORE.Id_Tab_Type;
3697 g_selected_det_count number :=0;
3698 g_appended_det_num number :=0;
3699 g_appended_del_num number :=0;
3700 g_autocreate_del_det_num number :=0;
3701 g_new_del_num number :=0;
3702
3703 l_client_id NUMBER; -- Modified R12.1.1 LSP PROJECT
3704 l_client_code VARCHAR2(10); -- Modified R12.1.1 LSP PROJECT
3705
3706 BEGIN
3707 --
3708 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3709 --
3710 IF l_debug_on IS NULL THEN
3711 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3712 END IF;
3713 --
3714 IF l_debug_on THEN
3715 wsh_debug_sv.push(l_module_name);
3716 wsh_debug_sv.logmsg(l_module_name,'Input Parameters: ');
3717 wsh_debug_sv.logmsg(l_module_name,'=================================');
3718 wsh_debug_sv.log(l_module_name,'p_entity_type', p_entity_type);
3719 wsh_debug_sv.log(l_module_name,'p_append_deliveries',p_append_deliveries);
3720 wsh_debug_sv.log(l_module_name,'p_deliveries_status',p_deliveries_status);
3724 wsh_debug_sv.log(l_module_name,'p_pickup_date_lo',p_pickup_date_lo);
3721 wsh_debug_sv.log(l_module_name,'p_scheduled_ship_date_lo',p_scheduled_ship_date_lo);
3722 wsh_debug_sv.log(l_module_name,'p_scheduled_ship_date_hi',p_scheduled_ship_date_hi);
3723 wsh_debug_sv.log(l_module_name,'p_source_system',p_source_system);
3725 wsh_debug_sv.log(l_module_name,'p_pickup_date_hi',p_pickup_date_hi);
3726 wsh_debug_sv.log(l_module_name,'p_dropoff_date_lo',p_dropoff_date_lo);
3727 wsh_debug_sv.log(l_module_name,'p_dropoff_date_hi',p_dropoff_date_hi);
3728 wsh_debug_sv.log(l_module_name,'p_deploy_mode',p_deploy_mode);
3729 wsh_debug_sv.log(l_module_name,'p_client_id',p_client_id);
3730 wsh_debug_sv.log(l_module_name,'p_organization_id',p_organization_id);
3731 wsh_debug_sv.log(l_module_name,'p_customer_id',p_customer_id);
3732 wsh_debug_sv.log(l_module_name,'p_ship_to_loc_id',p_ship_to_loc_id);
3733 wsh_debug_sv.log(l_module_name,'p_ship_method_code',p_ship_method_code);
3734 wsh_debug_sv.log(l_module_name,'p_autocreate_deliveries',p_autocreate_deliveries);
3735 wsh_debug_sv.log(l_module_name,'p_ac_del_criteria',p_ac_del_criteria);
3736 wsh_debug_sv.log(l_module_name,'p_append_deliveries',p_append_deliveries);
3737 wsh_debug_sv.log(l_module_name,'p_grp_ship_method',p_grp_ship_method);
3738 wsh_debug_sv.log(l_module_name,'p_grp_ship_from',p_grp_ship_from);
3739 wsh_debug_sv.log(l_module_name,'p_max_del_number',p_max_del_number);
3740 wsh_debug_sv.log(l_module_name,'p_log_level',p_log_level);
3741 END IF;
3742
3743
3744
3745
3746 -- set the completion status to NORMAL
3747 l_completion_status := 'NORMAL';
3748
3749 IF p_log_level IS NULL THEN
3750 l_log_level := 0;
3751 ELSE
3752 l_log_level := p_log_level;
3753 END IF;
3754
3755 -- enable printing of the concurrent request
3756 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
3757 -- set the log level for the whole session
3758 WSH_UTIL_CORE.Set_Log_Level(l_log_level);
3759
3760
3761 FND_MESSAGE.SET_NAME('WSH','WSH_PD_PARM');
3762 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3763 FND_FILE.put_line(FND_FILE.output,'====================');
3764
3765 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PROCESSED_ENTITIES');
3769 FND_MESSAGE.SET_TOKEN('ENTITY_TYPE', l_msg_string);
3766 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3767 p_lookup_code => p_entity_type,
3768 p_lookup_type => 'WSH_PD_ENTITY' );
3770 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3771
3772
3773 -- print required input parameters
3774 WSH_UTIL_CORE.PrintMsg('Input Parameters: ' );
3775 WSH_UTIL_CORE.PrintMsg(' Processed Entities: '|| p_entity_type);
3776
3777 IF p_entity_type = 'D' THEN
3778 --
3779 IF WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
3780 FND_FILE.put_line(FND_FILE.output, ' ');
3781 FND_FILE.put_line(FND_FILE.output, ' ');
3782 -- print the summary results of auto create trips action
3783 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_TRIP_CONSOLIDATION');
3784 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3785 WSH_UTIL_CORE.PrintMsg('Trip Consolidation is disabled because Transportation Planning is installed. ');
3786 ELSE
3787 --
3788 l_pickup_date_lo := fnd_date.canonical_to_date(p_pickup_date_lo);
3789 l_pickup_date_hi := fnd_date.canonical_to_date(p_pickup_date_hi);
3790 l_dropoff_date_lo := fnd_date.canonical_to_date(p_dropoff_date_lo);
3791 l_dropoff_date_hi := fnd_date.canonical_to_date(p_dropoff_date_hi);
3792
3793 -- bug 3319789
3794 IF p_scheduled_ship_date_lo is not NULL OR
3795 p_scheduled_ship_date_hi is not NULL THEN
3796 FND_MESSAGE.SET_NAME('WSH', 'WSH_SCHD_DATE_NOT_ALLOWED');
3797 FND_FILE.put_line(FND_FILE.output,' ');
3798 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3799 WSH_UTIL_CORE.PrintMsg('Scheduled Ship Date parameters are not applicable for entity Deliveries Only. Please do not enter Sheduled Ship Date range when selecting entity Deliveries Only.');
3800 raise Parameters_ERR;
3801 END IF;
3802
3803 -- Deliveries Only, auto create trips
3804
3805 -- default value : Set Deliveries Status to 'BOTH'
3806 IF p_deliveries_status IS NULL THEN
3807 l_deliveries_status := 'BOTH';
3808 ELSE
3809 l_deliveries_status := p_deliveries_status;
3810 END IF;
3811
3812 -- default value : Set Group Deliveries by Ship Method to 'Y'
3813 IF p_grp_ship_method IS NULL THEN
3814 l_grp_ship_method := 'Y';
3815 ELSE
3816 l_grp_ship_method := p_grp_ship_method;
3817 END IF;
3818
3819 -- default value : Set Group Deliveries by Ship From Organization to 'Y'
3820 IF p_grp_ship_from IS NULL THEN
3821 l_grp_ship_from := 'Y';
3822 ELSE
3823 l_grp_ship_from := p_grp_ship_from;
3824 END IF;
3825
3826 -- default value : Set Maximum Number of Deliveries per Trip to 50
3827 IF p_max_del_number IS NULL THEN
3828 l_max_del_number := 50;
3829 ELSE
3830 l_max_del_number := p_max_del_number;
3831 END IF;
3832
3833
3834 -- print Input Parameters
3835
3836 IF l_deliveries_status IS NOT NULL THEN
3837 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_STATUS');
3838 l_msg_string := NULL;
3839 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3840 p_lookup_code => p_deliveries_status,
3841 p_lookup_type => 'WSH_PD_DEL_STATUS' );
3842 FND_MESSAGE.SET_TOKEN('DELIVERIES_STATUS', l_msg_string);
3843 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3844 WSH_UTIL_CORE.PrintMsg(' Deliveries Status: '|| l_deliveries_status);
3845 END IF;
3846
3847 IF p_pickup_date_lo IS NOT NULL THEN
3848 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_LO');
3849 FND_MESSAGE.SET_TOKEN('PICKUP_DATE_LO', to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3850 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3851 WSH_UTIL_CORE.PrintMsg(' Pick Up Date Start: '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3852 END IF;
3853
3854 IF p_pickup_date_hi IS NOT NULL THEN
3855 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_HI');
3856 FND_MESSAGE.SET_TOKEN('PICKUP_DATE_HI', to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3857 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3858 WSH_UTIL_CORE.PrintMsg(' Pick Up Date End: '|| to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3859 END IF;
3860
3861 -- bug 3332670
3862 IF p_pickup_date_hi is NOT NULL and
3863 p_pickup_date_lo is NOT NULL THEN
3864 IF l_pickup_date_hi < l_pickup_date_lo THEN
3865 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_PICKUP_DATE_RANGE');
3866 FND_MESSAGE.SET_TOKEN('PICKUP_DATE_LO', to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3867 FND_MESSAGE.SET_TOKEN('PICKUP_DATE_HI', to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3868 FND_FILE.put_line(FND_FILE.output,' ');
3869 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3870 WSH_UTIL_CORE.PrintMsg('The end date of Pick Up Date range '||to_char(l_pickup_date_hi, 'DD-MON-YYYY HH24:MI:SS')||' should not precede the start date '|| to_char(l_pickup_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3871 raise Parameters_ERR;
3872 END IF;
3873 END IF;
3874
3875 IF p_dropoff_date_lo IS NOT NULL THEN
3876 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_LO');
3877 FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_LO', to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3881
3878 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3879 WSH_UTIL_CORE.PrintMsg(' Drop Off Date Start: '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3880 END IF;
3882 IF p_dropoff_date_hi IS NOT NULL THEN
3883 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_HI');
3884 FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_HI', to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3885 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3886 WSH_UTIL_CORE.PrintMsg(' Drop Off Date End: '|| to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3887 END IF;
3888
3889 -- bug 3332670
3890 IF p_dropoff_date_hi is NOT NULL and
3891 p_dropoff_date_lo is NOT NULL THEN
3892 IF l_dropoff_date_hi < l_dropoff_date_lo THEN
3893 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DROPOFF_DATE_RANGE');
3894 FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_LO', to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3895 FND_MESSAGE.SET_TOKEN('DROPOFF_DATE_HI', to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
3896 FND_FILE.put_line(FND_FILE.output,' ');
3897 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3898 WSH_UTIL_CORE.PrintMsg('The end date of Drop Off Date range '||to_char(l_dropoff_date_hi, 'DD-MON-YYYY HH24:MI:SS')||' should not precede the start date '|| to_char(l_dropoff_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
3899 raise Parameters_ERR;
3900 END IF;
3901 END IF;
3902
3903 /*Modified R12.1.1 LSP PROJECT*/
3904 IF p_client_id IS NOT NULL THEN
3905 l_client_id := p_client_id;
3906 wms_deploy.get_client_details(
3907 x_client_id => l_client_id,
3908 x_client_name => l_msg_string,
3909 x_client_code => l_client_code,
3910 x_return_status => l_return_status);
3911
3912 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3913 --{
3914 IF l_debug_on THEN
3915 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in WMS_DEPLOY.GET_CLIENT_DETAILS');
3916 END IF;
3917 END IF;
3918
3919 FND_MESSAGE.SET_NAME('WSH', 'WSH_CLIENT');
3920 FND_MESSAGE.SET_TOKEN('CLIENT_NAME', l_msg_string);
3921 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3922 WSH_UTIL_CORE.PrintMsg(' Client ID: '|| (p_client_id));
3923 END IF;
3924 /*Modified R12.1.1 LSP PROJECT*/
3925
3926 IF p_organization_id IS NOT NULL THEN
3927 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_ORGANIZATION');
3928 FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
3929 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3930 WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| to_char(p_organization_id));
3931 END IF;
3932
3933 IF p_customer_id IS NOT NULL THEN
3934
3935 OPEN get_customer_name(p_customer_id);
3936 FETCH get_customer_name INTO l_msg_string;
3937 CLOSE get_customer_name;
3938
3939 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_CUSTOMER');
3940 FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_msg_string);
3941 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3942 --bug fix 3286811
3943 WSH_UTIL_CORE.PrintMsg(' Customer ID: '||(SubStr(p_customer_id,3))); --RTV changes
3944 END IF;
3945
3946 IF p_ship_to_loc_id IS NOT NULL THEN
3947 l_msg_string := substrb(WSH_UTIL_CORE.Get_Location_Description(
3948 p_location_id => p_ship_to_loc_id,
3949 p_format => 'NEW UI CODE'),
3950 1, 80);
3951
3952 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_TO');
3953 FND_MESSAGE.SET_TOKEN('SHIP_TO', l_msg_string);
3954 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3955 WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_ship_to_loc_id));
3956 END IF;
3957
3958 IF p_ship_method_code IS NOT NULL THEN
3959
3960 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SHIP_METHOD');
3961 l_msg_string := NULL;
3962 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3963 p_lookup_code => p_ship_method_code,
3964 p_lookup_type => 'SHIP_METHOD' );
3965 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_msg_string);
3966 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3967 WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_ship_method_code);
3968 END IF;
3969
3970 IF l_grp_ship_method IS NOT NULL THEN
3971 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_GRP_SHIP_METHOD');
3972 l_msg_string := NULL;
3973 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3974 p_lookup_code => l_grp_ship_method,
3975 p_lookup_type => 'YES_NO' );
3976 FND_MESSAGE.SET_TOKEN('GRP_SHIP_METHOD', l_msg_string);
3977 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3978 WSH_UTIL_CORE.PrintMsg(' Group Deliveries by Ship Method: '|| l_grp_ship_method);
3979 END IF;
3980
3981 IF l_grp_ship_from IS NOT NULL THEN
3985 p_lookup_code => l_grp_ship_from,
3982 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_GRP_SHIP_FROM');
3983 l_msg_string := NULL;
3984 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
3986 p_lookup_type => 'YES_NO' );
3987 FND_MESSAGE.SET_TOKEN('GRP_SHIP_FROM', l_msg_string);
3988 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3989 WSH_UTIL_CORE.PrintMsg(' Group Deliveries by Ship from Organization: '|| l_grp_ship_from);
3990 END IF;
3991
3992 IF l_max_del_number IS NOT NULL THEN
3993 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MAX_DEL_NUMBER');
3994 FND_MESSAGE.SET_TOKEN('MAX_DEL_NUMBER', to_char(l_max_del_number));
3995 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
3996 WSH_UTIL_CORE.PrintMsg(' Maximum Number of Deliveries per Trip: '|| to_char(l_max_del_number));
3997 END IF;
3998
3999 IF l_log_level IS NOT NULL THEN
4000 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_LOG_LEVEL');
4001 FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(l_log_level));
4002 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4003 WSH_UTIL_CORE.PrintMsg(' Log Level: '|| l_log_level );
4004 END IF;
4005
4006 IF l_debug_on THEN
4007 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips',WSH_DEBUG_SV.C_PROC_LEVEL);
4008 END IF;
4009
4010 -- calling WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips to create trips
4011 WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips(
4012 p_deliv_status => l_deliveries_status,
4013 p_pickup_start => l_pickup_date_lo,
4014 p_pickup_end => l_pickup_date_hi,
4015 p_dropoff_start => l_dropoff_date_lo,
4016 p_dropoff_end => l_dropoff_date_hi,
4017 p_client_id => p_client_ID, --Modified R12.1.1 LSP PROJECT
4018 p_ship_from_org_id => p_organization_id,
4019 p_customer_id => p_customer_id,
4020 p_ship_to_location => p_ship_to_loc_id,
4021 p_ship_method_code => p_ship_method_code,
4022 p_grp_ship_method => l_grp_ship_method,
4023 p_grp_ship_from => l_grp_ship_from,
4024 p_max_num_deliveries => l_max_del_number,
4025 x_TotDeliveries => l_del_num,
4026 x_SuccessDeliv => l_del_grouped_num,
4027 x_Trips => l_trip_num,
4028 x_return_status => l_return_status);
4029
4030 -- print the summary results of auto create trips action
4031 FND_FILE.put_line(FND_FILE.output,' ');
4032 FND_FILE.put_line(FND_FILE.output,' ');
4033 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SUMMARY');
4034 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4035 FND_FILE.put_line(FND_FILE.output,'====================');
4036
4037 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_SELECTED');
4038 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(l_del_num));
4039 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4040
4041 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_GROUPED');
4042 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(l_del_grouped_num));
4043 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4044
4045 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_TRIPS_CREATED');
4046 FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRIPS', to_char(l_trip_num));
4047 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4048
4049 WSH_UTIL_CORE.PrintDateTime;
4050 WSH_UTIL_CORE.PrintMsg('Summary: ');
4051 WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
4052 WSH_UTIL_CORE.PrintMsg(to_char(l_del_grouped_num)||' deliveries have been grouped to new trips');
4053 WSH_UTIL_CORE.PrintMsg(to_char(l_trip_num)||' trips have been successfully created');
4054
4055 -- print return status of autocreate trips
4056 IF l_debug_on THEN
4057 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from WSH_TRIP_CONSOLIDATION.Create_Consolidated_Trips is '|| l_return_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
4058 END IF;
4059
4060 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4061 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4062 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_ERROR');
4063 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4064 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_WARNING');
4065 END IF;
4066
4067 FND_FILE.put_line(FND_FILE.output,' ');
4068 FND_FILE.put_line(FND_FILE.output,' ');
4069 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4070
4071 -- print warning or error messages Process_Delivery_Lines_Batch
4072 l_msg_count := FND_MSG_PUB.count_msg;
4073
4074 IF l_msg_count > 0 THEN
4075 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
4076 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4077 FND_FILE.put_line(FND_FILE.output,'====================');
4078 WSH_UTIL_CORE.PrintMsg('List of Messages: ');
4079 WSH_UTIL_CORE.PrintMsg('====================');
4080 FOR i in 1..l_msg_count LOOP
4081 FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4082 p_msg_index => i,
4083 p_data => l_buffer,
4084 p_msg_index_out => l_index_out);
4085 IF l_buffer IS NOT NULL THEN
4086 FND_FILE.put_line(FND_FILE.output,substrb(l_buffer,1,2000));
4087 WSH_UTIL_CORE.PrintMsg(substrb(l_buffer,1,2000));
4088 END IF;
4089 END LOOP;
4090
4091 END IF;
4092 raise Create_Consolidated_Trips_ERR;
4093 END IF;
4094
4095 END IF; -- TP is not installed
4096 ELSE
4097
4098 IF l_debug_on THEN
4099 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Select_Deliveries_Lines ',WSH_DEBUG_SV.C_PROC_LEVEL);
4100 END IF;
4101
4102 -- bug 3319789
4103 IF p_pickup_date_lo is not NULL OR
4104 p_pickup_date_hi is not NULL THEN
4105 FND_MESSAGE.SET_NAME('WSH', 'WSH_PICKUP_DATE_NOT_ALLOWED');
4106 FND_FILE.put_line(FND_FILE.output,' ');
4107 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4111
4108 WSH_UTIL_CORE.PrintMsg('Pick Up Date parameters are not applicable for entity Delivery Lines Only. Please do not enter Pick Up Date range when selecting entity Delivery Lines Only.');
4109 raise Parameters_ERR;
4110 END IF;
4112 -- bug 3319789
4113 IF p_dropoff_date_lo is not NULL OR
4114 p_dropoff_date_hi is not NULL THEN
4115 FND_MESSAGE.SET_NAME('WSH', 'WSH_DROPOFF_DATE_NOT_ALLOWED');
4116 FND_FILE.put_line(FND_FILE.output,' ');
4117 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4118 WSH_UTIL_CORE.PrintMsg('Drop Off Date parameters are not applicable for entity Delivery Lines Only. Please do not enter Drop Off Date range when selecting entity Delivery Lines Only.');
4119 raise Parameters_ERR;
4120 END IF;
4121
4122 -- set default value of Delivery Lines Status to 'ALL'
4123 IF p_delivery_lines_status IS NULL THEN
4124 l_select_criteria.delivery_lines_status := 'ALL';
4125 ELSE
4126 l_select_criteria.delivery_lines_status := p_delivery_lines_status;
4127 END IF;
4128
4129 -- bug 3332670
4130 IF p_scheduled_ship_date_lo is not NULL AND
4131 p_scheduled_ship_date_hi is not NULL THEN
4132 l_scheduled_date_lo := fnd_date.canonical_to_date(p_scheduled_ship_date_lo);
4133 l_scheduled_date_hi := fnd_date.canonical_to_date(p_scheduled_ship_date_hi);
4134 IF l_scheduled_date_hi < l_scheduled_date_lo THEN
4135 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SCH_SHIP_DATE_RANGE');
4136 FND_MESSAGE.SET_TOKEN('SCHEDULED_DATE_LO', to_char(l_scheduled_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
4137 FND_MESSAGE.SET_TOKEN('SCHEDULED_DATE_HI', to_char(l_scheduled_date_hi, 'DD-MON-YYYY HH24:MI:SS'));
4138 FND_FILE.put_line(FND_FILE.output,' ');
4139 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4140 WSH_UTIL_CORE.PrintMsg('The end date of Scheduled Ship Date range '||to_char(l_scheduled_date_hi, 'DD-MON-YYYY HH24:MI:SS')||' should not precede the start date '|| to_char(l_scheduled_date_lo, 'DD-MON-YYYY HH24:MI:SS'));
4141 raise Parameters_ERR;
4142 END IF;
4143 END IF;
4144
4145 l_select_criteria.scheduled_ship_date_lo := p_scheduled_ship_date_lo;
4146 l_select_criteria.scheduled_ship_date_hi := p_scheduled_ship_date_hi;
4147
4148 -- set default value of Source System to 'ALL'
4149 IF p_source_system IS NULL THEN
4150 l_select_criteria.source_code := 'ALL';
4151 ELSE
4152 l_select_criteria.source_code := p_source_system;
4153 END IF;
4154
4155 l_select_criteria.organization_id := p_organization_id;
4156 l_select_criteria.customer_id := p_customer_id;
4157 l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
4158 l_select_criteria.ship_method_code := p_ship_method_code;
4159 l_select_criteria.client_id := p_client_id; -- LSP PROJECT
4160
4161 IF p_log_level IS NULL THEN
4162 l_select_criteria.log_level := 0;
4163 ELSE
4164 l_select_criteria.log_level := p_log_level;
4165 END IF;
4166
4167 -- set append deliveries and autocreate deliveries crtieria to shipping parameter
4168 -- if autocreate deliveries is shipping parameter
4169
4170 IF p_autocreate_deliveries IS NULL OR p_autocreate_deliveries = 'SP' THEN
4171 l_autocreate_deliveries := 'SP';
4172 l_append_deliveries := 'N';
4173 l_ac_del_criteria := 'SP';
4174 ELSIF p_autocreate_deliveries = 'N' THEN
4175 -- turn off append deliveries and autocreate deliveries criteria if autocreate
4176 -- deliveries is off
4177 l_autocreate_deliveries := 'N';
4178 l_append_deliveries := 'N';
4179 l_ac_del_criteria := 'N';
4180 ELSE
4181 -- get the value of append deliveries and autocreate deliveries criteria from
4182 -- user input
4183 l_autocreate_deliveries := p_autocreate_deliveries;
4184 l_ac_del_criteria := p_ac_del_criteria;
4185 IF p_ac_del_criteria is NULL or p_ac_del_criteria = 'SP' or p_ac_del_criteria = 'Y' THEN
4186 l_append_deliveries := 'N';
4187 ELSE
4188 l_append_deliveries := p_append_deliveries;
4189 END IF;
4190 END IF;
4191
4192
4193 -- turn off append deliveries if TP is installed OR lines are not from OE OR
4194 -- lines status is not Release to Warehouse or Ready to Release
4195 -- IF WSH_UTIL_CORE.TP_Is_Installed = 'Y'
4196 IF p_source_system <> 'OE'
4197 OR p_delivery_lines_status in ('ALL', 'X', 'Y')
4198 OR p_organization_id is NULL THEN
4199 l_append_deliveries := 'N';
4200 END IF;
4201
4202 IF ( l_append_deliveries is NULL OR l_ac_del_criteria is NULL OR l_ac_del_criteria = 'SP') THEN --Bug10353854
4203 IF p_organization_id is not NULL THEN
4204 OPEN get_appending_limit(p_organization_id);
4205 FETCH get_appending_limit INTO l_appending_limit,l_sp_ac_del_criteria ; --Bug10353854
4206 IF get_appending_limit%NOTFOUND THEN
4207 CLOSE get_appending_limit;
4208 raise Process_Delivery_Lines_ERR;
4209 END IF;
4210 CLOSE get_appending_limit;
4211 IF l_appending_limit ='N' OR l_appending_limit is NULL THEN
4212 l_append_deliveries := 'N';
4213 ELSE
4214 l_append_deliveries := 'Y';
4215 END IF;
4216 ELSE
4217 l_append_deliveries := 'N';
4218 END IF;
4219 END IF;
4220
4221 IF l_autocreate_deliveries is not NULL THEN
4222 l_msg_string := NULL;
4223 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
4224 p_lookup_code => l_autocreate_deliveries,
4225 p_lookup_type => 'YES_NO');
4226 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_AUTOCREATE_DEL');
4227 FND_MESSAGE.SET_TOKEN('AUTOCREATE_DEL', l_msg_string);
4228 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4229 --Bug10353854
4230 IF ((l_ac_del_criteria is NULL OR l_ac_del_criteria = 'SP') and p_organization_id is not null and l_sp_ac_del_criteria is NOT NULL) THEN
4231 l_ac_del_criteria := l_sp_ac_del_criteria;
4232 END IF;
4233 END IF;
4234
4235 IF l_ac_del_criteria is not NULL THEN
4236 l_msg_string := NULL;
4237 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
4238 p_lookup_code => l_ac_del_criteria,
4239 p_lookup_type => 'WSH_AC_DEL_CRITERIA');
4240 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_AC_DEL_CRITERIA');
4241 FND_MESSAGE.SET_TOKEN('AC_DEL_CRITERIA', l_msg_string);
4242 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4246 l_msg_string := NULL;
4243 END IF;
4244
4245 IF l_append_deliveries is not NULL THEN
4247 l_msg_string := WSH_XC_UTIL.Get_Lookup_Meaning(
4248 p_lookup_code => l_append_deliveries,
4249 p_lookup_type => 'YES_NO');
4250 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_APPEND_DEL');
4251 FND_MESSAGE.SET_TOKEN('APPEND_DEL', l_msg_string);
4252 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4253 END IF;
4254
4255 -- populate the selection criteria record and call select_delivery_lines
4256 Select_Delivery_Lines(
4257 p_select_criteria => l_select_criteria,
4258 p_autocreate_deliveries => l_autocreate_deliveries,
4259 x_selected_det_tbl => l_selected_det_tbl,
4260 x_return_status => l_return_status);
4261
4262 IF l_debug_on THEN
4263 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Select_Deliveries_Lines is '|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
4264 END IF;
4265
4266 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4267 -- select_delivery_lines failed, exit
4268 raise WSH_SELECT_ERR;
4269 END IF;
4270 --Bug 10320421
4271 IF (l_ac_del_criteria IN ('Y','N') AND l_append_deliveries = 'N') THEN
4272 IF l_debug_on THEN
4273 WSH_DEBUG_SV.logmsg(l_module_name, 'Generating new WSH_PICK_LIST.G_BATCH_ID');
4274 END IF;
4275 select mtl_txn_request_headers_s.nextval into WSH_PICK_LIST.G_BATCH_ID from dual;
4276 END IF;
4277 IF l_debug_on THEN
4278 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_PICK_LIST.G_BATCH_ID : '||WSH_PICK_LIST.G_BATCH_ID);
4279 END IF;
4280 --Bug 10320421
4281
4282 IF l_selected_det_tbl.count > 0 THEN
4283 --Perf Bug 5215740
4284 l_processed := 0;
4285 Loop
4286 l_inner_loop_count :=0;
4287 <<inner_loop>>
4288 Loop
4289 l_processed := l_processed +1 ;
4290 l_inner_loop_count := l_inner_loop_count + 1;
4291 l_selected_det_tbl_tmp(l_inner_loop_count) := l_selected_det_tbl(l_processed);
4292 If (( l_inner_loop_count = 1000) or
4293 (l_processed = l_selected_det_tbl.count)) then
4294 Exit inner_loop;
4295 End If;
4296 End Loop;
4297
4298 -- have selected some lines for auto create
4299 IF l_debug_on THEN
4300 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery lines selected to be processed are: ');
4301 FOR k in 1 .. l_selected_det_tbl_tmp.count LOOP
4302 WSH_DEBUG_SV.logmsg(l_module_name, ' delivery lines: ' || l_selected_det_tbl_tmp(k));
4303 END LOOP;
4304
4305 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Process_Delivery_Lines_Batch',WSH_DEBUG_SV.C_PROC_LEVEL);
4306 END IF;
4307
4308 -- call Process_delivery_lines_batch to append deliveries and autocreate deliveries
4309 Process_Delivery_Lines_Batch(
4310 p_selected_det_tbl => l_selected_det_tbl_tmp,
4311 p_append_flag => l_append_deliveries,
4312 p_ac_del_criteria => l_ac_del_criteria,
4313 x_appended_det_num => l_appended_det_num,
4314 x_autocreate_del_det_num => l_autocreate_del_det_num,
4315 x_appended_del_num => l_appended_del_num,
4316 x_new_del_num => l_new_del_num,
4317 x_msg_count => l_msg_count,
4318 x_msg_data => l_msg_data,
4319 x_return_status => l_return_status);
4320
4321 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4322 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
4323 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_ERROR');
4324 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4325 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DEL_DET_WARNING');
4326 END IF;
4327 FND_FILE.put_line(FND_FILE.output,' ');
4328 FND_FILE.put_line(FND_FILE.output,' ');
4329 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4330
4331 -- print warning or error messages Process_Delivery_Lines_Batch
4332 l_msg_count := FND_MSG_PUB.count_msg;
4333
4334 IF l_msg_count > 0 THEN
4335 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_MESSAGE_LIST');
4336 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4337 FND_FILE.put_line(FND_FILE.output,'====================');
4338
4339 WSH_UTIL_CORE.PrintMsg('====================');
4340 FOR i in 1..l_msg_count LOOP
4341 FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4342 p_msg_index => i,
4343 p_data => l_buffer,
4344 p_msg_index_out => l_index_out);
4345 IF l_buffer IS NOT NULL THEN
4346 FND_FILE.put_line(FND_FILE.output,substrb(l_buffer,1,2000));
4347 WSH_UTIL_CORE.PrintMsg(substrb(l_buffer,1,2000));
4348 END IF;
4349 END LOOP;
4350
4351 END IF;
4352 raise Process_Delivery_Lines_ERR;
4353 END IF;
4354
4355 g_selected_det_count := g_selected_det_count + l_selected_det_tbl_tmp.count;
4356 g_appended_det_num := g_appended_det_num + l_appended_det_num;
4357 g_autocreate_del_det_num := g_autocreate_del_det_num + l_autocreate_del_det_num ;
4358 g_appended_del_num := g_appended_del_num + l_appended_del_num;
4359 g_new_del_num := g_new_del_num + l_new_del_num ;
4360
4361 IF l_debug_on THEN
4365 WSH_DEBUG_SV.logmsg(l_module_name,'g_selected_det_count = '|| g_selected_det_count ,WSH_DEBUG_SV.C_PROC_LEVEL);
4362 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Process_Delivery_Lines_Batch is '|| l_return_status ,WSH_DEBUG_SV.C_PROC_LEVEL);
4363
4364 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);
4366
4367 WSH_DEBUG_SV.logmsg(l_module_name,'l_appended_det_num = '|| l_appended_det_num,WSH_DEBUG_SV.C_PROC_LEVEL);
4368 WSH_DEBUG_SV.logmsg(l_module_name,'g_appended_det_num = '|| g_appended_det_num,WSH_DEBUG_SV.C_PROC_LEVEL);
4369
4370 WSH_DEBUG_SV.logmsg(l_module_name,'l_autocreate_del_det_num = '|| l_autocreate_del_det_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4371 WSH_DEBUG_SV.logmsg(l_module_name,'g_autocreate_del_det_num = '|| g_autocreate_del_det_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4372
4373 WSH_DEBUG_SV.logmsg(l_module_name,'l_appended_del_num = '|| l_appended_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4377 WSH_DEBUG_SV.logmsg(l_module_name,'g_new_del_num = '|| g_new_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4374 WSH_DEBUG_SV.logmsg(l_module_name,'g_appended_del_num = '|| g_appended_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4375
4376 WSH_DEBUG_SV.logmsg(l_module_name,'l_new_del_num = '|| l_new_del_num, WSH_DEBUG_SV.C_PROC_LEVEL);
4378 END IF;
4379
4380 exit when l_processed = l_selected_det_tbl.count;
4381 l_selected_det_tbl_tmp.delete;
4382 l_appended_det_num := 0;
4383 l_autocreate_del_det_num := 0;
4384 l_appended_del_num := 0;
4385 l_new_del_num := 0;
4386 End Loop;
4387
4388 -- print results of appending deliveries and autocreate deliveries
4389 -- print the summary results of auto create trips action
4390 FND_FILE.put_line(FND_FILE.output,' ');
4391 FND_FILE.put_line(FND_FILE.output,' ');
4392 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SUMMARY');
4393 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4394 FND_FILE.put_line(FND_FILE.output,'====================');
4395
4396 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
4397 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_selected_det_count));
4398 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4399
4400 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_APPENDED');
4401 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_appended_det_num));
4402 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4403
4404 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_GROUPED');
4405 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_autocreate_del_det_num));
4406 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4407
4408 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_CREATED');
4409 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DELIVERIES', to_char(g_new_del_num));
4410 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4411
4412 WSH_UTIL_CORE.PrintDateTime;
4413 WSH_UTIL_CORE.PrintMsg('Summary: ');
4414 WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
4415 WSH_UTIL_CORE.PrintMsg(to_char(g_appended_det_num)||' delivery lines have been successfully appended to existing deliveries');
4416 WSH_UTIL_CORE.PrintMsg(to_char(g_appended_del_num)||' existing deliveries have been successfully appended');
4417 WSH_UTIL_CORE.PrintMsg(to_char(g_autocreate_del_det_num)||' delivery lines have been successfully grouped to new deliveries');
4418 WSH_UTIL_CORE.PrintMsg(to_char(g_new_del_num)||' deliveries have been successfully created');
4419
4420
4421 ELSE
4422 -- no delivery lines selected
4423 FND_FILE.put_line(FND_FILE.output,' ');
4424 FND_FILE.put_line(FND_FILE.output,' ');
4425 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_SUMMARY');
4426 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4427 FND_FILE.put_line(FND_FILE.output,'====================');
4428
4429 FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
4430 FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(l_selected_det_tbl.count));
4431 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
4432
4433 WSH_UTIL_CORE.PrintDateTime;
4434 WSH_UTIL_CORE.PrintMsg('Summary: ');
4435 WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
4436
4437 END IF;
4438
4439 END IF;
4440
4441 errbuf := 'Process Deliveries is completed successfully';
4442 retcode := '0';
4443 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4444
4445 IF l_debug_on THEN
4446 WSH_DEBUG_SV.pop(l_module_name);
4447 END IF;
4448
4449
4450 EXCEPTION
4451
4452 WHEN Create_Consolidated_Trips_ERR THEN
4456 retcode := '1';
4453 l_completion_status := 'WARNING';
4454 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4455 errbuf := 'Process Deliveries completed with warnings';
4457 IF l_debug_on THEN
4458 WSH_DEBUG_SV.pop(l_module_name);
4459 END IF;
4460
4461 WHEN Process_Delivery_Lines_ERR THEN
4462 l_completion_status := 'WARNING';
4463 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4464 errbuf := 'Process Deliveries completed with warnings';
4465 retcode := '1';
4466 IF l_debug_on THEN
4467 WSH_DEBUG_SV.pop(l_module_name);
4468 END IF;
4469
4470 WHEN WSH_SELECT_ERR THEN
4471 WSH_UTIL_CORE.PrintMsg('Error Messages: ' || l_msg_data);
4472 l_completion_status := 'ERROR';
4473 WSH_UTIL_CORE.PrintMsg('Failed to select delivery lines for processing');
4474 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4475 errbuf := 'Process Deliveries is completed with warning';
4476 retcode := '1';
4477 IF l_debug_on THEN
4478 WSH_DEBUG_SV.pop(l_module_name);
4479 END IF;
4480
4481 WHEN Parameters_ERR THEN
4482 l_completion_status := 'WARNING';
4483 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4484 errbuf := 'Process Deliveries is completed with warning';
4485 retcode := '1';
4486 IF l_debug_on THEN
4487 WSH_DEBUG_SV.pop(l_module_name);
4488 END IF;
4489
4490
4491 WHEN OTHERS THEN
4492 l_completion_status := 'ERROR';
4493 l_error_code := SQLCODE;
4494 l_error_text := SQLERRM;
4495 WSH_UTIL_CORE.PrintMsg('Process Deliveries SRS failed with unexpected error.');
4496 WSH_UTIL_CORE.PrintMsg('The unexpected error is ' || l_error_text);
4497 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4498 errbuf := 'Process Deliveries failed with unexpected error';
4499 retcode := '2';
4500 IF l_debug_on THEN
4501 WSH_DEBUG_SV.pop(l_module_name);
4502 END IF;
4503
4504 END Process_Deliveries_SRS;
4505
4506
4507 END WSH_BATCH_PROCESS;
4508
4509