[Home] [Help]
PACKAGE BODY: APPS.WSH_BULK_PROCESS_GRP
Source
1 PACKAGE BODY WSH_BULK_PROCESS_GRP as
2 /* $Header: WSHBPGPB.pls 120.2 2007/01/03 23:07:11 parkhj noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_BULK_PROCESS_GRP';
5
6 --========================================================================
7 -- PROCEDURE : Create_update_delivery_details
8 --
9 -- PARAMETERS: p_api_version_number known api versionerror buffer
10 -- p_init_msg_list FND_API.G_TRUE to reset list
11 -- x_return_status return status
15 -- p_action_prms Additional attributes needed
12 -- x_msg_count number of messages in the list
13 -- x_msg_data text of messages
14 -- p_commit FND_API.G_TRUE to perform a commit
16 -- x_Out_Rec Place holder
17 -- p_line_rec Line record
18 -- VERSION : current version 1.0
19 -- initial version 1.0
20 -- COMMENT : This API is called from ONT to import delivery details and
21 -- delivery_assignments
22 -- At this time only insert operation (action='CREATE') is
23 -- supported for OM lines.
24 -- This API is also called from PO.
25 --========================================================================
26 PROCEDURE Create_update_delivery_details(
27 p_api_version_number IN NUMBER,
28 p_init_msg_list IN VARCHAR2,
29 p_commit IN VARCHAR2,
30 p_action_prms IN
31 WSH_BULK_TYPES_GRP.action_parameters_rectype,
32 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_rec_type,
33 x_Out_Rec OUT NOCOPY
34 WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 )
39 IS
40 l_debug_on BOOLEAN;
41 --
42 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME ||
43 '.' || 'CREATE_UPDATE_DELIVERY_DETAILS';
44 l_api_name CONSTANT VARCHAR2(40):= 'CREATE_UPDATE_DELIVERY_DETAILS';
45 l_api_version_number CONSTANT NUMBER := 1.0;
46 l_token_name VARCHAR2(200);
47 l_return_status VARCHAR2(1);
48 l_num_warnings NUMBER;
49 l_num_errors NUMBER;
50 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
51
52 --Bugfix 4070732
53 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
54 l_reset_flags BOOLEAN;
55 l_otm_installed VARCHAR2(1) ;
56 e_success EXCEPTION;
57
58 BEGIN
59
60 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
61 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
62 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
63 END IF;
64
65 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
66 IF l_debug_on IS NULL
67 THEN
68 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
69 END IF;
70
71 SAVEPOINT S_CREATE_UPDATE_DETAILS_BULK;
72
73 IF FND_API.to_Boolean(p_init_msg_list) THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76 --
77 IF l_debug_on THEN
78 wsh_debug_sv.push (l_module_name);
79 wsh_debug_sv.log (l_module_name,'p_api_version_number',p_api_version_number);
80 wsh_debug_sv.log (l_module_name,'p_init_msg_list',p_init_msg_list);
81 wsh_debug_sv.log (l_module_name,'p_commit',p_commit);
82 wsh_debug_sv.log (l_module_name,'Caller',p_action_prms.Caller);
83 wsh_debug_sv.log (l_module_name,'action_code',p_action_prms.action_code);
84 wsh_debug_sv.log (l_module_name,'Org_id',p_action_prms.Org_id);
85 END IF;
86
87 IF NOT FND_API.Compatible_API_Call
88 ( l_api_version_number,
89 p_api_version_number,
90 l_api_name,
91 G_PKG_NAME
92 )
93 THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96 --
97 IF p_action_prms.caller IS NULL THEN
98 l_token_name := 'p_action_prms.caller';
99 ELSIF p_action_prms.action_code IS NULL THEN
100 l_token_name := 'p_action_prms.action_code';
101 END IF;
102
103 IF l_token_name IS NOT NULL THEN
104 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
105 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_token_name);
106 l_return_status:=wsh_util_core.g_ret_sts_unexp_error;
107 wsh_util_core.add_message(x_return_status,l_module_name);
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; --bms should we raise unexpected??
109 END IF;
110
111 -- IF caller is neither 'OM' or 'PO', raise an exception.
112 IF p_action_prms.caller NOT IN ('OM','PO') THEN
113 IF l_debug_on THEN
114 wsh_debug_sv.log (l_module_name,'bad caller',p_action_prms.Caller);
115 END IF;
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END IF;
118
119 l_otm_installed := WSH_UTIL_CORE.Get_Otm_Install_Profile_Value;
120 IF l_otm_installed IN ( 'Y','P')
121 AND NVL(p_action_prms.caller,'PO') <> 'OM'
122 THEN --{
123 RAISE e_success;
124 END IF;
125
126
127 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
128
129 -- Added the condition on caller for Inbound logistics.
130 -- added for testing purpose
131 /*
132 if p_action_prms.action_code = 'REOPEN_PO'
133 then
134 p_action_prms.action_code := 'APPROVE_PO';
135 end if;
136 */
137
138 -- IF True -> Calls API WSH_bulk_process_pvt.create_delivery_details to create
139 -- new delivery lines.
140 IF p_action_prms.action_code = 'CREATE' OR
141 (p_action_prms.caller like '%PO%' AND
145 'PURGE_PO','REOPEN_PO')) THEN
142 wsh_util_core.fte_is_installed = 'Y' AND
143 p_action_prms.action_code in ('APPROVE_PO','FINAL_CLOSE','CANCEL_PO',
144 'CLOSE_PO','CLOSE_PO_FOR_RECEIVING',
146 l_action_prms := p_action_prms;
147
148 WSH_bulk_process_pvt.create_delivery_details(
149 p_action_prms => l_action_prms,
150 p_line_rec => p_line_rec,
151 x_return_status => l_return_status
152 );
153
154 --Calls API WSH_IB_TXN_MATCH_PKG.matchTransaction to match the Records
155 --based on Action code.
156 elsif
157 (p_action_prms.caller like '%PO%' AND
158 wsh_util_core.fte_is_installed = 'Y' AND
159 p_action_prms.action_code in
160 (
161 'ASN',
162 'CANCEL_ASN',
163 'RECEIPT',
164 'MATCH',
165 'RECEIPT_CORRECTION',
166 'RTV',
167 'RTV_CORRECTION',
168 'RECEIPT_ADD',
169 'RECEIPT_HEADER_UPD'
170 )
171 )
172 THEN
173
174 --l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
175 --l_action_prms := p_action_prms;
176 WSH_IB_TXN_MATCH_PKG.matchTransaction
177 (
178 p_action_prms => p_action_prms,
179 p_line_rec => p_line_rec,
180 x_return_status => l_return_status
181 );
182
183
184 END IF;
185
186 IF l_debug_on THEN
187 wsh_debug_sv.log (l_module_name,'return status from create_delivery_details',l_return_status);
188 END IF;
189
190 wsh_util_core.api_post_call(
191 p_return_status => l_return_status,
192 x_num_warnings => l_num_warnings,
193 x_num_errors => l_num_errors);
194 IF l_debug_on THEN
195 wsh_debug_sv.log (l_module_name,'after api_post_call');
196 END IF;
197
198 IF (l_num_errors > 0 ) THEN
199 RAISE FND_API.G_EXC_ERROR;
200 ELSE
201 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
202 END IF;
203
204
205 IF FND_API.To_Boolean( p_commit ) THEN
206
207 --bug 4070732
208 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
209 --{
210
211 l_reset_flags := FALSE;
212
213 IF l_debug_on THEN
214 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
215 END IF;
216
217 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
218 x_return_status => l_return_status);
219
220 IF l_debug_on THEN
221 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
222 END IF;
223
224 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
225 RAISE FND_API.G_EXC_ERROR;
226 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
229 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
230 END IF;
231
232 --}
233 END IF;
234 --bug 4070732
235
236 COMMIT WORK;
237 END IF;
238 --
239 --bug 4070732
240 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
241 --{
242 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
243 --{
244
245 IF FND_API.To_Boolean( p_commit ) THEN
246 IF l_debug_on THEN
247 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
248 END IF;
249
250 WSH_UTIL_CORE.reset_stops_for_load_tender(p_reset_flags => TRUE,
251 x_return_status => l_return_status);
252
253 ELSE
254
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
257 END IF;
258
259 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
260 x_return_status => l_return_status);
261 END IF;
262
263 IF l_debug_on THEN
264 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
265 END IF;
266
267 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
268 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
269 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
270 x_return_status := l_return_status;
271 END IF;
272
273 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
274 IF NOT FND_API.To_Boolean( p_commit ) THEN
275 ROLLBACK TO S_CREATE_UPDATE_DETAILS_BULK;
276 END IF;
277 END IF;
278
279 --}
280 END IF;
281 --}
282 END IF;
283
284 --bug 4070732
285 --
286 IF l_debug_on THEN
287 wsh_debug_sv.log (l_module_name,'getting the messages');
288 END IF;
289 FND_MSG_PUB.Count_And_Get
293 p_encoded => FND_API.G_FALSE
290 (
291 p_count => x_msg_count,
292 p_data => x_msg_data,
294 );
295 --
296 IF l_debug_on THEN
297 WSH_DEBUG_SV.pop(l_module_name);
298 END IF;
299 --
300 EXCEPTION
301 WHEN e_success THEN
302 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
303 --
304
305 /*
306 if any messages are set before this exception is
307 called then this should be uncommented.
308 FND_MSG_PUB.Count_And_Get
309 (
310 p_count => x_msg_count,
311 p_data => x_msg_data,
312 p_encoded => FND_API.G_FALSE
313 );
314 */
315 IF l_debug_on THEN
316 WSH_DEBUG_SV.pop(l_module_name,'e_success');
317 END IF;
318
319 WHEN FND_API.G_EXC_ERROR THEN
320 --ROLLBACK TO S_CREATE_UPDATE_DETAILS; bms
321 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
322 --
323 --
324 -- Start code for Bugfix 4070732
325 --
326 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
327 --{
328 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
329 --{
330 IF l_debug_on THEN
331 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
332 END IF;
333
334 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
335 x_return_status => l_return_status);
336
337
338 IF l_debug_on THEN
339 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
340 END IF;
341
342 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
343 x_return_status := l_return_status;
344 END IF;
345 --}
346 END IF;
347 --}
348 END IF;
349 --
350 -- End of Code Bugfix 4070732
351 --
352 FND_MSG_PUB.Count_And_Get
353 (
354 p_count => x_msg_count,
355 p_data => x_msg_data,
356 p_encoded => FND_API.G_FALSE
357 );
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
360 || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
361 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
362 END IF;
363
364
365 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
366 ROLLBACK TO S_CREATE_UPDATE_DETAILS_BULK;
367 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
368 --
369 --
370 -- Start code for Bugfix 4070732
371 --
372 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
373 --{
374 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
375 --{
376 IF l_debug_on THEN
377 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
378 END IF;
379
380 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
381 x_return_status => l_return_status);
382
383
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
386 END IF;
387
388 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
389 x_return_status := l_return_status;
390 END IF;
391 --}
392 END IF;
393 --}
394 END IF;
395 --
396 -- End of Code Bugfix 4070732
397 --
398 FND_MSG_PUB.Count_And_Get
399 (
400 p_count => x_msg_count,
401 p_data => x_msg_data,
402 p_encoded => FND_API.G_FALSE
403 );
404 IF l_debug_on THEN
405 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. ' ||
406 'Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
407 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
408 END IF;
409
410 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
411 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
412 --
413 --
414 -- Start code for Bugfix 4070732
415 --
416 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
417 --{
418 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
419 --{
420 IF l_debug_on THEN
421 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
422 END IF;
423
424 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
425 x_return_status => l_return_status);
426
427
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
430 END IF;
431
432 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
433 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
434 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
435 x_return_status := l_return_status;
436 END IF;
437 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
438 ROLLBACK TO S_CREATE_UPDATE_DETAILS_BULK;
439 END IF;
440
441 --}
442 END IF;
443 --}
444 END IF;
445 --
446 -- End of Code Bugfix 4070732
447 --
448 FND_MSG_PUB.Count_And_Get
449 (
450 p_count => x_msg_count,
451 p_data => x_msg_data,
452 p_encoded => FND_API.G_FALSE
453 );
454 IF l_debug_on THEN
455 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING '||
456 'exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
457 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
458 END IF;
459 --
460
461 WHEN OTHERS THEN
462 ROLLBACK TO S_CREATE_UPDATE_DETAILS_BULK;
463 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
464 wsh_util_core.default_handler('WSH_BULK_PROCESS_GRP.CREATE_UPDATE_DELIVERY_DETAILS');
465 --
466 --
467 -- Start code for Bugfix 4070732
468 --
469 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
470 --{
471 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
472 --{
473 IF l_debug_on THEN
474 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
475 END IF;
476
477 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
478 x_return_status => l_return_status);
479
480
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
483 END IF;
484
485 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
486 x_return_status := l_return_status;
487 END IF;
488 --}
489 END IF;
490 --}
491 END IF;
492 --
493 -- End of Code Bugfix 4070732
494 --
495 FND_MSG_PUB.Count_And_Get
496 (
497 p_count => x_msg_count,
498 p_data => x_msg_data,
499 p_encoded => FND_API.G_FALSE
500 );
501 IF l_debug_on THEN
502 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
503 'Oracle error message is '||
504 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
505 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
506 END IF;
507
508 END Create_update_delivery_details;
509
510
511
512 END WSH_bulk_process_grp;