[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_STATS_PVT
Source
1 PACKAGE BODY INV_MGD_MVT_STATS_PVT AS
2 -- $Header: INVVMVTB.pls 120.3.12010000.3 2008/11/03 12:12:34 ajmittal ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVVMVTB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| |
14 --| PROCEDURE LIST |
15 --| Create_Movement_Statistics |
16 --| Init_Movement_Record |
17 --| Get_Open_Mvmt_Stats_Txns |
18 --| Validate_Movement_Statistics |
19 --| Update_Movement_Statistics |
20 --| Delete_Movement_Statistics |
21 --| Validate_Rules |
22 --| Get_Invoice_Transactions |
23 --| Get_Pending_Txns |
24 --| Get_PO_Trans_With_Correction |
25 --| |
26 --| HISTORY |
27 --| 04/17/00 pseshadr Created |
28 --| 06/12/00 ksaini Added procedures |
29 --| 07/18/00 ksaini Added Validate_Rules procedure |
30 --| 09/15/00 ksaini Updated with right message code for |
31 --| incorrect or missing values in Validate_Rules procedure |
32 --| 09/26/00 ksaini Corrected return status of validate_rules|
33 --| 09/29/00 ksaini Corrected Validation rule for Not |
34 --| Required fields and incorrect attribute values|
35 --| 04/01/02 pseshadr Added new procedure Get_Pending_Txns |
36 --| 08/01/03 tsimmond Added code to Validate_rules for missing |
37 --| Trading Partner VAT number exception (FPJ) |
38 --| 09/22/03 tsimmond Corrected code in Validate_record, so that |
39 --| exception will not be reported first if it can|
40 --| be corrected |
41 --| 03/30/04 tsimmond Enhancement request 2757987, changed cursor in |
42 --| Get_Open_Mvmt_Stats_Txns to show only warnings |
43 --| and exceptions for the chosen period |
44 --+========================================================================
45
46 --===================
47 -- GLOBALS
48 --===================
49
50 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_STATS_PVT';
51 g_final_excp_list INV_MGD_MVT_DATA_STR.excp_list ;
52 G_rpt_page_col CONSTANT INTEGER := 78 ;
53 G_format_space CONSTANT INTEGER := 2;
54 g_mvt_count NUMBER;
55 g_movement_id NUMBER;
56 g_parent_movement_id NUMBER;
57 g_too_many_transactions_exc EXCEPTION;
58 g_no_data_transaction_exc EXCEPTION;
59 g_oe_or_om VARCHAR2(30);
60 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_STATS_PVT.';
61
62 --========================================================================
63 -- PROCEDURE : Create_Movement_Statistics PUBLIC
64 -- PARAMETERS: p_api_version_number known api version
65 -- p_init_msg_list FND_API.G_FALSE not to reset list
66 -- p_transaction_type transaction type(inv,rec.,PO etc)
67 -- x_return_status return status
68 -- x_msg_count number of messages in the list
69 -- x_msg_data message text
70 -- p_material_transaction material transaction data record
71 -- p_shipment_transaction shipment transaction data record
72 -- p_receipt_transaction receipt transaction data record
73 -- p_movement_transaction movement transaction data record
74 -- VERSION : current version 1.0
75 -- initial version 1.0
76 -- COMMENT : Called by the Process Transaction after all the
77 -- processing is done to insert the transaction/record
78 -- into the movement statistics table.
79 -- This procedure does the insert into the table.
80 --=======================================================================
81 PROCEDURE Create_Movement_Statistics
82 ( p_api_version_number IN NUMBER
83 , p_init_msg_list IN VARCHAR2
84 , x_movement_transaction IN OUT NOCOPY
85 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
86 , x_return_status OUT NOCOPY VARCHAR2
87 , x_msg_count OUT NOCOPY NUMBER
88 , x_msg_data OUT NOCOPY VARCHAR2
89 )
90 IS
91 l_api_version_number CONSTANT NUMBER := 1.0;
92 l_api_name CONSTANT VARCHAR2(30) := 'Create_Movement_Statistics';
93 l_movement_id NUMBER;
94 l_parent_movement_id NUMBER := NULL;
95 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
96 l_insert_flag VARCHAR2(1);
97 l_return_status1 VARCHAR2(1);
98 l_return_status2 VARCHAR2(1);
99
100 BEGIN
101 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
102 THEN
103 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
104 , G_MODULE_NAME || l_api_name || '.begin'
105 ,'enter procedure'
106 );
107 END IF;
108
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 l_return_status1 := 'S';
111 l_return_status2 := 'S';
112
113 g_movement_id := NULL;
114 g_parent_movement_id := NULL;
115
116 -- Standard call to check for call compatibility
117 IF NOT FND_API.Compatible_API_Call
118 ( l_api_version_number
119 , p_api_version_number
120 , l_api_name
121 , G_PKG_NAME
122 )
123 THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 l_movement_transaction := x_movement_transaction;
128 l_insert_flag := 'Y';
129
130 IF l_movement_transaction.movement_id is not null
131 THEN
132 l_movement_id := l_movement_transaction.movement_id;
133 END IF;
134
135 IF l_movement_transaction.movement_status IN ('O','V','P')
136 THEN
137 INV_MGD_MVT_DEF_ATTR.Default_Attr
138 ( p_api_version_number => 1.0
139 , p_init_msg_list => FND_API.G_FALSE
140 , p_movement_transaction => l_movement_transaction
141 , x_transaction_nature => l_movement_transaction.transaction_nature
142 , x_delivery_terms => l_movement_transaction.delivery_terms
143 , x_area => l_movement_transaction.area
144 , x_port => l_movement_transaction.port
145 , x_csa_code => l_movement_transaction.csa_code
146 , x_oil_reference_code => l_movement_transaction.oil_reference_code
147 , x_container_type_code => l_movement_transaction.container_type_code
148 , x_flow_indicator_code => l_movement_transaction.flow_indicator_code
149 , x_affiliation_reference_code =>
150 l_movement_transaction.affiliation_reference_code
151 , x_taric_code => l_movement_transaction.taric_code
152 , x_preference_code => l_movement_transaction.preference_code
153 , x_statistical_procedure_code =>
154 l_movement_transaction.statistical_procedure_code
155 , x_transport_mode => l_movement_transaction.transport_mode
156 , x_msg_count => x_msg_count
157 , x_msg_data => x_msg_data
158 , x_return_status => l_return_status1
159 );
160
161 INV_MGD_MVT_DEF_ATTR.Default_Value
162 ( p_api_version_number => 1.0
163 , p_init_msg_list => FND_API.G_FALSE
164 , p_movement_transaction => l_movement_transaction
165 , x_document_unit_price => l_movement_transaction.document_unit_price
166 , x_document_line_ext_value => l_movement_transaction.document_line_ext_value
167 , x_movement_amount => l_movement_transaction.movement_amount
168 , x_stat_ext_value => l_movement_transaction.stat_ext_value
169 , x_msg_count => x_msg_count
170 , x_msg_data => x_msg_data
171 , x_return_status => l_return_status2
172 );
173
174 --yawang fix bug 2268875
175 IF l_return_status1 <> 'S'
176 THEN
177 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
178 THEN
179 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
180 , G_MODULE_NAME || l_api_name
181 , 'Failed in calling default_attr '||substrb(x_msg_data,1,255)
182 );
183 END IF;
184 END IF;
185
186 IF l_return_status2 <> 'S'
187 THEN
188 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
189 THEN
190 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
191 , G_MODULE_NAME || l_api_name
192 , 'Failed in calling default_Value '||substrb(x_msg_data,1,255)
193 );
194 END IF;
195 END IF;
196
197 IF (l_return_status1 = 'S' AND l_return_status2 = 'S')
198 THEN
199 SELECT MTL_MOVEMENT_STATISTICS_S.NEXTVAL
200 INTO l_parent_movement_id
201 FROM SYS.DUAL;
202
203 INSERT INTO
204 MTL_MOVEMENT_STATISTICS(
205 movement_id
206 , organization_id
207 , entity_org_id
208 , movement_type
209 , movement_status
210 , transaction_date
211 , last_update_date
212 , last_updated_by
213 , creation_date
214 , created_by
215 , last_update_login
216 , document_source_type
217 , creation_method
218 , document_reference
219 , document_line_reference
220 , document_unit_price
221 , document_line_ext_value
222 , receipt_reference
223 , shipment_reference
224 , shipment_line_reference
225 , pick_slip_reference
226 , customer_name
227 , customer_number
228 , customer_location
229 , transacting_from_org
230 , transacting_to_org
231 , vendor_name
232 , vendor_number
233 , vendor_site
234 , bill_to_name
235 , bill_to_number
236 , bill_to_site
237 , ship_to_name
238 , ship_to_number
239 , ship_to_site
240 , po_header_id
241 , po_line_id
242 , po_line_location_id
243 , order_header_id
244 , order_line_id
245 , picking_line_id
246 , shipment_header_id
247 , shipment_line_id
248 , ship_to_customer_id
249 , ship_to_site_use_id
250 , bill_to_customer_id
251 , bill_to_site_use_id
252 , vendor_id
253 , vendor_site_id
254 , from_organization_id
255 , to_organization_id
256 , parent_movement_id
257 , inventory_item_id
258 , item_description
259 , item_cost
260 , transaction_quantity
261 , transaction_uom_code
262 , primary_quantity
263 , invoice_batch_id
264 , invoice_id
265 , customer_trx_line_id
266 , invoice_batch_reference
267 , invoice_reference
268 , invoice_line_reference
269 , invoice_date_reference
270 , invoice_quantity
271 , invoice_unit_price
272 , invoice_line_ext_value
273 , outside_code
274 , outside_ext_value
275 , outside_unit_price
276 , currency_code
277 , currency_conversion_rate
278 , currency_conversion_type
279 , currency_conversion_date
280 , period_name
281 , report_reference
282 , report_date
283 , category_id
284 , weight_method
285 , unit_weight
286 , total_weight
287 , transaction_nature
288 , delivery_terms
289 , transport_mode
290 , alternate_quantity
291 , alternate_uom_code
292 , dispatch_territory_code
293 , destination_territory_code
294 , origin_territory_code
295 , dispatch_territory_eu_code
296 , destination_territory_eu_code
297 , origin_territory_eu_code
298 , stat_method
299 , stat_adj_percent
300 , stat_adj_amount
301 , stat_ext_value
302 , area
303 , port
304 , stat_type
305 , comments
306 , commodity_code
307 , commodity_description
308 , requisition_header_id
309 , requisition_line_id
310 , picking_line_detail_id
311 , attribute1
312 , attribute2
313 , attribute3
314 , attribute4
315 , attribute5
316 , attribute6
317 , attribute7
318 , attribute8
319 , attribute9
320 , attribute10
321 , attribute11
322 , attribute12
323 , attribute13
324 , attribute14
325 , attribute15
326 , edi_sent_flag
327 , usage_type
328 , zone_code
329 , statistical_procedure_code
330 , movement_amount
331 , taric_code
332 , preference_code
333 , triangulation_country_code
334 , triangulation_country_eu_code
335 , csa_code
336 , oil_reference_code
337 , container_type_code
338 , flow_indicator_code
339 , affiliation_reference_code
340 , set_of_books_period
341 , rcv_transaction_id
342 , mtl_transaction_id
343 , total_weight_uom_code
344 , distribution_line_number
345 , financial_document_flag
346 , edi_transaction_reference
347 , edi_transaction_date
348 , esl_drop_shipment_code
349 , customer_vat_number
350 )
351 VALUES(
352 l_parent_movement_id
353 , l_movement_transaction.organization_id
354 , l_movement_transaction.entity_org_id
355 , l_movement_transaction.movement_type
356 , l_movement_transaction.movement_status
357 , TRUNC(l_movement_transaction.transaction_date)
358 , l_movement_transaction.last_update_date
359 , l_movement_transaction.last_updated_by
360 , l_movement_transaction.creation_date
361 , l_movement_transaction.created_by
362 , l_movement_transaction.last_update_login
363 , l_movement_transaction.document_source_type
364 , NVL(l_movement_transaction.creation_method,'A')
365 , l_movement_transaction.document_reference
366 , l_movement_transaction.document_line_reference
367 , l_movement_transaction.document_unit_price
368 , l_movement_transaction.document_line_ext_value
369 , l_movement_transaction.receipt_reference
370 , l_movement_transaction.shipment_reference
371 , l_movement_transaction.shipment_line_reference
372 , l_movement_transaction.pick_slip_reference
373 , l_movement_transaction.customer_name
374 , l_movement_transaction.customer_number
375 , l_movement_transaction.customer_location
376 , l_movement_transaction.transacting_from_org
377 , l_movement_transaction.transacting_to_org
378 , l_movement_transaction.vendor_name
379 , l_movement_transaction.vendor_number
380 , l_movement_transaction.vendor_site
381 , l_movement_transaction.bill_to_name
382 , l_movement_transaction.bill_to_number
383 , l_movement_transaction.bill_to_site
384 , l_movement_transaction.ship_to_name
385 , l_movement_transaction.ship_to_number
386 , l_movement_transaction.ship_to_site
387 , l_movement_transaction.po_header_id
388 , l_movement_transaction.po_line_id
389 , l_movement_transaction.po_line_location_id
390 , l_movement_transaction.order_header_id
391 , l_movement_transaction.order_line_id
392 , l_movement_transaction.picking_line_id
393 , l_movement_transaction.shipment_header_id
394 , l_movement_transaction.shipment_line_id
395 , l_movement_transaction.ship_to_customer_id
396 , l_movement_transaction.ship_to_site_use_id
397 , l_movement_transaction.bill_to_customer_id
398 , l_movement_transaction.bill_to_site_use_id
399 , l_movement_transaction.vendor_id
400 , l_movement_transaction.vendor_site_id
401 , l_movement_transaction.from_organization_id
402 , l_movement_transaction.to_organization_id
403 , nvl(l_movement_id,l_parent_movement_id)
404 , l_movement_transaction.inventory_item_id
405 , l_movement_transaction.item_description
406 , l_movement_transaction.item_cost
407 , l_movement_transaction.transaction_quantity
408 , l_movement_transaction.transaction_uom_code
409 , l_movement_transaction.primary_quantity
410 , l_movement_transaction.invoice_batch_id
411 , l_movement_transaction.invoice_id
412 , l_movement_transaction.customer_trx_line_id
413 , l_movement_transaction.invoice_batch_reference
414 , l_movement_transaction.invoice_reference
415 , l_movement_transaction.invoice_line_reference
416 , l_movement_transaction.invoice_date_reference
417 , l_movement_transaction.invoice_quantity
418 , l_movement_transaction.invoice_unit_price
419 , l_movement_transaction.invoice_line_ext_value
420 , l_movement_transaction.outside_code
421 , l_movement_transaction.outside_ext_value
422 , l_movement_transaction.outside_unit_price
423 , l_movement_transaction.currency_code
424 , l_movement_transaction.currency_conversion_rate
425 , l_movement_transaction.currency_conversion_type
426 , l_movement_transaction.currency_conversion_date
427 , l_movement_transaction.period_name
428 , l_movement_transaction.report_reference
429 , l_movement_transaction.report_date
430 , l_movement_transaction.category_id
431 , l_movement_transaction.weight_method
432 , l_movement_transaction.unit_weight
433 , l_movement_transaction.total_weight
434 , l_movement_transaction.transaction_nature
435 , l_movement_transaction.delivery_terms
436 , l_movement_transaction.transport_mode
437 , l_movement_transaction.alternate_quantity
438 , l_movement_transaction.alternate_uom_code
439 , l_movement_transaction.dispatch_territory_code
440 , l_movement_transaction.destination_territory_code
441 , l_movement_transaction.origin_territory_code
442 , l_movement_transaction.dispatch_territory_eu_code
443 , l_movement_transaction.destination_territory_eu_code
444 , l_movement_transaction.origin_territory_eu_code
445 , l_movement_transaction.stat_method
446 , l_movement_transaction.stat_adj_percent
447 , l_movement_transaction.stat_adj_amount
448 , nvl(l_movement_transaction.stat_ext_value,
449 l_movement_transaction.movement_amount)
450 , l_movement_transaction.area
451 , l_movement_transaction.port
452 , l_movement_transaction.stat_type
453 , l_movement_transaction.comments
454 , l_movement_transaction.commodity_code
455 , l_movement_transaction.commodity_description
456 , l_movement_transaction.requisition_header_id
457 , l_movement_transaction.requisition_line_id
458 , l_movement_transaction.picking_line_detail_id
459 , l_movement_transaction.attribute1
460 , l_movement_transaction.attribute2
461 , l_movement_Transaction.attribute3
462 , l_movement_Transaction.attribute4
463 , l_movement_Transaction.attribute5
464 , l_movement_Transaction.attribute6
465 , l_movement_Transaction.attribute7
466 , l_movement_Transaction.attribute8
467 , l_movement_Transaction.attribute9
468 , l_movement_Transaction.attribute10
469 , l_movement_Transaction.attribute11
470 , l_movement_Transaction.attribute12
471 , l_movement_Transaction.attribute13
472 , l_movement_Transaction.attribute14
473 , l_movement_Transaction.attribute15
474 , l_movement_transaction.edi_sent_flag
475 , l_movement_transaction.usage_type
476 , l_movement_transaction.zone_code
477 , l_movement_transaction.statistical_procedure_code
478 , l_movement_transaction.movement_amount
479 , l_movement_transaction.taric_code
480 , l_movement_transaction.preference_code
481 , l_movement_transaction.triangulation_country_code
482 , l_movement_transaction.triangulation_country_eu_code
483 , l_movement_transaction.csa_code
484 , l_movement_transaction.oil_reference_code
485 , l_movement_transaction.container_type_code
486 , l_movement_transaction.flow_indicator_code
487 , l_movement_transaction.affiliation_reference_code
488 , l_movement_transaction.set_of_books_period
489 , l_movement_transaction.rcv_transaction_id
490 , l_movement_transaction.mtl_transaction_id
491 , l_movement_transaction.total_weight_uom_code
492 , l_movement_transaction.distribution_line_number
493 , NVL(l_movement_transaction.financial_document_flag,'NOT_REQUIRED')
494 , l_movement_transaction.edi_transaction_reference
495 , l_movement_transaction.edi_transaction_date
496 , l_movement_transaction.esl_drop_shipment_code
497 , l_movement_transaction.customer_vat_number
498 );
499
500 g_movement_id := l_parent_movement_id;
501 g_parent_movement_id := nvl(l_movement_id,l_parent_movement_id);
502
503 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
504 THEN
505 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
506 , G_MODULE_NAME || l_api_name
507 , 'Insert movement record successfully'
508 );
509 END IF;
510 END IF;
511
512 x_movement_transaction := l_movement_transaction;
513 x_movement_transaction.movement_id := g_movement_id;
514 END IF;
515
516 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
517 THEN
518 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
519 , G_MODULE_NAME || l_api_name || '.end'
520 ,'exit procedure'
521 );
522 END IF;
523 EXCEPTION
524
525 WHEN NO_DATA_FOUND THEN
526 g_movement_id := null;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 FND_MESSAGE.Set_Name('INV', 'INV_MGD_UPDATE_EXC');
529 FND_MSG_PUB.Add;
530 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
531 THEN
532 FND_MSG_PUB.Add_Exc_Msg
533 ( G_PKG_NAME
534 , 'Create_Movement_Statistics'
535 );
536 END IF;
537 RAISE g_no_data_transaction_exc;
538
539
540 WHEN FND_API.G_EXC_ERROR THEN
541 g_movement_id := null;
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 -- Get message count and data
544 FND_MSG_PUB.Count_And_Get
545 ( p_count => x_msg_count
546 , p_data => x_msg_data
547 );
548
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550 g_movement_id := null;
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
552 -- Get message count and data
553 FND_MSG_PUB.Count_And_Get
554 ( p_count => x_msg_count
555 , p_data => x_msg_data
556 );
557
558 WHEN OTHERS THEN
559 g_movement_id := null;
560 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
561 THEN
562 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
563 , 'Create_Movement_Statistics'
564 );
565 END IF;
566 -- Get message count and data
567 FND_MSG_PUB.Count_And_Get
568 ( p_count => x_msg_count
569 , p_data => x_msg_data
570 );
571 RAISE;
572
573 END Create_Movement_Statistics;
574
575 --========================================================================
576 -- PROCEDURE : Init_Movement_Record
577 -- PARAMETERS:
578 -- x_movement_transaction in out movement transaction data record
579 -- COMMENT : This procedure defaults values for certain attributes which
580 -- are common for all the transactions.
581 -- Eg: statistical_procedure_code,creation_method etc.
582 --=======================================================================
583 PROCEDURE Init_Movement_Record
584 ( x_movement_transaction IN OUT NOCOPY
585 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
586 )
587 IS
588 l_api_name CONSTANT VARCHAR2(30) := 'Init_Movement_Record';
589 BEGIN
590 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
591 THEN
592 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
593 , G_MODULE_NAME || l_api_name || '.begin'
594 ,'enter procedure'
595 );
596 END IF;
597
598 x_movement_transaction.last_updated_by :=
599 NVL(TO_NUMBER(fnd_profile.value('USER_ID')),0);
600 x_movement_transaction.creation_date := SYSDATE;
601 x_movement_transaction.last_update_date := SYSDATE;
602 x_movement_transaction.created_by :=
603 NVL(TO_NUMBER(fnd_profile.value('USER_ID')),0);
604 x_movement_transaction.last_update_login :=
605 NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')),0);
606 x_movement_transaction.weight_method := 'S';
607 x_movement_transaction.stat_method := 'S';
608 -- x_movement_transaction.creation_method := 'A';
609 IF NVL(x_movement_transaction.movement_status,'N') = 'N' THEN
610 x_movement_transaction.movement_status := 'O';
611 END IF;
612 x_movement_transaction.edi_sent_flag := 'N';
613 x_movement_transaction.statistical_procedure_code := NULL;
614 x_movement_transaction.transport_mode := '3';
615 x_movement_transaction.financial_document_flag := 'NOT_REQUIRED';
616
617 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
618 THEN
619 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
620 , G_MODULE_NAME || l_api_name || '.end'
621 ,'exit procedure'
622 );
623 END IF;
624 END Init_Movement_Record;
625
626
627 --========================================================================
628 -- PROCEDURE : Get_Open_Mvmt_Stats_Txns PRIVATE
629 -- PARAMETERS: val_crsr REF cursor
630 -- x_return_status return status
631 -- p_start_date Transaction start date
632 -- p_end_date Transaction end date
633 -- COMMENT :
634 -- This opens the cursor for INV and returns the cursor.
635 --========================================================================
636
637 PROCEDURE Get_Open_Mvmt_Stats_Txns (
638 val_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.valCurTyp
639 , p_movement_statistics IN
640 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
641 , p_legal_entity_id IN NUMBER
642 , p_economic_zone_code IN VARCHAR2
643 , p_usage_type IN VARCHAR2
644 , p_stat_type IN VARCHAR2
645 , p_period_name IN VARCHAR2
646 , p_document_source_type IN VARCHAR2
647 , x_return_status OUT NOCOPY VARCHAR2
648 )
649 IS
650 l_api_name CONSTANT VARCHAR2(30) := 'Get_Open_Mvmt_Stats_Txns';
651 BEGIN
652 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
653 THEN
654 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
655 , G_MODULE_NAME || l_api_name || '.begin'
656 ,'enter procedure'
657 );
658 END IF;
659
660 x_return_status := 'Y';
661
662 IF val_crsr%ISOPEN THEN
663 CLOSE val_crsr;
664 END IF;
665
666
667 OPEN val_crsr FOR
668 SELECT
669 mtl_stats.movement_id
670 , mtl_stats.organization_id
671 , mtl_stats.entity_org_id
672 , mtl_stats.movement_type
673 , mtl_stats.movement_status
674 , mtl_stats.transaction_date
675 , mtl_stats.last_update_date
676 , mtl_stats.last_updated_by
677 , mtl_stats.creation_date
678 , mtl_stats.created_by
679 , mtl_stats.last_update_login
680 , mtl_stats.document_source_type
681 , mtl_stats.creation_method
682 , mtl_stats.document_reference
683 , mtl_stats.document_line_reference
684 , mtl_stats.document_unit_price
685 , mtl_stats.document_line_ext_value
686 , mtl_stats.receipt_reference
687 , mtl_stats.shipment_reference
688 , mtl_stats.shipment_line_reference
689 , mtl_stats.pick_slip_reference
690 , mtl_stats.customer_name
691 , mtl_stats.customer_number
692 , mtl_stats.customer_location
693 , mtl_stats.transacting_from_org
694 , mtl_stats.transacting_to_org
695 , mtl_stats.vendor_name
696 , mtl_stats.vendor_number
697 , mtl_stats.vendor_site
698 , mtl_stats.bill_to_name
699 , mtl_stats.bill_to_number
700 , mtl_stats.bill_to_site
701 , mtl_stats.po_header_id
702 , mtl_stats.po_line_id
703 , mtl_stats.po_line_location_id
704 , mtl_stats.order_header_id
705 , mtl_stats.order_line_id
706 , mtl_stats.picking_line_id
707 , mtl_stats.shipment_header_id
708 , mtl_stats.shipment_line_id
709 , mtl_stats.ship_to_customer_id
710 , mtl_stats.ship_to_site_use_id
711 , mtl_stats.bill_to_customer_id
712 , mtl_stats.bill_to_site_use_id
713 , mtl_stats.vendor_id
714 , mtl_stats.vendor_site_id
715 , mtl_stats.from_organization_id
716 , mtl_stats.to_organization_id
717 , mtl_stats.parent_movement_id
718 , mtl_stats.inventory_item_id
719 , mtl_stats.item_description
720 , mtl_stats.item_cost
721 , mtl_stats.transaction_quantity
722 , mtl_stats.transaction_uom_code
723 , mtl_stats.primary_quantity
724 , mtl_stats.invoice_batch_id
725 , mtl_stats.invoice_id
726 , mtl_stats.customer_trx_line_id
727 , mtl_stats.invoice_batch_reference
728 , mtl_stats.invoice_reference
729 , mtl_stats.invoice_line_reference
730 , mtl_stats.invoice_date_reference
731 , mtl_stats.invoice_quantity
732 , mtl_stats.invoice_unit_price
733 , mtl_stats.invoice_line_ext_value
734 , mtl_stats.outside_code
735 , mtl_stats.outside_ext_value
736 , mtl_stats.outside_unit_price
737 , mtl_stats.currency_code
738 , mtl_stats.currency_conversion_rate
739 , mtl_stats.currency_conversion_type
740 , mtl_stats.currency_conversion_date
741 , mtl_stats.period_name
742 , mtl_stats.report_reference
743 , mtl_stats.report_date
744 , mtl_stats.category_id
745 , mtl_stats.weight_method
746 , mtl_stats.unit_weight
747 , mtl_stats.total_weight
748 , mtl_stats.transaction_nature
749 , mtl_stats.delivery_terms
750 , mtl_stats.transport_mode
751 , mtl_stats.alternate_quantity
752 , mtl_stats.alternate_uom_code
753 , mtl_stats.dispatch_territory_code
754 , mtl_stats.destination_territory_code
755 , mtl_stats.origin_territory_code
756 , mtl_stats.stat_method
757 , mtl_stats.stat_adj_percent
758 , mtl_stats.stat_adj_amount
759 , mtl_stats.stat_ext_value
760 , mtl_stats.area
761 , mtl_stats.port
762 , mtl_stats.stat_type
763 , mtl_stats.comments
764 , mtl_stats.attribute_category
765 , mtl_stats.commodity_code
766 , mtl_stats.commodity_description
767 , mtl_stats.requisition_header_id
768 , mtl_stats.requisition_line_id
769 , mtl_stats.picking_line_detail_id
770 , mtl_stats.usage_type
771 , mtl_stats.zone_code
772 , mtl_stats.edi_sent_flag
773 , mtl_stats.statistical_procedure_code
774 , mtl_stats.movement_amount
775 , mtl_stats.triangulation_country_code
776 , mtl_stats.csa_code
777 , mtl_stats.oil_reference_code
778 , mtl_stats.container_type_code
779 , mtl_stats.flow_indicator_code
780 , mtl_stats.affiliation_reference_code
781 , mtl_stats.origin_territory_eu_code
782 , mtl_stats.destination_territory_eu_code
783 , mtl_stats.dispatch_territory_eu_code
784 , mtl_stats.set_of_books_period
785 , mtl_stats.taric_code
786 , mtl_stats.preference_code
787 , mtl_stats.rcv_transaction_id
788 , mtl_stats.mtl_transaction_id
789 , mtl_stats.total_weight_uom_code
790 , mtl_stats.financial_document_flag
791 , mtl_stats.customer_vat_number
792 , mtl_stats.attribute1
793 , mtl_stats.attribute2
794 , mtl_stats.attribute3
795 , mtl_stats.attribute4
796 , mtl_stats.attribute5
797 , mtl_stats.attribute6
798 , mtl_stats.attribute7
799 , mtl_stats.attribute8
800 , mtl_stats.attribute9
801 , mtl_stats.attribute10
802 , mtl_stats.attribute11
803 , mtl_stats.attribute12
804 , mtl_stats.attribute13
805 , mtl_stats.attribute14
806 , mtl_stats.attribute15
807 , mtl_stats.triangulation_country_eu_code
808 , mtl_stats.distribution_line_number
809 , mtl_stats.ship_to_name
810 , mtl_stats.ship_to_number
811 , mtl_stats.ship_to_site
812 , mtl_stats.edi_transaction_date
813 , mtl_stats.edi_transaction_reference
814 , mtl_stats.esl_drop_shipment_code
815 FROM
816 MTL_MOVEMENT_STATISTICS mtl_stats
817 WHERE entity_org_id = p_legal_entity_id
818 AND period_name = p_period_name
819 AND document_source_type = nvl(p_document_source_type, document_source_type)
820 AND zone_code = p_economic_zone_code
821 AND usage_type = p_usage_type
822 AND stat_type = p_stat_type
823 AND (movement_status = 'O' OR (financial_document_flag = 'MISSING'))
824 ORDER BY
825 mtl_stats.movement_id;
826
827 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
828 THEN
829 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
830 , G_MODULE_NAME || l_api_name || '.end'
831 ,'exit procedure'
832 );
833 END IF;
834
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 x_return_status := 'N';
838 WHEN OTHERS THEN
839 x_return_status := 'N';
840
841 END Get_Open_Mvmt_Stats_Txns;
842
843
844 --========================================================================
845 -- PROCEDURE : Update_Movement_Statistics PRIVATE
846 --
847 -- PARAMETERS: x_return_status Procedure return status
848 -- x_msg_count Number of messages in the list
849 -- x_msg_data Message text
850 -- P_MOVEMENT_STATISTICS Material Movement Statistics transaction
851 -- Input data record
852 --
853 -- COMMENT : Procedure body to Update the Movement
854 -- Statistics record with the
855 -- calculated values ( EX: Invoice information, Status etc ).
856 -- Updated : 09/Jul/1999
857 --=======================================================================--
858 PROCEDURE Update_Movement_Statistics (
859 p_movement_statistics IN
860 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
861 , x_return_status OUT NOCOPY VARCHAR2
862 , x_msg_count OUT NOCOPY NUMBER
863 , x_msg_data OUT NOCOPY VARCHAR2
864 )
865 IS
866 l_api_name CONSTANT VARCHAR2(30) := 'Update_Movement_Statistics';
867 l_api_version_number CONSTANT NUMBER := 1.0;
868 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
869 l_return_status1 VARCHAR2(1);
870 l_return_status2 VARCHAR2(1);
871 BEGIN
872 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
873 THEN
874 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
875 , G_MODULE_NAME || l_api_name || '.begin'
876 ,'enter procedure'
877 );
878 END IF;
879 l_movement_transaction := p_movement_statistics;
880 x_return_status := FND_API.G_RET_STS_SUCCESS;
881 /*bug #7499719 Call Out Program was not called for Update Movement Statistic*/
882 IF l_movement_transaction.movement_status IN ('O','V','P')
883 THEN
884 INV_MGD_MVT_DEF_ATTR.Default_Attr
885 ( p_api_version_number => 1.0
886 , p_init_msg_list => FND_API.G_FALSE
887 , p_movement_transaction => l_movement_transaction
888 , x_transaction_nature => l_movement_transaction.transaction_nature
889 , x_delivery_terms => l_movement_transaction.delivery_terms
890 , x_area => l_movement_transaction.area
891 , x_port => l_movement_transaction.port
892 , x_csa_code => l_movement_transaction.csa_code
893 , x_oil_reference_code => l_movement_transaction.oil_reference_code
894 , x_container_type_code => l_movement_transaction.container_type_code
895 , x_flow_indicator_code => l_movement_transaction.flow_indicator_code
896 , x_affiliation_reference_code =>
897 l_movement_transaction.affiliation_reference_code
898 , x_taric_code => l_movement_transaction.taric_code
899 , x_preference_code => l_movement_transaction.preference_code
900 , x_statistical_procedure_code =>
901 l_movement_transaction.statistical_procedure_code
902 , x_transport_mode => l_movement_transaction.transport_mode
903 , x_msg_count => x_msg_count
904 , x_msg_data => x_msg_data
905 , x_return_status => l_return_status1
906 );
907
908 INV_MGD_MVT_DEF_ATTR.Default_Value
909 ( p_api_version_number => 1.0
910 , p_init_msg_list => FND_API.G_FALSE
911 , p_movement_transaction => l_movement_transaction
912 , x_document_unit_price => l_movement_transaction.document_unit_price
913 , x_document_line_ext_value => l_movement_transaction.document_line_ext_value
914 , x_movement_amount => l_movement_transaction.movement_amount
915 , x_stat_ext_value => l_movement_transaction.stat_ext_value
916 , x_msg_count => x_msg_count
917 , x_msg_data => x_msg_data
918 , x_return_status => l_return_status2
919 );
920 IF l_return_status1 <> 'S'
921 THEN
922 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
923 THEN
924 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
925 , G_MODULE_NAME || l_api_name
926 , 'Failed in calling default_attr '||substrb(x_msg_data,1,255)
927 );
928 END IF;
929 END IF;
930
931 IF l_return_status2 <> 'S'
932 THEN
933 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
934 THEN
935 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
936 , G_MODULE_NAME || l_api_name
937 , 'Failed in calling default_Value '||substrb(x_msg_data,1,255)
938 );
939 END IF;
940 END IF;
941 END IF;
942 /*bug #7499719 End*/
943
944 UPDATE MTL_MOVEMENT_STATISTICS
945 SET movement_id = P_MOVEMENT_STATISTICS.movement_id
946 , organization_id = P_MOVEMENT_STATISTICS.organization_id
947 , entity_org_id = P_MOVEMENT_STATISTICS.entity_org_id
948 , movement_type = P_MOVEMENT_STATISTICS.movement_type
949 , movement_status = P_MOVEMENT_STATISTICS.movement_status
950 , transaction_date = P_MOVEMENT_STATISTICS.transaction_date
951 , last_update_date = SYSDATE
952 , last_updated_by = NVL
953 ( TO_NUMBER(FND_PROFILE.Value('USER_ID'))
954 , last_updated_by
955 )
956 , last_update_login = NVL
957 ( TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
958 , last_update_login
959 )
960 , document_source_type = P_MOVEMENT_STATISTICS.document_source_type
961 , creation_method = P_MOVEMENT_STATISTICS.creation_method
962 , document_reference = P_MOVEMENT_STATISTICS.document_reference
963 , document_line_reference =
964 P_MOVEMENT_STATISTICS.document_line_reference
965 , document_unit_price = l_movement_transaction.document_unit_price
966 , document_line_ext_value =
967 P_MOVEMENT_STATISTICS.document_line_ext_value
968 , receipt_reference = P_MOVEMENT_STATISTICS.receipt_reference
969 , shipment_reference = P_MOVEMENT_STATISTICS.shipment_reference
970 , shipment_line_reference =
971 P_MOVEMENT_STATISTICS.shipment_line_reference
972 , pick_slip_reference = P_MOVEMENT_STATISTICS.pick_slip_reference
973 , customer_name = P_MOVEMENT_STATISTICS.customer_name
974 , customer_number = P_MOVEMENT_STATISTICS.customer_number
975 , customer_location = P_MOVEMENT_STATISTICS.customer_location
976 , transacting_from_org = P_MOVEMENT_STATISTICS.transacting_from_org
977 , transacting_to_org = P_MOVEMENT_STATISTICS.transacting_to_org
978 , vendor_name = P_MOVEMENT_STATISTICS.vendor_name
979 , vendor_number = P_MOVEMENT_STATISTICS.vendor_number
980 , vendor_site = P_MOVEMENT_STATISTICS.vendor_site
981 , bill_to_name = P_MOVEMENT_STATISTICS.bill_to_name
982 , bill_to_number = P_MOVEMENT_STATISTICS.bill_to_number
983 , bill_to_site = P_MOVEMENT_STATISTICS.bill_to_site
984 , ship_to_name = P_MOVEMENT_STATISTICS.ship_to_name
985 , ship_to_number = P_MOVEMENT_STATISTICS.ship_to_number
986 , ship_to_site = P_MOVEMENT_STATISTICS.ship_to_site
987 , po_header_id = P_MOVEMENT_STATISTICS.po_header_id
988 , po_line_id = P_MOVEMENT_STATISTICS.po_line_id
989 , po_line_location_id = P_MOVEMENT_STATISTICS.po_line_location_id
990 , order_header_id = P_MOVEMENT_STATISTICS.order_header_id
991 , order_line_id = P_MOVEMENT_STATISTICS.order_line_id
992 , picking_line_id = P_MOVEMENT_STATISTICS.picking_line_id
993 , shipment_header_id = P_MOVEMENT_STATISTICS.shipment_header_id
994 , shipment_line_id = P_MOVEMENT_STATISTICS.shipment_line_id
995 , ship_to_customer_id = P_MOVEMENT_STATISTICS.ship_to_customer_id
996 , ship_to_site_use_id = P_MOVEMENT_STATISTICS.ship_to_site_use_id
997 , bill_to_customer_id = P_MOVEMENT_STATISTICS.bill_to_customer_id
998 , bill_to_site_use_id = P_MOVEMENT_STATISTICS.bill_to_site_use_id
999 , vendor_id = P_MOVEMENT_STATISTICS.vendor_id
1000 , vendor_site_id = P_MOVEMENT_STATISTICS.vendor_site_id
1001 , from_organization_id = P_MOVEMENT_STATISTICS.from_organization_id
1002 , to_organization_id = P_MOVEMENT_STATISTICS.to_organization_id
1003 , parent_movement_id = P_MOVEMENT_STATISTICS.parent_movement_id
1004 , inventory_item_id = P_MOVEMENT_STATISTICS.inventory_item_id
1005 , item_description = P_MOVEMENT_STATISTICS.item_description
1006 , item_cost = P_MOVEMENT_STATISTICS.item_cost
1007 , transaction_quantity = P_MOVEMENT_STATISTICS.transaction_quantity
1008 , transaction_uom_code = P_MOVEMENT_STATISTICS.transaction_uom_code
1009 , primary_quantity = P_MOVEMENT_STATISTICS.primary_quantity
1010 , invoice_batch_id = P_MOVEMENT_STATISTICS.invoice_batch_id
1011 , invoice_id = P_MOVEMENT_STATISTICS.invoice_id
1012 , customer_trx_line_id = P_MOVEMENT_STATISTICS.customer_trx_line_id
1013 , invoice_batch_reference = P_MOVEMENT_STATISTICS.invoice_batch_reference
1014 , invoice_reference = P_MOVEMENT_STATISTICS.invoice_reference
1015 , invoice_line_reference = P_MOVEMENT_STATISTICS.invoice_line_reference
1016 , invoice_date_reference = P_MOVEMENT_STATISTICS.invoice_date_reference
1017 , invoice_quantity = P_MOVEMENT_STATISTICS.invoice_quantity
1018 , invoice_unit_price = P_MOVEMENT_STATISTICS.invoice_unit_price
1019 , invoice_line_ext_value = P_MOVEMENT_STATISTICS.invoice_line_ext_value
1020 , outside_code = P_MOVEMENT_STATISTICS.outside_code
1021 , outside_ext_value = P_MOVEMENT_STATISTICS.outside_ext_value
1022 , outside_unit_price = P_MOVEMENT_STATISTICS.outside_unit_price
1023 , currency_code = P_MOVEMENT_STATISTICS.currency_code
1024 , currency_conversion_rate = P_MOVEMENT_STATISTICS.currency_conversion_rate
1025 , currency_conversion_type = P_MOVEMENT_STATISTICS.currency_conversion_type
1026 , currency_conversion_date = P_MOVEMENT_STATISTICS.currency_conversion_date
1027 , period_name = P_MOVEMENT_STATISTICS.period_name
1028 , report_reference = P_MOVEMENT_STATISTICS.report_reference
1029 , report_date = P_MOVEMENT_STATISTICS.report_date
1030 , category_id = P_MOVEMENT_STATISTICS.category_id
1031 , weight_method = P_MOVEMENT_STATISTICS.weight_method
1032 , unit_weight = P_MOVEMENT_STATISTICS.unit_weight
1033 , total_weight = P_MOVEMENT_STATISTICS.total_weight
1034 , transaction_nature = l_movement_transaction.transaction_nature
1035 , delivery_terms = l_movement_transaction.delivery_terms
1036 , transport_mode = l_movement_transaction.transport_mode
1037 , alternate_quantity = P_MOVEMENT_STATISTICS.alternate_quantity
1038 , alternate_uom_code = P_MOVEMENT_STATISTICS.alternate_uom_code
1039 , dispatch_territory_code = P_MOVEMENT_STATISTICS.dispatch_territory_code
1040 , destination_territory_code =
1041 P_MOVEMENT_STATISTICS.destination_territory_code
1042 , origin_territory_code = P_MOVEMENT_STATISTICS.origin_territory_code
1043 , dispatch_territory_eu_code =
1044 P_MOVEMENT_STATISTICS.dispatch_territory_eu_code
1045 , destination_territory_eu_code =
1046 P_MOVEMENT_STATISTICS.destination_territory_eu_code
1047 , origin_territory_eu_code =
1048 P_MOVEMENT_STATISTICS.origin_territory_eu_code
1049 , stat_method = P_MOVEMENT_STATISTICS.stat_method
1050 , stat_adj_percent = P_MOVEMENT_STATISTICS.stat_adj_percent
1051 , stat_adj_amount = P_MOVEMENT_STATISTICS.stat_adj_amount
1052 , stat_ext_value = l_movement_transaction.stat_ext_value
1053 , area = l_movement_transaction.area
1054 , port = l_movement_transaction.port
1055 , stat_type = P_MOVEMENT_STATISTICS.stat_type
1056 , commodity_code = P_MOVEMENT_STATISTICS.commodity_code
1057 , commodity_description = P_MOVEMENT_STATISTICS.commodity_description
1058 , requisition_header_id = P_MOVEMENT_STATISTICS.requisition_header_id
1059 , requisition_line_id = P_MOVEMENT_STATISTICS.requisition_line_id
1060 , picking_line_detail_id = P_MOVEMENT_STATISTICS.picking_line_detail_id
1061 , statistical_procedure_code = l_movement_transaction.statistical_procedure_code
1062 , comments = P_MOVEMENT_STATISTICS.comments
1063 , attribute_category = P_MOVEMENT_STATISTICS.attribute_category
1064 , attribute1 = P_MOVEMENT_STATISTICS.attribute1
1065 , attribute2 = P_MOVEMENT_STATISTICS.attribute2
1066 , attribute3 = P_MOVEMENT_STATISTICS.attribute3
1067 , attribute4 = P_MOVEMENT_STATISTICS.attribute4
1068 , attribute5 = P_MOVEMENT_STATISTICS.attribute5
1069 , attribute6 = P_MOVEMENT_STATISTICS.attribute6
1070 , attribute7 = P_MOVEMENT_STATISTICS.attribute7
1071 , attribute8 = P_MOVEMENT_STATISTICS.attribute8
1072 , attribute9 = P_MOVEMENT_STATISTICS.attribute9
1073 , attribute10 = P_MOVEMENT_STATISTICS.attribute10
1074 , attribute11 = P_MOVEMENT_STATISTICS.attribute11
1075 , attribute12 = P_MOVEMENT_STATISTICS.attribute12
1076 , attribute13 = P_MOVEMENT_STATISTICS.attribute13
1077 , attribute14 = P_MOVEMENT_STATISTICS.attribute14
1078 , attribute15 = P_MOVEMENT_STATISTICS.attribute15
1079 , usage_type = P_MOVEMENT_STATISTICS.usage_type
1080 , zone_code = P_MOVEMENT_STATISTICS.zone_code
1081 , edi_sent_flag = P_MOVEMENT_STATISTICS.edi_sent_flag
1082 , movement_amount = l_movement_transaction.movement_amount
1083 , triangulation_country_code =
1084 P_MOVEMENT_STATISTICS.triangulation_country_code
1085 , triangulation_country_eu_code =
1086 P_MOVEMENT_STATISTICS.triangulation_country_eu_code
1087 , distribution_line_number =
1088 P_MOVEMENT_STATISTICS.distribution_line_number
1089 , csa_code = l_movement_transaction.csa_code
1090 , oil_reference_code = l_movement_transaction.oil_reference_code
1091 , container_type_code = l_movement_transaction.container_type_code
1092 , flow_indicator_code = l_movement_transaction.flow_indicator_code
1093 , affiliation_reference_code = l_movement_transaction.affiliation_reference_code
1094 , financial_document_flag = P_MOVEMENT_STATISTICS.financial_document_flag
1095 , set_of_books_period = P_MOVEMENT_STATISTICS.set_of_books_period
1096 , edi_transaction_date = P_MOVEMENT_STATISTICS.edi_transaction_date
1097 , edi_transaction_reference =
1098 P_MOVEMENT_STATISTICS.edi_transaction_reference
1099 , taric_code = l_movement_transaction.taric_code
1100 , preference_code = l_movement_transaction.preference_code
1101 , rcv_transaction_id = P_MOVEMENT_STATISTICS.rcv_transaction_id
1102 , mtl_transaction_id = P_MOVEMENT_STATISTICS.mtl_transaction_id
1103 , total_weight_uom_code = P_MOVEMENT_STATISTICS.total_weight_uom_code
1104 , esl_drop_shipment_code = P_MOVEMENT_STATISTICS.esl_drop_shipment_code
1105 , customer_vat_number = P_MOVEMENT_STATISTICS.customer_vat_number
1106 WHERE movement_id = P_MOVEMENT_STATISTICS.movement_id;
1107
1108 COMMIT ;
1109 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1110 THEN
1111 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1112 , G_MODULE_NAME || l_api_name || '.end'
1113 ,'exit procedure'
1114 );
1115 END IF;
1116
1117 EXCEPTION
1118 WHEN FND_API.G_EXC_ERROR THEN
1119 x_return_status := FND_API.G_RET_STS_ERROR;
1120 -- Get message count and data
1121 FND_MSG_PUB.Count_And_Get
1122 ( p_count => x_msg_count
1123 , p_data => x_msg_data
1124 );
1125
1126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1128 -- Get message count and data
1129 FND_MSG_PUB.Count_And_Get
1130 ( p_count => x_msg_count
1131 , p_data => x_msg_data
1132 );
1133
1134 WHEN OTHERS THEN
1135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1136 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1137 THEN
1138 FND_MSG_PUB.Add_Exc_Msg
1139 ( G_PKG_NAME
1140 , 'INV_MGD_MVT_VALIDATE_TXN'
1141 );
1142 END IF;
1143 -- Get message count and data
1144 FND_MSG_PUB.Count_And_Get
1145 ( p_count => x_msg_count
1146 , p_data => x_msg_data
1147 );
1148
1149 END Update_Movement_Statistics ;
1150
1151 --==================
1152 --LOCAL PROCEDURES
1153 --==================
1154
1155
1156 --========================================================================
1157 -- PROCEDURE : Validate_Record PRIVATE
1158 -- PARAMETERS: p_movement_statistics IN movement transaction record
1159 -- p_movement_stat_usages_rec IN usage record
1160 -- x_movement_statistics OUT movement transaction record
1161 -- x_return_status OUT standard output
1162 -- x_record_status OUT 'Y' if corrected, 'N' otherwise
1163 --
1164 -- VERSION : current version 1.0
1165 -- initial_version 1.0
1166 -- COMMENT : Validate the transaction record for its DELIVERY_TERMS,
1167 -- UNIT_WEIGHT/TOTAL_WEIGHT and COMMODITY_CODE.
1168 --=======================================================================
1169 PROCEDURE Validate_Record
1170 ( p_movement_stat_usages_rec IN
1171 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1172 , x_movement_statistics IN OUT NOCOPY
1173 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1174 , x_return_status OUT NOCOPY VARCHAR2
1175 , x_record_status OUT NOCOPY VARCHAR2
1176 , x_updated_flag OUT NOCOPY VARCHAR2
1177 );
1178
1179 --========================================================================
1180 -- PROCEDURE : Create_Table_Data PRIVATE
1181 -- PARAMETERS: p_col_name
1182 -- p_message_cd
1183 -- VERSION : current version 1.0
1184 -- initial version 1.0
1185 -- COMMENT : Create the Exception message into the Pl/SQL table. This table
1186 -- will be scanned while printing the Exception
1187 --=========================================================================
1188 PROCEDURE Create_Table_Data( p_col_name IN VARCHAR2
1189 , p_message_cd IN NUMBER );
1190
1191 --=================
1192 --PROCEDURE BODY
1193 --=================
1194
1195
1196 --=========================================================================
1197
1198 -- PROCEDURE : Validate_Movement_Statistics PRIVATE
1199 --
1200 -- PARAMETERS:
1201 -- p_movement_statistics Material Movement Statistics transaction
1202 -- Input data record
1203 -- p_movement_stat_usages_rec usage record
1204 -- x_excp_list PL/SQL Table type list for storing
1205 -- and returning the Exception messages
1206 -- x_return_status Procedure return status
1207 -- x_msg_count Number of messages in the list
1208 -- x_msg_data Message text
1209 -- x_movement_statistics Material Movement Statistics transaction
1210 -- Output data record
1211 --
1212 -- VERSION : current version 1.0
1213 -- initial version 1.0
1214 --
1215 -- COMMENT : Procedure specification to Perform the
1216 -- Validation for the Movement
1217 -- Statistics Record FOR Exceptions
1218 --
1219 -- CREATED : 10/20/1999
1220 --=============================================================================-
1221 PROCEDURE Validate_Movement_Statistics
1222 ( p_movement_statistics IN
1223 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1224 , p_movement_stat_usages_rec IN
1225 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1226 , x_excp_list OUT NOCOPY
1227 INV_MGD_MVT_DATA_STR.excp_list
1228 , x_updated_flag OUT NOCOPY VARCHAR2
1229 , x_return_status OUT NOCOPY VARCHAR2
1230 , x_msg_count OUT NOCOPY NUMBER
1231 , x_msg_data OUT NOCOPY VARCHAR2
1232 , x_movement_statistics OUT NOCOPY
1233 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1234 )
1235 IS
1236 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MOVEMENT_STATISTICS';
1237
1238 -- local variables
1239 l_orig_mvmt_transaction
1240 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1241 l_invoice_arrived VARCHAR2(1);
1242 -- removed the use of empty list to reset the list, use DELETE instead
1243 -- l_excp_list_empty INV_MGD_MVT_DATA_STR.excp_list;
1244 l_before_validate
1245 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1246 l_new_movement_transaction
1247 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1248 --l_created_movement_transaction
1249 -- INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1250 l_transaction_type VARCHAR2(30);
1251 l_record_status VARCHAR2(1);
1252 l_uom_status VARCHAR2(1);
1253 l_return_status VARCHAR2(1);
1254 l_updated_flag VARCHAR2(1);
1255 l_corrected VARCHAR2(1);
1256 validate_rule_flag VARCHAR2(1);
1257
1258 BEGIN
1259 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1260 THEN
1261 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1262 , G_MODULE_NAME || l_api_name || '.begin'
1263 ,'enter procedure'
1264 );
1265 END IF;
1266
1267 --Initiliaze the Message Stack IF Required
1268 --IF FND_API.to_Boolean(p_init_msg_list)
1269 --THEN
1270 FND_MSG_PUB.initialize;
1271 --END IF;
1272
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274 validate_rule_flag := 'Y';
1275
1276 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1277 , p_data => x_msg_data );
1278
1279 -- intialize local variables
1280 l_orig_mvmt_transaction := p_movement_statistics;
1281 l_corrected := 'Y';
1282 x_updated_flag :='N';
1283
1284 --yawang fix bug 2261790, duplicate error messages for same mvt id
1285 --Clear global exception list
1286 g_final_excp_list.delete;
1287
1288 --yawang fix invoice info not picked up even though the invoice is in
1289 --Get order number for SO, used in call Calc_Invoice_Info
1290 IF (l_orig_mvmt_transaction.invoice_id IS NULL
1291 AND l_orig_mvmt_transaction.order_header_id IS NOT NULL)
1292 THEN
1293 SELECT
1294 order_number
1295 INTO
1296 l_orig_mvmt_transaction.order_number
1297 FROM
1298 oe_order_headers_all
1299 WHERE header_id = l_orig_mvmt_transaction.order_header_id;
1300 END IF;
1301
1302 -- check scenario 1 : movement_status is FROZEN, EXPORT or EDI is sent
1303 -- modified by yawang, added EXPORT for IDEP support
1304 IF l_orig_mvmt_transaction.movement_status IN ('F', 'X')
1305 OR
1306 l_orig_mvmt_transaction.edi_sent_flag = 'Y'
1307 THEN
1308 IF l_orig_mvmt_transaction.financial_document_flag = 'MISSING'
1309 THEN
1310 -- get invoice information
1311 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1312 ( p_stat_typ_transaction => p_movement_stat_usages_rec
1313 , x_movement_transaction => l_orig_mvmt_transaction
1314 );
1315 -- if we have an invoice_id that means we found the invoice
1316 IF l_orig_mvmt_transaction.invoice_id IS NOT NULL
1317 THEN
1318 IF l_orig_mvmt_transaction.document_source_type IN ('PO','RTV')
1319 THEN
1320 l_transaction_type := 'RECEIPT';
1321 ELSIF l_orig_mvmt_transaction.document_source_type = 'SO'
1322 THEN
1323 l_transaction_type := 'SHIPMENT';
1324 END IF;
1325 l_new_movement_transaction := l_orig_mvmt_transaction;
1326 -- see state diagram for reason of setting status to V and edi to Y
1327 l_new_movement_transaction.movement_status := 'V';
1328 l_new_movement_transaction.edi_sent_flag := 'N';
1329
1330 -- record updated
1331 x_updated_flag :='Y';
1332
1333 -- we make it an adjustment if the new record is within a different
1334 -- period from the original transaction period
1335 IF l_orig_mvmt_transaction.movement_status IN ('F', 'X')
1336 OR l_orig_mvmt_transaction.edi_sent_flag = 'Y'
1337 THEN
1338 IF l_new_movement_transaction.movement_type = 'A'
1339 THEN
1340 l_new_movement_transaction.movement_type := 'AA';
1341 ELSIF l_new_movement_transaction.movement_type = 'D'
1342 THEN
1343 l_new_movement_transaction.movement_type := 'DA';
1344 END IF;
1345 END IF;
1346
1347 l_new_movement_transaction.financial_document_flag := 'PROCESSED_ADJUSTED';
1348 l_new_movement_transaction.report_reference := NULL;
1349 l_new_movement_transaction.report_date := NULL;
1350 l_new_movement_transaction.edi_transaction_reference := NULL;
1351 l_new_movement_transaction.edi_transaction_date := NULL;
1352
1353 l_new_movement_transaction.movement_amount :=
1354 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
1355 (p_movement_transaction => l_new_movement_transaction
1356 );
1357
1358 --Calculate freight charge and include in statistics value
1359 l_new_movement_transaction.stat_ext_value :=
1360 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
1361 (p_movement_transaction => l_new_movement_transaction);
1362
1363 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
1364 ( p_api_version_number => 1.0
1365 , p_init_msg_list => FND_API.G_FALSE
1366 , x_movement_transaction => l_new_movement_transaction
1367 , x_msg_count => x_msg_count
1368 , x_msg_data => x_msg_data
1369 , x_return_status => x_return_status
1370 );
1371
1372 --set original record still no invoice info so that not confusing with
1373 --newly creatd adjusted record
1374 l_orig_mvmt_transaction.invoice_batch_id := null;
1375 l_orig_mvmt_transaction.invoice_date_reference := null;
1376 l_orig_mvmt_transaction.invoice_id := null;
1377 l_orig_mvmt_transaction.invoice_quantity := null;
1378 l_orig_mvmt_transaction.invoice_unit_price := null;
1379 l_orig_mvmt_transaction.invoice_line_ext_value := null;
1380 l_orig_mvmt_transaction.customer_trx_line_id := null;
1381
1382 --Set the orignal financial flag to not missing so that not picked up again
1383 --when run exception report
1384 l_orig_mvmt_transaction.financial_document_flag := 'CREATEDADJ';
1385 END IF;
1386 END IF;
1387
1388 -- check scenario 2 : movement_status is VERIFIED
1389 ELSIF l_orig_mvmt_transaction.movement_status = 'V'
1390 THEN
1391 IF l_orig_mvmt_transaction.financial_document_flag = 'MISSING'
1392 THEN
1393 -- get invoice information
1394 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1395 ( p_stat_typ_transaction => p_movement_stat_usages_rec
1396 , x_movement_transaction => l_orig_mvmt_transaction
1397 );
1398 -- if we have an invoice_id that means we found the invoice
1399 IF l_orig_mvmt_transaction.invoice_id IS NOT NULL
1400 THEN
1401 -- see state diagram for reason of setting status to V
1402 -- and financial_document_flag to PROCESSED_INCLUDED
1403 l_orig_mvmt_transaction.movement_status:='V';
1404 l_orig_mvmt_transaction.financial_document_flag:='PROCESSED_INCLUDED';
1405 -- record updated
1406 x_updated_flag :='Y';
1407
1408 l_orig_mvmt_transaction.movement_amount :=
1409 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
1410 (p_movement_transaction => l_orig_mvmt_transaction
1411 );
1412
1413 --Calculate freight charge and include in statistics value
1414 l_orig_mvmt_transaction.stat_ext_value :=
1415 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
1416 (p_movement_transaction => l_orig_mvmt_transaction);
1417 ELSE
1418 -- see state diagram for reason of setting status to V
1419 -- and financial_document_flag to MISSING
1420 l_orig_mvmt_transaction.movement_status:='V';
1421 l_orig_mvmt_transaction.financial_document_flag:='MISSING';
1422
1423 -- record updated
1424 x_updated_flag :='Y';
1425
1426 -- report exception for MISSING invoice
1427 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1428 THEN
1429 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1430 , G_MODULE_NAME || l_api_name
1431 ,'Exception: invoice missing'
1432 );
1433 END IF;
1434
1435 create_table_data( p_col_name => 'INVOICE_ID'
1436 , p_message_cd => 1
1437 );
1438 END IF;
1439 END IF;
1440
1441 -- check scenario 3 : movement_stauts is OPEN
1442 ELSIF l_orig_mvmt_transaction.movement_status = 'O'
1443 THEN
1444 -- Call validate rules to validate record per rule sets definitions
1445 Validate_Rules
1446 ( p_movement_stat_usages_rec => p_movement_stat_usages_rec
1447 , x_movement_transaction => l_orig_mvmt_transaction
1448 , x_return_status => x_return_status
1449 , x_uom_status => l_uom_status
1450 , x_msg_count => x_msg_count
1451 , x_msg_data => x_msg_data
1452 );
1453
1454 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1455 THEN
1456 validate_rule_flag := 'Y';
1457 ELSE
1458 validate_rule_flag := 'N';
1459 END IF;
1460
1461 -- Check to see if alternate Uom has been updated
1462 IF l_uom_status = 'Y'
1463 THEN
1464 x_updated_flag := 'Y';
1465 END IF;
1466
1467
1468 l_before_validate := l_orig_mvmt_transaction;
1469 Validate_Record
1470 ( p_movement_stat_usages_rec => p_movement_stat_usages_rec
1471 , x_movement_statistics => l_orig_mvmt_transaction
1472 , x_return_status => l_return_status
1473 , x_record_status => l_record_status
1474 , x_updated_flag => l_updated_flag
1475 );
1476
1477 x_updated_flag := l_updated_flag;
1478
1479 IF l_return_status=FND_API.G_RET_STS_ERROR
1480 AND l_record_status='N'
1481 THEN
1482 -- nothing corrected although we found error
1483 -- the API will return FND_API.G_RET_STS_ERROR
1484 l_corrected := 'N';
1485 ELSE
1486 -- see state diagram for reason of setting status to V
1487 -- Added check for rule based validation if Y only then set status to V
1488 IF validate_rule_flag = 'Y' THEN
1489 l_orig_mvmt_transaction.movement_status:='V';
1490 END IF;
1491 END IF;
1492 IF l_orig_mvmt_transaction.financial_document_flag = 'MISSING'
1493 THEN
1494 -- get invoice information
1495 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1496 ( p_stat_typ_transaction => p_movement_stat_usages_rec
1497 , x_movement_transaction => l_orig_mvmt_transaction
1498 );
1499 -- if we have an invoice_id that means we found the invoice
1500 IF l_orig_mvmt_transaction.invoice_id IS NOT NULL
1501 THEN
1502 -- see state diagram for reason of setting status to V
1503 -- and financial document flag to PROCESSED_INCLUDED
1504 -- l_orig_mvmt_transaction.movement_status:='V';
1505 -- commented above line to add check for rule based validation
1506 -- Set status to V only if the record is validated
1507 IF validate_rule_flag = 'Y' THEN
1508 l_orig_mvmt_transaction.movement_status:='V';
1509 END IF;
1510 l_orig_mvmt_transaction.financial_document_flag:='PROCESSED_INCLUDED';
1511 -- record updated
1512 x_updated_flag :='Y';
1513
1514 l_orig_mvmt_transaction.movement_amount :=
1515 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
1516 (p_movement_transaction => l_orig_mvmt_transaction
1517 );
1518
1519 --Calculate freight charge and include in statistics value
1520 l_orig_mvmt_transaction.stat_ext_value :=
1521 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
1522 (p_movement_transaction => l_orig_mvmt_transaction);
1523 ELSE
1524 -- see state diagram for reason of setting status to V
1525 -- Set status to V only if the record is validated
1526 IF validate_rule_flag = 'Y' THEN
1527 l_orig_mvmt_transaction.movement_status:='V';
1528 END IF;
1529 --l_orig_mvmt_transaction.movement_status:='V';
1530 l_orig_mvmt_transaction.financial_document_flag:='MISSING';
1531 -- record updated
1532 x_updated_flag :='Y';
1533 -- report exception for MISSING invoice
1534 create_table_data( p_col_name => 'INVOICE_ID'
1535 , p_message_cd => 1
1536 );
1537 END IF;
1538
1539 -- we are checking 'PROCESS_INCLUDED' just because of previous typo in
1540 -- the code
1541 ELSIF l_orig_mvmt_transaction.financial_document_flag in ('PROCESS_INCLUDED','PROCESSED_INCLUDED')
1542 THEN
1543 -- get invoice information
1544 /*
1545 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1546 ( p_movement_transaction => l_orig_mvmt_transaction
1547 , p_stat_typ_transaction => p_movement_stat_usages_rec
1548 , x_movement_transaction => l_orig_mvmt_transaction
1549 );
1550 */
1551 -- if we have an invoice_id that means we found the invoice
1552 IF l_orig_mvmt_transaction.invoice_id IS NOT NULL
1553 THEN
1554 -- see state diagram for reason of setting status to V
1555 -- Set status to V only if the record is validated
1556 IF validate_rule_flag = 'Y' THEN
1557 l_orig_mvmt_transaction.movement_status:='V';
1558 END IF;
1559 --l_orig_mvmt_transaction.movement_status:='V';
1560 ELSE
1561 -- the invoice is not found, raise unexpected error exception
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END IF;
1564 END IF;
1565 END IF;
1566
1567 -- ssui
1568 -- check if conversion rate is 0, if so, report missing value
1569 -- call get invoice info to obtain conversion rate information
1570 -- (the magic of get invoice info is it update exchange rate information
1571 -- whether or not the invoice is available)
1572 IF l_orig_mvmt_transaction.currency_conversion_rate = 0
1573 THEN
1574 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1575 THEN
1576 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1577 , G_MODULE_NAME || l_api_name
1578 ,'Exception: currency_conversion_rate missing'
1579 );
1580 END IF;
1581
1582 create_table_data( p_col_name => 'CURRENCY_CONVERSION_RATE'
1583 , p_message_cd => 1
1584 );
1585
1586 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1587 ( p_stat_typ_transaction => p_movement_stat_usages_rec
1588 , x_movement_transaction => l_orig_mvmt_transaction
1589 );
1590
1591 -- END IF; ksaini commented out for conversion rate not modified
1592 -- Move conversion rate settting check within this segment
1593
1594 -- check to see if the conversion rate is still 0, if so, mark
1595 -- as exception not corrected, else, report corrected and
1596 -- also recalculate movement_amount
1597 IF l_orig_mvmt_transaction.currency_conversion_rate = 0
1598 THEN
1599 l_corrected := 'N';
1600 ELSE
1601 create_table_data(p_col_name => 'CURRENCY_CONVERSION_RATE'
1602 ,p_message_cd => 4
1603 );
1604 l_orig_mvmt_transaction.movement_amount :=
1605 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
1606 (p_movement_transaction => l_orig_mvmt_transaction
1607 );
1608
1609 --Calculate freight charge and include in statistics value
1610 l_orig_mvmt_transaction.stat_ext_value :=
1611 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
1612 (p_movement_transaction => l_orig_mvmt_transaction);
1613 END IF;
1614 END if; --ksaini moved here to encapsulate the condition within previous IF
1615
1616 -- if l_corrected='N', that means we found errors but didn't correct it
1617 -- so set return status to error
1618 IF l_corrected='N'
1619 THEN
1620 --ssui: bug 1072889, keep status as OPEN if exception but not corrected
1621 l_orig_mvmt_transaction.movement_status:='O';
1622 X_return_status:=FND_API.G_RET_STS_ERROR;
1623 END IF;
1624
1625 -- set return exception list
1626 x_excp_list := g_final_excp_list;
1627 g_final_excp_list.DELETE;
1628 x_movement_statistics := l_orig_mvmt_transaction;
1629
1630 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1631 THEN
1632 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1633 , G_MODULE_NAME || l_api_name || '.end'
1634 ,'exit procedure'
1635 );
1636 END IF;
1637
1638 EXCEPTION
1639 WHEN FND_API.G_EXC_ERROR THEN
1640 x_return_status := FND_API.G_RET_STS_ERROR;
1641 -- Get message count and data
1642 FND_MSG_PUB.Count_And_Get
1643 ( p_count => x_msg_count
1644 , p_data => x_msg_data
1645 );
1646
1647 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1649 -- Get message count and data
1650 FND_MSG_PUB.Count_And_Get
1651 ( p_count => x_msg_count
1652 , p_data => x_msg_data
1653 );
1654
1655 WHEN OTHERS THEN
1656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1657 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1658 THEN
1659 FND_MSG_PUB.Add_Exc_Msg
1660 ( G_PKG_NAME
1661 , 'Validate_Movement_Statistics'
1662 );
1663 END IF;
1664 -- Get message count and data
1665 FND_MSG_PUB.Count_And_Get
1666 ( p_count => x_msg_count
1667 , p_data => x_msg_data
1668 );
1669 END Validate_Movement_Statistics;
1670
1671 --========================================================================
1672 -- PROCEDURE : Validate_Record PRIVATE
1673 -- PARAMETERS: p_movement_statistics IN movement transaction record
1674 -- p_movement_stat_usages_rec IN usage record
1675 -- x_mtl_transaction OUT movement transaction record
1676 -- x_return_status OUT standard output
1677 -- x_record_status OUT 'Y' if corrected, 'N' otherwise
1678 --
1679 -- VERSION : current version 1.0
1680 -- initial_version 1.0
1681 -- COMMENT : Validate the transaction record for its DELIVERY_TERMS,
1682 -- UNIT_WEIGHT/TOTAL_WEIGHT and COMMODITY_CODE.
1683 --=======================================================================
1684 PROCEDURE Validate_Record
1685 ( p_movement_stat_usages_rec IN
1686 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1687 , x_movement_statistics IN OUT NOCOPY
1688 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1689 , x_return_status OUT NOCOPY VARCHAR2
1690 , x_record_status OUT NOCOPY VARCHAR2
1691 , x_updated_flag OUT NOCOPY VARCHAR2
1692 )
1693 IS
1694 l_orig_mvmt_transaction
1695 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1696 l_chk NUMBER;
1697 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Record';
1698 l_weight_precision NUMBER;
1699 l_total_weight NUMBER;
1700 l_rounding_method VARCHAR2(30);
1701
1702 BEGIN
1703 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1704 THEN
1705 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1706 , G_MODULE_NAME || l_api_name || '.begin'
1707 ,'enter procedure'
1708 );
1709 END IF;
1710
1711 -- Copy input transaction record to local record for processing
1712 l_orig_mvmt_transaction := x_movement_statistics;
1713
1714 -- Init x_return_status and x_record_status
1715 x_return_status := FND_API.G_RET_STS_SUCCESS;
1716 -- x_record_status set to Y for correct record
1717 -- whenever record missing or incorrect then change the status to N
1718 x_record_status := 'Y';
1719 x_updated_flag := 'N';
1720
1721 /*
1722 -- Delivery Terms Validation
1723 IF l_orig_mvmt_transaction.delivery_terms is NOT NULL
1724 THEN
1725 -- we only check for invalid value
1726 BEGIN
1727 SELECT
1728 1
1729 INTO
1730 l_chk
1731 FROM
1732 FND_LOOKUPS
1733 WHERE lookup_type = 'MVT_DELIVERY_TERMS'
1734 AND lookup_code = l_orig_mvmt_transaction.delivery_terms;
1735
1736 EXCEPTION
1737 WHEN NO_DATA_FOUND THEN
1738 create_table_data( p_col_name => 'DELIVERY_TERMS'
1739 , p_message_cd => 2
1740 );
1741 -- 'INCORRECT' value for delivery_terms, set return status to error to
1742 -- indicate validate found error
1743 x_return_status := FND_API.G_RET_STS_ERROR;
1744 -- indicate incorrect record
1745 x_record_status := 'N';
1746 END;
1747 END IF;
1748 */
1749
1750 -- Commodity Infomation Validation
1751 -- check for missing commodity_code, if missing, we call
1752 -- get_category_id and get_commodity_info
1753 -- to fix the value
1754 IF l_orig_mvmt_transaction.commodity_code IS NULL
1755 THEN
1756
1757 -----if value can be corrected, correct it first
1758 ---- and if it can not be corrected, then report an exception
1759
1760 /* create_table_data(p_col_name => 'COMMODITY_CODE'
1761 ,p_message_cd => 1
1762 );
1763
1764 -- 'MISSING' value, so set return status to error
1765 x_return_status := FND_API.G_RET_STS_ERROR;
1766
1767 */
1768
1769 l_orig_mvmt_transaction.category_id
1770 := INV_MGD_MVT_UTILS_PKG.Get_Category_Id
1771 ( p_movement_transaction => l_orig_mvmt_transaction
1772 , p_stat_typ_transaction => p_movement_stat_usages_rec
1773 );
1774
1775 INV_MGD_MVT_UTILS_PKG.Get_Commodity_Info
1776 ( x_movement_transaction => l_orig_mvmt_transaction
1777 );
1778
1779 IF l_orig_mvmt_transaction.commodity_code IS NOT NULL
1780 THEN
1781 create_table_data(p_col_name => 'COMMODITY_CODE'
1782 ,p_message_cd => 4
1783 );
1784 x_updated_flag :='Y';
1785 ELSE
1786 -- record still incorrect , report as an exception
1787 x_record_status := 'N';
1788
1789 create_table_data(p_col_name => 'COMMODITY_CODE'
1790 ,p_message_cd => 1
1791 );
1792
1793 -- 'MISSING' value, so set return status to error
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795
1796 END IF;
1797 END IF;
1798
1799 -- Unit Weight Validation
1800 -- check for missing unit weight
1801 IF l_orig_mvmt_transaction.unit_weight IS NULL
1802 THEN
1803 -----if value can be corrected, correct it first
1804 ---- and if it can not be corrected, then report an exception
1805
1806 /* create_table_data(p_col_name => 'UNIT_WEIGHT'
1807 ,p_message_cd => 1
1808 );
1809 -- 'MISSING' value, so set return status to error
1810 x_return_status := FND_API.G_RET_STS_ERROR;
1811 */
1812 l_orig_mvmt_transaction.unit_weight :=
1813 INV_MGD_MVT_UTILS_PKG.Calc_Unit_Weight
1814 ( p_inventory_item_id => l_orig_mvmt_transaction.inventory_item_id
1815 , p_organization_id => l_orig_mvmt_transaction.organization_id
1816 , p_stat_typ_uom_code => p_movement_stat_usages_rec.weight_uom_code
1817 , p_tranx_uom_code => l_orig_mvmt_transaction.transaction_uom_code
1818 );
1819
1820 --Fix bug 4866967 and 5203245 get weight precision and rounding method
1821 INV_MGD_MVT_UTILS_PKG.Get_Weight_Precision
1822 (p_legal_entity_id => l_orig_mvmt_transaction.entity_org_id
1823 , p_zone_code => l_orig_mvmt_transaction.zone_code
1824 , p_usage_type => l_orig_mvmt_transaction.usage_type
1825 , p_stat_type => l_orig_mvmt_transaction.stat_type
1826 , x_weight_precision => l_weight_precision
1827 , x_rep_rounding => l_rounding_method);
1828
1829 l_total_weight := l_orig_mvmt_transaction.unit_weight *
1830 l_orig_mvmt_transaction.transaction_quantity;
1831
1832 l_orig_mvmt_transaction.total_weight := INV_MGD_MVT_UTILS_PKG.Round_Number
1833 ( p_number => l_total_weight
1834 , p_precision => l_weight_precision
1835 , p_rounding_method => l_rounding_method
1836 );
1837
1838 IF l_orig_mvmt_transaction.unit_weight IS NOT NULL
1839 THEN
1840 create_table_data(p_col_name => 'UNIT_WEIGHT'
1841 ,p_message_cd => 4
1842 );
1843 -- set record status to 'Y' as we made a change
1844 x_updated_flag := 'Y';
1845 ELSE
1846 -- record still incorrect, report an exception
1847 x_record_status := 'N';
1848 create_table_data(p_col_name => 'UNIT_WEIGHT'
1849 ,p_message_cd => 1
1850 );
1851 -- 'MISSING' value, so set return status to error
1852 x_return_status := FND_API.G_RET_STS_ERROR;
1853
1854 END IF;
1855 END IF;
1856
1857 x_movement_statistics := l_orig_mvmt_transaction;
1858
1859 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1860 THEN
1861 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1862 , G_MODULE_NAME || l_api_name || '.end'
1863 ,'exit procedure'
1864 );
1865 END IF;
1866
1867 EXCEPTION
1868
1869 WHEN OTHERS THEN
1870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1871 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1872 THEN
1873 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Validate_Record');
1874 END IF;
1875
1876 END Validate_Record;
1877
1878 --========================================================================
1879
1880 --========================================================================
1881 -- PROCEDURE : Set_Record_Status
1882 -- PARAMETERS: in_out_status
1883 --
1884 -- COMMENT : Set the Record Status to 'Y'
1885 --=======================================================================
1886
1887
1888
1889 --========================================================================
1890 -- PROCEDURE : Create_Table_Date PRIVATE
1891 -- PARAMETERS: p_col_name
1892 -- p_message_cd
1893 -- VERSION : current version 1.0
1894 -- initial version 1.0
1895 -- COMMENT : Create the Exception message into the Pl/SQL table. This table
1896 -- will be scanned while printing the Exception
1897 --=======================================================================--
1898
1899 PROCEDURE create_table_data
1900 ( p_col_name IN VARCHAR2
1901 , p_message_cd IN NUMBER
1902 )
1903 IS
1904 l_exception_rec INV_MGD_MVT_DATA_STR.Excp_Rec;
1905 BEGIN
1906
1907 l_exception_rec.excp_col_name := p_col_name;
1908 l_exception_rec.excp_message_cd := p_message_cd;
1909 g_final_excp_list(g_final_excp_list.COUNT + 1) := l_exception_rec;
1910
1911 END create_table_data ;
1912
1913 --========================================================================
1914 -- PROCEDURE : Delete_Movement_Statistics PUBLIC
1915 -- PARAMETERS:
1916 -- p_movement_transaction movement transaction data record
1917 -- VERSION : current version 1.0
1918 -- initial version 1.0
1919 -- COMMENT : Called by the Form to delete a movement record
1920 --=======================================================================
1921 PROCEDURE Delete_Movement_Statistics
1922 ( p_movement_transaction IN
1923 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1924 , x_return_status OUT NOCOPY VARCHAR2
1925 )
1926 IS
1927
1928 BEGIN
1929
1930 x_return_status := FND_API.G_RET_STS_SUCCESS;
1931
1932 DELETE FROM MTL_MOVEMENT_STATISTICS
1933 WHERE movement_id = p_movement_transaction.movement_id
1934 AND movement_status in ('O','V')
1935 AND edi_sent_flag = 'N';
1936
1937 EXCEPTION
1938
1939 WHEN NO_DATA_FOUND THEN
1940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1941
1942 WHEN OTHERS THEN
1943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1944
1945 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1946 THEN
1947 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Delete_Movement_Statistics');
1948 END IF;
1949
1950 END Delete_Movement_Statistics;
1951
1952
1953 --========================================================================
1954 -- PROCEDURE : Partner_vat_number PRIVATE
1955 -- PARAMETERS: p_movement_transaction movement transaction data record
1956 -- p_lookup_type
1957 -- p_rule_field_value
1958 -- x_return_status
1959 -- COMMENT : Called by the Validate_Rules procedure.
1960 -- This procedure creates Partner VAT Number exception.
1961 --=======================================================================
1962 PROCEDURE Partner_vat_number
1963 ( p_mvt_transaction_rec IN
1964 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1965 , p_lookup_type IN VARCHAR2
1966 , p_attribute_code IN VARCHAR2
1967 , p_rule_field_value IN VARCHAR2
1968 , x_return_status OUT NOCOPY VARCHAR2
1969 )
1970 IS
1971 l_count NUMBER;
1972 l_count1 NUMBER;
1973 l_form_vat_value VARCHAR2(50);
1974 l_mvt_transaction_rec INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1975 l_api_name CONSTANT VARCHAR2(30) := 'Partner_vat_number';
1976
1977 BEGIN
1978 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1979 THEN
1980 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1981 , G_MODULE_NAME || l_api_name || '.begin'
1982 ,'enter procedure'
1983 );
1984 END IF;
1985
1986 ---- Initialize x_return_status
1987 x_return_status := FND_API.G_RET_STS_SUCCESS;
1988
1989 l_mvt_transaction_rec:=p_mvt_transaction_rec;
1990
1991 ----check if the value matches any of the value in the lookup table
1992 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1993 THEN
1994 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1995 , G_MODULE_NAME || l_api_name
1996 ,'check if the value matches any of the value in the lookup table'
1997 );
1998 END IF;
1999
2000 SELECT COUNT(*)
2001 INTO l_count
2002 FROM fnd_lookups
2003 WHERE lookup_type = p_lookup_type
2004 AND lookup_code = p_rule_field_value;
2005
2006 IF l_count=0
2007 THEN
2008 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2009 THEN
2010 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2011 , G_MODULE_NAME || l_api_name
2012 , 'No,the value does not matches any of the values in the lookup table,
2013 get the value from the appropiate form'
2014 );
2015 END IF;
2016
2017 ------check value in the SO or PO or ORG form
2018 -----for SO
2019 IF p_mvt_transaction_rec.document_source_type IN ('SO','IO','RMA')
2020 AND p_mvt_transaction_rec.bill_to_site_use_id IS NOT NULL
2021 THEN
2022
2023 l_form_vat_value:=INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
2024 ( p_site_use_id => p_mvt_transaction_rec.bill_to_site_use_id
2025 );
2026
2027 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2028 THEN
2029 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2030 , G_MODULE_NAME || l_api_name
2031 , 'partner=customer, VAT='||l_form_vat_value
2032 );
2033 END IF;
2034
2035 ------for PO
2036 ELSIF p_mvt_transaction_rec.document_source_type in ('PO','RTV')
2037 AND p_mvt_transaction_rec.vendor_site_id IS NOT NULL
2038 THEN
2039 INV_MGD_MVT_UTILS_PKG.Get_Vendor_Info
2040 ( x_movement_transaction => l_mvt_transaction_rec
2041 );
2042
2043 l_form_vat_value:=l_mvt_transaction_rec.customer_vat_number;
2044
2045 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2046 THEN
2047 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2048 , G_MODULE_NAME || l_api_name
2049 , 'partner=vendor, VAT='||l_form_vat_value
2050 );
2051 END IF;
2052
2053 ---- for ORG
2054 ELSIF p_mvt_transaction_rec.document_source_type = 'INV'
2055 THEN
2056 l_form_vat_value :=INV_MGD_MVT_UTILS_PKG.Get_Org_VAT_Number
2057 ( p_entity_org_id => p_mvt_transaction_rec.entity_org_id
2058 , p_date => p_mvt_transaction_rec.transaction_date);
2059
2060 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2061 THEN
2062 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2063 , G_MODULE_NAME || l_api_name
2064 , 'partner=organization, VAT='||l_form_vat_value
2065 );
2066 END IF;
2067
2068 END IF;
2069
2070 ----check if the value from the form matches the lookup value
2071 SELECT COUNT(*)
2072 INTO l_count1
2073 FROM fnd_lookups
2074 WHERE lookup_type = p_lookup_type
2075 AND lookup_code = l_form_vat_value;
2076
2077 ----- the value does not match, this is the error
2078 IF l_count1=0
2079 THEN
2080 --Invalid Value for attribute field
2081 create_table_data(p_col_name => p_attribute_code
2082 ,p_message_cd => 1
2083 );
2084 x_return_status := FND_API.G_RET_STS_ERROR;
2085
2086 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2087 THEN
2088 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2089 , G_MODULE_NAME || l_api_name
2090 , 'The value in the form does not match to any of the lookup values, this is the exception'
2091 );
2092 END IF;
2093
2094 ----value match, correct value in the mvt_statistics table
2095 ELSE
2096 UPDATE mtl_movement_statistics
2097 SET customer_vat_number=l_form_vat_value
2098 WHERE movement_id=p_mvt_transaction_rec.movement_id;
2099
2100 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2101 THEN
2102 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2103 , G_MODULE_NAME || l_api_name
2104 , 'The value in the form matches one of the lookup value,
2105 correct value in the mvt_statistics table.'
2106 );
2107 END IF;
2108 END IF;
2109 END IF;
2110
2111 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2112 THEN
2113 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2114 , G_MODULE_NAME || l_api_name || '.end'
2115 ,'exit procedure'
2116 );
2117 END IF;
2118
2119 EXCEPTION
2120 WHEN OTHERS THEN
2121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2122 ---x_movement_transaction := p_mvt_transaction_rec;
2123 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2124 THEN
2125 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Partner_vat_number');
2126 END IF;
2127 INV_MGD_MVT_UTILS_PKG.Log( INV_MGD_MVT_UTILS_PKG.G_LOG_PROCEDURE,
2128 '-Partner_vat_number');
2129
2130
2131 END Partner_vat_number;
2132
2133
2134 --========================================================================
2135 -- PROCEDURE : Validate_Rules PRIVATE
2136 -- PARAMETERS: p_mtl_transaction IN movement transaction record
2137 -- x_mtl_transaction OUT movement transaction record
2138 -- x_return_status OUT standard output
2139 -- x_msg_count OUT NUMBER
2140 -- x_msg_data OUT VARCHAR2
2141 --
2142 -- VERSION : current version 1.0
2143 -- initial_version 1.0
2144 -- COMMENT : Validate the transaction record based Validation Rules
2145 --=======================================================================
2146 PROCEDURE Validate_Rules
2147 ( p_movement_stat_usages_rec IN
2148 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
2149 , x_movement_transaction IN OUT NOCOPY
2150 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
2151 , x_return_status OUT NOCOPY VARCHAR2
2152 , x_uom_status OUT NOCOPY VARCHAR2
2153 , x_msg_count OUT NOCOPY NUMBER
2154 , x_msg_data OUT NOCOPY VARCHAR2
2155 )
2156 IS
2157 l_orig_mvmt_transaction
2158 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
2159 l_chk NUMBER;
2160 Rule_field_Value VARCHAR2(240);
2161 correct_value VARCHAR2(240);
2162 valid_value VARCHAR2(1);
2163 lookup_value VARCHAR2(80);
2164 msg_cd NUMBER;
2165 conv_rate NUMBER;
2166 l_return_status VARCHAR2(200);
2167 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rules';
2168
2169 --Declare a cursor to fetch Attribute_Property set of Rules defined for the transaction Source Type
2170 CURSOR Rules IS
2171 Select ATTRIBUTE_CODE
2172 , ATTRIBUTE_PROPERTY_CODE
2173 , ATTRIBUTE_LOOKUP_TYPE
2174 FROM MTL_MVT_STATS_RULES
2175 WHERE RULE_SET_CODE = p_movement_stat_usages_rec.Attribute_rule_set_code
2176 AND SOURCE_TYPE = x_movement_transaction.document_source_type;
2177
2178 BEGIN
2179 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2180 THEN
2181 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2182 , G_MODULE_NAME || l_api_name || '.begin'
2183 ,'enter procedure'
2184 );
2185 END IF;
2186
2187 -- Initialize to 'N'
2188 x_uom_status := 'N';
2189
2190 -- Copy input transaction record to local record for processing
2191 l_orig_mvmt_transaction := x_movement_transaction;
2192
2193 -- Init x_return_status
2194 x_return_status := FND_API.G_RET_STS_SUCCESS;
2195
2196 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2197 THEN
2198 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2199 , G_MODULE_NAME || l_api_name
2200 , 'RULE_SET_CODE= '||p_movement_stat_usages_rec.Attribute_rule_set_code
2201 );
2202 END IF;
2203
2204 --Check to see if there is a rule defined for any of the user configurable fields
2205
2206 FOR Rules_rec IN Rules LOOP
2207
2208 IF Rules_rec.attribute_code= 'ORIGIN_TERRITORY_CODE' Then
2209 Rule_Field_Value := l_orig_mvmt_transaction.ORIGIN_TERRITORY_CODE;
2210 ELSIF Rules_rec.attribute_code='TRANSACTION_NATURE' Then
2211 Rule_Field_Value := l_orig_mvmt_transaction.TRANSACTION_NATURE;
2212 ELSIF Rules_rec.attribute_code= 'TRANSPORT_MODE' Then
2213 Rule_Field_Value := l_orig_mvmt_transaction.TRANSPORT_MODE;
2214 ELSIF Rules_rec.attribute_code= 'PORT' Then
2215 Rule_Field_Value := l_orig_mvmt_transaction.PORT;
2216 ELSIF Rules_rec.attribute_code= 'DELIVERY_TERMS' THEN
2217 Rule_Field_Value := l_orig_mvmt_transaction.DELIVERY_TERMS;
2218 ELSIF Rules_rec.attribute_code= 'STATISTICAL_PROCEDURE_CODE' THEN
2219 Rule_Field_Value := l_orig_mvmt_transaction.STATISTICAL_PROCEDURE_CODE;
2220 ELSIF Rules_rec.attribute_code= 'AREA' THEN
2221 Rule_Field_Value := l_orig_mvmt_transaction.AREA;
2222 ELSIF Rules_rec.attribute_code= 'OUTSIDE_CODE' THEN
2223 Rule_Field_Value := l_orig_mvmt_transaction.OUTSIDE_CODE;
2224 ELSIF Rules_rec.attribute_code= 'OUTSIDE_UNIT_PRICE' THEN
2225 Rule_Field_Value := l_orig_mvmt_transaction.OUTSIDE_UNIT_PRICE;
2226 ELSIF Rules_rec.attribute_code= 'OUTSIDE_EXT_VALUE' THEN
2227 Rule_Field_Value := l_orig_mvmt_transaction.OUTSIDE_EXT_VALUE;
2228 ELSIF Rules_rec.attribute_code= 'TRIANGULATION_COUNTRY_CODE' THEN
2229 Rule_Field_Value := l_orig_mvmt_transaction.TRIANGULATION_COUNTRY_CODE;
2230 ELSIF Rules_rec.attribute_code= 'CSA_CODE' THEN
2231 Rule_Field_Value := l_orig_mvmt_transaction.CSA_CODE;
2232 ELSIF Rules_rec.attribute_code= 'OIL_REFERENCE_CODE' THEN
2233 Rule_Field_Value := l_orig_mvmt_transaction.OIL_REFERENCE_CODE;
2234 ELSIF Rules_rec.attribute_code= 'CONTAINER_TYPE_CODE' THEN
2235 Rule_Field_Value := l_orig_mvmt_transaction.CONTAINER_TYPE_CODE;
2236 ELSIF Rules_rec.attribute_code= 'FLOW_INDICATOR_CODE' THEN
2237 Rule_Field_Value := l_orig_mvmt_transaction.FLOW_INDICATOR_CODE;
2238 ELSIF Rules_rec.attribute_code= 'AFFILIATION_REFERENCE_CODE' THEN
2239 Rule_Field_Value := l_orig_mvmt_transaction.AFFILIATION_REFERENCE_CODE;
2240 ------added for FPJ
2241 ELSIF Rules_rec.attribute_code= 'PARTNER_VAT_NUMBER' THEN
2242 Rule_Field_Value := l_orig_mvmt_transaction.CUSTOMER_VAT_NUMBER;
2243 ELSE Rule_Field_Value := NULL;
2244 END IF;
2245
2246 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2247 THEN
2248 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2249 , G_MODULE_NAME || l_api_name
2250 , 'Rules_rec.attribute_code= '||Rules_rec.attribute_code
2251 );
2252 END IF;
2253
2254 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2255 THEN
2256 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2257 , G_MODULE_NAME || l_api_name
2258 , 'Rule_Field_Value= '||Rule_Field_Value
2259 );
2260 END IF;
2261
2262 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2263 THEN
2264 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2265 , G_MODULE_NAME || l_api_name
2266 , 'Rules_rec.Attribute_property_Code= '||Rules_rec.Attribute_property_Code
2267 );
2268 END IF;
2269
2270 ---------------------
2271
2272 IF RULE_FIELD_VALUE IS NULL
2273 THEN
2274 -- Need to raise an exception only for REQUIRED fields
2275 IF Rules_rec.Attribute_property_Code = 'REQUIRED_UPDATEABLE'
2276 OR Rules_rec.Attribute_property_Code = 'REQUIRED_NON_UPDATEABLE'
2277 THEN
2278 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2279 THEN
2280 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2281 , G_MODULE_NAME || l_api_name
2282 , 'Exception for '||Rules_rec.attribute_code
2283 );
2284 END IF;
2285
2286 -----special code for Partner VAT Number
2287 IF Rules_rec.attribute_code='PARTNER_VAT_NUMBER'
2288 THEN
2289 Partner_vat_number
2290 ( p_mvt_transaction_rec => l_orig_mvmt_transaction
2291 , p_lookup_type => rules_rec.attribute_lookup_type
2292 , p_rule_field_value => rule_field_value
2293 , p_attribute_code => Rules_rec.attribute_code
2294 , x_return_status => l_return_status
2295 );
2296
2297 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2298 THEN
2299 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2300 , G_MODULE_NAME || l_api_name
2301 , 'out of Partner_vat_number with status '||l_return_status
2302 );
2303 END IF;
2304
2305 x_return_status :=l_return_status;
2306
2307 -------all other attribute codes
2308 ELSE
2309
2310 create_table_data(p_col_name => Rules_rec.attribute_code
2311 ,p_message_cd => 2
2312 );
2313
2314 x_return_status := FND_API.G_RET_STS_ERROR;
2315
2316 END IF;
2317 END IF;
2318 ELSE
2319 -- Verify if the value is valid from rules table
2320 -- Only If Lookup Type is defined for this field
2321
2322 IF Rules_rec.attribute_lookup_type is NOT NULL
2323 THEN
2324
2325 -----for Trading Partner VAT
2326 IF Rules_rec.attribute_code='PARTNER_VAT_NUMBER'
2327 THEN
2328 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2329 THEN
2330 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2331 , G_MODULE_NAME || l_api_name
2332 , 'Rules_rec.attribute_code='||Rules_rec.attribute_code
2333 );
2334 END IF;
2335
2336 Partner_vat_number( p_mvt_transaction_rec => l_orig_mvmt_transaction
2337 , p_lookup_type => rules_rec.attribute_lookup_type
2338 , p_rule_field_value => rule_field_value
2339 , p_attribute_code => Rules_rec.attribute_code
2340 , x_return_status => l_return_status
2341 );
2342
2343 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2344 THEN
2345 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2346 , G_MODULE_NAME || l_api_name
2347 , 'out of Partner_vat_number with status '||l_return_status
2348 );
2349 END IF;
2350 ELSE
2351
2352 BEGIN
2353 Select MEANING into lookup_value
2354 from fnd_lookups
2355 where lookup_type = rules_rec.attribute_lookup_type
2356 and lookup_code = rule_field_value;
2357 EXCEPTION
2358 WHEN NO_DATA_FOUND THEN
2359 --Invalid Value for attribute field
2360 create_table_data(p_col_name => Rules_rec.attribute_code
2361 ,p_message_cd => 2);
2362 x_return_status := FND_API.G_RET_STS_ERROR;
2363 END;
2364 END IF;
2365
2366 END IF; --Rules_rec.attribute_lookup_type
2367
2368 END IF; -- IF RULE_FIELD_VALUE IS NULL
2369
2370 END LOOP;
2371
2372
2373 -- For Rule Type as Alternate_UOM
2374 BEGIN
2375 IF l_orig_mvmt_transaction.alternate_uom_code is NULL then
2376
2377 -- Set x_uom_status to 'Y' to indicate Alternate Uom update
2378 x_uom_status := 'Y';
2379
2380 Select Attribute_Code
2381 Into l_orig_mvmt_transaction.alternate_uom_code
2382 From MTL_MVT_STATS_RULES
2383 Where RULE_SET_CODE = p_movement_stat_usages_rec.Alt_Uom_Rule_Set_Code
2384 And commodity_code = l_orig_mvmt_transaction.commodity_code;
2385
2386 -- Get Conversion rate using inv_convert.inv_um_conversion
2387 inv_convert.inv_um_conversion(
2388 from_unit => l_orig_mvmt_transaction.transaction_uom_code
2389 , to_unit => l_orig_mvmt_transaction.alternate_uom_code
2390 , item_id => l_orig_mvmt_transaction.inventory_item_id
2391 , uom_rate => conv_rate);
2392
2393 -- Calculate alternate quantity
2394 l_orig_mvmt_transaction.alternate_quantity :=
2395 l_orig_mvmt_transaction.transaction_quantity * conv_rate;
2396
2397 END IF;
2398
2399 EXCEPTION
2400 WHEN NO_DATA_FOUND THEN
2401 -- No Alternate UOM rule is defined
2402 --yawang
2403 IF l_orig_mvmt_transaction.alternate_uom_code IS NULL
2404 THEN
2405 x_uom_status := 'N';
2406 END IF;
2407 END;
2408
2409
2410
2411 x_movement_transaction := l_orig_mvmt_transaction;
2412
2413 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2414 THEN
2415 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2416 , G_MODULE_NAME || l_api_name || '.end'
2417 ,'exit procedure'
2418 );
2419 END IF;
2420
2421 EXCEPTION
2422 WHEN OTHERS THEN
2423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2424 x_movement_transaction := l_orig_mvmt_transaction;
2425 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2426 THEN
2427 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Validate_Rules');
2428 END IF;
2429
2430 END; -- Validate Rules
2431
2432 --========================================================================
2433 -- PROCEDURE : Get_Invoice_Transactions PRIVATE
2434 -- PARAMETERS: inv_crsr REF cursor
2435 -- x_return_status return status
2436 -- p_movement_transaction Movement stats record
2437 -- p_start_date Transaction start Date
2438 -- p_end_date Transaction End Date
2439 -- COMMENT : Get the Open and Verified Invoice Transactions
2440 --========================================================================
2441
2442 PROCEDURE Get_Invoice_Transactions (
2443 inv_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.valCurTyp
2444 , p_movement_transaction IN
2445 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
2446 , p_start_date IN DATE
2447 , p_end_date IN DATE
2448 , p_transaction_type IN VARCHAR2
2449 , x_return_status OUT NOCOPY VARCHAR2
2450 )
2451 IS
2452 l_api_name CONSTANT VARCHAR2(30) := 'Get_Invoice_Transactions';
2453 BEGIN
2454 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2455 THEN
2456 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2457 , G_MODULE_NAME || l_api_name || '.begin'
2458 ,'enter procedure'
2459 );
2460 END IF;
2461
2462 x_return_status := 'Y';
2463
2464 IF inv_crsr%ISOPEN THEN
2465 CLOSE inv_crsr;
2466 END IF;
2467
2468
2469 OPEN inv_crsr FOR
2470 SELECT
2471 mtl_stats.movement_id
2472 , mtl_stats.organization_id
2473 , mtl_stats.entity_org_id
2474 , mtl_stats.movement_type
2475 , mtl_stats.movement_status
2476 , mtl_stats.transaction_date
2477 , mtl_stats.last_update_date
2478 , mtl_stats.last_updated_by
2479 , mtl_stats.creation_date
2480 , mtl_stats.created_by
2481 , mtl_stats.last_update_login
2482 , mtl_stats.document_source_type
2483 , mtl_stats.creation_method
2484 , mtl_stats.document_reference
2485 , mtl_stats.document_line_reference
2486 , mtl_stats.document_unit_price
2487 , mtl_stats.document_line_ext_value
2488 , mtl_stats.receipt_reference
2489 , mtl_stats.shipment_reference
2490 , mtl_stats.shipment_line_reference
2491 , mtl_stats.pick_slip_reference
2492 , mtl_stats.customer_name
2493 , mtl_stats.customer_number
2494 , mtl_stats.customer_location
2495 , mtl_stats.transacting_from_org
2496 , mtl_stats.transacting_to_org
2497 , mtl_stats.vendor_name
2498 , mtl_stats.vendor_number
2499 , mtl_stats.vendor_site
2500 , mtl_stats.bill_to_name
2501 , mtl_stats.bill_to_number
2502 , mtl_stats.bill_to_site
2503 , mtl_stats.po_header_id
2504 , mtl_stats.po_line_id
2505 , mtl_stats.po_line_location_id
2506 , mtl_stats.order_header_id
2507 , mtl_stats.order_line_id
2508 , mtl_stats.picking_line_id
2509 , mtl_stats.shipment_header_id
2510 , mtl_stats.shipment_line_id
2511 , mtl_stats.ship_to_customer_id
2512 , mtl_stats.ship_to_site_use_id
2513 , mtl_stats.bill_to_customer_id
2514 , mtl_stats.bill_to_site_use_id
2515 , mtl_stats.vendor_id
2516 , mtl_stats.vendor_site_id
2517 , mtl_stats.from_organization_id
2518 , mtl_stats.to_organization_id
2519 , mtl_stats.parent_movement_id
2520 , mtl_stats.inventory_item_id
2521 , mtl_stats.item_description
2522 , mtl_stats.item_cost
2523 , mtl_stats.transaction_quantity
2524 , mtl_stats.transaction_uom_code
2525 , mtl_stats.primary_quantity
2526 , mtl_stats.invoice_batch_id
2527 , mtl_stats.invoice_id
2528 , mtl_stats.customer_trx_line_id
2529 , mtl_stats.invoice_batch_reference
2530 , mtl_stats.invoice_reference
2531 , mtl_stats.invoice_line_reference
2532 , mtl_stats.invoice_date_reference
2533 , mtl_stats.invoice_quantity
2534 , mtl_stats.invoice_unit_price
2535 , mtl_stats.invoice_line_ext_value
2536 , mtl_stats.outside_code
2537 , mtl_stats.outside_ext_value
2538 , mtl_stats.outside_unit_price
2539 , mtl_stats.currency_code
2540 , mtl_stats.currency_conversion_rate
2541 , mtl_stats.currency_conversion_type
2542 , mtl_stats.currency_conversion_date
2543 , mtl_stats.period_name
2544 , mtl_stats.report_reference
2545 , mtl_stats.report_date
2546 , mtl_stats.category_id
2547 , mtl_stats.weight_method
2548 , mtl_stats.unit_weight
2549 , mtl_stats.total_weight
2550 , mtl_stats.transaction_nature
2551 , mtl_stats.delivery_terms
2552 , mtl_stats.transport_mode
2553 , mtl_stats.alternate_quantity
2554 , mtl_stats.alternate_uom_code
2555 , mtl_stats.dispatch_territory_code
2556 , mtl_stats.destination_territory_code
2557 , mtl_stats.origin_territory_code
2558 , mtl_stats.stat_method
2559 , mtl_stats.stat_adj_percent
2560 , mtl_stats.stat_adj_amount
2561 , mtl_stats.stat_ext_value
2562 , mtl_stats.area
2563 , mtl_stats.port
2564 , mtl_stats.stat_type
2565 , mtl_stats.comments
2566 , mtl_stats.attribute_category
2567 , mtl_stats.commodity_code
2568 , mtl_stats.commodity_description
2569 , mtl_stats.requisition_header_id
2570 , mtl_stats.requisition_line_id
2571 , mtl_stats.picking_line_detail_id
2572 , mtl_stats.usage_type
2573 , mtl_stats.zone_code
2574 , mtl_stats.edi_sent_flag
2575 , mtl_stats.statistical_procedure_code
2576 , mtl_stats.movement_amount
2577 , mtl_stats.triangulation_country_code
2578 , mtl_stats.csa_code
2579 , mtl_stats.oil_reference_code
2580 , mtl_stats.container_type_code
2581 , mtl_stats.flow_indicator_code
2582 , mtl_stats.affiliation_reference_code
2583 , mtl_stats.origin_territory_eu_code
2584 , mtl_stats.destination_territory_eu_code
2585 , mtl_stats.dispatch_territory_eu_code
2586 , mtl_stats.set_of_books_period
2587 , mtl_stats.taric_code
2588 , mtl_stats.preference_code
2589 , mtl_stats.rcv_transaction_id
2590 , mtl_stats.mtl_transaction_id
2591 , mtl_stats.total_weight_uom_code
2592 , mtl_stats.financial_document_flag
2593 , mtl_stats.customer_vat_number
2594 , mtl_stats.attribute1
2595 , mtl_stats.attribute2
2596 , mtl_stats.attribute3
2597 , mtl_stats.attribute4
2598 , mtl_stats.attribute5
2599 , mtl_stats.attribute6
2600 , mtl_stats.attribute7
2601 , mtl_stats.attribute8
2602 , mtl_stats.attribute9
2603 , mtl_stats.attribute10
2604 , mtl_stats.attribute11
2605 , mtl_stats.attribute12
2606 , mtl_stats.attribute13
2607 , mtl_stats.attribute14
2608 , mtl_stats.attribute15
2609 , mtl_stats.triangulation_country_eu_code
2610 , mtl_stats.distribution_line_number
2611 , mtl_stats.ship_to_name
2612 , mtl_stats.ship_to_number
2613 , mtl_stats.ship_to_site
2614 , mtl_stats.edi_transaction_date
2615 , mtl_stats.edi_transaction_reference
2616 , mtl_stats.esl_drop_shipment_code
2617 FROM
2618 MTL_MOVEMENT_STATISTICS mtl_stats
2619 WHERE entity_org_id = p_movement_transaction.entity_org_id
2620 AND document_source_type = DECODE(p_transaction_type,null,
2621 document_source_type,'ALL',document_source_type,p_transaction_type )
2622 AND transaction_date BETWEEN trunc(p_start_date -1) and trunc(p_end_date +1)
2623 AND movement_status in ('O','V')
2624 AND financial_document_flag <> 'NOT_REQUIRED'
2625 ORDER BY mtl_stats.movement_id;
2626
2627 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2628 THEN
2629 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2630 , G_MODULE_NAME || l_api_name || '.end'
2631 ,'exit procedure'
2632 );
2633 END IF;
2634
2635 EXCEPTION
2636 WHEN NO_DATA_FOUND THEN
2637 x_return_status := 'N';
2638 WHEN OTHERS THEN
2639 x_return_status := 'N';
2640
2641 END Get_Invoice_Transactions;
2642
2643 --========================================================================
2644 -- PROCEDURE : Get_PO_Trans_With_Correction PRIVATE
2645 -- PARAMETERS: inv_crsr REF cursor
2646 -- x_return_status return status
2647 -- p_legal_entity_id Movement stats record
2648 -- p_start_date Transaction start Date
2649 -- p_end_date Transaction End Date
2650 -- COMMENT : Get the Open, Verified and Pending PO Transactions with
2651 -- correction
2652 --========================================================================
2653
2654 PROCEDURE Get_PO_Trans_With_Correction
2655 ( inv_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.valCurTyp
2656 , p_legal_entity_id IN NUMBER
2657 , p_start_date IN DATE
2658 , p_end_date IN DATE
2659 , p_transaction_type IN VARCHAR2
2660 , x_return_status OUT NOCOPY VARCHAR2
2661 )
2662 IS
2663 l_api_name CONSTANT VARCHAR2(30) := 'Get_PO_Trans_With_Correction';
2664 BEGIN
2665 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2666 THEN
2667 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2668 , G_MODULE_NAME || l_api_name || '.begin'
2669 ,'enter procedure'
2670 );
2671 END IF;
2672
2673 x_return_status := 'Y';
2674
2675 IF inv_crsr%ISOPEN
2676 THEN
2677 CLOSE inv_crsr;
2678 END IF;
2679
2680 OPEN inv_crsr FOR
2681 SELECT
2682 mtl_stats.movement_id
2683 , mtl_stats.organization_id
2684 , mtl_stats.entity_org_id
2685 , mtl_stats.movement_type
2686 , mtl_stats.movement_status
2687 , mtl_stats.transaction_date
2688 , mtl_stats.last_update_date
2689 , mtl_stats.last_updated_by
2690 , mtl_stats.creation_date
2691 , mtl_stats.created_by
2692 , mtl_stats.last_update_login
2693 , mtl_stats.document_source_type
2694 , mtl_stats.creation_method
2695 , mtl_stats.document_reference
2696 , mtl_stats.document_line_reference
2697 , mtl_stats.document_unit_price
2698 , mtl_stats.document_line_ext_value
2699 , mtl_stats.receipt_reference
2700 , mtl_stats.shipment_reference
2701 , mtl_stats.shipment_line_reference
2702 , mtl_stats.pick_slip_reference
2703 , mtl_stats.customer_name
2704 , mtl_stats.customer_number
2705 , mtl_stats.customer_location
2706 , mtl_stats.transacting_from_org
2707 , mtl_stats.transacting_to_org
2708 , mtl_stats.vendor_name
2709 , mtl_stats.vendor_number
2710 , mtl_stats.vendor_site
2711 , mtl_stats.bill_to_name
2712 , mtl_stats.bill_to_number
2713 , mtl_stats.bill_to_site
2714 , mtl_stats.po_header_id
2715 , mtl_stats.po_line_id
2716 , mtl_stats.po_line_location_id
2717 , mtl_stats.order_header_id
2718 , mtl_stats.order_line_id
2719 , mtl_stats.picking_line_id
2720 , mtl_stats.shipment_header_id
2721 , mtl_stats.shipment_line_id
2722 , mtl_stats.ship_to_customer_id
2723 , mtl_stats.ship_to_site_use_id
2724 , mtl_stats.bill_to_customer_id
2725 , mtl_stats.bill_to_site_use_id
2726 , mtl_stats.vendor_id
2727 , mtl_stats.vendor_site_id
2728 , mtl_stats.from_organization_id
2729 , mtl_stats.to_organization_id
2730 , mtl_stats.parent_movement_id
2731 , mtl_stats.inventory_item_id
2732 , mtl_stats.item_description
2733 , mtl_stats.item_cost
2734 , mtl_stats.transaction_quantity
2735 , mtl_stats.transaction_uom_code
2736 , mtl_stats.primary_quantity
2737 , mtl_stats.invoice_batch_id
2738 , mtl_stats.invoice_id
2739 , mtl_stats.customer_trx_line_id
2740 , mtl_stats.invoice_batch_reference
2741 , mtl_stats.invoice_reference
2742 , mtl_stats.invoice_line_reference
2743 , mtl_stats.invoice_date_reference
2744 , mtl_stats.invoice_quantity
2745 , mtl_stats.invoice_unit_price
2746 , mtl_stats.invoice_line_ext_value
2747 , mtl_stats.outside_code
2748 , mtl_stats.outside_ext_value
2749 , mtl_stats.outside_unit_price
2750 , mtl_stats.currency_code
2751 , mtl_stats.currency_conversion_rate
2752 , mtl_stats.currency_conversion_type
2753 , mtl_stats.currency_conversion_date
2754 , mtl_stats.period_name
2755 , mtl_stats.report_reference
2756 , mtl_stats.report_date
2757 , mtl_stats.category_id
2758 , mtl_stats.weight_method
2759 , mtl_stats.unit_weight
2760 , mtl_stats.total_weight
2761 , mtl_stats.transaction_nature
2762 , mtl_stats.delivery_terms
2763 , mtl_stats.transport_mode
2764 , mtl_stats.alternate_quantity
2765 , mtl_stats.alternate_uom_code
2766 , mtl_stats.dispatch_territory_code
2767 , mtl_stats.destination_territory_code
2768 , mtl_stats.origin_territory_code
2769 , mtl_stats.stat_method
2770 , mtl_stats.stat_adj_percent
2771 , mtl_stats.stat_adj_amount
2772 , mtl_stats.stat_ext_value
2773 , mtl_stats.area
2774 , mtl_stats.port
2775 , mtl_stats.stat_type
2776 , mtl_stats.comments
2777 , mtl_stats.attribute_category
2778 , mtl_stats.commodity_code
2779 , mtl_stats.commodity_description
2780 , mtl_stats.requisition_header_id
2781 , mtl_stats.requisition_line_id
2782 , mtl_stats.picking_line_detail_id
2783 , mtl_stats.usage_type
2784 , mtl_stats.zone_code
2785 , mtl_stats.edi_sent_flag
2786 , mtl_stats.statistical_procedure_code
2787 , mtl_stats.movement_amount
2788 , mtl_stats.triangulation_country_code
2789 , mtl_stats.csa_code
2790 , mtl_stats.oil_reference_code
2791 , mtl_stats.container_type_code
2792 , mtl_stats.flow_indicator_code
2793 , mtl_stats.affiliation_reference_code
2794 , mtl_stats.origin_territory_eu_code
2795 , mtl_stats.destination_territory_eu_code
2796 , mtl_stats.dispatch_territory_eu_code
2797 , mtl_stats.set_of_books_period
2798 , mtl_stats.taric_code
2799 , mtl_stats.preference_code
2800 , mtl_stats.rcv_transaction_id
2801 , mtl_stats.mtl_transaction_id
2802 , mtl_stats.total_weight_uom_code
2803 , mtl_stats.financial_document_flag
2804 , mtl_stats.customer_vat_number
2805 , mtl_stats.attribute1
2806 , mtl_stats.attribute2
2807 , mtl_stats.attribute3
2808 , mtl_stats.attribute4
2809 , mtl_stats.attribute5
2810 , mtl_stats.attribute6
2811 , mtl_stats.attribute7
2812 , mtl_stats.attribute8
2813 , mtl_stats.attribute9
2814 , mtl_stats.attribute10
2815 , mtl_stats.attribute11
2816 , mtl_stats.attribute12
2817 , mtl_stats.attribute13
2818 , mtl_stats.attribute14
2819 , mtl_stats.attribute15
2820 , mtl_stats.triangulation_country_eu_code
2821 , mtl_stats.distribution_line_number
2822 , mtl_stats.ship_to_name
2823 , mtl_stats.ship_to_number
2824 , mtl_stats.ship_to_site
2825 , mtl_stats.edi_transaction_date
2826 , mtl_stats.edi_transaction_reference
2827 , mtl_stats.esl_drop_shipment_code
2828 FROM
2829 MTL_MOVEMENT_STATISTICS mtl_stats
2830 WHERE entity_org_id = p_legal_entity_id
2831 AND document_source_type IN ('PO', 'RTV')
2832 AND movement_status IN ('O','V','P')
2833 AND transaction_date BETWEEN TRUNC(p_start_date -1)
2834 AND TRUNC(p_end_date +1)
2835 AND rcv_transaction_id
2836 IN (SELECT parent_transaction_id
2837 FROM rcv_transactions
2838 WHERE mvt_stat_status = 'NEW'
2839 AND transaction_type = 'CORRECT')
2840 ORDER BY mtl_stats.movement_id;
2841
2842 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2843 THEN
2844 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2845 , G_MODULE_NAME || l_api_name || '.end'
2846 ,'exit procedure'
2847 );
2848 END IF;
2849
2850 EXCEPTION
2851 WHEN NO_DATA_FOUND THEN
2852 x_return_status := 'N';
2853 WHEN OTHERS THEN
2854 x_return_status := 'N';
2855
2856 END Get_PO_Trans_With_Correction;
2857
2858
2859 --========================================================================
2860 -- PROCEDURE : Get_Pending_Txns PRIVATE
2861 -- PARAMETERS: val_crsr REF cursor
2862 -- x_return_status return status
2863 -- p_transaction_type Transaction Type
2864 -- COMMENT :
2865 -- This opens the cursor for INV and returns the cursor.
2866 --========================================================================
2867
2868 PROCEDURE Get_Pending_Txns (
2869 val_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.valCurTyp
2870 , p_movement_transaction IN
2871 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
2872 , p_document_source_type IN VARCHAR2
2873 , x_return_status OUT NOCOPY VARCHAR2
2874 )
2875 IS
2876 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pending_Txns';
2877 BEGIN
2878 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2879 THEN
2880 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2881 , G_MODULE_NAME || l_api_name || '.begin'
2882 ,'enter procedure'
2883 );
2884 END IF;
2885
2886 x_return_status := 'Y';
2887
2888 IF val_crsr%ISOPEN THEN
2889 CLOSE val_crsr;
2890 END IF;
2891
2892 OPEN val_crsr FOR
2893 SELECT
2894 mtl_stats.movement_id
2895 , mtl_stats.organization_id
2896 , mtl_stats.entity_org_id
2897 , mtl_stats.movement_type
2898 , mtl_stats.movement_status
2899 , mtl_stats.transaction_date
2900 , mtl_stats.last_update_date
2901 , mtl_stats.last_updated_by
2902 , mtl_stats.creation_date
2903 , mtl_stats.created_by
2904 , mtl_stats.last_update_login
2905 , mtl_stats.document_source_type
2906 , mtl_stats.creation_method
2907 , mtl_stats.document_reference
2908 , mtl_stats.document_line_reference
2909 , mtl_stats.document_unit_price
2910 , mtl_stats.document_line_ext_value
2911 , mtl_stats.receipt_reference
2912 , mtl_stats.shipment_reference
2913 , mtl_stats.shipment_line_reference
2914 , mtl_stats.pick_slip_reference
2915 , mtl_stats.customer_name
2916 , mtl_stats.customer_number
2917 , mtl_stats.customer_location
2918 , mtl_stats.transacting_from_org
2919 , mtl_stats.transacting_to_org
2920 , mtl_stats.vendor_name
2921 , mtl_stats.vendor_number
2922 , mtl_stats.vendor_site
2923 , mtl_stats.bill_to_name
2924 , mtl_stats.bill_to_number
2925 , mtl_stats.bill_to_site
2926 , mtl_stats.po_header_id
2927 , mtl_stats.po_line_id
2928 , mtl_stats.po_line_location_id
2929 , mtl_stats.order_header_id
2930 , mtl_stats.order_line_id
2931 , mtl_stats.picking_line_id
2932 , mtl_stats.shipment_header_id
2933 , mtl_stats.shipment_line_id
2934 , mtl_stats.ship_to_customer_id
2935 , mtl_stats.ship_to_site_use_id
2936 , mtl_stats.bill_to_customer_id
2937 , mtl_stats.bill_to_site_use_id
2938 , mtl_stats.vendor_id
2939 , mtl_stats.vendor_site_id
2940 , mtl_stats.from_organization_id
2941 , mtl_stats.to_organization_id
2942 , mtl_stats.parent_movement_id
2943 , mtl_stats.inventory_item_id
2944 , mtl_stats.item_description
2945 , mtl_stats.item_cost
2946 , mtl_stats.transaction_quantity
2947 , mtl_stats.transaction_uom_code
2948 , mtl_stats.primary_quantity
2949 , mtl_stats.invoice_batch_id
2950 , mtl_stats.invoice_id
2951 , mtl_stats.customer_trx_line_id
2952 , mtl_stats.invoice_batch_reference
2953 , mtl_stats.invoice_reference
2954 , mtl_stats.invoice_line_reference
2955 , mtl_stats.invoice_date_reference
2956 , mtl_stats.invoice_quantity
2957 , mtl_stats.invoice_unit_price
2958 , mtl_stats.invoice_line_ext_value
2959 , mtl_stats.outside_code
2960 , mtl_stats.outside_ext_value
2961 , mtl_stats.outside_unit_price
2962 , mtl_stats.currency_code
2963 , mtl_stats.currency_conversion_rate
2964 , mtl_stats.currency_conversion_type
2965 , mtl_stats.currency_conversion_date
2966 , mtl_stats.period_name
2967 , mtl_stats.report_reference
2968 , mtl_stats.report_date
2969 , mtl_stats.category_id
2970 , mtl_stats.weight_method
2971 , mtl_stats.unit_weight
2972 , mtl_stats.total_weight
2973 , mtl_stats.transaction_nature
2974 , mtl_stats.delivery_terms
2975 , mtl_stats.transport_mode
2976 , mtl_stats.alternate_quantity
2977 , mtl_stats.alternate_uom_code
2978 , mtl_stats.dispatch_territory_code
2979 , mtl_stats.destination_territory_code
2980 , mtl_stats.origin_territory_code
2981 , mtl_stats.stat_method
2982 , mtl_stats.stat_adj_percent
2983 , mtl_stats.stat_adj_amount
2984 , mtl_stats.stat_ext_value
2985 , mtl_stats.area
2986 , mtl_stats.port
2987 , mtl_stats.stat_type
2988 , mtl_stats.comments
2989 , mtl_stats.attribute_category
2990 , mtl_stats.commodity_code
2991 , mtl_stats.commodity_description
2992 , mtl_stats.requisition_header_id
2993 , mtl_stats.requisition_line_id
2994 , mtl_stats.picking_line_detail_id
2995 , mtl_stats.usage_type
2996 , mtl_stats.zone_code
2997 , mtl_stats.edi_sent_flag
2998 , mtl_stats.statistical_procedure_code
2999 , mtl_stats.movement_amount
3000 , mtl_stats.triangulation_country_code
3001 , mtl_stats.csa_code
3002 , mtl_stats.oil_reference_code
3003 , mtl_stats.container_type_code
3004 , mtl_stats.flow_indicator_code
3005 , mtl_stats.affiliation_reference_code
3006 , mtl_stats.origin_territory_eu_code
3007 , mtl_stats.destination_territory_eu_code
3008 , mtl_stats.dispatch_territory_eu_code
3009 , mtl_stats.set_of_books_period
3010 , mtl_stats.taric_code
3011 , mtl_stats.preference_code
3012 , mtl_stats.rcv_transaction_id
3013 , mtl_stats.mtl_transaction_id
3014 , mtl_stats.total_weight_uom_code
3015 , mtl_stats.financial_document_flag
3016 , mtl_stats.customer_vat_number
3017 , mtl_stats.attribute1
3018 , mtl_stats.attribute2
3019 , mtl_stats.attribute3
3020 , mtl_stats.attribute4
3021 , mtl_stats.attribute5
3022 , mtl_stats.attribute6
3023 , mtl_stats.attribute7
3024 , mtl_stats.attribute8
3025 , mtl_stats.attribute9
3026 , mtl_stats.attribute10
3027 , mtl_stats.attribute11
3028 , mtl_stats.attribute12
3029 , mtl_stats.attribute13
3030 , mtl_stats.attribute14
3031 , mtl_stats.attribute15
3032 , mtl_stats.triangulation_country_eu_code
3033 , mtl_stats.distribution_line_number
3034 , mtl_stats.ship_to_name
3035 , mtl_stats.ship_to_number
3036 , mtl_stats.ship_to_site
3037 , mtl_stats.edi_transaction_date
3038 , mtl_stats.edi_transaction_reference
3039 , mtl_stats.esl_drop_shipment_code
3040 FROM
3041 MTL_MOVEMENT_STATISTICS mtl_stats
3042 WHERE entity_org_id = p_movement_transaction.entity_org_id
3043 AND document_source_type = DECODE(p_document_source_type,null,
3044 document_source_type,'ALL',document_source_type,p_document_source_type )
3045 AND movement_status = 'P'
3046 ORDER BY
3047 mtl_stats.movement_id;
3048
3049 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3050 THEN
3051 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3052 , G_MODULE_NAME || l_api_name || '.end'
3053 ,'exit procedure'
3054 );
3055 END IF;
3056
3057 EXCEPTION
3058 WHEN NO_DATA_FOUND THEN
3059 x_return_status := 'N';
3060 WHEN OTHERS THEN
3061 x_return_status := 'N';
3062
3063 END Get_Pending_Txns;
3064
3065 END INV_MGD_MVT_STATS_PVT;