1 PACKAGE BODY WSH_INV_INTEGRATION_GRP AS
2 /* $Header: WSHINVIB.pls 120.1 2012/01/05 16:39:40 brana ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INV_INTEGRATION_GRP ';
5 --
6
7 PROCEDURE Find_Printer (
8 p_subinventory IN VARCHAR2 ,
9 p_organization_id IN NUMBER,
10 x_api_status OUT NOCOPY VARCHAR2,
11 x_error_message OUT NOCOPY VARCHAR2
12 ) is
13 -- local variables
14 l_printer_name VARCHAR2(30);
15 l_org_found number;
16 --
17 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FIND_PRINTER';
18 --
19 l_debug_on BOOLEAN;
20 v_concurrent_program_name VARCHAR2(30);
21 l_exists_org_sub VARCHAR2(1) ;
22 l_exists_printer VARCHAR2(1);
23
24 k number;
25 --
26 CURSOR c_orgsub_printer (v_organization_id IN NUMBER ,
27 v_subinventory in VARCHAR2 ,
28 v_concurrent_program_name in VARCHAR2) IS
29 select printer_name
30 from wsh_report_printers_v wrp
31 where wrp.level_type_id = '10006'
32 and application_id = 665
33 and concurrent_program_id = (
34 select concurrent_program_id from
35 fnd_concurrent_programs_vl
36 where concurrent_program_name =v_concurrent_program_name
37 and application_id = 665
38 and rownum = 1 )
39 and organization_id = v_organization_id
40 and wrp.subinventory = v_subinventory
41 and wrp.enabled_flag = 'Y'
42 order by decode ( nvl( wrp.default_printer_flag ,2) ,
43 'Y' ,1 , 2 ) ;
44
45 CURSOR c_org_printer (v_organization_id IN NUMBER,v_concurrent_program_name in VARCHAR2 ) IS
46 select printer_name
47 from wsh_report_printers_v wrp
48 where wrp.level_type_id = '10008'
49 and application_id = 665
50 and concurrent_program_id = (
51 select concurrent_program_id from
52 fnd_concurrent_programs_vl
53 where concurrent_program_name = v_concurrent_program_name
54 and application_id = 665
55 and rownum = 1 )
56 and level_value_id = v_organization_id
57 and wrp.enabled_flag = 'Y'
58 order by decode ( nvl( wrp.default_printer_flag ,2) ,
59 'Y' ,1 , 2 ) ;
60
61
62 BEGIN
63
64 x_api_status := FND_API.G_RET_STS_SUCCESS;
65 x_error_message := NULL ;
66 --
67 --
68 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
69 --
70 IF l_debug_on IS NULL THEN
71 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
72 END IF;
73
74 IF l_debug_on THEN
75 WSH_DEBUG_SV.push(l_module_name);
76 WSH_DEBUG_SV.log(l_module_name,'p_subinventory',p_subinventory);
77 WSH_DEBUG_SV.log(l_module_name,'p_organization_id',p_organization_id);
78 END IF;
79
80 FOR k in 1..3 LOOP
81
82 IF k=1 THEN
83 v_concurrent_program_name := 'WSHRDPIK' ;
84 ELSIF k=2 THEN
85 v_concurrent_program_name := 'WSHRDPIK_XML' ;
86 ELSE
87 v_concurrent_program_name := 'WSHRDPIKX' ;
88 END IF ;
89
90 l_exists_org_sub :='N' ;
91
92 for i in 1..WSH_INV_INTEGRATION_GRP.G_ORGSUBTAB.count loop
93 if WSH_INV_INTEGRATION_GRP.G_ORGSUBTAB(i) = p_organization_id || '~' || p_subinventory || '~' ||v_concurrent_program_name THEN
94 --
95 l_exists_org_sub := 'Y' ;
96 exit ;
97 end if ;
98 --
99 --
100 end loop ;
101
102 -- Insert the orgsub combination in the global table:
103
104 IF l_exists_org_sub = 'N' THEN
105
106 WSH_INV_INTEGRATION_GRP.G_ORGSUBTAB(WSH_INV_INTEGRATION_GRP.G_ORGSUBTAB.count + 1 ):= p_organization_id || '~' || p_subinventory||'~' ||v_concurrent_program_name ;
107
108 l_org_found := 0 ;
109
110 for i in 1..WSH_INV_INTEGRATION_GRP.G_ORGTAB.count loop
111
112 if WSH_INV_INTEGRATION_GRP.G_ORGTAB(i) = p_organization_id|| '~' ||v_concurrent_program_name THEN
113 --
114 l_org_found := 1 ;
115 exit ; -- we don't return here , because we although this org has been encountered before
116 -- we want to know if a printer has been setup for this OrgSub combo.
117 end if ;
118 --
119 end loop ;
120
121 -- Initialize the printer_name to -1 and see if a setup exists for this orgsub combo.
122
123 l_printer_name := '-1' ;
124
125 OPEN c_orgsub_printer ( p_organization_id , p_subinventory,v_concurrent_program_name ) ;
126 FETCH c_orgsub_printer into l_printer_name ;
127 CLOSE c_orgsub_printer;
128 --
129 IF l_debug_on THEN --{
130 WSH_DEBUG_SV.logmsg(l_module_name, 'l_printer_name is : ' ||l_printer_name|| ' at Subinventory for concurrent Program is '||v_concurrent_program_name );
131 END IF;
132 ---
133 if l_printer_name = '-1' and l_org_found = 0 then
134
135 -- printer not set for the ( sub , org ) combination ,there search for just the org
136 -- First insert the org in the global table
137
138 WSH_INV_INTEGRATION_GRP.G_ORGTAB(WSH_INV_INTEGRATION_GRP.G_ORGTAB.count + 1 ):= p_organization_id|| '~' ||v_concurrent_program_name;
139
140 -- Next , see if printer setup has been done for this org
141
142 OPEN c_org_printer ( p_organization_id ,v_concurrent_program_name) ;
143 FETCH c_org_printer into l_printer_name ;
144 CLOSE c_org_printer;
145 --
146 IF l_debug_on THEN --{
147 WSH_DEBUG_SV.logmsg(l_module_name, 'l_printer_name is : ' ||l_printer_name|| ' at Organization for concurrent Program is '||v_concurrent_program_name );
148 END IF;
149 --
150 end if ;
151
152 l_exists_printer :='N' ;
153
154 for i in 1..WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count loop
155
156 if WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i).printer_name = l_printer_name
157 and WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i).conc_program_name = v_concurrent_program_name THEN
158 --
159 l_exists_printer := 'Y';
160 exit;
161 end if ;
162 end loop ;
163 --
164 --
165 -- bug 3980388 - Not to add printer '-1' to the table because Find_Printer return '-1' printer when
166 -- printing report by user level.
167 IF l_printer_name <> '-1' and l_exists_printer ='N' THEN
168 WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count + 1 ).printer_name := l_printer_name ;
169 WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count).conc_program_name := v_concurrent_program_name;
170 END IF;
171
172 END IF;
173 -- end bug 3980388
174
175 END LOOP;
176 --
177 IF l_debug_on THEN
178 WSH_DEBUG_SV.pop(l_module_name);
179 END IF;
180 --
181 exception
182 when others then
183 x_error_message := 'Exception occurred in WSH_INV_INTEGRATION_GRP.Find_Printer';
184 x_api_status := FND_API.G_RET_STS_UNEXP_ERROR;
185 --
186 IF l_debug_on THEN
187 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
188 END IF;
189
190 END Find_Printer ;
191
192 /*
193 Procedure :Complete_Inv_Interface
194 Description: This procedure will be called by Inventory during processing of the data from their interface
195 tables.The purpose of this procedure is to update the inventory_interfaced_flag on
196 wsh_delivery_details to 'Y' if inventory interface process has completed successfully
197 and also to update the pending_interface_flag for the corresponding trip stops to NULL.
198 */
199 PROCEDURE Complete_Inv_Interface(
200 p_api_version_number IN NUMBER,
201 p_init_msg_list IN VARCHAR2,
202 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
203 p_txn_header_id IN NUMBER,
204 p_txn_batch_id IN NUMBER,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2) IS
208
209 CURSOR detail_rec_csr IS
210 SELECT st.stop_id, wdd.delivery_detail_id
211 FROM wsh_delivery_details wdd,
212 mtl_transactions_interface mtf,
213 wsh_delivery_assignments_v da ,
214 wsh_delivery_legs dg,
215 wsh_new_deliveries dl,
216 wsh_trip_stops st
217 WHERE wdd.delivery_detail_id = mtf.picking_line_id
218 AND mtf.error_code IS NULL
219 AND mtf.transaction_header_id = p_txn_header_id
220 AND nvl(mtf.transaction_batch_id,-1) = nvl(nvl(p_txn_batch_id,mtf.transaction_batch_id),-1)
221 AND ((mtf.transaction_action_id IN ( 21,3,2,1)
222 AND mtf.transaction_source_type_id = 8)
223 OR ((mtf.transaction_action_id = 1) AND
224 mtf.transaction_source_type_id IN (2,16,13)))
225 AND wdd.container_flag = 'N'
226 AND nvl(wdd.inv_interfaced_flag , 'N') <> 'Y'
227 AND nvl(wdd.inv_interfaced_flag , 'N') <> 'X'
228 AND wdd.released_status <> 'D'
229 AND (exists (
230 SELECT mmt.picking_line_id
231 FROM mtl_material_transactions mmt
232 WHERE mmt.picking_line_id = wdd.delivery_detail_id
233 and transaction_source_type_id in ( 2,8,13,16 )
234 and trx_source_line_id = wdd.source_line_id
235 )
236 )
237 AND wdd.delivery_detail_id = da.delivery_detail_id
238 AND dl.delivery_id = da.delivery_id
239 AND da.delivery_id IS NOT NULL
240 AND st.stop_id = dg.pick_up_stop_id
241 AND st.stop_location_id = dl.initial_pickup_location_id
242 AND dg.delivery_id = dl.delivery_id
243 AND wdd.source_code in ('OE','OKE', 'WSH')
244 ORDER BY st.stop_id, wdd.delivery_detail_id
245 FOR UPDATE of wdd.delivery_detail_id NOWAIT;
246
247 -- Cursor to verify that OM and INV interface is complete for trip stop.
248 CURSOR c_lines_not_interfaced(p_stop_id NUMBER) IS
249 SELECT wdd.delivery_detail_id
250 FROM wsh_trip_stops wts,
251 wsh_delivery_legs wdl,
252 wsh_delivery_assignments_v wda,
253 wsh_delivery_details wdd
254 WHERE (wdd.inv_interfaced_flag IN ('N', 'P') OR wdd.oe_interfaced_flag <> 'Y')
255 AND wts.stop_id = p_stop_id
256 AND wts.stop_location_id = wdd.ship_from_location_id
257 AND wts.stop_id = wdl.pick_up_stop_id
258 AND wdl.delivery_id = wda.delivery_id
259 AND wda.delivery_id IS NOT NULL
260 AND wda.delivery_detail_id = wdd.delivery_detail_id
261 AND wdd.source_code in ('OE','OKE', 'WSH')
262 AND wdd.released_status <> 'D'
263 AND rownum = 1;
264
265 l_api_version_number CONSTANT NUMBER := 1.0;
266 l_api_name CONSTANT VARCHAR2(30):= 'Complete_Inv_Interface';
267
268 l_debug_on BOOLEAN;
269 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMPLETE_INV_INTERFACE';
270
271 trip_stop_locked exception;
272 PRAGMA EXCEPTION_INIT(trip_stop_locked, -54);
273
274 TYPE tlb_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
275
276 TYPE Rec_Tab_Type IS RECORD (
277 stop_id tlb_num,
278 delivery_detail_id tlb_num);
279
280 l_det_rec Rec_Tab_Type;
281 l_row_count NUMBER:=0;
282 l_stop_id NUMBER;
283 l_temp VARCHAR2(1);
284 l_temp_id NUMBER;
285 l_encoded VARCHAR2(1) := 'F';
286 l_index NUMBER;
287 l_stop_cache wsh_util_core.id_tab_type;
288
289 BEGIN
290 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
291
292 IF l_debug_on IS NULL THEN
293 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
294 END IF;
295
296 IF l_debug_on THEN
297 WSH_DEBUG_SV.push(l_module_name);
298 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
299 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
300 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
301 WSH_DEBUG_SV.log(l_module_name,'p_txn_header_id',p_txn_header_id);
302 WSH_DEBUG_SV.log(l_module_name,'p_txn_batch_id',p_txn_batch_id);
303 END IF;
304
305 savepoint Complete_Inv_Interface;
306
307 -- Standard call to check for call compatibility
308 IF NOT FND_API.Compatible_API_Call(l_api_version_number,p_api_version_number,l_api_name,G_PKG_NAME) THEN
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310 END IF;
311
312 IF l_debug_on THEN
313 WSH_DEBUG_SV.logmsg(l_module_name,'Done Compatible_API_Call');
314 END IF;
315
316 -- Initialize message stack if required
317 IF FND_API.to_Boolean(p_init_msg_list) THEN
318 FND_MSG_PUB.initialize;
319 END IF;
320
321 IF l_debug_on THEN
322 WSH_DEBUG_SV.logmsg(l_module_name,'Done FND_API.to_Boolean');
323 END IF;
324
325 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
326
327 --Bulk fetch detail_rec_csr into record of table.
328 OPEN detail_rec_csr;
329 FETCH detail_rec_csr BULK COLLECT
330 INTO -- l_det_rec; -- replaced due to 8.1.7.4 pl/sql bug 3286811
331 l_det_rec.stop_id,
332 l_det_rec.delivery_detail_id;
333 l_row_count := detail_rec_csr%ROWCOUNT;
334 IF l_debug_on THEN
335 WSH_DEBUG_SV.log(l_module_name,'No of Record fetch l_row_count',l_row_count);
336 END IF;
337 CLOSE detail_rec_csr;
338
339 IF (l_row_count < 1 ) THEN
340 raise no_data_found;
341 END IF;
342
343 SAVEPOINT Complete_Inv_Interface;
344
345 l_stop_id := l_det_rec.stop_id(l_det_rec.stop_id.first);
346
347 IF l_debug_on THEN
348 WSH_DEBUG_SV.log(l_module_name,'l_stop_id',l_stop_id);
349 END IF;
350
351 FORALL i IN 1 ..l_row_count
352 UPDATE wsh_delivery_details
353 SET inv_interfaced_flag = 'Y'
354 WHERE delivery_detail_id = l_det_rec.delivery_detail_id(i);
355
356 l_row_count := SQL%ROWCOUNT;
357
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.log(l_module_name,'No of Record Update to Y l_row_count',l_row_count);
360 END IF;
361
362 /* Check if all the delivery details under the trip stop are interfaced to inventory and OM.
363 If yes, then update the pending_interface_flag of the trip to NULL.*/
364
365 l_index := l_det_rec.stop_id.FIRST;
366 WHILE l_index IS NOT NULL LOOP --{
367 IF NOT l_stop_cache.EXISTS(l_det_rec.stop_id(l_index)) THEN --{
368 BEGIN
369 l_stop_cache(l_det_rec.stop_id(l_index))
370 := l_det_rec.stop_id(l_index);
371 SELECT 'X' INTO l_temp
372 FROM WSH_TRIP_STOPS
373 WHERE stop_id = l_det_rec.stop_id(l_index)
374 FOR UPDATE NOWAIT;
375
376 OPEN c_lines_not_interfaced(l_det_rec.stop_id(l_index));
377 FETCH c_lines_not_interfaced INTO l_temp_id;
378
379 IF (c_lines_not_interfaced%NOTFOUND) THEN
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.log(l_module_name,'All lines are Interface to INV and OM',l_temp_id);
382 END IF;
383
384 UPDATE wsh_trip_stops
385 SET pending_interface_flag = NULL
386 WHERE stop_id = l_det_rec.stop_id(l_index);
387
388 l_row_count := SQL%ROWCOUNT;
389
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.log(l_module_name,'pending_interface_flag update to NULL for stop',l_det_rec.stop_id(l_index));
392 END IF;
393 ELSE
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.log(l_module_name,'All lines are Not Interface to INV and OM',l_temp_id);
396 END IF;
397 END IF;
398 CLOSE c_lines_not_interfaced;
399
400 --EXIT;
401 EXCEPTION
402 WHEN trip_stop_locked THEN
403 NULL;
404 END;
405
406 END IF; --}
407 l_index := l_det_rec.stop_id.NEXT(l_index);
408 END LOOP; --}
409
410 IF p_commit = FND_API.G_TRUE THEN
411 COMMIT;
412 END IF;
413
414
415 FND_MSG_PUB.Count_And_Get
416 ( p_encoded => l_encoded
417 , p_count => x_msg_count
418 , p_data => x_msg_data);
419
420 IF l_debug_on THEN
421 WSH_DEBUG_SV.pop(l_module_name);
422 END IF;
423
424 EXCEPTION
425 WHEN no_data_found THEN
426 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
427 IF l_debug_on THEN
428 WSH_DEBUG_SV.logmsg(l_module_name,'No Record found to complete interface',WSH_DEBUG_SV.C_EXCEP_LEVEL);
429 WSH_DEBUG_SV.pop(l_module_name);
430 END IF;
431
432
433 WHEN trip_stop_locked THEN
434 x_return_status := FND_API.G_RET_STS_ERROR ;
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.logmsg(l_module_name,'Could not locked the records for stop',WSH_DEBUG_SV.C_EXCEP_LEVEL);
437 WSH_DEBUG_SV.pop(l_module_name);
438 END IF;
439 IF detail_rec_csr%ISOPEN THEN
440 CLOSE detail_rec_csr;
441 END IF;
442
443 IF c_lines_not_interfaced%ISOPEN THEN
444 CLOSE c_lines_not_interfaced;
445 END IF;
446
447 rollback to savepoint Complete_Inv_Interface;
448
449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 -- Get message count and data
452 FND_MSG_PUB.Count_And_Get
453 ( p_encoded => l_encoded
454 , p_count => x_msg_count
455 , p_data => x_msg_data
456 );
457 IF l_debug_on THEN
458 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
459 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
460 END IF;
461 rollback to savepoint Complete_Inv_Interface;
462
463 WHEN OTHERS THEN
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
465 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
466 THEN
467 FND_MSG_PUB.Add_Exc_Msg
468 ( G_PKG_NAME
469 , '_x_'
470 );
471 END IF;
472 -- Get message count and data
473 FND_MSG_PUB.Count_And_Get
474 ( p_encoded => l_encoded
475 , p_count => x_msg_count
476 , p_data => x_msg_data
477 );
478 IF l_debug_on THEN
479 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
480 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
481 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
482 END IF;
483 rollback to savepoint Complete_Inv_Interface;
484
485 END Complete_Inv_Interface;
486
487
488
489 END WSH_INV_INTEGRATION_GRP ;
[Home] [Help]