[Home] [Help]
PACKAGE BODY: APPS.WSH_DSNO
Source
1 PACKAGE BODY WSH_DSNO as
2 /* $Header: WSHDSNOB.pls 120.3.12010000.2 2009/04/21 07:24:08 ueshanka ship $ */
3
4
5 G_RLM_INSTALL_STATUS VARCHAR2(30);
6 G_EDI_INSTALL_STATUS VARCHAR2(30);
7
8 -- bug 1677940: add new parameter x_return_status
9
10 --
11 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DSNO';
12 --
13 PROCEDURE ec_document_send (
14 p_trip_stop_id IN NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2) IS
16
17 CURSOR doc_num IS
18 SELECT ece_output_runs_s.nextval
19 FROM dual;
20
21 l_doc_num NUMBER;
22 l_call_status BOOLEAN;
23 l_request_id BINARY_INTEGER;
24 l_msg_count NUMBER;
25 l_msg_data VARCHAR2(2000);
26 l_return_status VARCHAR2(1);
27 l_tmp_out NUMBER;
28 l_debug_level NUMBER := 1;
29 --
30 l_debug_on BOOLEAN;
31 --
32 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EC_DOCUMENT_SEND';
33 --
34
35 -- Following varibales added as part of Bug# 4255379
36 l_output_filename VARCHAR2(100);
37 l_output_file_ext VARCHAR2(20);
38 -- Added for bug 8424489
39 l_output_fileprefix VARCHAR2(30);
40
41 BEGIN
42
43 --
44 -- Debug Statements
45 --
46 --
47 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
48 --
49 IF l_debug_on IS NULL
50 THEN
51 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
52 END IF;
53 --
54 IF l_debug_on THEN
55 WSH_DEBUG_SV.push(l_module_name);
56 --
57 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
58 END IF;
59 --
60 OPEN doc_num;
61 FETCH doc_num INTO l_doc_num;
62 CLOSE doc_num;
63
64 --Following lines of code added as part of Bug 4255379
65 l_output_file_ext := FND_PROFILE.Value('WSH_DSNO_OUTPUT_FILE_EXT');
66 -- Bug 8424489 - Start
67 --
68 IF l_debug_on THEN
69 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_CUSTOM_PUB.Dsno_Output_File_Prefix',WSH_DEBUG_SV.C_PROC_LEVEL);
70 END IF;
71 --
72 l_output_fileprefix := WSH_CUSTOM_PUB.Dsno_Output_File_Prefix(
73 p_trip_stop_id => p_trip_stop_id,
74 p_doc_number => l_doc_num,
75 p_dsno_file_ext => l_output_file_ext );
76 --
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.log(l_module_name, 'File prefix returned from custom hook', l_output_fileprefix);
79 END IF;
80 --
81 l_output_fileprefix := nvl(l_output_fileprefix, 'DSNO');
82 -- Bug 8424489 - End
83
84 If l_output_file_ext IS NULL THEN
85 l_output_filename := l_output_fileprefix || to_char(l_doc_num);
86 Else
87 l_output_filename := l_output_fileprefix || to_char(l_doc_num) || '.' ||l_output_file_ext;
88 End If;
89 --Changes for Bug# 4255379 End.
90
91 --
92 -- Debug Statements
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit EC_DOCUMENT.SEND',WSH_DEBUG_SV.C_PROC_LEVEL);
96 END IF;
97 --
98 ec_document.send(
99 p_api_version_number => 1.0,
100 i_Output_Path => null,
101 i_Output_Filename => l_output_filename,
102 i_Transaction_Type => 'DSNO',
103 call_status => l_call_status,
104 request_id => l_request_id,
105 x_msg_count => l_msg_count,
106 x_msg_data => l_msg_data,
107 x_return_status => l_return_status,
108 p_parameter1 => p_trip_stop_id,
109 p_parameter2 => null,
110 p_parameter3 => null,
111 p_parameter4 => null,
112 p_parameter5 => null,
113 p_parameter6 => null,
114 p_parameter7 => null,
115 p_parameter8 => null,
116 p_parameter9 => null,
117 p_parameter10 => null,
118 p_parameter11 => null,
119 p_parameter12 => null,
120 p_parameter13 => null,
121 p_parameter14 => null,
122 p_parameter15 => null,
123 p_parameter16 => null,
124 p_parameter17 => null,
125 p_parameter18 => null,
126 p_parameter19 => null,
127 p_parameter20 => null,
128 I_DEBUG_MODE => null);
129
130 -- bug 1677940: let caller know the status
131 x_return_status := l_return_status;
132
133 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) OR NOT(l_call_status) THEN
134 -- Print Error Messages
135 FOR i IN 1..l_msg_count LOOP
136 fnd_msg_pub.get(
137 p_msg_index => i,
138 p_encoded => FND_API.G_FALSE,
139 p_data => l_msg_data,
140 p_msg_index_out => l_tmp_out);
141
142 --
143 -- Debug Statements
144 --
145 IF l_debug_on THEN
146 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_DATA );
147 END IF;
148 --
149
150 END LOOP;
151
152 END IF;
153
154 --
155 -- Debug Statements
156 --
157 IF l_debug_on THEN
158 WSH_DEBUG_SV.pop(l_module_name);
159 END IF;
160 --
161 END ec_document_send;
162
163 --
164 -- PROCEDURE: Submit
165 -- Purpose: Submit DSNO for a Trip Stop
166 -- Arguments: p_trip_stop_id - Trip Stop Identifier
167 -- Description: Submits DSNO for a trip stop
168 --
169 --- Bug 2425936 : added a parameter p_trip_id which is
170 --- is included in concurrent program for performace
171 PROCEDURE Submit (
172 errbuf OUT NOCOPY VARCHAR2,
173 retcode OUT NOCOPY VARCHAR2,
174 p_trip_id IN NUMBER DEFAULT NULL,
175 p_trip_stop_id IN NUMBER) IS
176 l_completion_status VARCHAR2(30);
177 l_temp BOOLEAN;
178 --
179 l_debug_on BOOLEAN;
180 --
181 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SUBMIT';
182 --
183 BEGIN
184 --
185 -- Debug Statements
186 --
187 --
188 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
189 --
190 IF l_debug_on IS NULL
191 THEN
192 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
193 END IF;
194 --
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.push(l_module_name);
197 --
198 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
199 END IF;
200 --
201 Submit_Trip_Stop(p_trip_stop_id, l_completion_status);
202 wsh_util_core.enable_concurrent_log_print;
203 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status, '');
204 IF l_completion_status = 'NORMAL' THEN
205 errbuf := 'DSNO submission is completed successfully';
206 retcode := '0';
207 ELSIF l_completion_status = 'WARNING' THEN
208 errbuf := 'DSNO submission is completed with warning';
209 retcode := '1';
210 ELSE
211 errbuf := 'DSNO submission is completed with error';
212 retcode := '2';
213 END IF;
214 --
215 -- Debug Statements
216 --
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.pop(l_module_name);
219 END IF;
220 --
221 END Submit;
222
223 -- start bug 1578251: new procedure submit_trip_stop with x_completion_status
224 --
225 -- PROCEDURE: Submit_Trip_stop
226 -- Purpose: Submit DSNO for a Trip Stop
227 -- Arguments: p_trip_stop_id - Trip Stop Identifier
228 -- x_completion_status - result of this submission
229 -- Description: Submits DSNO for a trip stop
230 --
231
232 PROCEDURE Submit_Trip_Stop (
233 p_trip_stop_id IN NUMBER,
234 x_completion_status OUT NOCOPY VARCHAR2) IS
235
236 l_industry VARCHAR2(30);
237 l_rlm_install_status VARCHAR2(30);
238 l_edi_install_status VARCHAR2(30);
239 l_delivery_count NUMBER := 0;
240 l_return_status BOOLEAN;
241 l_msg_count NUMBER := 0;
242 l_msg_data VARCHAR2(2000);
243 l_tmp_out NUMBER;
244 i NUMBER;
245 l_submit_ret_sts VARCHAR2(1);
246 l_completion_status VARCHAR2(30) := 'NORMAL';
247
248
249 -- CSUN 16-AUG-2000, replace select (*) by this cursor for better performance
250 l_trip_stop_id NUMBER;
251
252 cursor c_dsno_deliveries(c_trip_stop_id number ) is
253 SELECT
254 DECODE(HCAS.ORG_ID,WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(wnd.delivery_id),1,NULL,1, NULL) record_exists
255 FROM WSH_TRIP_STOPS WTS,
256 WSH_TRIPS WTP,
257 WSH_TRIP_STOPS WTS2,
258 WSH_DELIVERY_LEGS WDL,
259 WSH_NEW_DELIVERIES WND,
260 WSH_LOCATIONS WSHL,
261 WSH_LOCATIONS WSHL2,
262 HR_ORGANIZATION_UNITS HOU,
263 MTL_PARAMETERS MTP,
264 HZ_PARTY_SITES HPS,
265 HZ_CUST_ACCT_SITES_ALL HCAS,
266 HZ_CUST_SITE_USES_ALL HCSU,
267 ECE_TP_HEADERS ETH,
268 ECE_TP_DETAILS ETD
269 WHERE
270 WND.INITIAL_PICKUP_LOCATION_ID = WSHL.WSH_LOCATION_ID AND
271 /*J Inbound Logistics Changes jckwok*/
272 NVL(WND.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') AND
273 WSHL.LOCATION_SOURCE_CODE = 'HR' AND
274 HOU.ORGANIZATION_ID = WND.ORGANIZATION_ID AND
275 HOU.ORGANIZATION_ID = MTP.ORGANIZATION_ID AND
276 WTP.TRIP_ID = WTS.TRIP_ID AND
277 WTP.TRIP_ID = WTS2.TRIP_ID AND
278 WTS.TRIP_ID = WTS2.TRIP_ID AND
279 WND.ULTIMATE_DROPOFF_LOCATION_ID = WTS2.STOP_LOCATION_ID AND
280 WND.ULTIMATE_DROPOFF_LOCATION_ID = WSHL2.WSH_LOCATION_ID AND
281 WSHL2.LOCATION_SOURCE_CODE = 'HZ' AND
282 WSHL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID AND
283 NVL(HCAS.ORG_ID, -999) = NVL(HCSU.ORG_ID, -999) AND
284 WND.DELIVERY_ID = WDL.DELIVERY_ID AND
285 WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID AND
286 WDL.PICK_UP_STOP_ID = WTS.STOP_ID AND
287 HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND
288 HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND
289 HCSU.SITE_USE_CODE = 'SHIP_TO' AND
290 HCSU.STATUS = 'A' AND
291 ETH.TP_HEADER_ID = HCAS.TP_HEADER_ID AND
292 ETD.TP_HEADER_ID = ETH.TP_HEADER_ID AND
293 ETD.EDI_FLAG = 'Y' AND
294 ETD.DOCUMENT_ID = 'DSNO' AND
295 WTS.STOP_ID = c_trip_stop_id
296 ORDER BY RECORD_EXISTS;
297
298 --
299 l_debug_on BOOLEAN;
300 --
301 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SUBMIT_TRIP_STOP';
302 --
303 BEGIN
304
305 --
306 -- Debug Statements
307 --
308 --
309 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
310 --
311 IF l_debug_on IS NULL
312 THEN
313 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
314 END IF;
315 --
316 IF l_debug_on THEN
317 WSH_DEBUG_SV.push(l_module_name);
318 --
319 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
320 END IF;
321 --
322 --
323 -- Debug Statements
324 --
325 IF l_debug_on THEN
326 WSH_DEBUG_SV.logmsg(l_module_name, 'PROCESSING DSNO IN TRIP STOP ' || TO_CHAR ( P_TRIP_STOP_ID ) );
327 END IF;
328 --
329
330 IF G_RLM_INSTALL_STATUS IS NULL THEN
331 IF NOT (fnd_installation.get(662,662,G_RLM_INSTALL_STATUS,l_industry)) THEN
332 G_RLM_INSTALL_STATUS := 'N'; -- invalid application == not installed
333 ELSE
334 IF G_RLM_INSTALL_STATUS IS NULL THEN
335 G_RLM_INSTALL_STATUS := 'N';
336 END IF;
337 END IF;
338 END IF;
339 --
340 l_rlm_install_status := G_RLM_INSTALL_STATUS;
341
342 IF (l_rlm_install_status = 'I') THEN
343 --
344 -- Debug Statements
345 --
346 IF l_debug_on THEN
347 WSH_DEBUG_SV.logmsg(l_module_name, 'RLM INSTALLED: YES' );
348 END IF;
349 --
350 -- RLM is installed, calculate Cum Quantities
351 --
352 -- Debug Statements
353 --
354 IF l_debug_on THEN
355 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit RLM_TPA_SV.UPDATECUMKEY',WSH_DEBUG_SV.C_PROC_LEVEL);
356 END IF;
357 --
358 rlm_tpa_sv.updatecumkey(
359 p_trip_stop_id,
360 l_return_status);
361
362 -- Dump message file information
363 l_msg_count := fnd_msg_pub.count_msg;
364 FOR i IN 1..l_msg_count LOOP
365 fnd_msg_pub.get(
366 p_msg_index => i,
367 p_encoded => FND_API.G_FALSE,
368 p_data => l_msg_data,
369 p_msg_index_out => l_tmp_out);
370
371 --
372 -- Debug Statements
373 --
374 IF l_debug_on THEN
375 WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_DATA );
376 END IF;
377 --
378
379 END LOOP;
380
381 -- Validate return status
382 IF (NOT l_return_status) THEN
383 --
384 -- Debug Statements
385 --
386 IF l_debug_on THEN
387 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED IN RLM CUM QTY API' );
388 END IF;
389 --
390 x_completion_status := 'WARNING';
391 --
392 -- Debug Statements
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.pop(l_module_name);
396 END IF;
397 --
398 RETURN;
399 END IF;
400 END IF; /* l_rlm_install_status = 'I' */
401
402 COMMIT;
403
404 --
405 -- Debug Statements
406 --
407 IF l_debug_on THEN
408 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING EDI INSTALLATION STATUS' );
409 END IF;
410 --
411
412 IF G_EDI_INSTALL_STATUS IS NULL THEN
413 IF NOT (fnd_installation.get(175,175,G_EDI_INSTALL_STATUS,l_industry)) THEN
414 G_EDI_INSTALL_STATUS := 'N'; -- invalid application == not installed
415 ELSE
416 IF G_EDI_INSTALL_STATUS IS NULL THEN
417 G_EDI_INSTALL_STATUS := 'N';
418 END IF;
419 END IF;
420 END IF;
421 --
422 l_edi_install_status := G_EDI_INSTALL_STATUS;
423
424 IF (l_edi_install_status = 'I' ) THEN
425 --
426 -- Debug Statements
427 --
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.logmsg(l_module_name, 'EDI INSTALLED: YES' );
430 END IF;
431 --
432 ELSE
433 --
434 -- Debug Statements
435 --
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.logmsg(l_module_name, 'EDI INSTALLED: NO' );
438 END IF;
439 --
440 --
441 -- Debug Statements
442 --
443 IF l_debug_on THEN
444 WSH_DEBUG_SV.logmsg(l_module_name, 'TERMINATING PROGRAM' );
445 END IF;
446 --
447 x_completion_status := 'NORMAL';
448 --
449 -- Debug Statements
450 --
451 IF l_debug_on THEN
452 WSH_DEBUG_SV.pop(l_module_name);
453 END IF;
454 --
455 RETURN;
456 END IF;
457
458 IF (FND_PROFILE.VALUE('ECE_DSNO_ENABLED') = 'Y') THEN
459 --
460 -- Debug Statements
461 --
462 IF l_debug_on THEN
463 WSH_DEBUG_SV.logmsg(l_module_name, 'DSNO PROFILE: ENABLED' );
464 END IF;
465 --
466 ELSE
467 --
468 -- Debug Statements
469 --
470 IF l_debug_on THEN
471 WSH_DEBUG_SV.logmsg(l_module_name, 'DSNO PROFILE: DISABLED' );
472 END IF;
473 --
474 --
475 -- Debug Statements
476 --
477 IF l_debug_on THEN
478 WSH_DEBUG_SV.logmsg(l_module_name, 'TERMINATING PROGRAM' );
479 END IF;
480 --
481 x_completion_status := 'NORMAL';
482 --
483 -- Debug Statements
484 --
485 IF l_debug_on THEN
486 WSH_DEBUG_SV.pop(l_module_name);
487 END IF;
488 --
489 RETURN;
490 END IF;
491
492
493
494
495
496 -- csun 13-JUN-2000, change for better performance
497 open c_dsno_deliveries(p_trip_stop_id);
498 fetch c_dsno_deliveries into l_trip_stop_id;
499 IF c_dsno_deliveries%NOTFOUND THEN
500 l_trip_stop_id := NULL;
501 END IF;
502 close c_dsno_deliveries;
503
504 if nvl(l_trip_stop_id,0) = 0 then
505 --
506 -- Debug Statements
507 --
508 IF l_debug_on THEN
509 WSH_DEBUG_SV.logmsg(l_module_name, 'NO ELIGIBLE DELIVERY FOR THE DSNO' );
510 END IF;
511 --
512 --
513 -- Debug Statements
514 --
515 IF l_debug_on THEN
516 WSH_DEBUG_SV.logmsg(l_module_name, 'TERMINATING PROGRAM' );
517 END IF;
518 --
519 x_completion_status := 'NORMAL';
520 --
521 -- Debug Statements
522 --
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.pop(l_module_name);
525 END IF;
526 --
527 return;
528
529 /* SELECT COUNT(*)
530 INTO l_delivery_count
531 FROM WSH_DSNO_DELIVERIES_V
532 WHERE PICK_UP_STOP_ID = p_trip_stop_id;
533
534 IF(l_delivery_count = 0) THEN
535 wsh_util_core.println('No Eligible Delivery for the DSNO');
536 wsh_util_core.println('Terminating program');
537 RETURN;
538 */
539 END IF;
540
541 -- Submit EC DSNO
542 --
543 -- Debug Statements
544 --
545 IF l_debug_on THEN
546 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING EC SEND' );
547 END IF;
548 --
549 ec_document_send(p_trip_stop_id, l_submit_ret_sts);
550
551 -- bug 1677940: set completion status of DSNO submit
552 IF l_submit_ret_sts = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
553 x_completion_status := 'NORMAL';
554 ELSIF l_submit_ret_sts = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
555 x_completion_status := 'ERROR';
556 ELSE
557 x_completion_status := 'WARNING';
558 END IF;
559
560 --
561 -- Debug Statements
562 --
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.pop(l_module_name);
565 END IF;
566 --
567 EXCEPTION
568 WHEN OTHERS THEN
569 wsh_util_core.default_handler('WSH_DSNO.Submit');
570 x_completion_status := 'ERROR';
571
572 --
573 -- Debug Statements
574 --
575 IF l_debug_on THEN
576 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
577 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
578 END IF;
579 --
580 END Submit_Trip_Stop;
581 -- end bug 1578251: new procedure submit_trip_stop with x_completion_status
582
583
584 END WSH_DSNO;