[Home] [Help]
PACKAGE BODY: APPS.WSH_DCP_PVT
Source
1 PACKAGE BODY WSH_DCP_PVT as
2 /* $Header: WSHDCPPB.pls 120.0 2005/05/26 17:09:47 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DCP_PVT';
6 g_userid NUMBER;
7 g_user_email VARCHAR2(32767);
8 g_user_name VARCHAR2(32767);
9 g_env VARCHAR2(32767);
10
11
12 Function get_email_server RETURN VARCHAR2
13 IS
14 l_debug_on BOOLEAN;
15 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_EMAIL_SERVER';
16
17 BEGIN
18 --
19 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
20 --
21 IF l_debug_on IS NULL
22 THEN
23 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
24 END IF;
25 --
26 IF l_debug_on THEN
27 wsh_debug_sv.push(l_module_name);
28 END IF;
29
30 IF wsh_dcp_pvt.g_email_server IS NOT NULL THEN
31
32 IF l_debug_on THEN
33 WSH_DEBUG_SV.LOG(l_module_name, 'server name cache', wsh_dcp_pvt.g_email_server);
34 wsh_debug_sv.pop(l_module_name);
35 END IF;
36
37 RETURN wsh_dcp_pvt.g_email_server;
38 END IF;
39
40 wsh_dcp_pvt.g_email_server := fnd_profile.value('WSH_DCP_EMAIL_SERVER');
41
42 IF l_debug_on THEN
43 WSH_DEBUG_SV.LOG(l_module_name, 'server name profile', wsh_dcp_pvt.g_email_server);
44 wsh_debug_sv.pop(l_module_name);
45 END IF;
46 RETURN wsh_dcp_pvt.g_email_server;
47
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 IF l_debug_on THEN
52 wsh_debug_sv.logmsg(l_module_name, 'When others error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
53 wsh_debug_sv.pop(l_module_name);
54 END IF;
55 RETURN NULL;
56 END Get_email_server;
57
58 Function get_email_address RETURN VARCHAR2
59 IS
60 l_debug_on BOOLEAN;
61 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_EMAIL_ADDRESS';
62 BEGIN
63 --
64 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
65 --
66 IF l_debug_on IS NULL
67 THEN
68 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
69 END IF;
70 --
71 IF l_debug_on THEN
72 wsh_debug_sv.push(l_module_name);
73 END IF;
74
75 IF wsh_dcp_pvt.g_email_address IS NOT NULL THEN
76 IF l_debug_on THEN
77 WSH_DEBUG_SV.LOG(l_module_name, 'Email Address cache', wsh_dcp_pvt.g_email_address);
78 wsh_debug_sv.pop(l_module_name);
79 END IF;
80 RETURN wsh_dcp_pvt.g_email_address;
81 END IF;
82
83 wsh_dcp_pvt.g_email_address := fnd_profile.value('WSH_DCP_EMAIL_ADDRESSES');
84
85 IF l_debug_on THEN
86 WSH_DEBUG_SV.LOG(l_module_name, 'Email Address profile', wsh_dcp_pvt.g_email_address);
87 wsh_debug_sv.pop(l_module_name);
88 END IF;
89 RETURN wsh_dcp_pvt.g_email_address;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 IF l_debug_on THEN
94 wsh_debug_sv.logmsg(l_module_name, 'When others error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
95 wsh_debug_sv.pop(l_module_name);
96 END IF;
97 RETURN NULL;
98 END Get_email_address;
99
100
101 Procedure Send_Mail(sender IN VARCHAR2,
102 recipient1 IN VARCHAR2,
103 recipient2 IN VARCHAR2,
104 recipient3 IN VARCHAR2,
105 recipient4 IN VARCHAR2,
106 message IN VARCHAR2)
107 IS
108 l_mailhost VARCHAR2(32767);
109 l_mail_conn utl_smtp.connection;
110 l_email_addrs VARCHAR2(32767);
111 l_spr VARCHAR2(30) := ',';
112 l_start_pos NUMBER;
113 l_end_pos NUMBER;
114 j NUMBER;
115
116 l_recipient1 VARCHAR2(32767);
117 l_recipient2 VARCHAR2(32767);
118 l_recipient3 VARCHAR2(32767);
119 l_recipient4 VARCHAR2(32767);
120 l_recipient5 VARCHAR2(32767);
121
122 l_sender VARCHAR2(32767) := 'Oracle-Order-Fulfillment-Data-Integrity-Check@oracleorderfulfillment';
123 l_debug_on BOOLEAN;
124 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_MAIL';
125
126 BEGIN
127 --
128 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
129 --
130 IF l_debug_on IS NULL
131 THEN
132 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
133 END IF;
134 --
135 IF l_debug_on THEN
136 WSH_DEBUG_SV.push(l_module_name);
137 WSH_DEBUG_SV.log(l_module_name, 'sender', sender);
138 WSH_DEBUG_SV.log(l_module_name, 'recipient1', recipient1);
139 WSH_DEBUG_SV.log(l_module_name, 'recipient2', recipient2);
140 WSH_DEBUG_SV.log(l_module_name, 'recipient3', recipient3);
141 WSH_DEBUG_SV.log(l_module_name, 'recipient4', recipient4);
142 END IF;
143
144 --Call function that will return the email server name
145 l_mailhost := get_email_server;
146
147 --Call function that will return the email addresses
148 l_email_addrs := get_email_address;
149
150 --Parse to get individual recipients
151 IF l_debug_on THEN
152 wsh_debug_sv.log(l_module_name, 'l_mailhost', l_mailhost);
153 wsh_debug_sv.log(l_module_name, 'l_email_addrs', l_email_addrs);
154 END IF;
155
156 IF l_mailhost IS NOT NULL
157 AND l_email_addrs IS NOT NULL
158 THEN
159 --{
160 l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
161
162 j := 1; l_start_pos := 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
163 if l_end_pos = 0 then
164 l_end_pos := lengthb(l_email_addrs) + 1;
165 end if;
166 l_recipient1 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
167
168 j := j+1; l_start_pos := l_end_pos + 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
169 if l_end_pos = 0 then
170 l_end_pos := lengthb(l_email_addrs) + 1;
171 end if;
172 l_recipient2 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
173
174 j := j+1; l_start_pos := l_end_pos + 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
175 if l_end_pos = 0 then
176 l_end_pos := lengthb(l_email_addrs) + 1;
177 end if;
178 l_recipient3 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
179
180 j := j+1; l_start_pos := l_end_pos + 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
181 if l_end_pos = 0 then
182 l_end_pos := lengthb(l_email_addrs) + 1;
183 end if;
184 l_recipient4 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
185
186 j := j+1; l_start_pos := l_end_pos + 1;
187 l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
188 if l_end_pos = 0 then
189 l_end_pos := lengthb(l_email_addrs) + 1;
190 end if;
191 l_recipient5 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
192
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.log(l_module_name, 'sender', l_sender);
195 WSH_DEBUG_SV.log(l_module_name, 'recipient1', l_recipient1);
196 WSH_DEBUG_SV.log(l_module_name, 'recipient2', l_recipient2);
197 WSH_DEBUG_SV.log(l_module_name, 'recipient3', l_recipient3);
198 WSH_DEBUG_SV.log(l_module_name, 'recipient4', l_recipient4);
199 WSH_DEBUG_SV.log(l_module_name, 'recipient5', l_recipient5);
200 END IF;
201
202 utl_smtp.helo(l_mail_conn, l_mailhost);
203
204 utl_smtp.mail(l_mail_conn, l_sender);
205
206 IF l_recipient1 IS NOT NULL THEN
207 utl_smtp.rcpt(l_mail_conn, l_recipient1);
208 END IF;
209
210
211 IF l_recipient2 IS NOT NULL THEN
212 utl_smtp.rcpt(l_mail_conn, l_recipient2);
213 END IF;
214
215 IF l_recipient3 IS NOT NULL THEN
216 utl_smtp.rcpt(l_mail_conn, l_recipient3);
217 END IF;
218
219 IF l_recipient4 IS NOT NULL THEN
220 utl_smtp.rcpt(l_mail_conn, l_recipient4);
221 END IF;
222
223 IF l_recipient5 IS NOT NULL THEN
224 utl_smtp.rcpt(l_mail_conn, l_recipient5);
225 END IF;
226
227 utl_smtp.data(l_mail_conn, message);
228
229 utl_smtp.quit(l_mail_conn);
230 ELSE
231 IF l_debug_on THEN
232 wsh_debug_sv.logmsg(l_module_name, 'Not sending mail. Server Name or Email id is null');
233 END IF;
234
235 --}
236 END IF;
237
238 IF l_debug_on THEN
239 wsh_debug_sv.pop(l_module_name);
240 END IF;
241
242 EXCEPTION
243 WHEN others THEN
244 IF l_debug_on THEN
245 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
246 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
247 END IF;
248 END Send_Mail;
249
250 /*===========================================================================
251 FUNCTION NAME: is_dcp_enabled
252
253 DESCRIPTION: This function returns the DCP profile
254
255 ===========================================================================*/
256
257 FUNCTION is_dcp_enabled RETURN NUMBER
258 IS
259 BEGIN
260
261 IF wsh_dcp_pvt.g_check_dcp IS NOT NULL
262 THEN
263 RETURN(wsh_dcp_pvt.g_check_dcp);
264 END IF;
265 --
266 wsh_dcp_pvt.g_check_dcp := nvl(fnd_profile.value('WSH_ENABLE_DCP'), 0);
267 --
268 RETURN wsh_dcp_pvt.g_check_dcp;
269
270 EXCEPTION
271 when others then
272 RETURN 0;
273 END is_dcp_enabled;
274
275 PROCEDURE Post_Process(p_action_code IN VARCHAR2,
276 p_raise_exception IN VARCHAR2)
277 IS
278 l_call_stack VARCHAR2(32767);
279 l_message VARCHAR2(32767);
280 l_debug_file Varchar2(32767);
281 l_debug_dir Varchar2(32767);
282 l_debug_on BOOLEAN;
283 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POST_PROCESS';
284 k NUMBER;
285 l_rollback_allowed VARCHAR2(1);
286 l_return_status VARCHAR2(30);
287 l_msg_count NUMBER;
288 l_msg_data VARCHAR2(32767);
289 l_conc_request_id NUMBER;
290 l_utl_file_locns VARCHAR2(32767);
291 l_module VARCHAR2(32767);
292 l_level NUMBER;
293 l_dir VARCHAR2(32767);
294 l_comma_pos NUMBER;
295 l_curr_msg_count NUMBER := 0;
296 l_debug_reset VARCHAR2(1) := 'N';
297 l_recipient1 VARCHAR2(32767);
298 l_recipient2 VARCHAR2(32767);
299 l_recipient3 VARCHAR2(32767);
300 l_temp_message VARCHAR2(32767);
301 CURSOR c_user_info(p_user_id IN NUMBER) IS
302 SELECT user_name, email_address
303 FROM fnd_user
304 WHERE user_id = p_user_id;
305
306 CURSOR c_utl_file IS
307 SELECT value from v$parameter
308 WHERE lower(name) = 'utl_file_dir';
309
310 CURSOR c_env IS
311 SELECT name from v$database;
312
313 l_om_debug_enabled VARCHAR2(30);
314
315 BEGIN
316 --
317 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
318 --
319 IF l_debug_on IS NULL
320 THEN
321 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
322 END IF;
323 --
324 IF l_debug_on THEN
325 WSH_DEBUG_SV.push(l_module_name);
326 WSH_DEBUG_SV.log(l_module_name, 'p_action_code', p_action_code);
327 WSH_DEBUG_SV.log(l_module_name, 'p_raise_exception', p_raise_Exception);
328 END IF;
329
330 -- NO NEED TO CHECK USER EMAIL. USING HARDCODED EMAIL IDS
331 IF g_userid IS NULL THEN
332 fnd_profile.get('USER_ID',g_userid);
333 OPEN c_user_info(g_userid);
334 FETCH c_user_info INTO g_user_name, g_user_email;
335 CLOSE c_user_info;
336 END IF;
337
338 IF g_env IS NULL THEN
339 OPEN c_env;
340 FETCH c_env INTO g_env;
341 CLOSE c_env;
342 END IF;
343
344 IF p_action_code NOT IN ('SPLIT-LINE', 'CYCLE-COUNT', 'PACK') THEN
345 l_conc_request_id := fnd_global.conc_request_id;
346 END IF;
347
348 IF l_debug_on THEN
349 wsh_debug_sv.log(l_module_name, 'User Id', g_userid);
350 wsh_debug_sv.log(l_module_name, 'User Name', g_user_name);
351 wsh_debug_sv.log(l_module_name, 'Env', g_env);
352 END IF;
353
354
355 IF p_action_code IN('ITS', 'SPLIT-LINE', 'CYCLE-COUNT', 'PACK', 'AUTO-PACK', 'CONFIRM', 'OM') THEN
356 IF g_add_to_debug = 0 THEN
357 IF nvl(p_raise_exception, 'Y') = 'Y' THEN
358 l_rollback_allowed := 'Y';
359 ELSE
360 --Cases where ITS cannot be rerun.
361 l_rollback_allowed := 'N';
362 g_add_to_debug := 1;
363 END IF;
364 ELSE
365 l_rollback_allowed := 'N';
366 END IF;
367 ELSE
368 l_rollback_allowed := 'N';
369 g_add_to_debug := 1;
370 END IF;
371
372 l_debug_dir := WSH_DEBUG_SV.g_dir;
373
374 IF g_dc_table.count > 0
375 AND l_rollback_allowed = 'Y' THEN
376 --{
377 fnd_profile.get('WSH_DEBUG_MODULE',l_module);
378 --
379 fnd_profile.get('WSH_DEBUG_LEVEL', l_level);
380 --
381 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_dir);
382
383
384 -- Check Debug directory is a valid location
385 -- Check Debug level is set to maximum i.e statement level
386 -- Check Debug module is set to %.
387
388 OPEN c_utl_file;
389 FETCH c_utl_file INTO l_utl_file_locns;
390 CLOSE c_utl_file;
391
392 --Check if debug directory is a valid directory for non-concurrent request transactions
393 IF INSTRB(l_utl_file_locns, l_debug_dir) = 0
394 AND nvl(l_conc_request_id, -1) = -1
395 THEN
396 --Debug directory is not a utl file location. Set debug directory.
397 l_comma_pos := INSTRB(l_utl_file_locns, ',');
398 l_debug_dir := SUBSTRB(l_utl_file_locns, 1, l_comma_pos-1);
399 fnd_profile.put('WSH_DEBUG_LOG_DIRECTORY', l_debug_dir);
400 IF l_debug_on THEN
401 l_debug_reset := 'Y';
402 END IF;
403 l_debug_on := FALSE;
404 END IF;
405
406 IF l_debug_on THEN
407 wsh_debug_sv.log(l_module_name, 'l_comma_pos', l_comma_pos);
408 END IF;
409
410 IF nvl(l_level, 9999) > WSH_DEBUG_SV.C_STMT_LEVEL THEN
411 --Debug level is either not set or is set to a higher level
412 --Need to set to statement level.
413 fnd_profile.put('WSH_DEBUG_LEVEL', WSH_DEBUG_SV.C_STMT_LEVEL);
414 IF l_debug_on THEN
415 l_debug_reset := 'Y';
416 END IF;
417 l_debug_on := FALSE;
418
419 END IF;
420 --}
421 END IF;
422
423 IF p_action_code NOT IN ('SPLIT-LINE', 'CYCLE-COUNT', 'PACK') THEN
424 l_conc_request_id := fnd_global.conc_request_id;
425 END IF;
426
427 IF l_debug_on THEN
428 wsh_debug_sv.log(l_module_name, 'l_debug_dir', l_debug_dir);
429 wsh_debug_sv.log(l_module_name, 'g_dc_table count' , g_dc_table.count);
430 wsh_debug_sv.log(l_module_name, 'l_rollback_allowed', l_rollback_allowed);
431 wsh_debug_sv.log(l_module_name, 'conc request id', l_conc_request_id);
432 WSH_DEBUG_SV.log(l_module_name, 'g_add_to_debug', g_add_to_debug);
433 END IF;
434
435 IF g_dc_table.count > 0
436 THEN
437 --{
438 --Get CallStack
439 l_call_stack := dbms_utility.format_call_stack;
440
441 IF NOT oe_debug_pub.ISDebugOn THEN
442 l_om_debug_enabled := 'N';
443 ELSE
444 l_om_debug_enabled := 'Y';
445 END IF;
446
447 IF l_debug_on THEN
448 wsh_debug_sv.log(l_module_name, 'l_om_debug_enabled', l_om_debug_enabled);
449 END IF;
450
451 -- turn debug on
452 IF NOT l_debug_on THEN
453 wsh_debug_sv.start_debugger(
454 x_file_name => l_debug_file,
455 x_return_status => l_return_status,
456 x_msg_count => l_msg_count,
457 x_msg_data => l_msg_data);
458
459 IF l_debug_reset = 'Y' THEN
460 G_DEBUG_STARTED := 'R';
461 ELSE
462 IF l_om_debug_enabled = 'N' THEN
463 --OM debug was not on initially, starting WSH debugger starts OM debug too
464 -- Set g_debug_started to B meaning Both.
465 G_DEBUG_STARTED := 'B';
466 ELSE
467 --only shipping debug has been started.
468 G_DEBUG_STARTED := 'W';
469 END IF;
470 END IF;
471
472 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
473 ELSE
474 --WSH debug is already ON. Check if OM debug is ON.
475
476 IF l_om_debug_enabled = 'N' THEN
477
478 IF l_debug_on THEN
479 wsh_debug_sv.logmsg(l_module_name, 'Starting OM DEBUG');
480 wsh_debug_sv.log(l_module_name, 'Directory', wsh_debug_sv.g_dir);
481 wsh_debug_sv.log(l_module_name, 'File', wsh_debug_sv.g_file);
482 END IF;
483 oe_debug_pub.start_ont_debugger(
484 p_directory => wsh_debug_sv.g_dir,
485 p_filename => wsh_debug_sv.g_file,
486 p_file_handle => null);
487 --Only OM debug is being started
488 G_DEBUG_STARTED := 'O';
489 ELSE
490 g_add_to_debug := 1;
491 l_debug_file := WSH_DEBUG_SV.g_file;
492 END IF;
493
494 END IF;
495
496
497 IF l_debug_on THEN
498 wsh_debug_sv.log(l_module_name, 'G_DEBUG_STARTED', G_DEBUG_STARTED);
499 wsh_Debug_sv.log(l_module_name, 'g_add_to_debug', g_add_to_debug);
500 WSH_DEBUG_SV.log(l_module_name, 'p_action_code', p_action_code);
501 END IF;
502
503 IF g_add_to_debug > 0 THEN
504 --{
505
506 IF p_action_code IN ('SPLIT-LINE', 'CYCLE-COUNT', 'PICK-RELEASE', 'PACK',
507 'AUTO-PACK', 'ITS', 'CONFIRM')
508 THEN
509 l_message := 'Action performed : ' || p_action_code;
510 ELSIF p_action_code = 'OM' THEN
511 l_message := 'Action performed : ' || 'OM call to Shipping';
512 END IF;
513
514
515
516 if nvl(l_conc_request_id, -1) <> -1 then
517 l_message := l_message ||'
518 Data Inconsistency found in environment ' || g_env || ' for concurrent request id ' || l_conc_request_id || ' submitted by user ' || g_user_name;
519 else
520 l_message := l_message || '
521 Data Inconsistency found in environment ' || g_env || ' for a transaction run by user ' || g_user_name || ' Debug Dir = ' || l_debug_dir || ' Debug file for this transaction= ' || l_debug_file;
522 end if;
523 --
524
525
526 -- dump the call stack and pl/sql table
527 -- if global was set , turn debug off
528 -- Put CallStack in debug file
529 if l_debug_on then
530 wsh_debug_sv.log(l_module_name, 'l_call_stack', l_call_stack);
531 end if;
532
533 k := g_dc_table.first;
534
535 WHILE k is not null LOOP
536 --{
537 l_temp_message := k||'. Data Mismatch #'||g_dc_table(k).dcp_script||' Detected for Order No: '||g_dc_table(k).source_header_number ||' Line No: '||g_dc_table(k).source_line_number || ' Delivery Detail No: ' || g_dc_table(k).delivery_detail_id;
538
539 IF length(l_message) < 31900 THEN
540 l_message := l_message || '
541 ' || l_temp_message;
542 END IF;
543
544 IF l_debug_on THEN
545 wsh_debug_sv.logmsg(l_module_name, l_temp_message);
546 END IF;
547 k := g_dc_table.next(k);
548 --}
549 END LOOP;
550
551 IF instrb(get_email_server, 'oracle') > 0
552 AND length(l_message) < 32300 THEN
553 IF l_debug_on THEN
554 wsh_debug_sv.logmsg(l_module_name, 'Adding link to email message');
555 END IF;
556
557 l_temp_message := '---------------------------------------------------------------------
558 For a description of the data mismatch, please refer to the following link:
559 http://www-apps.us.oracle.com:1100/~kvenkate/DCP_Case_descriptions.html';
560
561 l_message := l_message || '
562 ' || l_temp_message;
563
564 END IF;
565
566 --Need to re-initialize g_add_to_debug because for ITS cases where multiple
567 --headers or multiple batches are being processed, the subsequent headers or batches
568 --need to have the correct initialization.
569 g_add_to_debug := 0;
570
571 --Send Email
572
573
574
575 Send_Mail(sender => l_recipient1,
576 recipient1 => l_recipient1,
577 recipient2 => l_recipient2,
578 recipient3 => l_recipient3,
579 message => l_message);
580 --}
581 END IF;
582
583 --Raise exception. Stop debugger if rollback is not possible
584 IF l_rollback_allowed = 'Y'
585 AND G_DEBUG_STARTED IN ('W', 'R', 'O', 'B')
586 AND nvl(p_raise_exception, 'Y') = 'Y' THEN
587 --increase global constant;
588 g_add_to_debug := g_add_to_debug + 1;
589
590 --Delete additional messages from stack.
591 l_curr_msg_count := fnd_msg_pub.count_msg;
592
593 if l_debug_on then
594 wsh_debug_sv.log(l_module_name, 'G_INIT_MSG_COUNT', G_INIT_MSG_COUNT);
595 wsh_debug_sv.log(l_module_name, 'l_curr_msg_count', l_curr_msg_count);
596 end if;
597
598 FOR k IN REVERSE (G_INIT_MSG_COUNT+1)..l_curr_msg_count LOOP
599 --{
600 fnd_msg_pub.delete_msg(p_msg_index => k);
601 --}
602 END LOOP;
603 if l_debug_on then
604 wsh_debug_sv.log(l_module_name, 'new count', fnd_msg_pub.count_msg);
605 end if;
606 RAISE dcp_caught;
607 ELSE
608
609 IF G_DEBUG_STARTED IN ('B', 'W') THEN
610 wsh_debug_interface.stop_debugger;
611 END IF;
612
613 IF G_DEBUG_STARTED IN ('B', 'O') THEN
614 oe_debug_pub.stop_ont_debugger;
615 END IF;
616 END IF;
617 --}
618 END IF;
619 IF l_debug_on THEN
620 wsh_debug_sv.pop(l_module_name);
621 END IF;
622 EXCEPTION
623 WHEN dcp_caught THEN
624 IF l_debug_on THEN
625 wsh_debug_sv.logmsg(l_module_name, 'DCP Caught: Post Process');
626 wsh_debug_sv.pop(l_module_name, 'Exception: dcp_caught');
627 END IF;
628 RAISE dcp_caught;
629 WHEN others THEN
630 IF l_debug_on THEN
631 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
632 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
633 END IF;
634 END Post_Process;
635
636 Procedure Check_Scripts(p_source_header_id IN NUMBER,
637 p_source_line_id IN NUMBER,
638 p_delivery_id IN NUMBER,
639 p_batch_id IN NUMBER,
640 x_data_inconsistent OUT NOCOPY VARCHAR2)
641 IS
642
643 l_rec t_dc_columns_rec_type;
644
645
646 CURSOR c_combine_hdr IS
647 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id,
648 wdd.released_status, wdd.requested_quantity, wdd.source_code, wdd.batch_id,
649 wdd.source_line_id, wdd.source_header_id, wdd.oe_interfaced_flag, wdd.inv_interfaced_flag,
650 wdd.ship_set_id, wdd.date_requested, wdd.date_scheduled, wdd.ship_to_contact_id,
651 wdd.ship_to_site_use_id, wdd.org_id, wdd.organization_id,
652 wdd.ship_tolerance_above, wdd.ship_tolerance_below,
653 wdd.picked_quantity, wdd.cycle_count_quantity, wdd.shipped_quantity detail_sq,
654 ol.line_id,
655 RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number,
656 ol.ordered_quantity, ol.cancelled_flag,
657 ol.ship_set_id ol_ship_set_id, ol.shipped_quantity, ol.flow_status_code, ol.open_flag,
658 ol.ship_from_org_id, ol.org_id ol_org_id,ol.schedule_ship_date, ol.request_date,
659 ol.shipping_interfaced_flag, ol.header_id,
660 ol.ship_to_contact_id ol_ship_to_contact_id, ol.ship_to_org_id,
661 ol.fulfilled_quantity, ol.invoiced_quantity,
662 oh.order_number, oh.ship_from_org_id,
663 wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
664 from wsh_delivery_details wdd,
665 oe_order_lines_all ol,
666 oe_order_headers_all oh,
667 wsh_delivery_assignments_v wda,
668 wsh_new_deliveries wnd,
669 wsh_delivery_legs wdl,
670 wsh_trip_stops wts
671 where wdd.source_code = 'OE'
672 and wdd.source_line_id = ol.line_id
673 and nvl(ol.shippable_flag, 'N') = 'Y'
674 and wdd.source_header_id = p_source_header_id
675 and ol.header_id = oh.header_id
676 and wdd.delivery_detail_id = wda.delivery_detail_id
677 and wda.delivery_id = wnd.delivery_id (+)
678 and wnd.delivery_id = wdl.delivery_id (+)
679 and wdl.pick_up_stop_id = wts.stop_id (+);
680
681
682 CURSOR c_combine_line IS
683 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id,
684 wdd.released_status, wdd.requested_quantity, wdd.source_code, wdd.batch_id,
685 wdd.source_line_id, wdd.source_header_id, wdd.oe_interfaced_flag, wdd.inv_interfaced_flag,
686 wdd.ship_set_id, wdd.date_requested, wdd.date_scheduled, wdd.ship_to_contact_id,
687 wdd.ship_to_site_use_id, wdd.org_id, wdd.organization_id,
688 wdd.ship_tolerance_above, wdd.ship_tolerance_below,
689 wdd.picked_quantity, wdd.cycle_count_quantity, wdd.shipped_quantity detail_sq,
690 ol.line_id,
691 RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number,
692 ol.ordered_quantity, ol.cancelled_flag,
693 ol.ship_set_id ol_ship_set_id, ol.shipped_quantity, ol.flow_status_code, ol.open_flag,
694 ol.ship_from_org_id, ol.org_id ol_org_id,ol.schedule_ship_date, ol.request_date,
695 ol.shipping_interfaced_flag, ol.header_id,
696 ol.ship_to_contact_id ol_ship_to_contact_id, ol.ship_to_org_id,
697 ol.fulfilled_quantity, ol.invoiced_quantity,
698 oh.order_number, oh.ship_from_org_id,
699 wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
700 from wsh_delivery_details wdd,
701 oe_order_lines_all ol,
702 oe_order_headers_all oh,
703 wsh_delivery_assignments_v wda,
704 wsh_new_deliveries wnd,
705 wsh_delivery_legs wdl,
706 wsh_trip_stops wts
707 where wdd.source_code = 'OE'
708 and wdd.source_line_id = ol.line_id
709 and nvl(ol.shippable_flag, 'N') = 'Y'
710 and wdd.source_line_id = p_source_line_id
711 and ol.header_id = oh.header_id
712 and wdd.delivery_detail_id = wda.delivery_detail_id
713 and wda.delivery_id = wnd.delivery_id (+)
714 and wnd.delivery_id = wdl.delivery_id (+)
715 and wdl.pick_up_stop_id = wts.stop_id (+);
716
717 CURSOR c_combine_dlvy IS
718 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id,
719 wdd.released_status, wdd.requested_quantity, wdd.source_code, wdd.batch_id,
720 wdd.source_line_id, wdd.source_header_id, wdd.oe_interfaced_flag, wdd.inv_interfaced_flag,
721 wdd.ship_set_id, wdd.date_requested, wdd.date_scheduled, wdd.ship_to_contact_id,
722 wdd.ship_to_site_use_id, wdd.org_id, wdd.organization_id,
723 wdd.ship_tolerance_above, wdd.ship_tolerance_below,
724 wdd.picked_quantity, wdd.cycle_count_quantity, wdd.shipped_quantity detail_sq,
725 ol.line_id,
726 RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number,
727 ol.ordered_quantity, ol.cancelled_flag,
728 ol.ship_set_id ol_ship_set_id, ol.shipped_quantity, ol.flow_status_code, ol.open_flag,
729 ol.ship_from_org_id, ol.org_id ol_org_id,ol.schedule_ship_date, ol.request_date,
730 ol.shipping_interfaced_flag, ol.header_id,
731 ol.ship_to_contact_id ol_ship_to_contact_id, ol.ship_to_org_id,
732 ol.fulfilled_quantity, ol.invoiced_quantity,
733 oh.order_number, oh.ship_from_org_id,
734 wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
735 from wsh_delivery_details wdd,
736 oe_order_lines_all ol,
737 oe_order_headers_all oh,
738 wsh_delivery_assignments_v wda,
739 wsh_new_deliveries wnd,
740 wsh_delivery_legs wdl,
741 wsh_trip_stops wts
742 where wdd.source_code = 'OE'
743 and wdd.source_line_id = ol.line_id
744 and nvl(ol.shippable_flag, 'N') = 'Y'
745 and ol.header_id = oh.header_id
746 and wda.delivery_id = p_delivery_id
747 and wdd.delivery_detail_id = wda.delivery_detail_id
748 and wda.delivery_id is not null
749 and wda.delivery_id = wnd.delivery_id (+)
750 and wnd.delivery_id = wdl.delivery_id (+)
751 and wdl.pick_up_stop_id = wts.stop_id (+);
752
753 CURSOR c_combine_batch IS
754 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id,
755 wdd.released_status, wdd.requested_quantity, wdd.source_code, wdd.batch_id,
756 wdd.source_line_id, wdd.source_header_id, wdd.oe_interfaced_flag, wdd.inv_interfaced_flag,
757 wdd.ship_set_id, wdd.date_requested, wdd.date_scheduled, wdd.ship_to_contact_id,
758 wdd.ship_to_site_use_id, wdd.org_id, wdd.organization_id,
759 wdd.ship_tolerance_above, wdd.ship_tolerance_below,
760 wdd.picked_quantity, wdd.cycle_count_quantity, wdd.shipped_quantity detail_sq,
761 ol.line_id,
762 RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number,
763 ol.ordered_quantity, ol.cancelled_flag,
764 ol.ship_set_id ol_ship_set_id, ol.shipped_quantity, ol.flow_status_code, ol.open_flag,
765 ol.ship_from_org_id, ol.org_id ol_org_id,ol.schedule_ship_date, ol.request_date,
766 ol.shipping_interfaced_flag, ol.header_id,
767 ol.ship_to_contact_id ol_ship_to_contact_id, ol.ship_to_org_id,
768 ol.fulfilled_quantity, ol.invoiced_quantity,
769 oh.order_number, oh.ship_from_org_id,
770 wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
771 from wsh_delivery_details wdd,
772 oe_order_lines_all ol,
773 oe_order_headers_all oh,
774 wsh_delivery_assignments_v wda,
775 wsh_new_deliveries wnd,
776 wsh_delivery_legs wdl,
777 wsh_trip_stops wts
778 where wdd.source_code = 'OE'
779 and wdd.source_line_id = ol.line_id
780 and nvl(ol.shippable_flag, 'N') = 'Y'
781 and ol.header_id = oh.header_id
782 and wdd.batch_id = p_batch_id
783 and wdd.delivery_detail_id = wda.delivery_detail_id
784 and wda.delivery_id = wnd.delivery_id (+)
785 and wnd.delivery_id = wdl.delivery_id (+)
786 and wdl.pick_up_stop_id = wts.stop_id (+);
787
788 CURSOR c2_hdr IS
789 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
790 from wsh_delivery_details wdd,
791 oe_order_lines_all ol
792 where wdd.source_code = 'OE'
793 and wdd.source_line_id = ol.line_id
794 and nvl(wdd.ship_tolerance_above,0) = 0
795 and nvl(wdd.ship_tolerance_below,0) = 0
796 and wdd.source_header_id = p_source_header_id
797 and (wdd.released_status = 'D' or wdd.requested_quantity = 0)
798 and (ol.ordered_quantity > 0 or ol.cancelled_flag = 'N')
799 and not exists (
800 select 'x'
801 from wsh_delivery_details wdd1
802 where wdd1.source_line_id = wdd.source_line_id
803 and wdd1.delivery_detail_id <> wdd.delivery_detail_id
804 and (wdd1.released_status <> 'D' or wdd1.requested_quantity > 0))
805 and nvl(ol.shippable_flag, 'N') = 'Y';
806
807 CURSOR c2_line IS
808 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
809 from wsh_delivery_details wdd,
810 oe_order_lines_all ol
811 where wdd.source_code = 'OE'
812 and wdd.source_line_id = ol.line_id
813 and nvl(wdd.ship_tolerance_above,0) = 0
814 and nvl(wdd.ship_tolerance_below,0) = 0
815 and wdd.source_line_id = p_source_line_id
816 and (wdd.released_status = 'D' or wdd.requested_quantity = 0)
817 and (ol.ordered_quantity > 0 or ol.cancelled_flag = 'N')
818 and not exists (
819 select 'x'
820 from wsh_delivery_details wdd1
821 where wdd1.source_line_id = wdd.source_line_id
822 and wdd1.delivery_detail_id <> wdd.delivery_detail_id
823 and (wdd1.released_status <> 'D' or wdd1.requested_quantity > 0))
824 and nvl(ol.shippable_flag, 'N') = 'Y';
825
826 CURSOR c2_dlvy IS
827 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
828 from wsh_delivery_details wdd,
829 oe_order_lines_all ol,
830 wsh_delivery_assignments_v wda
831 where wdd.source_code = 'OE'
832 and wdd.source_line_id = ol.line_id
833 and nvl(wdd.ship_tolerance_above,0) = 0
834 and nvl(wdd.ship_tolerance_below,0) = 0
835 and wdd.delivery_detail_id = wda.delivery_detail_id
836 and wda.delivery_id is not null
837 and wda.delivery_id = p_delivery_id
838 and (wdd.released_status = 'D' or wdd.requested_quantity = 0)
839 and (ol.ordered_quantity > 0 or ol.cancelled_flag = 'N')
840 and not exists (
841 select 'x'
842 from wsh_delivery_details wdd1
843 where wdd1.source_line_id = wdd.source_line_id
844 and wdd1.delivery_detail_id <> wdd.delivery_detail_id
845 and (wdd1.released_status <> 'D' or wdd1.requested_quantity > 0))
846 and nvl(ol.shippable_flag, 'N') = 'Y';
847
848 CURSOR c2_batch IS
849 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
850 from wsh_delivery_details wdd,
851 oe_order_lines_all ol
852 where wdd.source_code = 'OE'
853 and wdd.source_line_id = ol.line_id
854 and nvl(wdd.ship_tolerance_above,0) = 0
855 and nvl(wdd.ship_tolerance_below,0) = 0
856 and wdd.batch_id = p_batch_id
857 and (wdd.released_status = 'D' or wdd.requested_quantity = 0)
858 and (ol.ordered_quantity > 0 or ol.cancelled_flag = 'N')
859 and not exists (
860 select 'x'
861 from wsh_delivery_details wdd1
862 where wdd1.source_line_id = wdd.source_line_id
863 and wdd1.delivery_detail_id <> wdd.delivery_detail_id
864 and (wdd1.released_status <> 'D' or wdd1.requested_quantity > 0))
865 and nvl(ol.shippable_flag, 'N') = 'Y';
866
867
868 CURSOR C12_hdr IS
869 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
870 from wsh_delivery_details wdd,
871 oe_order_lines_all ol
872 where wdd.source_code = 'OE'
873 and wdd.source_header_id = p_source_header_id
874 and wdd.source_line_id = ol.line_id
875 and nvl(wdd.oe_interfaced_flag,'N') = 'N'
876 and exists (
877 select 'x'
878 from oe_order_lines_all ol1
879 where ol1.ship_set_id = wdd.ship_set_id
880 and ol1.header_id = wdd.source_header_id
881 and ol1.shipped_quantity is NOT NULL
882 and ol1.flow_status_code <> 'AWAITING_SHIPPING')
883 and wdd.ship_set_id is NOT NULL
884 and nvl(ol.shippable_flag, 'N') = 'Y'
885 and wdd.released_status <> 'D';
886
887 CURSOR C12_line IS
888 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
889 from wsh_delivery_details wdd,
890 oe_order_lines_all ol
891 where wdd.source_code = 'OE'
892 and wdd.source_line_id = p_source_line_id
893 and wdd.source_line_id = ol.line_id
894 and nvl(wdd.oe_interfaced_flag,'N') = 'N'
895 and exists (
896 select 'x'
897 from oe_order_lines_all ol1
898 where ol1.ship_set_id = wdd.ship_set_id
899 and ol1.header_id = wdd.source_header_id
900 and ol1.shipped_quantity is NOT NULL
901 and ol1.flow_status_code <> 'AWAITING_SHIPPING')
902 and wdd.ship_set_id is NOT NULL
903 and nvl(ol.shippable_flag, 'N') = 'Y'
904 and wdd.released_status <> 'D';
905
906 CURSOR C12_dlvy IS
907 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
908 from wsh_delivery_details wdd,
909 wsh_delivery_assignments_v wda,
910 oe_order_lines_all ol
911 where wdd.source_code = 'OE'
912 and wdd.source_line_id = ol.line_id
913 and wdd.delivery_detail_id = wda.delivery_detail_id
914 and wda.delivery_id is not null
915 and wda.delivery_id = p_delivery_id
916 and nvl(wdd.oe_interfaced_flag,'N') = 'N'
917 and exists (
918 select 'x'
919 from oe_order_lines_all ol1
920 where ol1.ship_set_id = wdd.ship_set_id
921 and ol1.header_id = wdd.source_header_id
922 and ol1.shipped_quantity is NOT NULL
923 and ol1.flow_status_code <> 'AWAITING_SHIPPING')
924 and wdd.ship_set_id is NOT NULL
925 and nvl(ol.shippable_flag, 'N') = 'Y'
926 and wdd.released_status <> 'D';
927
928 CURSOR C12_batch IS
929 select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
930 from wsh_delivery_details wdd,
931 oe_order_lines_all ol
932 where wdd.source_code = 'OE'
933 and wdd.batch_id = p_batch_id
934 and wdd.source_line_id = ol.line_id
935 and nvl(wdd.oe_interfaced_flag,'N') = 'N'
936 and exists (
937 select 'x'
938 from oe_order_lines_all ol1
939 where ol1.ship_set_id = wdd.ship_set_id
940 and ol1.header_id = wdd.source_header_id
941 and ol1.shipped_quantity is NOT NULL
942 and ol1.flow_status_code <> 'AWAITING_SHIPPING')
943 and wdd.ship_set_id is NOT NULL
944 and nvl(ol.shippable_flag, 'N') = 'Y'
945 and wdd.released_status <> 'D';
946
947
948 --Prompt 14. Lines that are interfaced to shipping but there is no corresponding delivery detail
949 --Cursor #6
950 CURSOR C6_hdr IS
951 select oh.order_number,
952 RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number
953 from oe_order_headers_all oh,
954 oe_order_lines_all ol
955 where oh.header_id = ol.header_id
956 and oh.header_id = p_source_header_id
957 and ol.shipping_interfaced_flag = 'Y'
958 and nvl(ol.shippable_flag, 'N') = 'Y'
959 and not exists (
960 select 'x'
961 from wsh_delivery_details wdd
962 where wdd.source_code = 'OE'
963 and wdd.source_line_id = ol.line_id);
964
965 CURSOR C6_line IS
966 select oh.order_number,
967 RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number
968 from oe_order_headers_all oh,
969 oe_order_lines_all ol
970 where oh.header_id = ol.header_id
971 and ol.line_id = p_source_line_id
972 and ol.shipping_interfaced_flag = 'Y'
973 and nvl(ol.shippable_flag, 'N') = 'Y'
974 and not exists (
975 select 'x'
976 from wsh_delivery_details wdd
977 where wdd.source_code = 'OE'
978 and wdd.source_line_id = ol.line_id);
979
980 --Prompt 32. Orphan reservations against closed lines
981 --Cursor #13
982 CURSOR C13_hdr IS
983 select oeh.order_number,
984 oel.line_id,
985 RTRIM(oel.line_number || '.' || oel.shipment_number || '.' || oel.option_number || '.' || oel.component_number || '.' || oel.service_number, '.') line_number
986 from oe_order_headers_all oeh,
987 oe_order_lines_all oel
988 where oeh.header_id = p_source_header_id
989 and oeh.header_id = oel.header_id
990 and oel.open_flag = 'N'
991 and nvl(oel.shippable_flag, 'N') = 'Y'
992 and exists (
993 select 'x'
994 from mtl_reservations mr
995 where mr.demand_source_line_id = oel.line_id
996 and mr.primary_reservation_quantity > 0
997 )
998 and not exists (
999 select 'x'
1000 from wsh_delivery_details wdd
1001 where wdd.source_line_id = oel.line_id
1002 and wdd.source_code = 'OE'
1003 and wdd.inv_interfaced_flag in ('N','P'))
1004 order by 1,2;
1005
1006 CURSOR C13_line IS
1007 select oeh.order_number,
1008 oel.line_id,
1009 RTRIM(oel.line_number || '.' || oel.shipment_number || '.' || oel.option_number || '.' || oel.component_number || '.' || oel.service_number, '.') line_number
1010 from oe_order_headers_all oeh,
1011 oe_order_lines_all oel
1012 where oel.line_id = p_source_line_id
1013 and oeh.header_id = oel.header_id
1014 and oel.open_flag = 'N'
1015 and nvl(oel.shippable_flag, 'N') = 'Y'
1016 and exists (
1017 select 'x'
1018 from mtl_reservations mr
1019 where mr.demand_source_line_id = oel.line_id
1020 and mr.primary_reservation_quantity > 0
1021 )
1022 and not exists (
1023 select 'x'
1024 from wsh_delivery_details wdd
1025 where wdd.source_line_id = oel.line_id
1026 and wdd.source_code = 'OE'
1027 and wdd.inv_interfaced_flag in ('N','P'))
1028 order by 1,2;
1029
1030 l_dummy varchar2(30);
1031 k NUMBER;
1032 l_debug_on BOOLEAN;
1033 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_SCRIPTS';
1034 BEGIN
1035 --
1036 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1037 --
1038 IF l_debug_on IS NULL
1039 THEN
1040 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1041 END IF;
1042 --
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.push(l_module_name);
1045 WSH_DEBUG_SV.log(l_module_name,' p_source_header_id', p_source_header_id);
1046 WSH_DEBUG_SV.log(l_module_name, 'p_source_line_id', p_source_line_id);
1047 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id', p_delivery_id);
1048 WSH_DEBUG_SV.log(l_module_name, 'p_batch_id', p_batch_id);
1049 END IF;
1050
1051 x_data_inconsistent := 'N';
1052
1053 k := g_dc_table.count;
1054
1055 IF p_source_line_id IS NOT NULL THEN
1056 OPEN c_combine_line;
1057 ELSIF p_source_header_id IS NOT NULL THEN
1058 OPEN c_combine_hdr;
1059 ELSIF p_delivery_id IS NOT NULL THEN
1060 OPEN c_combine_dlvy;
1061 ELSIF p_batch_id IS NOT NULL THEN
1062 OPEN c_combine_batch;
1063 END IF;
1064
1065 LOOP
1066 --{
1067
1068 IF c_combine_hdr%ISOPEN THEN
1069 FETCH c_combine_hdr INTO l_rec;
1070 EXIT WHEN c_combine_hdr%NOTFOUND;
1071 ELSIF c_combine_line%ISOPEN THEN
1072 FETCH c_combine_line INTO l_rec;
1073 EXIT WHEN c_combine_line%NOTFOUND;
1074 ELSIF c_combine_dlvy%ISOPEN THEN
1075 FETCH c_combine_dlvy INTO l_rec;
1076 EXIT WHEN c_combine_dlvy%NOTFOUND;
1077 ELSIF c_combine_batch%ISOPEN THEN
1078 FETCH c_combine_batch INTO l_rec;
1079 EXIT WHEN c_combine_batch%NOTFOUND;
1080 ELSE
1081 EXIT;
1082 END IF;
1083
1084 --check c1
1085 if (l_rec.ol_ordered_quantity = 0 or l_rec.ol_cancelled_flag = 'Y')
1086 and (l_rec.wdd_requested_quantity > 0 or l_rec.wdd_released_status <> 'D')
1087 then
1088 x_data_inconsistent := 'Y';
1089 k := k+1;
1090 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1091 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1092 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1093 g_dc_table(k).dcp_script := 'C1';
1094 end if;
1095
1096
1097 --Check c3
1098 if nvl(nvl(l_rec.ol_shipped_quantity, l_rec.ol_fulfilled_quantity), l_rec.ol_invoiced_quantity) > 0
1099 and l_rec.wdd_oe_interfaced_flag = 'N'
1100 and l_rec.wdd_released_status = 'C'
1101 then
1102 x_data_inconsistent := 'Y';
1103 k := k+1;
1104 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1105 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1106 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1107 g_dc_table(k).dcp_script := 'C3';
1108 end if;
1109
1110 --Check c4
1111 if nvl(l_rec.ol_shipped_quantity,0) = 0
1112 and l_rec.wdd_oe_interfaced_flag = 'Y'
1113 and l_rec.wdd_released_status = 'C'
1114 then
1115 x_data_inconsistent := 'Y';
1116 k := k+1;
1117 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1118 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1119 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1120 g_dc_table(k).dcp_script := 'C4';
1121
1122 end if;
1123
1124 --Check c5
1125 if nvl(l_rec.wdd_ship_set_id,-99) <> nvl(l_rec.ol_ship_set_id,-99)
1126 then
1127 x_data_inconsistent := 'Y';
1128 k := k+1;
1129 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1130 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1131 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1132 g_dc_table(k).dcp_script := 'C5';
1133
1134 end if;
1135
1136
1137
1138 --Check c7
1139 if l_rec.ol_shipping_interfaced_flag = 'N'
1140 and l_rec.wdd_delivery_detail_id IS NOT NULL
1141 then
1142 x_data_inconsistent := 'Y';
1143 k := k+1;
1144 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1145 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1146 g_dc_table(k).dcp_script := 'C7';
1147 end if;
1148
1149 --Check c8
1150 if nvl(l_rec.wdd_oe_interfaced_flag,'N') = 'N'
1151 and ( nvl(l_rec.wdd_date_scheduled, (sysdate - 50000) ) <> nvl(l_rec.ol_schedule_ship_date, (sysdate - 50000) ) or
1152 nvl(l_rec.wdd_date_requested,(sysdate - 50000)) <> nvl(l_rec.ol_request_date, (sysdate - 50000)) or
1153 nvl(l_rec.wdd_ship_to_contact_id,-99) <> nvl(l_rec.ol_ship_to_contact_id,-99) or
1154 nvl(l_rec.wdd_ship_to_site_use_id,-99) <> nvl(l_rec.ol_ship_to_org_id,-99)
1155 )
1156 then
1157 k := k+1;
1158 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1159 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1160 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1161 g_dc_table(k).dcp_script := 'C8';
1162
1163 if l_debug_on then
1164
1165 wsh_debug_sv.logmsg(l_module_name, 'DD attributes dates:' || to_char(l_rec.wdd_date_requested, 'dd-mon-yy hh24:mi:ss') || '-' || to_char(l_rec.wdd_date_scheduled, 'dd-mon-yy hh24:mi:ss'));
1166
1167 wsh_debug_sv.logmsg(l_module_name, 'DD attributes contact use:' || l_rec.wdd_ship_to_contact_id || '-' || l_rec.wdd_ship_to_site_use_id);
1168
1169 wsh_debug_sv.logmsg(l_module_name, 'OL attributes dates:' || to_char(l_rec.ol_request_date,'dd-mon-yy hh24:mi:ss') || '-' || to_char(l_rec.ol_schedule_ship_date, 'dd-mon-yy hh24:mi:ss'));
1170
1171 wsh_debug_sv.logmsg(l_module_name, 'OL attributes contact use:' || l_rec.ol_ship_to_contact_id || '-' || l_rec.ol_ship_to_org_id);
1172
1173 end if;
1174 end if;
1175
1176 --Check c9
1177 if nvl(l_rec.wdd_org_id,-99) <> nvl(l_rec.ol_org_id, -99)
1178 then
1179 x_data_inconsistent := 'Y';
1180 k := k+1;
1181 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1182 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1183 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1184 g_dc_table(k).dcp_script := 'C9 diff org' || '-' || l_rec.wdd_org_id || '-' || l_rec.ol_org_id;
1185 end if;
1186
1187 --Check c10
1188 if l_rec.oh_ship_from_org_id IS NULL
1189 and l_rec.ol_ship_from_org_id IS NULL
1190 and l_rec.ol_open_flag = 'Y'
1191 then
1192 x_data_inconsistent := 'Y';
1193 k := k+1;
1194 g_dc_table(k).source_header_number := l_rec.oh_order_number;
1195 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1196 g_dc_table(k).dcp_script := 'C10';
1197 end if;
1198
1199 --Check c11
1200 if nvl(l_rec.wdd_Organization_id, -99) <> nvl(l_rec.ol_ship_from_org_id,-99)
1201 then
1202 x_data_inconsistent := 'Y';
1203 k := k+1;
1204 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1205 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1206 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1207 g_dc_table(k).dcp_script := 'C11 diff organization' || '-' || l_rec.wdd_organization_id || '-' || l_rec.ol_ship_from_org_id;
1208 end if;
1209
1210
1211 -- Check c14
1212 if l_rec.ol_open_flag = 'N'
1213 and l_rec.wdd_released_status in ('R','N','X','S','B','Y')
1214 then
1215 x_data_inconsistent := 'Y';
1216 k := k+1;
1217 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1218 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1219 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1220 g_dc_table(k).dcp_script := 'C14';
1221 end if;
1222
1223 --check 15
1224 if l_rec.wdd_requested_quantity = 0
1225 and l_rec.wdd_released_status = 'R'
1226 then
1227 x_data_inconsistent := 'Y';
1228 k := k+1;
1229 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1230 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1231 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1232 g_dc_table(k).dcp_script := 'C15';
1233 end if;
1234
1235 --delivery checks
1236 if l_rec.wdd_released_status = 'Y'
1237 and l_rec.wnd_status_code = 'CL'
1238 then
1239 x_data_inconsistent := 'Y';
1240 k := k+1;
1241 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1242 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1243 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1244 g_dc_table(k).dcp_script := 'C16';
1245
1246 end if;
1247
1248
1249 --Shipped delivery details assigned to open delivery
1250 if l_rec.wdd_released_status = 'C'
1251 and l_rec.wnd_status_code = 'OP'
1252 then
1253 x_data_inconsistent := 'Y';
1254 k := k+1;
1255 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1256 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1257 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1258 g_dc_table(k).dcp_script := 'C17';
1259 end if;
1260
1261 --Confirmed(but not closed) deliveries are assigned to closed stops
1262 if l_rec.wnd_status_code = 'CO'
1263 and l_rec.wts_status_code = 'CL'
1264 then
1265 x_data_inconsistent := 'Y';
1266 k := k+1;
1267 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1268 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1269 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1270 g_dc_table(k).dcp_script := 'C18';
1271 end if;
1272
1273 --Delivery closed but no trip/stops created
1274 if l_rec.wnd_status_code IN ('CO','IT','CL')
1275 and l_rec.wdl_delivery_leg_id IS NULL
1276 then
1277 x_data_inconsistent := 'Y';
1278 k := k+1;
1279 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1280 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1281 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1282 g_dc_table(k).dcp_script := 'C19';
1283
1284 end if;
1285
1286 --Quantity Checks
1287 if l_rec.wdd_released_status IN ('S', 'B')
1288 and l_rec.wdd_cycle_count_quantity IS NOT NULL
1289 then
1290 x_data_inconsistent := 'Y';
1291 k := k+1;
1292 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1293 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1294 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1295 g_dc_table(k).dcp_script := 'C20';
1296 end if;
1297
1298 if l_rec.wdd_released_status IN ('X', 'Y')
1299 and nvl(l_rec.wdd_cycle_Count_quantity,0) > ((nvl(nvl(l_rec.wdd_picked_quantity, l_rec.wdd_requested_quantity),0) - nvl(l_rec.wdd_shipped_quantity,0)))
1300 and nvl(l_rec.wdd_cycle_count_quantity,0) > 0
1301 then
1302 x_data_inconsistent := 'Y';
1303 k := k+1;
1304 g_dc_table(k).source_header_number := l_rec.wdd_source_header_number;
1305 g_dc_table(k).source_line_number := l_rec.ol_line_number;
1306 g_dc_table(k).delivery_detail_id := l_rec.wdd_delivery_detail_id;
1307 g_dc_table(k).dcp_script := 'C21';
1308 end if;
1309
1310 --} begin of loop
1311 END LOOP;
1312
1313 IF c_combine_hdr%ISOPEN THEN
1314 CLOSE c_combine_hdr;
1315 ELSIF c_combine_line%ISOPEN THEN
1316 CLOSE c_combine_line;
1317 ELSIF c_combine_dlvy%ISOPEN THEN
1318 CLOSE c_combine_dlvy;
1319 ELSIF c_combine_batch%ISOPEN THEN
1320 CLOSE c_combine_batch;
1321 END IF;
1322
1323 --Check c2
1324 if p_source_line_id is not null then
1325 for c_rec in c2_line loop
1326 x_data_inconsistent := 'Y';
1327 k := k+1;
1328 g_dc_table(k).source_header_number := c_rec.source_header_number;
1329 g_dc_table(k).source_line_number := c_rec.source_line_number;
1330 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1331 g_dc_table(k).dcp_script := 'C2';
1332
1333 end loop;
1334
1335 for c_rec in c6_line loop
1336 x_data_inconsistent := 'Y';
1337 k := k+1;
1338 g_dc_table(k).source_header_number := c_rec.order_number;
1339 g_dc_table(k).source_line_number := c_rec.line_number;
1340 g_dc_table(k).dcp_script := 'C6';
1341 end loop;
1342
1343 for c_rec in c12_line loop
1344 x_data_inconsistent := 'Y';
1345 k := k+1;
1346 g_dc_table(k).source_header_number := c_rec.source_header_number;
1347 g_dc_table(k).source_line_number := c_rec.source_line_number;
1348 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1349 g_dc_table(k).dcp_script := 'C12';
1350
1351 end loop;
1352
1353
1354 for c_rec in c13_line loop
1355 x_data_inconsistent := 'Y';
1356 k := k+1;
1357 g_dc_table(k).source_header_number := c_rec.order_number;
1358 g_dc_table(k).source_line_number := c_rec.line_number;
1359 g_dc_table(k).dcp_script := 'C13';
1360 end loop;
1361 elsif p_source_header_id is not null then
1362 for c_rec in c2_hdr loop
1363 x_data_inconsistent := 'Y';
1364 k := k+1;
1365 g_dc_table(k).source_header_number := c_rec.source_header_number;
1366 g_dc_table(k).source_line_number := c_rec.source_line_number;
1367 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1368 g_dc_table(k).dcp_script := 'C2';
1369
1370 end loop;
1371
1372 for c_rec in c6_hdr loop
1373 x_data_inconsistent := 'Y';
1374 k := k+1;
1375 g_dc_table(k).source_header_number := c_rec.order_number;
1376 g_dc_table(k).source_line_number := c_rec.line_number;
1377 g_dc_table(k).dcp_script := 'C6';
1378 end loop;
1379
1380 for c_rec in c12_hdr loop
1381 x_data_inconsistent := 'Y';
1382 k := k+1;
1383 g_dc_table(k).source_header_number := c_rec.source_header_number;
1384 g_dc_table(k).source_line_number := c_rec.source_line_number;
1385 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1386 g_dc_table(k).dcp_script := 'C12';
1387
1388 end loop;
1389
1390 for c_rec in c13_hdr loop
1391 x_data_inconsistent := 'Y';
1392 k := k+1;
1393 g_dc_table(k).source_header_number := c_rec.order_number;
1394 g_dc_table(k).source_line_number := c_rec.line_number;
1395 g_dc_table(k).dcp_script := 'C13';
1396 end loop;
1397
1398 elsif p_delivery_id is not null then
1399 for c_rec in c2_dlvy loop
1400 x_data_inconsistent := 'Y';
1401 k := k+1;
1402 g_dc_table(k).source_header_number := c_rec.source_header_number;
1403 g_dc_table(k).source_line_number := c_rec.source_line_number;
1404 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1405 g_dc_table(k).dcp_script := 'C2';
1406
1407 end loop;
1408
1409 for c_rec in c12_dlvy loop
1410 x_data_inconsistent := 'Y';
1411 k := k+1;
1412 g_dc_table(k).source_header_number := c_rec.source_header_number;
1413 g_dc_table(k).source_line_number := c_rec.source_line_number;
1414 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1415 g_dc_table(k).dcp_script := 'C12';
1416
1417 end loop;
1418
1419 elsif p_batch_id is not null then
1420 for c_rec in c2_batch loop
1421 x_data_inconsistent := 'Y';
1422 k := k+1;
1423 g_dc_table(k).source_header_number := c_rec.source_header_number;
1424 g_dc_table(k).source_line_number := c_rec.source_line_number;
1425 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1426 g_dc_table(k).dcp_script := 'C2';
1427
1428 end loop;
1429
1430 for c_rec in c12_batch loop
1431 x_data_inconsistent := 'Y';
1432 k := k+1;
1433 g_dc_table(k).source_header_number := c_rec.source_header_number;
1434 g_dc_table(k).source_line_number := c_rec.source_line_number;
1435 g_dc_table(k).delivery_detail_id := c_rec.delivery_detail_id;
1436 g_dc_table(k).dcp_script := 'C12';
1437
1438 end loop;
1439
1440 end if;
1441
1442 IF l_debug_on THEN
1443 wsh_debug_sv.log(l_module_name, 'x_data_inconsistent', x_data_inconsistent);
1444 wsh_debug_sv.log(l_module_name, 'g_dc_table count', g_dc_table.count);
1445 END IF;
1446
1447
1448 IF l_debug_on THEN
1449 wsh_debug_sv.pop(l_module_name);
1450 END IF;
1451 EXCEPTION
1452 WHEN others THEN
1453 IF c_combine_hdr%ISOPEN THEN
1454 CLOSE c_combine_hdr;
1455 ELSIF c_combine_line%ISOPEN THEN
1456 CLOSE c_combine_line;
1457 ELSIF c_combine_dlvy%ISOPEN THEN
1458 CLOSE c_combine_dlvy;
1459 ELSIF c_combine_batch%ISOPEN THEN
1460 CLOSE c_combine_batch;
1461 END IF;
1462 IF l_debug_on THEN
1463 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1464 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1465 END IF;
1466 END Check_Scripts;
1467
1468 Procedure Check_ITS(p_bulk_mode IN VARCHAR2,
1469 p_start_index IN NUMBER,
1470 p_end_index IN NUMBER,
1471 p_its_rec IN OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type,
1472 p_raise_exception IN VARCHAR2)
1473 IS
1474 l_debug_on BOOLEAN;
1475 l_data_inconsistent VARCHAR2(1) := 'N';
1476
1477 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ITS';
1478 i NUMBER;
1479 l_header_id NUMBER := 0;
1480 BEGIN
1481 --
1482 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1483 --
1484 IF l_debug_on IS NULL
1485 THEN
1486 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1487 END IF;
1488 --
1489 IF l_debug_on THEN
1490 WSH_DEBUG_SV.push(l_module_name);
1491 WSH_DEBUG_SV.log(l_module_name, 'p_bulk_mode', p_bulk_mode);
1492 WSH_DEBUG_SV.log(l_module_name, 'p_start_index', p_start_index);
1493 WSH_DEBUG_SV.log(l_module_name, 'p_end_index', p_end_index);
1494 WSH_DEBUG_SV.log(l_module_name, 'p_raise_exception', p_raise_Exception);
1495 END IF;
1496
1497 g_dc_table.delete;
1498
1499 if p_bulk_mode = 'N' then
1500 i := p_its_rec.header_id.first;
1501 elsif p_bulk_mode = 'Y' then
1502 i := p_start_index;
1503 end if;
1504
1505 WHILE i IS NOT NULL
1506 AND i <= nvl(p_end_index, p_its_rec.header_id.count) LOOP
1507
1508 if p_its_rec.header_id(i) <> l_header_id then
1509
1510 check_scripts(
1511 p_source_header_id => p_its_rec.header_id(i),
1512 x_data_inconsistent => l_data_inconsistent);
1513
1514 l_header_id := p_its_rec.header_id(i);
1515 end if;
1516
1517 i := p_its_rec.header_id.next(i);
1518
1519 IF p_bulk_mode = 'N' THEN
1520 EXIT;
1521 END IF;
1522
1523 END LOOP;
1524
1525 Post_Process(p_action_code => 'ITS',
1526 p_raise_exception => p_raise_exception);
1527
1528
1529 IF l_debug_on THEN
1530 wsh_debug_sv.pop(l_module_name);
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN dcp_caught THEN
1535 if NOT l_debug_on OR l_debug_on IS NULL THEN
1536 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1537 end if;
1538 IF l_debug_on THEN
1539 WSH_DEBUG_SV.logmsg(l_module_name,'dcp_caught exception: CHECK_ITS');
1540 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DCP_CAUGHT: CHECK_ITS');
1541 END IF;
1542 RAISE data_inconsistency_exception;
1543 WHEN others THEN
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1546 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1547 END IF;
1548 END Check_ITS;
1549
1550 Procedure Check_Delivery(p_action_code IN VARCHAR2,
1551 p_dlvy_table IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type)
1552 IS
1553 l_debug_on BOOLEAN;
1554 l_data_inconsistent VARCHAR2(1) := 'N';
1555 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_DELIVERY';
1556 i NUMBER;
1557 BEGIN
1558 --
1559 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1560 --
1561 IF l_debug_on IS NULL
1562 THEN
1563 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1564 END IF;
1565 --
1566 IF l_debug_on THEN
1567 WSH_DEBUG_SV.push(l_module_name);
1568 WSH_DEBUG_SV.LOG(l_module_name, 'p_action_code', p_action_code);
1569 END IF;
1570
1571 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1572
1573 IF l_debug_on IS NULL THEN
1574 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1575 END IF;
1576
1577 g_dc_table.delete;
1578
1579 i := p_dlvy_table.first;
1580
1581 WHILE i IS NOT NULL LOOP
1582
1583 check_scripts(
1584 p_delivery_id => p_dlvy_table(i).delivery_id,
1585 x_data_inconsistent => l_data_inconsistent);
1586
1587 i := p_dlvy_table.next(i);
1588
1589 END LOOP;
1590
1591 Post_Process(p_action_code => p_action_code);
1592
1593 IF l_debug_on THEN
1594 wsh_debug_sv.pop(l_module_name);
1595 END IF;
1596
1597 EXCEPTION
1598 WHEN dcp_caught THEN
1599 if NOT l_debug_on OR l_debug_on IS NULL THEN
1600 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1601 end if;
1602 IF l_debug_on THEN
1603 WSH_DEBUG_SV.logmsg(l_module_name,'dcp_caught exception');
1604 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DCP_CAUGHT');
1605 END IF;
1606 RAISE data_inconsistency_exception;
1607 WHEN others THEN
1608 IF l_debug_on THEN
1609 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1610 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1611 END IF;
1612 END Check_Delivery;
1613
1614
1615 Procedure Check_Detail(p_action_code IN VARCHAR2,
1616 p_dtl_table IN wsh_glbl_var_strct_grp.Delivery_Details_Attr_Tbl_Type)
1617 IS
1618 l_debug_on BOOLEAN;
1619 l_data_inconsistent VARCHAR2(1) := 'N';
1620
1621 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_DETAIL';
1622 i NUMBER;
1623 BEGIN
1624 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1625 --
1626 IF l_debug_on IS NULL
1627 THEN
1628 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1629 END IF;
1630 --
1631 IF l_debug_on THEN
1632 WSH_DEBUG_SV.push(l_module_name);
1633 WSH_DEBUG_SV.LOG(l_module_name, 'p_action_code', p_action_code);
1634 END IF;
1635
1636 g_dc_table.delete;
1637
1638 i := p_dtl_table.first;
1639
1640 WHILE i IS NOT NULL LOOP
1641
1642 check_scripts(
1643 p_source_line_id => p_dtl_table(i).source_line_id,
1644 x_data_inconsistent => l_data_inconsistent);
1645
1646 i := p_dtl_table.next(i);
1647
1648 END LOOP;
1649
1650 Post_Process(p_action_code => p_action_code);
1651
1652 IF l_debug_on THEN
1653 wsh_debug_sv.pop(l_module_name);
1654 END IF;
1655
1656 EXCEPTION
1657 WHEN dcp_caught THEN
1658 if NOT l_debug_on OR l_debug_on IS NULL THEN
1659 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1660 end if;
1661 IF l_debug_on THEN
1662 WSH_DEBUG_SV.logmsg(l_module_name,'dcp_caught exception');
1663 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DCP_CAUGHT');
1664 END IF;
1665 RAISE data_inconsistency_exception;
1666 WHEN others THEN
1667 IF l_debug_on THEN
1668 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1669 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1670 END IF;
1671 END Check_Detail;
1672
1673 Procedure Check_Pick_Release(p_batch_id IN NUMBER) IS
1674 l_debug_on BOOLEAN;
1675 l_data_inconsistent VARCHAR2(1);
1676 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_PICK_RELEASE';
1677 BEGIN
1678 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1679 --
1680 IF l_debug_on IS NULL
1681 THEN
1682 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1683 END IF;
1684 --
1685 IF l_debug_on THEN
1686 WSH_DEBUG_SV.push(l_module_name);
1687 WSH_DEBUG_SV.LOG(l_module_name, 'p_BATCH_ID', p_batch_id);
1688 END IF;
1689
1690 g_dc_table.delete;
1691
1692 Check_Scripts(p_batch_id => p_batch_id,
1693 x_data_inconsistent => l_data_inconsistent);
1694
1695 Post_Process(p_action_code => 'PICK-RELEASE');
1696
1697 IF l_debug_on THEN
1698 wsh_debug_sv.pop(l_module_name);
1699 END IF;
1700
1701 EXCEPTION
1702 WHEN dcp_caught THEN
1703 if NOT l_debug_on OR l_debug_on IS NULL THEN
1704 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1705 end if;
1706 IF l_debug_on THEN
1707 WSH_DEBUG_SV.logmsg(l_module_name,'dcp_caught exception');
1708 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DCP_CAUGHT');
1709 END IF;
1710 RAISE data_inconsistency_exception;
1711 WHEN others THEN
1712 IF l_debug_on THEN
1713 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1714 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1715 END IF;
1716 END Check_Pick_Release;
1717
1718
1719 END WSH_DCP_PVT;