[Home] [Help]
PACKAGE BODY: APPS.RCV_DCP_PVT
Source
1 PACKAGE BODY rcv_dcp_pvt AS
2 /* $Header: INVRDCPB.pls 120.10 2006/09/18 07:28:02 amohamme noship $ */
3 g_pkg_name CONSTANT VARCHAR2(50) := 'RCV_DCP_PVT';
4 g_userid NUMBER;
5 g_user_email VARCHAR2(32767);
6 g_user_name VARCHAR2(32767);
7 g_env VARCHAR2(32767);
8 g_debug_on VARCHAR2(2) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
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;
157 END IF;
158
159 l_recipient1 := SUBSTRB(l_email_addrs, l_start_pos, l_end_pos - l_start_pos);
160 j := j + 1;
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
256 BEGIN
253 ===========================================================================*/
254 FUNCTION is_dcp_enabled
255 RETURN NUMBER IS
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 /*
286 FND:Debug Log Enabled Yes
287 FND:Debug Log Level Statement
288 FND:Debug Log Module %
289 PO:Enable Sql Trace for Receiving Processor Yes
290 RCV:Debug Mode Yes
291 */
292 -- Turn debug on
293 fnd_profile.put('AFLOG_ENABLED', 'Y');
294 fnd_profile.put('AFLOG_LEVEL', '1');
295 fnd_profile.put('AFLOG_MODULE', '%');
296 fnd_profile.put('AFLOG_BUFFER_MODE', '1');
297 fnd_profile.put('PO_RVCTP_ENABLE_TRACE', 'Y');
298 fnd_profile.put('RCV_DEBUG_MODE', 'Y');
299 asn_debug.g_debugging_enabled := 'Y';
300 fnd_profile.put('INV_DEBUG_TRACE', '1');
301 fnd_profile.put('INV_DEBUG_LEVEL', '11');
302
303 -- Processing for INV Debug file
304 OPEN c_utl_file;
305 FETCH c_utl_file INTO l_utl_file_locns;
306 CLOSE c_utl_file;
307 l_dir_separator := '/';
308 l_ndx := instr(l_utl_file_locns,l_dir_separator);
309 IF (l_ndx = 0) then
310 l_dir_separator := '\';
311 END IF;
312 -- Validate that Filename in profile is correct
313 l_debug_dir := nvl(substr(g_inv_debug_file,1,instr(g_inv_debug_file,l_dir_separator,-1,1)-1),'-999');
314 IF (l_utl_file_locns <> '*') THEN
315 l_list := l_utl_file_locns;
316 LOOP
317 l_idx := instr(l_list,',');
318
319 IF l_idx > 0 THEN
320 IF l_debug_dir = rtrim(ltrim(SUBSTR(l_list,1,l_idx -1))) THEN
321 IF g_inv_debug_enabled <> '1' THEN
322 -- Filename in profile is correct but debug is off, Lets generate a new filename
323 l_file_name := l_debug_dir||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
324 ELSE
325 -- Filename in profile is correct, Lets use it
326 l_file_name := g_inv_debug_file;
327 END IF;
328 EXIT;
329 END IF;
330 l_list := SUBSTR(l_list,l_idx + 1);
331 ELSE
332 IF l_debug_dir = rtrim(ltrim(l_list)) THEN
333 IF g_inv_debug_enabled <> '1' THEN
334 -- Filename in profile is correct but debug is off, Lets generate a new filename
335 l_file_name := l_debug_dir||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
336 ELSE
337 -- Filename in profile is correct, Lets use it
338 l_file_name := g_inv_debug_file;
339 END IF;
340 ELSE
341 -- Filename in the profile is incorrect, generating a new filename.
342 l_comma_pos := INSTRB(l_utl_file_locns, ',');
343 IF l_comma_pos <> 0 THEN
344 l_debug_dir := SUBSTRB(l_utl_file_locns, 1, l_comma_pos-1);
345 ELSE
346 l_debug_dir := l_utl_file_locns;
347 END IF;
348 l_dir_separator := '/';
349 l_ndx := instr(l_debug_dir,l_dir_separator);
350 IF l_ndx = 0 THEN
351 l_dir_separator := '\';
352 END IF;
353 l_file_name := l_debug_dir||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
354 END IF;
355 EXIT;
356 END IF;
357 END LOOP;
358 ELSE
359 IF g_inv_debug_enabled <> '1' THEN
360 l_file_name :=l_dir_separator||'tmp'||l_dir_separator||'RCV_DCP'||userenv('SESSIONID')||'.dbg';
361 ELSE
362 l_file_name :=g_inv_debug_file;
363 END IF;
364 END IF;
365
366 fnd_profile.put('INV_DEBUG_FILE',l_file_name);
367 p_file_name := l_file_name;
368 g_debug_started := 'Y';
369
370 ELSIF p_action = 'OFF' THEN
371 --Restore back the profiles
372 fnd_profile.put('AFLOG_ENABLED', g_fnd_debug_enabled);
373 fnd_profile.put('AFLOG_LEVEL', g_fnd_debug_level);
374 fnd_profile.put('AFLOG_MODULE', g_fnd_debug_module);
375 fnd_profile.put('AFLOG_BUFFER_MODE', g_fnd_debug_mode);
376 fnd_profile.put('PO_RVCTP_ENABLE_TRACE', g_po_en_sql_trace);
377 fnd_profile.put('RCV_DEBUG_MODE', g_rcv_debug_enabled);
378 fnd_profile.put('INV_DEBUG_TRACE', g_inv_debug_enabled);
379 fnd_profile.put('INV_DEBUG_LEVEL', g_inv_debug_level);
380 fnd_profile.put('INV_DEBUG_FILE', g_inv_debug_file);
381 g_debug_started := 'N';
382 asn_debug.g_debugging_enabled := g_rcv_debug_enabled;
383 END IF;
384 END switch_debug;
385
386
387
388 PROCEDURE post_process(p_action_code IN VARCHAR2, p_raise_exception IN VARCHAR2) IS
389 l_call_stack VARCHAR2(32767);
390 l_message VARCHAR2(32767);
391 l_debug_file VARCHAR2(32767);
392 l_debug_dir VARCHAR2(32767);
396 k NUMBER;
393 l_debug_on BOOLEAN;
394 l_all_debug_on BOOLEAN;
395 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'POST_PROCESS';
397 l_rollback_allowed VARCHAR2(1);
398 l_return_status VARCHAR2(30);
399 l_msg_count NUMBER;
400 l_msg_data VARCHAR2(32767);
401 l_conc_request_id NUMBER;
402 l_module VARCHAR2(32767);
403 l_level NUMBER;
404 l_comma_pos NUMBER;
405 l_curr_msg_count NUMBER := 0;
406 l_recipient1 VARCHAR2(32767);
407 l_recipient2 VARCHAR2(32767);
408 l_recipient3 VARCHAR2(32767);
409 l_temp_message VARCHAR2(32767);
410 l_ndx VARCHAR2(1);
411 l_file_name VARCHAR2(2000);
412
413 crlf CONSTANT VARCHAR2(2) := fnd_global.local_CHR(13) || fnd_global.local_CHR(10);
414
415 CURSOR c_user_info(p_user_id IN NUMBER) IS
416 SELECT user_name
417 , email_address
418 FROM fnd_user
419 WHERE user_id = p_user_id;
420
421 CURSOR c_env IS
422 SELECT NAME
423 FROM v$database;
424 BEGIN
425 /***
426 a) Check if debug is ON
427 b) If rollback is allowed and debug is off then turn debug ON and Raise Exception
428 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.
429 d) If rollback is NOT allowed then collect information, put the information in a new debug file and Send email
430 *****/
431
432 --
433 IF g_debug_on = 'Y' THEN
434 l_debug_on := TRUE;
435 ELSE
436 l_debug_on := FALSE;
437 END IF;
438
439 IF g_debug_started <> 'Y' THEN
440 fnd_profile.get('AFLOG_ENABLED', g_fnd_debug_enabled);
441 fnd_profile.get('AFLOG_LEVEL', g_fnd_debug_level);
442 fnd_profile.get('AFLOG_MODULE', g_fnd_debug_module);
443 fnd_profile.get('AFLOG_BUFFER_MODE', g_fnd_debug_mode);
444 fnd_profile.get('PO_RVCTP_ENABLE_TRACE', g_po_en_sql_trace);
445 fnd_profile.get('INV_DEBUG_TRACE', g_inv_debug_enabled);
446 fnd_profile.get('INV_DEBUG_LEVEL', g_inv_debug_level);
447 fnd_profile.get('INV_DEBUG_FILE', g_inv_debug_file);
448 fnd_profile.get('RCV_DEBUG_MODE', g_rcv_debug_enabled);
449 END IF;
450
451
452 IF (g_fnd_debug_enabled = 'Y' and g_fnd_debug_level = '1' and (g_fnd_debug_module = '%' or g_fnd_debug_module = '%') and g_inv_debug_enabled = '1' and g_rcv_debug_enabled = 'Y') or (g_debug_started = 'Y') THEN
453 l_all_debug_on := TRUE;
454 ELSE
455 l_all_debug_on := FALSE;
456 END IF;
457
458 --
459 IF l_debug_on THEN
460 asn_debug.put_line('Entering :' || l_module_name);
461 asn_debug.put_line('p_action_code' || p_action_code);
462 asn_debug.put_line('p_raise_exception' || p_raise_exception);
463 END IF;
464
465 l_rollback_allowed := NVL(p_raise_exception, 'Y');
466
467 IF g_userid IS NULL THEN
468 fnd_profile.get('USER_ID', g_userid);
469 OPEN c_user_info(g_userid);
470 FETCH c_user_info INTO g_user_name, g_user_email;
471 CLOSE c_user_info;
472 END IF;
473
474 IF g_env IS NULL THEN
475 OPEN c_env;
476 FETCH c_env INTO g_env;
477 CLOSE c_env;
478 END IF;
479
480 l_conc_request_id := fnd_global.conc_request_id;
481
482 IF (l_conc_request_id = 0) THEN
483 l_conc_request_id := NULL;
484 END IF;
485
486 IF l_debug_on THEN
487 asn_debug.put_line('User Id' || g_userid);
488 asn_debug.put_line('User Name' || g_user_name);
489 asn_debug.put_line('Env' || g_env);
490 END IF;
491
492 IF (g_dc_table.COUNT > 0) THEN
493
494 IF NOT l_all_debug_on THEN
495 switch_debug(p_action => 'ON', p_file_name => g_file_name);
496 l_debug_on := TRUE;
497 g_debug_on := 'Y';
498
499 asn_debug.put_line('DCP - Started Debugger');
500 asn_debug.put_line('g_dc_table count :' || g_dc_table.COUNT);
501 asn_debug.put_line('l_rollback_allowed :' || l_rollback_allowed);
502 asn_debug.put_line('conc. request id :' || l_conc_request_id);
503
504 IF (l_rollback_allowed = 'Y') THEN
505 RAISE dcp_caught;
506 END IF;
507 ELSIF g_debug_started <> 'Y' THEN
508 g_file_name := g_inv_debug_file;
509 END IF;
510 --{
511 --Get CallStack
512 l_message := 'Subject: RCV Data inconsistency detected for ' || g_user_name || ' in ' || g_env || crlf || ' ' || crlf;
513 l_message := l_message || 'Action Performed:' || p_action_code;
514
515 IF NVL(l_conc_request_id, -1) <> -1 THEN
516 l_message :=
517 l_message
518 || '
519 Data Inconsistency found in environment '
520 || g_env
521 || ' for concurrent request id '
522 || l_conc_request_id
523 || ' submitted by user '
524 || g_user_name
528 l_message :=
525 || '. INV Debug file for this transaction = ' || g_file_name||'. '||crlf ||' ' || crlf
526 || '.';
527 ELSE
529 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;
530 END IF;
531
532 --
533 -- dump the call stack and pl/sql table
534 -- if global was set , turn debug off
535 -- Put CallStack in debug file
536 IF l_debug_on THEN
537 l_call_stack := DBMS_UTILITY.format_call_stack;
538 asn_debug.put_line('**********Begining of Call Stack**********');
539 asn_debug.put_line(l_call_stack);
540 asn_debug.put_line('**********End of Call Stack**********');
541 END IF;
542
543
544 l_message := l_message || '********** Here are the Details **********' || crlf;
545 k := g_dc_table.FIRST;
546
547 WHILE k IS NOT NULL LOOP
548 --***************************************************************--
549 --{The g_dc_table contents need to be changed accordingly
550 --***************************************************************--
551 l_temp_message :=
552 k
553 || '. Data Mismatch #'
554 || g_dc_table(k).msg
555 || '.(To Org:'
556 || g_dc_table(k).to_organization_code
557 || ', Item:'
558 || g_dc_table(k).item_name
559 || ', Header Interface id:'
560 || g_dc_table(k).header_interface_id
561 || ', Interface Transaction id:'
562 || g_dc_table(k).interface_transaction_id
563 || ', Shipment header id:'
564 || g_dc_table(k).shipment_header_id
565 || ', Shipment line id:'
566 || g_dc_table(k).shipment_line_id
567 || ',';
568 l_temp_message :=
569 l_temp_message
570 || ' Trx Type:'
571 || g_dc_table(k).txn_type
572 || ', RHI receipt header id: '
573 || g_dc_table(k).rhi_receipt_header_id
574 || ', RHI processing status code: '
575 || g_dc_table(k).rhi_processing_status_code
576 || ', RHI receipt source code: '
577 || g_dc_table(k).rhi_receipt_source_code
578 || ', RHI asn type: '
579 || g_dc_table(k).rhi_asn_type
580 || ', RHI creation date: '
581 || g_dc_table(k).rhi_creation_date
582 || ', RSH asn type: '
583 || g_dc_table(k).rsh_asn_type
584 || ', RT transaction id:'
585 || g_dc_table(k).rt_transaction_id
586 || ', MMT transaction id:'
587 || g_dc_table(k).mmt_transaction_id
588 || ', OEL line id:'
589 || g_dc_table(k).oel_line_id
590 || ', OEL Flow status Code:'
591 || g_dc_table(k).oel_flow_status_code
592 || ', MOH header id:'
593 || g_dc_table(k).moh_header_id
594 || ', MOL line id:'
595 || g_dc_table(k).mol_line_id
596 || ', MSN serial number:'
597 || g_dc_table(k).msn_serial_number
598 || ', MSN current status:'
599 || g_dc_table(k).msn_current_status
600 || ', MSN last update date:'
601 || g_dc_table(k).msn_last_update_date
602 || ', WLPN LPN context:'
603 || g_dc_table(k).wlpn_lpn_context
604 || ', From org:'
605 || g_dc_table(k).from_organization_code
606 || ')';
607
608 IF LENGTH(l_message) < 31900 THEN
609 l_message := l_message || '
610 ' || l_temp_message || '. ';
611 END IF;
612
613 IF l_debug_on THEN
614 asn_debug.put_line(l_temp_message);
615 END IF;
616
617 k := g_dc_table.NEXT(k);
618 --}
619 END LOOP;
620
621 l_message := l_message ||crlf|| '********** End of the Details **********';
622 --Send Email
623 send_mail(sender => l_recipient1, recipient1 => l_recipient1, recipient2 => l_recipient2, recipient3 => l_recipient3
624 , MESSAGE => l_message);
625 g_dc_table.DELETE;
626 g_table_count := 1;
627 --}
628 --}
629 END IF;
630
631 -- Stop the debugger if it was started earlier
632 IF g_debug_started = 'Y' THEN
633 switch_debug(p_action => 'OFF', p_file_name => l_file_name);
634 IF l_debug_on THEN
635 asn_debug.put_line('DCP - Stopped Debugger');
636 g_debug_on := g_rcv_debug_enabled;
637 END IF;
638 END IF;
639 EXCEPTION
640 WHEN dcp_caught THEN
641 IF l_debug_on THEN
642 asn_debug.put_line('DCP Caught: Post Process');
643 asn_debug.put_line('Exception: dcp_caught');
644 END IF;
645
646 RAISE dcp_caught;
647 WHEN OTHERS THEN
648 IF l_debug_on THEN
649 asn_debug.put_line('Unexpected error has occured. Oracle error message is ' || SQLERRM);
650 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
651 END IF;
652 END post_process;
653
657 SELECT 'RHI IN RUNNING WHEN THERE IS A ROW IN RSH'
654 PROCEDURE check_scripts(p_action_code IN VARCHAR2, p_header_interface_id IN NUMBER, p_interface_transaction_id IN NUMBER) IS
655 /*** Look for Data Mismatch and Add information to g_dc_table if found ***/
656 CURSOR c1 IS
658 , rhi.receipt_header_id
659 , rhi.ship_to_organization_id
660 , rhi.from_organization_id
661 , rhi.transaction_type
662 , rhi.processing_status_code
663 , rhi.receipt_source_code
664 , rhi.asn_type rhi_asn_type
665 , rsh.asn_type rsh_asn_type
666 , rhi.creation_date
667 FROM rcv_headers_interface rhi, rcv_shipment_headers rsh
668 WHERE rhi.header_interface_id = p_header_interface_id
669 AND rsh.shipment_header_id = rhi.receipt_header_id
670 AND rhi.processing_status_code IN('RUNNING', 'PENDING')
671 AND rhi.receipt_source_code in ('VENDOR','CUSTOMER')
672 AND nvl(rhi.asn_type, 'STD') = 'STD'
673 AND nvl(rsh.asn_type, '&&&&') NOT IN ('ASN','ASBN') ;
674
675
676 CURSOR c2 IS
677 SELECT 'Shipment line exists without a shipment header'
678 , rsl.shipment_line_id
679 , rti.item_id
680 , rti.to_organization_id
681 , rti.from_organization_id
682 , rti.transaction_type
683 FROM rcv_shipment_lines rsl, rcv_transactions_interface rti
684 WHERE rti.interface_transaction_id = p_interface_transaction_id
685 AND rti.shipment_line_id = rsl.shipment_line_id
686 AND NOT EXISTS(SELECT 1
687 FROM rcv_shipment_headers rsh
688 WHERE rsh.shipment_header_id = rsl.shipment_header_id);
689
690 --As per Maneesh's suggestion, commenting this check from DCP
691 /* CURSOR c3 IS
692 SELECT 'Shipment exists for Int Shp. or Internal Order without MMT'
693 , rsh.shipment_header_id
694 , rsl.shipment_line_id
695 , rsl.mmt_transaction_id
696 , rti.item_id
697 , rti.to_organization_id
698 , rti.from_organization_id
699 , rti.transaction_type
700 FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions_interface rti
701 WHERE rti.interface_transaction_id = p_interface_transaction_id
702 AND rti.shipment_line_id = rsl.shipment_line_id
703 AND rsh.shipment_header_id = rsl.shipment_header_id
704 AND rsh.receipt_source_code IN('INTERNAL ORDER', 'INVENTORY')
705 AND rsl.mmt_transaction_id IS NOT NULL
706 AND NOT EXISTS(SELECT 1
707 FROM mtl_material_transactions mmt
708 WHERE rsl.mmt_transaction_id = mmt.transaction_id);*/
709
710 CURSOR c4 IS
711 SELECT 'Flow status code is not awaiting return disposition for RMA receipt'
712 , rt.transaction_id
713 , oel.line_id
714 , rti.item_id
715 , rti.to_organization_id
716 , rti.from_organization_id
717 , rti.transaction_type
718 , oel.flow_status_code
719 FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
720 WHERE rti.interface_transaction_id = p_interface_transaction_id
721 AND rti.interface_transaction_id = rt.interface_transaction_id
722 AND oel.line_id = rt.oe_order_line_id
723 AND rti.receipt_source_code = 'CUSTOMER'
724 AND rt.transaction_type = 'RECEIVE'
725 AND nvl(rti.auto_transact_code, 'RECEIVE') = 'RECEIVE'
726 AND oel.flow_status_code = 'AWAITING_RETURN';
727
728
729
730 CURSOR c5 IS
731 SELECT 'Flow status code is not returned for RMA delivery'
732 , rt.transaction_id
733 , oel.line_id
734 , rti.item_id
735 , rti.to_organization_id
736 , rti.from_organization_id
737 , rti.transaction_type
738 , oel.flow_status_code
739 FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
740 WHERE rti.interface_transaction_id = p_interface_transaction_id
741 AND rti.interface_transaction_id = rt.interface_transaction_id
742 AND oel.line_id = rt.oe_order_line_id
743 AND rti.receipt_source_code = 'CUSTOMER'
744 AND rt.transaction_type = 'DELIVER'
745 AND oel.flow_status_code IN ('AWAITING_RETURN', 'AWAITING_RETURN_DISPOSITION')
746 AND oel.shipped_quantity = oel.fulfilled_quantity;
747
748 CURSOR c6 IS
749 SELECT 'MSN group mark id not null'
750 , msn.serial_number
751 , msn.inventory_item_id
752 , msn.current_organization_id
753 , rti.transaction_type
754 , msn.last_update_date
755 , msn.current_status
756 , wlpn.lpn_context
757 FROM mtl_serial_numbers msn,
758 rcv_transactions_interface rti,
759 mtl_serial_numbers_temp msnt,
760 wms_license_plate_numbers wlpn
761 WHERE rti.interface_transaction_id = p_interface_transaction_id
762 AND msnt.product_code = 'RCV'
763 AND msnt.product_transaction_id = rti.interface_transaction_id
764 AND msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
765 AND msn.inventory_item_id = rti.item_id
766 AND msn.current_organization_id = rti.to_organization_id
767 AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
768 AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
769 AND NVL(msn.group_mark_id ,-1) <> -1
770 UNION ALL
771 SELECT 'MSN group mark id not null'
772 , msn.serial_number
773 , msn.inventory_item_id
774 , msn.current_organization_id
775 , rt.transaction_type
776 , msn.last_update_date
780 rcv_transactions rt,
777 , msn.current_status
778 , wlpn.lpn_context
779 FROM mtl_serial_numbers msn,
781 mtl_unit_transactions mut,
782 wms_license_plate_numbers wlpn
783 WHERE rt.interface_transaction_id = p_interface_transaction_id
784 AND mut.product_code ='RCV'
785 AND mut.product_transaction_id = rt.transaction_id
786 AND msn.serial_number = mut.serial_number
787 AND msn.inventory_item_id = mut.inventory_item_id
788 AND msn.current_organization_id = mut.organization_id
789 AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
790 AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
791 AND NVL(msn.group_mark_id ,-1) <> -1
792 AND not exists
793 (select 1 from rcv_transactions rt1
794 where rt1.parent_transaction_id = rt.transaction_id);
795
796 CURSOR c7 IS
797 SELECT 'MOL exists with invalid wms_process_flag'
798 , mol.line_id
799 , moh.header_id
800 , mol.inventory_item_id
801 , rt.organization_id
802 , rt.transaction_type
803 FROM mtl_txn_request_lines mol, mtl_txn_request_headers moh, rcv_transactions rt
804 WHERE rt.interface_transaction_id = p_interface_transaction_id
805 AND rt.transaction_type = 'RECEIVE'
806 AND (mol.lpn_id = rt.transfer_lpn_id
807 OR mol.lpn_id = rt.lpn_id)
808 AND mol.line_status <> 5
809 AND mol.header_id = moh.header_id
810 AND moh.move_order_type = 6
811 AND mol.wms_process_flag = 2;
812
813 CURSOR c_org(p_org_id IN NUMBER) IS
814 SELECT organization_code
815 FROM org_organization_definitions
816 WHERE organization_id = p_org_id;
817
818 CURSOR c_item(p_org_id IN NUMBER, p_item_id IN NUMBER) IS
819 SELECT concatenated_segments
820 FROM mtl_system_items_kfv
821 WHERE inventory_item_id = p_item_id
822 AND organization_id = p_org_id;
823
824 l_debug_on BOOLEAN;
825 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'CHECK_SCRIPTS';
826 l_profile VARCHAR2(2000);
827 i NUMBER;
828 BEGIN
829 IF g_debug_on = 'Y' THEN
830 l_debug_on := TRUE;
831 ELSE
832 l_debug_on := FALSE;
833 END IF;
834 i := g_table_count;
835
836 IF (p_action_code = 'PREPROCESSOR') THEN ---{
837 FOR c1_rec IN c1 LOOP
838 g_dc_table(i).header_interface_id := p_header_interface_id;
839 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
840 g_dc_table(i).msg := 'C1=>RHI IN RUNNING WHEN THERE IS A ROW IN RSH';
841 g_dc_table(i).rhi_receipt_header_id := c1_rec.receipt_header_id;
842 g_dc_table(i).txn_type := c1_rec.transaction_type;
843 g_dc_table(i).rhi_processing_status_code:= c1_rec.processing_status_code;
844 g_dc_table(i).rhi_receipt_source_code := c1_rec.receipt_source_code ;
845 g_dc_table(i).rhi_asn_type := c1_rec.rhi_asn_type;
846 g_dc_table(i).rsh_asn_type := c1_rec.rsh_asn_type;
847 g_dc_table(i).rhi_creation_date := c1_rec.creation_date;
848
849 IF (c1_rec.ship_to_organization_id IS NOT NULL) THEN
850 FOR c_org_rec IN c_org(c1_rec.ship_to_organization_id) LOOP
851 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
852 END LOOP;
853 END IF;
854
855 IF (c1_rec.from_organization_id IS NOT NULL) THEN
856 FOR c_org_rec IN c_org(c1_rec.from_organization_id) LOOP
857 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
858 END LOOP;
859 END IF;
860
861 i := i + 1;
862 END LOOP;
863 ELSIF(p_action_code = 'Verify RTI') THEN ---{
864 FOR c2_rec IN c2 LOOP
865 g_dc_table(i).header_interface_id := p_header_interface_id;
866 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
867 g_dc_table(i).msg := 'C2=>Shipment line exists without a shipment header';
868 g_dc_table(i).shipment_line_id := c2_rec.shipment_line_id;
869 g_dc_table(i).txn_type := c2_rec.transaction_type;
870
871 IF (c2_rec.to_organization_id IS NOT NULL) THEN
872 FOR c_org_rec IN c_org(c2_rec.to_organization_id) LOOP
873 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
874 END LOOP;
875 END IF;
876
877 IF (c2_rec.from_organization_id IS NOT NULL) THEN
878 FOR c_org_rec IN c_org(c2_rec.from_organization_id) LOOP
879 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
880 END LOOP;
881 END IF;
882
883 IF (c2_rec.to_organization_id IS NOT NULL
884 AND c2_rec.item_id IS NOT NULL) THEN
885 FOR c_item_rec IN c_item(c2_rec.to_organization_id, c2_rec.item_id) LOOP
886 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
887 END LOOP;
888 END IF;
889
890 i := i + 1;
891 END LOOP;
892 --As per Maneesh's suggestion, commenting this check from DCP
893 /* FOR c3_rec IN c3 LOOP
894 g_dc_table(i).header_interface_id := p_header_interface_id;
895 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
896 g_dc_table(i).msg := 'C3=>Shipment exists for Int Shp. or Internal Order without MMT';
897 g_dc_table(i).shipment_header_id := c3_rec.shipment_header_id;
898 g_dc_table(i).shipment_line_id := c3_rec.shipment_line_id;
902 IF (c3_rec.to_organization_id IS NOT NULL) THEN
899 g_dc_table(i).mmt_transaction_id := c3_rec.mmt_transaction_id;
900 g_dc_table(i).txn_type := c3_rec.transaction_type;
901
903 FOR c_org_rec IN c_org(c3_rec.to_organization_id) LOOP
904 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
905 END LOOP;
906 END IF;
907
908 IF (c3_rec.from_organization_id IS NOT NULL) THEN
909 FOR c_org_rec IN c_org(c3_rec.from_organization_id) LOOP
910 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
911 END LOOP;
912 END IF;
913
914 IF (c3_rec.to_organization_id IS NOT NULL
915 AND c3_rec.item_id IS NOT NULL) THEN
916 FOR c_item_rec IN c_item(c3_rec.to_organization_id, c3_rec.item_id) LOOP
917 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
918 END LOOP;
919 END IF;
920
921 i := i + 1;
922 END LOOP;*/
923
924 FOR c4_rec IN c4 LOOP
925 g_dc_table(i).header_interface_id := p_header_interface_id;
926 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
927 g_dc_table(i).msg := 'C4=>Flow status code is not awaiting return disposition for RMA receipt';
928 g_dc_table(i).rt_transaction_id := c4_rec.transaction_id;
929 g_dc_table(i).oel_line_id := c4_rec.line_id;
930 g_dc_table(i).txn_type := c4_rec.transaction_type;
931 g_dc_table(i).oel_flow_status_code := c4_rec.flow_status_code;
932
933 IF (c4_rec.to_organization_id IS NOT NULL) THEN
934 FOR c_org_rec IN c_org(c4_rec.to_organization_id) LOOP
935 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
936 END LOOP;
937 END IF;
938
939 IF (c4_rec.from_organization_id IS NOT NULL) THEN
940 FOR c_org_rec IN c_org(c4_rec.from_organization_id) LOOP
941 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
942 END LOOP;
943 END IF;
944
945 IF (c4_rec.to_organization_id IS NOT NULL
946 AND c4_rec.item_id IS NOT NULL) THEN
947 FOR c_item_rec IN c_item(c4_rec.to_organization_id, c4_rec.item_id) LOOP
948 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
949 END LOOP;
950 END IF;
951
952 i := i + 1;
953 END LOOP;
954
955 FOR c5_rec IN c5 LOOP
956 g_dc_table(i).header_interface_id := p_header_interface_id;
957 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
958 g_dc_table(i).msg := 'C5=>Flow status code is not returned for RMA delivery';
959 g_dc_table(i).rt_transaction_id := c5_rec.transaction_id;
960 g_dc_table(i).oel_line_id := c5_rec.line_id;
961 g_dc_table(i).txn_type := c5_rec.transaction_type;
962 g_dc_table(i).oel_flow_status_code := c5_rec.flow_status_code;
963
964 IF (c5_rec.to_organization_id IS NOT NULL) THEN
965 FOR c_org_rec IN c_org(c5_rec.to_organization_id) LOOP
966 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
967 END LOOP;
968 END IF;
969
970 IF (c5_rec.from_organization_id IS NOT NULL) THEN
971 FOR c_org_rec IN c_org(c5_rec.from_organization_id) LOOP
972 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
973 END LOOP;
974 END IF;
975
976 IF (c5_rec.to_organization_id IS NOT NULL
977 AND c5_rec.item_id IS NOT NULL) THEN
978 FOR c_item_rec IN c_item(c5_rec.to_organization_id, c5_rec.item_id) LOOP
979 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
980 END LOOP;
981 END IF;
982
983 i := i + 1;
984 END LOOP;
985 ELSIF(p_action_code = 'Post WMS') THEN
986 FOR c6_rec IN c6 LOOP
987 g_dc_table(i).header_interface_id := p_header_interface_id;
988 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
989 g_dc_table(i).msg := 'C6=>MSN group mark id not null';
990 g_dc_table(i).msn_serial_number := c6_rec.serial_number;
991 g_dc_table(i).txn_type := c6_rec.transaction_type;
992 g_dc_table(i).msn_last_update_date := c6_rec.last_update_date;
993 g_dc_table(i).msn_current_status := c6_rec.current_status;
994 g_dc_table(i).wlpn_lpn_context := c6_rec.lpn_context;
995
999 END LOOP;
996 IF (c6_rec.current_organization_id IS NOT NULL) THEN
997 FOR c_org_rec IN c_org(c6_rec.current_organization_id) LOOP
998 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
1000 END IF;
1001
1002 /* IF (c6_rec.from_organization_id IS NOT NULL) THEN
1003 FOR c_org_rec IN c_org(c6_rec.from_organization_id) LOOP
1004 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
1005 END LOOP;
1006 END IF;*/
1007
1008 IF (c6_rec.current_organization_id IS NOT NULL
1009 AND c6_rec.inventory_item_id IS NOT NULL) THEN
1010 FOR c_item_rec IN c_item(c6_rec.current_organization_id, c6_rec.inventory_item_id) LOOP
1011 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1012 END LOOP;
1013 END IF;
1014
1015 i := i + 1;
1016 END LOOP;
1017
1018 FOR c7_rec IN c7 LOOP
1019 g_dc_table(i).header_interface_id := p_header_interface_id;
1020 g_dc_table(i).interface_transaction_id := p_interface_transaction_id;
1021 g_dc_table(i).msg := 'C7=>MOL exists with invalid wms_process_flag';
1022 g_dc_table(i).moh_header_id := c7_rec.header_id;
1023 g_dc_table(i).mol_line_id := c7_rec.line_id;
1024 g_dc_table(i).txn_type := c7_rec.transaction_type;
1025
1026 IF (c7_rec.organization_id IS NOT NULL) THEN
1027 FOR c_org_rec IN c_org(c7_rec.organization_id) LOOP
1028 g_dc_table(i).to_organization_code := c_org_rec.organization_code;
1029 END LOOP;
1030 END IF;
1031
1032 /* IF (c7_rec.from_organization_id IS NOT NULL) THEN
1033 FOR c_org_rec IN c_org(c7_rec.from_organization_id) LOOP
1034 g_dc_table(i).from_organization_code := c_org_rec.organization_code;
1035 END LOOP;
1036 END IF;*/
1037
1038 IF (c7_rec.organization_id IS NOT NULL
1039 AND c7_rec.inventory_item_id IS NOT NULL) THEN
1040 FOR c_item_rec IN c_item(c7_rec.organization_id, c7_rec.inventory_item_id) LOOP
1041 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1042 END LOOP;
1043 END IF;
1044
1045 i := i + 1;
1046 END LOOP;
1047 END IF; ---}
1048
1049 g_table_count := i;
1050 IF (i > 1) THEN
1051 IF l_debug_on THEN
1052 asn_debug.put_line('Data is inconsistent');
1053 END IF;
1054 END IF;
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 IF l_debug_on THEN
1058 asn_debug.put_line('Unexpected error has occurred. Oracle error message is ' || SUBSTR(SQLERRM, 1, 180));
1059 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1060 END IF;
1061 END check_scripts;
1062
1063 PROCEDURE validate_data(
1064 p_dcp_event IN VARCHAR2
1065 -- , p_header_interface_id IN NUMBER DEFAULT NULL
1066 , p_request_id IN NUMBER DEFAULT NULL
1067 , p_group_id IN NUMBER DEFAULT NULL
1068 , p_interface_transaction_id IN NUMBER DEFAULT NULL
1069 , p_lpn_group_id IN NUMBER DEFAULT NULL
1070 , p_raise_exception IN VARCHAR2
1071 , x_return_status OUT NOCOPY VARCHAR2
1072 ) IS
1073 l_debug_on BOOLEAN;
1074 l_module_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || 'Validate_data';
1075 i NUMBER;
1076 l_header_id NUMBER := 0;
1077 CURSOR headers_cur_dcp(x_request_id NUMBER, x_group_id NUMBER) IS
1078 SELECT *
1079 FROM rcv_headers_interface
1080 WHERE NVL(asn_type, 'STD') IN('ASN', 'ASBN', 'STD', 'WC')
1081 AND processing_status_code IN('RUNNING', 'SUCCESS','ERROR','PENDING')
1082 AND(NVL(validation_flag, 'N') = 'Y'
1083 OR processing_status_code = 'SUCCESS') -- include success row for multi-line asn
1084 AND(processing_request_id IS NULL
1085 OR processing_request_id = x_request_id)
1086 AND GROUP_ID = DECODE(x_group_id, 0, GROUP_ID, x_group_id);
1087
1088 CURSOR dcp_cursor1(x_lpn_group_id NUMBER) IS
1089 SELECT distinct interface_transaction_id
1090 FROM rcv_transactions
1091 WHERE lpn_group_id = x_lpn_group_id;
1092
1093 CURSOR dcp_cursor2(x_group_id NUMBER) IS
1094 SELECT distinct interface_transaction_id
1095 FROM rcv_transactions
1096 WHERE group_id = x_group_id
1097 AND lpn_group_id is null;
1098
1099 BEGIN
1100 IF g_debug_on = 'Y' THEN
1101 l_debug_on := TRUE;
1102 ELSE
1103 l_debug_on := FALSE;
1104 END IF;
1105
1106 IF l_debug_on THEN
1107 asn_debug.put_line('p_request_id= ' || p_request_id);
1108 asn_debug.put_line('p_group_id= ' || p_group_id);
1109 asn_debug.put_line('p_lpn_group_id= ' || p_lpn_group_id);
1110 asn_debug.put_line('p_interface_transaction_id=' || p_interface_transaction_id);
1111 asn_debug.put_line('p_raise_exception' || p_raise_exception);
1112 END IF;
1113 IF p_dcp_event = 'PREPROCESSOR' THEN
1114 FOR header_cur_rec IN headers_cur_dcp(p_request_id, p_group_id) LOOP
1115 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);
1116 END LOOP;
1117 ELSIF p_dcp_event = 'Post WMS' THEN
1118 IF (p_lpn_group_id is not null) THEN
1119 FOR dcp_cursor1_rec IN dcp_cursor1(p_lpn_group_id) LOOP
1120 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => NULL, p_interface_transaction_id => dcp_cursor1_rec.interface_transaction_id);
1121 END LOOP;
1122 ELSIF (p_group_id is not null) THEN
1123 FOR dcp_cursor2_rec IN dcp_cursor2(p_group_id) LOOP
1124 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => NULL, p_interface_transaction_id => dcp_cursor2_rec.interface_transaction_id);
1125 END LOOP;
1126 END IF;
1127 ELSE
1128 check_scripts(p_action_code => p_dcp_event, p_header_interface_id => NULL, p_interface_transaction_id => p_interface_transaction_id);
1129 END IF;
1130 post_process(p_action_code => p_dcp_event, p_raise_exception => p_raise_exception);
1131 x_return_status := 'S';
1132 EXCEPTION
1133 WHEN dcp_caught THEN
1134 IF l_debug_on THEN
1135 asn_debug.put_line('dcp_caught exception: Validate_data');
1136 asn_debug.put_line('EXCEPTION:DCP_CAUGHT: Validate_data');
1137 END IF;
1138
1139 x_return_status := 'S';
1140 RAISE data_inconsistency_exception;
1141 WHEN OTHERS THEN
1142 IF l_debug_on THEN
1143 asn_debug.put_line('Unexpected error has occurred. Oracle error message is ' || SUBSTR(SQLERRM, 1, 180));
1144 asn_debug.put_line('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1145 x_return_status := 'E';
1146 END IF;
1147 END validate_data;
1148 END rcv_dcp_pvt;