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