[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_VALIDATE_PROC
Source
1 PACKAGE BODY INV_MGD_MVT_VALIDATE_PROC AS
2 -- $Header: INVVALCB.pls 120.1.12010000.2 2009/10/20 10:25:02 abhissri ship $
3
4 --+=======================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+=======================================================================+
9 --| FILENAME |
10 --| INVVALCB.pls |
11 --| |
12 --| DESCRIPTION |
13 --| Body of INV_MGD_MVT_VALIDATE_PROC |
14 --| |
15 --| PROCEDURE LIST |
16 --| Validate_Transaction |
17 --| |
18 --| REFERENCED PROCEDURES |
19 --| INV_MGD_MVT_STATS_PVT.Get_Open_Mvmt_Stats_Txns |
20 --| INV_MGD_MVT_SETUP_MDTR.Get_Movement_Stat_Usages |
21 --| INV_MGD_MVT_STATS_PVT.Validate_Movement_Statistics |
22 --| INV_MGD_MVT_STATS_PVT.Update_Mtl_Movement_Statistics |
23 --| INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info |
24 --| INV_MGD_MVT_UTILS_PKG.Log |
25 --| INV_MGD_MVT_RPT_GEN.Print_Header |
26 --| INV_MGD_MVT_RPT_GEN.Print_Footer |
27 --| INV_MGD_MVT_RPT_GEN.Print_Body |
28 --| |
29 --| HISTORY |
30 --| 05/25/2000 ksaini Created Validate_Transaction Wrapper API |
31 --| For Exception Verification Report |
32 --| 07/14/2003 tsimmond added procedure Populate_temp_table for |
33 --| new design of the Exception Report |
34 --+======================================================================*/
35
36 --===================
37 -- GLOBALS
38 --===================
39
40 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_VALIDATE_PROC';
41 g_final_excp_list INV_MGD_MVT_DATA_STR.excp_list ;
42 G_rpt_page_col CONSTANT INTEGER := 78 ;
43 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_VALIDATE_PROC.';
44 --========================================================================
45 --PROCEDURE : Populate_temp_table PRIVATE
46 --
47 --PARAMETERS: p_excp_list IN
48 -- p_mtl_movement_transaction IN
49 --
50 -- COMMENT : Procedure populates temp table INV_MVT_EXCEP_REP_TEMP with
51 -- data, that is printed in Exception Report
52 --=======================================================================
53 PROCEDURE Populate_temp_table
54 ( p_excp_list IN INV_MGD_MVT_DATA_STR.EXCP_LIST
55 , p_mtl_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
56 )
57 IS
58 l_num_of_exceptions NUMBER;
59 l_item_code VARCHAR2(40);
60 l_count_i NUMBER;
61 l_error_name VARCHAR2(100);
62 l_error_type VARCHAR2(25);
63 l_count NUMBER;
64 l_excp_col_name VARCHAR2(100);
65 l_f_currency_code VARCHAR2(15);
66 l_tp_name VARCHAR2(360);
67 l_tp_type VARCHAR2(80);
68 l_procedure_name CONSTANT VARCHAR2(30) := 'Populate_Temp_Table';
69 BEGIN
70 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
71 THEN
72 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
73 , G_MODULE_NAME || l_procedure_name || '.begin'
74 ,'enter procedure'
75 );
76 END IF;
77
78 l_num_of_exceptions := p_excp_list.COUNT;
79
80 ---dbms_output.put_line('Number of exceptions: '|| l_num_of_exceptions);
81
82 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
83 THEN
84 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
85 , G_MODULE_NAME || l_procedure_name
86 , 'Number of exceptions: '|| l_num_of_exceptions
87 );
88 END IF;
89
90 l_count_i := 1;
91 WHILE l_count_i <= l_num_of_exceptions
92 LOOP
93 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
94 THEN
95 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
96 , G_MODULE_NAME || l_procedure_name
97 , 'Loop : '|| l_count_i
98 );
99 END IF;
100
101 ---------------Missing Invoice------------------------
102 IF p_excp_list(l_count_i).excp_message_cd=1
103 AND p_excp_list(l_count_i).excp_col_name='INVOICE_ID'
104 THEN
105 -- FND_MESSAGE.set_name('INV','INV_MGD_MVT_MIS_INV');
106
107 --FND_MESSAGE.set_token('EXCP_MISSING_COL'
108 -- , p_excp_list(l_count_i).excp_col_name
109 -- );
110
111 l_error_name:='INV_MGD_MVT_MIS_INV';
112 l_error_type:='Warning';
113
114 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
115 THEN
116 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
117 , G_MODULE_NAME || l_procedure_name
118 ,'Insert record into temp table'
119 );
120 END IF;
121
122 INSERT INTO inv_mvt_excep_rep_temp
123 ( error_type
124 , error_name
125 , inventory_item_id
126 , item_code
127 , movement_id
128 , from_currency_code
129 , to_currency_code
130 , exchange_type
131 , from_uom
132 , to_uom
133 , tp_name
134 , tp_type
135 , column_name
136 , number_of_records
137 )
138 VALUES
139 ( l_error_type
140 , l_error_name
141 , NULL
142 , NULL
143 , p_mtl_movement_transaction.movement_id
144 , NULL ----from_currency_code
145 , NULL ----to_currency_code
146 , NULL ----p_mtl_movement_transaction.exchange_type
147 , NULL ----from_uom
148 , NULL ----to_uom
149 , NULL ----tp_name
150 , NULL ----tp_type
151 , p_excp_list(l_count_i).excp_col_name
152 , NULL
153 );
154
155 ------------Missing Commodity_code --------
156 ELSIF p_excp_list(l_count_i).excp_message_cd=1
157 AND p_excp_list(l_count_i).excp_col_name='COMMODITY_CODE'
158 THEN
159 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
160 THEN
161 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
162 , G_MODULE_NAME || l_procedure_name
163 , 'Missing Commodity_code'
164 );
165 END IF;
166
167 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
168 THEN
169 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
170 , G_MODULE_NAME || l_procedure_name
171 , 'error_name='||l_error_name
172 );
173 END IF;
174
175 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
176 THEN
177 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
178 , G_MODULE_NAME || l_procedure_name
179 , 'column_name='||p_excp_list(l_count_i).excp_col_name
180 );
181 END IF;
182
183 l_error_name:='INV_MGD_MVT_MIS_COMC';
184 l_error_type:='Error';
185
186 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
187 THEN
188 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
189 , G_MODULE_NAME || l_procedure_name
190 , 'error_name='||l_error_name
191 );
192 END IF;
193
194 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
195 THEN
196 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
197 , G_MODULE_NAME || l_procedure_name
198 , 'Get item code for item_id='||TO_CHAR(p_mtl_movement_transaction.inventory_item_id)
199 );
200 END IF;
201
202 BEGIN
203
204 /* Bugfix 9003740: item_number should be used in place of segment1
205 SELECT segment1
206 INTO l_item_code
207 FROM mtl_item_flexfields
208 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
209 AND organization_id=p_mtl_movement_transaction.organization_id;
210 */
211
212 SELECT item_number
213 INTO l_item_code
214 FROM mtl_item_flexfields
215 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
216 AND organization_id=p_mtl_movement_transaction.organization_id;
217
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND
221 THEN
222 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
223 THEN
224 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
225 , G_MODULE_NAME || l_procedure_name
226 , 'There is no data in item_number in mtl_item_flexfields for
227 item_id='||TO_CHAR(p_mtl_movement_transaction.inventory_item_id)
228 );
229 END IF;
230
231 ------Exception Missing Item ---------------------
232 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
233 THEN
234 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
235 , G_MODULE_NAME || l_procedure_name
236 , 'Missing Item'
237 );
238 END IF;
239
240 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
241 THEN
242 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
243 , G_MODULE_NAME || l_procedure_name
244 , 'Insert record into temp table'
245 );
246 END IF;
247
248 INSERT INTO inv_mvt_excep_rep_temp
249 ( error_type
250 , error_name
251 , inventory_item_id
252 , item_code
253 , movement_id
254 , from_currency_code
255 , to_currency_code
256 , exchange_type
257 , from_uom
258 , to_uom
259 , tp_name
260 , tp_type
261 , column_name
262 , number_of_records
263 )
264 VALUES
265 ( 'Error'
266 , 'INV_MGD_MVT_MIS_ITEM'
267 , NULL
268 , NULL
269 , p_mtl_movement_transaction.movement_id
270 , NULL ----p_mtl_movement_transaction.currency_code
271 , NULL
272 , NULL ----p_mtl_movement_transaction.currency_conversion_rate
273 , NULL ----from_uom
274 , NULL ----to_uom
275 , NULL ----tp_name
276 , NULL ----tp_type
277 , NULL
278 , NULL
279 );
280
281 END;
282
283 ------Checking if information about this item and this exception
284 ------already exists in temp table
285
286 SELECT COUNT(*)
287 INTO l_count
288 FROM inv_mvt_excep_rep_temp
289 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
290 AND error_name=l_error_name
291 AND column_name='COMMODITY_CODE';
292
293 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
294 THEN
295 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
296 , G_MODULE_NAME || l_procedure_name
297 , 'l_count='||TO_CHAR(l_count)
298 );
299 END IF;
300
301 IF l_count>0
302 THEN
303 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
304 THEN
305 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
306 , G_MODULE_NAME || l_procedure_name
307 , 'Update temp table'
308 );
309 END IF;
310
311 ------update mode
312 UPDATE inv_mvt_excep_rep_temp
313 SET number_of_records=number_of_records+1
314 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
315 AND error_name=l_error_name
316 AND column_name='COMMODITY_CODE';
317
318 ELSE
319 ------insert mode
320
321 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
322 THEN
323 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
324 , G_MODULE_NAME || l_procedure_name
325 , 'Insert record into temp table:'
326 );
327 END IF;
328
329 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
330 THEN
331 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
332 , G_MODULE_NAME || l_procedure_name
333 , 'error_type='||l_error_type
334 );
335 END IF;
336
337 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
338 THEN
339 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
340 , G_MODULE_NAME || l_procedure_name
341 , 'error_name='||l_error_name
342 );
343 END IF;
344
345 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
346 THEN
347 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
348 , G_MODULE_NAME || l_procedure_name
349 , 'column_name='||p_excp_list(l_count_i).excp_col_name||
350 'item_id='||p_mtl_movement_transaction.inventory_item_id
351 );
352 END IF;
353
354 INSERT INTO inv_mvt_excep_rep_temp
355 ( error_type
356 , error_name
357 , inventory_item_id
358 , item_code
359 , movement_id
360 , from_currency_code
361 , to_currency_code
362 , exchange_type
363 , from_uom
364 , to_uom
365 , tp_name
366 , tp_type
367 , column_name
368 , number_of_records
369 )
370 VALUES
371 ( l_error_type
372 , l_error_name
373 , p_mtl_movement_transaction.inventory_item_id
374 , l_item_code
375 , NULL
376 , NULL
377 , NULL
378 , NULL
379 , NULL ----from_uom
380 , NULL ----to_uom
381 , NULL ----tp_name
382 , NULL ----tp_type
383 , p_excp_list(l_count_i).excp_col_name
384 , 1
385 );
386
387 END IF;
388
389 ------------Missing Unit_weight--------
390 ELSIF p_excp_list(l_count_i).excp_message_cd=1
391 AND p_excp_list(l_count_i).excp_col_name='UNIT_WEIGHT'
392 THEN
393 --FND_MESSAGE.set_name('INV','INV_MGD_MVT_MIS_UNW');
394
395 -- FND_MESSAGE.set_token('EXCP_MISSING_COL'
396 -- , p_excp_list(l_count_i).excp_col_name
397 -- );
398
399 l_error_name:='INV_MGD_MVT_MIS_UNW';
400 l_error_type:='Error';
401
402 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
403 THEN
404 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
405 , G_MODULE_NAME || l_procedure_name
406 , 'Get item code for item_id='||TO_CHAR(p_mtl_movement_transaction.inventory_item_id)
407 );
408 END IF;
409
410 BEGIN
411
412 /* Bugfix 9003740: item_number should be used in place of segment1
413 SELECT segment1
414 INTO l_item_code
415 FROM mtl_item_flexfields
416 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
417 AND organization_id=p_mtl_movement_transaction.organization_id;
418 */
419
420 SELECT item_number
421 INTO l_item_code
422 FROM mtl_item_flexfields
423 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
424 AND organization_id=p_mtl_movement_transaction.organization_id;
425
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND
429 THEN
430 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
431 THEN
432 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
433 , G_MODULE_NAME || l_procedure_name
434 , 'There is no data in item_number in mtl_item_flexfields for
435 item_id='||TO_CHAR(p_mtl_movement_transaction.inventory_item_id)
436 );
437 END IF;
438
439 ------Exception Missing Item ---------------------
440 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
441 THEN
442 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
443 , G_MODULE_NAME || l_procedure_name
444 , 'Missing Item'
445 );
446 END IF;
447
448 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
449 THEN
450 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
451 , G_MODULE_NAME || l_procedure_name
452 , 'Insert record into temp table:'
453 );
454 END IF;
455
456 INSERT INTO inv_mvt_excep_rep_temp
457 ( error_type
458 , error_name
459 , inventory_item_id
460 , item_code
461 , movement_id
462 , from_currency_code
463 , to_currency_code
464 , exchange_type
465 , from_uom
466 , to_uom
467 , tp_name
468 , tp_type
469 , column_name
470 , number_of_records
471 )
472 VALUES
473 ( 'Error'
474 , 'INV_MGD_MVT_MIS_ITEM'
475 , NULL
476 , NULL
477 , p_mtl_movement_transaction.movement_id
478 , NULL ----p_mtl_movement_transaction.currency_code
479 , NULL
480 , NULL ----p_mtl_movement_transaction.currency_conversion_rate
481 , NULL ----from_uom
482 , NULL ----to_uom
483 , NULL ----tp_name
484 , NULL ----tp_type
485 , NULL
486 , NULL
487 );
488
489 END;
490
491 ------Checking if information about this item and this exception
492 ------already exists in temp table
493
494 SELECT COUNT(*)
495 INTO l_count
496 FROM inv_mvt_excep_rep_temp
497 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
498 AND error_name=l_error_name;
499
500 IF l_count>0
501 THEN
502 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
503 THEN
504 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
505 , G_MODULE_NAME || l_procedure_name
506 , 'Update temp table:'
507 );
508 END IF;
509
510 ------update mode
511 UPDATE inv_mvt_excep_rep_temp
512 SET number_of_records=number_of_records+1
513 WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
514 AND error_name=l_error_name;
515
516 ELSE
517 ------insert mode
518 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
519 THEN
520 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
521 , G_MODULE_NAME || l_procedure_name
522 , 'Insert record into temp table:'
523 );
524 END IF;
525
526 INSERT INTO inv_mvt_excep_rep_temp
527 ( error_type
528 , error_name
529 , inventory_item_id
530 , item_code
531 , movement_id
532 , from_currency_code
533 , to_currency_code
534 , exchange_type
535 , from_uom
536 , to_uom
537 , tp_name
538 , tp_type
539 , column_name
540 , number_of_records
541 )
542 VALUES
543 ( l_error_type
544 , l_error_name
545 , p_mtl_movement_transaction.inventory_item_id
546 , l_item_code
547 , NULL
548 , NULL
549 , NULL
550 , NULL
551 , NULL ----from_uom
552 , NULL ----to_uom
553 , NULL ----tp_name
554 , NULL ----tp_type
555 , p_excp_list(l_count_i).excp_col_name
556 , 1
557 );
558
559 END IF;
560
561 -------------Missing Exchange Rate-----------------------
562 ELSIF p_excp_list(l_count_i).excp_message_cd=1
563 AND p_excp_list(l_count_i).excp_col_name='CURRENCY_CONVERSION_RATE'
564
565 THEN
566
567 --FND_MESSAGE.set_name('INV','INV_MGD_MVT_MIS_EXR');
568
569 --FND_MESSAGE.set_token('EXCP_MISSING_COL'
570 -- , p_excp_list(l_count_i).excp_col_name
571 -- );
572
573 l_error_name:='INV_MGD_MVT_MIS_EXR';
574 l_error_type:='Error';
575
576 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
577 THEN
578 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
579 , G_MODULE_NAME || l_procedure_name
580 , 'Missing Exchange Rate,l_error_name= '||l_error_name
581 );
582 END IF;
583
584 ----get functional currency
585 l_f_currency_code := INV_MGD_MVT_UTILS_PKG.Get_LE_Currency
586 (p_mtl_movement_transaction.entity_org_id);
587
588 ------Checking if information about this currency and this exception
589 ------already exists in temp table
590 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
591 THEN
592 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
593 , G_MODULE_NAME || l_procedure_name
594 , 'Checking if this currency already exists in temp table'
595 );
596 END IF;
597
598 SELECT COUNT(*)
599 INTO l_count
600 FROM inv_mvt_excep_rep_temp
601 WHERE from_currency_code=p_mtl_movement_transaction.currency_code
602 AND error_name=l_error_name;
603
604 IF l_count>0
605 THEN
606 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
607 THEN
608 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
609 , G_MODULE_NAME || l_procedure_name
610 , 'Update temp table'
611 );
612 END IF;
613
614 ------update mode
615 UPDATE inv_mvt_excep_rep_temp
616 SET number_of_records=number_of_records+1
617 WHERE from_currency_code=p_mtl_movement_transaction.currency_code
618 AND error_name=l_error_name;
619
620 ELSE
621 ------insert mode
622 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
623 THEN
624 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
625 , G_MODULE_NAME || l_procedure_name
626 , 'Insert record into temp table:'
627 );
628 END IF;
629
630 INSERT INTO inv_mvt_excep_rep_temp
631 ( error_type
632 , error_name
633 , inventory_item_id
634 , item_code
635 , movement_id
636 , from_currency_code
637 , to_currency_code
638 , exchange_type
639 , from_uom
640 , to_uom
641 , tp_name
642 , tp_type
643 , column_name
644 , number_of_records
645 )
646 VALUES
647 ( l_error_type
648 , l_error_name
649 , NULL
650 , NULL
651 , NULL
652 , p_mtl_movement_transaction.currency_code
653 , l_f_currency_code
654 , p_mtl_movement_transaction.currency_conversion_rate
655 , NULL ----from_uom
656 , NULL ----to_uom
657 , NULL ----tp_name
658 , NULL ----tp_type
659 , p_excp_list(l_count_i).excp_col_name
660 , 1
661 );
662
663 END IF;
664
665 ------------Missing VAT NUMBER --------
666 ELSIF p_excp_list(l_count_i).excp_message_cd=1
667 AND p_excp_list(l_count_i).excp_col_name='PARTNER_VAT_NUMBER'
668 THEN
669
670 l_error_name:='INV_MGD_MVT_MIS_TP_VAT';
671 l_error_type:='Error';
672
673 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
674 THEN
675 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
676 , G_MODULE_NAME || l_procedure_name
677 , 'Get Parner name and Partner Type'
678 );
679 END IF;
680
681 -----for SO
682 IF p_mtl_movement_transaction.document_source_type IN ('SO','IO','RMA')
683 AND p_mtl_movement_transaction.bill_to_site_use_id IS NOT NULL
684 THEN
685 l_tp_name:=p_mtl_movement_transaction.CUSTOMER_NAME;
686
687 FND_MESSAGE.set_name('INV','INV_MGD_MVT_EXCP_TP_CUST');
688 l_tp_type:=FND_MESSAGE.GET;
689
690
691 ELSIF p_mtl_movement_transaction.document_source_type in ('PO','RTV')
692 AND p_mtl_movement_transaction.vendor_site_id IS NOT NULL
693 THEN
694 l_tp_name:=p_mtl_movement_transaction.VENDOR_NAME;
695
696 FND_MESSAGE.set_name('INV','INV_MGD_MVT_EXCP_TP_SUP');
697 l_tp_type:=FND_MESSAGE.GET;
698
699 ELSIF p_mtl_movement_transaction.document_source_type = 'INV'
700 THEN
701 FND_MESSAGE.set_name('INV','INV_MGD_MVT_EXCP_TP_ORG');
702 l_tp_type:=FND_MESSAGE.GET;
703
704 BEGIN
705
706 SELECT name
707 INTO l_tp_name
708 FROM hr_all_organization_units
709 WHERE organization_id=p_mtl_movement_transaction.entity_org_id;
710 EXCEPTION
711 WHEN OTHERS
712 THEN
713 null;
714 END;
715 END IF;
716
717 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
718 THEN
719 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
720 , G_MODULE_NAME || l_procedure_name
721 , ' Parner name='||l_tp_name||
722 ' Partner Type='||l_tp_type
723 );
724 END IF;
725
726 ------Checking if information about this customer or supplier and this exception
727 ------already exists in temp table
728
729 SELECT COUNT(*)
730 INTO l_count
731 FROM inv_mvt_excep_rep_temp
732 WHERE tp_name=l_tp_name
733 AND tp_type=l_tp_type
734 AND column_name='PARTNER_VAT_NUMBER'
735 AND error_name=l_error_name;
736
737 IF l_count>0
738 THEN
739 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
740 THEN
741 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
742 , G_MODULE_NAME || l_procedure_name
743 , 'Update temp table:'
744 );
745 END IF;
746
747 ------update mode
748 UPDATE inv_mvt_excep_rep_temp
749 SET number_of_records=number_of_records+1
750 WHERE tp_name=l_tp_name
751 AND tp_type=l_tp_type
752 AND column_name='PARTNER_VAT_NUMBER'
753 AND error_name=l_error_name;
754
755 ELSE
756 ------insert mode
757 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
758 THEN
759 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
760 , G_MODULE_NAME || l_procedure_name
761 , 'Insert record into temp table, error_name='||l_error_name
762 );
763 END IF;
764
765 INSERT INTO inv_mvt_excep_rep_temp
766 ( error_type
767 , error_name
768 , inventory_item_id
769 , item_code
770 , movement_id
771 , from_currency_code
772 , to_currency_code
773 , exchange_type
774 , from_uom
775 , to_uom
776 , tp_name
777 , tp_type
778 , column_name
779 , number_of_records
780 )
781 VALUES
782 ( l_error_type
783 , l_error_name
784 , NULL
785 , NULL
786 , NULL
787 , NULL
788 , NULL
789 , NULL
790 , NULL ----from_uom
791 , NULL ----to_uom
792 , l_tp_name
793 , l_tp_type
794 , p_excp_list(l_count_i).excp_col_name
795 , 1
796 );
797
798 END IF;
799
800 -------------Incorrect Value------------------------------
801 ELSIF p_excp_list(l_count_i).excp_message_cd=2
802 THEN
803 --FND_MESSAGE.set_name('INV','INV_MGD_MVT_INVAL_VAL');
804
805 --FND_MESSAGE.set_token('EXCP_INCORRECT_COL'
806 -- , p_excp_list(l_count_i).excp_col_name
807 -- );
808
809 l_error_name:='INV_MGD_MVT_INVAL_VAL';
810 l_error_type:='Error';
811
812 ------get the name as it appears in the form instead of column_name
813 IF p_excp_list(l_count_i).excp_col_name='DELIVERY_TERMS'
814 THEN
815 FND_MESSAGE.set_name('INV'
816 ,'INV_MGD_MVT_EXCP_DT'
817 );
818 l_excp_col_name:=FND_MESSAGE.GET;
819
820 ELSIF p_excp_list(l_count_i).excp_col_name='TRANSACTION_NATURE'
821 THEN
822 FND_MESSAGE.set_name('INV'
823 ,'INV_MGD_MVT_EXCP_TN'
824 );
825 l_excp_col_name:=FND_MESSAGE.GET;
826
827 ELSIF p_excp_list(l_count_i).excp_col_name='TRANSPORT_MODE'
828 THEN
829 FND_MESSAGE.set_name('INV'
830 ,'INV_MGD_MVT_EXCP_TM'
831 );
832 l_excp_col_name:=FND_MESSAGE.GET;
833
834 ELSIF p_excp_list(l_count_i).excp_col_name='PORT'
835 THEN
836 FND_MESSAGE.set_name('INV'
837 ,'INV_MGD_MVT_EXCP_P'
838 );
839 l_excp_col_name:=FND_MESSAGE.GET;
840
841 ELSIF p_excp_list(l_count_i).excp_col_name='STATISTICAL_PROCEDURE_CODE'
842 THEN
843 FND_MESSAGE.set_name('INV'
844 ,'INV_MGD_MVT_EXCP_SPC'
845 );
846 l_excp_col_name:=FND_MESSAGE.GET;
847
848 ELSIF p_excp_list(l_count_i).excp_col_name='AREA'
849 THEN
850 FND_MESSAGE.set_name('INV'
851 ,'INV_MGD_MVT_EXCP_A'
852 );
853 l_excp_col_name:=FND_MESSAGE.GET;
854
855 ELSIF p_excp_list(l_count_i).excp_col_name='OUTSIDE_CODE'
856 THEN
857 FND_MESSAGE.set_name('INV'
858 ,'INV_MGD_MVT_EXCP_PC'
859 );
860 l_excp_col_name:=FND_MESSAGE.GET;
861
862 ELSIF p_excp_list(l_count_i).excp_col_name='OUTSIDE_UNIT_PRICE'
863 THEN
864 FND_MESSAGE.set_name('INV'
865 ,'INV_MGD_MVT_EXCP_OUP'
866 );
867 l_excp_col_name:=FND_MESSAGE.GET;
868
869 ELSIF p_excp_list(l_count_i).excp_col_name='TRIANGULATION_COUNTRY_CODE'
870 THEN
871 FND_MESSAGE.set_name('INV'
872 ,'INV_MGD_MVT_EXCP_TCC'
873 );
874 l_excp_col_name:=FND_MESSAGE.GET;
875
876 ELSIF p_excp_list(l_count_i).excp_col_name='OIL_REFERENCE_CODE'
877 THEN
878 FND_MESSAGE.set_name('INV'
879 ,'INV_MGD_MVT_EXCP_ORC'
880 );
881 l_excp_col_name:=FND_MESSAGE.GET;
882
883 ELSIF p_excp_list(l_count_i).excp_col_name='CONTAINER_TYPE_CODE'
884 THEN
885 FND_MESSAGE.set_name('INV'
886 ,'INV_MGD_MVT_EXCP_CTC'
887 );
888 l_excp_col_name:=FND_MESSAGE.GET;
889
890 ELSIF p_excp_list(l_count_i).excp_col_name='FLOW_INDICATOR_CODE'
891 THEN
892 FND_MESSAGE.set_name('INV'
893 ,'INV_MGD_MVT_EXCP_FIC'
894 );
895 l_excp_col_name:=FND_MESSAGE.GET;
896
897 ELSIF p_excp_list(l_count_i).excp_col_name='AFFILIATION_REFERENCE_CODE'
898 THEN
899 FND_MESSAGE.set_name('INV'
900 ,'INV_MGD_MVT_EXCP_ARC'
901 );
902 l_excp_col_name:=FND_MESSAGE.GET;
903
904 ELSIF p_excp_list(l_count_i).excp_col_name='OUTSIDE_EXT_VALUE'
905 THEN
906 FND_MESSAGE.set_name('INV'
907 ,'INV_MGD_MVT_EXCP_OEV'
908 );
909 l_excp_col_name:=FND_MESSAGE.GET;
910
911
912 ELSE
913 l_excp_col_name:=p_excp_list(l_count_i).excp_col_name;
914 END IF;
915
916 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
917 THEN
918 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
919 , G_MODULE_NAME || l_procedure_name
920 , 'Insert record into temp table:'
921 );
922 END IF;
923
924 INSERT INTO inv_mvt_excep_rep_temp
925 ( error_type
926 , error_name
927 , inventory_item_id
928 , item_code
929 , movement_id
930 , from_currency_code
931 , to_currency_code
932 , exchange_type
933 , from_uom
934 , to_uom
935 , tp_name
936 , tp_type
937 , column_name
938 , number_of_records
939 )
940 VALUES
941 ( l_error_type
942 , l_error_name
943 , NULL
944 , NULL
945 , p_mtl_movement_transaction.movement_id
946 , NULL ----p_mtl_movement_transaction.currency_code
947 , NULL
948 , NULL ----p_mtl_movement_transaction.currency_conversion_rate
949 , NULL ----from_uom
950 , NULL ----to_uom
951 , NULL ----tp_name
952 , NULL ----tp_type
953 , l_excp_col_name
954 , NULL
955 );
956
957
958 END IF;
959 l_count_i := l_count_i + 1;
960 END LOOP;
961
962 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
963 THEN
964 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
965 , G_MODULE_NAME || l_procedure_name || '.end'
966 ,'exit procedure'
967 );
968 END IF;
969
970 EXCEPTION
971
972 WHEN OTHERS THEN
973
974 IF
975 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
976 THEN
977 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
978 , 'INV_MGD_MVT_Populate_temp_table '
979 );
980 END IF;
981
982
983 END Populate_temp_table;
984
985
986 --========================================================================
987 --PROCEDURE : Validate_Transaction PUBLIC
988 --
989 --PARAMETERS: p_api_version_number IN Known api version
990 -- p_init_msg_list IN FND_API.G_FALSE to preserve list
991 -- p_legal_entity_id IN Legal Entity Id
992 -- p_economic_zone_code IN Economic Zone Code
993 -- p_usage_type IN Usage type
994 -- p_stat_type IN Stat Type
995 -- p_period_name IN Period name
996 -- p_document_source_type IN Document Source Type
997 -- x_return_status OUT return status
998 --
999 --
1000 -- VERSION : current version 1.0
1001 -- initial_version 1.0
1002 -- COMMENT : Wrapper API to call Validate_Movement_Statistics
1003 --=======================================================================
1004
1005 PROCEDURE Validate_Transaction (
1006 p_api_version_number IN NUMBER
1007 , p_init_msg_list IN VARCHAR2
1008 , p_legal_entity_id IN NUMBER
1009 , p_economic_zone_code IN VARCHAR2
1010 , p_usage_type IN VARCHAR2
1011 , p_stat_type IN VARCHAR2
1012 , p_period_name IN VARCHAR2
1013 , p_document_source_type IN VARCHAR2
1014 , x_return_status OUT NOCOPY VARCHAR2
1015 , x_msg_count OUT NOCOPY NUMBER
1016 , x_msg_data OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019
1020 -- Cursor For Fetching Movement Statistics Records
1021 val_crsr INV_MGD_MVT_DATA_STR.valCurTyp;
1022 l_excp_list INV_MGD_MVT_DATA_STR.excp_list;
1023 l_record_status VARCHAR2(1);
1024 l_return_status VARCHAR2(1);
1025 x_updated_flag VARCHAR2(1);
1026 l_msg_count NUMBER;
1027 l_msg_data VARCHAR2(100);
1028 l_mtl_movement_statistics
1029 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1030 l_init_movement_statistics
1031 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1032 l_ret_movement_statistics
1033 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1034 l_movement_stat_usages_rec
1035 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
1036
1037 l_procedure_name CONSTANT VARCHAR2(30) := 'Validate_Transaction';
1038
1039 l_api_version_number NUMBER;
1040 l_init_msg_list VARCHAR2(30);
1041 BEGIN
1042 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1043 THEN
1044 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1045 , G_MODULE_NAME || l_procedure_name || '.begin'
1046 ,'enter procedure'
1047 );
1048 END IF;
1049
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 IF p_api_version_number IS NULL
1053 THEN
1054 l_api_version_number := 1;
1055 END IF;
1056
1057 IF p_init_msg_list IS NULL
1058 THEN
1059 l_init_msg_list := FND_API.G_FALSE;
1060 END IF;
1061
1062 --INV_MGD_MVT_UTILS_PKG.Log_Initialize;
1063
1064 -- Initialize the Message Stack
1065 FND_MSG_PUB.Initialize;
1066
1067 l_excp_list.DELETE;
1068 l_mtl_movement_statistics := l_init_movement_statistics ;
1069 l_ret_movement_statistics := l_init_movement_statistics ;
1070
1071
1072 INV_MGD_MVT_SETUP_MDTR.Get_Movement_Stat_Usages
1073 ( x_return_status => x_return_status
1074 , x_msg_count => x_msg_count
1075 , x_msg_data => x_msg_data
1076 , p_legal_entity_id => p_legal_entity_id
1077 , p_economic_zone_code => p_economic_zone_code
1078 , p_usage_type => p_usage_type
1079 , p_stat_type => p_stat_type
1080 , x_movement_stat_usages_rec => l_movement_stat_usages_rec
1081 );
1082
1083 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1084 THEN
1085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1086 END IF;
1087
1088 l_movement_stat_usages_rec.gl_period_name := p_period_name;
1089
1090 /* -- Print Report Header
1091 INV_MGD_MVT_RPT_GEN.Print_Header( p_legal_entity_id => p_legal_entity_id
1092 , p_period_name => p_period_name
1093 , p_document_source_type => p_document_source_type
1094 );
1095 */ ---changed for FPJ
1096
1097
1098 -- Open Cursor
1099 INV_MGD_MVT_STATS_PVT.Get_Open_Mvmt_Stats_Txns(
1100 val_crsr => val_crsr
1101 , p_movement_statistics => l_mtl_movement_statistics
1102 , p_legal_entity_id => p_legal_entity_id
1103 , p_economic_zone_code => p_economic_zone_code
1104 , p_usage_type => p_usage_type
1105 , p_stat_type => p_stat_type
1106 , p_period_name => p_period_name
1107 , p_document_source_type => p_document_source_type
1108 , x_return_status => x_return_status );
1109
1110 IF x_return_status = 'Y' THEN
1111
1112 --- Fetch the Cursor into the Record Type
1113 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1114 THEN
1115 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1116 , G_MODULE_NAME || l_procedure_name
1117 , 'LOOP on val_cur cursor for validate transaction'
1118 );
1119 END IF;
1120
1121 LOOP
1122
1123 FETCH val_crsr INTO
1124 l_mtl_movement_statistics.movement_id
1125 , l_mtl_movement_statistics.organization_id
1126 , l_mtl_movement_statistics.entity_org_id
1127 , l_mtl_movement_statistics.movement_type
1128 , l_mtl_movement_statistics.movement_status
1129 , l_mtl_movement_statistics.transaction_date
1130 , l_mtl_movement_statistics.last_update_date
1131 , l_mtl_movement_statistics.last_updated_by
1132 , l_mtl_movement_statistics.creation_date
1133 , l_mtl_movement_statistics.created_by
1134 , l_mtl_movement_statistics.last_update_login
1135 , l_mtl_movement_statistics.document_source_type
1136 , l_mtl_movement_statistics.creation_method
1137 , l_mtl_movement_statistics.document_reference
1138 , l_mtl_movement_statistics.document_line_reference
1139 , l_mtl_movement_statistics.document_unit_price
1140 , l_mtl_movement_statistics.document_line_ext_value
1141 , l_mtl_movement_statistics.receipt_reference
1142 , l_mtl_movement_statistics.shipment_reference
1143 , l_mtl_movement_statistics.shipment_line_reference
1144 , l_mtl_movement_statistics.pick_slip_reference
1145 , l_mtl_movement_statistics.customer_name
1146 , l_mtl_movement_statistics.customer_number
1147 , l_mtl_movement_statistics.customer_location
1148 , l_mtl_movement_statistics.transacting_from_org
1149 , l_mtl_movement_statistics.transacting_to_org
1150 , l_mtl_movement_statistics.vendor_name
1151 , l_mtl_movement_statistics.vendor_number
1152 , l_mtl_movement_statistics.vendor_site
1153 , l_mtl_movement_statistics.bill_to_name
1154 , l_mtl_movement_statistics.bill_to_number
1155 , l_mtl_movement_statistics.bill_to_site
1156 , l_mtl_movement_statistics.po_header_id
1157 , l_mtl_movement_statistics.po_line_id
1158 , l_mtl_movement_statistics.po_line_location_id
1159 , l_mtl_movement_statistics.order_header_id
1160 , l_mtl_movement_statistics.order_line_id
1161 , l_mtl_movement_statistics.picking_line_id
1162 , l_mtl_movement_statistics.shipment_header_id
1163 , l_mtl_movement_statistics.shipment_line_id
1164 , l_mtl_movement_statistics.ship_to_customer_id
1165 , l_mtl_movement_statistics.ship_to_site_use_id
1166 , l_mtl_movement_statistics.bill_to_customer_id
1167 , l_mtl_movement_statistics.bill_to_site_use_id
1168 , l_mtl_movement_statistics.vendor_id
1169 , l_mtl_movement_statistics.vendor_site_id
1170 , l_mtl_movement_statistics.from_organization_id
1171 , l_mtl_movement_statistics.to_organization_id
1172 , l_mtl_movement_statistics.parent_movement_id
1173 , l_mtl_movement_statistics.inventory_item_id
1174 , l_mtl_movement_statistics.item_description
1175 , l_mtl_movement_statistics.item_cost
1176 , l_mtl_movement_statistics.transaction_quantity
1177 , l_mtl_movement_statistics.transaction_uom_code
1178 , l_mtl_movement_statistics.primary_quantity
1179 , l_mtl_movement_statistics.invoice_batch_id
1180 , l_mtl_movement_statistics.invoice_id
1181 , l_mtl_movement_statistics.customer_trx_line_id
1182 , l_mtl_movement_statistics.invoice_batch_reference
1183 , l_mtl_movement_statistics.invoice_reference
1184 , l_mtl_movement_statistics.invoice_line_reference
1185 , l_mtl_movement_statistics.invoice_date_reference
1186 , l_mtl_movement_statistics.invoice_quantity
1187 , l_mtl_movement_statistics.invoice_unit_price
1188 , l_mtl_movement_statistics.invoice_line_ext_value
1189 , l_mtl_movement_statistics.outside_code
1190 , l_mtl_movement_statistics.outside_ext_value
1191 , l_mtl_movement_statistics.outside_unit_price
1192 , l_mtl_movement_statistics.currency_code
1193 , l_mtl_movement_statistics.currency_conversion_rate
1194 , l_mtl_movement_statistics.currency_conversion_type
1195 , l_mtl_movement_statistics.currency_conversion_date
1196 , l_mtl_movement_statistics.period_name
1197 , l_mtl_movement_statistics.report_reference
1198 , l_mtl_movement_statistics.report_date
1199 , l_mtl_movement_statistics.category_id
1200 , l_mtl_movement_statistics.weight_method
1201 , l_mtl_movement_statistics.unit_weight
1202 , l_mtl_movement_statistics.total_weight
1203 , l_mtl_movement_statistics.transaction_nature
1204 , l_mtl_movement_statistics.delivery_terms
1205 , l_mtl_movement_statistics.transport_mode
1206 , l_mtl_movement_statistics.alternate_quantity
1207 , l_mtl_movement_statistics.alternate_uom_code
1208 , l_mtl_movement_statistics.dispatch_territory_code
1209 , l_mtl_movement_statistics.destination_territory_code
1210 , l_mtl_movement_statistics.origin_territory_code
1211 , l_mtl_movement_statistics.stat_method
1212 , l_mtl_movement_statistics.stat_adj_percent
1213 , l_mtl_movement_statistics.stat_adj_amount
1214 , l_mtl_movement_statistics.stat_ext_value
1215 , l_mtl_movement_statistics.area
1216 , l_mtl_movement_statistics.port
1217 , l_mtl_movement_statistics.stat_type
1218 , l_mtl_movement_statistics.comments
1219 , l_mtl_movement_statistics.attribute_category
1220 , l_mtl_movement_statistics.commodity_code
1221 , l_mtl_movement_statistics.commodity_description
1222 , l_mtl_movement_statistics.requisition_header_id
1223 , l_mtl_movement_statistics.requisition_line_id
1224 , l_mtl_movement_statistics.picking_line_detail_id
1225 , l_mtl_movement_statistics.usage_type
1226 , l_mtl_movement_statistics.zone_code
1227 , l_mtl_movement_statistics.edi_sent_flag
1228 , l_mtl_movement_statistics.statistical_procedure_code
1229 , l_mtl_movement_statistics.movement_amount
1230 , l_mtl_movement_statistics.triangulation_country_code
1231 , l_mtl_movement_statistics.csa_code
1232 , l_mtl_movement_statistics.oil_reference_code
1233 , l_mtl_movement_statistics.container_type_code
1234 , l_mtl_movement_statistics.flow_indicator_code
1235 , l_mtl_movement_statistics.affiliation_reference_code
1236 , l_mtl_movement_statistics.origin_territory_eu_code
1237 , l_mtl_movement_statistics.destination_territory_eu_code
1238 , l_mtl_movement_statistics.dispatch_territory_eu_code
1239 , l_mtl_movement_statistics.set_of_books_period
1240 , l_mtl_movement_statistics.taric_code
1241 , l_mtl_movement_statistics.preference_code
1242 , l_mtl_movement_statistics.rcv_transaction_id
1243 , l_mtl_movement_statistics.mtl_transaction_id
1244 , l_mtl_movement_statistics.total_weight_uom_code
1245 , l_mtl_movement_statistics.financial_document_flag
1246 , l_mtl_movement_statistics.customer_vat_number
1247 , l_mtl_movement_statistics.attribute1
1248 , l_mtl_movement_statistics.attribute2
1249 , l_mtl_movement_statistics.attribute3
1250 , l_mtl_movement_statistics.attribute4
1251 , l_mtl_movement_statistics.attribute5
1252 , l_mtl_movement_statistics.attribute6
1253 , l_mtl_movement_statistics.attribute7
1254 , l_mtl_movement_statistics.attribute8
1255 , l_mtl_movement_statistics.attribute9
1256 , l_mtl_movement_statistics.attribute10
1257 , l_mtl_movement_statistics.attribute11
1258 , l_mtl_movement_statistics.attribute12
1259 , l_mtl_movement_statistics.attribute13
1260 , l_mtl_movement_statistics.attribute14
1261 , l_mtl_movement_statistics.attribute15
1262 , l_mtl_movement_statistics.triangulation_country_eu_code
1263 , l_mtl_movement_statistics.distribution_line_number
1264 , l_mtl_movement_statistics.ship_to_name
1265 , l_mtl_movement_statistics.ship_to_number
1266 , l_mtl_movement_statistics.ship_to_site
1267 , l_mtl_movement_statistics.edi_transaction_date
1268 , l_mtl_movement_statistics.edi_transaction_reference
1269 , l_mtl_movement_statistics.esl_drop_shipment_code;
1270
1271 EXIT WHEN val_crsr%NOTFOUND;
1272
1273 -- Call the validate_movement_statistics Verification procedure inside
1274 -- LOOP FOR every record Fetched from the CURSOR
1275 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1276 THEN
1277 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1278 , G_MODULE_NAME || l_procedure_name
1279 , 'Processing movement_id '||TO_CHAR(l_mtl_movement_statistics.movement_id)
1280 );
1281 END IF;
1282
1283 --l_excp_list := l_excp_list_empty;
1284 l_excp_list.DELETE;
1285
1286
1287 INV_MGD_MVT_STATS_PVT.Validate_Movement_Statistics
1288 ( p_movement_statistics => l_mtl_movement_statistics
1289 , p_movement_stat_usages_rec => l_movement_stat_usages_rec
1290 , x_return_status => x_return_status
1291 , x_updated_flag => x_updated_flag
1292 , x_msg_count => x_msg_count
1293 , x_msg_data => x_msg_data
1294 , x_excp_list => l_excp_list
1295 , x_movement_statistics => l_ret_movement_statistics
1296 );
1297
1298 IF nvl(l_excp_list.COUNT,0) > 0
1299 THEN
1300 -------changes for FPJ-------------
1301 Populate_temp_table
1302 ( p_excp_list => l_excp_list
1303 , p_mtl_movement_transaction =>l_ret_movement_statistics
1304 );
1305
1306 /* INV_MGD_MVT_RPT_GEN.Print_Body( p_excp_list => l_excp_list
1307 , p_mtl_movement_transaction => l_ret_movement_statistics
1308 );
1309 */
1310
1311 ELSE
1312 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1313 THEN
1314 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1315 , G_MODULE_NAME || l_procedure_name
1316 , 'Record validated with no exception'
1317 );
1318 END IF;
1319 END IF;
1320
1321 l_excp_list.DELETE;
1322
1323 IF x_updated_flag='Y' OR l_ret_movement_statistics.movement_status='V'
1324 THEN
1325 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
1326 ( p_movement_statistics => l_ret_movement_statistics
1327 , x_return_status => x_return_status
1328 , x_msg_count => x_msg_count
1329 , x_msg_data => x_msg_data
1330 );
1331 END IF;
1332
1333 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1334 THEN
1335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1336 END IF;
1337
1338 l_mtl_movement_statistics := l_init_movement_statistics ;
1339
1340 END LOOP;
1341
1342 ELSE
1343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1344 END IF; --IF x_return_status='Y'
1345
1346 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1347 THEN
1348 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1349 , G_MODULE_NAME || l_procedure_name
1350 , 'END LOOP on val_crsr'
1351 );
1352 END IF;
1353
1354 -----INV_MGD_MVT_RPT_GEN.Print_Footer(p_page_width => g_rpt_page_col);
1355 ---- changed for FPJ
1356
1357 x_msg_data := NULL;
1358 x_msg_count := 0;
1359
1360 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1361 THEN
1362 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1363 , G_MODULE_NAME || l_procedure_name || '.end'
1364 ,'exit procedure'
1365 );
1366 END IF;
1367
1368 EXCEPTION
1369
1370 WHEN OTHERS THEN
1371
1372 IF
1373 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1374 THEN
1375 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1376 , 'INV_MGD_MVT_Validate_Txn '
1377 );
1378 END IF;
1379
1380 x_msg_data := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1381 x_msg_count := 2;
1382
1383
1384 END Validate_Transaction;
1385
1386 END INV_MGD_MVT_VALIDATE_PROC;