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