1 PACKAGE BODY INV_DCP_PVT as
2 /* $Header: INVDCPPB.pls 120.13 2006/12/06 13:21:41 amohamme noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'INV_DCP_PVT';
6 g_userid NUMBER;
7 g_user_email VARCHAR2(32767);
8 g_user_name VARCHAR2(32767);
9 g_env VARCHAR2(32767);
10 G_DEBUG_ON NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
11 G_DCP_MSG VARCHAR2(32767);
12
13 Function get_email_server RETURN VARCHAR2
14 IS
15 l_debug_on BOOLEAN;
16 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'GET_EMAIL_SERVER';
17
18 BEGIN
19 --
20 --
21 IF g_debug_on = 1 THEN
22 l_debug_on :=TRUE;
23 else
24 l_debug_on :=FALSE;
25 END IF;
26 --
27 IF l_debug_on THEN
28 INV_LOG_UTIL.TRACE('Entering '||l_module_name,l_module_name,'9');
29 END IF;
30 IF INV_DCP_PVT.g_email_server IS NOT NULL THEN
31 IF l_debug_on THEN
32 INV_LOG_UTIL.TRACE('server name cache:'||INV_DCP_PVT.g_email_server,l_module_name,'9');
33 END IF;
34 RETURN INV_DCP_PVT.g_email_server;
35 END IF;
36 INV_DCP_PVT.g_email_server := fnd_profile.value('INV_DCP_EMAIL_SERVER');
37 IF l_debug_on THEN
38 INV_LOG_UTIL.TRACE('server name profile :'||INV_DCP_PVT.g_email_server,'9');
39 END IF;
40 RETURN INV_DCP_PVT.g_email_server;
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 IF l_debug_on THEN
45 INV_LOG_UTIL.TRACE('When others error has occured. Oracle error message is '|| SQLERRM, l_module_name,'9');
46 END IF;
47 RETURN NULL;
48 END Get_email_server;
49
50 Function get_email_address RETURN VARCHAR2
51 IS
52 l_debug_on BOOLEAN;
53 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'GET_EMAIL_ADDRESS';
54 BEGIN
55 --
56 IF g_debug_on = 1 THEN
57 l_debug_on :=TRUE;
58 else
59 l_debug_on :=FALSE;
60 END IF;
61 --
62 IF l_debug_on THEN
63 INV_LOG_UTIL.TRACE('Entering :'||l_module_name,'l_module_name','9');
64 END IF;
65 IF INV_DCP_PVT.g_email_address IS NOT NULL THEN
66 IF l_debug_on THEN
67 INV_LOG_UTIL.TRACE('Email Address cache :'||INV_DCP_PVT.g_email_address,l_module_name,'9');
68 END IF;
69 RETURN INV_DCP_PVT.g_email_address;
70 END IF;
71 INV_DCP_PVT.g_email_address := fnd_profile.value('INV_DCP_EMAIL_ADDRESSES');
72 IF l_debug_on THEN
73 INV_LOG_UTIL.TRACE('Email Address profile :'||INV_DCP_PVT.g_email_address,l_module_name,'9');
74 END IF;
75 RETURN INV_DCP_PVT.g_email_address;
76 EXCEPTION
77 WHEN OTHERS THEN
78 IF l_debug_on THEN
79 INV_LOG_UTIL.TRACE('When others error has occured. Oracle error message is ' || SQLERRM,l_module_name,'9');
80 END IF;
81 RETURN NULL;
82 END Get_email_address;
83
84
85 Procedure Send_Mail(sender IN VARCHAR2,
86 recipient1 IN VARCHAR2,
87 recipient2 IN VARCHAR2,
88 recipient3 IN VARCHAR2,
89 recipient4 IN VARCHAR2,
90 message IN VARCHAR2)
91 IS
92 l_mailhost VARCHAR2(32767);
93 l_mail_conn UTL_SMTP.CONNECTION;
94 l_email_addrs VARCHAR2(32767);
95 l_spr VARCHAR2(30) := ',';
96 l_start_pos NUMBER;
97 l_end_pos NUMBER;
98 j NUMBER;
99 l_recipient1 VARCHAR2(32767);
100 l_recipient2 VARCHAR2(32767);
101 l_recipient3 VARCHAR2(32767);
102 l_recipient4 VARCHAR2(32767);
103 l_recipient5 VARCHAR2(32767);
104 l_sender VARCHAR2(32767) := 'Oracle-Logistics-Data-Integrity-Check@oraclelogistics';
105 l_debug_on BOOLEAN;
106 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'SEND_MAIL';
107
108 CURSOR c_env IS
109 SELECT name from v$database;
110
111 BEGIN
112 --
113 IF g_debug_on = 1 THEN
114 l_debug_on :=TRUE;
115 else
116 l_debug_on :=FALSE;
117 END IF;
118 --
119 --
120 FOR c_env_rec in c_env loop
121 l_sender := l_sender||'-'||c_env_rec.name;
122 END LOOP;
123
124 IF l_debug_on THEN
125 INV_LOG_UTIL.TRACE('Entering :'||l_module_name,l_module_name,'9');
126 INV_LOG_UTIL.TRACE('sender: '||sender,l_module_name,'9');
127 INV_LOG_UTIL.TRACE('recipient1: '||recipient1,l_module_name,'9');
128 INV_LOG_UTIL.TRACE('recipient2: '||recipient2,l_module_name,'9');
129 INV_LOG_UTIL.TRACE('recipient3: '||recipient3,l_module_name,'9');
130 INV_LOG_UTIL.TRACE('recipient4: '||recipient4,l_module_name,'9');
131 END IF;
132
133 --Call function that will return the email server name
134 l_mailhost := get_email_server;
135 --Call function that will return the email addresses
136 l_email_addrs := get_email_address;
137 --Parse to get individual recipients
138 IF l_debug_on THEN
139 INV_LOG_UTIL.TRACE('l_mailhost: '||l_mailhost,l_module_name,'9');
140 INV_LOG_UTIL.TRACE('l_email_addrs: '||l_email_addrs,l_module_name,'9');
141 END IF;
142 IF l_mailhost IS NOT NULL
143 AND l_email_addrs IS NOT NULL
144 THEN
145 --{
146 l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
147 j := 1; l_start_pos := 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
148 if l_end_pos = 0 then
149 l_end_pos := lengthb(l_email_addrs) + 1;
150 end if;
151 l_recipient1 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
152 j := j+1; l_start_pos := l_end_pos + 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
153 if l_end_pos = 0 then
154 l_end_pos := lengthb(l_email_addrs) + 1;
155 end if;
156 l_recipient2 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
157 j := j+1; l_start_pos := l_end_pos + 1; l_end_pos := instrb(l_email_addrs, l_spr, 1, j);
158 if l_end_pos = 0 then
159 l_end_pos := lengthb(l_email_addrs) + 1;
160 end if;
161 l_recipient3 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
162 j := j+1; l_start_pos := l_end_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_recipient4 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
167 j := j+1; l_start_pos := l_end_pos + 1;
168 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_recipient5 := substrb(l_email_addrs, l_start_pos, l_end_pos-l_start_pos);
173 IF l_debug_on THEN
174 INV_LOG_UTIL.TRACE('Now sender :'||l_sender,l_module_name,'9');
175 INV_LOG_UTIL.TRACE('Now recipient1: '||l_recipient1,l_module_name,'9');
176 INV_LOG_UTIL.TRACE('Now recipient2: '||l_recipient2,l_module_name,'9');
177 INV_LOG_UTIL.TRACE('Now recipient3: '||l_recipient3,l_module_name,'9');
178 INV_LOG_UTIL.TRACE('Now recipient4: '||l_recipient4,l_module_name,'9');
179 INV_LOG_UTIL.TRACE('Now recipient5: '||l_recipient5,l_module_name,'9');
180 END IF;
181 utl_smtp.helo(l_mail_conn, l_mailhost);
182 utl_smtp.mail(l_mail_conn, l_sender);
183 IF l_recipient1 IS NOT NULL THEN
184 utl_smtp.rcpt(l_mail_conn,l_recipient1);
185 END IF;
186 IF l_recipient2 IS NOT NULL THEN
187 utl_smtp.rcpt(l_mail_conn,l_recipient2);
188 END IF;
189 IF l_recipient3 IS NOT NULL THEN
190 utl_smtp.rcpt(l_mail_conn,l_recipient3);
191 END IF;
192 IF l_recipient4 IS NOT NULL THEN
193 utl_smtp.rcpt(l_mail_conn,l_recipient4);
194 END IF;
195 IF l_recipient5 IS NOT NULL THEN
196 utl_smtp.rcpt(l_mail_conn,l_recipient5);
197 END IF;
198 utl_smtp.data(l_mail_conn,message);
199 utl_smtp.quit(l_mail_conn);
200 ELSE
201 IF l_debug_on THEN
202 INV_LOG_UTIL.TRACE('Not sending mail. Server Name or Email id is null',l_module_name,'9');
203 END IF;
204 --}
205 END IF;
206 IF l_debug_on THEN
207 INV_LOG_UTIL.TRACE('Exiting :'||l_module_name,l_module_name,'9');
208 END IF;
209 EXCEPTION
210 WHEN others THEN
211 IF l_debug_on THEN
212 INV_LOG_UTIL.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM,l_module_name,'9');
213 INV_LOG_UTIL.TRACE('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR',l_module_name,'9');
214 END IF;
215 END Send_Mail;
216
217
218 /*====================================================================================
219 FUNCTION NAME: add_serial_data
220
221 DESCRIPTION: This function adds Serial status/Action related data to global table
222
223 =====================================================================================*/
224
225 FUNCTION add_serial_data(trx_qty IN NUMBER,
226 serial_control_code IN NUMBER,
227 xfer_org NUMBER,
228 inv_item_id NUMBER
229 ) RETURN BOOLEAN
230 IS
231 xfer_ser_num_code number :=0;
232 BEGIN
233 /*
234 for 21 if serial_number_control_code in transfer_org is in 2,5 then expected serial_status=5 else serial_status=4
235 for 3 if serial_number_control_code in transfer_org is in 2,5 then expected serial_status=3 else serial_status=4
236 */
237 BEGIN
238 if (xfer_org is not null and inv_item_id is not null) then
239 select SERIAL_NUMBER_CONTROL_CODE
240 into xfer_ser_num_code
241 from mtl_system_items
242 where organization_id = xfer_org
243 and inventory_item_id = inv_item_id;
244 end if;
245 EXCEPTION
246 WHEN OTHERS THEN
247 return FALSE;
248 END;
249
250 g_ser_check_tab(1).serial_status := 4;
251 g_ser_check_tab(2).serial_status := 3;
252 if xfer_ser_num_code in (2,5) then
253 g_ser_check_tab(3).serial_status := 3;
254 else
255 g_ser_check_tab(3).serial_status := 3;
256 end if;
257 if (trx_qty > 0) then
258 g_ser_check_tab(4).serial_status := 3;
259 else
260 g_ser_check_tab(4).serial_status := 4;
261 end if;
262 g_ser_check_tab(5).serial_status := 3;
263 g_ser_check_tab(6).serial_status := 3;
264 g_ser_check_tab(7).serial_status := 3;
265 if (trx_qty > 0) then
266 g_ser_check_tab(8).serial_status := 3;
267 else
268 g_ser_check_tab(8).serial_status := 4;
269 end if;
270 g_ser_check_tab(9).serial_status := 3;
271 g_ser_check_tab(10).serial_status := 3;
272 g_ser_check_tab(11).serial_status := 3;
273 g_ser_check_tab(12).serial_status := 3;
274 g_ser_check_tab(13).serial_status := 3;
275 g_ser_check_tab(14).serial_status := 3;
276 g_ser_check_tab(15).serial_status := 3;
277 g_ser_check_tab(17).serial_status := 3;
278
279 if xfer_ser_num_code in (2,5,6) then
280 g_ser_check_tab(21).serial_status := 5;
281 else
282 g_ser_check_tab(21).serial_status := 4;
283 end if;
284
285 g_ser_check_tab(22).serial_status := 3;
286 g_ser_check_tab(24).serial_status := 3;
287 g_ser_check_tab(25).serial_status := 3;
288 g_ser_check_tab(26).serial_status := 3;
289 if (serial_control_code = 6) then
290 g_ser_check_tab(27).serial_status := 1;
291 else
292 g_ser_check_tab(27).serial_status := 3;
293 end if;
294 g_ser_check_tab(28).serial_status := 3;
295 if (trx_qty > 0) then
296 g_ser_check_tab(29).serial_status := 3;
297 else
298 g_ser_check_tab(29).serial_status := 4;
299 end if;
300 g_ser_check_tab(30).serial_status := 4;
301 g_ser_check_tab(31).serial_status := 3;
302 g_ser_check_tab(32).serial_status := 4;
303 g_ser_check_tab(33).serial_status := 3;
304 g_ser_check_tab(34).serial_status := 4;
305 g_ser_check_tab(35).serial_status := 3;
306 g_ser_check_tab(36).serial_status := 3;
307 g_ser_check_tab(40).serial_status := 3;
308 g_ser_check_tab(41).serial_status := 3;
309 g_ser_check_tab(42).serial_status := 3;
310 g_ser_check_tab(43).serial_status := 3;
311 g_ser_check_tab(50).serial_status := 3;
312 g_ser_check_tab(51).serial_status := 3;
313 g_ser_check_tab(52).serial_status := 3;
314 g_ser_check_tab(55).serial_status := 3;
315 g_ser_check_tab(56).serial_status := 3;
316 g_ser_check_tab(57).serial_status := 3;
317 RETURN TRUE;
318 END add_serial_data;
319
320
321
322
323 /*===========================================================================
324 FUNCTION NAME: is_dcp_enabled
325
326 DESCRIPTION: This function returns the DCP profile
327
328 ===========================================================================*/
329
330 FUNCTION is_dcp_enabled RETURN NUMBER
331 IS
332 BEGIN
333
334 IF INV_DCP_PVT.g_check_dcp IS NOT NULL
335 THEN
336 RETURN(INV_DCP_PVT.g_check_dcp);
337 END IF;
338 --
339 INV_DCP_PVT.g_check_dcp := nvl(fnd_profile.value('INV_ENABLE_DCP'), 0);
340 --
341 RETURN INV_DCP_PVT.g_check_dcp;
342
343 EXCEPTION
344 when others then
345 RETURN 0;
346 END is_dcp_enabled;
347
348 PROCEDURE Post_Process(p_action_code IN VARCHAR2,
349 p_raise_exception IN VARCHAR2)
350 IS
351 l_call_stack VARCHAR2(32767);
352 l_message VARCHAR2(32767);
353 l_debug_file VARCHAR2(32767);
354 l_debug_dir VARCHAR2(32767);
355 l_debug_on BOOLEAN;
356 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'POST_PROCESS';
357 k NUMBER;
358 l_rollback_allowed VARCHAR2(1);
359 l_return_status VARCHAR2(30);
360 l_msg_count NUMBER;
361 l_msg_data VARCHAR2(32767);
362 l_conc_request_id NUMBER;
363 l_utl_file_locns VARCHAR2(32767);
364 l_dbg_file VARCHAR2(2000);
365 l_module VARCHAR2(32767);
366 l_level NUMBER;
367 l_dir VARCHAR2(32767);
368 l_comma_pos NUMBER;
369 l_curr_msg_count NUMBER := 0;
370 l_recipient1 VARCHAR2(32767);
371 l_recipient2 VARCHAR2(32767);
372 l_recipient3 VARCHAR2(32767);
373 l_temp_message VARCHAR2(32767);
374 L_NDX VARCHAR2(1);
375 crlf CONSTANT VARCHAR2 (2) := fnd_global.local_chr(13) || fnd_global.local_chr(10);
376
377 CURSOR c_user_info(p_user_id IN NUMBER) IS
378 SELECT user_name, email_address
379 FROM fnd_user
380 WHERE user_id = p_user_id;
381
382 CURSOR c_utl_file IS
383 SELECT rtrim(ltrim(value)) from v$parameter
384 WHERE lower(name) = 'utl_file_dir';
385
386 CURSOR c_env IS
387 SELECT name from v$database;
388
389 l_om_debug_enabled VARCHAR2(30);
390 l_file_name varchar2(2000);
391 l_dir_separator varchar2(1);
392 BEGIN
393
394 /***
395 a) Check if debug is ON
396 b) If rollback is allowed and debug is off then Turn debug ON and Raise Exception
397 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.
398 d) If rollback is NOT allowed then Collect information, put the information in a new debug file and Send email
399 *****/
400
401 --
402 IF g_debug_on = 1 THEN
403 l_debug_on :=TRUE;
404 else
405 l_debug_on :=FALSE;
406 END IF;
407 --
408 IF l_debug_on THEN
409 INV_LOG_UTIL.TRACE('Entering :'||l_module_name,l_module_name,'9');
410 INV_LOG_UTIL.TRACE('p_action_code'||p_action_code,l_module_name,'9');
411 INV_LOG_UTIL.TRACE('p_raise_exception'||p_raise_Exception,l_module_name,'9');
412 END IF;
413 l_rollback_allowed := nvl(p_raise_exception, 'Y');
414 IF g_userid IS NULL THEN
415 fnd_profile.get('USER_ID',g_userid);
416 OPEN c_user_info(g_userid);
417 FETCH c_user_info INTO g_user_name, g_user_email;
418 CLOSE c_user_info;
419 END IF;
420 IF g_env IS NULL THEN
421 OPEN c_env;
422 FETCH c_env INTO g_env;
423 CLOSE c_env;
424 END IF;
425 l_conc_request_id := fnd_global.conc_request_id;
426 if (l_conc_request_id=0) then
427 l_conc_request_id := NULL;
428 end if;
429 IF l_debug_on THEN
430 INV_LOG_UTIL.TRACE('User Id'||g_userid,l_module_name,'9');
431 INV_LOG_UTIL.TRACE('User Name'||g_user_name,l_module_name,'9');
432 INV_LOG_UTIL.TRACE('Env'||g_env,l_module_name,'9');
433 END IF;
434 IF (g_dc_table.count > 0) THEN
435 ---{
436 -- Turn debug on
437 -- Changes - Get INV Debug Directory and if it is valid use it with new filename. If it is not then use from utl_file_dir paramater
438 fnd_profile.get('INV_DEBUG_FILE',l_dbg_file);
439 OPEN c_utl_file;
440 FETCH c_utl_file INTO l_utl_file_locns;
441 CLOSE c_utl_file;
442 l_dir_separator := '/';
443 l_ndx := instr(l_dbg_file,l_dir_separator);
444 IF (l_ndx = 0) then
445 l_dir_separator := '\';
446 END IF;
447 ----Validate that Filename in profile is correct
448 l_debug_dir := nvl(substr(l_dbg_file,1,instr(l_dbg_file,l_dir_separator,-1,1)-1),'-999');
449 IF (l_utl_file_locns <> '*') THEN --{
450 IF (INSTRB(l_utl_file_locns,l_debug_dir) = 0) THEN ---{
451 --- Filename in profile is incorrect, generate a new one.
452 l_comma_pos := INSTRB(l_utl_file_locns, ',');
453 if (l_comma_pos <> 0) then
454 l_debug_dir := SUBSTRB(l_utl_file_locns, 1, l_comma_pos-1);
455 else
456 l_debug_dir := l_utl_file_locns;
457 end if;
458 l_dir_separator := '/';
459 l_ndx := instr(l_debug_dir,l_dir_separator);
460 if (l_ndx = 0) then
461 l_dir_separator := '\';
462 end if;
463 l_file_name := l_debug_dir||l_dir_separator||'INV_DCP'||userenv('SESSIONID')||'.dbg';
464 fnd_profile.put('INV_DEBUG_FILE',l_file_name);
465 ELSE ---}{
466 IF NOT l_debug_on THEN
467 --- Filename in profile is correct but debug is off, Lets generate a new filename
468 l_file_name := l_debug_dir||l_dir_separator||'INV_DCP'||userenv('SESSIONID')||'.dbg';
469 ELSE
470 --- Filename in profile is correct, Lets use it
471 l_file_name :=l_dbg_file;
472 END IF;
473 END IF; ---}
474 ELSE ----}{
475 IF NOT l_debug_on THEN
476 l_file_name :=l_dir_separator||'tmp'||l_dir_separator||'INV_DCP'||userenv('SESSIONID')||'.dbg';
477 ELSE
478 l_file_name :=l_dbg_file;
479 END IF;
480 END IF; ---}
481 IF NOT l_debug_on THEN
482 fnd_profile.put('INV_DEBUG_FILE',l_file_name);
483 fnd_profile.put('INV_DEBUG_LEVEL','9');
484 fnd_profile.put('INV_DEBUG_TRACE','1');
485 G_DEBUG_STARTED :='Y';
486 l_debug_on :=TRUE;
487 G_DEBUG_ON := 1;
488 IF l_debug_on THEN
489 INV_LOG_UTIL.TRACE('DCP - Started Debugger',l_module_name,'9');
490 END IF;
491 END IF;
492 IF l_debug_on THEN
493 INV_LOG_UTIL.TRACE('l_debug_file :'||l_file_name,l_module_name,'9');
494 END IF;
495 --}
496 IF l_debug_on THEN
497 INV_LOG_UTIL.TRACE('==========================================',l_module_name,'9');
498 INV_LOG_UTIL.TRACE(G_DCP_MSG,l_module_name,'9');
499 INV_LOG_UTIL.TRACE('==========================================',l_module_name,'9');
500 INV_LOG_UTIL.TRACE('l_debug_dir :'||l_debug_dir,l_module_name,'9');
501 INV_LOG_UTIL.TRACE('g_dc_table count :'|| g_dc_table.count,l_module_name,'9');
502 INV_LOG_UTIL.TRACE('l_rollback_allowed :'||l_rollback_allowed,l_module_name,'9');
503 INV_LOG_UTIL.TRACE('conc request id :'||l_conc_request_id,l_module_name,'9');
504 END IF;
505 IF (l_rollback_allowed = 'Y') THEN
506 raise dcp_caught;
507 END IF;
508 --{
509 --Get CallStack
510 l_message := 'Subject: INV Data inconsistency detected for '||g_user_name||' in '||g_env||crlf||crlf;
511 l_message := l_message ||crlf||'Action Performed:'||p_action_code;
512 if nvl(l_conc_request_id, -1) <> -1 then
513 l_message := l_message ||'
514 Data Inconsistency found in environment ' || g_env || ' for concurrent request id ' || l_conc_request_id || ' submitted by user ' || g_user_name||'.
515 Debug file for this transaction= '||l_file_name||'.';
516 else
517 l_message := l_message || '
518 Data Inconsistency found in environment ' || g_env || ' for a transaction run by user ' || g_user_name || '
519 Debug file for this transaction= ' || l_file_name||'. ';
520 end if;
521 --
522 -- dump the call stack and pl/sql table
523 -- if global was set , turn debug off
524 -- Put CallStack in debug file
525 if l_debug_on then
526 l_call_stack := dbms_utility.format_call_stack;
527 INV_LOG_UTIL.TRACE('**********Begining of Call Stack**********',l_module_name,'9');
528 INV_LOG_UTIL.TRACE(l_call_stack,l_module_name,'9');
529 INV_LOG_UTIL.TRACE('**********End of Call Stack**********',l_module_name,'9');
530 end if;
531 l_message := l_message||'
532 ';
533 l_message := l_message||'
534 ********** Here are the Details **********';
535 k := g_dc_table.first;
536 WHILE k is not null LOOP
537 --{
538 l_temp_message := k||'. Data Mismatch #'||g_dc_table(k).msg||'.
539 (Org:'||g_dc_table(k).organization_code||', Item:'||g_dc_table(k).item_name||', Source:'||g_dc_table(k).source_type||', Action:'||g_dc_table(k).action_code||',';
540 l_temp_message:= l_temp_message||' Trx Type:'||g_dc_table(k).trx_type||', header No: '||g_dc_table(k).trx_hdr_id ||', Trx Temp Id:'||g_dc_table(k).trx_temp_id||', Transfer org:'||g_dc_table(k).xfer_org_code||')';
541 IF length(l_message) < 31900 THEN
542 l_message := l_message ||'
543 '|| l_temp_message||'. ';
544 END IF;
545 IF l_debug_on THEN
546 INV_LOG_UTIL.TRACE(l_temp_message,l_module_name,'9');
547 END IF;
548 k := g_dc_table.next(k);
549 --}
550 END LOOP;
551 l_message := l_message||'
552 ********** End of the Details **********';
553 --Send Email
554 Send_Mail(sender => l_recipient1,
555 recipient1 => l_recipient1,
556 recipient2 => l_recipient2,
557 recipient3 => l_recipient3,
558 message => l_message);
559 dump_mmtt;
560 g_dc_table.delete;
561 --}
562 --}
563 END IF;
564 -- Stop the debugger if it was started earlier
565 IF G_DEBUG_STARTED ='Y' THEN
566 IF l_debug_on THEN
567 INV_LOG_UTIL.TRACE('DCP - Stopped Debugger',l_module_name,'9');
568 l_debug_on := FALSE;
569 END IF;
570 INV_debug_interface.stop_inv_debugger();
571 G_DEBUG_STARTED :='N';
572 END IF;
573
574 EXCEPTION
575 WHEN dcp_caught THEN
576 IF l_debug_on THEN
577 INV_LOG_UTIL.TRACE('DCP Caught: Post Process',l_module_name,'9');
578 INV_LOG_UTIL.TRACE('Exception: dcp_caught',l_module_name,'9');
579 END IF;
580 RAISE dcp_caught;
581 WHEN others THEN
582 IF l_debug_on THEN
583 INV_LOG_UTIL.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM,l_module_name,'9');
584 INV_LOG_UTIL.TRACE('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR',l_module_name,'9');
585 END IF;
586 END Post_Process;
587
588 Procedure dump_mmtt is
589 l_debug_on BOOLEAN;
590 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'DUMP_MMTT';
591 i NUMBER :=0;
592 CURSOR c1(trx_hdr_id in NUMBER,trx_temp_id IN NUMBER) is
593 SELECT *
594 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
595 WHERE TRANSACTION_HEADER_ID=trx_hdr_id
596 AND TRANSACTION_TEMP_ID = trx_temp_id;
597 begin
598 IF g_debug_on = 1 THEN
599 l_debug_on :=TRUE;
600 else
601 l_debug_on :=FALSE;
602 END IF;
603 IF l_debug_on THEN
604 INV_LOG_UTIL.TRACE('=============Start of MMTT Data===============',l_module_name,'9');
605 END IF;
606 FOR i in g_dc_table.FIRST..g_dc_table.LAST LOOP ---{
607 For c1_rec in c1(g_dc_table(i).trx_hdr_id,g_dc_table(i).trx_temp_id) loop
608 IF l_debug_on THEN
609 INV_LOG_UTIL.TRACE('===>Start of MMTT Record:'||i,l_module_name,'9');
610 INV_LOG_UTIL.TRACE('ACCT_PERIOD_ID:'||c1_rec.ACCT_PERIOD_ID,l_module_name,'9');
611 INV_LOG_UTIL.TRACE('ALLOCATED_LPN_ID:'||c1_rec.ALLOCATED_LPN_ID,l_module_name,'9');
612 INV_LOG_UTIL.TRACE('ALLOWED_UNITS_LOOKUP_CODE:'||c1_rec.ALLOWED_UNITS_LOOKUP_CODE,l_module_name,'9');
613 INV_LOG_UTIL.TRACE('ALTERNATE_BOM_DESIGNATOR:'||c1_rec.ALTERNATE_BOM_DESIGNATOR,l_module_name,'9');
614 INV_LOG_UTIL.TRACE('ALTERNATE_ROUTING_DESIGNATOR:'||c1_rec.ALTERNATE_ROUTING_DESIGNATOR,l_module_name,'9');
615 INV_LOG_UTIL.TRACE('ATTRIBUTE1:'||c1_rec.ATTRIBUTE1,l_module_name,'9');
616 INV_LOG_UTIL.TRACE('ATTRIBUTE10:'||c1_rec.ATTRIBUTE10,l_module_name,'9');
617 INV_LOG_UTIL.TRACE('ATTRIBUTE11:'||c1_rec.ATTRIBUTE11,l_module_name,'9');
618 INV_LOG_UTIL.TRACE('ATTRIBUTE12:'||c1_rec.ATTRIBUTE12,l_module_name,'9');
619 INV_LOG_UTIL.TRACE('ATTRIBUTE13:'||c1_rec.ATTRIBUTE13,l_module_name,'9');
620 INV_LOG_UTIL.TRACE('ATTRIBUTE14:'||c1_rec.ATTRIBUTE14,l_module_name,'9');
621 INV_LOG_UTIL.TRACE('ATTRIBUTE15:'||c1_rec.ATTRIBUTE15,l_module_name,'9');
622 INV_LOG_UTIL.TRACE('ATTRIBUTE2:'||c1_rec.ATTRIBUTE2,l_module_name,'9');
623 INV_LOG_UTIL.TRACE('ATTRIBUTE3:'||c1_rec.ATTRIBUTE3,l_module_name,'9');
624 INV_LOG_UTIL.TRACE('ATTRIBUTE4:'||c1_rec.ATTRIBUTE4,l_module_name,'9');
625 INV_LOG_UTIL.TRACE('ATTRIBUTE5:'||c1_rec.ATTRIBUTE5,l_module_name,'9');
626 INV_LOG_UTIL.TRACE('ATTRIBUTE6:'||c1_rec.ATTRIBUTE6,l_module_name,'9');
627 INV_LOG_UTIL.TRACE('ATTRIBUTE7:'||c1_rec.ATTRIBUTE7,l_module_name,'9');
628 INV_LOG_UTIL.TRACE('ATTRIBUTE8:'||c1_rec.ATTRIBUTE8,l_module_name,'9');
629 INV_LOG_UTIL.TRACE('ATTRIBUTE9:'||c1_rec.ATTRIBUTE9,l_module_name,'9');
630 INV_LOG_UTIL.TRACE('ATTRIBUTE_CATEGORY:'||c1_rec.ATTRIBUTE_CATEGORY,l_module_name,'9');
631 INV_LOG_UTIL.TRACE('BOM_REVISION:'||c1_rec.BOM_REVISION,l_module_name,'9');
632 INV_LOG_UTIL.TRACE('BOM_REVISION_DATE:'||c1_rec.BOM_REVISION_DATE,l_module_name,'9');
633 INV_LOG_UTIL.TRACE('BUILD_SEQUENCE:'||c1_rec.BUILD_SEQUENCE,l_module_name,'9');
634 INV_LOG_UTIL.TRACE('CARTONIZATION_ID:'||c1_rec.CARTONIZATION_ID,l_module_name,'9');
635 INV_LOG_UTIL.TRACE('CLASS_CODE:'||c1_rec.CLASS_CODE,l_module_name,'9');
636 INV_LOG_UTIL.TRACE('COMMON_BOM_SEQ_ID:'||c1_rec.COMMON_BOM_SEQ_ID,l_module_name,'9');
637 INV_LOG_UTIL.TRACE('COMMON_ROUTING_SEQ_ID:'||c1_rec.COMMON_ROUTING_SEQ_ID,l_module_name,'9');
638 INV_LOG_UTIL.TRACE('COMPLETION_TRANSACTION_ID:'||c1_rec.COMPLETION_TRANSACTION_ID,l_module_name,'9');
639 INV_LOG_UTIL.TRACE('CONTAINERS:'||c1_rec.CONTAINERS,l_module_name,'9');
640 INV_LOG_UTIL.TRACE('CONTAINER_ITEM_ID:'||c1_rec.CONTAINER_ITEM_ID,l_module_name,'9');
641 INV_LOG_UTIL.TRACE('CONTENT_LPN_ID:'||c1_rec.CONTENT_LPN_ID,l_module_name,'9');
642 INV_LOG_UTIL.TRACE('COST_GROUP_ID:'||c1_rec.COST_GROUP_ID,l_module_name,'9');
643 INV_LOG_UTIL.TRACE('COST_TYPE_ID:'||c1_rec.COST_TYPE_ID,l_module_name,'9');
644 INV_LOG_UTIL.TRACE('CREATED_BY:'||c1_rec.CREATED_BY,l_module_name,'9');
645 INV_LOG_UTIL.TRACE('CREATION_DATE:'||c1_rec.CREATION_DATE,l_module_name,'9');
646 INV_LOG_UTIL.TRACE('CURRENCY_CODE:'||c1_rec.CURRENCY_CODE,l_module_name,'9');
647 INV_LOG_UTIL.TRACE('CURRENCY_CONVERSION_DATE:'||c1_rec.CURRENCY_CONVERSION_DATE,l_module_name,'9');
648 INV_LOG_UTIL.TRACE('CURRENCY_CONVERSION_RATE:'||c1_rec.CURRENCY_CONVERSION_RATE,l_module_name,'9');
649 INV_LOG_UTIL.TRACE('CURRENCY_CONVERSION_TYPE:'||c1_rec.CURRENCY_CONVERSION_TYPE,l_module_name,'9');
650 INV_LOG_UTIL.TRACE('CURRENT_LOCATOR_CONTROL_CODE:'||c1_rec.CURRENT_LOCATOR_CONTROL_CODE,l_module_name,'9');
651 INV_LOG_UTIL.TRACE('CUSTOMER_SHIP_ID:'||c1_rec.CUSTOMER_SHIP_ID,l_module_name,'9');
652 INV_LOG_UTIL.TRACE('CYCLE_COUNT_ID:'||c1_rec.CYCLE_COUNT_ID,l_module_name,'9');
653 INV_LOG_UTIL.TRACE('DEMAND_CLASS:'||c1_rec.DEMAND_CLASS,l_module_name,'9');
654 INV_LOG_UTIL.TRACE('DEMAND_ID:'||c1_rec.DEMAND_ID,l_module_name,'9');
655 INV_LOG_UTIL.TRACE('DEMAND_SOURCE_DELIVERY:'||c1_rec.DEMAND_SOURCE_DELIVERY,l_module_name,'9');
656 INV_LOG_UTIL.TRACE('DEMAND_SOURCE_HEADER_ID:'||c1_rec.DEMAND_SOURCE_HEADER_ID,l_module_name,'9');
657 INV_LOG_UTIL.TRACE('DEMAND_SOURCE_LINE:'||c1_rec.DEMAND_SOURCE_LINE,l_module_name,'9');
658 INV_LOG_UTIL.TRACE('DEPARTMENT_CODE:'||c1_rec.DEPARTMENT_CODE,l_module_name,'9');
659 INV_LOG_UTIL.TRACE('DEPARTMENT_ID:'||c1_rec.DEPARTMENT_ID,l_module_name,'9');
660 INV_LOG_UTIL.TRACE('DISTRIBUTION_ACCOUNT_ID:'||c1_rec.DISTRIBUTION_ACCOUNT_ID,l_module_name,'9');
661 INV_LOG_UTIL.TRACE('EMPLOYEE_CODE:'||c1_rec.EMPLOYEE_CODE,l_module_name,'9');
662 INV_LOG_UTIL.TRACE('ENCUMBRANCE_ACCOUNT:'||c1_rec.ENCUMBRANCE_ACCOUNT,l_module_name,'9');
663 INV_LOG_UTIL.TRACE('ENCUMBRANCE_AMOUNT:'||c1_rec.ENCUMBRANCE_AMOUNT,l_module_name,'9');
664 INV_LOG_UTIL.TRACE('END_ITEM_UNIT_NUMBER:'||c1_rec.END_ITEM_UNIT_NUMBER,l_module_name,'9');
665 INV_LOG_UTIL.TRACE('ERROR_CODE:'||c1_rec.ERROR_CODE,l_module_name,'9');
666 INV_LOG_UTIL.TRACE('ERROR_EXPLANATION:'||c1_rec.ERROR_EXPLANATION,l_module_name,'9');
667 INV_LOG_UTIL.TRACE('EXPECTED_ARRIVAL_DATE:'||c1_rec.EXPECTED_ARRIVAL_DATE,l_module_name,'9');
668 INV_LOG_UTIL.TRACE('EXPENDITURE_TYPE:'||c1_rec.EXPENDITURE_TYPE,l_module_name,'9');
669 INV_LOG_UTIL.TRACE('FINAL_COMPLETION_FLAG:'||c1_rec.FINAL_COMPLETION_FLAG,l_module_name,'9');
670 INV_LOG_UTIL.TRACE('FLOW_SCHEDULE:'||c1_rec.FLOW_SCHEDULE,l_module_name,'9');
671 INV_LOG_UTIL.TRACE('FOB_POINT:'||c1_rec.FOB_POINT,l_module_name,'9');
672 INV_LOG_UTIL.TRACE('FREIGHT_CODE:'||c1_rec.FREIGHT_CODE,l_module_name,'9');
673 INV_LOG_UTIL.TRACE('INTRANSIT_ACCOUNT:'||c1_rec.INTRANSIT_ACCOUNT,l_module_name,'9');
674 INV_LOG_UTIL.TRACE('INVENTORY_ITEM_ID:'||c1_rec.INVENTORY_ITEM_ID,l_module_name,'9');
675 INV_LOG_UTIL.TRACE('ITEM_DESCRIPTION:'||c1_rec.ITEM_DESCRIPTION,l_module_name,'9');
676 INV_LOG_UTIL.TRACE('ITEM_INVENTORY_ASSET_FLAG:'||c1_rec.ITEM_INVENTORY_ASSET_FLAG,l_module_name,'9');
677 INV_LOG_UTIL.TRACE('ITEM_LOCATION_CONTROL_CODE:'||c1_rec.ITEM_LOCATION_CONTROL_CODE,l_module_name,'9');
678 INV_LOG_UTIL.TRACE('ITEM_LOT_CONTROL_CODE:'||c1_rec.ITEM_LOT_CONTROL_CODE,l_module_name,'9');
679 INV_LOG_UTIL.TRACE('ITEM_ORDERING:'||c1_rec.ITEM_ORDERING,l_module_name,'9');
680 INV_LOG_UTIL.TRACE('ITEM_PRIMARY_UOM_CODE:'||c1_rec.ITEM_PRIMARY_UOM_CODE,l_module_name,'9');
681 INV_LOG_UTIL.TRACE('ITEM_RESTRICT_LOCATORS_CODE:'||c1_rec.ITEM_RESTRICT_LOCATORS_CODE,l_module_name,'9');
682 INV_LOG_UTIL.TRACE('ITEM_RESTRICT_SUBINV_CODE:'||c1_rec.ITEM_RESTRICT_SUBINV_CODE,l_module_name,'9');
683 INV_LOG_UTIL.TRACE('ITEM_REVISION_QTY_CONTROL_CODE:'||c1_rec.ITEM_REVISION_QTY_CONTROL_CODE,l_module_name,'9');
684 INV_LOG_UTIL.TRACE('ITEM_SEGMENTS:'||c1_rec.ITEM_SEGMENTS,l_module_name,'9');
685 INV_LOG_UTIL.TRACE('ITEM_SERIAL_CONTROL_CODE:'||c1_rec.ITEM_SERIAL_CONTROL_CODE,l_module_name,'9');
686 INV_LOG_UTIL.TRACE('ITEM_SHELF_LIFE_CODE:'||c1_rec.ITEM_SHELF_LIFE_CODE,l_module_name,'9');
687 INV_LOG_UTIL.TRACE('ITEM_SHELF_LIFE_DAYS:'||c1_rec.ITEM_SHELF_LIFE_DAYS,l_module_name,'9');
688 INV_LOG_UTIL.TRACE('ITEM_TRX_ENABLED_FLAG:'||c1_rec.ITEM_TRX_ENABLED_FLAG,l_module_name,'9');
689 INV_LOG_UTIL.TRACE('ITEM_UOM_CLASS:'||c1_rec.ITEM_UOM_CLASS,l_module_name,'9');
690 INV_LOG_UTIL.TRACE('KANBAN_CARD_ID:'||c1_rec.KANBAN_CARD_ID,l_module_name,'9');
691 INV_LOG_UTIL.TRACE('LAST_UPDATED_BY:'||c1_rec.LAST_UPDATED_BY,l_module_name,'9');
692 INV_LOG_UTIL.TRACE('LAST_UPDATE_DATE:'||c1_rec.LAST_UPDATE_DATE,l_module_name,'9');
693 INV_LOG_UTIL.TRACE('LAST_UPDATE_LOGIN:'||c1_rec.LAST_UPDATE_LOGIN,l_module_name,'9');
694 INV_LOG_UTIL.TRACE('LINE_TYPE_CODE:'||c1_rec.LINE_TYPE_CODE,l_module_name,'9');
695 INV_LOG_UTIL.TRACE('LOCATOR_ID:'||c1_rec.LOCATOR_ID,l_module_name,'9');
696 INV_LOG_UTIL.TRACE('LOCATOR_SEGMENTS:'||c1_rec.LOCATOR_SEGMENTS,l_module_name,'9');
697 INV_LOG_UTIL.TRACE('LOCK_FLAG:'||c1_rec.LOCK_FLAG,l_module_name,'9');
698 INV_LOG_UTIL.TRACE('LOGICAL_TRX_TYPE_CODE:'||c1_rec.LOGICAL_TRX_TYPE_CODE,l_module_name,'9');
699 INV_LOG_UTIL.TRACE('LOT_ALPHA_PREFIX:'||c1_rec.LOT_ALPHA_PREFIX,l_module_name,'9');
700 INV_LOG_UTIL.TRACE('LOT_EXPIRATION_DATE:'||c1_rec.LOT_EXPIRATION_DATE,l_module_name,'9');
701 INV_LOG_UTIL.TRACE('LOT_NUMBER:'||c1_rec.LOT_NUMBER,l_module_name,'9');
702 INV_LOG_UTIL.TRACE('LPN_ID:'||c1_rec.LPN_ID,l_module_name,'9');
703 INV_LOG_UTIL.TRACE('MATERIAL_ACCOUNT:'||c1_rec.MATERIAL_ACCOUNT,l_module_name,'9');
704 INV_LOG_UTIL.TRACE('MATERIAL_ALLOCATION_TEMP_ID:'||c1_rec.MATERIAL_ALLOCATION_TEMP_ID,l_module_name,'9');
705 INV_LOG_UTIL.TRACE('MATERIAL_OVERHEAD_ACCOUNT:'||c1_rec.MATERIAL_OVERHEAD_ACCOUNT,l_module_name,'9');
706 INV_LOG_UTIL.TRACE('MOVEMENT_ID:'||c1_rec.MOVEMENT_ID,l_module_name,'9');
707 INV_LOG_UTIL.TRACE('MOVE_ORDER_HEADER_ID:'||c1_rec.MOVE_ORDER_HEADER_ID,l_module_name,'9');
708 INV_LOG_UTIL.TRACE('MOVE_ORDER_LINE_ID:'||c1_rec.MOVE_ORDER_LINE_ID,l_module_name,'9');
709 INV_LOG_UTIL.TRACE('MOVE_TRANSACTION_ID:'||c1_rec.MOVE_TRANSACTION_ID,l_module_name,'9');
710 INV_LOG_UTIL.TRACE('NEGATIVE_REQ_FLAG:'||c1_rec.NEGATIVE_REQ_FLAG,l_module_name,'9');
711 INV_LOG_UTIL.TRACE('NEW_AVERAGE_COST:'||c1_rec.NEW_AVERAGE_COST,l_module_name,'9');
712 INV_LOG_UTIL.TRACE('NEXT_LOT_NUMBER:'||c1_rec.NEXT_LOT_NUMBER,l_module_name,'9');
713 INV_LOG_UTIL.TRACE('NEXT_SERIAL_NUMBER:'||c1_rec.NEXT_SERIAL_NUMBER,l_module_name,'9');
714 INV_LOG_UTIL.TRACE('NUMBER_OF_LOTS_ENTERED:'||c1_rec.NUMBER_OF_LOTS_ENTERED,l_module_name,'9');
715 INV_LOG_UTIL.TRACE('OPERATION_PLAN_ID:'||c1_rec.OPERATION_PLAN_ID,l_module_name,'9');
716 INV_LOG_UTIL.TRACE('OPERATION_SEQ_NUM:'||c1_rec.OPERATION_SEQ_NUM,l_module_name,'9');
717 INV_LOG_UTIL.TRACE('ORGANIZATION_ID:'||c1_rec.ORGANIZATION_ID,l_module_name,'9');
718 INV_LOG_UTIL.TRACE('ORGANIZATION_TYPE:'||c1_rec.ORGANIZATION_TYPE,l_module_name,'9');
719 INV_LOG_UTIL.TRACE('ORG_COST_GROUP_ID:'||c1_rec.ORG_COST_GROUP_ID,l_module_name,'9');
720 INV_LOG_UTIL.TRACE('ORIGINAL_TRANSACTION_TEMP_ID:'||c1_rec.ORIGINAL_TRANSACTION_TEMP_ID,l_module_name,'9');
721 INV_LOG_UTIL.TRACE('OUTSIDE_PROCESSING_ACCOUNT:'||c1_rec.OUTSIDE_PROCESSING_ACCOUNT,l_module_name,'9');
722 INV_LOG_UTIL.TRACE('OVERCOMPLETION_PRIMARY_QTY:'||c1_rec.OVERCOMPLETION_PRIMARY_QTY,l_module_name,'9');
723 INV_LOG_UTIL.TRACE('OVERCOMPLETION_TRANSACTION_ID:'||c1_rec.OVERCOMPLETION_TRANSACTION_ID,l_module_name,'9');
724 INV_LOG_UTIL.TRACE('OVERCOMPLETION_TRANSACTION_QTY:'||c1_rec.OVERCOMPLETION_TRANSACTION_QTY,l_module_name,'9');
725 INV_LOG_UTIL.TRACE('OVERHEAD_ACCOUNT:'||c1_rec.OVERHEAD_ACCOUNT,l_module_name,'9');
726 INV_LOG_UTIL.TRACE('OWNING_ORGANIZATION_ID:'||c1_rec.OWNING_ORGANIZATION_ID,l_module_name,'9');
727 INV_LOG_UTIL.TRACE('OWNING_TP_TYPE:'||c1_rec.OWNING_TP_TYPE,l_module_name,'9');
728 INV_LOG_UTIL.TRACE('PARENT_LINE_ID:'||c1_rec.PARENT_LINE_ID,l_module_name,'9');
729 INV_LOG_UTIL.TRACE('PARENT_TRANSACTION_TEMP_ID:'||c1_rec.PARENT_TRANSACTION_TEMP_ID,l_module_name,'9');
730 INV_LOG_UTIL.TRACE('PA_EXPENDITURE_ORG_ID:'||c1_rec.PA_EXPENDITURE_ORG_ID,l_module_name,'9');
731 INV_LOG_UTIL.TRACE('PERCENTAGE_CHANGE:'||c1_rec.PERCENTAGE_CHANGE,l_module_name,'9');
732 INV_LOG_UTIL.TRACE('PHYSICAL_ADJUSTMENT_ID:'||c1_rec.PHYSICAL_ADJUSTMENT_ID,l_module_name,'9');
733 INV_LOG_UTIL.TRACE('PICKING_LINE_ID:'||c1_rec.PICKING_LINE_ID,l_module_name,'9');
734 INV_LOG_UTIL.TRACE('PICK_RULE_ID:'||c1_rec.PICK_RULE_ID,l_module_name,'9');
735 INV_LOG_UTIL.TRACE('PICK_SLIP_DATE:'||c1_rec.PICK_SLIP_DATE,l_module_name,'9');
736 INV_LOG_UTIL.TRACE('PICK_SLIP_NUMBER:'||c1_rec.PICK_SLIP_NUMBER,l_module_name,'9');
737 INV_LOG_UTIL.TRACE('PICK_STRATEGY_ID:'||c1_rec.PICK_STRATEGY_ID,l_module_name,'9');
738 INV_LOG_UTIL.TRACE('PLANNING_ORGANIZATION_ID:'||c1_rec.PLANNING_ORGANIZATION_ID,l_module_name,'9');
739 INV_LOG_UTIL.TRACE('PLANNING_TP_TYPE:'||c1_rec.PLANNING_TP_TYPE,l_module_name,'9');
740 INV_LOG_UTIL.TRACE('POSTING_FLAG:'||c1_rec.POSTING_FLAG,l_module_name,'9');
741 INV_LOG_UTIL.TRACE('PRIMARY_QUANTITY:'||c1_rec.PRIMARY_QUANTITY,l_module_name,'9');
742 INV_LOG_UTIL.TRACE('PRIMARY_SWITCH:'||c1_rec.PRIMARY_SWITCH,l_module_name,'9');
743 INV_LOG_UTIL.TRACE('PROCESS_FLAG:'||c1_rec.PROCESS_FLAG,l_module_name,'9');
744 INV_LOG_UTIL.TRACE('PROGRAM_APPLICATION_ID:'||c1_rec.PROGRAM_APPLICATION_ID,l_module_name,'9');
745 INV_LOG_UTIL.TRACE('PROGRAM_ID:'||c1_rec.PROGRAM_ID,l_module_name,'9');
746 INV_LOG_UTIL.TRACE('PROGRAM_UPDATE_DATE:'||c1_rec.PROGRAM_UPDATE_DATE,l_module_name,'9');
747 INV_LOG_UTIL.TRACE('PROJECT_ID:'||c1_rec.PROJECT_ID,l_module_name,'9');
748 INV_LOG_UTIL.TRACE('PUT_AWAY_RULE_ID:'||c1_rec.PUT_AWAY_RULE_ID,l_module_name,'9');
749 INV_LOG_UTIL.TRACE('PUT_AWAY_STRATEGY_ID:'||c1_rec.PUT_AWAY_STRATEGY_ID,l_module_name,'9');
750 INV_LOG_UTIL.TRACE('QA_COLLECTION_ID:'||c1_rec.QA_COLLECTION_ID,l_module_name,'9');
751 INV_LOG_UTIL.TRACE('RCV_TRANSACTION_ID:'||c1_rec.RCV_TRANSACTION_ID,l_module_name,'9');
752 INV_LOG_UTIL.TRACE('REASON_ID:'||c1_rec.REASON_ID,l_module_name,'9');
753 INV_LOG_UTIL.TRACE('REBUILD_ACTIVITY_ID:'||c1_rec.REBUILD_ACTIVITY_ID,l_module_name,'9');
754 INV_LOG_UTIL.TRACE('REBUILD_ITEM_ID:'||c1_rec.REBUILD_ITEM_ID,l_module_name,'9');
755 INV_LOG_UTIL.TRACE('REBUILD_JOB_NAME:'||c1_rec.REBUILD_JOB_NAME,l_module_name,'9');
756 INV_LOG_UTIL.TRACE('REBUILD_SERIAL_NUMBER:'||c1_rec.REBUILD_SERIAL_NUMBER,l_module_name,'9');
757 INV_LOG_UTIL.TRACE('RECEIVING_DOCUMENT:'||c1_rec.RECEIVING_DOCUMENT,l_module_name,'9');
758 INV_LOG_UTIL.TRACE('RELIEVE_HIGH_LEVEL_RSV_FLAG:'||c1_rec.RELIEVE_HIGH_LEVEL_RSV_FLAG,l_module_name,'9');
759 INV_LOG_UTIL.TRACE('RELIEVE_RESERVATIONS_FLAG:'||c1_rec.RELIEVE_RESERVATIONS_FLAG,l_module_name,'9');
760 INV_LOG_UTIL.TRACE('REPETITIVE_LINE_ID:'||c1_rec.REPETITIVE_LINE_ID,l_module_name,'9');
761 INV_LOG_UTIL.TRACE('REQUEST_ID:'||c1_rec.REQUEST_ID,l_module_name,'9');
762 INV_LOG_UTIL.TRACE('REQUIRED_FLAG:'||c1_rec.REQUIRED_FLAG,l_module_name,'9');
763 INV_LOG_UTIL.TRACE('REQUISITION_DISTRIBUTION_ID:'||c1_rec.REQUISITION_DISTRIBUTION_ID,l_module_name,'9');
764 INV_LOG_UTIL.TRACE('REQUISITION_LINE_ID:'||c1_rec.REQUISITION_LINE_ID,l_module_name,'9');
765 INV_LOG_UTIL.TRACE('RESERVATION_ID:'||c1_rec.RESERVATION_ID,l_module_name,'9');
766 INV_LOG_UTIL.TRACE('RESERVATION_QUANTITY:'||c1_rec.RESERVATION_QUANTITY,l_module_name,'9');
767 INV_LOG_UTIL.TRACE('RESOURCE_ACCOUNT:'||c1_rec.RESOURCE_ACCOUNT,l_module_name,'9');
768 INV_LOG_UTIL.TRACE('REVISION:'||c1_rec.REVISION,l_module_name,'9');
769 INV_LOG_UTIL.TRACE('RMA_LINE_ID:'||c1_rec.RMA_LINE_ID,l_module_name,'9');
770 INV_LOG_UTIL.TRACE('ROUTING_REVISION:'||c1_rec.ROUTING_REVISION,l_module_name,'9');
771 INV_LOG_UTIL.TRACE('ROUTING_REVISION_DATE:'||c1_rec.ROUTING_REVISION_DATE,l_module_name,'9');
772 INV_LOG_UTIL.TRACE('SCHEDULED_FLAG:'||c1_rec.SCHEDULED_FLAG,l_module_name,'9');
773 INV_LOG_UTIL.TRACE('SCHEDULED_PAYBACK_DATE:'||c1_rec.SCHEDULED_PAYBACK_DATE,l_module_name,'9');
774 INV_LOG_UTIL.TRACE('SCHEDULE_GROUP:'||c1_rec.SCHEDULE_GROUP,l_module_name,'9');
775 INV_LOG_UTIL.TRACE('SCHEDULE_ID:'||c1_rec.SCHEDULE_ID,l_module_name,'9');
776 INV_LOG_UTIL.TRACE('SCHEDULE_NUMBER:'||c1_rec.SCHEDULE_NUMBER,l_module_name,'9');
777 INV_LOG_UTIL.TRACE('SCHEDULE_UPDATE_CODE:'||c1_rec.SCHEDULE_UPDATE_CODE,l_module_name,'9');
778 INV_LOG_UTIL.TRACE('SECONDARY_TRANSACTION_QUANTITY:'||c1_rec.SECONDARY_TRANSACTION_QUANTITY,l_module_name,'9');
779 INV_LOG_UTIL.TRACE('SECONDARY_UOM_CODE:'||c1_rec.SECONDARY_UOM_CODE,l_module_name,'9');
780 INV_LOG_UTIL.TRACE('SERIAL_ALLOCATED_FLAG:'||c1_rec.SERIAL_ALLOCATED_FLAG,l_module_name,'9');
781 INV_LOG_UTIL.TRACE('SERIAL_ALPHA_PREFIX:'||c1_rec.SERIAL_ALPHA_PREFIX,l_module_name,'9');
782 INV_LOG_UTIL.TRACE('SERIAL_NUMBER:'||c1_rec.SERIAL_NUMBER,l_module_name,'9');
783 INV_LOG_UTIL.TRACE('SETUP_TEARDOWN_CODE:'||c1_rec.SETUP_TEARDOWN_CODE,l_module_name,'9');
784 INV_LOG_UTIL.TRACE('SHIPMENT_NUMBER:'||c1_rec.SHIPMENT_NUMBER,l_module_name,'9');
785 INV_LOG_UTIL.TRACE('SHIPPABLE_FLAG:'||c1_rec.SHIPPABLE_FLAG,l_module_name,'9');
786 INV_LOG_UTIL.TRACE('SHIPPED_QUANTITY:'||c1_rec.SHIPPED_QUANTITY,l_module_name,'9');
787 INV_LOG_UTIL.TRACE('SHIP_TO_LOCATION:'||c1_rec.SHIP_TO_LOCATION,l_module_name,'9');
788 INV_LOG_UTIL.TRACE('SOURCE_CODE:'||c1_rec.SOURCE_CODE,l_module_name,'9');
789 INV_LOG_UTIL.TRACE('SOURCE_LINE_ID:'||c1_rec.SOURCE_LINE_ID,l_module_name,'9');
790 INV_LOG_UTIL.TRACE('SOURCE_LOT_NUMBER:'||c1_rec.SOURCE_LOT_NUMBER,l_module_name,'9');
791 INV_LOG_UTIL.TRACE('SOURCE_PROJECT_ID:'||c1_rec.SOURCE_PROJECT_ID,l_module_name,'9');
792 INV_LOG_UTIL.TRACE('SOURCE_TASK_ID:'||c1_rec.SOURCE_TASK_ID,l_module_name,'9');
793 INV_LOG_UTIL.TRACE('STANDARD_OPERATION_ID:'||c1_rec.STANDARD_OPERATION_ID,l_module_name,'9');
794 INV_LOG_UTIL.TRACE('SUBINVENTORY_CODE:'||c1_rec.SUBINVENTORY_CODE,l_module_name,'9');
795 INV_LOG_UTIL.TRACE('SUPPLY_LOCATOR_ID:'||c1_rec.SUPPLY_LOCATOR_ID,l_module_name,'9');
796 INV_LOG_UTIL.TRACE('SUPPLY_SUBINVENTORY:'||c1_rec.SUPPLY_SUBINVENTORY,l_module_name,'9');
797 INV_LOG_UTIL.TRACE('TASK_GROUP_ID:'||c1_rec.TASK_GROUP_ID,l_module_name,'9');
798 INV_LOG_UTIL.TRACE('TASK_ID:'||c1_rec.TASK_ID,l_module_name,'9');
799 INV_LOG_UTIL.TRACE('TASK_PRIORITY:'||c1_rec.TASK_PRIORITY,l_module_name,'9');
800 INV_LOG_UTIL.TRACE('TO_PROJECT_ID:'||c1_rec.TO_PROJECT_ID,l_module_name,'9');
801 INV_LOG_UTIL.TRACE('TO_TASK_ID:'||c1_rec.TO_TASK_ID,l_module_name,'9');
802 INV_LOG_UTIL.TRACE('TRANSACTION_ACTION_ID:'||c1_rec.TRANSACTION_ACTION_ID,l_module_name,'9');
803 INV_LOG_UTIL.TRACE('TRANSACTION_BATCH_ID:'||c1_rec.TRANSACTION_BATCH_ID,l_module_name,'9');
804 INV_LOG_UTIL.TRACE('TRANSACTION_BATCH_SEQ:'||c1_rec.TRANSACTION_BATCH_SEQ,l_module_name,'9');
805 INV_LOG_UTIL.TRACE('TRANSACTION_COST:'||c1_rec.TRANSACTION_COST,l_module_name,'9');
806 INV_LOG_UTIL.TRACE('TRANSACTION_DATE:'||c1_rec.TRANSACTION_DATE,l_module_name,'9');
807 INV_LOG_UTIL.TRACE('TRANSACTION_HEADER_ID:'||c1_rec.TRANSACTION_HEADER_ID,l_module_name,'9');
808 INV_LOG_UTIL.TRACE('TRANSACTION_LINE_NUMBER:'||c1_rec.TRANSACTION_LINE_NUMBER,l_module_name,'9');
809 INV_LOG_UTIL.TRACE('TRANSACTION_MODE:'||c1_rec.TRANSACTION_MODE,l_module_name,'9');
810 INV_LOG_UTIL.TRACE('TRANSACTION_QUANTITY:'||c1_rec.TRANSACTION_QUANTITY,l_module_name,'9');
811 INV_LOG_UTIL.TRACE('TRANSACTION_REFERENCE:'||c1_rec.TRANSACTION_REFERENCE,l_module_name,'9');
812 INV_LOG_UTIL.TRACE('TRANSACTION_SEQUENCE_ID:'||c1_rec.TRANSACTION_SEQUENCE_ID,l_module_name,'9');
813 INV_LOG_UTIL.TRACE('TRANSACTION_SOURCE_ID:'||c1_rec.TRANSACTION_SOURCE_ID,l_module_name,'9');
814 INV_LOG_UTIL.TRACE('TRANSACTION_SOURCE_NAME:'||c1_rec.TRANSACTION_SOURCE_NAME,l_module_name,'9');
815 INV_LOG_UTIL.TRACE('TRANSACTION_SOURCE_TYPE_ID:'||c1_rec.TRANSACTION_SOURCE_TYPE_ID,l_module_name,'9');
816 INV_LOG_UTIL.TRACE('TRANSACTION_STATUS:'||c1_rec.TRANSACTION_STATUS,l_module_name,'9');
817 INV_LOG_UTIL.TRACE('TRANSACTION_TEMP_ID:'||c1_rec.TRANSACTION_TEMP_ID,l_module_name,'9');
818 INV_LOG_UTIL.TRACE('TRANSACTION_TYPE_ID:'||c1_rec.TRANSACTION_TYPE_ID,l_module_name,'9');
819 INV_LOG_UTIL.TRACE('TRANSACTION_UOM:'||c1_rec.TRANSACTION_UOM,l_module_name,'9');
820 INV_LOG_UTIL.TRACE('TRANSFER_COST:'||c1_rec.TRANSFER_COST,l_module_name,'9');
821 INV_LOG_UTIL.TRACE('TRANSFER_COST_GROUP_ID:'||c1_rec.TRANSFER_COST_GROUP_ID,l_module_name,'9');
822 INV_LOG_UTIL.TRACE('TRANSFER_LPN_ID:'||c1_rec.TRANSFER_LPN_ID,l_module_name,'9');
823 INV_LOG_UTIL.TRACE('TRANSFER_ORGANIZATION:'||c1_rec.TRANSFER_ORGANIZATION,l_module_name,'9');
824 INV_LOG_UTIL.TRACE('TRANSFER_ORGANIZATION_TYPE:'||c1_rec.TRANSFER_ORGANIZATION_TYPE,l_module_name,'9');
825 INV_LOG_UTIL.TRACE('TRANSFER_OWNING_TP_TYPE:'||c1_rec.TRANSFER_OWNING_TP_TYPE,l_module_name,'9');
826 INV_LOG_UTIL.TRACE('TRANSFER_PERCENTAGE:'||c1_rec.TRANSFER_PERCENTAGE,l_module_name,'9');
827 INV_LOG_UTIL.TRACE('TRANSFER_PLANNING_TP_TYPE:'||c1_rec.TRANSFER_PLANNING_TP_TYPE,l_module_name,'9');
828 INV_LOG_UTIL.TRACE('TRANSFER_PRICE:'||c1_rec.TRANSFER_PRICE,l_module_name,'9');
829 INV_LOG_UTIL.TRACE('TRANSFER_SECONDARY_QUANTITY:'||c1_rec.TRANSFER_SECONDARY_QUANTITY,l_module_name,'9');
830 INV_LOG_UTIL.TRACE('TRANSFER_SECONDARY_UOM:'||c1_rec.TRANSFER_SECONDARY_UOM,l_module_name,'9');
831 INV_LOG_UTIL.TRACE('TRANSFER_SUBINVENTORY:'||c1_rec.TRANSFER_SUBINVENTORY,l_module_name,'9');
832 INV_LOG_UTIL.TRACE('TRANSFER_TO_LOCATION:'||c1_rec.TRANSFER_TO_LOCATION,l_module_name,'9');
833 INV_LOG_UTIL.TRACE('TRANSPORTATION_ACCOUNT:'||c1_rec.TRANSPORTATION_ACCOUNT,l_module_name,'9');
834 INV_LOG_UTIL.TRACE('TRANSPORTATION_COST:'||c1_rec.TRANSPORTATION_COST,l_module_name,'9');
835 INV_LOG_UTIL.TRACE('TRX_FLOW_HEADER_ID:'||c1_rec.TRX_FLOW_HEADER_ID,l_module_name,'9');
836 INV_LOG_UTIL.TRACE('TRX_SOURCE_DELIVERY_ID:'||c1_rec.TRX_SOURCE_DELIVERY_ID,l_module_name,'9');
837 INV_LOG_UTIL.TRACE('TRX_SOURCE_LINE_ID:'||c1_rec.TRX_SOURCE_LINE_ID,l_module_name,'9');
838 INV_LOG_UTIL.TRACE('USSGL_TRANSACTION_CODE:'||c1_rec.USSGL_TRANSACTION_CODE,l_module_name,'9');
839 INV_LOG_UTIL.TRACE('VALID_LOCATOR_FLAG:'||c1_rec.VALID_LOCATOR_FLAG,l_module_name,'9');
840 INV_LOG_UTIL.TRACE('VALID_SUBINVENTORY_FLAG:'||c1_rec.VALID_SUBINVENTORY_FLAG,l_module_name,'9');
841 INV_LOG_UTIL.TRACE('VALUE_CHANGE:'||c1_rec.VALUE_CHANGE,l_module_name,'9');
842 INV_LOG_UTIL.TRACE('VENDOR_LOT_NUMBER:'||c1_rec.VENDOR_LOT_NUMBER,l_module_name,'9');
843 INV_LOG_UTIL.TRACE('WAYBILL_AIRBILL:'||c1_rec.WAYBILL_AIRBILL,l_module_name,'9');
844 INV_LOG_UTIL.TRACE('WIP_COMMIT_FLAG:'||c1_rec.WIP_COMMIT_FLAG,l_module_name,'9');
845 INV_LOG_UTIL.TRACE('WIP_ENTITY_TYPE:'||c1_rec.WIP_ENTITY_TYPE,l_module_name,'9');
846 INV_LOG_UTIL.TRACE('WIP_SUPPLY_TYPE:'||c1_rec.WIP_SUPPLY_TYPE,l_module_name,'9');
847 INV_LOG_UTIL.TRACE('WMS_TASK_STATUS:'||c1_rec.WMS_TASK_STATUS,l_module_name,'9');
848 INV_LOG_UTIL.TRACE('WMS_TASK_TYPE:'||c1_rec.WMS_TASK_TYPE,l_module_name,'9');
849 INV_LOG_UTIL.TRACE('XFR_OWNING_ORGANIZATION_ID:'||c1_rec.XFR_OWNING_ORGANIZATION_ID,l_module_name,'9');
850 INV_LOG_UTIL.TRACE('XFR_PLANNING_ORGANIZATION_ID:'||c1_rec.XFR_PLANNING_ORGANIZATION_ID,l_module_name,'9');
851 INV_LOG_UTIL.TRACE('===>End of MMTT Record:'||i,l_module_name,'9');
852 ELSE
853 null;
854 END IF;
855 END LOOP;
856 END LOOP; ---}
857 IF l_debug_on THEN
858 INV_LOG_UTIL.TRACE('=============End of MMTT Data===============',l_module_name,'9');
859 END IF;
860 EXCEPTION
861 WHEN OTHERS THEN
862 IF l_debug_on THEN
863 INV_LOG_UTIL.TRACE('Error in Dumping MMTT',l_module_name,'9');
864 INV_LOG_UTIL.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM,l_module_name,'9');
865 END IF;
866 end dump_mmtt;
867
868
869 Procedure Check_Scripts(p_action_code in VARCHAR2,
870 p_trx_hdr_id IN NUMBER,
871 p_trx_temp_id IN NUMBER,
872 p_batch_id IN NUMBER)
873
874 IS
875
876 /*** Look for Data Mismatch and Add information to g_dc_table if found
877 Checks For Validation - Sub Locator Combination, Valid Revision for Item
878 - Dump Complete MMTT record
879
880 Checks for Java TM at the End - Check if records have been deleted from MMTT, MTI and check Serial Status
881 - Dump data from MSNI, MTLI, MSNI, MTLT
882 ***/
883 CURSOR C1 is
884 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
885 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
886 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
887 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
888 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
889 AND PROCESS_FLAG = 'Y'
890 AND ((TRANSACTION_ACTION_ID in (1,2,3,30,31,5)
891 AND inventory_item_id <> -1
892 AND EXISTS (
893 SELECT 'X'
894 FROM MTL_SECONDARY_INVENTORIES MSI
895 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
896 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
897 AND MSI.QUANTITY_TRACKED = 2))
898 OR (TRANSACTION_ACTION_ID = 21
899 AND EXISTS (
900 SELECT 'X'
901 FROM MTL_SECONDARY_INVENTORIES MSI,
902 MTL_SYSTEM_ITEMS ITM
903 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
904 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
905 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
906 AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
907 AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
908 AND ITM.INVENTORY_ASSET_FLAG = 'Y'
909 AND MSI.ASSET_INVENTORY = 2)));
910
911
912 CURSOR C2 is
913 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
914 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
915 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
916 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
917 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
918 AND PROCESS_FLAG = 'Y'
919 AND TRANSACTION_ACTION_ID in (2,5)
920 AND inventory_item_id <> -1
921 AND EXISTS (
922 SELECT 'X'
923 FROM MTL_SECONDARY_INVENTORIES MSI,
924 MTL_SYSTEM_ITEMS ITM
925 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
926 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
927 AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
928 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
929 AND ITM.INVENTORY_ASSET_FLAG = 'Y'
930 AND MSI.ASSET_INVENTORY = 2)
931 AND EXISTS (
932 SELECT 'X'
933 FROM MTL_SECONDARY_INVENTORIES MSI,
934 MTL_SYSTEM_ITEMS ITM
935 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY
936 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
937 AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
938 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
939 AND ITM.INVENTORY_ASSET_FLAG = 'Y'
940 AND MSI.ASSET_INVENTORY = 1);
941
942 CURSOR C3 is
943 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
944 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
945 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
946 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
947 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
948 AND PROCESS_FLAG = 'Y'
949 AND TRANSACTION_ACTION_ID = 3
950 AND inventory_item_id <> -1
951 AND EXISTS (
952 SELECT 'X'
953 FROM MTL_SECONDARY_INVENTORIES MSI
954 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
955 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
956 AND MSI.ASSET_INVENTORY = 2)
957 AND EXISTS (
958 SELECT 'X'
959 FROM MTL_SECONDARY_INVENTORIES MSI
960 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY
961 AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
962 AND MSI.ASSET_INVENTORY = 1);
963
964 CURSOR c4 is
965 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
966 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
967 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
968 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
969 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
970 AND PROCESS_FLAG = 'Y'
971 AND inventory_item_id <> -1
972 AND ((TRANSACTION_ACTION_ID in (1,2,3,30,31,5)
973 AND EXISTS (
974 SELECT 'X'
975 FROM MTL_SECONDARY_INVENTORIES MSI
976 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
977 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
978 AND MSI.QUANTITY_TRACKED = 2)
979 )) ;
980 CURSOR C5 is
981 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
982 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
983 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
984 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
985 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
986 AND PROCESS_FLAG = 'Y'
987 AND LOCATOR_ID IS NOT NULL
988 AND transaction_action_id not in (24,30)
989 AND inventory_item_id <> -1
990 AND EXISTS (
991 SELECT 'x'
992 FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
993 WHERE I.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
994 AND S.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
995 AND P.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
996 AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
997 AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
998 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
999 AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4, decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE) ,P.STOCK_LOCATOR_CONTROL_CODE) <> 1 ))
1000 AND NOT EXISTS (
1001 SELECT NULL
1002 FROM MTL_ITEM_LOCATIONS MIL
1003 WHERE MIL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1004 AND MIL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
1005 AND MIL.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID);
1006
1007 CURSOR C5_1 is
1008 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1009 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1010 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1011 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1012 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1013 AND PROCESS_FLAG = 'Y'
1014 AND LOCATOR_ID IS NULL
1015 AND inventory_item_id <> -1
1016 AND transaction_action_id not in (24,30)
1017 AND EXISTS (
1018 SELECT 'x'
1019 FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
1020 WHERE I.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1021 AND S.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
1022 AND P.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1023 AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
1024 AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
1025 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
1026 AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4, decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE) ,P.STOCK_LOCATOR_CONTROL_CODE) <> 1 ))
1027 UNION ALL
1028 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1029 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1030 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1031 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1032 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1033 AND PROCESS_FLAG = 'Y'
1034 AND LOCATOR_ID IS NOT NULL
1035 AND transaction_action_id not in (24,30)
1036 AND inventory_item_id <> -1
1037 AND EXISTS (
1038 SELECT 'x'
1039 FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
1040 WHERE I.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1041 AND S.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
1042 AND P.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1043 AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
1044 AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
1045 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
1046 AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4, decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE) ,P.STOCK_LOCATOR_CONTROL_CODE) =1));
1047
1048 CURSOR c6 is
1049 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1050 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1051 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1052 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1053 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1054 AND PROCESS_FLAG = 'Y'
1055 AND LOCATOR_ID IS NOT NULL
1056 AND transaction_action_id not in (24,30)
1057 AND inventory_item_id <> -1
1058 AND NOT EXISTS (
1059 SELECT NULL
1060 FROM MTL_SECONDARY_LOCATORS MSL,
1061 MTL_SYSTEM_ITEMS MSI
1062 WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1063 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1064 AND MSI.RESTRICT_LOCATORS_CODE = 1
1065 AND MSL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1066 AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1067 AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
1068 AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
1069 AND MSL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
1070 AND MSL.SECONDARY_LOCATOR = MMTT.LOCATOR_ID
1071 UNION
1072 SELECT NULL
1073 FROM MTL_SYSTEM_ITEMS ITM
1074 WHERE ITM.RESTRICT_LOCATORS_CODE = 2
1075 AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1076 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID);
1077
1078 CURSOR c7 is
1079 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1080 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1081 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1082 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1083 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1084 AND PROCESS_FLAG = 'Y'
1085 AND TRANSACTION_ACTION_ID IN (2,3,5)
1086 AND TRANSFER_TO_LOCATION IS NOT NULL
1087 AND inventory_item_id <> -1
1088 AND NOT EXISTS (
1089 SELECT NULL
1090 FROM MTL_ITEM_LOCATIONS MIL
1091 WHERE MIL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,3,
1092 MMTT.TRANSFER_ORGANIZATION,MMTT.ORGANIZATION_ID)
1093 AND MIL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
1094 AND MIL.INVENTORY_LOCATION_ID = MMTT.TRANSFER_TO_LOCATION
1095 AND TRUNC(MMTT.TRANSACTION_DATE) <= NVL(MIL.DISABLE_DATE,
1096 MMTT.TRANSACTION_DATE + 1));
1097
1098 CURSOR c8 is
1099 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1100 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1101 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1102 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1103 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1104 AND PROCESS_FLAG = 'Y'
1105 AND TRANSACTION_ACTION_ID in (2,21,3,5)
1106 AND TRANSFER_TO_LOCATION IS NOT NULL
1107 AND inventory_item_id <> -1
1108 AND NOT EXISTS (
1109 SELECT NULL
1110 FROM MTL_SECONDARY_LOCATORS MSL,
1111 MTL_SYSTEM_ITEMS MSI
1112 WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,2,
1113 MMTT.ORGANIZATION_ID,5,MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
1114 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1115 AND MSI.RESTRICT_LOCATORS_CODE = 1
1116 AND MSL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,2,
1117 MMTT.ORGANIZATION_ID,5,MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
1118 AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1119 AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
1120 AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
1121 AND MSL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
1122 AND MSL.SECONDARY_LOCATOR = MMTT.TRANSFER_TO_LOCATION
1123 UNION
1124 SELECT NULL
1125 FROM MTL_SYSTEM_ITEMS MSI
1126 WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,2,
1127 MMTT.ORGANIZATION_ID,5,MMTT.ORGANIZATION_ID,MMTT.TRANSFER_ORGANIZATION)
1128 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1129 AND MSI.RESTRICT_LOCATORS_CODE = 2);
1130
1131 CURSOR c9 is
1132 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1133 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1134 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1135 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1136 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1137 AND PROCESS_FLAG = 'Y'
1138 AND TRANSACTION_ACTION_ID NOT IN (24,33,34,30)
1139 AND inventory_item_id <> -1
1140 AND NOT EXISTS (
1141 SELECT NULL
1142 FROM MTL_ITEM_REVISIONS MIR,
1143 MTL_SYSTEM_ITEMS MSI
1144 WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
1145 AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1146 AND MIR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1147 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1148 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1149 AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
1150 AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
1151 AND MIR.REVISION = MMTT.REVISION
1152 UNION
1153 SELECT NULL
1154 FROM MTL_SYSTEM_ITEMS ITM
1155 WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
1156 AND MMTT.REVISION IS NULL
1157 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1158 AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID);
1159
1160
1161 CURSOR c10 is
1162 SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
1163 TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
1164 ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
1165 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1166 WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
1167 AND PROCESS_FLAG = 'Y'
1168 AND TRANSACTION_ACTION_ID = 3
1169 AND inventory_item_id <> -1
1170 AND NOT EXISTS (
1171 SELECT NULL
1172 FROM MTL_ITEM_REVISIONS MIR,
1173 MTL_SYSTEM_ITEMS MSI
1174 WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
1175 AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1176 AND MIR.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
1177 AND MIR.REVISION = MMTT.REVISION
1178 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1179 AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
1180 AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
1181 AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
1182 UNION
1183 SELECT NULL
1184 FROM MTL_SYSTEM_ITEMS ITM
1185 WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
1186 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1187 AND ITM.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION);
1188
1189 CURSOR c_org(p_org_id in NUMBER) IS
1190 SELECT organization_code
1191 FROM org_organization_definitions
1192 WHERE organization_id = p_org_id;
1193
1194 CURSOR c_trx_type(p_trx_type_id IN NUMBER) IS
1195 SELECT TRANSACTION_TYPE_NAME
1196 FROM mtl_transaction_types
1197 WHERE TRANSACTION_TYPE_ID=p_trx_type_id;
1198
1199 CURSOR c_source_type(p_source_type_id IN NUMBER) IS
1200 SELECT transaction_source_type_name
1201 FROM mtl_txn_source_types
1202 WHERE transaction_source_type_id=p_source_type_id;
1203
1204 CURSOR c_action(p_action_id IN NUMBER) IS
1205 SELECT meaning
1206 FROM mfg_lookups
1207 WHERE lookup_code=p_action_id
1208 AND lookup_type='MTL_TRANSACTION_ACTION';
1209
1210 CURSOR c_item(p_org_id in number, p_item_id in number) IS
1211 SELECT concatenated_segments
1212 FROM mtl_system_items_kfv
1213 WHERE inventory_item_id = p_item_id
1214 AND organization_id = p_org_id;
1215
1216 CURSOR c21 is
1217 ---- First Part is to Select For Non Lot controlled Items
1218 ---- Second Part is to Select for Lot Controlled Items
1219
1220 SELECT a.TRANSACTION_TEMP_ID,a.TRANSACTION_TYPE_ID,a.TRANSACTIOn_QUANTITY,
1221 a.TRANSACTION_SOURCE_TYPE_ID,a.TRANSACTION_ACTION_ID,
1222 a.ORGANIZATION_ID,a.TRANSFER_ORGANIZATION,a.INVENTORY_ITEM_ID,
1223 A.SUBINVENTORY_CODE,A.LOCATOR_ID,A.REVISION,A.LOT_NUMBER,
1224 A.TRANSFER_SUBINVENTORY,a.TRANSFER_TO_LOCATION,
1225 b.CURRENT_SUBINVENTORY_CODE,b.CURRENT_LOCATOR_ID,b.REVISION current_revision,
1226 b.CURRENT_STATUS,b.SERIAL_NUMBER,b.current_organization_id,d.SERIAL_NUMBER_CONTROL_CODE
1227 FROM MTL_MATERIAL_TRANSACTIONS_TEMP a,MTL_SERIAL_NUMBERS b,
1228 MTL_SERIAL_NUMBERS_TEMP c,MTL_SYSTEM_ITEMS d
1229 WHERE A.TRANSACTION_TEMP_ID = C.TRANSACTION_TEMP_ID
1230 AND LPAD(B.SERIAL_NUMBER,30) >= LPAD(C.FM_SERIAL_NUMBER,30)
1231 AND LPAD(NVL(B.SERIAL_NUMBER,'-99'),30) <= LPAD(NVL(C.TO_SERIAL_NUMBER,'-99'),30)
1232 AND A.ORGANIZATION_ID = nvl(B.CURRENT_ORGANIZATION_ID,A.ORGANIZATION_ID)
1233 AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
1234 AND A.TRANSACTION_HEADER_ID=P_TRX_HDR_ID
1235 AND A.TRANSACTION_TEMP_ID=nvl(P_TRX_TEMP_ID,A.TRANSACTION_TEMP_ID)
1236 AND A.ORGANIZATION_ID = d.ORGANIZATION_ID
1237 AND A.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
1238 AND A.PROCESS_FLAG = 'Y'
1239 AND A.INVENTORY_ITEM_ID <> -1
1240 AND ((d.SERIAL_NUMBER_CONTROL_CODE =6 and ((a.TRANSACTION_ACTION_ID =1 and a.transaction_source_type_id=2) or (a.transaction_action_id in (3,21) and a.transaction_source_type_id=8)))
1241 OR d.SERIAL_NUMBER_CONTROL_CODE in (2,5));
1242 l_debug_on BOOLEAN;
1243 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'CHECK_SCRIPTS';
1244 l_profile VARCHAR2(2000);
1245 exp_to_ast_allowed NUMBER :=0;
1246 i number :=1;
1247 BEGIN
1248
1249 /*----------------------------------------------------------------------+
1250 | Validate Subinventory for the following:
1251 | You cannot issue from non tracked
1252 | You cannot issue from expense sub for intransit shipment
1253 | You cannot transfer from expense sub to asset sub for asset items
1254 | If the expense to asset transfer allowed profiel is set then
1255 | You cannot issue from a non-tracked sub
1256 | All other transfers are valid
1257 | exp_to_ast_allowed = 1 means that the exp to ast trx are not alowed
1258 ------------------------------------------------------------------------+*/
1259
1260 IF g_debug_on = 1 THEN
1261 l_debug_on :=TRUE;
1262 else
1263 l_debug_on :=FALSE;
1264 END IF;
1265
1266 IF (p_action_code='Validate MMTT') then ---{
1267 SELECT FND_PROFILE.VALUE('INV:EXPENSE_TO_ASSET_TRANSFER')
1268 INTO l_profile
1269 FROM dual;
1270
1271 IF SQL%FOUND THEN
1272 IF l_profile = '2' THEN
1273 exp_to_ast_allowed := 1;
1274 ELSE
1275 exp_to_ast_allowed := 2;
1276 END IF;
1277 ELSE
1278 exp_to_ast_allowed := 1;
1279 END IF;
1280 IF exp_to_ast_allowed = 1 THEN ---{
1281 FOR c1_rec in C1 loop
1282 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1283 g_dc_table(i).msg := 'C1=>Invalid Subinventory: The subinventories have incompatible types with respect to transaction type and item type' ;
1284 g_dc_table(i).trx_temp_id := c1_rec.transaction_temp_id;
1285
1286 if (c1_rec.organization_id is not null) then
1287 for c_org_rec in c_org (c1_rec.organization_id) loop
1288 g_dc_table(i).organization_code := c_org_rec.organization_code;
1289 end loop;
1290 end if;
1291 if (c1_rec.transfer_organization is not null) then
1292 for c_org_rec in c_org (c1_rec.transfer_organization) loop
1293 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1294 end loop;
1295 end if;
1296 if (c1_rec.transaction_type_id is not null) then
1297 for c_trx_type_rec in c_trx_type (c1_rec.transaction_type_id) loop
1298 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1299 end loop;
1300 end if;
1301 if (c1_rec.transaction_source_type_id is not null) then
1302 for c_source_type_rec in c_source_type (c1_rec.transaction_source_type_id) loop
1303 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1304 end loop;
1305 end if;
1306 if (c1_rec.transaction_action_id is not null) then
1307 for c_action_rec in c_action(c1_rec.transaction_action_id) loop
1308 g_dc_table(i).action_code := c_action_rec.meaning;
1309 end loop;
1310 end if;
1311 if (c1_rec.organization_id is not null and c1_rec.inventory_item_id is not null) then
1312 for c_item_rec in c_item(c1_rec.organization_id,c1_rec.inventory_item_id) loop
1313 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1314 end loop;
1315 end if;
1316 i :=i+1;
1317 END LOOP;
1318 FOR c2_rec in C2 loop
1319 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1320 g_dc_table(i).msg := 'C2=>Invalid Subinventory: The subinventories have incompatible types with respect to transaction type and item type' ;
1321 g_dc_table(i).trx_temp_id := c2_rec.transaction_temp_id;
1322 if (c2_rec.organization_id is not null) then
1323 for c_org_rec in c_org (c2_rec.organization_id) loop
1324 g_dc_table(i).organization_code := c_org_rec.organization_code;
1325 end loop;
1326 end if;
1327 if (c2_rec.transfer_organization is not null) then
1328 for c_org_rec in c_org (c2_rec.transfer_organization) loop
1329 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1330 end loop;
1331 end if;
1332 if (c2_rec.transaction_type_id is not null) then
1333 for c_trx_type_rec in c_trx_type (c2_rec.transaction_type_id) loop
1334 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1335 end loop;
1336 end if;
1337 if (c2_rec.transaction_source_type_id is not null) then
1338 for c_source_type_rec in c_source_type (c2_rec.transaction_source_type_id) loop
1339 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1340 end loop;
1341 end if;
1342 if (c2_rec.transaction_action_id is not null) then
1343 for c_action_rec in c_action(c2_rec.transaction_action_id) loop
1344 g_dc_table(i).action_code := c_action_rec.meaning;
1345 end loop;
1346 end if;
1347 if (c2_rec.organization_id is not null and c2_rec.inventory_item_id is not null) then
1348 for c_item_rec in c_item(c2_rec.organization_id,c2_rec.inventory_item_id) loop
1349 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1350 end loop;
1351 end if;
1352 i :=i+1;
1353 END LOOP;
1354 FOR c3_rec in c3 loop
1355 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1356 g_dc_table(i).msg := 'C3=>Invalid Subinventory: The subinventories have incompatible types with respect to transaction type and item type' ;
1357 g_dc_table(i).trx_temp_id := c3_rec.transaction_temp_id;
1358 if (c3_rec.organization_id is not null) then
1359 for c_org_rec in c_org (c3_rec.organization_id) loop
1360 g_dc_table(i).organization_code := c_org_rec.organization_code;
1361 end loop;
1362 end if;
1363 if (c3_rec.transfer_organization is not null) then
1364 for c_org_rec in c_org (c3_rec.transfer_organization) loop
1365 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1366 end loop;
1367 end if;
1368 if (c3_rec.transaction_type_id is not null) then
1369 for c_trx_type_rec in c_trx_type (c3_rec.transaction_type_id) loop
1370 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1371 end loop;
1372 end if;
1373 if (c3_rec.transaction_source_type_id is not null) then
1374 for c_source_type_rec in c_source_type (c3_rec.transaction_source_type_id) loop
1375 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1376 end loop;
1377 end if;
1378 if (c3_rec.transaction_action_id is not null) then
1379 for c_action_rec in c_action(c3_rec.transaction_action_id) loop
1380 g_dc_table(i).action_code := c_action_rec.meaning;
1381 end loop;
1382 end if;
1383 if (c3_rec.organization_id is not null and c3_rec.inventory_item_id is not null) then
1384 for c_item_rec in c_item(c3_rec.organization_id,c3_rec.inventory_item_id) loop
1385 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1386 end loop;
1387 end if;
1388 i :=i+1;
1389 END LOOP;
1390 ----}
1391 ELSE
1392 ---{
1393 FOR c4_rec in c4 loop
1394 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1395 g_dc_table(i).msg := 'C4=>Invalid Subinventory: The subinventories have incompatible types with respect to transaction type and item type' ;
1396 g_dc_table(i).trx_temp_id := c4_rec.transaction_temp_id;
1397 if (c4_rec.organization_id is not null) then
1398 for c_org_rec in c_org (c4_rec.organization_id) loop
1399 g_dc_table(i).organization_code := c_org_rec.organization_code;
1400 end loop;
1401 end if;
1402 if (c4_rec.transfer_organization is not null) then
1403 for c_org_rec in c_org (c4_rec.transfer_organization) loop
1404 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1405 end loop;
1406 end if;
1407 if (c4_rec.transaction_type_id is not null) then
1408 for c_trx_type_rec in c_trx_type (c4_rec.transaction_type_id) loop
1409 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1410 end loop;
1411 end if;
1412 if (c4_rec.transaction_source_type_id is not null) then
1413 for c_source_type_rec in c_source_type (c4_rec.transaction_source_type_id) loop
1414 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1415 end loop;
1416 end if;
1417 if (c4_rec.transaction_action_id is not null) then
1418 for c_action_rec in c_action(c4_rec.transaction_action_id) loop
1419 g_dc_table(i).action_code := c_action_rec.meaning;
1420 end loop;
1421 end if;
1422 if (c4_rec.organization_id is not null and c4_rec.inventory_item_id is not null) then
1423 for c_item_rec in c_item(c4_rec.organization_id,c4_rec.inventory_item_id) loop
1424 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1425 end loop;
1426 end if;
1427 i :=i+1;
1428 END LOOP;
1429 END IF; ----}
1430
1431 /*-------------------------------------------------------------+
1432 | Validating locators
1433 +-------------------------------------------------------------*/
1434 FOR c5_rec in C5 loop
1435 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1436 g_dc_table(i).msg := 'C5=>Invalid Locator: Locator is not valid. Please re-enter' ;
1437 g_dc_table(i).trx_temp_id := c5_rec.transaction_temp_id;
1438 if (c5_rec.organization_id is not null) then
1439 for c_org_rec in c_org (c5_rec.organization_id) loop
1440 g_dc_table(i).organization_code := c_org_rec.organization_code;
1441 end loop;
1442 end if;
1443 if (c5_rec.transfer_organization is not null) then
1444 for c_org_rec in c_org (c5_rec.transfer_organization) loop
1445 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1446 end loop;
1447 end if;
1448 if (c5_rec.transaction_type_id is not null) then
1449 for c_trx_type_rec in c_trx_type (c5_rec.transaction_type_id) loop
1450 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1451 end loop;
1452 end if;
1453 if (c5_rec.transaction_source_type_id is not null) then
1454 for c_source_type_rec in c_source_type (c5_rec.transaction_source_type_id) loop
1455 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1456 end loop;
1457 end if;
1458 if (c5_rec.transaction_action_id is not null) then
1459 for c_action_rec in c_action(c5_rec.transaction_action_id) loop
1460 g_dc_table(i).action_code := c_action_rec.meaning;
1461 end loop;
1462 end if;
1463 if (c5_rec.organization_id is not null and c5_rec.inventory_item_id is not null) then
1464 for c_item_rec in c_item(c5_rec.organization_id,c5_rec.inventory_item_id) loop
1465 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1466 end loop;
1467 end if;
1468 i :=i+1;
1469 END LOOP;
1470 FOR c5_1_rec in C5_1 loop
1471 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1472 g_dc_table(i).msg := 'c5_1=>Invalid Locator: Locator is null or Locator is being passed when not expected' ;
1473 g_dc_table(i).trx_temp_id := c5_1_rec.transaction_temp_id;
1474 if (c5_1_rec.organization_id is not null) then
1475 for c_org_rec in c_org (c5_1_rec.organization_id) loop
1476 g_dc_table(i).organization_code := c_org_rec.organization_code;
1477 end loop;
1478 end if;
1479 if (c5_1_rec.transfer_organization is not null) then
1480 for c_org_rec in c_org (c5_1_rec.transfer_organization) loop
1481 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1482 end loop;
1483 end if;
1484 if (c5_1_rec.transaction_type_id is not null) then
1485 for c_trx_type_rec in c_trx_type (c5_1_rec.transaction_type_id) loop
1486 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1487 end loop;
1488 end if;
1489 if (c5_1_rec.transaction_source_type_id is not null) then
1490 for c_source_type_rec in c_source_type (c5_1_rec.transaction_source_type_id) loop
1491 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1492 end loop;
1493 end if;
1494 if (c5_1_rec.transaction_action_id is not null) then
1495 for c_action_rec in c_action(c5_1_rec.transaction_action_id) loop
1496 g_dc_table(i).action_code := c_action_rec.meaning;
1497 end loop;
1498 end if;
1499 if (c5_1_rec.organization_id is not null and c5_1_rec.inventory_item_id is not null) then
1500 for c_item_rec in c_item(c5_1_rec.organization_id,c5_1_rec.inventory_item_id) loop
1501 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1502 end loop;
1503 end if;
1504 i :=i+1;
1505 END LOOP;
1506 FOR c6_rec in c6 loop
1507 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1508 g_dc_table(i).msg := 'C6=>Invalid Locator: Locator is not in the restricted list of locators for the item' ;
1509 g_dc_table(i).trx_temp_id := c6_rec.transaction_temp_id;
1510 if (c6_rec.organization_id is not null) then
1511 for c_org_rec in c_org (c6_rec.organization_id) loop
1512 g_dc_table(i).organization_code := c_org_rec.organization_code;
1513 end loop;
1514 end if;
1515 if (c6_rec.transfer_organization is not null) then
1516 for c_org_rec in c_org (c6_rec.transfer_organization) loop
1517 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1518 end loop;
1519 end if;
1520 if (c6_rec.transaction_type_id is not null) then
1521 for c_trx_type_rec in c_trx_type (c6_rec.transaction_type_id) loop
1522 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1523 end loop;
1524 end if;
1525 if (c6_rec.transaction_source_type_id is not null) then
1526 for c_source_type_rec in c_source_type (c6_rec.transaction_source_type_id) loop
1527 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1528 end loop;
1529 end if;
1530 if (c6_rec.transaction_action_id is not null) then
1531 for c_action_rec in c_action(c6_rec.transaction_action_id) loop
1532 g_dc_table(i).action_code := c_action_rec.meaning;
1533 end loop;
1534 end if;
1535 if (c6_rec.organization_id is not null and c6_rec.inventory_item_id is not null) then
1536 for c_item_rec in c_item(c6_rec.organization_id,c6_rec.inventory_item_id) loop
1537 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1538 end loop;
1539 end if;
1540 i :=i+1;
1541 END LOOP;
1542
1543 /*-----------------------------------------------------------+
1544 | Validating transfer locators against transfer organization
1545 +-----------------------------------------------------------*/
1546 FOR c7_rec in c7 loop
1547 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1548 g_dc_table(i).msg := 'C7=>Invalid transfer locator: Transfer locator is not valid for the item in the transfer organization' ;
1549 g_dc_table(i).trx_temp_id := c7_rec.transaction_temp_id;
1550 if (c7_rec.organization_id is not null) then
1551 for c_org_rec in c_org (c7_rec.organization_id) loop
1552 g_dc_table(i).organization_code := c_org_rec.organization_code;
1553 end loop;
1554 end if;
1555 if (c7_rec.transfer_organization is not null) then
1556 for c_org_rec in c_org (c7_rec.transfer_organization) loop
1557 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1558 end loop;
1559 end if;
1560 if (c7_rec.transaction_type_id is not null) then
1561 for c_trx_type_rec in c_trx_type (c7_rec.transaction_type_id) loop
1562 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1563 end loop;
1564 end if;
1565 if (c7_rec.transaction_source_type_id is not null) then
1566 for c_source_type_rec in c_source_type (c7_rec.transaction_source_type_id) loop
1567 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1568 end loop;
1569 end if;
1570 if (c7_rec.transaction_action_id is not null) then
1571 for c_action_rec in c_action(c7_rec.transaction_action_id) loop
1572 g_dc_table(i).action_code := c_action_rec.meaning;
1573 end loop;
1574 end if;
1575 if (c7_rec.organization_id is not null and c7_rec.inventory_item_id is not null) then
1576 for c_item_rec in c_item(c7_rec.organization_id,c7_rec.inventory_item_id) loop
1577 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1578 end loop;
1579 end if;
1580 i :=i+1;
1581 END LOOP;
1582
1583 /*------------------------------------------------------+
1584 | Validating transfer locators for restricted list
1585 +------------------------------------------------------*/
1586 FOR c8_rec in c8 loop
1587 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1588 g_dc_table(i).msg := 'C8=>Invalid transfer locator: Transfer locator is not in the restricted list for the given item in transfer organization' ;
1589 g_dc_table(i).trx_temp_id := c8_rec.transaction_temp_id;
1590 if (c8_rec.organization_id is not null) then
1591 for c_org_rec in c_org (c8_rec.organization_id) loop
1592 g_dc_table(i).organization_code := c_org_rec.organization_code;
1593 end loop;
1594 end if;
1595 if (c8_rec.transfer_organization is not null) then
1596 for c_org_rec in c_org (c8_rec.transfer_organization) loop
1597 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1598 end loop;
1599 end if;
1600 if (c8_rec.transaction_type_id is not null) then
1601 for c_trx_type_rec in c_trx_type (c8_rec.transaction_type_id) loop
1602 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1603 end loop;
1604 end if;
1605 if (c8_rec.transaction_source_type_id is not null) then
1606 for c_source_type_rec in c_source_type (c8_rec.transaction_source_type_id) loop
1607 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1608 end loop;
1609 end if;
1610 if (c8_rec.transaction_action_id is not null) then
1611 for c_action_rec in c_action(c8_rec.transaction_action_id) loop
1612 g_dc_table(i).action_code := c_action_rec.meaning;
1613 end loop;
1614 end if;
1615 if (c8_rec.organization_id is not null and c8_rec.inventory_item_id is not null) then
1616 for c_item_rec in c_item(c8_rec.organization_id,c8_rec.inventory_item_id) loop
1617 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1618 end loop;
1619 end if;
1620 i :=i+1;
1621 END LOOP;
1622
1623 /*--------------------------------------------------+
1624 | Validating item revisions
1625 +--------------------------------------------------*/
1626 FOR c9_rec in c9 loop
1627 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1628 g_dc_table(i).msg := 'C9=>Invalid item revision: The item revision is not valid. Please re-enter' ;
1629 g_dc_table(i).trx_temp_id := c9_rec.transaction_temp_id;
1630 if (c9_rec.organization_id is not null) then
1631 for c_org_rec in c_org (c9_rec.organization_id) loop
1632 g_dc_table(i).organization_code := c_org_rec.organization_code;
1633 end loop;
1634 end if;
1635 if (c9_rec.transfer_organization is not null) then
1636 for c_org_rec in c_org (c9_rec.transfer_organization) loop
1637 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1638 end loop;
1639 end if;
1640 if (c9_rec.transaction_type_id is not null) then
1641 for c_trx_type_rec in c_trx_type (c9_rec.transaction_type_id) loop
1642 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1643 end loop;
1644 end if;
1645 if (c9_rec.transaction_source_type_id is not null) then
1646 for c_source_type_rec in c_source_type (c9_rec.transaction_source_type_id) loop
1647 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1648 end loop;
1649 end if;
1650 if (c9_rec.transaction_action_id is not null) then
1651 for c_action_rec in c_action(c9_rec.transaction_action_id) loop
1652 g_dc_table(i).action_code := c_action_rec.meaning;
1653 end loop;
1654 end if;
1655 if (c9_rec.organization_id is not null and c9_rec.inventory_item_id is not null) then
1656 for c_item_rec in c_item(c9_rec.organization_id,c9_rec.inventory_item_id) loop
1657 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1658 end loop;
1659 end if;
1660 i :=i+1;
1661 END LOOP;
1662 FOR c10_rec in c10 loop
1663 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1664 g_dc_table(i).msg := 'C10=>Invalid item revision: The item revision specified must be defined in both Source and Destination organizations for direct inter-org transfers.' ;
1665 g_dc_table(i).trx_temp_id := c10_rec.transaction_temp_id;
1666 if (c10_rec.organization_id is not null) then
1667 for c_org_rec in c_org (c10_rec.organization_id) loop
1668 g_dc_table(i).organization_code := c_org_rec.organization_code;
1669 end loop;
1670 end if;
1671 if (c10_rec.transfer_organization is not null) then
1672 for c_org_rec in c_org (c10_rec.transfer_organization) loop
1673 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1674 end loop;
1675 end if;
1676 if (c10_rec.transaction_type_id is not null) then
1677 for c_trx_type_rec in c_trx_type (c10_rec.transaction_type_id) loop
1678 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1679 end loop;
1680 end if;
1681 if (c10_rec.transaction_source_type_id is not null) then
1682 for c_source_type_rec in c_source_type (c10_rec.transaction_source_type_id) loop
1683 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1684 end loop;
1685 end if;
1686 if (c10_rec.transaction_action_id is not null) then
1687 for c_action_rec in c_action(c10_rec.transaction_action_id) loop
1688 g_dc_table(i).action_code := c_action_rec.meaning;
1689 end loop;
1690 end if;
1691 if (c10_rec.organization_id is not null and c10_rec.inventory_item_id is not null) then
1692 for c_item_rec in c_item(c10_rec.organization_id,c10_rec.inventory_item_id) loop
1693 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1694 end loop;
1695 end if;
1696 i :=i+1;
1697 END LOOP;
1698 end if; ---}
1699
1700 IF (p_action_code='Validate Serial') then ---{
1701 FOR c21_rec IN c21 LOOP
1702 IF (c21_rec.TRANSACTION_ACTION_ID in (2,3,28) and (c21_rec.transaction_quantity < 0)) THEN ---{
1703 G_DCP_MSG:= G_DCP_MSG||'DCP-Skipping DCP Check (for 2,3,28 and qty < 0)
1704 ';
1705 ELSE --}{
1706 if (add_serial_data(c21_rec.transaction_quantity,
1707 c21_rec.serial_number_control_code,
1708 c21_rec.transfer_organization,
1709 c21_rec.inventory_item_id)) then --{
1710 IF (c21_rec.TRANSACTION_ACTION_ID not in (2,28)) THEN ---{
1711 IF (nvl(c21_rec.subinventory_code,'-A') <> nvl(c21_rec.current_subinventory_code,'-A')
1712 OR nvl(c21_rec.organization_id,-999) <> nvl(c21_rec.current_organization_id,-999)
1713 OR nvl(c21_rec.locator_id,-999) <> nvl(c21_rec.current_locator_id,-999)
1714 OR nvl(c21_rec.revision,'@@@') <> nvl(c21_rec.current_revision,'@@@')
1715 OR ((c21_rec.current_status <> g_ser_check_tab(c21_rec.transaction_action_id).serial_status)
1716 AND (c21_rec.current_status <> 6 ))) THEN
1717 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1718 g_dc_table(i).msg := 'C21=> Invalid Serial Attribute: One or More Serial Attributes for Serial#'||c21_rec.serial_number||' mismatch.';
1719 g_dc_table(i).trx_temp_id := c21_rec.transaction_temp_id;
1720 if (c21_rec.organization_id is not null) then
1721 for c_org_rec in c_org (c21_rec.organization_id) loop
1722 g_dc_table(i).organization_code := c_org_rec.organization_code;
1723 end loop;
1724 end if;
1725 if (c21_rec.transfer_organization is not null) then
1726 for c_org_rec in c_org (c21_rec.transfer_organization) loop
1727 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1728 end loop;
1729 end if;
1730 if (c21_rec.transaction_type_id is not null) then
1731 for c_trx_type_rec in c_trx_type (c21_rec.transaction_type_id) loop
1732 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1733 end loop;
1734 end if;
1735 if (c21_rec.transaction_source_type_id is not null) then
1736 for c_source_type_rec in c_source_type (c21_rec.transaction_source_type_id) loop
1737 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1738 end loop;
1739 end if;
1740 if (c21_rec.transaction_action_id is not null) then
1741 for c_action_rec in c_action(c21_rec.transaction_action_id) loop
1742 g_dc_table(i).action_code := c_action_rec.meaning;
1743 end loop;
1744 end if;
1745 if (c21_rec.organization_id is not null and c21_rec.inventory_item_id is not null) then
1746 for c_item_rec in c_item(c21_rec.organization_id,c21_rec.inventory_item_id) loop
1747 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1748 end loop;
1749 end if;
1750 i :=i+1;
1751
1752 G_DCP_MSG:= G_DCP_MSG||'Org - Current Org:'||c21_rec.organization_id||'-'||c21_rec.current_organization_id||'
1753 ';
1754 G_DCP_MSG :=G_DCP_MSG||'sub - Current sub:'||c21_rec.subinventory_code||'-'||c21_rec.current_subinventory_code||'
1755 ';
1756 G_DCP_MSG :=G_DCP_MSG||'Loc - Current Loc:'||c21_rec.locator_id||'-'||c21_rec.current_locator_id||'
1757 ';
1758 G_DCP_MSG :=G_DCP_MSG||'Rev - Current Rev:'||c21_rec.revision||'-'||c21_rec.current_revision||'
1759 ';
1760 G_DCP_MSG :=G_DCP_MSG||'Status - Expected Status:'||c21_rec.current_status||'-'||g_ser_check_tab(c21_rec.transaction_action_id).serial_status||'
1761 ';
1762
1763 END IF;
1764 ELSE ---}{
1765 IF (nvl(c21_rec.transfer_subinventory,'-A') <> nvl(c21_rec.current_subinventory_code,'-A')
1766 OR nvl(c21_rec.transfer_organization,c21_rec.current_organization_id) <> nvl(c21_rec.current_organization_id,-999)
1767 OR nvl(c21_rec.transfer_to_location,-999) <> nvl(c21_rec.current_locator_id,-999)
1768 OR nvl(c21_rec.revision,'@@@') <> nvl(c21_rec.current_revision,'@@@')
1769 OR ((c21_rec.current_status <> g_ser_check_tab(c21_rec.transaction_action_id).serial_status)
1770 AND (c21_rec.current_status <> 6))) THEN
1771 g_dc_table(i).trx_hdr_id := p_trx_hdr_id;
1772 g_dc_table(i).msg := 'C21=> Invalid Serial Attribute: One or More Serial Attributes for Serial#'||c21_rec.serial_number||' mismatch.';
1773 g_dc_table(i).trx_temp_id := c21_rec.transaction_temp_id;
1774 if (c21_rec.organization_id is not null) then
1775 for c_org_rec in c_org (c21_rec.organization_id) loop
1776 g_dc_table(i).organization_code := c_org_rec.organization_code;
1777 end loop;
1778 end if;
1779 if (c21_rec.transfer_organization is not null) then
1780 for c_org_rec in c_org (c21_rec.transfer_organization) loop
1781 g_dc_table(i).xfer_org_code := c_org_rec.organization_code;
1782 end loop;
1783 end if;
1784 if (c21_rec.transaction_type_id is not null) then
1785 for c_trx_type_rec in c_trx_type (c21_rec.transaction_type_id) loop
1786 g_dc_table(i).trx_type := c_trx_type_rec.transaction_type_name;
1787 end loop;
1788 end if;
1789 if (c21_rec.transaction_source_type_id is not null) then
1790 for c_source_type_rec in c_source_type (c21_rec.transaction_source_type_id) loop
1791 g_dc_table(i).source_type := c_source_type_rec.transaction_source_type_name;
1792 end loop;
1793 end if;
1794 if (c21_rec.transaction_action_id is not null) then
1795 for c_action_rec in c_action(c21_rec.transaction_action_id) loop
1796 g_dc_table(i).action_code := c_action_rec.meaning;
1797 end loop;
1798 end if;
1799 if (c21_rec.organization_id is not null and c21_rec.inventory_item_id is not null) then
1800 for c_item_rec in c_item(c21_rec.organization_id,c21_rec.inventory_item_id) loop
1801 g_dc_table(i).item_name := c_item_rec.concatenated_segments;
1802 end loop;
1803 end if;
1804 i :=i+1;
1805 G_DCP_MSG :=G_DCP_MSG||'Transfer Org - Current Org:'||c21_rec.transfer_organization||' - '||c21_rec.current_organization_id||'
1806 ' ;
1807 G_DCP_MSG :=G_DCP_MSG||'Transfer Sub - Current Sub:'||c21_rec.transfer_subinventory||' - '||c21_rec.current_subinventory_code||'
1808 ';
1809 G_DCP_MSG :=G_DCP_MSG||'Transfer Loc - Current Loc:'||c21_rec.transfer_to_location||' - '||c21_rec.current_locator_id||'
1810 ';
1811 G_DCP_MSG :=G_DCP_MSG||'Rev - Current Rev:'||c21_rec.revision||' - '||c21_rec.current_revision||'
1812 ';
1813 G_DCP_MSG :=G_DCP_MSG||'Status - Expected Status:'||c21_rec.current_status||' - '||g_ser_check_tab(c21_rec.transaction_action_id).serial_status||'
1814 ';
1815 END IF;
1816 END IF; ---}
1817 ELSE ---}{
1818 IF l_debug_on THEN
1819 INV_LOG_UTIL.TRACE('DCP-Error loading Serial Checks data',l_module_name,'9');
1820 END IF;
1821 END IF; --}
1822 END IF; --}
1823 END LOOP;
1824 END IF; ---}
1825
1826 IF (i > 1) THEN
1827 IF l_debug_on THEN
1828 INV_LOG_UTIL.TRACE('Data is inconsistent',l_module_name,'9');
1829 END IF;
1830 END IF;
1831
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834 IF l_debug_on THEN
1835 INV_LOG_UTIL.TRACE('Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,180),l_module_name,'9');
1836 INV_LOG_UTIL.TRACE('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR',l_module_name,'9');
1837 END IF;
1838 END Check_Scripts;
1839
1840 Procedure Validate_data(p_dcp_event IN VARCHAR2,
1841 p_trx_hdr_id IN VARCHAR2,
1842 p_temp_id IN NUMBER,
1843 p_batch_id IN NUMBER,
1844 p_raise_exception IN VARCHAR2,
1845 x_return_status OUT NOCOPY VARCHAR2)
1846 IS
1847 l_debug_on BOOLEAN;
1848 l_module_name CONSTANT VARCHAR2(100) :=G_PKG_NAME || '.' || 'Validate_data';
1849 i NUMBER;
1850 l_header_id NUMBER := 0;
1851 BEGIN
1852 IF g_debug_on = 1 THEN
1853 l_debug_on :=TRUE;
1854 else
1855 l_debug_on :=FALSE;
1856 END IF;
1857 --inv_log_util.TRACE
1858 IF l_debug_on THEN
1859 INV_LOG_UTIL.TRACE('p_trx_hdr_id='||p_trx_hdr_id,l_module_name,9);
1860 INV_LOG_UTIL.TRACE('p_raise_exception'||p_raise_Exception,l_module_name,9);
1861 END IF;
1862 check_scripts(p_action_code => p_dcp_event,
1863 p_trx_hdr_id => p_trx_hdr_id,
1864 p_trx_temp_id => p_temp_id);
1865
1866 Post_Process(p_action_code => p_dcp_event,
1867 p_raise_exception => p_raise_exception);
1868
1869 x_return_status :='S';
1870 EXCEPTION
1871 WHEN dcp_caught THEN
1872 IF l_debug_on THEN
1873 INV_LOG_UTIL.TRACE('dcp_caught exception: Validate_data',l_module_name,9);
1874 INV_LOG_UTIL.TRACE('EXCEPTION:DCP_CAUGHT: Validate_data',l_module_name,9);
1875 END IF;
1876 x_return_status :='S';
1877 RAISE data_inconsistency_exception;
1878 WHEN others THEN
1879 IF l_debug_on THEN
1880 INV_LOG_UTIL.TRACE('Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,180),l_module_name,9);
1881 INV_LOG_UTIL.TRACE('EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR',l_module_name,9);
1882 x_return_status :='E';
1883 END IF;
1884 END Validate_data;
1885 END INV_DCP_PVT;
[Home] [Help]