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