[Home] [Help]
PACKAGE BODY: APPS.RCV_DCP_PVT
Source
1 PACKAGE BODY rcv_dcp_pvt AS
5 g_user_email VARCHAR2(32767);
2 /* $Header: INVRDCPB.pls 120.14.12020000.2 2012/07/09 08:16:02 asugandh ship $ */
3 g_pkg_name CONSTANT VARCHAR2(50) := 'RCV_DCP_PVT';
4 g_userid NUMBER;
6 g_user_name VARCHAR2(32767);
7 g_env VARCHAR2(32767);
8 g_debug_on VARCHAR2(2) := asn_debug.is_debug_on; -- Bug 9152790
9 g_table_count NUMBER :=1;
10
11 FUNCTION get_email_server
12 RETURN VARCHAR2 IS
13 l_debug_on BOOLEAN;
14 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'GET_EMAIL_SERVER';
15 BEGIN
16 IF g_debug_on = 'Y' THEN
17 l_debug_on := TRUE;
18 ELSE
19 l_debug_on := FALSE;
20 END IF;
21
22 IF l_debug_on THEN
23 asn_debug.put_line('Entering ' || l_module_name);
24 END IF;
25
26 IF rcv_dcp_pvt.g_email_server IS NOT NULL THEN
27 IF l_debug_on THEN
28 asn_debug.put_line('Server name in cache:' || rcv_dcp_pvt.g_email_server);
29 END IF;
30
31 RETURN rcv_dcp_pvt.g_email_server;
32 END IF;
33
34 rcv_dcp_pvt.g_email_server := fnd_profile.VALUE('RCV_DCP_EMAIL_SERVER');
35
36 IF l_debug_on THEN
37 asn_debug.put_line('Server name profile :' || rcv_dcp_pvt.g_email_server);
38 END IF;
39
40 RETURN rcv_dcp_pvt.g_email_server;
41 EXCEPTION
42 WHEN OTHERS THEN
43 IF l_debug_on THEN
44 asn_debug.put_line('When others error has occurred. Oracle error message is ' || SQLERRM);
45 END IF;
46
47 RETURN NULL;
48 END get_email_server;
49
50 FUNCTION get_email_address
51 RETURN VARCHAR2 IS
52 l_debug_on BOOLEAN;
53 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'GET_EMAIL_ADDRESS';
54 BEGIN
55 IF g_debug_on = 'Y' THEN
56 l_debug_on := TRUE;
57 ELSE
58 l_debug_on := FALSE;
59 END IF;
60
61 IF l_debug_on THEN
62 asn_debug.put_line('Entering:' || l_module_name);
63 END IF;
64
65 IF rcv_dcp_pvt.g_email_address IS NOT NULL THEN
66 IF l_debug_on THEN
67 asn_debug.put_line('Email Address cache :' || rcv_dcp_pvt.g_email_address);
68 END IF;
69
70 RETURN rcv_dcp_pvt.g_email_address;
71 END IF;
72
73 rcv_dcp_pvt.g_email_address := fnd_profile.VALUE('RCV_DCP_EMAIL_ADDRESSES');
74
75 IF l_debug_on THEN
76 asn_debug.put_line('Email Address profile :' || rcv_dcp_pvt.g_email_address);
77 END IF;
78
79 RETURN rcv_dcp_pvt.g_email_address;
80 EXCEPTION
81 WHEN OTHERS THEN
82 IF l_debug_on THEN
83 asn_debug.put_line('When others error has occurred. Oracle error message is ' || SQLERRM);
84 END IF;
85
86 RETURN NULL;
87 END get_email_address;
88
89 PROCEDURE send_mail(
90 sender IN VARCHAR2
91 , recipient1 IN VARCHAR2
92 , recipient2 IN VARCHAR2
93 , recipient3 IN VARCHAR2
94 , recipient4 IN VARCHAR2
95 , MESSAGE IN VARCHAR2
96 ) IS
97 l_mailhost VARCHAR2(32767);
98 l_mail_conn UTL_SMTP.connection;
99 l_email_addrs VARCHAR2(32767);
100 l_spr VARCHAR2(30) := ',';
101 l_start_pos NUMBER;
102 l_end_pos NUMBER;
103 j NUMBER;
104 l_recipient1 VARCHAR2(32767);
105 l_recipient2 VARCHAR2(32767);
106 l_recipient3 VARCHAR2(32767);
107 l_recipient4 VARCHAR2(32767);
108 l_recipient5 VARCHAR2(32767);
109 l_sender VARCHAR2(32767) := 'Oracle-Logistics-Data-Integrity-Check@oraclelogistics';
110 l_debug_on BOOLEAN;
111 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'SEND_MAIL';
112
113 CURSOR c_env IS
114 SELECT NAME
115 FROM v$database;
116 BEGIN
117 --
118 IF g_debug_on = 'Y' THEN
119 l_debug_on := TRUE;
120 ELSE
121 l_debug_on := FALSE;
122 END IF;
123
124 FOR c_env_rec IN c_env LOOP
125 l_sender := l_sender || '-' || c_env_rec.NAME;
126 END LOOP;
127
128 IF l_debug_on THEN
129 asn_debug.put_line('Entering:' || l_module_name);
130 asn_debug.put_line('sender: ' || sender);
131 asn_debug.put_line('recipient1: ' || recipient1);
132 asn_debug.put_line('recipient2: ' || recipient2);
133 asn_debug.put_line('recipient3: ' || recipient3);
134 asn_debug.put_line('recipient4: ' || recipient4);
135 END IF;
136
137 --Call function that will return the email server name
138 l_mailhost := get_email_server;
139 --Call function that will return the email addresses
140 l_email_addrs := get_email_address;
141
142 --Parse to get individual recipients
143 IF l_debug_on THEN
144 asn_debug.put_line('l_mailhost: ' || l_mailhost);
145 asn_debug.put_line('l_email_addrs: ' || l_email_addrs);
146 END IF;
147
148 IF l_mailhost IS NOT NULL
149 AND l_email_addrs IS NOT NULL THEN
150 l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
151 j := 1;
152 l_start_pos := 1;
153 l_end_pos := INSTRB(l_email_addrs, l_spr, 1, j);
154
155 IF l_end_pos = 0 THEN
156 l_end_pos := LENGTHB(l_email_addrs) + 1;
160 j := j + 1;
157 END IF;
158
159 l_recipient1 := SUBSTRB(l_email_addrs, l_start_pos, l_end_pos - l_start_pos);
161 l_start_pos := l_end_pos + 1;
162 l_end_pos := INSTRB(l_email_addrs, l_spr, 1, j);
163
164 IF l_end_pos = 0 THEN
165 l_end_pos := LENGTHB(l_email_addrs) + 1;
166 END IF;
167
168 l_recipient2 := SUBSTRB(l_email_addrs, l_start_pos, l_end_pos - l_start_pos);
169 j := j + 1;
170 l_start_pos := l_end_pos + 1;
171 l_end_pos := INSTRB(l_email_addrs, l_spr, 1, j);
172
173 IF l_end_pos = 0 THEN
174 l_end_pos := LENGTHB(l_email_addrs) + 1;
175 END IF;
176
177 l_recipient3 := SUBSTRB(l_email_addrs, l_start_pos, l_end_pos - l_start_pos);
178 j := j + 1;
179 l_start_pos := l_end_pos + 1;
180 l_end_pos := INSTRB(l_email_addrs, l_spr, 1, j);
181
182 IF l_end_pos = 0 THEN
183 l_end_pos := LENGTHB(l_email_addrs) + 1;
184 END IF;
185
186 l_recipient4 := SUBSTRB(l_email_addrs, l_start_pos, l_end_pos - l_start_pos);
187 j := j + 1;
188 l_start_pos := l_end_pos + 1;
189 l_end_pos := INSTRB(l_email_addrs, l_spr, 1, j);
190
191 IF l_end_pos = 0 THEN
192 l_end_pos := LENGTHB(l_email_addrs) + 1;
193 END IF;
194
195 l_recipient5 := SUBSTRB(l_email_addrs, l_start_pos, l_end_pos - l_start_pos);
196
197 IF l_debug_on THEN
198 asn_debug.put_line('Now sender :' || l_sender);
199 asn_debug.put_line('Now recipient1: ' || l_recipient1);
200 asn_debug.put_line('Now recipient2: ' || l_recipient2);
201 asn_debug.put_line('Now recipient3: ' || l_recipient3);
202 asn_debug.put_line('Now recipient4: ' || l_recipient4);
203 asn_debug.put_line('Now recipient5: ' || l_recipient5);
204 END IF;
205
206 UTL_SMTP.helo(l_mail_conn, l_mailhost);
207 UTL_SMTP.mail(l_mail_conn, l_sender);
208
209 IF l_recipient1 IS NOT NULL THEN
210 UTL_SMTP.rcpt(l_mail_conn, l_recipient1);
211 END IF;
212
213 IF l_recipient2 IS NOT NULL THEN
214 UTL_SMTP.rcpt(l_mail_conn, l_recipient2);
215 END IF;
216
217 IF l_recipient3 IS NOT NULL THEN
218 UTL_SMTP.rcpt(l_mail_conn, l_recipient3);
219 END IF;
220
221 IF l_recipient4 IS NOT NULL THEN
222 UTL_SMTP.rcpt(l_mail_conn, l_recipient4);
223 END IF;
224
225 IF l_recipient5 IS NOT NULL THEN
226 UTL_SMTP.rcpt(l_mail_conn, l_recipient5);
227 END IF;
228
229 UTL_SMTP.DATA(l_mail_conn, MESSAGE);
230 UTL_SMTP.quit(l_mail_conn);
231 ELSE
232 IF l_debug_on THEN
233 asn_debug.put_line('Not sending mail. Server Name or Email id is null');
234 END IF;
235 END IF;
236
237 IF l_debug_on THEN
238 asn_debug.put_line('Exiting :' || l_module_name);
239 END IF;
240 EXCEPTION
241 WHEN OTHERS THEN
242 IF l_debug_on THEN
243 asn_debug.put_line('Unexpected error has occurred. Oracle error message is ' || SQLERRM);
244 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
245 END IF;
246 END send_mail;
247
248 /*===========================================================================
249 FUNCTION NAME: is_dcp_enabled
250
251 DESCRIPTION: This function returns the DCP profile
252
253 ===========================================================================*/
254 FUNCTION is_dcp_enabled
255 RETURN NUMBER IS
256 BEGIN
257 IF rcv_dcp_pvt.g_check_dcp IS NOT NULL THEN
258 RETURN(rcv_dcp_pvt.g_check_dcp);
259 END IF;
260
261 rcv_dcp_pvt.g_check_dcp := NVL(fnd_profile.VALUE('RCV_ENABLE_DCP'), 0);
262 RETURN rcv_dcp_pvt.g_check_dcp;
263 EXCEPTION
264 WHEN OTHERS THEN
265 RETURN 0;
266 END is_dcp_enabled;
267
268
269 PROCEDURE switch_debug(p_action IN VARCHAR2, p_file_name OUT NOCOPY VARCHAR2) IS
270 l_file_name VARCHAR2(2000);
271 l_dir_separator VARCHAR2(1);
272 l_debug_dir VARCHAR2(32767);
273 l_utl_file_locns VARCHAR2(32767);
274 L_NDX VARCHAR2(1);
275 l_idx NUMBER;
276 l_list VARCHAR2(32767);
277 l_comma_pos NUMBER;
278
279 CURSOR c_utl_file IS
280 SELECT rtrim(ltrim(value)) from v$parameter
281 WHERE lower(name) = 'utl_file_dir';
282
283 BEGIN
284 IF p_action = 'ON' THEN
285 -- Turn debug on
286 fnd_profile.put('INV_DEBUG_TRACE', '1');
287 fnd_profile.put('INV_DEBUG_LEVEL', '11');
288
289 -- Processing for INV Debug file
290 OPEN c_utl_file;
291 FETCH c_utl_file INTO l_utl_file_locns;
292 CLOSE c_utl_file;
293 l_dir_separator := '/';
294 l_ndx := instr(l_utl_file_locns,l_dir_separator);
295 IF (l_ndx = 0) then
296 l_dir_separator := '\';
297 END IF;
298 -- Validate that Filename in profile is correct
299 l_debug_dir := nvl(substr(g_inv_debug_file,1,instr(g_inv_debug_file,l_dir_separator,-1,1)-1),'-999');
300 IF (l_utl_file_locns <> '*') THEN
301 l_list := l_utl_file_locns;
302 LOOP
303 l_idx := instr(l_list,',');
304
305 IF l_idx > 0 THEN
306 IF l_debug_dir = rtrim(ltrim(SUBSTR(l_list,1,l_idx -1))) THEN
310 ELSE
307 IF g_inv_debug_enabled <> '1' THEN
308 -- Filename in profile is correct but debug is off, Lets generate a new filename
309 l_file_name := l_debug_dir||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
311 -- Filename in profile is correct, Lets use it
312 l_file_name := g_inv_debug_file;
313 END IF;
314 EXIT;
315 END IF;
316 l_list := SUBSTR(l_list,l_idx + 1);
317 ELSE
318 IF l_debug_dir = rtrim(ltrim(l_list)) THEN
319 IF g_inv_debug_enabled <> '1' THEN
320 -- Filename in profile is correct but debug is off, Lets generate a new filename
321 l_file_name := l_debug_dir||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
322 ELSE
323 -- Filename in profile is correct, Lets use it
324 l_file_name := g_inv_debug_file;
325 END IF;
326 ELSE
327 -- Filename in the profile is incorrect, generating a new filename.
328 l_comma_pos := INSTRB(l_utl_file_locns, ',');
329 IF l_comma_pos <> 0 THEN
330 l_debug_dir := SUBSTRB(l_utl_file_locns, 1, l_comma_pos-1);
331 ELSE
332 l_debug_dir := l_utl_file_locns;
333 END IF;
334 l_dir_separator := '/';
335 l_ndx := instr(l_debug_dir,l_dir_separator);
336 IF l_ndx = 0 THEN
337 l_dir_separator := '\';
338 END IF;
339 l_file_name := l_debug_dir||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
340 END IF;
341 EXIT;
342 END IF;
343 END LOOP;
344 ELSE
345 IF g_inv_debug_enabled <> '1' THEN
346 l_file_name :=l_dir_separator||'tmp'||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
347 ELSE
348 l_file_name :=g_inv_debug_file;
349 END IF;
350 END IF;
351
352 fnd_profile.put('INV_DEBUG_FILE',l_file_name);
353 p_file_name := l_file_name;
354 g_debug_started := 'Y';
355
356 ELSIF p_action = 'OFF' THEN
357 --Restore back the profiles
358 fnd_profile.put('INV_DEBUG_TRACE', g_inv_debug_enabled);
359 fnd_profile.put('INV_DEBUG_LEVEL', g_inv_debug_level);
360 fnd_profile.put('INV_DEBUG_FILE', g_inv_debug_file);
361 g_debug_started := 'N';
362 END IF;
363 END switch_debug;
364
365
366
367 PROCEDURE post_process(p_action_code IN VARCHAR2, p_raise_exception IN VARCHAR2) IS
368 l_call_stack VARCHAR2(32767);
369 l_message VARCHAR2(32767);
370 l_debug_file VARCHAR2(32767);
371 l_debug_dir VARCHAR2(32767);
372 l_debug_on BOOLEAN;
373 l_all_debug_on BOOLEAN;
374 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'POST_PROCESS';
375 k NUMBER;
376 l_rollback_allowed VARCHAR2(1);
377 l_return_status VARCHAR2(30);
378 l_msg_count NUMBER;
379 l_msg_data VARCHAR2(32767);
380 l_conc_request_id NUMBER;
381 l_module VARCHAR2(32767);
382 l_level NUMBER;
383 l_comma_pos NUMBER;
384 l_curr_msg_count NUMBER := 0;
385 l_recipient1 VARCHAR2(32767);
386 l_recipient2 VARCHAR2(32767);
387 l_recipient3 VARCHAR2(32767);
388 l_temp_message VARCHAR2(32767);
389 l_ndx VARCHAR2(1);
390 l_file_name VARCHAR2(2000);
391
392 crlf CONSTANT VARCHAR2(2) := fnd_global.local_CHR(13) || fnd_global.local_CHR(10);
393
394 CURSOR c_user_info(p_user_id IN NUMBER) IS
395 SELECT user_name
396 , email_address
397 FROM fnd_user
398 WHERE user_id = p_user_id;
399
400 CURSOR c_env IS
401 SELECT NAME
402 FROM v$database;
403 BEGIN
404 /***
405 a) Check if debug is ON
406 b) If rollback is allowed and debug is off then turn debug ON and Raise Exception
407 c) If rollback is allowed and debug is ON then collect all information and Finally Send email. If debug was turned on by DCP then turn it off.
408 d) If rollback is NOT allowed then collect information, put the information in a new debug file and Send email
409 *****/
410
411 --
412 IF g_debug_on = 'Y' THEN
413 l_debug_on := TRUE;
414 ELSE
415 l_debug_on := FALSE;
416 END IF;
417
418 IF g_debug_started <> 'Y' THEN
419 fnd_profile.get('INV_DEBUG_TRACE', g_inv_debug_enabled);
420 fnd_profile.get('INV_DEBUG_LEVEL', g_inv_debug_level);
421 fnd_profile.get('INV_DEBUG_FILE', g_inv_debug_file);
422 END IF;
423
424 IF (g_inv_debug_enabled = '1') or (g_debug_started = 'Y') THEN -- Bug 9152790
425 l_all_debug_on := TRUE;
426 ELSE
427 l_all_debug_on := FALSE;
428 END IF;
429
430 --
431 IF l_debug_on THEN
432 asn_debug.put_line('Entering :' || l_module_name);
433 asn_debug.put_line('p_action_code' || p_action_code);
434 asn_debug.put_line('p_raise_exception' || p_raise_exception);
435 END IF;
436
437 l_rollback_allowed := NVL(p_raise_exception, 'Y');
438
439 IF g_userid IS NULL THEN
440 fnd_profile.get('USER_ID', g_userid);
441 OPEN c_user_info(g_userid);
442 FETCH c_user_info INTO g_user_name, g_user_email;
443 CLOSE c_user_info;
444 END IF;
445
446 IF g_env IS NULL THEN
447 OPEN c_env;
448 FETCH c_env INTO g_env;
452 l_conc_request_id := fnd_global.conc_request_id;
449 CLOSE c_env;
450 END IF;
451
453
454 IF (l_conc_request_id = 0) THEN
455 l_conc_request_id := NULL;
456 END IF;
457
458 IF l_debug_on THEN
459 asn_debug.put_line('User Id' || g_userid);
460 asn_debug.put_line('User Name' || g_user_name);
461 asn_debug.put_line('Env' || g_env);
462 END IF;
463
464 IF (g_dc_table.COUNT > 0) THEN
465
466 IF NOT l_all_debug_on THEN
467 switch_debug(p_action => 'ON', p_file_name => g_file_name);
468 l_debug_on := TRUE;
469 g_debug_on := 'Y';
470
471 asn_debug.put_line('DCP - Started Debugger');
472 asn_debug.put_line('g_dc_table count :' || g_dc_table.COUNT);
473 asn_debug.put_line('l_rollback_allowed :' || l_rollback_allowed);
474 asn_debug.put_line('conc. request id :' || l_conc_request_id);
475
476 IF (l_rollback_allowed = 'Y') THEN
477 RAISE dcp_caught;
478 END IF;
479 ELSIF g_debug_started <> 'Y' THEN
480 g_file_name := g_inv_debug_file;
481 END IF;
482 --{
483 --Get CallStack
484 l_message := 'Subject: RCV Data inconsistency detected for ' || g_user_name || ' in ' || g_env || crlf || ' ' || crlf;
485 l_message := l_message || 'Action Performed:' || p_action_code;
486
487 IF NVL(l_conc_request_id, -1) <> -1 THEN
488 l_message :=
489 l_message
490 || '
491 Data Inconsistency found in environment '
492 || g_env
493 || ' for concurrent request id '
494 || l_conc_request_id
495 || ' submitted by user '
496 || g_user_name
497 || '. INV Debug file for this transaction = ' || g_file_name||'. '||crlf ||' ' || crlf
498 || '.';
499 ELSE
500 l_message :=
501 l_message || crlf || ' ' || crlf || 'Data Inconsistency found in environment ' || g_env || ' for a transaction run by user ' || g_user_name || '. INV Debug file for this transaction = ' || g_file_name||'. '||crlf ||' ' || crlf;
502 END IF;
503
504 --
505 -- dump the call stack and pl/sql table
506 -- if global was set , turn debug off
507 -- Put CallStack in debug file
508 IF l_debug_on THEN
509 l_call_stack := DBMS_UTILITY.format_call_stack;
510 asn_debug.put_line('**********Begining of Call Stack**********');
511 asn_debug.put_line(l_call_stack);
512 asn_debug.put_line('**********End of Call Stack**********');
513 END IF;
514
515
516 l_message := l_message || '********** Here are the Details **********' || crlf;
517 k := g_dc_table.FIRST;
518
519 WHILE k IS NOT NULL LOOP
520 --***************************************************************--
524 k
521 --{The g_dc_table contents need to be changed accordingly
522 --***************************************************************--
523 l_temp_message :=
525 || '. Data Mismatch #'
526 || g_dc_table(k).msg
527 || '.(To Org:'
528 || g_dc_table(k).to_organization_code
529 || ', Item:'
530 || g_dc_table(k).item_name
531 || ', Header Interface id:'
532 || g_dc_table(k).header_interface_id
533 || ', Interface Transaction id:'
534 || g_dc_table(k).interface_transaction_id
535 || ', Shipment header id:'
536 || g_dc_table(k).shipment_header_id
537 || ', Shipment line id:'
538 || g_dc_table(k).shipment_line_id
539 || ',';
540 l_temp_message :=
541 l_temp_message
542 || ' Trx Type:'
543 || g_dc_table(k).txn_type
544 || ', RHI receipt header id: '
545 || g_dc_table(k).rhi_receipt_header_id
546 || ', RHI processing status code: '
547 || g_dc_table(k).rhi_processing_status_code
548 || ', RHI receipt source code: '
549 || g_dc_table(k).rhi_receipt_source_code
550 || ', RHI asn type: '
551 || g_dc_table(k).rhi_asn_type
552 || ', RHI creation date: '
553 || g_dc_table(k).rhi_creation_date
554 || ', RSH asn type: '
555 || g_dc_table(k).rsh_asn_type
556 || ', RT transaction id:'
557 || g_dc_table(k).rt_transaction_id
558 || ', MMT transaction id:'
559 || g_dc_table(k).mmt_transaction_id
563 || g_dc_table(k).oel_flow_status_code
560 || ', OEL line id:'
561 || g_dc_table(k).oel_line_id
562 || ', OEL Flow status Code:'
564 || ', MOH header id:'
565 || g_dc_table(k).moh_header_id
566 || ', MOL line id:'
567 || g_dc_table(k).mol_line_id
568 || ', MSN serial number:'
569 || g_dc_table(k).msn_serial_number
570 || ', MSN current status:'
571 || g_dc_table(k).msn_current_status
572 || ', MSN last update date:'
573 || g_dc_table(k).msn_last_update_date
574 || ', WLPN LPN context:'
575 || g_dc_table(k).wlpn_lpn_context
576 || ', From org:'
577 || g_dc_table(k).from_organization_code
578 || ')';
579
580 IF LENGTH(l_message) < 31900 THEN
581 l_message := l_message || '
582 ' || l_temp_message || '. ';
583 END IF;
584
585 IF l_debug_on THEN
586 asn_debug.put_line(l_temp_message);
587 END IF;
588
589 k := g_dc_table.NEXT(k);
590 --}
591 END LOOP;
592
593 l_message := l_message ||crlf|| '********** End of the Details **********';
594 --Send Email
595 send_mail(sender => l_recipient1, recipient1 => l_recipient1, recipient2 => l_recipient2, recipient3 => l_recipient3
596 , MESSAGE => l_message);
597 g_dc_table.DELETE;
598 g_table_count := 1;
599 --}
600 --}
601 END IF;
602
603 -- Stop the debugger if it was started earlier
604 IF g_debug_started = 'Y' THEN
605 switch_debug(p_action => 'OFF', p_file_name => l_file_name);
606 IF l_debug_on THEN
607 asn_debug.put_line('DCP - Stopped Debugger');
608 g_debug_on := asn_debug.is_debug_on; -- Bug 9152790
609 END IF;
610 END IF;
611 EXCEPTION
612 WHEN dcp_caught THEN
613 IF l_debug_on THEN
614 asn_debug.put_line('DCP Caught: Post Process');
615 asn_debug.put_line('Exception: dcp_caught');
616 END IF;
617
618 RAISE dcp_caught;
619 WHEN OTHERS THEN
620 IF l_debug_on THEN
621 asn_debug.put_line('Unexpected error has occured. Oracle error message is ' || SQLERRM);
622 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
623 END IF;
624 END post_process;
625
626 PROCEDURE check_scripts(p_action_code IN VARCHAR2, p_header_interface_id IN NUMBER, p_interface_transaction_id IN NUMBER) IS
627 /*** Look for Data Mismatch and Add information to g_dc_table if found ***/
628 CURSOR c1 IS
629 SELECT 'RHI IN RUNNING WHEN THERE IS A ROW IN RSH'
630 , rhi.receipt_header_id
631 , rhi.ship_to_organization_id
632 , rhi.from_organization_id
633 , rhi.transaction_type
634 , rhi.processing_status_code
635 , rhi.receipt_source_code
636 , rhi.asn_type rhi_asn_type
637 , rsh.asn_type rsh_asn_type
638 , rhi.creation_date
639 FROM rcv_headers_interface rhi, rcv_shipment_headers rsh
640 WHERE rhi.header_interface_id = p_header_interface_id
641 AND rsh.shipment_header_id = rhi.receipt_header_id
642 AND rhi.processing_status_code IN('RUNNING', 'PENDING')
643 AND rhi.receipt_source_code in ('VENDOR','CUSTOMER')
644 AND nvl(rhi.asn_type, 'STD') = 'STD'
645 AND nvl(rsh.asn_type, '&&&&') NOT IN ('ASN','ASBN') ;
646
647
648 CURSOR c2 IS
649 SELECT 'Shipment line exists without a shipment header'
650 , rsl.shipment_line_id
651 , rti.item_id
652 , rti.to_organization_id
653 , rti.from_organization_id
654 , rti.transaction_type
655 FROM rcv_shipment_lines rsl, rcv_transactions_interface rti
656 WHERE rti.interface_transaction_id = p_interface_transaction_id
657 AND rti.shipment_line_id = rsl.shipment_line_id
658 AND NOT EXISTS(SELECT 1
659 FROM rcv_shipment_headers rsh
660 WHERE rsh.shipment_header_id = rsl.shipment_header_id);
661
662 --As per Maneesh's suggestion, commenting this check from DCP
663 /* CURSOR c3 IS
664 SELECT 'Shipment exists for Int Shp. or Internal Order without MMT'
665 , rsh.shipment_header_id
666 , rsl.shipment_line_id
667 , rsl.mmt_transaction_id
668 , rti.item_id
669 , rti.to_organization_id
670 , rti.from_organization_id
671 , rti.transaction_type
672 FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions_interface rti
673 WHERE rti.interface_transaction_id = p_interface_transaction_id
674 AND rti.shipment_line_id = rsl.shipment_line_id
678 AND NOT EXISTS(SELECT 1
675 AND rsh.shipment_header_id = rsl.shipment_header_id
676 AND rsh.receipt_source_code IN('INTERNAL ORDER', 'INVENTORY')
677 AND rsl.mmt_transaction_id IS NOT NULL
679 FROM mtl_material_transactions mmt
680 WHERE rsl.mmt_transaction_id = mmt.transaction_id);*/
681
682 CURSOR c4 IS
683 SELECT 'Flow status code is not awaiting return disposition for RMA receipt'
684 , rt.transaction_id
685 , oel.line_id
686 , rti.item_id
687 , rti.to_organization_id
688 , rti.from_organization_id
689 , rti.transaction_type
690 , oel.flow_status_code
691 FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
692 WHERE rti.interface_transaction_id = p_interface_transaction_id
693 AND rti.interface_transaction_id = rt.interface_transaction_id
694 AND oel.line_id = rt.oe_order_line_id
695 AND rti.receipt_source_code = 'CUSTOMER'
696 AND rt.transaction_type = 'RECEIVE'
697 AND nvl(rti.auto_transact_code, 'RECEIVE') = 'RECEIVE'
698 AND oel.flow_status_code = 'AWAITING_RETURN';
699
700
701
702 CURSOR c5 IS
703 SELECT 'Flow status code is not returned for RMA delivery'
704 , rt.transaction_id
705 , oel.line_id
706 , rti.item_id
707 , rti.to_organization_id
708 , rti.from_organization_id
709 , rti.transaction_type
710 , oel.flow_status_code
711 FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
712 WHERE rti.interface_transaction_id = p_interface_transaction_id
713 AND rti.interface_transaction_id = rt.interface_transaction_id
714 AND oel.line_id = rt.oe_order_line_id
715 AND rti.receipt_source_code = 'CUSTOMER'
716 AND rt.transaction_type = 'DELIVER'
717 AND oel.flow_status_code IN ('AWAITING_RETURN', 'AWAITING_RETURN_DISPOSITION')
718 AND oel.shipped_quantity = oel.fulfilled_quantity;
719
720 CURSOR c6 IS
721 SELECT 'MSN group mark id not null'
722 , msn.serial_number
723 , msn.inventory_item_id
724 , msn.current_organization_id
725 , rti.transaction_type
726 , msn.last_update_date
727 , msn.current_status
728 , wlpn.lpn_context
729 FROM mtl_serial_numbers msn,
730 rcv_transactions_interface rti,
731 mtl_serial_numbers_temp msnt,
732 wms_license_plate_numbers wlpn
733 WHERE rti.interface_transaction_id = p_interface_transaction_id
734 AND msnt.product_code = 'RCV'
735 AND msnt.product_transaction_id = rti.interface_transaction_id
736 AND msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
737 AND msn.inventory_item_id = rti.item_id
738 AND msn.current_organization_id = rti.to_organization_id
739 AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
740 AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
741 AND NVL(msn.group_mark_id ,-1) <> -1
742 UNION ALL
743 SELECT 'MSN group mark id not null'
744 , msn.serial_number
745 , msn.inventory_item_id
746 , msn.current_organization_id
747 , rt.transaction_type
748 , msn.last_update_date
749 , msn.current_status
750 , wlpn.lpn_context
751 FROM mtl_serial_numbers msn,
752 rcv_transactions rt,
753 mtl_unit_transactions mut,
754 wms_license_plate_numbers wlpn
755 WHERE rt.interface_transaction_id = p_interface_transaction_id
756 AND mut.product_code ='RCV'
757 AND mut.product_transaction_id = rt.transaction_id
758 AND msn.serial_number = mut.serial_number
759 AND msn.inventory_item_id = mut.inventory_item_id
760 AND msn.current_organization_id = mut.organization_id
761 AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
762 AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
763 AND NVL(msn.group_mark_id ,-1) <> -1
764 AND not exists
765 (select 1 from rcv_transactions rt1
766 where rt1.parent_transaction_id = rt.transaction_id);
767
768 CURSOR c7 IS
769 SELECT 'MOL exists with invalid wms_process_flag'
770 , mol.line_id
771 , moh.header_id
772 , mol.inventory_item_id
773 , rt.organization_id
774 , rt.transaction_type
775 FROM mtl_txn_request_lines mol, mtl_txn_request_headers moh, rcv_transactions rt
776 WHERE rt.interface_transaction_id = p_interface_transaction_id
777 AND rt.transaction_type = 'RECEIVE'
778 AND (mol.lpn_id = rt.transfer_lpn_id
779 OR mol.lpn_id = rt.lpn_id)
780 AND mol.line_status <> 5
781 AND mol.header_id = moh.header_id
782 AND moh.move_order_type = 6
783 AND mol.wms_process_flag = 2;
784
785 CURSOR c_org(p_org_id IN NUMBER) IS
786 SELECT organization_code
787 FROM org_organization_definitions
788 WHERE organization_id = p_org_id;
789
790 CURSOR c_item(p_org_id IN NUMBER, p_item_id IN NUMBER) IS
791 SELECT concatenated_segments
792 FROM mtl_system_items_kfv
793 WHERE inventory_item_id = p_item_id
794 AND organization_id = p_org_id;
795
796 l_debug_on BOOLEAN;
797 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'CHECK_SCRIPTS';
798 l_profile VARCHAR2(2000);
799 i NUMBER;
800 BEGIN
801 IF g_debug_on = 'Y' THEN
802 l_debug_on := TRUE;
803 ELSE
804 l_debug_on := FALSE;
805 END IF;
806 i := g_table_count;
807
808 IF (p_action_code = 'PREPROCESSOR') THEN ---{
809 FOR c1_rec IN c1 LOOP
810 g_dc_table(i).header_interface_id := p_header_interface_id;
811 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
812 g_dc_table(i).msg := 'C1=>RHI IN RUNNING WHEN THERE IS A ROW IN RSH';
813 g_dc_table(i).rhi_receipt_header_id := c1_rec.receipt_header_id;
814 g_dc_table(i).txn_type := c1_rec.transaction_type;
815 g_dc_table(i).rhi_processing_status_code:= c1_rec.processing_status_code;
816 g_dc_table(i).rhi_receipt_source_code := c1_rec.receipt_source_code ;
817 g_dc_table(i).rhi_asn_type := c1_rec.rhi_asn_type;
818 g_dc_table(i).rsh_asn_type := c1_rec.rsh_asn_type;
819 g_dc_table(i).rhi_creation_date := c1_rec.creation_date;
820
824 END LOOP;
821 IF (c1_rec.ship_to_organization_id IS NOT NULL) THEN
822 FOR c_org_rec IN c_org(c1_rec.ship_to_organization_id) LOOP
823 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
825 END IF;
826
827 IF (c1_rec.from_organization_id IS NOT NULL) THEN
828 FOR c_org_rec IN c_org(c1_rec.from_organization_id) LOOP
829 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
830 END LOOP;
831 END IF;
832
833 i := i + 1;
834 END LOOP;
835 ELSIF(p_action_code = 'Verify RTI') THEN ---{
836 FOR c2_rec IN c2 LOOP
837 g_dc_table(i).header_interface_id := p_header_interface_id;
838 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
839 g_dc_table(i).msg := 'C2=>Shipment line exists without a shipment header';
840 g_dc_table(i).shipment_line_id := c2_rec.shipment_line_id;
841 g_dc_table(i).txn_type := c2_rec.transaction_type;
842
843 IF (c2_rec.to_organization_id IS NOT NULL) THEN
844 FOR c_org_rec IN c_org(c2_rec.to_organization_id) LOOP
845 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
846 END LOOP;
847 END IF;
848
849 IF (c2_rec.from_organization_id IS NOT NULL) THEN
850 FOR c_org_rec IN c_org(c2_rec.from_organization_id) LOOP
851 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
852 END LOOP;
853 END IF;
854
855 IF (c2_rec.to_organization_id IS NOT NULL
856 AND c2_rec.item_id IS NOT NULL) THEN
857 FOR c_item_rec IN c_item(c2_rec.to_organization_id, c2_rec.item_id) LOOP
858 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
859 END LOOP;
860 END IF;
861
862 i := i + 1;
863 END LOOP;
864 --As per Maneesh's suggestion, commenting this check from DCP
865 /* FOR c3_rec IN c3 LOOP
866 g_dc_table(i).header_interface_id := p_header_interface_id;
867 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
868 g_dc_table(i).msg := 'C3=>Shipment exists for Int Shp. or Internal Order without MMT';
869 g_dc_table(i).shipment_header_id := c3_rec.shipment_header_id;
870 g_dc_table(i).shipment_line_id := c3_rec.shipment_line_id;
874 IF (c3_rec.to_organization_id IS NOT NULL) THEN
871 g_dc_table(i).mmt_transaction_id := c3_rec.mmt_transaction_id;
872 g_dc_table(i).txn_type := c3_rec.transaction_type;
873
875 FOR c_org_rec IN c_org(c3_rec.to_organization_id) LOOP
876 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
877 END LOOP;
878 END IF;
879
880 IF (c3_rec.from_organization_id IS NOT NULL) THEN
881 FOR c_org_rec IN c_org(c3_rec.from_organization_id) LOOP
882 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
883 END LOOP;
884 END IF;
885
886 IF (c3_rec.to_organization_id IS NOT NULL
887 AND c3_rec.item_id IS NOT NULL) THEN
888 FOR c_item_rec IN c_item(c3_rec.to_organization_id, c3_rec.item_id) LOOP
889 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
890 END LOOP;
891 END IF;
892
893 i := i + 1;
894 END LOOP;*/
895
896 FOR c4_rec IN c4 LOOP
897 g_dc_table(i).header_interface_id := p_header_interface_id;
898 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
899 g_dc_table(i).msg := 'C4=>Flow status code is not awaiting return disposition for RMA receipt';
900 g_dc_table(i).rt_transaction_id := c4_rec.transaction_id;
901 g_dc_table(i).oel_line_id := c4_rec.line_id;
902 g_dc_table(i).txn_type := c4_rec.transaction_type;
903 g_dc_table(i).oel_flow_status_code := c4_rec.flow_status_code;
904
905 IF (c4_rec.to_organization_id IS NOT NULL) THEN
906 FOR c_org_rec IN c_org(c4_rec.to_organization_id) LOOP
907 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
908 END LOOP;
909 END IF;
910
911 IF (c4_rec.from_organization_id IS NOT NULL) THEN
912 FOR c_org_rec IN c_org(c4_rec.from_organization_id) LOOP
913 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
914 END LOOP;
915 END IF;
916
917 IF (c4_rec.to_organization_id IS NOT NULL
918 AND c4_rec.item_id IS NOT NULL) THEN
919 FOR c_item_rec IN c_item(c4_rec.to_organization_id, c4_rec.item_id) LOOP
920 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
921 END LOOP;
922 END IF;
923
924 i := i + 1;
925 END LOOP;
926
927 FOR c5_rec IN c5 LOOP
928 g_dc_table(i).header_interface_id := p_header_interface_id;
929 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
930 g_dc_table(i).msg := 'C5=>Flow status code is not returned for RMA delivery';
931 g_dc_table(i).rt_transaction_id := c5_rec.transaction_id;
932 g_dc_table(i).oel_line_id := c5_rec.line_id;
933 g_dc_table(i).txn_type := c5_rec.transaction_type;
934 g_dc_table(i).oel_flow_status_code := c5_rec.flow_status_code;
935
936 IF (c5_rec.to_organization_id IS NOT NULL) THEN
937 FOR c_org_rec IN c_org(c5_rec.to_organization_id) LOOP
938 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
939 END LOOP;
940 END IF;
941
942 IF (c5_rec.from_organization_id IS NOT NULL) THEN
943 FOR c_org_rec IN c_org(c5_rec.from_organization_id) LOOP
944 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
945 END LOOP;
946 END IF;
947
948 IF (c5_rec.to_organization_id IS NOT NULL
949 AND c5_rec.item_id IS NOT NULL) THEN
950 FOR c_item_rec IN c_item(c5_rec.to_organization_id, c5_rec.item_id) LOOP
951 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
952 END LOOP;
953 END IF;
954
955 i := i + 1;
956 END LOOP;
957 ELSIF(p_action_code = 'Post WMS') THEN
958 FOR c6_rec IN c6 LOOP
959 g_dc_table(i).header_interface_id := p_header_interface_id;
960 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
961 g_dc_table(i).msg := 'C6=>MSN group mark id not null';
962 g_dc_table(i).msn_serial_number := c6_rec.serial_number;
963 g_dc_table(i).txn_type := c6_rec.transaction_type;
964 g_dc_table(i).msn_last_update_date := c6_rec.last_update_date;
965 g_dc_table(i).msn_current_status := c6_rec.current_status;
966 g_dc_table(i).wlpn_lpn_context := c6_rec.lpn_context;
967
968 IF (c6_rec.current_organization_id IS NOT NULL) THEN
969 FOR c_org_rec IN c_org(c6_rec.current_organization_id) LOOP
970 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
971 END LOOP;
972 END IF;
973
974 /* IF (c6_rec.from_organization_id IS NOT NULL) THEN
975 FOR c_org_rec IN c_org(c6_rec.from_organization_id) LOOP
976 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
977 END LOOP;
978 END IF;*/
979
980 IF (c6_rec.current_organization_id IS NOT NULL
981 AND c6_rec.inventory_item_id IS NOT NULL) THEN
982 FOR c_item_rec IN c_item(c6_rec.current_organization_id, c6_rec.inventory_item_id) LOOP
983 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
984 END LOOP;
985 END IF;
986
987 i := i + 1;
988 END LOOP;
989
990 FOR c7_rec IN c7 LOOP
991 g_dc_table(i).header_interface_id := p_header_interface_id;
992 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
993 g_dc_table(i).msg := 'C7=>MOL exists with invalid wms_process_flag';
994 g_dc_table(i).moh_header_id := c7_rec.header_id;
995 g_dc_table(i).mol_line_id := c7_rec.line_id;
996 g_dc_table(i).txn_type := c7_rec.transaction_type;
997
998 IF (c7_rec.organization_id IS NOT NULL) THEN
999 FOR c_org_rec IN c_org(c7_rec.organization_id) LOOP
1000 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
1001 END LOOP;
1002 END IF;
1003
1004 /* IF (c7_rec.from_organization_id IS NOT NULL) THEN
1005 FOR c_org_rec IN c_org(c7_rec.from_organization_id) LOOP
1006 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
1007 END LOOP;
1008 END IF;*/
1009
1010 IF (c7_rec.organization_id IS NOT NULL
1011 AND c7_rec.inventory_item_id IS NOT NULL) THEN
1012 FOR c_item_rec IN c_item(c7_rec.organization_id, c7_rec.inventory_item_id) LOOP
1013 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1014 END LOOP;
1015 END IF;
1016
1017 i := i + 1;
1018 END LOOP;
1019 END IF; ---}
1020
1021 g_table_count := i;
1022 IF (i > 1) THEN
1026 END IF;
1023 IF l_debug_on THEN
1024 asn_debug.put_line('Data is inconsistent');
1025 END IF;
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 IF l_debug_on THEN
1030 asn_debug.put_line('Unexpected error has occurred. Oracle error message is ' || SUBSTR(SQLERRM, 1, 180));
1031 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1032 END IF;
1033 END check_scripts;
1034
1035 PROCEDURE validate_data(
1036 p_dcp_event IN VARCHAR2
1037 -- , p_header_interface_id IN NUMBER DEFAULT NULL
1038 , p_request_id IN NUMBER DEFAULT NULL
1039 , p_group_id IN NUMBER DEFAULT NULL
1040 , p_interface_transaction_id IN NUMBER DEFAULT NULL
1041 , p_lpn_group_id IN NUMBER DEFAULT NULL
1042 , p_raise_exception IN VARCHAR2
1043 , x_return_status OUT NOCOPY VARCHAR2
1044 ) IS
1045 l_debug_on BOOLEAN;
1046 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'Validate_data';
1047 i NUMBER;
1048 l_header_id NUMBER := 0;
1049 CURSOR headers_cur_dcp(x_request_id NUMBER, x_group_id NUMBER) IS
1050 SELECT *
1051 FROM rcv_headers_interface
1052 WHERE NVL(asn_type, 'STD') IN('ASN', 'ASBN', 'STD', 'WC')
1053 AND processing_status_code IN('RUNNING', 'SUCCESS','ERROR','PENDING')
1054 AND(NVL(validation_flag, 'N') = 'Y'
1055 OR processing_status_code = 'SUCCESS') -- include success row for multi-line asn
1056 AND(processing_request_id IS NULL
1057 OR processing_request_id = x_request_id)
1058 AND GROUP_ID = DECODE(x_group_id, 0, GROUP_ID, x_group_id);
1059
1060 CURSOR dcp_cursor1(x_lpn_group_id NUMBER) IS
1061 SELECT distinct interface_transaction_id
1062 FROM rcv_transactions
1063 WHERE lpn_group_id = x_lpn_group_id;
1064
1065 CURSOR dcp_cursor2(x_group_id NUMBER) IS
1066 SELECT distinct interface_transaction_id
1067 FROM rcv_transactions
1068 WHERE group_id = x_group_id
1069 AND lpn_group_id is null;
1070
1071 BEGIN
1072 IF g_debug_on = 'Y' THEN
1073 l_debug_on := TRUE;
1074 ELSE
1075 l_debug_on := FALSE;
1076 END IF;
1077
1078 IF l_debug_on THEN
1079 asn_debug.put_line('p_request_id= ' || p_request_id);
1080 asn_debug.put_line('p_group_id= ' || p_group_id);
1081 asn_debug.put_line('p_lpn_group_id= ' || p_lpn_group_id);
1082 asn_debug.put_line('p_interface_transaction_id=' || p_interface_transaction_id);
1083 asn_debug.put_line('p_raise_exception' || p_raise_exception);
1084 END IF;
1085 IF p_dcp_event = 'PREPROCESSOR' THEN
1086 FOR header_cur_rec IN headers_cur_dcp(p_request_id, p_group_id) LOOP
1087 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => header_cur_rec.header_interface_id, p_interface_transaction_id => p_interface_transaction_id);
1088 END LOOP;
1089 ELSIF p_dcp_event = 'Post WMS' THEN
1090 IF (p_lpn_group_id is not null) THEN
1091 FOR dcp_cursor1_rec IN dcp_cursor1(p_lpn_group_id) LOOP
1092 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => NULL, p_interface_transaction_id => dcp_cursor1_rec.interface_transaction_id);
1093 END LOOP;
1094 ELSIF (p_group_id is not null) THEN
1095 FOR dcp_cursor2_rec IN dcp_cursor2(p_group_id) LOOP
1096 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => NULL, p_interface_transaction_id => dcp_cursor2_rec.interface_transaction_id);
1097 END LOOP;
1098 END IF;
1099 ELSE
1100 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => NULL, p_interface_transaction_id => p_interface_transaction_id);
1101 END IF;
1102 post_process(p_action_code => p_dcp_event, p_raise_exception => p_raise_exception);
1103 x_return_status := 'S';
1104 EXCEPTION
1105 WHEN dcp_caught THEN
1106 IF l_debug_on THEN
1107 asn_debug.put_line('dcp_caught exception: Validate_data');
1108 asn_debug.put_line('EXCEPTION:DCP_CAUGHT: Validate_data');
1109 END IF;
1110
1111 x_return_status := 'S';
1112 RAISE data_inconsistency_exception;
1113 WHEN OTHERS THEN
1114 IF l_debug_on THEN
1115 asn_debug.put_line('Unexpected error has occurred. Oracle error message is ' || SUBSTR(SQLERRM, 1, 180));
1116 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1117 x_return_status := 'E';
1118 END IF;
1119 END validate_data;
1120 END rcv_dcp_pvt;